1、第2章 SQL基礎(chǔ) 2.1 SQL簡(jiǎn)介 2.2 Oracle的數(shù)據(jù)類型 2.3 數(shù)據(jù)定義語(yǔ)言 2.4 數(shù)據(jù)操縱語(yǔ)言 2.5 事務(wù)控制語(yǔ)言 2.6 數(shù)據(jù)控制語(yǔ)言 2.7 SQL運(yùn)算符 2.8 SQL*Plus的函數(shù) 2.9 小結(jié) 習(xí)題二 上機(jī)實(shí)驗(yàn)二 2.1 SQL簡(jiǎn)介SQL(Structured Query Language)稱為結(jié)構(gòu)化查詢語(yǔ)言，最早是由Boyce和Chamberlin在1974年提出的。1976年，SQL開(kāi)始在商品化關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)中應(yīng)用。SQL是一種靈活、有效的語(yǔ)言，它的一些功能專門(mén)用來(lái)處理和檢驗(yàn)關(guān)系型數(shù)據(jù)。1982年，美國(guó)國(guó)家標(biāo)準(zhǔn)化組織(American National

2、 Standard Institute，ANSI)確認(rèn)SQL為數(shù)據(jù)庫(kù)系統(tǒng)的工業(yè)標(biāo)準(zhǔn)。1987年國(guó)際標(biāo)準(zhǔn)化組織(International Organization for Standardization，ISO)也通過(guò)了這一標(biāo)準(zhǔn)。目前，許多關(guān)系數(shù)據(jù)庫(kù)供應(yīng)商都在自己的數(shù)據(jù)庫(kù)中支持SQL語(yǔ)言，如Access、Sybase、SQL Server、Informix、DB2等。SQL已正式成為數(shù)據(jù)庫(kù)領(lǐng)域的一種主流語(yǔ)言。 SQL是所有RDBMS使用的公共語(yǔ)言，它不遵循任何特定的執(zhí)行模式，一次可以訪問(wèn)多個(gè)記錄。其功能不僅僅是查詢，它使用簡(jiǎn)單的維護(hù)數(shù)據(jù)的命令，能夠完成數(shù)據(jù)查詢(Data Query)、數(shù)據(jù)操縱

3、(Data Manipulation)、數(shù)據(jù)定義(Data Definition)和數(shù)據(jù)控制(Data Control)等功能。 2.2 Oracle的數(shù)據(jù)類型Oracle的數(shù)據(jù)類型有兩大類：常用數(shù)據(jù)類型和用戶自定義數(shù)據(jù)類型。常用數(shù)據(jù)類型是數(shù)據(jù)庫(kù)系統(tǒng)提供的一些類型，直接使用即可。用戶自定義數(shù)據(jù)類型一般是在多表操作的情況下，當(dāng)多個(gè)表中的列要存儲(chǔ)相同類型的數(shù)據(jù)時(shí)，為確保這些列具有完全相同的數(shù)據(jù)類型、長(zhǎng)度和是否允許空值等才采用的方法。本節(jié)主要講解Oracle中的常用數(shù)據(jù)類型，如圖2-1所示。 圖2-1 Oracle中的常用數(shù)據(jù)類型 2.2.1 常用數(shù)據(jù)類型1CHARCHAR數(shù)據(jù)類型用來(lái)存儲(chǔ)固定長(zhǎng)度的

4、字符數(shù)據(jù)。語(yǔ)法格式：Var_field CHAR(n)其中，n是指定的字符長(zhǎng)度。如果不指定n的值，則n默認(rèn)為1。在定義表的列類型(如定義CHAR類型)時(shí)，其數(shù)值的長(zhǎng)度不超過(guò)2000字節(jié)，例如：Var_field CHAR(8)在定義變量時(shí)，可以同時(shí)對(duì)其進(jìn)行初始化，例如：Mytext1 CHAR(8)：=Jack; 2VARCHARVARCHAR數(shù)據(jù)類型用來(lái)存儲(chǔ)可變長(zhǎng)度的字符數(shù)據(jù)，最大有32 767字節(jié)。語(yǔ)法格式：Var_field VARCHAR(n)其中，n是指定的字符最大長(zhǎng)度，n必須是正整數(shù)，例如：Var_field VARCHAR(10)在定義變量時(shí)，可以同時(shí)對(duì)其進(jìn)行初始化，例如：Myt

5、ext2 VARCHAR(10)：=Hello world; 3DATEDATE是用來(lái)存儲(chǔ)日期時(shí)間類型的數(shù)據(jù)，用7字節(jié)分別描述世紀(jì)、年、月、日、時(shí)、分、秒。語(yǔ)法格式：date_field DATE說(shuō)明：日期的默認(rèn)格式為DD-MON-YY，分別對(duì)應(yīng)日、月、年，例如17-JUN-2007。月份的表達(dá)要用英文單詞的縮寫(xiě)格式。日期的格式可以設(shè)置為中文格式，例如17-六月-2007。 4BOOLEAN邏輯型(布爾型)變量的值只有TRUE(真)或FALSE(假)兩種。邏輯型變量一般用于判斷狀態(tài)，然后根據(jù)其值是“真”或“假”來(lái)決定程序執(zhí)行分支。關(guān)系表達(dá)式的值就是一個(gè)邏輯值。 5NUMBERNUMBER數(shù)據(jù)類

6、型可用來(lái)表示所有的數(shù)值類型。語(yǔ)法格式：num_field NUMBER(precision，scale)說(shuō)明：precision表示總的位數(shù)；scale表示小數(shù)的位數(shù)，scale默認(rèn)表示小數(shù)位為0。如果實(shí)際數(shù)據(jù)超出設(shè)置精度，則會(huì)出現(xiàn)錯(cuò)誤。例如：num_field NUMBER(8，2)；其中，num_field是一個(gè)整數(shù)部分最多6位，小數(shù)部分最多2位的變量。 2.2.2 數(shù)據(jù)類型轉(zhuǎn)換SQL語(yǔ)言可以進(jìn)行數(shù)據(jù)類型之間的轉(zhuǎn)換。常見(jiàn)的數(shù)據(jù)類型之間的轉(zhuǎn)換函數(shù)如下：(1) TO_CHAR：將NUMBER和DATE類型轉(zhuǎn)換成VARCHAR類型。(2) TO_DATE：將CHAR轉(zhuǎn)換成DATE類型。(3) T

