记录mysql的一些用法

记录一些有用的sql用法

CREATE DATABASE storemng DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci; //创建数据库
ALTER TABLE INFO_APPLICATION add APPKEY VARCHAR(40);

delete FROM `rd_apply` where id in (SELECT id FROM (SELECT id FROM `rd_apply` GROUP BY realname ,idcard HAVING count(1)>1) t)
show databases; #查看mysql有哪些库
use certstamps; #应用某些库
show tables; #查看库里面有哪些表
desc t_api_log; #查看某个表具体的结构
truncate t_api_log; #截断表，自增长重新统计
select * from t_api_log; #查看表内数据
select count(*) from t_api_log #查询总条数
insert into t_api_log (datsa) values ('sdfsdfsd') #插入语句
update t_api_log set id = 's'; #更新语句
select * from t_api_log where license='sss' order by create_time DESC  limit 0,10;#分页查询语句
delete from t_api_log ;#删除表的语句

//扩充表属性长度
alter table cert_pdf_info modify column cert_dn varchar(180);

//按月份统计每月的数据量。
select date_format(create_time,'%Y-%m') k,sum(1) v from t_log_login 
where create_time >= '2019-01-01 00:00:00' and create_time <= '2019-12-31 59:59:59'
group by date_format(create_time,'%Y-%m');
//截取数组库中查询字段，去重，模糊查询
SELECT DISTINCT SUBSTR(logtext,9,LOCATE(',', logtext)-9) t  FROM `logging` where logtext like '证书过期%'
//内连接查询。
select a.person_real_name sname,a.person_id_card_num card,b.phone phone ,a.user_account_id ids from account_person_info a INNER JOIN account_info b on a.user_account_id = b.user_account_id

//子查询中存在条件查询，效率优化，多一层查询，效率高
select a.person_real_name sname,a.person_id_card_num card,b.phone phone ,a.user_account_id ids from account_person_info a INNER JOIN account_info b on a.user_account_id = b.user_account_id where a.person_real_name IN (SELECT t from (SELECT DISTINCT SUBSTR(logtext,10,LOCATE(',', logtext)-10) t  FROM `logging` where logtext like '有效期错误%') as h);

//mysql中截取两个字符串之间的内容。
SELECT DISTINCT SUBSTR(logtext,LOCATE('CN=', logtext)+3,LOCATE(', OU', logtext)-LOCATE('CN=', logtext)-3) t  FROM `logging`

//备份数据库  -B 备份的过程中包含创建数据库语句 --ignore-table 设置需要忽略备份的表
mysqldump -uroot -p123456 -B gm_sigverfiserver --ignore-table=gm_sigverfiserver.t_role --ignore-table gm_sigverfiserver.t_crl --ignore-table gm_sigverfiserver.t_auth_log --ignore-table gm_sigverfiserver.t_log_api --ignore-table gm_sigverfiserver.t_manager > dump.sql
//根据条件导出数据库表
mysqldump -uroot -p -h127.0.0.1 -B certstamp_appid --tables t_stamp -w " id in (1998,2001,2002) " > error_stamp3.sql

//恢复数据库
mysql -uroot -p123456 < dump.sql
//也可通过source 命令完成恢复
mysql -uroot -p123456  //登录
use 数据库名称
source /root/dump.sql;

删除mysql的外网访问权限
use mysql
delete from user where  user = "root" and host ='%';
flush privileges;

SELECT `subject`,not_end,
CASE
  business_id 
  WHEN 1 THEN
  '新制' 
  WHEN 2 THEN
  '年检' 
	WHEN 3 THEN
  '更新' 
	WHEN 4 THEN
  '补发' 
 END AS business_id,
 CASE
  `status` 
  WHEN 1 THEN
  '未安装' 
  WHEN 2 THEN
  '正常' 
	WHEN 4 THEN
  '补发失效' 
	WHEN 5 THEN
  '补发中' 
	WHEN 6 THEN
  '下证失败'
 END AS `status`
FROM t_cert where issuer = 'HNCA' AND status != 2 ORDER BY not_end DESC;


SELECT `subject` as 主题,sn as 证书序列号, stamp_sn as 印章序列号 , create_time AS 制作时间 FROM `t_stamp` where managerid = '321dd2f2-407c-4dd8-87fa-4081030256ed' AND opt_result = 'ukey章新制成功！'
select *,count(*) as count from t_stamp where managerid = '321dd2f2-407c-4dd8-87fa-4081030256ed' AND opt_result = 'ukey章新制成功！'  GROUP BY sn having count >= 2;
//查询数据库表所占空间大小
select table_name as '表', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables where table_schema='authgate' group by table_name;

select table_schema,table_name, table_rows,truncate(data_length/1024/1024,2) from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema') and table_rows > 500 order by table_schema;

# 用另一个表的查询数据作为新表的创建数据
create table test.t_cert select * from yunguan.t_cert where status =1;

# AA：bb
# cc:dd展示
select * from jh_cert_collected\G;
#根据已有数据创建表
create table t_cert select * from yunguan.t_cert where status = 2 and institution_id = 134 and name = '尚闫';



#################################证书使用情况统计#########################################################
create TABLE t_cert_jy SELECT name,idcard,REPLACE(`subject`,",",", ") `subject` FROM t_cert WHERE `status` = 2 AND institution_id = 130;
create TABLE t_cert_jy2 SELECT a.name,a.idcard, a.`subject`, b.staff_phone FROM t_cert_jy a INNER JOIN t_staff b WHERE a.idcard = b.staff_certificates_number;
SELECT * FROM t_staff where staff_certificates_number in (SELECT DISTINCT idcard FROM t_cert_jy);
SELECT name,idcard,sum(1) num FROM t_cert where `status` = 2 GROUP BY idcard HAVING num > 1;
SELECT DISTINCT idcard FROM t_cert_jy;
SELECT idcard FROM t_cert_jy;
create table t_get_img select DISTINCT `subject` from t_api_log WHERE create_time > '2022-01-01 00:00:00';
SELECT * FROM t_cert_jy WHERE `subject`  not IN (select * from t_get_img2);
create table t_get_img2 select REPLACE(`subject`,"S=","ST=") subject FROM t_get_img;
select count(*)  from t_get_img2;
SELECT count(idcard) FROM t_cert_jy;
SELECT * FROM t_get_img2 WHERE `subject` not in (SELECT `subject` from t_cert_jy);
SELECT * FROM t_cert_jy2 WHERE `subject` IN (select * from t_get_img2);
SELECT * FROM t_cert_jy2 WHERE `subject` NOT IN (select * from t_get_img2);

#################################按条件交换两列值#########################################################
UPDATE t_cert AS a,t_cert AS b SET a.validity_start = b.validity_end,a.validity_end = b.validity_start WHERE	a.id = b.id AND a.validity_start > a.validity_end;