SQLServer高級(jí)查詢(xún)與TSQL編程數(shù)據(jù)應(yīng)用課件

1、第2章 數(shù)據(jù)應(yīng)用SQL Server高級(jí)查詢(xún)與T-SQL編程第1頁(yè)，共46頁(yè)?；仡?3 個(gè)世界間的兩種轉(zhuǎn)換過(guò)程分別對(duì)應(yīng)于數(shù)據(jù)庫(kù)設(shè)計(jì)中的兩個(gè)設(shè)計(jì)階段一對(duì)多聯(lián)系可以轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，也可以與多端實(shí)體所對(duì)應(yīng)的關(guān)系模式合并使用 PowerDesigner 繪制概念模型方式：分別創(chuàng)建多對(duì)多的兩個(gè)實(shí)體，并點(diǎn)擊工具欄的聯(lián)系按鈕創(chuàng)建一個(gè)聯(lián)系分別選中一方實(shí)體，并點(diǎn)擊聯(lián)系連接按鈕，繪制一條指向多方的直線，則分別建立起該實(shí)體與聯(lián)系之間的聯(lián)系給聯(lián)系取名，并在該聯(lián)系上添加該聯(lián)系的自帶屬性第2頁(yè)，共46頁(yè)。數(shù)據(jù)應(yīng)用DDL 和 DML 語(yǔ)句select 基本查詢(xún)聚合函數(shù)和分組查詢(xún)連接查詢(xún)使用SQL語(yǔ)句定義、維護(hù)數(shù)據(jù)

2、庫(kù)結(jié)構(gòu)和數(shù)據(jù)查詢(xún)圖書(shū)出版數(shù)據(jù)“司途網(wǎng)”旅游系統(tǒng)數(shù)據(jù)庫(kù)業(yè)務(wù)操作統(tǒng)計(jì)“零聚網(wǎng)”訂單數(shù)據(jù)第3頁(yè)，共46頁(yè)。定義屬性使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)使用 DML 語(yǔ)句維護(hù)數(shù)據(jù)表內(nèi)容使用SQL語(yǔ)句定義、維護(hù)數(shù)據(jù)庫(kù)結(jié)構(gòu)和數(shù)據(jù)2520第4頁(yè)，共46頁(yè)。2.1.1 使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)數(shù)據(jù)定義語(yǔ)言（Data Definition Language，DDL），用于定義和管理 SQL 數(shù)據(jù)庫(kù)中所有對(duì)象，它最基本的功能是創(chuàng)建和維護(hù)數(shù)據(jù)庫(kù)和數(shù)據(jù)表結(jié)構(gòu)。第5頁(yè)，共46頁(yè)。2.1.1 使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)創(chuàng)建數(shù)據(jù)庫(kù)的 DDL 語(yǔ)法如下：語(yǔ)法說(shuō)明：database_name 為數(shù)據(jù)庫(kù)名，如創(chuàng)建“l(fā)i

3、ngju”數(shù)據(jù)庫(kù)的 DDL 語(yǔ)句為：create database lingju 。CREATE DATABASE database_name第6頁(yè)，共46頁(yè)。2.1.1 使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)刪除數(shù)據(jù)庫(kù)的 DDL 語(yǔ)法如下：DROP DATABASE database_name第7頁(yè)，共46頁(yè)。2.1.1 使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)創(chuàng)建數(shù)據(jù)表的 DDL 語(yǔ)法如下：語(yǔ)法說(shuō)明：“”中的內(nèi)容為可選項(xiàng)。table_name：表名。column_name：字段名。data_type：字段數(shù)據(jù)類(lèi)型。default_value：默認(rèn)值。identity ：主鍵值自動(dòng)增長(zhǎng)。pk_name：當(dāng)

4、前表中的主鍵名。fk_name：當(dāng)前表中的外鍵名。referenced_table_name：被引用表名（主表名）。ref_pk_name：被引用表主鍵名。CREATE TABLE table_name(column_name data_type NOT NULL DEFAULT default_value identity ,.PRIMARY KEY(pk_name),FOREIGN KEY(fk_name) REFERENCES referenced_table_name(ref_pk_name)第8頁(yè)，共46頁(yè)。2.1.1 使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)create table sho

