[Mysql-DQL数据查询语句]

目录

单表查询

直接查询(不带条件)

带条件的查询

多表查询

 内联查询

外联查询

并集

结果集控制语句

子查询

where子查询

from子查询

exists子查询

---

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

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

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

！记住一个重要点：所有查询得到的是一张虚拟表

单表查询

直接查询(不带条件)

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

-- 全字段查询
select * from student;  -- 查询学生表的所有信息

-- 部分字段查询
select sname,ssex from student; -- 查询学生表的姓名和性别信息

带条件的查询

-- 带条件的查询
-- 【where 子句】

-- 查找sid为5的学生信息
select * from student where sid = 5;

-- 查找sid不为5的学生信息  <> 等价于!=
select * from student where sid <> 5;
-- 查找sid在3和6之间的
select * from student where sid between 3 and 6; 

在where后面还可以通过一些关键字来限定范围

 -- in 在某个特定的范围
3 5 7 9
-- or 会让索引失效
select * from student where sid = 3 or sid  =5 or sid = 7 or sid = 9 or sid = 200;

-- 推荐
-- in 可以使用到索引
select * from student where sid in (3,5,7,9,200,300);

-- like 模糊查询
-- 模糊符号 
-- % 任意多的任意字符
-- _ 一个任意字符 

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

select * from student where sname like '杨_'
select * from student where sname like '杨__'

-- null 
-- is  是一个什么
select * from student where birthday is null
select * from student where birthday is not null;
 

多表查询

 内联查询

-- 内联查询
-- 只有完全满足条件（主外键关系）的数据才能出现的结果
-- 非等值联查  -- 笛卡尔积  逻辑上有错误
select * from  student, class; --  查询student表，class表

-- 等值联查  内存要求大 速度快 IO小

-- 方式一
select * from  student, class where student.classid = class.classid; -- 查询出学生和班级信息 student class 

-- 方式二

-- inner join  on  内存占用小， IO高 通过第一张表的结果进行on条件匹配

-- 查询性别是男的学生班级成绩信息

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

-- 每门课程的平均成绩  显示：课程名称  代课老师姓名 平均成绩
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 

外联查询

 -- 外联查询
-- 找到主查表

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

-- left join on   左外联
select * from student left join class on student.classid = class.classid

-- right join on  右外联
select * from class right join student on student.classid = class.classid

-- 查询出所有的学生学过多少门课程 学生姓名 课程数
select sname,count(cid) from student

left join sc on student.sid = sc.sid group by student.sid

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

并集

内联和外联表能取到的数据其实都是求表的交集，那也有求并集的方式

 -- union  两个结果集的并集
-- 去除重复 distinct 一样
-- 不同类型的字段是可以合并
-- 不同列数量的结果集不允许合并
-- 起别名给第一个结果集才有用

-- 库中的所有人的名字
select tname 姓名,tsex,temail from teacher 
union 
select sname,ssex 性别,classid from student 

-- 获取没有学生的班级和没有班级的学生的数据
select * from student 
left join class on student.classid = class.classid
where class.classid is null
union 
select * from student 
right join class on student.classid = class.classid
where student.sid is null

-- 获取没有班级的学生和班级和学生都有的还要获取没有学生的班级
-- 全连接
select * from student 
left join class on student.classid = class.classid
union 
select * from student 
right join class on student.classid = class.classid

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

结果集控制语句

有的时候一些数据我们希望在产生结构的时候显示出新的定义，我们就可以要到结果集控制语句

# 结果集控制语句
-- IF(expr1,expr2,expr3)
-- expr1 条件
-- expr2 条件成立 显示数据
-- expr3 条件不成立 显示数据

-- 1女
-- 0男

-- 查询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

-- case when then end 

select tid,tname, 
case tsex
    when 0 then '男'
    when 1 then '女'
    else '保密'
end tsex ,tbirthday from teacher
 

子查询

所有的子查询必须用()括号括起来

where子查询

-- where子查询！

-- 查询id最大的一个学生
select * from student order by sid desc limit 1

-- 查询id最大的一个学生(子查询)

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

-- 查询每个班下id最大的学生（子查询）
select * from student 
left join class on student.classid = class.classid
where sid in(
    select max(sid) from student group by classid
)

-- 查询学过张三老师课程的学生
select * from student where sid in(
    select sid from sc where cid in 
    (
        select cid from course where tid in 
            (select tid from teacher where tname <> '张三')
    )
)

from子查询

-- from 子查询 查询结果将作为一张表使用！
-- 起一个表名

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

exists子查询

- exists 子查询 子句有结果，父句执行，子句没结果，父句不执行

select * from teacher 
where exists (select * from student where classid = 10)

-- any \ some   all 

-- 题：查询出一班成绩比二班最低成绩高的学生
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
)

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

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