单表多表子查询
单表多表子查询
一、单表查询
增删改查语句
"""
增：
insert [into]
[数据库名.]表名[(字段1[, ..., 字段n])]
values
(数据1[, ..., 数据n])[, ..., (数据1[, ..., 数据n])];
删：
delete from [数据库名.]表名 [条件];
改：
updata [数据库名.]表名 set 字段1=值1[, ..., 字段n=值n] [条件];
查：
select [distinct] 字段1 [[as] 别名1],...,字段n [[as] 别名n] from [数据库名.]表名 [条件];
"""
# 条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果
# 注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置)，条件的种类可以不全
# 可以缺失，但不能乱序
-
去重 distinct
create table t1( id int, num int, x int );
mysql>: insert into t1 values(1,2,3),(2, 1, 2),(3, 2, 2),(4, 2, 2);
mysql>: select distinct * from t1; # 显示全部数据
+------+------+------+
| id | num | x |
+------+------+------+
| 1 | 2 | 3 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
| 4 | 2 | 2 |
+------+------+------+
mysql>: select distinct num, x from t1; # 结果
+------+------+
| num | x |
+------+------+
| 2 | 3 |
| 1 | 2 |
| 2 | 2 |
+------+------+
mysql>: select distinct num from t1;
+------+
| num |
+------+
| 2 |
| 1 |
mysql>:
mysql>:
<span style='color: red'>总结distinct:</span>distinct对参与查询的所有字段，整体去重（所查的数据记录全部相同，才认为是重复的数据）
2. 数据准备
``` mysql
CREATE TABLE `emp` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`gender` varchar(10),
`age` int(0) NULL DEFAULT 0,
`salary` float NULL DEFAULT 0,
`area` varchar(20) NULL,
`port` varchar(20) ,
`dep` varchar(20),
PRIMARY KEY (`id`)
)charset=utf8;
INSERT INTO `emp` VALUES
(1, 'yangsir', '男', 42, 10.5, '上海', '浦东', '教职部'),
(2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
(3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
(4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
(5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
(6, 'zero', '男', 18, 8.8, '中国', '黄浦', '咨询部'),
(7, 'owen', '男', 18, 8.8, '安徽', '宣城', '教学部'),
(8, 'jason', '男', 28, 9.8, '安徽', '巢湖', '教学部'),
(9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
(10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
(11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
(12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
(13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
(14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部'),
(15, 'ruakei', '女', 67, 2.501, '上海', '陆家嘴', '教学部');
- 常用函数
"""
拼接：concat() | concat_ws()
大小写：upper() | lower()
浮点型操作：ceil() | floor() | round()
整型：可以直接运算
"""
mysql>: select name,area,port from emp;
+---------+------+--------+
| name | area | port |
+---------+------+--------+
| yangsir | 上海 | 浦东 |
| engo | 山东 | 济南 |
| jerry | 江苏 | 张家港 |
# 拼接：concat()
mysql>: select name as 姓名, concat(area, '--', port) as 地址 from emp;
+---------+--------------+
| 姓名 | 地址 |
+---------+--------------+
| yangsir | 上海--浦东 |
| engo | 山东--济南 |
| jerry | 江苏--张家港 |
# 拼接：concat_ws()
mysql>: select name as 姓名 , concat_ws('-', area,port,dep) 信息 from emp;
+---------+--------------------+
| 姓名 | 信息 |
+---------+--------------------+
| yangsir | 上海-浦东-教职部 |
| engo | 山东-济南-教学部 |
| jerry | 江苏-张家港-教学部 |
# 大小写：upper() | lower()
mysql>: select upper(name) 姓名大写, lower(name) 姓名小写 from emp;
| 姓名大写 | 姓名小写 |
+----------+----------+
| YANGSIR | yangsir |
| ENGO | engo |
| JERRY | jerry |
# 浮点型操作：ceil() | floor() | round()
mysql>: select salary, ceil(salary) 上薪资, floor(salary) 下薪资, round(salary) 舍入 from emp;
+--------+--------+--------+------+
| salary | 上薪资 | 下薪资 | 舍入 |
+--------+--------+--------+------+
| 10.5 | 11 | 10 | 10 |
| 9.4 | 10 | 9 | 9 |
| 3 | 3 | 3 | 3 |
| 2.4 | 3 | 2 | 2 |
| 2.4 | 3 | 2 | 2 |
| 8.8 | 9 | 8 | 9 |
| 8.8 | 9 | 8 | 9 |
# 整型：可以直接运算
mysql>: select name 姓名, age 旧年龄, age+2 新年龄 from emp;
+---------+--------+--------+
| 姓名 | 旧年龄 | 新年龄 |
+---------+--------+--------+
| yangsir | 42 | 44 |
| engo | 38 | 40 |
| jerry | 30 | 32 |
| tank | 28 | 30 |
- 条件查询: where
# 多条件协调操作导入：where 奇数 [group by 部门 having 平均薪资] order by [平均]薪资 limit 1
mysql>: select * from emp where id < 15 limit 3 # 正常
mysql>: select * from emp limit 1 where id < 5; # 异常，条件乱序
# 判断规则
"""
比较符合：> | < | >= | <= | = | !=
区间符合：between 开始 and 结束 | in(自定义容器)
逻辑符合：and | or | not
相似符合：like _|%
正则符合：regexp 正则语法
"""
mysql>: select * from emp where salary>5;
mysql>: select * from emp where id%2=0;
mysql>: select * from emp where salary between 6 and 9;
mysql>: select * from emp where id in(2,3,4,6);
# _o 某o | __o 某某o | _o% 某o* (*是0~n个任意字符) | %o% *o*
mysql>: select * from emp where name like '%o%';
mysql>: select * from emp where name like '_o%';
mysql>: select * from emp where name like '__o%';
# sql只支持部分正则语法
mysql>: select * from emp where name regexp '.*\d'; # 不支持\d代表数字，认为\d就是普通字符串
mysql>: select * from emp where name regexp '.*[0-9]'; # 支持[]语法
+----+------+--------+------+--------+------+------+--------+
| id | name | gender | age | salary | area | port | dep |
+----+------+--------+------+--------+------+------+--------+
| 13 | san1 | 男 | 30 | 6 | 上海 | 浦东 | 咨询部 |
| 14 | san2 | 男 | 30 | 6 | 上海 | 浦西 | 教学部 |
+----+------+--------+------+--------+------+------+--------+
mysql>:
- 分组与筛选： group by | having
# 现象：在没有分组的情况下，where与having结果相同
# 重点：having可以对 聚合结果 进行筛选
mysql>: select * from emp where salary > 5;
mysql>: select * from emp having salary > 5;
mysql>: select * from emp where id in(12,45,3,2);
mysql>: select * from emp having id in(12,45,3,2);
- 聚合函数
"""
max(): 最大值
min(): 最小值
avg(): 平均值
sum(): 和
count(): 记数
group_concat(): 组内字段拼接，用来查看组内其他字段
"""
- 分组查询
# 修改my.ini配置重启mysql服务
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 在sql_mode没有 ONLY_FULL_GROUP_BY 限制下，可以执行，但结果没有意义
# 有 ONLY_FULL_GROUP_BY 限制，报错
mysql>: select * from emp group by dep; # 在安全模式下会出错
+----+---------+--------+------+--------+------+------+--------+
| id | name | gender | age | salary | area | port | dep |
+----+---------+--------+------+--------+------+------+--------+
| 6 | zero | 男 | 18 | 8.8 | 中国 | 黄浦 | 咨询部 |
| 2 | engo | 男 | 38 | 9.4 | 山东 | 济南 | 教学部 |
| 1 | yangsir | 男 | 42 | 10.5 | 上海 | 浦东 | 教职部 |
+----+---------+--------+------+--------+------+------+--------+
# 分组后，表中数据考虑范围就不是 单条记录，因为每个分组都包含了多条记录，参照分组字段，对每个分组中的 多条记录 统一处理
# eg: 按部门分组，每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人
# 将多条数据统一处理，这种方式就叫 聚合
# 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
# 注：参与分组的字段，也归于 聚合结果
mysql>: select dep 部门, group_concat(name) 合并, round(max(salary)) 最高薪资, min(salary) 最低薪资, avg(salary) 平均薪资, sum(salary) 总薪资, count(gender) 人数 from emp group by dep;
mysql>: select dep 部门, group_concat(name) 合并, round(max(salary)) 最高薪资, round(min(salary)) 最低薪资, round(avg(salary)) 平均薪资, round(sum(salary)) 总薪资, count(gender) 人数 from emp group by dep;
+--------+----------------------------------------------------+----------+----------+----------+--------+------+
| 部门 | 合并 | 最高薪资 | 最低薪资 | 平均薪资 | 总薪资 | 人数 |
+--------+----------------------------------------------------+----------+----------+----------+--------+------+
| 咨询部 | san1,san,zero,ying | 9 | 1 | 6 | 25 | 4 |
| 教学部 | ruakei,san2,kevin,jason,owen,jiboy,tank,jerry,engo | 10 | 2 | 6 | 50 | 9 |
| 教职部 | monkey,yangsir | 10 | 1 | 6 | 12 | 2 |
+--------+----------------------------------------------------+----------+----------+----------+--------+------+
mysql>: select dep 部门, max(salary) 最高薪资 from emp group by dep;
+--------+----------+
| 部门 | 最高薪资 |
+--------+----------+
| 咨询部 | 9 |
| 教学部 | 9.8 |
| 教职部 | 10.5
总结：分组后，在查询条件只能为 分组字段 和 聚合函数操作的聚合结果
- 分组后having
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep;
# 最低薪资小于2
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep having avg(salary) > 6;
总结：在分组中，可以使用having 对 聚合结果 再进行筛选，where不可以
- 排序
# 排序规则
order by 主排序字段[asc|desc], 次排序字段[asc|desc],.....,次排序字段n[asc|desc]
- 未分组状态下排序
mysql>: select * from emp;
# 按年龄升序
mysql>: select * from emp order by age asc;
# 按薪资降序
mysql>: select * from emp order by salary desc;
# 按薪资降序，如果相同按年龄排序
mysql>: select * from emp order by salary desc, age desc;
# 按年龄排序，如果相同再按薪资降序
mysql>: select * from emp order by age desc, salary desc;
分组状态下排序
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep;
# 最高薪资降序
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep order by 最高薪资 desc;
- 限制limit
# 语法：limit 条数 | limit 偏移量, 条数
mysql>: select name, salary from emp where salary < 8 order by salary desc limit 1;
mysql>: select name, salary from emp limit 14, 3;
mysql>: select name, salary from emp limit 4, 5;
二、连表查询
- 连接
# 连接：将有联系的多张表通过关联(有联系就行，不一定是外键)字段，进行连接，形参一张大表
# 连表查询：在大表的基础上进行查询，就称之为连表查询
# 将表与表建立连接的方式有四种：内连接、左连接、右连接、全连接
创建表
mysql>: create database db3;
mysql>: use db3;
mysql>:
create table dep(
id int primary key auto_increment,
name varchar(16),
work varchar(16)
);
create table emp(
id int primary key auto_increment,
name varchar(16),
salary float,
dep_id int
);
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);
- 笛卡尔积
# 笛卡尔积: 集合 X{a, b} * Y{o, p, q} => Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}}
mysql>: select * from emp, dep;
# 总结：是两张表 记录的所有排列组合，数据没有利用价值
- 内连接
# 关键字查询 inner join on
# 语法 from A表 inner join B表 on A表.关联的字段=B.关联的字段
mysql>:
select
emp.id, emp.name, salary, emp.dep_id, dep.id, dep.name, dep.work
from emp inner join dep on emp.dep_id=dep.id;
select
emp.id, emp.name, salary, emp.dep_id, dep.id, dep.name, dep.work
from emp, dep where emp.id=dep.id;
+----+----------+--------+--------+----+--------+------+
| id | name | salary | dep_id | id | name | work |
+----+----------+--------+--------+----+--------+------+
| 1 | egon | 3 | 2 | 1 | 市场部 | 销售 |
| 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 |
| 3 | sanjiang | 10 | 1 | 3 | 管理部 | 开车 |
+----+----------+--------+--------+----+--------+------+
3 rows in set (0.00 sec)
# 总结：只保留两个表有关联的数据
- 左连接
# 关键字 left join on
# 语法 from 左表 left join 表名 on 左边.关联字段=右表.关联字段
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from emp left join dep on emp.dep_id=dep.id order by emp.id;
+----+----------+--------+--------+------+
| id | name | salary | name | work |
+----+----------+--------+--------+------+
| 1 | egon | 3 | 教学部 | 授课 |
| 2 | yanghuhu | 2 | 教学部 | 授课 |
| 3 | sanjiang | 10 | 市场部 | 销售 |
| 4 | owen | 88888 | 教学部 | 授课 |
| 5 | liujie | 8 | 市场部 | 销售 |
| 6 | yingjie | 1.2 | NULL | NULL |
+----+----------+--------+--------+------+
6 rows in set (0.00 sec)
# 总结：保留左表的全部数据，右表有对应的数据直接连接表显示，没有对应关系用null填充
- 右连接
# 关键字 right join in
# 语法：from A表 right join B表 on A表.关联字段=B表.关联字段
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from emp right join dep on emp.dep_id=dep.id order by emp.id;
+------+----------+--------+--------+------+
| id | name | salary | name | work |
+------+----------+--------+--------+------+
| NULL | NULL | NULL | 管理部 | 开车 |
| 1 | egon | 3 | 教学部 | 授课 |
| 2 | yanghuhu | 2 | 教学部 | 授课 |
| 3 | sanjiang | 10 | 市场部 | 销售 |
| 4 | owen | 88888 | 教学部 | 授课 |
| 5 | liujie | 8 | 市场部 | 销售 |
+------+----------+--------+--------+------+
6 rows in set (0.00 sec)
# 总结: 保留右表的全部数据，左表有对应数据直接连表显示，没有对应关系用null填空
- 左右可以相互转化
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from emp right join dep on emp.dep_id=dep.id order by emp.id;
+------+----------+--------+--------+------+
| id | name | salary | name | work |
+------+----------+--------+--------+------+
| NULL | NULL | NULL | 管理部 | 开车 |
| 1 | egon | 3 | 教学部 | 授课 |
| 2 | yanghuhu | 2 | 教学部 | 授课 |
| 3 | sanjiang | 10 | 市场部 | 销售 |
| 4 | owen | 88888 | 教学部 | 授课 |
| 5 | liujie | 8 | 市场部 | 销售 |
+------+----------+--------+--------+------+
6 rows in set (0.00 sec)
select
emp.id,emp.name,salary,dep.name,work
from emp left join dep on emp.dep_id=dep.id order by emp.id;
+----+----------+--------+--------+------+
| id | name | salary | name | work |
+----+----------+--------+--------+------+
| 1 | egon | 3 | 教学部 | 授课 |
| 2 | yanghuhu | 2 | 教学部 | 授课 |
| 3 | sanjiang | 10 | 市场部 | 销售 |
| 4 | owen | 88888 | 教学部 | 授课 |
| 5 | liujie | 8 | 市场部 | 销售 |
| 6 | yingjie | 1.2 | NULL | NULL |
+----+----------+--------+--------+------+
6 rows in set (0.00 sec)
- 全连接(一对多)
select emp.id, emp.name, salary, dep.name, work
from emp left join dep on emp.dep_id=dep.id
union
select emp.id, emp.name, salary, dep.name, work
from emp right join dep on emp.dep_id=dep.id order by id;
# 总结：左表右表数据都被保留，彼此有对应关系正常显示，彼此没有对应关系均用空null填充对方
- 一对一与一对多情况一致
# 创建一对一 作者与作者详情 表
create table author(
id int,
name varchar(64),
detail_id int
);
create table author_detail(
id int,
phone varchar(11)
);
# 填充数据
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);
insert into author_detail values(1, '13344556677'), (2, '14466779988'), (3, '12344332255');
# 内联
select author.id, author.name, author_detail.phone from author inner join author_detail on author.detail_id=author_detail.id;
+------+------+-------------+
| id | name | phone |
+------+------+-------------+
| 1 | Bob | 13344556677 |
| 2 | Tom | 14466779988 |
+------+------+-------------+
2 rows in set (0.00 sec)
# 全连
select author.id, author.name, author_detail.phone from author left join author_detail on author.detail_id=author_detail.id
union
select author.id, author.name, author_detail.phone from author right join
author_detail on author.detail_id=author_detail.id;
+------+--------+-------------+
| id | name | phone |
+------+--------+-------------+
| 1 | Bob | 13344556677 |
| 2 | Tom | 14466779988 |
| 3 | ruakei | NULL |
| NULL | NULL | 12344332255 |
+------+--------+-------------+
4 rows in set (0.00 sec)
- 多对多：
# 在一对一基础上，建立 作者与书 的多对多关系关系
# 利用之前的作者表
create table author(
id int,
name varchar(64),
detail_id int
);
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);
# 创建新的书表
create table book(
id int,
name varchar(64),
price decimal(5,2)
);
insert into book values(1, 'python', 3.66), (2, 'Linux', 2.66), (3, 'Go', 4.66);
# 创建 作者与书 的关系表
create table author_book(
id int,
author_id int,
book_id int
);
# 数据：author-book：1-1,2 2-2,3 3-1,3
insert into author_book values(1,1,1),(2,1,2),(3,2,2),(4,2,3),(5,3,1),(6,3,3);
select author.id, author.name, book.name, book.price from author join
author_book on author.id = author_book.author_id # 这是一张表与另一张表拼接
join book on book.id=author_book.id;
------+------+--------+-------+
| id | name | name | price |
+------+------+--------+-------+
| 1 | Bob | python | 3.66 |
| 1 | Bob | Linux | 2.66 |
| 2 | Tom | Go | 4.66 |
+------+------+--------+-------+
3 rows in set (0.00 sec)
在当下的阶段，必将由程序员来主导，甚至比以往更甚。
