MySQL

alter user 'root'@'localhost' identified with mysql_native_password BY 'pass';
flush privileges;
sudo systemctl restart mysql;
 
# -p 后面没有空格
mysql -u <username> -p<password> [-D <databaseName>];

-- 查询所有数据库
show databases;
 
-- 创建数据库
create database [if not exists] <databaseName> [default charset <charsetName>] [collate <collateName>];
-- e.g.
create database if not exists db0 default charset utf8mb4 collate utf8mb4_general_ci;
 
-- 使用数据库
use <databaseName>;
 
-- 查询当前数据库
select database();
 
-- 删除数据库
drop database [if exists] <databaseName>;
drop database [if exists] <databaseName1>, <databaseName2>, ...;
 
-- 查询当前数据库的所有表
show tables;
show tables from <databaseName>;
 
-- 创建表
create table [if not exists] <tableName> (
  <primaryKey>  int unsigned auto_increment primary key,  -- 无符号整型自增主键
  <columnName2> varchar(160) not null unique,             -- 非空唯一变长字符串
  <columnName3> boolean default true,                     -- 默认 true
  <columnName4> int check (columnName4 between 0 and 100) -- 检查约束
)
  collate utf8mb4_general_ci -- 使用 utf8mb4_general_ci 排序规则
  default charset = utf8mb4  -- 使用 utf8mb4 字符集
  engine = InnoDB;           -- 使用 InnoDB 存储引擎
 
-- 描述表结构
desc <tableName>;
-- 等价于
describe <tableName>;
explain <tableName>;
show columns from <tableName>;
show fields from <tableName>;
 
-- 查询创建表的 sql
show create table <tableName>;
-- 格式化输出
show create table <tableName> \G;
 
-- 删除表
drop table [if exists] <tableName>;
drop table [if exists] <tableName>, <tableName2>, ...;
 
-- 清空表
truncate table <tableName>;
 
-- 增加字段
alter table <tableName> add <newColumnName> <dataType>;
 
-- 在指定字段的后面增加字段
alter table <tableName> add <newColumnName> <dataType> after <columnName>;
 
-- 删除字段
alter table <tableName> drop <columnName>;
 
-- 修改字段的数据类型 (modify)
alter table <tableName> modify <columnName> <dataType>;
 
-- 修改字段的字段名和数据类型 (change)
alter table <tableName> change <oldColumnName> <newColumnName> <dataType>;
 
-- 修改表名
alter table <oldTableName> rename <newTableName>;

-- 插入
insert into <tableName> (<columnName1>, <columnName2>, ...)
values (<row1value1>, <row2value2>, ...), (<row2value1>, <row2value2>, ...), ...;
-- All columns
insert into <tableName>
values (<row1value1>, <row2value2>, ...), (<row2value1>, <row2value2>, ...), ...;
 
-- 更新
update <tableName> set <columnName1> = <value1>, <columnName2> = <value2>, ... [where <conditionExpr>];
 
-- 删除
delete from <tableName> [where <conditionExpr>];

select [distinct] <columnName1> [as <alias1>], <columnName2> [as <alias2>], ... -- distinct 去重
 
from <tableName>
 
where <conditionExpr>                                            -- where 分组前过滤
 
group by <columnName1>, <columnName2>, ...                       -- group by 分组字段列表
 
having <conditionExpr>                                           -- having 分组后过滤
 
order by <columnName1> [asc]|desc, <columnName2> [asc]|desc, ... -- order by 排序查询
 
limit <startIndex>, <pageSize>;                                  -- limit 分页查询
limit <pageSize> offset <startIndex>;                            -- limit 分页查询

-- 查询用户
use mysql;
select * from user;
 
-- 创建用户
create user '<username>'@'<hostname>' identified by '<password>';
 
-- 修改用户密码
alter user '<username>'@'<hostname>' identified with mysql_native_password by '<newPassword>';
 
-- 删除用户
drop user '<username>'@'<hostname>';
 
-- 查询权限
show grants for '<username>'@'<hostname>';
 
-- 授予权限
grant <privilegeName1>, <privilegeName2>, ... on <databaseName>.<tableName> to '<username>'@'hostname';
 
