第三章 数据库语言SQL

第三章数据库语言SQL概述•SQL的发展–1974年，由Boyce和Chamberlin提出–1975~1979，IBMSanJoseResearchLab的关系数据库管理系统原型SystemR实施了这种语言–SQL-86是第一个SQL标准–SQL-89、SQL-92(SQL2)、SQL-99(SQL3)概述•现状–大部分DBMS产品都支持SQL，成为操作数据库的标准语言–有方言，支持程度不同•数据定义（DDL）–定义、删除、修改关系模式（基本表）–定义、删除视图（View）–定义、删除索引（Index）•数据操纵（DML）–数据查询–数据增、删、改•数据控制（DCL）–用户访问权限的授予、收回概述——SQL的功能•交互式SQL–一般DBMS都提供联机交互工具–用户可直接键入SQL命令对数据库进行操作–由DBMS来进行解释概述——SQL的形式嵌入式SQL–能将SQL语句嵌入到高级语言（宿主语言）–使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力–一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理的语句概述——SQL的形式•数据定义–Create、Drop、Alter•数据操纵–数据查询：Select–数据修改：Insert、Update、Delete•数据控制–Grant、Revoke概述——SQL的动词数据查询是数据库应用的核心功能•基本结构SelectA1,A2,...,AnFromr1,r2,...,rmWherePA1,A2,...,An(p(r1×r1×...×rm))数据查询SelectWhereFromSelect语句的含义•对From子句中的各关系，作笛卡儿积（×）•对Where子句中的逻辑表达式进行选择（σ）运算，找出符合条件的元组•根据Select子句中的属性列表，对上述结果作投影（π）操作Select语句的含义•结果集查询操作的对象是关系，结果还是一个关系，是一个结果集，是一个动态数据集Select子句•对应于关系代数的投影（π）运算，用以列出查询结果集中的期望属性•SQL具有包的特性•Select子句的缺省情况是保留重复元组（ALL），可用Distinct去除重复元组SelectDistinctsdeptFromStudentSelectAllsdeptFromStudent•去除重复元组:费时•需要临时表的支持Select子句——重复元组Select子句——*与属性列表•星号*表示所有属性–星号*：按关系模式中属性的顺序排列，并具有一定的逻辑数据独立性–显式列出属性名：按用户顺序排列Select*FromStudentSelectStudent.*，cno，gradeFromStudent，SCWhereStudent.sno=SC.snoSelect子句——更名•为结果集中的某个属性改名•使结果集更具可读性Selectsnoasstu_no，cnoascourse_no，gradeFromSCSelectsno，sname，2001-sageasbirthdayFromStudentWhere子句•where子句对应与关系代数中的选择(σ)•查询满足指定条件的元组可以通过Where子句来实现•使where子句中的逻辑表达式返回True值的元组，是符合要求的元组，将被选择出来Where子句——运算符•比较：、=、、=、=、、not+~•确定范围：BetweenAandB、NotBetweenAandB•确定集合：IN、NOTIN•字符匹配：LIKE，NOTLIKE•空值：ISNULL、ISNOTNULL•多重条件：AND、OR、NOTWhere子句——Like•字符匹配：Like、NotLike•通配符–%——匹配任意字符串–_——匹配任意一个字符•大小写敏感Where子句——Like例：列出姓张的学生的学号、姓名。Selectsno，snameFromStudentWheresnameLIKE‘张%’Where子句——Like例：列出张姓且单名的学生的学号、姓名。Selectsno，snameFromStudentWheresnameLIKE‘张__’Where子句——转义符escape•例：列出课程名称中带有‘_’的课号及课名。Selectcno，cnameFromCourseWherecnameLIKE‘%\_%’escape‘\’From子句•From子句对应与关系代数中的笛卡儿积（×）•列出将被扫描的关系（表）例：列出所有学生的学号、姓名、课号、成绩。SelectSudent.sno，sname，SC.cno，gradeFromStudent，SCWhereStudent.sno=SC.snoFrom子句——元组变量•为From子句中的关系定义元组变量•方便关系名的引用•在同一关系的笛卡儿积中进行辨别例：列出与95001同岁的同学的学号,姓名,年龄SelectT.sno,T.sname,T.sageFromStudentasT,StudentasSWhereS.sno=‘95001’ANDT.sage=S.sageOrderBy子句•指定结果集中元组的排列次序•耗时•ASC（缺省）、DESC、未选中的属性•例：列出CS系中的男生的学号、姓名、年龄，并按年龄进行排列（升序）Selectsno,sname,sageFromStudentWheresdept=‘CS’OrderBysageASCSQL的集合操作•SQL的结果集是“包”•多个Select语句的结果可以进行集合操作，使结果为“集合”（default）•SQL-92支持•参加集合操作的关系（结果集）必须是相容的SQL的集合操作——相容•属性个数必须一致•对应的类型必须一致•属性名无关•最终结果集采用第一个结果的属性名•缺省为自动去除重复元组–除非显式说明ALL•OrderBy放在整个语句的最后SQL的集合操作——并•union（并，对应与关系代数的∪），标准SQL都支持的•采用集合的观点，合成多个查询的结果select-without-orderby...UNION[ALL]select-without-orderby...[UNION[ALL]select-without-orderby]......[ORDERBYinteger[ASC|DESC],...]例：查询计算机系的学生或者年龄不大于19岁的学生，并按年龄倒排序。SQL的集合操作——并•intersect（交，对应与关系代数的∩），并不是所有的DBMS都支持•例：查询计算机系的学生并且年龄不大于19岁的学生，并按年龄倒排序。SQL的集合操作——交•except（差，对应与关系代数的-），并不是所有的DBMS都支持•例：查询计算机系的男生。SQL的集合操作——差•数据定义语言（DataDefinitionLanguage）–Create、Drop、Alter–定义一组关系（基本表）、说明各关系的信息•各关系的模式•各属性的值域•完整性约束•索引•安全性和权限数据定义语言（DDL）•字符型•char(n)、varchar(n)•数值型•integer、smallint•numeric(p,d)•real、double、float(n)•日期/时间型•date•timeDDL——SQL中的域类型•域定义语句（SQL-92支持）–需重复使用的–CreateDomainstu_namevarchar(20)–CreateDomainzip_codechar(6)DDL——SQL中的域类型CreateTabler(A1D1，A2D2，…，AnDn，表级完整性约束1，…表级完整性约束n）其中：r关系名（表名）、Ai关系r的一个属性名Dn属性Ai域值的域类型主键声明：primarykey(Aj1，Aj2，…，Ajvm)DDL——SQL的模式定义CreateDomainstu_namevarchar(20)CreateTableStudent(snochar(10)primarykey(sno),snamestu_name,sagesmallint,ssexchar(1),sdeptchar(2))DDL——SQL的模式定义CreateTableCourse(cnochar(10)primarykey(cno),cnamevarchar(20),creditsmallint)CreateTableSC(snochar(10)notnull,cnochar(10)notnull,gradesmallint,primarykey(sno,cno))DDL——SQL的模式定义•用SQL删除关系（表）–将整个关系模式（表结构）彻底删除–表中的数据也将被删除DropTablerDropTablestudent;DDL——删除表结构Drop•删除表中的某属性–去除属性及相应的数据AlterTablerDropADDL——修改模式Alter•增加表中的属性–向已经存在的表中添加属性–allownull–已有的元组中该属性的值被置为NullAlterTablerAddADAlterTablestudentphonechar(16);DDL——修改模式Alter•属性的默认值–用户不提供某属性的值时，默认值被使用•初始值•TimeStamp–DDL中：ModifyDatechar(30)DefaultTimeStamp;AlterTablestudentAddIDintegerDefaultAutoIncrement;DDL——DefaultValue•定义一个新的域（用户定义的域）–需要重复使用的域–必须具有相同类型的属性CreateDomain域名As数据类型描述；CreateDomaindatelogAschar(30)defaulttimestamp•域的删除–DropDomaindatelog–各DBMS的方法是不同的Restrict/Cascade–使用该域的属性的处理DDL——域定义•索引是一种数据结构，是对照表、指针表–索引是为了加速对表中元组的检索而创建的一种分散存储结构（B树）–索引是对表而建立的，由除存放表的数据页面以外的索引页面组成–索引是把双刃剑，减慢更新的速度•索引不是SQL标准的要求DDL——索引Index•索引的种类–聚簇索引（ClusteredIndex）–非聚簇索引（Non-ClusteredIndex）DDL——索引Index•聚簇索引（ClusteredIndex）–表中的元组按聚簇索引的顺序物理地存放–根级页面---中间层页面---叶级页面（数据页面）–一个表中只能有一个聚簇索引–更新的复杂性，需要大量的临时空间DDL——索引Index•非聚簇索引（Non-ClusteredIndex）–表中元组存储的物理顺序与索引的顺序无关–叶级索引页面是指向数据页面的指针–每个表可有多个非聚簇索引DDL——索引IndexCREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex-nameOnTableName(Column,Column,…)CreateIndexYearIndexOnMovie(year);CreateClusteredIndexSnoIndexOnstudent(sno);DropIndexYearIndex;DDL——索引Index•查询与更新的评估–查询多？–更新多？•索引的覆盖–Where表达式–Where表达式的顺序•索引越多越好吗？•了解优化器DDL——索引的使用•数据添加–用SQL的插入语句，向数据库表中添加数据–按关系模式的属性顺序InsertIntoStudentValues(‘95001’,‘张三’,27,‘M’,‘CS’)–按指定的属性顺序，也可以只添加部分属性（非Null属性为必需）InsertIntoStudent(sno,sname,sage)Values(‘95002’,‘李四’,26)–把一列中的值进行聚合运算，返回单