mysql数据库mysql零基础入门到进阶MySQL数据库入门

数据库是往全栈发展不得不跨过的一道坎，大家不可避免会学到用到相关知识，最近查资料的时候发现网上很多内容要么就特别深，要么不成体系，对一些希望浅尝辄止仅仅是使用一下的人不太友好。最近刚好有机会学到MySQL，集中一些时间学习了一下MySQL同时做了一些笔记，每个概念基本都有代码示例，每一行都是在下手打，读者可以直接复制了代码到命令行中运行，希望对大家有所帮助～

本文介绍的知识都不是特别深，目标用户是对MySQL零基础或弱基础的小伙伴们，可以帮助对MySQL建立一些概念，至少碰到相关问题知道怎么去百度，也不会碰到后端给的数据库文件看不懂。

对于Docker和CentOS相关知识不了解的小伙伴可以看看<手摸手带你Docker从入门到实践>和<半小时搞会CentOS入门必备基础知识>两篇文章，反正Docker和CentOS也早晚会用到

所有代码保存在Github上，可以自行Clone下来阅读和执行。

CentOS版本：7.6

MySQL版本：8.0.21

上面这个脑图可以加文末公众号回复「mysql脑图」获取xmind源文件。

1.什么是数据库

数据库是一个以某种有组织的方式存储的数据集合，可以将其想象为一个文件柜。

1.1基本信息

MySQL数据库隶属于MySQLAB公司，总部位于瑞典，后被oracle收购。是目前最流行的关系型数据库。

优点：

1. 成本低：开放源代码，一般可以免费试用；
2. 性能高：执行很快；
3. 简单：很容易安装和使用。

1.2MySQL安装

MySQL建议使用Docker安装，几行命令就安装好了，参见<手摸手带你Docker从入门到实践>-安装MySQL

我这里的命令是：

#创建mysql容器dockerrun-d-p3307:3306-eMYSQL_ROOT_PASSWORD=888888\-v/Users/sherlocked93/Personal/configs/mysql.d:/etc/mysql/conf.d\-v/Users/sherlocked93/Personal/configs/data:/var/lib/mysql\--namelocalhost-mysqlmysql#创建好之后进入mysql容器：dockerexec-itlocalhost-mysqlbash#登录mysqlmysql-uroot-p888888

如果你机子上安装了navicate，可以参考一下下面这个配置

选择NewConnection之后填一下配置：

就可以看到你数据库里面的内容了。

就可以啦，效果如下图：

不用Docker可以去官网MySQLCommunityServer下载对应版本的MySQL安装包，CommunityServer社区版本是不要钱的，下载安装完毕也可以，基本一直下一步就行了。

废话少说，下面直接开始知识灌体！

2.MySQL简单使用

2.1数据库相关术语

数据库相关的概念和术语：

1. 数据库(database)保存有组织的数据的容器；
2. 表(table)某种特定类型数据的结构化清单；
3. 模式(schema)关于数据库和表的布局及特性的信息；
4. 列(column)表中的一个字段，所有表都是由一个或多个列组成的；
5. 数据类型(datatype)所容许的数据的类型；
6. 行(row)表中的一个记录；
7. 主键(primarykey)一列(或一组列)，其值能够唯一区分表中每个行；
8. 外键(foreignkey)表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
9. 子句(clause)SQL语句由子句构成，有些子句是必需的，而有的是可选的。比如select语句的from子句。

2.2主键

主键的概念十分重要，它唯一标识表中每行的单个或者多个列称为主键。主键用来表示一个特定的行。

虽然并不总是都需要主键，但应尽量保证每个表都定义有主键，以便于以后的数据操纵和管理。没有主键，无法将不同的行区分开来，更新或删除表中特定行很困难。

表中的任何列都可以作为主键，只要它满足以下条件：

1. 任意两行都不具有相同的主键值；
2. 每个行都必须具有一个主键值（主键列不允许NULL值）。

在使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的（单个列的值可以不唯一)。

几个普遍认可的最好习惯为：

1. 不更新主键列中的值；
2. 不重用主键列的值；
3. 不在主键列中使用可能会更改的值。

2.3语法规范

语法规范：

1. 输入help或\h获取帮助；
2. 不区分大小写，但建议关键字大写，表名、列名小写；
3. 每条命令最好使用分号;或\g结尾，仅按Enter不执行命令；
4. 每条命令根据需要，可以进行缩进、换行；
5. 用#开头进行多行注释，用/*...*/进行多行注释；
6. 输入quit或exit推出MySQL命令行；

语法特点：

1. 大小写不敏感；
2. 可以写在一行或多行，可以分成多行以便于阅读和调试；
3. 关键字不能被缩写也不能分行；
4. 各子句一般分行写；
5. 推介使用缩进提高语句的可读性；

常见的简单命令：

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出

2.4创建表并填充数据

首先我们整点数据，方便后面的代码演示。

mysql-uroot-p888888#输入用户名密码，进入mysql命令行

然后在Github下载文件create.sql并运行（也可以直接复制文件里的内容到MySQL命令行中执行）。

如果你用的是navicate，在上一章创建到localhost-mysql的连接后，运行一下即可：

同理运行另一个文件populate.sql，填充每个表中的数据。

运行之后在命令行中showtables就可以看到库中的表了，如下图：

2.5关系表

简单介绍一下刚刚创建好的表。

为了数据分类处理，顾客customers、供应商vendors、订单orders、订单信息orderitems、产品记录productnotes、产品products表分别存储不同的信息。

