MySQL

1、初识MySQL

JavaEE：企业级Java开发

前端（页面：展示，数据）

后台（连接点：连接数据库JDBC，链接前端（控制，控制视图跳转和给前端传递数据））

数据库（存数据）

1.1、什么是数据库

数据库 DB DataBase

概念：数据仓库，安装在操作系统上的软件

作用：存储管理数据

1.2、数据库分类

关系型数据库：（SQL）

MySQL、Oracle、Sql Server、DB2、SQLlite
通过表和表之间、行和列之间的关系进行数据的存储

非关系型数据库：（NoSQL）

Redis，MongDB
存储对象，通过对象自身的属性来决定

DBMS（数据库管理系统）

数据库的管理软件，科学有效的管理数据，维护和获取数据
MySQL

1.3、MySQL

MySQL是一个关系型数据库管理系统

数据库中最基本的单元是表

任何一张表都有行和列，行被称为数据/记录，列被称为字段

1.4、安装MySQL

教程：

MySQL for Mac（M1）安装指南

一文搞懂阿里云服务器部署MySQL

不需要手动创建data目录、日志存放目录处存在问题

卸载MySQL：第一步点安装程序进行卸载，第二步删除系统中的所有mysql文件夹

登陆MySQL：

 mysql -u root -p

1.5、SQL语句分类(要记)

DQL(Data Query Language)：数据查询语言（带select关键字的都是查询语句）
DML(Data Manipulation Language)：数据操作语言（增insert，删delete，改update）
DDL(Data Definition Language)：数据定义语言（新建create，删除drop，修改alter等操作表的结构的语句）
TCL(Transactional Control Language)：事务控制语言（事务提交commit;，事务回滚rollback;）
DCL(Data Control Language)：数据控制语言（授权grant，撤销权限revoke）

2、常用命令

2.1、登陆退出

 # 登陆mysql
 $ mysql -u root -p

 # 退出mysql
 \q
 quit
 exit
 
 # 终止一条正在编写的语句
 \c

mysql中;表示语句结束

2.2、基本操作

 # 创建数据库 bjpowernode
 create database bjpowernode;
 
 # 删除数据库
 drop database bjpowernode;
 
 # 使用某个数据库
 use bjpowernode;
 
 # 导入数据（首先要选择一个数据库，然后再导入）
 source ~/study/bjpowernode.sql

2.3、简单查询

 # 查询数据库版本
 select version();
 
 # 查询当前使用的数据库
 select database();
 
 # 查看mysql中有哪些数据库（mysql默认自带4个数据库）
 show databases;
 # +--------------------+
 # | Database           |
 # +--------------------+
 # | information_schema |
 # | mysql              |
 # | performance_schema |
 # | sys                |
 # +--------------------+
 
 # 查看某个数据库下的表
 show tables;
 show tables from bjpowernode;
 # +-----------------------+
 # | Tables_in_bjpowernode |
 # +-----------------------+
 # | DEPT                  |
 # | EMP                   |
 # | SALGRADE              |
 # +-----------------------+
 
 # 查看表的结构 describe
 desc EMP;
 # +----------+-------------+------+-----+---------+-------+
 # | Field    | Type        | Null | Key | Default | Extra |
 # +----------+-------------+------+-----+---------+-------+
 # | EMPNO    | int         | NO   | PRI | NULL    |       |
 # | ENAME    | varchar(10) | YES  |     | NULL    |       |
 # | JOB      | varchar(9)  | YES  |     | NULL    |       |
 # | MGR      | int         | YES  |     | NULL    |       |
 # | HIREDATE | date        | YES  |     | NULL    |       |
 # | SAL      | double(7,2) | YES  |     | NULL    |       |
 # | COMM     | double(7,2) | YES  |     | NULL    |       |
 # | DEPTNO   | int         | YES  |     | NULL    |       |
 # +----------+-------------+------+-----+---------+-------+
 
 # 查看表的创建语句
 show create table EMP;

 # 查询字段（select + 字段名1,字段名2,... + from + 表名）
 select ename from EMP;
 select empno, ename from EMP;
 
 # 查询全部字段（效率低，仅供查看使用）
 select * from EMP;
 
 # select语句中可以使用运算符
 select empno, ename, sal*12 from EMP;
 
 # 对查询出的列设置别名
 select empno '员工编号', ename '员工姓名' from emp; 
 select empno 'emp no', ename 'e name' from emp; 
 
 # distinct去除查询结果中的重复记录（只能出现在所有字段的最前方）
 select distinct job from EMP;
 select distinct job, deptno from EMP; #联合去除重复记录
 select count(distinct job) from EMP; #可以用在函数中

