一、数据库的基本概念

1.什么是数据库？

• 用于存储和管理数据的仓库
• 英文单词为：DataBase，简称DB

2.数据库的好处？

• 可以持久化存储数据
• 方便存储和管理数据
• 使用了统一的方式操作数据库 – SQL类型

二、SQL语句

1.数据的关系介绍

• 数据库
  • 用于存储和管理数据的仓库
  • 一个库中可以包含多个数据表
• 数据表
  • 数据库最重要的组成部分之一
  • 它由纵向的列和横向的行组成(类似excel表格)
  • 可以指定列名、数据类型、约束等
  • 一个表中可以存储多条数据
• 数据
  • 想要永久化存储的数据
• 数据类型
  • 数值型
    • int、double(3,2)
  • 字符型
    • varchar(长度) – 可变长
    • varchar(8) 字符数据最长是8个字符,如果不到8个字符，按实际字符长度计算
    • char(长度) – 固定长
    • char(8) 字符数据长度必须是8个，如果不到8个，会用空字符补全
  • 日期型
  • date 年月日
  • datetime 年月日时分秒 ，如果不给值，默认是NULL
  • timestamp 年月日时分秒，如果不给值，默认会用当前系统时间作为值

2.SQL介绍

• 什么是SQL

  • Structured Query Language：结构化查询语言
  • 定义了操作所有关系型数据库的规则。每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”。

• SQL通用语法

  • SQL 语句可以单行或多行书写，以分号结尾。
  • 可使用空格和缩进来增强语句的可读性。
  • MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
  • 数据库的注释：
    • 单行注释：-- 注释内容 #注释内容(mysql特有)
    • 多行注释：/* 注释内容 */

3.DDL-操作数据库

• R(Retrieve)：查询

  • 查询所有数据库

  -- 查询所有数据库
  SHOW DATABASES;
  

  • 查询某个数据库的创建语句

  -- 查看mysql数据库的创建格式SHOW CREATE DATABASE mysql;
  SHOW CREATE DATABASE mysql;
  

• C(Create)：创建

  • 创建数据库

  -- 创建db1数据库
  CREATE DATABASE db1;
  -- 创建一个已存在的数据库会报错，错误代码：1007  Can't create database 'db1'; database existsCREATE DATABASE db1;
  

  • 创建数据库(判断，如果不存在则创建)

  -- 创建数据库db2(判断，如果不存在则创建)
  CREATE DATABASE IF NOT EXISTS db2;
  

  • 创建数据库、并指定字符集

  -- 创建数据库db3、并指定字符集utf8
  CREATE DATABASE db3 CHARACTER SET utf8;
  -- 查看db3数据库的字符集
  SHOW CREATE DATABASE db3;
  

• U(Update)：修改

  • 修改数据库的字符集

  -- 修改数据库db4的字符集为utf8
  ALTER DATABASE db4 CHARACTER SET utf8;
  

• D(Delete)：删除

  • 删除数据库

  -- 删除db1数据库
  DROP DATABASE db1;
  -- 删除一个不存在的数据库会报错，错误代码：1008  Can't drop database 'db1'; database doesn't existDROP DATABASE db1;
  

  • 删除数据库(判断，如果存在则删除)

  -- 删除数据库db2，如果存在则删除
  zDROP DATABASE IF EXISTS db2;
  

4.DDL-操作数据表

• R(Retrieve)：查询

  • 查询数据库中所有的数据表

  -- 查询库中所有的表
  SHOW TABLES;
  

  • 查询表结构

  -- 查询user表结构
  DESC user;
  

• C(Create)：创建

  • 创建数据表

    • 标准语法

    CREATE TABLE 表名(    列名1 数据类型1,    列名2 数据类型2,    ....    列名n 数据类型n);-- 注意：最后一列，不需要加逗号
    

    • 常用数据类型
    1. int：整数类型
    2. double:小数类型
    3. date:日期，只包含年月日 yyyy-MM-dd
    4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
    5. timestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss * 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
    6. varchar：字符串（可变长度
    7. char：字符型（定长）， 对于定长的数据操作，没有校验的逻辑，相对操作的效率要高一些（空间换时间）
    • 创建数据表

    -- 使用db3数据库
    USE db3;
    -- 创建一个product商品表
    CREATE TABLE product(	id INT,				-- 商品编号
                         NAME VARCHAR(30),	-- 商品名称	
                         price DOUBLE,		-- 商品价格	
                         stock INT,			-- 商品库存	
                         insert_time DATE    -- 上架时间
                        );
    

    • 复制表

    -- 复制product表到product2表
    CREATE TABLE product2 LIKE product;
    

