MySQL知识总结

小鹏仔0514

于 2022-08-20 18:50:56 发布

阅读量360

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_31063727/article/details/126273560

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

用到的表：
下面例子中用到的表

部门表(dept)

员工表（emp)

工资等级表(salgrade)

知识点

1.在查询全部字段的时候使用*号与字段名的区别。
使用*好代替全部字段的缺点在于效率低（因为后面还是需要将*号替换成全部字段名），可读性差。

2.起别名的时候可以不用as 可以用空格代替，如果起的别名中存在空格，那么可以将别名用单引号括起来

3.数据库中的字符串都采用单引号括起来，这是标准的。

条件查询

1.>, <,=, >=, <= ,<>(表示不等于)
2. between a and b (a要比b小，即左小右大)
3. 查询是否为空值的时候用is null 和 is not null.
4. 表示并且用and，或用or,多个或用in（需要注意的是in后面跟具体的值）
5. not 表示取反。
6. like表示模糊查询，用%号表示匹配任意个字符，下划线表示只匹配一个字符。（\表示转义字符，如果要匹配的字符串中有下划线，那么就需要转义符）

selct ename from emp where ename like '%明'%

表示查找用户表中姓名中包含明的用户。

not in 在使用的时候，后面的范围记得排除NULL

排序

1.使用order by xx字段（默认升序）。指定降序要在字段后面加desc,如

select ename, sal from user order by sal desc

如果要按照多个字段排序，比如按照薪资升序，如果薪资一样，再按照名字升序。(asc表升序）那么字段在前起主导作用

select ename, sal from ename order by sal asc, ename asc

还可以根据字段的位置排序，即不写出字段名。如

select ename, sal from ename order by 2;

这里2就是表示按sal字段排序。

常见单行处理函数（可嵌套）

1.lower 将输出转换成小写
2.upper转换成大写
3.substr取子串 (起始下标从1开始）
substr(字符串,起始下标,截取的长度)
4.length 取长度
5.concat 字符串拼接

select concat(ename, sal) from ename

这个例子的输出结果就是将name和sal拼接起来。
6.trim 去空格

select * from eanme where ename = trim('含空格数据')

7.str_to_date 将字符串转成成日期(varchar -> date)

str_to_date('字符串日期', '日期格式')
mysql 日期格式
%Y 	年
%m 	月
%d	日
%h	时
%i	分	
%s	秒

如果字符串日期格式为：%Y-%m-%d，则不需要使用str_to_date函数。
8. date_format 格式化日期（date->varchar)

date_format('日期字段', '%m/%d/%y(需要展示的日期格式)')

format 设置千分位

select ename, format(sal,'$999,999') from emp;

10.round 四舍五入

select round(1234.567, 0) as result from xxx;

round中的0表示保留几位小数。
11. rand() 生成随机数
12. ifnull 可以将null值转换成一个具体的值（NULL值只要参与运算，最终结果一定是NULL，所以在运算的时候需要使用ifnull).
ifnull(数据，当数据为NULL时当作的值).

 select ename, (sal+ifnull(commn, 0)) * 12 as yearsal from emp

12 case（匹配的某个字段） when … then …when … then …else … end
比如：当员工的工作岗位时MANAGER的时候，工资上调10%，当工作岗位时SALESMAN的时候，工资上调50%，其它正常。

