【MySQL】-【单行函数】-【聚合函数】-【SQL底层执行原理】

已于 2022-08-01 15:27:09 修改
阅读量210 收藏
点赞数
分类专栏： MySQL 文章标签： database
于 2022-06-20 10:02:50 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/CaraYQ/article/details/125366143
版权

目录

函数

一、函数分类：

  1. 单行函数：一个输入一个输出（本文）
    （1）操作数据对象
    （2）接受参数返回一个结果
    （3）只对一行进行变换
    （4）每行返回一个结果
    （5）可以嵌套
    （6）参数可以是一列或一个值
  2. 多行函数

单行函数

基本函数

在这里插入图片描述

SELECT
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;

在这里插入图片描述

# 当你传入的因子一样时，返回的随机数一样，这说明`RAND()`函数的底层其实是根据因子计算出的随机数，是伪随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

在这里插入图片描述

SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2),
ROUND(123.456,-1),ROUND(153.456,-2)
FROM DUAL;

在这里插入图片描述

SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1)
FROM DUAL;

在这里插入图片描述

# 单行函数可以嵌套
SELECT TRUNCATE(ROUND(123.456,2),0)
FROM DUAL;

在这里插入图片描述

角度与弧度互换函数

在这里插入图片描述

SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90),
DEGREES(2*PI()),DEGREES(RADIANS(60))
FROM DUAL;

在这里插入图片描述

三角函数

在这里插入图片描述

SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1))
FROM DUAL;

在这里插入图片描述

指数与对数

在这里插入图片描述
指数：SELECT POW(2,5),POWER(2,4),EXP(2) FROM DUAL;
在这里插入图片描述
对数：SELECT LN(EXP(2)),LOG(EXP(2)),LOG10(10),LOG2(4) FROM DUAL;
在这里插入图片描述

进制间的转换

在这里插入图片描述
CONV(x,f1,f2)将f1进制的x转换成f2进制，如CONV(10,2,8)就是将二进制的10转换成八进制，二进制的10也就是2，转换成八进制也是2

SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,8)
FROM DUAL;

在这里插入图片描述

字符串函数

在这里插入图片描述
在这里插入图片描述

SELECT ASCII('Abcdfsf'),CHAR_LENGTH('hello'),CHAR_LENGTH('我们'),
LENGTH('hello'),LENGTH('我们')

LENGTH('hello')一个字符用一个字节存储就行，所以LENGTH('hello')站5个字节。而UTF-8中每个汉字占3个字节，所以LENGTH('我们')占6个字节。用LENGTH()就是在衡量底层存储时使用了多少个字节，CHAR_LENGTH()衡量的是内存中占了多少个字符，所以CHAR_LENGTH('hello'),CHAR_LENGTH('我们')都为5。
在这里插入图片描述

# 字符串拼接成：xxx worked for yyy
SELECT CONCAT(emp.last_name,' worked for ',mgr.last_name) "details"
FROM employees emp JOIN employees mgr
WHERE emp.`manager_id` = mgr.employee_id;

在这里插入图片描述

SELECT CONCAT_WS('-','hello','world','hello','beijing')
FROM DUAL;

字符串与字符串之间用第一个参数拼接
在这里插入图片描述
如果第一个参数是空字符串

SELECT CONCAT_WS('','hello','world','hello','beijing')
FROM DUAL;

在这里插入图片描述
则效果同CONCAT方法：

SELECT CONCAT('hello','world','hello','beijing')
FROM DUAL;

在这里插入图片描述

SELECT INSERT('helloworld',2,3,'aaaaa'),REPLACE('hello','ll','mmm')
FROM DUAL;

字符串的索引是从1开始的，INSERT('helloworld',2,3,'aaaaa')将helloworld中以索引2开始，往后数3个字符（包含第3个字符），将这些字符（这一范围的字符）替换为aaaaa。REPLACE('hello','ll','mmm')将hello中的ll替换为mmm
在这里插入图片描述

SELECT REPLACE('hello','lol','mmm')
FROM DUAL;

如果在将hello中没有找到lol，则不替换不处理不报错，返回原字符串。
在这里插入图片描述

SELECT UPPER('HelLo'),LOWER('HelLo')
FROM DUAL;

