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 体系结构

mysql 体系结构

连接器：实现 redis 协议
服务层

连接池：管理连接，校验用户信息
sql 接口：sql 语句词法分析，生成对象。
解析器：句法分析，生成语法树
优化器：优化执行方式。
缓冲组件: 缓存最近操作的数据

引擎层：数据的存储和获取（表），默认 innoDB

2.1.2、innoDB 体系结构

innoDB 体系结构

2.2、mysql 连接池

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

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

mysql 命令是并发处理的。

mysql 连接池

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

**2.3、* sql 执行流程**

sql 语句执行流程

面试题：一条 sql 执行流程

server 层

连接器：建立连接，管理连接，校验用户身份
查询缓存：mysql 8.0 废除。kv 存储，命中直接返回，否则继续执行
分析器：解析 SQL ，词法分析、句法分析，生成语法树，方便后续模块读取表名、字段、语句类型。
优化器：指定执行计划，选择执行成本最小的计划
执行器：根据执行计划，从存储引擎获取数据，并返回客户端

引擎层

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

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, truncate, delete
-- 删除表
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;

Mysql 入门