-- 子查询: 出现在Where子句中的Select语句被称为子查询(subquery)
-- 三种类型的子查询: (NOT) IN, θ-Some / θ-All, (Not) Exists

-- 表达式 [not] in (子查询)
-- 示例: 列出张三, 王三同学的所有信息 
Select Sname from Student 
  where Sname in ('SanZhang', 'SanWang');
-- 列出选修了001号课程的学生的学号和姓名
-- 不用子查询
select SC.`S#`, Student.Sname from SC, Student 
  where `C#` in ('001') and SC.`S#`=Student.`S#`;
-- 使用子查询( 先找出选了001课程的学号, 然后再从Student表中找学号对应的姓名 )
select `S#`, `Sname` from Student 
  where `S#` in (select `S#` from SC where `C#`='001');
-- 求既学过001号课程又学过002号课程的学生学号
select `S#` from SC 
  where `C#`='001' and
    `S#` in (select `S#` from SC where `C#`='002');
-- 列出没有学过李明老师讲授课程的所有同学的姓名
-- 我写的
select distinct Sname from Student S, SC, Course C 
  where S.`S#`=SC.`S#` and SC.`C#`=C.`C#` and C.`T#` not in 
    (select `T#` from Teacher where Tname='MingLi');
-- PPT给的(小问题, 选出来的人有重复的, 应该加上distinct)(起别名, 简洁)
select Sname from Student 
  where `S#` not in (select `S#` from SC, Course C, Teacher T 
    where T.Tname='MingLi' and SC.`C#`=C.`C#` and T.`T#`=C.`T#`);
-- 查询嵌套, 分外层查询和内层查询
-- 内层查询与外层查询无关, 为非相关子查询, 有关就是相关子查询(需要用到外层查询的相关变量)

-- 相关子查询
-- 示例: 求学过001号课程的同学的姓名
select Sname from Student Stud 
  where `S#` in (select `S#` from SC where `S#`=Stud.`S#` and `C#`='001');
-- 外层可以给内层传递参数, 反之不行, 也称为变量的作用域原则

-- θ-Some / θ-All 子查询
-- 基本语法:
-- 表达式 θ-Some (子查询)
-- 表达式 θ-All (子查询)
-- θ是比较运算符: <, >, >=, <=, =, <>
-- 示例: 找出工资最低的教师的姓名
select Tname from Teacher
  where Salary <= all(select Salary from Teacher);

-- 找出001号课成绩不是最高的所有学生的学号
-- 我写的
select `S#` from SC 
  where `C#`='001' and Score < some(select Score from SC);
-- ppt的
select `S#` from SC
  where`C#`='001' and  Score < some(select Score from SC where `C#`='001');
-- 找出所有课程都不及格的学生的姓名(相关子查询)
select Sname from Student 
  where 60 > all(select Score from SC where `S#`=Student.`S#`);
-- 人工查验, 这条语句是不对的, 所有课程都不及格应该只有一个人, 结果却是有过挂科的
-- +----------+------+-------+
-- | S#       | C#   | Score |
-- +----------+------+-------+
-- | 98030101 | 001  |    92 |
-- | 98030101 | 002  |    85 |
-- | 98030101 | 003  |    88 |
-- | 98040202 | 002  |    90 |
-- | 98040202 | 003  |    80 |
-- | 98040202 | 001  |    55 |
-- | 98040203 | 003  |    56 |
-- | 98030102 | 001  |    54 |
-- | 98030102 | 002  |    85 |
-- | 98030102 | 003  |    48 |
-- +----------+------+-------+
-- +----------+----------+------+------+------+--------+-------+------+
-- | S#       | Sname    | Ssex | Sage | D#   | Sclass | Saddr | PID  |
-- +----------+----------+------+------+------+--------+-------+------+
-- | 98030203 | WuWang   | fe   |   19 | 04   | 980402 | NULL  | NULL |

-- 找出001号课成绩最高的所有学生的学号
select `S#` from SC where `C#`='001' and Score >= all(select Score from SC where `C#`='001');
-- 找出98030101号同学成绩最低的课程号
select `C#` from SC 
  where `S#`='98030101' and Score <= all(select Score from SC where `S#`='98030101');
-- 找出张三同学成绩最低的课程号
-- 涉及相关子查询
select `C#` from SC, Student S  
  where S.`S#`=SC.`S#` 
    and S.`Sname`='SanZhang' 
      and Score<=all(select Score from SC where S.`S#` = `S#`);

