【教學(xué)設(shè)計】高一下——結(jié)構(gòu)化查詢語言SQL

 結(jié)構(gòu)化查詢語言SQL

[舊課復(fù)習(xí)]：

復(fù)習(xí)內(nèi)容：

1
數(shù)據(jù)庫的建立、編輯和維護。

2
數(shù)據(jù)庫表索引的建立，使用及查詢。

3
臨時關(guān)聯(lián)和永久關(guān)系的建立。

復(fù)習(xí)目的：進一步鞏固學(xué)生對數(shù)據(jù)庫和表基本操作方法。

復(fù)習(xí)時長：大約5分鐘

[新課導(dǎo)入]：

導(dǎo)入方式：解讀為什么要建立數(shù)據(jù)庫，建立數(shù)據(jù)庫的目的不僅僅是為了存儲數(shù)據(jù)，更重要的是如何利用數(shù)據(jù)庫技術(shù)來處理這些數(shù)據(jù)，以獲得有用信息。而SQL語言是關(guān)系數(shù)據(jù)庫的標準語言，是處理數(shù)據(jù)庫的強有力手段。

導(dǎo)入目的：增強學(xué)生的學(xué)習(xí)積極性，初步了解SQL的作用。

導(dǎo)入時長：大約5分鐘

[新課講授]：

重點：利用 SQL對數(shù)據(jù)庫進行增、刪、改、查。

難點：SQL-select語句。

方法：運用多媒體輔助教學(xué)，采用案例教學(xué)和任務(wù)驅(qū)動等教學(xué)法。

3.1 SQL簡介

1．SQL語言的特點

     概括起來，SQL語言的主要特點有如下幾個特點：

（1
非過程化。

（2
以記錄集合為操作對象。

（3
涵蓋數(shù)據(jù)庫操作的絕大部分功能。

（4
可與多種關(guān)系數(shù)據(jù)庫程序協(xié)同工作。

（5）語言簡捷，易學(xué)易用。

SQL語言功能極強，但由于設(shè)計巧妙，語言十分簡捷，完成核心功能只用9個動詞，如表5-1所示。另外，SQL語言非常接近英語口語，因此容易學(xué)習(xí)、容易使用。

                   表5-1 SQL命令動詞

3.2  數(shù)據(jù)查詢

SELECT基本結(jié)構(gòu)

   SELECT  字段名

FROM  表名;

WHERE  條件;

GROUP BY  分組字段;

HAVING  分組篩選條件;

ORDER BY  排序字段;

INTO  輸出目標

SELECT  score.學(xué)號,avg(成績) as 平均分;

FROM  score inner join student on score.學(xué)號=student.學(xué)號;

WHERE  院系號='06';

GROUP BY  score.學(xué)號;

HAVING  平均分 > 60;

ORDER BY  平均分 desc

INTO  CURSOR AVG_06

SELECT命令基本用法

例： SELECT * FROM STUDENT

SELECT 學(xué)號,姓名 FROM STUDENT

SELECT AVG(金額) AS 每單平均金額, SUM(金額) AS 總金額 ；

FROM ORDER1

帶條件的查詢

SELECT * FROM STUDENT WHERE 性別=‘男’

IN運算符

IN運算符的操作對象是一個集合，作用是判斷是否是集合中的元素

SELECT * FROM  xscj  WHERE 課程名稱 IN （‘計算機基礎(chǔ)’,’高等數(shù)學(xué)’）

BETWEEN運算符

?查詢在2000-7-1和2001-4-1之間的訂單

SELECT * FROM order1 ;

    WHERE 訂單日期 BETWEEN {^2000-7-1} AND {^2001-4-1}

2 查詢成績在60到80之間的記錄

SELECT * FROM xscj WHERE 分數(shù) BETWEEN 60 AND 80

LIKE運算符

查詢姓劉的同學(xué)的記錄

SELECT * FROM xscj WHERE 姓名 LIKE ‘劉%’

