MySQL必知必会

阅读《MySQL必知必会》的一些笔记

1. 基本概念解释

数据库：是一个以某种有组织的方式存储的数据集合
DBMS：数据库管理系统（数据库软件）
表：是一种结构化的文件，一种特定类型的数据的集合（结构化清单）
模式：定义表的结构以及存储的数据该如何表现
列：存储着表中某一部分的信息，把表理解为一个人，列则是这个人的一个描述属性
数据类型：每个表的列都应对应有具体的数据类型，起到优化磁盘、正确排序的作用
行：数据表中的一行，等同于一条数据，一条记录
主键：唯一标识行的一个列或一组列。作为唯一标识它不允许重复，一张表中的每一行的主键都唯一
- 不更新主键列中的值
- 不重用主键列的值
- 不在主键列中使用可能会更改的值
SQL：结构化查询语言（Structured Query Language），用来与数据库进行通信
- SQL不是某个特定的数据库供应商专有的语言。几乎所有的DBMS都支持SQL，所以学习SQL几乎能让你与所有的数据库打交道
- SQL简单易学
- SQL主要在于如何灵活的进行组合使用

2. 使用MySQL

使用 SOURCE C:/xxx目录/populate.sql; 命令去导入数据库文件

在执行任意数据库操作之前，需要先选择一个数据库，使用 use xxx; 命令去选择你要使用的数据库。

使用 SHOW DATABASES; 命令查看所有已创建的数据库。

使用 SHOW TABLES; 查看一个数据库中所有的表。

使用 SHOW COLUMNS FROM 表名; 查看指定表的结构。

使用 DESCRIBE 表名; 也可以查看指定表的结构，是上一个语句的简写。

使用 SHOW STATUS; 用于显示广泛的服务器状态信息。

使用 SHOW CREATE DATABASE; 和 SHOW CREATE TABLE; 分别用来显示创建特定数据库或表的MySQL语句。

使用 SHOW GRANTS; 用来显示授予用户（所有或特定用户）的安全权限。

使用 SHOW ERRORS; 和 SHOW WARNINGS; 用来显示服务器错误或警告消息

自动增量：设置某一列为自增长 ===> auto increment

3. 检索数据

使用 SELECT 检索数据，抓住两个点：想选择什么以及从哪里选择

SELECT prod_name FROM products; # 上述语句利用SELECT语句从products表中检索一个名为prod_name的列
SELECT prod_name, prod_id FROM products; # 检索多个列，最后一个列名不能加逗号
SELECT * FROM products; # 检索所有的列

注意：

未排序数据

结束SQL语句要使用分号

SQL语句不区分大小写。但建议关键字大写。易于阅读和理解。

使用空格或换行组织 SQL 语句，更易于阅读理解。

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

检索未知列使用通配符有一个大优点。由于不明确指定列名（因为星号检索每个列），所以能检索出名字未知的列。

3.1 DISTINCT关键字

此关键字指示MySQL只返回不同的值

DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被检索出来。

返回指定字段下数据表中该字段的所有值并去重。

-- SELECT DISTINCT vend_id告诉MySQL只返回不同（唯一）的vend_id行，因此只返回4行，如下面的输出所示。如果使用DISTINCT关键字，它必须直接放在列名的前面。
SELECT DISTINCT vend_id FROM products;

4. 排序检索数据

如何使用SELECT语句的ORDER BY子句，根据需要排序检索出的数据。

关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。子句（clause） SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句的FROM子句。

为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字，据此对输出进行排序。

指示MySQL对prod_name列以字母顺序排序数据的ORDER BY子句：

select prod_name from products order by prod_name;

4.1 按多个列排序

如下示例指示价格的排序优先级更高，如果价格相同，再根据名字进行排序：

select prod_id, prod_price, prod_name from products order by prod_price, prod_name;

重要的是理解在按多个列排序时，排序完全按所规定的顺序进行。换句话说，对于上述例子中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

4.2 指定排序方向

数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序，还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。如下按价格以降序排序产品（最贵的排在最前面）：

select prod_id, prod_name, prod_price from products order by prod_price desc;

下面的例子以降序排序产品（最贵的在最前面），然后再对产品名排序：

select prod_id, prod_name, prod_price from products order by prod_price desc, prod_name;

DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序。

