ch3-关系数据库语言

第3章关系数据库语言中国铁道出版社高等应用型院校计算机教育规划教材上海市精品课程“数据库原理及应用”目录3.1SQL的基本概念与特点13.2SQL的数据定义23.3索引33.4SQL的数据查询43.5SQL的数据操纵53.6视图6*3.7嵌入式SQL73.8小结8重点3.1.1SQL的基本概念SQL语言支持数据库三级模式结构，其中外模式对应视图，模式对应基本表，内模式对应存储文件。如图3-1所示。1.基本表（BaseTable）基本表是模式的基本内容。它是实际存储在数据库中的表，是独立存在的，不是由其它的表导出的表。一个基本表对应一个实际存在的关系。关系模型中的元组在基本表中称为行，属性称为列；2.视图（View）视图是外模式的基本单位。是从基本表或其他视图导出的一虚表，用户可以通过视图使用数据库中基于基本表的数据。视图本身不独立存储在数据库中，数据库把视图以一种逻辑定义形式保存在数据字典里。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。要点复习姓名性别学号3.1.1SQL的基本概念3.存储文件存储文件的逻辑结构组成了关系数据库的内模式。一个基本表可以跨几个存储文件，一个存储文件也可存放几个基本表。一个基本表可以有若干个索引，索引同样存放在存储文件中.存储文件的存储结构对用户来说是简捷的.每个存储文件与外部存储器上一个物理文件对应。4.SQL用户SQL用户可以是终端用户，也可以是应用程序。用户可用SQL语句对视图和基本表进行查询等操作。课堂讨论（1）什么是基本表？（2）什么是视图？（3）什么是存储文件？（4）SQL语言有哪些特点？3.1.2SQL的主要特点1.语言简洁，易学易用功能强、语法简单.核心数据定义、操纵、控制9动词.2.高度非过程化非过程语言,只提要求及数据,无需知道怎么做及存储等.3.面向集合(语言；操作对象及结果都是关系)4.功能一体化(定义语言,查询语言,操纵语言,控制语言)5.用同一种语法结构提供两种使用方式(自含式-嵌入式)Create,drop,select,insert,updateDelete,grant授权,revoke取消3.2.1创建和删除SQL模式1．数据库的创建基本表的集合被定义为一个SQL模式，即数据库模式。一个SQL模式由模式名和模式拥有者的用户名或帐号来确定，SQL模式包含了这个模式中所有元素的定义。创建了一个SQL模式，就是定义了一个存储空间，其语法如下：CREATESCHEMA模式名AUTHORIZATION用户名【案例3-1】定义商品销售数据库的SQL模式,模式拥有者为李平.CREATESCHEMA商品销售AUTHORIZATION李平【注意】模式中的元素是指：基本表、视图、索引等。大多数系统将“SQL模式”成为“数据库”，语句采用“SCHEMA”改为“DATABASE”。DATABASE符号,[],/数据库3.2.1创建和删除SQL模式2．SQL模式的删除当一个SQL模式及其中的所有元素都不需要时，可以删除这个SQL模式。其语法如下：DROPSCHEMA模式名[CASCADE|RESTRICT]删除方式有两种：CASCADE(级联式)方式：执行DROP语句时，把SQL模式及其下属的所有元素全部撤消.这种删除不可恢复,使用要慎重。RESTRICT(约束式)方式：执行DROP语句时，当SQL模式中有下属元素时，拒绝执行DROP语句，即只有SQL模式下没有下属元素时才能撤消。此为SQL模式删除的默认参数选项.【案例3-2】撤消SQL模式商品销售及其下属所有的元素。DROPSCHEMA商品销售CASCADEDATABASEDATABASE修改ALTER3.2.2数据类型数值型INTEGER、INT长整数SMALLINT短整数REAL浮点数（取决于计算机精度）DOUBLEPRECISION双精度浮点数（取决于计算机精度）FLOAT（n）浮点数,精度至少为n位数字NUMERIC（p,s）定点数,由p位数字(不包括符号、小数点)组成,小数点后面有s位数字(也可写成DECIMAL(P,s)或DEC(P,s))字符串型CHAR（n）固定长度为n的字符串VARCHAR(n)最大长度为n的变长字符串位串型BIT(n)固定长度为n的二进制位串BITVARYING(n)最大长度为n的变长二进制位串布尔型BOOLEAN其值可以是：TRUE（真）、FALSE（假）。日期时间型DATE用来存储年、月和日的值,表示日期,形式为YYYY-MM-DDTIME用来存储时、分和秒的值,表示时间,形式为HH:MM:SS3.2.3创建、修改和删除基本表(结构)1.创建基本表创建基本表就是定义基本表的结构，SQL语言使用CREATETABLE语句定义基本表结构。其一般格式如下：CREATETABLE基本表名（列名1数据类型1[列完整性约束]，[列名2数据类型2[列完整性约束2]，……][表级完整性约束]）其中：基本表名是指所定义的基本表的名字，可以由一个或多个属性组成，在一个数据库中不允许有两个基本表同名。列名是指该列（属性）的名称。一个表中不能有两列同名。列数据类型是指该列的数据类型。列完整性约束是指针对该列设置的约束条件。USE数据库名属性名3.2.3创建、修改和删除基本表SQL的列级完整性约束条件最常见的有以下4种。（1）NOTNULL、NULL约束NOTNULL是指该列值不能为空。NULL是指该列值可以为空。（2）UNIQUE约束UNIQUE是唯一性约束，是指该列中不能存在重复的属性值。（3）DEFAULT约束DEFAULT约束是默认约束，是指该列某值在未定义时的默认值。（4）CHECK约束CHECK约束是检查约束，该约束通过约束条件表达式设置列值应该满足的条件。表级完整性约束是规定了关系的主键、外键和用户自定义完整性约束。3.2.3创建、修改和删除基本表【案例3-3】商品销售数据库中基本表的关系模式如下所示,用SQL语言定义这3个表。商品（商品编号，商品名，产地，价格，等级）；售货员（售货员编号，姓名，性别，年龄）；售货（商品编号，售货员编号，数量）。CREATETABLE商品（商品编号CHAR（4），NOTNULLUNIQUE,商品名VARCHAR（50）NOTNULL，产地VARCHAR（50）NULL，价格REALNOTNULL，等级CHAR（6）NULL，）；USE商品销售GO3.2.3创建、修改和删除基本表CREATETABLE售货员（售货员编号CHAR（3），姓名VARCHAR（50）NOTNULL，性别CHAR（2）NOTNULL，年龄SMALLINT，PRIMARYKEY（售货员编号））；CREATETABLE售货（商品编号CHAR（4），售货员编号CHAR（3），数量INT，PRIMARYKEY（商品编号，售货员编号），FOREIGNKEY(商品编号)REFERENCES商品（商品编号），FOREIGNKEY(售货员编号)REFERENCES售货员（售货员编号）,）；主键（唯一性）pt.exe.lnk3.2.3创建、修改和删除基本表2.修改基本表基本表建立后，如果实际需要发生改变，可对基本表结构进行修改，即增加新的列、删除原有的列和修改原有列的类型。其一般格式如下：ALTERTABLE基本表名[ADD新列名列数据类型[列完整性约束]][DROPCOLUMN列名[CASCADE|RESTRICT][MODIFY列名列数据类型]其中,ADD表示增加新的列，DROP表示删除原有的列,MODIFY表示修改原有的列。【案例3-4】在售货员基本表中增加一个地址列。ALTERTABLE售货员ADD地址VARCHAR(50)；新增加的属性不能定义为“NOTNULL”.基本表在增加一个属性后,原有元组在新增加的属性列上的值都被定义为空值(NULL)。3.2.3创建、修改和删除基本表3．基本表的删除当需求发生改变，不再需要数据库某个基本表时，可以将其删除。当一个基本表被删除后，该表中的所有数据连同该表建立的索引都会被删除，而建立在该表上的视图不会随之删除，系统将继续保留其定义，但已无法使用。删除基本表的一般格式如下：DROPTABLE基本表名【案例3-6】删除商品表。DROPTABLE商品课堂讨论（1）SQL提供的主要数据类型有哪些？（2）SQL的列级完整性约束条件最常见的有哪几种，并说明它们的含义？（3）举例说明如何删除一个基本表。3.3索引3.3.1索引的作用1.加快查询速度，这也是创建索引的最主要的原因。2.通过创建唯一性索引，可保证表中每一行数据的唯一性。3.3.2索引的分类1.按照索引记录存放位置可分为聚集索引与非聚集索引。聚集索引确定表中数据的物理顺序.按照索引的字段排列记录,并且依照排好的顺序将记录存储在表中。非聚集索引按照索引的字段排列记录，数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。2.唯一索引唯一索引表示表中每一个索引值只对应唯一的数据记录，这与表的PRIMARYKEY的特性类似，因此唯一性索引常用于PRIMARYKEY的字段上以区别每一记录。3.复合索引的概念复合索引是指建立在多个列上的索引,单独的字段允许有重复的值.排序文件3.3.3创建索引在SQL语言中,建立索引使用CREATEINDEX语句,一般格式为：CREATE[UNIQUE][CLUSTER]INDEX索引名ON基本表名(列名[次序],[,列名[次序]]…);其中：UNIQUE表明建立唯一索引。CLUSTER表示建立聚集索引.次序用来指定索引值的排列顺序,可为ASC（升序)或DESC（降序）,缺省值为ASC。【案例3-7】商品表的属性列商品编号上创建一个非聚集索引CREATEINDEX商品_编号ON商品（商品编号SC）；3.3.4删除索引建立索引是为了提高查询速度，但过多或不当的索引会导致系统低效，应删除不必要的索引。在SQL语言中，删除索引使用DROPINDEX语句,一般格式为：DROPINDEX索引名【案例3-8】删除表商品的索引商品_编号。DROPINDEX商品_编号课堂讨论（1）索引有什么作用？（2）什么是唯一索引，它有什么作用？（3）什么是复合索引？pt.exe.lnk3.4SQL的数据查询(记录操作)3.4.1SELECT语句命令的格式SELECT语句命令格式:SELECT[ALL|DISTINCT]目标表的列名或列表达式1[,目标表的列名或列表达式2]…FROM表名或视图名1[,表名或视图名2]…[WHERE行条件表达式][GROUPBY列名[HAVING组条件表达式]][ORDERBY列名[ASC|DESC],...]属性名HAVING短语用于GROUPBY分组中的筛选,如普通行筛选WHERE短语一样。例:各科平均成绩高于80分(含80分)学生及平均成绩SELECT学生姓名,AVG(成绩)as平均成绩FROM成绩表GROUPBY学生姓名HAVINGAVG(成绩)=803.4.2SELECT语句中的限定1.SELECT子句SELECT子句描述的是最终查询结果的表结构和记录。（1）在目标表的列名或列表达式前加DISTINCT，保证输出的结果表中不出现重复元组。（2）列表达式是对一个单列求聚合值的表达式，SQL提供的聚合函数如表3-2所示。列表达式允许出现加减乘除及列名、常数的算术表达式。（3）需要在结果表中显示FROM子句中表或视图的所有列，用*来表示。（4）需要在结果表中输出的列名与基本表或视图的列名不一致,可使用旧名[AS新名]的形式改名.实际使用时,AS可省略。3.4.2SELECT语句中的限定表3-2聚合函数聚合函数描述COUNT（*）计算元组（记录）的个数COUNT（列名）对一列中的值计算个数SUM（列名）求某一列值的总和（此列须是数值型）AVG（列名）求某一列值的平均值（此列须是数值型）MAX（列名）求某一列值的最