比如供应商信息表vendors总每个供应商都有一个唯一标识，也就是主键vend_id，而products产品表的每个产品也有一个主键prod_id，还有一个字段vend_id供应商ID和供应商表中的vend_id一一对应，这就是外键。

如果你希望通过产品ID查到对应的供应商信息，那么就通过外键来找到另一个表中的信息。外键避免了每个产品都重复保存供应商的详细信息，只要保存供应商的ID就行，当供应商信息变了，比如邮箱、地址变更，也不用挨个改每一行的数据，只需更改供应商表中对应供应商信息。

这样做的好处：

1. 供应商信息不重复，从而不浪费时间和空间；
2. 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；
3. 由于数据无重复，显然数据是一致的，这使得处理数据更简单。

可伸缩性(scale)，能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scalewell)。

2.6数据类型

MySQL数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。

数值型

整型：Tinyint、Smallint、Mediumint、Int（Integer）、Bigint，可以为无符号和有符号，默认有符号。

1. 如果不设置有无符号默认是有符号，如果想设置无符号，可以添加unsigned关键字；
2. 如果插入的数值超出了整型的范围，会报outofrange异常，并且插入临界值；
3. 如果不设置长度，会有默认的长度。

小数

1. 定点数：dec(M，D)、decimal(M，D)
2. 浮点数：float(M,D)、double(M,D)

M为整数部位小数部位，D为小数部位，M和D都可以省略。如果是decimal，则M默认为10，D默认为0。

字符型

1. 较短的文本：char(n)、varchar(n)中的n代表字符的个数，不代表字节个数。
2. 较长的文本：text（长文本数据）、blob（较长的二进制数据）。
3. binary、varbinary用于保存较短的二进制。
4. enum用于保存枚举。
5. set用于保存集合。

日期和时间类型

1. date格式YYYY-MM-DD，保存日期；
2. time格式HH:MM:SS，保存时间；
3. year格式YYYY，保存年；
4. datetime格式YYYY-MM-DDHH:MM:SS，保存日期时间，范围1000-9999，不受时区印象；
5. timestamp时间戳，格式保存日期时间，范围1970-2038，受时区影响；

3.检索数据select

用来查询的select语句大概是最常用的了，用来从一个或多个表中检索信息，一条select语句必须至少给出两条信息：想选择什么、从什么地方选择。

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();

3.1去重distinct

查询出来的结果可能有多个重复值，可以使用distinct关键字来去重

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重

3.2限制结果limit

select语句返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用limit子句。

limitm表示返回找出的前m行，limitm,n表示返回第m行开始的n行，也可以使用limitmoffsetn含义和前面一样。

注意，检索出来的第一行的索引为0行。

3.3完全限定表名与列名

在某些情况下，语句可能使用完全限定的列明与表名：

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;

4.排序检索数据orderby

上一章从orderitems这个表中检索的数据是没有排序的，一般情况下返回的顺序是在底层表中出现的顺序。可以通过orderby子句来对检索后的数据进行排序。

可以用asc、desc关键字来指定排序方向。orderbyasc升序、orderbydesc降序，不写默认是升序。

orderby子句中可以支持单个字段、多个字段、表达式、函数、别名，一般放在句子的最后面，除了limit之外。

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;

5.过滤数据where

在from子句后使用where关键字可以增加筛选条件，过滤数据。

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];

按条件表达式来筛选>、=、<、>=、<=、!=、<>、<=>

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;

5.1范围检查betweenand

使用between...and...指定所需范围的开始值和结束值，可以达到范围查询的效果。

注意betweenand左右数字是按小大的顺序的，调过来不行。

#查询产品价格在3到10内的产品selectprod_name,prod_pricefromproductswhereprod_pricebetween3and10;#单独使用and也可以打到这个效果selectprod_name,prod_pricefromproductswhereprod_price<=10andprod_price>=3;

5.2空值检查is(not)null

创建表时，可以指定某些列可以不包含值，即可以为null，null表示无值novalue，这与字段包含0、空字符串或仅仅包含空格不同。

使用isnull或isnotnull可以用来判断一个值是否为null。

说明：

1. 等于=和不等于<>、!=不能用来判断null，只能用isnull和isnotnull来判断null
2. <=>安全等于号可以用来判断null

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出0

5.3逻辑与操作符and

操作符(operator)用来联结或改变where子句中的子句的关键字，也称为逻辑操作符(logicaloperator)。

前文提到了and操作符，通过这个操作符可以增加限定条件：

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出1

5.4逻辑或操作符or

or操作符和and操作符相反，这是逻辑或操作符，返回匹配任一条件的行：

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出2

在and和or同时出现时，会优先处理and，比如这句：

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出3

这句会先处理and，表示vend_id为1003且prod_price大于等于10的产品，或者vend_id为1001的产品。

遇到这种情况，可以通过增加圆括号：

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出4

这样检索的结果就是vend_id为1001或1003的产品里，所有prod_price大于等于10的产品列表了。

任何时候使用具有and和or操作符的where子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此，而且使用圆括号能消除歧义，增加可读性。

5.5范围操作符in(set)

使用in操作符可以指定条件范围，范围中的每个条件都可以进行匹配。in要匹配的值放在其后的圆括号中：

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出5

in操作符可以用or来取代，在以下情况建议使用in：

1. 在选项比较多时，in更清楚且更直观；
2. 使用in时，计算的次序更容易管理（因为使用的操作符更少）；
3. in一般比or操作符清单执行更快；
4. in的最大优点是可以包含其他select语句，使得能够更动态地建立where子句。

5.6逻辑否操作符not

not否操作符可以和前面的in和betweenand一起使用，表示对范围取反：

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出6

5.7like操作符

