mysql 单行变多行

本文课件内容均来自于b站尚硅谷，大家想要详细的解释可以移步b站
MySQL所有笔记链接

MySQL单行函数

数值型函数

其实同C语言math标准库存中的函数类似，简单大致看一下就好了，用到了再查。

进制之间的转换

函数	用法
BIN(x)	返回x的二进制编码
HEX(x)	返回x的十六进制编码
OCT(x)	返回x的八进制编码
CONV(x,f1,f2)	返回f1进制数变成f2进制数

我们输入的X那么肯定是十进制的数值了

SELECT BIN(10), HEX(10), OCT(10),CONV(10,2,8)
FROM employees;
+---------+---------+---------+--------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1010    | A       | 12      | 2            |
+---------+---------+---------+--------------+

字符串函数

• LENGTH : 计算字符串长度函数，返回字符串的字节长度.

SELECT LENGTH('name'),LENGTH('数据库');
+----------------+---------------------+
|LENGTH('name')  | LENGTH('数据库')    |
+----------------+---------------------+
|              4 |                   9 |
+----------------+---------------------+

• CONCAT：将所有的参数连接成为字符串，如果有一个参数为NULL的话，返回值就是NULL。

SELECT CONCAT('MySQL','5.7'),CONCAT('MySQL',NULL);
+-----------------------+----------------------+
| CONCAT('MySQL','5.7') | CONCAT('MySQL',NULL) |
+-----------------------+----------------------+
| MySQL5.7              | NULL                 |
+-----------------------+----------------------+

函数	用法
ASCII(S)	返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s)	返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)	返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,…,sn)	连接s1,s2,…,sn为一个字符串
CONCAT_WS(x, s1,s2,…,sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x
INSERT(str, idx, len, replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b)	用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(str,n)	返回字符串str最左边的n个字符
RIGHT(str,n)	返回字符串str最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)	去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
SPACE(n)	返回n个空格
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同
LOCATE(substr,str)	返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)	返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s)	返回s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

样🌰

注意，在MYSQL中字符串的位置同以往的语言不同，是从1开始的

例如使用FIELD函数和FIND_IN_SET函数来说的，没有找到 自然是返回0

SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma')
    -> FROM DUAL;
+----------------------------------+-----------------------------------+
| FIELD('mm','hello','msm','amma') | FIND_IN_SET('mm','hello,mm,amma') |
+----------------------------------+-----------------------------------+
|                                0 |                                 2 |
+----------------------------------+-----------------------------------+

获取日期、时间

函数	用法
CURDATE() ，CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期和时间
UTC_DATE()	返回UTC（世界标准时间）日期
UTC_TIME()	返回UTC（世界标准时间）时间

举🌰演示一下：

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;

流程控制函数

很像三目运算啊🤔

函数	用法
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END	相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END	相当于Java的switch…case…

举🌰

通过工资是否是大于6000来加上高工资和低工资的tag

SELECT last_name, salary, IF(salary >= 6000,'高工资','低工资') "details"	#details 新建一行的名字
FROM employees;

根据薪资的增涨比例来确定最终的年薪资，如果比例为null，直接看作是0对待

SELECT last_name, commission_pct, IF(commission_pct IS NOT NULL, commission_pct, 0) "details",
salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_salary"
FROM employees;

IFNULL 可以看作是IF(VALUE,VALUE1,VALUE2)的一种特殊的情况

上面书写的函数可以用IFNULL重写

CASE WHEN … THEN

根据薪水的多少进行称呼,白骨精： 白领、骨干、精英

SELECT last_name, salary, CASE WHEN salary >= 15000 THEN "白骨精"
															 WHEN salary >= 10000 THEN "潜力股"
															 WHEN salary >= 8000 THEN "小屌丝"
															 ELSE "草根" END "BIEMING"
FROM employees;

练习题目1:

查询部门号为10，20，30的员工信息，如果部门号为10则打印工资的1.1倍

如果部门号为20则打印工资的1.2倍

如果部门号为30则打印工资的1.4倍

SELECT last_name, employee_id,department_id,salary, CASE department_id WHEN 10 THEN salary * 1.1
																																			WHEN 20 THEN salary * 1.2
																																			WHEN 30 THEN salary * 1.3
																																			ELSE salary * 1.4 END "alias"
FROM employees;

如果是限定只能查找部门号在10 20 30之间的数据的话，需要在最后加上WHERE进行过滤

SELECT last_name, employee_id,department_id,salary, CASE department_id WHEN 10 THEN salary * 1.1
																																			WHEN 20 THEN salary * 1.2
																																			WHEN 30 THEN salary * 1.3
																																			ELSE salary * 1.4 END "alias"
FROM employees
WHERE department_id IN (10, 20, 30)

单行函数的练习题

1. 显示系统时间（日期 + 时间）

#多种显示时间的方式
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP()
FROM DUAL;

2. 查询员工号， 姓名， 工资，工资提高百分之20之后的结果（别名： new salary）

SELECT employee_id, last_name, salary, salary * 1.2 "new salary"
FROM employees;

3. 将员工的姓名按照name_length排序，并且写出姓名的长度（length）

SELECT last_name, LENGTH(last_name) "name_length"
FROM employees
ORDER BY name_length ASC;

4. 查询员工id, last_name, salary,作为 一个列输出，名字为 OUT_PUT

SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT"
FROM employees;

5. 查询公司各个员工工作的年数，工作的天数， 按照工作的年数进行降序排列

我们就是通过找到当前的时间和入职的时间进行相减就可以了

SELECT employee_id, DATEDIFF(CURTIME(),hire_date) "worked_days", DATEDIFF(CURTIME(),hire_date) / 365 "worked_years"
FROM employees
ORDER BY worked_years ASC;

6. 查询员工姓名， hire_date, department_id, 满足以下条件：

• 雇用时间在1997年之后，department_id为80 或者是 90 或者是 110
• commission_pct不为空

SELECT last_name, hire_date, department_id
FROM employees
WHERE department_id IN (80, 90, 110)
AND commission_pct IS NOT NULL
AND hire_date >= '1997-01-01'

7. 查询公司中入职超过10000天的员工姓名，入职时间

SELECT last_name, hire_date, department_id
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) >= 10000;

8. 做一个查询，产生下面的结果(去除小数)
   <last_name> earns monthly but want <salarys * 3>

SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0), ' monthly but wants', TRUNCATE(salary * 3,0)) "Dream Salary"
FROM employees;

9.如下图：根据部门ID的不同来设置grade

SELECT last_name "Last_name", job_id "Job_id", CASE job_id WHEN 'AD_PRES' THEN 'A'
																													 WHEN 'ST_MAN'  THEN 'B'
																													 WHEN 'IT_PROG' THEN 'C'
																													 WHEN 'SA_REP'  THEN 'D'
																													 WHEN 'ST_CLERK'THEN 'E'
																													 END 'Grade'
FROM employees;