在这里插入图片描述
在Oracle中字符串里面区分大小写，下述代码只会找到last_name = 'King';的人，king和KING都找不到

SELECT last_name,salary
FROM employees
WHERE last_name = 'King';

在这里插入图片描述
所以可以使用UPPER()、LOWER()转换一下：

SELECT last_name,salary
FROM employees
WHERE LOWER(last_name) = 'King';

不过MySQL中不存在这个问题，MySQL字符串里面也不区分大小写

SELECT LEFT('hello',2),RIGHT('hello',3),RIGHT('hello',13)
FROM DUAL;

在这里插入图片描述

# LPAD:实现右对齐效果
# RPAD:实现左对齐效果
SELECT employee_id,last_name,LPAD(salary,10,'*')
FROM employees;

LPAD(salary,10,'*')表示salary占10位（小数点也要算），不够的用*在左边补齐
在这里插入图片描述

SELECT CONCAT('---',LTRIM('    h  el  lo   '),'***'),
TRIM('oo' FROM 'ooheollo')
FROM DUAL;

TRIM(字符串)用于去除字符串首尾空格，TRIM('oo' FROM 'ooheollo')用于去除ooheollo首尾的oo，LTRIM()、RTRIM()分别用于去除首、尾的空格
在这里插入图片描述

SELECT REPEAT('hello',4),LENGTH(SPACE(5)),STRCMP('abc','abe')
FROM DUAL;

SPACE()用于提供空格，STRCMP()用于字符串比大小，返回1表示前面一个字符串比后面的大
在这里插入图片描述

SELECT SUBSTR('hello',2,2),LOCATE('ll','hello')
FROM DUAL;

SUBSTR(字符串,索引位置（截取起始位置，要截取该位置的字符）,截取数量)
LOCATE()第一个字符串在第二个字符串中首次出现的位置
在这里插入图片描述
如果第二个字符串中没有第一个字符串，返回0
在这里插入图片描述

SELECT ELT(2,'a','b','c','d'),FIELD('mm','gg','jj','mm','dd','mm'),
FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg')
FROM DUAL;

在这里插入图片描述

SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) "compare"
FROM employees;

NULLIF(字符串1, 字符串2)比较两个字符串，如果相等，则返回NULL，否则返回字符串1
在这里插入图片描述

日期和时间函数

获取日期、时间

在这里插入图片描述

SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),
UTC_DATE(),UTC_TIME()
FROM DUAL;

在这里插入图片描述

SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0
FROM DUAL;

在这里插入图片描述

日期与时间戳的转换

在这里插入图片描述

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-10-01 12:12:32'),
FROM_UNIXTIME(1635173853),FROM_UNIXTIME(1633061552)
FROM DUAL;

在这里插入图片描述

获取月份、星期、星期数、天数等函数

在这里插入图片描述

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

在这里插入图片描述

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

在这里插入图片描述
在这里插入图片描述

日期的操作函数

在这里插入图片描述
type的取值与含义：
在这里插入图片描述

SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2021-05-12')
FROM DUAL;

在这里插入图片描述

时间和秒钟转换的函数

在这里插入图片描述

SELECT TIME_TO_SEC(CURTIME()),
SEC_TO_TIME(83355)
FROM DUAL;

在这里插入图片描述

计算日期和时间的函数

在这里插入图片描述
type的取值：
在这里插入图片描述

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),
DATE_ADD(NOW(),INTERVAL -1 YEAR),
DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;

DATE_ADD(NOW(),INTERVAL 1 YEAR),在当前时间加1年，DATE_ADD(NOW(),INTERVAL -1 YEAR)相当于DATE_SUB(NOW(),INTERVAL 1 YEAR)都是在当前时间减1年
在这里插入图片描述

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND)在2021-10-21 23:32:12上增加1分1秒，注意'1_1'不是数值，要用''引起来
在这里插入图片描述
在这里插入图片描述

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),32),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;

SUBTIME(NOW(),'1:1:3')当前时间减去1小时1分钟1秒
在这里插入图片描述
在这里插入图片描述

日期的格式化与解析

