日常操作数据库总结

###############################################################################################################
mysql环境
###############################################################################################################
#查看mysql版本
select version();

#查看用户权限
show grants for test;

#mysl查看binlog过期时间（0表示永远不清理）
show variables like 'expire_logs_days';
#mysql显示binlog最大文件大小
show variables like 'max_binlog_size';

#mysl设置过期时间（单位：天）
set global expire_logs_days=1;

#立即清除binlog命令
flush logs;

#设为0后，在Master数据库上执行的语句都不记录binlog（1表示记录binlog）
set sql_log_bin=0;
#所有在数据库上执行的语句都不记录到binlog
set global sql_log_bin=0

#MySQL支持3种注释风格：
1、‘#’字符从#号至行尾。
2、从‘-- ’序列到行尾。请注意‘-- ’(双破折号)注释风格要求第2个破折号后面至少跟一个空格符(例如空格、tab、换行符等等)。
该语法与标准SQL注释语法稍有不同，后者将在1.8.5.7, “‘--’作为注释起始标记”中讨论。
3、从/*序列到后面的*/序列。结束序列不一定在同一行中，因此该语法允许注释跨越多行。

#最大连接数
show variables like '%max_connections%';
#当前连接数等信息
show status like 'Threads%';

#乱码问题
1、数据库（MySQL）确保是utf8
2、jboss配置确保是utf8
3、如果还不行，要联系相关人员反映情况

#命令行连接数据库
mysql -h 10.27.176.229 -u devadmin -p

#CHAR和VARCHAR区别
CHAR 和 VARCHAR 很类似，都用来保存 MySQL 中较短的字符串。二者的主要区别在于存储方式的不同：CHAR 列的长度固定为创建表时声明的长度，
长度可以为从 0～255的任何值；而VARCHAR列中的值为可变长字符串， 长度可以指定为0～255 （5.0.3以前） 或者65535 （5.0.3以后） 之间的值。
在检索的时候， CHAR 列删除了尾部的空格， 而 VARCHAR 则保留这些空格。

#Innodb引擎中,数据与索引文件默认会存储在ibdata1文件中，查看mysql中数据文件和索引文件存储方式命令
show variables like 'innodb_file_per_table%';
ON：分开存储
OFF：同一个文件存储
#设置数据文件和索引文件分开存储
set global innodb_file_per_table=1;

#新建用户（参考文章：https://www.cnblogs.com/wuxunyan/p/9095016.html）
create user "username"@"host" identified by "password";
1.mysql->create user 'test'@'localhost' identified by '123';
2.mysql->create user 'test'@'192.168.7.22' identified by '123';
3.mysql->create user 'test'@'%' identified by '123';
/*host="localhost"为本地登录用户，host="ip"为ip地址登录，host="%"，为外网ip登录*/

#授权
grant privileges on databasename.tablename to 'username'@'host' IDENTIFIED BY 'PASSWORD';

#查看所有用户的权限
show grants;
#查看用户test的权限
show grants for test;

#删除用户
drop user 'username'@'host';

#碎片化产生的原因
删除数据就会导致页（page）中出现空白空间，大量随机的DELETE操作，必然会在数据文件中造成不连续的空白空间。而当插入数据时,这些空白空间则会被利用起来，
于是造成了数据的存储位置不连续。
对于大量的UPDATE，也会产生文件碎片化 ， Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂（page split），频繁的页分裂，页会变得稀疏，
并且被不规则的填充，所以最终数据会有碎片。

#碎片化带来的问题
表的数据存储也可能碎片化。然而数据存储的碎片化比索引更加复杂。有三种类型的数据碎片化。##下面部分内容摘自【高性能MySQL】##
行碎片（Row fragmentation）
这种碎片指的是数据行被存储为多个地方的多个片段。即使查询只从索引中访问一行记录。行碎片也会导致性能下降。
行间碎片（Intra-row fragmentaion）
行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
剩余空间碎片（Free space fragmentation）
剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据。从而造成浪费。

#碎片化整理，即重组（例如对表cs_recon_statement_detail重组）
alter table cs_recon_statement_detail ALGORITHM=inplace, lock=none, engine='InnoDB';

#查询表中碎片情况
#查看数据库eppssassit中表的碎片情况
show table status from eppssassit;
#查看数据库eppssassit中表的碎片情况，根据碎片大小倒序排列
select round(sum(data_length/1024/1024),2) as data_length_MB,
round(sum(index_length/1024/1024),2) as index_length_MB,
round(sum(data_free/1024/1024),2) as data_free_MB,
table_name
from information_schema.tables where TABLE_SCHEMA= 'eppssassit' group by table_name order by 3 desc;

#查看慢查询日志是否开启
show variables like 'slow_query_log%';

 

 

