MySQL云数据库应用与实践 课件汇 李宁 第8-13章 数据库应用程序开发- 数据库应用开发综合实战案例

第八章

数据库应用程序开发Database西北工业大学NorthwesternPolytechnicalUniversity数据库目录8.1

实战目标与准备8.2数据库应用软件开发概述8.3

JDBC编程实战8.4ODBC编程实战8.5数据库连接池应用实战8.6ADO.NET——Window窗口程序实战8.7ORM技术——基于Django框架的Web程序实战8.8其他数据库访问方法实战8.1实战目标与准备实战目标本章目标是掌握数据库应用程序中的数据库访问技术，期望读者能独立开发一个基本的数据库应用程序，特别是其中的数据库访问部分。实战准备本机的应用程序开发，需要根据自己选择则的编程语言提前安装好以下软件：JDBC：JAVA开发环境ODBC：C++或者C#开发环境或者Python开发环境其他访问数据库方式：C++或者Python开发环境8.2数据库应用软件开发概述8.3JDBC类名路径备注驱动程序类java.sql.Driver由各数据库厂商提供驱动程序管理类java.sql.DriverManager作用于应用程序与驱动程序之间数据库连接类java.sql.Connection用于建立与指定数据库的连接静态SQL语句执行类java.sql.Statement用于执行静态SQL语句并返回结果动态SQL语句执行类java.sql.PreparedStatement用于执行含参SQL语句并返回结果存储过程语句执行类java.sql.CallableStatement用于执行存储过程语句并返回结果结果集处理类java.sql.ResultSet用于检索结果集中的数据JDBC常用类JDBC开发涉及到的所有类都包含在java.sql包中不同的JDBC版本接口名和使用略有差异8.3JDBCJDBC操作数据库的基本工作流程步骤1：加载驱动程序步骤2：建立与数据库的连接定义连接的URL利用生成的URL建立与数据库的连接步骤3：执行SQL语句创建语句执行类对象执行SQL语句，可以通过executeQuery、executeUpdate、execute三种方式执行。步骤4：处理结果集步骤5：释放资源8.3JDBCConnectionconn=null;//mysql8.0Stringdriver="com.mysql.cj.jdbc.Driver";//驱动Stringurl="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=UTC";//URL//mysql5.7//Stringdriver="com.mysql.jdbc.Driver";//StringURL="jdbc:mysql://localhost:3306/student";

Stringuser="root";//用户名Stringpasswd="*****";//密码

try{Class.forName(driver);conn=DriverManager.getConnection(url,user,passwd);}catch(Exceptione){ e.printStackTrace();}8.3JDBCtry{ stat=conn.createStatement(); ret=stat.executeQuery("select*froms");

while(ret.next()) { for(inti=1;i<=4;i++) System.out.print(ret.getString(i)+"");

System.out.println(""); } conn.close();}catch(SQLExceptione){ e.printStackTrace();}8.4ODBCODBC的概念微软开发的用于连接各种关系型数据库的函数库，以动态链接库的形式提供给程序使用，其目的是给出统一的编程接口，从而简化数据库应用程序的编写。ODBC数据库应用程序驱动程序管理器(DLL)SQLServer驱动程序Oracle驱动程序Foxpro驱动程序(DLL)Sybase驱动程序DB2驱动程序SQLServer数据源Oracle数据源Foxpro数据源Sybase数据源DB2数据源DBDBDBDBDB8.4ODBCODBC数据源的配置手工配置：控制面板-〉管理工具->ODBC数据源(64位)或32位（需要事先安装该MySQL的ODBC驱动）8.4ODBC——C++调用ODBCCdatabasedatabase;CrecordsetrecordSet;if(!database.IsOpen()){database.Open(NULL,FALSE,FALSE,_T("ODBC;DSN=student_mysql64;UID=root;PWD=123456"));recordSet.m_pDatabase=&database;}CStringstrSQL;strSQL.Format("select*froms");recordSet.Open(CRecordset::forwardOnly,strSQL);for(inti=0;i<recordSet.GetRecordCount();i++){CStringtemp;recordSet.GetFieldValue("score_id",temp);recordSet.GetFieldValue("student_name",temp);recordSet.MoveNext();}recordSet.Close();database.Close();8.4ODBC—Python调用ODBCimportmysql.pyodbc8.5数据库连接池应用实战数据库连接:昂贵（反复建立和释放与数据库的物理连接）数据库连接池：负责分配、管理和释放数据库连接。需要使用时去连接池中取出一条空闲的连接，而不是新建一条连接。常见连接池：Java：

