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;
评论区