第7章表數(shù)據(jù)操作

第7章數(shù)據(jù)表和表數(shù)據(jù)7.1表的創(chuàng)建、修改和刪除一、SQLserver支持的數(shù)據(jù)類型

1）整數(shù)型：bigint,int,smallint,tinyint2)精確數(shù)值型：整數(shù)部分+小數(shù)部分，分為：decimal和numeric兩類

3）浮點型:foat和real兩類

4）貨幣型：money和smallmoney5)位型：相當于其他語言中的邏輯型數(shù)據(jù)，只存儲0和16)字符型：varchar和char7)unicode字符型8）文本型：text和ntext9）二進制型10）日期時間類型：datetime和smalldatetime2.創(chuàng)建表首先要設(shè)計表（1）空值(null)概念空值表示通常表示未知、不可用或?qū)⒁院筇砑拥臄?shù)據(jù)。表的關(guān)鍵字不允許為空值。（2）學生情況表的創(chuàng)建表結(jié)構(gòu)二、表表是SQLserver中最主要的數(shù)據(jù)庫對象，它是用來存儲和操作數(shù)據(jù)的一種邏輯結(jié)構(gòu)，由行和列組成。

1表結(jié)構(gòu)組成表的各列的名稱及數(shù)據(jù)類型

2記錄表中的一行

3字段構(gòu)成記錄的每個數(shù)據(jù)項

4關(guān)鍵字表中記錄的某一字段或字段組合能唯一表示記錄，該字段或字段組合成為關(guān)鍵字。通過管理器創(chuàng)建表XS的操作步驟

三、使用CREATETABLE創(chuàng)建表示例：設(shè)已經(jīng)創(chuàng)建數(shù)據(jù)庫XSCJ，現(xiàn)在該數(shù)據(jù)庫中需創(chuàng)建學生情況表XS，

Kc(kch,kcm)xs_kc(xh,kch,chj)Xs(xh,xm,zhy,xb,chsh,zxf,bzh)

usexscjcreatetablexs(學號char(6)notnull,

姓名char(8)notnull,

專業(yè)名char(10)null,

性別char(2)default‘男’notnull,

出生時間smalldatetimenotnull,

總學分tinyintnull,

備注textnull)goCreatetable語句的語法：

createtable表名（{column_namedatatype|identity|notnull|null}）例：Createtablecard(卡號decimal(6,0)identity(10000,1)

姓名char(8)notnull,

專業(yè)名char（10）null)

createtable完整的語法結(jié)構(gòu)：

