数据库 - 开发常用SQL语句总结 - 持续更新

前言:

        总结一下, 自己工作开发中遇到的一些常用SQL语句, 后续会持续更新.

---

1. with语句 - 临时表

在SQL中，WITH 语句通常用于创建临时性的结果集，也被称为"公共表表达式"（Common Table Expression，CTE）。通过WITH语句，您可以在查询中定义一个临时的结果集，然后在查询中引用这个结果集，从而使查询更清晰、更易于理解。

通常，WITH语句的语法如下所示：

WITH temporary_table_name AS (
    -- 查询语句
),
-- 可以跟多个, 也可以是单个
temporary_table_name2 AS (
    -- 查询语句
)

-- 查询语句, 创建临时表后, 后面需要跟查询语句
SELECT columns
FROM table_name
JOIN temporary_table_name ON condition;

在这个语法中，temporary_table_name是您定义的临时表的名称，后面跟着一个AS关键字和一对括号。括号中包含一个查询，用于定义临时表的内容。然后，您可以在查询的主体部分引用这个临时表。

使用WITH语句可以使复杂的查询更易于管理，可以将逻辑上相关的部分分解为可重用的部分，提高查询的可读性和可维护性。

2. Union和Union All的区别 - 合并返回结果字段相同的结果集

1. Union：对两个结果集进行并集操作。不包括重复行。会进行默认规则的排序。由于需要扫描重复值，所以效率相对较低。
2. Union All：对两个结果集进行并集操作。包括重复行。不进行排序。由于不去除重复行，执行效率通常比 Union 高。

举例来说，如果两个查询语句分别返回了不同的员工信息，使用 Union 会去除重复的员工信息，只显示不同的记录。而使用 Union All 则会显示所有记录，包括重复的。

需要注意的是，使用 Union 和 Union All 时，必须保证各个查询集合的结果有相同个数的列，并且每个列的类型是一样的。

3. 查询数据库中库表是否存在

MySQL

SELECT count(1) from information_schema.tables
where
    -- 数据库名称
    table_schema = 'demo_data_base'
    -- 库表名称
    AND table_name = 'user'
    
-- 如果查询到, 结果就是具体的数量
-- 如果没有查询到, 结果就是0

PostgreSQL

-- relname: 查询的具体的库表名称
SELECT COUNT(1) FROM pg_class where relname = 'qt_data_warn_log';

-- 如果查询到, 结果就是具体的数量
-- 如果没有查询到, 结果就是0

4. 创建基表的分表 - 复制相同结构的表

MySQL

---

MySQL 支持创建一个新的表并复制另一个存在的表的结构. 下面的语句可以创建一个新的表并且复制原表的结构：

CREATE TABLE new_table LIKE existing_table;

这个语句会创建一个新的名为 new_table 的表，并且复制 existing_table 表的列、索引和其他属性, 但是没有复制数据。

如果你希望复制表结构以及数据，可以使用下面的语句：

CREATE TABLE new_table SELECT * FROM existing_table;

在这个语句中，请将 new_table 替换成新表的名称，而 existing_table 则应被替换成已存在的表的名称。需要注意的是，这种写法只会复制原表的数据和列，不会复制索引和触发器等其他属性。

PostgreSQL

---

CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL);

5. 数据同步-相同表结构

-- MySQL 和 PostgreSQL, 都可以
INSERT into user_1 SELECT * FROM user WHERE 可以加查询条件

6. Update 和 From 连用(关联表的更新)

解决问题： 针对于， 大数据量一条一条的update更新（比如mybatisplus一条一条修改）， 会导致执行时间太慢， 所以可以通过【update   from   关联表的更新】来提高效率；

    -- 1，update   from   关联表的更新        [postgresql ]
　　update table a set name=b.name from tableB b  where a.id=b.id;
　　update test set info=tmp.info from (values (1,'new1'),(2,'new2'),(6,'new6')) as tmp (id,info) where test.id=tmp.id; 
  
    --   2 update from                         [postgresql ]
　　update TABLEA a 
　　set  update_time=now,
　　　　name=case when b.name is not null then d.name else a.name end,
　　　　age=case when b.age is not null then d.age else a.age end 
　　from TABELEB b   where a.id=b.id;

.........