步骤
 
点击数据库右键新建查询
填充数据名和表名即可。
想要导出全部去掉表名选项。
 
SELECT
	CONCAT(
	'ALTER TABLE ',
	TABLE_NAME,
	'',
	'ADD ',
IF
	(
	NON_UNIQUE = 1,
CASE
	UPPER( INDEX_TYPE ) 
	WHEN 'FULLTEXT' THEN
	'FULLTEXT INDEX' 
	WHEN 'SPATIAL' THEN
	'SPATIAL INDEX' ELSE CONCAT( 'INDEX ', INDEX_NAME, 'USING ', INDEX_TYPE ) 
	END,
IF
	( UPPER( INDEX_NAME ) = 'PRIMARY', CONCAT( 'PRIMARY KEY USING ', INDEX_TYPE ), CONCAT( 'UNIQUE INDEX ', INDEX_NAME, 'USING ', INDEX_TYPE ) ) 
	),
	'(',
	GROUP_CONCAT( DISTINCT CONCAT( '', COLUMN_NAME, '' ) ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', ' ),
	');' 
) AS 'Show_Add_Indexes' 
FROM
	information_schema.STATISTICS 
WHERE
	TABLE_SCHEMA = '数据库名字' 
	AND TABLE_NAME='表名' 
GROUP BY
	TABLE_NAME,
	INDEX_NAME 
ORDER BY
	TABLE_NAME ASC,
	INDEX_NAME ASC;

一、实验简介
 
1.1 实验内容
 
本节实验中我们将学习并实践数据库的其它基本操作：索引、视图，导入和导出，备份和恢复等。
 
这些概念对于数据库管理员而言都非常重要，请仔细理解并完成所有实验操作。
 
作为基础篇，不会涉及到关于索引和视图的高级应用和核心概念，但是基本操作大家会了解，尤其是关于索引的内容，学会建立恰当的索引可以大大提高数据库的查询效率，更高级的应用我们会在进阶篇详细讲解。
 
1.2 实验知识点
 
索引
视图
导入和导出
备份和恢复
二、开发准备
 
 
 
注：如果你是从上一节直接进入本节进行学习的，请先删除上一节建立的数据库 mysql_shiyan ，删除语句为 DROP DATABASE mysql_shiyan; 。
 
 
在正式开始本实验内容之前，需要先下载相关代码。
 
下载的代码会搭建好一个名为 mysql_shiyan 的数据库（有三张表：department，employee，project），并向其中插入数据。
 
具体操作如下,首先输入命令进入 /home/shiyanlou/Desktop 目录：
 
cd /home/shiyanlou/Desktop
 
然后再输入命令，下载代码：
 
git clone https://github.com/shiyanlou/SQL6
 
下载完成后，输入命令开启 MySQL 服务并使用 root 用户登录：
 
# 打开 MySQL 服务
sudo service mysql start

# 使用 root 用户登录
mysql -u root
 
下载的 SQL6 目录下，有两个文件 MySQL-06.sql 和 in.txt，其中第一个文件用于创建数据库并向其中插入数据，第二个文件用于测试数据导入功能。
 
（SQL6 目录在桌面上，你可以尝试查看、编辑里面的文件。）
 
如果你是接着上一个实验操作的话，首先把 mysql_shiyan 数据库删掉：
 
mysql> drop database mysql_shiyan;
 
输入命令运行第一个文件，搭建数据库并插入数据：
 
mysql> source /home/fangsi/桌面/Python/MYSQL/SQL_4/SQL6/MySQL-06.sql
 
 
 
三、实验步骤
 
本节将通过实践操作，带领大家熟悉数据库的其它操作。
 
3.1 索引
 
索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。
 
当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行对比，然后返回满足条件的记录。这样做会执行大量磁盘 I/O 操作，并花费大量数据库系统时间。
 
而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。
 
对一张表中的某个列建立索引，有以下两种语句格式：
 
ALTER TABLE 表名字 ADD INDEX 索引名 (列名);

CREATE INDEX 索引名 ON 表名字 (列名);
 
我们用这两种语句分别建立索引：
 
ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名为idx_id的索引

CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名为idx_name的索引
 
索引的效果是加快查询速度，当表中数据不够多的时候是感受不出它的效果的。这里我们使用命令 SHOW INDEX FROM 表名字; 查看刚才新建的索引：
 
 
 
在使用 SELECT 语句查询的时候，语句中 WHERE 里面的条件，会自动判断有没有可用的索引。
 
