MySQL第四讲

最新推荐文章于 2023-04-25 12:16:16 发布

奕霖、Martin

最新推荐文章于 2023-04-25 12:16:16 发布

阅读量1.9k

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_56528635/article/details/126809232

版权

四、多表查询与子查询

1、多表查询

在sql语句中，一个查询结果就是一个结果集。

两个查询语句就是两个结果集——两个结果集可以进行并交差运算。

并集有可能有重复，两个集合中都有的交集的那一部分会重复。

使用union可以自动排重，但是效率会低

使用union all 不会自动排重，会把两个表的信息都拼接起来

案例：

-- 列出郑州和洛阳的学生信息
-- in关键字在下面的讲解中会出现
select * from studentinfo where city in('郑州','洛阳');
select * from studentinfo where city ='郑州' or city='洛阳';
-- union是合并结果集合，与表无关，只要的是最后的结果集
-- 只要保证结果集的列数相同，类型可以匹配。
select * from studentinfo where city ='郑州'
union all
select * from studentinfo where city ='洛阳';
select studentid,studentname from studentinfo
union
select classid,classname from class;

2、子查询

2.1 子查询特点

1）子查询是一个嵌套在 select、insert、update或 delete语句中的查询。

2）数据库引擎将子查询作为虚表（子查询结果为多行多列，可以看做是一张虚拟表）执行查询操作。

3）子查询可作为联接语句中的一个表，也可作为选择语句中的一个值。

4）子查询的执行依赖于嵌套查询。顺序从最内层开始，一层一层向外执行，外层的嵌套查询可以访问内层嵌套查询的结果，相比变量方式执行效率更高，子查询还可以将多表的数据组合在一起。

5）查询语句中的任何条件，值，范围，都可以使用子查询表示。

-- 创建学生表
drop table student;
select * from student;
CREATE TABLE student(
Sid VARCHAR(10), 
Sname VARCHAR(10), 
Sage datetime, 
Ssex VARCHAR(10)
);
INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO student VALUES('08' , '王菊' , '1990-01-20' , '女');
-- 创建科目表
drop table course;
select * from course;
CREATE TABLE course(
Cid VARCHAR(10), 
Cname VARCHAR(10), 
Tid VARCHAR(10)
);
INSERT INTO course VALUES('01' , '语文' , '02');
INSERT INTO course VALUES('02' , '数学' , '01');
INSERT INTO course VALUES('03' , '英语' , '03');
-- 创建教师表
drop table teacher;
select * from teacher;
CREATE TABLE teacher(
Tid VARCHAR(10), 
Tname VARCHAR(10)
);
INSERT INTO teacher VALUES('01' , '张三');
INSERT INTO teacher VALUES('02' , '李四');
INSERT INTO teacher VALUES('03' , '王五');
-- 创建学生成绩表
drop table sc;
select * from sc;
CREATE TABLE sc(
Sid VARCHAR(10), 
Cid VARCHAR(10), 
score DECIMAL(18,1)
);
INSERT INTO sc VALUES('01' , '01' , 80);
INSERT INTO sc VALUES('01' , '02' , 90);
INSERT INTO sc VALUES('01' , '03' , 99);
INSERT INTO sc VALUES('02' , '01' , 70);
INSERT INTO sc VALUES('02' , '02' , 60);
INSERT INTO sc VALUES('02' , '03' , 80);
INSERT INTO sc VALUES('03' , '01' , 80);
INSERT INTO sc VALUES('03' , '02' , 80);
INSERT INTO sc VALUES('03' , '03' , 80);
INSERT INTO sc VALUES('04' , '01' , 50);
INSERT INTO sc VALUES('04' , '02' , 30);
INSERT INTO sc VALUES('04' , '03' , 20);
INSERT INTO sc VALUES('05' , '01' , 76);
INSERT INTO sc VALUES('05' , '02' , 87);
INSERT INTO sc VALUES('06' , '01' , 31);
INSERT INTO sc VALUES('06' , '03' , 34);
INSERT INTO sc VALUES('07' , '02' , 89);
insert INTO sc VALUES('07' , '03' , 98);

2.1.1案例：

如果想要将查询的结果作为条件存在，返回的结果必须是一列或者是一个

-- 查询和赵雷的01课程的成绩相同的同学
-- 1.查询赵雷的学生sid
select sid from student where sname='赵雷'
-- 2.根据赵雷的sid查询赵雷的01成绩
select score from sc where 
sid=(select sid from student where sname='赵雷') and cid='01'
-- 3.根据赵雷的成绩查询相等成绩学生的信息
-- 这里用in而不是用=，是因为结果中可能有多行，如果用=只能显示第一行的信息
select * from student where sid in(
    select sid from sc where score=(
        select score from sc 
        where sid=(select sid from student where sname='赵雷')and         cid='01'
    ) and cid='01'
)

子查询结果为多行一列，可以当做多个值来使用

