基于达梦Sql基础学习

基于达梦Sql基础学习

---

# 1.单表查询 查看表结构 方法一： SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE','DMHR') FROM dual; 方法二： SP_TABLEDEF('DMHR','EMPLOYEE');

空值与运算
NULL 不支持加、减、乘、除、大小、相等比较，所有查询结果都为空。
处理空值
因为 NULL 不支持加、减、乘、除、大小、相等比较，所以我们在处理空值之前，需要把空值改为有意义的值。示例语句如下所示：
SELECT employee_name, employee_id, NVL (commission_pct, 0) AS commission_pct
FROM dmhr.employee;
空值与函数
函数对空值的处理方式各不一样，有些会返回空值，示例语句如下所示：
SELECT GREATEST(16,NULL) FROM dual;
查找满足多个条件的行
对于需要进行多个条件组合的复杂查询，例如：需查询部门编号为 102 的员工，或者工资大于 20000 的员工，或者部门编号为 105 且工资大于 9000 的员工。示例语句如下所示：

2.多表联合检索

IN、NOT IN、EXISTS比较：

查询含有 null 值的行时，如果包含 IN、NOT IN 要注意两者的区别。IN 相当于 OR, 而 NOT IN 相当于 AND。示例语句如下所示：
SELECT * FROM dmhr.employee WHERE employee_id IN (1002, 1005, NULL);
返回 2 行数据记录，示例语句如下所示：
SELECT * FROM dmhr.employee WHERE employee_id NOT IN (1002, 1005, NULL);
返回记录为空，因为 NOT IN 的逻辑是 1002 AND 1005 AND NULL。当 NOT IN 后面跟的子查询返回的列存在 NULL 值，可能得不到正确的结果。
聚集与内连接
首先建立案例用表，示例语句如下所示：

以上是员工奖金发放表，type 列决定了奖金的数额。若 type=1，则奖金是工资的 10%；若 type=2，则奖金是工资的 20%; type=3，则奖金是工资的 30%。要求返回上述（部门编号是 105）员工工资和奖金的总额。
先关联再聚合，示例语句如下所示：
SELECT e.employee_id,e.employee_name,e.salary,e.department_id,e.salary
* CASE WHEN eb.TYPE = 1 THEN .1 WHEN eb.TYPE = 2 THEN .2 ELSE .3 END
AS bonus
FROM dmhr.employee e, dmhr.emp_bonus eb
WHERE e.employee_id = eb.employee_id

聚合后，示例语句如下所示：

SELECT department_id, SUM (salary) AS total_sal, SUM (bonus) AS total_bonus
FROM (SELECT e.employee_id,e.employee_name,e.salary,e.department_id,e.salary
* CASE
WHEN eb.TYPE = 1 THEN .1
WHEN eb.TYPE = 2 THEN .2
ELSE .3
END
AS bonus
FROM dmhr.employee e, dmhr.emp_bonus eb
WHERE e.employee_id = eb.employee_id) y
GROUP BY y.department_id

3.数据操纵

删除重复记录
实际工作中经常遇到表内包含重复数据的情况，下面介绍几种删除重复数据的方法。
//准备数据，使用上面创建的 dup_emp 表，插入重复数据:
INSERT INTO dmhr.dup_emp VALUES
(1109, ‘程东生’, ‘410107197103252999’, 4400, 102),
(1110, ‘王金玉’, ‘410107197103258999’, 4300, 102),
(1111, ‘程东生’, ‘410107197103252999’, 4400, 102);
COMMIT;
可在查找到重复记录后直接删除，示例语句如下所示：
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);

SELECT * FROM dmhr.dup_emp;

4.如何使用字符串

将字符与数字分离
使用 regexp_replace 正则表达式实现字符串中字符与数字分离。
创建测试视图，示例语句如下所示：
CREATE OR REPLACE VIEW v
AS
SELECT ‘CLARK10’ data FROM DUAL
UNION ALL
SELECT ‘MILLER20’ FROM DUAL
UNION ALL
SELECT ‘KING30’ FROM DUAL;
使用 regexp_replace 正则表达式，示例语句如下所示：
SELECT REGEXP_REPLACE (data, ‘[0-9]’, ‘’) dname,
REGEXP_REPLACE (data, ‘[^0-9]’, ‘’) deptno
FROM v;

创建分隔列表
通过 listagg 分析函数实现多行字段的合并显示。
创建测试视图，示例语句如下所示：
CREATE OR REPLACE VIEW v
AS
SELECT ‘10’ deptno, ‘CLARK’ name, ‘800’ sal FROM DUAL
UNION ALL
SELECT ‘10’, ‘KING’, ‘900’ FROM DUAL
UNION ALL
SELECT ‘20’, ‘JAMES’, ‘1000’ FROM DUAL
UNION ALL
SELECT ‘20’, ‘KATE’, ‘2000’ FROM DUAL
UNION ALL
SELECT ‘30’, ‘JONES’, ‘1150’ FROM DUAL;
使用 listagg 分析函数实现合并显示，示例语句如下所示：
SELECT deptno,SUM (sal) AS total_sal,
LISTAGG (name, ‘,’) WITHIN GROUP (ORDER BY name) AS total_name
FROM v
GROUP BY deptno;