SQL中使用 _ 和 % 作為通配符，分別與 ? 和 * 的作用相類似

IS NULL運算符

選擇未填寫分數(shù)的記錄

SELECT  * FROM xscj WHERE 分數(shù) IS NULL

選擇已填寫分數(shù)的記錄

SELECT  * FROM xscj WHERE 分數(shù) IS NOT NULL

注：”分數(shù) IS NULL” 與 “分數(shù) = NULL” 不等價

嵌套查詢

一個SELECT無法完成查詢?nèi)蝿?wù)，需要一個子SELECT的結(jié)果作為條件語句的條件

例：選擇紅太陽公司的訂單

SELECT * FROM ORDER1;

    WHERE 客戶編號 = ;

( SELECT 客戶編號 FROM CUST ;

WHERE 公司名稱 = ’紅太陽’ )

IN謂詞

選擇北京客戶的訂單

SELECT * FROM ORDER1;

    WHERE 客戶編號 IN ;

( SELECT 客戶編號 FROM CUST ;

WHERE 所在地 = ’北京’ )

ANY（SOME）謂詞

ANY(SOME)表示集合中任意(任選)一個元素，只要有一個滿足條件就返回.T.

例：查詢滿足以下條件的女職員記錄

–年齡只要比’06’號部門中任意一個人小

SELECT * ;

 FROM 雇員 ;

 WHERE 性別 = '女' AND 年齡 < ANY ;

 (SELECT 年齡 FROM 雇員 WHERE 部門號 = '06')

ALL謂詞

ALL表示集合中所有一個元素，所有元素滿足條件才返回.T.，只要有一個不滿足就返回.F.

查詢年齡比’06’號部門中所有人小的女職員記錄

SELECT * ;

 FROM 雇員 ;

 WHERE 性別 = '女' AND 年齡 < ALL ;

 (SELECT 年齡 FROM 雇員 WHERE 部門號 = '06')

ORDER BY子句

ORDER BY子句用于對查詢結(jié)果排序，排序選項可以使用字段名或數(shù)字，如使用數(shù)字2表示第2列

例1：顯示ORDER1表所有記錄，并按送貨方式和金額降序排序

SELECT * FROM ORDER1 ORDER BY 送貨方式, 金額 DESC

例2：顯示ORDER1表的客戶編號、訂單日期、金額字段，并按金額降序排序

SELECT 客戶編號,訂單日期,金額 FROM ORDER1 ORDER BY 3 DESC

例3：查詢各客戶的送貨方式，按送貨方式排序

SELECT DISTINCT 客戶編號, 送貨方式 FROM ORDER1 ORDER BY 2

TOP 子句

TOP 子句在符合條件的所有記錄中選取指定數(shù)量或百分比的記錄，必須和ORDER BY一起使用。

例1：顯示選修高等數(shù)學(xué)課程的前三名姓名，分數(shù)

SELECT TOP 3 姓名, 分數(shù) FROM xscj ;

WHERE 課程名稱 = '高等數(shù)學(xué)‘ ;

ORDER BY 分數(shù) DESC

注：如需選擇前30％的記錄可使用 TOP 30 PERCENT

分組統(tǒng)計－GROUP BY子句

GROUP BY子句對查詢結(jié)果進行分組匯總。

例：統(tǒng)計各門課程男生成績的平均分

SELECT 課程名稱,AVG(分數(shù)) ;

FROM xscj WHERE 性別 = ‘男’ ;

GROUP BY 課程名稱

例：統(tǒng)計男女生各門成績的平均分

SELECT 課程名稱,性別,AVG(分數(shù)) ;

FROM xscj GROUP BY 課程名稱,性別

篩選－HAVING子句

HAVING子句是對分組統(tǒng)計結(jié)果的篩選，因而必須與GROUP BY一起使用，不能單獨使用。

例：查詢該課程的男生平均分超過70分的課程名稱和平均分

SELECT 課程名稱,AVG(分數(shù)) ;

