第5章表的管理

第5章表的管理本章內(nèi)容5.1SQLServer表概述5.2表的創(chuàng)建與維護(hù)5.3表中數(shù)據(jù)的維護(hù)5.1SQLServer表概述在SQL中,一個(gè)數(shù)據(jù)庫(kù)最多可以存儲(chǔ)20億個(gè)表。表是數(shù)據(jù)庫(kù)的實(shí)體,由行和列組成。每行最多可存儲(chǔ)8092字節(jié)的內(nèi)容,每個(gè)表最多可以有1024列。設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí),需要考慮的問題：共有哪些表？每個(gè)表由哪些列組成？每列的數(shù)據(jù)類型及列寬？主鍵？約束與規(guī)則等？5.1SQLServer表概述因此,本章的任務(wù)就是建立表、管理表。建立表數(shù)據(jù)類型約束表的定義(Create、Drop、Alter)管理表插入數(shù)據(jù)(Insert)修改數(shù)據(jù)(Update)刪除數(shù)據(jù)(Delete)5.1.1

數(shù)據(jù)類型簡(jiǎn)介

數(shù)據(jù)類型SQLServer支持4種基本數(shù)據(jù)類型：字符和二進(jìn)制數(shù)據(jù)類型、日期時(shí)間數(shù)據(jù)類型、邏輯數(shù)據(jù)類型、數(shù)字?jǐn)?shù)據(jù)類型,用于各類數(shù)據(jù)值的存儲(chǔ)、檢索和解釋。

5.1.1數(shù)據(jù)類型簡(jiǎn)介1、字符和二進(jìn)制數(shù)據(jù)類型(1)字符數(shù)據(jù)類型字符數(shù)據(jù)類型用于存儲(chǔ)漢字、英文字母、數(shù)字符號(hào)和其他各種符號(hào)。char數(shù)據(jù)類型varchar數(shù)據(jù)類型text數(shù)據(jù)類型SQLServer允許使用多國(guó)語(yǔ)言,采用Unicode標(biāo)準(zhǔn)字符集。為此SQLServer提供多字節(jié)的字符數(shù)據(jù)類型：nchar(n)、nvarchar(n)和ntext。nchar,char,varchar與nvarchar的區(qū)別char是以固定長(zhǎng)度的,使用時(shí)往往要用trim()函數(shù)把兩邊的空格去掉Varchar存儲(chǔ)變長(zhǎng)數(shù)據(jù),但存儲(chǔ)效率沒有Char高

Nchar類型和Nvarchar,為了與其他多種字符的轉(zhuǎn)換,如中文、音標(biāo)等。字節(jié)的存儲(chǔ)大小是所輸入字符個(gè)數(shù)的兩倍,就是說它是雙字節(jié)來存儲(chǔ)數(shù)據(jù)的char、varchar一般適用于英文和數(shù)字;Nchar、Nvarchar適用中文和其他字符,其中N表示Unicode常量,可以解決多語(yǔ)言字符集之間的轉(zhuǎn)換問題nchar,char,varchar與nvarchar的區(qū)別問題：namechar(10)、namenchar(10)分別能存放多少個(gè)漢字？為什么？解答：namechar(10)、namenchar(10)分別能存放5個(gè)10個(gè)漢字原因：char與varchar以單字節(jié)形式存放；nchar與nvarchar以雙字節(jié)形式存放；幾種字符類型的最大長(zhǎng)度Char、Varchar—最多可以存儲(chǔ)8000個(gè)字符Nchar、Nvarchar—最多可以存儲(chǔ)4000個(gè)字符text、ntext—最多可以存儲(chǔ)230-1個(gè)字符5.1.1數(shù)據(jù)類型簡(jiǎn)介(2)二進(jìn)制數(shù)據(jù)類型SQLServer二進(jìn)制數(shù)據(jù)類型用于存儲(chǔ)二進(jìn)制數(shù)或字符串。