-- 查询考试不及格学生的名单
-- 1.查询考试不及格学生的sid 可以使用distinct对查询出来的数据排重
select distinct sid from sc where score<60
-- 2.通过sid查询学生的全部信息
select * from student where sid in(
select distinct sid from sc where score<60
)

2.2 关键字

1）使用in来查询考试不及格的学生名单以及成绩

-- 查询考试不及格的学生名单以及成绩
-- 1.查询考试不及格学生的sid 可以使用distinct对查询出来的数据排重
select distinct sid from sc where score<60
-- 2.通过sid查询学生的全部信息
select * from student where sid in(
select distinct sid from sc  where score<60
)
-- 3.学生的信息加上学生的成绩
select * from student s,sc sc1,course c where sc1.sid in(
    select sc2.sid from sc sc2 where sc2.score<60
)and sc1.sid=s.sid and sc1.cid=c.cid

2）使用any关键字查询成绩比'01'这门课程的某一个成绩高的学生

-- 查询成绩比'01'这门课程的某一个成绩高的学生
-- 1.查询01成绩的最小值
select min(score) from sc sc1 where cid='01'
-- 2.查询信息
-- 第一种
select sname,sid from student where sid in(
select distinct sid from sc where score>
(select min(score) from sc sc1 where cid='01')
)
-- 第二种 any
select sname,sid from student where sid in(
select distinct sid from sc where score > 
any(select score from sc sc1 where cid='01')
)

3）使用all查询成绩比“01”这门课程的任意成绩都大的学生考试信息。

-- 查询成绩比“01”这门课程的任意成绩都大的学生考试信息。 
-- 1.求出课程01的最大的成绩
SELECT max(score) from sc where cid='01'  
-- 2. 找出成绩比步骤1中结果大的学生id（sid）
select DISTINCT sid from sc where score >(SELECT max(score) from sc where cid='01'  )
-- 3. 根据sid 查询对应的学生的信息
select sname,sid from student where sid in(
    select DISTINCT sid from sc where score >(SELECT max(score) from sc where cid='01'  )
) 
-- 使用ALL查询对应的学生的信息
select sname,sid from student where sid in(
    select DISTINCT sid from sc where score > all(SELECT score from sc where cid='01'  )
)

4）练习

-- 查询没有参加数学考试的学生名单 student
-- 1.查询数学的课程的id  course
select cid from course where cname='数学'
-- 2.查询成绩表找出参加数学考试的学生的id cid=步骤1的值 代表参加考试
select sid from sc sc1 where cid = (select cid from course where cname='数学')
-- 3. 查询学生表 sid 不在步骤2中的值
select * from student where sid not in(
 select sid from sc sc1 where cid = (select cid from course where cname='数学')
)

-- 查找最低分的所有学生信息 
-- 1.查出最低分
select min(score) from sc
-- 2.根据最低分找出该学生的sid
select sid from sc where score=(select min(score) from sc)
-- 3.通过该学生的sid在学生表中找到该学生的全部信息
select * from student where sid=(
select sid from sc where score=(select min(score) from sc)

5）使⽤exists 和 not exists ⼦查询

-- 1）where  exist （⼦查询）如果该⼦查询有结果数据（⽆论什么数据，只要⼤于等于1⾏），则就为true，否则就为false
-- 2）如果内层select返回true则外层select可以返回值，否则就返回空。
-- 3）带有EXISTS谓词的子查询不返回任何实际数据，只产生逻辑真值“true”或逻辑假值“false

-- 如果存在成绩大于90的学生，那么就展示全部的学生
select * from student where exists(
select sid from sc where score>90)
-- 如果存在成绩大于90的学生，那么就不展示全部的学生
select * from student where not exists(
select sid from sc where score>90)

6）case when分类

--  学生student做分类：
--  年龄为0岁的显示0岁  
--  年龄小于18显示未成年  
--  年龄大于30岁的显示压力比较大  
--  年龄在18~30之间的显示无忧无虑
select s.*,(
     case when year(now())-year(sage)=0 then '0岁'
                when year(now())-year(sage)<18 then '未成年'
                when year(now())-year(sage)>30 then '压力比较大'
        else '无忧无虑'
end)bz from student s

7）输出学生成绩表

-- max是取每一行三个数中的最大值，另外两个为空，所以这里选用min()也可以实现
-- ifnull（） 当前面的数为null时，就用后面的数补充
-- case when 类似于Switch case 分情况进行返回结果，使用时注意格式
select sname,
    max(ifnull (case when cname='语文' then score end,0))语文,
    max(ifnull (case when cname='数学' then score end,0))数学,
    max(ifnull (case when cname='英语' then score end,0))英语
from student s,sc sc1,course c where s.sid=sc1.sid and sc1.cid=c.cid
group by s.sid

-- 输出结果
sname  语文   数学     英语
赵雷   80.0   90.0    99.0
钱电   70.0   60.0    80.0
孙风   80.0   80.0    80.0
李云   50.0   30.0    20.0
周梅   76.0   87.0    0.0
吴兰   31.0   0.0     34.0
郑竹   0.0    89.0    98.0