查询关键字 多表查询思路 可视化软件navicat 多表练习查询题
2022.5.6 查询关键字补充、多表查询思路、可视化软件navicat
- 查询关键字
- 多表查询思路
- 可视化软件navicat
- 多表练习查询题
一、查询关键字
- 查询关键字之having过滤
- 查询关键字之distinct去重
- 查询关键字之order by排序
- 查询关键字之limit分页
- 查询关键字之regexp正则
1、查询关键字之having过滤
having与where的功能是一模一样的，都是对数据进行筛选；
where用在分组之前的筛选；
havng用在分组之后的筛选；
为了更好的区分 所以将where说成筛选，havng说成过滤；
# 统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门
1.先获取每个部门年龄在30岁以上的员工的平均薪资
# 获取每个部门要按部门分组，分组之前筛选30岁以上用where
select post,avg(salary) from emp where age>30 group by post;
2.在过滤出平均薪资大于10000的数据
# 分组之后筛选数据用having
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
;2、查询关键字之distinct去重
# 去重的前提，数据必须是一样的才可以（如果数据有主键肯定无法去重）
select distinct age from emp;
"""
注意！
等我们学到django orm之后，数据会被封装成对象；
那个时候主键很容易被我们忽略，从而导致去重没有效果!!!
"""3、查询关键字之order by排序
order by 字段 （asc/desc）
# 1.按照薪资高低排序
select * from emp order by salary asc; # 关键字asc，可以省略，不写默认是asc升序
select * from emp order by salary desc; # 降序(从大到小)
# 2.先按照年龄升序排序，其中如果年龄相同，则再按照薪资降序排序
select * from emp order by age asc,salary desc # 后面再加一个代表对第一次排序后重复的进行二次排序
# 3.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门并按照从大到小的顺序排序
select post,avg(salary) as avg_salary from emp
where age>10
group by post
having avg_salary>1000
order by avg_salary desc;4、查询关键字之limit分页
用于分页限制展示条数
limit 数字（限制展示5条数据）
limit 数字，数字（从第几个数字开始往后开始展示几个数字）
# 1.限制只展示五条数据
select * from emp limit 5;
# 2.分页效果
select * from emp limit 5,5; # 从第6个开始展示6条数据
# 3.查询工资最高的人的详细信息
select * from emp order by salary desc limit 1; # 先排序然后限制展示第一个
"""
当数据特别多的时候 经常使用limit来限制展示条数 节省资源 防止系统崩溃
"""5、查询关键字之regexp正则
条件>>>: 字段 regexp '正则表达式'
select * from emp where name regexp '^j.*(n|y)$' # 晒徐娜以j开头，以n或y结尾的不限长度的字段name对应的值
"""
补充说明:我们目前所讲的是MySQL查询关键字中使用频率较高的一些；
其实还有一些关键字目前无需讲解，并且SQL语句里面同样还支持流程控制语法；
可自行研究
"""二、多表查询思路
1、子查询
将一条SQL语句的查询结果 “加括号” 当作另外一条SQL语句的查询条件，即为子查询；
eg：以员工表和部门表为例 >>> 查询jason所在的部门名称
步骤>>>:
子查询
1.先在员工表查询jason所在的部门编号；
select dep_id from emp where name='jason';
2.根据部门编号去部门表中查找部门名称；
select dep_name from dep
where id=(select dep_id from emp where name='jason')
# 可见将子查询的结果放入查询条件可以进行使用2、连表操作
先将多张表拼接在一起，形成一张大表，然后基于单表查询获取数据
eg：以员工表和部门表为例 >>> 查询jason所在的部门名称
步骤>>>:
连表操作
1.先将员工表和部门表按照某个字段拼接到一起
2.基于单表查询3、代码实际演练
# 数据准备
部门表>>>:
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,'安保');
+----+-------+--------+------+--------+
| id | name | gender | age | dep_id |
+----+-------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | tony | male | 28 | 201 |
| 3 | oscar | male | 38 | 201 |
| 4 | jerry | male | 29 | 202 |
| 5 | kevin | male | 39 | 203 |
| 6 | jack | male | 48 | 204 |
+----+-------+--------+------+--------+
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);
+-----+--------------+
| id | name |
+-----+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
| 205 | 安保 |
+-----+--------------+
# 需求 >>>: 查询jason所在的部门名称
方法一：子查询
1.先在员工表查询jason所在的部门编号；
select dep_id from emp where name='jason';
2.根据部门编号去部门表中查找部门名称；
select dep_name from dep
where id=(select dep_id from emp where name='jason') # 将1的结果加括号作为查询条件
方法二：连表操作
了解概念引入：（笛卡尔积）
select * from emp,dep; # 会将所有的数据全部对应一遍，即将部门表的每个数据都匹配给员工表的每个数据，假如员工表有6条数据，部门表有5条数据，那么会匹配成30条数据
+----+-------+--------+------+--------+-----+--------------+
| id | name | gender | age | dep_id | id | name |
+----+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 1 | jason | male | 18 | 200 | 201 | 人力资源 |
| 1 | jason | male | 18 | 200 | 202 | 销售 |
| 1 | jason | male | 18 | 200 | 203 | 运营 |
| 1 | jason | male | 18 | 200 | 205 | 安保 |
| 2 | tony | male | 28 | 201 | 200 | 技术 |
| 2 | tony | male | 28 | 201 | 201 | 人力资源 |
| 2 | tony | male | 28 | 201 | 202 | 销售 |
| 2 | tony | male | 28 | 201 | 203 | 运营 |
| 2 | tony | male | 28 | 201 | 205 | 安保 |
| 3 | oscar | male | 38 | 201 | 200 | 技术 |
| 3 | oscar | male | 38 | 201 | 201 | 人力资源 |
| 3 | oscar | male | 38 | 201 | 202 | 销售 |
| 3 | oscar | male | 38 | 201 | 203 | 运营 |
| 3 | oscar | male | 38 | 201 | 205 | 安保 |
| 4 | jerry | male | 29 | 202 | 200 | 技术 |
| 4 | jerry | male | 29 | 202 | 201 | 人力资源 |
| 4 | jerry | male | 29 | 202 | 202 | 销售 |
| 4 | jerry | male | 29 | 202 | 203 | 运营 |
| 4 | jerry | male | 29 | 202 | 205 | 安保 |
| 5 | kevin | male | 39 | 203 | 200 | 技术 |
| 5 | kevin | male | 39 | 203 | 201 | 人力资源 |
| 5 | kevin | male | 39 | 203 | 202 | 销售 |
| 5 | kevin | male | 39 | 203 | 203 | 运营 |
| 5 | kevin | male | 39 | 203 | 205 | 安保 |
| 6 | jack | male | 48 | 204 | 200 | 技术 |
| 6 | jack | male | 48 | 204 | 201 | 人力资源 |
| 6 | jack | male | 48 | 204 | 202 | 销售 |
| 6 | jack | male | 48 | 204 | 203 | 运营 |
| 6 | jack | male | 48 | 204 | 205 | 安保 |
+----+-------+--------+------+--------+-----+--------------+
可见这样是不合理的，不过我们可以加上筛选条件，如下：
select * from emp,dep where emp.dep_id=dep.id;
+----+-------+--------+------+--------+-----+--------------+
| id | name | gender | age | dep_id | id | name |
+----+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | tony | male | 28 | 201 | 201 | 人力资源 |
| 3 | oscar | male | 38 | 201 | 201 | 人力资源 |
| 4 | jerry | male | 29 | 202 | 202 | 销售 |
| 5 | kevin | male | 39 | 203 | 203 | 运营 |
+----+-------+--------+------+--------+-----+--------------+
但是这样效率会比较低，因此我们可以使用连表操作！
连表操作(四种)>>>:
1.inner join 内连接
select * from emp inner join dep on emp.dep_id=dep.id;
'''只连接两张表中有对应关系的数据'''
+----+-------+--------+------+--------+-----+--------------+
| id | name | gender | age | dep_id | id | name |
+----+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | tony | male | 28 | 201 | 201 | 人力资源 |
| 3 | oscar | male | 38 | 201 | 201 | 人力资源 |
| 4 | jerry | male | 29 | 202 | 202 | 销售 |
| 5 | kevin | male | 39 | 203 | 203 | 运营 |
+----+-------+--------+------+--------+-----+--------------+
2.left join 左连接
select * from emp left join dep on emp.dep_id=dep.id;
'''以左表为基准，展示所有的数据，没有对应项则用NULL填充'''
+----+-------+--------+------+--------+------+--------------+
| id | name | gender | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | tony | male | 28 | 201 | 201 | 人力资源 |
| 3 | oscar | male | 38 | 201 | 201 | 人力资源 |
| 4 | jerry | male | 29 | 202 | 202 | 销售 |
| 5 | kevin | male | 39 | 203 | 203 | 运营 |
| 6 | jack | male | 48 | 204 | NULL | NULL |
+----+-------+--------+------+--------+------+--------------+
3.right join 右连接
select * from emp right join dep on emp.dep_id=dep.id;
'''以右表为基准 展示所有的数据 没有对应项则用NULL填充'''
+------+-------+--------+------+--------+-----+--------------+
| id | name | gender | age | dep_id | id | name |
+------+-------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | tony | male | 28 | 201 | 201 | 人力资源 |
| 3 | oscar | male | 38 | 201 | 201 | 人力资源 |
| 4 | jerry | male | 29 | 202 | 202 | 销售 |
| 5 | kevin | male | 39 | 203 | 203 | 运营 |
| NULL | NULL | NULL | NULL | NULL | 205 | 安保 |
+------+-------+--------+------+--------+-----+--------------+
4.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;
'''左右两表数据全部展示，没有对应项则用NULL填充，相当于全集'''
+------+-------+--------+------+--------+------+--------------+
| id | name | gender | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | tony | male | 28 | 201 | 201 | 人力资源 |
| 3 | oscar | male | 38 | 201 | 201 | 人力资源 |
| 4 | jerry | male | 29 | 202 | 202 | 销售 |
| 5 | kevin | male | 39 | 203 | 203 | 运营 |
| 6 | jack | male | 48 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 205 | 安保 |
+------+-------+--------+------+--------+------+--------------+
# 答案求解
最后我们回到题目上来，查询jason所在的部门名称
select dep.name from emp inner join dep on emp.dep_id=dep.id where emp.name='jason';
"""
总结>>>:
我们学会了连表操作之后 其实就可以将N多张表拼接到一起
思路:我们可以将两张表拼接之后的结果起别名当做一张表使用
然后再去跟另外一张表拼接
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;
"""三、可视化软件之Navicate
Navicat是一款可以操作很多数据库的软件，提供了图形化的界面窗口，方便我们更加快速地操作数据库；
1、下载
试用版下载地址：https://www.navicat.com.cn/download/navicat-premium
（1）navicat有很多版本，并且默认都是收费使用；
（2）正版可以免费体验14天
（3）针对这种图形化软件，版本越新越好(不同版本图标颜色不一样 但是主题功能是一样的)；2、使用
使用鼠标点击的方式即可进行操作...
1.连接数据库 创建库和表 录入数据 操作数据
2.外键
创建表时或者右键进入设计表吗添加或修改外键
3.SQL文件
创建新库>>>运行SQL文件>>>导入SQL文件
4.逆向数据库到模型
查询表之间的关系
5.查询(自己写SQL语句)
可以自己写SQL语句并且可以直接验证结果
# 使用navicat编写SQL 如果自动补全语句 那么关键字都会变大写
# SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?)
# 注释方式：
1.#
2.--四、多表查询练习题
表关系如下：
按照表的关系进行下列题目的作答
1、查询所有的课程的名称以及对应的任课老师姓名
select course.cname,teacher.tname from course INNER JOIN teacher on course.teacher_id=teacher.tid;
4、查询平均成绩大于八十分的同学的姓名和平均成绩
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 a on student.sid=a.student_id;
7、查询没有报李平老师课的学生姓名
select student.sname from student where student.sid not in (select student_id from score inner join (select cid from course inner join teacher on course.teacher_id=teacher.tid where teacher.tname='李平老师') as t1 on score.course_id=t1.cid);
8、查询没有同时选修物理课程和体育课程的学生姓名
select sname from student where student.sid in (select student_id from score inner join (select cid from course where course.cname in ('体育','物理')) as t1 on score.course_id=t1.cid group by student_id having count(course_id)=1);
9、查询挂科超过两门(包括两门)的学生姓名和班级
select sname,caption from student inner join class on student.class_id=class.cid where student.sid in (select student_id from score where num<60 group by student_id having count(course_id)>=2);
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
