《《SQL語言基礎(chǔ)》PPT課件》由會員分享，可在線閱讀，更多相關(guān)《《SQL語言基礎(chǔ)》PPT課件（194頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、數(shù)據(jù)庫管理系統(tǒng),授課教師 吳濤,第4章 SQL語言基礎(chǔ),4.1 SQL語言概述 4.2系統(tǒng)提供的數(shù)據(jù)類型 4.3 用戶定義數(shù)據(jù)類型 4.4 T-SQL語言的一些基礎(chǔ)知識 4.5 變量,2/34,概述,SQL（Structured Query Language）是用戶操作關(guān)系數(shù)據(jù)庫的通用語言。 包含數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操作和數(shù)據(jù)控制等與數(shù)據(jù)庫有關(guān)的全部功能。 已成為關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。 所有的關(guān)系數(shù)據(jù)庫管理系統(tǒng)都支持SQL。,3/34,4.1 SQL語言概述,4.1.1 SQL語言的發(fā)展 4.1.2 SQL語言的特點(diǎn) 4.1.3 SQL語言功能概述,4/34,4.1.1 SQL語言的發(fā)展,

2、1986年10月美國ANSI公布最早的SQL標(biāo)準(zhǔn)。 1989年4月，ISO提出了具備完整性特征的SQL，稱為SQL-89（SQL1)。 1992年11月，ISO又公布了新的SQL標(biāo)準(zhǔn)，稱為SQL-92（SQL2) （以上均為關(guān)系形式）。 1999年頒布SQL-99（SQL3)，是SQL92的擴(kuò)展。,5/34,4.1.2 SQL語言的特點(diǎn),1. 一體化 2. 高度非過程化 3. 簡潔 4. 使用方式多樣,6/34,4.1.3 SQL語言功能概述,四部分：數(shù)據(jù)定義功能、數(shù)據(jù)控制功能、數(shù)據(jù)查詢功能和數(shù)據(jù)操縱功能。,7/34,4.2 系統(tǒng)提供的數(shù)據(jù)類型,4.2.1 數(shù)值類型 4.2.2 字符串類型 4

3、.2.3 日期時間類型 4.2.4 貨幣類型,8/34,準(zhǔn)確數(shù)值類型,9/34,近似數(shù)值數(shù)類型,10/34,字符串類型,普通編碼字符串類型 統(tǒng)一字符編碼字符串類型 二進(jìn)制字符串類型,11/34,普通編碼字符串類型,說明：如果在使用char(n)或varchar(n)類型時未指定n，則默認(rèn)長度為1。如果在使用CAST和CONVERT函數(shù)時未指定n，則默認(rèn)長度為30。,12/34,統(tǒng)一字符編碼字符串類型,13/34,二進(jìn)制字符串類型,14/34,日期時間類型,15/34,日期時間類型（續(xù)）,16/34,貨幣類型,17/34,4.3 用戶定義數(shù)據(jù)類型,實(shí)際上是為系統(tǒng)數(shù)據(jù)類型起了個別名，因此也稱為別名

4、類型。 當(dāng)在多個表中存儲語義相同的列時，一般要求這些列的數(shù)據(jù)類型和長度應(yīng)該完全一致。為避免語義相同的列在不同的地方定義不一致，可以使用用戶定義的數(shù)據(jù)類型。,18/34,創(chuàng)建用戶定義數(shù)據(jù)類型,CREATE TYPE schema_name. type_name FROM base_type ( precision , scale ) NULL | NOT NULL 例1創(chuàng)建一個名為telephone的數(shù)據(jù)類型，其相應(yīng)的系統(tǒng)數(shù)據(jù)類型為：char(8)，不允許空 CREATE TYPE telephone FROM CHAR(8) NOT NULL,19/34,4.4 T-SQL語言的一些基

5、礎(chǔ)知識,語句批 一組SQL語句集合 作為一個執(zhí)行單元 結(jié)束標(biāo)記：GO 腳本 存儲在文件中的SQL語句集合 注釋 單行注釋符：-- 多行注釋符：/* */,20/34,4.5 變量,4.5.1變量的種類 4.5.2 變量的聲明與賦值,21/34,變量種類,（1）局部變量： 變量名：用戶使用 （2）全局變量： 變量名：系統(tǒng)用于記錄信息,22/34,聲明局部變量,DECLARE local_variable AS data_type | = value , ... n local_variable：變量名。必須以“”開頭，且最多可包含128個字符。 data_type：任何系統(tǒng)提供的數(shù)據(jù)類型

6、或用戶定義的數(shù)據(jù)類型。但不能是text、ntext或image。,23/34,給變量賦值,SET local_variable = expression | local_variable += | -= | *= | /= | %= | ::= column_name AS computed_column_expression,36/38,基本表定義（續(xù)）, ::= column_name COLLATE collation_name NULL | NOT NULL CONSTRAINT constraint_name DEFAULT constant_express

7、ion | IDENTITY ( seed ,increment ) ,37/38,示例：創(chuàng)建學(xué)生表,CREATE TABLE Student ( Sno CHAR(7) PRIMARY KEY, Sname NCHAR(5)NOT NULL, SID CHAR(18)UNIQUE, Ssex NCHAR(1)DEFAULT 男, Sage TINYINTCHECK(Sage=15 AND Sage<=45), Sdept NVARCHAR(20) ),38/38,創(chuàng)建課程表,CREATE TABLE Course ( Cno CHAR(6) PRIMARY KEY, Cname

8、 NVARCHAR(20)NOT NULL, Credit NUMERIC(3,1)CHECK(Credit0), Semester TINYINT ),39/38,創(chuàng)建選課表,CREATE TABLE SC ( Sno CHAR(7) NOT NULL, Cno CHAR(6) NOT NULL, Grade TINYINT, PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) ),40/38,示例：創(chuàng)建有計(jì)算列的表,CREATE