###############################################################################################################
DDL（Data Definition Language，数据定义语言）
###############################################################################################################
适用范围：对数据库中的某些对象(例如，database,table)进行管理，如Create,Alter和Drop等。
#mysql DDL
#建库
create database if not exists `cssmock`;
#建表
CREATE TABLE IF NOT EXISTS `cs_recon_accrued_detail` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`ORDER_NO` varchar(32) DEFAULT NULL COMMENT '明细订单号，RAD开头',
`DATA_TYPE` varchar(2) DEFAULT NULL COMMENT '数据类型:15外催佣金',
`BP_NO` varchar(16) DEFAULT NULL COMMENT '商户号',
`MAIN_COMPANY_CODE` varchar(16) DEFAULT NULL COMMENT '主体公司代码',
`PRODUCT_CODE` varchar(8) NOT NULL DEFAULT '01010001',
`TRANDATE` date DEFAULT NULL COMMENT '交易日期',
`PAY_TIME` timestamp NULL DEFAULT NULL COMMENT '交易时间',
`ACCRUED_STATUS` varchar(2) DEFAULT '02' COMMENT '计提状态: 01-不需要计提 02-未计提 03-已计提',
`ACCRUED_CREDENCE` varchar(16) DEFAULT NULL COMMENT '计提凭证',
`CREATE_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
`UPDATE_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
`REMARK` varchar(128) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`ID`),
UNIQUE KEY `UI_cs_recon_accrued_detail_DATA_TYPE_ORD_SEQ_SERIAL_NO` (`RECON_SEQ_NO`,`DATA_TYPE`),
KEY `I_CS_RECON_ACCRUED_DETAIL_TRANDATE` (`TRANDATE`),
KEY `I_CS_RECON_ACCRUED_DETAIL_BUSINESS_ORDER_NO` (`ORDER_NO`),
KEY `I_CS_RECON_ACCRUED_DETAIL_BP_NO_DATE` (`BP_NO`,`TRANDATE`),
KEY `I_CS_RECON_ACCRUED_DETAIL_FILE_BATCH_NO` (`FILE_BATCH_NO`),
KEY `I_CS_RECON_ACCRUED_DETAIL_ACCRUED_STATUS` (`ACCRUED_STATUS`),
KEY `I_CS_RECON_ACCRUED_DETAIL_MAIN_COMPANY_CODE` (`MAIN_COMPANY_CODE`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='对账计提明细表';

#删表
DROP TABLE IF EXISTS tasks, nonexistent_table;

#创建普通索引
CREATE INDEX I_CS_DAILY_SUMMARY_BP_NO ON CS_DAILY_SUMMARY
(
BP_NO
);

#创建唯一索引
CREATE UNIQUE INDEX UI_CS_DAILY_SUMMARY_BP_NO_TRANDATE ON CS_DAILY_SUMMARY
(
BP_NO,
TRANDATE,
CLEAR_TYPE,
SUMMARY_TYPE,
PRODUCT_CODE,
MAIN_COMPANY_CODE,
FILE_BATCH_NO
);

#删除索引
alter table CS_DAILY_SUMMARY drop index I_CS_DAILY_SUMMARY_BP_NO;

#新增字段
alter table CS_DAILY_SUMMARY add column SETTLE_CATEGORY varchar(2) default '1' COMMENT '(1时间区间/2文件)' after PRODUCT_CODE;

#修改字段（只修改类型等，不涉及字段名修改）
alter table CS_DAILY_SUMMARY modify column SETTLE_CATEGORY varchar(10);

#修改字段名
alter table CS_DAILY_SUMMARY CHANGE SETTLE_CATEGORY SETTLE_CATEGORY_01 varchar(10);

#字段添加注释
alter table CS_DAILY_SUMMARY modify column SETTLE_CATEGORY varchar(10) comment '修改后的字段注释';

#删除字段
alter table CS_DAILY_SUMMARY drop column SETTLE_CATEGORY;
alter table CS_DAILY_SUMMARY drop column SETTLE_CATEGORY_01,drop column SETTLE_CATEGORY_02,drop column SETTLE_CATEGORY_03;

#给表添加注释
alter table CS_SETTLE_BIZ_TYPE_CONFIG comment '结算单业务类型配置表';

#mysql索引
1.普通索引默认是包含主键列的
2以下sql中使用了聚合函数，唯一过滤字段name已建索引，并且聚合函数中字段是主键或该索引字段，虽然name是不等于表达式，仍然可以走索引（“覆盖索引”，不需要查表）
select sum(id)或sum(name) from t_address where name <> 'aaa';

#有时Mysql优化器的选择的并不是最优的索引，通过force index指令可以指定本次查询强制使用哪个索引
update cs_recon_statement_detail
force index(I_CS_RECON_STATEMENT_DETAIL_FILE_BATCH_NO)
set SUMMARY_FLAG = '1',
UPDATE_TIME = '2019-09-18 10:57:32.366'
where FILE_BATCH_NO = 'SWS700578802017040100001617'
and PRODUCT_CODE = '01015632'
and TRANDATE = '2017-04-01 00:00:00'
and SUMMARY_FLAG = '0'

select CustName,count(1) c from WorkOrder force index(ix_namedate)
where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc;

#可以使用ignore index()，这个指令可以强制Mysql在查询时，不使用某索引
select CustName,count(1) c from WorkOrder ignore index(ix_date)
where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc;

#java中Date类型的格式化
yyyy-MM-dd HH:mm:ss
以上分别是年月日时分秒的格式，大小写一定要区分清楚，否则会出现非预期的结算
例如：当前时间：2019-09-30 20:55:59，大小写不同的格式化结果如下：
yyyyMM：201909
yyyymm：201955

#修改表名
alter table ts01 rename to ts01_new;

###############################################################################################################
DML（Data Manipulation Language，数据操纵语言）
###############################################################################################################
适用范围：对数据库中的数据进行一些简单操作，如insert,delete,update,select等。
#MySQL执行sql文件
source d:\test\ss.sql 或者 \. d:\test\ss.sql

#mysql插入多行
INSERT INTO users(name, age) VALUES('姚明', 25),('比尔.盖茨', 50),('火星人', 600);

#MySQL设置空值
update tb set column1 = null

#同时修改多个字段
ALTER TABLE `cscs_accountant_config_info`
DROP COLUMN `SETTLE_CYCLE`,
DROP COLUMN `FMS_FLAG`,
MODIFY COLUMN `ACCRUED_FLAG` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '0-不汇总记提 1-汇总记提' AFTER `PRODUCT_CODE`,
ADD COLUMN `DETAIL_ACCRUED_FLAG` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '0:不明细计提，1:明细计提' AFTER `ACCRUED_FLAG`,
DROP INDEX `UI_CSCS_ACCOUNTANT_CONFIG_INFO_PRODUCT_CODE` ,
ADD UNIQUE INDEX `UI_CSCS_ACCOUNTANT_CONFIG_INFO_PRODUCT_CODE` (`PRODUCT_CODE`) USING BTREE ;

#添加全文索引
#FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表
#全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配
#fulltext索引配合match against操作使用，而不是一般的where语句加like。目前只有char、varchar，text 列上可以创建全文索引
ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(col_name);

#如果删除字段，或修改字段名，会对基于该字段的索引有什么影响
如果是单列索引，删除字段时，该索引将自动被删除；修改字段名时，该索引将自动指向新字段名

#允许用户myuser使用mypassword从任何主机连接到mysql服务器
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

#允许用户myuser使用mypassword从ip为192.168.1.6的主机连接到mysql服务器的dk数据库
GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

#100表示查询偏移量（从第100条开始查询），50表示查询出的条数
select * from t limit 100,50;

#删除表中重复记录，只保留id最小的那条（prodcut_code为分组字段）
delete from a
using cscs_accountant_config_info as a, cscs_accountant_config_info as b
where (a.id > b.id)
and (a.product_code = b.product_code);

DELETE FROM cscs_accountant_config_info WHERE id NOT IN (SELECT min(id) AS id FROM cscs_accountant_config_info GROUP BY product_code);

DELETE FROM cscs_accountant_config_info a WHERE
a.product_code IN (SELECT product_code FROM vitae GROUP BY product_code HAVING count(*) > 1)
AND id NOT IN (SELECT min(id) id FROM cscs_accountant_config_info GROUP BY product_code HAVING count(*) > 1);

#删除表中所有数据，无法回滚
TRUNCATE [TABLE] 表名;

#分页查询慢，查询结果有几十万时，在limit M,N中，当M越大，查询越慢
#原查询
select * from cs_recon_statement_detail where trandate='2018-12-26' limit 810527,10000
持续时间 1 query: 2.106 sec
#优化后查询
select t1.* from cs_recon_statement_detail t1,
(select id from cs_recon_statement_detail where trandate='2018-12-26' limit 810527,10000) t2 where t1.id=t2.id
持续时间 1 query: 0.312 sec
原因：优化的sql中，子查询只遍历了索引文件（索引文件含sql中所有用到的字段），然后根据id查表数据，因为索引文件量比表数据小很多（索引文件在内存中），所以相对快些

#修改表中字段信息
UPDATE 表名 SET 字段名 = REPLACE(替换前的字段值, '替换前关键字', '替换后关键字') WHERE 字段名='xxx';

#mysql中，当执行update操作时，字段UPDATE_TIME会自动更新为当前UPDATE操作时的时间
`UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

#插入记录时
1）.如果该记录已存在（根据主键或唯一索引判断），则不再插入
insert ignore into
2).如果该记录已存在（根据主键或唯一索引判断），先删除后插入
replace into

#唯一索引字段（可能是多个字段组合）中为null时，唯一索引失效（即再来一笔该字段为null的记录，也可以入库），可以将null转成空串入库，这样唯一索引有效
另外，如果唯一索引字段中某个字段默认值时null的话，页面上对该记录进行修改时，即使不修改该字段，该字段值也会默认是空串""，导致该字段值前后不一样

#in中子查询时，外部查询不走索引
select
ID, BP_NO, TRANS_DATE,MAIN_COMPANY_CODE,REQUEST_SERIAL_NO,REMOTE_FILE,TASK_TYPE,STATUS, FILE_BATCH_NO,CREATE_TIME,UPDATE_TIME,
DESCRIPTION,UPDATER
from cs_ftp_schedule
where REQUEST_SERIAL_NO in (select REQUEST_SERIAL_NO
from cs_ftp_schedule
where
FILE_BATCH_NO = 'SWS700563882016101700000642'
)
改造后，子查询和外部查询全部走索引（关联查询）
select
ID, BP_NO, TRANS_DATE,MAIN_COMPANY_CODE,a.REQUEST_SERIAL_NO,REMOTE_FILE,TASK_TYPE,STATUS, FILE_BATCH_NO,CREATE_TIME,UPDATE_TIME,
DESCRIPTION,UPDATER
from cs_ftp_schedule a,
(select REQUEST_SERIAL_NO
from cs_ftp_schedule
where
FILE_BATCH_NO = 'SWS700563882016101700000642'
) b where a.REQUEST_SERIAL_NO = b.REQUEST_SERIAL_NO
或者这样写，
select
a.ID, a.BP_NO, a.TRANS_DATE,a.MAIN_COMPANY_CODE,a.REQUEST_SERIAL_NO,a.FILE_BATCH_NO
from cs_ftp_schedule a join cs_ftp_schedule b
on a.REQUEST_SERIAL_NO = b.REQUEST_SERIAL_NO where
b.FILE_BATCH_NO = 'SWS00700473812017090600005690'

#查询重复项id（随机显示一条）
select any_value(id)
from cs_daily_summary
group by BP_NO,TRANDATE,CLEAR_TYPE,SUMMARY_TYPE,PRODUCT_CODE,MAIN_COMPANY_CODE,FILE_BATCH_NO having count(1)>1;
#删除重复项id（随机删一条）
delete from cs_daily_summary where id in (select id from (select any_value(id) id
from cs_daily_summary
group by BP_NO,TRANDATE,CLEAR_TYPE,SUMMARY_TYPE,PRODUCT_CODE,MAIN_COMPANY_CODE,FILE_BATCH_NO having count(1)>1) t);

#timestamp默认精确到毫秒吗
`CREATE_TIME` timestamp NULL DEFAULT null COMMENT '创建时间',
这种是不带毫秒的，如果存进来的数据有毫秒，会进行四舍五入后精确到秒，如：2020-01-08 10:38:10
`CREATE_TIME` timestamp(3) NULL DEFAULT null COMMENT '创建时间',
这种是带毫秒的，保留3位毫秒数，如：2020-01-08 10:38:10.295
注：mysql中函数now()，current_timestamp()只精确到秒，只有函数current_timestamp(3)时精确到毫秒

 

####################################################################################################################################
#DB2
####################################################################################################################################
#查询结果限制条数
select * from CSS.CS_REFUND_PROMOTION_DETAIL_201904 fetch first 100 rows only;

#多表关联查询
select a.*,b.* from TTMS.REMIT_ACCEPT_ORDER a,TTMS.FUND_TRANS_DETAIL b
where a.SERIAL_NO=b.SERIAL_NO and a.REMIT_TYPE='1' and a.UPDATE_TIME>='2019-12-17 16:00:35' and a.UPDATE_TIME<='2019-12-17 16:09:59' fetch first 500 rows only;

#修改字段长度
alter table TTMS.FUND_TRANS_DETAIL alter COLUMN SERIAL_NO SET DATA TYPE varchar(100);
reorg table TTMS.FUND_TRANS_DETAIL inplace allow write access notruncate table;

#测试环境下sql执行窗口无法直接运行reorg，可以通过以下封装执行：
CALL SYSPROC.ADMIN_CMD('reorg table TTMS.FUND_TRANS_DETAIL inplace allow write access notruncate table');
CALL SYSPROC.ADMIN_CMD('reorg table FCIP.TRANSFER_ORDER');