old - 数据库基础知识 - 《MySQL 学习笔记》

1 基本术语
- 1 数据库相关术语
- 2 数据库表相关术语
2 使用MySQL
- 连接MySQL
- SQL语句
3 检索数据
4 插入数据
5 更新数据
6 删除数据
7 条件语句
8 视图
9 触发器
10 事务

1 基本术语

1 数据库相关术语

数据库（database）
- 保存有组织的数据的容器，通常是一个文件或一组文件
数据库软件/数据库管理系统（DBMS）
- 人们常使用数据库这个术语来代表他们使用的数据库软件，这是不正确的。
- 数据库是通过DBMS创建和操纵的容器，我们不直接访问数据库，我们使用的是DBMS，其将代替我们访问数据库。
SQL（Structured Query Language）
- SQL是结构化查询语言的缩写，它是一种专门用来与数据库通信的语言
- SQL不是某个特定数据库供应商专用的语言，所用重要的DBMS都支持SQL
- 事实上任意两个DBMS实现的SQL都不完全相同，因此SQL语法并不是完全可移植的
MySQL
- MySQL是一种DBMS
- MySQL广受欢迎，因为MySQL是开源免费的，而且MySQL的执行速度很快
客户机-服务器

DBMS可分为两类，一类是基于共享文件系统的（如Microsoft Access），一类是基于客户机-服务器的（如MySQL、Oracle和Microsoft SQL Server）。

客户机-服务器分为两个部分

客户机是与用户交互的软件，其将提出各种数据请求
服务器是负责所有数据访问和处理的软件，与数据文件交互的只有服务器软件，关于数据查询、添加、删除和更新的请求都由服务器软件完成。

服务器软件是MySQL DBMS
客户机可以是MySQL提供的工具（MySQL Command Line Client）、图形交互客户机（Navicat、MySQL Query Browser等）、Web应用开发语言（ASP、JSP、PHP等）、程序设计语言（C、C++、Java等）等。

2 数据库表相关术语

表（table）
- 向数据库存储数据时，并不是随便将其存入数据库，而是将数据存入数据库的表中。
- 表是一种结构化的文件，存储在表中的数据是一种类型的数据或一个清单。
- 一个数据库中的每个表都有一个唯一的名字用来标识自己，当然在不同的数据库中是可以使用相同的表名的。
模式（schema）
- 表具有一些特性，这些特性定义了数据在表中如何存储，描述表的这组信息就是所谓的模式。
- 模式可以用来描述数据库中特定的表以及整个数据库的布局及特性信息。
列（column）
- 列是表中的一个字段，每一列存储着一条特定的信息。
- 所有表都是由一个或多个列组成的
- 每个列都有相应的数据类型（datatype），数据类型定义列可以存储的数据种类
行（row）/记录（record）
- 行和数据库记录是可以相互替代的
- 表中的数据是按行存储的，所保存的每个记录存储在行内
主键（primary key）
- 表中的每一行都应该有可以唯一标识自己的一列或一组列，这些列被称为主键，即主键可以唯一区分表中的每个行
- 没有主键，更新或删除表中特定行时就很困难，因为没有安全的方法保证只涉及相关行
- 并不总是需要主键，但是一个好的习惯就是为每个表都设定一个主键。
- 表中的任何列都可以作为主键，但是MySQL规定主键列必须满足以下条件
  - 任意两行都不具有相同的主键值（当多个列作为键值时，所有列值的组合必须唯一，但单个列的值可以不唯一）
  - 主键列不允许NULL值
- 有关主键的好习惯
  - 不更新主键列的值
  - 不重用主键列的值
  - 不在主键列使用可能会更改的值（如名称、年龄等）
外键（foreign key ）
- 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系
- 主键+外键可以保证表示关系的列中值的合法性

2 使用MySQL

连接MySQL

在使用MySQL DBMS之前，客户机需要与DBMS建立连接
- 要建立连接就需要登录到DBMS，DBMS在内部保存自己的用户列表，并且把每个用户与各种权限关联起来。
为了登录到MySQL DBMS，客户机需要提供以下信息
- 主机名（计算机名）

如果连接到本地MySQL服务器，则为localhost

端口

默认端口为3306

一个合法的用户名和密码
- 在连接到MySQL DBMS后，没有任何数据库可供使用，因此在执行数据库操作前，首先需要选择并打开一个数据库
mysql数据库存储着MySQL DBMS的数据库、表、列、用户、权限等信息。

SQL语句

SQL语句的组成
- SQL语句由子句（clause）构成，有些子句是必需的，而有的是可选的。
- 一个子句由一个关键字和所提供的数据组成。
分号**;**
- 单条SQL语句不需要加分号
- 多条SQL语句一起执行，则必须以分号分隔。
大小写
- SQL语句不区分大小写，因此SELECT和select是相同的
- 建议对所有SQL关键字使用大写，对所有列和表名使用小写
空格
- DBMS在处理SQL语句时，其中所有空格都被忽略，因此SQL语句可以在一行给出，也可以分成许多行。
- 可以根据关键字将SQL语句分成多行，便于阅读
完全限定名

在SQL语句中，可以直接使用列名/表名引用列和表，也可以使用完全限定名来引用列和表，如

SELECT table_name.column_name
FROM base_name.table_name

单引号**''**

单引号用来限定字符串

3 检索数据

1 SELECT语句

功能

SELECT语句是从一个或多个表中检索数据，返回多行需要的数据

语句格式
```
SELECT column_name1, column_name2,...
FROM table_name
```
行的顺序
- 这里没有对查询结果进行排序，则返回的数据的顺序可以认为是随机的、没有意义的。

