创建命令别名
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'; -- 设置会话变量