MySQL 基础详讲（高级部分）
1、约束
什么是约束
对表中的数据进行限定，保证数据的正确性、有效性、完整性 约束通常是在创建表的时候进行约束
| 约束 | 说明 |
|---|---|
| PRIMARY KEY | 主键约束 |
| UNIQUE | 唯一约束 |
| NOT NULL | 非空约束 |
| DEFAULT | 默认值约束 |
| FOREIGN KEY | 外键约束 |
1.1、主键约束
主键的作用
用来区分表中的数据
主键的特点
主键必须是唯一不重复的值 主键不能包含 null 值
添加主键
建表时添加主键
#方法1
create table 表名(
字段名 字段类型 PRIMARY KEY,
字段名 字段类型
);
#方法2
create table 表名(
列名 数据类型,
[CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);
/* =========== 主键约束 =========== */
-- 创建表学生表st1, 包含字段(id, name, age)将id做为主键
-- 创建表时添加主键
CREATE TABLE st1 (
id INT PRIMARY KEY,
NAME VARCHAR(10),
age INT
);
建表后单独添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
-- 在已有表中添加主键约束(了解)
ALTER TABLE st1 ADD PRIMARY KEY(id);
删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 删除主键约束(了解)
ALTER TABLE st1 DROP PRIMARY KEY;
注意：当你设置主键后，插入相同主键会报错
[2023-10-28 10:02:45] [23000][1062] Duplicate entry '1' for key 'st1.PRIMARY'
1.2、主键自增
为什么自增
主键如果让我们自己添加很可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值
字段名 字段类型PRIMARY KEY AUTO_INCREMENT
#注意 AUTO_INCREMENT 的字段类型必须是数值类型
/* =========== 主键自动增长 =========== */
-- 创建学生表st2, 包含字段(id, name, age)将id做为主键并自动增长
CREATE TABLE st2 (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT
);
-- 修改自动增长的开始值
ALTER TABLE st2 AUTO_INCREMENT = 1000;
1.3、唯一约束
唯一约束的作用
让字段的值唯一，不能重复
/* =========== 唯一约束 =========== */
CREATE TABLE 表名(
字段名 字段类型 UNIQUE,
字段名 字段类型
);
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工id，主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE , -- 员工姓名，非空并且唯一
);
1.4、非空约束
非空约束的作用
让字段的值不能为 null
/* =========== 非空约束 =========== */
CREATE TABLE 表名(
字段名 字段类型 NOT NULL,
字段名 字段类型
);
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工id，主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE , -- 员工姓名，非空并且唯一
joindate DATE NOT NULL , -- 入职日期，非空
salary DOUBLE(7,2) NOT NULL , -- 工资，非空
);
1.5、默认值约束
默认值约束的作用
如果这个字段不设置值，就使用默认值
/* =========== 默认值约束 =========== */
CREATE TABLE 表名(
字段名 字段类型 DEFAULT值,
字段名 字段类型
);
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工id，主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE , -- 员工姓名，非空并且唯一
bonus DOUBLE(7,2) DEFAULT 1000-- 奖金，如果没有奖金默认为1000
);
1.6、外键约束
外键约束介绍
外键约束是关系数据库中的一种约束，用于确保一个表中的数据在另一个相关表中存在对应的数据。它定义了两个表之间的关系，其中一个表中的外键引用另一个表中的主键。
通过外键约束，可以实现数据的完整性和一致性。当在一个表中定义了外键约束后，插入、更新、删除操作都会受到限制，只有符合约束的操作才会被允许执行。如果违反了外键约束，数据库会拒绝执行该操作，并返回错误信息。
外键约束的使用
CTEATE TABLE 表名(
字段名 字段类型，
字段名 字段类型，
[CONSTRAINT 外键约束名] FOREIGN KEY(外键字段名) REFERENCES 主表(主键字段名)
);
-- 创建 employee 并添加外键约束
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
CONSTRAINT fk_dep_id Foreign Key(dep_id) REFERENCES department(id)
);
2、事务
什么是事务
数据库的事务是一种机制，一个操作序列，包含了一组操作命令 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令，要么同时成功，要么同时失败 事务是一个不可分割的工作逻辑单元
为什么需要事务
事务的主要目的是确保数据库操作的一致性和完整性。下面是一个简单的例子来解释为什么需要事务。
假设有一个银行系统，其中有两个账户 A 和 B，当前账户 A 的余额为 1000 元，账户 B 的余额为 2000 元。现在有两个用户同时进行转账操作，一个用户从账户 A 向账户 B 转账 500 元，另一个用户从账户 B 向账户 A 转账 700 元。
如果没有事务的支持，那么这两个转账操作可以并发执行，可能会导致以下问题：
并发问题：在没有事务的情况下，两个用户同时进行转账操作时，可能会出现竞争条件。例如，如果用户 A 首先读取账户 A 的余额为 1000 元，在用户 B 读取账户 B 的余额为 2000 元之前执行转账操作，那么用户 A 的转账操作会以账户 A 余额为 1000 元进行计算，导致账户 A 的余额不正确。 数据不一致：如果两个转账操作不在同一个事务中，当第一个转账操作成功并提交后，而第二个转账操作失败并中止，会导致账户 A 和账户 B 的余额不一致。 数据丢失：如果没有事务支持，当一个转账操作成功时，另一个转账操作发生错误并中止，导致其中一个用户的转账款项丢失。
通过使用事务，可以解决上述问题。事务可以确保这两个转账操作要么全部成功，要么全部失败。如果其中一个转账操作失败，事务可以回滚到事务开始之前的状态，保证数据的一致性。同时，事务还可以提供隔离性，使得并发执行的转账操作相互不影响。
事务的四大特性
| 事务特性 | 含义 |
|---|---|
| 原子性(Atomicity) | 事务是不可分割的最小操作单位，要么同时成员，要么同时失败 |
| 一致性(Consistency) | 事务前后数据的完整性必须保持一致 |
| 隔离性(Isolation) | 是指多个事务并发访问数据库时，一个事务不能被其它的事务所干扰，多个并发事务之间数据要相互隔离，不能互相影响 |
| 持久性(Durability) | 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的 |
3、多表查询
什么是多表查询
一次性同时查询多张表
多表查询的分类
表连接查询
同时查询多张表
子查询
先查一张表，后查另一张表
3.1、隐式内连接
select 字段列表 from 表 1,表 2...where 条件;
-- 准备数据
-- 创建部门表
use db1;
CREATE TABLE tb_dept (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO tb_dept (NAME) VALUES ('开发部'),('市场部'),('财务部'),('销售部');
-- 创建员工表
CREATE TABLE tb_emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT
);
INSERT INTO tb_emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO tb_emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
-- 查询孙悟空员工的信息, 包括所在的部门名称
select tb_emp.*,tb_dept.NAME from tb_emp,tb_dept where tb_emp.id = tb_dept.id and tb_emp.NAME = '孙悟空';
3.2、显式内连接
使用 inner join ... on 语句，可以省略 inner
select 字段列表 from 表 1 [inner] join 表 2 on 条件
-- INNER可以省略,初学者不建议省略
select * from tb_emp inner join
tb_dept on tb_emp.id = tb_dept.id
where tb_emp.NAME = '孙悟空';
3.3、左外连接
select 字段列表 from 表 1 left [outer] join 表 2 on 条件
/* ===========左外连接查询=========== */
-- 左外连接查询 (满足要求的显示,保证左表不满足要求的也显示)
select * from tb_emp left outer join tb_dept on tb_emp.dept_id = tb_dept.id;
3.4、右外连接
select 字段列表 from 表 1 right [outer] join 表 2 on 条件
/* ===========右外连接=========== */
-- 右外连接
select * from tb_emp right outer join tb_dept on tb_emp.id = tb_dept.id;
3.5、子查询
什么是子查询
一个查询语句的结果作为另一个查询语句的一部分
select 查询字段 from 表 where 条件;
select * from employee where salary = (select MAX(salary) from employee);
子查询结果的三种情况
子查询结果是单行单列，在 where 后面作为条件
select 查询字段 from 表 where 字段 = (子查询)
子查询结果是多行单列，结果类似一个数组，在 where 后面作为条件，父查询使用 IN / ANY / ALL 运算符
select 查询字段 from 表 where 字段 in(子查询)
子查询结果是多行多列，在 from 后面作为虚拟表
select 查询字段 from(子查询) 表别名 where 条件
/* ===========子查询的结果是单行单列=========== */
-- 查询工资最高的员工是谁？
-- 1.查询最高工资
select MAX(salary) from tb_emp;
-- 2.通过最高工资查询员工姓名
select * from tb_emp where salary = (select MAX(salary) from tb_emp);
/* ===========子查询的结果是多行单列的时候=========== */
-- 查询工资大于5000的员工, 来自于哪些部门的名字
-- 1.查询工资大于5000的员工所在部门id
select dept_id from tb_emp where salary > 5000;
-- 2.根据部门id查找部门名称
select NAME from tb_dept where id in (select dept_id from tb_emp where salary > 5000);
/* ===========子查询的结果是多行多列=========== */
-- 查询出2011年以后入职的员工信息, 包括部门名称
-- 1.查询出2011年以后入职的员工信息
select * from tb_emp where join_date >= '2011-1-1';
-- 2.找到对应的部门信息
select * from(select * from tb_emp where join_date >= '2011-1-1') as a ,tb_dept where a.dept_id = tb_dept.id;
发表评论