Druid、C3P0

HikariCP等Python:DBUtils.PooledDB等8.5数据库连接池应用实战8.6ADO.NET——Window窗口程序实战ADO.NET的体系结构8.6ADO.NET——Window窗口程序实战ADO.NET中几个重要概念Connection对象：主要用于建立与数据源的活动连接Command对象：主要用来执行SQL语句DataAdaper对象：主要用来通过Dataset处理数据DataReader对象：主要用来读取数据（快速不缓冲的顺序访问）5.DataSet:离线处理的核心，用来存储数据6.DataTable:DataSet中以表的形式存储数据8.6ADO.NET——Window窗口程序实战8.7ORM技术—基于Django框架的Web程序实战ORM（Object/RelationalMapping）即对象关系映射，是一种数据持久化技术。它在对象模型和关系型数据库直接建立起对应关系。Dept.javadept-deptNo-deptName-locationDEPT(table)DEPTnumber(2)DNAMEVARCHAR(20)LOCVARCHAR(20)ORM类表8.7ORM技术—基于Django框架的Web程序实战Django的MVT架构MVC(Model-View-Control)8.7ORM技术—基于Django框架的Web程序实战model层：数据库的每张表有一个继承自models类的对应子类view层：new_book:Book类的实例，该类基类方法save()可以插入数据8.8其他数据库访问技术—libmysql例如：mysql提供lib：libmysql.dll

mysql_init() //初始化数据结构mysql_library_init()//初始化数据库mysql_real_connect()//连接数据库mysql_query() //查询

mysql_store_result() //存储结果mysql_fetch_row() //逐行处理mysql_free_result() //释放资源mysql_close() //关闭连接直接利用DBMS提供的动态链接库8.8其他数据库访问技术—嵌入式SQL什么是嵌入式SQL(EmbeddedSQL)?

SQL语句嵌入到其他高级语言中，这时高级语言被称为(宿)主语言，这种方式下使用的SQL称为嵌入式SQL。(静态SQL/动态SQL)如何区分主语言与SQL语句？前缀：EXECSQL结束标志：随主语言的不同而不同如：C语言中使用SQL：EXECSQL<SQL语句>;例：EXECSQLDROPTABLEStudent;常见的几个数据库系统预编译器（命令）PostgreSQL:ecpg(.pgc->.c)SQLServer:nsqlprep.exe（.sqc->.c）Oralce:proc/cc++8.8其他数据库访问技术—嵌入式SQL例：带有嵌入式SQL的一小段C程序:查询并打印学生成绩。EXECSQLINCLUDESQLCA;

/*(1)定义SQL通信区*/EXECSQLBEGINDECLARESECTION;/*(2)说明主变量*/

CHARSno(5);

CHARCno(3);

INTgrade;EXECSQLENDDECLARESECTION;main()

{

EXECSQLDECLAREC1CURSORFOR

/*(3)游标操作(定义游标)*/

SELECTSno,Cno,gradeFROMSC;

/*从SC表查询Sno,Cno,Grade*/

EXECSQLOPENC1;

/*(4)游标操作（打开游标）*/

for(;;) {

EXECSQLFETCHC1INTO:Sno,:Cno,:Grade;

/*(5)游标操作（将当前数据放入主变量并推进游标指针）*/

if(sqlca.sqlcode<>SUCCESS)

/*(6)利用SQLCA中的状态信息决定何时退出循环*/

break;printf("Sno:%s,Cno:%s,Grade:%d",Sno,Cno,Grade);/*打印查询结果*/ }

EXECSQLCLOSEC1;

/*(7)游标操作（关闭游标）*/

EXECSQLDEALLOCATEC1；/*(8)游标操作（释放游标）*/}合作QQ：243001978第九章

