数据库概述

什么是数据库？
数据库，通常是一个或一组文件，保存了一些符合特定规格的数据。数据库对应的英语单词是 DataBase, 简称 :DB。
数据库管理系统
DataBase Management System，简称DBMS。数据库管理系统是专门用来管理数据库中的数据的，数据库管理系统可以对数据库当中的数据进行增删改查。
常见的数据库：MySQL，Oracle，SqlServer等。
SQL-结构化查询语句
程序员通过编写SQL语句，DBMS负责执行SQL语句，最终完成数据库中数据的增删改查操作。
sql语句不区分大小写。

命名规范：所有的标识符全部小写，单词和单词之间使用下划线进行衔接。

MySQL

MySQL 是⼀种关系型数据库，在Java企业级开发中⾮常常⽤，因为 MySQL 是开源免费的，并且⽅便扩展。阿⾥巴巴数据库系统也⼤量⽤到了 MySQL，因此它的稳定性是有保障的。 MySQL是开放源代码的，因此任何⼈都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进⾏修改。 MySQL的默认端⼝号是3306。

使用终端操作数据库

  1. 登录

mysql -uroot -p123456 隐式登录： mysql -uroot -p (然后再输入密码）

  1. 查询服务器中所有的数据库——show databases;
  2. 使用某个数据库——use test; (使用test数据库)
  3. 创建数据库——create database myt;
  4. 删除数据库——drop database myt;
  5. 查看mysql数据库的版本号——select version();
  6. 查看当前使用的数据库——select database();

  7. mysql不见分号不执行，分号表示结束。

    表的概述

  8. 数据库中最基本的单元是表——table

  9. table是数据库的基本组成单元，所有的数据都以表格的形式组织的，目的是可读性强。
  10. 任何一张表都有行和列：
    1. 行（row）：数据
    2. 列（column）：字段
    3. 每一个字段都有：字段名，数据类型，约束等属性。
  11. 查询某个数据库中有哪些表——show tables;

image.png

SQL语句的分类

DQL⭐ 数据查询语言（Data Query Language） select
DML⭐ 数据操纵语言（Data Manipulation Language）
对表中的数据进行增删改		 insert,delete,update
DDL 数据定义语言（Data Definition Language）
对表的结构进行增删改查		 create,drop,alter
TCL 事务控制语言（Transaction Control Language） commit，rollback
DCL 数据控制语言（Data Control Language） grant(授权),revoke(撤销权限)

表的各种操作

  1. 查看表中的数据——select * from 表名;
  2. 查看表的结构——desc 表名;
  3. 查看数据库中有哪些表——show tables;

  4. 查看该表的建表语句——show create table 表名；

    DQL

    简单查询

  5. 查询一个字段——select 字段名 from 表名；

  6. 查询两个及多个字段——select 字段名，字段名 from 表名；（使用逗号隔开即可）
  7. 查询所有字段
    1. 写上所有字段
    2. 使用 * （效率低，可读性差，开发中不建议使用）
  8. 给查询的字段起别名——select a,b as myt from 表名;
    1. as关键字可以省略，用空格代替。
    2. 当别名中有空格时，使用” “或’ ‘将别名括起来，建议使用单引号。
    3. 当别名中有中文时，使用” “或’ ‘将别名括起来，建议使用单引号。
    4. as只是将显示的查询结果列名显示为myt，原表列名还是叫b。

select语句永远不会进行修改操作。（只负责查询）

  1. 给字段使用数学表达式——select ename，sal*12 from emp；

    条件查询（where）

    | 运算符 | 说明 | | —- | —- | | = | 等于 | | <> 或 != | 不等于 | | < | 小于 | | <= | 小于等于 | | > | 大于 | | >= | 大于等于 | | between….and…… | 两个值之间，等同于 >=and<= | | is null | 为null（is not null 不为空） | | and | 并且 | | or | 或者 | | in | 包含，相当于多个or | | not in | 不包含 | | not | 可以取非，主要用于is或in中 | | like | 模糊查询 |

  2. 等于（=）

查询薪资等于800的员工的姓名和编号——select empno,ename from emp where sal=800;
image.png

  1. 两值之间

查询薪资在2450-3000之间的员工信息（包括2450和3000）
① >=and<=：select empno,ename from emp where sal>=2450 and sal<=3000;
② between..and..：select empno,ename from emp where sal between 2450 and 3000;
③ between and除了可以使用在数字方面之外，还可以使用在字符串方面。
select * from emp where ename between ‘A’ and ‘C’;
此时代表左闭右开 [A，C)

  1. 是否为空（is null，is not null）

查询哪些员工的补助为null——select from emp where comm is null;
查询哪些员工的补助不为null——select from emp where comm is not null;
在数据库中null不能用等号进行衡量，因为数据库中的null代表什么也没有，不是一个值。

查询哪些员工没有津贴——select * from emp where comm is null or comm=0;

  1. and和or联合使用

查询薪资大于1000的并且部门编号是20或30部门的员工
select from emp where sal>1000 and deptno=20 or deptno=30;（✖）
select from emp where sal>1000 and (deptno=20 or deptno=30);（✔）

and和or同时出现时，and的优先级比or高
当运算符的优先级不确定的时候加小括号。

  1. 包含（in，not in）

查询出job是manager或者job为salesman的员工——select from emp where job in(‘manager’,’salesman’);
in相当于多个or，完全可以用or表示，采用 in 会更简洁一些。
查询出job不是manager和salesman的员工——select from emp where job not in(‘manager’,’salesman’);
not in表示不在这几个值中的。

  1. 模糊查询（like）

%：匹配任意个字符
下划线：一个下划线只匹配一个字符。
查询名字中含有O的：select from emp where ename like ‘%O%’;
查询名字中以T结尾的：select from emp where ename like ‘%T’;
查询名字中以K开始的：select from emp where ename like ‘K%’;
查询第二个字母是A的：select from emp where ename like ‘A%’;
查询名字中有的：select * from emp where ename like ‘%_%’; （用转义表示\）

排序（order by）

升序——asc（默认）
降序——desc
查询所有员工的工资并降序排列——select * from emp order by sal desc;

多个字段排序：查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列。
select from emp order by sal asc,ename asc;（当sal相等时，再按照ename升序排列）
*根据字段位置排序：select ename,sal from emp order by 2 desc; （2表示第二列，按查1询结果的第2列倒序排）

综合案例

查询工资在1250-3000之间的员工信息，要求按照薪资降序排列
select * from emp where sal between 1250 and 3000 order by sal desc;
执行顺序：from-where-select-order by

数据处理函数（单行处理函数）

  1. 单行处理函数的特点：一个输入对应一个输出。
  2. 常见的单行处理函数：
    1. lower(转为小写)：select lower(ename) from emp;
    2. upper(转为大写)：select upper(ename) from emp;
    3. substr(取子串):【substr(被截取的字段，起始下标（从1开始），截取的长度)】

select substr(ename,1,2) from emp;
image.png
查询员工名字第一个字母是A的员工信息：
①：select from emp where ename like ‘A%’;
②：select from emp where substr(ename,1,1)=’A’;

  1. length(取长度)：select length(ename) from emp;
  2. trim(去空格)：去除字符前后的空格。
  3. round(四舍五入)：round(数字，保留的位数)

87FCE09E9C36AC7E75AE49D721BC8D58.png
select round(123.45,0) as result from emp; //保留整数，并四舍五入。 结果：124
select round(123.56,1) as result from emp; //保留1位小数，并四舍五入。 结果：123.6
select round(123.56,-1) as result from emp; //保留到十位，并四舍五入。 结果：120

  1. rand(生成随机数)：select rand() from emp;

100以内的随机数：select round(rand()*100,0) from emp;

  1. ifnull（空处理函数）：ifnull可以将null转换为一个具体值。在数据库中，只要有null参与的数学运算，最终结果都为null，为了避免这种情况使用ifnull。

ifnull的用法：ifnull（数据，被当做哪个值）
计算每个员工的年薪：年薪=(月薪+月补助)12
select ename,(sal+ifnull(comm,0))12 as ‘年薪’ from emp;

  1. case….when….then….when….then….else….end：

当员工的工作岗位是manager的时候，工资上调%10，当工作岗位是salesman的时候，工资上调%50，其他正常。
select ename,
job,
(case job when ‘MANAGER’ then sal1.1 when ‘SALESMAN’ then sal1.5 else sal end) as newsal
from
emp;

分组函数（多行处理函数）

  1. 多行处理函数的特点：输入多行，最终输出一行。
  2. 常见的多行处理函数：count-计数 sum-求和 avg-平均值 max-最大值 min-最小值
  3. 分组函数使用注意事项：
    1. 分组函数自动忽略NULL，不需要提前对NULL进行处理。
    2. 分组函数在使用的时候必须先进行分组（group by），然后才能使用。若未分组，本张表默认为1组
    3. 分组函数中count(*)与count(具体字段)有什么区别？

count(*)：统计表中的行数。
count(具体字段)：表示统计该字段下所有不为null的元素的总数。

  1. 分组函数不能够直接使用在where子句中。

select * from emp where sal>min(sal);（✖） where在执行时，group by还未执行。

分组查询

group by

  1. 书写顺序：select …. from …. where …. group by …. order by …. ;

执行顺序：from-where-group by-select-order by

  1. 在一条select语句中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数，其他一律不跟。

例题：

  1. 找出每个工作岗位（job）的工资(sal)和：select job,sum(sal) from emp group by job;

执行顺序：先从emp表中查询数据，根据job字段进行分组，然后对每一组数据进行求和。

  1. 找出每个部门（deptno）的最高薪资（sal）：select deptno,max(sal) from emp group by deptno;
  2. 找出每个部门（deptno）不同岗位（job）的最高工资（sal)：

