02建立數(shù)據(jù)庫和表

1、Ch2 建立數(shù)據(jù)庫,,1,本章內(nèi)容,,1. 建立數(shù)據(jù)庫,2,SQL server2008客戶端工具,SQL Server Management Studio 是為 SQL Server 數(shù)據(jù)庫管理員和開發(fā)人員提供的新工具。它提供了用于數(shù)據(jù)庫管理的圖形工具和功能豐富的開發(fā)環(huán)境。完成數(shù)據(jù)庫管理與維護(hù)的大部分工作。如配置系統(tǒng)環(huán)境和管理SQL Server服務(wù)器，顯示、建立、管理所有的SQL Server對象，備份和恢復(fù)數(shù)據(jù)庫，復(fù)制數(shù)據(jù)庫，

2、管理用戶帳戶等。輸入和執(zhí)行Transact-SQL語句，并且迅速查看這些語句的結(jié)果，以分析和處理數(shù)據(jù)庫中的數(shù)據(jù)。,3,SQL server2005系統(tǒng)數(shù)據(jù)庫簡介,1). master(主要的) 是系統(tǒng)最重要的數(shù)據(jù)庫，它記錄了SQL Server系統(tǒng)的所有系統(tǒng)信息。這些系統(tǒng)信息包括所有的登錄信息、系統(tǒng)設(shè)置信息、SQL Server的初始化信息和其它系統(tǒng)及用戶數(shù)據(jù)庫的相關(guān)信息。 建議不要在 master 數(shù)據(jù)庫中創(chuàng)建任

3、何用戶對象。如表、視圖、存儲(chǔ)過程或觸發(fā)器等。2). model（模型的） 是所有用戶數(shù)據(jù)庫和tempdb（臨時(shí)）數(shù)據(jù)庫的模板數(shù)據(jù)庫，它含有master數(shù)據(jù)庫所有系統(tǒng)表的子集，這些系統(tǒng)表是每個(gè)用戶定義數(shù)據(jù)庫需要的。,4,,3). msdb 是代理服務(wù)數(shù)據(jù)庫，用于為警報(bào)、任務(wù)調(diào)度和記錄操作員的操作提供存儲(chǔ)空間。 4). tempdb （臨時(shí)） 用于為所有的臨時(shí)表、臨時(shí)存儲(chǔ)過程提供存儲(chǔ)空間，還用于任何其它的臨時(shí)存儲(chǔ)要求

4、。 例如存儲(chǔ)SQL Server生成的工作表。tempdb數(shù)據(jù)庫是全局資源，所有連接到系統(tǒng)的用戶的臨時(shí)表和存儲(chǔ)過程都存儲(chǔ)在該數(shù)據(jù)庫中。在SQL Server 每次啟動(dòng)時(shí)都重新創(chuàng)建，因此該數(shù)據(jù)庫在系統(tǒng)啟動(dòng)時(shí)總是干凈的，臨時(shí)表和存儲(chǔ)過程在連接斷開時(shí)自動(dòng)除去。,5,自帶的示例數(shù)據(jù)庫,5).AdventureWorks數(shù)據(jù)庫（示例） AdventureWorks數(shù)據(jù)庫: 以一家大型跨國生產(chǎn)公司為模型，該公司生產(chǎn)金屬和復(fù)合材料的

5、自行車，產(chǎn)品遠(yuǎn)銷北美、歐洲和亞洲市場。該數(shù)據(jù)庫及其中的表經(jīng)常在幫助文檔和教程所介紹的示例中使用。 默認(rèn)情況下不安裝 AdventureWorks數(shù)據(jù)庫 ，可以從 Microsoft 下載中心下載并安裝。,6,1. 建立數(shù)據(jù)庫,SQL Server中每個(gè)數(shù)據(jù)庫由一組操作系統(tǒng)文件組成。數(shù)據(jù)庫中的所有數(shù)據(jù)、對象和數(shù)據(jù)庫操作日志都存儲(chǔ)在這些文件中。數(shù)據(jù)庫文件可以分為三類：主數(shù)據(jù)庫文件(.mdf)次數(shù)據(jù)庫文件(.ndf)事務(wù)日志

