SQL知识点.txt

一、查询
SELECT prod_name, prod_id
FROM Products;
二、排序
SELECT prod_id, prod_name
FROM Products
ORDER BY prod_id, prod_name DESC;
三、过滤
SELECT prod_id, prod_name
FROM Products
WHERE prod_price BETWEEN 5 AND 10
ORDER BY prod_id DESC, prod_name;
四、高级过滤
1 SELECT prod_id, prod_name
FROM Products
WHERE vend_id=‘DLL01’ AND prod_price <= 4;

2 SELECT prod_id, prod_name
FROM Products
WHERE vend_id=‘DLL01’ OR prod_price <= 4;

3 AND和OR一起使用时，优先使用AND

4 SELECT prod_id, prod_name
FROM Products
WHERE vend_id IN (‘DLL01’, ‘BRS01’)
ORDER BY prod_name;

5 SELECT prod_id, prod_name
FROM Products
WHERE vend_id NOT ‘DLL01’
ORDER BY prod_name;

五、用通配符进行过滤
SELECT prod_id, prod_name
FROM Products
WHERE vend_name LIKE ‘Fish%’

SELECT prod_id, prod_name
FROM Products
WHERE prod_desc LIKE '__ inch teddy bear';

SELECT prod_id, prod_name 
FROM Products
WHERE prod_name LIKE '[JM]%'

六、创建计算字段
计算字段并不实际存在数据库表中，而是运行时在SELECT语句内创建。
1 拼接字段,使用 + 或 || 表示(使用RTRIM()函数来消除空格)
SELECT RTRIM(vend_name) + ’ (’ + RTRIM(vend_country) + ‘)’
FROM Vendors
ORDER BY vend_name’;

2 使用别名AS
SELECT RTRIM(vend_name) + ’ (’ + RTRIM(vend_country) + ‘)’ AS vend_title
FROM Vendors
ORDER BY vend_name;

3 执行算数计算
SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_number = 2008;

七、使用数据处理函数
1 文本处理函数UPPER()#转化为大写
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

2 日期和时间处理函数
SELECT order_number
FROM Orders
WHERE DATEPART(yy, order_date) = 2004;

3 数值处理函数
ABS() EXP() PI() SQRT() SIN() COS() TAN()

八、汇总数据
1 聚集函数
AVG() COUNT() MAX() MIN() SUM()
SELECT AVG(prod_price) AS avg_price
FROM Products;

2 聚集不同值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = ‘DLL01’;
#只计算不同值的平均值

3 组合聚集函数，将多个函数一起应用

九、分组数据
1 创建分组
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

2 过滤分组
SELECT cust_id, COUNT() AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT() >= 2;

3 分组和排序
GROUP BY 和 ORDER BY

十、使用子查询:嵌套在其他查询中的查询
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = ‘RGAN01’);

十二、联结表
SELECT Products.vend_id, vend_name, prod_price
FROM Products, Vendors
WHERE Products.vend_id = Vendors.vend_id;

#等同于(等值联结，也称为内部联结)
SELECT Products.vend_id, vend_name, prod_price
FROM Products INNER JOIN Vendors
 ON Products.vend_id = Vendors.vend_id;

十三、高级联结
#FROM同一个表，使用表别名(自联结)
SELECT C1.cust_id, C1.cust_name
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_name = C2.cust_name
AND C1.cust_contact = ‘Jim Jones’;

自然联结
SELECT * 
FROM Products INNER JOIN Vendors
ON Vendors.vend_id = Products.vend_id;

外部联结
SELECT Customers.cust_id, cust_name, order_num
FROM Customers LEFT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;

十四、组合查询
#将多个结果显示在一起
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_id IN (1000000005, 1000000003, 1000000001)
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = ‘Fun4All’
ORDER BY cust_name;

十五、插入数据
INSERT INTO Customers
VALUES(‘1000000006’,
‘Zed’,
‘2 street’,
‘shanghai’,
‘SH’,
‘65656’,
‘China’,
‘Jim’,
NULL
);

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000007', 'Zero', '3 street', 'BJ', 89478, 'China', 'Kite');

