本文介绍了如何在shell中读写mysql数据库。主要介绍了如何在shell 中连接mysql数据库,如何在shell中创建数据库,创建表,插入csv文件,读取mysql数据库,导出mysql数据库为xml或html文件, 并分析了核心语句。本文介绍的方法适用于PostgreSQL ,相对mysql而言,shell 中读写PostgreSQL会更简单些。
#1. 连接mysql 数据库
shell中连接数据库的方法很简单,只需要指定用户名,密码,连接的数据库名称,然后通过重定向,输入mysql的语句,如下所示:
1 2 3 |
<span class="pln">mysql </span><span class="pun">-</span><span class="pln">u USERNAME </span><span class="pun">-</span><span class="pln">p PASSWORD DATABASENAME </span><span class="pun"><<</span><span class="pln">EOF </span><span class="lit">2</span><span class="pun">></span><span class="str">/dev/</span><span class="kwd">null</span><span class="pln"> show databases</span><span class="pun">;</span><span class="pln"> EOF</span> |
但这并不是一个好办法,任何使用该脚本的用户都能看到该数据库用户的账号和密码,要解决这个问题,可以用mysql 数据库的一个特殊配置文件。mysql 数据库使用$HOME/.my.cnf
文件来读取特殊的启动命令和设置。其中一项设置是由该用户账户发起的mysql 会话的默认密码。要在这个文件中设置默认密码,可以加入下面的内容:
1 2 |
<span class="pun">[</span><span class="pln">client</span><span class="pun">]</span><span class="pln"> password </span><span class="pun">=</span> <span class="lit">123456</span> |
然后,别忘了修改权限:
1 |
<span class="pln">chmod </span><span class="lit">400</span> <span class="pun">.</span><span class="kwd">my</span><span class="pun">.</span><span class="pln">cnf</span> |
这样就可以通过脚本访问mysql数据库了,如下所示:
1 2 3 4 5 6 7 |
<span class="c"><span class="com">#!/bin/bash</span></span> <span class="nv"><span class="pln">MYSQL</span></span><span class="o"><span class="pun">=</span></span><span class="sb"><span class="str">`</span></span><span class="str">which mysql</span><span class="sb"><span class="str">`</span></span> <span class="nv"><span class="pln">$MYSQL</span></span> <span class="nb"><span class="pln">test</span></span> <span class="pun">-</span><span class="pln">u root </span><span class="sh"><span class="pun"><<</span><span class="pln"> EOF show databases</span><span class="pun">;</span><span class="pln"> show tables</span><span class="pun">;</span> <span class="kwd">select</span> <span class="pun">*</span> <span class="kwd">from</span><span class="pln"> employees </span><span class="kwd">where</span><span class="pln"> salary </span><span class="pun">></span> <span class="lit">4000</span><span class="pun">;</span><span class="pln"> EOF</span></span> |
#2. 创建数据库
通过上面的方法连接数据库,再通过重定向输入mysql语句,shell中读写mysql基本就介绍完了。只要把sql语句写对了,通过重定向执行即可,下面来看一个实例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<span class="c"><span class="com">#!/bin/bash</span></span> <span class="c"><span class="com">##############################</span></span> <span class="c"><span class="com"># @file create_db_mysql.sh</span></span> <span class="c"><span class="com"># @brief create database and tables in mysql</span></span> <span class="c"><span class="com"># @author Mingxing LAI</span></span> <span class="c"><span class="com"># @version 0.1</span></span> <span class="c"><span class="com"># @date 2013-01-20</span></span> <span class="c"><span class="com">##############################</span></span> <span class="nv"><span class="pln">USER</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"root"</span></span> <span class="nv"><span class="pln">DATABASE</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"students"</span></span> <span class="nv"><span class="pln">TABLE</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"students"</span></span> <span class="c"><span class="com">######################</span></span> <span class="c"><span class="com">#crate database</span></span><span class="pln"> mysql </span><span class="pun">-</span><span class="pln">u </span><span class="nv"><span class="pln">$USER</span></span> <span class="sh"><span class="pun"><<</span><span class="pln"> EOF </span><span class="lit">2</span><span class="pun">></span><span class="str">/dev/</span><span class="kwd">null</span><span class="pln"> CREATE DATABASE $DATABASE EOF </span></span><span class="o"><span class="pun">[</span></span> <span class="nv"><span class="pln">$</span><span class="pun">?</span></span> <span class="pun">-</span><span class="pln">eq </span><span class="lit">0</span> <span class="o"><span class="pun">]</span></span> <span class="o"><span class="pun">&&</span></span> <span class="nb"><span class="pln">echo</span></span> <span class="s2"><span class="str">"created DB"</span></span> <span class="o"><span class="pun">||</span></span> <span class="nb"><span class="pln">echo </span></span><span class="pln">DB already exists </span><span class="c"><span class="com">######################</span></span> <span class="c"><span class="com">#create table</span></span><span class="pln"> mysql </span><span class="pun">-</span><span class="pln">u </span><span class="nv"><span class="pln">$USER</span></span> <span class="nv"><span class="pln">$DATABASE</span></span> <span class="sh"><span class="pun"><<</span><span class="pln"> EOF </span><span class="lit">2</span><span class="pun">></span><span class="str">/dev/</span><span class="kwd">null</span><span class="pln"> CREATE TABLE $TABLE</span><span class="pun">(</span><span class="pln"> id </span><span class="kwd">int</span><span class="pun">,</span><span class="pln"> name varchar</span><span class="pun">(</span><span class="lit">100</span><span class="pun">),</span><span class="pln"> mark </span><span class="kwd">int</span><span class="pun">,</span><span class="pln"> dept varchar</span><span class="pun">(</span><span class="lit">4</span><span class="pun">)</span> <span class="pun">);</span><span class="pln"> EOF </span></span><span class="o"><span class="pun">[</span></span> <span class="nv"><span class="pln">$</span><span class="pun">?</span></span> <span class="pun">-</span><span class="pln">eq </span><span class="lit">0</span> <span class="o"><span class="pun">]</span></span> <span class="o"><span class="pun">&&</span></span> <span class="nb"><span class="pln">echo</span></span> <span class="s2"><span class="str">"Created table students"</span></span> <span class="o"><span class="pun">||</span></span> <span class="nb"><span class="pln">echo</span></span> <span class="s2"><span class="str">"Table students already exist"</span></span> <span class="c"><span class="com">######################</span></span> <span class="c"><span class="com">#delete data</span></span><span class="pln"> mysql </span><span class="pun">-</span><span class="pln">u </span><span class="nv"><span class="pln">$USER</span></span> <span class="nv"><span class="pln">$DATABASE</span></span> <span class="sh"><span class="pun"><<</span><span class="pln"> EOF </span><span class="lit">2</span><span class="pun">></span><span class="str">/dev/</span><span class="kwd">null</span><span class="pln"> DELETE FROM $TABLE</span><span class="pun">;</span><span class="pln"> EOF</span></span> |
这个脚本比较简单,就是几条SQL语句,没什么好解释的,下面来看一下,如何读入csv 文件,然后插入到mysql数据库中。
#3. 插入csv 文件
上面创建了一个学生表,表中有学生的学号,姓名,成绩,系别,假设有一个csv文件,内容如下:
1 2 3 4 5 6 7 8 9 10 11 |
<span class="pln">$cat data </span><span class="lit">1</span><span class="pun">,</span><span class="typ">Navin</span><span class="pln"> M</span><span class="pun">,</span><span class="lit">98</span><span class="pun">,</span><span class="pln">CS </span><span class="lit">2</span><span class="pun">,</span><span class="typ">Kavya</span><span class="pln"> N</span><span class="pun">,</span><span class="lit">70</span><span class="pun">,</span><span class="pln">CS </span><span class="lit">3</span><span class="pun">,</span><span class="typ">Nawaz</span><span class="pln"> O</span><span class="pun">,</span><span class="lit">80</span><span class="pun">,</span><span class="pln">CS </span><span class="lit">4</span><span class="pun">,</span><span class="typ">Hari</span><span class="pln"> S</span><span class="pun">,</span><span class="lit">80</span><span class="pun">,</span><span class="pln">EC </span><span class="lit">5</span><span class="pun">,</span><span class="typ">Alex</span><span class="pln"> M</span><span class="pun">,</span><span class="lit">50</span><span class="pun">,</span><span class="pln">EC </span><span class="lit">6</span><span class="pun">,</span><span class="typ">Neenu</span><span class="pln"> J</span><span class="pun">,</span><span class="lit">70</span><span class="pun">,</span><span class="pln">EC </span><span class="lit">7</span><span class="pun">,</span><span class="typ">Bob</span><span class="pln"> A</span><span class="pun">,</span><span class="lit">30</span><span class="pun">,</span><span class="pln">EC </span><span class="lit">8</span><span class="pun">,</span><span class="typ">Anu</span><span class="pln"> M</span><span class="pun">,</span><span class="lit">90</span><span class="pun">,</span><span class="pln">AE </span><span class="lit">9</span><span class="pun">,</span><span class="typ">Sruthi</span><span class="pun">,</span><span class="lit">89</span><span class="pun">,</span><span class="pln">AE </span><span class="lit">10</span><span class="pun">,</span><span class="typ">Andrew</span><span class="pun">,</span><span class="lit">89</span><span class="pun">,</span><span class="pln">AE</span> |
为了将csv 文件插入到数据库,我们需要逐行读入,然后给字符串加上双引号,最后生成语句如下:
1 |
<span class="pln">insert </span><span class="kwd">into</span><span class="pln"> students VALUES</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span> <span class="str">"Navin M"</span><span class="pun">,</span> <span class="lit">98</span><span class="pun">,</span> <span class="str">"CS"</span><span class="pun">);</span> |
要解析csv 文件,最好的工具莫过于awk了,将域的分隔符指定为逗号-F,
,awk就自动将各个域拆分出来了,然后在需要双引号的地方打印输出一个双引号,就能够轻松得到下面这样的数据:
1 |
<span class="lit">1</span><span class="pun">,</span> <span class="str">"Navin M"</span><span class="pun">,</span> <span class="lit">98</span><span class="pun">,</span> <span class="str">"CS"</span> |
awk 代码如下:
1 2 3 |
<span class="pln">query</span><span class="pun">=</span><span class="str">`echo $line | awk -F, '{ printf("%s,\"%s\",%s,\"%s\"", $1, $2, $3, $4)}'`</span><span class="pln"> statement</span><span class="pun">=</span><span class="str">`echo "INSERT INTO $TABLE VALUES($query);"`</span><span class="pln"> echo $statement</span> |
当然了,你也可以用其他办法,不过,几乎没有比awk更简单的了,第2种方法如下:
1 2 3 4 5 6 7 8 9 10 11 12 |
<span class="pln">oldIFS</span><span class="pun">=</span><span class="pln">$IFS IFS</span><span class="pun">=,</span><span class="pln"> values</span><span class="pun">=(</span><span class="pln">$line</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">[</span><span class="lit">1</span><span class="pun">]=</span><span class="str">"\"`echo ${values[1]} | tr ' ' '#' `\""</span><span class="pln"> values</span><span class="pun">[</span><span class="lit">3</span><span class="pun">]=</span><span class="str">"\"`echo ${values[3]}`\""</span><span class="pln"> query</span><span class="pun">=</span><span class="str">`echo ${values[@]} | tr ' #' ', '`</span><span class="pln"> IFS</span><span class="pun">=</span><span class="pln">$oldIFS statement</span><span class="pun">=</span><span class="str">`echo "INSERT INTO $TABLE VALUES($query);"`</span><span class="pln"> echo </span><span class="str">"$statement"</span> |
首先通过指定域分隔符,将csv文件解析成一个数组,然后将空格替换成一个特殊的符号”#”(因为后面的替换中,会一次性输出数组,而数组是用空格分隔各字段,我们要将分隔数组的空格替换成逗号,所以这里将数据中的空格替换成”#”) ,给字符串加上双引号,最后再把空格替换成逗号,把”#”替换为空格。这种方法真是让人抓狂,我第一次就没有看明白,尤其是为什么要将空格替换成”#”。
完整的插入数据的程序如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
<span class="c"><span class="com">#!/bin/bash</span></span> <span class="c"><span class="com">#</span></span> <span class="c"><span class="com"># @file write_to_db_mysql.sh</span></span> <span class="c"><span class="com"># @brief wirte data to database in mysql</span></span> <span class="c"><span class="com"># @author Mingxing LAI</span></span> <span class="c"><span class="com"># @version 0.1</span></span> <span class="c"><span class="com"># @date 2013-01-20</span></span> <span class="c"><span class="com">#</span></span> <span class="nv"><span class="pln">USER</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"root"</span></span> <span class="nv"><span class="pln">DATABASE</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"students"</span></span> <span class="nv"><span class="pln">TABLE</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"students"</span></span> <span class="k"><span class="kwd">if</span></span> <span class="o"><span class="pun">[</span></span> <span class="nv"><span class="pln">$</span><span class="com"># </span></span><span class="com">-ne 1 </span><span class="o"><span class="com">]</span></span><span class="com">; </span><span class="k"><span class="com">then</span> </span><span class="nb"><span class="pln">echo</span></span> <span class="nv"><span class="pln">$0</span></span><span class="pln"> DATAFILE </span><span class="nb"><span class="pln">echo </span><span class="kwd">exit</span> </span><span class="lit">2</span> <span class="k"><span class="kwd">fi</span> </span><span class="nv"><span class="pln">data</span></span><span class="o"><span class="pun">=</span></span><span class="nv"><span class="pln">$1</span></span> <span class="k"><span class="kwd">while</span> </span><span class="nb"><span class="pln">read </span></span><span class="pln">line</span><span class="pun">;</span> <span class="k"><span class="kwd">do</span></span> <span class="c"><span class="com"># query=`echo $line | awk -F, '{ printf("%s,\"%s\",%s,\"%s\"", $1, $2, $3, $4)}'`</span></span> <span class="nv"><span class="pln">oldIFS</span></span><span class="o"><span class="pun">=</span></span><span class="nv"><span class="pln">$IFS</span></span> <span class="nv"><span class="pln">IFS</span></span><span class="o"><span class="pun">=</span></span><span class="pun">,</span> <span class="nv"><span class="pln">values</span></span><span class="o"><span class="pun">=(</span></span><span class="nv"><span class="pln">$line</span></span><span class="o"><span class="pun">)</span></span><span class="pln"> values</span><span class="pun">[</span><span class="lit">1</span><span class="pun">]</span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"</span></span><span class="se"><span class="str">\"</span></span><span class="sb"><span class="str">`</span></span><span class="nb"><span class="str">echo</span></span> <span class="k"><span class="str">${</span></span><span class="nv"><span class="str">values</span></span><span class="p"><span class="str">[1]</span></span><span class="k"><span class="str">}</span></span><span class="str"> | tr </span><span class="s1"><span class="str">' '</span></span> <span class="s1"><span class="str">'#'</span></span> <span class="sb"><span class="str">`</span></span><span class="se"><span class="str">\"</span></span><span class="s2"><span class="str">"</span></span><span class="pln"> values</span><span class="pun">[</span><span class="lit">3</span><span class="pun">]</span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"</span></span><span class="se"><span class="str">\"</span></span><span class="sb"><span class="str">`</span></span><span class="nb"><span class="str">echo</span></span> <span class="k"><span class="str">${</span></span><span class="nv"><span class="str">values</span></span><span class="p"><span class="str">[3]</span></span><span class="k"><span class="str">}</span></span><span class="sb"><span class="str">`</span></span><span class="se"><span class="str">\"</span></span><span class="s2"><span class="str">"</span></span> <span class="nv"><span class="pln">query</span></span><span class="o"><span class="pun">=</span></span><span class="sb"><span class="str">`</span></span><span class="nb"><span class="str">echo</span></span> <span class="k"><span class="str">${</span></span><span class="nv"><span class="str">values</span></span><span class="p"><span class="str">[@]</span></span><span class="k"><span class="str">}</span></span><span class="str"> | tr </span><span class="s1"><span class="str">' #'</span></span> <span class="s1"><span class="str">', '</span></span><span class="sb"><span class="str">`</span></span> <span class="nv"><span class="pln">IFS</span></span><span class="o"><span class="pun">=</span></span><span class="nv"><span class="pln">$oldIFS</span></span> <span class="nv"><span class="pln">statement</span></span><span class="o"><span class="pun">=</span></span><span class="sb"><span class="str">`</span></span><span class="nb"><span class="str">echo</span></span> <span class="s2"><span class="str">"INSERT INTO </span></span><span class="nv"><span class="str">$TABLE</span></span><span class="s2"><span class="str"> VALUES(</span></span><span class="nv"><span class="str">$query</span></span><span class="s2"><span class="str">);"</span></span><span class="sb"><span class="str">`</span></span> <span class="c"><span class="com"># echo $statement</span></span><span class="pln"> mysql </span><span class="pun">-</span><span class="pln">u </span><span class="nv"><span class="pln">$USER</span></span> <span class="nv"><span class="pln">$DATABASE</span></span> <span class="sh"><span class="pun"><<</span><span class="pln"> EOF INSERT INTO $TABLE VALUES</span><span class="pun">(</span><span class="pln">$query</span><span class="pun">);</span><span class="pln"> EOF </span></span><span class="k"><span class="kwd">done</span></span> <span class="pun"><</span> <span class="nv"><span class="pln">$data</span></span> <span class="k"><span class="kwd">if</span></span> <span class="o"><span class="pun">[[</span></span> <span class="nv"><span class="pln">$</span><span class="pun">?</span></span> <span class="pun">-</span><span class="pln">eq </span><span class="lit">0</span> <span class="o"><span class="pun">]]</span></span><span class="pun">;</span> <span class="k"><span class="kwd">then</span> </span><span class="nb"><span class="pln">echo</span></span> <span class="s2"><span class="str">"Wrote data into DB"</span></span> <span class="k"><span class="kwd">fi</span></span> |
#4. 读取数据
知道怎么在shell 中连接mysql ,也知道了怎么在shell中批量执行sql 语句,读取数据,就没有任何难度了。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
<span class="c"><span class="com">#!/bin/bash</span></span> <span class="c"><span class="com">#</span></span> <span class="c"><span class="com"># @file read_db_mysql.sh</span></span> <span class="c"><span class="com"># @brief read data from mysql</span></span> <span class="c"><span class="com"># @author Mingxing LAI</span></span> <span class="c"><span class="com"># @version 0.1</span></span> <span class="c"><span class="com"># @date 2013-01-20</span></span> <span class="c"><span class="com">#</span></span> <span class="nv"><span class="pln">USER</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"root"</span></span> <span class="nv"><span class="pln">DATABASE</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"students"</span></span> <span class="nv"><span class="pln">TABLE</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"students"</span></span> <span class="c"><span class="com">#用tail 去掉表头</span></span> <span class="nv"><span class="pln">depts</span></span><span class="o"><span class="pun">=</span></span><span class="sb"><span class="str">`</span></span><span class="str">mysql -u </span><span class="nv"><span class="str">$USER</span></span> <span class="nv"><span class="str">$DATABASE</span></span> <span class="sh"><span class="str"><<EOF | tail -n +2 SELECT DISTINCT dept FROM $TABLE; EOF`</span> <span class="kwd">for</span><span class="pln"> d </span><span class="kwd">in</span><span class="pln"> $depts</span><span class="pun">;</span> <span class="kwd">do</span><span class="pln"> echo </span><span class="typ">Department</span><span class="pun">:</span><span class="pln"> $d result</span><span class="pun">=</span><span class="str">"`mysql -u $USER $DATABASE << EOF set @i:=0; SELECT @i:=@i+1 as rank, name, mark FROM students WHERE dept="</span><span class="pln">$d</span><span class="str">" ORDER BY mark DESC; EOF`"</span><span class="pln"> echo </span><span class="str">"$result"</span><span class="pln"> echo </span><span class="kwd">done</span></span> |
我们还可以在mysql语句中,使用选项来控制数据的输出格式
- -H 输出为html
- -X 输出为xml
如下所示:
1 2 3 4 5 6 7 8 9 |
<span class="c"><span class="com">#!/bin/bash</span></span> <span class="nv"><span class="pln">USER</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"root"</span></span> <span class="nv"><span class="pln">DATABASE</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"students"</span></span> <span class="nv"><span class="pln">TABLE</span></span><span class="o"><span class="pun">=</span></span><span class="s2"><span class="str">"students"</span></span><span class="pln"> mysql </span><span class="pun">-</span><span class="pln">u </span><span class="nv"><span class="pln">$USER</span></span> <span class="nv"><span class="pln">$DATABASE</span></span> <span class="pun">-</span><span class="pln">H </span><span class="sh"><span class="pun"><<</span><span class="pln"> EOF </span><span class="kwd">select</span> <span class="pun">*</span> <span class="kwd">from</span><span class="pln"> $TABLE EOF</span></span> |
####html 格式的可读性比较差,输出效果如下:
1 |
<span class="tag"><TABLE</span> <span class="atn">BORDER</span><span class="pun">=</span><span class="atv">1</span><span class="tag">><TR><TH></span><span class="pln">id</span><span class="tag"></TH><TH></span><span class="pln">name</span><span class="tag"></TH><TH></span><span class="pln">mark</span><span class="tag"></TH><TH></span><span class="pln">dept</span><span class="tag"></TH></TR><TR><TD></span><span class="pln">1</span><span class="tag"></TD><TD></span><span class="pln">Navin M</span><span class="tag"></TD><TD></span><span class="pln">98</span><span class="tag"></TD><TD></span><span class="pln">CS</span><span class="tag"></TD></TR><TR><TD></span><span class="pln">2</span><span class="tag"></TD><TD></span><span class="pln"> Kavya N</span><span class="tag"></TD><TD></span><span class="pln">70</span><span class="tag"></TD><TD></span><span class="pln">CS</span><span class="tag"></TD></TR><TR><TD></span><span class="pln">3</span><span class="tag"></TD><TD></span><span class="pln"> Nawaz O</span><span class="tag"></TD><TD></span><span class="pln">80</span><span class="tag"></TD><TD></span><span class="pln">CS</span><span class="tag"></TD></TR><TR><TD></span><span class="pln">4</span><span class="tag"></TD><TD></span><span class="pln">Hari S</span><span class="tag"></TD><TD></span><span class="pln">80</span><span class="tag"></TD><TD></span><span class="pln">EC</span><span class="tag"></TD></TR><TR><TD></span><span class="pln">5</span><span class="tag"></TD><TD></span><span class="pln">Alex M</span><span class="tag"></TD><TD></span><span class="pln">50</span><span class="tag"></TD><TD></span><span class="pln">EC</span><span class="tag"></TD></TR><TR><TD></span><span class="pln">6</span><span class="tag"></TD><TD></span><span class="pln">Neenu J</span><span class="tag"></TD><TD></span><span class="pln">70</span><span class="tag"></TD><TD></span><span class="pln">EC</span><span class="tag"></TD></TR><TR><TD></span><span class="pln">7</span><span class="tag"></TD><TD></span><span class="pln">Bob A</span><span class="tag"></TD><TD></span><span class="pln">30</span><span class="tag"></TD><TD></span><span class="pln">EC</span><span class="tag"></TD></TR><TR><TD></span><span class="pln">8</span><span class="tag"></TD><TD></span><span class="pln">Anu M</span><span class="tag"></TD><TD></span><span class="pln">90</span><span class="tag"></TD><TD></span><span class="pln">AE</span><span class="tag"></TD></TR><TR><TD></span><span class="pln">9</span><span class="tag"></TD><TD></span><span class="pln">Sruthi</span><span class="tag"></TD><TD></span><span class="pln">89</span><span class="tag"></TD><TD></span><span class="pln">AE</span><span class="tag"></TD></TR><TR><TD></span><span class="pln">10</span><span class="tag"></TD><TD></span><span class="pln">Andrew</span><span class="tag"></TD><TD></span><span class="pln">89</span><span class="tag"></TD><TD></span><span class="pln">AE</span><span class="tag"></TD></TR></TABLE></span> |
可读性差也可以理解,因为人家觉得,你没必要修改么,直接以html形式展示数据就可以了。 <TABLE BORDER=1><TR><TH>id</TH><TH>name</TH><TH>mark</TH><TH>dept</TH></TR><TR><TD>1</TD><TD>Navin M</TD><TD>98</TD><TD>CS</TD></TR><TR><TD>2</TD><TD> Kavya N</TD><TD>70</TD><TD>CS</TD></TR><TR><TD>3</TD><TD> Nawaz O</TD><TD>80</TD><TD>CS</TD></TR><TR><TD>4</TD><TD>Hari S</TD><TD>80</TD><TD>EC</TD></TR><TR><TD>5</TD><TD>Alex M</TD><TD>50</TD><TD>EC</TD></TR><TR><TD>6</TD><TD>Neenu J</TD><TD>70</TD><TD>EC</TD></TR><TR><TD>7</TD><TD>Bob A</TD><TD>30</TD><TD>EC</TD></TR><TR><TD>8</TD><TD>Anu M</TD><TD>90</TD><TD>AE</TD></TR><TR><TD>9</TD><TD>Sruthi</TD><TD>89</TD><TD>AE</TD></TR><TR><TD>10</TD><TD>Andrew</TD><TD>89</TD><TD>AE</TD></TR></TABLE>
xml形式的数据显示就比较正常了,直接将上面的-H 换成-X,输出如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<span class="cp"><span class="pun"><?</span><span class="pln">xml version</span><span class="pun">=</span><span class="str">"1.0"</span><span class="pun">?></span></span> <span class="nt"><span class="tag"><resultset</span></span> <span class="na"><span class="atn">statement</span><span class="pun">=</span></span><span class="s"><span class="atv">"select</span></span> <span class="err"><span class="atv">*</span></span> <span class="err"><span class="atv">from</span></span> <span class="err"><span class="atv">students</span></span> <span class="err"><span class="atv">"</span></span> <span class="na"><span class="atn">xmlns:xsi</span><span class="pun">=</span></span><span class="s"><span class="atv">"http://www.w3.org/2001/XMLSchema-instance"</span></span><span class="nt"><span class="tag">></span></span> <span class="nt"><span class="tag"><row></span></span> <span class="nt"><span class="tag"><field</span></span> <span class="na"><span class="atn">name</span><span class="pun">=</span></span><span class="s"><span class="atv">"id"</span></span><span class="nt"><span class="tag">></span></span><span class="pln">1</span><span class="nt"><span class="tag"></field></span></span> <span class="nt"><span class="tag"><field</span></span> <span class="na"><span class="atn">name</span><span class="pun">=</span></span><span class="s"><span class="atv">"name"</span></span><span class="nt"><span class="tag">></span></span><span class="pln">Navin M</span><span class="nt"><span class="tag"></field></span></span> <span class="nt"><span class="tag"><field</span></span> <span class="na"><span class="atn">name</span><span class="pun">=</span></span><span class="s"><span class="atv">"mark"</span></span><span class="nt"><span class="tag">></span></span><span class="pln">98</span><span class="nt"><span class="tag"></field></span></span> <span class="nt"><span class="tag"><field</span></span> <span class="na"><span class="atn">name</span><span class="pun">=</span></span><span class="s"><span class="atv">"dept"</span></span><span class="nt"><span class="tag">></span></span><span class="pln">CS</span><span class="nt"><span class="tag"></field></span></span> <span class="nt"><span class="tag"></row></span></span> <span class="nt"><span class="tag"><row></span></span> <span class="nt"><span class="tag"><field</span></span> <span class="na"><span class="atn">name</span><span class="pun">=</span></span><span class="s"><span class="atv">"id"</span></span><span class="nt"><span class="tag">></span></span><span class="pln">2</span><span class="nt"><span class="tag"></field></span></span> <span class="nt"><span class="tag"><field</span></span> <span class="na"><span class="atn">name</span><span class="pun">=</span></span><span class="s"><span class="atv">"name"</span></span><span class="nt"><span class="tag">></span></span><span class="pln"> Kavya N</span><span class="nt"><span class="tag"></field></span></span> <span class="nt"><span class="tag"><field</span></span> <span class="na"><span class="atn">name</span><span class="pun">=</span></span><span class="s"><span class="atv">"mark"</span></span><span class="nt"><span class="tag">></span></span><span class="pln">70</span><span class="nt"><span class="tag"></field></span></span> <span class="nt"><span class="tag"><field</span></span> <span class="na"><span class="atn">name</span><span class="pun">=</span></span><span class="s"><span class="atv">"dept"</span></span><span class="nt"><span class="tag">></span></span><span class="pln">CS</span><span class="nt"><span class="tag"></field></span></span> <span class="nt"><span class="tag"></row></span></span> <span class="nt"><span class="tag"></resultset></span></span> |
完。
