达梦数据库（DM Database）是一款由中国公司自主研发的关系型数据库管理系统。它在安全性、性能、易用性等方面具有许多独特的优势。本文简单记录其 sql 简单使用。更多 SQL 语言使用说明，请参考《DM_SQL 语言使用手册》，手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题，请在社区内咨询。

参考文章：

达梦技术文档

常规数据类型

DM SQL 程序支持所有的 DM SQL 数据类型，包括：精确数值数据类型、近似数值数据类型、字符数据类型、多媒体数据类型、一般日期时间数据类型、时间间隔数据类型。此外，DM SQL 还扩展支持了 %TYPE、%ROWTYPE、记录类型、数组类型、集合类型和类类型，用户还可以定义自己的子类型。

DM 数据库具有 SQL-92 的绝大部分数据类型，常规数据类型主要包括如下几大类：

  • 字符数据类型
  • 数值数据类型
  • 位串数据类型，即 bit 类型
  • 日期时间数据类型，包括一般日期时间数据类型、时区数据类型和时间间隔数据类型
  • 多媒体数据类型，主要包括两种格式，字符串和二进制

字符数据类型

根据数据库初始化时的参数不同，字符串中能存储的汉字的个数也不尽相同。主要由如下参数决定：

  • UNICODE_FLAG：此参数表示了数据库中所有数据的字符集，包括数据字典的字符集。需要注意的是，字符集在安装初始化库的时候指定，数据库一旦初始化完成，字符集就将无法修改。我们可以使用 select unicode 来查询当前数据库的字符集种类，0 代表 gb18030；1 代表 UTF-8。

  • VARCHAR 类型

    若没有指定 USING LONG ROW 存储选项时，插入 VARCHAR 数据类型的实际最大存储长度由数据库页面大小决定；如果指定了 USING LONG ROW 存储选项，则插入 VARCHAR 数据类型的长度不受数据库页面大小限制。VARCHAR 类型在表达式计算中的长度上限不受页面大小限制，为 32767。

    1
    2
    -- 查询数据库的页大小
    SELECT '页大小',cast(PAGE()/1024 as varchar)；
1
2
3
4
5
6
7
8
9
10
11
12
-- 创建表，定义字段为 VARCHAR 类型，定长为 3
CREATE TABLE dmhr.CHAR_TEST(
       name VARCHAR(3)
);
-- 插入 1 个汉字成功
INSERT INTO dmhr.CHAR_TEST values('测');
COMMIT;
-- 插入 2 个汉字失败
INSERT INTO dmhr.CHAR_TEST values('测试');
COMMIT;
-- 回滚
rollback;

在字符串中增加引号