比如想找出名称中包含anvil的所有产品，可以通过like操作符来进行搜索。like表示后面跟的搜索模式使用通配符匹配而不是直接相等匹配。

操作符%

最常使用的通配符是%操作符，%表示任意多个字符，包括没有字符。

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出7

注意，%是无法匹配null的。

操作符_

_表示任意单个字符。

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出8

另外，转译使用\，比如\_

mysql-uroot-p#–h主机名–u用户名-P端口号–p密码，注意-p跟密码之间不能加空格其他可以加可以不加selectversion();#查看mysql服务版本showdatabases;#查看所有数据库，注意最后有screatedatabase[库名];#创建库use[库名];#打开指定的库showtables;#查看当前库的所有表showtablesfrom[库名];#查看其他库的所有表desc[表名];#查看表结构createtable[表名](#创建表列名列类型,列名列类型,);dropdatabase[库名];#删除库droptable[表名];#删除表exit;#退出9

注意：

1. 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
2. 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

5.8正则表达式regexp

关于正则表达式，可以先简单看一下「正则表达式必知必会」这篇博客。

使用regexp关键字来表示匹配其后的正则表达式：

mysql-uroot-p888888#输入用户名密码，进入mysql命令行0

正则表达式中转译使用\\，比如希望查找.这个字符而不是正则中的.通配符，使用\\.，为了转移\这个字符，使用\\\

mysql-uroot-p888888#输入用户名密码，进入mysql命令行1

6.计算字段

有时候我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化，这时我们就需要计算字段了。

6.1别名as

查询出来的虚拟表格可以起一个别名，方便理解，可读性好，另外如果查询的字段有重名的情况，可以使用别名as来区分开来。

mysql-uroot-p888888#输入用户名密码，进入mysql命令行2

6.2拼接concat

想把多个字段连接成一个字段，可以使用到拼接字段函数concat：

mysql-uroot-p888888#输入用户名密码，进入mysql命令行3

注意中间如果有任何一个数据为null，拼接的结果也是null。

所以对某些可能为null的字段要使用ifnull函数判断一下，第一个参数为要判断的字段，第二个参数是如果是null希望返回的结果：

mysql-uroot-p888888#输入用户名密码，进入mysql命令行4

如果表中的数据前后有空格，可以使用rtrim()函数去除右边空格，ltrim()去除左边空格，或者trim()去除前后空格：

mysql-uroot-p888888#输入用户名密码，进入mysql命令行5

6.3算术计算-*/

基本的算术运算符在select语句中也是支持的：

mysql-uroot-p888888#输入用户名密码，进入mysql命令行6

基本运算符加减乘除都是支持的、-、*、/。

7.数据处理函数

前面介绍的去除数据首位空格的trim()函数就是数据处理函数，除此之外还有多种其他类型的数据处理函数：

1. 用于处理文本串的文本函数，如删除或填充值，转换值为大写或小写。
2. 用于在数值数据上进行算术操作的数值函数，如返回绝对值，进行代数运算。
3. 用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数，例如，返回两个日期之差，检查日期有效性等。
4. 系统函数，如返回用户登录信息，检查版本细节。

在不了解如何使用一个函数的时候，可以使用help命令，比如helpsubstr就可以获取substr的使用方式和示例。

7.1字符函数

示例：

mysql-uroot-p888888#输入用户名密码，进入mysql命令行7

7.2数学函数

示例：

mysql-uroot-p888888#输入用户名密码，进入mysql命令行8

7.3日期函数

示例：

mysql-uroot-p888888#输入用户名密码，进入mysql命令行9

日期格式符：

7.4聚集函数

聚集函数(aggregatefunction)运行在行组上，计算和返回单个值的函数。

示例：

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();0

8.分组数据

之前的聚集函数都是在where子句查询到的所有数据基础上进行的计算，比如查询某个供应商的产品平均价格，但假如希望分别返回每个供应商提供的产品的平均价格，该怎么处理呢。这该用到分组了，分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

8.1创建分组groupby

使用groupby子句可以指示MySQL按某个数据排序并分组数据，然后对每个组而不是整个结果集进行聚集。

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();1

注意：

1. groupby子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2. 如果在groupby子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
3. groupby子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在select中使用表达式，则必须在groupby子句中指定相同的表达式。不能使用别名。
4. 除聚集计算语句外，select语句中的每个列都必须在groupby子句中给出。
5. 如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，它们将分为一组。
6. groupby子句必须出现在where子句之后，orderby子句之前。

8.2过滤分组having

除了能用groupby分组数据外，MySQL还允许使用having关键字过滤分组，指定包括哪些分组，排除哪些分组。

语法顺序如下：

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();2

where过滤没有分组的概念，指定的是行而不是分组，针对分组的过滤使用having子句。事实上，目前为止所学过的所有类型的where子句都可以用having来替代。

关于having和where的差别，这里有另一种理解方法，where在数据分组前进行过滤，having在数据分组后进行过滤。where排除的行不包括在分组中，这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组。

能用分组前筛选where的，优先考虑分组前筛选。

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();3

9.子查询

子查询(subquery)，嵌套在其他查询中的查询。

9.1使用子查询进行过滤

当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询，外面的select语句称为主查询或外查询。

之前所有查询都是在同一张表中的，如果我们想获取的信息分散在两张甚至多张表呢，比如要从订单表orders中获取顾客ID，然后用顾客ID去顾客表custormers找到对应顾客信息。

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();4

这里实际上有三条语句，最里边的子查询返回订单号列表，此列表用于其外面的子查询的where子句。外面的子查询返回顾客ID列表，此顾客ID列表用于最外层查询的where子句。最外层查询最终返回所需的数据。

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