检索多个列

可以在SELECT关键字后列出多个列名，检索多个列

检索所有列

在列名的位置使用星号*****通配符表示检索所有列。
除非明确需要表中的每个列，否则最好不要使用*通配符，因为检索到不需要的列会降低性能。
使用通配符的一个优点就是可以检索名字未知的列

2 DISTINCT关键字

功能

指示DBMS只返回不同的数据

语句格式
```
SELECT DISTINCT column_name1, column_name2,...
FROM table_name
```
- DISTINCT关键字必须直接放在所有列名的前面
- DISTINCT关键字应用于所有列，而不仅仅是前置DISTINCT关键字的列。

3 LIMIT子句

功能

使用LIMIT子句可以限制返回的行数

注意LIMIT是限制返回的行数，并不限制SELECT得到的行数，因此在对SELECT得到的数据进行各种计算时，与LIMIT子句无关
LIMIT只对返回的结果进行限制，因此LIMIT必须位于所有SELECT子句的最后

语句格式
```
SELECT column_name1, column_name2,...
FROM table_name
LIMIT [begin_index,] rows_num
```
- LIMIT关键字后可以跟一个值，也可以跟两个值。
- 跟一个值时，指示至多返回的行数，跟两个值时，第一个值指示返回的开始行（从第0行开始），第二个值指示至多返回的行数
- MySQL 5支持LIMIT的另一种替代语法，可以使用OFFSET（开端）关键字将LIMIT后的两个值分开
```
LIMIT 4 OFFSET 3
```
  上述语句表示从检索结果的行3开始至多取4行，等价于**LIMIT 3, 4**（常用于分页显示）

4 ORDER BY子句

功能

可以明确地排序用SELECT语句检索出的数据。

语句格式
```
SELECT column_name1, column_name2,...
FROM table_name
ORDER BY column_name1, column_name2,...
```
- ORDER BY子句可以取一个或多个列的名字，并据此来对数据进行排序。
- 通常，ORDER BY子句中使用到的列是所检索的列，实际上也可以选择没有被检索的列作为排序依据。

位置

ORDER BY子句应确保位于FROM子句之后，如果使用LIMIT子句，则它必须位于ORDER BY子句之后。

按多个列排序

在按多个列排序时，排序完全按照列名的顺序进行。
如在多个行具有相同的column_name1列的值时，才对行按column_name2列的值进行排序。

MySQL中的字典序

MySQL中默认大写与小写字母相同的，如A被视为与a相同。

5 DESC关键字

功能

ORDER BY子句默认按升序排序，可以使用DESC关键字指定ORDER BY子句以降序顺序进行排序。

语句格式

SELECT column_name1, column_name2,...
FROM table_name
ORDER BY column_name1 DESC, column_name2,...

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

6 WHERE子句

功能

只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）
在SELECT语句中，数据可以根据WHERE子句中指定的搜索条件进行过滤

注意WHERE子句中不能使用列别名

语句格式
```
SELECT column_name1, column_name2,...
FROM table_name
WHERE filter_condition
```
- WHERE子句中的过滤条件支持条件操作符，也支持一个特殊的子句IS NULL、IS NOT NULL
- WHERE的匹配默认不区分大小写

位置

在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后.

IS NULL子句
- IS NULL可以检查某列是否包含空值NULL，如WHERE prod_price IS NULL
- NULL与0、空字符串或空格不同
- 数据未知的列并不能与NULL匹配，因为未知具有特殊的含义
IS NOT NULL子句
- IS NOT NULL可以检查某列是否不包含空值
WHERE子句支持的条件操作符 | 操作符 | 说明 | 实例 | | —- | —- | —- | | =、!= | 等于、不等于 | WHERE prod_name = 'fuses' | | <、<=、>、>= | 小于、小于等于、大于、大于等于 | WHERE prod_price < 10 | | <> | 不等于 | WHERE vend_id != 1003 | | BETWEEN a AND b | BETWEEN匹配范围内所有值，包括开始值和结束值 | WHERE prod_price BETWEEN 5 AND 10 |

7 逻辑操作符

为了进行更强的过滤控制，SQL允许在WHERE中给出多个过滤条件，并使用逻辑操作符AND或OR将这些过滤条件联结起来。
WHERE可以包含任意数目的AND和OR操作符

1 AND关键字

功能

用在WHERE子句中的关键字，指示DBMS只返回满足所有过滤条件的行，相当于逻辑与

语句格式

SELECT column_name1, column_name2,...
FROM table_name
WHERE filter_condition1 AND filter_condition2

2 OR关键字

功能

用在WHERE子句中的关键字，指示DBMS返回满足任意过滤条件的行，相当于逻辑或

语句格式

SELECT column_name1, column_name2,...
FROM table_name
WHERE filter_condition1 OR filter_condition2

3 组合使用AND和OR

WHERE允许AND和OR两者起来进行复杂的过滤
计算次序

AND在计算次序中优先级高于OR

圆括号()
- 圆括号具有较AND和OR高的计算次序
- 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符，这样可以避免错误的组合AND和OR，也消除了歧义
- 如WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10

8 IN关键字

功能
- WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当
语法格式
```
SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name IN (val1, val2, ...)
```
- IN后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中
- IN关键字完成的工作都可以由OR关键字代替，如

WHERE vend_id IN (1002, 1003)等价于WHERE vend_id = 1002 OR vend_id = 1003

IN关键字的优点
- 与多个OR相比，IN关键字的语法更清楚且直观
- 计算的次序更容易管理
- IN最大的优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE语句