-- 等价性变换
-- 如下两种表达相同
-- 表达式 = some (子查询)
-- 表达式 in (子查询)
-- 但是 not in 与 <>some 是不等价的
-- 与 not in等价的是 表达式 <> all

-- (NOT) EXISTS 子查询
-- 基本语法
-- [not] exists (子查询)
-- 语义: 子查询结果中有无元组的存在(!!!选的是元组)
-- 示例: 检索选修了赵三老师主讲课程的所有同学的姓名(exists选出符合条件的元组, 而后再从中选择需要的字段)
select distinct Sname from Student 
  where exists( select * from SC, Course, Teacher where 
    SC.`C#`=Course.`C#` and SC.`S#`=Student.`S#` 
      and Course.`T#`=Teacher.`T#` and Tname='SanZhao');

-- 检索学过001号教师主讲的所有课程的所有同学的姓名
-- (似乎有问题.....)(表示难以理解....)
select Sname from Student
  where not exists  -- 不存在
    (select * from Course where Course.`T#`='001' and not exists  -- 有一门001教师的课程, 没学过
      (select * from SC where `S#`=Student.`S#` and `C#`=Course.`C#`));
-- 列出没学过李明老师讲授的任何一门课程的所有同学的姓名
select Sname from Student
  where not exists 
    (select * from Course, SC, Teacher 
      where Tname='MingLi' 
        and Course.`T#`=Teacher.`T#` 
          and Course.`C#`=SC.`C#` and `S#`=Student.`S#`);
-- 列出至少学过98030101号同学学过所有课程的同学的学号
Select distinct `S#` from SC SC1 
  where not exists                                                          -- 不存在
    (select * from SC SC2 where SC2.`S#`='98030101' and                     -- 有一门课该同学没学过
      not exists (select * from SC where `C#`=SC2.`C#` and `S#`=SC1.`S#`));

-- 结果计算
-- 示例: 求有差额(差额>0)的任意两位教师的薪水差额
select T1.Tname as TR1 , T2.Tname as TR2, T1.Salary-T2.Salary from Teacher T1, Teacher T2 
  where T1.Salary > T2.Salary;
-- 示例: 依据学生年龄求学生的出生年份, 当前是2015年
select Sname, 2015-Sage+1 as Syear from Student;

-- 聚集函数
-- SQL提供了5个作用在简单列值集合上的内置聚集函数agfunc, 分别是:
-- count sum avg max min
-- 示例: 求教师的工资总和
select sum(Salary) from Teacher;
-- 求计算机系教师的工资总和
select sum(Salary) from Teacher as T, Dept as D  
  where T.`D#`=D.`D#` and D.Dname='computer';
-- 求数据库课的平均成绩
select avg(Score) from SC, Course 
  where Cname='database' and Course.`C#`=SC.`C#`;

-- 分组聚集和分组过滤(分组是SQL的亮点)
-- group by 分组条件
-- 示例: 求每个学生的平均成绩(以每个学生为一个组)
select `S#`, avg(Score) from SC group by `S#`;
-- 求每一门课程的平均成绩
select C.Cname, avg(SC.Score) avg from SC, Course C 
  where SC.`C#`=C.`C#` group by SC.`C#`;
-- 求不及格课程超过两门的同学的学号
select `S#` from SC where Score<60 and count(*)>2 group by `S#`;  -- 错误示范
-- 上面是错误的, 因为Score<60选出来的是元组, 而count(*) 是对于某一列的判断, 所以是不正确的(后面会讲解正确的操作)

-- 分组过滤
-- having子句, 无分组, 不过滤. 该子句的前提是有group的支持
-- 示例: 求不及格课程超过两门的同学的学号
select `S#` from SC where Score<60 group by `S#` having count(*)>2;
-- having 表示对元组进行统计, 统计需要满足要求是大于2
-- 示例: 求有1人以上不及格的课程号
select `C#` from SC where Score<60 group by `C#` having count(*)>1;

--having 和where子句表达条件的区别
-- 每一行都要检查where子句, having是对分组进行检查, having前提要有group by子句

-- 示例: 求两门以上不及格课程同学的学号及其平均成绩
select `S#`, avg(Score) from SC where Score < 60 group by `S#` having count(*)>1;
-- 这条语句求的是不及格成绩的平均成绩, 我们一般要这个学生的成绩的平均 所以不正确
-- 正解(先选出不及格成绩大于1的同学的学号, 然后再对这些学号分组, 然后求平均, 这样就不会有歧义了)
select `S#`, avg(Score) from SC  
    where `S#` in  (select `S#` from SC where Score<60 group by `S#` having count(*)>1) 
        group by `S#`;

