Navicat for Mysql多表导出,方便特殊需求

打开需要导出的数据库

选择需要导出的表，右键选择导出sql文件

选择保存路径和保存的文件名

打开刚才保存的文件，里面有需要导出的表及数据

如果单表，按照多表操作一样，选择一张表进行导出

导出整个数据库，直接选择表右键进行导出

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement. 对于上述错误，相信对于第一次执行 MySQL 数据导出操作的同学大都会遇见。至于为什么会遇到这个错误，原因很简单，那就是：我们不知道 MySQL 默认的导出目录是哪里，甚至都不知道 MySQL 对于数据的导出目录都有所限制。这其实很正常，因为我们不可能知道所有事，只要我们在遇到问题之后，能够找到正确的解决方法并记住就足够啦！学习嘛，就是一个不断积累的过程。

如果数据库能登录的话直接将文件导出就可以

但是如果数据库无法登录，只能从服务器导出

以PHPstudy一键包为例（一键包测试环境不要用于实际生产环境，如果需要装运行环境建议还是自己装，一键包实际生产过程很多问题真不建议使用）

数据是存储在MySQLdate文件里面直接将整个文件复制下来就可以

找到想对应的数据库把数据复制出来就好

由于各种原因，本人要使用了upupw一键包

Upupw的数据存储于MariaDB/date有别于PHPstudy

数据导入后需要在后台更新数据库信息

以WordPress为例，会出现打开网址空白是因为导入数据没及时更新，登录后台地址

提示网址需要更新，点击更新过段时间，大概半小时将服务器重启就可以

其他的程序，需要更新下后台数据库信息就可以

感谢关注天善智能，走好数据之路↑↑↑

欢迎关注天善智能，我们是专注于商业智能BI，大数据，数据分析领域的垂直社区，学习，问答、求职一站式搞定！

每周2-3场免费数据领域公开课，每月线下活动，欢迎关注！

最近学了一下 sql ，因为做数据分析不会sql真不行。

平时学的都是 Python，所以如果要用 pandas 做数据分析，数据除了导入 excel 和 csv 文件，应该还要会从数据库中导入数据到 Python 中，于是我进行了以下的学习和探索。

环境

Python 3.X

IDE : juyter notebook

安装必要的模块

进入：

http://lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient

找到适合自己的版本

下载到本地（我的下载到e：盘根目录）

然后打开 cmd 如下图安装

这里推荐本地安装

使用Python连接数据库

import MySQLdb

如果导入模块没报错，恭喜你，安装模块成功了！

通过connect 方法连接本地 mysql 数据库，这里要注意你要修改的或许是登录密码和数据库名称

创建游标

首先，我们要创建一个游标

cursor = conn.cursor()

什么是游标呢？

游标就相当于一个缓冲区，存放暂时的结果（这是我的理解）

就像去超市买买买，你可能要推一个手推车，这里的手推车就像游标，推着手推车去对应的货架完成挑选操作，而手推车最终会在出超市时候清空

观察数据库数据

我本地的数据库 test 中有个 tdb_goods 的表

表的内容如上图所示

有商品的 id，名称，种类 id，品牌 id，是否在售和是否下架几个字段

一共有23条记录

获取数据

我们使用 select 语句可以获取数据

首先我们用游标的 execute 方法执行一句 sql 语句获取 tdb_goods 中的所有数据

然后使用游标的 fetchone 方法取出其中一条记录并且展现出来

如上图，我们取出的数据是元组形式

当然，我们应该养成打开后关闭的好习惯，所以，在操作结束，应该断开与数据库的连接

conn.close()

异常处理

当我们在操作时，希望和在 mysql 数据库中一样，异常时会提示异常信息

那么我们就要加入异常处理模块

这里异常时，就将异常的信息告知我们了

我们将以上代码整理一下：

当我们将 fetchone 改成 fetchall 时候，就是取出所有信息了

使用面向对象编程

注：这里定义了一个类叫 Mysql，然后用类创建一个对象名为 object，在创建对象的时候，类的初始化时调用连接数据库函数

