1、第4章 數(shù)據(jù)庫(kù)管理 本章導(dǎo)讀 本章主要介紹了有關(guān)數(shù)據(jù)表的操作，包括表的創(chuàng)建、修改、刪除和建立索引等操作以及表中數(shù)據(jù)維護(hù)的有關(guān)操作和方法，基本掌握使用 SQL Server Management Studio和T-SQL語(yǔ)句對(duì)表的操作和表的數(shù)據(jù)操作，包括創(chuàng)建表、刪除表、對(duì)表中字段建立索引、向表中增加數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)等。讀者要對(duì)SQL Server2005中的數(shù)據(jù)類型有一個(gè)清楚的認(rèn)識(shí)。,4.1數(shù)據(jù)庫(kù)的創(chuàng)建與管理,4.1.1 SQL Server系統(tǒng)數(shù)據(jù)庫(kù) 在第一次安裝SQL Server時(shí)（安裝了數(shù)據(jù)庫(kù)示例），將默認(rèn)安裝系統(tǒng)數(shù)據(jù)庫(kù)、數(shù)據(jù)庫(kù)快照和示例數(shù)據(jù)庫(kù)。示例數(shù)據(jù)庫(kù)包含Northwind數(shù)

2、據(jù)庫(kù)、Pubs數(shù)據(jù)庫(kù)、AdventureWorks數(shù)據(jù)庫(kù)和AdventureWorksDW數(shù)據(jù)庫(kù)。,1系統(tǒng)數(shù)據(jù)庫(kù) 系統(tǒng)數(shù)據(jù)庫(kù)中包含以下6個(gè)數(shù)據(jù)庫(kù)： 1）Master數(shù)據(jù)庫(kù) Master數(shù)據(jù)庫(kù)是SQL Server系統(tǒng)最重要的數(shù)據(jù)庫(kù)。它包含用戶登錄信息、系統(tǒng)配置設(shè)置信息、服務(wù)器配置信息、 SQL Server的初始化信息，其他系統(tǒng)數(shù)據(jù)庫(kù)及用戶數(shù)據(jù)庫(kù)的相關(guān)文件信息。 2）Model數(shù)據(jù)庫(kù) Model數(shù)據(jù)庫(kù)為所有用戶數(shù)據(jù)庫(kù)提供數(shù)據(jù)庫(kù)模板，它含有Master數(shù)據(jù)庫(kù)所有系統(tǒng)表的子集，該系統(tǒng)數(shù)據(jù)庫(kù)是每個(gè)用戶定義數(shù)據(jù)庫(kù)所需要的。當(dāng)創(chuàng)建用戶數(shù)據(jù)庫(kù)時(shí)，系統(tǒng)自動(dòng)把該模板數(shù)據(jù)庫(kù)的所有信息復(fù)制到新建的數(shù)據(jù)庫(kù)中。Mod

3、el數(shù)據(jù)庫(kù)是Tempdb數(shù)據(jù)庫(kù)的基礎(chǔ)，對(duì)Model數(shù)據(jù)庫(kù)的任何改動(dòng)都將反映在Tempdb數(shù)據(jù)庫(kù)中。 3）Msdb數(shù)據(jù)庫(kù) Msdb數(shù)據(jù)庫(kù)是代理服務(wù)器數(shù)據(jù)庫(kù)，該系統(tǒng)數(shù)據(jù)庫(kù)記錄有關(guān)作業(yè)、警報(bào)、操作員和調(diào)度等信息，是SQL Agent處理存儲(chǔ)系統(tǒng)任務(wù)的地方。如果在數(shù)據(jù)庫(kù)上安放一個(gè)夜間運(yùn)行的備份日程表，那么在Msdb上就會(huì)存在一個(gè)對(duì)應(yīng)表項(xiàng)。 4）Tempdb數(shù)據(jù)庫(kù) Tempdb數(shù)據(jù)庫(kù)是一個(gè)連接到SQL Server實(shí)例的所有用戶都可用的全局資源的臨時(shí)數(shù)據(jù)庫(kù)，它為所有的臨時(shí)表、臨時(shí)存儲(chǔ)過(guò)程及其他臨時(shí)操作提供存儲(chǔ)空間。 5）Resource數(shù)據(jù)庫(kù) Resource數(shù)據(jù)庫(kù)是一個(gè)資源數(shù)據(jù)庫(kù)，它包含了SQL Se

4、rver2005包括的所有系統(tǒng)對(duì)象。 6）Distribution數(shù)據(jù)庫(kù) Distribution數(shù)據(jù)庫(kù)是一個(gè)分發(fā)數(shù)據(jù)庫(kù)。在利用數(shù)據(jù)庫(kù)復(fù)制技術(shù)實(shí)現(xiàn)數(shù)據(jù)同步更新時(shí)要進(jìn)行配置出版服務(wù)器，它是在完成了出版服務(wù)器的設(shè)置，并且在系統(tǒng)為該服務(wù)器的數(shù)形結(jié)構(gòu)中添加一個(gè)復(fù)制監(jiān)視器后生成的。,2、數(shù)據(jù)庫(kù)快照 數(shù)據(jù)庫(kù)快照是SQL Server2005企業(yè)版中新增的功能。數(shù)據(jù)庫(kù)快照是源數(shù)據(jù)庫(kù)的只讀、靜態(tài)視圖。多個(gè)快照可以位于一個(gè)源數(shù)據(jù)庫(kù)中，并且可以作為數(shù)據(jù)庫(kù)始終駐留在同一服務(wù)器實(shí)例上。創(chuàng)建快照時(shí)，每個(gè)數(shù)據(jù)庫(kù)快照在事務(wù)上與源數(shù)據(jù)庫(kù)一致。在被數(shù)據(jù)庫(kù)所有者顯式刪除之前，快照始終存在。,4.1數(shù)據(jù)庫(kù)的創(chuàng)建與管理,4.1.2

