进阶1笔记的补充

# a  course（多） teacher（一）: 一门课一个老师讲，一个老师可以将多门课  (一对多，或一对一)
cid  name   tid
 1           1
 2           2
 3           3
 4           1
 # b course（一） teacher（多）:一门课多个老师讲，一个老师讲一门课 (一对多，或一对一)
 tid name  cid
 1           1
 2           2
 3           3
 4           1
 # c  score course teacher：多对多:一门课多个老师讲，一个老师讲多门课
 id sid  cid  tid
 1        1    1
 2        2    2
 3        3    1
 4        1    2
 
 
 # 作业
 
 #1.查询（“1”课程的）所有学生的学号与分数, 课程名（course）；
 select sid,score
 from sorce
 where cid=1;
 
 select sid,score,c.cname
 from sorce as so
 inner join course as c on so.cid = c.cid
 where so.cid=1;
 
 #2.查询（平均成绩大于60分的）同学的学号和平均成绩；
 select  sid,avg(score) as avg
 from sorce
 group by sid
 having avg > 60
 
 #3.查询所有同学的学号、姓名(student)、选课数、总成绩,取班级第一名的信息
 select so.sid,s.sname,count(*) as 选课数,sum(score) as 总成绩
 from sorce as so
 inner join student as s on s.sid = so.sid
 group by so.sid
 
 select so.sid,s.sname,count(*) as 选课数,sum(score) as 总成绩
 from sorce as so
 inner join student as s on s.sid = so.sid
 group by so.sid
 order by 总成绩 desc
 limit 0,1;
 
 #4.查询(姓“李”的)老师的个数；
 select count(*) as 人数
 from teacher
 where tname like '李%';
 
 #6.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
 select cid,max(score),min(score)
 from sorce
 group by cid
 
 #5.查询学过“张三”老师课的同学的学号、姓名(student)
 select so.sid,s.sname
 from sorce as so
 inner join student as s  on so.sid = s.sid
 inner join course as c on so.cid = c.cid
 inner join teacher as t  on t.tid = c.tid
 where t.tname="张红";
 
 # 子查询
 # 出现在其他语句中的select语句，称为子查询或内查询.外部的查询语句，称为主查询或外查询
 # 分类: 出现的位置
 /*
 select后面：仅仅支持标量子查询
 from后面：支持表子查询
 where或having后面：★
 		标量子查询（单行单列） √
 		列子查询  （多行单列 in） √
 		行子查询 (一行多列)
 exists后面（相关子查询）表子查询
 
 
 标量子查询（结果集只有一行一列）
 列子查询（结果集只有一列多行）
 行子查询（结果集有一行多列）
 表子查询（结果集一般为多行多列）
 */ 
# where或having 后面的
# a.标量子查询，一般搭配着单行操作符使用> < >= <= = <>
# eg1 查询employee 中 salary工资大于平均工资的员工记录
select * from employee where salary > (select avg(salary) from employee);
# eg2 查询（工资最低的）员工信息
select * from employee 
where salary = (select min(salary) from employee);

# b. 列子查询  （多行单列 in）
# eg3 查询(每个部门的最低工资的员工的）名称，部门编号和薪水的记录
select min(salary) from employee group by department_id;

select name,department_id,salary
from employee where salary in ( select min(salary) from employee group by department_id)

# 了解
select name,department_id,salary
from employee where (salary,department_id) in (select min(salary),department_id from employee group by department_id)
# eg4  面试题: 查询（每个班第一名同学的）名称和分数
# 表名： 名字(name)   班级(class)   分数(score)
         A               1           88
         B               1           90
         C               1           92
         D               2           93
         E               2           85
   
# 错误 name 对不上
select name,max(score) from 表名 group by class;

select name,score
from  表名 where score in (select max(score) from 表名 group by class)

# eg5 (1)查询（课程(sorce)和张三(student)一样的学生的）最低分
select so.cid from sorce as so inner join student as s on so.sid=s.sid where s.sname='alice';

select min(score) 
from sorce where cid in (select so.cid from sorce as so inner join student as s 
on so.sid=s.sid where s.sname='alice');

# eg6 (6)查询(平均工资高于公司平均工资)的部门有哪些?
select deparment_id 
from employee
group by deparment_id
having avg(salary) > (select avg(salary) from employee);

/*
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用> < >= <= = <>
列子查询，一般搭配着多行操作符使用in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/ 
# 列子查询： any/some、all
/*

= any/some 等同 in （10,20,30）
> >=any/some(大于最小值) 任意
> >=all (大于最大值)  所有
< <= any/some (小于最大值)
< <= all (小于最小值)  所有
*/

# eg 查询(每个部门的最低工资的员工的）名称，部门编号和薪水的记录
select name,department_id,salary
from employee where salary in ( select min(salary) from employee group by department_id)


# 查询(每个部门的最低工资）中所有工资比员工工资都低的  名称，部门编号和薪水的记录
select name,department_id,salary
from employee where salary <=all( select min(salary) from employee group by department_id)

# 查询(每个部门的最低工资的员工的）中任意工资比员工工资低的 名称，部门编号和薪水的记录
select name,department_id,salary
from employee where salary <=any( select min(salary) from employee group by department_id)