#INSERT SELECT 语句，可以一次插入多行
#SELECT INTO 复制一个新表

十六、更新和和删除数据
UPDATA Customers
SET cust_name = ‘Jan’
WHERE cust_id = 1000000007;

DELETE FROM Customers
WHERE cust_id = 1000000007;

十七、创建和操作表
#创建表
CREATE TABLE ProdCopy
(
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 DEFAULT 8.88,
prod_desc VARCHAR(1000) ,
);
#删除表
DROP TABLE ProdCopy;
#更新表
ALTER TABLE ProdCopy
ADD prod_phone CHAR(10);
#重命名表

十八、视图(VIEW,虚拟的表)
CREATE VIEW ProductCustomers
AS
SELECT cust_name, cust_contact, prod_id
FROM Orders, Customers, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

#从视图中检索数据
SELECT * FROM ProductCustomers;

十九、存储过程(PROCEDURE)
#创建存储过程
CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;

CREATE PROCEDURE NewOrder @cust_id INTEGER 
AS 
DECLARE @order_num INTEGER
SELECT @order_num = MAX(order_num)
FROM Orders
SELECT @order_num = @order_num + 1
INSERT INTO Orders(cust_id, order_num, order_date) 
VALUES(@cust_id, @order_num, GETDATE());
RETURN @order_num;

#使用标识字段
CREATE PROCEDURE NewOrder2 @cust_id CHAR(10)
AS
INSERT INTO Orders(cust_id, order_date)
VALUES(@cust_id, GETDATE())
SELECT order_num = @@IDENTITY;  

#执行存储过程
EXECUTE NewOrder 20005

二十、事务处理(TRANSACTION)
事务：一组SQL语句
回退：撤销指定SQl语句
提交：将未储存的SQL语句结果写入数据库
保留点：事务处理中设置的临时占位符，可以对他进行回退。
(可以回退INSERT, UPDATE, DELETE语句。CREATE, DROP不可回退)。

BEGIN TRANSACTION
DELETE 
FROM Orders
WHERE cust_id=1000000006
SAVE TRANSACTION delete1
DELETE 
FROM Orders
WHERE cust_id=1000000007
COMMIT TRANSACTION;

#回退
ROLLBACK TRANSACTION  delete1

二十一、游标(CURSOR)
@创建游标
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

OPEN CustCursor
DECLARE @cust_id CHAR(10),
	@cust_name CHAR(50),
	@cust_address CHAR(50),
	@cust_city	CHAR(50),
	@cust_state CHAR(5),
	@cust_zip CHAR(10),
	@cust_country CHAR(50),
	@cust_contact CHAR(50),
	@cust_email CHAR(255),

FETCH NEXT FROM CustCursor
    INTO @cust_id, @cust_name, @cust_address, @cust_city, @cust_state, @cust_zip, @cust_country, @cust_contact, @cust_email
CLOSE CustCursor
DEALLOCATE CustCursor

二十二、高级特性
<1>约束

主键
CREATE TABLE Test
(	
test_id CHAR(10) NOT NULL PRIMARY KEY,
test_name CHAR(1) NOT NULL,
test_address CHAR(10) NOT NULL,
);

外键
CREATE TABLE test2
(
order_num  INTEGER  NOT NULL PRIMARY KEY,
order_date DATETIME NOT NULL,
cust_id    CHAR(10) NOT NULL REFERENCES Customers(cust_id)
);
#另一种外键方法
ALTER TABLE Customers
ADD CONSTRAINT
FOREIGN KEY(cust_id) REFERENCES Customers(cust_id)

唯一约束
CREATE TABLE NewTable
(
name      INTEGER   NOT NULL,
id        CHAR(10)  NOT NULL,
quantity  CHAR(10)  NOT NULL CHECK(quantity>0),
);   

索引
CREATE INDEX prod_name_index
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

数据库安全
#GRANT和REVOKE语句