SQL必知必会 笔记

1. 了解SQL

数据库database和数据库管理软件DBMS是有差别的.
用来描述表table或者整个数据库信息的是模式schema.
一个表由一个或多个列column组成. 表中的数据是按行row存储的. 每一行都应该有一列或者几列可以唯一标识自己, 对应的列就是主键primary key.
数据库类型及其名称是SQL不兼容的一个主要原因.
SQL结构化查询语言Structured Query Language, 标准SQL由ANSI标准委员会管理, 因而也称为ANSI SQL, 而各大DBMS厂商有自己的扩展(PL/SQL, Transact-SQL等).

2. 检索数据

SQL语句是不区分大小写的, 但是表名, 列名和值因为DBMS不同可能有所不同, 一般开发人员为了使代码易读和调试通常对SQL关键字使用大写, 对列名和表名使用小写. SQL通常都有相当一致的实现, 但我们不能想当然认为它总是这样的, 非常基本的语句是容易移植的, 而复杂的就不一样了.

一般而言, 除非确实需要表中的每一列, 不然最好别用*通配符, 检索不需要的列通常会降低检索和应用程序的性能.

DISTINCT 关键字作用于所有的列, 不仅仅是跟在其后的那一列.

3. 排序检索数据

关系数据库设计理论认为, 如果不明确规定排序顺序, 则不应该假定检索出的数据的顺序有任何意义.

SQL语句由子句clause构成, 子句有必需和可选, 它通常由一个关键字加上所提供的数据组成. (ORDER BY 子句)

4. 过滤数据

注意: NULL和非匹配
通过过滤选择不包含指定值的所有行时, 你可能希望返回含NULL值的行. 但是这做不到. 因为未知(unknown) 有特殊的含义, 数据库不知道它们是否匹配, 所以在进行匹配过滤或非匹配过滤时, 不会返回这些结果. 也因此过滤数据时一定要验证被过滤列中含NULL的行确实出现在返回的数据中.

5. 高级数据过滤

AND, OR等是能用来联结或改变WHERE子句中的子句的关键字, 也称为逻辑操作符(logical operator).

AND在求值过程中优先级比OR要高, 多操作符组合时, 任何时候都应该使用圆括号进行分组, 它能消除歧义.

IN操作符完成了OR一样的操作, 但好像比OR性能要好?

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

6. 用通配符进行过滤

通配符wildcard是用来匹配值的一部分的特殊字符.
从技术上说LIKE是谓词predicate而不是操作符.
通配符也能放在搜索模式search pattern中间, 比如根据邮件地址一部分查找: ‘b%forta.com’

不要过度使用通配符, 如果其它操作符能达到相同目的, 就使用其它操作符.
把通配符置于开始处搜索起来是最慢的.

注意尾空格: 尾空格可能会干扰通配符匹配. 比如保存词anvil时, 如果它后面有一个或多个空格, 那么子句WHERE prod_name LIKE ‘%anvil’将不会匹配他们. 建议使用函数去掉首尾空格.

注意: %通配符不能匹配NULL.

7. 创建计算字段

作者认为在数据库服务器上完成转换和格式化工作比客户端中完成要快得多. (我自己觉得数据库资源还是比较宝贵的… 如果是IO密集型CPU瓶颈不高感觉这样也好)
使用别名建议还是加AS关键字.

8. 使用函数处理数据

对文本处理, 日期和时间处理的函数各DBMS都很不一致, 但数值处理函数基本一致.

9. 汇总数据

聚集函数(aggregate function): AVG(), COUNT(), MAX(), MIN(), SUM()

COUNT()如果指定列名, 那么会忽略NULL的行, 所以可以使用COUNT(*).

10. 分组数据

目前为止以上的所有计算都是在表的所有数据或匹配待定的WHERE子句的数据上进行的, 如果要返回每个xxx提供的xx数目要怎么办呢? 这时候就可以使用分组了(关键字GROUP BY), 它可以将数据分为多个逻辑组, 对每个组进行聚集计算.

通过HAVING子句来过滤分组(过滤的是分组聚集值, 而不是特定值), WHERE来过滤行.
WHERE在数据分组前进行过滤, HAVING在数据分组后进行过滤.

11. 使用子查询

作为子查询是SELECT语句只能查询单个列.

例: 列出所有包含物品RGAN01的订单的所有顾客ID(利用子查询进行过滤): 

1
2
3
4
5
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');

例: 显示Custemers表中每个顾客的订单总数(作为计算字段): 

1
2
3
4
5
6
7
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

12. 联结表

例: 列出供应商与对应的产品(关系表): 

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

如果联结表时忘记了WHERE子句, 那么就会返回很多数据(笛卡尔积cartesian product也叫叉连接cross join)

目前为止使用的联结称为等值联结(equijoin), 基于两个表之间的相等测试, 也叫内联结(inner join). 下面的SELECT语句返回与上面例子完全相同的数据:

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

注: ANSI SQL规范首选INNER JOIN语法(因为SQL语言纯正者是使用鄙视的眼光看待简单语法的… 看来写个SQL也能写出优越感, 容我笑两分钟). 所以DBMS两种都支持的, 具体使用就看哪个顺手了.

出于性能考虑不要联结不必要的表, 同时注意DBMS中联结表的最大数目.

执行任一给定的SQL操作一般不止一种方法(可以用联结, 可以用子查询等), 不同的方法会受到操作类型, DBMS, 表数据量, 索引或者键等条件的影响, 因此, 有必要试验不同的选择机制, 找出最适合具体情况的方法.

13. 创建高级联结

表别名只在查询执行中使用, 与列别名不一样, 表别名不返回到客户端.

13.1 自联结(self-join)

使用表别名联结两个相同的表.
许多DBMS处理联结远比处理子查询快得多, 所以两种方法都应试试确定哪种性能更好.

13.2 自然联结(natural join)

做为两个表联结的列(联结后不应返回两个一样的列), 应该只出现一次, 所以一般对一个表使用通配符(SELECT *), 然后对其他表的列使用明确的子集来完成.

13.3 外联结(outer join)

联结会包含那些在相关表中没有关联行的行. 左外联结LEFT OUTER JOIN和右外联结RIGHT OUTER JOIN区别在于需要包括哪边表的所有行, 有的DBMS支持全外联结FULL OUTER JOIN.

14. 组合查询

也就是查询多个表, 将结果作为单个查询结果集返回. UNION几乎总是完成WHERE条件相同的工作, 所以它默认取消重复的行, 如果不想取消可以用UNION ALL.

15. 插入数据

插入有几种方式:

  • 插入完整的行
  • 插入行的一部分
  • 插入某些查询的结果(INSERT SELECT或SELECT INTO)

为了DBMS之间的可移植性, 不要忘记使用INTO关键字. 不要使用没有明确给出列的INSERT语句, 给出列能使SQL代码继续发挥作用, 即使表结构发生了变化.

INSERT SELECT可以插入多行, INSERT通常直插入一行.
SELECT INTO用来将数据复制到一个新表.

16. 更新和删除数据

  • 操作表中的特定行
  • 操作表中的所有行

删除某个列的值, 可以设置它为NULL.

无论更新还是删除其实都比较简单, 需要注意的是操作前最好先用SELECT测试一下是否是需要操作的行, 就是注意一下过滤的条件. 还有注意外键. 为了可以执行DELETE注意保留FROM关键字.

DELETE删除整行, UPDATE可以删除整列, 如果想更快的删除表中所有行, 可以使用TRUNCATE TABLE语句.

17. 创建和操纵表

创建表需要参阅具体的DBMS文档.
注: 最好指定NULL / NOT NULL.

对于用于计算或数据分组的列建议用DEFAULT值而不是NULL列.

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
(1) 用新的列布局创建一个新表；
(2) 使用 INSERT SELECT 语句（关于这条语句的详细介绍，请参阅第 15
课）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算
字段；
(3) 检验包含所需数据的新表；
(4) 重命名旧表（如果确定，可以删除它）；
(5) 用旧表原来的名字重命名新表；
(6) 根据需要，重新创建触发器、存储过程、索引和外键。

注: 小心使用ALTER TABLE, 应该在改动前做完整的备份(表结构和数据的备份).

18. 使用视图

有点像是Java中的工具类, 封装一下一些可能被用于固定的, 多次的, 格式化的, 而且变量比较少的SELECT语句, 例如隐藏复杂的SQL联结, 或者减少SQL格式化拼接. 不同的DBMS对视图的限制都可能不大同.

19. 使用存储过程

有点像是开发架构中的Service层, 存储过程可以完成一些需要多条语句才能完成的复杂功能, 它简单, 安全, 高性能.

说明: SQL和其他语言一样也可以写注释, 一般都是推荐写注释的. 所有DBMS都支持的注释是这么写的(两个连接符):

1
-- 注释balabala...

20. 管理事务处理

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

提示: 事务处理用来管理INSERT, UPDATE, DELETE语句, 不能回退SELECT, CREATE, DROP操作.

21. 使用游标

有时候需要在检索出来的行中前进或后退一行或多行, 这就是游标的用途. 通常会经历: 声明, 打开(检索), 关闭.

22. 高级SQL特性

  • 约束
    • 外键约束
    • 唯一约束
    • 检查约束
  • 主键
  • 索引(没有严格的规则要求什么应该索引, 何时索引. 大多数DBMS都提供可以用来确定索引效率的实用程序, 应该经常使用这些实用程序. 定期检查索引. )
  • 触发器(特殊的存储过程. 约束比触发器更快, 尽可能使用约束)