進(jìn)行了為期兩三個(gè)月的數(shù)據(jù)庫(kù)和軟件測(cè)試培訓(xùn)，打開(kāi)pl-sql進(jìn)行數(shù)據(jù)查詢分析估計(jì)是日常工作最經(jīng)常做的事情，很多時(shí)候我們應(yīng)用人員對(duì)SQL語(yǔ)句的應(yīng)用卻是不甚明了，本文嘗試結(jié)合日常工作經(jīng)驗(yàn)和網(wǎng)上的資料對(duì)工作中常用的簡(jiǎn)單語(yǔ)句進(jìn)行總結(jié)，希望本文能拋磚引玉，不正之處望多指教。

目錄

一、DDL－數(shù)據(jù)定義語(yǔ)言

作為一般黑盒測(cè)試人員，在一般測(cè)試工作中，數(shù)據(jù)庫(kù)環(huán)境已經(jīng)由數(shù)據(jù)庫(kù)管理員建立好，并不需要測(cè)試人員建庫(kù)，刪表。所以大家能看懂格式與意義就ok了，這部分只介紹簡(jiǎn)單的。

1、創(chuàng)建數(shù)據(jù)庫(kù) （幾乎用不著）

CREATE DATABASE [database-name]

2、刪除數(shù)據(jù)庫(kù) （慎重使用）

DROP DATABASE dbname1,dbname2…

4、創(chuàng)建表

create table tabname(<列名><數(shù)據(jù)類型> [not null] [primary key], <列名2><數(shù)據(jù)類型> [not null],..)

例如: CREATE TABLE S

(SNO CHAR(10) NOT NULL ,

SN VARCHAR(20),

AGE INT,

SEX CHAR(2) DEFAULT '男' ,

DEPT VARCHAR(20));

根據(jù)已有的表創(chuàng)建新表（常用）： 

A：create table NEW like OLD (使用舊表創(chuàng)建新表)

B：create table NEW as select col1,col2… from OLD where……

5、刪除表（慎重使用）

drop table TABNAME 

6、增加字段

Alter table TABNAME

ADD <列名><數(shù)據(jù)類型>[NULL|NOT NULL]

7、修改字段

 ALTER TABNAME

ALTER COLUMN <列名><數(shù)據(jù)類型>[NULL|NOT NULL]

8、刪除字段

ALTER TABNAME

DROP COLUMN <列名><數(shù)據(jù)類型>[NULL|NOT NULL]

9、創(chuàng)建索引

create [unique] index idxname on TABNAME (col….) 

10、刪除索引

drop index IDXNAME

注：索引是不可更改的，想更改必須刪除重新建。

11、創(chuàng)建視圖

create view VIEWNAME as SELECT……

12、刪除視圖

drop view VIEWNAME

 二、DML－數(shù)據(jù)操縱語(yǔ)言

1、數(shù)據(jù)查詢

數(shù)據(jù)查詢是數(shù)據(jù)庫(kù)中最常見(jiàn)的操作。在本文檔里將作重點(diǎn)介紹。SQL語(yǔ)言提供SELECT語(yǔ)句，通過(guò)查詢操作可得到所需的信息。

SELECT語(yǔ)句：估計(jì)沒(méi)有應(yīng)用人員不會(huì)使用，但是真正了解select語(yǔ)句的整體結(jié)構(gòu)還是需要頗費(fèi)功夫，這里只作介紹，詳細(xì)可通過(guò)下面例子學(xué)習(xí)。順便還介紹一個(gè)學(xué)習(xí)的好辦法，sql的幫助里面有很多例子而且都有對(duì)應(yīng)的練習(xí)數(shù)據(jù)庫(kù)，可以具體分析。

SELECT語(yǔ)句的一般格式為：

SELECT select_list

[ INTO new_table ]

FROM table_source

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

SELECT語(yǔ)句的執(zhí)行過(guò)程是:

根據(jù)WHERE子句的檢索條件，從FROM子句指定的基本表或視圖中選取滿足條件的元組，再按照SELECT子句中指定的列，投影得到結(jié)果表。