6、文件(.ldf),7,1.1 數(shù)據(jù)庫文件,主數(shù)據(jù)庫文件(Primary Database file)每個(gè)數(shù)據(jù)庫有且僅有一個(gè)主數(shù)據(jù)庫文件，主數(shù)據(jù)庫文件用來存儲(chǔ)數(shù)據(jù)庫的啟動(dòng)信息和部分或全部數(shù)據(jù)。一個(gè)數(shù)據(jù)庫可以有1到多個(gè)數(shù)據(jù)庫文件，其中只有一個(gè)文件為主數(shù)據(jù)庫文件。主數(shù)據(jù)庫文件的文件擴(kuò)展名為mdf，如：try1.mdf。次數(shù)據(jù)庫文件(Secondary Database File)用于存儲(chǔ)主數(shù)據(jù)庫文件中未存儲(chǔ)的剩余數(shù)據(jù)和數(shù)據(jù)庫對象。一

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

8、名稱、設(shè)計(jì)數(shù)據(jù)庫所占用的存儲(chǔ)空間和文件的存放位置。創(chuàng)建數(shù)據(jù)庫的途徑: SQL Server Management Studio,兩種方法,使用管理工具,使用Transact-SQL語句,,9,,使用管理工具創(chuàng)建數(shù)據(jù)庫,創(chuàng)建mydb1數(shù)據(jù)庫，文件存放在C盤db下,10,,,數(shù)據(jù)庫名稱數(shù)據(jù)庫的所有者,默認(rèn)為登錄用戶,數(shù)據(jù)庫文件存儲(chǔ)位置,添加此數(shù)據(jù)庫文件,11,,使用SQL命令CREATE DATABASE mydb2 ON

9、 PRIMARY --在主文件組創(chuàng)建, PRIMARY可以省略 ( NAME = ‘mydb2', FILENAME = ‘c:\db\mydb2.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON --創(chuàng)建日志文件 ( NAME = ‘mydb2_log', FILENAME = ‘c:\db\mydb2_log.ldf' , S

10、IZE = 1024KB , FILEGROWTH = 10%),創(chuàng)建mydb2數(shù)據(jù)庫，文件存放在C盤db下,12,練習(xí)：,1、創(chuàng)建一個(gè)mydb1數(shù)據(jù)庫，位置“D:\mydb1”,初始容量8M，最大容量16M，文件增長量為5%。 CREATE DATABASE mydb1 ON ( NAME = ‘mydb1', FILENAME = ‘d:\mydb1.mdf' ,

11、 SIZE = 8MB , MAXSIZE=16MB, FILEGROWTH = 5% )2、增加一個(gè)“D:\mydb_log1”日志文件，要求初始容量為2MB,最大容量50MB，文件增長量10%。 CREATE DATABASE mydb_log1 ADD LOG FILE ( NAME = ‘mydb_log1’, FILENAME = ‘D:\mydb_log1’, S

12、IZE = 2MB , MAXSIZE=50MB, FILEGROWTH = 10% ),13,1.3 數(shù)據(jù)庫包含的數(shù)據(jù)對象,在一個(gè)SQL Server數(shù)據(jù)庫中，除了包含基本表以外，還包含了與這些基本表相關(guān)的多種對象，如：視圖、索引、存儲(chǔ)過程和觸發(fā)器等等。目的是為執(zhí)行與數(shù)據(jù)有關(guān)的活動(dòng)提供支持。,14,,1. 表在數(shù)據(jù)庫中，所有的數(shù)據(jù)存放在表中，表由行(記錄)和列(字段)組成，一個(gè)數(shù)據(jù)庫可以包含多個(gè)表。2. 視圖視圖是由查

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