9、 TABLE CompTable ( low int, high int, myavg AS (low + high)/2 ),41/38,示例：創(chuàng)建包含標(biāo)識列的表,標(biāo)識列的種子值為1，增量值也為1。 CREATE TABLE IDTable ( SID INT IDENTITY(1,1) NOT NULL, Name VARCHAR(20) ),42/38,說明,一般情況下，在插入數(shù)據(jù)時不能為標(biāo)識列提供值。標(biāo)識列的值是系統(tǒng)自動生成的。 如果確實(shí)要為標(biāo)識列提供值，則必須將表的IDENTITY_INSERT屬性設(shè)置為ON（默認(rèn)時該屬性的值為OFF）。 SET IDENTITY_INSERT d

10、atabase_name. schema_name . table ON | OFF ,43/38,修改表結(jié)構(gòu),可以使用ALTER TABLE語句實(shí)現(xiàn)。 ALTER TABLE語句可以對已定義的表進(jìn)行添加列、刪除列、修改列定義等操作，也可以進(jìn)行添加和刪除約束的操作。,44/38,,ALTER TABLE database_name.schema_name.| schema_name . table_name ALTER COLUMN column_name type_schema_name. type_name ( precision , scale | max ) NULL |

11、NOT NULL | ADD | | ,...n | DROP CONSTRAINT constraint_name | COLUMN column_name ,...n ; ,45/38,示例,例2為SC表添加“修課類別”列，此列的定義為：Type NCHAR(1)，允許空。 ALTER TABLE SC ADD Type NCHAR(1) NULL 例3將新添加的Type列的數(shù)據(jù)類型改為NCHAR(2)。 ALTER TABLE SC ALTER COLUMN Type NCHAR(2),46/38,示例,例4為Type列添加限定取值范圍為必修、重

12、修、選修的約束。 ALTER TABLE SC ADD CHECK(Type IN (必修, 重修, 選修) ) 例5刪除SC表的“Type”列。 ALTER TABLE SC DROP COLUMN Type,47/38,刪除表,DROP TABLE , 例6刪除test表。 DROP TABLE test 注意：如果被刪除的表中有其他表對它的外鍵引用約束，則必須先刪除外鍵所在的表，然后再刪除被引用的表。,48/38,5.2 分區(qū)表,分區(qū)表是把數(shù)據(jù)按某種標(biāo)準(zhǔn)劃分成區(qū)域存儲在不同的文件組中，使用分區(qū)可以快速而有效地管理和訪問數(shù)據(jù)子集，從而使大型表或索引更易于管理。 合理的使用分區(qū)會在很大

13、程度上提高數(shù)據(jù)庫的性能。 分區(qū)表是將表中的數(shù)據(jù)按水平方式劃分成不同的子集，這些數(shù)據(jù)子集存儲在數(shù)據(jù)庫的一個或多個文件組中。,49/38,比較適于進(jìn)行分區(qū)的情況,表中數(shù)據(jù)量大； 該表包含（或?qū)┮远喾N不同方式使用的大量數(shù)據(jù)。 數(shù)據(jù)是分段的，比如數(shù)據(jù)以年為分隔。 對表的常規(guī)維護(hù)操作只針對表的一個數(shù)據(jù)子集。,50/38,分區(qū)表特點(diǎn),分區(qū)表是從物理上將一個大表分成幾個小表，但從邏輯上來看，還是一個大表。 對用戶而言，所面對的依然是一個大表，他們不需要考慮操作的年份對應(yīng)的小表，用戶只要將記錄插入到大表邏輯表中就可以了，數(shù)據(jù)庫管理系統(tǒng)會自動將數(shù)據(jù)放置到它對應(yīng)的那個物理小表中。,51/38,創(chuàng)建分區(qū)表步驟

14、,1創(chuàng)建分區(qū)函數(shù)。告訴SQL Server以什么方式對表進(jìn)行分區(qū)。 2創(chuàng)建分區(qū)方案。將分區(qū)函數(shù)生成的分區(qū)映射到文件組中去。分區(qū)函數(shù)的作用是告訴SQL Server如何將數(shù)據(jù)進(jìn)行分區(qū)，而分區(qū)方案的作用則是告訴SQL Server將已分區(qū)的數(shù)據(jù)放在哪個文件組中。 3使用分區(qū)方案創(chuàng)建表。 說明：在創(chuàng)建分區(qū)表之前，最好先創(chuàng)建數(shù)據(jù)庫文件組。,52/38,1. 創(chuàng)建分區(qū)函數(shù),CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type ) AS RANGE LEFT | RIGHT FOR VALUES ( boundar

15、y_value , n ),53/38,示例,例1在 int 列上創(chuàng)建左側(cè)分區(qū)函數(shù)。下列分區(qū)函數(shù)將表分為四個分區(qū)。 CREATE PARTITION FUNCTION myRangePF1(int) AS RANGE LEFT FOR VALUES (1,100,1000);,54/38,示例,例2在int列上創(chuàng)建右側(cè)分區(qū)函數(shù)。 CREATE PARTITION FUNCTION myRangePF2(int) AS RANGE RIGHT FOR VALUES (1, 100, 1000),55/38,示例,例3在datetime列上創(chuàng)建右側(cè)分區(qū)函數(shù)。將表分成12個分區(qū)，每個分區(qū)對應(yīng)一個月的