9 NOT关键字

功能

WHERE子句中用来否定NOT关键后跟的任何条件

语法格式
```
SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name NOT IN (val1, val2, ...)
```
- MySQL支持使用NOT对IN、BETWEEN、EXISTS、NULL、LIKE等关键字取反，这与其他DBMS允许使用NOT对各种条件取反有很大差别

10 通配符

通配符（wildcard）
- 通配符是可用来匹配值的一部分的特殊字符
- 通配符可以在搜索模式中任意位置使用，并且可以使用多个通配符
- 使用通配符会减慢搜索的速度，因此如果有代替的方式，则不要使用通配符
搜索模式（search pattern）

由字面值、通配符或两者组合构成的搜索条件

1 LIKE关键字

功能
- 为了在WHERE子句中使用通配符，必须使用LIKE操作符。
- LIKE指示其后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

语法格式

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name LIKE search_pattern

2 百分号`%`通配符

功能
- %匹配任何不同字符出现任意次数（包括0次）
- 虽然%好似能匹配任何东西，但是不能匹配值为**NULL**的行
实例
- 找出所有以词jet开头的产品

WHERE prod_name LIKE 'jet%'

找出以xy结尾的产品

WHERE prod_name LIKE '%xy'

3 下划线`_`通配符

功能

_配合任何单个字符

11 正则表达式

前面了解了使用匹配、比较和通配符对查找到的数据进行过滤，随着过滤条件复杂性的增加，正则表达式开始变得有用
正则表达式
- 正则表达式是用来匹配文本的特殊的字符串
- MySQL仅支持多数正则表达式实现的一个很小的子集

1 REGEXP关键字

功能
- 为了在WHERE子句中使用正则表达式，必须使用REGEXP操作符。
- REGEXP指示其后跟的是正则表达式

语法格式

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name REGEXP regular_expression

REGEXP和LIKE的区别
- LIKE用来匹配整个列值，如果被匹配的文本仅是在列值中出现，相应的行并不会返回
- REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，相应的行将会返回

如果在正则表达式中使用**^**和**$**定位符，REGEXP也可以匹配整个列值

2 正则表达式元字符表

正则表达式由一些普通字符和一些元字符组成。

普通字符包括大小写的字母和数字，而元字符则具有特殊的含义

元字符	说明	实例
.	匹配任意一个字符	.000匹配1000、2000等
\|	OR操作，匹配两者之一	1000 \| 2000 \| 3000匹配1000、2000或3000
[]	另一种形式的OR语句，匹配其内的任意单一字符	[123]匹配1、2或3
-	定义一个范围	[1-3]相当于[123]；[a-c]相当于[abc]
\\	转义正则表达式的特殊字符	\\.匹配.；\\-匹配-；\\?匹配?
{n}	匹配前面的子表达式n次	[01]{2}匹配00和11
{n, }	匹配前面的子表达式不少于n次
{n, m}	匹配前面的子表达式至少n次，至多m次
*	匹配前面的子表达式0次或多次	ab*匹配a、ab、abbb等
+	匹配前面的子表达式1次或多次（等于{1, }）	ab+匹配ab、abbb等
?	匹配前面的子表达式0次或1次（等于{0, 1}）	ab?匹配a和ab
^	^位于[]内的开头，用来否定该集合； ^位于正则表达式的开头，指示必须匹配文本开头	[^0-9]匹配除数字外的其他字符； ^[0-9]匹配以数字开头的字符串
$	$位于正则表达式的尾部，指示必须匹配文本的结尾	a$匹配以小写字母a结尾的字符串

12 计算字段

计算字段定义
- 存储在数据库表中的数据一般不是应用程序所需要的格式（如应用程序需要两个列值的拼接结果，或某个列值格式化后的结果等），那么从数据库表中检索出数据后，就需要对数据进行计算、转换或格式化等操作。虽然这些操作都可以在客户机应用程序完成，但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快很多。
- 为了在数据库服务器完成数据的转换、计算、格式化等操作，需要在SELECT语句中对相应列指定具体操作。经过计算、转换或格式化而得到的列就称为计算字段，计算字段是在运行SELECT语句时创建的。
- 计算字段并不实际存在于数据库表中，因为计算字段的值来自于表中的数据。
- 只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户机角度来看，计算字段与其他列的数据是以相同的方式返回的。
  
  字段（field）基本上与列的意思相同，两者可以互换使用，一般来说，数据库表列一般称为列，而字段通常用在计算字段。

创建计算字段

在SELECT语句中进行算术计算或使用数据处理函数可以创建计算字段

1 AS关键字

功能
- AS关键字可以赋予一个字段或值别名（alias）
- 对于计算字段来说，在经过计算后，得到的只是一个值，而没有名字。如果计算字段仅用在SQL查询工具中查看一下结果，那么没什么问题，但是一个未命名的列是不能用于客户机应用中的，客户机没办法引用它，因此需要AS关键字为计算字段赋一个别名。
- 在实际的表列名包含不符合规定的字符（如空格）或容易产生误解时，也可以为表列名赋一个别名

语法格式

SELECT column_name/calculated_field AS alias
FROM table_name

2 算术计算

在SELECT语句中可以对检索出的数据进行算术计算，算术计算的结果为一个计算字段。

MySQL支持四种基本算术操作符，即+、-、*、/。此外，圆括号可以用来调整计算顺序。

实例

输入

SELECT prod_id, quantity, item_price, quantity*item_price AS totall_price
FROM orderitems
WHERE order_num = 20005