7、O_NUMBER：將CHAR轉(zhuǎn)換成NUMBER類型。此外，SQL語(yǔ)言還會(huì)自動(dòng)地轉(zhuǎn)換各種類型，如可以將數(shù)值類型轉(zhuǎn)換成字符串類型。 2.3 數(shù)據(jù)定義語(yǔ)言數(shù)據(jù)定義語(yǔ)言(DDL)包含一組命令，用來(lái)創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象(如表等)。本節(jié)主要講述用數(shù)據(jù)定義語(yǔ)言創(chuàng)建表、修改表和刪除表。本章中將要用到的實(shí)例主要以下面列出的三個(gè)表(表2-1表2-3)為例。 表2-1 雇員表(Employee1) 表2-2 部門(mén)表(Department1) 表2-3 雇員技能表(EmployeeSkill1) 2.3.1 CREATE TABLE命令要?jiǎng)?chuàng)建新的數(shù)據(jù)表，可以使用CREATE TABLE命令。在以自己的模式創(chuàng)建表時(shí)，必須擁有

8、CREATE TABLE系統(tǒng)權(quán)限。在其他用戶模式中創(chuàng)建表時(shí)，必須擁有CREATE ANY TABLE系統(tǒng)權(quán)限。語(yǔ)法格式：CREATE TABLE table_name ( column_name datatypeDEFAULT expresssion column_constraint, column_name datatype) 說(shuō)明：table_name：表的名稱。column_name：指定表的一個(gè)列的名字。datatype：該列的數(shù)據(jù)類型。DEFAULT expresssion：指定由expresssion表達(dá)式定義的默認(rèn)值。column_constraint：定義一個(gè)完整性約束作為列

9、定義的一部分。 column_constraint子句的基本語(yǔ)法格式如下： CONSTRAINT constraint_name NOT NULL UNIQUE PRIMARY KEY REFERENCES schema. table_name(column_name) CHECK(condition)其中：NOT NULL定義該列是否允許為空；UNIQUE定義字段的唯一性；PRIMARY KEY定義字段為主鍵；REFERENCES定義外鍵約束；CHECK(condition)定義該列數(shù)據(jù)必須符合的條件。 【例】 創(chuàng)建雇員表Employee1。SQLCREATE TABLE Employee1