9.2相关子查询

相关子查询(correlatedsubquery)涉及外部查询的子查询。

使用子查询的另一方法是创建计算字段。假如需要显示customers表中每个顾客的订单总数。订单与相应的顾客ID存储在orders表中。

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();5

注意到上面这个whereorders.cust_id=customers.cust_id，这种类型的子查询叫做相关子查询，任何时候只要列名可能有多义性，就必须使用完全限定语法（表名和列名由一个句点分隔)。

10.联结表

如果要查的数据分散在多个表中，如何使用单条select语句查到数据呢，使用联结可以做到。

联结是一种机制，用来在一条select语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

维护引用完整性：在使用关系表时，仅在关系列中插入合法的数据非常重要。如果在products表中插入拥有没有在vendors表中出现的供应商ID的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。

为防止这种情况发生，可指示MySQL只允许在products表的供应商ID列中出现合法值（即出现在vendors表中的供应商）。这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的。

10.1创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();6

这里在where后面用完全限定列名方式指定MySQL匹配vender表的vend_id列和products表的vend_id字段。

当引用的列可能有歧义时，必须使用完全限定列名的方式，因为MySQL不知道你指的是哪个列。

在联结两个表时，实际上做的是将一个表的每一行与另一个表的每一行配对，所以where子句作为过滤条件，过滤出只包含指定联结条件的列wherevendors.vend_id=products.vend_id，没有where子句，将返回两个表的长度乘积个字段，这叫笛卡尔积（cartesianproduct），可以运行一下这句看看：

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();7

所有联结应该总是使用联结条件，否则会得出笛卡尔积。

10.2联结多个表

一条select语句也可以联结多个表，比如需要把某个订单的产品信息、订单信息、供应商信息都列出来，要找的产品信息分散在供应商、产品、订单信息三个表中。

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();8

这里使用and来连接多个联结条件，定义了3个表之间用什么作为关联。

注意：MySQL在运行时关联多个表以处理联结可能是非常耗费资源的，不要联结不必要的表。联结的表越多，性能下降越厉害。

这里可以使用联结来实现9.1节的例子，之前是使用子查询来实现的，从订单表orders中获取顾客ID，然后用顾客ID去顾客表custormers找到对应顾客信息。

#基本语法select[查询列表]from[表名];#查询单个/多个/所有字段selectcust_namefromcustomers;selectcust_name,cust_city,cust_addressfromcustomers;select`select`fromcustomers;#如果某字段是关键字，可以用`符号包起来select*fromcustomers;#*表示所有#查询常量值/表达式/函数select100;select'zhangsan';select100%98;selectversion();9

这里提一句，不仅仅列可以起别名，表也可以起，用法跟列的别名一样：

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重0

这样不仅仅可以缩短SQL语句，也允许在单条select语句中多次使用相同的表，同时起的别名不仅可以用在select子句，也可以使用在where、orderby子句以及语句的其他部分。

10.3内部联结innerjoin

目前为止所用的联结称为等值联结(equijoin)，它基于两个表之间的相等测试，也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。下面的select语句返回与前面例子完全相同的数据：

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重1

这里的联结条件使用on子句而不是where，这两种语法都可以达到效果。尽管使用where子句定义联结的确比较简单，但使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

10.4自联结

比如某个产品出现了质量问题，现在希望找出这个产品的供应商提供的所有产品信息。按照之前介绍的子查询，我们可以先找到对应产品的供应商，然后找到具有这个供应商ID的产品列表：

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重2

使用子查询确实可以实现，使用联结也可以做到，这就是自联结：

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重3

自联结查询的两个表是同一个表，因此products表需要分别起别名，以作为区分，而且select子句中出现的列名也需要限定表明，因为两个表都出现了相同的字段。

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

10.5自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

自然联结就是你只选择那些唯一的列，这一般是通过对表使用通配符，对所有其他表的列使用明确的子集来完成的。

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重4

10.6外部链接outerjoin

有些情况下，联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结。

比如：

• 对每个顾客下了多少订单进行计数，包括那些至今尚未下订单的顾客;
• 列出所有产品以及订购数量，包括没有人订购的产品；
• 计算平均销售规模，包括那些至今尚未下订单的顾客。

此时联结需要包含哪些没有关联行的那些行。

比如检索所有用户，及其所下的订单，没有订单的也要列举出来：

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重5

在使用outerjoin语法时，必须使用right或left关键字指定包括其所有行的表。right指出的是outerjoin右边的表，而left指出的是outerjoin左边的表。上面使用leftouterjoin从from子句的左边表custermers中选择所有行。为了从右边的表中选择所有行，应该使用rightouterjoin。

左外部联结可通过颠倒from或where子句中表的顺序转换为右外部联结，具体用哪个看你方便。

10.7使用带聚集函数的联结

比如想检索一个顾客下过的订单数量，即使没有也要写0，此时使用分组和count聚集函数来统计数量：

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重6

因为即使顾客没有下单，也要在结果里，所以把顾客表放在左边，用左外部联结。

11.组合查询

MySQL允许执行多条select语句，并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compoundquery)。

有两种情况需要使用组合查询：

1. 在单个查询中从不同的表返回类似结构的数据；
2. 对单个表执行多个查询，按单个查询返回数据。

多数情况下，组合查询可以使用具有多个where子句条件的单条查询代替。具体场景可以尝试一下这两种方式，看看对特定的查询哪一种性能更好。

11.1创建组合查询union

当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用组合查询。在每条select语句之间放上关键字union即可。

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重7

