1、第4章 數據庫及運用程序開發 關系數據庫規范言語SQL西安交通大學計算機教學實驗中心4.1.3關系數據庫規范言語關系數據庫規范言語SQLn構造化查詢言語(SQL，Structured Query Language)是關系數據庫的規范言語。它的主要功能包括：n數據定義:Data Defination Language(DDL)n數據查詢: Data Query n數據支配:Data Manipulation Language(DML)n數據控制 : Data Control Language(DCL)2SQL特點n綜合一致：集數據定義言語綜合一致：集數據定義言語DDL、數據支配言語數據支配言語D

2、ML和數據控制言語和數據控制言語DCL于一體，綜合管理功能于一身。于一體，綜合管理功能于一身。n高度非過程化：用戶只需提出高度非過程化：用戶只需提出“做什么，做什么，而不需指明而不需指明“怎樣做。怎樣做。n面向集合的操作方式：用戶查詢、修正、面向集合的操作方式：用戶查詢、修正、的數據不是一條記錄，而是記錄的集合。的數據不是一條記錄，而是記錄的集合。SQL特點2n以同一語法構造提供兩種運用方式：用戶可以同一語法構造提供兩種運用方式：用戶可以從鍵盤運用以從鍵盤運用SQL命令，也可以將命令，也可以將SQL語句語句嵌入其他高級言語中。嵌入其他高級言語中。(PHP VC例如例如)n言語簡約、易于運用：語

3、句很少，言語簡約、易于運用：語句很少，n數據定義：數據定義：CTEATE DROP ALTERn數據支配：數據支配：INSERT UPDATE DELETEn數據查詢：數據查詢：SELECTn數據控制：數據控制：GRANT REVOTEn可以對根本表和視圖進展查詢可以對根本表和視圖進展查詢關系數據庫的三級方式構造 nSQL Structured Query Language) n1 建立表nCREATE TABLE table_namen (column_name data-type consraint, )建立表nCREATE TABLE employeen(eid CHAR(6),nnam

4、e VARCHAR(8) NOT NULL,nage NUMERIC(3,0),ncountry VARCHAR(10) NULL)n/ NUMERIC(3,0) - NUMERIC建立表nCREATE TABLE employeen(eid CHAR(6) NOT NULL PRIMARY KEY,nname VARCHAR(8) NOT NULL,nage NUMERIC(3,0),ncountry VARCHAR(10) NULLn)ndrop table employee3 刪除數據表 SQL SERVER中常用的數據類中常用的數據類型型 n學號、身份證號等，運用字符型數據2 插入記錄

5、nINSERT INTO 表名 列名表 VALUES 值表nINSERT INTO employee n(eid, name, age, country) nVALUES (3018, zhang, 28, China) nINSERT INTO employee nVALUES (3056, wang, 35, USA)nINSERT INTO employee n(eid, name, country) nVALUES (4025, li, Russia)2、 插入記錄3、修正記錄nUPDATE 表名 nSET 字段名=表達式 nWHERE 條件 nUPDATE employeenSET

6、name=Li MingnWHERE eid=4025nUPDATE employeenSET name=Zhang Li,nage=36,ncountry=USAnWHERE eid=3056n可以修正多個字段的值。假設不帶WHERE修正一切的記錄。4、刪除記錄nDELETE 表名nWHERE 條件nDELETE employeenWHERE eid=30185、 查詢記錄nSELECT 表達式 nFROM 表名表nWHERE 條件nGROUP BY 字段名表nHAVING 條件nORDER BY 字段名表 1 舉例nSELECT pub_id, pub_name, countrynFROM

7、 publishersnSELECT * nFROM publishers2 刪除反復的行DISTICT nSELECT city, statenFROM authersnSELECT DISTICT city, statenFROM authers 3 計算表達式 n nS E L E C T t i t l e _ i d , t y p e , p r i c e , price_price*0.3nFROM titlen nSELECT au_lname+.+au_fname, city+.+statenFROM authors4挑選條件 na條件：n比較= = != nprice45

8、.3ncountry=China n邏輯運算nNOT AND OR nCountry=China AND age35 b舉例nSELECT title_id, type, price, price - price*0.3 nFROM titlesnWHERE price - price*0.318nSELECT pub_id, pub_namenFROM publishersnWHERE country=USA AND state=CA(c) LIKE n列名 NOT LIKE 方式n%: 恣意字符串, _:恣意一個字符n nSELECT au_lname+.+au_fname, city+.

