Mysql 基础常识篇mysql为什么使用数据库数据库实际应用简图表的关联关系 SQL分类 DDL data de

为什么使用数据库

数据持久化

数据存在可断电式存储设备中以供之后使用

数据库实际应用简图

表的关联关系

 一对一
 一对多
 多对多
 自我引用

SQL分类

DDL data definition language 定义例: create
DML data manipulation languages 操作例: insert CRUD
DCL data control language 控制例: grant commit rollback

select

显示表结构

describe table;

去重复行

select distinct department_id from employees;

排序

ASC 升序
DESC 降序

... order by salary asc , age desc;

分页

limit 位置偏移量 , 页大小;
limit (pageNo-1) * pageSize , pageSize;

... limit 0, 10;

多表查询

笛卡儿积
两表相连不加条件
a , b 所有组合可能

a1	a2
a	aa

b1	b2
1	2

组合后

a , 1	a , 2
aa .1	aa , 2

连接图

提示 full=> mysql 92 语法 mysql 不支持

连接案例

from employees e left/right join departments d on 连接条件

e 17 b 2 重复 1 求 e 中 16 无需与b重复

  select employees_id , department_name from employees e left jion departments d 
  on e.departments d on e.department_id = d.department_id where d.department_id is null;

union 去除重复 e 17 b 2 重复 1 -> 1+16+1
union all 不去重 e 17 b 2 重复 1 -> 1+1+1+16

优化小常识

多表查询每个字段前都指明其所在的表
超过三个表禁止 join (阿里手册)

函数

米.png

聚合函数

avg / sum (不包含 null 记录)
max / min
count (不包含 null 记录)

 select max(salary) from employees;

count(*) count(1) count(具体字段) 执行效率?

myism 存储引擎三者同

MyISAM把表的总行数单独记录下来
innodb 存储引擎 count(*)=count(1)>count(字段)

SELECT COUNT(*) FROM 语句执行过程，会自动选择一个成本较低的索引进行

group by 分组

select department_id, avg(salary), job_id from employees group by department_id , job_id;

having 数据过滤

过滤条件中使用聚合函数

group by department_id having max(salary) >10000;

SQL语句执行过程

sql 99 语法

 select ... (存在聚合函数) from ... (left/right) join ... on ... 
 where 不包含聚合条件函数的过滤条件
 group by ...
 having 包含聚合函数的过滤条件
 order by ... (ASC / DESC)
 limit ... , ...

sql 92 语法

from 表 , 表 where 连表条件

SQL 执行过程

from -> on ->(left/right) join ->where ->group by ->having -> select ->distinct -> order by ->limit

SQL 执行原理 每步都会产生->虚拟表 多表联查

1.通过CROSS JOIN 求笛卡儿积相当于得到虚拟表 vt 1-1 (virtual table)

2.通过on 进行筛选在虚拟表vt 1-1 的基础上进行筛选得到虚拟表 vt1-2

3.添加外部行如果使用左,右,全连接,就会涉及到外部行, 也就是在虚拟表vt1-2的基础上添加外部行,得到虚拟表vt1-3 (两个表以上重复操作) 得到原始数据最终虚拟表 vt1

4.进行 where阶段进行筛选过滤得到vt2

5.进行group和having阶段在vt2基础上进行分组和分组过滤,得到中间vt3和vt4

6.首先在select阶段会提取想要的字段,然后在distinct阶段过滤掉重复的行,分别得到中间表 vt5-1, vt5-2

7.按照指定的字段进行排序 order by阶段, 得到虚拟表vt6

8.取出指定行的记录 limit阶段, 得到最终的结果, 对应虚拟表vt7

子查询

基础案例

   主查询
   select last_name , salary from employees where salary > 
   子查询-> 在主查询之前一次执行完成
   (select salart from employees where last_name='Able')

case应用

显示员工 e_id last_name location , 其中若员工 d_id 与 location_id 为1800的d_id 相同则 location为 canada 其余为 USA

  select employee_id , last_name , 
  (case department_id when 
    (select department_id from departments where location_id = 1800)
    then 'Canada' else 'USA' END 
  ) location from employees;

多行子查询 (子查询 -> 返回结果多个)

in any all some(同any)

  where salary in / < all/any
  (select min (salary) from employees group by department_id);

from 中子查询

查询平均工资最低的部门id

mysql 聚合函数不可嵌套 orcale 可以

 select department_id from employees group by department_id having AVG(salary) =
  (
   select min(avg_sal) from (
      select avg (salary) avg_sal from employees group by department_id 
     ) t_dept_avg_sal
  );

  select dempartment_id from employees group by department_id having avg (salary) <=
   ALL (
        select avg(salary) avg_sal from employees group by department_id 
       )

exists / not exists

NOT EXISTS 的工作原理是，如果子查询的结果集为空，那么条件被视为真，否则条件被视为假。当子查询和主查询有关联条件时，相当于从主查询中去掉子查询的数据；

查询公司管理者的employee_ud ,last_name ,job_id ,department_id 信息

 select employee_id , last_name from employees e1 where exists 
 (
  select * from employees e2 where e1.employee_id =e2.manage_id
 )

