数据查询（2）-高级查询

 

•        基本条件查询

比较运算符:>,>=,<,<=,=,!=

between a and b,in(a,b,c),not exists,is null,like ‘%_’,or,and, any,all等

•          把某一字段中内容在特定范围内的记录查询出来

SELECT StudentID, Score FROM SCore WHERE Score BETWEEN 60 AND 80

•          把某一字段中内容与所列出的查询内容列表匹配的记录查询出来

SELECT SName AS 学员姓名,SAddress As 地址 FROM Students WHERE SAddress IN ('北京','广州','上海')

 

•         模糊查询

        SELECT * from Students where SName like ‘_A%’

        SELECT * from Students where SName like ‘%S%’

               

•         集合函数

         -sum,count,max,min,avg

        SELECT AVG(SCore) AS 平均成绩, MAX (Score) AS 最高分,

        MIN (Score) AS 最低分 From Score WHERE Score >=60

 

•        分组查询—GROUP BY

group by 字段名 having 组过滤条件

SELECT CourseID, AVG(Score) AS 课程平均成绩

FROM Score

GROUP BY CourseID

 

分组查询—多列分组

SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩

FROM Score

GROUP BY StudentID,CourseID

 

分组查询—HAVING

SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩

FROM Score

GROUP BY StudentID,CourseID

HAVING COUNT(Score)>1

 

分组查询—对比

1.  WHERE子句从数据源中去掉不符合其搜索条件的数据

2.  GROUP BY子句搜集数据行到各个组中，统计函数为各个组计算统计值

3.      HAVING子句去掉不符合其组搜索条件的各组数据行

理解一下下面的查询语句：

SELECT       部门编号, COUNT(*)

FROM          员工信息表

WHERE       工资 >= 2000

GROUP BY 部门编号

HAVING       COUNT(*) > 1

 

•         子查询

SELECT * FROM BOOKS B WHERE B.PRICE IN (

SELECT MAX(PRICE) FROM  BOOKS);

 

•        并集(Union)和交集(Intersect)

 (SELECT *

FROM BOOKS B, BORROW C

WHERE B.BNO=C.BNO

AND B.BNAME = '计算方法' INTERSECT

SELECT *

FROM BOOKS B, BORROW C

WHERE B.BNO=C.BNO AND B.BNAME != '计算方法习题集')

ORDER BY C.CNO DESC;

 

•         多表联接查询—分类

多表联接查询—内联接-1

SELECT Students.SName, Score.CourseID, Score.Score

FROM     Students,Score

WHERE  Students.SCode = Score.StudentID

 

多表联接查询—内联接-2

SELECT         S.SName,C.CourseID,C.Score

From             Students AS S

INNER JOIN Score AS C

ON         C.StudentID = S.Scode

 

多表联接查询—三表联接

SELECT

S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩

FROM Students AS S

INNER JOIN Score AS C ON (S.SCode = C.StudentID)

INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)

 

多表联接查询—左外联接

SELECT S.SName,C.CourseID,C.Score

From             Students AS S

LEFT JOIN     Score AS C

ON         C.StudentID = S.Scode

 

多表联接查询—右外联接(和左外连接相反)

SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name

FROM titles

RIGHT OUTER JOIN Publishers

ON Titles.Pub_id = Publishers.Pub_id

 

案例分析1—要求

•          在数据库表中，数据行位置并不重要，但是一个单位中要根据奇数行和偶数行的数据来汇总，在这个汇总的基础上再得到一个数值，因此，要查询数据库表的奇数行和偶数行的总数

•          数据库表名：TBL

字段名：A

主键字段：IDKEY（标识列，种子：1；增长量：1）

 

案例分析1—分析

•          只能依靠标识列的值来进行判断和选取

•          因为数据行可能存在增加、修改和删除，因此标识列的数据值并不“完全可靠”，例如标识列值为3，并不一定是第三行，因为如果第二行被删除了，它就是第二行，实际上也是偶数行

•          根据我们前面使用过的SELECT…INTO，可以创建一张新表，顺便创建新的标识列，再在新的标识列上执行奇偶判断

•          奇数判断依据：标识列值%2不等于0；偶数判断依据：标识列值%2等于

 

案例分析1—T-SQL

SELECT   A, IDENTITY(int,1,1) AS ID

INTO              TEMPTABLE

FROM             TBL

 

SELECT   SUM(A) AS 奇数列汇总

FROM             TEMPTABLE

WHERE   ID%2 <> 0

 

SELECT   SUM(A) FROM AS 偶数列汇总

FROM             TEMPTABLE

WHERE    ID%2 = 0

 

案例分析2—要求

•          一家银行发行了新的信用卡，刚开始的时候推广得很好，但是逐渐废卡也越来越多（卡上的余额少于2元，并且用户长时间不使用该卡），因此银行在二月份把这些少于2元的卡从都数据库表中删除了，但是很快问题就来了，用户发现他的卡再也不能使用而投诉，因此只能再把这些卡恢复。

•          编写SQL，把CardID为23、30等在子表中没有的记录插入到子表中，插入的数据Score项值为2……

 

案例分析2—分析

•          这是一个数据插入的操作，因此要使用INSERT语句来进行

•          这种方式的数据插入，不是固定的数据项，而是从其它表中筛选数据再插入，因此要使用到INSERT INTO…SELECT接构

•          插入的数据项是子表中没有的，需要找到子表中没有的这些数据，我们可以把整个语句的INSERT（前半部分）不考虑，先只考虑SELECT（后半部分），SELECT的任务就是找出两个表中不同的项

•          在前面的联接查询中，使用INNER JOIN…ON可以找出相同的项，编写以下T-SQL：

SELECT M.CardID,2 FROM M

INNER JOIN S ON (S.CardID<>M.CardID)

•          以上把“=”简单地改为“<>”，不能找出不同的项，所找到的项比原来的要多很多，因为两个表之间是进行“交叉”对比的，而不是一一对比，所以会对比出很多“不同”的项来；因此，这种方法不可行

•          考虑我们在上面的左外连接查询，能够查询出左表中存在而相关表不存在的数据项

•          最后，子表中不存在的数据项，可以再加WHERE条件来进行筛选，S.CardID IS NULL可以判断其是否存在子表中存在

 

案例分析2—T-SQL

INSERT INTO        S (S.CardID,S.Score)

SELECT   M.CardID,2

FROM             M

LEFT JOIN      S

ON          (S.CardID=M.CardID)

WHERE   S.CardID IS NULL