数据库应用软件测试Database西北工业大学NorthwesternPolytechnicalUniversity数据库目录9.1

实战目标与准备9.2数据库应用软件测试概述9.3

数据库设计验证9.4功能测试9.5性能测试9.6负载测试与压力测试9.7安全性测试9.1实战目标与准备实战目标本章目标是掌握数据库应用程序的基本测试内容、方法以及工具等。实战准备JMeter5.4：性能测试工具官网下载地址：/9.2数据库应用软件测试概述数据库应用系统数据库管理系统9.3数据库设计验证Student(Sno,Sdept,Mname,Cname,Grade)1.数据冗余太大：浪费大量的存储空间例：每一个系主任的姓名重复出现2.修改复杂数据冗余，更新数据时，维护数据完整性代价大。例：某系更换系主任，系统必须修改与该系学生有关的每个元组3.插入异常该插的数据无法插入到表中。例：如果一个系刚成立，尚无学生，我们就无法把这个系及其系主任的信息存入数据库。⒋删除异常不该删除的数据被删掉了例：如果某个系的学生全部毕业了，我们在删除该系学生信息的同时，把这个系及其系主任的信息也丢掉了9.3数据库设计验证数据库设计步骤、成果与验证观点9.4功能测试功能测试通过测试验证软件的每个功能是否都按照用户的需求进行了实现并且能正常使用。测试重点—用户需求—WebApp,移动App测试方法

白盒测试+黑盒测试9.5性能测试性能测试：验证系统的主要功能或组件的实际性能是否与用户的性能需求一致。

数据库应用软件的性能测试：关注该软件整体或者其中某些组件在规定时间内，响应用户或系统输入的能力，通常用请求响应时间，吞吐量等度量性能优劣。常见工具：LoadRunner、Jmeter、LocustDBMS基准性能测试：Sysbench、TPCC、TPCH等9.5性能测试基于Jmeter的测试示例：9.6负载和压力测试负载测试（LoadTest）通过逐渐增加负载评估系统或者组件性能的测试方法，目的是观察系统在各种不同的负载情况下是否都能够正常工作。而性能测试通常是直接指定一个特定的负载进行测试。压力测试（StressTest）评估系统或者组件处于或超过预期负载时的运行情况。重点关注系统在峰值负载或超出最大负载情况下的处理能力，在压力级别逐渐增加时，系统性能应该是按照预期缓慢下降，但是不应发生系统崩溃的现象。9.7安全性测试SQL注入是指通过把SQL命令插入到Web表单提交或页面请求等的查询字符串中。select*frommemberwhereUID='"&request("ID")&"'andPasswd='"&request("Pwd")"'select*frommemberwhereUID='Admin‘--'AndPasswd=''假设猜测系统有Admin的用户，构造以上SQL语句，由于SQL语言中“--”是注释符号，其后的字符都会被当作注解，因此该攻击者无需输入密码可直接进入系统9.7安全性测试针对SQL注入的测试构造专门的注入字符或者语句单独测试-例如给可输入的用户名构造zhang'--借助漏洞扫描工具测试-扫描之后，进行对应的升级数据库等动作合作QQ：243001978第十章

数据库中的事务与锁Database西北工业大学NorthwesternPolytechnicalUniversity数据库目录10.1

实战目标与准备10.2事务概述与常用命令10.3

MySQL中ACID特性验证10.4并发导致的数据不一致问题重现实战10.5MySQL并发控制——锁10.6封锁协议与MySQL的隔离级别10.7基于隔离级别与锁解决数据不一致问题10.8MySQL的死锁10.9MySQL并发控制——MVCC10.1实战目标与准备实战目标本章的目标是在掌握了数据库和基本表的创建和管理的基础上，学习如何通过一定的工具或SQL命令来对基本表中的数据进行增、删、改、查等基本操作。熟练掌握对基本表进行数据插入、修改和删除的SQL语句。熟练掌握数据查询的SQL语句（简单查询和复合查询）。掌握对视图的数据操作，并了解其与基本表数据操作的关系。掌握SQL语句查询性能分析的基本知识。了解TPC-C基准数据库10.2事务概述与常用命令1.

