Mysql数据库1——基本原理和基础操作

Mysql数据库1——基本原理和基础操作

本文讲述了mysql数据库的基本概念、原理和常用CRUD语句。

1. 基本概念

数据库 按照一定数据结构组织、存储和管理数据的仓库。

数据表 数据表是数据库中数据存储的基本单元，由行和列组成。每个表用于存储与某个特定主题或实体相关的数据。

行 表中的一行，通常代表一个数据实体或记录。每行包含多个字段的值。

列 表中的一个字段，定义了数据的类型和结构。每列包含相同类型的数据，描述了行中的某个属性。

关键字 能够唯一标识一个数据记录的最小属性集合。

主键 被选择唯一标识一个数据记录的关键字，非空，每个表只能有一个记录。

**外键 ** 外键是指向另一个表中主键或唯一键的列，用于建立表与表之间的关联关系。外键约束确保了数据的一致性和完整性。

组合键 组合键是由多个列组合成的主键或唯一键，通常用于标识表中的记录，这些列的组合必须是唯一的。

索引 索引是数据库中一种数据结构，用于加速数据检索操作。它可以提高查询效率，但会增加插入和更新的开销。

级联与限制

级联：在外键约束中，级联操作指的是当主表数据发生变化时，如何影响从表的数据。例如，级联删除（CASCADE DELETE）会删除外键关联的记录。

限制：限制是对数据表中的列进行约束，如 NOT NULL、UNIQUE、CHECK 等，以确保数据的完整性。

关系型数据库 关系型数据库是基于关系模型的数据库，其中数据通过表格（关系）组织，每个表之间可以通过外键建立联系。

OLTP (On-Line Transaction Processing) 翻译为联机事务处理；主要对数据库进行增删改查。

OLAP (On-Line Analytical Processing) 联机分析处理；主要对数据库查询；

SQL (Structured Query Language) 结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系型数据库系统。

DQL (Data Query Language) 数据查询语言，主要是select语句。

DML (Data Manipulate Language) 数据操作语言，insert into, delete from, …

DDL (Data Define Language) 数据定义语言, create table, alter table, …

DCL (Data Control Language) 数据控制语言, create user, grant, …

TCL (Transaction Control Language) 事务控制语言，包括commit, rollback等

2. Mysql体系结构

2.1 连接层

Mysql作为一个服务器运行，因此需要有一个网络层连接层负责创建和管理与客户端的连接。

IO复用

早期mysql使用select进行IO复用，后来针对linux平台使用epoll，针对windows平台使用iocp。

select(listenfd + 1, readfds, NULL, NULL, 0);
int clientfd = accept(listenfd, &addr, &len);
mysql_thread_create(key_thread_one_connection, &id,&connection_attrib,handle_connection,(void *)channel_info);

以上代码是mysql建立客户端连接的主要操作，从中可以得到如下信息：

1. select只负责监听listenfd
2. 每个客户端连接都独占一个线程进行处理。

mysql内部网络模型——每个线程一个连接

默认情况下，mysql只能同时处理151个连接。

2.2 服务层

如何执行一条SELECT语句:

1. 查询缓存（5.7之前）

   如果再kv缓存中命中则直接返回，否则继续向下执行。

2. 分析器

   进行词法分析生成语法树。

3. 优化器

   指定执行计划，选择查询成本最小的计划。

4. 执行器

   根据执行计划，从存储引擎获取数据，并返回客户端。

2.3 存储引擎层

InnoDB是默认使用的也是最常被使用的存储引擎，因此主要介绍它。Mysql也支持存储引擎的热插拔。

3. 三级范式与反范式

范式的主要目的：

1. 减少数据冗余和空间占用
2. 增强数据的一致性

一级范式：列不可再拆分，数据记录的每个字段都具有原子性。

二级范式：在一级范式基础上，消除了非主属性对主键的部分依赖。

三级范式：在二级范式基础上，消除了非主属性对主键的传递依赖。

反范式：为了提升查找效率，允许一定的数据冗余。

4. 完整性约束

4.1 实体完整性约束

• not null 非空约束
• auto_increment 自增约束
• unique 唯一约束
• primary 主键约束 --> 非空且唯一

4.2 参照完整性约束

• foreign key 外键约束

5. CRUD

DDL