输出

13 数据处理函数

函数
- 与多数计算机语言一样，SQL支持利用函数来处理数据
- 函数一般是在数据上执行的，进行数据转换和处理
- 函数接受的参数为各种类型的数据，但实际传参时，参数都是列名，表示对某列中的数据进行处理
- 当函数用在SELECT子句时，将创建一个计算字段，而用在WHERE子句时，则可以丰富数据的过滤选择，如下：

输入：

SELECT Upper(cust_name)
FROM customers

输出：

输入：

SELECT cust_name
FROM customers
WHERE Upper(cust_name) = 'WASCALS'

输出：

多数DBMS支持以下类型的函数
- 用于处理文本数据的文本处理函数
- 用于在数值数据上进行算术操作的数值处理函数
- 用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数
- 返回DBMS正使用的特殊信息（如返回用户登录信息、版本）的系统函数

1 文本处理函数

常用的文本处理函数 | 函数 | 说明 | | —- | —- | | Left(str, len) | 返回str最左边长度为len的字符串 | | Right(str, len) | 返回str最右边长度为len的字符串 | | Length(str) | 返回str的长度 | | Lower(str) | 将str转换为小写 | | Upper(str) | 将str转换为大写 | | LTrim(str) | 去掉str左边的空格 | | RTrim(str) | 去掉str右边的空格 | | Trim(str) | 去掉str两边的空格 | | Concat(str1, str2, …) | 多数DBMS使用+来实现字符串拼接，而MySQL使用Concat()函数来实现 | | Soundex(str) | 返回str的SOUNDEX值（发音） |

e30b4fecf9efc5204624d2b5656f4d73_4EF7B5DAE69AD54DE81C49D58020D7A4.png

2 日期和时间处理函数

数据库中的日期和时间格式
- 数据库中，日期和时间采用相应的数据类型和特殊的格式存储，以便能快速有效地排序或过滤，并且节省存储空间。
- 一般来说，应用程序不直接使用数据库的日期和时间格式，因此日期和时间函数总是被用来读取、统计和处理这些值。
- MySQL的日期格式

不论是插入或更新表值还是用WHERE子句进行过滤，日期必须为yyyy-mm-dd格式（虽然其他日期格式也可行，但yyyy-mm-dd格式是首选）

对于数据库中datatime类型的值来说，其既存储日期也存储时间，那么如果只用日期则无法进行匹配，此时就需要借助Data()函数来提取数据中的日期。

常用的日期和时间处理函数 | 函数 | 说明 | | —- | —- | | AddDate(expr, days)
AddDate(date, INTERVAL expr unit) | 给指定日期增加一个日期（天、周、年） | | AddTime(expr1, expr2) | 给时间表达式expr1加上时间表达式expr2 | | CurDate()/CURRENT_DATE | 返回当前日期 | | CurTime()/CURRENT_TIME | 返回当前时间 | | Now()/CURRENT_TIMESTAMP | 返回当前日期和时间 | | Date(expr) | 从日期时间表达式中提取日期部分 | | Time(expr) | 从日期时间表达式中提取时间部分 | | Day(date) | 返回一个日期的天数部分 | | Month(date) | 返回一个日期的月份部分 | | Year(date) | 返回一个日期的年份部分 | | Hour(time) | 返回一个时间的小时部分 | | Minute(time) | 返回一个时间的分钟部分 | | DayOfWeek(date) | 返回一个日期对应的周几 | | DateDiff(expr1, expr2) | 计算两个日期表达式相差的天数 |

实例

检索出2005年9月的所有订单

输入

SELECT cust_id, Date(order_date) AS order_date, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9

输出

3 数值处理函数

数值处理函数仅处理数值数据，没有串和日期-时间函数使用的那么频繁
常用的数值处理函数 | 函数 | 说明 | | —- | —- | | Abs(X) | 返回一个数的绝对值 | | Exp(X) | 返回一个数的指数值 | | Rand(X) | 返回一个随机数 | | Sqrt(X) | 返回一个数的平方根 | | Round(X, digit) | 保留一个数的digit位小数 |

4 聚集函数

聚集函数的定义

运行在多个行（一个表的所有行或一个组的所有行）上，计算和返回单个值的函数称为聚集函数

聚集函数的应用
- 实际应用时经常需要汇总数据而不用把它们实际检索出来，例如下列场景
  - 获取表中/组中满足某个条件或包含某个特定值的行数
  - 获取表中/组中行的和
  - 获取表列/组列的最大值、最小值和平均值

如实际想要的是汇总信息，那么返回实际表数据是对时间、带宽、处理资源的浪费，因此需要聚集函数

聚集函数常常与分组关键字GROUP BY组合使用，如果不与分组关键字使用，则聚集整表的信息
聚集函数可以令被聚集的列具有“聚集”的含义，在SELECT中出现的列只能全部是具有“聚集”含义的列或全部不具有“聚集”含义的列，具有“聚集”含义的列包括
- 使用聚集函数的列
- GROUP BY中的名
对于条件筛选语句来说，聚集函数只能出现在HAVING子句中，不能出现在WHERE子句中，因为只有HAVING子句接纳具有“聚集”含义的列
- GROUP BY中的列能出现在WHERE子句中，这是因为语句执行顺序的原因，执行WHERE时，列还不具有聚集的含义

