个人工作SQL整理（大佬勿ru）

书山有鹿

已于 2024-04-26 14:02:36 修改

阅读量143

点赞数

分类专栏： mysql 文章标签： sql 数据库 mysql

于 2023-06-01 14:12:31 首次发布

本文链接：https://blog.csdn.net/weixin_44591357/article/details/130983000

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

个人常用SQL整理（MYSQL）

查看运行事务进程

-- 查询运行事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 查询运行进程
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST P ;
-- 查询运行进程
SHOW PROCESSLIST;
-- 杀死某个进程（一般锁表后使用）
kill 303218; -- kill 进程id

清表语句

1. 清除表数据还要删除表结构
drop table 表名;
-- 或者
drop table if exists 表名;
2. 用于清空表中的数据，但不会删除表结构。
-- 会将表中的所有数据一次性删除，而不是逐条删除，故效率1远远大于DELETE；
-- 自增列会被置为初始值；
-- 删除数据后不会写服务器log。
truncate table 表名;
3. 有条件删除，删除部分数据
delete from 表名 where 条件;

统计数量

假设表中id为主键，erpcode有为空的值存在。
name语句1、2、3统计的是表中数量，语句4统计的是erpcode不为空的数量，所以非特殊情况查询总数使用count(主键)、count(*)或者count(1)。

1. select count(*) from RDS_EQP_NERESOURCE_DF;
2. select count(id) from RDS_EQP_NERESOURCE_DF;
3. select count(1) from RDS_EQP_NERESOURCE_DF;
4. select count(erpcode) from RDS_EQP_NERESOURCE_DF;

执行效率：通常情况下count(主键) > count(1) > count(*)
若表中只有一列，那 count(*) > count(1）

字段

– 添加字段

ALTER TABLE 表名 ADD COLUMN 字段名 VARCHAR(8) DEFAULT NULL COMMENT '资产所属单位';

– 修改字段

ALTER TABLE 表名 MODIFY COLUMN 字段名 TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '备注1';

索引

– 查询索引

show keys from 表名;

– 删除索引

ALTER TABLE 表名 DROP INDEX 索引名;

– 添加索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名) USING BTREE;			-- 唯一索引
CREATE INDEX 索引名 ON 表名(列名) USING BTREE; 				    -- 普索引
CREATE INDEX 索引名 ON 表名(列名1,列名2,列名3) USING BTREE;		-- 组合索引

– 查询SQL执行计划的信息（是否走索引）
在select语句之前增加EXPLAIN关键字，执行后MySQL就会返回执行计划的信息。

查询A表未在B表关联的数据

-- 利用外连接特性，查询A表设备类型未能映射或为空
SELECT A.* FROM A LEFT JOIN B ON A.DEVICE_TYPE = B.MAP_TYPE_ID WHERE B.MAP_TYPE_ID IS NULL;

常用脚本整理

重新建表脚本，举个栗子

DROP PROCEDURE IF EXISTS SCHEMA_CHAGE;
DELIMITER //
  -- 单    号: 
  -- 功    能: 删除表XX并重新创建（无需备份数据）
  -- 作    者: 
  -- 脚本类型: 
  -- 时    间: 
  -- 重要说明: 
CREATE PROCEDURE SCHEMA_CHAGE() 
BEGIN 
  DECLARE V_COMPID VARCHAR(50); 
  DECLARE V_CSZ VARCHAR(10);
  DECLARE V_SYEAR INT(10); 
  DECLARE V_EYEAR INT(10); 
  DECLARE V_NYEAR INT(10); 
  DECLARE V_COUNT INT(10); 

  IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=database() AND TABLE_NAME='表名') THEN
	DROP TABLE 表名;
    CREATE TABLE 表名(
      ACCO_ID VARCHAR(36)  NOT NULL   COMMENT '主键编号',
      COMP_ID VARCHAR(200)  NULL    COMMENT '单位ID',
      EDIT_RELATION VARCHAR(200)  NULL    COMMENT '编报关系名称',
      BUDGET_START_YEAR INT(10)  NULL    COMMENT '预算开始年度',
      BUDGET_END_YEAR INT(10)  NULL    COMMENT '预算结束年度',
      ENABL_STA VARCHAR(20)  NULL    COMMENT '启用状态',
      IS_DISTRI INT(2)  NULL    COMMENT '是否下发',
      CRE_TIME DATETIME  NULL   COMMENT '创建时间',
      LAST_UP_TIME DATETIME   NULL   COMMENT '最后更新时间',
      FOUNDER VARCHAR(36)  NULL    COMMENT '创建人名称',
      LS_MODIFIER VARCHAR(36)  NULL    COMMENT '最后修改人') COMMENT='下达上报关系主表';

    END IF;
  COMMIT;
END //
DELIMITER ;
CALL SCHEMA_CHAGE();

修复表缺失字段脚本，举个栗子：

DROP PROCEDURE IF EXISTS SCHEMA_CHAGE; 
DELIMITER // 
  -- 单    号: 
  -- RTC单号： 
  -- 功    能: prj_inv_bud_edit_link添加缺失字段
  -- 作    者:  
  -- 脚本类型: 
  -- 时    间: 2022-7-14 16:58:13
  -- 脚本路径: 