5、數(shù)據(jù)庫(kù)的創(chuàng)建,1準(zhǔn)備創(chuàng)建數(shù)據(jù)庫(kù),1） 確定數(shù)據(jù)庫(kù)的名稱、所有者（創(chuàng)建數(shù)據(jù)庫(kù)的用戶） 數(shù)據(jù)庫(kù)名稱：studentcourse 2）確定存儲(chǔ)該數(shù)據(jù)庫(kù)的數(shù)據(jù)文件的大小及文件空間增長(zhǎng)方式，確定關(guān)系、索引，及系統(tǒng)存儲(chǔ)參數(shù)的配置，確定數(shù)據(jù)庫(kù)的存取方法. 主數(shù)據(jù)文件：邏輯名稱studentcourse； 物理文件名：C:Datastudentcourse.mdf，初始大小：3MB，最大空間：UNLIMITED，空間增加量：1MB，屬于文件組primary。 次數(shù)據(jù)文件：邏輯名稱secondsc；物理文件名：C:mydbsecondsc.ndf，初始大?。?MB，最大空間：50MB，空間增加量：1MB，屬于文

6、件組group1。 日志文件：邏輯名稱studentcourse_log； 物理文件名：C:Logstudentcourse_log.ldf，始初大?。?MB，最大空間：20MB，空間增加量：10% 索引：每一數(shù)據(jù)表關(guān)于主關(guān)鍵字建立索引文件。,2使用SQL Server Management Studio創(chuàng)建數(shù)據(jù)庫(kù),3.1 關(guān)系數(shù)據(jù)庫(kù)設(shè)計(jì)思路,圖4.1 【創(chuàng)建數(shù)據(jù)庫(kù)】界面,圖4.2 【新建數(shù)據(jù)庫(kù)】對(duì)話框,3使用Transact-SQL語(yǔ)言創(chuàng)建數(shù)據(jù)庫(kù),1）命令格式 CREATE DATABASE ONPRIMARY ，N ，N LOG ON ，N 其中，N 表示設(shè)置文件屬性，格式如下。 (NAM

7、E=邏輯文件名， FILENAME=物理文件名稱 ，SIZE=數(shù)據(jù)庫(kù)文件的初始容量值 ，MAXSIZE=物理文件的最大容量值 |UNLIMITED ，F(xiàn)ILEGROWTH=增加容量值 ) ，N 其中，N 表示設(shè)置文件組屬性，格式如下。 FILEGROUP文件組名稱 DEFAULT ，N,2）參數(shù)說(shuō)明 （1）放在“ ”中的“”表示整個(gè)“ ”括起來(lái)的選項(xiàng)都可省略，如果不省，則“” 括起的選項(xiàng)不能省。使用“”分隔的多個(gè)選項(xiàng)，表示只能選擇其中一個(gè)。 （2）數(shù)據(jù)庫(kù)的名稱必須符合標(biāo)識(shí)符規(guī)則，最長(zhǎng)為128個(gè)字符。數(shù)據(jù)庫(kù)名稱在 SQL Server 的實(shí)例中必須唯一。數(shù)據(jù)庫(kù)的邏輯文件名是數(shù)據(jù)庫(kù)在SQL Ser

8、ver中的標(biāo)識(shí)符。FILENAME指定數(shù)據(jù)庫(kù)物理文件名稱和路徑，它和數(shù)據(jù)庫(kù)邏輯名稱一一對(duì)應(yīng)。文件組的邏輯名稱必須在數(shù)據(jù)庫(kù)中唯一，不能是系統(tǒng)提供的名稱 PRIMARY 和 PRIMARY_LOG。 （3）“ON”定義數(shù)據(jù)文件；“PRIMARY”定義主文件組中的文件；“LOG ON”定義日志文件。一個(gè)數(shù)據(jù)庫(kù)只能有一個(gè)主文件，如果沒(méi)有定義主文件，列在數(shù)據(jù)文件項(xiàng)的第一個(gè)文件就是主文件。 （4）數(shù)據(jù)庫(kù)文件容量單位可以是KB，MB，GB，TB，缺省值為MB，長(zhǎng)度必須為整數(shù)，主文件的，最小容量是Model數(shù)據(jù)庫(kù)的主文件長(zhǎng)度；對(duì)于其他類型文件，最小長(zhǎng)度為512KB。 （5）MAXSIZE：指定物理文件的最大容

9、量。如果不設(shè)置文件的最大尺寸，那么文件的增長(zhǎng)最大值將是磁盤(pán)的所有空間。UNLIMITED選項(xiàng)允許文件增長(zhǎng)到磁盤(pán)已滿。 （6）FILEGROWTH：指定文件每次增加容量的大小或百分比，當(dāng)FILEGROWTH=0時(shí)，表示文件不增長(zhǎng)。 （7）DEFAULT：指定命名文件組為數(shù)據(jù)庫(kù)中的默認(rèn)文件組。,3使用Transact-SQL語(yǔ)言創(chuàng)建數(shù)據(jù)庫(kù),圖4.11 用命令創(chuàng)建Studentcourse數(shù)據(jù)庫(kù)界面,CREATE DATABASE studentcourse -邏輯數(shù)據(jù)名稱：studentcourse ON PRIMARY -ON 子句指出文件屬于PRIMARY組 ( NAME =studentco

10、urse, -NAME指出了對(duì)應(yīng)的邏輯文件名 FILENAME=C:DATAstudentcourse.mdf, -FILENAME指出物理文件名 SIZE = 3072KB , -Size指出了初始分配空間 MAXSIZE = UNLIMITED, -MAXSIZE指出了最大空間 FILEGROWTH = 1024KB ), -FILEGROWTH指出了文件空間的增長(zhǎng)量 FILEGROUP group1 - FILEGROUP子句指出次數(shù)據(jù)文件.NDF ( NAME = N secondsc , FILENAME = NC:mydb secondsc.ndf , SIZE = 3072KB

11、, MAXSIZE =51200KB, FILEGROWTH = 1024KB ) LOG ON -LOG ON 子句指出新建數(shù)據(jù)庫(kù)的日志文件.LDF ( NAME = Nstudentcourse_log, FILENAME= C:LOGstudentcourse_log.ldf , SIZE = 1024KB , MAXSIZE = 20480KB , FILEGROWTH = 10%),3）創(chuàng)建學(xué)生選課數(shù)據(jù)庫(kù),4.1.3 管理數(shù)據(jù)庫(kù),1查看數(shù)據(jù)庫(kù)信息,1）命令格式 EXEC sp_helpdb 數(shù)據(jù)庫(kù)名 2）功能 查看指定數(shù)據(jù)庫(kù)的相關(guān)數(shù)據(jù)文件信息、數(shù)據(jù)庫(kù)擁有者、創(chuàng)建時(shí)間等信息。若缺省數(shù)據(jù)

