1、Ch2 建立數(shù)據(jù)庫(kù),1,本章內(nèi)容,1. 建立數(shù)據(jù)庫(kù),2,SQL server2008客戶端工具,SQL Server Management Studio 是為 SQL Server 數(shù)據(jù)庫(kù)管理員和開發(fā)人員提供的新工具。它提供了用于數(shù)據(jù)庫(kù)管理的圖形工具和功能豐富的開發(fā)環(huán)境。 完成數(shù)據(jù)庫(kù)管理與維護(hù)的大部分工作。如配置系統(tǒng)環(huán)境和管理SQL Server服務(wù)器，顯示、建立、管理所有的SQL Server對(duì)象，備份和恢復(fù)數(shù)據(jù)庫(kù)，復(fù)制數(shù)據(jù)庫(kù)，管理用戶帳戶等。 輸入和執(zhí)行Transact-SQL語句，并且迅速查看這些語句的結(jié)果，以分析和處理數(shù)據(jù)庫(kù)中的數(shù)據(jù)。,3,SQL server2005系統(tǒng)數(shù)據(jù)庫(kù)簡(jiǎn)介,1

2、). master(主要的) 是系統(tǒng)最重要的數(shù)據(jù)庫(kù)，它記錄了SQL Server系統(tǒng)的所有系統(tǒng)信息。這些系統(tǒng)信息包括所有的登錄信息、系統(tǒng)設(shè)置信息、SQL Server的初始化信息和其它系統(tǒng)及用戶數(shù)據(jù)庫(kù)的相關(guān)信息。 建議不要在 master 數(shù)據(jù)庫(kù)中創(chuàng)建任何用戶對(duì)象。如表、視圖、存儲(chǔ)過程或觸發(fā)器等。 2). model（模型的） 是所有用戶數(shù)據(jù)庫(kù)和tempdb（臨時(shí)）數(shù)據(jù)庫(kù)的模板數(shù)據(jù)庫(kù)，它含有master數(shù)據(jù)庫(kù)所有系統(tǒng)表的子集，這些系統(tǒng)表是每個(gè)用戶定義數(shù)據(jù)庫(kù)需要的。,4,3). msdb 是代理服務(wù)數(shù)據(jù)庫(kù)，用于為警報(bào)、任務(wù)調(diào)度和記錄操作員的操作提供存儲(chǔ)空間。 4). tempdb （臨時(shí)） 用于

3、為所有的臨時(shí)表、臨時(shí)存儲(chǔ)過程提供存儲(chǔ)空間，還用于任何其它的臨時(shí)存儲(chǔ)要求。 例如存儲(chǔ)SQL Server生成的工作表。tempdb數(shù)據(jù)庫(kù)是全局資源，所有連接到系統(tǒng)的用戶的臨時(shí)表和存儲(chǔ)過程都存儲(chǔ)在該數(shù)據(jù)庫(kù)中。在SQL Server 每次啟動(dòng)時(shí)都重新創(chuàng)建，因此該數(shù)據(jù)庫(kù)在系統(tǒng)啟動(dòng)時(shí)總是干凈的，臨時(shí)表和存儲(chǔ)過程在連接斷開時(shí)自動(dòng)除去。,5,自帶的示例數(shù)據(jù)庫(kù),5).AdventureWorks數(shù)據(jù)庫(kù)（示例） AdventureWorks數(shù)據(jù)庫(kù): 以一家大型跨國(guó)生產(chǎn)公司為模型，該公司生產(chǎn)金屬和復(fù)合材料的自行車，產(chǎn)品遠(yuǎn)銷北美、歐洲和亞洲市場(chǎng)。該數(shù)據(jù)庫(kù)及其中的表經(jīng)常在幫助文檔和教程所介紹的示例中使用。 默認(rèn)情況下