• U(Update)：修改

  • 修改表名

  -- 修改product2表名为product3
  ALTER TABLE product2 RENAME TO product3;
  

  • 添加一列

  -- 给product3表添加一列color
  ALTER TABLE product3 ADD color VARCHAR(10);
  

  • 修改列名称和数据类型

  -- 将color数据类型修改为int
  ALTER TABLE product3 MODIFY color INT;
  -- 将color修改为address,数据类型为varchar
  ALTER TABLE product3 CHANGE color address VARCHAR(30);
  

  • 删除列

  -- 删除address列
  ALTER TABLE product3 DROP address;
  

• D(Delete)：删除

  • 删除数据表

  -- 删除product3表
  DROP TABLE product3;
  -- 删除不存在的表，会报错-- 错误代码：1051  Unknown table 'product3'DROP TABLE product3;
  

  • 删除数据表(判断，如果存在则删除)

  -- 删除product3表，如果存在则删除
  DROP TABLE IF EXISTS product3;
  

5.DML-INSERT语句

• 新增表数据语法

  • 新增格式1：给指定列添加数据

  -- 向product表添加一条数据
  INSERT INTO product(id,NAME,price,stock,insert_time) VALUES (1,'手机',1999,22,'2099-09-09');
  -- 向product表添加指定列数据
  INSERT INTO product (id,NAME,price) VALUES (2,'电脑',4999);
  

  • 新增格式2：默认给全部列添加数据

  -- 默认给全部列添加数据
  INSERT INTO product VALUES (3,'电视',2999,18,'2099-06-06');
  

  • 新增格式3：批量添加数据

  -- 批量添加数据
  INSERT INTO product VALUES (4,'冰箱',999,26,'2099-08-08'),(5,'洗衣机',1999,32,'2099-05-10');
  -- 批量添加指定列数据
  INSERT INTO product (id,NAME,price) VALUES (6,'微波炉',499),(7,'电磁炉',899);
  

• 注意事项

  • 列名和值的数量以及数据类型要对应
  • 除了数字类型，其他数据类型的数据都需要加引号(单引双引都可以，推荐单引)

6.DML-UPDATE语句

• 修改表数据语法

-- 修改手机的价格为3500
UPDATE product SET price=3500 WHERE NAME='手机';
-- 修改电视的价格为1800、库存为36
UPDATE product SET price=1800,stock=36 WHERE NAME='电视';
-- 修改电磁炉的库存为10
UPDATE product SET stock=10 WHERE id=7;

• 注意事项
  • 修改语句中必须加条件
  • 如果不加条件，则将所有数据都修改

7.DML-DELETE语句

• 删除表数据语法

-- 删除product表中的微波炉信息
DELETE FROM product WHERE NAME='微波炉';
-- 删除product表中库存为10的商品信息
DELETE FROM product WHERE stock=10;

• 注意事项
  • 删除语句中必须加条件
  • 如果不加条件，则将所有数据删除

8.DQL-单表查询

• 查询语法

select	字段列表from	表名列表where	条件列表group by	分组字段having	分组之后的条件order by	排序limit	分页限定

• 查询全部

-- 查询product表所有数据
SELECT * FROM product;

• 查询部分

  • 多个字段查询

  -- 查询名称、价格、品牌
  SELECT NAME,price,brand FROM product;
  

  • 去除重复查询
    • 注意：只有全部重复的才可以去除

  -- 查询品牌，去除重复
  SELECT DISTINCT brand FROM product;
  

  • 计算列的值(四则运算)

  -- 如果某一列为null，可以进行替换	ifnull(表达式1,表达式2)	表达式1：想替换的列	表达式2：想替换的值
  -- 查询商品名称和库存，库存数量在原有基础上加10
  SELECT NAME,stock+10 FROM product;
  -- 查询商品名称和库存，库存数量在原有基础上加10。进行null值判断
  SELECT NAME,IFNULL(stock,0)+10 FROM product;
  

  • 起别名

  -- 查询商品名称和库存，库存数量在原有基础上加10。进行null值判断。起别名为getSum
  SELECT NAME,IFNULL(stock,0)+10 AS getsum FROM product;SELECT NAME,IFNULL(stock,0)+10 getsum FROM product;
  

• 条件查询

  • 条件分类

  符号	功能
  >	大于
  <	小于
  >=	大于等于
  <=	小于等于
  =	等于
  <> 或 !=	不等于
  BETWEEN … AND …	在某个范围之内(都包含)
  IN(…)	多选一
  LIKE 占位符	模糊查询 _单个任意字符 %多个任意字符
  IS NULL	是NULL
  IS NOT NULL	不是NULL
  AND 或 &&	并且
  OR 或 ||	或者
  NOT 或 !	非，不是

