MySQL

SQL的介绍

SQL(Structured Query Language):结构化查询语言。其实就是定义了操作所有关系型数据库的一种规则。通用语法规则
SQL语句可以单行或多行书写，以分号结尾可使用空格和缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

单行注释:

-- 注释内容
# 注释内容(MySQL特有)

多行注释︰

/* 注释内容 */

SQL分类

• DDL(Data Definition Language):数据定义语言。用来操作数据库，表，列等。
• DML(Data Manipulation Language)∶数据操作语言。用来对数据库中表的数据进行增删改
• DQL(Data Query Language):数据查询语言。用来查询数据库中表的记录(数据)。
• DCL(Data Control Language):数据控制语言。用来定义数据库的访问权限和安全级别，及创建用户。(由数据库管理员操作)

MySQL语法

创建数据库

• 创建数据库demo

  create database demo;
  

• 创建数据库demo（demo数据库不存在时创建）

  create database if not exists demo;
  

• 创建数据库demo并制定字符集utf8mb4（不懂MYSQL utf8和utf8mb4的去百度）

  create database demo character set utf8mb4;
  

修改、删除、使用数据库

• 修改数据库（修改字符集）

  alter database demo character set gbk;
  

• 删除数据库

  drop database demo;
  

• 删除数据库（如果存在）

  drop database if exists demo;
  

• 使用数据库

  use demo;
  

• 查看当前使用的数据库

  select database();
  

DDL查询数据表

t_user为demo数据库中的一个表，含义为用户表。带自己的表命名标准 t_ 前缀。

• 查询所有数据库

  show tables;
  

• 查询表结构

  desc 是description的缩写，描述的意思。

  desc t_user;
  

• 查询表的字符集

  show table status from demo like 't_user';
  

DDL创建数据表

• 创建数据表

  注意最后一行不要在后面加逗号了 ,

  另外所有的列定义是用小括号包起来的

  约束条件是可选的，列名和数据类型是必须的。

  create table 表名(
  	列名字 数据类型 约束条件,
  	列名字 数据类型 约束条件,
  	...
  	列名字 数据类型 约束条件
  );
  

  简单的示范SQL

  所有可用的数据类型放在文章末尾

  auto_increment声明是自增约束，必须配合键约束使用！

  create table t_user(
  	id int auto_increment primary key,
      name varchar(12),
      age int
  );
  

修改数据表结构

• 修改表名

  -- alter table 表名 rename to 新表名;
  alter table t_user rename to t_student;
  

• 修改表的字符集

  -- alter table 表名 character set 新字符集名;
  alter table t_user character set utf8mb4;
  

• 单独添加一列

  -- alter table 表名 add 列名 数据类型;
  alter table t_user add weight float;
  

• 修改某些的数据类型

  -- alter table 表名 modify 列名 数据类型;
  alter table t_user modify weight int;
  

• 修改列明和数据类型

  -- alter table 表名 change 列名 新列名 数据类型;
  alter table t_user change sex gender int;
  

删除数据表

• 直接删除

  drop table t_user;
  

• 存在则删除

  drop table if exists t_user;
  

DML添加数据

列名和值的数量以及数据类型要对应，除了数字类型，其他数据类型的数据都需要加引号(单引双引都行)

• 添加数据的时候只添加部分列的

  -- insert into 表名 (列名1,列名2) values (列名1的值,列名2的值);
  insert into t_user (name,age) values ("张三",18);
  

• 添加数据的时候所有列都写了

  -- insert into 表名 values (列名1的值,列名2的值);
  insert into t_user values(1,"张三",18);
  

• 添加多条数据

  -- insert into 表名 (列名1,列名2) values (列名1的值,列名2的值),(列名1的值,列名2的值);
  insert into t_user (name,age) values ("张三",18),("李四",20);
  

DML修改表里的数据

• 修改单个字段

  -- update 表名 set 字段名=值 where 字段=值
  -- where 字段=值这个是个条件，不写则代表修改全部
  update t_user set name = "张三三" where name = "张三";
  

• 修改多个字段

  -- update 表名 set 字段名=值,字段名=值 where 字段=值 and 字段名=值
  -- 修改多个用逗号,隔开，条件多个用and 并且 or 或者
  update t_user set name = "张三三" where name = "张三";
  

DML删除表里的数据

• 删除名字为张三的记录

  -- delete from 表名 where 字段=值
  -- 条件不写的话删除全部记录
  delete from t_user where name = "张三";
  

