mysql8.0学习笔记 ①

DDL-数据定义语言

对数据库和数据表结构进行定义。

对数据库的常用操作

# 查看所有的数据库
show databas;

# 创建数据库
create database [if not exists] 数据库名 [charset=utf8]

#  切换数据库
use 数据库名;

# 删除数据库
drop database [if exists] 数据库名;

# 修改数据库编码
alter database 数据库名 set utf8

对表结构的常用操作

# 创建表格式
create table [if not exists] 表名(
    字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];

英文字母和汉字所用字节大小

• 1个英文字母->2个字节；

• 1个汉字->2个字节；

• 1个数字->1个字节。

  *有的id是时间戳或生成的雪花id要用bigint

数据类型

1. 数值类型

   字段加上unsigned表示无符号

   

2. 日期和时间类型

   

3. 字符串类型

   字符串类型使用单引号赋值

   

对表结构的常用操作

# 查看当前数据库的所有表名称
show tables;

# 查看指定某个表的创建语句
show create table 表名;

# 查看表结构
desc 表名;

# 删除表
drop table 表名;

修改表结构

# 添加新的列-语法格式(关键字不区分大小写)
alter table 表名 add 列名 类型(长度) [约束];

# 修改列名和类型
alter table 表名 change 旧列名 新列名 类型(长度) 约束;

# 修改表删除列
alter table 表名 drop 列名;

# 修改表名
rename table 表名 to 新表名;

DML-数据操作语言

主要对数据库中表的数据记录进行更新。

插入数据

# 向表中插入某些数据
insert into 表(列名1,列名2,列名3......) values(值1,值2,值3......);

# 向表中插入所有列
insert into 表 values(值1,值2,值3......);

修改数据

# 格式一（值为字符串记得用单引号包起来）
update 表名 set 字段名1=值1,字段名2=值2...;

# 格式二
update 表名 set 字段名1=值1,字段名2=值2... where 条件;

删除数据

# drop关键字用于删除表结构
# delete关键字删除表中数据
delete from 表名 [where 条件];

# truncate关键字清空表数据
# truncate和delete的区别：
# 实现原理不同，truncate类似drop，删除表后，再新建该表（空的）
truncate table 表名;

truncate 表名;

约束

约束实际上就是表中数据的限制条件。

分类：

• 主键约束（primary key）PK
• 自增长约束（auto_increment）
• 非空约束（not null）
• 唯一性约束（unique）
• 默认约束（default）
• 零填充约束（zerofill）
• 外键约束（foreign key）FK

主键约束👇

# 主键列唯一且不为空
# 设置单个字段主键-1
create table 表名(
    sid int primary key,
    ......
);

# 设置单个字段主键-2
create table 表名(
   name varchar(20),
    ......
   sid int,
   constraint 约束名 primary key (字段名)
);

# 设置单个字段主键-3(表已经存在的情况)
alter table 表名 add primary key(字段名);

# 设置多个字段主键-1
create table 表名(
   name varchar(20),
    ......
   sid int,
   constraint 约束名 primary key (字段名1,字段名2......)
);

# 设置多个字段主键-2(表已经存在的情况)
alter table 表名 add primary key(字段名1,字段名2......);

# 删除主键
alter table 表名 drop primary key;

自增长约束👇

自增长约束和主键约束一起用，每新增一条记录，主键会自动以相同的步长进行增长。

只能用于整型字段，超出字段范围就会失效。

字段名 数据类型 auto_increment

# 例子
sid int primary key auto_increment

非空约束👇

# 方法一（表yi'j）
alter table 表名 modify 字段名 类型 not null;

# 方法二(定义表时)
字段名 类型 not null;

唯一约束👇

# 方法一
字段名 类型 unique

# 方法二
alter table 表名 add constraint 约束名 unique(列);

默认约束👇

# 方法一,特殊值为字符串需要用单引号括起来
字段名 类型 default 默认值

# 方法二
alter table 表名 modify 字段名 类型 default 默认值;

# 删除默认约束
alter table 表名 modify 字段名 类型 default null;

零填充约束👇

# 拆数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0

# zerofill默认为int(10)