12、庫(kù)名，則顯示所有數(shù)據(jù)庫(kù)信息。 【例4.1】查看學(xué)生選課“studentcourse”數(shù)據(jù)庫(kù)的信息。 方法一：使用SQL Server Management Studio查看數(shù)據(jù)庫(kù)信息 方法二：使用系統(tǒng)存儲(chǔ)過(guò)程命令查看數(shù)據(jù)庫(kù)信息。 EXEC sp_helpdb studentcourse 【例4.2】查看所有數(shù)據(jù)庫(kù)信息。 EXEC sp_helpdb,4.1.3 管理數(shù)據(jù)庫(kù),2打開(kāi)數(shù)據(jù)庫(kù),1）命令格式 USE 2）功能 使指定數(shù)據(jù)庫(kù)成為當(dāng)前數(shù)據(jù)庫(kù) 【例4.3】打開(kāi)學(xué)生選課“studentcourse”數(shù)據(jù)庫(kù)。 方法一：使用SQL Server Management Studio打開(kāi)數(shù)據(jù)庫(kù) 方法二

13、：使用命令。 USE studentcourse,3修改數(shù)據(jù)庫(kù),1）命令格式 Alter Database 數(shù)據(jù)庫(kù)名 AddFile,N To Filegroup 文件組名稱 |Add Log File ,N |Remove File 邏輯文件名稱 With Delete |Modify File |Modify Name=新數(shù)據(jù)庫(kù)名稱 |Add Filegroup新增文件組名稱 |Remove Filegroup 文件組名稱 |Modify Filegroup 原文件組名稱 文件組屬性|Name=新文件組名稱 2）功能 Add File：向數(shù)據(jù)庫(kù)添加文件。Add Log file：向數(shù)據(jù)庫(kù)添

14、加日志文件。Remove File：從數(shù)據(jù)庫(kù)中刪除文件。Modify File：對(duì)文件進(jìn)行修改，包括SIZE、FILEGROWTH和MAXSIZE，每次只能對(duì)一個(gè)屬性進(jìn)行修改。Modify Name：重新命名數(shù)據(jù)庫(kù)。Add | Remove | Modify Filegroup：向數(shù)據(jù)庫(kù)中添加刪除修改文件組,【例4.4】向數(shù)據(jù)庫(kù)Studentcourse中添加一個(gè)名為group2的文件組，并在該文件組中添加一個(gè)名為Studentcourse2、路徑為默認(rèn)的次數(shù)據(jù)文件，初始值大小為 2MB ，最大值為50MB，文件以1MB增長(zhǎng)；再添加一個(gè)名為Studentcourse_Log2的日志文件，初始值

15、大小為1MB ，最大值為100MB，文件以10%增長(zhǎng) 。然后對(duì)數(shù)據(jù)庫(kù)中Studentcourse2文件重命名為Studentcourse_2，最后把該文件從數(shù)據(jù)庫(kù)中移除。 方法一：使用SQL Server Management Studio 方法二：使用SQL語(yǔ)言修改數(shù)據(jù)庫(kù)。 命令如下所示： alter DATABASE studentcourse add filegroup group2 -新增group2文件組 GO,3修改數(shù)據(jù)庫(kù),4.1.3 管理數(shù)據(jù)庫(kù),4.1.3 管理數(shù)據(jù)庫(kù),alter DATABASE studentcourse add file (NAME =studentcour

16、se2, -新增studentcourse2次數(shù)據(jù)文件 FILENAME = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA studentcourse2.ndf, SIZE = 2MB , MAXSIZE =50MB, FILEGROWTH =1MB ) to filegroup group2 Go alter DATABASE studentcourse modify file -修改studentcourse主數(shù)據(jù)文件的文件增長(zhǎng)為2MB ( NAME =studentcourse, FILEGROWTH =2MB ) Go alte

17、r DATABASE studentcourse add LOG file -新增studentcourse_log2日志文件 ( NAME = Nstudentcourse_log2, FILENAME = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL DATA studentcourse_log2.ldf, SIZE =1MB , MAXSIZE = 100MB , FILEGROWTH = 10%) Go alter DATABASE studentcourse modify file (name=studentcourse2,newna

18、me=studentcourse_2) -對(duì)數(shù)據(jù)庫(kù)中studentcourse2次數(shù)據(jù)文件重命名成studentcourse_2 Go alter DATABASE studentcourse remove file studentcourse_2 -從數(shù)據(jù)庫(kù)中移除studentcourse_2次要數(shù)據(jù)文件,4、刪除數(shù)據(jù)庫(kù) 1）命令格式 DROP DATABASE ,N 2）功能 其功能是刪除指定數(shù)據(jù)庫(kù)。,1）命令格式 DBCC SHRINKDATABASE (數(shù)據(jù)庫(kù)名,Target_Percent) Notruncate|Truncateonly 2）功能 選擇Notruncate選項(xiàng)可以產(chǎn)