比如有一个用户表，它拥有用户名(username)和个人签名(note)两个字段。其中用户名具有唯一性，并且格式具有较强的限制，我们给用户名加上一个唯一索引；个性签名格式多变，而且允许不同用户使用重复的签名，不加任何索引。
 
这时候，如果你要查找某一用户，使用语句 select * from user where username=? 和 select * from user where note=? 性能是有很大差距的，对建立了索引的用户名进行条件查询会比没有索引的个性签名条件查询快几倍，在数据量大的时候，这个差距只会更大。
 
一些字段不适合创建索引，比如性别，这个字段存在大量的重复记录无法享受索引带来的速度加成，甚至会拖累数据库，导致数据冗余和额外的 CPU 开销。
 
3.2 视图
 
视图是从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。
 
注意理解视图是虚拟的表：
 
数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；
视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；
在使用视图的时候，可以把它当作一张表。
创建视图的语句格式为：
 
CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;
 
可见创建视图的语句，后半句是一个 SELECT 查询语句，所以视图也可以建立在多张表上，只需在 SELECT 语句中使用子查询或连接查询，这些在之前的实验已经进行过。
 
现在我们创建一个简单的视图，名为 v_emp，包含v_name，v_age，v_phone三个列：
 
CREATE VIEW v_emp (v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;
 
 
3.3 导入
 
此处讲解的是导入一个纯数据文件，该文件中将包含与数据表字段相对应的多条数据，这样可以快速导入大量数据，除此之外，还有用 SQL 语句的导入方式，语法为：source *.sql 这是实验中经常用到的。两者之间的不同是：数据文件导入方式只包含数据，导入规则由数据库系统完成；SQL 文件导入相当于执行该文件中包含的 SQL 语句，可以实现多种操作，包括删除，更新，新增，甚至对数据库的重建。
 
数据文件导入，可以把一个文件里的数据保存进一张表。导入语句格式为：
 
LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;
 
现在 SQL6 目录下有一个名为 in.txt 的文件，我们尝试把这个文件中的数据导入数据库 mysql_shiyan 的 employee 表中。
 
由于导入导出大量数据都属于敏感操作，根据 mysql 的安全策略，导入导出的文件都必须在指定的路径下进行，在 mysql 终端中查看路径变量：
 
mysql -uroot
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)
 
注意到 secure_file_priv 变量指定安全路径为 /var/lib/mysql-files/ ，要导入数据文件，需要将该文件移动到安全路径下。
 
打开 Xfce 终端，输入命令拷贝 SQL6 文件夹到 /var/lib/mysql-files/ 目录：
 
sudo cp -a /home/shiyanlou/Desktop/SQL6 /var/lib/mysql-files/
 
使用命令 sudo vim /var/lib/mysql-files/SQL6/in.txt 查看 in.txt 文件中的内容：
 
6	Alex	26	3000	123456	dpt1
7	Ken	27	3500	654321	dpt1
8	Rick	24	3500	987654	dpt3
9	Joe	31	3600	100129	dpt2
10	Mike	23	3400	110110	dpt1
11	Jim	35	3000	100861	dpt4
12	Mary	21	3000	100101	dpt2
 
 
 
可以看到其中仅仅包含了数据本身，没有任何的 SQL 语句
 
再使用以下命令以 root 用户登录数据库，再连接 mysql_shiyan 数据库：
 
# 在Xfce 终端输入命令
mysql -u root

# 在 MySQL 控制台中输入命令
use mysql_shiyan
 
查看一下没有导入数据之前，employee 表中的数据：
 
mysql> SELECT * FROM employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Jobs | NULL |   3600 |  19283 | dpt2   |
|  4 | Tony | NULL |   3400 | 102938 | dpt3   |
|  5 | Rose |   22 |   2800 | 114114 | dpt3   |
+----+------+------+--------+--------+--------+
5 rows in set (0.08 sec)

mysql> 
 
现在执行导入语句，文件中的数据成功导入 employee 表：
 
mysql> LOAD DATA INFILE '/var/lib/mysql-files/SQL6/in.txt' INTO TABLE employee;
Query OK, 7 rows affected (0.67 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Jobs | NULL |   3600 |  19283 | dpt2   |
|  4 | Tony | NULL |   3400 | 102938 | dpt3   |
|  5 | Rose |   22 |   2800 | 114114 | dpt3   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
|  8 | Rick |   24 |   3500 | 987654 | dpt3   |
|  9 | Joe  |   31 |   3600 | 100129 | dpt2   |
| 10 | Mike |   23 |   3400 | 110110 | dpt1   |
| 11 | Jim  |   35 |   3000 | 100861 | dpt4   |
| 12 | Mary |   21 |   3000 | 100101 | dpt2   |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)

