第4章

數據庫管理本章導讀本章主要介紹了有關數據表的操作，包括表的創建、修改、刪除和建立索引等操作以及表中數據維護的有關操作和方法，基本掌握使用SQLServerManagementStudio和T-SQL語句對表的操作和表的數據操作，包括創建表、刪除表、對表中字段建立索引、向表中增加數據、修改數據、刪除數據等。讀者要對SQLServer2019中的數據類型有一個清楚的認識。4.1數據庫的創建與管理

4.1.1SQLServer系統數據庫1．系統數據庫1）Master數據庫2）Model數據庫3）Msdb數據庫4）Tempdb數據庫2．數據庫快照數據庫快照是源數據庫的只讀、靜態視圖。4.1數據庫的創建與管理

4.1.2數據庫的創建1．準備創建數據庫1）確定數據庫的名稱、所有者（創建數據庫的用戶）數據庫名稱：studentcourse2）確定存儲該數據庫的數據文件的大小及文件空間增長方式，確定關系、索引，及系統存儲參數的配置，確定數據庫的存取方法.主數據文件：邏輯名稱studentcourse；物理文件名：C:\Data\studentcourse.mdf，初始大小：8MB，最大空間：UNLIMITED，空間增加量：1MB，屬于文件組primary。次數據文件：邏輯名稱secondsc；物理文件名：C:\mydb\secondsc.ndf，初始大小：5MB，最大空間：50MB，空間增加量：1MB，屬于文件組group1。日志文件：邏輯名稱studentcourse_log；物理文件名：C:\Log\studentcourse_log.ldf，始初大小：4MB，最大空間：20MB，空間增加量：10%索引：每一數據表關于主關鍵字建立索引文件。2．使用SQLServerManagementStudio創建數據庫3.1關系數據庫設計思路圖4.1【創建數據庫】界面圖4.2【新建數據庫】對話框3．使用Transact-SQL語言創建數據庫1）命令格式CREATEDATABASE<數據庫名>[ON[PRIMARY][<Filespec>[，…N]][，<Filegroupspec>[，…N]]][LOGON{<Filespec>[，…N]}]

其中，[<Filespec>[，…N]]表示設置文件屬性，格式如下。

([NAME=邏輯文件名，]FILENAME=‘物理文件名稱’

[，SIZE=數據庫文件的初始容量值][，MAXSIZE={物理文件的最大容量值|UNLIMITED}][，FILEGROWTH=增加容量值])[，…N]其中[<Filegroupspec>[，…N]]表示設置文件組屬性，格式如下。FILEGROUP文件組名稱[DEFAULT]<Filespec>[，…N]2）參數說明

（1）放在“[]”中的“<>”表示整個“[]”括起來的選項都可省略，如果不省，則“<>”

括起的選項不能省。使用“｜”分隔的多個選項，表示只能選擇其中一個。（2）數據庫的名稱必須符合標識符規則，最長為128個字符。數據庫名稱在SQLServer的實例中必須唯一。數據庫的邏輯文件名是數據庫在SQLServer中的標識符。FILENAME指定數據庫物理文件名稱和路徑，它和數據庫邏輯名稱一一對應。文件組的邏輯名稱必須在數據庫中唯一，不能是系統提供的名稱PRIMARY和PRIMARY_LOG。（3）“ON”定義數據文件；“PRIMARY”定義主文件組中的文件；“LOGON”定義日志文件。一個數據庫只能有一個主文件，如果沒有定義主文件，列在數據文件項的第一個文件就是主文件。（4）數據庫文件容量單位可以是KB，MB，GB，TB，缺省值為MB，長度必須為整數，主文件的，最小容量是Model數據庫的主文件長度；對于其他類型文件，最小長度為512KB。（5）MAXSIZE：指定物理文件的最大容量。如果不設置文件的最大尺寸，那么文件的增長最大值將是磁盤的所有空間。UNLIMITED選項允許文件增長到磁盤已滿。（6）FILEGROWTH：指定文件每次增加容量的大小或百分比，當FILEGROWTH=0時，表示文件不增長。（7）DEFAULT：指定命名文件組為數據庫中的默認文件組。3．使用Transact-SQL語言創建數據庫CREATEDATABASE

studentcourse--邏輯數據名稱：studentcourseONPRIMARY--ON子句指出文件屬于PRIMARY組

(NAME=‘studentcourse’,--NAME指出了對應的邏輯文件名

FILENAME=‘C:\DATA\studentcourse.mdf’,--FILENAME指出物理文件名

SIZE=8MB,--Size指出了初始分配空間

MAXSIZE=UNLIMITED,--MAXSIZE指出了最大空間

FILEGROWTH=1024KB),--FILEGROWTH指出了文件空間的增長量

