1、SQL Server2019 數(shù)據(jù)庫技術(shù)及運用第第7 7章章 表的管理與運用表的管理與運用 7.1創(chuàng)建表格創(chuàng)建表格 7.2刪除表格刪除表格 7.3修正表格定義修正表格定義 7.4表格重命名表格重命名 7.5查看表格屬性查看表格屬性 7.6表格索引表格索引7.17.1創(chuàng)建表創(chuàng)建表 SQL ServerSQL Server的每個數(shù)據(jù)庫最多可存儲的每個數(shù)據(jù)庫最多可存儲2020億個表，每個表可以億個表，每個表可以有有10241024列。表的行數(shù)及總大小僅受可用存儲空間的限制。每列。表的行數(shù)及總大小僅受可用存儲空間的限制。每行最多可以存儲行最多可以存儲80608060字節(jié)。假設(shè)創(chuàng)建具有字節(jié)。假設(shè)創(chuàng)建具有

2、varcharvarchar、nvarcharnvarchar或或varbinaryvarbinary列的表，那么即使列的字節(jié)總數(shù)超越列的表，那么即使列的字節(jié)總數(shù)超越8,0608,060字節(jié)，字節(jié)，仍可以創(chuàng)建此表。仍可以創(chuàng)建此表。 SQL ServerSQL Server中的數(shù)據(jù)表分為永久表和暫時表兩種。永久表在中的數(shù)據(jù)表分為永久表和暫時表兩種。永久表在創(chuàng)建后，除非用戶刪除，否那么將不斷存儲在數(shù)據(jù)庫文件中；創(chuàng)建后，除非用戶刪除，否那么將不斷存儲在數(shù)據(jù)庫文件中；而暫時表那么會在用戶退出或者進展系統(tǒng)修復(fù)的時候被自動而暫時表那么會在用戶退出或者進展系統(tǒng)修復(fù)的時候被自動刪除。刪除。 SQL Serve

3、r 2019SQL Server 2019提供了兩種方法創(chuàng)建數(shù)據(jù)庫表：一種方法是提供了兩種方法創(chuàng)建數(shù)據(jù)庫表：一種方法是利用利用T-SQLT-SQL語句中的語句中的createcreate命令創(chuàng)建表；另一種方法是利用命令創(chuàng)建表；另一種方法是利用SQSQL Server Management StudioL Server Management Studio創(chuàng)建表。創(chuàng)建表。 7.17.1創(chuàng)建表創(chuàng)建表7.1.17.1.1運用運用T-SQLT-SQL言語創(chuàng)建表言語創(chuàng)建表運用運用CREATE TABLECREATE TABLE創(chuàng)建表的語法構(gòu)造如下：創(chuàng)建表的語法構(gòu)造如下：CREATE TABLECREATE

4、 TABLE database_name. owner .| owner. database_name. owner .| owner. table_nametable_name | column_name AS computed_column_e | column_name AS computed_column_expression| xpression| ，nn ON filegroup | DEFAULT ON filegroup | DEFAULT := column_name data_type := column_name data_type DEFAULT constant_ex

5、pression DEFAULT constant_expression | IDENTITY | IDENTITY seed , increment seed , increment NOT FOR REPLICATIO NOT FOR REPLICATION N ROWGUIDCOL ROWGUIDCOL .n .n 參數(shù)闡明：參數(shù)闡明：database_namedatabase_name：用于指定在其中創(chuàng)建表的數(shù)據(jù)庫稱號。：用于指定在其中創(chuàng)建表的數(shù)據(jù)庫稱號。ownerowner：用于指定新建表的一切者的用戶名。：用于指定新建表的一切者的用戶名。table_nametable_name：用

6、于指定新建的表的稱號。：用于指定新建的表的稱號。 ：列的定義。：列的定義。column_namecolumn_name：用于指定新建表的列的稱號。：用于指定新建表的列的稱號。computed_column_expressioncomputed_column_expression：用于指定計算列的列值的表達式。：用于指定計算列的列值的表達式。ON filegroup | DEFAULTON filegroup | DEFAULT：用于指定存儲表的文件組名。：用于指定存儲表的文件組名。data_typedata_type：用于指定列的數(shù)據(jù)類型。：用于指定列的數(shù)據(jù)類型。DEFAULTDEFAULT：

