sql-课件-第3章-关系数据库标准语言SQL

山东财政学院计算机信息工程学院第三章关系数据库标准语言SQL本章主要介绍标准SQL的数据定义、数据查询、数据更新数据删除等语句的基本用法以及视图的创建和使用。山东财政学院计算机信息工程学院结构化查询语言SQL（structuredquerylanguage）是一种介于关系代数和关系演算之间的语言，其功能包括数据定义、数据操纵、数据查询和数据控制四个方面，是关系数据库系统的国际标准。SQL86：第一个SQL标准，ANSI（AmericanNationalStandardsInstitute，美国国家标准协会）。SQL89：1989年作了少许改进SQL92：也被称作SQL2，ANSI和ISO（InternationalOrganizationforStandardization，国际标准化组织）作了较大改动和完善，这是目前绝大多数商用RDBMS支持的版本。SQL99：也被称作SQL3，是在SQL92的基础上扩展而成的。SQL2003：也称为SQL4，扩展了对XML的支持。此后又依次推出了SQL2006和SQL2008。SQL山东财政学院计算机信息工程学院第三章关系数据库标准语言SQL3.1银行数据库3.2数据定义3.3数据查询3.4数据操纵3.5视图山东财政学院计算机信息工程学院3.1银行数据库支行：branch（branch_name，branch_city，assets）客户：customer（customer_id，customer_name，customer_street，customer_city）存款账户：account（account_number，branch_name，balance）贷款账户：loan（loan_number，branch_name，amount）储蓄：deposits（customer_id，account_number）借贷：borrows（customer_id，loan_number）山东财政学院计算机信息工程学院3.2数据定义3.2.1数据库的定义3.2.2表的定义3.2.3索引的定义数据定义SQLServer数据库由包含数据的表集合和其他对象（如视图、索引、存储过程、触发器、用户、角色等）组成，支持三级模式结构。其中用户模式对应于视图，逻辑模式对应于基本表，物理模式对应于存储文件、索引等。山东财政学院计算机信息工程学院3.2.1数据库的定义数据库是一个存储空间，用于存放数据库中的数据库对象。SQLServer数据库对象包括：关系图、表、视图、存储过程、用户、角色、规则、默认、用户定义的数据类型以及用户定义的函数。数据库对象及其含义关系图：描述一些表及它们之间的关系，包含键与键之间的约束表：是数据的集合，用来存储数据和操作数据的逻辑结构视图：是虚表，数据仍存放在相应基本表中，可简化用户的操作，可与授权机制相结合实现数据安全性存储过程：是SQLServer服务器上一组预先定义并编译好的T-SQL语句，可用于性能优化和控制访问用户：在特定的数据库内创建，并关联一个登录名，可通过给用户授权来控制用户对数据的使用角色：是权限的集合，可为一组具有相同权限的用户创建角色，通过角色来管理数据库权限数据库对象及其含义规则：实现check约束保证数据的完整性默认：针对表中的列的设置，如果某列没被指定为任何值时可使用该值用户定义的数据类型：如果多个表的列要存储相同类型的数据，用户可基于基本数据类型来定义新的数据类型。用户定义的函数：由多个T-SQL语句组成，用于封装代码以便重新使用。数据库文件SQLServer2000将数据库映射为一组操作系统文件，数据和日志信息分别存储在不同的文件中。SQLServer数据库中有三种物理文件：主数据文件、辅助数据文件和日志文件。山东财政学院计算机信息工程学院1、主数据文件主数据文件也称为基本数据文件，是数据库的起点，包含了数据库的初始信息，并记录数据库还拥有哪些文件。每个数据库有且只能有一个主数据文件。主数据文件是数据库必须的文件。其文件扩展名是*.mdf。山东财政学院计算机信息工程学院2、辅助数据文件辅助数据库文件又称从属文件，用于保存所有主数据文件中容纳不下的数据，不是数据库必须的文件。一个数据库中可以没有、也可以有一个或多个辅助数据文件。其文件扩展名是*.ndf。山东财政学院计算机信息工程学院3、日志文件是数据库故障恢复的重要手段和方法，用于记录对数据库的各种操作及所涉及的相关数据。每个数据库至少拥有一个日志文件，日志文件的大小最少是1MB。其文件扩展名是*.ldf。山东财政学院计算机信息工程学院一、文件组为了有助于数据布局和管理任务，可将多个数据文件集合起来形成一个整体，这个整体就是文件组，每个组有一个组名。一个数据文件只能存在于一个文件组中，日志文件不属于任何文件组。SQLServer2000的文件组有三种类型：主文件组、用户定义文件组、默认文件组

