2.外连接:以左表为基础表,在右表中找符合条件的数据,查找不到则以NULL补全;右连接一样;
一、联合查询--多表查询(多表联查):
简单理解就是将多张表合并到一起,然后进行数据的查询;
如何将多张表合并到一起---表的连接:取两张表的笛卡尔积;
A表中的数据与B中数据逐条进行合并,最终将临时表的数据=A表中数据的条数*B表中数据的条数;
如果单纯进行合并,会导致最终生成的临时表非常庞大,查询数据的效率会非常低;因此通常会伴随一个合并条件,符合条件的数据才能在一起合并;
但是有了条件,如果在另一张表中找不到符合条件的数据该怎么处理?所以有三种连接方式被创建:内连接,外连接(左连接&右连接)
1.内连接:只连接符合合并的数据,不符合的全部丢弃;
2.外连接:以左表为基础表,在右表中找符合条件的数据,查找不到则以NULL补全;右连接一样;
在MYSQL中连接操作:left_table[inner/left/right]join right_table on conn_condition;
inner join-内连接;left join-左连接;right join-右连接;condition:合并条件
内外连接实例:
例子:
查询学生的成绩:
select sco.score from student stu
inner join score sco on stu.id=sco.student_id and stu.name='许仙';
或者select * from student
inner join score on student.id=score.student_id where student.name='许仙';
查询所有同学的总成绩,及每个同学的个人信息:
select student.*,sum(score.score) from student
inner join score on student.id=score.student_id group by student.name;
3.自连接:自己连接自己;
显示同一个同学的所有“计算机”比"JAVA"成绩要高的同学信息(同一列):
select score1.score as '计算机原理',score2.score as 'JAVA' from score as score1
inner join score as score2 on score1.student_id=score2.student_id
(and score1.course_id=3 and score2.course_id=1)
inner join course as course1 on course1.id=score1.course_id and course1.name='计算机原理'
inner join course as course2 on course2.id=score1.course_id and course2.name='JAVA'
where score1.score>score2.score ;
二、子语句查询:
一个SQL语句查询的过滤条件,依赖另一个sql语句的查询结果
1.子sql返回结果为单个时 :=
比如:找一个与“不想毕业”同班的同学:
select stu1.* from student as stu1
inner join student as stu2 on stu1.classes_id=stu2.classes_id where stu2.name='不想毕业'; //复杂了
所以子句查询为:
select *from student where classes_id=(select classes_id from student where name='不想毕业'); //依赖的子句只有一句;
2、子句多个时:in、exists
找与两个不同班同学同班的学生:
=()换成in();
或者用exists():
比如:查询“语文”或“英文”的成绩信息
select * from score where exists(select * from course where(name='语文' or name='英文')
and score.course_id=couse.id);
1、先从score表中取出一条数据,然后使用这条数据的course_id,作为子语句查询条件的过滤依赖项
2、比如子语句是要查询课程信息,查询语文或英文的课程信息,但是查询的课程信息必须与刚才主sql中的course_id一样
in()和exists()的区别:
in是先执行子sql语句--()里的语句,将每一条数据判断是否指定字段在内存的集合中,相当于是每条数据都跟内存中的集合数据进行一次对比
exists先执行主语句,逐条取出数据,将数据的某个字段到子语句中执行,如果有结果返回真,否则为假,exists相当于要针对主语句的每条语句都要重新查一次表(也就是说只需要查询主语句的条数就行)
当子语句结果并不多的情况下用in,多的情况用exists;比如子语句结果有10W条,最差比对10亿次,适用于exists,每条数据在数据库中重新再次查询一次(数据库索引关系效率也不低);
四、合并查询:
不是把多表合起来查询,而是把多个类似的sql语句的结果合并在一起返回,每个sql语句都是独立执行;
比如:查询语文或者英文的成绩:
select * from score where course_id=4 or course_id=6;--->
select * from score where course_id=4
union
select * from score where course_id=6;
一个语句如果用到了or,会忽略索引,索引是一个一个大大提高查询效率的东西,忽略了就会降低效率,反而不如使用合并查询;
合并查询的两个操作:
union:自动去重语句结果;union all:就算结果有重复也会全部返回;