5、p( shopID int identity(1,1) primary key, shopName varchar(50), shopAddress varchar(50), contact varchar(50), city varchar(50) defaul 武漢 )Create table orders( ordersID int identity(1,1) primary key, ordersDate date, deliveryDate date, amount decimal(10,2), customerID int, foreign key(customerID) refe

6、rences customer(customerID)第9頁(yè)，共46頁(yè)。2.1.1 使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)新增字段語(yǔ)法如下：alter table 表名 add 字段名 數(shù)據(jù)類(lèi)型alter table customer add bankNo nvarchar(20)第10頁(yè)，共46頁(yè)。2.1.1 使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)刪除字段語(yǔ)法如下：alter table 表名 drop column 字段名alter table customer drop column bankNo第11頁(yè)，共46頁(yè)。2.1.1 使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)修改字段名語(yǔ)法如下：alter tabl

7、e 表名 rename column 舊字段名 to 舊字段名alter table customer rename column tel to mobile第12頁(yè)，共46頁(yè)。2.1.1 使用 DDL 語(yǔ)句維護(hù)數(shù)據(jù)表結(jié)構(gòu)修改字段類(lèi)型語(yǔ)法如下：alter table 表名 alter column 字段名 數(shù)據(jù)類(lèi)型alter table goods alter column unitPrice decimal(18,4)第13頁(yè)，共46頁(yè)。2.1.2 使用 DML 語(yǔ)句維護(hù)數(shù)據(jù)表內(nèi)容insert 語(yǔ)句用于向表中新增記錄，其中新增一條新記錄的語(yǔ)法如下：Insert into table_name

8、( 字段列表 ) values( 值列表 )Insert into product(title,productDesc,originalPrice,currentPrice)values( 資生堂菲婷 , 日本原裝 ,369,289)第14頁(yè)，共46頁(yè)。2.1.2 使用 DML 語(yǔ)句維護(hù)數(shù)據(jù)表內(nèi)容使用 update 語(yǔ)句可以對(duì)表中的記錄進(jìn)行修改。update 語(yǔ)句的語(yǔ)法格式如下：update table_name set 字段名 1= 值 1, 字段名 2= 值 2 , , 字段名 n= 值 n; where 條件表達(dá)式 Update customer set city=concat(city

9、, 市 ), phone= concat(86,phone)第15頁(yè)，共46頁(yè)。2.1.2 使用 DML 語(yǔ)句維護(hù)數(shù)據(jù)表內(nèi)容刪除表記錄的語(yǔ)法如下：Delete from table_name where 條件表達(dá)式 Update customer set city=concat(city, 市 ), phone= concat(86,phone)第16頁(yè)，共46頁(yè)。2.1.3 學(xué)生實(shí)踐練習(xí)25現(xiàn)有一圖書(shū)出版信息系統(tǒng)，相關(guān)信息如下：圖書(shū)信息包括圖書(shū)號(hào)、圖書(shū)名、作者、出版社號(hào)、書(shū)籍類(lèi)型和單價(jià)。出版社信息包括出版社號(hào)、出版社名、地址和電話。根據(jù)以上信息，完成以下任務(wù)：創(chuàng)建數(shù)據(jù)庫(kù) bookDB。打開(kāi)數(shù)

10、據(jù)庫(kù) bookDB，在其中創(chuàng)建數(shù)據(jù)表圖書(shū) book 和出版社 publisher。新增一個(gè)出版社（zgdzdxcbs，中國(guó)地質(zhì)大學(xué)出版社，武漢市洪山區(qū)魯磨路 388 號(hào)。新增由中國(guó)地質(zhì)大學(xué)出版社出版的一本書(shū)，相關(guān)信息分別為：9787562540717，地球物理勘探概論，劉天佑，zgdzdxcbs，地質(zhì)科學(xué)，48.4。更新圖書(shū)地球物理勘探概論的類(lèi)型為“地球科學(xué)”，價(jià)格為原價(jià)的 8 折。刪除圖書(shū)地球物理勘探概論第17頁(yè)，共46頁(yè)。2.1.3 學(xué)生實(shí)踐練習(xí)創(chuàng)建數(shù)據(jù)庫(kù) bookDB 使用DDL創(chuàng)建數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)中的表使用DML完成相應(yīng)的業(yè)務(wù)需求第18頁(yè)，共46頁(yè)。selec