16、值 CREATE PARTITION FUNCTION myDateRangePF1(datetime) AS RANGE RIGHT FOR VALUES ( 20110201, 20110301, 20110401, 20110501, 20110601, 20110701, 20110801, 20110901, 20111001, 20111101, 20111201);,56/38,示例,例3在字符列上創(chuàng)建右側(cè)分區(qū)函數(shù)。下列分區(qū)函數(shù)將表分為四個分區(qū)。 CREATE PARTITION FUNCTION myRangePF3(char(20)) AS RANGE RIGHT FOR V

17、ALUES (EX, RXE, XR);,57/38,2. 創(chuàng)建分區(qū)方案,CREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name ALL TO ( file_group_name | PRIMARY ,...n ) ; ,58/38,示例,例4.先創(chuàng)建一個分區(qū)函數(shù)，該函數(shù)將表分為四個分區(qū)。然后創(chuàng)建一個分區(qū)方案，在其中指定擁有這四個分區(qū)中每一個分區(qū)的文件組 CREATE PARTITION FUNCTION myRangePF1(int) AS RANGE LEFT FOR

18、VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg);,59/38,例4的分區(qū)情況,在某表上對分區(qū)依據(jù)列col1使用分區(qū)函數(shù)myRangePF1后，對該表的分區(qū)進(jìn)行分配的情況:,60/38,示例,例5. 創(chuàng)建將多個分區(qū)映射到同一個文件組的分區(qū)方案。如果要將所有分區(qū)都映射到同一個文件組，可使用ALL關(guān)鍵字。但如果是將多個（不是全部）分區(qū)映射到同一個文件組，則必須分別列出文件組名稱 CREATE PART

19、ITION FUNCTION myRangePF2(int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS2 AS PARTITION myRangePF2 TO ( test1fg, test1fg, test1fg, test2fg ),61/38,例5分區(qū)情況,在某表上對分區(qū)依據(jù)列col1使用分區(qū)函數(shù)myRangePF2后，對該表的分區(qū)進(jìn)行分配的情況,62/38,示例,例6. 創(chuàng)建將所有分區(qū)映射到同一個文件組的分區(qū)方案。 CREATE PARTITION FUNCTION myR

20、angePF3(int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS3 AS PARTITION myRangePF3 ALL TO ( test1fg ),63/38,示例,例7.創(chuàng)建指定“NEXT USED”文件組的分區(qū)方案。本示例所創(chuàng)建的分區(qū)方案列出的文件組數(shù)超過了關(guān)聯(lián)的分區(qū)函數(shù)所創(chuàng)建的分區(qū)數(shù)。 CREATE PARTITION FUNCTION myRangePF4(int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE

21、 PARTITION SCHEME myRangePS4 AS PARTITION myRangePF4 TO (test1fg, test2fg, test3fg, test4fg, test5fg) 執(zhí)行該語句時系統(tǒng)返回以下消息： 分區(qū)方案myRangePS4已成功創(chuàng)建。test5fg在分區(qū)方案myRangePS4中標(biāo)記為下次使用的文件組。,64/38,示例,例8. 本示例首先創(chuàng)建一個分區(qū)函數(shù)，將數(shù)據(jù)分為四個分區(qū)。然后創(chuàng)建一個分區(qū)方案，最后創(chuàng)建使用該分區(qū)方案的表。本示例假定數(shù)據(jù)庫中已經(jīng)存在文件組。 CREATE PARTITION FUNCTION myRangePF1(int) AS R

22、ANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg); GO CREATE TABLE PartitionTable ( col1 int, col2 char(10) ) ON myRangePS1 (col1);,65/38,第6章 數(shù)據(jù)操作語言,6.1 數(shù)據(jù)查詢語句 6.2 數(shù)據(jù)更改功能,,,66,6.1 數(shù)據(jù)查詢語句,6.1.1 查詢語句基本結(jié)構(gòu) 6.1.2 單表查

23、詢 6.1.3 多表連接查詢 6.1.4 使用TOP限制結(jié)果集 6.1.5 將查詢結(jié)果保存到新表中,67/130,6.1.1 查詢語句基本結(jié)構(gòu),SELECT -- 需要哪些列 FROM -- 來自于哪些表 WHERE -- 根據(jù)什么條件 GROUP BY HAVING ORDER BY ,68/130,部分能夠包含的內(nèi)容,SELECT ALL | DISTINCT TOP expression PERCENT WITH TIES ::= * | table_name | view_name | table_alias .* | table_name |

24、view_name | table_alias . column_name | $IDENTITY | expression AS column_alias | column_alias = expression ,...n ,69/130,6.1.2 單表查詢,1.選擇表中若干列 2.選擇表中的若干元組 3.對查詢結(jié)果排序 4.使用聚合函數(shù)統(tǒng)計(jì)數(shù)據(jù) 5.對數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì),70/130,1.選擇表中若干列：查詢指定列,在SELECT子句的中指定要查詢的屬性。 例1. 查詢?nèi)w學(xué)生的學(xué)號與姓名。 SELECT Sno，Sname FROM Student,71/1

25、30,示例,例2.查詢?nèi)w學(xué)生的姓名、學(xué)號和所在系。 SELECT Sname, Sno, Dept FROM Student 說明：查詢列表中的列順序與表中列定義的順序無關(guān)。,72/130,查詢?nèi)苛?如果要查詢表中的全部列，可以使用兩種方法： 在中列出所有的列名； 如果列的顯示順序與其在表中定義的順序相同，則可以簡單地在中寫星號“*”。,73/130,示例,例3 查詢?nèi)w學(xué)生的詳細(xì)記錄。 SELECT Sno, Sname, Sex, Sage, Dept FROM Student 等價(jià)于： SELECT * FROM Student,74/130,查詢經(jīng)過計(jì)算的列,SELECT子句中

