MySQL单表查询与多表查询

一 单表查询

表准备

create table emp(
  id int not null unique 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);


# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;

#如果在插入中文的时候还是会出现乱码或者空白的现象 可以将字符编码统一设置成GBK

1、单表查询的语法

select 字段1，字段2... from 表名
			where 条件
			group by 字段名 #分组
			having 条件 #过滤
			order by 字段名 #排序
			linmit;

select distinct 字段名 from 表名; #去重

2、关键字的执行优先级(重点)

#关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

​ 具体执行步骤：

1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数

1）where 筛选条件

​ 作用：是对整体数据的一个筛选操作

# 1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name from emp where id between 3 and 6;  两者等价

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);#支持成员运算，等同上面

# 3.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
	like
		%  匹配任意多个字符
		_  匹配任意单个字符
"""
select name,salary from emp where name like '%o%';

# 4.查询员工姓名是由四个字符组成的 姓名和薪资  char_length()   _
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;

# 5.查询id小于3或者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;

2）group by分组

将表数据按照字段进行分组，分组后的表数据是按照组来查找的

大前提：

​ 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数。

分组实际应用场景 ：
男女比例
部门平均薪资
部门秃头率
国家之间数据统计

# 1	按照部门分组
select * from emp group by post;

具体如下所示：

分组之后 最小可操作单位应该是组 ，而上述命令返回的是分组之后 每个组的第一条数据。但是这不符合分组的规范，且查询结果也不是每个组的数据。

修改模式：

set global sql_mode = 'strict_trans_tables,only_full_group_by';
'''
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。
'''

结果展示：

再次查找测试：

select * from emp group by post; #ERROR 1055 (42000): 'day48.emp.id' isn't in GROUP BY

结果如下所示：

注：分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)

设置ONLY_FULL_GROUP_BY之后, 分组 默认只能拿到分组的依据；按照什么分组就只能拿到分组字段，其他字段不能直接获取 需要借助于一些方法(聚合函数)。

什么时候需要分组：
如查询有中关键字 ：
每个 平均 最高 最低

聚合函数:
		max
		min
		sum
		count
		avg

# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
# as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱

# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
# 5.获取每个部门的人数
select post,count(id) from emp group by post;  # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post;  #null不行

# 6.查询分组之后的部门名称和每个部门下所有的员工姓名 
# group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# concat不分组的时候用 
select concat('NAME:',name),concat('SAL:',salary) from emp;

# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;  
select emp.id,emp.name from emp as t1;   #报错
select t1.id,t1.name from emp as t1;

# 查询每个人的年薪  12薪
select name,salary*12 from emp;

分组注意事项

# 关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000; #错误，where 筛选条件不能使用聚合函数

select max(salary) from emp;  # 不分组 默认整体就是一组

# 统计各部门年龄在30岁以上的员工平均薪资
	1 先求所有年龄大于30岁的员工
    	select * from emp where age>30;
    2 再对结果进行分组
     	select * from emp where age>30 group by post;
    
    select post,avg(salary) from emp where age>30 group by post;

3）having过滤

having是group by分组之后的筛选条件，having的语法根where是一致的；只不过having是在分组之后进行的过滤操作，即having是跟在group by 之后的，可以直接使用聚合函数的。

# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp 
		where age>30 
    	group by post
        having avg(salary) > 10000
        ;

4）distinct 去重

​ 注：一定要注意 必须是完全一样的数据才可以去重！！！，一定不要将自增的id忽视了， 有自增id存在的情况下是不可能去重的。

select distinct id,age from emp;
select distinct age from emp;

5）order by 排序

select * from emp order by salary; #升序
select * from emp order by salary asc; #升序
select * from emp order by salary desc; #降序
"""
order by默认是升序  asc 该asc可以省略不写
也可以修改为降序     desc
"""
select * from emp order by age desc,salary asc;
# 先按照age降序排  如果碰到age相同 则再按照salary升序排

# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
	select post,avg(salary) from emp 
		where age>10 
    	group by post
        having avg(salary) > 1000
        order by avg(salary) desc
        ;

6）limit 限制展示条数

​ 当表记录过多时，可以采用固定条数展示

select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3;  # 只展示三条数据

select * from emp limit 0,5;
select * from emp limit 5,5;
#第一个参数是起始位置
#第二个参数是展示条数

7）正则

select * from emp where name regexp '^j.*(n|y)$';

二 多表查询

多表查询就两种方式：

​ 1）先拼接表再查询

​ 2）子查询，将一张表查询结果作为另一张表查询条件

前期表准备

#建表
create table dep(
id int,
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);

1、连表查询

select * from dep,emp;  # 结果   笛卡尔积
select * from emp,dep where emp.dep_id = dep.id;#拼表查询

具体如下图所示：

1）inner join 内连接

​ 只拼接两张表中公有的数据部分

# inner join  内连接
select * from emp inner join dep on emp.dep_id = dep.id;

具体如下图所示：

2）left join 左连接

​ 左表所有的数据都展示出来，没有对应的项的就用 NULL

# left join   左连接
select * from emp left join dep on emp.dep_id = dep.id;

具体如下图所示：

3）right join 右连接

右表所有的数据都展示出来 没有对应的项就用NULL

# right join  右连接
select * from emp right join dep on emp.dep_id = dep.id;

具体如下图所示：

4）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;

具体如下图所示：

2、子查询

​ 子查询：将一个查询语句的结果当做另外一个查询语句的条件去用

"""
子查询就是我们平时解决问题的思路
	分步骤解决问题
		第一步
		第二步
		...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
		1 先获取部门的id号
    2 再去员工表里面筛选出对应的员工
    select id from dep where name='技术' or name = '人力资源';
    
    select name from emp where dep_id in (200,201);
    
    select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');

总结

​ 表的查询结果可以作为其他表的查询条件，也可以通过起别名的方式把它作为一张虚拟表跟其他表关联。

练习

	1. 查询岗位名以及岗位包含的所有员工名字
#select dep.name,group_concat(emp.name) from dep inner join emp on emp.dep_id= dep.id group by dep.name;

select post,group_concat(name) from emp group by post;

	2. 查询岗位名以及各岗位内包含的员工个数
#select dep.name, count(emp.name) from dep inner join emp on emp.dep_id=dep.id group by dep.name;

select post, count(name) from emp group by post;
 
	3. 查询公司内男员工和女员工的个数
select sex, count(name)  from emp  where sex='male' union
select sex, count(name)  from emp where sex='female';

	4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
  
	5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;

	6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post; 
  
	7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp where sex='male' union
select sex,avg(salary) from emp where sex='female';