事务的概念一个数据库操作序列一个不可分割的工作单位（要么全做，要么全不做）恢复和并发控制的基本单位在关系数据库中的事务和程序：一个事务可以是一条SQL语句，一组SQL语句或整个程序。一个程序通常包含多个事务。10.2事务概述与常用命令2.

事务的特性(ACID)原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持续性（Durability）要么全做，要么全不做事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态一个事务的执行不能被其他事务干扰事务一旦提交，它对数据库中数据的改变就应该是永久性的。10.2事务概述与常用命令commit10.2事务概述与常用命令rollback10.2事务概述与常用命令starttransaction;insertintotvalues(1);insertintotvalues(2);savepoints1;insertintotvalues(3);insertintotvalues(4);savepoints2;insertintotvalues(5);insertintotvalues(6);select*fromt;rollbacktosavepoints2;select*fromt;savepoint10.2事务概述与常用命令功能例子查看当前活动事务select*frominformation_schema.innodb_trx;设置/查看是否自动提交set@@autocommit=0;setglobal@@autocommit=0;setautocommit=0;setglobalautocommit=0;showglobal/sessionvariableslike'autocommit’;(省略global/session修饰符时，默认为session范围)设置/查看全局隔离级别setglobaltransactionisolationlevelrepeatableread;showglobalvariableslike'transaction_isolation';设置/查看当前连接(session)隔离级别settransactionisolationlevelrepeatableread;setsessiontransactionisolationlevelrepeatableread;showvariableslike'transaction_isolation’;showsessionvariableslike'transaction_isolation’;select@@transaction_isolation;10.3MySQL中ACID特性验证事务的原子性与一致性示例10.3MySQL中ACID特性验证事务的隔离性与持久性示例10.4并发导致的数据不一致问题重现实战第一类丢失更新问题10.4并发导致的数据不一致问题重现实战第二类丢失更新问题10.4并发导致的数据不一致问题重现实战读脏数据问题10.4并发导致的数据不一致问题重现实战不可重复读10.4并发导致的数据不一致问题重现实战幻读10.5MySQL并发控制——锁10.5MySQL并发控制——锁几张与锁相关的系统表：10.5MySQL并发控制——锁InnoDB的支持的锁锁类别描述SharedandExclusiveLocks共享锁/排他锁IntentionLocks意向共享锁/意向排他锁RecordLocks行锁GapLocks间隙锁Next-KeyLocks临键锁InsertIntentionLocks插入意向锁AUTO-INCLocks自增锁PredicateLocksforSpatialIndexes空间索引使用，本处暂不讨论10.5MySQL并发控制——锁InnoDB：间隙锁create

tablet(

idint(11)

not

null,

cint(11),

dint(11),

primary

key

(id),

keyc(c)

)

ENGINE

=

InnoDB;

insert

intotvalues(0,0,0),(5,5,5),(15,15,15)；(-∞,0)(0,5)(5,15)(15,+∞)0515间隙锁冲突：是“往这个间隙中插入一个记录”。select*fromtwherec=5forupdate;限制插入c值=5的左右两个间隙锁范围内的值：[0,15]10.5MySQL并发控制——锁InnoDB：间隙锁insert

intot(id,c,d)values(0,0,0),(5,5,5),(15,15,15)；begin;

select*fromtwherec=5forupdate;primarykey(id),keyc(c))ENGINE

=

InnoDB;

T110.5MySQL并发控制——锁InnoDB：间隙锁10.5MySQL并发控制——锁InnoDB：间隙锁10.5MySQL并发控制——锁查看锁的方式：showengineinnodbstatus;MySQL8.0的系统表：innodb_trx,data_locks,innodb_lock_waits.

select*frominformation_schema.innodb_trxselect*fromperformance_schema.data_locks