10、(cEcodechar(6) constraint E_pk primary key,cEnamechar(10) NOT NULL,cSex char(2), cPcode char(6) constraint E1_fk references Department1 (cPcode),salary Number(6,1),cScode Char(6) constraint E2_fk references EmployeeSkill1 (cScode),dBirthDate Date,cAddress varchar2(30),cPhonechar(20),cEmailchar(30)；表

11、已創(chuàng)建。 【例】 創(chuàng)建部門(mén)表Department1。SQLCREATE TABLE Department1(cPcodechar(6) constraint dt_pk primary key,cPnamechar(20) ,cPhead char(10),cLocation char(20)；表已創(chuàng)建。 2.3.2 ALTER TABLE命令利用ALTER TABLE命令可以修改表，包括增加列、修改列的屬性和刪除列。語(yǔ)法格式：ALTER TABLE schema.tablename ADD (columnname datatype DEFAULT expressioncolumn_const

12、raint,n)MODIFY (columnname datatype DEFAULT expression column_constraint,n)DROP COLUMN columnname 說(shuō)明：schema：修改的表的擁有者。tablename：修改的表名。ADD：添加列或完整性約束。MODIFY：修改已有列的定義。DROP：從表中刪除列或約束。 【例】 修改雇員表Employee1。在列中增加一列，用來(lái)存放性別數(shù)據(jù)。查看修改后的結(jié)果，如圖2-2所示。SQLALTER TABLE Employee1 ADD CSEX CHAR(2)；表已更改。如果所添加的列包含空值，則必須分三步進(jìn)行：

13、 添加沒(méi)有NOT NULL說(shuō)明的列； 在新列中添加數(shù)據(jù)到所有行； 將列修改成NOT NULL。 圖2-2 修改后的雇員表結(jié)構(gòu) 【例】 修改雇員表Employee1中列的寬度、小數(shù)位、數(shù)據(jù)類型或缺省值。SQL ALTER TABLE Employee1 MODIFY cEname varChar2(20)； 【例】 從表中刪除一個(gè)列。SQL ALTER TABLE Employee1 DROP COLUMN cEmail； 說(shuō)明： 一次只能撤消一列； 必須保證表中至少還剩有一列； 列中有數(shù)據(jù)也可被撤消。 2.3.3 DROP命令使用DROP TABLE可以刪除表。不需要使用的表應(yīng)及時(shí)刪除，否則它

14、將占用不必要的存儲(chǔ)空間。使用DROP TABLE刪除表時(shí)，表中存儲(chǔ)的數(shù)據(jù)也將同時(shí)被刪除。語(yǔ)法格式：DROP TABLE table_name 【例】 刪除Department1表。SQLDROP TABLE Department1；命令執(zhí)行后，將刪除Department1表和表中的所有內(nèi)容。 2.3.4 TRUNCATE TABLE命令使用TRUNCATE TABLE可以刪除表中的全部記錄。利用此命令可以釋放占用的數(shù)據(jù)塊表空間。此操作不可退回。語(yǔ)法格式：TRUNCATE TABLE table_name 【例】 清空EmployeeSkill1表。SQLTRUNCATE TABLE Emplo

15、yeeSkill1；TRUNCATE TABLE命令執(zhí)行后，將刪除表中的所有數(shù)據(jù)，且不能恢復(fù)，所以使用時(shí)一定要慎重。TRUNCATE TABLE刪除了指定表中所有的行，但表的結(jié)構(gòu)及其列、約束、索引等保持不變。它的刪除全部記錄的功能等同于不帶WHERE子句的DELETE語(yǔ)句，但其執(zhí)行速度更快。TRUNCATE TABLE不能刪除帶外鍵約束的表中數(shù)據(jù)。 2.4 數(shù)據(jù)操縱語(yǔ)言 2.4.1 INSERT命令I(lǐng)NSERT命令用于給創(chuàng)建好的表添加記錄。語(yǔ)法格式：INSERT INTO table_name column_list VALUES(values) |subquery說(shuō)明：table_name：

16、要插入數(shù)據(jù)的表的名稱。column_list：要插入數(shù)據(jù)的表中的字段名稱列表。VALUES：插入表中的值或值列表。 subquery：若有此部分，則表示由一個(gè)子查詢來(lái)向表中插入數(shù)據(jù)。插入數(shù)據(jù)時(shí)，值列表必須與字段名稱列表的順序和數(shù)據(jù)類型一致。如果不指定column_list，則在VALUES子句中要給出每一列的值，且順序和數(shù)據(jù)類型必須與原表一致。插入的數(shù)據(jù)若是字符型，則必須用單引號(hào)括起來(lái)；若列值為空，則值必須置為NULL；若列值為默認(rèn)值，則用DEFAULT。 【例】 向Employee1表中插入如下數(shù)據(jù)：0001、向林、P002、1500、S015、19870211、珠海、12365478、男S

17、QLINSERT INTO Department1 VALUES (P002, 生產(chǎn)部, 何明, 珠海)；SQLINSERT INTO EmployeeSkill1VALUES (S015, 車工, 張大文, 一車間)；SQLINSERT INTO Employee1 VALUES(0001，向林，P002，1500，S015，TO_DATE(19870211, YYYYMMDD), 珠海, 12365478, xs126 ，男)；提問(wèn)：為什么要先向表Department1和EmployeeSkill1中插入數(shù)據(jù)？若只執(zhí)行第三條插入語(yǔ)句，則可能會(huì)出現(xiàn)什么問(wèn)題？插入數(shù)據(jù)時(shí)應(yīng)該注意哪些環(huán)節(jié)？ 2.

18、4.2 UPDATE命令UPDATE命令使用戶可以修改表中已有記錄的值。語(yǔ)法格式：UPDATE table_name SET column_name=valueWHERE condition說(shuō)明：table_name：要更新數(shù)據(jù)的表的名稱。column_name：要更新數(shù)據(jù)的表中的字段名。value：表示將要更新字段的更改值。WHERE：指定哪些記錄需要更新值。若沒(méi)有此項(xiàng)，則將更新所有記錄的指定列的值。 【例】 將編碼為“0001”的雇員的住址改為深圳。SQLUPDATE Employee1SET vAddress=深圳WHERE cEcode=0001；【例】 將所有雇員的基本工資增加200

19、元。SQLUPDATE Employee1SET salary = salary +200； 2.4.3 DELETE命令DELETE命令使用戶可以刪除表中的記錄。語(yǔ)法格式：DELETE FROM table_name WHERE condition說(shuō)明：table_name：要?jiǎng)h除數(shù)據(jù)的表的名稱。WHERE condition：指定滿足哪些條件的記錄需要被刪除。若沒(méi)有此項(xiàng)，則將刪除所有記錄。 【例】 將編碼為“0001”的雇員的記錄刪除。SQLDELETE FROM Employee1WHERE cEcode=0001； 2.4.4 SELECT命令使用數(shù)據(jù)庫(kù)和表的主要目的是存儲(chǔ)數(shù)據(jù)，以便在

20、需要時(shí)檢索、統(tǒng)計(jì)或組織輸出。通過(guò)SELECT語(yǔ)句就可以從數(shù)據(jù)表或視圖中獲取數(shù)據(jù)，迅速、方便地檢索數(shù)據(jù)。同時(shí)還可以將返回的記錄進(jìn)行排序、分組，進(jìn)行多表連接，并可以將查詢得到的數(shù)據(jù)利用一個(gè)INSERT語(yǔ)句將其插入到另一個(gè)表中。SELECT語(yǔ)句比較復(fù)雜，本節(jié)介紹其主要的子句及功能。 語(yǔ)法格式：SELECT select_ listFROM table_name WHERE search_condition GROUP BY group_expresssionHAVING search_conditionORDER BY order_expresssionASC|DESC 說(shuō)明：select_ lis

21、t：指定要選擇的列。FROM子句：指定需要查詢數(shù)據(jù)的表名。WHERE子句：指定查詢條件。search_condition是查詢的條件。GROUP BY子句：指定將查詢結(jié)果進(jìn)行分組。group_expresssion為分組表達(dá)式。HAVING子句：指定按條件進(jìn)行分組統(tǒng)計(jì)。search_condition為分組條件。ORDER BY 子句：指定查詢結(jié)果排序。order_expresssion為排序指定表達(dá)式。ASC|DESC：設(shè)置查詢結(jié)果按升序或降序排列。 1在表中查詢指定的列使用WHERE子句可查詢指定的行，使用SELECT子句可查詢指定的列，查詢所有的列可以使用通配符“*”。選擇列時(shí)，各列名之

22、間用逗號(hào)分隔?！纠?查詢Department1表中的所有數(shù)據(jù)。SQLSELECT * FROM Department1；【例】 查詢雇員的姓名、性別、 號(hào)碼和家庭住址。SQLSELECT cName，cSex，cPhone，cAddress FROM employee1； 2更改查詢結(jié)果中的列標(biāo)題若希望查詢結(jié)果中的某些列或所有列顯示時(shí)使用自己選擇的列標(biāo)題，則可以在列名之后使用AS子句來(lái)更改查詢結(jié)果的列標(biāo)題。其格式如下：column_name AS column_alias其中：column_alias為指定的列標(biāo)題?！纠?查詢Department1表中的cPcode、cPname，將結(jié)果中

23、的標(biāo)題分別指定為部門(mén)代碼和部門(mén)名稱。SQLSELECT cPcode AS 部門(mén)代碼，cPname AS 部門(mén)名稱 FROM Department1； 3計(jì)算列值使用SELECT對(duì)列進(jìn)行查詢時(shí)，在結(jié)果集中可以輸出經(jīng)過(guò)計(jì)算后得到的值，即可以使用表達(dá)式作為SELECT的結(jié)果。其格式如下：SELECT expression , expression【例】 創(chuàng)建一個(gè)銷售表XSB，計(jì)算各種產(chǎn)品的銷售額。銷售表的結(jié)構(gòu)如表2-4所示。 表2-4 銷售表XSB 下列語(yǔ)句將列出產(chǎn)品編號(hào)、產(chǎn)品名稱和銷售金額。SQLSELECT CPBH，CPMC，JG*XSSL AS 銷售金額FROM XSB；表達(dá)式中可以使用算

24、術(shù)運(yùn)算符+、-、*、/，這些運(yùn)算符均可用于數(shù)字類型的計(jì)算。【例】 創(chuàng)建一個(gè)表SCJ，計(jì)算學(xué)生各科考試的總分。表的結(jié)構(gòu)如表2-5所示。 下列語(yǔ)句將列出各科考試成績(jī)及總分。SQLSELECT XH，XM，YW，SX，YY，YW+SX+YY AS 總分FROM SCJ； 表2-5 表SCJ 4去掉結(jié)果集中的重復(fù)值在表中進(jìn)行選擇列操作時(shí)，可能會(huì)出現(xiàn)重復(fù)值。例如，若對(duì)XSB表只查詢XSSL和JG，則可能出現(xiàn)多行重復(fù)的現(xiàn)象。消除重復(fù)行的方法是使用DISTINCT關(guān)鍵字，其格式如下：SELECT DISTINCT column_name ,column_name, 其中，關(guān)鍵字DISTINCT的含義是在結(jié)果

25、集中的重復(fù)行只保留一個(gè)?！纠?查詢XSB表中的XSSL和JG，同時(shí)消除結(jié)果集中重復(fù)的行。SQLSELECT DISTINCT XSSL AS 銷售數(shù)量，JG AS 價(jià)格FROM XSB；如果使用關(guān)鍵字ALL，則將保留所有的行。 【例】 查詢XSB表中的XSSL和JG，不消除結(jié)果集中重復(fù)的行。SQLSELECT ALL XSSL AS 銷售數(shù)量，JG AS 價(jià)格FROM XSB；當(dāng)不注明DISTINCT或ALL時(shí)，系統(tǒng)默認(rèn)的是ALL。 5選擇指定的行使用WHERE子句可以查詢指定的行。1) 表達(dá)式比較在WHERE子句的條件表達(dá)式中，常用的比較運(yùn)算符有7個(gè)，分別是：=(等于)、(大于)、=(大于

26、或等于)、=(小于或等于)、(不等于)、!=(不等于)。 【例】 查詢總分高于285分的女生的資料。SQLSELECT XH，XM，XB，ZFFROM SCJWHERE XB=女 AND ZF285；該代碼執(zhí)行后，將顯示所有總分高于285分的女生的相關(guān)資料?！纠?查詢所有女職員的姓名、性別、 號(hào)碼和家庭住址。SQLSELECT cName，cSex，cPhone，cAddressFROM employee1WHERE cSex=女； 2) 模式匹配利用LIKE運(yùn)算符可以運(yùn)算一個(gè)字符串是否與指定的定符串相匹配，返回值為邏輯值TURE或FALSE。其使用格式如下：string_expressio

