【MySQL】DML数据操作语句和基本的DQL语句

        update stu1 set birthday='1678-2-2';

        update stu1 set birthday='1888-1-1' where sname='陈博远';

        update newstu set classid = 200 where xingbie != '男';     -- null是类型，不是值，性别为null，不会受影响

        update newstu set classid = 300 where xingbie <> '女';     -- <> 不等于

        update newstu set xingbie = '保密' where classid < 260;

        update newstu xingbie = '外星人' where classid >=30 and classid <=90;

        -- 30 50 70 性别变为地球人

        update newstu set xingbie='地球人' where classid=30 or classid=50 or classid=70;

        update newstu set xingbie='火星人' where classid>=30 and classid<=90;

        update newstu set xingbie='水星人' where classid between 30 and 90;

3. 删除

-- delete from 表名【where 子句】

delete from newstu;

delete from stu1 where sid=1;

-- 清空表/截断表

-- truncate 表名

truncate stu1;

3.1 delete、truncate、drop区别

-- delete只删数据

-- truncate不仅把数据删掉，还删除了索引

-- drop 不仅把数据删掉，还删除了索引，表结构也删了

二、DQL语言（重点）

DQL(Data Query Language 数据查询语言)。用途是查询数据库数据，如SELECT语句。是SQL语句中最核心、最重要的语句，也是使用频率最高的语句。其中，可以根据表的结构和关系分为单表查询和多表联查。

单表查询

针对数据库中的一张数据表进行查询，可以通过各种查询条件和方式去做相关的优化。

多表联查

针对数据库中两张或者两张以上的表同时进行查询，依赖的手段有复杂查询和嵌套查询

1. 单表查询

-- 所有的查询都会得到一张虚拟表

1.1 最简单的查询

select 123;

select 'abc';

select 1+1;

1.2 从表中获取数据

-- select 字段名,字段名... from 表名

（1）全字段查询

select sid,sname,birthday,ssex,classid from student;

select * from student; -- 效率慢，不推荐使用

（2）部分字段查询

select sname,ssex from student;

1.3 字段名起别名

select sname as '姓名' from student;

select sname as '姓名',birthday '生日' from student;

select sname as '姓名',birthday '生日',ssex 性别 from student;

select sname 姓名 from student;

1.4 添加字段

select sname,'猿究院' from student;

select sname,'猿究院' 学校 from student; -- 字段猿究院别名为学校

1.5 distinct 去重

-- 所有的字段的数据都一致才会去重

select ssex from student;

select distinct ssex from student;

select distinct sname,ssex from student;

1.6 带条件的查询

-- 【where子句】

select * from student where sid = 5

select * from student where sid <> 5;

select * from student where sid > 5;

select * from student where sid between 3 and 6;

-- 查找1班的女同学

select * from student where classid=1 and ssex='女';

-- 面试题

-- 查询年龄大于1990-1-1 的同学

select * from student where birthday < '1990-1-1'; -- 日期比大小，逻辑反的

1.7 in 表示在某个特定的范围内

-- 3 5 7 9

-- or 会让索引失效

select * from student where sid=3 or sid=5 or sid=7 or sid=9;

-- 推荐

-- in 可以使用索引

select * from student where sid in(3,5,7,9,20,100);

1.8 like 模糊查询

-- 模糊符号

（1）% 任意多的字符

（2）_ 一个任意字符

insert into student(sname) values('杨文齐'),('小小杨'),('杨帅哥'),('帅气的杨同学');

select * from student where sname like '%杨%';

select * from student where sname like '%杨';

select * from student where sname like '杨%';

select * from student where sname like '杨__'; -- 两个下划线

1.9 null

-- is：是一个什么

select * from student where birthday is null;

select * from student where birthday is not null;

