MySQL 第7章 SQL高级子查询

    SQL语法看似简单，但其深刻和复杂之处在于子查询，其魅力之处也在于子查询。子查询是指在查询中嵌套的查询，SQL支持多层子查询嵌套，可以编写非常复杂的语句来实现相应的业务功能。SQL子查询无处不在，只要可以使用表达式的地方以及可以使用表连接的语句，都可以用子查询替换。基本的子查询无处不在，只要可以使用表达式的地方以及可以使用表联接的语句，都可以用子查询替换。基本的子查询分为3类：通过IN、ANY、ALL修改的比较运算符引入的列表上操作的子查询；通过未修改的比较运算符引入且必须返回单个值的子查询；通过EXISTS引入的存在测试的子查询。

create database two20210610 default charset=utf8;
use two20210610;
CREATE TABLE `studentinfo` (
  `StudentID` char(10) DEFAULT NULL,
  `StudentName` varchar(20) DEFAULT NULL,
  `Gender` varchar(2) DEFAULT NULL,
  `Birthday` date DEFAULT NULL,
  `ClassID` int(4) DEFAULT NULL,
  `BeginYear` year(4) DEFAULT NULL,
  `Phone` varchar(11) DEFAULT NULL,
  `Province` varchar(20) DEFAULT NULL,
  `City` varchar(20) DEFAULT NULL,
  `Email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table teacher (
    id int,
    teachername VARCHAR(10),
    hiredate DATE,
    gender char

);

create table classinfo (
    id int,   # 班级编号
    classname varchar(10),
    gradeid int,   # 年级编号
    beginyear varchar(10)  # 开始年份

);

create table subject (
    id int ,
    subjectname varchar(10),
    teacherid int 
    
);

create table exam (
    id int,
    exam int,
    subjectid int,
    studentid int,
    remark varchar(10) # 备注
);

create table grade(
    id int ,
    gradename varchar(10),
    major varchar(20)
);

INSERT INTO `two20210610`.`classinfo`(`id`, `classname`, `gradeid`, `beginyear`) VALUES (2001, '20级1班', 20, '2020');
INSERT INTO `two20210610`.`classinfo`(`id`, `classname`, `gradeid`, `beginyear`) VALUES (2002, '20级2班', 20, '2020');

INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (1, 98, 101, 1, '无');
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (2, 89, 102, 1, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (3, 79, 103, 1, '');
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (4, 96, 104, 1, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (5, 85, 101, 2, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (6, 89, 102, 2, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (7, 79, 103, 2, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (8, 83, 104, 2, NULL);


INSERT INTO `two20210610`.`grade`(`id`, `gradename`, `major`) VALUES (20, '20级', '软件技术');


INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('1', '张无忌', '男', '2021-06-10', 2001, 2020, '13012340001', '河南', '许昌', 'zwj@qq.com');
INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('2', '张铁牛', '男', '2021-06-16', 2001, 2020, '13012340002', '河南', '许昌', 'ztn@qq.com');
INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('3', '林平之', '男', '2021-06-15', 2001, 2020, '13012340003', '河南', '安阳', 'lpz@qq.com');
INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('4', '令狐冲', '男', '2021-06-08', 2002, 2019, '13012340004', '河南', '濮阳', 'lhc@qq.com');
INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('5', '岳灵珊', '女', '2021-06-02', 2002, 2019, '13012340005', '河南', '南阳', 'yls@qq.com');


INSERT INTO `two20210610`.`subject`(`id`, `subjectname`, `teacherid`) VALUES (101, 'mysql', 201);
INSERT INTO `two20210610`.`subject`(`id`, `subjectname`, `teacherid`) VALUES (102, 'java基础', 201);
INSERT INTO `two20210610`.`subject`(`id`, `subjectname`, `teacherid`) VALUES (103, '面向对象', 202);
INSERT INTO `two20210610`.`subject`(`id`, `subjectname`, `teacherid`) VALUES (104, '网页设计', 202);



INSERT INTO `two20210610`.`teacher`(`id`, `teachername`, `hiredate`, `gender`) VALUES (201, 'hys', '2021-06-04', '男');
INSERT INTO `two20210610`.`teacher`(`id`, `teachername`, `hiredate`, `gender`) VALUES (202, 'dj', '2021-06-01', '女');


-- 子查询  用子查询实现，查询出学生“林平之”的同班同学  

-- 1 找到林平之的班级
    select classid from studentinfo where studentname='林平之';
-- 2 根据班级查询同班学生信息
    select * from studentinfo where classid=2001;
-- 合并   
    select * from studentinfo where classid=(select classid from studentinfo where studentname='林平之');
    
-- 查询《mysql》考试成绩刚好等于85分的学生名单
-- 1 需要查询学生名单 在studentinfo
-- 2 科目是mysql 在subject表中
-- 3 成绩为90 在exam表中subjectid studentid

select studentname from studentinfo inner join exam on studentinfo.StudentID=exam.studentid inner join subject on subject.id=exam.studentid where subject.subjectname='mysql' and exam.exam=85;

select studentname from studentinfo , exam `subject` where studentinfo.StudentID=exam.studentid and subject.id=exam.studentid and subject.subjectname='mysql' and exam.exam=85;

-- 查询《mysql》考试成绩刚好等于85分的学生名单
# 根据学生id查询学生的名单
    select studentname from studentinfo where studentid = ?
# 在成绩中找到成绩为85的学生id
    select studentid from exam where exam=85;
# 在课程表中找到mysql的学生
    select id from subject where subjectname='mysql' ;
-- 融合
    select studentname from studentinfo where id = (select studentid from exam where exam=85) and select id from subject where subjectname='mysql';


-- 更新“令狐冲”的“mysql”成绩为+5分
    
-- 找到……成绩 +5 exam id subjectid studentid
    update exam set exam = exam + 5 where subjectid = ?
-- 找到mysql课程的id subject id subjectname
    select id from subject where subjectid = "网页设计"
-- 根据名字找id teacher
    select id from teacher where teachername='dj'
    
-- 融合
    update exam set exam = exam + 5 where subjectid = (select id from subject where subjectname = "网页设计" and subject.teacherid = (select id from teacher where teachername='dj' ))

-- 删除“张无忌”的所有考试成绩
-- 1 在studentinfo表中找到张无忌的学生id
    select studentid from studentinfo where studentname="张无忌"
-- 2 根据id在exam表中删除成绩
    delete from exam where studentid=?
    
-- 合并
delete from exam where studentid= (select studentid from studentinfo where studentname="张无忌")

-- 插入的子查询 把查询的结果作为内容 插入到新的数据
insert into studentinfo (select * from studentinfo where studentid=5)


-- 查询《mysql》考试成绩刚好等于85分的学生名单    子查询
# 根据学生id查询学生的名单
    select studentname from studentinfo where studentid = ?
# 在成绩中找到成绩为85的学生id
    select studentid from exam where exam=85;
# 在课程表中找到mysql的学生
    select id from subject where subjectname='mysql' ;
-- 融合
    select studentname from studentinfo where studentid in (select studentid from exam where exam=85 and subjectid in (select id from subject where subjectname='mysql' ) );
    
    
-- 查询成绩中科目编号为102的考试成绩中是否存在不及格的学生，如果存在不及格的学生就将参加编号102考试的学生编号和成绩全部查询显示出来
-- 是否存在不及格的学生
    select studentid from exam where exam<60
-- 参加科目编号2考试的学生
    where subjectid=102
-- 学生编号和成绩全部查询显示出来
    select studentid, exam from exam
-- 合并
    select studentid,exam from exam where subjectid=102 and exists (select studentid from exam where exam<60);
    
-- 查询成绩比科目编号为“1”的这门课程的所有成绩都大的学生考试信息
-- 1 查询……学生考试信息
    select * from exam where where ?
-- 2 成绩比科目编号为“101”的这门课程的所有成绩都大
all (select exam from exam where subjectid=1)
-- 合并
select * from exam where exam > all (select exam from exam where subject=101);

                                        本章总结

● 简单子查询
■ 子查询的通常写法
● 在增删改中实现子查询
● 高级子查询
■ 使用 IN 和 NOT IN 的子查询
■ 使用 REXISTS 和 NOT EXISTS 的子查询
■ 使用 ALL,ANY/SOME 的子查询

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • 数据库(5) | MySQL SQL 语言查询
    SQL语言基础 本章，我们将会重点探讨SQL语言基础，学习用SQL进行数据库的基本数据查询操作。另外请注意本章的S...
    厲铆兄阅读 5,388评论 2赞 46
  • MySQL
    MySQL基础操作* 开启与关闭MySQL服务 登录连接MySQL 创建用户 数据类型 类型描述int整型，储存整...
    刻苦驴哝阅读 462评论 0赞 0
  • Mysql高级查询语句
    Exists子查询 Exists的特点 drop table if exists student; 意思是如果存...
    Recorder_MZou阅读 813评论 0赞 0
  • 表情管理
    表情是什么，我认为表情就是表现出来的情绪。表情可以传达很多信息。高兴了当然就笑了，难过就哭了。两者是相互影响密不可...
    Persistenc_6aea阅读 126,659评论 2赞 7
  • 2019-11-28 173
    16宿命：用概率思维提高你的胜算 以前的我是风险厌恶者，不喜欢去冒险，但是人生放弃了冒险，也就放弃了无数的可能。 ...
    yichen大刀阅读 6,147评论 0赞 4