0925 单表查询和连表查询的具体操作
单表查询
"""
增：insert into [数据库名].表名(字段1,....,字段n) values(数据1,...,数据n),...,(数据1,...,数据n);
删：delete from [数据库名].表名 [条件];
改：update [数据库名].表名 set 字段1=值1[,...,字段n=值n] [条件];
查：select [distinct(去重)] 字段1,...,字段n from [数据库名].表名 [条件];
select [distinct(去重)] 字段1 [[as] 别名1],...,字段n from [数据库名].表名 [条件]; # 给查询的结果起别名
"""
"""
查语法：
select [distinct] 字段1 [[as] 别名], ..., 字段n [[as] 别名] from [库名.]表名
[
where 约束条件
group by 分组依据
having 过滤条件
order by 排序的字段
limit 限制显示的条数
];
注：
1.查表中所有字段用*表示
2.条件的书写规则严格按照语法顺序书写，可以缺省，但不可以错序
3.约束条件的流程：from -> where -> group by -> having -> distinct -> order by -> limit
# 重点：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置),条件的种类可以不全，可以缺失，但不可以乱序
4.字段可以起别名
5.字段可以直接做运算 select age + 1 'new_age' from emp;
6.分组后的条件均可以使用聚合函数
"""
一、去重：distinct
mysql> create table t1(id int,x int,y int);
msyql> insert into t1 values(1,1,1),(2,1,2),(3,2,2),(4,2,2);
# 查
mysql> select distinct * from t1; # 得到全部数据
mysql> select distinct x,y from t1; # 结果1,1 1,2 2,2
mysql> select distinct y from t1; # 结果1 2
# 总结:distinct对参与查询的所有字段，整体去重（所查的全部字段的值都相同，才认为是重复数据）
二、数据准备
create table emp (
id int(0) NOT NULL AUTO_INCREMENT,
name varchar(10) NOT NULL,
gender enum('男','女','未知') NULL DEFAULT '未知',
age int(0) NULL DEFAULT 0,
salary float NULL DEFAULT 0,
area varchar(20) NULL DEFAULT '中国',
port varchar(20) DEFAULT '未知',
dep varchar(20),
primary key (`id`)
);
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, '上海', '浦西', '教学部');
三、常用函数
"""
concat(字段1,...,字段n)：完成字段的拼接
concat_ws(x, 字段1,...,字段n)：完成字段的拼接，x为连接符
lower()：小写
upper()：大写
ceil()：向上取整
floor()：向下取整
round()：四舍五入
"""
mysql>: select name as 姓名, concat(area,'-',port) 地址 from emp; # name别名为姓名，用-拼接area和port
mysql>: select name as 姓名, concat_ws('-',area,port,dep) 信息 from emp; # 用-拼接后面的area,port,dep
mysql>: select upper(name) 姓名大写,lower(name) 姓名小写 from emp;
mysql>: select id,salary,ceil(salary) 向上取整薪资,floor(salary) 向下取整薪资,round(salary) 四舍五入薪资 from emp;
mysql>: select name 姓名, age 旧年龄, age+1 新年龄 from emp;
四、where条件
# 多条件协调操作导入：where 奇数 [group by 部门 having 平均薪资] order by [平均]薪资 limit 1
mysql>: select * from emp where id<5 limit 1; # 正常
mysql>: select * from emp limit 1 where id<5; # 异常，条件乱序
# 判断规则
"""
比较符合：> | < | >= | <= | = | !=
区间符合：between 开始 and 结束 | in(自定义容器)
逻辑符合：and | or | not
相似符合：like _|%
正则符合：regexp 正则表达式（只支持部分正则语法）
"""
mysql>: select * from emp where salary>5; # 得到所有薪资大于5的结果
mysql>: select * from emp where id%2=0; # 得到id为偶数的所有结果
mysql>: select * from emp where salary between 6 and 9; # 薪资在6~9的所有结果
mysql>: select * from emp where id in(1, 3, 7, 20); # id为1，3,7的结果
# _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]'; # 支持[]语法
五、分组与筛选：group by | having
where与having
# 表象：在没有分组的情况下，where与having结果相同
# 重点：having可以对 聚合结果 进行筛选，where不可以
mysql>: select * from emp where salary > 5;
mysql>: select * from emp having salary > 5;
mysql>: select * from emp where id in (5, 10, 15, 20); # 这个不可以
mysql>: select * from emp having id in (5, 10, 15, 20); # 得到id为5和10的结果
聚合函数
max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：记数
group_concat()：组内字段拼接，用来查看组内其他字段
group by ：分组查询
# 修改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;
# 分组后，表中数据考虑范围就不是 单条记录，因为每个分组都包含了多条记录，参照分组字段，对每个分组中的 多条记录 统一处理
# eg: 按部门分组，每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人
# 将多条数据统一处理，这种方式就叫 聚合
# 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
# 注：参与分组的字段，也归于 聚合结果
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep;
mysql>: select
dep 部门,
max(age) 最高年龄
from emp group by dep;
# 总结：分组后，查询条件只能为 分组字段 和 聚合函数操作的聚合结果
分组后的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 min(salary)<2;
# having可以对聚合函数结果再进行筛选，where不可以
六、排序
排序规则
# order by 主排序字段 [asc|desc], 次排序字段1 [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; # order by后第一个字段是主排序字段，后面的是次排序字段
# 按年龄降序，如果相同，再按薪资降序
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 max(salary) desc;
七、limit：限制结果条数
# 语法：limit 条数 | limit 偏移量,条数
mysql>: select name, salary from emp where salary<8 order by salary desc limit 1; # 查询薪资小于8的按降序排序后的最高薪资的结果
mysql>: select * from emp limit 5,3; # 先偏移5条满足条件的记录，再查询3条
八、应用举例
1、查询教学部山东人的平均薪资
mysql>
select
dep 部门,
area 地区,
avg(salary) 平均薪资
from emp where dep='教学部' and area='山东';
# 结果
+-----------+--------+-------------------+
| 部门 | 地区 | 平均薪资 |
+-----------+--------+-------------------+
| 教学部 | 山东 | 7.599999904632568 |
+-----------+--------+-------------------+
2、查询姓名中包含英文字母n并且居住在上海的人的所有信息
mysql> select * from emp where name like '%n%' and area='上海';
# 结果
+----+---------+--------+------+--------+--------+--------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+---------+--------+------+--------+--------+--------+-----------+
| 1 | yangsir | 男 | 42 | 10.5 | 上海 | 浦东 | 教职部 |
| 12 | san | 男 | 30 | 9 | 上海 | 浦东 | 咨询部 |
| 13 | san1 | 男 | 30 | 6 | 上海 | 浦东 | 咨询部 |
| 14 | san2 | 男 | 30 | 6 | 上海 | 浦西 | 教学部 |
+----+---------+--------+------+--------+--------+--------+-----------+
3、查询姓名中包含英文字母n但不包含数字的人的所有信息
mysql> select * from emp where name like '%n%' and name not regexp '.*[0-9].*';
# 结果
+----+---------+--------+------+--------+--------+--------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+---------+--------+------+--------+--------+--------+-----------+
| 1 | yangsir | 男 | 42 | 10.5 | 上海 | 浦东 | 教职部 |
| 2 | engo | 男 | 38 | 9.4 | 山东 | 济南 | 教学部 |
| 4 | tank | 女 | 28 | 2.4 | 广州 | 广东 | 教学部 |
| 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 | 上海 | 浦东 | 咨询部 |
+----+---------+--------+------+--------+--------+--------+-----------+
4、查看各部门的平均年龄并升序排序
mysql>
select
dep 部门,
avg(age) 平均年龄
from emp group by dep order by avg(age) asc;
# 结果
+-----------+--------------+
| 部门 | 平均年龄 |
+-----------+--------------+
| 咨询部 | 28.5000 |
| 教学部 | 29.5000 |
| 教职部 | 35.0000 |
+-----------+--------------+
5、查询各部门中年纪最大的人的姓名与居住地（户籍+区域）
方法一
mysql>
select
dep 部门,
name 姓名,
max(age) 年龄,
concat(area,port) 居住地
from (select * from emp order by age desc) as AAA
group by AAA.dep;
# 结果
+-----------+---------+--------+--------------+
| 部门 | 姓名 | 年龄 | 居住地 |
+-----------+---------+--------+--------------+
| 咨询部 | ying | 36 | 安徽芜湖 |
| 教学部 | engo | 38 | 山东济南 |
| 教职部 | yangsir | 42 | 上海浦东 |
+-----------+---------+--------+--------------+
方法二
mysql>
select
dep 部门,
name 姓名,
age 年龄,
concat(area,port) 居住地
from emp where (dep,age) in (select dep,max(age) from emp group by dep);
# 结果
----------+---------+--------+--------------+
| 部门 | 姓名 | 年龄 | 居住地 |
+-----------+---------+--------+--------------+
| 教职部 | yangsir | 42 | 上海浦东 |
| 教学部 | engo | 38 | 山东济南 |
| 咨询部 | ying | 36 | 安徽芜湖 |
+-----------+---------+--------+--------------+
6、查询不同年龄层次平均薪资大于5w组中工资最高者的姓名与薪资
mysql>
select
age 年龄,
name 姓名,
salary 薪资
from emp where (age,salary) in (
select age,max(salary) from emp group by age having avg(salary)>5);
+--------+---------+--------+
| 年龄 | 姓名 | 薪资 |
+--------+---------+--------+
| 42 | yangsir | 10.5 |
| 38 | engo | 9.4 |
| 18 | zero | 8.8 |
| 18 | owen | 8.8 |
| 30 | san | 9 |
+--------+---------+--------+
连表查询
一、连接
# 连接：将有联系的多张表通过关联(有联系就行，不一定是外键)字段，进行连接，形参一张大表
# 连表查询：在大表的基础上进行查询，就称之为连表查询
# 将表与表建立连接的方式有四种：内连接、左连接、右连接、全连接
二、一对多数据准备
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;
# 总结：是两张表的记录的所有排列组合，数据没有利用价值
四、内连接
# 内连接：结果为两张表有对应关系的数据(emp有dep没有,emp没有dep有的记录均不会被虚拟表展示)
# 关键字：inner join on (inner可以省略)
# 语法：from A表 inner join B表 on A表.关联字段=B表.关联字段
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from emp inner join dep on emp.dep_id = dep.id
order by emp.id;
# 总结：只保留两个表有关联的数据
五、左连接
# 左连接：在内连接的基础上还保留左表特有的记录
# 关键字：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;
# 总结：保留左表的全部数据，右表有对应数据直接连表显示，没有对应关系空填充
六、右连接
# 右连接：在内连接的基础上还保留右表特有的记录
# 关键字：right join on
# 语法：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;
# 总结：保留右表的全部数据，左表有对应数据直接连表显示，没有对应关系空填充
七、左右可以相互转化
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;
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from dep left join emp on emp.dep_id = dep.id
order by emp.id;
# 总结：更换一下左右表的位置，相对应更换左右连接关键字，结果相同
八、全连接
# 全连接：在内连接的基础上分别保留这左表及右表特有的记录
mysql>:
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;
# 总结：左表右表数据都被保留，彼此有对应关系正常显示，彼此没有对应关系均空填充对方
九、应用举例
查询每一个部门下的员工们及员工职责
mysql>
select
max(dep.name),
max(dep.work),
group_concat(emp.name)
from emp right join dep on emp.dep_id = dep.id group by dep_id;
# 结果
--------------+---------------+------------------------+
| max(dep.name) | max(dep.work) | group_concat(emp.name) |
+---------------+---------------+------------------------+
| 管理部 | 开车 | NULL |
| 市场部 | 销售 | sanjiang,liujie |
| 教学部 | 授课 | egon,yanghuhu,owen |
+---------------+---------------+------------------------+
十、一对一与一对多情况一致
# 创建一对一 作者与作者详情 表
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,name,phone from author join author_detail on author.detail_id = author_detail.id order by author.id;
# 全连
select author.id,name,phone from author left join author_detail on author.detail_id = author_detail.id
union
select author.id,name,phone from author right join author_detail on author.detail_id = author_detail.id
order by id;
十一、多对多：两表两表建立连接
# 在一对一基础上，建立 作者与书 的多对多关系
# 利用之前的作者表
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 book.name, book.price, author.name, author_detail.phone from book
join author_book on book.id = author_book.book_id
join author on author_book.author_id = author.id
left join author_detail on author.detail_id = author_detail.id;