FROM xscj WHERE 性別 = ‘男’ ;

GROUP BY 課程名稱 HAVING AVG(分數(shù))>70

GROUP BY和HAVING使用規(guī)則

帶有統(tǒng)計要求的（如平均值、總和、計數(shù)等）命題，往往要使用GROUP BY子句，后接分類字段。

命題中如果有篩選條件：

–與分組無關(guān)的條件放在WHERE子句后

–與統(tǒng)計值相關(guān)的條件接在HAVING子句后

注：WHERE子句后不可使用統(tǒng)計函數(shù)

查詢各同學(xué)的不及格門數(shù)

SELECT 學(xué)號,count(成績) as 不及格門數(shù);

FROM score WHERE 成績<60 GROUP BY 學(xué)號;

選擇不及格門數(shù)最多的同學(xué)

SELECT top 1 學(xué)號,count(成績) as 不及格門數(shù);

FROM score WHERE 成績<60;

GROUP BY 學(xué)號 ORDER BY 不及格門數(shù) desc

練習(xí)題

?查詢各送貨方式的平均金額、總金額

?查詢員工人數(shù)超過三個（不含三個）的部門，列出部門號、員工人數(shù)

?查詢員工平均年齡<28歲的部門，列出部門號、該部門員工平均年齡和最年輕員工年齡

查詢各送貨方式平均金額、總金額

SELECT 送貨方式, avg(金額), sum(金額)

FROM order1 GROUP BY 送貨方式 HAVING avg(金額)>800

查詢平均金額>800的各送貨方式

SELECT 送貨方式, avg(金額), sum(金額)

FROM order1 WHERE 金額>800

GROUP BY 送貨方式 HAVING avg(金額)>1000

查詢員工人數(shù)超過三個的部門

SELECT 部門號, COUNT(雇員號) FROM 雇員

GROUP BY 部門號 HAVING COUNT(雇員號)>3

查詢女員工人數(shù)超過三個的部門

SELECT 部門號, COUNT(雇員號)  FROM 雇員 WHERE 性別 = ‘女’

GROUP BY 部門號  HAVING COUNT(雇員號)>3

查詢員工平均年齡<28歲的部門

SELECT 部門號, AVG(年齡), MIN(年齡)

FROM 雇員  GROUP BY 部門號  HAVING AVG(年齡)<28

輸出合并UNION

合并兩個查詢結(jié)果，兩個查詢結(jié)果必須列數(shù)和相應(yīng)列的數(shù)據(jù)類型均相同。

注：UNION ALL表示全部合并,沒有ALL則過濾重復(fù)記錄

例：列出選修’001’或’003’課程的所有學(xué)生的學(xué)號

SELECT  學(xué)號 FROM score WHERE 課號 ＝ ‘001’ ;

  UNION SELECT  學(xué)號 FROM score WHERE 課號 ＝ ‘003’

重定向輸出－INTO子句

INTO子句表示查詢結(jié)果的輸出，一般有三種選擇：

–數(shù)組ARRAY

–臨時表（游標–）CURSOR (臨時表一旦關(guān)閉就被刪除)

–表DBF | TABLE。

SELECT * FROM ORDER1

WHERE 金額>800

INTO ARRAY a

SELECT * FROM ORDER1

WHERE 金額>800

INTO CURSOR ORDER800

SELECT * FROM ORDER1

WHERE 金額>1000

INTO TABLE ORDER1000

多表查詢－等值連接

例1：查詢所有訂單的公司名稱、訂單日期、金額

SELECT 公司名稱,訂單日期,金額;

FROM ORDER1,CUST;

WHERE ORDER1.客戶編號=CUST.客戶編號

SELECT b.公司名稱,a.訂單日期,a.金額;

FROM ORDER1 a,CUST b;

WHERE a.客戶編號=b.客戶編號

例2：查詢選修大學(xué)語文且分數(shù)>80分的學(xué)生學(xué)號、姓名、分數(shù)