山东财政学院计算机信息工程学院1、主文件组主文件组中包含着主数据文件以及相关内容。在创建数据库时，系统会自动创建主文件组，并将主数据文件及系统表的所有页都分配到主要文件组中。山东财政学院计算机信息工程学院2、用户定义文件组由用户通过SQLServer企业管理器创建的文件组称为用户定义文件组（User-DefinedFilegroup）。该组中包含逻辑上一体的数据文件和相关信息，大多数数据库只需要一个文件组和一个日志文件就可很好的运行。但如果库中的文件很多，就要创建用户定义文件组，以便管理。使用时，可以通过企业管理器或Transact-SQL语句中的filegroup子句指定需要的用户定义文件组。山东财政学院计算机信息工程学院3、默认文件组在每个数据库中，同一时间只能有一个文件组是默认文件组（DefaultFilegroup）。当进行数据库操作时，如果不指定文件组，则系统自动选择默认文件组。可使用Transact-SQL语句中的alterdatabase语句指定数据库的默认文件组。在不特别指定的情况下，系统将主要文件组认定为默认文件组。山东财政学院计算机信息工程学院二、数据库的物理存储结构数据文件的结构按照层次可以划分为页面（page）和扩展盘区（extent）。

山东财政学院计算机信息工程学院1、页面是数据存储的基本单位。页的尺寸是8KB。每页的开头是96B的页头，在页头存储页码、页类型、页上剩余的自由空间以及拥有此页对象的ID等有关页的系统信息。数据库中的每个页面中只能存储一种数据库对象的数据。一个页面可以存放多条记录，但一条记录不能跨页存放，即SQLServer2000中一条记录不能超过8060B。数据文件中的页有8种类型：数据页、索引页、文本/图像页、全局分配映射表页、页空闲空间、索引分配映射表页、大容量更改映射表页和差异更改映射表页。日志文件中不包含页，仅含有一系列的日志记录。

山东财政学院计算机信息工程学院2、扩展盘区由8个连续页面组成的数据结构称为一个盘区。是SQLServer分配给表和索引的基本单位。扩展盘区可分为统一扩展盘区和混合扩展盘区。统一扩展盘区由一个数据库对象所有。混合扩展盘区可以为多个数据库所有，即其中最多可以放8种数据库对象。山东财政学院计算机信息工程学院注意一个数据库是由文件组成的，文件是由盘区组成的，而盘区是由页面组成的。所有的SQLServer数据文件都会拥有两个文件名：逻辑文件名和物理文件名。逻辑文件名是在Transact-SQL语句中引用物理文件时所使用的名称，逻辑文件名必须是唯一的。物理文件名是包括路径在内的物理文件名。数据库的类型系统数据库mastermodelmsdbtempdb

实例数据库：pubsnorthwind

用户创建的数据库创建数据库－企业管理器山东财政学院计算机信息工程学院创建数据库-T-SQLCREATEDATABASEdatabase_name[ON[<filespec>[，…n]][,<filegrop>[，…n]]][LOGON{<filespec>[，…n]}]语法说明database_name：新创建的数据库的名称。ON：用于定义存放“数据”的空间。LOGON：用于定义存储“日志”的空间。<filespec>：代表数据文件或日志文件的定义，其语法格式如下：<filespec>：：＝[PRIMARY]([NAME=logical_file_name,]FILENAME=’os_file_name’[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])[,…n]语法说明PRIMARY：指定主文件。主文件组的第一个文件被认为是主数据文件。如果没有PRIMARY项，则在CREATEDATABASE命令中列出的第一个文件将被默认为主文件。NAME=logical_file_name：文件逻辑名。FILENAME=’os_file_name’：操作系统下的物理文件名。SIZE：文件的初始大小。MAXSIZE：文件大小的最大值。FILEGROWTH：文件大小的递增量。例创建银行Bank数据库CREATEDATABASEBankON(NAME=Bank_dat,FILENAME='d:\bank\bankdat.mdf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5MB)LOGON(NAME=Bank_log,FILENAME='d:\bank\banklog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)山东财政学院计算机信息工程学院例：创建具有两个文件组的数据库bank2CREATEDATABASEBANK2ONPRIMARY(NAME=BANK2_1_DAT,FILENAME='d:\bank\bank2_f1dat.mdf',SIZE=20MB,MAXSIZE=50MB,FILEGROWTH=5MB),(NAME=BANK2_2_DAT,FILENAME='d:\bank\bank2_f2dat.ndf',SIZE=10MB,MAXSIZE=40MB,FILEGROWTH=5MB),FILEGROUPBANK2_GROUP1(NAME=BANK2_3_DAT,FILENAME='d:\bank\bank2_g1f1dat.ndf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10MB),(NAME=BANK2_4_DAT,FILENAME='d:\bank\bank2_g1f2dat.ndf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)LOGON(NAME=BANK2_LOG,FILENAME='d:\bank\bank2log.ldf',SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=5MB)数据库的修改ALTERDATABASEdatabase_name｛ADDFILE<filespec>[,…n][TOFILEGROUPfilegroup_name]|ADDLOGFILE<filespec>[,…n]|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE<filespec>|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}……｝语法说明database_name：要更改的数据库名称。ADDFILE：指定要添加的文件。ADDLOGFILE：指定要将日志文件添加到指定的数据库。TOFILEGROUP：指定要将指定文件添加到的文件组。REMOVEFILE：从系统中删除文件。ADDFILEGROUP：指定要添加的文件组。REMOVEFILEGROUP：从系统中删除文件组。MODIFYFILE：指定要更改的文件及文件属性。MODIFYNAME=new_dbname：重命名数据库。MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}：指定要修改的文件组和所需的改动。例：重命名上例中创建的数据库bank2为bank_2。ALTERDATABASEbank2MODIFYNAME=bank_2山东财政学院计算机信息工程学院数据库的删除DROPDATABASEdatabase_name语法说明：master、tempdb、model、msdb系统数据库不能删除。正在使用的数据库不能删除。例：删除数据库bank_2。DROPDATABASEBank_2山东财政学院计算机信息工程学院3.2.2表的定义数据库中包含一个或多个表。表是数据的集合，是用来存储数据和操作数据的逻辑结构。SQLServer允许每个数据库最多包含2000000个表，每个表最多允许有1024列，每行最多允许有8060B的用户数据。表的类型系统表Sysobjects表：用于描述数据库中的对象：表、视图、存储过程等。出现在每个数据库中。它对每个数据库对象含有一行记录。Sysconfigures表：用户设置的每个配置选项在表中各占一行。包含最近启动MicrosoftSQLServer前定义的配置选项，还包含最近启动后设置的所有动态配置选项。该表只位于master数据库中。Sysusers：数据库中每个MicrosoftWindows用户、Windows组、MicrosoftSQLServer用户或SQLServer角色在表中占一行。Syscolumns表。出现在master数据库和每个用户自定义的数据库中，它对基表或者视图的每个列和存储过程中的每个参数含有一行记录。Sysdatabases表：保存服务器上所有数据库名、所有者、状态及其他信息。它对SQLServer系统上的每个系统数据库和用户自定义的数据库含有一行记录，只出现在master数据库中。Syscomments：

