3.31单表查询 假设:S(S#,SN,SS,SA,SD) C (C#, cN, CP, CR) SC(S#, C#, GR) 选取表中的某些列,即投影运算 查指定列 SELECT S#, SN FROMS 查全部列 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 FROM S WHERE SA<20 ●确定范围: BETWEEN.AND SELECT* FROM S WHERE SA BETWEEN 20 AND 21 ●确定集合:N SELECT* FROMS WHERE SD IN(CS, IS, MA) ●字符匹配:LKE,转义字符’ 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
➢ 选择表中的若干元组，即选择运算 – 消除取值重复行 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<字段表达式> ASCDESO SELECT FROM SC WHERE C#=3 ORDER BY GR DESC >使用集(聚合)函数 COUNT、SUM、AVG、MAx、MIN SELECT COUNTO FROM S SELECT COUNTDISTINCT 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 S. SC FROM S SC WHERE SS#=SC S# 自身连接 检索每门课的间接预修课 SELECT f.C# s CP FROM C EC S WHERE f CP=s C# C+CN CP CR C CN CR DB 4 DB 4 MA MA 234567 IS 4 IS 4 OS OS Datastruct Datastruct DataProcess 2 6 DataProcess 2 PASCAL 4 7 PASCAL 4
3.3.2连接查询 ➢ 等值与非等值连接查询 自然连接 SELECT S.*,SC.* FROM S,SC WHERE S.S# = SC.S# ➢ 自身连接 检索每门课的间接预修课 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 表
表 外连接 列出所有学生的修课情况,如果没有选课也列出其基 本信息(左外连接) SELECT S# SN.SS.SASD. C# GR FROMS. SC WHERES.S#*SC.S#(TSQL语法 SYBASE) SELECT S#SN.SS.SA. SD. C# GR FROM S SC WHERE SS#=SCS#(+)( PL/SQL语法 ORACLE SELECT S#SN.SS.SA.SD. C#. GR FROM S LEFT OUTER JION SC ON SS#=SCS# (MYSQL MSSQL)
➢ 外连接 列出所有学生的修课情况，如果没有选课也列出其基 本信息(左外连接) SELECT S#,SN,SS,SA,SD,C#,GR FROM S, SC WHERE S.S# *=SC.S# (T-SQL语法 SYBASE) SELECT S#,SN,SS,SA,SD,C#,GR FROM S, SC WHERE S.S# =SC.S#(+) (PL/SQL语法ORACLE) SELECT S#,SN,SS,SA,SD,C#,GR FROM S LEFT OUTER JION SC ON S.S#=SC.S# (MYSQL MSSQL) 表