DQL表数据查询

查询语句结构

第一行的select from 是最基本的，后面的是可选的

select 字段列表 from 表名列表

where 条件列表

group by 分组字段

having 分组后的过滤条件

order by 排序字段列表

limit 分页

查询全部数据

-- select * from 表名
select * from t_user;

查询指定字段的数据

-- select 列名1,列名2,... from 表名
select name,age from t_user;

去除重复查询 distinct

-- select distinct 字段列表 from 表名
select distinct name,age from t_user;

计算列的值（四则运算、函数比如sum）

-- select 列名1 运算符(+-*/) 列名2 from 表名
-- 查询出所有人年龄的和
select sum(age) from t_user;

对查询结果起别名

-- select 列名 as 别名  from 表名;
-- 这样查询出的结果里就会是 age_sum
select sum(age) as age_sum from t_user;

查询可使用的条件

查询可使用的聚合函数

聚合函数语法

select 函数名(列名) from 表名 [where 条件可选];

示范查询用户表有多少条记录

select count(id) from t_user;

查询结果排序

排序语法

排序方式asc升序，desc降序，可不写，默认升序

如果写了多个排序条件，则先按第一个排序，当第一个列值相同，则继续用第二个排序，以此类推

select 列名列表 from 表名 [where 条件可选] order by 列名 排序方式,列名 排序方式

示范

• 根据年龄从大到小排序且年龄必须大于0

select * from t_user where age > 0 order by age desc;

• 还是上一个例子，但是如果年龄相同则按照id升序排序

select * from t_user where age > 0 order by age desc,id asc;

分组查询

分组查询语法

select 列名列表 from 表名 [where条件] group by 分组列名 [having分组后的条件过滤] [orderby 排序列名排序方式];

• 把用户根据年龄分组

select * from t_user group by age;

• 把用户根据年龄分组，且年龄必须大于等于18岁。

select * from t_user group by age having age >= 18;

• 把用户根据年龄分组，且年龄必须大于等于18岁,根据年龄从大到小排序。

select * from t_user group by age having age >= 18 order by age desc;

分页查询

分页查询语法

select 列名列表 from 表名 [where条件] group by 分组列名 [having分组后的条件过滤] [orderby 排序列名排序方式] limit 从第多少条开始,要的条数;

从多少条开始计算公式=(页码-1)*页数

• 每页10条，查询第3页的所有用户

  -- (3-1)*10 = 20
  select * from t_user limit 20,10;
  

约束

约束主要是对表中的数据进行限定，保证数据的正确性、有效性、完整性。

一般在创建数据表的时候会定义好约束

主键约束primary key

• 主键约束的特点
  主键约束默认包含非空和唯一两个功能。一张表只能有一个主键。
  主键一般用于表中数据的唯一标识。

• 建表时添加主键约束

  CREATETABLE表名(
  列名 数据类型 primary key,
  列名 数据类型 约束
  );
  

• 删除主键约束

  -- alter table 表名 drop primary key;
  alter table t_user drop primary key;
  

• 建表后单独添加主键约束

  auto_increment声明是自增约束，必须配合键约束使用！

  -- alter table 表名 modify 列名 数据类型 primary key;
  alter table t_user modify id int primary key auto_increment;
  

唯一约束 unique

• 建表时添加唯一约束

  create table 表名(
  列名 数据类型 unique,
  列名 数据类型 约束
  );
  

  create table t_user(
  id int primary key auto_increment,
  phone char(11) unique
  );
  

• 删除唯一约束

  -- alter table 表名 drop index 列名;
  alter table t_user drop index phone;
  

• 建表后单独添加唯一约束

  -- alter table 表名 modify 列名 数据类型 unique;
  alter table t_user modify phone char(11) unique;
  

非空约束 not null

• 建表时添加非空约束

  create table 表名(
  列名 数据类型 not null,
  列名 数据类型 约束
  );
  

  create table t_user(
  id int primary key auto_increment,
  name varchar(12) not null
  );
  

• 删除非空约束

  -- alter table 表名 modify 列名 数据类型;
  alter table t_user modify name varchar(12);
  

• 建表后单独添加唯一约束

  -- alter table 表名 modify 列名 数据类型 not null;
  alter table t_user modify phone varchar(12) not null;
  

外键约束 foreign key

外键约束不建议使用,尤其是级联删除

• 为什么要有外键约束?
  当表与表之间的数据有相关联性的时候，如果没有相关的数据约束，则无法保证数据的准确性!