# 创建数据库
CREATE DATABASE `数据库名` DEFAULT CHARACTER SET utf8;
# 修改数据库默认编码
ALTER DATABASE `数据库名` CHARACTER SET utf8mb4 COLLATE utf8mb4-general-ci;
# 选择数据库
USE `数据库名`;
# 删除数据库
DROP DATABASE `数据库名`;
# 查看数据库有哪些表
SHOW DATABASE;
# 创建表
CREATE TABLE [IF NOT EXISTS] `表名` (column_name1, column_type1,column_name2, column_type2,...
) ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT = '注释';
# 查看表结构
DESC `table_name`;# 修改表结构——添加列
ALTER TABLE `table_name` ADD `column` column_type [约束] [AFTER `another_table_name` | FIRST];
# 修改表结构——删除列
ALTER TABLE `table_name` DROP COLUMN `column`;
# 修改表结构——修改列数据类型或属性
ALTER TABLE `table_name` MODIFY COLUMN `column` column_type [约束];
# 修改表结构——修改列名和数据类型
ALTER TABLE `table_name` CHANGE COLUMN `old_column_name` `new_column_name` new_column_type [约束];
# 修改表结构——添加外键
ALTER TABLE `table_name` ADD PRIMARY KEY (`column1`, ...);
# 修改表结构——删除主键
ALTER TABLE `table_name` DROP PRIMARY KEY;
# 修改表结构——添加外键
ALTER TABLE `table_name` ADD CONSTRAINT 外键名 FOREIGN KEY (`column`) REFERENCES `tar_table`(`tar_column`) [ON DELETE 操作 ON UPDATE 操作];
# 修改表结构——删除外键
ALTER TABLE `table_name` DROP FOREIGN KEY 外键名;
# 修改表结构——重命名表
ALTER TABLE `table_name` RENAME TO `new_table_name`;
# 修改表结构——修改默认值
ALTER TABLE `table_name` ALTER COLUMN `column` SET DEFAULT 默认值;
# 修改表结构——修改表的字符编码
ALTER TABLE `table_name` CONVERT TO CHARACTER SET 字符集名 COLLATE 排序规则;# 删除表
# 删除整个表结构和数据记录，释放空间，不能回滚，不具备事务性
DROP TABLE `table_name`;	# DDL# 清空表中数据
# 不能回滚，以页（16KB）为单位删除数据，释放空间，有自增索引的话，从初始值开始自增
TRUNCATE TABLE `table_name`;	# DDL
# 可以回滚，标记删除，逐行删除，不会重置自增索引
DELETE TABLE `table_name`;		# DML

DML

# 插入
INSERT INTO `table_name` (`field1`, `field2`) VALUES (v1, v2);
# 删除
DELETE FROM `table_name` [WHERE Clause];
# 修改
UPDATE `table_name` SET field1=v1, field2=v2, ... [WHERE Clause];

DCL

# 创建用户
CREATE USER 'user_name'@'host_name' IDENTIFIED BY 密码;
# 授予权限
GRANT 权限列表 ON `db_name`.`table_name` TO `user_name` [IDENTIFIED BY 密码] [WITH GRANT OPTION];
# 撤销权限
REVOKE 权限列表 ON `da_name`.`table_name` FROM `user_name`;
# 查看权限
SHOW GRANTS;	# 查看当前用户权限
SHOW GRANTS FOR 'user_name'[@'host_name'];
# 查看已创建的用户
SELECT `user`, `host` FROM mysql.user;
# 修改用户密码
ALTER USER 'user_name'[@'host_name'] IDENTIFIED BY 新密码;
# 允许用户从不同主机访问
RENAME USER 'user_name'@'old_host' TO 'user_name'@'new_host';
# 删除用户
DROP USER 'user_name'[@'host_name'];

DQL

请看下一节高级查询。

6. 高级查询

基础查询

# 查询指定字段，可指定别名和是否去重
SELECT [DISTINCT] [`field1` [AS `别名`], ..., `fieldn` [AS `别名`] | *] FROM `table_name`;

条件查询

