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

比眉伴天荒 2022-07-18 05:25 177阅读 0赞

## 多表操作 ##

### 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][-1]图-1

![image][]  
图-2

![image][image 1]  
图-3

![image][image 2]  
图-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';

![image][image 3]  
内连接示意图（来源菜鸟教程）

![image][image 4]  
外连接之left join连接示意图（来源菜鸟教程）

![image][image 5]  
外连接之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 ;

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

![image][image 6]  
图-5

![image][image 7]  
图-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][MySql]

[-1]: http://i4.buimg.com/567571/dab82a02f1443856.jpg
[image]: http://i1.piimg.com/567571/34d3041e317fe6ba.jpg
[image 1]: http://i2.buimg.com/567571/2d8b48c6e0b5e708.jpg
[image 2]: http://i2.buimg.com/567571/25da2536d5a22cde.jpg
[image 3]: /images/20220717/f5e9f9cb26c547eea11cfafb233dd276.png
[image 4]: /images/20220717/7b06b84864af48ea928fb39b779be387.png
[image 5]: /images/20220717/c701b95c924f4530ad7c0cad64aac7e7.png
[image 6]: http://i4.buimg.com/567571/3380113ba9f85131.jpg
[image 7]: http://i4.buimg.com/567571/bbd5e1685da57790.jpg
[MySql]: http://www.runoob.com/mysql/mysql-tutorial.html