• 外键约束的作用
  让表与表之间产生关联关系，从而保证数据的准确性!

• 语法

  建表的时候使用

  -- constraint 限制条件名 foreign key (本表内字段名) references 引用来源表(字段)
  -- 比如这里我这个表里的user_id必须是从t_user的id字段里有这个值
  -- 其他的字段我就省略了
  create table t_order(
  	id int auto_increment primary key,
      user_id int,
      constraint out_fk_demo foreign key (user_id) references t_user(id) 
  );
  -- 或者(不建议)
  create table t_order(
  	id int auto_increment primary key,
      user_id int foreign key  references t_user(id)
  );
  

  建表后使用

  -- constraint 限制条件名 foreign key (本表内字段名) references 引用来源表(字段)
  -- 比如这里我这个表里的user_id必须是从t_user的id字段里有这个值
  alter table t_order add constraint out_fk_demo foreign key (user_id) references t_user(id);
  

• 级联更新、级联删除（可选）

  on update cascade 级联更新

  on delete cascade 级联删除

  alter table t_order add constraint out_fk_demo foreign key (user_id) references t_user(id) on update cascade on delete cascade;
  

多表操作

• 一对一

  例如用户表和身份证表，一个用户只有一个身份证记录

• 一对多

  例如用户表和收货地址表,一个用户可以有多个收货地址，但是每个收货地址只属于一个用户。

• 多对多

  例如用户表和商品表，一个用户可以收藏多个商品，一个商品也可以被多个用户收藏。

起别名

关键字 as

t_user as u 就是给t_user起了个别名为u

-- 从t_user（别名u）和t_order隐式内连接查询出订单表中uid等于用户表的id的
select * from t_user as u,t_order as o where u.id = o.uid;

内连接查询

交集部分

• 显示内连接

  -- select 列名 from 表名1 [inner] join 表名2 on 条件;
  select * from t1 inner join t2 on t1.id = t2.uid;
  

• 隐式内连接

  -- select 列名 from 表名1,表名2 where条件
  select * from t1 where t1.id = t2.uid;
  

外连接查询

全部数据+交集数据

• 左连接（左外连接）左表的全部数据+交集数据

-- select 列名 from 表名1 left [outer] join 表名2 on 条件;
select * from t_user left outer join t_order on t_user.id = t_order.uid;

• 右连接（右外连接）右表的全部数据+交集数据

-- select 列名 from 表名1 right [outer] join 表名2 on 条件;
select * from t_user right outer join t_order on t_user.id = t_order.uid;

全连接查询

全部数据

• 全连接查询（全外连接查询）

-- select 列名 from 表名1,表名2;
select * from t_user,t_order;

子查询

1. 结果是单行数据（可以看做是一个值）

   • 查询年级最大的用户姓名

   select name from t_user where age = (select max(age) from t_user);
   

2. 结果是多行单列的（可以看做是一个数组）

   • 查询年龄小于18岁的用户的订单记录

   select * from t_order where uid in (select id from t_user where age < 18);
   

3. 查询结果是多行多列的（可以看做是一个虚拟表）

   • 查询年龄小于18岁的用户的信息和他们的订单记录

   select * from t_order as o,(select * from t_user where age < 18) as u where o.uid = u.id;
   

4. 自关联查询（自己和自己连接）

   • 查询自己的信息和自己的上级信息（没有上级的也要显示，其实就是左外连接）

   create table employ(
       id int auto_increment primary key,
       name varchar(12),
       -- 上级领导的ID
       leadedId int
   );
   

   select e1.id as id,e1.name as name,e1.leaderId as leaderId,e2.name as leaderName from employ as e1 left out join employ as e2 on e1.leaderId = e2.id ;
   

视图

将你书写的查询语句查询出的结果封装成一个虚拟的数据表，这个表并不实际存在，而是在你查询这个表的时候会先去执行你之前写的查询语句，把结果作为一个表来使用。

创建视图

create table city(
	id int primary key,
    name varchar(24),
    countryId int comment '国家ID'
)comment '城市表';
create table country(
	id int primary key,
    name varchar(24)
)comment '国家表';

• 建立一个城市和所属国家名字的视图

-- create view 视图名 [(列名...)] as 查询语句
-- 可选项 列名列表，会和查询结果一一对应
create view city_country (city_id,city_name,country_name) as select c1.id,c1.name,c2.name from city c1,country c2 where c1.countryId = c2.id;

-- city_id |  city_name | country_name
   1          上海           中国

