SQL必知必会 CH01~07

SQL

CH01 了解SQL

表（table）

某种特定类型数据的结构化清单。

表名

表名在相同的数据库中必须唯一。

模式（schema）

描述了数据库和表的布局及特性的信息

列（column）

表中的一个字段。可想像成一张网格表中的竖列。

数据类型

数据类型限制（允许）了列中所存储数据的类型。
一方面有助于分类数据，另一方面有助于优化磁盘使用。

行（row）

表中功能的一个记录（record）。可想像成网格表中的一横行。

主键（primary key）

一列（或一组列），其值能唯一地标识表中的每一行。

虽然不总是需要主键，但每个表有唯一主键有助于以后的数据操作和管理

CH02 检索数据

SELECT语句

从一个或多个表中检索信息

检索单个列

SELECT prod_name
FROM Products;

• 多条语句必须以分号（；）分割
• SQL语句不区分大小写，一般SQL关键字使用大写
• SQL语句可以写成长长的一行，也可以分多行写

检索多个列

SELECT prod_id, prod_name, prod_price
FROM Products;

选择多个列时，列之间要加逗号，但最后一个列名后不加。

SQL语句一般返回原始、无格式的数据。格式化属于表示问题，不是检索问题。

检索所有列

可以使用星号通配符（*）返回所有的列

SELECT *
FROM Products;

警告：除非确实需要表中每一列，否则最好不要使用星号通配符。因为检索不需要的列通常会降低检索的应用程序的性能。

提示：使用通配符的其中一个优点，就是能检索出名字未知的列

检索不同的值

如果想仅检索出不同的值（重复值只显示一次），可以使用DISTINCT关键字：

SELECT DISTINCT vend_id
FROM Products;

警告：DISTINCT关键字作用于所有的列，故不能部分使用DISTINCT

限制结果

如果只想返回一定数量的行，不同的DBMS有不同的实现。

SQL Server和Access

使用TOP关键字

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、MariaDB、PostgreSQL或者SQLite

SELECT prod_name
FROM Products
LIMIT 5;

如果还想指定从哪一行开始数，还可以使用OFFSET关键字：

SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;

注意第一个被检索的是第0行。

注释

-- 单行注释
/* 这里是
     多行注释 */

CH03 排序检索内容

排序数据

如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。

使用ORDER BY

使用ORDER BY子句对一个或多个列排序（默认生序）：

SELECT prod_name
FROM Products
ORDER BY prod_name;

ORDER BY子句的位置需要确保它是SELECT语句中的最后一条子句。

用非检索的列排序是完全合法的。

按多个列排序

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

上述代码首先对price排序，如果相同，则按name排序。

按列位置排序

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

优点：不用重新输入键名
缺点：1. 可能用错列；2. 修改SELECT清单时忘了同时修改

指定排序方向

默认升序排列，如果想进行降序排列，需要指定DESC关键字

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

注意，DESC关键字只应用到其前面的列名。如果想在多个列上降序排列，必须对每一列指定DESC关键字。

CH04 过滤数据

WHERE子句

WHERE子句指定搜索条件进行过滤

位置

WHERE子句在FROM子句后给出。同时使用ORDER BY和WHERE时，ORDER BY需位于WHERE之后。

WHERE子句操作符

WHERE子句一般需要搭配操作符使用，下面是一个操作符清单：
[image:D6775C92-9408-4EAA-B901-3F756B00FE7A-3677-00006FF301E9D66A/ECC488AD-CAA1-48CE-B130-B51E7452BFD0.png]

关于兼容性：上面的部分操作符是冗余的，且并非所有DBMS都支持，需查阅相应文档。

例子

-- 检查单个值
SELECT prod_name, prod_price
FROM Products
WHERE prod_price != 10;
-- 范围值内检查
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
-- 空值检查
SELECT prod_name
FROM Products
WHERE prod_price IS NULL
-- 

关于非匹配与NULL：有时希望过滤出不包含指定值（如prod_price != 10），同时包含值为NULL的结果。但因为NULL的含义为未知(unknown)，所以在进行过滤时不会返回这些结果。如果想返回含NULL值的行，需要配合使用 AND 和 IS NULL

CH05 高级数据过滤

逻辑操作符

用来联结或改变WHERE子句中的子句的关键字。

例子

-- AND操作符
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;
-- OR操作符(许多DBMS采用短路求值)
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’
-- IN操作符(范围匹配，相当于组合AND和OR)
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;
-- NOT操作符(否定其后所跟的任何条件)
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01

关于求值顺序

AND和OR组合使用时，会优先处理AND操作符。为避免歧义，应尽量使用圆括号对操作符进行分组

IN操作符的优点

• 在有多个合法选项时，IN操作符更直观
• 容易管理求值顺序
• IN一般比一组OR执行更快
• IN可以包含其他SELECT语句

NOT操作符的优点

在复杂的子句中十分有用（例如与IN操作符联用）。

CH06 用通配符进行过滤

LIKE操作符

LIKE指示DBMS，后跟的搜索模式使用通配符匹配而不是简单的相等匹配。

例子

-- 百分号（%）通配符：匹配任意字符出现任意次数（包含0次）
SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE 'Fish%';
-- 下划线（_）通配符：匹配单个字符（有且只有1个）
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';  -- 此处匹配两个字符，只有一个字符时不匹配
-- 方括号（[]）通配符：指定字符集，匹配指定位置的一个字符; 仅Access和SQL Server支持
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'     -- 匹配以J或M开头的的名字
ORDER BY cust_contact;

技巧

• 不要过度使用通配符
• 尽量不要在开始处使用通配符
• 仔细注意通配符的位置

注意空格：
一些DBMS会用空格填补字段内容。如某列有50个字符，但只存储了20字符的文本，则会在后面附加30个空格以填满该列。
因此部分SQL语句会收到影响。例如想用’F%y’匹配F开头，y结尾的数据，但如果后面填充了空格，将导致部分数据不会被检索出来。解决办法是使用TRIM函数。

CH07 创建计算字段

计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式（如一般不存储总价，而是存储数量和单价）。
字段（filed）与列（column）不同，它并不实际存在于数据库表中。如计算字段是运行时在SELECT语句内创建的。

拼接字段

如果我们需要创建由两列组成的标题（如供应商名+所在国家），可以通过把两个列拼接来实现

拼接操作符（不同DBMS不一致）：
+：Access, SQL Server
|| ：DB2、Oracle、PostgreSQL、SQLite和Open Office Base
Concat()函数：MySQL, MariaDB

-- 以+操作符为例
SELECT vend_name + ' (' + TRIM(vend_country) + ')' -- 去除填充的空格
FROM Vendors;

使用别名

一般的列有列名，而拼接后的字段可以使用别名（alias）。别名用AS关键字赋予。

SELECT vend_name + ' (' + TRIM(vend_country) + ')'
    AS vend_title
FROM Vendors;

执行算数计算

除了拼接，还可以对检索出的数据进行计算（如总价=单价*数量）。

SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;

SQL算数操作符

[image:60D35F67-E97A-4871-A44D-15E8494F434A-3677-000072B32D1E5BAF/ECEC2C1C-099C-45C1-A886-74A842737AFD.png]

如何测试计算：
SELECT语句为测试、检验函数和计算提供了很好的方法。如果省略了FROM子句后就是简单的访问和处理表达式，例如SELECT 3 * 2; 将返回6