《MySQL必知必会》读书笔记

最新推荐文章于 2023-02-09 23:48:55 发布

_studyMachine

最新推荐文章于 2023-02-09 23:48:55 发布

阅读量418

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/study_machine/article/details/114888865

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

第1章：了解SQL

本章主要是对数据库的各种名词进行解释。

数据库（database）：数据库更多像是一个概念，而不是一个实体，也可以理解为存放数据的硬件实体。

【注意】：像是MySQL、Oracle这类软件，虽然平时统称为数据库，但是严格上来说，其应该属于下面的DBMS。
数据库管理系统（DBMS）：像是MySQL、Oracle等常见数据库软件。
表（table）：某特定类型数据的结构化清单。
1. 属于数据库的子范畴，一个数据库中包含多个表。
2. 同一数据库中不能出现表名重复的两张表
模式（schema）：关于数据库和表的布局和特性的信息。
- 关于表：schema 定义了 数据在表中应该怎么存储、可以存什么样的数据 等此类信息。
- 关于数据库：schema定义了表与表之间的关系 等以及其他关于整个数据库的信息。
总的来说，schema是对数据库以及表的各种定义。
列（column）：表中的一个字段，一张表有多个列，每列存放同一数据类型的数据。
数据类型（datatype）：数据类型限制表中每列只能存放特定的数据类型。
行（row）：表中的一行为一条具体的数据，平时也可以将一行称作一条记录，当然行才是专业术语。
主键（primary key）：每张表都必须有一列，其值能够唯一区分该表的每一条数据。则该列数据我们成为主键。
- 一般来说，定义一张表必须要有主键。
- 不同的行主键的值不能重复。
SQL（Structured Query Language）：结构化查询语言，一种专门用来与数据库通信的语言。

第2章：MySQL简介

2.1 什么是MySQL

MySQL是其中一种DBMS软件，同类型的还有Oracle、Access、DB2等。
MySQL主要由客户机和服务器软件组成。

客户机：

使用MySQL的应用，例如我们用Java开发的web应用、操作MySQL的客户端工具（Navicat）等。

这些客户机通过网络发送请求给服务器软件。

服务器软件：

与数据打交道的软件。在接收到来自客户机的请求后，对数据进行相应的增删查改等操作。

2.2 MySQL工具

MySQL命令行：安装完MySQL后自带的无图形界面的客户机软件。
MySQL Administrator ：图形化的交互客户机，用于简化MySQL服务器的管理。
MySQL Query Browser：图形化的交互客户机，同于编写和执行MySQL语句。

上面两个MySQL组成了我们常用的数据库客户端工具，例如Navicat、以及JB家的data grip。

第3章：使用MySQL

3.1 连接

通过MySQL客户机连接到MySQL服务器软件，需要如下信息：

主机名（域名或者ip地址）
端口（默认3306）
合法的username
用户名对应的密码（如果有的话）

连接之后便可访问任意当前登录用户能够访问的任意数据库以及表了。

3.2 选择数据库

指令：use 数据库名

3.3 查看可用数据库

指令：show databases

3.4 查看可用表

指令：show tables

3.5 查看表中所有列

指令：show columns from 表名

3.6 其他show语句

show status：显示服务器状态信息
show create database：显示创建特定数据库的语句
show createtable：显示创建特定表的语句
show grants：显示所有用户或者特定用户的安全权限
show errors：显示服务器错误消息
show warnings：显示服务器警告消息

进一步了解show可以执行 help show指令。

第4章：检索数据

4.1 检索单列

select 列名 from 表名

【注意】

SQL语句不区分大小写。
某些标识符（如：数据库名、表名、列名）默认区分大小写，编写sql语句时要大小写保持一致。

4.2 检索多列

select 列名1, 列名2, 列名3 from 表名

【注意】

不同的列名之间以逗号分隔

4.3 检索所有列

select * from 表名

【注意】

虽然使用通配符 * 可能会使你自己省事，但检索不需要的列通常会 降低检索和应用程序的性能。

4.4 只显示不同的行（去重）

关键字：distinct

select distinct 列名 from 表名

在这里插入图片描述

【注意】

distinct 必须放在所有列的前面；
distinct不能单独指定某个列进行去重，如果检索多列，必须保证所有列均各不相同的行才会显示出来。

4.5 限制结果

关键字：limit

# 示例1：查询符合条件的前5条记录
select 列名 from 表名 limit 5

# 示例2：查询从第5行开始的（包括第5行）的五条记录
select 列名 from 表名 limit 5,5;

【注意】

limit关键字有两种使用方式。在示例2中，limit后的第一个数为 开始的行数，第二个数为查询的记录数。
检索出来的第一行为行0，行数从0开始。

4.6 使用全限定表名（通过表名指定列名）

select  tb_products.prod_name from tb_products ;

如下所示，通过表名指定列名，如上：tb_products.prod_name

也可以通过数据库名指定表名，

select  tb_products.prod_name from db_name.tb_products ;

通过数据库名指定表名，如上：db_name.tb_products

第5章：排序

5.1 根据单个字段排序

关键字：order by
根据 product_name （首字母A-Z顺序）进行排序

select product_name from tb_product order by product_name

5.2 根据多个字段排序

先根据 product_name（首字母A-Z顺序）进行排序
在 product_name 相同的情况下，再根据 product_price 进行排序

select product_name from tb_product order by product_name, product_price

5.3 指定升序降序

5.3.1 指定单个字段

通过在order by xxxx后，添加asc指定按升序排序，或者添加desc指定降序排序。
不添加 desc或 asc ，则默认是升序排序

# desc 指定升序排序
select * from tb_product order by product_name desc

5.3.2 指定多个字段

需求：根据多个字段进行排序，按照字段A进行升序，按照字段B进行降序排序

select * from tb_product order by product_name desc product_price asc

5.4 注意

与limit混用时，limit必须放在最后面。
MySQL排序规则默认是将大小写字母看做一致，可以通过修改数据库设置进行更新排序策略。

第6章：过滤数据

关键字：where
过滤数据避免放在应用层实现，应在MySQL服务机中实现，避免网络传输大量数据。
order by 与 where 同时使用，order by 应该在 where 之后。

6.1 where子句操作符

在这里插入图片描述

6.2 单值检查

# 查找名字为 'fuses' 的商品
select product_name from tb_product where product_name = 'fuses'

【注意】

MySQL默认不区分大小写，即product_name = ‘Fuses’的数据，也会被查出来。
将值与字符串类型的列进行比较，需要加单引号‘’

6.3 范围检查

关键字：<、 >、 <=、 >= 、between... and ...

# 价格 ≤100 的记录
select product_name from tb_product where product_price <= 100

# 价格 在 100 - 200 之间的记录 (不包括100 , 200)
select product_name from tb_product where product_price between 100 and 200

6.4 空值检查

关键字：is null，is not null

