达梦数据库

试玩地址：https://eco.dameng.com/tour/

达梦数据库

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

  1. 查看达梦数据库运行状态，build_version是小版本
    SELECT * FROM v$instance;
    SELECT status$ as 状态 FROM v$instance;

  2. 查看达梦数据库版本
    SELECT banner as 版本信息 FROM v$version;
    SELECT * FROM v$version;

创建用户并授权

  1. 创建用户
  • 使用 CREATE USER 语句创建 DM 用户，登录密码为 "dameng123"。
    CREATE USER DM IDENTIFIED BY "dameng123";
  1. 授予用户基本权限

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

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

  • 给 DM 用户授予 dmhr 用户下 employee 表的 update 权限；
    GRANT UPDATE ON dmhr.employee TO DM;

  1. 查看用户信息
  • 通过字典表 dba_users 查看基本信息。
    SELECT username,account_status,created FROM dba_users WHERE username='SYSDBA';

切换用户

需从 sysdba 用户切换到 DM 用户，以下小节操作均在 DM 普通用户下完成。

  1. 切换到DM用户
  • 使用conn命令切用户
    conn DM/dameng123;
    conn DM/dameng123@localhost:5236;
  1. 查看当前登录用户
    SELECT user FROM DUAL;

创建表并添加约束

  1. 创建表
  • 使用 CREATE TABLE 语句创建 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
    		);
  1. 添加表约束

  • 非空约束，使用 ALTER TABLE 语句给表增加非空约束。
    ALTER TABLE employee MODIFY (employee_name NOT NULL);

  • 主键约束，使用 ALTER TABLE 语句给表增加主键约束。
    ALTER TABLE employee ADD constraint pk_empid PRIMARY KEY(employee_id);

  • 外键约束，使用 ALTER TABLE 语句给表增加外键约束。
    ALTER TABLE employee ADD constraint fk_dept FOREIGN KEY (department_id) REFERENCES departm(department_id);

  1. 查看表主键外键，通过系统表 all_constraints 查看自定义的主键、外键。
    SELECT table_name, constraint_name, constraint_type FROM all_constraints WHERE owner='DM' AND table_name='EMPLOYEE';

验证数据表 CRUD 功能

  1. 插入数据

因为 employee 员工表和 department 部门表存在主外键约束，所以此示例中须按顺序执行插入语句，即先在 department 表中插入数据。

  • 使用 INSERT INTO 语句向 department 表插入数据记录。
    INSERT INTO department VALUES(666, '数据库产品中心');
  • 使用 INSERT INTO 语句向 employee 表插入数据记录。
    INSERT INTO employee VALUES(9999, '王达梦','2008-05-30 00:00:00', 30000, 666);
  • 插入语句后，使用commit语句提交事务。
    commit;
  1. 修改数据
  • 使用 UPDATE 语句更新表数据。
    UPDATE employee SET salary='35000' WHERE employee_id=9999;
  • 更新数据后，使用commit语句提交事务。
    commit;
  1. 删除数据
  • 使用 DELETE FROM 语句删除表数据。
    DELETE FROM employee;
  • 删除数据后，使用commit语句提交事务。
    commit;

批量插入及选择排序

  1. 批量插入数据
  • 在 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;
  1. 查看插入数据
  • 使用 COUNT(*) 聚集函数统计 t1 表中的总数据记录。
    SELECT COUNT(*) FROM t1;
  1. 排序数据
  • 使用 ORDER BY 语句实现选择排序。
    SELECT * FROM t1 where rownum<5 ORDER BY id DESC;

验证分组查询

  1. 插入准备数据
  • 使用 INSERT INTO 语句在 department 表中插入批量数据。
    INSERT INTO department (department_id, department_name) SELECT department_id, department_name FROM dmhr.department;
  • 使用 INSERT INTO 语句在 employee 表中插入批量数据。
    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 语句提交事务。
    commit;
  1. 分组查询
  • 使用 GROUP BY、HAVING 语句实现分组过滤。
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;

创建视图

  1. 定义视图

例如需要查询薪资大于 10000 且入职时间大于等于 2013 年 8 月 1 日 员工的部门名称、姓名、薪资、入职时间。

  • 使用 CREATE OR replace VIEW 语句定义视图 v1。
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;
  1. 通过视图简化查询
  • 使用 视图 v1 检索数据。
    SELECT * FROM v1 WHERE hire_date > '2014-09-01';

创建索引

  1. 创建普通索引
  • 使用 CREATE INDEX 语句创建普通索引。
    CREATE INDEX ind_emp_salary ON employee(salary);
  1. 查看创建的索引
  • 通过字典表 user_indexes 查看已创建索引的名称、类型。
    SELECT table_name, index_name, index_type from user_indexes WHERE index_name='IND_EMP_SALARY';
  1. 删除索引
  • 使用 DROP INDEX 语句删除索引。
    DROP INDEX IND_EMP_SALARY;