1. 有些情况下，比如更复杂的过滤条件、需要从多个表中检索数据的情况下，使用union可能会更简单。
2. union每个查询必须包含相同的列、表达式、聚集函数，不过每个列不需要以相同的次序列出。
3. 列数据类型必须兼容，类型不必完全相同，但必须是数据库管理系统可以隐式的转换。
4. 组合查询的排序orderby只能出现在最后一条select语句之后，不能对不同的select语句分别排序。

11.2包含或取消重复的行union(all)

两行union分开的语句可能会返回重复的行，但前面那个例子实际结果却并没有包含重复行，这是因为union关键字自动去除了重复的行，如果不希望去重，可以使用unionall关键字。

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重8

如果需要出现重复行，此时无法使用where关键字来达成同样的效果了。

12.数据的增删改

前面说的都是数据的查询，这一章将所以说数据的增删改。

12.1数据插入insertinto

数据插入使用insert关键字，它可以插入一行、多行数据，也可以插入某些查询的结果。

selectorder_numfromorderitems;#有一些重复值selectdistinctorder_numfromorderitems;#将重复值去重9

这里插入一条数据到顾客表中，存储到每个表列中的数据需要在values子句中给出，按照表在创建的时候的顺序依次给出。如果某个列没值就给null。虽然第一条数据对应cust_id列的属性是notnull的，但是这个列是auto_increment也就是自增的，MySQL会自动忽略你给出的null并将值自动增加再填充。

但使用上面values子句这种方式并不安全，因为这种方式注入数据完全靠输入数据的顺序，如果表结构变动，就会导致输入数据错位。

安全的数据插入方式是这样的：

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;0

这里在前一个括号给出了后面括号中数据对应的列名，这样的话即使表结构或者顺序发生变化，也能正确插入数据。

可以看到列cust_id被省略了，当满足下面条件时，列可以省略：

1. 列定义为允许null值；
2. 表定义时这个列给出了默认值，表示如果不给值则使用默认值。

如果不能省略却省略了，会报错。

insert操作可能很耗时，特别是有很多索引需要更新时，而且它可能降低等待处理的select语句的性能。如果数据检索是最重要的，你可以通过在insert和into之间添加关键字low_priority，降低insert语句的优先级，这也同样适用于下文提到的update和delete语句。

12.2插入多个行

上面介绍的insert语句可以一次插入一个行，如果想一次插入多个行，每次都列出列名就比较繁琐了，可以使用下面这种方式：

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;1

values子句后面继续用括号将字段括起来添加新行，中间加个逗号。这可以提高数据库处理的性能，因为单条insert语句处理多个插入比使用多条insert语句快。

12.3插入检索出的数据insertselect

insert可以将一条select语句的结果插入表中，这就是insertselect。比如你想将另一个表中查询的数据插入到这个表中：

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;2

select的新行也可以省略cust_id，这样insert的时候也是可以自动生成新的cust_id的。另外可以看到select语句的列名跟insertinto语句后的列名并不对应，这也没关系，因为insertselect使用的是位置对应，select语句返回的第一列对应cust_id，第二列对应cust_name，依次对应。select语句后也可以加入where子句进行过滤。

12.4修改数据update

update语句用来修改表中的数据，使用update的时候一定要小心，不要忘了添加where子句，因为一不小心就会更新表中所有行。

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;3

如果这里没有使用where子句，update将会更新这个表中的所有行的cust_email字段，所以一定要注意。

要删除某行某列的值，可以将值修改为null。

更新多个字段的方式也很简单：

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;4

如果用update语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个update操作被取消(错误发生前更新的所有行被恢复到它们原来的值)。为即使是发生错误，也继续进行更新，可以在update后使用ignore关键字。

update语句可以使用子查询，用select语句检索出的数据来更新列数据。

12.5删除数据delete

delete语句可以用来从表中删除特定的行或者所有行。使用delete语句的时候要小心，不要忘了添加where子句，因为一不小心就会删除表中所有行。

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;5

如果将where子句去掉，那么就是删除这个表中的所有行，但不是删除这个表，删除表使用的是另一个语句drop。另外删除一个表中所有行更快的语句是truncatetable，因为delete是逐行删除数据，而truncate是删除原来的表重新建个表。

注意，在使用update和delete之前，应该非常小心，因为MySQL没有撤销，使用之前建议先使用select进行测试，防止where子句不正确导致数据丢失。

13.创建和操作表

13.1创建表createtable

我们可以打开之前为了整点数据执行的create.sql文件看看，用VSCode/Webstorm/Navivate/文本都能打开，这个文件除了注释的第一行就是创建表语句：

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;6

从这里可以看到createtable的格式。

如果要在一个表不存在时创建，应该在表名前、createtable后加上ifnotexists。这样会先检查表名是否已存在，并且在不存在时进行创建。

对于auto_increment，每个表只能有一个auto_increment，而且它必须被索引。当你使用insert语句插入一个新的值，后续自动增量将从这个值重新开始增加。如果一个表创建新的列需要得到最auto_increment的值，可以使用last_insert_id()来获取最后自增的值。

上面创建语句的列名后null表示这个列在插入和修改时允许不给出值，如果是notnull，那么在插入或修改时就必须给值，否则会报错。默认为null，如果不显式的给出notnull，则会默认为null。

primarykey指示主键的值，在插入时主键值必须是不重复的，主键也可以是多个字段primarykey(cust_id,cust_name)用逗号分开。作为主键的列是不能允许null的。

default关键字可以指定默认值，如果插入行没有指定默认值，那么将默认使用默认值。

最后的engine字段指定了不同的引擎，以下是MySQL支持的几个常用的引擎：