4、不安裝 AdventureWorks數(shù)據(jù)庫(kù) ，可以從 Microsoft 下載中心下載并安裝。,6,1. 建立數(shù)據(jù)庫(kù),SQL Server中每個(gè)數(shù)據(jù)庫(kù)由一組操作系統(tǒng)文件組成。 數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)、對(duì)象和數(shù)據(jù)庫(kù)操作日志都存儲(chǔ)在這些文件中。 數(shù)據(jù)庫(kù)文件可以分為三類： 主數(shù)據(jù)庫(kù)文件(.mdf) 次數(shù)據(jù)庫(kù)文件(.ndf) 事務(wù)日志文件(.ldf),7,1.1 數(shù)據(jù)庫(kù)文件,主數(shù)據(jù)庫(kù)文件(Primary Database file) 每個(gè)數(shù)據(jù)庫(kù)有且僅有一個(gè)主數(shù)據(jù)庫(kù)文件，主數(shù)據(jù)庫(kù)文件用來存儲(chǔ)數(shù)據(jù)庫(kù)的啟動(dòng)信息和部分或全部數(shù)據(jù)。一個(gè)數(shù)據(jù)庫(kù)可以有1到多個(gè)數(shù)據(jù)庫(kù)文件，其中只有一個(gè)文件為主數(shù)據(jù)庫(kù)文件。 主數(shù)據(jù)庫(kù)文件的

5、文件擴(kuò)展名為mdf，如：try1.mdf。 次數(shù)據(jù)庫(kù)文件(Secondary Database File) 用于存儲(chǔ)主數(shù)據(jù)庫(kù)文件中未存儲(chǔ)的剩余數(shù)據(jù)和數(shù)據(jù)庫(kù)對(duì)象。一個(gè)數(shù)據(jù)庫(kù)可以沒有次數(shù)據(jù)庫(kù)文件，也可以有多個(gè)次數(shù)據(jù)庫(kù)文件。 次數(shù)據(jù)庫(kù)文件的文件擴(kuò)展名為ndf 如：try1_2.ndf。 事務(wù)日志文件(Transcation Log File) 用于存儲(chǔ)數(shù)據(jù)庫(kù)的更新情況等事務(wù)日志信息。 當(dāng)數(shù)據(jù)庫(kù)損壞時(shí)，可以利用事務(wù)日志文件恢復(fù)數(shù)據(jù)庫(kù)。一個(gè)數(shù)據(jù)庫(kù)可以有1到多個(gè)事務(wù)日志文件。 事務(wù)日志文件的擴(kuò)展名為ldf 如：try1_log.ldf。,8,1.2 創(chuàng)建數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)庫(kù)的過程實(shí)際上是確定數(shù)據(jù)庫(kù)的名稱、設(shè)

6、計(jì)數(shù)據(jù)庫(kù)所占用的存儲(chǔ)空間和文件的存放位置。 創(chuàng)建數(shù)據(jù)庫(kù)的途徑: SQL Server Management Studio,兩種方法,使用管理工具,使用Transact-SQL語句,9,使用管理工具創(chuàng)建數(shù)據(jù)庫(kù),創(chuàng)建mydb1數(shù)據(jù)庫(kù)，文件存放在C盤db下,10,數(shù)據(jù)庫(kù)名稱 數(shù)據(jù)庫(kù)的所有者,默認(rèn)為登錄用戶,數(shù)據(jù)庫(kù)文件存儲(chǔ)位置,添加此數(shù)據(jù)庫(kù)文件,11,使用SQL命令CREATE DATABASE mydb2 ON PRIMARY -在主文件組創(chuàng)建, PRIMARY可以省略 ( NAME = mydb2, FILENAME = c:dbmydb2.mdf , SIZE = 3072KB , FILEGR

7、OWTH = 1024KB ) LOG ON -創(chuàng)建日志文件 ( NAME = mydb2_log, FILENAME = c:dbmydb2_log.ldf , SIZE = 1024KB , FILEGROWTH = 10%),創(chuàng)建mydb2數(shù)據(jù)庫(kù)，文件存放在C盤db下,12,練習(xí)：,1、創(chuàng)建一個(gè)mydb1數(shù)據(jù)庫(kù)，位置“D:mydb1”,初始容量8M，最大容量16M，文件增長(zhǎng)量為5%。 CREATE DATABASE mydb1 ON ( NAME = mydb1, FILENAME = d:mydb1.mdf , SIZE = 8MB , MAXSIZE=16MB, FILEGROWTH

8、 = 5% ) 2、增加一個(gè)“D:mydb_log1”日志文件，要求初始容量為2MB,最大容量50MB，文件增長(zhǎng)量10%。 CREATE DATABASE mydb_log1 ADD LOG FILE ( NAME = mydb_log1, FILENAME = D:mydb_log1, SIZE = 2MB , MAXSIZE=50MB, FILEGROWTH = 10% ),13,1.3 數(shù)據(jù)庫(kù)包含的數(shù)據(jù)對(duì)象,在一個(gè)SQL Server數(shù)據(jù)庫(kù)中，除了包含基本表以外，還包含了與這些基本表相關(guān)的多種對(duì)象，如：視圖、索引、存儲(chǔ)過程和觸發(fā)器等等。目的是為執(zhí)行與數(shù)據(jù)有關(guān)的活動(dòng)提供支持。,14,1.

