数据库在单个表里操作其实很简答,但是涉及在多张表里寻找数据的时候,难度会大大增加,这里解释一些多表联合查询常用的操作。
一、join操作
在数据库的查询中,多表连接查询是一大难点,也是多表查询里的重点。连接主要有以下四种情况:
INNER JOIN(内连接):如果表中有至少一个匹配,则返回行 【在语法中可以省略INNER关键字】LEFT JOIN(左连接):从左表返回所有的行,如果右表中没有匹配,对应的列返回NullRIGHT JOIN(右连接):从右表返回所有的行 ,如果左表中没有匹配,对应的列返回NullFULL JOIN(全连接):只要其中一个表中存在匹配,则返回行(即结合左连接和右连接的结果)
这里主要要理清两个问题:1)以哪个表为基础(从哪个表返回);2)遇到没有匹配的怎么处理。
以下是各个连接方式的关系图:
对于学生表,课程表两张表来说,分别进行四种连接方式,结果如下:
注:
实际中,最常用的是inner join在有些语句里应用了“,”隔开两张表,它的作用相当于inner join
二、union操作
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。
这是在展示结果的时候使用。
比如,还是上边的两张表,可以这样操作:
SELECT 学号 FROM 学生表 UNION SELECT 学号 FROM 课程表
返回结果:
100 200 300
SELECT 学号 FROM 学生表 UNION ALL SELECT 学号 FROM 课程表
这样会允许重复值,返回结果:
100 200 300 100 100 200
三、关于多表查询我的一些小技巧:
先附上一个挺全的数据库题,再以这个题为例说明一些多表查询的技巧:SQL数据库查询练习题及答案(四十五道题)。
1、顺藤摸瓜法
以23题为例:
第二十三题 查询“张旭“教师任课的学生成绩。
通过给出的数据表,我完全可以写出这样一个关系式:
因为给出的条件是张旭老师的名字,那么我就要从Teacher表中查找Tno,然后再通过Tno在Course表中找Cno,最后在通过Cno在Score表中找到条件符合的成绩。
那么,关系就是这样的。知道了这条线,就可以从后往前写出查询语句:
select Sno,Cno,Degree from Score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname=‘张旭’))
####2、合起来搞事情法
根据上边的分析,我可以看到要想完成题目要求,需要用到三个表:Score,Course,Teachet。
那么我把三个表按照对应的联系联合起来即可,格式如下。
select Degree from Score,Teacher,Course where Teacher.Tname=‘张旭’ and Teacher.Tno=Course.Tno and o=o
总之,多表查询最重要的是对着给定的数据库表查一遍,只要能根据已知数据倒推回去,那么就可以倒着写出其查询语句。
当然,会和一些统计语句进行结合,但是思路不变:
第二十四题 查询选修某课程的同学人数多于5人的教师姓名。
同样可以屡出这样一条线:(【】代表在箭头上边)
count(cno)>5 →【Score】→Cno→【Course】→Tno→【Teacher】→Tname。
最后可以写出查询语句:
select Tname from Teacher where Tno in (select Tno from Course where
Cno in (select Cno from Score group by Cno having COUNT(*)>5) )
附:关于统计类函数的使用:
1、group by函数
GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。
其实只要分组处理,一般就会求出这个组里的一些统计值,下边列出一些常用的统计函数:
AVG() :求平均数MAX() :返回指定列的最大值。MIN() :返回指定列的最小值。SUM() :返回数值列的总和COUNT():返回行数SQL MID() :从文本字段中提取字符SQL LEN() :返回文本字段的长度SQL ROUND() :用于把数值字段舍入为指定的小数位数。SQL NOW() :返回系统当前时间
2、having函数
这个函数其实用一句话就可以说清楚:它是聚合函数中的 where 函数。也就是说只是因为group by和where两个函数“一山不容二虎”,所以才出现的having函数进行补充。具体的使用方法和where函数没有什么区别。
例如:
第三十六题 查询至少有2名男生的班号。
select Class FROM student where Ssex=‘男’ group by Class having
COUNT(*)>1
我们意图选择count(*)>1的班级,本来可以用group by class where (数量条件)即可,只是因为where不能用在这里,所以使用having就行。
【待:补充一些全连接,左右连接的应用实例】