MySQL数据库详解 二：数据库的高级语言和操作

1. 克隆表 ---- 将数据表的数据记录生成到新的表中

1.1 方式一：先创建新表，再导入数据

create table 新表 like 旧表;			
insert into 新表 select * from 旧表;
#这种方式可以完全复制表内容和表结构		#示例
create table test1 like st;		#通过 LIKE 方法，复制 info 表结构生成 test01 表
insert into test1 select * from st;			#导入数据

1.2方式二：创建的时候同时导入

create table test2 (select * from st);
#创建内容来自st表的test2表
#这种方式不能克隆表结构，只能克隆表内容

2. 清空表 ---- 删除表内的所有数据

2.1 delete删除

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

delete from 表名;delete from students;

2.2 truncate删除（重新记录）

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

truncate table 表名;#示例
truncate table students;

2.3 创建临时表（退出数据库自动删除）

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

create temporary table 表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);##添加临时表test
create temporary table test (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));show tables;					## 查看当前库中所有表

insert into test values(1,'张三',123456789);		##在临时表中添加数据
select * from test;			##查看当前表中所有数据

quit							##退出数据库mysql -u root -p				##重新登录后进行查看	select * from test;			##查看之前创建的临时表中所有数据，发现已经被自动销毁

3. 创建外键约束 ---- 保证数据的完整性和一致性

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

主键表和外键表的区别
（1）以公共关键字作主键的表为主键表（父表、主表）
（2）以公共关键字作外键的表为外键表（从表、外表）

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

#基本格式
外键约束
主键表： alter table 表名 add primary key (主键字段);
外键表： alter table 表名 add foreign key (外键字段) references 主键表名 (主键字段);插入新数据时，需要先在主键表插入数据再在外键表插入对应数据；删除数据时，需要先在外键表删除数据再在主键表删除对应数据

#示例
#创建主表 profession
create table profession (id int(4),proname varchar(50));#创建从表 student
create table student (id int(4) primary key auto_increment,name varchar(10),age int(3),proid int(4));
#创建一个以id为外键并自增长的表student

#为主表 profession 添加一个主键约束。主键名以“PK_”开头。
alter table profession add constraint PK_id primary key (id);#为从表 student 表添加外键，并将 student 表的 proid 字段和 profession 表的 pid 字段建立外键关联。外键名以“FK_”开头。
alter table student add constraint FK_pro foreign key (proid) references profession (id);desc student;

**#插入新的数据记录时，要先主表再从表
insert into profession values(1,'云计算');
insert into profession values(2,'大数据');
insert into student values(1,'张三',18,1);
insert into student values(2,'李四',19,1);
insert into student values(3,'王五',20,2);

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

#查看和删除外键约束
show create table student;     #以sql语句方式显示表结构
desc student;
alter table student drop foreign key FK_pro;
alter table student drop key FK_pro;

3.1 MySQL约束特性

约束名	功能
primary key	主键约束 字段的值不能重复，且不能为null，一个自建的表只能有一个主键
unique key	唯一性约束 字段的值不能重复，能为null，一个表可以有多个唯一键
not null	非空约束 字段的值不能为null
default	默认值约束 字段的值如果没有设置则使用默认值自动填充
auto_increment	自增约束 字段的值如果没有设置默认会从1开始每次自动递增1，要求自增字段必须设置主键
foreign key	外键约束 保证相关联的表数据的完整性和一致性
int(N) zerofill	零填充 表示若数值不满4位数，则前面用“0”填充，例0001

4. SQL高级查询语句

4.1 添加所需表格

create database scj;
use scj;
create table location (Region char(20),Store_Name char(20));
#创建地域表insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');

create table store_info (Store_Name char(20),Sales int(10),Date char(10));insert into store_info values('Los Angeles','1500','2020-12-05');
insert into store_info values('Houston','250','2020-12-07');
insert into store_info values('Los Angeles','300','2020-12-08');
insert into store_info values('Boston','700','2020-12-08');

4.2 selecte 语句

显示表格中一个或数个字段的所有数据记录

#基本语法
SELECT "字段" FROM "表名";#示例
select date,store_name,sales from store_info;
#按照date,store_name,sales的顺序显示store_info表内容

select store_name,sales from store_info;
#也可以只显示特定字段

4.2.1 distinct 语句

不显示重复的数据记录

#基本语法
SELECT DISTINCT "字段" FROM "表名";#示例
select distinct store_name from store_info;
#显示store_info表并去重