MYSQL基础之DQL

全字段查询

 select 字段名，字段名，字段名，字段名 from student;

❌select * from student (不推荐，会影响性能)

1️⃣局部查询

❓所有查询出来的数据，是不是一张表？

所有的查询都会得到一张虚拟的表 ❗，查过的表再也不会改变 ❗

 -- 正常的对字段的别名
 select sname as '姓名' from user
 -- 对字段的别名，有三种格式
 select sname as '姓名', birthday '生日', sex 生日 from user

2️⃣添加字段

 select name, ‘援救’ 学校 from student

⭐️⭐️ ⭐️distinct去重

所有指定的字段的数据一致的情况才会进行去重

 select distinct seex from student

3️⃣带条件的查询

 select name from where sid = 5;
 select name from where sid <>5;
 select name from where sid >5;
 select name from where sid <10;
 select sname from where ssex = '女' and classid = 1
 ​
 select sname 姓名 from student where ssex = '女' and classid = 1
 ​
 select sname from student where birthday < '1990-1-1';

⭐️面试题：入职时间，日期的比较，逻辑都是倒着的！找出比1998-4-9年龄大于她的人？

 select sname from student where birthday < '1998-4-9';

4️⃣in在某个特定范围内,不使用or

因为or会让索引失效！

 -- 删选sid等于1 3 5 7 9的所有学生！
 select * from student 
 where sid in (1,3,5,7,9);

5️⃣Like模糊查询

• 模糊符号

  • %：表示任意多的任意字符

  • _(下划线)：单个字进行匹配查询

 select * from user where user_name Like '杨_'
 select * from user where user_name like '%杨'

6️⃣null的查询 is关键词

 select * from user where birthday is null
 select * from user where birthday is not null 

✅7️⃣ 聚合函数(🔥把多个值变为一个值🔥)

