MySQL基础详讲（高级部分）-阿里云开发者社区

[TOC]

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;

MySQL基础详讲（高级部分）