SQL基础笔记

随手笔记。
参考资料：《SQL必知必会（第4版）》Ben Forta

第1课 了解SQL

数据库中没有其他表具有相同的名字

虽然在相同数据库中不能两次使用相同的表名，但在不同的数据库中完全可以使用相同的表名

第2课 检索数据

SELECT检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择

多条SQL语句必须以分号（;）分隔

SQL语句不区分大小写

但是表名、列名和值可能有所不同（这有赖于具体的DBMS及其如何配置

多个列时，一定要在列名之间加上逗号，但最后一个列名后不加。如果在最后一个列名后加了逗号，将出现错误

SELECT语句还可以检索所有的列而不必逐个列出它们。在实际列名的位置使用星号（＊）通配符可以做到这点

检索不需要的列通常会降低检索和应用程序的性能

不明确指定列名（因为星号检索每一列），所以能检索出名字未知的列。

DISTINCT关键字，顾名思义，它指示数据库只返回不同的值

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

为了得到后面的5行数据，需要指定从哪儿开始以及检索的行数

LIMIT带的OFFSET指定从哪儿开始。

第一个被检索的行是第0行，而不是第1行。因此，LIMIT 1 OFFSET 1会检索第2行，而不是第1行。

SQL虽然通常都有相当一致的实现，但你不能想当然地认为它总是这样

注释使用–（两个连字符）嵌在行内

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

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

第3课 排序检索数据

如果不排序，数据一般将以它在底层表中出现的顺序显示，这有可能是数据最初添加到表中的顺序。

SQL语句由子句构成，有些子句是必需的，有些则是可选的

指定一条ORDER BY子句时，应该保证它是SELECT语句中最后一条子句

首先按价格，然后按名称排序。

ORDER BY 2表示按SELECT清单中的第二个列prod_price进行排序。ORDER BY 2, 3表示先按prod_price，再按prod_name进行排序

好处在于不用重新输入列名

在对SELECT清单进行更改时容易错误地对数据进行排序（忘记对ORDER BY子句做相应的改动）

为了进行降序排序，必须指定DESC关键字。

DESC关键字只应用到直接位于其前面的列名

必须对每一列指定DESC关键字。

DESC是DESCENDING的缩写

第4课 过滤数据

数据根据WHERE子句中指定的搜索条件进行过滤

往往是因为DBMS指定了所使用的数据类型及其默认行为

而让客户端应用（或开发语言）处理数据库的工作将会极大地影响应用的性能

order by 应该放在select语句的最后

应该让ORDER BY位于WHERE之后

应该让ORDER BY位于WHERE之后

并非所有DBMS都支持这些操作符

单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

！=和＜>通常可以互换。但是，并非所有DBMS都支持这两种不等于操作符。例如，Microsoft Access支持＜>而不支持！=

可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句

DBMS扩展了标准的操作符集，提供了更高级的过滤选择

因为未知（unknown）有特殊的含义，数据库不知道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果

第5课 高级数据过滤

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件

在处理OR操作符前，优先处理AND操作符

解决方法是使用圆括号对操作符进行明确分组

任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配

IN操作符完成了与OR相同的功能

NOT关键字可以用在要过滤的列前，而不仅是在其后。

也可以使用＜>操作符来完成

但在更复杂的子句中，NOT是非常有用的。例如，在与IN操作符联合使用时，NOT可以非常简单地找出与条件列表不匹配的行。

第6课 用通配符进行过滤

利用通配符，可以创建比较特定数据的搜索模式

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较

%表示任何字符出现任意次数

%告诉DBMS接受Fish之后的任意字符，不管它有多少字符。

根据DBMS的不同及其配置，搜索可以是区分大小写的。

许多DBMS都用空格来填补字段的内容

简单的解决办法是给搜索模式再增加一个%号：'F%y%'还匹配y之后的字符（或空格）

WHERE prod_name LIKE '%’不会匹配产品名称为NULL的行。

下划线的用途与%一样，但它只匹配单个字符，而不是多个字符

与%能匹配多个字符不同，_总是刚好匹配一个字符，不能多也不能少

并不是所有DBMS都支持用来创建集合的[]

即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间

在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的

第7课 创建计算字段

我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化

计算字段是运行时在SELECT语句内创建的。

concat

在SQL中的SELECT语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的DBMS，此操作符可用加号（+）或两个竖杠（||）表示。在MySQL和MariaDB中，必须使用特殊的函数