7、用于指定列的缺省值。：用于指定列的缺省值。 constant_expression constant_expression：用于指定列的缺省值的常量表達式。：用于指定列的缺省值的常量表達式。IDENTITYIDENTITY：用于指定列為標識列。：用于指定列為標識列。SeedSeed：用于指定標識列的初始值。：用于指定標識列的初始值。IncrementIncrement：用于指定標識列的增量值。：用于指定標識列的增量值。 NOT FOR REPLICATION NOT FOR REPLICATION：用于指定列的：用于指定列的IDENTITYIDENTITY屬性在把從其它表中復(fù)制的數(shù)據(jù)插入到屬性

8、在把從其它表中復(fù)制的數(shù)據(jù)插入到表中時不發(fā)生作用，即不自動生成列值，使得復(fù)制的數(shù)據(jù)行堅持原來的列值。表中時不發(fā)生作用，即不自動生成列值，使得復(fù)制的數(shù)據(jù)行堅持原來的列值。ROWGUIDCOLROWGUIDCOL：用于指定列為全球獨一鑒別行號列。：用于指定列為全球獨一鑒別行號列。column_constraintcolumn_constraint：用于指定列約束，如：用于指定列約束，如NULLNULL，NOT NULLNOT NULL和和PRIMARYPRIMARY等，與約束有關(guān)的等，與約束有關(guān)的內(nèi)容可以參閱本書第內(nèi)容可以參閱本書第1212章相關(guān)內(nèi)容。章相關(guān)內(nèi)容。table_constraintta

9、ble_constraint：定義對表的約束。：定義對表的約束。 7.17.1創(chuàng)建表創(chuàng)建表【例【例7.17.1】在】在SCHOOLSCHOOL數(shù)據(jù)庫中創(chuàng)建一張數(shù)據(jù)庫中創(chuàng)建一張teacherteacher表。表。USE SCHOOLUSE SCHOOLGOGOCREATE TABLE teacherCREATE TABLE teacher(t_id char(8),(t_id char(8),t_name char(10) not null,t_name char(10) not null,age int,age int,sex char(4),sex char(4),address char(

10、20) null)address char(20) null)GO GO 7.17.1創(chuàng)建表創(chuàng)建表【例【例7.17.1】在】在SCHOOLSCHOOL數(shù)據(jù)庫中創(chuàng)建一張數(shù)據(jù)庫中創(chuàng)建一張teacherteacher表。表。USE SCHOOLUSE SCHOOLGOGOCREATE TABLE teacherCREATE TABLE teacher(t_id char(8),(t_id char(8),t_name char(10) not null,t_name char(10) not null,age int,age int,sex char(4),sex char(4),address c

11、har(20) null)address char(20) null)GO GO 7.1.27.1.2運用運用SQL Server Management StudioSQL Server Management Studio創(chuàng)建表創(chuàng)建表 7.27.2刪刪除表 7.2.17.2.1運用運用T-SQLT-SQL刪除表刪除表運用運用T-SQLT-SQL刪除表的語法構(gòu)造如下：刪除表的語法構(gòu)造如下：DROP TABLE table_nameDROP TABLE table_name【例【例7.27.2】將】將SCHOOLSCHOOL數(shù)據(jù)庫中的數(shù)據(jù)庫中的studentstudent表刪除。表刪除。USE S

12、CHOOLUSE SCHOOLGOGODROP TABLE studentDROP TABLE studentGOGO7.2.27.2.2運用運用SQL Server Management StudioSQL Server Management Studio刪除表刪除表 7.37.3修正表定義義 數(shù)據(jù)表創(chuàng)建以后，假設(shè)發(fā)現(xiàn)有屬性設(shè)置不適宜的對方可數(shù)據(jù)表創(chuàng)建以后，假設(shè)發(fā)現(xiàn)有屬性設(shè)置不適宜的對方可以對表進展修正。修正表可以經(jīng)過以對表進展修正。修正表可以經(jīng)過T-SQLT-SQL或者或者SQL Server SQL Server Management StudioManagement Studio來實現(xiàn)

