第三章 关系数据库标准语言SQL.

第三章关系数据库标准语言SQL本章目标本章主要介绍SQL语言的基本语法和使用方式，要求熟练掌握SQL语言的使用规则，能够根据实际需求写出能够准确完成操作的SQL语句。1.SQL语言概况了解SQL的4大功能.2.SQL数据定义语言了解基表和视图的概念以及各种数据类型。掌握基表模式的定义和修改基表模式的7条命令。3.SQL查询语言掌握最常用最基本的查询语句的定义和应用。4.SQL中的视图了解视图的概念以及视图与基表的区别；掌握视图的定义和操作。SQL概述11.SQL的发展1974年，由Boyce和Chamberlin提出1975~1979，IBMSanJoseResearchLab的关系数据库管理系统原型SystemR实施了这种语言SQL-86是第一个SQL标准SQL-89、SQL-92(SQL2)、SQL-99(SQL3)2.现状大部分DBMS产品都支持SQL，成为操作数据库的标准语言3、SQL的功能数据定义（DDL）定义数据库的三级模式；定义、修改、删除基本表（table），定义、删除视图（View），定义、删除索引（Index）数据操纵（DML）数据查询：查询、统计、分组、排序等操作数据更新：对数据的插入、删除、修改操作数据控制（DCL）对基本表和视图的授权、完整性规则的描述和事物控制语句等。4、主要特点：1].语言简洁、易学易用：核心功能只有9个动词，语法简单，接近英语。SQL功能动词数据定义CREATE，DROP，ALTER数据库查询SELECT数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE2].高度非过程化的语言：用户只需提出“干什么”，至于“怎么干”由DBMS解决；用户只需要在查询语句中提出需要什么，DBMS即可按路径存取，并把结果返回给用户。3].面向集合的语言：SQL采用集合操作方式，不仅操作对象、查询结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。4].以同一种语法结构提供两种使用方式：具有自含式和嵌入式两种形式。5].综合统一：具有查询、操作、定义和控制四种语言一体化的特点。它只向用户提供一种语言，但该语言具有上述多种功能，且每种操作只需一种操作符。6].支持关系数据库三级模式结构：全体基本表构成了DB的模式，视图和部分基本表构成了外模式，DB的存储文件及其索引构成了内模式。SQL用户BaseTableB1ViewV1ViewV2BaseTableB2BaseTableB3BaseTableB4StoredFileS1StoredFileS1StoredFileS1StoredFileS1外模式模式内模式SQL语言支持的关系数据库的三级模式结构5、概念：用户可以用SQL语言对视图(View)和基本表(BaseTable)进行查询等操作，在用户观点里，视图和表一样，都是关系。基本表是本身独立存在的表，SQL中的一个关系对应一个基本表，全体基本表构成了数据库的概念模式。视图是从一个或多个基本表中导出的表，本身不独立存储在数据库中，数据库中只存储其定义而不直接存放视图对应的数据，数据仍存放在与视图相关的基本表中。视图是一个虚表。视图和部分基本表构成数据库的外模式。2SQL语言的数据定义功能即指SQLDDL语句。它包括对数据库用户、基本表、视图、索引等数据库对象的定义和撤销。介绍：基本表、视图和索引的定义1.数据库（1）定义数据库的语句格式：CREATEDATABASE数据库名注：一对尖括号标记的汉语词组为需要用户给定的标识符如：[1]CREATEDATABASEMYDB[2]createdatabasemydb[3]CREATEDATABASE学籍管理authorization张三注：在语句关键字和用户标识符中使用英文字母时，其大小写等价。（2）修改数据库语句格式：ALTERDATABASE数据库名如：alterdatabasemydb（3）撤销数据库语句格式：DROPDATABASE数据库名如：dropdatabasestudent2.基本表(1)定义基本表的语句格式：CREATETABLE表名(列定义，[列级完整性约束]…[﹤表级完整性约束﹥])表名：自己定义的标识符如：user列定义：列名、列数据类型、长度等。定义完整性约束：列级约束和表级约束[CONSTRAINT约束名]约束定义例1：createtable学生（学生号char（8）primarykey，姓名char（6）notnullunique，性别char（2）notnullcheck（性别=‘男’or性别=‘女’），出生日期datecheck（出生日期﹤’1982-12-31’），年级intcheck（年级﹥﹦1and年级﹤﹦4））完整性约束类型：PRIMARYKEY——主关键字约束，是实体完整性约束，保证主关键字的惟一性和非空性。可以在列级或表级上定义。FOREIGNKEY——外部关键字约束，是参照完整性约束，用来维护两个基本表之间的一致性。可以在列级或表级上定义。UNIQUE——确保不受PRIMARYKEY约束的列上的数据的惟一性。可以在列级或表级上定义。CHECK——通过检查输入表列的数据的值来维护值域的完整性。一般用于列级上的定义。NULL/NOTNULL——允许列值为空/不允许列值为空。一般用于列级上的定义。例2：①学生（学号，姓名，性别，专业号）②专业（专业号，专业名）create学生（学生号char（8）姓名char（6）notnullunique，性别char（2）notnullcheck（性别=‘男’or性别=‘女’），专业号char（10）create专业（专业号char（10）primarykey；专业名char（12）notnullunique）primarykey，references专业（专业号））create学生（学生号char（8），姓名char（6）notnullunique，性别char（2）notnullcheck（性别=‘男’or性别=‘女’），专业号char（10），primarykey（学生号），foreignkey（专业号）references专业（专业号））constrainta1primarykey（学生号），constrainta2foreignkey（专业号）references专业（专业号））练1：建立一个课程基本表，包含课程号、课程名、课程学分三个字段，其中课程号为主码，课程名为候选码，课程学分带有检查约束，只允许取1——6之间的整数。CREATETABLE课程(课程号CHAR(4)primarykey,课程名CHAR(10)notnullunique,课程学分intcheck（课程学分﹥﹦1and课程学分﹤﹦6）)练2：建立一个选课基本表，包含学生号、课程号和成绩三个字段，其学生号与学生基本表中的学生号的类型完全相同，其课程号与课程表中的课程号的类型完全相同。成绩属性的定义中带有检查约束，规定成绩必须在0—100之内。createtable选课(学生号char（8），课程号char(4),成绩intcheck（成绩﹥﹦0and成绩﹤﹦100），primarykey（学生号，课程号），foreignkey（学生号）references学生（学生号），foreignkey（课程号）references课程（课程号）)注意：外码必须与被参照关系中对应的主码具有完全相同的数据类型。（2）修改基本表ADD方式使用ADD方式可以向表中增加列定义和表约束。语法格式：ALTERTABLE表名ADD(列定义|约束条件[{,(列定义|约束条件}])例1：在S表中增加一个班号列ALTERTABLESADD(CLASSNOCHAR(6));例2：在S表中增加一个完整性约束定义，使DEPT的取值只能在“1”到“15”之间ALTERTABLESADD(CHECK(DEPTBWTEEN“1”AND”15”));MODIFY方式该方式能对基本表中已有的列定义进行修改，语法格式：ALTERTABLE表名MODIFY(列名数据类型[NULL|NOTNULL][{,(列名数据类型[NULL|NOTNULL]}])例：把S表中S#列个加宽到10为字符宽度ALTERTABLESMODIFY(S#CHAR(10));DROP方式该方式能删除基本表中已有的完整性约束定义或指定的列，语法格式：ALTERTABLE表名DROPCONSTRAINT约束名|PRMARYKEY|UNIQUE(列名[{,列名}])例：删除S表中的主码删除R表中的籍贯列ALTERTABLESALTERTABLERDROPPRIMARYKEYDROP籍贯（3）.改变基本表的名字RENAME旧表名TO新表名（4）.撤销基本表DROPTABLE表名练3：向student表中增加“入学时间scome”字段，其数据类型为日期型。altertablestudentaddscomedate练4：将学生表中年龄sage的数据类型改为短整型。altertablestudentmodifysagesmallint练5：删除学生姓名sname必须取唯一值的约束。Altertablestudentdropunique（sname）3.视图（1）定义视图是用一个查询块的查询结果来定义的，语法格式：CREATEVIEW视图名[(视图列的列表)]AS子查询例1：创建一个有关计算机系学生情况的视图SUB。CREATEVIEWSUBSELECTS#,SN,AGEFROMSWHEREDEPT=‘计算机’AS例2：在学生管理数据库中，建立“01001”班学生的视图view_01001，其结构包含学号、姓名、年龄、班级号。createviewview_01001asselect学号，姓名，年龄，班级号from学生where班级号=01001例3：创建一个有关信息专业学生成绩情况的视图S_SC_CCREATEVIEWS_SC_C(S#,SNAME,C#，CNAME,GRADE)ASSELECTS.S#,SNAME,C.C#，CNAME,GRADEFROMS,C,SCWHERES.S#=SC.S#ANDSC.C#=C.C#ANDPRO=‘信息’;例4：创建一个有关学生平均成绩的视图resultview在设计基本表时，考虑到关系的规范化要求，不能把通过计算得到的属性作为新关系的属性，因为会产生传递依赖。如不能出现平均成绩、总成绩、合计、年龄[若存在出生日期]、工龄[若存在参加工作日期]等属性。但为了日常查询方便，这些属性完全可以在视图中定义。如假定学生成绩表对应的关系模式为result（学号，姓名，性别，语文，数学，英语，物理，化学），则创建包含平均成绩和总成绩的视图定义如下：CREATEVIEWresultview(学号，姓名，性别，语文，数学，英语，物理，化学，平均成绩，总成绩)ASSELECT学号，姓名，性别，语文，数学，英语，物理，化学，（语文+数学+英语+物理+化学）/5，语文+数学+英语+物理+化学FROMresultGROUPBY学号（2）.改变视图的名字RENAME旧视图名TO新视图名（3）.撤销视图DROPVIEW视图名如：学生平均成绩的视图resultviewdropviewresultview（4）.使用视图的优点：1]能够将对数据库的访问限制在一定范围内，有利于数据保密2]视图机制使不同的用户能以不同的方式看待同一数据，当许多不同种类的用户使用同一个集成数据库时，这种灵活性非常重要3]视图对于数据库的重构提供了一定程度的逻辑独立性4.索引1、索引：是SQL在基本表中列上建立的一种数据库对象，也可称之为索引文件。它和建立于其上的基本表是分开存储的。建立索引的主要目的是提高数据检索的性能。2、索引的用途：[1]提高数据查询的速度；[2]保证数据