数据库07——条件查询和多表查询

数据库07——条件查询和多表查询

查询条件之——having

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

# 查询每个部门中大于30岁的平均工资，并且，保留中平均工资在10000以上的。
1. 过滤大于30岁的
select * from  dep where age >30;
2. 在大于30岁的这群人中，在分组得到每一个部门
select  post ,avg(salary) from emp where age>30 grounp by post;
3. 在分组结果的基础上，过滤掉平均工资小于10000的部门
select post ,avg(salary) as avg_salary from emp where age>30 group by post having avg(salary)>10000;

查询条件之——distinct

# 去重的前提条件：数据必须一毛一样（多个数据是完全一样的）
如果说在数据中有主键，去重有意义？
# 去重年龄
select dictinct age from emp;

查询条件之order by

select * from emp order by salary; #
select * from  emp  order by  salary asc; #升序，可以不指定，建议指定
select * from emp order by salary desc;
# 排序还可以指定多个字段进行排序
select *from emp order by age, salary desc ;
    
# 查询每个部门中大于30岁的平均工资，并且，保留中平均工资在10000以上的, 按照平均工资降序排列
select post, avg(salary) as 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;  # 限制前5条数据
select *from emp limit 5, 5;  # 从第5条开始，查询5条数据

# 查询工资最高的员工
select max(salary) from emp;
select * from emp order by salary desc, age asc limit 1;

多表查询

1. 子查询,  一个SQL语句的结果作为另外一个SQL的条件
# 查询egon所在的部门
1） 查询egon所在部门的id
select dep_id from emp where name='egon';
2） 在拿着dep_id取dep表中查询部门名称
	select * from dep where id = (select dep_id from emp where name='egon');
    
    
2. 连表查询, 将多个表拼接成一张表，当成单表查询
select *from emp,dep;
select * from emp,dep where emp.dep_id=dep.id;

# 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、 查询所有的课程的名称以及对应的任课老师姓名
4、 查询平均成绩大于八十分的同学的姓名和平均成绩
7、 查询没有报李平老师课的学生姓名
8、 查询没有同时选修物理课程和体育课程的学生姓名
9、 查询挂科超过两门(包括两门)的学生姓名和班级

#####################关键字习惯都用大写###############################
# 建议:在书写SQL语句的时候一定不要想着一次性成功 写一点看一点再写一点  慢慢拼凑起来
-- 1、 查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要的表	course表 teacher表
-- 	select * from course;
-- 	select * from teacher;
# 2.连表操作 明确字段
-- SELECT
-- 	course.cname,
-- 	teacher.tname
-- FROM
-- 	course
-- 	INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、 查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先查看成绩表 
-- 	select * from score;
# 2.求所有学生的平均成绩
-- 	select score.student_id,avg(num) from score group by score.student_id;
# 3.筛选出大于80分
-- 	select score.student_id,avg(num) as 'avg_num' from score group by score.student_id having avg(num)>80
-- 	;
# 4.学生表与上述查询出来的表连接
-- SELECT
-- 	student.sname,
-- 	t1.avg_num 
-- FROM
-- 	student
-- 	INNER JOIN ( SELECT score.student_id, avg( num ) AS 'avg_num' FROM score GROUP BY score.student_id HAVING avg( num )> 80 ) AS t1 ON student.sid = t1.student_id;
-- 7、 查询没有报李平老师课的学生姓名
# 1.正向思路:课下可以尝试一下
# 2.反向思路:先找所有报了李平老师课程的学生 再取反
# 1.先查询李平老师教授的课程id号
-- select tid from teacher WHERE tname='李平老师';
-- select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师');
# 2.去成绩表中筛选出所有报了李平老师课程的学生id号
-- select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师')); 
# 3.去学生表中 取反获取没有报李平老师课程的学生姓名
-- 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 IN ( SELECT tid FROM teacher WHERE tname = '李平老师' )));
-- 8、 查询没有同时选修物理课程和体育课程的学生姓名(只要报了一门的 两门和都不报都不要)
# 1.先查询物理 和 体育课程的id号
-- 	select cid from course where cname in ('物理','体育');
# 2.去成绩表中先筛选出所有报了课程的数据(报了一门 报了两门)
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'));
# 3.按照学生id分组 统计每个学生报了的课程数目
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- 	group by student_id
-- 	having count(course_id) = 1
-- ;
# 4.去学生表中根据id获取学生姓名
-- SELECT
-- 	sname 
-- FROM
-- 	student 
-- WHERE
-- 	sid 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) = 1);
-- 9、 查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先去成绩表中 筛选出分数小于60分的数据
-- select * from score where num<60;
# 2.按照学生id分组 然后统计个数
-- select student_id from score where num<60 group by student_id
-- 	having count(num) >= 2
-- ;
# 3.将班级表与学生表拼接起来
SELECT
	class.caption,
	student.sname 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( num ) >= 2 );