MySQL基础命令11条

1:查看显示所有数据库

mysql> show databases;

+--------------------+

| Database      |

+--------------------+

| information_schema |

| INVOICE      |

| mysql       |

| performance_schema |

| test        |

+--------------------+

5 rows in set (0.00 sec)

2:查看当前使用的数据库

mysql> select database();

+------------+

| database() |

+------------+

| INVOICE  |

+------------+

1 row in set (0.00 sec)

3:查看数据库使用端口

mysql> show variables like 'port';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port     | 3306 |

+---------------+-------+

1 row in set (0.00 sec)

4:查看当前数据库大小

例如,我要查看INVOICE数据库的大小,那么可以通过下面SQL查看

mysql> use information_schema

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

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='INVOICE';

+-----------+

| DB Size  |

+-----------+

| 7929.58MB |

+-----------+

1 row in set, 1 warning (0.00 sec)

查看数据所占的空间大小

mysql> use information_schema;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select concat(round(sum(data_length)/(1024*1024),2),'MB') as 'DB Size'

  -> from tables

  -> where table_schema='INVOICE';

+-----------+

| DB Size  |

+-----------+

| 6430.26MB |

+-----------+

1 row in set, 1 warning (0.00 sec)

查看索引所占的空间大小

mysql> select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' 

  -> from tables 

  -> where table_schema='INVOICE';

+-----------+

| DB Size  |

+-----------+

| 1499.32MB |

+-----------+

1 row in set, 1 warning (0.13 sec) 

5:查看数据库编码

mysql> show variables like 'character%';

+--------------------------+----------------------------+

| Variable_name      | Value           |

+--------------------------+----------------------------+

| character_set_client   | utf8            |

| character_set_connection | utf8            |

| character_set_database  | utf8            |

| character_set_filesystem | binary           |

| character_set_results  | utf8            |

| character_set_server   | latin1           |

| character_set_system   | utf8            |

| character_sets_dir    | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

character_set_client 为客户端编码方式;

character_set_connection 为建立连接使用的编码;

character_set_database 为数据库的编码;

character_set_results 为结果集的编码;

character_set_server 为数据库服务器的编码;

只要保证以上采用的编码方式一样,就不会出现乱码问题。

mysql> show variables like 'collation%';

+----------------------+-------------------+

| Variable_name    | Value       |

+----------------------+-------------------+

| collation_connection | utf8_general_ci  |

| collation_database  | utf8_general_ci  |

| collation_server   | latin1_swedish_ci |

+----------------------+-------------------+

3 rows in set (0.00 sec)

status也可以查看数据库的编码

mysql> status;

\--------------

mysql Ver 14.14 Distrib 5.6.20, for Linux (x86_64) using EditLine wrapper

Connection id:     1

Current database:    INVOICE

Current user:      root@localhost

SSL:          Not in use

Current pager:     stdout

Using outfile:     ''

Using delimiter:    ;

Server version:     5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Protocol version:    10

Connection:       Localhost via UNIX socket

Server characterset:  latin1

Db   characterset:  latin1

Client characterset:  utf8

Conn. characterset:  utf8

UNIX socket:      /var/lib/mysql/mysql.sock

Uptime:         5 hours 18 min 51 sec

Threads: 1 Questions: 10884 Slow queries: 0 Opens: 650 Flush tables: 1 Open tables: 268 Queries per second avg: 0.568

\--------------

6:查看数据库的表信息

mysql> show tables;

+---------------------------------------+

| Tables_in_information_schema     |

+---------------------------------------+

| CHARACTER_SETS            |

| COLLATIONS              |

| COLLATION_CHARACTER_SET_APPLICABILITY |

| COLUMNS                |

| COLUMN_PRIVILEGES           |

| ENGINES                |

| EVENTS                |

| FILES                 |

| GLOBAL_STATUS             |

| GLOBAL_VARIABLES           |

| KEY_COLUMN_USAGE           |

| OPTIMIZER_TRACE            |

| PARAMETERS              |

| PARTITIONS              |

| PLUGINS                |

| PROCESSLIST              |

| PROFILING               |

| REFERENTIAL_CONSTRAINTS        |

| ROUTINES               |

| SCHEMATA               |

| SCHEMA_PRIVILEGES           |

| SESSION_STATUS            |

| SESSION_VARIABLES           |

| STATISTICS              |

| TABLES                |

| TABLESPACES              |

| TABLE_CONSTRAINTS           |

| TABLE_PRIVILEGES           |

| TRIGGERS               |

| USER_PRIVILEGES            |

| VIEWS                 |

| INNODB_LOCKS             |

| INNODB_TRX              |

| INNODB_SYS_DATAFILES         |

| INNODB_LOCK_WAITS           |

| INNODB_SYS_TABLESTATS         |

| INNODB_CMP              |

| INNODB_METRICS            |

| INNODB_CMP_RESET           |

| INNODB_CMP_PER_INDEX         |

| INNODB_CMPMEM_RESET          |

| INNODB_FT_DELETED           |

| INNODB_BUFFER_PAGE_LRU        |

| INNODB_SYS_FOREIGN          |

| INNODB_SYS_COLUMNS          |

| INNODB_SYS_INDEXES          |

| INNODB_FT_DEFAULT_STOPWORD      |

| INNODB_SYS_FIELDS           |

| INNODB_CMP_PER_INDEX_RESET      |

| INNODB_BUFFER_PAGE          |

| INNODB_CMPMEM             |

| INNODB_FT_INDEX_TABLE         |

| INNODB_FT_BEING_DELETED        |

| INNODB_SYS_TABLESPACES        |

| INNODB_FT_INDEX_CACHE         |

| INNODB_SYS_FOREIGN_COLS        |

| INNODB_SYS_TABLES           |

| INNODB_BUFFER_POOL_STATS       |

| INNODB_FT_CONFIG           |

+---------------------------------------+

59 rows in set (0.00 sec)

或者使用下面SQL语句查看某个数据库的表信息。

mysql> select * from information_schema.tables where table_schema='databasename';

查看某种具体表的信息

mysql> select * from information_schema.tables where table_name ='table_name'

7:查看数据库的所有用户信息

mysql> select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;

+-------------------------------------+

| query                |

+-------------------------------------+

| user: 'root'@'127.0.0.1';      |

| user: 'root'@'::1';         |

| user: 'root'@'gettesx20.test.com'; |

| user: 'root'@'localhost';      |

+-------------------------------------+

4 rows in set (0.00 sec)

8: 查看某个具体用户的权限

mysql> show grants for 'root'@'localhost';

+---------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@localhost                                                       |

+---------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*C7B1594FD74578DA3A92A61720AC67C6DBE6FC23' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                      |

+---------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

9: 查看数据库的最大连接数

mysql> show variables like '%max_connections%';

+-----------------+-------+

| Variable_name  | Value |

+-----------------+-------+

| max_connections | 151  |

+-----------------+-------+

1 row in set (0.00 sec)

10:查看数据库当前连接数,并发数。

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状态。

11:查看数据文件存放路径

mysql> show variables like '%datadir%';

+---------------+-------------------+

| Variable_name | Value       |

+---------------+-------------------+

| datadir    | /mysqldata/mysql/ |

+---------------+-------------------+

1 row in set (0.00 sec)
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