Learning MySQL 2 - 常用命令

1 登录

mysql -u songguo -p[password] mysql
mysql -u songguo -p

2 创建&删除 用户

create user ‘songguo’@'localhsot' identified by 'admin'
drop user ‘songguo’@'localhsot'

3 更改数据库

use mysql
use mydb
use test

4 查看数据库

(1)查看当前正在使用的数据库

select database();

(2)查看所有数据库

show databases;

5 查看用户

(1)查看当前用户

select user();

(2)查看所有用户

select user, host from user;

6 授予&回收权限

权限分为global privileges, database privileges, table privileges, column privileges 等.

(1)授予权限

grant ALL on mydb.* to 'songguo'@'localhost';
grant FILE on *.* to 'songguo'@'localhost'; -- FILE: global privilege

(2)回收权限

revoke ALL on mydb.* from 'songguo'@'localhost';
revoke FILE on *.* from 'songguo'@‘localhost’;

(3)查看权限

show grants;

flush privileges;

7 查看表&表的结构

show tables;
describe table_name;

8 导出

(1)select into outfile 'filename'

这种方法只适用于单个表, 且导出的文件中只有数据, 没有表结构.且使用该命令的用户必须有FILE权限, 
且输出的目录的权限也有限制(/tmp目录可以), 因此很有限制性.
select * from table_name into outfile '/path/to/afile.txt';
select * from table_name into outfile '/path/to/afile.txt'
    [fields terminated by ','] 
    [enclosed by '"']
    [lines terminated by '\n'];

(2)mysqldump

(a) A common use of mysqldump is for making a backup of an entire database:
mysqldump -u songguo -p[password] db_name > /path/to/backup-file.sql
(b) You can load the dump file back into the server like this:
mysql -u songguo -p[password] db_name < backup-file.sql
(c) 只导出建表命令,无数据,则命令如下
mysqldump -d -u songguo -p[password] db_name < /path/from/backup-file.sql
(d) 只导出插入数据的sql命令,无建表建表命令
mysqldump -t -u songguo -p[password] db_name < /path/from/backup-file.sql
(e) 只有纯文本数据,没有任何命令
mysqldump -T /tmp -u songguo -p[password] db_name [table_name]
(f)和select into outfile方法一样,可以指定字段分隔符等
mysqldump -T /tmp -u songguo -p[password] db_name [table_name]
    [--fields-terminated-by=,]
    [--fields-enclosed-by="]
    [--lines-terminated-by=\n]

9 导入

(1)使用load命令

load data infile '/path/from/afile.txt' into table table_name 
    [fields terminated by ',']
    [enclosed by '"']
    [lines terminated by '\n']
注意此时空白行(包括最后的一个空白行)会当作空值被导入.

(2)使用mysql命令

mysql -u songguo -p[password] db_name < /path/from/backup-file.sql

(3)使用mysqldump命令

mysql -u songguo -p[password] db_name < backup-file.sql