# 添加零填充约束
create table 表名(

    字段名 int zerofill--零填充约束
);

# 删除零填充约束
alter table 表名 modify 字段名 int;

DQL

基本查询

# 查询语句格式（完整版）
select [all | distinct]
列名1 [别名] ,列名2 [别名]......
from 表名或试图名 [别名] ,表名或试图名 [别名]......
[where 条件表达式]
[group by 列名]
[having 条件表达式]
[order by 列名 asc|desc]
# desc是降序排列，asc是升序排列（默认升序）
[limit 数字或列表]

# 简化版
select * from 表名 where 条件

运算符

# 算数运算符

+ 加法运算
- 减法运算
* 乘法运算
/ 或 div 除法运算
% 或 mod 求余运算

# 算数运算符例子
select name, price + 100 as new_price
from product;
select name, price * 2.1 as newp 
from product;

# 比较运算符

= 等于
< 和 <= 小于和小于等于
> 和 >= 大于和大于等于
<=> 安全的等于，两个操作码均为null时，其所得值位1；而当一个操作码为null时，其所得值为0
<> 或 != 不等于
is null 或 isnull 判断一个值是否为null
is not null 判断一个值是否不为null
least 当有两个或多个参数时，返回最小值
greatest 当有两个或多个参数时，返回最大值
between and 判断一个值是否落在两个值之间
in 判断一个值是in列表中的任意一个值
not in 判断一个值不是in列表中的任意一个值
like 通配符匹配
regexp 正则表达式匹配

# 比较运算符例子
select * from product 
where price is not null

# 逻辑运算符

not 或者 ! 逻辑非（真变成假，假变成真）
and 或者 && 逻辑与（条件都得满足）
or 或者 || 逻辑或（有一个满足即可）
xor 逻辑异或（相同为假，不同为真）

# 逻辑运算符例子
select * from product
where pname = '妞子水瓶' and price > 100

# 位运算符

| 按位或
& 按位与
^ 按位异或
<< 按位左移
>> 按位右移
~ 按位取反（反转所有比特）

聚合查询

count() - 记录行数
sum() - 数值和
max() - 最大值
min() - 最小值
avg() - 平均值

# 例子
select count(id) from product;
select max(price) max_price from product;

聚合查询的null值处理

count(*) 统计所有记录个数，不统计null值的记录个数

count(*)和count(1)等价

sum()、max()、min()、avg()忽略null值的存在

分组查询

having用于分组后筛选，从分组结果中筛选

where用于筛选from子句中指定的操作所产生的行

group by子句用于分组where子句的输出

select 字段,字段......from 表名
where 条件
group by 分组字段
having 分组条件;

分页查询-limit

# 显示前n条记录
select 字段,字段...... from 表名 limit n

# 分页显示
# m:整数，表示从第几条索引开始
# n:整数，表示查询多少条记录
select 字段,字段...... from 表名 limit m,n

insert into select 语句

# 将表1中查到的内容放入表2中
# 表2必须存在
insert into 表2 select * from 表1

正则表达式

正则表达式描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、定义匹配规则，匹配一系列符合某个语法规则的字符串。

在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。

^ 匹配输入字符串的开始位置

$ 匹配输入字符串的结束位置

. 匹配除\n之外的任何单个字符

[...] 字符集合，匹配所包含的任意一个字符，例如[abc]可以匹配"plain"中的a

[^...] 负值字符集合，匹配未包含的任意字符，例如[^abc]可以匹配"plain"中的p

p1|p2|p3 匹配p1或p2或p3,例如'z|food'能匹配"z"或"food",'(z|f)ood'匹配"zood"或"food"

* 匹配前面的子表达式零次或多次，例如，zo*能匹配"z"以及"zoo"。*等价于{0,}

+ 匹配前面的子表达式一次或多次，例如，'zo+'能匹配"zo"以及"zoo",但不能匹配z。+等价于{1,}

{n} n是一个非负整数，匹配确定的n次，例如，'o{2}'不能匹配"Bob"中的'o',但能匹配"food"中的两个o

{n,m} m和n均为非负整数，其中n<=m,最少匹配n次且最多匹配m次。

