《数据库SQL语言》PPT课件.ppt

1,第3章 SQL语言,2,本章概要,SQL是结构化查询语言（Structured Query Language）的缩写，其功能包括数据查询、数据操纵、数据定义和数据控制四个部分。 SQL 语言简洁、方便实用、功能齐全，已成为目前应用最广的关系数据库语言。 本章要求 了解 SQL语言的特点， 掌握SQL语言的四大功能及使用方法， 重点掌握其数据查询功能及其使用。,3,3.1 SQL语言的基本概念与特点,实例1：大一新生报到时需要经历一系列的报到流程，其中包括建立学生信息数据库，以下是学生信息数据库建立的过程，,这里，我们假设建立的是一个关系数据库，新生数据即是一个二维表，那么学生表的结构是什么？包含什么内容？,4,3.1 SQL语言的基本概念与特点,3.1.1 SQL语言的发展及标准化 3.1.1.1 SQL语言发展史 SQL语言是当前最为成功、应用最为广泛的关系数据库语言，其发展主要经历了以下几个阶段： 1974年由CHAMBERLIN和BOYEE提出，当时称为SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE)； IBM公司对其进行了修改，并用于其SYSTEM R关系数据库系统中； 1981年 IBM推出其商用关系关系数据库SQL/DS，并将其名字改为SQL，由于SQL语言功能强大，简洁易用，因此得到了广泛的使用； 今天广泛应用于各种大型数据库，如SYBASE、INFORMIX、 ORACLE、DB2、INGRES等，也用于各种小型数据库，如FOXPRO、ACCESS。,5,3.1.1.2 SQL语言标准化 随着关系数据库系统和SQL语言应用的日益广泛，SQL语言的标准化工作也在紧张革进行着，十多年来已制订了多个SQL标准； 1. 1982年，美国国家标准化局（AMERICAN NATIONAL STANDARD INSTITUTE，简称ANSI）开始制定SQL标准； 2. 1986年，美国国家标准化协会公布了SQL语言的第一个标准SQL86； 3. 1987年，国际标准化组织（ISO）通过了SQL86标准； 4. 1989年，国际标准化组织（ISO）对SQL86进行了补充，推出了SQL89标准； 5. 1992年，ISO又推出了SQL92标准，也称为SQL2； 6.SQL/99：Core level跟其他8种相应的level，包括递归查询，程序跟流程控制，基本的对象（object）支持包括oids； 7.SQL/2003：包含了XML相关内容，自动生成列值（column values）； 8.2005-09-30：“Data is the next generation inside.SQL is the new HTML”！ Tim Oeilly提出了Web 2.0理念，称数据将是核心，SQL将成为“新的HTML； 9.SQL/2006：定义了SQL与XML（包含XQuery）的关联应用； 10.2006：Sun公司将以SQL基础的数据库管理系统嵌入Java V6； 11.2007 ：SQL Server 2008（Katmi）在过去的SQL2005基础上增强了它的安全性，主要在：简单的数据加密，外键管理，增强了审查，改进了数据库镜像，加强了可支持性。,6,3.1.2 SQL语言的基本概念 首先介绍两个基本概念：基本表和视图。 基本表（BASE TABLE）：是独立存在的表，不是由其它的表导出的表。一个关系对应一个基本表，一个或多个基本表对应一个存储文件。 视图（VIEW）：是一个虚拟的表，是从一个或几个基本表导出的表。它本身不独立存在于数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。,7,例如：学生数据库中有学生基本情况表STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT)，此表为基本表，对应一个存储文件。可以在其基础上定义一个男生基本情况表STUDENT_MALE(SNO,SNAME,SAGE,SDEPT)， 它是从STUDENT中选择SSEX=男的各个行，然后在SNO,SNAME,SAGE,SDEPT上投影得到的。 在数据库中只存有STUDENT_MALE的定义，而STUDENT_MALE的记录不重复存储。 在用户看来，视图是通过不同路径去看一个实际表，就象一个窗口一样，我们通过窗户去看外面的高楼，可以看到高楼的不同部分，而透过视图可以看到数据库中自己感兴趣的内容。,8,图3.1 SQL语言支持的关系数据库的三级逻辑结构,SQL语言支持数据库的三级模式结构，如图3.1所示。其中外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。,9,3.1.3 SQL语言的主要特点 1.综合统一。 SQL语言具有:数据查询（QUERY）；数据定义（DEFINITION） 数据操纵（MANIPULATION）；数据控制（CONTROL） 四种语言一体化的功能。 2.高度非过程化 即用户只要提出“干什么”即可，不必管具体操作过程，也不必了解数据的存取路径，只要指明所需的数据即可。 3.面向集合的操作方式 SQL语言是一种面向集合的语言，每个命令的操作对象是一个或多个关系，结果也是一个关系。 4.以同一种语法结构提供两种使用方式 SQL语言既是自含式语言，又是嵌入式语言。可独立使用，也可嵌入到宿主语言中。 自含式语言可以独立使用交互命令，适用于终端用户、应用程序员和DBA； 嵌入式语言使其嵌入在高级语言中使用，供应用程序员开发应用程序。,10,主要特点 （5）语言简洁、易学易用：核心功能只有8个动词，语法简单，接近英语,11,3.2 SQL数据定义,SQL语言使用数据定义语言（DATA DEFINITION LANGUAGE，简称DDL）实现其数据定义功能，可对数据库用户、基本表、视图、索引进行定义和撤消。,12,3.2 SQL数据定义,3.2.1 字段数据类型 当用SQL语句定义表时，需要为表中的每一个字段设置一个数据类型，用来指定字段所存放的数据是整数、字符串、货币或是其它类型的数据。 SQL SERVER 的数据类型有很多种，分为以下9类： 1. 整数数据类型：依整数数值的范围大小，有BIT, INT , SMALLINT, TINYINT四种。 2. 精确数值类型：用来定义可带小数部分的数字，有NUMERIC和DECIMAL两种。二者相同，但建议使用DECIMAL。如：123.0、8000.56,13,3. 近似浮点数值数据类型：当数值的位数太多时，可用此数据类型来取其近似值，用FLOAT和REAL两种。如：1.23E+10 4. 日期时间数据类型：用来表示日期与时间，依时间范围与精确程度可分为DATETIME与SMALLDATETIME两种。如：1998-06-08 15:30:00 5. 字符串数据类型：用来表示字符串的字段。包括：CHAR, VARCHAR, TEXT三种，如：“数据库” 6. UNICODE字符串数据类型：UNICODE是双字节文字编码标准，包括NCHAR, NVARCHAR与NTEXT三种。与字符串数据类型相类似，但UNICODE的一个字符用2字节存储，而一般字符数据用一个字节存储。 7. 二进制数据类型：用来定义二进制码的数据。有：BINARY, VARBINARY，IMAGE 三种，通常用十六进制表示：如：OX5F3C,14,8. 货币数据类型：用来定义与货币有关的数据，分为MONEY 与SMALLMONEY两种，如：123.0000 9. 标记数据类型：有UNIQUEIDENTIFIER ，TIMESTAMP两种，此数据类型通常系统自动产生，而不是用户输入的，TIMESTAMP记录数据更新的时间戳印，而UNIQUEIDENTIFIER用来识别每一笔数据的唯一性。 各种数据类型的有关规定如下表：,15,16,17,18,3.2.2 定义、修改和撤消数据库的用户 3.2.2.1 建立数据库用户 数据库用户是指能够登录到数据库，并能够对数据库进行存取操作的用户。 当SQL SERVER系统安装完毕后，数据库管理员就可以通过CREATE USER语句建立其他数据库用户了。 语法格式为： CREATE USER IDENTIFIED BY 指定数据库用户的帐号名字，即用户标识符， 指用户登录到数据库系统时使用的口令， 这里的用户名和口令可以与用户登录到操作系统时所使用的用户名和口令不同。,19,例3.1 建立一个新用户，其名称为ZHANGSAN，登录口令为123。 CREATE USER ZHANGSAN IDENTIFIED BY 123 3.2.2.2 更改数据库用户的口令 数据库用户最初的口令是由数据库管理员指定的，数据库用户可以用ALTER USER命令来更改它， ALTER USER语句的基本语法格式为： ALTER USER IDENTIFIED BY 例3.2 将用户ZHANGSAN的口令改为456。 ALTER USER ZHANGSAN IDENTIFIED BY 456,20,3.2.2.3 删除用户 随着数据库应用的发展和变化，数据库的用户也会发生变化。 如果某些数据库用户不再需要使用数据库，数据库管理员就可以使用DROP USER把该用户删掉， DROP USER 语句的基本语法格式为： DROP USER 例3.3 删除用户ZHANGSAN DROP USER ZHANGSAN 注意：删除数据库用户之前应首先删除该用户建立的数据库对象，包括基本表、视图、索引等，否则系统将不允许删除这个用户。,21,3.2.2 建立数据库 CREATE DATABASE 3.2.3 创建、修改和删除数据表 3.2.3.1 创建数据表 数据表是关系数据库的基本组成单位，它物理地存储于数据库的存储文件中。 1. 创建一个数据表时主要包括以下几个组成部分： （1）字段名（列名）：字段名可长达128个字符。字段名可包含中文、英文字母、下划线、#号、货币符号（￥）及AT符号()。同一表中不许有重名列； （2）字段数据类型：见表3.2； （3）字段的长度、精度和小数位数；,22,字段的长度：指字段所能容纳的最大数据量，但对不同的数据类型来说，长度对字段的意义可能有些不同。 对字符串与UNICODE数据类型而言，长度代表字段所能容纳的字符的数目，因此它会限制用户所能输入的文本长度。 对数值类的数据类型而言，长度则代表字段使用多少个字节来存放数字。 对BINARY、VARBINARY、IMAGE数据类型而言，长度代表字段所能容纳的字节数。 精度和小数位数 精度是指数中数字的位数，包括小数点左侧的整数部分和小数点右侧的小数部分； 小数位数则是指数字小数点右侧的位数。 例如：数字12345.678，其精度为8，小数位数为3； 所以只有数值类的数据类型才有必要指定精度和小数位数。,23,经常以如下所示的格式来表示数据类型以及它所采用的长度、精度和小数位数，其中的N代表长度，P代表精度，S表示小数位数。 BINARY(N) - BINARY(10) CHAR(N) - CHAR(20) NUMERIC(P,S) - NUMERIC(8,3) 但有的数据类型的精度与小数位数是固定的，对采用此类数据类型的字段而言，不需设置精度与小数位数， 如：如果某字段采用INT数据类型，其长度固定是4，精度固定是10，小数位数则固定是0，这表示字段将能存放10位数没有小数点的整数。存储大小则是4个字节。 （4）NULL值与DEFAULT值 DEFAULT值表示某一字段的默认值，当没有输入数据时，则使用此默认的值。,24,2. 创建数据表的SQL语法格式 在SQL语言中，使用语句CREATE TABLE创建数据表，其基本语法格式为： CREATE TABLE (，|) 是合法标识符，最多可有128个字符，如S,SC,C，不允许重名。 ：DEFAULT DEFAULT：若是某字段设置有默认值，当该字段未被输入数据时，则以该默认值自动填入该字段。,25,例3.4 建立一学生表 CREATE TABLE S (SNO CHAR(8) , SN VARCHAR(20), AGE INT, SEX CHAR(2) DEFAULT 男 , DEPT VARCHAR(20); 执行该语句后，便产生了学生基本表的表框架，此表为一个空表。 其中，SEX列的缺省值为“男”。,26,实例2： 设某商业集团数据库中有三个实体集。一是“仓库”实体集，属性有仓库号、仓库名和地址等；二是“商店”实体集，属性有商店号、商店名、地址等；三是“商品”实体集，属性有商品号、商品名、单价。 1.创建仓库表，商店表，商品表。,27,实例2： 设某商业集团数据库中有三个实体集。一是“仓库”实体集，属性有仓库号、仓库名和地址等；二是“商店”实体集，属性有商店号、商店名、地址等；三是“商品”实体集，属性有商品号、商品名、单价。 设仓库与商品之间存在“库存”联系，每个仓库可存储若干种商品，每种商品存储在若干仓库中，每个仓库每存储一种商品有个日期及存储量，它的关系模式是：库存（仓库号，商品号，日期，库存量）； 1.使用SQL创建仓库表，商店表，商品表以及库存表。 2.要求仓库表中仓库号为主键，仓库地址唯一。 商店表中商店号为主键，商店名不能为空值。 商品表中商品号为主键，单价大于0。,28,3. 定义完整性约束 在SQL SERVER中，对于基本表的约束分为列约束和表约束。 列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名； 表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用,分隔，定义表约束时必须指出要约束的那些列的名称。完整性约束的基本语法格式为： CONSTRAINT 约束名：约束不指定名称时，系统会给定一个名称。,29,约束类型：在定义完整性约束时必须指定完整性约束的类型。 在SQL SERVER中可以定义五种类型的完整性约束，下面分别加以介绍： （1）NULL/NOT NULL 是否允许该字段的值为NULL。 NULL值不是0也不是空白，更不是填入字符串“NULL”，而是表示“不知道”、“不确定”或“没有数据”的意思。 当某一字段的值一定要输入才有意义的时候，则可以设置为NOT NULL。 如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用 只能用于定义列约束， 其语法格式如下： CONSTRAINT NULL|NOT NULL,30,例3.5 建立一个S表，对SNO字段进行NOT NULL约束。 CREATE TABLE S (SNO CHAR(10) CONSTRAINT S_CONS NOT NULL， SN VARCHAR(20), AGE INT, SEX CHAR(2) DEFAULT 男 , DEPT VARCHAR(20); 当SNO为空时，系统给出错误信息，无NOT NULL约束时，系统缺省为NULL。 其中S_CONS为指定的约束名称，当约束名称省略时，系统自动产生一个名字。如下列功能同上，只是省略约束名称。,31,CREATE TABLE S (SNO CHAR(10) NOT NULL , SN VARCHAR(20), AGE INT, SEX CHAR(2) DEFAULT 男 , DEPT VARCHAR(20);,32,（2）UNIQUE约束 UNIQUE约束用于指明基本表在某一列或多个列的组合上的取值必须唯一。 定义了UNIQUE约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。 唯一键允许为空，但系统为保证其唯一性，最多只可以出现一个NULL值。 UNIQUE既可用于列约束，也可用于表约束。 UNIQUE用于定义列约束时，其语法格式如下： CONSTRAINT UNIQUE 例3.6 建立一个S表，定义SN为唯一键。 CREATE TABLE S (SNO CHAR(6), SN CHAR(8) CONSTRAINT SN_UNIQ UNIQUE, SEX CHAR(2), AGE NUMERIC(2);,33,其中SN_UNIQ为指定的约束名称，约束名称可以省略， 如下例： CREATE TABLE S (SNO CHAR(6), SN CHAR(8) UNIQUE, SEX CHAR(2), AGE NUMERIC(2); UNIQUE用于定义表约束时，其语法格式如下： CONSTRAINT UNIQUE（,）,34,例3.7 建立一个S表，定义SN+SEX为唯一键。 CREATE TABLE S ( SNO CHAR(5), SN CHAR(8), SEX CHAR(2), CONSTRAINT S_UNIQ UNIQUE(SN,SEX); 系统为SN+SEX建立唯一索引，确保同一性别的学生没有重名。 （3）PRIMARY KEY约束 PRIMARY KEY约束用于定义基本表的主键，起唯一标识作用，其值不能为NULL，也不能重复，以此来保证实体的完整性。,35,PRIMARY KEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别： 在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束； 对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。 注意：不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。 PRIMARY KEY既可用于列约束，也可用于表约束。 PRIMARY KEY用于定义列约束时，其语法格式如下： CONSTRAINT PRIMARY KEY,36,例3.8 建立一个S表，定义SNO为S的主键 CREATE TABLE S (SNO CHAR(5) NOT NULL CONSTRAINT S_PRIM PRIMARY KEY, SN CHAR(8), AGE NUMERIC(2); PRIMARY KEY用于定义表约束时，即将某些列的组合定义为主键，其语法格式如下： CONSTRAINT S PRIMARY KEY (),37,例3.9 建立一个SC表，定义SNO+CNO为SC的主键 CREATE TABLE SC (SNO CHAR(5) NOT NULL, CNO CHAR(5) NOT NULL, SCORE NUMERIC(3), CONSTRAINT SC_PRIM PRIMARY KEY(SNO,CNO);,38,（4）FOREIGN KEY约束 FOREIGN KEY约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为从表，包含外部键所引用的主键或唯一键的表称主表。 系统保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。 FOREIGN KEY既可用于列约束，也可用于表约束， 其语法格式为： CONSTRAINT FOREIGN KEY REFERENCES (),39,例3.10 建立一个SC表，定义SNO,CNO为SC的外部键。 CREATE TABLE SC (SNO CHAR(5) NOT NULL CONSTRAINT S_FORE FOREIGN KEY REFERENCES S(SNO), CNO CHAR(5) NOT NULL CONSTRAINT C_FORE FOREIGN KEY REFERENCES C(CNO), SCORE NUMERIC(3), CONSTRAINT S_C_PRIM PRIMARY KEY (SNO,CNO);,40,（5）CHECK约束 CHECK约束用来检查字段值所允许的范围，如，一个字段只能输入整数，而且限定在0-100的整数，以此来保证域的完整性。 CHECK既可用于列约束，也可用于表约束， 其语法格式为： CONSTRAINT CHECK () 例3.10 建立一个SC表，定义SCORE 的取值范围为0到100之间。 CREATE TABLE SC (SNO CHAR(5), CNO CHAR(5), SCORE NUMERIC(5,1) CONSTRAINT SCORE_CHK CHECK(SCORE=0 AND SCORE =100);,41,例3.11 建立包含完整性定义的学生表 CREATE TABLE S (SNO CHAR(6) CONSTRAINT S_PRIM PRIMARY KEY, SN CHAR(8) CONSTRAINT SN_CONS NOT NULL, AGE NUMERIC(2) CONSTRAINT AGE_CONS NOT NULL CONSTRAINT AGE_CHK CHECK (AGE BETWEEN 15 AND 50), SEX CHAR(2) DEFAULT 男, DEPT CHAR(10) CONSTRAINT DEPT_CONS NOT NULL);,42,实例2： 设某商业集团数据库中有三个实体集。一是“仓库”实体集，属性有仓库号、仓库名和地址等；二是“商店”实体集，属性有商店号、商店名、地址等；三是“商品”实体集，属性有商品号、商品名、单价。 设仓库与商品之间存在“库存”联系，每个仓库可存储若干种商品，每种商品存储在若干仓库中，每个仓库每存储一种商品有个日期及存储量，它的关系模式是：库存（仓库号，商品号，日期，库存量）； 1.使用SQL创建仓库表，商店表，商品表以及库存表。 2.要求仓库表中仓库号为主键，仓库地址唯一。 商店表中商店号为主键，商店名不能为空值。 商品表中商品号为主键，单价大于0。,43,3.2.3.2 修改基本表 由于应用环境和应用需求的变化，经常需要修改基本表的结构，比如，增加新列和完整性约束、修改原有的列定义和完整性约束等。 SQL语言使用ALTER TABLE命令来完成这一功能，有如下三种修改方式：,语句格式 ALTER TABLE ALTER COLUMN ADD 完整性约束 DROP | ；,：要修改的基本表 ALTER COLUMN子句：用于修改列 ADD子句：增加新列和新的完整性约束条件 DROP子句：删除指定列或完整性约束条件,44,ADD方式 用于增加新列和完整性约束，定义方式同CREATE TABLE语句中的定义方式相同，其语法格式为： ALTER TABLE ADD | 例3.12 在S表中增加一个班号列和住址列。 ALTER TABLE S ADD CLASS_NO CHAR(6), ADDRESS CHAR(40),45,注意：使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束 。 例3.13 在SC表中增加完整性约束定义，使SCORE在0-100之间。 ALTER TABLE SC ADD CONSTRAINT SCORE_CHK CHECK(SCORE BETWEEN 0 AND 100),46,2. ALTER 方式 用于修改某些列，其语法格式为： ALTER TABLE ALTER COLUMN NULL|NOT NULL 例3.14 把S表中的SNO列加宽到8位字符宽度 ALTER TABLE S ALTER COLUMN SNO CHAR(8),47,注意：使用此方式有如下一些限制： 不能改变列名； 不能将含有空值的列的定义修改为NOT NULL约束； 若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型； 只能修改NULL|NOT NULL约束，其它类型的约束在修改之前必须先删除，然后再重新添加修改过的约束定义。,48,例3.15 删除S表中的AGE_CHK约束 ALTER TABLE S DROP CONSTRAINT AGE_CHK 3.2.3.3 改变基本表的名字 使用RENAME命令，可以改变基本表的名字，其语法格式为： RENAME TO 例3.16 将S表的名字更改为STUDENT RENAME S TO STUDENT,49,3.DROP方式 删除完整性约束定义，其语法格式为： ALTER TABLE DROP CONSTRAINT ,50,实例2： 设某商业集团数据库中有三个实体集。一是“仓库”实体集，属性有仓库号、仓库名和地址等；二是“商店”实体集，属性有商店号、商店名、地址等；三是“商品”实体集，属性有商品号、商品名、单价。 设仓库与商品之间存在“库存”联系，每个仓库可存储若干种商品，每种商品存储在若干仓库中，每个仓库每存储一种商品有个日期及存储量，它的关系模式是：库存（仓库号，商品号，日期，库存量）； 1.修改商品表，添加产地属性。 2. 为库存量添加完整性约束条件使库存量值大于等于0。 3.删除商品表中商品地址唯一的完整性约束条件,51,3.2.3.4 删除基本表 当某个基本表无用时，可将其删除。 删除后，该表中的数据和在此表上所建的索引都被删除，而建立在该表上的视图不会随之删除，系统将继续保留其定义，但已无法使用。 如果重新恢复该表，这些视图可重新使用。 删除表的语法格式： DROP TABLE 例3.17 删除表STUDENT USE STUDENT DROP TABLE STUDENT 注意：只能删除自己建立的表，不能删除其他用户所建的表。,52,3.2.5 设计、创建和维护索引 3.2.5.1 索引的作用 在日常生活中我们会经常遇到索引，例如图书目录、词典索引等。 借助索引，人们会很快地找到需要的东西。 索引是数据库随机检索的常用手段，它实际上就是记录的关键字与其相应地址的对应表。 例如，当我们要在本书中查找有关“SQL查询”的内容时，应该先通过目录找到“SQL查询”所对应的页码，然后从该页码中找出所要的信息。这种方法比直接翻阅书的内容要快。 如果把数据库表比作一本书，则表的索引就如书的目录一样，通过索引可大大提高查询速度。 此外，在SQL SERVER中，行的唯一性也是通过建立唯一索引来维护的。 索引的作用可归纳为： 1. 加快查询速度； 2. 保证行的唯一性。,53,3.2.5.2 索引的分类 1. 按照索引记录的存放位置可分为聚集索引与非聚集索引 聚集索引：按照索引的字段排列记录，并且依照排好的顺序将记录存储在表中。 非聚集索引：按照索引的字段排列记录，但是排列的结果并不会存储在表中，而是另外存储。 2. 唯一索引的概念 唯一索引表示表中每一个索引值只对应唯一的数据记录， 这与表的PRIMARY KEY的特性类似，因此唯一性索引常用于PRIMARY KEY的字段上，以区别每一笔记录。 当表中有被设置为UNIQUE的字段时，SQL SERVER会自动建立一个非聚集的唯一性索引。 而当表中有PRIMARY KEY的字段时，SQL SERVER会在PRIMARY KEY字段建立一个聚集索引。 3. 复合索引的概念 复合索引是将两个字段或多个字段组合起来建立的索引，而单独的字段允许有重复的值。,54,3.2.5.3 建立索引 建立索引的语句是CREATE INDEX，其语法格式为： CREATE UNIQUE CLUSTER INDEX ON ( 次序 , 次序) UNIQUE表明建立唯一索引。 CLUSTER表示建立聚集索引。 次序用来指定索引值的排列顺序，可为ASC（升序）或DESC（降序），缺省值为ASC。 例3.18 为表SC在SNO和CNO上建立唯一索引。 USE STUDENT CREATE UNIQUE INDEX SCI ON SC(SNO,CNO),55,执行此命令后，为SC表建立一个索引名为SCI的唯一索引， 此索引为SNO和CNO两列的复合索引，即对SC表中的行先按SNO的递增顺序索引，对于相同的SNO，又按CNO的递增顺序索引。 由于有UNIQUE的限制，所以该索引在(SNO,CNO)组合列的排序上具有唯一性，不存在重复值。 例3.19 为教师表T在TN上建立聚集索引。 CREATE CLUSTER INDEX TI ON T(TN) 执行此命令后，为T表建立一个索引名为TI的聚集索引，T表中的记录将按照TN值的升序存放。,56,注意： 1. 改变表中的数据（如增加或删除记录）时，索引将自动更新。 索引建立后，在查询使用该列时，系统将自动使用索引进行查询。 2. 索引数目无限制，但索引越多，更新数据的速度越慢。对于仅用于查询的表可多建索引，对于数据更新频繁的表则应少建索引。 3.2.5.4 删除索引 建立索引是为了提高查询速度，但随着索引的增多，数据更新时，系统会花费许多时间来维护索引。这时，应删除不必要的索引。 删除索引的语句是DROP INDEX，其语法格式为： DROP INDEX 数据表名.索引名 例3.20 删除表SC的索引SCI。 DROP INDEX SC.SCI,57,实例3: 现有关系模式如下： 部门（部门编号，部门名称，电话） 员工(员工编号，姓名，性别，部门编号，职务) 项目(项目编号，项目名称，预算) 施工(员工编号，项目编号，工时) 1.创建部门表，部门编号数据类型为CHAR（10）且为主键，部门名称数据类型为CHAR（50），电话数据类型为CHAR（11）； 2.创建员工表，员工编号数据类型为CHAR（10）且为主键，姓名数据类型为CHAR（20），性别数据类型为CHAR（2），部门编号数据类型为CHAR（10）且为外键，职务数据类型为VARCHAR（20）； 3.创建项目表，项目编号数据类型为CHAR（10）且为主键，项目名称数据类型为CHAR（20），预算数据类型为INT； 4.创建施工表，员工编号数据类型为CHAR（10），项目标号数据类型为CHAR（10），（员工编号，项目标号）为主键，工时数据类型为FLOAT；,58,3.3 SQL数据查询,3.3.1 SELECT命令的格式与基本使用 数据查询是数据库中最常见的操作。 SQL语言提供SELECT语句，通过查询操作可得到所需的信息。 SELECT语句的一般格式为： SELECT列名，列名 FROM表名或视图名，表名或视图名 WHERE检索条件 GROUP BY HAVING ORDER BY ASC|DESC;,59,SELECT语句的格式： SELECTALL|DISTINCTTOP N PERCENTWITH TIES 列名1 AS 别名1 , 列名2 AS 别名2 INTO 新表名 FROM 表名 1AS 表1别名 INNER|RIGHT|FULL|OUTEROUTERJOIN 表名2 AS 表2别名 ON 条件,60,查询的结果是仍是一个表。 SELECT语句的执行过程是: 根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。 如果有GROUP子句，则将查询结果按照相同的值进行分组。 如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。 如果有ORDER子句，查询结果还要按照的值进行排序。,61,例3.21 查询全体学生的学号、姓名和年龄。 SELECT SNO, SN, AGE FROM S 例3.22 查询学生的全部信息。 SELECT * FROM S 用 * 表示S表的全部列名，而不必逐一列出。 例3.23 查询选修了课程的学生号。 SELECT DISTINCT SNO FROM SC 查询结果中的重复行被去掉 上述查询均为不使用WHERE子句的无条件查询，也称作投影查询。,62,另外，利用投影查询可控制列名的顺序，并可通过指定别名改变查询结果的列标题的名字。 例3.24 查询全体学生的姓名、学号和年龄。 SELECT SNAME NAME, SNO, AGE FROM S 其中，NAME为SNAME的别名,63,3.3.2条件查询 当要在表中找出满足某些条件的行时，则需使用WHERE子句指定查询条件。 WHERE子句中，条件通常通过三部分来描述： 1 列名； 2 比较运算符； 3 列名、常数。,表3.8 常用的比较运算符,64,3.3.2.1 比较大小 例3.25 查询选修课程号为C1的学生的学号和成绩。 SELECT SNO,SCORE FROM SC WHERE CNO=C1 例3.26 查询成绩高于85分的学生的学号、课程号和成绩。 SELECT SNO,CNO,SCORE FROM SC WHERE SCORE85,65,3.3.2.2 多重条件查询 当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。 其优先级由高到低为：NOT、AND、OR，用户可以使用括号改变优先级。 例3.27 查询选修C1或C2且分数大于等于85分学生的的学号、课程号和成绩。 SELECT SNO，CNO，SCORE FROM SC WHERE（CNO=C1 OR CNO=C2） AND SCORE=85,66,3.3.2.3 确定范围 例3.28 查询工资在1000至1500之间的教师的教师号、姓名及职称。 SELECT TNO,TN,PROF FROM T WHERE SAL BETWEEN 1000 AND 1500 等价于 SELECT TNO,TN,PROF FROM T WHERE SAL=1000 AND SAL=1500,67,例3.29 查询工资不在1000至1500之间的教师的教师号、姓名及职称。 SELECT TNO,TN,PROF FROM T WHERE SAL NOT BETWEEN 1000 AND 1500 3.2.2.4 确定集合 利用“IN”操作可以查询属性值属于指定集合的元组。 例3.30 查询选修C1或C2的学生的学号、课程号和成绩。 SELECT SNO, CNO, SCORE FROM SC WHERE CNO IN(C1, C2) 此语句也可以使用逻辑运算符“OR”实现。,68,SELECT SNO, CNO, SCORE FROM SC WHERE CNO=C1 OR CNO= C2 利用“NOT IN”可以查询指定集合外的元组。 例3.31 查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。 SELECT SNO, CNO, SCORE FROM SC WHERE CNO NOT IN(C1, C2) 等价于： SELECT SNO, CNO, SCORE FROM SC WHERE CNO!=C1 AND CNO!= C2,69,3.3.2.5 部分匹配查询 上例均属于完全匹配查询，当不知道完全精确的値时，用户还可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）。 LIKE定义的一般格式为： LIKE 属性名必须为字符型，字符串常量的字符可以包含如下两个特殊符号： %：表示任意知长度的字符串； _：表示任意单个字符。 例3.32 查询所有姓张的教师的教师号和姓名。 SELECT TNO, TN FROM T WHERE TN LIKE 张%,70,例3.33 查询姓名中第二个汉字是“力”的教师号和姓名。 SELECT TNO, TN FROM T WHERE TN LIKE _ _力% 注：一个汉字占两个字符。 3.3.2.6空值查询 某个字段没有值称之为具有空值（NULL）。 通常没有为一个列输入值时，该列的值就是空值。 空值不同于零和空格，它不占任何存储空间。 例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。,71,例3.34 查询没有考试成绩的学生的学号和相应的课程号。 SELECT SNO, CNO FROM SC WHERE SCORE IS NULL 注意：这里的空值条件为IS NULL，不能写成SCORE=NULL。,72,3.2.2常用库函数及统计汇总查询 SQL提供了许多库函数，增强了基本检索能力。 常用的库函数，如表3.2所示,73,例3.35 求学号为S1学生的总分和平均分。 SELECT SUM(SCORE) AS TotalScore, AVG(SCORE) AS AveScore FROM SC WHERE (SNO = S1) 注意：函数SUM和AVG只能对数值型字段进行计算。,74,例3.36 求选修C1号课程的最高分、最低分及之间相差的分数 SELECT MAX(SCORE) AS MaxScore, MIN(SCORE) AS MinScore, MAX(SCORE) - MIN(SCORE) AS Diff FROM SC WHERE (CNO = C1) 例3.37 求计算机系学生的总数 SELECT COUNT(SNO) FROM S WHERE DEPT=计算机,75,例3.38 求学校中共有多少个系 SELECT COUNT(DISTINCT DEPT) AS DeptNum FROM S 注意：加入关键字DISTINCT后表示消去重复行，可计算字段“DEPT“不同值的数目。 COUNT函数对空值不计算，但对零进行计算。 例3.39 统计有成绩同学的人数 SELECT COUNT (SCORE) FROM SC 上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。,76,例3.40 利用特殊函数COUNT(*)求计算机系学生的总数 SELECT COUNT(*) FROM S WHERE DEPT=计算机 COUNT（*）用来统计元组的个数 不消除重复行，不允许使用DISTINCT关键字。,77,3.3.3 分组查询 GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。 例3.42 查询各位教师的教师号及其任课的门数。 SELECT TNO,COUNT(*) AS C_NUM FROM TC GROUP BY TNO GROUP BY子句按TNO的值分组，所有具有相同TNO的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。,78,若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。 例3.43 查询选修两门以上课程的学生学号和选课门数 SELECT SNO,COUNT(*) AS SC_NUM FROM SC GROUP BY SNO HAVING COUNT(*)=2 GROUP BY子句按SNO的值分组，所有具有相同SNO的元组为一组，对每一组使用函数COUNT进行计算，统计出每位学生选课的门数。 HAVING子句去掉不满足COUNT（*）=2的组。,79,当在一个SQL查询中同时使用WHERE子句，GROUP BY 子句和HAVING子句时，其顺序是WHEREGROUP BY HAVING。 WHERE与HAVING子句的根本区别在于作用对象不同。 WHERE子句作用于基本表或视图，从中选择满足条件的元组； HAVING子句作用于组，选择满足条件的组，必须用于GROUP BY子句之后，但GROUP BY子句可没有HAVING子句。,80,3.3.5 查询的排序 当需要对查询结果排序时，应该使用ORDER BY子句 ORDER BY子句必须出现在其他子句之后 排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序 例3.44 查询选修C1 的学生学号和成绩，并按成绩降序排列。 SELECT SNO, SCORE FROM SC WHERE CNO=C1 ORDER BY SCORE DESC,81,例3.45 查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。 SELECT SNO,CNO, SCORE FROM SC WHERE CNO IN (C2 ,C3, C4,C5) ORDER BY SNO,SCORE DESC 例3.46 求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。 SELECT SNO,SUM(SCORE) AS TotalScore FROM SC WHERE SCORE=60 GROUP BY SNO HAVING COUNT(*)=3 ORDER BY SUM(SCORE) DESC,82,此语句为分组排序，执行过程如下： 1.（FROM）取出整个SC 2.（WHERE）筛选SCORE=60的元组 3.（GROUP BY）将选出的元组按SNO分组 4.（HAVING）筛选选课三门以上的分组 5.（SELECT）以剩下的组中提取学号和总成绩 6.（ORDER BY）将选取结果排序 ORDER BY SUM(SCORE) DESC 可以改写成 ORDER BY 2 DESC 2 代表查询结果的第二列。,83,3.3.6 数据表连接及连接查询 数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。 连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。 前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，称为连接查询。 表的连接方法有两种： 方法1：表之间满足一定的条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。 方法2：利用关键字JOIN进行连接。,84,具体分为以下几种： INNER JOIN ：显示符合条件的记录，此为默认值； LEFT （OUTER） JOIN：显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以NULL来显示，此称为左连接； RIGHT （OUTER） JOIN：显示符合条件的数据行以及右边表中不符合条件的数据行，此时左边数据行会以NULL来显示，此称为右连接； FULL （OUTER） JOIN：显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以NULL来显示； CROSS JOIN：会将一个表的每一笔数据和另一表的每笔数据匹配成新的数据行。 当将JOIN 关键词放于FROM子句中时，应有关键词ON与之相对应，以表明连接的条件。,85,3.3.6.1 等值连接与非等值连接 例3.47 查询刘伟老师所讲授的课程。 方法1： SELECT T.TNO ,TN,CNO FROM T,TC WHERE (T.TNO = TC. TNO) AND (TN=刘伟) 这里，TN=刘伟为查询条件，而T.TNO = TC.TNO 为连接条件，TNO为连接字段。连接条件的一般格式为： . . 其中,比较运算符主要有：、！。 当比较运算符为“时，称为等值连接，其他情况为非等值连接。,86,引用列名TNO时要加上表名前缀，是因为两个表中的列名相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。如果列名是唯一的，比如TN，就不必须加前缀。 上面的操作是将T表中的TNO 和TC表中的TNO相等的行连接，同时选取TN为“刘伟“的行，然后再在TN，CNO列上投影，这是连接、选取和投影的操作组合。 方法2： SELECT T.TNO,TN,CNO FROM T INNER JOIN TC ON T.TNO=TC.TNO AND T.TN=刘伟,87,方法3： SELECT R2.TNO,R2.TN, R1.CNO FROM (SELECT TNO,CNO FROM TC ) AS R1 INNER JOIN (SELECT TNO ,TN FROM T WHERE TN=刘伟) AS R2 ON R1.TNO=R2.TNO,88,例3.48 查询所有选课学生的学号、姓名、选课名称及成绩。 SELECT S.SNO,SN,CN,SCORE FROM S,C,SC WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO 本例涉及三个表，WHERE子句中有两个连接条件。当有两个以上的表进行连接时，称为多表连接。,89,3.3.6.2 自身连接 当一个表与其自已进行连接操作时，称为表的自身连接。 例3.49 查询所有比刘伟工资高的教师姓名、性别、工资和刘伟的工资。 要查询的内容均在同一表T中，可以将表T分别取两个别名，一个是X，一个是Y。将X, Y 中满足比刘伟工资高的行连接起来。这实际上是同一表T的自身连接。 方法1： SELECT X.TN,X.SAL AS SAL_a,Y.SAL AS SAL_b FROM T AS X ,T AS Y WHERE X.SALY.SAL AND Y.TN=刘伟,90,方法2： SELECT X.TN, X.SAL,Y.SAL FROM T AS X INNER JOIN T AS Y ON X.SALY.SAL AND Y.TN=刘伟 方法3： SELECT R1.TN,R1.SAL, R2.SAL FROM (SELECT TN,SAL FROM T ) AS R1 INNER JOIN (SELECT SAL FROM T WHERE TN=刘伟) AS R2 ON R1.SALR2.SAL,91,例3.50 检索所有学生姓名，年龄和选课名称。 方法1： SELECT SN,AGE,CN FROM S,C,SC WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO 方法2： SELECT R3.SNO,R3.SN,R3.AGE,R4.CN FROM (SELECT SNO,SN,AGE FROM S) AS R3 INNER JOIN (SELECT R2.SNO,R1.CN FROM (SELECT CNO,CN FROM C) AS R1 INNER JOIN (SELECT SNO,CNO FROM SC) AS R2 ON R1.CNO=R2.CNO) AS R4 ON R3.SNO=R4.SNO,92,3.3.6.3 外连接 在上面的连接操作中，不满足连接条件的元组不能作为查询结果输出。 如例3.48的查询结果只包括有选课记录的学生，而不会有吴丽同学的信息。若将例3.48改成： 例3.51 查询所有学生的学号、姓名、选课名称及成绩。（没有选课的同学的选课信息显示为空）则应写成如下的SQL语句。 SELECT S.SNO,SN,CN,SCORE FROM S LEFT OUTER JOIN SC ON S.SNO=SC.SNO LEFT OUTER JOIN C ON C.CNO=SC.CNO 则查询结果只包括所有的学生，没有选课的吴丽同学的选课信息显示为空。,93,3.3.7 子查询 在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。 嵌套查询可以将一系列简单查询构成复杂查询，增强查询能力。 子查询的嵌套层次最多可达到255层，以层层嵌套的方式构造查询充分体现了SQL“结构化”的特点。 嵌套查询在执行时由里向外处理，每个子查询是在上一级外部查询处理之前完成，父查询要用到子查询的结果。,94,3.3.7.1 返回一个值的子查询 当子查询的返回值只有一个时，可以使用比较运算符（=, , =, =, !=）将父查询和子查询连接起来。 例3.52 查询与刘伟教师职称相同的教师号、姓名。 SELECT TNO,TN FROM T WHERE PROF=(SELECT PROF FROM T WHERE TN=刘伟) 此查询相当于分成两个查询块来执行。先执行子查询： SELECT PROF FROM T WHERE TN=刘伟,95,子查询向主查询只