Mysql 入门

1、数据库相关知识

1.1、数据处理分类

• OLTP（online transaction processing）：联机事务处理，主要对数据库增删改查
• OLAP（On-Line Analytical Processing）：联机分析处理，主要对数据库查询

1.2、SQL

Structured Query Language，结构化查询语言

• DQL：数据查询语言 Data Query Language，select
• DDL：数据定义语言 Data Define Languge，create alter drop
• DML：数据操作语言 Data Manipulate Language，insert update delete
• DCL：数据控制语言 Data Control Language，grant revoke
• TCL：事务控制语言 Transaction Control Language，commit rollback

1.3、数据库设计范式

1.3.1、范式

范式目的：减少空间占用，避免数据冗余。

• 范式1：列不可分。每列（字段）保持原子性。
• 范式2：依赖主键。不能只与主键的某一部分相关（组合索引）。(a,b) <- c, a <- d
• 范式3：直接相关。每列都和主键直接相关，而不是间接相关。a <- b <- c

1.3.2、反范式

范式设计可能导致数据库涉及的表变多，造成更多的连表查询，降低系统性能。为了提升效率，允许冗余存储，也就是反范式设计。

1.4、约束

为了实现数据的完整性，innoDB 提供了约束

• primary 主键约束
• foreign 外键约束
• unique 唯一约束
• not null 非空约束
• auto_increment 自增约束

1.4.1、外键约束

外键约束用来关联两个表，来保证参照完整性。innoDB 完整支持外键，不具备事务性。

create table parent (
   id int not null,
    primary key(id)
) engine=innoDB;
create table child (
   id int,
   parent_id int,
    foreign key(parent_id) references parent(id)
ON DELETE CASCADE ON UPDATE CASCADE
) engine=innoDB;


CREATE TABLE `parent` (
	`id` INT NOT NULL,
	PRIMARY KEY(`id`)
) ENGINE=innoDB;