所有数据库中，字符串统一使用单引号，双引号在Oracle数据库中无法使用。

2.4、条件查询

查询出符合条件的数据

 # 语法格式：select 字段 from 表名 where 条件;
 
 # 等于 =
 select empno, ename from EMP where sal = 800;
 select empno, sal from EMP where ename = 'SMITH';
 
 # 不等于 <>或!=
 
 # < > <= >=
 
 # 两值之间（包括两端） between ... and ...  左小右大
 
 # 为空 is null  不为空 is not null (null不能使用等号比较)
 
 # 和 and  或 or  优先级and更高，先执行and，如果想让or先执行可以加括号()
 
 # in 包含  not in 不包含
 select empno, ename, job from EMP where job in('MANAGER', 'SALESMAN');

2.5、模糊查询

 # like %匹配任意多个字符 _匹配任意一个字符
 select ename from EMP where ename like '%O%'; # 找出名字中含有O的
 select ename from EMP where ename like '%\_%'; # 找出名字中有下划线的 \表示转义

2.6、排序

排序总是在最后执行

 # select ... from ... where ... order by ... 关键字顺序不能变
 
 # 单字段排序
 select ename, sal from EMP order by sal; # 查询结果按sal排序，默认或asc是升序
 select ename, sal from EMP order by sal desc; # desc查询结果按降序排列
 select ename, sal from EMP order by 2; # 根据字段的位置也可以排序，2表示第二列，即sal（这种写法不健壮）
 
 # 多字段排序
 select ename, sal from EMP order by sal asc, ename asc; # 先按工资升序，工资相同时按名字升序排列

2.7、DBA命令

 # 新建用户
 create user 用户名 identified by '密码';
 
 # 授权
 
 # 回收权限

 # 导出数据库
 $ mysqldump 数据库名>导出路径\xxx.sql -u root -p 123456
 # 导出指定的表
 $ mysqldump 数据库名 表名>导出路径\xxx.sql -u root -p 123456
 
 # 导入数据库
 mysql> create database xxx; 
 mysql> use xxx; # 需要先创建数据库再导入
 mysql> source 导出路径\xxx.sql

截屏2022-04-22 11.23.27

截屏2022-04-22 11.23.44

截屏2022-04-22 11.23.56

3、函数

3.1、单行处理函数

数据处理函数也被称为单行处理函数，一个输入对应一个输出

多行处理函数也叫分组函数：多个输入对应一个输出