mysql> 
 
3.4 导出
 
导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。导出语句基本格式为：
 
SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
 
注意：语句中 “文件路径” 之下不能已经有同名文件。
 
现在我们把整个 employee 表的数据导出到 /var/lib/mysql-files/ 目录下，导出文件命名为 out.txt 具体语句为：
 
SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;
 
用 gedit 可以查看导出文件 /var/lib/mysql-files/out.txt 的内容：
 
 
 
也可以使用 sudo cat /var/lib/mysql-files/out.txt 命令查看。
 
 
mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;
Query OK, 12 rows affected (0.38 sec)

fangsi@fangsi-virtual-machine:~/桌面/Python/MYSQL/SQL_4/SQL6$ sudo gedit /var/lib/mysql-files/out.txt
 
1	Tom	26	2500	119119	dpt4
2	Jack	24	2500	120120	dpt2
3	Jobs	\N	3600	19283	dpt2
4	Tony	\N	3400	102938	dpt3
5	Rose	22	2800	114114	dpt3
6	Alex	26	3000	123456	dpt1
7	Ken	27	3500	654321	dpt1
8	Rick	24	3500	987654	dpt3
9	Joe	31	3600	100129	dpt2
10	Mike	23	3400	110110	dpt1
11	Jim	35	3000	100861	dpt4
12	Mary	21	3000	100101	dpt2
 
3.5 备份
 
数据库中的数据十分重要，出于安全性考虑，在数据库的使用中，应该注意使用备份功能。
 
 
 
备份与导出的区别：导出的文件只是保存数据库中的数据；而备份，则是把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件。
 
 
mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。
 
使用 mysqldump 备份的语句：
 
mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表
 
 
 
mysqldump 是一个备份工具，因此该命令是在终端中执行的，而不是在 mysql 交互环境下
 
 
我们尝试备份整个数据库 mysql_shiyan，将备份文件命名为 bak.sql，先 Ctrl+D 退出 MySQL 控制台，再打开 Xfce 终端，在终端中输入命令：
 
cd /home/shiyanlou/
mysqldump -u root mysql_shiyan > bak.sql;
 
使用命令 “ls” 可见已经生成备份文件 bak.sql：
 
fangsi@fangsi-virtual-machine:~/桌面/Python/MYSQL/SQL_4/SQL6/Copy_MySQL$ mysqldump -u root -p mysql_shiyan > bak.sql;
Enter password: 
fangsi@fangsi-virtual-machine:~/桌面/Python/MYSQL/SQL_4/SQL6/Copy_MySQL$ ls
bak.sql
fangsi@fangsi-virtual-machine:~/桌面/Python/MYSQL/SQL_4/SQL6/Copy_MySQL$ 
 
 
 
你可以用 gedit 查看备份文件的内容，可以看见里面不仅保存了数据，还有所备份的数据库的其它信息。
 
 
 
 
3.6 恢复
 
用备份文件恢复数据库，其实我们早就使用过了。在本次实验的开始，我们使用过这样一条命令：
 
source /tmp/SQL6/MySQL-06.sql
 
这就是一条恢复语句，它把 MySQL-06.sql 文件中保存的mysql_shiyan 数据库恢复。
 
还有另一种方式恢复数据库，但是在这之前我们先使用命令新建一个空的数据库 test：
 
mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录

CREATE DATABASE test;  #新建一个名为test的数据库
 
再次 Ctrl+D 退出 MySQL，然后输入语句进行恢复，把刚才备份的 bak.sql 恢复到 test 数据库：
 
mysql -u root -p test < bak.sql
 
我们输入命令查看 test 数据库的表，便可验证是否恢复成功：
 
mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录

use test               #连接数据库test

SHOW TABLES;           #查看test数据库的表
 
可以看见原数据库的4张表和1个视图，现在已经恢复到 test 数据库中：
 
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| department     |
| employee       |
| project        |
| table_1        |
| v_emp          |
+----------------+
5 rows in set (0.04 sec)

mysql> 
 
再查看 employee 表的恢复情况：
 
