产品读书《SQL必知必会》_产品必会sql-CSDN博客

大学期间学过数据库相关的知识，无奈几年下来能吃到肚子里的都吃进去了，留在脑子里的恐怕没多少了，其实之前也没有认认真真的学习过数据库，全靠实践中一点点查，还好也不算晚，看一本非常基础但是比较全面的基础书来补一补N多年前的知识，就当是复习笔记吧！很遗憾的一点是在这本书的读书笔记里面我不会太过详细的记录具体的语法，更多的是回顾，如若不详细，还请各位移步原著去通读一遍！但也希望与我有益的同时帮助到您！

Xmind脑图下载

一了解数据库SQL

数据库（database） vs 数据库管理系统（DBMS）
表（table）、模式（schema）
列（column）、数据类型（datatype）
行（row）
主键（primary key）：应该总是定义主键

二检索数据

检索单个列

SELECT PROD_NAME 
FROM Product;

检索多个列

SELECT prod_id, prod_name, prod_price
FROM Products;

检索所有列

SELECT *
FROM Products;

检索不同值：不能部分使用DISTINCT

SELECT DISTINCT vend_id
FROM Products;

限制结果

SQL SERVER & Access ：TOP DB2: Oracle：ROWNNUM MySQL、MariaDB、PostgreSQL或者SQLite: LIMIT

SELECT TOP 5 prod_name
FROM Products;

SELECT prod_name
FROM Products
FETCH FIRST 5 ROWS ONLY;

SELECT prod_name
FROM Products
WHERE ROWNUM <=5;

SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;       -- 从第5行起ide5行数据

补充：

#1. 这是一条注释

/*2. 这是多

行注释*/

--3. 这是另外一种注释：从第5行起ide5行数据

三排序检索数据

排序数据

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price;

按多个列排序检索数据

#仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

按列位置进行排序:不建议

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

指定排序方向：默认升序，DESC降序，且只对其前面的有效

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC,prod_name;   --只对prod_price列指定DESC，对prod_name列不指定

四过滤数据

where语句：警告：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

where字句操作符：

五高级数据过滤

AND 和OR组合使用时：AND优先级>OR,因此需要将OR语句用（）括起来

SELECT prod_name,prod_price

FROM Products

WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')

AND prod_price >=10;

六通配符进行过滤

LIKE

百分号（%）通配符
下划线（_）通配符
方括号（[]）通配符：方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

注意：

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

七创建计算字段

（1）拼接字段（将值联结到一起构成单个值）

concat函数（mysql用）：select concat(vend_id,’ (‘,vend_country,’)’) #select语句联结以上4个元素。
‘+’号（Access，SQL Server,Sybase使用）：select vend_id + ‘ (‘ + vend_country + ‘)’。
‘||’号（DB2,Oracle使用）：select vend_id || ‘ (‘ || vend_country || ‘)’。
TRIM函数：RTRIM()去掉值右边所有空格，LTRIM()去掉值左边所有空格，TRIM()去掉值两边所有空格。
AS关键字：给联结字段取别名。
完整联结字段语句： select RTRIM(vend_id) + ‘ (‘ + TRIRM(vend_country) + ‘)’ AS vendor_title FROM Vendors ORDER BY vend_name。

（2）执行算数计算：+、-、*、/

八使用数据处理函数

SQL函数不可移植；

（1）文本处理函数：

注：SOUNDEX将任何文本串转换为描述其语音表示的字母数字模式。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。

（2）日期和时间处理函数

DATAPART()、to_date()等，不同环境语法不同；

（3）数值处理

九汇总数据

（1）聚集函数

（2）聚集不同值：DISTINCT

警告：DISTINCT不能用于COUNT(*)
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

提示：将DISTINCT用于MIN()和MAX()
虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否只考虑不同值，结果都是相同的。

（3）组合聚集函数

十分组数据

（1）GROUP BY

GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。
除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

（2）过滤分组Having： where过滤行，Having过滤分组

说明：HAVING和WHERE的差别
这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

（3）分组和排序

（4）SELECT子句顺序

十一使用子查询

（1）利用子查询进行过滤

SELECT cust_id
FROM Order
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

