sql笔记

去空格 update 低保特困 set 身份证号=replace(replace(replace(身份证号,char(9),''),char(10),''),char(13),'');

筛选日期
AND DATE_FORMAT(b.FEE_OCUR_TIME,'%y%m%d')>220630 或者用between and

数据备份恢复
mysqldump -u root -p123 xy xyyy --where="FIXMEDINS_CODE in ('H61040200055')" >1.sql
mysqldump -u root -p --query="" database_name > output.sql
导入数据
mysql -u root -p123456 xy <E:\渭南审计数据\wnfj\psn_info_b.sql
远程
mysql -h192.1.11.1 -p3306 -u root -p123 数据库<*.sql

查看数据库编码
show variables like 'character%';

mysqldump --default-character-set=数据库编码 -u root -p applist >spplist.sql

方案二
在power shell 中使用这段命令
mysqldump -uroot -p --default-character-set gbk --databases amazing | Out-file -Encoding utf8 amazing.sql
原文链接：https://blog.csdn.net/OnedayIlove/article/details/104021527

取消drop语句

--skip-add-drop-table

取消LOCK语句

--skip-add-locks
mysqldump导出报
: Got error: 1044: Access denied for user 'xxx'@'xxx' to database 'xxx' when doing LOCK TABLES

解决方法：添加　--skip-lock-tables选项则能正常导出　
Mysqldump --skip-lock-tables -u root -p123 wnsj fee_list_d
原文链接：https://blog.csdn.net/dzjun/article/details/50454435

SELECT * INTO OUTFILE '/path/to/export/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM [表名] WHERE age >= 18;
导出为csv
SELECT * INTO OUTFILE 'E:/xy/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM fee_list_d
WHERE FIXMEDINS_CODE IN ('H61050200152','H61052600039','H61052800057','H61050200186','H61052300306');

进阶
SELECT setl_d.PSN_NO,FORMAT(setl_d.BEGNDATE,'%y')
INTO OUTFILE 'E:/xy/住院次数大于5.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM setl_d
JOIN fee_list_d ON setl_d.SETL_ID = fee_list_d.SETL_ID
GROUP BY setl_d.PSN_NO,FORMAT(setl_d.BEGNDATE,'%y')
HAVING COUNT(1)>5;

赋予内网之间连接
第一步 GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION //赋予任何主机访问数据的权限
第二步 FLUSH PRIVILEGES