MySQL#

基本使用方式#

添加索引

CREATE [UNIQUE] INDEX 索引名称 on 表名(字段);

alter table 表名 add index 索引名称(字段);

删除索引

drop index 索引名称 on 表名;

删除主键索引

alter table dept drop primary key;

索引的类型#

1.主键索引，主键自动的为主键添加索引（primary key）

2.唯一索引（unique），在创建表的时候，添加为唯一索引

3.普通索引（index）

4.全文索引（fulltext）在实际使用的时候一般不使用自带的全文索引往往切换为Solr或者ElasticSearch

显示来自于dept的索引

show index from dept;

show keys from dept;

show indexes from dept;

练习#

为以下的表格添加主键索引

create table `order` (
`id` int,
`name` varchar(32),
`p_name` varchar(32),
`count` int
)

alter table `order` add unique index id_index (id);

create index id_index on `order`(id);

创建索引规则#

1.较为频繁作为查询条件字段应该创建索引

例如：一个学校中的学生id

2.唯一性太差的字段不适合单独创建索引，即使查询频繁

例如：学生的性别

3.更新非常频繁的字段不适合创建索引

例如：学生账号的登录次数

4.不会出现在where子句中的字段不该创建索引

例如：学生信息表中的详细的家庭住址

什么是事务？#

事务用于保证数据的一致性，它由一组相关的dml语句组成，改组的dml语句要么全部执行，要么全部失败，例如在转账的时候就需要使用事务来处理，以保证数据的一致性

事务和锁

当执行事务操作的时候，mysql会在表上加锁，防止其他用户修改表的数据，这对于用户而言非常重要

引用示例#

我们现在有一个运用场景，有如下的一张表格代表两个用户的银行账户信息

create table balance (
`id` int primary key,
`name` varchar(32),
`money` double
)

insert into balance values
(100,'tom',3000.00),
(200,'king',6000);

我们现在有如下需求：

将tom的100块钱转移到king的账户下

update balance set money = money-100 where id=100;
update balance set money = money+100 where id=200;

但是假如我们的数据库运行出现问题：第一条转账语句成功但是第二条失败，则tom转出的100块钱就凭空消失了，这个时候我们就需要运用到事务操作，将两条语句当为一个整体执行（要么全部执行成功，要么失败）

事务基本操作#

start transaction; --设置了事物的开始

savepoint a;  --保存点a
update balance set money = money-100 where id=100;--dml

savepoint b;  --保存点a
update balance set money = money+100 where id=200;--dml

ROLLBACK;  --回滚
commit;  --提交
select * from balance;

注意事项#

1.如果不开始事务，默认情况下，dml操作是自动提交的，不可以进行回滚

2.如果开始一个事务，没有创建保存点，可以执行rollback操作，默认回滚到事务开始的状态

3.可以在事务还没有提交的时候，创建多个保存点

4.可以在事务还没有提交的时候，选择回退到哪个保存点

5.mysql事务机制需要innodb存储引擎，myisam不可以使用事务机制

事务的acid特性#

（atomicity）原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

（consistency）一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态

（isolation）隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

（durability）持久性：持久性是指一个事务一旦被提交，他对于数据库中的数据的改变就是永久的，接下来即使数据库发生故障可而不应该对其有任何的影响

事务隔离表格#

事务隔离级别：MySQL隔离级别定义了事物与事务之间的隔离程度

Mysql中解决幻读#

Mysql中的默认隔离级别是可重复读，但是在使用innodb引擎的时候不会发生幻读，原因如下

InnoDB 存储引擎通过多版本并发控制(MVCC, Multi-Version Concurrency Control)机制，在”可重复读”(Repeatable Read)隔离级别下有效解决了幻读问题。

普通SELECT操作(快照读)
通过ReadView判断哪些版本对当前事务可见
总是读取事务开始时的一致性快照
其他事务的插入操作不会影响当前事务的查询结果

当前读操作
对于SELECT ... FOR UPDATE、UPDATE、DELETE等操作：
InnoDB会加Next-Key Lock(记录锁+间隙锁)
防止其他事务在查询范围内插入新记录
从而彻底解决幻读问题

START TRANSACTION;
SELECT * FROM t WHERE id > 1; -- 看到3,5
-- 此时事务B插入id=4并提交
SELECT * FROM t WHERE id > 1; -- 仍然只看到3,5(快照读)
SELECT * FROM t WHERE id > 1 FOR UPDATE; -- 会看到3,4,5(当前读)
COMMIT;

在可重复读隔离级别下，普通SELECT不会看到事务B插入的id=4，而加锁的SELECT会看到并锁定这些记录。

读已提交和可重复读#

可重复读和可串行化#

常用代码#

select @@tx_isolation;

SELECT @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;

SET global TRANSACTION ISOLATION LEVEL 隔离级别;

SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;

SET SESSION TRANSACTION ISOLATION LEVEL read committed;

SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;

SET SESSION TRANSACTION ISOLATION LEVEL serializable;

引擎比较#

接下来来比较InnoDB、Myisam、Memory、Archive之间的优缺点：

小结#

1.MyISAM不支持事务、也不支持外键、但是访问速度极快、对于事物完整性没有要求

2.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全、但是相比于MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的从磁盘空间以保留数据和索引