查询视图

可以看做是一个表来使用

• 查询上面所建立的视图里的全部内容

  select * from city_country;
  

修改视图数据

源表中相关数据也会随之修改！

• 将北京修改为深圳

  update city_country set city_name = '深圳' where city_name = '北京';
  -- 修改后城市表中之前的北京会被改为深圳！
  

删除视图

• 删除上面的那个视图

  drop view city_country;
  -- 或者如果存在则删除
  drop view if exists city_country;	
  

存储过程和函数

其实就是提前写好的一些SQL语句的集合，提高复用性。

• 存储过程和存储函数的区别

  存储函数必须有返回值，存储过程可以没有返回值，区别不大。

存储过程

• 创建存储过程

  -- 修改结束分隔符
  delimiter $
  -- 创建存储过程
  create procedure 存储过程名称(参数列表或者说是形式参数)
  begin
        SQL语句列表
  end$
  -- 修改回结束分隔符
  delimiter ;
  

  delimiter $
  create procedure order_detail()
  begin
  	select * from t_order as o,t_user as u where u.id = o.uid;
  end$
  delimiter ;
  

• 定义变量

  declare 变量名 数据类型[默认值];
  -- 同类型变量支持多个一次性定义
  -- declare num1,num2 int;
  

  delimiter $
  create procedure order_detail()
  begin
  	declare num int default 10;
  	select num;
  end$
  delimiter ;
  

• 变量赋值

  • 直接赋值

  delimiter $
  create procedure proc_demo()
  begin
  	declare num int;
  	set num = 10;
  	select num;
  end$
  delimiter ;
  

  • 查询赋值

  delimiter $
  create procedure sum_score()
  begin
  	declare sum_score int;
  	select sum(score) into sum_score from t_grade;
  	select sum_score;
  end$
  delimiter ;
  -- 最终直接结果是 sum_score的值
  

• if条件判断

  delimiter $
  create procedure score_level_demo()
  begin
  	declare sum_score int;
  	declare score_level varchar(20);
  	select sum(score) into sum_score from t_grade;
  	if sum_score > 500 then
  		set score_level = '优秀';
      elseif total >300 then
      	set score_level = '良好';
      else
      	set score_level = '一般';
      end if;
      select sum_score,score_level;
  end$
  delimiter ;
  -- 最终直接结果是 sum_score的值
  

• 参数使用

  • 输入参数 in
  • 输出参数 out
  • 输入/输出参数 inout

  -- create procedure 存储过程名称([in|out|inout] 参数名 参数类型)
  delimiter $
  create procedure score_level_demo(in sum_score int,out score_level varchar(24))
  begin
  	if sum_score > 500 then
  		set score_level = '优秀';
      elseif total >300 then
      	set score_level = '良好';
      else
      	set score_level = '一般';
      end if;
  end$
  delimiter ;
  

  调用

  -- 定义一个变量为@level_result用来接收返回值
  call score_level_demo(383,@level_result);
  -- 还可以这样
  -- call score_level_demo((select sum(score) from t_grade),@level_result);
  -- 查询返回值
  select @level_result;
  

• while循环

  初始化语句;
  while 条件判断语句 do
  	循环体语句;
  	条件控制语句;
  end while;
  

  delimiter $
  create procedure while_demo()
  begin
  	declare sum_num int default 0;
  	declare i int default 1;
  	while i < 100 do
  		-- 循环体语句
  		if i % 2 = 0 then
  			sum_num = sum_num + i;
  		end if;
  		-- 条件控制语句
  		set i = i + 1;
      end while;
      select sum_num;
  end$
  delimiter ;
  

• 调用存储过程

  call 存储过程名称(实际参数);
  

  call order_detail();
  

• 查看存储过程

  -- select * from mysql.procedure where db='数据库名称';
  select * from mysql.procedure where db='demo_db';
  

• 删除存储过程

  -- drop procedure [if exists] 存储过程名称;
  drop procedure if exists order_detail;
  

存储函数

必须有返回值，和存储过程很类似。

• 创建存储函数

  create function 函数名称(参数列表)
  returns 返回值类型
  begin
  	SQL语句列表;
  	return 结果;
  end$
  

  create function adult_count()
  returns int
  begin
  	declare adult_count default 0;
  	select count(*) into adult_count from t_user where age >= 18;
  	return adult_count;
  end$
  

• 调用存储函数

  select 函数名称(实际参数);
  

  select adult_count();
  

