mysql常用命令总结

文章目录

• 1.查询所有数据：
• 2. 查询单个数据：
• 3. 计算数据并且给出别名：
• 4. 升序排序数据:
• 5. 降序排序数据:
• 6. 多条件排序数据：
• 7. 根据区间范围查询数据：
• 8. 枚举查询：
• 9. 模糊查询：
• 10. case end语句
• 11.给数据分组
• 12.查询事务隔离级别
• 13.证明事务隔离级别的可重复读
• 14.修改事务级别
• 15.查看和修改事务提交方式
• 16证明脏读，隔离级别read uncommitted
• 17.证明不可重复读 隔离级别read committed
• 18 Alter语句
• • 增加单个字段
  • 修改字段的类型
  • 删除字段
  • 修改字段名和属性
• 19 Insert语句
• • 插入数据
  • 只插入某个列数据
• 20.update语句
• • 更新数据
  • 带判断更新数据

1.查询所有数据：

select * from student;

2. 查询单个数据：

select name from student;

3. 计算数据并且给出别名：

select age*2 as '2倍年龄' from student;
+---------+
| 2倍年龄 |
+---------+
|      24 |
|      48 |
|      44 |
|      26 |
|      26 |
+---------+

4. 升序排序数据:

select age from student order by age asc;
+------+
| age  |
+------+
|   12 |
|   13 |
|   13 |
|   22 |
|   24 |
+------+

5. 降序排序数据:

select age from student order by age desc;
+------+
| age  |
+------+
|   24 |
|   22 |
|   13 |
|   13 |
|   12 |
+------+

6. 多条件排序数据：

mysql> select * from student order by name desc,age asc;
//name中有重复值的情况下，才会走age的排序规则；
//name没重复值，则只走name的排序规则；
+--------+------+
| name   | age  |
+--------+------+
| tom    |   12 |
| mike   |   24 |
| jack   |   22 |
| engine |   13 |
| ai     |   13 |
+--------+------+

7. 根据区间范围查询数据：

select * from student where age between 22 and 24;
+------+------+
| name | age  |
+------+------+
| mike |   24 |
| jack |   22 |
+------+------+

8. 枚举查询：

mysql> select * from student where age in(13,24,22);
//有多个固定值需要一块查询，就用枚举查询；
+--------+------+
| name   | age  |
+--------+------+
| mike   |   24 |
| jack   |   22 |
| engine |   13 |
| ai     |   13 |
+--------+------+

9. 模糊查询：

%表示占位符，可以表示任意数量的字符；
_表示单个字符，你需要占多少位就写多少个下划线；

//以m开头的所有数据；
mysql> select * from student where name like 'm%';
+------+------+
| name | age  |
+------+------+
| mike |   24 |
+------+------+ 
//以e结尾的所有数据；
mysql> select * from student where name like '%e';
+--------+------+
| name   | age  |
+--------+------+
| mike   |   24 |
| engine |   13 |
+--------+------+
//以m开头的所有数据；
mysql> select * from student where name like 'm___';
+------+------+
| name | age  |
+------+------+
| mike |   24 |
+------+------+

10. case end语句

注意：select 中 *后面要加逗号，不然会报错；

mysql> select * ,
    -> case
    -> when age>=18 then '成年'
    -> else '未成年'
    -> end as '是否成年' //将查询后的数据起名字
    -> from student;
+--------+------+----------+
| name   | age  | 是否成年 |
+--------+------+----------+
| tom    |   12 | 未成年   |
| mike   |   24 | 成年     |
| jack   |   22 | 成年     |
| engine |   13 | 未成年   |
| ai     |   13 | 未成年   |
+--------+------+----------+
5 rows in set (0.00 sec)

11.给数据分组

mysql> select * from student ;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   12 |
| mike   |   24 |
| jack   |   22 |
| engine |   13 |
| ai     |   13 |
+--------+------+
5 rows in set (0.00 sec)

mysql> select name,age from student group by age;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   12 |
| engine |   13 |
| jack   |   22 |
| mike   |   24 |
+--------+------+

12.查询事务隔离级别

select @@tx_isolation;

13.证明事务隔离级别的可重复读

隔离级别：repeattable read 可重复读
A事务