在这里插入图片描述
上述非GET_FORMAT函数中fmt参数常用的格式符及说明如下：
在这里插入图片描述
GET_FORMAT函数中date_type和format_type参数取值如下：
在这里插入图片描述
在这里插入图片描述
格式化：日期 —> 字符串
解析： 字符串 ----> 日期
此时我们谈的是日期的显式格式化和解析，之前，我们接触过隐式的格式化或解析

SELECT *
FROM employees
WHERE hire_date = '1993-01-13';

我们这里'1993-01-13'是个字符串，因为它满足Date格式，所以在查找时，底层会自动将这个字符串转换成Date格式进行匹配，这就是隐式格式化或解析

#格式化：
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'),
DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r')
FROM DUAL;

在这里插入图片描述

#解析：格式化的逆过程
SELECT STR_TO_DATE('2021-October-25th 11:37:30 Monday 1','%Y-%M-%D %h:%i:%S %W %w')
FROM DUAL;

在这里插入图片描述

SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;

在这里插入图片描述

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;

在这里插入图片描述

流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
在这里插入图片描述

#4.1 IF(VALUE,VALUE1,VALUE2)
SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details"
FROM employees;

在这里插入图片描述

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details",
salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal"
FROM employees;

在这里插入图片描述

#4.2 IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;

在这里插入图片描述

#4.3 CASE WHEN ... THEN ...WHEN ... THEN ... ELSE ... END
# 类似于java的if ... else if ... else if ... else
SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' 
			     WHEN salary >= 10000 THEN '潜力股'
			     WHEN salary >= 8000 THEN '小屌丝'
			     ELSE '草根' END "details",department_id
FROM employees;

在这里插入图片描述

SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' 
			     WHEN salary >= 10000 THEN '潜力股'
			     WHEN salary >= 8000 THEN '小屌丝'
			     END "details"
FROM employees;

在这里插入图片描述

#4.4 CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
# 类似于java的swich ... case...
/*

练习1
查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门,打印其工资的 1.3 倍数,
其他部门,打印其工资的 1.4 倍数

*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1
								     WHEN 20 THEN salary * 1.2
								     WHEN 30 THEN salary * 1.3
								     ELSE salary * 1.4 END "details"
FROM employees;

在这里插入图片描述

/*
练习2
查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门打印其工资的 1.3 倍数
*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1
								     WHEN 20 THEN salary * 1.2
								     WHEN 30 THEN salary * 1.3
								     END "details"
FROM employees
WHERE department_id IN (10,20,30);

在这里插入图片描述

加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。
在这里插入图片描述

# PASSWORD()在mysql8.0中弃用。
SELECT MD5('mysql'),SHA('mysql'),MD5(MD5('mysql'))
FROM DUAL;

在这里插入图片描述
MD5()和SHA()这两种加密方式是不可逆的，只能将明文加密成暗文，不能将暗文解密成明文

#ENCODE()\DECODE() 在mysql8.0中弃用。
SELECT ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql')
FROM DUAL;

MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
在这里插入图片描述

SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CURRENT_USER(),CHARSET('尚硅谷'),COLLATION('尚硅谷')
FROM DUAL;

在这里插入图片描述

其他函数

在这里插入图片描述

#如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2)
FROM DUAL;

在这里插入图片描述

SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;

在这里插入图片描述

#以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;

在这里插入图片描述

#BENCHMARK()用于测试表达式的执行效率
SELECT BENCHMARK(100000,MD5('mysql'))
FROM DUAL;

在这里插入图片描述

# CONVERT():可以实现字符集的转换
SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'gbk'))
FROM DUAL;

在这里插入图片描述

练习

# 1.显示系统时间(注：日期+时间)
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() #大家只需要掌握一个函数就可以了
FROM DUAL;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary * 1.2 "new salary"
FROM employees;


# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name) "name_length"
FROM employees
#order by last_name asc;
ORDER BY name_length ASC;


# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT"
FROM employees;


# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
# DATEDIFF(date1, date2)：date1与date2相差的天数
# TO_DAYS(date)：date距离0000年1月1日的天数
SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 "worked_years",DATEDIFF(CURDATE(),hire_date) "worked_days",
TO_DAYS(CURDATE()) - TO_DAYS(hire_date) "worked_days1"
FROM employees
ORDER BY worked_years DESC;

# 6.查询员工姓名，hire_date , department_id，满足以下条件：
#雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN (80,90,110)
AND commission_pct IS NOT NULL
#and hire_date >= '1997-01-01';  #隐式转换
#and  date_format(hire_date,'%Y-%m-%d') >= '1997-01-01';  # 显式转换操作，格式化：日期---> 字符串
#and  date_format(hire_date,'%Y') >= '1997';   # 显式转换操作，格式化
# STR_TO_DATE('1997-01-01','%Y-%m-%d')：将字符串1997-01-01转为%Y-%m-%d格式的日期
AND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d');# 显式转换操作，解析：字符串 ----> 日期

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) >= 10000;


# 8.做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3> 

# TRUNCATE(数字,从第几位开始截断)
SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0), ' monthly but wants ',TRUNCATE(salary * 3,0)) "Dream Salary"
FROM employees;


# 9.使用case-when，按照下面的条件：
/*job                  grade
AD_PRES              	A
ST_MAN               	B
IT_PROG              	C
SA_REP               	D
ST_CLERK             	E

产生下面的结果:
*/

SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
							 WHEN 'ST_MAN' THEN 'B'
							 WHEN 'IT_PROG' THEN 'C'
							 WHEN 'SA_REP' THEN 'D'
							 WHEN 'ST_CLERK' THEN 'E'
							 END "Grade"
FROM employees;


SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
							 WHEN 'ST_MAN' THEN 'B'
							 WHEN 'IT_PROG' THEN 'C'
							 WHEN 'SA_REP' THEN 'D'
							 WHEN 'ST_CLERK' THEN 'E'
							 ELSE "undefined" END "Grade"
FROM employees;

聚合函数

聚合函数是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

常见的聚合函数

一、AVG/SUM ：只适用于数值类型的字段（或变量），不会将null值计算在内

SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;

在这里插入图片描述
如下的操作没有意义：

SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;

在这里插入图片描述
二、MAX/MIN：适用于数值类型、字符串类型、日期时间类型的字段（或变量）

SELECT MAX(salary),MIN(salary)
FROM employees;

在这里插入图片描述

SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees;

在这里插入图片描述
三、COUNT：计算指定字段在查询结果中出现的个数（不包含NULL值的）

SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*)
FROM employees ;

在这里插入图片描述
计算表中有多少条记录：

  1. 方式1：COUNT(*)
  2. 方式2：COUNT(1)
  3. 方式3：COUNT(具体字段) : 不一定对！因为如果具体字段的值为null，是不会被计算在内的

如果需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段)哪个效率更高呢？

  1. 如果使用的是MyISAM存储引擎，则三者效率相同，都是O(1)
  2. 如果使用的是InnoDB存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段)

四、GROUP BY：

#需求：查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id

在这里插入图片描述

#需求：查询各个job_id的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;

在这里插入图片描述

#需求：查询各个department_id,job_id的平均工资，同一个部门同一个工种的平均工资
#方式1：
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY  department_id,job_id;
#方式2：这两种方式查询结果一样，表每一列排序不一样
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

在这里插入图片描述

SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;

在这里插入图片描述
这样会出错，导致数据紊乱，department_id为10的不止SA_REP这一个部门
结论：

  1. 结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY中。反之，GROUP BY中声明的字段可以不出现在SELECT中。
  2. 结论2：GROUP BY声明在FROM后面、WHERE后面，ORDER BY前面、LIMIT前面

再GROUP BY中使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

在这里插入图片描述
注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

#错误的：
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

五、HAVING：用来过滤数据

  1. 查询各个部门中最高工资比10000高的部门信息
#错误的写法：
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;

如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错，并且HAVING必须声明在GROUP BY的后面。

#正确的写法：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id# 根据department_id分组
HAVING MAX(salary) > 10000;# 分组后，查询每个组薪水大于10000的组

注意：开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

  1. 查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
#方式1：推荐，执行效率高于方式2.
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

#方式2：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);

注意：
（1）当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
（2）当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。

  1. WHERE与HAVING的对比
    （1）从适用范围上来讲，HAVING的适用范围更广。这是因为HAVING在GROUP BY之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选
    （2）如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING。这是因为在需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选