createtable[database_name.[schema_name]|[schema_name.]table_name(<column_definition>|column_nameascomputed_column_expression|<table_constraint>)xscj..xs四、使用ALTERTABLE修改表Altertable語句的基本語法結(jié)構(gòu)

ALTERTABLEtable_name{[altercolumncolumn_name{new_datatime_type[(precision[,scale])][notnull|null]]}|add{[<column_definition>]}[,…n]|drop{[constraint]constraint_name|columncolumn_name}[,…n]1修改已有列的屬性：ALTERCOLUMN列名2向表中增加新列：ADD3刪除表中列：DROPCOLUMN例在表XS中增加一個新列—獎學金等級列

usexscjaltertablexsadd

獎學金等級tinyintnullgo例：在XS表中刪除名為獎學金等級的列

usexscjaltertablexsdropcolumn獎學金等級

go例：修改表XS中已有列的屬性：將名為“姓名”的列長度由原來的8該為10，將“出生時間”的列的數(shù)據(jù)類型由原來的smalldatetime改為datetime.usexscjaltertablexsaltercolumn姓名char(10)altertablexsaltercolumn出生時間datetimego六、使用DROPTABLE刪除表

droptable表名（3）列的identity屬性對任何表都可創(chuàng)建包含系統(tǒng)所生成序號值的一個標識列，該序號值唯一標識表中的一行，可以作為鍵值。每個表只能有一列作為標識列，int,numeric,smallint,biginORtinyint,定義標識列可指定其種子（起始）值和增量值，建立數(shù)據(jù)庫library,在其中創(chuàng)建card表，表結(jié)構(gòu)如所示3修改表通過管理器和T-SQL語言兩種方法來進行：更改表名，增加列，刪除列，修改已有列的屬性4刪除表創(chuàng)建數(shù)據(jù)庫和表以后，需要對表中的數(shù)據(jù)進行操作，包括插入、刪除和修改。有兩種方法：一是通過SQLServer2005管理器，一是通過T-SQL語句。7.2界面操作表數(shù)據(jù)一、插入記錄添加記錄在表尾二、刪除記錄三、修改記錄7.3命令操作表數(shù)據(jù)（T-SQL語句）一、通過INSERT語句插入表數(shù)據(jù)向表中插入數(shù)據(jù)可以使用INSERT語句。INSERT最基本的格式為：

INSERT[into]表名(column1,column2,…)VALUES（constant1,constant2,…）該語句的功能是向指定的表中加入由VALUES指定的各列值的行例：向XSCJ數(shù)據(jù)庫的表XS中插入一行：

001112羅林林計算機01/30/19800:0:040可以使用如下的T-SQL語句：

USExscjINSERTINTOXSCJ.dbo.XS()VALUES(‘001112’,’羅林林’,‘計算機’,0,‘1/30/19800:0:0’,40,null)Go

例：用如下語句建立表testCREATETABLEtest(xmchar(20)notnull,zyvarchar(30)default(‘計算機’),njtinyintnotnull)用INSERT語句向表中插入一條記錄：

INSERTINTOtest(xm,nj)values(‘

王林’,3)例：創(chuàng)建表XS2CREATETABLEXS2(numchar(10)notnull,namechar(10)notnull,specialtychar(10)null)可用如下的INSERT語句向XS2中插入數(shù)據(jù)：Insertintoxs2selectxh,xm,zhyfromxs1Wherezhy=‘生工’查詢結(jié)果：select*fromxs2二、使用DELETE或TRUNCATETABLE語句刪除數(shù)據(jù)1.使用delete語句刪除數(shù)據(jù)

delete語句的功能是從表中刪除行，其基本語法格式為：

delete[from]{table_name?view_name}[where<search_condition>]Example:將XSCJ數(shù)據(jù)庫的表XS中總學分小于39的行刪除：

USEXSCJDELETEFROMXSWHERE總學分<39go2.使用TRUNCATETABLE語句刪除表數(shù)據(jù)使用該語句將刪除指定表中的所有數(shù)據(jù)，語法格式為：

TRuNCATETABLEname

3.使用UPDATE語句修改數(shù)據(jù)UPDATE語句可以用來修改表中的數(shù)據(jù)行，其最基本語法格式如下：UPDATE{table_name?view_name}SETcolumn_name={expression?DEFAULT?NULL}[,…n][WHERE<search_condition>]例：將XSCJ數(shù)據(jù)庫的XS表中學號為001110的學生的備注列值改為“三好生”：

USEXSCJUPDATEXSSET備注=‘三好生’

WHERE 學號=‘001110’GO例將XS表中的所有學生的總學分都增加10個學分。

USEXSCJUPDATEXSSET總學分=總學分+10例將姓名為“王林”的同學的專業(yè)改為“材料工程”，備注改為“轉(zhuǎn)專業(yè)學習”，學號改為001230UsexscjUpdatexssetxh=‘001230’,zhy=‘材料工程’,bzh=‘轉(zhuǎn)專業(yè)學習’

wherexm=‘王林’

7.4數(shù)據(jù)完整性和約束7.4.1數(shù)據(jù)完整性的分類數(shù)據(jù)完整性是指數(shù)據(jù)庫中的數(shù)據(jù)在邏輯上的一致性和準確性.一般包括四種.1域完整性又稱列完整性,指給定列的輸入的有效性.可以通過定義相應(yīng)的規(guī)則、約束、默認值對象等方法實現(xiàn)。2實體完整性又稱行的完整性，要求表中有一個鍵，其值不能為空且能唯一地表示對應(yīng)的實體。通過索引、UNIQUE約束primarykey約束或identity屬性可實現(xiàn)數(shù)據(jù)的完整性.3參照完整性參照完整性又稱引用完整性.參照完整性保證主表(被參照表)中的數(shù)據(jù)與從表(參照表)中數(shù)據(jù)的一致性.通過定義外鍵(外碼)與主鍵(主碼)之間或外鍵與唯一鍵之間的對應(yīng)關(guān)系來實現(xiàn)參照完整性.參照完整性確保鍵值在所有表中一致.Xs(xh,xm,nl,zhy),kc(kch,kcm),cj(xh,kch,cj)4.用戶定義完整性7.4.2約束的類型1.check約束2.default約束3.primarykey約束4.foreignkey約束5.unique約束7.4.3完整性的實現(xiàn)一、域完整性的實現(xiàn)可以通過定義約束和規(guī)則等來實現(xiàn)域完整性

1.check約束的定義與刪除

check約束是字段輸入內(nèi)容的驗證規(guī)則,表示一個字段的輸入內(nèi)容必須滿足check約束的條件,若不滿足,則數(shù)據(jù)無法正常輸入.1)通過SQLServerManangementStudio創(chuàng)建與刪除check約束2)利用sql語句在創(chuàng)建表時創(chuàng)建check約束

