数据库多表查询

约束条件

• 查询条件之having

  # having 的功能和where一样
  # where 在分组之前用， where中写的条件必须在表中存在
  # having 在分组之后使用
  
  
  eg:
      # 查询每个部门中大于30岁的平均工资，并且，保留中平均工资在10000以上的。
      # 第一步： 查询得到每个部门中大于30岁的的平均工资
      	select avg(salary) from emp where age > 30 group by post;
      # 第二步： 在筛选平均工资在10000以上的
      	select avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
  

• 查询条件之distinct

  # 这个意思是去重
  # 去重的前提条件，数据必须一模一样，数据中有主键去重就没有意义
  
  eg:
     # 对年龄去重
     		 select distinct age from emp;
  

• 查询条件之order by

  select * from emp order by salary;  # 默认是升序排序
  select * from emp order by salary desc;  # desc是降序排序，esc是升序排序，esc默认的，可以不写
  select * from emp order by age, salary desc; 
  # 上述sql语句的意思是 先对年龄按照升序排序，然后对那些年龄一样的在按照工资降序排序
  
  eg:
      # 查询每个部门中大于30岁的平均工资，并且，保留中平均工资在10000以上的, 按照平均工资降序排列 
      	select avg(salary) from emp where age > 30 group by post having avg(salary) > 10000 order by avg(salary) desc;
  
  

• 查询条件是limit

  # 分页人， 限制数据
  select * from emp limit 5; # 拿前五条数据
  select * from emp limit 5, 5; # 从第五条开始往后拿五条
  
  eg：
  	# 查询工资最高的员工
      	select max(salary) from emp;
          select * from emp order by salary desc limit 1;
  

多表查询

• 子查询

  1 .子查询就是一个sql语句的执行结果当作另外一个sql语句查询的条件
  
  eg:
      # 查询tom所在的部门
      	select post from emp where id = (select id from emp where name='tom');
  

• 联表查询

  连表查询就是把多个表拼接成一张表，当成单表查询
  
  # inner join 内连接
  	select * from emp inner join dep on emp.dep_id = dep.id;
  # left join 左连接  左连接 以左表为基表， 查询出左表的所有数据，右表的数据用null填充
  	select * from emp left join dep on emp.dep_id=dep.id;
  # right join 右连接  以右表为基表，查询出右表的所有数据，左表的数据用null填充
  	select * from emp right join dep on emp.dep_id=dep.id;
  # 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、 查询所有的课程的名称以及对应的任课老师姓名
  2、 查询平均成绩大于八十分的同学的姓名和平均成绩
  3、 查询没有报李平老师课的学生姓名
  4、 查询没有同时选修物理课程和体育课程的学生姓名
  5、 查询挂科超过两门(包括两门)的学生姓名和班级
  
  
  # 1. 
  	select cname, tname from course inner join teacher on course.cid=teacher.tid;
      
      
  # 2. 
  	# 第一步 拿到平均成绩大于80的学生id
      	select student_id, avg(num) from score group by student_id having avg(num) > 80;
      # 最后
      	select sname, avg_num from student inner join (select student_id, avg(num) as avg_num from score group by student_id having avg(num) > 80) as t1 on student.sid=t1.student_id;
          
          
   # 3.
  	# 第一步 拿到报了李平老师的课
      	select cname from course where teacher_id=(select tid from teacher where tname='李平老师');
      # 第二步 找报了这两门课程的学生
      	select student_id from score inner join (select cid from course where teacher_id=(select tid from teacher where tname='李平老师')) as t1 on score.course_id=t1.cid;
          # 或
          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 student_id from score inner join (select cid from course where teacher_id=(select tid from teacher where tname='李平老师')) as t1 on score.course_id=t1.cid);
          
          
    # 4  
  	# 第一步 先拿到 选修物理课程或体育课程的学生 id
      	select student_id from score inner join (select cid from course  where cname='物理' or cname='体育') t1 on score.course_id=t1.cid;
      # 第二步  拿到同时选修了物理和体育的学生
      	select student_id, count(student_id) from score inner join (select cid from course  where cname='物理' or cname='体育') t1 on score.course_id=t1.cid group by student_id having count(student_id)=2;
      # 最后  取反
      	select sname from student where sid not in (select student_id from score inner join (select cid from course  where cname='物理' or cname='体育') t1 on score.course_id=t1.cid group by student_id having count(student_id)=2);
          
          
    # 5 
  	# 第一步：先拿到挂科超过两门的课程的学生id
      	select student_id from score where num < 60 group by student_id having count(student_id) >=2;
      # 最后 
      	select sname, caption from class inner join (select sname, class_id from student where sid=(select student_id from score where num < 60 group by student_id having count(student_id) >=2)) as t1 on class.cid=t1.class_id;