如果有GROUP子句，則將查詢結(jié)果按照<列名1>相同的值進(jìn)行分組。

如果GROUP子句后有HAVING短語(yǔ)，則只輸出滿足HAVING條件的元組。

如果有ORDER子句，查詢結(jié)果還要按照<列名2>的值進(jìn)行排序。

1.1、查詢指定列

SELECT <列名> FROM <表名或視圖名>

1.2、查詢?nèi)苛?/span>

SELECT * FROM <表名或視圖名>

或SELECT <全部列名> FROM <表名或視圖名>

1.3、取消相同取值的行

在查詢結(jié)果中有可能出現(xiàn)取值完全相同的行了。

SELECT DISTINCT <列名> FROM <表名或視圖名>

1.4、比較大小

比較運(yùn)算符有       =，>，>=，<=，<，<>，!>，!<

NOT 上述比較運(yùn)算符

SELECT <列名> FROM <表名或視圖名> WHERE <列名> [比較運(yùn)算符] <比較的值>

1.5、多重條件查詢

當(dāng)WHERE子句需要指定一個(gè)以上的查詢條件時(shí)，則需要使用邏輯運(yùn)算符AND、OR和NOT將其連結(jié)成復(fù)合的邏輯表達(dá)式。

其優(yōu)先級(jí)由高到低為：NOT、AND、OR，用戶可以使用括號(hào)改變優(yōu)先級(jí)。

SELECT <列名> FROM <表名或視圖名> WHERE <條件1> AND <條件1> OR <條件1>…

1.6、確認(rèn)范圍查詢

用于確定范圍運(yùn)算符有：BETWEEN…AND…和NOT BETWEEN…AND…

SELECT <列名> FROM <表名或視圖名> WHERE <列名> [NOT] BETWEEN  值1 AND 值2

這與下等價(jià)

SELECT <列名> FROM <表名或視圖名> WHERE <列名>>=值1 AND <列名><=值2

SELECT <列名> FROM <表名或視圖名> WHERE <列名><值1 OR <列名>>值2

1.7、確認(rèn)集合

確定集合符號(hào)：IN，NOT IN

SELECT <列名> FROM <表名或視圖名> WHERE <列名>[NOT] IN  (常量1，常量2，…，常量n)

1.8字符匹配查詢

字符匹配查詢符號(hào)：LIKE，NOT LIKE

ORACLE支持如下四種通配符：

_(下劃線)：匹配任意一個(gè)字符；

%(百分號(hào))： 匹配O個(gè)或多個(gè)字符；

SELECT <列名> FROM <表名或視圖名> WHERE <列名> [NOT] LIKE <匹配字符串>

注意：oracle字符匹配比sqlserve嚴(yán)格得多，如char類型后帶的自動(dòng)填補(bǔ)的空格就必須作為單個(gè)字符考慮。

1.9空值查詢

空值不同于零和空格，它不占任何存儲(chǔ)空間。

判斷某個(gè)值是否為NULL值，不能使用普通的比較運(yùn)算符(一、!一等)，而只能使用專門的判斷NULL值的子句來(lái)完成。

SELECT <列名> FROM <表名或視圖名> WHERE <列名> IS [NOT] NULL

1.10常用庫(kù)函數(shù)及統(tǒng)計(jì)匯總查詢

常用的庫(kù)函數(shù)

AVG： 按列計(jì)算平均值

SUM：按列計(jì)算值的總和

MAX：求一列中的最大值

MIN：求一列中的最小值

COUNT：按列值計(jì)算個(gè)數(shù)

總數(shù)：select count(field1) as totalcount from table1

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1

注1：SQL規(guī)定，當(dāng)使用計(jì)算函數(shù)時(shí)，列名不能與計(jì)算函數(shù)一起使用(除非他們出現(xiàn)在其他集合中)。

例如查詢年齡最大的學(xué)生的姓名和年齡，如下寫(xiě)法是錯(cuò)誤的：

SELECT 姓名，MAX(年齡)FROM Student

