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

目录

一 函数

(一 字符串函数

(二 数值函数

(三 日期函数

(四 流程函数

二 约束

(一 概述

(二 约束演示

(三 外键约束

三 多表查询

(一 多表关系

1 一对多（多对一）

2 多对多

3 一对一

(二 多表查询概述

(三 内连接

1 查询语法

2 代码实现：

(四 外连接

1 查询语法

2 代码实现

(五 自连接

1 查询语法

2  代码实现

(六 联合查询

1 查询语法

​编辑

2 代码实现：

(七 子查询

查询语法

1 标量子查询（子查询结果为单个值）

2 列子查询（子查询结果为一列）

​编辑

3 行子查询（子查询结果为一行）

​编辑

4 表子查询（子查询结果为多行多列）

四 事务

 (一 事务简介

(二 事务操作

(三 事务四大特性

(四 并发事务问题

(五 事务的隔离级别

---

一 函数

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);

可视化界面

(二 多表查询概述

1 概述：指从多张表中查询数据。

2 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，要消除无效的笛卡尔积 需要指定连接条件）。

3 多表查询分类：

连接查询 

• 内连接：相当于查询A，B交集部分数据
• 外连接：

  • 左外连接：查询左表所有数据，以及两张表交际部分数据

  • 右外连接：查询右表所有数据，以及两张表交集部分数据

• 自连接：当前表与自身的连接查询，自链接必须使用表别名

子查询

(三 内连接

1 查询语法

• 隐式内连接

select 字段列表 from 表1，表2 where 条件；

• 显式内连接

select 字段列表 from 表1 （inner） join 表2 on 连接条件；

内连接查询的时两张表交集的部分

2 代码实现：

# 隐式内连接演示
# 查询员工的姓名以及关联部门的名称 表结构：emp dept 连接条件 emp.dept_id = dept.id
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;select e.name,d.name from emp as e,dept as d where e.dept_id = d.id;
# 显示内连接演示
select e.name,d.name from emp as e inner join dept d on d.id = e.dept_id;

图形化展示：

(四 外连接

1 查询语法

• 左外连接

select 字段 from 表1 left (outer) join 表2 on 条件；

查询的是左表的所有数据和交集部分。

• 右外连接

select 字段 from 表1 right (outer) join 表2 on 条件；

查询的是右表的所有数据和交集部分

2 代码实现

# 左外连接
select e.*,d.name from emp e left outer join dept d on d.id = e.dept_id
# 右外连接
select e.name,d.* from dept d right join emp e on d.id = e.dept_id;

图形化展示：

(五 自连接

1 查询语法

select 字段列表 from 表A  别名A  join 表A 别名B on 条件；

自连接查询，可以是内连接查询也可以是外连接查询。

2  代码实现

# 自连接
select a.name '员工',b.name '领导' from emp a,emp b where a.id=b.dept_id;

图形化展示

(六 联合查询

1 查询语法

2 代码实现：

# 联合查询 - 去重
select * from users where age >= 48
union
select * from users where id<10;
# 联合查询 - 直接合并
select * from users where age >= 48
union all 
select * from users where id<10;

注意事项

(七 子查询

查询语法

1 标量子查询（子查询结果为单个值）

 查询语法

代码实现

# 查询工作地址安徽淮北的员工信息 子嵌套中只能查出一个id
select * from users where id = (select id from users where workaddress='安徽淮北');

2 列子查询（子查询结果为一列）

查询语法

代码实现

some与any作用相同

3 行子查询（子查询结果为一行）

查询语法

代码实现

4 表子查询（子查询结果为多行多列）

查询语法

代码实现

 

四 事务

 (一 事务简介

概念：事务是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。

(二 事务操作

1 操作语法

2 代码实现：

# 修改提交方式
set @@autocommit = 0;
# 查询提交方式 1 为自动 0 为手动
select @@autocommit;# 转账操作
# 查询张三
select * from account where name ='张三';# 张三余额减1000
update account set money = money - 1000 where name = '张三';# 李四余额加1000
update account set money = money + 1000 where name = '李四';# 提交事务（相当于run执行）
commit ;
# 回滚事务(类似清空clear)
rollback ;

---------------------------------------------------------------------------------------------------------------------------------

代码实现

start transaction ;
# 转账操作
# 查询张三
select * from account where name ='张三';# 张三余额减1000
update account set money = money - 1000 where name = '张三';# 李四余额加1000
update account set money = money + 1000 where name = '李四';# 提交事务（相当于run执行）
commit ;
# 回滚事务(类似清空clear)
rollback ;

(三 事务四大特性

1. 原子性（Atomicity）：事务是不可分割的最小操作单位，要么全部成功，要么全部失败。
2. 一致性（Consistency）：事务完成时，必须使所有的数据保持一致状态。
3. 隔离性（Isolation）：数据库提供的隔离机制，保证事务在不受外部并发操作的独立环境下运行。
4. 持久性（Durability）：事务一旦提交或回滚，他对数据库中的改变就是永久的。

(四 并发事务问题

(五 事务的隔离级别

代码实现：

select @@transaction_isolation;# 设置事务隔离级别-read uncommitted
set session transaction isolation level read uncommitted ;