# ^ 在字符串开始处进行匹配
select 'abc' regexp '^a';
# 找到“牛”开头的记录
select * from product where pname regexp '^牛';

# $ 在字符串末尾开始匹配
select 'abc' regexp 'a$';
# 找到“水”结尾的记录
select * from product where pname regexp '水$';

# . 匹配任意单个字符串(可以匹配除了换行符之外的任意字符)
select 'abc' regexp '.b'; -- 结果1
select 'abc' regexp 'a.'; -- 结果1

# [...] 匹配括号内的任意单个字符
select 'abc' regexp '[ghj]'; -- 结果0,ghj没有一个字符出现在abc中
select 'abc' regexp '[aer]'; -- 结果1

# [^...] 没有匹配括号内任意字符
select 'a' regexp '[^abc]'; -- 结果0，a和括号内的a匹配
select 'x' regexp '[^acv]'; -- 结果1，x不和括号内任意一个字符相匹配
select 'abc' regexp '[^a]'; -- 结果1，a虽然不和括号内的a匹配，但是bc不和括号内的a匹配

# a* 匹配0/多个a,包括空字符串，可以作为占位符使用，有没有指定字符串都可以匹配到数据
select 'stab' regexp '.ta*b'; -- .匹配到了s,a*匹配到了1个a,结果是1
select 'stb' regexp '.ta*b'; -- .匹配到了s,a*匹配了0个a，结果是1
select '' regexp 'a*'; -- 空字符串匹配了0个a，结果是1

# a+ 匹配1/多个a，但是不包括空字符
select 'stab' regexp '.ta+b'; -- .匹配到了s,a+匹配到了1个a,结果是1
select 'stb' regexp '.ta+b'; -- .匹配到了1个s,a+匹配到了0个a,结果是0

# a? 匹配0个/1个a
select 'stb' regexp '.ta?b'; -- .匹配到了s,a?匹配到了0个a,结果是1
select 'stab' regexp '.ta?b';-- .匹配到了s,a?匹配到了1a,结果是1
select 'staab' regexp '.ta?b';-- .匹配到了s,a?匹配到了2a,结果是0

# a1|a2 匹配a2或a2
select 'a' regexp 'a|b'; -- 1
select 'b' regexp 'a|b'; -- 1
select 'b' regexp '^(a|b)'; -- 1,^只有在[]中才表示取反，这里表示以a或者b开头
select 'c' regexp '^(a|b)'; -- 0，c不匹配以a或者b开头这个规则

# a{m} 匹配m个a
select 'auuuuc' regexp 'au{4}c'; -- 1,u出现了4次符合u出现4次的规则
select 'auuuuc' regexp 'au{3}c'; -- 0,u不匹配出现3次的规则，u出现了4次

# a{m,} 匹配m个或更多个a 
select 'auuuc' regexp 'au{2,}c'; -- 1,u符合最少匹配到2次的规则
select 'auuuuc' regexp 'au{4,}c'; -- 
select 'auuuuc' regexp 'au{12,}c'; -- 0,u不符合最少匹配到12次的规则，只有4个u

# (abc) 作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号，所以括号适合上面的所有情况
select 'xabcy' regexp 'x(abc)y'; -- 1
select 'xabcy' regexp 'x(ab){1,2}y'; -- 1,ab出现了1次符合ab出现1-2次的规则

多表操作

创建外键约束

[constraint <外键名> ] foreign key 字段名 [,字段名2,...] references <主表名> 主键列 [,主键列2,...] 

# 例子1
create table if not exists emp(
    eid varchar(20) primary key, 
    ename varchar(20),
    dept_id varchar(20),
    constraint emp_fk foreign key ( dept_id) references dept (detpno)
);

# 例子2(emp和dept两个表都存在)
alter table emp add constraint dept_fk foreign key(dept_id) references dept(deptno);

多表联合查询

交叉连接查询（产生笛卡尔积，会产生冗余数据）

假如a表有m行数据，b表有n行数据，则返回m*n行数据

select * from a,b;

内连接查询（使用关键字inner join – inner可以省略）

内连接查询多张表的交集

# 显示内连接
select * from a inner join b on 条件;

# 隐式内连接
select * from a,b where 条件;

外连接查询（使用关键字outer join – outer可以省略）

