MySQL复习基础语句速览

发表于 | 分类于

MySQL基础速览

数据库快忘掉了，回头再总结一下常用语句，还有事务的一些基础知识。

增加

1
2
3
4
5
6
7
8
#插入单行
insert into user(name,pwd) values('shirtiny','123');
#将现有表数据 插入到一个已有表
insert into user(name,pwd) select name,pwd from user;
#将现有表数据 插入到新建表
select name,pwd into user2 from user;
#合并数据插入多行
insert user(name, pwd) select 'shirtiny','123' union select 'anro','123'

删除

1
2
3
4
#删除满足条件的行
delete from user where name = 'shirtiny'
#删除整个表
truncate table user;

修改

1
2
#修改 满足条件的行
update user set name='anro',pwd='123' where name = 'shirtiny';

查询

distinct排除重复

1
2
select distinct name from teacher;
-- 这样会如果有同名的老师，相同名字只会查出一条

between x and y查出x到y范围内的记录

1
2
3
select * from student where score between 60 and 100;
-- 这样会查出分数在60到100的学生，相当于：
select * from student where score > 60 and score < 100;

in查询多个值，或的关系

1
2
3
4
select * from student where score in(60,70,80);
-- 查出分数是60、70、80的学生
select * from student where class not in(1, 2);
-- 查询班级不是1班和2班的学生

or表示或者

1
2
select * from student where score = 70 or score = 80;
-- 查出分数是70、80的学生

order by ... asc/desc升序、降序

1
2
3
4
5
select * from student order by age asc;
select * from student order by score desc;
-- 分别以年龄升序、分数降序查询学生，如果需要多个字段同时排序：
select * from student order by age asc, score desc;
-- 会先按照年龄升序，年龄相同时，按照分数降序

count统计和计数

1
2
select count(*) from student where score = 60;
-- 查出分数为60的学生数

max得出某字段的最大值

1
2
select max(score) from student;
-- 查出学生分数的最高值

子查询，可以使用括号来嵌入一个子查询

1
2
select name,age from student where score = (select max(score) from student);
-- 查出分数最高的学生的名字和年龄

limit x,y，从x开始查询y个

1
2
select name,age from student order by score desc limit 0,10;
-- 先把学生按照分数降序排列，然后取前10个，得到分数前10的学生

avg计算平均值

1
2
select avg(score) from student where class = 1;
-- 计算1班学生的平均成绩

group by ... having分组

1
2
3
4
select avg(score) from student group by class;
-- 列出每个班学生的平均成绩
select avg(score) from student group by class having age>16;
-- 查询每个班的学生平均成绩 筛选出年龄大于16的学生

like模糊查询

1
2
3
4
select avg(score) from student group by class having age>16 and name like '王%';
-- 查询每个班的学生平均成绩 筛选出年龄大于16并且姓王的学生
select avg(score) from student group by class having age>16 and name not like '王%';
-- 查询每个班的学生平均成绩 筛选出年龄大于16并且不姓王的学生

多表查询

1
2
select cname,sname,score from course,student where course.cid=student.cid;
-- 同时查询课程和学生这两张表，同样的方法也可以查3张表，加个and

year得出时间字段的年份，now得出当前日期和时间

1
2
3
4
select * from student where year(birthday) = 1998;
-- 查出所有在1998年出生的学生
select year(now()) - year(birthday) from student;
-- 查出所有学生的年龄，当前时间的年份减去生日的年份

union将两个查询语句的结果合并

1
2
3
4
5
select * from class1 union select * from class2;
-- 将显示班级1和班级2的学生，但重名的学生只会显示一个，比如有2个班共有3个李华，但只会显示1个李华
-- 请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
select * from class1 union all select * from class2;
-- 将显示2个班的学生，重名的也会显示

as取别名

1
2
3
4
select sname as name,age from student union all select tname,age from teacher;
-- 查询结果的第一列将以name为字段名
select name as '姓名', year(now()) - year(birthday) as '年龄' from student;
-- 查出所有学生的姓名和年龄

any(子查询语句)表示子查询语句结果的任何一个值

