SQL基本操作（二）

1、连接查询

需要从多个表中查询

1.1 等值连接

会把多个表生成一个临时的表，再使用where条件过滤

表之间关联的时候得使用一个同样含义的字段来进行关联，比如说学号与学号

例1：查询学生信息及学生的成绩

（用这个方式生成的时候不会管理你的学号，比如上述会对应学号001的同学直接生成8条成绩记录，学号002的同学页生成8条，如此循环知道最后一个同学）

select * from student2,scores

过滤掉错误信息，用2个表学号是否相等来判断

select * from student2,scores
where student2.id=scores.id

用表名.列名表示太麻烦的话，可以起别名 stu sc

结果也如上图

select * from student2 as stu,scores as sc
where stu.id=sc.id

例2：只想要学生表的姓名和成绩表的成绩

select stu.name,sc.score from student2 as stu,scores as sc
where stu.id=sc.id

1.2 内连接

在连接的过程中，先进行条件的判断，条件符合才会连接生成结果
（上一个会生成临时表，这一个不会）

先查一张表，再去连接另一张表

select * from student2
inner join scores

过滤掉

使用join on条件来过滤

select * from student2 as stu
inner join scores as sc
on stu.id=sc.id

1.3 多表连接

例3：查询课程信息及课程成绩

select * from scores as sc,courses as cs
where sc.courseNo=cs.courseNo

使用内连接

select * from scores as sc
inner join courses as cs
on sc.courseNo=cs.courseNo

查询多张表应该两两之间进行关联

需要用到and

例4：查询学生信息及学生的课程对应的成绩

select * from student2 as stu,scores as sc,courses as cs
where stu.id=sc.courseNo and sc.courseNo=cs.courseNo

内连接

（先连接两张表，在此基础之上再连接另一张表，如果还有在连接）

select *from student2 as stu
inner join scores as sc on stu.id=sc.id
inner join courses as cs on cs.courseNo=sc.courseNo

在上述之上再过滤掉某个人

需要在后面写条件还是且的关系，在后面加and

例1：查询狄仁杰的成绩，要求显示姓名、课程名称、成绩

（若有重复的列则会自动显示name,name1，为了避免可以起别名）

select stu.`name`,cs.name as courseName,sc.score from student2 as stu,scores as sc,courses as cs
where stu.id=sc.courseNo and sc.courseNo=cs.courseNo and stu.`name`='狄仁杰'

使用内连接

（条件不能直接写在on后面，需要重新用where语句）

select stu.`name`,cs.`name` as courseName,sc.score from student2 as stu
inner join scores as sc on stu.id=sc.id
inner join courses as cs on cs.courseNo=sc.courseNo
where stu.`name`='狄仁杰'

例2：查询狄仁杰的单元测试成绩，要求显示姓名、课程名、成绩

（在上一题基础上再加一个条件就可了）

select stu.`name`,cs.name as courseName,sc.score from student2 as stu,scores as sc,courses as cs
where stu.id=sc.courseNo and sc.courseNo=cs.courseNo and stu.`name`='狄仁杰' and cs.`name`='单元测试'

内连接

select stu.`name`,cs.`name` as courseName,sc.score from student2 as stu
inner join scores as sc on stu.id=sc.id
inner join courses as cs on cs.courseNo=sc.courseNo
where stu.`name`='狄仁杰' and cs.`name`='Linux'

例3：查询所有学生的数据库成绩，要求显示姓名、课程名、成绩

（删掉上一个例子中的姓名条件即可）

 select stu.`name`,cs.name as courseName,sc.score from student2 as stu,scores as sc,courses as cs
where stu.id=sc.courseNo and sc.courseNo=cs.courseNo  and cs.`name`='数据库'

内连接

select stu.`name`,cs.`name` as courseName,sc.score from student2 as stu
inner join scores as sc on stu.id=sc.id
inner join courses as cs on cs.courseNo=sc.courseNo
where cs.`name`='数据库'

