MySql 的概念

sql 的分类

mysql 数据类型

• 支持多种类型，分为三类 数值 日期 字符串

DDL 操作数据库

数据库的说明

• mysql 存储里面最为核心的数据，权限，安全
• sys 存储的mysql里面系统信息
• performance_schema mysql 里面性能相关的信息
• information_schema 存储的是mysql里面库啊， 表啊的信息

查询数据库

show databases;

创建数据库

方法1: create database [数据库名] 方法2: create database if not exists [数据库名]

删除库

方法1: drop database [数据库名] 方法2: drop database if not exists [数据库名]

查看当前在使用的数据库

select database()

使用数据库

use [数据库名]

DDL 操作数据表

创建表

create table [表明] ( 字段名1 数据类型, 字段名2 数据类型, ... )

查询表

show tables

查询表的结构

desc [数据表]

修改表

修改表名 alter table [表名] rename to [新表名]

添加一列 alter table [表名] add [列名] [数据类型]

修改数据类型 alter table [表名] modify 列名 [新数据类型]

修改列名和数据类型 alter table [表名] change [列名] [新列名] [新数据类型]

删除列 alter table [表名] drop 列名

删除表

方法1: drop table [表名]

方法2: drop table if not exists [表名]

DML 对表中的数据进行增删改

添加数据

insert into [表名](列名1, 列名2,...) values(值1, 值2,...)

给全部列添加数据

insert into [表名] values(值1, 值2, ...)

批量添加数据

insert into [表名](列名1, 列名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...) insert into [表名] values(值1,值2,...),(值1,值2,...),(值1,值2,...),(值1,值2,...)

查询数据

select * from [表名]

修改数据

update [表名] set 列名1=值1，列名2=值2，...[where 条件]

删除数据

delete from [表名] [where 条件]

DQL 查询数据库中表的数据

查询数据

基础查询

查询多个字段 select [字段列表] from [表名]

去除重复记录 select disitinct [字段列表] from [表名]

起别名 as: as

排序查询

select [字段列表] from [表名] order by 排序字段名1 [排序方式1], 排序字段名2 [排序方式2]...

select * from stu order by math desc;
select * from stu order by math asc;

排序方法

• asc 升序排列（默认）
• desc： 降序排列

注意： 如果有多个排序规则， 前面条件一样时， 才会根据后面的第二规则排序

聚合函数

select [聚合函数] [列名] from [表]

注意： null 不参与所有聚合函数的运算

分组查询

select [字段列表] from [表名] [where 分组前条件限定] group by [分组字段名] [having 分组后过滤条件]

注意： 分组后，查询的字段为聚合函数和分组字段， 查询其他字段无意义

分页查询

select [字段列表] from [表名] limit [起始索引],查询条目数

计算公式： 起始索引=（当前页码-1）* 每页的显示条数

约束

• 约束时作用于表中列上的规则，用于限制加入表的数据
• 约束的存在保证了数据库中的数据的完整性，有效性和

约束的分类

demo

mysql 外建约束

• 表和表的数据有关联性的时候， 没有相关的约束， 无法保证数据的准确性

建表时添加外建约束

删除外建约束

建表后单独添加外建约束

外建级联更新 alter table [表名] add constraint [外建名] foreign key [本表列明] references [主表名] [列名] on update cascade 外建级联删除 alter table [表名] add constraint [外建名] foreign key [本表列明] references [主表名] [列名] on delete cascade

demo

删除唯一约束 alter table [表名] drop index [列名]

建表后单独添加唯一约束 alter table [表名] modify [列名] [数据类型] unique

非空约束 alter table [表名] modify [列名] [数据类型]

建表后单独添加非空约束 alter table [表名] modify [列名] [数据类型] not null

多表操作

表和表之间有一定的关联关系

• 一对一
• 一对多
• 多对多

一对一 （外建约束）

场景： 人和身份证

一对多

场景： 用户和订单 建表原则： 在多的一方建立外建约束，来关联一方的主键

多对多

场景： 学生和课程， 一个课程可以被多个学生选择，一个学生也可以选择多个课程

建表原则：需要借助第三方中间表，中间表至少包含中间两个列， 这两个列作为中间表的外建，分别关联到两张表的主键

多表查询

• 内连接查询
• 外连接查询
• 子查询
• 自关联查询

内连接 inner join

原理： 内连接查询的是两张表有交集部分的数据（有主外建关联的数据） 显示内连接 select [列名] from [表名1] inner join [表名2] on 条件 隐式内连接 select [列名] from [表名1] [表名2] where 条件

外链接查询

