SQL语句基本概念及定义、更新操作

单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,*,SQL,语句基本概念及定义、更新操作,SQL,语言概述,结构化查询语言,SQL(Structured,Query Language),是一种包括数据定义、数据操纵和数据控制三方面功能的语言。由于它功能丰富、使用方式灵活、语言简洁易学等突出优点，在计算机工业界和计算机用户中备受欢迎。目前,SQL,语言已经被确定为关系数据库系统的国际标准，被绝大多数的商品化关系数据库系统所采用。,第一个,SQL,标准，由,ANSI(American,National Standards Institute,，美国国家标准协会,),于,1986,年,10,月制定，标准文本为,ANSI X3.135,一,1986,，简称为,SQL86,。,1989,年作了少许改进，推出的版本为,ANSI X3.1351989,，简称为,SQL89,。,1992,年，由,ANSI,和,ISO(International,Organization for Standardization,，国际标准化组织,),合作，对,SQL89,作了较大改动和完善，推出的版本为,ANSI X3.135,1992,，简称为,SQL92,，也被称之为,SQL2,，这是目前绝大多数商用,RDBMS,支持的版本。,1999,年推出,SQL99,，也被称作,SQL3,，表示是第三代,SQL,语言，是在,SQL92,的基础上扩展而成的。,目前大多数商用,RDBMS,都遵循,SQL92,的大部分特性，同时为了提高自身的系统性能，还提供了特定的、非,SQL,标准的功能。为此大家在使用不同的关系数据库管理系统时，需参照其相应的,RDBMS,技术手册。,下面将以微软的,SQL Server 2005,关系数据库系统为背景，来介绍,SQL,语言的一些基本特征。,SQL Server 2005,下的数据类型可以在“联机丛书”的索引中通过键入“数据类型”来查询其详细资料，下面给出实际应用中经常用到的数据类型：,bigint,从,-263 (-9223372036854775808),到,263-1 (9223372036854775807),的整型数据,(,所有数字,),。,int,从,-231 (-2,147,483,648),到,231 - 1 (2,147,483,647),的整型数据,(,所有数字,),。,smallint,从,-215 (-32,768),到,215 - 1 (32,767),数据。,tinyint,从,0,到,255,的整数数据。,decimal,从,-1038+1,到,1038-1,的固定精度和小数位的数字数据。,datetime,从,1753,年,1,月,1,日到,9999,年,12,月,31,日的日期和时间数据，精确到百分之三秒,(,或,3.33,毫秒,),。,smalldatetime,从,1900,年,1,月,1,日到,2079,年,6,月,6,日的日期和时间数据，精确到分钟。,char,固定长度的非,Unicode,字符数据，最大长度为,8,，,000,个字符。,varchar,可变长度的非,Unicode,数据，最长为,8,000,个字符。,SQL Server 2005,下的常用数据类型,SQL Server 2005,下的常用函数,SQL Server 2005,下的函数可以在“联机丛书”的索引中通过键入“函数”来查询其详细资料，下面给出实际应用中经常用到的函数,(,注意有些函数的参数是以字为单位的,),：,convert,数值类型转换函数,left,返回字符串左边指定数目的子字符串,right,返回字符串右边指定数目的子字符串,len,返回字符串中包含字符的数目 例子：,len,(,张三丰,),len(abc,),lower,将字符串中的字母转为小写,upper,将字符串中的字母转为大写,ltrim,删除字符串左边的所有空格,rtrim,删除字符串右边的所有空格,str,将数值型数据转换为字符串,substring,返回一个字符串指定位置的子串,dateadd,返回给定日期值增加一个时间间隔后的日期,datediff,返回两个日期之间的时间间隔,datename,返回日期的文本表示方式,datepart,返回某数值对应的日期值,day,返回日期值中天数的整数值,getdate,返回当前系统日期,month,返回日期值中月份的整数值,year,返回日期值中年份的整数值,SQL,的数据定义,关系模型中的关系，在,RDBMS,中被“具体化”为“表”。要对表中数据进行操纵,(,查询、插入、删除或修改表中的数据,),，首先应建立表的结构，即“表格”。附带地，还应定义好相关的完整性约束。下面将介绍建立和维护表结构的命令及语法。,创建表,1.,利用,SQL Server,管理平台创建表,在,SQL Server,管理平台中，展开指定的服务器和数据库，打开想要创建新表的数据库，右击表对象，并从弹出的快捷菜单中选择“新建表”选项，如图,5-4,所示。在图,5-4,的对话框中，可以对表的结构进行更改，设置主键及字段属性，使用,SQL Server,管理平台可以非常直观地修改数据库结构和添加数据。在表中任意行上右击，则弹出一个快捷菜单，如图,5-6,所示。,设置字段属性对话框,新建表对话框,创建表,2.,利用,create,命令创建表,使用,create,命令创建表非常灵活，它允许对表设置几种不同的选项，包括表名、存放位置和列的属性等。,命令语法：,create table ,database_name,.,owner,. |,owner,. ,table_name,( ,column_definition,|,table_constraint, ,.n),语法说明：,（,1,）在,column_definition,中的主要内容包括：列名、列类型、是否允许空值、 初值和列级约束等；,（,2,）,table_constraint,指的是设定表级约束，包括主键约束、唯一约束和外键约束等。,创建表,2.,利用,create,命令创建表,其完整语法形式如下：,CREATE TABLE,database_name,.,owner,.,|owner,.,table_name,(,|,column_name,AS,computed_column_expression,|,，,n),ON,filegroup|DEFAULT,TEXTIMAGE_ON ,filegroup|DEFAULT,:=,column_name,data_type,COLLATE ,DEFAULT,constant_expression, |,IDENTITY(seed,increment,)NOT FOR REPLICATION,ROWGUIDCOL,.n,其中，各参数的说明如下：,database_name,：用于指定所创建表的数据库名称。,owner,：用于指定新建表的所有者的用户名。,table_name,：用于指定新建表的名称。,column_name,：用于指定新建表的列名。,computed_column_expression,：用于指定计算列的列值表达式。,column_definition,中的主要内容包括：列名、列类型、是否允许空值、初值和列级约束等；,table_constraint,指的是设定表级约束，包括主键约束、唯一约束和外键约束等。,column_constraint,：用于指定列约束,ON ,filegroup,| DEFAULT,：用于指定存储表的文件组名。,TEXTIMAGE_ON,：用于指定,text,、,ntext,和,image,列的数据存储的文件组。,data_type,：用于指定列的数据类型。,DEFAULT,：用于指定列的默认值。,constant_expression,：用于指定列的默认值的常量表达式、可以为一个常量或,NULL,或系统函数。,IDENTITY,：用于将列指定为标识列。,Seed,：用于指定标识列的初始值。,Increment,：用于指定标识列的增量值。,NOT FOR REPLICATION,：用于指定列的,IDENTITY,属性，在把从其他表中复制的数据插入到表中时不发生作用，即不生成列值，使得复制的数据行保持原来的列值。,ROWGUIDCOL,：用于将列指定为全局惟一标识行号列,(row global unique identifier column),。,COLLATE,：用于指定表的校验方式。,创建表,2.,利用,create,命令创建表,T,able_name,为新建立的表名。对于临时表，表名字符串长度不能超过,116,个字符，而永久表的表名字符串长度则不能超过,128,个字符。此外，在同一个数据库中，每个表所有者创建的表名必须保证唯一。,computed_column_expression,指出计算列的定义表达式，计算列是一个虚拟列，它并不是存储在表中，而是由表中的其它非计算列（常规列）导出。计算列定义表达式可以为常规列、常量、变量、函数组成的表达式，但它不能由一个子查询构成。除了下面情况，计算列可以与常规列一样使用在,SELECT,列表、,WHERE,子句和,ORDER BY,子句中： 计算列不能用在索引的关键词列； 计算列不能作为,PRIMARY KEY,、,UNIQUE,、,FOREIGN KEY,或,DEFAULT,约束定义的一部分； 计算列不能用,INSERT,和,UPDATE,语句插入资料。 注意： 在建表语句中，不允许对计算列设置空值属性（,NULL,或,NOT NULL,）,Create table student(,sno,char(8) not null,name char(10) not null,namelen,as,len(name,),sex char(2) not null,department char(20),constraint,pk_id,primary,key(sno,),),创建表,2.,利用,create,命令创建表,示例：创建了一个学生信息表,student,，包括学号,(,主键,),、姓名、性别、院系,Create table student(,sno,char(8) not null,name char(10) not null,sex char(2) not null,department char(20),constraint,pk_id,primary,key(sno,),),作业,1,：创建了一个工人信息表,worker,，它包括工人编号,(,主键,),、姓名、性别、出生时间、职位、工资和备注信息。备注：详细要求参考下表。,列名,数据类型,是否为空,备注,number,char,(8),否,工人编号,name,char,(8),否,姓名,sex,char,(2),否,性别,birthday,datetime,否,出生时间（精确到时分）,Job_title,char,(12),是,职位,salary,decimal(7,2,),否,工资,memo,V,archar(255),是,备注信息,创建约束,约束是,SQL Server,提供的自动保持数据库完整性的一种方法，它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。在,SQL SERVER,中，对于基本表的约束分为列约束和表约束。,列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用,分隔，定义表约束时必须指出要约束的那些列的名称。,完整性约束的基本语法格式为：,CONSTRAINT,constraint_name,(,约束名,) ,约束不指定名称时，系统会给定一个名称。,在,SQL Server 2005,中有,6,种约束：主键约束,(primary key constraint),、惟一性约束,(unique constraint),、检查约束,(check constraint),、默认约束,(default constraint),、外部键约束,(foreign key constraint),和空值,(NULL),约束。,创建约束,1.,主键,(PRIMARY KEY),约束,PRIMARY KEY,约束用于定义基本表的主键，它是惟一确定表中每一条记录的标识符，其值不能为,NULL,，也不能重复，以此来保证实体的完整性。,PRIMARY KEY,与,UNIQUE,约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别：,在一个基本表中只能定义一个,PRIMARY KEY,约束，但可定义多个,UNIQUE,约束；,对于指定为,PRIMARY KEY,的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于,UNIQUE,所约束的唯一键，则允许为空。,注意：不能为同一个列或一组列既定义,UNIQUE,约束，又定义,PRIMARY KEY,约束。,PRIMARY KEY,既可用于列约束，也可用于表约束。,创建约束,1.,主键,(PRIMARY KEY),约束,主键的创建操作方法有两种：,SQL Server,管理平台操作法和,Transact-SQL,语句操作法。,(1) SQL Server,管理平台操作法，如下图所示。,创建约束,1.,主键,(PRIMARY KEY),约束,(2),使用,Transact-SQL,语句操作法设置主键约束，其语法形式如下：,CONSTRAINT,constraint_name,PRIMARY KEY CLUSTERED|NONCLUSTERED (,column_name,),PRIMARY KEY,用于定义表约束时，即将某些列的组合定义为主键，其语法格式如下：,CONSTRAINT,constraint_name,PRIMARY KEY CLUSTERED|NONCLUSTERED,(,column_name,n),示例：建立一个,sc,表，定义,cno,为主键,create table sc (,cno char(5) not null,sno char(5) not null,score numeric(3),constraint sc_prim primary key(cno),),作业,2,：创建工人信息表,(,作业,1,),，定义工人编号、姓名共同组成主键。,创建约束,2.,惟一性约束,惟一性约束用于指定一个或者多个列的组合值具有惟一性，以防止在列中输入重复的值。定义了,UNIQUE,约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。,当使用惟一性约束时，需要考虑以下几个因素：,使用惟一性约束的字段允许为空值；,一个表中可以允许有多个惟一性约束；,可以把惟一性约束定义在多个字段上；,惟一性约束用于强制在指定字段上创建一个惟一性索引；,默认情况下，创建的索引类型为非聚集索引。,创建约束,2.,惟一性约束,创建惟一性约束的方法有两种：通过,SQL Server,管理平台可以完成创建和修改惟一性约束的操作；使用,Transact-SQL,语句完成惟一性约束的操作。,(1),通过,SQL Server,管理平台可以完成创建和修改惟一性约束的操作，如下图所示。,创建约束,2.,惟一性约束,创建惟一性约束的方法有两种：通过,SQL Server,管理平台可以完成创建和修改惟一性约束的操作；使用,Transact-SQL,语句完成惟一性约束的操作。,(2),使用,Transact-SQL,语句完成惟一性约束的操作，其语法形式如下：,CONSTRAINT,constraint_name,UNIQUE CLUSTERED|NONCLUSTERED,(,column_name,n),示例：创建一个学生信息表，其中,name,字段具有惟一性。,create table student(,sno,char(8) not null,name char(10) not null,constraint,uk_identity,unique(name,) ,sex char(2) not null,department char(20),constraint,pk_id,primary,key(sno,),),作业,3,：创建,工人信息表,(,作业,1,),定义,工人编号为主键，姓名字段具有惟一性。,创建约束,3.,检查约束,检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。,当使用检查约束时，应该考虑和注意以下几点：,一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关；,一个表中可以定义多个检查约束；,每个,CREATE TABLE,语句中每个字段只能定义一个检查约束；,在多个字段上定义检查约束，则必须将检查约束定义为表级约束；,当执行,INSERT,语句或者,UPDATE,语句时，检查约束将验证数据；,检查约束中不能包含子查询。,创建约束,3.,检查约束,创建检查约束常用的操作方法有如下两种 ：使用,SQL Server,管理平台创建检查约束；用,Transact-SQL,语句创建检查约束。,(1),使用,SQL Server,管理平台创建检查约束，如下图所示。,创建约束,3.,检查约束,(2),用,Transact-SQL,语句创建检查约束。,用,Transact-SQL,语句创建检查约束。其语法形式如下：,CONSTRAINT,constraint_name,CHECK NOT FOR REPLICATION (,logical_expression,),示例：建立一个,sc,表，定义,score,的取值范围为,0,到,100,之间。,create table sc (,cno,char(5),sno,char(5),score numeric(5,1),constraint,score_chk,check (score=0 and score =100),),作业,4,：创建工人信息表,(,作业,1,),，定义工资的取值范围为,3000,到,5000,之间。,创建约束,4.,默认,(DEFAULT),约束,默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。默认约束可以包括常量、函数、不带变元的内建函数或者空值。,使用默认约束时，应该注意以下几点：,(1),每个字段只能定义一个默认约束；,(2),如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断；,(3),不能加入到带有,IDENTITY,属性或者数据类型为,timestamp,（时间戳）的字段上；,(4),如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个数据类型上，则不允许该字段有默认约束。,创建约束,4.,默认,(DEFAULT),约束,创建默认约束常用的操作方法有如下两种：使用,SQL Server,管理平台创建默认约束；创建默认约束的,Transact-SQL,语句操作法。,(1),使用,SQL Server,管理平台创建默认约束，如下图所示。,创建约束,4.,默认,(DEFAULT),约束,(2),创建默认约束的,Transact-SQL,语句操作法。其语法形式如下：,CONSTRAINT,constraint_name,DEFAULT,constraint_expression,FOR,column_name,示例：创建一个学生信息表，默认,score,为零、,dept,为计算机。,create table student(,sno,char(8),name char(10),sex char(2),score decimal(4,2) not null,constraint,df_student_score,default 0,department char(20) not null,constraint,df_student_dept,default,计算机,),作业,5,：创建工人信息表,(,作业,1,),，定义工资默认值为,3000,、职位为,经理,。,创建约束,5.,外部键约束,外键,(FOREIGN KEY),是用于建立和加强两个表数据之间的链接的一列或多列。外部键约束用于强制参照完整性。,当使用外部键约束时，应该考虑以下几个因素：,外部键约束提供了字段参照完整性；,外部键从句中的字段数目和每个字段指定的数据类型都必须和,REFERENCES,从句中的字段相匹配；,外部键约束不能自动创建索引，需要用户手动创建；,一个表中最多可以有,31,个外部键约束；,在临时表中，不能使用外部键约束；,主键和外部键的数据类型必须严格匹配。,创建约束,5.,外部键约束,创建外部键约束常用的操作方法有如下两种：在,SQL Server,管理平台中添加外部键约束；使用,Transact-SQL,语句设置外部键约束。,(1),在,SQL Server,管理平台中添加外部键约束，在,SQL Server,管理平台中添加外部键约束。如下图所示。,创建约束,5.,外部键约束,(2),使用,Transact-SQL,语句设置外部键约束 ，其语法形式如下：,CONSTRAINT,constraint_name,FOREIGN KEY (,column_name,n),REFERENCES,ref_table,(,ref_column,n),例,5-11,建立一个,sc,表，定义,sno,为,sc,的外部键。,create table sc (,c,no char(8),not null,sno char(8) not null,constraint s_fore foreign key references student(sno),score numeric(3),constraint s_c_prim primary key (,c,no ),),修改表,当数据库中的表创建完成后，可以根据需要改变表中原先定义的许多选项，以更改表的结构。用户可以增加、删除和修改列，增加、删除和修改约束，更改表名以及改变表的所有者等。,命令语法：,alter table,table_name,alter column,column_name,data_type, (,precision,scale,) null | not null ,| add,column_definition,|,table_constraint, ,.n,| drop column,column_name,| constraint ,constraint_name, ,.n,语法说明：,(1)alter column,用于修改表中原有列，可修改内容包括数据类型、大小和可空性；,(2)add,column_definition,|,table_constraint,用于增加新的列或新的表级约束；,(3)drop column,column_name,| constraint ,constraint_name,用于删除原有列或原有约束。,修改表,示例：创建一个雇员信息表，然后在表中增加一个,salary,jointime,字段，删除表中的,age,字段，并且修改,memo,字段的数据类型。,create table employees(,id char(8) primary key,name char(20) not null,department char(20) null,memo char(30) null,age,int,null,),alter table employees add salary,decimal(8,2) not null,jointime,datetime,not null,alter table employees drop column age,alter table employees alter column memo varchar(200) null,作业,6,：在工人信息表,(,作业,1,),中增加地址,(address),、补贴（,subsidy,）、入厂 时间（,jointime,）列，要求补贴一列默认值为,2000,；删除表中的职位字段；修改备注字段的数据类型；改变表的主键为工人编号和姓名的联合主键。,删除表,命令语法：,drop table,table_name,语法说明：,要删除的表如果不在当前数据库中，则应在,table_name,中指明其所属的数据库和用户名（,drop table,database.dbo.table_name,）。在删除一个表之前要先删除与此表相关联的表中的外部关键字约束。当删除表后，绑定的规则或者默认值会自动松绑。,删除表操作会将表中所有数据删除，而且直接或间接地建立在该表上的视图，及相关授权等与此表有关的内容也会被自动撤销。由于其破坏性很大，因此在执行这类命令时，一定要非常小心。,示例：删除,employees,表,drop table employees,索引,索引（,index,）是关于数据位置信息的关键字表，是数据库系统中的数据存取方法之一。利用索引，系统可较快地在磁盘上定位所需数据，而不需要在磁盘上从头到尾或从后往前，一个数据一个数据地匹配和查找，从而加快了数据查询的速度。索引的类型有：聚集（,clustered,）索引、非聚集（,nonclustered,）索引、唯一（,unique,）索引和普通索引。,聚集索引：磁盘上表中数据与索引存储在相邻物理空间，并且表中行的物理顺序与索引的顺序一致。也就是说，表中的记录是按索引顺序存放的，表中数据一有变化，系统均须对表中数据重新排序。由于表中的数据在磁盘上是按索引顺序存放的，因此每张表最多只能建一个聚集索引。,非聚集索引：与聚集索引不同，它不要求表中行的物理顺序与索引的顺序一致。,唯一索引：该索引要求被索引的列不能有相同值出现。唯一索引可用来限定聚集索引和非聚集索引，如唯一的聚集索引、唯一的非聚集索引，表示限定这两类索引所索引的列不能有相同的值。这类索引适合于限定基于主键或候选键的聚簇或非聚集索引。,普通索引：与唯一索引不同，普通索引允许在被索引列上的值出现重复。,可以通过在查询条件中经常涉及的属性上建立各类索引以提高查询速度，到底在哪些列上建立索引，以及建立什么类型的索引，应当根据实际应用的情况来决定。比如可以在查询实时性要求较高的条件所涉及的属性上建立聚集索引，以达到最佳查询速度。,一但建立好索引，系统会自动引用已存在的索引来加快查询速度，无须也不允许用户指明对索引的使用。应适当建立索引，索引会提高查询速度，但也会增加数据更新时的响应时间。索引除了可以在定义表时通过,primary key,、,unique,等参数由系统自动创建之外，也可以通过,create index,命令单独创建,。,创建索引,命令语法：,create unique clustered |,nonclustered, index,index_name,on,table_name,( ,column_name,asc,|,desc, ,.n ),语法说明：,（,1,）索引的建立和删除，必须要有,DBA,权限或者是表的拥有者；,（,2,）,unique,表示建立唯一性的索引；,（,3,）,clustered |,nonclustered,表示聚集索引或非聚集索引；,（,4,）,on,table_name,表示为哪个表创建索引；,（,5,）,column_name,asc,|,desc,用于指定索引基于的列和索引排序方式。,一个索引中最多可以指定,16,个列，但列的数据类型的长度和不能超过,900,个字节。,注意：数据类型为,TEXT,、,NTEXT,、,IMAGE,或,BIT,的列不能作为索引的列。由于索引的宽度不能超过,900,个字节，因此数据类型为,CHAR,、,VARCHAR,、,BINARY,和,VARBINARY,的列的列宽度超过了,900,字节，或数据类型为,NCHAR,、,NVARCHAR,的列的列宽度超过了,450,个字节时也不能作为索引的列。,在使用索引创建向导创建索引时，不能将计算列包含在索引中，但在直接创建或使用,CREATE INDEX,命令创建索引时，则可以对计算机列创建索引。,create index,pk_p_main,on,products(p_id,p_name,sumvalue,) -,其中,sumvalue,是一个计算列表达式为,price*quantity,示例：在,employees,中的有关列上建立索引。,create unique index,employees,_name,on,employees,(name,),create index,employees,_,department,on,employees,(,department,),作业,7,：给工人信息表,(,作业,1,),的姓名列建立唯一性索引。,创建索引,完整命令语法：,CREATE UNIQUE CLUSTERED | NONCLUSTEREDINDEX,index_name,ON table | view column ASC | DESC ,.n)WITHPAD_INDEX , FILLFACTOR =,fillfactor, , IGNORE_DUP_KEY , DROP_EXISTING , STATISTICS_NORECOMPUTE , SORT_IN_TEMPDB ON,filegroup,各参数说明如下：,UNIQUE,创建一个惟一索引，即索引的键值不重复。在列包含重复值时，不能建惟一索引。如要使用此选项，则应确定索引所包含的列均不允许,NULL,值，否则在使用时会经常出错。,CLUSTERED,指明创建的索引为簇索引。如果此选项缺省，则创建的索引为非簇索引。,NONCLUSTERED,指明创建的索引为非簇索引其索引。数据页中包含了指向数据库中实际的表数据页的指针。,index_name,指定所创建的索引的名称。索引名称在一个表中应是惟一的，但在同一数据库或不同数据库中可以重复。,table,指定创建索引的表的名称。必要时还应指明数据库名称和所有者名称。,view,指定创建索引的视图的名称。视图必须是使用,SCHEMABINDING,选项定义过的，其具体信息请参见“视图创建”章节。,ASC | DESC,指定特定的索引列的排序方式。默认值是升序（,ASC,）。,column,指定被索引的列。如果使用两个或两个以上的列组成一个索引，则称为复合索引。,创建索引,PAD_INDEX,指定填充索引的内部节点的行数，至少应大于等于两行。,PAD_INDEX,选项只有在,FILLFACTOR,选项指定后才起作用。因为,PAD_INDEX,使用与,FILLFACTOR,相同的百分比。缺省时，,SQL Server,确保每个索引页至少有能容纳一条最大索引行数据的空闲空间。如果,FILLFACTOR,指定的百分比不够容纳一行数据,S,，,QL Server,会自动内部更改百分比。,FILLFACTOR =,fillfactor,FILLFACTOR,称为填充因子，它指定创建索引时，每个索引页的数据占索引页大小的百分比，,fillfactor,的值为,1,到,100,。它其实同时指出了索引页保留的自由空间占索引页大小的百分比。即,100 -,fillfactor,。 对于那些频繁进行大量数据插入或删除的表在建索引时应该为将来生成的索引数据预留较大的空间，即将,fillfactor,设得较小，否则，索引页会因数据的插入而很快填满，并产生分页，而分页会大大增加系统的开销。但如果设得过小，又会浪费大量的磁盘空间，降低查询性能。因此，对于此类表通常设一个大约为,10,的,fillfactor,。 而对于数据不更改的、高并发的、只读的表，,fillfactor,可以设到,95,以上乃至,100,。 如果没有指定此选项，,SQL Server,默认其值为,0,。,0,是个特殊值，与其它小,FILLFACTOR,值（如：,1,，,2,）的意义不同，其叶节点页被完全填满，而在索引页中还有一些空间。可以用存储过程,Sp_configure,来改变默认的,FILLFACTOR,值。,IGNORE_DUP_KEY,此选项控制了当往包含于一个惟一约束中的列中插入重复数据时,SQL Server,所作的反应。当选择此选项时，,SQL Server,返回一个错误信息，跳过此行数据的插入，继续执行下面的插入数据的操作：当没选择此选项时，,SQL Server,不仅会返回一个错误信息，还会回滚（,Rolls Back,）整个,INSERT,语句（关于回滚，请参见“数据库更新”章节中的“事务”一节）。,DROP_EXISTING,指定要删除并重新创建簇索引。删除簇索引会导致所有的非簇索引被重建，因为需要用行指针来替换簇索引键。如果再重建簇索引，那么非簇索引又会再重建一次，以便用簇索引键来替换行指针。使用,DROP_EXISTING,选项可以使非簇索引只重建一次。,STATISTICS_NORECOMPUTE,指定分布统计不自动更新。需要手动执行不带,NORECOMPUTE,子句的,UPDATESTATISTICS,命令。,SORT_IN_TEMPDB,指定用于创建索引的分类排序结果将被存储到,Tempdb,数据库中。如果,Tempdb,数据库和用户数据库位于不同的磁盘设备上，那么使用这一选项可以减少创建索引的时间，但它会增加创建索引所需的磁盘空间。,ON,filegroup,指定存放索引的文件组。,创建索引,索引的优点和不足,索引有一些先天不足,1,、系统要占用大约为表的,1.2,倍的硬盘和内存空间来保存索引；,2,、更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。,当然建立索引的优点也是显而易见的，在海量数据的情况下，如果合理的建立了索引，则会大大加强,SQLS,执行查询、对结果进行排序、分组的操作效率。,实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引要花费更多的系统时间。,在如下字段建立索引应该是不恰当的：,1,、很少或从不引用的字段；,2,、逻辑型的字段，如男或女,(,是或否,),等。,综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，必须要有统筹的规划，一定要在“加快查询速度”与“降低修改速度”之间做好平衡。,索引使用原则：,1,逻辑主键使用惟一的成组索引，对系统键（作为存储过程）采用惟一的非成组索引，对任何外键列采用非成组索引。考虑数据库的空间有多大，表如何进行访问，还有这些访问是否主要用作读写；,2,不要索引,memo/note,字段，不要索引大型字段（有很多字符），这样作会让索引占用太多的存储空间；,3,不要索引常用的小型表；,4,一般不要为小型数据表设置过多的索引，如果经常有插入和删除操作就更不要设置索引，因为,SQLS,对插入和删除操作提供的索引维护可能比扫描表空间消耗的时间更多。,创建索引,类别,对比项,主键,聚集索引,用途,强制表的实体完整性,对数据行的排序，方便查询用,一个表多少个,一个表最多一个主键,一个表最多一个聚集索引,是否允许多个字段来定义,一个主键可以多个字段来定义,一个索引可以多个字段来定义,是否允许,null,数据行出现,如果要创建的数据列中数据存在,null,，无法建立主键。创建表时指定的,PRIMARY KEY,约束列隐式转换为,NOT NULL,。,没有限制建立聚集索引的列一定必须,not null .,也就是可以列的数据是,null,参看最后一项比较,是否要求数据必须唯一,要求数据必须唯一,数据即可以唯一，也可以不唯一。看你定义这个索引的,UNIQUE,设置。（这一点需要看后面的一个比较，虽然你的数据列可能不唯一，但是系统会替你产生一个你看不到的唯一列）,创建的逻辑,数据库在创建主键同时，会自动建立一个唯一索引。如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引,如果未使用,UNIQUE,属性创建聚集索引，数据库引擎 将向表自动添加一个四字节,uniqueifier,列。必要时，数据库引擎 将向行自动添加一个,uniqueifier,值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。,主键和聚集索引的对比：,删除索引,命令语法：,drop index,table_name,.,index_name,语法说明：,（,1,）只有索引的拥有者和具有,DBA,权限的用户，可以删除索引。索引删除后，有关索引的定义将从系统的数据字典（,DD,）中删除，并且包含在索引中的全部索引项将被清除。索引的删除，不会影响其它表和索引的正常使用，只会在某种程度上影响系统的性能。,（,2,）在数据定义语言中，没有索引的修改功能。要修改索引，可以先删除要修改的索引，然后再创建一个同名的索引。,备注：,（,1,）索引的作用在于提高查询操作速度，但是这是以增加存储空间和降低数据更新效率为代价的，因此应删除那些不被引用或不常被引用的索引。,（,2,）在被删除索引名前必须给出索引所属表名，中间用圆点分隔。,示例：删除,employees,表中,name,列上的索引。,drop index,employees,.,employees,_name,作业,8,：删除工人信息表,(,作业,1,),姓名列上的索引。,SQL,的数据更新,利用,DDL,数据定义语言建立起数据结构和完整性约束后，就可以利用,DML,数据操纵语言对数据库进行“插入”、“修改”和“删除”三种数据更新操作。,1.,数据插入,命令语法：,格式一：,insert into,table_name,|,view_name,(,column_list,) values,(,data_values,),格式二：,insert into,table_name,|,view_name,(,column_list,),select_statement,语法说明：,（,1,）如果有列名表“,column_list,”,，则要求其后的数据在顺序、个数和类型上，应与其一一对应；如果没有列名表，则要求插入的数据在顺序、个数和类型上，应与表或视图定义中的列一一对应。,（,2,）插入数据时，在列名表“,column_list,”,中没有出现列的值为空值。但是，这类插入空值的列不能具有,not null,属性，否则系统将拒绝插入。,（,3,）格式一为单行数据插入方式，一次只能插入一行记录。,（,4,）格式二为集合数据插入方式，一次可插入一行或多行记录。,SQL,的数据更新,利用,DDL,数据定义语言建立起数据结构和完整性约束后，就可以利用,DML,数据操纵语言对数据库进行“插入”、“修改”和“删除”三种数据更新操作。,1.,数据插入,示例：创建,employees,表，并给其中插入如下数据。,create table employees(,id char(8) primary key,name char(20) not null,department char(20) null,age,int,null,salary decimal(6,2) not null,jointime,datetime,not null,memo char(30) null,),insert into employees values,(11080301, ,张三, ,工程处,36,3888.88, ts 1999-12-01 15:38:00.000, ,主任,),insert into employees values,(11080302, ,李四, ,工程处, null ,3666.88, ts 1998-09-01 15:38:00.000, ,副主任,),SQL,的数据更新,1.,数据插入,作业,9,：给工人信息表,(,作业,1,),插入下表中的数据。,作业,10,：新建工人信息表,1,（结构同工人信息表），使用了第二种插入数据的格式把工人信息表的数据全部插入到工人信息表,1,。,number,name,sex,birthday,Job_title,salary,memo,11080301,张飞,男,1981/10/12 15:32,总经理,6000.36,喜欢台球,11080302,李逵,男,1979/11/12 05:36,销售总监,5666.66,11080303,黑格尔,男,1995/01/12 21:06,5000,博士,11080304,刘青青,女,1995/09/12 01:06,秘书,3000.36,11080305,孙格格,女,1990/09/12 01:06,3666,SQL,的数据更新,2.,数据修改,命令语法：,update,table_name,|,view_name,set ,column_name,=,expression,| null,.n, where,search_condition,语法说明：,（,1,）,update,后的表名和视图名，是指要进行修改操作的表或视图。,（,2,）“,set”,子句中的表达式，除了有一般意义上的表达式外，还可以将“,select,子查询”作为表达式。,（,3,）如果没有,where,子句，则将修改表中的所有数据，否则只修改满足,search_condition,查询条件的记录。,示例：修改,employees,，把记录中,name,为李四的,department,修改动力处。,update employees set department =,动力处, where name =,李四,作业,11,：修改工人信息表,(,作业,1,),，把记录中姓名为黑格尔的工资修改为,5606.23,。,SQL,的数据更新,3.,数据删除,命令语法：,delete from,table_name,|,view_name, where,search_condition,语法说明：,（,1,）,delete,后的表名和视图名，是指要进行删除操作的表或视图。,（,2,）如果没有,where,子句，则将删除表中的所有数据，否则只删除满足,search_condition,查询条件的记录。,示例：删除,employees,中,name,为张三的记录。,delete employees where name =,张三,作业,12,：删除工人信息表,(,作业,1,),中性别为男性的记录。,Thank You !,