• 聚合函数

  • 将一列数据作为一个整体，进行纵向的计算
  • 聚合函数分类

  函数名	功能
  count(列名)	统计数量(一般选用不为null的列)
  max(列名)	最大值
  min(列名)	最小值
  sum(列名)	求和
  avg(列名)	平均值

  • 聚合函数语法

  -- 计算product表中总记录条数
  SELECT COUNT(*) FROM product;
  -- 获取最高价格
  SELECT MAX(price) FROM product;
  -- 获取最高价格的商品名称
  SELECT NAME,price FROM product WHERE price = (SELECT MAX(price) FROM product);
  -- 获取最低库存
  SELECT MIN(stock) FROM product;
  -- 获取最低库存的商品名称
  SELECT NAME,stock FROM product WHERE stock = (SELECT MIN(stock) FROM product);
  -- 获取总库存数量
  SELECT SUM(stock) FROM product;
  -- 获取品牌为苹果的总库存数量
  SELECT SUM(stock) FROM product WHERE brand='苹果';
  -- 获取品牌为小米的平均商品价格
  SELECT AVG(price) FROM product WHERE brand='小米';
  

• 排序查询

  • 排序分类
    • 注意：多个排序条件，当前边的条件值一样时，才会判断第二条件

  关键词	功能
  ORDER BY 列名1 排序方式1,列名2 排序方式2	对指定列排序，ASC升序(默认的) DESC降序

  • 排序语法

  -- 按照库存升序排序
  SELECT * FROM product ORDER BY stock ASC;
  -- 查询名称中包含手机的商品信息。按照金额降序排序
  SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;
  -- 按照金额升序排序，如果金额相同，按照库存降序排列
  SELECT * FROM product ORDER BY price ASC,stock DESC;
  

• 分组查询

-- 按照品牌分组，获取每组商品的总金额
SELECT brand,SUM(price) FROM product GROUP BY brand;
-- 对金额大于4000元的商品，按照品牌分组,获取每组商品的总金额
SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;

• 分页查询

-- LIMIT 开始索引,查询条数;-- 公式：开始索引 = (当前页码-1) * 每页显示的条数,每页显示2条数据
SELECT * FROM product LIMIT 0,2;  -- 第一页 开始索引=(1-1) * 2
SELECT * FROM product LIMIT 2,2;  -- 第二页 开始索引=(2-1) * 2
SELECT * FROM product LIMIT 4,2;  -- 第三页 开始索引=(3-1) * 2
SELECT * FROM product LIMIT 6,2;  -- 第四页 开始索引=(4-1) * 2

9.执行顺序

• 编写顺序：select … from …where …group by …having … order by … limit
• 执行顺序：from … where …group by … having … select … order by … limit

三、约束

1.约束的概念和分类

• 约束的概念
  • 对表中的数据进行限定，保证数据的正确性、有效性、完整性！
• 约束的分类

2.主键约束

• 主键约束特点
  • 主键约束包含：非空和唯一两个功能
  • 一张表只能有一个列作为主键
  • 主键一般用于表中数据的唯一标识
• 建表时添加主键约束

-- 标准语法CREATE TABLE 表名(	列名 数据类型 PRIMARY KEY,    列名 数据类型,    ...);

• 删除主键

-- 删除主键
ALTER TABLE student DROP PRIMARY KEY;

• 建表后单独添加主键

-- 添加主键
ALTER TABLE student MODIFY id INT PRIMARY KEY;

3.主键自动增长约束

• 建表时添加主键自增约束

-- 标准语法CREATE TABLE 表名(	列名 数据类型 PRIMARY KEY AUTO_INCREMENT,    列名 数据类型,    ...);

• 删除自动增长

-- 标准语法ALTER TABLE 表名 MODIFY 列名 数据类型;
-- 删除自动增长
ALTER TABLE student2 MODIFY id INT;

• 建表后单独添加自动增长

-- 标准语法ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
-- 添加自动增长
ALTER TABLE student2 MODIFY id INT AUTO_INCREMENT;

4.唯一约束

• 建表时添加唯一约束

-- 标准语法CREATE TABLE 表名(	列名 数据类型 UNIQUE,    列名 数据类型,    ...);-

• 删除唯一约束

-- 标准语法ALTER TABLE 表名 DROP INDEX 列名;-- 删除唯一约束
ALTER TABLE student3 DROP INDEX tel;