警告：：只能是单列

作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

子查询常用于WHERE子句的IN操作符中，以及用来填充计算列。

（2）作为计算字段使用子查询

SELECT cust_name,
       cust_state,
      (SELECT COUNT(*)
       FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers ORDER BY cust_name;

注：子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名，而不只是列名（cust_id）

十二、联结表&高级联结表

1. 使用别名：AS

2. 创建联结

a. 等值联结，又叫内联结（inner join）

原始：

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id;

内联结：

SELECT vend_name, prod_name, prod_price 
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

警告：：完全限定列名--在引用的列可能出现歧义时，必须使用完全限定列名（用一个句点分隔表名和列名，eg：Vendors.vend_id）。

注意：
由没有联结条件的表关系返回的结果为笛卡儿积（叉联结（cross join））。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数，要保证所有联结都有WHERE子句，否则DBMS将返回比想要的数据多得多的数据。

b. 自联结（self-join）

提示：用自联结而不是子查询
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多。

子查询：
SELECT cust_id, cust_name, cust_contact
FROM Customers WHERE cust_name = (SELECT cust_name
                                  FROM Customers 
                                  WHERE cust_contact = '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';

c.自然联结（natural join）

自然联结排除多次出现，使每一列只返回一次,迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结.

SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price
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';

d. 外联结（outer join）:

联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders 
ON Customers.cust_id = Orders.cust_id

左外联结:使用LEFT OUTER JOIN从FROM子句左边的表中选择所有行
右外联结: 使用RIGHT OUTER JOIN从FROM子句右边的表中选择所有行
全外联结（full outer join）:检索两个表中的所有行并关联那些可以关联的行,全外联结包含两个表的不关联的行

e. 使用带聚集函数的联结

十四、组合查询（并UNION、符合查询）

主要有两种情况需要使用组合查询：

在一个查询中从不同的表返回结构数据；
对一个表执行多个查询，按一个查询返回数据

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';

十五、数据插入

1. 插入完整/部分的行

INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,)
VALUES('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',NULL,);

警告：小心使用VALUES
不管使用哪种INSERT语法，VA LUES的数目都必须正确。如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每个列一个值。否则，就会产生一条错误消息，相应的行不能成功插入。

2.插入检索出的数据

INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact)

SELECT cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact

FROM CustNew

说明：

INSERT SELECT与SELECT INTO
它们之间的一个重要差别是前者导出数据，而后者导入数据。

3. 从一个表复制到另一个表

SELECT *
INTO CustCopy
FROM Customers;

MariaDB、MySQL、Oracle、PostgreSQL和SQLite使用：

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

十六、更新和删除数据

1. 更新数据

UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

注：其中NULL用来去除cust_email列中的值。这与保存空字符串很不同（空字符串用''表示，是一个值），而NULL表示没有值。

2.删除数据

DELETE FROM Customers
WHERE cust_id = '1000000006';

DELETE不需要列名或通配符。DELETE删除整行而不是删除列。要删除指定的列使用UPDATE语句。
DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。
如果想从表中删除所有行，不使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快。

十七、创建和操纵表

1、创建表

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 DEFAULT 10,
prod_desc VARCHAR(1000) NULL
);

提示：替换现有的表
在创建新的表时，指定的表名必须不存在，否则会出错。防止意外覆盖已有的表，SQL要求首先手工删除该表（请参阅后面的内容），然后再重建它，而不是简单地用创建表语句覆盖它。

1.1、使用NULL值

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值。
NULL为默认设置，如果不指定NOT NULL，就认为指定的是NULL。
在不指定NOT NULL时，多数DBMS认为指定的是NULL，但不是所有的DBMS都这样。DB2要求指定关键字NULL，如果不指定将出错。
主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识。
不要把NULL值与空字符串相混淆。NULL值是没有值，不是空字符串。如果指定''（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空字符串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空字符串指定。

1.2.使用默认值DEFAULT

2.更新表

ALTER TABLE Vendors
ADD vend_phone CHAR(20);
DROP COLUMN vend_phone;

3.删除表

DROP TABLE CustCopy;

注：删除表（删除整个表而不是其内容）

4.重命名表

