MySQL 基础笔记1、数据库概述2、MySQL 常用命令3、DQL语句（查询语言）3.1、简单查询3.2、条件查询3.3、模糊查询3.4、排序查询3.5、单行处理函数3.6、多行处理函数 (聚合函数)3.7、分组查询3.8、连接查询3.9、子查询3.10、常用关键字3.11、SQL 执行顺序4、DDL语句（定义语言）4.1、MySQL 数据类型4.2、表操作5、DML 语句（操作语言）5.1、数据插入（insert语句）5.2、数据修改（update语句）7.3、数据删除（delete语句）6、约束（constraint）7、存储引擎8、事务8.1、事务实现10.2、事务的特性10.3、事务的隔离级别11、索引 index（重点）11.1、索引的实现原理11.2、索引的创建和删除11.3、索引失效11.4、索引的分类12、视图view12.1、创建和删除视图12.2、视图的作用13、DBA命令

MySQL 基础笔记

1、数据库概述

• 数据库（ DataBase ）：按照一定的格式来储存数据的一些文件的组合，就是储存数据的仓库。
  
• 数据库管理系统（ DataBaseMangement ）：是专门用来管理数据库中的数据的，可以对数据库中的数据进行增删改查。常见数据库管理系统：MySQL、Oracle、MS等。
  
• SQL：结构化语言，通过SQL语句来实现对数据库中的数据进行增删改查。
  

数据库管理系统 --->  执行--->SQL--->操作--->数据库

MySQL数据库的官网

SQL 语句分类：

• DQL：数据查询语言，凡是带有select关键字的都是查询语句。
  
• DML：数据操作语言，凡是对数据库的增、删、改。insert、delete、update。
  
• DDL：数据定义语言，主要操作表的结构，凡是带有create、drop、alter。
  
• TCL：事务控制语言，包括事务提交（commit）、事务回滚（rollback）。
  
• DCL：数据控制语言，包括授权和撤销权限。
  

2、MySQL 常用命令

# windows启动、停止命令(可能需要管理员权限)

net stop/start MySQL服务名称

# 命令登陆到MySQL

mysql -u[登陆用户名] -p[登陆密码]

# 退出MySQL

exit

# 展示数据库列表(要分号结尾)

show databases;

# 使用某个数据库

use [数据库名称]

# 创建数据库

create database [数据库名]

# 查看数据库下的表列表

show tables;

# 导入某个SQL资源，路径使用绝对路径，路劲不能存在中文

source 资源的路径;

# 查看表结构

desc [表名];

# 查看数据库版本号

select version();

HeidiSQL 数据库管理工具：一般不会再命令行中去创建数据库、表等操作，而是在数据库管理工具中进行操作。

下载地址：https://www.heidisql.com/download.php

3、DQL语句（查询语言）

注意：查询语句只会查询数据，永远不会修改表中的内容。

3.1、简单查询

查询字段：

# 查询一个或多个字段，多个字段名使用英文逗号隔开，字段一定要在表中存在，如果不存在可以使用默认值

select 字段1,字段2,默认值 from 表;

 

# 查询所有字段(方式一)

select 所有字段 from 表;

# (方式二：*会自动解析，解析成所有字段会消耗性能，不推荐)

select*from 表;

起别名：查询字段名可能不是自己想要的，可以给查询出来的字段取一个别名。

# 起别名，方式一：as

select 字段 as 别名 from 表;

# 方式二：as 可以省略

select 字段 别名 from 表;

如果你取的别名中也存在空格或者是中文，那必须使用单引号把别名括起来。

表达式：字段在查询的时候可以使用数学表达式去计算查询后的值。

# 数学表达式，查询出来的值会乘12

select 字段 *12from 表;

# 这样查询出来的字段名会是 (字段 * 12)，一般会重新起一个别名

select 字段*12as 别名 from 表;

3.2、条件查询

条件查询：查询符合条件的数据，不符合条件的就不查询出来，通过 where 添加条件。

语法：select 字段 from 表 where 条件表达式;

# 等于 =

select 字段 from 表 where 字段 = 条件值;

# 不等于 <>或!=

select 字段 from 表 where 字段 <> 条件值;

select 字段 from 表 where 字段 != 条件值;

# 小于 <, 小于等于 <=, 大于 >, 大于等于>=

select 字段 from 表 where 字段 < 条件值;

select 字段 from 表 where 字段 <= 条件值;

select 字段 from 表 where 字段 > 条件值;

select 字段 from 表 where 字段 >= 条件值;

# 在.....之间，between ... and ...，左小又大，相当于大于等于和小于等于一起使用

select 字段 from 表 where 字段 betwee 条件值1 and 条件值2;

# 字段值为 null，不能使用 字段 = null

select 字段 from 表 where 字段 isnull;

# 字段值不为 null

select 字段 from 表 where 字段 isnotnull;

# 并且 and(多个条件同时满足)，或者 or(多个条件有一个满足)，and的优先级比or的优先级高

select 字段 from 表 where 字段1 = 条件值1 and 字段2 = 条件值2;

select 字段 from 表 where 字段1 = 条件值1 or 字段2 = 条件值2;

# in，包含，相当于使用多个or，数据值是否在in这个集合中，多个匹配数据使用逗号隔开

