Java-JavaWeb—（4）MySQL数据库

1.数据库的基本概念

1.1IO流保存数据

接下来我有这样一个需求：将下面的user.txt文件中的王五年龄修改为35

张三 23 男
李四 24 男
王五 25 女
赵六 26 女
周七 27 男

我们要如何实现呢？

• 可以采用字符缓冲流，将每一行数据读取出来，封装为User对象。将多个User对象保存到集合中

• 然后遍历集合，将王五对象的年龄修改为35,再重新将集合中的对象信息写回到文件中

1.2数据库

用来存储和管理数据的仓库，DataBase，简称为DB

1.3数据库的优点

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

2.MySQL数据库

2.1介绍

MySQl是一个最流行的关系型数据库管理系统之一。关系型数据库是将数据保存在不同的数据表中，而不是将所有数据放在一个大仓库内， 而且表与表之间还可以有关联关系。这样就提高了访问速度以及提高了灵活性。

2.2安装

MySQL 8.0.19安装教程(windows 64位)_日日言谧-CSDN博客_mysql8安装

3.SQL语句

3.1数据库、数据表、数据的关系

数据库：用于存储和管理数据的仓库，一个库中可以包含多个数据表

数据表：数据库最重要的组成部分之一，它由纵向的列和横向的行组成，可以指定列名、数据类型、约束等，一个表中可以存储多条数据

数据：想要永久存放的数据

3.2SQL

SQL:结构化查询语言。其实就是定义了操作所有关系型数据库的一种规则。

SQL通用语法：SQL 语句可以单行或多行书写，以分号结尾。 可使用空格和缩进来增强语句的可读性。 MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。

SQL注释：单行：--注释内容  #注释内容（SQL独有）  多行：/*注释内容*/

SQL分类：

• DDL(Data Definition Language)数据定义语言

  • 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

• DML(Data Manipulation Language)数据操作语言

  • 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

• DQL(Data Query Language)数据查询语言

  • 用来查询数据库中表的记录(数据)。关键字：select, where 等

• DCL(Data Control Language)数据控制语言(了解)

  • 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

 3.3DDL操作数据库

show databases：查询所有数据库

show database 数据库名称：查询数据库

create database 数据库名称：创建数据库

create database not exists 数据库名称：创建数据库（判断，如果不存在则创建）

create datebase 数据库名称 character set 字符集名称：创建数据库（指定字符集）

 

 

 

 alter database 数据库名称 character set 字符集名称：修改数据库字符集

drop database 数据库名称：删除数据库

drop database if exists 数据库名称：删除数据库（判断  如果存在则删除）

use 数据库名称：使用数据库

select database()：使用当前数据库

 

 

 

 3.4DDL查询数据表

show tables：查询所有数据表

desc 表名：查询表结构

show table status from 库名 like '表名'：查询表字符集

 3.5DDL创建数据表

        CREATE TABLE 表名(
            列名 数据类型 约束,
            列名 数据类型 约束,
            ...
            列名 数据类型 约束
        );

数据类型：

数据类型	字节长度	范围或用法
Bit	1	无符号[0,255]，有符号[-128,127]，天缘博客备注：BIT和BOOL布尔型都占用1字节
TinyInt	1	整数[0,255]
SmallInt	2	无符号[0,65535]，有符号[-32768,32767]
MediumInt	3	无符号[0,2^24-1]，有符号[-2^23,2^23-1]]
Int	4	无符号[0,2^32-1]，有符号[-2^31,2^31-1]
BigInt	8	无符号[0,2^64-1]，有符号[-2^63 ,2^63 -1]
Float(M,D)	4	单精度浮点数。天缘博客提醒这里的D是精度，如果D<=24则为默认的FLOAT，如果D>24则会自动被转换为DOUBLE型。
Double(M,D)	8	双精度浮点。
Decimal(M,D)	M+1或M+2	未打包的浮点数，用法类似于FLOAT和DOUBLE，天缘博客提醒您如果在ASP中使用到Decimal数据类型，直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。
Date	3	以YYYY-MM-DD的格式显示，比如：2009-07-19
Date Time	8	以YYYY-MM-DD HH:MM:SS的格式显示，比如：2009-07-19 11：22：30
TimeStamp	4	以YYYY-MM-DD的格式显示，比如：2009-07-19
Time	3	以HH:MM:SS的格式显示。比如：11：22：30
Year	1	以YYYY的格式显示。比如：2009
Char(M)	M	定长字符串。
VarChar(M)	M	变长字符串，要求M<=255
Binary(M)	M	类似Char的二进制存储，特点是插入定长不足补0
VarBinary(M)	M	类似VarChar的变长二进制存储，特点是定长不补0
Tiny Text	Max:255	大小写不敏感
Text	Max:64K	大小写不敏感
Medium Text	Max:16M	大小写不敏感
Long Text	Max:4G	大小写不敏感
TinyBlob	Max:255	大小写敏感
Blob	Max:64K	大小写敏感
MediumBlob	Max:16M	大小写敏感
LongBlob	Max:4G	大小写敏感
Enum	1或2	最大可达65535个不同的枚举值
Set	可达8	最大可达64个不同的值
Geometry
Point
LineString
Polygon
MultiPoint
MultiLineString
MultiPolygon
GeometryCollection