26、的可以是表中存在的屬性列，也可以是表達(dá)式、常量或者函數(shù)。 例4 查詢?nèi)w學(xué)生的姓名及其出生年份。 SELECT Sname, year(getdate()) - year(Birthdate) FROM Student getdate():得到系統(tǒng)的當(dāng)前日期和時間 year():得到日期數(shù)據(jù)中年的部分,75/130,示例,例5. 含字符串常量的列。 SELECT Sname, 出生年份, year(getdate()) FROM Student,,76/130,指定列別名, 列名 | 表達(dá)式 AS 列別名 或 列別名 列名 | 表達(dá)式 例： SELECT Sname, year(get

27、date()) - year(Birthdate) AS 年齡 FROM Student,77/130,指定列別名后的查詢結(jié)果,,SELECT Sname, year(getdate()) - year(Birthdate) AS 年齡 FROM Student,78/130,用DISTINCT去掉結(jié)果中的重復(fù)行,SELECT DISTINCT Sno FROM SC,,,Sno,DISTINCT Sno,79/130,注意,SELECT語句不會自動去掉結(jié)果中的重復(fù)行，如果要求結(jié)果中不出現(xiàn)行，必須要明確地指出 DISTINCT是確保檢索后的每一行是唯一的，這種唯一性是相對于其他行來說的。,80

28、/130,2. 選擇表中的若干元組,查詢滿足條件的元組可通過 WHERE子句實(shí)現(xiàn)。,81/130,常用的查詢條件,82,（1）比較大小,例7查詢計(jì)算機(jī)系全體學(xué)生。 SELECT Sname FROM Student WHERE Dept = 計(jì)算機(jī)系 例8查詢所有年齡20歲以下的學(xué)生的姓名及年齡。 SELECT Sname, year(getdate()) - year(Birthdate) AS 年齡 FROM Student WHERE year(getdate())-year(Birth)<20,83/130,示例,例9.查詢成績不及格學(xué)生的學(xué)號。 SELECT DISTINCT Sn

29、o FROM SC WHERE Grade < 60,,84/130,（2）確定范圍,BETWEENAND NOT BETWEENAND 作用：查找屬性值在或不在指定范圍內(nèi)的元組。 說明： BETWEEN后是范圍的下限（低值） AND后是范圍的上限（高值）,85/130,示例,例10查詢考試成績在8090之間的學(xué)生學(xué)號、課程號和成績。,SELECT Sno, Cno, Grade FROM SC WHERE Grade BETWEEN 80 AND 90,等價(jià)于： SELECT Sno, Cno, Grade FROM SC WHERE Grade =80 AND Grade <=90,86/

30、130,示例,例11查詢考試成績不在8090之間的學(xué)生學(xué)號、課程號和成績。,SELECT Sno, Cno, Grade FROM SC WHERE Grade NOT BETWEEN 80 AND 90,等價(jià)于： SELECT Sno, Cno, Grade FROM SC WHERE Grade 90,87/130,示例：日期比較,例12. 設(shè)有圖書表（titles），其中包含書號（title_id）、類型（type）、價(jià)格（price）和出版日期（pubdate）列，查詢1991年6月出版的圖書信息： SELECT title_id, type, price, pubdate FROM

31、titles WHERE pubdate BETWEEN 1991/6/1 AND 1991/6/30,88/130,（3）確定集合（IN）,作用：用來查找屬性值屬于指定集合的元組。 格式： 列名 NOT IN (常量1, 常量2, ),89/130,示例,SELECT Sname, Sex FROM Student WHERE Dept IN（ 信息管理系, 通信工程系, 計(jì)算機(jī)系）,例13查詢信息管理系、通信工程系和計(jì)算機(jī)系學(xué)生的姓名和性別。,等價(jià)于： SELECT Sname, Sex FROM Student WHERE Dept 信息管理系, OR Dept 通信工程系,

32、 OR Dept 計(jì)算機(jī)系,90/130,示例,例14.查詢信息管理系、通信工程系和計(jì)算機(jī)系三個系之外的其他系學(xué)生的姓名和性別。 SELECT Sname, Sex FROM Student WHERE Dept NOT IN ( 信息管理系, 通信工程系, 計(jì)算機(jī)系) 等價(jià)于： SELECT Sname, Sex FROM Student WHERE Dept!= 信息管理系 AND Dept!= 通信工程系 AND Dept!= 計(jì)算機(jī)系,91/130,（4）字符串匹配（LIKE）,列名 NOT LIKE ESCAPE 匹配串中可包含如下通配符： %（百分號）：匹配0個或多個字符。 _（下

33、劃線）：匹配一個字符。 ：匹配方括號中的任何一個字符。 ：不匹配方括號中的任何一個字符。 若要比較的字符是連續(xù)的，則可以用連字符“-”表達(dá)，例如，要匹配b、c、d、e中的任何一個字符，則可以表示為：b-e,92/130,示例,例15.查詢姓“張”的學(xué)生詳細(xì)信息。 SELECT * FROM Student WHERE Sname LIKE 張%,93/130,示例,例16.查詢姓“張”、姓“李”和姓“劉”的學(xué)生的詳細(xì)信息。 SELECT * FROM Student WHERE Sname LIKE 張李劉%,94/130,示例,例17.查詢名字的第2個字為“小”或“大”的學(xué)生的姓名和學(xué)號。

34、 SELECT Sname, Sno FROM Student WHERE Sname LIKE _小大% 例18查詢所有不姓“劉”的學(xué)生姓名。 SELECT Sname FROM Student WHERE Sname NOT LIKE 劉%,95/130,示例,例19.在Student表中查詢學(xué)號的最后一位不是2、3、5的學(xué)生信息。 SELECT * FROM Student WHERE Sno LIKE %235,96/130,轉(zhuǎn)義字符,如果要查找的字符串正好含有通配符，比如下劃線或百分號，就需要用ESCAPE來說明。 ESCAPE 轉(zhuǎn)義字符 其中“轉(zhuǎn)義字符”是任何一個有效的字符，