11、t 基本查詢(xún)select 基本結(jié)構(gòu)語(yǔ)法查詢(xún)結(jié)果排序distinct 關(guān)鍵字使用 top n 返回指定行數(shù)模糊查詢(xún)查詢(xún)圖書(shū)出版數(shù)據(jù)2520第19頁(yè)，共46頁(yè)。2.2.1 select 基本結(jié)構(gòu)語(yǔ)法select 查詢(xún)語(yǔ)句的語(yǔ)法如下：語(yǔ)法說(shuō)明：必須的子句只有 select 子句和 from 子句。where 子句用于對(duì)查詢(xún)結(jié)果進(jìn)行過(guò)濾。group by 子句根據(jù)指定列分組，having 子句對(duì)分組后的結(jié)果進(jìn)行過(guò)濾。order by 子句用于對(duì)查詢(xún)結(jié)果進(jìn)行排序。asc 表示升序排序，desc 表示降序排序，默認(rèn)按 asc 排序。SELECT FROM WHERE GROUP BY COLUMN1, C

12、OLUMN2, COLUMN3. | HAVING ORDER BY ASC 或 DESC第20頁(yè)，共46頁(yè)。2.2.1 select 基本結(jié)構(gòu)語(yǔ)法查詢(xún)商品標(biāo)題、原價(jià)和團(tuán)購(gòu)價(jià)，要求采用別名顯示查詢(xún)結(jié)果Select title as 標(biāo)題 , originalPrice as 原價(jià) , currentPrice as 團(tuán)購(gòu)價(jià) from product第21頁(yè)，共46頁(yè)。2.2.2 查詢(xún)結(jié)果排序查詢(xún)商品標(biāo)題、原價(jià)和團(tuán)購(gòu)價(jià)，按商品種類(lèi)升序排列，如果種類(lèi)相同，則按團(tuán)購(gòu)價(jià)降序排列Select title as 標(biāo)題 , originalPrice as 原價(jià) , currentPrice as 團(tuán)購(gòu)價(jià)

13、from product order by categoryID, currentPrice desc第22頁(yè)，共46頁(yè)。2.2.3 distinct 關(guān)鍵字查詢(xún)訂購(gòu)過(guò)商品的全部客戶(hù)編號(hào)，要求僅顯示客戶(hù)編號(hào)且編號(hào)不能重復(fù)Select distinct customerID from orders第23頁(yè)，共46頁(yè)。2.2.4 使用 top n 返回指定行數(shù)使用top n返回結(jié)果集的前n行數(shù)據(jù)，它的具體語(yǔ)法如下：Select top n column1,colmn2 from tableSelect top 3 title 標(biāo)題 , currentPrice 團(tuán)購(gòu)價(jià) from product o

14、rder by currentPrice第24頁(yè)，共46頁(yè)。2.2.5 模糊查詢(xún)查詢(xún)所有 KTV 的價(jià)格信息Select title as 標(biāo)題 , originalPrice as 原價(jià) , currentPrice as 團(tuán)購(gòu)價(jià) from productWhere title like %KTV%第25頁(yè)，共46頁(yè)。2.2.6 學(xué)生實(shí)踐練習(xí)針對(duì) 2.1.3 所設(shè)計(jì)的 bookDB，完成以下任務(wù)：（1）查詢(xún)中國(guó)地質(zhì)大學(xué)出版社基本信息，要求顯示社號(hào)、地址 和電話。（2）查詢(xún) IT 類(lèi)書(shū)籍基本信息，要求按類(lèi)型升序、價(jià)格降序顯示查詢(xún)信息。（3）顯示所有不重復(fù)的書(shū)籍類(lèi)型。（4）顯示書(shū)價(jià)最高的前 3

15、名書(shū)籍信息。（5）按類(lèi)型顯示書(shū)名含有“編程”字樣的所有書(shū)籍信息25第26頁(yè)，共46頁(yè)。聚合函數(shù)和分組查詢(xún)聚合函數(shù)分組查詢(xún)司途網(wǎng)”旅游系統(tǒng)數(shù)據(jù)庫(kù)業(yè)務(wù)操作2520第27頁(yè)，共46頁(yè)。2.3.1 聚合函數(shù)聚合函數(shù)用于對(duì)一組值進(jìn)行計(jì)算并返回一個(gè)匯總值，使用聚合函數(shù)可以統(tǒng)計(jì)記錄行數(shù)、計(jì)算某個(gè)字段值的總和以及這些值的最大值、最小值和平均值等。 常用聚合函數(shù)及其功能：函數(shù)名稱(chēng)函數(shù)功能sum 返回選取的某列值的總和max 返回選取的某列的最大值min 返回選取的某列的最小值avg 返回選取的某列的平均值count 返回選取的某列或記錄的行數(shù)第28頁(yè)，共46頁(yè)。2.3.1 聚合函數(shù)獲取火鍋類(lèi)商品的最高團(tuán)購(gòu)價(jià)、最

