MySQL基础（五）
查询关键字
1.1 --- having过滤
1.2---distinct去重
1.3 ---order by排序
1.4 ---limit分页
1.5 ---regexp正则
多表查询思路
1.1---子查询
1.2---连表操作
可视化软件之Navicat
1.1---下载
1.2---使用
1.3---运行sql文件
查询关键字之having（过滤）
having与where的功能是一模一样的 都是对数据进行筛选 # where用在分组之前的筛选 # havng用在分组之后的数据再进行一次针对性的筛选 为了更好的区分 所以将where说成筛选 havng说成过滤 eg: 统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门 # 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; # 3.针对聚合函数，如果还需要在其它地方作为条件使用，可以先起别名 select post,avg(salary) as avg_salary from emp where age>30 group by post having avg_salary > 10000;
查询关键字之distinct(去重)
# 去重的前提，数据必须是一模一样的才可以，只要有一个不一样，都不能算是重复的数据（如果数据有主键肯定无法去重） select distinct age from emp; # 在django orm中，数据都会被封装成对象，所以主键很容易被忽略，从而导致去重没有效果
查询关键字之order by(排序)
# 1.按照薪资高低排序 select * from emp order by salary; # 默认是升序(从小到大) select * from emp order by salary asc; # 关键字asc 可以省略 select * from emp order by salary desc; # 降序(从大到小) # 2.先按照年龄升序排序，如果年龄相同，则再按照薪资降序排序 select * from emp orfer 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;
查询关键字之limit（分页）
# 分页就是限制展示的条数 # 1.限制只展示五条数据 select * from emp limit 5; # 2.分页效果 select * from emp limit 5,5; 当limit只有一个参数的时候，表示的是只展示几条 当limit有两个参数的时候，第一个参数表示的是起始位置，第二个参数表示从起始位置开始往后展示的条数 # 查询工资最高的人的详细信息 select * from emp order by salary desc limit 1; # 当数据特别多的时候 经常使用limit来限制展示条数 节省资源 防止系统崩溃
查询关键字之regexp（正则）
# 在编程中，只要看到reg开头的，基本上都是跟正则相关 emp表中获取j开头的以n或y结尾的名字： select * from emp where name regexp '^j.*(n|y)$';
多表查询之子查询
# 1.子查询 就相当于是我们日常生活中解决问题的方式(一步步解决) 将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件 eg:以员工表和部门表为例 查询jason所在的部门名称 子查询的步骤 1.先查jason所在的部门编号 2.根据部门编号去部门表中查找部门名称
多表查询之连表操作
2.连表操作 2.连表操作 先将多张表拼接到一起 形成一张大表 然后基于单表查询获取数据 eg:以员工表和部门表为例 查询jason所在的部门名称 连表操作 1.先将员工表和部门表按照某个字段拼接到一起 2.基于单表查询 select * from emp,dep; # 左表一条记录与右表所有记录都对应一遍笛卡尔积 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据 1、内连接：只取两张表有对应关系的记录(inner join) select * from emp inner join dep on emp.dep_id = dep.id; select * from emp inner join dep on emp.dep_id = dep.id where dep.name = 技术; 2、左连接: 在内连接的基础上保留左表没有对应关系的记录(left join) select * from emp left join dep on emp.dep_id = dep.id; 3、右连接: 在内连接的基础上保留右表没有对应关系的记录(right join) select * from emp right join dep on emp.dep_id = dep.id; 4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录(union) 只要将左连接和右连接的sql语句，加一个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;
实际演练
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.将结果加括号作为查询条件 select name from dep where id=(select dep_id from emp where name='jason'); # 使用连表操作 获取jason所在的部门名称 1.一条SQL语句的查询结果 我们也可以看成是一张虚拟表 2.如果一条SQL语句中设计到多张表的字段名称编写 建议使用表名前缀做区分 select dep.name from emp inner join dep on emp.dep_id=dep.id where emp.name='jason' ;
可视化软件之Navicat
Navicat可以充当很多数据库软件的客户端 提供了图形化界面能够让我们更加快速的操作数据库 # 下载 navicat有很多版本 并且默认都是收费使用 正版可以免费体验14天 针对这种图形化软件 版本越新越好(不同版本图标颜色不一样 但是主题功能是一样的) # 使用 内部封装了SQL语句 用户只需要鼠标点点点就可以快速操作 连接数据库 创建库和表 录入数据 操作数据 外键 SQL文件 逆向数据库到模型 查询(自己写SQL语句) # 使用navicat编写SQL 如果自动补全语句 那么关键字都会变大写 SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?) # -- # 运行SQL文件
多表查询练习题
1、查询所有的课程的名称以及对应的任课老师姓名 2、查询平均成绩大于八十分的同学的姓名和平均成绩 3、查询没有报李平老师课的学生姓名 4、查询没有同时选修物理课程和体育课程的学生姓名 5、查询挂科超过两门(包括两门)的学生姓名和班级 -- 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; -- 2、查询平均成绩大于八十分的同学的姓名和平均成绩 # 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; -- 3、查询没有报李平老师课的学生姓名 # 此题有两种思路 第一种是正向查询 第二种是反向查询(先查所有报了李平老师课程的学生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 = '李平老师' ) ) ) -- 4、查询没有同时选修物理课程和体育课程的学生姓名(两门都选了和一门都没选的 都不要 只要选了一门) # 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 -- ) -- 5、查询挂科超过两门(包括两门)的学生姓名和班级 # 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 只需要完成三分之一及以上即可!!!