SQLServer的3種有效二進(jìn)制數(shù)據(jù)類型：binary[(n)]數(shù)據(jù)類型:默認(rèn)n=1,存放空間n+4個(gè)字節(jié),取值1～8000varbinary[(n)]數(shù)據(jù)類型:基本同上,但空間足夠時(shí)取實(shí)際長(zhǎng)度image數(shù)據(jù)類型:與next類型類似,可存1～231-1字節(jié)的二進(jìn)制。通常用來存放word文檔、Excel表格、圖像等。5.1.1數(shù)據(jù)類型簡(jiǎn)介2、日期時(shí)間數(shù)據(jù)類型日期時(shí)間數(shù)據(jù)類型用于存儲(chǔ)日期和時(shí)間數(shù)據(jù)。SQLServer支持兩種日期時(shí)間數(shù)據(jù)類型：datetime和smalldatetime。Datetime:1753-1-1～9999-12-31Smalldatetime:1900-1-1～2079-6-65.1.1數(shù)據(jù)類型簡(jiǎn)介3、邏輯數(shù)據(jù)類型SQLServer的邏輯數(shù)據(jù)類型為bit,也稱為位數(shù)據(jù)類型。適用于判斷真/假的場(chǎng)合,長(zhǎng)度為一個(gè)字節(jié)。取值：1、0、NULL注意事項(xiàng)：非0當(dāng)1處理；位列不允許建索引；如果一個(gè)表不多于8個(gè)位列,合起來用一個(gè)字節(jié)存儲(chǔ)。5.1.1數(shù)據(jù)類型簡(jiǎn)介4、數(shù)字?jǐn)?shù)據(jù)類型SQLServer提供了多種方法存儲(chǔ)數(shù)值,SQLServer的數(shù)字類型大致可分為4種基本類型。(1)整數(shù)數(shù)據(jù)類型有4種整數(shù)數(shù)據(jù)類型：int、smallint、tinyint和bigint,用于存儲(chǔ)不同范圍的值。

int---存儲(chǔ)長(zhǎng)度4字節(jié)

smallint---存儲(chǔ)長(zhǎng)度2字節(jié)tinyint---存儲(chǔ)長(zhǎng)度1字節(jié)

bigint---存儲(chǔ)長(zhǎng)度8字節(jié)只要有可能，列盡量取整型5.1.1數(shù)據(jù)類型簡(jiǎn)介4、數(shù)字?jǐn)?shù)據(jù)類型(1)整數(shù)數(shù)據(jù)類型(2)浮點(diǎn)數(shù)據(jù)類型浮點(diǎn)數(shù)據(jù)用來存儲(chǔ)系統(tǒng)所能提供的最大精度保留的實(shí)數(shù)數(shù)據(jù)

。由于近似的運(yùn)算存在誤差，因此不能用于需要固定精度的運(yùn)算，如貨幣。

float(n)----可精確到第15位小數(shù),占8個(gè)字節(jié)的存儲(chǔ)空間。n為指定的精度

real(n)----可精確到第7位小數(shù),占4個(gè)字節(jié)的存儲(chǔ)空間5.1.1數(shù)據(jù)類型簡(jiǎn)介(3)精確數(shù)值數(shù)據(jù)類型精確數(shù)值數(shù)據(jù)類型用于存儲(chǔ)有小數(shù)點(diǎn)且小數(shù)點(diǎn)后位數(shù)確定的實(shí)數(shù)

。格式：decimal[(p[,s])]numeric[(p[,s])]其中,p指定精度,即小數(shù)點(diǎn)左邊和右邊可以存儲(chǔ)的十進(jìn)制數(shù)字的最大個(gè)數(shù);s指定小數(shù)位數(shù)。例如:decimal[(5[,3])],輸入87.341結(jié)果如何？5.1.1數(shù)據(jù)類型簡(jiǎn)介(4)貨幣數(shù)據(jù)類型除了decimal和numeric類型適用于貨幣數(shù)據(jù)的處理外,SQLServer還專門提供了兩種貨幣數(shù)據(jù)類型：money和smallmoney。顯示貨幣數(shù)據(jù)值時(shí),小數(shù)部分僅保留2位有效數(shù)字。5.1.1數(shù)據(jù)類型簡(jiǎn)介5、其他數(shù)據(jù)類型除了以上4種基本數(shù)據(jù)類型,SQLServer還支持其他一些數(shù)據(jù)類型。(1)Timestamp：時(shí)間戳數(shù)據(jù)類型,用于自動(dòng)記錄插入或刪除操作的時(shí)間。(2)sql_variant：可變數(shù)據(jù)類型。該類型的變量可用來存放大部分SQLServer數(shù)據(jù)類型的值,最大長(zhǎng)度為8016字節(jié),不支持text、ntext、timestamp和sql_variant類型。(3)table：表類型。這是一種特殊的數(shù)據(jù)類型,存儲(chǔ)供以后處理的結(jié)果集。(4)uniqueidentifier：GUID類型(GlobalUniqueIDentifier,全局惟一標(biāo)識(shí)符)。

5.1.2空值和默認(rèn)值空值(NULL):空值不同于空白(空字符串)或數(shù)值零,通常表示未填寫、未知(Unknown)、不可用或?qū)⒃谝院筇砑拥臄?shù)據(jù)。