# 左外链接
select * from a left join b on 条件;

# 右外连接
select * from a right join b on 条件;

# 满外连接
select * from a full outer join b on 条件;

# 子查询（select的嵌套）


# 表自关联（将一张表当成多张表来用）

DCL

数据控制语言：Data Control Language。用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视。

创建用户

create user 用户名@地址 identified  by '密码';
-- 创建一个testuser用户,密码111
create user testuser@localhost identidied by '111';

给用户授权

GRANT 权限1, … , 权限n ON 数据库.对象  TO 用户名;
--将test数据库中所有对象(表、视图、存储过程，触发器等。*表示所有对象)的create,alter,drop,insert,update,delete,select赋给testuser用户
grant create,alter,drop,insert,update,delete,select on test.* to testuser@localhost;

撤销授权

REVOKE权限1, … , 权限n ON 数据库.对象 FORM 用户名;
--将test数据库中所有对象的create,alter,drop权限撤销
revoke create,alter,drop on test.* to testuser@localhost;

查看用户权限

SHOW GRANTS FOR 用户名;
--查看testuser的用户权限
show grants for testuser@localhost;

删除用户

DROP USER 用户名;
--删除testuser用户
drop user testuser@localhost;

修改用户密码

USE mysql;
UPDATE USER 
SET PASSWORD=PASSWORD(‘密码’) 
WHERE User=’用户名’ and Host=’IP’;
FLUSH PRIVILEGES;
--将testuser的密码改为123456
update user 
set password=password('123456') 
where user='testuser' and host=’localhost’;
FLUSH PRIVILEGES

多表查询事务

内连接-返回两表交集的内容

-- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件
-- 隐式内连接
select 字段列表 from 表1,表2 where 条件;

左外连接-返回的数据是左表的全部数据＋左表和右边相交的数据

select 字段列表 from 左表 left [outer] join 表2 on 条件;

右外连接-返回的数据是右表的全部数据+右表和左表相交的数据

select 字段列表 from 表1 right [outer] join 右表 on 条件;

自连接-可以是内连接查询也可以是外连接查询

select 字段列表 from 表a 别名a join 表a 别名b on 条件;
select 字段列表 from 表a 别名a left join 表a 别名b on 条件;

联合查询-union,union all

-- 查询 薪资低于5000的员工，和年龄大于50的员工
-- union all 可以将两条sql查询结果合并在一起显示
-- 只写union不加all，会将查询结果进行去重处理
-- 如果两条语句查询的字段列数和字段类型不一样会报错
select * from emp where salary < 5000
union all
select * from emp where age > 50;

子查询

根据子查询位置划分：

• where之后
• from之后
• select之后

根据子查询结果不同划分：

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为多行多列）

-- 子查询外部的语句可以是insert/update/select/delete的任何一个
select * from 表1 where column=(select column from 表2)

--  查询比研发部其中任意一人工资高的员工信息
-- 查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
-- 比研发部其中任意一人工资高的员工信息
-- any和some效果一样，查询结果有一个满足条件就行
-- all要求查询结果都得满足条件
select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = 'm发部') );

-- 查询在“方东白” 入职之后的员工信息
-- 1. 查询 方东白 的入职日期
select entrydate from emp where name = '方东白';
-- 2. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白);                          

子查询返回的结果是一行(可以是多列)，

这种子查询称为行子查询常用的操作符:=、<>、IN、NOT IN

行子查询

-- 查询与“张无忌” 的薪资及直属领导相同的员工信息 
-- 1. 查询“张无忌” 的薪资及直属领导
select salary，managerid from emp where name ='张无忌';
-- 2. 查询与“张无忌” 的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name =张无忌);

表子查询

子查询返回的结果是多行多列，

这种子查询称为表子查询常用的操作符:IN

-- 查询与“鹿杖客”，“宋远桥” 的职位和薪资相同的员工信息
-- a. 查询 “鹿权客”"宋远桥” 的职位和薪资
select job，salary from emp where nam ='鹿杖客'or name ='宋远桥';
-- b. 查询与“鹿杖客”，“宋远桥” 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name = '魔秋客' or name = '宋远桥' );