CREATE TABLE `child` (
	`id` INT,
	`parent_id` INT,
	FOREIGN KEY(`parent_id`) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=innoDB;

1.4.2、约束与索引

创建主键索引或者唯一索引的时候同时创建了相应的约束。但是约束是逻辑上的概念，索引是一个数据结构，既包含逻辑的概念，也包含物理的存储方式。

2、mysql 体系结构

2.1、mysql 构成

2.1.1、mysql 体系结构

• 连接器：实现 redis 协议
• 服务层
  • 连接池：管理连接，校验用户信息
  • sql 接口：sql 语句词法分析，生成对象。
  • 解析器：句法分析，生成语法树
  • 优化器：优化执行方式。
  • 缓冲组件: 缓存最近操作的数据
• 引擎层：数据的存储和获取（表），默认 innoDB

2.1.2、innoDB 体系结构

2.2、mysql 连接池

这里单独讲一下连接池。连接池用于管理连接，校验用户信息等。

• 网络流程：主线程接收连接，接收连接交由连接池处理
• 处理方式：io 多路复用 select + 阻塞 io。区别 reactor (非阻塞 io)

mysql 命令是并发处理的。

mysql 连接池如图所示，主线程负责接收客户端连接，然后为每个 clientfd 分配一个连接线程，负责处理该客户端的 sql 命令处理。由于线程的数量有上限，所以 mysql 使用短连接。

2.3、sql 语句流程

sql 执行流程：

server 层

• 连接器：建立连接，管理连接，校验用户信息
• 查询缓存：mysql 8.0废除，kv 存储，命中直接返回，否则继续执行
• 分析器：sql 语句词法句法分析，生成语法树
• 优化器：指定执行计划，选择执行成本最小的计划
• 执行器：根据执行计划，从存储引擎获取数据，并返回客户端

引擎层

• 写 undolog：事务回滚
• 索引缓存：判断目标页是否在内存缓存
• 写 redolog：事务持久化，确保本地数据一致
• 写 binlog：数据备份，主从复制，确保主从数据一致
• 提交事务
• commit-prepare：redolog 刷盘
• commit-commit：binlog 刷盘

3、CRUD

3.1、DDL

用于对结构的操作（数据库、表、索引、视图、触发器等）。

3.1.1、数据库

语法

-- 创建数据库
CREATE DATABASE DBName;
-- 删除数据库
DROP DATABASE DBName
-- 查看数据库
SHOW DATABASES;
-- 使用数据库
USE DBName
-- 复制数据库
mysqldump -u root -p 密码 --add-drop-table olddb| mysql -u root -p 密码 newdb

3.1.2、表

语法

-- 查看表 SHOW 
SHOW tables;

-- 创建表 CREATE
CREATE TABLE tableName (field type [constraint], ...)
-- 显示表的创建过程
SHOW CREATE TABLE tableName
-- 显示表的结构
DESC | DESCRIBE tableName

-- 修改表 ALTER
-- 添加列
ALTER TABLE tableName ADD (field type [constraint], ...)
-- 修改列
ALTER TABLE tableName MODIFY | CHANGE field type [constraint]
-- 删除列
ALTER TABLE tableName DROP field

-- 删除表
DROP table tableName
-- 截断表
TRUNCATE TABLE tableName;
-- 清空表
DELETE FROM tableName;

-- 表的复制
-- 结构复制
CREATE TABLE newName LIKE oldName
-- 数据复制
CREATE TABLE newName SELECT field... FROM oldName

实例：

CREATE TABLE IF NOT EXISTS `schedule` (
	`id` INT AUTO_INCREMENT COMMENT '编号',
	`course` VARCHAR(100) NOT NULL COMMENT '课程',
	`teacher` VARCHAR(40) NOT NULL COMMENT '讲师',
	PRIMARY KEY (`id`)
) ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT = '课程表';
SHOW CREATE TABLE `schedule`;
DESC `schedule`;

ALTER TABLE `schedule` ADD (`begin_time` DATE DEFAULT '2022-10-24');
ALTER TABLE `schedule` MODIFY `begin_time` DATE AFTER `id`;  
ALTER TABLE `schedule` DROP `begin_time`;

TRUNCATE TABLE `schedule`;
DESC `schedule`;

3.2、DML

用于对数据库表中数据进行操作。

-- 增 insert
INSERT INTO tableName (field1,field2...) values (value1,value2...)

-- 删 delete
DELETE FROM `table_name` [WHERE condition];

-- 改 update
UPDATE tableName SET field=new_value [WHERE condition]

-- 查 select 
SELECT field... FROM table_name [WHERE condition]

三种删除操作的比较

• drop：最快，删除整张表结构和表数据，包括索引、约束、触发器等，不能回滚。
• truncate：较快，删除表数据，其他保留，以页为单位进行删除，不能回滚。
• delete：慢，删除部分或全部数据，其他保留，条件删除，逐行删除，可以回滚。

drop, truncate 命令最好在停机的时候使用，且注意不能回滚。

3.3、DQL

用于查询数据。

SELECT columns... FROM TABLE 
WHERE condition
GROUP BY column... HAVING condition
ORDER BY column...

3.3.1、条件查询

WHERE condition
GROUP BY column... HAVING condition
A JOIN B ON condition

条件表示

-- 去重
- GROUP BY column
- DISTINCT column

-- 比较运算
BETWEEN ... AND ..  -- 范围查询
IN			-- 枚举查询				  				
LIKE	 	-- 模糊查询，正则表达
IS NULL		-- 判空查询

-- 逻辑运算
AND | OR | NOT

-- 排序，默认 ascend 升序，descend 降序
ORDER BY ASC | DESC

3.3.2、分页查询

-- 分页查询，查看第M条到第N条信息
-- 参数1：从该条记录开始显示，默认0 (从第一条开始) ；参数2：要显示的数目
LIMIT m, n

3.3.3、分组聚合

• 去除重复：分组查询
• 合并重复：聚合查询

-- 分组
GROUP BY field... [HAVING conditions]

-- 聚合 
SUM		-- 列的总和
AVG		-- 列的平均值
COUNT 	-- 列的行数
MAX	 	-- 列的最大值
MIN 	-- 列的最大值

3.3.4、连接查询

-- 内连接，交集
INNER JOIN

-- 外连接，内连接基础上，保留左（右）表没有对应关系的记录
LEFT JOIN
RIGHT JOIN
FULL JOIN

3.3.5、嵌套查询

-- 满足特定条件，结果相同，区别在于查询的顺序
IN 	    -- 先子查询，后主查询
EXISTS	-- 先主查询，后子查询

-- 满足所有条件
ALL  
-- 满足任一条件
ANY 

4、视图

视图 view 不是表，是一种虚表，没有实体，其内容由查询 select 定义。用来创建视图的表称为基表，通过视图，可以展现基表的部分数据。

视图的作用

• 复用：减少重复语句书写
• 重构：视图可以屏蔽表结构的变化对用户的影响，源表结构改变，视图只有在必要时才会修改。
• 简单：屏蔽查询细节，关注数据返回。用户不必关心表的结构，关联结构和筛选条件，只需关注过滤好的复合条件的结果集。
• 权限控制：使用视图的用户只能访问被允许查询的结果集。对表的管理权限不能限制具体行列，但可以给用户视图来操作被屏蔽的表。

在实际工作中通常只用 select，几乎不会使用 update delete insert，其限制条件很多。

语法：

CREATE VIEW 视图名 AS SELECT 语句

案例：创建视图，查询A课程比B课程成绩高的所有学生的学号。

USE mark;

DROP VIEW IF EXISTS `view_test1`;

CREATE VIEW `view_test1` AS SELECT A.student_id FROM
(SELECT `student_id`, `num` FROM `score` WHERE `course_id` = 1) AS A
LEFT JOIN
(SELECT `student_id`, `num` FROM `score` WHERE `course_id` = 2) AS B
ON A.student_id = B.student_id WHERE A.num > IFNULL(B.num, 0);
 
SELECT * FROM `view_test1`;

5、触发器

触发器（trigger）是一种对表执行某操作后会触发执行其他命令的机制。

5.1、要素

• 监视对象：table
• 监视事件：insert、update、delete
• 触发时间：before ，after
• 触发事件：insert、update、delete

5.2、语法

-- 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
BEGIN
	trigger_body
END

-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | UPDATE | DELETE }
-- trigger_order: { FOLLOWS | PRECEDES }
-- trigger_body：tbl_name表更新前(OLD, 列名)，表更新后(NEW, 列名)

