数据库系统讲义第3章 - 4


哈尔滨工业大学 软件学院 《数据库系统》 数据库系统 哈尔滨工业大学 软件学院 《数据库系统》 第3章 SQL语言 哈尔滨工业大学 软件学院 《数据库系统》 内容回顾 查询结构 Select 表达式 [, 子查询…] From 表名 [, 子查询 …] [Where 检索条件 ] [Group by 分组方式] [Having 过滤条件] [Order by 排序方式]  表达式 + (not)in + 子查询  表达式 +  some/all+ 子查询  (not)exists + 子查询 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 分组查询与分组过滤(续)  例如: 查询平均分高于80的课程号、课程名及平均分 Select C#, Cname, AVG(Score) From SC, Course Group by C# Having AVG(Score)>80 不正确 正确写法: Select SC.C#, Cname, AVG(Score) From SC, Course Where SC.C#=Course.C# Group by SC.C#,Cname Having AVG(Score)>80 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 分组查询与分组过滤(续)  关于count的使用 Select Count(*) from Table Select Count(S#) from (Select S# from SC where C# ='001') A Select Count(Distinct 列名) from Table Select Count(列名) from Table 返回表中的记录数 返回指定列的值的数目(去掉了空值) 返回指定列非重复值的数目(去掉了重复值) 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 分组查询与分组过滤(续)  例如:求有两门以上不及格课程的同学的学号及该同学平均成绩 Select S#, Avg(Score) From SC Where Score < 60 Group by S# Having Count(*)>2; 不正确  正确写法: Select S#, AVG(Score) From SC Where S# in ( Select S# From SC Where Score < 60 Group by S# Having Count(*)>2 ) Group by S# ; 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 并、交、差的处理 SQL语言中:  并运算运算符UNION  交运算运算符INTERSECT  差运算运算符EXCEPT 其基本语法形式为: 子查询 { Union [ALL] | Intersect [ALL] | Except [ALL] 子查询 }  ALL关键字: 结果集中保留重复元组,具体使用如下:  若子查询1的一个元组出现m次,子查询2的一个元组出现n次,则: 子查询1 Union ALL 子查询2 ,结果集中出现m + n次 子查询1 Intersect ALL 子查询2 ,结果集中出现min(m,n)次 子查询1 Except ALL 子查询2 ,结果集中出现max(0, m – n)次 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 并、交、差的处理(续)  SQL并运算示例:  求学过002号课的同学或学过003号课的同学学号 Select S# From SC Where C# = ‘002’ UNION Select S# From SC Where C# = ‘003’;  不用UNION的方式 Select S# From SC Where C# = ‘002’ OR C# = ‘003’; 有些查询请求不能转换成不用UNION的方式 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 并、交、差的处理(续)  SQL交运算示例:  求既学过002号课,又学过003号课的同学学号 Select S# From SC Where C# = ‘002’ INTERSECT Select S# From SC Where C# = ‘003’; 上述语句也可采用如下不用INTERSECT的方式来进行 Select S# From SC,SC SC1 Where SC.S#=SC1.S# and SC.C# = ‘002’ and SC1.C# = ‘003’; Select S# From SC Where C# = ‘002’ and S# IN (Select S# From SC Where C# = ‘003’); 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 并、交、差的处理(续) SQL差运算示例:  假定所有的同学都有选课,求没学过002号课程的学生学号  可写成如下形式:所有学生  学过002号课的学生 Select DISTINCT S# From SC EXCEPT Select S# From SC Where C# = ‘002’; 不用EXCEPT的方式来进行 Select DISTINCT S# From SC SC1 Where not exists ( Select * From SC Where C# = ‘002’ and S# = SC1.S#) ; 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 空值处理  空值代表不知道、不确定、不存在的值 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 空值处理(续)  在SQL标准中和许多现在流行的DBMS中,空值被用一种特殊的符号Null 来标记,使用特殊的空值检测函数来获得某列的值是否为空值。  空值检测 列名 is [not ] null 测试指定列的值是否为空值 例如:找出年龄值为空的学生姓名 Select Sname From Student Where Sage is null ; 注意:上例条件不能写为Where Sage = null 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 空值处理(续) 空值:数据库中用null表示,代表“不知道”或“不存在”  现行DBMS的处理空值的一般方法 (1)null参与的算术运算,结果为null (2)null参与的逻辑运算,结果为 T,F, unknown  T  null= unknown;F  null= F;null  null= unknown  T  null=T; F  null=unknown; null  null=unknown   null=unknown (3)null参加的聚集运算,除count(*)外其它聚集函数都忽略null 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 空值处理(续)  例如: Select AVG(Score) From SC; 结果为73.5 = (92 + 55)/2。  再例如: Select COUNT(*) From SC; 结果为3。 Select COUNT(Score) From SC; 结果为2。 哈尔滨工业大学 软件学院 《数据库系统》  例:查询学生的学号、姓名及对应的选课数量 Select Student.S#, Student.Sname, Count(*) From Student, SC Where Student.S# = SC.S# Group by Student.S#, Student.Sname; Select S#, Sname, (Select count(*) From SC Where SC.S#= Student.S#) From Student; 3.3 SQL-DML之查询Select ---- 空值处理(续) 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 内连接、外连接  关系代数运算中的连接运算  连接(自然连接)  外连接  标准SQL语言中连接运算通常是采用 Select 列名 [[, 列名]…] From 表名1, 表名2,… Where 检索条件 ;  即相当于采用 列名, … , 列名( 检索条件 (表名1  表名2  …))。 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 内连接、外连接(续)  在SQL的高级语法中引入了自然连接、内连接与外连接运算, 具体形式如下: Select 列名 [[, 列名]…] From 表名1 [NATURAL] [ INNER | { LEFT | RIGHT | FULL} [OUTER]] JOIN 表名2 { ON 连接条件 | Using (列名{, 列名…}) } 上例的连接运算由两部分构成:连接类型和连接条件 连接类型(四者选一) inner join left outer join right outer join full outer join 连接条件(三者选一) natural on <连接条件> using (Col1, Col2, ..., Coln) X 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 内连接、外连接(续)  Inner Join: 即关系代数中的-连接运算  Left Outer Join, Right Outer Join, Full Outer Join: 即关系代数中的外连 接运算  如“表1 Left Outer Join 表2”,则连接后,表1的任何元组t都会出现在 结果表中,如表2中有满足连接条件的元组s, 则t与s连接;否则t与空值元 组连接;  如“表1 Right Outer Join 表2”,则连接后,表2的任何元组s都会出现 在结果表中,如表1中有满足连接条件的元组t, 则t与s连接;否则s与空值 元组连接;  如“表1 Full Outer Join 表2”,是前两者的并。 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 内连接、外连接(续) 连接中使用 natural  出现在结果关系中的两个连接关系的元组在公共属性上取值相等,且 结果集去掉重复的公共属性 连接中使用 on <连接条件>  出现在结果关系中的两个连接关系的元组取值满足连接条件,且结果 集中公共属性出现两次 连接中使用 using (Col1, Col2,..., Coln)  (Col1, Col2,..., Coln)是两个连接关系的公共属性的子集,元组在(Col1, Col2,..., Coln)上取值相等,且结果集中(Col1, Col2,..., Coln)只出现一次 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 内连接、外连接(续)  Inner Join示例: 求所有教师的任课情况并按教师号排序 Select Teacher.T#, Tname, Cname From Teacher Inner Join Course ON Teacher.T# = Course.T# Order by Teacher.T# ASC; 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select ---- 内连接、外连接(续)  Outer Join示例: 求所有教师的任课情况(没有任课的教师也需列在表中) Select Teacher. T#, Tname, Cname From Teacher Left Outer Join Course ON Teacher.T# = Course.T# Order by Teacher.T# ASC ; 哈尔滨工业大学 软件学院 《数据库系统》 3.3 SQL-DML之查询Select Select [Distinct] 列表达式 [别名] [ , 列表达式 [别名]] … [, (Selcet子查询) [as 别名]] … From 表名[别名][,表名 别名] … [,(Select子查询)[as 别名]] … {表名[别名]join[表名 别名] on 连接条件…..} //高级SQL [Where 条件表达式 [and(or) 条件表达式] … [Group by [列名][,列名] … [Having 分组过滤条件]] [Order by [列名] [ASC|DESC]]; ---- Select语句的一般形式 哈尔滨工业大学 软件学院 《数据库系统》 列表达式格式 (1)* (2) <表名>.* (3) Count(*|列名) (4)[表名.] 属性列表达式 属性列表达式:  常量  由常量、列名、特殊函数及算术运算符构成的算术运算式 3.3 SQL-DML之查询Select ---- Select语句的一般形式:目标列表达式 哈尔滨工业大学 软件学院 《数据库系统》 属性列名 属性列名 θ 常量 [some|all] (Select子查询) (1)属性列表达式 θ (值1,值2,…) (Select子查询)(2)属性列表达式 (not)in (3)属性列名 [not] like 匹配字符串 (4)属性列名 is [not] Null (5)[not] Exists (Select子查询) ---- Select语句的一般形式:条件表达式 3.3 SQL-DML之查询Select 哈尔滨工业大学 软件学院 《数据库系统》 第3章 SQL语言 3.1 SQL语言概述 3.2 简单的SQL-DDL/DML: 创建数据库 3.3 SQL-DML之查询Select 3.4 SQL-DML之更新Insert/Update/Delete ---- 元组新增操作:Insert ---- 元组更新操作:Update ---- 元组删除操作: Delete 3.5 SQL-视图及DDL的进一步介绍 3.6 SQL Server及其交互式环境简介 哈尔滨工业大学 软件学院 《数据库系统》 3.4 SQL-DML之更新Insert/Update/Delete ---- SQL-DML之元组新增Insert  元组新增Insert命令有两种形式  插入元组 insert into 表名 [(列名[,列名]…)] values (值 [,值]…) [,(值 [,值]…)] … [,(值 [,值]…) ];  插入子查询结果 insert into 表名 [(列名[,列名]…)] 子查询; 哈尔滨工业大学 软件学院 《数据库系统》 3.4 SQL-DML之更新Insert/Update/Delete ---- SQL-DML之元组新增Insert(续)  新增元组示例 Insert Into Teacher (T#, Tname, D#, Salary) Values (“005”, “李明”, “03”, “3000”) , (“006”, “王芳”, “04”, “3500”) ;  插入子查询结果 Insert Into St (S#, Sname, avgScore) Select Student.S#, Sname, Avg(Score) From Student, SC Where Student.S# = SC.S# Group by Student.S#, Sname ; 哈尔滨工业大学 软件学院 《数据库系统》 3.4 SQL-DML之更新Insert/Update/Delete ---- SQL-DML之元组删除Delete  元组删除Delete命令: 删除满足指定条件的元组 Delete From 表名 [ Where 条件表达式] ;  如果Where条件省略,则删除所有的元组。  元组删除Delete示例 删除SC表中所有元组 Delete From SC ; 删除98030101号同学所选的所有课程 Delete From SC Where S# = ‘98030101’ ; 哈尔滨工业大学 软件学院 《数据库系统》 3.4 SQL-DML之更新Insert/Update/Delete ---- SQL-DML之元组删除Delete(续)  删除自动控制系的所有同学 Delete From Student Where D# in ( Select D# From Dept Where Dname = ‘自动控制’);  删除有四门不及格课程的所有同学 Delete From Student Where S# in ( Select S# From SC Where Score < 60 Group by S# Having Count(*)>= 4 ); 哈尔滨工业大学 软件学院 《数据库系统》 3.4 SQL-DML之更新Insert/Update/Delete ---- SQL-DML之元组更新Update  元组更新Update命令: 用指定要求的值,更新指定表中满足指定条件的 元组的指定列的值 Update 表名 Set 列名 = 表达式 | (子查询) [ [ , 列名 = 表达式 | (子查询) ] … ] [ Where 条件表达式] ;  如果Where条件省略,则更新所有的元组。 哈尔滨工业大学 软件学院 《数据库系统》 3.4 SQL-DML之更新Insert/Update/Delete ---- SQL-DML之元组更新Update(续)  元组更新Update示例  将所有教师工资上调5% Update Teacher Set Salary = Salary * 1.05 ;  将所有计算机系的教师工资上调10% Update Teacher Set Salary = Salary * 1.1 Where D# in ( Select D# From Dept Where Dname = ‘计算机’); 哈尔滨工业大学 软件学院 《数据库系统》 3.4 SQL-DML之更新Insert/Update/Delete ---- SQL-DML之元组更新Update(续)  将张三同学001号课的成绩置为该门课的平均成绩 Update SC Set Score = ( Select AVG(SC1.Score) From SC SC1 Where SC1.C# = ‘001’ ) Where C# = ‘001’ and S# in ( Select S# From Student Where Sname = ‘张三’ ) ; 哈尔滨工业大学 软件学院 《数据库系统》 第3章 SQL语言 3.1 SQL语言概述 3.2 简单的SQL-DDL/DML: 创建数据库 3.3 SQL-DML之查询Select 3.4 SQL-DML之更新Insert/Update/Delete 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的概念 ---- 视图的定义与使用 ---- 视图更新问题 ---- SQL-DDL的进一步介绍: 撤消语句的使用 3.6 SQL Server及其交互式环境简介 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的概念 (续)  已经学习的表的类型:基本表,派生表(临时表)  视图:从一个或几个基本表或视图导出的表 (虚表)  只存储视图的定义(导出视图所需要的公式),不存储视图中的数据  在查询中,视图定义后可以象基本表一样使用 视图的优点:  只显示对用户有用的数据  保证数据安全  隐藏表与表之间的复杂性  保证一定的逻辑独立性 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- 视图属于外模式 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的定义续)  视图支持的操作  Create,Alter,Drop  Select  insert,update,delete (更新受限)  定义视图 create view view_name [(列名[,列名] …)] as 子查询 [with check option]  如果视图的属性名缺省,则默认为子查询结果中的属性名  with check option指明当对视图进行insert,update,delete时,要满 足视图定义中子查询的条件 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的定义  再例如:建立计算机系的学生的视图 Create View CompStud AS ( Select * From Student Where D# in ( Select D# From Dept Where Dname = ‘计算机’ ) ; 例如:建立教师授课情况的视图 Create View Teach AS ( Select T.Tname, C.Cname, Credit From Teacher T, Course C Where T.T# = C.T# ) ; 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的使用  例如,检索计算机系20岁以下的所有学生,可使用CompStud Select Sname From CompStud where Sage < 20; Select Sname From ( Select * From Student Where D# in ( Select D# From Dept Where Dname = ‘计算机’)) where Sage < 20;  定义好视图后,视图可以像Table一样用来查询  视图消解:涉及到视图的查询将会被重写,用视图的定义代替视图 重写后的查询仍然不是DBMS真正处理的查询 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的使用(续) 例如: 先定义一个视图StudStat, 描述学生的平均成绩、最高成绩,最低成绩等 Creat View StudStat(S#, Sname, AvgS, MinS, MaxS) as ( Select S.S#, Sname, AVG(Score), MIN(Score), Max(Score) From Student S, SC Where S.S# = SC.S# Group by S.S#, Sname) ; 在定义了视图StudStat后,再检索某一学生平均成绩就很简单了: Select Sname, AvgS From StudStat Where Sname = ‘张三’ ;  定义视图可以简化查询  定义视图可以保证逻辑独立性 (参见教材128页中对student表分解后创建视图的例子) 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的更新  不保存视图数据,对视图的更新最终要反映到对基本表的更新  视图上的更新将会被“重写”为基本表上的更新 例如: create view S_G(S#, Savg ) as ( select S#, AVG(Score) from SC group by S# ); 如要进行下述更新操作? update S_G set Savg = 85 where S# = ‘98030101’ ; 能否通过更新视图S_G,而更新SC? 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的更新  再例如: create view ClassStud(Sname, Sclass) as ( select Sname, Sclass from Student ); 如要进行下述更新操作? Insert into ClassStud Values ( ‘张三’, ‘980301’ ); 能否由视图ClassStud的更新,而更新Student呢? 不能,因为缺少S# , 而S#是Student的主键,主键不能为空。 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的更新  因此,SQL视图更新操作受到很大的约束,很多情况是不能进行视图 更新的。  如果视图的select目标列包含聚集函数,则不能更新  如果视图的select子句使用了distinct,则不能更新  如果视图中包括了group by子句,则不能更新  如果视图中包括经算术表达式计算出来的列,则不能更新  如果视图是由单个表的列构成,但并没有包括主键,则不能更新  对于由单一Table子集构成的视图,即如果视图是从单个基本表使用选择、 投影操作导出的,并且包含了基本表的主键,则一般可更新 (行列子集视图) 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的更新  可更新SQL视图示例: create view CStud(S#, Sname, Sclass) as ( select S#, Sname, Sclass from Student where D# =‘03’); insert into CStud values (‘98030104’, ‘张丰’, ‘980301’ ) insert into Student values (‘98030104’, ‘张丰’, Null, Null, ’03’ , ‘980301’ ) 转换为  上例是可以更新的 Insert into CStud Values ( ‘98030104’, ‘张丰’, ‘980301’ ); 哈尔滨工业大学 软件学院 《数据库系统》 3.5 SQL-视图及DDL的进一步介绍 ---- SQL视图的撤消(续)  已经定义的视图也可以撤消  撤消视图 drop view view_name  例如,撤消视图Teach Drop View Teach;  再例如,撤消视图CompStud Drop View CompStud; 哈尔滨工业大学 软件学院 《数据库系统》 3.7 小结 ---- 本章我们学习了以下一些概念  SQL语言基本知识  数据定义语言(DDL)  数据操纵语言(DML)  数据控制语言(DCL)  简单的SQL-DDL  创建操作:Create  修改操作:Alter  撤销操作:Drop Database, Schema, Table, View 对模式(型)的操作 哈尔滨工业大学 软件学院 《数据库系统》 3.7 小结 ---- 本章我们学习了以下一些概念  SQL- DML中的查询语句Select  基本的查询操作  多表联合查询  子查询  结果计算与聚集函数  分组查询与分组过滤  并、交、差的处理  空值处理  内连接、外连接  Select的完整语法 Table, View 对实例(值)的操作 哈尔滨工业大学 软件学院 《数据库系统》 3.7 小结 ---- 本章我们学习了以下一些概念  SQL- DML中的更新操作语句  元组新增操作:Insert  元组更新操作:Update  元组删除操作:Delete  SQL视图  视图的概念  视图的定义  视图的使用  视图的更新 Table, View(限制) 对实例(值)的操作 哈尔滨工业大学 软件学院 《数据库系统》 某公司数据库包含四个关系:  Department (Dept_no, Dept_name, Location), 部门信息表,描述了各部门编号 Dept_no,部门名称Dept_name,以及部门所在地Location。  Employee (Emp_no, Emp_name, Dept_no),员工信息表,描述了员工编号 Emp_no ,员工姓名Emp_name,以及员工所属部门Dept_no。  Project (Pro_no, Pro_name, Budget),项目信息表,描述了项目编号Pro_no,项 目名称Pro_name,以及项目预算金额Budget。  Works (Emp_no, Pro_no, Job),工作信息表,描述了员工与项目的对应关系, Job用于说明员工参与到项目中的工作性质,如分析员、经理、开发人员等。 课堂测试 哈尔滨工业大学 软件学院 《数据库系统》 Dept_no Dept_name 检索参加项目数超过5个的员工编号 SELECT Emp_no FROM Works GROUP BY Emp_no HAVING COUNT(*)>5 针对查询要求写出SQL语句 将预算金额少于100万元项目的预算金额上调10% UPDATE Project SET Budget = Budget *1.1 WHERE Budget <100
还剩48页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

444167415

贡献于2016-07-19

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf