MySQL:实验三:数据库设计与应用开发

一．实验目的（1）掌握数据库设计步骤和方法（2）针对用户具体需求，能够运用数据库设计理论设计数据库结构（3）能够使用数据库设计工具进行数据库设计（4）能够运用高级语言成功连接数据库（5）掌握触发器的设计和使用方法（6）掌握存储过程的设计和使用方法（7）掌握基于JDBC驱动的数据库应用开发方法二．实验内容1.某学院有若干系，每个系有若干班级和教研室，每个教研室有若干教师，其中有的教授和副教授每人各带若干研究生，每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修，某学生选修某一门课程有一个成绩。请根据需求分析描述，利用POWERDESIGNER数据库设计工具设计该数据库的概念结构。（1）设计结果截图（图片要居中）（2）对设计结果中的要素说明如下：该数据库概念结构中的实体包括：学校 系 班级 教研室 学生 课程 教员该数据库概念结构中实体之间的联系包括：校-系 系-班 系-教研室 班-学生 室-教 指导 选修利用POWERDESIGNER数据库设计工具设计该数据库的逻辑结构。（1）设计结构截图如下：（图片要居中）对数据库逻辑结构设计结果的说明如下：（根据转换原则分析实体之间联系的转换原则，分析相关表格的属性组成，标出表的外键）学校（学校名） 此为“学校”实体对应的关系模式，主键为学校名。系（系编号，学校名，系名） 此为“系”实体对应的关系模式，包含了联系“校-系”(1:n)所对应的关系模式，主键是系编号，外键是学校名。班级（班级编号，系编号，班级名） 此为“班级”实体对应的关系模式，包含了联系“系-班”(1:n)所对应的关系模式，主键是班级编号，外键是系编号。教研室（教研室编号，系编号，教研室名） 此为“教研室”实体对应的关系模式，包含了联系“系-教研室”(1:n)所对应的关系模式，主键是教研室编号，外键是系编号。学生（学号，班级编号，职工号，学生姓名，学历） 此为“学生”实体对应的关系模式，包含了联系“班-学生”(1:n)和“指导”(1:n)所对应的关系模式，主键是学号，外键是班级编号，职工号。课程（课程编号，课程名） 此为“课程”实体对应的关系模式，主键是课程编号。教员（职工号，教研室编号，职工姓名，职称） 此为“教员”实体对应的关系模式，包含了联系“室-教”(1:n)所对应的关系模式，主键是职工号，外键是教研室编号。选修（课程编号，学号，成绩） 此为联系“选修”(n:n)对应的关系模式，主键是课程编号和学号，外键是课程编号，学号。设计数据库的物理结构MYSQL的常用存储引擎名称特点MEMORY（1）在数据库目录内，每个表均以.frm格式的文件表示。（2）表数据及索引被存储在内存中，支持哈希索引（3）数据存取速度快（4）常应用于临时表（5）后缀为.frm，仅存储表结构而不存储表数据MyISAM（1）不支持事务（2）行级锁和外键约束（3）大量select下使用（4）早期默认的引擎（5）.frm存储表结构、.myd存储数据、.myi存储索引InnoDB（1）支持事务、故障恢复、并发控制（2）提供行级锁机制和外键约束的功能（3）大量insert、delete和update下使用（4）目前MySQL默认的存储引擎。（5）.ibd文件存储表结构、表数据、索引（0）存储引擎——如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。存储引擎也称表类型（即存储和操作此表的类型）。（1）查看数据库可以支持的存储引擎，用show engines; （2）查询到系统默认的存储引擎show variables like 'default_storage_engine';（3）创建表的时候指定存储引擎，默认是 InnoDBCREATE TABLE test_table(id int primary key auto_increment,name varchar(128) NOT NULL)ENGINE = InnoDB;（4）mysql支持很多存储引擎，每一种存储引擎对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎（1）学校信息管理系统数据库的物理结构截图：（2）相关表格的分区存储说明分区—可以把单个表的多个部分存储到不同的路径下。提高查询效率，提高磁盘IO吞吐量。类型说明RANGE分区允许DBA将数据划分不同范围LIST分区通过DBA定义的列表的值所对应的行数据进行分割HASH分区通过这个Hash码不同数值对应的数据区域进行分区KEY分区根据KEY值进行分区创建一个普通未分区的表S0:CREATE TABLE S0(sname varchar(20) DEFAULT NULL,sno char(9) NOT NULL,ssex char(6) DEFAULT NULL,sage tinyint DEFAULT NULL,sdept varchar(255),PRIMARY KEY(sno)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 首先在my.ini配置文件中添加以下配置项，保存以后重新启动：innodb_file_per_table=1 #允许每张表单独存放innodb_directories="d:\data0;d:\data1;d:\data2" #添加存放分区表的目录创建分区表：按照院部系类别拆分学生表，水平拆分为三个区存放，各个分区数据单独存放，分别对应三个.ibd数据文件。CREATE TABLE S1(sname varchar(20) DEFAULT NULL,sno char(9) NOT NULL,ssex char(6) DEFAULT NULL,sage tinyint DEFAULT NULL,sdept varchar(255),PRIMARY KEY(sno,sdept)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 PARTITION by list COLUMNS (sdept)PARTITIONS 3(PARTITION p0 VALUES IN('CS') DATA DIRECTORY='d:/data0',PARTITION p1 VALUES IN('IS') DATA DIRECTORY='d:/data1',PARTITION p2 VALUES IN('MA') DATA DIRECTORY='d:/data2');运行截图如下（包括物理文件目录截图）：（3）分别插入至少10条有效数据到 S0,S1中，运行如下查询。通过explain语句来模拟查询语句的执行情况，测试SQL性能。注意rows参数的值为估算要扫描的记录行数。explain select * from s0 where sdept='CS';//查询s0表，注意rows值运行截图如下：explain select * from s1 where sdept='CS';//查询s1表，注意partitions,rows值运行截图如下：explain select * from s1 partition(p0);//查单独查询分区P0,提高查询效率，注意rows值运行截图如下：执行create index i_sdept on s0(sdept);然后再执行explain select * from s0 where sdept='CS';//查询s0表，注意rows、possible_keys、key值。通过索引，提高查询效率。运行截图如下：注意：在我们项目开发中，分区表其实是很少用的，下面简单说明下几点原因：①分区字段的选择有限制。②若查询不走分区键，则可能会扫描所有分区，效率不会提升。③若数据分布不均，分区大小差别较大，可能性能提升也有限。④普通表改造成分区表比较繁琐。⑤需要持续对分区进行维护，比如到了6月份前就要新增6月份的分区。4.数据库的索引设计MYSQL8.0支持的索引类型：索引类型创建语句存储方式用途普通索引create index 索引名 on 表名（列名 排序方式）B+树仅加速查询唯一索引create unique index 索引名 on 表名（列名 排序方式）B+树加速查询 + 列值唯一主键索引创建表时通过primary key关键字指定B+树加速查询 + 列值唯一组合索引（普通索引）create index 索引名 on 表名（列名1 排序方式1,列名2 排序方式2,......）B+树仅加速查询单列索引（普通索引）create index 索引名 on 表名（列名 排序方式）B+树仅加速查询聚集索引（有且只有一个）默认主键索引为聚集索引B+树叶子节点是数据节点；找到了索引值就找到了需要的数据哈希索引自适应性，自动创建哈希适合等值查询和连接查询全文索引create fulltext index 索引名 on 表名（列名 排序方式）倒排索引CHAR,VARCHAR, TEXT 等才可以建立全文索引；提高WHERE name LIKE “%word%"模糊查询效率；不支持分区表查看索引：show index from 表名 \G;//\G可以将查询结果垂直输出，也可以去掉\G,水平输出运行截图如下：users表：部门表：运行结果说明如下：表名索引关键字索引设计意图users表user_id主键索引已作为主键自动创建username唯一索引假设用户名需要唯一 uk_usernameemail普通索引如果经常通过邮箱进行登录或查找用户部门表部门编号主键索引已作为主键自动创建部门_name唯一索引假设部门名需要唯一 uk_部门名称部门_负责人普通索引如果经常通过部门负责人进行登录或查找部门5.数据库存储过程编写（1）统计离散数学的成绩分布情况，即按照各分数段统计人数先创建统计表：create table grade_num(dist char(20) primary key,num int);再编写存储过程：delimiter $$create procedure dist_num(n_0_60 int,n_60_70 int,n_70_80 int,n_80_90 int,n_90_100 int)beginselect count(*) into n_0_60 from sc where cno='2' and grade>=0 and grade<60;select count(*) into n_60_70 from sc where cno='2' and grade>=60 and grade<70;select count(*) into n_70_80 from sc where cno='2' and grade>=70 and grade<80;select count(*) into n_80_90 from sc where cno='2' and grade>=80 and grade<90;select count(*) into n_90_100 from sc where cno='2' and grade>=90 and grade<=100;insert into grade_num(dist,num) values('0~60',n_0_60);insert into grade_num(dist,num) values('60~70',n_60_70);insert into grade_num(dist,num) values('70~80',n_70_80);insert into grade_num(dist,num) values('80~90',n_80_90);insert into grade_num(dist,num) values('90~100',n_90_100);end $$最后执行并验证：call dist_num(0,0,0,0,0);select * from grade_num;运行结果截图如下：（2）统计任意一门课的平均成绩编写存储过程：delimiter $$create procedure avegrade(incname char(40))beginselect avg(grade) from sc where cno=(select cno from course where cname=incname);end $$执行验证：call avegrade(‘离散数学’);运行结果截图如下：（3）将学生选课成绩从百分制改为等级制（即A、B、C、D、E）先修改sc表增加等级制的属性列chengji：alter table sc add column chengji char(2);再编写存储过程：delimiter $$create procedure gradetype()beginupdate sc set chengji= 'E' where grade < 60;update sc set chengji= 'D' where grade >= 60 and grade < 70;update sc set chengji= 'C' where grade >= 70 and grade < 80;update sc set chengji= 'B' where grade >= 80 and grade < 90;update sc set chengji= 'A' where grade >= 90; end $$最后执行并验证：call gradetype();select * from sc;运行结果截图如下：6.触发器的编写（1）在sc表中创建一个插入触发器sc_insert，实现当向sc表插入某个学生的选课记录之前，先检查student 表中是否存在该学生，若不存在则给出错误并终止操作。delimiter $$create trigger sc_insert before inserton sc for each rowbeginif not exists(select * from student where sno=new.sno) thensignal sqlstate '45000' set message_text='不存在这个学生，无法插入该选课记录';end if;end $$执行并验证的结果截图如下：（2）在student表中创建一个删除触发器s_delete，实现当删除student表中某个学生的记录时，同时将sc表中与该学生有关的数据全部删除。delimiter $$create trigger s_delete after deleteon student for each rowbegindelete from sc where sno=old.sno;end $$执行并验证的结果截图如下：（3）在student表中创建一个更新触发器s_upate，实现当更新student表中某个学生的记录时，同时将sc表中与该学生有关的数据全部更新。delimiter $$create trigger s_update after updateon student for each rowbeginupdate sc set sno=new.sno where sno=old.sno;end $$执行并验证的结果截图如下：三．心得体会1.本次实验项目的收获如下：（1）熟练掌握了数据库设计的技术与方法，数据库设计是设计数据库的各级模式并建立数据库及其应用系统，用来有效地存储和管理数据；本次实验通过Power Designer进行了数据库的概念结构设计（将需求分析得到的用户需求抽象为信息结构的过程，用E-R模型来体现），逻辑结构设计（根据六个转换原则将设计好的基本E-R 图转换为相符合的逻辑结构）和物理结构设计（给逻辑数据模型选取最合适的物理结构，主要包括存取方法和存储结构）的练习，其中还学习了运用高级语言连接数据库，实现逻辑结构与物理结构的相互转换。（2）通过编写存储过程，更好掌握了存储过程的知识，存储过程是过程化SQL语句书写的过程，调用存储过程可以减少数据在数据库和服务器之间的传输，提高数据处理的效率，还有利于集中控制，方便维护；可以通过SQL语句进行存储过程的创建create，执行call，修改alter和删除drop，执行时通过调用存储过程的名字和参数，使数据库管理系统运行其中的SQL语句进行并数据处理，从而实现该存储过程。（3）此外还掌握了一种特殊的存储过程——触发器trigger，它是一个在修改指定表中的数据时执行的存储过程，创建触发器可以用来强制实现不同表中逻辑相关数据的引用完整性或一致性，其确保了数据的完整性；存储过程可以通过调用存储名来执行，而触发器可以通过事件的进行触发被执行。2.本次实验项目的不足之处如下：（1）初次使用高级语言连接数据库，操作起来可能比较困难，需要先下载所安装MySQL对应的JDBC包或ODBC包，再通过包进行连接，连接时要注意选择对应的网络地址和对应操作的某个数据库。（2）编写存储过程时，容易出现语法错误，存储过程名字后面必须带有“()”，即使没有任何参数，语句末尾要带上分号，用delimiter $$...$$进行隔离区分；存储过程的参数没有默认值，所以在调用带参数的存储过程时，不能省略参数（可以用null替代）。3.实验过程中的错误以及调试解决方法（1）在数据库存储过程的编写中出现了未知的语法错误，错误截图如下：尝试修改语句并重新调试，发现去掉关键字as后就可以正确运行，截图如下：查阅资料才知道，MySQL存储过程不需要在procedure的body前加as，而SQL Server存储过程必须加as关键字。（2）在执行存储过程时出现了错误，错误截图如下：分析可知是因为在编写存储过程时用到了参数，而在执行调用时却没有带上参数，所以出现了该错误；修改执行语句并尝试运行，错误得以解决，截图如下：