• 建表后单独添加唯一约束

-- 标准语法ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;-- 添加唯一约束
ALTER TABLE student3 MODIFY tel VARCHAR(20) UNIQUE;

5.非空约束

• 建表时添加非空约束

-- 标准语法CREATE TABLE 表名(	列名 数据类型 NOT NULL,    列名 数据类型,    ...);

• 删除非空约束

-- 标准语法ALTER TABLE 表名 MODIFY 列名 数据类型;-- 删除非空约束
ALTER TABLE student4 MODIFY NAME VARCHAR(20);

• 建表后单独添加非空约束

  -- 标准语法ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;-- 添加非空约束
  ALTER TABLE student4 MODIFY NAME VARCHAR(20) NOT NULL;
  

6.外键约束

• 外键约束概念

  • 让表和表之间产生关系，一张表的变得将关联另外一张表，从而保证数据的准确性！

  • 外键约束格式

  CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
  

• 删除外键约束

  -- 删除外键
  ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
  

• 建表后添加外键约束

  -- 标准语法
  ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
  
  -- 添加外键约束
  ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);
  

四、多表设计

1.一对一

• 分析
  • 人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！
• 实现原则
  • 在任意一个表建立外键，去关联另外一个表的主键

2.一对多

• 分析
  • 用户和订单。一个用户可以有多个订单！一个订单只能由一个用户
  • 商品分类和商品。一个分类下可以有多个商品！一个商品只能有一个分类
• 实现原则
  • 在多的一方，建立外键约束，来关联一的一方主键

3.多对多

• 分析

  • 学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！

• 实现原则

  • 需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键

• 图解
  

五、多表查询

1.多表查询-笛卡尔积查询

• 有两张表，获取这两个表的所有组合情况

• 要完成多表查询，需要消除这些没有用的数据

• 多表查询格式

• 笛卡尔积查询

-- 标准语法SELECT 列名 FROM 表名1,表名2,...;
-- 查询user表和orderlist表
SELECT * FROM USER,orderlist;

2.多表查询-内连接查询

• 查询原理
  • 内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
• 显式内连接

-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid;
SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid;

• 隐式内连接

-- 查询用户姓名，年龄。和订单编号
SELECT	u.`name`,-- 姓名	
u.`age`,	-- 年龄	
o.`number`	-- 订单编号
FROM	USER u,		-- 用户表	
orderlist o     -- 订单表
WHERE	u.`id`=o.`uid`;

3.多表查询-外连接查询

• 左外连接

  • 查询原理
    • 查询左表的全部数据，和左右两张表有交集部分的数据
  • 基本演示

  -- 查询所有用户信息，以及用户对应的订单信息
  SELECT	u.`name`,	-- 姓名	
  u.`age`,	-- 年龄	
  o.`number`	-- 订单编号
  FROM USER u   -- 用户表
  LEFT OUTER JOIN	orderlist o     -- 订单表
  ON	u.`id`=o.`uid`;
  

• 右外连接

  • 查询原理
    • 查询右表的全部数据，和左右两张表有交集部分的数据
  • 基本演示

  -- 查询所有订单信息，以及订单所属的用户信息
  SELECT	u.`name`,	-- 姓名	
  u.`age`,	-- 年龄	
  o.`number`	-- 订单编号
  FROM	USER u  -- 用户表
  RIGHT OUTER JOIN	orderlist o     -- 订单表
  ON	u.`id`=o.`uid`;
  

4.多表查询-子查询

• 子查询介绍

  • 查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！

• 子查询-结果是单行单列的

  • 可以作为条件，使用运算符进行判断！
  • 基本演示

  -- 查询年龄最高的用户姓名
  SELECT MAX(age) FROM USER;
  -- 查询出最高年龄
  SELECT NAME,age FROM USER WHERE age=26; 
  -- 根据查询出来的最高年龄，查询姓名和年龄
  SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);
  

• 子查询-结果是多行单列的

  • 可以作为条件，使用运算符in或not in进行判断！
  • 基本演示

   -- 查询张三和李四的订单信息
   SELECT id FROM USER WHERE NAME='张三' OR NAME='李四';   
   -- 查询张三和李四用户的id
   SELECT number,uid FROM orderlist WHERE uid=1 OR uid=2;
   -- 根据id查询订单
   SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME='张三' OR NAME='李四');
  

• 子查询-结果是多行多列的

  • 可以作为一张虚拟表参与查询！
  • 基本演示

  -- 查询订单表中id大于4的订单信息和所属用户信息
  SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;
  

六、视图