FILEGROUP[group1]--FILEGROUP子句指出次數據文件.NDF(NAME=N'secondsc',FILENAME=N'C:\mydb\secondsc.ndf',SIZE=5120KB,MAXSIZE=51200KB,FILEGROWTH=1024KB)LOGON--LOGON子句指出新建數據庫的日志文件.LDF(NAME=N'studentcourse_log',FILENAME='C:\LOG\studentcourse_log.ldf',SIZE=4096KB,MAXSIZE=20480KB,FILEGROWTH=10%)3）創建學生選課數據庫4.1.3管理數據庫1．查看數據庫信息1）命令格式EXECsp_helpdb[數據庫名]2）功能查看指定數據庫的相關數據文件信息、數據庫擁有者、創建時間等信息。若缺省數據庫名，則顯示所有數據庫信息。【例4.1】查看學生選課“studentcourse”數據庫的信息。方法一：使用SQLServerManagementStudio查看數據庫信息方法二：使用系統存儲過程命令查看數據庫信息。EXECsp_helpdbstudentcourse【例4.2】查看所有數據庫信息。EXECsp_helpdb4.1.3管理數據庫2．打開數據庫1）命令格式USE<數據庫名>2）功能使指定數據庫成為當前數據庫【例4.3】打開學生選課“studentcourse”數據庫。方法一：使用SQLServerManagementStudio打開數據庫方法二：使用命令。USEstudentcourse3．修改數據庫1）命令格式AlterDatabase數據庫名{Add

File<Filespec>[,…N][ToFilegroup文件組名稱]|AddLogFile<Filespec>[,…N]|RemoveFile邏輯文件名稱[WithDelete]|ModifyFile<Filespec>|ModifyName=新數據庫名稱|AddFilegroup新增文件組名稱|RemoveFilegroup文件組名稱|ModifyFilegroup原文件組名稱{文件組屬性|Name=新文件組名稱}}2）功能AddFile：向數據庫添加文件。AddLogfile：向數據庫添加日志文件。RemoveFile：從數據庫中刪除文件。ModifyFile：對文件進行修改，包括SIZE、FILEGROWTH和MAXSIZE，每次只能對一個屬性進行修改。ModifyName：重新命名數據庫。Add|Remove|

ModifyFilegroup：向數據庫中添加刪除修改文件組【例4.4】向數據庫Studentcourse中添加一個名為group2的文件組，并在該文件組中添加一個名為Studentcourse2、路徑為默認的次數據文件，初始值大小為5MB，最大值為50MB，文件以1MB增長；再添加一個名為Studentcourse_Log2的日志文件，初始值大小為8MB，最大值為100MB，文件以10%增長。然后對數據庫中Studentcourse2文件重命名為Studentcourse_2，最后把該文件從數據庫中移除。方法一：使用SQLServerManagementStudio方法二：使用SQL語言修改數據庫。命令如下所示：alterDATABASEstudentcourseaddfilegroupgroup2--新增group2文件組GO3．修改數據庫4.1.3管理數據庫4.1.3管理數據庫alterDATABASEstudentcourseaddfile(NAME=‘studentcourse2’,--在文件組group2中新增studentcourse2次數據文件FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\studentcourse2.ndf',SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=1MB)tofilegroupgroup2GoalterDATABASEstudentcoursemodifyfile--修改studentcourse主數據文件的文件增長為2MB(NAME='studentcourse',FILEGROWTH=2MB)GoalterDATABASEstudentcourseaddLOGfile--新增studentcourse_log2日志文件(NAME=N'studentcourse_log2',FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\studentcourse_log2.ldf',SIZE=8MB,MAXSIZE=100MB,FILEGROWTH=10%)GoalterDATABASEstudentcoursemodifyfile(name='studentcourse2',newname='studentcourse_2')--對數據庫中studentcourse2次數據文件重命名成studentcourse_2GoalterDATABASEstudentcourseremovefilestudentcourse_2--從數據庫中移除studentcourse_2次要數據文件4.刪除數據庫1) 命令格式

DROPDATABASE<數據庫名>[,…,N]

2) 功能刪除指定數據庫。【例4.5】

刪除學生選課數據庫studentcourse。方法一：使用SQLServerManagementStudio刪除數據庫。方法二：使用SQL命令刪除數據庫。DROPDATABASEstudentcourse1）命令格式DBCCSHRINKDATABASE(數據庫名[,Target_Percent])[{Notruncate|Truncateonly}]2）功能壓縮指定數據庫。【例4.6】壓縮學生選課studentcourse數據庫，使其最大可用空間為30%。方法一：使用SQLServerManagementStudio壓縮數據庫方法二：使用SQL命令壓縮數據庫DBCCSHRINKDATABASE(Studentcourse,30)4.1.3管理數據庫5．壓縮數據庫4.1.3管理數據庫【例4.7】壓縮學生選課數據庫studentcourse中的一個secondsc次數據文件，將其壓縮為2MB。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。DBCCSHRINKFILE(secondsc,2)5．壓縮數據庫4.2.1數據系統視圖1．Sysobjects系統視圖2．Syscolumns系統視圖3．Sysindexes系統視圖4．Sysusers系統視圖5．Sysdatabases系統視圖6．Sysdepends系統視圖7．Sysconstraints系統視圖4.2數據表的創建SQLServer2019將系統數據存儲在隱藏“資源”表中。只有通過具有權限的管理員連接，方可調用和查看。低級用戶必須使用系統視圖，從隱藏表和隱藏函數中獲得系統信息。4.2.1數據系統視圖4.2數據表的創建【例4.8】

使用系統視圖sysdatabases顯示所有已經安裝的數據庫名稱。操作步驟如下。在SQLServerManagementStudio主窗口中，單擊“新建查詢”按鈕，在新建查詢命令窗口輸入如下命令。

useMASTERSELECTname,filenameFROMsysdatabases4.2.1數據系統視圖4.2數據表的創建【例4.9】

使用系統視圖sysobjects顯示studentcourse數據庫中由用戶定義的對象。操作步驟如下。在SQLServerManagementStudio主窗口中，單擊“新建查詢”按鈕，在新建查詢命令窗口輸入如下命令。

