第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）浮點(diǎn)型:foat和real兩類

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

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

2記錄表中的一行

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

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)建學(xué)生情況表XS，

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

usexscjcreatetablexs(學(xué)號(hào)char(6)notnull,

姓名char(8)notnull,

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

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

出生時(shí)間smalldatetimenotnull,

總學(xué)分tinyintnull,

備注textnull)goCreatetable語句的語法：

createtable表名（{column_namedatatype|identity|notnull|null}）例：Createtablecard(卡號(hào)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中增加一個(gè)新列—獎(jiǎng)學(xué)金等級(jí)列

usexscjaltertablexsadd

獎(jiǎng)學(xué)金等級(jí)tinyintnullgo例：在XS表中刪除名為獎(jiǎng)學(xué)金等級(jí)的列

usexscjaltertablexsdropcolumn獎(jiǎng)學(xué)金等級(jí)

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

droptable表名（3）列的identity屬性對(duì)任何表都可創(chuàng)建包含系統(tǒng)所生成序號(hào)值的一個(gè)標(biāo)識(shí)列，該序號(hào)值唯一標(biāo)識(shí)表中的一行，可以作為鍵值。每個(gè)表只能有一列作為標(biāo)識(shí)列，int,numeric,smallint,biginORtinyint,定義標(biāo)識(shí)列可指定其種子（起始）值和增量值，建立數(shù)據(jù)庫library,在其中創(chuàng)建card表，表結(jié)構(gòu)如所示3修改表通過管理器和T-SQL語言兩種方法來進(jìn)行：更改表名，增加列，刪除列，修改已有列的屬性4刪除表創(chuàng)建數(shù)據(jù)庫和表以后，需要對(duì)表中的數(shù)據(jù)進(jìn)行操作，包括插入、刪除和修改。有兩種方法：一是通過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羅林林計(jì)算機(jī)01/30/19800:0:040可以使用如下的T-SQL語句：

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

例：用如下語句建立表testCREATETABLEtest(xmchar(20)notnull,zyvarchar(30)default(‘計(jì)算機(jī)’),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中總學(xué)分小于39的行刪除：

USEXSCJDELETEFROMXSWHERE總學(xué)分<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表中學(xué)號(hào)為001110的學(xué)生的備注列值改為“三好生”：

USEXSCJUPDATEXSSET備注=‘三好生’

WHERE 學(xué)號(hào)=‘001110’GO例將XS表中的所有學(xué)生的總學(xué)分都增加10個(gè)學(xué)分。

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

wherexm=‘王林’

7.4數(shù)據(jù)完整性和約束7.4.1數(shù)據(jù)完整性的分類數(shù)據(jù)完整性是指數(shù)據(jù)庫中的數(shù)據(jù)在邏輯上的一致性和準(zhǔn)確性.一般包括四種.1域完整性又稱列完整性,指給定列的輸入的有效性.可以通過定義相應(yīng)的規(guī)則、約束、默認(rèn)值對(duì)象等方法實(shí)現(xiàn)。2實(shí)體完整性又稱行的完整性，要求表中有一個(gè)鍵，其值不能為空且能唯一地表示對(duì)應(yīng)的實(shí)體。通過索引、UNIQUE約束primarykey約束或identity屬性可實(shí)現(xiàn)數(shù)據(jù)的完整性.3參照完整性參照完整性又稱引用完整性.參照完整性保證主表(被參照表)中的數(shù)據(jù)與從表(參照表)中數(shù)據(jù)的一致性.通過定義外鍵(外碼)與主鍵(主碼)之間或外鍵與唯一鍵之間的對(duì)應(yīng)關(guān)系來實(shí)現(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完整性的實(shí)現(xiàn)一、域完整性的實(shí)現(xiàn)可以通過定義約束和規(guī)則等來實(shí)現(xiàn)域完整性

1.check約束的定義與刪除

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

語法格式:createtabletable_name(column_namedatatypenotnull/null[defaultconstraint_expression][[check_name]check(logical_expression)][…n])例在學(xué)生數(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語句在修改表時(shí)創(chuàng)建check約束

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

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

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

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

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

createrulerule_nameascondition_expression綁定

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

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

usexscjgoCreaterulelist_ruleas@list1in('C語言','微機(jī)原理','離散數(shù)學(xué)')goUsexscjexecsp_bindrule'list_rule','kc.kcm'go例3定義一個(gè)用戶數(shù)據(jù)類型course_num,然后將前面定義的規(guī)則‘kc_rule’綁定到course_num上，最后定義表kc1，其課程號(hào)的數(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二、實(shí)體完整性的實(shí)現(xiàn)通過選擇一列或多列做主鍵可實(shí)現(xiàn)表的實(shí)體完整性。一個(gè)表只能有一個(gè)primarykey約束,且primarykey約束中的列不能為空值.如果要確保一個(gè)表中的非主鍵列不輸入重復(fù)值,應(yīng)在該列上定義唯一約束(unique約束).Primarykey與unique約束的主要區(qū)別如下:(1)一個(gè)表只能創(chuàng)建一個(gè)primarykey約束,但可以對(duì)不同的列創(chuàng)建若干個(gè)unique約束.(2)primarykey約束列不能為空值,unique約束能為空值(3)創(chuàng)建primarykey約束時(shí),系統(tǒng)自動(dòng)產(chǎn)生索引,索引的默認(rèn)類型為聚集索引,創(chuàng)建unique約束時(shí),系統(tǒng)自動(dòng)創(chuàng)建一個(gè)unique索引,索引類型為非聚集索引.1利用管理器定義和刪除primarykey約束2利用管理器定義和刪除unique約束3利用sql語句創(chuàng)建及刪除primarykey約束和unique約束1)創(chuàng)建表的同時(shí)創(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三、參照完整性的實(shí)現(xiàn)

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

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

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

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

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

1）創(chuàng)建表的同時(shí)定義外碼約束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)系，實(shí)際是刪除從表的外碼即可

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

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

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

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

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

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

createdefaultdefault_nameasconstraint_expr