📚 前言：为什么选择学习国产数据库？
在当今“数字中国”建设和信创产业发展的大背景下，国产数据库技术正迎来黄金发展期。作为一名计算机应用技术专业的学生，我深刻认识到掌握国产数据库技术不仅是学术要求，更是未来职业发展的核心竞争力。在《国产数据库技术》课程的学习中，我系统学习了达梦数据库DM8，并在此分享我的实战经验与技术心得。
学习价值：
国家战略需求：信创产业核心基础软件
就业优势：掌握稀缺技术，提升就业竞争力
技术积累：理解数据库底层原理，夯实基础
📋 目录
第一部分：环境部署篇
1.1 DM8数据库安装全流程
1.2 系统环境优化配置
1.3 常见安装问题解决方案
第二部分：配置管理篇
2.1 数据库实例初始化与参数优化
2.2 存储结构与表空间管理
2.3 用户权限与安全策略
第三部分：备份恢复篇
3.1 多级备份策略设计
3.2 数据恢复实战演练
3.3 容灾方案规划
第四部分：SQL编程篇
4.1 DMSQL函数与存储过程
4.2 复杂查询优化技巧
4.3 事务处理与并发控制
第五部分：性能调优篇
5.1 SQL执行计划分析
5.2 索引优化策略
5.3 系统监控与故障排查
第六部分：项目实战篇
6.1 学生管理系统数据库设计
6.2 电商平台数据库优化案例
第七部分：学习总结与建议
🚀 第一部分：环境部署篇
1.1 DM8数据库安装全流程（Windows环境）
安装前准备：
下载DM8安装包（官网或学校资源平台）
检查系统要求：
Windows 10/Server 2016+
内存≥4GB，磁盘空间≥20GB
关闭杀毒软件实时防护
详细安装步骤：
bash
复制
下载
# 步骤1：以管理员身份运行安装程序 DMInstall.exe -i # 步骤2：选择安装组件（建议全选） ☑ 数据库服务器 ☑ 客户端工具 ☑ 管理工具 ☑ 示例数据库 # 步骤3：设置安装路径（避免中文路径） 安装目录：D:\dmdbms\ # 步骤4：初始化数据库实例 实例名：DMSERVER 端口号：5236 数据库名：DAMENG 字符集：UTF-8 页大小：32KB # 步骤5：设置管理员密码 SYSDBA密码：Dameng123（实际环境需复杂密码） # 步骤6：完成安装并启动服务
验证安装成功：
sql
复制
下载
-- 使用DM管理工具连接 -- 主机：localhost -- 端口：5236 -- 用户名：SYSDBA -- 密码：Dameng123 -- 执行测试查询 SELECT '达梦数据库DM8安装成功！' AS 安装验证 FROM DUAL;
1.2 Linux环境系统优化配置
bash
复制
下载
#!/bin/bash # dm8_preinstall.sh - 达梦数据库安装前优化脚本 echo "===== 达梦数据库安装前系统优化 =====" # 1. 创建安装用户和组 echo "1. 创建安装用户..." groupadd dinstall useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba echo "dmdba:Dameng@2024" | chpasswd # 2. 配置内核参数 echo "2. 配置内核参数..." cat >> /etc/sysctl.conf << 'EOF' # 达梦数据库优化参数 kernel.shmall = 4294967296 kernel.shmmax = 8589934592 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 6815744 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.wmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_max = 4194304 fs.aio-max-nr = 1048576 vm.swappiness = 10 EOF sysctl -p # 3. 配置资源限制 echo "3. 配置资源限制..." cat >> /etc/security/limits.conf << 'EOF' dmdba soft nproc 65536 dmdba hard nproc 65536 dmdba soft nofile 65536 dmdba hard nofile 65536 dmdba soft stack 10240 dmdba hard stack 10240 EOF # 4. 创建安装目录并授权 echo "4. 创建安装目录..." mkdir -p /opt/dm8 chown -R dmdba:dinstall /opt/dm8 chmod -R 755 /opt/dm8 echo "===== 系统优化完成 ====="
1.3 常见安装问题解决方案
| 问题现象 | 原因分析 | 解决方案 |
|---|---|---|
| 图形界面无法启动 | DISPLAY变量未设置 | export DISPLAY=:0.0 |
| 安装过程中断 | 磁盘空间不足 | 清理空间或选择其他分区 |
| 服务启动失败 | 端口被占用 | 修改端口或停止占用程序 |
| 连接超时 | 防火墙未放行端口 | firewall-cmd --add-port=5236/tcp |
⚙️ 第二部分：配置管理篇
2.1 数据库实例参数优化
sql
复制
下载
-- 1. 查看当前参数配置 SELECT * FROM V$PARAMETER WHERE NAME IN ('MEMORY_POOL', 'BUFFER', 'MAX_SESSIONS') ORDER BY NAME; -- 2. 内存参数优化（8GB内存服务器示例） SP_SET_PARA_VALUE(1, 'MEMORY_POOL', 500); -- 内存池500MB SP_SET_PARA_VALUE(1, 'BUFFER', 2048); -- 缓冲区2GB SP_SET_PARA_VALUE(1, 'MAX_BUFFER', 4096); -- 最大缓冲区4GB SP_SET_PARA_VALUE(1, 'SORT_BUF_SIZE', 50); -- 排序缓冲区50MB SP_SET_PARA_VALUE(1, 'HJ_BUF_SIZE', 100); -- 哈希连接缓冲区100MB -- 3. 会话参数优化 SP_SET_PARA_VALUE(2, 'MAX_SESSIONS', 3000); -- 最大会话数 SP_SET_PARA_VALUE(2, 'MAX_SESSION_STATEMENT', 50000); -- 最大SQL数 -- 4. 事务参数 SP_SET_PARA_VALUE(2, 'UNDO_RETENTION', 900); -- 回滚段保留900秒 SP_SET_PARA_VALUE(1, 'RLOG_PARALLEL_ENABLE', 1); -- 启用并行重做日志 -- 5. 查询优化参数 SP_SET_PARA_VALUE(2, 'OPTIMIZER_MODE', 1); -- 优化器模式 SP_SET_PARA_VALUE(1, 'ENABLE_FREQ_ROOTS', 1); -- 启用频繁根2.2 表空间管理与优化
sql
复制
下载
-- 1. 创建业务表空间 CREATE TABLESPACE TS_BUSINESS DATAFILE 'TS_BUSINESS01.DBF' SIZE 1024 AUTOEXTEND ON NEXT 100 MAXSIZE UNLIMITED STORAGE( INITIAL 64, NEXT 64, MINEXTENTS 1, MAXEXTENTS UNLIMITED, BUFFERPOOL KEEP ); -- 2. 创建索引表空间 CREATE TABLESPACE TS_INDEX DATAFILE 'TS_INDEX01.DBF' SIZE 512 AUTOEXTEND ON NEXT 50 MAXSIZE 2048; -- 3. 创建临时表空间 CREATE TEMPORARY TABLESPACE TS_TEMP TEMPFILE 'TS_TEMP01.DBF' SIZE 512 AUTOEXTEND ON NEXT 50 MAXSIZE 1024; -- 4. 表空间监控SQL SELECT A.TABLESPACE_NAME 表空间名, A.TOTAL_SPACE_MB 总大小_MB, A.FREE_SPACE_MB 空闲_MB, ROUND((A.TOTAL_SPACE_MB - A.FREE_SPACE_MB) / A.TOTAL_SPACE_MB * 100, 2) 使用率百分比, CASE WHEN ROUND((A.TOTAL_SPACE_MB - A.FREE_SPACE_MB) / A.TOTAL_SPACE_MB * 100, 2) > 90 THEN '⚠️紧急' WHEN ROUND((A.TOTAL_SPACE_MB - A.FREE_SPACE_MB) / A.TOTAL_SPACE_MB * 100, 2) > 80 THEN '⚠️警告' ELSE '正常' END 状态 FROM ( SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024) TOTAL_SPACE_MB, ROUND(SUM(MAXBYTES) / 1024 / 1024) MAX_SPACE_MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) A LEFT JOIN ( SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024) FREE_SPACE_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) B ON A.TABLESPACE_NAME = B.TABLESPACE_NAME ORDER BY 使用率百分比 DESC;
2.3 用户权限与安全管理
sql
复制
下载
-- 1. 创建业务用户（最小权限原则） CREATE USER APP_USER IDENTIFIED BY "App@Secure123" DEFAULT TABLESPACE TS_BUSINESS TEMPORARY TABLESPACE TS_TEMP QUOTA UNLIMITED ON TS_BUSINESS QUOTA 100M ON TS_INDEX; -- 2. 角色管理 CREATE ROLE ROLE_DATA_OPERATOR; GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.SALES TO ROLE_DATA_OPERATOR; GRANT ROLE_DATA_OPERATOR TO APP_USER; CREATE ROLE ROLE_DATA_ANALYST; GRANT SELECT ON SCHEMA.SALES TO ROLE_DATA_ANALYST; GRANT EXECUTE ON PKG_REPORT TO ROLE_DATA_ANALYST; -- 3. 细粒度权限控制 -- 列级权限控制 GRANT SELECT(EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT) ON HR.EMPLOYEES TO APP_USER; -- 行级权限控制（使用视图） CREATE VIEW VW_MY_DEPT_EMPLOYEES AS SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT = USERENV('DEPARTMENT'); GRANT SELECT ON VW_MY_DEPT_EMPLOYEES TO APP_USER; -- 4. 密码策略加强 -- 修改系统密码策略 SP_SET_PARA_VALUE(2, 'PWD_POLICY', 3); -- 3: 强密码策略 SP_SET_PARA_VALUE(2, 'PWD_MIN_LEN', 10); -- 最小长度10 SP_SET_PARA_VALUE(2, 'PWD_MAX_LEN', 30); -- 最大长度30 SP_SET_PARA_VALUE(2, 'PWD_LIFE_TIME', 90); -- 90天过期 -- 5. 审计功能配置 -- 启用数据库审计 SP_SET_ENABLE_AUDIT(1); -- 审计关键操作 CREATE AUDIT POLICY AUDIT_DDL ACTIONS CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE USER, DROP USER, ALTER USER; CREATE AUDIT POLICY AUDIT_DML ACTIONS INSERT, UPDATE, DELETE ON SCHEMA.SENSITIVE_DATA; ALTER AUDIT POLICY AUDIT_DDL ENABLE; ALTER AUDIT POLICY AUDIT_DML ENABLE;💾 第三部分：备份恢复篇
3.1 企业级备份策略设计
sql
复制
下载
-- 备份策略：全量+增量+归档三级备份 -- 1. 全量备份（每周日0点） BACKUP DATABASE FULL TO WEEKLY_FULL BACKUPSET '/dm8/backup/full/weekly_full_%Y%m%d' COMPRESSED LEVEL 5 PARALLEL 4 TASK THREAD 4 MAXPIECESIZE 2048 IDENTIFIED BY "BackupKey@2024"; -- 2. 累积增量备份（每天2点） BACKUP DATABASE INCREMENT WITH BACKUPDIR '/dm8/backup/full' TO DAILY_INC BACKUPSET '/dm8/backup/inc/daily_inc_%Y%m%d' COMPRESSED LEVEL 3 IDENTIFIED BY "BackupKey@2024"; -- 3. 归档日志备份（每小时） BACKUP ARCHIVE LOG ALL TO ARCH_LOG BACKUPSET '/dm8/backup/arch/arch_%Y%m%d_%H%M%S' DELETE INPUT IDENTIFIED BY "BackupKey@2024"; -- 4. 备份验证 SELECT BACKUP_NAME, BACKUP_TYPE, START_TIME, END_TIME, ELAPSED_SECONDS, BYTES/1024/1024 AS SIZE_MB, CASE WHEN IS_VALID = 1 THEN '✅有效' ELSE '❌无效' END AS 有效性 FROM V$BACKUPSET WHERE START_TIME > SYSDATE - 7 ORDER BY START_TIME DESC;
3.2 自动化备份脚本
bash
复制
下载
#!/bin/bash # auto_backup_dm8.sh - 达梦数据库自动备份脚本 # 配置参数 DB_USER="SYSDBA" DB_PASS="Dameng123" DB_HOST="localhost" DB_PORT="5236" BACKUP_BASE="/dm8/backup" LOG_FILE="${BACKUP_BASE}/logs/backup_$(date +%Y%m%d).log" RETENTION_DAYS=30 # 创建目录 mkdir -p ${BACKUP_BASE}/{full,inc,arch,logs} mkdir -p ${BACKUP_BASE}/scripts # 日志函数 log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a ${LOG_FILE} } # 备份函数 backup_full() { log "开始全量备份..." BACKUP_FILE="full_$(date +%Y%m%d_%H%M%S)" disql ${DB_USER}/${DB_PASS}@${DB_HOST}:${DB_PORT} <<EOF BACKUP DATABASE FULL TO ${BACKUP_FILE} BACKUPSET '${BACKUP_BASE}/full/${BACKUP_FILE}' COMPRESSED LEVEL 5 PARALLEL 4; exit; EOF if [ $? -eq 0 ]; then log "✅ 全量备份成功: ${BACKUP_FILE}" return 0 else log "❌ 全量备份失败" return 1 fi } backup_increment() { log "开始增量备份..." BACKUP_FILE="inc_$(date +%Y%m%d_%H%M%S)" LATEST_FULL=$(ls -t ${BACKUP_BASE}/full/full_* | head -1) disql ${DB_USER}/${DB_PASS}@${DB_HOST}:${DB_PORT} <<EOF BACKUP DATABASE INCREMENT WITH BACKUPDIR '${LATEST_FULL}' TO ${BACKUP_FILE} BACKUPSET '${BACKUP_BASE}/inc/${BACKUP_FILE}' COMPRESSED LEVEL 3; exit; EOF if [ $? -eq 0 ]; then log "✅ 增量备份成功: ${BACKUP_FILE}" return 0 else log "❌ 增量备份失败" return 1 fi } # 清理旧备份 clean_old_backups() { log "清理${RETENTION_DAYS}天前的备份..." find ${BACKUP_BASE}/full -name "full_*" -mtime +${RETENTION_DAYS} -delete find ${BACKUP_BASE}/inc -name "inc_*" -mtime +${RETENTION_DAYS} -delete find ${BACKUP_BASE}/arch -name "arch_*" -mtime +${RETENTION_DAYS} -delete log "旧备份清理完成" } # 备份检查 check_backup() { log "检查备份文件完整性..." LATEST_BACKUP=$(ls -t ${BACKUP_BASE}/full/full_* 2>/dev/null | head -1) if [ -n "${LATEST_BACKUP}" ]; then disql ${DB_USER}/${DB_PASS}@${DB_HOST}:${DB_PORT} <<EOF SELECT SF_BAKECHECK('${LATEST_BACKUP}'); exit; EOF if [ $? -eq 0 ]; then log "✅ 备份文件检查通过" else log "❌ 备份文件损坏" fi else log "⚠️ 未找到备份文件" fi } # 主逻辑 main() { log "===== 达梦数据库备份开始 =====" # 判断备份类型（周日全量，其他增量） DAY_OF_WEEK=$(date +%u) # 1=周一, 7=周日 if [ ${DAY_OF_WEEK} -eq 7 ]; then backup_full else backup_increment fi # 检查备份 check_backup # 清理旧备份 clean_old_backups log "===== 备份流程完成 =====" } # 执行主函数 main3.3 数据恢复实战
sql
复制
下载
-- 场景模拟：误删除数据恢复 -- 步骤1：查看当前时间 SELECT SYSDATE FROM DUAL; -- 假设返回：2024-10-25 14:30:00 -- 步骤2：模拟误操作 CREATE TABLE test_data AS SELECT * FROM dba_objects; INSERT INTO test_data SELECT * FROM test_data; -- 插入测试数据 COMMIT; SELECT COUNT(*) FROM test_data; -- 记录数据量 -- 误删除操作 DELETE FROM test_data WHERE ROWNUM < 10000; COMMIT; SELECT COUNT(*) FROM test_data; -- 确认数据减少 -- 步骤3：基于时间点的恢复 -- 3.1 关闭数据库（在命令行执行） -- dmserver /dm8/data/DAMENG/dm.ini -- 3.2 执行恢复（在dmrman中执行） /* RMAN> RESTORE DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/backup/full/full_20241025'; RMAN> RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/backup/full/full_20241025' UNTIL TIME '2024-10-25 14:30:00'; RMAN> RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' UPDATE DB_MAGIC; */ -- 步骤4：验证恢复结果 STARTUP; SELECT COUNT(*) FROM test_data; -- 应该恢复删除的数据 -- 步骤5：闪回查询（不需要恢复整个数据库） -- 启用闪回功能 ALTER SYSTEM SET 'UNDO_RETENTION' = 1800; -- 闪回查询误删除前的数据 SELECT COUNT(*) FROM test_data AS OF TIMESTAMP TO_TIMESTAMP('2024-10-25 14:29:00', 'YYYY-MM-DD HH24:MI:SS'); -- 从闪回查询恢复数据 INSERT INTO test_data SELECT * FROM test_data AS OF TIMESTAMP TO_TIMESTAMP('2024-10-25 14:29:00', 'YYYY-MM-DD HH24:MI:SS') WHERE object_id NOT IN (SELECT object_id FROM test_data);🔧 第四部分：SQL编程篇
4.1 常用DMSQL函数详解
sql
复制
下载
-- 1. 字符串处理函数 -- 基础函数 SELECT CONCAT('Hello', ' ', 'DM8') AS concat_result, SUBSTR('达梦数据库', 1, 2) AS substr_result, LENGTH('DM8 Database') AS length_result, UPPER('dameng') AS upper_result, LOWER('DATABASE') AS lower_result, REPLACE('Hello World', 'World', 'DM8') AS replace_result, TRIM(' DM8 ') AS trim_result, LPAD('DM8', 10, '*') AS lpad_result, RPAD('DM8', 10, '*') AS rpad_result; -- 正则表达式函数 SELECT REGEXP_SUBSTR('联系电话：010-12345678', '[0-9]{3,4}-[0-9]{7,8}') AS phone, REGEXP_REPLACE('Email: test@example.com', '@.*', '@dameng.com') AS new_email, REGEXP_LIKE('ABC123', '^[A-Z]{3}[0-9]{3}$') AS is_match; -- 2. 数值处理函数 SELECT ROUND(123.4567, 2) AS round_2, -- 123.46 TRUNC(123.4567, 2) AS trunc_2, -- 123.45 CEIL(123.45) AS ceil_result, -- 124 FLOOR(123.45) AS floor_result, -- 123 MOD(10, 3) AS mod_result, -- 1 POWER(2, 10) AS power_result, -- 1024 SQRT(64) AS sqrt_result, -- 8 ABS(-123.45) AS abs_result; -- 123.45 -- 3. 日期时间函数 SELECT SYSDATE AS current_date, SYSTIMESTAMP AS current_timestamp, ADD_DAYS(SYSDATE, 7) AS next_week, ADD_MONTHS(SYSDATE, 1) AS next_month, MONTHS_BETWEEN(SYSDATE, TO_DATE('2024-01-01')) AS months_diff, LAST_DAY(SYSDATE) AS month_end, NEXT_DAY(SYSDATE, '星期一') AS next_monday, EXTRACT(YEAR FROM SYSDATE) AS current_year, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date, TO_DATE('2024-10-25', 'YYYY-MM-DD') AS date_from_string; -- 4. 聚合函数与窗口函数 SELECT department_id, employee_name, salary, -- 窗口函数示例 SUM(salary) OVER(PARTITION BY department_id) AS dept_total, AVG(salary) OVER(PARTITION BY department_id) AS dept_avg, RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS dept_rank, DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS dept_dense_rank, ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num, LAG(salary, 1) OVER(PARTITION BY department_id ORDER BY salary DESC) AS prev_salary, LEAD(salary, 1) OVER(PARTITION BY department_id ORDER BY salary DESC) AS next_salary, FIRST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary DESC) AS top_salary, NTILE(4) OVER(PARTITION BY department_id ORDER BY salary DESC) AS quartile FROM employees; -- 5. 系统函数 SELECT USER AS current_user, CURRENT_SCHEMA() AS current_schema, SYS_CONTEXT('USERENV', 'IP_ADDRESS') AS client_ip, SYS_CONTEXT('USERENV', 'SESSIONID') AS session_id, DBMS_RANDOM.VALUE(1, 100) AS random_number, DBMS_RANDOM.STRING('A', 10) AS random_string, UID AS user_id;4.2 存储过程开发实战
sql
复制
下载
-- 示例：学生成绩管理系统存储过程包 -- 1. 创建包规范 CREATE OR REPLACE PACKAGE pkg_student_grade AS -- 自定义异常 invalid_grade EXCEPTION; student_not_found EXCEPTION; -- 记录类型 TYPE grade_record IS RECORD ( student_id INT, student_name VARCHAR2(50), course_name VARCHAR2(100), grade_score DECIMAL(5,2), grade_level VARCHAR2(10) ); -- 表类型 TYPE grade_table IS TABLE OF grade_record; -- 添加成绩 PROCEDURE add_grade( p_student_id INT, p_course_id INT, p_score DECIMAL(5,2), p_teacher_id INT, OUT p_grade_id INT ); -- 更新成绩 PROCEDURE update_grade( p_grade_id INT, p_new_score DECIMAL(5,2), p_operator_id INT ); -- 查询学生成绩 FUNCTION get_student_grades( p_student_id INT, p_semester VARCHAR2 DEFAULT NULL ) RETURN grade_table PIPELINED; -- 统计课程平均分 PROCEDURE calc_course_avg( p_course_id INT, OUT p_avg_score DECIMAL(5,2), OUT p_max_score DECIMAL(5,2), OUT p_min_score DECIMAL(5,2), OUT p_pass_rate DECIMAL(5,2) ); -- 生成成绩报表 PROCEDURE generate_grade_report( p_semester VARCHAR2, p_department_id INT DEFAULT NULL ); END pkg_student_grade; / -- 2. 创建包体 CREATE OR REPLACE PACKAGE BODY pkg_student_grade AS -- 成绩有效性检查函数 FUNCTION check_grade_valid(p_score DECIMAL) RETURN BOOLEAN IS BEGIN RETURN p_score >= 0 AND p_score <= 100; END; -- 成绩等级计算函数 FUNCTION calc_grade_level(p_score DECIMAL) RETURN VARCHAR2 IS BEGIN RETURN CASE WHEN p_score >= 90 THEN '优秀' WHEN p_score >= 80 THEN '良好' WHEN p_score >= 70 THEN '中等' WHEN p_score >= 60 THEN '及格' ELSE '不及格' END; END; -- 添加成绩过程 PROCEDURE add_grade( p_student_id INT, p_course_id INT, p_score DECIMAL(5,2), p_teacher_id INT, OUT p_grade_id INT ) AS v_student_exists INT; v_course_exists INT; BEGIN -- 参数验证 IF NOT check_grade_valid(p_score) THEN RAISE invalid_grade; END IF; -- 检查学生是否存在 SELECT COUNT(*) INTO v_student_exists FROM students WHERE student_id = p_student_id AND status = '在读'; IF v_student_exists = 0 THEN RAISE student_not_found; END IF; -- 检查课程是否存在 SELECT COUNT(*) INTO v_course_exists FROM courses WHERE course_id = p_course_id AND is_active = 1; IF v_course_exists = 0 THEN RAISE_APPLICATION_ERROR(-20002, '课程不存在或已停用'); END IF; -- 插入成绩记录 INSERT INTO student_grades ( grade_id, student_id, course_id, score, grade_level, teacher_id, create_time, update_time ) VALUES ( SEQ_GRADE_ID.NEXTVAL, p_student_id, p_course_id, p_score, calc_grade_level(p_score), p_teacher_id, SYSDATE, SYSDATE ) RETURNING grade_id INTO p_grade_id; -- 记录操作日志 INSERT INTO operation_logs ( log_id, operator_id, operation_type, target_id, description, ip_address ) VALUES ( SEQ_LOG_ID.NEXTVAL, p_teacher_id, 'ADD_GRADE', p_grade_id, '添加成绩：学生' || p_student_id || '，课程' || p_course_id, SYS_CONTEXT('USERENV', 'IP_ADDRESS') ); COMMIT; DBMS_OUTPUT.PUT_LINE('成绩添加成功，成绩ID：' || p_grade_id); EXCEPTION WHEN invalid_grade THEN RAISE_APPLICATION_ERROR(-20001, '成绩分数无效（0-100）'); WHEN student_not_found THEN RAISE_APPLICATION_ERROR(-20003, '学生不存在或已毕业'); WHEN OTHERS THEN ROLLBACK; RAISE; END add_grade; -- 查询学生成绩函数 FUNCTION get_student_grades( p_student_id INT, p_semester VARCHAR2 DEFAULT NULL ) RETURN grade_table PIPELINED IS CURSOR c_grades IS SELECT sg.student_id, s.student_name, c.course_name, sg.score, sg.grade_level FROM student_grades sg JOIN students s ON sg.student_id = s.student_id JOIN courses c ON sg.course_id = c.course_id WHERE sg.student_id = p_student_id AND (p_semester IS NULL OR c.semester = p_semester) ORDER BY c.course_id; v_record grade_record; BEGIN OPEN c_grades; LOOP FETCH c_grades INTO v_record.student_id, v_record.student_name, v_record.course_name, v_record.grade_score, v_record.grade_level; EXIT WHEN c_grades%NOTFOUND; PIPE ROW(v_record); END LOOP; CLOSE c_grades; RETURN; END get_student_grades; -- 其他过程实现... END pkg_student_grade; / -- 3. 使用示例 DECLARE v_grade_id INT; v_record pkg_student_grade.grade_record; BEGIN -- 添加成绩 pkg_student_grade.add_grade(1001, 2001, 85.5, 3001, v_grade_id); -- 查询成绩 DBMS_OUTPUT.PUT_LINE('学生成绩列表：'); DBMS_OUTPUT.PUT_LINE('学号 姓名 课程名称 成绩 等级'); DBMS_OUTPUT.PUT_LINE('-------------------------------------------'); FOR rec IN (SELECT * FROM TABLE(pkg_student_grade.get_student_grades(1001))) LOOP DBMS_OUTPUT.PUT_LINE( RPAD(rec.student_id, 7) || RPAD(rec.student_name, 10) || RPAD(rec.course_name, 12) || RPAD(TO_CHAR(rec.grade_score), 7) || rec.grade_level ); END LOOP; END; /4.3 触发器应用实例
sql
复制
下载
-- 1. 审计触发器（记录数据变更） CREATE OR REPLACE TRIGGER trg_audit_student_changes BEFORE INSERT OR UPDATE OR DELETE ON students FOR EACH ROW DECLARE v_operation VARCHAR2(10); v_user_name VARCHAR2(50); BEGIN -- 判断操作类型 IF INSERTING THEN v_operation := 'INSERT'; ELSIF UPDATING THEN v_operation := 'UPDATE'; ELSE v_operation := 'DELETE'; END IF; -- 获取当前用户 v_user_name := USER; -- 插入审计记录 INSERT INTO student_audit_log ( audit_id, student_id, operation_type, old_data, new_data, operator, operation_time, ip_address ) VALUES ( SEQ_AUDIT_ID.NEXTVAL, NVL(:OLD.student_id, :NEW.student_id), v_operation, CASE WHEN v_operation IN ('UPDATE', 'DELETE') THEN JSON_OBJECT( 'student_name' VALUE :OLD.student_name, 'gender' VALUE :OLD.gender, 'birth_date' VALUE TO_CHAR(:OLD.birth_date, 'YYYY-MM-DD'), 'status' VALUE :OLD.status ) ELSE NULL END, CASE WHEN v_operation IN ('INSERT', 'UPDATE') THEN JSON_OBJECT( 'student_name' VALUE :NEW.student_name, 'gender' VALUE :NEW.gender, 'birth_date' VALUE TO_CHAR(:NEW.birth_date, 'YYYY-MM-DD'), 'status' VALUE :NEW.status ) ELSE NULL END, v_user_name, SYSTIMESTAMP, SYS_CONTEXT('USERENV', 'IP_ADDRESS') ); END; / -- 2. 数据完整性触发器 CREATE OR REPLACE TRIGGER trg_check_grade_score BEFORE INSERT OR UPDATE ON student_grades FOR EACH ROW BEGIN -- 检查成绩范围 IF :NEW.score < 0 OR :NEW.score > 100 THEN RAISE_APPLICATION_ERROR(-20010, '成绩必须在0-100之间'); END IF; -- 自动计算成绩等级 :NEW.grade_level := CASE WHEN :NEW.score >= 90 THEN '优秀' WHEN :NEW.score >= 80 THEN '良好' WHEN :NEW.score >= 70 THEN '中等' WHEN :NEW.score >= 60 THEN '及格' ELSE '不及格' END; -- 更新时间戳 :NEW.update_time := SYSDATE; -- 如果是新增记录，设置创建时间 IF INSERTING THEN :NEW.create_time := SYSDATE; END IF; END; / -- 3. 复杂业务触发器（限制选课人数） CREATE OR REPLACE TRIGGER trg_limit_course_selection BEFORE INSERT ON course_selections FOR EACH ROW DECLARE v_current_count INT; v_max_students INT; BEGIN -- 获取当前选课人数和最大限制 SELECT COUNT(*), max_students INTO v_current_count, v_max_students FROM courses c LEFT JOIN course_selections cs ON c.course_id = cs.course_id WHERE c.course_id = :NEW.course_id GROUP BY c.course_id, c.max_students; -- 检查是否超过限制 IF v_current_count >= v_max_students THEN RAISE_APPLICATION_ERROR(-20011, '课程选课人数已满（当前：' || v_current_count || '，最大：' || v_max_students || '）'); END IF; -- 检查学生是否已经选过该课程 IF EXISTS ( SELECT 1 FROM course_selections WHERE student_id = :NEW.student_id AND course_id = :NEW.course_id ) THEN RAISE_APPLICATION_ERROR(-20012, '该学生已经选择了此课程'); END IF; -- 设置选课时间 :NEW.selection_time := SYSDATE; END; /🚀 第五部分：性能调优篇
5.1 SQL执行计划分析
sql
复制
下载
-- 1. 获取SQL执行计划 -- 方法1：使用EXPLAIN命令 EXPLAIN PLAN FOR SELECT s.student_name, c.course_name, g.score FROM students s JOIN student_grades g ON s.student_id = g.student_id JOIN courses c ON g.course_id = c.course_id WHERE s.department_id = 100 AND g.semester = '2024-2025-1' AND g.score >= 60 ORDER BY g.score DESC; -- 查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); -- 方法2：使用AUTOTRACE SET AUTOTRACE ON; SELECT /*+ INDEX(s idx_student_dept) */ s.student_name, c.course_name, g.score FROM students s JOIN student_grades g ON s.student_id = g.student_id JOIN courses c ON g.course_id = c.course_id WHERE s.department_id = 100 AND g.semester = '2024-2025-1' ORDER BY g.score DESC; SET AUTOTRACE OFF; -- 2. 分析执行计划要点 /* 执行计划关键指标： 1. COST：执行成本，值越小越好 2. CARDINALITY：预估行数 3. BYTES：预估字节数 4. TIME：预估时间 5. 访问路径：全表扫描（TABLE SCAN） vs 索引扫描（INDEX SCAN） 6. 连接方式：NESTED LOOP、HASH JOIN、MERGE JOIN */ -- 3. 执行计划解读示例 /* OPERATION | OBJECT_NAME | COST | CARDINALITY | BYTES ------------------------------|-------------------|------|-------------|------- SELECT STATEMENT | | 2345 | 1000 | 85000 SORT ORDER BY | | 2345 | 1000 | 85000 HASH JOIN | | 1234 | 1000 | 85000 TABLE ACCESS FULL | STUDENTS | 500 | 5000 | 250000 HASH JOIN | | 734 | 10000 | 600000 TABLE ACCESS FULL | STUDENT_GRADES | 400 | 100000 | 3000000 INDEX RANGE SCAN | IDX_COURSE_ID | 334 | 1000 | 15000 优化建议： 1. STUDENTS表全表扫描 -> 创建department_id索引 2. STUDENT_GRADES表全表扫描 -> 创建复合索引(semester, score) */ -- 4. 创建优化索引 -- 针对上面的查询创建索引 CREATE INDEX idx_students_dept ON students(department_id); CREATE INDEX idx_grades_semester_score ON student_grades(semester, score DESC); CREATE INDEX idx_courses_name ON courses(course_name); -- 5. 重新分析执行计划 EXPLAIN PLAN FOR SELECT s.student_name, c.course_name, g.score FROM students s JOIN student_grades g ON s.student_id = g.student_id JOIN courses c ON g.course_id = c.course_id WHERE s.department_id = 100 AND g.semester = '2024-2025-1' AND g.score >= 60 ORDER BY g.score DESC; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
5.2 索引优化策略
sql
复制
下载
-- 1. 查看现有索引 SELECT table_name, index_name, index_type, uniqueness, status, tablespace_name FROM user_indexes WHERE table_name IN ('STUDENTS', 'STUDENT_GRADES', 'COURSES') ORDER BY table_name, index_name; -- 2. 查看索引列信息 SELECT i.table_name, i.index_name, ic.column_name, ic.column_position FROM user_indexes i JOIN user_ind_columns ic ON i.index_name = ic.index_name WHERE i.table_name = 'STUDENT_GRADES' ORDER BY i.index_name, ic.column_position; -- 3. 创建不同类型索引 -- B树索引（默认） CREATE INDEX idx_students_name ON students(student_name); -- 唯一索引 CREATE UNIQUE INDEX idx_students_id_card ON students(id_card); -- 复合索引 CREATE INDEX idx_grades_student_course ON student_grades(student_id, course_id); -- 函数索引 CREATE INDEX idx_students_upper_name ON students(UPPER(student_name)); -- 位图索引（适合低基数列） CREATE BITMAP INDEX idx_students_gender ON students(gender); -- 4. 索引使用情况分析 -- 查看索引使用统计 SELECT index_name, table_name, num_rows, distinct_keys, clustering_factor, blevel, leaf_blocks, avg_leaf_blocks_per_key, avg_data_blocks_per_key FROM user_indexes WHERE table_name = 'STUDENT_GRADES'; -- 5. 识别未使用索引 -- 需要开启索引监控 ALTER INDEX idx_students_name MONITORING USAGE; ALTER INDEX idx_students_name NOMONITORING USAGE; -- 查看监控结果 SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring FROM v$object_usage WHERE index_name = 'IDX_STUDENTS_NAME'; -- 6. 索引维护 -- 重建索引（碎片整理） ALTER INDEX idx_students_name REBUILD ONLINE; -- 合并索引 ALTER INDEX idx_students_name COALESCE; -- 修改索引存储参数 ALTER INDEX idx_students_name STORAGE (NEXT 1M MAXEXTENTS UNLIMITED); -- 7. 索引优化建议 /* 索引设计原则： 1. WHERE子句中的列 2. JOIN连接条件的列 3. ORDER BY/GROUP BY中的列 4. 选择性高的列（distinct值多） 5. 避免过多索引，影响DML性能 6. 复合索引列顺序：等值查询列在前，范围查询列在后 示例： -- 好：等值查询列在前 CREATE INDEX idx_grades_semester_score ON student_grades(semester, score); -- 不好：范围查询列在前 CREATE INDEX idx_grades_score_semester ON student_grades(score, semester); */ -- 8. 使用索引提示（Hints） SELECT /*+ INDEX(g idx_grades_semester_score) */ s.student_name, g.score FROM students s JOIN student_grades g ON s.student_id = g.student_id WHERE g.semester = '2024-2025-1' AND g.score > 90; -- 强制全表扫描 SELECT /*+ FULL(s) */ s.student_name, s.gender FROM students s WHERE s.department_id = 100; -- 强制使用特定索引 SELECT /*+ INDEX(s idx_students_dept) */ s.student_name, s.gender FROM students s WHERE s.department_id = 100;5.3 性能监控与调优
sql
复制
下载
-- 1. 系统级性能监控 -- 查看数据库整体状态 SELECT '数据库状态' AS 指标, (SELECT STATUS FROM V$INSTANCE) AS 值 FROM DUAL UNION ALL SELECT '启动时间', TO_CHAR(STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS') FROM V$INSTANCE UNION ALL SELECT '版本信息', BANNER FROM V$VERSION WHERE ROWNUM = 1 UNION ALL SELECT '会话数', TO_CHAR(COUNT(*)) FROM V$SESSION UNION ALL SELECT '活跃会话数', TO_CHAR(COUNT(*)) FROM V$SESSION WHERE STATUS = 'ACTIVE'; -- 2. 会话级监控 SELECT sess_id, sql_text, state, login_time, last_recv_time, client_ip, program, elapsed_time/1000000 as elapsed_sec, cpu_time/1000000 as cpu_sec FROM v$sessions WHERE state != 'IDLE' AND elapsed_time > 1000000 -- 超过1秒 ORDER BY elapsed_time DESC LIMIT 20; -- 3. SQL性能分析 SELECT sql_id, sql_text, executions, elapsed_time/1000000 as total_elapsed_sec, cpu_time/1000000 as total_cpu_sec, disk_reads, buffer_gets, rows_processed, parse_calls, first_load_time, last_load_time, ROUND(elapsed_time/NULLIF(executions, 0)/1000000, 4) as avg_elapsed_sec, ROUND(cpu_time/NULLIF(executions, 0)/1000000, 4) as avg_cpu_sec, ROUND(disk_reads/NULLIF(executions, 0), 2) as avg_disk_reads, ROUND(buffer_gets/NULLIF(executions, 0), 2) as avg_buffer_gets FROM v$sqlarea WHERE executions > 0 AND elapsed_time > 1000000 -- 总耗时超过1秒 AND last_load_time > SYSDATE - 1 -- 最近1天执行过 ORDER BY elapsed_time DESC LIMIT 20; -- 4. 等待事件分析 SELECT event, total_waits, total_timeouts, time_waited/100 as time_waited_sec, average_wait/100 as avg_wait_sec, max_wait/100 as max_wait_sec, time_waited_micro FROM v$system_event WHERE wait_class != 'Idle' AND total_waits > 0 ORDER BY time_waited DESC LIMIT 20; -- 5. 锁监控 SELECT lh.trx_id, s1.sess_id as blocking_session, s1.sql_text as blocking_sql, s2.sess_id as waiting_session, s2.sql_text as waiting_sql, lh.table_name, lh.lock_mode, lh.lock_wait_start_time, TIMESTAMPDIFF(SECOND, lh.lock_wait_start_time, SYSDATE) as wait_seconds FROM v$lock_held lh LEFT JOIN v$sessions s1 ON lh.blocking_sess_id = s1.sess_id LEFT JOIN v$sessions s2 ON lh.sess_id = s2.sess_id WHERE lh.lock_wait = 1 ORDER BY wait_seconds DESC; -- 6. 表空间和文件I/O监控 SELECT df.tablespace_name, df.file_name, df.bytes/1024/1024 as total_mb, (df.bytes - NVL(fs.bytes, 0))/1024/1024 as used_mb, NVL(fs.bytes, 0)/1024/1024 as free_mb, ROUND((df.bytes - NVL(fs.bytes, 0))/df.bytes*100, 2) as usage_percent, df.autoextensible, df.increment_by*8192/1024/1024 as increment_mb, df.maxbytes/1024/1024 as max_mb FROM dba_data_files df LEFT JOIN ( SELECT file_id, SUM(bytes) as bytes FROM dba_free_space GROUP BY file_id ) fs ON df.file_id = fs.file_id ORDER BY usage_percent DESC; -- 7. 缓冲池命中率 SELECT '缓冲池命中率' as 指标, ROUND((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100, 2) as 值 FROM v$buffer_pool_statistics UNION ALL SELECT '库缓存命中率', ROUND((1 - (reloads / pins)) * 100, 2) FROM v$librarycache WHERE namespace = 'SQL AREA' UNION ALL SELECT '字典缓存命中率', ROUND((1 - (getmisses / gets)) * 100, 2) FROM v$rowcache; -- 8. 自动生成性能报告 CREATE OR REPLACE PROCEDURE generate_performance_report AS v_report CLOB; BEGIN v_report := '===== 数据库性能报告 =====' || CHR(10) || CHR(10); -- 系统状态 FOR rec IN ( SELECT '系统状态' as section, name as item, value FROM v$parameter WHERE name IN ('sessions', 'processes', 'memory_target') UNION ALL SELECT '会话统计', '总会话数', COUNT(*) FROM v$session UNION ALL SELECT '会话统计', '活跃会话数', COUNT(*) FROM v$session WHERE status = 'ACTIVE' ) LOOP v_report := v_report || rec.section || ' - ' || rec.item || ': ' || rec.value || CHR(10); END LOOP; v_report := v_report || CHR(10) || '===== Top 10 慢SQL =====' || CHR(10); -- 慢SQL FOR rec IN ( SELECT sql_id, ROUND(elapsed_time/1000000, 2) as elapsed_sec FROM v$sqlarea WHERE elapsed_time > 1000000 ORDER BY elapsed_time DESC LIMIT 10 ) LOOP v_report := v_report || 'SQL_ID: ' || rec.sql_id || ', 耗时: ' || rec.elapsed_sec || '秒' || CHR(10); END LOOP; -- 保存报告 INSERT INTO performance_reports(report_id, report_time, report_content) VALUES (SEQ_REPORT.NEXTVAL, SYSTIMESTAMP, v_report); COMMIT; DBMS_OUTPUT.PUT_LINE('性能报告生成完成'); END; / -- 定时执行性能报告 BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'JOB_PERFORMANCE_REPORT', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN generate_performance_report; END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=HOURLY; INTERVAL=1', enabled => TRUE, comments => '每小时生成性能报告' ); END; /🎯 第六部分：项目实战篇
6.1 学生管理系统数据库设计
sql
复制
下载
-- 1. 创建学生管理系统数据库架构 -- 院系表 CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR2(100) NOT NULL, dean VARCHAR2(50), phone VARCHAR2(20), email VARCHAR2(100), address VARCHAR2(200), create_time TIMESTAMP DEFAULT SYSTIMESTAMP, update_time TIMESTAMP DEFAULT SYSTIMESTAMP ); -- 专业表 CREATE TABLE majors ( major_id INT PRIMARY KEY, major_name VARCHAR2(100) NOT NULL, department_id INT REFERENCES departments(department_id), duration INT DEFAULT 4, -- 学制（年） degree_type VARCHAR2(20), -- 学位类型 description VARCHAR2(500), create_time TIMESTAMP DEFAULT SYSTIMESTAMP ); -- 班级表 CREATE TABLE classes ( class_id INT PRIMARY KEY, class_name VARCHAR2(50) NOT NULL, major_id INT REFERENCES majors(major_id), enrollment_year INT, -- 入学年份 counselor VARCHAR2(50), -- 辅导员 student_count INT DEFAULT 0, create_time TIMESTAMP DEFAULT SYSTIMESTAMP ); -- 学生表 CREATE TABLE students ( student_id INT PRIMARY KEY, student_no VARCHAR2(20) UNIQUE NOT NULL, -- 学号 student_name VARCHAR2(50) NOT NULL, gender CHAR(1) CHECK (gender IN ('M', 'F')), birth_date DATE, id_card VARCHAR2(18) UNIQUE, phone VARCHAR2(20), email VARCHAR2(100), address VARCHAR2(200), class_id INT REFERENCES classes(class_id), enrollment_date DATE, -- 入学日期 status VARCHAR2(20) DEFAULT '在读' CHECK (status IN ('在读', '休学', '退学', '毕业')), photo_url VARCHAR2(200), create_time TIMESTAMP DEFAULT SYSTIMESTAMP, update_time TIMESTAMP DEFAULT SYSTIMESTAMP ); -- 课程表 CREATE TABLE courses ( course_id INT PRIMARY KEY, course_no VARCHAR2(20) UNIQUE NOT NULL, course_name VARCHAR2(100) NOT NULL, credit DECIMAL(3,1) DEFAULT 1.0, -- 学分 course_hours INT, -- 学时 course_type VARCHAR2(20), -- 课程类型：必修、选修 department_id INT REFERENCES departments(department_id), teacher_id INT, semester VARCHAR2(20), -- 学期：2024-2025-1 max_students INT DEFAULT 100, description VARCHAR2(500), is_active CHAR(1) DEFAULT '1' CHECK (is_active IN ('0', '1')), create_time TIMESTAMP DEFAULT SYSTIMESTAMP ); -- 成绩表 CREATE TABLE student_grades ( grade_id INT PRIMARY KEY, student_id INT REFERENCES students(student_id), course_id INT REFERENCES courses(course_id), score DECIMAL(5,2) CHECK (score BETWEEN 0 AND 100), grade_level VARCHAR2(10), -- 成绩等级 semester VARCHAR2(20), exam_date DATE, teacher_id INT, remark VARCHAR2(200), create_time TIMESTAMP DEFAULT SYSTIMESTAMP, update_time TIMESTAMP DEFAULT SYSTIMESTAMP, CONSTRAINT uk_student_course UNIQUE (student_id, course_id, semester) ); -- 教师表 CREATE TABLE teachers ( teacher_id INT PRIMARY KEY, teacher_no VARCHAR2(20) UNIQUE NOT NULL, teacher_name VARCHAR2(50) NOT NULL, gender CHAR(1) CHECK (gender IN ('M', 'F')), title VARCHAR2(20), -- 职称 department_id INT REFERENCES departments(department_id), phone VARCHAR2(20), email VARCHAR2(100), hire_date DATE, status VARCHAR2(20) DEFAULT '在职', create_time TIMESTAMP DEFAULT SYSTIMESTAMP ); -- 选课表 CREATE TABLE course_selections ( selection_id INT PRIMARY KEY, student_id INT REFERENCES students(student_id), course_id INT REFERENCES courses(course_id), selection_time TIMESTAMP DEFAULT SYSTIMESTAMP, status VARCHAR2(20) DEFAULT '已选' CHECK (status IN ('已选', '退选')), CONSTRAINT uk_student_course_selection UNIQUE (student_id, course_id) ); -- 2. 创建序列 CREATE SEQUENCE seq_department_id START WITH 100 INCREMENT BY 1; CREATE SEQUENCE seq_major_id START WITH 1000 INCREMENT BY 1; CREATE SEQUENCE seq_class_id START WITH 10000 INCREMENT BY 1; CREATE SEQUENCE seq_student_id START WITH 20240001 INCREMENT BY 1; CREATE SEQUENCE seq_course_id START WITH 1000 INCREMENT BY 1; CREATE SEQUENCE seq_grade_id START WITH 1 INCREMENT BY 1; CREATE SEQUENCE seq_teacher_id START WITH 1000 INCREMENT BY 1; CREATE SEQUENCE seq_selection_id START WITH 1 INCREMENT BY 1; -- 3. 创建索引 -- 学生表索引 CREATE INDEX idx_students_class ON students(class_id); CREATE INDEX idx_students_status ON students(status); CREATE INDEX idx_students_name ON students(student_name); CREATE INDEX idx_students_no ON students(student_no); -- 成绩表索引 CREATE INDEX idx_grades_student ON student_grades(student_id); CREATE INDEX idx_grades_course ON student_grades(course_id); CREATE INDEX idx_grades_semester ON student_grades(semester); CREATE INDEX idx_grades_score ON student_grades(score DESC); -- 课程表索引 CREATE INDEX idx_courses_dept ON courses(department_id); CREATE INDEX idx_courses_semester ON courses(semester); CREATE INDEX idx_courses_teacher ON courses(teacher_id); -- 4. 创建视图 -- 学生详细信息视图 CREATE OR REPLACE VIEW vw_student_details AS SELECT s.student_id, s.student_no, s.student_name, s.gender, s.birth_date, s.phone, s.email, s.status, c.class_name, m.major_name, d.department_name, s.enrollment_date, TIMESTAMPDIFF(YEAR, s.enrollment_date, SYSDATE) + 1 as grade_year -- 年级 FROM students s JOIN classes c ON s.class_id = c.class_id JOIN majors m ON c.major_id = m.major_id JOIN departments d ON m.department_id = d.department_id; -- 学生成绩统计视图 CREATE OR REPLACE VIEW vw_student_grades_summary AS SELECT s.student_id, s.student_name, c.class_name, COUNT(DISTINCT g.course_id) as course_count, COUNT(g.grade_id) as grade_count, AVG(g.score) as avg_score, SUM(CASE WHEN g.score >= 60 THEN c.credit ELSE 0 END) as passed_credits, SUM(c.credit) as total_credits, ROUND(SUM(CASE WHEN g.score >= 60 THEN c.credit ELSE 0 END) * 100.0 / SUM(c.credit), 2) as pass_rate FROM students s LEFT JOIN student_grades g ON s.student_id = g.student_id LEFT JOIN courses c ON g.course_id = c.course_id JOIN classes cl ON s.class_id = cl.class_id GROUP BY s.student_id, s.student_name, c.class_name; -- 课程统计视图 CREATE OR REPLACE VIEW vw_course_statistics AS SELECT c.course_id, c.course_name, c.credit, c.teacher_id, t.teacher_name, COUNT(DISTINCT cs.student_id) as selected_count, COUNT(g.grade_id) as graded_count, AVG(g.score) as avg_score, MAX(g.score) as max_score, MIN(g.score) as min_score, COUNT(CASE WHEN g.score >= 60 THEN 1 END) as pass_count, COUNT(CASE WHEN g.score < 60 THEN 1 END) as fail_count, ROUND(COUNT(CASE WHEN g.score >= 60 THEN 1 END) * 100.0 / NULLIF(COUNT(g.grade_id), 0), 2) as pass_rate FROM courses c LEFT JOIN course_selections cs ON c.course_id = cs.course_id LEFT JOIN student_grades g ON c.course_id = g.course_id AND cs.student_id = g.student_id LEFT JOIN teachers t ON c.teacher_id = t.teacher_id GROUP BY c.course_id, c.course_name, c.credit, c.teacher_id, t.teacher_name; -- 5. 插入测试数据 BEGIN -- 插入院系 INSERT INTO departments VALUES (seq_department_id.NEXTVAL, '计算机科学与技术学院', '张院长', '010-12345678', 'cs@university.edu', '主楼A座', SYSDATE, SYSDATE); INSERT INTO departments VALUES (seq_department_id.NEXTVAL, '电子信息工程学院', '李院长', '010-87654321', 'ee@university.edu', '主楼B座', SYSDATE, SYSDATE); -- 插入专业 INSERT INTO majors VALUES (seq_major_id.NEXTVAL, '计算机科学与技术', 100, 4, '工学学士', '计算机科学基础专业', SYSDATE); INSERT INTO majors VALUES (seq_major_id.NEXTVAL, '软件工程', 100, 4, '工学学士', '软件设计与开发专业', SYSDATE); INSERT INTO majors VALUES (seq_major_id.NEXTVAL, '电子信息工程', 101, 4, '工学学士', '电子信息处理专业', SYSDATE); -- 插入班级 INSERT INTO classes VALUES (seq_class_id.NEXTVAL, '计科2401班', 1000, 2024, '王辅导员', 0, SYSDATE); INSERT INTO classes VALUES (seq_class_id.NEXTVAL, '软件2401班', 1001, 2024, '赵辅导员', 0, SYSDATE); -- 插入学生（使用序列） FOR i IN 1..50 LOOP INSERT INTO students VALUES ( seq_student_id.NEXTVAL, '2024' || LPAD(i, 4, '0'), CASE MOD(i, 5) WHEN 0 THEN '张' || i WHEN 1 THEN '王' || i WHEN 2 THEN '李' || i WHEN 3 THEN '刘' || i ELSE '陈' || i END, CASE MOD(i, 2) WHEN 0 THEN 'M' ELSE 'F' END, TO_DATE('2000-' || LPAD(MOD(i, 12)+1, 2, '0') || '-' || LPAD(MOD(i, 28)+1, 2, '0'), 'YYYY-MM-DD'), '11010120000101' || LPAD(i, 4, '0'), '13800138' || LPAD(i, 3, '0'), 'student' || i || '@university.edu', '北京市海淀区', CASE WHEN i <= 25 THEN 10000 ELSE 10001 END, DATE '2024-09-01', '在读', NULL, SYSDATE, SYSDATE ); END LOOP; -- 更新班级人数 UPDATE classes c SET student_count = ( SELECT COUNT(*) FROM students s WHERE s.class_id = c.class_id ); COMMIT; END; / -- 6. 业务查询示例 -- 查询某班级所有学生 SELECT student_no as 学号, student_name as 姓名, gender as 性别, TO_CHAR(birth_date, 'YYYY-MM-DD') as 出生日期, phone as 电话 FROM students WHERE class_id = 10000 ORDER BY student_no; -- 查询学生成绩单 SELECT s.student_name as 学生姓名, c.course_name as 课程名称, c.credit as 学分, g.score as 成绩, g.grade_level as 等级, TO_CHAR(g.exam_date, 'YYYY-MM-DD') as 考试日期 FROM students s JOIN student_grades g ON s.student_id = g.student_id JOIN courses c ON g.course_id = c.course_id WHERE s.student_id = 20240001 AND g.semester = '2024-2025-1' ORDER BY c.course_id; -- 统计各班级平均成绩 SELECT cl.class_name as 班级名称, COUNT(DISTINCT s.student_id) as 学生人数, COUNT(g.grade_id) as 成绩记录数, ROUND(AVG(g.score), 2) as 平均成绩, ROUND(SUM(CASE WHEN g.score >= 60 THEN c.credit ELSE 0 END), 1) as 总获得学分 FROM classes cl JOIN students s ON cl.class_id = s.class_id LEFT JOIN student_grades g ON s.student_id = g.student_id LEFT JOIN courses c ON g.course_id = c.course_id WHERE g.semester = '2024-2025-1' GROUP BY cl.class_id, cl.class_name ORDER BY 平均成绩 DESC; -- 7. 复杂业务逻辑：奖学金评定 CREATE OR REPLACE PROCEDURE calculate_scholarship(p_semester VARCHAR2) AS CURSOR c_students IS SELECT s.student_id, s.student_name, cl.class_name, AVG(g.score) as avg_score, SUM(c.credit) as total_credits FROM students s JOIN classes cl ON s.class_id = cl.class_id JOIN student_grades g ON s.student_id = g.student_id JOIN courses c ON g.course_id = c.course_id WHERE g.semester = p_semester AND s.status = '在读' GROUP BY s.student_id, s.student_name, cl.class_name HAVING AVG(g.score) >= 85 AND SUM(c.credit) >= 15 ORDER BY AVG(g.score) DESC; v_scholarship_id INT; v_level VARCHAR2(20); BEGIN -- 清空当期奖学金记录 DELETE FROM scholarships WHERE semester = p_semester; -- 计算奖学金 FOR rec IN c_students LOOP -- 根据平均分确定奖学金等级 IF rec.avg_score >= 95 THEN v_level := '一等奖学金'; ELSIF rec.avg_score >= 90 THEN v_level := '二等奖学金'; ELSIF rec.avg_score >= 85 THEN v_level := '三等奖学金'; ELSE v_level := NULL; END IF; IF v_level IS NOT NULL THEN -- 插入奖学金记录 INSERT INTO scholarships ( scholarship_id, student_id, semester, scholarship_level, amount, award_date ) VALUES ( SEQ_SCHOLARSHIP.NEXTVAL, rec.student_id, p_semester, v_level, CASE v_level WHEN '一等奖学金' THEN 5000 WHEN '二等奖学金' THEN 3000 WHEN '三等奖学金' THEN 1000 END, SYSDATE ); DBMS_OUTPUT.PUT_LINE('授予 ' || rec.student_name || ' ' || v_level); END IF; END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE('奖学金评定完成'); EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END; /6.2 电商平台数据库优化案例
sql
复制
下载
-- 电商平台核心表结构优化案例 -- 原始表结构（存在问题） CREATE TABLE orders_old ( order_id INT PRIMARY KEY, user_id INT, product_id INT, quantity INT, price DECIMAL(10,2), order_time TIMESTAMP, status VARCHAR2(20) ); -- 优化后的表结构 CREATE TABLE orders ( order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_no VARCHAR2(32) UNIQUE NOT NULL, -- 订单号 user_id INT NOT NULL, total_amount DECIMAL(10,2) DEFAULT 0, discount_amount DECIMAL(10,2) DEFAULT 0, pay_amount DECIMAL(10,2) DEFAULT 0, payment_method VARCHAR2(20), payment_time TIMESTAMP, shipping_address VARCHAR2(500), receiver_name VARCHAR2(50), receiver_phone VARCHAR2(20), order_time TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL, pay_time TIMESTAMP, ship_time TIMESTAMP, receive_time TIMESTAMP, order_status VARCHAR2(20) DEFAULT '待付款' CHECK (order_status IN ('待付款', '已付款', '已发货', '已完成', '已取消', '退款中')), is_deleted CHAR(1) DEFAULT '0', create_time TIMESTAMP DEFAULT SYSTIMESTAMP, update_time TIMESTAMP DEFAULT SYSTIMESTAMP ) PARTITION BY RANGE (order_time) ( PARTITION p202401 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')), PARTITION p202402 VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD')), PARTITION p202403 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')), PARTITION p202404 VALUES LESS THAN (TO_DATE('2024-05-01', 'YYYY-MM-DD')), PARTITION p_max VALUES LESS THAN (MAXVALUE) ); -- 订单明细表（拆分子表） CREATE TABLE order_items ( item_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(order_id), product_id INT NOT NULL, product_name VARCHAR2(200) NOT NULL, sku_id VARCHAR2(50), quantity INT DEFAULT 1 CHECK (quantity > 0), unit_price DECIMAL(10,2) NOT NULL, discount_rate DECIMAL(5,4) DEFAULT 1, subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price * discount_rate) VIRTUAL, create_time TIMESTAMP DEFAULT SYSTIMESTAMP ); -- 创建索引策略 -- 1. 订单表索引 CREATE INDEX idx_orders_user ON orders(user_id); CREATE INDEX idx_orders_status ON orders(order_status); CREATE INDEX idx_orders_time ON orders(order_time DESC); CREATE INDEX idx_orders_pay_time ON orders(pay_time DESC); -- 2. 订单明细表索引 CREATE INDEX idx_items_order ON order_items(order_id); CREATE INDEX idx_items_product ON order_items(product_id); CREATE INDEX idx_items_sku ON order_items(sku_id); -- 3. 复合索引 CREATE INDEX idx_orders_user_status ON orders(user_id, order_status); CREATE INDEX idx_orders_time_status ON orders(order_time, order_status); -- 优化查询示例 -- 查询用户最近订单（使用索引） SELECT /*+ INDEX(o idx_orders_user_time) */ o.order_no, o.order_time, o.total_amount, o.order_status, COUNT(i.item_id) as item_count FROM orders o LEFT JOIN order_items i ON o.order_id = i.order_id WHERE o.user_id = 1001 AND o.order_time >= SYSDATE - 30 AND o.is_deleted = '0' GROUP BY o.order_no, o.order_time, o.total_amount, o.order_status ORDER BY o.order_time DESC LIMIT 10; -- 销售统计查询优化 WITH daily_sales AS ( SELECT /*+ INDEX(o idx_orders_time_status) MATERIALIZE */ TRUNC(o.order_time) as sales_date, COUNT(DISTINCT o.order_id) as order_count, COUNT(i.item_id) as item_count, SUM(i.quantity) as total_quantity, SUM(o.pay_amount) as total_sales FROM orders o JOIN order_items i ON o.order_id = i.order_id WHERE o.order_status IN ('已完成', '已发货') AND o.order_time >= DATE '2024-01-01' AND o.order_time < DATE '2024-02-01' GROUP BY TRUNC(o.order_time) ) SELECT sales_date, order_count, item_count, total_quantity, total_sales, ROUND(AVG(total_sales) OVER (ORDER BY sales_date ROWS 6 PRECEDING), 2) as sales_7day_avg FROM daily_sales ORDER BY sales_date; -- 热销商品分析 SELECT /*+ INDEX(i idx_items_product) */ p.product_name, p.category, COUNT(DISTINCT i.order_id) as order_count, SUM(i.quantity) as total_sales, SUM(i.subtotal) as sales_amount, RANK() OVER (ORDER BY SUM(i.quantity) DESC) as sales_rank FROM order_items i JOIN products p ON i.product_id = p.product_id JOIN orders o ON i.order_id = o.order_id WHERE o.order_time >= SYSDATE - 30 AND o.order_status IN ('已完成', '已发货') GROUP BY p.product_id, p.product_name, p.category ORDER BY total_sales DESC LIMIT 20; -- 分区维护 -- 添加新分区 ALTER TABLE orders ADD PARTITION p202405 VALUES LESS THAN (TO_DATE('2024-06-01', 'YYYY-MM-DD')); -- 合并旧分区（归档） ALTER TABLE orders MERGE PARTITIONS p202401, p202402 INTO PARTITION p2024_q1; -- 分区交换（将历史数据迁移到历史表） ALTER TABLE orders EXCHANGE PARTITION p2024_q1 WITH TABLE orders_history; -- 性能监控 EXPLAIN PLAN FOR SELECT TO_CHAR(o.order_time, 'YYYY-MM') as month, p.category, SUM(i.quantity) as total_quantity, SUM(o.pay_amount) as total_amount FROM orders o JOIN order_items i ON o.order_id = i.order_id JOIN products p ON i.product_id = p.product_id WHERE o.order_time >= DATE '2024-01-01' AND o.order_status = '已完成' GROUP BY TO_CHAR(o.order_time, 'YYYY-MM'), p.category ORDER BY month, total_amount DESC; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());📝 第七部分：学习总结与建议
学习收获总结
通过《国产数据库技术》课程的系统学习，我在以下方面获得显著提升：
技术体系全面掌握
从DM8安装部署到高级调优的完整技术栈
深入理解国产数据库架构设计和实现原理
掌握企业级数据库运维管理技能
实战能力显著提升
完成多个实际项目案例设计
解决复杂性能优化问题
设计高可用数据库架构
问题解决能力增强
系统化故障排查思路
性能瓶颈分析方法
数据安全保护策略
课程学习建议
针对后续学习该课程的同学，建议如下：
学习路径建议
text
复制 下载第一阶段：基础入门（2周） ├── DM8安装与配置 ├── SQL基础语法 ├── 数据库对象管理 └── 简单查询编写 第二阶段：进阶提升（3周） ├── 存储过程开发 ├── 触发器应用 ├── 复杂查询优化 └── 事务与并发控制 第三阶段：高级应用（3周） ├── 性能调优 ├── 备份恢复策略 ├── 高可用架构 └── 安全管理 第四阶段：项目实战（2周） ├── 完整项目设计 ├── 问题排查演练 └── 性能优化实践
实践建议
每学完一个知识点，立即动手实践
记录遇到的问题和解决方案
参与开源社区或技术论坛讨论
尝试在实际项目中应用所学知识
资源推荐
官方文档：达梦技术手册
在线课程：CSDN学院相关课程
实践环境：搭建个人实验环境
社区交流：达梦技术社区、CSDN博客
职业发展规划
掌握国产数据库技术为职业发展带来新机遇：
技术方向
数据库管理员（DBA）
数据库开发工程师
数据架构师
数据库性能优化专家
行业机会
政府信息化项目
金融行业国产化改造
大型企业数字化转型
云计算与大数据平台
持续学习
考取达梦DCA/DCP认证
学习其他国产数据库（OceanBase、GaussDB）
关注数据库前沿技术（AI for DB、云原生数据库）
参与开源项目贡献
致谢
感谢《国产数据库技术》课程的老师们悉心教导，让我系统掌握了达梦数据库技术。同时也感谢CSDN平台提供的学习资源和实践机会，通过撰写技术博客，不仅巩固了所学知识，也锻炼了技术写作能力。
特别提示：在学习过程中，要遵守学校规定，不在网络上发布课程内部资料和带有学校标识的资源，保护知识产权和学校信息安全。
