2023-05-29 10:41 广州番禺职业技术学院大数据开发工程师

关注

一篇文章入门MySQL，select语法篇【下】

3.5 多行函数

3.5.1 常用聚合函数

所谓聚合函数，也就是多行函数，即多行记录作为参数进行执行来获得结果
min(), max()：求最大最小，不包含null
sum(), avg()：求和与平均，不包含null
count()：计数
- count(*)：返回记录的总数，包含null
- count("列名")：返回记录中，某列不为空的记录，不包含null

mysql> select count(*) from employees;	 # 包含null为107行
+----------+
| count(*) |
+----------+
|      107 |
+----------+

mysql> select count(commission_pct) from employees;	# commission_pct 不包含null为35行
+-----------------------+
| count(commission_pct) |
+-----------------------+
|                    35 |
+-----------------------+

# avg不包含null行，其他也一样
mysql> select avg(commission_pct) from employees;
+---------------------+
| avg(commission_pct) |
+---------------------+
|            0.222857 |
+---------------------+

mysql> select avg(commission_pct) from employees where commission_pct is not null;
+---------------------+
| avg(commission_pct) |
+---------------------+
|            0.222857 |
+---------------------+

3.5.2 group by

group by关键字，用于数据进行分组，并利用聚合函数求各个组的属性

基本语法

select 列名1, group_func(列名2)		# 聚合函数
from 表名
[where ...]			
group by 列名1 [with rollup]
[order by ... ]

注意：

所有在select中出现的、不在聚合函数里面的列名必须出现在group by 的字句中，这是因为一旦分为了多个组，这该列就被分散了。
with rollup关键字：
- 作用：在所有分组的结尾添加上一行记录，该记录为所有记录的总和，也就是对原表进行聚合函数的结果（见例子）
- with rollup 不应该与 order by 关键字一起使用，因为with rollup为计算总和应该放在最后面，若利用with rollup，则会改变他的位置，失去了他的作用
例子：

# 获得不同部门的平均工资以及人数
mysql> select department_id, avg(salary), count(*)
    -> from employees
    -> group by department_id with rollup;	
+---------------+--------------+----------+
| department_id | avg(salary)  | count(*) |
+---------------+--------------+----------+
|          NULL |  7000.000000 |        1 |
|            10 |  4400.000000 |        1 |
|            20 |  9500.000000 |        2 |
|            30 |  4150.000000 |        6 |
|            40 |  6500.000000 |        1 |
|            50 |  3475.555556 |       45 |
|            60 |  5760.000000 |        5 |
|            70 | 10000.000000 |        1 |
|            80 |  8955.882353 |       34 |
|            90 | 19333.333333 |        3 |
|           100 |  8600.000000 |        6 |
|           110 | 10150.000000 |        2 |
|          NULL |  6461.682243 |      107 |		# 最后一行为总和，也就是所有人的平均工资，也是各个部门人数的总和
+---------------+--------------+----------+
13 rows in set (0.00 sec)

3.5.3 having

对数据进行筛选，主要为分组数据

having 与 where

相同点：都能对数据进行筛选
区别1：两者可以利用的筛选条件不同，
- having能利用聚合函数作为筛选条件，但where只能利用一行数据作为筛选条件，
- 因此这导致了当需要对分组数据进行筛选时，只能利用having，
  
  因此where在group by之前，having在group by之后，
- 对单行数据进行筛选时，having和where都能使用
区别2：多表连接时，两者对数据的筛选流程不一样
- 明确一点：
  
  having可以利用聚合函数进行筛选，聚合函数是多个行之间的逻辑函数，因此前提需要存在有多个行，having才会发生作用
  
  where只能对单行数据进行筛选，因此存在单行数据时，where就能发生作用
