MySQL基础学习（二）

MySQL基础（二）

1 外键约束的要求解析

外键约束FOREIGN KEYp

1. 保持数据一致性、完整性

2. 实现一对一或一对多的关系

外键约束的要求

1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表

2. 数据表的存储引擎只能为InnoDB

3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同

4. 外键列和参照列必须创建索引。如果参照列不存在索引的话，MySQL将自动创建索引。（外键列不会）

   以上四条要求必须全部满足。

编辑数据表的默认存储引擎：修改MySQL配置文件（my.ini)

default - storage - engine = INNODB

案例分析

mysql>USE test;

mysql>CREATE TABLE provinces(

​ id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

​ pname VARCHAR(20) NOT NULL);

查看数据表默认存储引擎

SHOW CREATE TABLE provinces;

mysql>CREATE TABLE users(

​ id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

​ username VARCHAR(10) NOT NULL,

​ pid BIGINT,

​ FOREIGN KEY (pid) REFERENCES provinces (id));

错误，不能创建数据表users，因为pid数据类型为BIGINT，与参照列不一致

修改数据表user中的pid数据类型为SMALLINT UNSIGNED

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));

查看参照列索引

SHOE INDEXES FROM provinces\G;

参看外键列的索引

SHOW CREATE TABLE users;

2 外键约束的参照操作

1. CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行

2. SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。

3. RESTRICT：拒绝对父表的删除或更新操作

4. NO ACTION：标准SQL的关键字。在MySQL中与RESTICT相同

   案例分析

   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);

   mysql>INSERT provinces(pname) VALUES('A');

   mysql>INSERT provinces(pname) VALUES('B');

   mysql>INSERT provinces(pname) VALUES('C');

   mysql>SELECT * FROM provinces;

   mysql>INSERT users1(username,pid) VALUES('Tom',1);

   mysql>INSERT users1(username,pid) VALUES('Tom',7); %错误，不可行

   mysql>INSERT users1(username,pid) VALUES('John',3);

   mysql>INSERT users1(username,pid) VALUES('Tom',3);

   mysql>DELETE FROM provinces WHERE id = 3;

   mysql>SELECT * FROM provinces;

   mysql>SELECT * FROM users1;

   注：存储引擎为InnoDB是才使用外键约束，其他引擎时更多使用的是逻辑约束（根据表结构）

3 表级约束和列级约束

1. 表级约束

   对多个数据列建立的约束

2. 列级约束

   对一个数据列建立的约束

   列级约束既可以在列定义时声明，也可以在列定义后声明，表级约束只能在列定义后声明。

4 修改数据表

4.1 添加列

1. 添加单列

   ALTER TABLE tbl_name ADD [COLUMNS] col_name column_definition [FIRST | AFTER col_name]

   FIRST：在所有列的最前面

   AFTER col_name ：在指定列的下面

   案例分析

   添加age,如果没有指定FIRST和AFTER col_name，则添加的列在所有列的后面

   mysql>ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

   添加password列，非空，在username 后面

   mysql>ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;

   添加true列，FIRST在所有列的最前面

   mysql>ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;

2. 添加多列

   ALTER TABLE tbl_name ADD [COLUMNS] (col_name col_definition,...)

   不能指定位置，只能在所有列的最下面

4.2 删除列

ALTER TABLE tbl_name DROP [COLUMNS] col_name

案例分析

msyql>ALTER TABLE users1 DROP truename;

msyql>ALTER TABLE users1 DROP password;

msyql>ALTER TABLE users1 DROP age;

4.3 添加-删除主键约束

4.3.1 添加主键约束（只能有一个）

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name,...)

案例分析

创建一张无用表

mysql>CREATE TABLE users2(

username VARCHAR(20) NOT NULL,

pid SMALLINT UNSIGNED);

mysql>SHOW CREATE TABLE users2; %没有主键

添加id列

mysql>ALTER TABLE users2 ADD id SMALLINT UNSIGNED;

添加主键

mysql>ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);

mysql>SHOW COLUMNS FROM users2;

4.3.2 删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY

案例分析

mysql>ALTER TABLE users2 DROP PRIMARY KEY;

mysql>SHOW COLUMNS FROM users2;

4.4 添加-删除唯一约束

4.4.1 添加唯一约束（可以有多个）

ALTER TABLE users2 ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] index_type (index_col_name,...)

案例分析

mysql>ALTER TABLE users2 ADD UNIQUE (username);

mysql>SHOW CREATE TABLE users2;

4.4.2 删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

案例分析

查看约束表名称

mysql>SHOW INDEXES FROM users2;

mysql>ALTER TABLE users2 DROP INDEX username;

mysql>SHOW INDEXES FROM users2;

4.5 添加-删除外键约束

4.5.1 添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] FOREIGN KEY

[index_name] (index_col_name,...) reference_definition

案例分析

mysql>ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);

mysql>SHOW CREATE TABLE users2;

4.5.2 删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

fk_symbol：外键约束名称

案例分析

查看外键约束名称

mysql>SHOW CREATE TABLE users2;

mysql>ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1

删除索引

mysql>ALTER TABLE users2 DROP INDEX pid;

4.6 添加-删除默认约束

4.6.1 添加默认约束

ALTER TABLE tbl_name ALTER [COLUMNS] col_name {SET DEFAULT literal}

案例分析

mysql>ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;

mysql>ALTER TABLE users2 ALTER age SET DEFAULT 15;

4.6.2 删除默认约束

ALTER TABLE tbl_name ALTER [COLUMNS] col_name {DROP DEFAULT}

案例分析

mysql>ALTER TABLE users2 ALTER age DROP DEFAULT;

5 修改列定义和更名数据表

5.1 修改列定义

1. 修改列定义

   ALTER TABLE tbl_name MODIEY [COLUMNS] col_name column_definition [FIRST|AFTER col_name]

   案例分析

   mysql>ALTER TABLE users2 MODIEY id SMALLINT UNSIGNED NOT NULL FIRST;

   mysql>SHOW COLUMNS FROM users2;

   注：大类型修改为小类型，可能会造成数据丢失

2. 修改列名称

   ALTER TABLE tbl_name CHANGE [COLUMNS] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

   案例分析

   mysql>ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

   mysql>SHOW COLUMNS FROM users2;

5.1 数据表更名

1. 方法1

   ALTER TABLE tbl_name RENAME [TO|AS] new_tal_name

   案例分析

   mysql>ALTER TABLE users2 TO users3;

   mysql>SHOW TABLES;

2. 方法2

   RENAME TABLE tbl_name TO new_tbl_name [tbl_name1 TO new_tbl_name1,...]

   可更改多张数据表

   注：不建议随意更改

   案例分析

   mysql>RENAME TABLE users3 TO users2;

   mysql>SHOW TABLES;

小结

约束

1. 按功能分：NOT NULL, PRIMAEY KEY, UNIQUE KEY, DEFAULT, FOREIGN KEY

2. 按数据列的数目划分：表级约束， 列级约束

修改数据表

1. 针对字段的操作：添加-删除字段，修改列定义

修改名称

1. 针对约束的操作：添加-删除各种约束

2. 针对数据表的操作：数据表更名（两种方式）