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,…)函数，但是每个字符串之间要加上xINSERT(str, idx, len, replacestr)将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestrREPLACE(str, a, b)用字符串b替换字符串str中所有出现的字符串aUPPER(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开始与结尾的s1TRIm(LEADING s1 FROM s)去掉字符串s开始处的s1TRIm(TRAILING s1 FROM s)去掉字符串s结尾处的s1REPEAT(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)相同。未找到，返回0ELT(m,s1,s2,…,sn)返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回snFIELD(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()+0FROM DUAL;

流程控制函数

很像三目运算啊樂

函数用法IF(value,value1,value2)如果value的值为TRUE，返回value1，否则返回value2IFNULL(value1, value2)如果value1不为NULL，返回value1，否则返回value2CASE 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.1WHEN 20 THEN salary * 1.2WHEN 30 THEN salary * 1.3ELSE 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.1WHEN 20 THEN salary * 1.2WHEN 30 THEN salary * 1.3ELSE salary * 1.4 END "alias"FROM employeesWHERe department_id IN (10, 20, 30)

单行函数的练习题

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

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

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

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

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

SELECt last_name, LENGTH(last_name) "name_length"FROM employeesORDER BY name_length ASC;

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

SELECt CONCAt(employee_id,',',last_name,',',salary) "OUT_PUT"FROM employees;

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

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

SELECt employee_id, DATEDIFF(CURTIME(),hire_date) "worked_days", DATEDIFF(CURTIME(),hire_date) / 365 "worked_years"FROM employeesORDER BY worked_years ASC;

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

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

SELECt last_name, hire_date, department_idFROM employeesWHERe department_id IN (80, 90, 110)AND commission_pct IS NOT NULLAND hire_date >= '1997-01-01'

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

SELECt last_name, hire_date, department_idFROM employeesWHERe DATEDIFF(CURDATE(),hire_date) >= 10000;

做一个查询，产生下面的结果(去除小数)

earns monthly but want

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;