35、在匹配串中也包含這個字符，表明位于該字符后面的那個字符將被視為普通字符，而不是通配符。,97/130,示例,查找field1字段中包含字符串“30%”的記錄： WHERE field1 LIKE %30!%% ESCAPE ! 查找field1字段中包含下劃線（_）的記錄： WHERE field1 LIKE %!_% ESCAPE !,98/130,（5）涉及空值的查詢,空值是未確定的值或其值尚不知道。 例如，學(xué)生選課，在開學(xué)初學(xué)生只有選課記錄，沒有修課成績，這時成績成績一項(xiàng)的值就是空值。 判斷列取值為空的語句格式為： 列名IS NULL 判斷列取值不為空的語句格式為： 列名 IS NOT

36、NULL,99/130,示例,例20.查詢還沒有考試的學(xué)生的學(xué)號和相應(yīng)的課程號。 SELECT Sno, Cno FROM SC WHERE Grade IS NULL,100/130,注意,空值不是一個確定的值，所以不可以用等于或不等于來比較或衡量； 空值只能說是空值（IS NULL）或不是空值（IS NOT NULL）。,101/130,（6）多重條件查詢,當(dāng)需要多個查詢條件時，可以在WHERE子句中使用邏輯運(yùn)算符AND和OR來組成多條件查詢。 例21.查詢計(jì)算機(jī)系男生的姓名。 SELECT Sname FROM Student WHERE Dept = 計(jì)算機(jī)系 AND Sex =

37、男,102/130,示例,例22.查詢C002和C003課程中考試成績在8090的學(xué)生的學(xué)號、課程號和成績。 SELECT Sno, Cno, Grade FROM SC WHERE Cno IN( C002, C003) AND Grade BETWEEN 80 AND 90 注：OR的優(yōu)先級小于AND，要改變運(yùn)算的順序可以通過加括號的方式實(shí)現(xiàn)。 SELECT Sno, Cno, Grade FROM SC WHERE (Cno = C001 OR Cno = C002) AND Grade BETWEEN 80 AND 90,103/130,3. 對查詢結(jié)果排序,可用ORDER B

38、Y子句對查詢結(jié)果進(jìn)行排序。 ORDER BY ASC | DESC , 說明：按進(jìn)行升序（ASC）或降序（DESC）排序。 當(dāng)用多個列排序時，這些列在該子句中出現(xiàn)的順序決定了對結(jié)果集進(jìn)行排序的方式。,104/130,示例,例23.查詢修了“C002”課程的學(xué)生的學(xué)號及成績，查詢結(jié)果按成績降序排列。 SELECT Sno, Grade FROM SC WHERE Cno = C002 ORDER BY Grade DESC,105/130,示例,例24.查詢?nèi)w學(xué)生詳細(xì)信息，結(jié)果按系名升序排列，同一個系的學(xué)生按出生日期降序排列。 SELECT * FROM Student ORDER BY

39、 Dept ASC, Birthdate DESC,106/130,例24執(zhí)行結(jié)果,,ORDER BY Dept, Birthdate DESC,107/130,4使用聚合函數(shù)匯總數(shù)據(jù),也稱為集合函數(shù)或聚合函數(shù)， 其作用是對一組值進(jìn)行計(jì)算并返回一個統(tǒng)計(jì)結(jié)果。,108/130,聚合函數(shù),COUNT（*）：統(tǒng)計(jì)表中元組的個數(shù)。 COUNT（DISTINCT ）：統(tǒng)計(jì)列值個數(shù) SUM（）：計(jì)算列值的和值（必須是數(shù)值型列）。 AVG（）：計(jì)算列值的平均值（必須是數(shù)值型列）。 MAX（）：得到列值的最大值。 MIN（）：得到列值的最小值。 除COUNT（*）外，其他函數(shù)在計(jì)算過程中均忽略NULL值。,1

40、09/130,示例,例25. 統(tǒng)計(jì)學(xué)生總?cè)藬?shù)。 SELECT COUNT(*) FROM Student,,10,110/130,示例,例26.統(tǒng)計(jì)選修了課程的學(xué)生人數(shù)。 SELECT COUNT(DISTINCT Sno) FROM SC,,4,111/130,示例,例. 計(jì)算學(xué)號為“0811101”的學(xué)生的考試總成績。 SELECT SUM(Grade) FROM SC WHERE Sno = 0811101,,322,112/130,示例,例28.計(jì)算“0831103”學(xué)生的平均成績。 SELECT AVG(Grade) FROM SC WHERE Sno = 0831103,,71

41、,113/130,示例,例28.查詢“C001”課程考試成績的最高分和最低分。 SELECT MAX(Grade) 最高分, MIN(Grade) 最低分 FROM SC WHERE Cno = C001,,96,50,,114/130,注意,聚合函數(shù)不能出現(xiàn)在WHERE子句中。 例如，查詢學(xué)分最高的課程名，如下寫法是錯誤的： SELECT Cname FROM Course WHERE Credit = MAX(Credit),115/130,5對數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì),作用：可以控制計(jì)算的級別：對全表還是對一組。 目的：細(xì)化聚合函數(shù)的作用對象。 分組語句的一般形式： GROUP BY HAVI

42、NG ,116/130,使用GROUP BY子句,例29.統(tǒng)計(jì)每門課程的選課人數(shù)，列出課程號和選課人數(shù)。 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno 對查詢結(jié)果按Cno的值分組，所有具有相同Cno值的元組為一組，然后再對每一組使用COUNT計(jì)算，求出每組的學(xué)生人數(shù)。,117/130,示例,例30.統(tǒng)計(jì)每個學(xué)生的選課門數(shù)和平均成績。 SELECT Sno 學(xué)號, COUNT(*) 選課門數(shù), AVG(Grade) 平均成績 FROM SC GROUP BY Sno,118/130,注意,GROUP BY子句中的分組依據(jù)列必須是表中存在的列名，不能使用

