數(shù)據(jù)庫之表操作.doc

4.5.3允許空值約束 表操作目 錄4.1設計表4設計表時需要確定的項目.41.基本數(shù)據(jù)類型4（1）數(shù)據(jù)類型4（2）數(shù)據(jù)類型的一些注意事項62.別名數(shù)據(jù)類型8（1）創(chuàng)建別名數(shù)據(jù)類型8（2）使用別名數(shù)據(jù)庫。9（3）刪除用戶自定義類型。93.目錄視圖94.2 使用SSMS表101.創(chuàng)建表102.修改表103.查看表104.刪除表104.3使用T-SQL管理表111.創(chuàng)建表112.修改表12（1）添加列12（2）修改列13（3）刪除表13（4）重命名表143.查看表144.刪除表144.4記錄操作154.4.1使用SSMS進行記錄操作154.4.2使用T-SQL進行記錄操作151.使用T-SQL插入記錄15(1)插入所有列15(2)插入指定列.152.用T-SOL修改記錄16(1)修改單條記錄.16(2)修改多條記錄17(3)修改所有記錄并指定多項修改.173.使用T-SQL刪除記錄17（1）刪除指定記錄。18（2）刪除所有記錄。184.5 SQL Sever 2005 中的數(shù)據(jù)庫完整性204.5.1數(shù)據(jù)庫完整性概述201.實體完整性202.域完整性203.引用完整性204.用戶完整性214.5.2列約束和表約束214.5.3允許空值約束22234.1設計表設計表時需要確定的項目.(1) 表的名稱.(2) 表中每一列的名稱.(3) 表中每一列的數(shù)據(jù)類型和長度.(4) 表中的列中是否允許空值、是否唯一、是否要進行默認設置或添加用戶定義約束。(5) 表中的關系，即確定哪些列是主鍵，哪些是外鍵。(6) 表中需要的索引的類型和需要建立索引的列。1. 基本數(shù)據(jù)類型（1）數(shù)據(jù)類型數(shù)據(jù)類型范圍存儲精確數(shù)字Bigint-922337203685477580892233720368547758078字節(jié)Int-214748364821474836474字節(jié)Smallint-32 786327852字節(jié)Tinyint02551字節(jié)Bit0、1、null不定DecimalNumeric-1038+11038-1517字節(jié)Money-922 337 203 685 477.5808922 337 203 685 477.58078字節(jié)Smallmoney-214 748.3648214 748.36474字節(jié)近似數(shù)值Float-1.79E+308-2.23E-308、0以及2.23E-3081.79E+30848字節(jié)Real-3.40E+38-1.18E-38、0、1.18E-383.40E+384字節(jié)日期和時間Datetime1753年1月1日9999年12月31日（精確到3.33毫秒）8字節(jié)Smalldatetime1900年1月1日2079年6月6日（精確到1分鐘）4字節(jié)字符串（2）數(shù)據(jù)類型的一些注意事項l Bit類型.bit列為8bit或更少時作為1個字節(jié)儲存.如果為9bit16bit,則這些列作為2個字節(jié)存儲,以此類推.l Char與Varchar類型.如果列數(shù)據(jù)項的大小一致,則使用char.如果列數(shù)據(jù)項的大小差異相當大,則使用varchar.如果數(shù)據(jù)項大小相差很大,而且大小可能超過8000字節(jié),請使用varchar(max)(231-1)l Binary與Varbinary類型.如果列數(shù)據(jù)項的大小一致,則使用binary.如果列數(shù)據(jù)項的大小差異相當大，則使用varbinary。當列數(shù)據(jù)條目超出8000字節(jié)時，請使用varbinary（max）。l 二進制數(shù)據(jù)類型。二進制數(shù)據(jù)由十六進制表示（十進制數(shù)245等于十六進制數(shù)F5）。l Image類型。Image數(shù)據(jù)列可以用來儲存超過8kb的可變長度的二進制數(shù)據(jù)，如Microsoft word文檔、Microsoft Excel電子表格、包含位圖的圖像、圖形交換格式（gif0文件和聯(lián)合圖形專家組（JPEG）文件。l Text類型。Text數(shù)據(jù)類型的列可用于儲存大于8kb的ASCII字符。例如，由于HTML文檔均由ASCII字符組成且一般長于8kb，所以用瀏覽器查看之前應在SQL Server中存儲在text列中。l Nchar、nvarchar和ntext類型。字符列寬度的定義不超過所儲存的字符數(shù)據(jù)可能的最大長度，如果要在SQL Server中存儲國際化字符數(shù)據(jù)，請使用nchar、nvarchar和ntext數(shù)據(jù)類型。l Unicode數(shù)據(jù)類型。Unicode數(shù)據(jù)類型需要相當于非unicode數(shù)據(jù)類型兩倍的存儲空間。l Numeric與decimal類型。在SQL Server中，numeric數(shù)據(jù)類型等價于decimal數(shù)據(jù)類型，如果數(shù)值超過貨幣數(shù)據(jù)范圍，則可使用decimal數(shù)據(jù)類型代替。l Timestamp類型。Timestamp用于表示Sql Server在一行上的活動順序，按二進制格式以遞增的數(shù)字來表示。當表中的行發(fā)生變動時，用從DBTS的函數(shù)獲得的當前數(shù)據(jù)庫的時間戳值來更新時間戳。Timestamp數(shù)據(jù)與插入或修改數(shù)據(jù)的日期和時間無關。若要自動記錄表中數(shù)據(jù)更改的時間，使用datetime或smalldatetime數(shù)據(jù)類型記錄時間或觸發(fā)器。l Uniqueidentifier類型。Uniqueidentifier以一個16位的十六進制數(shù)表示全局唯一標識符（GUID）。當需要在多行中唯一標識莫一行時可使用GUID，例如，可使用unique_identifiler數(shù)據(jù)類型定義一個客戶標識代碼列，已編輯公司來自多個國家/地區(qū)的總的客戶名錄。l Table類型。Table數(shù)據(jù)類型只能用于定義table類型的局部變量或用戶定義函數(shù)的返回值。2.別名數(shù)據(jù)類型（1）創(chuàng)建別名數(shù)據(jù)類型Create type 類型名From 基本數(shù)據(jù)類型【null | not null】參考含義：l 類型名：別名數(shù)據(jù)類型或用戶定義類型的名稱。類型名稱必須符合標識符的規(guī)則。l 基本數(shù)據(jù)類型：別名數(shù)據(jù)類型所基于的數(shù)據(jù)類型（decimal、int等）由SQl Server提供。l Null | not null：是否可以為空。例子Use webshopGoCreate type mydataFrom varchar(10) not null【提示】l 在SQL Server 2005中建議不使用儲存過程sp_addtype創(chuàng)建別名數(shù)據(jù)庫類型，也不使用sp_droptype刪除別名數(shù)據(jù)類型。l 在SQL Server 2005中有別名數(shù)據(jù)類型和用戶自定義數(shù)據(jù)類型之分，后者使用create assembly語句創(chuàng)建。l 如果別名數(shù)據(jù)類型是在model數(shù)據(jù)庫中創(chuàng)建的，他將作用于所有用戶定義的新數(shù)據(jù)庫中；如果別名數(shù)據(jù)類型是在用戶定義的數(shù)據(jù)庫中創(chuàng)建的，則該數(shù)據(jù)類型只作用于此用戶定義的數(shù)據(jù)庫。（2）使用別名數(shù)據(jù)庫。（3）刪除用戶自定義類型。使用drop type可以從systypes中刪除用戶定義的數(shù)據(jù)庫類型，其基本語句格式如下：Drop type 類型名參考含義：類型是指已經(jīng)定義好的別名數(shù)據(jù)類型的名稱。例子：Use WebShopGoDrop type mydata3.目錄視圖4.2 使用SSMS表1.創(chuàng)建表【提示】l 盡可能地在創(chuàng)建表時正確的輸入獵德信息。l 同一數(shù)據(jù)庫中，列名不能相同。2.修改表3.查看表4.刪除表【提示】l 數(shù)據(jù)庫中的表刪除后不能恢復。l 如果要刪除的表與其他的標有依賴關系，則該表不能被刪除。4.3使用T-SQL管理表1.創(chuàng)建表使用T-Sql語句創(chuàng)建表的操作的基本語句如下:Create table （【列級完整性約束條件】【列級完整性約束條件】。）例子：Use WebShopGoCreate table goods（G_ID char（6），G_name varchar(50),T_ID char(2),G_price float,G_Discount float,G_number smallint,G_ProduceDate datetime,g-Image varchar(100)，g_status varchar（10），g_Description varcharvarchar(1000)）【提示】l 表是數(shù)據(jù)庫的組成對象，在進行創(chuàng)建表的操作之前，先要通過命令USE WebShop打開要操作的數(shù)據(jù)庫。l 用戶在選擇表和列名稱時不要使用SQL語言中的保留關鍵詞，如select、create和insert等。l 在這里沒有考慮表中的約束情況。2.修改表修改表的基本語句格式如下：Alter tableAlter column Add完整性約束Drop（1）添加列Alter table goods add g_Producer varchar(20)【提示】l 在alter table語句中使用add關鍵字增加列。l 不論表中是否已有數(shù)據(jù)，新增加的列一律為空值，且新增加的一列位于表結(jié)構(gòu)的末尾。（2）修改列Alter table goods alter column g_ProduceDate char（10）【提示】l 在alter table 語句使用alter column關鍵字修改的數(shù)據(jù)類型或?qū)挾取 在“對象資源管理器”中展開表節(jié)點中的指定表節(jié)點后再展開【列】節(jié)點可以查看指定表中列的信息。（3）刪除表用Alter table 語句刪除列,可用drop column關鍵字.【例子】Alter table goods drop column g_Produce【提示】l 在使用alter table時，每次只能添加或刪除一列。l 在添加列時，不需要帶關鍵字column；在刪除列時，在列名前要帶上關鍵字column，因為在默認情況下，認為是刪除約束。l 在添加列時，需要帶數(shù)據(jù)類型和長度；在刪除列時，不需要帶數(shù)據(jù)類型和長度，只需指定列名。l 如果在該列定義了約束，在修改列時會進行限制，如果確實需要修改該列，線必須刪除該列上的約束，然后再進行修改。（4）重命名表使用存儲過程sp_rename可以更改當前數(shù)據(jù)庫中表的名稱，存儲過程sp_rename基本語句格式如下：Sp_rename當前表名，【新表名】3.查看表Sp_help 【表名】4.刪除表Drop table如：drop table good4.4記錄操作4.4.1使用SSMS進行記錄操作4.4.2使用T-SQL進行記錄操作1.使用T-SQL插入記錄使用Insert into語句可以向表中添加記錄或者創(chuàng)建追加查詢,插入單個記錄的基本語句格式如下:Insert into ，Values (,)(1)插入所有列。Insert into goods values （020003，愛國者MP3-1G，2，128,0.8,20，2007-08-01，pImage/020003.gif,熱點,容量G）(2)插入指定列.Insert into goods (g_ID,g_Name,t_ID,g_Discount,g_Number,g_ProduceDate,g_Status)Values (040002,杉杉西服，04，1288,0.9,20，2007-08-01，熱點)【提示】l Insert 語句中的into可以省略。l 如果某些屬性列在表名后的列名表沒有出現(xiàn)，則新紀錄在這些列上將取空值。但是要注意的是，在表定義時說明了not null的屬性列不能去空值，否則系統(tǒng)會出現(xiàn)錯誤提示。l 如果沒有指明任何列名，則新插入的記錄必須在每個屬性列上均有值。l 字符數(shù)據(jù)類型必須“”將其引起來。l 常量的順序必須與指定的列名順序保持一致。2.用T-SOL修改記錄使用Update語句可以按照某個條件修改特定表中的字段值，其基本語句格式如下：UpdateSet =,=from where (1)修改單條記錄.Update goodsSet g_Status=熱點Where g_name=勁霸西服(2)修改多條記錄Update goods Set g_Image=Images/+g_ImageWhere g_Image is not null【提示】l 如果不指定條件，則會修改所有的記錄。l 如果加上條件is not null就可以保證對已有圖片的商品進行修改。(3)修改所有記錄并指定多項修改.Update goodsSet g_discount=0.8，g_ProduceDate=2007-07-01【提示】l 如果要修改多列,則在set語句后用”,”分隔各修改子句.l 這類一般在進行數(shù)據(jù)初始化時使用.l 修改記錄時可以通過約束和觸發(fā)器實現(xiàn)數(shù)據(jù)完整性.3.使用T-SQL刪除記錄使用Delete語句可以刪除表中的記錄,其基本語句如下:DeleteFrom【where】Delete 語句的功能是從表中刪除滿足where子句條件的所有記錄。如果省略where子句，表示刪除表中全部記錄，但表的定義仍存在。也就是說，delete語句刪除的是表中的數(shù)據(jù)，而不是關于表的定義。（1）刪除指定記錄。Delete From goodsWhere g_ID=040002Delete操作也是一次只能操作一個表，因此同樣會遇到update操作中提到的數(shù)據(jù)不一致問題。比如商品號為040002商品被刪除后，包含該商品的訂單信息也應同時刪除?！咎崾尽縧 如果是外鍵約束，則可以先將外鍵表中對應的記錄刪除，然后再刪除主鍵表中的記錄。l 記錄刪除后不能被恢復。（2）刪除所有記錄。Delete From goods該語句使goods成為空表，它刪除了goods的所有記錄。刪除表中所有記錄也可以使用truncate table語句來完成。Truncate table goods【提示】l Delete刪除操作被當做是系統(tǒng)事務，刪除操作可以被撤銷。l Truncate table 則不是，刪除操作不能被撤銷。4.5 SQL Sever 2005 中的數(shù)據(jù)庫完整性4.5.1數(shù)據(jù)庫完整性概述數(shù)據(jù)庫的完整性是指數(shù)據(jù)的準確性和一致性。它是防止數(shù)據(jù)庫中存在不符合語義規(guī)定的數(shù)據(jù)和防止因錯誤信息的輸入輸出造成無效操作而提出的。數(shù)據(jù)完整性主要分為四類：實體完整性、域完整性、引用完整性和用戶定義完整性。在SQL Server 2005 中可以通過空值約束、默認值定義、check約束、primary key約束、foreign key約束和unique約束等來實施數(shù)據(jù)庫完整性。1.實體完整性實體完整性規(guī)定表的每一行在表中是唯一的.實體表中定義的索引、unique約束、primary key 約束和identity屬性就是實體完整性的體現(xiàn)。2.域完整性域完整性是指數(shù)據(jù)庫表中的列必須滿足某種特定的數(shù)據(jù)類型或約束，其中約束又包括取值范圍和精度等規(guī)定。表中的check約束、foreign key約束、default定義、not null和規(guī)則都屬于域完整性的范疇。3.引用完整性引用完整性是指兩個表的主關鍵字和外關鍵字的數(shù)據(jù)應對應一致。它確保了有主關鍵字的表中對應其他表的外關鍵字的行存在，即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義的數(shù)據(jù)庫中擴散。引用完整性是創(chuàng)建在外關鍵字和主關鍵字之間或外關鍵字和唯一關鍵字之間的關系上的。引用完整性時，SQL Server將阻止用戶執(zhí)行下列操作。（1） 在主表中沒有關聯(lián)的記錄時，將記錄添加或更改到相關表中。（2） 更改主表中的值時，這會導致相關表中生成孤立記錄。（3） 從主表中刪除記錄，但仍存在與該記錄匹配的相關記錄。Foreign key和check約束都屬于引用完整性。4.用戶完整性用戶定義完