3.2、常见的单行处理函数

 # lower 转换小写
 select lower(ename) as ename from EMP; # 将查询结果的ename转为小写，设置列名为ename
 
 # upper 转换大写
 select upper(ename) as ename from EMP;
 
 # substr 取子串 
 # substr(字符串，起始下标，截取长度)
 select substr(ename,1,1) as ename from EMP; # 对ename从1开始截取1个字符（sql字符串下标从1开始）
 select ename from EMP where substr(ename,1,1)='A'; # 取子串也可以作查询条件
 
 # concat 字符串拼接
 # concat(str1,str2)
 
 # length 取字符串长度
 length(ename);
 select length(ename) from EMP;
 
 # trim 去空格 （用于处理传入的数据）
 select * from EMP where ename=trim('    KING');
 
 # str_to_data 将字符串转为日期
 
 # data_format 格式化日期
 
 # fromat 设置千分位
 
 # round 四舍五入
 round(1234.7,0) # 0表示保留到整数位，1表示保留一位小数，-1表示保留到十位
 
 # select 数据 from EMP; 会生成一个结构为EMP，全为指定值的表
 
 # rand 生成0～1随机数
 rand()
 # 可以配合round一起使用，例如生成100以内的随机数：round(rand()*100)
 
 # ifnull 空处理函数
 # ifnull(数据，被视作的值)
 # 因为sql中只要有null参与的数学运算最终结果一定是null
 select ename, sal+comm from EMP;
 select ename, sal+ifnull(comm,0) from EMP; # 如果comm为null则视为0
 
 # case...when...then...when...then...else...end 当..怎么做
 # 不修改数据库，只是修改查询的显示结果
 select ename,(case job when 'MANAGER' then sal*1.1 else sal end) as newsal from EMP;

3.3、多行处理函数

多行处理函数也叫分组函数，输入多行，输出一行

分组函数在使用的时候必须先进行分组，然后才能使用

如果没有分组，整张表默认为一组

分组函数的使用注意事项：

分组函数自动忽略NULL
count(*)统计所有记录条数，count(字段)统计某个字段下不为NULL的值
分组函数不能直接在where子句中使用
分组函数可以组合使用

# count 计数
select count(ename) from EMP;

# sum 求和
select sum(sal) from EMP;

# avg 平均值
select avg(sal) from EMP;

# max 最大值
select max(sal) from EMP;

# min 最小值
select min(sal) from EMP;

3.4、分组查询（重要‼️）

先分组，再对每一组的数据进行操作

关键字的执行顺序（要记🌟）：

from

where

group by

having

select

order by

从某张表中查询数据，先经过where条件筛选出有价值的数据，对这些数据进行group by分组，分组之后可以用having继续筛选，然后用select查询出来，最后order by排序输出

where执行的时候还没有分组，所以分组函数不能直接在where中使用

# 语句格式：
select..from..where..group by..having..order by..

# 按工作岗位分类，对各岗位工资求和?
select job, sum(sal) salsum from EMP group by job;

# 求出每个部门的最高工资?
select deptno, max(sal) salmax from EMP group by deptno;

# 找出每个部门中不同工作岗位的最高工资?
# 技巧：两个字段联合分组，将部门和工作岗位连成一个字段
select deptno, job, max(sal) from EMP group by deptno, job;

# 找出每个部门最高薪资，要求显示最高薪资大于3000的
select deptno, max(sal) from EMP where sal>3000 group by deptno;
# 或者也可以用having，但效率比where低
select deptno, sal from EMP group by deptno having max(sal)>3000;

# 找出每个部门的平均薪资，要求显示平均薪资大于2500的

select后只能跟group by的类和分组函数

优化策略：选择where而不是having，即先选出所有sal>3000的，然后再分组，而不是先分组再选（having在group by后执行）；where实在完成不了再选择having。

3.5、连接查询（重要‼️）

跨表，多张表连接起来查询数据

连接查询根据语法年代分类可以分为SQL92和SQL99

根据表的连接方式分类可以分为：

内连接：等值连接、非等值连接、自连接
外连接：左（外）连接，右（外）连接
全连接

笛卡尔积：表连接查询通常的查询方法是用一张表的记录去和另一张表的所有记录进行配对比较，当连接查询没有任何查询条件限制时，会输出的记录数=两张表记录数的乘积。（记录数为n,m的两张表，连接查询的匹配次数为n*m）

# 避免笛卡尔积现象产生，需要对连接查询设置条件
select ename, dname from EMP, DEPT where EMP.deptno = DEPT.deptno;

# 对表起别名可以提高效率
select e.ename, d.dname from EMP e, DEPT d where e.deptno = d.deptno; # SQL92语法

减小表的连接次数也是提高效率优化的重要手段。