9、表 在數(shù)據(jù)庫(kù)中，所有的數(shù)據(jù)存放在表中，表由行(記錄)和列(字段)組成，一個(gè)數(shù)據(jù)庫(kù)可以包含多個(gè)表。 2. 視圖 視圖是由查詢數(shù)據(jù)表產(chǎn)生的結(jié)果，是一種虛構(gòu)的表。視圖把表中的部分?jǐn)?shù)據(jù)映射出來供用戶使用，這樣可以防止所有的用戶直接對(duì)表進(jìn)行操作而導(dǎo)致系統(tǒng)的性能和安全性的下降。 3. 存儲(chǔ)過程 一個(gè)存儲(chǔ)過程實(shí)際上是由一組SQL語句組成的完成特定功能的程序。存儲(chǔ)過程在服務(wù)器端被編譯后可以反復(fù)執(zhí)行。 4索引 索引是對(duì)表中的一個(gè)或多個(gè)列的值進(jìn)行排序的結(jié)構(gòu)?？梢岳盟饕岣邔?duì)數(shù)據(jù)庫(kù)表中的特定信息的訪問速度。,15,5. 約束 約束是一種定義自動(dòng)強(qiáng)制數(shù)據(jù)庫(kù)完整性的方式。約束定義了關(guān)于列中允許值的規(guī)則。例如，強(qiáng)制定義

10、某成績(jī)列的值只能在0到100之間。 6. 默認(rèn)值 如果在插入行時(shí)沒有指定該行中某列的值，那么使用默認(rèn)值可以指定該列自動(dòng)使用的值。例如，定義某“性別”列的默認(rèn)值為“男”，則插入某學(xué)生信息時(shí)，如果沒有指定其性別，則自動(dòng)采用定義的默認(rèn)值“男”。 7. 觸發(fā)器 觸發(fā)器由一組SQL語句組成，當(dāng)對(duì)表或視圖進(jìn)行某種操作（添加、刪除或修改）時(shí)，這組命令會(huì)自動(dòng)執(zhí)行。,16,8. 用戶 用于定義允許訪問當(dāng)前數(shù)據(jù)庫(kù)的用戶及其權(quán)限。 9. 角色 角色定義了一組具有相同權(quán)限的用戶。,17,整數(shù)型 bit 1 或 0 的整數(shù)數(shù)據(jù)。 Tinyint（極小整數(shù)） 從 0 到 255 的整數(shù)數(shù)據(jù)。 Smallint（小整數(shù)）

11、從 -215 (-32,768) 到 215 - 1 (32,767) 的整數(shù)數(shù)據(jù)。 int 從 -231 (-2,147,483,648) 到 231 - 1 (2,147,483,647) 的整型數(shù)據(jù)（所有數(shù)字）。 bigint 從 -263 (-9223372036854775808) 到 263-1 (9223372036854775807) 的整型數(shù)據(jù)（所有數(shù)字）。,2、數(shù)據(jù)類型,18,浮點(diǎn)數(shù)型： decimal（小數(shù)） 從 -1038 +1 到 1038 1 的固定精度和小數(shù)位的數(shù)字?jǐn)?shù)據(jù)。例如：decimal(5,2) 總長(zhǎng)度5位（不含小數(shù)點(diǎn)），2位小數(shù)。 numeric（數(shù)值）

12、功能上等同于 decimal。 貨幣型： Money（貨幣） 貨幣數(shù)據(jù)值介于 -263 (-922,337,203,685,477.5808) 與 263 - 1 (+922,337,203,685,477.5807) 之間，精確到貨幣單位的千分之十，小數(shù)點(diǎn)后4位。 smallmoney 貨幣數(shù)據(jù)值介于 -214,748.3648 與 +214,748.3647 之間，精確到貨幣單位的千分之十。,19,近似數(shù)字 real 從 -3.40E + 38 到 3.40E + 38 的浮點(diǎn)精度數(shù)字 float 從 -1.79E + 308 到 1.79E + 308 的浮點(diǎn)精度數(shù)字 時(shí)間日期型 sma