select*fromsys.innodb_lock_waits;information_schema.innodb_trx表（事务信息表，trx_id）10.5MySQL并发控制——锁查看锁的方式：sys.innodb_lock_waits表performance_schema.data_locks表LOCK_TYPE:record/tableLOCK_MODE:S[,GAP],X[,GAP],IS[,GAP],IX[,GAP],AUTO_INC,UNKNOWNLOCK_STATUS:GRANTED/WAITINGLOCK_DATA:与锁关联的数据（表锁时，该值为NULL）。10.6封锁协议与MySQL的隔离级别10.6封锁协议与MySQL的隔离级别封锁协议-各级封锁区别：什么操作需要申请封锁以及何时释放锁-封锁协议级别越高，一致性程度越高10.6封锁协议与MySQL的隔离级别隔离级别读未提交：ReadUncommitted读已提交：ReadCommitted可重复读：RepeatableRead可串行化：Serializable默认隔离级别：MySQL是可重复读；KingBase、SQLServer、Oracle等为读已提交注意：事务隔离级别并非越高越好MySQL常用:setsessiontransactionisolationlevelreaduncommittedselect@@transaction_isolation10.7基于隔离级别与锁解决数据不一致问题10.8MySQL的死锁10.9MySQL并发控制——MVCC多版本并发控制(MVCC):保留数据的多个版本。写数据时，旧版本的数据并不删除，写一个新的版本，这样并发的读操作还能读到旧版本的数据。读写不阻塞，并发度高。传统封锁方式MVCC在T1准备写A的时候，

为A生成一个新版本（A’），T2事务不用等待，可继续在A’上执行。T2准备提交时，确认事务T1是否已经完成。如果T1已经完成了，T2就可以放心地提交。如果T1还没有完成，那么T2必须等待直到T1完成。10.9MySQL并发控制——MVCCMySQL中的InnoDB中实现MVCC写新数据时，把旧数据移动到一个单独的地方，如回滚段中其他读数据时，从回滚段中把旧版本数据读出来MySQLInnodb中每个表都包含三个隐藏字段rowid,trx_id和roll_pointer，MVCC主要使用了trx_id和roll_pointer的数据版本信息。10.9MySQL并发控制——MVCC合作QQ：243001978第十一章

数据库的恢复技术Database西北工业大学NorthwesternPolytechnicalUniversity数据库目录11.1

实战目标与准备11.2数据库中的恢复技术概述11.3

MySQL中基于数据转储的备份与恢复实战11.4MySQL中基于日志的手动恢复实战11.5MySQL中的Redo与Undo日志11.1实战目标与准备实战目标本章的目标是在掌握了数据库和基本表的创建和管理的基础上，学习如何通过一定的工具或SQL命令来对基本表中的数据进行增、删、改、查等基本操作。熟练掌握对基本表进行数据插入、修改和删除的SQL语句。熟练掌握数据查询的SQL语句（简单查询和复合查询）。掌握对视图的数据操作，并了解其与基本表数据操作的关系。掌握SQL语句查询性能分析的基本知识。了解TPC-C基准数据库11.2数据库中的恢复技术概述故障是不可避免的：系统故障：计算机软、硬件故障人为故障：操作员的失误、恶意的破坏等。故障的影响：运行中事务非正常中断，影响数据库中数据的正确性。破坏数据库，全部或部分丢失数据。数据库的恢复

