数据约束的分类：
————————————————————————————————————————————————————
外键约束的要求解析：
//在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 provinceG; *************************** 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 anameG; *************************** 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_nameG; 查询。
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)
我们在修改定义的时候我们不需要带上主键的名称。