数据约束的分类：
————————————————————————————————————————————————————
外键约束的要求解析：
//在my文件中的这句话代表着搜索引擎，如果不是的就需要技能型更改。然后重启。my文件存在于我的电脑中的mysql文件夹里。
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
root@127.0.0.1 t2>CREATE TABLE province(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username 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,
`username` 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,
-> username VARCHAR(10) NOT NULL,
-> pid BIGINT ,
-> FOREIGN KEY(pid) REFERENCES province(id)
-> );
ERROR 1005 (HY000): Can't create table 't2.aname' (errno: 150)
root@127.0.0.1 t2>CREATE TABLE aname(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username 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: province
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.10 sec) ERROR:
No query specified root@127.0.0.1 t2>SHOW INDEXES FROM aname\G;
*************************** 1. row ***************************
Table: aname
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: aname
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.00 sec) ERROR:
No query specified
——————————————————————————————————————————————————————————————————
外键约束的参照操作：
root@127.0.0.1 t2>CREATE TABLE aname1(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username 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,
`username` 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(username) VALUES('A');
Query OK, 1 row affected (0.16 sec)
root@127.0.0.1 t2>INSERT province(username) VALUES('B');
Query OK, 1 row affected (0.14 sec)
root@127.0.0.1 t2>INSERT province(username) 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 exist
root@127.0.0.1 t2>SELECT * FROM province;
+----+----------+
| id | username |
+----+----------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+----------+
3 rows in set (0.00 sec)
root@127.0.0.1 t2>INSERT aname1(username,pid) VALUES('D',3);
Query OK, 1 row affected (0.16 sec)
root@127.0.0.1 t2>INSERT aname1(username,pid) VALUES('E',1);
Query OK, 1 row affected (0.07 sec)
root@127.0.0.1 t2>INSERT aname1(username,pid) VALUES('E',t);
ERROR 1054 (42S22): Unknown column 't' in 'field list'
root@127.0.0.1 t2>INSERT aname1(username,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(username,pid) VALUES('F',2);
Query OK, 1 row affected (0.05 sec)
root@127.0.0.1 t2>SELECT * FROM province;
+----+----------+
| id | username |
+----+----------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+----------+
3 rows in set (0.00 sec)
root@127.0.0.1 t2>SELECT * FROM aname1;
+----+----------+------+
| id | username | 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 | username |
+----+----------+
| 1 | A |
| 2 | B |
+----+----------+
2 rows in set (0.00 sec)
root@127.0.0.1 t2>SELECT * FROM aname1;
+----+----------+------+
| id | username | 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 Warnings: 0 root@127.0.0.1 t2>SHOW COLUMNS FROM aname1;
+----------+----------------------+------+-----+---------+----------------+
| 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)
root@127.0.0.1 t2>ALTER TABLE aname1 ADD ab TINYINT UNSIGNED NOT NULL AFTER username;
Query OK, 2 rows affected (0.30 sec)
Records: 2 Duplicates: 0 Warnings: 0 root@127.0.0.1 t2>SHOW COLUMNS FROM aname1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | 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 Warnings: 0 root@127.0.0.1 t2>SHOW COLUMNS from user2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | 0 | |
| username | 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 |
| username | 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 Warnings: 0 root@127.0.0.1 t2>SHOW COLUMNS from tb3;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| username | varchar(30) | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
我们在修改定义的时候我们不需要带上主键的名称。