SQL教程

Spring,2009.1数据库原理主讲：赵海霞河南科技大学电信学院Spring,2009.2第三章关系数据库语言SQL概述SQL的数据定义SQL的数据操纵SQL的视图定义SQL的数据控制嵌入式SQL第三章关系数据库语言SQLSpring,2009.3概述SQL（StructuredQueryLanguage）是1974年由Boyde和Chamberlin提出的1974年IBM的SystemR1979年Oracle1982年IBM的DB21984年Sybase1986年10月ANSI公布SQL-86标准1989年ISO公布SQL-89标准（120页）1992年公布SQL-92标准（622页）1999年公布SQL-99标准（1700页）2003年公布SQL-2003标准（3600页）第三章关系数据库语言SQL3.1概述Spring,2009.4SQL语言的特点综合统一集DDL、DML、DCL为一体实体和联系都是关系，因此每种操作只需一种操作符高度非过程化面向集合的操作方式以同一种语法结构提供两种使用方式（交互式和嵌入式）语言简捷，易学易用数据定义CREATE、DROP、ALTER数据查询SELECT数据更新INSERT、UPDATE、DELETE数据控制GRANT、REVOKE第三章关系数据库语言SQL3.1概述Spring,2009.5SQL语言的特点支持三级模式结构视图外模式基本表（的集合）模式存储文件和索引内模式第三章关系数据库语言SQL3.1概述Spring,2009.6SQL数据库体系结构用户2用户3用户4视图V1视图V2基本表B1基本表B2基本表B3基本表B4存储文件S1存储文件S2存储文件S3存储文件S4用户1SQL用户视图基本表存储文件第三章关系数据库语言SQL3.1概述Spring,2009.7SQL的组成数据定义语言(TheDataDefinitionLanguage,DDL)数据操作语言(TheDataManipulationLanguage,DML)嵌入式和动态SQL事务管理安全性管理触发器和高级完整性约束客户服务器执行和远程数据库存取高级特性第三章关系数据库语言SQL3.1概述Spring,2009.8SQL的数据定义定义和修改基本表（定义模式中的关系）：CREATETABLEDROPTABLEALTERTABLE定义视图（定义外模式）：CREATEVIEWDROPVIEW定义索引（定义内模式）：CREATEINDEXDROPINDEX第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.9基本表的定义基本格式CREATETABLE表名（列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]…[，表级完整性约束条件]）；表名：所要定义的基本表的名字列名：组成该表的各个属性（列）列级完整性约束条件：涉及相应属性列的完整性约束条件表级完整性约束条件：涉及一个或多个属性列的完整性约束条件第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.10[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15))；基本表的定义第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.11基本表的定义说明：SQL支持空值的概念。允许空值的列未输入数据时系统自动置为空值。SQL支持的数据类型随系统不同而有所差异，但一般都有：全字长整型、半字长整型、定点实型、浮点实型、CHAR（n）、VARCHAR（n）、TEXT、DATE第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.12基本表的定义常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束FOREIGNKEYcheck约束思考：PRIMARYKEY与UNIQUE的区别？Primarykey在建立的时候会默认地建立此field的索引，且此primarykey可以作为作为另外的表的foreignkey；再者primarykey跟unique的区别是Primarykey一定是notnull，而unique则没有此限制第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.13SQLServer2005中的数据类型精确数字bigintdecimalintnumericsmallintmoneytinyintsmallmoneybit近似数字floatreal日期和时间datetimesmalldatetime字符串chartextvarcharUnicode字符串ncharntextnvarchar二进制字符串binaryimagevarbinary其他数据类型cursortimestampsql_variantuniqueidentifiertablexmlSpring,2009.14[例2]建立一个“课程”表C，它由课程号Cno，课程名称Cname，教师Teacher组成，其中Cno为主码。CREATETABLEC(CnoCHAR(5),CnameCHAR(10),TeacherCHAR(10),Primarykey(Cno));或像书上写的将Primarykey直接定义在属性列后。Spring,2009.15基本表的定义[例3]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno),FOREIGNKEY(Sno)REFERENCESS(Sno),FOREIGNKEY(Cno)REFERENCESC(Cno));第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.16基本数据类型(1)数值型（DB2)SMALLINT半字长二进制整数INTEGER全字长二进制整数DECIMAL(P[,q]）或者DEC（p[,q])压缩十进制数，共p位，其中小数点后q位FLOAT双字长浮点数（2)字符串型CHARTER(n)或CHAR(n)VARCHAR(n)(3)时间型DATETIME(4)位串型BIT（n）Spring,2009.17基本表的删除基本格式DROPTABLE表名；示例DROPTABLES；第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.18基本表的修改基本格式ALTERTABLE表名[ADD新列名数据类型[完整性约束]][DROP完整性约束名][ALTERCOLUMN列名数据类型]；表名：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件ALTERCOLUMN子句：用于修改列名和数据类型第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.19基本表的修改增加列基本格式ALTERTABLE表名ADD列名类型；示例ALTERTABLESADDADDRESSVARCHAR(30)修改列基本格式ALTERTABLE表名ALTERCOLUMN列名类型；ALTERTABLESALTERCOLUMNSASMALLINT；删除列基本格式ALTERTABLE表名DROP列名；ALTERTABLESDROPUNIQUE(Sn)；第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.20例子修改表ALTERTABLEstudentADDsnoCHAR(5)NULL--向student表中添加一列snoALTERTABLEstudent--修改sno的定义保障此列不为空MODIFYsnoCHAR(5)NOTNULLALTERTABLEstudent--向student表添加主键约束ADDPRIMARYKEY(sno)ALTERTABLEstudent--从student表删除列sexDROPCOLUMNsexSpring,2009.21例子修改表--向study表中添加外键约束ALTERTABLEstudyADDCONSTRAINTfkcnoFOREIGNKEY(cno)REFERENCEScourse(cno)ONDELETECASCADEONUPDATECASCADE--向study表中添加外键约束ALTERTABLEstudyADDCONSTRAINTfksnoFOREIGNKEY(sno)REFERENCESstudent(sno)ONDELETECASCADEONUPDATECASCADESpring,2009.22视图的定义和修改基本操作CREATEVIEWDROPVIEW后面详细讨论第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.23索引的建立和删除建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.24索引的建立和删除索引的结构RDBMS中，索引一般采用B+树或HASH索引实现，具体由RDBMS决定。B+树具有动态平衡的优点HASH索引查找速度快。索引是关系数据库的内部实现技术，属内模式范畴。用户创建索引时，可创建唯一索引、非唯一索引或聚簇索引。Spring,2009.25在SQL86和SQL89标准中，基本表没有关键码概念，可以用索引机制来弥补。索引属于物理存储的路径概念，而不是逻辑的概念。在定义基本表时，还要定义索引，就把数据库的物理结构和逻辑结构混在一块了。因此在SQL2中引入了主码（主键）的概念，用户在创建基本表时用主码子句Primarykey直接定义主码。但至今大多数DBMS仍使用索引机制，有索引创建和撤销语句，其功能仅限于查询时起作用。Spring,2009.26索引的建立索引建立的基本格式ASCDESC[，列名ASCDESC]…）；一个索引项对应一个记录升序或降序CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名（列名第三章关系数据库语言SQL3.2SQL的数据定义聚簇索引Unique索引表明此索引的每个索引值只对应唯一的数据记录Cluster索引又称聚簇索引，必须维护表中行的物理存储顺序和索引顺序一致，经常进行更新操作的表不宜建立聚簇索引。Spring,2009.27索引的建立例、为学生-课程数据库中的S，C，SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXSsnoONS(Sno)；CREATEUNIQUEINDEXCcnoONC(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；第三章关系数据库语言SQL3.2SQL的数据定义Spring,2009.28索引的建立唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束第三章