1. InnoDB可靠的事务处理引擎，不支持全文搜索。
2. MEMORY功能等同于MyISAM，但由于数据存储在内存，速度很快，适合于临时表。
3. MyISAM默认是这个，性能高，支持全文搜索，不支持事务。

根据不同需要可以选择不同引擎。

13.2修改表 altertable

修改表使用 altertable语句，一般情况下，当表中开始存储数据后，就不应该再修改表。所以表在设计时需要大量时间来考虑，尽量在后期不对表进行大的改动。

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;7

altertable经常用来定义外键foreignkey，用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值。外键具有保持数据完整性和一致性的机制，对业务处理有着很好的校验作用。

外键用来在从表添加外键约束，用于引用主表中某列的值，比如学生表的专业编号，员工表的部门编号，员工表的工种编号，产品表的供应商编号等等。

可以从create.sql文件最下面看到外键的例子，这里列举一行：

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;8

设置外键之后，如果外键已经有对应数据，就不能直接删除主表的这个外键行了：

selectorderitems.order_numfrommysql_demo1.orderitems;#上面这句等价于selectorder_numfromorderitems;9

所以在插入数据时，先插入主表，再插入从表。删除数据时，先删除从表，再删除主表。

注意：使用 altertable要小心，最好在改动之前做一个完整的备份，数据库表的更改不能撤销。如果不小心增加了不需要的列，可能无法删除它们，如果删除了不该删除的列，可能就丢失了数据。

13.3删除表droptable

删除一个表可以使用droptable关键字。

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;0

删除表没有确认，也没有撤销，执行后将永久删除该表。

如果删除时不存在这个表会报错，可以在droptable关键字后加上ifexists，这样数据库会先检查这个目标表是不是存在：

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;1

创建新表时，指定的表名必须不存在，否则会报错，所以在创建前也可以执行这个句子。

13.4重命名表renametable

重命名一个表可以使用renametable关键字。

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;2

14.视图

视图是虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存SQL逻辑，不保存查询结果。

14.1创建视图createview

比如说现在要查询购买了TNT2产品的顾客信息，按之前介绍的知识使用联结从三个表中查找：

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;3

那如果现在要换成找到购买了另一个产品的顾客信息呢，重新写一遍查询语句似乎有点重复。程序员永远不做重复的事，如果有一个虚拟表，名为prod_cust，然后使用select*fromprod_custwhereprod_id='TNT1'就可以轻松找到对应的行了，这就是视图。

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;4

使用起来挺简单的，可以根据需要编写出可重复使用的视图，方便查询。

视图的使用如下：

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;5

如果要修改视图，可以先删除再新建，也可以直接createorreplaceview，如果存在则会替换，如果不存在则会新建。

视图并不直接包含数据，而是一个SQL查询。视图和普通表的关系，就像临时组建的歌唱团和普通班级的关系。

但也因为视图不包含数据，每次都要重新执行，所以如果使用视图的场景比较复杂比如嵌套视图等，那么你会发现性能下降的厉害，所以在大量使用视图的场景可能需要关注性能问题。

视图创建后，可以像使用表一样使用视图，对视图进行select、过滤、排序、联结等等操作。

使用视图可以：

1. 复用SQL语句。
2. 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
3. 使用表的组成部分而不是整个表。
4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

顺便说一句，创建视图之后，showtables也会显示视图，所以你可以通过下面方式查询所有基表或者视图：

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;6

14.2使用视图重新格式化检索出的数据

比如某个场景，经常会使用到一些格式化的数据，那么就可以使用视图把数据格式化的形式先拼接好：

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;7

14.3使用视图过滤不想要的数据

比如某个场景，需要找到邮箱地址不为null的顾客下的订单：

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;8

但是另一个场景又需要找到邮箱地址不为null的顾客购买的所有商品列表，此时我们可以使用视图，把邮箱地址不为null的顾客创建为一个视图，在其他场景使用：

select*fromorderitemsorderbyitem_price;#按item_price升序排列#先按quantity升序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantity,item_price;#先按quantity降序排列，quantity的值一样时按item_price的值升序排列select*fromorderitemsorderbyquantitydesc,item_price;#找到最贵订单select*fromorderitemsorderbyitem_pricedesclimit1;9

可以看到视图这里就完全被当成了一个表来和其他表一起使用。

14.4使用视图与计算字段

视图对于简化计算字段的使用很有用，比如希望查找20008的订单的订单总额：

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];0

那么希望查找另一个订单总额时，可以使用视图来改造一下：

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];1

看到视图十分好用，实际使用中按需使用视图可以极大方便数据库操作。

14.5更新视图

视图也是可以使用insert、update、delete更新数据的，虽然视图只是一个SQL句子而不是实际数据。

更新视图的数据会更新其基表，但并非所有视图都可以更新的，如果数据库不冷确定被更新的基数据，则不允许更新。比如分组、联结、子查询、并、聚集函数、distinct等等。

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];2

不过一般，应该把视图用于检索数据select，而不是增删改数据insert、update、delete。

15.存储过程

前面介绍的大部分SQL语句都是对一个或者多个表的单个查询，但是实际情况下一个完整的操作可能是由多个语句组合而成的，比如考虑下面这个下单流程:

1. 为了处理订单，需要核对以保证库存中有相应的物品。
2. 如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。
3. 库存中没有的物品需要订购，需要与供应商进行一些交互。
4. 关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户。

可以说存储过程就是数据库SQL语言层面上的代码封装和重用，可以回传值，也可以接受参数。可以将其视为批文件，但作用不仅限于批处理。

