一、约束条件
步骤一：设置约束
1）在db2库里创建t2表时设置字段约束条件
1 mysql> create database db2; //建库 2 Query OK, 1 row affected (0.00 sec) 3 mysql> use db2; //切换库 4 Database changed 5 mysql> create table t2 ( //建表 6 -> class char(9), 7 -> name char(10) not null , 8 -> age tinyint not null default 19 , 9 -> likes set("a","b","c","d") default "a,b" 10 -> ); 11 Query OK, 0 rows affected (0.05 sec) 12 13 14 mysql> desc t2; //查看表结构 15 +-------+----------------------+------+-----+---------+-------+ 16 | Field | Type | Null | Key | Default | Extra | 17 +-------+----------------------+------+-----+---------+-------+ 18 | class | char(9) | YES | | NULL | | 19 | name | char(10) | NO | | NULL | | 20 | age | tinyint(4) | NO | | 19 | | 21 | likes | set('a','b','c','d') | YES | | a,b | | 22 +-------+----------------------+------+-----+---------+-------+ 23 4 rows in set (0.00 sec) 24 25 26 mysql> insert into t2 values (null,"bob",29,"c,d"); 27 Query OK, 1 row affected (0.05 sec) 28 mysql> insert into t2(class,name) values ("nsd1902","tom"); 29 Query OK, 1 row affected (0.05 sec) 30 mysql> insert into t2 values (null,null,null,null); 31 ERROR 1048 (23000): Column 'name' cannot be null //不允许赋null值 32 MariaDB [db2]> 33 MariaDB [db2]> select * from db2.t1; //查看记录 34 +---------+------+-----+-------+ 35 | class | name | age | likes | 36 +---------+------+-----+-------+ 37 | NULL | bob | 29 | c,d | 38 | nsd1902 | tom | 19 | a,b | 39 +---------+------+-----+-------+ 40 2 rows in set (0.00 sec)
二、修改表结构
步骤一：添加字段
1）在studb库下创建tea6表
1 Mysql> create database studb; 2 mysql> CREATE TABLE studb.tea6( 3 -> id int(4) , 4 -> name varchar(4) NOT NULL, 5 -> age int(2) NOT NULL 6 -> ); 7 Query OK, 0 rows affected (0.34 sec)
2）为tea6表添加一个address字段
1 mysql> DESC tea6; 2 +-------+------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-------+------------+------+-----+---------+-------+ 5 | id | int(4) | YES | | NULL | | 6 | name | varchar(4) | NO | | NULL | | 7 | age | int(2) | NO | | NULL | | 8 +-------+------------+------+-----+---------+-------+ 9 3 rows in set (0.00 sec)
添加address字段：
1 mysql> ALTER TABLE tea6 ADD address varchar(48); 2 Query OK, 0 rows affected (0.84 sec) 3 Records: 0 Duplicates: 0 Warnings: 0
添加后（默认作为最后一个字段）：
1 mysql> DESC tea6; 2 +---------+-------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +---------+-------------+------+-----+---------+-------+ 5 | id | int(4) | YES | | NULL | | 6 | name | varchar(4) | NO | | NULL | | 7 | age | int(2) | NO | | NULL | | 8 | address | varchar(48) | YES | | NULL | | 9 +---------+-------------+------+-----+---------+-------+ 10 4 rows in set (0.00 sec)
3）在tea6表的age列之后添加一个gender字段
添加操作：
1 mysql> ALTER TABLE tea6 ADD gender enum('boy','girl') AFTER age; 2 Query OK, 0 rows affected (0.59 sec) 3 Records: 0 Duplicates: 0 Warnings: 0
确认添加结果：
1 mysql> DESC tea6; 2 +---------+--------------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +---------+--------------------+------+-----+---------+-------+ 5 | id | int(4) |YES | | NULL | | 6 | name | varchar(4) | NO | | NULL | | 7 | age | int(2) | NO | | NULL | | 8 | gender | enum('boy','girl') | YES | | NULL | | 9 | address | varchar(48) | YES | | NULL | | 10 +---------+--------------------+------+-----+---------+-------+ 11 5 rows in set (0.00 sec)
步骤二：修改字段名和字段类型
将tea6表的gender字段改名为sex，并添加非空约束
修改操作：
1 mysql> ALTER TABLE tea6 CHANGE gender 2 -> sex enum('boy','girl') NOT NULL; 3 Query OK, 0 rows affected (0.08 sec) 4 Records: 0 Duplicates: 0 Warnings: 0
确认修改结果：
1 mysql> DESC tea6; 2 +---------+--------------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +---------+--------------------+------+-----+---------+-------+ 5 | id | int(4) | YES | | NULL | | 6 | name | varchar(4) | NO | | NULL | | 7 | age | int(2) | NO | | NULL | | 8 | sex | enum('boy','girl') | NO | | NULL | | 9 | address | varchar(48) | YES | | NULL | | 10 +---------+--------------------+------+-----+---------+-------+ 11 5 rows in set (0.00 sec)
步骤三：删除字段
删除tea6表中名为sex的字段：
1 mysql> ALTER TABLE tea6 DROP sex; //删除操作 2 Query OK, 0 rows affected (0.52 sec) 3 Records: 0 Duplicates: 0 Warnings: 0
4 mysql> DESC tea6; //确认删除结果 5 +---------+-------------+------+-----+---------+-------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +---------+-------------+------+-----+---------+-------+ 8 | id | int(4) | YES | | NULL | | 9 | name | varchar(4) | NO | | NULL | | 10 | age | int(2) | NO | | NULL | | 11 | address | varchar(48) | YES | | NULL | | 12 +---------+-------------+------+-----+---------+-------+ 13 4 rows in set (0.00 sec)
三、index 普通索引
步骤一：索引的创建与删除
1）创建表的时候指定INDEX索引字段
创建库home：
1 mysql> create database home; 2 Query OK, 1 row affected (0.00 sec)
允许有多个INDEX索引字段。比如，以下操作在home库中创建了tea4表，将其中的id、name作为索引字段：
1 mysql> USE home; 2 Database changed 3 mysql> CREATE TABLE tea4( 4 -> id char(6) NOT NULL, 5 -> name varchar(6) NOT NULL, 6 -> age int(3) NOT NULL, 7 -> gender ENUM('boy','girl') DEFAULT 'boy', 8 -> INDEX(id),INDEX(name) 9 -> ); 10 Query OK, 0 rows affected (0.59 sec)
查看新建tea4表的字段结构，可以发现两个非空索引字段的KEY标志为MUL：
1 mysql> DESC tea4; 2 +--------+--------------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +--------+--------------------+------+-----+---------+-------+ 5 | id | char(6) | NO | MUL | NULL | | 6 | name | varchar(6) | NO | MUL | NULL | | 7 | age | int(3) | NO | | NULL | | 8 | gender | enum('boy','girl') | YES | | boy | | 9 +--------+--------------------+------+-----+---------+-------+ 10 4 rows in set (0.00 sec)
2）删除现有表的某个INDEX索引字段
比如，删除tea4表中名称为named的INDEX索引字段：
1 mysql> drop INDEX name ON tea4; //删除name字段的索引 2 Query OK, 0 rows affected (0.18 sec) 3 Records: 0 Duplicates: 0 Warnings: 0 4 mysql> DESC tea4; //确认删除结果 5 +--------+--------------------+------+-----+---------+-------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +--------+--------------------+------+-----+---------+-------+ 8 | id | char(6) | NO | MUL | NULL | | 9 | name | varchar(6) | NO | | NULL | | 10 | age | int(3) | NO | | NULL | | 11 | gender | enum('boy','girl') | YES | | boy | | 12 +--------+--------------------+------+-----+---------+-------+ 13 4 rows in set (0.00 sec)
3）在已有的某个表中设置INDEX索引字段
比如，针对tea4表的age字段建立索引，名称为 nianling：
1 mysql> CREATE INDEX nianling ON tea4(age); //针对指定字段创建索引 2 Query OK, 0 rows affected (0.62 sec) 3 Records: 0 Duplicates: 0 Warnings: 0 4 mysql> DESC tea4; //确认创建结果 5 +--------+--------------------+------+-----+---------+-------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +--------+--------------------+------+-----+---------+-------+ 8 | id | char(6) | NO | MUL | NULL | | 9 | name | varchar(6) | NO | | NULL | | 10 | age | int(3) | NO | MUL | NULL | | 11 | gender | enum('boy','girl') | YES | | boy | | 12 +--------+--------------------+------+-----+---------+-------+ 13 4 rows in set (0.00 sec)
4）查看指定表的索引信息
使用SHOW INDEX 指令：
1 mysql> SHOW INDEX FROM tea4\G 2 *************************** 1. row *************************** 3 Table: tea4 4 Non_unique: 1 5 Key_name: id 6 Seq_in_index: 1 7 Column_name: id 8 Collation: A 9 Cardinality: 0 10 Sub_part: NULL 11 Packed: NULL 12 Null: 13 Index_type: BTREE //使用B树算法 14 Comment: 15 Index_comment: 16 *************************** 2. row *************************** 17 Table: tea4 18 Non_unique: 1 19 Key_name: nianling //索引名称 20 Seq_in_index: 1 21 Column_name: age //字段名称 22 Collation: A 23 Cardinality: 0 24 Sub_part: NULL 25 Packed: NULL 26 Null: 27 Index_type: BTREE 28 Comment: 29 Index_comment: 30 2 rows in set (0.00 sec) 31 Query OK, 0 rows affected (0.30 sec) 32 Mysql> 33 Query OK, 0 rows affected (0.47 sec) 34 Records: 0 Duplicates: 0 Warnings: 0
四、primary key 主键
步骤一：练习主键的使用
1）建表时设置PRIMARY KEY主键索引
1 Mysql> create database db2; 2 mysql> CREATE TABLE db2.biao01( 3 -> id int(4) PRIMARY KEY, //直接在字段定义时约束 4 -> name varchar(8) 5 -> ); 6 Query OK, 0 rows affected (0.19 sec)
或者：
1 mysql> CREATE TABLE db2.biao02( 2 -> id int(4), 3 -> name varchar(8), 4 -> PRIMARY KEY(id) //所有字段定义完，最后指定 5 -> ); 6 Query OK, 0 rows affected (0.17 sec)
在建表的时候，如果主键字段为int类型，还可以为其设置AUTO_INCREMENT自增属性，这样当添加新的表记录时，此字段的值会自动从1开始逐个增加，无需手动指定。比如，新建一个tea6表，将id列作为自增的主键字段：
1 mysql> CREATE TABLE db2.tea6( 2 -> id int(4) AUTO_INCREMENT, 3 -> name varchar(4) NOT NULL, 4 -> age int(2) NOT NULL, 5 -> PRIMARY KEY(id) 6 -> ); 7 Query OK, 0 rows affected (0.29 sec)
2）删除现有表的PRIMARY KEY主键索引
如果要移除某个表的PRIMARY KEY约束，需要通过ALTER TABLE指令修改。比如，以下操作将清除biao01表的主键索引。
清除前（主键为id）：
1 mysql> DESC db2.biao01; 2 +-------+------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-------+------------+------+-----+---------+-------+ 5 | id | int(4) | NO | PRI | NULL | | 6 | name | varchar(8) | YES | | NULL | | 7 +-------+------------+------+-----+---------+-------+ 8 2 rows in set (0.00 sec)
清除操作：
1 mysql> ALTER TABLE db2.biao01 DROP PRIMARY KEY; 2 Query OK, 0 rows affected (0.49 sec) 3 Records: 0 Duplicates: 0 Warnings: 0
清除后（无主键）：
1 mysql> DESC db2.biao01; 2 +-------+------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-------+------------+------+-----+---------+-------+ 5 | id | int(4) | NO | | NULL | | 6 | name | varchar(8) | YES | | NULL | | 7 +-------+------------+------+-----+---------+-------+ 8 2 rows in set (0.00 sec)
当尝试删除tea6表的主键时，会出现异常：
1 mysql> ALTER TABLE tea6 DROP PRIMARY KEY; 2 ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
这是因为tea6表的主键字段id具有AUTO_INCREMNET自增属性，提示这种字段必须作为主键存在，因此若要清除此主键必须先清除自增属性——修改id列的字段定义：
1 mysql> ALTER TABLE tea6 MODIFY id int(4) NOT NULL; 2 Query OK, 0 rows affected (0.75 sec) 3 Records: 0 Duplicates: 0 Warnings: 0
然后再清除主键属性就OK了：
1 mysql> ALTER TABLE tea6 DROP PRIMARY KEY; //清除主键 2 Query OK, 0 rows affected (0.39 sec) 3 Records: 0 Duplicates: 0 Warnings: 0 4 mysql> desc tea6; //确认清除结果 5 +-------+------------+------+-----+---------+-------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +-------+------------+------+-----+---------+-------+ 8 | id | int(4) | NO | | NULL | | 9 | name | varchar(4) | NO | | NULL | | 10 | age | int(2) | NO | | NULL | | 11 +-------+------------+------+-----+---------+-------+ 12 3 rows in set (0.01 sec)
3）为现有表添加PRIMARY KEY主键索引
重新为tea6表指定主键字段，仍然使用id列：
1 mysql> ALTER TABLE tea6 ADD PRIMARY KEY(id); //设置主键字段 2 Query OK, 0 rows affected (0.35 sec) 3 Records: 0 Duplicates: 0 Warnings: 0 4 mysql> DESC tea6; //确认设置结果 5 +-------+------------+------+-----+---------+-------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +-------+------------+------+-----+---------+-------+ 8 | id | int(4) | NO | PRI | NULL | | 9 | name | varchar(4) | NO | | NULL | | 10 | age | int(2) | NO | | NULL | | 11 +-------+------------+------+-----+---------+-------+ 12 3 rows in set (0.00 sec)
4）建表时创建复合主键
1 mysql> create table db2.t6( 2 -> class char(7), 3 -> name char(15), 4 -> pay enum("yes","no") default "no", 5 -> primary key(class,name,pay) //指定多个字段一起做主键 6 -> ); 7 Query OK, 0 rows affected (0.04 sec) 8 mysql> desc db2.t6; 9 +-------+------------------+------+-----+---------+-------+ 10 | Field | Type | Null | Key | Default | Extra | 11 +-------+------------------+------+-----+---------+-------+ 12 | class | char(7) | NO | PRI | | | 13 | name | char(15) | NO | PRI | | | 14 | pay | enum('yes','no') | NO | PRI | no | | 15 +-------+------------------+------+-----+---------+-------+ 16 3 rows in set (0.01 sec)
五、foreign key 外键
步骤一：创建外键
1）创建yg表，用来记录员工工号、姓名，其中yg_id列作为主键，并设置自增属性
1 mysql> CREATE TABLE yg( 2 -> yg_id int primary key AUTO_INCREMENT, 3 -> name char(16) 4 -> )engine=innodb; 5 Query OK, 0 rows affected (0.15 sec)
2）创建gz表，用来记录员工的工资信息
其中gz_id需要参考员工工号，即gz表的gz_id字段设为外键，将yg表的yg_id字段作为参考键：
1 mysql> CREATE TABLE gz( 2 -> gz_id int, 3 -> name char(16) , 4 -> gz float(7,2) , 5 -> FOREIGN KEY(gz_id) REFERENCES yg(yg_id) //创建外键 6 -> ON UPDATE CASCADE ON DELETE CASCADE //同步更新、同步删除 7 -> )engine=innodb; 8 Query OK, 0 rows affected (0.23 sec)
3）为yg表添加2条员工信息记录
因yg_id有AUTO_INCREMENT属性，会自动填充，所以只要为name列赋值就可以了。
插入表记录可使用INSERT指令
1 mysql> INSERT INTO yg(name) VALUES('Jerry'),('Tom'); 2 Query OK, 2 rows affected (0.16 sec) 3 Records: 2 Duplicates: 0 Warnings: 0
确认yg表的数据记录：
1 mysql> SELECT * FROM yg; 2 +-------+-------+ 3 | yg_id | name | 4 +-------+-------+ 5 | 1 | Jerry | 6 | 2 | Tom | 7 +-------+-------+ 8 2 rows in set (0.00 sec)
4）为gz表添加2条工资信息记录
同上，数据参考图-2，插入相应的工资记录（gz_id字段未指定默认值，也未设置自增属性，所以需要手动赋值）：
1 mysql> INSERT INTO gz(gz_id,name,gz) 2 -> VALUES(1,'Jerry',12000),(2,'Tom',8000) 3 -> ; 4 Query OK, 2 rows affected (0.06 sec) 5 Records: 2 Duplicates: 0 Warnings: 0
确认gz表的数据记录：
1 mysql> SELECT * FROM gz; 2 +-------+-------+----------+ 3 | gz_id | name | gz | 4 +-------+-------+----------+ 5 | 1 | Jerry | 12000.00 | 6 | 2 | Tom | 8000.00 | 7 +-------+-------+----------+ 8 2 rows in set (0.05 sec)
5）验证表记录的UPDATE更新联动
将yg表中Jerry用户的yg_id修改为1234：
1 mysql> update yg SET yg_id=1234 WHERE name='Jerry'; 2 Query OK, 1 row affected (0.05 sec) 3 Rows matched: 1 Changed: 1 Warnings: 0
确认修改结果：
1 mysql> SELECT * FROM yg; 2 +-------+-------+ 3 | yg_id | name | 4 +-------+-------+ 5 | 2 | Tom | 6 | 1234 | Jerry | 7 +-------+-------+ 8 2 rows in set (0.00 sec)
同时也会发现，gz表中Jerry用户的gz_id也跟着变了：
1 mysql> SELECT * FROM gz; 2 +-------+-------+----------+ 3 | gz_id | name | gz | 4 +-------+-------+----------+ 5 | 1234 | Jerry | 12000.00 | 6 | 2 | Tom | 8000.00 | 7 +-------+-------+----------+ 8 2 rows in set (0.00 sec)
6）验证表记录的DELETE删除联动
删除yg表中用户Jerry的记录：
1 mysql> DELETE FROM yg WHERE name='Jerry'; 2 Query OK, 1 row affected (0.05 sec)
确认删除结果：
1 mysql> SELECT * FROM yg; 2 +-------+------+ 3 | yg_id | name | 4 +-------+------+ 5 | 2 | Tom | 6 +-------+------+ 7 1 row in set (0.00 sec)
查看gz表中的变化（Jerry的记录也没了）：
1 mysql> SELECT * FROM gz; 2 +-------+------+---------+ 3 | gz_id | name | gz | 4 +-------+------+---------+ 5 | 2 | Tom | 8000.00 | 6 +-------+------+---------+ 7 1 row in set (0.00 sec)
7）删除指定表的外键约束
先通过SHOW指令获取表格的外键约束名称：
1 mysql> SHOW CREATE TABLE gz\G 2 *************************** 1. row *************************** 3 Table: gz 4 Create Table: CREATE TABLE `gz` ( 5 `gz_id` int(4) NOT NULL, 6 `name` char(16) NOT NULL, 7 `gz` float(7,2) NOT NULL DEFAULT '0.00', 8 KEY `name` (`name`), 9 KEY `gz_id` (`gz_id`), 10 CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE 11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 12 1 row in set (0.00 sec)
其中gz_ibfk_1即删除外键约束时要用到的名称。
删除操作：
1 mysql> ALTER TABLE gz DROP FOREIGN KEY gz_ibfk_1; 2 Query OK, 0 rows affected (0.01 sec) 3 Records: 0 Duplicates: 0 Warnings: 0
确认删除结果：
1 mysql> SHOW CREATE TABLE gz\G 2 *************************** 1. row *************************** 3 Table: gz 4 Create Table: CREATE TABLE `gz` ( 5 `gz_id` int(4) NOT NULL, 6 `name` char(16) NOT NULL, 7 `gz` float(7,2) NOT NULL DEFAULT '0.00', 8 KEY `name` (`name`), 9 KEY `gz_id` (`gz_id`) 10 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 11 1 row in set (0.00 sec)
浙公网安备 33010602011771号