SQL Server 2005復(fù)習(xí)資料 (3).ppt

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

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

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

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

5、數(shù)據(jù)庫的創(chuàng)建,1準(zhǔn)備創(chuàng)建數(shù)據(jù)庫,1） 確定數(shù)據(jù)庫的名稱、所有者（創(chuàng)建數(shù)據(jù)庫的用戶） 數(shù)據(jù)庫名稱：studentcourse 2）確定存儲該數(shù)據(jù)庫的數(shù)據(jù)文件的大小及文件空間增長方式，確定關(guān)系、索引，及系統(tǒng)存儲參數(shù)的配置，確定數(shù)據(jù)庫的存取方法. 主數(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，始初大小：1MB，最大空間：20MB，空間增加量：10% 索引：每一數(shù)據(jù)表關(guān)于主關(guān)鍵字建立索引文件。,2使用SQL Server Management Studio創(chuàng)建數(shù)據(jù)庫,3.1 關(guān)系數(shù)據(jù)庫設(shè)計思路,圖4.1 【創(chuàng)建數(shù)據(jù)庫】界面,圖4.2 【新建數(shù)據(jù)庫】對話框,3使用Transact-SQL語言創(chuàng)建數(shù)據(jù)庫,1）命令格式 CREATE DATABASE ONPRIMARY ，N ，N LOG ON ，N 其中，N 表示設(shè)置文件屬性，格式如下。 (NAM

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

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

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

10、urse, -NAME指出了對應(yīng)的邏輯文件名 FILENAME=C:DATAstudentcourse.mdf, -FILENAME指出物理文件名 SIZE = 3072KB , -Size指出了初始分配空間 MAXSIZE = UNLIMITED, -MAXSIZE指出了最大空間 FILEGROWTH = 1024KB ), -FILEGROWTH指出了文件空間的增長量 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ù)庫的日志文件.LDF ( NAME = Nstudentcourse_log, FILENAME= C:LOGstudentcourse_log.ldf , SIZE = 1024KB , MAXSIZE = 20480KB , FILEGROWTH = 10%),3）創(chuàng)建學(xué)生選課數(shù)據(jù)庫,4.1.3 管理數(shù)據(jù)庫,1查看數(shù)據(jù)庫信息,1）命令格式 EXEC sp_helpdb 數(shù)據(jù)庫名 2）功能 查看指定數(shù)據(jù)庫的相關(guān)數(shù)據(jù)文件信息、數(shù)據(jù)庫擁有者、創(chuàng)建時間等信息。若缺省數(shù)據(jù)

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

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

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

15、大小為1MB ，最大值為100MB，文件以10%增長 。然后對數(shù)據(jù)庫中Studentcourse2文件重命名為Studentcourse_2，最后把該文件從數(shù)據(jù)庫中移除。 方法一：使用SQL Server Management Studio 方法二：使用SQL語言修改數(shù)據(jù)庫。 命令如下所示： alter DATABASE studentcourse add filegroup group2 -新增group2文件組 GO,3修改數(shù)據(jù)庫,4.1.3 管理數(shù)據(jù)庫,4.1.3 管理數(shù)據(jù)庫,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ù)文件的文件增長為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) -對數(shù)據(jù)庫中studentcourse2次數(shù)據(jù)文件重命名成studentcourse_2 Go alter DATABASE studentcourse remove file studentcourse_2 -從數(shù)據(jù)庫中移除studentcourse_2次要數(shù)據(jù)文件,4、刪除數(shù)據(jù)庫 1）命令格式 DROP DATABASE ,N 2）功能 其功能是刪除指定數(shù)據(jù)庫。,1）命令格式 DBCC SHRINKDATABASE (數(shù)據(jù)庫名,Target_Percent) Notruncate|Truncateonly 2）功能 選擇Notruncate選項可以產(chǎn)

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

20、【例4.7】壓縮學(xué)生選課數(shù)據(jù)庫studentcourse中的一個secondsc次數(shù)據(jù)文件，將其壓縮為2MB。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 DBCC SHRINKFILE (secondsc ,2),5壓縮數(shù)據(jù)庫,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ù)存儲信息，但在SQL Server2005中用戶無法直接查詢或更新系統(tǒng)表。 SQL Server2005將系統(tǒng)數(shù)據(jù)存儲在隱藏“資源”表中。只有通過具有權(quán)限的管理員連接，方可調(diào)用和查看。低級用戶必須使用系統(tǒng)視圖，從隱藏表和隱藏函數(shù)中獲得系統(tǒng)信息。,數(shù)據(jù)類型及其確定原則 決定要使用的數(shù)據(jù)類型時，請參照以下原則： （1）為列選擇一個合適的長度。 （2）如果屬性值的長度不會大幅改變，就使用固定長度數(shù)據(jù)類型（Char和Nchar）。 （3）如果用戶存儲的字符串來源于不同的國家，就使用Unicode數(shù)據(jù)類型。 Unicode比Char支持更大范圍的