usestudentcourseSELECTname,typeFROMsysobjectsWHEREtype='u'１．數據類型及其確定原則２．SQLServer的九大類數據類型1）整型數據類型(Integer)2）精確數字數據類型(ExactNumeric)3）近似數字數據類型(ApproximateNumeric)4）貨幣數據類型(Monetary)5）日期和時間數據類型(DateTime)6）字符數據類型(Character)7）二進制數據類型8）專用數據類型9）自定義數據類型(UDT)EXEC4.2.2數據類型4.2.2數據類型數據類型存儲字節數取值范圍使用說明Bigint8-263～263-1即-9,223,372,036,854,775,808～9,223,372,036,854,775,807存儲非常大的正負整數Int4-231～231-1即-2,147,483,648～2,147,483,647存儲正負整數Smallint2-215～215-1即-32,768～32,767存儲正負整數Tinyint10～27-1即0～255存儲小范圍的正整數表4.2整型數據類型4.2.2數據類型數據類型存儲字節數取值范圍使用說明Decimal(P,S)依據不同的精度，需要5～17字節-1038+1～1038-1P的默認值為18，最大可以存儲38位十進制數；S的默認值是0，只能取0～P之間的值。例如，3.14的數據類型指定為Decimal(3,2)Numeric(P,S)依據不同的精度，需要5～17字節-1038+1～1038-1功能上等價于Decimal，并可以與Decimal交換使用表4.3精確數字數據類型4.2.2數據類型數據類型存儲字節數取值范圍使用說明Float或

Float(N)4或8-1.79E+308～-2.23E-308、0、2.23E-308～1.79E+308存儲大型浮點數，超過十進制數據類型的容量，默認精確到第15位數(當N取1～24時相當于REAL類型，4個字節存儲；取24～53則8個字節)Real4-3.4E+38～-1.18E-38、0、1.18E-38～3.4E+38仍然有效，但為了滿足SQL-92標準，已經被Float替換了，精確到第7位數表4.4近似數字數據類型4.2.2數據類型表4.5貨幣數據類型(Monetary)數據類型存儲字節數取值范圍使用說明Money8字節-263～263-1即-922

337

203

685

477.5808～922

337

203

685

477.5807存儲大型貨幣值，精確到小數點后4位，精確至萬分之一Smallmoney4字節-231～231-1即-214

748.3648～214

748.3647存儲小型貨幣值，精確到小數點后4位4.2.2數據類型表4.6日期和時間數據類型數據類型存儲

字節數取值范圍使用說明Datetime8從1753年1月1日到9999年12月31日，精確度為3.33毫秒存儲大型日期和時間值Smalldatetime4從1900年1月1日到2079年6月6日，精確度為1分鐘存儲較小范圍的日期和時間值4.2.2數據類型表4.7字符數據類型數據類型存儲字節數取值范圍使用說明Char(N)N字節1～8000固定寬度的ANSI數據類型Varchar(N)輸入的字符個數為實際長度(0～N字節)1～8000可變寬度的ANSI數據類型Varchar(Max)最大長度為2GB，輸入的字符個數為實際長度(0～N字節)1～231-1，即1～2G，即1～214

748

3647可變寬度的ANSI數據類型Text最大長度為2GB個字節1～231-1，即1～2G，即1～214

748

3647固定寬度的ANSI數據類型，已由VARCHAR(MAX)取代4.2.2數據類型表4.8二進制數據類型數據類型存儲字節數取值范圍使用說明Binary(N)占N字節空間，N可以取從1到8000的值。-263～263-1存儲固定大小的二進制數據，在輸入數據時必須在數據前加上字符“0X”作為二進制標識Varbinary(N)占實際長度+2字節,N可以取從1到8000的值。-263～263-1存儲可變大小的二進制數據，若不指定N的值，則默認為1Image最大長度231-10～231-10～2GB存儲可變大小的二進制數據，在輸入數據時必須在數據前加上字符“0X”作為二進制標識4.2.2數據類型表4.9特殊數據類型數據類型存儲字節數取值范圍使用說明Bit10，1，Null存儲0、1或Null。用于基本“標記”值。TRUE被轉換為1，而FALSE被轉換為0，輸入0和1之外的任何值，系統都會作為1來處理Timestamp8二進制的字符串可自動生成二進制數字的數據類型，并在插入或修改行時被設置到數據庫時間戳，每當行中的某個日期發生變化時，該行上的TIMESTAMP型列中的值就自動更新。適合用來檢測在一個用戶處理數據期間另一個用戶是否已修改了該數據Sql_Variant0~8016

通配符數據類型，它會自動地將自己“轉換為”寫到它里面的數據的類型。最多存儲8000個字節，能保存除VARCHAR(MAX)，NVARCHAR(MAX)，TEXT，NTEXT，IMAGE，TIMESTAMP和SQL_VARIANT以外的任何其他合法的SQL數據類型4.2.2數據類型自定義數據類型T-SQL的自定義數據類型是基于SQLServer2019中的系統數據類型，可用作一種別名機制。(1) 命令格式

EXECSP_ADDTYPE{新數據類型名稱},[,系統原有數據類型名稱][,NULL|NOTNULL]

(2) 功能