-- SQL语言: 并运算 union, 交运算 intersect, 差运算 except
-- 基本语法:
-- 子查询 {union [ALL] | intersect [ALL] | except [ALL] 子查询}
-- 通常情况下自动删除重复元组: 不带ALL. 若要保留重复的元祖, 则要带ALL
-- 示例:
-- 学过002号课的同学或学过003号课的同学学号
select `S#` from SC where `C#`='002' union 
select `S#` from SC where `C#`='003';
-- 也可以这样
select distinct `S#` from SC where `C#`='002' or `C#`='003';
-- 当表不一样时, or就不好使了, 就得用union

-- 求既学过002号课, 又学过003号课的同学学号(mysql 不支持 该 关键字)
select `S#` from SC where `C#`='002' intersect
select `S#` from SC where `C#`='003';
-- 也可以不用intersect来进行(intersect 并不是唯一可选的, 所以增加了sql语言的不唯一性)
select `S#` from SC 
  where `C#`='002' and `S#` in (select `S#` from SC where `C#` = '003');

-- 假定所有学生都有选课, 求没学过002号课程的学生的学号
-- 错误示范(一个学生只要有两条选课记录, 就必然包含在该合集里面)
select `S#` from SC where `C#` <> '002';
-- 可以写成这样(mysql中也没有except关键字)
select distinct `S#` from SC except select `S#` from SC where `C#` = '002';
-- 也可以这样
select distinct `S#` from SC SC1 
  where not exists  (select * from SC where `C#`='002' and `S#` = SC1.`S#`);
-- 这说明except也并没有增强sql的表达能力了,
-- 没有except, sql也可以用其他的方式表达同样的查询需求, 表达更简单, 但是增加了sql语言的不唯一性.

-- 空值处理
-- 示例: 找出年龄为空的学生的姓名
select Sname from Student where Sage is null;
-- mysql中空值的计算
-- count(*)包含null项, count(具体列名) 忽略null项; count(null)为0
-- avg, max, min, sum对null计算时全部忽略, 如果AVG(null)... 那么值为0
-- group by 对于null, 会单独将其作为一项置于顶部, distinct类似

-- 内连接, 外连接
-- 连接类型(四选一)
-- inner join
-- left outer join
-- right outer join
-- full outer join
-- 连接条件(三选一)
-- natural
-- on <连接条件>
-- using (col1, col2, ..., coln)
-- 错误示例: 求所有教师的任课情况并按教师号排序(没有任课的教师也需列在表中)
select Teacher.`T#`, Tname, Cname from Teacher 
  inner join Course on Teacher.`T#`=Course.`T#` order by Teacher.`T#`;
-- 上面丢失了一些老师的信息
-- 应该使用左外连接(因为要保留没有任课的老师也在列表之中)
select Teacher.`T#`, Tname, Cname from Teacher
  left outer join Course on Teacher.`T#`=Course.`T#` order by Teacher.`T#`;

-- 视图
-- 示例: 定义一个视图CompStud为计算机系的学生, 通过该视图可以将Student表中其他系的学生屏蔽掉
create view CompStud as
  (select * from Student where `D#` in 
    (select `D#` from Dept where Dname='computer'));
-- 示例: 定义一个视图Teach为教师任课的情况, 把Teacher表中的个人隐私方面的信息, 如工资等屏蔽掉, 仅反映其教哪门课及学分等
create view Teach 
  as ( select T.Tname, C.Cname, Cred from Teacher T, Course C where T.`T#`=C.`T#`);
-- 使用视图的时候就像使用table一样的去使用

-- 对视图的更新(注意哪些视图是可更新的, 哪些是不可更新的)
-- 如果视图Select目标列包含聚集函数, 
-- Select子句使用unique或distinct,
-- 包含group by子句,
-- 包括算术表达式计算出来的列,
-- 由单个表的列构成, 但并没有包括主键, 则都 不能更新

-- 对于由单一Table子集构成的视图, 即如果视图是从单个基本表使用选择, 投影操作导出的, 并且包含了基本表的主键, 则可以更新

-- 撤销视图
drop view view_name;
-- 示例: 撤销视图 Teach
drop view Teach;

数据库系统学习笔记