27、n NOT LIKE string_expression在使用LIKE時(shí)，可以使用兩個(gè)通配符：“%”和“_”。“%”代表任意多個(gè)任意字符，“_”代表任意一個(gè)任意字符。 【例】 查詢CPMC中帶有“空調(diào)”的產(chǎn)品的資料。SQLSELECT CPBH，CPMC，JG，XSSLFROM XSBWHERE CPMC LIKE %空調(diào)%；【例】 查詢所有姓“李”且為單名的學(xué)生的資料。SQLSELECT XH，XM，XB，ZFFROM SCJWHERE XM LIKE 李_； 3) 范圍比較用于范圍比較的運(yùn)算符有兩個(gè)，即BETWEEN和IN。當(dāng)查詢條件是某個(gè)值的范圍時(shí)，可以使用BETWEEN關(guān)鍵字。其格式如

28、下：expression NOT BETWEEN expression1 AND expression2當(dāng)查詢條件是可以指定的值的列表時(shí)，使用IN關(guān)鍵字來(lái)限制查詢的值。其格式如下：expression IN (expression1，expression2，n) 【例】 查詢總分介于265至285分的資料。SQLSELECT *FROM SCJWHERE ZF BETWEEN 265 AND 285；【例】 查詢JG為“1500”、“1800”、“2600”、“3000”的產(chǎn)品的情況。SQLSELECT *FROM XSBWHERE JG IN (1500，1800，2000，3000)； 4

29、) 空值比較當(dāng)需要判定一個(gè)數(shù)據(jù)是否存在時(shí)，可使用IS NULL關(guān)鍵字。其格式如下：expression IS NOT NULL當(dāng)不使用NOT時(shí)，若表達(dá)式expression的值為空值，則返回TURE，否則返回FALSE；當(dāng)使用NOT時(shí)，結(jié)果剛好相反?！纠?查詢沒(méi)有參加數(shù)學(xué)考試的學(xué)生資料。SQLSELECT *FROM SCJWHERE SX IS NULL； 6分組查詢結(jié)果使用GROUP BY子句可以將查詢的結(jié)果按字段分組。其格式如下：GROUP BY ALL group_by_expression , , n指定ALL將顯示所有組。使用GROUP BY 子句后，SELECT子句中的列表只能

30、包含在GROUP BY中指出的列或在統(tǒng)計(jì)函數(shù)中指定的列。 【例】 分組查詢各個(gè)部門(mén)雇員的姓名、性別、 號(hào)碼和家庭住址。SQLSELECT cName，cSex，cPhone，cAddress，cPcodeFROM employee1GROUP BY cPcode； 7篩選分組結(jié)果使用GROUP BY子句和統(tǒng)計(jì)函數(shù)對(duì)數(shù)據(jù)進(jìn)行分組后，還可以使用HAVING子句對(duì)分組數(shù)據(jù)進(jìn)行進(jìn)一步的篩選。例如，在XSB表中查找產(chǎn)品的平均價(jià)格在3000元以上的生產(chǎn)商，就是在表中按SCS分組后再篩選出平均價(jià)格大于或等于3000元的產(chǎn)品。分組篩選命令的格式如下：HAVING 【例】 在XSB表中查找產(chǎn)品的平均價(jià)格在300

