MySQL（六）常见函数——单行函数

冷不防 2021-06-24 16:11 663阅读 0赞

> 文章内容索引：  
> 字符函数：length()、 concat()、upper() 、lower()、substr()或substring()、 instr()、trim()、lpad()和rpad()、replace() ；  
> 数学函数：round()、ceil()和floor()、truncate()、mod()；  
> 日期函数：now()、curdate()、curtime()、年、月、日、小时、分钟、秒、str\_to\_date()和date\_format()；  
> 其他函数：version()、database()、user()；  
> 流程控制函数： if函数、switch case 函数

# MySQL常见函数 #

**概念：**  
类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名  
**好处：**  
1 隐藏了实现细节  
2 提高了代码的重用性  
**调用:**  
`select 函数名(实参列表) [from 表名];`  
**关注：**  
1.方法的名字  
2.函数的功能

分类：

1.  单行函数，例如concat、length、ifnull
2.  分组函数，  
    功能：做统计使用，又称为统计函数、聚合函数、组函数

## 单行函数 ##

### 一、字符函数 ###

#### ① length() ####

length()：获取参数值的字节个数。

`SELECT LENGTH("llj");`  
![在这里插入图片描述][20190529110534816.png]  
`SELECT LENGTH("刘伟佳LLJ");`  
![在这里插入图片描述][20190529110613557.png]

#### ② concat() ####

concat()：拼接字符串。

SELECT 
      CONCAT(first_name, "_", last_name) 
    FROM
      employees ;

![在这里插入图片描述][20190529112015493.png]

#### ③ upper() 、lower() ####

upper() 、lower()：把字符串转换成大写、小写。  
`SELECT UPPER("I love you");`：  
![在这里插入图片描述][20190529112200728.png]  
`SELECT LOWER("I love you");`：  
![在这里插入图片描述][20190529112214186.png]  
**示例：** 将姓变大写，名变小写，拼接起来。

SELECT 
      CONCAT(LOWER(first_name), UPPER(last_name)) 
    FROM
      employees ;

![在这里插入图片描述][20190529112243874.png]

#### ④ substr()或substring() ####

`substr()`或`substring()`用于截取字符串，两者作用都是一样的。写哪一种都可以。  
substr()的重载：

*  截取从指定索引开始处后面所有的字符。  
    例如：`SELECT SUBSTR("liuweijia",5);`结果为“eijia”。注意SQL中的索引下标从1开始。
 *  截取从指定索引处指定字符长度的字符  
    `SELECT SUBSTR("liuweijia",1,3);`结果是“liu”。

**示例：姓名中首字符大写，其他字符小写然后用“\_”拼接，显示出来。**

SELECT 
      CONCAT(
        UPPER(SUBSTR(last_name, 1, 1)),
        "_",
        LOWER(SUBSTR(last_name, 2))
      ) AS out_put 
    FROM
      employees ;

![在这里插入图片描述][2019052911381390.png]

#### ⑤ instr() ####

返回子串在字符串中第一次出现的起始索引。如果找不到，就返回0。  
例如：

SELECT INSTR("liuweijia","jia") AS out_put;

输出结果为：  
![在这里插入图片描述][20190529114418713.png]

#### ⑥ trim() ####

去掉两端的空格或者自定义的字符。  
例如：

SELECT TRIM(" liuweijia ") AS out_put;

结果为“`liuweijia`”；

SELECT TRIM(" liu wei jia ") AS out_put;

结果为：“`liu wei jia`”；

SELECT TRIM('a' FROM"aaaaaaLIUaaaWEIaaaJIAaaaa") AS out_put;

结果为：“`LIUaaaWEIaaaJIA`”。

#### ⑦ `lpad()`和`rpad()` ####

`lpad()`：左填充：用**指定字符**作为前缀填充**字符串**到最终**指定长度**。  
例如：

SELECT LPAD('veeja',10,'-');

结果为“`-----veeja`”。  
如果字符串本身长度就大于最终指定长度。就会截断初始字符串，例如：

SELECT LPAD('veeja',3,'-');

结果为：“`vee`”。

`rpad()`为右填充，和左填充类似。不再赘述。

#### ⑧ replace() ####

replace()：替换字符串。  
例如：

SELECT REPLACE('liuweijialilingjie','i','-') AS out_put;

结果为“`l-uwe-j-al-l-ngj-e`”。

### 2.数学函数 ###