13、。來實現(xiàn)。 【例【例7.37.3】 給給SCHOOLSCHOOL數(shù)據(jù)庫中的數(shù)據(jù)庫中的teacherteacher表添加一列。表添加一列。 USE SCHOOL USE SCHOOL GO GO ALTER TABLE teacher ALTER TABLE teacher ADD tele_number char(11) ADD tele_number char(11) 7.37.3修正表定義義 【例【例7.47.4】將】將SCHOOLSCHOOL數(shù)據(jù)庫中數(shù)據(jù)庫中teacherteacher表的表的addressaddress字段數(shù)據(jù)類型改為字段數(shù)據(jù)類型改為vavarchar(30)rchar

14、(30)。USE SCHOOLUSE SCHOOLGOGOALTER TABLE teacher ALTER TABLE teacher ALTER COLUMN address varchar(30)ALTER COLUMN address varchar(30)【例【例7.57.5】將】將SCHOOLSCHOOL數(shù)據(jù)庫中數(shù)據(jù)庫中teacherteacher表的表的tele_numbertele_number字段刪除。字段刪除。USE SCHOOLUSE SCHOOLGOGOALTER TABLE teacherALTER TABLE teacherDROP COLUMN tele_numb

15、erDROP COLUMN tele_number7.3.27.3.2運用運用SQL Server Management StudioSQL Server Management Studio修正表的定義修正表的定義 7.47.4表重命名 7.4.17.4.1運用運用T-SQLT-SQL為表重命名為表重命名運用系統(tǒng)存儲過程運用系統(tǒng)存儲過程SP_RENAMESP_RENAME為表重命名的語法構(gòu)造如下：為表重命名的語法構(gòu)造如下：SP_RENAME old_table_name,new_table_nameSP_RENAME old_table_name,new_table_name【例【例7.67.

16、6】將】將SCHOOLSCHOOL數(shù)據(jù)庫中數(shù)據(jù)庫中teacherteacher表重新命名為表重新命名為“教師教師表。表。USE SCHOOLUSE SCHOOLGOGOSP_RENAME teacher,SP_RENAME teacher,教師表教師表GOGO7.4.27.4.2運用運用SQL Server Management StudioSQL Server Management Studio為表重命名為表重命名 7.57.5查查看表屬屬性 7.5.17.5.1運用存儲過程運用存儲過程SP_HELPSP_HELP查看表有關(guān)屬性查看表有關(guān)屬性系統(tǒng)存儲過程系統(tǒng)存儲過程SP_HELPSP_HEL

17、P可以查看系統(tǒng)表中與表和表中數(shù)據(jù)有可以查看系統(tǒng)表中與表和表中數(shù)據(jù)有關(guān)的信息，語法構(gòu)造如下：關(guān)的信息，語法構(gòu)造如下：SP_HELP table_nameSP_HELP table_name【例【例7.77.7】運用】運用SP_HELPSP_HELP查看查看JWGLJWGL數(shù)據(jù)庫中數(shù)據(jù)庫中studentstudent表的相關(guān)表的相關(guān)信息。信息。USE JWGLUSE JWGLGOGOSP_HELP studentSP_HELP studentGOGO7.57.5查查看表屬屬性 7.5.27.5.2運用存儲過程運用存儲過程SP_SPACEUSEDSP_SPACEUSED查看存儲空間信息查看存儲空間信

18、息運用存儲過程運用存儲過程SP_SPACEUSEDSP_SPACEUSED可以查看表的行數(shù)以及表所用可以查看表的行數(shù)以及表所用的存儲空間信息，語法構(gòu)造如下：的存儲空間信息，語法構(gòu)造如下：SP_SPACEUSED table_nameSP_SPACEUSED table_name【例【例7.87.8】運用】運用SP_SPACEUSEDSP_SPACEUSED查看查看JWGLJWGL數(shù)據(jù)庫中數(shù)據(jù)庫中studentstudent表表的相關(guān)信息。的相關(guān)信息。USE JWGLUSE JWGLGOGOSP_SPACEUSED studentSP_SPACEUSED studentGOGO7.57.5查查看

