MySQL 函数、约束
函数
- 函数是指一段可以直接被另一段程序调用的程序或代码
- 字符串函数
- 数值函数
- 日期函数
- 流程函数
字符串函数
MySQL 中内置了很多字符串函数，常用的几个如下
函数 功能
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 个长度的字符串
格式
SELECT 函数（参数）;
-- concat
select concat('Hello', ' MySQL'); -- Hello MySQL
-- lower
select lower('Hello'); -- hello
-- upper
select upper('Hello'); -- HELLO
-- lpad
select lpad('01', 5, '-'); -- ---01
-- rpad
select rpad('01', 5, '-'); -- 01---
-- trim
select trim(' Hello MySQL '); -- Hello MySQL
-- substring
select substring('Hello MySQL', 1, 5); -- Hello
案例
-- 由于业务需求变更，企业员工的工号，统一为 5 位数，目前不足 5 位数的全部在前面补 0。
-- 比如：1 号员工的工号应该为 00001
update emp set workno = lpad(workno, 5, '0');
数值函数
常见的数值函数如下
函数 功能
CEIL(X) 向上取整
FLOOR(X) 向下取整
MOD(x, y) 返回 x / y 的模
RAND() 返回 0 ~ 1 内的随机数
ROUND(x, y) 求参数 x 的四舍五入的值，保留 y 位小数
-- ceil
select ceil(1.5); -- 2
select ceil(1.1); -- 2
-- floor
select floor(1.1); -- 1
select floor(1.9); -- 1
-- mod
select mod(3, 4); -- 3
select mod(6, 4); -- 2
-- rand
select rand(); -- 0.9170928587364897(不固定)
-- round
select round(2.34, 2); -- 2.34
select round(2.345, 2); -- 2.35
select round(2.344, 2); -- 2.34
案例
-- 通过数据库的函数，生成一个六位数的随机验证码
select lpad(round(rand() * 1000000, 0), 6, 0);
日期函数
常见的日期函数
函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定 date 的年份
MONTH(date) 获取指定 date 的月份
DAY(date) 获取指定 date 的日期
DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔 expr 后的时间值
DATEDIFF(date1, date2) 返回起始时间 date1 和 结束时间 date2 之间的天数
-- curdate()
select curdate();
-- curtime()
select curtime();
-- now()
select now();
-- YEAR, MONTH, DAY
select year(now());
select month(now());
select day(now());
-- date_add
select date_add(now(), INTERVAL 70 DAY);-- 2023-04-07 22:17:07
select date_add(now(), INTERVAL 70 MONTH); -- 2028-11-27 22:17:33
select date_add(now(), INTERVAL 70 YEAR); -- 2093-01-27 22:17:54
-- datediff
select datediff('2021-12-01', '2021-11-01'); -- 30
select datediff('2021-12-01', '2021-10-01'); -- 61
select datediff('2021-10-01', '2021-12-01'); -- -61
案例
-- 查询所有员工的入职天数，并根据入职天数倒叙排序
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;
流程函数
- 流程函数也是很常用的一类函数，可以在 SQL 语句中实现条件筛选，从而提高语句的效率
常用函数
函数 功能
IF(value, t, f) 如果 value 为 true，则返回 t，否则返回 f
IFNULL(value1, value2) 如果 value 不为空，返回 value1，否则返回 value2
CASE WHEN [val1] THEN [res1] ... ELSE [default] END 如果 val1 为 true，返回 res1, ... 否则返回 default 默认值
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END 如果 expr 的值等于 val1，返回 res1, ... 否则返回 default 默认值
-- if
select if(
true,
'Ok',
'Error'
); -- Ok
-- ifnull
select ifnull (
'Ok',
'Default'
); -- Ok
select ifnull (
'',
'Default'
); --
select ifnull (
null,
'Default'
); -- Default
-- case when then else end
-- 需求：查询 emp 表的员工姓名和工作地址（上海/北京 ----> 一线城市， 其他 ----> 二线城市）
select
name,
(case workaddress
when '北京' then '一线城市'
when '上海' then '一线城市'
else '二线城市' end) as '工作地址'
from emp;
select
name,
(case
when workaddress = '北京' then '一线城市'
when workaddress = '上海' then '一线城市'
else '二线城市' end) as '工作地址'
from emp;
案例
-- 数据
create table score (
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment '英语',
chinese int comment '语文'
) comment '学员成绩表';
insert into score (id, name, math, english, chinese)
values (1, 'Tom', 67, 88, 95),
(2, 'Rose', 23, 66, 90),
(3, 'Jack', 56, 98, 76);
-- 统计班级各个学员的成绩，展示规则如下：
-- >= 85, 展示优秀
-- >= 60, 展示及格
-- 否则, 展示不及格
select
id,
name,
(case
when math >= 85 then '优秀'
when math >= 60 then '及格'
else '不及格' end) as '数学',
(case
when english >= 85 then '优秀'
when english >= 60 then '及格'
else '不及格' end) as '英语',
(case
when chinese >= 85 then '优秀'
when chinese >= 60 then '及格'
else '不及格' end) as '语文'
from score;
约束
概念
- 概念：约束时作用于表中字段上的规则，用于限制存储在表中的数据
- 目的：保证数据库中数据的正确、有效性和完整性
- 分类：
约束 描述 关键字
非空约束 限制该字段的数据不能为 null NOT NULL
唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE
主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY
默认约束 保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT
检查约束(8.0.16版本之后) 保证字段值满足某一个条件 CHECK
外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 FOREIGN KEY
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束
约束演示
use itheima;
create table user (
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment'姓名',
age int check (age > 0 and age <= 120) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
) comment '用户表';
show tables;
select * from user;
-- 插入数据
insert into user (name, age, status, gender) values ('Tom1', 19, '1', '男'), ('Tom2', 25, '0', '男');
insert into user (name, age, status, gender) values ('Tom3', 19, '1', '男');
insert into user (name, age, status, gender) values (null, 19, '1', '男');
insert into user (name, age, status, gender) values ('Tom3', 19, '1', '男');
insert into user (name, age, status, gender) values ('Tom4', 80, '1', '男');
insert into user (name, age, status, gender) values ('Tom5', -1, '1', '男');
insert into user (name, age, status, gender) values ('Tom5', 121, '1', '男');
insert into user (name, age, gender) values ('Tom5', 120, '男');
unique会申请自增
外键约束
概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
注意：目前上述的两张表，在数据库里面，并未建立外键关联，所以是无法保证数据的一致性和完整性的
语法
添加外键
CREATE TABLE 表名 (
字段名 数据类型;
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
create table dept (
id int auto_increment primary key comment 'ID',
name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id, name) values (1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部'), (5, '总经办');
select * from dept;
create table emp (
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
) comment '员工表';
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
(1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
(3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
(5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
(6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);
select * from emp;
delete from dept where id = 1;
insert into dept (id, name) values (1, '研发部');
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
alter table emp drop foreign key fk_emp_dept_id;
删除更新行为
行为 说明
NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与 RESTRICT 一致）
RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与 NO ACTION 一致）
CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为 null（这就要求该外键允许取 null）。
SET DEFAULT 父表有变更时，子表将外键列设置成一个默认的值（lnnodb不支持）。
语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
-- 删除外键
alter table emp drop foreign key fk_emp_dept_id;
-- 外键的删除和更新行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;
drop table emp;
drop table dept;
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null;
浙公网安备 33010602011771号