16、低團(tuán)購(gòu)價(jià)、平均團(tuán)購(gòu)價(jià)、數(shù)量以及銷(xiāo)售數(shù)量合計(jì)select max(currentPrice) 最高團(tuán)購(gòu)價(jià) , min(currentPrice) 最低團(tuán)購(gòu)價(jià) , avg(currentPrice) 平均團(tuán)購(gòu)價(jià) , count(*) 數(shù)量 , sum(salesCount) 銷(xiāo)售數(shù)量合計(jì) from product where categoryID=7第29頁(yè)，共46頁(yè)。2.3.1 聚合函數(shù)統(tǒng)計(jì)有商家信息的商品的數(shù)量Select count(areaID) 商品數(shù)量 from product第30頁(yè)，共46頁(yè)。2.3.2 分組查詢(xún)使用 group by 子句可以將數(shù)據(jù)劃分到不同的組中，實(shí)現(xiàn)對(duì)記錄的分

17、組查詢(xún)。group by 從英文字面的意義上可以理解為“根據(jù)（by）一定的規(guī)則進(jìn)行分組（group）”，該子句的作用是通過(guò)一定的規(guī)則將一個(gè)數(shù)據(jù)集劃分成若干個(gè)小的區(qū)域，然后針對(duì)這若干個(gè)小區(qū)域進(jìn)行統(tǒng)計(jì)匯總。group by 字段列表 having 條件表達(dá)式 第31頁(yè)，共46頁(yè)。2.3.2 分組查詢(xún)顯示不同類(lèi)型的商品數(shù)量和平均團(tuán)購(gòu)價(jià)，按照商品數(shù)量升序排列，如果商品數(shù)量相同，則按平均團(tuán)購(gòu)價(jià)降序排列select categoryID 商品類(lèi)型編號(hào) , count(*) 商品數(shù)量 , avg(currentPrice) 平均團(tuán)購(gòu)價(jià)from product group by categoryID orde

18、r by count(*), 平均團(tuán)購(gòu)價(jià) desc第32頁(yè)，共46頁(yè)。2.3.2 分組查詢(xún)顯示有商家信息的每一種類(lèi)型的商品數(shù)量和平均團(tuán)購(gòu)價(jià)，按照商品數(shù)量升序排列，如果商品數(shù)量相同，則按照平均團(tuán)購(gòu)價(jià)降序排列select categoryID 商品類(lèi)型編號(hào) , count(*) 商品數(shù)量 , avg(currentPrice) 平均團(tuán)購(gòu)價(jià)from product where shopID is not null group by categoryID order by count(*), 平均團(tuán)購(gòu)價(jià) desc第33頁(yè)，共46頁(yè)。2.3.2 分組查詢(xún)顯示有商家信息的每一種類(lèi)型的商品數(shù)量和平均團(tuán)購(gòu)價(jià)，

19、按照商品數(shù)量升序排列，如果商品數(shù)量相同，則按照平均團(tuán)購(gòu)價(jià)降序排列，但僅顯示平均團(tuán)購(gòu)價(jià)大于 100 元的分組信息select categoryID 商品類(lèi)型編號(hào) , count(*) 商品數(shù)量 , avg(currentPrice) 平均團(tuán)購(gòu)價(jià)from product where shopID is not null group by categoryID having avg(currentPrice)100 order by count(*), 平均團(tuán)購(gòu)價(jià) desc第34頁(yè)，共46頁(yè)。2.3.3 學(xué)生實(shí)踐練習(xí)根據(jù)“司途網(wǎng)”旅游系統(tǒng)數(shù)據(jù)庫(kù)表結(jié)構(gòu)，快速生成業(yè)務(wù)數(shù)據(jù)，并完成以下任務(wù)：（1）按客戶(hù)統(tǒng)

