整理的SQL语句

  PostgreSql 数据库

 -- ===== sql 开始 =====
 -- 数据库时区
 SHOW timezone;
 -- 清空表
 TRUNCATE t1 RESTART IDENTITY; 
 # 查看sql执行过程
 EXPLAIN
 -- 设置默认值
 SELECT COALESCE(NULL, 0); 
 -- 生成序列 start,stop,step
 SELECT generate_series(0,9,1);
 -- 时间计算
 SELECT  now(), now() + (t1.days||' Days')::INTERVAL, "t1".* FROM "t1" WHERE (t1.end_at > now() + (t1.days||' Days')::INTERVAL) ;  
 -- 插入数据
 INSERT INTO t1(id, name) VALUES (1, '测试') 
 -- 查询-插入数据
 INSERT INTO t1(id, name) (SELECT id, name FROM users)
 -- 查询-插入/更新数据
 INSERT INTO t1(id, name) (SELECT id, name FROM users) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; 
 -- 查询-插入/更新数据[取最近的一次最高成绩]
 INSERT INTO t1 (name, seq) (
  SELECT tt.name, ROW_NUMBER() OVER(ORDER BY tt.score DESC) AS seq 
  FROM (SELECT DISTINCT ON (name) ttt.name, ttt.score FROM ttt WHERE ttt.class_name = '1班' ORDER BY ttt.name, ttt.score DESC, ttt.created_at
 ) AS tt) ON CONFLICT (name) DO UPDATE SET seq = EXCLUDED.seq;
 -- 批量更新
 UPDATE tt SET name = tmp.name FROM (values (1, 'Ruby'),(2, 'Java'),(6, 'Php')) AS tmp (id, name) WHERE tt.id = tmp.id;
 -- 批量更新(Select)
 UPDATE tt SET name = tmp.name FROM tmp WHERE tt.id = tmp.id;
 -- 添加序号
 SELECT *, (ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY score DESC) AS seq FROM t1;
 -- 添加排名（空并列）
 SELECT *, (RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS seq FROM t1;
 -- 添加排名（不空并列）
 SELECT *, (DENSE_RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS seq FROM t1;
 -- 自定义排序
 WITH ot
 AS (SELECT * FROM UNNEST(ARRAY[432, 211, 393, 343]) WITH ORDINALITY o(id, seq))
 SELECT * FROM t1 LEFT JOIN ot USING(id) 
 ORDER BY seq NULLS LAST, id 
 -- 构建临时查询
 SELECT t.*, users.name FROM (VALUES(1, 'a'), (2, 'b'), (3, 'c')) t(id, name) LEFT JOIN users ON t.id = users.id;
 -- ===== 结束 =====

 

    存储过程（函数）

-- 初始化数据信息（或者先导入数据）
DROP PROCEDURE IF EXISTS addNos;
delimiter $
CREATE PROCEDURE addNos()
BEGIN
  DECLARE i INT DEFAULT 100000;
  SET @val = "INSERT INTO `pay_center`.`cfacct_card_no` (base_sn, full_sn) VALUES ";
    while i < 999999
    do
      SET @val = CONCAT(@val, '(', i, ',', "'", CONCAT('302XXXXX', i), "'", '),');
      SET i = i + 1;
    end while;
  SET @val = CONCAT(@val, "(999999, '302XXXXX200000')");
  PREPARE stmt FROM @val;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $
delimiter;

CALL addNos;
DROP PROCEDURE addNos;
SELECT COUNT(*) FROM  `pay_center`.`cfacct_card_no` LIMIT 10;