• 基本条件查询
比较运算符:>,>=,<,<=,=,!=
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—要求
• 在数据库表中，数据行位置并不重要，但是一个单位中要根据奇数行和偶数行的数据来汇总，在这个汇总的基础上再得到一个数值，因此，要查询数据库表的奇数行和偶数行的总数
•