檢索
最簡(jiǎn)單的查詢語(yǔ)句
select * from [TableName]
排序
降序
select * from [TableName] order by [Field] desc
升序
select * from [TableName] order by [Field]
過濾
高級(jí)
利用多列組合進(jìn)行過濾（拼接字段）：
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%'
常用通配符
| 通配符 | 描述 |
|---|---|
| % | 替代一個(gè)或多個(gè)字符 |
| _ | 僅替代一個(gè)字符 |
| [charlist] | 字符列中的任何單一字符 |
| [^charlist]或[!charlist] | 不在字符列中的任何單一字符 |
創(chuàng)建計(jì)算字段
select prod_id, quantity, quantity*item_price as expanded price
from OrderItems
數(shù)據(jù)處理函數(shù)
- 文本處理函數(shù)
| 函數(shù) | 說明 |
|---|---|
| LEFT | 返回字符串左邊的字符 |
| LENGTH | 返回字符串長(zhǎng)度 |
- 數(shù)值處理
| 函數(shù) | 說明 |
|---|---|
| ABS | 絕對(duì)值 |
| 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操作符中，以及用來填充計(jì)算列。
select cust_name, cust_contact
from Customers
where cust_id IN (select cust_id from Order where prod_id = 'RGAN01')
聯(lián)結(jié)表
笛卡爾積A x B
select vend_name, prod_name, prod_price
from Vendors, Products
where Vendors.vend_id = Products.vend_id
內(nèi)聯(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';
如果需要?jiǎng)h除所有行，可使用TRUNCATE TABLE
TRUNCATE TABLE Customers;
復(fù)制
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í)行結(jié)果插入到表中(導(dǎo)出數(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ù)復(fù)制到一個(gè)新表,導(dǎo)入數(shù)據(jù)
SELECT *
INTO CustCopy
FROM Customers;
視圖
作為視圖，它不包含任何列或數(shù)據(jù)，包含的是一個(gè)查詢。
存儲(chǔ)過程
相當(dāng)于編程語(yǔ)言中的函數(shù)，用于批量執(zhí)行語(yǔ)句。
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 所包裹的語(yǔ)句
使用存儲(chǔ)過程：
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;
事務(wù)管理
事務(wù)處理是用來保證數(shù)據(jù)庫(kù)完整性的一種機(jī)制。
其中：
事務(wù)（transaction）指一組SQL語(yǔ)句；
回退（rollback）指撤銷指定SQL語(yǔ)句的過程；
提交（commit）指將未存儲(chǔ)的SQL語(yǔ)句結(jié)果寫入數(shù)據(jù)庫(kù)表；
保留點(diǎn)（savepoint）指事務(wù)處理中設(shè)置的臨時(shí)占位符（placeholder），可以對(duì)它發(fā)布回退（與回退整個(gè)事務(wù)處理不同）。
sql server為例：
提交事務(wù)：
BEGIN TRANSACTION
...
COMMIT TRANSACTION
回退
DELETE FROM Orders;
ROLLBACK;
定義保留點(diǎn)
SAVE TRANSACTION delete1;
其他高級(jí)特性
約束
約束(constraint)是管理如何插入或處理數(shù)據(jù)庫(kù)數(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ā)器是特殊的存儲(chǔ)過程，它在特定的數(shù)據(jù)庫(kù)活動(dòng)發(fā)生時(shí)自動(dòng)執(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ā)器快，因此在可能的時(shí)候，應(yīng)該盡量使用約束。
