【读书笔记】《SQL必知必会（第5版）》 By：本·福达

此文档是《sql必知必会（第五版）》的读书笔记

本书的代码部分为书籍摘抄与引用；块引用部分为个人理解与总结

◆ 1.1 数据库基础

主键通常定义在表的一列上，但并不是必须这么做
也可以一起使用多个列作为主键
在使用多列作为主键时，上述条件必须应用到所有列，所有列值的组合必须是唯一的

◆ 2.4 检索所有列

但检索不需要的列通常会降低检索速度和应用程序的性能。
使用通配符有一个大优点。由于不明确指定列名（因为星号检索每一列），所以能检索出名字未知的列

◆ 2.5 检索不同的值

办法就是使用DISTINCT关键字，顾名思义，它指示数据库只返回不同的值
DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列

◆ 2.6 限制结果

可以用TOP关键字来限制最多返回多少行
FETCH FIRST 5 ROWS ONLY就会按字面的意思去做的（只取前5行）
如果你使用Oracle，需要基于ROWNUM（行计数器）来计算行
如果你使用MySQL、MariaDB、PostgreSQL或者SQLite，需要使用LIMIT子句，像这样：
LIMIT 5 OFFSET 5指示MySQL等DBMS返回从第5行起的5行数据。第一个数字是检索的行数，第二个数字是指从哪儿开始。
MySQL、MariaDB和SQLite可以把LIMIT 4 OFFSET 3语句简化为LIMIT 3,4。使用这个语法，逗号之前的值对应OFFSET，逗号之后的值对应LIMIT（反着的，要小心）

SQL虽然通常都有相当一致的实现，但你不能想当然地认为它总是这样。
非常基本的语句往往是相通的，但较复杂的语句就不同了

◆ 2.7 使用注释

注释使用--（两个连字符）嵌在行内
——之后的文本就是注释 
注释从/＊开始，到＊/结束，/＊和＊/之间的任何内容都是注释

◆ 3.1 排序数据

关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。
在指定一条ORDER BY子句时，应该保证它是SELECT语句中最后一条子句。如果它不是最后的子句，将会出错。
通常，ORDER BY子句中使用的列将是为显示而选择的列。实际上并不一定要这样，用非检索的列排序数据是完全合法的。

◆ 6.1 LIKE操作符

根据DBMS的不同及其配置，搜索可以是区分大小写的
如果区分大小写，则’fish%’与Fish bean bag toy就不匹配

◆ 6.2 使用通配符的技巧

通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间
不要过度使用通配符
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处
把通配符置于开始处，搜索起来是最慢的
仔细注意通配符的位置

◆ 7.2 拼接字段

将值联结到一起（将一个值附加到另一个值）构成单个值
为正确返回格式化的数据，必须去掉这些空格。这可以使用SQL的RTRIM()函数来完成

◆ 9.1 聚集函数

AVG()只能用值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数
AVG()函数忽略列值为NULL的行
SUM()函数忽略列值为NULL的行

◆ 9.2 聚集不同值

在使用了DISTINCT后，avg_price比较高，原因是什么？
因为有多个物品具有相同的较低价格，排除它们提升了平均价格。

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

◆ 9.3 组合聚集函数

在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名

◆ 10.2 创建分组

GROUP BY子句指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集
GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）
如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名
除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前

 ◆ 10.3 过滤分组

2种条件过滤的差别：WHERE过滤行，而HAVING过滤分组

有关WHERE的所有技术和选项都适用于HAVING，它们的句法是相同的，只是关键字有差别
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
WHERE排除的行不包括在分组中，这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组

◆ 10.4 分组和排序

一般在使用GROUP BY子句时，应该也给出ORDER BY子句
ORDER BY子句是保证数据正确排序的唯一方法
千万不要仅依赖GROUP BY排序数据

◆ 11.2 利用子查询进行过滤

在SELECT语句中，子查询总是从内向外处理

◆ 11.3 作为计算字段使用子查询

要对每个顾客执行COUNT(＊)，应该将它作为一个子查询
如果在SELECT语句中操作多个表，就应使用完全限定列名来避免歧义

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 联结

数据库要满足基本的数据范式（一般数据库的设计满足第三范式即可）

• 范式的好处是：使编程相对简单，数据量更小，更适合放入内存，更新更快，只需更新更少的数据。更少的冗余数据意味着更少地需要GROUP、DISTINCT之类的操作
• 不利之处是查询会变得更加复杂，查询时需要更多连接（JOIN）操作，一些可以复合索引的列由于范式化的需要被分布到了不同的表中，导致索引策略不佳
• 我们日常输出的数据呈现清单报表的时候，又需要一定程度地反范式，通过增加冗余数据或通过分组数据来优化数据库读取性能的过程

关系表的设计就是要把信息分解成多个表，一类数据一个表
各表通过某些共同的值互相关联（所以才叫关系数据库）

◆ 12.2 创建联结

相应的笛卡儿积不是我们想要的。
案例：用每个供应商匹配了每个产品，包括了供应商不正确的产品（即使供应商根本就没有产品）

SQL不限制一条SELECT语句中可以联结的表的数目,创建联结的基本规则也相同
不要联结不必要的表:联结的表越多，性能下降越厉害

◆ 14.2 创建组合查询