5.如何使用数字

返回最值所在的行数据
如需查询最大工资 (30000) 所在行的员工姓名。
我们可以使用分析函数满足这个需求，还可以同时取最大和最小值。示例语句如下所示：

6.日期运算

操作方法
加减日、月、年
date 类型的数据可以直接加减天数，加减月份需要使用 add_months 函数，同时也可以使用 add_days 加减天数。示例语句如下所示：
//根据某个员工的入职日计算其前五天、后五天、前五个月、后五个月、前五年及后5年的具体时间

7.日期操作

EXTRACT
EXTRACT 函数可以提取时间字段中的年、月、日、时、分、秒，返回的值为 NUMBER 类型。示例语句如下所示：

创建本月日历
枚举指定月份所有的日期，并转换为对应的周信息，再按所在周做一次 行转列 即可。示例语句如下所示：

8.范围处理

定位连续范围的起始点
为了便于引用，先定义一个项目计划的明细视图 v：
CREATE OR REPLACE VIEW v(pro_id,pro_start,pro_end) as
SELECT 1,date ‘2020-10-01’,date ‘2020-10-02’ FROM dual UNION ALL
SELECT 2,date ‘2020-10-02’,date ‘2020-10-03’ FROM dual UNION ALL
SELECT 3,date ‘2020-10-03’,date ‘2020-10-06’ FROM dual UNION ALL
SELECT 4,date ‘2020-10-06’,date ‘2020-10-07’ FROM dual UNION ALL
SELECT 5,date ‘2020-10-09’,date ‘2020-10-11’ FROM dual UNION ALL
SELECT 6,date ‘2020-10-13’,date ‘2020-10-15’ FROM dual;
SELECT * FROM v;

现在要求把连续的项目合并，返回合并后的起始时间。比如上例中合并后的时间是 ‘2020-10-01’ 到 ‘2020-10-07’。可按照如下操作步骤实现：
提取上一个工程结束时间
CREATE OR REPLACE VIEW x0
AS
SELECT pro_id AS 编号,
pro_start AS 开始日期,
pro_end AS 结束日期,
LAG (pro_end) OVER (ORDER BY pro_id) AS 上一工程结束日期
FROM v;
SELECT * FROM x0;

9.触发器

操作方法
表级触发器
表级触发器的触发动作是三种数据操作命令，即 INSERT、DELETE 和 UPDATE 操作。
创建触发器，如下所示：
CREATE OR REPLACE TRIGGER TRG_NAME
AFTER INSERT OR DELETE OR UPDATE ON TABLE_NAME
FOR EACH ROW – 行级：此子句一定不能省略
BEGIN
PRINT ‘INSERT OR DELETE OR UPDATE OPERATION ON TABLE_NAME’;–要执行的SQL
END;

时间触发器
时间触发器属于一种特殊的事件触发器，可以定义一些有规律性执行的、定点执行的任务。
创建时间触发器，在屏幕上每隔一分钟输出一行 HELLO WORLD。如下所示：
CREATE OR REPLACE TRIGGER timer2
AFTER TIMER on database
for each 1 day for each 1 minute
BEGIN
print ‘HELLO WORLD’;
END;

10. 闪回查询

开启闪回功能方法一，示例语句如下所示：
alter system set ‘enable_flashback’=1 both;
闪回查询
按时间查询历史记录
通过指定一个时间（timestamp类型），通常可以用一个日期时间字符串表达。
测试在 disql 工具中进行，使用 dmhr 模式下的 city 表。示例语句如下所示：
SELECT *
FROM dmhr.city
WHERE city_id = ‘CD’;

UPDATE dmhr.city
SET region_id = 10
WHERE city_id = ‘CD’;

SELECT *
FROM dmhr.city
WHERE city_id = ‘CD’;

SELECT *
FROM dmhr.city WHEN
TIMESTAMP WHERE ‘2020-11-03 09:56:06’ city_id=‘CD’;
按事务 id 号查询历史记录
首先使用 versions_endtrxid 伪列查询事务 id 号。用户通过闪回版本查询子句，可以得到指定表过去某个时间段内，事务导致记录变化的全部记录，指定条件可以为时刻或事务号。
执行闪回版本查询，示例语句如下所示：
SELECT versions_endtrxid, *
FROM dmhr.job VERSIONS BETWEEN TIMESTAMP ‘2020-11-03 10:30:00’ AND SYSDATE
WHERE JOB_ID = 22;

11.物化视图

创建物化视图
创建基于主键的物化视图
默认的物化视图类型，通过主键来标示行的变化，表上必须有主键。示例语句如下：
CREATE MATERIALIZED VIEW mv_employees REFRESH WITH PRIMARY KEY AS
SELECT * FROM dmhr.employee;
查询创建的物化视图。示例语句如下：
SELECT * FROM user_mviews t WHERE mview_name=‘MV_EMPLOYEES’;

12.创建Dblink

