mysql之约束以及修改数据表

数据约束的分类：

 

————————————————————————————————————————————————————

外键约束的要求解析：

//在my文件中的这句话代表着搜索引擎，如果不是的就需要技能型更改。然后重启。my文件存在于我的电脑中的mysql文件夹里。# The default storage engine that will be used when create new tables whendefault-storage-engine=INNODB

root@127.0.0.1 t2>CREATE TABLE province(-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> useame VARCHAR(20) NOT NULL-> );Query OK, 0 rows affected (0.13 sec)root@127.0.0.1 t2>SHOW CREATE TABLE province;+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table| Create Table|+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| province | CREATE TABLE `province` (  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  `useame` varchar(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)root@127.0.0.1 t2>CREATE TABLE aname(-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> useame VARCHAR(10) NOT NULL,-> pid BIGINT ,-> FOREIGN KEY(pid) REFERENCES province(id)-> );ERROR 1005 (HY000): Can't create table 't2.aname' (ero: 150)root@127.0.0.1 t2>CREATE TABLE aname(-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> useame VARCHAR(10) NOT NULL,-> pid SMALLINT UNSIGNED,-> FOREIGN KEY(pid) REFERENCES province(id)-> );Query OK, 0 rows affected (0.23 sec)

root@127.0.0.1 t2>SHOW INDEXES FROM province\G;*************************** 1. row ***************************Table: provinceNon_unique: 0 Key_name: PRIMARY Seq_in_index: 1  Column_name: idCollation: A  Cardinality: 0 Sub_part: NULLPacked: NULL Null:Index_type: BTREE  Comment:Index_comment:1 row in set (0.10 sec)ERROR:No query specifiedroot@127.0.0.1 t2>SHOW INDEXES FROM aname\G;*************************** 1. row ***************************Table: anameNon_unique: 0 Key_name: PRIMARY Seq_in_index: 1  Column_name: idCollation: A  Cardinality: 0 Sub_part: NULLPacked: NULL Null:Index_type: BTREE  Comment:Index_comment:*************************** 2. row ***************************Table: anameNon_unique: 1 Key_name: pid Seq_in_index: 1  Column_name: pidCollation: A  Cardinality: 0 Sub_part: NULLPacked: NULL Null: YESIndex_type: BTREE  Comment:Index_comment:2 rows in set (0.00 sec)ERROR:No query specified

——————————————————————————————————————————————————————————————————

外键约束的参照操作：

root@127.0.0.1 t2>CREATE TABLE aname1(-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> useame VARCHAR(10) NOT NULL,-> pid SMALLINT UNSIGNED,-> FOREIGN KEY(pid) REFERENCES province(id) ON DELETE CASCADE-> );Query OK, 0 rows affected (0.23 sec)root@127.0.0.1 t2>SHOW CREATE TABLE aname1;+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table|+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| aname1 | CREATE TABLE `aname1` (  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  `useame` varchar(10) NOT NULL,  `pid` smallint(5) unsigned DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `pid` (`pid`),  CONSTRAINT `aname1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

root@127.0.0.1 t2>INSERT province(useame) VALUES('A');Query OK, 1 row affected (0.16 sec)root@127.0.0.1 t2>INSERT province(useame) VALUES('B');Query OK, 1 row affected (0.14 sec)root@127.0.0.1 t2>INSERT province(useame) VALUES('C');Query OK, 1 row affected (0.13 sec)root@127.0.0.1 t2>SELECT * FROM procvince;ERROR 1146 (42S02): Table 't2.procvince' doesn't existroot@127.0.0.1 t2>SELECT * FROM province;+----+----------+| id | useame |+----+----------+|  1 | A||  2 | B||  3 | C|+----+----------+3 rows in set (0.00 sec)root@127.0.0.1 t2>INSERT aname1(useame,pid) VALUES('D',3);Query OK, 1 row affected (0.16 sec)root@127.0.0.1 t2>INSERT aname1(useame,pid) VALUES('E',1);Query OK, 1 row affected (0.07 sec)root@127.0.0.1 t2>INSERT aname1(useame,pid) VALUES('E',t);ERROR 1054 (42S22): Unknown column 't' in 'field list'root@127.0.0.1 t2>INSERT aname1(useame,pid) VALUES('F',7);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t2`.`aname1`, CONSTRAINT `aname1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`) ON DELETE CASCADE)root@127.0.0.1 t2>INSERT aname1(useame,pid) VALUES('F',2);Query OK, 1 row affected (0.05 sec)root@127.0.0.1 t2>SELECT * FROM province;+----+----------+| id | useame |+----+----------+|  1 | A||  2 | B||  3 | C|+----+----------+3 rows in set (0.00 sec)root@127.0.0.1 t2>SELECT * FROM aname1;+----+----------+------+| id | useame | pid  |+----+----------+------+|  1 | D|3 ||  2 | E|1 ||  4 | F|2 |+----+----------+------+3 rows in set (0.00 sec)root@127.0.0.1 t2>DELETE FROM province where id = 3;Query OK, 1 row affected (0.20 sec)root@127.0.0.1 t2>SELECT * FROM province;+----+----------+| id | useame |+----+----------+|  1 | A||  2 | B|+----+----------+2 rows in set (0.00 sec)root@127.0.0.1 t2>SELECT * FROM aname1;+----+----------+------+| id | useame | pid  |+----+----------+------+|  2 | E|1 ||  4 | F|2 |+----+----------+------+2 rows in set (0.00 sec)

——————————————————————————————————————————————————————————————————

表级约束以及列级约束：

在实际开发的时候我们使用列级约束的较多，表级约束较少。default和not null只存在列级约束，其他的都存在表级约束。

————————————————————————————————————————————————————————————

修改数据表增加和删除列：

