MySQL学习（二）——MySQL内置函数

1. 函数

和其他编程语言一样，MySQL也有函数的定义。函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。这里的函数既指MySQL中内置的函数，也指自己定义的函数。

MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。

1.1 字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：

演示如下：

# concat : 字符串拼接
select concat('Hello', ' MySQL');

# lower : 全部转小写
select lower('Hello');

# lpad : 左填充
select lpad('01', 5, '-');

# trim : 去除空格
select trim(' Hello MySQL ');

# substring : 截取子字符串
select substring('Hello MySQL',1,5);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

1.2 数值函数

常见的数值函数如下：

演示如下：

# ceil：向上取整
select ceil(1.1);

# mod：取模
select mod(7,4);

# rand：获取随机数
select rand();

# round：四舍五入
select round(2.344,2);
1
2
3
4
5
6
7
8
9
10
11

1.3 日期函数

常见的日期函数如下：

演示如下：

# curdate：当前日期
select curdate();

# curtime：当前时间
select curtime();

# YEAR , MONTH , DAY：当前年、月、日
select YEAR(now());
select MONTH(now());
select DAY(now());

# now：当前日期和时间
select now();

# date_add：增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR );

# datediff：获取两个日期相差的天数
select datediff('2021-10-01', '2021-12-01');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

1.4 流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

演示如下：

# if
select if(false, 'Ok', 'Error');

# ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');

# case when then else end
# 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select
	name,
	( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else
'二线城市' end ) as '工作地址'
from emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2. 约束

2.1 概述

约束是作用于表中字段上的规则，用于限制存储在表中的数据。目的是为了保证数据库中数据的正确、有效性和完整性。约束的分类如下：

**注意：**约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

2.2 外键约束

2.2.1 外键使用

外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

外键约束示例如下，加入我们有两张表，员工表和部门表。左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键,关联的是另一张表的主键。

对外键操作的语法如下。

• 创建表时添加

  CREATE TABLE 表名(
  	字段名 数据类型,
  	...
  	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
  );
  1
  2
  3
  4
  5

• 创建表后添加

  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
  1

• 删除外键

  ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  1

代码演示如下：

# 为emp表的dept_id字段添加外键约束，关联dept表的主键id
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

# 删除emp表的外键fk_emp_dept_id
alter table emp drop foreign key fk_emp_dept_id;
1
2
3
4
5

2.2.2 删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种:

默认的外键删除/更新行为是 NO ACTION ，如果想要有其他的行为，那么其语法为：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
1
2

语法演示如下：

# CASCADE
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references 
dept(id) on update cascade on delete cascade ;

# SET NULL
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update set null on delete set null ;
1
2
3
4
5
6
7