mysql> SELECT * FROM employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Jobs | NULL |   3600 |  19283 | dpt2   |
|  4 | Tony | NULL |   3400 | 102938 | dpt3   |
|  5 | Rose |   22 |   2800 | 114114 | dpt3   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
|  8 | Rick |   24 |   3500 | 987654 | dpt3   |
|  9 | Joe  |   31 |   3600 | 100129 | dpt2   |
| 10 | Mike |   23 |   3400 | 110110 | dpt1   |
| 11 | Jim  |   35 |   3000 | 100861 | dpt4   |
| 12 | Mary |   21 |   3000 | 100101 | dpt2   |
+----+------+------+--------+--------+--------+
12 rows in set (0.29 sec)

mysql> 
 
四、实验总结
 
在本实验中，我们实践了下面几个基本概念：
 
索引：可以加快查询速度
视图：是一种虚拟存在的表
导入：从文件中导入数据到表
导出：从表中导出到文件中
备份：mysqldump 备份数据库到文件
恢复：从文件恢复数据库
下一节我们将学习 MySQL 服务安装及命令使用 。
 
五、课后习题
 
建立员工名字 employee.name 和对应部门人数 department.people_num 的视图并展示。
 
练习题参考答案
 
以下内容仅供参考，为了能有更好的学习效果，请尽量按自己的想法来完成练习题。
 
mysql> CREATE VIEW name_people_num (name, people_num)
    -> AS SELECT name, people_num FROM employee, department
    -> WHERE in_dpt = dpt_name;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM name_people_num;
+------+------------+
| name | people_num |
+------+------------+
| Alex |         11 |
| Ken  |         11 |
| Mike |         11 |
| Jack |         12 |
| Jobs |         12 |
| Joe  |         12 |
| Mary |         12 |
| Tony |         10 |
| Rose |         10 |
| Rick |         10 |
| Tom  |         15 |
| Jim  |         15 |
+------+------------+
12 rows in set (0.00 sec)

参考文章：
 
pg_dump 详解/使用举例
 
目录
 
1.pg_dump导出整个数据库
 
2.pg_dump导出某个表
 
2.1 pg_dump 及pgsql配置密码导入导出数据
 
 3.pgsql导入整个数据库
 
4. pg_dumpall导出及psql恢复
 
5.pg_restore恢复
 
6.压缩方法
 
7.xz压缩数据导入数据库方法
 
8.pg_restore数据导入工具
 
9.pg_dump工具参数
 
10.pg_dumpall工具参数
 

关于pg_dump:
 
pg_dump --  将一个PostgreSQL数据库抽出到一个脚本文件或者其它归档文件中。
pg_dump是一个用于备份PostgreSQL数据库的实用工具，即使当前数据库正在使用，也能够生成一致性的备份，且不会阻塞其他用户访问数据库(包括读、写)。
PostgreSQL提供的一个工具pg_dump,逻辑导出数据，生成sql文件或其他格式文件。
pg_dump是一个客户端工具，可以远程或本地导出逻辑数据，恢复数据至导出时间点。
pg_dump只能备份一个数据库
pg_dump 一次只转储一个数据库，并不会转储有关角色或表空间的信息 (因为那些是群集范围而不是每个数据库)。。
关于pg_dumpall：
 
如果要备份Cluster中数据库共有的全局对象，例如角色和表空间，需要使用pg_dumpall。
备份文件以文本或存档文件格式输出。
Script dumps是一个普通文本文件，包含将数据库重构到保存时的状态所需的SQL命令。
要从这样的脚本恢复，需要将其提供给psql。脚本文件甚至可以用来在其他机器或者其他架构上重构数据库;进行一些必要的修改，甚至可以在其他数据库上使用。
pg_dumpall 在给定的群集中备份每个数据库, 并保留群集范围内的数据, 如角色和表空间定义。
关于pg_restore：
 
PostgreSQL提供的一个工具pg_restore用来导入数据
1.pg_dump导出整个数据库
 
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)>/data/dum.sql

#use
[postgres@vmonline postgres]$pg_dump -h localhost -U postgres pgtest> ~/dum.sql

#特别注意：数据库名称:pgtest与'>' 之间不能存在空格
 
其它导出示例：
 
