MySQL数据库 （三）- 函数/约束/多表查询/事务

一 函数

1 概念 ：函数是指一段可以直接被另一段程序调用的程序或代码。

2 语法 

select 函数;

(一 字符串函数

1 常见字符串函数

2 代码实现：

# concat
select concat('hello ','mysql');
# lower
select lower('ANxain');
# upper
select upper('ANxian');
# lpad
select lpad('01',5,'_');
# rpad
select rpad('01',5,'_');
# trim
select trim(' hello world ');
# substring
select substring('abcdefghi',3,5);

# 将员工id补全至5位数，不足的使用0
update users set id = lpad(id,5,'0');

(二 数值函数

1 常见数值函数

2 代码实现

# ceil
select ceil(1.1);
# floor
select floor(2.1);
# mod
select mod(7,4);
# rand
select rand();
# round
select round(2.34,1);

# 设置一个六位数验证码
select rpad(round(rand()*1000000),6,'0');

(三 日期函数

1 常见日期函数

2 代码实现

# curdate
select curdate();
# curtime
select curtime();
# now
select now();
# year(date)
select year('2025-11-11');
# month(date)
select month('2025-11-11');
# day(date)
select day('2025-11-11');
# date_add(date,interval expr type)
select date_add('2025-01-01',interval 100 day );
select date_add('2025-01-01',interval 10 month );
# datediff(date1,date2)
select datediff('2025-04-13',now());

# 查询员工的入职时间-倒序排序
select name,datediff(now(),entrydate) as en from users order by en DESC ;

(四 流程函数

1 常见流程控制函数

2 代码实现

# if
select if(true,'OK','error');# ifnull
select ifnull('OK','DEFAULT');
select ifnull(null,'DEFAULT');

# case when then else end
# 需求查询员工的姓名和工作地址如果是安徽合肥展示省会城市，其他的则显示不是省会城市
select name,(case workaddress when '安徽合肥' then '省会城市' when '山东济南' then '省会城市' else '非省会城市' end) as '是否为省会地址' from users;# 统计公司员工的年龄
# 18-38为壮年 48 - 68 为中年 其余为老年
select name,(case when age between 18 and 38 then '壮年' when age>=48 and age<= 68 then '中年' else '老年' end) as '年龄结构' from users;

二 约束

(一 概述

1 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

2 目的：保证数据库中数据的正确。

3 分类

4 注意事项

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

(二 约束演示

代码实现

create table uuu(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 '约束演示表';# 插入数据
insert into uuu(name, age, status, gender)
values ('张三', 19, '1', '男'),('李四', 20, '0', '女');insert into uuu(name, age, gender)
values ('ax',100,'男');

图形化展示

(三 外键约束

1 概念：外键约束是让两张表的约束建立连接，从而保证数据的一致性和完整性。

2 解决方案

3 代码实现：

CREATE TABLE dept (id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT '部门表';INSERT INTO dept (id, name) VALUES
(1, '研发部'),
(2, '市场部'),
(3, '财务部'),
(4, '销售部'),
(5, '总经办');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);# 添加外键
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;

4 删除/更新行为

代码演示：

alter table empadd constraint fk_emp_dept_id foreign key (dept_id) references dept (id) on update set null on delete set null;

补充

三 多表查询

(一 多表关系

概述：在项目开发中在进行数据库结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以每个表结构之间也存在这各种联系，基本上分为三种：

• 一对多（多对一）
• 多对多
• 一对一

1 一对多（多对一）

• 案例：部门与员工之间的关系
• 关系：一个部门对应多个员工，一个员工对应一个部门。
• 实现：在多的一方建立外键，指向一的一方的主键。

2 多对多

案例：学生和课程之间的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择。

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

代码实现：

# 创建学生表
create table student(id int auto_increment primary key comment '主键id',name varchar(10) comment '姓名',no varchar(10) comment '学号'
) comment '学生表';insert into student values
(null, '黛绮丝', '2000100101'),
(null, '谢逊', '2000100102'),
(null, '殷天正', '2000100103'),
(null, '韦一笑', '2000100104');# 创建课程表
create table course(id int auto_increment primary key comment '主键id',name varchar(10) comment '课程名称'
) comment '课程表';insert into course values
(null, 'java'),
(null, 'php'),
(null, 'mysql'),
(null, 'hadoop');# 创建中间表
create table student_course(id int auto_increment comment '主键' primary key,studentid int not null comment '学生id',courseid int not null comment '课程id',constraint fk_courseid foreign key (courseid) references course (id),constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';insert into student_course values
(null, 1, 1),
(null, 1, 2),
(null, 1, 3),
(null, 2, 2),
(null, 2, 3),
(null, 3, 4);

 可视化界面

3 一对一

案例：用户与用户之间详细的关系。

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在另一张表中，以提升操作效率。

实现：在任意的一方加入外键，关联另外一方的主键，并且设置外键为唯一的。(unique)

代码实现

create table tb_user(id int auto_increment primary key comment '主键id',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '1: 男, 2: 女',phone char(11) comment '手机号'
) comment '用户基本信息表';create table tb_user_edu(id int auto_increment primary key comment '主键id',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户id',constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';insert into tb_user(id, name, age, gender, phone) values
(null, '黄渤', 45, '1', '18800001111'),
(null, '冰冰', 35, '2', '18800002222'),
(null, '码云', 55, '1', '18800008888'),
(null, '李彦宏', 50, '1', '18800009999');insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
(null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
(null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
(null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
(null, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);

可视化界面

(二 多表查询概述

(三 内连接

(四 外连接

(五 自链接

(六 子查询

(七 多表查询案例

四 事务