MySQL库表和约束

1 创建和管理表

1.1 基础知识

1.1.1 一条数据存储的过程

存储数据是处理数据的第一步 。只有正确地把数据存储起来，我们才能进行有效的处理和分析。在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

从系统架构的层次上看，MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数据表的行与列 。所以必须从创建数据库开始。

1.1.2 标识符命名规则

• 数据库名、表名不得超过30个字符，变量名限制为29个字符
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来。
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

1.1.3 MySQL中的数据类型

常用的几类类型介绍如下：

1.2 创建和管理数据库

1.2.1 创建数据库

方式1：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）

CREATE DATABASE IF NOT EXISTS 数据库名;

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

1.2.2 使用数据库

查看当前所有的数据库

SHOW DATABASES; # 有一个S，代表多个数据库

查看当前正在使用的数据库

SELECT DATABASE(); # 使用的一个 mysql 中的全局函数

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;

使用/切换数据库

USE 数据库名;

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

1.2.3 修改数据库

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集; # 比如：gbk、utf8等

1.2.4 删除数据库

方式1：删除指定的数据库

DROP DATABASE 数据库名;

方式2：删除指定的数据库（ 推荐 ）

DROP DATABASE IF EXISTS 数据库名;

1.3 创建和管理数据表

1.3.1 创建数据表

创建方式1
语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);

必须指定：表名，列名(或字段名)，数据类型，长度
可选指定：约束条件，默认值

示例：

CREATE TABLE dept(
-- int类型，自增
deptno INT(2) AUTO_INCREMENT,
dname VARCHAR(14),
loc VARCHAR(13),
-- 主键
PRIMARY KEY (deptno)
);

创建方式2
使用 AS subquery 选项，将创建表和插入数据结合起来
语法：

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table
WHERE condition;

指定的列和子查询中的列要一 一对应，通过列名和默认值定义列

示例：

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

查看数据表结构：

SHOW CREATE TABLE 表名;
或者
DESCRIBE/DESC 表名;

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

1.3.2 修改数据表

使用 ALTER TABLE 语句可以实现：

向已有的表中添加列

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
# 示例：
ALTER TABLE dept80 ADD job_id varchar(15);

修改现有表中的列

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER字段名2】;
# 示例：
ALTER TABLE dept80 MODIFY last_name VARCHAR(30);

对默认值的修改只影响今后对表的修改
删除现有表中的列

ALTER TABLE 表名 DROP 【COLUMN】字段名
# 示例：
ALTER TABLE dept80 DROP COLUMN job_id;

重命名现有表中的列

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
# 示例：
ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);

1.3.3 重命名数据表

使用RENAME

RENAME TABLE emp TO myemp;

使用ALERT

ALTER table dept RENAME [TO] detail_dept;  # [TO]可以省略

1.3.4 删除数据表

在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。数据和结构都被删除，所有正在运行的相关事务被提交。所有相关索引被删除。

语法:

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

DROP TABLE 语句不能回滚

1.3.5 清空数据表

TRUNCATE TABLE语句：

• 删除表中所有的数据
• 释放表的存储空间

语法：

TRUNCATE TABLE 表名;

TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚。

示例：

SET autocommit = FALSE;

DELETE FROM detail_dept;

SELECT * FROM detail_dept;

ROLLBACK;

SELECT * FROM detail_dept;

TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

2 数据处理之增删改

2.1 插入数据

2.1.1 VALUES的方式添加

使用这种语法一次只能向表中插入一条数据。

情况1：为表的所有字段按默认顺序插入数据

INSERT INTO 表名
VALUES (value1,value2,....);

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

示例：

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

情况2：为表的指定字段插入数据

INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。
在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,…valuen要与column1,…columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

示例：

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');

情况3：同时插入多条记录
INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开。

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
# 或者
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

示例:

INSERT INTO emp(emp_id,emp_name)
VALUES (1001,'kong'),
       (1002,'de'),
       (1003,'xing');

2.1.2 将查询结果插入到表中

INSERT 还可以将 SELECT 语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条 INSERT 语句和一条 SELECT 语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

语法:

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

在 INSERT 语句中加入子查询。
不必书写 VALUES 子句。
子查询中的值列表应与 INSERT 子句中的列名对应。

示例：

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

2.2 更新数据

语法：

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

可以一次更新多条数据。
如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
使用 WHERE子句指定需要更新的数据。