13、lldatetime 從 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和時(shí)間數(shù)據(jù)，精確到分鐘 datetime 從 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和時(shí)間數(shù)據(jù)，精確到百分之三秒（或 3.33 毫秒）。,20,字符串 char 固定長(zhǎng)度的ASCII字符數(shù)據(jù)，最大長(zhǎng)度為 8,000 個(gè)字符。例如：char（5），占據(jù)5個(gè)字節(jié)。 varchar 可變長(zhǎng)度的ASCII 數(shù)據(jù)，最長(zhǎng)為 8,000 個(gè)字符。 例如： varchar（5），占據(jù)1-5個(gè)字節(jié)。 text 可變長(zhǎng)度的ASCII 數(shù)據(jù)，最大長(zhǎng)度為 231 - 1 (2,147,483,6

14、47) 個(gè)字符。 nchar 固定長(zhǎng)度的 二字節(jié)ASCII碼數(shù)據(jù)，最大長(zhǎng)度為 4,000 個(gè)字符。 nvarchar 可變長(zhǎng)度 Unicode 數(shù)據(jù)，其最大長(zhǎng)度為 4,000 字符。 ntext 可變長(zhǎng)度 Unicode 數(shù)據(jù)，其最大長(zhǎng)度為 230 - 1 (1,073,741,823) 個(gè)字符。,21,二進(jìn)制 binary 固定長(zhǎng)度的二進(jìn)制數(shù)據(jù)，其最大長(zhǎng)度為 8,000 個(gè)字節(jié)。 varbinary 可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)，其最大長(zhǎng)度為 8,000 個(gè)字節(jié)。 image 可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)，其最大長(zhǎng)度為 231 - 1 (2,147,483,647) 個(gè)字節(jié)。 主要用于圖片、視頻、聲音等。,

15、22,其它數(shù)據(jù)類型 uniqueidentifier 全局唯一標(biāo)識(shí)符 (GUID) 使用newid( )自動(dòng)生成。 cursor 游標(biāo)的引用。 sql_variant 一種存儲(chǔ) SQL Server 支持的各種數(shù)據(jù)類型（text、ntext、timestamp 和 sql_variant 除外）值的數(shù)據(jù)類型。 table 一種特殊的數(shù)據(jù)類型，存儲(chǔ)供以后處理的結(jié)果集。 timestamp 數(shù)據(jù)庫(kù)范圍的唯一數(shù)字，每次更新行時(shí)也進(jìn)行更新。,23,3. 建立數(shù)據(jù)表使用管理工具SSMS創(chuàng)建表,列的常用屬性 列名 列的數(shù)據(jù)類型 是否允許為空（NULL或NOT NULL） 主鍵（PRIMARY KEY） 默

16、認(rèn)值（DEFAULT）,24,設(shè)置自動(dòng)增加的標(biāo)識(shí)字段,25,使用CREATE TABLE語句在數(shù)據(jù)庫(kù)“mydb1”中創(chuàng)建“學(xué)生”表。 CREATE TABLE 學(xué)生 ( 學(xué)號(hào) char(10) PRIMARY KEY , 姓名 varchar(50) NOT NULL, 性別 bit DEFAULT(0), 出生日期 smalldatetime, 班級(jí) char(8) NOT NULL, 入學(xué)成績(jī) float DEFAULT(0), 所屬院系 tinyint NOT NULL ),3. 建立數(shù)據(jù)表使用SQL語句創(chuàng)建表,26,刪除表,在查詢窗口中執(zhí)行該語句，建立表結(jié)構(gòu)。 使用 drop tabl

