MySQL-常用命令

MySQL-常用命令

摘要

本文记录一些用到的重要Mysql命令，不断更新中。

0x01 系统

1.1 连接mysql

mysql -h 192.168.1.1 -P 3306 -u admin -p123456

1.2 字符集

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/xxx/mysql/charsets/   |
+--------------------------+----------------------------+

可以看到默认客户端和连接的字符集是latin1，按需可以设置为utf8等：

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

我们再看看字符集：

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/xxx/mysql/charsets/   |
+--------------------------+----------------------------+

可以看到client和connection字符集已经改为utf8mb4了

0x02 DDL

2.1 truncate

truncate table;

可以将表数据清空，自增id从1重新开始。

0x03 DML

0x04 数据导入导出

4.1 load data local

load data local 
infile '/Users/chengc/cc/work/xxx.csv' 
into table tableName
character set utf8
fields terminated by ','
lines terminated by '\n';