43、AS子句指派的列別名。 帶有GROUP BY 子句的SELECT語句的查詢列表中只能出現(xiàn)分組依據(jù)列和統(tǒng)計(jì)函數(shù)，因?yàn)榉纸M后每個組只返回一行結(jié)果。,119/130,示例,例31. 帶WHERE子句的分組。統(tǒng)計(jì)每個系的女生人數(shù)。 SELECT Dept, Count(*) 女生人數(shù) FROM Student WHERE Sex = 女 GROUP BY Dept,120/130,示例,例32 按多個列分組。統(tǒng)計(jì)每個系的男生人數(shù)和女生人數(shù)以及男生的最大年齡和女生的最大年齡。結(jié)果按系名的升序排序。 SELECT Dept, Sex, Count(*) 人數(shù), Max(Sage) 最大年齡 FROM S

44、tudent GROUP BY Dept, Sex ORDER BY Dept,121/130,使用HAVING子句,HAVING用于對分組自身進(jìn)行限制，它有點(diǎn)象WHERE子句，但它用于組而不是對單個記錄。,122/130,示例,例33. 查詢選課門數(shù)超過3門的學(xué)生的學(xué)號和選課門數(shù)。 SELECT Sno, Count(*) 選課門數(shù) FROM SC GROUP BY Sno HAVING COUNT(*) 3 處理過程為：先執(zhí)行GROUP BY子句對SC表數(shù)據(jù)按Sno進(jìn)行分組，然后再用統(tǒng)計(jì)函數(shù)COUNT分別對每一組進(jìn)行統(tǒng)計(jì)，最后篩選出統(tǒng)計(jì)結(jié)果滿足大于3的組。,123/130,示例,例34.

45、查詢選課門數(shù)大于等于4門的學(xué)生的平均成績和選課門數(shù)。 SELECT Sno, AVG(Grade) 平均成績, COUNT(*) 選課門數(shù) FROM SC GROUP BY Sno HAVING COUNT(*) = 4,124/130,一些說明,WHERE子句用來篩選FROM子句中指定的數(shù)據(jù)源所產(chǎn)生的行數(shù)據(jù)。 GROUP BY子句用來對經(jīng)WHERE子句篩選后的結(jié)果數(shù)據(jù)進(jìn)行分組。 HAVING子句用來對分組后的統(tǒng)計(jì)結(jié)果再進(jìn)行篩選。,125/130,一些說明,可以在分組操作之前應(yīng)用的篩選條件，在WHERE子句中指定更有效。 在HAVING子句中指定的篩選條件應(yīng)該是那些必須在執(zhí)行分組操作之后應(yīng)

46、用的篩選條件。 將所有應(yīng)該在分組之前進(jìn)行的篩選條件放在WHERE子句中而不是HAVING子句中。,126/130,示例,例35. 查詢計(jì)算機(jī)系和信息管理系每個系的學(xué)生人數(shù)。 SELECT Dept, COUNT(*) FROM Student GROUP BY Dept HAVING Dept in (計(jì)算機(jī)系, 信息管理系) SELECT Dept, COUNT (*) FROM Student WHERE Dept in ( 計(jì)算機(jī)系, 信息管理系) GROUP BY Dept,,X,127/130,示例,例36查詢每個系的男生人數(shù)。 SELECT Dept, COUNT(*) F

47、ROM Student WHERE Sex = 男 GROUP BY Dept 該查詢語句不能寫成： SELECT Dept, COUNT(*) FROM Student GROUP BY Dept HAVING Sex = 男,128/130,6.1.3 多表連接查詢,若一個查詢同時涉及兩個或兩個以上的表，則稱之為連接查詢。 連接查詢是關(guān)系數(shù)據(jù)庫中最主要的查詢，主要包括： 內(nèi)連接 外連接：左外連接、右外連接,129/130,內(nèi)連接,是一種最常用的連接類型。使用內(nèi)連接時，如果兩個表的相關(guān)字段滿足連接條件，則從這兩個表中提取數(shù)據(jù)并組合成新的記錄。 在非ANSI標(biāo)準(zhǔn)的實(shí)現(xiàn)中，連接操作是在WHE

48、RE子句中執(zhí)行的theta連接 在ANSI SQL-92中，連接是在JOIN子句中執(zhí)行的ANSI連接。 我們介紹ANSI方式的連接格式： FROM 表1 INNER JOIN 表2 ON ,130/130,連接基礎(chǔ)知識,連接查詢中用于連接兩個表的條件稱為連接條件或連接謂詞。 一般格式為： =,必須是可比列,,,131/130,執(zhí)行連接操作的大致過程,首先取表1中的第1個元組，然后從頭開始掃描表2，逐一查找滿足連接條件的元組， 找到后就將表1中的第1個元組與該元組拼接起來，形成結(jié)果表中的一個元組。 表2全部查找完畢后，再取表1中的第2個元組，然后再從頭開始掃描表2， 重復(fù)這個過程，直到表1中的全

49、部元組都處理完畢為止。,132/130,示例,SELECT * FROM Student INNER JOIN SC ON Student.Sno=SC.Sno,133/130,去掉重復(fù)列,SELECT Student.Sno, Sname, Sex, Sage, Dept, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno,134/130,示例,例38. 查詢計(jì)算機(jī)系學(xué)生的修課情況，要求列出學(xué)生的名字、所修課的課程號和成績。 SELECT Sname, Cno, Grade FROM Student JOIN SC ON S