存储过程简单、安全、高性能。不过有些数据库管理员会限制存储过程的创建权限，只允许用户使用，但不允许用户创建存储过程。

15.1创建存储过程createprocedure

创建存储过程使用createprocedure，可以设置参数，存储过程体使用begin...end分隔开，调用使用call

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];3

这里的存储过程使用了参数，也可以不使用参数，和其他语言中的函数类似。

15.2删除存储过程dropprocedure

删除使用drop关键字，如果不存在这个存储过程会报错，此时可以增加ifexists关键字：

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];4

15.3使用参数

变量(variable)内存中一个特定的位置，用来临时存储数据。

存储过程输入了4个参数，一个输入参数，还有三个用来存储的参数，每个参数用in（传递给存储过程）、out（从存储过程传出）、inout（对存储过程传入和传出）指定参数。

MySQL中的变量都必须以@开始，存储过程中检索得到的值使用into保存到相应变量，之后可以就可以查询到变量中存储的值了。

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];5

再试个例子，使用存储过程计算出指定订单号的总价，并输出到变量中：

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];6

15.4使用条件语句

存储过程也可以使用if(条件)then...elseif(条件)then...else语句，比如现在要计算折扣后的商品价格，总商品数量3件8折，4件7折，这里使用存储过程：

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];7

这个例子中我们使用了一个临时变量prod_count，计算出该订单总件数之后将其赋到这个临时变量中，然后在之后的ifelse条件语句中对其进行判断，再通过视图计算出总金额，最后保存给输出变量。

16.游标

有时，需要在检索出来的行中前进或后退一行或多行，这就是使用游标的原因。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

MySQL中的游标只能用于存储过程或函数。

游标处理分为下面几个步骤：

1. 声明游标declare：没有检索数据，只是定义要使用的select语句；
2. 打开游标open：打开游标以供使用，用上一步定义的select语句把数据实际检索出来；
3. 检索游标fetch：对于填有数据的游标，根据需要取出(检索)各行；
4. 关闭游标close：在结束游标使用时，必须关闭游标，如果你不关闭游标，MySQL将在到达end语句时关闭游标。

下面直接看例子：

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];8

首先使用declare定义了几个局部变量，这几个变量用来存中间值，其中默认值为false的done是循环的终止条件，将在后面的repeat语句中用来作为判断是否继续循环的标志位，当repeat没有更多行供循环时满足notfound，此时给done赋值true终止循环。在循环体中使用上一章的存储过程给表ordertotals填充计算的订单实际价格。

declare语句是有顺序的，局部变量需要在句柄之后定义，句柄必须在游标之后定义，否则会报错。

除了repeat循环外，MySQL还支持loop循环、while循环，基本大同小异，可以自己查询学习一下。

17.触发器

如果你想要某些语句在事件发生时自动执行，可以考虑触发器。

1. 只可以响应delete、insert、update语句；
2. 只有表支持触发器，临时表和视图不支持；

17.1创建触发器createtrigger

创建触发器使用createtrigger关键字，格式如下：

#基本语法select[查询列表]from[表名]where[筛选条件]orderby[排序条件];9

foreachrow表示对每个插入行执行触发器。只有表

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;0

17.2使用触发器

触发器要谨慎使用，由于触发器是针对每一行的，对增删改非常频繁的表上切记不要使用触发器，因为会非常消耗资源。

insert触发器

1. insert触发器内可以通过访问名为new的虚拟表访问被插入的行；
2. beforeinsert语句中可以通过更改new虚拟表中的值来修改插入行的数据；
3. 对于auto_increment自增列，在before中new中的值为0，在after中为自动生成的自增值。

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;1

before经常被用于数据验证。

delete触发器

1. delete触发器内可以通过访问名为old的虚拟表来访问被删除的行；
2. old虚拟表中的字段都是只读的，不能修改。

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;2

这里使用before而不是after的原因是，如果因为某种原因顾客信息不能存档，delete操作将会放弃，避免信息丢失。

update触发器

1. update触发器内可以通过访问名为old的虚拟表访问更新前的值，访问名为new的虚拟表来访问更新后的值；
2. beforeupdate触发器中，new中的值是可以被修改的；
3. old虚拟表中的字段都是只读的，不能修改。

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;3

18.管理事务处理

事务处理(transactionprocessing)可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

举个例子，如果我们要转账给别人，首先把别人账户增加钱，再把我们账户上钱扣除，如果中间出现问题，那么麻烦就大了。

或者在当前数据库中，如果我们要添加一个订单信息，分为下面几步：

1. 检查数据库中是否存在相应的客户（从customers表查询），如果不存在则添加这个用户信息。
2. 检索顾客的ID，cust_id。
3. 添加一行订单信息到orders表，把它与顾客ID关联。
4. 检索orders表中赋予的新订单ID，order_id。
5. 对于订购的每个物品在orderitems表中添加一行，通过检索出来的ID把它与orders表关联，以及通过产品ID与products表关联。

如果发生了某种数据库故障（超出磁盘限制、安全限制、表锁等），阻止了一个完整的流程，会出现什么情况。如果故障出现在1和2之间，这没什么关系，因为一个顾客没有订单信息是合法的，如果出现在3和4之间，那么就会出现一个空的订单，这个订单没有包含的产品信息，这很严重，如果出现在5时，添加orderitems过程中出现问题，那么可能出现订单信息不完整的情况，也很严重。

使用事务可以避免这个情况，如果中间发生了问题，那么则回退到某个安全的状态。

18.1事务处理

那么使用事务如何处理这个过程呢：