1.视图的概念

• 视图是一种虚拟存在的数据表
• 这个虚拟的表并不在数据库中实际存在
• 作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
• 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上

2.视图的好处

• 简单
  • 对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
• 安全
  • 视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
• 数据独立
  • 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

3.视图的创建

• 创建视图语法

-- 标准语法CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;

• 普通多表查询，查询城市和所属国家

-- 普通多表查询，查询城市和所属国家
SELECT	t1.*,	t2.country_nameFROM	city t1,	country t2WHERE	t1.cid = t2.id;	
-- 经常需要查询这样的数据，就可以创建一个视图

• 创建视图基本演示

-- 创建一个视图。将查询出来的结果保存到这张虚拟表中
CREATEVIEW	city_countryAS	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

• 创建视图并指定列名基本演示

-- 创建一个视图，指定列名。将查询出来的结果保存到这张虚拟表中
CREATEVIEW	city_country2 (city_id,city_name,cid,country_name) AS SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

4.视图的查询

• 查询视图语法

-- 标准语法SELECT * FROM 视图名称;

• 查询视图基本演示

-- 查询视图。查询这张虚拟表，就等效于查询城市和所属国家
SELECT * FROM city_country;
-- 查询指定列名的视图
SELECT * FROM city_country2;-- 查询所有数据表，视图也会查询出来SHOW TABLES;

• 查询视图创建语法

-- 标准语法SHOW CREATE VIEW 视图名称;

• 查询视图创建语句基本演示

SHOW CREATE VIEW city_country;

5.视图的修改

• 修改视图表中的数据

-- 修改视图表中的城市名称北京为北京市
UPDATE city_country SET city_name='北京市' WHERE city_name='北京';
-- 查询视图
SELECT * FROM city_country;
-- 查询city表,北京也修改为了北京市
SELECT * FROM city;
-- 注意：视图表数据修改，会自动修改源表中的数据

• 修改视图表结构

-- 查询视图2
SELECT * FROM city_country2;
-- 修改视图2的列名city_id为id
ALTERVIEW city_country2 (id,city_name,cid,country_name)AS	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

6.视图的删除

• 删除视图

-- 删除视图
DROP VIEW city_country;
-- 删除视图2，如果存在则删除
DROP VIEW IF EXISTS city_country2;

7.视图的总结

• 视图是一种虚拟存在的数据表
• 这个虚拟的表并不在数据库中实际存在
• 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上
• 视图的好处
  • 简单
  • 安全
  • 数据独立

七、MySQL事务

1.事务的概念

• 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，单元中的每条 SQL 语句都相互依赖，形成一个整体，如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态，如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。

2.未管理事务演示

-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';
-- 该场景下，这两条sql语句要么同时成功，要么同时失败。就需要被事务所管理！

3.管理事务演示

• 操作事务的三个步骤
  1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
  2. 执行sql语句：执行具体的一条或多条sql语句
  3. 结束事务(提交|回滚)
     • 提交：没出现问题，数据进行更新
     • 回滚：出现问题，数据恢复到开启事务时的状态
• 开启事务

-- 标准语法
START TRANSACTION;

• 回滚事务

-- 标准语法
ROLLBACK;

• 提交事务

-- 标准语法
COMMIT;

• 管理事务演示

-- 开启事务
START TRANSACTION;
-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500-- 出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';
-- 回滚事务(出现问题)
ROLLBACK;
-- 提交事务(没出现问题)
COMMIT;

4.事务的提交方式

• 提交方式

  • 自动提交(MySQL默认为自动提交)
  • 手动提交

• 修改提交方式

  • 查看提交方式

  -- 标准语法
  SELECT @@AUTOCOMMIT;  -- 1代表自动提交    0代表手动提交
  

  • 修改提交方式

  -- 标准语法
  SET @@AUTOCOMMIT=数字;
  -- 修改为手动提交
  SET @@AUTOCOMMIT=0;
  -- 查看提交方式
  SELECT @@AUTOCOMMIT;
  

5.事务的四大特征(ACID)

• 原子性(atomicity)
  • 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
• 一致性(consistency)
  • 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
  • 拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性
• 隔离性(isolcation)
  • 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
• 持久性(durability)
  • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

7.事务的隔离级别

• 隔离级别的概念
  • 多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。
  • 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别 , 否则就会产生问题 。
  • 我们先来了解一下四种隔离级别的名称 , 再来看看可能出现的问题
• 四种隔离级别

• 可能引发的问题

• 查询数据库隔离级别

-- 标准语法
SELECT @@TX_ISOLATION;

8.隔离级别总结