select name, job, sal as oldsal,
(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
from emp

13.timestampdiff(间隔类型，前一个日期，后一个日期)
间隔类型有：
Second 秒，
Minute 分钟，
HOUR 小时，
DAY 天，
WEEK 星期，
MONTH 月，
Quarter 季度，
YEAR 年

14.now()函数，获取系统当前时间，是datetime类型。

多行处理函数

特点：输入多行，最终输出一行
注意：分组函数在使用的时候必须进行分组，然后才能使用。
如果没有对数据进行分组，整张表默认为一组。
分组函数自动忽略NULL ，不需要提前对NULL进行处理。
分组函数不能用在where语句中
1.count 计数
2. sum 求和
3. avg 平均值
4. max 最大值
5. min 最小值

分组查询

关键字执行顺序：

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

执行顺序：
1.from
2.where
3.group by
4.having
5.select
6.order by
比如：

select
	job, sum(sal)
from
	emp
group by
	job;

这个语句的执行顺序为：先从emp表中查询数据据，根据job字段进行分组，然后对每一组数据进行sum(sal)在从这些数据中选取(select)字段显示。
在一条select语句中，如果有group by语句的话，select后面只能跟：参加分组的字段以及分组函数，其它一律不能跟。

having语句在分组过后再过滤数据，要在group by语句后使用。
where和having优先选择where,where实在完成不了的，再选择having

distinct去重

使用distinct关键子去除重复记录，需要注意的是，原表数据不会被修改，只是查询结果去重。
distinct只能出现在所有字段的前方，表示联合所有字段联合起来去重,可以出现在分组函数中。

select distinct job, deptno from emp;

连接查询⭐⭐⭐⭐⭐

内连接

1.等值连接
例子：查询每个员工所在部门名称，显示员工名和部门名。
SQL92语法：

select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno

SQL99语法

select e.ename, d.dname from emp e  inner join dept d on e.deptno=d.deptno

SQL92语法的缺点是结构不清晰，表的连接条件和后期进一步筛选的条件，都放到了where后面。
SQL99的优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where。
SQL99语法

select 
	...
from 
	a
inner join
	b
on 
	a和b的连接条件
where
	筛选条件

2.非等值连接(条件不是一个等量关系）
例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级。

select
	e.ename, e.sal, s.grade
from 
	emp e
inner join 
	salgrade s
on 
	e.sal between s.losal and s.hisal;

3.自连接
例：查询员工的上级领导，要求显示员工名和对应的领导名

select
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
join
	emp b
on a.mgr = b.empno;

自连接：一张表看作两张表。

外连接

在外连接表之间有主次关系，而内连接表之间没有主次关系。
1.右外连接（右连接）

select 
	e.ename, d.dname
from 
	emp e right join dept d 
on
	e.deptno = d.deptno

其中right关键字表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。

2.左外连接
将上述right关键字换成left即左外连接（左连接）。

多表连接（两张表以上）

语法:

select
	...
from
	a
join 
	b
on 
	a和b的连接条件
join
	c
on 
	a和c的连接条件
right join
	d
on 
	a和d的连接条件

一条SQL语句中内连接和外连接可以混合使用。
例子：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级。

select
	e.ename, d.dname, e.sal, s.grade
from 
	emp e 
join 
	dept d 
on
	 e.deptno = d.deptno
join 
	salgrade s 
on
	 e.sal between s.losal and s.hisal;

是内连接还是外连接，重点在于你想要的查询的数据是哪一张表，这张表的对应其它表中的数据是否存在NULL值，如果存在则用外连接，如果不存在，内连接即可。

子查询

1.什么是子查询？
select语句中嵌套select语句，被嵌套的select语句称为子查询
2.子查询都可以出现在哪里？

select
	..(select).
from
	..(select).
where
	..(select).

where子句中的子查询

例：找出比最低工资高的员工姓名和工资。

select 
	ename, sal
from emp
	where sal > (select min(sal) from emp)

from 子句中的子查询

注意：from后面的子查询可以即将查询结果当作一张临时的表。
例：找出每个岗位的平均工资的薪资等级。

select
	s.grade, t.*
from 
	(select job, avg(sal) as avgsal from emp group by job) as t 
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

select后面出现的子查询

例：找出每个员工的部门名称，要求显示员工名、部门名

select 
	e.ename, e.deptno,
 (select d.dname from dept d where e.deptno=d.deptno) as dname
 from
  emp e

注意，这种操作一次只能查询一个字段，如果子查询中包含多个字段，则会报错。

union合并查询结果集

例：查询工作岗位是MANAGER和SALESMAN的员工

select ename, job from emp where job='MANAGER'
union
select ename, job from emp where job = 'SALESMAN'

union的效率要高，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，也就是消耗时间随表的大小成倍的增加。
但是Union可以减少匹配次数，并且在减少匹配次数的情况下还可以完成两个结果集的拼接。
举个例子：
a表10条记录，b表10条记录、c表10条记录。
如果 a连接b连接c，则匹配次数为101010=1000

如果a连接b，a连接c然后使用union合并结果集，则匹配次数为：1010+1010=200次。(union把乘法变成了加法）。

注意事项：union在进行结果集的合并的时候，要求列数相同、数据类型相同

limit 关键字

limit是将查询结果集的一部分取出来，通常用在分页查询中。
用法：

select
	...
from
	...
order by
	...
limit(起始位置，取多少个)

起始位置默认从0开始。
注意：在mysql当中limit在order by之后执行！

分页

假设每页显示3条记录
第一页：limit 0, 3 [0, 1, 2]
第二页：limit 3, 3 [3, 4, 5]
第三页：limit 6, 3 [6, 7, 8]
第四页：limit 9, 3 [9, 10, 11]

每页显示pageSize条记录:
第pageNo页：limit (pageNo - 1) * pageSize , pageSize

关于DQL语句的大总结

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

执行顺序：
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit

练习题

-- 取得每个部门最高薪水的人员名称
select
	ename, sal, e.deptno
from 
	emp e
join
	(select deptno, max(sal) as maxSal from emp group by deptno) as t    
on e.sal = t.maxSal and e.deptno = t.deptno;

-- 哪些人的薪水在部门的平均薪水之上
select
	ename, sal, e.deptno
from 
	emp e
join
	(select deptno, avg(sal) as avgSal from emp group by deptno) as t    
on e.sal > t.avgSal and e.deptno = t.deptno;

-- 取得部门中（所有人的）平均薪水的等级
select
	grade, t.*
from
	salgrade s
join
	(select deptno, avg(sal) as avgSal from emp group by deptno) as t
on t.avgSal between s.losal and s.hisal;

-- 取得部门中（所有人的）平均的薪水等级
select 
	e.deptno, avg(s.grade)
from 
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal
group by
	e.deptno;
-- 不准用组函数(MAX)，取得最高薪水（给出两种解决方案）
-- 第一种 sal降序
select
	ename, sal	
from
	emp
order by
	sal desc
limit
	1;
-- 第二种 表的自连接
select
	sal
from
	emp
where
	sal
not in
	(select distinct a.sal from emp a join emp b on a.sal < b.sal);

-- 取得平均薪水最高的部分的部门编号（至少给出两种解决方案）
-- 第一种，利用降序
select
	deptno, avg(sal) as avgSal
from
	emp 
group by
	deptno
order by
	avgSal desc
limit 1;

-- 第二种 临时表
select
	deptno, avg(sal) as avgSal
from
	emp
group by
	deptno
having
	avgSal = (select max(t.avgSal) from (select deptno, avg(sal) as avgSal from emp group by deptno) as t);

-- 取得平均薪水最高的部门的部门名称
-- 第一种
select
	dname
from 
	dept d
join
	(select deptno, avg(sal) as avgSal from emp group by deptno order by avgSal desc limit 1) as t
on
	d.deptno = t.deptno;

-- 第二种
select
	d.dname, avg(sal) as avgSal
from
	emp e
join
	dept d
on 
	d.deptno = e.deptno
group by
	d.dname
order by
	avgSal desc
limit 1;

-- 求平均薪水的等级最低的部门的部门名称
-- 第一步按照部门名称分组，找出每个部门的平均薪水
select e.deptno, avg(sal) from emp e group by e.deptno;
-- 第二步：找出每个部门的平均薪水的等级对应的部门编号
select
	t.*, s.grade
from 
	(select e.deptno, avg(sal) as avgSal from emp e group by e.deptno) as t
join 
	salgrade s
on t.avgSal between s.losal and s.hisal;
-- 找最低等级
select grade from salgrade s where
 (select t.avgSal from  (select e.deptno, avg(sal) as avgSal from emp e group by e.deptno) as t join  salgrade s
on t.avgSal between s.losal and s.hisal order by t.avgSal limit 1) 
 between s.losal and s.hisal;
-- 第三步：找出最低的等级部门编号对应的部门名称
select
	t.*, s.grade
from 
	(select d.dname, avg(e.sal) as avgSal from emp e  join dept d on e.deptno = d.deptno group by d.dname) as t
join 
	salgrade s
on t.avgSal between s.losal and s.hisal
where 
	s.grade = (select grade from salgrade s where (select t.avgSal from  (select e.deptno, avg(sal) as avgSal from emp e group by e.deptno) as t join  salgrade s on t.avgSal between s.losal and s.hisal order by t.avgSal limit 1)  between s.losal and s.hisal);
    

 -- 取得比普通员工(员工代码没有在mgr字段上出现)的最高薪水还高的领导人姓名
 -- 第一步：查找非领导的员工，即编号不在mgr中都是普通员工
 select distinct mgr from emp where mgr is not null;
 -- 第二步：找出普通员工的最高薪水
 select  max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
 
-- 第三步：找出大于最高薪水的领导人姓名
select e.ename, e.sal from emp e,(select max(sal) as maxSal from emp where empno not in (select distinct mgr from emp where mgr is not null)) as t 
where e.sal > t.maxSal;

-- 取得薪水最高的前五名员工
select ename, sal from emp  order by sal desc limit 5;

--  取得薪水最高的第六到第十名员工
select ename, sal from emp  order by sal desc limit 5, 5;

-- 取得最后入职的5名员工
select ename, hiredate from emp order by hiredate desc limit 5;

-- 取得每个薪水等级有多少员工
select
	s.grade, count(s.grade)
from
	emp e
join 
	salgrade s
on
	e.sal between s.losal and hisal
group by
	s.grade;

-- 列出所有员工及领导的姓名
select
	a.ename '员工', b.ename '领导'
from 
	emp a
left join
	emp b
on
	a.mgr = b.empno;

-- 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称。
select
	a.empno '员工编号', a.ename '员工', a.hiredate,  d.dname '部门名称', b.ename '领导', b.hiredate
from 
	emp a
join
	emp b
on 
	a.mgr = b.empno and a.hiredate < b.hiredate
join
	dept d
on 
	a.deptno = d.deptno;
    
-- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select 
	d.dname, e.*
from 
	dept d
left join
	emp e
on 
	d.deptno = e.deptno;
    
-- 列出至少有5个员工的所有部门
select 
	d.dname, count(*)
from 
	dept d
join
	emp e
on 
	d.deptno = e.deptno
group by 
	d.deptno
having
	count(d.deptno) >= 5;

-- 列出薪水比"SMITH"多的所有员工信息
select
	ename, sal
from 
	emp
where
	sal > (select sal  from emp where  ename = 'SMITH');

-- 列出所有"CLERK"的姓名及其部门名称、部门的人数
select * from emp where job = 'CLERK';
-- 查询这些员工的部门编号、名称
select 
	e.ename, e.job ,d.dname, d.deptno
from 
	emp e
join
	dept d
on
	d.deptno = e.deptno
where 
	e.job = 'CLERK';
  
-- 统计每个部门人数
select deptno, count(*) as deptCount from emp group by deptno;

-- 通过部门名称进行连接
select
	t1.*, t2.deptCount
from
	(select e.ename, e.job, d.dname, d.deptno from emp  e join dept d on e.deptno = d.deptno where job = 'CLERK') t1
join
	(select deptno, count(*) as deptCount from emp group by deptno) t2
on
	t1.deptno = t2.deptno;
    
-- 取得最低薪水大于1500的各种工作及从事此工作的全部雇员人数。
select job from emp group by job having min(sal) > 1500;

select job, count(*) as jobCount from emp group by job;

-- 连接
select 
	t1.job, t2.jobCount
from
	(select job from emp group by job having min(sal) > 1500) t1
join
	(select job, count(*) as jobCount from emp group by job) t2
on
	t1.job = t2.job;

-- 方法2
select job, count(*) from emp group by job having min(sal) > 1500;

-- 列出在部门"SALES"工作的员工的姓名，假定不知道销售部部门编号
-- 获得其它部门编号
select deptno from dept where dname != 'SALES';
-- 查找不在其它部门的员工
select
	e.ename
from
	emp e
where
	e.deptno not in (select deptno from dept where dname != 'SALES');

-- 列出薪水高于公司平均薪水的所有员工，所在部门、上级领导，雇员的工资等等级。
-- 获得高于公司平均薪水的所有员工
select a.ename '员工', b.ename '领导',a.sal, a.deptno from emp a join emp b on a.mgr=b.empno where a.sal > (select avg(sal) from emp);
-- 方法1
select
	t1.*,  d.dname, s.grade
from
	(select a.ename '员工', b.ename '领导',a.sal, a.deptno from emp a left join emp b on a.mgr=b.empno where a.sal > (select avg(sal) from emp)) t1
join
	dept d
on 
	t1.deptno = d.deptno
join
	salgrade s
on 
	t1.sal between s.losal and s.hisal;

-- 方法2
select
	e.ename, d.dname, l.ename, s.grade
from 
	emp e
join
	dept d
on 
	e.deptno = d.deptno
left join
	emp l
on 
	e.mgr = l.empno
join 
	salgrade s
on 
	e.sal between s.losal and s.hisal
where
	e.sal > (select avg(sal) from emp);
    
-- 列出与“SCOTT”从事相同工作的所有员工及部门名称
select
	e.ename, d.dname
from
	emp e
join 
	dept d
on 
	e.deptno = d.deptno
where
	e.job = (select job from emp where ename ='SCOTT')
having
	e.ename != 'SCOTT';

-- 24、列出薪水等于部门30中员工薪水的其他员工的姓名和薪水
select
	ename, sal
from 
	emp
where
	sal in (select distinct sal from emp where deptno=30)
and
	deptno <> 30;
-- 25、 列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水、部门名称。
select
	e.ename, e.sal, d.dname
from 
	emp e
join 
	dept d
on 
	e.deptno = d.deptno
where
	e.sal > (select max(sal) from emp where deptno = 30);

-- 26、列出在每个部门工作的员工数量、平均工资和平均服务期限
select
	d.*, count(e.ename), ifnull(avg(e.sal), 0), ifnull(timestampdiff(YEAR, hiredate, now()), 0)
from 
	emp e
right join
	dept d
on 
	e.deptno = d.deptno
group by
	d.deptno, d.dname, d.loc;
     
-- 27、列出所有员工的姓名、部门名称和工资
select
	e.ename, d.dname, e.sal
from 
	emp e
left join 
	dept d
on
	e.deptno = d.deptno;

-- 28、列出所有部门的详细信息和人数
select
	d.*, count(e.ename) '人数'
from
	emp e
right join 
	dept d
on 
	e.deptno = d.deptno
group by
	d.deptno, d.dname, d.loc;

-- 29、列出各种工作的最低工资及从事此工作的雇员姓名
select
	e.ename, t.*
from
	emp e
join
	(select job, min(sal) as minSal from	emp group by	job) as t
on 
e.job = t.job and t.minSal = e.sal;

-- 30、列出各个部门的MANAGER的最低薪水
-- 方法1
select
	d.deptno, e.ename, min(e.sal), e.job
from
	emp e
join 
	dept d
on 
	e.deptno = d.deptno
group by
	e.deptno, e.job
having
	e.job='MANAGER';

-- 方法2
select
	deptno, min(sal)
from 
	emp
where
	job='MANAGER'
group by
	deptno;
    
-- 31、列出所有员工的年工资、按年薪从低到高排序
select
	ename, (sal * 12) as income
from
	emp
order by
	sal;
    
-- 32、求出员工领导的薪水超过3000的员工名称与领导名称
select
	a.ename '员工', b.ename '领导'
from 
	emp a
join 
	emp b
on 
	a.mgr = b.empno
where
	b.sal > 3000;
    
-- 33、求出部门名称中，带'S'字符的部门员工的工资合计、部门人数
select
	d.*, sum(e.sal), count(e.deptno)
from 
	emp e
right join
	dept d
on 
	e.deptno = d.deptno
where 
	d.dname like '%S%'
group by
	d.deptno, d.dname, d.loc;

-- 34、给任职日期超过30年的员工加薪10%
select
	ename, sal, (sal*1.1)
from 
	emp
where
	timestampdiff(YEAR, hiredate, now()) > 30;
    
update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

表

表名建议以t_或者tbl_开始

字段的数据类型（常见）

varchar(最长255）可以根据实际传递的数据长度动态分配空间。
char(最长255）定长字符串，即分配的空间是固定的不管实际的数据长度是多少。
固定长度的字段采用char，可变长度采用varchar。
int(最长11）
bigint 长整型，等同于java中的long.
float
double
date 短日期类型。(只包括年月日信息）
mysql短日期默认格式：%Y-%m-%d
datetime 长日期类型。（包括年月日时分秒信息）
mysql长日期默认格式为：%Y-%m-%d %h:%i:%s
clob 字符大对象，最多可以存储4G的字符串。比如：存储一篇文章、摘要等。超过255个字符都要采用CLOB字符大对象。
blob **二进制大对象，专门用来存储图片、声音、视频等流媒体数据。**往BLOB类型的字段上插入数据的时候，要使用IO流才行。

修改Update

语法格式：

   update 表名 set 字段名=值1，字段名=值2...where 条件；

删除数据Delete

语法格式：

delete from 表名 where 条件；

注意：没有条件，整张表的数据会全部删除！
这种删除数据效率低，因为真实存储空间不会被释放，只是数据被清除了，优点在于可以恢复数据（支持回滚）。

快速删除数据方式：
truncate语句，优点是删除效率高，表被一次截断，是物理删除，缺点是不支持回滚。

truncate table 表名;

约束

约束包括哪些？

非空约束 not null (只有列级约束）
唯一性约束 unique 可以为NULL （有表级约束）
要求两个字段或者多个字段联合具有唯一性时，要这样创建，比如要求用户名和邮箱联合唯一

create table t_user(
	`id` int,
	`name` varchar(255),
	`email` varchar(255),
	unique(`name`, `email`)
);

主键约束 primary key
主键字段：添加了主键约束的字段称为主键字段，主键字段中的每一个值称为主键值。
主键的特征：not null + unique(主键值不能是NULL，同时也不能重复，类似于人的身份证号）
主键约束也是表级约束，可以多个字段共同组成主键！
一个字段做主键称为单一主键，多个字段联合起来做主键，称为复合主键。
主键除了根据字段的个数分成单一主键和复合主键，还可以根据其含义分为：自然主键和业务主键。
自然主键的主键值是一个自然数和业务无关，而业务主键的主键值和业务关系紧密（如拿银行卡账号做主键值）。
通常使用自然主键比较多，因为主键只要做到不重复具有唯一性即可，不需要有意义，如果主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以不建议使用业务主键。
通常配合auto_increment使用，自动维护主键值。
外键约束 foreign key
外键约束涉及到的相关术语：
外键约束:一种约束
外键字段：添加了外键约束的字段
外键值：外键字段中的每一个值。
创建示例：学生表（子表）和班级表（父表）
```
create table t_class(
	classno int primary key,
	classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) references t_class(classno)
);
```
创建包含外键约束表的时候，先创建父表再创建子表。
外键值可以为NULL，外键引用的字段可以不是主键，但是至少具有unique约束。
检查约束 check （mysql不支持，oracle支持）

注意：在Mysql当中，如果一个字段同时被not null和unique约束的时候，该字段自动变成主键字段（Oracle不一样）。

Mysql常用存储引擎

MyISAM存储引擎

它管理的表具有以下特征:

使用三个文件表示每个表:
1.格式文件―存储表结构的定义(mytable.frm>
2.数据文件一存储表行的内容《mytable. YD)
3.索引文件–存储表上索引(mytable.MYI)︰索引是一本书的目录，缩小扫描范围，提高查询效率。
可被转换为压缩、只读表来节省空间

MyISAM存储引擎特点：可被转换为压缩、只读表来节省空间，是这种存储引擎的优势。

InnoDB存储引擎

这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。InnoDB支持事物，支持数据库崩溃后自动恢复机制，其最主要的特点就是非常安全。

它管理的表具有下列主要特征:

每个InnoDB_表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容（表空间是一个逻辑名称，表空间存储数据＋索引。
提供一组用来记录事务性活动的日志文件
COMMTT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理-提供全AcID兼容
在MysQL服务器崩溃后提供自动恢复
多版本(MvcC)和行级锁定
支持外键及引用的完整性,包括级联删除和更新

InnoDB最大的特点就是支持事物（以保证数据的安全性），效率不是很高，并且也不能压缩，不能转换为只读。

Memory存储引擎

使用MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。
MEMORY存储引擎管理的表具有下列特征:

在数据库目录内，每个表均以.frm格式的文件表示。
表数据及索引被存储在内存中。（目的就是快，查询快!)
表级锁机制。
不能包含PEXT或BLOB字段

MEMORY存储引擎以前被称为HEAP 引擎。
MEMORY引擎优点:查询效率是最高的。
MEMORY引擎缺点:不安全，关机之后数据消失。因为数据和索引都是在内存当中。

事务⭐⭐⭐⭐⭐

什么是事务？

答：一个事务其实就是一个完整的业务逻辑。本质上，一个事务就是多条DML语句的执行集合。这个集合要么执行成功，要么失败。

那么什么是一个完整的业务逻辑？
举个转账的例子：从A账户向B账户中转1000，那么要将A账户的钱减去1000，B账户的钱加上1000，这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分！

只有DML语句（增删改）才会有事务一说。

如何做到多条DML语句同时成功或失败？

根基:InnoDB存储引擎，提供一组用来记录事务性活动的日志文件。

在事务的执行过程中，每一条DM的操作都会记录到"事务性活动的日志文件w中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。
提交事务：
1.清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
2.提交事务标志着事务的结束，并且是一种全部成功的结束。

回滚事务：
1.将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件.
2.回滚事务标志着事务的结束，是一种全部失败的结束。

如何提交事务、回滚事务？

提交事务： commit语句
回滚事务: rollback语句(回滚永远都是只能回滚到上一次的提交点!)

注意：在Mysql当中，默认情况下是自动提交事务的，也就是说每执行一条DML语句，则提交一次。

使用start transaction来关闭自动提交机制。

事务的四个特性

A:原子性
说明事务时最小的工作单元，不可再分。
C:一致性
所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性.
I:隔离性
A事务和B事务之间具有一定的隔离。比如教室A和教室B之间有一堵墙，这堵墙就是隔离性。
D:持久性
事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

隔离性（重点）

事务和事务之间的隔离级别可分为4个等级：

读未提交：read uncommitted (最低的隔离级别)
读已提交: read committed
可重复读: repeatable read
序列化/串行化：serializable (最高的隔离级别）
效率最低，这种级别下，事务排队执行，不能并发！

什么是读未提交？
答：事务A可以读取到事务B未提交的数据。这种隔离级别存在的问题就是：脏读现象！(dirty read)。
这种隔离级别一般都是理论上的，大多数数据库隔离级别从第二个开始。

什么是读已提交？
答：事务A只能读取到事务B提交之后的数据，这种隔离级别解决了脏读现象；这种隔离级别存在的问题是：不可重复读取数据！

什么又是不可重复读取数据？
答：比如，在事务开启之后，第一次读取到的数据是3条，当前事务还没有结束，可能在第二次读取的时候，读到的数据是4条，和之前读取的数据条数不等。

什么是可重复读取？
答：事务A开启之后，不管多久，每一次在事务A中读取到的数据都是一致的，即使事务B已经将数据修改并且提交了，事务A读取到的数据还是不变，这就是可重复读。
可重复读解决了不可重复读取数据的问题，可重复读存在的问题是可能会出现幻读，永远读取的都是刚开启事务时的数据。

查看隔离级别语句：

select @@tx_isolation;

小鹏仔0514

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL知识总结

MySQL相关内容总结
复制链接

扫一扫

专栏目录