SQL語句基本概念及定義、更新操作

SQL語句基本概念及定義、更新操作

SQL語言概述結構化查詢語言SQL(StructuredQueryLanguage)是一種包括數(shù)據定義、數(shù)據操縱和數(shù)據控制三方面功能的語言。由于它功能豐富、使用方式靈活、語言簡潔易學等突出優(yōu)點，在計算機工業(yè)界和計算機用戶中備受歡迎。目前SQL語言已經被確定為關系數(shù)據庫系統(tǒng)的國際標準，被絕大多數(shù)的商品化關系數(shù)據庫系統(tǒng)所采用。第一個SQL標準，由ANSI(AmericanNationalStandardsInstitute，美國國家標準協(xié)會)于1986年10月制定，標準文本為ANSIX3.135一1986，簡稱為SQL86。1989年作了少許改進，推出的版本為ANSIX3.135—1989，簡稱為SQL89。1992年，由ANSI和ISO(InternationalOrganizationforStandardization，國際標準化組織)合作，對SQL89作了較大改動和完善，推出的版本為ANSIX3.135－1992，簡稱為SQL92，也被稱之為SQL2，這是目前絕大多數(shù)商用RDBMS支持的版本。1999年推出SQL99，也被稱作SQL3，表示是第三代SQL語言，是在SQL92的基礎上擴展而成的。目前大多數(shù)商用RDBMS都遵循SQL92的大部分特性，同時為了提高自身的系統(tǒng)性能，還提供了特定的、非SQL標準的功能。為此大家在使用不同的關系數(shù)據庫管理系統(tǒng)時，需參照其相應的RDBMS技術手冊。下面將以微軟的SQLServer2005關系數(shù)據庫系統(tǒng)為背景，來介紹SQL語言的一些基本特征。

SQLServer2005下的數(shù)據類型可以在“聯(lián)機叢書”的索引中通過鍵入“數(shù)據類型”來查詢其詳細資料，下面給出實際應用中經常用到的數(shù)據類型：

bigint從-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型數(shù)據(所有數(shù)字)。

int從-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型數(shù)據(所有數(shù)字)。

smallint從-2^15(-32,768)到2^15-1(32,767)數(shù)據。

tinyint從0到255的整數(shù)數(shù)據。

decimal從-10^38+1到10^38-1的固定精度和小數(shù)位的數(shù)字數(shù)據。

datetime從1753年1月1日到9999年12月31日的日期和時間數(shù)據，精確到百分之三秒(或3.33毫秒)。

smalldatetime從1900年1月1日到2079年6月6日的日期和時間數(shù)據，精確到分鐘。

char固定長度的非Unicode字符數(shù)據，最大長度為8，000個字符。

varchar可變長度的非Unicode數(shù)據，最長為8,000個字符。SQLServer2005下的常用數(shù)據類型

SQLServer2005下的常用函數(shù)

SQLServer2005下的函數(shù)可以在“聯(lián)機叢書”的索引中通過鍵入“函數(shù)”來查詢其詳細資料，下面給出實際應用中經常用到的函數(shù)(注意有些函數(shù)的參數(shù)是以字為單位的)：

convert數(shù)值類型轉換函數(shù)

left返回字符串左邊指定數(shù)目的子字符串

right返回字符串右邊指定數(shù)目的子字符串

len返回字符串中包含字符的數(shù)目例子：len(‘張三豐’)len(‘abc’)

lower將字符串中的字母轉為小寫

upper將字符串中的字母轉為大寫

ltrim刪除字符串左邊的所有空格

rtrim刪除字符串右邊的所有空格

str將數(shù)值型數(shù)據轉換為字符串

substring返回一個字符串指定位置的子串

dateadd返回給定日期值增加一個時間間隔后的日期

datediff返回兩個日期之間的時間間隔

datename返回日期的文本表示方式

datepart返回某數(shù)值對應的日期值

day返回日期值中天數(shù)的整數(shù)值

getdate返回當前系統(tǒng)日期

month返回日期值中月份的整數(shù)值

year返回日期值中年份的整數(shù)值SQL的數(shù)據定義關系模型中的關系，在RDBMS中被“具體化”為“表”。要對表中數(shù)據進行操縱(查詢、插入、刪除或修改表中的數(shù)據)，首先應建立表的結構，即“表格”。附帶地，還應定義好相關的完整性約束。下面將介紹建立和維護表結構的命令及語法。

創(chuàng)建表