50、tudent.Sno = SC.Sno WHERE Dept = 計(jì)算機(jī)系,135/130,指定列別名, AS 為表指定別名可以簡化表的書寫。 SELECT Sname, Cno, Grade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Dept = 計(jì)算機(jī)系 注意：當(dāng)為表指定了別名時，在查詢語句中的其他地方，所有用到表名的地方都要使用別名，而不能再使用原表名。,136/130,示例,例39. 查詢“信息管理系”修了“計(jì)算機(jī)文化學(xué)” 的學(xué)生姓名和成績。 SELECT Sname, Grade FROM Student s JOIN SC ON

51、 s.Sno = SC. Sno JOIN Course c ON c.Cno = SC.Cno WHERE Dept = 信息管理系 AND Cname = 計(jì)算機(jī)文化學(xué),137/130,示例,例40. 查詢所有選修了Java課程的學(xué)生情況，列出學(xué)生姓名和所在系。 SELECT Sname, Dept FROM Student S JOIN SC ON S.Sno = SC. Sno JOIN Course C ON C.Cno = SC.cno WHERE Cname = Java,138/130,示例,例41.有分組的多表連接查詢。統(tǒng)計(jì)每個系的學(xué)生的考試平均成績。 SELECT Dept

52、, AVG(grade) as AverageGrade FROM student S JOIN SC ON S.Sno = SC.Sno GROUP BY Dept,139/130,示例,例42. 有分組和行選擇條件的多表連接查詢。統(tǒng)計(jì)計(jì)算機(jī)系學(xué)生每門課程的選課人數(shù)、平均成績、最高成績和最低成績。 SELECT Cno, COUNT(*) AS Total, AVG(Grade) as AvgGrade, MAX(Grade) as MaxGrade, MIN(Grade) as MinGrade FROM Student S JOIN SC ON S.Sno = SC.Sno

53、 WHERE Dept = 計(jì)算機(jī)系 GROUP BY Cno,140/130,自連接,是特殊的內(nèi)連接。 相互連接的表物理上為同一張表。 必須為兩個表取別名，使之在邏輯上成為兩個表。 FROM 表1 AS T1 -- 在內(nèi)存中生成“T1” JOIN 表1 AS T2 -- 在內(nèi)存中生成“T2”,141/130,示例,例43. 查詢與劉晨在同一個系學(xué)習(xí)的學(xué)生的姓名和所在的系。 SELECT S2.Sname, S2.Dept FROM Student S1 JOIN Student S2 ON S1.Dept = S2.Dept WHERE S1.Sname = 劉晨 AND S2.Sname

54、!= 劉晨,142/130,示例,例44. 查詢與“數(shù)據(jù)結(jié)構(gòu)”在同一個學(xué)期開設(shè)的課程的課程名和開課學(xué)期 SELECT C1.Cname, C1.Semester FROM Course C1 JOIN Course C2 ON C1.Semester = C2.Semester WHERE C2.Cname = 數(shù)據(jù)結(jié)構(gòu),143/130,示例,例45. 查詢至少被兩個學(xué)生選的課程的課程號。 SELECT DISTINCT a.Cno FROM SC a JOIN SC b ON a.Cno = b.Cno AND a.Sno != b.Sno 或者： SELECT Cno FROM SC GR

55、OUP BY Cno HAVING COUNT(*) 1,144/130,外連接,外連接會返回FROM子句中提到的至少一個表的所有行，只要這些行符合任何WHERE或HAVING搜索條件。 左外連接 FROM 表1 LEFT OUTER JOIN 表2 ON 右外連接 FROM 表1 RIGHT OUTER JOIN 表2 ON ,145/130,內(nèi)連接與外連接示意圖,A與B的左外連接結(jié)果： A + C,A與B的右外連接結(jié)果： B+ C,A與B的內(nèi)連接結(jié)果： C,146/130,示例,例46 查詢?nèi)w學(xué)生的選課情況，包括選修了課程的學(xué)生和沒有選修課程的學(xué)生。 SELECT Stu

56、dent.Sno, Sname, Cno, Grade FROM Student LEFT JOIN SC ON Student.Sno = SC.Sno 或： SELECT Student.Sno, Sname, Cno, Grade FROM SC RIGHT JOIN Student ON Student.Sno = SC.Sno,147/130,例46執(zhí)行部分結(jié)果示例,148/130,示例,例47. 查詢沒人選的課程的課程名。 SELECT Cname FROM Course C LEFT JOIN SC ON C.Cno = SC.Cno WHERE SC.Cno IS NU

57、LL,149/130,示例,例48. 查詢計(jì)算機(jī)系沒有選課的學(xué)生，列出學(xué)生姓名和性別。 SELECT Sname,Dept,Cno,grade FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Dept = 計(jì)算機(jī)系 AND SC.Sno IS NULL,150/130,示例,例49. 統(tǒng)計(jì)計(jì)算機(jī)系每個學(xué)生的選課門數(shù)，包括沒有選課的學(xué)生。 SELECT S.Sno AS 學(xué)號, COUNT(SC.Cno) AS 選課門數(shù) FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Dept =

58、計(jì)算機(jī)系 GROUP BY S.Sno,151/130,示例,例50. 查詢信息管理系選課門數(shù)少于3門的學(xué)生的學(xué)號和選課門數(shù)，包括沒有選課的學(xué)生。查詢結(jié)果按選課門數(shù)遞增排序。 SELECT S.Sno AS 學(xué)號,COUNT(SC.Cno) AS 門數(shù) FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Dept = 信息管理系 GROUP BY S.Sno HAVING COUNT(SC.Cno) < 3 ORDER BY COUNT(SC.Cno) ASC,152/130,說明,當(dāng)外連接與內(nèi)連接混合使用時，對側(cè)的概念的理解就非常重要。 所

