常用函数

格式：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 则返回值NULL

mysql> 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;