表的管理數(shù)據(jù)庫天津大學(xué).ppt

1,第5章表的管理,2,本章內(nèi)容,5.1SQLServer表概述5.2表的創(chuàng)建與維護(hù)5.3表中數(shù)據(jù)的維護(hù),3,5.1SQLServer表概述,數(shù)據(jù)類型SQLServer支持4種基本數(shù)據(jù)類型：字符和二進(jìn)制數(shù)據(jù)類型、日期時(shí)間數(shù)據(jù)類型、邏輯數(shù)據(jù)類型、數(shù)字?jǐn)?shù)據(jù)類型，用于各類數(shù)據(jù)值的存儲(chǔ)、檢索和解釋。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ó)語言，采用Unicode標(biāo)準(zhǔn)字符集。為此SQLServer提供多字節(jié)的字符數(shù)據(jù)類型：nchar(n)、nvarchar(n)和ntext。,4,5.1SQLServer表概述,(2)二進(jìn)制數(shù)據(jù)類型SQLServer二進(jìn)制數(shù)據(jù)類型用于存儲(chǔ)二進(jìn)制數(shù)或字符串。SQLServer的3種有效二進(jìn)制數(shù)據(jù)類型：binary數(shù)據(jù)類型varbinary(n)數(shù)據(jù)類型image數(shù)據(jù)類型,5,5.1SQLServer表概述,2日期時(shí)間數(shù)據(jù)類型日期時(shí)間數(shù)據(jù)類型用于存儲(chǔ)日期和時(shí)間數(shù)據(jù)。SQLServer支持兩種日期時(shí)間數(shù)據(jù)類型：datetime和smalldatetime。3邏輯數(shù)據(jù)類型SQLServer的邏輯數(shù)據(jù)類型為bit，適用于判斷真/假的場(chǎng)合，長(zhǎng)度為一個(gè)字節(jié)。,6,5.1SQLServer表概述,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ǔ)不同范圍的值。(2)浮點(diǎn)數(shù)據(jù)類型浮點(diǎn)數(shù)據(jù)用來存儲(chǔ)系統(tǒng)所能提供的最大精度保留的實(shí)數(shù)數(shù)據(jù)。,7,5.1SQLServer表概述,(3)精確數(shù)值數(shù)據(jù)類型精確數(shù)值數(shù)據(jù)類型用于存儲(chǔ)有小數(shù)點(diǎn)且小數(shù)點(diǎn)后位數(shù)確定的實(shí)數(shù)。格式：decimal(p,s)numeric(p,s)(4)貨幣數(shù)據(jù)類型除了decimal和numeric類型適用于貨幣數(shù)據(jù)的處理外，SQLServer還專門提供了兩種貨幣數(shù)據(jù)類型：money和smallmoney。,8,5.1SQLServer表概述,5其他數(shù)據(jù)類型除了以上4種基本數(shù)據(jù)類型，SQLServer還支持其他一些數(shù)據(jù)類型。(1)Timestamp：時(shí)間戳數(shù)據(jù)類型，用于自動(dòng)記錄插入或刪除操作的時(shí)間。注意：服務(wù)器不允許用戶指定時(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í)符)。,9,5.1SQLServer表概述,空值(NULL)空值不同于空白(空字符串)或數(shù)值零，通常表示未填寫、未知(Unknown)、不可用或?qū)⒃谝院筇砑拥臄?shù)據(jù)。默認(rèn)值是指表中數(shù)據(jù)的默認(rèn)取值，默認(rèn)值對(duì)象是數(shù)據(jù)庫的對(duì)象不依附于具體的表對(duì)象，即默認(rèn)值對(duì)象的作用范圍是整個(gè)數(shù)據(jù)庫。約束約束定義了關(guān)于列中允許值的規(guī)則，SQLServer通過限制列中數(shù)據(jù)、行中數(shù)據(jù)和表之間數(shù)據(jù)來保證數(shù)據(jù)的完整性。(1)非空值約束(NotNull)(2)默認(rèn)約束(Default)(3)惟一性約束(Unique）(4)主鍵約束(PrimaryKey，也稱主關(guān)鍵字約束)(5)外鍵約束(ForeignKey，也稱為外部關(guān)鍵字約束),10,5.2表的創(chuàng)建與維護(hù),使用SQLServer管理平臺(tái)對(duì)表進(jìn)行操作使用SQLServer管理平臺(tái)創(chuàng)建和修改表使用SQLServer管理平臺(tái)設(shè)計(jì)數(shù)據(jù)庫關(guān)系在SQLServer管理平臺(tái)中刪除表使用Transact-SQL語句創(chuàng)建表語法格式：CREATETABLEdatabase_name.owner.|owner.table_name(|column_nameAScomputed_column_expression|:=CONSTRAINTconstraint_name|PRIMARYKEY|UNIQUE,.n)ONfilegroup|DEFAULTTEXTIMAGE_ONfilegroup|DEFAULT,11,5.2表的創(chuàng)建與維護(hù),例5-1簡(jiǎn)單的表定義。USESalesGOCREATETABLEemployee(employee_idchar(4)NOTNULL,employee_namechar(8)NOTNULL,sexchar(2)NOTNULL,birth_datedatetimeNOTNULL,hire_datedatetimeNOTNULL,addressvarchar(50),telephonechar(8),wagesmoney,department_idchar(4)NOTNULL,resumetextNOTNULL),12,5.2表的創(chuàng)建與維護(hù),例5-2為表指定文件組。CREATETABLESales.dbo.supplier(supplier_idchar(5)NOTNULL,supplier_namevarchar(50)NOTNULL,linkman_namevarchar(8),addressvarchar(50),telephonechar(12)NOTNULL)ONPRIMARY,13,5.2表的創(chuàng)建與維護(hù),例5-3對(duì)計(jì)算列使用表達(dá)式。CREATETABLEsalarys(姓名varchar(10),基本工資money,獎(jiǎng)金money,總計(jì)AS基本工資+獎(jiǎng)金),14,5.2表的創(chuàng)建與維護(hù),例5-4定義表autouser自動(dòng)獲取用戶名稱。CREATETABLEautouser(編號(hào)intidentity(1,1)NOTNULL,用戶代碼varchar(18),登錄時(shí)間ASGetdate(),用戶名ASUser_name(),15,5.2表的創(chuàng)建與維護(hù),例5-5創(chuàng)建臨時(shí)表。CREATETABLE#students(學(xué)號(hào)varchar(8),姓名varchar(10),性別varchar(2),班級(jí)varchar(10),16,5.2表的創(chuàng)建與維護(hù),使用Transact-SQL語句修改表語法格式：ALTERTABLEtable_nameALTERCOLUMNcolumn_namenew_data_type(precision,scale)NULL|NOTNULL|ADD|DROPROWGUIDCOL|ADD|column_nameAScomputed_column_expression,.n|WITHCHECK|WITHNOCHECKADD,.n|DROPCONSTRAINTconstraint_name|COLUMNcolumn_name,.n|CHECK|NOCHECKCONSTRAINTALL|constraint_name,.n|ENABLE|DISABLETRIGGERALL|trigger_name,.n,17,5.2表的創(chuàng)建與維護(hù),例5-6更改表以添加新列，然后再刪除該列。ALTERTABLEemployeeADDemailvarchar(20)NULLGOsp_helpemployeeALTERTABLEemployeeDROPCOLUMNemailGOsp_helpemployee,18,5.2表的創(chuàng)建與維護(hù),例5-7將表employee的列address改為varchar(150)數(shù)據(jù)類型，并且不允許為空。ALTERTABLEemployeeALTERCOLUMNaddressvarchar(150)NOTNULLGO注意：一定要確認(rèn)已有的數(shù)據(jù)中列address均不為空后，才能進(jìn)行此操作。,19,5.2表的創(chuàng)建與維護(hù),使用Transact-SQL語句刪除表語句格式如下：DROPTABLEtable_name其中，table_name是要?jiǎng)h除的表名。注意：(1)定義有外鍵約束的表必須先刪除外鍵約束，才能刪除。(2)系統(tǒng)表不能使用DROPTABLE語句刪除。,20,5.2表的創(chuàng)建與維護(hù),例5-8刪除當(dāng)前數(shù)據(jù)庫內(nèi)的表。USESalesGODROPTABLEemployee本例從當(dāng)前數(shù)據(jù)庫Sales中刪除employee表及其數(shù)據(jù)和索引。例5-9刪除另外一個(gè)數(shù)據(jù)庫內(nèi)的表。DROPTABLESales.dbo.employee本例刪除Sales數(shù)據(jù)庫內(nèi)的employee表?？梢栽谌魏螖?shù)據(jù)庫內(nèi)執(zhí)行此操作。,21,5.3表中數(shù)據(jù)的維護(hù),對(duì)表中數(shù)據(jù)進(jìn)行維護(hù)有兩種方法：使用SQLServer管理平臺(tái)使用Transact-SQL語句插入數(shù)據(jù)語法格式如下：INSERTINTOtable_name|view_name(column_list)VALUES(value_list)|derived_table|execute_statement|DEFAULTVALUES,22,5.3表中數(shù)據(jù)的維護(hù),例5-10使用簡(jiǎn)單的INSERT語句。USESalesGOINSERTSupplierVALUES(S001,華科電子有限公司,施賓彬,朝陽路56號(hào),2636565)例5-11顯式指定列列表。INSERTSales.dbo.Supplier(supplier_id,supplier_name,linkman_name,address,telephone)VALUES(S001,華科電子有限公司,施賓彬,朝陽路56號(hào),2636565),23,5.3表中數(shù)據(jù)的維護(hù),修改數(shù)據(jù)語法格式：UPDATEtable_name|view_nameSETcolumn_name|variable=expression,.nFROM,.nWHERE,24,5.3表中數(shù)據(jù)的維護(hù),例5-14使用簡(jiǎn)單的UPDATE語句。UPDATECustomer2SETlinkman_name=佚名,address=NULL,telephone=例5-15在UPDATE語句中使用WHERE子句。UPDATECustomer2SETtelephone=0731-+telephoneWHERELEN(telephone)=7,25,5.3表中數(shù)據(jù)的維護(hù),例5-17在UPDATE語句中使用SELECTTOP語句。UPDATEGoodsSETUnit_Price=Unit_Price*0.9FROMGoods,(SELECTTOP10Goods_ID,SUM(Order_Num)ASTotal_NumFROMSell_OrderGROUPBYGoods_IDORDERBYTotal_NumDESC)ASTotal_SumWHEREGoods.Goods_id=Total_Sum.Goods_id,26,5.3表中數(shù)據(jù)的維護(hù),刪除數(shù)據(jù)1DELETE語句DELETE語句可刪除表或視圖中的一行或多行，每一行的刪除都將被記入日志。DELETE語句的語法格式如下：DELETEFROMtable_name|view_nameFROM,.nWHERE<sea,27,5.3表中數(shù)據(jù)的維護(hù),例5-18不帶參數(shù)使用DELETE命令刪除所有行。USESalesGODELETECustomer2本例從Customer2表中刪除所有行。注意：將DELETE語句與DROPTABLE語句的功能區(qū)分開來。例5-19帶WHERE子句的DELETE語句，有條件地刪除行。DELETEFROMSell_OrderWHERECustom_ID=C0003,28,5.3表中數(shù)據(jù)的維護(hù),例5-20在DELETE中使用聯(lián)接或子查詢。-基于Transact-SQL，使用表的聯(lián)接DELETESell_OrderFROMSell_OrderSOINNERJOINcustomer2CONSO.customer_id=C.customer_idWHEREC.addressLIKE東方市%-等同于下列命令DELETESell_OrderFROMSell_OrderSO,customer2CWHERESO.customer_id=C.customer_idANDC.addressLIKE東方市%-基于SQL-92標(biāo)準(zhǔn)，須使用子查詢DELETEFROMSell_OrderWHEREcustomer_idIN(SELECTcustomer_idFROMcustomer2WHEREaddressLIKE東方市%),29,5.3表中數(shù)據(jù)的維護(hù),2TRUNCATETABLE語句語法格式：TRUNCATETABLEtable_name其中table_name是要清空的表的名稱。例5-21使用TRUNCATETABLE語句清空表。TRUNCATETABLEcustomer2,30,本章小結(jié),（1）表的相關(guān)概念：表是數(shù)據(jù)庫中數(shù)據(jù)的實(shí)際存儲(chǔ)處所，每個(gè)表代表一個(gè)實(shí)體。表由行和列組成，每行標(biāo)識(shí)實(shí)體的一個(gè)個(gè)體，每列代表實(shí)體的一個(gè)屬性。（2）數(shù)據(jù)類型：數(shù)據(jù)類型描述并約束了列中所能包含的數(shù)據(jù)的種類、所存儲(chǔ)值的長(zhǎng)度或大小、數(shù)字精度和小數(shù)位數(shù)（對(duì)數(shù)值數(shù)據(jù)類型）。（3）空值：未對(duì)列指定值時(shí)，該列將出現(xiàn)空值?？罩挡煌诳兆址驍?shù)值零，通常表示未知?？罩禃?huì)對(duì)查詢命令或統(tǒng)計(jì)函數(shù)產(chǎn)生影響，應(yīng)盡量少使用空值。（4）約束：約束是數(shù)據(jù)庫自動(dòng)保持?jǐn)?shù)據(jù)完整性的機(jī)制，它是通過限制列中數(shù)據(jù)、行中數(shù)據(jù)和表之間數(shù)據(jù)來保持?jǐn)?shù)據(jù)完整性。SQLServer2005支持NotNull、Default、Check、PrimaryKey、ForeignKey、Unique6種約束。關(guān)于約束的操作將在第8章詳細(xì)介紹。（5）可以使用SQLServer管理平臺(tái)和Transact-SQL語句創(chuàng)建表并對(duì)表進(jìn)行維護(hù)，包括修改和刪除等操作。（6）可以使用SQLServer管理平臺(tái)和Transact-SQL語句對(duì)表中數(shù)據(jù)進(jìn)行編輯，包括插入、更新和刪除等操作。,