===== 第 1 页 =====
第4节、多表操作
4-1:表的复制
4-2：多表查询
4-3：带子查询的插入修改删除
===== 第 2 页 =====
4-1、表的复制
1、复制一个表（结构和数据）
create table 新表 select * from 旧表;
只是复制部分结构（主键和自动增长不复制）和全部数据。
2、只复制一个表的结构
create table 新表 select * from 旧表 where 1=2;
只是复制部分结构（主键和自动增长不复制）
===== 第 3 页 =====
3、只复制数据（假设2个表结构一样）
假设2个表都存在，且结构一样
Insert into 目的表 select * from 源表
将“源表”中的数据，插入到“目的表”中。
4、复制部分数据（假设2个表结构不一样）
假设2个表都存在，且结构不完全一样
Inset into 目的表(字段列表1) select 字段列表2  from 源表  where 条件
===== 第 4 页 =====
5、只拷贝结构
create table 新表 like 旧表;
只拷贝结构，包括主键。
如果要再复制数据，可以再用命令实现
Insert into 目的表 select * from 源表
6、只复制部分字段和数据
Create table 新表 (select 字段1,字段2,… from 旧表)
===== 第 5 页 =====
7、不同数据库拷贝表
Create table 库1.新表 like 库2.旧表;
新表只有结构（包括主键），没有数据。
===== 第 6 页 =====
4-2、多表查询
一、连接
多表查询，首先要在表之间建立连接。再在连接生成的结果集上进行筛选。
连接类型有：
（1）交叉连接
（2）内连接
（3）外连接
以下以2个表为例：左表L，右表R。
===== 第 7 页 =====
1、交叉连接
交叉连接，就是将2个表的所有行进行组合，结果集称为笛卡儿积。
左表L (A\B)      右表R(C\D\E)
左表L 有2行数据，右表R 有3行数据。
那么，交叉连接生成的结果集就是
5列（ABCDE），2*3=6行数据。
A | B
1 | 2
3 | 4
C | D | F
5 | 6 | 7
8 | 9 | 0
a | b | c
===== 第 8 页 =====
创建表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  右表
===== 第 9 页 =====
A | B | C | D | E
1 | 2 | 5 | 6 | 7
1 | 2 | 8 | 9 | 0
1 | 2 | a | b | c
3 | 4 | 5 | 6 | 7
3 | 4 | 8 | 9 | 0
3 | 4 | a | b | c
以下为结果集示意
语句为：
Select  *  FROM  L  CROSS JOIN  R
===== 第 10 页 =====
2、内连接
1）内连接，是将交叉连接生成的结果集进行筛选后的结果。
2）语法：
Select 字段列表  from 左表 join 右表 on  条件
等同于传统的写法是：
Select 字段列表  from 左表, 右表  where  条件
3）说明：
（1）因为2个表中可能有相同名称的字段名，
所以字段列表中，要用“表名.字段名”的方式，指明字段名是哪个表的；条件中，也要用“表名.字段名”的方式。
===== 第 11 页 =====
（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';
===== 第 12 页 =====
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 ; 也是等值连接。
===== 第 13 页 =====
（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;
===== 第 14 页 =====
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;
===== 第 15 页 =====
二、联合查询
从多个表中查询的结果，合并到一个结果集中。
各个表的字段列表相同或类型兼容。
select a,b from l union select c,d from r;
select classno,classname from class union select classno,stuname from student;
===== 第 16 页 =====
三、子查询
1、比较子查询
比较子查询，指在父查询与子查询之间，用比较运算符进行连接的查询。
在这种类型的子查询中，子查询返回的值最多只能有一个。
显示表student中，班级是“00电子商务”的学生
select * from student where ClassNo=(select classno from class where ClassName='00电子商务');
===== 第 17 页 =====
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 '%电子商务%');
===== 第 18 页 =====
3、批量比较子查询
批量比较子查询，指子查询的结果不止一个，父查询和子查询之间需要用比较运算符进行连接。
（1）使用any谓词
如：显示表student中，班级是‘00’级的学生
select * from student where ClassNo=any(select classno from class where ClassName like '00%');
此时，student中的classno，只要与子查询中有一个结果匹配（即为真），则父查询的值为真。否则，父查询为假。
显示结果60条记录
===== 第 19 页 =====
（2）使用all谓词
如：显示表student中，班级不是‘00’级的学生
select * from student where ClassNo
!=all(select classno from class where ClassName like '00%');
此时，将student中的classno，与子查询中的结果一一比较，当都匹配时（即为真），则父查询的值为真。否则，父查询为假。
===== 第 20 页 =====
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条
===== 第 21 页 =====
4-3：带子查询的插入修改删除
1、带子查询的插入
向表中插入的记录可以来自另一张表， 我们可以将一个子查询的结果集插入到一个表中，语法格式如下：
INSERT INTO 表名[{字段列表}]   <子查询>
说明：
其中，当表名后的{字段列表}省略时，子查询的目标列要与表中字段的数目顺序和数据类型一致；若不省略{字段列表}，子查询的目标列须和{字段列表}保持一致。
===== 第 22 页 =====
例1：
（1）将student表，只拷贝结构为表“学生”。
create table 学生 like student;
（2）向学生表中插入数据，数据来自student表中classno为‘20000001’的同学。
insert into 学生 select * from student where classno='20000001';
===== 第 23 页 =====
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%');
===== 第 24 页 =====
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%');