首页 > 数据库 >达梦数据库

达梦数据库

时间：2024-06-20 15:59:11浏览次数：27  
标签：语句 name 数据库 department 达梦 employee id SELECT

试玩地址：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','[email protected]','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
     );

标签：语句,name,数据库,department,达梦,employee,id,SELECT
From： https://www.cnblogs.com/wszzn/p/18258812

相关文章

  • 5.24实验三 数据库完整性、安全性实现
    实验三 数据库完整性、安全性实现一、实验目的:使学生加深对数据库安全性和完整性的理解，并掌握SQLServer中有关用户、角色及操作权限的管理方法，学会创建和使用规则、缺省和触发器以及存储过程。二、实验要求：通过实验对数据进行完整性控制、安全性维护。三、实验步骤:......
  • 5.27实验四 数据库的备份和恢复
    实验四 数据库的备份和恢复一、实验目的：熟悉并掌握数据库备份和恢复的原理和操作。二、实验要求：掌握存储设备的创建、使用。掌握数据库中数据的导入导出操作。掌握数据上的备份和恢复操作。掌握数据库备份策略的制定原理和具体操作。三、实验步骤：1、开始→程序→Micros......
  • 6.3实验五 数据库编程
    实验五 数据库编程一、实验目的熟悉并掌握嵌入式SQL编程、使用数据库访问接口技术实现对数据库的访问。二、实验要求熟悉使用嵌入式SQL编程访问数据库，熟悉VB中开发数据库应用程序的过程。三、实验步骤设计一个小型的数据库应用程序 1. 基本表建立（1）教师表建立Xum......
  • 5.21实验三 Web数据库程序设计
    一、实验目的通过使用JSP技术设计一个简单的数据库管理系统，了解展示页面和编辑页面的区别，掌握Web服务器与MySQL数据库的连接和数据库操作的方法，掌握使用Java语言编写JSP文件的方法。二、实验内容和基本要求从以下列举的四个数据库中，任选其一，或者自行定义其他数据库，每个数据库中......
  • 5.22 实验一 数据库和表的建立、数据操作
    实验一 数据库和表的建立、数据操作一、实验目的：掌握使用SQL语言进行数据定义和数据操纵的方法。二、实验要求：建立一个数据库stumanage，建立三个关系表student，course，sc。向表中插入数据，然后对数据进行删除、修改等操作，对关系、数据库进行删除操作。三、实验步骤：1、......
  • 关于数据库的一些常用操作
    数据定义语言（DDL）DDL主要用于创建、修改和删除数据库中的对象，如数据库、表、视图等。创建数据库sql复制代码CREATEDATABASE数据库名;选择数据库sql复制代码USE数据库名;创建表sql复制代码CREATETABLE表名(列名1数据类型1,列名2数据类型2,......
  • 服务器数据库
    数据库往往是服务器中最核心的部分，所以一旦数据库发生损坏，将会带来巨大的损失，因此数据库的数据恢复功能变得越来越重要了。在服务器运行过程中，由于断电、操作不当原因损坏到服务器的硬盘的时候，怎样才能恢复网站服务器的数据呢？一、服务器存储系统非常重要，硬盘作为服务器数据存储......
  • 数据库数据恢复-oracle数据库常见故障及数据恢复分析
    作为存储和处理数据的系统，oracle数据库在使用过程中不可避免会出现各种导致数据丢失和数据损坏的故障。总结出oracle数据库常见故障以及恢复可能性。1、Oracle数据库无法启动或者启动后无法正常工作。如果故障是突发性的，通常情况下恢复的可能性极高。出现这类故障后，首先要检查s......
  • SQL、Mysql、数据库到底什么关系
    sql很多都搞不清Sql和Mysql，数据库之间的关系，其实这些概念之间的关系用下面这张图就能搞懂：也就是说，数据库是草图，Mysql是交稿，数据可以理解成砖块，sql是处理砖块（数据）的工具。下面这张图是SQL的常见分类，以及常用操作指令：数据库中表的结构包括了代表列名的列，和具体数据的行表中的......
  • 数据库管理系统的数据隔离级别
    数据库中的幻读和不可重复读是并发事务处理中的两个重要概念，它们与事务的隔离级别紧密相关。以下是对这些概念的进一步解释以及它们在不同隔离级别下的表现：不可重复读（Non-repeatableRead）:不可重复读发生在一个事务读取了某个数据项，然后在同一事务中再次尝试读取同一数据项时，发......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99