第4章SQL基础

数据库原理与应用（Oracle版）人民邮电出版社2第4章SQL基础4.1SQL的三级模式结构4.2SQL的数据定义4.3SQL数据查询4.4SQL的数据操纵4.5视图4.6Oracle常用函数4.1SQL的三级模式结构SQL是通用的、功能极强的关系DB语言。SQL的主要标准SQL-86SQL-89SQL-92(SQL2)SQL-99(SQL3)SQL-2003人民邮电出版社34.1SQL的三级模式结构SQL不仅具有丰富的查询功能还具有数据定义和数据控制功能，是集数据定义语言（DDL）、数据查询语言（DQL）、数据操纵语言（DML）、数据控制语言（DCL）于一体的关系数据语言。人民邮电出版社4SQL功能SQL动词数据定义CREATE、DROP、ALTER数据查询SELECT数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOTE4.1SQL的三级模式结构SQL支持的数据库三级模式结构人民邮电出版社5SQL用户视图1视图2视图3外模式基本表1基本表2基本表3SQL用户SQL用户存储文件1存储文件2存储文件3模式内模式4.2SQL的数据定义通过SQL语言的数据定义功能，可以完成数据库、基本表、视图以及索引的创建和修改。通过CREATE、DROP、ALTER3个核心动词完成数据定义功能。人民邮电出版社6动词功能CREATECREATEDATABASECREATETABLECREATEVIEWCREATEINDEX创建数据库创建表创建视图创建索引DROPDROPDATABASEDROPTABLEDROPVIEWDROPINDEX删除数据库删除表删除视图删除索引ALTERALTERTABLE修改表4.2SQL的数据定义Oracle支持的数据类型1）字符类型CHAR、VARCHAR、VARCHAR2、NCHAR、NVARCHAR22）数值类型NUMBER、LONG、BINARY_FLOAT、BINARY_DOUBLE3）日期/时间类型DATE、TIMESTAMP、TIMESTAMPWITHTIMEZONE、TIMESTAMPWITHLOCALTIMEZONE、INTERVALYEARTOMONTH、INTERVALDAYTOSECOND4）大对象RAW、LONGRAW、CLOB、NCLOB、BLOB、BFILE5）ROWID人民邮电出版社74.2SQL的数据定义基本表的定义CREATETABLE表名(字段名1字段数据类型[列完整性约束],字段名2字段数据类型[列完整性约束],……[表级完整性约束]);人民邮电出版社84.2SQL的数据定义基本表定义的例子建立学生信息表studentSQLCREATETABLEstudent(snoNUMBER(8)NOTNULL,snameVARCHAR2(64)NOTNULL,sexCHAR(4)DEFAULT'男',birthdayDATE,addressVARCHAR2(256),CONSTRAINT[PK_student]PRIMARYKEY(sno),CONSTRAINT[gen_check]CHECK(sexin('男','女'));人民邮电出版社94.2SQL的数据定义完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也可以定义在表级。定义表结构，并指定相应的数据完整性约束条件。人民邮电出版社10分析外码：sno,cno主码：sno姓名：非空性别：男、女两值Student表：Course表：主码：cno课程名：非空外码：pcnoSC表：主码：(sno,cno)成绩：0-1004.2SQL的数据定义基本表定义的例子CREATETABLEStudent(snoCHAR(8)NOTNULL,snameVARCHAR2(64)NOTNULL,sexCHAR(4),birthdayDATE,deptCHAR(32),PRIMARYKEY(sno),CHECKsexIN(‘男’,‘女’));人民邮电出版社11列级完整性约束实体完整性约束用户自定义完整性约束4.2SQL的数据定义基本表定义的例子CREATETABLECourse(cnoCHAR(4),cnameCHAR(10)NOTNULL,pcnoCHAR(4),creditNUMBER(4),PRIMARYKEY(cno),FOREIGNKEY(pcno)REFERENCESCourse(cno));人民邮电出版社12参照完整性约束条件4.2SQL的数据定义基本表定义的例子CREATETABLESC(snoCHAR(8),cnoCHAR(4),gradeNUMBER(6,2),PRIMARYKEY(sno,cno),FOREIGNKEY(sno)REFERENCESStudent(sno),FOREIGNKEY(cno)REFERENCESCourse(cno),CHECK((gradeISNULL)OR(gradeBETWEEN0AND100)));人民邮电出版社134.2SQL的数据定义在Oracle中创建表的完整的语法如下：CREATETABLE[模式名.]表名(字段名字段数据类型[,字段名字段数据类型]…)[TABLESPACE表空间名][PCTFREE][PCTUSED][INITRANS][MAXTRANS][STORAGECLAUSE][LOGGING|NOLOGGING][CACHE|NOCACHE];人民邮电出版社144.2SQL的数据定义基本表的修改ALTERTABLE基本表名[ADD新列名列数据类型[列完整性约束]][DROPCOLUMN列名][MODIFY列名新的数据类型][ADDCONSTRAINT表级完整性约束][DROPCONSTRAINT表级完整性约束]人民邮电出版社154.2SQL的数据定义基本表修改的例子例在student表中增加一个age列（年龄）。ALTERTALBEstudentADDageNUMBER(4)NULL;例修改student表中dept列为CHAR(16)。ALTERTALBEstudentMODIFYdeptCHAR(16);例删除student表中新增的age列。ALTERTALBEstudentDROPCOLUMNage;人民邮电出版社164.2SQL的数据定义基本表的删除当数据库某个基本表不再使用时，可以将其删除。当一个基本表被删除后，该表中的所有数据连同该表建立的索引都会被删除。但由该表导出的视图的定义仍然存在数据字典当中，只是无法使用。DROPTABLE基本表名例删除student表DROPTALBEstudent;人民邮电出版社174.2SQL的数据定义什么是索引汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等我们可以根据拼音或偏旁部首，快速查找某个字词人民邮电出版社18在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无需对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。数据库中的索引是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。4.2SQL的数据定义索引的建立和维护为了改善查询性能，可以建立索引。索引是根据表中的一列或若干列按照一定顺序建立的列值，与表中记录行之间具有一一对应关系的辅助表结构。索引属于物理存储的路径概念，而不是用户使用的逻辑概念。建立在多个列上的索引被称为复合索引。有两种重要的索引：聚集索引（ClusteredIndex）非聚集索引（Non-clusteredIndex）。人民邮电出版社194.2SQL的数据定义索引的建立和维护聚集索引类似于按姓氏排列数据的电话簿。由于聚集索引规定了数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。人民邮电出版社204.2SQL的数据定义索引的建立和维护非聚集索引与书本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息则按另一种顺序存储（这可以由聚集索引规定）。与使用书中索引的方式相似，DBMS在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。人民邮电出版社214.2SQL的数据定义索引的建立和维护创建索引CREATE[UNIQUE][CLUSTER]INDEX索引名ON基本表名（列名[次序]，[，列名[次序]]…）；说明：UNIQUE：规定索引的每一个索引值只对应于表中的唯一记录。CLUSTER：规定此索引为聚集索引。省略CLUSTER则表示创建的索引为非聚集索引。次序：建立索引时指定列名的索引表是ASC（升序）或DESC（降序）。人民邮电出版社224.2SQL的数据定义索引的优缺点优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新人民邮电出版社234.2SQL的数据定义索引的建立和维护创建索引例子例在Employee表的属性列Eno上创建一个非聚集索引。CREATEINDEXIDX_DNO_ENOONEmployee（DnoASC，EnoASC）；例在Employee表的属性列Ename上创建一个聚集索引。CREATECLUSTERINDEXIDX_ENAMEONEmployee（EnameASC）；人民邮电出版社244.2SQL的数据定义索引的建立和维护删除索引过多或不当的索引会导致系统低效。用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片，降低系统效率。DROPINDEX索引名ON基本表名例删除Employee表的IDX_ENAME索引。DROPINDEXIDX_ENAMEONEmployee;人民邮电出版社254.3SQL数据查询SQL数据查询是SQL语言中最重要、最丰富也是最灵活的内容。SELECT语句的语法SELECT[ALL|DISTINCT]列名或表达式[别名1][，列名或表达式[别名2]]…FROM表名或视图名[表别名1][，表名或视图名[表别名2]]…[WHERE条件表达式][GROUPBY列名[,列名…][HAVING条件表达式]][ORDERBY列名[ASC|DESC][,列名[ASC|DESC]…]人民邮电出版社264.3SQL数据查询查询的基本结构包括了三个字句：SELECT、FROM、WHERE。从表中（From子句）,选择满足记录选择条件（Where子句）的记录，并对他们进行分组（Group子句,Having子句表达组选择条件）统计(统计函数）和排序（Orderby子句）和投影（Select子句），形成查询结果集人民邮电出版社274.3SQL数据查询单表无条件查询从Northwind数据库的Customers表中查询出所有的列：SELECT*FROMcustomers从Customers表中查询出部分列：SELECTCompanyName,ContactName,Address,PhoneFROMcustomers在结果集中给查询的列以别名：SELECTCompanyNameAS公司名称,ContactNameAS联系人姓名,AddressAS地址,PhoneAS电话FROMcustomers人民邮电出版社284.3SQL数据查询Oracle所提供的scott模式可以提供一些示例表和数据，该模式演示了一个很简单的