59、有查詢語句中JOIN左側(cè)或之前的所有表只作為一個表（這個表有可能是多個連接操作產(chǎn)生的結(jié)果表），同樣，所有查詢語句中JOIN右側(cè)或之后的表也只作為一個表。,153/130,示例：設(shè)有三張表,154/130,查詢1,查詢?nèi)抗?yīng)商的VendorName及其在Vendors和VendorAddress兩個表中的VendorID。 SELECT v.VendorID, v.VendorName, va.VendorID FROM Vendors v LEFT OUTER JOIN VendorAddress va ON v.VendorID = va.VendorID,155/130,查詢2,查詢?nèi)?/p>

60、供應(yīng)商的VendorName及其Address。由于并不要求返回全部的地址，因此對Address表可以采用內(nèi)連接 SELECT v.VendorName, a.Address FROM Vendors v LEFT OUTER JOIN VendorAddress va ON v.VendorID = va.VendorID JOIN Address a ON va.AddressID = a.AddressID,丟失了Vendor3,156/130,修改查詢2,（1）添加一個外連接操作 SELECT v.VendorName, a.Address FROM Vendors v LEFT OU

61、TER JOIN VendorAddress va ON v.VendorID = va.VendorID LEFT OUTER JOIN Address a ON va.AddressID = a.AddressID （2）改變連接操作順序 SELECT v.VendorName, a.Address FROM VendorAddress va JOIN Address a ON va.AddressID = a.AddressID RIGHT OUTER JOIN Vendors v ON v.VendorID = va.VendorID,157/130,4全外連接,若要保留兩個表中都不滿

62、足連接條件的數(shù)據(jù)行，則可使用完全外部連接（簡稱全外連接）。 SQL Server 提供的全外連接運(yùn)算符為： FULL OUTER JOIN 該操作的結(jié)果將包含兩個表中的所有行，不論另一個表中是否有匹配的值。,158/130,實(shí)例,select * from T1 full join T2 on T1.T1_C11 = T2.T2_C11,,159/130,6.1.4 使用TOP限制結(jié)果集行數(shù),TOP (expression) percent WITH TIES expression ：指定返回行數(shù)的數(shù)值表達(dá)式。 TOP n percnet：取查詢結(jié)果的前n%行。 WITH TIES：包括并列的

63、結(jié)果。 TOP謂詞寫在SELECT單詞的后邊,160/130,示例,例52.查詢考試成績最高的三個成績，列出學(xué)號、課程號和成績。 SELECT TOP 3 Sno, Cno, Grade FROM SC ORDER BY Grade DESC,161/130,示例,例53. 查詢Java考試成績最高的前三名的學(xué)生的姓名、所在系和VB考試成績。 SELECT TOP 3 WITH TIES Sname, Dept, Grade FROM Student S JOIN SC on S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname

64、= Java ORDER BY Grade DESC 注意：如果在TOP子句中使用了WITH TIES謂詞，則要求必須使用ORDER BY子句對查詢結(jié)果進(jìn)行排序,162/130,示例,例54. 查詢選課人數(shù)最少的兩門課程（不包括沒有人選的課程），列出課程號和選課人數(shù)。 SELECT TOP 2 WITH TIES Cno, COUNT(*) 選課人數(shù) FROM SC GROUP BY Cno ORDER BY COUNT(Cno) ASC,163/130,示例,例55. 查詢計(jì)算機(jī)系選課門數(shù)超過2門的學(xué)生中，考試平均成績最高的前2名（包括并列的情況）學(xué)生的學(xué)號、選課門數(shù)和平均成績。 SELEC

65、T TOP 2 WITH TIES S.Sno, COUNT(*) 選課門數(shù),AVG(Grade) 平均成績 FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Dept = 計(jì)算機(jī)系 GROUP BY S.sno HAVING COUNT(*) 2 ORDER BY AVG(Grade) DESC,164/130,6.1.5 將查詢結(jié)果保存到新表中,如果希望將查詢結(jié)果保存到一個表中，則可以通過在SELECT語句中使用INTO子句實(shí)現(xiàn)。 包含INTO子句的SELECT語句的簡單語法格式可描述為： SELECT 查詢列表序列 INTO FROM 數(shù)據(jù)源

66、 -- 其他行過濾、分組等語句,165/130,語句功能,根據(jù)查詢列表序列的內(nèi)容創(chuàng)建一個新表，新表中各列的列名就是查詢結(jié)果中顯示的列標(biāo)題，列的數(shù)據(jù)類型是這些查詢列在原表中定義的數(shù)據(jù)類型。 執(zhí)行查詢語句并將查詢的結(jié)果按列對應(yīng)順序保存到該新表中。,166/130,局部臨時表和全局臨時表,局部臨時表在表名前加一個#來標(biāo)識。局部臨時表的生存期為創(chuàng)建此局部臨時表的連接的生存期，它只能在創(chuàng)建此局部臨時表的當(dāng)前連接中使用； 全局臨時表在表名前加兩個#來標(biāo)識。全局臨時表的生存期為創(chuàng)建全局臨時表的連接的生存期，并且在生存期內(nèi)可以被所有的連接使用。,167/130,示例,例56將計(jì)算機(jī)系的學(xué)生信息保存到#ComputerStudent局部臨時表中。 SELECT Sno, Sname, Sex, Sage INTO #ComputerStudent FROM Student WHERE Dept = 計(jì)算機(jī)系,168/130,示例,例57將選了Java課程的學(xué)生的學(xué)號及成績存入永久表Java_Grade中。 SELECT Sno, Grade INTO Java_Grade FROM SC JOIN