数据库链接创建与使用
数据库链接对象 (LINK) 是 DM 中的一种特殊的数据库实体对象，它记录了远程数据库的连接和路径信息，用于建立与远程数据的联系。
同构数据库链接—DM 到 DM
创建 DM 到 DM 的数据库链接，并使用数据库链接对远程库做增、删、改、查操作。
注意
准备两台数据库服务器 A、B（目前只支持同平台，不支持跨平台建数据库连接），均安装了 DM 数据库，网络必须互通。
两台服务器，其中一个为目的主机 A，另一个为测试机 B；分别在这两台服务器上进入数据库安装目录下的库目录里修改 dm.ini 文件：MAL_INI=1，实例名 INSTANCE_NAME 要对应，且配置 dmmal.ini（如无此文件，新建此文件）如下所示：
[mal_inst1]
mal_inst_name = DMSERVER --A的实例名
mal_host = 127.0.0.1 --A的ip
mal_port = 5282
[mal_inst2]
mal_inst_name = DMSERVER2 --B的实例名
mal_host = 127.0.0.1 --B的ip
mal_port = 5283
注意
A 和 B 的实例名不能一样；–中文注释部分只是方便解释含义，配置 dmmal.ini 时不必写，以免造成格式问题导致 dmserver 服务无法启动。
两台主机的 dmmal.ini 文件相同。配置成功之后分别重启 dmserver DM 数据库服务。
在主机 A 上建表 test，如下所示：
CREATE TABLE TEST(C1 INT,C2 VARCHAR(20));
在 B 上建立到 A 的数据库链接 LINK01，使用链接进行插入、更新和删除操作。如下所示：
CREATE PUBLIC LINK LINK01 CONNECT WITH SYSDBA IDENTIFIED BY SYSDBA USING ‘127.0.0.1/5282’;

INSERT INTO TEST@LINK01 VALUES(1,‘A’);
INSERT INTO TEST@LINK01 VALUES(2,‘B’);
UPDATE TEST@LINK01 SET C2=‘C’ WHERE C1=1;
DELETE FROM TEST@LINK01 WHERE C1=2;

COMMIT;
在 B 上查询 A 服务器上表 test 的数据。如下所示：
select * from TEST@LINK01;

13.视图同义词

视图略前面有介绍过，同义词创建与使用
同义词 (Synonym) 让用户能够为数据库的一个模式下的对象提供别名，可以替换模式下的表、视图、序列、函数、存储过程等对象。
创建同义词。在 DMHR 模式下的表 T1 创建同义词。如下所示：
//进入 DMHR 模式
SET SCHEMA DMHR;
//创建 T1 表
CREATE TABLE DMHR.T1 (ID INTEGER, NAME VARCHAR(50), PRIMARY KEY(ID));
INSERT INTO DMHR.T1 (ID, NAME) VALUES (1, ‘张三’);
INSERT INTO DMHR.T1 (ID, NAME) VALUES (2, ‘李四’);
//对表 T1 创建同义词。
CREATE SYNONYM DMHR.S1 FOR DMHR.T1
其他用户查询 T1 表，可以通过同义词 S1 查询。如下所示：
SELECT COUNT(*) FROM DMHR.S1;

14.存储过程

操作方法
存储过程创建与使用
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。
创建带参数存储过程。如下所示：
//创建测试表 test_tab
create table test_tab (id int primary key, name varchar(30));
//创建有参数存储过程 p_test
create or replace procedure p_test(i in int)
as j int;
begin
for j in 1 …i loop
insert into test_tab values(j,‘p_test’||j);
end loop;
end;
执行调用过程。如下所示：
p_test(3);
//对表 test_tab 进行查询
select * from test_tab;

15.函数

自定义函数的创建
函数主要有下面几部分组成：
输入部分：函数可以有输入参数，在调用函数时，必须给输入参数赋值。
逻辑计算部分：逻辑计算部分是由 DMSQL 块组成业务逻辑计算部分。这部分主要是通过输入参数、表数据、SQL 计算函数等进行逻辑计算得到想要的结果。
输出部分：通过逻辑计算部分，我们会得到一个函数的唯一返回值进行返回（函数必须要有返回值）。
语法结构如下：
create [or replace] function 函数名
([p1,p2…pn])
return datatype
is|as
–声明部分
begin
–程序块
end
语法解析：
function 是创建函数的关键字。
p1,p2…pn 是函数的入参，DM 数据库创建的函数也可以不需要入参。
return datatype 是函数的返回值的类型。
通过 is 或者 as 承接着程序块。这部分是函数的计算内容。
自建 GET_SEX 函数，可以根据身份证号计算出员工的性别。示例语句如下所示：
CREATE OR REPLACE FUNCTION GET_SEX(id_card IN VARCHAR(50))
RETURN CHAR(2)
AS
v_sex CHAR(2);
BEGIN
IF to_number(substr(id_card,17,1))%2=1 THEN
v_sex:= ‘男’;
ELSE
v_sex:= ‘女’;
END IF;
RETURN v_sex;
END;
使用函数：
select identity_card ,GET_SEX(identity_card) from DMHR.EMPLOYEE;

以上所有都是参考达梦云适配文档，更加详细见https://eco.dameng.com/