1.利用SQLServer管理平臺創(chuàng)建表在SQLServer管理平臺中，展開指定的服務器和數(shù)據庫，打開想要創(chuàng)建新表的數(shù)據庫，右擊表對象，并從彈出的快捷菜單中選擇“新建表”選項，如圖5-4所示。在圖5-4的對話框中，可以對表的結構進行更改，設置主鍵及字段屬性，使用SQLServer管理平臺可以非常直觀地修改數(shù)據庫結構和添加數(shù)據。在表中任意行上右擊，則彈出一個快捷菜單，如圖5-6所示。設置字段屬性對話框

新建表對話框

創(chuàng)建表

2.利用create命令創(chuàng)建表

使用create命令創(chuàng)建表非常靈活，它允許對表設置幾種不同的選項，包括表名、存放位置和列的屬性等。命令語法：createtable[database_name.owner.|owner.]table_name({column_definition|table_constraint}[,...n]

)語法說明：（1）在column_definition中的主要內容包括：列名、列類型、是否允許空值、初值和列級約束等；（2）table_constraint指的是設定表級約束，包括主鍵約束、唯一約束和外鍵約束等。

創(chuàng)建表

2.利用create命令創(chuàng)建表

其完整語法形式如下：

CREATETABLE

[database_name.[owner].|owner.]table_name

(

{<column_definition>|column_nameAScomputed_column_expression|<table_constraint>}[，…n])

[ON{filegroup|DEFAULT}][TEXTIMAGE_ON{filegroup|DEFAULT}]

<column_definition>::={column_name

data_type}

[COLLATE<collation_name>]

[[DEFAULTconstant_expression]

|[IDENTITY[(seed,increment)[NOTFORREPLICATION]]]]

[ROWGUIDCOL]

[<column_constraint>][...n]其中，各參數(shù)的說明如下：database_name：用于指定所創(chuàng)建表的數(shù)據庫名稱。owner：用于指定新建表的所有者的用戶名。table_name：用于指定新建表的名稱。column_name：用于指定新建表的列名。computed_column_expression：用于指定計算列的列值表達式。column_definition中的主要內容包括：列名、列類型、是否允許空值、初值和列級約束等；table_constraint指的是設定表級約束，包括主鍵約束、唯一約束和外鍵約束等。column_constraint：用于指定列約束ON{filegroup|DEFAULT}：用于指定存儲表的文件組名。TEXTIMAGE_ON：用于指定text、ntext

和image列的數(shù)據存儲的文件組。data_type：用于指定列的數(shù)據類型。DEFAULT：用于指定列的默認值。constant_expression：用于指定列的默認值的常量表達式、可以為一個常量或NULL或系統(tǒng)函數(shù)。IDENTITY：用于將列指定為標識列。Seed：用于指定標識列的初始值。Increment：用于指定標識列的增量值。NOTFORREPLICATION：用于指定列的IDENTITY屬性，在把從其他表中復制的數(shù)據插入到表中時不發(fā)生作用，即不生成列值，使得復制的數(shù)據行保持原來的列值。ROWGUIDCOL：用于將列指定為全局惟一標識行號列(rowglobaluniqueidentifiercolumn)。COLLATE：用于指定表的校驗方式。

創(chuàng)建表

2.利用create命令創(chuàng)建表

Table_name為新建立的表名。對于臨時表，表名字符串長度不能超過116個字符，而永久表的表名字符串長度則不能超過128個字符。此外，在同一個數(shù)據庫中，每個表所有者創(chuàng)建的表名必須保證唯一。

computed_column_expression指出計算列的定義表達式，計算列是一個虛擬列，它并不是存儲在表中，而是由表中的其它非計算列（常規(guī)列）導出。計算列定義表達式可以為常規(guī)列、常量、變量、函數(shù)組成的表達式，但它不能由一個子查詢構成。除了下面情況，計算列可以與常規(guī)列一樣使用在SELECT列表、WHERE子句和ORDERBY子句中：

計算列不能用在索引的關鍵詞列；

計算列不能作為PRIMARYKEY、UNIQUE、FOREIGNKEY或DEFAULT約束定義的一部分；

計算列不能用INSERT和UPDATE語句插入資料。

注意：①在建表語句中，不允許對計算列設置空值屬性（NULL或NOTNULL）Createtablestudent(

snochar(8)notnull, namechar(10)notnull,

namelenaslen(name) sexchar(2)notnull,departmentchar(20)

constraintpk_idprimarykey(sno))

創(chuàng)建表

2.利用create命令創(chuàng)建表