创建了一个叫 object 的对象，使用对象的 get_a 方法从数据库 tdb_goods 中选出 cate_id=1 的一条记录，在 get_a 函数最后位置断开数据库连接

结果如下：

插入记录到数据库

注：这里，将一条记录插入了数据库，但是当我执行完这条语句（没报错）

刷新数据库并没有新增一条记录

划重点：在这里，有一个 self.connmit()，这个叫提交，如果不写这句，就无法将所做修改保存的数据库中

加上这个之后（加在以下两句之间）：

可以看到，数据成功写入数据库了~

数据导入pandas（数据分析准备）

如果是用 pandas 做数据分析

简单的几行代码即可将 mysql 的数据导入 pandas 进行分析

导入表中所有数据并查看前五行

选你所想

一般来说，学 sql 增删查改基础中，查用的比较多，接下来我们简单使用一些 sql 查询

查找 cate_id=3 的记录：

查找价格大于5000的商品，注意这里默认按照 goods_id 排序的：

加上 ORDER BY `goods_prices` 之后，就是按照价格升序排列：

sql 默认的是按照升序排列，如果想按照降序排列，可以这样写：

查找所有的平板电脑：

将数据库文件导出成csv

df.to_csv('E:\goods_info.csv', encoding = 'utf-8', index = False)

可以看到，数据成功导出成 csv 了~

最后养成好习惯，断开与数据库的连接：

我们再次查询，发现报错了，说明确实已经断开了连接：

如果运行代码报错，很可能是你的数据库名和数据库登录密码没有修改~

文中如有错误和叙述不妥之处，望指正。

本文作者：天善智能社区学员王大伟

天善社区博客地址：https://hellobi/u/wangdawei/articles

天善学院svip正火爆报名中！包含业务知识一站通、Excel BI商业智能、七周成为数据分析师、对话大数据系列技术、R语言15案例、Python3网络爬虫实战案例、Python机器学习、Python数据科学家精华实战课程、深度学习模型和实战课程、数据分析报告共10套课程，其他课程只需五折即可，欢迎大家关注报名。https://hellobi/svip

（1）：导入 xlsx,没有xlrd,MySQLdb 的安装一下

import xlrd

import MySQLdb

# Open the workbook and define the worksheet

book = xlrd.open_workbook("1.xlsx")

sheet = book.sheets()[0]

database = MySQLdb.connect("localhost","root","","test",charset='utf8')

cursor = database.cursor()

spe = ';'

query = """INSERT INTO tb_ic_info_test (firm_name, ic_name, ic_domain, ic_area, ic_host_type, ic_ip_f, ic_ip_s) VALUES (%s, %s, %s, %s, %s, %s, %s)"""

for r in range(1, sheet.nrows):

      firm_name = sheet.cell(r,0).value      

      ic_name = sheet.cell(r,1).value

      ic_domain= sheet.cell(r,2).value

      ic_area= sheet.cell(r,3).value

      ic_host_type= sheet.cell(r,4).value

      ic_ip_f= sheet.cell(r,5).value

      ic_ip_s= sheet.cell(r,6).value

      if spe in ic_domain:

            ic_domain_arr = ic_domain.split(spe)

          for s in ic_domain_arr:

                valuess = (firm_name, ic_name, s_domain, ic_area, ic_host_type, ic_ip_f, ic_ip_s)

                cursor.execute(query, valuess)                    

      print r 

      values = (firm_name, ic_name, ic_domain, ic_area, ic_host_type, ic_ip_f, ic_ip_s)      

      cursor.execute(query, values)    

cursor.close()

databasemit()

database.close()

print "Done! "

print ""

(2):从数据库里，查询，删除

import xlrd

import MySQLdb

import json

# Open the workbook and define the worksheet

database = MySQLdb.connect("localhost","root","","test",charset='utf8')

cursor = database.cursor()

query = """SELECT * from tb_ic_info  where ic_domain like '%;%'"""