1
2
3
4
-- 将一个单引号换成两个单引号，引入单引号
SELECT 'girl''day' qmarks FROM DUAL UNION ALL SELECT '''' FROM DUAL;
-- 使用界定符，通过 Q 或者 q 开头，字符串前后使用界定符 “ ‘ ”
SELECT q'[girl'day]' qmarks FROM DUAL UNION ALL SELECT q'[']' FROM DUAL;

计算字符出现的次数

使用函数 regexp_count、regexp_replace 或 translate 统计子串个数。

1
2
3
4
5
6
7
8
-- 创建测试视图
CREATE OR REPLACE VIEW v AS SELECT 'STUDENT,TEACHER,TEAM' AS str FROM DUAL;
-- 使用函数 regexp_count 统计子串个数
SELECT regexp_count(str,',')+1 as cnt FROM v;
-- 使用 regexp_replace 迂回求值统计子串个数
SELECT length(regexp_replace(str,'[^,]'))+1 as cnt FROM v;
-- 使用 translate 统计子串个数
SELECT length(translate(str,',' || str,','))+1 AS cnt FROM v;

删除字符串中不需要的字符

使用 translate 或者 regexp_replace 在某个字段中去掉不需要的字符

1
2
3
4
-- 使用 translate 方法
SELECT ename,translate(ename,'1AEIOU','1') stripped1 FROM v;
-- 使用正则函数 regexp_replace [] 内列举的字符替换为空，示例语句如下所示：
SELECT ename,regexp_replace(ename,'[AEIOU]') AS stripped FROM v;

将字符与数字分离

使用 regexp_replace 正则表达式实现字符串中字符与数字分离。

1
SELECT REGEXP_REPLACE (data, '[0-9]', '') dname, REGEXP_REPLACE (data, '[^0-9]', '') deptno FROM v;

查询只包含字母或数字的数据

有些数据包含了空格、逗号、$ 等字符。使用 regexp_like 实现查询只包含字母或者数字型的数据。

1
SELECT data FROM v WHERE REGEXP_LIKE (data, '^[0-9a-zA-Z]+$');

注意

  1. regexp_like(data,’A’) 对应普通的like ‘%A%’。
  2. 前面加“^”，regexp_like(data,’^A’) 对应普通的like ‘A%’，没有前模糊查询。
  3. 后面加“$”，regexp_like(data,’A$’) 对应普通的like ‘%A’，没有后模糊查询。
  4. 前后各加“^$”，regexp_like(data,’^A$’) 对应普通的like ‘A’，变成精确查询。

按字符串中的数字排序

通过正则表达式或者 translate 函数实现按字符串中的数值排序。

1
2
3
4
-- 用正则表达式替换非数字字符
SELECT data, TO_NUMBER (REGEXP_REPLACE (data, '[^0-9]', '')) AS deptno FROM V ORDER BY 2;
-- 使用 translate 函数，直接替换掉非数字字符
SELECT data, TO_NUMBER (TRANSLATE (data, '0123456789' || data, '0123456789')) AS deptno FROM V ORDER BY 2;

创建分隔列表

使用 listagg 分析函数实现合并显示，同 sum 一样，listagg 函数也起到汇总结果作用。sum 将数值结果累计求和，而 listagg 是把字符串的结果连在一起。

1
2
3
4
5
SELECT 
    deptno,SUM (sal) AS total_sal, 
    LISTAGG (name, ',') WITHIN GROUP (ORDER BY name) AS total_name
FROM v
GROUP BY deptno;

提取子串与分解 IP 地址

通过 regexp_substr 实现第 n 个子串的分割，字符串拆分。

1
2
3
4
5
6
7
8
-- 比如在"CLARK,KATE,JAMES"字符串中提取出“KATE”
SELECT REGEXP_SUBSTR (v.name,'[^,]+',1,2) AS 子串 FROM v;
-- 将 IP 地址“192.168.1.111”中的各段取出来
SELECT REGEXP_SUBSTR (v.ip,'[^.]+',1,1) a,
       REGEXP_SUBSTR (v.ip,'[^.]+',1,2) b,
       REGEXP_SUBSTR (v.ip,'[^.]+',1,3) c,
       REGEXP_SUBSTR (v.ip,'[^.]+',1,4) d
FROM (SELECT '192.168.1.111' AS ip FROM DUAL) v;

参数 1：“^”在方括号里表示否的意思，+ 表示匹配 1 次以上。第二个参数表示匹配不包含逗号的多个字符。

参数 2：“1”表示从第一个字符开始。

参数 3：“2”表示第二个能匹配目标的字符串，也就是 KATE。

数值数据类型

  • 精确数值数据类型包括：NUMERIC、DECIMAL、DEC 类型、NUMBER 类型、INTEGER 类型、INT 类型、BIGINT 类型、TINYINT 类型、BYTE 类型、SMALLINT 类型、BINARY 类型、VARBINARY 类型。
  • 近似数值类型包括：FLOAT 类型、DOUBLE 类型、REAL 类型、DOUBLE PRECISION 类型。

NUMERIC 数据类型用于存储零、正负定点数。其中：精度是一个无符号整数，定义了总的数字数，精度范围是 1 至 38。标度定义了小数点右边的数字位数。一个数的标度不应大于其精度，如果实际标度大于指定标度，那么超出标度的位数将会四舍五入省去。

例如：NUMERIC(4,1) 定义了小数点前面 3 位和小数点后面 1 位，共 4 位的数字，范围在 -999.9 到 999.9。所有 NUMERIC 数据类型，如果其值超过精度，会返回一个出错信息，如果超过标度，则多余的位会被截断。

使用语法：NUMERIC[(精度 [, 标度])]，DECIMAL、DEC 类型、NUMBER 类型与 NUMERIC 的语法一致。

1
2
3
4
5
6
7
8
9
10
11
-- 创建表，定义金额字段为 NUMERIC 类型，精度为 10, 标度为 2。即小数点前面 8 位，小数点后面 2 位
CREATE TABLE dmhr.NUMERIC_TEST(
        cust_id INT NOT NULL,
        amt NUMERIC(10,2)
);
-- 插入数据
INSERT INTO dmhr.NUMERIC_TEST values('10000001','88888888.99');
INSERT INTO dmhr.NUMERIC_TEST values('10000002','-66666666.99');
COMMIT;
-- 查询全部
SELECT * FROM dmhr.NUMERIC_TEST;

常用聚集函数

SQL 中的聚集函数共包括 5 个 (MAX、MIN、SUM、AVG、COUNT)，可以帮我们求某列的最大值、最小值及平均值等。

1
2
3
4
5
6
7
8
9
-- 查询每个部门员工的平均薪资、最小薪资、最大薪资、总工资及总记录
SELECT job_id,
    AVG (salary) AS 平均值,
    MIN (salary) AS 最小值,
    MAX (salary) AS 最大值,
    SUM (salary) AS 工资合计,
    COUNT (*) AS 总行数
FROM dmhr.employee
GROUP BY job_id;

当表中没有数据时，不加 group by 会返回一行数据，加了 group by 无数据返回。

生成累计和

使用分析函数 sum (…) over (order by…) 可以生成累计和。

1
2
3
4
5
6
7
8
9
-- 按员工编号排序对员工的工资进行累加,用 listagg 模拟出每一行是哪些值相加
SELECT employee_id AS 编号,
    employee_name AS 姓名,
    salary AS 人工成本,
    SUM (salary) OVER (ORDER BY employee_id) AS 成本累计,
    ( SELECT LISTAGG (salary, '+') WITHIN GROUP (ORDER BY employee_id) 
        FROM dmhr.employee b WHERE b.job_id = 11 AND b.employee_id <= a.employee_id ) 计算公式
FROM dmhr.employee
WHERE job_id = 11;

求总和的百分比

使用分析函数 SUM() 和 OVER() 计算各部门工资合计及该合计工资占总工资的比例。用 group by 语句可以用到合计工资。

当 OVER() 后不加任何内容时，就是对所有的数据进行汇总。

1
2
3
4
SELECT job_id, 工资合计, SUM (工资合计) OVER () AS 总合计
FROM (  
    SELECT job_id, SUM (salary) 工资合计 FROM dmhr.employee GROUP BY job_id
) x;

位串数据类型 BIT

  • BIT 类型语法：BIT

BIT 类型用于存储整数数据 1、0 或 NULL，只有 0 才转换为假，其他非空、非 0 值都会自动转换为真，可以用来支持 ODBC 和 JDBC 的布尔数据类型。DM 的 BIT 类型与 SQL Server 2000 的 BIT 数据类型相似。

1
2
3
4
5
6
7
8
9
10
-- 创建表，定义性别字段为 sex, 1-男，0-女
CREATE TABLE dmhr.BIT_TEST(
        cust_id INT NOT NULL,
        cust_name VARCHAR(10),
        sex BIT
);
-- 插入数据
INSERT INTO dmhr.BIT_TEST values('10000001','李丽',1);
INSERT INTO dmhr.BIT_TEST values('10000002','王强',0);
COMMIT;

日期时间数据类型

日期时间数据类型分为一般日期时间数据类型、时间间隔数据类型和时区数据类型三类，用于存储日期、时间和它们之间的间隔信息。

  • 一般日期时间数据类型

    DATE 类型包括年、月、日信息；TIME 类型包括时、分、秒信息；TIMESTAMP 类型包括年、月、日、时、分、秒信息。

    TIME 类型的小数秒精度规定了秒字段中小数点后面的位数，取值范围为 0～6，如果未定义，缺省精度为 0。 TIMESTAMP 类型的小数秒精度规定了秒字段中小数点后面的位数，取值范围为 0～6，如果未定义，缺省精度为 6。

  • 时间间隔数据类型

    DM 支持两类时间间隔类型：年-月间隔类和日-时间隔类，它们通过时间间隔限定符区分，前者结合了日期字段年和月，后者结合了时间字段日、时、分、秒。时间间隔数据类型所描述的值是有符号的。

    使用时间间隔数据类型时，如果不指定精度，那么将使用默认精度 6。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建表，定义 3 个日期时间数据类型，DATE、TIME 及 TIMESTAMP
CREATE TABLE dmhr.DATE_TEST(
        cust_id INT NOT NULL,
        goods VARCHAR(10),
        pay_date DATE,
        pay_time TIME（2）,
        pay_timestamp TIMESTAMP
);
-- 插入数据
INSERT INTO dmhr.DATE_TEST
values('10000001','百事可乐','2020-11-19','23:59:59.99','2020-11-19 23:59:59.999999');
INSERT INTO dmhr.DATE_TEST
values('10000002','可口可乐','2020-11-18','10:05:30.00','2020-11-18 10:05:30.000000');
COMMIT;

加减日、月、年

1
2
3
4
5
6
7
8
9
10
-- 根据某个员工的入职日计算其前五天、后五天、前五个月、后五个月、前五年及后5年的具体时间
SELECT hire_date AS 聘用日期,
   add_days (hire_date, -5) AS 减5天,
   add_days (hire_date, 5) AS 加5天,
   add_months (hire_date, -5) AS 减5月,
   add_months (hire_date, 5) AS 加5月,
   add_months (hire_date, -5 * 12) AS 减5年,
   add_months (hire_date, 5 * 12) AS 加5年
FROM dmhr.employee
WHERE ROWNUM <= 1;

加减时、分、秒

1
2
3
4
5
6
7
-- 时间类型的数据可以直接加减时、分、秒，1/24 指的是 1 小时。
select sysdate as 当前日期,
    sysdate - 5.0 / 24 as 减5小时,
    sysdate + 5.0 / 24 as 加5小时,
    sysdate - 5.0 / 24 / 60 as 减5分钟,
    sysdate + 5.0 / 24 / 60 as 加5分钟
from dual;

日期间隔之时、分、秒

1
2
3
4
5
6
7
8
9
10
-- 两个 date 相减，得到的是天数，乘以 24 即为小时，以此类推可计算出秒。
SELECT 间隔天数,
   间隔天数 * 24 AS 间隔小时,
   间隔天数 * 24 * 60 AS 间隔分,
   间隔天数 * 24 * 60 * 60 AS 间隔秒
FROM (
    SELECT MAX (hire_date) - MIN (hire_date) AS 间隔天数 
    FROM dmhr.employee 
    WHERE employee_name IN ('马学铭', '陈仙')
) x;

日期间隔之日、月、年

1
2
3
4
5
6
7
-- 使用 months_between 函数计算间隔月份，以此类推计算出间隔年。
SELECT max_hd - min_hd 间隔天,
   MONTHS_BETWEEN (max_hd, min_hd) 间隔月,
   MONTHS_BETWEEN (max_hd, min_hd) / 12 间隔年
FROM (
  SELECT MAX (hire_date) max_hd, MIN (hire_date) min_hd FROM dmhr.employee
);

求两个日期间的工作天数

通过 MAX() 将原数据转为一行，枚举 2 个日期之间的天数，与 T500 做笛卡尔积枚举 30 天的所有日期，根据日期得到对应工作日信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH tmp AS (
    SELECT 日期, TO_CHAR (日期, 'DY', 'NLS_DATE_LANGUAGE = American') AS dy
    FROM (
        SELECT min_hd + (dmhr.t500.id - 1) AS 日期
        FROM (
            SELECT MIN (hire_date) AS min_hd, MAX (hire_date) AS max_hd
            FROM dmhr.employee
            WHERE employee_name IN ('马学铭', '陈仙')
        ) x, dmhr.t500
        WHERE dmhr.t500.id <= ( (max_hd - min_hd) + 1)
    )
)
-- 过滤并汇总
SELECT COUNT (*) FROM tmp WHERE dy NOT IN ('SAT', 'SUN');

求一年中周内各日期的天数

比如，计算一年内有多少天是星期一，多少天是星期二等，可以按照如下步骤实现：

  • 取得大当前年度信息。
  • 计算一年有多少天。
  • 生成日期列表。
  • 转换为对应的星期标识。
  • 汇总统计。
1
2
3
4
5
6
WITH x0 AS (SELECT TO_DATE ('2020-01-01', 'yyyy-mm-dd') AS 年初 FROM DUAL),
     x1 AS (SELECT 年初, ADD_MONTHS (年初, 12) AS 下年初 FROM x0),
     x2 AS (SELECT 年初, 下年初, 下年初 - 年初 AS 天数 FROM x1),
     x3 AS (SELECT 年初 + (LEVEL - 1) AS 日期 FROM x2 CONNECT BY LEVEL <= 天数),
     x4 AS (SELECT 日期, TO_CHAR (日期, 'DY') AS 星期 FROM x3)
SELECT 星期, COUNT (*) AS 天数 FROM x4 GROUP BY 星期;

确定当前记录和下一条记录之间相差的天数

1
2
3
4
5
6
7
-- 使用 lead() over() 分析函数，将下一条记录的雇佣日期作为当前行
WITH tmp AS (
    SELECT employee_id,employee_name,hire_date, LEAD (hire_date) OVER (ORDER BY hire_date) next_hd
    FROM dmhr.employee
    WHERE job_id = 11
)
SELECT employee_name,hire_date,next_hd,next_hd - hire_date diff FROM tmp;

SYSDATE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT hire_date,
   TO_NUMBER (TO_CHAR (hire_date, 'hh24')) 时,
   TO_NUMBER (TO_CHAR (hire_date, 'mi')) 分,
   TO_NUMBER (TO_CHAR (hire_date, 'ss')) 秒,
   TO_NUMBER (TO_CHAR (hire_date, 'dd')) 日,
   TO_NUMBER (TO_CHAR (hire_date, 'mm')) 月,
   TO_NUMBER (TO_CHAR (hire_date, 'yyyy')) 年,
   TO_NUMBER (TO_CHAR (hire_date, 'ddd')) 年内第几天,
   TRUNC (hire_date, 'dd') 一天之始,
   TRUNC (hire_date, 'day') 周初,
   TRUNC (hire_date, 'mm') 月初,
   LAST_DAY (hire_date) 月末,
   ADD_MONTHS (TRUNC (hire_date, 'mm'), 1) 下月初,
   TRUNC (hire_date, 'yy') 年初,
   TO_CHAR (hire_date, 'day') 周几,
   TO_CHAR (hire_date, 'month') 月份
FROM (
  SELECT hire_date + 30 / 24 / 60 / 60 + 20 / 24 / 60 + 5 / 24 AS hire_date
  FROM dmhr.employee
  WHERE ROWNUM <= 1
);

EXTRACT

EXTRACT 函数可以提取时间字段中的年、月、日、时、分、秒，返回的值为 NUMBER 类型。

1
2
3
4
5
6
7
SELECT EXTRACT (YEAR FROM SYSTIMESTAMP) AS "YEAR",
   EXTRACT (MONTH FROM SYSTIMESTAMP) AS "MONTH",
   EXTRACT (DAY FROM SYSTIMESTAMP) AS "DAY",
   EXTRACT (HOUR FROM SYSTIMESTAMP) AS "HOUR",
   EXTRACT (MINUTE FROM SYSTIMESTAMP) AS "MINUTE",
   EXTRACT (SECOND FROM SYSTIMESTAMP) AS "SECOND"
FROM DUAL;

to_char 函数可以用来取日期时间类型字段中的时、分、秒。

1
2
3
SELECT created, TO_CHAR (created, 'dd') AS d, TO_CHAR (created, 'hh24') AS h
FROM dba_objects
WHERE object_id = 2;

EXTRACT 函数可以用来取 INTERVAL 中的信息，to_char 函数不支持。

1
2
SELECT EXTRACT (HOUR FROM it) AS "hour"
FROM (SELECT INTERVAL '2 12:30:59' DAY TO SECOND AS it FROM DUAL);

判断一年是否为闰年

判断一年是否为闰年，可以看二月的月末具体是哪一天。使用 TO_CHAR、 LAST_DAY、 ADD_MONTHS、 TRUNC 函数共同实现。

1
2
3
4
5
6
7
8
9
10
11
12
-- 计算年初
SELECT TRUNC (hire_date, 'y') 年初 
FROM dmhr.employee WHERE ROWNUM <= 1;
-- 计算二月初
SELECT ADD_MONTHS (TRUNC (hire_date, 'y'), 1) 二月初 
FROM dmhr.employee WHERE ROWNUM <= 1;
-- 计算二月底
SELECT LAST_DAY (ADD_MONTHS (TRUNC (hire_date, 'y'), 1)) AS 二月底 
FROM dmhr.employee WHERE ROWNUM <= 1;
-- 计算二月底对应的日期
SELECT TO_CHAR (LAST_DAY (ADD_MONTHS (TRUNC (hire_date, 'y'), 1)), 'DD') AS 日
FROM dmhr.employee WHERE ROWNUM <= 1;

周的计算

使用 TO_CHAR、 NEXT_DAY、TRUNC 函数共同实现周的计算。

1
2
3
4
5
6
7
8
9
10
WITH x AS (
    SELECT TRUNC (SYSDATE, 'yy') + (LEVEL - 1) AS 日期 FROM DUAL CONNECT BY LEVEL <= 8
)
SELECT 日期,
   TO_CHAR (日期, 'd') AS d,
   TO_CHAR (日期, 'day') AS day,
   NEXT_DAY (日期, 1) AS 下个周日,
   TO_CHAR (日期, 'ww') AS ww,
   TO_CHAR (日期, 'iw') AS iw
FROM x;

注意：参数 “day” 与字符集无关，所以建议使用 “d”。 WW 与 IW 都是取 “第几周”，只是两个参数的初始值不一样。

确定一年内属于周内某一天的所有日期

1
2
3
4
5
6
-- 返回指定年份内的所有周五对应的日期。
WITH x AS (
    SELECT TRUNC (SYSDATE, 'yy') + (LEVEL - 1) dy 
    FROM DUAL CONNECT BY LEVEL <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y')
)
SELECT dy, TO_CHAR (dy, 'day') AS 周五 FROM x WHERE TO_CHAR (dy, 'd') = 6;

确定某月内第一个和最后一个周末某天的日期

1
2
3
4
5
-- 返回当月内第一个星期一与最后一个星期一，分别找上月某及当月末之前七天的下一周的周一即可
SELECT NEXT_DAY (TRUNC (hire_date, 'mm') - 1, 2) 第一个周一,
       NEXT_DAY (LAST_DAY (TRUNC (hire_date, 'mm')) - 7, 2) 最后一个周一
FROM dmhr.employee
WHERE ROWNUM <= 1;

创建本月日历

枚举指定月份所有的日期，并转换为对应的周信息，再按所在周做一次 行转列 即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 给定一个日期
WITH x1 AS ( SELECT TO_DATE('2020-11-01', 'yyyy-mm-dd') AS cur_date FROM DUAL ),
     -- 取月初
     x2 AS ( SELECT TRUNC(cur_date, 'mm') AS 月初, ADD_MONTHS(TRUNC(cur_date, 'mm'), 1) AS 下月初 FROM x1 ),
     -- 枚举当月所有天
     x3 AS ( SELECT 月初 + (LEVEL - 1) AS 日 FROM x2 CONNECT BY LEVEL <= (下月初 - 月初) ),
     -- 提取周信息
     x4 AS ( SELECT TO_CHAR(日,'iw') 所在周, TO_CHAR(日,'dd') 日期, TO_NUMBER(TO_CHAR(日,'d')) 周几 FROM x3 )
SELECT 
    MAX (CASE 周几 WHEN 2 THEN 日期 END) 周一,
    MAX (CASE 周几 WHEN 3 THEN 日期 END) 周二,
    MAX (CASE 周几 WHEN 4 THEN 日期 END) 周三,
    MAX (CASE 周几 WHEN 5 THEN 日期 END) 周四,
    MAX (CASE 周几 WHEN 6 THEN 日期 END) 周五,
    MAX (CASE 周几 WHEN 7 THEN 日期 END) 周六,
    MAX (CASE 周几 WHEN 1 THEN 日期 END) 周日
FROM x4 GROUP BY 所在周 ORDER BY 所在周;

确定指定年份季度的开始日期和结束日期

按季度分类汇总，提取对应的季度开始日期和结束日期。可以通过 add_months、to_date 函数实现。

1
2
3
4
5
6
7
SELECT sn AS 季度,
   (sn - 1) * 3 + 1 AS 开始月份,
   ADD_MONTHS (TO_DATE (年, 'yyyy'), (sn - 1) * 3) AS 开始日期,
   ADD_MONTHS (TO_DATE (年, 'yyyy'), sn * 3) - 1 AS 结束日期
FROM (
    SELECT '2020' AS 年, LEVEL AS sn FROM DUAL CONNECT BY LEVEL <= 4
);

按照给定的时间单位查找

使用 to_char 函数查询给定时间单位的时间。

1
2
3
4
-- 查询如入职日期在 1 月或者 12 月且非星期三的员工信息。
SELECT employee_name 姓名, hire_date 入职日期, TO_CHAR (hire_date, 'day') AS 星期
FROM dmhr.employee
WHERE TO_CHAR (hire_date, 'mm') IN ('01', '12') AND TO_CHAR (hire_date, 'd') != '4';

使用日期的特殊部分比较记录

1
2
3
4
5
6
7
-- 使用 to_char 函数统计相同月份与周内日期入职的员工。
SELECT employee_name 姓名, hire_date 入职日期, TO_CHAR (hire_date, 'MON day') AS 月周
FROM (
    SELECT employee_name, hire_date, COUNT (*) OVER (PARTITION BY TO_CHAR (hire_date, 'MON day')) AS ct
    FROM dmhr.employee
)
WHERE hire_date LIKE '2015%';

多媒体数据类型

多媒体数据类型的字值有两种格式：一是字符串，例如：’ABCD’，二是 BINARY，如 0x61626364。

多媒体数据类型包括：

  • TEXT/LONG/LONGVARCHAR 类型：变长字符串类型。其字符串的长度最大为 100G-1，用于存储长的文本串。
  • IMAGE/LONGVARBINARY 类型：用于指明多媒体信息中的图像类型，长度最大为 100G-1 字节。
  • BLOB 类型：用于指明变长的二进制大对象，长度最大为 100G-1 字节。
  • CLOB 类型：用于指明变长的字母数字字符串，长度最大为 100G-1 字节。
  • BFILE 类型：用于指明存储在操作系统中的二进制文件。

BLOB 和 IMAGE 类型的字段内容必须存储十六进制的数字串内容

1
2
3
4
5
6
7
8
9
10
-- 创建表，定义多个多媒体数据类型字段，c1，c3长文本，c2，c4二进制数据
CREATE TABLE dmhr.TEXT_TEST(
        c1 TEXT,
        c2 BLOB,
        c3 CLOB,
        c4 IMAGE
);
-- 插入数据
INSERT INTO dmhr.TEXT_TEST values('dameng','0x123456789','clob','0x987654321');
commit;

单表查询 SQL 操作方法

查看表结构

1
2
SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE','DMHR') FROM dual;
SP_TABLEDEF('DMHR','EMPLOYEE');

全表数据检索

1
SELECT * FROM dmhr.employee;

行过滤

1
2
3
4
5
-- 按员工入职时间进行筛选
SELECT * FROM dmhr.employee WHERE hire_date > '2015-01-01';
-- 查找满足多个条件的行
SELECT * FROM dmhr.employee
WHERE (department_id = 102 OR salary > 20000 OR (department_id = 105 AND salary > 9000));

空值

1
SELECT *  FROM dmhr.employee WHERE commission_pct IS NULL;

NULL 不支持加、减、乘、除、大小、相等比较，所有查询结果都为空。在处理空值之前，需要把空值改为有意义的值。

1
SELECT employee_name, employee_id, NVL (commission_pct, 0) AS commission_pct FROM dmhr.employee;

转换函数 (nvl)，只能转换 null 值为同类型或者可以隐式转换成同类型的值。

函数对空值的处理方式各不一样，返回结果不同：

1
2
3
4
-- 返回空值
SELECT GREATEST(16,NULL) FROM dual;
-- 返回预期的值
SELECT REPLACE('123456',3,NULL) FROM dual;

临时表

1
2
3
4
5
WITH x AS (
    SELECT ROWNUM AS seq, a.*
    FROM (  SELECT 编号, 项目, 金额 FROM dmhr.detail ORDER BY 编号) a
)
SELECT * FROM x;

列别名及部分列检索

列名通常是英文表示，所以可以给列取个别名，增强可读性。可使用 as 关键字或直接跟别名。示例语句如下所示：

1
2
3
4
-- 使用 as 关键字或直接跟别名
SELECT employee_id AS "员工编号", employee_name AS "员工姓名" FROM dmhr.employee;
-- 检索部分列需要明确指定要查询的列，而不是用 * 号替代
SELECT employee_id, employee_name, salary FROM dmhr.employee WHERE salary > 20000;

where 子句中引用别名列

where 条件引用别名一定要嵌套一层，因为别名是在 select 之后才有效。

1
SELECT * FROM (SELECT employee_id emid, email emna, salary sal FROM dmhr.employee) WHERE sal > 10000;

列拼接

1
2
3
4
5
6
7
-- 使用 || 可以把字符串拼接起来
SELECT employee_name || ' salary is ' || salary AS col1 FROM dmhr.employee;
-- 也可以将需要批量执行的 SQL 语句拼接起来
SELECT 'TRUNCATE TABLE ' || schema_name || '.' || table_name FROM ALL_TABLES_DIS_INFO 
WHERE schema_name = 'DMHR';
-- 字符串连接符 || 也可以改成 concat 函数
SELECT CONCAT ('ABC','BCD','DDD','BBB') AS "OUTPUT" FROM DUAL;

条件逻辑

1
2
3
4
5
6
7
8
9
SELECT 
    employee_name,
    salary,
    CASE
        WHEN salary <= 4000  THEN 'low'
        WHEN salary >= 12000 THEN 'high'
        ELSE 'ok'
    END AS salary_status
FROM dmhr.employee;

限制返回的行数

1
2
3
4
5
6
-- 在查询的时候并不是每次都要求返回所有的行，比如抽查的时候只要求返回 10 行，可以使用伪列 `rownum` 来过滤
SELECT * 
FROM (SELECT ROWNUM AS rn, t.* FROM dmhr.employee t WHERE ROWNUM <= 10)
WHERE rn = 2;
-- 使用 LIMIT 子句返回两行
SELECT  * FROM dmhr.employee LIMIT 2;

查询结果排序

1
2
3
4
5
6
-- 按子串排序、字符串替换、字符串混合排序及按指定列排序。
SELECT employee_name, hire_date FROM dmhr.employee WHERE ROWNUM < 5
ORDER BY hire_date ASC;
…………ORDER BY hire_date ASC; -- 按照列名排序
…………ORDER BY 2 ASC; -- 列名替换成数字，按第二列进行升序排序
…………ORDER BY 2 ASC, 3 DESC; -- 按多个字段排序

多列排序，若前面的列有重复，后面的排序才有用。即先通过前面的列将数据分组，再按照后面的列进行排序。

按子串排序

1
2
SELECT EMPLOYEE_NAME AS 姓名, SUBSTR (PHONE_NUM, -4) AS 尾号 FROM dmhr.employee WHERE ROWNUM < 5
ORDER BY 2;

TRANSLATE 应用

1
2
3
4
5
6
7
8
-- 实现字符替换
TRANSLATE(expr,from_string,to_string)
-- from_string 与 to_string 以字符为单位，对应字符逐个替换
SELECT TRANSLATE ('ab你好bcadefg', 'abcdefg', '1234567') AS new_str FROM DUAL; -- 输出 12你好2314567
-- 如果 to_string 为空，则直接返回空值
SELECT TRANSLATE('ab你好bcadefg','abcdefg','') AS new_str FROM DUAL; -- 输出 NULL
-- 如果 to_string 对应的位置没有字符，from_string 中列出的字符将会被删除
SELECT TRANSLATE('ab你好bcadefg','1abcdefg','1') AS new_str FROM DUAL;  --输出 你好

按数字和字母混合字符串中的字母排序

1
2
3
4
5
6
7
-- 为了实现混合字符串排序，首先需创建视图
CREATE OR REPLACE VIEW v AS
   SELECT postal_code || ' ' || city_id AS data FROM dmhr.location;
-- 查询视图信息
SELECT * FROM v;
-- 使用 translate 替换功能，把数字和空格都替换为空，然后再进行排序
SELECT data, TRANSLATE (data, '- 0123456789', '-') AS oper_type FROM v WHERE ROWNUM < 5 ORDER BY 2;

按指定条件排序

如需将工资在 6000~8000 之间的员工排列在靠前位置，以便优先查看。可以在查询中新生成一列，实现指定条件排序。

1
2
3
4
5
6
SELECT 
    job_title AS 职务,
    CASE WHEN min_salary >= 6000 AND min_salary <= 8000 THEN 1 ELSE 2 END AS 级别,
    min_salary AS 工资
FROM dmhr.job WHERE ROWNUM < 5
ORDER BY 2, 3;

范围处理

语法：lead(列名,n,m) over(partition by ... order by ...)，不带参数 n，m，则查找当前记录后面第一行的记录列名的值。

  • lead() 只能用于取后面第 n 行记录说明，不能取前面的。如果要取前面第 n 行记录说明，使用 lag（）函数。
  • over() 在什么条件之上，使用语法 over(partition by…order by…)。
  • partition by 按某个字段划分组。
  • order by 按某个字段排序。

可以使用分析函数 lead() over() 定位某一段连续值的范围。

可以使用分析函数 lead() over() 查找同一分区中行之间的差。

定位连续范围的起始点

详见 达梦技术文档-范围处理

多表查询 SQL 操作方法

union all

union all 与空字符串

使用 union 或者 union all 关键字合并多个结果集时，对应的列数必须一致，列的数据类型必须匹配。当其中一个结果集的列数不满足要求时，可以使用 NULL 或者 空字符串 填充。

1
2
3
4
5
6
7
8
-- 使用 NULL 填充
SELECT employee_name, department_id FROM dmhr.employee WHERE ROWNUM < 5
UNION ALL
SELECT 'DM2021', NULL FROM DUAL;
-- 使用空字符串填充
SELECT employee_name, department_id FROM dmhr.employee WHERE ROWNUM < 5
UNION ALL
SELECT 'DM2021', '' FROM DUAL;

如果存在 order by 子句，必须添加到 union 和 union all 的最后。

union all 与 or

union all 用于合并两个结果集。比如姓名为常鹏程的员工编号是 2002，所有 or 查询是正确的，但 union all 却重复了。

1
2
3
4
5
SELECT * FROM dmhr.employee WHERE employee_id = 2002 OR employee_name = '常鹏程';
------ 等同于 ------
SELECT * FROM dmhr.employee WHERE employee_id = 2002
UNION ALL
SELECT * FROM dmhr.employee WHERE employee_name = '常鹏程';

使用 union all 替换 or，执行计划更高效，出现重复行时，使用 union 去重。

union 与去重：union 也用于合并两个结果集，同时去重。union 相当于对 union all 的输出结果再执行一次 DISTINCT 操作。

差集函数

EXCEPT 用于从一个表（原表）里查找出某个目标表里不存在的值

1
SELECT department_id FROM dmhr.dept EXCEPT SELECT department_id FROM dmhr.employee;

EXCEPT 获取第一个结果集的数据，然后排除第二个结果集的数据，类似减法运算

IN、NOT IN、EXISTS

查询含有 null 值的行时，如果包含 IN、NOT IN 要注意两者的区别。IN 相当于 OR, 而 NOT IN 相当于 AND。

1
2
SELECT * FROM dmhr.employee WHERE employee_id IN (1002, 1005, NULL);
SELECT * FROM dmhr.employee WHERE employee_id NOT IN (1002, 1005, NULL);

第二句执行后返回记录为空，因为 NOT IN 的逻辑是 1002 AND 1005 AND NULL。当 NOT IN 后面跟的子查询返回的列存在 NULL 值，可能得不到正确的结果。

连接类型

连接包括：内连接、左连接、右连接、全连接、自连接 5 种类型，以上连接类型 DM 数据库都支持。

  • 内连接：结果完全满足连接条件的记录。

    1
    2
    SELECT tab1.col1, tab2.col2 FROM tab1, tab2 WHERE tab1.id = tab2.tab1_id;
    SELECT tab1.col1, tab2.col2 FROM tab1 JOIN tab2 ON (tab1.id = tab2.tab1_id);

  • 左外连接：结果不仅包含满足条件的记录，还包含位于左表中不满足条件的记录，此时右表的记录显示为 NULL。

    1
    SELECT tab1.col1, tab2.col2 FROM tab1 LEFT OUTER JOIN tab2 ON (tab1.id = tab2.tab1_id);

  • 右外连接：结果不仅包含满足条件的记录，还包含位于右表中不满足条件的记录，对应的左表的记录显示为 NULL。

    1
    SELECT tab1.col1, tab2.col2 FROM tab1 RIGHT OUTER JOIN tab2 ON (tab1.id = tab2.tab1_id);

  • 全外连接：结果不仅包含满足条件的记录，还会包含位于两边表中所有不满足条件的记录，对应的两边表的记录显示为 NULL。

    1
    SELECT tab1.col1, tab2.col2 FROM tab1 FULL OUTER JOIN tab2 ON (tab1.id = tab2.tab1_id);

  • 自连接：表和自身进行连接。

    1
    2
    3
    SELECT je.employee_name, je.department_id, d.employee_name
    FROM dmhr.employee je, dmhr.employee d
    WHERE (je.manager_id = d.employee_id);

    自连接查询至少要对一张表起别名，否则，服务器无法识别要处理的是哪张表。

数据操纵

插入数据

单表插入

插入一行或多行数据，或按指定列插入行和 MySQL 语法一样。

按指定列插入行，未指定值的列上若定义了默认值，则插入默认值。没有指定默认值，为 NULL，则插入 NULL 值。

多表插入

一次性向两张表中插入多条数据，存在默认值同样可以省略。

1
2
3
4
5
INSERT ALL
  INTO dmhr.t1 (id,name,class_id,tp)
  INTO dmhr.t2 (id,name,class_id,tp)
       SELECT dmhr.seq_id.nextval, DBMS_RANDOM.STRING('X',4), CEIL(DBMS_RANDOM.VALUE(100,106)), SYSDATE
    FROM DUAL CONNECT BY LEVEL <10;

复制表结构

1
2
-- 快速复制表结构且不需要数据
CREATE TABLE dmhr.t2 AS SELECT * FROM dmhr.t1 WHERE 1 = 0;

使用 SP_TABLEDEF 过程查看 t2 的结构，所有定义在 t1 列上的约束均没有被新表继承。

1
2
3
4
5
-- 需使用如下语句添加各类约束
ALTER TABLE dmhr.t2  ADD PRIMARY KEY (id);
ALTER TABLE dmhr.t2  ALTER COLUMN name SET DEFAULT 'dm2024';
ALTER TABLE dmhr.t2  ALTER COLUMN class_id SET NOT NULL;
ALTER TABLE dmhr.t2  ALTER COLUMN tp SET DEFAULT SYSDATE;

MERGE INTO 操作

使用 MERGE INTO 语法可合并 UPDATE 和 INSERT 语句。通过 MERGE 语句，根据一张表（或视图）的连接条件对另外一张表（或视图）进行查询，连接条件匹配上的进行 UPDATE（可能含有 DELETE），无法匹配的执行 INSERT。

使用 MERGE 可以实现记录“存在则 update，不存在则 insert”的逻辑。

1
2
3
4
5
6
7
8
MERGE INTO dmhr.dup_emp
     USING dmhr.emp_salary ON (dmhr.dup_emp.employee_id = dmhr.emp_salary.employee_id)
WHEN MATCHED
THEN 
    UPDATE SET dmhr.dup_emp.salary = dmhr.emp_salary.new_salary
WHEN NOT MATCHED
THEN
   INSERT VALUES (dmhr.emp_salary.employee_id,'dm2024',410107197103257999,dmhr.emp_salary.new_salary,102);

目标表 dup_emp 中添加了一条数据，其余行的 salary 列被更新成 emp_salary 中对应的值。如果 emp_salary 中有多行和目标表匹配成功，将会报错：没有一组稳定的行。

删除重复记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 通过 group by + having 子句分组查询的方式
SELECT employee_name, count(*) FROM dmhr.dup_emp GROUP BY employee_name HAVING COUNT(*) > 1;
-- 通过 group by + rowid 的方式
SELECT * FROM dmhr.dup_emp
WHERE ROWID NOT IN (  
    SELECT MAX (ROWID) FROM dmhr.dup_emp GROUP BY employee_name 
);
-- 可在查找到重复记录后直接删除                     
DELETE FROM dmhr.dup_emp
WHERE ROWID NOT IN (  
    SELECT MAX (ROWID) FROM dmhr.dup_emp GROUP BY employee_name
);
DELETE FROM dmhr.dup_emp t
WHERE ROWID <> (
    SELECT MAX (ROWID) FROM dmhr.dup_emp WHERE employee_name = t.employee_name
);