MySQL数据表的高级操作

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

方法1：先克隆表结构，再导入表数据。

格式

 create table 新表名 like 旧表名;             #通过LIKE方法，复制旧表的结构生成新表
 
 insert into 新表名 select * from 旧表名;     #再将旧表数据导入新表

 create table zzq02 like zzq;    #通过zzq表克隆一个zzq02表，这样只克隆表结构，没有表数据
 ​
 insert into zzq02 select * from zzq;    #将zzq的表数据导入zzq02中

方法2：创建新表的同时，导入旧表数据。

格式：

 create table 新表名 (select * from 旧表名);

示例：

 create table zzq03 (select * from zzq);   #创建zzq03表的同时，导入zzq表的数据

二、查看表结构的三种方法

 方法一：
 desc 表名;      #以表格形式显示表结构
 ​
 方法二：
 show create table 表名;     #以创建表的命令形式显示表结构
 ​
 方法三：
 desc 表名\G       #如果字段较多，也可以使用"desc 表名\G"的方式竖向显示表结构

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

方法一：delete

 delete from 表名;
 ​
 #DELETE清空表后，返回的结果内有删除的记录条目；
 DELETE 工作时是一行一行的删除记录数据的，删除时速度较慢；
 如果表中有自增长字段，使用"DELETE FROM"删除所有记录后，新添加的记录会从原来最大的记录ID后面继续自增写入记录。
 ​
 
 示例：
 #delete删除所有数据后，新添加的记录id字段值会在原有的13基础上递增1，即增长为14。
 insert into zzq02(cardid,hobby) values(1101,'running');

方法二：truncate

 truncate table 表名;    #相当于格式化，速度很快
 ​
 #TRUNCATE清空表后，没有返回被删除的条目。
 TRUNCATE 工作时是将表结构按原样重新建立，因此在速度方面 TRUNCATE 会比DELETE清空表快。
 使用TRUNCATE TABLE清空表内数据后，ID会从1开始重新记录。
 ​
 
 示例：
 #truncate删除所有数据后，新添加的记录ID会从1开始重新记录。
 insert into zzq02(cardid,hobby) values(1101,'running');

四、创建临时表 TEMPORARY

临时表创建成功之后，使用"SHOW TABLES"命令是看不到创建的临时表的，临时表会在连接退出后被销毁。

如果在退出连接之前，也可以执行增删改查等操作，比如使用"DROP TABLE"语句手动直接删除临时表。

临时表一般用于测试使用，只有当前连接的用户才能看到，相同用户换个终端登录也看不到。退出当前连接（退出数据库）之后临时表会被销毁。

格式：

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

示例：

 create temporary table test01 (        #创建临时表test01
 id int(4) zerofill primary key,
 name varchar(10) not null,
 cardid int(18) not null unique key,
 hobby varchar(50));
 ​
 show tables;      #使用"show tables"查看不到临时表
 ​
 insert into test01 values (1,'张三',123456,'running');    #向临时表中插入数据
 ​
 select * from test01;      #查看临时表的数据，可以查看到
 ​
 quit                       #退出mysql
 
 ​
 #退出数据库后重新进入，查看临时表数据，提示表不存在。临时表会在连接退出后被销毁。
 select * from test01;

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

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

外键的作用：

为了一张表记录的数据不要太过冗余。

保持数据的一致性、完整性。

主键表和外键表的理解：

（1）以公共关键字作主键的表为主键表（父表、主表）

（2）以公共关键字作外键的表为外键表（从表、外表）

注意：

与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

添加外键时必须关联主键（即主表先要添加主键，之后从表再添加外键）

5.1 创建主键表和外键表

创建主键表和外键表：

 #创建主表class
 create table class (cid int,cname varchar(10));
 ​
 #创建从表student
 create table student (id int,name varchar(10),age int,classid int);
 ​
 #为主表class的cid字段添加一个主键约束。constraint为主键创建别名，主键名建议以“PK_”开头。
 alter table class ADD constraint PK_CID primary key (cid);
 ​
 #为从表student的classid字段添加外键，并将student 表的classid字段和class 表的cid字段建立外键关联。外键名建议以"FK_”开头。
 #references关联主键表中的字段。
 alter table student ADD constraint FK_CLASSID foreign key (classid) references class(cid);
 ​
 
 #添加外键时必须关联主键（即先要添加主键，再添加外键）
 #添加主键和外键时，即使不设置别名，系统也会自动创建一个别名。

为两个表插入数据：

 INSERT INTO class values(1,'钢琴');          #为主表插入数据
 ​
 INSERT INTO student values(1,'张三',18,1);   #为从表插入数据
 ​
 INSERT INTO student values(2,'李四',18,2);   #这条数据会插入失败，因为主表的cid字段没有为2的值

5.2 删除主键表中的数据记录

如果想删除主键表中的某条记录，必须先删除外键表中相关联的字段记录。

 #删除数据记录时，要先删从表再删主表。也就是说删除主键表的记录时，必须先删除其他与之关联的表中的记录。
 delete from student where classid=1;       //先删从表的数据
 delete from class where cid=1;             //再删主表的数据

5.3 查看和删除外键约束

第一步删除外键约束，第二步删除别名。

 #查看外键约束
 show create table student;
 desc student;
 ​
 #删除外键约束。
 alter table student drop foreign key FK_CLASSID;   //先删除外键约束
 alter table student drop key FK_CLASSID;           //再删除键（即删除别名）