#### ① round() ####

**round()：四舍五入。可以指定参数来保留小数点后的位数。**  
示例：  
`SELECT ROUND(1.6);`结果为“`2`”；  
`SELECT ROUND(1.4);`结果为“`1`”；  
`SELECT ROUND(-2.4);`结果为“`-2`”；  
`SELECT ROUND(-3.9);`结果为“`-4`”；  
`SELECT ROUND(- 1.568, 2);`结果为“`-1.57`”；  
`SELECT ROUND(8.8472, 1);`结果为“`8.8`”。

#### ② ceil()和floor() ####

**ceil()：向上取整，返回大于等于改参数的最小整数。**  
**floor()：向下取整，返回小于等于改参数的最大整数。**  
示例：  
`SELECT CEIL(1.00);`结果为“`1`”；  
`SELECT CEIL(1.001);`结果为“`2`”；  
`SELECT CEIL(2.6);`结果为“`3`”；  
`SELECT CEIL(-1.3);`结果为“`-1`”。

`SELECT FLOOR(1.00);`结果为“`1`”；  
`SELECT FLOOR(9.9);`结果为“`9`”；  
`SELECT FLOOR(-9.9);`结果为“`-10`”。

#### ③ truncate() ####

**truncate()：截断，保留小数点后指定长度位数。**  
示例：  
`SELECT TRUNCATE(1.66666, 2);`结果为“`1.66`”。

#### ④ mod() ####

**mod()：取余。**  
`SELECT MOD(10,3);`结果为“`1`”；  
`SELECT MOD(-10,3);`结果为“`-1`”；  
`SELECT MOD(10,-3);`结果为“`1`”；  
`SELECT MOD(-10,-3);`结果为“`-1`”。

### 3.日期函数 ###

#### ① now()、curdate()、curtime() ####

**now()：返回当前系统日期+时间。**  
`SELECT NOW();`  
![在这里插入图片描述][20190530113328688.png]  
**curdate()：返回当前系统日期，不包含时间。**  
`SELECT CURDATE();`  
![在这里插入图片描述][2019053011350090.png]  
**curtime()：返回当前系统时间，不包含日期。**  
`SELECT CURTIME();`  
![在这里插入图片描述][20190530113622509.png]

#### ② 获取指定的部分，年、月、日、小时、分钟、秒 ####

例如：

SELECT YEAR(NOW()) AS 年;
    SELECT YEAR('1997-03-15') AS 年;
    SELECT YEAR(hiredate) AS 年 FROM employees;
    
    SELECT MONTH(NOW()) AS 月 ;
    SELECT MONTHNAME(NOW()) AS 月;
    
    SELECT DAY(NOW()) AS 日;
    
    SELECT HOUR(NOW()) AS 小时;
    
    SELECT MINUTE(NOW()) AS 分钟;
    
    SELECT SECOND(NOW()) AS 秒;

#### ③ str\_to\_date()和date\_format() ####

`str_to_date()`：将日期格式的字符串转换成指定格式的日期  
`date_format()`：将日期转换成字符

SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');

结果是：1999-09-13

SELECT DATE_FORMAT('2019/5/30','%Y年%m月%d日');

结果是：2019年05月30日  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70]

##### 示例：查询入职日期为1992年4月3日的员工信息，给定参数格式（`4-3 1992`） #####

SELECT 
      * 
    FROM
      employees 
    WHERE hiredate = STR_TO_DATE('4-3 1992', '%c-%d %Y') ;

![在这里插入图片描述][20190530120430956.png]

##### 示例：查询有奖金的员工名和入职日期（xx月/xx日 xx年） #####

SELECT 
      last_name,
      DATE_FORMAT(hiredate, '%m月/%d日 %y年') AS 入职日期 
    FROM
      employees 
    WHERE commission_pct IS NOT NULL ;

![在这里插入图片描述][20190530121116575.png]

### 4.其他函数 ###

#### ① version() ####

查看当前MySQL版本。

SELECT VERSION();

![在这里插入图片描述][20190530121553758.png]

#### ② database() ####

查看当前使用的数据库。

SELECT DATABASE();

![在这里插入图片描述][20190530121639927.png]

#### ③ user() ####

查看当前的用户。

SELECT USER();

![在这里插入图片描述][20190530121710561.png]

### 5.流程控制函数 ###

#### ① if函数 ####

类似于三元运算符，可以实现`if else`的效果。  
例如：