不同的连接查询语法（推荐使用SQL99）

# 不同的连接查询语法

select e.ename, d.dname from EMP e, DEPT d where e.deptno = d.deptno; # SQL92 (表连接条件和后续过滤条件where混合在一起，结构不清晰)

select e.ename, d.dname from EMP e join DEPT d on e.deptno = d.deptno where..; # SQL99 (将表连接条件独立出来，更清晰)

内连接（只显示满足连接条件的数据）

# 内连接-等值连接：连接条件是等量关系
select e.ename, d.dname from EMP e join DEPT d on e.deptno = d.deptno where..;

# 内连接-非等值连接：连接条件不是一个等量关系
# 例子：查询每个员工的薪资等级，输出员工名，薪资，薪资等级，排序
select e.ename, e.sal, s.grade from EMP e join SALGRADE s on e.sal between s.losal and s.hisal order by s.grade asc, e.sal asc;

# 内连接-自连接：对自身进行连接，将一张表视为两张表
# 例子：查询员工的上级领导，要求显示员工名的对应的领导名 
select a.ename ename, b.ename mgrname from EMP a join EMP b on a.mgr = b.empno;

外连接（除了满足条件的数据之外还显示其他数据）

# 右/左外连接，将join右/左边的表视为主表，将全部数据查询出来
select e.ename, d.dname from EMP e right join DEPT d on e.deptno = d.deptno;
# 例子：查询每个员工的上级领导，要求显示员工名和对应的领导名
select e.ename, d.ename mgrname from EMP e left join EMP d on e.mgr = d.empno;

连接多张表（以一张表为基础）

# 使用多个join..on..
select..from..join..on..join..on..;
# 例子1：找出每个员工的部门名称和薪资等级，要求显示员工名、部门名、薪资、薪资等级
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;
# 例子2：找出每个员工的部门名称和薪资等级，要求显示员工名、领导名、部门名、薪资、薪资等级
select e.ename, e1.ename mgrname, 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 left join EMP e1 on e.mgr = e1.empno;

3.6、子查询

子查询：select语句中嵌套select语句，被嵌套的select称为子查询

# 子查询的使用位置：
select..(select).from..(select).where..(select).;

# where子句中的子查询
select..from..where..(select).;
# 例子：找出最低工资大于800的员工
select ename, sal from EMP where sal > (select min(sal) from EMP);

# from子句中的子查询（可以将子查询的结果当作一张临时表）
select..from..(select).where..;
# 例子：找出每个岗位平均工资的薪资等级
select 
	t.*, s.grade 
from 
		(select job, avg(sal) avgsal from EMP group by job) t 
	join 
		SALGRADE s 
on 
	t.avgsal between s.losal and s.hisal;
	
# select子句中的子查询（要求子查询只能一次返回一条结果，否则报错）
select..(select).from..where..;
# 例子：找出每个员工的部门名称，要求显示员工名和部门名
select e.ename, (select d.dname from DEPT d where e.deptno = d.deptno) dname from EMP e;

3.7、union合并查询结果

union：合并查询结果集

使用union效率更高，用于表连接可以减少匹配的次数（将笛卡尔积变为加法运算）

要求结果合并时列数和列的数据类型相同

# union连接两个select语句
select..from..where..
union
select..from..where..
# 例子：查询工作岗位是manager和salesman的员工
select ename, job from EMP where job = 'manager' union select ename, job from EMP where job = 'salesman';
# 也可以写作以下形式
select ename, job from EMP where job = 'manager' or job = 'salesman';
select ename, job from EMP where job in ('manager','salesman');

3.8、limit分页显示

取出查询结果集的一部分，通常用在分页查询中，提高用户体验

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

# limit用法：

# 取起始startIndex(从0开始)共length条记录
# limit startIndex, length 
# 例子：按薪资降序排序取排名在3到5名的员工
select ename, sal from EMP order by sal desc limit 2,3;

