MySQL① —— 基础概念与SQL语句

Mr. Lee～！

已于 2024-03-16 15:17:22 修改

阅读量747

点赞数 15

文章标签： mysql sql 数据库

于 2024-03-16 15:15:54 首次发布

本文链接：https://blog.csdn.net/qq_51148151/article/details/136763025

版权

1. MySQL基本概念

OLTP

联机事务处理，主要对数据库增删改查
OLTP 主要用来记录某类业务事件的发生；数据会以增删改的方式在数据库中进行数据的更新处理操作，要求实时性高、稳定性强、确保数据及时更新成功

OLAP

联机分析处理，主要对数据库查询
当数据积累到一定的程度，我们需要对过去发生的事情做一个总结分析时，就需要把过去一段时间内产生的数据拿出来进行统计分析，从中获取我们想要的信息，为公司做决策提供支持，这时候就是在做 OLAP 了

SQL

结构化查询语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL 是关系数据库系统的标准语言
SQL 命令包括：DQL、DML、DDL、DCL以及TCL

DQL

数据查询语言
select：从一个或者多个表中检索特定的记录

DML

数据操作语言
insert：插入记录；update：更新记录；delete：删除记录；

DDL

数据定义语言
create：创建一个新的表、表的视图、或者在数据库中的对象；alter：修改现有的数据库对象，例如修改表的属性或者字段；drop：删除表、数据库对象或者视图；

DCL

数据控制语言
grant：授予用户权限；revoke：收回用户权限；

TCL

事务控制语言
commit：事务提交；rollback：事务回滚；

数据库术语

主键：主键是唯一的；一个数据表只能包含一个主键
外键：外键用来关联两个表，来保证参照完整性；MyISAM 存储引擎本身并不支持外键，只起到注释作用；而 innoDB 完整支持外键；
复合键：或称组合键；将多个列作为一个索引键；
索引：用于快速访问数据表的数据；索引是对表中的一列或者多列的值进行排序的一种结构；

2. MySQL体系结构

在这里插入图片描述

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

连接池

管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求
网络处理流程：主线程接收连接，接收连接交由连接池处理
主要处理方式：IO多路复用 select + 阻塞io
MySQL 命令处理是多线程并发处理的
主线程负责接收客户端连接，然后为每个客户端 fd 分配一个连接线程，负责处理该客户端的 sql 命令处理

管理服务和工具组件

系统管理和控制工具，例如备份恢复、MySQL 复制、集群等

SQL接口

将 SQL 语句解析生成相应对象；DML，DDL，存储过程，视图，触发器等

查询解析器

将 SQL 对象交由解析器验证和解析，并生成语法树

查询优化器

SQL 语句执行前使用查询优化器进行优化

缓冲组件

是一块内存区域，用来弥补磁盘速度较慢对数据库性能的影响
缓冲池中采用最近最少使用算法 LRU 来进行管理
缓冲池缓存的数据类型有：索引页、数据页、以及与存储引擎缓存相关的数据（比如innoDB 引擎：undo 页、插入缓冲、自适应 hash 索引、innoDB 相关锁信息、数据字典信息等）

3. 数据库设计三范式

范式一：确保每列保持原子性；数据库表中的所有字段都是不可分解的原子值
范式二：确保表中的每列都和主键相关，而不能只与主键的某一部分相关（组合索引）
范式三：确保每列都和主键直接相关，而不是间接相关；减少数据冗余
反范式：
- 范式可以避免数据冗余，减少数据库的空间，减小维护数据完整性的麻烦；但是采用数据库范式化设计，可能导致数据库业务涉及的表变多，并且造成更多的联表查询，将导致整个系统的性能降低；因此基于性能考虑，可能需要进行反范式设计

4. CRUD

创建数据库

create database `数据库名` default character set UTF8;

删除数据库

drop database `数据库名`;

选择数据库

use `数据库名`;

创建表

create table `table_name` (column_name column_type);

