1. 了解SQL

1.1 数据库基础

数据库的定义：以某种有组织的方式存储的数据集合。
表的定义：一种结构化的文件，可以用来存储某种特定类型的数据。
表的模式：定义了数据在表中如何存储。
列：表中的一个字段，所有的表都是由一个或多个列组成的。
行：表中的一条记录。
主键（primary key）：一列或一组列，其值能够唯一区分表中的每个行。

作为主键的条件：任意两行的都不具有相同的主键；每一行都必须具有一个主键（主键不能是NULL）。

主键的好习惯：

不更新主键列中的值。
不重用主键列的值。
不在主键列中使用可能会更改的值。

1.2 什么是SQL

SQL的定义：是结构化查询语言（Structured Query Language）的缩写。SQL是一种专门用来与数据库通信的语言。
SQL的优点：

通用性。几乎所有重要的DBMS都支持SQL。
简单易学。
强有力。灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

2. MySQL简介

2.1 什么是MySQL

MySQL：是一种数据库管理系统（DBMS），即它是一种数据库软件。
DBMS可以分为两类：

基于共享文件系统的。一般用于桌面用途，通常不用于更高端的应用。
基于客户机-服务器的。MySQL，Oracle，SQL Sever等。

客户机-服务器应用分为两个不同的部分。服务器部分是负责所有数据访问和处理的一个软件。这个软件运行在称为数据库服务器的计算机上。与数据文件打交道的只有服务器软件。关于数据的所有请求都由服务器软件完成。而这些请求来自于运行客户机软件的计算机。客户机是与用户打交道的软件。

客户机软件和服务器软件可能安装在两台计算机上，也可能安装在同一台计算机上。不管是否在两台计算机上，为进行数据库交互，客户机软件和服务器软件都要进行通信。

服务器软件为MySQL DBMS。可以在本地安装，也可以连接到远程MySQL服务器上。
客户机可以是MySQL提供的工具、脚本语言、Web应用开发语言、程序设计语言等。

2.2 MySQL工具

MySQL命令行工具。
MySQL Administrator图形交互客户机，用来简化MySQL服务器的管理。
MySQL Query Browser图形交互客户机，用来编写和执行MySQL命令。

3. 使用MySQL

3.1 连接

连接到数据库：主机名端口用户名用户口令（如果需要）。

3.2 选择数据库

最初连接到MySQL的时候，没有任何数据库打开供你使用。需要用USE关键字选择一个数据库。

USE mysql;

必须先打开数据库才能读取其中的数据。

3.3 了解数据库和表

如果不知道可以使用的数据库的名字可以用SHOW关键字来显示可用的数据库。

SHOW DATABASES

这条命令返回的就是可用的数据库。
为了获得一个数据库内的表的列表，可以使用：

SHOW TABLES;

SHOW关键字还可以用来显示一个表里的所有列：

SHOW COLUMNS
FROM user;

除了使用SHOW COLUMNS FROM来显示一个表的所有列的信息，还可以用DESCRIBE语句来实现。

DESCRIBE user;

SHOW关键字还支持一下用法：

SHOW STATUS，用于显示广泛的服务器状态。
SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创建特定数据或者表。
SHOW GRANTS，用来显示授权用户的安全权限。
SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告。

4. 检索数据库

4.1 SELECT语句

最常用SQL语句就是SELECT语句，它用来从一个或者多个表中检索信息。为了检索表的数据，必须至少给出两条信息--想选择什么，以及从什么地方选择。

4.2 检索单个列

从user表中检索出host列。

SELECT host
FROM user;

未排序数据：如果没有明确排序查询结果，则返回的数据的顺序没有特殊意义。返回的数据顺序有可能是数据被添加到表中的数据，也可能不是。

4.3 检索多个列

在SELECT关键字给出要检索的多个列的列名，并用逗号分隔。

SELECT host,user
FROM user;

数据表示：SQL语句一般返回原始的、无格式的数据。数据格式化是一个表示问题，而不是一个检索问题。因此表示经常在显示数据的应用程序中规定。很少使用实际检索出的原始数据。

4.4 检索所有列

使用通配符（*）来代表所有的列：

SELECT *
FROM user;

使用通配符：一般除你确实需要表中的每个列，否则最好不要使用通配符。虽然通配符可能会使你自己省事，但检索不需要的列通常会降低检索和应用程序的性能。

4.5 检索不同的行