示例：創(chuàng)建了一個學生信息表student，包括學號(主鍵)、姓名、性別、院系Createtablestudent(

snochar(8)notnull, namechar(10)notnull, sexchar(2)notnull,departmentchar(20)

constraintpk_idprimarykey(sno))作業(yè)1：創(chuàng)建了一個工人信息表worker，它包括工人編號(主鍵)、姓名、性別、出生時間、職位、工資和備注信息。備注：詳細要求參考下表。列名數(shù)據類型是否為空備注numberchar(8)否工人編號namechar(8)否姓名sexchar(2)否性別birthdaydatetime否出生時間（精確到時分）Job_titlechar(12)是職位salarydecimal(7,2)否工資memoVarchar(255)是備注信息創(chuàng)建約束約束是SQLServer提供的自動保持數(shù)據庫完整性的一種方法，它通過限制字段中數(shù)據、記錄中數(shù)據和表之間的數(shù)據來保證數(shù)據的完整性。在SQLSERVER中，對于基本表的約束分為列約束和表約束。列約束是對某一個特定列的約束，包含在列定義中，直接跟在該列的其他定義之后，用空格分隔，不必指定列名；表約束與列定義相互獨立，不包括在列定義中，通常用于對多個列一起進行約束，與列定義用’,’分隔，定義表約束時必須指出要約束的那些列的名稱。完整性約束的基本語法格式為：

[CONSTRAINTconstraint_name(約束名)]<約束類型>

約束不指定名稱時，系統(tǒng)會給定一個名稱。在SQLServer2005中有6種約束：主鍵約束(primarykeyconstraint)、惟一性約束(uniqueconstraint)、檢查約束(checkconstraint)、默認約束(defaultconstraint)、外部鍵約束(foreignkeyconstraint)和空值(NULL)約束。

創(chuàng)建約束1.主鍵(PRIMARYKEY)約束

PRIMARYKEY約束用于定義基本表的主鍵，它是惟一確定表中每一條記錄的標識符，其值不能為NULL，也不能重復，以此來保證實體的完整性。PRIMARYKEY與UNIQUE約束類似，通過建立唯一索引來保證基本表在主鍵列取值的唯一性，但它們之間存在著很大的區(qū)別：①在一個基本表中只能定義一個PRIMARYKEY約束，但可定義多個UNIQUE約束；②對于指定為PRIMARYKEY的一個列或多個列的組合，其中任何一個列都不能出現(xiàn)空值，而對于UNIQUE所約束的唯一鍵，則允許為空。注意：不能為同一個列或一組列既定義UNIQUE約束，又定義PRIMARYKEY約束。PRIMARYKEY既可用于列約束，也可用于表約束。創(chuàng)建約束1.主鍵(PRIMARYKEY)約束

主鍵的創(chuàng)建操作方法有兩種：SQLServer管理平臺操作法和Transact-SQL語句操作法。(1)SQLServer管理平臺操作法，如下圖所示。創(chuàng)建約束1.主鍵(PRIMARYKEY)約束

(2)使用Transact-SQL語句操作法設置主鍵約束，其語法形式如下：

CONSTRAINTconstraint_namePRIMARYKEY[CLUSTERED|NONCLUSTERED](column_name)PRIMARYKEY用于定義表約束時，即將某些列的組合定義為主鍵，其語法格式如下：

CONSTRAINTconstraint_namePRIMARYKEY[CLUSTERED|NONCLUSTERED](column_name[,…n])示例：建立一個sc表，定義cno為主鍵

createtablesc( cnochar(5)notnull,

snochar(5)notnull, scorenumeric(3), constraintsc_primprimarykey(cno))

作業(yè)2：創(chuàng)建工人信息表(作業(yè)1)，定義工人編號、姓名共同組成主鍵。創(chuàng)建約束2.

惟一性約束

惟一性約束用于指定一個或者多個列的組合值具有惟一性，以防止在列中輸入重復的值。定義了UNIQUE約束的那些列稱為唯一鍵，系統(tǒng)自動為唯一鍵建立唯一索引，從而保證了唯一鍵的唯一性。當使用惟一性約束時，需要考慮以下幾個因素：使用惟一性約束的字段允許為空值；一個表中可以允許有多個惟一性約束；可以把惟一性約束定義在多個字段上；惟一性約束用于強制在指定字段上創(chuàng)建一個惟一性索引；默認情況下，創(chuàng)建的索引類型為非聚集索引。創(chuàng)建約束2.

惟一性約束

