Mysql

基本表

表操作

1. 创建表

   create table <表名>();

2. 修改表

   -- 修改字段类型
   alter table <表名> modify <字段名> <类型>;
   
   
   -- 修改字段名和字段类型
   alter table <表名> change <旧字段名> <新字段名> <类型>;
   
   
   -- 删除字段
   alter table <表名> drop 字段;
   
   -- 修改表名
   rename table <旧表名> to <新表名>; 

3. 删除表

   -- 删除表
   drop table [if exists] <表名>;
   
   -- 删除并重新创建表
   truncate table <表名>;

---

增删改查

1. 插入记录

   insert into <表名>(<字段列表>) values (<值列表>);

2. 删除记录

   delete from <表名> where <条件>;

3. 更新记录

   update <表名> set <字段名1> = <值1>,<字段名2> = <值2> where <条件>;

1. 查找数据

   select <字段列表> from <表名> where <条件>;

---

条件查询

条件运算符	含义
>/>=/</<=/=	懂的都懂（）
<>/!=	不等于
between...and...	在…和…中
in(<集合>)	在集合中多选一满足
like	模糊匹配
is null	是空
is not null	非空
and/&&	并且
or/双竖杠	或者
not/!	非

between … and …包括端点
like模糊匹配中_表示一个字符，%表示多个字符

---

聚合函数

聚合函数	含义
count()	非空计数
max()	最大值
min()	最小值
avg()	平均值
sum()	求和

---

分组/排序/分页

-- 分组后使用having过滤
group by <字段列表> having <过滤条件>

-- asc默认升序，desc降序
order by <字段1 排序1> , <字段2 排序2>

/*
* 起始索引 = (第n页 - 1)*查询记录数
* 索引为0时可以省略
*/
limit <起始索引>,<查询记录数>

---

执行顺序

语句部分	顺序
select	4
from	1
where	2
group by	3
having	3
order by	5
limit	6

---

用户与权限

-- 查用户
use mysql;
select * from user;

-- 创建用户
create user `用户名`@`主机名` identified by `密码`;

-- 修改密码
alter user '用户名'@'主机名' identified with caching_sha2_password by '新密码';


-- 删除用户
drop user `用户名`@`主机名`;

-- 授予权限
grant <权限列表>/<all> on <数据库名>.<表名> to `用户名`@`主机名`;

-- 撤销权限
revoke <权限列表>/<all> on <数据库名>.<表名> from `用户名`@`主机名`;

-- 查找权限
show grants for `用户名`@`主机名`;

---

常用内置函数

字符串函数

1. concat(s1,s2…sn)
   • 字符串拼接
2. lower(str)
   • str字符全变小写
3. upper(str)
   • str字符全变大写
4. lpad(str,n,pad)
   • 左填充，用pad左填充str,达到n个字符串长度，若str长度大于n，则削减str右边，直到二者长度一样
5. rpad(str,n,pad)
   • 右填充，用pad右填充str,达到n个字符串长度，若str长度大于n，则削减str左边，直到二者长度一样
6. trim(str)
   • 消除str头尾空格
7. substring(str,start,len)
   • 截取str，从start开始的len长度

---

数值函数

1. ceil()
   • 向上取整
2. floor()
   • 向下取整
3. mod(x,y)
   • 返回x/y的模
4. rand()
   • 返回0到1的随机数
5. round(x,y)
   • 返回x的四舍五入，保留y位小数

---

日期函数

1. curdate()
   • 返回当前日期
2. curtime()
   • 返回当前时间
3. now()
   • 返回当前日期和时间
4. year(date)
   • 返回date的年
5. month(date)
   • 返回date的月
6. day(date)
   • 返回date的日
7. date_add(date,interval expr type)
   • 返回一个日期/时间加上一个时间间隔expr后的时间值
8. datediff(d1,d2)
   • 返回起始时间d1和结束时间d2之间的天数

---

流程函数

1. if(val,t,f)
   • 若val为true，返回t，否则返回f
2. ifnull(v1,v2)
   • 若v1不为空则返回v1，否则返回v2
3. case when val1 then res1, … else default end
   • 若val1为true，则返回res1，…否则返回默认值
4. case expr when val1 then res1,…else default end
   • 若expr=val1,则返回res1，…否则返回默认值

---

约束

约束命名	语句
非空约束	not null
主键约束	primary key
唯一约束	unique
默认约束	未指定值时置默认值
检查约束	check
外键约束	foreign key

外键约束删除、更新行为
no action restrict 拒绝操作
cascade 级联
set null 置空（需可为空）
set default 值默认值

---

多表查询

-- 隐式内连接
select <字段表> from <表1>,<表2> where <条件>;

-- 显式内连接
select <字段名> from <表1> [inner] join <表2> on 条件;

-- 左外连接,左表所有数据保留，连接右表数据子集
select <字段名> from <表1> left join <表2> on 条件;

