ch3 关系数据库语言ppt课件.ppt

第3章 关系数据库语言,中国铁道出版社,数据库技术及应用贾铁军主编 沈学东副主编刘泓漫 熊玉梅 闫俊英编著,高等应用型院校计算机教育规划教材,上海市精品课程“数据库原理及应用”,目 录,重点,3.1.1 SQL的基本概念,SQL语言支持数据库三级模式结构，其中外模式对应视图，模式对应基本表，内模式对应存储文件。如图3-1所示。 1. 基本表（Base Table） 基本表是模式的基本内容。它是实际存储在数据库中的表，是独立存在的，不是由其它的表导出的表。一个基本表对应一个实际存在的关系。关系模型中的元组在基本表中称为行，属性称为列； 2. 视图（View） 视图是外模式的基本单位。是从基本表或其他视图导出的一虚表，用户可以通过视图使用数据库中基于基本表的数据。视图本身不独立存储在数据库中，数据库把视图以一种逻辑定义形式保存在数据字典里。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。,要点复习,姓名,性别,学号,3.1.1 SQL的基本概念,3. 存储文件 存储文件的逻辑结构组成了关系数据库的内模式。一个基本表可以跨几个存储文件，一个存储文件也可存放几个基本表。一个基本表可以有若干个索引，索引同样存放在存储文件中.存储文件的存储结构对用户来说是简捷的.每个存储文件与外部存储器上一个物理文件对应。 4. SQL用户 SQL用户可以是终端用户，也可以是应用程序。用户可用SQL语句对视图和基本表进行查询等操作。,课堂讨论（1）什么是基本表？（2）什么是视图？（3）什么是存储文件？（4）SQL语言有哪些特点？,3.1.2 SQL的主要特点,语言简洁，易学易用功能强、语法简单.核心数据定义、操纵、控制9动词.2. 高度非过程化非过程语言,只提要求及数据,无需知道怎么做及存储等.3. 面向集合(语言；操作对象及结果都是关系)4. 功能一体化(定义语言,查询语言,操纵语言,控制语言)5. 用同一种语法结构提供两种使用方式(自含式-嵌入式),Create,drop,select,insert,updateDelete,grant授权,revoke取消,3.2.1创建和删除SQL模式,1数据库的创建 基本表的集合被定义为一个SQL模式，即数据库模式。一个SQL模式由模式名和模式拥有者的用户名或帐号来确定，SQL模式包含了这个模式中所有元素的定义。创建了一个SQL模式，就是定义了一个存储空间，其语法如下： CREATE SCHEMA AUTHORIZATION 【案例3-1】定义商品销售数据库的SQL模式,模式拥有者为李平. CREATE SCHEMA 商品销售 AUTHORIZATION 李平【注意】模式中的元素是指：基本表、视图、索引等。 大多数系统将“SQL模式”成为“数据库”，语句采用“SCHEMA”改为“DATABASE”。,DATABASE,符号,/,数据库,3.2.1创建和删除SQL模式,2SQL模式的删除 当一个SQL模式及其中的所有元素都不需要时，可以删除这个SQL模式。其语法如下： DROP SCHEMA CASCADE|RESTRICT 删除方式有两种：CASCADE(级联式)方式：执行DROP语句时，把SQL模式及其下属的所有元素全部撤消.这种删除不可恢复,使用要慎重。RESTRICT(约束式)方式：执行DROP语句时，当SQL模式中有下属元素时，拒绝执行DROP语句，即只有SQL模式下没有下属元素时才能撤消。此为SQL模式删除的默认参数选项.【案例3-2】撤消SQL模式商品销售及其下属所有的元素。 DROP SCHEMA 商品销售 CASCADE,DATABASE,DATABASE,修改ALTER,3.2.2 数据类型,3.2.3创建、修改和删除基本表(结构),1.创建基本表 创建基本表就是定义基本表的结构，SQL语言使用CREATE TABLE语句定义基本表结构。其一般格式如下：CREATE TABLE （ 列完整性约束， 列完整性约束2， 表级完整性约束）其中：基本表名是指所定义的基本表的名字，可以由一个或多个属性组成，在一个数据库中不允许有两个基本表同名。 列名是指该列（属性）的名称。一个表中不能有两列同名。 列数据类型是指该列的数据类型。 列完整性约束是指针对该列设置的约束条件。,USE 数据库名,属性名,3.2.3创建、修改和删除基本表,SQL的列级完整性约束条件最常见的有以下4种。（1）NOT NULL、NULL约束 NOT NULL是指该列值不能为空。NULL是指该列值可以为空。（2）UNIQUE约束 UNIQUE是唯一性约束，是指该列中不能存在重复的属性值。（3）DEFAULT约束 DEFAULT约束是默认约束，是指该列某值在未定义时的默认值。（4）CHECK约束 CHECK约束是检查约束，该约束通过约束条件表达式设置列值应该满足的条件。 表级完整性约束是规定了关系的主键、外键和用户自定义完整性约束。,3.2.3创建、修改和删除基本表,【案例3-3】商品销售数据库中基本表的关系模式如下所示,用SQL语言定义这3个表。商品（商品编号，商品名，产地，价格，等级）；售货员（售货员编号，姓名，性别，年龄）；售货（商品编号，售货员编号，数量）。CREATE TABLE 商品（商品编号 CHAR（4），NOT NULL UNIQUE,商品名 VARCHAR（50） NOT NULL，产地 VARCHAR（50） NULL，价格 REAL NOT NULL，等级 CHAR（6） NULL，）；,USE 商品销售GO,3.2.3创建、修改和删除基本表,CREATE TABLE 售货员 （售货员编号 CHAR（3）， 姓名 VARCHAR（50）NOT NULL， 性别 CHAR（2） NOT NULL， 年龄 SMALLINT， PRIMARY KEY（售货员编号）；CREATE TABLE 售货 （商品编号 CHAR（4）， 售货员编号 CHAR（3）， 数量 INT， PRIMARY KEY（商品编号，售货员编号）， FOREIGN KEY (商品编号) REFERENCES 商品（商品编号）， FOREIGN KEY(售货员编号)REFERENCES 售货员（售货员编号）, ）；,主键（唯一性）,3.2.3创建、修改和删除基本表,2.修改基本表基本表建立后，如果实际需要发生改变，可对基本表结构进行修改，即增加新的列、删除原有的列和修改原有列的类型。其一般格式如下： ALTER TABLE ADD 列完整性约束 DROP COLUMN CASCADE|RESTRICT MODIFY 其中,ADD表示增加新的列，DROP表示删除原有的列,MODIFY表示修改原有的列。【案例3-4】在售货员基本表中增加一个地址列。 ALTER TABLE 售货员 ADD 地址 VARCHAR(50)； 新增加的属性不能定义为“NOT NULL”.基本表在增加一个属性后,原有元组在新增加的属性列上的值都被定义为空值(NULL)。,3.2.3创建、修改和删除基本表,3基本表的删除当需求发生改变，不再需要数据库某个基本表时，可以将其删除。当一个基本表被删除后，该表中的所有数据连同该表建立的索引都会被删除，而建立在该表上的视图不会随之删除，系统将继续保留其定义，但已无法使用。删除基本表的一般格式如下： DROP TABLE 【案例3-6】 删除商品表。 DROP TABLE 商品,课堂讨论（1）SQL提供的主要数据类型有哪些？ （2）SQL的列级完整性约束条件最常见的有哪几种，并说明它们的含义？（3）举例说明如何删除一个基本表。,3.3 索 引,3.3.1 索引的作用 1. 加快查询速度，这也是创建索引的最主要的原因。 2. 通过创建唯一性索引，可保证表中每一行数据的唯一性。3.3.2 索引的分类 1. 按照索引记录存放位置可分为聚集索引与非聚集索引。 聚集索引确定表中数据的物理顺序.按照索引的字段排列记录,并且依照排好的顺序将记录存储在表中。 非聚集索引按照索引的字段排列记录，数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。 2. 唯一索引 唯一索引表示表中每一个索引值只对应唯一的数据记录，这与表的PRIMARY KEY的特性类似，因此唯一性索引常用于PRIMARY KEY的字段上以区别每一记录。 3. 复合索引的概念 复合索引是指建立在多个列上的索引,单独的字段允许有重复的值.,排序文件,3.3.3创建索引,在SQL语言中,建立索引使用CREATE INDEX语句,一般格式为： CREATE UNIQUECLUSTERINDEX ON ( , );其中： UNIQUE表明建立唯一索引。CLUSTER表示建立聚集索引.次序用来指定索引值的排列顺序,可为ASC（升序)或DESC（降序）,缺省值为ASC。 【案例3-7】商品表的属性列商品编号上创建一个非聚集索引 CREATE INDEX 商品_编号 ON 商品（商品编号SC）；,3.3.4删除索引,建立索引是为了提高查询速度，但过多或不当的索引会导致系统低效，应删除不必要的索引。 在SQL语言中，删除索引使用DROP INDEX语句,一般格式为： DROP INDEX 索引名【案例3-8】删除表商品的索引商品_编号。 DROP INDEX 商品_编号,课堂讨论（1）索引有什么作用？ （2）什么是唯一索引，它有什么作用？（3）什么是复合索引？,3.4 SQL的数据查询(记录操作),3.4.1 SELECT语句命令的格式SELECT语句命令格式:SELECT ALL|DISTINCT目标表的列名或列表达式1,目标表的列名或列表达式2 FROM 表名或视图名1,表名或视图名2 WHERE 行条件表达式 GROUP BY 列名HAVING 组条件表达式 ORDER BY 列名ASC|DESC,.,属性名,HAVING短语用于GROUP BY 分组中的筛选,如普通行筛选WHERE短语一样。 例:各科平均成绩高于80分(含80分)学生及平均成绩 SELECT 学生姓名, AVG(成绩) as 平均成绩 FROM 成绩表 GROUP BY 学生姓名 HAVING AVG (成绩)=80,3.4.2 SELECT语句中的限定,1.SELECT子句SELECT子句描述的是最终查询结果的表结构和记录。（1）在目标表的列名或列表达式前加DISTINCT，保证输出的结果表中不出现重复元组。（2）列表达式是对一个单列求聚合值的表达式，SQL提供的聚合函数如表3-2所示。列表达式允许出现加减乘除及列名、常数的算术表达式。（3）需要在结果表中显示FROM子句中表或视图的所有列，用*来表示。（4）需要在结果表中输出的列名与基本表或视图的列名不一致,可使用旧名AS新名的形式改名.实际使用时,AS可省略。,3.4.2 SELECT语句中的限定,表3-2 聚合函数,3.4.2 SELECT语句中的限定,【案例3-9】 在商品销售数据库中,查询每一等级的商品数量.在查询结果表中，商品数量显示的列名为数量。 SELECT 等级，COUNT(*)商品名AS 数量 FROM 商品 GROUP BY 等级【案例3-10】查询男售货员卖出的商品编号。 SELECT DISTINCT 售货.商品编号 FROM 售货员,售货 WHERE 售货员.售货员编号=售货.售货员编号 AND 售货员.性别=男,等值连接,3.4.2 SELECT语句中的限定,2.同一个基本表在 FROM子句中多次引用时可用AS来增加别名。3.WHERE子句（1）行条件表达式可以使用BETWEEN.AND.来限定一个值的范围，也可以使用算术比较运算符。（2）行条件表达式中字符串匹配用LIKE操作符，在表达式中可使用两个通配符： 百分号( % ):表示可与任意长度(可为零)的字符串匹配. 下划线( _ ): 表示可与任意单个字符匹配.（3）查询空值操作使用 IS NULL和IS NOT NULL。,3.4.2 SELECT语句中的限定,【案例3-11】 查询价格在10元到100元之间的商品信息。SELECT *FROM 商品WHERE 价格 BETWEEN 10 AND 100【案例3-12】 查询姓李的售货员的售货员编号和姓名。SELECT 售货员编号，姓名FROM 售货员WHERE 姓名 LIKE李%【案例3-13】 查询产地不为空的商品信息。SELECT *FROM 商品WHERE 产地 IS NOT NULL,3.4.3 SELECT查询语句,这一节中所有的例子所涉及到的表均来源于商品销售数据库的三个基本表：商品（商品编号，商品名，产地，价格，等级）；售货员（售货员编号，姓名，性别，年龄）；售货（商品编号，售货员编号，数量）。【案例3-14】查询年龄是大于25岁售货员的信息。 SELECT * FROM 售货员 WHERE 年龄25;【案例3-15】查询商品的产地。 SELECT DISTINCT 产地 FROM 商品,3.4.3 SELECT查询语句,【案例3-16】查询销售了商品编号为“G004”商品的售货员的编号和销售数量，并按数量降序排列。SELECT 售货员编号，数量FROM 售货WHERE 商品编号=“G004”ORDER BY 数量 DESC【案例3-17】查询所有售货员的编号、姓名和出生年份。 SELECT 售货员编号,姓名,2012-年龄 AS 出生年份 FROM 售货员 其中，查询结果包括售货员的出生年份，而在商品销售数据库中并没有售货员出生年份的属性，可通过当今年份减去售货员的年龄得到。【案例3-18】 查询售货员李平所销售过的商品的编号和商品名。 SELECT 商品.商品编号，商品名 FROM 商品，售货员，售货 WHERE商品.商品编号=售货.商品编号 AND 售货员.售货员编号=售货.售货员编号 AND 姓名=李平 其中，由于商品和售货两个表均有商品编号属性，为了明确表示属性的来源，在属性前面加上属性所属的基本表名,如商品.商品编号.,等值连接,3.4.3 SELECT查询语句,【案例3-19】 查询销售过编号为G001或G002商品的售货员的编号。SELECT 售货员编号FROM 售货WHERE 商品编号=G001UNIONSELECT 售货员编号FROM 售货WHERE 商品编号=G002 注查询结果的结构完全一致时可将两个查询进行并(UNION)、交(INTERSECT)、差(EXCPT)操作。【案例3-20】查询产地为北京或上海的商品信息。SELECT *FROM 商品WHERE 产地 IN (北京,上海) 其中，使用了一个特殊运算符IN，表示判断属性值是否在一个集合内。NOT IN表示判断属性值是否不在一个集合内。,法二 or 商品编号=G002,3.4.3 SELECT查询语句,【案例3-21】查询销售了电话的售货员的编号和相应的销售数量。SELECT 售货员编号, 数量 FROM 售货 WHERE 商品编号=（SELECT 商品编号 FROM 商品 WHERE 商品名=电话） 在其中使用了SELECT语句的嵌套查询。嵌套查询是指一个SELECT语句中嵌入在另一个SELECT语句的WHERE子句中的查询.外层查询称为父查询，内层查询称为子查询。子查询可以将一系列简单的查询组合成复杂的查询。法二该例也可通过以下语句实现：SELECT 售货员.售货员编号，数量 FROM 商品，售货 WHERE 商品.商品编号=售货.商品编号AND 商品名=电话,3.4.3 SELECT查询语句,【案例3-22】查询至少比的一个售货员销售G002商品的销售数量多的售货员的编号。 SELECT 售货员编号 FROM 售货 WHERE 数量 SOME（SELECT 数量 FROM 售货 WHERE 商品编号 =G002）AND 商品编号【案例3-23】查询比每个售货员销售G002商品的销售数量多的售货员的编号。 SELECT 售货员编号 FROM 售货 WHERE 数量 ALL（SELECT 数量 FROM 售货 WHERE 商品编号 =G002）AND 商品编号 在两例中，SOME运算符表示某一,ALL运算符表示所有或每个。,3.4.3 SELECT查询语句,【案例3-24】查询销售了电话的售货员的编号。 SELECT 售货员编号FROM售货WHERE EXISTS（SELECT *FROM 商品WHERE 商品编号=售货.商品编号 AND 商品名=电话）【案例3-25】查询没有销售电话的售货员的编号。 SELECT 售货员编号FROM售货WHERE NOT EXISTS（SELECT *FROM 商品WHERE商品编号=售货.商品编号 AND 商品名=电话） 注在子查询中常用EXISTS存在谓词。若含有此谓词不返回任何结果,只得到“真”/“假”.使用此量词后，若内层查询结果不空,则外层的WHERE子句返回真,否则返回假;使用存在量词NOT EXISTS后,若内层查询结果为空,则外层的WHERE子句返回真值,否则返回假值.,课堂讨论,（1）在什么情况下使用HAVING子句？ （2）SQL提供的哪些聚合函数？它们的含义是什么？（3）SQL语言如何实现关系代数中的并运算？,3.5.1插入数据,1.插入元组可以向指定表中插入一条或几条新元组，将新元组插入指定表的一般格式为：INSERT INTO(，) VALUES（，） ,(，）,;,插入的对应值,插入的属性名,3.5.1插入数据,【案例3-26】向基本表商品中插入一个元组(G006,键盘 ,广东,127)INSERT INTO 商品(商品编号,商品名,产地，价格) VALUES(G006，键盘,广东，127);【案例3-27】向基本表售货员中插入三个元组(T05，李平,女，23)，(T06，柳梅,女，21)，(T07，杨力,男，25)INSERT INTO 售货员 VALUES(T05，李平,女，23)， (T06，柳梅,女，21)， (T07，杨力,男，25);,插入的属性名(略),3.5.1插入数据,2.插入查询结果可把SELECT语句的查询结果插到指定表中，插入查询结果一般格式:INSERET INTO (，) 子查询;【案例3-28】如果已建有销售统计表销售_统计(销售员编号,销售总量)其中销售总量表示每个销售员销售商品的总数量，向销售_统计表中插入每个销售员的销售总量。 INSERT INTO 销售_统计（销售员编号,销售总量） SELECT 销售员编号 ,COUNT（数量） FROM 销售 GROUP BY 销售员编号,3.5.2修改数据,数据可以根据需要进行修改，修改操作通过UPDATE语句实现，其语句的一般格式为： UPDATE SET = ， = WHERE 其中，SET子句用于指定修改值，即用表达式的值取代相应的属性列值。该语句实现修改指定表中满足WHERE条件的元组。如果省略WHERE子句，则表示要修改表中的所有元组。【案例3-29】将商品编号为G003的商品价格提高1。 UPDATE 商品 SET 价格=价格*1.01 WHERE 商品编号=G003;,3.5.3删除数据,删除语句的一般格式为： DELETE FROM WHERE 该语句实现删除指定表中满足WHERE条件的元组。如果省略WHERE子句，则表示要删除表中的所有元组。DELETE语句只能从一个关系中删除元组，而不能一次从多个关系中删除元组。要删除多个关系的元组，就要写多个DELETE语句。【案例3-30】 删除售货员编号为T02的售货记录。 DELETE FROM 售货 WHERE 售货员编号=T02;,（1）SQL中数据更新包括哪些操作？（2）如何将查询结果插入到基本表中？ （3）如何使用DELETE语句删除符合一定条件的元组？,3.6.1创建和删除视图,1视图的创建在SQL语言中，使用CREATE VIEW语句创建视图，一般格式为： CREATE VIEW （，） AS（子查询） WITH CHECK OPTION其中，列名用于指定创建的视图所包含的属性，如果视图的列名与子查询SELECT子句里的各列名完全相同时，列名序列可以省略。在子查询中禁止使用DISTINCT短语和ORDER BY子句。WITH CHECK OPTION 子句将约束通过视图更新表，拒绝那些不符合视图定义中WHERE子句里限定的条件的更新数据。,姓名,性别,学号,3.6.1创建和删除视图,【案例3-31】对商品销售数据库中，经常用到有关商品情况信息：商品编号、商品名、价格等数据，请用SQL语句创建商品_价格视图。 CREATE VIEW 商品_价格 AS SELECT (商品编号,商品名，价格) FROM 商品【案例3-32】对商品销售数据库，经常用到有关商品销售情况信息：商品编号、商品名、销售数量等数据,用SQL语句创建商品_销售量视图. CREATE VIEW 商品_销售量(商品编号，商品名，销售数量) AS SELECT (商品.商品编号,商品名，COUNT(数量) FROM 商品，售货 WHERE 商品.商品编号=售货.商品编号 GROUP BY 商品编号；,3.6.1创建和删除视图,2视图的删除在SQL语言中，使用DROP VIEW语句删除视图，其一般格式为： DROP VIEW 【案例3-33】删除商品_销售量视图。 DROP VIEW 商品_销售量；,3.6.2更新视图,使用视图修改数据时，需要注意以下几点：（1）如果视图是从多个基本表使用连接操作导出的，那么不允许对该视图进行更新操作。（2）如果视图包含通过计算得到的字段，如计算值或者聚合函数的字段，那么不允许对该视图进行更新操作。（3）如果定义视图时加上WITH CHECK OPTION子句，在视图上更新数据时，系统会进一步检查视图定义中的条件，若不满足条件，则拒绝执行该操作。,3.6.2更新视图,【案例3-34】 向商品_价格视图插入数据(G010，台灯，117) INSERT INTO 商品_价格 Values(G010，台灯，117)【案例3-35】 将商品_价格视图中商品编号为G001的商品价格改为55。 UPDATE 商品_价格 SET 价格 = 55 WHERE 商品编号 =G001 【案例3-36】在商品_价格视图中删除商品编号为G002的商品 DELETE * FROM 商品_价格 WHERE商品编号 =G002,3.6.3查询视图,视图定义后，可以使用视图进行查询。由于视图是虚表，系统执行对视图的查询时，把用户对视图的查询和视图定义中的子查询结合起来，转换成对基本表的查询。【案例3-37】查找商品键盘的销售量。SELECT 销售数量 FROM 商品_销售量 WHERE 商品名=键盘如果对一个基本表的查询较为复杂时，可以通过对基本表建立一个视图，然后对此视图进行查询的方式来接近。这样就可以将一个复杂的查询转换成创建一个视图和一个简单查询，从而简化了操作。,（1）举例说明如何使用SQL语言创建视图？ （2）使用视图修改数据时，需要注意什么？（3）使用视图进行查询有什么优点？,*3.7 嵌入式SQL,嵌入式SQL的语法有以下规定：(1) 每条嵌入式SQL语句都用EXEC SQL开始，表明它是一条SQL语句，以“；”为结束标志。告诉预编译器在EXEC SQL和“；”之间是嵌入式SQL语句。格式如下： EXEC SQL ；【案例3-38】 用嵌入式SQL完成将商品编号为G003的商品价格提高1。 EXEC SQL UPDATE 商品 SET 价格=价格*1.01 WHERE 商品编号=G003；(2) 如果一条嵌入式SQL语句占用多行，在C程序中可以用续行符“”，在Fortran中必须有续行符。其他语言也有相应规定。,*3.7 嵌入式SQL,（3）允许在嵌入SQL中引用宿主语言的程序变量。 （4）处理多条记录可以使用游标。在嵌入式SQL中使用游标的规则与交互式SQL中基本一致，只要在语句前面加EXEC SQL前缀即可。（5）必须解决数据库工作单元与程序工作单元之间的通信问题.,课堂讨论（1）SQL语言提供了哪两种使用方式？（2）什么是嵌入式SQL？ （3）嵌入式SQL的语法有哪些规定？,3.8 小结,结构化查询语言SQL具有语言简洁、易学易用、高度非过程化、一体化等特点，是目前广泛使用的数据库标准语言。本章详细介绍了数据定义、数据查询、数据更新、索引、视图和嵌入式SQL等内容，并通过实例说明了SQL语言的具体使用方法。,数 据 库 技 术 及 应 用,诚挚谢意！,