SQL-DQL（4）之单行函数

一. 单行函数：可以理解为向函数传入一个参数，返回一个值。

(分组函数：可以理解为向函数传入一组值，返回一个值。主要做统计使用，又称为统计函数，聚合函数，组函数。）

接下来主要讲的是单行函数的一些常见函数：

1.语法

select  函数名（实参列表）；

#学习函数，主要知道函数名以及它的功能即可。

2.常见函数：

√1.字符函数：

• lenth
• concat
• substr
• instr
• trim
• upper
• lower
• lpad
• rpad
• replace

√2.数学函数：

• round
• ceil
• floor
• truncate
• mod

√3.日期函数：

• now
• curdate
• curtime
• year
• month
• monthname
• day
• hour
• minute
• second
• str_to_date
• date_format
• 注意：解析格式符
  • %Y (四位的年份 eg:2018)
  • %y (二位的年份 eg:18)
  • %m(月份 ：01，02，03...11,12)
  • %c(月份 ：1，2，3...11,12)
  • %d(日：01，02...)
  • %H(小时：24小时制）
  • %h(小时：12小时制）
  • %i(分钟：00，01，02...59)
  • %s(秒：00，01，02...59)

√4.其它函数：

• version
• datebase
• user

√5.控制函数：

• if
• case

√2-1字符函数的练习

1. length（):获取参数的字节个数

 mysql> select length('hello,keen');
+----------------------+
| length('hello,keen') |
+----------------------+
|                   10 |
+----------------------+
1 row in set (0.00 sec)

mysql> select length("你好");
+------------------+
| length("你好")   |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

##查看我们用的字符编码
mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------------------+
| Variable_name            | Value                                                     |
+--------------------------+-----------------------------------------------------------+
| character_set_client     | utf8                                                      |
| character_set_connection | utf8                                                      |
| character_set_database   | utf8                                                      |
| character_set_filesystem | binary                                                    |
| character_set_results    | utf8                                                      |
| character_set_server     | utf8mb4                                                   |
| character_set_system     | utf8                                                      |
| character_sets_dir       | /usr/local/mysql-8.0.11-macos10.13-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+
8 rows in set (0.16 sec)

2. concat ()：拼接字符串

#将员工的姓和名拼接一起。
mysql> select concat(last_name,'_',first_name) as 姓名 from employees;
+-------------------+
| 姓名              |
+-------------------+
| K_ing_Steven      |
| Kochhar_Neena     |
| De Haan_Lex       |
| Hunold_Alexander  |
| Ernst_Bruce       |
| Austin_David      |
| Pataballa_Valli   |
| Lorentz_Diana     |
| Greenberg_Nancy   |
| Faviet_Daniel     |
| Chen_John         |
| Sciarra_Ismael    |
| Urman_Jose Manuel |
| Popp_Luis         |
| Raphaely_Den      |
| Khoo_Alexander    |
| Baida_Shelli      |
...
...

3.upper():转换成大写字母

4.lower():转换成小写字母

#将员工的姓转换成大写字母，名转换成小写字母,并拼接起来；
mysql> select concat(upper(last_name),lower(first_name)) as 姓名 from employees; 
+------------------+
| 姓名             |
+------------------+
| K_INGsteven      |
| KOCHHARneena     |
| DE HAANlex       |
| HUNOLDalexander  |
| ERNSTbruce       |
| AUSTINdavid      |
| PATABALLAvalli   |
| LORENTZdiana     |
| GREENBERGnancy   |
| FAVIETdaniel     |
| CHENjohn         |
| SCIARRAismael    |
| URMANjose manuel |
| POPPluis         |
...
...

5.substr()/substring()：截取从指定索引处开始的字符。注意索引从1开始。

#1.截取从指定索引处后面的所有字符
mysql> select substr('小百鸟真可爱',4) as output;
+-----------+
| output    |
+-----------+
| 真可爱    |
+-----------+
1 row in set (0.00 sec)

#2.截取从指定索引处开始指定字符长度的字符
mysql> select substr('小百鸟真可爱',1,3) as output;
+-----------+
| output    |
+-----------+
| 小百鸟    |
+-----------+
1 row in set (0.00 sec)

6.instr():返回字符串第一次出现的索引，如果找不到则返回零。

mysql> select instr('小百鸟长大了，真的长大了','长大') as output;
+--------+
| output |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)


mysql> select instr('小百鸟长大了，真的长大了','可爱') as output;
+--------+
| output |
+--------+
|      0 |
+--------+
1 row in set (0.07 sec)

7.trim():去除字符串的前后空格，或者指定的符号。

mysql> select trim(   '小变态'   ) as output;
+-----------+
| output    |
+-----------+
| 小变态    |
+-----------+
1 row in set (0.00 sec)

mysql> select trim('*' from '******小变态*********') as output;
+-----------+
| output    |
+-----------+
| 小变态    |
+-----------+
1 row in set (0.00 sec)

8.lpad():用指定字符实现左填充指定长度。

#用*号左填充 ‘I LOVE U!’直至15个字符长度。

mysql> select lpad('I LOVE U!',15,'*') as output;
+-----------------+
| output          |
+-----------------+
| ******I LOVE U! |
+-----------------+
1 row in set (0.00 sec)

9.rpad():用指定字符实现右填充指定长度。

#用*号右填充 ‘I LOVE U!’直至15个字符长度。
mysql> select rpad('I LOVE U!',15,'*') as output;
+-----------------+
| output          |
+-----------------+
| I LOVE U!****** |
+-----------------+
1 row in set (0.00 sec)

10. replace():替换

mysql> select replace('谢霆锋爱上张柏芝','张柏芝','王菲') as output;
+-----------------------+
| output                |
+-----------------------+
| 谢霆锋爱上王菲        |
+-----------------------+
1 row in set (0.00 sec)

√2-2数学函数的练习

1. round():四舍五入

mysql> select round(5.20) as result;
+--------+
| result |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

mysql> select round(5.56) as result;
+--------+
| result |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

2. ceil():向上取整，返回 >= 该参数的最小整数。

3. floor():向下取整，返回 <= 该参数的最大整数。

mysql> select ceil(1.3) as result;
+--------+
| result |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

mysql> select floor(1.3) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

4. truncate ():截断

#只留下小数点后一位，其他的都简单粗暴地截除。
mysql> select truncate(1.53,1) as result;
+--------+
| result |
+--------+
|    1.5 |
+--------+
1 row in set (0.00 sec)

5. mod():取模（余）

mysql> select mod(5,3) as result;
+--------+
| result |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

#取模的结果，符号与被除数的一致。
mysql> select mod(-5,3) as result;
+--------+
| result |
+--------+
|     -2 |
+--------+
1 row in set (0.00 sec)

mysql> select mod(5,-3) as result;
+--------+
| result |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

mysql> select mod(-5,-3) as result;
+--------+
| result |
+--------+
|     -2 |
+--------+
1 row in set (0.00 sec)

√2-3 日期函数的练习

1. now():返回当前的系统日期+时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-08-20 14:37:24 |
+---------------------+
1 row in set (0.29 sec)

2. curdate():返回当前系统日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2018-08-20 |
+------------+
1 row in set (0.00 sec)

3.curtime():返回当前系统时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:37:54  |
+-----------+
1 row in set (0.00 sec)

4.可以获取指定的部分，年，月，日，时，分，秒。

mysql> select year(now()) 年;
+------+
| 年   |
+------+
| 2018 |
+------+
1 row in set (0.08 sec)

mysql> select year('1995-3-24') 年;
+------+
| 年   |
+------+
| 1995 |
+------+
1 row in set (0.01 sec)

mysql> select month(now()) 月;
+------+
| 月   |
+------+
|    8 |
+------+
1 row in set (0.00 sec)

mysql>  select monthname(now()) 月;
+--------+
| 月     |
+--------+
| August |
+--------+
1 row in set (0.00 sec)


mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|          14 |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            47 |
+---------------+
1 row in set (0.00 sec)

mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
|            23 |
+---------------+
1 row in set (0.00 sec)

5. str_ to_date():将日期格式的字符转换成指定格式日期

mysql> select str_to_date('1996-6-9','%Y-%c-%d') as output;
+------------+
| output     |
+------------+
| 1996-06-09 |
+------------+
1 row in set (0.00 sec)

mysql> #将日期如：8-20-2018 转换成系统默认的格式日期,告诉机器你这个日期是什么格式的，它就可以解析出来。
mysql> select str_to_date('8-20-2018','%m-%d-%Y') as date;
+------------+
| date       |
+------------+
| 2018-08-20 |
+------------+
1 row in set (0.00 sec)

#查询入职日期为“1992-4-3”的员工信息。
mysql> select * from employees where hiredate = str_to_date('1992-4-3','%Y-%c-%d');
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email    | phone_number | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven     | K_ing     | SKING    | 515.123.4567 | AD_PRES | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
|         101 | Neena      | Kochhar   | NKOCHHAR | 515.123.4568 | AD_VP   | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         102 | Lex        | De Haan   | LDEHAAN  | 515.123.4569 | AD_VP   | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         103 | Alexander  | Hunold    | AHUNOLD  | 590.423.4567 | IT_PROG |  9000.00 |           NULL |        102 |            60 | 1992-04-03 00:00:00 |
|         104 | Bruce      | Ernst     | BERNST   | 590.423.4568 | IT_PROG |  6000.00 |           NULL |        103 |            60 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
5 rows in set (0.06 sec)

6. date_format:将日期转换成字符

mysql> select date_format(now(),'%Y年%m月%d日')  as date;
+-------------------+
| date              |
+-------------------+
| 2018年08月20日    |
+-------------------+
1 row in set (0.07 sec)

mysql> #查询有奖金的员工名和入职日期(xx月/xx日/xx年）
mysql> select last_name,date_format(hiredate,'%m月/%d日/%Y年') as date ,commission_pct from employees where commission_pct is not null;
+------------+---------------------+----------------+
+------------+---------------------+----------------+
| last_name  | date                | commission_pct |
+------------+---------------------+----------------+
| Russell    | 12月/23日/2002年    |           0.40 |
| Partners   | 12月/23日/2002年    |           0.30 |
| Errazuriz  | 12月/23日/2002年    |           0.30 |
| Cambrault  | 12月/23日/2002年    |           0.30 |
| Zlotkey    | 12月/23日/2002年    |           0.20 |
| Tucker     | 03月/05日/2014年    |           0.30 |
| Bernstein  | 03月/05日/2014年    |           0.25 |
| Hall       | 03月/05日/2014年    |           0.25 |
| Olsen      | 03月/05日/2014年    |           0.20 |
| Cambrault  | 03月/05日/2014年    |           0.20 |
| Tuvault    | 03月/05日/2014年    |           0.15 |
| K_ing      | 03月/05日/2014年    |           0.35 |
| Sully      | 03月/05日/2014年    |      
...
...

√2-4其它函数

mysql> select version();#当前使用的mysql 的版本
+-----------+
| version() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.06 sec)

mysql> select database();#当前使用的数据库
+-------------+
| database()  |
+-------------+
| myemployees |
+-------------+
1 row in set (0.00 sec)

mysql> select user();#当前使用的用户
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

5.流程控制函数

1.if（）：类似三元运算符的感觉。

mysql> select if(4 > 5,'大于','小于');
+-----------------------------+
| if(4 > 5,'大于','小于')     |
+-----------------------------+
| 小于                        |
+-----------------------------+
1 row in set (0.00 sec)

mysql> #查询员工的名字，奖金，如果有奖金的就打出“好开心，有钱了”否则打出“哎，又 没有奖金”；
mysql> select last_name,commission_pct,if(commission_pct is null,'哎，又没有奖金','好开心，有钱了') from employees;
+-------------+----------------+----------------------------------------------------------------------------+
| last_name   | commission_pct | if(commission_pct is null,'哎，又没有奖金','好开心，有钱了')               |
+-------------+----------------+----------------------------------------------------------------------------+
| K_ing       |           NULL | 哎，又没有奖金                                                             |
| Matos       |           NULL | 哎，又没有奖金                                                             |
| Vargas      |           NULL | 哎，又没有奖金                                                             |
| Russell     |           0.40 | 好开心，有钱了                                                             |
| Partners    |           0.30 | 好开心，有钱了                                                             |
| Errazuriz   |           0.30 | 好开心，有钱了                                                             ...
...                                                                           

