进阶1笔记的补充

冷不防 2024-03-26 18:18 86阅读 0赞

# 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)