MySQL数据表高级操作（克隆、清空、创建临时表、外键约束）

--------数据表高级操作--------

 

1、克隆表，将数据表的数据记录生成到新的表中

方法一：先复制表再导入数据

create table test01 like abc;   #通过 LIKE 方法，复制 abc 表结构生成 test01 表
insert into test01 select * from abc;

 

 

 

 

 

 

 

 

 

方法二：复制表的同时导入数据

CREATE TABLE test02 (SELECT * from abc);
 
show create table test02\G  #获取数据表的表结构、索引等信息
SELECT * from test02;

 

 

 

2、清空表，删除表内的所有数据

方法一：delete删除

delete from test01;

#DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。

 

 

 

 

 

 

方法二:truncate删除

truncate table test01;

#TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录。

 

 

 

3、创建临时表

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

例：

create temporary table test03 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));
 
insert into test03 values(1,'zhangsan',123456,'running');
 
select * from test03;
show tables;
 
quit
select * from test03;

 

 

 

 

 

 

4、创建外键约束，保证数据的完整性和一致性

外键的定义:

如果同一个属性字段x在表一中 是主键，而在表二中不是主键，则字段x称为表二的外键

 

MySQL中6种常见的约束

主键约束（primary key）

外键约束（foreign key）

非空约束（not null）

唯一性约束（unique [key|index]）

默认值约束（default）

自增约束（auto_increment）

主键表和外键表的理解:

以公共关键字作主键的表为主键表(父表、主表)

以公共关键字作外键的表为外键表(从表、外表)

注意：与外键关联的子表的字段必须设置为主键。要求主表外键字段和子表的字段具备相同的数据类型、字符长度和约束。

操作步骤：

1）创建主键表和从表

 

 

 

 

2） 为主键表添加一个主键约束。主键名建议以“PK_”开头（Primary Key）

alter table pri add constraint PK_id primary key(id);

 

 

 

3）为从表添加外键，外键名建议以“FK_”开头，并与主键表建立关联

alter table sub add constraint FK_id foreign key (id) references pri (id);

 

 

 

4）插入新的数据记录时，要先主表再从表

 

 

 

 

 

 

注意事项：

数据的添加顺序是先主表后从表

从表的数据条数不得多于主表的数据条数

 

5）删数数据记录时，要先从表再主表，也就是说删除主键表时必须先删除其他与之关联的表

 

 

 

6）查看和解除外键约束以及删除外键

show create table sub;
alter table sub drop foreign key FK_id;
alter table sub drop key FK_id;
desc sub;