练习

#1.where子句可否使用组函数进行过滤?  No!

#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;


#4.选择具有各个job_id的员工人数
SELECT COUNT(employee_id)
FROM employees
GROUP BY job_id;

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）  #DATEDIFF
SELECT MAX(salary) - MIN(salary)
FROM employees;

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序 
SELECT d.department_name,d.location_id,COUNT(employee_id),AVG(salary)
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name,location_id


# 8.查询每个工种、每个部门的部门名、工种名和最低工资 
SELECT d.department_name,e.job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name,job_id

SQL底层执行原理

sql92语法：

SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

sql99语法：

SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

其中：
（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选
（6）order by：排序
（7）limit：分页
SQL语句的执行过程：#FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

  1. 首先先通过CROSS JOIN求笛卡尔积，相当于得到虚拟表vt（virtual table）1-1；
  2. 通过ON进行筛选，在虚拟表vt1-1的基础上进行筛选，得到虚拟表vt1-2；
  3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表vt1-2的基础上增加外部行，得到虚拟表vt1-3。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。当我们拿到了查询数据表的原始数据，也就是最终的虚拟表vt1 ，就可以在此基础上再进行WHERE阶段 。在这个阶段中，会根据vt1表的结果进行筛选过滤，得到虚拟表vt2。然后进入第三步和第四步，也就是GROUP和HAVING阶段 。在这个阶段中，实际上是在虚拟表vt2的基础上进行分组和分组过滤，得到中间的虚拟表vt3和vt4 。当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到SELECT和DISTINCT阶段 。

  1. 首先在SELECT阶段会提取想要的字段，然后在DISTINCT阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2 。
  2. 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是ORDER BY阶段 ，得到虚拟表vt6 。
  3. 最后在vt6的基础上，取出指定行的记录，也就是LIMIT阶段 ，得到最终的结果，对应的是虚拟表vt7 。

当然我们在写SELECT语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