語法格式:createtabletable_name(column_namedatatypenotnull/null[defaultconstraint_expression][[check_name]check(logical_expression)][…n])例在學生數(shù)據(jù)庫中創(chuàng)建books,其中包括所有的約束定義

usexscjcreatetablebooks(book_idsmallint,book_namevarchar(50)notnull,book_descvarchar(50)notnull,max_lvltinyintnotnullcheck(max_lvl<=250))Gousexscjcreatetablebooks1(book_idsmallint,book_namevarchar(50)notnull,book_descvarchar(50)notnull,max_lvltinyintnotnullconstraintcheck_mcheck(max_lvl<=250))Go3)利用sql語句在修改表時創(chuàng)建check約束

altertabletable_nameaddconstraintcheck_namecheck(logical_expression)例:通過修改xscj數(shù)據(jù)庫的xs_kc表,增加成績字段的check約束

usexscjaltertablexs_kcaddconstraintcj_constraintcheck(成績>=0and成績<=100)4)利用SQL語句刪除check約束

altertabletable_namedropconstraintcheck_name例刪除xscj數(shù)據(jù)庫中的xs_kc表的成績字段的check約束

usexscjaltertablexs_kcdropconstraintcj_constraintgo2.規(guī)則對象的定義、使用與刪除（1）定義規(guī)則對象（2）將規(guī)則對象邦定到列或用戶自定義數(shù)據(jù)類型

1）利用SQL命令定義規(guī)則并綁定到自定義類型或列規(guī)則對象的定義

createrulerule_nameascondition_expression綁定

sp_bindrule@rulename=]’rule’,[@objectname=]’object_name’[,[@futureonly=]‘futureonly_flag’]例創(chuàng)建一個規(guī)則，并綁定到表KC的課程號列，用于限制課程號的輸入范圍

usexscjgoCreaterulekc_ruleas@ranglike‘[1-5][0-9][0-9]’goUsexscjexecsp_bindrule‘kc_rule’,’kc.kch’go例2創(chuàng)建一個規(guī)則，用以限制輸入到該規(guī)則所綁定的列中的值只能是該規(guī)則中列出的值

usexscjgoCreaterulelist_ruleas@list1in('C語言','微機原理','離散數(shù)學')goUsexscjexecsp_bindrule'list_rule','kc.kcm'go例3定義一個用戶數(shù)據(jù)類型course_num,然后將前面定義的規(guī)則‘kc_rule’綁定到course_num上，最后定義表kc1，其課程號的數(shù)據(jù)類型為course_num。

usexscjexecsp_addtype'course_num','char(3)','notnull'execsp_bindrule'kc_rule','course_num'Gousexscjcreatetablekc1(kchcourse_num,kcmchar(30)notnull,kkxqtinyint,xshtinyint,xftinyint)usexscjifexists(selectnamefromsysobjectswherename='kc_rule'andtype='r')beginexecsp_unbindrule'kc.kch'droprulekc_ruleendgo二、實體完整性的實現(xiàn)通過選擇一列或多列做主鍵可實現(xiàn)表的實體完整性。一個表只能有一個primarykey約束,且primarykey約束中的列不能為空值.如果要確保一個表中的非主鍵列不輸入重復值,應(yīng)在該列上定義唯一約束(unique約束).Primarykey與unique約束的主要區(qū)別如下:(1)一個表只能創(chuàng)建一個primarykey約束,但可以對不同的列創(chuàng)建若干個unique約束.(2)primarykey約束列不能為空值,unique約束能為空值(3)創(chuàng)建primarykey約束時,系統(tǒng)自動產(chǎn)生索引,索引的默認類型為聚集索引,創(chuàng)建unique約束時,系統(tǒng)自動創(chuàng)建一個unique索引,索引類型為非聚集索引.1利用管理器定義和刪除primarykey約束2利用管理器定義和刪除unique約束3利用sql語句創(chuàng)建及刪除primarykey約束和unique約束1)創(chuàng)建表的同時創(chuàng)建語法格式:createtabletable_name(column_namedatatype[constraintconstraint_name]notnullPrimarykey/unique[clustered/nonclustered][…n])usexscjcreatetablexs3(xhchar(6)notnullconstraintxh_pkprimarykey,xmchar(8)notnull,identtitycardchar(20)constraintsh_ukunique,zhychar(10)null,xbchar(2)notnull,chshsmalldatetimenotnull,zxftinyintnull,bzhtextnull,rxrqdatetimeconstraintdatedfltdefaultgetdate())