# 取前n的记录
# limit n 
# 例子：按薪资降序排序取排名前5的员工
select ename, sal from EMP order by sal desc limit 5;

分页显示

# 假设每页显示pageSize条记录
# 分页显示公式：第pageNo页为
limit (pageNo - 1) * pageSize, pageSize

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

3.9、插入、删除、修改数据

插入数据
- 字段名和值的数量、数据类型都要对应
- insert语句执行成功后必然会多一条记录
- 如果没有给其他字段指定值，默认为null
- 省略字段名相当于包括所有字段
- 一次可以插入多条记录

# 插入语法格式：
insert into 表名(字段名1,字段名2,...) values (值1,值2,...);
insert into 表名(字段名1,字段名2,...) values (...),(...),...;
# 例子：
insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m','20','123@qq.com');

# 插入多条记录：
insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m','20','123@qq.com'),(2,'zhangsan','m','20','123@qq.com'),(3,'zhangsan','m','20','123@qq.com');

# 将查询记录插入一张表中
insert into dept_bak select * from DEPT;

数据库的命名规范：所有标识符都使用小写，单词之间下划线连接

修改数据
- 不设置字段名表示更新整张表

# 修改语法格式：
update 表名 set 字段名1=值1, 字段名2=值2, ... where 条件;

# 存在则更新，不存在则插入
replace into students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

删除数据
- 不设置条件会删除整张表！
- delete语句删除效率低，删除后硬盘上的真实存储空间不会被释放，可以通过rollback恢复，属于DML语句
- truncate语句能快速删除表的所有数据，但无法恢复，属于DDL操作

# 删除语法格式：
delete from 表名 where 条件;
delete table 表名;

# 快速删除（不支持恢复,不支持单条删除）
truncate table 表名;

大数据的SQL执行优化时，可以尝试使用 merge into 代替 update 和 insert，对于已存在（符合on中的条件）的数据执⾏更新操作，不存在的数据执⾏插⼊操作。


merge into target t using source s on (t.id = s.aid)
when matched then
update set t.year = s.year
when not matched then
insert values(s.aid, s.name, s.year);

4、表

4.1、创建表

# 建表的语法格式：
create table 表名(
		字段名1 数据类型 (长度), 
		字段名2 数据类型 (长度), 
    ...
);

# 创建一个学生表
create table t_student(
  no int,
  name varchar(32),
  sex char(1) default 'm',
  age int(3),
  email varchar(255)
);

# 删除表
drop table t_student; # 如果表不存在会报错
drop table if exists t_student; # 推荐使用这种方法

# 通过复制快速创建表 as
create table EMP2 as select * from EMP;

# 根据查询结果创建表 as
create table mytable as select empno, ename from EMP where job = 'manager';

表名建议以t_或tbl_开始，可读性强

MySql常见数据类型
- varchar(最长255)：可变长度字符串，根据传入数据动态分配空间，速度慢
- char(最长11)：定长字符串，分配固定长度的空间，速度快，可能会浪费空间，例如：性别字段
- int：整数型（-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647)）
- bigint：长整型（20位 9223372036854775807）
- float：单精度浮点
- double：双精度浮点
- date：短日期（年月日），默认为%Y-%m-%d
- datetime：长日期（年月日时分秒），默认为%Y-%m-%d %h:%i:%s
- clob：字符大对象，最多可存储4G的字符串
- blob：二进制大对象，存储图片声音视频等流媒体数据，插入数据要使用IO流

# 日期相关

# str_to_date 将字符串varchar类型转换为date类型
# 通常用在插入日期
str_to_date('字符串日期', '日期格式')
str_to_date('01-10-1990','%d-%m-%Y')

# date_format 将date类型转换为特定格式的varchar字符串
# 设置展示的日期格式，通常用在查询
date_format(日期类型数据, '日期格式')
date_format(birth, '%m/%d/%Y')

# now()获取当前系统时间，datetime类型
now()

4.2、增加、删除、修改表结构