如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

与DESC相反的关键字是ASC（ASCENDING），在升序排序时可以指定它。但实际上，ASC没有多大用处，因为升序是默认的（如果既不指定ASC也不指定DESC，则假定为ASC）。

在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL（和大多数数据库管理系统）的默认行为。

使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值。下面的例子演示如何找出最昂贵物品的值：

select prod_id, prod_name, prod_price from products order by prod_price desc limit 1;

ORDER BY子句的位置在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。

ORDER BY 位于 FROM 之后，LIMIT 位于 ORDER BY 之后。

5. 过滤数据

如何使用SELECT语句的WHERE子句指定搜索条件。

WHERE 字句在表名（FROM 子句）之后给出，如下所示：

select prod_name, prod_price from products where prod_price = 2.50;

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

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

5.1 WHERE 子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
大于
>=	大于等于
BETWEEN	在指定的两个值之间

select prod_name, prod_price from products where prod_name = 'fuses';

通过如上语句进行查询，我们可以发现它在查询时不区分字母的大小写。

列出价格小于10美元的所有产品：

select prod_name, prod_price from products where prod_price < 10;

列出价格小于等于10美元的所有产品：

select prod_name, prod_price from products where prod_price <= 10;

列出不是由供应商1003制造的所有产品：

select vend_id, prod_name from products where vend_id <> 1003;
select vend_id, prod_name from products where vend_id != 1003;

何时使用引号： 如果仔细观察上述WHERE子句中使用的条件，会看到有的值括在单引号内（如前面使用的’fuses'），而有的值未括起来。单引号用来限定字符串。如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

BETWEEN操作符，它需要两个值，即范围的开始值和结束值，BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。检索价格在5美元和10美元之间的所有产品：

select prod_price, prod_name from products where prod_price between 5 and 10;

在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为包含空值NULL。它与字段包含0、空字符串或仅仅包含空格不同。

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是ISNULL子句。其语法如下：

select prod_name from products where prod_price is null;
select cust_id from customers where cust_email is null;

5.2 组合 WHERE 字句

以AND子句的方式或OR子句的方式使用。OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行。通过不止一个列进行过滤，可使用AND操作符给WHERE子句附加条件。

select prod_id, vend_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10;

此SQL语句检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格。

select prod_id, vend_id, prod_price, prod_name from products where vend_id = 1003 or vend_id = 1002;

AND 操作符的优先级大于 OR

select prod_id, vend_id, prod_price, prod_name from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;

请看上面的结果。返回的行中有两行价格小于10美元，显然，返回的行未按预期的进行过滤。为什么会这样呢？原因在于计算的次序。SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。当SQL看到上述WHERE子句时，它理解为由供应商1003制造的任何价格为10美元（含）以上的产品，或者由供应商1002制造的任何产品，而不管其价格如何。换句话说，由于AND在计算次序中优先级更高，操作符被错误地组合了。

解决方法是使用圆括号明确地分组相应的操作符：

select prod_id, vend_id, prod_price, prod_name from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;

5.3 IN操作符

圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。下面的例子说明了这个操作符：

select prod_id, vend_id, prod_price, prod_name from products where vend_id in (1002, 1003);
select prod_id, vend_id, prod_price, prod_name from products where vend_id = 1002 or vend_id = 1003 order by prod_name; # 效果于上一句相同

此SELECT语句检索供应商1002和1003制造的所有产品。IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中。

为什么要使用IN操作符？其优点具体如下。

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

5.4 NOT操作符

否定它之后所跟的任何条件。

列出除1002和1003之外的所有供应商制造的产品，可编写如下的代码：

select prod_id, vend_id, prod_price, prod_name from products where vend_id not in(1002, 1003) order by prod_name;

MySQL中的NOT MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

6. 用通配符进行过滤

利用通配符可创建比较特定数据的搜索模式：通配符是用来匹配值的一部分的特殊字符，为在搜索子句中使用通配符，必须使用LIKE操作符。

6.1百分号（%）

找出所有以词jet起头的产品，可使用以下SELECT语句：

select prod_id, prod_name from products where prod_name like 'jet%';

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

查找产品名中含有指定字符anvil的产品：

select prod_id, prod_name from products where prod_name like '%anvil%';

找出以s起头以e结尾的所有产品：

