MySQL之约束与修改数据表

约束

① 约束保证数据的完整性和一致性；
② 约束分为表级约束和列级约束；
③ 约束类型包括：
　　NOT NULL 非空约束
　　PRIMARY KEY 主键约束
　　UNIQUE KEY 唯一约束
　　DEFAULT 默认约束
　　FOREIGN KEY 外键约束
外键约束
　　保持数据一致性，完整性
　　实现一对一或一对多关系
外键约束的要求
　　1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表
　　2. 数据表的存储引擎只能为InnoDB
　　3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同
　　4. 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引
编辑数据表的默认存储引擎
　　修改配置文件 my.ini,重启服务使其生效
　　　　default-storage-engine=INNODB
查看新创建表的的存储引擎：
　　mysql> USE test;
　　Database changed
　　
　　mysql> CREATE TABLE provinces(
　　　　-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
　　　　-> pname VARCHAR(20) NOT NULL
　　　　-> );
　　Query OK, 0 rows affected (0.03 sec)
　　
　　mysql> SHOW CREATE TABLE provinces;
　　--------------------------------------
　　| 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,
　　　　-> FOREIGN KEY (pid) REFERENCES provinces(id)
　　　　-> );
　　ERROR 1005 (HY000): Can’t create table ‘test.users’ (errno: 150)
　　子表与父表具有相同的数据类型，但有无符号位不同（默认有符号位） ----> 创建失败
　　mysql> CREATE TABLE users(
　　　　-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
　　　　-> pid BIGINT,
　　　　-> FOREIGN KEY (pid) REFERENCES provinces(id)
　　　　-> );
　　ERROR 1005 (HY000): Can’t create table ‘test.users’ (errno: 150)
　　子表与父表具有相同的数据类型，有无符号位也相同 ----> 创建成功
　　mysql> CREATE TABLE users(
　　　　-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
　　　　-> username VARCHAR(10) NOT NULL,
　　　　-> pid SMALLINT UNSIGNED,
　　　　-> FOREIGN KEY (pid) REFERENCES provinces(id)
　　　　-> );
　　Query OK, 0 rows affected (0.02 sec)
字表中的pid为外键列，父表中的id为参照列
查看父表的索引，\G表示以网格的方式显示：
　　mysql> SHOW INDEXES FROM provinces\G;
　　*************************** 1. row ***************************
　　　Table: 　provinces
　　　Non_unique:　 0
　　　Key_name: 　PRIMARY
　　　Seq_in_index:　1
　　　Column_name:　 id
　　　Collation:　 A
　　　Cardinality: 　0
　　　Sub_part:　 NULL
　　　Packed: 　NULL
　　　Null:
　　　Index_type:　 BTREE
　　　Comment:
　　　Index_comment:
　　　1 row in set (0.01 sec)
查看子表的索引，子表存在两个索引，主键上的索引，以及自动生成的外键上的索引：
　　mysql> SHOW INDEXES FROM users\G;
　　*************************** 1. row ***************************
　　Table: users
　　Non_unique: 0
　　Key_name: PRIMARY
　　Seq_in_index: 1
　　Column_name: 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
　　Collation: A
　　Cardinality: 0
　　Sub_part: NULL
　　Packed: NULL
　　Null: YES
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　2 rows in set (0.01 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),
　　KEY pid (pid),
　　CONSTRAINT users_ibfk_1 FOREIGN KEY (pid) REFERENCES 　provinces (id)
　　) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
　　±------±---------------------------------------------------------------
　　1 row in set (0.01 sec)
外键约束的参照操作
　　① CASCADE: 从父表删除或更新行时自动删除或更新子表中匹配的子表中匹配的行
　　② SET NULL：从父表删除或更新行时，设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL
　　③ RESTRICT: 拒绝对父表的删除和更新操作
　　④ NO ACTION: 标准SQL的关键字，在MySQL中与RESTRICT相同
　　　　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.02 sec)

表级约束与列级约束
　　表级约束： 对多个数据列建立约束；只能在列定义后声明
　　列级约束： 对一个数据列建立约束；既可以在列定义的时候声明，也可以在列定义后声明。

修改数据表