• 左外连接 查询原理： 查询的是左表的全部数据， 和左右两张表有交集部分的数据

select [列名] from [表名1] left outer join [表名2] on [条件]

• 右外连接 查询原理： 查询的是右表的全部数据， 和左右两张表有交集部分的数据

select [列名] from [表名1] right outer join [表名2] on [条件]

子查询

概念：在查询语句中嵌套了查询语句，称之为子查询

查询结果是单行单列 - 可以将查询结果作为另一条语句的查询条件，使用运算符判断，=> >= < <=等 select [列名] from [表名] where (select [列名] from [表名] where [条件])

查询结果是多行单列 - 可以作为条件， 使用运算符in 或者 not in 进行判断 select [列名] from [表名] where [列名] [not] in (select [列名] from [表名] where [条件])

查询结果是多行多列 - 查询结果可以作为一张虚拟表参与查询

select [列名] from [表名],(select [列名] from [表名]) where [条件]

自关联查询

概念： 在同一张表的数据有关联性，我们可以把这张表当成多个表来查询

视图

• 是一种虚拟存在的数据表，这个虚拟表并不再数据库中实际存在 作用： 将一些较为复杂的查询语句的结果，封装到一个虚拟表中，后期在有相同的需求时，直接查询改虚拟表

创建视图 create view [视图名称] (列名列表名称) as 查询语句

查询视图 select * from [视图名称]

修改视图数据 update [视图名称] set [列名=值] where [条件] 注意“ 修改视图中的数据，源表中的数据也会改变

修改视图结构 alter view [视图名称] (列名列表) as 查询语句

删除视图 drop view [if exists] [视图名称]

demo

数据库备份 和 恢复

备份 登录到mysql 服务器 输入mysqldump -u root -p 数据库名称 〉 文件保存路径

恢复 1. 登录mysql 数据库 2. 删除已备份的数据库 3. 重新创建名称相同的数据库 4. 使用该数据库 5. 导入文件执行： source 备份文件全路径

存储过程 和 函数介绍

• 存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合

优势：

1. 提高代码复用性
2. 减少数据在数据库和应用服务器之间的传输，提高效率
3. 较少代码层面的业务处理

存储过程和函数的区别 1. 存储函数必需有返回值 2. 存储过程可以没有返回值

存储过程的demo

查看数据库中的存储过程 select * from mysql.proc where db= "数据库名称"

删除存储过程 drop procedure[if exists] 存储过程名称

存储过程-变量 declare [变量名] [数据类型] default [默认值]

变量赋值方法1: set [变量名] = [变量值]

变量赋值方法2: select [列名] into [变量名] from [表名] where [条件]

demo

存储过程-if语句

存储过程- 参数传递

demo

存储过程-while循环

存储函数

触发器

• 可以在insert 、update、delete之前或之后触发并执行触发器中定义的sql语句
• 这种特性可以协助应用系统在数据库端确保数据的完整性，日志记录，数据校验等

创建触发器

CREATE TRIGGER trigger_name  
AFTER trigger_name_expression  
ON table_name  
FOR EACH ROW  
BEGIN  
  // 执行触发器的代码  
END;

CREATE TRIGGER my_trigger  
AFTER INSERT ON my_table  
FOR EACH ROW  
BEGIN  
  INSERT INTO my_log (id, timestamp) VALUES (NEW.id, NOW());  
END;

要设置MySQL触发器，您需要执行以下步骤：

确定要在哪个表上创建触发器。语法如下：

CREATE TRIGGER trigger_name
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
  ON table_name FOR EACH ROW
  trigger_body;

其中，trigger_name是您为触发器命名的名称，table_name是触发器将附加到的表名，BEFORE AFTER指示触发器应该在操作之前还是之后触发，INSERT UPDATE | DELETE则指示触发器应该在何种情况下触发。最后是trigger_body，它包含在触发器被触发时要执行的SQL语句。

例如，以下是一个简单的示例，它在customers表上创建了一个在每次插入操作之前运行的触发器：

CREATE TRIGGER my_trigger
  BEFORE INSERT
  ON customers FOR EACH ROW
  BEGIN
    -- 在此处编写要执行的SQL语句
  END;

编写触发器体。触发器体是在触发器被激活时执行的SQL语句。您可以在触发器体中编写任何标准SQL语句，例如SELECT、UPDATE、DELETE或INSERT。您还可以使用变量和条件语句等高级功能。

将触发器添加到数据库中。一旦您定义了触发器，就可以使用CREATE TRIGGER语句将其添加到MySQL数据库中。

