从入门到入土：MySQL完整学习指南，包教包会！

 

 一 SQL 介绍

SQL（Structured Query Language）,语义是结构化语言， 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统；

二 数据库介绍

2.1 数据库

我们已经知道了SQL是操作数据库的语言，那么数据库是什么？数据库（database）是 保存一个文件或者一组文件的容器；这样说你可能还不懂，可以比喻为数据是一个图书馆，图书馆里面收藏有好多书，我们可以认为数据库就是能够存储数据的容器；其实数据库很常见，你每天都在操作数据库，比如你看手机，上网，微信聊天，其实背后都在操作数据库，只是展现操作数据库的形式不一样而已；

2.2 数据库管理系统

数据库里面有好多的数据，就像图书管一样有好多的书，书是由图书管理员进行管理，那么什么东西能够管理数据库呢？没错就是数据库管理系统（Database Manage System），比如 mysql, Access、DB2、Informix、 Server、Oracle、Sybase以及其他数据库系统；

2.3 表

表是一种结构化的文件可以用来存储特定类型的数据；这么说你可能没听懂，我们再举个例子，表就像图书馆里面的图书架子，每个书架都放了好多书；再不懂也没关系，你总用过excel吧，excel表格里面的标签页 （sheet） 我们就可类比数据表，表存放的就是特定类型的数据，跟excel的sheet一样；重点是每个表的表名是唯一，不可重复；

2.4 列和数据类型

列是表中的一个字段，一个表由多个列组成；每个列都由特定的数据类型，只能存放指定数据类型的数据；听不懂没关系，我们类比excel，excel中的sheet也是由多个列组成，每个列都有指定的数据类型，比如文本，数字，日期类型；那数据库的表跟excel的结果是类似的；

数据类型就是限定表中的每个列只能存储特定类型的数据，常见的数据类型有整型，数字，文本，字符串，日期等等。

2.5 行

行就是表中的一条记录，类比excel，很容易理解；

2.6 主键

主键就是每行的唯一标识，其特性是主键，不能为空，不能重复，不能修改；主键我们可以类比身份证号，每个身份证号都是唯一不可重复的；

2.7 行号

行号指表中每个行的行号；

在这里插入图片描述

三 基本检索

如果是初学者，建议去网上寻找安装Mysql的文章安装，以及使用navicat连接数据库，以后的示例基本是使用mysql数据库管理系统；

需要建立一张学生表，列分别是id，名称，年龄，学生信息；本示例中文章篇幅原因SQL注释略；

建表语句：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


表中的数据：

INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (1, 'youku1', 18, '大一新生');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (2, 'youku2', 23, '毕业生');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (3, 'jeff', 25, '社会人士');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (4, 'smile', 17, '高三学子');

3.1 select 关键字

sql 都是由许多关键字（keyword）组成的语句，关键字是数据库的保留字，用户不能将其当作建表的表名，字段等；表中的数据检索使用select关键字作为开头进行查询数据库表的信息；

3.2 检索单个字段

语句示例：

SELECT name FROM student 


查询结果：

youku1
youku2
jeff
smile

语句分析：

select 是 查询 的意思 ， name 代表表中的字段 ，from 表示来自哪张表，其后面的student就是表；连起来的意思就是查询字段是name的数据来自表student；

3.3 sql语句注意点

多sql语句一起执行使用封号（;）隔开，在拼写语句时表的关键字都建议使用大写，表的字段和表名使用小写；为了容易阅读建议将sql分成多行（由于文章篇幅原因就不分了）；sql语言也是使用英文字母，不要开中文，以免造成符号错误不容易发现；sql语句默认不区分大小写；

3.4 检索多个字段

语句示例：

SELECT name,age FROM student;


语句结果：

youku1 18
youku2 23
jeff 25
smile 17

语句分析：

查询字段 名字，年龄来自表student；多个列检索时使用逗号（,）隔开；

3.5 检索所有字段

语句示例：

SELECT * FROM student;


语句结果：

1 youku1 18 大一新生
2 youku2 23 毕业生
3 jeff 25 社会人士
4 smile 17 高三学子

语句分析：

通配符 （*）表示返回表中的所有列，如果不是必须建议不要使用通配符，会影响数据库性能；

3.6 distinct

distinct 表示区分，意指检索出来的行是唯一（去重），其放在列的最前面；如果使用了关键字distinct，其作用于后面的所有列（由于本示例数据没有重复示例，结果就不是很清晰）；

SELECT DISTINCT name, age FROM student;


语句结果：

youku1 18
youku2 23
jeff 25
smile 17

3.7 限制条数

access 和 sql server ：

SELECT TOP 2 name FROM student


TOP 2 表示限制返回前2行

postgresql 、SQLite和 mysql：

SELECT name FROM student LIMIT 2;

limit 2 表示限制返回前2行；

DB2：

SELECT name FROM student FETCH FIRST 2 ROWS ONLY


FETCH FIRST 2 ROWS ONLY 表示只抓取前2行数据

语句结果：

youku1
youku2

3.8偏移

示例：

SELECT name FROM student LIMIT 1 OFFSET 1;


语句分析

表示查询列名称来自学生表 限制条数1，偏移值1；意思就是查询学生表中的第二行数据；offset表示跳跃或者偏移；

mysql和MariaDB简化形式:

SELECT name FROM student LIMIT 1,2;

表示查询字段名称来自学生表，限制2条，偏移1条；注意顺序；

语句结果：

youku2
jeff


四 排序检索

4.1 ORDER BY 子句

示例：

SELECT name,age FROM student ORDER BY age

语句分析；

检索字段名称，年龄来自学生表按照列年龄排序；注意默认是升序，ORDER BY 子句通常在语句末尾；

语句结果：

smile 17
youku1 18
youku2 23
jeff 25


4.2 多列排序

示例：

SELECT name,age FROM student ORDER BY age DESC, name ASC;

语句分析：

查询名称，年龄来自学生表，按照年龄降序，名称升序进行排序；关键字 DESC(descending) 意指降序，字母默认Z-A; ASC（ascending）意指升序，字母默认A-Z；多列情况下，每个列后面指定使用DESC，使用逗号（,）隔开，如果不写，默认升序；

语句结果：

jeff 25
youku2 23
youku1 18
smile 17


4.3 按位排序

语句示例：

SELECT name,age FROM student ORDER BY 2 DESC, 1 ASC;

按位指查询字段的位置，2 对应字段age,1对应字段name；结果和4.2一致；

五 过滤检索

sql 语句中过滤条件（filter condition）的关键字是 WHERE，其使用方式是跟在表名之后；

5.1 WHERE语句操作符

根据不同的数据库管理系统，其支持的操作符略有不同，所以下文较多重复意思的操作符，应查阅官方文档说明，哪些操作符是支持使用的数据库管理系统；

操作符	说明
=	等于
>	大于
<	小于
!=	不等于
<>	不等于
>=	大于等于
<=	小于等于
!<	不小于
!>	不大于
BETWEEN	在中间
IS NULL	为空

5.2 单条件过滤

示例：

SELECT * FROM student WHERE name = 'jeff';



语句分析;

查询所有字段来自学生表 条件是 学生名称是 jeff；注意 非表字段，表名，关键字，使用2个单引号（''）括起来，里面存放的是我们输入的数据；

语句结果;

3 jeff 25 社会人士

5.3 多条件过滤

多条件过滤时使用 AND 或者 OR 子句；AND连接表达式表示过滤条件都为真的数据；OR连接表达式表示匹配过滤条件任意一个；

AND示例：