19、表屬屬性 7.5.37.5.3運用系統(tǒng)存儲過程運用系統(tǒng)存儲過程SP_DEPENDSSP_DEPENDS查看表的相關(guān)性關(guān)系查看表的相關(guān)性關(guān)系SQL ServerSQL Server的數(shù)據(jù)庫對象之間經(jīng)常存在一些相關(guān)性關(guān)系的數(shù)據(jù)庫對象之間經(jīng)常存在一些相關(guān)性關(guān)系, ,比比如視圖建立的根底表如視圖建立的根底表, ,存儲過程的建立所援用的相關(guān)表等。存儲過程的建立所援用的相關(guān)表等。語法構(gòu)造如下：語法構(gòu)造如下：SP_DEPENDS table_nameSP_DEPENDS table_name【例【例7.97.9】運用】運用SP_DEPENDSSP_DEPENDS查看查看JWGLJWGL數(shù)據(jù)庫中數(shù)據(jù)庫中tea

20、cherteacher表的表的相關(guān)性關(guān)系。相關(guān)性關(guān)系。USE JWGLUSE JWGLGOGOSP_DEPENDS teacherSP_DEPENDS teacherGO GO 7.67.6表索引7.6.17.6.1索引的概念與作用索引的概念與作用 數(shù)據(jù)庫中的索引與書籍中的索引類似，在一本書中，利用索引可以快速查找數(shù)據(jù)庫中的索引與書籍中的索引類似，在一本書中，利用索引可以快速查找所需信息，無須閱讀整本書。在數(shù)據(jù)庫中，索引使數(shù)據(jù)庫程序無須對整所需信息，無須閱讀整本書。在數(shù)據(jù)庫中，索引使數(shù)據(jù)庫程序無須對整個表進展掃描，就可以在其中找到所需數(shù)據(jù)。書中的索引是一個章節(jié)列個表進展掃描，就可以在其中找到所

21、需數(shù)據(jù)。書中的索引是一個章節(jié)列表，其中注明了各個章節(jié)的頁碼。而數(shù)據(jù)庫中的索引是某個表中一列或表，其中注明了各個章節(jié)的頁碼。而數(shù)據(jù)庫中的索引是某個表中一列或者假設(shè)干列值的集合和相應(yīng)的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯者假設(shè)干列值的集合和相應(yīng)的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。創(chuàng)建索引主要有以下幾個作用：指針清單。創(chuàng)建索引主要有以下幾個作用： 1 1、經(jīng)過創(chuàng)建獨一索引，可以保證數(shù)據(jù)記錄的獨一性。、經(jīng)過創(chuàng)建獨一索引，可以保證數(shù)據(jù)記錄的獨一性。 2 2、可以大大加快數(shù)據(jù)檢索速度。、可以大大加快數(shù)據(jù)檢索速度。 3 3、可以加速表與表之間的銜接，這一點在實現(xiàn)數(shù)據(jù)的參照完好性方面、可以加速表與

22、表之間的銜接，這一點在實現(xiàn)數(shù)據(jù)的參照完好性方面有特別的意義。在運用有特別的意義。在運用ORDER BYORDER BY和和GROUP BYGROUP BY子句中進展檢索數(shù)據(jù)時，可子句中進展檢索數(shù)據(jù)時，可以顯著減少查詢中分組和排序的時間。以顯著減少查詢中分組和排序的時間。 4 4、建立索引有利也有弊，它可以提高查詢速度，但過多地建立索引會占、建立索引有利也有弊，它可以提高查詢速度，但過多地建立索引會占據(jù)大量的磁盤空間。所以在建立索引時，數(shù)據(jù)庫管理員必需權(quán)衡利弊，據(jù)大量的磁盤空間。所以在建立索引時，數(shù)據(jù)庫管理員必需權(quán)衡利弊，讓索引帶來的利大于弊。讓索引帶來的利大于弊。 7.6.17.6.1索引的概

23、概念與與作用SQL ServerSQL Server提供了三種方式的索引，一種是簇集索引，另一種是提供了三種方式的索引，一種是簇集索引，另一種是非簇集索引，還有一個是主非簇集索引，還有一個是主XMLXML索引。索引。簇集索引：根據(jù)鍵值對行進展排序，所以每個表只能有一個簇集簇集索引：根據(jù)鍵值對行進展排序，所以每個表只能有一個簇集索引。索引。非簇集索引：不根據(jù)鍵值排序，索引數(shù)據(jù)構(gòu)造與數(shù)據(jù)行是分開的。非簇集索引：不根據(jù)鍵值排序，索引數(shù)據(jù)構(gòu)造與數(shù)據(jù)行是分開的。由于非簇集索引的表沒有按順序進展陳列，所以查找速度明由于非簇集索引的表沒有按順序進展陳列，所以查找速度明顯低于帶簇集索引的表。顯低于帶簇集索引的

