Mysql 常用命令

Aug 27, 201220

AI-generated summary

This article provides a list of commonly used MySQL commands, including importing and exporting data, creating tables, and accessing databases through the command line. It also includes specific examples and references for further reading.

1 导入数据到远程数据库：
直接执行文件中 sql 语句
cmd> mysql -h 182.50.133.140 -u root -p databasename < "c:/tmp/da.sql"

进入 mysql 控制命令行

cmd> mysql -h 182.50.133.140 -u root -p

show databases;

use mydatabase;

show tables ;

select * from users limit 1,3;

mysqldump -u backup -p 123456 -h 192.168.1.2 backup_test > D:\bak\bakcup.sql

在命令行中导入本地数据文件，以 '#' 分割字段
mysql>load data local infile 'c:/tmp/renrentest.txt' into table openkavaadmin.renrentest fields terminated by '#'

use databasename;

create table test(t varchar(2000));

load data infile '/etc/ssh/ssh_config' into table test;

select * into outfile '/tmp/t.txt' from test;

如果您不指定 FIELDS 子句，则默认值为假设您写下如下语句时的值：
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\'

mysqlimport 导入数据
cmd>mysqlimport -C -h 182.50.133.140 -u openkavaadmin -p openkavaadmin -L c:/tmp/t.txt

命令行下具体用法如下：

mysqldump -u 用戶名 -p 密码 -d 数据库名表名 > 脚本名；

导出整个数据库结构和数据 mysqldump -h localhost -uroot -p123456 database > dump.sql

导出单个数据表结构和数据 mysqldump -h localhost -uroot -p123456 database table > dump.sql

导出整个数据库结构（不包含数据）
mysqldump -h localhost -uroot -p123456 -d database > dump.sql

导出单个数据表结构（不包含数据）
mysqldump -h localhost -uroot -p123456 -d database table > dump.sql

参考：
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-data
http://database.51cto.com/art/200510/8105.htm
http://blog.csdn.net/adparking/article/details/6676571