达梦数据库--常用SQL语句实战

达梦数据库作为中国自主研发的优秀数据库产品，以其强大的性能和可靠的安全性，正在越来越多的项目中广泛应用。但对于初学者来说，如何高效地掌握达梦数据库的SQL语法和操作技巧呢？本篇文章带你快速入门！

达梦数据库的SQL语法与常见的MySQL、Oracle有哪些异同？在日常开发和维护中，有哪些高频使用的SQL值得掌握？

在国家的推动下，达梦数据库正逐步取代部分国外数据库产品，成为国产数据库的代表。企业对达梦数据库的需求增长，也带动了市场对熟悉其SQL操作的技术人才的需求。

1.检查数据库版本及服务状态

1.1 查看达梦数据库运行状态

SELECT status$ as 状态 FROM v$instance;

1.2 查看达梦数据库版本

SELECT banner as 版本信息 FROM v$version;

2.创建用户并授权

2.1 创建用户

-- 使用 CREATE USER 语句创建 DM 用户，登录密码为 "dameng123"。

CREATE USER DM IDENTIFIED BY "dameng123";

2.2 授予用户基本权限

-- 使用GRANT语句给DM用户授予RESOURCE角色
GRANT RESOURCE TO DM;

-- 给 DM 用户授予 dmhr 用户下 employee 表的 select 权限；
GRANT SELECT ON dmhr.employee TO DM;

-- 给 DM 用户授予 dmhr 用户下 department 表的 select 权限；
GRANT SELECT ON dmhr.department TO DM;

-- 给 DM 用户授予 创建表的权限
GRANT CREATE TABLE to DM;

2.3 查看用户信息

-- 通过字典表 dba_users 查看基本信息

SELECT username,account_status,created FROM dba_users WHERE username='DM';

从输出结果看出，用户状态 account_status 为 open，输出的时间为用户的创建时间。

2.4 切换用户

-- 使用 conn 命令切换用户。

conn DM/dameng123;

注意：windows系统在DM管理工具中使用该命令无法完成用户切换，直接断开登录后重新连接，更换登录用户即可。

-- 使用 user 关键字返回当前登录用户。

SElECT user FROM DUAL;

3. 表相关

3.1创建表并添加约束

-- 创建employee表
CREATE TABLE employee(  
    employee_id INTEGER,  
    employee_name VARCHAR2(20) NOT NULL,
    hire_date DATE,
    salary INTEGER,
   department_id INTEGER NOT NULL
);

-- 使用 CREATE TABLE 语句创建 department 表
CREATE TABLE department
(  
department_id INTEGER PRIMARY KEY,
   department_name VARCHAR(30) NOT NULL
);

-- 非空约束
ALTER TABLE employee MODIFY( hire_date not null);
-- 主键约束
ALTER TABLE employee ADD constraint pk_empid PRIMARY KEY(employee_id);
-- 外键约束
ALTER TABLE employee ADD constraint fk_dept FOREIGN KEY (department_id) REFERENCES department (department_id);

​​​​​​​

3.2 查看表主键外键

SELECT table_name, constraint_name, constraint_type FROM all_constraints 
WHERE owner='DM' AND table_name='EMPLOYEE';

​​​​​​​

3.3 表字段增加

ALTER TABLE "DM"."EMPLOYEE" add "IS_REPEAT" VARCHAR(255);
COMMENT ON COLUMN "DM"."EMPLOYEE"."IS_REPEAT" IS '是否重复，1、是，0、否';

​​​​​​​

3.4 表字段删除

ALTER TABLE "DM"."EMPLOYEE" DROP COLUMN IS_REPEAT;

4. 验证数据表 CRUD 功能

-- 插入数据
INSERT INTO department VALUES(666, '数据库产品中心');
INSERT INTO employee VALUES (9999, '王达梦','2008-05-30 00:00:00', 30000, 666);
commit;

-- 因为 employee 员工表和 department 部门表存在主外键约束，所以此示例中须按顺序执行插入语句，即先在 department 表中插入数据。
-- 更新数据
UPDATE employee SET salary='35000' WHERE employee_id=9999;
commit;

-- 验证数据
SELECT salary,employee_id FROM employee;

-- 删除数据
DELETE FROM employee;
DELETE FROM department WHERE department_id=666;
commit;

-- 验证数据
SELECT * FROM employee;

​​​​​​​

5. 批量插入及选择排序

-- 在 t1 表中批量插入 100000 条数据记录。

CREATE TABLE t1 AS
SELECT rownum AS id,
       trunc(dbms_random.value(0, 100)) AS random_id,
       dbms_random.string('x', 20) AS random_string
FROM dual
connect BY level <= 100000;

-- 使用 COUNT(*) 聚集函数统计 t1 表中的总数据记录。
SELECT COUNT(*) FROM t1;

-- 使用 ORDER BY 语句实现选择排序。
SELECT * FROM t1 where rownum<5 ORDER BY id DESC;

​​​​​​​

6.验证分组查询

