2023學(xué)年完整公開(kāi)課版SQL數(shù)據(jù)操作

第10章SQL數(shù)據(jù)操作重點(diǎn)內(nèi)容：掌握使用T-SQL語(yǔ)句對(duì)數(shù)據(jù)庫(kù)的操作掌握使用T-SQL語(yǔ)句對(duì)表的操作掌握使用T-SQL對(duì)數(shù)據(jù)的操作掌握使用T-SQL創(chuàng)建各類試圖數(shù)據(jù)庫(kù)的創(chuàng)建在數(shù)據(jù)庫(kù)系統(tǒng)中，創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)永遠(yuǎn)是第一位的，數(shù)據(jù)庫(kù)的創(chuàng)建就是在磁盤(pán)中劃分一個(gè)區(qū)域用于數(shù)據(jù)的存儲(chǔ)和管理，創(chuàng)建數(shù)據(jù)庫(kù)需要制定一系列的參數(shù)對(duì)數(shù)據(jù)庫(kù)進(jìn)行描述，常用的參數(shù)有：數(shù)據(jù)庫(kù)名、數(shù)據(jù)庫(kù)容量、起始容量、可否自增長(zhǎng)等。在SQLServer2016中使用CREATEDATABASE語(yǔ)句對(duì)數(shù)據(jù)庫(kù)進(jìn)行創(chuàng)建，下面是CREATEDATABASE的基本語(yǔ)法規(guī)則：CREATEDATABASEdatabase_name[ON[PRIMARY]([NAME=logical_file_name,]FILENAME='os_file_name'[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])[,...n]]數(shù)據(jù)庫(kù)的創(chuàng)建參數(shù)解釋database_name：創(chuàng)建數(shù)據(jù)庫(kù)的名稱，數(shù)據(jù)庫(kù)名只能定義在128個(gè)字符以內(nèi)，并且不能和SQLServer2016中已存在的數(shù)據(jù)庫(kù)實(shí)例重名。PRIMARY：指定關(guān)聯(lián)的<filespec>列表定義主文件。也就是.mdf文件。一個(gè)數(shù)據(jù)庫(kù)只能有一個(gè)主文件。如未指定PRIMARY，那么CREATEDATABASE語(yǔ)句中列出的第一個(gè)文件將成為主文件。NAME：指定文件的邏輯名稱，在引用數(shù)據(jù)庫(kù)時(shí)使用文件名。FILENAME：指定數(shù)據(jù)庫(kù)文件的全路徑地址，也是最終數(shù)據(jù)庫(kù)文件.mdf或次要文件.ndf和日志文件.ldf的文件名。SIZE：數(shù)據(jù)庫(kù)文件的起始大小，如果在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)未指定，將使用model數(shù)據(jù)庫(kù)中的文件大小。MAXSIZE：指FILENAME文件可擴(kuò)展容量的極限，如果不指定MAXSIZE則數(shù)據(jù)庫(kù)文件會(huì)一直擴(kuò)充，直到磁盤(pán)容量不足為止。FILEGROWTH：指定文件的可增長(zhǎng)量，文件的FILEGROWTH不能超過(guò)MAXSIZE設(shè)置。數(shù)據(jù)庫(kù)的修改使用ALERT語(yǔ)句修改數(shù)據(jù)庫(kù)語(yǔ)法規(guī)則如下：ALTERDATABASEdatabase{ADDFILE<filespec>[,...n][TOFILEGROUPfilegroup_name]|ADDLOGFILE<filespec>[,...n]|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE<filespec>|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}|SET<optionspec>[,...n][WITH<termination>]|COLLATE<collation_name>}<filespec>::=(NAME=logical_file_name[,NEWNAME=new_logical_name][,FILENAME='os_file_name'][,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])數(shù)據(jù)庫(kù)的修改參數(shù)解釋database：要進(jìn)行修改的數(shù)據(jù)庫(kù)名。ADDFILE.TOFILEGROUP：添加新數(shù)據(jù)庫(kù)文件。ADDLOGFILE：添加日志文件。REMOVEFILE：從SQLServer實(shí)例中刪除文件。MODIFYFILE：指定要修改的文件名。MODIFYNAME：對(duì)數(shù)據(jù)庫(kù)進(jìn)行重命名。MODIFYFILEGROUP：通過(guò)將狀態(tài)設(shè)置為READ_ONLY或READ_WRITE，將文件組設(shè)置為數(shù)據(jù)庫(kù)的默認(rèn)文件組或者更改文件組名稱來(lái)修改文件組。修改數(shù)據(jù)庫(kù)size屬性時(shí)修改的值必須大于當(dāng)前的大小。如果要縮小數(shù)據(jù)庫(kù)文件的大小，需要使用dbccshrinkfile(‘邏輯文件名’,大小)刪除數(shù)據(jù)庫(kù)既然能夠通過(guò)T-SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)，那么也可以刪除。在SQL中刪除數(shù)據(jù)庫(kù)和刪除數(shù)據(jù)表都是用DROP語(yǔ)句來(lái)完成。刪除數(shù)據(jù)庫(kù)后數(shù)據(jù)庫(kù)中所有的內(nèi)容同時(shí)被刪除并釋放磁盤(pán)的存儲(chǔ)容量，用DROP語(yǔ)句刪除數(shù)據(jù)庫(kù)的基本語(yǔ)法格式如下：DROPDATABASEdatabase_name刪除數(shù)據(jù)庫(kù)的SQL語(yǔ)句比較簡(jiǎn)單，只需注意database_name為指定需要?jiǎng)h除數(shù)據(jù)庫(kù)的名稱即可。創(chuàng)建表使用CREATETABLE語(yǔ)句，基本語(yǔ)法格式如下：CREATETABLE[database_name.[schema_name].|schema_name.]table_namecolumn_name<data_type>[NULL|NOTNULL]|[DEFAULTconstant_exptession]|[ROWGUIDCOL]{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED][ASC|DESC]}[,…n]參數(shù)解釋 database_name：要在其中創(chuàng)建表的數(shù)據(jù)庫(kù)的名稱。database_name必須指定現(xiàn)有數(shù)據(jù)庫(kù)的名稱。如果未指定，則database_name默認(rèn)為當(dāng)前數(shù)據(jù)庫(kù)。schema_name：新表所屬架構(gòu)的名稱。table_name：新建數(shù)據(jù)表的名稱。column_name：表中數(shù)據(jù)列的名稱，列名不能重復(fù)。data_type：指定列的數(shù)據(jù)類型，可以是系統(tǒng)數(shù)據(jù)類型，也可是用戶自定義的數(shù)據(jù)類型。創(chuàng)建表參數(shù)解釋NULL|NOTNULL：設(shè)定數(shù)據(jù)列中是否可以使用空值。DEFAULT：指定默認(rèn)列。ROWGUIDCOL：指示新列是行GUID列。對(duì)于每個(gè)表，只能將其中的一個(gè)uniqueidentifier列指定為ROWGUIDCOL列。PRIMARYKEY：是通過(guò)唯一索引對(duì)給定的一列或多列強(qiáng)制實(shí)體完整性的約束。每個(gè)表只能創(chuàng)建一個(gè)PRIMARYKEY約束。UNIQUE：唯一性約束，該約束通過(guò)唯一索引為一個(gè)或多個(gè)指定列提供實(shí)體完整性。一個(gè)表可以有多個(gè)UNIQUE約束。CLUSTERED|NONCLUSTERED：指示為PRIMARYKEY或UNIQUE約束創(chuàng)建聚集索引還是非聚集索引。PRIMARYKEY約束默認(rèn)為CLUSTERED，UNIQUE約束默認(rèn)為NONCLUSTERED。[ASC|DESC]：指定列的排序方式，ASC為升序排列，DESC為降序排列，在不指定的情況下默認(rèn)為ASC。操作約束創(chuàng)建約束的語(yǔ)法規(guī)則如下：AlterTabletable_nameAddConstraintConstraint_nameConstraint_type參數(shù)解釋 table_name：指定需要添加約束的數(shù)據(jù)表。Constraint_name：指定約束名。Constraint_type：指定約束的具體類型。刪除約束如果創(chuàng)建約束時(shí)發(fā)生了錯(cuò)誤，可以對(duì)已建好的約束進(jìn)行刪除，語(yǔ)法規(guī)則如下：AlterTable表名DropConstraint約束名使用ALTERTABLE語(yǔ)句修改表結(jié)構(gòu)ALTERTABLE語(yǔ)句語(yǔ)法ALTERTABLE[database_name.[schema_name].|schema_name.]table_nameALTER[COLUMNcolumn_nametype_name[column_constraints]][,….n]}[ADD{[column_name1typename[column_constraints],[table_constraint]][,….n]}|DROP{[COLUMNcolumn_name1][,….n]}}參數(shù)解釋 ALTER：修改表字段屬性。 ADD：添加字段關(guān)鍵字，表示在表中添加一列，可連續(xù)定義多個(gè)字段信息，字段之間的內(nèi)容按逗號(hào)隔開(kāi)即可。 DROP：刪除表中字段，可以同時(shí)刪除多個(gè)字段，多字段之間按逗號(hào)隔開(kāi)。使用DROPTABLE語(yǔ)句刪除表刪除數(shù)據(jù)表是對(duì)數(shù)據(jù)庫(kù)中已建立好的表進(jìn)行刪除，在刪除表的同時(shí)會(huì)對(duì)表中定義的數(shù)據(jù)、檢索、視圖同時(shí)進(jìn)行清除。在做任何刪除操作前應(yīng)做好備份工作，使用DROPTABLE語(yǔ)句對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)表進(jìn)行刪除，語(yǔ)法格式如下：DROPTABLEtable_nametable_name為要?jiǎng)h除的表名。實(shí)例：刪除xsxk數(shù)據(jù)庫(kù)中的xs表，USExsxkGODROPTABLEdbo.xs使用SELECT語(yǔ)句瀏覽數(shù)據(jù)SELECT語(yǔ)句的基本語(yǔ)法如下：SELECT[ALL|DISTINCT[ON(expression[,...])]]*|expression[ASoutput_name][,...][FROMfrom_item[,...]][WHEREcondition][GROUPBYexpression[,...]][HAVINGcondition[,...]][{UNION|INTERSECT|EXCEPT}[ALL]select][ORDERBYexpression[ASC|DESC|USINGoperator][,...]][FORUPDATE[OFtablename[,...]]][LIMIT{count|ALL}][OFFSETstart]參數(shù)解釋 ALL：指定結(jié)果集中的記錄可以包含重復(fù)行。 DISTINCT：在結(jié)果集中可能包含重復(fù)值，DISTINCT用于返回唯一不同的值。 FROM：查詢的數(shù)據(jù)源，在SQLServer2016中查詢的數(shù)據(jù)源可以是表和試圖。 WHERE<condition>：指定查詢的條件。 GROUPBY：表示查詢結(jié)果是否按字段進(jìn)行分組。 HAVING：分組過(guò)濾條件，對(duì)集合函數(shù)運(yùn)行結(jié)果的輸出進(jìn)行限制。 OREDERBY：指定查詢結(jié)果的排序方法ASC升序，DESC降序。查詢方式普通查詢SELECT*FROMxs;查詢語(yǔ)句中星號(hào)（*）作為通配符來(lái)使用，表示返回所有列。%”符號(hào)是字符匹配符，能匹配0個(gè)或更多字符的任意長(zhǎng)度的字符串。在SQL語(yǔ)句中可以在查詢條件的任意位置放置一個(gè)%來(lái)代表一個(gè)任意長(zhǎng)度的字符串。在查詢條件時(shí)也可以放置兩個(gè)%進(jìn)行查詢，但在查詢條件中最好不要連續(xù)出現(xiàn)兩個(gè)%。指定字段查詢SELECT姓名,班級(jí)fromxs;如果只想查找出某個(gè)特定字段的內(nèi)容，可以通過(guò)指定查詢字段的方式來(lái)檢索。使用表達(dá)式查詢SELECT姓名,班級(jí),總學(xué)分+5AS'學(xué)分'fromxs;使用了AS關(guān)鍵字的作用是為顯示新的學(xué)分字段增加一個(gè)臨時(shí)的列名帶條件查詢SELECT姓,班級(jí)fromxsWHERE班級(jí)='14信管';在查詢數(shù)據(jù)的過(guò)程中經(jīng)常要做的一項(xiàng)操作就是查找出數(shù)據(jù)表中符合條件的記錄，通過(guò)設(shè)定特殊的條件對(duì)數(shù)據(jù)進(jìn)行過(guò)濾。在SELECT語(yǔ)句中可以通過(guò)WHERE子句對(duì)過(guò)濾條件進(jìn)行設(shè)定。查詢方式使用AND的多條件查詢使用SELECT查詢時(shí)，可以使用AND操作符同時(shí)設(shè)定多個(gè)查詢條件。多個(gè)條件之間用AND相連，表示返回同時(shí)滿足多個(gè)條件的記錄。SELECT姓名,總學(xué)分,性別fromxsWHERE總學(xué)分>18and性別='女';AND和OR關(guān)鍵字可以連接條件表達(dá)式。這些條件表達(dá)式中可以使用“=”“>”等操作符，也可以使用IN、BETWEENAND和LIKE等關(guān)鍵字，而且，LIKE關(guān)鍵字匹配字符串時(shí)可以使用“%”和“_”等通配符模糊查詢使用SELECT查詢記錄時(shí)，有時(shí)條件的限定不是特別明確，或者說(shuō)只知道查詢條件中的一部分沒(méi)辦法設(shè)定一個(gè)完整的限定條件，這個(gè)時(shí)候可以使用LIKE操作符進(jìn)行匹配查詢，通過(guò)使用通配符來(lái)代替模糊的部分，達(dá)到模糊查詢的效果。SELECT姓f(shuō)romxsWHERE姓LIKE'李%';查詢方式T-SQL中常用通配符使用ORDERBY排序?qū)Σ樵兂鰜?lái)的記錄集可以使用ORDERBY子句指定排序的字段，對(duì)記錄集進(jìn)行升序或降序的排列。SELECT*FROMxsORDERBY總學(xué)分DESC;使用INSERT語(yǔ)句添加數(shù)據(jù)使用INSERT語(yǔ)句可以向已經(jīng)建好的表中添加數(shù)據(jù)，可插入一條記錄和多條記錄，插入的記錄必須符合表中字段的數(shù)據(jù)類型和相關(guān)約束條件。INSERT語(yǔ)句基本語(yǔ)法格式如下：INSERTINTOtable_name(column_list)VALUES(value_list);語(yǔ)法解釋table_name：要插入數(shù)據(jù)的表名。column_list：指定要將數(shù)據(jù)插入到的列。value_list：插入到列中的對(duì)應(yīng)數(shù)據(jù)。實(shí)例INSERTINTOxsVALUES('14001','張三','男','1998-11-02','14信管','江西萍鄉(xiāng)',21,'');使用UPDATE語(yǔ)句修改指定數(shù)據(jù)UPDATE語(yǔ)句的基本語(yǔ)法結(jié)構(gòu)如下：UPDATEtable_nameSETcolumnname1=value1,column_name2=value2,…..,column_nameN=valueNWHEREsearch_condition語(yǔ)法解釋columnname1為要更新的字段名，value1為更新后的值。。從數(shù)據(jù)參數(shù)可以看出執(zhí)行UPDATE操作的時(shí)候可以一次對(duì)多個(gè)列進(jìn)行操作，只需要在每個(gè)“column=value”對(duì)之間用逗號(hào)隔開(kāi)當(dāng)UPDATE沒(méi)有設(shè)置search_condition的時(shí)候會(huì)對(duì)表中所有記錄進(jìn)行操作。使用DELETE語(yǔ)句刪除指定數(shù)據(jù)DELETE語(yǔ)句的基本語(yǔ)法格式如下：DELETEFROMtable_name[WHEREcondition]參數(shù)解釋table_name為指定需要?jiǎng)h除操作的表，condition為刪除的條件表達(dá)式?？梢栽诓粍h除表的情況下刪除所有的行。這意味著表的結(jié)構(gòu)、屬性和索引都是完整的。SQL的刪除操作能對(duì)表中的一條或多條記錄進(jìn)行刪除，如果指定了刪除記錄的條件則刪除符合條件的記錄，如果沒(méi)有指定會(huì)刪除所有的記錄，清空數(shù)據(jù)表。使用CREATEVIEW語(yǔ)句創(chuàng)建視圖使用T-SQL命令創(chuàng)建視圖的基本語(yǔ)法如下。CREATEVIEW[schema_name.]view_name[column_list][WITH<ENCRYPTION|SCHEMABINDING|VIEW]METADATA>]ASselect_statement[WITHCHECKOPTION]參數(shù)解釋schema_name：視圖所屬架構(gòu)的名稱。view_name：指定創(chuàng)建視圖的名稱。column_list：指定視圖中列名稱。AS：指定視圖的操作。select_statement：指定視圖所用的SELECT語(yǔ)句。該語(yǔ)句的操作對(duì)象可以是多個(gè)表和其他視圖。WITHCHECKOPTION：視圖中所有的數(shù)據(jù)修改語(yǔ)句都必須符合select_statement中設(shè)置的條件。視圖定義的語(yǔ)句不能包括以下內(nèi)容：（1）COMPUTE或COMPTEBY語(yǔ)句（2）ORDERBY語(yǔ)句（3）INTO關(guān)鍵字（4）OPTION語(yǔ)句（5）引用臨時(shí)變量或表修改視圖和刪除視圖使用ALTERVIEW語(yǔ)句可以對(duì)已創(chuàng)建好的視圖進(jìn)行修改，在使用ALTERVIEW語(yǔ)句修改視圖前先要確定有使用該視圖的權(quán)限。ALTERVIEW語(yǔ)法與CREATEVIEW語(yǔ)法基本相同。實(shí)例ALTERVIEWview_xsASALTERVIEWview_xsASSELECT姓名,家庭住址FROMxs使用DROPVIEW語(yǔ)句刪除視圖，其語(yǔ)法格式如下。DROPVIEWview_name1,view_name2….,view_nameN;實(shí)例DROPVIEWview_xs;視圖中的數(shù)據(jù)操作由于視圖是由一個(gè)或多個(gè)表中的字段信息動(dòng)態(tài)地組合成一個(gè)數(shù)據(jù)集合，視圖是一個(gè)虛擬的表，本身是不存儲(chǔ)任何數(shù)據(jù)信息的，對(duì)視圖的所有增刪改操作實(shí)際上是對(duì)作為視圖數(shù)據(jù)源的表進(jìn)行修改。修改視圖數(shù)據(jù)時(shí)要注意以下幾點(diǎn)：（1）修改視圖數(shù)據(jù)時(shí)一次只能對(duì)一個(gè)表中的數(shù)據(jù)進(jìn)行操作。（2）不能對(duì)表中的計(jì)算字段進(jìn)行修改。向視圖中添加數(shù)據(jù)INSERTINTOview_infoVALUES('14001','張三');當(dāng)我們向視圖中插入一條數(shù)據(jù)時(shí)，作為視圖數(shù)據(jù)源的提供者xs表中也添加了一條記錄。修改視圖中的數(shù)據(jù)UPDATEview_infoSET"學(xué)號(hào)"=15001WHERE姓