MySQL概述 安装
略
MySQL命令行基本命令
-- 登录
mysql -uroot -p 回车，然后输入root账户的密码
可选参数
-P 端口
-h ip
-- 列出当前数据库管理系统中有哪些数据库。
show databases;
-- 建库
create database test;
-- 使用库
use test;
-- 展示当前库的表
show tables;
-- 查看当前用的是哪个数据库
select database();
--删除库
drop database bjpowernode;
--退出mysql 可选
exit
quit
ctrl + c
--查看当前mysql版本 登录状态
select version();
--在没有登录mysql之前使用：
mysql --version查询操作
--查询操作须在登录完成 且选择完数据库
--语法格式：select 字段名 from 表名;
--例如：
select id，name,age from user;
select * from user;
select id from user;
--查询语句 一般都是查询所需信息 不需要全部返回
--其中*代表的是全部字段 会先去另一张表里面查询你该表拥有的字段 会对性能有一定损耗
--如果需要全部查询所有字段 建议一个字段一个字段添加上去 用,作为分割
--查询时字段也可参与运算
select age*12 from user;
--查询时字段可起别名 as可忽略不写
select age*12 as '月份' from user;条件查询
常见的过滤条件如下：
| 条件 | 说明 |
|---|---|
| = | 等于 |
| <>或!= | 不等于 |
| >= | 大于等于 |
| <= | 小于等于 |
| > | 大于 |
| < | 小于 |
| between...and... | 等同于 >= and <= |
| is null | 为空 |
| is not null | 不为空 |
| <=> | 安全等于（可读性差，很少使用了）。 |
| and 或 && | 并且 |
| or 或 || | 或者 |
| in | 在指定的值当中 |
| not in | 不在指定的值当中 |
| exists | |
| not exists | |
| like | 模糊查询 |
--条件查询语法格式：
select ... from ... where 过滤条件;
--第一步：先执行from
--第二步：再通过where条件过滤
--第三步：最后执行select，查询并将结果展示到控制台
例如：
select id from user where id = 22;
select * from user where age > 18;
select * from user where name ！= '李';
-- 姓王 %代表通配符 可匹配0-n个字符 _ 也是一种通配符 可匹配1个字符
-- 通配符在前 不会走索引 下文会讲到
select * from user where name like '王%';
-- and 连接 查询年龄为18且姓名为王的数据 交集
select * from user where age = 18 and name='王';
-- or 连接 查询年龄为18或者姓名为王的数据 并集
select * from user where age = 18 or name='王';
-- and和or and优先级较高 可使用（）更改优先级
-- 查询年龄为18或者姓名为王并且邮箱是qq邮箱的数据
select * from user where (age = 18 or name='王') and email like '%@qq.com';
--between...and...等同于 >= and <= 区间 且左小右大 闭区间
select * from user where age >=18 and age <=30;
--等效于
select * from user where age between 18 and 30;
--is null、is not null
-- 查询邮箱为null 的数据
select * from user where email is null;
-- 查询邮箱不为null 的数据
select * from user where email is not null;
-- mysql中 null 和 '' 有区别
--in、not in
--in 等效于 and
select id from user where id in (22,23);
--等效于
select id from user where id = 22 and id = 23;
--not in 等效于or (注意是不等)
select id from user where id not in (22,23);
--等效于
select id from user where id != 22 or id != 23;
排序
-- 按照年龄 升序排序 asc desc代表的是 升序 降序
-- asc可忽略 默认就是升序排列
select * from user order by age asc;
-- 按照年龄 降序排序
select * from user order by age desc;
--也可多字段联合排序 先按照年龄降序 如果年龄相同 则按照邮箱升序排列
-- 可以多字段排序 每个字段都可自定义升序降序 缺省为升序
select * from user order by age desc,email;
select * from user where age > 18 order by age;
-- 执行顺序为
from 数据来源
where 过滤条件
* 展示结果
order by 排序函数
--如何在显示的时候去除重复记录呢？在字段前添加distinct关键字
select distinct age from user;
-- 一旦去重 数据量可能会减少 就会与原表数据条数对不上 所以
--distinct只能出现在所有字段的最前面。
--当distinct出现后，后面多个字段一定是联合去重的
--数据处理函数
-- select 常量;
select 1;
select 100;
select 'li';
--转大写upper和ucase 会将字符串里面的英文字符统一转大写
select upper('asc');
select upper(name) as Uname from user;
select name from user where upper (name) = 'ASD'; --不会走索引 下文讲
--转小写lower和lcase 会将字符串里面的英文字符统一转小写 示例略
--截取字符串substr 查找用户表里面 名字第二个是A的
--substr('被截取的字符串', 起始下标, 截取长度) 截取长度可缺省 缺省默认截取到最后
select name from user where substr(name, 2, 1) = 'A';
--获取字符串长度length
select length('你好 世界');
--获取字符的个数char_length
select char_length('你好 世界');
-- mysql 一个中文是两个长度
--字符串拼接 concat('字符串1', '字符串2', '字符串3'....)
select concat('a', 'b', 'c'); --abc
--去除字符串前后空白trim
select concat(trim(' abc '), 'def');
--默认是去除前后空白，也可以去除指定的前缀后缀，例如：
--去除前置0
select trim(leading '0' from '000111000');
--去除后置0
select trim(trailing '0' from '000111000');
--前置0和后置0全部去除
select trim(both '0' from '000111000');
--rand()和rand(x)
--rand()生成0到1的随机浮点数。
--rand(x)生成0到1的随机浮点数，通过指定整数x来确定每次获取到相同的浮点值。
-- 两次查询结果相同 再=在一次会话中
select rand(22);
select rand(22);
--round(x)和round(x,y)四舍五入
--round(x) 四舍五入，保留整数位，舍去所有小数
--round(x,y) 四舍五入，保留y位小数
--truncate(x, y)舍去 不遵守四舍五入
--ceil与floor
--ceil函数：返回大于或等于数值x的最小整数
--floor函数：返回小于或等于数值x的最大整数
--空处理 ifnull(x, y)，空处理函数，当x为NULL时，将x当做y处理。
-- 时间处理函数
-- 获取当前日期
- curdate()
- current_date()
- current_date
--获取当前时间
- curtime()
- current_time()
- current_time
--当前日期+时间
--now()和sysdate()的区别：
--now()：获取的是执行select语句的时刻。
--sysdate()：获取的是执行sysdate()函数的时刻。
--获取单独的年、月、日、时、分、秒
select year(接收一个datatime类型数据);
select year(now()); --当前年
select month(now()); --当前月
select day(now()); --当前日
select hour(now()); --当前小时 其他同理 minute(分钟) second(秒)
select data(now()); --当前日期
select time(now()); --当前时间
date_add函数 作用：给指定的日期添加间隔的时间，从而得到一个新的日期。
语法格式：date_add(日期, interval expr 单位) 支持fushu
--2024-7-30 三天后的日期
select date_add('2024-7-30',interval 3 day);
--2024-7-30 三年三月后的日期
select date_add('2024-7-30',interval '3,3' YEAR_MONTH);
- year：年
- month：月
- day：日
- hour：时
- minute：分
- second：秒
- microsecond：微秒（1秒等于1000毫秒，1毫秒等于1000微秒）
- week：周
- quarter：季度
--复合单位
- SECOND_MICROSECOND
- MINUTE_MICROSECOND
- MINUTE_SECOND：几分几秒之后
- HOUR_MICROSECOND
- HOUR_SECOND
- HOUR_MINUTE：几小时几分之后
- DAY_MICROSECOND
- DAY_SECOND
- DAY_MINUTE
- DAY_HOUR：几天几小时之后
- YEAR_MONTH：几年几个月之后
--date_format日期格式化函数 将日期转换成具有某种格式的日期字符串，
--通常用在查询操作当中。（date类型转换成char类型）
--语法格式：date_format(日期, '日期格式')
select date_format(now(), '%Y-%m-%d %H:%i:%s');
--在mysql当中，默认的日期格式就是：%Y-%m-%d %H:%i:%s
--str_to_date函数
--该函数的作用是将char类型的日期字符串转换成日期类型date，通常使用在插入和修改操作当中。
select str_to_date('22/11/1998', '%d/%m/%Y');
--dayofweek、dayofmonth、dayofyear函数
--dayofweek：一周中的第几天（1~7），周日是1，周六是7。
--dayofmonth：一个月中的第几天（1~31）
--dayofyear：一年中的第几天（1~366）
select dayofweek(now());
--last_day函数
--获取给定日期所在月的最后一天的日期
-- datediff函数 计算两个日期之间所差天数 时分秒不算，只计算日期部分相差的天数
select datediff('2000-10-02','2001-02-05');
-- timediff函数 计算两个日期所差时间
select timediff('2000-10-02 10:22:24','2001-02-05 11:32:44');
-- if函数 如果条件为TRUE则返回“YES”，如果条件为FALSE则返回“NO”
SELECT IF(500<1000, "YES", "NO");
--cast函数 =用于将值从一种数据类型转换为表达式中指定的另一种数据类型
--语法：cast(值 as 数据类型)
cast('2020-10-11' as date); --表示将字符串'2020-10-11'转换成日期date类型。
--在使用cast函数时，可用的数据类型包括：
- date：日期类型
- time：时间类型
- datetime：日期时间类型
- signed：有符号的int类型（有符号指的是正数负数）
- char：定长字符串类型
- decimal：浮点型
--加密函数 md5函数，可以将给定的字符串经过md5算法进行加密处理，
--字符串经过加密之后会生成一个固定长度32位的字符串，md5加密之后的密文通常是不能解密的
-- 单向加密
select MD5('ABC');
--exists、not exists
--在 MySQL 数据库中，EXISTS（存在）用于检查子查询的查询结果行数是否大于0。
--如果子查询的查询结果行数大于0，则 EXISTS 条件为真。（即存在查询结果则是true。）
select * from user A where exists(select * from contact B where A.emil=B.email);
--not exists 同理分组相关及其函数
--分组函数的执行原则：先分组，然后对每一组数据执行分组函数。
--如果没有分组语句group by的话，整张表的数据自成一组。
--分组函数包括五个：
- max：最大值
- min：最小值
- avg：平均值
- sum：求和
- count：计数
--找出年龄最大
select max(age) from user;
--找出年龄最小
select min(age) from user;
--统计个数 count(*)和count(1)的效果一样，统计该组中总记录行数。
--count(name)统计的是这个ename字段中不为NULL个数总和。
select count(name) from user;
select count(*) from user;
select count(1) from user;
--分组函数不能直接使用在where子句当中
select age from emp where age > avg(age );
--这个会报错的 原因：分组的行为是在where执行之后才开始的
--group by按照某个字段分组，或者按照某些字段联合分组。
--注意：group by的执行是在where之后执行。
--语法：
--group by 字段
--group by 字段1,字段2,字段3....
-- 按照城市分组 并且求出每个城市的平均年龄
select city, avg(age) from user group by city;
--当select语句中有group by的话，c
-- having having写在group by的后面，
--当你对分组之后的数据不满意，可以继续通过having对分组之后的数据进行过滤。
--where的过滤是在分组前进行过滤。
--使用原则：尽量在where中过滤，实在不行，再使用having。越早过滤效率越高。
-- 按照城市分组 并且求出每个城市的平均年龄 再过滤出平均年龄大于18的城市
select city, avg(age) from user group by city having avg(age)>18;
--组内排序
--group_concat函数 因为select后面只能跟分组函数或参加分组的字段 可以使用这个函数把你想要的数--据拼接再一起
--substring_index函数 切割字符串
--执行顺序。
select ...5
from ...1
where ...2
group by ...3
having ...4
order by ...6
limit ... 7连接查询
-
根据连接方式的不同进行分类：
-
内连接
-
等值连接
-
非等值连接
-
自连接
-
-
外连接
-
左外连接（左连接）
-
右外连接（右连接）
-
-
全连接
-
内连接
--示例代码
-- 连接用户表 与联系方式表
-- 条件为 用户的邮箱等于联系方式里面的邮箱
select
A.id,A.name,B.phone
from
user A
join
contact B
on
A.email=B.email;内连接也可以自己和自己连接 这种一般适用于 表里面存储的数据有上下级关系的数据
例如员工表 员工A的上级可能就是员工B 同时存在同一张表中
连接条件就是 A.上级=B
外连接
示例代码 左连接
select
A.id,A.name,B.phone
from
user A
left join
contact B
on
A.email=B.email;
右连接
select
A.id,A.name,B.phone
from
user A
left join
contact B
on
A.email=B.email;任何一个左连接都可以写成右连接 两者可以相互推导
内连接和外连接在图片上面 就可看到差异
比如内连接中 A表中的数据再B表中没有匹配上 所以最终数据不会展示那些数据 内连接是绝对公平的 有就是有 没有就没有 不展示 只展示相关的交集部分
但是外连接 比较偏向其中一张表 （左连接 偏向左表 右连接偏向右表）
外连接允许它偏向的那张表在另一张表中没有连接到 也可以查询出来
即结果为 偏向表+交集
全连接
MySQL不支持full join。oracle数据库支持
从图片就可以看到区别
两张表数据全部查询出来，没有匹配的记录，各自为对方模拟出NULL进行匹配。
多张表连接
--示例代码
select
A.id,A.name,B.phone
from
user A
join
contact B
on
A.email=B.email;
join
test C
on
A.id=C.id 子查询
什么是子查询
select语句中嵌套select语句就叫做子查询。
select语句可以嵌套在哪里？
--where后面
select * from user where age > (select avg(age) from user);
--from后面 from后面的子查询可以看做一张临时表。 仅为示例展示 简单sql复杂化
select * from (select * from user);
--select后面都是可以的
select
A.id,A.name,
(select B.phone from contact where A.email=B.email) as '联系方式'
from
user A
union&union all
不管是union还是union all都可以将两个查询结果集进行合并。 union会对合并之后的查询结果集进行去重操作。 union all是直接将查询结果集合并，不进行去重操作。（union all和union都可以完成的话，优先选择union all，union all因为不需要去重，所以效率高一些。）
select * from user where id = 88
union
select * from user where id = 22;以上案例采用or也可以完成，那or和union all有什么区别？考虑走索引优化之类的选择union all，其它选择or。
两个结果集合并时，列数量要相同
or in exists 效率问题
exists > in > or
limit分页
limit作用：查询第几条到第几条的记录。通常是因为表中数据量太大，需要分页显示。
limit语法格式：limit 开始下标, 长度
--取前三条
select * from user limit 3;
--跳过五条 从第六条开始 取5条 即 6-10
select * from user limit 5,5;
--跳过10条 从11开始取 取两条 即11-12
select * from user limit 10,2;表相关
数据类型
略
创建表
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
......
);
--default '男' 这个为默认值 如果插入时不给这个赋值 默认为男
create table student(
no int,
name varchar(25),
gender char(1) default '男'
);删除表
drop table 表名;
--或者
drop table if exists 表名;
-- 判断是否存在这个表，如果存在则删除。避免不存在时的报错。查看建表语句
show create table 表名;修改表名
alter table 表名 rename 新表名;新增字段
alter table 表名 add 字段名 数据类型;修改字段
-- 修改字段名
alter table 表名 change 旧字段名 新字段名 数据类型;
-- 修改字段类型
alter table 表名 modify column 字段名 数据类型;删除字段
alter table 表名 drop 字段名;新增数据
--表名后面的小括号当中的字段名如果省略掉，表示自动将所有字段都列出来了，
--并且字段的顺序和建表时的顺序一致。一般为了可读性强，建议把字段名写上。
insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);
--或者
insert into 表名 values(值1,值2,值3,...);
--或者 一次插入多条
insert into t_stu(no,name,age) values(1,'jack',20),(2,'lucy',30);删除数据
-- 将所有记录全部删除
delete from 表名;
-- 删除符合条件的记录
delete from 表名 where 条件;
--使用delete删除数据可以恢复 而且删除时间长 可以使用这个删除整张表
truncate table 表名;更新数据
update 表名 set 字段名1=值1, 字段名2=值2, 字段名3=值3 where 条件;约束constraint
创建表时，可以给表的字段添加约束，可以保证数据的完整性、有效性。比如大家上网注册用户时常见的：用户名不能为空。对不起，用户名已存在。等提示信息。 约束通常包括：
-
非空约束：not null
-
检查约束：check
-
唯一性约束：unique
-
主键约束：primary key
-
外键约束：foreign key
这些都可以通过可视化操作 略
数据库三范式
第一范式：任何一张表都应该有主键，每个字段是原子性的不能再分
第二范式：建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：建立在第二范式基础上的，非主键字段不能传递依赖于主键字段
最终以满足客户需求为原则，有的时候会拿空间换速度。
视图
只能将select语句创建为视图 可以将视图看作一张临时表
如果开发中有一条非常复杂的SQL，而这个SQL在多处使用，会给开发和维护带来成本。使用视图可以降低开发和维护的成本。
且视图可以隐藏原本字段名
修改视图数据会影响元数据
创建视图
create
or replace view aa as
select
id as '序号',
name as '姓名',
age as '年龄'
from user;修改视图
alter view view aa as
select
id as '序',
name as '姓名',
age as '年龄'
from user;删除视图
drop view if exists aa;对视图操作
-- 查询
select 序,姓名,年龄 from aa;
-- 条件查
select 序,姓名,年龄 from aa where 序 =2 ;
--更新
update aa set 姓名 ='ling' where 序 = 11;
--删除
delete from aa where 序 =22;看对试图操作时 不需要对原来字段进行操作 且对视图操作时 数据会影响到原表本身
事务
-
事务是一个最小的工作单元。在数据库当中，事务表示一件完整的事儿。
-
一个业务的完成可能需要多条DML语句共同配合才能完成，例如转账业务，需要执行两条DML语句，先更新张三账户的余额，再更新李四账户的余额，为了保证转账业务不出现问题，就必须保证要么同时成功，要么同时失败，怎么保证同时成功或者同时失败呢？就需要使用事务机制。
-
也就是说用了事务机制之后，在同一个事务当中，多条DML语句会同时成功，或者同时失败，不会出现一部分成功，一部分失败的现象。
-
事务只针对DML语句有效：因为只有这三个语句是改变表中数据的。
DML是指 更新 删除 新增语句
事务四大特性：ACID
-
原子性（Atomicity）：是指事务包含的所有操作要么全部成功，要么同时失败。
-
一致性（Consistency）：是指事务开始前，和事务完成后，数据应该是一致的。例如张三和李四的钱加起来是5000，中间不管进行过多少次的转账操作(update)，总量5000是不会变的。这就是事务的一致性。
-
隔离性（Isolation）：隔离性是当多个⽤户并发访问数据库时，⽐如操作同⼀张表时，数据库为每⼀个⽤户开启的事务，不能被其他事务的操作所⼲扰，多个并发事务之间要相互隔离。
-
持久性（Durability）：持久性是指⼀个事务⼀旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
事务隔离级别
隔离级别从低到高排序：读未提交 < 读提交 < 可重复读 < 串行化 不同隔离级别会存在不同的现象，现象按照严重性从高到低排序：脏读 > 不可重复读 > 幻读
查看与设置隔离级别
mysql默认的隔离级别：可重复读（REPEATABLE READ）。
-
查看当前会话的隔离级别：select @@transaction_isolation;
-
查看全局的隔离级别：select @@gobal.transaction_isolation;
设置事务隔离级别：
-
会话级：set session transaction isolation level read committed;
-
全局级：set global transaction isolation level read committed;
脏读
指的是一个事务读取了另一个事务尚未提交的数据，即读取了另一个事务中的脏数据（Dirty Data）。在此情况下，如果另一个事务回滚了或者修改了这些数据，那么读取这些脏数据的事务所处理的数据就是不准确的。
不可重复读
指在一个事务内，多次读取同一个数据行，得到的结果可能是不一样的。这是由于其他事务对数据行做出了修改操作，导致数据的不一致性。
幻读
指在事务执行过程中，前后两次相同的查询条件得到的结果集不一致，可能会变多或变少。
MySQL默认的隔离级别可重复读 如何解决幻读问题
-
针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好的避免了幻读问题。
-
针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。
什么是快照读？普通的select语句都是采用的快照读。顾名思义：在整个事务的处理过程中，执行相同的一个select语句时，每次都是读取的快照。（快照指的是固定的某个时刻的数据，就像现实世界中的拍照一样，把那个美好的时刻留下来）。也就是说，当事务隔离级别是可重复读，并且执行的select语句是一个普通的select语句时，都会采用快照读的方式读取数据，底层实现原理是：
-
底层由 MVCC（多版本并发控制）实现，实现的方式是开始事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好的避免了幻读问题。
当前读，顾名思义：每一次都读取最新的数据。当前读包括：update、delete、insert、select...for update。这个很好理解，因为增删改的时候都要基于最新的数据进行增删改。 而select...for update原理是：对查询范围内的数据进行加锁，不允许其它事务对这个范围内的数据进行增删改。也就是说这个select语句范围内的数据是不允许并发的，只能排队执行，从而避免幻读问题。 select...for update加的锁叫做：next-key lock。我们可以称其为：间隙锁 + 记录锁。间隙锁用来保证在锁定的范围内不允许insert操作。记录锁用来保证在锁定的范围内不允许delete和update操作
对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。
MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。 要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。
DBA命令
DBA是指 创建用户 给用户授权 删除用户 撤销权限 修改普通用户密码 数据备份等等 后期再补充
存储过程
存储过程可称为过程化SQL语言，是在普通SQL语句的基础上增加了编程语言的特点，把数据操作语句(DML)和查询语句(DQL)组织在过程化代码中，通过逻辑判断、循环等操作实现复杂计算的程序语言。 换句话说，存储过程其实就是数据库内置的一种编程语言，这种编程语言也有自己的变量、if语句、循环语句等。在一个存储过程中可以将多条SQL语句以逻辑代码的方式将其串联起来，执行这个存储过程就是将这些SQL语句按照一定的逻辑去执行，所以一个存储过程也可以看做是一组为了完成特定功能的SQL 语句集。 每一个存储过程都是一个数据库对象，就像table和view一样，存储在数据库当中，一次编译永久有效。并且每一个存储过程都有自己的名字。客户端程序通过存储过程的名字来调用存储过程。 在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。
存储过程优缺点
优点：速度快。 降低了应用服务器和数据库服务器之间网络通讯的开销。尤其在数据量庞大的情况下效果显著。
缺点：移植性差。编写难度大。维护性差。
- 每一个数据库都有自己的存储过程的语法规则，这种语法规则不是通用的。一旦使用了存储过程，则数据库产品很难更换，例如：编写了mysql的存储过程，这段代码只能在mysql中运行，无法在oracle数据库中运行。
- 对于数据库存储过程这种语法来说，没有专业的IDE工具（集成开发环境），所以编码速度较低。自然维护的成本也会较高。
在实际开发中，存储过程还是很少使用的。只有在系统遇到了性能瓶颈，在进行优化的时候，对于大数量的应用来说，可以考虑使用一些。
存储过程示例代码
--存储过程的创建
create procedure p1()
begin
select * from user;
end;
--存储过程的调用
call p1();
--查看创建存储过程的语句
show create procedure p1;
--存储过程的删除
drop procedure if exists p1;
通过系统表information_schema.ROUTINES查看存储过程的详细信息： information_schema.ROUTINES 是 MySQL 数据库中一个系统表，存储了所有存储过程、函数、触发器的详细信息，包括名称、返回值类型、参数、创建时间、修改时间等。
select * from information_schema.routines where routine_name = 'p1';
information_schema.ROUTINES 表中的一些重要的列包括：
-
SPECIFIC_NAME：存储过程的具体名称，包括该存储过程的名字，参数列表。
-
ROUTINE_SCHEMA：存储过程所在的数据库名称。
-
ROUTINE_NAME：存储过程的名称。
-
ROUTINE_TYPE：PROCEDURE表示是一个存储过程，FUNCTION表示是一个函数。
-
ROUTINE_DEFINITION：存储过程的定义语句。
-
CREATED：存储过程的创建时间。
-
LAST_ALTERED：存储过程的最后修改时间。
-
DATA_TYPE：存储过程的返回值类型、参数类型等。
MySQL的变量
mysql中的变量包括：系统变量、用户变量、局部变量。
系统变量
MySQL 系统变量是指在 MySQL 服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置，以满足不同的需求。 MySQL 系统变量可以具有全局（global）或会话（session）作用域。
-
全局作用域是指对所有连接和所有数据库都适用；
-
会话作用域是指只对当前连接和当前数据库适用。
查看系统变量
show [global|session] variables;
show [global|session] variables like '';
select @@[global|session.]系统变量名;注意：没有指定session或global时，默认是session。
设置系统变量
set [global | session] 系统变量名 = 值;
set @@[global | session.]系统变量名 = 值;注意：无论是全局设置还是会话设置，当mysql服务重启之后，之前配置都会失效。可以通过修改MySQL根目录下的my.ini配置文件达到永久修改的效果。
用户变量
用户自定义的变量。只在当前会话有效。所有的用户变量'@'开始。
给用户变量赋值
set @name = 'jackson';
set @age := 30;
set @gender := '男', @addr := '郑州';
select @email := 'jackson@123.com';
select sal into @sal from emp where ename ='李';读取用户变量的值
select @name, @age, @gender, @addr, @email, @sal;注意：mysql中变量不需要声明。直接赋值就行。如果没有声明变量，直接读取该变量，返回null
局部变量
在存储过程中可以使用局部变量。使用declare声明。在begin和end之间有效。
变量的声明
declare 变量名 数据类型 [default ...];
create PROCEDURE p2()
begin
/*声明变量*/
declare a int default 0;
/*声明变量*/
declare b double(10,2) default 0.0;
/*给变量赋值*/
select count(*) into a from user;
/*给变量赋值*/
set b := 5000.0;
/*读取变量的值*/
select a;
/*读取变量的值*/
select b;
end;
call p2();变量的数据类型就是表字段的数据类型，例如：int、bigint、char、varchar、date、time、datetime等。 注意：declare通常出现在begin end之间的开始部分。
流程语句
year season count
2010 一季度 100
2010 二季度 200
2010 三季度 300
2010 四季度 400
-- case 语句
SELECT
season,
CASE season
WHEN '一季度' THEN '春天'
WHEN '二季度' THEN '夏天'
WHEN '四季度' THEN '秋天'
WHEN '三季度' THEN '冬天'
ELSE '其他' END
as '状态' FROM t_temp
season 状态
一季度 春天
二季度 夏天
三季度 冬天
四季度 秋天
--if 语句
if 条件 then
......
elseif 条件 then
......
elseif 条件 then
......
else
......
end if;
--创建
create procedure p3()
begin
declare sal int default 5000;
declare grade varchar(20);
if sal > 10000 then
set grade := '高收入';
elseif sal >= 6000 then
set grade := '中收入';
else
set grade := '低收入';
end if;
select grade;
end;
--调用
call p3();
--while循环
--创建 求偶数之和
create procedure sum(in n int)
begin
declare sum int default 0;
while n > 0 do
if n % 2 = 0 then
set sum := sum + n;
end if;
set n := n - 1;
end while;
select sum;
end;
//调用
call sum(100);
--repeat循环 条件成立时结束循环
repeat
循环体;
until 条件
end repeat;
--创建
create procedure sum1(in n int, out sum int)
begin
set sum := 0;
repeat
if n % 2 = 0 then
set sum := sum + n;
end if;
set n := n - 1;
until n <= 0
end repeat;
end;
call sum1(10, @sum);
select @sum;
--loop循环 这个循环可以自己跳过或者跳出
参数
存储过程的参数包括三种形式：
-
in：入参（未指定时，默认是in）
-
out：出参
-
inout：既是入参，又是出参
游标
存储函数
触发器
这三个先欠着 等等再补
存储引擎
在实际开发中，以下存储引擎是比较常用的：
-
InnoDB：
-
MySQL默认的事务型存储引擎
-
支持ACID事务
-
具有较好的并发性能和数据完整性
-
支持行级锁定。
-
适用于大多数应用场景，尤其是需要事务支持的应用。
-
-
MyISAM：
-
是MySQL早期版本中常用的存储引擎
-
支持全文索引和表级锁定
-
不支持事务
-
由于其简单性和高性能，在某些特定的应用场景中会得到广泛应用，如读密集的应用。
-
-
MEMORY：
-
称为HEAP，是将表存储在内存中的存储引擎
-
具有非常高的读写性能，但数据会在服务器重启时丢失。
-
适用于需要快速读写的临时数据集、缓存和临时表等场景。
-
-
CSV：
-
将数据以纯文本格式存储的存储引擎
-
适用于需要处理和导入/导出CSV格式数据的场景。
-
-
ARCHIVE：
-
将数据高效地进行压缩和存储的存储引擎
-
适用于需要长期存储大量历史数据且不经常查询的场景。
-
修改存储引擎
ALTER TABLE my_table ENGINE = MyISAM;索引
索引是一种能够提高检索（查询）效率的提前排好序的数据结构。例如：书的目录就是一种索引机制。索引是解决SQL慢查询的一种方式。
主键会自动添加索引
主键字段会自动添加索引，不需要程序员干涉，主键字段上的索引被称为主键索引
unique约束的字段自动添加索引
unique约束的字段也会自动添加索引，不需要程序员干涉，这种字段上添加的索引称为唯一索引
--如果表已经创建好了，后期给字段添加索引
ALTER TABLE emp ADD INDEX idx_name (name);
--也可以这样添加索引：
create index idx_name on emp(name);
--删除指定字段上的索引
ALTER TABLE emp DROP INDEX idx_name;
--查看某张表上添加了哪些索引
show index from 表名;索引的分类
-
按照数据结构分类：
-
B+树 索引（mysql的InnoDB存储引擎采用的就是这种索引）采用 B+树 的数据结构
-
Hash 索引（仅
memory存储引擎支持）：采用 哈希表 的数据结构
-
-
按照物理存储分类：
-
聚集索引：索引和表中数据在一起，数据存储的时候就是按照索引顺序存储的。一张表只能有一个聚集索引。
-
非聚集索引：索引和表中数据是分开的，索引是独立于表空间的，一张表可以有多个非聚集索引。
-
-
按照字段特性分类：
-
主键索引（primary key）
-
唯一索引（unique）
-
普通索引（index）
-
全文索引（fulltext：仅
InnoDB和MyISAM存储引擎支持）
-
-
按照字段个数分类：
-
单列索引、联合索引（也叫复合索引、组合索引）
-
MySQL索引采用了B+树数据结构
常见的树相关的数据结构包括：
-
二叉树
-
红黑树
-
B树
-
B+树
区别：树的高度不同。树的高度越低，性能越高。这是因为每一个节点都是一次I/O
Data Structure Visualization (usfca.edu) 常见的数据结构可视化网址
普通二叉树 查找10 需要10次io
红黑树 五次
b树三次io
在B Trees中，每个节点不仅存储了索引值，还存储该索引值对应的数据行。 并且每个节点中的p1 p2 p3是指向下一个节点的指针。
B Trees数据结构存在的缺点是：不适合做区间查找，对于区间查找效率较低。假设要查id在[3~7]之间的，需要查找的是3,4,5,6,7。那么查这每个索引值都需要从头节点开始。 因此MySQL使用了B+ Trees解决了这个问题。
B+ Trees 相较于 B Trees改进了哪些？
-
B+树将数据都存储在叶子节点中。并且叶子节点之间使用链表连接，这样很适合范围查询。
-
B+树的非叶子节点上只有索引值，没有数据，所以非叶子节点可以存储更多的索引值，这样让B+树更矮更胖，提高检索效率。
索引优化
最左前缀原则
user表 设置 name age email 复合索引
最左前缀原则表示 当要想使用这些索引去查询时 查询条件里面必须有name的条件
当查询语句条件中包含了这个复合索引最左边的列 name 时，此时索引才会起作用。
范围查询时索引会失效
--这个查询只会使用复合索引的一部分 name 和 age email不走
select * from user where name ='wang' and age> 13 and email = '111@qq.com'
-- 优化 全走索引
select * from user where name ='wang' and age>= 13 and email = '111@qq.com'原因解析 ：sql优化，关于联合索引某个中间字段使用>（大于）号，导致后面索引失效的理解-CSDN博客
避免回表
--主键索引为一级索引 其他都是二级索引
--一张用户表 有id 主键索引 name字段普通索引
-- 数据可以直接从索引上面取值 直接从id索引里面拿 不用回表
select id from user where id = 222;
--首先 id时一级索引 name是二级索引
--一级索引 叶子节点包含的数据 是 该条信息的完整信息（id name age email）
--二级索引 叶子点包含的信息是一级索引数据（主键id）
--该条查询语句的信息 可有name索引 和包含的主键id获得 也不用回表
select id ,name from where name ='wanmg';
--同上 但是age信息拿不到 二级索引查询到ids 后 还需要再去一级索引里面查age信息 回表
select id ,name，age from where name ='wanmg';覆盖索引
覆盖索引（Covering Index）聚合索引，顾名思义，是指某个查询语句可以通过索引的覆盖来完成，而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时，查询需要的所有列都可以从索引中提取到，而不需要再去查询实际数据行获取查询所需数据。 当使用覆盖索引时，MySQL可以直接通过索引，也就是索引上的数据来获取所需的结果，而不必再去查找表中的数据。这样可以显著提高查询性能。主要通过回表来增加性能
覆盖索引具有以下优点：
-
提高查询性能：覆盖索引能够满足查询的所有需求，同时不需要访问表中的实际数据行，从而可以提高查询性能。这是因为DBMS可以直接使用索引来执行查询，而不需要从磁盘读取实际的数据行。
-
减少磁盘和内存访问次数：当使用覆盖索引时，DBMS不需要访问实际的数据行。这样可以减少磁盘和内存访问次数，从而提高查询性能。
-
减少网络传输：由于在覆盖索引中可以存储所有查询所需的列，因此可以减少数据的网络传输次数，从而提高查询的性能。
-
可以降低系统开销：在高压力的数据库系统中，使用覆盖索引可以减少系统开销，从而提高系统的可靠性和可维护性。
覆盖索引的缺点包括：
-
需要更多的内存：覆盖索引需要存储查询所需的所有列，因此需要更多的内存来存储索引。在大型数据库系统中，这可能会成为一项挑战。
-
会使索引变得庞大：当索引中包含了许多列时，它们可能会使索引变得非常庞大，从而影响查询性能，并且可能会占用大量的磁盘空间。
-
只有在查询中包含了索引列时才能使用：只有当查询中包含了所有的索引列时才能使用覆盖索引。如果查询中包含了其他列，DBMS仍然需要访问实际的数据行，并且无法使用覆盖索引提高查询性能。
索引下推
索引下推（Index Condition Pushdown）是一种 MySQL 中的优化方法，它可以将查询中的过滤条件下推到索引层级中处理，从而减少回表次数，优化查询性能。
具体来说，在使用索引下推时，MySQL 会在索引的叶节点层级执行查询的过滤条件，过滤掉无用的索引记录，仅返回符合条件的记录的主键，这样就可以避免查询时回表读取表格的数据行，从而缩短了整个查询过程的时间。
即 where 条件上面设置索引 争取索引多覆盖 查询效率会高
单列索引（单一索引）
单列索引是指对数据库表中的某一列或属性进行索引创建，对该列进行快速查找和排序操作。单列索引可以加快查询速度，提高数据库的性能。
复合索引（组合索引）
复合索引（Compound Index）也称为多列索引（Multi-Column Index），是指对数据库表中多个列进行索引创建。
与单列索引不同，复合索引可以包含多个列。这样可以将多个列的值组合起来作为索引的键，以提高多列条件查询的效率。
相对于单列索引，复合索引有以下几个优势：
-
减少索引的数量：复合索引可以包含多个列，因此可以减少索引的数量，减少索引的存储空间和维护成本。
-
提高查询性能：当查询条件中涉及到复合索引的多个列时，数据库可以使用复合索引进行快速定位和过滤，从而提高查询性能。
-
覆盖查询：如果复合索引包含了所有查询需要的列，那么数据库可以直接使用索引中的数据，而不需要再进行表的读取，从而提高查询性能。
-
排序和分组：由于复合索引包含多个列，因此可以用于排序和分组操作，从而提高排序和分组的性能。
索引的优缺点
索引是数据库中一种重要的数据结构，用于加速数据的检索和查询操作。它的优点和缺点如下：
优点：
-
提高查询性能：通过创建索引，可以大大减少数据库查询的数据量，从而提升查询的速度。
-
加速排序：当查询需要按照某个字段进行排序时，索引可以加速排序的过程，提高排序的效率。
-
减少磁盘IO：索引可以减少磁盘IO的次数，这对于磁盘读写速度较低的场景，尤其重要。
缺点：
-
占据额外的存储空间：索引需要占据额外的存储空间，特别是在大型数据库系统中，索引可能占据较大的空间。
-
增删改操作的性能损耗：每次对数据表进行插入、更新、删除等操作时，需要更新索引，会导致操作的性能降低。
-
资源消耗较大：索引需要占用内存和CPU资源，特别是在大规模并发访问的情况下，可能对系统的性能产生影响。
何时用索引
在以下情况下建议使用索引：
-
频繁执行查询操作的字段：如果这些字段经常被查询，使用索引可以提高查询的性能，减少查询的时间。
-
大表：当表的数据量较大时，使用索引可以快速定位到所需的数据，提高查询效率。
-
需要排序或者分组的字段：在对字段进行排序或者分组操作时，索引可以减少排序或者分组的时间。
-
外键关联的字段：在进行表之间的关联查询时，使用索引可以加快关联查询的速度。
在以下情况下不建议使用索引：
-
频繁执行更新操作的表：如果表经常被更新数据，使用索引可能会降低更新操作的性能，因为每次更新都需要维护索引。
-
小表：对于数据量较小的表，使用索引可能并不会带来明显的性能提升，反而会占用额外的存储空间。
-
对于唯一性很差的字段，一般不建议添加索引。当一个字段的唯一性很差时，查询操作基本上需要扫描整个表的大部分数据。如果为这样的字段创建索引，索引的大小可能会比数据本身还大，导致索引的存储空间占用过高，同时也会导致查询操作的性能下降。
总之，索引需要根据具体情况进行使用和权衡，需要考虑到表的大小、查询频率、更新频率以及业务需求等因素。
MySQL优化手段
MySQL数据库的优化手段通常包括但不限于：
-
SQL查询优化：这是最低成本的优化手段，通过优化查询语句、适当添加索引等方式进行。并且效果显著。
-
库表结构优化：通过规范化设计、优化索引和数据类型等方式进行库表结构优化，需要对数据库结构进行调整和改进
-
系统配置优化：根据硬件和操作系统的特点，调整最大连接数、内存管理、IO调度等参数
-
硬件优化：升级硬盘、增加内存容量、升级处理器等硬件方面的投入，需要购买和替换硬件设备，成本较高
主要掌握：SQL查询优化
查看数据库整体情况
--通过以下命令可以查看当前数据库在SQL语句执行方面的整体情况
show global status like 'Com_select';
show global status like 'Com_insert';
show global status like 'Com_delete';
show global status like 'Com_update';
show global status like 'Com_______';
这些结果反映了从 MySQL 服务器启动到当前时刻，
所有的 SELECT 查询总数。对于 MySQL 性能优化来说，
通过查看 `Com_select` 的值可以了解
SELECT 查询在整个 MySQL 服务期间所占比例的情况
--慢查询日志
show variables like 'slow_query_log';
--通过show profiles可以查看一个SQL语句在执行过程中具体的耗时情况。帮助我们更好的定位问题所在
show profiles;
show profile for query 1;
--想查看执行过程中cpu的情况，可以执行以下命令
show profile cpu for query 4;
--explain命令可以查看一个DQL语句的执行计划，根据执行计划可以做出相应的优化措施。提高执行效率
explain select * from user where id=7369;
对于复杂sql 情况如下
id反映出一条select语句执行顺序，id越大优先级越高。id相同则按照自上而下的顺序执行。
select_type
反映了mysql查询语句的类型。常用值包括：
-
SIMPLE：表示查询中不包含子查询或UNION操作。这种查询通常包括一个表或是最多一个联接（JOIN）
-
PRIMARY：表示当前查询是一个主查询。（主要的查询）
-
UNION：表示查询中包含UNION操作
-
SUBQUERY：子查询
-
DERIVED：派生表（表示查询语句出现在from后面）
table
反映了这个查询操作的是哪个表。
type
反映了查询表中数据时的访问类型，常见的值：
-
NULL：效率最高，一般不可能优化到这个级别，只有查询时没有查询表的时候，访问类型是NULL。例如：select 1;
-
system：通常访问系统表的时候，访问类型是system。一般也很难优化到这个程序。
-
const：根据主键或者唯一性索引查询，索引值是常量值时。explain select * from emp where empno=7369;
-
eq_ref：根据主键或者唯一性索引查询。索引值不是常量值。
-
ref：使用了非唯一的索引进行查询。
-
range：使用了索引，扫描了索引树的一部分。
-
index：表示用了索引，但是也需要遍历整个索引树。
-
all：全表扫描
效率最高的是NULL，效率最低的是all，从上到下，从高到低。
possible_keys
这个查询可能会用到的索引
key
实际用到的索引
key_len
反映索引中在查询中使用的列所占的总字节数。
rows
查询扫描的预估计行数。
Extra
给出了与查询相关的额外信息和说明。这些额外信息可以帮助我们更好地理解查询执行的过程。
索引失效原因
索引列参加了运算，索引失效
索引列进行模糊查询时以 % 开始的，索引失效
索引列是字符串类型，但查询时省略了单引号，索引失效
查询条件中有or，只要有未添加索引的字段，索引失效
当查询的符合条件的记录在表中占比较大，索引失效
关于is null和is not null的索引失效问题 走索引还是不走索引，根数据分布有很大关系，如果符合条件的记录占比较大，会考虑使用全表扫描，而放弃走索引。
指定索引
当一个字段上既有单列索引，又有复合索引时，我们可以通过以下的SQL提示来要求该SQL语句执行时采用哪个索引：
-
use index(索引名称)：建议使用该索引，只是建议，底层mysql会根据实际效率来考虑是否使用你推荐的索引。
-
ignore index(索引名称)：忽略该索引
-
force index(索引名称)：强行使用该索引
select * from user use index(idx_name) where name='zhangsan';索引创建原则
-
表数据量庞大，通常超过百万条数据。
-
经常出现在where，order by，group by后面的字段建议添加索引。
-
创建索引的字段尽量具有很强的唯一性。
-
如果字段存储文本，内容较大，一定要创建前缀索引。
-
尽量使用复合索引，使用单列索引容易回表查询。
-
如果一个字段中的数据不会为NULL，建议建表时添加not null约束，这样优化器就知道使用哪个索引列更加有效。
-
不要创建太多索引，当对数据进行增删改的时候，索引需要重新重新排序。
-
如果很少的查询，经常的增删改不建议加索引。
SQL优化
order by的优化
explain查看一个带有order by的语句时，Extra列会显示：using index 或者 using filesort，区别是什么？
-
using index: 表示使用索引，因为索引是提前排好序的。效率很高。
-
using filesort：表示使用文件排序，这就表示没有走索引，对表中数据进行排序，排序时将硬盘的数据读取到内存当中，在内存当中排好序。这个效率是低的，应避免。
order by 优化原则总结：
-
排序也要遵循最左前缀法则。
-
使用覆盖索引。
-
针对不同的排序规则，创建不同索引。（如果所有字段都是升序，或者所有字段都是降序，则不需要创建新的索引）
-
如果无法避免filesort，要注意排序缓存的大小，默认缓存大小256KB，可以修改系统变量 sort_buffer_size ：
show variables like 'sort_buffer_size';group by优化
group by遵循最左前缀法则
limit优化
数据量特别庞大时，取数据时，越往后效率越低，怎么提升？mysql官方给出的解决方案是：使用覆盖索引+子查询的形式来提升效率
主键优化
主键设计原则：
-
主键值不要太长，二级索引叶子结点上存储的是主键值，主键值太长，容易导致索引占用空间较大。
-
尽量使用auto_increment生成主键。尽量不要使用uuid做主键，因为uuid不是顺序插入。
-
最好不要使用业务主键，因为业务的变化会导致主键值的频繁修改，主键值不建议修改，因为主键值修改，聚集索引一定会重新排序。
-
在插入数据时，主键值最好是顺序插入，不要乱序插入，因为乱序插入可能会导致B+树叶子结点频繁的进行页分裂与页合并操作，效率较低。
-
主键值对应聚集索引，插入主键值如果是乱序的，B+树叶子结点需要不断的重新排序，重排过程中还会频繁涉及到页分裂和页合并的操作，效率较低。
-
B+树上的每个节点都存储在页（page）中。一个页面中存储一个节点。
-
MySQL的InnoDB存储引擎一个页可以存储16KB的数据。
-
如果主键值不是顺序插入的话，会导致频繁的页分裂和页合并。在一个B+树中，页分裂和页合并是树的自动调整机制的一部分。当一个页已经满了，再插入一个新的关键字时就会触发页分裂操作，将页中的关键字分配到两个新的页中，同时调整树的结构。相反，当一个页中的关键字数量下降到一个阈值以下时，就会触发页合并操作，将两个相邻的页合并成一个新的页。如果主键值是随机的、不是顺序插入的，那么页的利用率会降低，页分裂和页合并的次数就会增加。由于页的分裂和合并是比较耗时的操作，频繁的分裂和合并会降低数据库系统的性能。因此，为了优化B+树的性能，可以将主键值设计成顺序插入的，这样可以减少页的分裂和合并的次数，提高B+树的性能。在实际应用中，如果对主键值的顺序性能要求不是特别高，也可以采用一些技术手段来减少页分裂和合并，例如B+树分裂时采用“延迟分裂”技术，或者通过调整页的大小和节点的大小等方式来优化B+树的性能。
-
insert优化
多数据批量查询
update优化
当存储引擎是InnoDB时，表的行级锁是针对索引添加的锁，如果索引失效了，或者不是索引列时，会提升为表级锁。 什么是行级锁？A事务和B事务，开启A事务后，通过A事务修改表中某条记录，修改后，在A事务未提交的前提下，B事务去修改同一条记录时，无法继续，直到A事务提交，B事务才可以继续。
count(*)优化
分组函数count的使用方式：
-
count(主键)
-
原理：将每个主键值取出，累加
-
-
count(常量值)
-
原理：获取到每个常量值，累加
-
-
count(字段)
-
原理：取出字段的每个值，判断是否为NULL，不为NULL则累加。
-
-
count(*)
-
原理：不用取值，底层mysql做了优化，直接统计总行数，效率最高。
-
结论：如果你要统计一张表中数据的总行数，建议使用 count(*)
注意：
-
对于InnoDB存储引擎来说，count计数的实现原理就是将表中每一条记录取出，然后累加。如果你想真正提高效率，可以自己使用额外的其他程序来实现，例如每向表中插入一条记录时，在redis数据库中维护一个总行数，这样获取总行数的时候，直接从redis中获取即可，这样效率是最高的。
-
对于MyISAM存储引擎来说，当一个select语句没有where条件时，获取总行数效率是极高的，不需要统计，因为MyISAM存储引擎维护了一个单独的总行数。
91
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