創(chuàng)建惟一性約束的方法有兩種：通過SQLServer管理平臺可以完成創(chuàng)建和修改惟一性約束的操作；使用Transact-SQL語句完成惟一性約束的操作。(1)通過SQLServer管理平臺可以完成創(chuàng)建和修改惟一性約束的操作，如下圖所示。

創(chuàng)建約束2.惟一性約束

創(chuàng)建惟一性約束的方法有兩種：通過SQLServer管理平臺可以完成創(chuàng)建和修改惟一性約束的操作；使用Transact-SQL語句完成惟一性約束的操作。

(2)使用Transact-SQL語句完成惟一性約束的操作，其語法形式如下：CONSTRAINTconstraint_nameUNIQUE[CLUSTERED|NONCLUSTERED]

(column_name[,…n])示例：創(chuàng)建一個學生信息表，其中name字段具有惟一性。createtablestudent(

snochar(8)notnull, namechar(10)notnullconstraintuk_identity

unique(name), sexchar(2)notnull,departmentchar(20)

, constraintpk_idprimarykey(sno))作業(yè)3：創(chuàng)建工人信息表(作業(yè)1),定義工人編號為主鍵，姓名字段具有惟一性。創(chuàng)建約束3.檢查約束

檢查約束對輸入列或者整個表中的值設置檢查條件，以限制輸入值，保證數(shù)據庫數(shù)據的完整性。當使用檢查約束時，應該考慮和注意以下幾點：一個列級檢查約束只能與限制的字段有關；一個表級檢查約束只能與限制的表中字段有關；一個表中可以定義多個檢查約束；每個CREATETABLE語句中每個字段只能定義一個檢查約束；在多個字段上定義檢查約束，則必須將檢查約束定義為表級約束；當執(zhí)行INSERT語句或者UPDATE語句時，檢查約束將驗證數(shù)據；檢查約束中不能包含子查詢。

創(chuàng)建約束3.檢查約束

創(chuàng)建檢查約束常用的操作方法有如下兩種：使用SQLServer管理平臺創(chuàng)建檢查約束；用Transact-SQL語句創(chuàng)建檢查約束。(1)使用SQLServer管理平臺創(chuàng)建檢查約束，如下圖所示。創(chuàng)建約束3.檢查約束

(2)用Transact-SQL語句創(chuàng)建檢查約束。用Transact-SQL語句創(chuàng)建檢查約束。其語法形式如下：

CONSTRAINTconstraint_nameCHECK[NOTFORREPLICATION](logical_expression)示例：建立一個sc表，定義score的取值范圍為0到100之間。createtablesc(

cnochar(5),

snochar(5),scorenumeric(5,1)constraintscore_chkcheck(score>=0andscore<=100))作業(yè)4：創(chuàng)建工人信息表(作業(yè)1)，定義工資的取值范圍為3000到5000之間。創(chuàng)建約束4.默認(DEFAULT)約束

默認約束指定在插入操作中如果沒有提供輸入值時，則系統(tǒng)自動指定值。默認約束可以包括常量、函數(shù)、不帶變元的內建函數(shù)或者空值。使用默認約束時，應該注意以下幾點：

(1)每個字段只能定義一個默認約束；

(2)如果定義的默認值長于其對應字段的允許長度，那么輸入到表中的默認值將被截斷；

(3)不能加入到帶有IDENTITY屬性或者數(shù)據類型為timestamp（時間戳）的字段上；

(4)如果字段定義為用戶定義的數(shù)據類型，而且有一個默認綁定到這個數(shù)據類型上，則不允許該字段有默認約束。創(chuàng)建約束4.默認(DEFAULT)約束

創(chuàng)建默認約束常用的操作方法有如下兩種：使用SQLServer管理平臺創(chuàng)建默認約束；創(chuàng)建默認約束的Transact-SQL語句操作法。

(1)使用SQLServer管理平臺創(chuàng)建默認約束，如下圖所示。創(chuàng)建約束4.默認(DEFAULT)約束

(2)創(chuàng)建默認約束的Transact-SQL語句操作法。其語法形式如下：

CONSTRAINTconstraint_nameDEFAULTconstraint_expression[FORcolumn_name]

