章關(guān)系數(shù)據(jù)庫語言

第4章關(guān)系數(shù)據(jù)庫標準語言SQL4.1SQL概述4.2查詢功能4.3操作功能4.4定義功能了解SQL掌握簡單查詢、連接查詢、嵌套查詢的方法掌握使用特殊運算符、謂詞、量詞、空值進行查詢的方法掌握查詢排序、計算、分組的方法掌握SQL的插入、更新、刪除數(shù)據(jù)的方法掌握SQL表的定義、刪除、修改表結(jié)構(gòu)、定義視圖的功能了解別名、自連接查詢、內(nèi)外層相互嵌套查詢、超鏈接查詢、查詢的并運算。本章知識點與要求：第4章關(guān)系數(shù)據(jù)庫標準語言SQL4.1SQL概述4.2查詢功能4.3操作功能4.4定義功能4.1SQL語言概述SQL是StructuredQueryLanguage(結(jié)構(gòu)化查詢語言)的縮寫，查詢是SQL的重要組成部分，但不是全部，SQL還包含數(shù)據(jù)定義、操縱、控制等功能。目前，SQL語言已被確定為關(guān)系型數(shù)據(jù)庫系統(tǒng)的國際標準，被絕大多數(shù)商品化關(guān)系型數(shù)據(jù)庫系統(tǒng)采用，如Oracle、Sybase、DB2、Informix、SQLServer這些數(shù)據(jù)庫管理系統(tǒng)都支持SQL語言作為查詢語言。

SQL語言特點

綜合統(tǒng)一：有數(shù)據(jù)定義、查詢、操縱、控制等功能，能獨立完成數(shù)據(jù)庫的全部操縱。內(nèi)容簡潔：雖功能強大，但核心功能只有9個動詞；語法簡單，接近自然語言。

非過程化：

用戶只需了解想做什么，具體怎么做有SQL自動完成使用方式靈活：

命令交互或嵌入程序均可SQL功能命令動詞數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)操縱INSERT、UPDATE、DELETE數(shù)據(jù)控制GRANT、REVOKE第4章關(guān)系數(shù)據(jù)庫標準語言SQL4.1SQL概述4.2查詢功能4.3操作功能4.4定義功能SQL數(shù)據(jù)查詢由SELECT命令實現(xiàn)，該命令的基本框架是SELECT-FROM-WHERE查詢模塊

SELECT命令的語法格式如下：select