CREATE PROCEDURE SCHEMA_CHAGE() 
BEGIN 
  DECLARE V_COMPID VARCHAR(50);
  DECLARE V_CSZ VARCHAR(10);
  DECLARE V_SYEAR INT(10); 
  DECLARE V_EYEAR INT(10); 
  DECLARE V_NYEAR INT(10); 
  DECLARE V_COUNT INT(10);


    IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_SCHEMA=database() AND TABLE_NAME='表名' AND COLUMN_NAME='字段名') THEN
      SET @V_SQL='ALTER TABLE 表名 ADD COLUMN 字段名 INT(2) DEFAULT 0 COMMENT ''是否下发''';
      PREPARE V_SQL FROM @V_SQL;
      EXECUTE V_SQL;
      DEALLOCATE PREPARE V_SQL;
    END IF;
	
	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_SCHEMA=database() AND TABLE_NAME='表名' AND COLUMN_NAME='字段名') THEN
      SET @V_SQL='ALTER TABLE 表名 ADD COLUMN 字段名 DATETIME COMMENT ''下发时间''';
      PREPARE V_SQL FROM @V_SQL;
      EXECUTE V_SQL;
      DEALLOCATE PREPARE V_SQL;
    END IF;

  COMMIT;
END //
DELIMITER ;
CALL SCHEMA_CHAGE();

添加索引脚本，举个栗子：

DROP PROCEDURE IF EXISTS SCHEMA_CHAGE; 
DELIMITER // 
  -- 元数据申请编号: 
  -- RTC单号: 
  -- 功     能: 设备层历年成本池添加索引
  -- 作     者: 
  -- 脚本类型: 
  -- 生成时间: 2023-05-29 14:06:11 
  -- 脚本路径: 
CREATE PROCEDURE SCHEMA_CHAGE() 
BEGIN 
  DECLARE V_COMPID VARCHAR(50);
  DECLARE V_CSZ VARCHAR(10);
  DECLARE V_SYEAR INT(10); 
  DECLARE V_EYEAR INT(10); 
  DECLARE V_NYEAR INT(10); 
  DECLARE V_COUNT INT(10);
  

  -- 判断索引是否存在，不存在则创建
  IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.statistics WHERE TABLE_SCHEMA=database() AND TABLE_NAME='表名' AND index_name='索引名') THEN
	  CREATE INDEX IND_EFCA_EFTOTALCPOOL_CYEARFOCUSON ON T_EFCA_EFTOTALCOSTPOOL(CYEAR, FOCUSON_DIREC) USING BTREE;
  END IF;
  COMMIT;
END //
DELIMITER ;
CALL SCHEMA_CHAGE();

行列转换

SELECT 
(CASE WHEN E.VOLT_LVL_NO='37' THEN '1000kV'
	 WHEN E.VOLT_LVL_NO='36' THEN '750kV'
	 WHEN E.VOLT_LVL_NO='35' THEN '500kV'
	 WHEN E.VOLT_LVL_NO='34' THEN '330kV'
	 WHEN E.VOLT_LVL_NO='33' THEN '220kV'
	 WHEN E.VOLT_LVL_NO='32' THEN '110kV'
	 WHEN E.VOLT_LVL_NO IN ('30','31') THEN '66kV（72.5kV）'
	 WHEN E.VOLT_LVL_NO='25' THEN '35kV'
	 WHEN E.VOLT_LVL_NO='91' THEN '±1100kV'
	 WHEN E.VOLT_LVL_NO='85' THEN '±800kV'
	 WHEN E.VOLT_LVL_NO='84' THEN '±660kV'
	 WHEN E.VOLT_LVL_NO='70' THEN '±600kV'
	 WHEN E.VOLT_LVL_NO='93' THEN '±550kV'
	 WHEN E.VOLT_LVL_NO='83' THEN '±500kV'
	 WHEN E.VOLT_LVL_NO='82' THEN '±400kV'
	 WHEN E.VOLT_LVL_NO='88' THEN '±320kV'
 	 WHEN E.VOLT_LVL_NO IN ('51','52','53','54','55','56','60','70','71','72','73','76','77','78','79','80','81','87','90','94','95') THEN '±320kV以下' END) AS VOLT_LVL_NO,
 SUM(IF(E.AVGYEAR = 1, ROUND(E.ZCYZ, 2), 0)) AS age_1,
 SUM(IF(E.AVGYEAR = 2, ROUND(E.ZCYZ, 2), 0)) AS age_2,
 SUM(IF(E.AVGYEAR = 3, ROUND(E.ZCYZ, 2), 0)) AS age_3,
 SUM(IF(E.AVGYEAR = 4, ROUND(E.ZCYZ, 2), 0)) AS age_4,
 SUM(IF(E.AVGYEAR = 5, ROUND(E.ZCYZ, 2), 0)) AS age_5,
 SUM(IF(E.AVGYEAR = 6, ROUND(E.ZCYZ, 2), 0)) AS age_6,
 SUM(IF(E.AVGYEAR = 7, ROUND(E.ZCYZ, 2), 0)) AS age_7,
 SUM(IF(E.AVGYEAR = 8, ROUND(E.ZCYZ, 2), 0)) AS age_8,
 SUM(IF(E.AVGYEAR = 9, ROUND(E.ZCYZ, 2), 0)) AS age_9,
 SUM(IF(E.AVGYEAR > 10, ROUND(E.ZCYZ, 2), 0)) AS age_10
FROM(
	SELECT A.VOLT_LVL AS VOLT_LVL_NO,
		(TIMESTAMPDIFF(YEAR, A.ASST_CAPITAL_DT, '2022-12-31' ) + 1) AVGYEAR,
	  	(IFNULL(B.accu_acquire_prod_cost, 0) + IFNULL(B.year_output_val_busi, 0)) ZCYZ
  	FROM 表1 A
LEFT JOIN 表2 B ON A.MAIN_ASST_NO = B.ASST_NO
	WHERE A.ASST_ST='ZZZY' -- 资产使用状态：在用
	AND A.FIX_ASSET_TYP='110000001' -- 资产细类代码
	AND SUBSTR(B.deprec_tm, 1, 4) = 2022 -- 年份
) E
GROUP BY E.VOLT_LVL_NO;