alter table 表名 rename to 新表名：修改表名

alter table 表名 character set 字符集：修改表的字符集

alter table 表名 add 列名 数据类型：单独添加一列

alter table 表名 modify 型数据类型：修改某列的数据类型

alter table 表名 change 列名 新列名 新数据类型：修改列名和数据类型

drop table 表名：删除数据表

drop table if exists 表名：删除数据表（判断   如果存在则删除）

 

 

 3.6DML新增表数据

insert into 表名(列名1,列名2,....) values (值1,值2,....)：给指定列添加数据
insert into 表名 values (值1,值2,....)：给全部列添加数据
insert into 表名 (列名1,列名2,....) values (值1,值2,....)(值1,值2,....)：批量添加数据
insert into 表名 values (值1,值2,....)(值1,值2,....)：批量添加数据

 使用1、3方法时，要注意列名和值的数量以及数据类型要对应，除了数字类型，其他数据类的数据都需要加引号(单引双引都可以，推荐单引)

update 表名 set 列名1 = 值1,列名2 = 值2,... [where 条件]：修改表数据
--修改语句中必须加条件，如果不加条件，则会将所有数据都修改
delete from 表名 [where 条件]：删除表中的数据
--删除语句中必须加条件，如果不加条件，则会将所有数据都删除

 3.7DQL表数据查询

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

 3.7.1查询全部

select * from 表名：查询全部的表数据

select 列名1,列名2,.... from 表名：查询指定字段的表数据

select distinct 列名1 运算符(+ - * /) 列名2 from 表名：计算列的值(四则运算)

select 列名 as 别名 from 表名：起别名查询

 

 

 

3.7.2条件查询

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

%：表示任意多个字符

_：表示单个字符

 3.7.3聚合函数

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

3.7.4排序查询

ASC：升序（默认）

DESC： 降序

3.7.5分组查询

 分组后的条件：having 条件

3.7.6分页查询 

3.8约束

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

约束	说明
PRIMARY KEY	主键约束
PRIMARY KEY AUTO_INCREMENT	主键、自动增长
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASCADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除

3.8.1主键约束

主键约束特点

• 主键约束包含：非空和唯一两个功能

• 一张表只能有一个列作为主键

• 主键一般用于表中数据的唯一标识

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


-- 删除约束：标准语法
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 建表后：标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

3.8.2主键自动增长约束

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

-- 删除约束：标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型;

-- 建表后：标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;

 3.8.3唯一约束

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


-- 删除约束：标准语法
ALTER TABLE 表名 DROP INDEX 列名;


-- 建表后：标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;

 

 3.8.4非空约束

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


-- 删除约束：标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型;


-- 建表后：标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

 

 3.8.5外键约束

概念：让表和表之间产生关系，从而保证数据的准确性

---建表时：语法格式
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)

---删除约束：标准语法
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

-- 标建表后：准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 创建db2数据库
CREATE DATABASE db2;

-- 使用db2数据库
USE db2;

/*
	外键约束
	标准语法：
		CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
*/
-- 建表时添加外键约束
-- 创建user用户表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	NAME VARCHAR(20) NOT NULL             -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');


-- 创建orderlist订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	number VARCHAR(20) NOT NULL,          -- 订单编号
	uid INT,			      -- 外键列
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'h001',1),(NULL,'h002',1),
(NULL,'hm03',2),(NULL,'h004',2);


