数据库-子查询

子查询

1.联合查询

联合查询:需要查询多表数据垂直拼接到一起
关键字: union/union all

#union:合并去重复 两个的集合并集；并且会排重
#union all:联合但是不会去重
#实例:请查询所有学生的姓名性别和老师的姓名性别
create table teacher ( 
teacherId int primary key auto_increment, 
teacherName varchar(20), 
sex char(1) )
insert into teacher (teacherName,sex) VALUES 
('张三','男'), 
('李四','女'), 
('贾宝玉','男'), 
('薛宝钗','女')

2.子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中的查询。数据库引擎将子查询作为虚表执行查询操作。
子查询可作为联接语句中的一个表，也可作为选择语句中的一个值。
子查询的执行依赖于嵌套查询。顺序从最内层开始，一层一层向外执行，外层的嵌套查询可以访问内层嵌套查询的结果，相比变量方式执行效率更高，子查询还可以将多表的数据组合在一起。

#子查询: 在sql中再嵌套另外一个sql。
#分为：简单子查询，相关子查询

#简单子查询: 子级查询能够独立运行
#查询班级AAA01的所有学生
#1.关联查询
SELECT* from classinfo c join studentInfo s #inner可以省掉
on c.classId = s.classId where c.className = 'AAA01' 
#2.简单子查询实现: 学生的班级编号等于AAA01班级编号
#子查询实现:查询AAA01的所有学生

# =用于返回值是一个
select * from studentinfo 
where classId = ( select classId from classInfo where className = 'AAA01' )

#in 用于返回值有多个
select * from studentinfo where classId in ( select classId from classInfo where className = 'AAA01' )

#相关子查询: 子级查询不能独立运行，要依赖主查询的数据
#查询学生成绩信息: 学生姓名,科目名称,分数
#1.三表关联
SELECT s.name,c.courseName,e.score from studentInfo s join examInfo e on s.studentId = e.studentId join courseInfo c
on e.courseId = c.courseId
#2.相关子查询：不能独立运行的，需要依赖外部查询的输入，查询结果返回到外部
select studentId,courseId,score from examInfo;

SELECT(select name from studentInfo where studentId =e.studentId ) as 姓名, (select courseName from courseInfo where courseId = e.courseId) as 科 目,score
from examInfo e

3.比较运算符中使用子查询

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较 运算符和子查询之间插入ANY、SOME或ALL。其中等值关系可以用IN操作符（in关键字用于 where子句中用来判断查询的表达式是否在多个值的列表中。返回满足in列表中的满足 条件的记录）。

3.1 all子查询

all可以与=、>、>=、<、<=、<>结合是来使⽤，分别表示等于、⼤于、⼤于等于、 ⼩于、 ⼩于等于、不等于其中的所有数据。当所有数据都满足才是true，会返回满足所有条件的数据。 只要大于其中的所有值才会被显示。

#查询比所有男生分数高的女生信息
#方式1: 子查询
select * from myexam where sex='女' and score>(select max(score) from myexam where sex='男')
#方式2: ALL
select * from myexam
where sex='女'
and score>all(select score from myexam where sex='男')

3.2any/some子查询

#查询比某些男生成绩高的女生信息
#方式1:子查询
select * from myexam where sex='女' and score>(select min(score) from myexam where sex='男') 
#方式2: any/SOME
select * from myexam
where sex='女'
and score>any(select score from myexam where sex='男')

3.3使用exists和not exists子查询(常用,效率高)

where exist （⼦查询）如果该⼦查询有结果数据（⽆论什么数据，只要⼤于等于1⾏）， 则就为true，否则就为false 如果内层select返回true则外层select可以返回值，否则就返回空。

#查询没有参加java考试的学生信息
#select * from examInfo
#方式1: 
#1.查询哪些学生考了java 
select studentId from examinfo 
where courseId = (select courseId from courseInfo where courseName = 'java') 
#2.查询不在这些学生中的其他学生
select * from studentinfo 
where studentId not in ( select studentId from examinfo where courseId = (select courseId from courseInfo where courseName = 'java') )
#方式2: EXISTS: 如果后面查询返回了记录则exsists结果为true 
#not EXISTS:如果后面查询没有返回记录则not EXISTS 返回true 
select * from studentinfo s 
where not exists #当前查询的学生在成绩表中不存在java成绩
( select * from examinfo 
where courseId = 2 and studentId = s.studentId )