MySQL数据表格导入导出

二 创建数据库、创建表user

<span style="color: #0000ff;">create</span> <span style="color: #0000ff;">database</span> youku;   <span style="color: #008080;">--</span><span style="color: #008080;">创建数据库</span>
<span style="color: #0000ff;">use</span> youku;  <span style="color: #008080;">--</span><span style="color: #008080;"> 选择数据库</span>
<span style="color: #0000ff;">DROP</span> <span style="color: #0000ff;">TABLE</span> <span style="color: #0000ff;">IF</span> <span style="color: #808080;">EXISTS</span> `<span style="color: #ff00ff;">user</span><span style="color: #000000;">`;
</span><span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span> `<span style="color: #ff00ff;">user</span><span style="color: #000000;">` (
  `username` </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #0000ff;">DEFAULT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">,
  `userpwd` </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #0000ff;">DEFAULT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">,
  `age` </span><span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #0000ff;">DEFAULT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">
) ENGINE</span><span style="color: #808080;">=</span>InnoDB <span style="color: #0000ff;">DEFAULT</span> CHARSET<span style="color: #808080;">=</span><span style="color: #000000;">utf8;
 </span><span style="color: #808080;">//</span><span style="color: #000000;">插入数据
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `<span style="color: #ff00ff;">user</span>` <span style="color: #0000ff;">VALUES</span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">admin</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">admin</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">18</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">root</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">root</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">18</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">cat</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">cat</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">17</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">dog</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">dog</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">15</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">dog1</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">dog1</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">15</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">zhu</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">zhu</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">21</span><span style="color: #000000;">);
</span><span style="color: #008080;">--</span><span style="color: #008080;"> 表数据导出</span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> <span style="color: #ff00ff;">user</span>
    <span style="color: #0000ff;">INTO</span> OUTFILE <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">F:/user.txt</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
    
</span><span style="color: #008080;">--</span><span style="color: #008080;"> 根据需要设置输出的格式,每一行数据‘,‘分隔,同时字符串型的数据用双引号""包含</span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">INTO</span> OUTFILE <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">F:/user1.txt</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
    Fields TERMINATED </span><span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> OPTIONALLY ENCLOSED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">"</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
    
</span><span style="color: #008080;">--</span><span style="color: #008080;"> 表数据导入 (注意,导入的数据的列数要对应表的各列)</span>
<span style="color: #008080;">--</span><span style="color: #008080;"> 先删除数据</span>
<span style="color: #0000ff;">use</span><span style="color: #000000;"> youku;
</span><span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">where</span> age<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">LOAD</span> DATA INFILE <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">F:/user.txt</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">INTO</span> <span style="color: #0000ff;">TABLE</span> <span style="color: #ff00ff;">user</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span><span style="color: #000000;">;
 
</span><span style="color: #008080;">--</span><span style="color: #008080;"> 导入有特殊格式的表user1.txt(去除分隔号‘,‘和字串的引号“”)</span>
<span style="color: #0000ff;">use</span><span style="color: #000000;"> youku;
</span><span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">where</span> age<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">LOAD</span> DATA INFILE <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">F:/user1.txt</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">INTO</span> <span style="color: #0000ff;">TABLE</span> <span style="color: #ff00ff;">user</span><span style="color: #000000;">
Fields TERMINATED </span><span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> OPTIONALLY ENCLOSED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">"</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">; 
</span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span>;

=================

 三 txt文件查看

<span style="color: #008080;">--</span><span style="color: #008080;"> 表数据导出</span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> <span style="color: #ff00ff;">user</span>
    <span style="color: #0000ff;">INTO</span> OUTFILE <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">F:/user.txt</span><span style="color: #ff0000;">‘</span>;

 生成文件user.txt

admin admin 18

root root 18

cat cat 17

dog dog 15

dog1 dog1 15

zhu zhu 21

<span style="color: #008080;">--</span><span style="color: #008080;"> 根据需要设置输出的格式,每一行数据‘,‘分隔,同时字符串型的数据用双引号""包含</span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">INTO</span> OUTFILE <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">F:/user1.txt</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
    Fields TERMINATED </span><span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> OPTIONALLY ENCLOSED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">"</span><span style="color: #ff0000;">‘</span>;

生成文件 user1.txt

"admin","admin",18

"root","root",18

"cat","cat",17

"dog","dog",15

"dog1","dog1",15

"zhu","zhu",21

结语：数据的具体导入导出格式大家可以自己尝试一下。但个人建议涉及格式转换的话尽量先用其他工具，为防止出错还是尽量只转换纯文本列数据（无特殊格式）。

MySQL数据表格导入导出

标签：