RENAME、sp_rename、ALTER TABLE(都要求指定旧表名和新表名)

十八、使用视图

视图是虚拟的表，视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的，在更改这些表中的数据时，视图将返回改变过后的数据。

（1）视图的规则和限制

视图可以嵌套；
禁止在视图查询时使用ORDER BY语句。

（2）创建视图（creat view）

利用视图简化复杂的联结；
用视图重新格式化检索出的数据；
用视图过滤不想要的数据（where子句）；
使用视图与计算字段。

说明：视图重命名
删除视图，可以使用DROP语句，其语法为DROP VIEW viewname;。
覆盖（或更新）视图，必须先删除它，然后再重新创建。

十九、使用存储过程

为以后的使用而存储一条或多条SQL语句的集合。

二十、管理事务处理

（1）什么是事务处理

事务管理用来管理必须成批执行的SQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组数据不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。

如果没有错误发生，则整组语句写到数据库表，如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

（2）几个术语

事务（transction）：一组SQL语句；
回退（rollback）：指撤销指定SQL语句的过程；
提交（commit）：指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）：指事务处理中设置的临时占位符，你可以对它发布回退（与回退整个事务处理不同）。

（3）可以回退哪些语句

可管理INSERT，UPDATE，DELETE语句，不能回退SELECT语句也不能回退CREATE，DROP操作。事务处理中可以使用这些语句，但回退时它们不被撤销。

（4）控制事务处理

在这个例子中，BEGIN TRA NSA CTION和COMMIT TRA NSA CTION语句之间的SQL必须完全执行或者完全不执行。

BEGIN TRANSACTION
...
COMMIT TRANSACTION

其他：

START、SET TRANSCATION...COMMIT、BEGIN。。。其他DBMS采用上述语法的变体。多数实现没有明确标识事务处理在何处结束。事务一直存在，直到被中断。通常，COMMITT用于保存更改，ROLLBA CK用于撤销。

1.ROLLBACK

DELETE FROM Orders;
ROLLBACK;

分析：执行DELETE操作，然后用ROLLBA CK语句撤销。

2. COMMIT

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

分析：从系统中完全删除订单12345。因为涉及更新两个数据库表Orders和OrderItems，所以使用事务处理块来保证订单
不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交。

3. 保留点（占位符）

提示：可以在SQL代码中设置任意多的保留点，因为保留点越多，你就越能灵活地进行回退。

mysql： savepoint delete1；------>rollback to delete1;
sql server：save trasaction delete1; #delete1为此占位符的名字。 ------> rollback transaction delete1;#回退到保留点delete1;

二十一、使用游标（cursor）

游标不是一条select语句，而是被该语句检索出来的结果集，在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或更改。

声明：declare

DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

打开：open cursor,在处理OPEN CURSOR语句时，执行查询，存储检索出的数据以供浏览和滚动。
检索：fetch… into…
关闭：close 一旦游标关闭，如果不再次打开，将不能使用。第二次使用它时不需要再声明，只需用OPEN打开它即可

二十二、高级SQL特性

约束：管理如何插入或处理数据库数据的规则

主键：一种特殊约束PRIMARY KEY

CREATE TABLE Vendors
(
vend_id CHAR(10) NOT NULL PRIMARY KEY,
vend_name CHAR(50) NOT 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)

唯一约束：UNIQUE 唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含NULL值。
唯一约束列可修改或更新。
唯一约束列的值可重复使用。
与主键不一样，唯一约束不能用来定义外键。

检查约束：检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点。

检查最小或最大值。例如，防止0个物品的订单（即使0是合法的数）。
指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
只允许特定的值。例如，在性别字段中只允许M或F。

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 prod_name_ind
ON PRODUCTS (prod_name);

索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

触发器：触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、UPDA TE和DELETE操作（或组合）相关联。

保证数据一致。例如，在INSERT或UPDATE操作中将所有州名转换为大写。
基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
计算计算列的值或更新时间戳。

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;

数据库安全：安全性使用SQL的GRA NT和REVOKE语句来管理，不过，大多数DBMS提供了交互式的管理实用程序，这些实用程序在内部使用GRA NT和REVOKE语句。