MySQL单表查询、聚合函数、多表连接查询、子查询

文章目录

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

单表查询

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

聚合函数

# 聚合函数:聚合函数只能在分组之后使用,聚合的是组的内容,若是没有分组,则默认一组

# 关键字====>每个 平均 最高 最低
# 聚合函数
max 最大
min 最小
sum
#  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同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
# 多表合成一张虚拟表  然后根据单表查询  可以用 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
;

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

;

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)

逻辑梳理图

作业

作业详细连接