mysql总结

Mysql总结

sql的分类

1、DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等； 常用语句：CREATE、 ALTER、DROP
2、DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别； 常用语句：GRANT、DENY
4、DQL(Data Query Language) 数据查询语言，用来查询数据 常用语句：SELECT

数据库的三大范式

1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。
2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。
3、第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。

sql数据类型

整数类型

根据数值取值范围的不同MySQL 中的整数类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的。

浮点数类型和定点数类型

在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。下图列举了 MySQL中浮点数和定点数类型所对应的字节大小及其取值范围：

从上图中可以看出：DECIMAL类型的取值范围与DOUBLE类型相同。但是，请注意：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长 度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(6,2)的数据6.5243 插人数据库后显示的结果为6.52

字符串类型

在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串。
当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1

文本类型用于表示大文本数据，例如，文章内容、评论、详情等，它的类型分为如下4种：

日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是 ：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值

使用CURRENT_TIME或NOW()输人当前系统时间。

二进制类型

在MySQL中常用BLOB存储二进制类型的数据，例如：图片、PDF文档等。BLOB类型分为如下四种：

数据库，数据表的基本操作

进入mysql命令

mysql -uroot -p

数据库的基本操作

MySQL安装完成后，要想将数据存储到数据库的表中，首先要创建一个数据库。创建数据库就是在数据库系统中划分一块空间存储数据，语法如下：

create database 数据库名称;

查看已创建的数据库：

show create database db1;

删除数据库：

drop database db1;

查询出mysql中所有数据库的名称：

show databases;

切换数据库：

use db1;

查看当前使用的数据库：

select database();

数据表的基本操作

创建表的语法结构：

 create table student(
 id int,
 name varchar(20),
 gender varchar(10),
 birthday date
 );

查看已经创建的数据表：

show tables;

查看表的基本信息mysql命令：

show create table student;

查看表结构：

desc student;

修改数据表

有时，希望对表中的某些信息进行修改，例如：修改表名、修改字段名、修改字段 数据类型…等等。在MySQL中使用alter table修改数据表.

修改表名:

alter table student rename to stu;

修改表name列字段：

alter table stu change name sname varchar(10);

修改表字段数据类型：

alter table stu modify sname int;

增加表字段：

alter table stu add address varchar(20);

删除表字段：

alter table stu drop address;

删除数据表：

drop table stu;

数据表的约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下：

约束条件	说明
primary key	主键约束用于唯一标识对应的记录
not null	非空约束
default	默认值约束
unique	唯一约束
foreign key	外键约束

主键约束

主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的。主键约束基本语法：

字段名 数据类型 primary key;

设置主键的第一种方式：

create table student(
id int primary key,
name varchar(20)
);

设置主键的第二种方式：

create table student(
id int,
name varchar(20),
primary key(id)
);

设置主键自增： auto_increment

alter table  tableName  modify id int auto_increment primary key

非空约束

非空约束即 NOT NULL指的是字段的值不能为空，基本的语法格式如下所示：

字段名 数据类型 NOT NULL;

设置方式 ：

create table student(
	id int primary key,
    name varchar(20) not null
);

默认值约束

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；其基本的语法格式如下所示：

字段名 数据类型 DEFAULT 默认值;

设置方式：

create table student(
	id int primary key,
    name varchar(20) not null,
    gender varchar(10) default 'male'
);

唯一约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现，其基本的语法格式如下所示：

字段名 数据类型 UNIQUE;

设置方式：

create table student04(
id int,
name varchar(20) unique
);

外键约束

外键约束即FOREIGN KEY常用于多张表之间的约束。基本语法如下：

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创建后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

表创建完成后添加外键：

create table class(
classid int primary key,
studentid int
);

alter table class add constraint fk_class_studentid foreign key(studentid) references student(id);

建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。

删除外键：

alter table class drop foreign key fk_class_studentid;

注意

1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

表中的数据操作

 create table student(
 id int,
 name varchar(30),
 age int,
 gender varchar(30)
 );

插入语句Insert

insert into student (id,name,age,gender) values (1,'bob',16,'male');

批量添加

insert into student (id,name,age,gender) values (2,'lucy',17,'female'),(3,'jack',19,'male'),(4,'tom',18,'male');

更新数据Update

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

将name为tom的记录的age设置为20并将其gender设置为female MySQL命令：

update student set age = 20,gender = 'female' where name = 'tom';

如果不加where条件所有的数据都会被修改：

update student set age = 18;

删除数据Delete

DELETE FROM 表名 [WHERE 条件表达式];

删除姓名为tom的学生：

delete from student where name = 'tom';

删除表中的所有数据：

delete from student;

Truncate和Delete的区别

