数据库基础知识63377.pptx

《数据库基础知识63377.pptx》由会员分享，可在线阅读，更多相关《数据库基础知识63377.pptx（102页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、13.2 3.2 查询查询所用数据库所用数据库:1)学生学生-课程数据库课程数据库 Students(Sno,Sname,Ssex,Sage,Sdept)Courses(Cno,Cname,Cpno,Ccredit)SC(Sno,Cno,Grade)第1页/共102页22)电影数据库电影数据库 Movies(title,year,length,genre,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStars(name,address,gender,birthdate)MovieExecs(name,addr

2、ess,cert#,netWorth)Studios(name,address,presC#)第2页/共102页3单表查询单表查询 *选择若干列选择若干列-SQL 投影投影 例例:查找学生的学号和姓名查找学生的学号和姓名 SELECT Sno,Sname FROM Students 例例:查找学生的姓名、学号和所在系查找学生的姓名、学号和所在系 SELECT Sname,Sno,Sdept FROM Students第3页/共102页4 例例:查找学生的相关信息查找学生的相关信息 SELECT *FROM Students例例:查找学生的姓名和出生年份查找学生的姓名和出生年份 SELECT S

3、name,2011-Sage FROM Students第4页/共102页5 去掉重复元组去掉重复元组 例例:查找选课学生的学号查找选课学生的学号 SELECT Sno 等价于等价于 SELECT ALL Sno FROM SC FROM SC SELECT DISTINCT Sno FROM SC 第5页/共102页6*查询满足条件的元组查询满足条件的元组-SQL 选择选择 WHERE子句子句 运算符运算符/关键字关键字 比较比较 =,=,=,!,!确定范围确定范围 NOT BETWEEN .AND.确定集合确定集合 NOT IN 空值空值 IS NOT NULL 逻辑逻辑 AND OR N

4、OT第6页/共102页7例例:查找计算机系学生的名字查找计算机系学生的名字 SELECT Sname FROM Students WHERE Sdept=计算机计算机第7页/共102页8例例:查找年龄小于查找年龄小于19的学生的姓名和年龄的学生的姓名和年龄 SELECT Sname,Sage FROM Students WHERE Sage=19 第8页/共102页9例例:查找有过不及格成绩的学生的学号查找有过不及格成绩的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade60第9页/共102页10P144 例6.1：查找Disney 公司，在1990年制

5、作的所有电影的有关信息 SELECT*FROM Movies WHERE studioName=Disney AND year=1990第10页/共102页11例：例：查找没有成绩的选课记录中的学号和课程号查找没有成绩的选课记录中的学号和课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NULL 例：例：查找已有成绩的选课记录中的学号和课程号查找已有成绩的选课记录中的学号和课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL第11页/共102页12例：例：查找计算机系年龄小于查找计算机系年龄小于19的学生的名字的

6、学生的名字 SELECT Sname FROM Students WHERE Sdept=计算机计算机 AND Sage19例：例：查找计算机系查找计算机系或者或者年龄小于年龄小于19的学生的名字的学生的名字 SELECT Sname FROM Students WHERE Sdept=计算机计算机 OR Sage,=,=,连接操作选择满足条件的行连接操作选择满足条件的行 第18页/共102页19例：查询选修例：查询选修2号课程且成绩在号课程且成绩在90分以上的学生的分以上的学生的 学号与姓名学号与姓名 SELECT Students.Sno,Sname FROM Students,SC WH

7、ERE Students.Sno=SC.Sno AND Cno=2 AND Grade90 第19页/共102页20例例：查询每个学生选修的课程名及其成绩查询每个学生选修的课程名及其成绩,同时输出学号、姓名同时输出学号、姓名 SELECT Students.Sno,Sname,Cname,Grade FROM Students,SC,Courses WHERE Students.Sno=SC.Sno AND SC.Cno=Courses.Cno第20页/共102页21例例：查询计算机系每个学生选修的课程名及其成绩查询计算机系每个学生选修的课程名及其成绩 SELECT Students.Sno,

8、Sname,Cname,Grade FROM Students,SC,Courses WHERE Students.Sno=SC.Sno AND SC.Cno=Courses.Cno AND Sdept=计算机计算机第21页/共102页22子查询（嵌套查询）子查询（嵌套查询）-多表查询的方法二多表查询的方法二 子查询是在子查询是在SELECT语句的语句的WHERE子句中嵌套的子句中嵌套的SELECT语句。外层语句。外层SELECT语句称为主查询。语句称为主查询。又分为又分为 独立子查询：独立子查询：子查询独立求值，其结果代入主查询。子查询独立求值，其结果代入主查询。相关子查询：相关子查询：子查

9、询对主查询可能选出的每一行均执行一次，子查询对主查询可能选出的每一行均执行一次，不能独立求值。不能独立求值。第22页/共102页23子查询的几种形式：子查询的几种形式：1.NOT IN(子查询子查询)P160P160例例6.206.20 查找查找 Harrison Ford Harrison Ford 主演的所有电影的制片人姓名主演的所有电影的制片人姓名StarsIn(movieTitle,movieYear,starName)Movies(title,year,length,genre,studioName,producerC#)MovieExecs(name,address,cert#,n

10、etWorth)第23页/共102页241)SELECT name2)FROM MovieExecs3)WHERE cert#IN4)(SELECT producerC#5)FROM Movies6)WHERE(title,year)IN SQL Server 20007)(SELECT movieTitle,movieYear8)FROM StarsIn9)WHERE starName=Harrison Ford)10)第24页/共102页25分析：分析：由内而外由内而外 7）8）9）返回返回 图图6-8 4)5)6)返回这些影片的制片人的证书号返回这些影片的制片人的证书号 1)2)3)返回