是指定系統原有的數據類型為新數據類型。如果指定“NULL”選項，則表示新定義的數據類型允許輸入空值。如果未明確定義為空性，系統將基于數據庫或連接的ANSINULL默認設置進行指定。【例4.10】建一個以Datetime為基礎的出生日期（Birthday）可為空的數據類型。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。EXECSP_ADDTYPEBirthday，Datetime，Null4.2.2數據類型4.2.3創建數據表結構1．熟悉CREATETABLE語句的格式1）CREATETABLE語句的簡化格式CREATETABLE[{服務器名.[數據庫名].[架構名].|數據庫名.[架構名].|架構名.}]數據庫表名(列名，數據類型[NOTNULL][identity(初值,步長)][DEFAULT默認值][UNIQUE][PRIMARYKEY][CLUSTERED|NONCLUSTERED][，列名，數據類型[NOTNULL][DEFAULT默認值][UNIQUE][，…n]][，列名AS計算列值的表達式[，…n]][，[CONSTRAINT主鍵約束名]PRIMARYKEY(屬性名)][，[CONSTRAINT檢查約束名]CHECK(邏輯表達式)[，n]][，[FOREIGNKEY(外鍵屬性)REFERENCES參照表(參照屬性)[，…n]])[ON{文件組|默認文件組}]2）CREATETABLE語句的說明【例4.11】創建數據庫“book_shop”的數據表book，數據表由書號、書名、出版社、出版日期、單價、數量、總價（單價*數量）、電子郵件地址和數據庫表使用者字段組成。其中書號列定義為主鍵并且為系統自動編號即標識列，種子值（起始值）為1000，增量為1，要求出版社字段的值只能是高教、浙大、電子和中央四個之一，電子郵件地址字段中必須包含@符號，單價必須大于0，數量必須大于等于0，出版日期的默認值設置為當前日期函數。（1）標識IDENTITY屬性（2）計算所得的列（3）空值NULL約束（4）PRIMARYKEY約束（5）UNIQUE約束（6）DEFAULT約束（7）CHECK約束（8）FOREIGNKEY約束2）CREATETABLE語句的說明（1）標識IDENTITY屬性

定義列時，還可以為一個表中的其中一列指定一個特殊的自動增長標識屬性。每一個表只能有一個“標識”屬性，標識屬性有兩個參數：標識初始值和增量。例如：設置book表中屬性“書號”為標識列。

CREATETABLEbook(……

書號intidentity(1000,1)NOTFORREPLICATION,

……)2）CREATETABLE語句的說明（2）計算所得的列

我們還可以創建一種稱為計算所得的列的特殊列，它包含一個涉及表中一個或多個其他列的計算公式。

例如：設置book表中屬性“總價”為計算所得的列。

CREATETABLEbook(……

總價as單價*數量,

數據庫表使用者ASUSER_NAME(),

……)2）CREATETABLE語句的說明（3）空值NULL約束

空值NULL約束決定屬性值是否允許為空值(NULL)。NULL表示沒有輸入任何內容，它不是零或空白。timestamp數據類型只能定義為NOTNULL。例如：設置book表中屬性“書名”不允許為空值。

CREATETABLEbook(……

書名char(20)NOTNULL,

……)2）CREATETABLE語句的說明（4）PRIMARYKEY約束設置為PRIMARY約束的屬性集就是表的主鍵。由PRIMARYKEY約束生成的索引不能使表中的非聚集索引超過249個，聚集索引超過1個。例如：建立book表中屬性“書號”為主鍵的聚集索引。

CREATETABLEbook(……

書號intCONSTRAINTPK_snoPRIMARYKEYCLUSTERED,

……)2）CREATETABLE語句的說明（4）PRIMARYKEY約束或者

CREATETABLEbook(……

書號int,CONSTRAINTPK_snoPRIMARYKEYCLUSTERED(書號)

……)2）CREATETABLE語句的說明（5）UNIQUE約束UNIQUE約束相應屬性列的取值必須唯一，允許存在空值。如果UNIQUE約束中沒有指定CLUSTERED或NONCLUSTERED，則默認為NONCLUSTERED。每個UNIQUE約束都生成一個索引。例如：對book表中的屬性“書名”建立唯一性約束。CREATETABLEbook(……

書名char(20)UNIQUE,

……)2）CREATETABLE語句的說明（5）UNIQUE約束也可以在定義UNIQUE約束時，提供約束名稱。

CREATETABLEbook(……

書名char(20),CONSTRAINTU_snUNIQUE(書名)

……)2）CREATETABLE語句的說明（6）DEFAULT約束

每個屬性只能有一個默認值。可以包含常量值、函數或NULL，但不能引用表中的其他列。數據類型為timestamp或具有IDENTITY屬性的列上不能定義默認值。使用INSERT和UPDATE語句時，自動提供默認值。例如：設置

“出版日期”的默認值為當前日期。

CREATETABLEbook(……

出版日期datetimeDEFAULT(getdate())

……)2）CREATETABLE語句的說明（7）CHECK約束

每個屬性列可以有多個CHECK約束，約束條件是邏輯表達式，不能引用其他表。當執行INSERT和DELETE語句時，將檢查數據是否滿足約束。例如：約束屬性“出版社”只能取值為高教、浙大、電子、中央四個之一，電子郵件地址字段中必須包含@符號，單價必須大于0，數量必須大于等于0。

CREATETABLEbook(……

出版社char(20),

單價smallintcheck(單價>0),

數量smallintcheck(數量>=0),

電子郵件地址varchar(25)check(電子郵件地址like'%@%'),check(出版社in('高教','浙大','電子','中央')

……)2）CREATETABLE語句的說明（8）FOREIGNKEY約束FOREIGNKEY(外鍵)約束可以確保一個特定的列(外鍵)中可以輸入的值存在于一個指定的表的指定列(被參照列)中。例如：學生選課數據表SC(如表3.5所示)中的屬性“課程號”參照課程數據表C(如表3.4所示)中的屬性“課程號”的值，則SC為外鍵表，“SC.課程號”為外鍵，C表為主鍵表，“C.課程號”是主鍵。命令如下：

CREATETABLESC(

課程號char(3),FOREIGNKEY(課程號)REFERENCESC(課程號))方法一：使用SQLServerManagementStudio方法二：使用命令。CREATEdatabasebookshopgoCREATETABLEbook(書號intidentity(1000,1)NOTFORREPLICATIONPRIMARYKEYCLUSTERED,

書名char(20)notnull,

出版社char(20),

出版日期datetimeDEFAULT(getdate()),

單價smallintcheck(單價>0),

數量smallintcheck(數量>=0),

總價as單價*數量,

電子郵件地址varchar(25),

數據庫表使用者ASUSER_NAME(),check(電子郵件地址like'%@%'),check(出版社in('高教','浙大','電子','中央')))2）CREATETABLE語句的說明【例4.12】創建數據庫學生選課“studentcourse”的數據表S、C、SC，數據表結構如表3.6，表3.7，表3.8所示，各表的完整性約束如表3.9，表3.10，表3.11所示。方法一：使用SQLServerManagementStudio創建數據表方法二：使用SQL命令。1）創建課程表C的語句如下：CREATETABLEC(

課程號Char(3)NOTNULL,

課程名Varchar(20)NOTNULL,

學分SmallintNULL,

預選課程號Char(3)NULL,

教師Char(8)NULL, CONSTRAINTFK_PcnoFOREIGNKEY(預選課程號)REFERENCESC(課程號), CONSTRAINTCK_CnoCHECK(課程號Like'[A-C][0-9][0-9]'), CONSTRAINTPK_CPRIMARYKEYCLUSTERED(課程號ASC) )ON[PRIMARY]2）CREATETABLE語句的說明【例4.12】創建數據庫學生選課“studentcourse”的數據表S、C、SC，數據表結構如表3.6，表3.7，表3.8所示方法二：使用SQL命令。2）創建學生基本信息表S的語句如下：CREATETABLES(

學號Char(6)NOTNULLCONSTRAINTDF_S_學號DEFAULT('J04000'),

姓名Char(8)NOTNULL,

性別Char(2)NOTNULL,

出生日期DatetimeNOTNULLCONSTRAINTDF_S_出生日期DEFAULT('19800101'),

系Varchar(20)NOTNULL,

電話Char(8)NULL,CONSTRAINTCK_SCHECK(學號Like'[A-Z][0-9][0-9][0-9][0-9][0-9]'),CONSTRAINTCK_SexCHECK(性別='女'OR性別='男'),CONSTRAINTCK_TELCHECK(電話Like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),CONSTRAINTPK_SPRIMARYKEYCLUSTERED(學號ASC) )ON[PRIMARY]

2）CREATETABLE語句的說明【例4.12】創建數據庫學生選課“studentcourse”的數據表S、C、SC，數據表結構如表3.6，表3.7，表3.8所示，各表的完整性約束如表3.9，表3.10，表3.11所示。方法二：使用SQL命令。3）創建學生選課數據表SC的語句如下：CREATETABLESC(

學號Char(6)NOTNULL,

課程號Char(3)NOTNULL,

成績SmallintNULL, CONSTRAINTFK_C_CNOFOREIGNKEY(課程號)REFERENCESC(課程號), CONSTRAINTFK_S_SNOFOREIGNKEY(學號)REFERENCESS(學號), CONSTRAINTCK_GradeCHECK(成績>=0AND成績<=100OR成績ISNULL), CONSTRAINTPK_SCPRIMARYKEYCLUSTERED(學號ASC, 課程號ASC) )ON[PRIMARY]2）CREATETABLE語句的說明【例4.13】返回有關所有對象的信息。USEMaster;GOEXECSp_Help;GO【例4.14】返回學生選課“studentcourse”中學生表的信息。USEStudentcourseGOEXECSp_Helps4.2.4查看數據表1．修改表的結構1）命令格式ALTERTABLE<數據表名>

{ALTERCOLUMN<屬性列名>類型(寬度)[NULL|NOTNULL]

|ADD<屬性列名>類型(寬度)[NULL|NOTNULL][完整性約束][,…n]

|DROPCOLUMN[<屬性列名>][,…n]

|ADD[CONSTRAINT<約束名>]CHECK(邏輯表達式)[,…n]}

|DROP[CONSTRAINT<約束名>|ALL][,…n]}2）功能ALTERTABLE：將要修改的當前數據庫中的指定數據表的表名。ALTERCOLUMN：修改當前數據庫中的指定數據表的指定屬性。ADD：向當前數據庫中的指定數據表增加指定屬性或列級完整性約束。DROPCOLUMN：刪除當前數據庫中的指定數據表中的指定屬性。DROP：刪除當前數據庫中的指定數據表中的指定列級完整性約束。4.2.5修改數據表【例4.15】修改當前數據庫“studentcourse”中S表的系屬性改成char(25)，增加一個入學時間字段，它的數據類型為datetime，默認值為getdate()，最后刪除入學時間字段。方法一：使用SQLServerManagementStudio方法二：使用命令。ALTERTABLEsALTERCOLUMN

系char(25)GoALTERTABLEsADD入學時間datetimeGoALTERTABLEsADDCONSTRAINTDF_sjDEFAULT(getdate())for入學時間4.2.5修改數據表【例4.15】修改當前數據庫“studentcourse”中S表的系屬性改成char(25)，增加一個入學時間字段，它的數據類型為datetime，默認值為getdate()，最后刪除入學時間字段。也可以將增加入學時間字段與默認值進行一次性設置，操作如下：

ALTERTABLESADD入學時間datetimeCONSTRAINTDF_sjDEFAULT(getdate());GoALTERTABLESDROPDF_sj

--從當前數據庫的S表中刪除DF_SJ默認值約束goALTERTABLESDROPCOLUMN入學時間

--從當前數據庫的S表中刪除入學時間字段4.2.5修改數據表2．修改表的名稱1）命令格式

Sp_rename<原數據表名>,<新數據表名>2）功能重命名當前數據庫中的指定數據表名。【例4.16】重命名數據庫bookshop的數據表book名稱，改為“書籍資料”。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。Sp_rename'book','書籍資料'

4.2.5修改數據表1）命令格式DROPTABLE表名2）功能刪除表【例4.17】刪除當前數據庫中的表S。

方法一：使用SQLServerManagementStudio方法二：使用SQL命令。DROPTABLES4.2.6刪除數據表【例4.18】刪除其他數據庫中的表。例如，刪除studentcourse數據庫中的S表。DROPTABLEStudentcourse.Dbo.s4.2.7外部表

SQLServer的外部表(ExternaltableforSQLServer)是SQLServer2016版本新增的內容，以前版本訪問外部數據源使用的是鏈接服務器，SQLServer2019可以配置PolyBase訪問Hadoop、AzureBlobStorage、SQLServer、Oracle、Teradata、MongoDB和基于ODBC的源等。PolyBase允許你將來自SQLServer實例的數據與外部數據連接起來。當然需要安裝PolyBase查詢服務。SQLServer2019Preview中的PolyBase允許訪問各種數據源，AzureDataStudioSQLServer2019Preview擴展當前支持SQLServer和Oracle數據僅從“外部表”向導獲取源。SQLServer的外部表(ExternaltableforSQLServer)是SQLServer2016版本新增的內容，以前版本訪問外部數據源使用的是鏈接服務器，SQLServer2019可以配置PolyBase訪問Hadoop、AzureBlobStorage、SQLServer、Oracle、Teradata、MongoDB和基于ODBC的源等。PolyBase允許你將來自SQLServer實例的數據與外部數據連接起來。當然需要安裝PolyBase查詢服務。SQLServer2019Preview中的PolyBase允許訪問各種數據源，AzureDataStudioSQLServer2019Preview擴展當前支持SQLServer和Oracle數據僅從“外部表”向導獲取源。4.2.7外部表

4.2.7外部表

1．命令格式CREATEEXTERNALTABLE{數據庫名.架構名.表名|架構名.表名|表名}(<列名<數據類型>[COLLATEcollation_name][NULL|NOTNULL]>[,...n])WITH(LOCATION='實際數據指定文件夾或文件路徑和文件名',DATA_SOURCE=外部數據源名,FILE_FORMAT=external_file_format_name[,<|REJECT_TYPE=value|percentage|REJECT_VALUE=reject_value|REJECT_SAMPLE_VALUE=reject_sample_value,|REJECTED_ROW_LOCATION='/目錄位置'>[,...n]])[;]4.2.7外部表

（1）外部表，是存儲在數據庫外面的表。用來存取數據庫以外的文本文件（TextFile）或SQLSERVER專屬格式文件。只有與表相關的定義放在數據字典中。當存取時才能從SQLServer專屬格式文件中取得數據，外部表僅供查詢，不能對外部表的內容進行修改（INSERT、UPDATE、DELETE操作）。不能對外部表建立索引。位于文件系統之中（一定要在數據庫服務器中，而不是其它網絡路徑），按一定格式分割，例如@#$等，文本文件或者其他類型的文件可以作為外部表。（遠程文件不行）

僅存儲表元數據以及有關Hadoop或Azureblob存儲中引用的文件或文件夾的基本統計信息。2．功能4.2.7外部表

（2）對外部表的訪問可以通過SQL語句來完成，而不需要先將外部表中的數據裝載進數據庫中。數據在數據庫的外部組織，是操作系統文件。操作系統文件在數據庫中的標志是通過一個邏輯目錄來映射的。（3）可以查詢操作和連接。也可以并行操作。（4）外部表字段只能進行類型、長度、非空之類的一般設置，而不能進行主鍵、默認值、check約束、IDENTITY標識規范、計算列規范等設置。列定義（包括數據類型和列數）必須與外部文件中的數據匹配。如果存在不匹配，則在查詢實際數據時會拒絕文件行。4.2.7外部表

（5）FILE_FORMAT=external_file_format_name指定為外部數據存儲文件類型和壓縮方法的外部文件格式對象的名稱。（7）REJECT_TYPE=value|percentage說明REJECT_VALUE選項是指定為文本值還是百分比。REJECT_VALUE=reject_value指定在查詢失敗之前可以拒絕的行數的值或百分比。值REJECT_VALUE是文本值。當拒絕的行數超過reject_value時，PolyBase查詢會失敗。例如，如果REJECT_VALUE=5并且REJECT_TYPE=value，則PolyBaseSELECT查詢會在拒絕了5行之后失敗。值REJECT_VALUE是百分比。當失敗行的百分比超過reject_value時，PolyBase查詢會失敗。每隔一段時間計算失敗行的百分比。4.2.7外部表

（8）REJECT_SAMPLE_VALUE=reject_sample_value當指定REJECT_TYPE=percentage時，此屬性是必需的。它確定在PolyBase重新計算拒絕的行的百分比之前要嘗試檢索的行數。Reject_sample_value參數必須是介于0與2,147,483,647之間的整數。例如，如果REJECT_SAMPLE_VALUE=1000，則PolyBase會在嘗試從外部數據文件導入1000行后計算失敗行的百分比。如果失敗行的百分比小于reject_value，則PolyBase會嘗試檢索另外1000行。它在嘗試導入每個另外1000行后會繼續重新計算失敗行的百分比。4.2.7外部表

【例4.19】在當前數據庫服務器配置PolyBase，在studentcourse數據庫中創建sqlserver.book外部表來訪問同一個數據庫服務器中的bookshop數據庫中的book表。如圖4.33所示。操作步驟如下：步驟一：配置選項'polybaseenabled'為1。運行RECONFIGURE語句進行安裝USEmasterGO

EXECsp_configure'polybaseenabled',1

reconfigurewithoverrideGo4.2.7外部表

步驟二：數據庫主密鑰是指用于保護證書私鑰的對稱密鑰以及數據庫中存在的非對稱密鑰。創建studentcourse數據庫主密鑰MASTERKEYUSEstudentcourseGODROPMASTERKEYCREATEMASTERKEYENCRYPTIONBYPASSWORD='888888';GO4.2.7外部表

步驟三：

創建數據庫憑據。數據庫憑據不會映射到服務器登錄或數據庫用戶。只要數據庫在執行需要訪問權限的操作，數據庫就可使用憑據訪問外部位置--DROPDATABASESCOPEDCREDENTIALSqlServerCredentialsCREATEDATABASESCOPEDCREDENTIALSqlServerCredentialsWITHIDENTITY='sa',Secret='123456';--登錄名密碼GO4.2.7外部表

步驟四：創建外部數據源SQLServerInstance--DROPEXTERNALDATASOURCESQLServerInstanceCREATEEXTERNALDATASOURCESQLServerInstanceWITH(LOCATION='sqlserver://127.0.0.1:1433',PUSHDOWN=ON,CREDENTIAL=sqlServerCredentials,CONNECTION_OPTIONS='ssl=false;');GO4.2.7外部表

步驟五：創建獨立的架構sqlServer--dropSCHEMAsqlserverCREATESCHEMAsqlserver;GO4.2.7外部表

步驟六：創建外部表sqlserver.book，引用的是上面定義的外部數據源（外部表與外部數據源類型要求一致）

--DROPEXTERNALTABLEsqlserver.bookCREATEEXTERNALTABLEsqlserver.book(

書號intNOTnull,

書名char(20)notnull,

出版社char(20),

出版日期datetime,

單價smallint,

數量smallint,

電子郵件地址varchar(25))WITH(LOCATION='bookshop.dbo.book',DATA_SOURCE=SQLServerInstance–在步驟四創建的外部數據源);GO4.2.7外部表

步驟七：創建統計信息CREATESTATISTICSOrdersOrderKeyStatisticsONsqlserver.book([書號])WITHFULLSCAN4.2.8圖形表

圖形數據庫中圖形指的拓撲圖形，是一些節點（Node）表和邊緣(Edge)表的合集，（Node）對應關系數據庫中的實體，邊緣表指示節點之間的關系。1．創建節點表與邊緣表命令格式

CREATETABLE{數據庫名.架構名.表名|架構名.表名|表名}(

列名,數據類型[NOTNULL][identity(初值,步長)][DEFAULT默認值][UNIQUE][PRIMARYKEY][CLUSTERED|NONCLUSTERED][,

列名,數據類型[NOTNULL][DEFAULT默認值][UNIQUE][,…n]][,

列名AS計算列值的表達式[,…n]][,[CONSTRAINT主鍵約束名]PRIMARYKEY(屬性名)][,[CONSTRAINT檢查約束名]CHECK(邏輯表達式)[,…n]][,[FOREIGNKEY(外鍵屬性)REFERENCES參照表(參照屬性)[,…n]])AS[NODE|EDGE][ON{文件組|默認文件組}][;]4.2.8圖形表2．功能

創建圖形表的語句與普通表大體相同，只是在語句最后加上ASNODE或ASEDGE，表示創建的是Node表（節點表）還是Edge表（邊緣表）。圖形表比較適合用來表示這種實體與實體之間有明顯關聯關系的情況，比如學生和課程，學生和課程是Node表，選課記錄是Edge表。

在SSMS中，可以看到數據庫的“表”節點下面有“圖形表”這個節點。4.2.8圖形表【例4.20】

在studentcourse數據庫中創建s2(NODE)、c2(NODE)、sc2(EDGE)三張圖形表。關系圖如圖3.5所示。 CREATETABLES2(

學號Char(5)NOTNULLDEFAULT('J0400')CHECK(學號Like'[A-Z][0-9][0-9][0-9][0-9]')PRIMARYKEYCLUSTERED,

姓名Char(8)NOTNULL,

性別Char(2)NOTNULLCHECK(性別='女'OR性別='男'),

出生日期DatetimeNOTNULLDEFAULT('19800101'),

系Varchar(20)NOTNULL,

電話Char(8)NULLCHECK(電話Like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') )ASNODE;

4.2.8圖形表CREATETABLEC2(

課程號Char(3)NOTNULLCHECK(課程號Like'[A-C][0-9][0-9]')PRIMARYKEYCLUSTERED,

課程名Varchar(20)NOTNULL,

學分SmallintNULL,

預選課程號Char(3)NULLFOREIGNKEY(預選課程號)REFERENCESC(課程號),

教師Char(8)NULL )ASNODE;

CREATETABLESC2(

成績SmallintNULLCHECK(成績>=0AND成績<=100OR成績ISNULL) )ASEDGE;

4.3.1使用SQLServerManagementStudio插入、修改與刪除數據4.3數據庫表的操作4.3.2使用T-SQL語句進行插入、修改和刪除數據1．插入數據2．更新數據3．刪除記錄插入數據記錄的方法有三種，第一種是利用SQLServerManagementStudio，第二種是使用SELECT查詢語句（本節暫不介紹），第三種是使用INSERT命令。1）命令格式INSERTINTO數據表名(列名表)VALUES(元組值)INSERTINTO數據表名(列名表)SELECT查詢語句INSERTINTO數據表名(列名表)DEFAULTVALUES2）功能向指定數據表的屬性列插入數據，VALUES后跟的元組值為屬性列提供數據。其中列名表中的屬性排列順序和VALUES后跟的元組值的排列順序要一致。對應的數據類型要一致。如果沒有指定列名表，則表示數據表中的所有屬性列。“DEFAULTVALUES”選項會將默認值插入到該屬性列中，如果某列沒有默認值，允許則向該列插入空值NULL，如果某列不允許空值也沒有默認值，則會出錯。1．插入數據【例4.21】以下示例使用屬性列顯式指定插入到每個列的值。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。USEStudentcourseGOINSERTINTOs(學號,姓名,性別,出生日期,系)VALUES(‘L04001','張云龍','男',‘2002-11-11','路橋系')GoSELECT*FROMs1．插入數據【例4.22】將查詢結果插入數據表，如將學號‘L04001’、成績80以及課程表中所有課程號插入到sc中。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。USEStudentcourseGOINSERTINTOscSELECT‘L04001',課程號,80FROMcGoSELECT*FROMsc1．插入數據1．插入數據【例4.23】向s2和c2節點表插入數據，如圖4.35-4.36所示。--插入s2節點表記錄值的語句如下：insertintos2values('J04001','李麗','女','2002-2-12','管理信息系','931-1234')insertintos2values('J04002','馬俊萍','女','2000-12-2','管理信息系','931-1288')insertintos2values('J04003','王永明','男','2001-12-1','管理信息系','571-2233')insertintos2values('J04004','姚江','男','2002-8-9','管理信息系','571-8848')insertintos2values('Q04001','陳小紅','女','2002-2-12','汽車系','571-1122')insertintos2values('Q04003','張干勁','男','2002-1-15','汽車系','571-1111')1．插入數據圖4.35對圖形表s2插入數據結果窗口1．插入數據--插入c2節點表記錄值的語句如下：insertintoc2values('C01','數據庫',3,'C04','陳弄清')insertintoc2values('C02','C語言',4,'C04','應刻苦')insertintoc2values('C03','數據結構',3,'C02','管功臣')insertintoc2values('C04','計算機應用基礎',2,null,'李學成')insertintoc2values('C05','網絡技術',null,'C04','馬努力')1．插入數據圖4.36對圖形表c2插入數據結果窗口1．插入數據【例4.24】將表3.5中的學生選課信息插入sc2邊緣數據，需要節點提供$node_id和$from_id兩個字段（屬于隱藏字段），如圖4.37所示。--插入sc2邊緣表記錄值的語句如下：INSERTINTOsc2VALUES((SELECT$node_idFROMs2WHERE[學號]='J0401'),(SELECT$node_idFROMc2WHERE[課程號]='C01'),88);INSERTINTOsc2VALUES((SELECT$node_idFROMs2WHERE[學號]='J0401'),(SELECT$node_idFROMc2WHERE[課程號]='C02'),93);INSERTINTOsc2VALUES((SELECT$node_idFROMs2WHERE[學號]='J0401'),(SELECT$node_idFROMc2WHERE[課程號]='C03'),99);INSERTINTOsc2VALUES((SELECT$node_idFROMs2WHERE[學號]='J0401'),(SELECT$node_idFROMc2WHERE[課程號]='C04'),89);INSERTINTOsc2VALUES((SELECT$node_idFROMs2WHERE[學號]='J0401'),(SELECT$node_idFROMc2WHERE[課程號]='C05'),86);INSERTINTOsc2VALUES((SELECT$node_idFROMs2WHERE[學號]='J0402'),(SELECT$node_idFROMc2WHERE[課程號]='C01'),90);1．插入數據圖4.37對圖形表sc2插入數據結果窗口1）命令格式UPDATE基本表名

SET列名=值表達式[，列名=值表達式…][WHERE條件表達式]2）功能更新指定基本表，滿足WHERE子句條件的記錄的指定屬性值。其中值表達式可以是常量、變量、表達式。若缺省WHERE，則修改表中的所有元組。但在進行修改操作時，需注意數據庫的一致性。【例4.25】更新s表中的所有行出生日期列中的值變為原出生日期值加1。方法一：使用SQLServerManagementStudio方法二：使用SQL命令

USEStudentcourseGOUPDATEsSET出生日期=出生日期+1