常用的5个聚集函数 | 函数 | 说明 | | —- | —- | | Avg(expr) |
- 返回某列的平均值
- 忽略列值为NULL的行
| | Count(expr) |
- 返回某列的行数
- 使用Count(*)表示计数表中行的数目，包括空值（NULL）和非空值
- 使用Count(column_name)则只对column_name列中具有值的行进行计数，忽略NULL值
| | Max(expr) | 返回某列的最大值 | | Min(expr) | 返回某列的最小值 | | Sum(expr) | 返回某列值之和 |

聚合多个列

利用标准的算术操作符，所有聚合函数都可用来执行多个列上的计算
实例
返回订单中所有物品价钱之和

输入

SELECT SUM(item_price*quantity) AS total_price,
FROM orderitems
WHERE order_num = 20005

输出

聚集不同值

所有聚集函数的列名参数前，都可以使用DISTINCT参数，这样只聚集列值的不同值
实例：查询tb_user中不同username的数量

SELECT COUNT(DISTINCT username)
FROM tb_user

组合聚集函数
- SELECT语句可根据需要包含多个聚集函数
- 聚集函数也可以和不使用聚集函数的查询组合，不使用聚集函数的列只能是GROUP BY中的列名，因为只有GROUP BY中的列名具有“聚集”的含义

实例

输入

SELECT     COUNT(*) AS num_items,
           MIN(prod_price) AS price_min,
           MAX(prod_price) AS price_max,
           AVG(prod_price) AS price_avg            
FROM    products

输出

14 数据分组

数据分组的定义
- 目前为止所有的计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的

对于产品目录表（products），如果我们需要每个供货商提供的产品数目，或只返回提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商，此时就需要进行数据分组了

数据分组允许把数据分为多个逻辑组，以便能对每个分组使用聚集函数进行计算

1 GROUP BY子句

功能

分组是在SELECT语句的GROUP BY子句中建立的

语句格式
```
SELECT column_name1, column_name2,...
FROM table_name
GROUP BY column_name1, column_name2,...
```
- GROUP BY子句指示MySQL将查找到的数据按指定列进行排序并分组数据
- GROUP BY子句可以包含任意数目的列，只有GROUP BY指定的所有列的值相等的行才会被聚集到一起
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
- 当SELECT子句中既出现列，又出现聚集函数，则必须使用GROUP BY子句

GROUP BY通常也是按组计算聚集时使用

实例

实例1

SELECT vend_id
FROM products

上述代码输出

实例2

SELECT vend_id
FROM products
GROUP BY vend_id

上述代码输出

实例3
查询每个供应商提供的产品数目

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id

上述代码输出

上述代码中，GROUP BY子句指示MySQL将products表按vend_id进行排序并分组数据，这导致对每个vend_id而不是整个表进行COUNT()计算

2 HAVING子句

功能
- MySQL支持对分组进行过滤，就像对行进行过滤一样，如列出需要至少有两个订单的顾客
- WHERE不能用于过滤分组，因为WHERE过滤指定的是行，而HAVING过滤的目标是“聚集”列（分组）
- MySQL为分组过滤提供了HAVING子句，其与WHERE子句的区别除了过滤目标是分组外，其它的都相同，即HAVING支持所有WHERE支持的过滤条件
- 可以说WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

语法格式

SELECT column_name1, column_name2,...
FROM table_name
WHERE filter_condition
GROUP BY column_name1, column_name2,...
HAVING filter_condition

实例

列出具有两个及以上价格为10以上的产品的供应商

SELECT vend_id, COUNT(*) AS num_prods 
FROM products 
WHERE prod_price >= 10 
GROUP BY vend_id 
HAVING COUNT(*) >= 2

15 检索数据所用子句的顺序及必要性表

表格中行的顺序即为子句的出现顺序 | 子句 | 说明 | 是否必要使用 | | —- | —- | —- | | SELECT | 要返回的列或表达式 | √ | | FROM | 从中检索数据的表 | 仅在从表选择数据时使用 | | WHERE | 行级过滤 | × | | GROUP BY | 分组说明 | 仅在按组计算聚集时使用 | | HAVING | 组级过滤 | × | | ORDER BY | 输出排序顺序 | × | | LIMIT | 检索结果的行数（不影响上述子句） | × |

16 联结表

联结定义
- 如果要使用单条SELECT语句查询多个表的数据时（查询多个表的数据在一行），需要使用联结
- 联结即指定多个表的行之间的配对关系
- 如果不使用联结，那么多个表的查询结果称为笛卡尔积，某个表的一行将可以行其他所有表的所有行配对，此时查询结果将是所有表的行数量的乘积
- 联结在实际的数据库表中不存在，联结存在于查询的执行当中
- 联结是SQL中最重要最强大的特性
- MySQL在运行时需要关联指定的每个表以处理联结，这种处理非常消耗资源，因此不要联结不必要的表，联结的表越多，性能下降的越厉害
- 用于联结两个表的列（联结字段）应该同时出现在两个表中，因此一般是一个表的外键，而是另一个表的主键
联结的实现

联结有以下两种方式实现

由于联结相当于一种过滤条件（联结条件），因此联结也可以在WHERE子句中实现
SQL提供有特定的子句来实现联结（JOIN … ON …），而ANSI SQL规范推荐使用特定的子句来实现联结，这样做能够确保不会忘记设定联结条件

1 等值联结/内联结

等值联结概述

等值联结指根据两个表都存在的列值是否相等来判断是否将两个表的行匹配在一起

WHERE子句实现等值联结实例

现有存储供应商信息的表vendors以及存储产品信息的表products。products表的外键是vendors的主键，即供应商id，products表除了存储存储供应商id外不存储任何供应商信息。
现查询所有产品的产品名、产品价格、供应商名

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE products.vend_id = vendors.vend_id
ORDER BY vend_name, prod_name

