1 / 57

第五章 关系数据库语言--- SQL

第五章 关系数据库语言--- SQL. §1 SQL 概述. SQL, 即：结构化查询语言 Structured Query Language. 一、 SQL 的发展. 1974年由 Boyce 和 Chamberlin 提出 SQL。. 1975年 ~ 1979年 IBM 在 System R 上实现了 SQL 语言。. 第一个 SQL 标准是1986年10月由美国国家标准局（ American National Standard Institute， 简称 ANSI） 公布，称 SQL-86。.

kert
Download Presentation

第五章 关系数据库语言--- 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. 第五章 关系数据库语言---SQL §1 SQL概述 SQL,即：结构化查询语言Structured Query Language 一、SQL的发展 1974年由Boyce和Chamberlin提出SQL。 1975年~1979年IBM在System R上实现了SQL语言。 第一个SQL标准是1986年10月由美国国家标准局（American National Standard Institute，简称ANSI）公布，称SQL-86。 1987年国际标准化组织（International Standards Organization，即ISO）也通过了这一标准。

  2. 1989年，ANSI公布SQL-89。 1992年，ANSI公布SQL-92（也称SQ2）。 当前最新标准是SQL-99（ 即，SQL3 ）。 二、SQL特点 1．综合统一。 2. 高度非过程化。 3. 面向集合的操作方法。 4. 多种使用方式：语言既是自含式语言，又是嵌入式语言。 • 5. 语言简洁，语法简单，易学易用。

  3. SQL 视图1  视图m 外模式 基本表1 基本表2  基本表n 模式 存储文件1  存储文件k 存储模式 三、SQL语言使用的三种表

  4. SQL语言使用三种表：基本表、查询表、视图表 即，基本表是实表。DBMS保存并维护基本表的数据和元数据。 1. 基本表：实际存在的表。 在一个基本表上，可以创建并维护若干个索引表。 用于存放查询的中间结果，以及查询结果的表。 2. 查询表： 查询表是临时表。查询表的数据和元数据都是“临时”的。 3. 视图表：简称：视图。 它是由基本表或者其它视图表“导出”的表。 视图表是“虚”表。视图本身没有独立存在的数据。

  5. 四、SQL语句概述 1. 数据定义： 创建(删除/修改)表、视图、索引 CREATE / DROP /（ALTER） （数据类型） 2. 查询语句 SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

  6. （1）单表查询 目标列表达式 * （2）多表查询(连接查询) DISTINCT 连接表达式 字符串 自身连接 条件表达式 （日期类型） （3）嵌套查询 使用IN谓词 “空”值（NULL） 使用ANY或ALL谓词 聚集函数 （SUM、AVG、 MAX、MIN、 COUNT ） 使用EXISTS谓词 （相关查询） （4）集合查询 （5）视图查询 3. 数据更新： INSERT / DELETE/ UPDATE

  7. 定义 定 义 目 的 对象 创 建 删 除 修 改 表 CREATE TABLE DROP TABLE ALTER TABLE 视图 CREATE VIEW DROP VIEW 索引 CREATE INDEX DROP INDEX §2 数据定义

  8. 一、创建、删除与修改基本表 1.创建基本表 CREATE TABLE〈表名〉 （〈列名〉〈数据类型〉[列完整性约束] [， 〈列名〉〈数据类型〉[列完整性约束] ] ··· [，〈表完整性约束〉] )

  9. 例 创建一个表名Student的基本表。属性包括：Sno(学号；主码)，Sname，Ssex，Sage，Sdept（学生所在系）。 CREATE TABLEStudent （Sno CHAR（5） Sname CHAR（20） NOT NULLUNIQUE， Ssex CHAR（1）， Sage INT DEFAULT 18 ， Sdept CHAR（15）， PRIMARY KEY(Sno) ）

  10. 2. SQL数据类型 （MS SQL Server 2000） INTEGER（或 INT） 四个字节整数 DECIMAL（p [，q]）或NUMERIC(p[，q]) 十进制数共P位，小数点后占q位。5 ~ 17字节。 q≤p， 1≤ P≤38。当q=0时，q可省略。  REAL / FLOAT 实数  CHARACTER（n）或CHAR（n）字符串  VARCHAR（n）变长字符串  DATETIME日期时间型，八个字节 例 ‘2006-4-15 8:30:50’

  11. 3. 修改基本表-----修改表结构(MS SQL SERVER) ALTER TABLE〈表名〉 {ALTER COLUMN〈列名〉{〈新数据类型〉} ADD COLUMN {〈新列名〉〈数据类型〉[列完整性约束] }[, ...n] DROP COLUMN〈列名〉[, ...n] } 例 在表Student中增加一列Scome（“入学时间” ） ALTERTABLE Student ADD Scome DATETIME

  12. 4. 删除基本表 语句格式：DROP TABLE 〈表名〉 例 DROP TABLE Student

  13. 二、创建和删除索引 1.关于数据库索引（自学：p185-p189） （1）能够加快给定属性值的查询响应。 （2）增加数据更新操作的系统开销。 2. 创建数据库索引 CREATE[UNIQUE] [CLUSTERED] INDEX〈索引名〉 ON 〈表名〉 （ 〈列名〉[ASCDESC] [，〈列名〉[ASCDESC] ] ...）

  14. 根结点（内部结点） 索引表 30 10 70 30 80 70 20 60 90 30 60 70 80 90 10 20 数据表 数据块 例 一棵n=3的B树索引 叶子结点

  15. 例1 CREATE UNIQUE INDEX IndSname ON Student(Sname) 例2 CREATE CLUSTERED INDEX IndSdept ON Student(Sdept ASC， Sname DESC) 3. 删除索引 DROP INDEX 〈索引名〉 例 DROP INDEX IndSname

  16. §3 查询 一、查询语句格式 SELECT[ALLDISTINCT] 〈目标列表达式〉[，〈目标列表达式〉]··· FROM 〈表名或视图名〉 [，〈表名或视图名〉] ··· [WHERE〈条件表达式〉] [GROUP BY 〈列名1〉 [HAVING〈条件表达式〉]] [ORDER BY 〈列名2〉[ASC  DESC] [，〈列名3〉[ASC  DESC]]··· ]

  17. 下面的例子经常涉及如下三个表: 1. Student（Sno，Sname，Ssex，Sage，Sdept） 2. Course（Cno，Cname，Cdate， Cpno，Ccredit） 其中，Cdate是开课日期（DATETIME类型）, Cpno是“先修课号”； Ccredit是“学分”。 3. SC（Sno，Cno，Grade） 其中， Grade是考试成绩(INT类型)。

  18. 二、单表查询（参阅：p150 6.1； p174 6.4） SnameY-BirthYearSdept 张三 Year of Birth 1986 CS 李四 Year of Birth 1985 MA 例1 查所有学生的所有属性值。 SELECT * FROM Student 例2 查找学生姓名，出生年份， 所在系。---使用查询结果表达式！ SELECT Sname, ‘Year of Birth’ AS Y-Birth， 2006 – Sage AS Year, Sdept From Student

  19. 例3 查所有选过课的学生（每个学生只允许出现一次-----“自动”消去重复的元组！）。 设：SC当前数据如下： Sno Cno Grade 0301001 1 90 0301001 2 84 0301002 1 91 0301003 2 86 ······ ··· • Sno • 0301001 • 0301002 • 0301003 • ······ SELECT Sno DISTINCT FROM SC

  20. 例4 查找计算机系、年龄不大于19岁的学生学号,姓名，年龄。 SELECT Sno， Sname， Sage FROM Student WHERE（Sage ＜=19）AND（Sdept =‘CS’） 例5 找出年龄介于19和22 的学生学号,姓名,出生年份。 SELECT Sno， Sname， 2006 - Sage FROM Student WHERE Sage BETWEEN 19 AND 22

  21. 例6 （字符串匹配）找计算机学院2003级所有同学的学号，姓名。 • SELECT Sno， Sname • FROM Student WHERE Sno LIKE ‘2003%’ 例7 找出姓名的第二个字是“庆”的学生学号，姓名 SELECT Sno，Sname FROM Student WHERE Sname LIKE ‘_庆%’

  22. 例8 （“空”值查询）找选了课，但至今没有考试成绩的学生学号，课号。 SELECT Sno, Cno FROM SC WHERE Grade IS NULL 例9 找出2004年8月31日以后开课的课程号（使用日期型数据类型） SELECT Cno FROM Course WHERE Cdate > ‘08/31/2004’

  23. 例10（使用聚集函数）查0305050同学的考试总分 注意： 计算不同Sno的个数！ SELECT Sno, SUM(Grade) FROM SC WHERE Sno = ‘0305050’ 例11 查询并统计选过课的学生人数 COUNT（ ） SELECT DISTINCT Sno FROM SC

  24. 例12 查询各门课程号，以及各课程选课人数。 实例 Cno Sno Grade 1 0301001 … 1 0301002 … 2 0301001 … 2 0301003 … 4 0301002 … Cno COUNT（Sno） 4 1 2 2 1 2 按Cno值分组,有相同Cno值的为一个组。每组输出一行。 对结果按Cno排序 SELECT Cno，COUNT（Sno） FROM SC GROUP BY Cno ORDER BY Cno DESC

  25. 例13 查选修了3门以上课程的学生学号（使用HAVING子句）。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT( * )  3 （1）首先，按照GROUP子句的要求，对查询中间结果进行“分组”。 （2）对于（1）中的分组结果，按照HAVING短语指定的“条件表达式”，进行“筛选”，得到查询结果。

  26. 例14 查学生“0302003”的平均考分。 SELECT AVG(Grade) FROM SC WHERE Sno = ‘0302003’ 例15 查03级、1号课程的考试最高分。 SELECT MAX(Grade) FROM SC WHERE (Sno LIKE ‘03%’) AND (Cno = ‘1’) 注：聚集函数不能用于WHERE子句的条件表达式。

  27. 三、多表查询与连接查询（参阅：p158 6.2） Student(Sno Sname Ssex Sage Sdept) 0307001 张三 男 20 CS 0307002 李四 女 19IS 0307003 王五 女 18 MA 0307004 赵六 男 19 IS SC(Sno Cno Grade) 0307001 1 92 0307001 2 85 0307001 3 87 0307002 2 90 0307002 3 80 1. 多表查询的一般方法 例1 设：Student表和SC表有如下一些元组。找出学生基本情况和考试成绩。

  28. SELECT Student.Sno，Sname， Sdept， Grade • Student.Sno Sname Sdept Grade • 0307001 张三 CS 92 • 0307001 张三 CS 85 • 0307001 张三 CS 87 • 0307002 李四 IS 90 • 0307002 李四 IS 80 FROM Student ，SC WHEREStudent.Sno = SC.Sno

  29. 例2 查找张三同学各科考试成绩（给出课程名） SELECT Cname, Grade FROM Course， SC , Student WHERE SC.Cno = Course.Cno AND Student.Sno = SC.Sno AND Sname = ‘张三’ 或： SELECT Cname, Grade FROM Course as A， SC B, Student C WHERE B.Cno = A.Cno AND C.Sno = B.Sno ANDSname = ‘张三’

  30. 2. 连接表达式及其应用（以SQL2为背景） 连接表达式的一般形式： FROM join_table1 join_type join_table2 [ON〈连接条件表达式〉] 其中，join_type可以是： （1）自然连接：NATURAL JOIN 例3 找出学生基本情况和考试成绩（与例1相同） SELECT Student.Sno，Sname， Sdept， Grade FROM Student NATURAL JOIN SC

  31. （2）内连接：JOIN （应加上ON子句） （3）笛卡尔积：CROSS JOIN（不能加ON子句） （4）外连接（略）： 全外连接：NATURAL FULL OUTER JOIN 左外连接：NATURAL LEFT OUTER JOIN 右外连接：NATURAL RIGHT OUTER JOIN 注：连接表达式经常用于SELECT语句的FROM子句。

  32. 例4 列出单科成绩不少于80分的学生学号、姓名、课程号、考试成 绩。 SELECT Student.Sno，Sname， Cno， Grade FROM Student NATURAL JOIN SC WHERE NOT( Grade < 80 )

  33. 例5(左外连接)找出学生基本情况和选课情况。如果一个学生没有选课，就只输出学生基本情况例5(左外连接)找出学生基本情况和选课情况。如果一个学生没有选课，就只输出学生基本情况 SELECT Student.Sno，Sname， Sdept，Cno，Grade FROM StudentNATURAL LEFT OUTER JOINSC

  34. Student.Sno Sname Sdept Cno Grade 0307001 张三 CS 1 92 0307001 张三 CS 2 85 0307001 张三 CS 3 87 0307002 李四 IS 2 90 0307002 李四 IS 3 80 0307003 王五 MA NULL NULL 0307004 赵六 ISNULL NULL

  35. 3. 自身连接 例 设：Course 表中数据如下： Cno Cname Cdate Cpno Ccredit 1 数学 2000/09/01 2 2 数据结构 2000/09/01 1 3 3 数据库 2000/09/01 2 3 查询要求：找出每一门课的先修课的先修课。

  36. FIRST表（Course） Cno Cname Cpno 1 数学 2 数据结构 1 3 数据库 2 SECOND表（Course） Cno Cname Cpno 1 数学 2 数据结构 1 3 数据库 2 Cno Cpno 2 3 1

  37. SELECT FIRST.Cno ， SECOND.Cpno FROM Course ASFIRST Course ASSECOND JOIN ONFIRST.Cpno = SECOND.Cno 其中：FIRST，SECOND 称为表Course 的别名.

  38. 四、嵌套查询（参阅：p165 6.3） 在SQL语言中，一个SELECT-FROM- WHERE称为一个查询块。 如果有一个查询语句，它把一个查询块嵌套在另一个查询块的WHERE子句，或嵌套在GROUP BY子句的HAVING短语中，这样的查询语句称为嵌套查询语句。

  39. 例1 （带IN谓词的子查询）查找与“李四” 在同一个系的学生学号、姓名、系。 父查询 子查询 SELECT Sno，Sname，Sdept FROM Student WHERESdept IN（ ） 注意：子查询不能使用ORDER BY子句。 SELECT Sdept FROM Student WHERE Sname=‘李四’

  40. 例2 查询选择了课程号为3的学生学号与姓名。 SELECT Sno， Sname FROM Student WHERE Sno IN （ ） SELECT Sno FROM SC WHERE Cno IN （‘3’）

  41. 例3（带ANY或ALL谓词的子查询）找出比IS系至少一名学生年龄小的其他系学生名单。 SELECT Sname，Sage，Sdept FROM Student WHERE Sage < ANY（ ） AND Sdept   ‘IS’ ORDER BY Sage DESC SELECT distinct Sage FROM Student WHERE Sdept=‘IS’

  42. 例4 查询比IS系所有学生年龄都小的其他各系学生姓名, 年龄，系名。 SELECT Sname，Sage，Sdept FROM Student WHERE （ Sage < ALL（ ） ） AND （ Sdept   ‘IS’ ）ORDER BY Sage DESC SELECT Sage FROM Student WHERE Sdept=‘IS’

  43. 例5 （带EXISTS谓词的子查询———相关查询） 找出选修了1号课程的所有学生姓名。 相关 ！ 相关！ • SELECT Sname • FROM Student WHERE EXISTS（ SELECT * FROM SC WHERE Cno =‘1’ AND SC.Sno = Student.Sno ）

  44. 五、集合查询 ：集合查询就是对几个查询块的结果集进行并、交、差运算。 例1 查找选修了课程1或课程2的学生学号和姓名。 SELECT SC.Sno, Sname FROM SC, Student WHERE (Cno=‘1’) AND (SC.Sno =Student.Sno) UNION SELECT SC.Sno, Sname FROM SC, Student WHERE (Cno=‘2’) AND (SC.Sno =Student.Sno)

  45. 例2 查找既选修课程1，又选修课程2的学生集合 这实际上是查找选修了课程1学生，与选修了课程2 的学生的交集。 • SELECT Sno • FROM SC • WHERE Cno =‘1’ • AND Sno IN（SELECT Sno • FROM SC • WHERE Cno =‘2’）

  46. 例3 查找选修了课程1，但不选修课程2的学生。 这实际上是查找选修课程1的学生，与选修课程2的学生的差集： SELECT Sno FROM SC WHERE Cno=‘1’ SELECT Sno FROM SC WHERE Cno =‘2’ AND Sno NOT IN（ ）

  47. §4 数据更新（参阅：p179 6.5） 一、插入数据---在表中增加元组数据 INSERT INTO 〈表名〉[（〈属性列1〉[，〈属性列2〉]…）] VALUES（〈常量1〉 [，〈常量2〉 ]…） 例1 插入一个学生新记录 INSERT INTO Student（Sno，Sname，Ssex，Sage，Sdept） VALUES（‘0307005’，‘周泉’，‘男’，19，‘IS’）

  48. 二、修改数据----更改属性取值 UPDATE 〈表名〉 SET 〈列名〉=〈表达式〉 [，〈列名〉=〈表达式〉] … [WHERE〈条件] 例1 把学生0307001的年龄改为21岁。 UPDATE Student SET Sage = 21 WHERE Sno =‘0307001’

  49. 例2（修改多个元组）把所有学生的年龄增加1岁 UPDATE Student SET Sage = Sage + 1 例3 把‘IS’系学生原来选修的‘1’课程改为‘2’ UPDATESC SET Cno = ‘2’ WHERE Sno IN ( SELECT Sno FROM Student WHERE Sdept =‘IS’ ) AND Cno = ‘1’

  50. 三、删除数据-----删去表的元组数据 注意：这是SQL2的DELETE语句格式。 DELETE FROM 〈表名〉 [WHERE〈条件〉] 例1 删去学号为‘0307006’的学生记录 DELETE FROM Student WHERE Sno =‘0307006’ 例2 删去多个元组 DELETE FROM SC WHERE Grade IS NULL

More Related