用户创建的表

山东财政学院计算机信息工程学院SQLServer2000数据类型整数数据类型

浮点数据类型

二进制数据类型

字符数据类型

逻辑型日期/时间数据类型

货币数据类型

其他数据类型

用户自定义数据类型

山东财政学院计算机信息工程学院一、整型数据类型tinyint：可以存储从0到255之间的所有正整数。所占存储空间大小为1个字节。

smallint：可存储从－215到215－1之间的所有正负整数。所占存储空间大小为2个字节，其中1位表示整数值的正负号，其它15位表示整数值的长度和大小。int：可存储从－231到231－1之间的所有正负整数。所占存储空间大小为4个字节，其中1位表示整数值的正负号，其它31位表示整数值的长度和大小。bigint：可存储从－263到263－1之间的所有正负整数。所占存储空间大小为8个字节。

山东财政学院计算机信息工程学院二、浮点数据类型real：可精确到第7位小数，其范围为从－3.40E+38到3.40E+38，占用4个字节的存储空间。float：可精确到第15位小数，其范围为从－1.79E+308到1.79E+308，占用8个字节的存储空间。decimal：可以提供小数所需要的实际存储空间，可以用2到17个字节来存储从－1038－1到1038－1之间的数值。numeric：与decimal数据类型完全相同。

山东财政学院计算机信息工程学院三、二进制数据类型binary(n)：固定长度为n个字节二进制数据，最大长度可达8KB。n取值为1到8000。varbinary(n)：n个字节可变长二进制数据，不能超过8KB。image：可用于存储字节数超过8KB的可变长度的二进制数据，如MicrosoftWord文档、MicrosoftExcel图表以及图像数据等，其最大长度为231－1个字节。二进制常量以0X开始，后面跟着位模式的十六进制表示。例如：0X1A表示十六进制的值1A，它等于十进制的数26。山东财政学院计算机信息工程学院四、字符数据类型char[(n)]：使用固定长度来存储字符，每个字符和符号占一个字节的存储空间。n表示所有字符所占的存储空间，n的取值为1到8000，默认值为1。varchar[(n)]：与char类型相似，n的取值为1到8000。varchar数据类型具有变动长度的特性，存储长度为实际数值长度。nchar[(n)]：与char类型相似，n的取值为1到4000。NCHAR类型采用UNICODE标准字符集（CharacterSet）。UNICODE标准规定每个字符占用两个字节的存储空间，存储空间是：字符数×2（字节）。nvarchar[(n)]：与varchar类型相似，n的取值为1到4000。nvarchar数据类型采用UNICODE标准字符集。text：专门用于存储数量庞大的变长字符数据。最大长度可达231－1（2147483647）个字符。ntext:：用于存储可变长度的unicode数据，最多可存储230－1个unicode数据。例如：’thisisastring’就是一个合法的字符型常量。

山东财政学院计算机信息工程学院五、逻辑型bit：bit数据类型占用1个字节的存储空间，其值为0或1。如果输入0或1以外的值，将被视为1。

