单行函数
单行函数:
-
操作数据对象
-
接收参数返回一个结果
-
只对一行进行变换
-
每行返回一个结果
-
可以嵌套
-
参数可以直一列或一个值
1.数值函数
1.1基本函数
| 函数 | 用法 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| SIGN | 返回x的符号. 正数返回1,负数返回-1,0返回0 |
| PI() | 返回圆周率的值 |
| CEIL(x) ,CEILNG(x) | 返回大于或等于某个值的最小整数 |
| FLOOR(x) | 返回小于或等于某个值的最大整数 |
| LEAST(e1,e2,e3,...) | 返回列表中的最小值 |
| GREATEST(e1,e2,e3,...) | 返回列表中的最大值 |
| MOD(x,y) | 返回x除以y后的余数 |
| RAND() | 返回一个0~1的随机数 |
| RAND(x) | 返回0~1的随机值,其中x值用做种子值,相同的x值会产生相同的随机数 |
| ROUND(x) | 返回一个对x的值进行四舍五入后,最接近于x的整数 |
| ROUND(x,y) | 返回一个对x四舍五入后,最接近于x的值,并且保留到小数点后y位 |
| TRUNCATE(x,y) | 返回数字x阶段为y位小数的结果 |
| SQRT(x) | 返回x的平方根.当x值为负数,返回NULL |
2. 字符串函数
| 函数名 | 用途 | 语法 |
|---|---|---|
CONACT | 连接两个或多个字符串 | CONCAT(str1, str2, ...) |
LENGTH | 返回字符串的字节长度 | 普通 |
CHAR_LENGTH or CHARACTER_LENGTH | 返回字符串的字符长度 | 普通 |
SUBSTRING() or SUBSTR() | 获取字符串中的子字符串 | SUBSTRING(str, start, length) |
UPPER() 和 LOWER() | 转换大小写 | 普通 |
TRIM() | 删除字符串左右的空格或字符串 | 普通或TRIM(s1 FROM s) |
LTRIM() RTRIM() | 删除字符串左边或右边的空额 | 普通 |
REPLEACE() | 替换字符串中的字符串 | REPLACE(str, old_str, new_str) |
INSTR() | 返回子字符串首次出现的位置（从 1 开始），如果没有找到则返回 0。 | INSTR(str, substr) |
CONCAT_WS() | 连接各个字符串,并且用指定字符连接 | CONCAT_WS(分隔符, str1, str2, ...) |
REVERSE() | 反转字符 | 普通 |
LEFT() 和 RIGHT() | 截取字符串左边或右边的子字符串 | LEFT(str, length) RIGHT(str, length) |
FORMAT() | 格式化字符串，通常用于数字的格式化。 | FORMAT(X, D)D是要保留的小数点 |
ASCII | 返回字符串第一字符的ASCII值 | 普通 |
3. 日期与时间函数
3.1获取日期,时间
select curdate(),curtime(),now(),sysdate(),current_dateFROM DUAL;
3.2日期与时间戳的转换
select UNIX_TIMESTAMP(),date_format(now(),'%Y-%m-%d %H:%i:%s'), FROM_UNIXTIME(UNIX_TIMESTAMP());
3.3获取月份,星期,星期数,天数等函数
select YEAR(now()),month(now()),day(now()),
dayofmonth(now()),dayofweek(now()),dayofyear(now()),
week(now()),hour(now()),minute(now()),second(now());3.4日期操作函数
select extract(day from CURDATE())
3,5时间和秒钟的转换
select TIME_TO_SEC('12:30:30'),SEC_TO_TIME(45030);
3.6计算日期和时间的函数
select now(),
date_add(now(),interval 1 year),
date_sub(now(),interval 1 year)
from dual;
#加减时间
3,7日期的格式化与解析
格式化: 日期-->字符串
解析: 字符串-->日期
默认有隐式的格式化或解析,当字符串满足某种格式可以之间转换成日期
| 函数 | 用法 |
|---|---|
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(date_type,format_type) | 按照字符串fmt格式化时间time值 |
| GET_FORMAT(date_type,format-type) | 返回日期字符串的显示格式 |
| STR_TO_DATE(str,fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
4.流程控制函数
select employees.last_name,salary,if(employees.salary>=6000,'high_salary','low_salary') as salary_level
from employees;
select last_name,ifnull(employees.commission_pct,0) as commission_pct
from employees;
select employees.last_name,employees.salary,case when employees.salary>15000 then 'diaobaole'when employees.salary>10000 then 'gao'when employees.salary>8000 then 'zhong'else 'di' end as salary_level
from employees
order by salary desc;#降序5.加密与解密函数
加密与解密的函数
#password()在MySQL8.0中弃用
select md5('123456'),sha1('123456')#不可逆
from dual;ENCODE和DECODE在8.0中弃用(可逆)
6.MySQL中的聚合函数
1. DATABASE()
-
用途：返回当前使用的数据库名称。
-
语法：
DATABASE() -
示例
：
SELECT DATABASE(); -- 输出：当前使用的数据库名称（例如 'testdb'）
2. USER()
-
用途：返回当前 MySQL 用户的名称和主机信息。
-
语法：
USER() -
示例
：
SELECT USER(); -- 输出：'root@localhost'
3. VERSION()
-
用途：返回当前 MySQL 服务器的版本信息。
-
语法：
VERSION() -
示例
：
SELECT VERSION(); -- 输出：'8.0.28'
4. CURRENT_USER()
-
用途：返回当前认证用户的名称和主机信息，通常与
USER()的区别在于认证机制。 -
语法：
CURRENT_USER() -
示例
：
SELECT CURRENT_USER(); -- 输出：'root@localhost'
5. CONNECTION_ID()
-
用途：返回当前数据库连接的 ID。
-
语法：
CONNECTION_ID() -
示例
：
SELECT CONNECTION_ID(); -- 输出：一个数字，表示当前连接的 ID
6. FOUND_ROWS()
-
用途：返回上一条查询（通常是
SELECT）中符合LIMIT限制的行数。 -
语法：
FOUND_ROWS() -
示例
：
SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 10; SELECT FOUND_ROWS(); -- 输出：总行数，忽略 LIMIT 限制
7. SYSDATE()
-
用途：返回当前的日期和时间，与
NOW()不同，SYSDATE()返回查询执行的时间，而不是连接建立时的时间。 -
语法：
SYSDATE() -
示例
SELECT NOW(), SYSDATE(), SLEEP(5), NOW(), SYSDATE();假设查询开始时是
2025-01-10 10:00:00，运行到SLEEP(5)时经过了 5 秒，结果可能是这样的：NOW() SYSDATE() SLEEP(5) NOW() SYSDATE() 2025-01-10 10:00:00 2025-01-10 10:00:00 5 秒延迟 2025-01-10 10:00:00 2025-01-10 10:00:05
8. LAST_INSERT_ID()
-
用途：返回最近一次插入操作的自动增量 ID。
-
语法：
LAST_INSERT_ID() -
示例
：
INSERT INTO users (name) VALUES ('John'); SELECT LAST_INSERT_ID(); -- 输出：最近插入的行的 auto_increment 值
9. TABLES()
-
用途：返回当前数据库中的所有表的列表（有些版本支持）。
-
语法：
SHOW TABLES -
示例
：
SHOW TABLES; -- 输出：当前数据库中的所有表的名称
10. COLUMNS()
-
用途：返回当前数据库中某个表的列信息。
-
语法：
SHOW COLUMNS FROM table_name -
示例
：
SHOW COLUMNS FROM employees; -- 输出：列名、数据类型、是否允许 NULL、键、默认值等信息
11. EXPLAIN
-
用途：返回查询的执行计划，帮助了解查询优化器如何执行查询。
-
语法：
EXPLAIN query -
示例
：
EXPLAIN SELECT * FROM employees WHERE salary > 50000; -- 输出：查询的执行计划，包含各个步骤的执行顺序、扫描的行数等信息
12. SHOW TABLE STATUS
-
用途：返回有关表的各种状态信息，如表的大小、行数、创建时间、更新时间等。
-
语法：
SHOW TABLE STATUS [FROM db_name] -
示例
：
SHOW TABLE STATUS FROM testdb; -- 输出：表的状态信息，如 `name`, `engine`, `row_format`, `data_length`, `index_length` 等
13. SHOW DATABASES
-
用途：列出所有数据库的名称。
-
语法：
SHOW DATABASES -
示例
SHOW DATABASES; -- 输出：所有数据库的名称列表
14. SHOW PROCESSLIST
-
用途：显示当前 MySQL 服务器上所有连接的进程和查询信息。
-
语法：
SHOW PROCESSLIST -
示例
SHOW PROCESSLIST; -- 输出：当前正在执行的查询和进程的信息
15. SHOW VARIABLES
-
用途：列出当前 MySQL 服务器的系统变量及其值。
-
语法：
SHOW VARIABLES -
示例
SHOW VARIABLES; -- 输出：MySQL 系统变量及其当前值（例如 `max_connections`, `datadir` 等）
16. SHOW STATUS
-
用途：显示 MySQL 服务器的运行状态信息。
-
语法：
SHOW STATUS -
示例
SHOW STATUS; -- 输出：MySQL 的运行状态信息，如连接数、查询数、缓存命中率等
如果需要统计表中的记录数,使用count(1),count(*),count(具体字段).哪个效率更高?
如果使用的是MyISAM存储引擎,则三者效率相同,都是o(1)
如果使用的是InnoDB存储引擎,则三者效率count(1)=count(*)>count(具体字段)
7.GROUP BY的使用
结论:
-
SELECT中出现的非组函数的字段必须声明在GROUP BY中
-
GROUP BY中声明的字段可以不出现在SELECT中
-
MySQL中使用WITH ROLLUP,生成总计
8.Having
8.1基本使用
*用来过滤数据的
查询各个部门中最高工资比10000高的部门信息
要求1:如果过滤条件中使用了聚合函数,则必须使用HAVING来代替WHERE,否则报错
要求2: HAVING必须声明在GROUP BY后面
要求3: 开发中,我们使用HAVING的前提是SQL中使用了GROUP BY
结论
-
当过滤条件中有聚合函数时,则此过滤条件必须声明在HAVING中
-
当过滤条件中没有聚合函数时,则此过滤条件声明在WHERE或HAVING中,建议声明在WHERE中
WHERE与HAVING的对比
-
从适用范围来讲,HAVING适用范围更广
-
如果过滤条件中没有聚合函数: 这种情况下.WHERE的执行效率要高于HAVING
9.SQL底层执行原理
9.1 SELECT语句的完整结构
SQL92语法
SELECT ........,.....,......
FROM .........,.........,......
WHERE 多表连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ......,.......
HAVING 包含聚合函数的过滤条件
LIMIT ..,......
SELECT ........,.....,......
FROM .........,.........,......JOIN...... ON........JOIN ....ON
WHERE 不包含聚合函数的过滤条件
GROUP BY ......,.......
HAVING 包含聚合函数的过滤条件
LIMIT ..,......