3.3.1单表查询 假设： S (S#,SN,SS,SA,SD) C (C#,CN,CP,CR) SC (S#,C#,GR) >选取表中的某些列，即投影运算 一查指定列 SELECT S#.SN FROM S 一查全部列 SELECT FROM STUDENT -查经过计算的列 SELECT SN,2015-SA FROM S
3.3.1单表查询 假设： S（S#，SN，SS，SA，SD） C（C#，CN，CP，CR） SC（S#，C#，GR） ➢ 选取表中的某些列，即投影运算 – 查指定列 SELECT S#,SN FROM S – 查全部列 SELECT * FROM STUDENT – 查经过计算的列 SELECT SN，2015-SA FROM S
>选择表中的若干元组，即选择运算 表 -消除取值重复行 SELECT DISTINCT SD FROM S 一查询满足条件的元组 ●比较大小：<、<=、>、>=、=、◇ SELECT SN.SA FROM S WHERE SD-'CS SELECT FROMS WHERE SA<20 ●确定范围：BETWEEN..AND SELECT FROMS WHERE SA BETWEEN 20 AND 21 ●确定集合：N SELECT FROMS WHERE SD IN (CS','IS','MA') ●字符匹配：LKE,转义字符’ SELECT FROMS WHERE S#LIKE TB SELECT*FROMS WHERE SN LIKE‘刘？ ●涉及空值的查询：IS NULL SELECT FROM SC WHERE GR IS NULL ●多重条件查询： SELECT FROMS WHERE SD='CS'AND SA<20
➢ 选择表中的若干元组，即选择运算 – 消除取值重复行 SELECT DISTINCT SD FROM S – 查询满足条件的元组 ⚫比较大小：<、<= 、>、>=、=、<> SELECT SN,SA FROM S WHERE SD=’CS’ SELECT * FROM S WHERE SA<20 ⚫确定范围：BETWEEN... AND SELECT * FROM S WHERE SA BETWEEN 20 AND 21 ⚫确定集合：IN SELECT * FROM S WHERE SD IN (‘CS’,’IS’,’MA’) ⚫字符匹配：LIKE，转义字符’\’ SELECT * FROM S WHERE S# LIKE ‘TB%’ SELECT * FROM S WHERE SN LIKE ‘刘_’ ⚫涉及空值的查询：IS NULL SELECT * FROM SC WHERE GR IS NULL ⚫多重条件查询： SELECT * FROM S WHERE SD=’CS’ AND SA<20 表
>查询结果排序 表 ORDER BY<字段表达式>ASCDESC SELECT FROM SC WHERE C#=3'ORDER BY GR DESC >使用集（聚合）函数 COUNT、SUM、AVG、MAX、MN SELECT COUNT(*)FROM S SELECT COUNT(DISTINCT S#)FROM SC SELECT AVG(GR)FROM SC WHERE S#=95001' SELECT MAX(GR)FROM SC WHERE C#='1' >查询分组：GROUP BY SELECT C#,COUNT(*)FROM SC GROUP BY C# SELECT S#FROM SC GROUP BY S#HAVING COUNT(*)>3 检索选修>3门的课学生学号
➢ 查询结果排序 ORDER BY <字段表达式> ASC|DESC SELECT * FROM SC WHERE C#=’3’ ORDER BY GR DESC ➢ 使用集(聚合)函数 COUNT 、SUM、AVG、MAX、MIN SELECT COUNT(*) FROM S SELECT COUNT(DISTINCT S#) FROM SC SELECT AVG(GR) FROM SC WHERE S#=’95001’ SELECT MAX(GR) FROM SC WHERE C#=’1’ ➢ 查询分组：GROUP BY SELECT C#,COUNT(*) FROM SC GROUP BY C# SELECT S# FROM SC GROUP BY S# HAVING COUNT(*) >3 检索选修>3门的课学生学号 表
表 3.3.2连接查询 >等值连接 SELECT FROM S.SC WHERE S.S#=SC.S# (SQL92,SQL99,自然连接通过select列体现) SELECT FROM S [inner]join SC on S.S#=SC.S#(SQL92 ,SQL99) >自然连接（消除重复列sd) SELECT FROM S natural join SC (SQL99 SELECT FROMS join SC using (s#)(SQL99) SELECT...FROM tablel 99标准更灵活 JOIN table2 ON tablel和table2的连接条件 JOIN table3 ON table2和table3的连接
3.3.2连接查询 ➢ 等值连接 SELECT * FROM S,SC WHERE S.S# = SC.S# (SQL92 ，SQL99,自然连接通过select列体现) SELECT * FROM S [inner] join SC on S.S# = SC.S# (SQL92 ,SQL99) ➢ 自然连接 （ 消除重复列sid） SELECT * FROM S natural join SC (SQL99 ) SELECT * FROM S join SC using (s#) (SQL99 ) 表 SELECT ... FROM table1 JOIN table2 ON table1 和 table2 的连接条件 JOIN table3 ON table2 和 table3 的连接 99标准更灵活
>自连接 检索每门课的间接预修课 SELECT f.C#.s.CP FROM C fC s WHERE f.CP=s.C C# CN CP CR C# CN P CR 1 DB 5 4 1 DB 5 2 MA 2 2 MA 2 3 IS 4 3 IS 1 4 4 OS 6 3 4 OS 6 3 5 DataStruct 7 5 DataStruct 7 6 DataProcess 2 6 DataProcess 2 7 PASCAL 6 7 PASCAL 6
➢ 自连接 检索每门课的间接预修课 SELECT f.C#, s.CP FROM C f,C s WHERE f.CP=s.C C＃ CN CP CR 1 DB 5 4 2 MA 2 3 IS 1 4 4 OS 6 3 5 DataStruct 7 4 6 DataProcess 2 7 PASCAL 6 4 C＃ CN P CR 1 DB 5 4 2 MA 2 3 IS 1 4 4 OS 6 3 5 DataStruct 7 4 6 DataProcess 2 7 PASCAL 6 4