22、字符集。 SQL Server 的九大類數(shù)據(jù)類型 1）整型數(shù)據(jù)類型(Integer) 2）精確數(shù)字數(shù)據(jù)類型(Exact Numeric) 3）近似數(shù)字數(shù)據(jù)類型(Approximate Numeric) 4）貨幣數(shù)據(jù)類型(Monetary) 5）日期和時間數(shù)據(jù)類型(Date Time) 6）字符數(shù)據(jù)類型(Character) 7）二進制數(shù)據(jù)類型 8）特殊數(shù)據(jù)類型 9）自定義數(shù)據(jù)類型(UDT)EXEC,4.2.2 數(shù)據(jù)類型,【例4.10】建一個以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語句的格式 1）CREATE TABLE語句的簡化格式 CREATE TABLE 服務(wù)器名.數(shù)據(jù)庫名.架構(gòu)名.|數(shù)據(jù)庫名.架構(gòu)名.|架構(gòu)名.數(shù)據(jù)庫表名 ( 列名，數(shù)據(jù)類型 NOT NULL identity(初值,步長) DEFAULT 默認值 UNIQUE PRIMARY KEY CLUSTERED | NONCLUSTERED ， 列名，數(shù)據(jù)類型 NOT NULL DEFAULT 默認值UNIQUE ，n ， 列名 AS

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

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

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

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

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

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

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

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

32、0) not null, 出版社 char(20), 出版日期 datetime DEFAULT (getdate(), 單價 smallint check(單價0), 數(shù)量 smallint check(數(shù)量=0), 總價 as 單價*數(shù)量, 電子郵件地址 varchar(25), check(電子郵件地址 like %), check (出版社 in (高教,浙大,電子,中央),2）CREATE TABLE語句的說明,【例4.12】創(chuàng)建數(shù)據(jù)庫學(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的語句如下： CREATE TABLE C ( 課程號 Char (3) NOT NULL, 課程名 Varchar (20) NOT NULL, 學(xué)分 Smallint NULL, 預(yù)選課程號 Char (3) NULL, 教師 Char (8) NULL, CONSTRAINT FK_Pcno FOREIGN KEY( 預(yù)選課程號 ) REFERENCES C (課程號) CONSTRAINT CK_Cno CHECK ( 課程號 Like 0-9 0

34、-9 ) CONSTRAINT PK_C PRIMARY KEY CLUSTERED (課程號 ASC ) ) ON PRIMARY,2）CREATE TABLE語句的說明,【例4.12】創(chuàng)建數(shù)據(jù)庫學(xué)生選課“studentcourse”的數(shù)據(jù)表S、C、SC，數(shù)據(jù)表結(jié)構(gòu)如表3.6，表3.7，表3.8所示 方法二：使用SQL命令。 2）創(chuàng)建學(xué)生基本信息表S的語句如下： CREATE TABLE S ( 學(xué)號 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é)號 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é)號 ASC) ) ON PRIMARY,2）CREATE TABLE語句的說明,【例4.12】創(chuàng)建數(shù)據(jù)庫學(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的語句如下： CREATE TABLE SC ( 學(xué)號 Char (6) NOT NULL, 課程號 Char (3) NOT NULL, 成績 Smallint NULL, FOREIGN KEY(課程號) REFERENCES C (課程號), FOREIGN KEY( 學(xué)號 ) REFERENCES S (學(xué)號), CHECK (成績=(0) AND 成績=(100), PRIMARY KEY CLUSTERED (學(xué)號 ASC, 課程號

37、 ASC),2）CREATE TABLE語句的說明,【例4.13】返回有關(guān)所有對象的信息。 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)常會需要對原先的某些定義進行一定的修改，例如添加、修改、刪除列以及各種約束，修改列屬性值。,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ù)庫中的指定數(shù)據(jù)表的表名。 ALTER COLUMN：修改當(dāng)前數(shù)據(jù)庫中的指定數(shù)據(jù)表的指定屬性。 ADD：向當(dāng)前數(shù)據(jù)庫中的指定數(shù)據(jù)表增加指定屬性或列級完整性約束。 DROP COLUMN：刪除當(dāng)前數(shù)據(jù)庫中的指定數(shù)據(jù)表中的指定屬性。 DROP：刪除當(dāng)前數(shù)據(jù)庫中的指定數(shù)據(jù)表中的指定列級完整性約束。,4.2.6 修改數(shù)據(jù)表,【例4.15】修改當(dāng)前數(shù)據(jù)庫“studentcourse”中S表的系屬性改成char(25)，增加一個

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

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

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

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

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

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

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

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

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

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

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

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

51、引。通常在主鍵上創(chuàng)建聚集索引。 2）非聚集索引 非聚集索引是完全獨立于數(shù)據(jù)行的結(jié)構(gòu)，表中的數(shù)據(jù)行不按非聚集索引的順序排序和存儲。在非聚集索引內(nèi)，從索引行指向數(shù)據(jù)行的指針稱為行定位器。在檢索數(shù)據(jù)時，SQL Server先在非聚集索引上搜索，找到相關(guān)信息后，再利用行定位器，找到數(shù)據(jù)表中的數(shù)據(jù)行。一個表上可以建立多個非聚集索引。 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）功能 見P89,4.4.2 創(chuàng)