MySQL数据库常用命令

1.    查看当前用户
mysql> select user();        # 查询当前用户命令语句
            +----------------+
            | user()                  |
            +----------------+
            | dbdh@localhost |
          +----------------+
          1 row in set (0.00 sec)

2. 查看所有用户信息操作

mysql> select user,password,host from mysql.user;

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

| user | password                                                                    | host            |

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

| root | *2E91CB3102EF6B7544F608943E5F0345EA4B1B6E | localhost |

| root | *2E91CB3102EF6B7544F608943E5F0345EA4B1B6E | 127.0.0.1 |

| root | *2E91CB3102EF6B7544F608943E5F0345EA4B1B6E | ::1              |

| dbdh | *2E91CB3102EF6B7544F608943E5F0345EA4B1B6E | 127.0.0.1 |

| dbdh | *2E91CB3102EF6B7544F608943E5F0345EA4B1B6E | localhost |

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

5 rows in set (0.00 sec)

3. 查询当前的数据库版本信息

mysql> select @@version;
+------------+
| @@version    |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)


4. 查询数据库是否启动log_bin

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                                      | Value |
+---------------------------------+-------+
| log_bin                                                  | OFF      |
| log_bin_basename                                |              |
| log_bin_index                                      |              |
| log_bin_trust_function_creators | OFF      |
| log_bin_use_v1_row_events              | OFF      |
| sql_log_bin                                          | ON        |
+---------------------------------+-------+
6 rows in set (0.00 sec)

5.    查询当前支持的字符集

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset    | Description                                  | Default collation      | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5          | Big5 Traditional Chinese        | big5_chinese_ci          |            2 |
| dec8          | DEC West European                      | dec8_swedish_ci          |            1 |
| cp850        | DOS West European                      | cp850_general_ci        |            1 |
| hp8            | HP West European                        | hp8_english_ci            |            1 |
| koi8r        | KOI8-R Relcom Russian              | koi8r_general_ci        |            1 |
| latin1      | cp1252 West European                | latin1_swedish_ci      |            1 |
| latin2      | ISO 8859-2 Central European | latin2_general_ci      |            1 |
| swe7          | 7bit Swedish                                | swe7_swedish_ci          |            1 |
| ascii        | US ASCII                                        | ascii_general_ci        |            1 |
| ujis          | EUC-JP Japanese                          | ujis_japanese_ci        |            3 |
| sjis          | Shift-JIS Japanese                    | sjis_japanese_ci        |            2 |
| hebrew      | ISO 8859-8 Hebrew                      | hebrew_general_ci      |            1 |
| tis620      | TIS620 Thai                                  | tis620_thai_ci            |            1 |
| euckr        | EUC-KR Korean                              | euckr_korean_ci          |            2 |
| koi8u        | KOI8-U Ukrainian                        | koi8u_general_ci        |            1 |
| gb2312      | GB2312 Simplified Chinese      | gb2312_chinese_ci      |            2 |
| greek        | ISO 8859-7 Greek                        | greek_general_ci        |            1 |
| cp1250      | Windows Central European        | cp1250_general_ci      |            1 |
| gbk            | GBK Simplified Chinese            | gbk_chinese_ci            |            2 |
| latin5      | ISO 8859-9 Turkish                    | latin5_turkish_ci      |            1 |
| armscii8 | ARMSCII-8 Armenian                    | armscii8_general_ci |            1 |
| utf8          | UTF-8 Unicode                              | utf8_general_ci          |            3 |
| ucs2          | UCS-2 Unicode                              | ucs2_general_ci          |            2 |
| cp866        | DOS Russian                                  | cp866_general_ci        |            1 |
| keybcs2    | DOS Kamenicky Czech-Slovak    | keybcs2_general_ci    |            1 |
| macce        | Mac Central European                | macce_general_ci        |            1 |
| macroman | Mac West European                      | macroman_general_ci |            1 |
| cp852        | DOS Central European                | cp852_general_ci        |            1 |
| latin7      | ISO 8859-13 Baltic                    | latin7_general_ci      |            1 |
| utf8mb4    | UTF-8 Unicode                              | utf8mb4_general_ci    |            4 |
| cp1251      | Windows Cyrillic                        | cp1251_general_ci      |            1 |
| utf16        | UTF-16 Unicode                            | utf16_general_ci        |            4 |
| utf16le    | UTF-16LE Unicode                        | utf16le_general_ci    |            4 |
| cp1256      | Windows Arabic                            | cp1256_general_ci      |            1 |
| cp1257      | Windows Baltic                            | cp1257_general_ci      |            1 |
| utf32        | UTF-32 Unicode                            | utf32_general_ci        |            4 |
| binary      | Binary pseudo charset              | binary                            |            1 |
| geostd8    | GEOSTD8 Georgian                        | geostd8_general_ci    |            1 |
| cp932        | SJIS for Windows Japanese      | cp932_japanese_ci      |            2 |
| eucjpms    | UJIS for Windows Japanese      | eucjpms_japanese_ci |            3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name                        | Value
          |
+--------------------------+----------------------------------------------------
-----+
| character_set_client          | gbk
          |
| character_set_connection | gbk
          |
| character_set_database      | utf8
          |
| character_set_filesystem | binary
          |
| character_set_results        | gbk
          |
| character_set_server          | utf8
          |
| character_set_system          | utf8
          |
| character_sets_dir              | C:\Program Files\MySQL\MySQL Server 5.6\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)

6.    查询一个数据库字符集及库中某一个表字符集

mysql> show create database test;    #库的字符集
+----------+---------------------------------------------------------------+
| Database | Create Database                                                                                              |
+----------+---------------------------------------------------------------+
| test          | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table test.t1\G;    #test库中t1表中的字符集
*************************** 1. row ***************************
              Table: t1
Create Table: CREATE TABLE `t1` (
    `id` int(3) DEFAULT '0',
    `age` int(3) DEFAULT NULL,
    `name` varchar(20) DEFAULT NULL,
    `school` varchar(30) DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

7. 查找日志文件文件:

mysql> show variables like '%dir%';

8. 修改字符集操作

mysql> alter database test character set utf8;
Query OK, 1 row affected (0.00 sec)

9. 连接mysql 数据库命令语句如下

mysql -h localhost | hostIP -u username -ppassword -D

10. 查询当前所有的数据库

mysql> show databases;
+--------------------+
| Database                      |
+--------------------+
| information_schema |
| mysql                            |
| performance_schema |
| qfclear                        |
| sakila                          |
| test                              |
| train                            |
| world                            |
+--------------------+
8 rows in set (0.00 sec)

11. 如果想使用某一个数据可以使用命令如下:

mysql>use databasename;