select 字段 from 表 where 字段 in(值1,值2,值3);

# not in，不包含，查询数据值不在in这个集合中的数据

select 字段 from 表 where 字段 notin(值1,值2,值3);

3.3、模糊查询

模糊查询：使用 like 去匹配模糊查询，可以查询匹配的结果。

• %：匹配多个任意字符，例如：%三，表示以三结尾的数据。
  
• -：匹配任意一个字符，例如：_三，表示任意一个字符开头，三结尾的数据，数据只有两个字符。
  

语法：select 字段 from 表 where 字段 like '%张%';

# 左模糊查询

select 字段 from 表 where 字段 like'%张';

# 右模糊查询

select 字段 from 表 where 字段 like'张%';

注意：在查询时，遇到特殊字符，例如 _ 和 % 需要使用转义字符来转义。

例如：select 字段名 from 表名 where 字段名 like '%\_%';

3.4、排序查询

排序查询：在查询数据时，可以按照某个字段查询后的数据进行排序。

# 默认排序，默认是升序排序

select 字段 from 表 orderby 字段;

# 降序排序，在后面添加desc，asc是升序排列

select 字段 from 表 orderby 字段 desc

多个字段排序：可以按照过个字段进行排序，当某个字段相等时这时可以按照另一个字段进行排序。

select 字段 from 表 orderby 字段1 asc, 字段2 desc;

上面表示先按照字段1进行升序排列，如果遇到字段1的值相等的时候，在按照字段2的值进行降序排列。前面的字段起主导作用。

字段位置排序：选择表中对应位置字段进行排序。

# 按照表中排第二的字段进行排序，但是在实际开发中不建议使用该方法，可能表中的字段位置可能发生变化

select 字段 from 表 order by 2;

拓展：

select

字段

from

表名

where

条件表达式

order by

字段

上面的执行顺序是

from --》where ---》 select ---》order by（排序总是在最后执行的）

3.5、单行处理函数

单行处理函数：一条数据输入，对应一条数据输出，一般会给查询出来的字段取一个别名。

# 英文字符串转小写，lower

select lower(字段) as 别名 from 表;

# 英文字符串转大写，upper

select upper(字段) as 别名 from 表;

# 截取子串，下标从1开始，substr

select substr(字段,起始下标,截取的长度) from 表;

# 拼接字符串，可以将查询的字符串拼接，也可以在条件中拼接，concat

select concat(字段1,字段2) as 别名 from 表;

select 字段 from 表 where 字段 like concat('%','张','%');

# 取长度，查询出字段对应数据的长度，length

select length(字段) as 别名 from 表;

# 去除数据前后的空格，trim

select 字段 from 表 where 字段 = trim(值);

# 四舍五入，round，例如：round(123.78,1) --> 123.8，保留位数可以是负数，例如：round(123.78,-1)-->120

select round(字段,保留位数) from 表;

# 产生随机数，产生的是大于0小于1的一个小数，rand

select rand()*100 from 表名;

# 空数据的处理，ifnull

select if(字段,如果为空的默认值) from 表;

# 当...时候怎样...时候怎样...其它怎样...结束，case...when...then...when...then...end

select (case 字段 when '值1' then 操作1 when '值2' then 操作2 else 操作3 end) as 别名 from 表名;

格式化日期：可以将字符串按照一定格式格式化成日期类型。

MySQL 中日期格式：默认格式：%Y-%m-%d

• %Y：表示年
  
• %m：表示月
  
• %d：表示天
  
• %h：表示时
  
• %i：表示分
  
• %s：表示秒
  

# 字符串转换成date日期，str_to_date

insert into 表名

(日期字段)

values

(str_to_date('01-11-1999','%d-%m-%Y'));

 

# 日期按照指定格式转换成字符串，date_format

select date_format(字段,日期格式) from 表;

3.6、多行处理函数 (聚合函数)

聚合函数：输入一组数据，最终输出一行数据，多组数据有多条结果，相当于数据的统计。

注意：分组函数在使用时需要进行分组，然后才能使用，如果没有分组，默认整张表就是一组。

# 最大值/最小值，max/min

select max(字段)/min(字段) as 别名 from 表;

# 求和，sum

select sum(字段) as 别名 from 表;

# 求平均值，avg

select avg(字段) as 别名 from 表;

# 统计数据的多少，count

select count(字段) as 别名 from 表;

注意事项：

1. 聚合函数自动忽略 null 值得数据记录，不需要对 null 的数据处理。
   
2. count(*) 函数表示统计表中所有数据的条数，而 count(字段) 会自动忽略 null 值得数据。
   
3. 聚合函数不能使用在 where 语句中。因为聚合函数使用前必须先进行分组，但是 where 语句得执行顺序比分组 group by 先执行。
   
4. 所有得聚合函数可以组合起来一起使用。
   

3.7、分组查询

分组查询：将数据按照一定字段进行分组，然后对每组数据进行处理。

语法：select 字段,sum(字段) from group by 字段;

注意：

1. 分组函数后的 select 只能查询到参与分组的字段和使用分组函数得到的值。如果存在其它字段在其它数据库中可能会报错。
   
2. 分组函数可以对多个字段进行分组查询，多个字段使用英文逗号隔开。
   

