中资源的 域名管理网站没网站可以做seo吗

中资源的 域名管理网站,没网站可以做seo吗,我要装修网,外贸网站关键词#x1f4da; 前言#xff1a;为什么选择学习国产数据库#xff1f; 在当今“数字中国”建设和信创产业发展的大背景下#xff0c;国产数据库技术正迎来黄金发展期。作为一名计算机应用技术专业的学生#xff0c;我深刻认识到掌握国产数据库技术不仅是学术要求#xff0… 前言为什么选择学习国产数据库在当今“数字中国”建设和信创产业发展的大背景下国产数据库技术正迎来黄金发展期。作为一名计算机应用技术专业的学生我深刻认识到掌握国产数据库技术不仅是学术要求更是未来职业发展的核心竞争力。在《国产数据库技术》课程的学习中我系统学习了达梦数据库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:Dameng2024 | 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-port5236/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 AppSecure123 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 BackupKey2024; -- 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 BackupKey2024; -- 3. 归档日志备份每小时 BACKUP ARCHIVE LOG ALL TO ARCH_LOG BACKUPSET /dm8/backup/arch/arch_%Y%m%d_%H%M%S DELETE INPUT IDENTIFIED BY BackupKey2024; -- 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_USERSYSDBA DB_PASSDameng123 DB_HOSTlocalhost DB_PORT5236 BACKUP_BASE/dm8/backup LOG_FILE${BACKUP_BASE}/logs/backup_$(date %Y%m%d).log RETENTION_DAYS30 # 创建目录 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_FILEfull_$(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_FILEinc_$(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: testexample.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 FREQHOURLY; INTERVAL1, 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, csuniversity.edu, 主楼A座, SYSDATE, SYSDATE); INSERT INTO departments VALUES (seq_department_id.NEXTVAL, 电子信息工程学院, 李院长, 010-87654321, eeuniversity.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平台提供的学习资源和实践机会通过撰写技术博客不仅巩固了所学知识也锻炼了技术写作能力。特别提示在学习过程中要遵守学校规定不在网络上发布课程内部资料和带有学校标识的资源保护知识产权和学校信息安全。