在SQL中的SELECT语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的DBMS，此操作符可用加号（+）或两个竖杠（||）表示。在MySQL和MariaDB中，必须使用特殊的函数

RTRIM()函数去掉值右边的所有空格

AS vend_title。它指示SQL创建一个包含指定计算结果的名为vend_title的计算字段

但是省略了FROM子句后就是简单地访问和处理表达式，例如SELECT 3 ＊2；将返回6, SELECT Trim(’ abc ');将返回abc, SELECTNow()；使用Now()函数返回当前日期和时间。

第8课 使用函数处理数据

每一个DBMS都有特定的函数。事实上，只有少数几个函数被所有主要的DBMS等同地支持。

DBMS函数的差异[插图]

UPPER()将文本转换为大写

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

因为Michael Green和Michelle Green发音相似，所以它们的SOUNDEX值匹配

大多数DBMS具有比较日期、执行基于日期的运算、选择日期格式等的函数。

常用数值处理函数[插图]

第9课 汇总数据

需要汇总表中的数据，而不需要实际数据本身

SQL聚集函数[插图]

为了获得多个列的平均值，必须使用多个AVG()函数。

AVG()函数忽略列值为NULL的行。

COUNT()确定表中行的数目

COUNT()函数有两种使用方式：□ 使用COUNT(＊)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。□ 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

如果指定列名，则COUNT()函数会忽略指定列的值为空的行，但如果COUNT()函数中用的是星号（＊），则不忽略。

MAX()要求指定列名

在用于文本数据时，MAX()返回按该列排序后的最后一行。

SUM()用来返回指定列值的和（总计）

只包含不同的值，指定DISTINCT参数

第10课 分组数据

分组是使用SELECT语句的GROUP BY子句建立的。

GROUP BY子句指示DBMS按vend_id排序并分组数据。这就会对每个vend_id而不是整个表计算num_prods一次。

WHERE过滤指定的是行而不是分组

目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是，WHERE过滤行，而HAVING过滤分组。

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

ORDER BY与GROUP BY[插图]

SELECT子句及其顺序[插图]

第11课 使用子查询

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。也可以使用子查询来把3个查询组合成一条语句。

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

在WHERE子句中使用子查询能够编写出功能很强且很灵活的SQL语句。

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

子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名，而不只是列名（cust_id）。它指定表名和列名

下面的WHERE子句告诉SQL，比较Orders表中的cust_id和当前正从Customers表中检索的cust_id：[插图]

如果不采用完全限定列名，DBMS会认为要对Orders表中的cus

第12课 联结表

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

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

因此，关系数据库的可伸缩性远比非关系数据库要好。可伸缩（scale）能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好

联结是一种机制，用来在一条SELECT语句中关联表

创建联结非常简单，指定要联结的所有表以及关联它们的方式即可

与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表：Vendors和Products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确地联结

在引用的列可能出现歧义时，必须使用完全限定列名（用一个句点分隔表名和列名）

检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

要保证所有联结都有WHERE子句，否则DBMS将返回比想要的数据多得多的数据

返回笛卡儿积的联结，也称叉联结（cross join）

等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）

但FROM子句不同。这里，两个表之间的关系是以INNER JOIN指定的部分FROM子句。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

不要联结不必要的表。联结的表越多，性能下降越厉害。

第13课 创建高级联结

SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做有两个主要理由：□ 缩短SQL语句；□ 允许在一条SELECT语句中多次使用相同的表

此查询中需要的两个表实际上是相同的表，因此Customers表在FROM子句中出现了两次。虽然这是完全合法的，但对Customers的引用具有歧义性，因为DBMS不知道你引用的是哪个Customers表。解决此问题，需要使用表别名。Customers第一次出现用了别名c1，第二次出现用了别名c2。

MySQL这个例子用子查询耗时0.002秒，用联结查耗时0.001秒

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然联结排除多次出现，使每一列只返回一次

自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT ＊），而对其他表的列使用明确的子集来完成。

联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。

这条SELECT语句使用了关键字OUTER JOIN来指定联结类型（而不是在WHERE子句中指定）

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）

就是全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行

Access、MariaDB、MySQL、Open Office Base和SQLite不支持FULL OUTERJOIN语法。

应该总是提供联结条件，否则会得出笛卡儿积。

第14课 组合查询

SQL也允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询

UNION指示DBMS执行这两条SELECT语句，并把输出组合成一个查询结果集。

但对于较复杂的过滤条件，或者从多个表（而不是一个表）中检索数据的情形，使用UNION可能会使处理更简单。

UNION中的每个查询必须包含相同的列、表达式或聚集函数

类型不必完全相同，但必须是DBMS可以隐含转换的类型

UNION从查询结果集中自动去除了重复的行

，如果想返回所有的匹配行，可使用UNION ALL而不是UNION。

如果确实需要每个条件的匹配行全部出现（包括重复行），就必须使用UNION ALL，而不是WHERE。

第15课 插入数据

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

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

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

这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，DBMS一点儿也不关心SELECT返回的列名。它使用的是列的位置

INSERT SELECT中SELECT语句可以包含WHERE子句

与INSERT SELECT将数据添加到一个已经存在的表不同，SELECT INTO将数据复制到一个新表

在使用SELECT INTO时，需要知道一些事情：□ 任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY；□ 可利用联结从多个表插入数据；□ 不管从多少个表中检索数据，数据都只能插入到一个表中

第16课 更新和删除数据

更新（修改）表中的数据，可以使用UPDATE语句

UPDATE语句总是以要更新的表名开始

SET命令用来将新值赋给被更新的列

UPDATE语句以WHERE子句结束，它告诉DBMS更新哪一行。没有WHERE子句，DBMS将会用这个电子邮件地址更新Customers表中的所有行

要删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。

从一个表中删除（去掉）数据，使用DELETE语句。有两种使用DELETE的方式：□ 从表中删除特定的行；□ 从表中删除所有行。

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

如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句

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

第17课 创建和操纵表

一般有两种创建表的方法：□ 多数DBMS都具有交互式创建和管理数据库表的工具；□ 表也可以直接用SQL语句操纵。

不同DBMS的CREATE TABLE的语法有所不同，这个简单脚本也说明了这一点。这条语句在Oracle、PostgreSQL、SQL Server和SQLite中有效，而对于MySQL, varchar必须替换为text；对于DB2，必须从最后一列中去掉NULL。

允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值。

每个表列要么是NULL列，要么是NOT NULL列

NULL为默认设置，如果不指定NOT NULL，就认为指定的是NULL。

默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定。

MySQL用户指定DEFAULT CURRENT_DATE(),Oracle用户指定DEFAULT SYSDATE，而SQL Server用户指定DEFAULTGETDATE()。遗憾的是，这条获得系统日期的命令在不同的DBMS中几乎都是不同的。

更新表定义，可以使用ALTER TABLE语句。

应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。

使用ALTER TABLE要极为小心，应该在进行改动前做完整的备份（表结构和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

DB2、MariaDB、MySQL、Oracle和PostgreSQL用户使用RENAME语句，SQLServer用户使用sp_rename存储过程，SQLite用户使用ALTER TABLE语句

第18课 使用视图

视图是虚拟的表。

重要的是，要知道视图仅仅是用来查看存储在别处数据的一种设施。

许多DBMS禁止在视图查询中使用ORDER BY子句。

删除视图，可以使用DROP语句，其语法为DROP VIEW viewname;

覆盖（或更新）视图，必须先删除它，然后再重新创建。

视图的另一常见用途是重新格式化检索出的数据

第19课 使用存储过程

可以创建存储过程。简单来说，存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批文件，

执行存储过程的SQL语句很简单，即EXECUTE。EXECUTE接受存储过程名和需要传递给它的任何参数。

要保证恰当地生成此ID，最好是使生成此ID的过程自动化（而不是依赖于最终用户的输入）

第20课 管理事务处理

事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）

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

也不能回退CREATE或DROP操作

管理事务的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退

BEGIN TRANSACTION和COMMIT TRANSACTION语句之间的SQL必须完全执行或者完全不执行。

多数实现没有明确标识事务处理在何处结束。事务一直存在，直到被中断。通常，COMMIT用于保存更改，ROLLBACK用于撤销，

SQL的ROLLBACK命令用来回退（撤销）SQL语句

即提交（写或保存）操作是自动进行的。在事务处理块中，提交不会隐式进行。

最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交。

在SQL中，这些占位符称为保留点。在MariaDB、MySQL和Oracle中创建占位符，可使用SAVEPOINT语句

每个保留点都要取能够标识它的唯一名字，以便在回退时，DBMS知道回退到何处。

第21课 使用游标

有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。

第22课 高级SQL特性

添加关键字PRIMARY KEY，使其成为主键