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(*) >2?检索选修>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(*) >2 检索选修>3门的课学生学号 表
表3.3.2连接查询>等值连接SELECT *FROM S.SCWHERE S.S# = SC.S#(SQL92，SQL99,自然连接通过select列体现)SELECT * FROM S [inner] join SC0n S.S# = SC.S# (SQL92 ,SQL99)>自然连接（消除重复列sid）SELECT * FROM S natural join SC (SQL99)SELECT * FROM S join SC using (s#) (SQL99)99标准更灵活SELECT... FROM tablelJOINtable2ONtable1和table2的连接条件JOINtable3ONtable2和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 fCP=s.CCRPC#CNCPC#CNCR551DB41DB42222MAMA331414ISIS463463osOs575744DataStructDataStruct2266DataProcessDataProcess767644PASCALPASCAL
➢ 自连接 检索每门课的间接预修课 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