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ù)庫管理與維護的大部分工作。如配置系統(tǒng)環(huán)境和管理SQL Server服務器，顯示、建立、管理所有的SQL Server對象，備份和恢復數(shù)據(jù)庫，復制數(shù)據(jù)庫，

2、管理用戶帳戶等。輸入和執(zhí)行Transact-SQL語句，并且迅速查看這些語句的結果，以分析和處理數(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)設置信息、SQL Server的初始化信息和其它系統(tǒng)及用戶數(shù)據(jù)庫的相關信息。 建議不要在 master 數(shù)據(jù)庫中創(chuàng)建任

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

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

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

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

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

8、名稱、設計數(shù)據(jù)庫所占用的存儲空間和文件的存放位置。創(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ù)庫的所有者,默認為登錄用戶,數(shù)據(jù)庫文件存儲位置,添加此數(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,練習：,1、創(chuàng)建一個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、增加一個“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ù)對象,在一個SQL Server數(shù)據(jù)庫中，除了包含基本表以外，還包含了與這些基本表相關的多種對象，如：視圖、索引、存儲過程和觸發(fā)器等等。目的是為執(zhí)行與數(shù)據(jù)有關的活動提供支持。,14,,1. 表在數(shù)據(jù)庫中，所有的數(shù)據(jù)存放在表中，表由行(記錄)和列(字段)組成，一個數(shù)據(jù)庫可以包含多個表。2. 視圖視圖是由查

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

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

15、庫的用戶及其權限。9. 角色角色定義了一組具有相同權限的用戶。,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,,浮點數(shù)型：decimal（小數(shù)） 從 -10^38 +1 到 10^38 –1 的固定精度和小數(shù)位的數(shù)字數(shù)據(jù)。例如：decimal(5,2) 總長度5位（不含小數(shù)點），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ù)點后4位。smallmoney 貨幣數(shù)據(jù)值介于 -214,748.3648 與 +214,748.3647 之間，精確到貨幣單位的千分之十。,19,,近似數(shù)字real 從 -3.40E + 38 到

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

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

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

21、7,483,647) 個字節(jié)。主要用于圖片、視頻、聲音等。,22,,其它數(shù)據(jù)類型uniqueidentifier 全局唯一標識符 (GUID) 使用newid( )自動生成。cursor 游標的引用。sql_variant 一種存儲 SQL Server 支持的各種數(shù)據(jù)類型（text、ntext、timestamp 和 sql_variant 除外）值的數(shù)據(jù)類型。table 一種特殊的數(shù)據(jù)類型，存儲供以后處理的

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

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

24、使用SQL語句創(chuàng)建表,26,刪除表,在查詢窗口中執(zhí)行該語句，建立表結構。 使用 drop table 可刪除表,練習：創(chuàng)建”學生表”,練習：刪除”學生表”,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 , ‘計算機學院’ ),所有字段,部分字段,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) 結果怎樣？

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

30、to 學生 values('2009051101','李珊',1,'1988-3-25','20090511',598,5),練習6：（1）類似地，依次地向?qū)W生表、課程表、選課表插入記錄。每個表的記錄數(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'結果如何？原因？,4 操作數(shù)據(jù)——插入、刪除、修改,修改一條記錄,一次修改多條記錄,32,刪除記錄DELETE FROM 表名 WHERE 刪除條件例如：delete from 教師 where 職稱='助教' delete from 教師 where 年齡>50嘗試刪除被參照數(shù)據(jù)表（主鍵表）的數(shù)據(jù)記錄，如刪除院系表的一條記錄，觀察結果，分析原因。

33、delete from 院系 where 院系編號=6當6系還有教師、學生時，根據(jù)系統(tǒng)的設置，會有不同的處理方式：正常刪除，但可能造成數(shù)據(jù)不一致（沒有設置外鍵約束時）；拒絕刪除（有外鍵約束時）；級聯(lián)刪除（需要設置。刪除6系的同時，刪除所有6系教師和學生）。刪除表中全部記錄 delete from 表名,或者使用SQL命令進行設置,再或者修改外鍵的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 /*當刪除院系表中的元組造成了與教師表不一致時拒絕刪除*/ ON UPDATE NO ACTION /*當更新院系表中的院系編號時，級聯(lián)更新教師表中相應的元組*/),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)建一個聚集索引。　NONCLUSTERED:創(chuàng)建一個非聚集索引。　ind

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

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

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