SELECT返回所有匹配的行，但是如果不想每个值重复出现可以使用DISTINCT关键字。

SELECT DISTINCT user
FROM user;

如果使用关键字DISTINCT，它必须直接放在列名的前面。DISTINCT关键字应用于所有列而不仅是前置它的列。

4.6 限制结果

SELECT语句返回匹配的所有行，为了返回第一行或者前几行，可使用LIMIT关键字。

SELECT user
FROM user LIMIT 2;

为了得到下一个2行，可指定要开始的行和行数。

SELECT user
FROM user LIMIT 2,2;

4.7 使用完全限定的表名

迄今为止使用的SQL例子只通过列名引用列。除此之外，也可使用完全限定名来引用列或表。

SELECT user.user
FROM mysql.user;

5. 排序检索数据

5.1 排序数据

其实检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般以它在底层表中出现的顺序线束。这可以是数据最初被添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响。因此，如果不明确控制的话，不能依赖改排序顺序。关系数据库设计理论认为，如果不能明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。
子句（clause）：SQL语句是由子句构成的，有些子句是必需的，而有些是可选的。一个子句通常由一个关键字和所提供的的数据组成。
为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句根据一个列或几个列对输出进行排序。

SELECT user
FROM user
ORDER BY user;

允许使用非选择类进行排序。

5.2 按多个列排序

按多个列排序的时候，排序完全按规定的顺序进行。先按第一个给出的列排序，第一个相同的行再按第二个列排序。

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

5.3 指定排序方向

数据排序默认使用升序，但是也可以用DESC关键词来指定为降序：

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

当用多个列排序的时候，只对其中一列按降序排序的话，就只需要在该列后面加上DESC，对其他列没有影响。

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

如果要对多个列都指定用降序排序，就需要在每一列的后面加上关键词DESC。
使用ORDER BY和LIMIT的组合可以所引出一个列中的最高值或最低值。

SELECT prod_price
FROM products
ORDER BY prod_price DEC
LIMIT 1;

6. 过滤数据

数据库一般包含大量的数据，很少需要检索表中所有行。通常可能需要的只是一个子集，只检索所需要的数据需要指定搜索条件，也称为过滤条件。在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

SQL过滤与应用过滤：数据也可以在应用层过滤。SQL的SELECT语句为客户机应用检索出超出实际所需的数据，然后客户机代码对返回的数据进行循环，以提取出需要的行。但是这种实现并不令人满意。因此数据库进行了优化，以便快速有效地对数据进行过滤。让客户机应用（或开发语言）处理数据库的工作将会及大地影响应用的性能，并且使所创建的应用完全不具备可伸缩性。此外，如果在客户机上过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费。

WHERE子句的位置：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE子句之后，否则将会产生错误。

6.2 WHERE子句操作符

除了高级语言支持的比较操作符(=, !=, >, >=, <, <=)外，还支持BETWEEN过滤操作。

SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';

匹配fuses时不区分大小写，因此Fuses与上述语句匹配。
列出价格小于10美元的所有商品：

SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;

检索出不是由供应商1003制造的所有商品：

SELECT vend_id, prod_name
FROM products
WHERE vend_id != 1003;

何时使用引号：当要比较的列的值是字符串就用单引号，是数值就不需要引号。
检索价格在5美元和10美元之间的所有商品：

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，称为包含空值NULL。SELECT语句有一个特殊的WHERE子句IS NULL用来检查具有NULL值的列。

SELECT prod_name
FROM products
WHERE prod_price IS NULL;

NULL与不匹配：在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行。但是，不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。所以，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL行。

7. 数据过滤

7.1 组合WHERE子句

MySQL允许给出多个WHERE子句，通过AND或OR组合使用。
检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格：

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

检索由供应商1002或者1003供应的商品名和价格：

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;

计算次序：默认是AND的优先级高于OR，实际中尽量使用括号。

7.2 IN操作符

完成与OR相同的功能。但是具有以下4个优点：

在使用长的合法选项清单时，IN操作符的语法更清楚更直观。
在使用IN时，计算次序更容易管理。
IN操作符一般比OR操作符更快。
IN最大的优点是可以包含其他SELECT语句，使得能够动态地建立WHERE子句。

7.3 NOT操作符

取反操作。列出除了1002和1003之外所有供应商制造的产品：

SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN(1002, 1003);

