創(chuàng)建數(shù)據(jù)庫，指定字符集和排序規(guī)則

CREATE DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

重命名表

RENAME TABLE old_table_name TO new_table_name;

復(fù)制表結(jié)構(gòu)

CREATE TABLE new_table LIKE old_table;

復(fù)制表結(jié)構(gòu)，并插入數(shù)據(jù)

CREATE TABLE new_table AS SELECT * FROM old_table;

查詢所有表的大小，顯示字段：Database，Table，Size in GB

SELECT table_schema AS "Database",
       table_name AS "Table",
       round(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS "Size in GB"
FROM information_schema.TABLES
WHERE table_schema IN (SELECT schema_name FROM information_schema.schemata)
ORDER BY "Size in GB " DESC

查詢所有數(shù)據(jù)庫的大小，顯示字段：Database，Size in GB

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS "Size in GB"
FROM 
    information_schema.TABLES
GROUP BY 
    table_schema;

新安裝的MySQL5.7實例修改root密碼

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

新安裝的MySQL8.0實例修改root密碼

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

修改用戶權(quán)限

GRANT privileges ON database_name.* TO 'username'@'host' IDENTIFIED BY 'password';