select deptno,job,max(sal) from emp group by deptno,job;
思路：将两个字段联合成一个字段看（两个字段联合分组）。

having

  1. 使用having可以对分组后的数据进一步过滤。
  2. havaing不可以单独使用，必须和group by联合使用。
  3. having不能替代where，having与where都可以时，优先选择where，where无法使用时再用having。
  4. 因为where子句中不能直接使用分组函数所以要有having。

例题：

  1. 找出每个部门的最高工资，要求显示最高工资大于3000的。

select deptno,max(sal) from emp where sal>3000 group by deptno;
select deptno,max(sal) from emp group by deptno having max(sal)>3000;

  1. 找出每个部门的平均工资，要求显示平均工资高于2500的。（此题只能使用having，where就不行。）

select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;

前半部分总结

  1. 书写顺序：select….from….where….group by….having….order by;

执行顺序：from-where-group by-having-select-order by
首先执行 where 语句过滤原始数据
执行 group by 进行分组
执行 having 对分组数据进行操作
执行 select 选出数据
执行 order by 排序
例题

  1. 找出每个岗位的平均工资，要求显示平均工资大于1500的，除manager岗位之外的，要求按照平均工资降序排列。

select deptno,avg(sal) from emp
where
job!=’MANAGER’ group by deptno having avg(sal)>1500 order by avg(sal) desc;