-- 查询入职日期是 “2006-01-01” 之后的员工信息 ， 及其部门信息
-- a. 入职日期是 “2006-01-01” 之后的员工信息
select * from emp where entrydate > '2006-01-01';
-- b. 查询这部分员工， 对应的部门信息;
select e,*, d* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e,dept_id = d.id ;

事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

-- 查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit=0;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

-- 转账例子-转账作 (张三给李四转账1000)-方式1
SELECT @@autocommit;
SET @@autocommit=0; -- 改为手动提交事务
-- 1.查询张三账户余额
select * from account where name = '张三';
-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name ='张三';
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
-- 如果上面几句没有报错，就提交事务
COMMIT;
-- 如果上面几句有报错，就回滚
rollback;

-- 转账例子-转账作 (张三给李四转账1000)-方式2
-- 开启事务
start transaction; -- 或者begin;
-- 1.查询张三账户余额
select * from account where name = '张三';
-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name ='张三';
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
-- 如果上面几句没有报错，就提交事务
COMMIT;
-- 如果上面几句有报错，就回滚
rollback;

事务四大特性

• 原子性(Atomicity): 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性(Consistency): 事务完成时，必须使所有的数据都保持一致状态。
• 隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行持久性
• 持久性(Durability): 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

-- 查看事务隔离级别
SELECT @@TRANSACTION ISOLATION;
-- 设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACION ISOLATON LEVEL {READ UNCOMMITTED|READ COMMITTED  |REPEATABLE READ|SERIALZABLE}

存储引擎

mysql体系结构
默认的是innoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 My5QL5.5之后，innoDB是默认的 MySQL存储引擎

特点:

• DML操作遵循ACID模型，支持事务
• 行级锁，提高并发访问性能;
• 支持外键 FOREIGN KEY约束，保证数据的完整性和正确性

MyISAM

MyISAM是MySQL早期的默认存储引擎

特点:

• 不支持事务
• 不支持外键支持表锁
• 不支持行锁
• 访问速度快

索引

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，

主要包含以下几种:

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引	底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效不支持范围查询
R-tree（空间索引）	空间索引是MVSAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）	是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

索引的语法

-- 创建索引
CREATE[UNIQUE|FULLTEXT] INDEX index_name ON table_name ( index_col_name,... ) ;
-- 查看索引
SHOW INDEX FROM table name :
-- 删除索引
DROP INDEX index name ON table name ;

sql性能分析

-- sql执行频率
-- 查看当前数据库的insert、update、delete、select的访问频次
-- 
show global status like 'Com_______'
-- select的权重高，就需要进行sql优化