修改数据表–添加删除操作
添加单列
　ALTER TABLE tbl_name ADD[COLUMN] col_name
　column_definition [FIRST | AFTER col_name]
　　　mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 15;
　　　Query OK, 0 rows affected (0.03 sec)
　　　Records: 0 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 | | 15 | |
　　　±---------±---------------------±-----±----±--------±---------------+
　　　4 rows in set (0.02 sec)
添加多列
　ALTER TABLE tbl_name ADD[COLUMN] (col_name column_definition,…)
删除列
　ALTER TABLE tb1_name DROP [COLUMN] col_name
　　　mysql> ALTER TABLE users1 DROP age;
　　　Query OK, 0 rows affected (0.04 sec)
　　　Records: 0 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 | |
　　　±---------±---------------------±-----±----±--------±---------------+
　　　3 rows in set (0.01 sec)

删除列和添加列可在同一条语句中执行，用,分隔即可

**添加主键约束 **
　ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]]
　PRIMARY KEY [index_type] (index_col_name,…)
添加唯一约束：
　ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]]
　UNIQUE [INDEX | KEY] [index_name] [index_type]
　(index_col_name,…)
　　　mysql> ALTER TABLE users1 ADD UNIQUE (username);
　　　Query OK, 0 rows affected (0.04 sec)
　　　Records: 0 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 | UNI | NULL | |
　　　| pid | smallint(5) unsigned | YES | MUL | NULL | |
　　　±---------±---------------------±-----±----±--------±---------------+
　　　3 rows in set (0.01 sec)
添加外键约束
　ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]]
　FOREIGN KEY [index_name] (index_col_name,…)
　reference_definition
　　　mysql> ALTER TABLE users1 ADD UNIQUE (username);
　　　Query OK, 0 rows affected (0.04 sec)
　　　Records: 0 Duplicates: 0 Warnings: 0
　　　
　　　mysql> SHOW CREATE TABLE users1;
添加/删除默认约束
　ALTER TABLE tbl_name ALTER [COLUMN] col_name
　{SET DEFAULT literal | DROP DEFAULT}

删除主键约束：
　ALTER TABLE tbl_name DROP PRIMARY KEY
　　　mysql> ALTER TABLE users3 DROP PRIMARY KEY;
　　　Query OK, 0 rows affected (0.03 sec)
　　　Records: 0 Duplicates: 0 Warnings: 0
　　　
　　　mysql> SHOW COLUMNS FROM users3;
　　　±---------±---------------------±-----±----±--------±------+
　　　| 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
　　　mysql> ALTER TABLE users3 DROP INDEX username;
　　　Query OK, 0 rows affected (0.02 sec)
　　　Records: 0 Duplicates: 0 Warnings: 0
　　　
　　　mysql> SHOW COLUMNS FROM users3;
　　　±---------±---------------------±-----±----±--------±------+
　　　| 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 users3\G;
　　　*************************** 1. row ***************************
　　　Table: users3
　　　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 users3;
　　　±-------±-------------------------------------------------------
　　　| Table | Create Table
　　　±-------±-------------------------------------------------------
　　　| users3 | CREATE TABLE users3 (
　　　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 users3_ibfk_1 FOREIGN KEY (pid) REFERENCES 　　　provinces (id)
　　　) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
　　　±-------±---------------------------------------------------------
　　　1 row in set (0.00 sec)
　　　
　　　mysql> ALTER TABLE users3 DROP FOREIGN KEY users3_ibfk_1;
　　　Query OK, 0 rows affected (0.02 sec)
　　　Records: 0 Duplicates: 0 Warnings: 0
修改数据表–修改列定义和更名数据表
修改列定义
　ALTER TABLE tb1_name MODIFY[COLUMN] col_name
　column_definition [FIRST | AFTER col_name]
　　　mysql> ALTER TABLE users3 MODIFY id TINYINT UNSIGNED 　　　NOT NULL FIRST;
　　　Query OK, 0 rows affected (0.03 sec)
　　　Records: 0 Duplicates: 0 Warnings: 0
　　　　
　　　mysql> SHOW COLUMNS from users3;
　　　±---------±---------------------±-----±----±--------±------+
　　　| 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.01 sec)
修改列名称
　ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name
　new_col_name column_definition [FIRST | AFTER col_name]
　　　mysql> ALTER TABLE users3 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
　　　Query OK, 0 rows affected (0.02 sec)
　　　Records: 0 Duplicates: 0 Warnings: 0
　　　
　　　mysql> SHOW COLUMNS from users3;
　　　±---------±--------------------±-----±----±--------±------+
　　　| 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_tb1_name
　　　mysql> ALTER TABLE users3 RENAME users4;
　　　Query OK, 0 rows affected (0.01 sec)
方法2：
　RENAME TABLE tb1_name TO new_tb1_name
　[, tbl_name2 TO new_tb1_name2]…
　　　mysql> RENAME TABLE users4 TO users3;
　　　Query OK, 0 rows affected (0.01 sec)