having 关键字：用于对分完组的数据进行筛选，和group by联合使用，不能单独使用，也不能代替where。能用where就用where，where不行的在使用having。

select 字段 from 表 where 条件 group by 字段 having 条件;

3.8、连接查询

连接查询：两张表或多张表联合起来查询数据称为连接查询。

分类：

• 内连接：两张表之间没有主次之分，没有匹配就不显示。

1. 等值连接：表与表连接的条件时某个字段的值相等。
   
2. 非等值连接：表与表连接的条件时某个字段的值不相等。
   
3. 自连接：一张表自己与自己连接查询。
   

• 外连接：

1. 左外连接：左边表为主表，显示左表所有数据，右表没有匹配的查询字段显示为 null。
   
2. 右外连接：右边表为主表，显示中的所有数据，没有匹配的也会显示。
   

• 全连接：两张表都为主表，两张表的所有数据都会展示出来，不匹配的字段显示为 null。
  

笛卡尔积现象：当两张或多张表进行连接查询时，没有任何的条件限制，最终的查询结果条数是两张表条数的乘积，这个现象称为笛卡尔积现象。例如：select 字段 from 表1,表2; ---> 查询的数据就是表1数据的条数乘以表2数据的条数。

如何避免笛卡尔积？ 在进行多表联查的时候，对查询的条件进行加限制。

# 虽然避免了笛卡尔积现象，但是表之间的比配次数还是没有减少，就是表1和表2数据记录数的乘积。

select 字段 from 表1,表2 where 表1.字段 = 表2.字段;

注意：表的连接次数越多，查询的效率越低，尽量避免表之间的连接。

内连接

# inner 可省略默认是内连接

select 字段 from 表1

inner join 表2

on 连接的表达式 where 条件

# 连接的表达式是一个值相等关系就是等值连接，如果是一个不相等关系就是一个非等值连接。

外连接：

# outer可省略

select 字段 from 表1

left outer join 表2

on 条件表达式 where 条件

# 左外连接和右外连接是可以相互转换的，只是表的位置不同，更具需求来写`sql`语句。

注意：

• 一条sql语句中，内连接和外联接可以同时存在。
  
• 外连接查询的数据数，一定比内连接查询的数据数多。
  

3.9、子查询

子查询：指 select 语句中嵌套 select 语句，被嵌套的select语句称为子查询。

where 中子查询：在 where 的后面出现 select 子查询，一般子查询的结果作为 where 的条件判断依据。

select 字段 from 表

where (select 字段 from 表)

例如：

# 只有当子查询出来的结果条数大于3时，主查询才会满足条件

select distinct name from user u1 where (select count(*) from user u2 where u1.name=u2.name)>3

这样的子查询的效率很低，因为主查询每查询一条数据，子查询都会去执行一次，导致效率很低。

from 中子查询：把 from 中的子查询的结果当成一张临时的表提供给主查询进行查询。

select 字段 from (select 字段 from 表名) as 临时表的别名

where 条件表达式

注意：临时表必须起别名，不然无法在 where 语句中使用表。临时表中的字段也是要取别名的。

3.10、常用关键字

distinct ：使用 distinct 关键字去除查询的重复数据。

# 按照指定字段进行去重

select distinct 字段 from 表;

注意：distinct 只能使用在 select 的后面，字段的前面。如果 distinct 后面有多个字段，则是多个字段的联合，然后去除重复的数据。

不能这样写 select 字段1,distinct 字段2 from 表名;

union：用于将查询的结构集合并起来，称为一个结果集。

# 表示会把从表1查询到的数据和从表2查询的结果合并为一个结果集，然后显示出来

select 字段 from 表 where 条件表达式

union

select 字段 from 表 where 条件表达式

注意：

• 使用 union 两个的查询的字段数目必须保持一样，不然会报错。
  
• union 的查询效率较高，在进行联表查询时，相当于将两个表各自查询一遍，不会像连接查询那样匹配符合条件的数据。
  

limit：用于限制查询出来数据的条数，一般用于分页查询。limit 后面需要指定数据开始的下标和查询的长度。它的下标是从0开始的。

-- 查询下标开始后面指定长度的数据条数

select 字段 from 表名 limit 下标,长度;

分页查询：

• 实现分页查询，一般是从前端传入一个页码和每一页的数据条数，然后执行相应的sql语句。
  
• limit 的下标公式：下标 = (页码 - 1) * 每页条数，每页条数一般就是作为长度。
  

3.11、SQL 执行顺序

SQL 执行顺序：在 SQL 语句中有自己的执行顺序，理解执行顺序才能写出正确的 SQL 语句。

# 下面是一句较完整的 SQL 语句

select ... from ... where ... group by ... having ... order by;

执行顺序：

• 首先执行 from，先知道从哪个表中获取数据。
  
• 再执行 where，过滤掉表中不符合条件的数据。
  
• 再执行 group by，给过滤好的数据进行分组，没有指定默认是所有的数据为一组。
  
• 再执行 having，having 必须和 group by 同时使用，是将分组的数据进行再次过滤。
  
• 再执行 select，选取出满足条件的所有数据。
  
• 最后执行 order by，将查询的数据按照指定字段进行排序。
  

4、DDL语句（定义语言）

4.1、MySQL 数据类型

基本数据类型：

