4.4+ 多表操作（进阶）

第4节、多表操作

4-1:表的复制

4-2：多表查询

4-3：带子查询的插入修改删除

4-1、表的复制

1、复制一个表（结构和数据）

create table 新表 select * from 旧表;

只是复制部分结构（主键和自动增长不复制）和全部数据。

2、只复制一个表的结构

create table 新表 select * from 旧表 where 1=2;

只是复制部分结构（主键和自动增长不复制）

3、只复制数据（假设2个表结构一样）

假设2个表都存在，且结构一样

Insert into 目的表 select * from 源表

将“源表”中的数据，插入到“目的表”中。

4、复制部分数据（假设2个表结构不一样）

假设2个表都存在，且结构不完全一样

Inset into 目的表(字段列表1) select 字段列表2 from 源表 where 条件

5、只拷贝结构

create table 新表 like 旧表;

只拷贝结构，包括主键。

如果要再复制数据，可以再用命令实现

Insert into 目的表 select * from 源表

6、只复制部分字段和数据

Create table 新表 (select 字段1,字段2,… from 旧表)

7、不同数据库拷贝表

Create table 库1.新表 like 库2.旧表;

新表只有结构（包括主键），没有数据。

4-2、多表查询

一、连接

多表查询，首先要在表之间建立连接。再在连接生成的结果集上进行筛选。

连接类型有：

（1）交叉连接

（2）内连接

（3）外连接

以下以2个表为例：左表L，右表R。

1、交叉连接

交叉连接，就是将2个表的所有行进行组合，结果集称为笛卡儿积。

左表L (A\B) 右表R(C\D\E)

左表L 有2行数据，右表R 有3行数据。

那么，交叉连接生成的结果集就是

5列（ABCDE），2*3=6行数据。

创建表L, 并赋值

CREATE TABLE l (A varchar(2),B varchar(2));

INSERT INTO l VALUES ('1', '2'), ('3', '4');

创建表R, 并赋值

CREATE TABLE R (C varchar(2),D varchar(2), E varchar(2));

