第七章 SQL查询(三)

第七章 SQL查询(三)

一,什么是子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询
子查询在WHERE语句中的一般用法：

SELECT … FROM 表1 WHERE 字段1  比较运算符(子查询)

#编写SQL语句，查看年龄比“李三”小的学生，要求显示这些学生的信息 ？
#1.查出李三的出生日期
SELECT borndate FROM	student where studentname='李三';
#2.查出比李三出生日期大的学生
SELECT*FROM	student where borndate>'2010-05-24 08:54:41';
#3. 把第二步中的 日期  替换为sql语句
SELECT*FROM	student where borndate>(SELECT borndate FROM	student where studentname='李三');
# 比学生1的科目1 成绩低的学生信息(除了自己)
#1.学生1的2022-03-01 08:58:39 成绩
SELECT studentResult FROM result where studentNo=1 and examDate='2022-03-01 08:58:39';
#2.成绩高的学生信息(除了自己)
SELECT*FROM	result where studentNo!=1 and studentResult<80;
# 综合
SELECT*FROM	result where studentNo!=1 and studentResult<(SELECT studentResult FROM result where studentNo=1 and examDate='2022-03-01 08:58:39');

​ 将子查询和比较运算符联合使用，必须保证子查返回的值不能多于一个

首先，执行小括号中的子查询，返回的结果是所有来自子查询的结果
其次，才开始执行外围的父查询，返回查询的最终结果

-- 查找和王月同班的学生的学号和姓名
#1.王月的班级
SELECT gradeId from student where studentname='王三';
#2.班级为 .. 的 学生的学号和姓名
SELECT studentNo,studentname from student where gradeId=1;
# 合并
SELECT studentNo,studentname from student where gradeId=(SELECT gradeId from student where studentname='王三');

使用多张表的子查询

# 查询参加最近一次“html”考试成绩最高分和最低分
# 1.html的id
SELECT SubjectId from subject where SubjectName='html';
#2.html的最近一次的考试时间
SELECT max(examDate) FROM	result where subjectNo=1;
# 3.最高分,最低分
SELECT MAX(studentResult),min(studentResult) from result where
subjectNo=1 and examDate='2021-07-01 08:58:39';
# 整合
SELECT MAX(studentResult),min(studentResult) from result where
subjectNo=(SELECT SubjectId from subject where SubjectName='html') 
and examDate=(SELECT max(examDate) FROM	result 
where subjectNo=(SELECT SubjectId from subject where SubjectName='html'));

IN子查询

IN后面的子查询可以返回多条记录
常用IN替换等于（＝）的子查询

#查询“Java Logic”课程考试成绩为60分的学生名单
#1.java的id
SELECT SubjectId FROM	`subject` where SubjectName='java';
#2.根据java的id和分值查询学生的id
SELECT studentNo FROM	result WHERE subjectNo=2 and studentResult=60;
#3.根据学生id获取姓名
SELECT studentname FROM	student where studentNo=4;
# 整合
SELECT studentname FROM	student where studentNo=(
SELECT studentNo FROM	result WHERE subjectNo=
(SELECT SubjectId FROM	`subject` where SubjectName='java')
 and studentResult=60
);
-- 子查询返回不止1个值时，使用比较运算符会出错
SELECT studentname from student WHERE studentNo in(2,4);
SELECT studentname FROM	student where studentNo=(
SELECT studentNo FROM	result WHERE subjectNo in
(SELECT SubjectId FROM	`subject` where SubjectName='java')
 and studentResult=60
);

多表联查

#查询“Java Logic”课程考试成绩为60分的学生名单
#1.java的id
SELECT SubjectId FROM	`subject` where SubjectName='java';
#2.根据java的id和分值查询学生的id
SELECT studentNo FROM	result WHERE subjectNo=2 and studentResult=60;
#3.根据学生id获取姓名
SELECT studentname FROM	student where studentNo=4;
# 整合
SELECT studentname FROM	student where studentNo=(
SELECT studentNo FROM	result WHERE subjectNo=
(SELECT SubjectId FROM	`subject` where SubjectName='java')
 and studentResult=60
);
-- 子查询返回不止1个值时，使用比较运算符会出错
SELECT studentname from student WHERE studentNo in(2,4);
SELECT studentname FROM	student where studentNo=(
SELECT studentNo FROM	result WHERE subjectNo in
(SELECT SubjectId FROM	`subject` where SubjectName='java')
 and studentResult=60
);