事务特性

  1. 插入数据并创建保存点
  • 在 employee 表中插入一条数据记录。
    INSERT INTO employee VALUES (999, '罗小刚', '2020-05-30 00:00:00', 50000, 101);
  • 插入数据后，使用 savepoint 语句创建保存点。
    SAVEPOINT my_insert;
  1. 更新数据记录
  • 使用 UPDATE 语句更新数据，不提交。
    UPDATE employee SET department_id=102 WHERE employee_id=999;
  1. 不提交查看数据记录
  • 使用 SELECT 语句查看在不提交状态下的数据记录。
    SELECT employee_id, department_id FROM employee WHERE employee_id=999;
  1. 回滚到保存点
  • 使用 ROLLBACK TO 语句回滚到保存点 my_insert。
    ROLLBACK TO my_insert;
  1. 验证保存点特性
  • 在回滚保存点成功后，再次查看数据记录，验证保存点特性，事务会回滚到保存点的状态。
    SELECT employee_id, department_id FROM employee WHERE employee_id=999;

序列

  1. 创建序列
  • 使用 CREATE SEQUENCE 语句创建序列，通过此命令创建了一个起始值 START WITH 为 1，步进值 INCREMENT BY 为 1，最大值 MAXVALUE 为 10000，CACHE 缓存值为 5，NOCYCLE 不循环使用序列。
    CREATE SEQUENCE SEQ1 START WITH 1 INCREMENT BY 1 MAXVALUE 10000 CACHE 5 NOCYCLE;
  1. 查询下一个序列号
  • 使用 seq1.nextval() 语句查询下一个序列号。
    SELECT SEQ1.nextval FROM dual;
  1. 查询当前序列号
  • 使用 seq1.currval 语句查询当前序列号。
    SELECT SEQ1.currval FROM dual;

创建物化视图

  1. 定义物化视图
  • 使用 CREATE MATERIALIZED VIEW 语句定义物化视图。 刷新方式：完全；刷新时机：基表数据提交。
CREATE MATERIALIZED VIEW mv1 BUILD IMMEDIATE REFRESH
     COMPLETE ON COMMIT AS
     SELECT department_id as 部门号, count(*) as 人数
     FROM employee GROUP BY department_id;
  1. 查看物化视图
  • 使用物化视图 mv1 检索数据。
    SELECT * FROM mv1 WHERE 部门号='101';
  1. 表中插入数据
  • 在 employee 表中插入一条数据记录,验证物化视图特性。
    INSERT INTO employee VALUES(8888, '苏林','2020-05-31 00:00:00', 60000, 101);
  • 在 employee 表中插入数据后，使用 commit 完成事务提交。
    COMMIT;
  1. 验证物化视图特性
  • 使用物化视图 mv1 检索数据。
    SELECT * FROM mv1 WHERE 部门号='101';

创建函数

  1. 创建生成随机数函数
  • 使用 CREATE OR REPLACE FUNCTION 语句创建函数。
CREATE OR REPLACE FUNCTION random_password
    (pass_len IN NUMBER) RETURN VARCHAR2 AS
    l_pw VARCHAR2(128);
    BEGIN l_pw = dbms_random.string('x', pass_len);
    RETURN l_pw;
    END;
  1. 调用函数生成随机数
  • 使用 random_password 函数，生成 12 位随机数。
    seleect random_password(12) from dual;

存储过程

  1. 创建存储过程
  • 使用 SELECT 语句查询 102 部门所有员工入职时间大于 2012 年 3 月 1 日 的员工上浮前的薪资。