• 删除存储函数

  -- drop function 函数名称;
  drop function adult_count;
  

触发器

在某个操作被执行的时候触发另一个操作

触发器介绍

• 触发器是与表有关的数据库对象，可以在insert、update、delete之前或之后触发并执行触发器中定义的SQL语句

• 这种特性可以协助应用系统在数据库确保数据的完整性、日志记录、数据校验等操作。

• 使用别名NEW 和 old 来引用触发器中发生变化的内容记录

触发器分类

创建触发器

delimiter $
create trigger 触发器名称
before|after insert|update|delete
on 表名
for each row
begin
	触发器要执行的功能
end$
delimiter ;

• insert 触发器示范

delimiter $
create trigger trigger_demo
after insert
on t_user
for each row
begin
	insert into t_user_log (operation,operation_time,operation_id,operation_data) values('insert',now(),new.id,concat('插入后：{id=',new.id,',username=',new.username,'}'))
end$
delimiter ;

• update 触发器示范

delimiter $
create trigger trigger_demo
after update
on t_user
for each row
begin
	insert into t_user_log (operation,operation_time,operation_id,operation_data) values('update',now(),old.id,concat('更新前：{id=',old.id,',username=',old.username,'}更新后{id=',new.id,',username=',new.username,'}'))
end$
delimiter ;

• delete触发器示范

delimiter $
create trigger trigger_demo
after delete
on t_user
for each row
begin
	insert into t_user_log (operation,operation_time,operation_id,operation_data) values('delete',now(),old.id,concat('删除前：{id=',old.id,',username=',old.username,'}'))
end$
delimiter ;

查看触发器

show triggers;

删除触发器

-- drop trigger 触发器名称;
drop trigger trigger_demo;

事务

事务介绍

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句
• 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。*

事务ACID四原则

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务Demo

张三给李四转账500元，需要对张三的账号余额减500，李四的账号余额加500。

-- 下面的命令不是全部直接执行，而是一条条自己去执行
-- 开启事务
start transaction;
-- 进行操作
update t_user set money = money - 500 where name = '张三';
update t_user set money = money + 500 where name = '李四';
-- 回滚事务（出错的时候去执行这个就能恢复到开始事务之前的状态，执行都成功你就别执行回滚了）
rollback;
-- 提交事务（自动提交的时候不需要）
commit;

事务提交方式（自动/手动）

• 查询事务提交方式

  select @@autocommit;
  

• 修改事务提交方式(1自动提交，0手动提交)

  set @@autocommit = 0;
  

事务隔离级别

多个客户端操作时，各个客户端的事务之间应该是隔离的，相互独立的，不受影响的。
而如果多个事务操作同一批数据时，就会产生不同的问题，我们需要设置不同的隔离级别来解决这些问题。

隔离级别 有四种，分别是：读未提交、读已提交、可重复读、序列化。

• 读未提交： Read Uncommitted，顾名思义，就是一个事务可以读取另一个未提交事务的数据。最低级别，它存在4个常见问题（脏读、不可重复读、幻读、丢失更新）。

• 读已提交： Read Committed，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。 它解决了脏读问题，存在3个常见问题（不可重复读、幻读、丢失更新）。

• 可重复读： Repeatable Read，就是在开始读取数据（事务开启）时，不再允许修改操作 。它解决了脏读和不可重复读，还存在2个常见问题（幻读、丢失更新）。

• 序列化： Serializable，序列化，或串行化。就是将每个事务按一定的顺序去执行，它将隔离问题全部解决，但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

大多数数据库默认的事务隔离级别是 Read Committed，比如 SQL Server , Oracle。但 MySQL 的默认隔离级别是 Repeatable Read。

• 查询数据库隔离级别

select @@tx_isolation;
-- 默认是 repeatable read

• 修改数据库隔离级别

  修改后需要重新连接

-- set global transaction isolation level 级别字符串;
set global transaction isolation level read committed;

脏读问题

需要设置事务隔离级别为read uncommited读未提交(允许读取其他事务未提交的修改了的数据) 才能复现

张三给李四转账，开启事务，给自己余额减去五百，给李四余额加五百，李四开启事务，查询自己余额是不是多了500，查到是的的确多了500，然后张三让李四给他打一个欠条，李四打完欠条后张三回滚了账号余额数据，而李四给张三打了500欠条，血亏。因此李四读到了张三事务进行中影响的数据造成了读取到了脏的数据。

脏读其实就是读到了别人事务正在修改的数据（未提交）

