Mysql基础篇（二）之函数和约束

宋兵发

一. 函数

Mysql中的函数主要分为四类：字符串函数、数值函数、日期函数、流程函数
1. 字符串函数

常用函数如下：
函数功能CONCAT(S1, S2, ......Sn)字符串拼接，将S1，S2，.....Sn拼接成一个字符串LOWER(str)将字符串str全部转为小写UPPER(str)将字符串str全部转为大写LPAD(str, n, pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度RPAD(str, n, pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度TRIM(str)去掉字符串头部和尾部的空格SUBSTRING(str, start, len)返回字符串str从start位置起的len个长度的字符串(1).案例1：将yun3k和com进行拼接

1. select concat('yun3k', 'com');

复制代码

(2). 案例2：将YUN3k_com全部转为小写

1. select lower('YUN3k_com');

复制代码

(3). 案例3：将yun3k_com全部转为大写

1. select upper('yun3k_com');

复制代码

(4). 案例4：将yun3k左边不满足10位的全部填充'a'

1. select lpad('yun3k', 10, 'a');

复制代码

(5). 案例5：将yun3k右边不满足10位的全部填充'b'

1. select rpad('yun3k', 10, 'b');

复制代码

(6). 去除" yun3k com   "的首尾空格

1. select trim(' yun3k com  ');

复制代码

(7). 截取yun3k_com的前5位字符串

1. select substring('yun3k_com', 1, 5);

复制代码

(8). 将yun3k_emp表中的员工工号统一变为8位数，不足8位数的全部在前面补6

1. update yun3k_emp set workno = lpad(workno, 8, '6');

复制代码

2. 数值函数

常见函数如下：
函数功能CEIL(x)向上取整FLOOR(x)向下取整MOD(x, y)返回x/y的模RAND()返回0～1内的随机数ROUND(x, y)求参数x的四舍五入的值，保留y位小数(1). 案例1：1.1向上取整

1. select ceil(1.1);

复制代码

(2). 案例2：1.9向下取整

1. select floor(1.9);

复制代码

(3). 案例3：7与4取模

1. select mod(7,4);

复制代码

(4).案例4：获取随机小数

1. select rand();

复制代码

(5). 案例5：2.334四舍五入保留2位小数

1. select round(2.324,2);

复制代码

(6). 案例6：生成一个六位数的随机验证码

思路：获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

1. select lpad(round(rand() * 1000000 , 0), 6, '0');

复制代码

3. 日期函数

常见日期函数如下：
函数功能CURDATE()返回当前日期CURTIME()返回当前时间NOW()返回当前日期和时间YEAR(date)获取指定date的年份MONTH(date)获取指定date的月份DAY(date)获取指定date的日期DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数(1).案例1：获取当前日期

1. select curdate();

复制代码

(2). 案例2：获取当前时间

1. select curtime();

复制代码

(3).案例3：获取当前日期和时间

1. select now();

复制代码

(4). 案例4：获取当前年、月、日

1. select YEAR(now()), MONTH(now()), DAY(now());

复制代码

(5). 案例5：当前时间分别增加50年，50月，50日

1. select date_add(now(), INTERVAL 50 YEAR), date_add(now(), INTERVAL 50 MONTH), date_add(now(), INTERVAL 50 DAY);

复制代码

(6). 案例6：获取当前时间与”2022-06-30”日期的差值

1. select datediff(now(), '2022-06-30');

复制代码

(7). 案例7：查询yun3k_emp表中所有员工的入职天数，并根据入职天数倒序排序

1. select name, datediff(curdate(), entrydate) as 'entrydays' from yun3k_emp order by entrydays desc;

复制代码

4. 流程函数

常见流程函数如下：
函数功能IF(value, t, f)如果value位true，则返回t，否则返回fIFNULL(vales1, value2)如果value不为空，返回value1，否则返回value2CASE WHEN [val1] THEN [resl] ... ELSE [default] END如果val1为true，返回res1，... 否则返回default默认值CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END如果expr的值等于val1，返回res1，... 否则返回fault默认值(1). 案例1：if使用

1. select if(false, 'Ok', 'Error');

复制代码

(2). 案例2：ifnull使用

1. select ifnull('Ok','Default');
3. select ifnull('','Default');
5. select ifnull(null,'Default');

复制代码

(3). 案例3：查询yun3k_emp表的员工姓名和工作地址(北京/上海 ---> 一线城市，其他 ---> 二线城市)

1. select name, (
2.         case workaddress when '北京'
3.         then '一线城市' when '上海'
4.         then '一线城市'
5.         else '二线城市' end
6. ) as '工作地址' from yun3k_emp;

复制代码

(4). 案例4：查询yun3k_emp表中年龄为40以下的显示为青年，年龄为40-60显示为中年，年龄60岁以上显示为老年

[code]select id, age, (case         when age < 40 then '青年'         when age >=40 and age  0 && age