31、0元以上的生產(chǎn)商和平均價(jià)格。下列語(yǔ)句將列出產(chǎn)品編號(hào)、產(chǎn)品名稱和銷售金額。SQLSELECT SCS AS 生產(chǎn)商，AVG(JG) AS 平均價(jià)格FROM XSBGROUP BY SCSHAVING AVG(JG)=3000；在SELECT語(yǔ)句中，當(dāng)WHERE、GROUP BY 與 HAVING子句都被使用時(shí)，要注意它們的作用和執(zhí)行順序。WHERE用于篩選由FROM指定的數(shù)據(jù)對(duì)象；GROUP BY 用于對(duì)WHERE的結(jié)果進(jìn)行分組；HAVING則用于對(duì)GROUP BY以后的分組數(shù)據(jù)進(jìn)行過(guò)濾。 8排序查詢結(jié)果在應(yīng)用中經(jīng)常需要對(duì)查詢結(jié)果進(jìn)行排序輸出，使用ORDER BY子句可以將查詢的結(jié)果按指定的條件

32、排序。其格式如下： ORDER BY ASC|DESC【例】 將冰箱類產(chǎn)品的名稱按價(jià)格的高低降序輸出。SQLSELECT CPMC，JG FROM XSB WHERE CPMC LIKE “%冰箱%”O(jiān)RDER BY JG DESC； 【例】 分組查詢各個(gè)部門(mén)的工資，結(jié)果按工資升序輸出。SQLSELECT cEname，cPcode，salaryFROM employee1GROUP BY cPcodeORDER BY salary ASC； 2.5 事務(wù)控制語(yǔ)言 2.5.1 COMMIT命令當(dāng)向數(shù)據(jù)庫(kù)發(fā)出COMMIT命令時(shí)，意味著一個(gè)數(shù)據(jù)庫(kù)事務(wù)結(jié)束了。如果執(zhí)行的是對(duì)數(shù)據(jù)的修改，那么所做的工作

33、也就永久地寫(xiě)進(jìn)了數(shù)據(jù)庫(kù)中，這時(shí)其他用戶可以立即看到你所做的修改，而同時(shí)修改之前的數(shù)據(jù)將徹底消失。語(yǔ)法格式：SQL statement1;SQL statement2;COMMIT;說(shuō)明：COMMIT：保證statement1和statement2所做的修改持久有效。 【例】 將經(jīng)理職位的代碼由0015改為C015。完成該操作的代碼如下：SQLUPDATE EmployeeSkill1SET cScode=C015WHERE cSname=經(jīng)理；UPDATE Employee1SET cScode=C015WHERE cScode =0015；COMMIT；因?yàn)樵贓mployee1表和Emplo

34、yeeSkill1表中都含有cScode數(shù)據(jù)，所以當(dāng)修改職位的代碼時(shí)，必須同時(shí)修改兩個(gè)表中的數(shù)據(jù)，否則會(huì)出現(xiàn)數(shù)據(jù)不一致的現(xiàn)象。 2.5.2 ROLLBACK命令當(dāng)我們?cè)趫?zhí)行SQL語(yǔ)句出現(xiàn)錯(cuò)誤時(shí)，通常希望通過(guò)一個(gè)顯式的指令來(lái)撤消當(dāng)前的修改，利用ROLLBACK命令即可實(shí)現(xiàn)撤消操作的功能。語(yǔ)法格式：SQL statement1;SQL statement2;ROLLBACK;說(shuō)明：ROLLBACK：撤消自上一個(gè)COMMIT語(yǔ)句執(zhí)行以來(lái)所做的修改。 【例】 有如下一段代碼，試分析其功能。SQL DELETE FROM EmployeeSkill1;ROLLBACK;DELETE FROM Emplo

35、yeeSkill1 WHERE cScode=0010;SELECT * FROM EmployeeSkill1 WHERE cScode=0010;COMMIT;上述代碼的含義是：從EmployeeSkill1表中將cScode的值為“0010”的記錄刪除，但在具體操作時(shí)，忘記加上WHERE子句里的條件，導(dǎo)致表中的所有記錄都被刪除了，為了立即恢復(fù)數(shù)據(jù)，使用了ROLLBACK，之后再使用帶有條件的DELETE語(yǔ)句將不要的數(shù)據(jù)刪除。 2.5.3 SAVEPOINT命令在實(shí)際工作中，當(dāng)出現(xiàn)錯(cuò)誤時(shí)，我們一般不希望一次性地回滾一個(gè)很大的事務(wù)，而是將一個(gè)大的事務(wù)分成很多小事務(wù)，將每一個(gè)小塊作為一個(gè)保存點(diǎn)

36、，這樣當(dāng)我們?cè)趫?zhí)行程序的時(shí)候如果發(fā)生錯(cuò)誤，也只是回滾到最近或指定的保存點(diǎn)。這對(duì)于要求有大量的多步更新操作是很有幫助的。當(dāng)程序發(fā)生錯(cuò)誤時(shí)，也只是回滾到最近的保存點(diǎn)，而不是撤消整個(gè)事務(wù)，這樣就不用再次處理保存點(diǎn)以前的語(yǔ)句，從而減少了不必要的數(shù)據(jù)庫(kù)開(kāi)銷。 語(yǔ)法格式：SQL statement1;SAVEPOINT savepoint_name;SQL statement2;ROLLBACK to savepoint_name;說(shuō)明：savepoint_name：SAVEPOINT的名稱。 【例】 有如下一段代碼，試分析其功能。SQLSAVEPOINT jack;UPDATE Employee1 SE

37、T cEcode=C025WHERE cEname=jack；SAVEPOINT mary;UPDATE Employee1 SET cEcode=C035WHERE cEname =mary； 上述代碼段中用SAVEPOINT語(yǔ)句創(chuàng)建了兩個(gè)標(biāo)記jack和mary。如果想回滾到第二個(gè)DML語(yǔ)句前，則需要使用的代碼如下：ROLLBACK TO mary;如果想要回滾到最前面，則使用語(yǔ)句：ROLLBACK TO jack;SAVEPOINT的優(yōu)點(diǎn)是：它可以根據(jù)條件撤消自上一條COMMIT語(yǔ)句執(zhí)行以來(lái)所做的數(shù)據(jù)修改。一旦執(zhí)行了COMMIT語(yǔ)句，所有的SAVEPOINT語(yǔ)句都將被取消。 2.6 數(shù)據(jù)控