# 检索 价格 为 NULL 的记录
select product_name from tb_product where product_price is null

# 检索 价格 【不】为 NULL 的记录
select product_name from tb_product where product_price is not null

【注意】：null 与不匹配

若需求为检索不符合条件的记录时，例如where price <> 100，此时不会将显示 price is null 的记录，如果希望显示price is null的记录，需要将检索条件改为where price <> 100 or price is null.

第7章：组合where字句

7.1 and

关键字：and

作用：对where字句中的and前后的两个条件进行与运算

# 查找 【生产商id=1】且【价格小于等于10】的产品
select product_name from tb_product where vend_id = 1 and  product_price <=10

7.2 or

关键字：or

作用：对where字句中的or条件进行或运算

# 查找 【生产商id=1】或【价格小于等于10】的产品
select product_name from tb_product where vend_id = 1 or  product_price <=10

7.3 计算次序

关键字：( )
需求：列出价格为10元（含）以上，且由1002或1003制造的所有产品。

示例：

# 错误示例 x
select product_name from tb_product where vend_id = 1002 and vend_id = 1003 or  product_price <=10

# 正确示例 √
select product_name from tb_product where (vend_id = 1002 and vend_id = 1003) or  product_price <=10

【注意】：
- and、or 操作符的优先级默认是从左到右运算
- 尽可能使用( )对操作符进行分组，语义更加清晰

7.4 in

【示例】

#  检索厂商为 1002 或 1003 或 1004 的产品
select product_name from tb_product where vend_id in (1002,1003,1004)

【注意】：
- 实质与or作用相同，等价于where vend_id = 1002 or vend_id = 1003 or vend_id = 1004，相对来说，用in更直观。
- in操作符执行更快

7.5 not

【作用】：否定之后所跟的任何条件

【示例】：

#  检索厂商 除了1002  、1003 、 1004 以外的产品
select product_name from tb_product where vend_id not  in (1002,1003,1004)

【注意】：
- MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反

第8章：用通配符进行过滤

关键字：like
通配符：%，_

8.1 百分号（%）通配符

【用法】：% 放在子串的前面或者后面，可匹配 0个、1个或者多个字符。

【示例】：

# 查找产品名 以 'jet' 开头 的记录

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

# 查找产品名 以 'jet' 结尾 的记录

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

8.2 下划线（_）通配符

【用法】：_ 放在子串的前面或者后面，可匹配 1个字符。

【示例】：

# 查找产品名 以 'jet' 开头，后面还有一个字符 的记录

select prod_id,prod_name 
from  products
where  prod_name like 'jet_';

8.3 使用通配符的技巧

通配符搜索的处理，一般要比其他搜索所花时间更长。
**不要过度使用通配符。**如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。

第9章：用正则表达式

检索时，可使用正则表达式，至于where字句中进行检索。
MySQL仅支持部分正则表达式。

9.1 基本字符匹配

【示例】

# 检索列`prod_name`包含文本 '1000' 的所有行
select prod_name 
from products
where pro_name regexp '1000' 
order by prod_name;


# 检索列`prod_name`包含文本 '任意字符+000' 的所有行, `.`表示任意字符
select prod_name 
from products
where pro_name regexp '.000' 
order by prod_name;

上述是最基本的例子，一般不会这么使用。

相比而言，不如直接用like（正则的性能相对较低）。

【注意】
- MySQL的正则匹配默认不区分大小写。为了区分大小写，可以使用BINARY关键字，例如：
```
select prod_name 
from products
where prod_name regexp BINARY 'JetPack .000'。
```

9.2 OR匹配

【关键字】：|
【作用】：表示两个或者多个串的其中之一。

【示例】：

# 检索 `prod_name` 中含有1000 或 2000 记录。
select prod_name from products 
where prod_nmae regexp '1000|2000'

如果需要匹配两个以上的选项的其中之一，则需要使用到[ ]，例如：

select prod_name from products 
where prod_nmae regexp '[1000|2000|3000]'

9.3 范围匹配

【示例】：
- [0-9]：匹配0到9的数字，当然也可以指定其他范围，例如[1-2]、[3-6]等。
- [a-z]、[A-Z]：分别为匹配小写字母，匹配大写字母
```
# 检索符合 数字 + ' ' + 'Ton' 的记录
select prod_name from products
where prod_name regexp '[1-5] Ton'
```

9.4 匹配特殊字符（使用转义字符）

．、[]、|和- 等一些字符为MySQL的关键字，如果匹配的正则模式串包含这些字符时，需要使用转义字符 \，一般在特殊字符前面加上两个转义字符\\。

【示例】：

# 检索 vend_name 中 包含 `.` 的记录。
select vend_name from vendors 
where vend_name regexp '\\.'

注意不是直接 regexp '.'，这样这个.则表示任意字符。

\\也用来匹配空白元字符

元字符说明
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表
【注意】
- 为了匹配反斜杠（\）字符本身，需要使用\\。
- 多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）。

元字符	说明
`\\f`	换页
`\\n`	换行
`\\r`	回车
`\\t`	制表
`\\v`	纵向制表

9.5 匹配字符类

字符类，为MySQL为常见提供的常见的字符集。

在这里插入图片描述

【注意】：使用时，在上表显示的字符类的基础上再加上一个中括号[ ]，例如：
```
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;
```

9.6 匹配多个实例重复

重复元字符：用于指定字符出现的次数

在这里插入图片描述

【示例】：

# 包含 (【数字0-9】 + 空格 + 'stick' + 0或者1个s )
select prod_name from products 
where prod_name regexp '\\([0-9] sitcks?\\)';

查找结果为：

TNT (1 stick)
TNT (5 sticks)

9.7 定位符

【作用】：指定匹配特定位置的文本。

元字符说明
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾

元字符	说明
`^`	文本的开始
`$`	文本的结尾
`[[:<:]]`	词的开始
`[[:>:]]`	词的结尾

【示例】

# 找出以一个数（包括以小数点开始的数）开始的所有产品
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'  -- 以0-9或者.开头的记录
ORDER BY prod_name;

---------------------查找结果---------------------
.5 ton anvil
1 ton anvil
2 ton anvil

^的双重用途
- 在集合内（用[ ]来定义），^用于否定该集合
- 在集合外，则表示指定匹配串的开始处

第10章创建计算字段

10.1 拼接字段

10.1.1 拼接

使用concat()函数
【示例】：生成一个供应商报表，需要在供应商的名字中按照**name(location)**这样的格式列出供应商的位置。
```
SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors;
```
得到的结果是：

10.1.2 去空格

RTrim()：去除右边的空格
LTrim()：去掉串左边的空格
Trim()：去掉串左右两边的空格

【示例】

