频道栏目
首页 > 资讯 > MySQL > 正文

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

16-09-29        来源:[db:作者]  
收藏   我要投稿

本文知识点概括如下:

外键约束的要求解析:

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

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

外键列和参照列必须具有相似的数据类型。若为数字,则数字的 长度和是否有符号位 必须完全一致;
若为字符,则字符的长度可以不同。
外键列和参照列必须创建索引。如果外键列上没有索引的话,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)
外键约束的参照操作,如下:

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关键字。


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

在实际开发中,列级约束使用较多,表级约束很少使用。本文中前面出现的约束都是列级约束(在列定义之后声明的列级约束)!!
其中,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)
注意:我们应尽量少使用 数据列(字段)的更名以及数据表的更名!
本文的知识点总结如下:

(完)

相关TAG标签
上一篇:台积电:绝大多数7nm客户都会转向6nm_IT新闻_博客园
下一篇:最后一页
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站