TRUNCATE和DETELE都能实现删除表中的所有数据的功能，但两者也是有区别的：
1、DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录。
2、使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1
3、DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句

数据查询语句Select

-- 创建student表
CREATE TABLE student (
    sid CHAR(6),
    sname VARCHAR(50),
    age INT,
    gender VARCHAR(50) DEFAULT 'male'
);

-- 向student表插入数据
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');

查询所有字段所有数据: 一般只在测试的时候使用

select * from student;

查询指定字段（sid,sname）并且年龄大于15的学生

select sid,sname from student where age > 15;

过滤重复数据distinct

select distinct gender from student;

算数运算符

查询学生15年后的年龄

select sname,age+15 from student ;

聚合函数

计数count

select count(*) from student;

最大值max

select max(age) from student;

最小值min

select sname,min(age) from student;

求和sum

select sum(age) from student;

平均值avg

select avg(age) from student;

时间函数

select now();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

字符串函数

--连接函数
SELECT CONCAT ()
--
SELECT INSTR ();
--统计长度
SELECT LENGTH();

数学函数

-- 绝对值
SELECT ABS(-136);
-- 向下取整
SELECT FLOOR(3.14);
-- 向上取整
SELECT CEILING(3.14);

条件查询

使用In查询

IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

select * from student where sid in ('S_1002','S_1003');

使用BETWEEN AND关键字查询

select * from student where age between 15 and 18;

null

select * from student where sname is not null;

AND

select * from student where age>15 and gender='male';

OR

select * from student where age>15 or gender='male';

LIKE

%用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串

查询学生姓名以li开始的记录 MySQL命令：

select * from student where sname like 'li%';

下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。例如，字符串“ab_”匹配以字符串“ab”开始长度为3的字符串，如abc、abp等等；字符串“a__d”匹配在字符“a”和“d”之间包含两个字符的字符串，如"abcd"、"atud"等等。
查询学生姓名以zx开头且长度为4的记录 MySQL命令：

select * from student where sname like 'zx__';

LIMIT

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条
查询学生表中年纪最小的3位同学 MySQL命令：

select * from student order by age asc limit 3;

GROUP BY

-- 创建员工表
CREATE TABLE employee (
    id int,
    name varchar(50),
    salary int,
    departmentnumber int
);

-- 向员工表中插入数据
INSERT INTO employee values(1,'tome',2000,1001); 
INSERT INTO employee values(2,'lucy',9000,1002); 
INSERT INTO employee values(3,'joke',5000,1003); 
INSERT INTO employee values(4,'wang',3000,1004); 
INSERT INTO employee values(5,'chen',3000,1001); 
INSERT INTO employee values(6,'yukt',7000,1002); 
INSERT INTO employee values(7,'rett',6000,1003); 
INSERT INTO employee values(8,'mujk',4000,1004); 
INSERT INTO employee values(9,'poik',3000,1001);

统计各部门员工个数 MySQL命令：

select count(*), departmentnumber from employee group by departmentnumber;

GROUP BY和聚合函数以及HAVING一起使用

统计工资总和大于8000的部门 MySQL命令：

select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

Order By

从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序
其语法格式如下所示：

SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

查询所有学生并按照年纪大小升序排列 MySQL命令：

select * from student order by age asc;

as

不写也行~~~

给字段写别名：

SELECT * FROM 表名 [AS] 表的别名 WHERE .... ;

将student改为stu查询整表 MySQL命令：

select * from student as stu;

给字段起别名：

SELECT 字段名1 [AS] 别名1 , 字段名2 [AS] 别名2 , ... FROM 表名 WHERE ... ;

将student中的name取别名为“姓名” 查询整表 MySQL命令：

select sname as 姓名 from student;

表的关联关系

在实际开发中数据表之间存在着各种关联关系。在此，介绍MySQL中数据表的三种关联关系。

多对一

多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系 中，应将外键建在多的一方否则会造成数据的冗余。

多对多

多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。

一对一 在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
    cid int(4) NOT NULL PRIMARY KEY,
    cname varchar(30) 
);

-- 创建学生表
CREATE TABLE student(
    sid int(8) NOT NULL PRIMARY KEY,
    sname varchar(30),
    classid int(8) NOT NULL
);

-- 为学生表添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY(classid) REFERENCES class(cid);
-- 向班级表插入数据
INSERT INTO class(cid,cname)VALUES(1,'Java');
INSERT INTO class(cid,cname)VALUES(2,'Python');

-- 向学生表插入数据
INSERT INTO student(sid,sname,classid)VALUES(1,'tome',1);
INSERT INTO student(sid,sname,classid)VALUES(2,'lucy',1);
INSERT INTO student(sid,sname,classid)VALUES(3,'lili',2);
INSERT INTO student(sid,sname,classid)VALUES(4,'domi',2);