count()：统计个数 🌹不统计NULL

 学生表中有多少人？
 ​
 select count(字段/常量/*号) from user
 ​
 select count(id) from user; -- 主键
 //统计某个字段
 select count(1) from user;  -- 推荐使用，数字随便填写
 select count(*) from user; -- 推荐使用

max()：求最大值

min()：求最小值

sun()：求总和

avg()：求平均值

8️⃣Group by分组

 -- 查询每个班同学的个数
 select classid, count(*) from student group by classid  

9️⃣having进行分组之后进行筛选

分组之后进行筛选

 select sid"主键", sum(score) "总分", avg(score) "平均分" from  sc group by sid having avg(score) < 60

✔面试题havimg和where的区别？

having是对分组聚合后的数据进行条件筛选，而where是针对使用单个数据的。

1️⃣0️⃣Order By排序

• asc 升序（默认）

• desc 降序 （必须声明）

• 先写先排，有相同的以此类推进行排序

 select * from student order by classid asc ;

1️⃣1️⃣ limit分页

limit分页，从0 开始 （页码-1）*步长，步长，应用层解决

limit后面不支持表达式

limit 1,1 (即每页1条记录) 可称跳过1条记录，拿1条记录 或者称 拿第2页的一条记录

 select * from student limit 6,3; -- 第6页的三条数据

例子：成绩及格的总分数排名第二的学生的sid和总成绩

 select sid "id", sum(score) "总成绩" from sc group by sid having min(score) > 60 limit 1,1

1️⃣2️⃣多表查询

非等值联查(笛卡尔积) 逻辑上有错误

 select * from 表1,表2;

等值联查

🌈使用where and连接进行等值查询

 select * from 表1,表2 where 表1.字段1=表2.字段2...

例子：查询出学生和班级信息

 select * from student,class 
 where student.classid=class.classid;

⭐️⭐️⭐️例子(面试)：查询出学过张三老师课程的学生信息

 select student.*  from student,course,sc,teacher
 where student.Sid=sc.Sid
 and sc.Cid =course.Cid
 and course.Tid=teacher.Tid
 and teacher.Tname ='张三';

内联查询(返回两个表中都满足记录的条件）

只有完全满足条件（主外键关系）的数据才能出现的结果

（子表引用了父表的主键）表A中引用了表B的主键，称表A为子表，表B为主表或父表。

inner join on(内连查询)

用于将两个或多个表中的记录根据某个条件进行匹配，并返回匹配的记录。它只返回那些在两个表中都有匹配的记录

例子：五张表进行联查，使用inner join

   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.Cid = teacher.Tid

例子：每门课程的平均成绩(课程名称，代课教师姓名，平均成绩)

 select course.Cname,teacher.Tname,avg(score) from course
 inner join teacher on course.Tid=teacher.Tid
 inner join sc on sc.Cid =course.Cid
 group by course.cid;

⭐️⭐️⭐️inner join on和笛卡尔积的各适用于哪种情景？

笛卡尔积：

非常适合表多，表的数据量不大，笛卡尔积 ,io少，但是内存占用大 浪费内存，效率高✔

 select *from student ,class where student.classid = classid.classid
 and seex = '男'

inner join on:

表少，每张表的数据大，内存占用少，每次拿表都需要判断，io多 通过第一张表的结果进行on的条件匹配，符合条件的放入，不符合则不要了 节省内存，效率低❗

 select *from student inner join class on student.classid = class.classid where ssex = '男'

外联查询（知道自己，主查什么？）

主查表放=左边用左连接，放=右边用右连接，一般情况下，数据量小的表放在=的左边。

左连接Left join on查询

例子：从student表中返回所有的记录，即便在class表中没有匹配的行。

 select * from student
 left join class on student.classid=class.classid

右连接right join on查询

例子：从class表中返回所有的记录，即便在student表中没有匹配的行。

 select * from class
 right join student on student.classid=class.classid

例子：

 -- 没有班级的学生
 select * from student
 left join class on student.classid=class.classid
 where class.classid is null;
 ​
 -- 没有学生的班级
 select * from student
 right join class on student.classid=class.classid
 where student.sid is null;

例子：所有学生学了多少门课程? 学生名 课程数

 select sname, count(cid)  from student
 left join sc ON student.Sid = sc.Sid
 group by student.Sid

union 合并

• 两个结果集的并集

• 具有去除重复 和distinct一样 (不去重的并集可写 union all)

• 不同类型的字段可以合并

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

• 表头字段名称是第一个结果集的(起别名必须是给第一个结果集才有用)

例子：库中所有人的名字，给查询出的表头字段起别名

 select sname '学生' from student
 union
 select tname from teacher

• 全连接

例子: 获取没有班级的学生和没有学生的班级以及两者都有的结果

 select * from student
 left join class on student.classid=class.classid
 -- 去重
 union
 -- 不去重 union all
 select * from student
 right join class on student.classid=class.classid

1️⃣3️⃣子查询(所有子查询必须要用（）括起来)

✨where型子查询

例子：查询id最大的一个学生

 -- 查询id最大的一个学生(排序+分页)
 select * from student order by sid desc limit 1
 ​
 -- 查询id最大的一个学生(子查询)
 select * from student where sid = (select max(sid) from student)
 ​
 -- 查询学过张三老师课程的学生
 -- 将in改为not in即查没学过张三老师课程的学生
 select * from student where sid in (
     select sid from sc where cid =
     (select cid from course where Tid = 
         (select tid from teacher where Tname ='张三'))
 )

✨from型子查询(查询结果将作为一张表使用)

将内层的查询结果当成临时表，供外层sql再次查询，查询结果集可以当成表看待。临时表要使用一个别名。

例子：查询大于5人的班级名称和人数

 -- 查询大于5人的班级名称和人数 (不用子查询)
 select classname,count(*) from class
 left join student on class.classid = student.classid
 group by class.classid having count(*) > 5
 ​
 -- 查询大于5人的班级名称和人数(子查询)
 select classname, 人数 from class left join 
 (select classid,count(*) 人数 from student group by classid) t1
 on class.classid=t1.classid
 where 人数 >5

✨exists型子查询

子句有结果，父句执行，子句没结果，父句不执行

 -- 在student表中查到classid=10的数据后，才会去查teacher表内容，若查不到class=10的数据，则不会执行查teacher表内容的sql语句
 select * from teacher
 where EXISTS(select * from student where classid=10)

✨any、some(or)、all(and)子查询

any平替是some

例子：查询出一班成绩比二班最低成绩高的学生(any 子句)

 -- 思路： 1.先查2班最低成绩                   
 --       2.再查1班的所有学生信息(条件是1班且该生成绩高于从2班查出的最低成绩)
 -- 将any替换成some，查询结果一样
 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
 )

例子：查询出一班成绩比二班最高成绩高的学生(all 子句)

 select  DISTINCT student.* from sc
 left join student on sc.Sid=student.Sid
 where student.classid = 1 and score >all(
 select max(score) from sc
 left join student on sc.Sid=student.Sid
 where student.classid = 2
 )

1️⃣4️⃣流程控制函数

🎈IF(value,value1,value2)函数

-- IF(expr1,expr2,expr3) -- expr 1条件 -- expr 2条件成立，显示数据 -- expr 3条件不成立，显示数据

例子：将teacher表中tsex字段的值变为男或者女

 select * from teacher
 -- 1 女
 -- 0 男
 select Tid,Tname,if(tsex=1,'女','男')Tsex,Tbirthday,Taddress from teacher

🎈IFNULL(value1,value2)函数

-- expr1 字段 -- expr2 当字段为null 默认值2

例子：对student表中birthday字段为空的填写默认值"没有生日哦！"

 select sid,sname,IFNULL(birthday,'没有生日哦！') bir from student

🎈CASE WHEN 条件函数

-- case when then end -- when 后必须为常量(类型不限)，自带break -- 没有在选项里的查询出来的是null

例子：查询teacher表中的信息，并将tsex改为相对应的男女

 -- 类似于switch结构
 select tid,tname,
 case tsex
     when 0 then '男'
     when 1 then '女'
     else '保密'
 end tsex,Tbirthday from teacher
 ​
 -- 类似于多重if的写法
 select tid,tname,
 case
     when tsex > 1 then '男'
     when tsex = 1 then '女'
     when tsex < 1 then '未知'
 end tsex,Tbirthday 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 '100-90' 分数段,count(score) 人数 from sc where score >=90 and score<=100
 union
 select '90-70'分数段,count(score) 人数 from sc where score >=70 and score<=90
 union
 select '60-70' 分数段,count(score) 人数 from sc where score >=60 and score<=70
 union
 select '不及格' 分数段,count(score) 人数 from sc where score <60

运行结果:

---

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

运行结果: