Mysql（一）sql语句、索引、视图初体验

简介

Mysql是按照数据结构来组织、存储和管理数据的仓库；是一个长期存 储在计算机内的、有组织的、可共享的、统一管理的大量数据 的集合；

Mysql和redis的区别：

• Mysql做增删改操作必须落盘才返回。redis不持久的话就存在内存中。
• Mysql是关系型数据库（类似与二维表），Redis非关系型数据库（key-value）。
• Mysql网络结构是select + 阻塞的io，redis是单reactor模型，并且单线程执行命令。

数据处理的类别：
OLTP（On-Line transaction processing）翻译为联机事务处理；主要对数据库增删改查；强调实时性高、稳定性强、确保数据及时更新成功；通常会使用cache、B+树进行优化。
OLAP（On-Line Analytical Processing）翻译为联机分析处理；主要对数据库查询；通常使用多磁盘加大数据吞吐量来进行优化；主要用于数据的分析决策。

SQL命令定义：
DQL：Data Query Language - 数据查询语言；用于数据查询，例如select。 DML：Data Manipulate Language - 数据操作语言；用于数据修改，例如insert、update、delete。
DDL：Data Define Languge - 数据定义语言；例如create、alter、drop。
DCL：Data Control Language- 数据控制语言；例如grant、revoke.
TCL：Transaction Control Language - 事务控制语言；例如commit、rollback。

Mysql体系结构

MySQL 由以下几部分组成：
连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件。

值得注意的是连接池的网络处理流程是主线程接收连接，接收的连接交由连接池处理；因此MySQL 命令处理是并发处理的；而redis是单线程顺序执行。
Mysql网络的主要处理方式是IO多路复用 select + 阻塞的 io；select只监听listenfd,不会管连接线程的读写。select是跨平台的，mysql可以在Linux和windows下运行；redis（使用epoll）只能在Linux下运行,在windows中使用的是用select替换的，并且windows没有fork子线程，功能不全。

一条SQL语句经历的步骤：
首先经历连接器（建立，管理连接，校验用户信息）然后通过查询缓存，查到直接命中，没查到会继续运行然后sql语句被分析器语句分析，语法分> 析，生成语法树；经过优化器选择最优的执行步骤；通过执行器根据执行计划，从存储引擎获取数据，并返回客户端。

数据库设计三范式

设计三范式的目的是为了降低空间占用。建立冗余较小、结构合理的数据库。（时间换空间）

范式一

确保每列保持原子性；即列不可分。例如：某表中有一个地址字段，如果经常需要访问地址字段中的城市属性，则需要将该字段拆分为多个字段，省份、城市、详细地址等；

范式二

确保表中的每列都和主键相关；即依赖主键。例如

其中主键为订单编号。可以看到商品名称、数量等信息与主键没有关系，而是和商品名称有关，所以需要拆分表格。

注意：范式二也不允许只依赖于主键的一部分，必须完全依赖主键。

范式三

确保每列都和主键直接相关，而不是间接相关；即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

反范式

范式可以避免数据冗余，减少数据库的空间，减小维护数据完整性的麻烦；但是采用数据库范式化设计，可能导致数据库业务涉及的表变多，并且造成更多的联表查询，将导致整个系统的性能降低；因此处于性能考虑，可能需要进行反范式设计；（空间换时间）

CRUD

增

INSERT INTO `table_name`(`field1`, `field2`, ...,`fieldn`) VALUES (value1, value2, ..., valuen);

删

删除数据的三种方式：drop,truncate,delete速度依次降低

DROP TABLE `table_name`;//删除整张表，包括索引，约束，触发器等（不能回滚）
TRUNCATE TABLE `table_name`;//删除表数据，以以页为单位删除；其他保留（不能回滚）
DELETE TABLE `table_name`;//删除部分或全部数据，逐行删除，其他保留（条件删除）可以回滚

查

SELECT field1, field2,...fieldN FROM table_name[WHERE Clause]

改

UPDATE table_name SET field1=new_value1,field2=new_value2 [, fieldn=new_valuen]

高级查询

高级查询主要了解分组查询和聚合查询

分组查询

-- 分组加group_concat
SELECT `gender`, group_concat(`age`) as ages FROM `student` GROUP BY `gender`;
-- 分组加条件（having的条件可以用select中本条命令查到的，而where做不到）
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num > 6;
//SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` where num > 6;  该指令有问题

条件判断：
1.where condition
2.group by column having condition

聚合查询

SELECT sum(`num`) FROM `score`;

联表查询

内联：inner join,只取两张表有对应关系的记录

SELECT
cid
FROM
`course`
INNER JOIN `teacher` ON course.teacher_id =
teacher.tid;

外联：left join, right join；在内连接的基础上保留左表/右表没有对应关系的记录

SELECT
course.cid
FROM
`course`
LEFT JOIN `teacher` ON course.teacher_id =
teacher.tid;

SELECT
course.cid
FROM
`course`
RIGHT JOIN `teacher` ON course.teacher_id =
teacher.tid;

视图

视图（ view ）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。其内容由查询定义。

视图只做select查询，不做增删改（虽然可以做，但是一般不用，限制比较多）。在工作项目中：比如一个充值表，我只给你一个视图，不会给你表，你就没法修改这个核心资源。只能查，不能改。

作用：

可复用，减少重复语句书写；类似程序中函数的作用； 重构利器（假如因为某种需求，需要将 user 拆成表 usera 和表
userb；如果应用程序使用 sql 语句： select * from user 那就会提示该表不存在；若此时创建视图 create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name; ，则只需要更改数据库结构，而不需要更改应用程序；） 逻辑更清晰，屏蔽查询细节，关注数据返回；
权限控制，某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作；

文章参考与<零声教育>的C/C++linux服务器高级架构系统教程学习