测试触发器。为了测试触发器是否按预期工作，您可以手动执行一个INSERT、UPDATE或DELETE操作并验证触发器执行的SQL语句是否正确。

在编写MySQL触发器时，请确保仔细考虑所有可能的情况，并在实际上生效之前进行彻底的测试。

查看触发器 show triggers

删除触发器 drop trigger [触发器名字]

事务

• 一条或者多条语句组成的一个执行单元，特点：要么同时成功，要么同时失败
• 单元中每条sql 语句相互依赖， 形成一个整体
• 如果某条sql 失败，那么整个单元就会撤回到初始态
• 如果sql 都成功了， 那么事务就执行成功

开启事务 start transacition

回滚事务 rollback

提交事务 commit

事务的四大特征

1. 原子性 a: 原子性是指事务包含所有的操作，要么全部成功，要么全部失败 b: 事务的操作如果成功就要完全应用到数据库， 如果操作失败则不能对数据库有任何影响

2. 一致性 a: 一致性是指事务必需使数据库从一个一致性状态变换到另一个一致性状态 b： 也就是说一个事务执行之前和执行之后都必需处于一致性状态

3. 隔离性 a: 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库必需为每一个用户开启事务，不能被其他事务的操作所干扰，多个并发事务之间要互相隔离

4. 持久性 a: 持久性是指一个事务一旦被提交了，那么对数据库中的数据改变就是永久性，即便是在数据库系统运到问题也不会丢失提交事务的操作

事务隔离级别

引发的问题

总结

存储引擎

概念介绍 MySQL支持多种存储引擎，包括：

InnoDB：是MySQL的默认存储引擎，它提供了高效、可靠的事务处理和行级锁定功能，适用于大型数据库。
MyISAM：是一种较为简单的存储引擎，适用于对数据库性能要求不高的场景，如日志、缓存等。
MEMORY：是一种快速存储引擎，适用于临时数据存储，如游标、事务日志等。
CSV：是一种纯文本存储引擎，适用于存储结构化数据，如表结构、索引等。
NDBC：是一种基于行级锁定的存储引擎，适用于对并发性能要求较高的场景，如高并发、分布式系统等。
ARCHIVE：是一种归档存储引擎，适用于存储大量历史数据，如日志、审计等。
不同的存储引擎有不同的优缺点，选择存储引擎时需要根据具体的需求和场景进行权衡。

查询数据库支持的存储引擎 show engines

查询某个数据库中所有数据表存储引擎 show table status from [数据库名称]

查询某个数据库某个表的存储引擎 show table status from [数据库名称] where name ='数据表名称'

创建数据表，指定存储引擎 create Table [表名] (

)engine=[引擎名称]

引擎的选择

索引

介绍

MySQL 是一种关系型数据库管理系统，它支持创建索引来提高查询效率。索引是一种在表中存储的数据的有序列表，它可以加快查询速度，因为它可以减少需要扫描的数据量。

索引的分类

按照索引的作用分类：

主键索引：用于唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。 唯一索引：用于唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。 组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。 普通索引：在创建普通索引时，不附加任何限制条件，只是用于提高查询效率，这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。 按照索引的存储方式分类：

全文索引： 只有在 MyISAM 引擎上才能使用，只能在 CHAR、VARCHAR 和 TEXT 类型字段上使用全文索引。 普通索引： 在创建普通索引时，不附加任何限制条件，只是用于提高查询效率，这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。 组合索引： 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。 按照索引的键值范围分类：

单键索引： 只包含一个键值的索引，例如数字字段的 UNIQUE INDEX。 多键索引： 包含多个键值的索引，例如数字字段的 UNIQUE INDEX、全文字段的 FULLTEXT INDEX。 全文索引： 在表中的所有字段上创建的索引，包括数字字段、文本字段和二进制字段。 以上是索引的一些分类方式，不同的分类方式适用于不同的场景和数据类型。在创建索引时，需要根据具体的情况选择合适的索引类型，并确保选择的列是唯一的，不包含重复的值。

索引关键字

PRIMARY：主键索引。
UNIQUE：唯一索引。
INDEX：普通索引。
KEY：普通索引。
PRIMARY KEY：主键索引。
UNIQUE KEY：唯一索引。
CHECK：检索索引。
REFERENCES：外键。

索引操作

查询索引
show index form [表名]

添加索引

删除索引

索引原理

BTree 原理

B + Tree 原理

索引的设计原则

锁

锁的介绍，分类

共享锁

排他锁：

MyIsAm 读锁

MyIsAm 写锁

悲观锁，乐观锁

MyCat

安装使用mycat