关联查询

查询Java班的所有学生 MySQL命令：

select * from student where classid=(select cid from class where cname='Java');

多表连接查询

交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接，其语法格式如下：

SELECT * FROM 表1 CROSS JOIN 表2;

在该语法中：CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。
由于这个交叉连接查询在实际运用中没有任何意义，所以只做为了解即可

为了比较inner join 和left join , right join的区别可以在class表中添加一条数据

insert into class (cid,cname) values (3,'c++');

在student的表中没有classid为3的数据

内连接查询

内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的 记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。其语法格式如下：

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

select * from student s join class c on s.classid = c.cid;

执行成功后可以看到并没有查出class表中cid为3的那条数据

左外连接与右外连接

左外连接和右外连接相对来说差不多是一个东西，与from字段后表的先后顺序有关

select * from class c left join student s on c.cid = s.classid;

对比内连接查询来看，多出了cid为3 的这条数据,左外连接可以查出右边表中没有而左边表有的数据

select * from student s right join class c on s.classid = c.cid;

右外连接与左外连接相反，可以查出右边表有而左边表没有的数据

子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
  cid int (4) NOT NULL PRIMARY KEY, 
  cname varchar(20)
);

-- 创建学生表
CREATE TABLE student (
  sid int (4) NOT NULL PRIMARY KEY, 
  sname varchar (20), 
  sage int (2), 
  classid int (4) NOT NULL
);

-- 向班级表插入数据
INSERT INTO class VALUES(1001,'Java');
INSERT INTO class VALUES(1002,'C++');
INSERT INTO class VALUES(1003,'Python');
INSERT INTO class VALUES(1004,'PHP');
INSERT INTO class VALUES(1005,'Android');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'张三',20,1001);
INSERT INTO student VALUES(2,'李四',21,1002);
INSERT INTO student VALUES(3,'王五',24,1003);
INSERT INTO student VALUES(4,'赵六',23,1004);
INSERT INTO student VALUES(5,'小明',21,1001);
INSERT INTO student VALUES(6,'小红',26,1001);
INSERT INTO student VALUES(7,'小亮',27,1002);

带比较运算符的子查询

select * from class where cid>(select classid from student where sname='张三');

带exists关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询， 它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会 执行

select * from class where exists (select * from student where sname='王五');

带any关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

select * from class where cid > any (select classid from student);

带All关键字的子查询

select * from class where cid > all (select classid from student);

查询总结

查询语句的书写顺序和执行顺序

select ==>from ==>where==>group by==>having==>order by==>limit

事务

事务处理是将多个操作或者命令一起执行，所有命令全部成功执行才意味着该事务的成功，任何一个命令失败都意味着该事务的失败。

数据库事务处理的相关命令

进行的操作	命令
查看存储索引	show create table 表名
更改索引	alter table 表名 engine = 新索引名
回滚	rollback
声明事务开始	begin
事务提交	commit
查看自动提交功能状态	select @@autocommit;
设置自动提交功能	set autocommit = 0/1;
设置分离水平	set session transaction iaolation level 分离水平;

存储引擎

MySQL数据库的存储引擎是可以选择改变和替换的（可替换存储引擎构架，Pluggable Storage Engine Architecture）。MySQL主要有8种存储引擎：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TXZUgA3D-1627565645760)(…/img/1627545127033.png)]

查看数据表使用的引擎：

show create table 表名;

engine后面的就是引擎。

更改存储引擎:

alter table 表名 engine=引擎名;

InnoDB与MyISAM的区别

	MYISAM	INNODB
