MYSQL数据库语句之多表操作(三)

多表操作

join查询(join按照功能分可以分为3类)：

• cross join(交叉连接)：获取多个表中的交叉关系。
• inner join(内连接)：获取2个表中字段匹配关系的记录。
• 外连接
  
  • left join(左连接)：获取左表所有记录，即使右表没有对应的匹配记录。
  • right join(右连接)：用于获取右表所有记录，即使左表没有对应匹配的记录。

案例参考下面多表关系的实例。

union

union:求两张表的并集

语法：
select * from tb1 union select * from tb2; //查询的出来的表，以tb1的表的查询结果的字段为准，且需要列数相同。

案例参考下面的多对多关系的实例。

子查询

子查询：是通过查询的结果作为新表，然后对新表进行数据查询，一般发生在多对多关系中。

语法：
select a.* from (select * from tb1 where 过滤条件) as a where 过滤条件;

多张表
select a.column1,a.column2,b.column1,b.column2... from (select * from tb1 where 过滤条件) as a , (select * from tb2 where 过滤条件) as b where 外键匹配 and 过滤条件;

[注](select * from tb1 where 过滤条件) as a：表示tb2查询出来的结果作为a表，整条语句的意思是：tb2查询的结果作为a表，通过匹配规则查询a表的相关数据

(select * from tb1 where 过滤条件) as a:只要把括号里面的当做一张表就好理解了。

外键

在MySQL中，InnoDB引擎类型的表支持了外键约束。

外键的使用条件：

• 两个表必须是InnoDB表。
• 外键列必须建立了索引，MySQL4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立。
• 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以。

外键语法
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)  
    REFERENCES tbl_name (index_col_name, ...)  
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]  
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]  


 关键字       含义
 CASCADE      删除包含与已删除键值有参照关系的所有记录
 SET NULL     修改包含与已删除键值有参照关系的所有记录，使用NULL值替换（只能用于已标记为NOT NULL的字段）
 RESTRICT     拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)
 NO ACTION    无动作，默认
 SET DEFAULT  设置默认值

请注意，通过ON UPDATE 和 ON DELETE规则，设置MYSQL能够实现自动操作时，如果键的关系没有设置好，可能会导致严重的数据破坏。

外键所有表必须是InnoDB型的，不能是临时表。

如果CONSTRAINT symbol被给出，他在数据库里必须是唯一的，如果没有给出，InnoDB自动创建这个名字。

外键的使用：
- 为已经添加好的数据库表添加外键

语法：
alter table 表名 add constraint FK_ID foreign key(你的外键字段名) references 外表表名(对应的表的主键字段名);

• 删除外键

语法：
ALTER TABLE 表名 DROP FOREIGN KEY 对应的字段名;

• 创建表时添加外键

语法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)  
    REFERENCES tbl_name (index_col_name, ...)  
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]  
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]  

• 查看外键

show create table 表名;

---

多表关系实例：

• 一对一

表设计：创建一个woman表和一个man表，在man表中通过外键连接相关关系。
eg:
一对一关系：
create table woman(
    woman_id int primary key,
    name varchar(30),
    age int
);

insert into woman values(1,'mary',20);
insert into woman values(2,'lucy',22);
insert into woman values(3,'lili',21);
insert into woman values(4,'angle',18);

create table man(
    man_id int primary key,
    name varchar(30),
    age int,
    wife int unique,
    foreign key (wife) references woman(woman_id) on delete cascade
);

[注：]foreign key (wife) references woman(woman_id) on delete cascade;为外键关联

insert into man values(1,'smith',24,2);
insert into man values(2,'jack',22,1);
insert into man values(3,'tom',25,4);
insert into man values(4,'tim',23,3);
insert into man (man_id,name,age) values(5,'yue',18);

1.交叉连接（笛卡儿积查询）

交叉连接（笛卡儿积查询）
写法一：
select * from CUSTOMER c,ORDERS o;
写法二：
select c.ID,c.NAME,o.ORDER_NUMBER,o.PRICE from CUSTOMER c cross join ORDERS o;