-- 事务1
start transaction;
-- 张三跟李四借钱，张三开始修改余额
update t_user set money = money - 500 where name = '张三';
update t_user set money = money + 500 where name = '李四';
-- 查到余额改变了，跟李四要欠条，然后回滚数据
select * from t_user;
rollback;
commit;

-- 事务2
start transaction;
-- 查看转账
select * from t_user;
-- 写借条
commit;

解决办法：将事务隔离界别上升到读已提交以上的级别，那么张三没有提交之前，李四这边查到的永远还是之前余额的数据，因此不会给张三收据，直到张三提交事务，李四才能查到自己的账号钱增加了

不可重复读问题

程序员拿着自己的卡去买东西，结账的时候，收银台开启事务，查询卡余额，有三万够支付的，而此时他妻子拿着手机打开了银行转账，开启事务，把所有钱转走，事务完成，现在程序员的卡里没有一分钱了，而收银台的事务读到的还是三万余额，等扣钱的时候发现没钱了。事务中两次对同一数据的读取结果不同，则产生了不可重复读问题。

-- 收银台开启事务1
start transaction;
-- 查卡余额三万
select * from t_user where name = "程序员";
--  ........
-- 程序员妻子转完账了，再查询余额的时候没钱了，无法扣款
commit;

-- 程序员妻子转账开启事务2
start transaction;
-- 查看转账
update t_user set money = money -30000 where name = '程序员';
commit;
-- 转账成功

解决办法：将事务隔离界别上升到可重复读以上的级别，可重复读以后呢，底层使用MVCC（ Mutil-Version Concurrent Control(多版本并发控制)），让每个事务读取的时候查如果没有自己这个事务对这个数据查询的结果那就去查询并建立ReadView,如果有的话就返回之前读取的结果，因此在这个事务你可以重复多次读取仍然是第一次读取到的那个值。

幻读问题

你开启事务，查询手机号码为xx的账号是否注册了，你发现没有注册，你就准备插入这条用户记录，而你查询的时候，我也开启了事务，我往里面也插入这条记录，我提交了，而你提交的时候会报错，因为这条记录已经存在了。而你明明查询的时候这条记录没有，执行插入又有，就成了幻读。

解决办法：将事务隔离界别上升到串行化级别，每个事务依次执行完，执行完一个才能执行下一个。

存储引擎

• 客户端连接
  支持接口∶支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库。

• 第一层∶网络连接层
  连接池∶管理、缓冲用户的连接，线程处理等需要缓存的需求。

• 第二层︰核心服务层
  管理服务和工具:系统的管理和控制工具，例如备份恢复、复制、集群等。

  SQL接口∶接受SQL命令，并且返回查询结果。
  查询解析器:验证和解析SQL命令，例如过滤条件、语法结构等。
  查询优化器:在执行查询之前，使用默认的一套优化机制进行优化sql语句。
  缓存∶如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询.

• 第三层:存储引擎层
  插件式存储引擎︰管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)

• 第四层︰系统文件层
  文件系统︰配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存。

常用存储引擎

• MySQL数据库使用不同的机制存取表文件,包括存储方式、索引技巧、锁定水平等不同的功能。这些不同的技术以及配套的功能称为存储引擎。
• Oracle、SqlServer等数据库只有一种存储引擎。而MySQL针对不同的需求,配置不同的存储引擎就会让数据库采取不同处理数据的方式和扩展功能。
• MySQL支持的存储引擎有很多，常用的有三种: InnoDB、MylSAM、MEMORY.
• 特性对比
  • MylSAM存储引擎:访问快，不支持事务和外键操作。
  • InnoDB存储引擎︰支持事务和外键操作，支持并发控制，占用磁盘空间大。(MySQL5.5版本后默认)
  • MEMORY存储引擎:内存存储，速度快，不安全。适合小量快速访问的数据。
    心

数据库引擎操作

• 查询数据库所支持的引擎

show engines;

• 查询某个数据库中所有数据表的存储引擎

-- show table status from 数据库名称;
-- 下面例子里就一个staff表
show table status from demo_db;

• 查询数据库中某个数据表的存储引擎

  -- show table status from 数据库名字 where name = 数据表名字;
  show table status from demo_db where name = 'staff';
  

• 创建数据表，指定存储引擎

  create table t_user(
  	id int primary key,
      name varchar(12)
  )engine = InnoDB;
  

• 修改数据表的存储引擎

  alter table t_user engine = InnoDB;
  

