ch0320关系数据库标准语言SQL.

第三章关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3完整性定义3.4查询3.5数据更新3.6视图3.1SQL概述SQL（StructuredQueryLanguage）结构化查询语言，是关系数据库的标准语言SQL是一个通用的、功能极强的关系数据库语言SQL标准制定SQL标准的目的是要不同厂商的DBMS都遵从SQL标准，使得数据库应用系统在不同的DBMS平台之间易于移植，提高DBMS的互操作性。SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用表表SSQQLL的的动动词词SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE图SQL对关系数据库模式的支持SQL的基本概念SQL语言支持关系数据库三级模式结构，如图所示。其中外模式对应于视图（View）和部分基本表（BaseTable），模式对应于基本表，内模式对应于存储文件。学生-课程数据库:学生表：Student(Sno,Sname,Ssex,Sage,Sdept)课程表：Course(Cno,Cname,Cpno,Ccredit)学生选课表：SC(Sno,Cno,Grade)示例数据库2006070120060702200607032006070420060701200607012006070120060702200607023.2数据定义SQL的数据定义功能包括模式定义、表定义、视图和索引定义SQL的数据定义语句表SQL的数据定义语句操作对象操作方式创建删除修改模式CREATESCHEMADROPSCHEMA表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX3.2.1模式的定义与删除一、定义模式语句格式CREATESCHEMA模式名AUTHORIZATION用户名[表定义子句|视图定义子句|授权定义子句]；若没指定模式名则隐含为用户名使用该命令的用户必须拥有DBA权限或获得DBA授予的CREATESCHEMA权限[例1]定义一个学生-课程模式S_TCREATESCHEMAS_TAUTHORIZATIONWANG；CREATESCHEMAAUTHORIZATIONWANG；定义模式实际上定义了一个命名空间，在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图等。即创建模式的同时可在该模式定义中进一步创建基本表、视图以及定义权限，在CREATESCHEMA中可接受CREATETABLE、CREATEVIEW和GRANT子句。[例2]为用户ZHANG创建一个模式TEST，并在其中定义一个表t1CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLEtest1(c1INTPRIMARYKEY,c2INT,c3CHAR(20)，c4NUMERIC(10，3)，c5DECIMAL(5，2));二、删除模式语句格式DROPSCHEMA模式名CASCADE|RESTRICT；CASCADE(级联删除)和RESTRICT(限制删除)两者必选其一注意：SQLServer不包含选项，要删除的架构不能包含任何对象[例3]删除模式ZHANGDROPSCHEMAZHANG；三、模式与表每一个基本表都属于某一个模式，一个模式包含多个基本表。定义基本表时指定其所属模式的方法：1、在表名中给出模式名CREATETABLETEST.t1(c1INTPRIMARYKEY,c2INT)2、在创建模式的同时创建表CREATESCHEMATEST1AUTHORIZATIONZHANGCREATETABLEt1(c1INTPRIMARYKEY,c2INT);3、先设置所属的模式，然后创建表(SQLServer不支持)SETsearch_pathTOS_T,PUBLIC;CREATETABLEt1(c1INTPRIMARYKEY,c2INT);四、SQLServer定义数据库CREATEDATABASEDatabaseNameON(NAME=DatabaseName_dat,FILENAME='driver:\path\DatabaseName.mdf')LOGON(NAME=DatabaseName_log,FILENAME='driver:\path\DatabaseName.ldf');•建立数据库的语句格式：例如，建立一个学生-课程数据库，数据库名：姓名（拼音）+学号CreateDatabasezhangyan2113on(name=zhangyan2113_dat,Filename=‘E:\info2007\zhangyan2113.mdf’)Logon(name=zhangyan2113_log,Filename=‘E:\info2007\zhangyan2113.ldf’);3.2.2基本表的定义、删除与修改一、定义基本表语句格式CREATETABLE表名(列名数据类型[列级完整性约束条件][,列名数据类型[列级完整性约束条件]]…[,表级完整性约束条件])；表名：所要定义的基本表的名字列名：组成该表的各个属性（列）列级完整性约束条件：涉及相应属性列的完整性约束条件表级完整性约束条件：涉及一个或多个属性列的完整性约束条件[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(8)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15),CONSTRAINTPK_StuPrimarykey(Sno))；8[例2]建立一个“课程”表Course，它由课程号Cno、课程名Cname、课程的先修课Cpno、学分Ccredit四个属性组成。其中课程号不能为空，值是唯一的，并且课程名取值也唯一。CREATETABLECourse(CnoCHAR(4)CONSTRAINTPK_CPrimarykey，CnameCHAR(20)UNIQUE，CpnoCHAR(4)，CcreditINT,CONSTRAINTFK_CForeignkey(Cpno)REFERENCESCourse(Cno))；[例3]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(8),CnoCHAR(3),Gradeint,CONSTRAINTPK_SCPrimarykey(Sno,Cno),CONSTRAINTFK_SC1FOREIGNKEY(Sno)REFERENCESStudent(Sno),CONSTRAINTFK_SC2FOREIGNKEY(Cno)REFERENCESCourse(Cno));请自己建立学生选课表SC常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束PRIMARYKEY与UNIQUE的区别？二、数据类型SQL中域的概念用数据类型来实现定义表的属性时需要指明其数据类型及长度属性的长度要根据具体情况确定选用哪种数据类型取值范围要做哪些运算SQLServer支持的数据类型bigint从-2^63到2^63-1的整型数据（所有数字）。int从-2^31到2^31-1的整型数据（所有数字）。smallint从-2^15到2^15-1的整数数据。tinyint从0到255的整数数据。bit1或0的整数数据。decimal从-10^38+1到10^38–1的固定精度和小数位的数字数据。numeric功能上等同于decimal。money货币数据值介于-2^63与2^63-1之间，精确到货币单位的千分之十。smallmoney货币数据值介于-214,748.3648与+214,748.3647之间，精确到货币单位的千分之十。1、精确数字2、近似数字float从-1.79E+308到1.79E+308的浮点精度数字。real从-3.40E+38到3.40E+38的浮点精度数字。3、日期和时间数据datetime从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒（或3.33毫秒）。smalldatetime从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。4、字符串Char固定长度的非Unicode字符数据，最大长度为8,000个字符Varchar可变长度的非Unicode数据，最长为8,000个字符。Text可变长度的非Unicode数据，最大长度为2^31-1个字符。5、Unicode字符串nchar固定长度的Unicode数据，最大长度为4,000个字符。nvarchar可变长度Unicode数据，其最大长度为4,000字符。sysname是系统提供用户定义的数据类型，在功能上等同于nvarchar(128)，用于引用数据库对象名。ntext可变长度Unicode数据，其最大长度为2^30-1个字符。6、二进制字符串binary固定长度的二进制数据，其最大长度为8,000个字节。varbinary可变长度的二进制数据，其最大长度为8,000个字节。image可变长度的二进制数据，其最大长度为2^31-1个字节。三、修改基本表语句格式ALTERTABLE表名[ALTERCOLUMN列名数据类型][ADD新列名数据类型[完整性约束]][DROPCOLUMN列名|完整性约束名]；三、修改基本表表名：要修改的基本表ALTERCOLUMN子句：用于修改列ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定列或完整性约束条件[例5]向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATETIME；注意：不论基本表中原来是否已有数据，新增加的列一律为空值。[例4]将年龄的数据类型改为半字长整数。ALTERTABLEStudentALTERCOLUMNSageSMALLINT；注：修改原有的列定义有可能会破坏已有数据[例6]删除学生姓名必须取唯一值的约束。ALTERTABLEStudentDROPUQ__student__********；[例7]直接删除属性列ALTERTABLEStudentDropCOLUMNScome；[例6`]增加学生姓名必须取唯一值的约束。ALTERTABLEStudentADDUNIQUE(Sname)；四、删除基本表语句格式DROPTABLE表名[RESTRICT|CASCADE];RESTRICT为缺省值，表的删除有限制条件CASCADE为级联删除注意：SQLServer不包含选项[例8]删除Student表DROPTABLEStudent;注意：基本表定义一旦删除，表的定义、表中的数据、在此表上建立的索引、视图、触发器等有关对象都将自动被删除掉。因此执行删除操作一定要格外小心。3.2.3建立与删除索引建立索引是加快查询速度的有效手段建立索引DBA或表的属主根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成一、建立索引语句格式CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON表名(列名[次序][,列名[次序]]…)；