添加（删除）列 alter table +表明drop+列名
添加（删除）多列 alter table +表明 【drop+列名，drop+列名】；（在添加单列的时候所有的列不需要指明小括号，在指明多列的时候，我们不能指定位子）

添加单列alert table tbl_name add +列名字+column_definition [firstafter col_name]

 

root@127.0.0.1 t2>ALTER TABLE aname1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;Query OK, 2 rows affected (0.34 sec)Records: 2  Duplicates: 0  Waings: 0root@127.0.0.1 t2>SHOW COLUMNS FROM aname1;+----------+----------------------+------+-----+---------+----------------+| Field| Type | Null | Key | Default | Extra  |+----------+----------------------+------+-----+---------+----------------+| id| smallint(5) unsigned | NO| PRI | NULL| auto_increment || useame | varchar(10)  | NO| | NULL||| pid  | smallint(5) unsigned | YES  | MUL | NULL||| age  | tinyint(3) unsigned  | NO| | 10  ||+----------+----------------------+------+-----+---------+----------------+4 rows in set (0.01 sec)

root@127.0.0.1 t2>ALTER TABLE aname1 ADD ab TINYINT UNSIGNED NOT NULL AFTER useame;Query OK, 2 rows affected (0.30 sec)Records: 2  Duplicates: 0  Waings: 0root@127.0.0.1 t2>SHOW COLUMNS FROM aname1;+----------+----------------------+------+-----+---------+----------------+| Field| Type | Null | Key | Default | Extra  |+----------+----------------------+------+-----+---------+----------------+| id| smallint(5) unsigned | NO| PRI | NULL| auto_increment || useame | varchar(10)  | NO| | NULL||| ab| tinyint(3) unsigned  | NO| | NULL||| pid  | smallint(5) unsigned | YES  | MUL | NULL||| age  | tinyint(3) unsigned  | NO| | 10  ||+----------+----------------------+------+-----+---------+----------------+5 rows in set (0.02 sec)

在我们进行操作的时候我们可以同时的进行删除和增加的操作，我们只需要在两个操作之间用‘，’进行分隔就好了。

——————————————————————————————————————————————————————————

 修改数据表添加约束：

添加外键约束：ALTER TABLE name ADD FOREIGN KEY(pid) REFERENCES provinces (id);

添加默认约束：ALTER TABLE name ALTER age SET DEFAULT 15;

删除默认约束：ALTER TABLE name ALTER age DROP DEFAULT;

数据表的修改操作：无非就是添加列，删除列，添加约束，删除约束。用的是ALTER，而INSERT是对数据表添加插入记录用的

1、添加主键约束：
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_column_name,...);
2、添加单个唯一约束：
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] [index_type] (index_column_name,...);
3、添加多个唯一约束：
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] [index_type] (index_column_name,...),ADD [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] [index_type] (index_column_name,...);

Ps1：CONSTRAINT 可加可不加，加该关键字后还可以选择添加主键别名；
Ps2：唯一约束可以有多个，但主键约束有且只能有一个。

root@127.0.0.1 t2>ALTER TABLE user2 ADD CONSTRAINT id PRIMARY KEY (id);Query OK, 0 rows affected (0.34 sec)Records: 0  Duplicates: 0  Waings: 0root@127.0.0.1 t2>SHOW COLUMNS from user2;+----------+----------------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| id| smallint(5) unsigned | NO| PRI | 0||| useame | varchar(20)  | NO| | NULL||| pid  | smallint(5) unsigned | NO| | NULL||+----------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)

————————————————————————————————————————

修改数据表—删除约束：

1、删除主键约束：
ALTER TABLE table_name DROP PRIMARY KEY;
2、删除唯一约束：
ALTER TABLE table_name DROP {INDEX} key_name;
3、删除外键约束：
ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;

Ps1：唯一约束的 index_name 可通过 SHOW INDEX FROM table_name\G; 查询。
Ps2：外键约束的名字 fk_symbol 可通过 SHOW CREATE TABLE table_name; 查询。
Ps3：INDEX：index是索引标识，和create table name 中的table是相同的标识。
Ps4：当字段id smallint unsigned auto_increment primary key时，不可以删除主键约束；必须先修改为 id smallint unsigned，再删除主键约束。

————————————————————————————————————————

修改列定义以及更名数据表：

修改列定义和更名数据表1、修改列定义（列类型/列位置）ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST AFTER col_name];2、修改列名称ALTER TABLE tbl_name CHANGE [COLUMN] col_name new_col_name column_definition [FIRSTAFTER col_name];3、数据表更名方法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] ...（这种方法可以给多个数据表更名）Ps1：修改数据类型（特别是大类型转到小类型）,需注意数据丢失的问题。Ps2：尽量不要修改数据表名和列名，以免影响后台等问题。

root@127.0.0.1 t2>SHOW COLUMNS from tb3;+----------+----------------------+------+-----+---------+----------------+| Field| Type | Null | Key | Default | Extra  |+----------+----------------------+------+-----+---------+----------------+| id| smallint(5) unsigned | NO| PRI | NULL| auto_increment || useame | varchar(30)  | NO| | NULL||+----------+----------------------+------+-----+---------+----------------+2 rows in set (0.02 sec)root@127.0.0.1 t2>ALTER TABLE tb3 MODIFY id SMALLINT UNSIGNED NOT NULL;Query OK, 0 rows affected (0.34 sec)Records: 0  Duplicates: 0  Waings: 0root@127.0.0.1 t2>SHOW COLUMNS from tb3;+----------+----------------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| id| smallint(5) unsigned | NO| PRI | NULL||| useame | varchar(30)  | NO| | NULL||+----------+----------------------+------+-----+---------+-------+2 rows in set (0.01 sec)

我们在修改定义的时候我们不需要带上主键的名称。