例4：查询男生中的最高成绩，要求显示姓名、课程名、成绩

select stu.name,stu.sex,cs.name as courseName,sc.score from student2 as stu,scores as sc,courses as cs
where stu.id=sc.courseNo and sc.courseNo=cs.courseNo and stu.sex='男'
order by sc.score desc limit 0,1

内连接

select stu.`name`,stu.sex,cs.`name` as courseName,sc.score from student2 as stu
inner join scores as sc on stu.id=sc.id
inner join courses as cs on cs.courseNo=sc.courseNo
where stu.sex='男'
order by sc.score desc
limit 0,1

1.4 左连接

什么是左连接呢?
join前面的表，称之为左表，join后面的表称为右表
左连接意思不管左边的表在右边的表中能不能找到对应的数据结果，左边的数据都要放在最终的结果中，然后再去看左边的数据在右边能不能找到，找到的话就把右边对应的数据添加到那一行后面；如果左边表的记录在右边表中找不到信息，那就用NULL填充。

就是将之前写的inner join变成left join。（对于内连接，若这一学号在另一个表中没有相等的，那么就不会把你这些信息显示到结果表中）

select * from 表1
left join 表2 on 表1.列=表2.列

例1：查询所有学生的成绩，包括没有成绩的学生

select * from students as stu
left join scores as sc on stu.studentNo=sc.studentNo

（执行后会显示所有学生的成绩，没有成绩也会显示，全部以NULL为填充）
（使用内连接只能显示6条记录，因为成绩表中只有6个学生号；而外连接可以看到12条全部的，哪怕没有成绩）

内连接与左连接区别：
内连接是只有两个表中有相同值的时候才会把结果给显示出来。左连接是左边这个表数据在右边这个表找不到对应结果，它也会把这个左边表的数据全部显示到最终的结果中。

例如：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

select * from students as stu
left join scores as sc on stu.id=sc.id
left join courses as cs on cs.courseNo=sc.courseNo

1.5 右连接

与左连接对称，区别就是只需记住哪个是左表哪个是右表，join前面的是左表，join后面的是右表。右连接就是把右边的表的数据全部显示出来，在左边找不到对应的数据就以NULL来填充。

select * from 表1
right join 表2 on 表1.列=表2.列

例1：查询所有课程的成绩，包括没有成绩的课程

使用内连接，并没有显示出来没有成绩的课程

select * from scores as sc
inner join courses as cs
on sc.courseNo=cs.courseNo

使用右连接

select * from scores as sc
right join courses as cs
on sc.courseNo=cs.courseNo

左连接与右连接：对于右连接来说，join前面是左表，join右边是右表，区别就是写成left就是把左表所有数据都显示出来；写成right就是把右表所有数据都显示出来。

小结：

• 当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，在选择合适的列返回
• 等值连接查询：查询的结果为两个表匹配到的数据
• 左连接查询：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用NULL填充
• 右连接查询：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用NULL填充

1.6 自关联

自关联就是一个表与自身连接.
比如说一个区域表，表中3个字段，一个自己的编号aid，一个是区域的名称，还有一个pid，代表了它的上级id。
同一张表中有省有市，想要查询该表中哪些省有哪些市

例1：查询一共有多少省
（省默认是没有上级单位的，所以表中没有上级id，即pid为空的就是省）

select count(*) from areas where pid is null

例2：查询河南省的所有城市（前面是省后面是市，对应）
（市的pid与省的aid相等，代表这个市属于这个省）

select * from areas as sheng,areas as shi
where sheng.aid=shi.pid and sheng.atitle = '河南省'

可以根据省查市，也可以根据市查区县
（也可以用inner join做）

例3：查询河南省的所有区县（省市区在同一个表里）

select * from areas as shi
inner join areas as qu on shi.aid=qu.aid
inner joni areas as sheng on shi.pid sheng.aid
where sheng.atitle='河南省'