格式:select 函数名(实参列表) [from xxx];
1 、系统函数
1.1查看版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.22 |
+-----------+
1 row in set (0.27 sec)
1.2 查看当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.28 sec)mysql>
1.3查看当前数据库
mysql> select database();
+------------+
| database() |
+------------+
| dbname |
+------------+
1 row in set (0.29 sec)mysql>
2、length(str) --获取字节个数
mysql> select length('john') 长度;
+--------+
| 长度 |
+--------+
| 4 |
+--------+
1 row in set (0.28 sec)
3、concat 拼接字符串
mysql> select concat("ke", "_", "yang");
+---------------------------+
| concat("ke", "_", "yang") |
+---------------------------+
| ke_yang |
+---------------------------+
1 row in set (0.28 sec)mysql>
concat_ws()函数
功能:一次性指定分隔符
语法:concat_ws(separator,str1,str2,...)
说明:第一个参数指定分隔符 分隔符不能为空 如果为NULL 则返回值NULLmysql> select concat_ws("_", "hello", "world");
+----------------------------------+
| concat_ws("_", "hello", "world") |
+----------------------------------+
| hello_world |
+----------------------------------+
1 row in set (0.00 sec)mysql>
4、upper、lower
mysql> select upper('john') 大写;
+--------+
| 大写 |
+--------+
| JOHN |
+--------+
1 row in set (0.00 sec)mysql>
示例:
select concat(upper(last_name), lower(first_name)) 姓名 from employees;
5、substr/substring 截取字符串
1、substr(str, pos) 从pos截取到结尾,pos为负数,那么 表示就从倒数第几个开始截取到结尾。位置索引从1开始。
mysql> select substr("hello, world", 1);
+---------------------------+
| substr("hello, world", 1) |
+---------------------------+
| hello, world |
+---------------------------+
1 row in set (0.00 sec)mysql>
mysql> select substr("hello, world", -2);
+----------------------------+
| substr("hello, world", -2) |
+----------------------------+
| ld |
+----------------------------+
1 row in set (0.00 sec)mysql>
2、substr(str, pos, size) 从pos截取size长度
mysql> select substr("hello, world", 2,3);
+-----------------------------+
| substr("hello, world", 2,3) |
+-----------------------------+
| ell |
+-----------------------------+
1 row in set (0.00 sec)mysql>
3、substr(str from pos [for len]);
mysql> select substr("Hello, world" from 1 for 2);
+-------------------------------------+
| substr("Hello, world" from 1 for 2) |
+-------------------------------------+
| He |
+-------------------------------------+
1 row in set (0.00 sec)mysql>
mysql> select substr("Hello, world" from 2);
+-------------------------------+
| substr("Hello, world" from 2) |
+-------------------------------+
| ello, world |
+-------------------------------+
1 row in set (0.00 sec)mysql>
6、instr(str, substr); 返回子串的位值,子串不存在返回0;
mysql> select instr("你好,平安", "平安");
+------------------------------------+
| instr("你好,平安", "平安") |
+------------------------------------+
| 4 |
+------------------------------------+
1 row in set (0.30 sec)mysql> select instr("你好,平安", "张平安");
+---------------------------------------+
| instr("你好,平安", "张平安") |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)mysql>
7、trim
trim(str), 去掉str前后的空格
trim(substr from str), 去掉str所有的substr前缀和后缀。
mysql> select trim(" abcdddd ") "结果";
+---------+
| 结果 |
+---------+
| abcdddd |
+---------+
1 row in set (0.00 sec)mysql>
mysql> select length(trim(" abcdddd ")) "结果";
+--------+
| 结果 |
+--------+
| 7 |
+--------+
1 row in set (0.00 sec)mysql>
mysql> select trim("aa" from "aaaaabbbbbaaaa") "结果";
+--------+
| 结果 |
+--------+
| abbbbb |
+--------+
1 row in set (0.00 sec)mysql>
8、lpad,rpad
lpad(str, len, substr); 返回len长度字符串,如果字符串str不够len,使用substr填充。
rpad(str, len, substr);
mysql> select lpad("abc", 10, "*");
+----------------------+
| lpad("abc", 10, "*") |
+----------------------+
| *******abc |
+----------------------+
1 row in set (0.28 sec)mysql>
9、replace(str, subs tr, substrnew); 使用 substrnew 替换str中素有的substr
mysql> select replace("我的妻子", "妻子", "老婆") 结果;
+--------------+
| 结果 |
+--------------+
| 我的老婆 |
+--------------+
1 row in set (0.00 sec)
10、round 四舍五入
<1> round(value) 保留整数
<2> round(value, n) 保留n位小数
mysql> select round(1.65);
+-------------+
| round(1.65) |
+-------------+
| 2 |
+-------------+
1 row in set (0.29 sec)mysql> select round(1.5);
+------------+
| round(1.5) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)mysql> select round(-1.6);
+-------------+
| round(-1.6) |
+-------------+
| -2 |
+-------------+
1 row in set (0.00 sec)mysql>
mysql> select round(1.222, 2);
+-----------------+
| round(1.222, 2) |
+-----------------+
| 1.22 |
+-----------------+
1 row in set (0.00 sec)mysql> select round(1.238, 2);
+-----------------+
| round(1.238, 2) |
+-----------------+
| 1.24 |
+-----------------+
1 row in set (0.00 sec)mysql>
11、ceil/flooer/truncate/mod
ceil(val)向上取整,返回大于等于val的最小整数。
flooer(val) 向下取整,返回小于等于val的最大整数。
truncate(val, n),对value进行n位截断
mode(val1,val2) 求余
mysql> select ceil(1.8);
+-----------+
| ceil(1.8) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)mysql> select ceil(1.2);
+-----------+
| ceil(1.2) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)mysql>
mysql> select floor(-9.1);
+-------------+
| floor(-9.1) |
+-------------+
| -10 |
+-------------+
1 row in set (0.28 sec)mysql>
mysql> select truncate(10.123,2) 结果;
+--------+
| 结果 |
+--------+
| 10.12 |
+--------+
1 row in set (0.00 sec)mysql>
12、日期函数
12.1 now 返回当前系统日期和时间
mysql> select truncate(10,2)
-> \c
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-04-18 21:35:52 |
+---------------------+
1 row in set (0.04 sec)mysql>
12.2 curdate 返回当前系统日期,不包含时间(cur--->current)
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-04-18 |
+------------+
1 row in set (0.28 sec)mysql>
12.3 curtime 返回单曲时间,不包含日期
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 21:38:13 |
+-----------+
1 row in set (0.29 sec)mysql>
12.4 获取指定的部分,年(year), 月(month), 日(day),小时(hour),分钟(minute),秒(second)。
monthname,返回英文月份,例如 select monthname(now());
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2021 |
+-------------+
1 row in set (0.27 sec)mysql>
mysql> select year("1990-1-2");
+------------------+
| year("1990-1-2") |
+------------------+
| 1990 |
+------------------+
1 row in set (0.28 sec)mysql>
mysql> select month("1990-1-2");
+-------------------+
| month("1990-1-2") |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.28 sec)mysql>
mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| April |
+------------------+
1 row in set (0.28 sec)
12.5 str_to_date: 将日期格式的字符串转按照格式字符串解析成日期规范字符串。
例如:str_to_date("9-13-1999", "%m-%d-%Y");
格式符:Y(年),y(两位的年份),m(月份,固定显示两位), c(月份,自适应显示),d(日,固定显示两位), H(小时,24小时制),h(小时,12小时制),i(分钟), s(秒)
mysql> select str_to_date("9-13-1999", "%m-%d-%Y") 日期;
+------------+
| 日期 |
+------------+
| 1999-09-13 |
+------------+
1 row in set (0.00 sec)mysql>
使用场景:用户输入的是字符串,需要和数据库中的date类型比较:
select * from employees where hiredate = str_to_date('4-3 1992', '%c-%d %Y); ---->date可以和日期字符直接比较,如果日期字符串不规范,可以使用str_to_date转换。
12.6 date_format: 将日期按照格式字符串转换成字符串
例如 date_format(’2018/6/6', '%Y年%m月%d日‘) ===》 2018年06月06日。
mysql> select date_format('2018/6/6', "%Y年%m月%d日");
+--------------------------------------------+
| date_format('2018/6/6', "%Y年%m月%d日") |
+--------------------------------------------+
| 2018年06月06日 |
+--------------------------------------------+
1 row in set (0.30 sec)mysql>
mysql> select date_format('2018/6/6', '%m月%d日%Y年'); ===》格式符代表了从日期提取的值,和c语言printf不同,可以随便调整顺序
+--------------------------------------------+
| date_format('2018/6/6', '%m月%d日%Y年') |
+--------------------------------------------+
| 06月06日2018年 |
+--------------------------------------------+
1 row in set (0.00 sec)mysql>
13、流程控制函数
13.1 if函数
select if(expr1, expr2, expr3); 如expr1 为true,返回expr2, 否则返回expr3;
mysql> select if(10<5, "大", "小");
+------------------------+
| if(10<5, "大", "小") |
+------------------------+
| 小 |
+------------------------+
1 row in set (0.31 sec)mysql>
13.2 case函数
<1> switch case效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1;(注意点,使用值的时候不能有结尾分号,语句时需要分号)
when 常量2 then 要显示的值2或者语句2;
when 常量3 then 要显示的值3或者语句3;
。。。
else 要显示的值n或者语句n;
end
<2> 多重if效果
case
when 条件1 then 要显示的值1或者语句1;
when 条件2 then 要显示的值2或者语句2;
when 条件3 then 要显示的值3或者语句3;
。。。
else 要显示的值n或者语句n;
end
部门30,工资1.1倍,部门40, 工资1.2倍,其他部门,工资不变。
select salary 原始工资, department_id,
case department_id
when 30 then salary *1.1
when 40 then salary*1.2
else salary
end as 新工资 from employees;
select salary,
case
when salary>2000 then 'A'
when salary>1500 then 'B'
else 'C'
end “工资级别" from employees;