19、生由數(shù)據(jù)庫(kù)所保留的自由空間，而且這些空間不被操作系統(tǒng)使用；而Truncateonly選項(xiàng)的作用與其相反，它將自由空間留給操作系統(tǒng)。 如果只需要壓縮單一的一個(gè)文件，則使用DBCC SHRINKFILE，命令，要注意一個(gè)數(shù)據(jù)庫(kù)不能被壓縮到小于模板數(shù)據(jù)庫(kù)的大小。 【例4.6】壓縮學(xué)生選課studentcourse數(shù)據(jù)庫(kù)，使其最大可用空間為30%。 方法一：使用SQL Server Management Studio壓縮數(shù)據(jù)庫(kù) 方法二：使用SQL命令壓縮數(shù)據(jù)庫(kù) DBCC SHRINKDATABASE(Studentcourse, 30 ),4.1.3 管理數(shù)據(jù)庫(kù),5壓縮數(shù)據(jù)庫(kù),4.1.3 管理數(shù)據(jù)庫(kù),

20、【例4.7】壓縮學(xué)生選課數(shù)據(jù)庫(kù)studentcourse中的一個(gè)secondsc次數(shù)據(jù)文件，將其壓縮為2MB。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 DBCC SHRINKFILE (secondsc ,2),5壓縮數(shù)據(jù)庫(kù),4.2.1 數(shù)據(jù)系統(tǒng)視圖,1Sysobjects 系統(tǒng)視圖 2Syscolumns系統(tǒng)視圖 3Sysindexes系統(tǒng)視圖 4Sysusers系統(tǒng)視圖 5Sysdatabases系統(tǒng)視圖 6Sysdepends系統(tǒng)視圖 7Sysconstraints系統(tǒng)視圖,4.2 數(shù)據(jù)表的創(chuàng)建,SQL Server2005除了用戶

21、定義的數(shù)據(jù)表以外，還提供了系統(tǒng)表，它記錄了服務(wù)器配置及數(shù)據(jù)存儲(chǔ)信息，但在SQL Server2005中用戶無(wú)法直接查詢或更新系統(tǒng)表。 SQL Server2005將系統(tǒng)數(shù)據(jù)存儲(chǔ)在隱藏“資源”表中。只有通過(guò)具有權(quán)限的管理員連接，方可調(diào)用和查看。低級(jí)用戶必須使用系統(tǒng)視圖，從隱藏表和隱藏函數(shù)中獲得系統(tǒng)信息。,數(shù)據(jù)類型及其確定原則 決定要使用的數(shù)據(jù)類型時(shí)，請(qǐng)參照以下原則： （1）為列選擇一個(gè)合適的長(zhǎng)度。 （2）如果屬性值的長(zhǎng)度不會(huì)大幅改變，就使用固定長(zhǎng)度數(shù)據(jù)類型（Char和Nchar）。 （3）如果用戶存儲(chǔ)的字符串來(lái)源于不同的國(guó)家，就使用Unicode數(shù)據(jù)類型。 Unicode比Char支持更大范圍的

22、字符集。 SQL Server 的九大類數(shù)據(jù)類型 1）整型數(shù)據(jù)類型(Integer) 2）精確數(shù)字?jǐn)?shù)據(jù)類型(Exact Numeric) 3）近似數(shù)字?jǐn)?shù)據(jù)類型(Approximate Numeric) 4）貨幣數(shù)據(jù)類型(Monetary) 5）日期和時(shí)間數(shù)據(jù)類型(Date Time) 6）字符數(shù)據(jù)類型(Character) 7）二進(jìn)制數(shù)據(jù)類型 8）特殊數(shù)據(jù)類型 9）自定義數(shù)據(jù)類型(UDT)EXEC,4.2.2 數(shù)據(jù)類型,【例4.10】建一個(gè)以Datetime為基礎(chǔ)的出生日期（Birthday）可為空的數(shù)據(jù)類型。 方法一：使用SQL Server Management Studio 方法二：使用

23、SQL命令。 EXEC SP_ADDTYPE Birthday，Datetime，Null,4.2.2 數(shù)據(jù)類型,4.2.3 創(chuàng)建數(shù)據(jù)表結(jié)構(gòu),1熟悉CREATE TABLE語(yǔ)句的格式 1）CREATE TABLE語(yǔ)句的簡(jiǎn)化格式 CREATE TABLE 服務(wù)器名.數(shù)據(jù)庫(kù)名.架構(gòu)名.|數(shù)據(jù)庫(kù)名.架構(gòu)名.|架構(gòu)名.數(shù)據(jù)庫(kù)表名 ( 列名，數(shù)據(jù)類型 NOT NULL identity(初值,步長(zhǎng)) DEFAULT 默認(rèn)值 UNIQUE PRIMARY KEY CLUSTERED | NONCLUSTERED ， 列名，數(shù)據(jù)類型 NOT NULL DEFAULT 默認(rèn)值UNIQUE ，n ， 列名 AS

24、 計(jì)算列值的表達(dá)式，n ， CONSTRAINT 主鍵約束名 PRIMARY KEY(屬性名)， CONSTRAINT 檢查約束名 CHECK ( 邏輯表達(dá)式)，n， FOREIGN KEY (外鍵屬性) REFERENCES 參照表(參照屬性) ，n ) ON 文件組|默認(rèn)文件組,2）CREATE TABLE語(yǔ)句的說(shuō)明,【例4.11】創(chuàng)建數(shù)據(jù)庫(kù)“book_shop”的數(shù)據(jù)表book，數(shù)據(jù)表由書(shū)號(hào)、書(shū)名、出版社、出版日期、單價(jià)、數(shù)量、總價(jià)（單價(jià)*數(shù)量）、電子郵件地址和數(shù)據(jù)庫(kù)表使用者字段組成。其中書(shū)號(hào)列定義為主鍵并且為系統(tǒng)自動(dòng)編號(hào)即標(biāo)識(shí)列，種子值（起始值）為1000，增量為1，要求出版社字段的值