//查询2张表的所有相匹配的数据，对应图-1
select * from man,woman;

//查询2张表的夫妻对应的关系数据，对应图-2
select * from man,woman where wife = woman_id;(如果列名不重复可以这样写，但不正规)
select * from man,woman where man.wife = woman.woman_id;（正规写法）

//查询tom和他老婆的信息(man和woman谁放在前面，他们对应的列就在前面),对应图-3
select * from man,woman where man.wife = woman.woman_id and man.name = 'tom';
或者（man m是为了避免表名太长，采用简单的字符替代获取指定的列名）
select m.man_id, m.name, m.age, m.wife, w.woman_id, w.name, w.age from woman w,man m where w.woman_id = m.wife and m.name = 'tom';

//查询tom老婆的信息，对应图-4
select woman.woman_id,woman.name,woman.age from man,woman where man.wifi = woman.woman_id and man.name='tom';
或者
select w.woman_id, w.name, w.age from woman w,man m where w.woman_id = m.wife and m.name = 'tom';

图-1

图-2

图-3

图-4

2.内连接查询

联合查询（inner join）：
写法一：
隐式内连接：select * from CUSTOMER c,ORDERS o where c.id = o.customer_id;
写法二：
显式内连接：select * from CUSTOMER c inner join ORDERS o on c.id = o.customer_id;


//查询2张表中夫妻关系的对应的数据，对应图-3
select * from man inner join woman on man.wife = woman.woman_id;
或者
select * from woman inner join man on man.wife = woman.woman_id;
//这2者的区别，列的排序不同，前者是man表中的列排在前面(从左向右)，后者反之。

//查询tom和他老婆的信息，对应图-3
select m.man_id, m.name, m.age, m.wife, w.woman_id, w.name, w.age from woman w inner join man m on w.woman_id = m.wife and m.name = 'tom';

//查询tom的老婆信息。对应图-4
select w.woman_id, w.name, w.age from woman w inner join man m on w.woman_id = m.wife and m.name = 'tom';

内连接示意图（来源菜鸟教程）

外连接之left join连接示意图（来源菜鸟教程）

外连接之right join示意图（来源菜鸟教程）

3.外连接：

left join(左连接),见图-5

eg:
select * from man left join woman  on woman_id = wife ;

select * from woman left join man  on woman_id = wife ;

right join(右连接)，见图-6

select * from man right join woman  on woman_id = wife ;

select * from woman right join man  on woman_id = wife ;

与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。

图-5

图-6

---

• 一对多与多对一
  一个班级对应多个学生(一对多)，多个学生对应一个班级(多对一)。

案例：建一个班级表class,创建一个学生表students,具体看下面语句。

create table class(
    class_id int primary key,
    grade varchar(30) unique,
    count int
);

insert into class values (1,'first grade',4);
insert into class values (2,'second grade',3);
insert into class values (3,'third grade',4);
insert into class values (4,'fourth grade',3);
insert into class values (5,'fifth grade',4);
insert into class values (6,'sixth grade',3);


create table students(
    stu_id int primary key,
    name varchar(30),
    age int,
    class_id int,
    foreign key(class_id) references class (class_id)
);

insert into students values(101,'tom',6,1);
insert into students values(102,'tim',7,1);
insert into students values(103,'tam',5,1);
insert into students values(104,'tem',6,1);


insert into students values(201,'blue',7,2);
insert into students values(202,'lusi',8,2);
insert into students values(203,'keou',6,2);

insert into students values(301,'lisi',9,3);
insert into students values(302,'lusi',7,3);
insert into students values(303,'lucy',7,3);
insert into students values(304,'kiki',8,3);


insert into students values(401,'mumu',10,4);
insert into students values(402,'bubu',8,4);
insert into students values(403,'mimi',9,4);

insert into students values(501,'koko',10,5);
insert into students values(502,'pop',12,5);
insert into students values(503,'bob',10,5);
insert into students values(504,'coco',11,5);