-- 添加一个订单，但是没有真实用户。添加失败
INSERT INTO orderlist VALUES (NULL,'hm005',3);

-- 删除李四用户。删除失败
DELETE FROM USER WHERE NAME='李四';




/*
	删除外键约束
	标准语法：
		ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
*/
-- 删除外键约束
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);

3.8.6外键级联更新和删除

什么是级联更新和级联删除

• 当我想把user用户表中的某个用户删掉，我希望该用户所有的订单也随之被删除

• 当我想把user用户表中的某个用户id修改，我希望订单表中该用户所属的订单用户编号也随之修改

-- 添加外键约束，同时添加级联更新  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;

-- 添加外键约束，同时添加级联删除  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;

-- 添加外键约束，同时添加级联更新和级联删除  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;


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

/*
	添加外键约束，同时添加级联更新  标准语法：
	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
	ON UPDATE CASCADE;
	
	添加外键约束，同时添加级联删除  标准语法：
	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
	ON DELETE CASCADE;
	
	添加外键约束，同时添加级联更新和级联删除  标准语法：
	ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
	ON UPDATE CASCADE ON DELETE CASCADE;
*/
-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

-- 添加外键约束，同时添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
ON UPDATE CASCADE ON DELETE CASCADE;


-- 将李四这个用户的id修改为3,订单表中的uid也自动修改
UPDATE USER SET id=3 WHERE id=2;

-- 将李四这个用户删除,订单表中的该用户所属的订单也自动删除
DELETE FROM USER WHERE id=3;

 4.多表操作

4.1一对一

-- 创建person表
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(20)                        -- 姓名
);
-- 添加数据
INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四');

-- 创建card表
CREATE TABLE card(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	number VARCHAR(20) UNIQUE NOT NULL,	-- 身份证号
	pid INT UNIQUE,                         -- 外键列
	CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id)
);
-- 添加数据
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

4.2一对多

-- 创建user表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(20)                        -- 姓名
);
-- 添加数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');

-- 创建orderlist表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	number VARCHAR(20),                     -- 订单编号
	uid INT,				-- 外键列
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2);



/*
	商品分类和商品
*/
-- 创建category表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(10)                        -- 分类名称
);
-- 添加数据
INSERT INTO category VALUES (NULL,'手机数码'),(NULL,'电脑办公');

-- 创建product表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(30),			-- 商品名称
	cid INT,				-- 外键列
	CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (NULL,'华为P30',1),(NULL,'小米note3',1),
(NULL,'联想电脑',2),(NULL,'苹果电脑',2);

4.3多对对

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(20)			-- 学生姓名
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');

-- 创建course表
CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(10)			-- 课程名称
);
-- 添加数据
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');


-- 创建中间表
CREATE TABLE stu_course(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	sid INT,  -- 用于和student表中的id进行外键关联
	cid INT,  -- 用于和course表中的id进行外键关联
	CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
	CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 添加外键约束
);
-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);

 4.4多表查询

4.4.1数据准备

-- 创建db4数据库
CREATE DATABASE db4;
-- 使用db4数据库
USE db4;

-- 创建user表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 用户id
	NAME VARCHAR(20),			-- 用户姓名
	age INT                                 -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);


-- 订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 订单id
	number VARCHAR(30),			-- 订单编号
	uid INT,    -- 外键字段
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);


-- 商品分类表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
	NAME VARCHAR(10)                    -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');


-- 商品表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
	NAME VARCHAR(30),                    -- 商品名称
	cid INT, -- 外键字段
	CONSTRAINT cp_fk2 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);


-- 中间表
CREATE TABLE us_pro(
	upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
	uid INT, -- 外键字段。需要和用户表的主键产生关联
	pid INT, -- 外键字段。需要和商品表的主键产生关联
	CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
	CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);

 4.4.2内连接查询

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

-- 显式内连接查询：标准语法
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;

-- 隐式内连接查询：标准语法
SELECT 列名 FROM 表名1,表名2 WHERE 条件;

/*
	显示内连接
	标准语法：
		SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
*/
-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON orderlist.uid = user.id;


-- 查询用户信息和对应的订单信息，起别名
SELECT * FROM USER u INNER JOIN orderlist o ON o.uid=u.id;


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



