一、联合查询1.联合查询的意义在表的设计之初由于要遵守范式的要求数据会被转移到多张表里这时如果要查询一条完整的信息就要从多张表中查找数据比如要获取学生的基本信息和学生的班级信息就要从学生表和班级表中共同获取信息这里的联合就是指多个表。2.多表联合查询时mysql内部的计算过程现有以下两张表和它们的结构1取这两张表的笛卡尔积笛卡尔积给定两个集合 A、B它们的笛卡尔积是 把 A 里每一个元素和 B 里每一个元素两两配对得到所有有序组合的集合记作A × B。取两张表的笛卡尔积并存入临时表的查询语法如下2筛选出有效数据观察两张表可知有效数据为class_idid时的数据因为class表的主键是idstudent的外键是class_id它们的数值相等时数据才能被正常插入所以它们相等时的数据是有效的。3精简数据确定笛卡尔积表的有效数据是class_idid时的数据后就应该把剩余的无效数据去掉精简这个表而我们去掉无效数据的条件也就是class_idid只保留满足这个条件的数据这个条件也就是连接条件。我们在原来取笛卡尔积表的语句后面加上where关键字并跟上连接条件就得到了保留了有效数据的一个临时表也就达到了我们多表联合查询的目的即同时得到这个同学的详细信息和这个同学班级的详细信息。这个也是内连接的老式写法二、内连接inner join内连接是最常用的表连接方式只返回两张表中满足连接条件的匹配数据不匹配的会直接过滤掉本质上就是在笛卡尔积的基础上用连接条件筛选有效数据。语法select * from 表1 inner join 表2 on 表1的主键表2的外键我们删掉on和后面的连接条件发现只剩笛卡尔积表说明on关键字是专门配合inner join关键字使用用来写表和表之间的连接关系用的。我们插入新的学生信息和班级信息再执行一次内连接语句发现班级表中的c语言2509班没有显示出来这是因为它直接被连接条件过滤掉了内连接无法查询出它的存在。这时就需要用到外连接来查询了。三、外连接1.左外连接左连接返回左表中所有的记录和右表中匹配连接条件的记录若右表中没有与左表匹配的记录则返回null。语法select * from 表1 left join 表2 on 连接条件;可以看到left join 左边的表的所有记录会被返回右表没有与左表匹配的记录的地方会返回null2.右外连接右连接与左外连接相反返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录则返回null。语法select * from 表1 right join 表2 on 连接条件可以看到right join 右边表的所有记录都被返回左表没有与右表匹配的记录的地方会返回null3.全外连接结合了左外连接和右外连接的特点会把两张表的所有数据集中显示到这张表上如果某一边表上没有匹配的记录则会返回null。原生mysql不支持全外连接full joinmysql可以通过left join结合right join并通过union合并结果达到全外连接的效果。4.自连接自连接是一个表自己与自己取笛卡尔积可以做到把行转化成列在查询时可以通过where来过滤结果或者实现行与行之间的比较在使用自连接时需要给这个表起两个不同的别名才可以正常运行否则会报错。如图所示需要起别名(as可以省略)5.子查询子查询是把一个select的结果当做另一个select的条件也叫嵌套查询语法select * from 表名 where 列名 (select 列名 from 表名 where 条件语句)或者也可以像俄罗斯套娃一样select * from 表名 where 列名 (select 列名 from 表名 where 列名.......)1单行子查询嵌套的查询中只返回一行数据例如查询李四的同班同学可以看到括号内部就是被嵌套的查询语句它返回了class_id1给外层select语句以便外层查询执行。这也相当于把两个select语句结合起来使用。2多行子查询嵌套查询中返回多行数据例如查询张三和李四的成绩是多少我们创建成绩表并插入张三李四王五赵六这四位同学的成绩所以可以得到select * from score where student_idin(select sid from student where name 张三 or name 李四);与单行子查询不同多行子查询用的不是而是in这个in的意思就是让sid的多个值返回给student_id也就是让student_id匹配子查询中的任意一个值。为了更清楚的看到这是张三和李四的成绩我们还可以用内连接把他们的信息显示出来使用not in可以查询除了张三和李四其他成绩表中留有他们成绩的人的成绩同样也可以使用内连接3多列子查询单行子查询和多行子查询都只返回一列数据而多列子查询则可以返回多列数据外层查询和嵌套的内层查询的列要匹配。比如上面多行子查询中嵌套的内层数据返回的就一列student_id的值单行子查询中也是同理。例如查询重复录入的分数,score,class_id,student_id列相同先插入重复的三组数据select * from score where (score,class_id,student_id) in (select score,class_id,student_id from score group by score,class_id,student_id having count(*)1);其中group by score,class_id,student_id的意思就是把这三列都相同的行分到一个组里having count(*)1就是将分组后存在重复数据的组筛选出来6.合并查询union和union allunion all直接把两段查询结果拼接在一起不去重速度快union拼接后自动去掉重复行还要排序速度慢语法格式查询语句1 union/union all 查询语句2;例如查出class_id1和class_id3的所有学生我们先插入一组重复的数据然后用union all试试看可以看到两个查询的结果被拼接到了一起。再用union试试发现张三还在这是因为两个张三的sid不同所以不算重复数据故无法去除我们转到分数score表里看看插入重复数据我们现在查询一下student_id4和5的分数是多少先用union all试试可以发现并没有去重再用union试试重复数据被去除