--cmd从pipe（管道）读取语句
type filename | mysql dbname /*cmd*/
cat filename | mysql dbname /*shell*/
type limbs.sql | mysql cookbook
cat limbs.sql | mysql cookbook
--结合mysqldump和mysql拷贝一个数据库到另外一个mysql服务器
mysqldump dbname | mysql -h some.other.host.com other_dbname
mysqldump cookbook | mysql -h other.host cookbook
--使用程序生成sql语句发送到mysql
generate-test-data | mysql cookbook
----一行输入所有sql------
--cmd一行输入sql
mysql -e "statement1" dbname -u username -p password
mysql -e "select count(*) from limbs" cookbook -uroot -p password
--cmd一行输入多个sql
mysql -e "statement1; statement2" dbname -u username -p password
mysql -e "select count(*) from limbs;,select now()" cookbook -uroot -p password
---预防查询输出超出屏幕范围-----
--为mysql设置分页显示程序
mysql --pager=/usr/bin/less
mysql --pager /*已经设置环境变量pager*/
--mysql命令行开启关闭分页功能
p /*开启分页功能*/
p /usr/bin/less
n /*关闭分页功能*/
---发送查询输出到文件或程序---
--交互模式下表格显示结果集数据
select * from limbs
--非交互模式下制表符显示结果集数据
echo select * from limbs | mysql cookbook
--将mysql输出保存到文件
mysql dbname > outputfile
statement1;
statement2;
exit
mysql cookbook > d:result.txt
select * from limbs;
select * from testscore;
exit
--从指定文件读入sql，将结果保存到指定文件
mysql dbname < inputfile > outfile
mysql cookbook < test.txt > d:result.txt
--从指定文件读入sql，将结果发送到另一程序
mysql dbname < inputfile | mail paul
mysql dbname < test.txt | more
mysql cookbook < test.txt | find "alien" /*cmd*/
mysql cookbook < test.txt | grep "alien" /*linux*/
-------选择表格或制表符定界的查询输出格式-----------------
mysql -t dbname < inputfile
mysql -t cookbook < test.txt
mysql -t dbname < inputfile | program
mysql -t cookbook < test.txt | find "a"
---指定任意的输出列分隔符-----------------
mysql cookbook < test.sql | sed -e "s/TAB/:/g" > result.txt
mysql cookbook < test.sql | tr " TAB" ":" > result.txt
mysql cookbook < test.sql | tr " 11" ":" > result.txt
----生成xml或html-----------------
mysql -H -e "statement" dbname > outfile
mysql -X -e "statement" dbname > outfile
mysql -H -e "select * from limbs" cookbook > demohtml.txt
mysql -X -e "select * from limbs" cookbook > demoxml.txt
----使长输出行更具有可读性-----------------
--mysql命令行垂直显示结果集
show full columns from tables;
show full columns from tablesg
show full columns from tablesG /*局部控制垂直显示方式*/
--cmd命令行垂直显示结果集
mysql -E /*全局控制垂直显示方式*/
------控制mysql的繁冗级别-----------------
echo select now() | mysql /*cmd*/
echo select now() | mysql -v /*cmd*/
echo select now() | mysql -vv /*cmd*/
echo select now() | mysql -vvv /*cmd*/
echo "select now()" | mysql /*linux*/
echo "select now()" | mysql -v /*linux*/
echo "select now()" | mysql -vv /*linux*/
echo "select now()" | mysql -vvv /*linux*/
-------记录交互式的mysql会话-----------------
--cmd命令行记录交互日志到指定文件
mysql -tee=outfile dbname
mysql -tee=tmp.out cookbook
--T开启交互日志功能、t关闭日志交互功能
----以之前执行的语句创建mysql脚本-----------------
--使用mysql执行语句历史文件.mysql_history(位于用户home目录下)
cd; cat .mysql_history