把数据库从错误状态恢复到某一已知的正确状态(亦称为一致状态或完整状态。恢复技术是衡量系统优劣的重要指标11.2数据库中的恢复技术概述数据转储是指数据库管理员定期将全部或者部分数据存储到备份介质上，发生故障时从备份介质恢复的过程。通常DBA可以根据具体业务场景、涉及数据量大小等因素，设计合适的定时全量备份、增量备份等不同备份策略，例如每周进行全量备份，每日进行增量备份。日志系统：记录事务对数据库的更新操作，主要用于事务故障恢复和系统故障恢复。11.3MySQL中基于数据转储的备份与恢复实战MySQL中的基于数据转储的数据备份恢复可以通过多种方式进行。官方提供的mysqldump、mysqlbackup等第三方工具，如xtrabackup，mydumper等。11.4MySQL中基于日志的手动恢复实战日志类型日志标识默认说明一般查询日志log不开启记录所有的查询，占空间影响性能，默认不开(general-log)错误日志log-err开启记录mysql服务的错误慢查询日志log-slow-queries开启记录执行时间超过long_query_time设定时间阈值(秒)的SQL语句，mysqldumpslow。二进制日志log-bin开启Mysql的server层变更日志，主要用于记录修改数据或有可能引起数据改变的mysql语句，可用于数据复制或者恢复。滚动文件，由.index文件管理。中继日志relaylog不开启主从复制时使用的日志事务日志innodb_log开启InnoDB特有的事务日志redo和undo日志，帮助提高事务的效率。存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。MySQL中日志文件类型：my.ini或者f11.4MySQL中基于日志的手动恢复实战二进制日志：Binlog文件：用于进行数据恢复的日志（主从同步或者恢复）--显示binlog的基本信息showvariableslike'log_bin‘;--确认binlog是否开启showmasterlogs;--显示所有的binlog文件showmasterstatus;--显示最新的binlog的最后位置showbinlogevents;

--显示所有的binlog--显示指定binlogshowbinlogeventsin'THINKPAD_LINING-bin.000019’;--显示指定binlog中某个位置之后的logshowbinlogeventsin'THINKPAD_LINING-bin.000019'from40080;showbinlogeventsin'THINKPAD_LINING-bin.000019'from40080limit10;--刷新binlog，生成一个新的binlog文件flushlogs;Binlog文件：用于进行事务恢复的日志Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。Statement：每一条会修改数据的sql都会记录在binlog中。Mixedlevel:

是以上两种的混合使用，一般的语句修改使用statement格式保存binlog，如一些函数;statement无法完成主从复制的操作，则采用row格式保存binlog.MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式。11.4MySQL中基于日志的手动恢复实战11.4MySQL中基于日志的手动恢复实战Binlog文件：用于binlog进行事务恢复创建新的binlog日志文件执行常规SQL数据操作（含创建，增删改操作）从日志中找回待恢复之前的SQL语句，导出为test000022.sql1.mysqlbinlog.exemysql-bin.000022>test_000022.txt2.在txt日志中查找待恢复（如DROPTABLE）日志的位置(该语句的at2413)3.导出binlog日志中'DROPTABLE'之前的SQL语句mysqlbinlogmysql-bin.000022-ddb1--skip-gtids--stop-position=2413>test000022.sql在mysql中执行以上SQL文件flushlogs;showmasterstatus;假设：最新：mysql-bin.000022sourceC:\ProgramData\MySQL\MySQLServer8.0\Data\test000022.sql11.4MySQL中基于日志的手动恢复实战Binlog文件：(输出为txt文件）Window的日志默认路径：C:\ProgramData\MySQL\MySQLServer8.0\Data11.5MySQL中的Redo与Undo日志MySQL的InnoDB存储引擎架构（引自MySQL官网）11.5MySQL中的Redo与Undo日志redolog：物理日志，提升事务持久化处理的性能。只记录事务对数据页做了哪些修改。在磁盘上由名为ib_logfile0和ib_logfile1。包含两部分：先写内存

->

后续某个时间一次性写多条到磁盘。内存中的日志缓冲（redologbuffer，易失）磁盘上的日志文件（redologfile，稳定）undolog：逻辑日志，用于保证数据的原子性。保存事务发生之前的数据的一个版本，可以用于回滚。是MVCC(多版本控制)实现的关键。/doc/dev/mysql-server/8.0.11/PAGE_INNODB_REDO_LOG.html合作QQ：243001978第十二章

面向云数据库的运维DatabaseSystem西北工业大学NorthwesternPolytechnicalUniversity数据库系统李宁目录12.1数据库的运维任务12.2.云数据库管理12.3.云DBA的智能运维数据库的运维任务环境部署与维护备份与恢复数据库的重组与重构12.1数据库的运维任务监控与诊断目录12.1数据库的运维任务12.2.云数据库管理12.3.云DBA的智能运维12.2云数据库管理云数据库实例整体管理备份管理参数管理任务中心回收站单个云数据库实例管理查看监控指标、登录、转按需（按需与包月可以互转）、创建只读购买相同配置、磁盘扩容、规格变更、创建备份、参数修改、重置密码、重启实例与退订12.2云数据库管理DAS数据管理服务库管理SQL操作导入导出结构方案目录12.1数据库的运维任务12.2.云数据库管理12.3.云DBA的智能运维12.3云DBA的智能运维SQL诊断日报诊断12.3云DBA的智能运维SQL分析慢SQL合作QQ：243001978第十三章

数据库应用开发综合实战案例DatabaseSystem西北工业大学NorthwesternPolytechnicalUniversity数据库系统李宁目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行1.选题背景源自真实场景：数据库实验教学的痛点学生提交大量SQL作业增大教师批阅工作量SQL语句写法灵活多样导致批阅难度较大作业批阅结果可能也无法及时反馈给学生开发一个高效的SQL-OJ（OnlineJudge）系统以提高数据库课程的教学效率和效果。目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行2.需求调研——方法

调查组织机构/用户调查各个部门业务活动明确新系统的各种要求确定新系统的边界调查的方法跟班作业开调查会专人介绍调查表查阅记录其他信息需求+处理需求+非功能需求2.需求调研案例——MYSQL-OJ数据需求用户信息题库与题目信息试卷信息练习或考试信息答题信息功能需求教师：班级管理、班内成员管理、题库/题目/试卷/考试管理、统计信息学生：注册、个人信息管理、考试答题功能、仪表盘管理员：学校管理、教师用户管理非功能需求安全性需求:权限控制性能需求：支持1000人同时在线答题，题目3秒内提交到系统分权限、管数据、定功能需求调研结果的分析和表达方法数据流图数据字典数据项数据结构数据流数据存储处理过程2.需求调研——结果表达信息需求处理需求完整性需求安全性需求性能需求其他2.需求调研案例——MYSQL-OJ数据流图2.需求调研案例——MYSQL-OJ数据字典：以该系统服务100所学校，10年为例数据处理数据流数据存储目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行3.数据库设计概念结构设计：ER图设计逻辑结构设计:ER图转换成关系模式安全性与完整性设计：加密、防注入、约束、触发器等物理结构设计：存储与存取，重点是索引物理数据库3.1概念结构设计数据抽象，局部视图设计视图集成DFDDD总E-R图逻辑结构设计返回用户，征求意见到满意为止需求分析分E-R图

1)抽象数据+设计局部视图

2)集成局部视图，得到全体的概念结构3.1概念设计案例—MYSQL-OJ

