MySQL学习笔记-单行函数以及练习题

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

MySQL单行函数

数值型函数

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

进制之间的转换

我们输入的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                 |
+-----------------------+----------------------+

样🌰

注意，在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 |
+----------------------------------+-----------------------------------+

获取日期、时间

举🌰演示一下：

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

流程控制函数

很像三目运算啊🤔

举🌰

通过工资是否是大于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;