第4章SQL关系数据库查询语言课件

1、第4章 SQL关系数据库查询语言 1第4章 SQL关系数据库查询语言 14.1 SQL语言的特点 SQL是英文（Structured Query Language）的缩写，意思为结构化查询语言，是对存放在计算机数据库中的数据进行组织、管理和查询的一种工具，是一种对关系数据库操作的语言。需要掌握的SQL语句： （1）数据查询 SELECT（2）数据定义 CREATE、DROP、ALTER（3）数据修改 INSERT、UPDATE、DELETE4.1 SQL语言的特点 SQL是英文（Structure4.2 SQL的查询功能 SQL语法格式SELECT ALL|DISTINCT TOP|PERCE

2、NT, AS FROM ,WHERE GROUP BY HAVING ORDER BY ASC|DESCINTO ARRAY |INTO CURSOR | INTO DBF|TABLE |TO FILE ADDITIVE|TO PRINTERPROMPT4.2 SQL的查询功能 SQL语法格式4.2.2 简单查询简单查询是指对单表的简单查询。利用WHERE语句筛选出符合条件的记录；利用AS语句显示出虚拟字段。格式SELECT FROM SELECT FROM WHERE SELECT AS FROM 4.2.2 简单查询简单查询是指对单表的简单查询。4.2.2 简单查询1.显示部分或全部字段【

3、例】显示教师表中所有人的姓名和性别信息SELECT FROM姓名,性别教师【例】显示教师表中所有信息SELECT FROM教师号,姓名,性别,职称,党员否教师*【例】显示教师表中男教师的姓名、职称和性别信息。SELECT 姓名,职称,性别 FROM 教师 WHERE 性别=男4.2.2 简单查询1.显示部分或全部字段姓名,性别教师【例】显示学生表中入学成绩小于500的日语生或英语生的信息。SELECT * FROM 学生; WHERE 入学成绩500 AND 专业=日语 OR 专业=英语SELECT * FROM 学生; WHERE 入学成绩500 AND (专业=日语 OR 专业=英语)SE