*  一：  
    `SELECT IF(10>5,'大','小');`  
    结果为“`大`”；
 *  二：
    
        SELECT 
          last_name,
          commission_pct AS 奖金率,
          IF(
            commission_pct IS NULL,
            '没奖金',
            '有奖金'
          ) AS 奖金情况 
        FROM
          employees ;
    
    ![在这里插入图片描述][20190530122543782.png]

#### ② switch case 函数 ####

##### 第一种情况 #####

类似于java中的`switch case`函数。

**语法：**

case 要判断的字段或者表达式
    	when 常量1 then (要显示的值1或语句1;)
    	when 常量2 then (要显示的值2或语句2;)
    	...
    	else 要显示的值n或语句n;
    end

###### 示例： ######

查询员工的工资。  
**要求：**  
部门号为30的，显示的工资为1.1倍，  
部门号为40的，显示的工资为1.2倍，  
部门号为50的，显示的工资为1.3倍，  
其他部门，显示为原工资。

SELECT 
      last_name,
      department_id,
      CASE
        salary 
        WHEN department_id = 30 THEN salary * 1.1 
        WHEN department_id = 40 THEN salary * 1.2 
        WHEN department_id = 50 THEN salary * 1.3 
        ELSE salary 
      END AS 工资
    FROM
      employees ;

![在这里插入图片描述][2019053012420061.png]

##### 第二种情况 #####

类似于java中的`多重if`语句。

**语法：**

case
    	when 条件一 then 要显示的值1或者执行的语句1
    	when 条件一 then 要显示的值1或者执行的语句1
    	...
    	else 要显示的值n或者执行的语句n
    end

###### 示例： ######

查询员工的工资的工资情况。  
要求：  
如果工资>20000，显示A级别；  
如果工资>15000，显示B级别；  
如果工资>10000，显示C级别；  
否则，显示D级别。

SELECT 
      last_name,
      salary,
      CASE
        WHEN salary > 20000 THEN 'A' 
        WHEN (salary < 20000 AND salary > 15000) THEN 'B' 
        WHEN (salary < 15000 AND salary > 10000) THEN 'C' 
        ELSE 'D' 
      END AS 工资级别 
    FROM
      employees;

![在这里插入图片描述][20190530125648824.png]

--------------------

end.

[20190529110534816.png]: /images/20210615/b46cd69539e34d87b5bbbf27eadcbe5e.png
[20190529110613557.png]: /images/20210615/a58288eb2c8446a58f0fa50bbd81e920.png
[20190529112015493.png]: /images/20210615/85b7f2afbe344ce1bddcb59a0994ac2b.png
[20190529112200728.png]: /images/20210615/d94ea61844314701998bde9e76373fd1.png
[20190529112214186.png]: /images/20210615/b3f743f5cc1e4b34928b0cfc149418c8.png
[20190529112243874.png]: /images/20210615/bb47ae4544274248bf3a5b79b1926bec.png
[2019052911381390.png]: /images/20210615/41075ebd6149441fa7080304a0a807e9.png
[20190529114418713.png]: /images/20210615/ce8b8620dea544aa95126db6aee028a6.png
[20190530113328688.png]: /images/20210615/6cb5c46faa334295a242a2de6e7dfa5c.png
[2019053011350090.png]: /images/20210615/0bda9474c6aa46daa8f6fa9a257e3b1e.png
[20190530113622509.png]: /images/20210615/27158527b94540f7b1f15bb9189ae5b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70]: /images/20210615/3ebebc49b86b4f85935156a4dc157ca6.png
[20190530120430956.png]: /images/20210615/445a0ba2735b499ba4d588d707507bd4.png
[20190530121116575.png]: /images/20210615/dbc2dc4536174d08b2203ef3550beb94.png
[20190530121553758.png]: /images/20210615/0165cadb570b4176bf4c914a7e8bd901.png
[20190530121639927.png]: /images/20210615/c3f89b4474ad4903b9b999c36d8b3c37.png
[20190530121710561.png]: /images/20210615/7cb00e903bed4915a77a48583640fba7.png
[20190530122543782.png]: /images/20210615/638cedaf975c4e1fa85dcdf3673a8f6b.png
[2019053012420061.png]: /images/20210615/8d31668fb1ca489e8014ba1ab77111e5.png
[20190530125648824.png]: /images/20210615/ed691d88499644ddb6f4d5f0f80b0f15.png