/*
	隐式内连接
	标准语法：
		SELECT 列名 FROM 表名1,表名2 WHERE 关联条件;
*/
-- 查询用户姓名，年龄。和订单编号
SELECT
	u.name,		-- 用户姓名
	u.age,		-- 用户年龄
	o.number	-- 订单编号
FROM
	USER u,		-- 用户表
	orderlist o     -- 订单表
WHERE
	o.uid=u.id;

 

 4.4.3外连接查询

左外连接

• 查询原理

  • 查询左表的全部数据，和左右两张表有交集部分的数据

-- 标准语法
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

右外连接

• 查询原理

  • 查询右表的全部数据，和左右两张表有交集部分的数据

-- 基本语法
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;

/*
	左外连接
	标准语法：
		SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有用户信息，以及用户对应的订单信息
SELECT
	u.*,
	o.number
FROM
	USER u
LEFT OUTER JOIN
	orderlist o
ON
	o.uid=u.id;
	


/*
	右外连接
	标准语法：
		SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有订单信息，以及订单所属的用户信息
SELECT
	o.*,
	u.name
FROM
	USER u
RIGHT OUTER JOIN
	orderlist o
ON
	o.uid=u.id;

4.4.4子查询

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

子查询-结果是单行单列的，可以作为条件，使用运算符进行判断！

-- 标准语法
SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]);

子查询-结果是多行单列的，可以作为条件，使用运算符in或not in进行判断！

-- 标准语法
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 

子查询-结果是多行多列的，可以作为一张虚拟表参与查询！

-- 标准语法
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];

 4.4.5自关联查询

-- 1.查询用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄  user表      订单编号 orderlist表
	条件：user.id=orderlist.uid
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.id=o.uid;



-- 2.查询所有的用户。用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄  user表    订单编号 orderlist表
	条件：user.id=orderlist.uid
	查询所有的用户，左外连接
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u
LEFT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;



-- 3.查询所有的订单。用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄 user表    订单编号 orderlist表
	条件：user.id=orderlist.uid
	查询所有的订单，右外连接
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u
RIGHT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;

	

-- 4.查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄 user表    订单编号 orderlist表
	条件：user.id=orderlist.uid AND user.age > 23
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.id=o.uid
	AND
	u.age > 23;


-- 5.查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄 user表   订单编号 orderlist表
	条件：user.id=orderlist.uid AND user.name IN ('张三','李四')
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.id=o.uid
	AND
	u.name IN ('张三','李四');


-- 6.查询商品分类的编号、分类名称。分类下的商品名称
/*
分析
	商品分类的编号、分类名称 category表    商品名称 product表
	条件：category.id=product.cid
*/
SELECT
	c.id,
	c.name,
	p.name
FROM
	category c,
	product p
WHERE
	c.id=p.cid;


-- 7.查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
/*
分析
	商品分类的编号、分类名称 category表    商品名称 product表
	条件：category.id=product.cid
	查询所有的商品分类，左外连接
*/
SELECT
	c.id,
	c.name,
	p.name
FROM
	category c
LEFT OUTER JOIN
	product p
ON
	c.id=p.cid;


-- 8.查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
/*
分析
	商品分类的编号、分类名称  category表   商品名称 product表
	条件：category.id=product.cid
	查询所有的商品信息，右外连接
*/
SELECT
	c.id,
	c.name,
	p.name
FROM
	category c
RIGHT OUTER JOIN
	product p
ON
	c.id=p.cid;



-- 9.查询所有的用户和该用户能查看的所有的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析
	用户的编号、姓名、年龄 user表   商品名称 product表    中间表 us_pro
	条件：us_pro.uid=user.id AND us_pro.pid=product.id
*/
SELECT
	u.id,
	u.name,
	u.age,
	p.name
FROM
	USER u,
	product p,
	us_pro up
WHERE
	up.uid=u.id
	AND
	up.pid=p.id;



-- 10.查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析
	用户的编号、姓名、年龄 user表   商品名称 product表   中间表 us_pro
	条件：us_pro.uid=user.id AND us_pro.pid=product.id AND user.name IN ('张三','李四') 
*/
SELECT
	u.id,
	u.name,
	u.age,
	p.name
FROM
	USER u,
	product p,
	us_pro up