14、束定義了關(guān)于列中允許值的規(guī)則。例如，強(qiáng)制定義某成績列的值只能在0到100之間。6. 默認(rèn)值如果在插入行時(shí)沒有指定該行中某列的值，那么使用默認(rèn)值可以指定該列自動(dòng)使用的值。例如，定義某“性別”列的默認(rèn)值為“男”，則插入某學(xué)生信息時(shí)，如果沒有指定其性別，則自動(dòng)采用定義的默認(rèn)值“男”。7. 觸發(fā)器觸發(fā)器由一組SQL語句組成，當(dāng)對表或視圖進(jìn)行某種操作（添加、刪除或修改）時(shí)，這組命令會(huì)自動(dòng)執(zhí)行。,16,,8. 用戶用于定義允許訪問當(dāng)前數(shù)據(jù)

15、庫的用戶及其權(quán)限。9. 角色角色定義了一組具有相同權(quán)限的用戶。,17,整數(shù)型bit 1 或 0 的整數(shù)數(shù)據(jù)。Tinyint（極小整數(shù)） 從 0 到 255 的整數(shù)數(shù)據(jù)。Smallint（小整數(shù)） 從 -215 (-32,768) 到 215 - 1 (32,767) 的整數(shù)數(shù)據(jù)。int 從 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型數(shù)據(jù)

16、（所有數(shù)字）。bigint 從 -263 (-9223372036854775808) 到 263-1 (9223372036854775807) 的整型數(shù)據(jù)（所有數(shù)字）。,2、數(shù)據(jù)類型,18,,浮點(diǎn)數(shù)型：decimal（小數(shù)） 從 -10^38 +1 到 10^38 –1 的固定精度和小數(shù)位的數(shù)字?jǐn)?shù)據(jù)。例如：decimal(5,2) 總長度5位（不含小數(shù)點(diǎn)），2位小數(shù)。numeric（數(shù)值） 功能上等同于 decimal。

17、貨幣型：Money（貨幣） 貨幣數(shù)據(jù)值介于 -2^63 (-922,337,203,685,477.5808) 與 2^63 - 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 到

18、 3.40E + 38 的浮點(diǎn)精度數(shù)字float 從 -1.79E + 308 到 1.79E + 308 的浮點(diǎn)精度數(shù)字時(shí)間日期型smalldatetime 從 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和時(shí)間數(shù)據(jù)，精確到分鐘datetime 從 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和時(shí)間數(shù)據(jù)，精確到百分之三秒（或 3.33 毫秒）。,20,,字符串char 固

19、定長度的ASCII字符數(shù)據(jù)，最大長度為 8,000 個(gè)字符。例如：char（5），占據(jù)5個(gè)字節(jié)。varchar 可變長度的ASCII 數(shù)據(jù)，最長為 8,000 個(gè)字符。例如： varchar（5），占據(jù)1-5個(gè)字節(jié)。text 可變長度的ASCII 數(shù)據(jù)，最大長度為 2^31 - 1 (2,147,483,647) 個(gè)字符。nchar 固定長度的 二字節(jié)ASCII碼數(shù)據(jù)，最大長度為 4,000 個(gè)字符。 nvarchar

20、 可變長度 Unicode 數(shù)據(jù)，其最大長度為 4,000 字符。ntext 可變長度 Unicode 數(shù)據(jù)，其最大長度為 2^30 - 1 (1,073,741,823) 個(gè)字符。,21,,二進(jìn)制binary 固定長度的二進(jìn)制數(shù)據(jù)，其最大長度為 8,000 個(gè)字節(jié)。varbinary 可變長度的二進(jìn)制數(shù)據(jù)，其最大長度為 8,000 個(gè)字節(jié)。image 可變長度的二進(jìn)制數(shù)據(jù)，其最大長度為 2^31 - 1 (2,14

21、7,483,647) 個(gè)字節(jié)。主要用于圖片、視頻、聲音等。,22,,其它數(shù)據(jù)類型uniqueidentifier 全局唯一標(biāo)識符 (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ǔ)供以后處理的

