python学习day43笔记

查询条件

having

# having的功能与where一样
where在分组之前用，where中写的条件必须在表中存在
having在分组之后用

查询每个部门中大于30岁的平均工资，并且保留平均工资在10000以上的
第一步：过滤大于30岁的
select * from dep where age>30;
第二步：再分组得到每个部门
select post,avg(salary) from dep where age>30 group by post;
第三步：在分组结果的基础上，过滤掉平均工资小于10000的部门
select post,avg(salary) from dep where age>30 group by post having avg(salary)>10000;

distinct

作用：去重
去重的前提条件：数据必须一模一样
如果在数据中有主键，去重没有意义

给年龄去重
select distinct age from emp;

order by

作用：排列，默认按升序排列

select * from emp order by salary;
select * from emp order by salary asc;  # 建议指定顺序
# asc是升序
# desc是降序
select * from emp order by age,salary desc;
# 先按年龄升序排列，如果年龄一样按工资降序排列
查询每个部门中大于30岁的平均工资，并且保留平均工资在10000以上的，按照平均工资降序排列0

limit

作用：分页，限制数据
# 如果查询的数据过大，mysql有个查询限制时间，如果超过这个时间没查询出来则报错
select * from emp limit 5;  # 限制前5条数据
select * from emp limit 5,5;  # 从第5条开始查询5条数据

多表查询

1.子查询  # 一个sql语句的结果作为另外一个sql语句的条件
查询egon所在的部门
第一步：查询egon所在部门的id
select dep_id emp where name='egon';
第二步：再拿着dep_id去dep表中查询部门名称
select * from dep where id=(select dep_id emp where name='egon');

2.连表查询  # 将多个表拼接成一张表，当成单表查询
select * from emp,dep;  # 会有重复数据
select * from emp,dep where dep_id=id;
# 报错ambiguous，意思是表达的模糊不清，因为两个表都有id
select * from emp,dep where emp.dep_id=dep.id;

# inner join 内连接
select * from emp inner join dep on emp.dep_id=dep.id;
# on后写条件，相当于where

# left join 左连接
select * from emp left join dep on emp.dep_id=dep.id;
# 以左表为基表，查询出左表的所有数据，右表的数据用null填充

# right join 右连接
select * from emp right join dep on emp.dep_id=dep.id;
# 以左表为基表，查询出右表的所有数据，左表的数据用null填充

# union 全连接
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;
# 将两个表拼接起来

题目

1.查询所有的课程的名称以及对应的任课老师的姓名

思路：先查看课程和老师表，然后用多表查询把两个表对应的关联起来

select cname,tname from course INNER JOIN teacher on course.teacher_id=teacher.tid

2.查询平均成绩大于80分的同学的姓名和平均成绩

思路：先把所有同学的平均成绩先算出来，然后再过滤平均成绩大于80分的，由于查询的结果本身就是一个表，所以直接多表查询与查询的结果进行连接

select s.sname,t.avg_num from student as s inner join (select student_id,avg(num) as avg_num from score group by student_id having avg(num)>80) as t on s.sid=t.student_id;

3.查询没有报李平老师课的学生姓名

思路：先查询李平老师的id，再通过id找到李平老师教授的课程，再在socre表中找到报了该课程的学生，再去student表中查询学生姓名

select tid from teacher where tname='李平老师'

select cid from course where teacher_id = (select tid from teacher where tname='李平老师')

select DISTINCT student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname='李平老师'))

select sname from student where sid not in (select DISTINCT student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname='李平老师'))
)

4.查询没有同时选修物理课程和体育课程的学生的姓名

思路：去course表中查询物理和体育课程id，然后去成绩表中查到同时选修这两门课程的学生id，然后通过not反取id找到姓名

select cid from course where cname in('物理','体育')

select * from score where course_id in(select cid from course where cname in('物理','体育')) group by student_id having count(course_id)=2

select sname from student where sid not in (select student_id from score where course_id in(select cid from course where cname in('物理','体育')) group by student_id having count(course_id)=2)