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一查询满足条件的元组●比较大小： <、<=、>、>=、=、SELECTSN.SAFROMSWHERESD-'CSSELECT*FROMSWHERE SA<20确定范围：BETWEEN...ANDSELECT*FROMSWHERESABETWEEN2OAND21确定集合：INSELECT*FROMSWHERESDIN(CS',IS'MA')字符匹配：LIKE，转义字符，1SELECT*FROMSWHERES#LIKE‘TB%SELECT*FROMSWHERESNLIKE‘刘涉及空值的查询：ISNULLSELECT*FROMSCWHEREGRISNULL多重条件查询：SELECT*FROMSWHERESD='CSANDSA<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 表
表>查询结果排序ORDERBY<字段表达式>ASCDESCSELECT * FROM SC WHERE C#='3' ORDER BY GR DESC>使用集(聚合)函数COUNT 、SUM、AVG、MAX、MINSELECT COUNT(*) FROM SSELECT COUNT(DISTINCT S#) FROM SCSELECT AVG(GR) FROM SC WHERE S#='95001"SELECT MAX(GR) FROM SC WHERE C#='1">查询分组：GROUPBYSELECT C#,COUNT(*) FROM SC GROUP BY C#SELECT S# FROM SC GROUP BY S# HAVING COUNT(*) >37检索选修>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 S.S# = SC.S#自身连接检索每门课的间接预修课SELECT f.C#. s.CP FROM C fC s WHERE fCP=s.C#CPCRPC#CNC#CNCR55441DB1DB2222MAMA34314IS1IS346346OSOS554747DataStructDataStruct2626DataProcessDataProcess776464PASCALPASCAL
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,SA,SD,C#,GR FROM S, SCWHERES.S#*=SC.S#(T-SQL语法 SYBASE)SELECT S#,SN,SS,SA,SD,C#,GR FROM S, SCWHERE S.S#=SC.S#(+）(PL/SQL语法ORACLE)SELECT S#,SN,SS,SA,SD,C#,GRFROM S LEFT OUTER JION SC ON S.S#=SC.S#(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) 表