• varchar：可变长度的字符串，根据实际的数据来动态分配空间，节省空间，最长255。
  
• char：定长的字符串，不管实际长度多少，都会分配固定的长度来储存数据，可能会导致空间的浪费，最长255。
  
• int：数字中的整型，等同于Java中的 int，最长11。
  
• bigint：数字中的长整型，等同于Java中的 long。
  
• float：单精度的浮点型数据。
  
• double：双精度的浮点型数据。
  
• date：短日期类型，日期默认格式：%Y-%m-%d。
  
• datetime：长日期类型，日期默认格式：%Y-%m-%d %h:%i:%s。
  
• clob：字符大对象，最多存储4G的字符串，超过255的字符串使用该类型来存储，通常存储一篇文章、说明等。
  
• blob：二进制大对象，专门用来储存图片、声音、视频等，往该类型的字段插入数据的时候通常使用IO流来完成。
  

注意：当数据为固定长度的时候使用char，这样的速度较快，不用动态分配空间。当数据为可变长度的时候，使用varchar这样可以避免空间的浪费。

now() 函数：用户获取当前系统时间，默认时datetime类型。

insert into 表 (当前系统时间字段)

values (now());

4.2、表操作

创建表：

create table 表(

字段1 数据类型,

   字段2 数据类型,

   字段3 数据类型

);

注意：最后一个字段的数据类型的后面不能有逗号，否则报错。建议表名以 t_ 开头，字段名见名知意，这样可读性较强。

复制表：复制表的结构，也可以将表中的数据查询出来，把数据也复制到新表

create table 新表名 as select 复制字段 from 源表;

删除表：

delete：删除表中的数据，如果不添加条件限制则是删除表中所有数据，这种删除会把删除日志记录，支持事务回滚操作。

delete from if exists 表

drop：删除表的数据和结构，将表所占用的空间全释放掉，无法进行事务回滚操作。

# 如果表不存在就会报错

drop table 表;

# 这样可以解决，如果表不存在就会自动结束

drop table if exists 表;

truncate：用于清空表中的所有数据，会保留表的结构、属性、索引等，truncate 会重置自增列的值。执行后立即生效，数据无法回滚删除效率高。

truncate table 表;

5、DML 语句（操作语言）

5.1、数据插入（insert语句）

insert：用于向 MySQL 数据库中添加数据。

insert into 表 (字段1,字段2,字段3) values (值1,值2,值3);

注意：

• 字段的顺序可以和表中的字段顺序不一样，但是值得顺序要和前面指定得顺序对应。
  
• 如果缺少一些表中得字段，那些缺少得字段则会为默认值，如果没有设置默认值，则指为null。
  
• 如果所有得字段都不写，默认是插入全部得字段，后面得值要全部填上。
  

默认值：在创建表得时候给字段指定默认值。

create table 表(

字段1 数据类型,

   字段2 数据类型 default 默认值,

   字段3 数据类型

);

批量插入：向数据库中批量插入多条数据。

insert into 表 (字段)

values

(一条数据),

(两条数据),

(三条数据);

查询插入：通过 SQL 查询出来的结果插入到指定表中。

insert into 表 select 字段 from 查询表;

注意：查询结果的字段和插入表的字段需要保持一致。不然报错！

5.2、数据修改（update语句）

数据修改：通过 SQL 语句去修改表中指定的数据。

update 表 set 字段1=值1,字段2=值2

where 条件表达式;

注意：在修改时没有条件来限制的的话，它会修改表中的全部数据。

7.3、数据删除（delete语句）

数据删除：通过 SQL 语句删除表中的数据。

delete from 表

where 条件表达式;

注意：如果没有条件表达式，则会删除表中的所有数据，可以用于清空表中的数据。

delete from 表;

注意：

• 只是删除表中的数据，表的结构没有删除，例如索引、约束等，可以重置自增列的值。
  
• 支持事务回滚，删除后可以后悔，但是效率低下。
  

6、约束（constraint）

约束：创建表时，可以给字段添加一些约束，在插入数据时必须满足这些约束，不然不能插入成功以此来保证数据完整性、有效性。

常见约束：非空约束（not null）、唯一性约束（unique）、主键约束（primary key）pk、外键约束（foreign key）fk、检查约束（check）mysql不支持，oracle支持。

非空约束（not null）：添加约束后，插入数据字段值不能为空，如果不设置非空约束，默认数据库为空是 null。

create table 表(

字段1 数据类型 not null,

   字段2 数据类型,

   字段3 数据类型

);

唯一性约束(unique)：唯一性约束的字段的值不能重复，但是可以为 null ，多个 null 可以重复。插入非空重复的数据会报错。

create table 表(

字段1 数据类型 unique,

   字段2 数据类型,

   字段3 数据类型

);

联合唯一：多个字段可以联合起来作为一个唯一约束，当联合字段都一样时就不能插入，有一个不样就可以插入。

create table 表(

字段1 数据类型,

   字段2 数据类型,

   字段3 数据类型

   unique(字段1,字段2)

);

主键约束(primary key)：主键约束表示字段数据不能为空同时所有数据必须唯一，任何一张表都必须要有一个主键。

create table 表(

字段1 数据类型 primary key,

   字段2 数据类型,

   字段3 数据类型

);

