MySQL数据库的约束+进阶版新增与查询-2

MySQL数据库的约束+进阶版新增与查询-1

https://developer.aliyun.com/article/1504235

联合查询（多表查询）

笛卡尔积

要想知道多表查询，我们就必须先理解笛卡尔积，笛卡尔积是一种运算，对可能出现的情况进行排列组合，示例如下：

笛卡尔积的特点是如果表1有n列，表2有m列，那么笛卡尔积后的表有n+m列，以此类推多个表的笛卡尔积的列数，如果表1有a行，表2有b行，那么笛卡尔积后的表有a*b行，以此类推多表进行笛卡尔积的行数

多表查询（内连接）

示例导入

我们先要创建几张表来演示如何进行多表查询，示例如下：

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) ,
        classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values
('09982','如来','rulai@qq.com',1),
('00835','孙悟空',null,1),
('00391','唐僧',null,1),
('00031','猪八戒','bajie@qq.com',1),
('00054','沙和尚',null,1),
('51234','牛魔王','niumowang@qq.com',2),
('83223','铁扇公主',null,2),
('09527','白龙马','bailongma@qq.com',2);

insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values
-- 如来
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 孙悟空
(60, 2, 1),(59.5, 2, 5),
-- 唐僧
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 猪八戒
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 沙和尚
(81, 5, 1),(37, 5, 5),
-- 牛魔王
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- 铁扇公主
(80, 7, 2),(92, 7, 6);

进入SQL中查询四张表

学生表:

班级表：

分数表：

课程表：

查询

语法

select 列… from 表1，表2…;可以在后面加上where对表与表进行笛卡尔积进行筛选

一 查询孙悟空同学的成绩

1 先查询全部的，发现成绩中有student_id与学生中的id对应，所以可以利用这个条件作为筛选条件

2 条件筛选的时候，我们必须指定表名中的属性与那个表中的属性相对应，本例中student表中id与score表中的student_id相对应，所以有student.id=score.student_id

3 从第二步就可以看出我们要的是孙悟空同学的成绩，所以我们就可以查出来了

查询思路：

对于刚学习sql多表查询，我们可以先一步一步的写出分析，最后得到一步到位的sql语句

二 查询所有同学的总成绩

这个步骤就是在第一个步骤增加聚合查询

我们在进行聚合查询，进行分组求和

三 查询所有同学的成绩和课程以及个人信息

这就是对三张表进行笛卡尔积了，我们可以先查询我们知道了，然后根据条件筛选，

我们可以发现score.course_id对应的就是course.id所以我们就可以进一步查询

最终查询:

多表查询（外连接 join on关键字）

连接示例：

create table student(id int,name varchar(20));

insert into student values(1,‘张三’),(2,‘李四’),(3,‘王五’);

create table score(student_id int,score decimal(3,1));

insert into score values(1,90.0),(2,80.5),(4,70.0);

我们可以发现两张表内容不一样，表二没有id为3的同学，表1没有id为4的同学，这个是故意这样设置理解的，正常情况下表都是对称的

join on 内连接

select 列 from 表1 inner join 表2 on 条件;其中inner可以省略，其实就是与上一节的内连接展示效果是一样的，如果遇到多个表join on可以嵌套，select 列 from 表1 join 表2 on 条件 join 表3 on 条件；

所以可以看出，内连接其实就是两个表的交集

join on 左外连接

select 列 from 表1 left join 表2 on 条件

此时我们查询的内容就以左边这张表为主，把左边这张表的内容要全部查出

join on 右外连接

select 列 from 表1 right join 表2 on 条件；

此时查询出来的我们就是以右边的表为主，集合形式也就是如下：

总结

1 对于内外连接，join on都可以做到，但是利用where条件查询只能进行内连接查询

2 还有子查询以及合并查询等操作，这些操作都不常用，这里就不过多介绍

3 对于多表查询而言，其核心依旧还是sql语句，熟练的掌握sql语句有助于我们高效的工作