11、这些制片人的姓名返回这些制片人的姓名书写次序书写次序:由结果到条件由结果到条件第25页/共102页26不采用子查询：不采用子查询：SELECT DISTINCT name FROM MovieExecs,Movies,StarsIn WHERE cert#=producerC#AND title=movieTitle AND year=movieYear AND starName=Harrison Ford第26页/共102页27例：例：查询选修了查询选修了数据结构数据结构的学生的学号、姓名的学生的学号、姓名 SELECT Sno,Sname FROM Students WHERE Sno I

12、N (SELECT Sno FROM SC 过程过程 不相关不相关 WHERE Cno IN (SELECT Cno FROM Courses WHERE Cname=数据结构数据结构)第27页/共102页282.比较运算符比较运算符 ANY|ALL(子查询子查询)返回单值返回单值时可使用时可使用,=,=,!,等运算符等运算符 P159例例6.19 查找查找“Star Wars”的制片人的制片人 SELECT name FROM MovieExecs WHERE cert#=(SELECT producerC#返回单值返回单值12345 FROM Movies WHERE title=Star

13、 Wars)第28页/共102页29例：例：查询与刘雪同系的学生学号、姓名及所在系查询与刘雪同系的学生学号、姓名及所在系 SELECT Sno,Sname,Sdept FROM Students WHERE Sdept=返回单值返回单值 对比对比IN (SELECT Sdept FROM Students WHERE Sname=刘雪刘雪)第29页/共102页30例：例：查询选修了查询选修了数据结构数据结构的学生的学号、姓名的学生的学号、姓名 SELECT Sno,Sname FROM Students WHERE Sno IN 集合集合 (SELECT Sno FROM SC WHERE C

14、no =单值单值 (SELECT Cno FROM Courses WHERE Cname=数据结构数据结构)过程过程 不相关不相关第30页/共102页313.3 3.3 数据库更新数据库更新插入插入 INSERT INTO 关系名关系名 (属性属性1，属性，属性2.)VALUES(常量常量1，常量，常量2.)|SELECT语句语句 说明：插入完整的行，可以省略属性名表说明：插入完整的行，可以省略属性名表 建议包含属性名表，关系模式变化时仍可正确插入建议包含属性名表，关系模式变化时仍可正确插入 插入不完整行，必须包含属性名表插入不完整行，必须包含属性名表 没有列出的列填入没有列出的列填入NUL

15、L(可为空可为空)或缺省值或缺省值 第31页/共102页32 P173 例例6.35 INSERT INTO StarsIn(movieTitle,movieYear,starName)VALUES (The Maltese Falcon,1942,Sydney Greenstreet)INSERT INTO StarsIn 插入完整的行标准顺序插入完整的行标准顺序 VALUES (The Maltese Falcon,1942,Sydney Greenstreet)第32页/共102页33例例 6.36 插入所有插入所有Movie中有中有,Studio中没有的制片公司中没有的制片公司 INS

16、ERT INTO Studios(name)SELECT DISTINCT studioName 子查询子查询 FROM Movies WHERE studioName NOT IN (SELECT name FROM Studios)属性属性 address,presC#取取 NULL 第33页/共102页34删除删除 DELETE FROM 关系名关系名 WHERE 条件条件 无无WHERE子句删除所有行子句删除所有行 第34页/共102页35例例6.37:删除删除Sydney Greenstreet主演主演1942年的影片年的影片 The Maltese Falcon这一事实这一事实.D

17、ELETE FROM StarsIn WHERE movieTitle=The Maltese Falcon AND movieYear=1942 AND starName=Sydney Greenstreet 多对多多对多 (年年,片名片名)-影片影片 准确描述要删除的元组准确描述要删除的元组第35页/共102页36例例6.38:删除所有净收入不足删除所有净收入不足1000万美金的高级主管万美金的高级主管 DELETE FROM MovieExecs WHERE netWorth=100000)6位编号位编号第66页/共102页67CREATE TABLE MovieStars(name C

18、HAR(30)PRIMARY KEY,address VARCHAR(255),gender CHAR(1)CHECK(gender IN(F,M),birthdate DATETIME)第67页/共102页68 触发器及其他触发器及其他 触发器（触发器（trigger）事件事件 条件条件 动作动作仅当触发器声明的事件发生时，触发器被激活仅当触发器声明的事件发生时，触发器被激活事件激活的时候测试触发条件事件激活的时候测试触发条件 条件不满足条件不满足 不做任何动作不做任何动作 条件满足条件满足 DBMS执行触发器中定义的动作执行触发器中定义的动作第68页/共102页69 触发器的相关选项触发器

19、的相关选项1）触发器的条件检查和动作）触发器的条件检查和动作 可以在触发器事件发生之前执行可以在触发器事件发生之前执行 BEFORE 也可以在触发器事件发生之后执行也可以在触发器事件发生之后执行 AFTER2）条件和动作可以引用元组）条件和动作可以引用元组/表的旧值和表的旧值和/或触发或触发事件中更新的新值事件中更新的新值 OLD ROW NEW ROW OLD TABLE NEW TABLE第69页/共102页703）更新事件可以被局限到属性）更新事件可以被局限到属性/属性组属性组4）可以选择动作执行的方式）可以选择动作执行的方式 行级触发行级触发 语句级触发语句级触发第70页/共102页7

20、1例:阻止降低高级主管净资产的操作(改回旧值)CREATE TRIGGER NetWorthTrigger AFTER UPDATE OF netWorth ON MovieExecs REFERRENCING OLD ROW AS OldTuple,NEW ROW AS NewTuple FOR EACH ROW 2-1第71页/共102页72 WHEN (OldTWorthNewTWorth)UPDATE MovieExecs SET netWorth=OldTWorth WHERE cert#=NewTuple.cert#2-2第72页/共102页73例：阻止制片人的平均净资产降到500

21、000美元以下 这里只给出更新 触发器 删除+插入的情况略CREATE TRIGGER AvgNetWorthTrigger AFTER UPDATE OF netWorth ON MovieExec REFERENCING OLD TABLE AS OldStuff NEW TABLE AS NewStuff FOR EACH STATEMENT 第73页/共102页74WHEN (500000 (SELECT AVG(netWorth)FROM MovieExec)BEGIN DELETE FROM MovieExec WHERE (name,address,cert#,netWorth)

22、IN NewStuff INSERT INTO MovieExec (SELECT*FROM OldStuff)END第74页/共102页75 断言断言:约束涉及整个关系约束涉及整个关系 甚至其他关系甚至其他关系 CREATE ASSERTION 制片公司制作的所有电影总长制片公司制作的所有电影总长10000分钟分钟 不存在净资产不足不存在净资产不足1000万美金的总裁万美金的总裁 第75页/共102页76 3.6 索引索引#索引的意义索引的意义 SELECT*FROM Movies WHERE year=1990 AND studioName=Disney Movies 10 000个元组个

23、元组 200个个 1990年制作年制作 可否只从可否只从200个个1990年制作的影片中查询年制作的影片中查询Disney公公司的影片司的影片?以年代建索引以年代建索引 加快查询速度加快查询速度第76页/共102页77索引的分类：索引的分类：UNIQUE（唯一性）（唯一性）：任意两行的被索引列，不能出现重复值（包括空值）任意两行的被索引列，不能出现重复值（包括空值）CLUSTERED（簇式）（簇式）：行的物理顺序和索引顺序相同行的物理顺序和索引顺序相同 一表仅一个一表仅一个 更新时移动数据更新时移动数据 NONCLUSTERED（非簇式）（非簇式）：默认默认 包含索引值和指向数据行的指针包含索

24、引值和指向数据行的指针 不要求行的物理顺序不要求行的物理顺序和索引顺序相同和索引顺序相同 一表可多个一表可多个 复合索引：对表的两列或多列进行索引复合索引：对表的两列或多列进行索引第77页/共102页78 例例:在单一属性上建索引在单一属性上建索引 CREATE INDEX YearIndex ON Movies(year)索引名索引名 关系名关系名 属性属性 例例:在一组属性上建索引在一组属性上建索引 CREATE INDEX KeyIndex ON Movies(title,year)索引名索引名 关系名关系名 属性组属性组 第78页/共102页79删除索引删除索引:DROP INDEX

25、YearIndex 索引名索引名第79页/共102页80关于速度与开销关于速度与开销 索引的有利之处索引的有利之处:提高该属性值已知的查询的速度提高该属性值已知的查询的速度 索引的不利之处索引的不利之处:占空间占空间 增删改时维护索引增加增删改时维护索引增加 系统开销系统开销第80页/共102页81Movies 在什么列建索引在什么列建索引?year title (year,title)速度慢速度慢/空间小空间小 适中适中 速度快速度快/空间大空间大 维护的开销小维护的开销小 维护的开销大维护的开销大 考虑考虑:表表 更新多更新多?查询多查询多?经常用什么列查询经常用什么列查询?第81页/共1

26、02页823.7 3.7 视图视图 为什么要有视图？为什么要有视图？视图是从一个或几个基本表（视图）导出的表。视图是从一个或几个基本表（视图）导出的表。第82页/共102页83从视图中（看到）的数据是从视图中（看到）的数据是 由定义视图的由定义视图的SELECTSELECT语句查询而来。语句查询而来。与基本表不同，系统只保存视图的定义，不重与基本表不同，系统只保存视图的定义，不重复保存其中数据。复保存其中数据。第83页/共102页84 视图的使用：可以对视图进行查询，就象它实际存在一样 -查询到的数据来自基本表 某些情况下，甚至可以对视图进行更新 -更新基本表中的数据第84页/共102页85视

27、图的好处：视图的好处：1.1.一定程度的数据的逻辑独立性一定程度的数据的逻辑独立性 (更新受限更新受限)2.2.安全性安全性 3.3.简化用户的数据处理简化用户的数据处理 4.4.从需要的角度看待数据从需要的角度看待数据 第85页/共102页86视图的定义视图的定义 句法：句法：CREATE VIEW 视图名视图名 (列名表列名表)AS SELECT 语句语句 第86页/共102页87说明：说明：1.1.可建视图的视图（可建视图的视图（SELECTSELECT中象使用基本表一中象使用基本表一样使用视图）样使用视图）2.2.可用列名表指出所有列名可用列名表指出所有列名 或或 省去列名表省去列名表

28、 不指定视图的列名不指定视图的列名 使用查询结果表列名使用查询结果表列名 如下情况必须指定列名如下情况必须指定列名：希望使用不同的列名希望使用不同的列名 表达式、函数所得的列表达式、函数所得的列 多表中的公共列多表中的公共列第87页/共102页88P202 例例8.1 将关系将关系 Movies(title,year,length,genre,studioName,producerC#)的一部分，即的一部分，即Paramount(派拉蒙派拉蒙)制片公司制作的电影名制片公司制作的电影名称和年份作为一个视图：称和年份作为一个视图：CREATE VIEW ParamountMovies AS SEL

29、ECT title,year FROM Movies WHERE studioName=Paramount视图名视图名 视图属性（列）视图属性（列）视图的定义视图的定义第88页/共102页89 将视图的定义存入系统表，不执行将视图的定义存入系统表，不执行SELECTSELECT语句。语句。对视图查询时对视图查询时 按视图的定义从基本表中将数据查出。按视图的定义从基本表中将数据查出。第89页/共102页90例：建立数学系学生的视图例：建立数学系学生的视图 CREATE VIEW MA_Students AS SELECT Sno,Sname,Sage FROM Students WHERE Sd

30、ept=数学数学 视图的列名表视图的列名表 行列子集视图：取行列、保留码行列子集视图：取行列、保留码第90页/共102页91视图可以建立在多表（视图）上视图可以建立在多表（视图）上P203 例例8.2：建立包含电影名和制片人名的视图：建立包含电影名和制片人名的视图 CREATE VIEW MovieProds AS SELECT title,name FROM Movies,MovieExecs WHERE producerC#=cert#视图属性名：视图属性名：title,name第91页/共102页92例：数学系选了例：数学系选了1 1号课程的学生视图号课程的学生视图 CREATE VIE

31、W MA_S1(Sno,Sname,Grade)AS SELECT Students.Sno,Sname,Grade FROM Students,SC WHERE Sdept=数学数学 AND Students.Sno=SC.Sno AND SC.Cno=1 视图属性表不可省视图属性表不可省第92页/共102页93P203 有时更愿意使用自己选择的属性名有时更愿意使用自己选择的属性名改改 例例8.2：建立包含电影名和制片人名的视图：建立包含电影名和制片人名的视图 CREATE VIEW MovieProds（movieTitle,prodName）AS SELECT title,name FR

32、OM Movies,MovieExecs WHERE producerC#=cert#视图属性名：视图属性名：movieTitle,prodName第93页/共102页94例例 数学系选了数学系选了1 1号课程号课程且分数在且分数在9090以上以上 的学生视图的学生视图 CREATE VIEW MA_S2 AS SELECT Sno,Sname,Grade FROM MA_S1 WHERE Grade=90 视图的视图视图的视图 第94页/共102页95例：定义一个反映学生出生年份的视图例：定义一个反映学生出生年份的视图 CREATE VIEW BT_S (Sno,Sname,Sbirth)A

33、S SELECT Sno,Sname,2010-Sage FROM Students 第95页/共102页96例：将学生的学号和例：将学生的学号和各科的各科的平均成绩定义平均成绩定义 为一个视图为一个视图 CREATE VIEW S_G (Sno,Gavg)AS SELECT Sno,AVG(Grade)FROM SC GROUP BY Sno第96页/共102页97例：例：CREATE VIEW F_Students(stdnum,name,sex,age,dept)AS SELECT *FROM Students WHERE Ssex=女女 依当前基本表生成视图定义，基本表改变则不依当前基

34、本表生成视图定义，基本表改变则不能正常工作能正常工作第97页/共102页98法法1 1：CREATE VIEW F_Students (stdnum,name,sex,age,dept)AS SELECT Sno,Sname,Ssex,Sage,Sdept FROM Students WHERE Ssex=女女 法法2：删除旧视图，重建。删除旧视图，重建。第98页/共102页99 DBMS 执行视图查询时，执行视图查询时，首先进行有效性检查，检查涉及的表、视图是否首先进行有效性检查，检查涉及的表、视图是否存在，存在，如存在从系统表中取出视图定义，如存在从系统表中取出视图定义，把定义中的把定义中

35、的SELECT语句与对视图进行的查询的语句与对视图进行的查询的SELECT语句结合起来，转换成对基本表的查询，语句结合起来，转换成对基本表的查询，然后，再执行这个修正后的查询。然后，再执行这个修正后的查询。第99页/共102页100例：查询数学系年龄小于例：查询数学系年龄小于20 的学生的学号的学生的学号 和年龄和年龄 SELECT Sno,Sage FROM MA_Students WHERE Sage20 过程过程第100页/共102页101P203 例例8.4 可以写出涉及基本表和视图的查询可以写出涉及基本表和视图的查询 SELECT DISTINCT starName FROM ParamountMovies,StarsIn WHERE title=movieTitle AND year=movieYear第101页/共102页102感谢您的观看！第102页/共102页