《SQL必知必会》拾遗

原创 Django Java工程师成长日记 2018-07-30

数据库（database）
保存有组织的数据的容器（通常是一个文件或一组文件）。

表（table）
某种特定类型数据的结构化清单。

模式
关于数据库和表的布局及特性的信息。

列（column）
表中的一个字段。所有表都是由一个或多个列组成的。

主键（primary key）
一列（或一组列），其值能够唯一标识表中每一行。

SQL（发音为字母S-Q-L或sequel）是结构化查询语言（StructuredQuery Language）的缩写。SQL是一种专门用来与数据库沟通的语言。

SQL语句不区分大小写。许多SQL开发人员喜欢对SQL关键字使用大写，而对列名和表名使用小写，这样做使代码更易于阅读和调试。

处理SQL语句时，其中所有空格都被忽略。SQL语句可以写成长长的一行，也可以分写在多行。多数SQL开发人员认为，将SQL语句分成多行更容易阅读和调试。

限制结果行数

SQL Server和Access中：SELECT TOP 5 prod_name  FROMProducts;

DB2：SELECTprod_name FROM Products FETCH FIRST 5 ROWS ONLY;

Oracle：SELECTprod_name FROM Products WHERE ROWNUM <=5;

MySQL、MariaDB、PostgreSQL或者SQLite:SELECT prod_name FROM Products LIMIT 5;

LIMIT 5 OFFSET 5指示MySQL等DBMS返回从第5行起的5行数据

MySQL和MariaDB支持简化版的LIMIT 4 OFFSET 3语句，即LIMIT3,4

行内注释  注释使用-- （两个连字符）嵌在行内。-- 之后的文本就是注释

在一行的开始处使用#，这一整行都将作为注释。

注释从/*开始，到*/结束，/*和*/之间的任何内容都是注释。

ORDER BY子句必须放在末尾

通过非选择列进行排序
通常，ORDER BY子句中使用的列将是为显示而选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

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

升序：ASC（ASCENDING）

降序：DESC（DESCENDING）

表4-1 WHERE子句操作符

SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。此问题的解决方法是使用圆括号对操作符进行明确分组。

IN
WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。

NOT
WHERE子句中用来否定其后条件的关键字。

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

说明：MariaDB中的NOT
MariaDB支持使用NOT否定IN、BETWEEN和EXISTS子句。大多数DBMS允许使用NOT否定任何条件。

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。

说明：Access通配符
如果使用的是Microsoft Access，需要使用*而不是%。

另一个有用的通配符是下划线（_）。它只匹配单个字符

说明：DB2通配符
DB2不支持通配符_。
说明：Access通配符
如果使用的是Microsoft Access，需要使用?而不是_。

方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。说明：并不总是支持集合
与前面描述的通配符不一样，并不是所有DBMS都支持用来创建集合的[]。只有微软的Access和SQL Server支持集合。为确定你使用的
DBMS是否支持集合，请参阅相应的文档。

可以用前缀字符^（脱字号）来否定。

说明：Access中的否定集合
如果使用的是Microsoft Access，需要用!而不是^来否定一个集合，因此，使用的是[!JM]而不是[^JM]

计算字段：

拼接字段：

拼接（concatenate）
将值联结到一起（将一个值附加到另一个值）构成单个值。
解决办法是把两个列拼接起来。在SQL中的SELECT语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的DBMS，此操作符可用加号（+）或两个竖杠（||）表示。在MySQL和MariaDB中，必须使用特殊的函数。
说明：是+还是||？
Access和SQLServer使用+号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base使用||。详细请参阅具体的DBMS文档

下面是MySQL和MariaDB中使用的语句：
输入▼
SELECT Concat(vend_name, ' (', vend_country, ')')
AS vend_title
FROM Vendors
ORDER BY vend_name;

说明：TRIM函数
大多数DBMS都支持RTRIM()（正如刚才所见，它去掉字符串右边的空格）、LTRIM()（去掉字符串左边的空格）以及TRIM()（去掉字符串左
右两边的空格）。

表7-1 SQL算术操作符

数据处理函数：

常用的文本处理函数

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

常用数值处理函数

SQL聚集函数

分组数据

Group by

使用having来过滤分组，其后必须使用聚集函数

SELECT子句及其顺序

子查询（subquery），即嵌套在其他查询中的查询。

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

利用子查询进行过滤

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Order
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));

作为计算字段使用子查询

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

可伸缩（scale）
能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好（scalewell）。

简单联结：内联结或等值联结

高级联结：自联结（self-join）、自然联结（natural join）和外联结
（outer 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';

自然联结排除多次出现，使每一列只返回一次。怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它（指定列名，排除掉重复的列）。

外联结：联结包含了那些在相关表中没有关联行的行。

警告：FULL OUTER JOIN的支持Access、MariaDB、MySQL、OpenOffice Base或SQLite不支持FULL OUTER JOIN语法。

编写依赖于特定列次序的SQL语句是很不安全的，一旦表结构发生变化就会出问题。

提示：总是使用列的列表
不要使用没有明确给出列的INSERT语句。给出列能使SQL代码继续发挥作用，即使表结构发生了变化。

插入检索出的数据：将SELECT语句的结果插入表中，这就是所谓的INSERTSELECT。顾名思义，它是由一条INSERT语句和一条SELECT语句组成的。

INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM CustNew;

从一个表复制到另一个表

SELECT *
INTO CustCopy
FROM Customers;

MariaDB、MySQL、Oracle、PostgreSQL和SQLite使用的语法稍有不同：

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。

表重命名

rename table MyClass to YouClass;

使索引有用的因素是什么？很简单，就是恰当的排序。