使用MySQL client代替navicat

创建命令别名

alias mysql="mysql -hlocalhost -uroot -proot"

备份一个数据库（表）

mysqldump -hlocalhost -uroot -proot databaseName tableName > ~/db.sql

导入备份数据到新表

mysql> create database dbName;
mysql> use dbName;
mysql> source ~/db.sql

查看所有数据库

mysql> show databases;

查看名字包含“sj_”的数据库

mysql> show databases like '%sj_%'

使用某个库

mysql> use dbName;

显示所有表

mysql> show tables;
mysql> show tables from mysql; -- 指定数据库

查看名字包含“sj_”的表

mysql> show tables like '%sj_%'

修改字段

mysql> alter table change column fieldA fieldB int(10) unsigned not null default 0 comment '' after id;

查看表的建表语句

mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cellphone` char(20) NOT NULL DEFAULT '' COMMENT '手机号',
  `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `cellphone` (`cellphone`)
) ENGINE=InnoDB AUTO_INCREMENT=332 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
1 row in set (0.00 sec)

mysql> show create table mysql.user \G -- 指定数据库

“\G”终结符的用处是将数据竖向排列，有时候更方便查看数据

mysql> select * from user limit 1 \G
*************************** 1. row ***************************
              id: 1
         user_id: 1000301
       cellphone: 15623450851
accepted_license: 1
   idnumverified: 0
    creditcardno:
      created_at: 2019-05-17 19:37:21
      updated_at: 2020-07-20 11:25:07
1 row in set (0.00 sec)

“;”终结符横向排列数据(如果窗口窄了换行就比较难看)

mysql> select * from user limit 1 ;
+----+---------+-------------+------------------+---------------+--------------+---------------------+---------------------+
| id | user_id | cellphone   | accepted_license | idnumverified | creditcardno | created_at          | updated_at          |
+----+---------+-------------+------------------+---------------+--------------+---------------------+---------------------+
|  1 | 1000301 | 15623450851 |                1 |             0 |              | 2019-05-17 19:37:21 | 2020-07-20 11:25:07 |
+----+---------+-------------+------------------+---------------+--------------+---------------------+---------------------+
1 row in set (0.00 sec)

修改表名

mysql> alter table old_talbe rename to new_table;

修改表属性（不是所有的属性都必须出现）

alter table table_a ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 comment='xxxx';

更多

mysql> status; -- 查看状态

mysql> select @@global.tx_isolation; -- 查看系统事务隔离级别
mysql> select @@session.tx_isolation; -- 查看当前会话的隔离级别

mysql> select @@global.SQL_MODE; -- 查看系统中的sql_mode
mysql> select @@session.SQL_MODE; -- 查看当前会话的sql_mode

mysql > SET GLOBAL sql_mode = 'modes'; -- 设置全局变量
mysql > SET SESSION sql_mode = 'modes'; -- 设置会话变量

参考

1. 5.1.10 Server SQL Modes