2. case() :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 倍
其他部门为原工资。
*/

mysql> select salary as 原工资, department_id,
    -> case department_id
    -> when 30 then salary*1.1
    -> when 40 then salary*1.2
    -> when 50 then salary*1.3
    -> else salary
    -> end as 新工资
    -> from employees;
+-----------+---------------+-----------+
| 原工资    | department_id | 新工资    |
+-----------+---------------+-----------+
|  24000.00 |            90 |  24000.00 |
|  17000.00 |            90 |  17000.00 |
|  17000.00 |            90 |  17000.00 |
|   9000.00 |            60 |   9000.00 |
|   6000.00 |            60 |   6000.00 |
|   7800.00 |           100 |   7800.00 |
|   6900.00 |           100 |   6900.00 |
|  11000.00 |            30 |  12100.00 |
|   3100.00 |            30 |   3410.00 |
|   2900.00 |            30 |   3190.00 |
|   2700.00 |            50 |   3510.00 |
|   2400.00 |            50 |   3120.00 |
|   2200.00 |            50 |   2860.00 |
|   4400.00 |            10 |   4400.00 |
|  13000.00 |            20 |  13000.00 |
|   6000.00 |            20 |   6000.00 |
|   6500.00 |            40 |   7800.00 |
...
...

注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！