删除数据方法对比
delete删除(DML)

delete删除数据的优缺点:

  • 优点:表中的数据被删除了,但是这个数据在硬盘上的真实存储空间是不会被释放的,支持回滚数据
  • 缺点:删除效率比较低

例:

有一张表t_user

mysql> select * from t_user;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
+-------+-------+
3 rows in set (0.00 sec)

对其执行这条命令

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

然后使用delete删除

mysql> delete from t_user;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from t_user;
Empty set (0.00 sec)

使用回滚命令

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

再查询试试

mysql> select * from t_user;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
+-------+-------+
3 rows in set (0.00 sec)

发现又恢复了

truncate删除(DDL)

truncate删除数据的优缺点:

  • 优点:快速
  • 缺点:不支持回滚

例:

删除前

mysql> select * from t_user;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
+-------+-------+
3 rows in set (0.00 sec)

使用truncate删除

mysql> truncate table t_user;
Query OK, 0 rows affected (0.02 sec)

使用rollback试试

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

再查看试试

mysql> select * from t_user;
Empty set (0.00 sec)

无法回滚

注:使用truncate前,必须仔细咨询客户是否真的需要删除,并警告删除后不可恢复

对表结构的增删改

表结构的修改就是添加一个字段,删除一个字段,或者修改一个字段,但我们在实际开发当中,却很少对表的结构进行修改,原因是因为:

  1. 在实际的开发当中,需求一旦确定下来,表的设计就会设计好,很少对表的结构进行修改,因为开发进行中的时候,修改表的结构,成本是比较高的,对表的结构进行修改,对应的Java代码就需要进行大量的修改,成本比较高,这个责任应该由设计人员来承担
  2. 由于修改表结构的操作很少,所以我们不需要掌握,假如真的有一天要修改表结构,使用工具修改即可

约束

概述

约束(constraint)的作用就是为了保证表中的数据有效,在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中的数据的完整性,有效性

约束种类
  • 非空约束:not null
  • 唯一性约束:unique
  • 主键约束:primary key(简称PK)
  • 外键约束:foregin key(简称FK)
  • 检查约束:check(mysql不支持,oracle支持)
非空约束

非空约束not null约束的字段不能为NULL

例:

创建一个带有非空约束的表

mysql> create table team_user(id int,name varchar(255) not null);
Query OK, 0 rows affected (0.02 sec)

查看一下

mysql> desc team_user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | YES  |     | NULL    |       |
| name  | varchar(255) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

插入一条完整数据试试

mysql> insert into team_user(id,name) values(1,'YQHP-YuKi'),(2,'Oracle');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from team_user;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | YQHP-YuKi |
|    2 | Oracle    |
+------+-----------+
2 rows in set (0.00 sec)

当我尝试插入不完整的数据时,就出现了报错

