多表查询过滤分页去重排序
目录
数据库基础四之多表查询
一.查询关键字
1.1 关键字之having过滤
1.having与where的区别
having与where的功能是一模一样的 都是对数据进行筛选
where用在分组之前的筛选
havng用在分组之后的筛选
2.练习
#统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门
select post,avg(salary) as avg_salary from emp
where age>30
group by post
having avg_salary > 10000
;
1.2 查询关键字distinct去重
# 去重的前提 数据必须是一模一样的才可以(如果数据有主键肯定无法去重)
1.关键字 distinct # 去除数据的重复
select distinct age from emp;
'''django orm中数据会被封装成对象会容易忽略主键从而失去去重效果'''
1.3 关键字order by排序
1.关键字order by ... asc # 升序 可以省略asc
2.关键字order by ... desc# 降序
# 一般只有在分组之后
eg:
按照薪资排序
select * from emp order by salary; # 默认是升序(从小到大)
select * from emp order by salary desc; # 降序(从大到小)
先按照年龄升序排序 如果年龄相同 则再按照薪资降序排序
select * from emp order by age asc,salary desc;
统计各部门年龄在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;
1.4 查询关键字limint分页
1.限制只展示五条数据
select * from emp limit 5;
2.分页效果
select * from emp limit 5,5;
3.查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
'''对于数据比较多的时候用limit来限制展示的条数，节省资源'''
1.5 查询关键字regexp正则
select * from emp where name regexp '^j.*(n|y)$'; # 获取名字以j开头的以n或者y结尾的
二.多表查询以及navicat
2.1 多表查询思路
1.子查询
分步获取最后慢慢到目标
2.连表操作
将多张表链接在一起然后就是基于单表查询
eg:
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);
# 使用子查询
1.先获取jsaon的部门编号
select dep_id from emp where name='jason';
2.将结果加括号作为查询条件
select name from dep where id=(select dep_id from emp where name='jason');
# 使用链表操查询
select dep.name from emp
inner join dep on emp.dep_id=dep.id
where emp.name='jason'
;
# 关键字
# inner join 内连接 eg:
select * from emp inner join dep on
emp.dep_id=dep.id;
'''以对应关系链接表'''
# left join 左连接 eg:
select * from emp left join dep on emp.dep_id=dep.id;
'''以左表为基准 展示所有的数据 没有就为NULL'''
# right join 左连接 eg:
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;
'没有对应的数据以null填充'
# 多张表连在一起的思路为先将两张表连在一起组合成一张表的结果再将与其他表相连就可实现多张表链接
eg:
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;
2.2 可视化软件之navicat
1.下载
找到破解版下载
2.使用
内部封装了SQL语句 用户只需要鼠标点点点就可以快速操作
连接数据库 创建库和表 录入数据 操作数据
外键 SQL文件 逆向数据库到模型 查询(自己写SQL语句)
# 使用navicat编写SQL 如果自动补全语句 那么关键字都会变大写
SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?)
3.对于建好的数据
可以直接运行sql文件库和表就会出来
2.3 多表查询练习题
1、查询所有的课程的名称以及对应的任课老师姓名
select course.cname,teacher.tname from course inner join teacher on course.teacher_id=teacher.tid;
4、查询平均成绩大于八十分的同学的姓名和平均成绩
select student.sname,student_score.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 student_score on student.sid=student_score.student_id;
7、查询没有报李平老师课的学生姓名
select student.sname from student left join (select distinct score.student_id from score inner join (select cid from course where teacher_id in (select tid from teacher where tname='李平老师')) as course_teacher on score.course_id=course_teacher.cid) as stu_id on student.sid=stu_id.student_id where stu_id.student_id is null;
8、查询没有同时选修物理课程和体育课程的学生姓名
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、查询挂科超过两门(包括两门)的学生姓名和班级
select t3.sname,class.caption from class INNER JOIN (select class_id,sname from student where sid in (select student_id from score where num<60 group by student_id having count(num)>=2)) as t3 on class.cid=t3.class_id;
浙公网安备 33010602011771号