事务支持	N	Y
数据行锁定	N(表锁定）	Y
外键约束	N	Y
全文索引	Y	N
表空间大小	较小	较大，约为两倍

事务处理

事务要么都成功，要么都失败。全部执行成功进行commit提交，如果任何一个任务失败，强制rollback回到初始状态。

事务原则：ACID，原子性（一起成功，一起失败），一致性（最终一致性，比如转账前后两人的金额之和不变），持久性（事务没有提交恢复原状，提交后就永久保存，不可逆了），隔离性（互不干扰，隔离级别）：

脏读：一个数据读取了另一个事务没有提交的数据。

不可重复读：多次读取的结果不一致

幻读：事务内读取到别的事务插入的数据，导致前后读取不一致

事务处理涉及到三个最重要的命令：BEGIN,ROLLBACK,COMMIT，分别表示声明事务开始，回滚和确认提交。

回滚事务演示：

现将表格student的存储引擎设置为支持事务的InnoDB

确认表格数据；SELECT * FROM student;
事务开始；BEGIN;
删除表格数据；DELETE FROM student;
再次查看表格数据；SELECT * FROM student;
回滚到初始状态；ROLLBACK;
再次查看表格数据；SELECT * FROM student;

可以看到大概执行rollback后，删除的记录又恢复到了begin之前的状态。

自动提交

当搜索引擎为MyISAM时，因为不支持事务处理，因此命令一旦执行，就一定会提交，这种默认的提交方式被称为自动提交。

而当搜索引擎设置为InnoDB时，可以设置自动提交功能是否开启，当自动提交功能为ON时，命令执行就会提交（COMMIT）,而自动提交设置为OFF 时，必须执行COMMIT才提交，可以使用ROLLBACK进行回滚。

查询当前自动提交功能状态：

SELECT @@AUTOCOMMIT;

设置自动提交功能：

set autocommit = 0或1;

当autocommit

部分回滚 savepoint

1.保存点

savepoint 保存点名;

2.回滚到保存点

rollback to savepoint 保存点名;

不能事务处理的命令（直接提交）

大部分命令都可以通过事务处理（BEGIN -ROLLBACK- COMMIT）进行操作，但是：

• DROP DATABASE;
• DROP TABLE;
• DROP;
• ALTER TABLE

不能通过事务处理，会直接COMMIT;

锁定与事务处理

前面讲到的ROLLBACK 等操作指令都是基于一个用户进行的。但是事务类型往往不只一个用户，多个用户同时操作，如被人广泛诟病的“12306”火车票购票系统，全国各地的售票窗口以及互联网购票注册账户，成千上万的用户同时使用，因此事务处理必须能够处理多个用户同时操作的情况，这就需要锁定。

举个例子，如果某班火车只剩最后一张票，A和B 同时登陆网站购票，得到的反馈是还剩一张，于是A，B 都赶紧下单，处理这种冲突事件，就需要对该事务进行锁定（LOCK），接触锁定被称为解锁（Unlock）。

锁定的分类

锁定分为共享锁定（Shared Lock）和排他锁定（Exclusive Lock）：

• 共享锁定是将对象数据变为只读形式，不能进行更新，所以也成为读取锁定；
• 排他锁定是当执行INSERT/UPDATE/DELETE的时候，其它事务不能读取该数据，因此也成为写入锁定。

锁定的粒度

锁定对象的大小是锁定的粒度，有三种粒度：

• 记录
• 表
• 数据库

事务处理的分离水平

需要使用锁定来有效解决事务冲突的情况，但是锁定也会使性能下降（因为别人无法访问），因此频繁锁定不一定合理，数据库中，使用分离水平来表示事务处理之间的影响程度。

事务处理的分离水平对应的数据整合情况：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-koOqCXpn-1627565645763)(…/img/1627550571574.png)]

设置分离水平可以使用命令：

set session transaction isolation level 分离水平;

（1）非提交读取

非提交读取指的是别的事务能够读取到还没有提交的更新数据，只发生在分离水平为READ UNCOMMITED的情况下。 因为对事务处理的读取没有任何限制，所以一般不推荐使用。

当A还没提交，B就可以看到更新的数据，这个时候很可能出现问题，比如A后来执行ROLLBACK，B看到的数据实际上是错误的数据。

（2）不可重复读取

不可重复读取是指在某事务处理过程中对数据进行读取，由于该事务更新操作导致多次读取数据时发生了改变。

（3）幻象读取

幻象读取指的是，在某事物处理数据过程中对数据多次读取，由于该事务的插入/删除操作而导致在多次读取过程中读取到不存在或者消失的数据。

索引

可以更快速的获取数据的数据结构。

索引的分类：

• 主键索引 primary key

  – 唯一的标识，只有一个键作为主键

• 唯一索引 unique key

  – 避免重复的列出现

• 常规索引 key/index

  – 默认的，index ,key关键字可以设置

• 全文索引 fullkey

  – 一些特定引擎才可以使用

  索引的应用：

  显示所有的索引信息：

  show index from 表名;
  

  创建一个索引：

  create index 索引名 on 表名(列名);
  

  添加一个索引：

  alter table 表名 add index 索引名(列名);
  

  删除一个索引：

  alter table 表名 drop index 索引名；
  

  用explain+sql可以分析sql语句

  添加索引可以在大数量的时候可以优化查询速度。

  索引原则

  小数据量的表不要加索引

  经常修改的数据不加索引，经常查询的数据加索引。

  权限管理

  用户管理

  创建用户：

  create user 用户名 identified by '密码';
  

  修改密码：

  set password for 用户名 = '新密码';
  

  用户授权

  grant all privileges on *.* to 用户;
  

  查看权限：

  show grant for 用户;
  

  删除用户:

  drop user 用户;
  

数据库备份

保证数据不丢失：

• 可视化工具右键导出

• 使用mysqldump

  mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置
  

  导入备份文件

  source 数据表路径