MySQL学习笔记3:约束以及修改数据表

时间:2021-12-20 07:20:44

本文知识点概括如下:

MySQL学习笔记3:约束以及修改数据表

外键约束的要求解析:

MySQL学习笔记3:约束以及修改数据表

约束的作用和种类,如下:

MySQL学习笔记3:约束以及修改数据表

列级约束:约束只针对于某一个字段。。
表级约束:约束针对于2个或2个以上的字段。
FOREIGN KEY
外键约束的作用:保持数据的一致性和完整性;实现数据表的一对一或一对多的关系。
外键列:加过FOREIGN KEY关键词的列。
参照列:外键列所参照的那一列。
子表:具有外键列的表。
父表:子表所参照的表。
外键约束的要求,如下:

MySQL学习笔记3:约束以及修改数据表

外键列和参照列必须具有相似的数据类型。若为数字,则数字的 长度和是否有符号位 必须完全一致
若为字符,则字符的长度可以不同。
外键列和参照列必须创建索引。如果外键列上没有索引的话,MySQL将自动创建索引。
而参照列没有索引的话,MySQL则不会自动创建索引!
编辑数据表的默认存储引擎
打开MySQL的配置文件 my.ini ,修改如下所示的行(将默认的存储引擎修改为INNODB):
[mysqld]
default-storage-engine=INNODB
修改完配置文件,记得重新启动MySQL服务!

mysql> CREATE TABLE provinces(   #创建父表(子表所参照的表)
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, # id被定义为主键,而主键列会自动创建索引!
    -> pname VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW CREATE TABLE provinces; #可以看到,数据表provinces的存储引擎确实为InnoDB
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                              |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| provinces | CREATE TABLE `provinces` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE users(  #创建子表(具有外键列的表)
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED, #外键列pid与参照列id的类型都是SMALLINT UNSIGNED,符合要求。
    -> FOREIGN KEY (pid) REFERENCES provinces(id) #pid是外键列,数据表provinces中的id是参照列。
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW INDEXES FROM provinces\G; #显示索引(参照列id被定义为主键,会自动创建索引)
*************************** 1. row ***************************
        Table: provinces
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id   # id被定义为主键,主键列会自动创建索引!
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> SHOW INDEXES FROM users\G; #可以看到,数据表users中,存在2个索引。
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id   #第一个索引(数据表users中的id,被定义为主键,会自动创建索引!)
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: pid   
 Seq_in_index: 1
  Column_name: pid  #第二个索引(如果外键列上没有索引的话,MySQL将自动创建索引。)
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),  #id被定义为主键,而主键列会自动创建索引。
  KEY `pid` (`pid`),   #pid被定义为外键列,系统会为外键列自动添加索引。
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
外键约束的参照操作,如下:

MySQL学习笔记3:约束以及修改数据表

CASCADE使用示例
mysql> CREATE TABLE users1(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE #父表中的删除或更新操作,同步到子表。
    -> );
Query OK, 0 rows affected (0.26 sec)

mysql> SHOW CREATE TABLE users1;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users1 | CREATE TABLE `users1` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE    
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |                                #父表中的删除或更新操作,同步到子表。
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

先在父表中插入记录,然后才能在子表中插入记录!(注意插入记录的顺序)
mysql> INSERT provinces(pname) VALUES('A'); #向父表中插入记录
Query OK, 1 row affected (0.09 sec)

mysql> INSERT provinces(pname) VALUES('B');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT provinces(pname) VALUES('C');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
|  3 | C     |
+----+-------+
3 rows in set (0.00 sec)

mysql> INSERT users1(username,pid) VALUES('Tom',3);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT users1(username,pid) VALUES('John',7);#记录插入失败,因为父表中id=7的记录不存在(插入失败,但占用了一个编号)。
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE)
mysql> INSERT users1(username,pid) VALUES('John',1);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT users1(username,pid) VALUES('Rose',3);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users1; #第2次插入记录时,插入失败。占用了一个编号2。
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    3 |
|  3 | John     |    1 |
|  4 | Rose     |    3 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> DELETE FROM provinces WHERE id=3;  #删除父表中id=3的记录(删除操作会同步到子表中的相应记录)
Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM provinces; #可以看到,父表中id=3的记录已经被删除。
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users1; #子表中外键列pid=3的记录,已经被删除。
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  3 | John     |    1 |
+----+----------+------+
1 row in set (0.00 sec)

