单表查询

我们都知道MySQL是由很多数据存入组成的一张又一张表，那么我们肯定需要对MySQL进行操作。那么之前我们讲了一些增删改的内容，那么今天我们来说一下查吧。

查

通过select 来查找指定表内的数据等

select [distinct] 字段1 [[as] 别名1],...,字段n [[as] 别名n] from [数据库名.]表名 [条件];

查找筛选条件：

条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果

注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，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, '上海', '浦西', '教学部'),
	(15, 'ruakei', '女', 67, 2.501, '上海', '陆家嘴', '教学部');

条件:where

通过where后面加上条件，来筛选数据

select 查询字段(*表示所有字段) from 表名 where 条件 ;
#判断规则
"""
比较符合：>  |  <  |  >=  |  <=  |  =  |  !=
区间符合：between 开始 and 结束 |  in(自定义容器)
逻辑符合：and  |  or  |  not
相似符合：like _|%
正则符合：regexp 正则语法
"""
#匹配salary 六到九以内的所有字段
select * from emp where salary between 6 and 9;
#匹配id 1，3，7，20的字段，没有则不显示
select * from emp where id in(1, 3, 7, 20);


聚合函数：group_concat()、max()

having：可以对 聚合函数 结果进行筛选，不能使用 聚合函数 别名
order by：分组后对 聚合函数 进行排序，能使用 聚合函数 别名
limit：条数 | 偏移,条数

group by | having

where 与 having

#在没有使用group by分组的情况下，having 和 where的结果相同
select * from emp where salary > 5;
select * from emp having salary > 5;

聚合函数

"""
max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：记数
group_concat()：组内字段拼接，用来查看组内其他字段
"""

分组查询group by

# 分组后，表中数据考虑范围就不是 单条记录，因为每个分组都包含了多条记录，参照分组字段，对每个分组中的 多条记录 统一处理
# eg: 按部门分组，每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人

# 将多条数据统一处理，这种方式就叫 聚合
# 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
# 注：参与分组的字段，也归于 聚合结果
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep;

+-----------+----------------------------------------------------+-------------------+--------------------+-------------------+--------------------+--------+
| 部门      | 成员                                               | 最高薪资          | 最低薪资           | 平均薪资          | 总薪资             | 人数   |
+-----------+----------------------------------------------------+-------------------+--------------------+-------------------+--------------------+--------+
| NULL      | guapi                                              |                 0 |                  0 |                 0 |                  0 |      1 |
| 咨询部    | san1,san,ying,zero                                 |                 9 | 1.2000000476837158 | 6.250000059604645 |  25.00000023841858 |      4 |
| 教学部    | jiboy,owen,jason,tank,kevin,ruakei,jerry,engo,san2 | 9.800000190734863 | 2.4000000953674316 | 5.566777812110053 |  50.10100030899048 |      9 |
| 教职部    | monkey,yangsir                                     |              10.5 | 1.2000000476837158 | 5.850000023841858 | 11.700000047683716 |      2 |
+-----------+----------------------------------------------------+-------------------+--------------------+-------------------+--------------------+--------+

分组后的having

# 最低薪资小于2
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]

限制 limit

# 语法：limit 条数  |  limit 偏移量,条数
select name, salary from emp where salary<8 order by salary desc limit 1;
select * from emp limit 5,3;  # 先偏移5条满足条件的记录，再查询3条

去重 distinct

create table t1(
	id int,
    x int,
    y int
);

insert into t1 values(1, 1, 1), (2, 1, 2), (3, 2, 2), (4, 2, 2);
select distinct * from t1;  # 全部数据
select distinct x, y from t1;  # 结果 1,1  1,2  2,2
select distinct y from t1;  # 结果 1  2
# 总结：distinct对参与查询的所有字段，整体去重(所查的全部字段的值都相同，才认为是重复数据)

连表查询

连接

# 连接：将有联系的多张表通过关联(有联系就行，不一定是外键)字段，进行连接，形参一张大表
# 连表查询：在大表的基础上进行查询，就称之为连表查询
# 将表与表建立连接的方式有四种：内连接、左连接、右连接、全连接
内连接：from emp inner join dep on emp.dep_id = dep.id  只保存两表有对应关系的记录
左连接：from emp left join dep on emp.dep_id = dep.id   左表记录全部保存，右边没有对应记录空填充
右连接：from emp right join dep on emp.dep_id = dep.id  右表记录全部保存，左边没有对应记录空填充
全连接：
from emp left join dep on emp.dep_id = dep.id
union
from emp right join dep on emp.dep_id = dep.id

数据准备

create database db3;
use db3;
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);

笛卡尔积

select * from emp,dep;
笛卡尔积就是将两张表记录所有的排列组合，但是其数据没有利用价值