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

文章目录

Mysql数据库1——基本原理和基础操作
- 1. 基本概念
- 2. Mysql体系结构
- - 2.1 连接层
  - 2.2 服务层
  - 2.3 存储引擎层
- 3. 三级范式与反范式
- 4. 完整性约束
- - 4.1 实体完整性约束
  - 4.2 参照完整性约束
- 5. CRUD
- - - DDL
    - DML
    - DCL
    - DQL
- 6. 高级查询
- - - 基础查询
    - 条件查询
    - 分页查询
    - 查询结果排序
    - 分组聚合查询
    - 联表查询
    - 子查询
- 7. 预处理语句
- 8. 视图与触发器
- 学习参考

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建立客户端连接的主要操作，从中可以得到如下信息：

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

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

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

2.2 服务层

如何执行一条SELECT语句:

查询缓存（5.7之前）

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

进行词法分析生成语法树。
优化器

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

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

2.3 存储引擎层

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

3. 三级范式与反范式

范式的主要目的：

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

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

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

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

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

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`;

# 补集
SELECT
	A.`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服务器程序解析、编译和优化，生成执行计划然后才能执行，使用预处理语句可以将执行计划缓存起来，每次执行只需要传参数即可。

使用预处理语句的好处：

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

8. 视图与触发器

CREATE VIEW `view_name` AS
	SELECT ...;

作用

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

学习参考

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

秒客网

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

文章目录

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

1. 基本概念

2. Mysql体系结构

2.1 连接层

2.2 服务层

2.3 存储引擎层

3. 三级范式与反范式

4. 完整性约束

4.1 实体完整性约束

4.2 参照完整性约束

5. CRUD

DDL

DML

DCL

DQL

6. 高级查询

基础查询

条件查询

分页查询

查询结果排序

分组聚合查询

联表查询

子查询

7. 预处理语句

8. 视图与触发器

学习参考

相关文章