select prod_id, prod_name from products where prod_name like 's%e';

注意尾空格 尾空格可能会干扰通配符匹配。例如，在保存词anvil时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE '%anvil’将不会匹配它们，因为在最后的 l 后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。

注意NULL 虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE '%’也不能匹配用值NULL作为产品名的行。

6.2 下划线（_）

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

select prod_id, prod_name from products where prod_name like '_ ton anvil';
select prod_id, prod_name from products where prod_name like '% ton anvil';

根据两个语句的对比结果可以发现，_ 通配符只返回两条语句，因为它只能匹配单个字符

6.3 使用技巧

通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

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

7. 使用正则表达式进行过滤

正则表达式是用来匹配文本的特殊的串（字符集合）。MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。

仅为正则表达式语言的一个子集： 如果你熟悉正则表达式，需要注意：MySQL仅支持多数正则表达式实现的一个很小的子集。

7.1 基本字符匹配

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

select prod_name from products where prod_name regexp '1000' order by prod_name;

它告诉MySQL:REGEXP后所跟的东西作为正则表达式（与文字正文1000匹配的一个正则表达式）处理。

select prod_name from products where prod_name regexp '.000' order by prod_name;

．是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符，因此，1000和2000都匹配且返回。

select prod_name from products where prod_name like '1000' order by prod_name;
select prod_name from products where prod_name regexp '1000' order by prod_name;
# 这两个语句的差别就是：like关键字匹配整个列，而regexp匹配的是有出现过该值的列

MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

7.2 进行OR匹配

为搜索两个串之一（或者为这个串，或者为另一个串），使用|，如下所示：

select prod_name from products where prod_name regexp '1000|2000' order by prod_name;

7.3 匹配几个字符之一

select prod_name from products where prod_name regexp '[123] ton' order by prod_name;
# 这两个语句不相等
select prod_name from products where prod_name regexp '1|2|3 ton' order by prod_name;
# 这个语句表示要检索的列的内容含有 1 或 2 或 3 ton 的列。

[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回（没有3 ton）。正如所见，[]是另一种形式的OR语句。字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可。因此，尽管[123]匹配字符1、2或3，但[^123]却匹配除这些字符外的任何东西。

7.4 匹配范围

集合可用来定义要匹配的一个或多个字符。例如，下面的集合将匹配数字0到9：[0123456789]为简化这种类型的集合，可使用-来定义一个范围。下面的式子功能上等同于上述数字列表：[0-9]范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范围不一定只是数值的，[a-z]匹配任意字母字符。

检索产品名称中带有一到五的数字加一个空格和Ton三个字母的产品：

select prod_name from products where prod_name regexp '[1-5] Ton';

7.5 匹配特殊字符

为了匹配特殊字符，必须用\\为前导。\\-表示查找-, \\.表示查找．。

select prod_name from products where prod_name regexp '\\.';

正则表达式内具有特殊意义的所有字符都必须以这种方式转义

为了匹配反斜杠（\）字符本身，需要使用\\。

多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）。

7.6 匹配字符类

预定义的字符集，也称为字符类：

7.7 匹配多个实例

通过重复元字符来完成任务：

select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)';

正则表达式\([0-9] sticks? \)需要解说一下。\(匹配), [0-9]匹配任意数字（这个例子中为1和5）, sticks？匹配stick和sticks（s后的？使s可选，因为？匹配它前面的任何字符的0次或1次出现）, \)匹配)。没有？，匹配stick和sticks会非常困难。

匹配连在一起的4位数字：

select prod_name from products where prod_name regexp '[[:digit:]]{4}';

[:digit:]匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符（任意数字）出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字。

7.8 定位符

匹配特定位置的文本，需要使用定位符。

select prod_name from products where prod_name regexp '^[[:digit:]\\.]';
select prod_name from products where prod_name regexp '^[0-9\\.]';
# 如下语句匹配非数字和点开头的产品
select prod_name from products where prod_name regexp '^[^[:digit:]\\.]';

^匹配串的开始。因此，^[0-9\\.]只在．或任意数字为串中第一个字符时才匹配它们。没有^，则还要多检索出4个别的行（那些中间有数字的行）。

使用以下数据测试正则表达式： select 'artsmp' regexp 'a';

^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处

MySQL入门到入坟（一）