第七章导入和导出精选文档.ppt

第七章导入和导出本讲稿第一页，共四十二页本章重点q掌握数据导入和导出的方法q不同方法之间的区别本讲稿第二页，共四十二页数据导出方法q方法一：使用selectintooutfilefilename语句q方法二：使用mysqldump实用程序本讲稿第三页，共四十二页两种方法的区别：使用select方法输出的文件只有数据，没有表结构，而且一次只能处理一个表，要处理多个表则不是很容易，不过可以将select命令写入一个sql文件，然后在命令行下执行即可在(mysqldump命令的讲解中可以看到如何执行这个sql文件)；mysqldump可以导出纯数据文本或只导出含有创建表结构而没有数据的sql文件，同时可以对库中的多个表进行操作，比select的方法灵活，强大的多。select适合利用程序进行处理，而mysqldump则为手工操作，同时提供强大的导出功能，并且可以处理整个库，或库中指定的多表。4本讲稿第四页，共四十二页1使用selectintooutfile语句q只能导出表的数据，不能导出表结构q语法SELECT*FROMTABLE_NAMEINTOOUTFILEfilenameq要求：用户拥有file的权限5本讲稿第五页，共四十二页q使用样例：mysqlusecookbook;DatabaseChangedmysqlselect*fromstu_infointooutfilec:/a.txt;QueryOK,12rowsaffected(0.05sec)这样就把stu_info表的数据导在了a.txt文件里面6本讲稿第六页，共四十二页指定分隔符q使用select命令还可以指定导出文件时，字段之间的分隔字符，转义字符，包括字符，及记录行分隔字符。FIELDSTERMINATEDBY字段分割字符，默认为tOPTIONALLYENCLOSEDBY字段用什么字符包括，默认为无,如果使用了OPTIONALLY，则只有CHAR和VERCHAR被包括；ESCAPEDBY转义字符，默认为LINESTERMINATEDBY行分隔符，默认无7本讲稿第七页，共四十二页实例qmysqlselect*fromstu_infointooutfilec:/a1.txtfieldsterminatedby,enclosedby“;QueryOK,12rowsaffected(0.06sec)结果可能如下：200801001,杨振军,1,N,男200801002,白云,1,N,女200801003,吴楠,2,N,女q看到每个字段都用,进行了分隔，且每个字段都用”包括了起来。注意，行记录分隔符可以是一个字符串，请大家自行测试。不过，如果输出文件在指定目录下如果存在的话就会报错，先删除再测试即可。8本讲稿第八页，共四十二页2、使用mysqldump实用程序先举个简单的例子C:Userszpmysqldump-uroot-pcookbookd:a.sqlEnterpassword:可能结果如下：-MySQLdump10.13Distrib5.5.9,forWin32(x86)-Host:localhostDatabase:cookbook-Serverversion5.5.9/*!40101SETOLD_CHARACTER_SET_CLIENT=CHARACTER_SET_CLIENT*/;/*!40101SETOLD_CHARACTER_SET_RESULTS=CHARACTER_SET_RESULTS*/;/*!40101SETOLD_COLLATION_CONNECTION=COLLATION_CONNECTION*/;/*!40101SETNAMESutf8*/;/*!40103SETOLD_TIME_ZONE=TIME_ZONE*/;/*!40103SETTIME_ZONE=+00:00*/;9本讲稿第九页，共四十二页如何解决乱码qshowvariableslike%char%;qcharacter-set-databasegb2312q在导出语句中在cookbook之前增加default-character-set=gb2312本讲稿第十页，共四十二页字符校对q服务器字符集和校对MySQL服务器有一个服务器字符集和一个服务器校对规则，它们均不能设置为空MySQL按照如下方法确定服务器字符集和服务器校对规则：当服务器启动时根据有效的选项设置根据运行时的设定值q在服务器级别，确定方法很简单。当启动mysqld时，根据使用的初始选项设置来确定服务器字符集和校对规则。可以使用-default-character-set设置字符集，并且可以在字符集后面为校对规则添加-default-collation。如果没有指定一个字符集，那就与-default-character-set=latin1相同。如果你仅指定了一个字符集（例如，latin1），但是没有指定一个校对规则，那就与-default-charset=latin1-default-collation=latin1_swedish_ci相同，因为latin1_swedish_ci是latin1的默认校对规则。因此，以下三个命令有相同的效果：shellmysqldshellmysqld-default-character-set=latin1shellmysqld-default-character-set=latin1-default-collation=latin1_swedish_ci本讲稿第十一页，共四十二页q数据库字符集和校对每一个数据库有一个数据库字符集和一个数据库校对规则，它不能够为空。CREATEDATABASE和ALTERDATABASE语句有一个可选的子句来指定数据库字符集和校对规则：本讲稿第十二页，共四十二页q表字符集和校对每一个表有一个表字符集和一个校对规则，它不为空。为指定表字符集和校对规则，CREATETABLE和ALTERTABLE语句有一个可选的子句：CREATETABLEtbl_name(column_list)DEFAULTCHARACTERSETcharset_nameCOLLATEcollation_name本讲稿第十三页，共四十二页q列字符集和校对每一个“字符”列（即，CHAR、VARCHAR或TEXT类型的列）有一个列字符集和一个列校对规则，它不能为空。列定义语法有一个可选子句来指定列字符集和校对规则：col_nameCHAR|VARCHAR|TEXT(col_length)CHARACTERSETcharset_nameCOLLATEcollation_name例如：CREATETABLETable1(column1VARCHAR(5)CHARACTERSETlatin1COLLATElatin1_german1_ci);MySQL按照下面的方式选择列字符集和校对规则：1.如果指定了CHARACTERSETX和COLLATEY，那么采用CHARACTERSETX和COLLATEY。2.如果指定了CHARACTERSETX而没有指定COLLATEY，那么采用CHARACTERSETX和CHARACTERSETX的默认校对规则。3.否则，采用表字符集和服务器校对规则。CHARACTERSET和COLLATE子句是标准的SQL。本讲稿第十四页，共四十二页q不同的编码格式会导致同一字符，在不同字符集下的编码会不同。同样同一编码在不同的字符集中代码的字符也不相同。当你的MySQL返回的字符串的编码格式（字符集）与你的客户工具程序（mysql,php,querybrowser,.）当前使用的字符集不同时，就会造成乱码。比如一个英国朋友告诉你Long,当一位中国小学生看到后就会告诉你“龙”而不是“长”本讲稿第十五页，共四十二页qMySQL中默认字符集的设置有四级:服务器级，数据库级，表级。最终是字段级的字符集设置。注意前三种均为默认设置，并不代码你的字段最终会使用这个字符集设置。所以我们建议要用showcreatetabletable;或showfullfieldsfromtableName;来检查当前表中字段的字符集设置。qMySQL中关于连接环境的字符集设置有Client端，connection,results通过这些参数，MySQL就知道你的客户端工具用的是什么字符集，结果集应该是什么字符集。这样MySQL就会做必要的翻译，一旦这些参数有误，自然会导致字符串在转输过程中的转换错误。基本上99%的乱码由些造成。本讲稿第十六页，共四十二页乱码后需要检查的信息q1数据库表中字段的字符集设置。showcreatetableTableName或showfullcolumnsfromtableNamemysqlshowcreatetablestu_info;CREATETABLEstu_info(idvarchar(20)NOTNULLDEFAULT,namevarchar(20)DEFAULTNULL,classvarchar(10)DEFAULTNULL,birth_placeint(11)DEFAULTNULL,sexchar(2)DEFAULTNULL,PRIMARYKEY(id)ENGINE=InnoDBDEFAULTCHARSET=gb2312本讲稿第十七页，共四十二页qshowfullcolumnsfromstu_info;本讲稿第十八页，共四十二页q2当前联接系统参数qshowvariableslikechar%+-+-|Variable_name|Value+-+-|character_set_client|utf8|character_set_connection|utf8|character_set_database|gb2312|character_set_filesystem|binary|character_set_results|utf8|character_set_server|latin1|character_set_system|utf8|character_sets_dir|C:ProgramFile+-+-本讲稿第十九页，共四十二页q中文，请确保表中该字段的字符集为中文兼容：big5|Big5TraditionalChinesegb2312|GB2312SimplifiedChinesegbk|GBKSimplifiedChineseutf8|UTF-8Unicodeq确保，联接参数与这个字段字符集一致本讲稿第二十页，共四十二页qMySQLdump工具很多方面类似相反作用的工具MySQLimport。它们有一些同样的选项。但MySQLdump能够做更多的事情。它可以把整个数据库装载到一个单独的文本文件中。这个文件包含有所有重建您的数据库所需要的SQL命令。这个命令取得所有的模式（Schema，后面有解释）并且将其转换成DDL语法（CREATE语句，即数据库定义语句），取得所有的数据，并且从这些数据中创建INSERT语句。这个工具将您的数据库中所有的设计倒转。因为所有的东西都被包含到了一个文本文件中。这个文本文件可以用一个简单的批处理和一个合适SQL语句导回到MySQL中。这个工具令人难以置信地简单而快速。决不会有半点让人头疼地地方。21本讲稿第二十一页，共四十二页q导出创建表结构和插入数据的SQL语句语法：mysqldump连接信息数据库表1表2路径文件名.sql如:mysqldumpurootpcookbookc:cookbook.sqlq只导出创建表结构的SQL语句语法：mysqldump连接信息d数据库表1表2路径文件名.sql如：mysqldumpurootpdcookbookstu_infof:backupstu_info.sqlq导出表的SQL语句语法：mysqldump连接信息数据库表1表2路径文件名.sql如：mysqldumpurootpcookbookstu_infof:backupstu_info.sql22本讲稿第二十二页，共四十二页q这个非常的灵活，您甚至可以使用WHERE从句来选择您需要的记录导出到文件中。要达到这样的目的，可以使用类似于下面的命令：MySQLdumpurootpwID200801001cookbookstu_infof:backupstuinfo001.txt23本讲稿第二十三页，共四十二页q只导出插入数据的SQL语句语法：mysqldump连接信息t数据库表1表2路径文件名.sql如：mysqldumpuroot-ptcookbookstu_gradef:backupstu_grade.sqlq单独生成文本文件和SQL文件语法：mysqldump连接信息-T本地路径数据库名表1表2如：mysqldumpurootpTf:backupcookbookstu_infostu_grade本讲稿第二十四页，共四十二页l只有指定了-T参数才可以导出纯文本文件，表示导出数据的目录，./表示当前目录，即与执行命令时处于同一目录。如果不指定表，则将导出整个数据库的数据。每个表会生成两个文件，一个为.sql文件，包含建表执行。另一个为.txt文件，只包含数据，且没有sql指令。25本讲稿第二十五页，共四十二页q补充:mysqldump支持下列选项：-add-locks在每个表导出之前增加LOCKTABLES并且之后UNLOCKTABLE。(为了使得更快地插入到MySQL)。-add-drop-table在每个create语句之前增加一个droptable。-allow-keywords允许创建是关键词的列名字。这由在列名前面加表名的方法做到。-c,-complete-insert使用完整的insert语句(用列名字)。-C,-compress如果客户和服务器均支持压缩，压缩两者间所有的信息。-delayed用INSERTDELAYED命令插入行。-e,-extended-insert使用全新多行INSERT语法。（给出更紧缩并且更快的插入语句）-#,-debug=option_string跟踪程序的使用(为了调试)。26本讲稿第二十六页，共四十二页q-help显示一条帮助消息并且退出。-fields-terminated-by=.-fields-enclosed-by=.-fields-optionally-enclosed-by=.-fields-escaped-by=.-lines-terminated-by=.这些选择与-T选择一起使用，并且有相应的LOADDATAINFILE子句相同的含义。LOADDATAINFILE语法。-F,-flush-logs在开始导出前，洗掉在MySQL服务器中的日志日志文件。-f,-force,即使我们在一个表导出期间得到一个SQL错误，继续。-h,-host=.从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。-l,-lock-tables.为开始导出锁定所有表。-t,-no-create-info不写入表创建信息(CREATETABLE语句）本讲稿第二十七页，共四十二页q-d,-no-data不写入表的任何行信息。如果你只想得到一个表的结构的导出，这是很有用的！-opt同-quick-add-drop-table-add-locks-extended-insert-lock-tables。应该给你为读入一个MySQL服务器的尽可能最快的导出。-pyour_pass,-password=your_pass与服务器连接时使用的口令。如果你不指定“=your_pass”部分，mysqldump需要来自终端的口令。-Pport_num,-port=port_num与一台主机连接时使用的TCP/IP端口号。（这用于连接到localhost以外的主机，因为它使用Unix套接字。）-q,-quick不缓冲查询，直接导出至stdout；使用mysql_use_result()做它。-S/path/to/socket,-socket=/path/to/socket与localhost连接时（它是缺省主机)使用的套接字文件。本讲稿第二十八页，共四十二页q-T,-tab=path-to-some-directory对于每个给定的表，创建一个table_name.sql文件，它包含SQLCREATE命令，和一个table_name.txt文件，它包含数据。注意：这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作工作。.txt文件的格式根据-fields-xxx和-lines-xxx选项来定。-uuser_name,-user=user_name与服务器连接时，MySQL使用的用户名。缺省值是你的登录名。-Ovar=option,-set-variablevar=option设置一个变量的值。可能的变量被列在下面。-v,-verbose冗长模式。打印出程序所做的更多的信息。-V,-version打印版本信息并且退出。-w,-where=where-condition只导出被选择了的记录；注意引号是强制的！-where=user=jimf-wuserid1-wuserid1本讲稿第二十九页，共四十二页二数据导入q使用LOADDATAINFILEfilename命令q使用mysqlimport实用程序q使用mysql命令30本讲稿第三十页，共四十二页1、使用load命令qloaddatainfilec:/a1.txtintotablestu_infofieldsterminatedby,enclosedby;可以带的参数如下：FIELDSTERMINATEDBYOPTIONALLYENCLOSEDBYESCAPEDBYLINESTERMINATEDBYFIELDSTERMINATED表示字段分隔；OPTIONALLYENCLOSED表示字段用什么字符包括起来，如果使用了OPTIONALLY，则只有CHAR和VERCHAR被包括；ESCAPED表示当需要转义时用什么作为转义字符；LINESTERMINATED表示每行记录之间用什么分隔。上面列的是缺省值，而且这些项都是可选的，不选则使用缺省值。可以根据需要进行修改。31本讲稿第三十一页，共四十二页q您也可以设置插入语句的优先级，如果您要把它标记为低优先级（LOW_PRIORITY），那么MySQL将会等到没有其他人读这个表的时候，才把插入数据。可以使用如下的命令：LOADDATALOW_PRIORITYINFILE“c:/1.txtINTOTABLEstu_grade;q您也可以指定是否在插入数据的时候，取代或者忽略文件与数据表中重复的键值。替代重复的键值的语法：qLOADDATALOW_PRIORITYINFILE“c:/1.txtREPLACEINTOTABLEstu_info;q上面的句子看起来有点笨拙，但却把关键字放在了让您的剖析器可以理解的地方。32本讲稿第三十二页，共四十二页qTERMINATEDBY描述字段的分隔符，默认情况下是tab字符（t）ENCLOSEDBY描述的是字段的括起字符。比方以引号括起每一个字段。ESCAPEDBY描述的转义字符。默认的是反些杠（backslash：）.33本讲稿第三十三页，共四十二页q使用前面的MySQLimport命令的例子，用LOADDATAINFILE语句把同样的文件导入到数据库中:qselect*fromstu_gradeintooutfilec:/2.txtfieldsterminatedby,enclosedby;qLOADDATAINFILE“c:/2.txtREPLACEINTOTABLEstu_infoFIELDSTERMINATEDBY,ENCLOSEDBY;qLOADDATAINFILE语句中有一个MySQLimport工具中没有特点：qLOADDATAINFILE可以按指定的列把文件导入到数据库中。34本讲稿第三十四页，共四十二页q当我们要把数据的一部分内容导入的时候，这个特点就很重要。比方说，我们要从Access数据库升级到MySQL数据库的时候，需要加入一些栏目（列/字段/field）到MySQL数据库中，以适应一些额外的需要。q这个时候，我们的Access数据库中的数据仍然是可用的，但是因为这些数据的栏目(field)与MySQL中的不再匹配，因此而无法再使用MySQLimport工具。尽管如此，我们仍然可以使用LOADDATAINFILE，下面的例子显示了如何向指定的栏目(field)中导入数据：qCreatetablestud_gradeasselect*fromstu_gradewhere1=2;qAltertablestud_gradeaddcolumnmemovarchar(45);qLOADDATAINFILE“c:/1.txtINTOTABLEstud_grade(id,subject_id,score,semester);35本讲稿第三十五页，共四十二页2、使用mysqlimport实用程序qMySQLimport位于MySQL/bin目录中，是MySQL的一个载入（或者说导入）数据的一个非常有效的工具。这是一个命令行工具。有两个参数以及大量的选项可供选择。这个工具把一个文本文件（textfile）导入到你指定的数据库和表中。比方说我们要从文件stu_info.txt中把数据导入到数据库cookbook中的表stu_info中,表名即为文件名：qMySQLimporturootpcookbookc:stu_info.txt表名36本讲稿第三十六页，共四十二页.MySQLimport的常用选项介绍:q-dor-delete新数据导入数据表中之前删除数据数据表中的所有信息-for-force不管是否遇到错误，MySQLimport将强制继续插入数据-ior-ignoreMySQLimport跳过或者忽略那些有相同唯一关键字的行，导入文件中的数据将被忽略。-lor-lock-tables数据被插入之前锁住表，这样就防止了，你在更新数据库时，用户的查询和更新受到影响。-ror-replace这个选项与i选项的作用相反；此选项将替代表中有相同唯一关键字的记录。-fields-enclosed-by=char指定文本文件中数据的记录时以什么括起的，很多情况下数据以双引号括起。默认的情况下数据是没有被字符括起的。q-fields-terminated-by=char指定各个数据的值之间的分隔符，在句号分隔的文件中，分隔符是句号。您可以用此选项指定数据之间的分隔符。37本讲稿第三十七页，共四十二页默认的分隔符是跳格符（默认的分隔符是跳格符（Tab）q-lines-terminated-by=str此选项指定文本文件中行与行之间数据的分隔字符串或者字符。默认的情况下MySQLimport以newline为行分隔符。q您可以选择用一个字符串来替代一个单个的字符：q一个新行或者一个回车。qMySQLimport命令常用的选项还有-v显示版本（version），-p提示输入密码（password）等。q*重点3）.例子：导入一个以逗号为分隔符的文件q文件中行的记录格式是这样的：q“200801001”,“1”,“78.00”,“2011年春季q我们的任务是要把这个文件里面的数据导入到数据库cookbook中的表格stu_grade中，我们使用这个命令:qMysqlimportfields-terminated-by=,-fields-enclosed-by=”urootpzhangzgcookbookc:stu_grade.txtq这个命令可能看起来很不爽，不过当你熟悉了之后，这是非常简单的。38本讲稿第三十八页，共四十二页3、使用mysql命令qmysql-uroot-pmysqld:/a.sql39本讲稿第三十九页，共四十二页导入整个数据库qCreatedatabasetest3qC:mysqlhlocalhosturootptest3c:cookbook.sqlq导入步骤q1创建数据库q2创建表，删除触发器q3导入数据q4重建触发器和存储过程，函数等本讲稿第四十页，共四十二页作业q导出完整的cookbook数据库，分别为创建表结构和数据q创建新的数据库phpmysql,将cookbook中的内容导入本讲稿第四十一页，共四十二页君子终日乾乾，夕惕若，厉君子终日乾乾，夕惕若，厉,无咎无咎或跃在渊，无咎或跃在渊，无咎本讲稿第四十二页，共四十二页