17、e 可刪除表,練習(xí)：創(chuàng)建”學(xué)生表”,練習(xí)：刪除”學(xué)生表”,27,4、操作數(shù)據(jù)插入、刪除、修改,插入數(shù)據(jù) INSERT INTO 表名 (列名1, 列名2, ., 列名n) VALUES (值1, 值2, ., 值n) 例如： INSERT INTO 院系 VALUES (5, 水聲 , 0 , 0 , 王亮 , INSERT INTO 院系(院系編號(hào), 院系名稱) VALUES (6 , 計(jì)算機(jī)學(xué)院 ),所有字段,部分字段,28,向教師表插入數(shù)據(jù),向教師表中插入6條記錄（4人屬于5系，2人是6系的）： insert into 教師 values(t05001,王慧,

18、女,35,講師,5) insert into 教師 values(t05002,張紹華, 男,45,副教授,5) insert into 教師 values(t05003,李樹生, 男,55,教授,5) insert into 教師 values(t05004,劉華, 男,25,助教,5) insert into 教師 values(t06001,趙樹龍, 男,31,講師,6) insert into 教師 values(t06002,李建, 男,42,副教授,6) 若嘗試插入下面3條記錄： insert into 教師 values(t07001,王建新, 男,40,副教授,7) inser

19、t into 教師 values(t06002,李佩琦, 女,22,助教,6) insert into 教師 values(t06003,焦曉山, 女,17,無,6) 結(jié)果怎樣？原因？,被拒絕，違反了完整性約束,29,注意：,在插入數(shù)據(jù)時(shí)，還需要考慮到表的約束和規(guī)則等因素，如果插入的數(shù)據(jù)違反表約束或規(guī)則，則無法正常插入數(shù)據(jù)。例如： 不允許設(shè)置標(biāo)識(shí)列的值 不允許向唯一性約束列中插入相同的數(shù)據(jù) 不能違反檢查約束 不能與綁定到列的規(guī)則沖突 不能違反外部鍵約束 “mydb1”數(shù)據(jù)庫(kù)中的各個(gè)數(shù)據(jù)表建立和插入數(shù)據(jù)時(shí)，順序應(yīng)該是先被參照的表（主鍵表），然后是參照表（外鍵表）。,30,向?qū)W生表插入記錄 ins

20、ert into 學(xué)生 values(2009051101,李珊,1,1988-3-25,20090511,598,5),練習(xí)6： （1）類似地，依次地向?qū)W生表、課程表、選課表插入記錄。每個(gè)表的記錄數(shù)要求在5條以上。 （2）注意各字段數(shù)據(jù)要與定義的數(shù)據(jù)類型一致。,31,修改數(shù)據(jù)語法： UPDATE 表名 SET 列名1=值1 , 列名2=值2, ., 列名n=值n WHERE 更新條件 例如： update 教師 set 年齡=38 where 教師號(hào)=t05001 update 教師 set 年齡=年齡+1 where 所在院系=6 請(qǐng)嘗試： update 教師 set 所在院系=8 whe

21、re 教師號(hào)=t05001 update 教師 set 性別=難 where 教師號(hào)=t05001 update 教師 set 年齡=68 where 教師號(hào)=t05001 結(jié)果如何？原因？,4 操作數(shù)據(jù)插入、刪除、修改,修改一條記錄,一次修改多條記錄,32,刪除記錄DELETE FROM 表名 WHERE 刪除條件 例如： delete from 教師 where 職稱=助教 delete from 教師 where 年齡50 嘗試刪除被參照數(shù)據(jù)表（主鍵表）的數(shù)據(jù)記錄，如刪除院系表的一條記錄，觀察結(jié)果，分析原因。 delete from 院系 where 院系編號(hào)=6 當(dāng)6系還有教師、學(xué)生時(shí)

22、，根據(jù)系統(tǒng)的設(shè)置，會(huì)有不同的處理方式： 正常刪除，但可能造成數(shù)據(jù)不一致（沒有設(shè)置外鍵約束時(shí)）； 拒絕刪除（有外鍵約束時(shí)）； 級(jí)聯(lián)刪除（需要設(shè)置。刪除6系的同時(shí)，刪除所有6系教師和學(xué)生）。 刪除表中全部記錄 delete from 表名,或者使用SQL命令進(jìn)行設(shè)置,再或者修改外鍵的insert和update規(guī)范,4 操作數(shù)據(jù)插入、刪除、修改,33,create table 教師 ( 教師號(hào) char(6) primary key, 姓名 char(10) not null, 性別 char(2) check(性別 in(男,女), 年齡 tinyint check(年齡 between 18 a

23、nd 65), 職稱 char(6) check(職稱 in (教授,副教授,講師,助教,工人,其他), 所在院系 tinyint foreign key references 院系(院系編號(hào)) ON DELETE CASCADE /*當(dāng)刪除院系表中的元組造成了與教師表不一致時(shí)拒絕刪除*/ ON UPDATE NO ACTION /*當(dāng)更新院系表中的院系編號(hào)時(shí)，級(jí)聯(lián)更新教師表中相應(yīng)的元組*/ ),4 操作數(shù)據(jù)插入、刪除、修改,34,CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ) WITH ,.n ON filegroup := PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB ,CREATE INDEX：創(chuàng)建索引,35,參數(shù)說明： UNIQUE:為表或視圖創(chuàng)建唯一索引,不允許存在索引值相同的兩行。CLUSTERED:為表或視圖創(chuàng)建一個(gè)聚集索