示例：創(chuàng)建一個學生信息表，默認score為零、dept為計算機。createtablestudent(

snochar(8), namechar(10),sexchar(2), scoredecimal(4,2)notnull constraintdf_student_scoredefault0, departmentchar(20)notnull constraintdf_student_deptdefault'計算機')作業(yè)5：創(chuàng)建工人信息表(作業(yè)1)，定義工資默認值為3000、職位為‘經理'。創(chuàng)建約束5.外部鍵約束外鍵(FOREIGNKEY)是用于建立和加強兩個表數(shù)據之間的鏈接的一列或多列。外部鍵約束用于強制參照完整性。當使用外部鍵約束時，應該考慮以下幾個因素：外部鍵約束提供了字段參照完整性；外部鍵從句中的字段數(shù)目和每個字段指定的數(shù)據類型都必須和REFERENCES從句中的字段相匹配；外部鍵約束不能自動創(chuàng)建索引，需要用戶手動創(chuàng)建；一個表中最多可以有31個外部鍵約束；在臨時表中，不能使用外部鍵約束；主鍵和外部鍵的數(shù)據類型必須嚴格匹配。創(chuàng)建約束5.外部鍵約束創(chuàng)建外部鍵約束常用的操作方法有如下兩種：在SQLServer管理平臺中添加外部鍵約束；使用Transact-SQL語句設置外部鍵約束。

(1)在SQLServer管理平臺中添加外部鍵約束，在SQLServer管理平臺中添加外部鍵約束。如下圖所示。

創(chuàng)建約束5.外部鍵約束

(2)使用Transact-SQL語句設置外部鍵約束，其語法形式如下：

CONSTRAINTconstraint_nameFOREIGNKEY(column_name[,…n])

REFERENCESref_table[(ref_column[,…n])]例5-11建立一個sc表，定義sno為sc的外部鍵。createtablesc(

cnochar(8)

notnull,snochar(8)notnullconstraints_foreforeignkeyreferencesstudent(sno),scorenumeric(3),constraints_c_primprimarykey(cno))修改表

當數(shù)據庫中的表創(chuàng)建完成后，可以根據需要改變表中原先定義的許多選項，以更改表的結構。用戶可以增加、刪除和修改列，增加、刪除和修改約束，更改表名以及改變表的所有者等。命令語法：altertabletable_namealtercolumncolumn_name

data_type

[(precision[,scale])][null|notnull]|add

{column_definition|table_constraint}[,...n]|drop

{columncolumn_name|[constraint]constraint_name}[,...n]語法說明：(1)altercolumn用于修改表中原有列，可修改內容包括數(shù)據類型、大小和可空性；(2)add

{column_definition|table_constraint}用于增加新的列或新的表級約束；(3)drop

{columncolumn_name|[constraint]constraint_name}用于刪除原有列或原有約束。修改表

示例：創(chuàng)建一個雇員信息表，然后在表中增加一個salary,jointime字段，刪除表中的age字段，并且修改memo字段的數(shù)據類型。createtableemployees(idchar(8)primarykey,namechar(20)notnull,departmentchar(20)null,memochar(30)null,ageintnull)altertableemployeesaddsalary

decimal(8,2)notnull,jointime

datetimenotnullaltertableemployeesdropcolumnagealtertableemployeesaltercolumnmemovarchar(200)null作業(yè)6：在工人信息表(作業(yè)1)中增加地址(address)、補貼（subsidy）、入廠時間（jointime）列，要求補貼一列默認值為2000；刪除表中的職位字段；修改備注字段的數(shù)據類型；改變表的主鍵為工人編號和姓名的聯(lián)合主鍵。刪除表

命令語法：droptabletable_name語法說明：要刪除的表如果不在當前數(shù)據庫中，則應在table_name中指明其所屬的數(shù)據庫和用戶名（droptabledatabase.dbo.table_name

