1 / 175

第 5 章 关系数据库标准语言 SQL 5.1 SQL 概述 特点：

第 5 章 关系数据库标准语言 SQL 5.1 SQL 概述 特点：. 集 DDL 、 DML 、 DCL 于一体. 非过程化. 集合操作. 易学易用. 与三级模式关系. 5.2 查询 所用数据库 : 1) 学生 - 课程数据库 Students( Sno , Sname, Ssex, Sage, Sdept) Courses( Cno , Cname, Cpno, Ccredit) SC( Sno , Cno , Grade). 2) 电影数据库

lamond
Download Presentation

第 5 章 关系数据库标准语言 SQL 5.1 SQL 概述 特点：

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第5章 关系数据库标准语言SQL 5.1 SQL 概述 特点： 集DDL、DML、DCL于一体 非过程化 集合操作 易学易用 与三级模式关系

  2. 5.2 查询 所用数据库: 1) 学生-课程数据库 Students(Sno, Sname, Ssex, Sage, Sdept) Courses(Cno, Cname, Cpno, Ccredit) SC(Sno, Cno, Grade)

  3. 2)电影数据库 Movies(title,year,length,genre,studioName,producerC#) StarsIn(movieTitle,movieYear,starName) MovieStars(name,address,gender,birthdate) MovieExecs(name,address,cert#,netWorth) Studios(name,address,presC#)

  4. 5.2.1 单表查询 *选择若干列--------SQL 投影 例: 查找学生的学号和姓名 SELECT Sno, Sname FROM Students 例:查找学生的姓名、学号和所在系 SELECT Sname, Sno, Sdept FROM Students

  5. 例: 查找学生的相关信息 SELECT * FROM Students 例:查找学生的姓名和出生年份 SELECT Sname, 2010- Sage FROM Students

  6. 去掉重复元组 例: 查找选课学生的学号 SELECT Sno 等价于 SELECT ALL Sno FROM SC FROM SC SELECT DISTINCT Sno FROM SC

  7. *查询满足条件的元组----------SQL 选择 WHERE子句 运算符/关键字 比较 =, >, <, >=, <=, <>, !>, !< 确定范围 [NOT] BETWEEN ... AND ... 确定集合 [NOT] IN 字符匹配 [NOT] LIKE 空值 IS [NOT] NULL 逻辑 AND OR NOT 串的拼接 ||P146 + SQL Server 2000以上

  8. 例: 查找计算机系学生的名字 SELECT Sname FROM Students WHERE Sdept=‘计算机’

  9. 例: 查找年龄小于19的学生的姓名和年龄 SELECT Sname, Sage FROM Students WHERE Sage<19 NOT Sage>=19

  10. 例:查找有过不及格成绩的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade<60

  11. P144 例6.1： 查找Disney 公司，在1990年制作的所有电影的有关信息 SELECT * FROM Movies WHERE studioName=‘Disney’ AND year=1990

  12. 例 :查找年龄在20到23 之间的学生的姓名,所在系和年龄 SELECT Sname, Sdept, Sage FROM Students WHERE Sage BETWEEN 20 AND 23 例 :查找年龄不在20到23 之间的学生的姓名,所在系和年龄 SELECT Sname, Sdept, Sage FROM Students WHERE Sage NOTBETWEEN 20 AND 23

  13. 例 : 查找计算机系、经管系的学生的姓名和性别 SELECT Sname, Ssex FROM Students WHERE Sdept IN (‘计算机’,’经管’) 不用IN? 例：查找非计算机系非经管系的学生的姓名和性别 SELECT Sname, Ssex FROM Students WHERE Sdept NOT IN (‘计算机’,’经管’) 不用IN?

  14. 字符匹配 [NOT] LIKE 匹配串 [ ESCAPE 换码字符] 通配符： % 任意长度为 n 的字符串 n>=0 _(下划线) 任意字符 [ ] 指定范围内的任意单个字符 [ ^] 不在指定范围内的任意单个字符

  15. 例： LIKE ‘%been%’ LIKE ‘Ma%’ LIKE ‘[CK]%’ 第1个是C或K LIKE ‘[^A-D]%’ 第1个不是A-D之间 LIKE ‘%ea_’ LIKE ‘_en’

  16. P148 换码字符/转义字符 LIKE ‘X% % X%’ ESCAPE ‘X’ %……%

  17. P148 例6.7 查找电影名以‘Star’开头后面紧跟4个字母的所有电影名 SELECT title FROM Movies WHERE title LIKE ‘Star _ _ _ _’

  18. 例：查找没有成绩的选课记录中的学号和课程号例：查找没有成绩的选课记录中的学号和课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NULL 例：查找已有成绩的选课记录中的学号和课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL

  19. 例：查找计算机系年龄小于19的学生的名字 SELECT Sname FROM Students WHERE Sdept=‘计算机’ AND Sage<19 例：查找计算机系或者年龄小于19的学生的名字 SELECT Sname FROM Students WHERE Sdept=‘计算机’ OR Sage<19

  20. 例：查找计算机系,经管系或建筑系的学生的名字和性别例：查找计算机系,经管系或建筑系的学生的名字和性别 SELECT Sname, Ssex FROM Students WHERE Sdept=‘计算机’ OR Sdept=‘经管’ OR Sdept=‘建筑’

  21. P145 例6.2 查找Disney公司1990年拍摄的影片的片名和长度 SELECT title,length FROM Movies WHERE studioName=‘Disney’ AND year=‘1990’

  22. 例6.3 同6.2 片名的列标题改为name 片长的列标题改为duration SELECT title AS name,length AS duration FROM Movies WHERE studioName=‘Disney’ AND year=‘1990’ SQL Server 2000: 列名 AS 别名 列名 别名 别名 = 列名

  23. 例6.4 进一步，片长以小时计，列标题为lengthInHours SELECT title AS name, length *0.016667 AS lengthInHours FROM Movies WHERE studioName=‘Disney’ AND year=‘1990’

  24. P145 例6.5 进一步改，片长以小时计，且在每一个片长后加‘hrs.’----- 加一个新的列 ，每行都是‘hrs.’ SELECT title ,length *0.016667 ASlength, ‘hrs.’ AS inHours FROM Movies WHERE studioName=‘Disney’ AND year=1990

  25. P147 例：查找MGM公司拍摄的: 1970年以后或片长小于90分钟的所有电影的名字 SELECT title FROM Movies WHERE (year>1970 OR length<90) AND studioName=‘MGM’ 优先级

  26. 日期时间的比较: 第一个日期早于第二个 小于 （<） 同一天内，第一个时间早于第二个 小于（ <） SQL Server 2000： DATETIME ‘6/15/2000’ ‘10:20:10 am’ ‘6/15/2000 10:20:10 am’ ‘6/15/2000 13:00:10’

  27. P151 例6.11 查询Disney公司1990年拍摄的影片信息，以片长的升序排列，同样片长的，以片名的字母序排列。 SELECT * FROM Movies WHERE studioName=‘Disney’ AND year=1990 ORDER BY length,title ORDER BY 3,1

  28. 例：查找选修3号课程的学生的学号及相应成绩, 以成绩降序排列 SELECT Sno, Grade FROM SC WHERE Cno=‘3’ ORDER BYGrade DESC

  29. 例：查找学生信息以所在系的升序,年龄降序排列例：查找学生信息以所在系的升序,年龄降序排列 SELECT * FROM Students ORDER BY Sdept, Sage DESC Sdept 升序

  30. 5.2.2 多表查询 *多表查询的方法之一： 连接查询-----FROM子句列出多个关系 连接条件的一般格式： 关系名1.属性名1 比较运算符 关系名2.属性名2 比较运算符 =,>,<,>=,<=,<> 连接操作选择满足条件的行

  31. P153 例6.12 查找影片“Star Wars”的制片人的姓名 Movies(title,year,length,genre,studioName,producerC#) MovieExecs(name,address,cert#,netWorth) SELECT name FROM Movies, MovieExecs WHERE title =‘Star Wars’ AND producerC# = cert#

  32. P153 例6.13 找所有具有相同地址的影星和高级主管的组合 MovieStars(name,address,gender,birthdate) MovieExecs(name,address,cert#,netWorth) SELECT MovieStars.name, MovieExecs.name FROM MovieStars, MovieExecs WHERE MovieStars.address = MovieExecs.address 消除属性的二义性 表名做前缀

  33. 例：查询每个学生及其选课情况 SELECT Students.*, SC.* 等值连接 FROM Students, SC WHERE Students.Sno =SC.Sno 笛卡尔积： SELECT Students.*, SC.* FROM Students, SC

  34. 例：查询每个学生及其选课情况 SELECT Students.*, SC.Cno, SC.Grade FROM Students, SC WHERE Students.Sno =SC.Sno 自然连接

  35. 例：查询每门课的课程号及其先修课的先修课的课程号例：查询每门课的课程号及其先修课的先修课的课程号 SELECT FIRST.Cno , SECOND.Cpno 别名 FROM Courses FIRST, Courses SECOND WHEREFIRST .Cpno= SECOND .Cno 自连接

  36. 例：查询选修2号课程且成绩在90分以上的学生的例：查询选修2号课程且成绩在90分以上的学生的 学号与姓名 SELECT Students.Sno, Sname FROM Students, SC WHERE Students.Sno = SC.Sno AND Cno=‘2’ AND Grade>90

  37. 例 ： 查询每个学生选修的课程名及其成绩, 同时输出学号、姓名 SELECT Students.Sno, Sname, Cname, Grade FROM Students, SC,Courses WHERE Students.Sno =SC.Sno AND SC.Cno=Courses.Cno

  38. 例 ： 查询计算机系每个学生选修的课程名及其成绩 SELECT Students.Sno, Sname, Cname, Grade FROM Students, SC,Courses WHERE Students.Sno =SC.Sno AND SC.Cno=Courses.Cno AND Sdept=‘计算机’

  39. 5.2.3 使用聚集函数 AVG ([ALL|DISTINCT] 列名) COUNT ([ALL|DISTINCT] 列名) 非空 COUNT (*) 统计元组个数 MAX ( 列名) MIN( 列名) SUM ([ALL|DISTINCT] 列名) SQL Server2000 列名-----表达式 DISTINCT 对不重复列统计

  40. P168 例6.29 找出所有高级主管的平均净资产 MovieExecs(name,address,cert#,netWorth) SELECT AVG(netWorth) FROM MovieExecs

  41. 例: 统计高级主管的人数 SELECT COUNT(*) FROM MovieExecs 假定 重名 查找不同 名字的个数 SELECT COUNT( DISTINCT name) FROM MovieExecs

  42. 例：学生人数 SELECT COUNT(*) FROM Students 例: 选课学生人数 SELECT COUNT (DISTINCT Sno) FROM SC

  43. 例：1#课程最高分 SELECT MAX(Grade) FROM SC WHERE Cno=‘1’ 例：1#课程平均分 SELECT AVG(Grade) FROM SC WHERE Cno=‘1’

  44. *对查询结果分组 使用聚集函数时，可用GROUP BY 子句对查询结果进行分组计算，分组的原则：指定列相等的分为一组 HAVING 子句：选择满足条件的组

  45. 例:课程,选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno 先分组 再统计 只有在GROUP BY 子句中出现的属性，才能以非聚集的形式出现

  46. P169 例6.31 查找各制片公司以及其摄制电影的总长度 Movies(title,year,length,genre,studioName,producerC#) SELECT studioName, SUM(length) FROM Movies GROUP BY studioName

  47. P170 例6.32 查找每一个制片人制作的电影的总长度 Movies(title,year,length,genre,studioName,producerC#) MovieExecs(name,address,cert#,netWorth) SELECT name, SUM(length) FROM MovieExecs, Movies WHERE producerC# = cert# GROUP BY name

  48. 例6.34 查找1930年以前至少制作过一部电影的制片人制作的所有电影的总长度。 Movies(title,year,length,genre,studioName,producerC#) MovieExecs(name,address,cert#,netWorth) SELECT name, SUM(length) FROM MovieExecs, Movies WHERE producerC# = cert# GROUP BY name HAVING MIN(year)<1930

  49. 例：经管系,选3门以上课程的 SELECT Students.Sno FROM SC,Students WHERE Sdept=‘经管’ ANDStudents.Sno=SC.Sno GROUP BY SC.Sno HAVING COUNT(*)>3 对每一组统计 COUNT(Cno)

  50. SQL查询中子句的顺序 SELECT FROM 必须 WHERE GROUP BY HAVING ORDER BY

More Related