默認(rèn)值:是指表中數(shù)據(jù)的默認(rèn)取值,默認(rèn)值對(duì)象是數(shù)據(jù)庫(kù)的對(duì)象不依附于具體的表對(duì)象,即默認(rèn)值對(duì)象的作用范圍是整個(gè)數(shù)據(jù)庫(kù)。約束(1)非空值約束(NotNull)(2)默認(rèn)約束(Default)(3)惟一性約束(Unique）(4)主鍵約束(PrimaryKey,也稱主關(guān)鍵字約束)(5)外鍵約束(ForeignKey,也稱為外部關(guān)鍵字約束)5.2表的創(chuàng)建與維護(hù)使用SQLServer的管理平臺(tái)對(duì)表進(jìn)行操作5.2表的創(chuàng)建與維護(hù)創(chuàng)建表CREATETABLE表名修改表

ALTERTABLE表名刪除表

DROPTABLE表名5.2表的創(chuàng)建與維護(hù)例5-3對(duì)計(jì)算列使用表達(dá)式。CREATETABLEsalarys(姓名varchar(10),基本工資money,獎(jiǎng)金money,總計(jì)AS基本工資+獎(jiǎng)金)5.2表的創(chuàng)建與維護(hù)例5-4定義表autouser自動(dòng)獲取用戶名稱。CREATETABLEautouser(編號(hào)intidentity(1,1)NOTNULL,用戶代碼varchar(18),登錄時(shí)間ASGetdate(),用戶名ASUser_name())5.2表的創(chuàng)建與維護(hù)例5-5創(chuàng)建臨時(shí)表。CREATETABLE#students(學(xué)號(hào)varchar(8),姓名varchar(10),性別varchar(2),班級(jí)varchar(10))臨時(shí)表用來暫存數(shù)據(jù),#表示本地臨時(shí)表,在當(dāng)前數(shù)據(jù)庫(kù)內(nèi)使用;##表示全局臨時(shí)表,可在所有數(shù)據(jù)庫(kù)在使用5.3表中數(shù)據(jù)的維護(hù)對(duì)表中數(shù)據(jù)進(jìn)行維護(hù)有兩種方法：使用SQLServer管理平臺(tái)使用Transact-SQL語(yǔ)句(必須熟練掌握)

Insert、Select、Update、Delete表中數(shù)據(jù)的維護(hù):求是指維護(hù)表中的數(shù)據(jù)。4種基本操作:添加或插入新數(shù)據(jù)檢索現(xiàn)有數(shù)據(jù)更新現(xiàn)有數(shù)據(jù)刪除現(xiàn)有數(shù)據(jù)5.3表中數(shù)據(jù)的維護(hù)對(duì)表中數(shù)據(jù)進(jìn)行維護(hù)方法之一使用SQLServer管理平臺(tái)

實(shí)際操作——在數(shù)據(jù)庫(kù)中打開表直接實(shí)現(xiàn)插入、修改、刪除問題1:使用SQLServer管理平臺(tái)可以實(shí)現(xiàn)少量的插入,但很難實(shí)現(xiàn)批量的插入、有條件的大量刪除與修改

問題2:數(shù)據(jù)庫(kù)中的數(shù)據(jù)是交給用戶方進(jìn)行管理的,面對(duì)用戶的是用戶與數(shù)據(jù)庫(kù)操作的接口。也就是不能在SQLServer管理平臺(tái)上操作，如何實(shí)現(xiàn)？

——應(yīng)用程序開發(fā)者用SQL語(yǔ)句實(shí)現(xiàn)5.3.1插入數(shù)據(jù)插入數(shù)據(jù)

語(yǔ)法格式如下：INSERT[INTO]{table_name|view_name}{[(column_list)]{VALUES(value_list)|derived_table|execute_statement}}|DEFAULTVALUES5.3.1插入數(shù)據(jù)注意：Insert…Values…一次只能插入一行如果插入的數(shù)據(jù)違反約束或規(guī)則，則插入會(huì)失敗插入的值可以是指定的值集,也可以是Select語(yǔ)句的執(zhí)行得到的一行(column_list)與(value_list)中順序必須一致(value_list)中的值必須是常量、變量或表達(dá)表,表達(dá)式中不能包含Select或Execute語(yǔ)句5.3.1插入數(shù)據(jù)例5-10使用簡(jiǎn)單的INSERT語(yǔ)句。USESalesGO

INSERTSupplier

VALUES

('S001','華科電子有限公司','施賓彬','朝陽(yáng)路56號(hào)','2636565')例5-11顯式指定列列表。

INSERTSales.dbo.Supplier(supplier_id,supplier_name,linkman_name,address,telephone)VALUES('S001','華科電子有限公司','施賓彬','朝陽(yáng)路56號(hào)','2636565')5.3.1插入數(shù)據(jù)INSERTSales.dbo.Supplier(Supplier_id,Supplier_name,telephone)VALUES('S003','韋力電子實(shí)業(yè)公司','4561681')INSERTSales.dbo.Supplier(Supplier_name,telephone,Supplier_id)VALUES('韋力電子實(shí)業(yè)公司','4561681','S003')課堂練習(xí)(1)將一個(gè)新學(xué)生元組(學(xué)號(hào)：200215128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲)插入到Student表中。INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('200215128','陳冬','男','IS',18)；課堂練習(xí)(2)將學(xué)生張成民的信息插入到Student表中。INSERTINTOStudentVALUES('200215126','張成民','男',18，'CS')(3)插入一條選課記錄('200215128‘,'1')。INSERTINTOSC(Sno，Cno)

VALUES('200215128','1')INSERTINTOSCVALUES('200215128','1',NULL)5.3.2修改數(shù)據(jù)修改數(shù)據(jù)語(yǔ)法格式：UPDATE{table_name|view_name}

SET{{column_name|@variable}=expression}[,…n][FROM{<table_source>}[,…n]][WHERE<search_condition>]5.3.2修改數(shù)據(jù)例5-14使用簡(jiǎn)單的UPDATE語(yǔ)句。UPDATECustomer2SETlinkman_name='佚名',address=NULL,telephone='

'例5-15在UPDATE語(yǔ)句中使用WHERE子句。UPDATECustomer2SET

telephone='0731-'+telephoneWHERELEN(telephone)=75.3.2修改數(shù)據(jù)例5-16在UPDATE語(yǔ)句中使用來自另一個(gè)表的信息UPDATESell_OrderSETcost=

Sell_Order.Order_Num*Goods.Unit_Price*(1-Sell_Order.Discount)FROMSell_Order,GoodsWHERESell_Order.Goods_id=Goods.Goods_id5.3.2修改數(shù)據(jù)例5-16在UPDATE語(yǔ)句中使用來自另一個(gè)表的信息UPDATESell_OrderSETcost=SO.Order_Num*G.Unit_Price*(1-SO。Discount)FROMSell_OrderSO,GoodsGWHERESO.Goods_id=G.Goods_id5.3.2修改數(shù)據(jù)例5-17在UPDATE語(yǔ)句中使用SELECT…TOP語(yǔ)句。UPDATEGoodsSETUnit_Price=Unit_Price*0.9FROMGoods,(SELECTTOP10Goods_ID,SUM(Order_Num)ASTotal_NumFROMSell_OrderGROUPBYGoods_IDORDERBYTotal_NumDESC)ASTotal_SumWHEREGoods.Goods_id=Total_Sum.Goods_id課堂練習(xí)(1)將學(xué)生200215121的年齡改為22歲UPDATEStudentSETSage=22WHERESno='200215121'；(2)將所有學(xué)生的年齡增加1歲UPDATEStudentSETSage=Sage+1；5.3.3刪除數(shù)據(jù)刪除數(shù)據(jù)

1、DELETE語(yǔ)句DELETE語(yǔ)句可刪除表或視圖中的一行或多行,每一行的刪除都將被記入日志。DELETE語(yǔ)句的語(yǔ)法格式如下：DELETE[FROM]{table_name|view_name}[FROM{<table_source>}[,…n]][WHERE{<search_condition>}]5.3.3刪除數(shù)據(jù)例5-18不帶參數(shù)使用DELETE命令刪除所有行。USESalesGODELETECustomer2本例從Customer2表中刪除所有行。例5-19帶WHERE子句的DELETE語(yǔ)句,有條件地刪除行。DELETEFROMSell_Order

WHERECustom_ID='C0003'5.3.3刪除數(shù)據(jù)例5-20在DELETE中使用聯(lián)接或子查詢。DELETESell_OrderFROMSell_OrderSO

INNERJOINcustomer2C

ONSO.customer_id=C.customer_idWHEREC.addressLIKE'東方市%'--等同于下列命令DELETE

Sell_OrderFROM

Sell_OrderSO,customer2C

WHERE

SO.customer_id=C.customer_idANDC.addressLIKE'東方市%'課堂練習(xí)(1)刪除學(xué)號(hào)為200215128的學(xué)生記錄(2)刪除所有的學(xué)生選課記錄DELETEFROMStudentWHERESno='200215128'DELETEFROMSC課堂練習(xí)(3)刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。DELETEFROMSCWHERE

Sno=

(SELETESnoFROMStudentWHERESdept=‘計(jì)算機(jī)科學(xué)系’)課堂練習(xí)(3)刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。DELET