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

第三章关系数据库语言SQL2020/2/2223.1SQL概述结构化查询语言（StructuredQueryLanguage，SQL）：一个关系数据库语言，它的操作对象是以表的形式存放在关系数据库系统中的数据。当前主流的数据库管理系统，如Oracle、SQLserver等，都是基于SQL语言的。SQL具有通用性，功能介于关系代数与关系演算之间。2020/2/223SQL的产生与发展1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEMR，配制的查询语言称为SQUARE(SpecifyingQueriesAsRelationalExpression)语言，在语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL(StructuredEnglishQUEryLanguage)语言。后来SEQUEL简称为SQL(StructuredQueryLanguage)，即“结构式查询语言”，SQL的发音仍为“sequel”。现在SQL已经成为一个标准。2020/2/224SQL数据库的体系结构用户1用户2用户3用户4视图1视图1基本表1基本表2基本表3基本表4存储文件1存储文件2存储文件3存储文件4图3－1SQL对RDBMS模式的支持SQL用户外模式模式内模式2020/2/225SQL语言的主要特点如下1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方式5.语言简洁，易学易用2020/2/2261、综合统一SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL功能于一体，语言风格统一，可独立完成数据库生命周期中的全部活动。如：定义关系模式、建立数据库、查询、更新、维护、数据库重构、数据库安全控制等2、高度非过程化“怎么做”“做什么”减轻了用户的负担，有利于提高数据独立性2020/2/2273、面向集合的操作方式非关系数据模型：面向记录SQL：集合操作4、以同一种语法结构提供两种使用方式可联机交互操作，可以嵌入到高级语言中2020/2/2285、语言简洁，易学易用9个动词表3.1SQL语言的动词SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATEDELETE数据控制GRANT，REVOKE2020/2/2293.2数据定义SQL语言可定义外模式…………………视图概念模式………………表、约束及索引表3.2SQL的数据定义语句操作方式操作对象创建删除修改表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX2020/2/22103.2.1基本表的定义、修改与删除1、定义基本表CREATETABLE表名（列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]…[，表级完整性约束条件]）；表名：所要定义的基本表的名字列名：组成该表的各个属性（列）列级完整性约束条件：涉及相应属性列的完整性约束条件表级完整性约束条件：涉及一个或多个属性列的完整性约束条件2020/2/2211列级约束的种类主码约束－－－－PRIMARYKEY唯一约束－－－－UNIQUE该列不允许出现重复的属性值非空值约束－－－－NOTNULL或NULL字段的值非空或空关系的主键必须指定为NOTNULL参照完整性约束－－－－FOREIGNKEY（外码约束）定义不同关系表之间的参照完整性[CONSTRAINT约束名]FOREIGNKEYREFERENCES被参照表名（与外码对应的主码名）2020/2/2212域完整性约束－－－－CHECK为该列设置应满足的条件[CONTRAINT＜约束名＞]CHECK（＜约束条件表达式＞）默认约束－－－－DEFAULT为该列的属性值设置一个默认值[CONTRAINT＜约束名＞]DEFAULT＜默认值＞2020/2/2213唯一约束指定若干列不允许出现有重复的属性值[CONSTRAINT约束名]UNIQUE（＜列组＞）主码约束指定主键不空，也不允许重复出现[CONSTRAINT约束名]PRIMARYKEY(列组)表级约束的种类2020/2/2214参照完整性约束（外键约束）定义不同关系表之间的参照完整性[CONSTRAINT约束名]FOREIGNKEY（外码）REFERENCES被参照表名（与外码对应的主码名）域完整性约束为该列设置应满足的条件[CONSTRAINT＜约束名＞]CHECK（＜约束条件表达式＞）2020/2/2215SQL中，域的概念用数据类型来实现。定义表的各个属性列时需指明其数据类型及长度（或精度）。2020/2/2216SQL数据类型常用数据类型含义CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串INT长整数（也可以写作INTEGER）SMALLINT短整数NUMERIC(p,d)十进制数，由p位数字（不包括符号、小数点）组成，小数后面有d位数字FLOAT(n)浮点数，精度至少为n位数字DATE日期，包含年、月、日，格式为YYYY-MM-DDTIME时间，包含一日的时、分、秒，格式为HH:MM:SS2020/2/2217例题[例1]建立学生表Student(学号，姓名，性别，出生年月，入学成绩，党员否，班级编号，简历，照片)。其中学号为主码，班级编号为外码，姓名取值唯一，性别只能是“男”或“女”。CREATETABLEStudent学生(学号CHAR(10)PRIMARYKEY,姓名CHAR(10)CONSTAINTS1UNIQUE,性别CHAR(2)CHECK(性别in(男,女)),出生年月DATE,入学成绩INT,党员否BOOLEAN,班级编号CHAR(6),简历CLOB,照片BLOB);2020/2/2218例题[例2]建立一个选课表Score(学号，课程编号，成绩，学期)，其中(学号,课程编号)为主码。CREATETABLEScore(学号CHAR(10),课程编号CHAR(8),成绩INT,学期CHAR(9),Primarykey(学号,课程编号),foreignkey(学号)referencesStudent(学号),foreignkey(课程编号)referencesCourse(课程编号),check(成绩=0and成绩=100));2020/2/2219[例3]建立学生选课数据库中的如下2个表及它们之间的关系。班级表：Class(班级编号，班级名称，所属专业，人数)课程表：Course(课程编号，课程名称，先修课号，学时，学分)2020/2/2220说明一个表中可有多个UNIQUE声明，但只能有一个PRIMARYKEY声明。声明为PRIMARYKEY的属性不允许取空值，但声明为UNIQUE属性可取空值。最后的分号是SQL命令的结束标志在SQL语句中不分大小写定义表的各个属性时，不仅需要指明其数据类型，还需要给出其长度。2020/2/2221Primarykey在建立的时候会默认地建立此域的索引，且此primarykey可以作为作为另外的表的foreignkey。PRIMARYKEY与UNIQUE的区别？Primarykey一定是notnullUnique无此限制，但只能有一个空值2020/2/2222CREATETABLE表名(列名数据类型[列级约束][,列名数据类型[列级约束]…][,表级约束]);表名：所要定义的基本表的名字列名：组成该表的各个属性（列）列级约束：涉及相应属性列的完整性约束条件表级约束：涉及一个或多个属性列的完整性约束条件2020/2/22232、修改基本表ALTERTABLE表名[ADDCOLUMN新列名数据类型[完整性约束]][DROPCOLUMN列名][DROPCONSTRAINT完整性约束名][ALTERCOLUMN列名数据类型]；表名：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的列或完整性约束条件ALTER子句：用于修改原有列的定义，如列名和数据类型2020/2/2224例题[例4]向Student表增加“Scome（入学时间）”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。2020/2/2225删除属性列直接删除间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名2020/2/2226[例5]删除Student表中的“Scome（入学时间）”列。ALTERTABLEStudentDROPScome；2020/2/2227例题[例6]删除学生表中姓名必须取唯一值的约束。ALTERTABLEStudentDROPCONSTRAINTS1；ALTERTABLEStudentDROPUNIQUE(Sname)；2020/2/2228[例7]将学生表“Sage”的数据类型改为半字长整数。ALTERTABLEStudentALTERSageSMALLINT；注：修改原有的列定义有可能会破坏已有数据2020/2/22293、删除基本表DROPTABLE表名[RESTRICT|CASCADE];表名：要删除的基本表RESTRICT选项：有条件删除没有基于此表的约束引用、视图、触发器、存储过程或函数等CASCADE选项：无条件删除与此表相关的依赖对象将一起删除2020/2/2230例题[例7]删除Student表DROPTABLEStudent;2020/2/22313.2.2建立与删除索引建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引2020/2/2232在SQL86和SQL89标准中，基本表没有主码概念，可以用索引机制来弥补。索引属于物理存储的路径概念，而不是逻辑的概念。在定义基本表时，还要定义索引，就把数据库的物理结构和逻辑结构混在一块了。2020/2/2233因此，在SQL2中引入了主码（主键）的概念，用户在创建基本表时用主码子句Primarykey直接定义主码。但至今大多数DBMS仍使用索引机制，有索引创建和撤销语句，其功能仅限于查询时起作用。2020/2/22341、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名[次序][,列名[次序]]…)；表名：指定要建索引的基本表名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔次序：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE：表明此索引的每一个索引值只对应唯一的数据记录CLUSTER：表示要建立的索引是聚簇索引省略UNIQUE和CLUSTER时，表示要建立非唯一索引，即普通索引。2020/2/2235例题[例6]为学生课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(