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

4.5.3允許空值約束 表操作目 錄4.1設(shè)計(jì)表4設(shè)計(jì)表時需要確定的項(xiàng)目.41.基本數(shù)據(jù)類型4（1）數(shù)據(jù)類型4（2）數(shù)據(jù)類型的一些注意事項(xiàng)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進(jìn)行記錄操作154.4.2使用T-SQL進(jìn)行記錄操作151.使用T-SQL插入記錄15(1)插入所有列15(2)插入指定列.152.用T-SOL修改記錄16(1)修改單條記錄.16(2)修改多條記錄17(3)修改所有記錄并指定多項(xiàng)修改.173.使用T-SQL刪除記錄17（1）刪除指定記錄。18（2）刪除所有記錄。184.5 SQL Sever 2005 中的數(shù)據(jù)庫完整性204.5.1數(shù)據(jù)庫完整性概述201.實(shí)體完整性202.域完整性203.引用完整性204.用戶完整性214.5.2列約束和表約束214.5.3允許空值約束22234.1設(shè)計(jì)表設(shè)計(jì)表時需要確定的項(xiàng)目.(1) 表的名稱.(2) 表中每一列的名稱.(3) 表中每一列的數(shù)據(jù)類型和長度.(4) 表中的列中是否允許空值、是否唯一、是否要進(jìn)行默認(rèn)設(shè)置或添加用戶定義約束。(5) 表中的關(guān)系，即確定哪些列是主鍵，哪些是外鍵。(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ù)類型的一些注意事項(xiàng)l Bit類型.bit列為8bit或更少時作為1個字節(jié)儲存.如果為9bit16bit,則這些列作為2個字節(jié)存儲,以此類推.l Char與Varchar類型.如果列數(shù)據(jù)項(xiàng)的大小一致,則使用char.如果列數(shù)據(jù)項(xiàng)的大小差異相當(dāng)大,則使用varchar.如果數(shù)據(jù)項(xiàng)大小相差很大,而且大小可能超過8000字節(jié),請使用varchar(max)(231-1)l Binary與Varbinary類型.如果列數(shù)據(jù)項(xiàng)的大小一致,則使用binary.如果列數(shù)據(jù)項(xiàng)的大小差異相當(dāng)大，則使用varbinary。當(dāng)列數(shù)據(jù)條目超出8000字節(jié)時，請使用varbinary（max）。l 二進(jìn)制數(shù)據(jù)類型。二進(jìn)制數(shù)據(jù)由十六進(jìn)制表示（十進(jìn)制數(shù)245等于十六進(jìn)制數(shù)F5）。l Image類型。Image數(shù)據(jù)列可以用來儲存超過8kb的可變長度的二進(jìn)制數(shù)據(jù)，如Microsoft word文檔、Microsoft Excel電子表格、包含位圖的圖像、圖形交換格式（gif0文件和聯(lián)合圖形專家組（JPEG）文件。l Text類型。Text數(shù)據(jù)類型的列可用于儲存大于8kb的ASCII字符。例如，由于HTML文檔均由ASCII字符組成且一般長于8kb，所以用瀏覽器查看之前應(yīng)在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ù)類型需要相當(dāng)于非unicode數(shù)據(jù)類型兩倍的存儲空間。l Numeric與decimal類型。在SQL Server中，numeric數(shù)據(jù)類型等價(jià)于decimal數(shù)據(jù)類型，如果數(shù)值超過貨幣數(shù)據(jù)范圍，則可使用decimal數(shù)據(jù)類型代替。l Timestamp類型。Timestamp用于表示Sql Server在一行上的活動順序，按二進(jìn)制格式以遞增的數(shù)字來表示。當(dāng)表中的行發(fā)生變動時，用從DBTS的函數(shù)獲得的當(dāng)前數(shù)據(jù)庫的時間戳值來更新時間戳。Timestamp數(shù)據(jù)與插入或修改數(shù)據(jù)的日期和時間無關(guān)。若要自動記錄表中數(shù)據(jù)更改的時間，使用datetime或smalldatetime數(shù)據(jù)類型記錄時間或觸發(fā)器。l Uniqueidentifier類型。Uniqueidentifier以一個16位的十六進(jìn)制數(shù)表示全局唯一標(biāo)識符（GUID）。當(dāng)需要在多行中唯一標(biāo)識莫一行時可使用GUID，例如，可使用unique_identifiler數(shù)據(jù)類型定義一個客戶標(biāo)識代碼列，已編輯公司來自多個國家/地區(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ù)類型或用戶定義類型的名稱。類型名稱必須符合標(biāo)識符的規(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ù)庫中的表刪除后不能恢復(fù)。l 如果要刪除的表與其他的標(biāo)有依賴關(guān)系，則該表不能被刪除。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ù)庫的組成對象，在進(jìn)行創(chuàng)建表的操作之前，先要通過命令USE WebShop打開要操作的數(shù)據(jù)庫。l 用戶在選擇表和列名稱時不要使用SQL語言中的保留關(guān)鍵詞，如select、create和insert等。l 在這里沒有考慮表中的約束情況。2.修改表修改表的基本語句格式如下：Alter tableAlter column Add完整性約束Drop（1）添加列Alter table goods add g_Producer varchar(20)【提示】l 在alter table語句中使用add關(guān)鍵字增加列。l 不論表中是否已有數(shù)據(jù)，新增加的列一律為空值，且新增加的一列位于表結(jié)構(gòu)的末尾。（2）修改列Alter table goods alter column g_ProduceDate char（10）【提示】l 在alter table 語句使用alter column關(guān)鍵字修改的數(shù)據(jù)類型或?qū)挾取 在“對象資源管理器”中展開表節(jié)點(diǎn)中的指定表節(jié)點(diǎn)后再展開【列】節(jié)點(diǎn)可以查看指定表中列的信息。（3）刪除表用Alter table 語句刪除列,可用drop column關(guān)鍵字.【例子】Alter table goods drop column g_Produce【提示】l 在使用alter table時，每次只能添加或刪除一列。l 在添加列時，不需要帶關(guān)鍵字column；在刪除列時，在列名前要帶上關(guān)鍵字column，因?yàn)樵谀J(rèn)情況下，認(rèn)為是刪除約束。l 在添加列時，需要帶數(shù)據(jù)類型和長度；在刪除列時，不需要帶數(shù)據(jù)類型和長度，只需指定列名。l 如果在該列定義了約束，在修改列時會進(jìn)行限制，如果確實(shí)需要修改該列，線必須刪除該列上的約束，然后再進(jìn)行修改。（4）重命名表使用存儲過程sp_rename可以更改當(dāng)前數(shù)據(jù)庫中表的名稱，存儲過程sp_rename基本語句格式如下：Sp_rename當(dāng)前表名，【新表名】3.查看表Sp_help 【表名】4.刪除表Drop table如：drop table good4.4記錄操作4.4.1使用SSMS進(jìn)行記錄操作4.4.2使用T-SQL進(jìn)行記錄操作1.使用T-SQL插入記錄使用Insert into語句可以向表中添加記錄或者創(chuàng)建追加查詢,插入單個記錄的基本語句格式如下:Insert into ，Values (,)(1)插入所有列。Insert into goods values （020003，愛國者M(jìn)P3-1G，2，128,0.8,20，2007-08-01，pImage/020003.gif,熱點(diǎn),容量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，熱點(diǎn))【提示】l Insert 語句中的into可以省略。l 如果某些屬性列在表名后的列名表沒有出現(xiàn)，則新紀(jì)錄在這些列上將取空值。但是要注意的是，在表定義時說明了not null的屬性列不能去空值，否則系統(tǒng)會出現(xiàn)錯誤提示。l 如果沒有指明任何列名，則新插入的記錄必須在每個屬性列上均有值。l 字符數(shù)據(jù)類型必須“”將其引起來。l 常量的順序必須與指定的列名順序保持一致。2.用T-SOL修改記錄使用Update語句可以按照某個條件修改特定表中的字段值，其基本語句格式如下：UpdateSet =,=from where (1)修改單條記錄.Update goodsSet g_Status=熱點(diǎn)Where g_name=勁霸西服(2)修改多條記錄Update goods Set g_Image=Images/+g_ImageWhere g_Image is not null【提示】l 如果不指定條件，則會修改所有的記錄。l 如果加上條件is not null就可以保證對已有圖片的商品進(jìn)行修改。(3)修改所有記錄并指定多項(xiàng)修改.Update goodsSet g_discount=0.8，g_ProduceDate=2007-07-01【提示】l 如果要修改多列,則在set語句后用”,”分隔各修改子句.l 這類一般在進(jìn)行數(shù)據(jù)初始化時使用.l 修改記錄時可以通過約束和觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)完整性.3.使用T-SQL刪除記錄使用Delete語句可以刪除表中的記錄,其基本語句如下:DeleteFrom【where】Delete 語句的功能是從表中刪除滿足where子句條件的所有記錄。如果省略where子句，表示刪除表中全部記錄，但表的定義仍存在。也就是說，delete語句刪除的是表中的數(shù)據(jù)，而不是關(guān)于表的定義。（1）刪除指定記錄。Delete From goodsWhere g_ID=040002Delete操作也是一次只能操作一個表，因此同樣會遇到update操作中提到的數(shù)據(jù)不一致問題。比如商品號為040002商品被刪除后，包含該商品的訂單信息也應(yīng)同時刪除?！咎崾尽縧 如果是外鍵約束，則可以先將外鍵表中對應(yīng)的記錄刪除，然后再刪除主鍵表中的記錄。l 記錄刪除后不能被恢復(fù)。（2）刪除所有記錄。Delete From goods該語句使goods成為空表，它刪除了goods的所有記錄。刪除表中所有記錄也可以使用truncate table語句來完成。Truncate table goods【提示】l Delete刪除操作被當(dāng)做是系統(tǒng)事務(wù)，刪除操作可以被撤銷。l Truncate table 則不是，刪除操作不能被撤銷。4.5 SQL Sever 2005 中的數(shù)據(jù)庫完整性4.5.1數(shù)據(jù)庫完整性概述數(shù)據(jù)庫的完整性是指數(shù)據(jù)的準(zhǔn)確性和一致性。它是防止數(shù)據(jù)庫中存在不符合語義規(guī)定的數(shù)據(jù)和防止因錯誤信息的輸入輸出造成無效操作而提出的。數(shù)據(jù)完整性主要分為四類：實(shí)體完整性、域完整性、引用完整性和用戶定義完整性。在SQL Server 2005 中可以通過空值約束、默認(rèn)值定義、check約束、primary key約束、foreign key約束和unique約束等來實(shí)施數(shù)據(jù)庫完整性。1.實(shí)體完整性實(shí)體完整性規(guī)定表的每一行在表中是唯一的.實(shí)體表中定義的索引、unique約束、primary key 約束和identity屬性就是實(shí)體完整性的體現(xiàn)。2.域完整性域完整性是指數(shù)據(jù)庫表中的列必須滿足某種特定的數(shù)據(jù)類型或約束，其中約束又包括取值范圍和精度等規(guī)定。表中的check約束、foreign key約束、default定義、not null和規(guī)則都屬于域完整性的范疇。3.引用完整性引用完整性是指兩個表的主關(guān)鍵字和外關(guān)鍵字的數(shù)據(jù)應(yīng)對應(yīng)一致。它確保了有主關(guān)鍵字的表中對應(yīng)其他表的外關(guān)鍵字的行存在，即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義的數(shù)據(jù)庫中擴(kuò)散。引用完整性是創(chuàng)建在外關(guān)鍵字和主關(guān)鍵字之間或外關(guān)鍵字和唯一關(guān)鍵字之間的關(guān)系上的。引用完整性時，SQL Server將阻止用戶執(zhí)行下列操作。（1） 在主表中沒有關(guān)聯(lián)的記錄時，將記錄添加或更改到相關(guān)表中。（2） 更改主表中的值時，這會導(dǎo)致相關(guān)表中生成孤立記錄。（3） 從主表中刪除記錄，但仍存在與該記錄匹配的相關(guān)記錄。Foreign key和check約束都屬于引用完整性。4.用戶完整性用戶定義完