语义描述与数据抽象用户相关实体：学校、班级、用户（管理员、教师、学生）考试相关实体：题库、题目、试卷、考试或者练习活动根据现实世界与应用需求分析，实体之间语义描述如下：一个学校拥有多个班级，一个班级只能属于某个学校；一个班级有多名学生，一个学生只能属于一个班级。系统可以有多个管理员，任意一个管理员可以增删改所有学校、教师的基本信息；每位教师管理所负责班级的学生信息。一位教师可以创建多个题库（题库指一个事先准备好的数据库，可包含多张表以及部分数据），每个题库只能被一位老师创建。基于一个题库可以出多道题目，每个题目只能属于某一个题库。每位教师可以出若干道题目。一套试卷可以由任意多道题目组成，相同题目可出现在多套试卷中，且相同题目在不同试卷中分值可以不同。每位教师可给所负责的一个或多个班级，将任意一套可访问的试卷发布为一次考练活动。学生可以作答自己可见的未过期考练活动。3.1概念设计案例—MYSQL-OJ

实体属性描述3.1概念设计案例—MYSQL-OJ系统总ER图（12个实体+14个联系）分ER图3.2逻辑结构设计—ER图转换

逻辑结构设计：ER图转换为逻辑结构基本转换实体与属性转换1对1联系：合并到任意1端[强制参与]或单独转成新关系1对多转换：合并到N端或单独成新关系多对多转换：联系转换成单独成新关系特殊转换：复合属性、弱实体、ISA等设计优化范式理论判断与分解基于应用特征分析的分解与合并外模式设计3.2逻辑设计案例ER图中的实体转换ER图中的联系转换3.2逻辑模式优化案例1.模式优化：以Exam、Exam_class为例分析。其他关系均满足BCNF。1)Exam(考试ID,考试名,考试描述,开始时间,结束时间,活跃状态,是否公布答案,教师ID,试卷ID)：该关系的码是考试ID，不存在非主属性和主属性（考试ID）对码的部分函数依赖和传递函数依赖，因此满足BCNF。2）Exam_class(考试ID(FK),班级ID(FK),发布时间)：假设允许把某次考试给某个班级发布多次，则码中需要加入发布时间。由此该关系为全码，无非主属性，满足BCNF。2.其他优化（反范式化设计、分解或合并等）