）。在刪除一個表之前要先刪除與此表相關聯(lián)的表中的外部關鍵字約束。當刪除表后，綁定的規(guī)則或者默認值會自動松綁。刪除表操作會將表中所有數(shù)據刪除，而且直接或間接地建立在該表上的視圖，及相關授權等與此表有關的內容也會被自動撤銷。由于其破壞性很大，因此在執(zhí)行這類命令時，一定要非常小心。示例：刪除employees表droptableemployees索引索引（index）是關于數(shù)據位置信息的關鍵字表，是數(shù)據庫系統(tǒng)中的數(shù)據存取方法之一。利用索引，系統(tǒng)可較快地在磁盤上定位所需數(shù)據，而不需要在磁盤上從頭到尾或從后往前，一個數(shù)據一個數(shù)據地匹配和查找，從而加快了數(shù)據查詢的速度。索引的類型有：聚集（clustered）索引、非聚集（nonclustered）索引、唯一（unique）索引和普通索引。聚集索引：磁盤上表中數(shù)據與索引存儲在相鄰物理空間，并且表中行的物理順序與索引的順序一致。也就是說，表中的記錄是按索引順序存放的，表中數(shù)據一有變化，系統(tǒng)均須對表中數(shù)據重新排序。由于表中的數(shù)據在磁盤上是按索引順序存放的，因此每張表最多只能建一個聚集索引。非聚集索引：與聚集索引不同，它不要求表中行的物理順序與索引的順序一致。唯一索引：該索引要求被索引的列不能有相同值出現(xiàn)。唯一索引可用來限定聚集索引和非聚集索引，如唯一的聚集索引、唯一的非聚集索引，表示限定這兩類索引所索引的列不能有相同的值。這類索引適合于限定基于主鍵或候選鍵的聚簇或非聚集索引。普通索引：與唯一索引不同，普通索引允許在被索引列上的值出現(xiàn)重復?？梢酝ㄟ^在查詢條件中經常涉及的屬性上建立各類索引以提高查詢速度，到底在哪些列上建立索引，以及建立什么類型的索引，應當根據實際應用的情況來決定。比如可以在查詢實時性要求較高的條件所涉及的屬性上建立聚集索引，以達到最佳查詢速度。一但建立好索引，系統(tǒng)會自動引用已存在的索引來加快查詢速度，無須也不允許用戶指明對索引的使用。應適當建立索引，索引會提高查詢速度，但也會增加數(shù)據更新時的響應時間。索引除了可以在定義表時通過primarykey、unique等參數(shù)由系統(tǒng)自動創(chuàng)建之外，也可以通過createindex命令單獨創(chuàng)建。創(chuàng)建索引命令語法：create[unique][clustered|nonclustered]indexindex_nameontable_name({column_name[asc|desc]}[,...n])語法說明：（1）索引的建立和刪除，必須要有DBA權限或者是表的擁有者；（2）unique表示建立唯一性的索引；（3）clustered|nonclustered表示聚集索引或非聚集索引；（4）ontable_name表示為哪個表創(chuàng)建索引；（5）column_name[asc|desc]用于指定索引基于的列和索引排序方式。一個索引中最多可以指定16個列，但列的數(shù)據類型的長度和不能超過900個字節(jié)。

注意：數(shù)據類型為TEXT、NTEXT、IMAGE或BIT的列不能作為索引的列。由于索引的寬度不能超過900個字節(jié)，因此數(shù)據類型為CHAR、VARCHAR、BINARY和VARBINARY的列的列寬度超過了900字節(jié)，或數(shù)據類型為NCHAR、NVARCHAR的列的列寬度超過了450個字節(jié)時也不能作為索引的列。在使用索引創(chuàng)建向導創(chuàng)建索引時，不能將計算列包含在索引中，但在直接創(chuàng)建或使用CREATEINDEX命令創(chuàng)建索引時，則可以對計算機列創(chuàng)建索引。createindexpk_p_mainonproducts(p_id,p_name,sumvalue)--其中sumvalue

是一個計算列表達式為price*quantity示例：在employees中的有關列上建立索引。createuniqueindexemployees_nameonemployees(name)createindexemployees_departmentonemployees(department

)作業(yè)7：給工人信息表(作業(yè)1)的姓名列建立唯一性索引。創(chuàng)建索引完整命令語法：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]

INDEXindex_nameON{table|view}column[ASC|DESC][,...n])

[WITH

[PAD_INDEX]

[[,]FILLFACTOR=fillfactor]

[[,]IGNORE_DUP_KEY]

[[,]DROP_EXISTING]

[[,]STATISTICS_NORECOMPUTE]

[[,]SORT_IN_TEMPDB]

]

[ONfilegroup]

各參數(shù)說明如下：UNIQUE

創(chuàng)建一個惟一索引，即索引的鍵值不重復。在列包含重復值時，不能建惟一索引。如要使用此選項，則應確定索引所包含的列均不允許NULL值，否則在使用時會經常出錯。CLUSTERED

指明創(chuàng)建的索引為簇索引。如果此選項缺省，則創(chuàng)建的索引為非簇索引。NONCLUSTERED

指明創(chuàng)建的索引為非簇索引其索引。數(shù)據頁中包含了指向數(shù)據庫中實際的表數(shù)據頁的指針。index_name

指定所創(chuàng)建的索引的名稱。索引名稱在一個表中應是惟一的，但在同一數(shù)據庫或不同數(shù)據庫中可以重復。table

