测试人员必知必会的SQL知识10
大家晚上好，这几天感冒的比较多，我也不幸中标了，所以这两天没有更新博客了。我们还是继续看SQL吧，今天我们的SQL就介绍完了。我们只介绍了一些最基本的语句，还有一些视图啊，存储过程这些，大家可以自己在学习一下。这里就不介绍了，因为我们用的也不是很多了。在强调一下，我们的重点是查询语句了。
USE commoditysell;--我们先来看一道题来复习一下上次学习的内容，上次我们学习了多表查询的连接--查询销售总量大于20的产品的信息--我们来看，这个题目要求我们查询的是产品信息，那么产品信息是在ProductInfo这个表里吧--我们肯定要用这个表了。又要是销售总量大于20，这个信息又在EmporiumSell这个表里，所以--这个就是多表查询了，SELECT * FROM EmporiumSell;SELECT * FROM ProductInfo;SELECT pd.ProdID,pd.ProdName,UnitPrice,ProdFact FROM ProductInfo pdLEFT JOIN Emporiumsell em ON pd.prodid=em.prodidWHERE em.sellamout>20;SELECT pd.ProdID,pd.ProdName,UnitPrice,ProdFact FROM ProductInfo pdRIGHT JOIN Emporiumsell em ON pd.prodid=em.prodidWHERE em.sellamout>20;SELECT pd.ProdID,pd.ProdName,UnitPrice,ProdFact FROM ProductInfo pdINNER JOIN Emporiumsell em ON pd.prodid=em.prodidWHERE em.sellamout>20;--查询单价大于“060101”产品的单价的产品信息--这个题目的查询的信息都在一个表了啊？他自己和自己连接可以吗？我们来看一下SELECT * FROM ProductInfo pd1LEFT JOIN ProductInfo pd2ON pd2.ProdID='060101' AND pd1.UnitPrice>pd2.UnitPrice;--哦，这个结果里有很多NULL哦，怎么回事--我们来看一下这个连接时怎么连的，ProductInfo表里共有9行记录，那么自己连接自己，先来看第一个--条件，ProdID是'060101'，也就是把060101的所有信息都连接到这个表里，也就是ProductInfo表--里每条记录后面都多了060101的这条记录的信息，而且还有一个条件pd1.UnitPrice>pd2.UnitPrice;--所以，不满足这条记录的，后面060101的信息就是NULL了，怎么解决呢，把NULL屏蔽掉就可以了SELECT * FROM ProductInfo pit1RIGHT OUTER JOIN ProductInfo pit2ON pit2.ProdID='060101' AND pit1.UnitPrice>pit2.UnitPriceWHERE pit1.ProdID IS NOT NULL;--这样就好了吧--那有没有更好的方法呢，看下下面的方法SELECT * FROM ProductInfoWHERE UnitPrice>(SELECT UnitPrice FROM ProductInfo WHERE ProdID='060101');--哦，这个结果比之前的好了啊，这个就是我们接下来要介绍的子查询了--通过这个例子我们可以看出，子查询就是在查询里还有查询了--再来看两个题目看一下查询--查询单价大于平均单价的产品信息--哦，这个不是很简单吗，用一个avg函数就行了啊SELECT * FROM ProductInfoWHERE UnitPrice>avg(UnitPrice);--哦哦，报错了啊，这个就是我们前面说的了，where里不能包含聚合函数了--我们可以用前面讲的使用having子句，也可以用今天的子查询SELECT * FROM ProductInfoWHERE UnitPrice>(SELECT avg(UnitPrice) FROM ProductInfo); --我们前面用的子查询的表都是外查询的表，可不可以是其他表呢，当然可以了--任务：查询已销售商品的厂家的所有信息（返回列表）SELECT * FROM ProductInfoWHERE ProdID IN (SELECT DISTINCT ProdID FROM EmporiumSell);--这里的IN就是在……里的，相当于连续的OR了--下面一个综合的题目，这个数据库是我们前面用过的了--任务：对TeachingDB库进行查询，统计年龄大于平均年龄的教师的姓名（Tname）、--性别（Tsex）、年龄（Tage）、职称（Trank）、所授课程名（Cname）、学分（Credit）、--课时数（Coursehour）、选修该课程的学生的姓名（Sname），按照教师年龄降序排列。--大家自己思考一下，下面是答案，这个不是唯一答案了，有很多种了SELECT tin.Tname,tin.Tsex,tin.Tage,tin.Trank,cin.Cname,cin.Credit,cin.CouresHour,sn.SnameFROM TeacherInfo tinRIGHT JOIN Teach t on tin.Tno=t.TnoRIGHT JOIN CourseInfo cin ON t.Cno=cin.Cno RIGHT JOIN StudentCourse sc ON cin.Cno=sc.CnoRIGHT JOIN StudentInfo sn ON sn.Sno=sc.SnoWHERE Tage>(SELECT avg(Tage) FROM TeacherInfo) ANDcin.Cno in (SELECT cin.Cno FROM TeacherInfo tinright join Teach t on tin.Tno=t.Tnoright join CourseInfo cin ON t.Cno=cin.CnoWHERE Tage>(SELECT avg(Tage) FROM TeacherInfo))ORDER BY tin.Tage DESC