22、結(jié)果集。timestamp 數(shù)據(jù)庫范圍的唯一數(shù)字，每次更新行時(shí)也進(jìn)行更新。,23,3. 建立數(shù)據(jù)表——使用管理工具SSMS創(chuàng)建表,列的常用屬性列名列的數(shù)據(jù)類型是否允許為空（NULL或NOT NULL）主鍵（PRIMARY KEY）默認(rèn)值（DEFAULT）,,24,,設(shè)置自動(dòng)增加的標(biāo)識字段,25,使用CREATE TABLE語句在數(shù)據(jù)庫“mydb1”中創(chuàng)建“學(xué)生”表。 CREATE TABLE 學(xué)生 (

23、 學(xué)號 char(10) PRIMARY KEY , 姓名 varchar(50) NOT NULL, 性別 bit DEFAULT(0), 出生日期 smalldatetime, 班級 char(8) NOT NULL, 入學(xué)成績 float DEFAULT(0), 所屬院系 tinyint NOT NULL ),3. 建立數(shù)據(jù)表——

24、使用SQL語句創(chuàng)建表,26,刪除表,在查詢窗口中執(zhí)行該語句，建立表結(jié)構(gòu)。 使用 drop table 可刪除表,練習(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, '水聲

25、‘ , 0 , 0 , '王亮‘ , '13534265487') INSERT INTO 院系(院系編號, 院系名稱) VALUES (6 , ‘計(jì)算機(jī)學(xué)院’ ),所有字段,部分字段,28,向教師表插入數(shù)據(jù),向教師表中插入6條記錄（4人屬于5系，2人是6系的）：insert into 教師 values('t05001','王慧', '女'

26、,35,'講師',5) insert into 教師 values(‘t05002’,‘張紹華’, '男',45,'副教授',5)insert into 教師 values('t05003','李樹生', '男',55,'教授',5)insert into 教師 values('t05004','

27、劉華', '男',25,'助教',5)insert into 教師 values('t06001','趙樹龍', '男',31,'講師',6)insert into 教師 values('t06002','李建', '男',42,'副教授',6)若嘗試插入下面3條記

28、錄：insert into 教師 values('t07001','王建新', '男',40,'副教授',7)insert into 教師 values(‘t06002’,‘李佩琦’, '女',22,'助教',6)insert into 教師 values(‘t06003’,‘焦曉山’, ‘女’,17,‘無',6) 結(jié)果怎樣？

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

30、to 學(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

31、=值n ] [WHERE 更新條件] 例如：update 教師 set 年齡=38 where 教師號='t05001‘update 教師 set 年齡=年齡+1 where 所在院系=6請嘗試：update 教師 set 所在院系=8 where 教師號='t05001‘update 教師 set 性別=‘難’ where 教師號='t05001'update 教師 set 年齡=68

32、 where 教師號='t05001'結(jié)果如何？原因？,4 操作數(shù)據(jù)——插入、刪除、修改,修改一條記錄,一次修改多條記錄,32,刪除記錄DELETE FROM 表名 WHERE 刪除條件例如：delete from 教師 where 職稱='助教' delete from 教師 where 年齡>50嘗試刪除被參照數(shù)據(jù)表（主鍵表）的數(shù)據(jù)記錄，如刪除院系表的一條記錄，觀察結(jié)果，分析原因。

33、delete from 院系 where 院系編號=6當(dāng)6系還有教師、學(xué)生時(shí)，根據(jù)系統(tǒng)的設(shè)置，會(huì)有不同的處理方式：正常刪除，但可能造成數(shù)據(jù)不一致（沒有設(shè)置外鍵約束時(shí)）；拒絕刪除（有外鍵約束時(shí)）；級聯(lián)刪除（需要設(shè)置。刪除6系的同時(shí)，刪除所有6系教師和學(xué)生）。刪除表中全部記錄 delete from 表名,或者使用SQL命令進(jìn)行設(shè)置,再或者修改外鍵的insert和update規(guī)范,4 操作數(shù)據(jù)——插入、刪除、修改,33,c

34、reate table 教師( 教師號 char(6) primary key, 姓名 char(10) not null, 性別 char(2) check(性別 in(‘男’,’女’)), 年齡 tinyint check(年齡 between 18 and 65), 職稱 char(6) check(職稱 in (‘教授’,‘副教授’,‘講師’,‘助教’,‘工人‘,’其他’)),

35、所在院系 tinyint foreign key references 院系(院系編號) ON DELETE CASCADE /*當(dāng)刪除院系表中的元組造成了與教師表不一致時(shí)拒絕刪除*/ ON UPDATE NO ACTION /*當(dāng)更新院系表中的院系編號時(shí)，級聯(lián)更新教師表中相應(yīng)的元組*/),4 操作數(shù)據(jù)——插入、刪除、修改,34

36、,CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name　　 ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) 　　[ WITH [ ,...n] ] 　　[ ON filegroup ] 　　 ::= 　　 { PAD_INDEX | 　　 FILLFACTOR = fillfactor

37、| 　　 IGNORE_DUP_KEY | 　　 DROP_EXISTING | 　　 STATISTICS_NORECOMPUTE | 　　 SORT_IN_TEMPDB 　　},CREATE INDEX：創(chuàng)建索引,35,,參數(shù)說明： UNIQUE:為表或視圖創(chuàng)建唯一索引,不允許存在索引值相同的兩行?！LUSTERED:為表或視圖創(chuàng)建一個(gè)聚集索引。　NONCLUSTERED:創(chuàng)建一個(gè)非聚集索引?！nd

38、ex_name:索引名?！able:包含要?jiǎng)?chuàng)建索引的列的表?？梢赃x擇指定數(shù)據(jù)庫和表所有者。 　View:要建立索引的視圖的名稱。 　Column:應(yīng)用索引的列。指定兩個(gè)或多個(gè)列名，可為指定列的組合值創(chuàng)建組合索引?！ASC | DESC]:確定具體某個(gè)索引列的升序或降序排序方向。默認(rèn)設(shè)置為 ASC。 　N:表示可以為特定索引指定多個(gè) columns 的占位符。 　PAD_INDEX:指定索引中間級中每個(gè)頁（節(jié)點(diǎn)）上保持開放

39、的空間。PAD_INDEX 選項(xiàng)只有在指定了 FILLFACTOR 時(shí)才有用，因?yàn)?PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。　FILLFACTOR = fillfactor:指定在 SQL Server 創(chuàng)建索引的過程中，各索引頁葉級的填滿程度。如果某個(gè)索引頁填滿，SQL Server 就必須花時(shí)間拆分該索引頁，以便為新行騰出空間，這需要很大的開銷。　IGNORE_DUP_KEY:控制當(dāng)嘗試向?qū)儆谖ㄒ痪?/p>

40、集索引的列插入重復(fù)的鍵值時(shí)所發(fā)生的情況。 　DROP_EXISTING:指定應(yīng)除去并重建已命名的先前存在的聚集索引或非聚集索引?！TATISTICS_NORECOMPUTE:指定過期的索引統(tǒng)計(jì)不會(huì)自動(dòng)重新計(jì)算?！ORT_IN_TEMPDB:指定用于生成索引的中間排序結(jié)果將存儲(chǔ)在 tempdb 數(shù)據(jù)庫中。 ON filegroup:在給定的 filegroup 上創(chuàng)建指定的索引。,36,課堂作業(yè)：,1、簡述int、cha