存储引擎的选择建议

• MylSAM
  特点:不支持事务和外键操作。读取速度快，节约资源。
  使用场景︰以查询操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高!
• lnnoDB
  特点:MySQL的默认存储引擎，支持事务和外键操作。
  使用场景︰对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作!
• MEMORY
  特点∶将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。使用场景︰通常用于更新不太频繁的小表，用来快速得到访问的结果!

总结∶针对不同的需求场景，来选择最适合的存储|擎即可!如果不确定、则使用数据库默认的存储引擎!

索引

索引介绍

• MySQL索引∶是帮助MySQL高效获取数据的一种数据结构。所以，索引的本质就是数据结构!
• 在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引分类

按照功能分类

• 普通索引∶最基本的索引，没有任何限制。
• 唯一索引∶索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。主
• 键索引:一种特殊的唯一索引，不允许有空值。在建表时有主键列同时创建主键索引。
• 联合索引∶顾名思义，就是将单列索引进行组合。
• 外键索引∶只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
• 全文索引∶快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

按照结构分类

• BTree索引:MySQL使用最频繁的一个索引数据结构，是InnoDB和MylSAM存储引擎默认的索引类型，
• 底层基于B+Tree数据结构。
  Hash索引:MySQL中Memory存储引擎默认支持的索引类型。

创建索引

• 给t_user表的name列创建一个普通索引

-- create index 索引名称 on 表名称(列名);
create index inx_name on t_user(name);

• 给t_user表的age列创建一个唯一索引

-- create index 索引名称 on 表名称(列名);
create unique index inx_age on t_user(age);

查询索引

show index from t_user;

添加索引

（大小写都是一样的）

• 普通索引:ALTER TABLE表名ADD INDEX索引名称(列名);
• 组合索引:ALTERTABLE表名ADD INDEX索引名称(列名1,列名2.….);
• 主键索引:ALTER TABLE表名APD PRIMARY KEY(主键列名);
• 外键索引:ALTER TABLE表名ADD CONSTRAINT外键名FOREIGNKEY(本表外键列名)REFERENCES主表名(主键列名);
• 唯一索引:ALTER TABLE表名ADD UNIQUE索引名称(列名);
• 全文索引:ALTERTABLE表名ADD FULLTEXT索引名称(列名);

删除索引

-- drop index 索引名称 on 表名称;
drop index idx_name on t_user;

索引原理

• 系统从磁盘读取数据到内存时是以磁盘块( block )为基本单位的。
• 位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
• InnoDB存储引擎中有页( Page )的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认
• 每个页的大小为16KB。InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在
• 查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

BTree

例如查找id为15的数据，读取磁盘块1，15小于17，从c2指针找到左边磁盘块2，读取磁盘块2，15大于12通过c7找到右下边的磁盘块7，读取磁盘块7，然后从左往右，依次遍历13,15找到id为15的数据。因为每次都需要读取每个找的过程中遇到的节点，效率不够高，因此有B+Tree优化。

B+Tree

找id为15的数据，读取磁盘块1，id15小于id28，则通过c2指针读取磁盘块2，发现id15在id10和id17之间，通过c5指针找到磁盘块5，读取磁盘块5，从磁盘块5中找到id15的数据。由于所有数据放在最底层第三层的叶子节点中，前面两层全部存的id和指针，因此需要读取的少，不需要读取数据，减少了io次数，因此效率更高。

另外B+Tree将最底层每个磁盘块左右用指针连接起来，在范围查找的时候，只需要找范围的两端，然后利用左右指针即可找出这个范围的。

• BTree数据结构
  每个节点中不仅包含key值，还有数据。会增加查询数据时磁盘的IO次数。B+Tree数据结构
• 非叶子节点只存储key值。所有数据存储在叶子节点。
  所有叶子节点之间都有连接指针。
• B+Tree好处
  提高查询速度。减少磁盘的IO次数。树型结构较小。

索引设计原则

• 创建索引遵循的原则

1. 对查询频次较高，且数据量比较大的表建立索引

2. 使用唯一索引，区分度越高，使用索引的效率越高。

3. 索引字段的选择，最佳候选列应当从where子句的条件中提取。

4. 索引虽然可以有效的提升查询数据的效率，但并不是多多益善。

• 最左匹配原则（适合组合索引）

• 例如︰为user表中的name、address、phone列添加组合索引
  ALTER TABLE user ADD INDEX idx_three(name,address,phone);

