多表连接与多表查询
-- 查询关键字
--多表查询的思想
--子查询
--连表操作
-- Navicat可视化软件
--鼠标点点点完成数据库的操作(内部还是SQL语句)
--多表查询练习题(重点)
内容详细
1 查询关键字
1.1 表准备
create table emp( id int primary key auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #插入记录 #三个部门：教学，销售，运营 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部 ('tom','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tony','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jack','female',18,'20110211','teacher',9000,401,1), ('jenny','male',18,'19000301','teacher',30000,401,1), ('sank','male',48,'20101111','teacher',10000,401,1), ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('呵呵','female',38,'20101101','sale',2000.35,402,2), ('西西','female',18,'20110312','sale',1000.37,402,2), ('乐乐','female',18,'20160513','sale',3000.29,402,2), ('拉拉','female',28,'20170127','sale',4000.33,402,2), ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3); 1 select - 用来指定表的字段数据 select * from emp; select id,name from emp; ''' 在工作中很少使用*号 ---> 内容大 ''' 2 from - 后面跟需要查询的表名 3 where 筛选数据
2 查询关键字之where
# 1 查询id大于等于3小于等于6的数据 # 2 查询薪资是20000或者18000或者17000的数据 ''' 模糊查询： 关键字 ---> like 关键符号 %：匹配任意个数的任意字符 _：匹配单个个数的任意字符 ''' # 3 查询姓名中带有字母o的员工姓名和薪资 select name,salary from emp where name like '%o%'; # 4 查询姓名由四个字符组成的员工姓名和薪资 select name,salary from emp where name like'____'; select name,salary from emp where char_length(name) = 4; # 5 查询id小于3或者大于6的数据 select * from emp where id < 3 or id > 6; select * from emp where id not between 3 and 6; # 6 查询薪资不在20000,18000,17000范围的数据 select * from emp where salary not in (20000,18000,17000); # 7 查询岗位描述为空的员工名与岗位名 针对null不能用'=',只能用is select name,post from emp where post_comment = NULL; # 查询为空 select name,post from emp where post_comment is NULL; select name,post from emp where post_comment is not NULL;
3 查询关键字之group by分组
分组 ---> 将单个单个的个体按照指定的条件分成一个个整体
''' 分组之后默认只能直接获取分组的依据 其他字段无法再直接获取(可以间接获取) ''' # 严格模式 set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by' # 1 每个部门的最高薪资 select post,max(salary) from emp group by post; # 2 每个部门的最低薪资 select post,min(salary) from emp group by post; # 3 每个部门的平均薪资 select post avg(salary) from emp group by post; # 4 每个部门的人数 select post,count(id) from emp group by post; # 5 每个部门的月工资总和 select post,sum(salary) fromm emp group by post; ''' 可以给字段起别名(as还可以给表起别名) select post as'部门'，sum(salary) as '总和' from emp group by post; ''' # 6 查询分组之后的部门名称和每个部门下所有的员工姓名 ''' group_concat() ---> 获取分组以外的字段数据，并且支持拼接操作(掌握) select post,group_concat(name) from emp group by post; select post,group_concat(name,':',salary) from emp group by post; concat() ---> 未分组之前使用的拼接功能(了解) select concat(name,':',sex) from emp; concat_ws() ---> 省步骤(了解) select concat_ws(':',name,sex,salary,age) from emp '''
3.1 聚合函数
分组之后频繁需要使用的 max 最大值 min 最小值
sum 求和
count 计数
avg 平均值
4 查询关键字之having(过滤)
功能上having与where是一模一样的
但是使用位置上有有所不同
-where在分组之前使用
-having在分组之后使用
1 统计各部门年龄在30岁以上的员工平均工资，并且保留工资大于10000的部门
# 1.1 先筛选出所有30岁以上的员工 select * from emp where age >30; # 1.2 然后再按照部门分组 '''SQL语句的查询结果我们也可以直接看成是一张表''' select post,avg(salary) from emp where age >30 group by post; # 1.3 分组之后再做过滤操作 select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000 ;
5 查询关键字之distinct(去重)
去重有一个非常严格的前提条件 数据必须是完全一样
如果数据带有主键name肯定无法去重
select distinct age from emp;
6 查询关键字之order by(排序)
select * from emp order by salary; # 默认是升序 select * from emp order by salary asc; # 升序关键字(可不写) select * from emp order by salary desc; # 降序 # 排序也可以指定多个字段 select from emp order by age desc,salary asc; # 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序 select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000 order by avg(salary);
7 查询关键字之limit(分页)
用来限制数据的展示条数 select * from emp limit 5;# 前五条 select * from emp limit 5,5; # 起始位置、条数 # 查询工资最高的人的详细信息 # 1 先按照工资排序，然后限制展示条数 select * from emp order by salary desc limit 1;
8 查询关键字之regexp正则
正则表达式 用一些特殊符号的组合去字符串中筛选出符合条件的数据 select * from emp where name regexp '^j.*(n|y)$'; # '^j.*(n|y)$' j开头 中间无所谓 n或者y结尾
9 多表查询思想
1 子查询
分步解决问题
将一条SQL语句的查询结果用括号括起来，当做另一条SQL语句的查询条件
2 连表操作
先将所有需要用到的表拼接到一起(一张表)
然后就是转换成单表查询
10 前期表准备
#建表 create table dep( id int primary key auto_increment, name varchar(20) ); create table emp( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into emp(name,sex,age,dep_id) values ('jason','male',18,200), ('egon','female',48,201), ('kevin','male',18,201), ('nick','male',28,202), ('owen','male',18,203), ('jerry','female',18,204);
11 子查询
# 查询Jason所在的部门名称 # 第一步 先获取Jason所在的部门id select dep_id from emp where name='jason'; # 第二步 根据id号去部门表中筛选 select * from dep where id = 200; # 完整句式 select * from dep where id = (select dep_id from emp where name='jason');
12 连表操作
# 前戏(了解) select * from emp,dep; # 基于上表筛选数据(了解) '''为了避免字段冲突 可以在字段名前面加表明确''' select * from emp,dep where emp.dep_id = dep.id;
--------------------------------------- 掌握 -------------------------------------------
inner join --- 内连接 拼接公共的部分 select * from emp inner join dep on emp.dep_id = dep.id; left join --- 左连接 以左表为基准展示所有数据 没有的null填充 (用得少) select * from emp left join dep on emp.dep_id=dep.id; right join --- 右连接 以右表为基准展示所有数据 没有的null填充 (用得少) 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;
13 Navicat可视化软件
可以充当很多数据库软件的客户端 封装了很多快捷方法
该软件默认也是收费的 需要破解
正版不破解免费试用14天
破解版(老版本):https://pan.baidu.com/s/1bpo5mqj
1.下载与安装
2.使用方法
创建库 表 记录
注意主键
外键字段
逆向数据库到模型
转储SQL文件
查询
14 多表查询练习题
1、 查询所有的课程的名称以及对应的任课老师姓名 4、 查询平均成绩大于八十分的同学的姓名和平均成绩 7、 查询没有报李平老师课的学生姓名 8、 查询没有同时选修物理课程和体育课程的学生姓名 9、 查询挂科超过两门(包括两门)的学生姓名和班级 #####################关键字习惯都用大写#######################
# 建议:在书写SQL语句的时候一定不要想着一次性成功 写一点看一点再写一点 慢慢拼凑起来 -- 1、 查询所有的课程的名称以及对应的任课老师姓名 # 1.先明确需要的表 course表 teacher表 -- select * from course; -- select * from teacher; # 2.连表操作 明确字段 -- SELECT -- course.cname, -- teacher.tname -- FROM -- course -- INNER JOIN teacher ON course.teacher_id = teacher.tid; -- 4、 查询平均成绩大于八十分的同学的姓名和平均成绩 # 1.先查看成绩表 -- select * from score; # 2.求所有学生的平均成绩 -- select score.student_id,avg(num) from score group by score.student_id; # 3.筛选出大于80分 -- select score.student_id,avg(num) as 'avg_num' from score group by score.student_id having avg(num)>80 -- ; # 4.学生表与上述查询出来的表连接 -- SELECT -- student.sname, -- t1.avg_num -- FROM -- student -- INNER JOIN ( SELECT score.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、 查询没有报李平老师课的学生姓名 # 1.正向思路:课下可以尝试一下 # 2.反向思路:先找所有报了李平老师课程的学生 再取反 # 1.先查询李平老师教授的课程id号 -- select tid from teacher WHERE tname='李平老师'; -- select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师'); # 2.去成绩表中筛选出所有报了李平老师课程的学生id号 -- select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher WHERE tname='李平老师')); # 3.去学生表中 取反获取没有报李平老师课程的学生姓名 -- 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 IN ( SELECT tid FROM teacher WHERE tname = '李平老师' ))); -- 8、 查询没有同时选修物理课程和体育课程的学生姓名(只要报了一门的 两门和都不报都不要) # 1.先查询物理 和 体育课程的id号 -- select cid from course where cname in ('物理','体育'); # 2.去成绩表中先筛选出所有报了课程的数据(报了一门 报了两门) -- select * from score where course_id in (select cid from course where cname in ('物理','体育')); # 3.按照学生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 -- ; # 4.去学生表中根据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.先去成绩表中 筛选出分数小于60分的数据 -- select * from score where num<60; # 2.按照学生id分组 然后统计个数 -- select student_id from score where num<60 group by student_id -- having count(num) >= 2 -- ; # 3.将班级表与学生表拼接起来 SELECT class.caption, student.sname 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( num ) >= 2 );
愿君前程似锦，归来仍是少年
