MySQL 基础详讲（高级部分）

1、约束

什么是约束

• 对表中的数据进行限定，保证数据的正确性、有效性、完整性
• 约束通常是在创建表的时候进行约束

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、事务

什么是事务

1. 数据库的事务是一种机制，一个操作序列，包含了一组操作命令
2. 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令，要么同时成功，要么同时失败
3. 事务是一个不可分割的工作逻辑单元

为什么需要事务

事务的主要目的是确保数据库操作的一致性和完整性。下面是一个简单的例子来解释为什么需要事务。

假设有一个银行系统，其中有两个账户 A 和 B，当前账户 A 的余额为 1000 元，账户 B 的余额为 2000 元。现在有两个用户同时进行转账操作，一个用户从账户 A 向账户 B 转账 500 元，另一个用户从账户 B 向账户 A 转账 700 元。

如果没有事务的支持，那么这两个转账操作可以并发执行，可能会导致以下问题：

1. 并发问题：在没有事务的情况下，两个用户同时进行转账操作时，可能会出现竞争条件。例如，如果用户 A 首先读取账户 A 的余额为 1000 元，在用户 B 读取账户 B 的余额为 2000 元之前执行转账操作，那么用户 A 的转账操作会以账户 A 余额为 1000 元进行计算，导致账户 A 的余额不正确。
2. 数据不一致：如果两个转账操作不在同一个事务中，当第一个转账操作成功并提交后，而第二个转账操作失败并中止，会导致账户 A 和账户 B 的余额不一致。
3. 数据丢失：如果没有事务支持，当一个转账操作成功时，另一个转账操作发生错误并中止，导致其中一个用户的转账款项丢失。

通过使用事务，可以解决上述问题。事务可以确保这两个转账操作要么全部成功，要么全部失败。如果其中一个转账操作失败，事务可以回滚到事务开始之前的状态，保证数据的一致性。同时，事务还可以提供隔离性，使得并发执行的转账操作相互不影响。

事务的四大特性

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

子查询结果的三种情况

1. 子查询结果是单行单列，在 where 后面作为条件

   select 查询字段 from 表 where 字段 = (子查询)

2. 子查询结果是多行单列，结果类似一个数组，在 where 后面作为条件，父查询使用 IN / ANY / ALL 运算符

   select 查询字段 from 表 where 字段 in(子查询)

3. 子查询结果是多行多列，在 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;