-- 慢查询日志
-- 慢查询日志记录了所有执行时间超过指定参数 (long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件 (/etc/my.cnf)中配置如下信息:
show variables like 'slow query log'; -- 查看慢查询日志是否开启
# 开启MySQL慢日志查询开关
slow query_log=1
# 设置慢日志的时间为2秒，SOL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
-- show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have profiling参数，能够看到当前MVSOL是否支持profile操作:
SELECT @@have profiling ;
默认profiling是关闭的，可以通过set语句在session/glqbal级别开启profiling:
SET profiling = 1;
#查看每一条SQL的耗时基本情况
show profiles;
#查看指定query id的SQL语句各个阶段的耗时情况
show profile for query query_id
#查看指定query id的SQL语句CPU的使用情况
show profile cpu for query query_id;

SQL性能分析

explain执行计划
EXPLAIN执行计划各字段含义:
Id
-- select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
select type
-- 表示SELECT 的类型，常见的取值有 SIMPLE (简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
type
-- 表示连接类型，性能由好到差的连接类型为NULL、System、const、eq_ref、ref、range、index、all。
possible_key
-- 显示可能应用在这张表上的索引，一个或多个。
Key
-- 实际使用的索引，如果为NULL，则没有使用索引。
Key_len
-- 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows
-- MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered
-- 表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好
高

索引的使用

最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

其他失效原因

字段使用>或<,索引失效

使用函数，索引失效

字段值不加单引号，索引失效（因为不加单引号就需要类型转换）

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

sql提示

sql提示，是优化数据库的一个重要手段，简单来说，就是在S0L语句中加入一些人为的提示来达到优化操作的目的

-- use index;建议使用（mysql还会考虑一下对不对
explain select* from tb user use index(idx user pro) where profession = '软件工程'
-- ignore index;忽略使用
explain select* from tb user ignore index(idx user pro) where profession = '软件工程'
-- force index;强制使用
explain select* from tb user force index(idx user prol where profession = '软件工程'

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select*。

前缀索引

当字段类型为字符串(varchar，tet等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁10，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

create index idx xxoxx on table name(columnn));

前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

select count(distinct email) / count(*) from tb_user ;

select count(distinct substring(email,1,5)) / count(*) from tb_user ;

覆盖索引

查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到，尽量使用覆盖索引，减少select*。

视图

-- 创建视图
create or replace view 视图名 as select 查询的字段 from 查询的表 where 条件;
-- 查询创建视图语句
show create view 视图名;
-- 查看视图数据
select * from 视图名 ......;
-- 修改
-- 方式一:
CREATE [OR REPLACE] VIEW 视图名称(列名列表] AS SELECT语句 [WITH[CASCADED LOCAL] CHECK OPTION)
-- 方式二:
ALTER VIEW 视图名称/(列名列表] AS SELECT语句[WITH[CASCADEDI LOCAL] CHECK OPTION]
-- 删除
DROP VIEW [IF EXISTS] 视图名称 [视图名称]

存储过程

存储过程就是将sql语言进行封装和重用

-- 创建存储过程
create procedure 存储过程名称([参数列表])
begin
	-- sql语句
end;
-- 在命令行中，记得用delimiter设置结束符号,表示sql语句结束
-- 例如：delimiter $$
create procedure 存储过程名称([参数列表])
begin
	-- sql语句
end&&
-- 调用存储过程
call 名称([参数]);
-- 查看指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema='xxx';
-- 查询某个存储过程的定义
show create procedure 存储过程名称;
-- 删除
drop procedure [if exists] 存储过程名称;

存储过程变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。

分为全局变量（global）、会话变量（session）、用户自定义变量、局部变量

@@变量名->系统变量

@变量名->用户自定义变量

局部变量需要用declare声明，课用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin…end块

默认是session级别的变量

-- 查看所有系统变量
show [session | global] variables
-- 可以通过like模糊匹配方式查找变量
show [session | global] variables like '...';
-- 查看指定变量的值
select @@[session | global] 系统变量名;

-- 设置系统变量
set [session | global] 系统变量名 = 值;
set @@[session | global] 系统变量名 = 值;

-- 例子，全局变量在服务器重启后会失效
-- 需要永久生效，需要修改系统配置文件，在/etc/my.cnf中配置
set global autocommit = 1;-- 设置全局自动提交

-- 用户自定义变量赋值
-- 推荐使用:=   为了和=做区分
set @var_name = expr [,@var_name = expr]...;
set @var_name := expr [,@var_name := expr]...;
select @var_name := expr [,@var_name := expr]...;
select 字段名 into @var_name from 表名;
-- 使用用户自定义变量
select @var_name;

-- 声明局部变量
declare 变量名 变量类型 [default ...];
-- 局部变量赋值
set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ...;
-- 例子
create procedure p1()
begin
	-- 声明一个局部变量
	declare stu_count int default 0;
	set stu_count := 100;
	-- select count(*) into stu_count from student;
end;
call p2();

存储过程if条件判断

-- if语法
if 条件 then 
	...
elseif 条件 then 
	...
else 
	...
end if;

-- 根据定义的分数score变量，判定当前分数对应的分数等级
-- score >= 85分，等级为优秀
-- score >= 60分,且score < 85分，等级为及格
-- score < 60分，等级为不及格
CREATE PROCEDURE p2 () BEGIN
	DECLARE
		score INT DEFAULT 58;
	DECLARE
		result VARCHAR ( 10 );
	IF
		score >= 85 THEN
			
			SET result := '优秀';
		
		ELSEIF score >= 60 THEN
		
		SET result := '及格';
		ELSE 
			SET result := '不及格';
		
	END IF;
	SELECT
		result;
	
END;
CALL p2 ();

存储过程的参数

类型	含义	备注
in	该类参数作为输入，也就是需要调用时传入值	默认
out	该类参数作为输出，也就是该参数可以作为返回值
inout	既可以作为输入参数，也可以作为输出参数

-- 语法
create procedure 存储过程名称 ([in/out/inout 参数名 参数类型])
begin
	-- sql语句
end;

-- 根据传入参数score,判定当前分数对应的分数等级，并返回
-- 1.score >= 85分，等级为优秀
-- 2.score >=60分且score < 85分，等级为及格
-- 3.score < 60分，等级为不及格
CREATE PROCEDURE pp (
	IN score INT,
	OUT result VARCHAR ( 10 )) BEGIN
	IF
		score >= 85 THEN
			
			SET result := '优秀';
		
		ELSEIF score >= 60 THEN
		
		SET result := '及格';
		ELSE 
			SET result := '不及格';
		
	END IF;
	
END;
CALL jun.pp ( 85, @result );
SELECT
	@result;
SHOW PROCEDURE STATUS;
-- 将传入的200分制的分数，进行换算，换算成百分制，然后返回
CREATE PROCEDURE p1(inout score double)
begin
	set score := score * 0.5;
END;
set @score := 200;
CALL jun.pp (@score);
SELECT @score;

存储过程的case

-- case语法-1
case case_value
	when when_value then statement_list
	[when when_value then statement_list]...
	[else satement_list]
end case;
-- case语法-2
case 
	when search_condition then statement_list
	[when search_condition then statement_list]...
	[else statement_list]
end case;

-- 根据传入的月份，判定月份所属的季节(要求采用case结构)
-- 1. 1-3月份，为第一季度
-- 2.4-6月份，为第二季度
-- 3.7-9月份，为第三季度
-- 4.10-12月份，为第四季度
create procedure ppp(in month int)
begin
	declare result varchar(10);
	case 
		when month >= 1 and month <= 3 then set result := '第一季度';
		when month >= 4 and month <= 6 then set result := '第二季度';
		when month >= 7 and month <= 9 then set result := '第三季度';
		when month >= 10 and month <= 12 then set result := '第四季度';
		else set result := '非法参数';
    end case;
    select concat('您输入的月份为：',month,'所属的季度为：',result);
end;
call ppp(7);

存储过程while

-- while语法
while 条件 do
	sql逻辑
end while;

-- 计算从1累加到n的值，n作为传入的数值
create procedure a(in n int)
begin
	declare total int default 0;
	
	while n>0 do
		set total := total + n;
		set n := n - 1;
  end while;
	select total;
end;
call a(3);

show procedure status;
-- 删除名为ppp 的 procedure
drop procedure ppp;

存储过程repeat

-- repeat语法
repeat
	sql逻辑
	until 条件
end repeat;

存储过程loop

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用:

LEAVE:配合循环使用，退出循环。
ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

-- loop语法
[begin_label:]LOOP
	sql逻辑...
end loop [end_label];

-- 退出指定标记的循环体
leave label;
-- 直接进入下一次循环
iterate label;

-- 计算从1累加到n的值，n为传入的参数值
create procedure ll(in n int)
begin
	declare total int default 0;
	sum:loop
		if n <= 0 then leave
		end if;
		set total := total + n;
		set n := n - 1;
	end loop sum;
	select total;
end;
call ll(10)

-- 计算从1到n之间的偶数累加的值，n为传入的参数值
CREATE PROCEDURE ll ( IN n INT ) BEGIN
	DECLARE total INT DEFAULT 0;
	sum :LOOP
		IF n <= 0 THEN
			LEAVE sum;
		END IF;
		SET total := total + n;
		SET n := n - 1;
	END LOOP sum;
	SELECT total;
END;
CALL ll (100);

存储过程游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。

游标的使用包括游标的声明、open、fetch和close

-- 声明游标
declare 游标名称 cursor for 查询语句;
-- 打开游标
open 游标名称;
-- 获取游标记录
fetch 游标名称 into 变量[,变量];
-- 关闭游标
close 游标名称;

-- 根据传入的参数uage,来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业(profession)
-- 并将用户的姓名和专业插入到所创建的一张新表中（id,name,profession）

-- 1. 声明游标，存储查询结果集
-- 2. 准备：创建表结构
-- 3. 开启游标
-- 4. 获取游标中的记录
-- 5. 插入数据到新表中
-- 6. 关闭游标

create procedure cc(in uage int)
begin
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name,profession from tb_user where age <= uage;
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
	open u_cursor;
	-- 遍历游标结果
	while ture do
		fetch u_cursor into uname,upro;
		insert into tb_user_pro
		values(null,uname,upro);
	end while;
end;
call cc(18);

存储过程条件处理程序

条件处理程序（handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

具体状态码，可以参考mysql官方文档。

-- 条件处理程序的语法
declare handler_action handler for condition_value [,condition_value]... statement;
handler_action
	continue:继续执行当前程序
	exit:终止执行当前程序
condition_value
	sqlstate sqlstate_value:状态码，如02000
	sqlwarning:所有以01开头的sqlstate代码的简写
	not found:所有以02开头的sqlstate代码的简写
	sqlexception:所有没有被sqlwarning或not found捕获的sqlstate代码的简写
	
-- 在上一个例子加入，可以在游标报错时不执行
declare exit handler for sqlstate '02000' close u_cursor;

存储函数

存储函数时有返回值的存储过程，存储函数的参数只能是in类型的。

create function 存储函数名称([参数列表])
returns type [characteristic...]
begin
	sql语句
	return ...;
end;

-- characteristic说明：
-- determinstic:相同的输入参数总是产生相同的结果
-- no sql:不包含sql语句
-- reads sql data:包含读取数据的语句，但不包含写入数据的语句

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

触发器只支持行级触发，不支持语句级触发。

触发器类型	NEW和OLD
insert型触发器	new表示将要或者已经新增得数据
update型触发器	old表示修改之前的数据，new表示将要或已经修改后的数据
delete型触发器	old表示将要或已经删除的数据

-- 创建触发器
create trigger trigger_name
before/after insert/update/delete
on tb_name for each row --行级触发器
begin
	trigger_stmt;
end;
-- 查看
show triggers;
-- 删除,如果没有指定schema_name，默认为当前数据库
drop trigger [schema_name]trigger_name;

-- 例子
create trigger tb_user_update_triggerafter
update on tbuser for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES(null, 'update', now(], new.id,
concat('更新之前的数据: id=',old.id,',name=',old.name,'，phone='，old.phone,"，emait='，old.email,profession=' oldprofession更新之后的数据: id=',new.id,",name=',new.name，"，phone="，NEW.phone，, email='，NEW.email,', profession='， NEW.profession));
end;

锁

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、1/0)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

over子句

在SQL中，OVER子句用于指定窗口函数的分区（partitioning）和排序（ordering）规则。它允许我们对查询结果集中的行进行分组并应用窗口函数，而不需要改变整个查询结果的分组方式。

OVER子句通常与窗口函数一起使用，它的一般语法如下：

SELECT 
    column1, 
    column2, 
    window_function(column3) OVER (PARTITION BY column4 ORDER BY column5)
FROM 
    table_name;

在这个语法中，OVER子句包含了两部分：PARTITION BY和ORDER BY。它们的作用如下：

• PARTITION BY：指定窗口函数的分区规则，即按照哪些列进行分组。窗口函数将在每个分组内进行计算。
• ORDER BY：指定窗口函数的排序规则，即对每个分组内的行进行排序。这是可选的，如果不指定ORDER BY，窗口函数将会在整个分区内进行计算。

下面是一个示例，演示了如何使用OVER子句与窗口函数：

SELECT 
    department_id, 
    salary,
    AVG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS avg_salary
FROM 
    employees;

在这个示例中，我们使用了OVER子句来计算每个部门内员工工资的平均值。我们指定了PARTITION BY department_id，这意味着窗口函数将按照部门进行分组。同时，我们也指定了ORDER BY salary，这意味着在每个部门内，工资将按照升序排序。

通过使用OVER子句，我们可以对查询结果集中的行进行分组和排序，并应用窗口函数进行灵活的计算，而不需要对整个结果集进行聚合操作。

更新中。。。