query_insert = """INSERT INTO tb_ic_info (firm_name, ic_name, ic_domain, ic_area, ic_host_type, ic_ip_f, ic_ip_s) VALUES (%s, %s, %s, %s, %s, %s, %s)"""

query_delete = """DELETE from tb_ic_info  where id = %s"""

cursor.execute(query)

resultss = cursor.fetchall()

for results in resultss:

      firm_id = results[0]

      firm_name = results[1]    

      ic_name = results[2]

      ic_domain = results[3]

      ic_area= results[4]

      ic_host_type= results[5]

      ic_ip_f= results[6]

      ic_ip_s= results[7]

      ic_domain_arr = ic_domain.split(';');

      for domain in ic_domain_arr:

      values = (firm_name, ic_name, domain, ic_area, ic_host_type, ic_ip_f, ic_ip_s)      

      cursor.execute(query_insert, values)    

      print values

      cursor.execute(query_delete, firm_id)

      print firm_id

      #values = (firm_name, ic_name, ic_domain, ic_area, ic_host_type, ic_ip_f, ic_ip_s)      

      #print firm_name

      #json_values = json.loads(values)

      #print json.dumps(json_values)

      #cursor.execute(query, values)    

cursor.close()

databasemit()

database.close()

print "Done! "

声明：本栏目所使用的素材都是凯哥学堂VIP学员所写，学员有权匿名，对文章有最终解释权；凯哥学堂旨在促进VIP学员互相学习的基础上公开笔记。

mysql高级查询2

select高级查询还有以下几个语法，模糊查询like，子查询，记录集合并union。

模糊查询like有以下三个符号%，_，[]。语法如下：

% 代表任意个数，任意字符。例如：

SELECT * FROM shaicheng WHERE username LIKE '%里%';

SELECT * FROM shaicheng WHERE username LIKE '%里';

_ 代表任意单个字符。语法如下：

SELECT * FROM SHAICHENG WHERE GROUPNAME LIKE '_士';

SELECT * FROM SHAICHENG WHERE changci LIKE '__场';

[AB]可选用一个字符。

子查询语法如下：

假如有两张联合的表，

如果你是关系运算符，子查询的结果只能是一个（单行单列）。 如果你要做多行值的判断，有in。 in语法如下：

记录集合并union语法如下：

查询出来的列名是按照第一个表的列名显示出来的。 去空查询语法如下：

排序和条件可写在最后一条查询语句上：

作为同门的数据库产品，Excel操作Access、SQLServer中的数据非常简单。但如果想在Excel中处理MySQL中的数据呢？MySQL是和php珠联璧合的一个数据库，网站开发中经常用到，如果Excel也能访问它就会给工作带来很多方便。

例如，MySQL数据库中的用户登录数据表，有时希望在Excel中对该表进行一些分析：

操作步骤如下：

一、到mysql官网下载odbc连接驱动

下载地址为：https://dev.mysql/downloads/connector/odbc/

可下载的驱动程序共有4个文件，请根据自身情况选择下载。例如，我们现在使用的是64位的win7系统，那么就下载安装64位的驱动：

32位和64位的驱动又分别包含2个文件，一个是安装包，一个是压缩包。一般选择下载msi安装即可。

选择文件下载时，将弹出登录页面；如果不需要登录，可点击“No thanks, just start my download.”直接进入下载。

安装驱动的过程中，有可能会出现这样的错误提示：

如果使用压缩包安装，则会直接提示缺少“msvcr120.dll”的错误。

这两种错误其实都指向了同一个问题，最简单的解决办法就是安装Visual C++ 2013运行库。微软官方的下载地址为：

https://microsoft/zh-cn/download/details.aspx?id=40784

同样的，这里有32位和64位2种文件可供下载，请根据自身情况选择。

二、创建数据源

进入“控制面板--系统和安全--管理工具”，双击打开“数据源（ODBC）”，点击“添加”：

点击“完成”后，接着设置要连接的mysql数据库：

点击“ok”后即可增加该数据源：

三、在Excel中建立连接

打开Excel，选择“数据-自其它来源-来自数据连接向导”：

选择“odbc dsn”：