SELECT employee_id, employee_name, salary
     FROM DM.employee
     WHERE department_id=102 AND hire_date >= to_date
     ('2012-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
  • 使用 CREATE OR REPLACE PROCEDURE 语句创建存储过程 proc。
CREATE OR REPLACE PROCEDURE proc
     (dept_in DM.employee.department_id%TYPE, hire_in varchar2(24))
     AS CURSOR by_dept_cur IS
     SELECT * FROM DM.employee WHERE department_id=dept_in;
     BEGIN FOR rec IN by_dept_cur
     LOOP
     IF rec.hire_date > to_date(hire_in, 'yyyy-mm-dd hh24:mi:ss')
     THEN
     UPDATE DM.employee set salary=salary+salary*0.15
     WHERE employee_id=rec.employee_id;
     END IF;
     END LOOP;
     commit;
     END;
  1. 调用存储过程
  • 调用存储过程实现薪资上浮。
    		begin
    		proc(102, '2012-03-01 00:00:00');
    		end;
  1. 查看上浮后的薪资
  • 使用 SELECT 语句查询 102 部门所有员工入职时间大于 2012 年 3 月 1 日 的员工上浮后的薪资。
    SELECT employee_id, employee_name, salary FROM DM.employee WHERE department_id=102 AND hire_date >= to_date ('2012-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');

触发器

表级触发器是基于表中数据的触发器，它通过针对相应表对象的插入/删除/修改等 DML 语句的触发。例如：在员工表上建立表级触发器，当姓名字段被更新时触发器动作。

  1. 创建表级触发器
  • 创建表 trg , 记录员工姓名更新前后的值。
    CREATE TABLE trg(name_old varchar, name_new varchar);
  1. 使用 CREATE OR REPLACE TRIGGER 语句创建触发器 trg1。
CREATE OR REPLACE TRIGGER trg1
     BEFORE
     UPDATE OF EMPLOYEE_NAME ON employee
     FOR EACH ROW
     DECLARE
     BEGIN
     INSERT INTO trg VALUES(:old.employee_name,
     :new.employee_name);
     END;
  1. 更新表数据
  • 更新编号为 1001 的员工姓名为 '达梦'。
    UPDATE employee SET employee_name='达梦' WHERE employee_id=1001;
  • 提交更新
    commit;
  1. 查看表数据
  • 查询 trg 表中员工更新前后的姓名。
    SELECT name_old , name_new FROM trg;

分区表

间隔分区可以在输入相应分区的数据后自动创建分区，是范围分区的扩展。例如：将 dmhr 用户下 employee 表中员工信息按入职时间以年为间隔转换为分区表。

  1. 创建间隔分区表
  • 创建分区表 emp_part, 起始分区时间为小于 2007 年 1 月 1 日。
CREATE TABLE emp_part
(
EMPLOYEE_ID INT PRIMARY KEY,
EMPLOYEE_NAME VARCHAR(20),
IDENTITY_CARD VARCHAR(18),
EMAIL VARCHAR(50) NOT NULL,
PHONE_NUM VARCHAR(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR(10) NOT NULL,
SALARY INT,
COMMISSION_PCT INT,
MANAGER_ID INT,
DEPARTMENT_ID INT
)
PARTITION BY RANGE(hire_date)
INTERVAL (NUMTOYMINTERVAL(1,'year'))
(
  PARTITION P_BEFORE_2007 VALUES less than (to_date('2007-01-01','yyyy-mm-dd'))
)
STORAGE
(
  FILLFACTOR 85,
  BRANCH(32,32)
);
  1. 插入数据，提交
INSERT INTO emp_part SELECT * FROM dmhr.employee;
commit;
  1. 查询分区信息
  • 通过 usertabpartitions 表查询分区信息。
    SELECT table_name,partition_name, high_value FROM user_tab_partitions WHERE table_name='EMP_PART' ORDER BY high_value;
  1. 检索某个分区数据
  • 按分区名称检索数据如下：
    SELECT * FROM emp_part PARTITION(P_BEFORE_2007);
  1. 插入数据，自动新增分区表
  • 在 emp_part 表中插入一条新记录，员工入职时间为 2020 年 5 月 30 日。
INSERT INTO emp_part(EMPLOYEE_ID,EMPLOYEE_NAME,IDENTITY_CARD,EMAIL,
     PHONE_NUM,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
     VALUES(9990,'武达梦','340102196202303999','wudm@dameng.com','15312348566',
     '2020-05-30','11',50000.00,0,1001,101);
  • 提交更新。
    commit;
  1. 查询新增数据分区
    SELECT table_name,partition_name, high_value FROM user_tab_partitions WHERE table_name='EMP_PART' ORDER BY high_value;

WITH 子句

WITH FUNCTION 子句用于在 SQL 语句中临时声明并定义存储函数，这些存储函数可以在其作用域内被引用。

  1. 定义 WITH FUNCTION 子句
  • 通过员工编号获取对应的薪资。
WITH FUNCTION GetSalary(emp_id INT) RETURN INT AS
     DECLARE
     sal int;
     BEGIN
     SELECT salary into sal FROM dmhr.employee WHERE employee_id=emp_id;
     RETURN sal;
     END;
     SELECT GetSalary(2001) FROM DUAL;
  1. 定义 WITH AS 子句

使用 WITH AS 子句可以优化查询流程，获得清晰的格式。例如：统计入职时间最早和最迟的员工的编号、姓名和入职时间。

WITH t AS (
     SELECT MAX(hire_date) max_hd, MIN(hire_date) min_hd FROM dmhr.employee)
     SELECT employee_name, employee_id, hire_date FROM dmhr.employee
     WHERE hire_date IN
     (
      SELECT t.max_hd FROM t
      UNION ALL
      SELECT t.min_hd FROM t
     );
posted @   武胜造纸农  阅读(342)  评论(0)  编辑  收藏  举报
相关博文：
· 搭建harbor仓库
· mysql8.0管理用户
· DM(达梦数据库基本操作)
· 达梦数据库可能用到的SQL语句
· 达梦数据库操作及一些奇葩错误
阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）
点击右上角即可分享
微信分享提示