25、只能是高教、浙大、電子和中央四個(gè)之一，電子郵件地址字段中必須包含符號(hào)，單價(jià)必須大于0，數(shù)量必須大于等于0，出版日期的默認(rèn)值設(shè)置為當(dāng)前日期函數(shù)。 （1）標(biāo)識(shí)IDENTITY 屬性 定義列時(shí)，可以為一個(gè)表中的其中一列指定一個(gè)特殊的自動(dòng)增長(zhǎng)標(biāo)識(shí)屬性。每一個(gè)表只能有一個(gè)標(biāo)識(shí)屬性，標(biāo)識(shí)屬性有兩個(gè)參數(shù)：標(biāo)識(shí)初始值和增量。標(biāo)識(shí)屬性的數(shù)據(jù)類型只能是Bigint、Int、Smallint、Tinyint、Decimal和Numeric。如果對(duì)Decimal或Numeric數(shù)據(jù)類型設(shè)置標(biāo)識(shí)屬性，必須把它們定義為有0個(gè)小數(shù)位。標(biāo)識(shí)屬性不能是空值，系統(tǒng)自動(dòng)根據(jù)種子值和增量更新標(biāo)識(shí)列值。 例如：設(shè)置book表中屬性“書(shū)

26、號(hào)”為標(biāo)識(shí)列。 CREATE TABLE book ( 書(shū)號(hào) Int identity(1000,1) NOT FOR REPLICATION, ),（2）計(jì)算所得的列 我們還可以創(chuàng)建一種稱為計(jì)算所得的列的特殊列，它包含一個(gè)涉及表中一個(gè)或多個(gè)其他列的計(jì)算公式。在默認(rèn)情況下，計(jì)算所得的列包含計(jì)算公式的定義，但在物理上不存儲(chǔ)數(shù)據(jù)。返回?cái)?shù)據(jù)時(shí)，應(yīng)用該計(jì)算公式以返回一個(gè)結(jié)果值。然而，通過(guò)使用PERSISTED關(guān)鍵字，可以強(qiáng)制一個(gè)計(jì)算所得的列在物理上存儲(chǔ)數(shù)據(jù)。該關(guān)鍵字使公式計(jì)算在插入或修改行時(shí)發(fā)生，然后將計(jì)算結(jié)果存儲(chǔ)在表中。 例如：設(shè)置book表中屬性“總價(jià)”為計(jì)算所得的列。 CREATE TABLE

27、book ( 總價(jià) AS 單價(jià)*數(shù)量, 數(shù)據(jù)庫(kù)表使用者 AS USER_NAME(), ),（3）空值NULL約束 例如：設(shè)置book表中屬性“書(shū)名”不允許為空值。 CREATE TABLE book ( 書(shū)名 Char(20) NOT NULL, ) （4）PRIMARY KEY 約束 CREATE TABLE book ( 書(shū)號(hào) Int PRIMARY KEY CLUSTERED ) 或 CREATE TABLE book ( 書(shū)號(hào) Int, CONSTRAINT PK_sno PRIMARY KEY CLUSTERED ) 或 CREATE TABLE book ( 書(shū)號(hào) Int, CO

28、NSTRAINT PK_sno PRIMARY KEY CLUSTERED(書(shū)號(hào)) ),（5）UNIQUE 約束 UNIQUE 約束相應(yīng)屬性列的取值必須唯一，允許存在空值。 例如：對(duì)book表中屬性“書(shū)名”建立一個(gè)唯一約束。 CREATE TABLE book ( 書(shū)名 Char(20) UNIQUE, ) 或 CREATE TABLE book ( 書(shū)名 Char(20), CONSTRAINT U_sn UNIQUE(書(shū)名) ),（6）DEFAULT約束 每個(gè)屬性只能有一個(gè)默認(rèn)值。默認(rèn)值可以包含常量值、函數(shù)或NULL，但不能引用表中的其他列。數(shù)據(jù)類型為T(mén)imestamp或具有IDENTIT