# 等值条件
SELECT * FROM `table` WHERE `field` = val;
# 范围条件
SELECT * FROM `table` WHERE `field` BETWEEN x AND y;
# 判空条件
SELECT * FROM `table` WHERE `field` IS [NOT] NULL;
SELECT * FROM `table` WHERE `field` = '';	# 空字符串
SELECT * FROM `table` WHERE `field` <> '';
# 字符串匹配
SELECT * FROM `table` WHERE `field` LIKE '谢%';	# %代表任意数量的任意字符
SELECT * FROM `table` WHERE `field` LIKE '谢_';	# _代表占位符

分页查询

# 产看第2到第5条数据记录
SELECT * FROM `table` LIMIT 1,4;

查询结果排序

SELECT * FROM `table` ORDER BY `field1` ASC, `field2` DESC;

分组聚合查询

# 聚合函数
SELECT sum(`field`) FROM `table`;
SELECT count(`field`) FROM `table`;
SELECT max(`field`) FROM `table`;
SELECT min(`field`) FROM `table`;
SELECT avg(`field`) FROM `table`;# 分组聚合
SELECT `sid`, count(DISTINCT `habits`) FROM `table` GROUP BY `sid`;# 使用HAVING对聚合结果近一步过滤
SELECT `sid`, count(DISTINCT `habits`) AS num FROM `table` GROUP BY `sid` HAVING num < 5;

联表查询

# INNER JOIN 只取两张表有对应关系的记录
SELECT `sid` 
FROM `course` INNER JOIN `teacher` 
ON `course`.`sid` = `teacher`.`tid`;# LEFT JOIN 在INNER JOIN基础上保留取左表中独有的记录
SELECT`sid`
FROM `course` LEFT JOIN `teacher`
ON `cousrse`.`sid` = `teacher`.`tid`;# RIGHT JOIN 在INNER JOIN基础上保留取右表中独有的记录
SELECT`sid`
FROM `course` RIGHT JOIN `teacher`
ON `course`.`sid` = `teacher`.`tid`;# 查询差集 左集 - 右集
SELECT`sid`
FROM `course` LEFT JOIN `teacher`
ON `course`.`sid` = `teacher`.`tid`
WHERE `teacher`.`tid` IS NULL;# 查询差集，右集 - 左集
SELECT`teacher`.`tid`
FROM `course` RIGHT JOIN `teacher`
ON `course`.`sid` = `teacher`.`tid`
WHERE `course`.`sid` IS NULL;# FULL OUTER JOIN 求并集
SELECT`teacher`.`tid`
FROM `course` FULL OUTER JOIN `teacher`
ON `course`.`sid` = `teacher`.`tid`;# 补集
SELECTA.`key`
FROM `course` A FULL OUTER JOIN `teacher` B
ON A.`key` = B.`key`
WHERE A.key IS NULL OR B.key IS NULL;

子查询

# 单行子查询（即子查询只返回一行）
SELECT * FROM `course` WHERE `teacher_id` = (SELECT `tid` FROM `teacher` WHERE `name` = '贴的');
# 多行子查询
# 用于WHERE Clause中时
# IN关键字
SELECT * FROM `student` WHERE `class_id` IN (SELECT `cid` FROM `course` WHERE `teacher_id` = 2);
# EXISTS关键字
SELECT * FROM `student` AS S WHERE EXISTS (SELECT `cid` FROM `course` WHERE `teacher_id` = S.tid);
# ALL关键字 子查询结果都满足条件时才成立
SELECT * FROM `student` AS S WHERE `score` > ALL (SELECT `score` FROM `course` WHERE `teacher_id` = 2);
# ANY关键字 只要有一个满足条件就成立
SELECT * FROM `student` AS S WHERE `score` > ANY (SELECT `score` FROM `course` WHERE `teacher_id` = 2);

7. 预处理语句

通常，一个sql语句需要经过sql服务器程序解析、编译和优化，生成执行计划然后才能执行，使用预处理语句可以将执行计划缓存起来，每次执行只需要传参数即可。

使用预处理语句的好处：

1. 可以减少重复的sql语句解析、过滤、优化等步骤。
2. 避免sql注入。

8. 视图与触发器

CREATE VIEW `view_name` ASSELECT ...;

作用

• 可复用，减少重复语句书写。
• 可用于重构，使数据库结构变更不影响应用程序。
• 逻辑更清晰，屏蔽查询细节，关注数据返回。
• 权限控制，某些表对于用户屏蔽，但是允许用户对视图进行操作。

学习参考

学习更多相关知识请参考零声 github。