Mysql常用语句

1、查询该库所有表注释：

select table_name,table_comment from information_schema.tables where table_schema = 'databases' and table_comment like "%任务%";

select table_name from information_schema.tables where table_schema='databases' and table_name like '%settings%';

2、查找数据表

select table_name,table_comment from information_schema.tables where table_schema = 'databases' and table_name like "%ims_ewei_shop%";

3、查看该表结构：

show create table testing \G;

4、查看线程：

show processlist;

5、设置数据库编码：

charset utf8;

6、新增用户与数据库：

CREATE USER 'bbhsky'@'%' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER ON blog.* TO 'bbhsky'@'%';

#如果是全权限，那么就写ALL PRIVILEGES
GRANT ALL PRIVILEGES ON blog.* TO 'bbhsky'@'%';

CREATE DATABASE blog;
flush privileges;

//创建删除用户
DROP USER 'bbhsky'@'%';

7、删除数据库

DROP DATABASE blog;

8、导出导入数据：

#导出库数据
mysqldump -h192.168.140.95 -ublog -p phpcms >phpcms.sql
#导出表数据
mysqldump phpcms -h192.168.140.95 -ublog -p --tables phpcms_section >phpcms_section.sql
#导入数据
mysql -h192.168.140.95 -ublog -p phpcms < phpcms.sql

9、给Root添加远程访问权限

UPDATE user SET `Host` = '%' WHERE `User` = 'root' LIMIT 1;
flush privileges;