MySQL内容整理（1）

基本概念

**数据库：**数据存储的仓库 DB

**数据库管理系统：**操纵和管理数据库的大型软件 DBMS

**sql：**操作关系型数据库的编程语言

关系型数据库

建立在关系模型的基础上，有多张相互连接的二维表组成

使用表存储，格式统一，便于维护

使用sql，标准统一

SQL

DDL

对数据库，表进行操作

DML

对表中数据进行操作

DQL

查询数据库表中数据

编写顺序：

select
	字段列表
from
	表名
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

执行顺序：

from
where
group by
having
select
order by
limit

DCL

用来管理数据库用户，控制数据库的访问权限

主要是数据库管理员（运维）使用

-- 查询用户
use mysql
select * from user；
-- 创建用户
create user '用户名'@'主机名' identified by '密码'
-- 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by      '新密码';
-- 删除用户
drop user '用户名'@'主机名';

函数

select 函数（参数）

字符串函数

数值函数

日期函数

流程函数

约束

约束用于限制存储在表中的数据，作用于表中字段

案例：

多表查询

内连接

显式：

select 字段 from 表1，表2 where 条件

隐式：

select 字段 from 表1 [inner] join 表2 on 条件

外连接

select 字段 from 表1 left join 表2 on 条件   # 完全包含左表数据
select 字段 from 表1 right join 表2 on 条件

自连接

也可以外连接

select 字段 from 表1 别名1 join 表1 别名2 on 条件

联合查询

就是把多次查询的结果合并起来，形成一个新的查询结果集

select 字段 from 表A……
union all
select 字段 from 表B……
-- 如果要进行去重，把all去掉
-- 多张表列数，字段类型保持一致

子查询

sql语句中嵌套select 语句，外部语句可以是insert，update，delete，select

根据结果不同，分为：

• 标量子查询（子查询结果为单个值）常用：> = != <
• 列子查询（子查询结果为一列） in not in any some all
• 行子查询（子查询结果为一行）= != in not in
• 表子查询（子查询结果多行多列）常用：in

事务

把所有操作作为整体一起向系统提交或撤销操作请求，即这些操作童成功同失败

默认mysql事务自动提交

select @@autocommit;  # 1为自动提交  0 手动提交
set @@autocommit = 0; # 设置事务手动提交

commit;  # 提交
rollback; # 回滚


# 方式2
开启事务
提交事务
回滚事务

四大特性

• 原子性：事务是不可分割的最小单元，童成功同失败
• 一致性：事务完成时，数据状态必须一致
• 隔离性：多个事务之间，操作不受影响
• 持久性：事务一旦提交或回滚，对数据库的影响是永久的

并发事务问题

事务隔离级别

读未提交的

读已提交的

可重复读：mysql默认隔离级别，使两次相同sql语句中查询结果相同，不受并发事务影响

串行化：并发事务操作时，只允许一个事务执行，提交后才能让别的事务执行

性能逐渐减低，安全性逐渐升高

存储引擎

决定数据库数据存储，更新，查询，获取的方式

存储引擎是基于表的，不是基于库的

• 连接层
• 服务层
• 引擎层：索引在这层实现
• 存储层

在创建表时指定存储引擎

engine = innoDB

show engines 查看支持的存储引擎

InnoDB

高可靠性，高性能通用的存储引擎

支持事务

行级锁

外键

文件：xxx.ibd，xxx代表表名，InnoDB每张表都对应这样一个表空间文件，存储表的（frm，sdi），数据和索引

参数：innodb_file_per_table 开关默认打开，表示每张表都对应一个表空间文件

逻辑存储结构：

ibd文件属于表空间文件

其中行Row中存储的就是表中一行行的数据

MyISAM

mysql早期默认存储引擎

不支持事务，外键

支持表锁，不支持行锁

访问速度快

文件：

xxx.sdi：存储表结构信息

xxx.MYD：存储数据

xxx.MYI：存储索引

Memory

该引擎的表数据存储在内存中，由于受到硬件问题，断电问题，只能将这些表作为临时表或缓存表使用

内存存放，所以访问速度快

支持hash索引

文件：

数据放在内存中

sdi：存放表结构

InnoDB和MySAM三大区别

• InnoDB支持事务安全，MyISAM不支持
• InnoDB支持行锁，MyISAM支持表锁
• InnoDB支持外键，MyISAM不支持

……