注意：隔离级别从小到大安全性越来越高，但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.

9.事务的总结

• 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。例如转账操作
• 开启事务：start transaction;
• 回滚事务：rollback;
• 提交事务：commit;
• 事务四大特征
  • 原子性
  • 持久性
  • 隔离性
  • 一致性
• 事务的隔离级别
  • read uncommitted(读未提交)
  • read committed (读已提交)
  • repeatable read (可重复读)
  • serializable (串行化)

八、MySQL存储引擎

1.MySQL体系结构

• 体系结构的概念

  • 任何一套系统当中，每个部件都能起到一定的作用！

• MySQL的体系结构
  

• 体系结构详解

  • 客户端连接
    • 支持接口：支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库
  • 第一层：网络连接层
    • 连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。
    • 例如：当客户端发送一个请求连接，会从连接池中获取一个连接进行使用。
  • 第二层：核心服务层
    • 管理服务和工具：系统的管理和控制工具，例如备份恢复、复制、集群等。
    • SQL接口：接受SQL命令，并且返回查询结果。
    • 查询解析器：验证和解析SQL命令，例如过滤条件、语法结构等。
    • 查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句
    • 缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询！
  • 第三层：存储引擎层
    • 插件式存储引擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)
  • 第四层：系统文件层
    • 文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存

2.MySQL存储引擎

• 引擎的概念

  • 生活中，引擎就是整个机器运行的核心，不同的引擎具备不同的功能。

• MySQL存储引擎的概念

  • MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力，在MySQL中 , 将这些不同的技术及配套的功能称为存储引擎
  • 在关系型数据库中数据的存储是以表的形式存进行储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。
  • Oracle , SqlServer等数据库只有一种存储引擎 , 而MySQL针对不同的需求, 配置MySQL的不同的存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能。
  • 通过选择不同的引擎 ,能够获取最佳的方案 , 也能够获得额外的速度或者功能，提高程序的整体效果。所以了解引擎的特性 , 才能贴合我们的需求 , 更好的发挥数据库的性能。

• MySQL支持的存储引擎

  • MySQL5.7支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等
  • 其中较为常用的有三种：InnoDB、MyISAM、MEMORY

3.常用引擎的特性对比

• 常用的存储引擎
  • MyISAM存储引擎
    • 访问快,不支持事务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。
  • InnoDB存储引擎(MySQL5.5版本后默认的存储引擎)
    • 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd 中。
  • MEMORY存储引擎
    • 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。
• 特性对比

4.总结：引擎的选择

• MyISAM ：由于MyISAM不支持事务、不支持外键、支持全文检索和表级锁定，读写相互阻塞，读取速度快，节约资源，所以如果应用是以查询操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• InnoDB : 是MySQL的默认存储引擎， 由于InnoDB支持事务、支持外键、行级锁定 ，支持所有辅助索引(5.5.5后不支持全文检索)，高缓存，所以用于对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作，那么InnoDB存储引擎是比较合适的选择，比如BBS、计费系统、充值转账等
• MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
• 总结：纯查询的表选择用MyISAM, 否则一律采用默认的InnoDB

九、MySQL索引

1.索引的概念

• MySQL数据库中的索引：是帮助MySQL高效获取数据的一种数据结构
• 在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
• 一张数据表，用于保存数据。一个索引配置文件，用于保存索引，每个索引都去指向了某一个数据

2.索引的分类

• 功能分类
  • 普通索引： 最基本的索引，它没有任何限制。
  • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
  • 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
  • 组合索引：顾名思义，就是将单列索引进行组合。
  • 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
  • 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
• 结构分类
  • B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
  • Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。

3.索引的实现原则

• 索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。InnoDB引擎默认使用的是B+Tree索引。
• B+Tree是一种树型数据结构，是B-Tree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。我们逐步的来了解一下。

3.1 磁盘存储

• 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
• 位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
• InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
• InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

3.2 BTree

• BTree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述BTree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的BTree：

• 根据图中结构显示，每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

查找顺序：

模拟查找15的过程 : 1.根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】	比较关键字15在区间（<17），找到磁盘块1的指针P1。2.P1指针找到磁盘块2，读入内存。【磁盘I/O操作第2次】	比较关键字15在区间（>12），找到磁盘块2的指针P3。3.P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】	在磁盘块7中找到关键字15。	-- 分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。-- 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree使用较少的节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

3.3 B+Tree

• B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
• 从上一节中的BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
• B+Tree相对于BTree区别：
  • 非叶子节点只存储键值信息。
  • 所有叶子节点之间都有一个连接指针。
  • 数据记录都存放在叶子节点中。
