文章目录

• • • 单表查询
    • 聚合函数
    • 多表查询（select查询的是虚拟表）
    • 连接查询
    • 多表连接查询案例
    • 多表连接查询之物理表和虚拟表链接
    • 子查询
    • • 带in关键字的子查询
      • 带ANY关键字的子查询
      • 带ALL关键字的子查询
      • 带比较运算符的子查询
      • 带EXISTS关键字的子查询
    • 逻辑梳理图
    • 作业

单表查询

select distinct 字段1,字段2,... from 库.表
					where 过滤条件
					group by 分组字段
					having 过滤条件
					order by 排序字段
					limit 条数

聚合函数

 首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 指定表中的字段名，将指定字段名的数据进行分类
  分组的意义在于指定表中的字段，将表中数据分类，以组的形式进行数据的操作 
  可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
  
  # 聚合函数：聚合函数只能在分组之后使用，聚合的是组的内容，若是没有分组，则默认一组
    
    # 关键字====>每个 平均 最高 最低
    # 聚合函数
    max     最大
    min     最小
    sum     求和
    count   查数
    avg     求平均值
    group_concat

#  group by分组

分组实际应用场景 分组应用场景非常的多
男女比例
部门平均薪资
国家之间数据统计


#查看分组    
 mysql> select @@global.sql_mode;
+--------------------+
| @@global.sql_mode  |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
# 设置分组
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';

  
  # 建表
  create table employee(
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 employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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);
  
  
  按照部门分组
  select * from employee group by post;
 
   # 案例
   取出每个部门的平均薪资
 select post,avg(salary) from employee group by post; 
  # select 只能看到分组字段以及聚合的结果  
   

    
     # 案例
  取出每个部门的员工数
 select post,count(id) from employee group by post;

  求男人数与女人数
  select sex,count(id) from employee group by sex;


  求年龄在20岁以上的男人数与女人数
  select sex,count(id) from employee where age > 20 group by sex;

  求每个部门20岁以上人的平均薪资
  select post,avg(salary) from employee where age > 20 group by post;
     
  
  用group_concat可以看到name和age的值
  select post,group_concat(name,":",age) from employee group by post;
  
  
   聚合函数整体使用
   select post,avg(salary),max(salary),min(age),count(id),sum(age) from employee group by post;

	# 分组注意事项
    关键字where和group by同时出现的时候group by必须在where的后面 （聚合函数只能在分组之后使用）
    where先对整体数据进行过滤之后再分组操作
    where筛选条件不能使用聚合函数		
			
     select max(salary) from employee;  # 不分组 默认整体就是一组  
    
    
    
  
    
# having分组之后的筛选条件
having的语法跟where是一致的
只不过having是再分组之后进行的过滤操作
即having是可以直接使用聚合函数的   

# 	案例
查出平均薪资在10000以上的部门
select post,avg(salary) from employee group by post having avg(salary) > 10000;


查出部门内男员工平均工资在3000以上的部门
select post,avg(salary) from employee where sex="male" group by post 
having avg(salary) > 3000;




#  order by 排序
select * from employee order by age;
select * from employee order by age asc;      # 同上命令的意思一样（升序）
select * from employee order by age desc;     # 降序

order by 默认是升序（从低到高） asc 该asc可以省略不写
也可以修改为降序（从高到低） desc

# 案例
查出部门内男员工平均工资在3000以上的部门升序排序
select post,avg(salary) from employee where sex="male" group by post 
having avg(salary) > 3000
order by avg(salary);

# 先按age字段排，在按id字段排（在age分不出胜负的时候按id子段排）
select * from employee order by age asc,id desc;


# limit限制展示条数
select * from employee;  # 该命令是将所有的数据都展示出来
针对数据过多的情况，我们通常都做分页处理
select * from employee limit 3;  # 只展示emp表中的3条数据


select * from employee limit 0,5;    # 展示1-5的数据
select * from employee limit 5,5;    # 展示6-10的数据
第一个参数是起始位置
第二个参数是展示条数（分页展示）



# 取出薪资最高那个人的信息
select * from employee order by salary desc limit 1;

单表查询详解

多表查询（select查询的是虚拟表）

• 多表连接查询
• 复合条件连接查询
• 子查询

# 准备表


mysql> create database db4;
mysql> use db4;


#建部门表
create table department(
id int,
name varchar(20) 
);

#建员工表
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;


#查看表结构和数据
 desc department;

 desc employee;

 select * from department;

 select * from employee;

连接查询

#1 交叉连接：不适用任何匹配条件。生成笛卡尔积
#两张表拼接成一张大表(虚拟表)
  假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0),(b, 1), (b, 2)}

 select * from employee,department;
 
 #2 内连接：只保留有对应关系的记录 （常用）
 # inner join 将表进行拼接，找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果

 select * from employee inner join department on employee.dep_id = department.id;

 # left join:左连接	将表进行拼接，以左表为准，优先显示左表全部记录
select * from employee left join department on employee.dep_id = department.id;


# right join 右连接：将表进行拼接，以右表为准，优先显示右表全部记录
select * from employee right join department on employee.dep_id = department.id;

# 全外连接： union （了解）
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;

多表连接查询案例

#分析步骤：
1）#先站在左表的角度去找
  是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

2）#再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#foreign key的对应关系
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

# 多表合成一张虚拟表  然后根据单表查询  可以用 where group by ......

# 建作者表
create table author(
id int primary key auto_increment,
name varchar(20)
);

# 建作者书表
create table book(
id int primary key auto_increment,
name varchar(20)
);

#建关联表
create table author2book(
id int primary key auto_increment,
author_id int not null,
book_id int not null,
foreign key(author_id) references author(id) on delete cascade on update cascade,
foreign key(book_id) references book(id) on delete cascade on update cascade
);