-- 确认触发器
SHOW TRIGGERS

-- 删除触发器
SHOW TRIGGER trigger_name

5.3、案例

下订单的时候，对应的商品的库存量要相应的减少，具体需求为：

• 新建订单的商品数量，商品表的库存数量改变。
• 修改订单的商品数量， 商品表的库存数量改变。

use mark;

DROP TABLE IF EXISTS `goods`;
DROP TABLE IF EXISTS `order`;

CREATE TABLE `goods` (
 `gid` INT PRIMARY KEY auto_increment,
 `name` VARCHAR (32),
 `num` SMALLINT DEFAULT 0
);

CREATE TABLE `order` (
 `id` INT PRIMARY KEY auto_increment,
 `gid` INT,
 `quantity` SMALLINT COMMENT '下单数量'
);

DROP TRIGGER if EXISTS `trig_order_1`;
DROP TRIGGER if EXISTS `trig_order_2`;

-- 需求1：客户新建订单购买的数量，商品表的库存数量自动改变
delimiter // -- 设置 Mysql 执行结束标志，否则不会执行 END
CREATE TRIGGER `trig_order_1` AFTER INSERT ON `order` FOR EACH ROW
BEGIN
	UPDATE goods SET num = num - new.quantity WHERE gid = new.gid;
END //
delimiter ; -- 默认结束标志 ;

-- 需求2：客户修改订单购买的数量，商品表的库存数量自动改变
delimiter // -- 设置 Mysql 执行结束标志，否则不会执行 END
CREATE TRIGGER `trig_order_2` BEFORE UPDATE ON `order` FOR EACH ROW
BEGIN
	UPDATE goods SET num = num + old.quantity - new.quantity WHERE gid = new.gid;
END //
delimiter ; -- 默认结束标志 ;

INSERT INTO `goods` VALUES (NULL, 'cat', 10);
INSERT INTO `goods` VALUES (NULL, 'dog', 10);
INSERT INTO `goods` VALUES (NULL, 'pig', 10);

-- 测试1：新建订单
INSERT INTO `order` VALUES (NULL, 1, 2);
INSERT INTO `order` VALUES (NULL, 2, 2);
INSERT INTO `order` VALUES (NULL, 3, 2);

-- 测试2：修改订单
UPDATE `order` SET quantity = quantity + 2 WHERE gid = 1;

6、权限管理

6.1、创建用户

CREATE USER username@host IDENTIFIED BY password;

host: 用户登录的主机，本地 localhost，任意远程主机 %

6.2、权限管理

对表授权

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

• privileges：用户的操作权限，所有权限 ALL, 其他SELECT, INSERT, UPDATE等
• databasename.tablename: . 表示任意数据库的任意表
• WITH GRANT OPTION: 该用户可以将自己拥有的权限授权给别人

对视图授权

GRANT select, SHOW VIEW ON `databasename`.`tablename`  to 'username'@'host';

刷新权限

FLUSH PRIVILEGES;