1. 检查数据库中是否存在相应的顾客，如果不存在则添加这个用户信息；
2. 提交顾客信息；
3. 检索顾客的ID；
4. 添加一行到orders表；
5. 如果在添加行到orders表时出现故障，回退；
6. 检索orders表中赋予的新订单ID；
7. 对于订购的每项物品，添加新行到orderitems表；
8. 如果在添加新行到orderitems时出现故障，回退所有添加的orderitems行和orders行；
9. 提交订单信息。

这里有几个概念：

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

18.2控制事务处理

使用回退rollback

starttransaction标识事务开始，使用rollback可以进行回退从start到rollback中间的所有语句。

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;4

rollback可以回退insert、update、delete语句，但不能回退create、drop语句，事务处理块中可以使用这两个语句，但rollback无效果。

使用提交commit

MySQL中用户的任何一个更新操作（写操作）都被视为一个事务，这就是所谓的隐含提交（implicitcommit），相当于MySQL帮你在后台提交了。

可以针对每个连接使用setautocommit=0来设置MySQL不自动提交更改，设置之后，每个SQL语句或者语句块所在的事务都需要显式commit才能提交事务。

但在事务处理块中，提交不会隐含地进行，需要你自己来显式的调用：

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;5

由于设计到订单和订单详情，所以使用事务来保证订单是完整的被删除，而不是部分删除。如果两个delete语句中发生了错误，那么commit将不会被执行。

在commit或rollback执行时，事务会被自动关闭。

使用保留点savepoint

之前的rollback、commit只能对整个事务处理块整体提交或回滚，某些复杂场景下可能要部分回滚或者部分恢复，比如之前例子，如果订单信息增加失败，可能要回滚到添加用户信息后。

此时可以使用保留点，这样在发生问题时回滚到保留点处即可。保留点使用比较简单：

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;6

保留点可以使用多一点，当在事务完成时，他们将会被自动释放，也可以使用releasesavpoint来手动释放。

19.安全管理

19.1访问控制

对数据库来说，用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。

1. 多数用户只需要对表进行读和写，少数用户需要能创建和删除表；
2. 某些用户需要读表，但可能不需要更新表；
3. 你可能想允许用户添加数据，但不允许他们删除数据；
4. 某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要；
5. 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；
6. 你可能想根据用户登录的地点限制对某些功能的访问。

给不同的用户提供不同的访问权，这就是访问控制。

对于root登陆的使用需要十分谨慎小心，仅在绝对需要时才使用，不应在日常MySQL操作中使用root账户。

19.2管理用户

用户信息存储在MySQL的mysql库中：

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;7

创建用户账号：

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;8

设置权限用grant关键字：

#找出产品价格为2.5的产品名字selectprod_name,prod_pricefromproductswhereprod_price=2.5;#找出产品价格小于等于10的产品名字，并按产品价格降序排列selectprod_name,prod_pricefromproductswhereprod_price<=10orderbyprod_pricedesc;#找到供应商id不为1003的产品，!=和<>含义一样，都是不等于selectvend_id,prod_namefromproductswherevend_id<>1003;9

权限设置和用户设置还有很多内容，不是本文的重点，可以百度一下或者看文档。

20.数据库维护

20.1备份数据

数据库也是经常需要备份的，可以使用以下方法：

1. 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
2. 用命令行实用程序mysqlhotcopy从一个数据库复制所有数据，但并非所有数据库引擎都支持这个实用程序。
3. 可以使用MySQL的backuptable或selectintooutfile转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用restoretable来复原。

20.2数据库维护

1. analyzetable用来检查键是否正确。
2. checktable用来针对许多问题对表进行检查。
3. 针对MyISAM表访问产生不正确和不一致的问题，可以使用repairtable来修复。
4. 如果从一个表中删除大量数据，应该使用optimizetable来收回所用空间，从而优化表的性能。

20.3查看日志

错误日志ErrorLog

记录Mysql运行过程中的Error、Warning、Note等信息，系统出错或者某条记录出问题可以查看错误日志。

通过showvariableslike"log_error";来查看错误日志存放的位置。

日常日志GeneralQueryLog

记录包括查询、修改、更新等的每条语句。

通过showglobalvariableslike"%genera%";查看日常日志存放的地方，如果general_log是off则不能查询，可以通过setglobalgeneral_log=on;打开查询，然后tail-f/var/lib/mysql/VM-0-17-centos.log;来查看。

二进制日志BinaryLog

包含一些事件，描述了数据库的改动，如建表、数据改动等，主要用于备份恢复、回滚操作等。

可以通过showvariableslike"%log_bin%";来查看Binlog存在哪，会有多个文件，使用showmasterlogs;可以看到查看所有Binlog日志列表，格式是bingo.000008这样，当Binlog日志写满或者数据库重启会产生新文件，使用flushlogs可以手动产生新文件，Binlog十分重要，产生问题要回滚用Binlog就可以了。

缓慢查询日志SlowQueryLog

记录执行缓慢的任何查询，在优化数据库时比较有用。

可以通过showvariableslike"%slow%";来查看缓慢日志存放的地方。

21.改善性能

性能是数据库永恒的追求，对于性能有以下Tips：

1. 数据库对硬件是有一定要求的，在老旧主机上运行自然远不如专用服务器上。
2. MySQL有很多配置，比如内存分配、缓存区大小等，熟练使用后通过调整配置可以获得更好的性能表现。查看配置可以使用showvariables;和showstatus;查看配置
3. 实现同样功能的不同语句有不同的性能表现，可以找到性能更好的方法。
4. 尽量不检索不需要的数据，比如能selectparam，就不要select*。
5. 一般组合查询比在select中使用or快。
6. 一般fulltext比like快。