操作比较少，并且可以使用工具完成，不是重点

4.3、约束

约束是对表中的字段的限制，为了保证数据有效

约束包括：
- 非空约束：not null 字段值不能为空
- 唯一性约束：unique 字段值不能重复，但可以为null
- 主键约束：primary key(PK) （重要‼️）
  - 主键值是每一行记录的唯一标识
  - 添加了主键约束的字段是主键字段，主键字段not null且unique
  - 任何一张表都应该有且只有一个主键约束，没有主键的表无效
  - 可以将多个字段联合起来做主键，称为复合主键（实际开发不建议使用）
- 外键约束：foreign key(FK)（重要‼️）
  - 建立两张表之间的约束联系，例如限制cno只能为classno中的值（建立两张表是为了减少占用的空间）
  - foreign key(cno) references t_calss(classno)，班级表为父表，学生表为子表，进行增删改时要考虑对父子表分别操作的先后顺序
  - 外键值可以为null，被引用的父表中的字段至少应为unique
- 检查约束：check(只有oracle支持)

如果一个字段同时被not null和unique约束，该字段主动变成主键字段

create table t_student(
  no int primary key auto_increment, # 主键约束，自增 
  name varchar(32) not null, # notnull没有表级约束
  sex char(1) default 'm',
  age int(3),
  email varchar(255) unique, # 列级约束
  unique (name,email) # 表级约束，多字段的联合约束，表示name和email具有联合唯一性，即不允许两个字段都相同
);

xxx.sql文件称为sql脚本文件，可以保存大量sql语句，通过source xxx/xxx/xxx.sql执行

5、存储引擎

5.1、什么是存储引擎

Mysql特有的术语，是表存储/组织数据的方式

# 查看表的创建语句、储存引擎等信息
show create table t_student;