WHERE
	up.uid=u.id
	AND
	up.pid=p.id
	AND
	u.name IN ('张三','李四');

5.视图

5.1介绍

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

5.2优点

• 简单

  • 对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集

• 安全

  • 视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集

• 数据独立

  • 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

 5.3视图的创建

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

5.4视图的修改和删除

-- 修改：标准语法
UPDATE 视图名称 SET 列名=值 WHERE 条件;


-- 删除：标准语法
DROP VIEW [IF EXISTS] 视图名称;

注意：视图表数据修改，会自动修改源表中的数据

/*
	修改视图数据
	标准语法
		UPDATE 视图名称 SET 列名=值 WHERE 条件;
	
	修改视图结构
	标准语法
		ALTER VIEW 视图名称 (列名列表) AS 查询语句; 
*/
-- 修改视图数据,将北京修改为深圳。(注意：修改视图数据后，源表中的数据也会随之修改)
SELECT * FROM city_country;
UPDATE city_country SET city_name='深圳' WHERE city_name='北京';


-- 将视图中的country_name修改为name
ALTER VIEW city_country (city_id,city_name,NAME) AS
SELECT
	c1.id,
	c1.name,
	c2.name
FROM
	city c1,
	country c2
WHERE
	c1.cid=c2.id;


/*
	删除视图
	标准语法
		DROP VIEW [IF EXISTS] 视图名称;
*/
-- 删除city_country视图
DROP VIEW IF EXISTS city_country;

6.存储过程和存储函数

6.1介绍

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合

6.2优点

• 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用

• 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可

• 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率

• 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理

6.3存储过程和函数的区别 

• 函数必须有返回值

• 存储过程没有返回值

6.4创建存储过程

-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句;
END$

-- 修改分隔符为分号
DELIMITER ;

-- 调用：标准语法
CALL 存储过程名称(实际参数);

-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';

 6.5变量

-- 定义变量：标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];

-- 变量赋值1：标准语法
SET 变量名 = 变量值;

-- 变量赋值2：标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

 

 

 6.6if语句

-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

 

 6.7参数传递

DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
	执行的sql语句;
END$
/*
	IN:代表输入参数，需要由调用者传递实际数据。默认的
	OUT:代表输出参数，该参数可以作为返回值
	INOUT:代表既可以作为输入参数，也可以作为输出参数
*/
DELIMITER ;

 6.8while循环

-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;

 6.9存储函数

DELIMITER $

-- 标准语法
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
	执行的sql语句;
	RETURN 结果;
END$

DELIMITER ;


-- 标准语法
SELECT 函数名称(实际参数);



-- 标准语法
DROP FUNCTION 函数名称;

7.触发器

7.1介绍

触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。

使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	OLD的含义	NEW的含义
INSERT 型触发器	无 (因为插入前状态无数据)	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据	NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据	无 (因为删除后状态无数据)

7.2创建触发器

DELIMITER $

CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
	触发器要执行的功能;
END$

DELIMITER ;

-- 创建日志表account_log
CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 日志id
	operation VARCHAR(20),			-- 操作类型 (insert update delete)
	operation_time DATETIME,		-- 操作时间
	operation_id INT,			-- 操作表的id
	operation_params VARCHAR(200)       	-- 操作参数
);

 

8.事务

8.1介绍

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

8.2事务的相关操作

操作事务的三个步骤

1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败

2. 执行sql语句：执行具体的一条或多条sql语句

3. 结束事务(提交|回滚)

   • 提交：没出现问题，数据进行更新

   • 回滚：出现问题，数据恢复到开启事务时的状态

-- 开始事务：标准语法
START TRANSACTION;

-- 回滚事务：标准语法
ROLLBACK;

-- 提交事务：标准语法
COMMIT;

8.3事务的提交方式

提交方式

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

• 手动提交（执行完语句后，需要通过commit来提交数据）

-- 查看提交方式：标准语法
SELECT @@AUTOCOMMIT;  -- 1代表自动提交    0代表手动提交

-- 修改提交方式：标准语法
SET @@AUTOCOMMIT=数字;

8.4事务的四大特征

• 原子性(atomicity)

  • 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响

• 一致性(consistency)

  • 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态

  • 拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性

• 隔离性(isolcation)

  • 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

• 持久性(durability)

  • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

8.5事务的隔离级别

