常用语句
SELECT语句
当前登录用户
select user();
当前时间
select now();
数据库版本
select version();
当前数据库
select database();
引擎相关
显示引擎相关信息
show engines;
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.03 sec)
MEMORY存储引擎：他是基于hash的，存储在内存中，对临时表非常有用
查看引擎的日志或者状态信息
show engine 引擎名 logs/status
mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2020-02-01 17:14:35 0x7f8c890a8700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3107911 srv_active, 0 srv_shutdown, 12588197 srv_idle
srv_master_thread log flush and writes: 15695253
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4100614
OS WAIT ARRAY INFO: signal count 4208879
RW-shared spins 0, rounds 6689634, OS waits 3170686
RW-excl spins 0, rounds 8368239, OS waits 175555
RW-sx spins 67685, rounds 1470742, OS waits 16696
Spin rounds per wait: 6689634.00 RW-shared, 8368239.00 RW-excl, 21.73 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-30 10:57:23 0x7f8c5dbb0700
*** (1) TRANSACTION:
TRANSACTION 296247402, ACTIVE 47 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 375641, OS thread handle 140240848148224, query id 1446007790 10.0.10.12 laijiandu_prod updating
表相关
查看表列表
show tables [from 库名] [like 'pattern']
mysql> show tables from laijiandu;
+--------------------------------------------+
| Tables_in_laijiandu |
+--------------------------------------------+
| ljd_Invent_count_and_dau |
| ljd_active_and_register_number |
| ljd_activity |
| ljd_activity_addr_info |
| ljd_activity_daka |
| ljd_activity_reward |
| ljd_activity_sign_up_user |
| ljd_admin_user_info |
| ljd_alipay_user_info |
....
查看表统计信息
show table status [from 库名] [like 'pattern']
也可以通过information_schema.tables这张表中查看这些信息
mysql> show table status from laijiandu_prod like 'ljd_user';
+----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| ljd_user | InnoDB | 10 | Dynamic | 64010 | 697 | 44646400 | 0 | 11599872 | 7340032 | 30134955 | 2019-08-21 19:16:58 | 2019-12-09 21:24:45 | NULL | utf8mb4_general_ci | NULL | | |
+----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.02 sec)
查看表索引信息
show index from [库名.]表名
mysql> show index from laijiandu_prod.ljd_user;
+----------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ljd_user | 0 | PRIMARY | 1 | uid | A | 64010 | NULL | NULL | | BTREE | | |
| ljd_user | 0 | invitation_code | 1 | invitation_code | A | 64010 | NULL | NULL | | BTREE | | |
| ljd_user | 0 | phone | 1 | phone | A | 50850 | NULL | NULL | YES | BTREE | | |
| ljd_user | 1 | last_login_time | 1 | last_login_time | A | 53310 | NULL | NULL | | BTREE | | |
| ljd_user | 1 | inviter_uid | 1 | inviter_uid | A | 2428 | NULL | NULL | YES | BTREE | | |
+----------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.03 sec)
这条命令输出了一个关键值：cardinality，表示该索引中唯一值的数目。这个值和表总行数应该尽量接近1，否则可以考虑是否可以删除这个索引。但这个值不是实时更新的，只是一个大概值。如果要手动更新这个值，可以使用 analyze table table_name来完成。
mysql是通过采样的方法来进行统计这个值的
优化器会根据这个值来判断是否使用这个索引，但这个值可能有时候不准确，因此可能影响sql执行性能，所以可以考虑在系统非高峰时间对核心表执行analyze table操作。
重新统计表的索引信息
analyze table [库名.]表名
mysql> analyze table laijiandu_prod.ljd_user;
+-------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| laijiandu_prod.ljd_user | analyze | status | OK |
+-------------------------+---------+----------+----------+
1 row in set (0.52 sec)
查看表结构
show create table 表名
下面4条语句输出相同，最后一个可以进行列名筛选
desc 表名
describe 表名
explain 表名
show columns from 表名 [like 'pattern']
表重命名、表名交换
rename 原表名 to 新表名
rename 原表名 to 库名.新表名 可将表移动到另一个数据库
rename 还可以交换两个表名
复制表结构
create table 新表名 like 要复制的表名
truncate
清空数据,删除重建表
TRUNCATE [TABLE] tbl_name
- truncate 是删除表再创建，delete 是逐条删除
- truncate 重置auto_increment的值。而delete不会
- truncate 不知道删除了几条，而delete知道。
- 当被用于带分区的表时，truncate 会保留分区
- truncate table_name是不可回滚的，因为会隐式提交sql
修改表
ALTER TABLE 表名 操作名
-- 操作名
ADD[ COLUMN] 字段定义 -- 增加字段
AFTER 字段名 -- 表示增加在该字段名后面
FIRST -- 表示增加在第一个
ADD PRIMARY KEY(字段名) -- 创建主键
ADD UNIQUE [索引名] (字段名)-- 创建唯一索引
ADD INDEX [索引名] (字段名) -- 创建普通索引
DROP[ COLUMN] 字段名 -- 删除字段
MODIFY[ COLUMN] 字段名 字段属性 -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
CHANGE[ COLUMN] 原字段名 新字段名 字段属性 -- 支持对字段名修改
DROP PRIMARY KEY -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
DROP INDEX 索引名 -- 删除索引
DROP FOREIGN KEY 外键 -- 删除外键
锁表
/* 锁表 */
表锁定只用于防止其它客户端进行不正当地读取和写入
MyISAM 支持表锁，InnoDB 支持行锁
-- 锁定
LOCK TABLES tbl_name [AS alias]
-- 解锁
UNLOCK TABLES
其他
-- 删除表
DROP TABLE[ IF EXISTS] 表名 ...
-- 清空表数据
TRUNCATE [TABLE] 表名
-- 复制表结构
CREATE TABLE 表名 LIKE 要复制的表名
-- 复制表结构和数据
CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
-- 检查表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- 优化表(整理表文件碎片)
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- 修复表
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- 分析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
MySQL的数据类型
整形
类型 字节 范围（有符号位）
tinyint 1字节 -128 ~ 127 无符号位：0 ~ 255
smallint 2字节 -32768 ~ 32767
mediumint 3字节 -8388608 ~ 8388607
int 4字节 -2147483647 ~ 2147483647（大概21.5亿）
bigint 8字节
无符号数范围翻倍，用 unsigned 修改为无符号数
int(M) M表示总位数
- 默认存在符号位，unsigned 属性修改
- 显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，zerofill 属性修改
例：int(5) 插入一个数'123'，补填后为'00123'
- 在满足要求的情况下，越小越好。
- 1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。
定点数
decimal -- 可变长度
decimal(M, D) M也表示总位数，D表示小数位数。
保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。
将浮点数转换为字符串来保存，每9位数字保存为4个字节。
字符型
char 定长字符串，速度快，但浪费空间
varchar 变长字符串，速度慢，但节省空间
M表示能存储的最大长度，此长度是字符数，非字节数。
不同的编码，所占用的空间不同。
char,最多255个字符，与采用的编码无关，每个字符固定占用3个字节
varchar,最大65535字节(64kb)，任何编码方式都不能超过这个空间。
一条有效记录最大不能超过65535个字节(64kb)。
varchar 是变长的，需要利用存储空间保存 varchar 的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。
varchar 的最大有效长度由最大行大小和使用的字符集确定。
最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。
例：若一个表定义为 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少？ 答：(65535-1-2-4-30*3)/3
日期时间类型
datetime 8字节 日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59
timestamp 4字节 时间戳 19700101000000 到 2038-01-19 03:14:07
date 3字节 日期 1000-01-01 到 9999-12-31
time 3字节 时间 -838:59:59 到 838:59:59
year 1字节 年份 1901 - 2155
datetime YYYY-MM-DD hh:mm:ss
timestamp YY-MM-DD hh:mm:ss
YYYYMMDDhhmmss
YYMMDDhhmmss
date YYYY-MM-DD
YY-MM-DD
YYYYMMDD
YYMMDD
time hh:mm:ss
hhmmss
hhmmss
year YYYY
YY
SQL语句
SELECT
向优化器建议如何选择索引
use index、ignore index、force index
SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
子查询
/* 子查询 */ ------------------
- 子查询需用括号包裹。
-- from型
from后要求是一个表，必须给子查询结果取个别名。
- 简化每个查询内的条件。
- from型需将结果生成一个临时表格，可用以原表的锁定的释放。
- 子查询返回一个表，表型子查询。
select * from (select * from tb where id>0) as subfrom where id>1;
-- where型
- 子查询返回一个值，标量子查询。
- 不需要给子查询取别名。
- where子查询内的表，不能直接用以更新。
select * from tb where money = (select max(money) from tb);
-- 列子查询
如果子查询结果返回的是一列。
使用 in 或 not in 完成查询
exists 和 not exists 条件
如果子查询返回数据，则返回1或0。常用于判断条件。
select column1 from t1 where exists (select * from t2);
-- 行子查询
查询条件是一个行。
select * from t1 where (id, gender) in (select id, gender from t2);
行构造符：(col1, col2, ...) 或 ROW(col1, col2, ...)
行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。
-- 特殊运算符
!= all() 相当于 not in
= some() 相当于 in。any 是 some 的别名
!= some() 不等同于 not in，不等于其中某一个。
all, some 可以配合其他运算符一起使用。
连接查询(join)
/* 连接查询(join) */ ------------------
将多个表的字段进行连接，可以指定连接条件。
-- 内连接(inner join)
- 默认就是内连接，可省略inner。
- 只有数据存在时才能发送连接。即连接结果不能出现空行。
on 表示连接条件。其条件表达式与where类似。也可以省略条件（表示条件永远为真）
也可用where表示连接条件。
还有 using, 但需字段名相同。 using(字段名)
-- 交叉连接 cross join
即，没有条件的内连接。
select * from tb1 cross join tb2;
-- 外连接(outer join)
- 如果数据不存在，也会出现在连接结果中。
-- 左外连接 left join
如果数据不存在，左表记录会出现，而右表为null填充
-- 右外连接 right join
如果数据不存在，右表记录会出现，而左表为null填充
常用工具
数据备份和恢复
mysqldump
慢查询日志分析工具
1. mysqldumpslow
2. pt-query-digest percona公司的，官网 percona.com
percona工具文档
https://www.percona.com/doc/percona-toolkit/3.0/index.html
常用函数
数值相关
abs(x) -- 绝对值 abs(-10.9) = 10
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 获得圆周率
pow(m, n) -- m^n
sqrt(x) -- 算术平方根
rand() -- 随机数
truncate(x, d) -- 截取d位小数
日期时间相关
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间
字符串相关
replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str
substring(str, position [,length]) -- 从str的position开始,取length个字符
left(string, length) -- 从string2中的左边起取length个字符
length(string) -- string存储空间，单位字节
char_length(string) -- string的字符个数
concat(string [,...]) -- 连接字串
lcase(string) -- 转换成小写
instr(string ,substring) -- 返回substring首次在string中出现的位置
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length
rpad(string, length, pad) -- 在str后用pad补充,直到长度为length
ltrim(string) -- 去除前端空格s
rtrim(string) -- 去除后端空格
repeat(string, count) -- 重复count次
strcmp(string1 ,string2) -- 逐字符比较两字串大小 0：相等，1：string1大，-1：string2大
charset(str) -- 返回字串字符集
load_file(file_name) -- 从文件读取内容
聚合函数
count()
sum();
max();
min();
avg();
group_concat()
用户和权限管理
重置root密码
1. 先停止MySQL服务
2. 跳过权限表启动MySQL：safe_mysqld --skip-grant-tables &
3. 连接MySQL：mysql -u root
4. 更改密码：
use mysql
alter user 'root'@'localhost' identified by '新密码';
# alter user 'root'@'%' identified by '新密码';
5. 刷新权限：flush privileges;
创建新用户
create user 用户名 identified by '密码';
删除用户
drop user 用户名
分配权限
grant 权限列表 on 表名 to 用户名
- all privileges 表示所有权限
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
GRANT ALL PRIVILEGES ON `pms`.* TO 'pms'@'%' IDENTIFIED BY 'pms0817';
- 随机毒鸡汤：梦想就像天上的星星，以前看得到摸不到，现在既看不到也摸不到。