# 插入数据
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

insert into book(name) values
('九阳神功'),
('九阴真经'),
('九阴白骨爪'),
('独孤九剑'),
('降龙十巴掌'),
('葵花宝典')
;

insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;

# 两张表拼接成一张表查询
select * from author inner join author2book 
on author.id = author2book.author_id
inner join book 
on author2book.book_id = book.id;

+----+---------+----+-----------+---------+----+-----------------+
| id | name    | id | author_id | book_id | id | name            |
+----+---------+----+-----------+---------+----+-----------------+
|  1 | egon    |  1 |         1 |       1 |  1 | 九阳神功        |
|  1 | egon    |  2 |         1 |       2 |  2 | 九阴真经        |
|  1 | egon    |  3 |         1 |       3 |  3 | 九阴白骨爪      |
|  1 | egon    |  4 |         1 |       4 |  4 | 独孤九剑        |
|  1 | egon    |  5 |         1 |       5 |  5 | 降龙十巴掌      |
|  1 | egon    |  6 |         1 |       6 |  6 | 葵花宝典        |
|  2 | alex    |  7 |         2 |       1 |  1 | 九阳神功        |
|  2 | alex    |  8 |         2 |       6 |  6 | 葵花宝典        |
|  3 | yuanhao |  9 |         3 |       4 |  4 | 独孤九剑        |
|  3 | yuanhao | 10 |         3 |       5 |  5 | 降龙十巴掌      |
|  3 | yuanhao | 11 |         3 |       6 |  6 | 葵花宝典        |
|  4 | wpq     | 12 |         4 |       1 |  1 | 九阳神功        |
+----+---------+----+-----------+---------+----+-----------------+

# 查询技术部的员工姓名
select employee.name from employee inner join department on employee.dep_id = department.id
where department.name = "技术";


# 查询平均年龄大于25岁的部门名
select department.name,avg(age) from employee inner join department on employee.dep_id = department.id
group by department.name
having avg(age) > 25
;

多表连接查询之物理表和虚拟表链接

 use db3;

 select * from  employee;

# 案例
查询每个部门最新入职的哪些员工？
# 插入数据
 insert employee values(19,"tom","male",19,"2017-03-01","老男孩驻沙河办事处外交大使 ",3000);

#查看整表
mysql> select * from employee;

#找到最大时间（最新时间）
mysql> select post,max(hire_date) from employee group by post;


select * from employee
inner join
(select post,max(hire_date) from employee group by post) as t1
on employee.post = t1.post


#as 定义别名
select employee.name,employee.hire_date,employee.post,t1.m_d from employee
inner join
(select post,max(hire_date) as m_d from employee group by post) as t1
on employee.post = t1.post		
where employee.hire_date = t1.m_d;	
+--------+------------+------------------------------------------+------------+
| name   | hire_date  | post                                     | m_d        |
+--------+------------+------------------------------------------+------------+
| egon   | 2017-03-01 | 老男孩驻沙河办事处外交大使               | 2017-03-01 |
| alex   | 2015-03-02 | teacher                                  | 2015-03-02 |
| 格格   | 2017-01-27 | sale                                     | 2017-01-27 |
| 张野   | 2016-03-11 | operation                                | 2016-03-11 |
| tom    | 2017-03-01 | 老男孩驻沙河办事处外交大使               | 2017-03-01 |
+--------+------------+------------------------------------------+------------+

子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

#案例
use db4;
mysql> select id from department where name = "技术"

# 子查询
mysql> select * from employee where dep_id = (select id from department where name = "技术"");     

#查询平均年龄在25岁以上的部门名
select dep_id from employee group by dep_id having avg(age) > 25;
   
select name from department where id in 
(select dep_id from employee group by dep_id having avg(age) > 25);


#查看不足1人的部门名(子查询得到的是有人的部门id)

mysql> select distinct dep_id from employee;   #先查有人的表


select name from department where id not in 
(select distinct dep_id from employee);


#查询大于所有人平均年龄的员工名与年龄
select avg(age) from employee; #先查所有人的平均年龄

select name,age from employee where age > 
(select avg(age) from employee);


#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from employee t1
inner join 
(select dep_id,avg(age) avg_age from employee group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 

带in关键字的子查询

#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技术');

#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理，如下

mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
| 7 | xxx | male | 19 | NULL |
+----+------------+--------+------+--------+
7 rows in set (0.00 sec)

mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)

# 子查询中存在null
mysql> select * from dep where id not in (select distinct dep_id from emp);
Empty set (0.00 sec)

# 解决方案如下
mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
+------+--------+
| id | name |
+------+--------+
| 203 | 运营 |
+------+--------+
1 row in set (0.00 sec)

！！！注意not in

带ANY关键字的子查询

#在 SQL 中 ANY 和 SOME 是同义词，SOME 的用法和功能和 ANY 一模一样。

# ANY 和 IN 运算符不同之处1
ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符放在 ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义

例如：使用 IN 和使用 ANY运算符得到的结果是一致的
select * from employee where salary = any (
select max(salary) from employee group by depart_id);

select * from employee where salary in (
select max(salary) from employee group by depart_id);

结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符

# ANY和 IN 运算符不同之处2
ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的

SELECT
*
FROM
T_Book
WHERE
FYearPublished < ANY (2001, 2003, 2005)

带ALL关键字的子查询

# all同any类似，只不过all表示的是所有，any表示任一
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all (
select avg(salary) from employee group by depart_id);
查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all (
select avg(salary) from employee group by depart_id);

查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工select * from employee where salary < any ( select avg(salary) from employee group by depart_id); 
查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any (
select avg(salary) from employee group by depart_id);

带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)


#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

逻辑梳理图

作业

作业详细连接