隔离级别的概念

• 多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。

• 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别 , 否则就会产生问题 。

• 我们先来了解一下四种隔离级别的名称 , 再来看看可能出现的问题

	名称	隔离级别	会引发问题
1	读未提交	read uncommitted	脏读、不可重复读、幻读
2	读已提交	read committed	不可重复读、幻读
3	可重复读	repeatable read	幻读
4	串行化	serializable	无

问题	现象
脏读	是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读	是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读	select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功

-- 查询数据库隔离界别：标准语法
SELECT @@TX_ISOLATION;


- 修改数据库隔离级别：标准语法
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

8.5.1脏读

窗口1

-- 查询账户表
select * from account;

-- 设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查询转账结果 ,出现脏读(查询到其他事务未提交的数据)

-- 窗口2查看转账结果后，执行回滚
rollback;

窗口2

-- 查询隔离级别
select @@tx_isolation;

-- 开启事务
start transaction;

-- 查询账户表
select * from account;

要解决脏读的问题，将改成读已提交

8.5.2不可重复读

窗口1

-- 设置隔离级别为read committed
set global transaction isolation level read committed;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查看转账结果，并没有发生变化(脏读问题被解决了)

-- 执行提交事务。
commit;

-- 窗口2查看转账结果，数据发生了变化(出现了不可重复读的问题，读取到其他事务已提交的数据)

窗口2

-- 查询隔离级别
select @@tx_isolation;

-- 开启事务
start transaction;

-- 查询账户表
select * from account;

解决不可重复读的问题，改为可重复读

8.5.3幻读

窗口1

-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查看转账结果，并没有发生变化

-- 执行提交事务
commit;

-- 这个时候窗口2只要还在上次事务中，看到的结果都是相同的。只有窗口2结束事务，才能看到变化(不可重复读的问题被解决)

窗口2

-- 查询隔离级别
select @@tx_isolation;

-- 开启事务
start transaction;

-- 查询账户表
select * from account;

-- 提交事务
commit;

-- 查询账户表
select * from account;

解决幻读的问题，需要使用可串行化

/*
	窗口1
*/
-- 设置隔离级别为serializable
set global transaction isolation level serializable;

-- 开启事务
start transaction;

-- 添加一条记录
INSERT INTO account VALUES (4,'赵六',1600);

-- 查询账户表，本窗口可以查看到id为4的结果
SELECT * FROM account;

-- 提交事务
COMMIT;



/*
	窗口2
*/
-- 查询隔离级别
select @@tx_isolation;

-- 开启事务
start transaction;

-- 查询账户表，发现查询语句无法执行，数据表被锁住！只有窗口1提交事务后，才可以继续操作
select * from account;

-- 添加id为4的一条数据，发现已经存在了，就不会再添加了！幻读的问题被解决
INSERT INTO account VALUES (4,'测试',200);

-- 提交事务
COMMIT;

9.存储引擎

9.1MySQL体系结构

体系结构详解

• 客户端连接

  • 支持接口：支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库

• 第一层：网络连接层

  • 连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。

  • 例如：当客户端发送一个请求连接，会从连接池中获取一个连接进行使用。

• 第二层：核心服务层

  • 管理服务和工具：系统的管理和控制工具，例如备份恢复、复制、集群等。

  • SQL接口：接受SQL命令，并且返回查询结果。

  • 查询解析器：验证和解析SQL命令，例如过滤条件、语法结构等。

  • 查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句

  • 缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询！

• 第三层：存储引擎层

  • 插件式存储引擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)

• 第四层：系统文件层

  • 文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存

9.2存储引擎

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

MySQL存储引擎的概念

• MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力，在MySQL中 , 将这些不同的技术及配套的功能称为存储引擎

• 在关系型数据库中数据的存储是以表的形式存进行储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

• Oracle , SqlServer等数据库只有一种存储引擎 , 而MySQL针对不同的需求, 配置MySQL的不同的存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能。

• 通过选择不同的引擎 ,能够获取最佳的方案 , 也能够获得额外的速度或者功能，提高程序的整体效果。所以了解引擎的特性 , 才能贴合我们的需求 , 更好的发挥数据库的性能。

MySQL支持的存储引擎

• MySQL5.7支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等

• 其中较为常用的有三种：InnoDB、MyISAM、MEMORY