示例：

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

如果省略 WHERE 子句，则表中的所有数据都将被更新。

2.3 删除数据

语法：

DELETE FROM 表名
WHERE 条件;

3 约束

3.1 概述

1. 为什么需要约束
数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

• 实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女”
• 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

2. 什么是约束
约束是表级的强制规定。可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过ALTER TABLE 语句规定约束。
3. 约束的分类
根据约束数据列的限制，约束可分为：

• 单列约束：每个约束只约束一列
• 多列约束：每个约束可约束多列数据

根据约束的作用范围，约束可分为：

• 列级约束：只能作用在一个列上，跟在列的定义后面
• 表级约束：可以作用在多个列上，不与列一起，而是单独定义

根据约束起的作用，约束可分为：

• NOT NULL：非空约束，规定某个字段不能为空
• UNIQUE ：唯一约束，规定某个字段在整个表中是唯一的
• PRIMARY KEY ：主键(非空且唯一)约束
• FOREIGN KEY ：外键约束
• CHECK ：检查约束
• DEFAULT： 默认值约束

3.2 非空约束

1. 建表时

CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);

2. 建表后

alter table 表名称 modify 字段名 数据类型 not null;

3. 删除非空约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;
ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT '默认值' NULL;

3.3 唯一性约束

1. 建表时

create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);

2. 建表后指定唯一键约束
字段列表中如果是一个字段，表示该列的值唯一。

# 方式1：
alter table 表名称 add unique key(字段列表);
# 方式2：
alter table 表名称 modify 字段名 字段类型 unique;

3. 关于复合唯一约束
字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的。

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段列表) 
);

4. 删除唯一约束

# 查看都有哪些约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';
# 删除唯一索引
ALTER TABLE USER DROP INDEX uk_name_pwd;

3.4 PRIMARY KEY 约束

用来唯一标识表中的一行记录。主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。

• 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
• 主键约束对应着表中的一列或者多列（复合主键）
• 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
• MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
• 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
• 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

1.建表时指定主键约束

create table 表名称(
字段名 数据类型 primary key, # 列级模式
字段名 数据类型,
字段名 数据类型
);

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) # 表级模式
);

2.建表后增加主键约束
字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); 

3.关于复合主键

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2) #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

4.删除主键约束

alter table 表名称 drop primary key;

3.5 自增列：AUTO_INCREMENT

某个字段的值自增

一个表最多只能有一个自增长列
当需要产生唯一标识符或顺序值时，可设置自增长
自增长列约束的列必须是键列（主键列，唯一键列）
自增约束的列的数据类型必须是整数类型
如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

1.建表时指定自增约束

create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);

create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);

2.建表后指定自增约束

alter table 表名称 modify 字段名 数据类型 auto_increment;

3.删除自增约束

alter table 表名称 modify 字段名 数据类型; # 去掉auto_increment相当于删除

4.重置auto_increment

#方式一
delete from tb1;
ALTER TABLE tbl AUTO_INCREMENT = 100;
#方式二
truncate tb1; #（好处,：简单、AUTO_INCREMENT 值重新开始计数.）

3.6 FOREIGN KEY 约束

限定某个表的某个字段的引用完整性。

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表

特点：

（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列
（2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。
（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表 （4）删表时，先删从表（或先删除外键约束），再删除主表
（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
（6）在“从表”中指定 外键约束，并且一个表可以建立多个外键约束
（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR1005 (HY000): Can’t createtable’database.tablename’(errno: 150)”。
（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
（9）删除外键约束后，必须手动删除对应的索引。

1.建表时添加外键约束

create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);

create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);

(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

FOREIGN KEY: 在表级指定子表中的列
REFERENCES: 标示在父表中的列

2.建表后添加外键约束
如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主
表名(被引用字段) [on update xx][on delete xx];

示例：

ALTER TABLE emp1 ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id)
REFERENCES dept(dept_id);

3.6.1 约束等级

• Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
• Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
• No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
• Restrict方式 ：同no action， 都是立即检查外键约束
• Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式。

阿里开发规范：【 强制 】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发 ，不适合 分布式 、 高并发集群 ；级联更新是强阻塞，存在数据库 更新风暴 的风险；外键影响数据库的 插入速度 。

3.7 DEFAULT 约束

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

1.建表时给字段加默认值

create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);

2.建表后给字段加默认值

alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;