-- 撤销权限
revoke <privilegeName1>, <privilegeName2>, ... on <databaseName>.<tableName> from '<username>'@'<hostname>';

create table <tableName> (
  <primaryKey>  int unsigned auto_increment primary key,    -- 主键约束: 无符号整型自增主键
  <columnName2> varchar(16) not null unique,                -- 非空约束, 唯一约束: 非空唯一变长字符串
  <columnName3> boolean default true,                       -- 默认约束: 默认 true
  <columnName4> int check (<columnName4> between 0 and 100) -- 检查约束
);

-- 创建子表时, 添加从子表某列指向父表某列的外键
create table <tableName> (
  [constraint] [<foreignKeyName>] foreign key (<columnName>) references <foreignTableName> (<foreignColumnName>);
)
 
-- 修改子表时, 添加从子表某列指向父表某列的外键
alter table <tableName> add constraint <foreignKeyName> foreign key (<columnName>) references <foreignTableName> <foreignColumnName>;
-- e.g. 添加从 t_emp 员工表 (子表) dep_id 字段指向 t_dep 部门表 (父表) id 字段的外键
alter table t_emp add constraint fk_emp_dep_id foreign key (dep_id) references t_dep id;
 
-- 删除外键
alter table <tableName> drop foreign key <foreignKeyName>;

-------------
-- 隐式内连接
-------------
select <columnName1>, <columnName2>, ... from <tableName1>, <tableName2> where <conditionExpr>;
-- e.g. 查询每个员工的姓名, 和关联的部门名
select t_emp.name, t_dep.name from t_emp, t_dep where t_emp.dep_id = t_dep.id;
 
-------------
-- 显式内连接
-------------
select <columnName1>, <columnName2>, ... from <tableName1> [inner] join <tableName2> on <conditionExpr>;
select e.name, d.name from t_emp as e inner join t_dep as d on e.dep_id = d.id; -- as 可省略
 
-------------
-- 左外连接
-------------
select <columnName1>, <columnName2>, ... from <tableName1> left [outer] join <tableName2> on <conditionExpr>;
-- e.g. 查询 t_emp 表的所有数据, 和关联的部门数据 (即使某些员工没有部门, 查询结果中也会保留这些员工)
select e.*, d.* from t_emp as e left outer join t_dep as d on e.dep_id = d.id;
-- 等价于
select e.*, d.* from t_dep as d right outer join t_emp as e on e.dep_id = d.id;
 
-------------
-- 右外连接
-------------
select <columnName1>, <columnName2>, ... from <tableName1> right [outer] join <tableName2> on <conditionExpr>;
-- e.g. 查询 t_dep 表的所有数据, 和关联的员工数据 (即使某些部门没有员工, 查询结果中也会保留这些部门)
select d.*, e.* from t_emp as e right outer join t_dep as d on e.dep_id = d.id;
-- 等价于
select d.*, e.* from t_dep as d left outer join t_emp as e on d.id = e.dep_id;
 
-------------
-- 自连接
-------------
select <columnName1>, <columnName2>, ... from <tableName> <alias1> join <tableName> <alias2> on <conditionExpr>;
-- e.g. 自连接 + 内连接: 在 t_emp 表中, 查询每个员工的姓名, 和关联的领导的姓名
select e.name, l.name from t_emp as e, t_emp as l where e.leader_id = l.id
 
-- e.g. 在 t_emp 表中, 查询每个员工的姓名, 和关联的领导的姓名 (即使某些员工没有领导, 查询结果中也会保留这些员工)
 
-- e.g. 自连接 + 左外连接
select e.name as 'employeeName', l.name as 'leaderName' from t_emp as e left outer join t_emp as l where e.leader_id = l.id;
-- e.g. 自连接 + 右外连接
select e.name 'employeeName', l.name 'leaderName' from t_emp l right join t_emp e where e.leader_id = l.id;

select <columnName1>, <columnName2>, ... from <tableName1> ...
union [all]
select <columnName1>, <columnName2>, ... from <tableName2> ...;
 
-- e.g.
select * from t_emp where salary < 5000
union [all]
select * from t_emp where age > 50;
 
-- 使用 union 时, 和 or 条件查询等价, 会去重
-- 使用 union all 时, 和 or 条件查询不等价, 不会去重
select * from t_emp where salary < 5000 or age > 50;
-- 当某个员工月薪 < 5000, 年龄也 > 50 时, 则该员工在联合查询的结果集中会出现两次

