libin's个人网站3.0-SQL模板

当前位置：首页 >> SQL >> SQL模板

SQL模板

2020-07-04 21:38:34 星期六阅读：2056

![](/static/images/article_images/1693752203.494427.jpeg)

####查看MySQL存储占用空间
注意：此种方法可能不准确，因为MySQL删除的数据可能也会被算在里面。
查看整个DB的占用空间
```
SELECT 
  concat(round(sum(data_length / 1024 / 1024), 2), "MB") AS data
FROM information_schema.tables
WHERE TABLE_SCHEMA = "db_name";
```
查看某个DB下每个表的占用空间，并按照占用空间倒序排序。需要输入库的名称
```
SELECT table_name
  , concat(round(data_length / 1024 / 1024, 2), "MB") AS data
FROM information_schema.tables
WHERE TABLE_SCHEMA = "db_name"
ORDER BY round(data_length / 1024 / 1024, 2) DESC;
```
查看某个db下某个表的占用空间
```
SELECT table_name
  , concat(round(data_length / 1024 / 1024, 2), "MB") AS data
FROM information_schema.tables
WHERE table_schema = "db_name"
AND table_name = "table_name";
```

####MySQL加密解密（Hive通用）
第一个字段：将"shenlibin"字符串加密，密钥可以自定义为任意字符串，但有长度限制。这里密钥自定义为data_system
第二个字段：将加密后的字段根据密钥解密
注意：Hive加解密时需要将对应的字段强转为为string类型才可以
```
SELECT hex(aes_encrypt("shenlibin", "data_system")) AS encrypt
    	, aes_decrypt(unhex(hex(aes_encrypt("shenlibin", "data_system"))), "data_system") AS decrypt
```
输出结果
```
+----------------------------------+-----------+
| encrypt                          | decrypt   |
+----------------------------------+-----------+
| 0C8781515288BC898D870DB6C9A2ED39 | shenlibin |
+----------------------------------+-----------+
1 row in set (0.00 sec)
```

####查看执行计划
```
# 总共涉及到以下几个命令
show processlist; 
show full processlist;
set profiling=on;
show profiles ;
show profile for query [query_id];
```
假设现在要查看如下SQL的执行情况：

`select * from product_stock limit 99999;`

需要执行的步骤：
```
# 先打开配置
set profiling = on;
# 执行SQL
select * from product_stock limit 99999;
# 等执行完后找到该SQL的query_id
show profiles ;
# 查看该query_id的详细执行情况
show profile for query 150 ;
```

####生成连续的时间序列
需求：输入一个日期字符串，以及整数n。返回这个日期之前的n天对应的日期序列。
比如：输入2019-10-25，以及5，则返回2019-10-20，2019-10-21，2019-10-22，2019-10-23，2019-10-24
原理：按照输入日期以及n，使用rpad函数来填充指定长度字符串，这些字符串都是按照逗号分隔的相同日期的字符串。在使用split函数来分隔，使用explode函数来把分隔好的字符串转换为多行。最后使用窗口函数排序生成行号，使用本行的日期减去行号即得到对应的日期。
```
# Hive版本
SELECT DATEADD(TO_DATE(dt, "yyyy-mm-dd"), -1 * ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY dt), "dd") dates
FROM (SELECT EXPLODE(SPLIT(RPAD("2019-10-25", 5 * 11 - 1, ",2019-10-25"), ",")) dt) arr

#MySQL版本，生成最近60天连续的日期序列
SELECT main.dates,
       @init := 0
FROM (SELECT date_add(curdate(), INTERVAL @init DAY) dates,
             @init := @init - 1
      FROM product
      LIMIT 60) main
```

####使用窗口函数实现每个窗口累加值
```
SELECT  a.pt
        ,num
        ,sum(a.num) over(partition by substr(a.pt,1,6)) nums 
        ,sum(a.num) over(partition by substr(a.pt,1,6) order by pt rows between unbounded preceding and current row) nums 
        -- 第4行的代码等价于下面第6行的
        --,SUM(a.num) OVER(PARTITION BY SUBSTR(a.pt,1,6) order by a.pt asc) nums
FROM    (
            SELECT  pt
                    ,COUNT(*) num
            FROM    log_stock
            WHERE   pt >= "20191120"
            GROUP BY pt
        ) a
        order by a.pt asc limit 1000;
```

####MySQL事件模板
从2018年1月17日开始开始，每天00：05：00执行存储过程:insert_table_event
```
CREATE EVENT  IF NOT EXISTS  insert_table_event
ON SCHEDULE  EVERY  1 DAY
STARTS "2018-01-17 00:05:00"
ON COMPLETION PRESERVE 
DO call insert_table();
```

从2018年2月6日12点半开始，每3小时执行一次存储过程
```
CREATE EVENT  IF NOT EXISTS  insert_bi_table_event
ON SCHEDULE  EVERY  3  HOUR
STARTS "2018-02-06 12:30:00"
ON COMPLETION PRESERVE 
DO call insert_bi_table();
```

####MySQL存储过程模板
```
DELIMITER ;;
CREATE DEFINER=`shenlibin`@`%` PROCEDURE `insert_bi_stt_identification_detail`()
BEGIN
delete from tablename0;
insert into tablename1
select * from tablename2
;
END;;
DELIMITER ;
```

当前位置： 首页 >> SQL >> SQL模板

SQL模板

技术交流 问题反馈

当前位置：首页 >> SQL >> SQL模板

技术交流
问题反馈