• MyISAM存储引擎

  • 访问快,不支持事务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。

• InnoDB存储引擎(MySQL5.5版本后默认的存储引擎)

  • 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd 中。

• MEMORY存储引擎

  • 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。

特性	MyISAM	InnoDB	MEMORY
存储限制	有(平台对文件系统大小的限制)	64TB	有(平台的内存限制)
事务安全	不支持	支持	不支持
锁机制	表锁	表锁/行锁	表锁
B+Tree索引	支持	支持	支持
哈希索引	不支持	不支持	支持
全文索引	支持	支持	不支持
集群索引	不支持	支持	不支持
数据索引	不支持	支持	支持
数据缓存	不支持	支持	N/A
索引缓存	支持	支持	N/A
数据可压缩	支持	不支持	不支持
空间使用	低	高	N/A
内存使用	低	高	中等
批量插入速度	高	低	高
外键	不支持	支持	不支持

9.3存储引擎的操作

/*
	查询数据库支持的存储引擎
	SHOW ENGINES;
*/
-- 查询数据库支持的存储引擎
SHOW ENGINES;



/*
	查询某个数据库中所有数据表的存储引擎
	SHOW TABLE STATUS FROM 数据库名称;
*/
-- 查询db4数据库所有表的存储引擎
SHOW TABLE STATUS FROM sql_study;



/*
	查询某个数据库中某个表的存储引擎
	SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
*/
-- 查看db4数据库中category表的存储引擎
SHOW TABLE STATUS FROM sql_study WHERE NAME = 'category';



/*
	创建数据表指定存储引擎
	CREATE TABLE 表名(
	      列名,数据类型,
	      ...
	)ENGINE = 引擎名称;
*/
CREATE TABLE engine_test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
)ENGINE = MYISAM;

SHOW TABLE STATUS FROM sql_study;

/*
	修改数据表的存储引擎
	ALTER TABLE 表名 ENGINE = 引擎名称;
*/
-- 修改engine_test表的存储引擎为InnoDB
ALTER TABLE engine_test ENGINE = INNODB;

 9.4存储引擎的选择

• MyISAM ：由于MyISAM不支持事务、不支持外键、支持全文检索和表级锁定，读写相互阻塞，读取速度快，节约资源，所以如果应用是以查询操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

• InnoDB : 是MySQL的默认存储引擎， 由于InnoDB支持事务、支持外键、行级锁定 ，支持所有辅助索引(5.5.5后不支持全文检索)，高缓存，所以用于对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作，那么InnoDB存储引擎是比较合适的选择，比如BBS、计费系统、充值转账等

• MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

• 总结：针对不同的需求场景，来选择最适合的存储引擎即可！如果不确定、则使用数据库默认的存储引擎！

10.索引

10.1介绍

MySQL数据库中的索引：是帮助MySQL高效获取数据的一种数据结构！所以，索引的本质就是数据结构。

在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

10.2分类

功能分类

• 普通索引： 最基本的索引，它没有任何限制。

• 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。

• 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。

• 组合索引：顾名思义，就是将单列索引进行组合。

• 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。

• 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

结构分类

• B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。

• Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。

10.3操作

创建索引

        注意：如果一个表中有一列是主键，那么就会默认为其创建主键索引！(主键列不需要单独创建索引)

-- 标准语法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]  -- 默认是B+TREE
ON 表名(列名...);


-- 为student表中姓名列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);

-- 为student表中年龄列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);

查看索引

-- 标准语法
SHOW INDEX FROM 表名;

添加索引

-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

删除索引

-- 标准语法
DROP INDEX 索引名称 ON 表名;

10.4原理

索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。这里我们主要介绍InnoDB引擎的实现的B+Tree索引。

B+Tree是一种树型数据结构，是B-Tree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。我们逐步的来了解一下。

10.4.1磁盘存储

• 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的

• 位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

• InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。

• InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

10.4.2BTree

BTree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述BTree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

10.4.3B+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区别：

• 非叶子节点只存储键值信息。

• 所有叶子节点之间都有一个连接指针。

• 数据记录都存放在叶子节点中。

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

• 创建索引时的原则

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

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

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

  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

• 联合索引的特点

• 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 = '张三';

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

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

 11.锁

11.1介绍

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