- 当对单个表进行筛选时，因为筛选对象都是一行，两者没有什么差别
- 当需要多个表连接进行筛选时，两者的差异就体现出来了，由于having需要先有多个行才能进行操作，因此其会先连接再进行筛选，而where是筛选再连接，这就导致了where能利用较小的表相连接，效率变高，因此筛选表时用的是where
小结：
- having能用聚合函数进行筛选，而where不能，因此分组数据的筛选都用having
- 由于having为先连接后筛选，而where为先筛选后连接，因此对表进行筛选时，最好用where
- 综上，where在group by之前，having在group by之后
```
select from 
where			# 先对表进行筛选	
group by..		# 筛选完毕后进行分组，因此被筛选的数据不包含在分组数据中
having...		# 对分完的组在进行筛选
```
例子：

# 在单行数据的查询上结果都一样
mysql> select salary from employees where salary > 3000 limit 2;
+----------+
| salary   |
+----------+
| 24000.00 |
| 17000.00 |
+----------+
mysql> select salary from employees having salary > 3000 limit 2;
+----------+
| salary   |
+----------+
| 24000.00 |
| 17000.00 |
+----------+

# 取各个最少工资少大于5000 的部门的平均值	
mysql> select department_id, AVG(salary) from employees group by department_id having min(salary) > 5000 limit 2;
+---------------+-------------+
| department_id | AVG(salary) |
+---------------+-------------+
|          NULL | 7000.000000 |
|            20 | 9500.000000 |
+---------------+-------------+
2 rows in set (0.00 sec)

# 用where报错
mysql> select department_id, AVG(salary) from employees group by department_id where min(salary) > 5000 limit 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where min(salary) > 5000 limit 2' at line 1

3.6 select语句执行过程

3.6.1 关键字顺序结构

select ...	distinct
from t1 		# 多表查询
    join t2
    on ...
    join t3
    on ... 
where ... 			# 不含组筛选
	and \ or ...	# 运算符
group by ...		# 分组
having ...			# 组筛选
order by ... desc	 # 排序
limit n.			# 分页

3.6.2 执行顺序与原理

执行顺序如下，而且每一步骤都会有个临时表，分组就会有多个表，然后select又回到了一个表

from t1 join ...		 # 先指定需要的表
where ... and \ or ...	 #  通过筛选机制获得临时表
group by ...			# 分组
having ...				# 筛选
select ... 				# 临时结果
distinct				# 去重
order by				# 结果排序
limit 					# 显示

3.7 单行函数

所谓单行函数，就是处理的对象都是单行的，以每一行作为参数进行的函数

3.7.1 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
if(v, v1, v2)：当v为正时返回v1，反之返回v2
ifnull(v1, v2)：若v1为null，返回v2，反之返回v1
case when 条件1 then 结果1 ... : 相当于python中的if ... elif... else
case v when 常数 then 结果 ... : 相当于 switch v case 常数..

# 对工资进行分类
SELECT employee_id, salary, 
  CASE 
    WHEN salary > 15000 THEN '高薪'
    WHEN salary > 10000 THEN '人生赢家'
    WHEN salary > 5000 THEN '潜力股'
    ELSE '垃圾'
    END '描述'
FROM employees	
;
+-------------+----------+----------+
| employee_id | salary   | 描述     |
+-------------+----------+----------+
|         100 | 24000.00 | 高薪     |
|         101 | 17000.00 | 高薪     |
|         102 | 17000.00 | 高薪     |
|         103 |  9000.00 | 潜力股   |
|         104 |  6000.00 | 潜力股   |

# 对发货状态进分类	# 根据status的值给定不同的结果
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款'
WHEN 2 THEN '已付款'
WHEN 3 THEN '已发货'
WHEN 4 THEN '确认收货'
ELSE '无效订单' END
FROM t_order;

3.7.2 数值函数

01) 基本函数

