目录
用到的表:
部门表(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;