INSERT INTO R VALUES (‘5', ‘6‘, ’7’), (‘8', ‘9‘, ’0’), (‘a', ‘b‘, ’c’) ;

交叉连接的语法：

Select 字段列表 from 左表 cross join 右表

以下为结果集示意

语句为：

Select * FROM L CROSS JOIN R

2、内连接

1）内连接，是将交叉连接生成的结果集进行筛选后的结果。

2）语法：

Select 字段列表 from 左表 join 右表 on 条件

等同于传统的写法是：

Select 字段列表 from 左表, 右表 where 条件

3）说明：

（1）因为2个表中可能有相同名称的字段名，

所以字段列表中，要用“表名.字段名”的方式，指明字段名是哪个表的；条件中，也要用“表名.字段名”的方式。

（2）如果没有on，则等同与交叉连接

select * from l join r;

（3）如果2个表中没有相同名称的字段名，字段列表也可以用*，则列出2个表的所有字段。

select * from l join r on a>1;

或者：select * from l,r where a>1;

（4）如果2个表中没有相同名称的字段名，字段列表也可以不用“表名.字段名”的方式。

select A,B,C from l join r on a>1 and c='8';

select A,B,C from l,r where a>1 and c='8';

（5）on和where同时用，相当于并且的关系。

select * from l join r on a>1 where c='8';

等同于select * from l join r on a>1 and c='8';

4）分类

内连接包括3种类型：

等值连接、非等值连接、自然连接

（1）等值连接： （是使用较多的多表查询）

连接条件使用等于（=）来比较连接字段的值。

通常采用“左表.字段1=右表.字段2”形式。

select classname,stuname,pwd from student join class on student.classno=class.ClassNo;

或：select classname,stuname,pwd from student, class where student.classno=class.ClassNo;

注：如果在select字段列表中有classno，则要使用

classno.student或class.ClassNo的形式。

语句select * from l join r on a=c ; 也是等值连接。

（2）非等值连接

连接条件使用除等于之外的比较运算符（<、>、<=、>=、!=等）来比较连接字段的值。

例：先修改一下r表的数据，以便观察

update r set e='2';

再使用非等值连接观察结果：

select * from l join r on b>e;

select * from l join r on a<e;

（3）自然连接

与等值连接相同，但结果中不出现重复字段。

为便于观察，先修改一下R表的结构和数据,以便2个表有相同字段。

修改结构：Alter table r change E A varchar(2);

修改数据：update r set A=‘3';

先看等值连接（这里A在2个表中都有，故用表名.字段名方式）

select L.A, B,C,D,R.A as 'ra' from L join R on R.A=L.A;

再看自然连接：select L.A, B,C,D from L join R on R.A=L.A;

3、外连接

外连接，将2个表分为主表和从表。

以主表中的每行数据去匹配从表中的数据。

语法：seelct 字段列表 from 左表 left|right join 右表 on 条件

注：外连接，必须有on子句。

外连接只适用于2个表连接，有3种方式：

（1）左外连接：left join

左表为主表

select * from l left join r on 1; （#on表示为真）

等同于：select * from l join r ;

select L.A, B,C,D,R.A as 'ra' from L left join R on L.A=2;

（2）右外连接：right join

右表为主表

select * from l right join r ON 1;

select L.A, B,C,D,R.A as 'ra' from L right join R on L.A=2;

（3）全外连接：full join

包含符合连接条件的行外，还包括2个表的所有行。

但MYSQL目前不支持出现包括2个表的所有行（有null的行）。

select * from L full join R on 1;

二、联合查询

从多个表中查询的结果，合并到一个结果集中。

各个表的字段列表相同或类型兼容。

select a,b from l union select c,d from r;

select classno,classname from class union select classno,stuname from student;

三、子查询

1、比较子查询

比较子查询，指在父查询与子查询之间，用比较运算符进行连接的查询。

在这种类型的子查询中，子查询返回的值最多只能有一个。

显示表student中，班级是“00电子商务”的学生

select * from student where ClassNo=(select classno from class where ClassName='00电子商务');

2、IN子查询

IN子查询，指父查询与子查询之间，用in或not in进行连接，并判断某个字段的值是否在子查询查找到的集合中。

如：

显示表student中，班级名称中有“电子商务”的学生

select * from student where ClassNo in (select classno from class where ClassName like '%电子商务%');

班级名称中没有“电子商务”的学生

select * from student where ClassNo not in (select classno from class where ClassName like '%电子商务%');

3、批量比较子查询

批量比较子查询，指子查询的结果不止一个，父查询和子查询之间需要用比较运算符进行连接。

（1）使用any谓词

如：显示表student中，班级是‘00’级的学生

select * from student where ClassNo=any(select classno from class where ClassName like '00%');

此时，student中的classno，只要与子查询中有一个结果匹配（即为真），则父查询的值为真。否则，父查询为假。

显示结果60条记录

（2）使用all谓词

如：显示表student中，班级不是‘00’级的学生

select * from student where ClassNo

!=all(select classno from class where ClassName like '00%');

此时，将student中的classno，与子查询中的结果一一比较，当都匹配时（即为真），则父查询的值为真。否则，父查询为假。

4、exists子查询

是指在子查询前加上exists或not exists，构成exists表达式；

如果子查询有满足条件的结果，则exists表达式为真，否则为假。

如：显示表student中，班级是‘00’级的学生

select * from student where exists(select classno from class where ClassName like '00%');

显示结果180条记录

说明：注意比较in、any、exists的写法的不同。其结果也不同。

ClassNo in(select classno from class where ClassName like ‘00%’); 结果60条

ClassNo=any(select classno from class where ClassName like '00%');结果60条

exists(select classno from class where ClassName like '00%');结果180条

4-3：带子查询的插入修改删除

1、带子查询的插入

向表中插入的记录可以来自另一张表， 我们可以将一个子查询的结果集插入到一个表中，语法格式如下：

INSERT INTO 表名[{字段列表}] <子查询>

说明：

其中，当表名后的{字段列表}省略时，子查询的目标列要与表中字段的数目顺序和数据类型一致；若不省略{字段列表}，子查询的目标列须和{字段列表}保持一致。

例1：

（1）将student表，只拷贝结构为表“学生”。

create table 学生 like student;

（2）向学生表中插入数据，数据来自student表中classno为‘20000001’的同学。

insert into 学生 select * from student where classno='20000001';

2、带子查询的修改

在带条件的UPDATE语句中，若条件的表达涉及到其他表，则可以将一个子查询放在其WHERE子句中。

Update 表名 set 字段名=新值 where 子查询；

例2：

将student表中00级的学生密码都初始化为‘123456’。

说明：00级的学生，其班级名称在class表中的classname前2位为00。

update student set pwd='123456' where ClassNo in (select classno from class where ClassName like '00%');

3、带子查询的删除

在带条件的delete语句中，若条件的表达涉及到其他表，则可以将一个子查询放在其WHERE子句中。

delete from 表名 where 子查询；

例3：

将student表中00级的学生都删除。

说明：00级的学生，其班级名称在class表中的classname前2位为00。

delete from student where ClassNo in (select classno from class where ClassName like '00%');