MySQL基础之多表连接与多表查询

内容概要

• 查询关键字
• 多表查询的思想
• 子查询
• 连表操作
• Navicat可视化软件
• 鼠标点点点完成数据库的操作(内部还是SQL语句)
• 多表查询练习题(重点)

内容详细

查询关键字

表准备

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
    筛选数据

查询关键字之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;

查询关键字之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
'''

聚合函数

分组之后频繁需要使用的
　　　　max     最大值
　　　　min     最小值　　　　sum     求和　　　　count   计数　　　　avg     平均值

查询关键字之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
    ;

查询关键字之distinct(去重)

去重有一个非常严格的前提条件 数据必须是完全一样
    如果数据带有主键name肯定无法去重
    select distinct age from emp;

查询关键字之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);

查询关键字之limit(分页)

用来限制数据的展示条数
select * from emp limit 5;# 前五条
select * from emp limit 5,5; # 起始位置、条数

# 查询工资最高的人的详细信息
    # 1 先按照工资排序，然后限制展示条数
    select * from emp order by salary desc limit 1;

查询关键字之regexp正则

正则表达式
    用一些特殊符号的组合去字符串中筛选出符合条件的数据
    
select * from emp where name regexp '^j.*(n|y)$';
# '^j.*(n|y)$'  j开头 中间无所谓 n或者y结尾

多表查询思想

1 子查询
    分步解决问题
    将一条SQL语句的查询结果用括号括起来，当做另一条SQL语句的查询条件
    
2 连表操作
    先将所有需要用到的表拼接到一起(一张表)
    然后就是转换成单表查询

前期表准备

#建表
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);

子查询

# 查询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');

连表操作

# 前戏(了解)
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;

Navicat可视化软件

可以充当很多数据库软件的客户端 封装了很多快捷方法

该软件默认也是收费的 需要破解
正版不破解免费试用14天
破解版(老版本):https://pan.baidu.com/s/1bpo5mqj

1.下载与安装
2.使用方法
	创建库 表 记录
    	注意主键
     外键字段
    逆向数据库到模型
    转储SQL文件
  	查询

多表查询练习题

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 );