查询结果如下

INNER JOIN关键字和ON子句

功能

只返回联结表中联结字段相等的行
INNER JOIN关键字用于在FROM子句中指定两个表之间的关系
ON子句用于指定联结条件，类似于WHERE子句
WHERE子句中的过滤条件在表联结后生效

语法格式

SELECT column_name1, column_name2,...
FROM table_name1 INNER JOIN table_name2 
ON table_name1.column_name = table_name2.column_name

实例

查询订购物品TNT2的所有客户的客户姓名及客户联系方式

SELECT
cust_name,
cust_contact 
FROM
customers
INNER JOIN orders
INNER JOIN orderitems ON customers.cust_id = orders.cust_id 
AND orders.order_num = orderitems.order_num
AND prod_id = 'TNT2'

查询结果如下

2 表别名

在进行自联结分析前需要先了解表别名
表别名概述
- 表别名与列别名一样，使用AS子句来为表名取一个别名
- 使用表别名主要有以下两个功能
  - 缩短SQL语句
  - 允许在单条SELECT语句中多次使用相同的表
表别名与列别名的区别
- 表别名只在查询中使用，不会返回到客户端，而列别名不一样
- 列别名在SELECT子句中赋予，表别名在FROM子句中赋予
- 表别名可以在所有查询子句中使用，而列名只能在除FROM和WHERE子句以外的查询子句上使用，这是查询子句的执行顺序导致的

3 自联结

自联结概述
- 自联结指的是一张表与其自己进行联结
- 自联结需要用到表别名
- 自联结与内联结的实现完全相同
自联结实例

我们发现ID为DTNTR的物品存在问题，因此想要查询生成该物品的供应商生产的其他物品，从而检查这些物品是否存在问题。
此次查询需要首先找到生成ID为DTNTR的供应商，然后找出这个供应商生产的其他物品

WHERE子句实现

SELECT
p2.prod_id,
p2.prod_name,
p2.vend_id 
FROM
products AS p1,
products AS p2 
WHERE
p1.prod_id = 'DTNTR' 
AND p1.vend_id = p2.vend_id

INNER JOIN ON关键字实现

SELECT
p2.prod_id,
p2.prod_name,
p2.vend_id 
FROM
products AS p1
INNER JOIN products AS p2 ON p1.vend_id = p2.vend_id
WHERE
p1.prod_id = 'DTNTR'

实际上利用子查询也可以实现联结表的效果

SELECT prod_id,
  prod_name,
  vend_id
FROM products
WHERE vend_id = (SELECT vend_id
            FROM products
            WHERE prod_id = 'DTNTR')

查询结果如下

4 自然联结

自然联结概述

自然连接（Natural join）是一种特殊的等值连接，它要求联结条件中进行比较的列必须是相同的属性组，并且查询结果不能有重复的列。

实现自然联结

自然联结完全靠查询语句来实现，实际上我们使用的等值联结基本都是自然联结，基本不会用到不是自然联结的等值联结，因为实际生活中，用两个具有不同含义的列去比较也没什么意义

5 外部联结

外部联结概述
- 内联结将一个表中的行与另一个表中的行相关联，即只查询具有关联行的行
- 外部联结则可以包含没有关联行的行

例如有表A客户信息表和表B订单表，其中表B包含外键客户ID。现需要查询每个客户下了多少订单，该查询包括那些至今尚未下订单的客户。
这里就需要使用外部联结，否则那些没有下订单的客户将无法查询到

LEFT/RIGHT OUTER JOIN关键字实现外部联结

功能

联结关键字左右的两个表，其中根据关键字的第一个词是LEFT还是RIGHT来确定查询左侧的所有行还是右侧的所有行

语法格式

SELECT column_name1, column_name2,...
FROM table_name1 LEFT/RIGHT OUTER JOIN table_name2 
ON table_name1.column_name = table_name2.column_name

实例

实现概述中的例子

SELECT
customers.cust_id,
orders.order_num 
FROM
customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id

查询结果为

过滤条件
- 对于INNER JOIN来说，过滤条件放在ON中或WHERE中效果一样
- 对于LEFT JOIN来说，过滤条件在ON中只对右表中的行有效，而过滤条件放在WHRER中时则对联结后的结果有效

17 查询子句执行顺讯

FROM JOIN ON
WHERE
GROUP BY（这一步及以后可以使用列别名）
HAVING
SELECT
ORDER BY
LIMIT

4 插入数据

1 INSERT语句

功能
- 顾名思义，INSERT语句是用来插入数据到数据库表的
- 执行INSERT语句后，不会产生任何输出
插入到表中的内容可以是
1. 插入完一行
2. 插入多行
3. 插入某些查询的结果

插入不同的内容，语句的格式也不同，因此下面将根据类型列出不同的语句格式

2 插入一行

语句格式
```
INSERT INTO table_name (column_name1, column_name2, ...)
VALUES (value1, value2, ...)
```
- 插入一行是最基本的INSERT语法
- 插入一行要求在表名后的小括号中明确给出列名，在VALUES子句后给出插入的值

第一个插入值对应于第一个指定的列名，以此类推

可以不给出列名，但当表结构改变时，插入语句也需要改变，因此一般插入语句都要明确给出列名
如果给出列名，则必须对每个给出的列一个值，否则将产生错误信息

省略列
- 在插入语句中给出列名时，不需要给出表的所有列名，有些列名可以省略，可以省略的列遵守以下规则
  - 该列定义为允许NULL值
  - 如果该列定义为不允许NULL值，也需要在表定义时设定为MySQL自动给予值（如自增、默认值）