20、計(jì)客戶(hù) ID、訂單數(shù)量、訂單總金額、最高訂單金額、最低訂單金額和每份訂單平均金額，并按訂單總金額升序顯示。（2）按客戶(hù)統(tǒng)計(jì)訂單總訂購(gòu)人次數(shù)大于 5 的統(tǒng)計(jì)信息25第35頁(yè)，共46頁(yè)。連接查詢(xún)內(nèi)連接查詢(xún)簡(jiǎn)單多表查詢(xún)統(tǒng)計(jì)“零聚網(wǎng)”訂單數(shù)據(jù)2520第36頁(yè)，共46頁(yè)。2.4.1 內(nèi)連接查詢(xún) 內(nèi)連接的語(yǔ)法： 語(yǔ)法說(shuō)明： fieldlist：table1 表和 table2 表中的字段列。如果 fieldlist 取兩張表所有列，則可用“*”代替，此時(shí)會(huì)出現(xiàn)連接依據(jù)列重復(fù)，即 table1 表的 column1 與 table2 表的 column2 為重復(fù)列。 table1 INNER JOIN ta

21、ble2：將 table1 表與 table2 表進(jìn)行內(nèi)連接，INNER 可省略。 table1.column1=table2.column2：連接條件，其中 column1 和column2 為table1 表與table2 表的關(guān)聯(lián)列，通常它們?yōu)橥怄I列和主鍵列?！癷nner”可省略?！皐here condition”可省略，它為查詢(xún)條件表達(dá)式。SELECT fieldlist FROM table1 INNER JOIN table2 ON table1.column1=table2.column2where condition連接依據(jù)的列可能包含 null 值，null 值不與任何值匹配

22、（甚至和它本身）。第37頁(yè)，共46頁(yè)。2.4.2 簡(jiǎn)單多表查詢(xún)?nèi)绻?FROM 子句中，直接列出所有要連接的表，然后在 WHERE 子句中指定連接條件，此為簡(jiǎn)單多表查詢(xún)， 它與內(nèi)連接功能相同。使用兩表連接查詢(xún)語(yǔ)法：SELECT fieldlist FROM table1, table2WHERE table1.column1=table2.column2 and 其他條件第38頁(yè)，共46頁(yè)。2.4.2 簡(jiǎn)單多表查詢(xún)顯示有商品訂購(gòu)信息的全部顧客姓名select distinct c.customerName 顧客姓名 from orders o inner join customer c on

23、o.customerID=c.customerID 第39頁(yè)，共46頁(yè)。2.4.2 簡(jiǎn)單多表查詢(xún)使用簡(jiǎn)單連接查詢(xún)select distinct c.customerName 顧客姓名 from orders o, customer c where o.customerID=c.customerID第40頁(yè)，共46頁(yè)。2.4.2 簡(jiǎn)單多表查詢(xún)顯示顧客“雷亞波”的所有訂購(gòu)明細(xì)，查詢(xún)信息包括訂單號(hào)、商品標(biāo)題、團(tuán)購(gòu)價(jià)、團(tuán)購(gòu)商品數(shù)量以及下單時(shí)間，顯示結(jié)果按照下單時(shí)間升序排列select o.ordersID 訂單號(hào) , title 商品標(biāo)題 , currentPrice 團(tuán)購(gòu)價(jià) , quantity 團(tuán)

24、購(gòu)商品數(shù)量 ,ordersDate 下單時(shí)間 from orders o, customer c, ordersDetail od, product pwhere o.customerID=c.customerID and o.ordersID=od.ordersID and ductID=ductID and c.customerName= 雷亞波 order by ordersDate第41頁(yè)，共46頁(yè)。2.4.2 簡(jiǎn)單多表查詢(xún)顯示全部商品類(lèi)型信息select categoryID 訂單號(hào) , categoryName 類(lèi)型名稱(chēng) , p_categoryID 上級(jí)類(lèi)型編號(hào) from category第42頁(yè)，共46頁(yè)。2.4.2 簡(jiǎn)單多表查詢(xún)顯示商品大類(lèi)名為“購(gòu)物”的商品小類(lèi)信息select c2.categoryName 商品大類(lèi) , c1.categoryName 商品小類(lèi)