• 将上一节中的BTree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：

• 【有范围】对于主键的范围查找和分页查找
• 【有顺序】从根节点开始，进行随机查找

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

3.4 为什么默认使用B+Tree,而不是BTree结构?

• B+Tree的每一个节点(页)包含更多的行记录信息,整个树结构相对于BTree而言,树的层级会更加浅
• 我们在查找数据时,需要读取的节点从次数(IO次数)就会更少,效率越高；另外当进行范围查询时，可以直接利用叶子节点之间的双向链表实现快速查询。

4. 总结：索引的设计原则

• 创建索引时的原则(只是推荐原则并非是规范，并且要根据后期的查询需求，动态变化，做取舍)

  • 对查询频次较高，且数据量比较大的表建立索引。

  • 使用唯一索引，区分度越高，使用索引的效率越高。

  • 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

  • 根据查询条件，基于最左匹配原则，构建索引；

  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。但是实际我们必须要根据某一列的值构建索引，而这一列的值有很大时，我们会通过取前缀的方式，构建‘前缀索引’，

    • 计算前缀大小的方式(值最少大于0.9)：例

      SELECT COUNT(DISTINCT SUBSTRING(NAME,1,8))/COUNT(1) FROM product;

    • 一张表的索引不宜过多，不超过5个；索引是一个存储数据的树，如果一张表的索引过多，回到这磁盘控件占用过大；索引上的数据是有序的，如果涉及增删改操作，索引树上的数据要重新维护，如果索引过多，会带来增删改性能的影响

  • 尽可能减少回表操作,可以将要查询的内容也做到索引树上 – ‘覆盖索引’（索引列不仅来自于查询条件，还可能来自于查询的内容）

• 联合索引的特点

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，
对列name列、address和列phone列建一个联合索引

ALTER TABLE user ADD INDEX index_three(name,address,phone);

联合索引index_three实际建立了(name)、(name,address)、(name,address,phone)三个索引。所以下面的三个SQL语句都可以命中索引。

SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
SELECT * FROM user WHERE name = '张三' AND address = '北京';
SELECT * FROM user WHERE name = '张三';

上面三个查询语句执行时会依照最左前缀匹配原则，检索时分别会使用索引

(name,address,phone)(name,address)(name)

进行数据匹配。

索引的字段可以是任意顺序的，如：

-- 优化器会帮助我们调整顺序，下面的SQL语句都可以命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';

Mysql的优化器会帮助我们调整where条件中的顺序，以匹配我们建立的索引。

联合索引中最左边的列不包含在条件查询中，所以根据上面的原则，下面的SQL语句就不会命中索引。

-- 联合索引中最左边的列不包含在条件查询中，下面的SQL语句就不会命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345';

十、锁

1.锁的概念

• 之前我们学习过多线程，多线程当中如果想保证数据的准确性是如何实现的呢？没错，通过同步实现。同步就相当于是加锁。加了锁以后有什么好处呢？当一个线程真正在操作数据的时候，其他线程只能等待。当一个线程执行完毕后，释放锁。其他线程才能进行操作！

• 那么我们的MySQL数据库中的锁的功能也是类似的。在我们学习事务的时候，讲解过事务的隔离性，可能会出现脏读、不可重复读、幻读的问题，当时我们的解决方式是通过修改事务的隔离级别来控制，但是数据库的隔离级别呢我们并不推荐修改。所以，锁的作用也可以解决掉之前的问题！

• 锁机制 : 数据库为了保证数据的一致性，而使用各种共享的资源在被并发访问时变得有序所设计的一种规则。

• 举例，在电商网站购买商品时，商品表中只存有1个商品，而此时又有两个人同时购买，那么谁能买到就是一个关键的问题。

  这里会用到事务进行一系列的操作：

  1. 先从商品表中取出物品的数据
  2. 然后插入订单
  3. 付款后，再插入付款表信息
  4. 更新商品表中商品的数量

  以上过程中，使用锁可以对商品数量数据信息进行保护，实现隔离，即只允许第一位用户完成整套购买流程，而其他用户只能等待，这样就解决了并发中的矛盾问题。

• 在数据库中，数据是一种供许多用户共享访问的资源，如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，MySQL由于自身架构的特点，在不同的存储引擎中，都设计了面对特定场景的锁定机制，所以引擎的差别，导致锁机制也是有很大差别的。

2.锁的分类

• 按操作分类：
  • 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据记录。
  • 排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入
• 按粒度分类：
  • 表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！
  • 行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！
  • 页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。