SELECT * FROM student WHERE age >= '18' AND age <= '23';



语句分析：

查询所有字段来自学生表，条件是 学生年龄大于等于18 并且 学生年龄小于 23；

语句结果：

1 youku1 18 大一新生
2 youku2 23 毕业生

OR示例：

SELECT * FROM student WHERE age >= '18' OR age <= '23';



语句分析：

检索所有字段来自学生表，条件是 学生年龄大于等于18，或者学生年龄小于23；

OR 和 AND 示例：

SELECT * FROM student WHERE age >= '18' AND (age <= '23' OR id >=2);

语句分析：

在使用OR 和 AND 的时候应该明确过滤条件，然后使用小括号括起来，由于数据库管理系统是根据顺序执行，如果不使用小括号很容易造成语义错误；查询所有字段来自学生表 过滤条件 年龄大于 18 并且 （年龄小于23 或者 id 大于2）的数据；

5.4 范围查询

示例：

SELECT * FROM student WHERE  age BETWEEN '18' And '23';



语句分析

BETWEEN 表示范围查询，查询所有字段来自学生表，过滤条件学生年龄在18至23之间；

语句结果：

1 youku1 18 大一新生
2 youku2 23 毕业生

5.5 空值查询

示例：

SELECT * FROM student WHERE  age IS NULL;



语句分析：

查询所有字段来自学生表，过滤条件 学生年龄 为空；数据库表不填充数据默认为空（NULL），当然你也可以给指定类型的列设置默认值；

5.6 IN 操作

示例：

SELECT * FROM student WHERE  age IN (18,23,25);

语句分析：

查询所有字段来自学生表 ，过滤条件 年龄 是 18 或者 23 或者 25 ；IN是个范围查询，匹配小括号中指定的任意值，其功能跟OR类似，一个IN 就相当于好多个OR；

语句结果：

1 youku1 18 大一新生
2 youku2 23 毕业生
3 jeff 25 社会人士


5.7 NOT 操作符

示例：

SELECT * FROM student WHERE  NOT age='25';

语句分析：

NOT 操作符表是否定；其跟在WHERE后面功能类似<>;

六 通配符检索

6.1 通配符的介绍

通配符是组成匹配模式的特殊字符串；如果有用过正则表达式的同学肯定不陌生，这个与其类似；检索文本的通配符是用在关键词Like 后面；

6.2 通配符 %

示例：

SELECT * FROM student WHERE name LIKE 'you%' ;



语句分析：

查询所有字段来自学生表，过滤条件 名字匹配以you开头的字符串后面匹配任意个任意字符；%通配符代表任意个任意字符串,0也算在内，但不包括null；

语句结果：

1 youku1 18 大一新生
2 youku2 23 毕业生

示例：

SELECT * FROM student WHERE name LIKE '%i%' ;



语句分析：

查询所有列来自学生表，过滤条件学生的名称匹配中间必须出现一次字母i，字母i的前后可以匹配任意个任意字符串；

语句结果;

4 smile 17 高三学子

6.3 通配符 _

通配符 _ 代表匹配一个字符串；在Access数据库中不是 _ 而是 ?；

示例：

SELECT * FROM student WHERE name LIKE 'youku_' ;



语句分析：

查询所有列来自学生表，过滤条件 学生名称 匹配模式 youku 后面一个为任意字符串；

语句结果;

1 youku1 18 大一新生
2 youku2 23 毕业生

6.4 通配符 []

通配符 [] 表示匹配指定一个位置一个字符；其里面可以存放多个字符，关系是or，模式匹配时只占用一个位置，Access,SQL SERVER 支持；

七 字段基本操作

7.1 字段拼接

示例：

SELECT concat('你好啊',name,'327今天心情怎么样') FROM student WHERE id = '1' ;



语句分析：

函数 concat 就是将多个字符子串拼接成一个字符串；不同的数据库管理系统其使用的方式略有差别，应查阅官方文档；在 mysql 中使用 concat函数；在postgresql中使用 || ；在 Access和 sql server中使用 +;

语句结果：

你好啊youku1327今天心情怎么样

7.2 去除空白字符串

语句示例：

SELECT  RTRIM('      哥，今天管饱        ') FROM student WHERE id = '1' ;



语句分析：

RTRIM(STR) 函数是去掉右边的字符串；TRIM(STR)是去掉字符串两边的空白字符；LTRIM(STR)是去掉字符串左边的空白字符；

语句结果;

      哥，今天管饱

7.3 别名

语句示例：

SELECT name as student_name FROM student WHERE  id = '1' ;



语句分析：

别名（alias）是可以给字段，或者表起别名；当多表操作出现重复字段时使用别名是个很好的选择；别名可以使用AS关键字，虽然其是可省略，但平时我们最好加上它增强阅读性；

7.4 计算

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

语句示例：

SELECT 2 * 8; 

语句结果：

16


# 八 聚集函数的使用

先声明一下，下面的库表只是简易的学习示例，不是生产的设计，不要深究，此文我们的目的是学习sql的检索不是库表设计；初学者最好跟着作者的文章一步一步敲一遍，如果没有使用过sql的可以查阅作者SQL系列专栏；

顾客表

