查询关键字 多表查询及练习题 可视化软件navicat
day 48
having与where的功能是一模一样的 都是对数据进行筛选
两者差异：
| where | having |
| 用于过滤数据行 | 用于过滤分组 |
| 在查询条件中不可以使用聚合函数 | 在查询条件中可以使用聚合函数 |
| 在数据分组前进行过滤 | 在数据分组后进行过滤 |
| 针对 数据库文件进行过滤（直接过滤表中字段） | 针对查询结果进行过滤（根据前面已经查询出的字段） |
| 不可以使用字段别名 | 可以使用字段别名 |
代码实操：
# 统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门
'''编写SQL语句 不要指望着一步到位 边写边看慢慢拼凑'''
# 1.先获取每个部门年龄在30岁以上的员工的平均薪资
先筛选出30岁以上的员工数据 然后再对数据进行分组
select post , avg(salary) from emp where age>30 group by post;
# 2.再过滤出平均薪资大于10000的数据
需要针对分组之后的数据再次筛选 需要使用having而不是where
select post avg(salary) from emp
where age > 30
group by post
having avg(salary) > 10000
;
改进篇
针对聚合函数 如果还需要在其他地方作为条件使用 可以先起别名
select post avg(salary) as avg_salary from emp
where age>30
group by post
having avg_salary > 10000
;
排序
asc 升序关键字（从小到大） 可以省略
desc 降序关键字 （从大到小）
# 3.统计各部门年龄在10岁以上的员工平均工资 并且保留平均工资大于1000的部门并按照降序进行排序
select post,avg(salary) as avg_salary from emp
where age > 10
group by post
having avg_salary > 1000
oerder by avg_salary desc;
limit关键字主要用于指定查询结果从哪条记录开始显示，一共显示多少条记录
记录数表示需要显示的数据条数 如果“记录数”的值小于查询结果的总数，则会从第一条记录开始，显示指定条数的记录。如果“记录数”的值大于查询结果的总数，则会直接显示查询出来的所有记录。
一般第一个参数（初始位置）从0开始 两个参数必须是正整数
代码实操：
^ 表示字符串的开头$ 表示字符串的结尾
| 表示''或者''的意思
# 1. 查找表中首字母 j 开头的 n或者y结尾的 中间字符不限的全部姓名
select * from emp where name regexp'^j.*(n|y)$';
create table dep(
id int primary key auto_increment,
name varchar(32)
);
create table emp(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others') default 'male',
age int,
dep_id int
);
insert into dep values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营'),(205,'安保');
insert into emp(name,age,dep_id) values('jason',18,200),('tony',28,201),('oscar',38,201),('jerry',29,202),('kevin',39,203),('jack',48,204);# 使用子查询 获取jason所在的部门名称
# 1.先获取jason的部门编号
select dep_id from emp where name='jason';
# 2.将结果加括号作为查询条件 通过jason部门编号获取到部门名称
select name from dep where id=(select dep_id from emp where name='jason');
# 使用连表操作 获取jason所在的部门名称
使用笛卡尔积
select * from emp,dep; # 会将所有的数据全部对应一遍
select * from emp,dep where emp.dep_id=dep.id; # 将emp表中dep_id和dep表中id两者对应的数据显示出来 效率较低
1. 一条sql语句的查询结果，可以看作是一张虚拟表
2. 如果一条sql语句中涉及到多张表的字段名称编写 建议使用表名作为前缀便于区分 比如多个表内都包含id字段 如果不加前缀 就会报错
连表操作关键字
inner join 内连接
select * from emp inner join dep on emp.dep_id=dep.id;
只链接两张表内共有的数据
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 union dep on emp.dep_id=dep.id;
左右两表数据全部展示 没有对应项则用NULL填充
答案
select dep.name from emp inner join dep on ep.dep_id=dep.id
where emp.name='jason';
学了连表操作 免不了优化一下
思路：之前我们把一个取值操作起别名，这两表拼接不得把拼接后的结果起个别名当作一张表来使用？又能去和另一张表去做两两拼接啦
select * from emp inner join (select emp.id as epd,emp.name,dep.id from emp inner join dep on emp.dep_id=dep.id) as t1
on emp.id=t1.epd;
Navicat可以充当很多数据库软件的客户端 提供了图形化界面能够让我们更加快速的操作数据库
# 下载
navicat有很多版本 并且默认都是收费使用
正版可以免费体验14天
针对这种图形化软件 版本越新越好(不同版本图标颜色不一样 但是主题功能是一样的)
# 使用
内部封装了SQL语句 用户只需要鼠标点点点就可以快速操作
连接数据库 创建库和表 录入数据 操作数据
外键 SQL文件 逆向数据库到模型 查询(自己写SQL语句)
# 使用navicat编写SQL 如果自动补全语句 那么关键字都会变大写
SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?)
#
--
# 运行SQL文件
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级
-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要几张表 course表 teacher表
# 2.大致查找一些表中的数据情况
# 3.既然是多表查询 那么查询思路 子查询 连表操作(复杂的SQL需要两者配合使用)
# 4.编写完成后 使用美化功能 将SQL语句规范化
-- SELECT
-- course.cname,
-- teacher.tname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先明确需要用到几张表 student score
# 2.大致查看一下两张表里面的数据
# 3.先获取平均成绩大于80分的学生信息(按照student_id分组)
-- select score.student_id,avg(num) as avg_num from score group by score.student_id having avg_num>80;
# 4.结果需要从两个表里面的获取 student SQL语句执行之后的虚拟表
-- SELECT
-- student.sname,
-- t1.avg_num
-- FROM
-- student
-- INNER JOIN ( SELECT 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、查询没有报李平老师课的学生姓名
# 此题有两种思路 第一种是正向查询 第二种是反向查询(先查所有报了李平老师课程的学生id 之后取反即可)
# 1.先明确需要用到几张表 四张表
# 2.先查询李平老师的编号
-- select tid from teacher where tname='李平老师'
# 3.再查李平老师教授的课程编号
-- select cid from course where teacher_id=(select tid from teacher where tname='李平老师')
# 4.根据课程编号 去score表中筛选出所有选了课程的学生编号
-- select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));
# 5.根据学生编号去学生表中反向筛选出没有报李平老师课程的学生姓名
-- 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 = '李平老师' ) ) )
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(两门都选了和一门都没选的 都不要 只要选了一门)
# 1.先明确需要用到几张表 三张
# 2.先获取物理课程和体育课程的编号
-- select cid from course where cname in ('物理','体育');
# 3.再去分数表中筛选出选了物理和体育的数据(包含了选了一门和两门 没有选的就已经被排除了)
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 4.如何剔除选了两门的数据(按照学生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;
# 5.根据上述学生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.先明确需要几张表 三张表
# 2.先去score表中筛选出所有不及格的数据
-- select * from score where num < 60;
# 3.如何筛选每个学生挂科的门数(按照学生id分组 对学科计数即可)
-- select student_id from score where num < 60 group by student_id
-- HAVING count(course_id) >= 2;
# 4.由于最终的结果需要取自两张表 所以应该拼接
-- select student.sname,class.caption from class inner join student on class.cid=student.class_id;
# 5.使用步骤3获取到的学生编号 对步骤4的表结果筛选数据
SELECT
student.sname,
class.caption
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( course_id ) >= 2 );
"""
重点掌握上述五道题目即可 如果还想扩展 可以考虑下面的题目
https://www.cnblogs.com/Dominic-Ji/p/10875493.html