-查询departments 表中 , 不存在于employees 表中的部门的department_id 和 department_name

  select department_id , department_name from departments d where not exists 
  (
   select * from employees e where d.department_id = e.department_id
  );

注意点

在select 中除group by 和 limit 之外都可以用子查询
mysql 聚合函数不可嵌套 orcale 可以

MySQL8.0 新特性计算列

某一列的值是通过别的列计算得来的

a->1 b->2 c(无需手动插入)->a+b

create table test (
 a int,
 b int,
 c int generated always as (a+b) virtual
);

数据类型概述

系统故障产生的成本远远超过增加几个字段存储空间所产生的成本

整数类型

名称	字节 1 bit->2^8	有符号取值范围	无符号
tinyint	1	-128~127	0~255

定点数类型

decimal(M,0) 字节数 M+2

decimal(5.2) -999.99~ 999.99

超出精度四舍五入 126.146->126.15

日期与时间类型

timestamp 便于计算

存储数据时需对当前时间所在的时区进行转换,查询数据的时候再将时间转换回当前的时区

datetime 直观取值范围大

只能反映出插入时当地的时区

文本字符串类型

char varchar

名称	作用	范围	占用空间
char(M)	固定长度	0<=M<=255 bytes	M字节(存储未满空格占用)
varchar(M)	可变长度	0<=M<=65535 bytes	实际+1 字节 (1 实际占用信息记录)

MySQL5.0以后 varchar(100) 100个字符 utf-8 1汉字 => 3字节 65535/3=21845

enum 类型

 create table test_name(
  season enum('春','夏','秋','冬','unknow')
 );

set 类型

 create table test_set (
  s set ('A','B','C')
 );

可存多个,去重复 A,B A,B,B=>A,B

 insert into test_set values ('A,B');

JSON类型

 create table test_json(
  js json;
 );
 
 insert into test_json values('{"name":"nick", "age": 18 }');
 
 select js-> '$.name' as name from test_json;

阿里手册

(强制)

任何字段如果为非负数必用 unsigned
小数类型 decimal 范围超出数据拆分整数+小数俩字段 float,double 存在精度损失
varchar 长度>5000 字节替换 text 独立出表来用主键对应避免影响其他字段索引效率

约束

数据完整性=> 1.数据精确性 2. 数据可靠性

约束是表级的强制规定字段限制

非空约束 not null
唯一约束 unique 允许多个空值
主键约束 => 唯一约束+非空约束一列/多列一表唯一主键

自增列 auto_increment

MySQL5.7 对于自增主键分配规则由InnoDB数据字典内部一个计数器(只在内存中维护) 决定

MySQL8.0 新特性持久化计算器持久化到重做日志中

foreign key 约束

父 id ->|相关联| 子 parent_id (系统会默认在所在列上建立对应的普通索引) 引用完整性

等级约束

cascade 方式父表update/delete同步update/delete掉子表的匹配记录
set null 方式父表update/delete 子匹配记录列设为null
no action/restrict 方式子表中有匹配记录父表不可对候选键进行update/delete操作

对外键约束,最好采用 : on update cascade on delete set null 方式

create table dept (
 did int primary key
);
create table emp (
 eid int primary key,
 dept_id int,
 foreign key (dept_id) peferences dept(did) on update cascade on delete set null
);

阿里手册(强制)

不得使用外键与级联,一切外键概念必须在应用层解决

说明:

学生s_id 主成绩 s_id 外更新学生表 s_id ,同时触发更新成绩表 s_id 即为级联

外键和级联更新适用于单机低并发不适合分布式,高并发集群

外键: 影响数据库的插入速度

级联更新: 强堵塞存在数据库更新风暴风险

check 约束

mysql 5.7 不支持有但是不起作用(不报错与警告)
mysql 8.0 可以使用

create table test10 (
  id int,
  last_name varchar(15),
  salary decimal(10,2) check (salary > 2000)
)

视图

视图是一种虚拟表存储起来的select语句

本身是不具有数据占用内存空间非常少

视图建立在已有表的基础上

   /* 
   精简语句
   create view 视图名称
   as 查询语句
   */
   
   create view vu_empl 
   as 
   select employee_id, last_name,salary
   from emps;

触发器

由事件来触发某个操作确保数据完整性

create trigger 触发器名称
{before | after} [insert | update | delete]
on 表名 for each row
触发器执行的语句块;

案例一

delimiter //
create trigger before_ins_tri
before insert on test_trigger
for each row
begin 
 insert into test_log(t_log) values('before insert ...');
end //
delimiter ;

进阶案例

insert 前检查新员工薪资是否大于领导,大于报错 'HY000' 从而使添加失败

-- New->新添加记录
delimiter //
create trigger before_ins_trii
before insert on employees
begin
declare myr_sal double;
select salary into mgr_sal from employees where employee_id = New.manage_id;
if New.salary > mgr_sal
   then signal sqlstate 'HY000'
   set message_text='薪资高于领导';
end if;
end //
delimiter ;

MYSQL8.0-窗口函数

窗口函数特点

可以分组,而且可以在分组内排序, 不会因分组而减少原表中的行数