-- 右外连接，右表所有数据保留，连接左表数据子集
select <字段名> from <表1> right join <表2> on 条件;

-- 自连接，表必须起别名，自连接可以是内连接也可以是外连接
select <字段名> from <表名> a left join <表名> b on 条件;

---

联合查询

/*
* 是两条语句结构的并集，union all时不会去重
* 联合查询的多张表列数和字段类型要一致
*/
<一条SQL语句>
union [all]
<另一条SQL语句>

---

子查询

标量子查询

子查询结果为单个值
常用操作符：=，<>，>，>=，<，<=

---

列子查询

子查询结果为一列
常用操作符：

符号	意义
in	指定集合内多选一
not in	不在指定集合内
any	满足任意一个
some	与any一样
all	满足集合内的全部

---

行子查询

子查询结果为一行
常用操作符：= <> in not in
多字段可以用(字段1,字段2,…) = (子查询)表示

---

表子查询

子查询结果为多行多列
常用操作符为in

---

存储过程/函数/触发器

存储过程模板

DELIMITER //

CREATE PROCEDURE sp_模块名_动作 (
    IN  p_参数1 INT,         -- 输入参数
    OUT p_参数2 VARCHAR(50)  -- 输出参数
)
BEGIN
    -- 1. 局部变量声明
    DECLARE v_变量1 INT DEFAULT 0;
    DECLARE v_sql TEXT;

    -- 2. 初始化逻辑
    SET v_变量1 = p_参数1;
    SET p_参数2 = '初始值';

    -- 3. 主体逻辑示例
    -- (1) 简单查询赋值
    SELECT COUNT(*) INTO v_变量1 FROM your_table WHERE some_column = p_参数1;

    -- (2) 更新/插入操作
    UPDATE your_table SET col1 = NOW() WHERE id = p_参数1;

    -- (3) 动态 SQL 示例
    SET v_sql = CONCAT('SELECT COUNT(DISTINCT SUBSTRING(a,1,5))/COUNT(*) ',
                       'FROM your_table WHERE id = ', p_参数1);

    PREPARE stmt FROM v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 4. 设置输出参数
    SET p_参数2 = CONCAT('处理完成, 记录数=', v_变量1);

    -- 5. 异常处理（可选）
    -- DECLARE EXIT HANDLER FOR SQLEXCEPTION
    -- BEGIN
    --     ROLLBACK;
    --     SET p_参数2 = '执行出错';
    -- END;

END //

DELIMITER ;

函数模板

DELIMITER //

CREATE FUNCTION fn_模块名_功能 (
    p_参数1 数据类型
)
RETURNS 数据类型
DETERMINISTIC
BEGIN
    DECLARE v_result 数据类型;

    -- 计算逻辑
    SET v_result = ...;

    RETURN v_result;
END //

DELIMITER ;

触发器模板

DELIMITER //

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
  -- 触发器逻辑
END;
//

DELIMITER ;

关键字	用于什么操作	用途
NEW	INSERT/UPDATE	表示新插入/修改后的值
OLD	UPDATE/DELETE	表示原来的旧值

事务

事务特性

字母	性质	解释
A	原子性	要么全部成功，要么全部失败
C	一致性	事务完成时所有数据处于一致状态
I	隔离性	事务的进行不受外部影响
D	永久性	事务一旦提交或者回滚，它对数据库的改变是永久的

---

并发事务问题

名词	解释
脏读	一个事务读取另一个事务未提交的数据
不可重复读	一个事务先后读取统一数据，但结果不同
幻读	查询数据时没有这条记录，但是后续插入时又发现该记录已存在

---

事务隔离

事务隔离级别	英文	脏读	不可重复读	幻读
读未提交	read uncommited	√	√	√
读已提交	read commited	×	√	√
可重复读（默认）	repeatable read	×	×	√
串行化	serializable	×	×	×

---

-- 查看事务隔离等级
select @@transaction_isolation;

-- 设置事务隔离等级
set [session/global] transaction isolation level <隔离等级>; 

---

索引

语法

-- 普通索引
create index <索引名> on <表名>(<列名>);

--- 唯一索引
create unique index <索引名> on <表名>(<列名>);

-- 联合索引
create index <索引名> on <表名>(<列名1>,<列名2>);

--
--

-- 删除索引
drop index <索引名> on <表名>;

-- 查看索引
show index <索引名> from <表名>;

索引结构

• 用B+树，叶子结点使用双向循环链表连接
• 也有hash，但是只能对等比较，不能范围比较
  • 无法使用索引完成排序
  • 查询效率高，通常比B+树的快

---

聚集索引

叶子结点存放完整的记录，而非聚集索引（二级索引）存放的是主键值。
回表：二级索引查到主键后返回聚集索引查找对应记录

---

索引使用

在from后面放入

-- 建议使用
use index(<索引名>)

-- 建议忽略
ignore index(<索引名>)

