MySQL 约束与索引

作者: Manchangdx | 来源:发表于2019-03-27 17:16 被阅读0次

索引 - MySQL / NoSQL
MySQL 约束与索引
MySQL不为人知的主键与唯一索引约束
MySQL-约束与索引
MySQL—— 约束&索引
mysql 索引和约束
day04 索引约束触发器
MySQL索引及查询优化书目录
MySQL索引
索引与约束练习

一、主键约束

1.1 `CONSTRAINT PRIMARY KEY` 创建双主键数据表

如果将一个字段设为主键，可以在字段定义时在后面添加 PRIMARY KEY
所谓双主键，其实是两个字段共同成为一个主键，每张表只能有一个主键

mysql> CREATE TABLE test1 (
    -> field1 INT,
    -> field2 INT,
    -> CONSTRAINT PRIMARY KEY (field1, field2)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> DESC test1;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | NO   | PRI | NULL    |       |
| field2 | int(11) | NO   | PRI | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

1.2 `ALTER TABLE 数据表名 DROP PRIMARY KEY` 删除主键

mysql> ALTER TABLE test1 DROP PRIMARY KEY;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC test1;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | NO   |     | NULL    |       |
| field2 | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

1.3 创建数据表后，`ALTER TABLE 数据表名 ADD PRIMARY KEY` 补增主键

mysql> ALTER TABLE test1 ADD PRIMARY KEY (field1, field2);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test1;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | NO   | PRI | NULL    |       |
| field2 | int(11) | NO   | PRI | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

二、唯一约束

2.1 `CONSTRAINT UNIQUE` 给字段增加唯一约束

注意，CONSTRAINT UNIQUE 后面括号内如果写两个列名
表示这两个列合在一起的值是唯一的，类似双主键的唯一性
同主键一样，对单列设置唯一约束，在定义列那一行后面加 UNIQUE 即可

mysql> CREATE TABLE test2 (
    -> field1 INT PRIMARY KEY,
    -> field2 INT,
    -> field3 INT,
    -> CONSTRAINT UNIQUE (field2),
    -> CONSTRAINT UNIQUE (field3)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC test2;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | NO   | PRI | NULL    |       |
| field2 | int(11) | YES  | UNI | NULL    |       |
| field3 | int(11) | YES  | UNI | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

2.2 删除唯一约束

首先，执行 SHOW CREATE TABLE 数据表名 查看约束名：

mysql> show create table test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `field1` int(11) NOT NULL,
  `field2` int(11) DEFAULT NULL,
  `field3` int(11) DEFAULT NULL,
  PRIMARY KEY (`field1`),   # 主键约束
  UNIQUE KEY `field2` (`field2`),   # 唯一约束
  UNIQUE KEY `field3` (`field3`)    # 唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

执行 ALTER TABLE 数据表名 DROP KEY 唯一约束名 删除唯一约束：

mysql> ALTER TABLE test2 DROP KEY field2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test2;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | NO   | PRI | NULL    |       |
| field2 | int(11) | YES  |     | NULL    |       |
| field3 | int(11) | YES  | UNI | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.3 创建数据表后，`ALTER TABLE 数据表名 ADD UNIQUE 字段名` 添加唯一约束

mysql> ALTER TABLE test2 ADD UNIQUE (field2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test2;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| field1 | int(11) | NO   | PRI | NULL    |       |
| field2 | int(11) | YES  | UNI | NULL    |       |
| field3 | int(11) | YES  | UNI | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

三、外键约束

3.1 `FOREIGN KEY (外键字段) REFERENCES 关联表(主键)` 设置外键约束

mysql> CREATE TABLE test3 (
    -> id INT PRIMARY KEY,
    -> create_at DATE,
    -> test2_id INT,
    -> FOREIGN KEY (test2_id) REFERENCES test2(field1)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> DESC test3;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int(11) | NO   | PRI | NULL    |       |
| create_at | date    | YES  |     | NULL    |       |
| test2_id  | int(11) | YES  | MUL | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.2 `DROP FOREIGN KEY` 删除外键

mysql> SHOW CREATE TABLE test3\G
*************************** 1. row ***************************
       Table: test3
Create Table: CREATE TABLE `test3` (
  `id` int(11) NOT NULL,
  `create_at` date DEFAULT NULL,
  `test2_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test2_id` (`test2_id`),
  CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`test2_id`) 
  REFERENCES `test2` (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> ALTER TABLE test3 DROP FOREIGN KEY test3_ibfk_1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.3 创建数据表后，`ADD CONSTRAINT FOREIGN KEY` 补增外键

mysql> ALTER TABLE test3
    -> ADD CONSTRAINT FOREIGN KEY (test2_id) REFERENCES test2(field1)
    -> ON DELETE CASCADE;  # 如果 test2 中数据被删，外键关联的 test3 数据也被删
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

四、索引

索引分两种 BTREE 和 HASH ，前者为默认索引，又称「B+树」索引
执行 SHOW INDEX FROM 数据表名 可以查看索引详情

4.1 创建数据表时顺便设置索引

mysql> CREATE TABLE test4 (
    -> id INT PRIMARY KEY,
    -> name VARCHAR(64) UNIQUE,
    -> addr VARCHAR(64),
    -> INDEX (id),   # 设置索引
    -> INDEX USING HASH (name)   # 设置 HASH 类型的索引
    -> );
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> SHOW CREATE TABLE test4\G
*************************** 1. row ***************************
       Table: test4
Create Table: CREATE TABLE `test4` (
  `id` int(11) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `addr` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `id` (`id`),   # 索引
  KEY `name_2` (`name`)   # 索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.2 删除索引方法有二

DROP INDEX 索引名 ON 数据表名;
ALTER TABLE 数据表名 DROP INDEX 索引名;

mysql> DROP INDEX id ON test4;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test4 DROP INDEX name_2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test4\G
*************************** 1. row ***************************
       Table: test4
Create Table: CREATE TABLE `test4` (
  `id` int(11) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `addr` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.3 对已有数据表创建索引

CREATE INDEX 索引名 USING 索引类型 ON 数据表名(字段名); 索引名可以省略

mysql> CREATE INDEX 主键_index ON test4(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX name_索引 USING HASH ON test4(name);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SHOW CREATE TABLE test4\G
*************************** 1. row ***************************
       Table: test4
Create Table: CREATE TABLE `test4` (
  `id` int(11) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `addr` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `主键_index` (`id`),
  KEY `name_索引` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

索引 - MySQL / NoSQL
文集主页 Ubuntu 安装 MySQL 和 mysqlclientMySQL 基本操作MySQL 约束与索引My...
MySQL 约束与索引
一、主键约束 1.1 CONSTRAINT PRIMARY KEY 创建双主键数据表如果将一个字段设为主键，可以...
MySQL不为人知的主键与唯一索引约束
转自 58沈剑今天和大家简单聊聊MySQL的约束主键与唯一索引约束： PRIMARY KEY and UNIQU...
MySQL-约束与索引
约束保证数据的完整性、一致性、有效性默认约束在插入记录时,如果不给该字段赋值,则使用默认值字段名数据类型...
MySQL—— 约束&索引
约束对表中字段（值）进行一定的约束限制，以保证数据的完整性、一致性、有效性约束的分类（2中基本约束）一、 (NO...
mysql 索引和约束
mysql 约束:约束类型(关键字):1 : 主键(PRIMARY KEY)2 : 默认值(DEFAULT)3 :...
day04 索引约束触发器
1 索引什么是索引索引的分类创建索引删除索引合理使用索引提升查询效率 2 约束什么是约束约束的类型 ...
MySQL索引及查询优化书目录
MySQL索引的原理之索引目的 MySQL索引的原理之索引原理 MySQL索引的原理之索引的类型 MySQL索引的...
MySQL索引
MySQL索引索引介绍索引原理与分析组合索引索引失效分析索引介绍什么是索引索引：包括聚集索引、覆盖索引...
索引与约束练习
作业部分索引学生表创建id为其主键约束设置学生表中name为非空，sex默认为男性插入记录查看约束情况

MySQL 约束与索引

一、主键约束

1.1 `CONSTRAINT PRIMARY KEY` 创建双主键数据表

1.2 `ALTER TABLE 数据表名 DROP PRIMARY KEY` 删除主键

1.3 创建数据表后，`ALTER TABLE 数据表名 ADD PRIMARY KEY` 补增主键

二、唯一约束

2.1 `CONSTRAINT UNIQUE` 给字段增加唯一约束

2.2 删除唯一约束

2.3 创建数据表后，`ALTER TABLE 数据表名 ADD UNIQUE 字段名` 添加唯一约束

三、外键约束

3.1 `FOREIGN KEY (外键字段) REFERENCES 关联表(主键)` 设置外键约束

3.2 `DROP FOREIGN KEY` 删除外键

3.3 创建数据表后，`ADD CONSTRAINT FOREIGN KEY` 补增外键

四、索引

4.1 创建数据表时顺便设置索引

4.2 删除索引方法有二

4.3 对已有数据表创建索引

相关文章

索引 - MySQL / NoSQL

MySQL 约束与索引

MySQL不为人知的主键与唯一索引约束

MySQL-约束与索引

MySQL—— 约束&索引

mysql 索引和约束

day04 索引约束触发器

MySQL索引及查询优化书目录

MySQL索引

索引与约束练习

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL 约束与索引

一、主键约束

1.1 CONSTRAINT PRIMARY KEY 创建双主键数据表

1.2 ALTER TABLE 数据表名 DROP PRIMARY KEY 删除主键

1.3 创建数据表后，ALTER TABLE 数据表名 ADD PRIMARY KEY 补增主键

二、唯一约束

2.1 CONSTRAINT UNIQUE 给字段增加唯一约束

2.2 删除唯一约束

2.3 创建数据表后，ALTER TABLE 数据表名 ADD UNIQUE 字段名 添加唯一约束

三、外键约束

3.1 FOREIGN KEY (外键字段) REFERENCES 关联表(主键) 设置外键约束

3.2 DROP FOREIGN KEY 删除外键

3.3 创建数据表后，ADD CONSTRAINT FOREIGN KEY 补增外键

四、索引

4.1 创建数据表时顺便设置索引

4.2 删除索引方法有二

4.3 对已有数据表创建索引

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

1.1 `CONSTRAINT PRIMARY KEY` 创建双主键数据表

1.2 `ALTER TABLE 数据表名 DROP PRIMARY KEY` 删除主键

1.3 创建数据表后，`ALTER TABLE 数据表名 ADD PRIMARY KEY` 补增主键

2.1 `CONSTRAINT UNIQUE` 给字段增加唯一约束

2.3 创建数据表后，`ALTER TABLE 数据表名 ADD UNIQUE 字段名` 添加唯一约束

3.1 `FOREIGN KEY (外键字段) REFERENCES 关联表(主键)` 设置外键约束

3.2 `DROP FOREIGN KEY` 删除外键

3.3 创建数据表后，`ADD CONSTRAINT FOREIGN KEY` 补增外键