• 按使用方式分类：
  • 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
  • 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据
• 不同存储引擎支持的锁

3.演示悲观锁和乐观锁

• 悲观锁的概念

  • 就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。
  • 整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
  • 我们之前所学的行锁，表锁不论是读写锁都是悲观锁。

• 乐观锁的概念

  • 就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
  • 但是在更新的时候会去判断在此期间数据有没有被修改。
  • 需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

• 悲观锁和乐观锁使用前提

  • 对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。
  • 如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁。

• 乐观锁的实现方式

  • 版本号

    • 给数据表中添加一个version列，每次更新后都将这个列的值加1。
    • 读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。
    • 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
    • 用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。

  • 时间戳

    • 和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp
    • 每次更新后都将最新时间插入到此列。
    • 读取数据时，将时间读取出来，在执行更新的时候，比较时间。
    • 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

4.锁的总结

• 表锁和行锁

  • 行锁：锁的粒度更细，加行锁的性能损耗较大。并发处理能力较高。InnoDB引擎默认支持！
  • 表锁：锁的粒度较粗，加表锁的性能损耗较小。并发处理能力较低。InnoDB、MyISAM引擎支持！

• InnoDB锁优化建议

  • 尽量通过带索引的列来完成数据查询，从而避免InnoDB无法加行锁而升级为表锁。

  • 合理设计索引，索引要尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定。

  • 尽可能减少基于范围的数据检索过滤条件。

  • 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。

  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。

  • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁的产生。

一、集群

1.集群的概念

• 如今随着互联网的发展，数据的量级也是成指数的增长，从GB到TB到PB。对数据的各种操作也是愈加的困难，传统的关系型数据库已经无法满足快速查询与插入数据的需求。一台数据库服务器已经无法满足海量数据的存储需求，所以由多台数据库构成的数据库集群成了必然的方式。不过，为了保证数据的一致性，查询效率等，同时又要解决多台服务器间的通信、负载均衡等问题。
• MyCat是一款数据库集群软件，是阿里曾经开源的知名产品——Cobar，简单的说，MyCAT就是：一个新颖的数据库中间件产品支持MySQL集群，提供高可用性数据分片集群。你可以像使用mysql一样使用mycat。对于开发人员来说根本感觉不到mycat的存在。MyCat不单单是支持MySQL，像常用的关系型数据库Oracle、SqlServer都支持。

2.集群的原理

• 我们来说个例子，大海捞针和一个水瓶里捞针，毋庸置疑水瓶里一定能更快找到针，因为它需要检索的范围更小。数据库集群也是如此原理，我们可以将一个数据量为300G的数据库数据平均拆分成3部分，每个数据库中只存储100G数据，此时用户搜索，先经过我们中间代理层，中间代理层同时发出3个请求执行查询，比如第1台返回100条数据，耗时3秒，第2台返回200条数据，耗时3秒，第3台返回500条数据，耗时3秒，此时中间件只需要在800条记录中进行筛选，即可检索出用户要的结果，此时耗时其实一共只有3秒，因为每台机器做运算的时候，都是同时执行。如果我们此时直接在300G的数据库查询，耗时10秒，那使用中间件进行集群的效率就非常明显

• MyCat的实现流程和这个流程大致相似。MyCat自身不存储数据，但用户每次链接数据库的时候，直接连接MyCat即可.所以我们MyCat自身其实就是个逻辑数据库，它自身还有表结构，表结构叫逻辑表。

3.主从复制

• 主从复制的概念

  • 为了使用Mycat进行读写分离，我们先要配置MySQL数据库的主从复制。
  • 从服务器自动同步主服务器的数据，从而达到数据一致。
  • 进而，我们可以写操作时，只操作主服务器，而读操作，就可以操作从服务器了。
  • 原理：主服务器在处理数据时，生成binlog日志，通过对日志的备份，实现从服务器的数据同步。

4.读写分离

• 读写分离的概念
  • 写操作只写入主服务器，读操作读取从服务器。

5.分库分表

• 分库分表的概念

  • 将庞大的数据进行拆分
  • 水平拆分：根据表的数据逻辑关系，将同一表中的数据按照某种条件，拆分到多台数据库服务器上，也叫做横向拆分。例如：一张1000万的大表，按照一模一样的结构，拆分成4个250万的小表，分别保存到4个数据库中。
  • 垂直拆分：根据业务的维度，将不同的表切分到不同的数据库之上，也叫做纵向拆分。例如：所有的订单都保存到订单库中，所有的用户都保存到用户库中，同类型的表保存在同一库，不同的表分散在不同的库中。