1.创建数据库语法
--创建名称为“testdb”数据库，并设定编码集为utf8
CREATE DATABASE zabbix; 

2.新建用户
--创建了一个名为：test 密码为：1234 的用户
create user 'zabbix'@'localhost' identified by '1234';
create user 'zabbix'@'%' identified by '1234';
#host – 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,  如 果想让该用户可以从任意远程主机登陆, 
可以使用通配符%

3.查询用户
--查询用户
select user,host from mysql.user;

4.删除用户
--删除用户“test”
drop user test@localhost ;
--若创建的用户允许任何电脑登陆，删除用户如下
drop user test@'%';

5.更改密码
--方法1，密码实时更新；修改用户“test”的密码为“1122”
set password for test =password('1122');
--方法2，需要刷新；修改用户“test”的密码为“1234”
update  mysql.user set  password=password('1234')  where user='test'
--刷新
flush privileges;    

6.用户分配权限
GRANT 权限名字(多个权限用","号隔开) ON 数据库名.表名 TO '用户名'@'允许登录的ip地址' [IDENTIFIED BY '密 
码'];
例子：
GRANT ALTER,DELETE,INDEX,INSERT,SELECT,UPDATE ON zabbix_server.* TO 'zabbix'@'localhost' IDENTIFIED BY 
'123456789';
#给zabbix用户授权所有库所有权限
GRANT ALL ON *.* TO 'zabbix'@'%' IDENTIFIED BY '123456789';
--刷新
flush privileges;       

查看用户获得的授权:
SHOW GRANTS FOR 'USERNAME'@'HOST';
SHOW GRANTS FOR zabbix;

--进入数据库使用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';

SELECT * FROM (表名) INTO OUTFILE '/usr/local/test.txt' ;
SELECT * FROM (表名) INTO OUTFILE '/tmp/1.sql' ;

#数据库增删改查
update easy_lhc_bet set bet_issue ='2019113' where bet_issue ='2019114' and lhc_name='lhc';

update easy_admin_ip_permit set ip_list ='["115.84.117.102","210.5.58.161","203.177.152.7"]' where 
id='8';

select id,oid,site_id,user_name,tel,email,country,city,pay_name from user_list where user_name 
='ok3698';

insert into easy_lot_kill (id,lot_id,percent,bets_num,bets_money,user_num,play_num) values 
(3,12701,80,0,0,0,0);

update easy_lot_result set opentime=opentime-interval '1 days' where lot_id=12101 and opentime>'2019-1 
0-02';

update easy_lot_result set date='4,6,6' where lot_id ='11101' and issue='20191016017';

update  easy_user_config set value='' where name='email' and value !='' and value not like '%@%';

update  easy_user_config set value='' where name='email' and value !='' and value not like '%@%' limit 
300;

select count(*) from easy_user_config where name='email' and value !='' and value not like '%@%' limit 
300;

select * from easy_user_config where name='email' and value !='' and value not like '%@%' limit 300;

select count(*) from easy_user_config where name='email' and value !='' limit 100;

select user_name,pay_name,tel from user_list where tel like '1%'；

select user_name,pay_name,tel,logintime,virtual from user_list where  tel  ~* '[A-Z]';

select user_name,pay_name,tel,logintime from user_list where tel!='' and virtual='t' order by id limit 
100;

-- pg_dump和psql看清楚在使用，否则容易造成数据丢失操作 --

-- 导出全库 --

pg_dump -U 用户 -h 主机 -d 数据库名 [-f|>] 导出的文件名
-- 导出单个表 --

pg_dump -U 用户 -h 主机 数据库名 -t 表名 [-f|>] 导出的文件名
-- 导入全库 --

psql -U 用户 -h 主机 -d 数据库名 [-f|<] 导入的文件名
-- 导入单个表 --

psql -U 用户 -h 主机 数据库名 -t 表名 [-f|<] 导入的文件名