检索
最简单的查询语句
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函数去掉值右边所有空格，LTRIM函数去掉值左边左右空格。
通配符
SELECT * FROM Persons
WHERE City LIKE 'Ne%'
常用通配符
| 通配符 | 描述 |
|---|---|
| % | 替代一个或多个字符 |
| _ | 仅替代一个字符 |
| [charlist] | 字符列中的任何单一字符 |
| [^charlist]或[!charlist] | 不在字符列中的任何单一字符 |
创建计算字段
select prod_id, quantity, quantity*item_price as expanded price
from OrderItems
数据处理函数
- 文本处理函数
| 函数 | 说明 |
|---|---|
| LEFT | 返回字符串左边的字符 |
| LENGTH | 返回字符串长度 |
- 数值处理
| 函数 | 说明 |
|---|---|
| ABS | 绝对值 |
| COS | 余弦 |
| EXP | 指数值 |
| PI | 圆周率 |
- 聚集函数
| 函数 | 说明 |
|---|---|
| AVG | 平均值 |
| COUNT | 行数 |
| 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')
联结表
笛卡尔积A x B
select vend_name, prod_name, prod_price
from Vendors, Products
where Vendors.vend_id = Products.vend_id
内联积(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执行结果插入到表中(导出数据)
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;
将数据复制到一个新表,导入数据
SELECT *
INTO CustCopy
FROM Customers;
视图
作为视图，它不包含任何列或数据，包含的是一个查询。
存储过程
相当于编程语言中的函数，用于批量执行语句。
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;
参数列表： ListCount OUT INTEGER
函数体： BEGIN END 所包裹的语句
使用存储过程：
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;
事务管理
事务处理是用来保证数据库完整性的一种机制。
其中：
事务（transaction）指一组SQL语句；
回退（rollback）指撤销指定SQL语句的过程；
提交（commit）指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。
sql server为例：
提交事务：
BEGIN TRANSACTION
...
COMMIT TRANSACTION
回退
DELETE FROM Orders;
ROLLBACK;
定义保留点
SAVE TRANSACTION delete1;
其他高级特性
约束
约束(constraint)是管理如何插入或处理数据库数据的规则, 目的是维持引用完整性(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)
- 唯一约束
与主键约束类似，用来保证一列（或一组列）中的数据是唯一的。唯一约束不能用来定义外键
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
索引
索引用来排序数据以加快搜索和排序操作的速度。
CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);
触发器
触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。
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;
一般来说，约束的处理比触发器快，因此在可能的时候，应该尽量使用约束。