[distinct][top<表達式>][別名]<select表達式>[as<列名>][，[別名]<select表達式>[as<列名>]...]from[數(shù)據(jù)庫名!]<表名>[[as]local_alias][[inner|left[outer]|right[outer]|full[outer]join[<數(shù)據(jù)庫名>!]<表名>[[as]local_alias][on<聯(lián)接條件>]][into<查詢結(jié)果>|tofile<文件名>[additive]|toprinter[prompt]|toscreen][preferencepreferencename][noconsole][plain][nowait][where<聯(lián)接條件1>...][groupby<分組表達式>...][having<篩選條件>][union[all]<select命令>][orderby<關(guān)鍵字表達式>[asc|desc]...]select后面跟要查詢的數(shù)據(jù)。from后面跟查詢數(shù)據(jù)的來源(表).where后面跟查詢條件。groupby：對查詢結(jié)果進行分組orderby：對查詢結(jié)果進行排序。下面通過例子說明該命令的使用本章查詢的例子將全部基于一個訂貨管理數(shù)據(jù)庫，其包含的表有：例4-1：從職工表中查詢出職工的工資情況職工select工資from可以看到查詢結(jié)果中有重復值，如果要去掉重復值，需要使用distinct短語：

select

distinct

工資from

職工4.2.1簡單查詢例4-2：查詢倉庫表中的所有數(shù)據(jù)倉庫select倉庫號,城市,面積from在SQL語句中也可以使用通配符(*)表示所有字段，上句命令等同于：select

*

from

倉庫例4-3：列出工資多于1230元的職工號當需要查詢滿足某個條件的記錄時，可以使用where

指定查詢條件where工資>1230select職工號from職工例4-4：查詢哪些倉庫中有工資多于1210的職工selectdistinct倉庫號from職工where工資>1210例4-5：列出在倉庫“WH1”或“WH2”，且工資少于1250元的職工號select職工號from職工;where工資<1250and(倉庫號="WH1"or倉庫號="WH2")通過上面的例子可以看出，select命令的執(zhí)行過程是：先從from指定的表中，找出滿足where后指定的條件的記錄，然后再顯示select后指定的字段的值。4.2.2簡單的連接查詢例4-6：列出工資多于1230元的職工號和所在的城市當查詢信息分別出自不同表時，就需要用連接查詢實現(xiàn)。select職工號,城市from職工,倉庫;where(工資>1230)AND職工.倉庫號=倉庫.倉庫號連接條件例4-7：列出所有職工的職工號、工資、所在的城市、所完成的訂購單號及日期、訂購單的供應(yīng)商名select職工號,工資,城市,訂購單號,訂購日期,供應(yīng)商名;where(職工.倉庫號=倉庫.倉庫號and;

職工.職工號=訂購單.職工號and;

訂購單.供應(yīng)商號=供應(yīng)商.供應(yīng)商號)from職工,倉庫,訂購單,供應(yīng)商;4.2.3嵌套查詢

在SQL中，一個select-from-where語句稱為一個查詢塊，將一個查詢塊(子查詢)嵌套在另一個查詢塊(父查詢)的where子句的條件條件中，就稱為嵌套查詢。

嵌套查詢形式如下：select….from….where

(

select….from….where…)子查詢父查詢

在處理嵌套查詢時，首先查詢出子查詢的結(jié)果，然后將子查詢的結(jié)果作為父查詢的條件，再進行父查詢的查詢。select….from….where…

[not]in|[not]exists|<比較運算符>|[any|all|some];

(select….from….where…

)子查詢父查詢在嵌套查詢中，常使用in、notin、exists、notexists、any、some、all、比較運算符等連接父查詢中已有的條件表達式和子查詢。嵌套查詢一般形式如下：in：表示在…之中,是屬于的意思,

即外層查詢where后的條件屬于子查詢集合中的元素。notin則相反exists:用來檢查在子查詢中是否有結(jié)果返回。帶有exists的子查詢不返回任何實際數(shù)據(jù)。當子查詢的查詢結(jié)果集合為非空時，外層的WHERE子句返回”真”，否則返回”假”。notexists則相反。比較運算符：主要有

>、<、>=、<=、<>等。any|some：子查詢的任何一個結(jié)果；all：子查詢的全部結(jié)果；select….from….where…

[not]in|[not]exists|<比較運算符>|[any|all|some];

(select….from….where…

)例4-8：列出哪些城市至少有一個倉庫的職工的工資為1250。select城市from倉庫where倉庫號in;(select倉庫號from職工where工資=1250)select城市from倉庫where首先，至少有一個職工的工資為1250的倉庫都有哪些呢？selectdistinct倉庫號from職工where工資=1250然后，根據(jù)檢索出來的倉庫號，再從倉庫表中檢索出所在的城市倉庫號=?!=集合單值在…之中

in故檢索語句應(yīng)該為：例4-9：列出至少有一名職工的倉庫信息。(select*from職工where倉庫號=倉庫.倉庫號)select*from倉庫whereexists;例4-10：列出還沒有職工的倉庫的信息。(select*from職工where倉庫號=倉庫.倉庫號)select*from倉庫wherenotexists;select*from倉庫where倉庫號notin;(select倉庫號from職工)例4-11：列出職工工資為1230的職工所在的城市。select城市from倉庫where倉庫號=;(select倉庫號from職工where工資=1230)例4-12：檢索有職工的工資大于等于WH1倉庫中任何一名職工的工資的倉庫號。selectdistinct倉庫號from職工where工資>=any;(select工資from職工where倉庫號="WH1")例4-13：檢索有職工的工資大于等于WH1倉庫中所有職工的工資的倉庫號。selectdistinct倉庫號from職工where工資>=all;(select工資from職工where倉庫號="WH1")4.2.4幾個特殊運算符①[not]

between<上界表達式>

and

<下界表達式>

：

表示[不]在上下界之間(包括上下界)。②[not]

like<字符表達式>：表示[不]與…匹配。③is

[not]

null:查詢某個字段的值是否為空值例4-14：列出工資在1220到1240之間的職工信息。例4-16：列出供應(yīng)商表中供應(yīng)商名為“..公司”的供應(yīng)商信息。select*from職工where工資between1220and1240這是一個字符串匹配的查詢，就可以用like運算符，like后的表達式中可以使用下面兩個通配符：①%：可以代表任意長度的字符串；②_

：可以代表任意一個字符。select*from供應(yīng)商

where供應(yīng)商名

like"%公司"例4-15：列出訂單總金額為null的職工號和訂單號。select職工號,訂購單號

from訂購單where總金額isnull4.2.5排序在SQL中，select的查詢結(jié)果是按照表記錄的物理順序給出的，當需要對查詢結(jié)果排序時，可以使用orderby子句對查詢結(jié)果按照一個或多個字段排序。orderby<字段名1>[asc|desc][,<字段名2>[asc|desc]…]注意：orderby子句必須放在其他子句之后,且不可以在子查詢中使用例4-17：先按倉庫號再按工資升序輸出全部職工信息。select*from職工orderby

倉庫號,工資當使用orderby子句對查詢結(jié)果排序時，還可以在select后面使用top

子句只顯示前幾項紀錄。格式為：topn[percent]說明：①不使用[percent]選項時,n是1至32767之間的整數(shù),表示前n項；②使用[percent]選項時,n是0.01至99.99之間的實數(shù),表示前n%項；③top只有與orderby同時使用才有效。例4-18：顯示工資最高的3位職工的信息。例4-19：顯示工資最低的那30%職工的信息。select*top3from職工orderby工資descselect*top30percentfrom職工orderby工資asc例4-20：找出供應(yīng)商所在地的數(shù)目。4.2.6簡單的計算查詢SQL不但具有強大的查詢功能，如果select后面跟avg(),sum(),count(),min(),max()等函數(shù)，還可以以字段名為參數(shù)進行計算selectcount(distinct地址)from供應(yīng)商例4-21：求在北京和上海的所有職工工資總和。selectsum(工資)from職工where倉庫號in；(select倉庫號from倉庫where城市=“北京”or城市=“上?！?例4-22：求在倉庫WH2工作的職工的最高工資。selectmax(工資)from職工where倉庫號=“WH2”例4-23：求每個倉庫的職工的平均工資。4.2.7分組與計算查詢

本查詢中，需要先按倉庫號進行分組，再計算查詢每個倉庫的職工的平均工資。就需要使用groupby子句來實現(xiàn)。其格式為：select倉庫號,avg(工資)from職工groupby

倉庫號groupby<字段名1>[,字段名2…]having<條件>說明：groupby子句用來按指定字段對記錄進行分組，

having<條件>是進一步限定分組的條件，having子句總是跟在groupby之后，不能單獨使用。例4-24：求至少有兩個職工的每個倉庫的職工數(shù)和平均工資。本查詢中，不僅需要先按倉庫號進行分組，并且當分組滿足一定條件時才檢索，然后再計算每個倉庫的職工數(shù)和平均工資。就可以用groupby進行分組，再用having子句來限定分組。select倉庫號,COUNT(*),AVG(工資)from職工;groupby倉庫號havingCOUNT(*)>=2例4-25：查詢職工的情況，查詢結(jié)果按工資升序輸出到文本文件。4.2.8查詢結(jié)果處理select語句默認的輸出是在“瀏覽”窗口中顯示查詢結(jié)果，也可以into子句來修改查詢結(jié)果的去向。如輸出到表、文件、數(shù)組、打印機等?！靖袷?】intodbf|table<表文件名>查詢結(jié)果輸出到永久表【格式2】intocursor<表文件名>查詢結(jié)果輸出到臨時表【格式3】intofile<文本文件名>查詢結(jié)果輸出到文本文件【格式4】intoarray<數(shù)組名>查詢結(jié)果輸出到數(shù)組【格式5】intoprinter[prompt]查詢結(jié)果輸出到打印機select*from職工intofiletxttestorderby工資asc第4章關(guān)系數(shù)據(jù)庫標準語言SQL4.1SQL概述4.2查詢功能4.3操作功能4.4定義功能4.3.1插入數(shù)據(jù)

Insert

into

表名(字段名1,字段名2,…)

values(表達式1,表達式2,...)在表的尾部插入一條新記錄，并將指定的值賦值給對應(yīng)的字段。當插入一條記錄的所有字段時，表名后的各字段名可以省略，若只插入某些字段的數(shù)據(jù)，則必須列出插入數(shù)據(jù)對應(yīng)的字段名。例：向訂購單中插入一組數(shù)據(jù):職工號為E7,供應(yīng)商號為S4,訂單號為OR01,訂購日期為2001/05/25，總金額為100000。命令為：insertinto

訂購單values(“E7”,”S4”,”O(jiān)R01”,{^2001-05-25},100000)若只插入職工號、訂單號數(shù)據(jù)，命令為：insertinto

訂購單(職工號，訂單號)values(“E7”,”O(jiān)R01”)SQL插入數(shù)據(jù)的命令是：4.3.2更新數(shù)據(jù)update

表名

set

字段名1=

表達式1，字段名2=

表達式2…

where<條件>例：給倉庫WH1的職工的工資增加10%，命令為：update

職工set

工資=工資*？(1+10%)(1+0.1)SQL更新數(shù)據(jù)的命令是：4.3.3刪除數(shù)據(jù)deletefrom

表名where

條件注意：當沒有where子句時，將表中的所有記錄。邏輯刪除例：將student表中所有男生的記錄都邏輯刪除Deletefromstudentwhere

性別="男"SQL刪除數(shù)據(jù)的命令是：第4章關(guān)系數(shù)據(jù)庫標準語言SQL4.1SQL概述4.2查詢功能4.3操作功能4.4定義功能4.4.1新建表createtable|dbf表名1[free](

字段名1

字段類型(寬度,小數(shù)位數(shù))[null|notnull][check<條件1>[error<文本1>]][default<表達式1>][primarykey|unique][references<表名2>][tag<索引標識1>][,字段名2字段類型(寬度,小數(shù)位數(shù))…][,…])free用在數(shù)據(jù)庫打開的情況下指明創(chuàng)建自由表設(shè)置字段的有效性規(guī)則、錯誤提示、默認值primarykey子句指定當前字段為主索引關(guān)鍵字；unique子句指定當前字段為候選索引關(guān)鍵字（注意不是惟一索引）references<表名>指定的表作為新建表的永久性父表;tag<標識>指明父表中的關(guān)聯(lián)字段，若缺省該參數(shù)，則默認父表的主索引字段作為關(guān)聯(lián)字段SQL新建表的命令是：例：在學生.dbc中建立一個stuinfo表，所包含的字段及類型、寬度如下表，其中性別字段的取值只能取“男”或“女”兩個值，且默認值為“男”。

opendatabase學生createtable

stuinfo;

(

學號C(13),姓名C(10),性別C(2);

default"男"check(性別="男"or性別="女"),;

出生日期D,班級C(12),聯(lián)系方式C(20);

)字段名字段類型字段寬度學號字符型13姓名字符型10性別字符型2出生日期日期型班級字符型12聯(lián)系方式字符型20<字段類型>:用字母代號表示的字段的數(shù)據(jù)類型，字段類型與代號對照表如下：字段類型代號字段寬度字符型C1-254數(shù)值型N整型I4浮點型F雙精度型B8貨幣型Y8日期型D8日期時間型T8邏輯型L1備注型M4通用型G44.4.2刪除表 d