38、制語(yǔ)言 2.6.1 GRANT命令當(dāng)創(chuàng)建了新的用戶時(shí)，該用戶的權(quán)限域?yàn)榭眨艿卿浀絆racle，但不能進(jìn)行任何操作，必須授予其一定的權(quán)限之后，才能進(jìn)行相關(guān)的操作。利用GRANT語(yǔ)句可以給用戶授權(quán)。 語(yǔ)法格式：GRANT system_priv|role TO userWITH ADMIN OPTION說(shuō)明：system_priv：要授予的系統(tǒng)權(quán)限。如果把權(quán)限授予user，則Oracle就把權(quán)限添加到該用戶的權(quán)限域，該用戶可以立即使用該權(quán)限。role：要授予的角色。一旦授予用戶角色，該用戶就能行使該角色的權(quán)限。WITH ADMIN OPTION：把向其他用戶授權(quán)的能力傳遞給被授予者。 【例】

39、授予用戶JACK以DBA的角色。GRANT DBA TO JACK;授予用戶JACK一些系統(tǒng)權(quán)限，并且該用戶可以向其他用戶授權(quán)，代碼如下：GRANT CREATE ANY TABLE，CREATE ANY VIEW TO JACKWITH ADMIN OPTION; 2.6.2 REVOKE命令當(dāng)需要從用戶手中將操作的某些權(quán)限收回時(shí)，可以使用REVOKE命令。在實(shí)際工作中，經(jīng)常會(huì)遇到這種情況，如工作崗位的變化、人員的調(diào)動(dòng)等，這些都將涉及到用戶的權(quán)限管理問(wèn)題。 語(yǔ)法格式：REVOKE system_priv|role FROM userWITH ADMIN OPTION說(shuō)明：system_pri

40、v：授予用戶的系統(tǒng)權(quán)限。role：賦予用戶的角色。 【例】 將授予用戶JACK的DBA角色收回。SQLREVOKE DBA FROM JACK;收回用戶JACK和MARY對(duì)表XSB的刪除和插入數(shù)據(jù)的權(quán)限，代碼如下：SQLREVOKE DELETE，INSERT ON XSB FROM JACK，MARY； 2.7 SQL運(yùn)算符 2.7.1 算術(shù)運(yùn)算符算術(shù)運(yùn)算符主要用來(lái)進(jìn)行加、減、乘、除等算術(shù)運(yùn)算。SQL中常用的算術(shù)運(yùn)算符有+、-、*、/等，如表2-6所示。 表2-6 算術(shù)運(yùn)算符 2.7.2 比較運(yùn)算符比較運(yùn)算符用于將一個(gè)表達(dá)式和另外一個(gè)表達(dá)式進(jìn)行比較。其結(jié)果總是TRUE、FALSE和NULL三

41、種。我們常在條件控制語(yǔ)句和SQL數(shù)據(jù)處理語(yǔ)句的WHERE子句中使用比較運(yùn)算符。SQL中常用的比較操作符有=、!=、=，如表2-7所示。 表2-7 比較運(yùn)算符 在前面的例題中，我們已經(jīng)看過(guò)多個(gè)使用比較運(yùn)算符的例子，例如：SQLUPDATE EmployeeSkill1SET cScode=C015WHERE cSname=經(jīng)理；相關(guān)的運(yùn)算符可以在任意復(fù)雜表達(dá)式之間進(jìn)行。 2.7.3 邏輯運(yùn)算符SQL中常用的邏輯運(yùn)算符有AND、NOT和OR。其中，AND和OR是二元運(yùn)算符，NOT是一元運(yùn)算符。運(yùn)算時(shí)，只有在兩個(gè)操作數(shù)都是TRUE的情況下，AND才會(huì)返回TRUE。而只要有一個(gè)操作數(shù)為T(mén)RUE，OR就

42、會(huì)返回TRUE。NOT返回與操作數(shù)相反的值，當(dāng)與比較運(yùn)算符連用時(shí)，表示非。例如：NOT age=20 表示age小于20。 2.7.4 集合運(yùn)算符集合運(yùn)算符又稱為謂詞運(yùn)算符。常用的集合運(yùn)算符如表2-8所示。 表2-8 集合運(yùn)算符 例如，LIKEteac%表示如果一個(gè)字符串的前4個(gè)為teac，后面為零個(gè)或多個(gè)任意字符，則都滿足集合條件；LIKE teac_er表示如果一個(gè)字符串的前4個(gè)為teac，第6、7個(gè)字符為er，第5個(gè)為任意字符，則都滿足集合條件；BETWEEN 100 AND 400表示數(shù)值在100和400之間。 2.7.5 連接運(yùn)算符連接運(yùn)算符由兩個(gè)豎起來(lái)的線條(11)組成，它用來(lái)將兩

43、個(gè)字符串連接起來(lái)，形成一個(gè)新的字符串。例如，ORACLE|程序員將返回字符串ORACLE程序員。 2.7.6 操作符優(yōu)先級(jí)當(dāng)我們使用括號(hào)指定計(jì)算的順序時(shí)，運(yùn)算的順序就被確定下來(lái)。如果不使用括號(hào)，則要根據(jù)運(yùn)算符的優(yōu)先級(jí)決定計(jì)算的順序。邏輯運(yùn)算符的計(jì)算順序是：NOT、AND、OR。比較運(yùn)算符的計(jì)算順序一般按照出現(xiàn)的先后順序進(jìn)行。在進(jìn)行比較復(fù)雜的表達(dá)式計(jì)算時(shí)，為了使結(jié)構(gòu)清晰，最好用圓括號(hào)將不同的運(yùn)算符括起來(lái)，以清晰地控制運(yùn)算，從而避免不必要的混亂。 2.8 SQL*Plus的函數(shù) 2.8.1 單行函數(shù)單行函數(shù)返回表中查詢的每一行的值。單行函數(shù)既可用于SELECT語(yǔ)句，也可通過(guò)WHERE子句指出單行函

44、數(shù)的條件表達(dá)式。例如，若要以大寫(xiě)方式顯示Employee1表的cName列的內(nèi)容，則可以用單行函數(shù)顯示大寫(xiě)的cName。這些函數(shù)又被稱為標(biāo)量函數(shù)。語(yǔ)法格式：SELECT function_name(arg1,arg2,) 說(shuō)明：function_name：函數(shù)名。arg1和arg2：函數(shù)的參數(shù)。常用的單行函數(shù)包括字符函數(shù)、數(shù)值函數(shù)等。 2.8.2 多行函數(shù)多行函數(shù)將查詢結(jié)果的多個(gè)行組合進(jìn)來(lái)，又稱為組合函數(shù)。將多行組合起來(lái)時(shí)可以附帶條件，也可以不帶條件。語(yǔ)法格式：SELECT function_name(column_name) FROM table_name WHERE condition;說(shuō)