注2：計(jì)算函數(shù)不能出現(xiàn)在WHERE子句中。    ．

 例如查詢年齡最大的學(xué)生的姓名如下寫(xiě)法是錯(cuò)誤的：

SELECT 姓名 FROM Student WHERE 年齡=MAX(年齡)

正確的命令應(yīng)為:

  SELECT 姓名，年齡 FROM Student

 Where  年齡=(select max(年齡) from student)

1.11分組查詢

SELECT <列名> FROM <表名或視圖名>

GROUP BY<分組依據(jù)列>[，…n]

[HAVING<組提取條件>]

注1：分組依據(jù)列不能是text、ntext、image和bit類型的列。

注2：有分組時(shí)，查詢列表中的列只能取自分組依據(jù)列(計(jì)算函數(shù)中的列除外)

1.12對(duì)查詢結(jié)果進(jìn)行排序

SELECT <列名> FROM <表名或視圖名>

ORDER BY<列名>[ASC l DESC][，…n]

1.13數(shù)據(jù)表連接查詢

A、 INNER JOIN：

這是最普通的聯(lián)接類型。只要在這兩個(gè)表的公共字段之中有相符值，內(nèi)部聯(lián)接將組合兩個(gè)表中的記錄。

SELECT fields

FROM table1 INNER JOIN table2

ON table1.field1 compopr table2.field1 AND

ON table1.field2 compopr table2.field2) OR

ON table1.field3 compopr table2.field3)];

B、left outer join： 

左外連接（左連接）：結(jié)果集包括連接表的匹配行，也包括左連接表的所有行。 

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

C：right outer join: 

右外連接(右連接)：結(jié)果集包括連接表的匹配連接行，也包括右連接表的所有行。 

D：full outer join： 

全外連接：不僅包括符號(hào)連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。

1.14使用TOP限制結(jié)果集

使用TOP謂詞時(shí)注意最好與ORDER BY子句一起使用，因?yàn)檫@樣的前幾名才有意義。但當(dāng)使用WITH TIES時(shí)，要求必須使用ORDER BY子句。

TOP謂詞寫(xiě)在SELECT單詞的后邊，查詢列表的前邊。

使用TOP謂詞的格式為：

    TOP n[percent]with ties]

    其中：n為非負(fù)整數(shù)。

    TOP n：表示取查詢結(jié)果的前n行；

    TOP n percent：表示取查詢結(jié)果的前n％ 行；  

    With ties：表示包括并列的結(jié)果。

1.15將查詢結(jié)果存入表中

INTO子句的語(yǔ)法格式為：

    INTO 新表名

INTO子句跟在SELECT子句之后、FROM子句之前。SELECT <列名> INTO 新表名 FROM。

新表名是要存放查詢結(jié)果的表名，SELECT INTO語(yǔ)句包含兩個(gè)操作：首先按查詢列表創(chuàng)建新表，然后執(zhí)行查詢語(yǔ)句，并將結(jié)果保存到新表中。

    用INTO子句創(chuàng)建的新表可以是永久表，也可以是臨時(shí)表。臨時(shí)表又分為兩種：局部臨時(shí)表和全局臨時(shí)表。局部臨時(shí)表要在表名前加#，它只能用在當(dāng)前的連接中；全局臨時(shí)表要在表名前加##，它的生存期為創(chuàng)建全局臨時(shí)表的連接的生存期

1.16合并查詢

使用UNION的格式為：

    SELECT 語(yǔ)句1

    UNION

    SELECT 語(yǔ)句2

    UNION [ALL]

    SELECT 語(yǔ)句n

使用UNION的兩個(gè)基本規(guī)則是：

A、所有查詢語(yǔ)句中的列個(gè)數(shù)和列的順序必須相同。

B、所有查語(yǔ)句中的對(duì)應(yīng)列的數(shù)據(jù)類型必須兼容。

1.17子查詢

A、使用子查詢進(jìn)行比較測(cè)試