9、+statenFROM authorsnWHERE au_fname LIKE D% nSELECT au_lname+.+au_fname, city+.+statenFROM authorsnWHERE au_id LIKE 72_-%其他的條件查詢(5) 結果排序nORDER BY 字段表n通常前往的結果是隨機陳列的。 nSELECT stor_name, citynFROM storesnORDER BY store_namenSELECT stor_name, citynFROM storesnORDER BY store_name DESCn nSELECT stor_name,

10、citynFROM storesnORDER BY store_name ASCnSELECT stor_name, citynFROM storesnORDER BY stor_name， city n用多個列陳列。n nSELECT stor_name, citynFROM storesnORDER BY staten用不出現的列。 (6) 統計函數 nSELECT AVG(price)nFROM titlesn nSELECT MAX(price)nFROM titlesnWHERE type=businessnSELECT AVG(qty), SUM(qty), COUNT(DISTI

11、CT stor_id)nFROM salesn nSELECT COUNT*nFROM publishersnWHERE state=CA(7) 結果分組 nGROUP BY 分組列名表nSELECT type, AVG(price), SUM(price), COUNT(*)nFROM titlesnWHERE type in (business, mod_cook, trad_cook)nGROUP BY typeSELECT type, pub_id, AVG(price), SUM(price), COUNT(*)FROM titlesWHERE type in (business,

12、mod_cook, trad_cook)GROUP BY type， pub_idnGROUP BY 分組列名表nHAVING 條件n對GROUP的分組結果再進一步挑選。 nSELECT type, AVG(price), SUM(price), COUNT(*)nFROM titlesnWHERE price$10nGROUP BY type運用HAVINGnSELECT type, AVG(price), SUM(price), COUNT(*)nFROM titlesnWHERE price$10nGROUP BY typenHAVING AVG(price)$20運用HAVING(8)

13、 多表查詢na笛卡爾乘積nSELECT title_id, au_fnamenFROM titles, authorsn nSELECT titles.title_id, titles.title, titleauthor.au_id nFROM titles, titleauthorb 別名nSELECT t.title_id, t.title, ta.au_id nFROM titles t, titleauthor tan nSELECT t.title_id, t.title, ta.au_id nFROM titles t, titleauthor tanWHERE t.title_

14、id=ta.title_idc挑選nSELECT t.title_id, a.au_id nFROM titles t, titleauthor ta, authors anWHERE t.title_id=ta.title_id and ta.au_id=a.au_id6 視圖 n(1視圖的定義nSQL建立視圖的語句格式為：nCREATE VIEW (，)nAS nCREATE VIEW au_viewnASnSELECT au_id, au_fname, au_lnamenFROM authorsnCREATE VIEW author_titlenASnSELECT t.title_id,

15、 a.au_id nFROM titles t, titleauthor ta, authors anW H E R E t . t i t l e _ i d = t a . t i t l e _ i d a n d ta.au_id=a.au_id(2)視圖查詢nSELECT * nFROM au_viewnSELECT t.title_id, a.au_id nFROM titles t, titleauthor ta, authors anWHERE t.title_id=ta.title_id and ta.au_id=a.au_id 3刪除視圖nDROP VIEW 視圖名n nD

16、ROP VIEW a_authors7、索引n索引可以極大地提高查詢的速度 n索引可以建在一列或幾列上 n有ASC(升序)和DESC(降序) n1索引類型n按照存儲位置：聚集索引和非聚集索引。n按照關鍵字值能否獨一：獨一索引和非獨一所以。2建立索引nCREATE UNIQUE CLUSTERED | NOCLUSTERED INDEX 索引名nON 表名 列名表nCREATE UNIQUE INDEX id_index nON authors (au_id)nCREATE INDEX id_index nON authors ( au_fname DESC,au_id)nCREATE CLUS

17、TERED INDEX id_index nON authors ( au_fname DESC,au_id)3刪除索引nDROP INDEX 表名.索引名n nDROP INDEX authors.id_index8、數據控制n數據控制也稱為數據維護n經過對數據庫用戶的運用權限加以限制而保證數據平安的重要措施。nSQL言語提供一定的數據控制功能，能在一定程度上保證數據庫中數據的完全性和完好性，并提供了一定的并發控制及恢復才干。nSQL的數據控制語句包括授權(Grant)、收權(Revoke)和回絕訪問(Deny)三種，其權限的設置對象可以是數據庫用戶或用戶組。 1授權 nGRANT語句向用戶授予操作權限 nGRANT , n ON nTO ， | PUBLICnWITH GRANT O