MySQL常用命令

-- Creates the new database
mysql> create database db_example;

-- Drop the database
mysql> drop database db_example;

-- Creates the user
-- % 代表可以从任何机器访问该数据库
mysql> create user 'USER'@'%' identified by 'PASSWORD';

-- Gives all privileges to the new user on the newly created database
mysql> grant all on db_example.* to 'USER'@'%';

-- Revokes all the privileges from the user
mysql> revoke all on db_example.* from 'springuser'@'%';

-- Grant the minimum privileges 
mysql> grant select, insert, delete, update on db_example.* to 'springuser'@'%';

-- List all database users
mysql> select user from mysql.user;

-- Modify user's password
-- Mysql 8.0以上密码策略限制必须要大小写加数字特殊符号，否则会报错
mysql> alter user 'USER'@'%' IDENTIFIED BY 'NEW-PASSWORD';

-- List all tables
mysql> show tables;

-- List specific tables
mysql> show tables like "%keyword%";

-- Show table create schema
mysql> show create table table_name;

-- 查看某一个表使用的存储空间大小
select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',  truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from 
    information_schema.tables
where
    table_name='sign_name'
order by
    data_length desc, index_length desc;

-- 查看某一个数据库的所有表使用的存储空间大小
select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',  truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from 
    information_schema.tables
where
    table_schema ='sms'
order by
    data_length desc, index_length desc;

-- 查看所有表使用的存储空间大小
select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',  truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from 
    information_schema.tables
order by
    data_length desc, index_length desc;

-- date和时间戳互转
-- unix_timestamp(): date转时间戳
-- from_unixtime(): 时间戳转date
mysql> select send_time , from_unixtime(unix_timestamp(send_time)) from sms_record sr limit 1; 

-- 创建索引
mysql> ALTER TABLE tableName ADD INDEX indexName(columnName);
mysql> show index from tableName;