psql数据导出导入｛COPY｝

2019-11-11 / 数据库 /

--进入数据库使用copy 导出数据为表格--

1. 例子1
\COPY (SELECT U.user_name,U.user_pass,U.money,A.agents_name,U.qq,U.tel,H.pay_card,H.pay_num,H.pay_name 
FROM user_list AS U LEFT JOIN history_bank AS H ON U.id = H.uid LEFT JOIN agents_list AS A ON 
U.recommend_id=A.id) to '/tmp/beifen.csv' with csv header;

2. 例子2

\COPY (select * from user_list) to '/user_list.csv' with csv header;

此种方式导出为csv 格式如需要 Excel 请使用 wps 自行转换

3. 例子3
\COPY (select * from easy_lot_result where lot_id=10902 and opentime>='2019-07-27 00:00:00' order by 
id asc) to '/tmp/1.sql' ;
4. 列子4
将某个用户真人注单信息导出清除再导入    
\copy (select * from live_order where live_username = 'amyaa785378959') to '/home/rose/1.sql';
delete from live_order where  live_username = 'amyaa785378959';
\copy live_order from '/home/rose/1.sql';