2)通過修改表創(chuàng)建primarykey約束和unique約束

altertabletable_nameadd[constraintconstraint_name]primarykey/uniqueclustered/nonclustered(column[,…n])如:Altertablexsaddconstraintxs_pkprimarykeyclustered(xh)GoAltertablexsaddconstraintxs_ukuniquenonclustered(shfzh)go3)刪除primarykey約束和unique約束AltertablexsDropconstraintxs_pk，xs_ukgo三、參照完整性的實現(xiàn)

對兩個相關(guān)聯(lián)的表進行數(shù)據(jù)插入和刪除時,通過參照完整性保證它們之間數(shù)據(jù)的一致性如果兩個表之間定義了參照完整性,則:(1)從表不能引用不存在的鍵值(2)如果主表中的鍵值更改了,那么整個數(shù)據(jù)庫中,對從表中該鍵值的所有引用要進行一致的更改.(3)如果主表中沒有相關(guān)聯(lián)的記錄,則不能將記錄添加到從表(4)如果要刪除某一記錄,應(yīng)先刪除從表中與該記錄相匹配的記錄.

利用foreignkey定義從表的外碼,primarykey約束和unique約束定義主表中的主碼或唯一碼(不允許為空),可實現(xiàn)主表和從表之間的參照完整性。

定義表間參照關(guān)系:先定義主表的主碼(或唯一碼),再對從表定義外碼約束.1利用企業(yè)管理器定義表間的參照關(guān)系

2利用企業(yè)管理上刪除表間的參照關(guān)系

3利用SQL命令定義表間的參照關(guān)系

1）創(chuàng)建表的同時定義外碼約束createtabletable_name(column_namedatatype[foreignkey]referencesref_table(ref_column)[…n])例在xscj數(shù)據(jù)庫中創(chuàng)建xs4表,xs4.xh為主鍵,然后定義從表xs_kc1.xh為外碼

usexscjcreatetablexs4(xhchar(6)notnullconstraintxh_pkprimarykey,…..)GoCreatetablexs_kc1(xhchar(6)notnullforeignkeyreferencesxs4(xh),

…)Go例：usexscjaltertablexs_kc1addconstraintkc_fkforeignkey(kch)referenceskc(kch)go

2)通過修改表定義外碼約束

altertabletable_nameadd[constraintconstraint_name]foreignkey(column[,…n])referencesref_table(ref_column[,…n])3)利用SQL命令刪除表間的參照關(guān)系刪除參照關(guān)系，實際是刪除從表的外碼即可

altertabletable_namedropconstraintconstraint_namego7.4默認值約束及默認值對象一個字段默認值的建立可通過如下兩種方式實現(xiàn)：（1）在定義和修改表時，定義默認值約束（2）先定義默認對象，然后將該對象綁定到表的相應(yīng)字段一在表中定義及刪除默認值約束1.默認值約束的定義通過SQL命令的執(zhí)行，在定義或修改表時，可定義一個字段的默認值約束。默認值約束定義的一般格式：

createtabletable_name(columnnamedatatypenotnull|null[defaultconstraint_expression][…n])例：在定義表時定義一個字段的默認值Usexscjcreatetablexs1(xhchar(6)notnull,xmchar(8)notnull,zhychar(10)null,xbbitnotnull,birthdatesmalldatetimenotnull,zxftinyintnull，

rxrqdatetimedefaultgetdate())go例2下例的功能與上例相同，但在定義默認值的同時指定了約束名

usexscjcreatetablexs1(xhchar(6)notnull,xmchar(8)notnull,zhychar(10)null,xbbitnotnull,birthdatesmalldatetimenotnull,zxftinyintnullrxrqdatetimeCONSTRAINTdatedfltdefaultgetdate())Go例3在修改表定義一個字段的默認值約束。

usexscjaltertablexs1addadddatesmalldatetimenullconstraintadddatedfltdefaultgetdate()withvaluesWithvalues僅用在對表添加新字段的情況下。若使用了withvalues，則將為表中各現(xiàn)有行添加的字段提供默認值：如果沒有使用withvalues那么每一行的新列中都將為null值。2默認值約束的刪除若已知一個默認值約束的約束名，通過SQL命令刪除例3刪除上例中定義的默認值約束

usexscjaltertablexs1dropconstraintadddatedfltgo二、默認值對象的定義、使用和刪除通過SQL語句定義和綁定default默認值對象1）通過sql命令定義default默認值對象語法如下：

createdefaultdefault_nameasconstraint_expr