题目名：该系统中教师身份登录时，以班级为单位进行作答统计信息，在访问题

目的作答记录表时，会频繁使用题目名称，因此在该表中设计如下的冗余题目名。

coding_exam_ques_answer_rec：考试题目记录ID,学生ID,题目ID,考试ID,

提交答案,答案正确性,最新提交时间,提交次数,本题得分,题目名。3.外模式设计

创建一个教师信息视图：Teacher，仅包含教师ID和教师姓名即可。3.3安全性完整性设计安全性权限控制（管理员、教师、学生各自的权限设计）数据加密（用户的密码）防SQL注入等（ORM框架等）完整性完整性约束实体完整性示例：

User_Student表的student_id为主键。参照完整性示例：

User_Student表的class_id为外键。用户自定义完整性：User_User表的priority取值：0:学生,1:教师,2:管理员。触发器T1：为了避免统计功能中的实时统计给系统带来过大压力，设计了触发器：在完成某个学生的判卷后更新Coding_ExamAnswerRec表的成绩字段时，自动更新单独设计的统计信息表Coding_Stat中相关信息。3.4物理结构设计1.确定数据库物理存储结构确定数据库物理结构的内容1.确定数据的存放位置和存储结构2.确定系统配置影响数据存放位置和存储结构的因素1.硬件环境2.应用需求3.存取时间4.存储空间利用率5.维护代价例如：将表和索引放在不同的磁盘（机械盘和SSD盘）；日志和数据分开；分区数据根据应用情况将易变部分与稳定部分、存取频率较高部分与存取频率较低部分分开存放，确定热点数据，以提高系统性能。3.4物理结构设计2.确定数据库物理存取方法哪些属性列建立索引（启发式规则）查询条件聚集函数连接条件建立什么样的索引聚集索引（主键索引）/二级索引（非主键索引）B+树索引/Hash索引唯一索引单一索引/复合索引全文索引3.4物理设计案例—MYSQL-OJ数据存储：

本例中，在数据存储方面，为了提高系统的高可用性，在云数据库实例创建时，选择了主备架构，该架构可以定期备份主库的数据，防止数据丢失。其他采用默认。数据存取

结合系统具体的应用功能需求，列出系统中可能相对频繁执行的查询需求，然后逐一考虑索引设计。系统中每个人仅能看到自己或者自己关联班级的相关信息，学生ID、教师ID、班级ID经常出现在连接条件中。答题功能中，频繁使用考试ID、试卷ID、题目ID等属性，但因为这些字段都有主键索引，所以无需额外创建索引。学生仪表盘的统计功能中，需统计该学生所有考试的总提交次数。该功能将对coding_exam_ques_answer_rec表的提交次数做求和计算，该表数据量较大，因此按照索引设计的启发式规则，对频繁使用聚集函数的字段设计复合索引（学生ID，提交次数）。3.4数据库设计产出成果物最终形成共13张表(含视图）3.4数据库设计产出成果物最终形成共13张表(含视图）3.4数据库设计产出成果物目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行4.数据库实施创建数据库表手动创建DDL语句基于建模工具生成DDL语句加载数据测试数据：造数脚本或者造数工具

（华为云数据库、存储过程、python\sh脚本等）生产数据：如果是已有库中有数据

（数据导入导出、数据迁移工具等，如kettle等）4.数据库实施造数脚本程序例子：python+MYSQL：loaddataLOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/userinfo.csv'INTOTABLEuserinfofieldsterminatedby',';目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行5.数据库应用程序设计功能设计

——确定功能模块可以用思维导图、模块图等形式等表示5.数据库应用程序设计功能设计

——分模块详细描述5.数据库应用程序设计功能设计

——分模块详细描述（简洁版）简要说明每个界面的功能5.数据库应用程序设计功能设计

——分模块详细描述（详细版）1.概要