序号函数

查询goods数据表中每个商品分类下价格降序排列的各个商品信息

select row number() over (partition by category_id order by price desc)
as row_num,id,g_name,price,category_id from goods;

数据目录

-- MySQL 数据存放在哪?
-- linux /var/lib/mysql
show variables like '%datadir%';

例数据库a,表 b

如果表b采用InnoDB,data\a中会产生1个或者2个文件:

b.frm: 描述表结构文件,字段长度等
如果采用系统表空间模式的,数据信息和索引信息都存储在ibdata1中
如果采用独立表空间存储模式,data\a 中还会产生b.ibd文件(存储数据信息和索引信息)

此外:

MYSQL5.7 中会在data/a 的目录下生成db.opt文件用于保存数据库的相关配置. 比如: 字符集,比较规则. 而MySQL8.0 不再提供db.opt文件
MySQL8.0 中不再单独提供b.frm, 而是合并在b.ibd文件中.

如果表b采用MYISAM data\a 会产生3个文件:

MySQL5.7中: b.frm: 描述表结构文件,字段长度等

MYSQL8.0中 b.xxx.sdi: 描述表结构文件,字段长度等
b.MYD(MYDATA) : 数据信息文件,存储数据信息(如果采用独立表存储模式)
b.MYI (MYINDEX) : 存放索引信息文件

常用的数据库对象

数据库常见对象.jpg

逻辑结构

SQL 语句在MySQL中流程

mysql执行流程.jpg

mysql5-7逻辑架构-解析版.jpg

ps:Mysql8.0 去除缓存模块

小工具 profiling -> 查看mysql执行过程

存储引擎

表的类型功能接收上层传下来的指令然后对表中的数据进行提取或写入操作

对比项	MyISAM	InnoDB
外键	x	√
事物	x	√
行表锁	表锁操作一条记录也会锁住整个表	行锁操作时只锁某一行不对其他行有影响适合高并发
关注点	性能: 节约资源,消耗少,简单业务	事务:并发写更大资源
5.5.8后默认使用	x	√

索引的数据结构

索引是帮助MYSQL高效获取数据的数据结构在存储引擎中实现的索引存储在外部磁盘上
磁盘的I/O操作次数对索引的使用效率至关重要

B+Tree形成过程

B-Tree

B-树图.jpg

B+Tree (存储能力) 与为什么选B+Tree 而不是 B-Tree 思考题

b+思考题.jpg

B+思考题-2.jpg

InnoDB 的索引

B+Tree

c1	c2	C3
主键	普通字段	普通字段

聚簇索引

聚簇索引.jpg

非聚簇索引

非聚簇索引.jpg

联合索引.jpg

ps: 联合索引为确保唯一性可主键+普通字段的方式形成

回表

根据c2列作为索引查询记录只能确定主键值,如果需要完整用户数据,仍然需要到聚簇索引中再查询一遍

唯一索引和普通索引查询效率

MyISAM 的索引

MyISAM的索引文件仅仅保存数据记录的地址
主键索引(key 唯一)和二级索引(key 可重复)
B+Tree data域保存数据记录的地址

索引检索算法为: 首先按照B+Tree搜索算法搜索索引,如果指定的key存在,则取data域的值,然后以data域的值为地址,读取相应数据记录

InnoDB 与 MyISAM 对比

InnoDB与MyISAM对比.jpg

索引的代价

空间上的代价

每建立一个索引都要为它建立一颗B+Tree,每颗B+Tree的每个节点都是一个数据页, 一个页默认会占用16KB的存储空间
时间上的代价

每次对表中的数据进行增,删,改操作时,都需要去修改各个B+Tree索引.

B+Tree每层节点都是按照索引列的值从小到大的顺序排列而组成的双向链表,

内节点的记录又都是按照索引列的值从小到大的顺序而形成的单向链表.

增,删,改操作时可能会对节点和记录的排序造成破坏,存储引擎需要额外的时间进行一些记录移位,页面分裂,页面回收等操作来维护好节点和记录的排序

InnoDB 数据存储结构

ps: 区是物理上连续地址页是双向链表连续起来的(不一定在物理结构相连)

页

页默认大小 16KB

数据库管理存储空间的基本单位
数据库I/O操作的最小单位

页大小

页结构概述

页a, 页b ,页c ... 这些页可以不在物理结构上相连,只通过双向链表关联.

每个数据页中的记录会按主键值从小到大的顺序排序组成单向链表

每个数据页都会为存储在它里面的记录生成一个页目录 (二分查找快速定位)

页详细结构

校验和

页目录

页目录用来存储每组最后一条记录的地址偏移量

二分查找快速锁定目标

行格式

数据以行为单位向表中插入数据

查看默认行格式

SELECT @@innodb_default_row_format;

compact行格式

ibd字节码源码解析

可变字段长度列表: 字节码 02 => varchar(8) "aa" 插入
null 值列表字段 c1 c2 c3 c4 => 值 a null null fff => 字节码 0 1 1 0 -> 06

行溢出

InnoDB 存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外

一页 16kb -> 16384 字节 varchar(M) max 65533字节

Mysql 基础常识篇