45、明：function_name：函數(shù)名。column_name：執(zhí)行函數(shù)運(yùn)算的列名。table_name：表名。condition：檢索結(jié)果的條件。常用的多行函數(shù)有AVG、SUM、COUNT、MAX、MIN、STDDEV、VARIANCE。 2.8.3 常用函數(shù)下面介紹在Oracle中常用的函數(shù)。1字符函數(shù)字符函數(shù)接受字符輸入，用于對(duì)字符串進(jìn)行處理，返回字符或數(shù)值。SQL中的字符處理函數(shù)有二十多個(gè)。下面介紹如表2-9所示的常用字符函數(shù)。 表2-9 常用字符函數(shù) 【例】 求字符串“student”的長(zhǎng)度。SQLSELECT LENGTH(student) FROM DUAL;輸出結(jié)果為“7”。【

46、例】 連接兩個(gè)字符串。SQLSELECT CONCAT(MY, BOOK) FROM DUAL;輸出的結(jié)果是“MY BOOK”。【例】 以大寫(xiě)方式顯示字符串。SQLSELECT UPPER(daniel) FROM DUAL;輸出的結(jié)果是“DANIEL”。 【例】 將字符串的首字母以大寫(xiě)方式顯示。SQLSELECT INITCAP(marks) FROM DUAL;輸出的結(jié)果是“Marks”?！纠?在字符串的左側(cè)填充指定個(gè)數(shù)的指定字符。SQLSELECT LPAD(THIS IS MY ORACLE,20, XY) FROM DUAL;輸出的結(jié)果是“XYXTHIS IS MY ORACLE”

47、。SQLSELECT LPAD(30000,7, $) FROM DUAL;輸出的結(jié)果是“$30000”。 【例】 從左側(cè)刪除輸入串的字符。SQLSELECT LTRIM(DANIEL THOMSON，DANIEL) FROM DUAL;輸出的結(jié)果是“THOMSON”。 2數(shù)值函數(shù)數(shù)值函數(shù)對(duì)數(shù)值的數(shù)據(jù)類型進(jìn)行計(jì)算。數(shù)值函數(shù)的輸出是任何值，具體依賴于SELECT語(yǔ)句中說(shuō)明的函數(shù)。數(shù)值函數(shù)只接受和顯示數(shù)字值。常用的數(shù)值函數(shù)如表2-10所示。 表2-10 常用的數(shù)值函數(shù) 【例】 返回大于或等于特定值的最小整數(shù)。SQLSELECT CEIL() FROM DUAL;輸出的結(jié)果是“7”?！纠?返回小于

48、或等于特定值的最大整數(shù)。SQLSELECT FLOOR() FROM DUAL;輸出的結(jié)果是“-9”?！纠?求余數(shù)。SQLSELECT MOD(7，3) FROM DUAL;輸出的結(jié)果是“1”?！纠?對(duì)數(shù)值進(jìn)行四舍五入。SQLSELECT ROUND(，2) FROM DUAL;輸出的結(jié)果是“4516.86”。 3統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)函數(shù)又稱分組函數(shù)，是指從一組記錄中返回匯總信息。常用的統(tǒng)計(jì)函數(shù)如表2-11所示。 表2-11 常用的統(tǒng)計(jì)函數(shù) 【例】 求語(yǔ)文的平均成績(jī)。SQLSELECT AVG(YW)AS語(yǔ)文FROM SCJ;【例】 求語(yǔ)文成績(jī)及格的人數(shù)。SQLSELECT COUNT(*) FR

49、OM SCJ WHERE YW=60; 4. 日期函數(shù)日期函數(shù)執(zhí)行的運(yùn)算有：操縱日期，從日期中提取年、月、日，計(jì)算兩個(gè)日期間隔多少個(gè)月或多少天等。例如，為了計(jì)算貨物從發(fā)出到實(shí)際交付之間相隔多長(zhǎng)時(shí)間，就要用日期函數(shù)來(lái)進(jìn)行計(jì)算。常用的日期函數(shù)如表2-12所示。 表2-12 常用的日期函數(shù) 【例】 在指定日期上增加月份。SQLSELECT ADD_MONTHS(12-APR-07,4) FROM DUAL;輸出的結(jié)果是“12-AUG-07”?！纠?比較兩個(gè)日期，顯示其中較大者的日期。SQLSELECT GREATEST(15-APR-07，16-MAY-07) FROM DUAL;輸出的結(jié)果是“1

50、6-MAY-07”?！纠?求某月的最后一天的日期。SQLSELECT LAST_DAY(10-AUG-07) FROM DUAL;輸出的結(jié)果是“31-AUG-07”。 【例】 求兩個(gè)日期相差的月份數(shù)。SQLSELECT MONTHS_BETWEEN(20-JAN-07，13-MAY-07) FROM DUAL;輸出的結(jié)果是“”?！纠?求下一個(gè)日子。SQLSELECT NEXT_DAY(04-AUG-07，MONDAY) FROM DUAL;輸出的結(jié)果是“06-AUG-07”?！纠?將日期型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)。SQLSELECT TO_CHAR(sysdate，DD-MONTH-YYYY

51、) FROM DUAL;輸出的結(jié)果是“07-AUG-2007”。【例】 字符型數(shù)據(jù)轉(zhuǎn)換為日期型數(shù)據(jù)。SQLSELECT TO_DATE(14-AUG-07，DD-MONTH-YYYY) FROM DUAL;輸出的結(jié)果是“14-AUG-07”。 2.9 小 結(jié)SQL稱為結(jié)構(gòu)化查詢語(yǔ)言。它可在很多關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)中使用，它的功能十分強(qiáng)大，集數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)控制、數(shù)據(jù)定義于一體。查詢數(shù)據(jù)使用SELECT命令；輸入數(shù)據(jù)使用INSERT命令；更新數(shù)據(jù)使用UPDATE命令；刪除數(shù)據(jù)使用DELETE命令；創(chuàng)建表使用CREATE TABLE命令；修改表使用ALTER TABLE命令；刪除表使用DR