# 操作系统平面文件格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fp -t test_dump > /tmp/test_dump1.dmp highgo
#自定义格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -t test_dump > /tmp/test_dump2.dmp highgo
#tar包格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Ft -t test_dump > /tmp/test_dump3.dmp highgo
#自定义、0级压缩格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -Z 0 -t test_dump > /tmp/test_dump4.dmp highgo
#自定义、9级压缩格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -Z 9 -t test_dump > /tmp/test_dump5.dmp highgo
#显示信息内容
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -v -t test_dump > /tmp/test_dump6.dmp highgo
 
验证不同格式的文件：
 
# ll
-rw-rw-r-- 1 pg   pg   379738 Nov  1 15:05 test_dump1.dmp
-rw-rw-r-- 1 pg   pg   217142 Nov  1 15:05 test_dump2.dmp
-rw-rw-r-- 1 pg   pg   384512 Nov  1 15:05 test_dump3.dmp
-rw-rw-r-- 1 pg   pg   429852 Nov  1 15:08 test_dump4.dmp
-rw-rw-r-- 1 pg   pg   217142 Nov  1 15:07 test_dump5.dmp
压缩格式的文件明显较小

不同格式的文件
# file test_dump1.dmp
test_dump1.dmp: ASCII text
# file test_dump2.dmp
test_dump2.dmp: PostgreSQL custom database dump - v1.13-0
# file test_dump3.dmp
test_dump3.dmp: POSIX tar archive
# file test_dump4.dmp
test_dump4.dmp: PostgreSQL custom database dump - v1.13-0
注意1：默认是-Fp格式（操作系统平面文件）
注意2：并行导出只支持-Fd 目录格式

$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -v -Z 1 -t test_dump > /tmp/test_dump3.dmp highgo
pg_dump: parallel backup only supported by the directory format
 
2.pg_dump导出某个表
 
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) -t table(表名) >/data/dum.sql

#use
pg_dump -h localhost -U postgres pgtest -t public.test_table> ~/dum_test_table.sql

#注意：public.test_table中, 'public'是模式名称, 'test_table'是模式中的表名称
 
pg_dump导出某个模式
 
pg_dump导出public模式:  
  pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) -s public(模式名称) >/data/dum.sql  
  [postgres@vmonline ~]$pg_dump -h localhost -U postgres pgtest -s public> /home/lpf/public.sql
 
2.1 pg_dump 及pgsql配置密码导出数据
 
# 数据库导出 -- 
psql "host=XX.XX.XX.XX port=5432  user=XXX password=XXX dbname=XXX" -f table_name.sql

#use
pg_dump "host=localhost port=5432 user=postgres password=123456 dbname=pgtest" -f ~/table_name.sql

# ---------------------------------------------------- #
# 数据库表导出
pg_dump "host=XX.XX.XX.XX port=5432 user=XXXX password=XXXX dbname=XXXXX" -t table_name -f table_name.sql
 
 3.pgsql导入整个数据库
 
psql -U postgres(用户名)  数据库名(缺省时同用户名) < /data/dum.sql

#use
[postgres@vmonline ~]$ psql -U postgres pgtest < ~/dum.sql

#pg_restore导入数据库
pg_restore -h 192.168.0.100 -p 5432 -U postgres -W -d us2011 -v "/root/us2010.backup"
 
4. pg_dumpall导出及psql恢复
 
#pg_dumpall导出
su postgres #（需要切换到系统的postgres用户来进行备份）
pg_dumpall > /backup/all_the_data.backup #（备份整个数据库集群）

#psql恢复
su postgres
psql -f /backup/all_the_data.backup postgres
exit
 
5.pg_restore恢复
 
#pg_restore恢复示例
pg_restore -h 192.168.0.100 -p 5432 -U postgres -W -d us2011 -v "/root/us2010.backup"
 
6.压缩方法
 
一般用dump导出数据会比较大，推荐使用xz压缩
 
压缩方法： 
 
xz dum.sql 
 
压缩后会生成 dum.sql.xz 的文件
 
7.xz压缩数据导入数据库方法
 
xzcat /data/dum.sql.xz | psql -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)
 
8.pg_restore数据导入工具
 
[postgres@vmonline ~]$ pg_restore --help
pg_restore 从一个归档中恢复一个由 pg_dump 创建的 PostgreSQL 数据库.

用法:
  pg_restore [选项]... [文件名]

一般选项:
  -d, --dbname=名字        连接数据库名字
  -f, --file=文件名        输出文件名
  -F, --format=c|d|t       备份文件格式(应该自动进行)
  -l, --list               打印归档文件的 TOC 概述
  -v, --verbose            详细模式
  -V, --version            输出版本信息, 然后退出
  -?, --help               显示此帮助, 然后退出

