原文链接:mysql常用命令总结
自已在工作的过程中总结的一些mysql常用命令,及一些不是很常用的,但也是比较有用的,所以一并呈现出来。
1、查看字符集
mysql>\s;
2、显示数据表的结构:
mysql>describe 表名;
3、分配权限
mysql>create user guest@localhost identified by ’123456′;
mysql>grant select on mydb.* to guest@localhost;
或
grant 权限 on 数据库.表 to 用户 @ 访问方式 identified by 密码
mysql>grant select on mydb.* to guest@localhost identified by ’123456′; //只有select
mysql>grant all privileges on mydb.* to mydb@’%’ identified by “mydb”;//全部
4、查看 MySQL 数据表(table) 的字符集
mysql>show table status from test like ‘%user%’;
5、查看 MySQL 数据列(column)的字符集
mysql>show full columns from user;
6、显示创建表:
mysql>show creata table user;
7、在库中查找表
mysql>SHOW TABLES LIKE ‘%test%’
或
mysql>select TABLE_NAME
from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=’test’ and TABLE_NAME like ‘%user%’ ;
8、mysql的group by排序
SELECT * FROM (SELECT * FROM test3 ORDER BY dtime DESC) as temp GROUP BY bid ORDER BY dtime DESC;
select id,bid,substring_index(group_concat(cid order by `dtime` desc),’,',1) as cid from `test3` group by bid
9、更改表引擎:
ALTER TABLE test3 ENGINE = INNODB;
10、查看 MySQL 数据库服务器和数据库字符集
mysql> show variables like ‘%char%’;
mysql>show variables like ‘collation_%’;
11、查看 MySQL 数据表(table) 的字符集
mysql> show table status from sqlstudy_db like ‘%countries%’;
12、查看 MySQL 数据列(column)的字符集
mysql> show full columns from countries;
13、查看当前安装的 MySQL 所支持的字符集
mysql> show charset;或者show char set;
14、查找MySQL的cnf文件的位置
find / -iname ‘*.cnf’ -print
15、修改数据库的字符集
mysql>alter database mydb character set utf-8(utf8);
16、创建数据库指定数据库的字符集
mysql>create database mydb character set utf-8(utf8);
17、通过配置文件修改:
修改/var/lib/mysql/mydb/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
为
default-character-set=utf8
default-collation=utf8_general_ci
18、通过MySQL命令行修改字符集
mysql> set character_set_client=utf8;
mysql> set character_set_connection=utf8;
mysql> set character_set_database=utf8;
mysql> set character_set_results=utf8;
mysql> set character_set_server=utf8;
mysql> set character_set_system=utf8;
mysql> set collation_connection=utf8;
mysql> set collation_database=utf8;
mysql> set collation_server=utf8;
19、查看表索引
mysql> show index from tblname;
over,后续再添加,只有自已总结过的知识印象才会更加深刻。