52、OP命令；事務(wù)提交使用COMMIT命令；事務(wù)回滾使用ROLLBACK命令；設(shè)置保存點(diǎn)使用SAVEPOINT命令；授權(quán)使用GRANT命令；收回權(quán)限使用REVOKE命令。 習(xí)題二一、選擇題1SQL查詢語(yǔ)句：SELECT name,salary FROM empWHERE salary BETWEEN 1000 and 2000對(duì)于查詢結(jié)果，說(shuō)法正確的是( )。A查詢返回工資大于1000而小于2000的員工信息B查詢返回工資大于或等于1000且小于2000的員工信息C查詢返回工資大于或等于1000且小于或等于2000的員工信息D查詢返回工資大于1000且小于或等于2000的員工信息 2要選擇某一列的

53、平均值，可使用函數(shù)( )。A. COUNT B. SUM C. MIN D. AVG3. 函數(shù)STDDEV可用來(lái)計(jì)算某一列所有數(shù)值的( )。A. 標(biāo)準(zhǔn)方差 B. 標(biāo)準(zhǔn)偏差 C. 平方根 D. 以上全不對(duì)4下面的查詢語(yǔ)句，( )有錯(cuò)誤。a. SELECT emp_ID,first_name,last_nameb. FROM hr.emp WHERE emp_ID121c. GROUP BY emp_IDA. a B. b C. c D. 沒(méi)有錯(cuò)誤 5在SQL*Plus工具中執(zhí)行下列語(yǔ)句：SELECT power(9,3) FROM DUAL;得到的查詢結(jié)果是( )。A. 729 B. 3 C.

54、27 D. 以上全不對(duì)6查詢語(yǔ)句：SELECT floor(13.57) FROM DUAL 對(duì)于返回結(jié)果，正確的是( )。A. 13.27 B. 13 C. 14 D. 13.67. 要使Oracle 在完成每一個(gè)SQL 命令或PL/SQL 塊時(shí)將未提交的改變立即提交(COMMIT)給數(shù)據(jù)庫(kù)，( )參數(shù)必須設(shè)置為ON。A. AUTO B. AUTOSTRACEC. STATISTICS D. ECHO 二、編程題1查詢?nèi)啃贞惖膶W(xué)生的信息。2統(tǒng)計(jì)學(xué)生數(shù)學(xué)成績(jī)?cè)?090分之間的人數(shù)。3計(jì)算全班同學(xué)的考試平均分。4列出所有冰箱的價(jià)格。5列出所有海爾牌洗衣機(jī)的銷售總值。6按品牌統(tǒng)計(jì)電視機(jī)的銷售量。

55、7求電視機(jī)的平均銷售價(jià)格。8求各科考試成績(jī)的總分和平均分。9按電視機(jī)的價(jià)格高低排序并輸出其基本信息。 上機(jī)實(shí)驗(yàn)二實(shí)驗(yàn)1 創(chuàng)建表目的和要求：1了解表的基本概念。2了解創(chuàng)建表的基本命令的用法。3了解基本的數(shù)據(jù)類型。4學(xué)會(huì)使用命令創(chuàng)建表。5學(xué)會(huì)設(shè)置簡(jiǎn)單的約束條件。 實(shí)驗(yàn)內(nèi)容：創(chuàng)建三個(gè)表，即Employees2(雇員自然信息)表、Departments(部門(mén)信息)表和Salary(員工薪水情況)表。各表的結(jié)構(gòu)如表2-13表2-15所示。 表2-13 Employees2表結(jié)構(gòu) 表2-14 Departments表結(jié)構(gòu) 表2-15 Salary表結(jié)構(gòu) 使用SQL語(yǔ)句分別創(chuàng)建表Employees2、表De

56、partments和表Salary。在SQL * Plus中輸入如下語(yǔ)句： SQLcreate table Employees2(EmployeeID char(6) constraint E_pk primary key, Name char(10) NOT NULL,Birthday Date NOT NULL,Sex number(1) NOT NULL,Address char(20) NOT NULL,Zip char(6) NULL,PhoneNumberchar(12) NULL,EmailAddress char(20) NULL, DepartmentID char(3) N

57、OT NULL); 按回車鍵，執(zhí)行上述程序段，即可創(chuàng)建表Employees2。按同樣的方法創(chuàng)建表Departments和表Salary，并查看結(jié)果。 實(shí)驗(yàn)2 向表中進(jìn)行數(shù)據(jù)插入、修改和刪除操作目的和要求：1學(xué)會(huì)使用SQL語(yǔ)句對(duì)數(shù)據(jù)表進(jìn)行數(shù)據(jù)的插入、修改和刪除操作。2了解進(jìn)行數(shù)據(jù)更新時(shí)要注意數(shù)據(jù)的完整性。3了解SQL語(yǔ)句對(duì)表數(shù)據(jù)操作的靈活控制功能。 實(shí)驗(yàn)內(nèi)容：1向創(chuàng)建的三個(gè)表(即Employees2表、Departments表和Salary表)中插入數(shù)據(jù)。(1) 向Employees2表中插入30條記錄。啟動(dòng)SQL * Plus界面，輸入以下SQL語(yǔ)句，按回車鍵執(zhí)行命令。SQLINSERT IN

58、TO Employees2 VALUES(001101, 王林,TO_DATE(1988-06-18, YYYYMMDD,1,梅華路2000號(hào)； (依次輸入30條記錄) (2) 向Departments表中插入10條記錄。SQLINSERT INTO Departments VALUES(001, 銷售部, NULL)；(依次輸入10條記錄)(3) 向Salary表中插入30條記錄。SQLINSERT INTO Salary VALUES(001101,3500.00, 809.00)；(依次輸入30條記錄) 2使用SQL命令修改表Salary中的數(shù)據(jù)。啟動(dòng)SQL * Plus界面，輸入以下SQL語(yǔ)句，按回車鍵執(zhí)行命令。SQLUPDATE Salary SET InCome=3800 WHERE EmployeesID=001101;上述命令執(zhí)行后，編號(hào)為001101的職工的收入將改為3800。打開(kāi)表，觀察數(shù)據(jù)的變化。然后仿照上例，做23個(gè)練習(xí)。練習(xí)：給所有收入在1000元以下