CREATE TABLE `t_student` (
  `no` int DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# 在建表时可以在最后小括号)右边指定ENGINE存储引擎,CHARSET编码方式

# 查看mysql支持的存储引擎
show engines \G

5.2、常用存储引擎

MyISAM：
- MyISAM 存储引擎是 MySQL 最常用的引擎。
- 它管理的表具有以下特征:
  - 使用三个文件表示每个表:
    - 格式文件 — 存储表结构的定义(mytable.frm)
    - 数据文件 — 存储表行的内容(mytable.MYD)
    - 索引文件 — 存储表上索引(mytable.MYI)
  - 灵活的 AUTO_INCREMENT 字段处理
  - 可被转换为压缩、只读表来节省空间（MyISAM的优势）
  - 对一张表来说，只要是主键或有unique约束的字段会自动创建索引
  - 不支持事务机制，安全性低
InnoDB：
- mysql默认存储引擎，支持事务，支持数据库崩溃后自动恢复，非常安全
- InnoDB 存储引擎是 MySQL 的缺省引擎。它管理的表具有下列主要特征:
  - 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
  - InnoDB 表空间 tablespace 被用于存储表的内容
  - 提供一组用来记录事务性活动的日志文件
  - 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理（InnoDB的最大特点）
  - 提供全 ACID 兼容
  - 在 MySQL 服务器崩溃后提供自动恢复
  - 多版本(MVCC)和行级锁定
  - 支持外键及引用的完整性，包括级联删除和更新
MEMORY：
- 使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。
- MEMORY 存储引擎管理的表具有下列特征:
  - 在数据库目录内，每个表均以.frm 格式的文件表示
  - 表数据及索引被存储在内存中（查询速度快，但不安全，关机后消失）
  - 表级锁机制
  - 不能包含 TEXT 或 BLOB 字段
- MEMORY 存储引擎以前被称为 HEAP 引擎

6、事务

6.1、什么是事务

一个事务transaction就是一个完整的业务逻辑，是最小的工作单元。

只有DML语句insert, delete, update与事务有关

有些操作需要多条DML语句共同联合起来才能完成，例如银行转账，多条DML语句需要同时成功或同时失败，所以需要事务

6.2、事务如何实现

InnoDB存储引擎提供了一组用来记录事务性活动的日志文件

事务执行过程中，每一条DML操作都会记录到事务性活动的日志文件中

提交事务：表示事务的（成功）结束，清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中

回滚事务：表示事务的（失败）结束，撤销之前所有的DML操作，清空事务性活动的日志文件

mysql在默认情况下支持自动提交事务

# 开启事务，关闭自动提交机制
start transaction;

insert ...;
select ...; # 查看目前状态

# 回滚
rollback;

# 提交
commit;

事务的特性：
- ACID（原子性、一致性、隔离性、持久性）
- 一致性：要求在同一个事务中所有操作必须同时成功或同时失败
- 隔离性：不同事务之间具有一定的隔离
- 持久性：事务提交就相当于将没有保存的数据进行保存

6.3、数据库的并发问题

数据库并发的三种并发问题：
- 脏读：对于两个事务 T1、T2，T1读取了已经被T2更新但还没有被提交的字段。之后，若 T2 回滚，T1读取的内容就是临时且无效的。
- 不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后 T2 更新了该字段。之后，T1再次读取同一个字段，值就不同了。
- 幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行。

6.4、事务隔离

事务隔离的级别（由低到高）：
- 读未提交：read uncommitted （未提交就能读到）
  - 事务A可以读取到事务B未提交的数据
  - 存在脏读现象dirty read，即读到脏数据
- 读已提交：read committed （提交之后才能读到）
  - 事务A只能读取到事务B提交之后的数据
  - 解决了脏读的问题，但不可重复读取数据（多次读到的数据可能不同，因为中间经历了数据提交）
- 可重复读：repeatable read （提交之后也读不到，读取的永远都是开启事务时的数据）
  - 事务A开启后，不管多久，每一次在事务A读取到的数据都是一致的，即使事务B已经将数据修改并提交。即事务A开启时创建一个副本。
  - mysql中默认的事物隔离级别
- 序列化/串行化：serializable
  - 最高隔离级别，事务排队，不能并发
  - 效率最低但都是真实数据

# 查看事务隔离级别
# 5.0以上版本
select @@tx_isolation; # 查看会话级的当前隔离级别
select @@global.tx_isolation; # 查看全局级的当前隔离级别
# 8.0以上版本
select @@transaction_isolation; # 查看会话级的当前隔离级别
select @@global.transaction_isolation; # 查看全局级的当前隔离级别

# 设置事务隔离级别
set global transaction isolation level read uncommitted;

7、索引

7.1、索引原理

索引是在数据库表的字段上添加的，是为了提高查询效率的一种机制，一个字段或多个字段联合可以添加一个索引，索引缩小了查找的范围

任何数据库中主键上会自动添加索引对象

截屏2022-04-21 20.44.27

（以B-Tree形式存储

mysql中主键和unique字段都会自动添加索引（通过主键或unique字段查询效率最高）

需要添加索引的情况：1、数据量庞大；2、字段经常出现在where后面，总是被扫描；3、该字段很少DML操作，因为每次DML操作后索引都需要重新排序

7.2、索引的应用

创建删除索引

# 创建索引
# 为EMP的ename创建名为EMP_ename_index的索引
create index EMP_ename_index index on EMP(ename);

# 删除索引
drop index EMP_ename_index on EMP;

查看索引

 # 查看sql语句执行情况，包括是否使用了索引进行检索
 explain select * from EMP where ename = 'KING';
 # type=ref 说明使用了索引检索

索引失效
- like %T 以百分号开头的模糊查询不会使用索引，所以应该尽量避免
- 使用or时会失效，除非两边的条件都有索引
- 使用复合索引时，没有使用左侧的列查找，索引失效
- 在where中索引列参加了运算，索引失效
- 在where中索引列使用了函数

优化思路：尽量不使用索引失效的情况，索引是数据库优化的重要手段，优化时应优先考虑索引

索引的分类
- 单一索引
- 复合索引
- 主键索引
- 唯一性索引（在唯一性比较弱的字段上添加索引用处不大，越唯一索引效率越高）

8、视图

8.1、什么是视图

view：从不同的角度看待同一份数据

 # 创建视图对象
 create view emp_view as select * from EMP;
 
 # 删除视图对象
 drop view emp_view;

只有DQL（即select）语句才能以view的形式创建

创建的视图对象可以当作表来使用

可以对视图对象进行增删改查，对视图的操作会影响到原表数据

8.2、视图的应用

创建视图可以临时保存多张表的查询结果

截屏2022-04-22 11.15.06

使用视图的优点：方便、简化开发、便于维护
- 将重复使用的，特别长的sql查询语句保存为视图对象，简化操作
- 有利于后期维护，修改时只需要修改映射的sql语句即可
- 视图可以当作table一样使用，存储在硬盘上，不会消失

9、数据库设计三范式

9.1、什么是数据库设计范式

设计范式：数据库表的设计依据

数据库设计范式共有三个：
1. 要求任何一个表必须有主键，每一个字段有原子性，不可再分；
2. 建立在第一范式基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖；
3. 建立在第二范式基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖

按照范式进行设计，可以避免表中数据的冗余和空间的浪费

9.2、第一范式

“任何一个表必须有主键，每一个字段有原子性，不可再分”

最核心最重要的范式，所有表的设计都需要满足。

截屏2022-04-22 11.34.34

9.3、第二范式

“建立在第一范式基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖”

截屏2022-04-22 11.41.05

产生部分依赖的缺点：数据冗余，空间浪费（如张三这个数据出现了两次）

截屏2022-04-22 11.43.19

多对多关系设计数据库：三张表，关系表两个外键

9.4、第三范式

“建立在第二范式基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖”

截屏2022-04-22 11.46.43

截屏2022-04-22 11.47.08

一对多关系设计数据库：两张表，多的表加外键

9.5、总结

一对一：外键唯一
- 通常用一张表，但如果字段太多表太庞大，建议拆分成多张表
- 拆分表很少使用主键共享策略，一般是添加一个unique的外键
一对多：两张表，多的表加外键
多对多：三张表，关系表两个外键

截屏2022-04-22 11.53.07

实际设计数据库要根据客户需求

表和表的连接次数越多效率越低，有的时候为了减少表的连接次数，可以允许一定程度的冗余存在，对开发人员来说这样也会使sql语句的编写难度降低

34道作业题

posted @ 2022-08-31 01:47 Colin13 阅读(128) 评论(0) 编辑收藏举报

刷新页面返回顶部

Colin13

MySQL

MySQL

1、初识MySQL

1.1、什么是数据库

1.2、数据库分类

1.3、MySQL

1.4、安装MySQL

1.5、SQL语句分类(要记)

2、常用命令

2.1、登陆退出

2.2、基本操作

2.3、简单查询

2.4、条件查询

2.5、模糊查询

2.6、排序

2.7、DBA命令

3、函数

3.1、单行处理函数

3.2、常见的单行处理函数

3.3、多行处理函数

3.4、分组查询（重要‼️）

3.5、连接查询（重要‼️）

3.6、子查询

3.7、union合并查询结果

3.8、limit分页显示

3.9、插入、删除、修改数据

4、表

4.1、创建表

4.2、增加、删除、修改表结构

4.3、约束

5、存储引擎

5.1、什么是存储引擎

5.2、常用存储引擎

6、事务

6.1、什么是事务

6.2、事务如何实现

6.3、数据库的并发问题

6.4、事务隔离

7、索引

7.1、索引原理

7.2、索引的应用

8、视图

8.1、什么是视图

8.2、视图的应用

9、数据库设计三范式

9.1、什么是数据库设计范式

9.2、第一范式

9.3、第二范式

9.4、第三范式

9.5、总结

34道作业题

公告