1、检索不同的值（类似于取集合）：用DISTINCT关键字

SELECT DISTINCT id 
FROM List；

2、限制输出结果的数目：用TOP关键字

SELECT TOP 5 id
FROM List；

如果使用的MySQL，也可以用下面语句（出来单独一列前五数据）:LIMIT

SELECT id FROM List LIMIT 5

指定检索起点和行数:OFFSET

SELECT id 
FROM List 
LIMIT 4 OFFSET 3；
#在MySQL中等同于SELECT id FROM List LIMIT 3,4

第一个4代表检索的行数，第二个5代表从第3行开始（最开始是第0行）。

3、排序检索数据

1. 按列排序

取一个或多个列的名字进行排序：ORDER BY

SELECT id 
FROM List 
ORDER BY id；

注意:ORDER BY的位置一定是最后的子句，否则会报错，另外排序也可以选择非检索数据。

2. 按多列排序

SELECT prod_id,prod_price,prod_name
FROM Prod_list
OREDR BY prod_price,prod_name；    --先对价格排序，价格相同的在对物品名称排序

3. 按列位置排序

SELECT prod_id,prod_price,prod_name
FROM Prod_list
OREDR BY 2,3;   --先对检索的第二列(prod_price)排序，然后对检索的第三列(prod_name)进行排序

4. 指定排序方向
   降序：在ORDER BY后指定关键字DESC

SELECT prod_id,prod_price,prod_name
FROM Prod_list
OREDR BY prod_price DESC； 

多个列中按之一进行降序

SELECT prod_id,prod_price,prod_name
FROM Prod_list
OREDR BY prod_price DESC, prod_name;    --最贵的排前面，然后产品名还是按字母顺序

4、过滤数据

1. 按条件搜索数据：WHERE（位置在表名即FROM子句之后）

SELECT prod_name,prod_price
FROM Prod_list
WHERE prod_price = 3.9;

2. 不匹配检查

SELECT prod_id,prod_name
FROM Prod_list
WHERE prod_id<>'DLL01';     --列出所有不是DLL01生产的产品，其中<>和！=可以互换

3. 范围值检查：BETWEEN … AND …

SELECT prod_price,prod_name
FROM Prod_list
WHERE prod_price BETWEEN 5 AND 10;    --筛选价格位于5至10之间的产品

4. 空值检查：WHERE … IS NULL
5. 组合WHERE子句，其中操作符优先级：()>AND>OR
6. IN操作符

SELECT prod_name,prod_price
FROM Prod_list
WHERE prod_id IN ('DLL01','BRS01')    --等价于WHERE prod_id='DLL01' OR prod_id='BRS01'
ORDER BY prod_name;

7. NOT操作符：否定其后所跟的任何条件

SELECT prod_name
FROM Prod_list
WHERE NOT prod_id='DLL01'    --等价于WHERE prod_id != 'DLL01'
ORDER BY prod_name;

5、用通配符进行过滤

前面所介绍的所有操作符都是基于已知值进行过滤的，下面用通配符就好比正则表达式。
谓词不是操作符：LIKE(用于字符串)

1. %通配符：表示任何字符出现任意次数

SELECT prod_id,prod_name
FROM Prod_list
WHERE prod_name LIKE 'Fish%';     --匹配以词Fish起头的产品

2. _通配符:只匹配单个字符

SELECT prod_price,prod_name
FROM Prod_list
WHERE prod_price LIKE '__ yuan'    --匹配价格为两位数的产品

3. []通配符：指定一个字符集，必须匹配指定位置的一个字符（MySQL不支持）

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'     --匹配所有以J或者M开头的联系人
ORDER BY cust_contact;

4. 否定符：^

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'     --匹配所有除了J和M开头的联系人，等价于WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

6、计算字段

与前面表中的列意思基本相同，但其并不实际存在于数据库表中。

1. 拼接字段
   把两个列拼接起来成为一个新列，在SQL的SELECT语句中一般使用+和||来拼接，但根据DBMS的不同，方式有所不同，如在MySQL中使用特殊函数Concat.

SELECT Concat(prod_name,'(',prod_country,')')
FROM Prod_list
ORDER BY prod_name;

2. 去掉字符串中的空格
   RTRIM():去掉字符串右边的空格
   LTRIM():去掉字符串左边的空格
   TRIM():去掉字符串左右两边的空格
