MySQL单表查询(重要)

目录

• 一、单表查询的语法及关键字执行的顺序
  • 1.1 单表查询语法
  • 1.2 关键字执行的优先级
• 二、简单查询
  • 2.1 数据准备
• 三、去重（distinct）
• 四、常用函数
• 五、约束条件(where)
• 六、聚合函数
• 七、分组与筛选(group by | having)
  • 7.1 分组查询(group by)
  • 7.2 where与having的区别
  • 7.3 分组后的having
  • 7.4 联合分组
• 八、排序(order by)
  • 8.1 排序规则
  • 8.2 未分组状态下
  • 8.3 分组状态下
• 九、限制 limit

一、单表查询的语法及关键字执行的顺序

1.1 单表查询语法

SELECT DISTINCT 字段1,字段2... FROM 表名
                              WHERE 条件
                              GROUP BY field
                              HAVING 筛选
                              ORDER BY field
                              LIMIT 限制条数

1.2 关键字执行的优先级

1. from：找到表
2. where：拿着where指定的约束条件，去文件/表中取出一条条记录
3. group by：将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4. having：将分组的结果进行having过滤
5. distinct：去重
6. order by：将结果按条件排序：order by
7. limit：限制结果的显示条数

注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置)，条件的种类可以缺失，但不能乱序

二、简单查询

"""
增：
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 => 层层筛选后的结果

2.1 数据准备

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, '上海', '陆家嘴', '教学部');

三、去重（distinct）

mysql>: 
create table t1(
	id int,
    x int,
    y int
);
mysql>: 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对参与查询的所有字段，整体去重(所查的全部字段的值都相同，才认为是重复数据)

四、常用函数

• 拼接：concat() | concat_ws()，区别是concat_ws把拼接字符放到第1个位置，后面放拼接字段。
• 大小写：upper() | lower()
• 浮点型操作：ceil() 向上取整| floor() 向下取整| round() 四舍五入
• 整型：可以直接运算
• 别名：只要是对查询的字段取别名都可以用 as 关键字，也可以省略不写

mysql>: select name,area,port from emp;
# 拼接：concat() | concat_ws()
mysql>: select name as 姓名, concat(area,'-',port) 地址 from emp;  # 对查询的结果取别名显示
mysql>: select name as 姓名, concat_ws("-",area,port,dep) 信息 from emp;

# 大小写
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)

判断规则：

1. 比较匹配：> | < | >= | <= | = | !=
2. 区间匹配：between 开始 and 结束 | in(自定义容器)
3. 逻辑匹配：and | or | not
4. 模糊匹配：like 'n%'
   • 通配符可以是%或_，
     • %表示任意多字符
     • _表示一个字符
5. 正则匹配：regexp 正则语法

# 多条件协调操作导入：where 奇数 [group by 部门 having 平均薪资] order by [平均]薪资 limit 1

# 比较匹配
mysql>: select * from emp where salary>5;	# 查询salary>5的全部记录
mysql>: select * from emp where id%2=0;		# 查询id为偶数的全部记录
mysql>: select * from emp where id%2=1;		# 查询id为奇数的全部记录

# 区间匹配
mysql>: select * from emp where salary between 6 and 9;	# 查询 salary 在 6-9 范围内的记录

# 模糊匹配
mysql>: select * from emp where name like "%w%";	# 匹配name字段 中包含w的记录
mysql>: select * from emp where name like "_w%";	# 匹配name字段 中包含一个任意字符w的记录
mysql>: select * from emp where name like "__e%";	# 匹配name字段 中包含两个任意字符w的记录

# sql只支持部分正则语法
mysql>: select * from emp where name regexp '.*[0-9]';  # 支持[]语法

六、聚合函数

• max()：最大值
• min()：最小值
• avg()：平均值
• sum()：求和
• count()：记数
• group_concat()：组内字段拼接

强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组。

# 求全部记录的个数
mysql>: select count(*) from emp;

# 求字段salary的最大数据
mysql>: select max(salary) from emp;

# 求字段salary的最小数据
mysql>: select min(salary) from emp;

# 求字段salary的平均数据
mysql>: select avg(salary) from emp;

# 对 字段salary的数据 求和
mysql>: select sum(salary) from emp;

七、分组与筛选(group by | having)

7.1 分组查询(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(salary) 部门人数 
from emp group by dep;

# eg: 按部门分组,查询每个组年龄最大的记录
mysql>: select dep 部门, max(age) 最高年龄 from emp group by dep;

7.2 where与having的区别

• 在没有分组的情况下，where与having结果相同
• 重点：having可以对 聚合结果 进行筛选

# 没有分组进行筛选,没有区别
mysql>: select * from emp where salary > 5;
mysql>: select * from emp having salary > 5;

7.3 分组后的having

• 重点：having可以对 聚合结果 进行筛选

# eg: 按部门分组，查询每个部门中最低薪资小于5的记录
mysql>: 
select
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	count(salary) 部门人数
from emp group by dep having min(salary) < 5;

# having可以对 聚合结果 再进行筛选，where不可以

7.4 联合分组

联合分组：按多个字段综合结果进行分组

• 分组的条件有多个。根据多个条件进行分组

# eg: 根据 所在地区 进行分组，
mysql>: 
select
	area 省份,
	port 地区,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资
from emp group by area,port;

# eg: 根据 部门，和 所在地区 进行分组，
mysql>: 
select
	dep 部门,
	area 省份,
	port 地区,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资
from emp group by dep,area,port;

八、排序(order by)

• 升序（从低到高）：asc (默认升序)
• 降序（从高到低）：desc

8.1 排序规则

order by 排序字段 [asc|desc], 排序字段1 [asc|desc], ..., 排序字段n [asc|desc]

8.2 未分组状态下

mysql>: select * from emp

# 按年龄升序（从低到高）
mysql>: select * from emp order by age asc;
# 按年龄降序（从高到低）
mysql>: select * from emp order by age desc;

8.3 分组状态下

# 按部门分组后, 排序部门人数为降序(从高到低)
mysql>: 
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(salary) 部门人数
from emp group by dep order by 部门人数 desc;

# 按部门分组后, 排序部门人数为升序(从低到高)
mysql>: 
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(salary) 部门人数
from emp group by dep order by 部门人数;	# 默认升序

九、限制 limit

语法：limit 条数 | limit 偏移量,条数

# 语法：limit 条数  |  limit 偏移量,条数
mysql>: select name, salary from emp where salary<8 order by salary desc limit 1;

mysql>: select * from emp limit 5,3;  # 先偏移5条满足条件的记录，再查询3条