恢复控制选项:
  -a, --data-only             只恢复数据, 不包括模式
  -c, --clean                  在重新创建之前，先清除（删除）数据库对象
  -C, --create                 创建目标数据库
  -e, --exit-on-error          发生错误退出, 默认为继续
  -I, --index=NAME             恢复指定名称的索引
  -j, --jobs=NUM               执行多个并行任务进行恢复工作
  -L, --use-list=FILENAME      从这个文件中使用指定的内容表排序
                               输出
  -n, --schema=NAME            在这个模式中只恢复对象
  -N, --exclude-schema=NAME    do not restore objects in this schema
  -O, --no-owner               不恢复对象所属者
  -P, --function=NAME(args)    恢复指定名字的函数
  -s, --schema-only           只恢复模式, 不包括数据
  -S, --superuser=NAME         使用指定的超级用户来禁用触发器
  -t, --table=NAME             restore named relation (table, view, etc.)
  -T, --trigger=NAME          恢复指定名字的触发器
  -x, --no-privileges          跳过处理权限的恢复 (grant/revoke)
  -1, --single-transaction     作为单个事务恢复
  --disable-triggers           在只恢复数据的过程中禁用触发器
  --enable-row-security        启用行安全性
  --if-exists              当删除对象时使用IF EXISTS
  --no-comments                do not restore comments
  --no-data-for-failed-tables  对那些无法创建的表不进行
                               数据恢复
  --no-publications            do not restore publications
  --no-security-labels         不恢复安全标签信息
  --no-subscriptions           do not restore subscriptions
  --no-tablespaces             不恢复表空间的分配信息
  --section=SECTION            恢复命名节 (数据前、数据及数据后)
  --strict-names               要求每个表和/或schema包括模式以匹配至少一个实体
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
                ALTER OWNER 命令来设置所有权

联接选项:
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在恢复前执行SET ROLE操作

The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified
multiple times to select multiple objects.
 
9.pg_dump工具参数
 
[root@vmonline ~]# pg_dump --help
pg_dump 把一个数据库转储为纯文本文件或者是其它格式.

用法:
  pg_dump [选项]... [数据库名字]

一般选项:
  -f, --file=FILENAME          输出文件或目录名
  -F, --format=c|d|t|p         输出文件格式 (定制, 目录, tar)
                               明文 (默认值))
  -j, --jobs=NUM               执行多个并行任务进行备份转储工作
  -v, --verbose                详细模式
  -V, --version                输出版本信息，然后退出
  -Z, --compress=0-9           被压缩格式的压缩级别
  --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   显示此帮助, 然后退出

控制输出内容选项:
  -a, --data-only              只转储数据,不包括模式
  -b, --blobs                  在转储中包括大对象
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  在重新创建之前，先清除（删除）数据库对象
  -C, --create                 在转储中包括命令,以便创建数据库
  -E, --encoding=ENCODING      转储以ENCODING形式编码的数据
  -n, --schema=SCHEMA          只转储指定名称的模式
  -N, --exclude-schema=SCHEMA  不转储已命名的模式
  -o, --oids                   在转储中包括 OID
  -O, --no-owner               在明文格式中, 忽略恢复对象所属者

  -s, --schema-only            只转储模式, 不包括数据
  -S, --superuser=NAME         在明文格式中使用指定的超级用户名
  -t, --table=TABLE            只转储指定名称的表
  -T, --exclude-table=TABLE    不转储指定名称的表
  -x, --no-privileges          不要转储权限 (grant/revoke)
  --binary-upgrade             只能由升级工具使用
  --column-inserts             以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-triggers           在只恢复数据的过程中禁用触发器
  --enable-row-security        启用行安全性（只转储用户能够访问的内容）
  --exclude-table-data=TABLE   不转储指定名称的表中的数据
  --if-exists              当删除对象时使用IF EXISTS
  --inserts                    以INSERT命令，而不是COPY命令的形式转储数据
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-security-labels         不转储安全标签的分配
  --no-subscriptions           do not dump subscriptions
  --no-synchronized-snapshots  在并行工作集中不使用同步快照
  --no-tablespaces             不转储表空间分配信息
  --no-unlogged-table-data     不转储没有日志的表数据
  --quote-all-identifiers      所有标识符加引号，即使不是关键字
  --section=SECTION            备份命名的节 (数据前, 数据, 及 数据后)
  --serializable-deferrable   等到备份可以无异常运行
  --snapshot=SNAPSHOT          为转储使用给定的快照
  --strict-names               要求每个表和/或schema包括模式以匹配至少一个实体
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
                ALTER OWNER 命令来设置所有权