3. 赋予字段替换名:AS

SELECT Concat(RTRIM(prod_name) ,' (' ,RTRIM(prod_country) , ')')
AS prod_title
FROM Prod_list
ORDER BY prod_name;

4. 算数计算字段：对检索数据进行算数计算

SELECT prod_id,quantity,prod_price,
quantity*prod_price AS expanded_price
FROM Prod_list
WHERE order_num=20008;

7、函数

下面函数主要用于MySQL

1. 提取字符串的组成部分：SUBSTRING()
2. 数据类型转换：CONVERT()
3. 取当前日期：CURDATE()

可移植：所编写的代码可以在多个系统上运行。SQL函数不是可移植的。
4. SOUNDEX():将任何文本串转化为描述其语音表示的字母数字模式的算法，这个函数可以用于在匹配发音相似但拼写不准确的人名上

8、汇总数据

1. 聚集函数：对某些行运行的函数，计算并返回一个值。
   AVG():返回某列的平均值

SELECT AVG(prod_price) AS avg_price
FROM Prod_list
WHERE prod_id='DLL01';

COUNT():确定表中行的数目或符合特定条件的行的数目，其有以下两种使用方式：
COUNT(*):对列中无论NULL值还是非空值均计数。
COUNT(column):对列中具有值的行进行计数，忽略NULL值。其中column为指定的列名。
MAX(),MIN(),SUM():列中最大，列中最小，以及对列求和。SUM()忽略值为NULL的行。

2. 聚集不同值：在聚集函数中添加DISTINCT参数，以AVG函数为例

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Prod_list
WHERE prod_id='DLL01';

9、分组数据

1. 创建分组：GROUP BY

SELECT prod_id,COUNT(*) AS num_prods
FROM Prod_list
GROUP BY prod_id;   --统计各个供货商id的产品数

注意：GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

2. 过滤分组：筛选哪些分组，排除哪些分组，必须基于完整的分组而不是个别的行进行过滤的。
   HAVING:类似于WHERE,但是唯一差别是，WHERE过滤行，而HAVING过滤分组。

SELECT cust_id,COUNT(*) AS orders
FROM Orders   --一个订单一个条目
GROUP BY cust_id
HAVING COUNT(*) >= 2;  --筛选出订单至少为2个的所有顾客，用WHERE无法实现

3. GROUP BY 和ORDER BY混合使用：例如对订购物品的数目至少为3个的订单号基于数目排序输出

SELECT order_num,COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING count(*)>=3
ORDER BY items,order_num;

4. SELECT子句顺序：SELECT 、FROM 、WHERE 、GROUP BY 、HAVING 、ORDER BY

10、子查询

把第一个查询的结果当作条件去继续查询,查询顺序总是为由内向外查询。

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

作为计算字段使用子查询

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

11、联结表

在数据查询的执行中联结表是SQL最强大的功能之一。

• 关系表
  对于一个产品目录的数据库表，其中每一类物品占一行，对于每一种物品，要储存的信息包括产品描述、价格，以及生产该产品的供应商。
  因为同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方式等供应商信息？将这些数据与产品信息分开存储好处：
  （1）同一供应商生产的每个产品，其供应商的信息相同，如果对每个产品重复此信息即浪费时间又浪费存储空间。
  （2）若供应商信息发生变化，只需要修改一次即可。
  （3）如果有重复数据（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用。
  关系数据可以有效地存储，方便处理，因此，关系数据库的可伸缩性远比非关系数据库要好。
  可伸缩：能适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好。
• 创建联结

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

• WHERE子句
  笛卡尔积：由没有联结条件的表关系返回的结果。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
  实例中的笛卡尔积返回的数据用每个供货商匹配每个产品，即使供货商根本没有生产的产品。
  所以要保证所有联结都有WHERE子句，否则过滤条件将会返回许多不正确的问题。
  叉联结：返回笛卡尔积的联结
• 内联结
  等值联结：基于两个表间的相等测试，这种联结也称为内联结。
  对于这种联结可以使用稍微不同的语法，明确指定联结的类型，下面的SELECT语句返回与前面例子完全相同的数据：

SELECT vend_name,prod_name,prod_price
FROM Vendors
INNER IOIN 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   --WHERE中前两句是关联联结中的表
AND order_num=20007;   --这个是筛选数据的条件

12、创建高级联结