# 查询有c++成绩的学生的学号和姓名
# 使用多表联查  student s 给表起别名
SELECT s.studentNo,s.studentname,r.studentResult from
 student s,result r where s.studentNo=r.studentNo; 
# 三张表
SELECT s.studentNo,s.studentname,r.studentResult ,sj.SubjectName from
 student s,result r,`subject` sj where 
 s.studentNo=r.studentNo and sj.SubjectId=r.subjectNo
 and sj.SubjectName='java'; 
 #查询 所有学生的考试成绩(姓名,成绩)
 SELECT s.studentname,r.studentResult FROM student s,result r 
 WHERE s.studentNo=r.studentNo; -- 两张表,一个外键
 # 查询所有的html的成绩和学生姓名,和成绩
 SELECT s.studentname,sj.SubjectName,r.studentResult FROM 
 student s,`subject` sj,result r -- 三张表
 where s.studentNo=r.studentNo and r.subjectNo=sj.SubjectId -- 两个外键关系
 and sj.SubjectName='html';-- 一个筛选条件
 #显示所有的菜品信息,(序号,名称,菜品类型,价格)
 SELECT m.id,m.`name`,t.`name`,m.price	FROM menus m,types t 
 where m.typeid=t.id;

ANY、SOME或ALL时

-- 查询比张四所有成绩都高的学生
-- all 和任何一个数字相比都要满足条件
SELECT*from result where studentResult > all(SELECT studentResult FROM result where studentNo=2);
-- any  比其中任意一个高
SELECT*from result where studentResult > any(SELECT studentResult FROM result where studentNo=2);
-- 请查询 学号是（1，2，5）的同学信息
SELECT * FROM student WHERE studentNo in(1,2,5);

EXISTS子查询

如果子查询的结果非空，即记录条数1条以上，则EXISTS （子查询）将返回真（true），否则返回假(false)
EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False

# 查询参加了java考试的学生姓名
-- 1.查出参加java考试的学生的学号
SELECT r.studentNo from result r,`subject` s 
where r.subjectNo=s.SubjectId and s.SubjectName='java';
-- 使用exists EXISTS返回值是布尔类型
# true 会查询所有，false 返回空值
SELECT studentname FROM	student where EXISTS(
SELECT r.studentNo from result r,`subject` s 
where r.subjectNo=s.SubjectId and s.SubjectName='java'
);

UNION和UNION All

UNION联合查询的语法

SELECT 字段1,字段2 FROM tableA    UNION  [ALL] 
SELECT 字段1,字段2 FROM tableB 

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行
UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型
同时，每条 SELECT 语句中的列的顺序必须相同
当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行

CREATE TABLE `stu`.`user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `phone` varchar(255) NULL,
  `sex` varchar(255) NULL,
  `age` int NULL,
  PRIMARY KEY (`id`)
); 
CREATE TABLE `stu`.`phonelist`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `phone` varchar(255) NULL,
  PRIMARY KEY (`id`)
);
-- 
INSERT INTO `stu`.`user` (`name`, `phone`, `sex`, `age`) VALUES ('张四1', '123', '男', 12),
('张五', '1234', '男', 12),
('张六', '1235', '男', 12),
('张七', '1236', '男', 12);
INSERT INTO `stu`.`phonelist` (`name`, `phone`) VALUES ('张三', '123'),('张四1', '123'),
('张五', '1234'),
('张六1', '1235'),
('张七2', '1236');
-- 查询学生表和通讯录中的所有学生姓名和联系方式
-- UNION 合并数据并去除重复
SELECT name,phone from user UNION
SELECT name,phone FROM phonelist;
-- UNION all合并数据，但不会并去除重复
SELECT name,phone from user UNION all
SELECT name,phone FROM phonelist;