函数	用法
abs(x)	取绝对值
sign(x)	或者x的符号，正数返回1，负数返回-1，0返回0
pi()	获得圆周率
ceil(x),ceiling(x)	向上取整，返回大于或等于x的最小整数
floor(x)	向下取整，返回小于或等于x的最小整数
least(x1,x2...xn)	返回列表中的最小值
greatest(x1,x2,...xn)	返回列表中的最大值
mod(x, y)	求X / y的余数
rand([x])	返回0~1之间的随机数，x为随机种子
round(x, [n])	四舍五入，保留n位，n默认为0sq
truncate(x, y)	返回数字x截止至y位小数的结果
sqrt(x)	开根号，若小于0，结果为null

例子

mysql> select truncate(1.23433, 3);
+----------------------+
| truncate(1.23433, 3) |
+----------------------+
|                1.234 |
+----------------------+
1 row in set (0.00 sec)

02) 指数与对数函数

函数	用法
pow(x,n),power(x,n)	返回x的n次方
exp(n)	返回e的n次方
ln(x), log(x)	以e为底，x的对数
log10(x)	以10为底，x的对数
log2(x)	以2为底，x的对数

3.7.3 字符串函数

函数	用法
char_length(S)	返回S的字符个数
length(S)	返回S的字字节个数，根据字符集的不同而不同
concat(s1, s2...sn)	连接字符串
concat_ws(x, s1,s2,...sn)	连接字符串，并以x为间隔
insert(s, index, len, s1)	将S中第index个开始，len个长度的子串替代为S1（下标从1开始）
nullif(S1, S2)	比较两个字符串，若相等则返回null，若不相等，返回S1

# 字符数为1，字节数为2（utf8）
mysql> select char_length("陈"), length("陈");
+-------------------+--------------+
| char_length("陈") | length("陈") |
+-------------------+--------------+
|                 1 |            2 |
+-------------------+--------------+

# 间隔为 “ ”
mysql> select concat_ws(" ", "陈", last_name) from employees limit 2;
+---------------------------------+
| concat_ws(" ", "陈", last_name) |
+---------------------------------+
| 陈 King                         |
| 陈 Kochhar                      |
+---------------------------------+

# 字符串不区分大小写
mysql> select nullif("陈", "陈"), nullif("chen", "CHEN"), nullif("chen","chen1");
+--------------------+------------------------+------------------------+
| nullif("陈", "陈") | nullif("chen", "CHEN") | nullif("chen","chen1") |
+--------------------+------------------------+------------------------+
| NULL               | NULL                   | chen                   |
+--------------------+------------------------+------------------------+

3.7.4 日期与时间函数

01) 获取日期与时间

函数	用法
curdate(),current_date()	获得当前日期（系统）
curtime()	获得当前时间（系统）
now(),localtime()	返回当前系统的日期和时间
utc_date()	返回UTC日期（世界标准日期）
utc_time()	返回UTC时间（世界标准时间）
current_timestamp(3)	精确到3位毫秒数

# 世界标准时间和系统时间是不一样的
mysql> select curdate(),curtime(), now(), utc_date(), utc_time();
+------------+-----------+---------------------+------------+------------+
| curdate()  | curtime() | now()               | utc_date() | utc_time() |
+------------+-----------+---------------------+------------+------------+
| 2023-04-08 | 10:10:42  | 2023-04-08 10:10:42 | 2023-04-08 | 02:10:42   |
+------------+-----------+---------------------+------------+------------+
1 row in set (0.00 sec)

02) 获得月份，星期，天数等函数

函数(参数都为date)	用法
year(),month(),day()	获得年份、月份、日期
hour(),minute(),second()	获得小时，分钟、秒
monthname()	获得月份名称：January...
dayname()	返回星期几：Monday..
quarter()	返回季度：1~4
weekofyear()	一年中的第几周
dayofyear()	一年中的第几天
dayofmonth(), day()	一月中的第几天
dayofweek()	一周中的第几天