SET NULL 使用示例
mysql> CREATE TABLE users2(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE SET NULL #删除父表记录时,将子表的相应记录的外键列pid设置为NULL。
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)

mysql> INSERT provinces(pname) VALUES('C');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
|  4 | C     |
+----+-------+
3 rows in set (0.00 sec)


mysql> INSERT users2(username,pid) VALUES('Tom',1);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT users2(username,pid) VALUES('Jerry',4);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT users2(username,pid) VALUES('Marry',4);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users2;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    1 |
|  2 | Jerry    |    4 |
|  3 | Marry    |    4 |
+----+----------+------+
3 rows in set (0.00 sec)
mysql> DELETE FROM provinces WHERE id=4; #删除父表中id=4的记录,同时将子表中外键列pid=4的记录设置为NULL
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM provinces;#可以看到,父表中id=4的记录已经被删除。
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users2;#子表中pid=4的记录已经被设置为NULL
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    1 |
|  2 | Jerry    | NULL |
|  3 | Marry    | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

RESTRICT使用示例:
mysql> CREATE TABLE users3(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE RESTRICT  #拒绝对父表的删除或更新操作。
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)

mysql> INSERT users3 (username,pid) VALUES('Tome',1);#向子表中插入记录
Query OK, 1 row affected (0.06 sec)

mysql> INSERT users3 (username,pid) VALUES('Jerry',2);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM users3;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tome     |    1 |
|  2 | Jerry    |    2 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> DELETE FROM provinces WHERE id=2;#删除失败(RESTRICT拒绝对父表的删除或更新操作)
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`users3`, CONSTRAINT `users3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`))

NO ACTION 使用示例(在MySQL中NO ACTION与RESTRICT作用相同):
mysql> CREATE TABLE users4(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE NO ACTION #拒绝对父表的删除或更新操作
    -> );
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)

mysql> INSERT users4(username,pid) VALUES('Tom',1); #向子表中插入记录
Query OK, 1 row affected (0.03 sec)

mysql> INSERT users4(username,pid) VALUES('Jerry',2);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users4;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    1 |
|  2 | Jerry    |    2 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> DELETE FROM provinces WHERE id=2;#删除失败(NO ACTION拒绝对父表的删除或更新操作)
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`users3`, CONSTRAINT `users3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`))
在实际开发中,我们很少使用物理的外键约束,大多使用逻辑的外键约束。
因为只有INNODB这一种存储引擎支持物理的外键约束,所以,在实际开发中,我们一般不去定义物理的外键约束。
所谓的逻辑外键,指定就是,我们在定义2张表的结构的时候,按照存在的某种结构的方式去定义,但是不使用FOREIGN KEY关键字


表级约束与列级约束,如下:

MySQL学习笔记3:约束以及修改数据表

在实际开发中,列级约束使用较多,表级约束很少使用。本文中前面出现的约束都是列级约束(在列定义之后声明的列级约束)!!
其中,NOT NULL约束和DEFAULT约束,只有列级约束(没有表级约束)!
而主键约束,唯一约束和外键约束,既有列级约束,又有表级约束!

修改数据表
添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.06 sec)

mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;#默认情况下,将列添加到最后。
Query OK, 1 row affected (0.27 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username; #将password列添加到username列的后面。
Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST; #将truename列添加到最前面。
Query OK, 1 row affected (0.22 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| truename | varchar(20)          | NO   |     | NULL    |                |
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

添加多列(添加多列时,只能添加到数据表的后面!)
ALTER TABLE tbl_name ADD [COLUMN] (col_name1 column_definition1,……)
mysql> ALTER TABLE users1 ADD COLUMN (sex ENUM('woman','man') NOT NULL,score FLOAT(4,2) NOT NULL);#添加多列
Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1; #可以看到,添加多列时,是将列添加到了数据表的最后!
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| truename | varchar(20)          | NO   |     | NULL    |                |
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| sex      | enum('woman','man')  | NO   |     | NULL    |                |
| score    | float(4,2)           | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

mysql> ALTER TABLE users1 DROP truename; #删除truename列
Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| sex      | enum('woman','man')  | NO   |     | NULL    |                |
| score    | float(4,2)           | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

mysql> ALTER TABLE users1 DROP password,DROP age; #删除password列和age列。
Query OK, 1 row affected (0.22 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| sex      | enum('woman','man')  | NO   |     | NULL    |                |
| score    | float(4,2)           | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE users1 ADD (number INT NOT NULL,height FLOAT(5,2)),DROP sex,DROP score; #添加列和删除列同时进行。
Query OK, 1 row affected (0.26 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| number   | int(11)              | NO   |     | NULL    |                |
| height   | float(5,2)           | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

修改数据表
添加主键约束(主键约束只能有1个)
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)
mysql> CREATE TABLE users5( #创建一个表
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW CREATE TABLE users5;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                              |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users5 ADD id SMALLINT UNSIGNED;#添加id列
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users5;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> ALTER TABLE users5 ADD CONSTRAINT PK_users5_id PRIMARY KEY(id);#为id列添加主键约束。
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users5; #此时,id列已经被设置为主键。
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

添加唯一约束(唯一约束可以有多个)
ALTER TABLE tbl_name ADD [CONSTRAINT[sysbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,……)
mysql> ALTER TABLE users5 ADD UNIQUE (username); #为username列添加唯一约束
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE users5;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                            |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`), #主键约束
  UNIQUE KEY `username` (`username`) #唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

添加外键约束(外键约束可以有多个)
ALTER TABLE tbl_name ADD [CONSTRAINT[sysbol]] FOREIGN KEY [index_name](index_col_name,……) reference_definition
mysql> SHOW COLUMNS FROM provinces;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| pname | varchar(20)          | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> SHOW COLUMNS FROM users5;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
mysql> ALTER TABLE users5 ADD FOREIGN KEY (pid) REFERENCES provinces(id);#添加外键约束
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE users5;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),  #主键约束
  UNIQUE KEY `username` (`username`), #唯一约束
  KEY `pid` (`pid`), 
  CONSTRAINT `users5_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)  #外键约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT}
mysql> ALTER TABLE users5 ADD age TINYINT UNSIGNED NOT NULL; #添加字段age
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users5; #可以看到,age字段的Default为NULL,表示没有默认值
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.04 sec)
mysql> ALTER TABLE users5  ALTER age SET DEFAULT 15; #为age列添加默认约束(设置默认值为15)
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users5; #可以看到age字段被设置了默认值15
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | 15      |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE users5  ALTER age DROP DEFAULT; #删除默认约束
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users5;#可以看到age字段的Default重新变为NULL,表示没有默认值(删除了默认约束)
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY
mysql> SHOW COLUMNS FROM users5;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | UNI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   | PRI | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE users5 DROP PRIMARY KEY;#删除主键约束;主键只能有1个,因此不需要加主键的名字。
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW COLUMNS FROM users5;#可以看到,id已经不再是主键约束了。
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | PRI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   |     | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name #唯一约束可以有多个,所以要指出具体的index_name
mysql> SHOW INDEXES FROM users5\G; #查看约束的名字
*************************** 1. row ***************************
        Table: users5
   Non_unique: 0
     Key_name: username  #此处的username是索引名(或称为约束名)
 Seq_in_index: 1
  Column_name: username  #username列被定义为主键,系统会为主键自动创建索引。
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users5
   Non_unique: 1
     Key_name: pid #此处的pid是索引的名字(或称为约束名)
 Seq_in_index: 1
  Column_name: pid #pid列是外键列,系统会自动为外键列创建索引。
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.04 sec)

mysql> ALTER TABLE users5 DROP INDEX username; #删除username约束(username字段还在)
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users5; #可以看到,username字段仍然存在,但主键约束已经不在了。
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   |     | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> SHOW INDEXES FROM users5\G; #现在,只剩下1个约束了(主键约束已经被删除)
*************************** 1. row ***************************
        Table: users5
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol  #外键约束可以有多个,因此,必须要加上约束的名称。
mysql> SHOW CREATE TABLE users5; #查看外键约束的名称
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                           |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `age` tinyint(3) unsigned NOT NULL,
  KEY `pid` (`pid`),
  CONSTRAINT `users5_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)#此处的users5_ibfk_1 就是外键约束的名称。
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users5 DROP FOREIGN KEY users5_ibfk_1; #删除外键约束
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE users5; #可以看到,外键约束已经被删除。但pid列上的索引还在!
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                           |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `age` tinyint(3) unsigned NOT NULL,
  KEY `pid` (`pid`)  #pid字段上的索引还存在
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users5 DROP INDEX pid; #此处的pid是索引名。(删除pid列上的索引)
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE users5; #可以看到pid列上的索引已经被删除。
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                      |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users5 | CREATE TABLE `users5` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `age` tinyint(3) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改列定义(列名保持不变,只修改列的定义)
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
mysql> SHOW COLUMNS FROM users5; #当前id列位于第3行
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| id       | smallint(5) unsigned | NO   |     | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> ALTER TABLE users5 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; #将id列移动到最前面
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users5;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | NO   |     | NULL    |       |
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE users5 MODIFY id TINYINT UNSIGNED NOT NULL;#修改id列的定义(数据类型修改为TINYINT)
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users5;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | tinyint(3) unsigned  | NO   |     | NULL    |       |
| username | varchar(10)          | NO   |     | NULL    |       |
| pid      | smallint(5) unsigned | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.05 sec)
修改数据类型时,要注意:将大类型 修改为 小类型,有可能会造成数据的丢失!
修改列名称(使用CHANGE既可以修改列名称,又可以修改列定义)

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
mysql> ALTER TABLE users5 CHANGE pid p_id TINYINT UNSIGNED NOT NULL; #修改pid列的 列名称和列定义
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users5;#列名称修改为p_id,列的数据类型修改为TINYINT
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id       | tinyint(3) unsigned | NO   |     | NULL    |       |
| username | varchar(10)         | NO   |     | NULL    |       |
| p_id     | tinyint(3) unsigned | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | NO   |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
数据表更名
方法1:ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
方法2:使用该方法可以为多张数据表更名
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2,……]

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| provinces      |
| users          |
| users1         |
| users2         |
| users3         |
| users4         |
| users5         |
+----------------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE users5 RENAME users55; #将数据表users5更名为users55
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| provinces      |
| users          |
| users1         |
| users2         |
| users3         |
| users4         |
| users55        |
+----------------+
7 rows in set (0.00 sec)
mysql> RENAME TABLE users1 TO users11,users3 TO users33;#同时更名多个数据表;
Query OK, 0 rows affected (0.16 sec)

mysql> SHOW TABLES;#可以看到,已经将表users1更名为users11,将表users3更名为users33;
+----------------+
| Tables_in_test |
+----------------+
| provinces      |
| users          |
| users11        |
| users2         |
| users33        |
| users4         |
| users55        |
+----------------+
7 rows in set (0.00 sec)
注意:我们应尽量少使用 数据列(字段)的更名以及数据表的更名!
本文的知识点总结如下:

MySQL学习笔记3:约束以及修改数据表


(完)