不满足上述条件的列插入时不可省略

对于自增列，插入时可省略，也有必要省略，因为无论指定插入什么值，MySQL都会将其忽略

3 插入多行

语句格式
```
INSERT INTO table_name(column_name1, column_name2, ...)
VALUES (value1, value2, ...), (value1, value2, ...), ...
```
- 插入多个行可以使用多条INSERT语句完成，也可以使用上述一条INSERT语句完成
- 使用一条INSERT语句时，在VALUES后可以给出多组值，每组值对应一行，使用逗号分隔
单条语句插入多行的优点

MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快

5 更新数据

更新数据使用UPDATE语句
更新的内容可以是
1. 更新表中特定行
2. 更新表中所有行

在UPDATE子句中使用WHERE子句将更新特定行，不使用WHERE子句将更新所有行

语法格式

UPDATE table_name
SET column_name1=new_value1, column_name2=new_value2, ...
WHERE filter_condition

6 删除数据

删除数据使用DELETE语句
删除的内容可以是
1. 删除表中特定行
2. 删除表中所有行

在DELETE子句中使用WHERE子句将删除特定行，不使用WHERE子句将删除所有行

DELETE语句删除的是表的内容而不是表本身，即使DELETE所有行

语法格式

DELETE 
FROM table_name
WHERE filter_condition

7 条件语句

条件语句执行的最后结果就是一个值

1 CASE WHEN
语句格式 ```sql CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE value3 END


<a name="j7Oxj"></a>
## 7 操纵表
<a name="oR37A"></a>
### 1 创建表
- **创建表的方式**
   - 使用可交互工具
   - 使用SQL语句**CREATE TABLE**
使用交互式工具时，实际上使用的是SQL语句，只不过这些工具会自动生成并执行SQL语句
- **创建表语法格式**
```sql
CREATE TABLE table_name
(
  column_name1    datatype    [NOT NULL] [AUTO_INCREMENT]
  column_name2    datatype    [NOT NULL] [DEFAULT value]
  ...
  column_namen    datatype    [NOT NULL] [DEFAULT value]
  PRIMARY KEY (column_name, column_name, ...)
) ENGINE=InnoDB|MEMORY|MyISAM

不允许NULL值
- 在表定义中，在每列的数据类型后可以使用关键字NOT NULL指定该列不允许NULL值
- NULL值就是没有值或缺值
- 允许NULL值的列即允许在插入行时不给出给列的值
- 每个表列或是NULL列，或是NOT NULL列，这种状态在创建表时定义，默认为NULL列
- 主键列只能使用不允许NULL值的列
自增
- 我们常常需要id列来唯一确定表中的行，然而这些id除它们是唯一的以外没有别的特殊意义，这些id可以随意，只要它们是唯一的即可
- 在创建表时，可以在每列的NOT NULL关键字后使用关键字AUTO_INCREMENT指定该列为自增列
- 当某个列为自增列时，每次插入一个新行，如果该自增列缺省值，那么MySQL会为该列自动赋予一个值，同时该值将自动增1，用于给下一个插入的行赋值
  - 在插入行时也可以为自增值指定一个值，只要这个值从没被使用过，该值将被用来替代自动生成的值，其后的自增也以该值为基础
- 每个表只允许有一个自增列，而且它必须被索引（如通过使该列为主键）
获取最后一个自增值
- 通过自增生成主键的一个缺点就是不知道这些主键的具体值是什么
- 我们可以使用last_insert_id()函数获取最后一个自增值，如
```
SELECT last_insert_id()
```
默认值
- 在创建表时可以在列定义中使用DEFAULT关键字指定列的默认值
- 当插入行时，如果没有指定列的值，列值将使用指定的默认值
- 对于用于计算或数据分组的列，常常使用默认值而不是NULL

2 数据库引擎

数据库引擎概述
- 在表定义的最后可以使用ENGINE关键字指定MySQL使用的数据库引擎
- 数据库引擎是具体管理和处理数据的，所有的SQL语句也都由数据库引擎执行
- MySQL包含多种数据库引擎，每种引擎都具有各自不同的功能和特性，根据不同的任务选择合适的引擎能获得良好的性能、功能和灵活性
- 创建表时如果没有显示指定数据库引擎，那么MySQL将使用默认数据库引擎，大多数为MyISAM
MySQL支持的数据库引擎
- InnoDB

InnoDB是一个可靠的事务处理引擎，但是不支持全文搜索

MyISAM

MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理

MEMORY

MEMORY在功能上与MyISAM一致，但由于数据存储在内存中而不是硬盘，所以速度很快，适合临时表

数据库引擎混用
- 数据库引擎是可以混用的，不同的表可以使用不同的数据库引擎
- 混用数据库引擎的缺陷是外键不能跨引擎

3 更新表定义

表定义更新概述
- 更新表定义可以使用ALTER TABLE语句
- 理想状态下，当表中存储数据后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对表的定义进行改动
- 更新表的操作要格外小心，因为该操作不能回滚

语句格式

为表新增一列

ALTER TABLE table_name
ADD column_name datatype

删除表中某列

ALTER TABLE table_name
DROP COLUMN column_name

定义外键

ALTER TABLE table1_name
ADD CONSTRAINT foreign_key_name
FOREIGN KEY (table1_column_name) REFERENCES table2_name (table2_column_name)

4 删除表

删除表概述

删除表没有撤销

语法格式
```
DROP TABLE table_name
```

5 重命名表

语法格式