mysql> insert into team_user(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
唯一性约束

唯一性约束unique约束的字段不能重复,但是可以为NULL

例:

创建一个表

mysql> create table team_table(id int,name varchar(255) unique,email varchar(255));
Query OK, 0 rows affected (0.02 sec)
mysql> desc team_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | YES  |     | NULL    |       |
| name  | varchar(255) | YES  | UNI | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

插入正常数据

mysql> insert into team_table(id,name,email) values(1,'YQHP-YuKi','123@.com'),(2,'Google','456@.com'),(3,'Twiter','789@.com');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from team_table;
+------+-----------+----------+
| id   | name      | email    |
+------+-----------+----------+
|    1 | YQHP-YuKi | 123@.com |
|    2 | Google    | 456@.com |
|    3 | Twiter    | 789@.com |
+------+-----------+----------+
3 rows in set (0.00 sec)

现在我插入一条名字一样的人

mysql> insert into team_table(id,name,email) values(4,'Google','000@.com');
ERROR 1062 (23000): Duplicate entry 'Google' for key 'team_table.name'

就出现报错了

当为NULL的情况

mysql> insert into team_table(id) values(4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into team_table(id) values(5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from team_table;
+------+-----------+----------+
| id   | name      | email    |
+------+-----------+----------+
|    1 | YQHP-YuKi | 123@.com |
|    2 | Google    | 456@.com |
|    3 | Twiter    | 789@.com |
|    4 | NULL      | NULL     |
|    5 | NULL      | NULL     |
+------+-----------+----------+
5 rows in set (0.00 sec)

却不出现报错,是因为NULL为空

联合具有唯一性

例如我这样要求name和email两个字段联合起来具有唯一性

也许会有人这么创建,但其实是错误的

mysql> create table team_table(id int,name varchar(255) unique,email varchar(255) unique);
Query OK, 0 rows affected (0.02 sec)

是因为这样创建的表是name具有唯一性,email具有唯一性,各自具有唯一性

例如以下创建方式创建表虽然符合我的要求,但却会创建失败

mysql> insert into team_table(id,name,email) values(1,'Google','g.cn');
Query OK, 1 row affected (0.00 sec)

mysql> insert into team_table(id,name,email) values(1,'Google','google.com');
ERROR 1062 (23000): Duplicate entry 'Google' for key 'team_table.name'

只有这样创建才符合要求

mysql> create table team_table(id int,name varchar(255),email varchar(255) ,unique(name,email));
Query OK, 0 rows affected (0.01 sec)

再插入试试

mysql> insert into team_table(id,name,email) values(1,'Google','g.cn');
Query OK, 1 row affected (0.00 sec)

mysql> insert into team_table(id,name,email) values(1,'Google','google.com');
Query OK, 1 row affected (0.00 sec)
mysql> select * from team_table;
+------+--------+------------+
| id   | name   | email      |
+------+--------+------------+
|    1 | Google | g.cn       |
|    1 | Google | google.com |
+------+--------+------------+
2 rows in set (0.00 sec)

但是这样却是仍然不可以的

mysql> insert into team_table(id,name,email) values(1,'Google','google.com');
ERROR 1062 (23000): Duplicate entry 'Google-google.com' for key 'team_table.name'

把约束添加到列后面的约束称之为列级约束,而把约束添加到表的后面,联合起来约束的被称之为表级约束

unique与not null联合使用

其实unique可以与not null一起联合使用

例:

mysql> create table team_table(id int, name varchar(255) not null unique);
Query OK, 0 rows affected (0.01 sec)

但我这时查看表的结构时,却发现name边为了primary key

mysql> desc team_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | YES  |     | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

注:在mysql中,如果一个字段同时被not null和unique约束的话,该字段自动变成主键约束(注意:在oracle中不一样)

正常插入

mysql> insert into team_table(id,name) values(1,'ins');
Query OK, 1 row affected (0.00 sec)

重复报错

mysql> insert into team_table(id,name) values(2,'ins');
ERROR 1062 (23000): Duplicate entry 'ins' for key 'team_table.name'

不能为NULL报错

mysql> insert into team_table(id) values(2);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
主键约束(primary key)
  • 主键字段:该字段上添加了主键约束,这样的字段就叫做主键约束
  • 主键值:主键字段中的每一个值都叫做主键值,主键值是每一行记录的唯一标识
  • 任何一张表都应该有主键,没有主键,表无效,主键的特征:not null与unique

创建一个主键约束的表

mysql> create table team_table(id int primary key,name varchar(255));
Query OK, 0 rows affected (0.01 sec)

插入正常数据

mysql> insert into team_table(id,name) values(1,'YQHP-YuKi');
Query OK, 1 row affected (0.01 sec)

mysql> insert into team_table(id,name) values(2,'Google');
Query OK, 1 row affected (0.00 sec)

插入重复数据报错

mysql> insert into team_table(id,name) values(2,'Google');
ERROR 1062 (23000): Duplicate entry '2' for key 'team_table.PRIMARY'

插入NULL报错

mysql> insert into team_table(name) values('YouTube');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

也可以使用标记约束添加主键约束

mysql> create table team_table(id int,name varchar(255),primary key(id));
Query OK, 0 rows affected (0.01 sec)
复合主键

一个字段做主键,叫做单一主键,而多个字段联合起来做主键,则称之为复合主键

创建一个复合主键

mysql> create table team_table(id int,name varchar(255),email varchar(255),primary key(id,name));
Query OK, 0 rows affected (0.01 sec)

正常插入

mysql> insert into team_table(id,name,email) values(1,'YQHP-YuKi','@123.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into team_table(id,name,email) values(2,'Google','@google.com');
Query OK, 1 row affected (0.00 sec)

重复插入报错

mysql> insert into team_table(id,name,email) values(1,'YQHP-YuKi','@google.com');
ERROR 1062 (23000): Duplicate entry '1-YQHP-YuKi' for key 'team_table.PRIMARY'

注:在实际开发当中,不建议使用复合主键,建议使用单一主键,因为主键值存在的意义就是记录这行的身份证号,只要意义达到即可,单一主键就可以做到,复合主键比较复杂,不建议使用

主键个数只能唯一

例:尝试创建两个主键,出现报错

mysql> create table team_table(id int primary key,name varchar(255) primary key);
ERROR 1068 (42000): Multiple primary key defined

注:一张表,主键约束只能添加1个

主键值建议类型

主键值一般都是数字,一般都是定长的

建议类型:

  • int
  • bigint
  • char

不建议类型:

  • varchar
自然主键和业务主键
  • 自然主键:主键值是一个自然数,与业务无关
  • 业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键,这就是业务主键

在实际开发中,自然主键使用比较多,因为主键只要做到不重复即可,不需要有意义,业务主键不好,因为主键一旦与业务挂钩,那么当业务发生变动的时候,可能会影响到主键,所有业务主键不建议使用,尽量使用自然主键

在mysql当中,使用auto_increment自动递增机制,可以帮助我们自动维护一个主键

注:auto_increment从1开始递增

例:创建一个auto_increment主键

mysql> create table team_table(id int primary key auto_increment,name varchar(255));
Query OK, 0 rows affected (0.01 sec)

插入数据:

mysql> insert into team_table(name) values('YQHP'),('Google'),('YouTube'),('Facebook'),('ins'),('Telegram');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

查看一下数据,发现id是从1开始递增

mysql> select * from team_table;
+----+----------+
| id | name     |
+----+----------+
|  1 | YQHP     |
|  2 | Google   |
|  3 | YouTube  |
|  4 | Facebook |
|  5 | ins      |
|  6 | Telegram |
+----+----------+
6 rows in set (0.00 sec)
Last modification：March 6, 2021
© Allow specification reprint
If you think my article is useful to you, please feel free to appreciate