# 返回具体时间
mysql> select now(), year(now()), month(now()), day(now()), hour(now()), minute(now()), second(now());
+---------------------+-------------+--------------+------------+-------------+---------------+---------------+
| now()               | year(now()) | month(now()) | day(now()) | hour(now()) | minute(now()) | second(now()) |
+---------------------+-------------+--------------+------------+-------------+---------------+---------------+
| 2023-04-08 10:36:18 |        2023 |            4 |          8 |          10 |            36 |            18 |
+---------------------+-------------+--------------+------------+-------------+---------------+---------------+

# 返回日期
mysql> select monthname(now()), dayname(now());
+------------------+----------------+
| monthname(now()) | dayname(now()) |
+------------------+----------------+
| April            | Saturday       |
+------------------+----------------+
1 row in set (0.00 sec)

mysql> select quarter(now()), weekofyear(now()), dayofyear(now()), dayofmonth(now()), dayofweek(now());
+----------------+-------------------+------------------+-------------------+------------------+
| quarter(now()) | weekofyear(now()) | dayofyear(now()) | dayofmonth(now()) | dayofweek(now()) |
+----------------+-------------------+------------------+-------------------+------------------+
|              2 |                14 |               98 |                 8 |                7 |
+----------------+-------------------+------------------+-------------------+------------------+

3.8 子查询

所谓子查询，就是在一个查询中嵌套这一个查询语句，也就内查询
根据子查询的结果返回记录的条数可以分为：单行子查询、多行子查询

根据子查询执行的次数可以分为：相关子查询、不相关子查询
内查询的结果往往用于外查询的做条件判断，因此与条件判断相关的关键字都可以进行子查询：case [v] when,where, having

3.8.1 单行子查询

主要操作符有： =,>,>=,<,<=,<>也就是比较运算符

01) where 中的子查询

最常用的子查询位置
例子：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

# 子查询方式
select last_name, job_id, salary
from employees
where job_id = (
	select job_id from employees
    where employees_id = 141	# 号数为141号
) and
salary > (
	select salary from employees
    where employees_id = 143
);

# 自连接方式
select e1.last_name, e1.job_id, e1.salary
from employees as e1 join employees as e2
on e2.employee_id in (141, 143) 
and e1.job_id = e2.job_id 
and e1.salary > e2.salary2;

02) having中的子查询

#  查询最低工资大于50号部门最低工资的部门id和其最低工资
sel

3.8.2 多行子查询

主要操作符号有：in、any,all,some(也就是any)
例子：查看平均工资最少的部门id

# 思路：先获得各个部门的平均工资，再获得工资中的最小值
SELECT MIN(e.avg_salary)
FROM 
	(SELECT AVG(salary) AS avg_salary
	FROM employees
	GROUP BY department_id
	) AS e;

3.8.3 相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

01) 在where中进行子查询

例子：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

select last_name, salary, department_id
from employees out_table
where out_table.salary > 
(select avg(salary)
from employees in_table
where out_table.employee_id =  in_table.employee_id 	# 调用了外部表
group by department_id 
)
# 相关子查询的方法比较慢
SELECT last_name, salary, department_id, avg_sal
FROM 
(SELECT last_name, salary, department_id ,
	AVG(salary) over(PARTITION BY department_id) AS 'avg_sal'	# 利用了窗口函数使查询变为了非相关子查询
FROM employees) AS new_table
WHERE salary > avg_sal;

02) 在from中使用子查询

例子：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP
BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

#MySQL必知必会#

全部评论

推荐最新楼层

牛客987852806号

北京北大方正软件技术学院运营

其实，真的要吃透的是逻辑，吃透逻辑的前提，要知道SQL语句执行顺序。现在实践的时候，大佬都使用虚拟表来做，我曾经受到大佬的指点，开始不一下整体思考，通过整体逻辑，把条件拆开合并，这样好多类似力扣，牛客上面的难题，大多也会迎刃而解，确实，基础函数很重要，最重要的还是逻辑。小白的浅谈，请大佬及作者多多批评，指正。

2 回复分享

发布于 2023-06-01 18:01 北京