一、查看显示所有数据库
mysql> show databases;
--------------------
| Database |
--------------------
| information_schema |
| dbname |
| mysql |
| test |
--------------------
4 rows in set (0.30 sec)
二、查看当前使用的数据库
为选择数据库查询时，为NUll
mysql> select database();
------------
| database() |
------------
| NULL |
------------
1 row in set (0.00 sec)
选择使用数据库后查询结构
mysql> use test
Database changed
mysql> select database();
------------
| database() |
------------
| test |
------------
1 row in set (0.00 sec)
三、查看数据库使用端口
mysql> show variables like 'port';
--------------- -------
| Variable_name | Value |
--------------- -------
| port | 3306 |
--------------- -------
1 row in set (0.00 sec)
四、查看当前数据库大小
我要查看test数据库的大小，可以通过information_schema数据库查看
在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。
mysql> use information_schema
Database changed
mysql> select concat(round(sum(data_length)/(1024*1024),2) round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size'
-> from tables
-> where table_schema='test';
---------
| DB Size |
---------
| 0.05MB |
---------
1 row in set (1.37 sec)
五、查看数据所占的空间大小
mysql> select concat(round(sum(data_length)/(1024*1024),2),'MB') as 'DB Size'
-> from tables
-> where table_schema='test';
---------
| DB Size |
---------
| 0.05MB |
---------
1 row in set (0.24 sec)
六、查看索引所占的空间大小
mysql> select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size'
-> from tables
-> where table_schema='test';
---------
| DB Size |
---------
| 0.00MB |
---------
1 row in set (0.19 sec)
七、查看数据库编码
通过匹配character查看编码
mysql> show variables like 'character%';
-------------------------- ------------------------------------------------
| Variable_name | Value |
-------------------------- ------------------------------------------------
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | f:wampbinmysqlmysql5.0.51bsharecharsets |
-------------------------- ------------------------------------------------
8 rows in set (0.00 sec)
关于以上查询编码说明参考编码设置
通过匹配collation查看编码
mysql> show variables like 'collation%';
---------------------- -------------------
| Variable_name | Value |
---------------------- -------------------
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
---------------------- -------------------
3 rows in set (0.00 sec)
通过status也可以查看数据库的编码
mysql> status;
--------------
f:/wamp/bin/mysql/mysql5.0.51b/bin/mysql.exe Ver 14.12 Distrib 5.0.51b, for Win32 (ia32)
Connection id: 21
Current database: information_schema
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.51b-community-nt MySQL Community Edition(GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 45 min 3 sec
Threads: 1 Questions: 131 Slow queries: 0 Opens: 25 Flush tables: 1 Open ta
bles: 5 Queries per second avg: 0.048
八、查看数据库的表信息
mysql> show tables;
---------------------------------------
| Tables_in_information_schema |
---------------------------------------
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| PROFILING |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
---------------------------------------
17 rows in set (0.00 sec)
或者使用下面SQL语句查看某个数据库的表信息。
select * from information_schema.tables where table_schema='databasename';
查看某种具体表的信息
select * from information_schema.tables where table_name ='table_name'
九、查看数据库的所有用户信息
mysql> select distinct concat('user: ''',user,'''@''',host,''';') as query from
mysql.user;
--------------------------------------
| query |
--------------------------------------
| user: ' pig'@'%'; |
| user: 'wangxiansong'@'%'; |
| user: 'xiaojianbing'@'%'; |
| user: 'root'@'127.0.0.1'; |
| user: 'root'@'localhost'; |
| user: 'xiaojianbing'@'localhost'; |
--------------------------------------
6 rows in set (0.00 sec)
十、查看某个具体用户的权限
mysql> show grants for 'root'@'localhost';
---------------------------------------------------------------------
| Grants for root@localhost |
---------------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
---------------------------------------------------------------------
1 row in set (0.00 sec)
十一、查看数据库的最大连接数
mysql> show variables like '%max_connections%';
----------------- -------
| Variable_name | Value |
----------------- -------
| max_connections | 255 |
----------------- -------
1 row in set (0.00 sec)
十二、查看数据库当前连接数，并发数。
mysql> show status like 'Threads%';
------------------- -------
| Variable_name | Value |
------------------- -------
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
------------------- -------
4 rows in set (0.00 sec)
Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动，已创建线程的数量。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。
mysql> show variables like '%max_connections%';
----------------- -------
| Variable_name | Value |
----------------- -------
| max_connections | 255 |
----------------- -------
1 row in set (0.00 sec)
十三、查看数据文件存放路径
mysql> show variables like '%datadir%';
--------------- --------------------------------------
| Variable_name | Value |
--------------- --------------------------------------
| datadir | f:wampbinmysqlmysql5.0.51bdata |
--------------- --------------------------------------
1 row in set (0.00 sec)