使用子查詢進(jìn)行比較測(cè)試時(shí)，通過(guò)比較運(yùn)算符(=、!=、<、>、<=、>=)，將一個(gè)表達(dá)式的值與子查詢返回的單值進(jìn)行比較。如果比較運(yùn)算的結(jié)果為True，則比較測(cè)試也返回True。

    使用子查詢進(jìn)行的比較測(cè)試要求子查詢語(yǔ)句必須是返回單值的查詢語(yǔ)句。

 例1:查詢修了"c02"課程的且成績(jī)高于此課程的平均成績(jī)的學(xué)生的學(xué)號(hào)和成績(jī)。

    SELECT 學(xué)號(hào),成績(jī) FROM SC

    WHERE 課程號(hào)=‘c02’

    and 成績(jī)>( SELECT AVG(成績(jī)) from SC

    WHERE 課程號(hào)=‘c02’)

B、使用子查詢基于集合的測(cè)試

使用子查詢進(jìn)行基于集合的測(cè)試時(shí)，通過(guò)運(yùn)算符IN和NOT IN，將一個(gè)表達(dá)式的值與子查詢返回的結(jié)果集進(jìn)行比較。這同前邊在WHERE子句中使用的IN作用完全相同。使用IN運(yùn)算符時(shí)，如果該表達(dá)式的值與集合中的某個(gè)值相等，則此測(cè)試為True；如果該表達(dá)式與集合中的所有值均不相等，則返回False。

    注意：使用子查詢進(jìn)行基于集合的測(cè)試時(shí)，由該子查詢返回的結(jié)果集是僅包含單個(gè)列的一個(gè)列表，該列必須與測(cè)試表達(dá)式的數(shù)據(jù)類型相同。當(dāng)子查詢返回結(jié)果之后，外層查詢將使用這些結(jié)果。  

C、 使用子查詢進(jìn)行存在性測(cè)試

使用子查詢進(jìn)行存在性測(cè)試時(shí)，往往使用EXISTS謂詞。帶EXISTS謂詞的子查詢不返回查詢的數(shù)據(jù)，只產(chǎn)生邏輯真值和邏輯假值。

例6:查詢選修了‘‘c01”號(hào)課程的學(xué)生姓名。

    SELECT 姓名 FROM Student  

    WHERE EXISTS

    (SELECT * FROM SC

    WHERE 學(xué)號(hào)=Student．學(xué)號(hào)

AND 課程號(hào)=‘c01’)

注1:帶EXISTS謂詞的查詢是先執(zhí)行外層查詢，然后再執(zhí)行內(nèi)層查詢。由外層查詢 的值決定內(nèi)層查詢的結(jié)果；內(nèi)層查詢的執(zhí)行次數(shù)由外層查詢的結(jié)果數(shù)決定。

上述查詢語(yǔ)句的處理過(guò)程為：

  (1)找外層表Student表的第一行，根據(jù)其學(xué)號(hào)的值處理內(nèi)層查詢；

  (2)用外層的值與內(nèi)層的結(jié)果比較，由此決定外層條件的真、假值；如果為真，則此記錄為符合條件的結(jié)果；

  (3)順序處理外層表Student表中的第2、3、…行。

注2：由于EXISTS的子查詢只能返回真或假值，因此在這里給出列名無(wú)意義。所以在有EXISTS的子查詢中，其目標(biāo)列表達(dá)式通常都用“*”。

2．?dāng)?shù)據(jù)更新

SQL語(yǔ)言的數(shù)據(jù)更新語(yǔ)句DML主要包括插入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)三種語(yǔ)句。

2.1插入一行新記錄

INSERT INTO <表名>[(<列名1>[,<列名2>…])] VALUES(<值>)

2.2插入一行的部分?jǐn)?shù)據(jù)值

只寫(xiě)上部分列名，沒(méi)有寫(xiě)上的列名值自動(dòng)為空，如果列是NOT NULL則必需賦值。

2.3插入多行記錄

INSERT INTO <表名> [(<列名1>[,<列名2>…])]  子查詢

2.4修改數(shù)據(jù)

UPDATE <表名>

SET <列名>=<表達(dá)式> [,<列名>=<表達(dá)式>]…

[WHERE <條件>]

2.5刪除記錄 

DELETE

FROM<表名>

[WHERE <條件>]