1、第四章 結構化查詢語言SQL,掌握數據模式的建立和刪除的命令格式與作用； 掌握基本表結構的建立、修改與刪除的命令格式與作用； 掌握基本表內容的插入、修改與刪除的命令格式與作用； 掌握視圖的建立、修改與刪除的命令格式與作用； 熟練掌握查詢語句的格式與各個選項的作用，能夠按照查詢要求寫出相應的查詢語句。,SQL語言的功能： SQL是結構化查詢語言（Structured Query Language）的縮寫，具有數據定義（DDL）、數據操縱（DML）和數據控制（DCL）、數據查詢四個部分。,SQL數據定義功能：能夠定義數據庫的三級模式結構，即外模式、全局模式和內模式結構。在SQL中，外模式有叫做視圖

2、（View），全局模式簡稱模式（Schema）或數據庫（Database），內模式由系統(tǒng)根據數據庫模式自動實現，一般無需用戶過問。,基本表（表）、屬性（字段）、元組（行）的概念,SQL數據操縱功能：包括對基本表和視圖的數據查詢、插入、刪除和修改，特別是具有很強的數據查詢功能。,SQL的數據控制功能：主要是對用戶的訪問權限加以控制，以保證系統(tǒng)的安全性。,SQL,視圖V1,視圖V2,基本表B4,基本表B3,基本表B2,基本表B1,外模式,模式,存儲模式,SQL支持的數據庫的體系結構,一、數據庫模式的建立和刪除 1、建立數據庫模式 語句格式： CREATESCHEMA|DATABASE AUTHOR

3、IZATION 語句功能： 在計算機系統(tǒng)中建立一個只有名字的空數據庫，并定義出它的所有者名。,語句舉例： （1）create schema xuesh authorization 劉勇 （2） create database 數據庫,2、刪除數據庫模式 語句格式： DROP SCHEMA|DATABASE 數據庫名 語句功能： 從計算機系統(tǒng)中刪除（撤銷）一個數據庫。當然會同時把該庫中的所有信息一并刪除掉。 語句舉例： drop database xuesh,二、表結構的建立、修改和刪除 1、建立表結構 語句格式： CREATE TABLE . （，,) 語句功能：在當前或給定的數據庫中定義一個

4、表的結構（即關系模式)。,語句說明： a.若省略和則在當前數據庫中建立一個表，否則在指定數據庫中建立一個表。使用戶給所定義的表所起的名字。可以在一個表定義中出現一次或多次，每個包括列名 （即屬性名)、相應數據類型和該列的完整性約束等內容。在所有列定義之后可以給出表級完整性約束。,b.可使用的數據類型主要有以下四種： char(n) 定長字符型 int 整型 float 浮點型，又稱實數型 date 日期型,c.列級完整性約束有以下六種: 1.DEFAULT 默認值約束。 2.NULL/NOT NULL 空值/非空值約束。注明每行上的該列值為空。 3.PRIMARY KEY 主碼約束。注明該列為