• 使用表别名:SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。
  列别名语法：

SELECT RTRIM(vend_name)+'('+RTRIM(vend_country)+')' AS vend_title
FROM Vendors
ORDER BY vend_name;

表别名语法：

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

注意：Oracle不支持AS关键字，若在Oracle中使用别名，应该是Customers C,而不是Customers AS C。另外，表别名只在查询执行中使用，与列别名不同，表别名不返回到客户端。

• 其他类型联结：自联结、自然联结、外联结
  （1）自联结：举个例子，假如要给与Jim Jones同一公司的所有顾客发送一封信件，首先用前面学到的子查询解决：

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

注意:此查询需要的两张表实际上是相同的表，因此Customers在FROM子句中出现了两次，在子查询中用的是同一张表，使用表别名是为了避免歧义。
（2）自然联结：无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。前面介绍的内联结返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。自然联结要求只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成。

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

(3)外联结：许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。例如：

• 对每个顾客下的订单进行计数，包括那些迄今为止尚未下订单的顾客
• 列出所有产品以及订购数量，包括没有人订购的产品
• 计算平均销售规模，包括那些至今尚未下订单的顾客。
  左外联结:

SELECT Customers.cust_id,Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id=Order.cust_id;   --从FROM子句左边的表中选择所有行

右外联结：

SELECT Customers.cust_id,Orders.order_num
FROM Customers
RIGHT OUTER JOIN Orders ON Customers.cust_id=Order.cust_id;   --从FROM子句右边的表中选择所有行

• 带聚集函数的联结
  聚集函数用来汇总数据，举个例子，要检索所有顾客及每个顾客所下的订单数，用COUNT()函数实现

SELECT Customers.cust_id,COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders ON Customers.cust_id=Orders.cust_id
GROUP BY Customers.cust_id;

13、组合查询

使用UNION，可以给出多条SELECT语句，将他们的结果组合成一个结果集。UNION从查询结果集中自动去除了重复的行，若想不取消重复的行，就用UNION ALL。

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进行组合时需要注意几条规则：

• UNION必须由两条及以上的SELECT语句组成，语句用关键字UNION分隔
• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）
• 在对组合查询结果排序时，只能使用一条ORDER BY子句，他必须位于最后一条SELECT语句之后。

14、插入数据

INSERT:将行插入到数据库表中。

1. 插入完整的行：要求指定表名和插入到新行中的值。每列提供一个值，否则用NULL代替。

INSERT INTO Customers
VALUES(10000000006,
'Toy Land',
'123 Any Street',
'New York',
'NY',
NULL,
NULL);

注意：这种语法简单，但不安全，依赖于表中列的定义次序，而且无法保证下一次表结构变动后保持完全相同的次序。
因此，更安全的语法（但是更繁琐,这种语法可以省略列，省略的列不提供值）如下：

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

2. 插入检索出的数据
   例子：把另一张表中的顾客列合并到Customers表中，不需要每次读取一行再将它用INSERT插入，可以如下：

INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
SELECT cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
FROM CustNew;   --注意CustNew表中不应该存在Customers中用过的cust_id值，因为此为主键，唯一，否则INSERT操作会失败。

3. 将表复制到另一个新表
   CREATE SELECT语句：

CREATE TABLE CustCopy AS SELECT * FROM Customers;

如果使用的是SQL Server,语法可以如下：

SELECT * INTO CustCopy FROM Customers;    --要想只复制部分列，可以将*换成明确的列名

15、更新或删除数据

1. 更新数据
   修改数据（UPDATE），注意不要忽略WHERE子句，因为稍不注意，就会更新表中的所有行。
   例子：客户10002现在有了电子邮件地址以及联系人，因此其记录需要更新:

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

删除某列的值，设置值为NULL,注意其与保存空字符串很不同，’'表示的还是一个值，而NULL表示没有值。

UPDATE Customers
SET cust_email=NULL
WHERE cust_id=10002;

2. 删除数据
   DELETE:从Customers表中删除一行，删除整行而不是列，若想删除指定的列，用UPDATE语句：

DELETE FROM Customers
WHERE cust_id=10002;

外键：联结表中的公用字段，即关联两张表的公共列，存在外键时，DBMS使用它们实施引用完整性。

16、创建和操纵表

CREATE TABLE:创建表常见信息如下：
新表的名字，在关键字CREATE TABLE之后给出；
表列的名字和定义，用逗号分隔；
有的DBMS还要求指定表的位置。