“下一步”后选择刚刚创建的mysql：

然后再选择要连接的数据库及库中的数据表：

接着再设定一个连接名称就ok啦：

注意：mysql数据库中的表名称和表字段不能使用中文，否则将带来乱码问题。因为excel和mysql的默认编码方式不一样，处理起来会相当麻烦。

前言

MySQL表格数据可以直接通过select导入导出到文本，可以快速的提取数据。

INTO OUTFILE

在命令行中直接使用into outfile写到指定的目录下就可以了，记得目录需要MySQL运行用户有写的权限

select * into outfile "/data/outputdata/1.txt" from test.t1;

如果对文本格式用要求，可以队列和行的数据进行格式化

fields：设置字段选项

设置字段分隔符：field terminated by '\t'

lines：设置行选项

设置导出每行添加换行符：lines terminated by '\n'

每行开始添加指定的字符串：starting by 'start：'

例如

设置列以 , 分割，每行添加换行符并设置行头以start:开始

select * into outfile "/data/outputdata/2.txt" fields terminated by "," lines terminated by '\n' starting by 'start：' from test.t1;

文本数据如下

start：2,pppP

start：1,ddd

LOAD DATA INFILE

MySQL可以支持从文本文件里面直接load文本数据到表中，但是也需要指定分割符

load data infile "/data/outputdata/2.txt" into table test.t2 fields terminated by ',';

总结

这样子的导入导出就是为了方便获取到数据，如果是备份不建议使用这种方式。

数据导入的方式(csv,txt之类)

在MySQL中，数据导入的方式有两种方式

通过第三方客户端导入(workbench)

通过mysql client 方式导入

通过mysql clinet的导入方式，相比第三方客户端导入的方式，效率会高点，但是类似workbench这种的，因为图形UI做的相对比较人性，研发人员在处理数据的过程中减少对指令的交互

具体操作方法 准备数据(csv准备)

当你的CSV数据准备好之后，需要有个点注意(由于导入容易碰到编码问题，所以在导入之前，需要使用notepade++将csv文件的编码转换成utf-8的编码方式[格式-->utf-8编码格式])然后保存 导入数据

1.选中你需要导入的库的项

2.选择需要导入的csv文件(也可以是json格式)

3.由于csv中的字段并不是完整是所有表的字段，故此我们在这里新创建一张表(建议是先创建一张跟要同步的表一样字段属性的表先)

4.留意导入的数据是否正确

5.一直点NEXT即可完成

总结

需要注意的是:如果使用workbench导入的时候，如果数据量不大，可以使用这种方式，如果数据量比较庞大，建议直接通过命令行的方式会高效，其中一个最明显的特征就是如果导入大量数据的CSV，在workbench导入的时候，可能会卡死或者桌面终端无法操作，从而导致导入失败

执行SQL语句

select fullname,time,endtime,closed from chat_archive into outfile 'c:/xxx.xls'

注意：因为office默认的是gb2312编码，服务器端生成的很有可能是utf-8编码，此时有几种选择

1、把查询出来的结果转换为GB2312格式（字段fullname）

selectconvert(fullname using gb2312)from chat_archive into outfile 'c:/xxx.xls'

2、在服务器端使用iconv来进行编码转换

iconv -futf8 -tgb2312 -otest2.xls femm.xls

如果转换顺利，那么从server上下载下来就可以使用了。

3.转换如果不顺利，则会提示：iconv: illegal input sequence at position 1841 类似于这样的错误，

先把femm.xls下载下来，这个时候文件是utf-8编码的，用excel打开，乱码。

把femm.xls以文本方式打开，然后另存为，在编码选择ANSI编码，保存。

引申：如何带表头输出Excel？

select convert("用户号" using gb2312),convert("用户名" using gb2312) from dual union select convert(userno using gb2312),convert(username using gb2312) from t_users into outfile 'd://users.xls';

注意：

1、使用合并的查询结果集 但是union会过滤掉重复记录 不过滤使用union all

2、如果保存的不是磁盘根目录，而是某个文件夹，需要将文件夹先创建好