```handlebars
mysql> select * from student;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   11 |
| mike   |  123 |
| jack   |   22 |
| engine |   13 |
| ai     |   13 |
+--------+------+

mysql> begin;
mysql> update student set age=110 where name='mike';
mysql> select * from student;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   11 |
| mike   |  110 |
| jack   |   22 |
| engine |   13 |
| ai     |   13 |
+--------+------+
5 rows in set (0.00 sec)

mysql> commit;

事务B

mysql> select * from student;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   11 |
| mike   |  123 |
| jack   |   22 |
| engine |   13 |
| ai     |   13 |
+--------+------+
mysql> begin;//开启事务B
mysql> select * from student;//A事务修改之后查询；
+--------+------+
| name   | age  |
+--------+------+
| tom    |   11 |
| mike   |  123 |
| jack   |   22 |
| engine |   13 |
| ai     |   13 |
+--------+------+

mysql> select * from student;//A事务commit后查询；
+--------+------+
| name   | age  |
+--------+------+
| tom    |   11 |
| mike   |  123 |
| jack   |   22 |
| engine |   13 |
| ai     |   13 |
+--------+------+
mysql> commit;
mysql> select * from student;//B事务commit后查询
+--------+------+
| name   | age  |
+--------+------+
| tom    |   11 |
| mike   |  110 |
| jack   |   22 |
| engine |   13 |
| ai     |   13 |
+--------+------+

14.修改事务级别

set session transaction isolation level 后面加事务隔离级别
 set session transaction isolation level read uncommitted;

15.查看和修改事务提交方式

mysql> select @@autocommit; //查看事务提交方式
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
// 1.表示自动提交 0表示手动提交
mysql> set @@autocommit=0;//修改事务提交方式
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

16证明脏读，隔离级别read uncommitted

事务A

mysql> begin;
mysql> select * from student;//原来是33,
+--------+------+
| name   | age  |
+--------+------+
| tom    |   33 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+
mysql> select * from student;//事务B更新了age为24，
//读取到了24，这个24是还没有commit的，所以是脏读
+--------+------+
| name   | age  |
+--------+------+
| tom    |   24 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+
mysql> select * from student;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   33 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+

事务B

mysql> begin;
mysql> update student set age=24 where name='tom';//更新值
mysql> select * from student;//更新成功
+--------+------+
| name   | age  |
+--------+------+
| tom    |   24 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+
mysql> rollback;//事务回滚
mysql> select * from student;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   33 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+

17.证明不可重复读 隔离级别read committed

事务A

mysql> begin;
mysql> select * from student;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   33 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+
mysql> select * from student;//事务B修改之后未commit
+--------+------+
| name   | age  |
+--------+------+
| tom    |   33 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+
mysql> select * from student;//事务B  commit后
+--------+------+
| name   | age  |
+--------+------+
| tom    |   24 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+

事务B

mysql> begin;
mysql> select * from student;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   33 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+
mysql> update student set age=24 where name='tom';
mysql> select * from student;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   24 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+
mysql> commit;
mysql> select * from student;
+--------+------+
| name   | age  |
+--------+------+
| tom    |   24 |
| mike   |   14 |
| jack   |   16 |
| engine |   13 |
| ai     |   13 |
| mike   |   15 |
+--------+------+

18 Alter语句 增加单个字段

alter table student add class int;
mysql> select * from student;
+--------+------+-------+
| name   | age  | class |
+--------+------+-------+
| tom    |   24 |  NULL |
| mike   |   14 |  NULL |
| jack   |   16 |  NULL |
| engine |   13 |  NULL |
| ai     |   13 |  NULL |
| mike   |   15 |  NULL |
| hello  |   24 |  NULL |
| hello  |   25 |  NULL |
+--------+------+-------+

修改字段的类型

 alter table student modify class varchar(33);
 +-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(33) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| class | varchar(33) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

删除字段

mysql> alter table student drop class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(33) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

修改字段名和属性

必须都修改，不能只修改一个，只修改属性用modify；

mysql> alter table student change name username varchar(33);
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(33) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

19 Insert语句 插入数据

insert into student(username,age) values("张三",14);

只插入某个列数据

insert into student(age) value(24);
mysql> select * from student;
+----------+------+
| username | age  |
+----------+------+
| tom      |   24 |
| mike     |   14 |
| jack     |   16 |
| engine   |   13 |
| ai       |   13 |
| mike     |   15 |
| hello    |   24 |
| hello    |   25 |
| 张三     |   14 |
| NULL     |   24 |
+----------+------+

20.update语句 更新数据

update student set age=33;
将age字段的所有值改成33

带判断更新数据

update student set age=24 where username="tom";
将tom的年龄改成24