一、 概念
什么是SQL？
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。
作用：用于存取数据、查询、更新和管理关系数据库系统。
什么是MySQL?
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。
RDBMS
数据库三大范式是什么？
第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。
二、 通用语法及分类
alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin
mysql -uroot -p
12345678
create itcast;
use itcast;
mysql> create table tb_user(
-> id int comment '编号',
-> name varchar(50) comment '姓名',
-> age int comment '年龄',
-> gender varchar(1) comment '性别'
-> )comment '用户表';
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| tb_user |
+------------------+
1 row in set (0.01 sec)
mysql> desc tb_user
-> desc tb_user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc tb_user' at line 2
mysql> desc tb_user;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
1. DDL
数据类型
char性能好，名字一般是varchar
2.DML
3. DQL
执行顺序
分组查询
分组一般返回分组之后的字段和聚合函数
排序查询
-- 排序查询 --
select * from emp order by age asc ; #默认升序
select * from emp order by age desc ;
# 先升再降
select * from emp order by age asc, enerydate desc ;
分页查询
三个条件就两个and
4. DCL
5. 函数
字符串函数
-- 函数演示 --
-- concat
select concat('Hello','cat');
select upper('hello');
#左填充 和 右
select lpad('01',5,'-');
select rpad('01',5,'-');
select trim(' hello jk ');
#索引从1开始
select substring(' hello jk ',1,5);
#给员工号补0 不足5位
update emp set workno = lpad(workno,5,'0');
数值函数
select rand(); # 0-1
#生成六位随机验证码
select lpad(round(rand()*1000000, 0), 6, '0');
日期函数
select day(now());
select date_add(now(), interval 70 day );
select datediff('2021-10-01','2021-11-02');# -32
#查询所有员工的入职天数，并根据入职天数倒叙排序
select name, datediff(curdate(), enerydate) as 'entrydays' from emp order by entrydays desc ;
流程控制函数
# 查询 员工的姓名和工作地址 北京 一线城市，其他 二线
select name,
(case workAdd when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作城市'
from emp;
select name,
(case when math >85 then '优秀' else '及格' end )'数学',
(case when English >85 then '优秀' else '及格' end )'英语'
from score;
6. 约束
自增是在主键基础上设置
create table user(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int check ( age between 0 and 120) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
) comment '用户表';
insert into user(name, age, status, gender)
values ('Tom1', 19, '1', '男'),
('Tom2', 24, '0', '女'),
('Tom3', 25, '3', '男');
insert into user(name, age, gender)
values ('Tom4', 19, '男');
外键约束
有外键 就不能删 附表的 研发部
#添加外键
alter table emp1 add constraint emp_dept_id foreign key (dept_id) references dept(id);
alter table emp1 drop foreign key emp_dept_id;
外键的删除和更新行为：
# 同时删除 同时更新
alter table emp1 add constraint emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;
# set null
alter table emp1 add constraint emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;
7. 多表查询
多表关系
- 一对多，多对一：在多的一方建立外键，指向1的一方的主键
- 多对多
create table student_course(
id int primary key auto_increment comment '主键ID',
studentid int not null comment '学生id',
courseid int not null comment '课id',
constraint fk_studentid foreign key (studentid) references student(id),
constraint fk_courseid foreign key (courseid) references course(id)
) comment '学生课程中间表';
insert into student_course(id, studentid, courseid) VALUES (null,1,1), (null,1,2),(null,2,3),(null,2,2),(null,3,1);
多表查询分类
内连接
隐式 显式内连接
# 隐式内连接
select e.name, d.name from emp1 e, dept d where e.dept_id = d.id;
#显式内连接
select e.name, d.name from emp1 e join dept d on e.dept_id = d.id;
外连接
左外连接会完全包含左表的数据
右外连接会完全包含右表的数据
谁在左谁就是左表
select e.name, d.name from emp1 e left outer join dept d on e.dept_id = d.id;
自连接
可以是内连接，可以是外连接
select a.name, b.name from emp1 a, emp1 b where a.managerid = b.id;
# 没有manager 也要查出来 左外连接
select a.name, b.name from emp1 a left join emp1 b on a.managerid = b.id;
联合查询
直接合并两张表，需要去重 不用all
子查询
标量子查询：
返回一条记录，单行单列
select * from emp1 where dept_id = (select id from dept where name = '开发');
列子查询：返回一列
all：所有
any，some：任意
行子查询：返回一行，可以多列
表子查询：返回多行多列
跟行像，= 变 in
实例
# 查询 员工的姓名 年龄 职位 部门信息（隐式内连接）
-- 表：emp1 dept
select e.name, e.age, e.job, d.name from emp1 e, dept d where e.dept_id = d.id;
-- （显式内连接）
select e.name, e.age, e.job, d.name from emp1 e inner join dept d on e.dept_id = d.id where age < 30;
# 查询有员工的部门信息 自连接
select distinct d.id, d.name from emp1 e, dept d where e.dept_id = d.id;
# 4
select e.*, d.name from emp1 e left join dept d on d.id = e.dept_id where age > 30;
# 查询所有员工薪资
select e.*, s.grade, s.losal, s.hisal from emp1 e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
# 查询 开发 所有员工的信息和工资等级
select e.*, s.grade from emp1 e, salgrade s where e.dept_id = 5 and e.salary >= s.losal and e.salary <= s.hisal;
# 开发部平均工资
select avg(e.salary) from emp1 e, dept d where e.dept_id = d.id and d.id = 1;
# 查工资比jin高的员工
select salary from emp1 where name = 'jin';
select e.*
from emp1 e
where e.salary > (select salary from emp1 where name = 'jin');
# 查工资比平均工资高的员工
select avg(e.salary) from emp1 e;
select * from emp1 e where e.salary > (select avg(e.salary) from emp1 e);
# 在公司里查 比 指定部门 工资比平均工资低的员工
select avg(e.salary) from emp1 e where e.dept_id = 1;
select * from emp1 e1 where e1.salary < (select avg(e.salary) from emp1 e where e.dept_id = 1);
# 统计部门的员工数
select id, name, (select count(*) from emp1 e where e.dept_id = d.id) '人数' from dept d ;
select count(*) from emp1 where dept_id = 1;
# 多对多
8. 事务
事务的概念
事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。
回滚
事务最经典也经常被拿出来说例子就是转账了。
假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。
事务操作
mysql默认自动提交事务
抛出异常就回滚
开启事务两种方式：commit 和 start transaction
# 1
select @@autocommit;
set @@autocommit = 0;
# 2
start transaction ;
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
出错
commit ;# 成功就提交
-- 抛异常就回滚事务 回到初始，代表事务已经结束
rollback ;
四大特性ACID
原子性Atomicity：事务时不可分割的最小操作单元，要么都成功，要么都失败
一致性Consistency：事务完成时，所有数据保持一致状态，例如转账完总和不变
隔离性Isolation：保证事务再不受外部并发操作影响的独立环境下运行。
持久性Durability：事务一旦提交或回滚，它对数据库中的数据的改变是永久的。在磁盘中。binlog日志文件
并发事务问题：脏读，幻读，不可重复读
事务的隔离级别
用于解决并发问题
同一个事务中查询的数据不同，叫不可重复读；
幻读：本来一个事务没有id=3的数据，但插入时说重复了，是另一个事务的主键插入了一个，且是主键，已提交。
select @@transaction_isolation;
set session transaction isolation level read committed ;
set session transaction isolation level repeatable read ;
三.
1. 存储引擎
Mysql体系结构
InnoDB
MyISAM
sdi表结构存放的文件
Memory
存储引擎选择
2. 索引
索引结构
二叉树->红黑树
中间元素向上分裂
非叶子结点只是索引
B+树索引
Hash索引
为什么使用B+树索引？
索引分类
回表查询：先走二级索引找到id值，在回聚集索引找到行数据。
思考题：InnoDB主键索引的B+tree高度为多高？
索引语法
SQL性能分析
查看执行频次
慢查询日志
超过10s，就是慢查询
通过慢查询日志定位执行效率低的sql。
profiles
执行计划
explain + SQL语句
索引的使用
最左前缀法则
联合索引使用时，
索引失效：
函数运算；
字符串不加单引号，索引失效；
模糊查询；
or连接： 的字段，一个有索引，一个没有索引，都会失效。
SQL提示
MySQL不一定接受
覆盖索引 & 回表查询
尽量避免* 容易回表查询
前缀索引
降低索引的体积，平衡选择性
单列索引和联合索引
索引的设计原则
3. SQL优化
4. 视图/存储过程/触发器（存储对象）
a 视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，**视图并不在数据库中以存储的数据值集形式存在。**行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
视图具有表结构文件，但不存在数据文件。
对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。
视图的检查选项 cascaded
当我们操作当前视图时，cascaded检查选项是，如果当前视图有检查选项，则插入数据要满足包括当前视图条件以及满足当前视图所依赖的视图的条件。如果当前视图没有检查选项，则插入数据要满足当时视图所依赖视图有检查选项及其依赖的视图的条件。
local
当我们在操作当前视图时，local检查选项是递归的查找当前视图所依赖的视图是否有检查选项，如果有，则检查；如果没有，就不做检查。
视图的更新
视图的作用
b 存储过程
delimiter 指定结束符
查看变量：全局变量，会话变量
存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。
优缺点
优点
1）存储过程是预编译过的，执行效率高。
2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3）安全性高，执行存储过程需要有一定权限的用户。
4）存储过程可以重复使用，减少数据库开发人员的工作量。
缺点
1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。
in out inout
用户定义变量
case
repeat
loop
游标cursor
条件处理程序 handler
把原来报错的状态码写入handler里，not found 处理02开头的状态码
存储函数（有返回值的存储过程）
c 触发器
确保数据完整性，日志记录，数据校验。
监听：记录的增加、修改、删除。
行级：执行一句sql，影响了5行，那么触发器触发5次。
语句级：一句触发一次。
触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。
使用场：
- 可以通过数据库中的相关表实现级联更改。
- 实时监控某张表中的某个字段的更改而需要做出相应的处理。
- 注意不要滥用，否则会造成数据库及应用程序的维护困难。
在MySQL数据库中有如下六种触发器：
Before Insert，After Insert，
Before Update，After Update
Before Delete，After Delete
5.锁
协调多个线程或进程并发访问某一资源的机制
全局锁
如果增删改，就会阻塞。
表级锁
读锁：自己和其他客户端都可以读，但都不能写。
写锁：自己可读可写，其他客户端不能读，不能写,阻塞
lock tables 表名 read;
lock tables 表名 write;
元数据锁
执行增删改查会产生对应的元数据锁。
注意：alter table时 的exclusive锁
如果一个客户端执行insert或者select命令，另一个客户端执行alter，就会阻塞。直到第一个用户提交了事务，才会执行alert
意向锁
与表锁的兼容情况
事务提交，锁就释放了。
行级锁 3类
行锁
对同一个表操作的两个事务
行锁是针对索引加的锁，没加索引，会升级为表锁
间隙锁 和 临键锁
临键锁包含 数据和间隙
唯一索引等值查询
普通索引等值查询
唯一索引范围查询
6 . InnoDB引擎
逻辑存储结构
InnoDB架构-内存结构
Buffer Pool
Change buffer
自适应哈希
日志缓冲区
InnoDB架构- 磁盘架构
后台线程
事务原理
MVCC 多版本并发控制
三个隐式字段
undo log版本链
readView
在RC隔离级别下的提取原理
每一次快照读都会生成readVIew
在RR（Repeatable Read）隔离级别下的提取原理
533
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