5、關系的主碼。 4.UNIQUE單值約束，又稱唯一值約束。必須不相同,d.表級完整性約束包括以下四種： 1. PRIMARY KEY (,）主碼約束。注明一列或同時多個列為關系的主碼。 2. UNIQUE單值約束。一個或同時若干個列為單值。 3.FOREIGN KEY () REFERENCES (,) 外碼約束。 4.CHECK () 檢查約束。,5.REFERENCES ()外碼約束。 6.CHECK () 檢查約束。注明該列的取值條件，或稱取值限制。,注：若只涉及到一個列時，則既可以作為列級完整性約束，又可以作為表級完整性約束，當然只取其一。,語句舉例： (1） create table

6、學生 ( 學生號 char(7) primary key, 姓名 char(6) not null unique, 性別 char(2) not null chech(性別=男 or 性別=女), 出生日期 datetime check(出生日期=1 and 年級=4) ),(2) Create table 課程( 課程號 char(4) primary key, 課程名 char(10) not null unique, 課程學分 int check (課程學分=1 and 課程學分=6) ),Create table 選課 ( 學生號 char(7), 課程號 char(4), 成績 in

7、t check (成績=0 and 成績=100), primary key (學生號,課程號), foreign key (學生號) references 學生 (學生號), foreign key (課程號) references 課程 (課程號) ),三、表內容的插入、修改和刪除 1、插入記錄 向一個表中插入記錄有兩種語句格式，一種是單行插入，另一種是多行插入。 單行插入： 格式 ：INSERT INTO . (,)VALUES 多行插入： 格式：INSERT (INTO) . (,) 語句功能： 向一個表中所指定的若干列插入一行或多行數據。 注意：當列值為字符串或日期時，必須用單引號括

8、起來，以區(qū)別于數值數據。 語句舉例：p79,2、修改表結構 語句格式： ALTER TABLE . ADD , |DROP COLUMN ,|DROP , 語句功能： 向已定義過的表中添加一些列的定義或一些表級完整性約束，或者從已定義過的表中刪除一些列或一些完整性約束。 舉例： （1）alter table 學生 add 籍貫 char(6) （2） alter table 學生 drop column 籍貫,3、刪除表結構 語句格式： DROP TABLE . 語句功能：從當前或給定的數據庫中刪除一個表。 舉例：drop table 學生1,2、修改記錄 語句格式： UPDATE . SET

9、 =, FROM,WHERE 語句功能： 按條件修改一個表中一些列的值、 語句舉例： (1) update 職工 set 年齡=年齡+1 (2) Update 職工 set 基本工資=職工1.基本工資+職工1.職務津貼 from 職工1 where 職工.職工號=職工1.職工號 (3) Update 職工 set 基本工資=基本工資*1.2 where 職工號=010405,3、刪除記錄 語句格式： DELETE FROM . FROM,WHERE ,語句功能：刪除一個表中滿足條件的所有行 語句舉例： (1) delete from 職工 where 年齡45 (2) Delete 職工 fr

10、om 職工1 where 職工.職工號=職工1.職工號 (3) Delete 職工,四、視圖的建立、修改和刪除 視圖是在基本表之上建立的表，它的結構(即所有列定義)和內容(即所有數據行)都來自基本表，它依據基本表存在而存在。每個視圖的列可以來自同一個基本表，也可以來自多個不同的基本表。它是基本表的抽象和在邏輯意義上建立的新關系。對視圖只能進行修改和查詢操作。,1、建立視圖 語句格式： CREATE VIEW (,) AS 功能：在當前庫中根據SELECT子局的查詢結果建立一個視圖，包括視圖的結構和內容。,語句舉例： create view 成績視圖表(學生號,姓名,課程號,課程名,成績) as

11、 select 選課.學生號,姓名,選課.課程號,課程名,成績 from 學號,課程,選課 where 學生.學生號=選課.學生號 and 課程.課程號=選課.課程號 and 專業(yè)=電子,2、修改視圖內容 語句格式： UPDATE . SET =, FROM,WHERE 語句功能： 按照一定條件對當前或指定數據庫中的一些列值進行修改。 語句舉例： update 成績視圖表 set 成績=80 where 學生號=0102005 and 課程號=E002,3、修改視圖定義： 語句格式： ALTER VIEW (,) AS 語句功能： 在當前數據庫中修改已知視圖的列,它與SELECT子句的查詢結果

12、相對應。 語句舉例： Create view 學生視圖 (學生號,姓名) as select 學生號，姓名 from 學生 Alter view 學生視圖 (學生號,專業(yè)) as select 學生號,專業(yè) from 學生,4、刪除視圖 語句格式： DROP VIEW 語句功能：刪除當前數據庫中一個視圖。 語句舉例： drop view 成績視圖表,五、SQL查詢 1、SELECT語句 格式： SELECT ALL|DISTINCT AS , |*| .*| INTO FROM AS , AS WHERE GROUP BY , HAVING ORDER BY ASC|DESC,ASC|DESC

13、 功能： 根據一個或多個表按條件進行查詢，產生出一個新表（即查詢結果），該新表被顯示出來或者被命名保存起來。,語句說明： a. Select選項給處在查詢結果中每一行(即每一條記錄)所包含的列，以及決定是否允許在查詢結果中出現重復行（即內容完全相同的記錄）； b. into 選項決定是否把查詢結果以基本表的形式保存起來，若需要則應帶有該選項; c. from選項提供用于查詢的基本表和視圖，它們均可以帶有表別名，稱這些表為源表，而把查詢結果稱為目的表; d. where 選項用來指定不同源表之間記錄的連接條件和每個源表中記錄的篩選（選擇）條件，只有滿足所給連接條件和篩選條件的記錄才能被寫入到目的

14、表中； e. group by 選項用于使查詢結果只包含按指定列的值進行分組的統(tǒng)計信息； f. having 子句通常同group by 選項一起使用，篩選出符合條件的分組統(tǒng)計信息； g. order by 選項用于將查詢結果按指定列值的升序或降序排序。 在查詢語句中，通過select 選項實現投影運算，通過from選項和where選項是實現連接和選擇運算（在SQL新版本中，用from 選項專門實現連接運算，用where 選項專門實現選擇運算）,2、SELECT 選項 在該選項中，ALL/DISTING為任選項，若選擇ALL,則允許在查詢結果中出現內容重復的行（記錄），若選擇DISTINCT,

15、則在查詢結果中不允許出現內容重復的行，即只有內容互不相同的記錄才能被寫入到查詢結果中，若省略該選項，則隱含為ALL.,應用舉例：P89 例4-14-10,3、FROM選項 例4-11,4、WHERE選項 例4-124-16,SPK1（商品代號 C（8），分類名 C（6），單價 N（8，2），數量 N（3）,一、簡單查詢,例1 從SPK1中查詢出每個記錄的分類名字段的值。,SELECT ALL 分類名 FROM SPK1,例2 從SPK1中查詢出所有商品的不同分類名。,SELECT DISTINCT 分類名 FROM SPK1,練習：1、從SPK1中查詢出單價低于2000元的商品代號、分類名和單

16、價。 2、從SPK1中查詢出單價在1000元至2500元之間的所有商品。,例3 從SPK1中查詢出分類名為“電視機”的所有商品,SELECT *； FROM SPK1； WHERE 分類名=“電視機”,用AS指出字段別名：,例4 從SPK1中查詢出每一種商品的最高價值、最低價值。,SELECT MAX（單價*數量）AS 最高價值，MIN（單價*數量）AS 最低價值； FROM SPK1,在SQL-SELECT命令中，使用的字段函數有： COUNT（字段名|*） 統(tǒng)計出對應字段的個數，它也就是相應的記錄數，通常使用*表示任一字段。 MAX（字段名） 求出最大值。 MIN（字段名） 求出最小值。

17、AVG（字段名） 求出對應的數值字段的平均值。 SMU（字段名） 求出對應的數值字段的總和。,SPK2（商品代號C（8），產地C（8），品牌C（8）,練習 3、從SPK1中查詢出每一種商品的價值。 4、查詢出SPK1庫中分類名為“電視機”的商品種數、最高價、最低價及平均價。,練習題答案： 1、SELECT 商品代號，分類名，單價； FROM SPK1； WHERE 單價1000 AND 單價2500 3、SELECT 商品代號，單價*數量 AS 價值； FROM SPK1 4、SELECT 分類名，COUNT（*） AS 種數； MAX（單價）AS 最高價，MIN（單價）AS 最低價； AVG

18、（單價）AS 平均價； FROM SPK1； WHERE 分類名=“電視機”,SELECT 商品代號，品牌； FROM SPK2； WHERE 產地 IN （南京，北京）,例 查找SPK1中所有商品中數量大于10的分類號及單價，并把單價按降續(xù)排序。,用ORDER BY 對查詢結果排序,在WHERE中使用謂詞 IN,例 查找SPK2中產地是南京或北京的商品的商品代號、品牌。,SELECT 分類號，單價； FROM SPK1； WHERE 數量=10； ORDER BY 單價 DESC,在WHERE中BETWEENAND和NOT BETWEEND 使用,例 從SPK1中查詢出單價在1000元至25

19、00元之間的所有商品。,SELECT 商品代號，分類名，單價； FROM SPK1； WHERE 單價 BETWEEN 1000 2500,例4-11 從教學庫中查詢出每個學生選修每門課程的學生號、姓名、課程號、課程名、成績等數據,Select x.學生號,姓名,y.課程號,課程名,成績 From 學生 x,課程 y,選課 z Where x.學生號=z.學生號 and y.課程號=z.課程號,例4-12 從商品表1種查詢出單價大于1500，同時數量大于等于10的商品。,Select * From 商品表1 Where 單價=1500 and 數量=10,例13 從商品庫中查詢出產地為南京或無

20、錫的所有商品的商品代號、分類名、產地和品牌。,Select x.商品代號,分類名,產地,品牌 From 商品表1 x,商品表2 y Where x.商品代號=y.商品代號 and (產地=南京 or 產地=無錫),例 14 從教學庫中查詢出選修至少兩門課程的學生學號。,Select distinct x.學號 From 選課 x,選課 y Where x.學生號=y.學生號 and x.課程號y.課程號,例 15 從教學庫中查詢出選修了課程名為“操作系統(tǒng)”課程每個學生的姓名。,Select 姓名,課程 From 學生 x,課程 y,選課 z Where x.學生號=z.學生號 and y.課程

21、號=z.課程號 and 課程名=操作系統(tǒng),注意：在新版的SQL中，為了使查詢語句更加結構化，已經把查詢連接條件從WHERE選項中轉移到FROM選項中，并且還豐富了連接的功能，除了上述介紹的一般連接（在新版本中稱作中間連接）外，還增加了左連接和右連接的功能。在FROM選相中的相應語法格式分別為：,中間連接 FROM INNER JOIN ON . . 左連接 FROM LEFT JOIN ON . . 右連接 FROM RIGHT JOIN ON . .,每一種連接都隱含著雙重循環(huán)的執(zhí)行過程： 對于中間連接，外循環(huán)依次掃描第一個表中的每個元組，內循環(huán)依次掃描第二個表中的每個元組，當滿足連接條件時

22、就連接起來形成中間表中的一個新元組； 對于左連接，除了按中間連接形成中間表中的新元組外，還把第一個表中的沒有形成連接的所有元組也加入到中間表中，這些元組在第二個表上所對應的列值被自動置為空； 對于右連接，除了按中間連接形成中間表中的新元組外，還把第二個表中的沒有形成連接的所有元組也加入到中間表中，這些元組在第一個表上所對應的列值被自動置為空。 完成連接后，查詢語句再根據WHERE選項中提供的篩選條件從中間表中選擇出元組，然后再根據SELECT選項投影出所需要的列形成結果表。,例 16.a 從教學庫中查詢出所有學生的選課情況，要求沒選修任何課程的學生信息也要反映出來,Select * From

23、學生 left join 選課 on 學生.學生號=選課.學生號,例 16.b 從學生庫中查詢出所有課程被學生選修的情況,Select * From 課程 left join (選課 inner join 學生 on 選修.學生號=選課.學生號) on 課程.課程號=選課.課程號,例 16.c 從教學庫中查詢出所有電子專業(yè)的學生選課的全部情況,Select * From 學生 inner join (選課 inner join 課程 on 選課.課程號=課程.課程號) on 學生.學生號=選課.學生號 Where 專業(yè)=電子 (與傳統(tǒng)查詢語句等效),用于查詢語句中的專門比較式又叫判斷式，它實現

24、單值與集合數據之間的比較。常用的有以下六種格式。,格式1： ALL () 功能：是一條完整的SELECT語句，被嵌套在該比較式中使用。當的查詢結果中的每一個值都滿足所給的比較條件時，此比較式的值才為真，否則為假。,例 17 從商品表1中查詢出單價比分類名為“洗衣機”的所有商品的單價都高的商品。,Select * From 商品表1 Where 單價all (select 單價 from 商品表1 where 分類名=“洗衣機”,例 18 查詢出數量小于分類名為“洗衣機”或“微波爐”的每一個商品數量的所有元組(包括 產地和品牌),Select x.*,產地,品牌 From 商品表1 x inne

25、r join 商品表2 y on x.商品代號=y.商品代號 Where 數量all (select 數量 from 商品表1 where 分類名=洗衣機 or 分類名=微波爐),格式2 ANY|SOME(),當子查詢的查詢結果中的任一個值滿足所給的比較條件時，此比較式為真，否則為假。該格式中的兩個關鍵字ANY和SOME具有同樣的作用，選用任一個即可。,例 19 從商品庫中查詢出產地與品牌為“春蘭”的商品的產地相同的所有商品的商品代號、分類名、品牌、產地等屬性的值,Select x.商品代號,分類名,品牌,產地 From 商品表1 x inner join 商品表2 x on x.商品代號=y

26、.商品代號 Where 產地=some (select 產地 from 商品表2 where 品牌=春蘭),例 20 從教學庫中查詢出選修了課程名為“C+語言”的所有學生的姓名和成績。,第一種方法：使用單重查詢語句處理 select 姓名,成績 from 學生 x,課程 y,選課 z where x.學生號=z.學生號 and y.課程號=z.課程號,第二種方法：使用雙重查詢語句處理 select 姓名,成績 from 學生 inner join 選課 on 學生.學生號=選課.學生號 where 課程號=some(select 課程號from 課程=C+語言 ),例 21 從商品庫中查詢出所

27、有商品中單價最高的商品和單價最低的商品,Select * From 商品表1 Where 單價=any(select max(單價) from 商品表1) or 單價=any(select min(單價) from 商品表1),或：select * from 商品表1 where 單價=any(select max(單價) from 商品表1 union select min(單價) from 商品表1),格式3： NOT BETWEEN AND ,例 22 從商品表1中查詢出單價在1000到2000元之間的所有商品,Select * From 商品表1 Where 單價 between 10

28、00 and 2000,格式4： EXISTS (),當子查詢結果中至少存在著一個元組時，表明查詢結果非空，則此判斷式為真，否則為假。但當判斷式中帶有NOT關鍵字時，情況正好相反，即當子查詢結果為空時，判斷式為真，否則為假。,例 23 從教學庫中查詢出選修至少一門課程的所有學生,Select * From 學生 Where exists (select * from 選課 where 學生.學生號=選課.學生號),例 24 從教學庫中查詢出與姓名為“王明”的學生選課至少有一門相同的所有學生,Select * From 學生 x Where x.姓名王明 and exists (select y

29、.課程號 from 選課 y where y.學生號=x.學生號 and y.課程號=any(select w.課程號 from 學生 z，選課 w where z.學生號=w.學生號 and z.姓名=王明),格式5： NOT IN () | (),使用逗號分開的若干個常量。當所制定列的當前值包括在由所給定的值之內時，則此判斷式為真，否則為假。,例 26 從學生表中查詢出專業(yè)為計算機、電氣、通信的所有學生。,Select * From 學生 Where 專業(yè) in (計算機,電氣,通信),例 27 從教學庫中查詢出選修了課程名為“操作系統(tǒng)”的所有學生。,Select * From 學生 Wh

30、ere 學生號 in (select 學生號 from 選課,課程 where 選課.課程號=課程.課程號 and 課程名=操作系統(tǒng)),格式6： NOT ,當的當前值與的值相匹配時，此判斷時為真。,例 28 從商品表1中查詢出商品代號以字符串“dsj”開頭的所有商品,Select * From 商品表1 Where 商品代號 like dsj%,5、GROUP BY選項 該選項中的,等必須是出現在SELECT選項中的被投影的表達式所指定的列名。通常在SELECT選項中使用列函數對列值相同的每一組進行有關統(tǒng)計,例 29 從學生表中查詢出每個專業(yè)的學生數,Select 專業(yè) 專業(yè)名,count(專

31、業(yè)) 學生數 From 學生 Group by 專業(yè),例 30 從教學庫中查詢出每個學生的學生號，姓名及所選課程的門數,Select x.學生號,姓名,count(x.學生號) 所選門數 From 選課 x ,學生 y Where x.學生號=y.學生號 Group by x.學生號,y.姓名,例 31 從商品表1種查詢出每一類（即分類名相同）商品的最高價、最低價和平均價。,Select 分類名,max(單價) as 最高價,min(單價) as 最低價,avg(單價) as 平均價 From 商品表1 Group by 分類名,6、HAVING 選項 該選項的是一個篩選條件。該選項通常跟在G

32、ROUP BY子句后面用來從分組統(tǒng)計中篩選出部分統(tǒng)計結果，因此該選項中的邏輯表達式通常帶有字段函數,例 32 從學生表中查詢出專業(yè)的學生數多于1人的專業(yè)名及人數,Select 專業(yè) as 專業(yè)名,count(專業(yè)) as 學生數 From 學生 Group by 專業(yè) Having count(專業(yè))1,例 33 從教學庫中查詢出選修課程超過1門的學生。,Select * From 學生 Where 學生號 in (select 學生號 from 選課 group by 學生號 having count(學生號)1 ),例 34 從教學庫中查詢出選課門數超過學生號為0101001學生的選課門數

33、的所有學生。,Select * From 學生 Where 學生號 in (select 學生號 from 選課 group by 學生號 having count(學生號) (select count(*) from 選課 where 學生號=0101001 ) ),7、ORDER BY 選項,此選項中的,等是需要是查詢結果按其進行排序的列。他們可以是原表中的列名，也可以是SELECT選項中所給表達式的順序號（即對應查詢結果中的列號）或定義的列名。若其后帶有ASC關鍵字，則將按值的升序排序查詢結果，若其后帶有DESC關鍵字，則將按值的降序排序查詢結果，若不指定排序方式，則默認按升序排序。 注

34、意：該選項只能用在最外層的查詢語句中，不能在子查詢中使用。,例 35 從商品表中按單價升序查詢出所有商品記錄。,Select * From 商品表1 Order by 單價,例 36 從商品表1中查詢出單價比平均單價高的所有商品，并使結果按降序排列。,Select * From 商品表1 Where 單價all (select avg(單價) from 商品表1) Order by 單價 desc,例 37 從教學庫中查詢出所有學生的學號及所選課程的門數，按門數升序排列結果,Select x.學生號,count(x.學生號) as 選課門數 From 學生 x,選課 y Where x.學生號=y.學生號 Group by x.學生號 Order by 選課門數,例 38 從教學庫中查詢出所有學生的信息及所選課程的門數，按門數升序排列結果。,Select x.*,count(x.學生號) as 選課門數 From 學生 x,選課 y Where x.學生號=選