为什么使用NOT？对于简单的WHERE子句，使用NOT确实没有什么优势。但是在更复杂的子句中，NOT是非常有用的。
MySQL中的NOT：MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

8. 用通配符进行过滤

8.1 LIKE操作

前面介绍的所有操作符都是针对已知值进行过滤的。但是这种方法不是任何时候都好用。例如，怎样搜索产品名中包含文本anvil的所有产品？用简单的比较操作符肯定是不行的，必须使用通配符。利用通配符可以创建比较特定数据的搜索模式。
通配符：用来匹配值的一部分的特殊字符。
搜索模式：有字面值、通配符或两者组合构成的搜索条件。
在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL后跟的搜索模式利用通配符而不是直接相等匹配进行比较。
谓词：操作符何时不是操作符？答案是在它作为谓词时。从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，但应该对此术语有所了解。
百分号（%）通配符：最常使用的通配符。在搜索串中，%表示任何字符出现任意次数。
索引所有以词jet起头的产品：

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

区分大小写：根据MySQL的配置方式，搜索可以是区分大小写的。
通配符可以在搜索模式的任意位置使用，并且可以使用多个通配符。
匹配产品名中包含anvil的所有产品：

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil%';

重要的是要注意到，除了匹配一个或多个字符外，%还能匹配0个字符。
注意尾空格：尾空格可能会干扰通配符匹配。例如，在保存词anvil时，如果它后面多了一个或者多个空格时，则子句WHERE prod_name LIKE '%anvil'将不会匹配它们，因为最后的l后面有多的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%，一个更好的办法是使用函数去掉尾空格。
注意NULL：虽然%通配符貌似可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配值为NULL作为产品名的行。
下划线（_）通配符：用途与%相同，但是只能匹配单个字符而不是多个字符。下划线只能匹配一个字符，不能多也不能少。

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

8.2 使用通配符技巧

正如所见，通配符很有用。但是这种功能是有代价的：通配符搜索的处理一般比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧：

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实使用通配符时，除非绝对必要，否则不要把他们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

9. 用正则表达式进行搜索

9.1 正则表达式介绍

正则表达式是用来匹配文本的特殊的串（字符集合）。

9.2 使用MySQL正则表达式

MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式过滤SELECT检索出的数据。
MySQL仅支持多数正则表达式实现的一个很小的子集。

基本字符匹配

检索prod_name包含文本1000的所有行：

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

除了关键字LIKE被REGEXP替代外，这条语句看上去非常像LIKE语句。REGEXP告诉MySQL其后所跟着的东西作为正则表达式处理。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

LIKE和REGEXP的区别：LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不会返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。
匹配不区分大小写：MySQL中的正则表达式匹配不区分大小写。为区分大小写，可以使用BINARY关键字。

进行OR匹配

为搜索两个串之一，使用|。索引产品名中包含1000或者2000的产品：

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

匹配几个字符之一

匹配任意的单一字符可以用.来实现。但是，如果只想匹配特定的字符，可以通过指定一组用[]括起来的字符来完成。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

[]是另一种形式的OR语句。事实上，正则表达式[123] Ton为[1|2|3] Ton的缩写，也可使用后者。但是需要用[]来定义OR语句查找什么。
字符集合也可以被否定，即匹配除了指定字符外的任何东西。为否定一个字符集，在集合的开始位置放置一个^即可。因此[123]匹配字符1,2或3，[^123]匹配除这些字符外的任何东西。

匹配范围

集合可以用来定义要匹配的一个或多个字符。[0123456789]等同于[0-9]。范围不限于完整的集合[0-3]和[6-9]也是合法的范围。范围不仅仅是数值，[a-z]匹配任意字母字符。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

匹配特殊字符

MySQL中正则表达式的特殊字符需要前面加\进行转义，如\-或\.等。
多数正则表达式实现使用单个反斜杠转义特殊字符。但是MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）。

匹配字符类

为更方便的工作，可以使用预定义的字符集，称为字符类。

类	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\t]）
[:cntrl:]	ACSII控制字符
[:digit:]	任意数字（同[0-9]）
[:graph:]	任意可打印字符，但是不包括空格
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意字符
[:upper:]	任意大写字母（同[A-Z]）
[:xdigit:]	任意16进制数字（同[a-fA-F0-9]）

匹配多个实例

目前为止使用的所有正则表达式都试图匹配单次出现。但有时需要对匹配的数目进行更强的控制。

《MySQL必知必会》笔记