24、表。主主 XML XML索引：索引：XMLXML數(shù)據(jù)文件中，元素名將返復(fù)出現(xiàn)，這必然會影數(shù)據(jù)文件中，元素名將返復(fù)出現(xiàn)，這必然會影響到查詢的效率。為了盡能夠地提高響到查詢的效率。為了盡能夠地提高XMLXML的查詢效率，的查詢效率，SQL SeSQL Server 2019rver 2019為為XMLXML類型提供了索引功能。類型提供了索引功能。 7.6.17.6.1索引的概概念與與作用SQL ServerSQL Server提供了三種方式的索引，一種是簇集索引，另一種是提供了三種方式的索引，一種是簇集索引，另一種是非簇集索引，還有一個是主非簇集索引，還有一個是主XMLXML索引。索引。簇集索引：

25、根據(jù)鍵值對行進展排序，所以每個表只能有一個簇集簇集索引：根據(jù)鍵值對行進展排序，所以每個表只能有一個簇集索引。索引。非簇集索引：不根據(jù)鍵值排序，索引數(shù)據(jù)構(gòu)造與數(shù)據(jù)行是分開的。非簇集索引：不根據(jù)鍵值排序，索引數(shù)據(jù)構(gòu)造與數(shù)據(jù)行是分開的。由于非簇集索引的表沒有按順序進展陳列，所以查找速度明由于非簇集索引的表沒有按順序進展陳列，所以查找速度明顯低于帶簇集索引的表。顯低于帶簇集索引的表。主主 XML XML索引：索引：XMLXML數(shù)據(jù)文件中，元素名將返復(fù)出現(xiàn)，這必然會影數(shù)據(jù)文件中，元素名將返復(fù)出現(xiàn)，這必然會影響到查詢的效率。為了盡能夠地提高響到查詢的效率。為了盡能夠地提高XMLXML的查詢效率，的查詢效率

26、，SQL SeSQL Server 2019rver 2019為為XMLXML類型提供了索引功能。類型提供了索引功能。 7.6.2 7.6.2 索引的創(chuàng)創(chuàng)建 1運用運用T-SQL創(chuàng)建表索引創(chuàng)建表索引運用運用T-SQL創(chuàng)建表索引的語法構(gòu)造如下：創(chuàng)建表索引的語法構(gòu)造如下：CREATE UNIQUE CLUSTERED| NONCLUSTERED|PRIMARY XML INDEX index_name ON table | view ( column ASC | DESC ,.n )參數(shù)闡明：參數(shù)闡明： UNIQUE：用于指定為表或視圖創(chuàng)建獨一索引，即不允許存在索：用于指定為表或視圖創(chuàng)建獨一索引，

27、即不允許存在索引值一樣的兩行。引值一樣的兩行。 CLUSTERED：用于指定創(chuàng)建的索引為聚集索引。：用于指定創(chuàng)建的索引為聚集索引。NONCLUSTERED：用于指定創(chuàng)建的索引為非聚集索引。：用于指定創(chuàng)建的索引為非聚集索引。PRIMARY XML：用于指定創(chuàng)建的索引為主：用于指定創(chuàng)建的索引為主XML索引。索引。index_name：用于指定所創(chuàng)建的索引的稱號。：用于指定所創(chuàng)建的索引的稱號。table：用于指定創(chuàng)建索引的表的稱號。：用于指定創(chuàng)建索引的表的稱號。view：用于指定創(chuàng)建索引的視圖的稱號。：用于指定創(chuàng)建索引的視圖的稱號。ASC|DESC：用于指定詳細某個索引列的升序或降序排序方向。：用于指定詳細某個索引列的升序或降序排序方向。Column：用于指定被索引的列。：用于指定被索引的列。 7.6.2 7.6.2 索引的創(chuàng)創(chuàng)建 【例【例7.107.10】對對SCHOOLSCHO