联接选项:
  -d, --dbname=DBNAME       对数据库 DBNAME备份
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在转储前运行SET ROLE

如果没有提供数据库名字, 那么使用 PGDATABASE 环境变量
的数值.
 
10.pg_dumpall工具参数
 
[postgres@vmonline ~]$ pg_dumpall --help
pg_dumpall 抽取一个 PostgreSQL 数据库簇进一个 SQL 脚本文件.

用法:
  pg_dumpall [选项]...

一般选项:
  -f, --file=FILENAME          输出文件名
  -v, --verbose                详细模式
  -V, --version                输出版本信息，然后退出
  --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
  -?, --help                   显示此帮助, 然后退出

控制输出内容选项:
  -a, --data-only              只转储数据,不包括模式
  -c, --clean                  在重新创建数据库前先清除(删除)数据库
  -E, --encoding=ENCODING      转储以ENCODING形式编码的数据
  -g, --globals-only           只转储全局对象, 不包括数据库
  -o, --oids                   在转储中包括 OID
  -O, --no-owner               不恢复对象所属者
  -r, --roles-only             只转储角色，不包括数据库或表空间
  -s, --schema-only            只转储模式, 不包括数据
  -S, --superuser=NAME         在转储中, 指定的超级用户名
  -t, --tablespaces-only       只转储表空间，而不转储数据库或角色
  -x, --no-privileges          不要转储权限 (grant/revoke)
  --binary-upgrade             只能由升级工具使用
  --column-inserts             以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-triggers           在只恢复数据的过程中禁用触发器
  --if-exists              当删除对象时使用IF EXISTS
  --inserts                    以INSERT命令，而不是COPY命令的形式转储数据
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-role-passwords          do not dump passwords for roles
  --no-security-labels         不转储安全标签的分配
  --no-subscriptions           do not dump subscriptions
  --no-sync                    do not wait for changes to be written safely to disk
  --no-tablespaces             不转储表空间分配信息
  --no-unlogged-table-data     不转储没有日志的表数据
  --quote-all-identifiers      所有标识符加引号，即使不是关键字
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
                ALTER OWNER 命令来设置所有权

联接选项:
  -d, --dbname=CONNSTR        连接数据库使用的连接串
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -l, --database=DBNAME    另一个缺省数据库
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在转储前运行SET ROLE
 
 

一、MongoDB
 
（一）索引
 
索引提高查询速度，降低写入速度，权衡常用的查询字段，不必在太多列上建索引
在mongodb中，索引可以按字段升序/降序来创建，便于排序
默认是用btree来组织索引文件，2.4版本以后，也允许建立hash索引
 
1.btree索引
 
 
2.hash索引
 
 
Hash检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到数据节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于B-Tree 索引。
 
hash索引缺点：
 
1.Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
 
2.Hash 索引无法被用来避免数据的排序操作。
 
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
 
3.Hash 索引不能利用部分索引键查询。
 
​ 对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
 
4.Hash 索引在任何时候都不能避免表扫描。
 
​ Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
 
5.Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
 
常用命令
 
1.查看当前索引状态：db.collection.getIndexes()
 
2.创建普通单列索引：db.collection.ensureIndex({field:1/-1})//1为正序，-1为逆序
 
3.删除单个索引：db.collection.dropIndex({field:1/-1})
 
4.删除所有索引：db.collection.dropIndexes()，但_id索引不会被删除
 
5.创建多列索引：db.collection.ensureIndex({field1:1/-1，field2:1/-1})
 
​ 多列索引的使用范围更广，因为一般情况下，我们都是通过多个字段来进行查询数据的，这时候单列索引其实用不到。
 
​ 两个列一起建立索引其实就是将两个列绑定到一起，来创建索引。
 
6.子文档索引：
 
插入两条带子文档的数据
 
db.shop. insert({name: 'N0kia' , SPC: {weight: 120 , area: ' taiwan ' } } ) ;
db.shop. insert({name: 'sanxing ' , SPC ：{weight: 100 , area: 'hanguo'} } ) ;
 
查询出产地在台湾的手机
 
db.shop.find({'spc.area':'taiwan'})
 
给子文档加索引
 