• 此时，组合索引idx_three实际建立了(name)、(name,address)、(name,address,phone)三个索引

• 下面的三个SQL语句都可以命中索引
  SELECT * FROM user WHEREaddress = '北京’AND phone = '12345’AND name = ‘张三’;

  SELECT *FROMuser WHERE name = '张三’AND address = ‘北京’;
  SELECT * FROMuser WHERE name = ‘张三’;

• 这三条SQL语句在检索时分别会使用以下索引进行数据匹配

  (name,address,phone)
  (name,address)

  (name)

• 索引字段出现的顺序可以是任意的，MySQL优化器会帮我们自动的调整where条件中的顺序如果

• 组合索引中最左边的列不在查询条件中，则不会命中索引，例如下面这个就不会命中
  SELECT * FROMuser WHEREaddress = ‘北京’;

锁机制

锁的介绍

• 锁机制:数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全所设计的一种规则。

• 锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性。

• 按操作分类
  共享锁:也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影呵，但是个能修议数话。

  排他锁:也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入。

• 按粒度分类
  表级锁︰会锁定整个表。开销小，加锁快。锁定粒度大，发生锁冲突概率高，并发度低。不会出现死锁情况。

  行级锁︰会锁定当前行。开销大，加锁慢。锁定粒度小，发生锁冲突概率低，并发度高。会出现死锁情况。

• 按使用方式分类
  悲观锁:每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
  乐观锁:每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据。

锁使用示范

InnoDB共享锁、排他锁

• 共享锁 lock in share mode

  条件了带索引则默认加行锁，不带则默认加表锁。

  -- 事务1
  start transaction;
  -- 对id为1的记录加共享锁
  select * from t_user where id = 1 lock in share mode;
  commit;
  

  -- 事务2
  start transaction;
  -- 对id为1的记录查询能够查询出
  select * from t_user where id = 1;
  -- 如果事务1不提交，则这个事务要修改这个记录就没办法修改，要一直阻塞等锁释放，由于上面加的是带有索引的，默认行锁，因此我们可以修改其他的行内容
  update t_user set name = '张三' where id = 1;
  

• 排他锁 for update

  加锁后其他的事务只能查询，无法加锁查询，不能修改

  -- 事务1
  start transaction;
  -- 对id为1的记录加排他锁
  select * from t_user where id = 1 for update;
  commit;
  

  -- 事务2
  start transaction;
  -- 对id为1的记录查询能够查询出
  select * from t_user where id = 1;
  -- 对id为1的记录查询不能加共享锁查询
  select * from t_user where id = 1 lock in share mode;
  -- 如果事务1不提交，则这个事务要修改这个记录就没办法修改，要一直阻塞等锁释放
  update t_user set name = '张三' where id = 1;
  -- 如果事务1不提交，则这个事务要加锁也无法成功，要一直阻塞等锁释放
  select * from t_user where id = 1 for update;
  commit;
  

MyISAM读锁

• MyISAM不支持事务

• 读锁特点

  所有连接只能查询数据、不能修改

• 读锁语法格式

  加锁

  -- lock table 表名 read;
  lock table t_user read;
  

  解锁

  unlock tables;
  

• 写锁语法格式

  当前连接能够查询和修改数据，其他连接无法查询和修改数据，需要等待解锁。

  加锁

  -- lock table 表名 write;
  lock table t_user write;
  

  解锁

  unlock tables;
  

悲观锁

前面的共享锁、排他锁就是悲观锁,先上锁，再操作。

悲观锁就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。我们之前所学的锁机制都是悲观锁。

乐观锁

乐观锁就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。但是在更新的时候会去判断在此期间数据有没有被修改。
需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

数据库备份和恢复

图形化操作自己用工具导出表结构和数据即可，此处只写命令方式。

备份到文件

# mysqldump -u 用户名 -p 数据库名 > 文件存储地址
# 下面例子使用root账户将demo_db这个数据库备份到/root/demo_db.sql这个文件中（数据表和数据都有）
mysqldump -u root -p demo_db > /root/demo_db.sql
# 回车后输入账号的密码回车

从文件中恢复数据库

# 进入mysql
mysql -u root -p
# 输入密码后回车（不会回显你输入的）

-- 查看所有数据库
show databases;
-- 如果数据库不存在，则先创建数据库
create database demo_db;
-- 切换到这个数据库
use demo_db
-- 加载执行sql文件，即可恢复之前的表和数据
source  /root/demo_db.sql

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

 

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

---

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

---

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。