-- 强制使用
force index(<索引名>)

---

索引失效

1. 联合索引中最左边一列必须存在，往右一直匹配索引中的列，若条件中缺失了某一列则停下，因为从缺失的那列后面的索引全部失效。
2. 在索引上进行运算
3. 字符串类型不加引号
4. 模糊匹配中
   • 尾部模糊匹配，索引不会失效。q%
   • 头部模糊匹配，会失效。%q
5. or连续条件中，有一个无索引，则整个条件索引失效
6. 索引比全表查找慢，优化器会根据成本估算来选择是否使用索引

---

优化

查询使用索引返回的列尽量包括在索引中，减少回表次数

SQL信息

-- SQL执行频率
show [global/session] status like 'Com_______';

-- profile

-- 是否支持profile
select @@have-profile;

-- 开启profile
set profiling =1;

-- 查看每条SQL耗时基本情况
show profiles;

--- 查看指定query_id的SQL各阶段耗时
show profile for query_id;

-- 查看指定query_id的SQL语句CPU使用情况
show profile cpu for query_id;

-- 执行计划，获得MySQL如何执行语句的信息
[explain/desc] +<sql语句>

大字符串优化

/*
* n为字符串前n个字符
* 前缀长度根据选择性：
* 选择性越高越好，最高为1
* 选择性：不重复记录值/记录总数
* 
*/
create index <索引名> on table name(column(n));

-- 根据该语句查看选择性
select count(distinct substring(a,1,5))/count(*) from <表名>;

---

insert优化

1. 多个insert合一，批量插入
2. 使用手动提交事务
3. 按主键顺序插入
4. 文件插入
   • 连接服务器时加上—local-infile
   • 打开本地文件加载开关
   • 文件插入

     mysql --local-infile -u root -p 1111

set global local_infile =1;

-- 数据按照，分列，按照\n分行
load data local infile '/root/sql.log' into table <表名> fields terminated by ',' lines terminated by '\n';

---

主键优化

1. 降低主键长度
2. 选择主键顺序插入

---

order by优化

使用索引进行排序
一个字段不是同升序或者同降序时，需要单独建立这个特殊排序的联合索引

---

group by优化

1. 避免使用临时表和文件排序
2. 为分组字段建立索引，索引的列需要尽量覆盖where和group by 字段

---

limit优化

• 使用子查询
  • 先找出需要的数据的主键。再使用主键查对应的记录

---

update优化

根据索引字段进行更新且该索引不能失效，防止行锁变成表锁

---

count优化

1. 尽量使用count(*),count(*)在 InnoDB 做了优化，所以会比count(字段)快，另外，查带有非空约束的键也快，因为服务器不用判断数据是不是空
2. count(1) 服务器不判断不取值，直接累加

---

视图

-- 创建视图
create or replace view <视图名> as <select语句> [with [cascaded/local] check option];

-- 查看视图创建语句
show create view <视图名>;

-- 查看视图数据
select * from <视图名>;

-- 修改视图数据
create or replace view <视图名> as <select语句> [with [cascaded/local] check option];

alter view <视图名> as <select语句> [with [cascaded/local] check option];

-- 删除视图
drop view [if exists] <视图名>;

• 嵌套视图检查

  -- 默认的级联检查，会检查本身以及祖先视图的条件是否符合
  with [cascaded] check option
  
  -- 只检查自身以及祖先视图带有check option 的视图
  with local check option

---

视图更新

1. 与基本表是一对一关系可以更新
2. 有聚合函数，distinct，having ,union则不可更新

---

锁

并发访问同一资源的机制

---

全局锁

锁定所有库，如全局逻辑备份

-- 加锁
flush tables with read lock;

-- 解锁
unlock tables;

备份：mysqldump -u root -p 1111 a>a/sql

问题

1. 全局锁期间无法进行业务
2. 若在从库加锁，则其期间无法执行主库的日志，造成主从延迟

可以进行不加锁的一致性备份

mysqldump --single-transaction -u root -p 1111 a>a.sql

---

表级锁

-- 表锁
lock tables <表名>,... read/write;

-- 解锁
unlock tables/客户端下线

---

元数据锁

1. 共享锁：可以加任意数量共享锁，但是不能加排它锁
   • 只可读不可写
2. 排它锁：不可加任何排它锁和共享锁
   • 仅仅加锁者可以读写，其他用户不可读写

select … for update 表明是先看，随后需要进行更新

---

意向锁

某个事务即将或已经对该表中的某行施加某种行锁（如排他行锁），告诉其他事务该表某行已经有锁，不可以加表级的排它锁。
但是意向锁兼容其他行锁

---

行级锁

对索引进行加锁，但不会对记录进行加锁

1. 行锁：锁定单条记录，防止该记录被更新和删除
2. 间隙锁：防止其他事务在某个区间insert，产生幻读
3. 临键锁：行锁和间隙锁的组合

---