db.shop.ensureIndex({'spc.area':1})
 
7.唯一索引：{unique:true}
 
db.collection.ensureIndex({field:1/-1},{unique:true})
 
8.创建稀疏索引：{sparse:true}
 
特点：如果针对field列做索引，针对不含field的列的文档，将不建立索引，与之相对，普通索引，会把该文档的field列的值认为null，并建立索引。
 
db.collection.ensureIndex({field:1/-1},{sparse:true})
 
9.hash索引
 
db.collection.ensureIndex({field:'hashed'})
 
（二）导入导出
 
通用选项
 
-h host   主机
--port port    端口
-u username 用户名
-p passwd   密码
 
mongoexport导出
 
-d  库名
-c  表名
-f  field1,field2...列名
-q  查询条件
-o  导出的文件名
--type csv  导出csv格式(便于和传统数据库交换数据，强制要求必须规定字段)
 
1.整体导出成json文件
 
mongoexport -d shop -c goods -o goods.json
 

 
 
2.只导出goods_id,goods_name列
 
mongoexport -d shop -c goods -f goods_id,goods_name -o goods.json
 
 
_id不指定也会导出
 
 
3.只导出价格低于1000元的行
 
mongoexport -d shop -c goods -q {shop_price:{$lt:1000}} -o goods.json
 
 
4.导出一个csv文件
 
mongoexport -d shop -c goods -f goods_name,shop_price -o goods.csv --type csv
 
 
可用excel打开
 
 
mongoimport导入
 
-d 待导入的数据库
-c 待导入的表(不存在会自己创建)
-f 字段
--type  csv（必须指定字段）/json(默认)
--file 备份文件路径
 
1.导入json
 
mongoimport -d shop -c goods_1 --file ./goods.json
 

 
 
2.导入csv
 
mongoimport -d shop -c goods_2 --type csv --file ./goods.csv -f goods_id,shop_price
 

 
 
mongodump导出二进制bson结构数据及其索引信息
 
-d  库名
-c  表名
 
规律：
 
每个表导出2个文件,分别是bson结构的数据文件和json结构的索引信息
如果不声明表名, 导出所有的表
 
mongodump -d shop -c goods
 

 
 
mongorestore 导入二进制文件
 
-d 数据库
-c 集合
--dir 指定文件夹
 
导入
 
mongorestore -d shop -c goods_3 --dir ./dump/shop/goods.bson
 
 
（三）replaction复制集
 
一般情况下，我们通常在机器上安装了一个数据库，这时我们的数据都是存在这个数据库中，如果有一天，因为一些不可控因素导致数据库宕机或者数据库的文件丢失，此时损失就很大了。针对于这种问题，我们希望有一个数据库集，在我们其中一个数据库进行插入的时候，其他数据库可以同步数据，这样其中一台服务器崩溃了，也能够使我们的数据正常存取。
 
在MongoDB中，是通过replaction复制集来实现此功能的。
 
步骤
 
1.创建三个存储数据库的文件夹，用来保存数据文件
 
 
2.将之前开启的服务关闭
 
3.打开三个cmd窗口，分别启动三个mongodb
 
mongod --dbpath C:\MongoDB\Server\3.4\data\db1 --logpath C:\MongoDB\Server\3.4\data\logs\db1.log --port 27017 --replSet rs
mongod --dbpath C:\MongoDB\Server\3.4\data\db2 --logpath C:\MongoDB\Server\3.4\data\logs\db2.log --port 27018 --replSet rs
mongod --dbpath C:\MongoDB\Server\3.4\data\db3 --logpath C:\MongoDB\Server\3.4\data\logs\db3.log --port 27019 --replSet rs
 
 
其中的–replSet就表示创建的数据集的名称，必须指定相同的名称才可以。
 
4.新打开一个cmd，进入27017客户端，进行配置
 
var rsconf = {
	_id:'rs',
	members:[
		{_id:0,host:'127.0.0.1:27017'},
		{_id:1,host:'127.0.0.1:27018'},
		{_id:2,host:'127.0.0.1:27019'}
	]
}
 
 
这时候我们可以打印rsconf来看一下
 
 
5.将配置初始化
 
rs.initiate(rsconf)
 
 
使用
 
 
需要执行rs.slaveOk()才能查看数据
 
 
 
删除
 
rs.remove('127.0.0.1:27019')
 
删除节点后，如果想再添加，必须重新配置才可以
 
查看数据库集
 
rs.status()