3.Memory存储引擎使用存在内存中的内容来创建表，每个Memory表只实际对应一个磁盘文件，该类型的表访问速度极快，因为数据是存放在内存中的，并且默认使用了Hash索引，但是MySQL服务关闭，表中的数据就会丢失，表的结构还在

使用案例#

修改存储引擎

alter talble 表名 engine = 存储引擎;

alter table balance engine = innodb;

创建一个MyISAM存储引擎的表，无法使用事务

create table balance (
`id` int primary key,
`name` varchar(32),
`money` double
) ENGINE MYISAM;

创建一个Memory存储引擎的表，支持Hash索引，执行速度很快

create table balance (
`id` int primary key,
`name` varchar(32),
`money` double
) ENGINE Memory;

总结#

1.如果应用不需要事务，处理的只是基本的增删改查，那么MyISAM是绝佳的选择，速度快

2.需要事务支持，就选择InnoDB

3.Memory存储引擎将数据存在内存中，由于没有磁盘的IO等待，速度非常快，但数据不持久

基本概念#

视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含列，数据来自于对应的真实表（基表）

1.视图是根据基表（可以多个基表）来创建的，视图是虚拟的表

2.视图也有列，数据来自于基表

3.通过修改视图可以修改基表的数据

4.基表的改变也会影响到视图的数据

基本使用#

创建视图

create view 视图名称 as select语句;

更新视图

alter view 视图名称 as select语句;

显示视图是如何创建的（select语句）

show create view 视图名;

删除视图

drop view 视图1,视图2...;

我们要对于员工表创建一个名为emp_view的视图（数据包含empno,ename,job,deptno）

create view emp_view as 
select empno,ename,job,deptno from emp;

我们现在来查看一下这个视图

select * from emp_view;

+-------+--------+----------+--------+
| empno | ename  | job      | deptno |
+-------+--------+----------+--------+
|  7396 | SMITH  | CLERK    |     20 |
|  7499 | ALLEN  | SALESMAN |     30 |
|  7596 | WARD   | SALESMAN |     30 |
|  7696 | JOOES  | MANAGER  |     20 |
|  7796 | MARTIN | SALESMAN |     30 |
|  7896 | BLAKE  | MANAGER  |     30 |
+-------+--------+----------+--------+

删除视图

drop view emp_view;

例题：针对emp，dept，salgrade 三张表格创建一个emp_view视图，可以显示雇员编号，雇员名称，雇员部门名称，薪水级别

相关的select语句

select empno,ename,dname,grade from emp, dept, salgrade 
where emp.deptno = dept.deptno
and sal between losal and hisal;

制作视图

create view emp_view as
select empno,ename,dname,grade from emp, dept, salgrade 
where emp.deptno = dept.deptno
and sal between losal and hisal;

视图细节#

1.创建视图后，到数据库看，对应的视图只有一个视图结构文件(视图名.frm)

2.视图和基表的修改相互影响

update emp_view set deptno = 40 where ename='SMITH';

update emp set deptno = 20 where ename='SMITH';

3.视图中可以再次使用视图

使用场景#

安全：一些数据表有着重要的信息，某些字段需要保密，不可以让用户直接看到，这个时候我们就可以创建一个视图，在这个视图中保留安全的字段，而不包含保密的字段

性能：关系数据库常常会分表存储，使用外键建立这一些表的关系，这个时候，数据库查询通常会用到join连接，这么做不但麻烦，效率也比较低，如果建立一个视图，将相关表的字段组合在一起，就可以避免使用join查询数据

灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃，然而，很多表都是基于这一张表，不方便修改，这个时候就可以建立一张视图，视图中的数据直接映射到新建的表，这样就可以做很少的改动，也达到了升级数据表的目的

user表#

mysql中的用户，都存储在系统数据库mysql中的user表

其中有一些重要的字段

host：允许登录的位置，localhost表示该用户只允许本机登录，也可以指定ip

user：用户名称

authentication_string：密码，通过mysql的password()函数加密后的密码

select host,user,authentication_string from user;

+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | $A$005$ xgY]U4ieS
8Q+#hB9QVmRfma9kzzSPEIJ1FgvgP7VMuobh4V.rZD0mc7FD  |
+-----------+------------------+------------------------------------------------------------------------+

用户管理#

当做项目开发的时候，Mysql数据库管理人员可以根据不同的开发人员，赋给相应的Mysql操作权限，供人员使用

create user 'thrinisty' @'localhost' identified by '123456';

select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
| localhost | thrinisty        |
+-----------+------------------+

drop user '用户名' @'允许登录的位置'

set password = password('密码');--修改自身密码

set password for '用户名' @'登录位置' = password('密码');
--修改他人的密码，需要有用户密码的权限

ALTER USER USER() IDENTIFIED BY '654321';--修改自身密码

ALTER USER 'username'@'hostname' IDENTIFIED BY '654321';
--修改他人的密码，需要有用户密码的权限

权限管理#

MySQL中有非常多的权限可以赋予用户，我们来使用以下常见的权限

grant 权限列表 on 库.对象名 to 
'用户名'@'登录位置' [identified by '密码'];

revoke 权限列表 on 库.对象名 from 
'用户名'@'登录位置'

FLUSH PRIVILEGES;

grant all on *.* to 'thrinisty'@'localhost';

管理细节#

1.在创建用户的时候，如果不指定host，则为%，表示所有ip都有链接的权限

create user jack;

2.在删除用户的时候，如果host不是%，需要明确指定‘用户’@‘host值’