山东财政学院计算机信息工程学院六、日期/时间数据类型Datetime：存储从1753年1月1日零时起到9999年12月31日23时59分59秒之间的所有日期和时间，其精确度可达三百分之一秒，即3.33毫秒。所占存储空间为8个字节。Smalldatetime：存储从1900年1月1日到2079年6月6日，但精度较低，只能精确到分钟。所占存储空间为4个字节。日期/时间常量使用特定格式的字符日期表示，用单引号括起来。SQLServer日期时间格式输入格式datetime值Smalldatetime值october1,20091:12:23.1222009-10-0101:12:23.1232009-10-0101:12:2410/1/20091pm2009-10-0113:00:00.0002009-10-0113:00:0010.1.200913:002009-10-0113:00:00.0002009-10-0113:00:0015:30:291900-01-0115:30:29.0001900-01-0115:30:2910/1/20092009-10-0100:00:00.0002009-10-0100:00:00山东财政学院计算机信息工程学院七、货币数据类型money：有4位小数的DECIMAL值，其取值从－263到263－1，数据精度为万分之一货币单位。所占存储空间为8个字节。smallmoney：其取值从－214,748.3648到+214,748.3647，所占存储空间为4个字节。若要指定800美元，则使用$800。山东财政学院计算机信息工程学院八、其他数据类型cursor：是游标型数据类型，用于创建游标变量或定义存储过程的输出参数。sql_variant：可以存储除文本、图形数据（text、ntext、image）和timestamp类型数据外的其它任何合法的SQLServer数据。table：用于存储对表或视图处理后的结果集。timestamp：相当于binary(8)或varbinary(8)，当定义的列在更新或插入数据行时，此列的值会被自动更新，每个数据库表中只能有一个TIMESTAMP数据列。uniqueidentifier：存储一个16位的二进制数字，由NEWID函数产生的惟一的编码。山东财政学院计算机信息工程学院九、用户自定义数据类型用户定义数据类型基于SQLServer2000中的系统数据类型。当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型。

用户自定义数据类型可使用存储过程sp_addtype所建立的数据类型，或企业管理器来实现。创建用户定义的数据类型必须提供3个参数：名称新数据类型所依据的系统数据类型为空性如果用户定义数据类型是在model数据库中创建的，它将作用于所有用户定义的新数据库中。如果数据类型在用户定义的数据库中创建，则该数据类型只作用于此用户定义的数据库。选中数据库并展开－右单击“用户定义的数据类型”Sp_addtypesp_addtype[@typename=]type,

[@phystype=]system_data_type

[,[@nulltype=]'null_type']

