第5章-結構化查詢語言SQLppt課件(全)

1、Access2010數(shù)據(jù)庫案例教程第5章 結構化查詢語言SQL本章選修學習要點（本章內容學生可以自學） SQL語言的基本概念、特點 SQL語言的功能 SQL語言的用法 通過本章的學習，了解SQL語言及其標準的發(fā)展、SQL語言的特點及分類、視圖相關語句，熟悉SQL語言中各種語句的語法，熟悉SQL數(shù)據(jù)定義語言（DDL）語句，掌握SQL語言中數(shù)據(jù)查詢、數(shù)據(jù)操縱語言的詳細語法，并能深刻理解、綜合應用，以便為今后深層次的學習打下更加堅實的基礎。學習目標重點難點重點：1、SELECT命令的使用，如：使用SELECT命令實現(xiàn)基本查詢、條件查詢、嵌套查詢、多表查詢和連接查詢。2、查詢結果的處理，輸出重定向，查

2、詢結果的排序、查詢分組和統(tǒng)計等。3、用查詢設計器構造SELECT語句。難點：1、SELETE命令的使用。特別是條件查詢、嵌套查詢、多表查詢和連接查詢的實現(xiàn)。2、查詢結果的排序、分組和統(tǒng)計處理。 5.1 SQL語言概述SQLStructured Query Language，結構化查詢語言1986年，由美國ANSI確定為關系數(shù)據(jù)庫的標準語言。1987年，ISO將其定為國際標準。1989年，ISO提出了具有完整性特征的SQL，成為SQL891992年公布了SQL的新標準，即SQL-92。SQL99SQL3SQL語言的特點SQL是一種結構一體化的語言。它包括了數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱和數(shù)據(jù)控制等