CaraYQ
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL常用函数
cjwtsl的博客
08-23 334
nullif(arg1,arg2)：如果arg1=arg2返回null，否则返回arg1。ifnull(arg1,arg2)：如果arg1不是空，返回arg1，否则返回arg2。建立在第一范式的基础上，要求所有非主键字段完全依赖于主键，不能产生部分依赖。count(*)≈count(1)>count(主键)count(*)：MySQL对count(*)底层优化，count(0）if(test,t,f)：如果test是真，则返回t，否则返回f。left(str,x)：返回字符串str的左边的x个字符。
【sql】之函数：聚合函数 | sql底层执行原理
null
04-15 194
【sql】之函数：聚合函数 | sql底层执行原理聚合函数sql底层执行原理执行过程练习 聚合函数 -- 聚合函数 # 1.常见的聚合函数 SELECT AVG(salary),SUM(salary) FROM employees; SELECT MAX(salary),MIN(salary),MAX(last_name),MIN(last_name) FROM employees; #适用于数值类型、字符串、时间和日期类型的字段 #count：计算指定字段在查询结果中出现的个数 SELECT CO
mysql基础函数
Liu_yu135的博客
05-07 153
文章目录 基础函数 lower upper length substr concat replace ifnull round & ceil & floor uuid now year & month & day 转义字符 条件查询 distinct where like null between and limit order by 统计案例 入职统计 年薪统计 聚合 aggregation count max / min sum / avg 分组 group group
【精选】收藏多年的MySQL函数大全笔记，笔记一生一起走，那些日子不再有
qq_53058639的博客
11-27 879
于是后来我就自己去整理属于自己的资料，不断更新和备注，比如说一些语法或者其他的命令等等，过程虽然麻烦了点，但当需要查资料的时候，直接打开文件搜索关键词快速查找，同时也看到了自己的一些备注，基本上就是看到了就能立马用起来。今天就来分享一下我的资料库里面关于MySQL的常用函数，基本上囊括了平时要用的函数，它们已经陪我走过了不少年头了，风里来雨里去，缝缝补补又几年。为了让大家更加简洁明了地看懂它们，我已对老文件做了很多处理，去掉了很多只有我自己才能看得懂的东西，尽可能还原一份大家都能看得懂的东西。
MySQL分析函数实现
热门推荐
woqutechteam的博客
10-29 1万+
| MySQL分析函数实现 还好MySQL8.0已经实现了与Oracle相同的分析函数。 1. 实现rownum SET @rn:=0; SELECT @rn:=@rn+1 AS rownum ,e.* FROM emp e; 或者写成： SELECT @rn:=@rn + 1 AS rownum ,e.* FROM emp e ,(SELECT @rn:=0) c 2. 各种...
mysql由高到低函数,MySQL-常用函数
weixin_42525328的博客
03-18 226
一、操作符优先级展示了所有操作符的执行优先级，从高到低，同一行中的操作符优先级相同，相同优先级的情况下则从左到右执行如果想改变优先级执行顺序，则可以使用括号：mysql> SELECT 1+2*3; #7mysql> SELECT (1+2)*3;#9二、对比操作符#对比操作符的执行结果为true,false,null三种Between A and B #代表检查值是否在A和B之间C...
MySQL用户定义函数
04-08
UDF分为标量函数、聚合函数和表值函数，分别处理单行、多行数据或返回结果集。MySQL支持用C语言编写UDF，因此C++作为C的超集，同样适用。 创建MySQL UDF的步骤大致如下： 1. **设计函数接口**：你需要按照MySQL...
MySQL基于group_concat()函数合并多行数据
12-14
MySQL中的`GROUP_CONCAT()`函数是一个非常实用的聚合函数，它允许你在查询结果中将同一组内多个行的某个或某些列的值合并成单行的字符串，这对于数据的汇总和展示非常有用。这个函数的基本语法是： ```sql GROUP_...
02-oracle函数1
08-03
在SQL中，我们可以将函数分为两大类：单行函数和组函数。单行函数，正如其名，作用于单个数据行，返回单个值，如字符串操作、数字计算等。而组函数，如SUM、AVG、COUNT等，它们处理多行数据并返回一个汇总结果，通常...
mysql加强 布尔教育 14-26 共46集
05-07
与WHERE子句不同，HAVING用于筛选GROUP BY语句产生的结果集，允许我们基于聚合函数（如SUM, COUNT等）的结果进行过滤。 2. **FROM型子查询**（20集）：FROM子句中的子查询是一种复杂查询技术，它允许在FROM部分使用...
第07章_单行函数.docx
01-17
MySQL的内置函数主要分为两大类：单行函数和聚合函数（或分组函数）。单行函数对每一行数据执行一次操作，返回一个结果；聚合函数则用于对一组数据进行计算，如SUM、AVG等。 2. MySQL的单行函数 - 数值函数： - ...
Mysql内置函数全解析——Mysql初级(三)
复制乔布斯
10-19 1333
一、前言: 在关系型数据库使用的过程中，我们总会对DB里面的数据做各种不同形式的转换，字符串处理等基本操作。本文将会比较系统的学习总结Mysql中的各种内置函数。这是一个系列的文章，感兴趣的小伙伴可以关注一下哦！ 本文的行文思路是这样的，因为Mysql的内置函数主要分以下几个部分: 1、数学函数：这类函数只要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数、获取随机数函数等。 2、...
常用的文件系统、存储类型小整理
Hehuyi_In的博客
02-07 3334
最近接触到了五花八门的文件系统、存储类型，名词听得头大，趁假期整理学习一番~
开题报告达达宠物认领信息网站的设计与实现 已通过开题答辩的.docx
07-25
宠物认领信息网站的主要目的是为寻找宠物的失主和寻找新家的宠物提供一个在线的信息交流平台。通过这个平台，失主可以发布失宠信息，寻找走失或遗弃的宠物；而救助人或组织则可以发布待领养的宠物信息，为那些无家可归的宠物寻找一个永久的归宿。 这样的网站通常会提供一个详细的宠物数据库，包括照片、品种、年龄、健康状况等信息，以便让失主能够方便地找到自己的宠物。同时，网站还会提供一些相关的服务，如失主与救助人之间的在线交流、宠物匹配推荐、领养手续办理等，以帮助双方顺利完成宠物的认领和领养过程。 宠物认领信息网站的目的在于提高失宠找到宠物的几率，减少流浪宠物的数量，并为那些无家可归的宠物提供一个温馨的新家。同时，网站还能促进社会对流浪宠物的关注和保护，提高人们的动物保护意识。
开题报告创权法律服务系统 已通过开题答辩的.doc
07-25
进入20世纪80年代，我国加快了改革开放的步伐，市场经济体制已逐步形成，我国的经济发展受世人所瞩目，但法律法规的置后，使经济发展不规范问题暴露的相当严重，特别是社会整体信用的缺失，使消费环境不断恶化。而如今随着城镇化的高速推进，精神追求远不及物质发展迅速，维权问题也越来越成为人们十分关注的社会话题。 维权是指维护个人或群体的合法权益，范围包括人身损害、土地纠纷、 医疗事故、婚姻、家庭、继承等民事纠纷所进行的行政及司法诉讼。维护消费者合法权益关系着全社会每个人的利益，群众利益无小事。维护消费者合法权益正体现着“权为民所用，利为民所谋”。而现如今由于法律规则的不完善加上自身对法律知识的不了解，导致很多公民的权益问题在复杂的社会情况下无法得到妥善的解决，使得公民的财产和心理受到难以弥补的损失，严重威胁到社会的安全与稳定，公民之间的诚信与统一。因此更便捷、迅速地维护公民的合法权益，已经成为了一个刻不容缓去解决的社会问题。
Matlab实现豪猪优化算法CPO-TCN-Multihead-Attention多输入单输出回归预测算法研究.rar
最新发布
07-25
1.版本：matlab2014/2019a/2021a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
人力资源-6 团结一致共创未来新员工座谈会.pptx
07-25
人力资源-6 团结一致共创未来新员工座谈会.pptx
《AI大模型应用》--基于PepperBot，可以保留上下文的LLM群聊机器人实现.zip
07-25
上接OpenAI、文心一言，下接QQ、微信、Telegram 个人深耕AI大模型应用领域积累的成果，希望对您有所帮助。有大模型账号、环境问题、AI大模型技术应用落地方案等相关问题，欢迎详聊，能为您解决问题是我的荣幸！ 个人深耕AI大模型应用领域积累的成果，希望对您有所帮助。有大模型账号、环境问题、AI大模型技术应用落地方案等相关问题，欢迎详聊，能为您解决问题是我的荣幸！ 个人深耕AI大模型应用领域积累的成果，希望对您有所帮助。有大模型账号、环境问题、AI大模型技术应用落地方案等相关问题，欢迎详聊，能为您解决问题是我的荣幸！ 个人深耕AI大模型应用领域积累的成果，希望对您有所帮助。有大模型账号、环境问题、AI大模型技术应用落地方案等相关问题，欢迎详聊，能为您解决问题是我的荣幸！ 个人深耕AI大模型应用领域积累的成果，希望对您有所帮助。有大模型账号、环境问题、AI大模型技术应用落地方案等相关问题，欢迎详聊，能为您解决问题是我的荣幸！ 个人深耕AI大模型应用领域积累的成果，希望对您有所帮助。有大模型账号、环境问题、AI大模型技术应用落地方案等相关问题，欢迎详聊，能为您解决问题是我的荣幸！
开题报告传统糕点 已通过开题答辩的.docx
07-25
理论意义： 1、网页设计是信息传播的重要手段，通过合理的布局、图文搭配等设计元素，可以更好地传递传统糕点的信息。理论上，这涉及到传播学、信息传递模型等方面的理论研究，确保信息能够被受众准确、清晰地接收。 2、用户体验理论强调用户与网页之间的交互和感知。在传统糕点推广网页设计中，理论上的考虑应该包括用户友好的界面、易于导航的网站结构，以及引导用户深入了解传统糕点的设计元素。
掌握SQL单行函数：理解、差异与MySQL应用
单行函数，如SQL中的内置函数，是编程中的核心概念，它们的作用是将常用的操作封装起来，提高代码的复用性和执行效率。函数可分为内置函数和自定义函数，其中内置函数是预定义的，例如MySQL的ABS()、SIGN()、PI()等...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值