進(jìn)行了為期兩三個(gè)月的數據庫和軟件測試培訓，打開(kāi)pl-sql進(jìn)行數據查詢(xún)分析估計是日常工作最經(jīng)常做的事情，很多時(shí)候我們應用人員對SQL語(yǔ)句的應用卻是不甚明了，本文嘗試結合日常工作經(jīng)驗和網(wǎng)上的資料對工作中常用的簡(jiǎn)單語(yǔ)句進(jìn)行總結，希望本文能拋磚引玉，不正之處望多指教。
目錄
一、DDL－數據定義語(yǔ)言
作為一般黑盒測試人員，在一般測試工作中，數據庫環(huán)境已經(jīng)由數據庫管理員建立好，并不需要測試人員建庫，刪表。所以大家能看懂格式與意義就ok了，這部分只介紹簡(jiǎn)單的。
1、創(chuàng )建數據庫 （幾乎用不著(zhù)）
CREATE DATABASE [database-name]
2、刪除數據庫 （慎重使用）
DROP DATABASE dbname1,dbname2…
4、創(chuàng )建表
create table tabname(<列名><數據類(lèi)型> [not null] [primary key], <列名2><數據類(lèi)型> [not null],..)
例如: CREATE TABLE S
(SNO CHAR(10) NOT NULL ,
SN VARCHAR(20),
AGE INT,
SEX CHAR(2) DEFAULT '男' ,
DEPT VARCHAR(20));
根據已有的表創(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 <列名><數據類(lèi)型>[NULL|NOT NULL]
7、修改字段
ALTER TABNAME
ALTER COLUMN <列名><數據類(lèi)型>[NULL|NOT NULL]
8、刪除字段
ALTER TABNAME
DROP COLUMN <列名><數據類(lèi)型>[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－數據操縱語(yǔ)言
1、數據查詢(xún)
數據查詢(xún)是數據庫中最常見(jiàn)的操作。在本文檔里將作重點(diǎn)介紹。SQL語(yǔ)言提供SELECT語(yǔ)句，通過(guò)查詢(xún)操作可得到所需的信息。
SELECT語(yǔ)句：估計沒(méi)有應用人員不會(huì )使用，但是真正了解select語(yǔ)句的整體結構還是需要頗費功夫，這里只作介紹，詳細可通過(guò)下面例子學(xué)習。順便還介紹一個(gè)學(xué)習的好辦法，sql的幫助里面有很多例子而且都有對應的練習數據庫，可以具體分析。
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ǔ)句的執行過(guò)程是:
根據WHERE子句的檢索條件，從FROM子句指定的基本表或視圖中選取滿(mǎn)足條件的元組，再按照SELECT子句中指定的列，投影得到結果表。
如果有GROUP子句，則將查詢(xún)結果按照<列名1>相同的值進(jìn)行分組。
如果GROUP子句后有HAVING短語(yǔ)，則只輸出滿(mǎn)足HAVING條件的元組。
如果有ORDER子句，查詢(xún)結果還要按照<列名2>的值進(jìn)行排序。
1.1、查詢(xún)指定列
SELECT <列名> FROM <表名或視圖名>
1.2、查詢(xún)全部列
SELECT * FROM <表名或視圖名>
或SELECT <全部列名> FROM <表名或視圖名>
1.3、取消相同取值的行
在查詢(xún)結果中有可能出現取值完全相同的行了。
SELECT DISTINCT <列名> FROM <表名或視圖名>
1.4、比較大小
比較運算符有 =，>，>=，<=，<，<>，!>，!<
NOT 上述比較運算符
SELECT <列名> FROM <表名或視圖名> WHERE <列名> [比較運算符] <比較的值>
1.5、多重條件查詢(xún)
當WHERE子句需要指定一個(gè)以上的查詢(xún)條件時(shí)，則需要使用邏輯運算符AND、OR和NOT將其連結成復合的邏輯表達式。
其優(yōu)先級由高到低為：NOT、AND、OR，用戶(hù)可以使用括號改變優(yōu)先級。
SELECT <列名> FROM <表名或視圖名> WHERE <條件1> AND <條件1> OR <條件1>…
1.6、確認范圍查詢(xú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、確認集合
確定集合符號：IN，NOT IN
SELECT <列名> FROM <表名或視圖名> WHERE <列名>[NOT] IN (常量1，常量2，…，常量n)
1.8字符匹配查詢(xún)
字符匹配查詢(xún)符號：LIKE，NOT LIKE
ORACLE支持如下四種通配符：
_(下劃線(xiàn))：匹配任意一個(gè)字符；
%(百分號)： 匹配O個(gè)或多個(gè)字符；
SELECT <列名> FROM <表名或視圖名> WHERE <列名> [NOT] LIKE <匹配字符串>
注意：oracle字符匹配比sqlserve嚴格得多，如char類(lèi)型后帶的自動(dòng)填補的空格就必須作為單個(gè)字符考慮。
1.9空值查詢(xún)
空值不同于零和空格，它不占任何存儲空間。
判斷某個(gè)值是否為NULL值，不能使用普通的比較運算符(一、!一等)，而只能使用專(zhuān)門(mén)的判斷NULL值的子句來(lái)完成。
SELECT <列名> FROM <表名或視圖名> WHERE <列名> IS [NOT] NULL
1.10常用庫函數及統計匯總查詢(xún)
常用的庫函數
AVG： 按列計算平均值
SUM：按列計算值的總和
MAX：求一列中的最大值
MIN：求一列中的最小值
COUNT：按列值計算個(gè)數
總數：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
最?。?/span>select min(field1) as minvalue from table1
注1：SQL規定，當使用計算函數時(shí)，列名不能與計算函數一起使用(除非他們出現在其他集合中)。
例如查詢(xún)年齡最大的學(xué)生的姓名和年齡，如下寫(xiě)法是錯誤的：
SELECT 姓名，MAX(年齡)FROM Student
注2：計算函數不能出現在WHERE子句中。 ．
例如查詢(xún)年齡最大的學(xué)生的姓名如下寫(xiě)法是錯誤的：
SELECT 姓名 FROM Student WHERE 年齡=MAX(年齡)
正確的命令應為:
SELECT 姓名，年齡 FROM Student
Where 年齡=(select max(年齡) from student)
1.11分組查詢(xún)
SELECT <列名> FROM <表名或視圖名>
GROUP BY<分組依據列>[，…n]
[HAVING<組提取條件>]
注1：分組依據列不能是text、ntext、image和bit類(lèi)型的列。
注2：有分組時(shí)，查詢(xún)列表中的列只能取自分組依據列(計算函數中的列除外)
1.12對查詢(xún)結果進(jìn)行排序
SELECT <列名> FROM <表名或視圖名>
ORDER BY<列名>[ASC l DESC][，…n]
1.13數據表連接查詢(xún)
A、 INNER JOIN：
這是最普通的聯(lián)接類(lèi)型。只要在這兩個(gè)表的公共字段之中有相符值，內部聯(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：
左外連接（左連接）：結果集包括連接表的匹配行，也包括左連接表的所有行。
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:
右外連接(右連接)：結果集包括連接表的匹配連接行，也包括右連接表的所有行。
D：full outer join：
全外連接：不僅包括符號連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。
1.14使用TOP限制結果集
使用TOP謂詞時(shí)注意最好與ORDER BY子句一起使用，因為這樣的前幾名才有意義。但當使用WITH TIES時(shí)，要求必須使用ORDER BY子句。
TOP謂詞寫(xiě)在SELECT單詞的后邊，查詢(xún)列表的前邊。
使用TOP謂詞的格式為：
TOP n[percent]with ties]
其中：n為非負整數。
TOP n：表示取查詢(xún)結果的前n行；
TOP n percent：表示取查詢(xún)結果的前n％ 行；
With ties：表示包括并列的結果。
1.15將查詢(xún)結果存入表中
INTO子句的語(yǔ)法格式為：
INTO 新表名
INTO子句跟在SELECT子句之后、FROM子句之前。SELECT <列名> INTO 新表名 FROM。
新表名是要存放查詢(xún)結果的表名，SELECT INTO語(yǔ)句包含兩個(gè)操作：首先按查詢(xún)列表創(chuàng )建新表，然后執行查詢(xún)語(yǔ)句，并將結果保存到新表中。
用INTO子句創(chuàng )建的新表可以是永久表，也可以是臨時(shí)表。臨時(shí)表又分為兩種：局部臨時(shí)表和全局臨時(shí)表。局部臨時(shí)表要在表名前加#，它只能用在當前的連接中；全局臨時(shí)表要在表名前加##，它的生存期為創(chuàng )建全局臨時(shí)表的連接的生存期
1.16合并查詢(xún)
使用UNION的格式為：
SELECT 語(yǔ)句1
SELECT 語(yǔ)句2
SELECT 語(yǔ)句n
使用UNION的兩個(gè)基本規則是：
A、所有查詢(xún)語(yǔ)句中的列個(gè)數和列的順序必須相同。
B、所有查語(yǔ)句中的對應列的數據類(lèi)型必須兼容。
1.17子查詢(xún)
A、使用子查詢(xún)進(jìn)行比較測試
使用子查詢(xún)進(jìn)行比較測試時(shí)，通過(guò)比較運算符(=、!=、<、>、<=、>=)，將一個(gè)表達式的值與子查詢(xún)返回的單值進(jìn)行比較。如果比較運算的結果為True，則比較測試也返回True。
使用子查詢(xún)進(jìn)行的比較測試要求子查詢(xún)語(yǔ)句必須是返回單值的查詢(xún)語(yǔ)句。
例1:查詢(xún)修了"c02"課程的且成績(jì)高于此課程的平均成績(jì)的學(xué)生的學(xué)號和成績(jì)。
SELECT 學(xué)號,成績(jì) FROM SC
WHERE 課程號=‘c
and 成績(jì)>( SELECT AVG(成績(jì)) from SC
WHERE 課程號=‘c
B、使用子查詢(xún)基于集合的測試
使用子查詢(xún)進(jìn)行基于集合的測試時(shí)，通過(guò)運算符IN和NOT IN，將一個(gè)表達式的值與子查詢(xún)返回的結果集進(jìn)行比較。這同前邊在WHERE子句中使用的IN作用完全相同。使用IN運算符時(shí)，如果該表達式的值與集合中的某個(gè)值相等，則此測試為True；如果該表達式與集合中的所有值均不相等，則返回False。
注意：使用子查詢(xún)進(jìn)行基于集合的測試時(shí)，由該子查詢(xún)返回的結果集是僅包含單個(gè)列的一個(gè)列表，該列必須與測試表達式的數據類(lèi)型相同。當子查詢(xún)返回結果之后，外層查詢(xún)將使用這些結果。
C、 使用子查詢(xún)進(jìn)行存在性測試
使用子查詢(xún)進(jìn)行存在性測試時(shí)，往往使用EXISTS謂詞。帶EXISTS謂詞的子查詢(xún)不返回查詢(xún)的數據，只產(chǎn)生邏輯真值和邏輯假值。
例6:查詢(xún)選修了‘‘c
SELECT 姓名 FROM Student
WHERE EXISTS
(SELECT * FROM SC
WHERE 學(xué)號=Student．學(xué)號
AND 課程號=‘c
注1:帶EXISTS謂詞的查詢(xún)是先執行外層查詢(xún)，然后再執行內層查詢(xún)。由外層查詢(xún) 的值決定內層查詢(xún)的結果；內層查詢(xún)的執行次數由外層查詢(xún)的結果數決定。
上述查詢(xún)語(yǔ)句的處理過(guò)程為：
(1)找外層表Student表的第一行，根據其學(xué)號的值處理內層查詢(xún)；
(2)用外層的值與內層的結果比較，由此決定外層條件的真、假值；如果為真，則此記錄為符合條件的結果；
(3)順序處理外層表Student表中的第2、3、…行。
注2：由于EXISTS的子查詢(xún)只能返回真或假值，因此在這里給出列名無(wú)意義。所以在有EXISTS的子查詢(xún)中，其目標列表達式通常都用“*”。
2．數據更新
SQL語(yǔ)言的數據更新語(yǔ)句DML主要包括插入數據、修改數據和刪除數據三種語(yǔ)句。
2.1插入一行新記錄
INSERT INTO <表名>[(<列名1>[,<列名2>…])] VALUES(<值>)
2.2插入一行的部分數據值
只寫(xiě)上部分列名，沒(méi)有寫(xiě)上的列名值自動(dòng)為空，如果列是NOT NULL則必需賦值。
2.3插入多行記錄
INSERT INTO <表名> [(<列名1>[,<列名2>…])] 子查詢(xún)
2.4修改數據
UPDATE <表名>
SET <列名>=<表達式> [,<列名>=<表達式>]…
[WHERE <條件>]
2.5刪除記錄
DELETE
FROM<表名>
[WHERE <條件>]
聯(lián)系客服
