MySQL快查-数据完整性

MySQL快查

因为在日常工作学习中经常忘记mysql的一些语句、关键字、操作等内容，所以最近抽取时间写了以下关于mysql相关内容。相当于一本字典吧

---

重置mysql密码
数据类型
运算符
常用函数
本文
数据库的基本操作
对表本身的操作
对表中数据的操作
子查询
多表连接
索引
视图
预处理SQL语句
自定义函数与存储过程
在MySQL中编程

---

主键约束

primary key
# 设置主键的目的是为了帮助mysql以最快的速度查找到表中的指定信息。
# 主键包含unique和not null约束
# 可以设置一个字段为主键，也可以设置多个字段为主键,如:

## 单个字段作为主键
create table t1 (id int primary key, name char(10));
# 或者
create table t1 (id int, name char(10), primary key(id));

desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 多个字段作为主键
create table t2 (id int, 
				commodity_id int, 
				price float, 
				primary key(id, commodity_id));  # 多个字段用“,”隔开

desc t2;
+--------------+-------+------+-----+---------+-------+
| Field        | Type  | Null | Key | Default | Extra |
+--------------+-------+------+-----+---------+-------+
| id           | int   | NO   | PRI | NULL    |       |
| commodity_id | int   | NO   | PRI | NULL    |       |
| price        | float | YES  |     | NULL    |       |
+--------------+-------+------+-----+---------+-------+
3 rows in set (0.01 sec)

更多

外键约束

外键是描述表与表之间的关系。
表和表之间往往存在一种“父子”关系，这种关系一般用外键来约束。比如员工和部门的关系。

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

• 外键references table_name(col_name) 中的col_name必须必须有索引值(通过show index from table_name可以查看)
• constraint [symbol]：可以理解为为外键取名
  • 如果constraint关键字不写或者关键字后面不跟随一个名字（symbol），则会自动生成
  • 在8.0.16以前的版本生成的名字是 index_name，8.0.16及之后的版本忽略index_name
• references 指定关联到哪个表的哪个(些)字段
• on delete 描述删除被关联的表中的数据（被关联到字表的）时怎么处理
• on update 同上，只是描述的时更新的
• reference_option:
  • restrict 当要删除或更新父表中被references的列上在外键中出现的值时，拒绝对父表的更新或删除
  • set null 当父表删除或更新行时，设置子表中references到父表的字段列设为null（注：该列不能时not null约束的）
  • cascade 父表删除或更新时自动删除或更新字表对应的行
  • no action 不采取任何行动。根restrict差不多
  • set default 根set null差不多，只是设置的值是子表中对应字段用default指定的值

# 创建表时指定外键

# 需要先建父表
create table t3 (id int primary key, co_id int default 99);
desc t3;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   | PRI | NULL    |       |
| co_id | int  | YES  |     | 99      |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 再建子表并设置外键
create table t4 (id int primary key,name char(10), t3_id int,foreign key (t3_id) references t3(id));
desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
| t3_id | int      | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 主键有索引，如果想references其他字段可以将
# 字段设为主键或者为字段设置索引,方法如下:
#			 自己取的键名  表名        字段名
CREATE INDEX key_name ON table_name(col_name);


# 为已有表添加外键
# 存在以下表
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc t2;
+--------------+-------+------+-----+---------+-------+
| Field        | Type  | Null | Key | Default | Extra |
+--------------+-------+------+-----+---------+-------+
| id           | int   | NO   | PRI | NULL    |       |
| commodity_id | int   | NO   | PRI | NULL    |       |
| price        | float | YES  |     | NULL    |       |
+--------------+-------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 将t2中的commodity_id关联到t1的id
alter table t2 add foreign key(commodity_id) references t1(id);



# 也可以写全
create table t1(id int primary key, 
				name char(10), 
				constraint fk_t1_2_t2
				foreign key(id) references t2(x_id)
				on delete set default
				on update cascade);

更多

唯一性约束

约束字段的值必须唯一，如果插入的数据中被唯一约束的字段存在相同值时会报错，无法插入。

unique

create table t1 (id int unique,...)
create table t1 (id int, name char(10), unique(id,name));
alter table t1 add unique(name);

非空约束

指定某字段的值不能为空

not null

create t1 (id int not null, ...);

检查约束

检查约束约束字段值的范围

create table t1(id int, birth char(12), check(birth>'1700-01-01'));
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| birth | char(12) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 大于这个值就插入成功
mysql> insert into t1 values(1,'2000-01-01');
Query OK, 1 row affected (0.01 sec)

# 小于设定的‘1700-01-01’就报错，无法插入
mysql> insert into t1 values(1,'1600-01-01');
ERROR 3819 (HY000): Check constraint 't5_chk_1' is violated.