SELECT student.學(xué)號, 姓名, 分數(shù);

FROM student, sc, course;

WHERE student.學(xué)號=sc.學(xué)號

and sc.課程編號=course.課程編號;

and 分數(shù) > 80 and 課程名稱 = '大學(xué)語文'

例3：查詢平均分>80分的學(xué)生姓名和平均分

SELECT 姓名, avg(分數(shù));

FROM student, sc, course;

WHERE student.學(xué)號=sc.學(xué)號

 and sc.課程編號=course.課程編號;

GROUP BY student.學(xué)號;

HAVING avg(分數(shù)) > 80

例4：查詢以C++為直接先修課程的所有課程記錄

SELECT a.* ;

FROM course a,course b ;

WHERE a.先修課號=b.課程號 and b.課程名 = 'C++'

連接查詢

?內(nèi)部連接（自然連接）

–只連接兩張表的匹配項目

?外部連接

–兩張表的不–匹配項目用NULL填充

內(nèi)部連接－INNER JOIN

查詢金額大于800元的訂單的公司名稱、訂單日期、金額

SELECT 公司名稱,訂單日期,金額 ;

    FROM order1 INNER JOIN cust ON order1.客戶編號 = cust.客戶編號 ;

  WHERE 金額 > 800

（INNER JOIN 可縮略為 JOIN）

SELECT 姓名, avg(分數(shù));

FROM student INNER JOIN sc INNER JOIN course

  ON sc.課程編號=course.課程編號 ON student.學(xué)號=sc.學(xué)號

超過兩張表的連接，先做最內(nèi)層的連接，并用內(nèi)層連接的結(jié)果做為外層連接的數(shù)據(jù)源，依此類推。

外部連接

左外連接（左連接）－LEFT [OUTER] JOIN

–左表的記錄全取，–右表如無匹配項則用NULL填充

右外連接（右連接）－RIGHT [OUTER] JOIN

–右表的記錄全取，–左表如無匹配項則用NULL填充

全外連接（完全連接）－FULL [OUTER] JOIN

–左右表的記錄都全取，–一方如無匹配項則用NULL填充

練習(xí)題1

一個數(shù)據(jù)庫STSC，其中有數(shù)據(jù)庫表STUDENT、SCORE和COURSE，利用SQL語句查詢選修了“C++”課程的學(xué)生的全部信息，并將結(jié)果按學(xué)號升序存放在CPLUS．DBF文件中。

select a.* ;

from student a,score b,course c;

where a.學(xué)號 = b.學(xué)號 and b.課程號 = c.課程號 and 課程名='C++' ;

order by a.學(xué)號 ;

into dbf cplus

練習(xí)題2

已有YUANGONG和ZHICHENG表。現(xiàn)在要給每個人增加工資，請計算YUANGONG表的新工資字段，方法是根據(jù)ZHICHENG表中相應(yīng)職稱的增加百分比來計算。

新工資=工資*（1+增加百分比/100）

use yuangong

scan

select 增加百分比 from zhicheng ;

  where 職稱代碼=yuangong.職稱代碼 ;

  into array a

replace 新工資 with 工資*(1+a(1,1)/100)

endscan

use

練習(xí)題3

查詢各課程男生成績>80的人數(shù)，列出課程、人數(shù)

SELECT 課程號, count(成績)

FROM student, score

WHERE student.學(xué)號=score.學(xué)號

AND 成績>80 AND 性別=‘男’

GROUP BY 課程號

查詢平均分>80分的女生，列出學(xué)號、平均分

SELECT student.學(xué)號, avg(成績)

FROM student, score

WHERE student.學(xué)號=score.學(xué)號 AND 性別=‘女’

GROUP BY student.學(xué)號 HAVING avg(成績)>80

查詢各地訂單的平均金額，列出平均金額>1000的地區(qū)和平均金額

SELECT 所在地, avg(金額)

FROM cust, order1

