MYSQL 命令行小技巧

MYSQL 连接方式

1 先登录数据库 再查询

#mysql -uroot -p -hlocalhost

Enter Password:

mysql>use mysql;

Database changed
mysql>select user from user;

2 不进入MYSQL DB直接进行查询工作

#mysql -uroot -p -e"select user from user;" user

#mysql -u用户名 -p密码 -e“待执行SQL;” dbname

Enter Password:

+------+
| user |
+------+
| root |
| root |
+------+

 

回滚和提交事物命令

commit;

rollback;

 

3 MYSQL 查询全部的参数信息

#mysql -uroot -proot -e"show variables" mysql

 

+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name                           | Value                                                                                     |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment          | 1                                                                                         |
| auto_increment_offset               | 1                                                                                         |
| autocommit                                 | ON                                                                                        |
| automatic_sp_privileges             | ON                                                                                        |
| back_log                                     | 50                                                                                        |
| basedir                                       | /usr/                                                                                     |
| big_tables                                  | OFF                                                                                       |
| binlog_cache_size                       | 32768                                                                                     |
| binlog_direct_non_transactional_updates | OFF                                                                                       |
| binlog_format                           | STATEMENT                                                                                 |
| bulk_insert_buffer_size                 | 8388608                                                                                   |
| character_set_client                    | utf8                                                                                      |
| character_set_connection                | utf8                                                                                      |
| character_set_database                  | utf8                                                                                      |
| character_set_filesystem                | binary                                                                                    |
| character_set_results                   | utf8                                                                                      |
| character_set_server                    | utf8                                                                                      |
| character_set_system                    | utf8                                                                                      |
| character_sets_dir                      | /usr/share/mysql/charsets/                                                                |
| collation_connection                    | utf8_general_ci                                                                           |
| collation_database                      | utf8_general_ci                                                                           |
| collation_server                        | utf8_general_ci                                                                           |
| completion_type                         | 0                                                                                         |