MySQL的学习路线
- 基础篇
- MySQL概述
- SQL
- 函数
- 约束
- 多表查询
- 事务
- 进阶篇
- 存储索引
- 索引
- SQL
- 视图/存储过程/触发器
- 锁
- InnoDB核心
- MySQL管理
- 运营篇
- 日志
- 主从复制
- 分库分类
- 读写分离
1.MySQL概述
1.1 数据库相关概念
- 关系型数据库
- 概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库
- 特点:
-
- 使用表存储数据,格式统一,便于维护使用
-
- SQL语言操作,标准统一,使用方便
-
1.2 主流的数据库排名
- MySQL官方提供了两种不同的版本:
- 社区版( MySQL Community Server)免费,MySQL不提供任何技术支持
- 商业版(MySQL Enterprise Edition)收费,可以试用30天,官方提供技术支持
- MySQL的启动与停止
#启动 net start mysql #停止 net stop mysql
- MySQL连接客户端
mysql -u root -p #输入密码即可
- 数据模型
- 一个DBMS可以管理多个数据库
- 一个数据库可以创建多张表
2. SQL
2.1 SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾
- SQL语句可以使用空格/缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
- 注释:
- 单行注释:–注释内容或#注释内容(MySQL特有)
- 多行注释:/注释内容/
2.2 SQL的分类
2.3 DDL(数据定义语言)
2.3.1 对数据库操作
#查询
#查询所有的数据库
show database;
#查询当前数据库
select database();
#创建
create database 数据库名;
#删除
drop database 数据库名;
#使用
use 数据库名;
2.3.2 对表操作
#DDL-表操作-查询
#查询当前数据库所有表
show tables;
#查询表结构
desc 表名;
#查询指定表的创建表的语句
show create table 表名;
#DDL-表操作-添加
create table 表名(
字段1字段1类型[comment 字段1注释],
字段2字段2类型[comment 字段2注释],
字段3字段3类型[comment 字段3注释],
字段n字段n类型[comment 字段n注释]
)[comment 表注释];
注意:[…]为可选参数,最后一个字段后面没有逗号
-
数据类型
-
字符串类型
-
日期类型
#DDL-表操作-修改
#修改数据类型
alter table 表名 add 字段名类型(长度)[comment 注释][约束];
# 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束];
#删除字段
alter table 表名drop 字段名;
#修改表名
alter table 表名 rename to 新表名;
#DDL-表操作-删除
#删除表
drop table [if exists]表名;
#删除指定表,并重新创建该表
truncate table 表名;
注意:在删除表时,表中的全部数据也会被删除
2.3.4 案例:设计一张员工信息表
- 编号(纯数字)
- 员工工号(字符串类型,长度不超过10位)
- 员工姓名(字符串类型,长度不超过10位)
- 性别(男/女,存储一个汉字)
- 年龄(正常人年龄,不可能存储负数)
- 身份证号(二代身份证号均为18位,身份证中有X这样的字符)
- 入职时间(取值年月日即可)
create table emp (
id int comment'编号',
workno varchar (10)comment '工号',
name varchar (10)comment '姓名',
gender char (1)comment '性别',
age tinyint unsigned comment '年龄',
idcard char (18)comment '身份证号',
entrydate date comment '入职时间'
)comment '员工表';
2.3.5 总结:
1.DDL-数据库操作
show databases;
create database 数据库名;
use数据库名;
select database;
drop database 数据库名;
2.DDL-表操作
show tables;
create table 表名(字段字段类型,字段字段类型);
desc 表名;
show create table 表名;
alter table 表名 add/modify/change/drop/rename to...;
drop table 表名;
2.4 DML(数据操作语言)
- DML-添加数据
1.给指定字段添加数据
insert into 表名(字段名1,字段名2,...)values(值1,值2,,..);
2.给全部字段添加数据
insert into 表名values (值1,值2,...);
3.批量添加数据
insert into 表名(字段名1,字段名2,..)values(值1,值2,...),(值1,值2,...),(值1,2.….);
insert into 表名values(值1,值2,...),(值1,值2,...),(值1,值2,...);
注意:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的,字符串和日期型数据应该包含在引号中
- 插入的数据大小,应该在字段的规定范围内
- DML-修改数据
DML-修改数据
update 表名 set 字段名1=值1,字段名2=值2....[where条件];
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据
- DML-删除数据
DML-删除数据
delete from 表名[where条件]
注意:
- delete语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据
- delete语句不能删除某一个字段的值(可以使用update)
2.5 DQL(数据查询语言)
2.5.1 语法总结
- 基本查询
- 条件查询(where)
- 聚合函数(count,max,min,avg,sum)
- 分组查询(group by)
- 排序查询(order by)
- 分页查询(limit)
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
1. DQL-基本查询
#1.查询多个字段
select 字段1,字段2,字段3... from 表名;
select t from 表名;
#2.设置别名
select 字段1[as别条1],字段2[as别名2]... from 表名;
#3.去除重复记录
select distinct 字段列表 from 表名;
2. DQL-条件查询
#1.语法
select 字段列表 from 表名 where 条件列表;
3. DQL-聚合函数
- 将一列数据作为一个整体,进行纵向计算
- 聚合函数包括(count,max,min,avg,sum)
#语法
select 聚合函数(字段列表)from 表名;
注意:null值不参与所有聚合函数运算
4. DQL-分组查询
#语法
select 字段列表 from 表名
[where 条件]
group by 分组字段名
[having 分组后过滤条件];
where与having区别
- 执行时机不同:
- where是分组之前进行过滤,不满足where条件,不参与分组
- having是分组之后对结果进行过滤
- 判断条件不同: where不能对聚合函数进行判断,having可以
注意
- 执行顺序: where >聚合函数>having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
5. DQL-排序查询
#1.语法
select 字段列表 from 表名
order by 字段1 排序方式1,字段2排序方式2;😁
#2.排序方式
asc:升序(默认值)
desc:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
6. DQL-分页查询
#1.语法
select 字段列表 from 表名 limit 起始索引,查询记录数;
注意
- 起始索引从o开始,起始索引=(查询页码-1)*每页显示记录数
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 每页显示记录数
2.5.2 DQL执行顺序
select 4
字段列表
from 1
表名列表
where 2
条件列表
group by 3
分组字段列表
having
分组后条件列表
order by 5
排序字段列表
limit 6
分页参数
2.5.3 案例
- 查询年龄为20,21,22,23岁的员工信息
- 查询性别为男,并且年龄在20-40岁(含)以内的姓名为三个字的员工
- 统计员工表中,年龄小于60岁的,男性员工和女性员工的人数
- 查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序
- 查询性别为男,且年龄在20-5O岁(含)以内的前5个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序
#1.查询年龄为20,21,22,23岁的员工信息
select * from emp where age in (20,21,22,23);
#2.查询性别为男,并且年龄在20-40岁(含)以内的姓名为三个字的员工
select * from emp where sex = '男' and (age between 20 and 40) and name like '___';
#3.统计员工表中,年龄小于60岁的,男性员工和女性员工的人数
select sex,count(*) from emp where age < 60 group by sex;
#4.查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序
select name,age from emp where age<=35
order by age asc,entrydate desc;
#5.查询性别为男,且年龄在20-5O岁(含)以内的前5个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序
select * from emp where sex = '男' and age between 20 and 50
order by age asc
limit 0,5;
2.6 DCL(数据控制语言)
- 概念:DCL用来管理数据库用户、控制数据库的访问权限
2.6.1 DCL-用户管理
#1.查询用户USE mysql;
SELECT * from user;
#2.创建用户
CREATE USER'用户名' @ '主机名' IDENTIFIED BY'密码';
#3.修改用户密码
ALTERUSER '用户名' @ '主机名' IDENTIFIED WlTH mysql_native_password BY'新密码';
#4.删除用户
DROP USER'用户名' @ '主机名';
注意:
- 主机名可以使用%通配
- 这类SQL开发人员操作的比较少,主要是DBA (Database Administrator数据库管理员)使用
2.6.2 DCL-权限控制
#1.查询权限
show grant for '用户名' @ '主机名';
#2.授予权限
grant 权限列表 on 数据库名.表名 to '用户名' @ '主机名';
#3.撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名' @ '主机名';
注意:
- 多个权限之间,使用逗号分隔
- 授权时,数据库名和表名可以使用*(代表所有)进行通配
3. 函数
MySQL内置函数
- 字符串函数
- 数值函数
- 日期函数
- 流程控制函数
1. 字符串函数
注意:语法为:SELECT 函数(参数);
案例:
由于业务需求变更,企业员工的工号,统一为5位数,目前不足5位数的全部在前面补>0。比如:1号员工的工号应该为00001update emp set workno = lpad(workno,5,'0');
2. 数值函数
注意:语法:SELECT 函数(参数);
案例:通过数据库的函数,生成一个六位数的随机验证码。
select rpad(round(rand()*1000000,0),6,'0');
3. 日期函数
注意:语法:SELECT 函数(参数);
案例:查询所有员工的入职天数,并根据入职天数倒序排序。
select name, datediff(now(),entrydate) as 'entrydays' from emp order by entrydays desc;
3. 流程控制函数
案例(1):查询emp表的员工姓名和工作地址(北京/上海---->一线城市,其他–—>二线城市)
select name, case workadress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end from emp;
案例(2):统计班级各个学员的成绩,展示的规则如下:
- 展示优秀:[85,100]
- 展示及格:[60,85)
- 不及格:[0,60)
select id, name, (case when Math>=85 then '优秀' when Math>=60 then '及格' else '不及格') '数学', (case when English>=85 then '优秀' when English>=60 then '及格' else '不及格') '英语', (case when Chinese>=85 then '优秀' when Chinese>=60 then '及格' else '不及格') '语文' from score;
4. 约束
4.1 概述
- 概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据
- 目的:保证数据库中数据的正确、有效性和完整性
- 分类:
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
4.2 约束的用法
- 根据下图需求,完成表结构的创建
create table user( id int primary key auto_increment comment '主键', name varchar(10) not null unique comment '姓名', age int check(age > 0 && age <= 120)comment '年龄', status char(1) default '1' comment '状态', gender char(1) comment '性别' )comment '用户表';
注意:auto_increment 在添加失败的数据后,数据库没有数据,但是还会自动增加id值
4.3 外键约束
4.3.1 概念:
- 外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
注意:目前上述的两张表,在数据库层面,并未建立外键关联,所以是无法保证数据的一致性和完整性的。
4.3.2 语法:
- 添加外键
#1.在表中创建
create table 表名(
字段 数据类型 约束条件,
...
[constraint][外键名称] foreign key(外键字段名) references 主表(主表列名)
);
#2.在表外创建
alter table 表名 add constraint 外键名称 foreign key(外键字段名)references 主表(主表列名);
- 删除外键
alter table emp drop foreign key 外键名称;
4.3.3 设置外键删除/更新的行为
- 语法
alter table 表名 add constraint 外键名称 foreign(外键字段) references 主键名(主键字段名)
--on update no action on delete no action;#默认
--on update restrict on delete restrict;#默认
--on update cascade on delete cascade;
--on update set null on delete set null;
--on update set default on set delete set default;
- 总结:
- 非空约束:not null
- 唯一约束:unique
- 主键约束:primary key (自增:auto_increment)
- 默认约束:default
- 检查约束:check
- 外键约束:foreign key
5. 多表查询
5.1 总章
- 多表关系
- 多表查询概述
- 内连接
- 外连接
- 自连接
- 子查询
- 多表查询案例
5.2 多表关系
- 概述
- 设计表结构时,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多
- 多对多
- 一对一
- 设计表结构时,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
5.2.1 一对多/多对一
- 案例:部门与员工的关系
- 关系:一个部门可以对应多个员工,一个员工对应一个部门
- 实现:在多的一方建立外键,指向另一方的主键
5.2.2 多对多
- 案例:学生与课堂的关系
- 关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
create table studegt_course(
id int auto_increment primary key comment '主键',
studentid int not null comment '学生ID',
courseid int not null comment '课程ID',
constraint fk_courseid foreign key (courseid) references course (id),
constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';
5.2.3 一对一
- 案例:用户与用户详情的关系
- 关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
5.3 多表查询
5.3.1 概述
- 概述:指从多张表中查询数据
- 笛卡尔积:指在数学中,两个集合A集合和B集合的所有组合情况。(在多表查询时需要消除无效的笛卡尔积)
5.3.2 多表查询分类
- 连接查询
- 内连接:相当于查询A、B交集部分数据
- 外连接
- 左外连接:查询左表所有数据,以及两张表交集部分数据
- 右外连接:查询右表所有数据,以及两张表交集部分数据
- 自连接:当前表与自身的连接查询,自连接必须使用表别名
子查询
1. 内连接
- 作用:内连接查询的是两张表交集的部分
- 语法
#1.隐式内连接 select 字段列表 from 表1,表2 where 条件...; #2.显示内连接 select 字段列表 from 表1[inner] join 表 2 on 连接条件...;
2. 外连接
-
左外连接:查询左表所有数据,以及两张表交集部分数据
# 相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据 select 字段列表 from 表1 left join 表2 on 条件...;
-
右外连接:查询右表所有数据,以及两张表交集部分数据
# 相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据 select 字段列表 from 表1 right join 表2 on 条件...;
3. 自连接
- 概念:当前表与自身的连接查询,自连接必须使用表别名
--自连接
-- 1.查询员工及其所属领导的名字--表结构: emp
select a.name , b.name from emp a , emp b where a.managerid = b.id;
-- 2.查询所有员工 emp及其领导的名字 emp ,如果员工没有领导,也需要查询出来--表结构: emp a , emp b
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id;
4. 联合查询
-
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
select 字段列表 from 表A ... union[all] select 字段列表 from 表B ...;
-- 将薪资低于5000的员工,和年龄大于50岁的员工全部查询出来 --1.得到含有重复的数据 select * from emp where salary < 500a union all select * from emp where age > 50; --2.删除重复的数据 select * from emp where salary < 5000 union select * from emp where age > 50;
注意:
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
- union all会将全部的数据直接合并在一起,union会对合并之后的数据去重
5.3.3 子查询
- 概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询
- 语法
select*from t1 where column1 = ( select column1 from t2 );
- 子查询外部的语句可以是INSERT /UPDATE /DELETE / SELECT的任何一个
- 根据子查询结果不同分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
- 根据子查询位置分为:
- WHERE之后
- FROM之后
- SELECT之后
1. 标量子查询
- 概念:子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询成为标量子查询
- 常用的操作符:= <> > >= < <=
- 案例:
- 查询" 销售部 "的所有员工信息
select * from emp where dep_id = (select id from dept where name = '销售部');
- 查询在" 方东白 "入职之后的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '东方白');
2. 列子查询
-
子查询返回的结果是一列一行(也可以是多行)
-
常用的操作符:IN、NOT IN 、ANY 、SOME、ALL
-
案例:
- 查询“销售部”和“市场部”的所有员工信息(IN)
select * from emp where dep_id in (select id from dep where name = '销售部' or name = '市场部')
- 查询比财务部所有人工资都高的员工信息(all)
select * from emp where (salary > all (select salary from emp where dep_id = (select * from dep where name = '财务部')));
- 查询比研发部其中任意一人工资高的员工信息(any/some)
#1.any select * from emp where (salary > any (select salary from emp where dep_id = (select * from dep where name = '研发部'))); #2.some select * from emp where (salary > some (select salary from emp where dep_id = (select * from dep where name = '研发部')));
3. 行子查询
-
子查询返回的结果是一行(可以是多列)
-
常用的操作符:= 、<>、IN 、NOT IN
-
查询与" 张无忌 "的薪资及直属领导相同的员工信息
select * from where (salary,manager_id) = (select salary,manager_id from emp where name = '张无忌')
4. 表子查询
-
子查询返回的结果是多行多列
-
常用的操作符:IN
-
案例:
- 查询与" 鹿杖客 “,” 宋远桥 "的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' or name = '宋远桥')
- 查询入职日期是" 2006-01-01 "之后的员工信息,及其部门信息
select * from (select * from emp where entrydata > '2006-01-01') e left join dep d on emp.dep_id = dep.id;
5.3.4 总练习
- 查询员工的姓名、年龄、职位、部门信息
- 查询年龄小于30岁的员工姓名、年龄、职位、部门信息
- 查询拥有员工的部门ID、部门名称
- 查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来
- 查询所有员工的工资等级
- 查询" 研发部 "所有员工的信息及工资等级
- 查询" 研发部 "员工的平均工资
- 查询工资比" 灭绝 "高的员工信息
- 查询比平均薪资高的员工信息
- 查询低于本部门平均工资的员工信息
- 查询所有的部门信息,并统计部门的员工人数
- 查询所有学生的选课情况,展示出学生名称,学号,课程名称
6. 事务
6.1 总章
- 事务简介
- 事务操作
- 事务四大特性
- 并发事务问题
- 事务隔离级别
6.2 事务介绍
- 事务是一组操作的集合,它是一个不可分割的工作单位
- 事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
注意:默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
6.3 事务操作
- 查看/设置事务提交方式
select @@autocommit;-- autocommit=0手动提交事务 autocommit=1自动提交事务
set @@autocommit = 0;--设置事务手动提交
-
提交事务
- 方式一
create table account( id int auto_increment primary key comment '主键id', name varchar(10) comment'姓名', money int comment '余额', )comment '账户表'; insert into account(id,name,money) values(null,'张三',2000), (null,'李四',2000);
select @@autocommit = 0; update account set money = money - 1000 where name = '张三'; 程序出现异常 update account set money = money + 1000 where name = '李四'; commit;提交事务,事务正常就执行提交语句 rollback;回滚事务,事务出现异常就执行回滚语句
- 方式二
# 1.start transaction # 2.start begin start transaction update account set money = money - 1000 where name = '张三'; 程序出现异常 update account set money = money + 1000 where name = '李四'; commit;提交事务,事务正常就执行提交语句 rollback;回滚事务,事务出现异常就执行回滚语句
6.4 事务的四大特性(ACID)
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态(总的不增加,不减小)
- 隔离性(lsolation):事务之间相互隔离,一个事务的执行不应该影响到其他事务的执行
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
6.5 并发事务问题
- 脏读:一个事务读到另外一个事务还没有提交的数据
- 不可重复读:一个事务没有提交之前先后读取同一条记录,但两次读取的数据不同
- 幻读:一个事务按照条件查询数据时,没有对应的数据行。(另一个事务提交插入相同的数据)这个事务在插入数据时,发现这行数据已经存在
事务隔离级别
-
查看事务隔离级别
select @@transaction_isolation;
-
设置事务隔离级别
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable }
注意:事务隔离级别越高,数据越安全,但是性能越低