1.10 聚合函数(重点）

-- 把多个值变为一个值

-- count() 统计个数

-- max() 求最大值

-- min() 求最小值

-- sum() 总和

-- avg() 平均值

（1）count 任何类型不统计null

-- select count(字段、常量、*) from 表名

select count(sid) from student; -- 主键

select count(classid) from student; -- 不统计null

select count('a') from student; -- 不推荐

select count(123) from student; -- 推荐

select count(*) from student; -- 推荐

（2） sum avg min max 数值类型

select sum(score) from sc;

select avg(score) from sc;

select max(score) from sc;

select min(score) from sc;

-- 统计出成绩表中一共有多少次考试，总成绩，平均分，最高分，最低分

select count(*),sum(score),avg(score),max(score),min(score) from sc;

1.11 group by(重重点)

对所有的数据进行分组统计；

分组的依据字段可以有多个，并依次分组。

-- 分组之后必然有聚合

-- 男女同学个有多少人

select ssex,count(1) from student group by ssex;

-- 统计出各班有多少人

select classid,count(1) from student group by classid;

-- 统计成绩表中每个同学的总分和平均分

select sid,sum(score),avg(score) from sc group by sid;

1.12 where和having区别

-- having 对数据表中单个数据进行判断，与group by结合使用，进行分组后的数据筛选。

-- 查询出平均分不及格的同学

select sid,sum(score),avg(score) from sc

group by sid

having avg(score) < 60;

select sid,sum(score),avg(score) from sc

where score < 60

group by sid

having avg(score) < 60;

1.13 order by 排序

-- 先写先排

-- 升序 asc 不写（默认）

-- 降序 desc 必须声明

select * from student order by classid;

select * from student order by classid asc;

select * from sc order by score desc,cid desc; -- score降序，分数相同，按cid降序

1.14 limit 分页 0开始（页码-1）*步长，步长

-- select * from 表名 limit 位置，步长

select * from student limit 6,3

-- 应用层解决

-- select * from student limit (3-1)*3,3 错误的

-- 找到成绩及格的总分数排名第二的：sid总成绩

        select sid,sum(score) from sc

        where score >60

        group by sid

        order by sum(score) desc

        limit 1,1

1.15 扩展

1.16 小结

• DML语句内容
        • INSERT语句,UPDATE语句和DELETE语句；
• 新增语句如何实现多记录同时新增？
        • INSERT INTO `表名` (`字段1`,`字段n`) VALUES (值1，值n),(值1，值n),(值1，值n);
• WHERE子句的功能？
        • 依赖逻辑条件对数据库的记录修改，删除或者查询；
• TRUNCATE语句和DELETE语句的异同？
        • 相同点：都能删除数据，都不能修改表结构；
        • 不同点：1、前者会重置自增计数器，后者不会；
2、前者无条件约束，速度快效率高。
• DQL语句内容
        • SELECT语句。

2. 多表查询

2.1 内联查询

（1）非等值查询 -- 笛卡尔积两个结果集相乘逻辑上有错误

-- 查询出学生和班级信息

select * from student,class;

（2）等值查询

-- 查询出学生和班级信息 student class

select * from student,class

where student.classid=class.classid;

-- 5张表联查

select * from student,class,course,teacher,sc

where student.classid=class.classid and course.cid=sc.cid and class.classid=student.classid and teacher.Tid=course.Tid;

-- 查询出学过张三老师课程的学生信息

select * from student,class,course,teacher,sc

where student.sid=sc.sid and course.cid=sc.cid and teacher.Tid=course.Tid and teacher.tname='张三';

-- 多表最终跟单表一致

-- 查询每个学生的平均成绩学生姓名，班级名称，平均成绩

select sname,classname,avg(score) from student,sc,class

where student.sid=sc.sid and class.classid=student.classid

group by student.sid;

（3）inner join on

-- 笛卡尔积

-- 表的个数多，每个表的数据量不大，吃内存，IO小

select * from student,class

where student.classid = class.classid and ssex='男';

-- 通过第一张表的结果集进行on条件匹配

-- 表少，每张表的数据量很大，内存占用小，但IO高

select * from student

inner join class on student.classid = class.classid

where ssex='男';

-- 3表查询

select * from student

inner join class on student.classid=class.classid

inner join sc on student.sid=sc.sid;

-- 5表联查

select * from student

inner join class on student.classid=class.classid

inner join sc on student.sid=sc.sid

inner join course on sc.cid=course.Cid

inner join teacher on course.tid=teacher.Tid;

-- 每门课程的平均成绩课程名称代课老师姓名平均成绩

select cname,tname,avg(score) from course,teacher,sc

where course.cid=sc.cid and teacher.tid=course.tid

group by course.cid;

select cname,tname,avg(score) from sc

inner join course on sc.cid=course.Cid

inner join teacher on course.tid=teacher.Tid

group by course.cid;

2.2 外联查询

（1）主查表

-- 查询出主表所有信息

-- 所有学生的数据和对应的班级信息主查学生

-- left join on 左外联 主查表在join左

select * from student

left join class on student.classid=class.classid;

-- right join on 右外联 主查表在join右

select * from class

right join student on student.classid=class.classid;

-- 查询出所有学生都学过多少门课程学生姓名课程数 *含null

-- 没有班级的学生绿色

select * from student

left join class on student.classid=class.classid

where class.classid is null;

-- 没有学生的班级橙色

select * from student  

right join class on class.classid= student.classid

where student.sid is null;

select * from class  

left join student on class.classid= student.classid

where student.sid is null

（2） union 两个结果集的并集

-- 去除重复 distinct一样

-- 不同类型的字段是可以合并的

-- 不同列数量的结果集是不允许合并的

-- 取别名给第一个结果集才有用

-- 库中的所有人的名字

select sname,ssex from student

UNION

select tname,tsex from teacher;

select sname,ssex,classid from student

UNION

select tname,tsex,temail from teacher;

select sname 姓名,ssex 性别,classid from student

UNION

select tname,tsex,temail from teacher;

-- 获取没有学生的班级和没有班级的学生紫色

select * from student

left join class on student.classid=class.classid

where class.classid is null

union

select * from student  

right join class on class.classid= student.classid

where student.sid is null

-- 获取没有班级的学生和班级和学生都有的还要获取没有学生的班级 -- 绿紫橙

1）全连接去重

select * from student

left join class on student.classid=class.classid

union

select * from student  

right join class on class.classid=student.classid

2）不去重的并集 union all

select * from student

left join class on student.classid=class.classid

union all

select * from student  

right join class on class.classid= student.classid

2.3 子查询（内部查询）

（1）where子查询 效率低

-- 查询id最大的一名同学

select * from student order by student.sid desc limit 0,1;

-- 查询id最大的一名同学(子查询）

select max(sid) from student;

select * from student where sid =16; -- 魔术

select * from student where sid =(select max(sid) from student);

-- 查询每个班下id最大的同学（子查询）

select max(sid) from student

group by student.classid;

select * from student

where sid in(select max(sid) from student group by student.classid);

select * from student,class

where sid in(select max(sid) from student group by student.classid) and student.classid=class.classid;

select * from student

left join class on student.classid=class.classid

where sid in(select max(sid) from student group by student.classid);

select * from student

inner join class on student.classid=class.classid

where sid in(select max(sid) from student group by student.classid);

-- 查询学过张三老师课程的学生

select * from student where sid in          -- in 查询多条记录

    (select sid from sc where cid=

       (select cid from course where tid=

           (select tid from teacher where tname='张三')

       )

    );

-- 查询大于5人的班级名称和人数（不适用子查询）

select classname,count(*) from class

left join student on class.classid=student.classid

group by class.classid

having count(*) > 5 ;

（2）from子查询

-- 查询大于5人的班级名称和人数(子查询）

select classname,人数 from class left join

(select classid,count(*) 人数 from student group by classid) t1 -- t1是别名

on class.classid=t1.classid

where 人数>5;

（3）exists 子查询子句有结果，父句执行，子句没结果，父句不执行

select * from teacher

where exists (select * from student where classid=1);

（4）any，some，all

1）any

-- 查询出一班成绩比二班最低成绩高的学生

select distinct student.* from sc

left join student on sc.sid=student.Sid

where student.classid=1 and score > any(

select min(score) from sc

left join student on sc.sid=student.sid

where student.classid=2)

2）some

select  DISTINCT student.* from sc

left join student on sc.sid = student.sid

where student.classid = 1 and score > some (

    select score from sc

left join student on sc.sid = student.sid

where student.classid = 2

)

3）all

-- 查询出一班成绩比二班最高成绩高的学生

select  DISTINCT student.* from sc

left join student on sc.sid = student.sid

where student.classid = 1 and score > (

    select max(score) from sc

left join student on sc.sid = student.sid

where student.classid = 2

)

select  DISTINCT student.* from sc

left join student on sc.sid = student.sid

where student.classid = 1 and (score > 70.0 and score > 60.0

and score >80.0 and score >50.0 and score > 30.0 and score > 20.0

and score > 31.0 and score > 34.0)

select distinct student.* from sc

left join student on sc.sid=student.Sid

where student.classid=1 and score > all(

select min(score) from sc

left join student on sc.sid=student.sid

where student.classid=2)

2.4 结果集控制语句

-- IF(expr1,expr2,expr3)

-- expr1 条件

-- expr2 条件成立显示数据

-- expr3 条件不成立显示数据

select * from teacher;

-- 1 女

-- 0 男

select tid,tname,if(tsex=1,'女','男') tsex,tbirthday,taddress from teacher;

-- IFNULL(expr1,expr2)

-- expr1 字段

-- expr2 当字段为null 默认值

select sid,sname,ifnull(birthday,'这个学生没有生日，很可怜') bir,ssex from student;

2.5 case when then end语句

-- 没有在选项的为null

select tid,tname,

case tsex

    when 0 then'男'

    when 1 then'女'

end tsex,tbirthday,taddress from teacher;

select tid,tname,

case tsex

    when 0 then '男'

    when 1 then '女'

    else '保密'

end tsex,tbirthday,taddress from teacher;

select tid,tname,

case

    when tsex >1 then '男'

    when tsex =1 then '女'

    when tsex <1 then '保密'

end tsex,tbirthday,taddress from teacher;

-- 查询学生成绩

select score,

case

    when score >=90 then 'A'

    when score >=80 then 'B'

    when score >=70 then 'C'

    when score >=60 then 'D'

    when score <60 then '不及格'

end

from sc;

select sname,score,

case

    when score >=90 then 'A'

    when score >=80 then 'B'

    when score >=70 then 'C'

    when score >=60 then 'D'

    when score <60 then '不及格'

end

from sc

inner join student on sc.sid=student.sid;

-- 面试题

-- 行专列列转行

-- 统计各分数段人数

（1）

-- 分数段人数

-- 100-90

-- 90-80

-- 80-70

-- 70-60

-- 不及格

select '100-90' 分数段,count(*) 人数 from sc where score <=100 and score >=90

union

select '90-80', count(*) from sc where score <90 and score >=80

union

select '80-70', count(*) from sc where score <80 and score >=70

union

select '70-60', count(*) from sc where score <70 and score >=60

union

select '不及格', count(*) from sc where score <60;

（2）

-- 分数段 100-90 90-80 80-70 70-60 不及格

-- 人数

select '人数' 分数段,
	count(case when score>=90 and score<=100 then score end) '100-90',
	count(case when score>=80 then score end) '90-80',
	count(case when score>=70 then score end) '80-70',
	count(case when score>=60 then score end) '70-60',
	count(case when score<60 then score end) '不及格'
from sc;