create table if not exists `table_name`(
	`id` int unsigned auto_increment comment `编号`,
	`course` varchar(100) not NULL comment `课程`,
	`teacher` varchar(40) not NULL comment `讲师`,
	`price` decimal(8, 2) not NULL comment `价格`,
	primary key(`id`)
)engine=innoDB default charset=utf8 comment=`课程表`;

删除表

drop table `table_name`;

清空数据表
- 截断表，以页为单位（至少有两行数据），有自增索引的话，从初始值开始累加
```
truncate table `table_name`;
```
- 逐行删除，有自增索引的话，从之前值继续累加
```
delete table `table_name`;
```
增

insert into `table_name` (`course`, `teacher`, `price`) values(`C/C++`, `Mark`, 7500.0);

delete from `table_name` where id = 3;

update `table_name` set `teacher` = `Mark` where id = 2;
update `table_name` set `age` = `age`+1 where id = 2;

select `course`, `id` from table_name where `teacher` = `Mark`;

5. 高级查询

基础查询

select * from student;
select `sname`, `class_id` from student;
select `sname` as '姓名' , `class_id` as '班级ID' FROM student; // 起别名
select distinct `class_id` from student; // 把查询出来的结果的重复记录去掉

条件查询

select * from `student` where `gender` = `男` and `class_id` = 2;

范围查询

select * from `student` where `class_id` between 1 and 3;

判空查询

select * from `student` where `class_id` is not NULL;  // 判断不为空
select * from `student` where `class_id` is NULL;      // 判断为空
select * from `student` where `gender` <> '';          // 判断不为空字符串
select * from `student` where `gender` = '';           // 判断为空字符串

模糊查询

select * from `teacher` where `tname` like '_小%';

分页查询

select * from `student` limit 1,2;

查询后排序

select * from `score` order by `course_id` DESC, `num` ASC;  // asc:升序, desc:降序

6. 聚合查询

selcet sum(`num`) from `score`;     // 计算某列的总和
selcet avg(`num`) from `score`;     // 计算某列的平均值
selcet max(`num`) from `score`;     // 计算某列的最大值
selcet min(`num`) from `score`;     // 计算某列的最小值
selcet count(`num`) from `score`;   // 计算某列的行数

7. 分组查询

// 分组加group_concat
select `gender`, group_concat(`age`) as ages from `student` group by `gender`;
// 可以把查询出来的结果根据某个条件来分组显示
select `gender` from `student` group by `gender`;
// 分组加聚合
select `gender`, count(*) as num from `student` group by `gender`;
// 分组加条件
select `gender`, count(*) as num from `student` group by `gender` having num > 6;

8. 联表查询

在这里插入图片描述

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

select cid from `course` inner join `teacher` on course.teacher_id = teacher.tid;

left join：在内连接的基础上保留左表没有对应关系的记录

select cid from `course` left join `teacher` on course.teacher_id = teacher.tid;

right join：在内连接的基础上保留右表没有对应关系的记录

select cid from `course` right join `teacher` on course.teacher_id = teacher.tid;

9. 子查询/合并查询

单行子查询

select  * from course where teacher_id = (select tid from teacher where tname = '谢小二');

多行子查询
- in：检测结果集中是否存在某个特定的值
- exists：返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值，否则返回一个假值
- all：表示满足所有条件。使用 ALL 关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句
- any：只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件
- 在from子句中使用子查询：子查询出现在 from 子句中，这种情况下将子查询当做一个临时表使用

select * from stuent where class_id in (selcet cid from course where teacher_id = 2);

select * from student where exists(select cid from course where cid = 5);

selcet student_id, sname from 
(selcet * from score where course_id = 1 or course_id = 2) as A 
left join student on A.student_id = student.sid;

10. 视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。其内容由查询定义
用来创建视图的表叫做基表；通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成；
优点：
- 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件
- 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现
- 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

create view view_test as select  A.stuent_id from
(selcet 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 > if(isnull(B.num), 0, B.num);

作用：
- 可复用，减少重复语句书写；类似程序中函数的作用；
- 重构：
  - 假如因为某种需求，需要将 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;，则只需要更改数据库结构，而不需要更改应用程序；
- 逻辑更清晰，屏蔽查询细节，关注数据返回；
- 权限控制，某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作。