4、LECT * FROM 学生 WHERE (入学成绩500 AND 专业=日语) ;OR (入学成绩500 AND 专业=英语)注意Where后面条件的写法【例】显示学生表中入学成绩小于500的日语生或英语生的信息。= DATE() YEAR( )虚拟字段2.查询结果可以是通过某一字段或几个字段计算得到的虚拟字段。【例】显示学生表中所有人的学号、姓名和年龄信息。年龄=？2007年出生的人的年龄=2010-2007=2年龄= 现在年份-出生年份-YEAR(出生年月)SELECT AS FROM SELECT 学号,姓名, 年龄FROM 学生 YEAR(DATE()-YEAR(出生年月) AS 年

5、龄 ;= DATE() YEAR( 注意：WHERE语句后不能使用虚拟字段进行条件限定。【例】显示学生表中年龄大于19岁女生的学号、姓名和年龄信息。SELECT 学号,姓名,YEAR(DATE()-YEAR(出生年月) AS 年龄; FROM 学生 WHERE 性别=“女” AND 年龄19 SELECT 学号,姓名,YEAR(DATE()-YEAR(出生年月) 年龄 FROM 学生;WHERE 性别=女 AND YEAR(DATE()-YEAR(出生年月)19注意：WHERE语句后不能使用虚拟字段进行条件限定。【例】显去掉重复记录3.去掉查询结果中的重复记录【例】显示学生表中都有哪些专业。S

6、ELECT DISTINCT 专业 FROM 学生SELECT 专业 FROM 学生利用DISTINCT去掉查询结果中的重复记录。去掉重复记录3.去掉查询结果中的重复记录SELECT DIS4.2.3 联接查询（简单联接）1.简单联接查询【例】显示学生的学号、姓名和成绩的信息。SELECT 学生.学号,学生.姓名,选课.成绩 FROM 学生,选课; 在一个数据库中多个表之间一般都存在着某些联系，在一个查询语句中如果同时涉及到两个或两个以上的表时，这种查询称之为联接查询（也称为多表查询）。 WHERE后是联接条件,通常表示为两个表中共有字段相等。当不同表中含有相同的字段名时，必须指明是哪个表的字

7、段。 WHERE 学生.学号=选课.学号4.2.3 联接查询（简单联接）1.简单联接查询SELEC4.2.3 联接查询（简单联接）【例】显示学生的学号、姓名、课程名和成绩的信息。SELECT 学生.学号,姓名,课程名,成绩 FROM 学生,选课,课程 ;WHERE 学生.学号=选课.学号 AND 课程.课程号=选课.课程号4.2.3 联接查询（简单联接）【例】显示学生的学号、姓名超联接查询3.超联接查询SELECTFROM INNER|LEFT|RIGHT|FULL JOIN ON INNER JOIN或JOIN为内联接（等值联接）LEFT JOIN 为左联接RIGHT JOIN 为右联接FU

8、LL JOIN 为全联接ON 指明联接条件超联接查询3.超联接查询内联接SELECTFROM INNER JOIN ON INNER JOIN 或JOIN为内联接，也称为等值联接。按照条件进行联接，不满足条件的记录不会出现在查询结果中。 【例】显示学生的学号、姓名和成绩的信息。SELECT 学生.学号,姓名, 成绩 ；SELECT 学生.学号, 姓名, 成绩 FROM 学生,选课;WHERE 学生.学号=选课.学号FROM 学生 JOIN 选课 ON 学生.学号=选课.学号简单联接=内联接内联接SELECTFROM INNER JOI内联接SELECT 学生.学号,姓名,课程名,成绩 ；FRO

9、M 学生 JOIN 选课 JOIN 课程 ；ON 选课.课程号=课程.课程号 ON 学生.学号=选课.学号注意：多个表用“JOIN”语句联接的顺序要与联接条件“ON”的顺序恰好相反。 【例】显示学生的学号、姓名、课程名和成绩的信息。SELECT * FROM A JOIN B JOIN C ON B.*=C.* ON A.*=B.*内联接SELECT 学生.学号,姓名,课程名,成绩 ；注意：左联接LEFT JOIN 为左联接，除按照联接条件进行联接外，第一个表不满足条件的记录也会出现在查询结果中。【例】左联接查询SELECT 学生.学号,姓名,成绩;FROM 学生 LEFT JOIN 选课 O

10、N 学生.学号=选课.学号左联接LEFT JOIN 为左联接，除按照联接条件进行联接外右联接RIGHT JOIN 为右联接，除按照联接条件进行联接外，最后一个表不满足条件的记录也会出现在查询结果中。 【例】右联接查询SELECT 学号, 课程名,成绩; FROM 选课 RIGHT JOIN 课程 ON 选课.课程号=课程.课程号右联接RIGHT JOIN 为右联接，除按照联接条件进行联接全联接FULL JOIN 为全联接，除按照联接条件进行联接外，两个表中不满足条件的记录也会出现在查询结果中。 全联接查询的结果是左联接查询和右联接查询结果的并集。【例】全联接查询SELECT 学生.学号,姓名,

11、课程名,成绩 ；FROM 学生 FULL JOIN 选课 FULL JOIN 课程 ；ON 选课.课程号=课程.课程号 ON 学生.学号=选课.学号全联接FULL JOIN 为全联接，除按照联接条件进行联接外别名在联接操作中，经常需要使用关系名用前缀，有时显得很麻烦。【例】显示学生的学号、姓名、课程名和成绩的信息。SELECT 学生.学号,姓名,课程名,成绩 FROM 学生,选课,课程 ;WHERE 学生.学号=选课.学号 AND 课程.课程号=选课.课程号在联接操作中，可在FROM语句中定义别名来代替关系名。【格式】 SELECT A.学号,姓名,课程名,成绩 ;FROM 学生 A,选课 B

12、,课程 C ;WHERE A.学号=B.学号 AND C.课程号=B.课程号别名并不是必须的，但在关系的自联接操作中，就必不可少了。别名在联接操作中，经常需要使用关系名用前缀，有时显得很麻烦。SELECT A.课程名,B.课程名 FROM 课程表 A,课程表 B; WHERE B.课程号= A.先行课自联接查询2.自联接查询【例】查询所有课程的先行课信息。当一个表自己与自己联接时，可以给表取两个别名别名为：课程表 A和课程表 BA BSELECT A.课程名,的先行课是,B.课程名 ;FROM 课程表 A,课程表 B WHERE B.课程号= A.先行课SELECT A.课程名,的先行课是 A

13、S 先行课程,B.课程名 ;FROM 课程表 A,课程表 B WHERE B.课程号= A.先行课A.课程号= B.先行课SELECT A.课程名,B.课程名 FROM 课程表 A,4.2.4 嵌套查询当查询的条件依赖于另一个查询的结果时，就要在查询条件WHERE短语中嵌套一个子查询。【例】查询与王志伟同一年出生的学生的学号、姓名和出生日期。SELECT 学号,姓名,出生日期 FROM 学生WHERE(SELECTYEAR(出生日期)=;姓名=王志伟)YEAR(出生日期)FROM 学生WHERE19884.2.4 嵌套查询当查询的条件依赖于另一个查询的结果时，量词查询【例】查询大于等于 “07

14、010002”的所有科成绩的学号和课程号。SELECT 学号,课程号 FROM 选课 WHERE 成绩 =(SELECT 成绩 FROM 选课 WHERE 学号=07010002)11ALL注意：在使用、=、!=等比较运算符时，子查询的结果只能是一条记录或一个字段（即唯一值）。当子查询结果是多条记录或多个字段时,应使用带有量词（ANY、SOME、ALL）的查询语句。ANY9095、85量词查询【例】查询大于等于 “07010002”的所有科成绩查询就是依次查找满足字段（列）上条件的所有记录谓词查询NOT IN和NOT EXISTS谓词运算IN和NOT IN表示某个字段值是否在子查询中出现过。【

15、例】查询选修了“C001”或“C004”课程的学生的学号。SELECT 学号 FROM 选课 WHERE 课程号=C001 OR 课程号=C004SELECT 学号 FROM 选课 WHERE 课程号 IN (C001,C004)SELECT 学号 FROM 选课 WHERE 课程号=C001 AND 课程号=C004SELECT 学号 FROM 选课 WHERE 课程号=C001 AND;学号 IN (SELECT 学号 FROM 选课 WHERE 课程号=C004)【例】查询同时选修了“C001”和“C004”课程的学生的学号。学号 =(SELECT )？C0010701000107010

16、00207010003C004070100010701000507010006查询就是依次查找满足字段（列）上条件的所有记录谓词查询NO谓词查询EXISTS和NOT EXISTS:子查询中是否有结果返回(是否存在子查询的结果)。【例】查询没有选修任何一门课的学生信息。SELECT * FROM 学生 WHERE NOT EXISTS ; (SELECT * FROM 选课 WHERE 学号=学生.学号)注意： NOT EXISTS前没有相应的字段名。谓词查询EXISTS和NOT EXISTS:【例】查询没有选谓词查询【例】查询至少选修一门课的学生信息。SELECT * FROM 学生 WHER

17、E EXISTS ; (SELECT * FROM 选课 WHERE 学号=学生.学号)EXISTS等同于简单联接SELECT DISTINCT 学生.学号,姓名 FROM 学生,选课 ; WHERE 学生.学号=选课.学号谓词查询【例】查询至少选修一门课的学生信息。SELECT *NOTEXISTS与NOTIN的区别两者的区别:NOTEXISTS:指定一个子查询，检测最终结果中是否存在子查询的结果。 子查询:根据两个表的关系进行查询,可以查询多个字段 如:NOT EXISTS (SELECT * FROM 选课 WHERE 学号=学生.学号)NOTEXISTS前没有字段名NOTIN:确定给定

18、的值是否与子查询或列表中的值相匹配。子查询:只对表中的某个字段进行查询, 如: 学号 IN (SELECT 学号 FROM 选课 WHERE 课程号=C004)NOT IN 前必须有字段名NOTEXISTS与NOTIN的区别两者的区别:4.2.5 几个特殊的运算符 NOT BETWEENAND查询的值介于什么范围之内(小数在前,大数在后)；【例】查询入学成绩在500到520之间的学生信息。SELECT * FROM 学生 WHERE SELECT * FROM 学生 WHERE 入学成绩 BETWEEN 500 AND 520入学成绩= 500 AND 入学成绩= 520【例】查询入学成绩小于

19、500或大于520的学生信息。SELECT * FROM 学生 WHERE 入学成绩 NOT BETWEEN 500 AND 5204.2.5 几个特殊的运算符 NOT BETWEENNOT LIKE对查询内容进行字符串匹配；通配符%（百分号）表示任意长度的字符串;通配符 _（下划线）表示任意单个字符;查询汉字时，_表示一个汉字;特殊运算符【例】查询学生表中所有姓王的学生信息。SELECT * FROM 学生 WHERE 姓名 LIKE 王%【例】查询课程表中所有课程名中没有”大学”的信息。SELECT * FROM 课程 WHERE 课程名 NOT LIKE%大学%NOT LIKE特殊运算符

20、【例】查询学生表中所有姓王的学IS NOT NULL空值查询。 特殊运算符【例】 查询课程1表中有哪些课程还没有选定老师来上课。SELECT * FROM 课程1 WHERE 教师号 IS NULL注意:空值不是一个确定的值,所以不能用 = NULL进行比较。IS NOT NULL特殊运算符【例】 查询课程1表中有4.2.6 排序【格式】ORDER BY|ASC|DESC ,|ASC|DESC对最终查询结果中的某些字段(或虚拟字段)进行排序，不能在子查询中使用。ASC为升序，DESC为降序，默认为升序排列。ORDER BY语句不能按某个表达式的运算结果进行排序。【例】查询国际贸易专业的学生信息

21、，查询结果按入学成绩升序排列。SELECT * FROM 学生WHERE 专业=国际贸易ORDER BY 入学成绩【例】查询国际贸易专业的学生信息，查询结果按性别降序、入学成绩升序排列。SELECT * FROM 学生 WHERE 专业=“外语 ORDER BY 性别 DESC,入学成绩SELECT * FROM 学生 ORDER BY (YEAR(DATE()-YEAR(出生年月)6SELECT 学号, 入学成绩 FROM 学生 WHERE 专业=国际贸易 24.2.6 排序【格式】ORDER BY|数值4.2.6 排序【格式】ORDER BY|ASC|DESC ,|ASC|DESC对于空值

22、排序，升序时空值记录在最前显示，降序时在最后显示。SELECT * FROM 学生 ORDER BY 入学成绩SELECT * FROM 学生 ORDER BY 入学成绩 DESC4.2.6 排序【格式】ORDER BY|数值4.2.6 排序【格式】 SELECT TOPPERCENT FROM ORDER BYASC|DESC满足条件的前几个前百分之几的记录。TOP要与ORDER BY同时使用。TOP ：1N32767TOP PERCENT: 0.01N99.99SELECT * TOP 4 FROM 学生1 ORDER BY 入学成绩SELECT * TOP 30 PERCENT FROM

23、 学生1 ORDER BY 入学成绩【例】查询入学成绩最高的4个人的信息。DESC【例】查询入学成绩最低的那30%学生的信息。4.2.6 排序【格式】 SELECT TOP4.2.7 计算查询SQL不仅具有一般的检索能力，而且还有计算方式的检索。COUNT()统计记录的个数。SUM() 计算某一列值的总和(此列必须是数值型)。AVG() 计算某一列值的平均值(此列必须是数值型)。MAX() 计算某一列值的最大值。MIN() 计算某一列值的最小值。4.2.7 计算查询SQL不仅具有一般的检索能力，而且还有4.2.7 计算查询【例】查询选修了课程的学生人数。SELECT COUNT( 学号) FR

24、OM 选课DISTINCT 【说明】除非对关系中记录的个数进行计数，否则都应该使用DISTINCTSELECT COUNT(*) FROM 选课【例】查询所有成绩之和。SELECT SUM(成绩) FROM 选课SUM(*) SELECT SUM(DISTINCT成绩) FROM 选课4.2.7 计算查询【例】查询选修了课程的学生人数。SEL4.2.7 计算查询【例】查询国际贸易专业最高的入学成绩。SELECT MAX(入学成绩) FROM 学生 WHERE 专业=国际贸易【例】查询日语专业的平均入学成绩。SELECT AVG(入学成绩) FROM 学生 WHERE 专业= 日语4.2.7 计

25、算查询【例】查询国际贸易专业最高的入学成绩。4.2.7 计算查询【例】查询大于等于 “07010001”的所有科成绩的学号。SELECT 学号FROM 选课 WHERE 成绩 =(SELECT 成绩 FROM 选课 WHERE 学号=07010001)ALL等价于：(SELECT MAX(成绩) FROM 选课 WHERE 学号=07010001)SELECT 学号FROM 选课 WHERE 成绩 =4.2.7 计算查询【例】查询大于等于 “070100014.2.7 计算查询【例】查询课程名为“大学计算基础”的所有成绩之和。SELE SUM(成绩) FROM 选课 WHERE 课程号； (S

26、ELECT 课程号 FROM 课程 WHERE 课程名=大学计算机基础)【例】查询课程名中带有“大学”的所有成绩之和。 (SELECT 课程号 FROM 课程 WHERE 课程名SELE SUM(成绩) FROM 选课 WHERE 课程号； LIKE %大学%) IN IN 4.2.7 计算查询【例】查询课程名为“大学计算基础”的所4.2.8 分组查询【格式】GROUP BY ,字段2,虚拟字段 HAVINGGROUP BY语句把记录分组，并分别对每组进行计算。HAVING语句对每组的记录限定条件，WHERE是对所有的记录限定条件。WHERE限定记录GROUP BY进行分组HAVING限定分组

27、【例】求每个专业的平均入学成绩。SELECT 专业,AVG(入学成绩) FROM 学生GROUP BY 专业【例】求每个专业的男女平均入学成绩。SELECT 专业,性别,AVG(入学成绩) FROM 学生 GROUP BY 专业,性别4.2.8 分组查询【格式】GROUP BY 4.2.8 分组查询SELECT 性别,专业,AVG(入学成绩) FROM 学生;【例】求所有男生各专业的平均入学成绩。WHERE 性别=男 GROUP BY 专业SELECT 专业,AVG(入学成绩) FROM 学生 ;WHERE 性别=“男” GROUP BY 专业 HAVING COUNT(*)=24.2.8 分

28、组查询SELECT 性别,专业,AVG(入学SELECT 学号,AVG(成绩) AS 平均成绩 FROM 选课【例】查询选修了两门以上课程的学生平均成绩等信息。HAVING COUNT(*)=2GROUP BY 学号SELECT 学号,AVG(成绩) AS 平均成绩 FROM4.2.9 集合的并运算UNION:并运算把两个查询结果合成一个结果为了完成并运算，两个查询的结果要求具有相同的字段数，并且对应字段的数据类型和取值范围应该一致。【例】查询选修了C001或C007这两课程的学生信息。SELECT * FROM 选课 WHERE 课程号=C001 ;UNION ;SELECT * FROM

29、选课 WHERE 课程号=C0074.2.9 集合的并运算UNION:并运算4.2.10 查询去向默认情况下,查询结果显示在查询窗口中，如果想保存查询结果，应该使用INTO语句。将结果存放在变量（数组）中：INTO ARRAY SELE * FROM 员工表 WHERE 性别=女 SELE * FROM 员工表 WHERE 性别=女 INTO ARRAY aaaa(1,1)aa(1,2)aa(1,3)aa(1,4)aa(1,5)aa(1,6)aa(1,7)“00001002”“王晓丽”“女”1969/02/03.T.“0003”1aa(4,7)4.2.10 查询去向默认情况下,查询结果显示在查

30、询窗口中，4.2.10 查询去向将结果存放在临时表中：INTO CURSORSELE * FROM 学生 INTO CURSOR cc【说明】临时表自动打开，是一个只读的表文件。程序关闭后该表自动删除。表名后不能写文件扩展名.DBF。 SELE * FROM 学生 INTO CURSOR cc.DBF SELE COUNT(*) FROM 员工表 INTO ARRAY bbbb(1)10SELE COUNT(*) FROM 员工表 INTO ARRAY bb(1,1) 【例】统计员工的个数，并存放到数组BB中。4.2.10 查询去向将结果存放在临时表中：INTO CUR4.2.10 查询去向将

31、结果存放在永久表中：INTO DBF |TABLE SELE * FROM 学生 INTO DBF dd.DBF【说明】表文件自动打开。扩展名.DBF可以省略。将结果存放在文本文件中：TO FILE SELE * FROM 学生 TO FILE ee.txt【说明】有ADDITIVE，结果追加到文件尾部，否则将覆盖原文件。扩展名.TXT可以省略。SELE * FROM 学生 INTO DBF dd TO FILE ee4.2.10 查询去向将结果存放在永久表中：INTO DBF练习：基本操作71.3SELECT DISTINCT * FROM TABA INTO TABLE TABC 注意：S

32、ELECT INTO TABLE具有复制的功能练习：基本操作71.3SELECT DISTINCT *练习：简单应用36.1select distinct 学号 from score1 into table tempselect count(*) as 学生人数 from temp into table new_tableselect count(distinct 学号) as 学生人数 from score1 练习：简单应用36.1select distinct 学练习：基本操作65.4 SELECT 顾客号,顾客点菜表.菜编号,菜名,单价,数量; FROM 顾客点菜表 JOIN 菜单表; ON 顾客点菜表.菜编号 = 菜单表.菜编号; WHERE 单价 = 40; ORDER BY 顾客点菜表.菜编号 DESC; INTO TABLE taba练习：基本操作65.4 SELECT 顾客号,练习select year(出生日期) as 年份,count(*) as 人数; from customers group by 年份 order by 年份; into dbf tableone练习select year(出生日期) as 年份,coun简单应用50.2SELECT 课程名称 FROM course, score ;WHERE course.课程编号= sco