指定創(chuàng)建索引的表的名稱。必要時還應指明數(shù)據庫名稱和所有者名稱。view

指定創(chuàng)建索引的視圖的名稱。視圖必須是使用SCHEMABINDING選項定義過的，其具體信息請參見“視圖創(chuàng)建”章節(jié)。ASC|DESC

指定特定的索引列的排序方式。默認值是升序（ASC）。column

指定被索引的列。如果使用兩個或兩個以上的列組成一個索引，則稱為復合索引。創(chuàng)建索引PAD_INDEX

指定填充索引的內部節(jié)點的行數(shù)，至少應大于等于兩行。PAD_INDEX選項只有在FILLFACTOR選項指定后才起作用。因為PAD_INDEX使用與FILLFACTOR相同的百分比。缺省時，SQLServer確保每個索引頁至少有能容納一條最大索引行數(shù)據的空閑空間。如果FILLFACTOR指定的百分比不夠容納一行數(shù)據S，QLServer會自動內部更改百分比。FILLFACTOR=fillfactor

FILLFACTOR

稱為填充因子，它指定創(chuàng)建索引時，每個索引頁的數(shù)據占索引頁大小的百分比，fillfactor

的值為1到100。它其實同時指出了索引頁保留的自由空間占索引頁大小的百分比。即100-fillfactor。對于那些頻繁進行大量數(shù)據插入或刪除的表在建索引時應該為將來生成的索引數(shù)據預留較大的空間，即將fillfactor

設得較小，否則，索引頁會因數(shù)據的插入而很快填滿，并產生分頁，而分頁會大大增加系統(tǒng)的開銷。但如果設得過小，又會浪費大量的磁盤空間，降低查詢性能。因此，對于此類表通常設一個大約為10的fillfactor。而對于數(shù)據不更改的、高并發(fā)的、只讀的表，fillfactor

可以設到95以上乃至100。

如果沒有指定此選項，SQLServer默認其值為0。0是個特殊值，與其它小FILLFACTOR

值（如：1，2）的意義不同，其葉節(jié)點頁被完全填滿，而在索引頁中還有一些空間?？梢?/p>

用存儲過程Sp_configure

來改變默認的FILLFACTOR值。IGNORE_DUP_KEY

此選項控制了當往包含于一個惟一約束中的列中插入重復數(shù)據時SQLServer所作的反應。當選擇此選項時，SQLServer返回一個錯誤信息，跳過此行數(shù)據的插入，繼續(xù)執(zhí)行下面的插入數(shù)據的操作：當沒選擇此選項時，SQLServer不僅會返回一個錯誤信息，還會回滾（RollsBack）整個INSERT語句（關于回滾，請參見“數(shù)據庫更新”章節(jié)中的“事務”一節(jié)）。DROP_EXISTING

指定要刪除并重新創(chuàng)建簇索引。刪除簇索引會導致所有的非簇索引被重建，因為需要用行指針來替換簇索引鍵。如果再重建簇索引，那么非簇索引又會再重建一次，以便用簇索引鍵來替換行指針。使用DROP_EXISTING選項可以使非簇索引只重建一次。STATISTICS_NORECOMPUTE

指定分布統(tǒng)計不自動更新。需要手動執(zhí)行不帶NORECOMPUTE子句的UPDATESTATISTICS命令。SORT_IN_TEMPDB

指定用于創(chuàng)建索引的分類排序結果將被存儲到Tempdb

數(shù)據庫中。如果Tempdb

數(shù)據庫和用戶數(shù)據庫位于不同的磁盤設備上，那么使用這一選項可以減少創(chuàng)建索引的時間，但它會增加創(chuàng)建索引所需的磁盤空間。ONfilegroup

指定存放索引的文件組。創(chuàng)建索引索引的優(yōu)點和不足索引有一些先天不足

1、系統(tǒng)要占用大約為表的1.2倍的硬盤和內存空間來保存索引；

2、更新數(shù)據的時候，系統(tǒng)必須要有額外的時間來同時對索引進行更新，以維持數(shù)據和索引的一致性。當然建立索引的優(yōu)點也是顯而易見的，在海量數(shù)據的情況下，如果合理的建立了索引，則會大大加強SQLS執(zhí)行查詢、對結果進行排序、分組的操作效率。實踐表明，不恰當?shù)乃饕坏谑聼o補，反而會降低系統(tǒng)性能。因為大量的索引在進行插入、修改和刪除操作時比沒有索引要花費更多的系統(tǒng)時間。在如下字段建立索引應該是不恰當?shù)模?/p>

