简单sql与dm功能实验记录

1. 实例与版本状态查询实验说明通过查询动态性能视图v$instance和v$version确认当前数据库实例的运行状态如 OPEN 状态以及具体的数据库版本号。-- 查看数据库实例当前状态 SELECT status$ AS 状态 FROM v$instance; -- 查看数据库版本信息 SELECT banner AS 版本信息 FROM v$version;2. 用户创建与权限管理实验说明常规业务不建议直接使用SYSDBA。创建了一个名为DM的普通用户并为其授予了基础的RESOURCE角色允许建表等操作同时赋予了查询dmhr测试表数据的特定权限。-- 创建新用户并设置密码 CREATE USER DM IDENTIFIED BY dameng123; -- 授予基础资源角色 GRANT RESOURCE TO DM; -- 授予跨模式查询权限 GRANT SELECT ON dmhr.employee TO DM; GRANT SELECT ON dmhr.department TO DM; -- 验证用户创建状态及创建时间 SELECT username, account_status, created FROM dba_users WHERE username DM;-- 切换到新用户连接注意密码中特殊字符的处理 conn DM/dameng123; -- 确认当前登录用户 SELECT user FROM DUAL;3. 表的创建与约束 (DDL)实验说明使用 DDL 语句建立employee和department两个实体表。重点演示了如何在建表后通过ALTER TABLE追加约束非空约束、主键PRIMARY KEY和外键FOREIGN KEY以保证数据的完整性。-- 创建员工表 (缺少主外键约束后续追加) 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 ( 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); -- 查询系统视图验证约束是否生效 SELECT table_name, constraint_name, constraint_type FROM all_constraints WHERE owner DM AND table_name EMPLOYEE;4. 基础数据变更 (DML) 与事务处理实验说明演示基础的增 (INSERT)、改 (UPDATE)、删 (DELETE) 操作。注意COMMIT的使用体现了关系型数据库的事务隔离特性操作只有在提交后才会持久化。-- 插入父表部门表数据 INSERT INTO department VALUES(666, 数据库产品中心); -- 插入子表员工表数据 INSERT INTO employee VALUES(9999, 王达梦, 2008-05-30 00:00:00, 30000, 666); COMMIT; -- 更新记录并提交 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. 海量测试数据生成 (CTAS 内置包)实验说明利用CREATE TABLE AS SELECT(CTAS)语法结合达梦的层次查询 (CONNECT BY LEVEL) 和内置随机函数包dbms_random快速生成 10 万条造数数据这对于性能测试和 SQL 调优非常实用。-- 生成 10 万条随机数据的测试表 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; -- 验证总行数 SELECT COUNT(*) FROM t1;-- 验证数据降序取前 4 条 SELECT * FROM t1 WHERE rownum 5 ORDER BY id DESC;6. 数据导入与多表联合聚合查询实验说明利用INSERT INTO ... SELECT语法从其他模式批量拉取数据。随后演示了包含多表关联 (JOIN)、分组 (GROUP BY) 和分组过滤 (HAVING) 的复合查询。-- 从 dmhr 模式批量导入数据 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; -- 多表关联查询统计员工数大于 20 人的部门 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;-- 为 salary 字段创建索引 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;8. 事务保存点控制 (SAVEPOINT)实验说明利用SAVEPOINT可以在一个大的事务中设立“书签”。当某一步骤出错时可以使用ROLLBACK TO回退到特定保存点而不需要撤销整个事务这为复杂业务逻辑提供了容错空间。-- 插入基础数据 INSERT INTO employee VALUES(999, 罗小刚, 2020-05-30 00:00:00, 50000, 101); -- 设立保存点 SAVEPOINT my_insert; -- 进行数据更新 UPDATE employee SET department_id 102 WHERE employee_id 999; -- 查看更新后的结果此时部门 ID 为 102 SELECT employee_id, department_id FROM employee WHERE employee_id 999;-- 回滚到保存点 ROLLBACK TO my_insert; -- 再次查询发现已恢复至更新前的状态部门 ID 为 101 SELECT employee_id, department_id FROM employee WHERE employee_id 999;9. 序列的使用 (SEQUENCE)实验说明序列是生成自增唯一数值的利器常用于主键填充。实验展示了如何建立带有缓存的序列并使用NEXTVAL递增取值和CURRVAL获取当前值。-- 创建带缓存的自增序列 CREATE SEQUENCE SEQ1 START WITH 1 INCREMENT BY 1 MAXVALUE 10000 CACHE 5 NOCYCLE; -- 获取序列下一个值 SELECT seq1.nextval() FROM dual;-- 获取序列当前值 SELECT seq1.currval() FROM dual;10. 物化视图 (MATERIALIZED VIEW)实验说明与普通视图逻辑上的查询不同物化视图会将查询结果进行物理存储。本实验中配置了REFRESH COMPLETE ON COMMIT这意味着当基表发生事务提交时物化视图的数据会自动刷新同步。-- 创建物化视图在事务提交时自动全量刷新 CREATE MATERIALIZED VIEW mv1 BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT AS SELECT department_id AS 部门号, COUNT(*) AS 人数 FROM employee GROUP BY department_id; -- 查询物化视图 SELECT * FROM mv1 WHERE 部门号 101;-- 插入新数据触发基表变更 INSERT INTO employee VALUES(8888, 苏林, 2020-05-31 00:00:00, 60000, 101); COMMIT; -- 再次查询数据已自动聚合更新 SELECT * FROM mv1 WHERE 部门号 101;11. 自定义函数、存储过程与游标实验说明PL/SQL 编程基础。包括如何通过FUNCTION返回计算值以及如何通过PROCEDURE结合游标CURSOR遍历特定条件的数据行并执行批量更新。-- 创建函数生成指定长度的随机字符串用作密码 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; SELECT random_password(12) FROM dual;-- 预查 102 部门符合条件的员工 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);-- 创建存储过程使用游标遍历数据为符合条件的员工涨薪 15% CREATE OR REPLACE PROCEDURE proc( dept_in DM.employee.department_id%TYPE, hire_in VARCHAR2 ) 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; -- 执行存储过程 BEGIN proc(102, 2012-03-01 00:00:00); END; -- 验证涨薪结果 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);12. 触发器 (TRIGGER) 记录审计日志实验说明触发器是一种被动执行的特殊存储过程。在此演示了创建行级BEFORE UPDATE触发器。一旦员工姓名发生修改触发器就会拦截该动作并将修改前 (:old) 和修改后 (:new) 的数据记录到日志表trg中。-- 创建审计日志表 CREATE TABLE trg(name_old VARCHAR, name_new VARCHAR); -- 创建触发器在员工姓名更新前自动记录旧值和新值 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; -- 执行更新触发规则 UPDATE employee SET employee_name 达梦 WHERE employee_id 1001; COMMIT; -- 查询审计表验证 SELECT name_old, name_new FROM trg;13. 表分区技术 (自动间隔分区)实验说明针对海量数据表将其物理分区可以极大提升管理和检索效率。这里创建了一个间隔分区表 (Interval Partitioning)。除了划定初始区间外每当有不在现有区间内的新数据插入时数据库会按照设定的步长如 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) ); -- 导入测试数据 INSERT INTO emp_part SELECT * FROM dmhr.employee; COMMIT; -- 查询当前分区的元数据 SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE table_name EMP_PART ORDER BY high_value;-- 查询指定分区中的数据 SELECT * FROM emp_part PARTITION(P_BEFORE_2007); -- 插入一条 2020 年的新记录将触发自动创建新分区 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, wudmdameng.com, 15312348566, 2020-05-30, 11, 50000.00, 0, 1001, 101); COMMIT; -- 再次查询分区元数据观察新建的系统分区 SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE table_name EMP_PART ORDER BY high_value;14. 高级语法WITH FUNCTION 及 CTE 表达式实验说明展示了达梦对较新 SQL 标准的支持。WITH FUNCTION允许你在 SQL 语句内部临时声明函数并即时调用避免了对象污染。而通用表达式 (CTE) 则通过WITH t AS (...)使复杂的子查询结构更加清晰。-- 临时定义函数并即时调用 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;-- 使用 CTE 抽取入职最早和最晚的日期作为临时表 t复用于下方查询 WITH t AS ( SELECT MAX(hire_date) AS max_hd, MIN(hire_date) AS 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 );