-- 标量子查询: 子查询的结果为 1 个值
select * from t_emp
where dep_id = (
  select id from t_dep where dep_name = "Web Infra"
)
 
-- 列子查询: 子查询的结果为 1 列
select * from t_emp
where salary > [all | some] (
  select salary from t_emp where dep_id = (
    select id from t_dep where dep_name = "Web Infra"
  )
)
 
-- 行子查询: 子查询的结果为 1 行
select * from t_emp
where (salary, leader_id) = (
  select salary, leader_id from t_emp where name = "lark"
)
 
-- 表子查询: 子查询的结果为多行多列 (一张表)
select * from t_emp
where (job, salary) in (
  select job, salary from t_emp where name = "lark" or name = "lark2"
)
 
select e.*, d.* from (
  select * from t_emp where birthday >= "2002-02-28" as e left outer join t_dep as d on e.dep_id = d.id
)

-- 查询事务提交方式
select @@autocommit;
-- 设置自动提交
set @@autocommit = 1;
-- 设置手动提交
set @@autocommit = 0;
 
-- 开启事务
start transaction;
-- 等价于
begin;
 
-- 提交事务
commit;
 
-- 回滚事务
rollback;

-- 查询事务隔离级别
select @@transaction_isolation;
 
-- 设置事务隔离级别
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};

-- id 是主键, 也是聚集索引
-- 聚集索引树的叶子节点存储 [id, name, age]
create table users (
  id int primary key,
  name varchar(50),
  age int
)
 
-- 为 name 字段建立索引 (二级索引)
-- 二级索引树的叶子节点存储 [name, id]
create index idx_name on users (name);
insert into users values (1, 'Alice', 22), (2, 'Bob', 23);
 
-- 先在二级索引树中找到 name = 'Bob' 的叶子节点, 叶子节点存储 [name: 'Bob', id: 2]
-- 再在聚集索引树中找到 id = 2 的叶子节点, 叶子节点存储 [id: 2, name: 'Bob', age: 23]
select * from users where name = 'Bob'

-- 建立索引
create [unique | fulltext] index <indexName> on <tableName> (<column>, <column2>, ...);
 
-- 查询索引
show index from <tableName> [\G];
 
-- 删除索引
drop index <indexName>, <indexName2>, ... on <tableName>;

-- 查询 CRUD 的频率
-- Com_insert, Com_delete, Com_update, Com_select
show [session | global] status like 'Com_______';
 
-- 是否开启慢查询日志, 默认关闭
-- show variables like 'slow_query_log';
 
-- 是否开启 profiling, 默认开启
select @@have_profiling;
 
-- 在 session/global 级别开启 profiling
set [session | global] profiling = 1;
 
-- 查询每条 sql 的 queryID, 耗时, 查询语句
show profiles;
 
-- 查询指定 queryID 的 sql 各个阶段的耗时
show profile for query <queryID>;
 
-- 查询指定 queryID 的 sql 各个阶段的耗时和 cpu 占用
show profile cpu for query <queryID>;
 
-- 查看 select 语句的执行计划
[explain | desc] select * from <tableName>;
 
-- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
-- | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
-- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
-- type: ALL 表示未使用索引
-- key: NULL 表示未使用索引

# /etc/my.cnf 开启慢查询日志
show_query_log=1
# sql 查询时间超过 2s 时, 记录慢查询日志
long_query_time=2
 
# 慢日志
/var/lib/mysql/localhost-slow.log

-- idx_name_age
-- 二级索引的叶子节点存储 [name, age, id]
-- 聚集索引的叶子节点存储行数据
show index from users;
 
-- using where; using index: 查询时使用了索引, 并且无需回表查询
explain select name, age from users where name = 'lark' and age = 22;
-- using index condition: 查询时使用了索引, 但是需要回表查询
explain select * from users where name = 'lark' and age = 22;

create index idx_name on users (name);
select * from users where id = 2; -- 1 次查询
select id, name from users where name = 'Alice'; -- 1 次查询
select id, name, age from users where name = 'Alice'; -- 2 次查询, 需要回表查询 age