[,[@ower=]‘owner_name']

语法说明：[@typename=]type

：用户定义的数据类型的名称命名遵循标识符规则，并且在每个数据库中必须是唯一的。type

的数据类型为sysname，无默认值。[@phystype=]system_data_type

：自定义类型所基于的物理数据类型或SQLServer提供的数据类型。[@nulltype=]‘null_type‘：指明处理空值的方式。默认为null。

[,[@ower=]‘owner_name’]

：指定创建者或所有者，若没指定，则为当前用户。该存储过程返回0为成功，否则返回1例题execsp_addtypetelephone,'varchar(24)','notnull‘execsp_addtypefax,'varchar(24)','null'山东财政学院计算机信息工程学院表的创建数据库中包含一个或多个表。表是数据的集合，是用来存储数据和操作数据的逻辑结构。要对表中数据进行操纵（查询、插入、删除或修改表中的数据），应建立表的结构，建表的同时还应定义好相关的完整性约束。创建表－企业管理器山东财政学院计算机信息工程学院表的创建CREATETABLE[database_name.[owner].|owner.]table_name（{〈column_definition〉|〈table_constraint〉[，…n]}）[ON{filegroup|DEFAULT}]语法说明：table_name：新建的表的名称。Owner：是新表所有者的用户ID名，owner必须是database_name所指定的数据库中的现有用户ID，owner默认为与database_name所指定的数据库中的当前连接相关联的用户ID。语法说明在column_definition中的主要内容包括：列名、列类型、是否允许空值、初值和列级约束等。table_constraint指的是设定表级约束，包括主码约束、唯一约束和外码约束、检查等。ON{filegroup|DEFAULT}：指定存储表的文件组。如果指定filegroup，则表将存储在指定的文件组中。数据库中必须存在该文件组。如果指定DEFAULT，或者根本未指定ON参数，则表存储在默认文件组中。定义表的同时要定义各种约束域完整性：主要通过CHECK、DEFAULT、NOTNULL等。实体完整性：主要通过UNIQUE约束、PRIMARYKEY约束、IDENTITY属性约束。引用完整性：主要通过FOREIGNKEY约束。用户定义的完整性例

创建银行数据库中的六个表结构CREATETABLEBranch(branch_nameCHAR(15)PRIMARYKEY,branch_cityVARCHAR(20)NOTNULL,assetsDECIMAL(20,2)NOTNULL)CREATETABLECustomer(customer_idCHAR(7)PRIMARYKEYNONCLUSTERED,customer_nameCHAR(8)NOTNULL,customer_streetVARCHAR(20)NULL,customer_cityVARCHAR(20)NULL)山东财政学院计算机信息工程学院创建银行数据库中的六个表结构CREATETABLEAccount(account_numberCHAR(4)PRIMARYKEY,branch_nameCHAR(15)NOTNULLFOREIGNKEYREFERENCESBranch,balanceDECIMAL(10,2)NOTNULL)CREATETABLELoan(loan_numberCHAR(4)PRIMARYKEY,branch_nameCHAR(15)NOTNULLFOREIGNKEYREFERENCESBranch,amountDECIMAL(10,2)NOTNULL)创建银行数据库中的六个表结构CREATETABLEDeposits(customer_idCHAR(7)NOTNULLFOREIGNKEYREFERENCESCustomer,account_numberCHAR(4)NOTNULLFOREIGNKEYREFERENCESAccountPRIMARYKEY(customer_id,account_number))CREATETABLEBorrows(customer_idCHAR(7)NOTNULL,loan_numberCHAR(4)NOTNULLPRIMARYKEY(customer_id,loan_number))山东财政学院计算机信息工程学院表的修改ALTERTABLEtable_name

{[ALTERCOLUMNcolumn_name

{new_data_type

[(precision[,scale])][NULL|NOTNULL]}]|ADD{[<column_definition>]|ADD{<table_constraint>}[,…n]|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn}[,…n]语法说明ALTERCOLUMN用于修改表中原有列，可修改内容包括数据类型、大小和可空性。ADD{[<column_definition>]用于增加新的列。ADD{<table_constraint>}用于增加新的表级约束。DROP{[CONSTRAINT]constraint_name|COLUMNcolumn}用于删除约束或原有的列。例子例

修改客户Customer表的结构，改变说明列的数据类型。ALTERTABLECustomerALTERCOLUMNcustomer_nameCHAR(10)ALTERTABLECustomerALTERCOLUMNcustomer_cityCHAR(20)例

修改借贷Borrows表结构，新增外码约束。ALTERTABLEBorrowsADDFOREIGNKEY(customer_id)REFERENCESCustomerALTERTABLEBorrowsADDFOREIGNKEY(loan_number)REFERENCESLoan山东财政学院计算机信息工程学院例子例

修改客户Customer表结构，增加一新列phone。ALTERTABLECustomerADDphoneCHAR(10)例

修改客户Customer表结构，删除上面的新增列。ALTERTABLECustomerDROPCOLUMNphone完整性约束命名子句CONSTRAINT<完整性约束名>[PRIMARYKEY短语|FOREIGNKEY短语|CHECK短语]例createtablestudent(

snochar(5)primarykey,

snamechar(8),

ssexchar(2))altertablesaddconstraintc1check(ssex='男'orssex='女')例：去掉上例student表中对性别的限制。ALTERTABLESTUDENTDROPCONSTRAINTC1;山东财政学院计算机信息工程学院表的删除DROPTABLEtable_name[,…]语法说明：删除表操作会将表中所有数据删除，而且直接或间接地建立在该表上的视图及相关授权等与此表有关的内容也会被自动撤销。DROPTABLE不能用于删除由FOREIGNKEY约束引用的表，必须先删除引用的FOREIGNKEY约束或引用的表。在系统表上不能使用DROPTABLE语句。可使用DROPTABLE对多个表删除。例

删除在例3.5中建立的六个表。DROPTABLEBorrowsDROPTABLEDepositsDROPTABLELoanDROPTABLEAccountDROPTABLECustomer,Branch山东财政学院计算机信息工程学院3.2.3索引的创建索引（index）是关于数据位置信息的关键字表，由表中的一列或若干列字段值以及相应的指向表中物理标识这些值的数据页的逻辑指针构成。利用索引，系统可较快地在磁盘上定位所需数据，从而加快了数据查询的速度；通过在表中创建索引还可以保证数据记录的唯一性；此外，利用索引还可以加速表与表之间的连接操作。山东财政学院计算机信息工程学院一、索引的类型聚簇（clustered）索引非聚簇（nonclustered）索引复合索引唯一（unique）索引山东财政学院计算机信息工程学院1、聚簇索引磁盘上表的数据存储在相邻物理空间，并且表中行的物理顺序与索引的顺序一致。每张表最多只能建一个聚簇索引。合理地创建聚簇索引，可以十分显著地提高系统性能。当在一个表格中建立了聚簇索引后，在执行插入、修改、删除等操作时，系统要维护聚簇结构，开销比较大。山东财政学院计算机信息工程学院2、非聚簇索引与表中数据行的实际存储结构无关，在创建与使用时不会改变数据表中记录的实际存储顺序。非聚簇索引保存的是行指针，而不是数据页，因此检索速度不如聚簇索引快。在SQLServer2000中每个表最多能创建249个非聚簇索引。山东财政学院计算机信息工程学院3、复合索引对多个字段创建的索引。山东财政学院计算机信息工程学院4、唯一索引该索引要求被索引的列不能有相同值出现，可用来限制表中某些字段值的唯一性。山东财政学院计算机信息工程学院建索引时应遵循的原则经常做为条件查询的字段加上索引，如果几个字段同时做为查询时就建复合索引。在表的主键、外键上创建索引。在经常用于表间连接的字段上建立索引。建立聚簇索引若满足下列情况之一，可考虑建立聚簇索引，否则应建立非聚簇索引：检索数据时，常以某个属性（组）作为排序、分组条件。检索数据时，常以某个属性（组）作为检索限制条件，并返回大量数据。表格中某个属性（组）的值重复性较大。山东财政学院计算机信息工程学院山东财政学院计算机信息工程学院二、创建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_name

ONtable_name(column[ASC|DESC][,…n])语法说明：索引的建立和删除，必须要有DBA权限或是表的拥有者。UNIQUE表示建立唯一性的索引。CLUSTERED|NONCLUSTERED表示聚簇索引或非聚簇索引。ONtable表示为哪个表创建索引。column[ASC|DESC][,…n]用于指定索引基于的列和索引排序方式。例子CREATECLUSTEREDINDEXCustomer_nameONCustomer(customer_name)CREATEINDEXCustomer_addressONCustomer(customer_street,customer_city)CREATEINDEXAccount_branch_nameONAccount(branch_name)CREATEINDEXAccount_balanceONAccount(balance)山东财政学院计算机信息工程学院山东财政学院计算机信息工程学院三、删除索引DROPINDEX

index_name语法说明：只有索引的拥有者和具有DBA权限的用户，可以删除索引。索引删除后，有关索引的定义将从系统的数据字典（DD）中删除，并且包含在索引中的全部索引项将被清除。索引的删除，不会影响其他表和索引的正常使用，只会在某种程度上影响系统的性能。在数据定义语言中，没有索引的修改功能。要修改索引，可先删除要修改的索引，然后创建一个同名的索引。例删除在客户customer表的customer_name列上建立的索引DROPINDEXCustomer.Customer_name注意：在被删除索引名前应给出索引所属表名，中间用圆点分隔。山东财政学院计算机信息工程学院3.3数据查询3.3.1基本语法格式3.3.2单表查询3.3.3聚集函数3.3.4多表查询山东财政学院计算机信息工程学院3.3.1基本语法格式SELECT[distinct|all][TOP

n]select_list[INTO子句]FROMtable_source

[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]]山东财政学院计算机信息工程学院语法说明：TOPn：指定只从查询结果集中输出前n

行。select_list：描述进入结果集的列，是由逗号分隔的表达式的列表。INTO子句：创建新表并将结果行从查询插入新表中。table_source：用于指定产生查询结果的数据源。search_condition：用于从指定数据源中筛选元组。group_by_expression：分组的依据。search_condition：用于筛选分组。order_expression：排序的依据。山东财政学院计算机信息工程学院一、从单表中读取指定列例查询全体客户表中的客户标识和客户姓名。SELECTcustomer_id,customer_name

FROMCustomer例查询全体客户的全部信息。SELECT*FROMCustomer例查询发放贷款的支行名称。。SELECTDISTINCTbranch_name

FROMLoan山东财政学院计算机信息工程学院一、从单表中读取指定列（续）例

以万元为单位显示支行的资产额。格式一：SELECTbranch_name,branch_city,assets/10000FROMBranch格式二：SELECTbranch_name,branch_city,assets/10000AS'assets_tenthousand'FROMBranch格式三：SELECTbranch_namename,branch_citycity,assets/10000assets_tenthousand

FROMBranch山东财政学院计算机信息工程学院二、从单表中读取指定行例查询客户姓名为“刘伟”的客户信息。SELECT*FROMCustomerWHEREcustomer_name='刘伟'山东财政学院计算机信息工程学院常用的条件表达式运算符运算类型运算符比较=、>、<、>=、<=、<>、!=范围BETWEENAND，NOTBETWEENAND集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL逻辑AND，OR，NOT山东财政学院计算机信息工程学院例子-BETWEENAND例查询贷款金额在10000元以上的贷款信息。SELECT*FROMLoanWHEREamount>10000例查询存款金额在5000元至7000元之间(含5000元和7000元)的账号。格式一：SELECT*FROMAccountWHEREbalanceBETWEEN5000AND7000格式二：SELECT*FROMAccountWHEREbalance>=5000ANDbalance<=7000山东财政学院计算机信息工程学院例子-NOTBETWEENAND例查询存款金额不在5000元至7000元之间的账号。格式一：SELECT*FROMAccountWHEREbalanceNOTBETWEEN5000AND7000格式二：SELECT*FROMAccountWHEREbalance<5000ORbalance>7000山东财政学院计算机信息工程学院例子-IN例查询“解放支行”和“铁路支行”的账户信息。格式一：SELECT*FROMAccountWHEREbranch_nameIN('解放支行','铁路支行')格式二：SELECT*FROMAccountWHEREbranch_name='解放支行'OR

branch_name='铁路支行'山东财政学院计算机信息工程学院例子-NOTIN例查询不是“解放支行”和“铁路支行”的账户信息。格式一：SELECT*FROMAccountWHEREbranch_nameNOTIN('解放支行','铁路支行')格式二：SELECT*FROMAccountWHEREbranch_name<>'解放支行'AND

branch_name!='铁路支行'山东财政学院计算机信息工程学院例子-ISNULL、ISNOTNULL例查询没有登记所在街道的客户信息。ELECT*FROMCustomerWHEREcustomer_streetISNULL例查询所有登记了所在街道的客户信息。SELECT*FROMCustomerWHEREcustomer_street

ISNOTNULL字符串匹配

[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串匹配模板为含通配符的字符串%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab

等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串山东财政学院计算机信息工程学院例子例查询所有姓“刘”的客户信息。SELECT*FROMCustomerWHEREcustomer_nameLIKE'刘%'例查询所有不姓“刘”的客户信息。SELECT*FROMCustomerWHEREcustomer_name

NOTLIKE'刘%'例查询姓名中第二个字为“大”的顾客信息。SELECT*FROMCustomerWHEREcustomer_nameLIKE'_大%'例查询支行名称以“青岛_”打头的支行信息。SELECT*FROMBranchWHEREbranch_nameLIKE'青岛\_%'ESCAPE'\'山东财政学院计算机信息工程学院三、从单表中读取指定行和指定列例查询所在街道字段取值不为“空”的客户姓名。SELECTcustomer_name

FROMCustomerWHEREcustomer_streetISNOTNULL例查询“市中支行”的所有账号。SELECTaccount_number

FROMAccountWHEREbranch_name='市中支行'山东财政学院计算机信息工程学院四、对查询结果进行排序例显示资产额最多的5个支行信息。SELECTtop5*FROMBranchORDERBYassetsdesc例查询全部账户信息，查询结果按开户支行升序排列，相同开户支行情况下按账户余额降序排列。SELECT*FROMAccountORDERBYbranch_name

ASC,balanceDESC五、复制数据库表例将存款金额超过5000的存款信息复制到test表中。SELECT*INTOtestFROMAccountWHEREbalance>50003.3.3聚集函数COUNT({[ALL|DISTINCT]expression|*})统计记录个数SUM([ALL|DISTINCT]expression)计算指定表达式的总和AVG([ALL|DISTINCT]expression)计算指定表达式的平均值MAX([ALL|DISTINCT]expression)计算指定表达式中的最大值MIN([ALL|DISTINCT]expression)计算指定表达式中的最小值

注意：如果在聚集函数中指定DISTINCT关键词，则表示在计算时要取消指定表达式中的重复值，如果不指定DISTINCT关键词或ALL关键词(ALL为缺省值)，则表示不取消重表达式值。COUNT（*）返回所有记录的行数，其中包括NULL值的行，而COUNT（[ALL|DISTINCT]expression）返回的是表达式值不为NULL值的行数。

山东财政学院计算机信息工程学院一、使用聚集函数例查询全部客户记录个数。SELECTCOUNT(*)FROMCustomer例查询登记了所在街道的客户人数。SELECTCOUNT(customer_street)FROMCustomer例查询有支行的城市个数。SELECTCOUNT(DISTINCTbranch_city)FROMBranch例查询开户行是“市中支行”的账户余额总额。SELECTSUM(balance)FROMAccountWHEREbranch_name='市中支行'山东财政学院计算机信息工程学院例子例查询所有支行的平均资产额。SELECTAVG(assets)FROMBranch例查询所有支行中资产额最大的支行。SELECTMAX(assets)FROMBranch例查询所有支行中资产额最小的支行。SELECTMIN(assets)FROMBranch二、查询结果分组–GROUPBY子句使用GROUPBY子句分组 细化集函数的作用对象未对查询结果分组，集函数将作用于整个查询结果对查询结果分组后，集函数将分别作用于每个组对查询结果分组GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数

山东财政学院计算机信息工程学院查询结果分组例查询不同所在城市中支行的个数。SELECTbranch_city,COUNT(*)FROMBranchGROUPBYbranch_city

例统计每个城市中开设的所有支行的资产总额。SELECTbranch_city,SUM(assets)FROMBranchGROUPBYbranch_city例统计至少开设了两个支行的城市。SELECTbranch_city

FROMBranchGROUPBYbranch_city

HAVINGcount(*)>=2使用HAVING短语筛选最终输出结果只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

山东财政学院计算机信息工程学院3.3.4多表查询多表查询方法包括：嵌套子查询、相关子查询、集合查询、连接查询等。

山东财政学院计算机信息工程学院一、嵌套子查询嵌套子查询概念：一个查询语句的查询结果作为另一个查询语句的条件，则前者为“子查询”，亦称“嵌套子查询”（nestedsub-query），而后者为“主查询”，或称“外查询”。子查询的限制:不能使用ORDERBY子句分为：相关子查询和不相关子查询。不相关子查询:是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。相关子查询:首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询例查询有存款的客户姓名。-INSELECT

customer_name

外层查询/主查询FROM

CustomerWHERE

customer_id

IN

（SELECTcustomer_id

内层查询/子查询

FROMDeposits

）；例查询与栈桥支行所在城市相同的支行名称①确定“栈桥支行”所在城市

SELECTbranch_city

FROMBranchWHEREbranch_name=‘栈桥支行'； 结果为：

branch_city

青岛例查询与栈桥支行所在城市相同的支行名称②查找所在城市为“青岛”的支行名称。

SELECTbranch_name

FROMBranchWHEREbranch_city=‘青岛'；结果为：Branch_name栈桥支行市中支行青岛_和平支行构造嵌套查询将第一步查询嵌入到第二步查询的条件中

SELECTbranch_name

FROMBranchWHEREbranch_city

IN(SELECTbranch_city

FROMBranchWHEREbranch_name=‘栈桥支行')；此查询为不相关子查询。DBMS求解该查询时也是分步去做的。父查询和子查询中的表均可以定义别名SELECTbranch_name

FROMBranchb1WHEREb1.branch_cityIN(SELECTbranch_city

FROMBranchb2WHEREb2.branch_name=‘栈桥支行')；例查询在青岛的支行有存款的客户号--INSELECTcustomer_id

③最后在Deposits关系中

FROMDeposits取出customer_idWHEREaccount_numberIN(SELECTaccount_number

②然后在Account关系中

FROMAccount找出存款账号WHEREbranch_nameIN(SELECTbranch_name

①首先在Branch关系中找出“青

FROMbranch岛”的支行名称

WHEREbranch_city=‘青岛’));带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。与ANY或ALL谓词配合使用带有比较运算符的子查询例查询与栈桥支行所在城市相同的支行名称可以用=代替INSELECTbranch_name

FROMBranchWHEREbranch_city

=(SELECTbranch_city

FROMBranchWHEREbranch_name=‘栈桥支行')；带有ANY或ALL谓词的子查询谓词语义ANY：某个值ALL：所有值山东财政学院计算机信息工程学院比较运算符>ANY大于子查询结果中的某个值>ALL大于子查询结果中的所有值<ANY小于子查询结果中的某个值<ALL小于子查询结果中的所有值>=ANY大于等于子查询结果中的某个值>=ALL大于等于子查询结果中的所有值<=ANY小于等于子查询结果中的某个值<=ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值(通常没有实际意义)!=(或<>)ANY不等于子查询结果中的某个值!=(或<>)ALL不等于子查询结果中的所有值山东财政学院计算机信息工程学院例查询比所在城市是“济南”的所有支行资产额都大的支行名称。格式一：SELECTbranch_name

FROMBranchWHEREassets>ALL(SELECTassetsFROMBranchWHEREbranch_city=‘济南’)andbranch_city<>’济南’格式二：SELECT

branch_name

FROMBranchWHEREassets>(SELECT

MAX(assets)FROMBranchWHEREbranch_city='济南')andbranch_city<>’济南’山东财政学院计算机信息工程学院带有ANY或ALL的子查询可用集函数来实现

=!=或<><<=>>=ANYIN

<MAX<=MAX>MIN>=MINALL

NOTIN<MIN<=MIN>MAX>=MAX用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数山东财政学院计算机信息工程学院例查询比所在城市是“济南”的某个支行的资产额小的支行名称。格式一：SELECT

branch_name

FROMBranchWHEREassets<ANY(SELECTassetsFROMBranchWHEREbranch_city='济南')AND

branch_city<>'济南'格式二：SELECT

branch_name

FROMBranchWHEREassets<(SELECT

MAX(assets)FROMBranchWHEREbranch_city='济南')and

branch_city<>'济南'二、相关子查询相关子查询概念：是一种特殊的嵌套子查询，即子查询与来自主查询的列有关。首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。带有EXISTS谓词的子查询EXISTS谓词存在量词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义NOTEXISTS谓词例查询有存款的客户姓名。SELECT

customer_name

FROM

CustomercWHERE

EXISTS

（SELECT*

FROMDepositsd

WHEREc.customer_id=d.customer_id）带有EXISTS谓词的子查询思路分析：本查询涉及customer和deposits关系。在customer中依次取每个元组的customer_id值，用此值去检查deposits关系。若deposits中存在这样的元组，其customer_id值等于此customer.customer_id值，则取此customer.customer_name送入结果关系。例查询没有存款的客户姓名。SELECT

customer_name

FROM

CustomercWHERE

NOT

EXISTS

（SELECT*

FROMDepositsd

WHEREc.customer_id=d.customer_id）山东财政学院计算机信息工程学院例查询比所在城市是“济南”的所有支行资产额都大的支行名称。SELECTbranch_name

FROMBranchASAWHEREA.branch_city<>'济南'andNOTEXISTS(SELECT*FROMBranchASBWHEREB.branch_city='济南'and

B.assets>A.assets)带有EXISTS谓词的子查询不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

例查询与栈桥支行所在城市相同的支行名称SELECTbranch_name

FROMBranchb1WHEREEXISTS(SELECT*FROMBranchb2WHEREb2.branch_name=‘栈桥支行‘andb1.branch_city=b2.branch_city)；带有EXISTS谓词的子查询用EXISTS/NOTEXISTS实现全称量词SQL语言中没有全称量词

（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

(

x)P≡

(

x(

P))例查询在所有支行都有存款的客户号SELECTcustomer_idFROMdepositsdWHER