RENAME TABLE table_name TO new_table_name

8 视图

视图概述
- 视图是虚拟的表，不包含数据库表应该有的任何列或数据，只包含一个SQL查询
- 视图本身不包含数据，其数据是每次被使用时从其他数据库表中检索出来的
- 视图被创建后，可以用与表基本相同的方式利用它们

可以对视图执行SELECT操作并过滤和排序数据、将视图联结到其他视图或表，甚至能添加和更新数据（更新和添加数据存在某些限制）

视图的规则与限制
- 视图必须唯一命名，不能与其他视图或表由相同的名字
- 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
- ORDER BY语句可以用在视图上，如果视图中的查询语句也含有ORDER BY，那么视图中的ORDER BY将被覆盖
- 视图不能索引，也不能有关联的触发器或默认值
- 视图可以和表一起使用，如联结视图和表
- 可以对视图进行有限制的插入、更新和删除操作，但一般应该将视图用于检索
视图的应用
- 重用SQL查询语句、简化的SQL操作

在编写复杂的SQL查询后，可以方便地重用它而不必知道它的实现细节

保护数据

利用视图可以给用户授予表的特定部分的访问权限而不是整个表的访问权限

更改表的数据格式和表示

视图可返回与底层表的表示和格式不同的数据，而不需要改动底层表

操作视图的语句

创建视图
```
CREATE VIEW view_name AS
SELECT ...
```

覆盖式创建/更新视图

CREATE OR REPLACE VIEW view_name AS
SELECT ...

查看视图中的查询语句
```
SHOW CREATE VIEW view_name
```
删除视图
```
DROP VIEW view_name
```

视图应用实例

创建一个名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers INNER JOIN orders INNER JOIN orderitems
ON customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num

如果执行SELECT * FROM productcustomers将所有列出订购了任意产品的客户

检索订购了产品TNT2的客户信息

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2'

这条查询通过WHERE子句从视图中检索特定的数据。
在MySQL处理此查询时，它将查询中的WHERE子句添加到视图查询中已有的WHERE子句中，以便正确过滤数据

9 触发器

10 事务

事务（transaction）概述
- 事务是一种机制，用来管理必须成批执行的MySQL操作
- 利用事务，可以保证一组操作不会中途停止，一组操作或作为整体执行，或完全不执行
- 如果没有错误发生，整组操作可提交给数据库表，如果发生错误，则进行回退以恢复数据到某个已知且安全的状态
- 并不是所有数据库引擎都支持事务，两种最常用的数据库引擎是MyISAM和InnoDB，只有后者支持事务
启动事务

启动事务使用START TRANSACTION语句，该语句之后的所有SQL语句都属于事务

START TRANSACTION
SQL语句...

结束事务

可以通过两种方式结束事务

回滚事务

使用ROLLBACK语句可以回滚事务
```
START TRANSACTION
SQL语句...
ROLLBACK
```
回滚事务后，撤销事务中执行的SQL语句，无论这些语句是否执行成功
只能回滚事务中的INSERT、UPDATE和DELETE语句

不能回滚SELECT、CREATE和DROP语句，这些语句可以出现在事务中，但是执行回滚时这些语句不会撤销

提交事务

事务以外的SQL语句是隐含提交的，即执行的结果会立即写入到数据库表中
在事务中的SQL的提交不会隐含进行，必须明确使用COMMIT语句来将SQL语句执行结果写入到数据库中
```
START TRANSACTION
SQL语句...
COMMIT
```

保留点
- 简单的事务可以通过ROLLBACK或COMMIT语句回滚或提交整个事务，但是复杂的事务可能需要部分回滚
- 为了支持部分回滚，可以在事务中的合适位置使用SAVEPOINT语句放置保留点，为了回退到保留点，可以使用ROLLBACK TO语句
```
START TRANSACTION
SQL语句...
SAVEPOINT save_point_name
SQL语句...
ROLLBACK TO save_point_name
SQL语句...
COMMIT/ROLLBACK
```
  - 每个事务中的保留点必须有唯一的名字
  - ROLLBACK TO语句不能结束事务，必须使用ROLLBACK或COMMIT来结束事务
  - 结束事务后，保留点将自动释放

数据库基础知识

1 基本术语

1 数据库相关术语

2 数据库表相关术语

2 使用MySQL

连接MySQL

SQL语句

3 检索数据

1 SELECT语句

2 DISTINCT关键字

3 LIMIT子句

4 ORDER BY子句

5 DESC关键字

6 WHERE子句

7 逻辑操作符

1 AND关键字

2 OR关键字

3 组合使用AND和OR

8 IN关键字

9 NOT关键字

10 通配符

1 LIKE关键字

2 百分号%通配符

3 下划线_通配符

11 正则表达式

1 REGEXP关键字

2 正则表达式元字符表

12 计算字段

1 AS关键字

2 算术计算

13 数据处理函数

1 文本处理函数

2 日期和时间处理函数

3 数值处理函数

4 聚集函数

14 数据分组

1 GROUP BY子句

2 HAVING子句

15 检索数据所用子句的顺序及必要性表

16 联结表

1 等值联结/内联结

2 表别名

3 自联结

4 自然联结

5 外部联结

17 查询子句执行顺讯

4 插入数据

1 INSERT语句

2 插入一行

3 插入多行

5 更新数据

6 删除数据

7 条件语句

1 CASE WHEN

2 数据库引擎

3 更新表定义

4 删除表

5 重命名表

8 视图

9 触发器

10 事务

2 百分号`%`通配符

3 下划线`_`通配符