查询条件
having
# 功能与 where 一样
where 在分组之前用,where 中的条件必须在表中存在
having 在分组之后用
distinct
# 去重前提条件,数据必须一模一样
如果数据中有主键,去重就失去意义
order by
order by <字段> asc; 升序排序
order by <字段> desc; 降序排序
# oder by 默认为升序
多重排序
order by <字段>, <字段> desc;
# 必须先执行第一道排序,在第一道排序结果相同的情况下才能执行第二道排序
limit
# 分页,限制数据
select * from <表名> limit 5; # 限制前五条数据
select * from <表名> limit 5, 5; # 从第 5 条数据开始,查询 5 条数据
# 查询工资最高的员工
1.select max(salary) from emp;
2.select * emp order by salary desc, 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.连表查询,将多个表拼接成一张表,当成表查询
# 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;
Navicat 可视化软件
多表查询练习题
1、 查询所有的课程的名称以及对应的任课老师姓名
2、 查询平均成绩大于八十分的同学的姓名和平均成绩
3、 查询没有报李平老师课的学生姓名
4、 查询没有同时选修物理课程和体育课程的学生姓名
5、 查询挂科超过两门(包括两门)的学生姓名和班级
#####################关键字习惯都用大写###############################
# 建议:在书写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 );