联合主键：多个字段联合起来作为联合主键，联合主键所有字段都相同时不能插入，有一个不同时可以插入。

create table 表名(

字段1 数据类型,

   字段2 数据类型,

   字段3 数据类型

   primary key(字段1,字段2)

);

注意：

• 任何表，主键约束只能存在一个，不能存在多个，联合主键是将多个字段联合起来作为一个主键。
  
• 建议主键值的类型为 int、bigint、char，不建议使用varchar，因为主键一般为定长的。
  
• 主键可分为 自然主键 和 业务主键。在实际开发过程中，通常使用 自然主键 作为一个表的主键，主键主要防止数据重复，如果使用业务主键，一旦业务发生变化，主键值也需要发生改变。
  

自增约束：当字段是 int 、bigint 时，添加自增约束，插入数据时可以不用查询这个字段值，默认会递增方式填充。

create table 表(

字段1 int auto_increment,

   字段2 数据类型,

   字段3 数据类型

);

外键约束(foreign key)：两个表之间，子表引用父表中指定字段的数据，只能使用这些数据不能使用其它的数据。

t_class 表：

create table if not exists t_class(

   c_no int(11) primary key, // 被引用的字段

   c_name varchar(255)

);

t_student 表：

create table if not exists t_student(

stu_no int(11) primary key,

   stu_name varchar(255),

   c_no int, // 被设置外键的字段

foreign key (c_no) references t_class(c_no)  // 这是设置外键的语法

);

两个表，t_class(父表)是班级表，t_student(子表)是学生表，子表中创建一个外键 c_no，引用父表中的 c_no 字段。在子表中这个字段值只能是父表中对应字段的值。

注意：

1. 创建表时，要先创建父表，然后创建子表。
   
2. 删除表时，要先删除子表，然后删除父表。
   
3. 删除数据时，要删除子表中的数据，然后才能删除父表中的数据。
   
4. 插入数据时，要插入父表中的数据，然后才能在子表中插入数据。
   
5. 外键的值可以为空，但父表中字段值必须是唯一的。
   

7、存储引擎

存储引擎：是mysql中的特有的术语，在其它数据库中没有。实际上存储引擎就是一个表存储数据的方式，不同存储引擎表数据的存储方式不一样。

# ENGINE=InnoDB 指定表的存储引擎

