mysql5

• 查询关键字之having过滤
• 查看关键字之distinct去重查询
• 查询关键字之order by排序
• 查询关键字之limit分页
• 查询关键字之regexp正则
• 多表查询思路
• 可视化软件Navicat
• 多表查询练习题

查询关键字之having过滤

having与where的功能是一模一样的都是对数据进行筛选where用在分组之前的筛选    having用在分组之后的筛选为了更好的区分 所以将where说成筛选 having说成过滤# 统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门select post 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;

查看关键字之distinct去重查询

# 去重的前提 数据必须是一模一样的才可以(如果数据有主键肯定无法去重)select distinct age from emp;"""等我们学到django orm之后 数据会被封装成对象 那个时候主键很容易被我们忽略 从而导致去重没有效果!!!"""

查询关键字之order by排序

# 1.按照薪资高低排序select * from emp order by salary;  # 默认是升序(从小到大)# 2.先按照年龄升序排序 如果年龄相同 则再按照薪资降序排序select * from emp order by age, salary DESC;# 3.统计各部门年龄在10岁以上的员工平均工资 并且保留平均工资大于1000的部门并按照从大到小的顺序排序select post,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;# 3.查询工资最高的人的详细信息select * from emp order by salary desc limit 1;"""当数据特别多的时候 经常使用limit来限制展示条数 节省资源 防止系统崩溃"""

查询关键字之regexp正则

select * from emp where name regexp '^j.*(n|y)$';"""补充说明:我们目前所讲的是MySQL查询关键字中使用频率较高的一些其实还有一些关键字目前无需讲解 并且SQL语句里面同样还支持流程控制语法如果感兴趣的话 自行百度查看"""

多表查询思路

# 多表查询的思路总共就两种1.子查询  就相当于是我们日常生活中解决问题的方式(一步步解决)    将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件    eg:以昨天的员工表和部门表为例 查询jason所在的部门名称      子查询的步骤        1.先查jason所在的部门编号          2.根据部门编号去部门表中查找部门名称            2.连表操作  先将多张表拼接到一起 形成一张大表 然后基于单表查询获取数据    eg:以昨天的员工表和部门表为例 查询jason所在的部门名称      连表操作        1.先将员工表和部门表按照某个字段拼接到一起          2.基于单表查询          # 实际演练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所在的部门名称笛卡尔积(了解知识)select * from emp,dep;  # 会讲所有的数据全部对应一遍  select * from emp,dep where emp.dep_id=dep.id;  # 效率低下"""1.一条SQL语句的查询结果 我们也可以看成是一张虚拟表2.如果一条SQL语句中设计到多张表的字段名称编写 建议使用表名前缀做区分"""连表操作有四个关键字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 left join dep on emp.dep_id=dep.idunionselect * from emp right join dep on emp.dep_id=dep.id;    '''左右两表数据全部展示 没有对应项则用NULL填充'''答案求解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 t1on emp.id=t1.epd;"""

可视化软件Navicat

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的表结果筛选数据SELECTstudent.sname,class.caption FROMclassINNER JOIN student ON class.cid = student.class_id WHEREstudent.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只需要完成三分之一及以上即可!!!"""