檢索
最簡單的查詢語句
select * from [TableName]
排序
降序
select * from [TableName] order by [Field] desc
升序
select * from [TableName] order by [Field]
過濾
高級
利用多列組合進行過濾（拼接字段）：
SELECT RTRIM(vend_name) + ' (' + LTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;
其中RTRIM函數(shù)去掉值右邊所有空格，LTRIM函數(shù)去掉值左邊左右空格。
通配符
SELECT * FROM Persons
WHERE City LIKE 'Ne%'
常用通配符
| 通配符 | 描述 |
|---|---|
| % | 替代一個或多個字符 |
| _ | 僅替代一個字符 |
| [charlist] | 字符列中的任何單一字符 |
| [^charlist]或[!charlist] | 不在字符列中的任何單一字符 |
創(chuàng)建計算字段
select prod_id, quantity, quantity*item_price as expanded price
from OrderItems
數(shù)據(jù)處理函數(shù)
- 文本處理函數(shù)
| 函數(shù) | 說明 |
|---|---|
| LEFT | 返回字符串左邊的字符 |
| LENGTH | 返回字符串長度 |
- 數(shù)值處理
| 函數(shù) | 說明 |
|---|---|
| ABS | 絕對值 |
| COS | 余弦 |
| EXP | 指數(shù)值 |
| PI | 圓周率 |
- 聚集函數(shù)
| 函數(shù) | 說明 |
|---|---|
| AVG | 平均值 |
| COUNT | 行數(shù) |
| MAX | 最大值 |
| MIN | 最小值 |
| SUM | 求和 |
分組
select vend_id, COUNT(*) as num_prods
from Products
group by vend_id
過濾分組使用HAVING操作符
select cust_id, COUNT(*) as orders
from Orders
group by cust_id
having count(*) >= 2;
子查詢
子查詢常用于WHERE子句的IN操作符中，以及用來填充計算列。
select cust_name, cust_contact
from Customers
where cust_id IN (select cust_id from Order where prod_id = 'RGAN01')
聯(lián)結表
笛卡爾積A x B
select vend_name, prod_name, prod_price
from Vendors, Products
where Vendors.vend_id = Products.vend_id
內聯(lián)積(inner join)
select vend_name, prod_name, prod_price
from Vendors inner join Products
on Vendors.vend_id = Products.vend_id;
表別名
SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
組合查詢
合并
select * from A union select * from B
表操作
新增表
CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL,
quantity INTEGER NOT NULL DEFAULT 1,
);
更新表
新增字段
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
刪除字段
ALTER TABLE Vendors
DROP COLUMN vend_phone;
刪除表
DROP TABLE CustCopy;
更新
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
刪除
DELETE FROM Customers
WHERE cust_id = '1000000006';
如果需要刪除所有行，可使用TRUNCATE TABLE
TRUNCATE TABLE Customers;
復制
INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA');
將select執(zhí)行結果插入到表中(導出數(shù)據(jù))
INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM CustNew;
將數(shù)據(jù)復制到一個新表,導入數(shù)據(jù)
SELECT *
INTO CustCopy
FROM Customers;
視圖
作為視圖，它不包含任何列或數(shù)據(jù)，包含的是一個查詢。
存儲過程
相當于編程語言中的函數(shù)，用于批量執(zhí)行語句。
CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
END;
參數(shù)列表： ListCount OUT INTEGER
函數(shù)體： BEGIN END 所包裹的語句
使用存儲過程：
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;
事務管理
事務處理是用來保證數(shù)據(jù)庫完整性的一種機制。
其中：
事務（transaction）指一組SQL語句；
回退（rollback）指撤銷指定SQL語句的過程；
提交（commit）指將未存儲的SQL語句結果寫入數(shù)據(jù)庫表；
保留點（savepoint）指事務處理中設置的臨時占位符（placeholder），可以對它發(fā)布回退（與回退整個事務處理不同）。
sql server為例：
提交事務：
BEGIN TRANSACTION
...
COMMIT TRANSACTION
回退
DELETE FROM Orders;
ROLLBACK;
定義保留點
SAVE TRANSACTION delete1;
其他高級特性
約束
約束(constraint)是管理如何插入或處理數(shù)據(jù)庫數(shù)據(jù)的規(guī)則, 目的是維持引用完整性(referential integrity)。
- 主鍵約束Primary Key
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);
- 外鍵約束
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)
- 唯一約束
與主鍵約束類似，用來保證一列（或一組列）中的數(shù)據(jù)是唯一的。唯一約束不能用來定義外鍵
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
- 檢查約束
CREATE TABLE OrderItems
(
order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
item_price MONEY NOT NULL
);
ADD CONSTRAINT CHECK (gender LIKE '[MF]')
- 刪除約束
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
索引
索引用來排序數(shù)據(jù)以加快搜索和排序操作的速度。
CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);
觸發(fā)器
觸發(fā)器是特殊的存儲過程，它在特定的數(shù)據(jù)庫活動發(fā)生時自動執(zhí)行。
CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;
一般來說，約束的處理比觸發(fā)器快，因此在可能的時候，應該盡量使用約束。