CREATE TABLE `customer` (
  `userId` int(11) NOT NULL AUTO_INCREMENT COMMENT '顾客id',
  `userName` varchar(255) DEFAULT NULL COMMENT '顾客名称',
  `telephone` varchar(255) DEFAULT NULL COMMENT '顾客电话',
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`customer`(`userId`, `userName`, `telephone`) VALUES (1, 'zxzxz', '1327');
INSERT INTO `springboot`.`customer`(`userId`, `userName`, `telephone`) VALUES (2, 'youku1327', '1996');

商品表

CREATE TABLE `product` (
  `productId` int(11) NOT NULL AUTO_INCREMENT COMMENT '产品id',
  `productName` varchar(255) DEFAULT NULL COMMENT '产品名称',
  `price` varchar(255) DEFAULT NULL COMMENT '产品价格',
  PRIMARY KEY (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`product`(`productId`, `productName`, `price`) VALUES (1, '苹果', '5');
INSERT INTO `springboot`.`product`(`productId`, `productName`, `price`) VALUES (2, '梨', '4');
INSERT INTO `springboot`.`product`(`productId`, `productName`, `price`) VALUES (3, '香蕉', '3');



订单表

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `userId` int(11) DEFAULT NULL COMMENT '客户id',
  `productId` int(11) DEFAULT NULL COMMENT '产品id',
  `orderName` varchar(255) DEFAULT NULL COMMENT '订单名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`order`(`id`, `userId`, `productId`, `orderName`) VALUES (1, 1, 1, '乖乖订单');
INSERT INTO `springboot`.`order`(`id`, `userId`, `productId`, `orderName`) VALUES (2, 2, 2, '悦悦订单');
INSERT INTO `springboot`.`order`(`id`, `userId`, `productId`, `orderName`) VALUES (3, 1, 3, '香香订单');

聚集函数的定义就是讲一些行的数据运行某些函数，返回一个期望值；下面讲述的是开发中经常使用到的聚集函数；

8.1 avg()

avg函数也就是计算行的数量，通过计算这些行的特定列值和，计算出平均值(特定列值之和/行数=平均值)；使用时注意其会忽略列值为NULL的行；

语句示例：

SELECT AVG(price) FROM product;



语句结果：

4

语句分析：查询价格平均值来自商品表（5+4+3）/3=4；

8.2 count()

count函数用于计算行数，其中count(*)计算所有行的数目，count('column')会忽略column为NULL的行数；

语句示例：

SELECT count(*) FROM product;



语句结果：

3

语句分析：

查询总行数来自商品表；

8.3 max()

max函数返回特定列值的最大值；忽略特定列为NULL的行；

语句示例：

SELECT max(price) FROM product;



语句结果：

5

语句分析：

查询价格的最大值来自商品表；

8.4 min()

返回特定列的最小值；忽略特定列为NULL的行；

语句示例：

SELECT min(price) FROM product;



语句结果：

3

语句分析：

查询价格的最小值来自商品表；

8.5 sum()

返回特定列的和；忽略特定列为NULL的行；

语句示例：

SELECT sum(price) FROM product;



语句结果：

12

语句分析：

查询价格的总和来自商品表；

九 分组数据

分组定义就是按照特定的列进行分组查询，使用 GROUP BY 子句进行分组查询；注意点：SELEC后面的列必须出现在group by 子句后面，否则报语法错误；通常 group by 子句的位置是where 条件之后，order by 子句之前；

9.1 分组求和

语句示例：

SELECT sum(price) FROM product GROUP BY productName;



语句结果：

4
5
3

语句分析：

先根据商品名称分为三组 苹果 ，梨 ， 香蕉 ；再根据不同的分组求和，因为我们表中的数据只有这三条所以就是每行的值；

9.2 分组过滤

语句示例：

SELECT count(*) FROM `order` GROUP BY userId HAVING count(*) > 1;



语句结果：

2

语句分析

查询 条数来自 订单表 根据客户id分组，过滤条件 条数大于2；注意 having 与 where其实差别不大，通常我们讲where当作标准的过滤条件，having用作分组过滤条件；注意有的数据库管理系统having不支持别名作为分组过滤条件中的一部分；

9.3 分组排序

语句示例：

SELECT count(*) as count FROM `order` GROUP BY userId ORDER BY count;



语句结果：

1
2

语句分析查询 行数 来自 订单表 根据 客户id分组，根据 行数排序；注意点是经过分组后结果看似经过排序，其实并不能确保是排序后的结果，所以要排序一定要使用order by子句；

十 子查询

子查询的定义是在查询中嵌套查询；注意子查询只能返回单列，若企图返回多列会报语法错误；

语句示例：

SELECT
 userName 
FROM
 customer 
WHERE
 userId = ( SELECT userId FROM `order` WHERE orderName = '乖乖订单' )


语句结果：

zxzxz

语句分析：

是执行语句 【SELECT userId FROM order WHERE orderName = '乖乖订单' 】得到结果 userId = '1' ;然后执行语句 【 SELECT userName FROM customer WHERE userId = '1'】；

十一 联结表

联结表也就是我们通常意义上的关联表查询，主要功能是能在多表中使用一条sql检索出期望值，但实际库表中是存在的，只在查询期间存在；其主要分为内联结和外连接使用的 join  关键字；联结表会返回一对多，一对一，多对多关系；联结表不建议超过三张表以上；

11.1 简单联结

语句示例：

SELECT
 userName,
 orderName 
FROM
 customer,
 `order` 
WHERE
 customer.userId = `order`.userId;


语句结果：

zxzxz 乖乖订单
youku1327 悦悦订单
zxzxz 香香订单

语句分析 ：

查询 用户名来自用户表，查询订单名称来自订单表，根据 订单表的客户id 等于 客户表的客户id做为联结条件；也就是说会查询出两张表根据userId为等值条件的 userName 和 orderName 的 数据;

注意点 ：简单联结中where子句后面 必须 要带上 两张表的联结关系，否则会出现笛卡尔集（比如3行数据联结另一张表3行数据会产生3*3=9条）

11.2 内联结

内连接（inner join） 又称等值联结，其查询结果跟之前的简单联结一致；

语句示例：

SELECT
 userName,
 orderName 
FROM
 customer
 INNER JOIN `order` ON ( customer.userId = `order`.userId );


语句结果：

zxzxz 乖乖订单
youku1327 悦悦订单
zxzxz 香香订单

语句分析：

跟之前的简单联结稍微不同的是 等值条件 是放在 on 关键字后面，在等值条件后面还可以进行 where 子句过滤条件查询；

11.3 自然联结

自然联结与标准的联结不同就是只返回值唯一的列，不会返回重复的列；

自然联结示例：

SELECT
 userName,
 orderName 
FROM
 customer
 INNER JOIN `order` ON ( customer.userId = `order`.userId );


自然联结结果

zxzxz 乖乖订单
youku1327 悦悦订单
zxzxz 香香订单

非自然联结示例：

SELECT
 * 
FROM
 customersqL
 INNER JOIN `order` ON ( customer.userId = `order`.userId ); 


非自然联结结果：

1 zxzxz 1327 1 1 1 乖乖订单
2 youku1327 1996 2 2 2 悦悦订单
1 zxzxz 1327 3 1 3 香香订单

重复列是 userId;

11.4 外联结

右外联结

语句示例：

SELECT
 * 
FROM
 `order`
 RIGHT OUTER JOIN customer ON ( customer.userId = `order`.userId ); 


右外联结是指 相对于 OUTER JOIN 右边的表，那么这会查询出右边表的所有数据 和根据等值条件匹配左边表的数据，如果左边表的数据不匹配，那么其返回列的值是NULL充当；

左外联结

语句示例：

SELECT
 * 
FROM
 customer
 LEFT OUTER JOIN   `order` ON ( customer.userId = `order`.userId ); 

左外联结是指 相对于 OUTER JOIN 左边的表，那么这会查询出左边表的所有数据 和根据等值条件匹配右边表的数据，如果右边表的数据不匹配，那么其返回列的值是NULL充当；

区别：

左外联结和右外联结其实没什么不同，只是查询表顺序不一致，我们通过置换 表的相对位置就可以查询出一样的结果；

十二 组合查询

组合查询是指可以执行多条SELECT 语句，其查询的结构是一致的，返回查询结果，通常我们称为复合操作或者并(union)

语句示例：

SELECT
 userId 
FROM
 customer UNION
SELECT
 userId 
FROM
 `order`


返回结果：

1
2

语句分析：

union 关联的字段或者聚合函数在两张表中必须是相同的，其默认会讲结果进行去重处理；如果不去重可以使用 union all

语句示例：

SELECT
 userId 
FROM
 customer UNION ALL
SELECT
 userId 
FROM
 `order`


执行结果：

1
2
1
2
1

语句分析：等同于讲客户表和订单表的用户id都合并为一个并集查询出来，而且不去重；如果对组合语句进行排序，默认是会作用于组合后的数据字段排序，而不是作用于其中的一条查询语句；

十三 插入

插入数据库记录也就是使用 INSERT 关键字，能将一条语句插入数据库，高级的可以组合 SELECT 关键字 实现 插入查询的结果集，插入整张表；

建表语句：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `telephone` varchar(255) DEFAULT NULL COMMENT '用户电话',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (1, 'youku1', 18, '大一新生');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (2, 'youku2', 23, '毕业生');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (3, 'jeff', 25, '社会人士');
INSERT INTO `springboot`.`student`(`id`, `name`, `age`, `info`) VALUES (4, 'smile', 17, '高三学子');



13.1 插入一条完整数据

语句示例：

INSERT INTO `user` ( id, `name`, telephone) VALUES ('2','zszxz','1327');

语句结果：

2 zszxz 1327


语句分析：

插入 数据 到 user 表 字段分别是 id, name , telephone; 值 分别是 2 , zszxz , 1327; 这是插一条完整的语句，虽然INTO可以忽略不写，但不建议这么做，原因是在数据库管理系统间会出现移植性问题；还有字段也可以忽略不写，但也不建议这么做，这容易造成插入数据出错；字段的位置和值的位置是一 一对应；如果有的位置没值可以使用NULL代替；

13.2 插入部分数据

语句示例：

INSERT INTO `user` ( id, `name`) VALUES ('3','zszxz');

语句结果：

3 zszxz 


语句分析：

插入数据到user表，字段分别是 id , name ; 值分别是，3，zszxz; 可以看见我们没有插入telephone字段；

13.3 插入检索数据

插入检索的数据也就是能将查询的结果插入进另一张表；我们可以使用 INSERT SELECT 关键组合成一条语句实现；

语句示例：

INSERT INTO `user` ( id, `name`) 
SELECT id, `name` FROM student WHERE id = '4';

语句结果：

4 smile 


语句分析：

插入数据到 user 表， 字段分别是 id, name ,值是查询字段 id ,name 来自 student 表，条件是 id 等于 4；可以看见我们插入数据的列根查询的列名称是匹配对应的，其实只要列顺序一致即可，不过为了不出错，建议使用名称匹配；

13.4 复制表

复制表即，检索一张表的数据全部插入另一张表；有两种方法，但是不同的数据库管理系统支持不同，具体的看下文；

语句示例：

SELECT id , `name`  INTO student_copy FROM student;

语句分析

查询字段 id, name  插入 student_copy 表，来自 student 表；注意 这条语句会帮我们自动创建表 student_copy，由于作者使用的是mysql做演示，这条sql执行失败，原因是其不支持这种方式；如果是想复制整张表可以使用通配符 * ；

语句示例：

CREATE TABLE student_copy AS 
SELECT * FROM student;


语句结果：

1 youku1 18 大一新生
2 youku2 23 毕业生
3 jeff 25 社会人士
4 smile 17 高三学子

语句分析：

创建表  student_copy 数据结构来源 查询 所有字段来自 student 表；

十四 更新

更新数据库的行使用 UPDATE 关键字；更新操作是个很危险的操作，在每次执行前都应该检查是否丢了 where 子句；

14.1 更新所有行

语句示例：

UPDATE student_copy set age = Null;


语句结果：

1 youku1  大一新生
2 youku2  毕业生
3 jeff  社会人士
4 smile  高三学子

语句分析：

更新 student_copy 表， 设置 字段 age 值为null；可以看见表中所有的学生年龄都是Null; 如果有多个字段需要更新，使用 逗号隔开；

14.2 更新特定的行

语句示例：

UPDATE student_copy set age = '18' WHERE id = '4';


语句结果：

4 smile 18 高三学子

语句分析：

更新 student_copy 设置 学生的年龄是 18 条件是 id 等于 4；

14.3 更新来自查询的结果集

语句示例：

UPDATE student_copy set age= student.age, name = student.name
FROM student
WHERE student.id = student_copy.id;


语句分析：

更新 student_copy 表 设置 age 是 student 表的 age，name 是 student 表的 name 条件是 student 的id 等于 student_copy 表的 id;  遗憾的是 mysql 数据库管理系统又执行失败了，其不支持这种方法更新，如果是postgresql就支持，其他数据库应查阅官方文档查看是否支持这种方式更新；

语句示例：

UPDATE student_copy INNER JOIN student on  student.id = student_copy.id 
SET student_copy.age= student.age, student_copy.name = student.name;

语句结果：

1 youku1 18 大一新生
2 youku2 23 毕业生
3 jeff 25 社会人士
4 smile 17 高三学子


语句分析更新 student_copy 关联 student 条件 是 student 的 id 等于 student_copy 表的id ; 设置 student_copy 表的 age 等于 student 的 age ; 设置 student_copy 表的 name 等于 student 的 name ；这才是正确进入Mysql 的更新查询姿势；

十五 删除表

删除表中的行可以使用 DELETE 关键字 ，可以删除特定的行或者全部；使用时请先看是否丢了where子句；

15.1 删除整张表数据

DELETE from student_copy;

语句分析

删除 全部行 来自 student_copy 表；

15.2 删除特定的行

语句示例：

DELETE from student WHERE id = '4';


语句分析：

删除 行 来自 student 表条件时 id 等于 4；

15.3 更新和删除的建议

1. 每次进行操作前检查是否丢失 where 子句；
2. 每次操作前最好先使用 select 语句验证；

十六 SQL 分类操作

16.1 SQL 分类

sql 对数据库的操作分为如下三种类型，如果都学会这三种SQL语言熟练对数据库操作，说明你已经对数据库登堂入室，如果再学会数据库高级操作，说明你对数据库就有一定的使用经验，如果你还学会对数据库进行优化，分表分库，读写分离等操作，说明你使用数据库已经到专家级别；

1. DDL：数据定义语言（Data Define Language），即定义数据的结构。比如：create，drop，alter操作
2. DML：数据管理语言（Data Manage Language），即增删改查。比如：insert，delete，update，select。这个也在之前的文章详细写过，不再提及；
3. DCL：数据控制语言（Data Control Language），对权限、事务等的控制。比如：grant（授权）revoke（取回权限），commit，roolback等; 事物在上篇已经说明，不会在本篇提及；

16.2 数据库基操

数据库的基本操作如下，也就是我们日常使用的操作

1. 连接数据库：mysql -h 地址 -P 端口 -u 用户名 -p 密码；

   mysql -h 192.168.0.127 -P 3306 -u root -p root
   

2. 查看当前数据库：

   SELECT DATABASE();


3. 显示用户活动线程：

   SHOW PROCESSLIST;
   

4. 显示系统变量：

   SHOW VARIABLES;


5. 显示当前时间，用户，数据库版本号

   SELECT now(), user(), version();
   

6. 创建数据库：CREATE DATABASE[ IF NOT EXISTS] 数据库名 [数据库选项]

   数据库选项：
CHARACTER SET 字符集名称
COLLATE 排序规则名称
示例：create database zszxz;


7. 删除数据库：DROP DATABASE [ IF EXISTS] 数据库名;

   drop  database zszxz;
   

16.3 建表语句

数据库表的日常操作如下

CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 ) [ 表选项]


其中  TEMPORARY 表示临时表；中括号内容都表示可选，在正规的数据库版本管理开发会经常使用到；

字段的修饰如下 数据类型

• 非空|空约束：[NOT NULL | NULL]
• 默认值：[DEFAULT default_value]
• 自动增长：[AUTO_INCREMENT]
• 唯一键|主键：[UNIQUE [KEY] | [PRIMARY] KEY]
• 备注：[COMMENT 'string']

表选项一般就是指定数据库引擎和字符集：

ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客表';

示例

CREATE TABLE IF NOT EXISTS `customer` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`customer_name` varchar(255) DEFAULT NULL COMMENT '顾客名称',
`gender` varchar(255) DEFAULT NULL COMMENT '性别',
`telephone` varchar(255) DEFAULT NULL COMMENT '电话号码',
`register_time` timestamp NULL DEFAULT NULL COMMENT '注册时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客表';


16.4 修改表结构

查看所有表

SHOW TABLES

查看指定数据库的表

SHOW TABLES FROM 数据库名称;
示例：SHOW TABLES FROM zszxz;


删除表

DROP TABLE[ IF EXISTS] 表名;
示例: drop table op;

清空表(清除数据)

TRUNCATE [TABLE] 表名


复制表结构

CREATE TABLE 表名 LIKE 要复制的表名;
示例: create table op like `order`;

复制表结构和数据

CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名;
示例: CREATE TABLE op AS SELECT * FROM `order`;


常见的alter操作如下：

增加一列(追加至末尾)

alter table [数据库名.]表名 add [column] 字段 数据类型;
示例：alter table `order` add column `year` year ;

增加到第一列

alter table [数据库名.]表名 add [column] 字段 数据类型 first;


增加一列到指定字段名后

alter table [数据库名.]表名 add [column] 字段 数据类型 after 另一个字段;

修改字段名的 数据类型

alter table [数据库名.]表名称 modify [column] 字段名 新的数据类型;
示例：altert table `order` modify column `gender` tinyint;


修改表字段的数据类型，并且移动至第一列

alter table [数据库名.]表名称 modify [column] 字段名 数据类型 first;

修改表字段的数据类型，并且移动至指定字段后面

alter table [数据库名.]表名称 modify [column] 字段名 数据类型 after 另一个字段名;


修改表字段的名称

alter table [数据库名.]表名称 change [column] 旧字段名 新的字段名 数据类型;

添加主键

alter table [数据库名.]表名称 ADD PRIMARY KEY(字段名);
示例：altert table `order` add primary key(`id`)


添加唯一键

alter table [数据库名.]表名称 ADD UNIQUE [索引名] (字段名)

添加索引

alter table [数据库名.]表名称 ADD INDEX [索引名] (字段名)


删除一列

alter table [数据库名.]表名称 drop [column] 字段名;
示例：altert table `order` drop column `gender`;

删除索引

alter table [数据库名.]表名称 DROP INDEX 索引名


删除主键

alter table [数据库名.]表名称 DROP PRIMARY KEY

删除外键

alter table [数据库名.]表名称 DROP FOREIGN KEY 外键


十七 视图

17.1 视图的概念

视图其实就是一张虚表，其本质上SQL的检索语句，所以其不储存任何的数据成分；我们使用视图有什么好处呢？

• 简化查询SQL，直接对视图进行查询，不用管视图具体生成的细节；
• 可以使用表的部分成为视图，保护数据，开放用户权限时，可以只开放视图，而不开放实体表；
• 修改数据结构，可以直接对已有的表建立视图，使用不同的表名，字段名称；

我们对视图的操作只能停留在查询上，如果是单表生成的视图还可以进行插入数据；如果是多表关联生成的视图，插入不会起作用；切记任何时候进行关联的时候如果关联3张表以上就是不符合规范，严重的拖累查询性能，视图也是如此，使用复杂的嵌套视图和多表关联也会极大的降低查询性能；

17.2 视图的规范

• 视图是虚表，其有表的部分特性；视图名 唯一 ，与表名类似；
• 如果非管理员用户，创建视图必须有创建权限；
• 视图本质是查询语句，故视图可以嵌套，可以与其它表进行联结；
• 视图不能有索引和触发器

17.3 视图语句

小伙伴们要记得使用视图之前要看看自己的MYSQL版本，5.0以上支持；

• 创建视图 与创建表类似 ， create view 语句用于创建视图
• 显示视图创建语句 使用 show create view viewName ；
• 删除视图 使用语句 drop view viewName
• 更新视图 使用语句 create or replace view

17.4 视图操作

我们的准备表如下

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
  `order_name` varchar(255) DEFAULT NULL COMMENT '订单名称',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `year` year(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

准备数据如下

INSERT INTO `zszxz`.`order`(`id`, `order_name`, `create_time`, `year`) VALUES (1, '知识追寻者的订单', '2020-03-04 11:01:25', 2019);
INSERT INTO `zszxz`.`order`(`id`, `order_name`, `create_time`, `year`) VALUES (2, '小天使的订单', '2020-03-04 11:01:25', 2020);
INSERT INTO `zszxz`.`order`(`id`, `order_name`, `create_time`, `year`) VALUES (3, '流浪者的订单', '2020-03-04 11:02:50', 2020);



新建简单的视图示例如下，使用 order 表 的 id , order_name , year 三个字段组成视图；as 后面就是查询语句，也可以是子查询，多表关联等复杂的查询语句；

create view `zszxz_order` as SELECT `id` , `order_name`, `year` from `order`

查询视图，其使用本质与查询表一样；示例如下

SELECT * from `zszxz_order`


向视图中插入数据，插入的数据实际在实体表 order 表中切记；

INSERT INTO `zszxz_order`(`order_name`, `year`) VALUES ('小凡汐的订单', 2020);

删除视图

drop view `zszxz_order`


17.5 小结

视图的本质上查询语句，故可以对一些简单的数据统计做成视图是个不错的选择；其次如果是开放权限给第三方公司，使用视图查询部分实体表的数据作为开放的表也是对视图的合理应用；最后，也可以将简单的表联结做成视图，简化开发；

由于视图的本质是查询语句，你可以理解为其前世就是查询，今生就是虚表，徒有其“表”，名不副实，只拥有表的部分功能；

十八 储存过程

18.1 储存过程的概念

我们经常使用的SQL查询语句都是单条语句，如果要使用多条语句达到一个目的就显得力不从心了，储存过程就是使用多条语句完成业务的操作，你可以理解为linux脚本编程类似，window的批处理文件那样；简单的定义储存过程就是多条SQL的集合；

我们使用储存过程能够简化复杂的单条SQL，相比于单条复杂的SQL极大提高了性能；如果表结构发生变化只需要改变储存过程使用到SQL语句的表名，如果业务逻辑发生变化，只需要跳转储存过程即可，具有很强的灵活性；建立一次储存过程，即可使用，不用反复建立，保证开发人员使用到都是相同的储存过程，保证数据可靠性；总之使用储存过程，简单，灵活，安全可靠，性能好；

18.2 存储过程语法

• 创建存储过程

Create PROCEDURE 储存过程名称 （参数列表）
begin
 过程体
end;

• 参数列表

IN 表示输入; 示例 IN var1 Decimal(6,2)

OUT 表示输出;示例 IN var2 Decimal(6,2)

INOUT 表示输入输出；示例 IN var3 Decimal(6,2)


• 变量

declare 变量名称 变量类型 [default value]

• 执行存储过程

call 储存过程名称


• 删除储存过程

DROP PROCEDURE 储存过程名称

• 赋值

使用 set 和 select into 语句为变量赋值。
set @var := 20
select sum(price) into total from table_name


• if 语句

f 条件 then
    表达式   
[elseif 条件 then
    表达式]
...
[else
    表达式]
end if;

• case语句

CASE 值 WHEN 匹配值 THEN 结果
[WHEN 匹配值 THEN 结果]
......
[ELSE 结果]
END


• while语句

[开始标签:]while 条件 do
    循环体
[结尾标签]    
end while ;

• loop 语句

[开始标签:] loop
语句体
[结尾标签]
end loop;


• iterate/ leave语句

其是通过标签可以实现；iterate 表示迭代， leave表示离开

• repeat语句

repeat
--循环体
until 循环条件  
end repeat;


tip : 如果你是用命令行学习，在写多行SQL的时候 使用 // 可以实现换行哟！

18.3 储存过程示例

我们准备表如下，一个订单明细表；

CREATE TABLE `oder_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `detail_name` varchar(255) DEFAULT NULL COMMENT '订单明细',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `oid` int(11) DEFAULT NULL COMMENT '订单id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='订单明细表';

准备数据如下

INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (1, '毛巾', 20.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (2, '牙膏', 15.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (3, '杯子', 5.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (4, '毛巾', 15.00, 2);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (5, '杯子', 15.00, 2);



无参储存过程

查看订单明细的所有订单名称,示例如下，跟普通的查询语句没区别；

create procedure slelect_detail()
begin
 select detail_name from oder_detail;
end; 

我们再调用储存过程

call slelect_detail();


此时就会打印内容如下

毛巾
牙膏
杯子
毛巾
杯子

删除储存过程

drop procedure slelect_detail;


带入参储存过程示例

现在我们需要查询oid为动态的所有订单明细名称，由于考虑到oid为动态，就是需要用户自己输入，故将oid作为入参;

create procedure slelect_detail(IN order_id INT)
begin
 select detail_name from oder_detail where oid = order_id;
end;

调用储存过程，只查询oid为1的用户的订单明细名称

call slelect_detail(1);


打印内容

毛巾
牙膏
杯子

删除储存过程

drop procedure slelect_detail;


带入参和出参的存储过程示例

查询任意用户的订单明细的所有金额；定义入参订单id 为 order_id ， 输出总金额为 total；

create procedure slelect_toatal_money(IN order_id INT, OUT total DECIMAL(8,2))
begin
 select sum(price) into total from oder_detail where oid = order_id;
end;

调用储存过程示例

call slelect_toatal_money(1,@total);


查询 order_id 为1 总金额示例

SELECT @total;

输出为 40；

删除储存过程

drop procedure slelect_toatal_money;


18.4 if 语句示例

上节的储存过程都是单条SQL，这次我们开始使用控制流程，实现复杂的储存过程；

知识追寻者对输入的 order_id 自动加5 ，然后判断 var 是否 小于7 ，如果是就查询订单明细价格，否则查询订单明细价格总和；

create procedure slelect_toatal_money(IN order_id INT)
begin
-- 定义变量
declare var  int;
-- 赋值
set var= order_id+5;
-- if 判断
if var<7 then
select price  from oder_detail where oid = order_id;
else
select sum(price)  from oder_detail where oid = order_id;
end if;
end;

调用

call slelect_toatal_money(1);


输出

price
20
15
5

调用

call slelect_toatal_money(2);


输出

sum(price)
30

18.5 while 语句示例

对 变量 var 进行判断，如果 var <7 就执行 查询价格语句，并且var 进行自增；

create procedure slelect_toatal_money(IN order_id INT)
begin
 -- 定义变量
 declare var  int;
 -- 赋值
 set var= order_id+5;
 -- while
 while var<7 do
  select price  from oder_detail where oid = order_id;
  set var = var + 1;
 end while;
end;


调用示例

call slelect_toatal_money(1);

输出

price 
20
15
5


18.6 case语句示例

如下语句实现的效果与上面if语句实现效果一致；

create procedure slelect_toatal_money(IN order_id INT)
begin
 -- 定义变量
 declare var  int;
 -- 赋值
 set var:= order_id;
 -- case 判匹配
 case var  
 when 1 then 
  select price  from oder_detail where oid = order_id;
 when 2 then
  select sum(price)  from oder_detail where oid = order_id;
 end case;
end;

调用示例

call slelect_toatal_money(2);


输出

sum(price)
30

将参数改为1试试结果

18.7 loop语句

如果 var 小于3 就 计算 价格 + var 的值；

create procedure slelect_toatal_money(IN order_id INT)
begin
 -- 定义变量
 declare var  int;
 -- 赋值
 set var:= order_id;
 -- loop
 select_loop :  loop
  select price+var  from oder_detail where oid = order_id;
  set var = var +1;
  -- 跳出循环
  if var > 3 then
   leave select_loop;
  end if;
 end loop;
end;


调用示例

call slelect_toatal_money(1);

会输出三组结果

18.8 repeat

repeat 与 while 的不同之处就是 ，while 在 执行之前检查条件，其实执行之后检查条件；

create procedure slelect_toatal_money(IN order_id INT)
begin
 -- 定义变量
 declare var  int;
 -- 赋值
 set var= order_id+5;
 -- repeat循环
 repeat
  select price  from oder_detail where oid = order_id;
  set var = var + 1;
  until var>7
 end repeat;
end;


调用示例

call slelect_toatal_money(1);

此时会输出2组相同结果；

price
20
15
5


tip: loop, while , repeat , iterate 都是循环，loop,while, repeat 功能几乎相同；iterate可以通过标签的形式调用 循环，与 leave 语句使用方式一样；

十九 游标

19.1 游标的概念

游标的本质就是查询后的结果集；当我们对查询的结果集进行前一行或者后一行类似的操作时就可以使用到游标

19.2 游标的语法

• 首先需要 定义游标；declare 游标名称 cursor for  查询语句；
• 其次，打开游标；open 游标名称
• 然后，对查询的结果集 即游标进行 检索行至变量提供使用
• 最后关闭游标；close 游标名称

19.3 使用游标

准备的表

CREATE TABLE `oder_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `detail_name` varchar(255) DEFAULT NULL COMMENT '订单明细',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `oid` int(11) DEFAULT NULL COMMENT '订单id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='订单明细表';

准备的数据

INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (1, '毛巾', 20.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (2, '牙膏', 15.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (3, '杯子', 5.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (4, '毛巾', 15.00, 2);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (5, '杯子', 15.00, 2);



简单的使用游标

查询oid为1 的订单明细名称 的结果集作为游标；

打开游标后抓取每行将结果赋值给变量name

CREATE PROCEDURE printName()
BEGIN
 -- 订单名称
    declare name varchar(20);
    -- 创建游标
    declare cur cursor for select detail_name from oder_detail where oid = '1';
    -- 打开游标
    open cur;
  fetch cur into name;
  select name;
    -- 关闭游标
    close cur;
END;

调用储存过程

call printName;


打印结果如下，只有一条数据，说明上述方式只在游标中抓取到一条数据，而且是表里面行号最小的行；

name
------
毛巾

在循环中使用游标

将 查询oid为1的 结果集赋值给游标；通过游标抓取每行 将 订单明细名称和价格分别赋值给变量 name 和 detail_price;  在 循环无法继续时 会出现 SQLSTATE '02000' ; 即此通过 变量 continue 时设置 done 为1 代表 true，此时循环结束，跳出循环；

drop procedure if exists  printDetail;
CREATE PROCEDURE printDetail()
BEGIN
 -- 订单名称
    declare name varchar(20);
  -- 价格
    declare detail_price decimal(8,2);
    -- 结束标志变量（默认为假）
    declare done boolean default 0;
    -- 创建游标
    declare cur cursor for select detail_name,price from oder_detail where oid = '1';
    -- 指定游标循环结束时的返回值
    declare continue HANDLER for SQLSTATE '02000' set done = 1;
    -- 打开游标
    open cur;
    -- 循环游标数据
    detail_loop:loop
   -- 根据游标当前指向的一条数据
   fetch cur into name,detail_price;
   select name , detail_price;
   -- 判断游标的循环是否结束
   if done then
     -- 跳出游标循环
     leave detail_loop;    
   end if;
  -- 结束游标循环
    end loop;
    -- 关闭游标
    close cur;
END;


调用储存过程

-- 调用存储过程
call printDetail();

美中不足的是会多遍历最后一行，如果要精细处理还是需要自定义标志位进行跳出循环；

二十 触发器

20.1触发器的概念

触发器是指当表发生改变的时候触发的动作；听起来有点抽象，举个栗子，当你往表中插入数据的时候，此时表发生了改变，现在想要在每次插入数据之前检测所有的入参是否都是小写，此时就可以用触发器来检测；经过上面的分析知道使用一个基本的触发器，至少表要发生改变，还要满足一个被触发的事件；

表发生改变通常指 增删改，其动作可以发生在增删改 之前或者之后；触发事件就是我们需要写的储存过程；

• update (after/ before)
• insert (after/ before)
• delete (after/ before)

20.2 触发器的基本语法

• 创建触发器：create trigger 触发器名称 触发动作  on 表名 for each row [触发事件]
• 删除触发器：drop trigger 触发器名称；
• 查看触发器：show triggers;

tip : 触发器是依赖于表创建，没有表就没有触发器，比如视图，临时表都不是真实的表，它们是没有触发器；一般来说每个表都有触发器的限制，一般最多支持6个不同类型的触发器；由于使用触发器会频繁的改变表的每行，故其十分影响性能，特别对一些更新频率比较快的大表，如果设置触发器就非常占用系统资源；一般来说触发器用在表变动较小的小表, 不使用触发器就立即删除；

20.3 insert 触发器示例

创建触发器; 创建一个触发器 getPrice 作用于 oder_detail 表的每行，每当 插入数据之后就查询这条订单明细的价格赋值给变量 @price ；小伙伴可能会疑惑 NEW 是何物，其是一张虚表，记录者被插入数据的行；故我们能在NEW表中获取每次插入的数据；

-- insert 触发器
CREATE TRIGGER getPrice AFTER INSERT ON oder_detail FOR EACH ROW
SELECT NEW.price INTO @price;


检测插入触发器; 插入一条数据，使用查询语句查询变量 显示为 20；

-- 检测插入触发器

INSERT INTO `oder_detail`( `detail_name`, `price`, `oid`) VALUES ( '脸盆', 20.00, 2);

select @price;

删除触发器;

-- 删除触发器
drop trigger getPrice;


20.4 update 触发器示例

将插入后触发器改为更新后的触发器如下 ， 只需要改动 after insert 为 after update 即可；

CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT NEW.price INTO @price;

将之前的插入的SQL语句进行修改价格,并查询价格,此时价格为30；NEW虚表储存的是即将更新的数据；

UPDATE `oder_detail` SET `price` = 30.00 WHERE `id` = 6;

select @price;


删除触发器

-- 删除触发器
drop trigger getPrice;

将 更新触发器的NEW表改为OLD表

CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT OLD.price INTO @price;


更新价格为40

UPDATE `oder_detail` SET `price` = 40.00 WHERE `id` = 6;

此时查询 价格为30，说明OLD表触发的是原始数据值；

select @price;


tip ：更新触发器主要是要搞懂OLD存放原始数据，NEW存放即将更新的数据；NEW表可以设置更改值，二OLD表是只读；

20.5 delete 触发器

将 更新触发器改为 delete 触发器, 之前我们省略了 begin, end 如果是多条执行语句则需要加上；

CREATE TRIGGER getPrice AFTER delete ON oder_detail FOR EACH ROW
begin 
SELECT OLD.price INTO @price;
end;

删除之前的SQL数据

delete from oder_detail where `id` = 6;


查询价格为40，OLD表存放的是将要被删除的数据；

select @price;

二十一 用户操作

有关用户账号的信息储存mysql的MYSQL数据库，故如果需要查看用户信息，则需要进入MYSQL库；

21.1查看用户信息

user表储存了所有的登陆账号；使用mysql库查询user表中的user；

use mysql;
select `user` from user;


打印

user
----
root
mysql.session
mysql.sys
root

21.2 创建用户

CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码

示例：创建用户 zszxz ,并指定密码为 zszxz；

create user zszxz IDENTIFIED by 'zszxz';


21.3 重名名用户

rename user 旧的用户名 to 新的用户名;

示例重命名 用户 zszxz 为 lsc

rename user zszxz to lsc;

21.4 删除用户

drop user 用户名

示例：删除用户lsc

drop user lsc;


21.5 更改密码

SET PASSWORD FOR 用户名 = PASSWORD('密码')

示例：为用户 zszxz 更改密码为 lsc

SET PASSWORD FOR zszxz = PASSWORD('lsc')

二十二 权限操作

22.1 查看用户权限

SHOW GRANTS FOR 用户名

示例：查看用户zszxz 拥有的权限

SHOW GRANTS FOR zszxz


打印

Grant for zszxz@%
----------------
GRANT USAGE ON *.* TO 'zszxz'@'%'

查询出一条权限，但 USAGE 表示 根本没有权限；

22.2 授予权限

GRANT 权限 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']

常见的权限 all, create, drop, insert, update, delete,select；

示例 给用户zszxz 分配 zszxz 库中的所有表的查询权限；

grant select on zszxz.* to zszxz;


再看下 zszxz变成 2 条

Grant for zszxz@%
----------------
GRANT USAGE ON *.* TO 'zszxz'@'%'
GRANT SELECT ON `zszxz`.* TO 'zszxz'@'%'

22.3 撤销权限

REVOKE 权限列表 ON 表名 FROM 用户名；

示例：撤销 用户 zszxz 对 zszxz库里面所有表的查询操作；

revoke select on  zszxz.* from zszxz


22.4 权限列表

使用授权，撤销权限时可以参考如下权限列表；

ALL --- 除GRANT OPTION外的所有权限
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 ---无访问权限

二十三 mysql 架构与锁

23.1 MYSQL架构概览

MYSQL 的层级大概可以分为3类；第一层 为 连接层，只要负责MYSQL的数据库连接，安全认证的功能；

第二层是MYSQL的核心层面，其主要功能包括，MYSQL的查询，缓存，执行计划，优化等都在第二层实现；

第三层是引擎层，为MYSQL指定不同的引擎将达到不同的数据操作效果；

23.2 Query Cache

MYSQL 的 Query Cache 是 基于 hash 值计算进行匹配的缓存机制；通常在大数据量的情况下如果开启Query Cache 会 频繁的计算Hash ，会增加性能的消耗，得不偿失，生产环境中建议关闭该选项；

可以使用 语句 show VARIABLES like '%query_cache%' 查看 Query Chach 是否关闭；我们主要关注的是 参数

query_cache_type 是否关闭 ，如果是OFF就是关闭状态，ON是开启状态；而不是 query_cache_size参数，其是缓存分配大小；更多关于 Query Chach 的内容可以参考如下文章

https://blog.csdn.net/dongnan591172113/article/details/52084041

https://www.jianshu.com/p/3ab10180fbd5

23.3 读锁

关于锁的知识希望读者学习过高并发相关知识，对所有的锁分类有个清晰的认识，学习本篇关于锁的概念将不会遇到阻碍；在MYSQL中根据不同的引擎，主要会出现三类锁的情况，即 表锁，读锁 和写锁；读锁很好理解，在MYSQL 中 读锁也是共享锁, 即 多用户状态下同一时间对资源的读取是互不影响，但不能对数据进行修改等操作；

一般情况下我们手动给一条或者某个范围内(一般使用在储存过程)的数据加上读锁；

使用语法示例如下

SELECT 字段 from 表名 [where 条件]  lock in share mode;


23.4 写锁

写锁是排他锁，也称为独占锁；使用的情况一般是写入数据的情况下，一个用户如果获得写锁，其它用户将不能获取写锁或者读锁，直到该用户执行完操作并释放锁；其使用方式为在执行语句后加上for update 语句即可

格式示例

SELECT 字段 from 表名 [where 条件] for update;

23.5 锁粒度

锁粒度是指对资源锁定范围的一个程度，使用不同的锁定策略达到并发性能较优的结果；通常锁粒度使用策略情况分为，行锁，表锁，页锁的情况；

**表锁：**即对整张表进行加锁，其性能开销较小，加锁的速度较快，但缺点也很明显，其锁粒度大，并发低；如果要手动加表锁，语法示例 lock tables 表名，释放锁 unlock tables 表名；

行锁：即对行进行锁定，能够最大支持并发量，故锁粒度最小，但其枷锁速度慢，性能消耗大，会出现死锁；行锁的种类又有 记录锁（主键或者唯一索引都属于记录锁），间隙锁（GAP），记录锁和间隙锁的组合（next -key lock）；间隙锁一般用于查询条件是范围情况下，而非相等条件；

页锁：通常情况下遇不到页锁，其开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间；

Tip: MyISAM和 Memory 引擎 支持表锁，其会自动给SELECT,UPDATE,INSERT,DELETE 自动加表锁；InnoDB 支持表锁和行锁，对于UPDATE, INSERT ,DELETE 语句 InnoDB 会自动给数据加排他锁，SELECT语句不加锁；

还有锁的其它分类也会使用到比如乐观锁（基于版本号实现），注意点是条件必须是主键，读取时将数据版本号读出，更新数据时，版本号加1；将查询的数据进行对比，如果版本号不一致就是过期数据；

查询示例

select id,value,version from 表名 where id = #{id}


更新示例

update 表名
set value=2,version=version+1
where id=#{id} and version=#{version}

悲观锁（如表锁，行锁，读写锁都是悲观锁）；

如果看了知识追寻者写的锁知识还有困惑可以参考如下链接

https:///post/5b82e0196fb9a019f47d1823

23.6 引擎简介

在上面的图例中可以看见MYSQL支持多种引擎，当然远不止图中显示的引擎数量；我们主流使用的引擎就是 InnoDB,其次是 MyISAM，特殊情况下会使用到Memory；引擎的知识是一本书都无法概括的内容，知识追寻者在这边给小伙伴们做个简介，有个大概的了解就好；

InnoDB 是使用最广泛的引擎，也是最重要的引擎，读者有必要了解其储存性能；InnoDB 是 可重复读的事物隔离级别，但其实现了next key lock ，防止的幻读出现；其基于聚簇索引实现；其组要组成结构为内存结构，线程，磁盘文件组；

MyISAM在早期版本是MYSQL的默认引擎，在MYSQL5.1之后不再使用；其不支持事物，不支持行锁，默认表锁，并发量低，；

Menory引擎故名思意，其储存内容都是存放在引擎当中，支持Hash和Btree索引，其数据读取快，但缺点也很明显，服务器如果发生故障重启后就会造成数据丢失；

二十四 锁等待

锁等待的意思非常好理解，就是session （事物会话，开启一个事物代表一个会话）A 对 某行数据获取独占锁（在这边一般就是写锁），然后session B 对相同的行进行获取独占锁就发生了锁等待；简单理解就是 小孩子抢玩具，谁先抢到 谁 先玩，没抢到的玩具的孩子只能 等待 抢到玩具孩子玩腻了再给你，瞬间泪奔有木有，就是这么残酷，当然MYSQL 没 这么残忍 其 还是有一个保留参数 innodb_lock_wait_timeout 指定死锁 的时间，如果超过 死锁等待时间就是报异常；

知识追寻者 做个实验：

session A 执行如下语句，开启事物，更新索引为1 的语句；此时 session A  获取了 id= 1 这条 语句的 写锁权限；

BEGIN;
update  `order` set `year`= '2022' where id = '1';


session B 执行如下 语句 , 跟 上面的语句一样 ，由于 id =1 这条数据的写锁已经被session A 获取，故会发生锁等待的情况；

BEGIN;
update  `order` set `year`= '2022' where id = '1';

知识追寻者这边默认等待了50秒 就报了如下异常

Lock wait timeout exceeded; try restarting transaction


查看 默认锁等待 语句

show  VARIABLES like  'innodb_lock_wait_timeout'

二十五 死锁

25.1 死锁的产生

死锁 就是 两个以上的会话 在 抢占 资源过程中 ，产生相互等待的情况；有点绕是不是，其实很简单 死锁是建立在 锁等待的基础上，session A 获取 id = 1 的写锁 ， session B 获取 id =2 的写锁 ，此时由于索引不同，故不会长生锁等待现象 ；当 session A 尝试 获取 id =2 的 写锁时 ，由于 id = 2  写锁已经被 session B 获取 ，故产生锁等待；当 session B 尝试 获取 id = 1 的写锁时  ，由于id =1  写锁已经被 session A 获取, 此时 产生锁等待；由于 session A 与 session B 同时 都在 锁 等待状态，产生了等待对方释放锁，故会产生死锁；

知识追寻者做个试验

session A 执行语句， 获取 id =1 的 写锁权限；

BEGIN;
update  `order` set `year`= '2022' where id = '1';


session B 执行语句， 获取 id =2 的 写锁权限；

BEGIN;
update `order` set `year`= '2022' where id = '2';

session A 执行语句， 尝试获取 id =2 的 写锁权限，进入锁等待状态

update `order` set `year`= '2022' where id = '2';


session B 执行语句， 尝试获取 id =1 的 写锁权限，进入锁等待状态

update  `order` set `year`= '2022' where id = '1';

当  B 进入 锁等待后就直接报死锁异常

Deadlock found when trying to get lock; try restarting transaction


25.2 查看死锁

可以使用 show engine innodb status 查看死锁

......
*** (1) TRANSACTION: // 事物A
TRANSACTION 253507, ACTIVE 474 sec starting index read
mysql tables in use 1, locked 1 // 已经使用一个锁
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17001, OS thread handle 139824777217792, query id 2191731 ......
root updating
update `order` set `year`= '2022' where id = '2'//执行得语句
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: // 等待锁释放获取锁
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253507 lock_mode X locks rec but not gap waiting
.....

*** (2) TRANSACTION: // 事物 B
TRANSACTION 253508, ACTIVE 425 sec starting index read
mysql tables in use 1, locked 1 // 已经使用一个锁
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17002, OS thread handle 139824778569472, query id 2191735 ......
root updating
update  `order` set `year`= '2022' where id = '1'//执行得语句
*** (2) HOLDS THE LOCK(S): //持有锁
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253508 lock_mode X locks rec but not gap
......

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: // 等待锁释放获取锁
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253508 lock_mode X locks rec but not gap waiting
......

不得不说下字母代表锁得类型如下

• 共享锁（S）
• 排他锁（X）
• 意向共享（IS）
• 意向排他（IX）
• gap lock（GK）， 间隙锁，锁定一个范围，不包括当前记录本身；
• RECORD LOCKS  代表记录锁；

可以看见上面得语句 （1） 代表 事物A ，MYSQL 线程id 17001 ；(2) 代表事物B, MYSQL 线程id 17002 ； 事物 A 与B 都在等待 对方释放锁 ，产生了死锁；

Tip；查看表锁 : show status like 'table%';

如何解决死锁，知识追寻者这边给个思路：

查找到死锁线程，杀死MYSQL死锁的线程(kill命令)；

如果事物未提交，直接回滚事物；

25.3 如何避免死锁

• 在死锁容易产生得表使用表锁不会产生死锁；
• 避免交叉使用相同的锁

程序员GitHub

专注于分享GitHub上Python，Java，Go，前端开发等优质的学习资源，并分享程序员圈的新鲜趣事，热门干货，职场感悟。

42篇原创内容

公众号