常用SQL

数据库相关

Create Datebase

CREATE DATABASE IF NOT EXISTS `new_db`  DEFAULT CHARACTER SET utf8mb4;

Create Table

CREATE TABLE IF NOT EXISTS `new_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT '' COMMENT '姓名',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态标记',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='new表';

Alert Table

ALTER `new_table` ADD COLUMN `new_field` varchar(32) DEFAULT ''  COMMENT 'newField';

Curd

SELECT column1, column2 FROM table_name WHERE condition;
UPDATE table_name SET column1 = value1, column2 = value2 WHERE [condition];
DELETE FROM table_name WHERE condition;

用户相关

创建用户&授权

# 查看用户
select User,Host,authentication_string,password_last_changed from mysql.user;
select CONCAT("DROP USER '",User,"'@'",Host,"';") from mysql.user;

# 查看用户授权
SHOW GRANTS for 'root'@'%';

# 创建普通用户
CREATE USER 'uname'@'localhost' IDENTIFIED BY 'u_pass';
CREATE USER 'uname'@'localhost' IDENTIFIED WITH mysql_native_password BY 'u_pass';

# 创建Slave 用户
GRANT SELECT, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'myslave' IDENTIFIED BY 'u_pass';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

# 删除用户
DROP USER 'uname'@'localhost';
# 用户授权
GRANT ALL ON your_db.* TO 'uname'@'localhost';
GRANT ALL ON new_db.* TO 'zc'@'%';
# 删除用户权限
REVOKE INSERT ON *.* FROM 'uname'@'localhost';
# 修改用户密码 5.7+
ALTER USER 'uname'@'%' IDENTIFIED BY 'new_password';
ALTER USER 'uname'@'%' IDENTIFIED WITH mysql_native_password BY 'new_password';

FLUSH PRIVILEGES; -- 刷新修改

系统相关

查看是否锁表

show open tables;
show open tables where in_use > 0;
# 查看表的状态
show status like 'table%';
show status like 'innodb_row_lock%';

Table_locks_immediate 表示立即释放表锁数，
Table_locks_waited 表示需要等待的表锁数，
Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎

查看/设置参数

show variables like '%max_connect%';
set global max_connect_errors = 1000;
set global max_connections = 300;
flush hosts;
# 或
mysqladmin flush-hosts -p${MYSQL_ROOT_PASSWORD}

数据库、表信息（结构、索引、容量）

# 查看表结构
desc your_table;
# 查看表索引
show index from your_table;
# 查看指定数据库的全部表索引
select TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN,CARDINALITY
from information_schema.statistics
where table_schema='your_db' and INDEX_NAME!='PRIMARY'
GROUP BY TABLE_NAME, INDEX_NAME,CARDINALITY;

# 查看所有数据库各容量大小
select
table_schema,
sum(table_rows) as 'table_rows',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

# 查看指定数据库各表容量大小
select
table_schema,
table_name,
table_rows,
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='your_db'
order by data_length desc, index_length desc;