了解SQL
主键的条件：
- 任意两行都不具有相同的主键值。
- 每一行都必须具有一个主键值，不允许为NULL值。
- 主键列中的值不允许修改或更新。
- 主键值不能重用，即使删除也不能赋给以后的新行。
什么是SQL
Structured Query Language，结构化查询语言
SQL语句的特点 - 以“；”分隔
- 不区分大小写
- 忽略空格
- 列名间用“，”
- “*”为通配符
- 注释格式“--”、“#”、“/* */”
关键字及相应语句
- SELECT/FROM
- DISTINCT 返回不同的值
SELECT DISTINCT vend_id
FROM Products;
- 限制结果 各种数据库中实现各异，具体如下：
--SQL Server
SELECT TOP 5 prod_name
FROM Products;
--DB2
SELECT prod_name
FROM Products
FETCH FIRST 5 ROWS ONLY;
--Oracle
SELECT prod_name
FROM Products
WHERE ROWNUM <= 5;
--MySQL
SELECT prod_name
FROM Products
LIMIT 5;
--第五行起的五行数据
SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;
--MySQL简写
LIMIT 5,5
- ORDER BY SELECT语句中最后一条子句：
SELECT prod_name
FROM Products
ORDER BY prod_name, prod_price;
--name相同时才会使用price排序
SELECT prod_id, prod__name, prod_price
FROM Products
ORDER BY 2 DESC, 3;
--DESC作用域只有一个列名，若都想降序排列需要在每个列名后添加
- WHERE 过滤子句：
SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;
-- <>不匹配，与！=同义
--BETWEEN AND
--IS NULL
--AND 优先级高于 OR
--IN ('DLL01', 'BRS01')，一般执行速度比OR更快，可动态建立WHERE子句
-- NOT prod_price = 3.49，常与IN、BETWEEN和EXISTS子句联合使用
- LIKE '%' 不会匹配到数据为NULL的行，主要考虑“空格”
- LIKE '_' 匹配单个字符
- LIKE '[JM]%' 匹配任一个
- 拼接字段
+ '(' + vend_country + ')'
Concat(vend_name, '(', vend_country, ')')
- 使用别名
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
AS vend_title
--TRIM()/LRIM()
- 处理数据的函数
LEFT()
LENGTH()
LOWER()
LTRIM()
RIGHT()
RTRIM()
SOUNDEX()--发音类似的
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_constact) = SOUNDEX('Michael Green');
UPPER()
WHERE DATEPART(yy, order_date) = 2012
--MySQL
WHERE YEAR(order_date) = 2012;
ABS()
COS()
EXP()
PI()
SIN()
SQRT()
TAN()
- 聚集函数
AVG()
COUNT()
MAX()
MIN()
SUM()
AVG(DISTINCT prod_price)
- GROUP BY
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
- HAVING
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 2;
- 子查询
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');
--OrderItems.order_num
- 联结
WHERE(叉联结，返回笛卡尔集) 或者INNER JOIN Products ON Vendors.vend_id = Products.vend_id;（内联结）
--自联结，使用相同的表
--找出与Jim Jones同一公司的所有顾客
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
--自然联结
--外联结
--包含本表中没有的数据
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
--LEFT OUTER JOIN 左表每一行去匹配右表
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
GROUP BY Customers.cust_id;
- 组合查询 注意列数据类型的一致性
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
-- UNION ALL 返回检索出的所有数据行
-- UNION 会自动取消重复行
- 插入数据
INSERT INTO Customers(...) VALUES(...)
--插入检索数据，忽略列名，直接填充第一列、第二列，两个表主键不能产生冲突
INSERT INTO Customers(...)
SELECT ...
FROM CustNew;
--创建新表
SELECT *
INTO CustCopy
FROM Customers;
- 更新和删除数据
--更新
UPDATE Customers
SET cust_email = '...'
cust_contact = '...'
WHERE cust_id = '...';
--删除
DELETE FROM Customers
WHERE cust_id = '...';
--删除所有行
TRUNCATE TABLE
- 创建表
CREATE TABLE Products
(
...
);
- 更新表
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
ALTER TABLE Vendors
DROP COLUMN vend_phone;
DROP TABLE CustCopy;
视图
什么是视图？
视图是虚拟的表，只包含使用时动态检索数据的查询。使用时需注意性能问题。
为什么使用视图？
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的一部分
- 保护数据，授予用户访问表的特定部分
- 更改数据格式和表示
视图的规则和限制
- 视图命名必须唯一，不能与视图或表命名一样
- 可以嵌套
- DBMS可能会在视图中禁用ORDER BY
- 不能索引，不能有关联的触发器或默认值
- DBMS可能把视图作为只读的查询，不能将数据写回底层表
创建视图
CREATE VIEW 视图名 AS SELECT ...
存储过程
为以后使用而保存的一条或多条SQL语句，可视为批文件，但作用不限于批处理。
为什么使用存储过程
简单、安全、高性能
执行存储过程
EXCUTE ...--方法名（参数）
CREATE PROCEDURE ...
事务
START TRANSACTION
...
ROLLBACK;
COMMIT TRANSACTION
SAVEPOINT delete1；
ROLLBACK TRANSACTION delete1；
ROLLBACK TO delete1；
游标
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL
OPEN CURSOR CustCursor
FETCH CustCursor INTO CustRecord;
CLOSE CustCursor;
高级SQL特性
- 约束
- 主键
- 外键 外键是表的一列，其值必须是另一表中的主键
- 索引
- 触发器