同步就相当于是加锁。加了锁以后有什么好处呢？当一个线程真正在操作数据的时候，其他线程只能等待。当一个线程执行完毕后，释放锁。其他线程才能进行操作！保证数据的一致性和安全性

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

11.2分类

• 按操作分类：

  • 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据记录。

  • 排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入

• 按粒度分类：

  • 表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！

  • 行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！

  • 页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。

• 按使用方式分类：

  • 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。

  • 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据

11.3InnoDB共享锁 

共享锁特点：数据可以被多个事务查询，但是不能修改

创建共享锁

-- 标准语法
SELECT语句 LOCK IN SHARE MODE;

-- 窗口1
/*
	共享锁：数据可以被多个事务查询，但是不能修改
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录。加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询分数为99分的数据记录。加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;

-- 窗口2
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询，可以查询)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(可以查询。共享锁和共享锁兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id为1的姓名为张三三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三三' WHERE id = 1;

-- 修改id为2的姓名为李四四(修改成功，InnoDB引擎默认是行锁)
UPDATE student SET NAME='李四四' WHERE id = 2;

-- 修改id为3的姓名为王五五(注意：InnoDB引擎如果不采用带索引的列。则会提升为表锁)
UPDATE student SET NAME='王五五' WHERE id = 3;

-- 提交事务
COMMIT;

11.4InnoDB排他锁

特点：加锁 的数据，不能被其他事务加锁查询或修改

创建排他锁

-- 标准语法
SELECT语句 FOR UPDATE;

-- 窗口1
/*
	排他锁：加锁的数据，不能被其他事务加锁查询或修改
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录，并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 提交事务
COMMIT;

-- 窗口2
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询id为1的数据记录，并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id为1的姓名为张三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三' WHERE id=1;

-- 提交事务
COMMIT;

11.5MyISAM读锁

特点：所有连接只能传数据，不能修改

-- 标准语法
-- 加锁
LOCK TABLE 表名 READ;

-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

-- 窗口1
/*
	读锁：所有连接只能读取数据，不能修改
*/
-- 为product表加入读锁
LOCK TABLE product READ;

-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改华为手机的价格为5999(修改失败)
UPDATE product SET price=5999 WHERE id=1;

-- 解锁
UNLOCK TABLES;

-- 窗口2
-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改华为手机的价格为5999(不能修改，窗口1解锁后才能修改成功)
UPDATE product SET price=5999 WHERE id=1;

11.6MyISAM写锁

特点：其他链接不能查询和修改数据

-- 标准语法
-- 加锁
LOCK TABLE 表名 WRITE;

-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

-- 窗口1
/*
	写锁：其他连接不能查询和修改数据
*/
-- 为product表添加写锁
LOCK TABLE product WRITE;

-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改小米手机的金额为3999(修改成功)
UPDATE product SET price=3999 WHERE id=2;

-- 解锁
UNLOCK TABLES;

-- 窗口2
-- 查询product表(不能查询。只有窗口1解锁后才能查询成功)
SELECT * FROM product;

-- 修改小米手机的金额为2999(不能修改。只有窗口1解锁后才能修改成功)
UPDATE product SET price=2999 WHERE id=2;

11.7悲观锁和乐观锁

悲观锁：

• 就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。

• 整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。

• 我们之前所学的行锁，表锁不论是读写锁都是悲观锁。

乐观锁

• 就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。

• 但是在更新的时候会去判断在此期间数据有没有被修改。

• 需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

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

• 对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。

• 如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁。

 乐观锁的实现方式：

        版本号

• 给数据表中添加一个version列，每次更新后都将这个列的值加1。

• 读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。

• 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

• 用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。

-- 创建city表
CREATE TABLE city(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
	NAME VARCHAR(20),                   -- 城市名称
	VERSION INT                         -- 版本号
);

-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1);

-- 修改北京为北京市
-- 1.查询北京的version
SELECT VERSION FROM city WHERE NAME='北京';
-- 2.修改北京为北京市，版本号+1。并对比版本号
UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;

时间戳

• 和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp

• 每次更新后都将最新时间插入到此列。

• 读取数据时，将时间读取出来，在执行更新的时候，比较时间。

• 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

---------------------------------------------------------------------------------------------------------------------------------

内容有部分存在书籍、课堂、网络记录，如有雷同纯属巧合