去除重复记录（distinct）

  1. 原表数据不会被修改，知识查询结果去重。
  2. distinct只能出现在所有字段的最前面（只是字段的最前面）！
  3. distinct出现在job和deptno两个字段之前，代表两个字段联合去重（两个字段都重复才算重复）。

例题
统计一下工作岗位（job）的数量——select count(distinct job) from emp;

⭐连接查询

定义：
多张表联合起来查询数据，被称为连接查询。
分类：

  1. 内连接
    1. 等值连接
    2. 非等值连接
    3. 自连接（将一张表看成2张表）
  2. 外连接
    1. 左连接
    2. 右连接
  3. 全连接（不讲）

笛卡尔积现象：当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数是两张表条数的乘积，这种现象被称为：笛卡尔积现象。
例如：select ename,dname from emp,dept;
如何避免？
在连接时添加条件：select ename,dname from emp,dept where emp.deptno = dept.deptno;
给表起别名：select ename,dname from emp e,dept d where e.deptno = d.deptno;

内连接

  1. 等值连接

语法：select….from a表 inner join b表 on a和b的连接条件 where 筛选调节; (inner可以省略）
例题：查询每个员工所在部门的名称，显示员工名和部门名。
思路：emp表和dept表进行连接，条件是：e.deptno=d.deptno

sql92语法：select ename,dname from emp e,dept d where e.deptno=d.deptno;
sql99语法：select ename,dname from emp e join dept d on e.deptno=d.deptno;

推荐使用sql99语法：因为sql92的结构不清晰，表的连接条件和后期进一步筛选的条件都放到了where后面。sql99表的连接是独立的，如果需要进一步筛选，再往后添加where。

  1. 非等值连接（连接条件不是一个等量关系）

例题：找出每个员工的薪资等级，要求显示员工名（ename），薪资（sal），薪资等级（grade）。
select ename,sal,grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

  1. 自连接（将一张表看做两张表）

例题：查询员工的上级领导，要求显示员工名字和对应的领导名。
假设 emp a是员工表，emp b是领导表。
select a.ename ‘员工名’,b.ename ‘领导名’ from emp a join emp b on a.mgr=b.empno;

外连接

在外连接中，两张表的连接，产生了主次关系。
外连接的查询结果条数一定>=内连接的查询结果条数。

  1. 右连接

表示将join关键字右边的表看成主表，主要是为了将这张表的数据全部查询出来，顺便关联查询左表。
语法：select….from a表 right outer join b表 on a和b的连接条件 where 筛选调节; (outer可以省略）

  1. 左连接（left）

依照右连接学习。
例题：查询每个员工的上级领导，要求显示所有员工的名字和领导名。
select a.ename ‘员工名’,b.ename ‘领导名’ from emp a left join emp b on a.mgr=b.empno;
image.png因为需要显示每个员工的名字，所以需要用外连接，用的左外连接，所以左表是主表，所以会有红框这一条。不然因为king没有领导就会不显示他。

总结

  1. 三张表，四张表怎么连接？

一条sql语句中内连接和外连接可以混合使用。
语法：select….from a join b on a和b的连接条件 join c on a和c的连接条件 right join d on a和d的连接条件;
例题：查找每个员工的部门名称以及工资等级，要求显示员工名，部门名，薪资，薪资等级。
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;
image.png
查找每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级？
注：a表为员工表，b表为领导表。
select d.dname ‘部门名称’,a.sal ‘薪资’,s.grade ‘薪资等级’,a.ename ‘员工名’,b.ename ‘领导名’ from emp a join dept d on a.deptno=d.deptno join salgrade s on a.sal between s.losal and s.hisal left join emp b on a.mgr=b.empno;
image.png

子查询

子查询就是嵌套的 select 语句，可以理解为子查询是一张表。

在where语句中使用子查询

例题：查询比最低工资高的员工姓名和工资
select ename,sal from emp where sal>(select min(sal) from emp);

在from语句中使用子查询

技巧：from后面的子查询，可以将子查询的查询结果当做一张临时表。
例题：查找每个岗位的平均工资的薪资等级。
第一步：先查找每个岗位的平均工资
select job,avg(sal) from emp group by job;
第二步：将第一步的查询结果当做一张表
select t.*,s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;
注意：起别名很重要。

在select语句中使用子查询（了解）

例题：查找每个员工的部门名称，要求显示员工名，部门名。
select e.ename,e.deptno,d.dname from emp e join dept d on e.deptno=d.deptno;
可以用内连接的呀，select感觉没啥用。

union（合并查询结果集）

例题：查询工作岗位是manager和salesman的员工。
①select ename,job from emp where job=’manager’ or job=’salesman’;
②select ename,job from emp where job in(‘manager’,’salesman’);
③select ename,job from emp where job=’manager’
union
select ename,job from emp where job=’salesman’;
三种方式都可以，为什么要使用union呢？
union的效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。但是union可以减少匹配次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接。
31A0B04DD1CBC897C8A11798F4B040D9.png
union使用时的注意事项：

  1. union在进行结果集合并时，要求两个结果集的列数相同。

D68507F08A6A0A696CDD9C075A632D7D.png

  1. union在结果集合并时列和列的数据类型要一致。（mysql其实可以不一致，但oracle必须一致！）
    088CB2C0B57FB72AE5D320EC9D9F7AE7.png

    ⭐limit

  2. limit的作用：将查询结果集的一部分取出来。通常使用在分页查询当中。

  3. 分页的作用：提高用户体验，可以一页一页翻看。
  4. 语法
    1. 完整用法：limit startIndex,length; startIndex：起始下标（从0开始） length：长度
    2. 缺省用法：limt 5; 表示取前五条数据。

例题：

  1. 按照薪资降序排列，查找排名前五的员工。

select ename,sal from emp order by sal desc limit 5;
select ename,sal from emp order by sal desc limit 0,5;
注意：mysql当中的limit在order by之后执行。

  1. 按照薪资降序排列,取出工资排名在【3-5】名的员工

select ename,sal from emp order by sal desc limit 2,3;
2：表示起始位置从下标2开始，就是第3条记录
3：表示长度

  1. 按照薪资降序排列，查找工资排名在【5-9】的员工

select ename,sal from emp order by sal desc limit 4,5;

分页

每页显示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
公式：limit（pageNo-1） pageSize，pageSize
limit（页数-1）* 显示条数，显示条数
pageNo：第几页
pageSize：显示几条记录

终极大总结

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

DDL

建表（create）

语法格式：
3A2F0009A1022DD3D335827BEADA68D5.png
数据类型：

varchar(最长255) 可变长度的字符，比较智能，比较节省空间，会根据实际的数据长度动态分配空间。
例如：varchar(10)，传入jack，则会自动分配4。		 优点：节省空间
缺点：需要动态分配空间，速度慢。
char(最长255) 定长字符串，不管实际的数据长度是多少，分配固定长度的空间去存储数据。使用不恰当的时候，可能会导致空间的浪费。 优点：不需要动态分配空间，速度快。
缺点：使用不恰当可能会导致空间的浪费。
int(最长11) 数字中的整数型，相当于java的int
bigint 大于11的就用bigint
数字中的长整型。相当于java中的long
float 单精度浮点型数据
double 双精度浮点型数据
date 短日期类型——年 月 日
datetime 长日期类型——年 月 日 时分秒 毫秒
clob 字符大对象，最多可以存储4G的字符串。例如，存储一篇文章，存储一个说明。超过255个字符的都要采用CLOB字符大对象来存储。
blob 二进制大对象，专门用来存储图片，声音，视频等流媒体数据。往blob类型的字段上插入数据的时候，例如插入一个图片，视频等。需要使用IO流

varchar和char我们应该如何选择？
取决于是否确定长度。
性别字段——选择char，因为性别是固定长度的字符串。
姓名字段——选择varchar，因为每一个人的名字长度不同，不固定。
例题：

  1. 创建一个运动员表（号码，姓名，年龄，位置，薪资）

image.png

删表（drop）

drop table t_student;
drop table if exists t_student;

增加|删除|修改表结构（alter）

  1. 添加列——alter table 表名 add 列名 类型(长度) 约束;

例题：需求发生改变，需要向 t_student 中加入联系电话字段，字段名称为： contatct_tel 类型为 varchar(40)
alter table t_student add contact_tel varchar(40);

  1. 修改列
    1. 修改现有列类型——alter table 表名 modify 列名 类型(长度) 约束;
    2. 修改现有列名称——alter table 表名 change 旧列名 新列名 类型(长度) 约束;

例题：

  1. 修改s_name的长度为100.

alter table t_student modify s_name varchar(100);

  1. 修改sex字段名为gender。

alter table t_student change sex gender char(2) not null;

  1. 删除列——alter table 表名 drop 列名；

例题：删除学生表中的联系电话字段。
alter table t_student drop contact_tel;

DML

添加数据（insert）

语法格式：

  1. insert into 表名(字段1，字段2，字段3) values(值1，值2，值3); //建议使用这种方式
  2. insert into 表名 values(值1，值2，值3);

//不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到 insert 语句。

  1. 一次性插入多条数据——insert into 表名(字段1，字段2，字段3) values(值1，值2，值3),(值1，值2，值3);

    修改数据（update）

    语法格式：
    update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3 where 条件;
    注：如果没有where条件限制会更新所有数据。
    例题：将job为manager的员工的工资上涨%10
    update emp set sal=sal*1.1 where job=’manager’;

    删除数据（delete）

    语法格式：
    delete from 表名 where 条件；
    例题：删除津贴为500的员工
    delete from emp where comm=500;

    MySQL中删除数据的3种方式

    讲解地址——delete是把目录撕了，truncate是把书的内容撕下来烧了，drop是把书烧了。

  2. 执行速度上：drop>truncate>delete

  3. delete
    1. delete from emp;
    2. 删除效率低，支持回滚rollback。
  4. truncate
    1. truncate table emp;
    2. 删除效率高，不支持回滚。
    3. truncate是删除表中的数据，表还在。
  5. drop
    1. drop table emp;
    2. 把表都直接删了，数据当然也没了。
  6. 当想删除上亿条记录时

删除用delete可能需要执行1个小时才能删完，truncate只用1秒钟就能删了，但是一定要确定确实要删除，因为truncate不支持rollback。

约束

什么是约束
约束（constraint）是在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性，有效性。
约束的作用
约束的作用就是为了保证数据的有效。
常见的约束

not null 非空约束
unique 唯一性约束
primary key 主键约束
foreign key 外键约束
check 检查约束（mysql不支持）

非空约束（not null）

  1. not null约束的字段不能为null。
  2. not null只有列级约束（只能在字段后加），没有表级约束。

例：学生的姓名不能为空。
image.png

唯一性约束（unique）

  1. unique约束的字段不能重复，但是可以为null。
  2. unique有列级约束（只能在字段后加），也有表级约束（在最后添加）。

例（列级约束）：email 不能重复：
image.png
例（表级约束）：name和email联合起来具有唯一性。
image.png
什么时候使用表级约束？
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。
注：在mysql中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。

主键约束（primary key）

  1. 主键可以标识记录的唯一性。
  2. 主键值（主键字段中的值）是每一行记录的唯一标识，身份证号。
  3. 任何一张表都必须有主键，没有主键，表无效。
  4. 一张表只能有一个主键。
  5. primary key=not null+unique.（主键值不能为空，也不能重复）

例（列级约束）：
image.png
例（表级约束）：
image.png

复合主键

39A80F083DD596D73B523DB8DE3767A5.png

  1. 在实际开发中不建议使用复合主键。复合主键比较复杂，不建议使用。

    自然主键和业务主键

    自然主键：主键值是一个自然数，和业务没关系。
    业务主键：主键值和业务紧密相连，例如拿银行卡号做主键值。
    注：在实际开发中使用自然主键比较多，因为主键只要做到不重复就行，不需要有意义。
    业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动时，可能会影响到主键值，所以业务主键不建议使用，尽量使用自然主键。

    自增主键

  2. auto_increment表示自增，从1开始，以1递增。

例：
image.png
插入数据后，主键会自动递增。
image.png

外键约束（foreign key）

先不学吧，不咋用

存储引擎

  1. 存储引擎是一个存储/组织表中数据的方式。

  2. 不同的存储引擎存储/组织数据的方式不同。

    建表时指定存储引擎

    image.png

  3. engine用来指定存储引擎，mysql默认的存储引擎是：InnoDB

  4. default charset用来指定字符编码方式，mysql默认的
  5. 默认的存储引擎可在 my.ini 配置文件中使用 default-storage-engine 选项指定。

  6. 现有表的存储引擎可使用 ALTER TABLE 语句来改变： ALTER TABLE TABLENAME ENGINE= INNODB;

    常用的存储引擎

    MyISAM存储引擎

    MyISAM是MySQL5.5版之前的默认存储引擎。虽然性能极佳，⽽且提供了⼤量的特性，支持表级锁
    ，但MyISAM不⽀持事务和⾏级锁，⽽且最⼤的缺陷就是崩溃后⽆法安全恢复。

    InnoDB存储引擎

    InnoDB是MySQL的5.5版本之后的默认存储引擎，支持事务和表级锁、行级锁，默认为行级锁。支持外键，支持数据库崩溃之后自动恢复机制，非常安全。

    MEMORY存储引擎

    MEMORY是查询效率最高的，其数据存储在内存当中，且行的长度固定。不需要与硬盘交互，但是不安全关机之后数据消失，因为数据和索引都在内存当中。

    事务（Transaction）

  7. 事务就是逻辑上的一组操作，要么都执行，要么都不执行。

  8. 只有DML（insert，delete，update）语句才有事务。

    事务的基本操作

  9. 开启事务（关闭了mysql的自动提交机制）：start transaction;

  10. 提交事务：commit;
  11. 回滚事务：rollback; （回滚只能回滚到上一次的提交点。）

  12. rollback或者commit之后代表着一次事务的结束。

    事务的4个特性（ACID）

  13. 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。

  14. 一致性（Consistency）：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。
  15. 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

  16. 持久性（Durability）：一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

    并发事务带来的问题

    在典型的应⽤程序中，多个事务并发运⾏，经常会操作相同的数据来完成各⾃的任务（多个⽤户对同⼀数据进⾏操作）。并发虽然是必须的，但可能会导致以下的问题：

  17. 脏读（Dirty Read）：当一个事务正在访问数据库并且对数据进行了修改，而这种修改还没有提交到数据库当中。这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读取到的这个数据就是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

  18. 丢失修改（Lost to modify）：在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此成为丢失修改。
    1. 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
  19. 不可重复读（Unrepeatable read ）：在一个事务内多次读取同一数据。在这个事务（T1）还没有结束时，另一个事务（T2）也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况，因此称为不可重复读.
  20. 幻读（Phantom read）： 幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了⼏⾏数据，接着另⼀个并发事务（T2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（T1）就会发现多了⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。

幻读与不可重复读的区别？
不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除⽐如多次读取⼀条记录发现记录增多或减少了。

事务的隔离级别

image.png

四种隔离级别

  1. 读未提交（READ UNCOMMITTED）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。(理论上的，基本上都是2挡起步）
  2. 读已提交（READ COMMITTED）：允许读取并发事务已经提交的数据， 可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
  3. 可重复读（REPEATABLE READ）：对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改， 可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。（与 SQL 标准不同的地⽅在于 InnoDB 存储引擎在 REPEATABLEREAD（可重读）事务隔离级别下使⽤的是Next-Key Lock 锁算法，因此可以避免幻读的产⽣，这与其他数据库系统(如 SQL Server)是不同的。）
  4. 可串行化（SERIALIZABLE）：最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏（事务排队，不能并发），这样事务之间就完全不可能产⽣⼲扰，也就是说， 该级别可以防⽌脏读、不可重复读以及幻读。

  5. MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重复读） 。

    设置服务器默认隔离级别

  6. 在 my.ini 文件中使用 transaction-isolation 选项来设置服务器的默认事务隔离级别。

image.png

  1. 通过命令动态设置隔离级别。
    1. 全局级：对所有的会话都有效。
      1. 设置全局级隔离级别为 READ COMMITTED：set global transaction isolation level read committed;
    2. 会话级：只对当前会话有效。
      1. 设置会话级隔离级别为 READ COMMITTED：set transaction isolation level read committed;
      2. 另一种方式：set session transaction isolation level read committed;

  2. 查看当前隔离级别

    1. 全局级： select @@global.tx_isolation;
    2. 会话级： select @@tx_isolation; 或者 select @@session.tx_isolation;

      索引（index）

      https://blog.csdn.net/wangfeijiu/article/details/113409719

  3. 索引是在数据库表的字段上添加的，是为了提高查询效率而存在的一种机制。

  4. 将数据库类比成书籍的话，索引就相当于一本书的目录。
  5. MySQL查询的两种方式：
    1. 全表扫描
    2. 根据索引检索
  6. primary key和unique约束的字段都会默认添加索引。

  7. MySQL索引使用的数据结构主要有BTree（自平衡二叉树）索引和哈希索引。

    1. 对于哈希索引来说，底层的数据结构就是哈希表，因此在大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快。（Hash索引仅仅能满足”=”,“IN”和”<=>”查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100。）
    2. BTree索引的底层数据结构就是B+Tree，大部分场景建议选择BTree索引。
    3. BTree遵循左小右大的原则存放，采用中序遍历方式遍历取数据。

      索引的实现原理

      image.png

  8. 在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。

  9. 在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。
    1. MyISAM：索引存储在.MYI中
    2. InnoDB：索引存储在tablespace中
    3. MEMORY：索引存储在内存中

      创建索引的准则

      应该创建索引的列

      image.png

      不应该创建索引的列

      image.png
      经常DML操作的列，因为DML之后，索引需要重新排序。

注：建议通过主键查询或者unique约束的字段进行查询，效率较高。
建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

索引的操作

创建索引

索引名称 index_name 是可以省略的，省略后，索引的名称和索引列名相同。

  • 创建普通索引：CREATE INDEX index_name ON table_name(col_name);
  • 创建唯一索引：CREATE UNIQUE INDEX index_name ON table_name(col_name);
  • 创建普通组合索引：CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
  • 创建唯一组合索引：CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

修改表结构创建索引：
ALTER TABLE table_name ADD INDEX index_name(col_name);
创建表时直接指定索引：

  1. CREATE TABLE table_name (
  2.     ID INT NOT NULL,
  3.     col_name VARCHAR (16) NOT NULL,
  4.     INDEX index_name (col_name)
  5. );

删除索引

  • 直接删除索引：drop index index_name on table_name;

  • 修改表结构删除索引：alter table table_name drop index index_name;

    查看索引信息

    语法：show index from 表名;
    image.png

    使用索引

    explain select sal from emp where sal>1500;
    image.png

  • id：选择标识符

  • select_type：表示查询的类型。
  • table：输出结果集的表
  • partitions：匹配的分区
  • type：表示表的连接类型
  • possible_keys：表示查询时，可能使用的索引
  • key：表示实际使用的索引
  • key_len：索引字段的长度
  • ref：列与索引的比较
  • rows：扫描出的行数(估算的行数)
  • filtered：按表条件过滤的行百分比
  • Extra：执行情况的描述和说明

    索引的失效

  1. 模糊查询时以%开头——select * from emp where ename like ‘%T’;
    1. ename上即使添加了索引，也不会走索引。
    2. 尽量避免模糊查询的时候以“%”开始。
  2. 使用or的时候——select * from emp where ename=’KING’ or ename=’MANAGER’;
    1. 如果使用or要求or的两边的条件字段都要有索引，才不会失效。
    2. 所以不建议使用or，使用union替代。
  3. 使用复合索引时，没有使用左侧的列查找。
    1. 复合索引：两个及两个以上的字段联合起来添加一个索引，叫做复合索引。
    2. create index emp_job_sal_index on emp(job,sal);
      1. explain select * from emp where job=’MANAGER’; 不失效。
      2. explain select * from emp where sal=800; 失效。

  4. 还有好多种，以后再说吧。

    索引的分类

    逻辑分类

    按功能划分

  5. 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；

ALTER TABLE TableName ADD PRIMARY KEY(column_list);

  1. 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX IndexName ON ‘TableName’ (字段名(length));
或者
ALTER TABLE TableName ADD UNIQUE (column_list);

  1. 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

CREATE INDEX IndexName ON ‘TableName’ (字段名(length));
或者
ALTER TABLE TableName ADD INDEX IndexName(字段名(length));

  1. 全文索引

按列数划分

  1. 单例索引：一个索引只包含一个列，一个表可以有多个单例索引。

  2. 组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

    物理分类

  3. 聚簇索引

聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。
聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

  1. 非聚簇索引（也叫辅助索引或二级索引）

数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

  1. 虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

聚簇索引优缺点：
优点：

  • 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
  • 聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

  • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）。
  • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
  • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

    视图（view）

  1. 视图是一种根据查询（DQL语句）定义的数据库对象，用于获取想要看到和使用的局部数据。
  2. 视图也被称为虚拟表。
  3. 对视图的crud，会导致原表数据的改变。
  4. 视图是存储在硬盘上的，关机不会消失。

    视图的作用

  • 视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；（安全原因）
  • 可以使复杂的查询易于理解和使用。

    视图的操作

    创建视图

    create view 视图名 as DQL语句；

    修改视图

    alter view 视图名 as DQL语句;

    删除视图

    drop view if exists 视图名;

    数据库设计的三范式

    第一范式：任何一张表必须有主键，每一个字段具有原子性不可再分。
    第二范式：建立在第一范式的基础上，要求所有的非主键字段完全依赖主键，不要产生部分依赖。
    第三范式：建立在第二范式的基础上，要求所有的非主键字段直接依赖主键，不要产生传递依赖。

注：数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。

附录

  1. 锁机制详解