最近需要用到SQL，之前学的SQL好久不用，好多知识点都记不清了，《SQL必知必会（第5版
）》在学习的过程中记录一下学习笔记，方便后续复习。
- 一般而言，除非你确实需要表中的每一列，否则最好别使用＊通配符。虽然使用通配符能让你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索速度和应用程序的性能。
-
DISTINCT关键字：
指示数据库只返回不同的值。
-
TOP：
限制最多返回多少行
- 限制结果：
- SQL Server：用TOP关键字来限制最多返回多少行
SELECT TOP 5 prod_name FROM Products; - Oracle：需要基于ROWNUM（行计数器）来计算行
SELECT prod_name FROM Products WHERE ROWNUM <=5; - MySQL、MariaDB、PostgreSQL或者SQLite：需要使用LIMIT子句，需要使用LIMIT和OFFSET指定从哪儿开始以及检索的行数，LIMIT（检索的行数） OFFSET（从哪儿开始）
SELECT prod_name FROM Products LIMIT 5; SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
-
ORDER BY：
取一个或多个列的名字，据此对输出进行排序（tips：在指定一条ORDER BY子句时，应该保证它是SELECT语句中最后一条子句。如果它不是最后的子句，将会出错。）
-
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC; --DESC降序 ASC升序（默认为升序） -
tips：
-
DESC是DESCENDING的缩写，ASC是ASCENDING的缩写。
-
在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。
- IS NULL：为NULL值
-
IN 操作符：
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;tips：
-
IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。
-
IN操作符一般比一组OR操作符执行得更快。
-
LIKE操作符：
通配符：%、_、[]
- 拼接字段：
SELECT vend_name + '(' + vend_country + ')' FROM Vendors ORDER BY vend_name; SELECT vend_name || '(' || vend_country || ')' FROM Vendors ORDER BY vend_name; -
文本处理函数：
-
RTRIM()、LTRIM()、TRIM()：
去掉不想要的空格
-
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' FROM Vendors ORDER BY vend_name;RTRIM()：去掉字符串右边不想要的空格
-
LTRIM()：去掉字符串左边的空格
-
TRIM()：去掉字符串左右两边的空格
-
UPPER()：
将文本转换为大写
-
LOWER（）：
将文本转化成小写
-
SUBSTR()：
提取字符串的组成部分
-
LENGTH（）：
字符串的长度
-
SOUNDEX（）：
是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。
-
日期和时间处理函数：
-
DATEPART()：
此函数返回日期的某一部分。
-
YEAR()：
从日期中提取年份
-
数值处理函数
- ABS()、COS()、EXP()、PI()、SIN()、SQRT()、TAN()
-
聚集函数（5个）
-
AVG()：
计算平均值
-
COUNT()：
计数
tips：
COUNT(*)：计数包括空值（NULL)以及非空值
COUNT(column)：忽略NULL值
DISTINCT不能用于COUNT(＊)
-
MAX()：
计算最大值
tips：
在用于文本数据时，MAX()返回按该列排序后的最后一行。
MAX()函数忽略列值为NULL的行。
-
MIN()：
计算最小值
tips：在用于文本数据时，MIN()返回该列排序后最前面的行。
MIN()函数忽略列值为NULL的行。
-
SUM()：
求和
tips：SUM()函数忽略列值为NULL的行。
-
分组数据
-
GROUP BY
-
SELECT vend_id, COUNT(＊) AS num_prods FROM Products GROUP BY vend_id;
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
tips：
HAVING和WHERE的差别：
这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。
-
笛卡儿积（cartesian product）：
由没有联结条件的表关系返回的结果为笛卡儿积。
检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
叉联结：
有时，返回笛卡儿积的联结，也称叉联结（cross join）
-
内联结（inner join）：
目前为止使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）。
INNER JION ON
SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id;联结多个表格：
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;tips：
性能考虑DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。
-
自联结（self-join）：
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';tips：
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。
-
自然联结（natural join）：
无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。
-
外联结（outer join）：
联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。
LEFT OUTER JOIN ON：从FROM子句左边的表（Customers表）中选择所有行
RIGHT OUTER JOIN ON：从FROM子句右边的表中选择所有行
FULL OUTER JOIN ON：检索两个表中的所有行并关联那些可以关联的行。（MySQL不支持）
与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。
-
内联结、外联结、自然联结之间的区别：
内联结：有重复的列，不会删除
自联结：两个表格是同一个，两个表格的内联结。
自然联结：会删除重复的列。
外联结：联结包含了那些在相关表中没有关联行的行。
-
组合查询（UNION)
可用UNION操作符来组合数条SQL查询。
利用UNION，可给出多条SELECT语句，将它们的结果组合成一个结果集。
UNION中的每个查询必须包含相同的列、表达式或聚集函数。
UNION从查询结果集中自动去除了重复的行。
如果想返回所有的匹配行，可使用UNION ALL而不是UNION。
tips:
UNION & UNION ALL:
加 ALL 是优化性能非常有效的手段，不过前提是不在乎结果是否有重复数据。
-
插入数据（INSERT)
插入数据：
INSERT INTO Customers
VALUES(1000000006,
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES(1000000006,
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);虽然这种语法很简单，但并不安全，应该尽量避免使用
-
更新数据（UPDATE）：
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = 1000000005;
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = 1000000006;-
删除数据：
DELETE FROM Customers
WHERE cust_id = 1000000006;tips：
- DELETE不需要列名或通配符。DELETE删除整行而不是删除列。要删除指定的列，请使用UPDATE语句。
- DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。
- 更快的删除如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。
-
表：
创建表：CREATE TABLE
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
);更新表：ALTER TABLE
增加列：
ALTER TABLE Vendors
ADD vend_phone CHAR(20);删除表：DROP TABLE
-
视图：
创建视图：CREATE VIEW
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;-
事务处理：
事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。
事务（transaction）指一组SQL语句；
回退（rollback）指撤销指定SQL语句的过程；
提交（commit）指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。
START TRANSACTION
...ROLLBACK命令：用来回退（撤销）SQL语句
DELETE FROM Orders;
ROLLBACK;-
游标
游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。
创建游标：使用DECLARE语句创建游标
DECLARE CustCursor CURSOR
FOR
SELECT ＊ FROM Customers
WHERE cust_email IS NULL;使用游标：使用OPEN CURSOR语句打开游标
OPEN CURSOR CustCursor访问游标：用FETCH语句访问游标数据
-
约束
-
主键：
主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。
tips：
任意两行的主键值都不相同。
每行都具有一个主键值（即列中不允许NULL值）。
包含主键值的列从不修改或更新。
主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。
CREATE TABLE Vendors
(
vend_id CHAR(10) NOT NULL PRIMARY KEY,
vend_name CHAR(50) NOT NULL,
vend_address CHAR(50) NULL,
vend_city CHAR(50) NULL,
vend_state CHAR(5) NULL,
vend_zip CHAR(10) NULL,
vend_country CHAR(50) NULL
); ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);-
外键：
CREATE TABLE Orders
(
order_num INTEGER NOT NULL PRIMARY KEY,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
); ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);-
唯一约束：
唯一约束用来保证一列（或一组列）中的数据是唯一的
tips：
表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含NULL值。
唯一约束列可修改或更新。
唯一约束列的值可重复使用。
与主键不一样，唯一约束不能用来定义外键。
-
检查约束：
检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。
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]');-
索引
-
创建索引：
用CREATE INDEX语句创建
CREATE INDEX prod_name_ind
ON Products (prod_name);-
触发器
触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。
- 能写在 WHERE 子句里的条件不要写在 HAVING 子句里
222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
