《MySQL 必知必会》学习笔记

type

status

date

slug

summary

概念

数据库：是保存有组织的数据容器。

表：某种特定类型数据的结构化清单。

列：表中的一个字段，所有的表都是由一个或多个列组成的。

数据类型：所容许的数据的类型，每个表列都有相对应的数据类型，它限制或容许该列中存储的数据。

行：表中的数据是按行存储的，所保存的每个记录储存在自己的行内。

抽象：将表比作网格，网格中垂直的列为表列，水平行为表行。

主键：表中每一行都应该有可以唯一标识自己的一列。

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

MySQL：它是一个客户机-服务器，因此要使用它就需要一个客户机来给它提供命令的一个应用。

快速语法示例

如果是命令就用下面这种语法显示，在命令前后加上 ` 这个符号

序号	SQL 语法	说明	例子
1	`use 数据库名;`	不会返回东西，但必须先使用use打开数据库才能读取其中的数据	在这里举个例子
2	`show databases;`	可用数据库的一个列表	ㅤ
3	`show tables;`	当前选择的数据库内可用表的列表	ㅤ
4	`show columns from 表名;`	列出该表的全部内容	ㅤ
5	show status；	用于显示广泛的服务器状态信息	ㅤ
6	show create database 和 show create table；	分别用来显示创建特定数据库或表的MySQL语句	ㅤ
7	show grants；	用来显示授予用户的安全权限	ㅤ
8	show errors 和 show warnings；	用来显示服务器错误或警告消息	ㅤ
9	select 列名 from 表名；	使用select语句从某表中检索某列	ㅤ
ㅤ	select 列名，列名 from 表名；	使用select从某表中检索多个列（列与列之间用逗号分隔）	ㅤ
ㅤ	select * from 表名；	从某表中检索里面所有的列（*号是全部的意思）	ㅤ
ㅤ	select distinct 列名 form 表名；	distinct关键字，列出某列中不同的行（关键字必须放在列的前面）	ㅤ
ㅤ	select 列名 from 表名 limit 5；	limit关键字，列出某列的行数，limit 5表示列出该列从头开始到第5行，一共五行。	ㅤ
ㅤ	select 列名 from 表名 limit 5, 5；	limit关键字，列出某列的行数，limit 5表示列出该列从第5行开始往下的五行，一共五行。（第一行为行0而不是行1）	ㅤ
ㅤ	select 列名 from 表名 order by 列名；	order by关键字，指定order by 后面的列升序	列名可以多个，同时排序多个列
ㅤ	select 列名 from 表名 order by 列名 desc；	desc关键字，指定order by后面的列降序	desc关键字只对写在它前面的列起作用
ㅤ	select 列名 from 表名 drder by 列名 desc limit 1；	如果需要取升序或降序结果的行数，需要将limit写在它们的后面	ㅤ
ㅤ	select 列名 1，列名2 from 表名 where 列名2 = 2；	where关键字最后的结果过滤掉列表2不等于2的行	使用到where子句操作符
ㅤ	select 列名1，列名2 from 表名 where 列表 = ‘字符串’；	不止可以过滤数值还可以过滤字符串	ㅤ
ㅤ	select 列名1，列名2 from 表名 where 列名 between 5 and 10；	between，and关键字，between是结果取范围的意思，and则是与的意思，5 and 10表示取结果为5到10之间的	ㅤ
ㅤ	selest 列名 from 表名 where 列名 is null；	is null关键字，结果返回列中空值的行，	ㅤ
ㅤ	select 列名1，列名2 from 表名 where 列名1 = 2 or 列名1 = 3；	or关键字，结果只要满足or前后两个条件的其中一个就会被输出	ㅤ
ㅤ	select 列名1，列名2 from 表名 where 列名1 = 2 or 列名1 =3 and 列名2 >5；	or，and 关键字同时使用时，默认先去判断and前后的条件再去判断or前后的条件	and的计算次序优先级比or更高
ㅤ	select 列名1，列名2 from 表名 where （列名1 = 2 or 列名1 =3） and 列名2 >5；	（）关键字,先判断圆括号内的条件再判断圆括号外的条件	（）圆括号的优先级高于and和or
ㅤ	select 列名1，列名2 from 表名 where 列名 in （2, 3）order by 列名；	in操作符，in取（）圆括号内的合法值，值需用逗号分隔	in操作符和or的功能相同，只是写法不同，但in适合使用在长的合法选项清单时
ㅤ	select 列名1，列名2 from 表名 where 表 not in （2， 3）order by 列名；	not操作符，表示否定跟着它的条件	not in 和 in操作符的功能是相反的
ㅤ	select 列名1，列名2 from 表名 where列名 like ’jet%‘；	like操作符和%通配符，like表示搜索模式，’jet%‘表示只要开头是jet的词就符合条件，%通配符表示后面不管是什么。	ㅤ
ㅤ	select 列名1，列名2 from 表名 where 列名 like ’s%s‘	s%s表示词的前后是s的才符合条件	’%‘也不能匹配值为NULL的行
ㅤ	select 列名1，列名2 from 表名 where 列名 like ’_anvil‘；	_下划线通配符，和%的用途一样，但下划线只匹配单个字符而不是多个字符	_只能匹配一个字符不能多也不能少
ㅤ	select 列名1 from 表名 where 列名 regexp ’10‘ order by 列名1；	regxp关键字，like被regexp代替，表示的含义是regexp后面跟的东西作为正则表达式处理	like后面跟的是通配符条件，regxp后面跟的是正则表达式条件
ㅤ	select 列名1 from 表名 where 列名 regexp ‘.00’ order by 列名；	. 是正则表达式的匹配任意一个字符的意思	结果像100,200这些都是符合条件的
ㅤ	select 列名1 from 表名 where 列名 regexp ‘10 \| 20’ order by 列名；	\| 正则表达式中的竖杆和or操作符是一样的意思，表示匹配其中一项条件即可。	ㅤ
ㅤ	select 列名1 from 表名 where 列名 regexp ‘[123]ton’ order by 列名；	正则表达式[ ]中括号表示一组字符，意思是匹配1，2 , 3的其中一个就符合条件	正则表达式[123]是[1\|2\|3]的缩写
ㅤ	select 列名1 from 表名 where 列名 regexp ‘[^123]ton’ order by 列名；	[^123]表示匹配除了这三个字符串外的任何东西	正则表达式^和not in 功能是一样的
ㅤ	select 列名1 from 表名 where 列名 regexp ‘[1-5]ton’ order by 列名；	[1-5]正则表达式定义了一个1到5的范围。	ㅤ
ㅤ	selct 列名1 from 表名 where 列名 regexp ‘\\ .’ order by 列名；	\\这是转义符，.点本来是任意字符的意思，\\.就会转义成只是匹配单个点的条件	正则表达式有很多特殊字符，如果想要匹配这些字符，就需要先转义
ㅤ	ㅤ	ㅤ	ㅤ

语法

基本操作

在终端登录MySQL

下载完MySQL后就可以在终端登录了。

mysql -u root -p

回车后输入密码即可在本地登录MySQL，输出以下代码说明登录成功，现在可以开始输入SQL语句啦。

查看所有数据库

SHOW DATABASES;

查看当前可用的数据库，返回一个可用的数据库列表。

进入指定的数据库

USE 数据库名;

不会返回东西，但必须先使用use打开数据库才能读取其中的数据。

查看数据库中的所有表

SHOW TABLES;

查看该数据库内的所有，返回当前数据库内可用表的列表。

查看指定表中的所有列名

SHOW columns FROM 表名;

查看指定表中的内容，from后面要给出一个表名，输出的就是该表的全部列。

检索数据

检索单个列

SELECT 列名 FROM 表名;

关键字SELECT，FROM 表示从FROM后面这么表中检索SELECT这个列，结果输出这个列的全部内容。

检索多个列

SELECT 列名1，列名2，列名3 FROM 表名;

检索多个列和单个列基本上相同，多个列只需要在列与列之间用逗号分隔开来。

检索所有列

SELECT * FROM 表名;

*通配符，的意思是返回表中的所有列，但现在列的顺序还没有排序。

检索不同的行

SELECT 列名 FROM 表名；

检索的这个列有很多重复的值，要该怎么把它们列出只列出一个呢？

SELECT DISTINCT 列名 FROM 表名；

DISTINCT关键字，把结果进行去重处理再输出。

限制结果

SELECT 列名 FROM 表名 LIMIT 5;

LIMIT关键字，对检索出来的行数进行限制，比如LIMIT5表示只取输出结果的前面5行。

SELECT 列名 FROM 表名 LIMIT 5,5;

LIMIT关键字5,5的含义是从结果的第五行开始往后取五行然后输出。

完全限定表名

SELECT product.prod_name FROM products；

SELECT product.prod_name FROM crashcourse.products；

这两行代码的输出是一样的，但后面学习到的有些情行需要这种完全限定名。

排序检索数据

排序数据

可以看到我们按之前那样检索出来的列是没有顺序的，哪要怎么才能使他们有序呢？

ORDER BY关键字后面的列将从升序排序再输出。

多个列排序

SELECT列名,列名, 列名 FROM 表名 ORDER BY 列名,列名;

ORDER BY 后面要升序排序的列需要用逗号分隔开。

SELECT 列名, 列名, 列名 FROM 表名 ORDER BY 列名 DESC, 列名；

DESC关键字，要跟在ORDER BY后面配合使用，在DESC前的列名将是倒序输出，想在多个列上进行降序排序，需要对每个列指定DESC关键字。

SELECT 列名 FROM 表名 ORDER BY 列名 DESC LIMIT 1;

在给出ORDER BY句子时，要保证它在FROM后面，要使用LIMIT时，要保证在ORDER BY后。

过滤数据

SELECT 列名,列名 FROM 表名 WHERE 列名 = 2.50；

WHERE子句是做判断的意思，跟在WHERE后面的是判断条件，只有满足条件的行才会被输出结果。

WHERE子句操作符操作符说明

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

检查单个值

SELECT 列名, 列名 FROM 表名 WHERE 列名 = '字符串'

WHERE子句不止可以判断数值还可以判断字符串。

SELECT 列名, 列名 FROM 表名 WHERE 列名操作符条件;

举个运用操作符的例子

可以看到=10的有两行符合

不匹配检查

运用不等于操作符来筛选出不匹配的行

上下两个运算符得出的结果都是一样的，都是不等于的意思。

范围值检查

SELECT 列名, 列名 FROM 表名 WHERE 列名 BETWEEN 5 AND 10;

BETWEEN， AND关键字，BETWEEN后面跟着的是一个范围值，比如5 AND 10表示从取结果为5到10的行，这里中间用AND分隔开来。

空值检查

SELECT 列名 FROM 表名 WHERE 列名 IS NULL;

表中有些列时不包含值的，也就是空的，空的我们用NULL表示。

IS NULL 关键字跟在WHERE子句后面表示结果输出该列值为空的行.

数据过滤

组合where子句

SELECT 列名, 列名, 列名 FROM 表名 WHERE 列名 = 1003 AND 列名 <= 10;

使用AND操作符给WHERE子句附加条件，结果要满足AND前后两个条件才会被输出。

OR操作符

SELECT 列名, 列名 FROM 表名 WHERE 列名 = 1002 OR 列名 = 1003;

OR关键字跟在WHERE后面，表示结果只要满足OR前后的任意一个条件的行都会被输出。

计算次序

SELECT 列名, 列名 FROM 表名 WHERE 列名 = 1002 OR 列名 = 1003 AND prod_price >= 10;

对比以上两行代码可以看到，AND在计算次序中比OR优先级更高，因此可以通过（）圆括号来提高优先级，这样就可以得出（）> AND>OR的优先级比较。

IN操作符

SELECT 列名, 列名 FROM 表名 WHERE 列名 IN (1002,1003) ORDER BY 列名;

IN操作符跟在WHERE后面，功能和OR相同，只是写法不同，优点在于使用长的选项时，IN操作符更清晰直观。

可以看到使用两个写法输出的结果是一样。

NOT操作符

SELECT 列名, 列名 FROM 表名 WHERE 列名 NOT IN （1002，1003）ORDER BY 列名;

NOT IN关键字与IN关键字的含义恰恰相反，表示结果过滤掉NOT IN后面条件语句的行再输出。

用通配符进行过滤

LIKE操作符

SELECT 列名, 列名 FROM 表名 WHERE 表名 LIKE ‘字符串%’

LIKE操作符表示搜索模式的意思， %百分号通配符表示任意字符，下面的语句表示在prod_name列中搜索开头为jet的字符串。

搜索模式 ’%anvil%’表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。

（_）下划线通配符

_ 下划线通配符的用途和 % 一样，但下划线只匹配单个字符而不是多个字符。举例下面的例子下划线只能匹配一个字符。

_ 和 % 对比可以看出如果匹配一个字符的话输出是一样的。

MySQL正则表达式

基本字符匹配

SELECT 列名 FROM 表名 WHERE 列名 REGEXP ’正则表达式‘ ORDER BY 列名;

REGEXP关键字跟在WHERE子句后面替代了LIKE，意思是代表REGEXP后面跟的条件是正则表达式，一样是搜索模式，只是搜索条件的表达形式不同了。

下面例子的含义是与正文1000匹配的一个正则表达式。

. 在正则表达式里面表示匹配任意一个字符。

正则OR匹配

正则表达式1000|2000中的| 和OR操作符的功能是一样的。

匹配几个字符之一

正则表达式[123]的意思是匹配1或2或3的其中有一个都成功，[] 是另一种形式的OR语句，[123]其实它是[1|2|3]的缩写。

为什么不写成1|2|3 Ton呢？1|2|3 Ton 因为写成这样MySQL会认为包含1或2的行都要输出，需要用[] 来定义OR语句查找什么。

这里还要提到的是[^123] 中的^表示除了包含123的行其他都要输出。

匹配范围

这里正则表达式[1-5]Ton 中的[1-5] 定义了一个范围，表达式的意思是匹配1到5的。

匹配特殊字符

. 在正则表达式里面表示匹配任意字符的意思，那想匹配带有. 符号的行要怎么办呢?请看下一段代码。

可以看到在.的前面多了\\，其实这个两个斜杆是转义符，它的作用是使跟在它后面的特殊字符失去意义，只是单单一个符号没有其他特殊用途。

空白元字符

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

字符类

类	说明
`[:alnum:]`	任意字母和数字（同[a-zA-Z0-9]）
`[:alpha:]`	任意字符（同[a-zA-Z]）
`[:blank:]`	空格和制表（同[\\t]）
`[:cntrl:]`	ASCII控制字符（ASCII 0到31和127）
`[:digit:]`	任意数字（同[0-9]）
`[:graph:]`	与[:print:]相同，但不包括空格
`[:lower:]`	任意小写字母（同[a-z]）
`[:print:]`	任意可打印字符
`[:punct:]`	既不在[:alnum:]又不在[:cntrl:]中的任意字符
`[:space:]`	包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）
`[:upper:]`	任意大写字母（同[A-Z]）
`[:xdigit:]`	任意十六进制数字（同[a-fA-F0-9]）

重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1,}）
？	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

下面来举几个正则表达式的例子

解释：\\用来转义跟在后面的（）不是特殊字符，[0-9]表示匹配任意数字， sticks？表示匹配sticks但？表示stick最后这个s有没有都要匹配。

解释：[:digit:]表示匹配任意数字，{4}表示4个数字，加在一起的意思就是匹配4个任意的数字。

其实不止一种写法的，下面这种写法一样可以到得到相同的结果。

定位符

定位元字符

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

解释：^匹配串的开始，所以^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们。

创建计算字段

拼接字段

Concat()函数，拼接串的作用，意思是把多个串连接起来形成一个较长的串，串与串之间需要用,分隔。

通过RTrim()函数删除数据右侧多余的空格来整理数据

使用别名

AS关键字，起别名的作用，像上面拼接了两个列，这时想拼接出来的新列想起新名字时就要用到as关键字，它跟在SELECT语句后面。

执行算术计算

对检索出的数据进行算术计算，SELECT和FROM之间是想输出的列，而WHERE是检索订单号20005中的所有物品。

SELECT 列名, 列名, 列名算法操作符列名 AS 新列名 FROM 表名 WHERE 条件

这个案例用quantity*item_price的结果创建出一个名为expanded_price的新行，最后检索的订单号20005中的所有物品。

MySQL算术操作符

操作符	说明
`+`	加
`-`	减
`*`	乘
`/`	除

使用函数处理数据

SELECT 列名，函数（即将处理的列名） AS 新列名 FROM 表名 ORDER BY 列表

Upper()函数的作用是将文本转为大写，例子可以看到将vend_name的列放进了Upper函数的括号内处理了，处理过后AS创建了一个新列，最后用升序排序。

常用的文本处理函数

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

日期和时间处理函数

常用日期和时间处理函数

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

案例检索出日期为’2005-09-01’的行。

但这里有个缺陷就是一般order_date的数据类型为datetime。这种类型存储日期及时间值，然后只比较order_date = '2005-09-01'日期不比较时间的话它是不会匹配成功的，这个时候就需要Date()函数来解决。使用Date()函数结果虽然一样，但使用Date()函数是一个好习惯。还有比如只想过滤时间段的行数可以使用Time()函数。

使用WETWEEN操作符来把'2005-09-01' AND '2005-09-30' 定义为一个要匹配的日期范围。

但像有些年份的二月是不足28天还有些月份不记得有多少天的时候，对比以上这种方法其实还有另一种更好的方法。

常用数值处理函数

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

汇总数据

聚集函数

SQL聚集函数

函数	说明
`AVG()`	返回某列的平均值
`COUNT()`	返回某列的行数
`MAX()`	返回某列的最大值
`MIN()`	返回某列的最小值
`SUM()`	返回某列值之和

AVG()函数

SELECT AVG(准备处理的列名) AS 新列名 FROM 表名;

AVG()函数是算平均值的函数，例子可以看到将prod_price列放进了AVG()行数内处理它会算出这个列的平均值，AS创建了新的列用于接住等下算出来的平均值。

同样AVG()函数也接受指定列或行的平均值。例子通过WHERE过滤出vend_id为1003的产品，所以AVG()函数只计算该产品的平均值。

COUNT()函数

COUNT()函数是计算行数的，可以用在确定表中行的数目或符合特定条件的行数数目。

SELECT COUNT(*) AS 新列名 FROM 表名;

把*放进函数里面代表什么，前面提到过的*是全部的意思，COUNT(*)的意思就是表中全部行进行计数。

当然也是可以对某个列进行行计数的，想要计算什么就把什么放到函数的圆括号内。

MAX()函数

MAX()函数的作用是返回指定列中的最大值。

SELECT MAX(列名) AS 新列名 FROM 表名;

MIN()函数

MIN()函数的作用是返回指定列中的最小值。

SELECT MAX(列名) AS 新列名 FROM 表名;

SUM()函数

SUM()函数用来返回指定列值的和（总计）。带有WHERE子句则表明只算order_num = 20005的订单总和，没有WHERE子句会计算quantity全部的总和。

SELECT SUM(列名) AS 新列名 FROM 表名 WHERE 列名 = 20005;

SUM()函数不在能写单个列，还可以像例子这样先做算术操作再把算出来的结果加到一起。

聚集不同值

SELECT AVG(DISTINCT 列名) AS 新列名 FROM 表名 WHERE 列名 = 1003;

可在ACG()函数内先执行DISTINCT 去重处理再去计算平均值。

组合聚集函数

SELECT 函数 AS 新列, 函数 AS 新列, 函数 AS 新列 FROM 表名;

SELECT 语句根据需要包含多个聚集函数，函数与函数之间用逗号分隔开来。

分组数据

数据分组

目前为止我们所计算的数据或匹配最后都是通过WHERE筛选输出的结果，但有没有想过将数据分组之后进行输出或是先分组然后筛选再输出呢。像计算年级各个班学生数量，是不是最后输出要按班级分组才能知道每个班有多少学生。

创建分组

SELECT 列名, 函数 AS 新列名 FROM 表名 GROUP BY 分组的列名;

如果没有GROUP BY的话COUNT(*)函数最后只会输出一个总数（全部编号产品行数的总和），而不会像现在这样按vend_id分组后再输出结果（按每个供应商ID来计算各个的行数）

在具体使用GROUP BY子句前，需要知道一些重要的规定。

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。

GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

过滤分组

SELECT 列名, 函数 AS 新列名 FROM 表名 GROUP BY 分组的列名 HAVING 函数 >=2;

通过下面的案例就可以看到 HAVING关键字是位于 GROUP BY 后面的，COUNT(*) >= 2 这个过滤条件是在分组后进行的，换句话说就是 HAVING 是在分完组之后进行条件过滤再输出结果的。由此可见 WHERE和 HAVING的区别就是前者是分组前进行过滤，后者是分组后进行过滤。

SELECT 列名, 函数 AS 新列名 FROM 表名 WHERE 过滤条件 GROUP BY 分组列名 HAVING 过滤条件

解释：开始使用了聚合函数的基本SELECT，WHERE过滤掉了分组前的prod_price >= 10，然后按vend_id进行分组数据，HAVING子句过滤掉计数为COUNT(*) >= 2的分组。记得区分WHERE和HAVING的用途。

分组和排序

ORDER BY 和 GROUP BY 虽然经常完成相同的工作，但它们是很不同的。

ORDER BY 与 GROUP BY 的区别

ORDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

对比下面两段SQL语句会发现，GROUP BY不会排序，需在最后再次使用ORDER BY 进行排序。

SELECT子句顺序

SELECT子句及其顺序

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

使用子查询

子查询

查询一般指SELECT语句，但SQL还允许创建子查询，也就是在查询中嵌套查询。

利用子查询进行过滤

既然子查询是嵌套查询，看如果把下面的两句普通SELECT查询语句嵌在一起的。

SELECT 列名 FROM 表名 WHERE 列名2 IN (SELECT 列名2 FROM 表名2 WHERE 列名3 = 条件)

通过子查询的方法嵌套以上两查询语句所以它们的输出是相同的，MySQL会先执行括号内的语句，得到结果之后再执行外面的语句。

子查询还可以进行多层嵌套。下面例子执行三条SELECT语句，先执行最里层的然后返回给外层，一级一级的执行，一般子查询与IN操作符结合使用，但也可以用于测试等于=、不等于<>等。由于性能影响不能嵌套太多的子查询。

计算字段使用子查询

下面的例子中orders是一个计算字段，它是由圆括号中的子查询建立的。这里还使用到了完全限定列名，因为orders和customers表中都有一个名为cust_id的列，如果不使用完全限定列名的话MySQL会假定你是orders表中的cust_id进行自身比较。

联结表

联结

联结是利用SQL的SELECT能执行的最重要的操作，理解联结及语法是学习SQL的一个极为重要的组成部分。

关系表

在数据库中，单表的操作是最简单的，但是在实际业务中最少也有十几张表，并且表与表之间常常相互间联系，这就称之为关系表，下面举个例子大家就应该明白啦。

学生表student（子表）

1	id	name	class_id
2	1001	张三	111
3	1002	张四	222
4	1003	王五	111
5	1004	赵六	111

班级表class（父表）

1	id	name
2	111	class1
3	222	class2

一个班级对应多个学生，一个学生只能对应一个班级，所以这两个表的关系也就很明确了，班级表中的一条记录可以对应学生表的多条数据，学生表中的一条记录只能对应班级表的一条数据。

外键：为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。像学生表的class_id列和班级表的id列的关系。

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

为什么要使用联结

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

创建联结

SELECT 列名, 列名2, 列名3 FROM 表名, 表名2 WHERE 表名.列名 = 表名2.列名 ORDER BY 列名, 列名2;

SELECT后面检索的三个列其中vend_name列是vendors表的，prod_name、prod_price列是products表的，然后可以看到FROM和之前不同的是它列出了两个表，分别是vendors和products，这两个表通过WHERE子句正确联结（WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id）。

需要注意的是这种完全限定列名，因为不使用这种写法的话，MySQL不知道vend_id是指向那个表的，因为两个表都有vend_id这个列。

笛卡尔积：如果没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的行数将是第一个表中的行数乘以第二个表中的行数。

下面举个没有联结条件的表例子。

内部联结

前面的所有联结都称为等值联结，它基于两个表之间的相等测试，也称之为内部联结（最常用的联结形式），它还有另外语法来表达，请看以下语句。

SELECT 列名, 列名, 列名 FROM 表名 INNER JOIN 表名2 ON 表名.列名 = 表名2.列名;

SELECT和之前的语句相同，但FROM不同，两个表之间的关系是FROM 子句组成部分，以INNER JOIN指定，使用这种语法时联结条件用特定ON子句而不是WHERE子句给出。

需要注意的是这种语法虽然没有WHERE子句简单，但这种是明确的联结语法，这样就不会忘记联结条件。

联结多个表

SELECT 列名, 列名, 列名, 列名 FROM 表名, 表名2, 表名3 WHERE 表名.列名 = 表名2.列名 AND 表名1.列名 = 表名3.列名 AND 表名3 = 2005;

FROM列出了三个表，WHERE定义了两个联结条件，而第三个联结条件用来过滤出订单20005中的物品。

注意：联结的越多，性能下降的越厉害。

子查询并不总是执行复杂SELECT操作最有效的方法，请看以下案例。

通过这两段语句，可以知道为执行SQL操作，一般不止一种方法，但这些方法会对性能有区别，尽量找出最合适的方法。

创建高级联结

使用表别名

别名除了用在列名和计算字段外，SQL还允许给表名起别名，这样做有两个好处缩短表名，允许在单条SELECT语句中多次使用相同的表。

SELECT 列名, 列名 FROM 表名 AS 表别名, 表名2 AS 表别名 WHERE…;

可以看到给FROM后面的三个表都起了别名，orders AS o建立o作为orders的别名，后面如果再次需要使用到该表的时候只需写上它的别名o即可，这样可以省去很多的输入减低错误率。

注意：表别名和列别名不一样，表列名只在查询执行中使用，表别名不返回到客户机。

使用不同类型的联结

前面学到的只是内部联结（等值联结），现在来看其他三种联结：自联结、自然联结、外部联结。

先说自联结

SELECT 表别名.列名, 表别名.列名2 FROM 表名 AS 表别名, 表名 AS 表别名2 WHERE 表名.列名 = 表名2.列名 AND 列名 = ‘DTNTR’;

查询中FROM后面的products表出现了两次，这两个表实际上是同一个表，但这对于MySQL来说是有二义性的，因为它不知道你引用的是products表中的那个实例。为了解决这个问题，使用了表别名因此就有了p1和p2，现在可以将这些别名用作表名，因为p1和p2拥有的列是相同的，所以WHERE子句后面要用完全限定名，不然MySQL不知道想要哪个列（即使它们事实上是同一个列），然后WHERE（通过匹配p1中的vend_id和p2中的vend_id）联结了两个表，然后按第二个表中的prod_id过滤掉数据，返回所需的数据。

注意：自联结通常作为外部语句用来代替从相同表中检索数据时使用的子查询语句，虽然结果相同但性能比子查询快得多。

自然联结

在下面例子中，通配符只对第一个表使用。其他列都明确列出，所以没有重复的列被检索出来。到现在为止我们建立的每个内部联结都是自然联结。

外部联结

外部联结将一个表中的行与另一个表中的行相关联，但有时候会需要包含没有关联行的那些行。

外部联结的语法和内部联结很相似，我们先来回忆一下内部联结的语法，请看下面例子。

SELECT 表名.列名, 表名2.列名 FROM 表名 LEFT OUTER JOIN 表名 ON 表名.列名 = 表名2.列名;

SELECT语句使用关键字OUTER JOIN来指定联结的类型（而不是在WHERE子句指定）。但是跟内部联结相比外部联结还要包括没有关联的行，在使用OUTER JOIN语法时，必须配合使用关键字RIGHT（右）或LEFT（左）来指定包括的其所有行的表是在OUTER JOIN的左边还是右边。

这里不明白的同学可以参考：一文讲懂SQL外连接OUTER JOIN - 知乎 (zhihu.com)

使用带聚集函数的联结

SELECT 表名.列名, 表名2.列名, COUNT(表名.列名) AS 新列名 FROM 表名 INNER JOIN orders ON 表名.列名 = 表名2.列名 GROUP BY 列名.表名;

这个案例就是使用了函数的联结，SELECT语句使用INNER JOIN将customers和orders表互相关联，GROUP BY 按customers.cust_id来分组，调用函数COUNT对(orders.order_num)进行计算然后AS别名为num_ord列返回。

这个例子使用左外部联结来包含所有行，甚至包含那些num_ord为0的。结果显示也包含了Mouse House行为0。

使用联结和联结条件

总结下前面两章联结的知识要点：

一般使用的是内部联结，但使用外部联结也是有效的。

要使用正确的联结条件，否则返回不正确的数据。

使用联结就要联结条件，否则会得出笛卡尔积。

一个联结可以包含多个表，每个联结可以用不同的联结类型，这么做也有用，但应该在使用之前分别测试每个联结，便于后期排除故障。

组合查询

前面学习到的结果基本上都是返回单条SELECT语句，但MySQL是允许执行多条SELECT语句并将结果作为单个查询结果集返回。这种查询称为并或是复合查询。

一般有两种情况需要用这种组合查询：

在单个查询中从不同的表返回相同的列。

对单个表执行多个查询，按单个查询返回结果。

创建组合查询

先看下面两段简单的SELECT语句。

现在要把两个SELECT语句合在一起，然后返回单个查询结果，来看看我们是怎么做的吧。

SELECT 列名, 列名, 列名 FROM 表名 WHERE 列名 <= 5 NUION SELECT 列名, 列名, 列名  FROM 表名 WHERE 列名 IN (1001,1002);

UNION关键字，两条SELECT中间用UNION关键字分隔，UNION指示MySQL执行两条SELECT语句并把结果合成单个查询结果集。

UNION规则

通过什么的例子发现其实UNION用起来非常简单，使用它时有几条规则需要注意：

UNION必须由两条或以上的SELECT语句组成，语句和语句之间要用UNION关键字分隔。

UNION中的每个查询必须包含相同的列、表达式或集合函数，但可以不用按顺序来排。

列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换的数据类型。（例如：不同的数值类型或不同的日期类型）

包含或取消重复的行

UNION会默认从查询结果集中对重复的行做去重处理。我们先来看下面的语句。

想改掉UNION这默认行为也很简单，可以在UNIION后面加上ALL关键字变UNION ALL这样MySQL就不会去重了。看下面的语句再对比上面的语句会发现有一行是重复的哦。

对组合查询结果排序

SELECT FROM WHERE NUION SELECT FROM WHERE ORDER BY;

SELECT语句输出使用ORDER BY进行排序，但UNION组合查询时只能在最后一条SELECT语句使用一次ORDER BY子句，是不允许对部分进行排列的。虽然ORDER BY子句是最后SELECT语句的组成部分，但实际上MySQL是对所有SELECT语句返回的结果进行排序的。

全文本搜索

理解全文本搜索

使用全文本搜索时，MySQL不需要分别查看每个行每个词来分析处理。MySQL会创建指定列中各词的一个索引，搜索可以针对这些词进行。这样MySQL可以快速有效的决定词是否匹配。

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且随着数据的改变不断的从新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索。

启用全文本搜索支持一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。下面我们来演示一遍CREATE TABLE语句和FULLTEXT子句的使用。

这条CREATE TABLE语句定义表productnotes并列出它所包含的列。为了对note_text列进行全文本搜索，MyAQL根据子句FULLTEXT(note_text)指示对它进行索引。这里FULLTEXT索引单个列，如果需要的话也可以指定多个列。

下面进行全文本搜索，在索引之后使用函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。下面举个例子。

SELECT 列名 FROM 表名 WHERE Match(要搜索的列) Against('要搜索的文本');

SELECT和之前的一样检索单个列note_text，由WHERE子句，全文本搜索别执行，Match(note_text)表示要搜索这个列，Against(’rabbit’)表示在列中搜索rabbit这个文本然后返回。

看一下语句使用前面学到的LIKE一样可以得到一样的行，但使用LIKE不会以特别有用的顺序返回数据。使用全文本搜索返回以文本匹配的良好程度排序的数据（rabbit作为第3个词会比作为第20个词的等级更高，所以较高等级的会先返回）。

使用查询拓展。查询拓展用来设法放宽所返回的全文本搜索结果的范围，能找出可能相关的结果，尽管它们并不精确包含所查找的词。

下面先看个没有拓展查询的简单文本搜索。

因为只有一行包含词anvils所以只返回一行，我们再来看下面的拓展查询是怎么样的。

从上面的语句查询结果可以看出拓展查询返回了7行，其中只有第一行含有anvils词，但第二第三行包含了第一行的customer和recommend，在拓展查询看来它们之间是有关联的，所以都返回了出来。

布尔文本搜索，全文搜索的另外一种形式，称为布尔方式（boolean mode）它很重要的一点就是技术没有FULLTEXT索引也可以使用。下面列出它的功能。

要匹配的词

要排斥的词（包含该词的行不返回）

排列提示（可以指定那些词更重要（等级更高））

表达式分组

下面演示IN BOOLEAN MODE的作用。

SELECT 列名 FROM 表名 WHERE Match(列名) Against('需要查找的词' IN BOOLEAN MODE);

全文搜索包含heavy的所有行。使用了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，其结果与没有指定布尔方式的结果相同。

再举个例子为了匹配包含heavy但不包含任意以rope开始的词的行，我们可以这么做。

这次只返回一行。这次仍然匹配词heavy，但-rope*明确地指示MySQL排除包含rope*（任何以rope开始的词，包含ropes）的行。

上面已经出现了搜索布尔操作符-和*，-是排除一个词，*是截断操作符，下面我们来看看其他的操作符的作用是什么。

全文本布尔操作符

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，且减少等级值
()	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

下面举例子分别说下这些操作符的含义和使用方法。

这个搜索匹配包含词rabbit和bait的行。

没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。

这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。

匹配rabbit和carrot，增加前者的等级，降低后者的等级。

这个搜索匹配词safe和combination，减低后者的等级。

插入数据

数据插入

INSTERT是用来插入（添加）行到数据库表的。插入可以用以下几种方式使用：

插入完整的行

插入行的一部分

插入多行

插入某些查询的结果

插入完整的行

最简单的插入语法INSERT，它要求指定表名和插入的值。

INSERT INTO 表名 VALUES(插入的数据);

INSERT INTO关键字后面跟着的是即将要插入数据的表格，VALUES()括号内写入要插入的数据，注意是每个列都要提供一个值（注意：数据分别插入哪个列的依据是依赖于表中列的定义次序）。

INSERT语句一般不会有输出。

上面这种插入语法虽然简单但很不安全，要避免使用，下面我们来看个不用依赖列次序的插入语法（更安全但更繁琐）。

INSERT INTO 表名(列名, 列名2, 列名3, 列名4, 列名5) VALUES ('数据', '数据2', '数据3', '数据4', '数据5', NULL );

VALUES后面插入数据的顺序是依据VALUES前面提供的列名进行插入。细心的同学可能已经发现了输入的数据比列名多一项，没错，如果插入的值是NULL（空）的话，前面的列名可以省略不写。

下面的INSERT语句给出了列名，以一种不同的次序填充，所以插入一样正确。

INSERT语句还可以多条使用，一次提交，但每条语句要用分号结束。

只要每条INSERT语句中的列名（和次序）相同，可以组合各语句，请看下面例子。

INSERT语句，SELECT把custnew的所有数据导入customers，SELECT语句从custnew检索出要插入的值（而不是列出），SELECT中列出的每个列对应于customers表名后所跟的列表中的每个列。这条语句将插入多少行有赖于custnew表中有多少行。

INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。

更新和删除数据

更新数据

更新数据有两种：：一种是更新指定行，一种是更新所有行。我们来看下一它们的语法吧。

更新指定行：UPDATE 表名 SET 列名 = '新值' WHERE 列名2 = 过滤条件;

更新所有行：UPDATE 表名 SET 列名 = ‘新值’;

UPDATE关键字语句没有WHERE子句结束，就会更新表中所有行。

下面就来举个更新行的例子。

UPDATE 表名 SET 列名 = '新值', 列名2 = '新值' WHERE 列名3 = 过滤条件;

上面是更新单个列的例子，如果想更新多行的话，需要在各个更新行后面加上逗号分开。下面举个更新多行的例子。

IGNORE关键字，如果用UPDATE语句更新多行时，如果其中有一行出现错误整个UPDATE操作被取消，为了避免这种情况可以在UPDATE后面加上关键字IGNORE（UPDATE IGNORE），就算有错误也不会取消操作而是继续往下更新其他行。

为了删除某个列中的值，可以用NULL(空值)填充它，注意要在该列允许设置NULL值的时候使用。下面我们来看下例子怎么使用它。

UPDATE 表名 SET 列名 = NULL WHERE 列名2 = 过滤条件;

删除数据

删除（去掉）数据有两种：一种删除指定行，一种删除所有行。我们来看下它们的语法吧。

删除指定行：DELETE FROM 表名 WHERE 列名 = 过滤条件;

删除所有行：DELECT FROM 表名;

想删除一个表的话还有语法可以更快的做到，那就是关键字TRUNCATE TABLE语句，它删除的同时还会新建一个表，而不是只删除表，这个功能要记得哦。

更新和删除的注意事项

使用UPDATE和DELETE语句时应该带上WHERE过滤条件不然它是作用于整个表的，UPDATE不带WHERE每个都将被新值更新，DELETE不带WHERE就会删除表中的全部数据。

除非确实打算更新或删除每一行，否则不要使用不带HWERE的UPDATE和DELETE语句。

保证每个表都有主键，像WHERE语句一样去使用它。

UPDATE和DELETE语句使用WHERE子句之前，应该单独测试一下WHERE是否过滤正确再去使用。

创建和操纵表

创建表

CREATE TABLE是创建新表的关键字，在CREATE TABLE之后给出新表名，表列的名字和定义在圆括号内用逗号分隔，在PRIMARY KEY关键字后面的圆括号内写入主键名。

CREATE TABLE 新表名(列名类型, 列名类型, PRIMARY KEY(主键名));

使用NULL（空值），允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受没有值的行，也就是要输入值，不然会返回错误。

在创建表列的时候要给出是否可以插入空值，可以就是NULL，不可以就是NOT NULL，如果没指定NOT NULL的话，则默认指定的是NULL。

主键再介绍：为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名。

CREATE TABLE 新表名(列名类型, 列名类型, PRIMARY KEY(主键名, 主键名));

指定默认值：如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。请看下面的例子。

引擎类型：到现在为止使用CREATE TABLE语句全都以ENGINE=InnoDB语句结束。MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。InnoDB是一个可靠的事务处理引擎。

更新表：ALTER TABLE语句，表存储数据以后一般不建议更新，真需要更新的话可以在ALTER TABLE关键字之后给出要更改的表名，和即将要做更改的列。

ALTER TABLE 表名 ADD 添加的列名类型;

比如想删除刚刚添加的列，可以像下面这样做。

删除表：DROP TABLE语句，在DROP TABLE后面写上表名即可。删除整个表而不是内容，是不能撤回的，执行这条语句之后将永久删除，要谨慎考虑。

重命名表：使用RENAME TABLE语句可以重命名一个表。

RENAME TABLE 新表名 TO 旧表名;

可以使用一下语句对多个表重命名。

RENAME TABLE 新表名 TO 旧表名, 新表名 TO 旧表名;

使用试图

试图

我们要先了解试图是什么，为什么要使用它，它有什么好处又有什么使用规则呢，请参考下面的文章可以快速的了解试图。

SQL-什么是视图及如何使用视图_视图怎么用_太阳晒屁股了的博客-CSDN博客

使用试图

来看下如何创建试图吧。

CREATE VIEW语句用来创建试图。

SHOW CREATE VIEW viewname查看创建试图的语句。

DROP VIEM viewname 删除试图。

更新试图有两种方法，先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW，如果更新的试图不存在使用第二种方法会新建一个试图，试图存在的话就会被替换掉。

利用试图简化复杂的联结

CREATE VIEW 试图名 AS SELECT 列名, 列名 FROM 表名, 表名 WHERE 表名.列名 = 表名2.列名;

前面的CREATE VIEW创建了一个名为productcustomers的试图，后面联结表。

有了这个试图时候，比如我们想筛选出”TNT2“这个客户，可以像下面这样写。

SELECT 列名, 列名 FROM 试图名 WHERE 筛选条件;

这条语句通过WHERE子句从试图中检索特定数据。

用试图重新格式化检索出的数据

试图常见的用途就是重新格式化检索出的数据，下面我们来看个例子。

比如我经常需要用到这个格式，不需要每次都执行联结，而是创建一个试图需要的时候使用它即可。

为了检索出以创建所有邮件标签的数据，可以进行下面的操作。

可用试图过滤不想要的数据

试图对于应用普通的WHERE子句也很有用，这里的WHERE子句过滤掉cust_email列中具有NULL值的那些行，这样它就不被检索出来。

可以像使用其他表一样使用视图customeremaillist。

使用试图与计算字段

先举个普通的计算价格总和的例子。

现在将它转为试图看一下是怎么的吧。

创建了试图之后就现在就可以使用它啦，举个例子，检索订单20005的详细内容。

更新试图

试图定义中有以下操作，则不能进行试图的更新。

分组（使用GROUP BY和HAVING）

联结

子查询

聚集函数

DISTINCT

导出（计算）列

看上去好像有很多限制，其实不是的，因为试图主要用于数据检索。

使用存储过程

存储过程

为什么要使用存储过程

存储过程这一篇就够了 - 知乎 (zhihu.com)

执行储存过程

MySQL称存储过程为调用，它的执行语句是CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。下面来举个例子。

CALL 执行名(参数, 参数);

执行名为productpricing，括号后面传入的是参数。

调用还是比较简单的，重点是我们要如何创建存储过程，下面我们一起来学习吧。

创建存储的过程

DELIMITER\\ CREATE PROCEDURE 存储过程名（参数列表）BEGIN 一句合法的SQL语句; END\\ DELIMITER;

其中参数列表包含三部分：参数模式+参数名+参数类型.

参数的模式有三种：

IN：该参数可作为输入。即该参数需要调用方传入值。（入口）

OUT：该参数可作为输出，该参数可以作为返回值。（出口）

INOUT：该参数可作为输出，也可以作为输出；即该参数既需要传入值，又可以返回值。（出入口）

BEGIN和END关键字的意思就好比我们c语言的大括号{}，作用就是包含SQL语句的，SQL语句开始前用BEGIN，语句结束后用END，有另外一种情况就是如果SQL语句只有一句话的时候，BEGIN和END可以省略不写。

可能细心的同学已经发现了，语句的前面多了DELIMITER\\关键字，这个关键字的作用是将该语句的结束符号从;临时改成了\\（可自定义符号），因为创建过程的语句里面有一句合法的SQL语句（自带分号）那么创建过程语句又有一个分号，这时MySQL读到第一个分号的时候就会认为该语句结束了，如果这个时候结束的话，这个创建语句是不完整的所以会创建不成功，后面的DELIMITER;的意思是恢复结束符号为;

存储过程名为productpricing,用CREATE PROCEDURE productpricing()语句定义， productpricing后面的()括号可以写入存储过程接受的参数，没有参数也需要要空括号，BEGIN和END语句用来限定存储过程体，这里的过程体仅是一个简单的SELECT语句。

这段代码只是创建了一个名为product-pricing新的存储过程，它是没有返回数据的，因为还没有调用它，只是为了以后需要用创建而已。

下面我们来调用一下它吧。

执行刚创建的存储过程并返回结果，存储过程实际上是一种函数，所以后面有括号，即使不用也要加。

删除存储过程

存储过程创建之后，被保存在服务器上面供以后使用的，直至删除。刚才已经演示了创建和调用，现在来看一下如何把存储过程删除的吧。

DROP PROCEDURE 存储过程名;

在关键字DROP PROCEDURE后面加上存储过程名即可，注意这里不需要加括号。

使用参数

上面的productpricing只是一个简单的过程，显示了SELECT语句的结果。但是一般存储过程并不显示结果，而是把结果返回给你指定的变量（内存中一个特定位置，用来临时存储数据）。下面我们来举个例子吧。

这个存储过程接受了3个参数，关键字OUT指出相应的参数用来从存储过程传出一个值返回给调用者， IN传递给存储过程，OUT和INUOT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内。SELECT语句用来检索值，然后通过指定INTO关键字保存到相应的变量。

为了调用修改的存储过程，必须指定3个变量名，就像下面这样。（MySQL变量都要以@开始）

想要显示检索可以像下面这样输入。

另外举个使用IN和OUT参数的例子。onumber定义为IN传入存储过程，ototal定义为OUT存储过程返回。SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计。

为调用这个新存储过程，可以用以下语句。

必须给ordertotal传递两个参数，两个参数计算出合计的变量名。

现在想要显示合计，可以像以下这样输入。

@total已由ordertotal的CALL语句填写，SELECT显示它包含的值。

为了得到另一个订单的显示，想要再次调用存储过程，然后重新显示变量。

检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句。

还有为了获取包括何时，由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。

使用游标

游标

游标是一个存储在MySQL服务器上的数据库查询。它不是一条SELECT语句，而是被SELECT语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。不像多少的DBMS，MySQL游标仅在存储程序和函数内被支持。语法如同嵌入SQL中。

使用游标

使用游标的几个重要步骤：

在能使用游标之前必须声明（定义）它。这个过程没有检索数据，它只是定义要使用的SELECT语句。

声明之后，必须要打开游标才能使用。这个过程就是用前面定义的SELECT语句把数据检索出来。

对于填有数据的游标，根据需要取出（检索）各行。

使用完游标，必须关闭游标。释放其占用的空间。

声明之后可以根据需要频繁的打开和关闭游标，在游标打开后，可以根据需要频繁地执行取操作。

创建游标

DECLARE关键字来创建游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。来看下它的语法格式是怎么样的吧。

DECLARE 游标名 CURSOR FOR SELECT语句;

使用关键字DECLARE语句创建游标，DECLARE命名游标，CURSOR FOR后面跟着SELECT语句，可以返回一行或多行数据。存储过程处理完成后，游标就消失（因为它仅限于存储过程）。

打开和关闭游标

创建声明游标之后，下一步就是打开了，打开我们用OPEN CURSOR语句来打开。

OPEN 游标名;

在处理OPEN语句时执行查询，存储检索出的数据以提供浏览和滚动。

游标处理完成之后我们需要用以下语句来关闭游标。

CLOSE 游标名;

关闭游标释放其占用的资源。如果不明确关闭游标，当语句执行到END之后MySQL就会自动将其关闭，以这种方式关闭的话下次再想使用时需重新创建。

明确关闭游标之后，要重新打开游标后才能使用它，但不需要再次声明，OPEN语句打开即可。

使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

触发器

触发器是与特定数据表相关联的存储过程。当相应的数据表被INSERT(增加)，DELETE(删除)，UPDATE(修改)语句修改时，触发器将自动执行。触发器可以被设置在语句执行前或执行后触发。换句话说就是触发器是MySQL响应增删改语句而执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）。

创建触发器

创建触发器要给出四个要素，分别是：

唯一的触发器名。(触发器名字)

触发器关联的表。（表名）

触发器应该响应的活动。（触发类型）

触发器何时执行。（处理之前和之后）

DELIMITER \\ CREATE TRIGGER 触发器名字 触发时间 事件类型 on 表名 FOR EACH ROW BEGIN SQL语句 END; DELIMITER;

下面我们来创建一个触发器吧。

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT（新增后），所以触发器会在INSERT语句执行后执行，这个触发器还指定FOR EACH ROW（行级触发），因此文本’product added’会插入每一行。

每个表每个次事件每次只允许一个触发器。到这里可以联想到，一个表中只能有6个触发器，这是因为（每个触发器只能在INSERT，UPDATE，DELECT的之前或之后）。单一的触发器不能与多个事件或多个表关联。

删除触发器

删除触发器用到的语法是DROP TRIGGER语句。

DROP TRIGGER 触发器名称;

演示一下删除刚才创建的newproduct触发器。

触发器不能更新或覆盖。如果要修改一个触发器必须先将其删除然后重新创建。

使用触发器

增删改三种触发器的类型都有所差别，下面分别详细说一下它们。

INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行，需要知道以下几点：

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行。

在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）。

对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

下面举个实际有用的例子。

此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFOREINSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

管理事务处理

事务处理

事务transaction处理是一种机制，用来管理必须成批执行的MySQL操作，以确保数据库不包含不完整的操作结果。利用事务处理（就像是做完成一件事一样）可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行返回（撤销）以恢复数据库到某个已知且安全的状态。

innoDB存储引擎提供一组用来记录事务性活动的日志文件。在事务执行过程中，每一条DML的操作都会记录在“事务性活动的日志文件”中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务commit：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务意味着事务的结束，并且是一种全部成功的结束。

回滚事务rollback：将之前的所有DML操作全部撤销，并且清空事务性活动的日志文件。回滚事务意味着事务的结束，并且是一种全部失败的结束。

下面列出了四个关于事务的术语和他们的含义：

事务（transaction）：指一组SQL语句。

回退（rollback）：指撤销指定SQL语句的过程。（只能回滚到上一次的提交点）

提交（commit）：指将未存储的SQL语句结果写入数据库表。

保留点（savepoint）：指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务不同）。

控制事务处理

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

使用ROLLBACK

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

这个例子从显示ordertotals表的内容开始。首先执行一条SELECT以显示该表不为空。然后开始一个事务处理，用一条DELETE语句删除ordertotals中的所有行。另一条SELECT语句验证ordertotals确实为空。这时用一条ROLLBACK语句回退START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空。显然，ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。

使用COMMIT

MySQL默认情况下是支持自动提交事务的（每执行一条DML语句，则提交一次），我们可以通过先执行start transaotion命令关掉自动提交。下面我们来看个例子。

从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

使用保留点

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符也称为保留点，为了创建占位符，可以使用SAVEPOINT。

SAVEPOINT deletel;

每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到本例给出的保留点，可如下进行：

ROLLBACK TO deletel;

更改默认的提交行为

MySQL默认情况下是支持自动提交事务的（每执行一条DML语句，则提交一次），我们可以更改这个默认提交行为。

SET autocommit = 0;

autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。

全球化和本地化

字符集和校对顺序

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同的方式存储和检索。因此，MySQL需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法。

下面有三个关语句和字符集的重要术语：

字符集：为字母和符号的集合。

编码：为某个字符集成员的内部表示。

校对：为规定字符如何对比的指令。

使用字符集和校对顺序

MySQL支持众多的字符集。为查看所支持的字符集完整列表，使用一下语句。

SHOW CHARACTER SET;

这条语句显示所有可用的字符集以及每个字符集的描述和默认校对。

为了查看所支持校对的完整列表，使用以下语句。

SHOW COLLATION;

此语句显示所有可用的校对，以及它们适用的字符集。可以看到有的字符集具有不止一种校对。例如，latin1对不同的欧洲语言有几种校对，而且许多校对出现两次，一次区分大小写（由_cs表示），一次不区分大小写（由_ci表示）。

通常系统管理在安装时定义一个默认的字符集和校对。此外，也可以在创建数据库时，指定默认的字符集和校对。为了确定所用的字符集和校对，可以使用以下语句。

SHOW VARIABLES LIKE ‘character%’;

SHOW CARIABLES LIKE ‘collation%’;

实际上，字符集很少是服务器范围（甚至数据库范围）的设置。不同的表，甚至不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定。

为了给表指定字符集和校对，可使用带子句的CREATE TABLE 。

此语句创建一个包含两个列的表，并且指定一个字符集和一个校对顺序。

这个例子中指定了CHARACTER SET和COLLATE两者。一般，MySQL如下确定使用什么样的字符集和校对。

如果指定CHARACTER SET和COLLATE两者，则使用这些值。

如果只指定CHARACTER SET，则使用此字符集及其默认的校对。

如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。

除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们，如下所示：

这里对整个表以及一个特定的列指定了CHARACTER SET和COLLATE。

如前所述，校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行：

此SELECT使用COLLATE指定一个备用的校对顺序（在这个例子中，为区分大小写的校对）。这显然将会影响到结果排序的次序。

安全管理

访问控制

MySQL服务器的安全基础是：用户不能对过多的数据具有过多的访问权。

举几个例子大概就知道为什么要访问控制了。比如：我想允许客户添加数据但不允许他删除。某些表我想只供用户读取，不允许做增删改。想根据用户的登录地点限制对某些功能的访问。等等这些问题。

这些都只是例子，但有助于说明一个重要的事实，即你需要给用户提供他们所需的访问权，且仅提供他们所需的访问权。这就是所谓的访问控制，管理访问控制需要创建和管理用户账号。

管理用户

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。一般不需要直接访问mysql数据库和表（你稍后会明白这一点），但有时需要直接访问。需要直接访问它的时机之一是在需要获得所有用户账号列表时。为此，可使用以下代码。

mysql数据库有一个名为user的表，它包含所有用户账号。user表有一个名为user的列，它存储用户登录名。新安装的服务器可能只有一个用户（如这里所示），过去建立的服务器可能具有很多用户。

为了创建一个新用户账号，使用CREATE USER语句，下面演示一下。

CREATE USER创建一个新用户账号。在创建用户账号时不一定需要口令，不过这个例子用IDENTIFIED BY 'p@$$wOrd'给出了一个口令。如果你再次列出用户账号，将会在输出中看到新账号。

为了删除一个用户账号（以及相关的权限），使用DROP USER语句，如下所示。

在创建用户账号后，必须接着分配访问权限。新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。为看到赋予用户账号的权限，使用SHOW GRANTS FOR，如下所示.

输出结果显示用户bforta有一个权限USAGE ON .。USAGE表示根本没有权限（我知道，这不很直观），所以，此结果表示在任意数据库和任意表上对任何东西没有权限。

为设置权限，使用GRANT语句。GRANT要求你至少给出以下信息。

要授予的权限。

被授予访问权限的数据库或表。

用户名。

此GRANT允许用户在crashcourse.*（crashcourse数据库的所有表）上使用SELECT。通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限。

输入SHOW GRANTS FOR反映这个更改。

每个GRANT添加（或更新）用户的一个权限。MySQL读取所有授权，并根据它们确定权限。

GRANT的反操作为REVOKE，用它来撤销特定的权限。下面举一个例子。

这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。被撤销的访问权限必须存在，否则会出错。

为了更改用户口令，可使用SET PASSWORD语句。新口令必须如下加密：

SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。

SET PASSWORD还可以用来设置你自己的口令。

在不指定用户名时，SET PASSWORD更新当前登录用户的口令。

数据库维护

备份数据

像所有数据一样，MySQL的数据也是必须经常备份的。可通过下面该文章深入了解备份的底层原理。

学习MySQL备份一篇就够了！！！（完全备份、增量备份、备份恢复）_mysql备份数据库_下一个艺术家的博客-CSDN博客

进行数据库维护

MySQL提供了一系列的语句，可以（应该）用来保证数据库正确和正常运行。ANALYZE TABLE，用来检查表键是否正确。

ANALYZE TABLE 表名;

CHECK TABLE发现和修复问题：

CHECK TABLE 表名, 表名;

诊断启动问题

在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld命令行选项：

--help显示帮助——一个选项列表。

--safe-mode装载减去某些最佳配置的服务器。

--verbose显示全文本消息（为获得更详细的帮助消息与--help联合使用）；

--version显示版本信息然后退出。

查看日志文件

MySQL维护管理员依赖的一系列日志文件。请参考下面的文章。

[玩转MySQL之八]MySQL日志分类及简介 - 知乎 (zhihu.com)