1. NULL值设置

CREATE TABLE Products
(
prod_id    CHAR(10)    NOT NULL,   --NOT NULL列表示状态，在插入行时不接受没有列值的行
vend_id    CHAR(10)    NOT NULL,
prod_price    DECIMAL(8,2)    NOT NULL,
prod_desc    VARCHAR(1000)    NULL    --NULL列表示插入行时允许不给出该列的值，默认设置，忽略时就是NULL状态
);

注意：在设置主键时，一定致命状态为NOT NULL,因为允许NULL值的列不能作为唯一标识。

2. 指定默认值：DEFAULT

CREATE TABLE Products
(
prod_id    CHAR(10)    NOT NULL,   
vend_id    CHAR(10)    NOT NULL,
prod_price    DECIMAL(8,2)    NOT NULL,
quantity    INTEGER    NOT NULL    DEFAULT 1,   --插入行如果不给出值，默认为1
prod_desc    VARCHAR(1000)    NULL   
);

默认值经常用于日期或时间戳列，MySQL指定DEFAULT CURRENT_DATE()。

3. 更新表：ALTER TABLE
   给表增加列

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

注意：使用ALTER TABLE要极为小心，应该在进行改动前做完整的备份（表结构和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除他们。

4. 删除表：DROP TABLE
   删除整张表，而不是其中部分内容，执行该语句将永久删除该表，不能撤销。

DROP TABLE CustCopy;

5. 重命名表：RENAME，要求指定旧表名和新表名。

17、视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
例如从三个表中检索数据：

SELECT cust_name,cust_contact
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id=Orders.cust_id
AND OrderItems.order_num=Orders.order_num
AND prod_id='RGAN01';

现在可以把整个查询包装成一个名为ProductCustomers的虚拟表，则可以如下轻松地检索出相同的数据：

SELECT cust_name,cust_contact
FROM ProductCustomers
WHERE prod_id='RGAN01';

1. 创建视图：CREATE VIEW，只能用于创建不存在的视图。在覆盖（更新）视图，必须先删除它，然后再重新创建，其语法为DROP VIEW viewname。

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;

2. 用视图重新格式化检索出的数据
   前面我们介绍了用RTRIM或者||语法来格式化检索结果，假设经常需要这个格式的结果，我们不必每次需要执行那种拼接，而是创建一个视图。

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name)+'('+RTRIM(vend_country)+')'
AS vend_title
FROM Vendors;

检索数据进行：

SELECT * FROM VendorLocations;

3. 用视图过滤不想要的数据
   创建CustomerEMailList视图，过滤没有电子邮件地址的顾客。

CREATE VIEW CustomerEMailList AS 
SELECT cust_id,cust_name,cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

4. 使用视图与计算字段

SELECT prod_id,quantity,prod_price,
quantity*prod_price AS expanded_price
FROM Prod_list
WHERE order_num=20008;

将其转化为一个视图：

CREATE VIEW OrderItemsExpanded AS
SELECT prod_id,quantity,prod_price,
quantity*prod_price AS expanded_price
FROM Prod_list

在对创建好的视图进行检索

SELECT *
FROM OrderItemsExpanded
WHERE order_num=20008;

18、存储过程(介绍)

概念：为以后使用而保存的一条或者多条SQL语句。可将其视为批文件。
为什么使用存储过程？简单、安全、高性能

1. 执行存储过程：EXECUTE
2. 创建存储过程：CREATE PROCEDURE

19、游标

1. 游标：是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。
   作用：在检索出来的行中前进或者后退一行或多行。存储游标后，应用程序可根据需要滚动或浏览其中的数据。
2. 创建游标：DECLARE
   例如创建一个游标来检索没有电子邮件地址的所有顾客，帮助操作人员来找空缺的电子邮件地址。

DECLARE CustCursor CURSOR    --CustCursor为定义的游标名
FOR 
SELECT * FROM Customers
WHERE cust_email IS NULL;

3. 使用游标：OPEN CURSOR

OPEN CURSOR CustCursor

访问游标数据：FETCH，指出要检索哪些行，从何处检索它们以及将它们放于何处。

4. 关闭游标

CLOSE CustCursor

游标在使用完毕后需要关闭，再次使用它时不需要再声明，只用OPEN打开即可。