1、很少或從不引用的字段；

2、邏輯型的字段，如男或女(是或否)等。綜上所述，提高查詢效率是以消耗一定的系統(tǒng)資源為代價的，索引不能盲目的建立，必須要有統(tǒng)籌的規(guī)劃，一定要在“加快查詢速度”與“降低修改速度”之間做好平衡。索引使用原則：

1．邏輯主鍵使用惟一的成組索引，對系統(tǒng)鍵（作為存儲過程）采用惟一的非成組索引，對任何外鍵列采用非成組索引。考慮數(shù)據庫的空間有多大，表如何進行訪問，還有這些訪問是否主要用作讀寫；2．不要索引memo/note字段，不要索引大型字段（有很多字符），這樣作會讓索引占用太多的存儲空間；3．不要索引常用的小型表；4．一般不要為小型數(shù)據表設置過多的索引，如果經常有插入和刪除操作就更不要設置索引，因為SQLS對插入和刪除操作提供的索引維護可能比掃描表空間消耗的時間更多。

創(chuàng)建索引

類別對比項主鍵聚集索引用途強制表的實體完整性對數(shù)據行的排序，方便查詢用一個表多少個一個表最多一個主鍵一個表最多一個聚集索引是否允許多個字段來定義一個主鍵可以多個字段來定義一個索引可以多個字段來定義是否允許null數(shù)據行出現(xiàn)如果要創(chuàng)建的數(shù)據列中數(shù)據存在null，無法建立主鍵。

創(chuàng)建表時指定的PRIMARYKEY約束列隱式轉換為NOTNULL。沒有限制建立聚集索引的列一定必須notnull.

也就是可以列的數(shù)據是null

參看最后一項比較是否要求數(shù)據必須唯一要求數(shù)據必須唯一數(shù)據即可以唯一，也可以不唯一?？茨愣x這個索引的UNIQUE設置。

（這一點需要看后面的一個比較，雖然你的數(shù)據列可能不唯一，但是系統(tǒng)會替你產生一個你看不到的唯一列）創(chuàng)建的邏輯數(shù)據庫在創(chuàng)建主鍵同時，會自動建立一個唯一索引。

如果這個表之前沒有聚集索引，同時建立主鍵時候沒有強制指定使用非聚集索引，則建立主鍵時候，同時建立一個唯一的聚集索引如果未使用UNIQUE屬性創(chuàng)建聚集索引，數(shù)據庫引擎將向表自動添加一個四字節(jié)uniqueifier

列。

必要時，數(shù)據庫引擎將向行自動添加一個uniqueifier

值，使每個鍵唯一。此列和列值供內部使用，用戶不能查看或訪問。主鍵和聚集索引的對比：刪除索引

命令語法：dropindex

table_name.index_name語法說明：（1）只有索引的擁有者和具有DBA權限的用戶，可以刪除索引。索引刪除后，有關索引的定義將從系統(tǒng)的數(shù)據字典（DD）中刪除，并且包含在索引中的全部索引項將被清除。索引的刪除，不會影響其它表和索引的正常使用，只會在某種程度上影響系統(tǒng)的性能。（2）在數(shù)據定義語言中，沒有索引的修改功能。要修改索引，可以先刪除要修改的索引，然后再創(chuàng)建一個同名的索引。備注：（1）索引的作用在于提高查詢操作速度，但是這是以增加存儲空間和降低數(shù)據更新效率為代價的，因此應刪除那些不被引用或不常被引用的索引。（2）在被刪除索引名前必須給出索引所屬表名，中間用圓點分隔。示例：刪除employees表中name列上的索引。

dropindexemployees.employees_name

作業(yè)8：刪除工人信息表(作業(yè)1)姓名列上的索引。SQL的數(shù)據更新利用DDL數(shù)據定義語言建立起數(shù)據結構和完整性約束后，就可以利用DML數(shù)據操縱語言對數(shù)據庫進行“插入”、“修改”和“刪除”三種數(shù)據更新操作。 1.數(shù)據插入命令語法：格式一：insert[into]table_name|view_name[(column_list)]values

(data_values)格式二：insert[into]table_name|view_name[(column_list)]select_statement語法說明：（1）如果有列名表“column_list”，則要求其后的數(shù)據在順序、個數(shù)和類型上，應與其一一對應；如果沒有列名表，則要求插入的數(shù)據在順序、個數(shù)和類型上，應