Mysql常用操作

常用操作

mysql -h localhost -u root -proot

// 删除数据库
drop database aaa

// 创建数据库 aaa
CREATE DATABASE aaa DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

// 导出数据库
mysqldump -h localhost -u root -proot --opt -d bbb --default-character-set=utf8 > bbb.sql
mysqldump -h localhost -u root -proot bbb --default-character-set=utf8 > bbb.sql

// 导入数据库
mysql -h localhost -uroot -proot --default-character-set=utf8 ccc < ccc.sql

修改用户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

创建新用户

CREATE USER 'dbname'@'localhost' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'dbname'@'localhost'

5.6 版本初次的密码修改

mysql -u root mysql

UPDATE user SET Password=PASSWORD('newpassword') where USER='root';

FLUSH PRIVILEGES;//记得要这句话，否则如果关闭先前的终端，又会出现原来的错误

\q 

删除指定前缀表

Select CONCAT( 'drop table ', table_name, ';' ) 
FROM information_schema.tables 
Where table_name LIKE 'ts_blog%' and TABLE_SCHEMA='sxpt_info';