SELECT Concat(RTrim(vend name), RTrim(vend country),')
FROM vendors
ORDER BY vend_name;

10.1.3 使用别名

【作用】：
- 自定义一个别名，作为检索结果集的字段名（列名）。
【关键字】：as

【示例】：

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
AS vend_title
FROM vendors
ORDER BY vend_name;

检索结果：

10.2 算术计算

【示例】：

# 计算订单中某一种商品的总价(单价 x 数量)
SELECT prod_id,quantity,item_price,
quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num=20005;

检索结果：
在这里插入图片描述

【算术操作符】：+、 -、 *、 /

【如何测试计算结果】：使用select

select 3*2 
返回6

select Trim('abc ')
返回abc

select now()
返回当前日期和时间

第11章：使用数据处理函数

11.1 概述

主流数据库都存在其他数据库不支持的函数，所以移植性（portable）不算强。
若复杂地使用函数，做好注释。
常见的函数分类（按用途）：
- 用于处理文本串
- 算术操作
- 处理日期和时间
- 返回数据库的系统信息（例如用户登录信息）

11.2 文本处理函数

【常见函数】：

函数	说明
Left ()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空
Soundex()	返回串的SOUNDEX
SubString()	返回子串的字符
Upper()	将串转换为大写

【示例】：upper 函数

# 查找供货商名，将其转化为大写
SELECT vend_name,Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

【关于Soundex()函数】：生成表示该字符串的发音的值。
例如，某条记录的中，有个字段的值为Y.Lee，但是搜索时输入的检索条件为Y.lie，即
```
SELECT cust_name,cust_contact
FROM customers
WHERE cust_contact ='Y. Lie';
```
则无法搜索到，而Y. Lie 与 Y.Lee 发音相近，可以使用Soundex()函数找到值为Y.Lee这条记录，如下：
```
SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');
```

11.3 日期和时间处理函数

MySQL中的日期格式必须是yyyy-mm-dd

常用日期时间处理函数：

函数	说明
AddDate ()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

【示例1】：查找日期为 2005-09-01 的全部订单

错误写法 × ：
```
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date)='2005-09-01';
```
由于日期字段类型一般为datetime，所以还包括时间部分，如果某条数据为2005-09-01 11:00:00，则使用上面的sql语句检索数据时，该类记录无法被检索出来。

正确写法 √ ：使用Date()函数
```
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date)='2005-09-01';
```
【示例2】：检索9月内的全部订单
- 【写法1】：使用between…and…
```
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
```
  注意：这种写法需要自己输入不同年份的最后一天是几号。
- 【写法2】：不需要手动设置不同月份的最后一天是几号。
```
SELECT cust_id,order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
```

11.4 数值处理函数

数值处理函数一般主要用于代数、三角或几何运算，因此没有串或日期——时间处理函数的使用那么频繁。
在主要DBMS的函数中，数值函数是最一致最统一的函数。

常用数值处理函数：

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

第12章：汇总数据（聚集函数）

聚集函数的主要作用为汇总数据，常见的检索类型有：

确定表的行数
后的表中航组的和
找出表列的最大值，最小值，平均值
…

12.1 AVG()

【作用】：对表中行数计数并计算特定列值之和，求得该列的平均值。
【示例】：求生产商id为1003的产品的平均价格
```
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id=1003;
```
其中avg_price 为别名。
【注意】：
- 如果对多列进行求平均值，则使用多个avg()函数
- avg()函数会忽略值为NULL的行。

12.2 COUNT()

【作用】：确定表中的所有记录行数，或者是符合特定条件的记录的行数。
【示例】：
```
SELECT COUNT(cust_email) AS num_cust
FROM customers;
```
需要注意的是：
- 上述sql语句中，若某条记录的cust_email字段值为null，则不会被统计到，若需要改类数据被count()函数统计到，应改为count(*)或者count(1)。

12.3 MAX()

【作用】：求该列的最大值
【示例】：求产品的列表的最高价格
```
SELECT MAX(prod_price) AS max_price
FROM products;
```
同样会忽略被统计字段值为null的记录。

12.4 MIN()

【作用】：求该列的最小值
【示例】：
```
SELECT MIN(prod_price) AS min_price
FROM products;
```
同样会忽略被统计字段值为null的记录。

12.5 SUM()

【作用】：统计某列的总和。

【示例】：

# 统计订单号为20005的订单的所有商品数量之和
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num=20005;

sum()函数也可以用来统计合计计算值，如下：

# 计算订单号为20005的订单的总价
SELECT SUM(item_price * quantity) AS total_price
FROM orderitems
NHERE order_num=20005;

如上SUM(item_price * quantity)所示，可以用于组合计算求和。

【注意】：SUM()同样会忽略被统计字段值为null的记录。

12.6 仅聚集不重复的值（聚合函数中使用distinct）

【作用】：对字段使用distinct，则仅统计不重复的数据。

【示例】：

# 仅统计不重复的价格的平均值
SELECT AVG(DISTINCT prod_price) AS avg price
FROM products
WHERE vend_id=1003;

由上 AVG(DISTINCT prod_price) 可以看到，即仅统计prod_price不同的值。

【注意】：DISTINCT后面必须更列名，则不能企图为了统计所有字段均不相同的数据，而出现count(distinct)这种情况。

12.7 组合聚集函数

检索的字段可以同时为多个聚合函数组成，如下所示：

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;

12.8 小结

聚合函数在设计上是高效，统计数据时比自行在客户机中运算统计要快得多。

第13章：分组数据

13.1 基本使用

【关键字】：GROUP BY
【作用】：根据指定的字段，对数据进行分组。

【示例】：

# 根据供货商id 对产品进行分组，统计各个供货商的产品数量
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

【注意】
- GROUP BY子句可以包含任意数目的列。
- GROUP BY 后面跟的必须是表中存在的列。
- SELECT后面所跟的字段有以下限制：
  - 必须是GROUP BY字句中出现的字段。
  - 是聚集函数。
- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。即``WHERE … GROUP BY …ORDER BY`
【WITH ROLLUP关键字】：对所有分组的检索的字段进行一个汇总，如下所示

13.2 过滤分组

【关键字】：having
【having 与 where的区别】：
- 前者过滤组，后者过滤行。
- 一般是先用where过滤行，再对其使用having过滤分组。
【示例】：
```
# 查找订单数 ≥ 2 的顾客
SELECT cust_id, COUNT(*)AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*)>=2;
```
示例中GROUP BY cust_id 订单根据顾客id进行分组， HAVING COUNT(*)>=2 即对小于2的分组进行过滤。

【where与having同时出现】

SELECT vend_id , COUNT(*) AS num_prods
FROM products
WHERE prod_price>=10 -- 先对行进行过滤，检索价格≥10的产品
GROUP BY vend_id  -- 再对产品进行分组
HAVING COUNT(*)>=2; -- 最后对组进行过滤

13.3 分组与排序

对数据进行分组后，如需要对各组进行排序，则还是需要用到order by，这里讲述==group by 与 order by同时出现==的情况。

【示例】：

SELECT order_num , SUM(quantity * item_price) AS order_total
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50
ORDER BY order_total;

【注意】：一般来说，这里ORDER BY后面出现的字段，最好是出现在Select中的字段。

13.4 select语句中各个字句的顺序

子句	说明	是否必须
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

第14章：使用子查询

14.1 子查询的结果作为父查询的条件

一般子查询作为父查询中where子句的 in 的查询条件

【示例】

SELECT cust_id
FROM orders
WHERE order_num IN (
    SELECT order_num FROM orderitems WHERE prod_id='TNT2'
);

子查询SELECT order_num FROM orderitems WHERE prod_id='TNT2'结果为：

在这里插入图片描述

整个查询相当于：

SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007);

14.2 子查询的结果父查询的其中一列

【示例】：
```
SELECT cust_name, cust_state, (
    SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id
) AS orders
FROM customers;
```
可以看到括号内的 SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id 为子查询。

其中WHERE orders.cust_id = customers.cust_id 将外层查询的customer表的cust_id与 orders表的cust_id关联起来。

查询结果为：

在这里插入图片描述

【注意】：上面示例中这两个cust_id来自不同的表，需要用不同表的表名或者别名进行区分。

第15章：联结表（join）

**【外键（foreign key）】：**外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
**【可伸缩性（scale）】：**够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）。
【笛卡儿积（cartesian product）】：即左表的记录数 * 右表记录数（n * m）

15.1 创建联结

【示例】：
```
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
```
注意：
- 示例中FROM vendors, products 、WHERE vendors.vend_id = products.vend_id 表示隐式内联接。

15.2 内联结

【隐式内联结】：如上面所示，通过where字句对两个表进行连接。

【显式内联结】：

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;

表1 inner join 表2 on ... ，这个称为显式内连接。

【注意】：
- ANSI SQL规范首选INNER JOIN语法。
- 使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

15.3 多表内联结

【示例】

# 找到订单中存在 prod_id ='TNT2' 的下单顾客的信息
SELECT cust_name,cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id ='TNT2';

where 子句中，前面两个条件是用于表之间的连接，后面的条件用于筛选连接后的数据。

第16章：创建高级联结

16.1 使用表别名

【示例】：

SELECT cust_name,cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id=o.cust_id
AND oi.order_num=o.order_num
AND prod_id='TNT2';

FROM customers AS c，表customers别名为c，这里的AS可以省略，直接写成 FROM customers c.

16.2 自联结

有时候需要将两张相同的表进行连表查询，称为自联结。

【示例】：查找产品id为DTNTR的产品对应的供应商的全部产品。
- 使用子查询实现
```
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (
    SELECT vend_id FROM products WHERE prod_id ='DTNTR'
);
```
- 使用自联结实现
```
SELECT p1.prod_id,p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id='DTNTR';
```
  p2.prod_id='DTNTR' 右表过滤id为 ‘DTNTR’ 的产品，然后p1.vend_id = p2.vend_id 左表与右表根据【供应商id】进行关联，
  
  ps. 内联结就是两个表同时符合条件的数据，才会进行联结。

16.3 自然联结

这章不是很懂啥意思。（对应16.2.2 自然联结）

16.4 外部联结

除了内联结，left join 和 right join 均为外联结。

写成left outer join或者roght outer join 亦可。

left join ：显示左表所有符合条件的行（包括没有关联上右表的行）
right join ：显示右表所有符合条件的行（包括没有关联上左表的行）

【示例】：

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

customer表右连接order表，这里会显示右表所有符合条件的表，也就是所有订单记录。

16.5 带聚集函数的联结

【示例】：检索所有客户，及每个客户所下的订单数。
```
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
```
注意这里用的是 LEFT OUTER JOIN 左联结，customers表为左表，保证没有订单的客户也能出现记录。如下所示，出现统计到0个订单的客户记录。

第17章：组合查询

17.1 概述

【什么是组合查询？】：将多个查询语句的查询结果合并为一个结果集返回。
【什么时候需要用到组合查询？】
- 单个查询，但是需要从不同的表中查询相同的结构的数据。
- 对单个表执行多个查询，按单个查询返回数据。
【注意】：【多语句union】跟【单语句多where条件】之间可以相互转换

17.2 使用 UNION 创建组合查询

利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。

【示例】：查询 “价格小于等于5的所有物品“，同时查询 “供应商1001和1002生产的所有物品”。
```
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend id IN (1001.1002):
```
可以看到，这个sql语句由两个select语句，通过union拼接而成，两个sql语句的结果集都是一样的为 vend_id,prod_id,prod_price 。

上面这条sql也可以不用union实现，而通过where字句的多个条件实现，如下所示：
```
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5
OR vend_id IN (1001,1002);
```
这两种写法得到的结果集都是一样的。

17.3 union 的使用规则

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

17.4 union all

如果使用union组合两条select语句，组合得到的结果集将会去除两个select语句结果集中的重复行，如果需要保留重复行，则需要使用union all 进行组合。

【示例】:

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5
UNION ALL
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001.1002):

17.5 对组合查询结果排序（union与order by）

如果需要对组合查询的结果集进行排序，则只能在组合查询的最后一个select语句汇总加上order by字句。

【示例】：

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002)
ORDER BY vend_id,prod_price;

虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上MySQL将用它来排序所有SELECT语句返回的所有结果。

第18章：全文本搜索（全文检索）

// TODO 用的较少，暂时跳过记录

第19章：插入数据

19.1 不指定列名插入

【示例】

INSERT INTO customers
VALUES(
    NULL,
    'Pep E. LaPew',
    '100 Main Street',
    ' Los Angeles' ,
    'CA',
    '90046',
    'USA',
    NULL,
    NULL
);

这种插入方式不用指定列名，按照创建customer表时的列排放顺序，将VALUES(）括号内的列值，插入到表中。

【注意】：严格要求VALUES(）括号内的列值与创建表的列顺序一致。且插入时，每个列都必须给出值。（无值或空值则指定为null）

19.2 指定列名插入

【示例】

INSERT INTO customers(
    cust_name,
	cust_contact,
	cust_email,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
)
VALUES(
    'Pep E. LaPew',
	NULL,
	NULL,
	'100 Main Street',
	' Los Angeles' ,
	'CA',
	'90046',
	'USA'
);

相比前面的不指定列名插入的例子，这里在INSERT INTO customers( )的括号内指定了表的列名。

【注意】：
- 推荐使用这种方式插入数据，这样即使表的列顺序发生了改变，插入语句也不用改动。
- INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。
- 可通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如下所示：
```
INSERT LOW_PRIORITY INTO ...
```

19.3 插入多个行

方式1：多个insert语句。

方式2：单个insert语句，多组次序相同的值，如下所示。

INSERT INTO customers(
    cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
)
VALUES(
	'Pep E. LaPew',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA'
),
(
	'M. Martian',
	'42 Galaxy Way',
	'New York',
	'NY',
	'11213',
	'USA'
);

可以看到，values之后跟了多个()，对应多组待插入的数值。

【注意】：MySQL用单条INSERT语句处理多个插入，比使用多条INSERT语句快

19.4 插入检索出来的数据

从一个表中查询数据，并插入另一个表中，合并到一条语句中完成。

【示例】：将custnew表的记录查询出来，再插入到customers表中。

INSERT INTO customers (
    cust_id ,
	cust_contact,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
)
SELECT cust_id,
	cust_contact,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
FROM custnew;

【注意】：
- 这个例子插入了cust_id，如果不能确定主键不会重复，则不要指定主键id的值。
- 这里的custnew表中的每个列不要求列名与customers一致，只需要对应插入顺序的列值数据类型与customers表一致即可。

第20章：更新和删除数据

20.1 更新数据

【关键字】：update...set...
【示例】：更新特定行的单列数据
```
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
```
【注意】：执行update语句时，千万要加上where字句，指定更新的范围，不然会出现全表更新的情况。

【示例】：更新多列数据

UPDATE customers
SET cust_name='The Fudds', cust_email='elmer@fudd.com'
WHERE cust id = 10005;

还是一个set，多个等号用逗号分隔。

【示例】：删除某列的值（将某列的值设置为NULL）
```
UPDATE customers
SET cust_email = NULL
WHERE cust_id=10005;
```
update语句中可以使用子查询。
【ignore关键字】：如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。可以使用IGNORE关键字，即使发生错误，也继续执行更新，如下所示：
```
UPDATE IGNORE customers…
```

20.2 删除数据

【关键字】: delete from
【示例】：
```
DELETE FROM customers WHERE cust_id=10006;
```
【注意】：delete语句同update语句一样，必须通过where指定要删除的数据，不然就变成删除整张表的全部数据了
【TRUNCATE关键字】：如果需要删除整张表的数据，则可以使用TRUNCATE关键字，其速度比delete更快，原理是删除原来表，然后重新创建一个表。示例：
```
truncate 表名
```

20.3 update与delete语句指导原则

除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
保证每个表都有主键。
在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。

第21章：创建和操纵表

21.1 创建表

21.1.1 创建表的基本操作

关键字：create table

【示例】：创建一张customer表

CREATE TABLE customers
(
    cust_id int NOT NULL AUTO_INCREMENT # 主键id自增
	cust_name char(50) NOT NULL, #  NOT NULL表示该列不能为空
	cust_address char(50) NULL,
	cust_city char(50) NULL,
	cust_state char(5) NULL,
	cust_zip char(10) NULL,
	cust_country char(50) NULL,
	cust_contact char(50) NULL,
	cust_email char(255) NULL,
	PRIMARY KEY (cust_id) # 指定主键
)ENGINE = InnoDB;

【注意】：
- 创建新表时，所指定的表名必须不存在，否则创建表失败。MySQL不支持直接覆盖掉重名的表。
- 如果仅希望在表不存在的时候才创建该表，则应该在表名后添加if not exists，例如：
```
CREATE TABLE customers IF NOT EXISTS
(
    cust_id int NOT NULL AUTO_INCREMENT # 主键id自增
	...

)ENGINE = InnoDB; 
```

21.1.2 关于`null`与空值

创建表时，可以指定某列是否可为null，如果声明列的最后标明not null，例如：cust_name char(50) NOT NULL，插入数据时，该列的值不能为空，否则插入失败。
不在列的最后标明not null，或者标明为null，则在插入行时，该列的值可以为空。

21.1.3 关于主键

如示例所示，PRIMARY KEY (cust_id)为将cust_id指定为该表的主键。

主键也可以由多个列组成，称为组合主键，例如：

CREATE TABLE orderitems
(
    order_num int NOT NULL,
	order_item int NOT NULL,
	prod_id char(10) NOT NULL,
	quantity int NOT NULL,
	item_price decimal(8,2) NOT NULL,
	PRIMARY KEY (order_num, order_item)   # <-----看这里
)ENGINE=InnoDB;

如果是组合主键，则需要保证表中任一行的主键组合是唯一的。

主键必须是不为null值。

21.1.4 关于自增`AUTO_INCREMENT`

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）
可以通过使用SELECT last_insert_id() 查询最后一个自增量。

21.1.5 指定插入默认值

如果插入行时，某列没有给出值，则MySQL此时使用默认值。默认值可以在创建表时使用关键字default设置。

【示例】：

CREATE TABLE orderitems
(
    order_num int NOT NULL,
	order_item int NOT NULL ,
	prod_id char(10) NOT NULL
	quantity int NOT NULL DEFAULT 1     # <=== 看这里 `DEFAULT 1`
	item_price decimal(8,2) NOT NULL
	PRIMARY KEY (order_num, order_item)
)
ENGINE=InnoDB;

【注意】：与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。

21.1.6 引擎类型

MySQL支持多种引擎，可以在创建表时指定所使用的引擎。
MySQL的三大引擎
- InnoDB：可靠的事务处理引擎，不支持全文本搜索。
- MEMORY：功能等同于MyISAM，数据存储在内存而不是磁盘，速度快，适合临时表。
- MyISAM：性能高，支持全文本搜索，不支持事务处理。
引擎类型可以混用（即同一个数据库，A表可以用InnoDB，B表可以用MyISAM）。
外键不能跨引擎，一个表不同引用不同引擎的表的列作为外键。

21.2 修改表结构

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

但是，理想状态下，当表中存储数据以后，该表就不应该再被更新。

在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

21.2.1 增加一个列

关键字：alter table ... add，示例如下：
```
ALTER TABLE vendors
ADD vend_phone CHAR(20);
```

21.2.2 删除一个列

关键字：alter table ... drop column

ALTER TABLE vendors
DROP COLUMN vend_phone CHAR(20);

21.2.3 添加外键

关键字： add constranint 外键名 foreign key (本表列名) references 外表名(外表列名)

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id)
REFERENCES products (prod_id);

【注意】
- 若需要对单个表进行多个修改，可以直接用一个alter table语句完成，后面跟多个更改，每个更改之间用逗号隔开即可。
- 使用alter table之前记得对原有数据做好备份，不然会导致原有的数据丢失。

21.3 删除表

需要注意的是，这里是删除整个表，而不是清空表里面的数据。
关键字：drop table 表名
【示例】：
```
drop table tb_customer;
```

21.4 重命名表

关键字：rename table ... to ...
【示例】：对单个表进行重命名
```
rename table customer to customer1
```

【示例】：对多个表进行重命名

RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products To products;

第22章：使用视图

22.1 概述

视图是虚拟的表，其数据来源于其他真实存在的表。
视图的常见应用：
- 重用sql语句。
- 简化sql操作。
- 保护数据，可以给特定用户访问部分数据，而不是整个表的访问权限。
- 更改数据格式和表示。
【性能问题】：每次使用视图是，都必须执行组成视图所需的每一个检索。如果使用了复杂的查询创建了视图，或者嵌套了视图，性能会下降得很厉害。
视图的使用规则与限制：
- 与表一样，视图必须唯一命名，不能与现有表或者视图重名。
- 视图的创建数目没有限制。
- 创建视图必须具有足够的访问权限。
- 视图可以嵌套，即可以利用其他视图中的数据来组成新的视图。
- order by可以在视图中使用，需要注意的是，若组成视图的sql语句包含order by则组成视图视图的sql语句的order by会被覆盖。
- 视图不能索引，不能关联触发器或者默认值。
- 视图可以与表一起使用，例如查询时关联表与视图。

22.2 使用视图

22.2.1 视图基本操作

创建视图：create view
查看创建视图的语句：show create view 视图名
删除视图：drop view viewname
更新视图（有两种方式）：
- 先 drop再用 create view
- 直接用 create or replace view，如果要更新的视图不存在，则会直接创建，如果要更新的视图存在，则会替换该视图。

22.2.2 简化复杂的联结

【示例】：

CREATE VIEW product_customers AS
SELECT cust_name,cust_contact,prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num = orders.order_num ;

上面示例简化了，customers, orders, orderitems这三个表的联表查询，创建视图为product_customers。如果此时需要检索【订购了产品TNT2的客户】，只需通过视图检索，如下：

# 检索 【订购了产品TNT2的客户】
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id ='TNT2';

22.2.3 重新格式化检索出的数据

查询供应商名和供应商所在地，按照一定格式拼接

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
AS vend_title
FROM vendors;

在这里插入图片描述

使用视图格式化：

CREATE VIEW vendor_locations AS
SELECT Concat(RTrim(vend_name),'(', RTrim(vend_country),')')
AS vend_title
FROM vendors;

则之后按照该格式检索供应商+地点时，只需要直接通过视图检索

SELECT * FROM vendor_locations ;

22.2.4 过滤不想要的数据

【示例】：定义视图过滤没有电子邮件的客户

CREATE VIEW customer_email_list AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL ;

22.2.5 简化计算字段

【示例】：检索某个订单的所有产品，以及产品的总价

CREATE VIEW order_items_expanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems:

22.3 更新视图

更新视图实质也是更新组成视图的表中的数据。
更新视图限制较多，创建视图的语句出现如下情况的无法更新视图：
- 分组
- 连表
- 带子查询
- 使用了union
- 带有聚合函数
- 含有distinct
- 带有导出列
视图一般用于检索，极少用于更新。

第23章：使用存储过程

23.1 概述

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
可将其视为批文件，虽然它们的作用不仅限于批处理。

23.1.1 优点

将多个操作封装在一个单元中，简化复杂的操作。
保证数据的完整性。（所有人都用同一个存储过程，减少出错几率）
简化对变动的管理。（只需要修改存储过程即可，对使用者是透明的）
提高性能。

23.1.2 缺点

编写存储过程较为复杂。
可能不具备创建存储过程的权限。（需要管理员授权）

23.2 执行存储过程

【关键字】：call

【示例】：执行名为productpricing的存储过程

CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);

23.3 创建存储过程

【关键字】：create procedure 存储过程名()、begin ... end
【示例】：创建一个返回产品平均价格的存储过程
```
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price)AS priceaverage
    FROM products;