WHERE cust.客戶編號=order1.客戶編號

GROUP BY 所在地

HAVING avg(金額)>1000

練習(xí)題4：

給定Student和score表，查詢選課門數(shù)是3門以上（不包括3門）的每個學(xué)生的學(xué)號、姓名、平均成績、最低分和選課門數(shù)，要求結(jié)果按平均成績降序排列，列出前3名，并輸出到數(shù)據(jù)表s1。

SELECT TOP 3 student.學(xué)號 AS 學(xué)號, 姓名,;

avg(成績) AS 平均成績, min(成績) AS 最低分,;

count(成績) AS 選課門數(shù);

FROM student,score ;

WHERE student.學(xué)號 = score.學(xué)號;

GROUP BY student.學(xué)號 ;

HAVING count(成績) > 3;

ORDER BY 平均成績 DESC;

INTO CURSOR s1

3.3 數(shù)據(jù)操作

?插入記錄（INSERT命令）

1  INSERT INTO student(學(xué)號,姓名) VALUES(‘010700035’,’丁一’)

2  INSERT INTO student VALUES(‘010700035’,’丁一’,‘男’)

若表名后無字段，則VALUES中應(yīng)包含表中所有字段，且順序與表定義中的字段順序一致

?更新記錄（UPDATE命令）

?更新所有記錄

–UPDATE sc SET 平時=平時*0.3,期末=期末*0.7

?更新符合條件的記錄

–UPDATE sc SET 綜合 = 平時＋期末

–    WHERE 修讀性質(zhì) = ‘初修’

?刪除記錄（DELETE命令）

?刪除所有記錄

–DELETE FROM score

?刪除符合條件的記錄

–DELETE FROM score WHERE  成績 > 100 or 成績 < 0

DELETE僅做邏輯刪除，要徹底刪除可用PACK

3.4數(shù)據(jù)定義

?新建表（CREATE  TABLE 命令）

格式：CREATE TABLE|DBF 表名（字段名1 字段類型（字段長度）[NOT NULL]

[CHECK]…）

?CREATE TABLE student(學(xué)號 C(15)  PRIMARY KEY,;

       姓名 C(10) NOT NULL, 性別 C(2) DEFAULT ‘男’, 出生年月 D)

?PRIMARY KEY為主鍵(主索引)

?UNIQUE 為候選索引

?NOT NULL為設(shè)置字段非空

?DEFAULT為設(shè)置默認值

?表的修改（ALTER TABLE命令）

?添加 ‘籍貫’ 字段

–ALTER TABLE XSCJ ADD COLUMN 籍貫 C(30)

?修改 ‘籍貫’ 字段

–ALTER TABLE XSCJ ALTER COLUMN 籍貫 C(50)

?刪除 ‘籍貫’ 字段

–ALTER TABLE XSCJ DROP COLUMN 籍貫

?表的刪除 （DROP  TABLE命令）

?刪除student表

–DROP TABLE student

[教學(xué)總結(jié)]：

本章主要學(xué)習(xí)了數(shù)據(jù)庫結(jié)構(gòu)化查詢語言SQL，實現(xiàn)對表的選擇、投影與連接操作。WHERE子句對應(yīng)選擇操作（選擇行），SELECT子句對應(yīng)投影操作（選擇列），F(xiàn)ROM子句對應(yīng)連接操作（多表操作）；SELECT語句中條件表達式用到的所有運算符（關(guān)系、邏輯、特殊）消除重復(fù)行DISTINCT子句，庫函數(shù)，分組GROUP BY子句、分組條件HAVING 子句、對查詢結(jié)果進行排序的ORDER BY 子句，嵌套查詢連接查詢等；數(shù)據(jù)操作語句INSERT、UPDATE、DELETE;數(shù)據(jù)定義語句CREATE、ALTER、DROP。

[作業(yè)布置]：

實驗5.1

P101選擇題1、3、5;  填空題1、2、3

P101選擇題2、4、8;  填空題11、12、13