-- idx_name_age_gender
-- 二级索引的叶子节点存储 [name, age, gender, id]
-- 聚集索引的叶子节点存储行数据
show index from users;
 
-- 联合索引有效
select * from users where name = 'lark' and age = 22 and gender = 1;
select * from users where name = 'lark' and age = 22;
select * from users where name = 'lark';
 
-- 未包含联合索引的最左列, 联合索引全部失效
select * from users where age = 22 and gender = 1;
select * from users where gender = 1;
 
-- 跳过联合索引中的 age 列, 联合索引的 gender 列部分失效 (多一次回表查询)
select * from users where name = 'lark' and gender = 1;

-- name 字段是二级索引
 
-- 对索引列使用「左」或「左右」模糊匹配
select * from users where name like 'htc%'; -- 索引有效
select * from users where name like '%ccc'; -- 索引失效
select * from users where name like '%tc%'; -- 索引失效
 
-- 对索引列使用函数
select * from users where length(name) = 5; -- 索引失效
-- 解决方法: 创建 length(name) 虚拟列并建立索引
alter table users add key idx_name_length ((length(name)));
 
-- 对索引列进行表达式计算
select * from users where id = 7 - 1; -- 索引有效
select * from users where id + 1 = 7; -- 索引失效
 
-- 对索引列进行隐式类转换 (phone: varchar)
-- mysql 比较字符串和数字时, 会自动将字符串转换为数字进行比较
select "10" > 9; -- 1
 
select * from users where phone = 15395377789; -- 索引失效
-- 原理: 等价于
select * from users where cast(phone as signed int) = 15395377789; -- 索引失效
 
select * from users where id = "1" -- 索引有效
-- 原理: 等价于
select * from users where id = cast("1" as signed int) -- 索引有效

select * from users where id = 1 or age = 7; -- 索引失效
-- 解决方法: 为 age 列建立索引
create index idx_age on users (age);

-- mysql 使用索引
explain select * from <tableName> use index(<indexName>) <conditionExpr>;
-- mysql 忽略索引
explain select * from <tableName> ignore index(<indexName>) <conditionExpr>;
-- 强制 mysql 使用索引
explain select * from <tableName> force index(<indexName>) <conditionExpr>;

create index <indexName> on <tableName> (<columnName>(n));
 
-- 计算前缀的选择性 (越接近 1 越好)
select count(distinct substring(name, 1, 5)) / count(*) from users;
create index idx_name_5 on users (name(5));

-- 对当前数据库 (的所有表) 加全局锁
flush tables with read lock;
-- 释放全局锁 (会话结束后, 自动释放全局锁)
unlock tables;

# 数据备份, 需要加全局锁
mysqldump -uroot -ppass db0 > ./db0.sql
# 数据备份, 开启事务, 无需加全局锁
mysqldump --single-transaction -uroot -ppass db0 > ./db0.sql

-- 加表共享读锁
lock tables <tableName>, <tableName2>, ... read;
-- 加表独占写锁
lock tables <tableName>, <tableName2>, ... write;
-- 释放表锁 (会话结束后, 自动释放全局锁)
unlock tables

-- 查看意向锁, 行锁的加锁情况
-- object_schema 数据库名
-- object_name 表名
-- index_name 索引名
-- lock_type 锁的级别: 表级锁 table, 行级锁 record
-- lock_mode 锁的类型
-- * 意向共享锁 table IS
-- * 意向排他锁 table IX
-- * 共享行锁 record S,rec_not_gap
-- * 排他行锁 record X,rec_not_gap
-- * 共享间隙锁 record S,gap
-- * 排他间隙锁 record X,gap
-- * 共享临键锁 record S
-- * 排他临键锁 record X
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

begin; -- transaction A
select name from users where id = 2; -- transaction A; Sakura
begin; -- transaction B
update users set name = 'Momoko' where id = 2; -- transaction B
select name from users where id = 2; -- transaction A; Sakura
commit; -- transaction B
select name from users where id = 2; -- transaction A; Sakura
-- 原理: mysql 的默认事务隔离级别是 Repeatable Read 可重复读
 
-- 当前读
select name from users where id = 2 lock in share mode; -- transaction A; Momoko
select name from users where id = 2 for update; -- transaction A; Momoko