END;
```
- 如果存储过程需要接收参数，则需要在()内指示出来。没有则不需要接收参数。
【注意】：MySQL默认的语句分隔符为;，如果存储过程体内的语句带有;，可以在外层暂时将分隔符修改为其他，如下所示：
```
DELIMITER //  # 暂时将分隔符改为 //

CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price)AS priceaverage
FROM products ;  # <== 存储过程体中的`;` 会在调用时还是当做分隔符被使用
END // # 这里的 // 充当此前的 `;`

DELIMITER ; # 将分隔符改回 `;`
```
需要注意的是，除了\符号外，任何字符都可以用作语句分隔符。

调用上面所创建的存储过程：
```
call productpricing();
```
由于存储过程实际上也是一种函数，所以需要带上()

23.4 删除存储过程

【关键字】：drop procedure 存储过程名
【示例】：
```
DROP PROCEDURE productpricing;
```
如果存储过程不存在，则会报错，可以使用 if exists 避免报错，如下
```
DROP PROCEDURE IF EXISTS productpricing;
```

23.5 使用参数

MySQL支持以下这三种方式传参。
- IN（传参给存储过程）
- OUT（从存储过程中传出）
- INOUT（对存储过程传入和传出）

【示例】：创建带OUT参数存储过程：

CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8,2),
	OUT ph DECIMAL(8,2),
	OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT Min(prod_price)
	INTO pl
	FROM products;
	SELECT Max(prod_price)
	INTO ph
	FROM products;
	SELECT Avg(prod_price)
	INTO pa
	FROM products;
END;

如示例所示，INTO为将存储过程体中的语句检索出来的数值，保存到相应的OUT后面的参数。即OUT与INTO是搭配使用。

调用上述存储过程的代码示例如下：

CALL productpricing(
    @pricelow,
	@pricehigh,
	@priceaverage
);

# PS. 这三个相当于实参，而创建存储过程时的三个参数相当于形参，两者命名不要求一直，检索存储过程的结果时以实参命名为准。

@pricelow、@pricehigh、@priceaverage这三个为传入的参数，调用存储过程不会显示任何数据，需要在通过select语句对上述三个传入的参数进行检索。

SELECT @pricehigh, @pricelow, @priceaverage

在这里插入图片描述

【示例】：创建带IN参数的存储过程。

# 接收订单号，并返回该订单的总价

CREATE PROCEDURE ordertotal(
	IN onumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num=onumber
	INTO ototal;
END;

注意这里onumber定义为IN，ototal用于接收返回的总价。调用该存储过程如下：

call ordertotal(20005,@total)

检索返回的结果

select @total

在这里插入图片描述

如果需要检索另一个订单的总价，则重新执行上述过程，传入另一个订单号即可。

23.6 在存储过程中编写业务逻辑

【示例】：编写一个计算订单总价的存储过程，可选是否需要加上购置税。

CREATE PROCEDURE ordertotal(
	IN onumber INT, # 传入的订单号
	IN taxable BOOLEAN, # 是否需要加税, 0或者1
	OUT ototal DECIMAL(8,2)
) COMMENT '计算订单总价，可选是否加税'
BEGIN
	# 存储过程体内声明一个总价的参数
	DECLARE total DECIMAL(8,2);
	
	# 声明税率为6
	DECLARE taxrate INT DEFAULT 6;
	
	# 计算订单总额
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num=onumber
	INTO total;
	
	# 判断是否需要加税  `IF ... END IF`
	IF taxable THEN 
        SELECT total+(total/100*taxrate)INTO total;
	END IF;
	
    # 将存储过程体内的总价参数，存到OUT参数中。
	SELECT total INTO ototal ;
END;

调用上面的存储过程得：

CALL ordertotal(20005,0,atotal) # 不需要加税
SELECT @total;

在这里插入图片描述

CALL ordertotal(20005,1,atotal) # 加税
SELECT @total;

在这里插入图片描述

23.7 检查存储过程

检查创建存储过程的语句：SHOW CREATE PROCEDURE 存储过程名
检查存储过程的创建时间、创建者等系信息：SHOW PROCEDURE STUTUS 存储过程名
- SHOW PROCEDURE STUTUS LIKE 'XXXXX'，可以通过这种方式过滤所有存储过程。

第24章：使用游标

24.1 概述

游标适用于MySQL 5 之后的版本。
游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制，充当指针的作用。
用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。
游标只适用于存储过程。
使用游标前需要先声明。声明游标后，必须要先打开游标才能使用，使用完毕后需要关闭游标。

24.2 使用游标

24.2.1 创建游标

【语法】：declare 游标名 cursor for 查询语句
【示例】：
```
CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernumbers CURSOR  
    FOR
    SELECT order_num FROM orders;