insert into students values(601,'ailis',13,6);
insert into students values(602,'reti',11,6);
insert into students values(603,'pipo',12,6);

//查询一年级的学生信息
select *from students,class where students.class_id = class.class_id and class.class_id = 1;

//查询tom的信息
select *from students,class where students.class_id = class.class_id and students.name = 'tom';

//查询tom和mimi的年龄，上几年级,班上多少人
select s.name,s.age,c.grade,c.count from students s,class c where s.class_id = c.class_id and (s.name = 'tom' or s.name = 'mimi');

//查询年龄8岁的学生信息
select * from students s,class c where s.class_id = c.class_id and age=8;

//查询__m的学生
select * from students s,class c where s.class_id = c.class_id and s.name like '__m';

//查询%m%的学生
select * from students s,class c where s.class_id = c.class_id and s.name like '%m%';

//查询6,8,10的学生,按class_id降序排列,年龄升序排列
select *from students s,class c where s.class_id=c.class_id and age in(6,8,10) order by s.class_id desc, s.age ;

• 多对多

create table students(
    stu_id int primary key,
    name varchar(30),
    age int
);

insert into students values (01,'tom',16);
insert into students values (02,'tim',15);
insert into students values (03,'jim',14);
insert into students values (04,'mary',16);
insert into students values (05,'lili',17);
insert into students values (06,'lucy',15);


create table teachers(
    tea_id int primary key,
    name varchar(30),
    age int
);

insert into teachers values (01,'blue',34);
insert into teachers values (02,'green',36);
insert into teachers values (03,'red',37);

create table stu_tea(
    id int primary key,
    stu_id int,
    tea_id int,
    foreign key (stu_id) references students(stu_id),
    foreign key (tea_id) references teachers(tea_id)
);

insert into stu_tea values (1,01,01);
insert into stu_tea values (2,01,03);
insert into stu_tea values (3,02,01);
insert into stu_tea values (4,03,02);
insert into stu_tea values (5,03,03);
insert into stu_tea values (6,04,01);
insert into stu_tea values (7,04,02);
insert into stu_tea values (8,05,02);
insert into stu_tea values (9,06,01);


//查询tom的老师及他们的信息
select * from students s,teachers t,stu_tea st where s.stu_id=st.stu_id and t.tea_id = st.tea_id and s.name = 'tom';

//查询tom的老师的信息(tom姓名，tom年龄，tom老师的name,tom老师的年龄)
select s.name as stu_name ,s.age as stu_age,t.name as tea_name,t.age as tea_age  from students s,teachers t,stu_tea st where s.stu_id=st.stu_id and t.tea_id = st.tea_id and s.name = 'tom';

//查询blue老师所教的学生
select * from teachers t,students s,stu_tea st where s.stu_id = st.stu_id and t.tea_id = st.tea_id and t.name = 'blue';

//查询blue老师所教的学生(blue姓名，blue年龄，blue的学生name,blue的学生年龄)
select t.name as tea_name,t.age as tea_age,s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where t.tea_id = st.tea_id and s.stu_id = st.stu_id and t.name = 'blue';

//查询red和blue所教的所有的学生（2者的并集 union）
select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' union select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'blue';

//查询red和blue所教的共有的学生（2者的交集）***
第一种：笛卡尔积
select a.* from 
(select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' ) 
as  a  cross join 
(select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'blue') 
as b on a.stu_name = b.stu_name;

第二种：内连接
select a.* from (select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' ) as  a  inner join 
(select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'blue'
) as b on a.stu_name = b.stu_name;

第三种：where查询
select a.* from (select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' ) as  a ,
(select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'blue') as b where a.stu_name = b.stu_name;

[注]：(select s.name as stu_name,s.age as stu_age from students s,teachers t,stu_tea st where s.stu_id = st.stu_id and t.tea_id=st.tea_id and t.name = 'red' )作为查询结果的表，这种查询方式成为子查询。

参考：

菜鸟教程之MySql