MySQL表数据补充
目录
内容概述
1.表数据补充
-1.1 修改表数据
-1.2 删除表数据
2.数据表约束
-2.1 主键索引约束
-2.2 唯一索引约束
-2.3 检查索引
-2.4 外键索引
3.添加字段
-3.1 在开头位置添加字段
-3.2 在中间位置添加字段
-3.3 在末尾位置添加字段
4.字段补充内容
内容详细
1.表数据补充
1.1 修改表数据
在数据表中存储的数据时常会有所改变，比如年龄变化，体重变化等。随着一些事务的推移从而需要修改表数据，这时候我们要用到MySQL UPDATE语句
格式：
UPDATE <表名> SET [修改的内容] [条件];
案例：
现在有一张表
mysql> select * from class01;
+------+-------+------+------+
| id | name | age | sex |
+------+-------+------+------+
| 1 | uzi | 23 | 1 |
| 2 | ak47 | 47 | 1 |
| 3 | m4 | 20 | 1 |
| 4 | knife | 1 | 2 |
+------+-------+------+------+
4 rows in set (0.01 sec)
修改单条数据（将数据表中的uzi的age改为18）
mysql> update class01 set age = 18 where name = 'uzi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from class01;
+------+-------+------+------+
| id | name | age | sex |
+------+-------+------+------+
| 1 | uzi | 18 | 1 |
| 2 | ak47 | 47 | 1 |
| 3 | m4 | 20 | 1 |
| 4 | knife | 1 | 2 |
+------+-------+------+------+
4 rows in set (0.00 sec)
案例：修改多条数据（将ak47 age改为49，同时将sex改为2）
mysql> update class01 set age = 49,sex = 2 where name = 'ak47';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from class01;
+------+-------+------+------+
| id | name | age | sex |
+------+-------+------+------+
| 1 | uzi | 18 | 1 |
| 2 | ak47 | 49 | 2 |
| 3 | m4 | 20 | 1 |
| 4 | knife | 1 | 2 |
+------+-------+------+------+
4 rows in set (0.00 sec)
1.2 删除表数据
当数据表中存在错误或没有任何价值的数据时，我们可以通过sql语句将这部分数据删除。
# 在生产环境中我们最好不要这么做
格式：
DELETE FROM <表名> [条件];
案例：
# 删除所有数据（慎用！！！）
mysql> DELETE FROM t1;
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
# 删除某一部分数据（比较常用，但还是要谨慎）
mysql> delete from class01 where id = 2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from class01;
+------+-------+------+------+
| id | name | age | sex |
+------+-------+------+------+
| 1 | uzi | 18 | 1 |
| 3 | m4 | 20 | 1 |
| 4 | knife | 1 | 2 |
+------+-------+------+------+
3 rows in set (0.00 sec)
# 清空数据表
mysql> DELETE FROM test01;
Query OK, 0 rows affected (0.00 sec)
mysql> TRUNCATE TABLE test01;
Query OK, 0 rows affected (0.00 sec)
这里解释下 DELETE 和 TRUNCATE 之间的区别：
DELETE 删除的是数据，不删除索引， TRUNCATE 不仅删除数据而且删除索引
2.数据表约束(索引约束和数据类型约束）
2.1 主键索引约束
数据表的约束从字段名字上来看就是为了控制数据而产生的
主键索引约束
主键索引约束就是在数据表中（一般是id字段）选择一个字段充当索引的角色。强烈建议或生产环境要求表中至少要有一个主键索引约束。
主键索引是一个字段的类型，不能够单独存在。
我们来创建一个具有主键索引的数据表：
create table if not exists test02(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test02\G
*************************** 1. row ***************************
Table: test02
Create Table: CREATE TABLE `test02` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
desc test02;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
那么被标记成为主键的字段能插入重复的数据吗？
显然是不能的
# 一旦字段被标记成立主键，则其值是无法重复的。除此之外，主键算是MySQL中最快的索引之一。具体的底层算法，我们将在后面的存储引擎原理中详细介绍。
# 自增长
我们在实际使用中经常不知道当天数据的主键索引的编号属于哪一个，这个时候我们就需要一个自动为我们填充主键编号的功能，这个就是自增长
案例：
自动填充主键
先创建一个具有主键索引的数据表
create table if not exists test03(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test03\G
*************************** 1. row ***************************
Table: test03
Create Table: CREATE TABLE `test03` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> desc test03;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
我们进行测试
mysql> insert into test03 (name) values ('杰洛特'),('希里雅');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test03;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 杰洛特 |
| 2 | 希里雅 |
+----+-----------+
2 rows in set (0.01 sec)
我们并没有插入id，id自动增加了
除此之外，我们还可以设置自动增加的起始值
我们依然是创建一个新的表
mysql> create table if not exists gun(id int primary key auto_increment,name varchar(20)) engine=innodb auto_increment=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> desc gun;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
然后我们进行测试：
mysql> insert into gun (name) values ('uzi'),('m4a1'),('ak47');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from gun;
+------+------+
| id | name |
+------+------+
| 1000 | uzi |
| 1001 | m4a1 |
| 1002 | ak47 |
+------+------+
3 rows in set (0.00 sec)
可以看出来自定义初始值成功了。
# 添加主键
上面的案例是我们在创建表的时候就添加了主键，那有的已经创建好的表该怎么为其添加主键呢？
格式：
ALTER TABLE <数据表> ADD PRIMARY KEY(字段名称);
案例：
创建一个不具有主键索引的数据表
mysql> create table if not exists test04(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> desc test04;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
接着我们为数据表添加主键
mysql> alter table test04 add primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test04;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 删除主键
当数据表不需要主键时，我们可以尝试将其删除 # 一般情况下我们是不删除主键的......
格式：
ALTER TABLE <数据表名> DROP PRIMARY KEY;
案例：
我们就用刚才的表
mysql> desc test04;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table test04 drop primary key;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test04;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2.2 唯一索引约束
唯一索引约束与主键索引类似，也是要求不允许重复，但是主键索引一般作用于id，唯一索引可以作用于所有的字段。
格式：
create table if not exists test05(id int primary key,name varchar(20) unique key) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> desc test05;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 同主键索引一样，字段一旦加上了唯一索引，则不能重复
2.3 检查索引
检查索引，顾名思义就是通过设置范围，来管控数据
案例：
创建一张新数据表
create table if not exists test06(id int primary key auto_increment,name varchar(20),code tinyint(2) check(code > 100 and code < 200)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test06\G
*************************** 1. row ***************************
Table: test06
Create Table: CREATE TABLE `test06` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`code` tinyint(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
测试：
mysql> insert into test06 (name,code) values ('jerryli',10);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test06;
+----+---------+------+
| id | name | code |
+----+---------+------+
| 1 | jerryli | 10 |
+----+---------+------+
1 row in set (0.00 sec)
2.4 外键索引
外键索引是一种依赖别的表的数据的一种索引
我们直接上案例就完事了：
我们需要先创建两个新的表
mysql> create table city(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> create table city02(id int primary key auto_increment,name varchar(20), fid int,foreign key(fid) references city(id));
Query OK, 0 rows affected (0.00 sec)
mysql> desc city;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc city02;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| fid | int(11) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
我们先为city2插入数据试试
mysql> insert into city02 (name,fid) values ('宝山区',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`xiaohu`.`city02`, CONSTRAINT `city02_ibfk_1` FOREIGN KEY (`fid`) REFERENCES `city` (`id`))
呃，这个报错大概意思是外键约束失败，无法添加或更新子行
所以我们要先为表city插入数据
mysql> insert into city (name,id) values ('上海市',1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from city;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 上海市 |
+----+-----------+
1 row in set (0.00 sec)
再次为表city02插入数据
mysql> insert into city02 (name,fid) values ('宝山区',1);
Query OK, 1 row affected (0.00 sec)
这次就成功了
mysql> select * from city02;
+----+-----------+------+
| id | name | fid |
+----+-----------+------+
| 2 | 宝山区 | 1 |
+----+-----------+------+
1 row in set (0.00 sec)
# 总结：city02变依赖于city表数据变化，当city表中没有相关的数据，则不能够添加数据到city02表中。
3.添加字段
3.1 在开头位置添加字段
# 在开头位置添加字段
MySQL 默认在表的最后位置添加新字段，如果希望在开头位置（第一列的前面）添加新字段，那么可以使用 FIRST 关键字。
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;
案例：
alter table test01 add name varchar(20) first;
3.2 在中间位置添加字段
# 在中间位置添加字段
MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 AFTER 关键字。
alter table <表名> add <新字段名> <数据类型> [约束条件] after <已经存在的字段名>;
after 的作用是将新字段添加到某个已有字段后面
alter table test01 sex varchar (20) after id;
3.3 在末尾位置添加字段
# 在末尾位置添加字段
一个完整的字段包括字段名、数据类型和约束条件。
ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
对语法格式的说明如下：
● <表名> 为数据表的名字
● <新字段名> 为所要添加的字段的名字
● <数据类型> 为所要添加的字段能存储数据的数据类型
● [约束条件] 是可选的，用来对添加的字段进行约束
使用alter table 语句添加一个 INT类型的字段age,SQL语句和运行结果如下：
alter table test01 add addr varchar(20);
4.字段补充内容
1.是否允许为空
是否为空的意思就是设置是否允许字段为空。
格式：
NOT NULL
案例：
老规矩创建新表
mysql> create table if not exists test07(id int primary key auto_increment,name varchar(20) not null) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> create table if not exists test08(id int primary key auto_inncrement,name varchar(20)) engine=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test08 (id) values (10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test07 (id) values (10);
# 报错......
# ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into test07 (name) values ('jreeyli');
Query OK, 1 row affected (0.00 sec)
mysql> desc test07;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc test08;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
2.默认值
就是给字段设置一个默认值，当字段没有添加任何值的时候，使用默认值进行填充。
案例：
mysql> create table if not exists test09(id int primary key auto_increment,name varchar(20) default 'jerryli') engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> desc test09;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | jerryli | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
插入数据看看
mysql> select * from test09;
+----+---------+
| id | name |
+----+---------+
| 10 | jerryli |
+----+---------+
1 row in set (0.00 sec)
mysql> insert into test09 (id) values (5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test09;
+----+---------+
| id | name |
+----+---------+
| 5 | jerryli |
| 10 | jerryli |
+----+---------+
2 rows in set (0.00 sec)
3.字段注释
字段注释就是给字段一个注释，它有利于后期维护的时候快速理解字段的含义 # 字段注释在生产环境下是必须要求加的，并且不只是MySQL数据库，写代码也是一样，加上合理的注释也方便自己修改和别人的阅读。
# 字段注释
案例：
mysql> create table if not exists test10(id int primary key auto_increment comment '测试字段',name varchar(20) default 'jerryli' comment '名字');
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test10\G
*************************** 1. row ***************************
Table: test10
Create Table: CREATE TABLE `test10` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '测试字段',
`name` varchar(20) DEFAULT 'jerryli' COMMENT '名字',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
# 表注释
案例：
mysql> create table if not exists test12(id int primary key auto_increment comment '主键字段',name varchar(20) default 'jerryli' comment '名字') engine=innodb comment '表注释';
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test12\G
*************************** 1. row ***************************
Table: test12
Create Table: CREATE TABLE `test12` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键字段',
`name` varchar(20) DEFAULT 'jerryli' COMMENT '名字',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表注释'
1 row in set (0.00 sec)
