mysql 数据库操作笔记

--更改root密码为D+>ab~[Y/,YF?#B,C%   

 

（mysql5.6版本）

UPDATE user SET password=password('D+>ab~[Y/,YF?#B,C%') WHERE user='root';

（mysql5.7版本）

 update user set authentication_string = password('Jcbaimi1098@')where user = 'root';

alter user 'root'@'localhost' identified by 'youpassword';

FLUSH PRIVILEGES;

mysql>use dbname； 打开数据库：

mysql>show databases; 显示所有数据库

使用mysqldump备份数据库成SQL文件

#mysqldump -u root -p hl_db_identifi > hl_db_identifi.sql

mysql -uroot -p
# 输入登录密码
mysql>
# 创建数据库
mysql>create database test charset=utf8;
# 进入数据库
mysql>use test;
# 执行source命令还有备份数据SQL
mysql>source /opt/test.sql

--查询数据库大小
select
TABLE_SCHEMA,
concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc;

select concat(truncate(sum(data_length)/1024/1024/1024,2),'GB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024/1024,2),'GB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024/1024,2),'GB') as data_free,
concat(truncate(sum(index_length)/1024/1024/1024,2),'GB') as index_size
from information_schema.tables
where TABLE_SCHEMA = 'bm_travel_db';

--查询指定数据库大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='bm_travel_db';