29、Y屬性的列上不能定義默認(rèn)值。 使用INSERT和UPDATE語(yǔ)句時(shí)，系統(tǒng)會(huì)自動(dòng)提供默認(rèn)值。 例如：設(shè)置book表中屬性“出版日期”的默認(rèn)值為當(dāng)前日期。 CREATE TABLE book ( 出版日期 Datetime DEFAULT (getdate(), ),（7）CHECK 約束 每個(gè)屬性列可以有多個(gè)CHECK約束，約束條件是邏輯表達(dá)式，不能引用其他表。列級(jí)CHECK約束只能引用被約束的列，表級(jí)CHECK約束只能應(yīng)用同一表中的列。當(dāng)執(zhí)行INSERT和DELETE語(yǔ)句時(shí)，將檢查數(shù)據(jù)是否滿足約束。 例如：約束book表中屬性“出版社”只能取值為高教、浙大、電子和中央四個(gè)出版社之一，電子郵件地

30、址字段中必須包含符號(hào)，單價(jià)必須大于0，數(shù)量必須大于等于0。 CREATE TABLE book ( 出版社 char(20), 單價(jià) Smallint CHECK(單價(jià)0), 數(shù)量 Smallint CHECK(數(shù)量=0), 電子郵件地址 varchar(25) check(電子郵件地址 like %), CHECK(出版社 IN (高教,浙大、電子,中央) ),(8）FOREIGN KEY 約束 受FOREIGN KEY 約束的屬性的列中只能輸入NULL值或者在被引用的屬性列中存在的值。 CREATE TABLE SC ( 課程號(hào) char(3), FOREIGN KEY(課程號(hào)) REFE

31、RENCES C(課程號(hào)) ) 或 CREATE TABLE SC ( 課程號(hào) char(3), CONSTRAINT課程號(hào)_FK FOREIGN KEY(課程號(hào)) REFERENCES C(課程號(hào)) ),方法一：使用SQL Server Management Studio 方法二：使用命令。 EXEC sp_helpdb studentcourse CREATE database bookshop Go CREATE TABLE book (書(shū)號(hào) int identity(1000,1) NOT FOR REPLICATION PRIMARY KEY CLUSTERED, 書(shū)名 char(2

32、0) not null, 出版社 char(20), 出版日期 datetime DEFAULT (getdate(), 單價(jià) smallint check(單價(jià)0), 數(shù)量 smallint check(數(shù)量=0), 總價(jià) as 單價(jià)*數(shù)量, 電子郵件地址 varchar(25), check(電子郵件地址 like %), check (出版社 in (高教,浙大,電子,中央),2）CREATE TABLE語(yǔ)句的說(shuō)明,【例4.12】創(chuàng)建數(shù)據(jù)庫(kù)學(xué)生選課“studentcourse”的數(shù)據(jù)表S、C、SC，數(shù)據(jù)表結(jié)構(gòu)如表3.6，表3.7，表3.8所示 各表的完整性約束如表3.9 ，表3.10 ，

33、表3.11所示。 方法一：使用SQL Server Management Studio創(chuàng)建數(shù)據(jù)表 方法二：使用SQL命令。 1）創(chuàng)建課程表C的語(yǔ)句如下： CREATE TABLE C ( 課程號(hào) Char (3) NOT NULL, 課程名 Varchar (20) NOT NULL, 學(xué)分 Smallint NULL, 預(yù)選課程號(hào) Char (3) NULL, 教師 Char (8) NULL, CONSTRAINT FK_Pcno FOREIGN KEY( 預(yù)選課程號(hào) ) REFERENCES C (課程號(hào)) CONSTRAINT CK_Cno CHECK ( 課程號(hào) Like 0-9 0

34、-9 ) CONSTRAINT PK_C PRIMARY KEY CLUSTERED (課程號(hào) ASC ) ) ON PRIMARY,2）CREATE TABLE語(yǔ)句的說(shuō)明,【例4.12】創(chuàng)建數(shù)據(jù)庫(kù)學(xué)生選課“studentcourse”的數(shù)據(jù)表S、C、SC，數(shù)據(jù)表結(jié)構(gòu)如表3.6，表3.7，表3.8所示 方法二：使用SQL命令。 2）創(chuàng)建學(xué)生基本信息表S的語(yǔ)句如下： CREATE TABLE S ( 學(xué)號(hào) Char (6) NOT NULL DEFAULT (J0400), 姓名 Char (8) NOT NULL, 性別 Char (2) NOT NULL, 出生日期 Datetime NOT

35、 NULL DEFAULT (19800101), 系 Varchar (20) NOT NULL, 電話 Char (8) NULL, CHECK (學(xué)號(hào) Like A-Z 0-9 0-9 0-9 0-9 ), CHECK (性別 =女 OR 性別 =男), CHECK (電話 Like 0-90-90-9 -0-90-90-90-9 ), CONSTRAINT PK_S PRIMARY KEY CLUSTERED ( 學(xué)號(hào) ASC) ) ON PRIMARY,2）CREATE TABLE語(yǔ)句的說(shuō)明,【例4.12】創(chuàng)建數(shù)據(jù)庫(kù)學(xué)生選課“studentcourse”的數(shù)據(jù)表S、C、SC，數(shù)據(jù)表結(jié)

36、構(gòu)如表3.6，表3.7，表3.8所示 各表的完整性約束如表3.9 ，表3.10 ，表3.11所示。 方法二：使用SQL命令。 3）創(chuàng)建學(xué)生選課數(shù)據(jù)表SC的語(yǔ)句如下： CREATE TABLE SC ( 學(xué)號(hào) Char (6) NOT NULL, 課程號(hào) Char (3) NOT NULL, 成績(jī) Smallint NULL, FOREIGN KEY(課程號(hào)) REFERENCES C (課程號(hào)), FOREIGN KEY( 學(xué)號(hào) ) REFERENCES S (學(xué)號(hào)), CHECK (成績(jī)=(0) AND 成績(jī)=(100), PRIMARY KEY CLUSTERED (學(xué)號(hào) ASC, 課程號(hào)

37、 ASC),2）CREATE TABLE語(yǔ)句的說(shuō)明,【例4.13】返回有關(guān)所有對(duì)象的信息。 USE Master; GO EXEC Sp_Help; GO 【例4.14】返回學(xué)生選課“studentcourse”中學(xué)生表的信息。 USE Studentcourse GO EXEC Sp_Help s,4.2.5 查看數(shù)據(jù)表,1修改表的結(jié)構(gòu) 數(shù)據(jù)表創(chuàng)建以后，經(jīng)常會(huì)需要對(duì)原先的某些定義進(jìn)行一定的修改，例如添加、修改、刪除列以及各種約束，修改列屬性值。,1）命令格式 ALTER TABLE ALTER COLUMN 類型（寬度）NULL|NOT NULL |ADD 類型（寬度）NULL|NOT NU

38、LL完整性約束,n |DROP COLUMN CASCADERESTRICT,n |DROP CONSTRAINT|ALL ,n 2）功能 ALTER TABLE：將要修改的當(dāng)前數(shù)據(jù)庫(kù)中的指定數(shù)據(jù)表的表名。 ALTER COLUMN：修改當(dāng)前數(shù)據(jù)庫(kù)中的指定數(shù)據(jù)表的指定屬性。 ADD：向當(dāng)前數(shù)據(jù)庫(kù)中的指定數(shù)據(jù)表增加指定屬性或列級(jí)完整性約束。 DROP COLUMN：刪除當(dāng)前數(shù)據(jù)庫(kù)中的指定數(shù)據(jù)表中的指定屬性。 DROP：刪除當(dāng)前數(shù)據(jù)庫(kù)中的指定數(shù)據(jù)表中的指定列級(jí)完整性約束。,4.2.6 修改數(shù)據(jù)表,【例4.15】修改當(dāng)前數(shù)據(jù)庫(kù)“studentcourse”中S表的系屬性改成char(25)，增加一個(gè)

39、入學(xué)時(shí)間字段，它的數(shù)據(jù)類型為date time，并設(shè)置默認(rèn)值為getdate()，最后刪除入學(xué)時(shí)間字段。 方法二：使用命令。 ALTER TABLE s ALTER COLUMN 系char(25) Go ALTER TABLE s ADD 入學(xué)時(shí)間 datetime Go ALTER TABLE s ADD CONSTRAINT DF_sj DEFAULT(getdate() for 入學(xué)時(shí)間 或增加入學(xué)時(shí)間字段與默認(rèn)值進(jìn)行一次性設(shè)置，操作如下： ALTER TABLE s ADD 入學(xué)時(shí)間 datetime CONSTRAINT DF_sj DEFAULT(getdate() ALTER

40、TABLE s drop DF_sj ALTER TABLE s drop column 入學(xué)時(shí)間 注意:要?jiǎng)h除字段,必須先刪除該字段的所有約束,4.2.6 修改數(shù)據(jù)表,2修改表的名稱,1）命令格式 Sp_rename , 2）功能 重命名當(dāng)前數(shù)據(jù)庫(kù)中的指定數(shù)據(jù)表名。 注意：當(dāng)重命名表時(shí)，表名在包含該表的各數(shù)據(jù)庫(kù)關(guān)系圖中自動(dòng)更新，當(dāng)保存表或關(guān)系圖時(shí)，表名在數(shù)據(jù)庫(kù)中被更新。在重命名表之前需慎重考慮。如果現(xiàn)有查詢、視圖、用戶定義函數(shù)、存儲(chǔ)過(guò)程或程序引用該表，則更改表名將使這些對(duì)象無(wú)效。 【例4.16】重命名數(shù)據(jù)庫(kù)bookshop的數(shù)據(jù)表book名稱，改為“書(shū)籍資料”。 方法一：使用SQL Serv

41、er Management Studio 方法二：使用SQL命令。 Sp_rename book, 書(shū)籍資料,4.2.6 修改數(shù)據(jù)表,1）命令格式 DROP TABLE 表名 2）功能 刪除表 【例4.17】刪除當(dāng)前數(shù)據(jù)庫(kù)中的表S。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 DROP TABLE S 例4.18刪除其他數(shù)據(jù)庫(kù)中的表。 DROP TABLE Studentcourse.dbo.s 注意：基本表定義一旦刪除，表中的數(shù)據(jù)和在此表上建立的索引將自動(dòng)被刪除掉。DELETE命令用于從一個(gè)表中刪除行，而要?jiǎng)h除整個(gè)表，應(yīng)使用DROP TAB

42、LE命令。要執(zhí)行該命令，執(zhí)行者必須是Sysadmin固定服務(wù)器角色的成員、數(shù)據(jù)庫(kù)所有者固定數(shù)據(jù)庫(kù)角色的成員或者是該表的所有者。,4.2.7 刪除數(shù)據(jù)表,4.3.1 使用SQL Server Management Studio插入、修改與刪除數(shù)據(jù),4.3 數(shù)據(jù)庫(kù)表的操作,4.3.2 使用T-SQL語(yǔ)句進(jìn)行插入、修改和刪除數(shù)據(jù),1 插入數(shù)據(jù),2更新數(shù)據(jù),3刪除記錄,插入數(shù)據(jù)記錄的方法有三種，第一種是利用SQL Server Management Studio，第二種是使用SELECT查詢語(yǔ)句（本節(jié)暫不介紹），第三種是使用INSERT命令。 1）命令格式 INSERT INTO 數(shù)據(jù)表名 (列名表)

43、 VALUES (元組值) INSERT INTO 數(shù)據(jù)表名(列名表) SELECT 查詢語(yǔ)句 INSERT INTO 數(shù)據(jù)表名(列名表) DEFAULT VALUES 2）功能 向指定數(shù)據(jù)表的屬性列插入數(shù)據(jù)，VALUES 后跟的元組值為屬性列提供數(shù)據(jù)。其中列名表中的屬性排列順序和VALUES 后跟的元組值的排列順序要一致。對(duì)應(yīng)的數(shù)據(jù)類型要一致。如果沒(méi)有指定列名表，則表示數(shù)據(jù)表中的所有屬性列。 如果在列名表中沒(méi)有列出所有的屬性列，對(duì)于未指定的列，如果該列有默認(rèn)值，則以默認(rèn)值填充，如果允許空值NULL，則向該列插入空值NULL，如果具有IDENTITY屬性，則插入下一個(gè)增量值。如果具有Times

44、tamp數(shù)據(jù)類型，則獲取當(dāng)前的時(shí)間戳值。 “DEFAULT VALUES”選項(xiàng)會(huì)將默認(rèn)值插入到該屬性列中，如果某列沒(méi)有默認(rèn)值，允許則向該列插入空值NULL，如果某列不允許空值也沒(méi)有默認(rèn)值，則會(huì)出錯(cuò)。,1 插入數(shù)據(jù),【例4.19】以下示例使用屬性列顯式指定插入到每個(gè)列的值。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 USE Studentcourse GO INSERT INTO s(學(xué)號(hào),姓名,性別,出生日期,系) VALUES (L0401, 張?jiān)讫? 男, 1987-11-11,路橋系) Go SELECT * FROM s,1 插入數(shù)

45、據(jù),【例4.20】將查詢結(jié)果插入數(shù)據(jù)表，如將學(xué)號(hào)L0401、成績(jī)80以及課程表中所有課程號(hào)插入到sc中。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 USE Studentcourse GO INSERT INTO sc SELECT J0401,課程號(hào),80 FROM c Go SELECT * FROM sc,1 插入數(shù)據(jù),1）命令格式 UPDATE 基本表名 SET 列名=值表達(dá)式，列名=值表達(dá)式 WHERE 條件表達(dá)式 2）功能 更新指定基本表，滿足WHERE子句條件的記錄的指定屬性值。其中值表達(dá)式可以是常量、變量、表達(dá)式。若缺省WH

46、ERE，則修改表中的所有元組。但在進(jìn)行修改操作時(shí)，需注意數(shù)據(jù)庫(kù)的一致性。使用UPDATE可以一次修改多列的值，這樣可以提高效率。 【例4.21】使用簡(jiǎn)單UPDATE語(yǔ)句 更新s表中的所有行出生日期列中的值變?yōu)樵錾掌谥导?。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令 USE Studentcourse GO UPDATE s SET 出生日期=出生日期+1,2更新數(shù)據(jù),【例4.22】帶WHERE子句使用UPDATE語(yǔ)句。 將選C01課程的學(xué)號(hào)是L0401的學(xué)生的成績(jī)改成85分。 USE Studentcourse GO UPDATE sc

47、SET 成績(jī)=85 WHERE 課程號(hào)=C01 AND 學(xué)號(hào)=L0401 GO SELECT * FROM sc GO 【例4.23】帶子查詢的UPDATE語(yǔ)句 將張?jiān)讫垖W(xué)生的成績(jī)減少5分。 USE Studentcourse GO UPDATE sc SET 成績(jī)=成績(jī)-5 WHERE 學(xué)號(hào) IN (SELECT 學(xué)號(hào) FROM s WHERE 姓名=張?jiān)讫? GO SELECT * FROM sc,2更新數(shù)據(jù),SQL的刪除操作是指從基本表或視圖中刪除一條或多條元組記錄。 1）命令格式 DELETE FROM 基本表名 WHERE 條件表達(dá)式 2）功能 從指定表中刪除滿足WHERE子句條件的

48、所有元組，若缺省WHERE，則刪除表中的全部元組。DELETE語(yǔ)句刪除的是表中的數(shù)據(jù)，而不是表的定義或表的結(jié)構(gòu)。 【例4.24】刪除學(xué)號(hào)為L(zhǎng)0401的學(xué)生選課信息。 DELETE FROM sc Where 學(xué)號(hào)=j0401 GO SELECT * FROM sc 【例4.25】從SC 表中刪除所有行 DELETE FROM sc GO SELECT * FROM sc,3刪除記錄,4、刪除所有行,（1）命令格式 TRUNCATE TABLE 數(shù)據(jù)庫(kù)名.架構(gòu)名. 架構(gòu)名. 表名; （2）功能 使用TRUNCATE TABLE命令刪除所有行。 例4.26下面的示例使用TRUNCATE命令從學(xué)生選

49、課數(shù)據(jù)表SC中刪除所有行。 TRUNCATE TABLE studentcourse.DBO.sc 注： TRUNCATE命令刪除表中的所有行，而不記錄單個(gè)行刪除操作。TRUNCATE TABLE 在功能上與沒(méi)有 WHERE 子句的 DELETE 語(yǔ)句相同；但是，TRUNCATE TABLE 速度更快，使用的系統(tǒng)資源和事務(wù)日志資源更少。,1索引的作用 1）加速數(shù)據(jù)檢索 2）優(yōu)化查詢 3）強(qiáng)制數(shù)據(jù)完整性,4.4 索引管理,4.4.1 索引概述 索引是數(shù)據(jù)庫(kù)中一種特殊的對(duì)象，它是對(duì)數(shù)據(jù)表中一個(gè)或多個(gè)字段的值進(jìn)行排序而創(chuàng)建的一種存儲(chǔ)結(jié)構(gòu)，主要用于提高表中數(shù)據(jù)的查詢速度。數(shù)據(jù)庫(kù)中的索引與書(shū)籍中的目錄類

50、似，在一本書(shū)中，利用目錄可以快速查找所需要的信息，而無(wú)須閱讀整本書(shū)；在數(shù)據(jù)庫(kù)中，可以利用索引快速查找需要的數(shù)據(jù)，而無(wú)須對(duì)整個(gè)表進(jìn)行掃描。,4.4.1 索引概述,索引是SQL Server使用的內(nèi)部表結(jié)構(gòu)，它是基于表中的一個(gè)或多個(gè)列的值，提供對(duì)表中行的快速存取。,2、索引的分類 SQL SERVER 2005支持在表中任何列上定義索引，按索引的組織方式，可以分為兩類：聚集索引和非聚集索引。 1）聚集索引 在聚集索引中，表中各記錄的物理順序與索引的邏輯順序相同，只有在表中建立了一個(gè)聚集索引后，數(shù)據(jù)才會(huì)按照索引鍵值的順序存儲(chǔ)到表中。由于一個(gè)表中的數(shù)據(jù)只能按照一種順序存儲(chǔ)，所以在表中只能建立一個(gè)聚集索

51、引。通常在主鍵上創(chuàng)建聚集索引。 2）非聚集索引 非聚集索引是完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)，表中的數(shù)據(jù)行不按非聚集索引的順序排序和存儲(chǔ)。在非聚集索引內(nèi)，從索引行指向數(shù)據(jù)行的指針?lè)Q為行定位器。在檢索數(shù)據(jù)時(shí)，SQL Server先在非聚集索引上搜索，找到相關(guān)信息后，再利用行定位器，找到數(shù)據(jù)表中的數(shù)據(jù)行。一個(gè)表上可以建立多個(gè)非聚集索引。 3）唯一索引 唯一索引要求所有數(shù)據(jù)行中任意兩行中的被索引列不能存在重復(fù)值。,1）命令格式 CREATEUNIQUECLUSTERED|NOCLUSTERED INDEX 索引名 ON數(shù)據(jù)表名|視圖名（字段名表ASC|DESC,n） WITH PAD_INDEX ,FILLFACTOR=填充因子 ,IGNORE_DUP_KEY ,STATISTICS_NORECOMPUTE ON 文件組名 2）功能 見(jiàn)P89,4.4.2 創(chuàng)