UNION指示DBMS执行这两条SELECT语句，并把输出组合成一个查询结果集
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）
如果想返回所有的匹配行，可使用UNION ALL而不是UNION
如果确实需要每个条件的匹配行全部出现（包括重复行），就必须使用UNION ALL，而不是WHERE

◆ 14.4 挑战题

SELECT语句的输出用ORDER BY子句排序
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后
对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况
因此不允许使用多条ORDER BY子句

◆ 15.1 数据插入

将一个新顾客插入到Customers表中。
存储到表中每一列的数据在VALUES子句中给出，必须给每一列提供一个值
如果某列没有值，如cust_contact和cust_email列，则应该使用NULL值（假定表允许对该列指定空值）
各列必须以它们在表定义中出现的次序填充

表名后的括号里明确给出了列名
在插入行时，DBMS将用VALUES列表中的相应值填入列表中的对应项
VALUES中的第一个值对应于第一个指定列名，第二个值对应于第二个列名，如此等等

因为提供了列名，VALUES必须以其指定的次序匹配指定的列名，不一定按各列出现在表中的实际次序
其优点是，即使表的结构改变，这条INSERT语句仍然能正确工作

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

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

如果表的定义允许，则可以在INSERT操作中省略某些列，省略的列必须满足以下某个条件：
    1、该列定义为允许NULL值（无值或空值）
    2、在表定义中给出默认值。这表示如果不给出值，将使用默认值

INSERT还存在另一种形式，可以利用它将SELECT语句的结果插入表中，这就是所谓的INSERT SELECT

◆ 16.2 删除数据

DELETE删除整行而不是删除列
要删除指定的列，请使用UPDATE语句

◆ 16.3 更新和删除的指导原则

在UPDATE或DELETE语句使用WHERE子句前：
应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确

◆ 17.1 创建表

利用CREATE TABLE创建表，必须给出下列信息：
❑ 新表的名字，在关键字CREATE TABLE之后给出；
❑ 表列的名字和定义，用逗号分隔，​​​​​​​（列名在表中必须是唯一的）；
❑ 有的DBMS还要求指定表的位置；

为了防止新表表名已经存在导致的报错，可以在建表示加上if not exists，
写成： 
create table 【if not exists】 
    表名( 字段名 字段类型 【约束】
    ,字段名 字段类型 【约束】
    , ……字段名 字段类型 【约束】 
）

在创建新的表时，指定的表名必须不存在，否则会出错
在创建新的表时，指定的表名必须不存在，否则会出错
主键是其值唯一标识表中每一行的列
只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识
NULL值是没有值，不是空字符串
空字符串是一个有效的值，它不是无值

◆ 17.2 更新表

1、添加列

alter table 表名 add column 列名 类型 【first|after 字段名】;

2、修改列的类型或约束

alter table 表名 modify column 列名 新类型 【新约束】;

3、修改列名

alter table 表名 change column 旧列名 新列名 类型;

4、删除列

alter table 表名 drop column 列名;

5、修改表名

alter table 表名 rename 【to】 新表名;

理想情况下，不要在表中包含数据时对其进行更新
应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动

◆ 17.2 更新表

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

用新的列布局创建一个新表；
使用INSERT SELECT语句，从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
检验包含所需数据的新表；
重命名旧表（如果确定，可以删除它）；
用旧表原来的名字重命名新表；
根据需要，重新创建触发器、存储过程、索引和外键；

数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除它们
类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据

◆ 17.3 删除表

许多DBMS允许强制实施有关规则，防止删除与其他表相关联的表
在实施这些规则时，如果对某个表发布一条DROP TABLE语句，且该表是某个关系的组成部分，则DBMS将阻止这条语句执行，直到该关系被删除为止
如果允许，应该启用这些选项，它能防止意外删除有用的表

◆ 18.1 视图

视图是mysql5.1版本出现的新特性，视图本身是一个虚拟表，它的数据来自于表，通过执行时动态生成

视图的好处是：

        简化sql语句

        提高了sql的重用性

        保护基表的数据，提高了安全性

视图的创建

create view 视图名 as 查询语句;

视图的修改

-- 方式一： 
create or replace view 视图名 as 查询语句;
-- 方式二： 
alter view 视图名 as 查询语句;

视图的删除

drop view 视图1，视图2,...;

视图的查看

desc 视图名; 

show create view 视图名;

视图的使用

插入 insert
修改 update
删除 delete
查看 select

注意：视图一般用于查询的，而不是更新的，注意下面几种不允许更新视图的情况：

1.包含分组函数、group by、distinct、having、union、join
2.常量视图
3.where后的子查询用到了from中的表
4.用到了不可更新的视图

​​​​​​​具备上面几个特点的视图都不允许更新

视图的一些常见应用

❑ 重用SQL语句
❑ 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节
❑ 使用表的一部分而不是整个表
❑ 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限
❑ 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据，创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据 

关于视图创建和使用的一些最常见的规则和限制：

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
对于可以创建的视图数目没有限制。
创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的DBMS中有所不同（嵌套视图可能会严重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试）
许多DBMS禁止在视图查询中使用ORDER BY子句。
有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名
视图不能索引，也不能有关联的触发器或默认值
有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表
有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客。如果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于视图

◆ 18.2 创建视图

SELECT约束全部适用
用视图过滤不想要的数据
在简化计算字段的使用上，视图也特别有用
视图非常容易创建，正确使用视图可极大地简化复杂数据的处理

◆ 18.3 视图小结

视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询
视图提供了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化或保护基础数据