END;
```
- 创建一个游标名为ordernumbers的游标，用于遍历SELECT order_num FROM orders所返回的结果集。

24.2.2 打开和关闭游标

【语法】
- 打开游标：open 游标名;
- 关闭游标：close 游标名;

【示例】：

CREATE PROCEDURE processorders()
BEGIN
    -- 创建游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- 打开游标
    OPEN ordernumbers;
    
    -- 关闭游标
    CLOSE ordernumbers;
END;

24.2.3 使用游标数据

【语法】：fetch 游标名 into 本地变量

【示例】：通过游标循环遍历数据。

CREATE PROCEDURE processorders()
BEGIN

    -- 创建本地变量 o
    DECLARE o INT;
    -- 创建一个遍历结束的标记 
    DECLARE done BOOLEAN DEFAULT 0;
    
    -- 创建游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- 创建一个 handler，数据遍历完毕之后，将结束标记表示设置为1，表示遍历结束。
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    
    -- 打开游标
    OPEN ordernumbers;
    
    -- 循环遍历数据集
    REPEAT
		-- 将游标当前所指向的数据存放到局部变量o中
        FETCH ordernumbers INTO o;
    UNTIL done END REPEAT;
    
    -- 关闭游标
    CLOSE ordernumbers;
END;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; ，这里的02000是当repeat没有更多的行供循环时，所出现的错误代码。

当游标遍历到底时，出现该错误码，然后handler将变量done设置为1，则结束repeat

24.2.4 综合示例

需求：调用【23.6 在存储过程中编写业务逻辑】中的存储过程示例，检索订单列表，计算出订单的价格，并将其存入一张新表中。

CREATE PROCEDURE processorders()
BEGIN
    
    DECLARE done BOOLEAN DEFAULT 0; -- 结束遍历的标记
    DECLARE o INT;  -- 订单号
    DECLARE t DECIMAL(8,2); -- 是否需要加税
    
    -- 定义游标，遍历订单号结果集
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- handler 处理遍历结束报错
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    
    -- 创建一个新表存 订单号，订单总价
    CREATE TABLE IF NOT EXISTS ordertotals
    (
        order_num INT, 
        total DECIMAL(8,2)
    );
    
    -- 打开游标
    OPEN ordernumbers
    
    -- 遍历订单号结果集
    REPEAT
        FETCH ordernumbers INTO o;

        -- 调用23.6 的计算订单总价的存储过程
        CALL ordertotal(o,1,t)
        -- 插入到新表中
        INSERT INTO ordertotals(order_num,total) VALUES(o,t);
	
	-- 结束遍历
    UNTIL done END REPEAT;
   
   	-- 关闭游标
    CLOSE ordernumbers;
END;

第25章：使用触发器

触发器：响应增删改操作的语句。除增删改之外语句不支持触发器。

25.1 创建触发器

创建触发器时必须指定的4条信息。
- 唯一的触发器名；
- 触发器所关联的表；
- 触发器所关联的表操作（增、删、改）；
- 触发器的执行时间；（sql语句执行前，还是执行后触发）
【示例】：创建触发名为newproduct触发器，每插入一条数据到products表时，显示一次Product added。
```
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
```
【注意】
- 只有表才支持触发器，视图不支持。
- 每个表最多支持6个触发器。
- 单个触发器不能与多个事件或多个表关联。例如同一个表的insert与update操作各需要一个触发器，则定义两个触发器。
- 如果before语触发器失败，或者语句本身执行报错，则after触发器将不会被触发。

25.2 删除触发器

【语法】：drop trigger 触发器名
【注意】：触发器不能更新或者覆盖，如果需要修改一个触发器，只能删除重新创建。

25.3 使用触发器

25.3.1 INSERT 触发器

在INSERT触发器代码内，可以引用一个名为NEW的虚拟表，访问被插入的行。
可以在BEFORE INSERT触发器中，更新NEW中的值，也就是修改被插入的值。
对于AUTO_INCREMENT修饰的列，可以通过INSERT AFTER触发器，获取插入之后自动生成的值。
- 【示例】：创建触发器，每次插入数据回显最新生成order_num
```
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
```
BEFORE触发器通常用于数据校验与净化。

25.3.2 DELETE触发器

在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
OLD中的值全都是只读的，不能更新。
【示例】：创建before触发器，将OLD中保存的被删除的数据保存到一个存档表中。
```
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num,order_date,cust_id)
    VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END:
```
相比于使用after触发器，before触发器若将待删除的数据插入存档失败时，本次删除操作将会被取消。

25.3.3 UPDATE触发器

可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值；
引用一个名为NEW的虚拟表访问新更新的值；
在update触发器中，NEW中的值可以被更新，OLD中的值是只读的，不能被更新。

【示例】：创建触发器，保证州名缩写总是大写。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

25.3.4 触发器的进一步介绍

与其他DBMS相比，MySQL 5 的触发器相当初级；
创建触发器可能需要相当安全等级，但是触发器的执行是自动的，只要能执行增删改操作，对应的触发器就会被执行。
触发器的一种很有意义的使用是，操作记录追踪。或者将更新前后的数据记录到另外一个表中。
MySQL中的触发器不支持call语句，也就是MySQL中的触发器不支持调用存储过程。

第26章：管理事务处理

26.1 概述

一句话概述事务：多个sql语句，要么全部执行成功，要么其中一条执行失败，全部执行失败。

26.2 控制事务处理

26.2.1 ROLLBACK

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

【示例】：

SELECT * FROM ordertotals; -- 查询有数据

START TRANSACTION; -- 开启事务

DELETE FROM ordertotals; -- 删除表的数据（未提交）
SELECT * FROM ordertotals;  -- 查询无数据

ROLLBACK; -- 回滚

SELECT * FROM ordertotals; -- 查询有数据（回滚后数据已还原）

rollback只能在一个事务处理内使用；
事务处理可以用来处理增、删、改语句，不能回退create与drop操作。

26.2.2 COMMIT

一般的SQL语句，MySQL默认都是会为我们执行自动commit的。
但是在事务处理中，其不会自动为我们提交，需要我们手动地使用commit语句。
【示例】: 删除订单，以及订单所关联的商品。
```
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
```
- 这里如果其中一条 delete语句执行失败，则最后的commit语句将不会被执行（自动回滚）。
- commit或者rollback被执行后，事务会自动关闭。

26.2.3 SAVEPOINT

一个事务中，如果出现一条语句执行失败，默认是整个事务的语句全部回滚。
如果不希望全部回滚，而是回滚到指定位置，可以通过savepoint指定回滚的位置。

【示例】：

START TRANSACTION;

DELETE FROM orderitems WHERE order_num = 20010;

SAVEPOINT delete1  -- 回滚点

DELETE FROM orders WHERE order_num = 20010;

ROLLBACK TO delete1 -- 回滚到delete1，并且提交事务（记住rollback也是会提交事务的。）
COMMIT;

保留点再事务处理完成后自动释放，也可以通过语句release savepoint 手动释放。

26.2.4 更改默认的事务提交行为

【语句】：
```
set autocommit = 0;
```
【注意】：这个autocommit标志是针对每个连接的，而不是直接修改了服务器的autocommit。

第27章：全球化和本地化

【术语】：
- 字符集：字符和符号的集合。
- 编码：某个字符集成员的内部表示。
- 校对：规定字符如何比较的指令。（影响排序以及搜索，例如搜索apple，能否找到APPLE）
  - _cs：区分大小写
  - _ci：不区分大小写
使用何种字符集和校对的决定在服务器、数据库和表级进行。
查看所有支持的字符集：
```
show character set ;
```
查看所支持的校对的完整列表
```
show collation;
```
指定字符集和校对很少在服务器级别或者是数据库级别进行指定。一般在创建表时指定，如下：
```
CREATE TABLE mytable
(
    columnn1 INT,
    columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
```
- 若指定character set ，collate，则使用指定值。
- 如果仅指定character set，则使用其对应的默认的collate
- 若两者均不指定，则使用数据库默认的。

也可以在创建表时，对某一列进行指定字符集和校对

CREATE TABLE mytable
(
    columnn1 INT,
    columnn2 VARCHAR(10),
    column3 VARCHAR(10) CHARACTER SET latin1 COLLATE 1atin1_general_ci
) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

不同的校对对检索结果的排序，可以在检索时指定本次检索使用何种校对：
```
SELECT * FROM customers
ORDER BY lastname, firstname 
COLLATE latin1_general_cs;  -- 进行一次区分大小写的检索
```
除了这里看到的在ORDER BY子句中使用以外，COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等。
字符串可以通过函数Cast()、Convert()进行转换。

第28章：安全管理

MySQL的用户账号信息存储在名为mysql的表中，查询所有用户账号列表可用：
```
SELECT user FROM user;
```

28.1 创建新用户账号

CREATE USER ben IDENTIFIED BY 'p@$$wOrd';

ben：用户名
IDENTIFIED BY 'p@$$wOrd'：该账号密码为p@$$wOrd
使用GRANT或INSERT GRANT语句也可以创建用户账号，但一般来说CREATE USER是最清楚和最简单的句子。

28.2 重新命名用户账号：

-- 将用户名从 ben 改为 bforta
rename user ben to bforta

28.3 删除用户账号

drop user 用户名

在MySQL 5以前，DROP USER只能用来删除用户账号，不能删除相关的权限。

因此，如果使用旧版本的MySQL，需要先用REVOKE删除与账号相关的权限，然后再用DROP USER删除账号。

28.4 设置访问权限

查看用户bforta的权限
```
show grants for bforta
```
输出结果为：
```
GRANT USAGE ON * T0 'bforta'@'%' -- 表示任意表无权限
```
- 'bforta'@'%'：用户名@主机名。主机名不设置的话默认是%，表示任意主机。

授予用户权限，包含3方面：要授予的权限、被授予权限的数据库或者表、用户名。

# 授予 用户bforta 数据库crashcourse 所有表的 SELECT 权限
GRANT SELECT ON crashcourse.* To bforta;

撤回用户权限：

REVOKE SELECT ON crashcourse.* FROM bforta;

GRANT和REVOKE可在几个层次上控制访问权限：
- 整个服务器grant all 以及 revoke all；
- 特定的数据库ON 数据库名.*；
- 特定的表ON 数据库名.表名；
- 特定的列；
- 特定的存储过程。
可授予或者撤销的权限列表：

权限	说明
ALL	除`GRANTOPTION`外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTINE	使用`ALTER PROCEDURE`和`DROP PROCEDURE`
CREATE	使用`CREATE TABLE`
CREATE ROUTINE	使用`CREATE PROCEDURE`
CREATE TEMPORARY TABLES	使用`CREATE TEMPORARY TABLE`
CREATE USER	使用`CREATE USER`、`DROP USER`、`RENAME USER`和`REVOKE ALL PRIVILEGES`
CREATE VIEW	使用`CREATE VIEW`
DELETE	使用`DELETE`
DROP	使用`DROP TABLE`
EXECUTE	使用`CALL`和存储过程
FILE	使用`SELECT INTO OUTFILE` 和 `LOAD DATA INFILE`
GRANT OPTION	使用`GRANT`和`REVOKE`
INDEX	使用`CREATE INDEX`和`DROP INDEX`
INSERT	使用`INSERT`
LOCK TABLES	使用`LOCK TABLES`
PROCESS	使用`SHOW FULL PROCESSLIST`
RELOAD	使用FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用`SELECT`
SHOW DATABASES	使用`SHOW DATABASES`
SHOW VIEW	使用`SHOW CREATE VIEW`
SHUTDOWN	使用`mysqladmin shutdown`(用来关闭MySQL)
SUPER	使用`CHANGE MASTER`、`KILL`、`LOGS`、`PURGE`、`MASTER`和`SET GLOBAL`。还允许`mysqladmin`调试登录
UPDATE	使用`UPDATE`
USAGE	无访问权限

授予多个权限可以使用,进行分隔，撤回同理
```
GRANT SELECT, INSERT ON crashcourse.* To bforta;
```

28.5 修改用户密码

SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。
```
SET PASSWORD FOR 用户名 = Password('n3w p@$$wOrd');
```
如果不指定用户名，则修改的是当前登录用户的密码
```
SET PASSWORD = Password('n3w p@$$wOrd');
```