-- 插入准备数据
INSERT INTO department (department_id, department_name)
SELECT department_id, department_name FROM dmhr.department;
INSERT INTO employee
     (employee_id, employee_name, hire_date, salary, department_id)
SELECT employee_id, employee_name, hire_date, salary,
     department_id FROM dmhr.employee;
commit;

SELECT dept.department_name as 部门, count(*) as 人数
FROM employee emp, department dept
where emp.department_id=dept.department_id
GROUP BY dept.department_name
HAVING count(*) > 20;

​​​​​​​

7.创建视图

-- 定义视图

CREATE OR replace VIEW v1 AS
SELECT dept.department_name, emp.employee_name,
     emp.salary,emp.hire_date
FROM employee emp, department dept
WHERE salary > 10000
AND hire_date >= '2013-08-01'
AND emp.department_id = dept.department_id;

-- 通过视图简化查询

SELECT * FROM v1 WHERE hire_date > '2014-09-01';

​​​​​​​

8.索引

-- 创建普通索引
CREATE INDEX ind_emp_salary ON employee(salary);
SELECT table_name, index_name, index_type
from user_indexes WHERE index_name='IND_EMP_SALARY';

-- 删除索引
DROP INDEX IND_EMP_SALARY;

​​​​​​​

9.创建表

-- 若该表存在则删除
DROP TABLE IF EXISTS TEST_TEMP; 

-- 创建对应的表：
-- 主键自增配置  IDENTITY(1, 1)，CLUSTER PRIMARY KEY("ID")
-- 自动创建时间  DEFAULT (CURDATE)
CREATE TABLE "TEST_TEMP"
(
"ID" INT IDENTITY(1, 1) NOT NULL,
"PROJECT_KEY"   VARCHAR(100), 
"CREAT_TIME"   DATE       DEFAULT (CURDATE),
CLUSTER PRIMARY KEY("ID"))
STORAGE(ON "TEST", CLUSTERBTR) ;

​​​​​​​

9.1 批量插入数据

-- 语法
SET IDENTITY_INSERT [<模式名>.]<表名> ON | OFF;
-- 初始化数据，自增id赋值。需要指定列且IDENTITY_INSERT设置ON
SET IDENTITY_INSERT SYSDBA.TEST_TEMP ON;
INSERT INTO TEST_TEMP(ID,PROJECT_KEY,CREAT_TIME) VALUES (1, '测试项目', '2022-12-15 14:15:30');

-- 记得提交，否则无法真正的落库。
commit;

​​​​​​​

9.2 数据库自动新增日期

-- 自动生成日期，不带时分秒
"CREAT_TIME"   DATE       DEFAULT (CURDATE),

-- 自动生成日期，带时分秒
"CREAT_TIME"   TIMESTAMP     DEFAULT (SYSDATE),

​​​​​​​

9.3 数据库自动更新时间

通过触发器脚本实现

create trigger UPDATE_TIME_CUSTOM_NAME
before update on "DEV"."CUSTOM_NAME" for each row
begin
new.UPDATE_TIME:=sysdate;
end;

​​​​​​​

解析

UPDATE_TIME_CUSTOM_NAME：触发名称

DEV： 模式名

CUSTOM_NAME：表名称

UPDATE_TIME：字段名称

– 字段配置，自动生成日期，带时分秒

“UPDATE_TIME” TIMESTAMP DEFAULT (SYSDATE),

10. 查询表中的字段信息

-- SYSDBA为模式，TEST_TEMP 为表名称
-- 查询表字段情况，但不包含注释信息

select * from all_tab_columns where owner='SYSDBA' and Table_Name='TEST_TEMP'

​​​​​​​​​​​​

-- 查询表字段的注释信息
select * from dba_col_comments  where owner='SYSDBA' and Table_Name='TEST_TEMP'

11.修改字段

11.1 修改字段名称及注释

-- 将SYSDBA模式的TEST_TEMP表中的UPDATE_TIME字段名称修改为CREATE_TIME

ALTER TABLE  "SYSDBA"."TEST_TEMP"  RENAME COLUMN "UPDATE_TIME" TO "CREATE_TIME";
COMMENT ON COLUMN "SYSDBA"."TEST_TEMP"."CREATE_TIME" IS '数据创建时间';

​​​​​​​

11.2 修改字段类型

-- 将SYSDBA模式的TEST_TEMP表中的CREATE_TIME 字段类型修改为TIMESTAMP DEFAULT SYSDATE

ALTER TABLE "SYSDBA"."TEST_TEMP" MODIFY CREATE_TIME TIMESTAMP DEFAULT SYSDATE

​​​​​​​如果你正在学习达梦数据库，不妨使用官方提供的**达梦管理工具（DM GUI）**进行实践操作，结合项目中的真实场景加深理解。同时，在线文档和教程也是学习的绝佳助手。

掌握达梦数据库的常用SQL，不仅是高效操作数据库的基础，更是提升职业竞争力的重要技能。随着国产数据库的快速发展，提前熟悉达梦SQL，将为你的职业发展增添更多机会。