1 登录
mysql -u songguo -p[password] mysqlmysql -u songguo -p
2 创建&删除 用户
create user ‘songguo’@'localhsot' identified by 'admin'drop user ‘songguo’@'localhsot'
3 更改数据库
use mysqluse 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