3、功能，可以完成數(shù)據(jù)庫活動中的全部工作。SQL語言的組成數(shù)據(jù)定義：CREATE、DROP(刪除)、 ALTER(修改) (刪除某記錄)數(shù)據(jù)操縱：INSERT、UPDATE、DELETE數(shù)據(jù)控制：GRANT、REVOKE數(shù)據(jù)查詢：SELECT(選擇工作區(qū))SQL語言的組成(1)數(shù)據(jù)定義語言(Data Definition Language，簡稱DDL) 用于定義SQL模式、基本表、視圖和索引。(2)查詢語言(Query Language，簡稱QL) 用于數(shù)據(jù)查詢。(3)數(shù)據(jù)操縱語言(Data Manipulation Language，簡稱DML) 用于數(shù)據(jù)的增、刪、修改。(4)數(shù)據(jù)控制語言(Da

4、ta Control Language，簡稱DCL) 用于數(shù)據(jù)訪問權限的控制。5.2 數(shù)據(jù)定義語言CREATE ALTERDROP創(chuàng)建表在CREATE TABLE中可以使用的數(shù)據(jù)類型及說明：C、N 、 D 、 T 、L、M、G創(chuàng)建表CREATE TABLE定義基本表的語句格式為 :CREATE TABLE( NOT NULL UNIQUE ,屬性名2類型2 NOT NULL UNIQUE ) 其他參數(shù)；例5-1：學生成績數(shù)據(jù)庫含有三張表：學生關系：S(SNO,SNAME,SEX, AGE, DNAME)課程關系：C(CNO, CNAME, CREDIT, PRE_CNO)選課關系：SC(SNO

5、, CNO, SCORE) 可用下列SQL語句來實現(xiàn)： 創(chuàng)建表CREATE TABLE CREATE TABLE S ( SNO CHAR(6) PRIMARY KEY， SNAME CHAR(8) NOT NULL， AGE SMALLINT， SEX CHAR(1)， DNAME VARCHAR(12)；CREATE TABLE C ( CNO CHAR(2) NOT NULL, CNAME VARCHAR(24) NOT NULL, CREDIT SMALLINT, PRE_CNO CHAR(2), PRIMARY KEY(CNO);CREATE TABLE SC ( SNO CHAR(

6、6) NOT NULL, CNO CHAR(2) NOT NULL, SCORE SMALLINT, PRIMARY KEY(SNO,CNO), FOREIGN KEY(SNO) REFERENCES S ON DELETE CASCADE, FOREIGN KEY(CNO) REFERENCES C ON DELETE NO ACTION);創(chuàng)建表例1、使用命令建立數(shù)據(jù)庫XSK，用SQL命令在該庫中建立STUDENT表，結構及要如下。字段名字段類型字段長度特殊要求學號C7主索引姓名C8不能為空性別C2年齡N3入學年月D是否黨員L備注M創(chuàng)建表例2、在XSK中建立score表，結構如下。字段名字

7、段類型字段長度小數(shù)位數(shù)學號C7課號C5期末N52修改表結構ALTER TABLE添加字段：ALTER TABLE 表名 ADD COLUMN 字段名 字段類型 (寬度)例：為SCORE表添加添加兩個字段，“平時(N(5,1)”和“期中(N(5,1)”。修改表結構修改字段ALTER TABLE 表名 ALTER COLUMN 字段名 字段類型(寬度)例：在score中，修改“期末”字段為N(5，1)。修改表結構刪除字段ALTER TABLE 表名 DROP COLUMN 字段名例：在student表中，刪除“是否黨員”字段。修改表結構字段更名ALTER TABLE 表名 RENAME COLUM

8、N 原字段名 TO 新字段名例：在STUDENT表中，把“備注”字段名改為“其它情況”。修改表結構改變表的結構時，索引表達式、字段和表的有效性規(guī)則、命令、函數(shù)等等可能仍會引用原始字段名，因此有時不能執(zhí)行命令。刪除表DROP TABLEDROP TABLE 表名執(zhí)行了 DROP TABLE 之后，所有與被刪除表有關信息都將丟失。例：刪除SCORE表。5.3 數(shù)據(jù)操縱語言INSERTDELETEUPDATE插入記錄INSERT INSERT INTO 表名 字段名1，字段名2 VALUE(表達式1,表達式2)如果要插入表中所有字段的數(shù)據(jù)，可省略字段名。如果省略了字段名，則必須按照表結構定義字段的順

9、序來指定字段值。例1、向STUDENT表中添加記錄。插入記錄INSERT INTO 表名 FROM ARRAY 數(shù)組名INSERT INTO 表名 FROM MEMVAR把內存變量的內容插入到與它同名的字段中。如果某一字段不存在同名的內存變量，則該字段為空。例1、從數(shù)組向表中添加記錄。例2、利用內存變量向表中添加記錄。刪除記錄DELETEDELETE FROM 數(shù)據(jù)庫名 表名WHERE 邏輯表達式1 AND|OR 邏輯表達式2為指定的表中的記錄加刪除標記。如果要刪除當前數(shù)據(jù)庫中的表的記錄，可省略數(shù)據(jù)庫名，否則必須加上包含有該表的數(shù)據(jù)庫名。在數(shù)據(jù)庫名的后面、表名的前面包含感嘆號 (!) 分隔符。

10、例：將STUDENT表中所有男生的記錄刪除。UPDATEUPDATE 數(shù)據(jù)庫名！表名 SET 字段名1=表達式1,字段名2=表達式2WHERE 邏輯表達式1 AND|OR 邏輯表達式2如果省略了 WHERE 子句，在列中的每一行都用相同的值更新。例：將STUDENT表中所有99級學生的“入學年月”改為“1999/09/10”。 索引的建立和刪除建立索引的語句格式為： CREATE UNIQUE INDEX 索引名 ON 基本表名(ASC|DESC, ASC|DESC)；例如，對表S建立以下索引 CREATE UNIQUE INDEX SNO_INDEX ON S (SNO)； CREATE U

11、NIQUE INDEX SNAME_ADDR_INDEX ON S (SNAME ASC，HOSTADDR DESC)；刪除索引的語句格式： DROP INDEX 索引名；5.4 數(shù)據(jù)查詢SELECTSELECT FROMWHERE功能：從一個表或多個表中查詢數(shù)據(jù)。SELECT指定查詢哪些字段使用WHERE子句限定記錄使用FROM子句限定表名含義：根據(jù)WHERE子句的條件表達式，從FROM子句指定的表中找出滿足條件的記錄，再按SELECT子句中的字段表達式，選出記錄形成結果表。在VFP中，查詢就是一個擴展名為QPR的查詢文件，其中的內容就是一條SELECT語句。5.4 數(shù)據(jù)查詢SELECT5.

12、4.1 基本查詢基本查詢就是單表查詢，：SELECT ALL|DISTINCT *|列表達式 FROM 學生ALL:查詢結果中包含所有行 ( 包括重復值 ), 是默認設置。DISTINCT:在查詢結果中剔除重復的行。注意 每一個 SELECT 子句只能使用一次DISTINCT。*表示所有字段。列表達式可以是字段名，表達式，字符串常量、函數(shù)等?；静樵兝?:列出“學生”表中所有同學名單SELECT * FROM 學生例2:列出成績表中所有學生的學號，去掉重復值。SELECT DISTINCT 學號 FROM 成績SELECT命令中的函數(shù)例3、列出所有學生的學號，姓名和入學成績，其中入學成績四舍五

13、入保留整數(shù)。SELECT 學號,姓名,ROUND(入學成績,0) AS “入學成績” FROM 學生AS :指定查詢結果中列的標題?？墒÷?。例4、列出學生表中所有學生的年齡。SELECT 姓名, YEAR(DATE()-YEAR(出生日期) AS 年齡 FROM 學生例5、求出學生總數(shù)。SELECT COUNT（*） FROM 學生例6、查詢選修了課程的學生人數(shù)。SELECT COUNT(DISTINCT 學號) FROM 成績課堂練習1、查詢全體學生的姓名、學號、專業(yè)。2、查全體學生的姓名及其出生年份。3、查詢成績表中的課號，去掉重復值。4、查詢成績表中平時，期中，期末的平均成績。5.4.2

14、 帶條件查詢WHERE子句可指定查詢的條件。格式:WHERE 條件表達式 AND|OR條件表達式2WHERE比較操作符例1、查詢所有的男生記錄。SELECT * FROM 學生 WHERE 性別=“男”例2、求出計算機專業(yè)學生入學成績的平均分。SELECT 專業(yè),AVG(入學成績) AS 入學成績平均分 FROM 學生 WHERE 專業(yè)=“計算機” 例3、列出非計算機專業(yè)的學生名單。SELECT * FROM 學生 WHERE 專業(yè)“計算機”例4、統(tǒng)計計算機專業(yè)入學成績在600分以上的學生的人數(shù)。SELECT COUNT(姓名) 人數(shù) FROM 學生 WHERE 入學成績=600 AND 專業(yè)

15、=“計算機”WHEREINWHERE子句中的IN謂詞用來確定查詢的集合。例1、查詢計算機專業(yè)和外語專業(yè)的學生姓名和專業(yè)。SELECT 姓名,專業(yè) FROM 學生 WHERE 專業(yè) IN (“計算機”,“外語”)相當于 SELECT 姓名,專業(yè) FROM 學生 WHERE 專業(yè) =“計算機” OR 專業(yè)=“外語”)例2、查詢既不是計算機專業(yè)也不是外語專業(yè)的學生姓名和專業(yè)。SELECT 姓名,專業(yè) FROM 學生 WHERE 專業(yè) NOT IN (“計算機”,“外語”)WHEREBETWEENWHERE子句中的BETWEEN運算符用來確定范圍。格式：BETWEENAND或NOT BETWEENAN

16、D其中BETWEEN后是范圍的下限(低值),AND后是范圍的上限(高值)。例1、列出入學成績在560分到600分之間的學生名單和成績。SELECT 姓名,入學成績 FROM 學生 WHERE 入學成績 BETWEEN 560 AND 600上述命令還可寫成如下形式：SELECT 姓名,入學成績 FROM 學生 WHERE 入學成績=560 AND 入學成績=600WHERE LIKEWHERE中的LIKE子句用來進行字符串的匹配。格式：NOT LIKE “字符串” 含義:查找指定的字段值中與匹配符相匹配的記錄。匹配符可以是字符串，也可以含有通配符。例:查詢學號為0108015的同學的詳細情況S

17、ELECT * FROM 學生 WHERE 學號 LIKE “0108015”LIKE中的通配符% 0任意多個字符_ 任意單個字符a%b可代表ab，acb，addgbA_b可代表acb,afb,akb等。LIKE中的通配符例1、列出學生表中所有姓李的同學。SELECT 學號,姓名 FROM 學生 WHERE 姓名 LIKE “李%”例2、列出學生表中所有不姓李且姓名是兩個字的同學名單SELECT 學號,姓名 FROM 學生 WHERE 姓名 NOT LIKE “李%” AND 姓名 LIKE “_ _”LIKE中的通配符如果要查詢的字符串本身就含有%或_，就要使用ESCAPE “換碼字符”對通

18、配符進行轉義。例：查詢以DB_開頭的課程的課程號和學分。SELECT 課號,學分 FROM 課程 WHERE 課名 LIKE “DB_%” ESCAPE 如果LIKE后面的匹配串中不含通配符,則可以用=取代LIKE,用取代NOT LIKE涉及空值的查詢空值指不確定的值, 查詢空值要使用 IS NULL,不能用比較運算符=NULL例1、列出成績表缺少期末成績的學生學號和課號SELECT 學號,課號 FROM 成績 WHERE 期末 IS NULL例2、查詢所有有期末成績的學生學號和課號SELECT 學號,課號 FROM 成績 WHERE 期末 IS NOT NULL對查詢結果排序使用ORDER

19、BY子句可以對查詢結果按照一個或多個字段的值排序輸出語法:ORDER BY 排序選項1 ASC|DESC,排序選項2 ASC|DESC例1、按專業(yè)順序列出學生的學號，姓名和專業(yè)，同一專業(yè)按學號排列。SELECT 專業(yè),學號,姓名 FROM 學生 ORDER BY 專業(yè),學號只顯示查詢的前幾項記錄使用TOP 表達式 PERCENT短語可以列出滿足條件的前幾個或前百分之幾記錄TOP短語要與ORDER BY同時使用才有效例1、顯示入學成績最高的前五名同學的記錄SELECT * FROM 學生 ORDER BY 入學成績TOP 5 DCSC例2、顯示入學成績最高的前50%同學的記錄SELECT * T

20、OP 50 PERCENT FROM 學生 ORDER BY 入學成績 DESC對查詢結果分組使用GROUP BY子句可以對查詢結果進行分組,分組的目的是為了細化統(tǒng)計函數(shù)的作用對象語法:GROUP BY 分組選項 HAVING 條件例1、統(tǒng)計各個課號及相應的選課人數(shù)SELECT 課號,COUNT(課號) 選課人數(shù) FROM 成績 GROUP BY 課號HAVING條件與WHERE子句的區(qū)別:作用對象不同,WHERE子句作用于表或視圖;HAVING短語作用于組.例2、查詢選修了3門以上課程的學生學號SELECT 學號 FROM 成績 GROUP BY 學號 HAVING COUNT(*)3連接查

21、詢若一個查詢同時涉及兩個或兩個以上的表,則稱之為連接查詢。連接查詢是關系數(shù)據(jù)庫中最主要的查詢，包括等值連接、自然連接、非等值連接、自身連接、外連接和復合條件連接查詢。等值與非等值連接查詢：連接查詢中用來連接兩個表的條件稱為連接條件或連接謂詞，連接謂詞為=時，稱為等值連接；連接謂詞為其它比較運算符時，為非等值連接。形式：表名1.列名1 連接謂詞 表名2.列名2上述的列名稱為連接字段,各連接字段必須有可比性,但不一定相同。DBMS執(zhí)行連接操作的過程是：先在表1中找到第1條記錄，然后從頭開始掃描表2，逐一查找滿足連接條件的記錄，找到后就將表1中的第1條記錄與該記錄拼接起來，形成結果表中的一條記錄。表

22、2全部查找完后，再找表1中的第2條記錄，然后再從頭開始掃描表2，逐一查找滿足連接條件的記錄，找到后就將表1中的第2條記錄與該記錄拼接起來，形成結果表中的一條記錄。重復上述過程，直到表1中的全部記錄都處理完為止。例1、查詢每個學生及其選課情況SELECT 學生.*,成績.* FROM 學生,成績 WHERE 學生.學號=成績.學號在等值連接中把目標列中重復的字段去掉則為自然連接。例2、對例1用自然連接完成。SELECT 學生.學號,姓名,性別,專業(yè),出生日期,入學成績,課號,平時,期中,期末 FROM 學生,成績 WHERE 學生.學號=成績.學號例3、查詢所有學生的成績單，要求輸出學號、姓名、

23、課號、課名和總成績。SELECT a.學號,姓名,b.課號,課名,(平時+期中+期末)/3 AS 總成績 FROM 學生 a,成績 b,課程 c WHERE a.學號=b.學號 AND b.課號=c.課號 例3中給每個表定義了別名,以方便在查詢的其它部分使用。自身連接一個表與其自己進行連接,稱為表的自身連接。自身連接必須指定別名。例4、列出選修“09001”課的學生中，期末成績大于學號為“0101019”的同學該門課期末成績的那些同學的學號及其期末成績。SELECT a.學號,a.期末 FROM 成績 a,成績 b WHERE a.課號=“09001” AND b.學號=“0101019” A

24、ND a.期末b.期末 AND a.課號=b.課號內部連接(Inner Join)在通常的連接中,將所有滿足條件的記錄都作為結果輸出,稱為內連接。內連接的方式：Inner Join ON例:查詢所有學生的成績單，要求輸出學號、姓名、課號、課名和總成績。SELECT a.學號,姓名,b.課號,課名,(平時+期中+期末)/3 AS 總成績 FROM 學生 a INNER JOIN 成績 b INNER JOIN 課程 c ON b.課號=c.課號 ON a.學號=b.學號內連接中JOIN的順序和ON的順序至關重要。JOIN連接的順序和ON條件的順序給出正好相反。Inner Join等價于Join。

25、內連接可使用WHERE 子句代替。外連接（Outer Join）在通常的連接中，都是把滿足連接條件的記錄添加到結果表中，在VFP中，還可以使不滿足條件的記錄也出現(xiàn)在結果表中，稱為外連接。外連接包括：左連接LEFT JOIN右連接RIGHT JOIN全連接FULL JOIN例1、列出每個學生的基本情況及其選課情況。SELECT 學生.學號,姓名,性別,專業(yè),課號,期末 FROM 學生 LEFT JOIN 成績 ON 學生.學號=成績.學號在上例中使用右連接和全連接,加以比較。查詢嵌套在SQL中，一個SELECT-FROM-WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子

26、句或HAVING短語的條件中的查詢稱為嵌套查詢。例1、查詢選修 “09001”課的學生名單。SELECT 姓名 FROM 學生,成績 WHERE 學生.學號=成績.學號 AND 課號=“09001”上述查詢還可寫成如下形式:SELECT 姓名 FROM 學生 WHERE 學號 IN (SELECT 學號 FROM 成績 WHERE 課號=“09001”)查詢嵌套嵌套查詢要求子SELECT的查詢結果必須是確定的內容。嵌套查詢一般是由里向外處理，即每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件。SQL允許多層嵌套查詢。但VFP只能支持單層嵌套。子查詢中不能使用ORDER

27、 BY子句，即ORDER BY只能對最終查詢結果排序。查詢嵌套嵌套查詢使我們可以用多個簡單查詢構成復雜的查詢，從而增強SQL的查詢能力。以層層嵌套的方式來構造程序正是SQL中“結構化”的含義所在。查詢嵌套例1、查詢與“夏天”同在一個專業(yè)學習的學生名單。先分步來完成此查詢第一步:確定夏天所在的專業(yè)SELECT 專業(yè) FROM 學生 WHERE 姓名=“夏天”結果為“計算機”第二步:查找所有在“計算機”專業(yè)的學生名單SELECT 學號,姓名,專業(yè) FROM 學生 WHERE 專業(yè)=“計算機”將第一步查詢嵌入到第二步查詢的條件中,構造嵌套查詢的SQL語句如下:SELECT 學號,姓名,專業(yè) FROM

28、 學生 WHERE 專業(yè) IN (SELECT 專業(yè) FROM 學生 WHERE 姓名=“夏天”)上例查詢也可以用自身連接來完成SELECT a.學號,a.姓名,a.專業(yè) FROM 學生 a,學生 b WHERE a.專業(yè)=b.專業(yè) AND b.姓名=“夏天”可見在SQL中,實現(xiàn)同一個查詢可以有多種方法。帶謂詞的子查詢子查詢返回單值時可以使用比較運算符。還可使用ANY、ALL或EXISTS謂詞。ALL：滿足子查詢中的所有值的記錄。ANY(SOME)：滿足子查詢中任何一個值的記錄。EXISTS:檢查子查詢中是否存在記錄,返回值為邏輯值。例1、查詢其它專業(yè)比計算機系所有學生年齡都小的學生姓名及年齡。SELECT 姓名,年齡 FROM 學生 WHERE 年齡ALL (SELECT 年齡 FROM 學生 WHERE