1
2
select * from student where age > any(select age from student where name in ('李华', 'Tom'));
-- 查找满足 年龄比李华或Tom年龄大的学生

all(子查询语句)表示子查询语句结果的全部值

1
2
select * from student where age > all(select age from student where name in ('李华', 'Tom'));
-- 查找满足 年龄比李华或tom年龄都大的学生

链接查询

1
2
3
4
5
6
7
8
9
10
11
-- 内连接查询 inner join ，inner可省略 ，比如查出有班级的所有学生
select * from student join class on student.cid = class.cid;
-- 左外连接 left outer join， outer可省略，比如查出所有学生包括没有班级的学生，此时没有班级的那部分学生班级会补null
select * from student left join class on student.cid = class.cid;
-- 右外连接 right outer join，outer可省略，比如查出所有班级，包括没有学生的班级，此时没有学生的那部分班级会补null
select * from student right join class on student.cid = class.cid;
-- 全外连接查询 msyql没有full join，但可以使用左外连接+union+右外链接来实现全连接
select * from student left join class on student.cid = class.cid union select * from student right join class on student.cid = class.cid;
-- 可以用别名代替表名
select * from student s join class c on s.cid = c.cid;
-- 更直观的可以看图 https://blog.csdn.net/zjt980452483/article/details/82945663

事务

感受一下什么是事务

1
2
3
4
5
6
7
8
9
10
11
-- 关闭mysql的自动提交，不关会自动提交每条sql，rollback无效的 （应用中不需要关，这里是玩
set autocommit = 0;
-- 之后的sql需要手动提交，才会真实的在数据库中生效
insert into student values(10,'lihua',16,78);
-- 回滚 撤销未提交的sql
rollback;
insert into student values(10,'李华',16,78);
-- 手动提交 提交后无法再撤销
commit;
-- 把自动提交改回来
set autocommit = 1;

begin或start transaction可以开启一个事务

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 开启事务 begin和start transaction都行
begin;
insert into student values(10,'lihua',16,78);
update student set age = 21 where sid = 10;
-- 可以rollback 撤销事务并结束
rollback;

-- 再开启一个事务
start transaction;
insert into student values(10,'李华',16,78);
update student set age = 20 where sid = 10;
-- 提交后结束事务
commit;

事务的四大特征 ACID

1
2
3
4
A 原子性
C 一致性 事务内的sql必须同时成功或失败
I 隔离性
D 持久性 一旦结束（commit和rollback），就不可返回

事务的隔离级别

1
2
3
4
5
6
7
-- 查看数据库的隔离级别
-- mysql 8.0 系统、会话
select @@global.transaction_isolation;
select @@transaction_isolation;
-- mysql 5.x 系统、会话
select @@global.tx_isolation;
select @@tx_isolation;
  • read uncommitted
1
2
3
--修改数据库的隔离级别 为read uncommitted
set global transaction isolation level read uncommitted;
-- 此时事务a可以读到事务b未commit的数据，一旦b事务rollback，a的事务就会受到影响

就会出现脏读：事务a读到了事务b未commit的数据

  • read committed
1
2
3
-- 修改数据库的隔离级别 为read committed
set global transaction isolation level read committed;
-- 此时事务a只能读到事务b commit后的数据，但读的数据前后可能会发生改变，因为b可能对表做了修改

会出现不可重复读：读取同一个表的数据，发现前后不一致

  • repeatable read可重复读
1
2
3
-- 修改数据库的隔离级别 为repeatable read
set global transaction isolation level repeatable read;
-- 此时即使事务b对表插入了id为1的一行记录，事务a也不会察觉到表发生了改变

会出现幻读：事务a明明看到表没有变化，但无法插入id为1的行

  • serializable串行化
1
2
3
-- 修改数据库的隔离级别 为serializable
set global transaction isolation level serializable;
-- 此时类似线程同步，事务a对表操作时，事务b对这个表的操作会排在a后面，a提交后，b的操作才会被执行

性能很差。

隔离与锁有关，隔离级别越高性能越差，未提交读取 > 已提交读取 > 可重复读取 > 串行化
mysql默认为可重复读取