CREATE TABLE `books` (

 `bookId` int NOT NULL AUTO_INCREMENT COMMENT '书的id',

 `bookName` varchar(100) NOT NULL COMMENT '书名',

 `bookCounts` int NOT NULL COMMENT '数量',

 `detail` varchar(200) NOT NULL COMMENT '描述',

 PRIMARY KEY (`bookId`),

 KEY `bookID` (`bookId`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb3

# 查看 MySQL 的存储引擎

show engines;

存储引擎：MySQL 常使用的三个存储引擎，InnoDB、MyISAM、MEMORY。

• MyISAM：它可以转换为压缩、只读表来节省空间。但是不支持事务，安全性低。它使用三个文件夹来表示每个表。

1. 格式文件：存储表结构的定义（mytable.frm）
   
2. 数据文件：存储表的数据（mytable.MYD）
   
3. 索引文件：存储表的索引（mytable.MYI）
   

• InnoDB：安全性高，但是性能低。

1. 每一个数据库表在数据库目录中以 .frm 格式表示。
   
2. 支持事务
   
3. 在mysql服务器崩溃时，提供自动恢复。
   

• MEMORY：内存存储引擎，数据存储在内存中，且行的长度固定。它的查询效率是最高的，不需要和硬盘交互，但是不安全，关机后就消失了。

1. 每个表均以 .frm 格式表示。
   
2. 表的数据及索引全部存放在内存中，断电消失。
   
3. 不能包含 blob 类型，text 类型，因为太大。
   

8、事务

事务：指的就是一个完整的业务，一个最小的工作单位不可在分。事务要么成功，要么就失败，一般只有 insert、update、delete 涉及数据的修改会有事务出现。例如：两个账户转账，修改两个账户数据，一个增加一个减少，这就需要一个事务去支持。

8.1、事务实现

事务流程：开启事务 ---> 执行DML语句（包括多条的insert、update、delete）---> 事务结束(事务提交、事务回滚)。再执行事务的过程中，每一条的DML语句都会记录到事务性活动的日志文件中。在执行的过程中，我们可以提交事务，也可以回滚事务。

• 事务提交 ：清空事务性活动的日志，将数据全部彻底持久化到数据库表中。提交事务标志着事务以成功结束。
  
• 事务回滚：将之前的DML语句全部撤销，清空事务性活动日志文件，标志着事务以失败结束。
  

怎么提交事务和回滚事务：

提交事务：使用命令       commit;

回滚事务：使用命令        rollback;    （回滚事务只能回滚到上次提交事务的位置）

开启事务：使用命令        start transaction;

在mysql中事务的默认是自动提交事务的，就是执行一条DML语句就会自动提交一次事务。但是在实际的开发中需要多条的DML语句来完成一件事情，所以不能使用mysql中的默认事务机制。

开启手动提交机制：

开启事务后，就开启了手动提交事务的机制。

事务回滚演示：

mysql> select *from books;    // 先查询books表中的数据

+--------+--------------------+------------+------------------------+

| bookId | bookName           | bookCounts | detail                 |

+--------+--------------------+------------+------------------------+

|      1 | Java               |         25 | 从入门到放弃           |

|      2 | MySQL              |         25 | 从删库到跑路           |

|      3 | Linux              |          5 | 从进门到进牢           |

|     24 | HTML基础教程       |         12 | 叫你如何入门web前端    |

|     25 | JavaScript基础教程 |         23 | 叫你如何入门JavaScript |

+--------+--------------------+------------+------------------------+

5 rows in set (0.00 sec)

mysql> start transaction;   // 开启事务，手动进行提交和回滚事务

Query OK, 0 rows affected (0.00 sec)

mysql> delete from books where bookId = 25;  // 删除表中bookId为25的书

Query OK, 1 row affected (0.01 sec)

mysql> select *from books;  // 删除后的数据

+--------+--------------+------------+---------------------+

| bookId | bookName     | bookCounts | detail              |

+--------+--------------+------------+---------------------+

|      1 | Java         |         25 | 从入门到放弃        |

|      2 | MySQL        |         25 | 从删库到跑路        |

|      3 | Linux        |          5 | 从进门到进牢        |

|     24 | HTML基础教程 |         12 | 叫你如何入门web前端 |

+--------+--------------+------------+---------------------+

4 rows in set (0.01 sec)

mysql> rollback;        // 事务回滚

Query OK, 0 rows affected (0.01 sec)

mysql> select *from books;    // 事务回滚后，被删除的数据就恢复了

+--------+--------------------+------------+------------------------+

| bookId | bookName           | bookCounts | detail                 |

+--------+--------------------+------------+------------------------+

|      1 | Java               |         25 | 从入门到放弃           |

|      2 | MySQL              |         25 | 从删库到跑路           |

|      3 | Linux              |          5 | 从进门到进牢           |

|     24 | HTML基础教程       |         12 | 叫你如何入门web前端    |

|     25 | JavaScript基础教程 |         23 | 叫你如何入门JavaScript |

+--------+--------------------+------------+------------------------+

5 rows in set (0.00 sec)

事务提交演示：

mysql> select *from books;     // 先查询表中的数据

+--------+--------------------+------------+------------------------+

| bookId | bookName           | bookCounts | detail                 |

+--------+--------------------+------------+------------------------+

|      1 | Java               |         25 | 从入门到放弃           |

|      2 | MySQL              |         25 | 从删库到跑路           |

|      3 | Linux              |          5 | 从进门到进牢           |

|     24 | HTML基础教程       |         12 | 叫你如何入门web前端    |

|     25 | JavaScript基础教程 |         23 | 叫你如何入门JavaScript |

+--------+--------------------+------------+------------------------+

5 rows in set (0.00 sec)

mysql> start transcation;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'transcation' at line 1

mysql> start transaction;         // 开启事务

Query OK, 0 rows affected (0.00 sec)

mysql> delete from books where bookId = 25;   // 删除一本书

Query OK, 1 row affected (0.01 sec)

mysql> commit;                    // 然后提交事务

Query OK, 0 rows affected (0.01 sec)

mysql> select *from books;       // 删除后查询书中的数据

+--------+--------------+------------+---------------------+

| bookId | bookName     | bookCounts | detail              |

+--------+--------------+------------+---------------------+

|      1 | Java         |         25 | 从入门到放弃        |

|      2 | MySQL        |         25 | 从删库到跑路        |

|      3 | Linux        |          5 | 从进门到进牢        |

|     24 | HTML基础教程 |         12 | 叫你如何入门web前端 |

+--------+--------------+------------+---------------------+

4 rows in set (0.01 sec)

mysql> rollback;           // 事务回滚到最后一次的事务提交，也就是上面的事务提交

Query OK, 0 rows affected (0.00 sec)

mysql> select *from books;    // 所以被删除的内容不能恢复

+--------+--------------+------------+---------------------+

| bookId | bookName     | bookCounts | detail              |

+--------+--------------+------------+---------------------+

|      1 | Java         |         25 | 从入门到放弃        |

|      2 | MySQL        |         25 | 从删库到跑路        |

|      3 | Linux        |          5 | 从进门到进牢        |

|     24 | HTML基础教程 |         12 | 叫你如何入门web前端 |

+--------+--------------+------------+---------------------+

4 rows in set (0.00 sec)

10.2、事务的特性

• 原子性（A）：
  事务是最小的工作单元，不能再分。
  
• 一致性（C）：
  所有的事务要求，在同一个事务中，所有的操作必须同时成功或同时失败。
  
• 隔离性（I）：重点
  a事务和b事务之间有一定的隔离，a和b同时操作一张表时，会有线程安全问题。
  
• 持久性（D）：
  事务结束的最终保障是事务提交，就相当于将没有保存到硬盘中的数据保存到硬盘中。
  

10.3、事务的隔离级别

事务的隔离存在一定的级别，级别越高，事务于事务之间的隔离性就越强。一共有4个级别。

1. 读未提交（read uncommitted）  最低级别
   
2. 读已提交（read committed）
   
3. 可重复读（repeatable read）
   
4. 序列化（serializable）            最高级别
   

读未提交：事务a可读取到事务b未提交的数据。

这种级别存在读到脏数据的现象（事务b未提交的数据），一般这种隔离级别是理论上的，一般没人使用，大多数的数据库默认隔离级别都是读已提交以上。

读已提交：事务a只能读取到事务b已提交的数据。

这种级别解决的脏读的现象，但是也存在不可重复读取数据的缺点。（不可重复读取数据：开启事务后，第一次读到的数据是3条，当前事务未结束可能存在第二次读取数据，可能第二次读取到的数据是4条，3不等于4，这就称为不可重复读取数据）这种级别读取到的数据绝对真实，oracle数据库默认的使用该隔离级别。

可重复读：事务a开启后，不管多久，在每次事务a读取的数据都是一致的，即使事务b修改了数据，并提交。事务a读取到的数据还以第一次读取到的数据。

这个隔离级别解决了不可重复的的问题，但是存在幻影读的现象，不够真实。mysql中默认的级别就是可重复读的级别。

序列化：只有事务a执行完以后，事务b才能执行。

这种级别是最高的级别，解决了所有的问题，但是效率极低。这种级别表示事务的排队，不能并发。类似于java中线程安全 synchronized 线程同步（事务同步），这个读取到的数据最真实。

验证各隔离级别：

1. 验证读未提交：
   

因为mysql默认的隔离级别是 repeatable read，所以我们需要先修改mysql的隔离级别。

步骤：

• 查看数据库的隔离级别
  

使用命令：select @@transaction_isolation; (我是用的是8以上的版本，其它版本可能不一样)

• 修改数据库的隔离级别未读未提交
  

使用命令：set global transaction isolation level read uncommitted ;

注意：修改以后需要重新登录数据库才能生效。

我们使用两个cmd窗口来模仿两个事务，以一个user表，只有一个name字段来测试：

结论：当处于读未提交级别时，事务B没有提交的数据，在事务A中也能够查询到。

2. 验证读以提交：
   依然需要修改数据库的隔离级别。
   

步骤：

• 修改级别
  

set global transaction isolation level read committed;

• 依然使用两个cmd窗口来模拟：
  

结论：当数据库处于读已提交的级别时，当事务B插入一条数据时，但是未提交事务，事务A是不能查询到事务B未提交的数据。只有当事务B提交事务后才能查询到提交的数据。

3. 验证可重复读
   依然需要修改数据库的隔离级别。
   

步骤：

• 修改级别
  

set global transaction isolation level repeatable read;

• 依然使用两个cmd窗口来模拟：
  

结论：当数据库在可重复的级别时，事务A查询的到数据始终是一致的，是第一次查询到的数据，即使事务B修改了并提交了数据。

4. 验证序列化
   依然需要修该数据库的隔离级别。
   

步骤：

• 修改级别：
  

set global transaction isolation level serializable;

• 依然使用两个cmd窗口来模拟：
  

结论：当数据库处于序列化的隔离级别时，当事务A在向数据库中插入一条数据，但是没有提交，事务B也操作user表，查询数据库中的数据，事务B的查询语句会一直卡在那里，等待事务A提交事务，只有事务A提交了事务，事务B才会执行对应的SQL语句。相当于是事务排队，两个事务操作同一张表时，只能一个事务一个事务进行，只有等前面的一个事务提交后，后面的事务才能执行。

11、索引 index（重点）

索引是在数据库字段上添加的，是为了提高查询的速度而存在的一种机制。一张表的一个字段可以添加索引，多个字段联合起来也可以添加索引。索引相当于是一本书的目录，是为了缩小扫描的范围。索引检索的查找效率较高。

select *from 表名 where 字段1 = 值;

如果字段1上没有添加索引，在进行查找时，mysql会将字段1上的所有值全部都扫描一遍，然后查询到符合条件的数据。

mysql的主要两种查找方式就是两种：

• 全表扫描：例如上面的例子。
  
• 根据索引扫描：
  

在mysql数据库中，索引是需要排序的，这个排序和TreeSet数据结构相同，TreeSet的底层是一个自平衡的二叉树。在mysql当中索引是一个B-Tree数据结构。遵循左小右大的原则，采用中序遍历方式遍历取数据。

11.1、索引的实现原理

使用一张表user来举例：

注意事项：

1. 在任何数据库中主键会自动添加索引对象，例如上面的id自动就有一个索引对象。另外，如果一个字段中存在unique约束也会自动添加索引对象
   
2. 在任何数据库中，任何一张表中的任何一条数据在硬盘的记录上都有一个硬盘的物理存储地址。
   
3. 在mysql中，索引是一个单独的对象，不同的存储引擎索引的存在方式不一样。在MyISAM存储引擎中，索引存储在 .MYI 文件中。在InnoDB存储引擎中，索引存储在一个逻辑名称为tablespace当中的。在MEMORY引擎中，索引存储在内存中。
   
4. 不管索引储存在哪里，它都是一个树的形式存在的（自平衡二叉树）
   

索引原理：

1. 在id字段上，将所有值按照平衡二叉树储存好，并且会把每一条数据的物理储存地址也会储存。
   
2. 当mysql发现查询的字段上有索引，它就会通过索引对象来查询。
   

例如：

select *from user where id = 56;

通过id字段的索引定位到 56 ，缩小了扫描的范围。通过 56 获取到这条数据的物理位置 0x6666，然后就能查询出来这条数据记录。

索引的使用条件：

1. 数据量庞大（这个要根据硬件来说，每个硬件的性能不一样）
   
2. 该字段经常出现在where后面，以条件的形式存在，也就是说这个字段经常被扫描。
   
3. 该字段很少的DML语句（insert、delete、update），因为数据改变后，索引需要重新排序。
   

建议不要随便添加索引，因为索引需要维护的，太多的话也会降低系统的性能。建议一般的查询通过主键和unique约束来查询。这样的效率比较高。

11.2、索引的创建和删除

索引的创建：

语法：

create index 索引的名称 on 表名(添加索引的字段);

例如：给user表中的name字段添加索引

create index user_name_index on user(name);

索引的删除：

语法：

drop index 删除的索引名称 on 表名;

例如：把user表中的name字段的索引删除

drop index user_name_index on user;

查看一条sql语句是否使用了索引：

语法：

explain 执行的sql语句;

例如：

explain select *from user where name = 'zhangsan';

11.3、索引失效

有时候一些sql语句会导致索引失效，即使添加了索引，查询的时候不会经过索引。

1. 模糊查询时以 % 开头
   

例如：

select *from user where name = '%hangsan';

所以在模糊查询的时候尽量避免给以 % 开头。

但是，在mysql版本8以上的是支持以 % 开头的索引查询的。为了统一，尽量不使用以 % 开头的索引查询。

2. 使用 or 来查询
   

      使用 or 的时候也会使索引失效，如果使用的 or 来进行索引查询，则要求两边的字段都必须有索引。如果一个字段没有索引，那莫另一个字段上的索引就会失效。

3. 使用复合索引时，没有使用左侧字段查找，索引失效
   

复合索引：两个或多个字段联合起来创建的一个索引。

创建复合索引：

语法：

create index 索引名称 on 表名(字段1,字段2);

将字段1和字段2联合起来创建一个索引。

例如：给user表中的id和name创建一个复合索引。

create index user_id_name_index on user(id,name);

如果使用id来查询的话，就可以使用索引，但是使用name来查询的话，就不会经过索引。

4. 在where中，索引列参加了运算，索引失效
   

例如：查询薪水是9999的员工

select *from staff where sallary + 1 = 10000;

这样的查询会使索引失效。

5. 在where中，索引字段使用了函数，索引失效
   

例如：

select *from user where upper(name) = 'ZHANGSAN';

这样的查询也会使索引失效。

11.4、索引的分类

• 单一索引：单个字段添加索引。
  
• 复合索引：两个或多个字段联合来添加一个索引。
  
• 主键索引：主键上添加索引。
  
• 唯一性索引：具有unique约束的字段上添加索引。
  

注意：唯一性较弱的字段上添加索引用处不大。

12、视图view

视图就是站在不同的角度去看待同一份数据。

12.1、创建和删除视图

创建视图：

语法：

create view 使视图的名称 as select语句;

例如：创建一个user表的视图。

create view user_view as select *from user;

删除视图：

语法：

drop view 视图的名称;

注意：as 后面必须是 select 语句。

12.2、视图的作用

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表的数据被操作。通过操作视图，会影响到原表的数据。方便、利于维护、简化开发

视图在原表中的作用：

视图主要是用来简化SQL语句的。

如果一条SQL语句很复杂，然而这条语句需要在多个地方使用，我们就可以创建视图对象，然后对试图对象进行操作，需要使用该条SQL语句就直接使用视图对象就可以了。

例如：

createview 视图名称

as

    很长的select语句;

在多次使用该复杂的SQL语句时，我们就直接对视图对象操作，也会更改原表中的数据。

在以后的实际开发中，使用视图就可以当成一个表来使用，可以对视图进行增删改查。视图不存在内存中，而是存在硬盘中，不会消失，可以当成一张表来使用。

13、DBA命令

数据的导出：通常用于数据的备份。

语法：

// 导出所有表的数据

mysqldump 数据库名>导出的保存路径 -uroot -p密码

// 导出某个表的数据

mysqldump 数据库名 表名>保存的路径 -uroot -p密码

注意：上面的命令必须在windows的dos命名窗口上运行。

数据的导入：用于将数据导入到数据库中。

语法：

// 登录数据库

mysql -uroot -p密码

// 创建一个数据库

// s

// 使用命令

source 导入的路径;

最近发现除了腾讯云和阿里云之外的一种好用的云服务器，那就是三丰云云服务器，它拥有众多的功能，其中一个就是可以免费试用一款云服务器，下面介绍它的使用方式。

官方地址:https://www.sanfengyun.com/

然后进行一个实名认证和微信的绑定就可以申请一个 1c1g的免费服务器。

三丰云是北京太极三丰云计算有限公司旗下网络服务品牌，十八年IDC老兵团队蛰伏三年后投资千万于2018年10月1日创建。公司致力于为大众提供优质的互联网基础服务和物联网服务，包括：域名注册、虚拟主机、云服务器、主机托管租用、CDN网站加速、物联网应用等服务。以帮助客户轻松、 高速、高效的应用互联网/物联网，提高企业竞争能力。，它拥有众多的功能，其中一个就是可以免费试用一款云服务器，下面介绍它的使用方式。

官方地址:https://www.sanfengyun.com/