精华内容
下载资源
问答
  • 下面为数据库实验的一些报告(oracle)(十一周之后) 这些报告每次大概是一节课的时间,由于课程原因,很多内容都简化了,知识内容也可能相对片面一些,并不全面,只作练习参考使用。 csdn不允许文字长度过长,...

    下面为数据库实验的一些报告(oracle)(第十周之后)

    可直接下载资源:https://download.csdn.net/download/qq_42785226/14622190

    这些报告每次大概是一节课的时间,由于课程原因,很多内容都简化了,知识内容也可能相对片面一些,并不全面,只作练习参考使用。

    csdn不允许文字长度过长,这里没办法只好拆成三份。

    数据库实验报告

    数据库实验报告 1

    数据库实验第四周 5

    一、准备工作: 5

    SQL*Plus创建公共用户C##scott: 5

    二、 实验阶段(SQL题目练习): 7

    1、列出至少有一个员工的所有部门。 8

    2、列出薪金比“SMITH”多的所有员工。 8

    3、列出所有员工的姓名及其直接上级的姓名。 9

    4、 列出受雇日期早于其直接上级的所有员工。 10

    5、 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 10

    6、列出所有“CLERK”(办事员)的姓名及其部门名称。 11

    7、列出最低薪金大于1500的各种工作。 12

    8、列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。 12

    9、列出薪金高于公司平均薪金的所有员工。 12

    10、列出与“SCOTT”从事相同工作的所有员工。 13

    11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 13

    12、 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 13

    14、列出所有员工的姓名、部门名称和工资。 16

    15、列出所有部门的详细信息和部门人数。 17

    16、列出各种工作的最低工资。 17

    17、列出各个部门的MANAGER(经理)的最低薪金。 17

    数据库实验第五周 18

    Dual操作(DESC,查询内容,日期(格式),作计算器等): 18

    临时变量&: 19

    模拟merge并查看结果: 20

    扩展update(Returning): 21

    %rowtype: 22

    %type: 23

    Select   ..as..: 23

    Dual查询user、日期(带特定格式)、生成随机数: 24

    定义(declare)(常量、变量)、赋值(:=)操作练习: 24

    IF、 ELSIF、 ENDIF练习(注意为:ELSIF): 25

    Case语句练习: 26

    循环语句(LOOP——EXIT;)练习: 26

    数据库实验第六周 27

    导入表:SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp; 27

    黑屏下查询归档文件信息:SQL> conn /as sysdba; SQL> ArCHIVE Log list; 28

    查询表信息:SQL> select * from cat; 28

    查询用户信息:SQL> select * from dba_users; 33

    查询数据文件信息dba_data_files:select * from dba_data_files; 36

    select * from v$datafile; 37

    查询归档文件信息:select name,log_mode from v$database; 38

    查询控制文件信息:select * from v$controlfile; 38

    查询日志文件信息:select * from v$log; 38

    select * from v$logfile; 39

    按组添加日志文件LOGFILE并作出相关查询:ALTER DATABASE ADD LOGFILE GROUP 4('log1a.rdo','log2a.rdo')size 5000k; 39

    按成员MEMBER添加日志文件LOGFILE到组GROUP并作出相关查询:ALTER DATABASE ADD LOGFILE MEMBER 'log3a.log' TO Group 4; 40

    按成员删除日志文件:ALTER DATABASE DROP LOGFILE MEMBER 'log3a.log'; 40

    按组删除日志文件:ALTER DATABASE DROP LOGFILE  Group 4; 41

    创建表空间: 41

    数据库实验第八周 42

    查询数据块大小:SHOW PARAMETER db_block_size; 42

    查询用户分区信息:select * from user_extents; 42

    归档模式与非归档模式: 42

    ARCHIVE LOG LIST;黑屏模式下进行归档模式查询 42

    SQL> SELECT name,log_mode FROM v$database; 43

    以下四步将数据库从非归档模式转化为归档模式(黑屏下): 43

    可能出现的错误: 43

    数据库实验第九周 45

    Command: 45

    /*分支结构1*/ 45

    /*分支结构2*/ 45

    /*带临时变量的多分支结构*/ 46

    /*多分支case语句*/ 47

    /* 47

    1.简单型case 47

    2.搜索性case 47

    3.嵌入到select语句执行复杂任务的case 47

    4.嵌入到PL/SQL程序语句(如赋值语句)的case 47

    */ 47

    /*简单型case*/ 47

    /*等值比较的case语句*/ 48

    /*搜索case表达式*/ 48

    /*嵌入到select语句执行复杂任务的case*/ 48

    /*将上述例子的结果以表的形式保存起来*/ 49

    /*case的select练习*/ 49

    /*对于学生借阅的图书信息,检验图书是否过期 49

    1.过期 49

    2.没过期 49

    3.没有借阅图书*/ 49

    --日期简单练习: 49

    select to_char(months_between(sysdate,to_date('20151001','yyyymmdd'))) from dual; 50

    select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL; 50

    select trunc(sysdate-to_date('20181001','yyyymmdd'))天数 from dual; 50

    /*嵌入到pl/sql程序语句(如赋值语句)的case*/ 50

    实现: 50

    数据库实验第十周 57

    创建分区表: 57

    查询分区表: 58

    SQL> select * from part_book1; 58

    SQL> select * from part_book1 partition(part1); 58

    SQL> select * from part_book1 partition(part2); 58

    SQL>select * from dba_part_tables; 58

    SQL> select * from dba_part_tables where table_name='PART_BOOK1'; 59

    修改分区表: 60

    SQL> Alter table part_book1 add partition part4 values('北京邮电出版社') tablespace system; 60

    Command: 60

    查询图书是否过期及应缴金额(select--case查询) 60

    查询优良等级(select--case查询) 61

    循环练习——exit when+for逆序循环(for count in reserve count_1..count_10) 61

    while和for循环练习(1+2+3=6) 62

    实现: 62

    数据库实验第十一周 68

    创建表空间: 68

    查询数据文件: 70

    在删除时将表空间中的内容和数据文件全部删除: 74

    创建临时表空间tmptbs: 74

    创建大文件表空间: 74

    创建撤销表空间: 74

    表空间和数据文件的维护: 74

    将表空间test1设置为脱机状态: 74

    更改表空间名字: 75

    在现有表空间基础上添加一数据文件: 75

    对已创建的表空间中已有的数据文件的管理: 75

    移动表空间中的数据文件: 75

    读写状态修改: 76

    将表空间table2设置为只读表空间 76

    将表空间table2设置为可读写状态: 76

    数据库实验第十二周 76

    不带参数的显式游标举例: 76

    游标的%isopen 属性练习: 77

    带 return和参数传递的游标: 77

    一、利用while循环检索游标 79

    二、利用for循环检索游标 79

    使用游标分别遍历xs表中的xh,zxf: 80

    利用游标WHILE循环统计并输出scott.emp表各个部门的平均工资, 若平均工资大于2000,则输出“该部门平均工资较高。” 81

    利用FOR循环统计并输出各个部门的平均工资。 82

    数据库实验第十三周 83

    带update的游标,loop EXIT WHEN -end loop;Scott.emp表,利用游标,给工资低于1200 的员工增加工资50。并输出“编码为’员工编码号’的工资已经更新” 83

    for update写法: 84

    for循环  FOR v_emp IN c_emp LOOP -END LOOP;修改scott.emp表员工的工资,如果员工的部门号为10,工资提高100;部门号为20,工资提高150;部门号为30,工资提高200;否则工资提高250。 85

    修改emp表的工资,工资不足1000的,调整为1500,工资高于1000的,调整为原来工资的1.5倍,调整后,若工资〉10000,则设其为10000。 86

    游标变量: 87

    数据库实验第十四周 88

    存储过程样例: 88

    存储过程练习: 89

    数据库实验第十五周 92

    创建触发器(在删除xs表中的数据时进行备份): 92

    将对xs表的操作记录(insert,delete,update)在sql_info表中记录下来: 92

    创建一个触发器,输出对scott.emp表的操作(插入后员工号和员工名,更新后员工工资,删除的员工号和员工名。): 93

    当执行插入操作时,统计操作后员工人数;当执行更新工资操作时,统计更新后员工平均工资;当执行删除操作时,统计删除后各个部门剩余的人数(游标): 94

    数据库实验第十六周 95

    创建一个存储过程,以部门号为该存储过程的in类型参数,查询该部门的平均工资,并输出该部门中比平均工资高的员工号、员工名。(for 循环遍历  游标): 95

    用存储过程进行模糊查找,如查找ename中包含L的雇员信息: 96

    rownum练习: 96

    补充:见存储过程需要注意的问题,异常处理与触发器补充 97

    补充内容 98

    循环结构: 98

    求10的阶乘: 98

    水仙花数: 102

    触发器补充: 103

    设置系统触发器: 103

    创建logon触发器,在登录的时候进行记录: 103

    创建logoff触发器,在退出登录的时候进行记录(注意此时的触发器时间上不要采用after,因为采用after客户机注销进程终止,不能进行其他操作): 105

    设置触发器:作用为禁止在休息日(周六、周天)改变scott.emp雇员信息(包括添加删除和修改)。 107

    启动和关闭触发器 108

    存储过程需要注意的一些问题 108

    异常处理(系统异常处理以及自定义异常处理) 113

     

    数据库实验第十一周

    创建表空间:

    一个表空间带多个数据文件,test1.dbf不支持自动扩展test2.dbf支持自动扩展,每次增加1000k,表空间区管理为本地管理,指定大小1M,段设置为自动管理。

    SQL> create tablespace table1

      2  datafile 'c:test1.dbf' size 2M autoextend off,

      3           'c:test2.dbf' size 5M autoextend on next 1000k

      4  extent management local

      5         uniform size 1M

      6  segment space management auto;

     

    Tablespace created

     

    一个表空间带多个数据文件,test3.dbf和test4.dbf,表空间区管理为本地管理管理,段设置为自动管理。

    SQL> create tablespace table2

      2  datafile 'c:test3.dbf' size 2M,

      3           'c:test4.dbf' size 5M

      4  extent management local autoallocate

      5  segment space management auto;

     

    Tablespace created

     

    SQL> select * from v$datafile;

     

         FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED    CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME   OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             PLUGGED_IN BLOCK1_OFFSET AUX_NAME                                                                         FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME

    ---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- --------------------

             1               11 2007/4/17 3:3          0          1 SYSTEM  READ WRITE            1014748 2020/11/13 15:4                     0                                                      521802         521803 2020/9/6 11  503316480      61440            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                                        0          8192 NONE                                                                                               0

             2           519918 2007/4/17 6:0          1          2 ONLINE  READ WRITE            1014748 2020/11/13 15:4                     0                                                      521802         521803 2020/9/6 11   73400320       8960            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                                       0          8192 NONE                                                                                               0

             3             5554 2007/4/17 3:3          2          3 ONLINE  READ WRITE            1014748 2020/11/13 15:4                     0                                                      521802         521803 2020/9/6 11  272629760      33280            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                                        0          8192 NONE                                                                                               0

             4             9202 2007/4/17 3:3          4          4 ONLINE  READ WRITE            1014748 2020/11/13 15:4                     0                                                      521802         521803 2020/9/6 11    5242880        640            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                                         0          8192 NONE                                                                                               0

             5           546572 2020/9/6 11:0          6          5 ONLINE  READ WRITE            1014748 2020/11/13 15:4                     0                                                           0              0              104857600      12800    104857600       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF                                       0          8192 NONE                                                                                               0

             6          1021166 2020/11/13 17          7          6 ONLINE  READ WRITE            1021170 2020/11/13 17:0                     0                                                           0              0                2097152        256      2097152       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST1.DBF                                          0          8192 NONE                                                                                               0

             7          1021169 2020/11/13 17          7          7 ONLINE  READ WRITE            1021170 2020/11/13 17:0                     0                                                           0              0                5242880        640      5242880       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST2.DBF                                          0          8192 NONE                                                                                               0

             8          1021775 2020/11/13 17          8          8 ONLINE  READ WRITE            1021779 2020/11/13 17:2                     0                                                           0              0                2097152        256      2097152       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST3.DBF                                          0          8192 NONE                                                                                               0

             9          1021778 2020/11/13 17          8          9 ONLINE  READ WRITE            1021779 2020/11/13 17:2                     0                                                           0              0                5242880        640      5242880       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST4.DBF                                          0          8192 NONE                                                                                               0

     

    9 rows selected

     

    SQL>

    SQL> create tablespace table3

      2  datafile 'c:test5.dbf' size 2M,

      3           'c:test6.dbf' size 5M

      4  extent management local autoallocate

      5  segment space management auto;

     

    Tablespace created

     

    查询数据文件:

    SQL> select * from v$datafile;

     

         FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED    CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME   OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             PLUGGED_IN BLOCK1_OFFSET AUX_NAME                                                                         FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME

    ---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- --------------------

             1               11 2007/4/17 3:3          0          1 SYSTEM  READ WRITE            1014748 2020/11/13 15:4                     0                                                      521802         521803 2020/9/6 11  503316480      61440            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                                        0          8192 NONE                                                                                               0

             2           519918 2007/4/17 6:0          1          2 ONLINE  READ WRITE            1014748 2020/11/13 15:4                     0                                                      521802         521803 2020/9/6 11   73400320       8960            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                                       0          8192 NONE                                                                                               0

             3             5554 2007/4/17 3:3          2          3 ONLINE  READ WRITE            1014748 2020/11/13 15:4                     0                                                      521802         521803 2020/9/6 11  272629760      33280            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                                        0          8192 NONE                                                                                               0

             4             9202 2007/4/17 3:3          4          4 ONLINE  READ WRITE            1014748 2020/11/13 15:4                     0                                                      521802         521803 2020/9/6 11    5242880        640            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                                         0          8192 NONE                                                                                               0

             5           546572 2020/9/6 11:0          6          5 ONLINE  READ WRITE            1014748 2020/11/13 15:4                     0                                                           0              0              104857600      12800    104857600       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF                                       0          8192 NONE                                                                                               0

             6          1021166 2020/11/13 17          7          6 ONLINE  READ WRITE            1021170 2020/11/13 17:0                     0                                                           0              0                2097152        256      2097152       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST1.DBF                                          0          8192 NONE                                                                                               0

             7          1021169 2020/11/13 17          7          7 ONLINE  READ WRITE            1021170 2020/11/13 17:0                     0                                                           0              0                5242880        640      5242880       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST2.DBF                                          0          8192 NONE                                                                                               0

             8          1021775 2020/11/13 17          8          8 ONLINE  READ WRITE            1021779 2020/11/13 17:2                     0                                                           0              0                2097152        256      2097152       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST3.DBF                                          0          8192 NONE                                                                                               0

             9          1021778 2020/11/13 17          8          9 ONLINE  READ WRITE            1021779 2020/11/13 17:2                     0                                                           0              0                5242880        640      5242880       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST4.DBF                                          0          8192 NONE                                                                                               0

            10          1021995 2020/11/13 17          9         10 ONLINE  READ WRITE            1021999 2020/11/13 17:3                     0                                                           0              0                2097152        256      2097152       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST5.DBF                                          0          8192 NONE                                                                                               0

            11          1021998 2020/11/13 17          9         11 ONLINE  READ WRITE            1021999 2020/11/13 17:3                     0                                                           0              0                5242880        640      5242880       8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST6.DBF                                          0          8192 NONE                                                                                               0

     

    11 rows selected

     

    SQL>

    SQL> create tablespace table4

      2  datafile 'c:/test5.dbf' size 2M,

      3           'c:/test6.dbf' size 5M

      4  extent management local autoallocate

      5  segment space management auto;

     

    Tablespace created

     

    经过上述一系列操作后表空间创建结果如下:

    (注意在创建表空间时,我们所规定的dbf文件所在位置的不同:如果我们路径名位置写错了(找不到路径),将会存在默认的路径位置。例如:

    datafile 'c:test5.dbf' size 2M,

              'c:test6.dbf' size 5M…

    与datafile 'c:/test5.dbf' size 2M,

                 'c:/test6.dbf' size 5M

    上面那个路径没有加/导致路径出错,放在默认位置

     

    删除表空间(先创建后删除):

    SQL>create table t1

      2  (no int)

      3  tablespace ts1;

     

    Table created

     

    在删除时将表空间中的内容和数据文件全部删除:

    SQL> drop tablespace ts1 including contents and datafiles;

     

    Tablespace dropped

     

    创建临时表空间tmptbs:

    SQL> create temporary tablespace tmptbs

      2  tempfile 'c:\tmptbs.dbf'

      3  size 2m reuse

      4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m;

     

    Tablespace created

    创建大文件表空间:

    (由于文件过大,不在机器上实验,以免造成空间浪费或者资源调度空间不够导致宕机):

    create bigfile tablespace bigtbs

       datafile 'c:\bigtbs.dbf' size 10G;

     

    创建撤销表空间:

    SQL> create undo tablespace undotbs01

      2  datafile 'c:\undotbs02.dbf' size 2m reuse;

     

    Tablespace created

     

    表空间和数据文件的维护:

    将表空间test1设置为脱机状态:

    SQL> Alter tablespace test1 OFFLINE;

     

    Tablespace altered

    更改表空间名字:

    有的时候在online状态下是不允许改名的,显示该文件正在使用,这时就可以先将表空间状态修改为offline状态之后再进行改名:

     

    SQL> alter tablespace test1 rename to t2;

     

    Tablespace altered

     

    在现有表空间基础上添加一数据文件:

    SQL> Alter tablespace t2

      2  add datafile 'c:\101.dbf' size 1m

      3  reuse;

     

    Tablespace altered

     

    对已创建的表空间中已有的数据文件的管理:

    SQL> Alter database orcl datafile 'c:\101.dbf' resize 2m;

     

    Database altered

     

    移动表空间中的数据文件:

    (四步:修改表空间状态为offline,物理方式手动移动,将表空间中文件的原名称和路径修改为新的路径和名称(在这里可以实现改名),修改表空间的状态为online)。


    SQL> alter tablespace table2 offline ;

     

    Tablespace altered

     

    物理方式手动移动数据文件。

     

    SQL> alter tablespace table2 rename datafile 'c:\test3.dbf' to 'd:\t3.dbf';

     

    Tablespace altered

     

    SQL> alter tablespace table2 online ;

     

    Tablespace altered

     

    读写状态修改:

    将表空间table2设置为只读表空间

    SQL> alter  tablespace table2 read only;

     

    Tablespace altered

     

    将表空间table2设置为可读写状态:

    在设置之前table2应该为只读状态,否则此时无法设置为读写。

    SQL> alter tablespace table2 read write;

     

    Tablespace altered

    数据库实验第十二周

    不带参数的显式游标举例

    SQL> declare

      2          cursor my_cursor

      3               is   select xh from xs;

      4           v_xh xs.xh%type;

      5  begin

      6           open my_cursor;

      7           fetch my_cursor into v_xh;

      8            dbms_output.put_line(v_xh);

      9            dbms_output.put_line(my_cursor%rowcount);

     10            Close my_cursor;

     11     exception

     12         when others then

     13            dbms_output.put_line(sqlcode||sqlerrm);

     14  end;

     15  /

     

    061101

    1

     

    PL/SQL procedure successfully completed

     

    SQL> select xh from xs;

     

    XH

    ------

    061101

    101112

    001

    121112

     

    游标的%isopen 属性练习

    SQL> declare

      2     cursor  c_1 is select * from xs;

      3     v_1 c_1%rowtype;

      4  begin

      5    if c_1%isopen=false then

      6           open c_1;

      7    end if;

      8    fetch c_1 into v_1;

      9    dbms_output.put_line(v_1.xh||v_1.xm||v_1.zxf);

     10    close c_1;

     11  end;

     12  /

     

    061101王林50

     

    PL/SQL procedure successfully completed

     

    SQL> select * from xs;

     

    XH     XM     ZYM    XB CSSJ        ZXF BZ

    ------ ------ ------ -- ----------- --- --------------------

    061101 王林   计算机 男 1986/2/10    50

    101112 李明   计算机 男 1986/1/30    36

    001    张琼   计算机                 45 三好学生

    121112 王小二 计算机 男 1986/1/30    36

     

    SQL>

    带 return和参数传递的游标

      2  DECLARE

      3     TYPE emp_record_type IS RECORD(

      4          f_name   scott.emp.ename%TYPE,

      5          h_date   scott.emp.hiredate%TYPE);

      6     v_1   EMP_RECORD_TYPE;

      7     CURSOR c3(v_deptno NUMBER,v_job VARCHAR2)

      8   --声明游标,有参数有返回值

      9            RETURN EMP_RECORD_TYPE

     10     IS

     11        SELECT ename, hiredate FROM scott.emp

     12        WHERE deptno=v_deptno AND job =v_job;

     13  BEGIN

     14     OPEN c3(v_job=>'MANAGER', v_deptno=>10);

     15   --打开游标,传递参数值

     16     LOOP

     17        FETCH c3 INTO v_1;    --提取游标

     18        IF c3%FOUND THEN

     19           DBMS_OUTPUT.PUT_LINE(v_1.f_name||'的雇佣日期是' ||v_1.h_date);

     20        ELSE

     21           DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');

     22           EXIT;

     23        END IF;

     24     END LOOP;

     25     CLOSE c3;   --关闭游标

     26  END;

     27  /

     

    CLARK的雇佣日期是09-6月 -81

    已经处理完结果集了

     

    PL/SQL procedure successfully completed

     

     

    SQL> SELECT ename, hiredate FROM scott.emp WHERE deptno>10 AND job='MANAGER';

     

    ENAME      HIREDATE

    ---------- -----------

    JONES      1981/4/2

    BLAKE      1981/5/1

     

    SQL> SELECT ename, hiredate FROM scott.emp WHERE deptno>=10 AND job>='MANAGER';

     

    ENAME      HIREDATE

    ---------- -----------

    ALLEN      1981/2/20

    WARD       1981/2/22

    JONES      1981/4/2

    MARTIN     1981/9/28

    BLAKE      1981/5/1

    CLARK      1981/6/9

    KING       1981/11/17

    TURNER     1981/9/8

     

    8 rows selected

     

    /*

    一、利用while循环检索游标

    DECLARE

        CURSOR cursor_name IS SELECT…;

    BEGIN

        OPEN cursor_name;

        FETCH…INTO…;

        WHILE cursor_name%FOUND

         LOOP

               ……   

               FETCH…INTO…;

         END LOOP;

        CLOSE cursor;

    END;

    注:在打开游标后用fetch语句先取一行到变量,然后再用while对该游标进行判断,而不是打开后就立即用while进行判断 。

    */

     

    /*

    二、利用for循环检索游标

     

    系统隐含地定义了一个数据类型为%ROWTYPE的变量,并以此作为循环的计算器。

    系统自动打开游标,不用显式地使用OPEN语句打开;

    系统重复地自动从游标工作区中fetch数据并放入计数器变量中。

    系统自动进行%FOUND属性检查以确定是否有数据

    当游标工作区中所有的记录都被提取完毕或循环中断时,系统自动地关闭游标。

    DECLARE

       CURSOR cursor_name IS SELECT…;

    BEGIN

       FOR loop_variable IN 游标名称

          LOOP

               ……

        END LOOP;

    END;

    */

     

     

    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

    Connected as system@ORCL

     

     

    SQL> set serveroutput on;

     

    SQL> select * from xs;

     

    XH     XM     ZYM    XB CSSJ        ZXF BZ

    ------ ------ ------ -- ----------- --- --------------------

    061101 王林   计算机 男 1986/2/10    50

    101112 李明   计算机 男 1986/1/30    36

    001    张琼   计算机                 45 三好学生

    121112 王小二 计算机 男 1986/1/30    36

     

    使用游标分别遍历xs表中的xh,zxf

      2  DECLARE

      3        v_xh char(6);

      4        v_zxf number(2);

      5        /* 定义游标is select from */

      6        CURSOR    XS_CUR3

      7              IS SELECT XH,ZXF FROM XS;

      8  BEGIN

      9      /* 打开游标 */

     10      OPEN XS_CUR3;

     11      /* 游标赋值fetch into */

     12      FETCH XS_CUR3 INTO v_xh,v_zxf;

     13      /*游标遍历while found条件*/

     14    WHILE XS_CUR3%FOUND

     15    LOOP

     16     dbms_output.put_line('学号: '||v_xh||'  总学分:'||v_zxf);

     17     FETCH XS_CUR3 INTO v_xh,v_zxf;

     18    END LOOP;

     19    /* 遍历执行完毕,关闭游标 */

     20    CLOSE XS_CUR3;

     21    END;

     22  /

     

    学号: 061101  总学分:50

    学号: 101112  总学分:36

    学号: 001     总学分:45

    学号: 121112  总学分:36

     

    PL/SQL procedure successfully completed

     

     

    利用游标WHILE循环统计并输出scott.emp表各个部门的平均工资 若平均工资大于2000,则输出“该部门平均工资较高。

      3  DECLARE

      4    CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno;

      5    v_dept c_dept_stat%ROWTYPE;

      6  BEGIN

      7    OPEN c_dept_stat;

      8    FETCH c_dept_stat INTO v_dept;

      9    WHILE c_dept_stat%FOUND LOOP

     10          DBMS_OUTPUT.PUT_LINE('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));

     11          if (v_dept.avgsal>=2000) then

     12             dbms_output.put_line(v_dept.deptno||'号部门工资较高');

     13          end if;

     14          FETCH c_dept_stat INTO v_dept;

     15    END LOOP;

     16    CLOSE c_dept_stat;

     17  END;

     18  /

     

    部门号为30 平均工资为1566.6

    部门号为20 平均工资为2175

    20号部门工资较高

    部门号为10 平均工资为2916.6

    10号部门工资较高

     

    PL/SQL procedure successfully completed

     

     

    SQL> select * from scott.emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    SQL> SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno;

     

    DEPTNO     AVGSAL

    ------ ----------

        30 1566.66666

        20       2175

        10 2916.66666

     

    SQL>

    利用FOR循环统计并输出各个部门的平均工资。

      2  DECLARE

      3      CURSOR c_1 IS SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno;

      4      V_dept  c_1%ROWTYPE;

      5  BEGIN

      6      FOR   v_dept    IN c_1

      7  LOOP

      8      DBMS_OUTPUT.PUT_LINE('部门号: '||v_dept.deptno||'  平均工资: '||v_dept.avgsal);

      9    END LOOP;

     10  END;

     11  /

     

    部门号: 30  平均工资: 1566.666666666666666666666666666666666667

    部门号: 20  平均工资: 2175

    部门号: 10  平均工资: 2916.666666666666666666666666666666666667

     

    PL/SQL procedure successfully completed

     

    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

    Connected as system@ORCL

     

    SQL> conn system/test

    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

    Connected as system

     

    SQL> set serveroutput on;

     

    SQL>

     

    数据库实验第十三周

    带update的游标,loop EXIT WHEN -end loop;Scott.emp表,利用游标,给工资低于1200 的员工增加工资50。并输出“编码为’员工编码号’的工资已经更新”

      4  DECLARE

      5     v_empno  scott.emp.empno%TYPE;

      6     v_sal      scott.emp.sal%TYPE;

      7     CURSOR c_cursor IS SELECT empno,sal FROM scott.emp;

      8  BEGIN

      9    --打开游标

     10     OPEN c_cursor;

     11     --循环遍历操作 loop-end loop;

     12     LOOP

     13        FETCH c_cursor INTO v_empno, v_sal;

     14        --exit when %notfound

     15        EXIT WHEN c_cursor%NOTFOUND;

     16        --查询条件,满足则执行update操作 if-end if;

     17        IF v_sal<=1200 THEN

     18              UPDATE scott.emp SET Sal=Sal+50 WHERE empno=v_empno;

     19              DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');

     20        END IF;

     21     DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor %ROWCOUNT);

     22     END LOOP;

     23     CLOSE c_cursor;

     24  END;

     25  /

     

    编码为7369工资已更新!

    记录数:1

    记录数:2

    记录数:3

    记录数:4

    记录数:5

    记录数:6

    记录数:7

    记录数:8

    记录数:9

    记录数:10

    编码为7876工资已更新!

    记录数:11

    编码为7900工资已更新!

    记录数:12

    记录数:13

    记录数:14

     

    PL/SQL procedure successfully completed

     

    SQL>

    for update写法:

    SQL> DECLARE

      2     v_empno  scott.emp.empno%TYPE;

      3     v_sal      scott.emp.sal%TYPE;

      4     --定义游标的时候标明update: for update

      5     CURSOR c_cursor IS SELECT empno,sal FROM scott.emp where sal<1200 for update;

      6   begin

      7        open c_cursor;

      8        LOOP

      9             FETCH c_cursor INTO v_empno, v_sal;

     10             EXIT WHEN c_cursor%NOTFOUND;

     11             --不再记录员工编号进行等值查询,而是查找当前游标所在的那一条位置: where current of c_cursor

     12              UPDATE scott.emp SET Sal=Sal+50 WHERE current of c_cursor;

     13              DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');

     14              DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor%ROWCOUNT);

     15        END LOOP;

     16     CLOSE c_cursor;

     17  END;

     18  /

     

    编码为7369工资已更新!

    记录数:1

    编码为7900工资已更新!

    记录数:2

     

    PL/SQL procedure successfully completed

     

    SQL>

    for循环  FOR v_emp IN c_emp LOOP -END LOOP;修改scott.emp表员工的工资,如果员工的部门号为10,工资提高100;部门号为20,工资提高150;部门号为30,工资提高200;否则工资提高250。 

      3  DECLARE

      4    CURSOR c_emp IS SELECT * FROM scott.emp FOR UPDATE;

      5     v_zl NUMBER;

      6     v_emp c_emp%rowtype;

      7  BEGIN

      8       FOR v_emp IN c_emp LOOP

      9          CASE v_emp.deptno

     10               WHEN 10 THEN v_zl:=100;

     11               WHEN 20 THEN v_zl:=150;

     12               WHEN 30 THEN v_zl:=200;

     13               ELSE   v_zl:=250;

     14         END CASE;

     15      UPDATE scott.emp SET sal=sal+v_zl WHERE CURRENT OF c_emp;

     16    END LOOP;

     17  END;

     18  /

     

    PL/SQL procedure successfully completed

     

    SQL> select * from scott.emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17    1150.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1800.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1450.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      3125.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1450.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      3050.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2550.00               10

     7788 SCOTT      ANALYST    7566 1987/4/19     3150.00               20

     7839 KING       PRESIDENT       1981/11/17    5100.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1700.00      0.00     30

     7876 ADAMS      CLERK      7788 1987/5/23     1400.00               20

     7900 JAMES      CLERK      7698 1981/12/3     1350.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3150.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1400.00               10

     

    14 rows selected

     

     

    SQL>

    修改emp表的工资,工资不足1000的,调整为1500,工资高于1000的,调整为原来工资的1.5倍,调整后,若工资〉10000,则设其为10000。

      3  declare

      4      cursor c_1 is

      5          select empno,sal from scott.emp

      6             for update of sal nowait;

      7   v_sal scott.emp.sal%type;

      8   begin

      9     for cursor_1 in c_1

     10       loop

     11           if cursor_1.sal<=1000 then

     12                 v_sal:=1500;

     13            else

     14                 v_sal:=cursor_1.sal*1.5;

     15                 if v_sal>10000 then

     16                      v_sal:=10000;

     17                  end if;

     18            end if;

     19            update scott.emp set sal=v_sal where current of c_1;

     20      end loop;

     21  end;

     22  /

     

    PL/SQL procedure successfully completed

     

    SQL> select * from scott.emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17    1725.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     2700.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     2175.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      4687.50               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     2175.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      4575.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      3825.00               10

     7788 SCOTT      ANALYST    7566 1987/4/19     4725.00               20

     7839 KING       PRESIDENT       1981/11/17    7650.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      2550.00      0.00     30

     7876 ADAMS      CLERK      7788 1987/5/23     2100.00               20

     7900 JAMES      CLERK      7698 1981/12/3     2025.00               30

     7902 FORD       ANALYST    7566 1981/12/3     4725.00               20

     7934 MILLER     CLERK      7782 1982/1/23     2100.00               10

     

    14 rows selected

     

    游标变量:

    SQL> declare

      2         type t_dept is REF CURSOR  return scott.emp%rowtype;

      3         c_1 t_dept;

      4         v_row  scott.emp%rowtype;

      5  begin

      6         open c_1 for select * from scott.emp where  deptno=10;

      7         fetch c_1 into v_row;

      8         dbms_output.put_line(v_row.empno||' ' ||v_row.job);

      9         close c_1;

     10  

     11         open c_1 for select * from scott.emp where sal>=2000;

     12         fetch c_1 into v_row;

     13         dbms_output.put_line(v_row.deptno||' ' ||v_row.job);

     14         close c_1;

     15  end;

     16  /

     

    7782 MANAGER

    30 SALESMAN

     

    PL/SQL procedure successfully completed

     

    SQL>

     

    数据库实验第十四周

    存储过程样例:

    SQL> CREATE OR REPLACE PROCEDURE HelloWorld3 (

      2  p_user_name VARCHAR2,

      3  p_val1 VARCHAR2 DEFAULT ' Good Moning,',

      4  p_val2 VARCHAR2 DEFAULT ' Nice to Meet you') AS

      5  BEGIN

      6  dbms_output.put_line('Hello ' || p_user_name || p_val1 || p_val2 || '!');

      7  END HelloWorld3;

      8  /

     

    Procedure created

     

    SQL>

    SQL> BEGIN

      2  HelloWorld3('Edward');

      3  HelloWorld3('Edward', ' Good Night,');

      4  HelloWorld3('Edward', ' Good Night,', 'Bye');

      5  END;

      6  /

     

    Hello Edward Good Moning, Nice to Meet you!

    Hello Edward Good Night, Nice to Meet you!

    Hello Edward Good Night,Bye!

     

    PL/SQL procedure successfully completed

     

    SQL>

    SQL> BEGIN

      2  HelloWorld3('Edward');

      3  HelloWorld3('Edward', p_val1 => ' Good Night,');

      4  HelloWorld3('Edward', p_val1 => ' Good Night,', p_val2 => 'Bye');

      5  HelloWorld3('Edward', p_val2 => ' HeiHei ');

      6  END;

      7  /

     

    Hello Edward Good Moning, Nice to Meet you!

    Hello Edward Good Night, Nice to Meet you!

    Hello Edward Good Night,Bye!

    Hello Edward Good Moning, HeiHei !

     

    PL/SQL procedure successfully completed

     

    SQL>

    存储过程练习:

    Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

    Connected as C##scott@ORCL

     

    在存储过程界面添加存储过程:

    create or replace procedure update_emp

     as

     begin

      update C##scott.emp set ename='candy1' where empno=7876;

    end update_emp;

     

    SQL> begin

      2      update_emp;

      3   end;

      4  /

     

    PL/SQL procedure successfully completed

     

    第一次存储过程执行完毕,查询执行结果发现7876编号名称改为candy:

    SQL> select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 candy      CLERK      7788 0087/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    二次单独查询7876编号,发现又被改回去了:

    SQL> select * from emp where empno = 7876;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

     

    SQL> select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    再次执行存储过程:

    SQL>

    SQL> begin

      2      update_emp;

      3   end;

      4  /

     

    PL/SQL procedure successfully completed

     

     

    SQL> select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 candy1     CLERK      7788 0087/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    再次执行二次查询:

    SQL> select * from emp where empno = 7876;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

     

    删除存储过程:

    SQL> DROP PROCEDURE update_emp;

     

    Procedure dropped

    数据库实验第十

    创建触发器(在删除xs表中的数据时进行备份):

    create or replace trigger del_xs

      before delete on xs

       for each row

      begin

        insert into  xs_2 (xh,xm,zym,xb,cssj,zxf)  values            (:old.xh,:old.xm, :old.zym, :old.xb, :old.cssj,:old.zxf);

      end del_xs;

     

     

    SQL> select * from xs;

     

    XH     XM     ZYM    XB CSSJ        ZXF BZ

    ------ ------ ------ -- ----------- --- --------------------

    061101 王林   计算机 男 1986/2/10    50

    101112 李明   计算机 男 1986/1/30    36

    001    张琼   计算机                 45 三好学生

    121112 王小二 计算机 男 1986/1/30    36

     

    SQL> delete from xs where xh=001;

     

    1 row deleted

     

    此时该行数据将会在新创建的表中找到。

     

     

    将对xs表的操作记录(insert,delete,update)在sql_info表中记录下来:

    SQL> select * from sql_info;

     

    INFO       TIME

    ---------- -----------

     

    SQL> insert into xs(xh,xm,zym) values ('002','hello','nan');

     

    1 row inserted

     

    SQL> select * from sql_info;

     

    INFO       TIME

    ---------- -----------

    插入       2020/12/11

     

    create or replace trigger t2

      after delete or insert or update on xs

      for each row

       declare

           v_info sql_info.info%type;

         begin

              if inserting then      

                   v_info:='插入';

              elsif updating then

                   v_info:='更新';

              else   

                   v_info:='删除';

              end if;

              insert INTO SQL_INFO  VALUES(v_info,sysdate);

      end t2;

     

    创建一个触发器,输出对scott.emp表的操作(插入后员工号和员工名,更新后员工工资,删除的员工号和员工名。):

    CREATE OR REPLACE TRIGGER  t3

    BEFORE INSERT OR UPDATE OR DELETE ON scott.emp

    FOR EACH ROW

    BEGIN

       IF INSERTING THEN

             DBMS_OUTPUT.PUT_LINE(:new.empno||' '||:new.ename);

       ELSIF UPDATING THEN

             DBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);

       ELSE

             DBMS_OUTPUT.PUT_LINE(:old.empno||' '|| :old.ename);

       END IF;

    END t3;

     

    SQL> set serveroutput on;

    SQL>

    SQL> declare

      2  begin

      3      update scott.emp set empno=7521 where empno=7522;

      4    commit;

      5  end;

      6  /

     

    更新记录后平均工资为 2073.21

     

     

    针对Scott.emp表,记录其相应操作的信息,具体如下:

    当执行插入操作时,统计操作后员工人数;当执行更新工资操作时,统计更新后员工平均工资;当执行删除操作时,统计删除后各个部门剩余的人数(游标):

    CREATE OR REPLACE TRIGGER t4   

        AFTER INSERT OR UPDATE OR DELETE

          ON  scott.emp

    declare

       v_1  number;  v_2  scott.emp.sal%type;

    begin

       if inserting then

             select count(*)  into  v_1 from scott.emp;

             DBMS_OUTPUT.PUT_LINE('添加记录后总人数为'||v_1);

       elsif updating then

              select avg(sal)  into v_2  from scott.emp;

              DBMS_OUTPUT.PUT_LINE('更新记录后平均工资为'||' '||v_2);

       else

             for v_s in (select  deptno,count(*)  num   from  scott.emp  group by deptno)

             loop

              DBMS_OUTPUT.PUT_LINE('删除记录后各个部门的部门号和人数为' ||v_s.deptno||' '||v_s.num);

         end  loop;

      end if;

      end t4;

     

    SQL> delete from scott.emp where hiredate<=to_date('1980-12-17','yyyy-mm-dd');

     

    7369 SMITH

    删除记录后各个部门的部门号和人数为30 6

    删除记录后各个部门的部门号和人数为20 4

    删除记录后各个部门的部门号和人数为10 3

     

    1 row deleted

     

    数据库实验第十

    创建一个存储过程,以部门号为该存储过程的in类型参数,查询该部门的平均工资,并输出该部门中比平均工资高的员工号、员工名。(for 循环遍历  游标)

    1. CREATE OR REPLACE PROCEDURE show_emp(  
    2. p_deptno emp.deptno%TYPE)  
    3. AS  
    4.   v_sal emp.sal%TYPE;  
    5. BEGIN  
    6. SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;  
    7. DBMS_OUTPUT.PUT_LINE(p_deptno||' '||'average salary is:'||v_sal);  
    8.   
    9. FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND sal>v_sal)  
    10. LOOP  
    11.     DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);  
    12. END LOOP;  
    13. END show_emp;  

    SQL> set serverout on;

    SQL> begin

      2    show_emp(20);

      3  end;

      4  /

     

    20 average salary is:2175

    7566 JONES

    7788 SCOTT

    7902 FORD

     

    PL/SQL procedure successfully completed

     

    用存储过程进行模糊查找,如查找ename中包含L的雇员信息

    1. create or replace procedure Tp1  
    2. (varEmpName emp.ename%type)  
    3. is  
    4.    --cursor c_1 is select * from scott.emp where ename like '%'||varEmpName||'%';  
    5.  begin  
    6.     for v_1 in (select * from emp where ename like '%'||varEmpName||'%')  
    7.       loop  
    8.       dbms_output.put_line(v_1.empno||'  '||v_1.ename||'  '||v_1.job||'  '||v_1.deptno);  
    9.     end loop;  
    10. end;  

    SQL> begin

      2     tp1('L');

      3  end;

      4  /

     

    7499  ALLEN  SALESMAN  30

    7698  BLAKE  MANAGER  30

    7782  CLARK  MANAGER  10

    7934  MILLER  CLERK  10

     

    PL/SQL procedure successfully completed

     

    rownum练习:

     

    SQL> select * from xs;

     

    XH     XM     ZYM    XB CSSJ        ZXF BZ

    ------ ------ ------ -- ----------- --- --------------------

    061101 王林   计算机 男 1986/2/10    50

    101112 李明   计算机 男 1986/1/30    36

    001    张琼   计算机                 45 三好学生

    121112 王小二 计算机 男 1986/1/30    36

     

    SQL> select * from xs where rownum<=2;

     

    XH     XM     ZYM    XB CSSJ        ZXF BZ

    ------ ------ ------ -- ----------- --- --------------------

    061101 王林   计算机 男 1986/2/10    50

    101112 李明   计算机 男 1986/1/30    36

     

    SQL> select deptno,count(*) PersonNum,avg(sal)     avgsal from scott.emp

      2  group by deptno

      3  order by PersonNum desc;

     

    DEPTNO  PERSONNUM     AVGSAL

    ------ ---------- ----------

        30          6 1566.66666

        20          5       2175

    10          3 2916.66666

    注意上述查询在使用rownum时不能直接写成如下格式:

    SQL> select deptno,count(*) PersonNum,avg(sal)     avgsal from scott.emp

      2  group by deptno

      3  order by PersonNum desc

    1.  where rownum<=1;

    ORA-00933: SQL 命令未正确结束

    否则会报ORA-00933: SQL 命令未正确结束错误。

     

    正确做法:

    再写一层查询嵌套内部查询:

    SQL> select * from(

      2  select deptno,count(*) PersonNum,avg(sal) avgsal from emp

      3   group by deptno

      4   order by PersonNum desc)

      5   where rownum<=1;

     

    DEPTNO  PERSONNUM     AVGSAL

    ------ ---------- ----------

        30          6 1566.66666

    SQL>

    补充:见存储过程需要注意的问题,异常处理与触发器补充

     

    补充内容

    循环结构:

    求10的阶乘:

     

    一、

    LOOP

    Exit when...

    END LOOP;

     

    二、

    WHILE

    LOOP

    END LOOP;

     

    三、

    FOR count IN count_1...count_n

    LOOP

    END LOPP;

     

    For循环中的逆序:

    for n in reverse 1..10

     

     

     

    一、

    LOOP

    Exit when...

    END LOOP;

     

    Command:

    SEt SERVEROUTPUT on;

    DECLARE

               s NUMBER:=1;

          n  NUMBER:=2;

      BEGIN

        LOOP

                s:=s*n;

                n:=n+1;

                exit when n>10;

        END LOOP;

        dbms_output.put_line(to_char(s));

      END;

    /

     

    实现:

    SQL> SEt SERVEROUTPUT on;

    SQL> DECLARE

      2             s NUMBER:=1;

      3        n  NUMBER:=2;

      4    BEGIN

      5      LOOP

      6              s:=s*n;

      7              n:=n+1;

      8              exit when n>10;

      9      END LOOP;

     10      dbms_output.put_line(to_char(s));

     11    END;

     12  /

    3628800

    PL/SQL procedure successfully completed

     

     

    二、

    WHILE

    LOOP

    END LOOP;

     

    Command:

    DECLARE

               s NUMBER:=1;

    n NUMBER:=2;

    BEGIN

        while n<=10

                     LOOP

            s:=s*n;

            n:=n+1;

     end LOOP;

    dbms_output.put_line(to_char(s));

    END;

     

    实现:

    SQL> DECLARE

      2             s NUMBER:=1;

      3   n NUMBER:=2;

      4   BEGIN

      5       while n<=10

      6                   LOOP

      7           s:=s*n;

      8           n:=n+1;

      9    end LOOP;

     10   dbms_output.put_line(to_char(s));

     11   END;

     12  /

     

    3628800

     

    PL/SQL procedure successfully completed

     

    三、

    FOR count IN count_1...count_n

    LOOP

    END LOPP;

     

    Command:

    DECLARE

               s NUMBER:=1;

       n NUMBER:=2;

    BEGIN

    for n in 2..10

                     Loop

          s:=s*n;

    end loop;

    dbms_output.put_line(to_char(s));

    END;

     

    实现:

    SQL> DECLARE

      2             s NUMBER:=1;

      3      n NUMBER:=2;

      4   BEGIN

      5   for n in 2..10

      6                   Loop

      7         s:=s*n;

      8   end loop;

      9   dbms_output.put_line(to_char(s));

     10   END;

     11  /

     

    3628800

     

    PL/SQL procedure successfully completed

     

    For循环中的逆序:

    for n in reverse 1..10

     

    Command:

    DECLARE

         s NUMBER:=1;

       n NUMBER:=2;

     begin

      for n in reverse 1..10

    loop

          s:=s*n;

          dbms_output.put_line(to_char(n));

          end loop;

    dbms_output.put_line(to_char(s));

    END;

     

    实现:

    SQL> DECLARE

      2       s NUMBER:=1;

      3      n NUMBER:=2;

      4   begin

      5    for n in reverse 1..10

      6   loop

      7        s:=s*n;

      8        dbms_output.put_line(to_char(n));

      9        end loop;

     10   dbms_output.put_line(to_char(s));

     11   END;

     12  /

     

    10

    9

    8

    7

    6

    5

    4

    3

    2

    1

    3628800

     

    PL/SQL procedure successfully completed

     

    水仙花数:

     

    Command:

    declare

           i int;a int;b int;c int;

    begin  

       for i in 100..999

        loop

           a:= trunc(i/100);

           b:=trunc(i/10) mod 10;

           c:=i mod 10;

          -- dbms_output.put_line(a||' '||b||' '||c);

           if (i=a*a*a+b*b*b+c*c*c) then

             dbms_output.put_line(i);

           end if;

        end loop;   

    end;

    /

     

    实现:

    SQL> set serveroutput on;

    SQL> declare

      2         i int;a int;b int;c int;

      3  begin

      4     for i in 100..999

      5      loop

      6         a:= trunc(i/100);

      7         b:=trunc(i/10) mod 10;

      8         c:=i mod 10;

      9        -- dbms_output.put_line(a||' '||b||' '||c);

     10         if (i=a*a*a+b*b*b+c*c*c) then

     11           dbms_output.put_line(i);

     12         end if;

     13      end loop;

     14  end;

     15  /

     

    153

    370

    371

    407

     

    PL/SQL procedure successfully completed

     

    触发器补充:

    设置系统触发器:

    创建logon触发器,在登录的时候进行记录:

    首先创建保存记录的表:

    SQL>  create table u_1

      2           (    username varchar2(50),

      3                activity  varchar2(20),

      4                 time date

      5            )

      6  ;

     

    Table created

     

    SQL>  select  username,activity,to_char(time,'yyyy-MM-dd HH24:mi') from u_1;

     

    USERNAME                                           ACTIVITY             TO_CHAR(TIME,'YYYY-MM-DDHH24:MI')

    -------------------------------------------------- -------------------- ---------------------------------

    C##SCOTT                                           LOGON                2020-12-19 23:41

    C##SCOTT                                           LOGON                2020-12-19 23:41

    C##SCOTT                                           LOGON                2020-12-19 23:41

    SYS                                                LOGON                2020-12-19 23:41

    SYS                                                LOGON                2020-12-19 23:41

    SYSTEM                                             LOGON                2020-12-19 23:42

    SYSTEM                                             LOGON                2020-12-19 23:42

    SYSTEM                                             LOGON                2020-12-19 23:42

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

    SYS                                                LOGON                2020-12-20 10:09

     

    USERNAME                                           ACTIVITY             TO_CHAR(TIME,'YYYY-MM-DDHH24:MI')

    -------------------------------------------------- -------------------- ---------------------------------

    SYSTEM                                             LOGON                2020-12-20 10:12

    SYSTEM                                             LOGON                2020-12-20 10:12

    SYSTEM                                             LOGON                2020-12-20 10:12

     

    23 rows selected

     

    创建logoff触发器,在退出登录的时候进行记录(注意此时的触发器时间上不要采用after,因为采用after客户机注销进程终止,不能进行其他操作):

    首先创建保存记录的表:

    SQL>  create table u_2

      2           (    username varchar2(50),

      3                activity  varchar2(20),

      4                 time date

      5            )

      6  ;

     

    Table created

     

    SQL>

    将触发器的after改成before:

    create or replace trigger st2

         before logoff on database

     begin

           insert into u_2   values(user,'LOGOFF',sysdate);

     END  st2;

     

    SQL> select  username,activity,to_char(time,'yyyy-MM-dd HH24:mi') from u_2;

     

    USERNAME                                           ACTIVITY             TO_CHAR(TIME,'YYYY-MM-DDHH24:MI')

    -------------------------------------------------- -------------------- ---------------------------------

    退出后查询表:

    SQL> select  username,activity,to_char(time,'yyyy-MM-dd HH24:mi') from u_2;

     

    USERNAME                                           ACTIVITY             TO_CHAR(TIME,'YYYY-MM-DDHH24:MI')

    -------------------------------------------------- -------------------- ---------------------------------

    SYSTEM                                             LOGOFF               2020-12-20 10:42

    SYSTEM                                             LOGOFF               2020-12-20 10:42

    SYSTEM                                             LOGOFF               2020-12-20 10:42

     

    查询sysdate并转换为char格式输出:

    SQL> Select to_char(sysdate,'yyyy-MM-dd HH24:mi')  from dual;

     

    TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI')

    ------------------------------------

    2020-12-20 11:06

     

    SQL> Select to_char(sysdate, 'DAY')  from dual;

     

    TO_CHAR(SYSDATE,'DAY')

    ----------------------

    星期日

     

    SQL>

    Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

    Connected as C##scott

     

    SQL> select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 candy1     CLERK      7788 0087/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    设置触发器:作用为禁止在休息日(周六、周天)改变scott.emp雇员信息(包括添加删除和修改)。

     

    create or replace trigger tr_sec_emp

    before insert or update or delete on emp

    begin

      if to_char(sysdate, 'DAY') in ('星期六','星期日') then

        raise_application_error(-20001,'不能在休息日修改员工信息');

      end if;

    end;

     

    SQL> update emp set ename='candy'  where empno=7876;

    update emp set ename='candy'  where empno=7876

     

    ORA-20001: 不能在休息日修改员工信息

    ORA-06512: 在 "C##SCOTT.TR_SEC_EMP", line 3

    ORA-04088: 触发器 'C##SCOTT.TR_SEC_EMP' 执行过程中出错

    上述不能执行的同时给出了相关触发器和触发器代码限制的所在行数。

     

    关闭相应触发器:

    SQL> Alter trigger tr_sec_emp disable;

     

    Trigger altered

     

     

    SQL> update emp set ename='candy'  where empno=7876;

     

    1 row updated

     

     

    SQL> select * from emp where empno=7876;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7876 candy      CLERK      7788 0087/5/23     1100.00               20

     

    打开触发器后则又不能在规定范围内进行修改:

    SQL> Alter trigger tr_sec_emp enable;

     

    Trigger altered

     

     

    SQL>  update emp set ename='candytest'  where empno=7876;

    update emp set ename='candytest'  where empno=7876

     

    ORA-20001: 不能在休息日修改员工信息

    ORA-06512: 在 "C##SCOTT.TR_SEC_EMP", line 3

    ORA-04088: 触发器 'C##SCOTT.TR_SEC_EMP' 执行过程中出错

     

    启动和关闭触发器

    Alter trigger 触发器名字  disable;

    Alter trigger  触发器名字  enable;

    关闭某表的所有触发器   

    alter   table   表名字  disable   all  triggers ;    

    开启所有触发器   

    alter   table   表名字   enable  all triggers ; 

     

    存储过程需要注意的一些问题

    存储过程要注意权限问题,很有可能权限不足,比如有时候在system用户下对scott的表进行操作等可能会出现权限问题,这个时候可以退出system用户(或者退出当前用户切换或者重新打开一个oracle进程登录(一台机器可允许多个oracle用户登录)),然后重新编译关于scott的存储过程即可编译成功。(即使有时候在system用户下可以编译运行也不是永久的修改,而只是临时的修改,再次运行程序可能会发现其中的数据并没有修改,只是在存储过程执行的后一次实现了临时性的结果展示,并没有对数据进行永久的替换保存,这一点需要注意。)

    例一(权限不足):

     

    例二(临时性结果展示,未保存到数据库内部):

    在存储过程界面添加存储过程:

    create or replace procedure update_emp

     as

     begin

      update C##scott.emp set ename='candy1' where empno=7876;

    end update_emp;

     

    SQL> begin

      2      update_emp;

      3   end;

      4  /

     

    PL/SQL procedure successfully completed

     

    第一次存储过程执行完毕,查询执行结果发现7876编号名称改为candy:

    SQL> select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 candy1      CLERK      7788 0087/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    二次单独查询7876编号,发现又被改回去了:

    SQL> select * from emp where empno = 7876;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

     

    SQL> select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    再次执行存储过程:

    SQL>

    SQL> begin

      2      update_emp;

      3   end;

      4  /

     

    PL/SQL procedure successfully completed

     

     

    SQL> select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 candy1     CLERK      7788 0087/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    再次执行二次查询:

    SQL> select * from emp where empno = 7876;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7876 ADAMS      CLERK      7788 0087/5/23     1100.00               20

     

    删除存储过程:

    SQL> DROP PROCEDURE update_emp;

     

    Procedure dropped

     

    例三(切换到scott用户之后不再会出现上述两个例子的问题,权限OK,编译compiled successful,执行程序的数据永久保存到数据库中,不会再产生临时性的错误):

     

    SQL> begin

      2    update_emp;

      3  end;

      4  /

     

    PL/SQL procedure successfully completed

     

    SQL>  select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 candy1      CLERK      7788 1987/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    SQL>  select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 candy1     CLERK      7788 1987/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    SQL>  select * from emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 candy1     CLERK      7788 1987/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

    SQL> select * from emp where ename='candy1';

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7876 candy1     CLERK      7788 1987/5/23     1100.00               20

     

     

    SQL> DROP PROCEDURE update_emp;

     

    Procedure dropped

     

    异常处理(系统异常处理以及自定义异常处理)

    Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

    Connected as system@ORCL

     

    SQL> select * from C##Scott.Emp;

     

    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

    ----- ---------- --------- ----- ----------- --------- --------- ------

     7369 SMITH      CLERK      7902 1980/12/17     800.00               20

     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

     7788 SCOTT      ANALYST    7566 0087/4/19     3000.00               20

     7839 KING       PRESIDENT       1981/11/17    5000.00               10

     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

     7876 candy1     CLERK      7788 0087/5/23     1100.00               20

     7900 JAMES      CLERK      7698 1981/12/3      950.00               30

     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

     

    14 rows selected

     

     

    SQL>

    SQL> declare

      2     v_1  number;

      3  begin

      4     select empno into v_1 from C##Scott.emp;

      5     dbms_output.put_line(v_1);

      6  exception

      7    when no_data_found then

      8        dbms_output.put_line('出现no_data_found异常了');

      9      when too_many_rows then

     10        dbms_output.put_line('出现too_many_rows异常了');

     11     when others then

     12        dbms_output.put_line('出现others异常了');

     13  end;

     14  /

    出现too_many_rows异常了

     

    PL/SQL procedure successfully completed

     

     

    SQL>

    SQL> declare

      2     v_1  number;

      3  begin

      4     select empno into v_1 from C##Scott.emp where ename = 'test_no_data_found';

      5     dbms_output.put_line(v_1);

      6  exception

      7    when no_data_found then

      8        dbms_output.put_line('出现no_data_found异常了');

      9      when too_many_rows then

     10        dbms_output.put_line('出现too_many_rows异常了');

     11     when others then

     12        dbms_output.put_line('出现others异常了');

     13  end;

     14  /

    出现no_data_found异常了

     

    PL/SQL procedure successfully completed

     

     

    PL/SQL中打印错误信息:dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

    SQL> declare

      2     v_1  number;

      3  begin

      4     select empno into v_1 from C##Scott.emp where ename = 'test_no_data_found';

      5     dbms_output.put_line(v_1);

      6  exception

      7     when no_data_found then

      8        dbms_output.put_line('出现no_data_found异常了');

      9        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     10     when too_many_rows then

     11        dbms_output.put_line('出现too_many_rows异常了');

     12     when others then

     13        dbms_output.put_line('出现others异常了');

     14  end;

     15  /

    出现no_data_found异常了

    错误代码: 100 错误信息: ORA-01403: 未找到任何数据

     

    PL/SQL procedure successfully completed

     

     

    SQL>

    SQL> declare

      2     v_1  number;

      3  begin

      4     select empno into v_1 from C##Scott.emp;

      5     dbms_output.put_line(v_1);

      6  exception

      7     when no_data_found then

      8        dbms_output.put_line('出现no_data_found异常了');

      9        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     10     when too_many_rows then

     11        dbms_output.put_line('出现too_many_rows异常了');

     12        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     13     when others then

     14        dbms_output.put_line('出现others异常了');

     15        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     16  end;

     17  /

    出现too_many_rows异常了

    错误代码: -1422 错误信息: ORA-01422: 实际返回的行数超出请求的行数

     

    PL/SQL procedure successfully completed

     

     

    出现代码异常抛出时不再执行下面的语句,直接跳转到异常处理(其中用户自定义异常错误代码为1,错误信息为User-Defined Exception):

    SQL> declare

      2     v_1  number;

      3     e_1  exception;

      4     e_2  exception;

      5  begin

      6     select empno into v_1 from C##Scott.emp where ename = 'candy1';

      7     dbms_output.put_line(v_1);

      8     if v_1 > 7600 then

      9       raise e_1;

     10     else

     11       raise e_2;

     12     end if;

     13  exception

     14     when e_1 then

     15        dbms_output.put_line('test自定义异常,这里以raise代替throws,超过7600号');

     16        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     17     when e_2 then

     18        dbms_output.put_line('test自定义异常,这里以raise代替throws,超过7500号');

     19        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     20     when others then

     21        dbms_output.put_line('出现others异常了');

     22        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     23  end;

     24  /

    7876

    test自定义异常,这里以raise代替throws,超过7600号

    错误代码: 1 错误信息: User-Defined Exception

     

    PL/SQL procedure successfully completed

     

     

    SQL>

    SQL> declare

      2     v_1  number;

      3     e_1  exception;

      4     e_2  exception;

      5  begin

      6     select empno into v_1 from C##Scott.emp where ename = 'WARD';

      7     dbms_output.put_line(v_1);

      8     if v_1 > 7600 then

      9       raise e_1;

     10     else

     11       raise e_2;

     12     end if;

     13  exception

     14     when e_1 then

     15        dbms_output.put_line('test自定义异常,这里以raise代替throws,超过7600号');

     16        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     17     when e_2 then

     18        dbms_output.put_line('test自定义异常,这里以raise代替throws,超过7500号');

     19        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     20     when others then

     21        dbms_output.put_line('出现others异常了');

     22        dbms_output.put_line('错误代码: '||sqlcode||' 错误信息: '||sqlerrm);

     23  end;

     24  /

    7521

    test自定义异常,这里以raise代替throws,超过7500号

    错误代码: 1 错误信息: User-Defined Exception

     

    PL/SQL procedure successfully completed

     

     

    SQL>

     

    展开全文
  • 广州大学学生实验报告 开课学院及实验室:计算机学院 2020年12月24日 学院 计算机学院 年级、专业、班级 网络192 姓名 林泽佳 学号 19062000** 实验课程名称 数据库原理实验 成绩 实验项目名称...

    广州大学学生实验报告

    开课学院及实验室:计算机学院 2020年12月24日

    学院 计算机学院 年级、专业、班级 网络192
    姓名 zoea 学号 19062000**
    实验课程名称 数据库原理实验 成绩
    实验项目名称 数据库系统设计综合实验 指导老师 ***

    一、预备知识

    本实验的任务是设计并实现一个数据库系统。数据库设计的一般步骤包括:需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护。

    (1) 概念结构设计

    了解概念结构设计的基本方法,根据需求分析的结果或实验题目给出的要求,能够准确地用实体联系图来描述实体和实体之间的联系。

    (2)逻辑结构设计

    理解逻辑结构设计的基本方法,根据实体联系图的设计,转换成合理的关系模式,每个关系模式至少应该满足第三范式的要求。

    (3) 物理结构设计

    理解物理结构设计的基本方法,选择合理的索引结构和存储结构,优化数据库的存取。

    (4)数据库实施

    选择一门熟悉的面向对象程序设计语言,完成应用程序的开发。

    二、实验目的

    通过实验,使学生掌握数据库系统设计和开发的一般方法,能够设计并实现简单的数据库系统。

    三、实验要求

    熟悉实验室实验环境,掌握实验预备知识,了解实验中故障排除的基本方法。实验中根据实验内容和步骤完成相应的任务,并独立完成实验报告。实验素材可选择数据库原理实验七素材,也可以自行选题,选题要求有一定的难度,设计的数据库系统至少要有5张表。

    四、实验环境

    操作系统:Windows10

    数据库管理系统:MySQL 5.7

    java库:JDK1.8

    web服务器:Tomcat 9.0.37

    项目结构:Maven 3.6.3

    开发工具:Navicat、Intellij IDEA 2020.1

    五、实验内容和步骤

    1.实验题目

    假设有“教师”、“学生”、“课程”三个实体,教师的基本信息包括:工号、姓名、职称、工资,课程的基本信息包括:课程号、课程名、学分数,学生的基本信息包括:学号、姓名、性别、年龄。系统必须满足以下要求:

    (1) 一门课程只能有一个教师任课,一个教师可以上多门课程;

    (2) 一个学生可以选修多门课程,一门课程可以由多个学生来选修,记录不同学生选修不同课程的成绩;

    (3) 设置一个管理员,用于维护(添加、删除和修改等基本任务)学生基本信息、教师基本信息和教师所授课程等工作,此外,管理员添加学生时,为其设置初始密码;当学生选修了某门课程,课程成绩由管理员录入;

    (4) 学生可以利用学号和密码登录系统,登陆系统后,可以进行选课、修改密码和个人基本信息、查询自己的选课及总学分等操作;

    (5) 能够统计不同职称的教师的数量、不同职称的教师的平均工资,可以统计每门课程的平均成绩、最高分、最低分,统计每个学生选修课程的总学分;

    根据上述描述,解答下列问题:

    (1) 设计并画出E-R 图,要求标注连通词(即联系类型);

    (2) 将E-R 图转化为关系模型,并指出各关系的主码和外码;

    (3) 在MySql、SQL Server、Oracle 中选择一个数据库管理系统,并完成数据库的逻辑设计;

    2.大致的需求

    image-20201231223536872

    3.数据库表的E-R图

    image-20201231230817404

    image-20201231230904924

    image-20201231230942259

    关系模型

    student(sid,sname,gender,age,uid,pwd)中sid为主码;

    course(cid,cname,credit)中cid为主码

    teacher(tid,tname,post,salary)中tid为主码

    tc(tid,cid)中tid,cid为主码,tid为外码引用teacher的tid,cid为外码引用course的cid

    sc(sid,cid)中sid,cid为主码,sid为外码引用student的sid,cid为外码引用course的cid

    4.创建数据库和表

    新建一个数据库

    image-20201226102846963

    建表:目前需要六张表,即:教师表、学生表、课程表、教师课程表、学生课程表、管理员表:

    image-20201226103039015

    源代码:

    CREATE TABLE teacher(
    		tid INT(16) PRIMARY KEY,
    		tname VARCHAR(11) NOT NULL,
    		post VARCHAR(11),#职位
    		salary DOUBLE(11, 3)
    );
    CREATE TABLE student(
    		sid INT(16) PRIMARY KEY,
    		sname VARCHAR(11) NOT NULL,
    		gender VARCHAR(1),
    		age INT(2),
    		uid VARCHAR(20),
    		pwd VARCHAR(20)#密码
    );
    CREATE TABLE course(
    		cid INT(16) PRIMARY KEY,
    		cname VARCHAR(11) NOT NULL,
    		credit DOUBLE(2,1)#学分
    );
    CREATE TABLE tc(
    		tid INT(16),
    		#一个课程只有一个老师,采用UNIQUE约束
    		cid INT(16) UNIQUE,
        FOREIGN KEY(tid) REFERENCES teacher(tid),
        FOREIGN KEY(cid) REFERENCES course(cid),		
    		PRIMARY KEY(tid,cid)
    );
    CREATE TABLE sc(
    		sid INT(16),
    		cid INT(16),
    		grade DOUBLE(4,1),#学生该课程的成绩
    		FOREIGN KEY(sid) REFERENCES student(sid),
    		FOREIGN KEY(cid) REFERENCES course(cid),
    		PRIMARY KEY(sid,cid)
    );
    CREATE TABLE manager(
        mid INT(16) PRIMARY KEY,
    		pwd VARCHAR(20)
    );
    

    5.创建SSM项目

    1.创建Maven项目

    image-20201226103624773

    image-20201226103652332

    添加web支持:

    image-20201226105558465

    IDEA连接数据库:

    image-20201226162429255

    连接成功

    image-20201226162836580

    6.项目结构

    主要采用MVC结构

    image-20201231232312886

    根据该结构创建的目录:

    image-20201231232423035

    7.相关配置文件的编写

    依赖相关

    首先是pom.xml导入相关依赖,以及解决Maven资源过滤的问题

    <?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>org.example</groupId>
        <artifactId>DBProject</artifactId>
        <version>1.0-SNAPSHOT</version>
    
        <dependencies>
            <!--Junit-->
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>
            <!--数据库驱动-->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.47</version>
            </dependency>
            <!-- 数据库连接池 -->
            <dependency>
                <groupId>com.mchange</groupId>
                <artifactId>c3p0</artifactId>
                <version>0.9.5.2</version>
            </dependency>
    
            <!--Servlet - JSP -->
            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>servlet-api</artifactId>
                <version>2.5</version>
            </dependency>
            <dependency>
                <groupId>javax.servlet.jsp</groupId>
                <artifactId>jsp-api</artifactId>
                <version>2.2</version>
            </dependency>
            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>jstl</artifactId>
                <version>1.2</version>
            </dependency>
    
            <!--Mybatis-->
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.5.2</version>
            </dependency>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis-spring</artifactId>
                <version>2.0.2</version>
            </dependency>
    
            <!--Spring-->
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-webmvc</artifactId>
                <version>5.1.9.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.1.9.RELEASE</version>
            </dependency>
        </dependencies>
    
        <!--解决Maven资源过滤问题-->
        <build>
            <resources>
                <resource>
                    <directory>src/main/java</directory>
                    <includes>
                        <include>**/*.properties</include>
                        <include>**/*.xml</include>
                    </includes>
                    <filtering>false</filtering>
                </resource>
                <resource>
                    <directory>src/main/resources</directory>
                    <includes>
                        <include>**/*.properties</include>
                        <include>**/*.xml</include>
                    </includes>
                    <filtering>false</filtering>
                </resource>
            </resources>
        </build>
    </project>
    

    Spring相关配置

    spring-dao.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xmlns:context="http://www.springframework.org/schema/context"
           xmlns:mvc="http://www.springframework.org/schema/mvc"
           xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/aop
            http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd">
        <!-- 配置整合mybatis -->
        <!-- 1.关联数据库文件 -->
        <context:property-placeholder location="classpath:jdbc.properties"/>
    
        <!-- 2.数据库连接池 -->
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <!-- 配置连接池属性 -->
            <property name="driverClass" value="${jdbc.driver}"/>
            <property name="jdbcUrl" value="${jdbc.url}"/>
            <property name="user" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
    
            <!-- c3p0连接池的私有属性 -->
            <property name="maxPoolSize" value="30"/>
            <property name="minPoolSize" value="10"/>
            <!-- 关闭连接后不自动commit -->
            <property name="autoCommitOnClose" value="false"/>
            <!-- 获取连接超时时间 -->
            <property name="checkoutTimeout" value="10000"/>
            <!-- 当获取连接失败重试次数 -->
            <property name="acquireRetryAttempts" value="2"/>
        </bean>
    
        <!-- 3.配置SqlSessionFactory对象 -->
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <!-- 注入数据库连接池 -->
            <property name="dataSource" ref="dataSource"/>
            <!-- 配置MyBaties全局配置文件:mybatis-config.xml -->
            <property name="configLocation" value="classpath:mybatis-config.xml"/>
        </bean>
    
        <!-- 4.配置扫描Dao接口包,动态实现Dao接口注入到spring容器中 -->
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <!-- 注入sqlSessionFactory -->
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
            <!-- 给出需要扫描Dao接口包 -->
            <property name="basePackage" value="com.deserts.dao"/>
        </bean>
    </beans>
    

    spring-mvc.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xmlns:context="http://www.springframework.org/schema/context"
           xmlns:mvc="http://www.springframework.org/schema/mvc"
           xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/aop
            http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd">
    
        <!-- 配置SpringMVC -->
        <!-- 1.开启SpringMVC注解驱动 -->
        <mvc:annotation-driven />
        <!-- 2.静态资源默认servlet配置-->
        <mvc:default-servlet-handler/>
    
        <!-- 3.配置jsp 显示ViewResolver视图解析器 -->
        <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
            <property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
            <property name="prefix" value="" />
            <property name="suffix" value=".jsp" />
        </bean>
    
        <!-- 4.扫描web相关的bean -->
        <context:component-scan base-package="com.deserts.controller" />
    </beans>
    

    spring-service.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xmlns:context="http://www.springframework.org/schema/context"
           xmlns:mvc="http://www.springframework.org/schema/mvc"
           xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/aop
            http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd">
        <!-- 扫描service相关的bean -->
        <context:component-scan base-package="com.deserts.service" />
        
        <!-- 配置事务管理器 -->
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <!-- 注入数据库连接池 -->
            <property name="dataSource" ref="dataSource" />
        </bean>
    
    </beans>
    

    整合三个文件applicationContext.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xmlns:context="http://www.springframework.org/schema/context"
           xmlns:mvc="http://www.springframework.org/schema/mvc"
           xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/aop
            http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd">
        <import resource="classpath:spring-dao.xml"/>
        <import resource="classpath:spring-service.xml"/>
        <import resource="classpath:spring-mvc.xml"/>
    </beans>
    

    数据库相关的配置

    连接数据库jdbc.properties

    jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/student_system
    jdbc.username=root
    jdbc.password=073838
    

    MyBatis配置文件mybatis-config.xml

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <properties resource="jdbc.properties"/>
    
       <typeAliases>
           <typeAlias type="com.deserts.pojo.Student" alias="student"/>
           <typeAlias type="com.deserts.pojo.Course" alias="course"/>
           <typeAlias type="com.deserts.pojo.Teacher" alias="teacher"/>
           <typeAlias type="com.deserts.pojo.Manager" alias="manager"/>
           <typeAlias type="com.deserts.pojo.Data" alias="data"/>
       </typeAliases>
      <!--  <mappers>
            <mapper resource="com."/>
        </mappers>-->
    
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            <mapper resource="mapper/StudentMapper.xml"/>
            <mapper resource="mapper/ManagerMapper.xml"/>
            <mapper resource="mapper/DataMapper.xml"/>
        </mappers>
    </configuration>
    

    web相关

    web.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
             version="4.0">
      <!--DispatcherServlet-->
      <servlet>
        <servlet-name>DispatcherServlet</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
          <param-name>contextConfigLocation</param-name>
          <param-value>classpath:applicationContext.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
      </servlet>
      <servlet-mapping>
        <servlet-name>DispatcherServlet</servlet-name>
        <url-pattern>/</url-pattern>
      </servlet-mapping>
      
      <!--encodingFilter-->
      <filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class>
          org.springframework.web.filter.CharacterEncodingFilter
        </filter-class>
        <init-param>
          <param-name>encoding</param-name>
          <param-value>utf-8</param-value>
        </init-param>
      </filter>
      <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
      </filter-mapping>
      <!--Session过期时间-->
      <session-config>
        <session-timeout>15</session-timeout>
      </session-config>
    </web-app>
    

    日志相关配置

    log4j.properties

    log4j.rootLogger=DEBUG, Console
    #Console
    log4j.appender.Console=org.apache.log4j.ConsoleAppender
    log4j.appender.Console.layout=org.apache.log4j.PatternLayout
    log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
    log4j.logger.java.sql.ResultSet=INFO
    log4j.logger.org.apache=INFO
    log4j.logger.java.sql.Connection=DEBUG
    log4j.logger.java.sql.Statement=DEBUG
    log4j.logger.java.sql.PreparedStatement=DEBUG
    
    

    8.功能实现的过程

    业务模型

    主要表现为实体类怎么与数据库的表建立相应的联系,我主要用三个类来对应student、course、teacher三张表,另外在统计数据时需要一个Data类来保存从数据库查出来的各种信息,映射到类的各个属性,而manager类仅仅是管理员登陆时的验证所用,影响不大

    image-20201231234445544

    在类中级联关系的体现:学生类中应当有相关的已选课程的信息,因为是一对多的关系,所以用一个集合list来存放课程信息:

    image-20201231234517078

    而在Course类中除了基本属性外,也还要有该课程所教授老师的信息,因为是一对一关系,只需用一个Teacher属性来表示即可:

    image-20201231234843045

    而Teacher类对应课程也是一对多的关系,除了基本属性同样需要list来存放课程信息:

    image-20201231235031016

    最后是存放统计数据各种信息的类,方便mapper操作:

    image-20201231235151525

    mapper的编写

    主要是处理各种需求需要操作数据库,同样三个mapper对应三个大功能

    image-20201231235316120

    主要sql语句还是写在了mapper配置里:

    image-20201231235406706

    StudentMapper.xml

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.deserts.dao.StudentMapper">
        <select id="findStuInfoByUid" parameterType="string" resultType="student">
            select * from student where uid = #{uid}
        </select>
    
        <update id="updatePwd" parameterType="student">
            update student set pwd = #{pwd} where uid = #{uid}
        </update>
    
        <update id="updateInfo" parameterType="student">
            update student set sname = #{sname},gender=#{gender},age=#{age} where sid = #{sid}
        </update>
    
        <resultMap id="studentInfo" type="student">
            <result column="sid" property="sid"/>
            <result column="sname" property="sname"/>
            <collection property="courseList" ofType="com.deserts.pojo.Course">
                <result column="cid" property="cid"/>
                <result column="cname" property="cname"/>
                <result column="credit" property="credit"/>
                <result column="grade" property="grade"/>
                <result column="tname" property="teacher.tname"/>
            </collection>
        </resultMap>
        <select id="findCourseInfoByUid" resultMap="studentInfo">
            SELECT s.sid sid,s.sname sname,c.*,sc.grade grade,t.tname tname
            FROM student s LEFT JOIN sc ON s.sid = sc.sid
            INNER JOIN course c ON c.cid = sc.cid
            INNER JOIN tc ON c.cid = tc.cid
            INNER JOIN teacher t ON t.tid = tc.tid
            WHERE uid = #{uid}
        </select>
        <select id="countTotalCredit" parameterType="string" resultType="double">
            SELECT SUM(credit) FROM course
            INNER JOIN sc ON course.cid = sc.cid
            INNER JOIN student ON sc.sid = student.sid
            WHERE student.uid = #{uid}
        </select>
    
        <resultMap id="courseInfo" type="course">
            <result column="cid" property="cid"/>
            <result column="cname" property="cname"/>
            <result column="credit" property="credit"/>
            <result column="tname" property="teacher.tname"/>
            <result column="post" property="teacher.post"/>
        </resultMap>
        <select id="findAllCourse" resultMap="courseInfo">
            SELECT c.cid cid,c.cname cname,c.credit credit,t.tname tname,t.post post
            FROM course c LEFT JOIN tc ON tc.cid = c.cid
            INNER JOIN teacher t ON tc.tid = t.tid
        </select>
    
        <insert id="selectCourse" parameterType="integer">
            INSERT INTO SC(sid,cid) VALUES(#{param1}, #{param2})
        </insert>
    </mapper>
    

    ManagerMapper.xml

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.deserts.dao.ManagerMapper">
        <select id="findByUid" resultType="manager">
            select * from manager where mid = #{uid}
        </select>
    
        <insert id="addStudentInfo" parameterType="student">
            insert into student values(#{sid},#{sname},#{gender},#{age},#{uid},#{pwd})
        </insert>
    
        <select id="selectStudentInfoBySid" parameterType="integer" resultType="student">
            select * from student where sid=#{sid}
        </select>
    
        <update id="updateStudentInfo" parameterType="student">
            update student set gender=#{gender},age=#{age},uid=#{uid},pwd=#{pwd} where sid = #{sid}
        </update>
    
        <delete id="deleteStudentInfo" parameterType="integer">
            delete from student where sid=#{sid}
        </delete>
    
        <delete id="deleteScInfo" parameterType="integer">
            delete from sc where sid = #{sid};
        </delete>
    
        <insert id="addTeacherInfo" parameterType="teacher">
            insert into teacher values(#{tid},#{tname},#{post},#{salary})
        </insert>
    
        <select id="selectTeacherInfo" parameterType="integer" resultType="teacher">
            select * from teacher where tid=#{tid};
        </select>
    
        <update id="updateTeacherInfo" parameterType="teacher">
            update teacher set tname=#{tname},post=#{post},salary=#{salary} where tid=#{tid};
        </update>
    
        <delete id="deleteTCInfo" parameterType="integer">
            delete from tc where tid=#{tid}
        </delete>
    
        <delete id="deleteTeacherInfo" parameterType="integer">
            delete from teacher where tid=#{tid}
        </delete>
    
        <insert id="addTC" parameterType="integer">
            insert into tc values(#{param1}, #{param2})
        </insert>
    
        <update id="updateTC" parameterType="integer">
            update tc set tid=#{param1} where cid=#{param2}
        </update>
    
        <delete id="deleteTC" parameterType="integer">
            delete from tc where cid = #{cid}
        </delete>
    
        <update id="addGrade">
            update sc set grade=#{param3} where sid=#{param1} and cid=#{param2}
        </update>
    </mapper>
    

    DataMapper.xml

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.deserts.dao.DataMapper">
        <select id="selectCountPost" resultType="data">
            SELECT post, COUNT(*) countPost FROM teacher GROUP BY post;
        </select>
        <select id="selectAVGSalary" resultType="data">
            SELECT post, AVG(salary) avgSalary FROM teacher GROUP BY post;
        </select>
        <select id="selectAVGGrade" resultType="data">
            SELECT cname, AVG(grade) avgGrade FROM sc
            INNER JOIN course ON sc.cid = course.cid
            GROUP BY sc.cid;
        </select>
        <select id="selectMAXGrade" resultType="data">
            SELECT cname, MAX(grade) maxGrade FROM sc
            INNER JOIN course ON sc.cid = course.cid
            GROUP BY sc.cid;
        </select>
        <select id="selectMINGrade" resultType="data">
            SELECT cname, MIN(grade) minGrade FROM sc
            INNER JOIN course ON sc.cid = course.cid
            GROUP BY sc.cid;
        </select>
        <select id="selectSUMCredit" resultType="data">
            SELECT sname, SUM(credit) sumCredit FROM course
            INNER JOIN sc ON course.cid = sc.cid
            INNER JOIN student ON sc.sid = student.sid
            GROUP BY sname;
        </select>
    </mapper>
    

    service层的编写

    主要是把调用杂七杂八的mapper来处理某个需求,具体代码很简单,目录如下:

    image-20201231235753520

    Controller控制器层的编写

    用户在页面点击发送相应的请求需要在控制器中进行处理,这里主要是用三个控制器对应三个大的功能:

    image-20201231235903437

    StudentController

    package com.deserts.controller;
    
    import com.deserts.pojo.Course;
    import com.deserts.pojo.Student;
    import com.deserts.service.StudentService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.List;
    
    /**
     * @ClassName StudentController
     * @Description TODO
     * @Author deserts
     * @Date 2020/12/28 22:00
     */
    @Controller
    @RequestMapping("/student")
    public class StudentController {
        @Autowired
        private StudentService service;
    
        @RequestMapping("/login")
        public String login(@RequestParam("username")String username, @RequestParam("password")String password, HttpServletRequest request, HttpServletResponse response){
            Student student = service.selectByUid(username);
            if(student != null && student.getPwd().equals(password)){
                request.getSession().setAttribute("username", username);
                request.getSession().setAttribute("password", password);
                return "/pages/student/main";
            }else {
                return "/fail";
            }
    
        }
    
        @RequestMapping("/updatePwd")
        public String updatePwd(@RequestParam("old")String oldP, @RequestParam("new")String newP,HttpServletRequest request, HttpServletResponse response){
            String username = (String) request.getSession().getAttribute("username");
            String password = (String) request.getSession().getAttribute("password");
            Student student = service.selectByUid(username);
            if(password.equals(oldP)){
                student.setPwd(newP);
                service.updatePwd(student);
                request.getSession().setAttribute("password", newP);
                return "/pages/student/success";
            }else {
                return "/fail";
            }
        }
    
        @RequestMapping("/info")
        public String info(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            String username = (String) request.getSession().getAttribute("username");
            Student student = service.selectByUid(username);
            request.setAttribute("student", student);
            return "/pages/student/info";
        }
    
        @RequestMapping("/updateInfo")
        public String updateInfo(@RequestParam(value = "name",required = false)String name,
                                 @RequestParam(value = "gender", required = false)String gender,
                                 @RequestParam(value = "age",required = false)String age,
                                 HttpServletRequest request){
            String username = (String) request.getSession().getAttribute("username");
            Student student = service.selectByUid(username);
            if(name != null){
                student.setSname(name);
            }
            if (gender!= null){
                student.setGender(gender);
            }
            if(age != null){
                student.setAge(Integer.parseInt(age));
            }
            service.updateInfo(student);
            return "/pages/student/success";
        }
    
        @RequestMapping("/courseInfo")
        public String courseInfo(HttpServletRequest request){
            String username = (String) request.getSession().getAttribute("username");
            Student student = service.findCourseInfoByUid(username);
            Double credit = service.countCredit(username);
            System.out.println(credit);
            System.out.println(student);
            request.setAttribute("studentInfo", student);
            request.setAttribute("credit", credit);
            return "/pages/student/courseInfo";
        }
    
        @RequestMapping("allCourse")
        public String allCourse(HttpServletRequest request){
            List<Course> courseList = service.findAllCourse();
            request.setAttribute("courseList", courseList);
            return "/pages/student/selectCourse";
        }
    
        @RequestMapping("selectCourse")
        public String selectCourse(@RequestParam("cid")String cid, HttpServletRequest request){
            String username = (String) request.getSession().getAttribute("username");
            Student student = service.selectByUid(username);
            Integer sid = student.getSid();
            service.selectCourse(sid, Integer.parseInt(cid));
            return "/pages/student/successSelect";
        }
    }
    
    

    ManagerController

    package com.deserts.controller;
    
    import com.deserts.pojo.Manager;
    import com.deserts.pojo.Student;
    import com.deserts.pojo.Teacher;
    import com.deserts.service.ManagerService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * @ClassName ManagerController
     * @Description TODO
     * @Author deserts
     * @Date 2020/12/30 19:03
     */
    @Controller
    @RequestMapping("/manager")
    public class ManagerController {
        @Autowired
        private ManagerService service;
    
        @RequestMapping("/login")
        public String login(@RequestParam("username")String username, @RequestParam("password")String password, HttpServletRequest request, HttpServletResponse response){
            Manager manager = service.findByUid(username);
            if(manager!=null && manager.getPwd().equals(password)){
                return "/pages/manager/main";
            }else {
                return "/fail";
            }
        }
    
        @RequestMapping("/addStudentInfo")
        public String addStudentInfo(@RequestParam("sid")String sid,
                                     @RequestParam("sname")String sname,
                                     @RequestParam("gender")String gender,
                                     @RequestParam("age")String age,
                                     @RequestParam("uid")String uid,
                                     @RequestParam("pwd")String pwd){
            Student student = new Student(Integer.parseInt(sid), sname, gender, Integer.parseInt(age), uid, pwd);
            service.addStudentInfo(student);
            return "/success";
        }
    
        @RequestMapping("/selectStudentInfo")
        public String selectStudentInfo(@RequestParam("sid") String sid, HttpServletRequest request){
            Student student = service.selectStudentBySid(sid);
            request.setAttribute("student", student);
            request.getSession().setAttribute("sid", sid);
            return "/pages/manager/updateStudentInfo";
        }
    
        @RequestMapping("/updateStudentInfo")
        public String updateStudentInfo(@RequestParam("gender")String gender,
                                        @RequestParam("age")String age,
                                        @RequestParam("uid")String uid,
                                        @RequestParam("pwd")String pwd,
                                        HttpServletRequest request){
            String sid = (String) request.getSession().getAttribute("sid");
            Student student1 = new Student(Integer.parseInt(sid), null, gender, Integer.parseInt(age), uid, pwd);
            service.updateStudentInfo(student1);
            return "/pages/manager/updateSuccess";
        }
    
        @RequestMapping("/selectStudentInfo2")
        public String selectStudentInfo2(@RequestParam("sid") String sid, HttpServletRequest request){
            Student student = service.selectStudentBySid(sid);
            request.setAttribute("student", student);
            request.getSession().setAttribute("sid", sid);
            return "/pages/manager/deleteStudentInfo";
        }
    
        @RequestMapping("/deleteStudent")
        public String deleteStudent(HttpServletRequest request){
            String sid = (String) request.getSession().getAttribute("sid");
            service.deleteStudentInfo(sid);
            return "/pages/manager/deleteSuccess";
        }
    
        @RequestMapping("/addTeacherInfo")
        public String addTeacherInfo(@RequestParam("tid")String tid,
                                     @RequestParam("tname")String tname,
                                     @RequestParam("post")String post,
                                     @RequestParam("salary")String salary){
            Teacher teacher = new Teacher(Integer.parseInt(tid), tname, post, Double.parseDouble(salary));
            service.addTeacherInfo(teacher);
            return "/pages/manager/addSuccess";
        }
    
        @RequestMapping("/selectTeacherInfo")
        public String selectTeacherInfo(@RequestParam("tid")String tid, HttpServletRequest request){
            request.getSession().setAttribute("tid", tid);
            Teacher teacher = service.selectTeacherInfo(tid);
            request.setAttribute("teacher", teacher);
            return "/pages/manager/updateTeacherInfo";
        }
    
        @RequestMapping("/updateTeacherInfo")
        public String updateTeacherInfo(@RequestParam("tname")String tname,
                                        @RequestParam("post")String post,
                                        @RequestParam("salary")String salary,
                                        HttpServletRequest request){
            String tid = (String) request.getSession().getAttribute("tid");
            Teacher teacher = new Teacher(Integer.parseInt(tid), tname, post, Double.parseDouble(salary));
            service.updateTeacherInfo(teacher);
            return "/pages/manager/updateSuccess";
        }
    
        @RequestMapping("/selectTeacherInfo2")
        public String selectTeacherInfo2(@RequestParam("tid")String tid, HttpServletRequest request){
            request.getSession().setAttribute("tid", tid);
            Teacher teacher = service.selectTeacherInfo(tid);
            request.setAttribute("teacher", teacher);
            return "/pages/manager/deleteTeacherInfo";
        }
    
        @RequestMapping("/deleteTeacher")
        public String deleteTeacher(HttpServletRequest request){
            String tid = (String) request.getSession().getAttribute("tid");
            service.deleteTeacherInfo(tid);
            return "/pages/manager/deleteSuccess";
        }
    
        @RequestMapping("/addTC")
        public String addTC(@RequestParam("tid")String tid, @RequestParam("cid")String cid){
            service.addTC(tid,cid);
            return "/pages/manager/addSuccess";
        }
    
        @RequestMapping("/updateTC")
        public String updateTC(@RequestParam("tid")String tid, @RequestParam("cid")String cid){
            service.updateTC(tid,cid);
            return "/pages/manager/updateSuccess";
        }
    
        @RequestMapping("/deleteTC")
        public String deleteTC(@RequestParam("cid")String cid){
            service.deleteTC(cid);
            return "/pages/manager/deleteSuccess";
        }
    
        @RequestMapping("/addGrade")
        public String addGrade(@RequestParam("sid")String sid,
                               @RequestParam("cid")String cid,
                               @RequestParam("grade")String grade){
            service.addGrade(sid, cid, grade);
            return "/pages/manager/addSuccess";
        }
    }
    
    

    DataController

    package com.deserts.controller;
    
    import com.deserts.pojo.Data;
    import com.deserts.service.DataService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import javax.servlet.http.HttpServletRequest;
    import java.util.List;
    
    /**
     * @ClassName DataController
     * @Description TODO
     * @Author deserts
     * @Date 2020/12/31 10:53
     */
    @Controller
    @RequestMapping("/data")
    public class DataController {
        @Autowired
        private DataService service;
    
        @RequestMapping("/selectCountPost")
        public String selectCountPost(HttpServletRequest request){
            List<Data> data = service.selectCountPost();
            request.setAttribute("dataList", data);
            return "/pages/data/selectCountPost";
        }
    
        @RequestMapping("/selectAVGSalary")
        public String selectAVGSalary(HttpServletRequest request){
            List<Data> data = service.selectAVGSalary();
            request.setAttribute("dataList2", data);
            return "/pages/data/selectAVGSalary";
        }
    
        @RequestMapping("/selectAVGGrade")
        public String selectAVGGrade(HttpServletRequest request){
            List<Data> data = service.selectAVGGrade();
            request.setAttribute("dataList3", data);
            return "/pages/data/selectAVGGrade";
        }
    
        @RequestMapping("/selectMAXGrade")
        public String selectMAXGrade(HttpServletRequest request){
            List<Data> data = service.selectMAXGrade();
            request.setAttribute("dataList4", data);
            return "/pages/data/selectMAXGrade";
        }
    
        @RequestMapping("/selectMINGrade")
        public String selectMINGrade(HttpServletRequest request){
            List<Data> data = service.selectMINGrade();
            request.setAttribute("dataList5", data);
            return "/pages/data/selectMINGrade";
        }
    
    
        @RequestMapping("/selectSUMCredit")
        public String selectSUMCredit(HttpServletRequest request){
            List<Data> data = service.selectSUMCredit();
            request.setAttribute("dataList6", data);
            return "/pages/data/selectSUMCredit";
        }
    }
    
    

    页面的编写

    页面十分简陋,主要由几个简单的标签组成,具体目录如下:

    主页:

    image-20201231233547950

    学生相关功能:

    image-20201231233620783

    管理员相关功能:

    image-20201231233659254

    统计数据相关功能:

    image-20201231233726696

    整体的目录结构:

    image-20201231233759450

    9.功能展示

    主页

    image-20210101001037317

    学生相关功能

    登录

    image-20210101001147683

    登录后的主页

    image-20210101001234740

    修改密码:

    image-20210101001341278

    修改成功:

    image-20210101001413470

    查看个人信息:

    image-20210101001502398

    修改个人信息:

    image-20210101001541421

    修改成功:

    image-20210101001559918

    修改后再查看:

    image-20210101001634132

    查看选课信息及总学分:

    image-20210101001715613

    选课:

    image-20210101001839700

    选课成功:

    image-20210101001903934

    管理员功能展示

    管理员登录

    image-20210101002312919

    主页

    image-20210101002343327

    添加学生信息:

    image-20210101002615819

    修改学生信息:

    image-20210101002720179

    image-20210101002734383

    删除学生信息

    image-20210101002828294

    image-20210101002842807

    添加教师信息

    image-20210101002925779

    修改教师信息

    image-20210101002959380

    image-20210101003016246

    删除教师信息:

    image-20210101003044221

    image-20210101003055855

    添加课程信息

    image-20210101003126117

    修改课程信息:

    image-20210101003149340

    删除课程信息:

    image-20210101003214909

    学生课程录入:

    image-20210101003243026

    统计数据

    主页

    image-20210101003323718

    各个数据统计

    image-20210101003345931

    image-20210101003413182

    image-20210101003434644

    image-20210101003452115

    image-20210101003505060

    image-20210101003522390

    六、实验总结

    涉及的知识

    主要是Spring+SpringMVC+MyBatis+Mysql这几个部分的知识。

    实验过程的问题

    在实现删除学生信息功能时,刚开始只删除student表中的信息,会发现有错误,因为还有sc表的外码指向它,所以需要先删除sc表的相关学生信息,再去删除student表的信息;后面做删除教师信息的功能也是同理。

    总结

    通过本次实验,对数据库的综合应用有了一定的实践,对所学知识有了一定的巩固。整个项目也有很多可以改进的地方。

    完整代码地址:https://gitee.com/desertslin/deserts.git

    展开全文
  • 实验三 数据库系统设计综合实验 1、实验目的 通过实验,使学生掌握数据库系统设计和开发的一般方法,能够设计并实现简单的数据库系统。 2、实验要求 熟悉实验室实验环境,掌握实验预备知识,了解实验中故障排除的...
                                     实验三 数据库系统设计综合实验
    1、实验目的
       通过实验,使学生掌握数据库系统设计和开发的一般方法,能够设计并实现简单的数据库系统。
    2、实验要求
       熟悉实验室实验环境,掌握实验预备知识,了解实验中故障排除的基本方法。实验中根据实验要求完成
    相应的任务,并独立完成实验报告。
    3、实验内容
       设计的数据库系统可从以下题目中选择,但不限定于以下题目,可自由选择。要求独立完成下列系统所需的
    数据库的需求分析、概念结构设计、逻辑结构设计、物理结构设计;任选自己熟悉的数据库管理系统完成
    数据库的搭建。
    4、系统题目参考:
    (1)学生成绩管理系统
    (2)网上书城系统
    (3)进销存系统
    (4)酒店客房管理系统
    (5)图书馆管理系统
    
    
    
    我的选题:同校学生互助问答平台的设计与实现
    说明:实验三的实验报告分成两个文档,但放在同一个Word文档中:
    1、需求分析规格说明书
    2、详细设计文档
    
                                         需求分析规格说明书
    

    项目名称 <同校学生互助问答平台的设计与实现>
    文档类别 <说明>
    文档编号 <128>
    版 本 <V1.0>
    密 级 <秘密>
    二〇二〇年六月二十一日

    目录
    0、修订历史记录……………………………………………………………………………4
    1、导言………………………………………………………………………………………4
    1.1、编写目的………………………………………………………………………………4
    1.2、背景……………………………………………………………………………………5
    1.3、项目范围………………………………………………………………………………5
    1.4、参考资料………………………………………………………………………………5
    2、产品……………………………………….………………………………………………5
    2.1、产品前景………………………………………………………………………………5
    2.2、产品功能………………………………………………………………………………6
    2.3、运行环境………………………………………………………………………….……6
    3、功能性需求…………………………………………………………………………….…6
    3.1、系统功能需求…………………………………………………………………………6
    3.2、用例图…………………………………………………………………………………7
    3.3、用例列表………………………………………………………………………………8
    4、性能……………………………………………………………………………….……10
    4.1、性能需求………………………………………………………………………………10

    在这里插入图片描述

    1、导言

    1.1、	编写目的
    一、
        该文档首先给出项目的整体结构和功能结构概貌,试图从总体架构上给出整个系统的轮廓。同时对功能
    需求、性能需求进行详细的描述。便于用户、开发人员进行理解和交流, 反映出用户问题的结构,可以作为
    软件开发工作的基础和依据以及确认测试和验收的依据。
    二、
    本文档面向的预期读者对象:
    1、项目经理:项目经理可以根据该文档了解预期产品的功能,并据此进行系统设计、项目管理。
    2、设计员:对需求进行分析,并设计出系统,包括数据库的设计。
    3、程序员:了解系统功能,编写《用户手册》。
    4、测试员:根据本文档编写测试用例,并对软件产品进行功能性测试和非功能性测试。
    5、用户:了解预期产品的功能和性能,并与分析人员一起对整个需求进行讨论和协商。 
    在阅读本文档时,首先要了解产品的功能概貌,然后可以根据自身的需要对每一功能进行适当的了解。
    
    1.2、	背景
    1、网上论坛是一种供人们进行交流的网络空间。它不受时间和空间的约束,论坛用户可以发表自己的观点,
    大家一起探讨某个问题。实现用户与用户间的交流,利用网络经济而又快捷地与外界进行各种信息沟通,取得
    更有用的信息资源。目前,网上论坛已不是新事物,许许多多的别具特色的论坛在网络上随处可见。
    2、对当代学生而言,刷论坛更是家常便饭。虎扑,知乎,小红书等论坛都可见他们的身影。男生热衷于篮球、
    球鞋等论坛。女生沉迷于美妆美食服饰等论坛。对于他们而言,在论坛上交流比面对面的交流显得更自在。这
    是当代学生的现状。因此本项目将开发一个互助问答平台,以便学生们线上交流分享经验。
    
    1.3、	项目范围
    该项目要求实现客户前端和管理后端。
    1)客户端主要功能模块:1、个人登录及个人信息资料修改
                  2、搜索、浏览信息
                  3、提问与回答
                  4、纠错与删除、举报
    5、退出登录
    客户端用户为游客的话只有搜索、浏览问答信息和申请下载某文件的功能。
    
    2)管理端主要功能模块:1、登录及团队信息资料修改
                  2、搜索、浏览信息
                  3、管理用户及其问答(警告违规用户与删除违规信息等)
                  4、退出登录
    
    1.4、参考资料
    [1] 韩万江、姜立新.《软件工程案例教程》软件项目开发实践第3版,
    Software Engineering A Case Study Approach ,书号978-7-111-50163-3  机械工业出版社
    [2] 张宇波、王善勤.《基于Java EE的校园问答系统的设计与实现别》,
    文章编号:1671-5993(2014)03-0046-06,[
    Online]Available: https://www.doc88.com/p-5179507306223.html (Sep,2014)
    [3]骑着飞机打乌鸦小组.《师生问答平台网站需求规格说明书》,
     [Online]Available:https://www.cnblogs.com/pirigg/p/10838686.html(May 8,2019)
    

    2、产品

    2.1、产品前景
        伴随着移动互联网的逐步成熟,问答平台的竞争也日益激烈,它们为众多网名搭建了一个强大的信息获取
    交流平台,因此问答平台也越来越受人们关注,比较有影响力的有百度知道、知乎、果壳及若帮等。编者也
    经常去这些平台上帮助网友回答一些关于互联网和心理健康等方面的问题,同事也经常在这些平台上去寻找
    自己需要的知识。
        因此,开发一个同校学生互助问答平台挺现实的,在这个平台上学生们可以有针对性的提问或者分享自己
    的经验,即使没有百度知道的回答那么多元化,没有知乎的答案相对专业化,但在校生在互助问答平台上可以
    更加有专一性地寻找自己需要的问题的答案或者发出关于校园内自己不懂的问题,相信可以很快得到其他用户
    的帮助。网络信息流通突破了时间和空间的限制,满足了学生快节奏生活、提高工作效率、更加深入了解校园
    文化、通过互联网自主学习的需要,同时,实现校园信息资源共享也起着重要作用,也对专业内涵建设具有
    良好的促进作用。
    
    2.2、产品功能
    为了满足学生日常问答的需要,本项目计划阶段完成如下三个核心功能:
    1、在校生的搜索、浏览、提问、回答、删除问答功能
    2、纠错功能,发现有错误的问题或者回答,可直接向用户本身发送纠错信息,同时也可接收别人发来的
    纠错信息。
    3、举报功能,如果提问的话题敏感、不合理,用户可以向管理员举报,管理员收到举报信息后对内容进行
    审核。
    【说明】本项目实现的主要功能也已在导言部分的项目范围中详细写出了。
    
    2.3、	运行环境
    服务器配置如下
    操作系统:Windows XP及以上
    CPU:Intel i5酷睿双核及以上
    内存:8G及以上
    硬盘空间:100G以上
    软件配置如下:
    开发工具:MyEclipse
    数据库:SQL Server
    Web服务器:Tomcat
    
    3、功能性需求
    3.1、系统功能需求:
    1、在校生:可凭借学号和初始密码进行登录平台,可以查看个人资料,搜搜问答信息,提可以发表自己的问题
    ,可以回答其他人的问题,删除自己曾发布的问答信息,举报违规用户,纠错,退出平台等。
    2、管理员:可凭借管理员账号登录平台,查看管理账号信息、搜索问题,删除别人发布的不当内容的信息,
    警告违规用户,处理游客申请,退出平台等。
    3、游客:不允许注册,但可搜索、浏览问答信息,并可申请下载需要的文件。
    

    3.2、用例图
    1)在校生用例图
    在这里插入图片描述
    2)管理员用例图
    在这里插入图片描述
    3)游客用例图
    在这里插入图片描述
    3.3、用例列表
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    4、性能

    4.1、性能需求
    在这里插入图片描述
    在这里插入图片描述

    ①按照规定的格式输入,否则系统提示错误并要求重新输入。
    ②搜索的时候所有包含关键字的记录保证全部搜索到。
    ③程序响应时间在人的感觉和视觉范围内,长时间无响应应提示用户刷新。
    ④每个问题可以有多个用户回答,并且得到相应的处理。
    ⑤能够适应迭代开发。
    ⑥平台的界面要求如下:
    1)页面内容:各类问答信息内容准确,行文格式统一、规范、明确,菜单设置和布局合理,传递的信息准确并
    具有时效性。
    2)导航结构:页面具有明确的导航提示,且便于理解,方便用户使用。
    3)技术环境:页面大小适当,能用各种常用浏览器以不同分辨率浏览,无错误链接和空链接。
    4)艺术风格:界面、版面形象清新悦目、布局合理、字号大小适宜、字体选择合理,前后一致,美观大方,
      色彩和谐自然,与内容相协调。
    ⑦响应时间需求:
        当用户登录时,平台应该及时地进行反应,反应的时间在3秒以内,平台应能检测出各种非正常情况,如与
    设备的通信中断,无法连接数据库服务器等,避免出现长时间等待甚至无响应。
    ⑧可靠性需求
        平台应保证在7*24小时内不宕机,保证1000人以上可以同时在客户端登录,平台正常运行,正确提示相关
    内容。
    ⑨可扩展性要求
        平台设计要求能够体现扩展性要求,以适应将来功能扩展的需求。
    ⑩系统安全性需求:
        平台有严格的权限管理功能,各功能模块需相应的权限方能使用(如在校生、管理员、游客具有不同的
        权限),平台能够防止各类误操作可能造成的数据丢失、破坏。防止用户非法获取网页以及内容。
    



                                          详细设计文档
    

    目录
    1、概念结构设计………………………………………………………13
    1.1、实体属性图………………………………………………………13
    1.2、绘制E-R图…………………………………….…………………18
    2、逻辑结构设计………………………………………………………19
    2.1、数据库表…………………………………………………………19
    2.2、数据库表间关系…………………………………………………23
    3、物理结构设计………………………………………………………25
    3.1、定义及步骤………………………………………………………25
    3.2、设计………………………………………………………………26

    1、概念结构设计

    1.1、实体属性图
    根据需求分析,可以得出以下数据项:
    ①用户管理:用户实体:id、用户名、密码、入学时间、真实姓名、性别、邮箱。
    在这里插入图片描述
    ②管理员管理:管理员实体:管理员id、名称、密码、头像地址、真实姓名、性别、邮箱。
    在这里插入图片描述
    ③回答管理:回答实体:用户id、回答时间、回答字数。
    在这里插入图片描述
    ④提问管理:提问实体:用户id、提问时间、提问字数、备注、手机号、用户邮箱。
    在这里插入图片描述
    ⑤举报管理:举报实体:举报用户id、被举报用户id、举报时间、举报内容、举报原因。
    在这里插入图片描述
    ⑥下载管理:下载实体:下载时间、下载内容、申请下载原因。
    在这里插入图片描述
    ⑦警告管理:警告实体:管理员id、管理员名称、被警告用户id、警告时间、警告原因、备注。
    在这里插入图片描述
    ⑧删除信息管理:删除信息实体:管理员id或用户id、管理员名称或用户名、删除时间、删除内容、删除原因。
    在这里插入图片描述
    ⑨纠错管理:纠错实体:纠错用户id、被纠错用户id、纠错内容、纠错原因、纠错时间。
    在这里插入图片描述
    ⑩搜索管理:搜索记录实体:管理员id或用户id、管理员名称或用户名、搜索时间、搜索内容。
    在这里插入图片描述
    11、角色管理:角色实体:角色id、角色名称、备注。
    在这里插入图片描述

    1.2、绘制E-R图
    E-R图是我们常用的对数据库进行结构设计方法,根据数据库的需求分析进行对实体表结构进行设计,从而确定
    各实体之间的关系。
    (1)数据库表间关系如下:
    1)一个用户可以对应多条回答,但是一个回答只能对应一个用户,所以用户实体和回答实体的关系是1:n;
    2)一个用户可以对应多条提问,但是一个提问只能对应一个用户,所以用户实体和提问实体的关系是1:n;
    3)一个用户可以发起多条举报,但一条举报只能对应一个用户,所以用户实体和举报实体的关系是1:n;
    4)一个用户可以对应多次下载,但一次下载只能对应一个用户,所以用户实体和下载实体的关系是1:n;
    5)一个用户可以对应删除多条信息,但删除一条信息只能由一个用户进行,所以用户实体和删除信息实体的
      关系是1:n;
    6)一个用户可以发送多条纠错信息,一条纠错信息也可由多个用户发起,所以用户实体和纠错实体的
      关系是n:n;
    7)一个用户可以发起多个搜索信息,一个搜索也可由多个用户发起,所以用户实体和搜索实体的关系是n:n;
    8)一个管理员可以接收处理多条举报,但一条举报只能对应一个管理员处理,所以管理员实体和举报实体
      的关系是1:n;
    9)一个管理员可以发出多条警告,但一条警告只能由一个管理员发出,所以管理员实体和警告实体的
      关系是1:n;
    10)一个管理员可以处理多条下载文件的申请,但一条申请只能由一个管理员处理,所以管理员实体和
       下载实体的关系是1:n;
    11)一个管理员可以删除多条信息,但一条信息只能由一个管理员删除,所以管理员实体和删除信息实体
        的关系是1:n;
    12)一个管理员可以发起多个搜索信息,一个搜索也可由多个管理员发起,所以管理员实体和搜索实体
       的关系是n:n;
    13)一个角色可对应多种管理员,但是一个管理员只能对应一种角色,所以角色实体和管理员实体的
       关系是1:n;
    14)一个提问可以对应多条回答,但一条回答只能对应一个提问,所以提问实体和回答实体的关系是1:n;
    
    【说明】因为篇幅原因,此处的同校学生互助问答平台的基本E-R图省略了其中的实体属性的绘制。
    (实体属性图在前面也画过了)
    

    在这里插入图片描述

    2、逻辑结构设计

    2.1、数据库表

    用户表(consumer)记录用户信息,其中主键为用户id(userid),用户名可自定义创建,密码(upassport)是用户身份验证的必要条件。其他一些属性为昵称(unickname)、入学时间(utime)、真实姓名(uname)、性别(usex)、用户邮箱(uemail)。

    在这里插入图片描述
    管理员表(administrators)记录管理员信息,其中主键是管理员id(adid),其中表中的adid属性参照的是角色表(role)中的角色id(rid),密码(adpassport)是管理员身份验证的必要条件。其他属性为名称(adnickname)、头像地址(adadd)、真实姓名(adname)、性别(adsex)、邮箱(ademail)。

    在这里插入图片描述
    超级管理员只有一个,但是可以创建不同权限的普通管理员,所以此角色表(role)记录着不同角色的管理员,角色id(rid)为表的主键,表的角色id(rid)属性被管理员表(administrators)的管理员id(adid)属性所参照。其余属性为角色名称(rname)、备注(rnote)。

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    警告表(warn)记录了管理员警告违规用户的一些信息,其中主键是管理员id(wid)和被警告用户id(wedid),前者参照的是管理员表(administrators)中的管理员id(adid),后者参照的是用户表(consumer)中的用户id(userid),其余属性还有管理员名称(wnickname),此处管理员名称并非外码,不做多加要求,警告时间(wtime)、警告原因(ereason)。

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    2.2、数据库表间关系图
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    3、物理结构设计

    3.1定义及步骤
    物理结构设计是利用已确定的逻辑数据结构以及DBMS提供的方法、技术、以较优的存储结构、数据存取路径、
    合理的数据存储位置以及存储分配,设计出一个高效的、可实现的物理数据库结构。由于数据库的物理结构
    依赖于给定的计算机软件及硬件环境,依赖于所选用的DBMS。因此,设计数据库的物理结构应充分考虑数据库
    的物理环境,例如数据库存取设备、存储组织和存取方法,数据库管理系统及其他辅助性软件工具等通常关系
    数据库的物理结构设计主要包括以下内容:
    (1)确定数据的存取方法
    存取方法是快速存取数据库中的数据的技术,数据库管理系统一般都提供多种存取方法,具体采取哪种存取
    方法由系统根据数据的存储方式来决定,用户一般不能干预。
    用户通常可以利用建立索引的方法来加快数据的查询效率。如果建立了索引,系统就可以使用索引查找方法,
    索引方法实际上就是根据应用要求确定在关系的哪个属性或哪些属性上建立索引,确定在哪此属性上建立
    复合索引,哪些索引要设计为唯一索引以及哪些索引要设计为聚簇索引,聚簇索引是将索引在物理上有序排列
    后得到的索引。需要注意的是,索引一般可以提高查询性能,但会降低数据修改性能。因为在修改数据时,
    系统要同时对索引进行维护,使索引与数据保持 一致。维护索引要占用相当多的时间,而且存放索引信息也会
    占用空间资源 因此在决定是否建立索引时,要权衡数据库的操作,如果查询多,而且对查询的性能要求
    比较高,则要考虑多建一些索引。如果数据更改多,并且对更改的效率要求比较高,则应考虑少建些索引,
    建立索引的原则下,满足以下条件之一的,可以在有关属性上建立索引:
    ①主键和外键上通常建立索引。
    ②如果一个属性经常在查询条件中出现,则考虑在这个属性 上建立索引。
    ③如果一个属性经常作为更大值和更小值等聚集函数的参数,则考虑在这个属性上建立索引。
    ④如果一个属性经常在连接操作的连接条件中出现,则考虑在这个属性上建立索引。
    ⑤对于以查询为主或者只查询的关系表,只要需要且存储空间允许,可以多建索引满足以下条件之一的,
    不宜建立索引。
    ①不出现或者很少出现在查询条件中的属性。
    ②属性值是可能取值的个数很少的属性。
    ③属性值分布严重不均的属性。
    ④经常更新的属性和表。因为在更新属性值时,必须对相应的索引做出修改,这就使系统为维护索引付出较大的
     代价。
    ⑤属性值过长,在过长的属性 上建立索引,索引所占的存储空间比较大,而且索引的级数随之增加,将会带来
     许多不便。
    ⑥太小的表不值得使用索引。
    
    (2)确定数据的存储结构
    物理结构设计中一个重要的考虑因素就是确定数据记录的存储方式。
    常用的存储方式有 :
    ①顺序存储。这种存储方式的平均查找次数。
    ②散列存储。这种存储方式的平均查找次数由散列算法决定。
    ③聚簇存储。这种存储方式是指将不同类型的记录外配到相同的物理区域中,充分利用物理顺序性的优点,提高
     数据访问速度。即将经常在一起使用的记录聚簇在一起,以减少物理输入/输出次数。
    
    总结:
    数据库在物理设备上的存储结构和存取方式称为数据库的物理结构。它依赖于选定的数据库管理系统。为一个
    给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程,就是数据库的物理设计。
    数据库的物理设计通常分为两步:
    (1)确定数据库的逻辑结构,在关系数据库中主要指存取方式和存储结构。
    (2)对物理结构进行评价,评价的重点是时间和空间效率。
        如果评价结果满足原设计要求,则可进入到物理实施阶段,否则,就需要重新设计或修改物理结构,有时
    甚至要返回逻辑设计阶段修改数据模型。
    
    3.2、设计
    (1)确定存取方式
    数据库系统是多用户共享的系统,对同一个关系要建立多条存取路径,才能满足多用户的多种应用要求。物理
    结构设计的任务之一是根据关系数据库管理系统支持的存取方法确定选择哪些存取方法。存取方式是快速存取
    数据库中数据的技术。数据库管理系统一般提供多种存取方式。常用的存取方法为索引方法和聚簇方法。
    此处我的数据库管理系统的很多表会有大量的数据信息且查询操作频繁,因此采用索引方法可以提高数据操作
    效率。但索引的建立和重建及其维护本身代价也挺大,所以应根据不同的表选择合适的存取策略。考虑搜索表
    及删除表等要进行频繁的插入操作,因此可选择建立非聚簇索引来提高操作效率。对于使用频率较低,如下载
    表,则不应该建立索引,因为这会耗费大量维护代价,不值得。
    一般来说关系数据库管理系统都会在建表时自动地建立主码索引,所以有的表可以直接使用主码索引便可尽快
    地找到对应的元组。当然有需要的话也可以再在相应的属性上建立联合索引等,这会更进一步加快存取数据库
    中数据的速度,但同时要考虑到其维护代价。
    

    索引截图:
    在这里插入图片描述
    在这里插入图片描述

    (2)确定存储结构
        为了提高系统性能,应根据应用情况将数据的易变部分与稳定部分,经常存取部分和存取频率较低部分
    分开存放。
    例如,数据库数据备份、日志文件备份等,由于只在故障恢复时才使用,而且数据量很大,可以考虑存放在
    磁带上。可以将比较大的表分别放在两个磁盘上,以加快存取速度,这在多用户环境下特别有效。可以将
    日志文件与数据库对象(表、索引等)放在不同的磁盘以改进系统的性能。
    关系数据库管理系统产品一般都提供了一些系统配置变量和存储分配参数,供设计人员和数据库管理员对
    数据库进行物理优化。
    
    (3)评价物理结构
    数据库物理设计过程中需要对时间效率、空间效率、维护代价和各种用户要求进行权衡,其结果可以产生多种
    方案。评价物理数据库的方法完全依赖于所选用的关系数据库管理系统,主要是从定量估算各种方案的存储
    空间、存取法时间和维护代价入手,对估算结果进行权衡、比较,选择出一个较优的、合理的物理结构。
    一般来说,消除一切冗余数据虽能够节约存储空间和减少维护代价,但往往会导致检索代价的增加,必须进行
    权衡,选择一个折中方案。
    

    数据库原理实验三—实验报告到此结束!希望能多大家有所帮助,如发现错漏可以留言修正或补充,一起进步呀,谢谢!😊

    欲浏览更多博文? 速戳–>大白的博客,欢迎来访噢!😊😊

    展开全文
  • 数据库实验报告

    2017-12-14 23:37:15
    本文档为数据库上机实验报告,是自己认认真真一步一步写的,报告包含试验中的具体步骤,过程以及代码和实验结果截图,和实验总结。 实验一 实验题目: 数据库管理系统的使用 实验目的: 掌握SQL SERVER2005的...
  • 西工大数据库实验三

    2016-10-21 10:32:43
    数据库实验三数据库完整性与安全性预习报告
  • 大二数据库实验报告

    万次阅读 多人点赞 2018-05-23 18:22:22
    实验三 使用SQL语言进行简单查询 实验四 使用SQL语言进行复杂查询 实验五 SQL常用数据更新操作 实验六 综合应用\ 实验一:熟悉数据库管理系统环境 实验过程及分析: 首先创建一个数据库和需要的表: ...

    实验要求:

    实验一 熟悉数据库管理系统环境
    实验二SQL定义语言
    实验三 使用SQL语言进行简单查询
    实验四 使用SQL语言进行复杂查询
    实验五 SQL常用数据更新操作
    实验六 综合应用\

    实验一:熟悉数据库管理系统环境

    实验过程及分析:
    首先创建一个数据库和需要的表:

    create database XSGL
    GO
    use XSGL
    GO
    Create table student      --创建学生表
    (sno char(8) primary key,        --(实体完整性)学生姓名
     sname char(8) not null unique, --学生姓名
     ssex char(2) default '男' check(ssex='男' or ssex='女'),  --性别给定默认值为'男',取值只能取‘男’或‘女’
     sage tinyint check(sage>13 and sage<50),
     sdept char(20))
    
    
    create table course       --创建课程表 
    (cno char(2) PRimary key,        --课程编号
     cname varchar(50),  --课程名称
     cpno char(2),       --先修课号
     ccredit tinyint)  --课程名
    
    create table sc        --创建成绩表
    (sno char(8),           --学生学号
     cno char(2),         --课程编号
     grade tinyint,           --成绩
     constraint pk_grade primary key(sno,cno),
     constraint fk_stuid foreign key(sno) references student(sno),
     constraint fk_course foreign key(cno) references course(cno),
     constraint ck_grade check(grade>=0 and grade<=100) )
    go
    insert into student(sno,sname, ssex,sage,sdept) values('95001', '李勇', '男', 20, 'CS')
    insert into student(sno,sname, ssex,sage,sdept) values('95002', '刘晨', '女', 19, 'IS')
    insert into student(sno,sname, ssex,sage,sdept) values('95003', '王敏', '女', 18, 'MA')
    insert into student(sno,sname, ssex,sage,sdept) values('95004', '张立', '男', 19, 'IS')
    insert into student(sno,sname, ssex,sage,sdept) values('95005', '刘云', '女', 18, 'CS ')
    insert into course(cno, cname,ccredit,cpno) values('1', '数据库', 4, '5')
    insert into course(cno, cname,ccredit,cpno) values('2', '数学', 6, null)
    insert into course(cno, cname,ccredit,cpno) values('3', '信息系统', 3, '1')
    insert into course(cno, cname,ccredit,cpno) values('4', '操作系统', 4, '6')
    insert into course(cno, cname,ccredit,cpno) values('5', '数据结构', 4, '7')
    insert into course(cno, cname,ccredit,cpno) values('6', '数据处理', 3, null)
    insert into course(cno, cname,ccredit,cpno) values('7', 'PASCAL语言', 4, '6')
    insert into sc(sno,cno,grade) values('95001', '1' ,92)
    insert into sc(sno,cno,grade) values('95001', '2' ,85)
    insert into sc(sno,cno,grade) values('95001', '3' ,88)
    insert into sc(sno,cno,grade) values('95002', '2' ,90)
    insert into sc(sno,cno,grade) values('95002', '3' ,80)
    insert into sc(sno,cno,grade) values('95003', '2' ,85)
    insert into sc(sno,cno,grade) values('95004', '1' ,58)
    insert into sc(sno,cno,grade) values('95004', '2' ,85)
    

    1)STUDENT表中增加一个字段入学时间scome,

    alter table student 
    add scome date

    2)删除STUDENT表中sdept字段;

    alter table student
    drop column sdept

    3)删除创建的SC表中cno字段和COURSE表cno字段之间的外键约束;

    alter table sc
    DROP fk_course

    4)重建3)中删除的约束

    alter table sc
    add constraint fk_course foreign key(cno) references course(cno)

    5、重新定义一个简单表,然后用SQL语言DROP语句删除该表结构;

    drop table sc

    6、用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序唯一索引;

    create index index_sname
    on student(sname desc)

    7、用SQL语言DROP语句删除索引;

    drop index index_sname on student

    8.我觉得没有错误……

    实验总结:
    1. 创建表的时候可以添加约束
    2. 可以添加主键唯一标识 用primary key
    3. 可以使用默认值 是 default
    4. 可以使用外键来限制取值范围、
    5. 使用alter添加,修改列;还可以删除表中约束如索引 index
    6. 使用DROP 可以直接删除表 删除的时候先要删除外键表后才可以删除主键表

    实验二: SQL定义语言

    实验过程及分析:
    1.首先建立服务器连接 如果连接无法连接到本地local可以参考
    sqlserver2008无法连接到local解决方案
    2.然后打开新建查询选择数据库
    3.创建表用SQL语言,具体的过程实验一已经有了

    用SQL语言ALTER语句修改表结构;
    (1)STUDENT表中增加一个字段入学时间scome,
    (2)删除STUDENT表中sdept字段;
    (3)删除创建的SC表中cno字段和COURSE表cno字段之间的外键约束;
    (4)重建(3)中删除的约束
    5、重新定义一个简单表,然后用SQL语言DROP语句删除该表结构;
    6、用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序唯一索引;
    7、用SQL语言DROP语句删除索引;

    有没有发现 和实验一重复了…………
    没错 所以为了凑字数 我再抄了一遍……

    实验总结:
    1. 删除外键只能用alter 指定表 而不能用on来选择表……原因不明
    2. 删除索引不能用alter 直接用DROP INDEX 索引 ON 表 原因不明

    实验三:使用SQL语言进行简单查询

    实验过程及分析:
    还是使用实验一的表
    (1)查询全体学生的学号和姓名

    select sno,sname 
    from student;

    (2)查询全体学生的详细记录

    select * 
    from student;

    (3)查询软件学院的学生姓名、年龄、系别

    select sname,sage,sdept
    from student where sdept='MA';

    (4)查询所有选修过课程的学生学号(不重复)

    select distinct sno 
    from sc;

    (5)查询考试不及格的学生学号(不重复)

    select distinct sno
    from sc
    where grade<60;

    (6)查询不是软件学院、计算机系的学生性别、年龄、系别

    select ssex,sage,sdept
    from student 
    where sdept not in('CS','MA');

    (7)查询年龄18-20岁的学生学号、姓名、系别、年龄;

    select sno,sname,sdept,sage 
    from student
    where sage>=18 and sage<=20;

    (8)查询姓刘的学生情况

    select * 
    from student
    where sname like '刘%';

    (9)查询姓刘或姓李的学生情况

    select * from student where sname like '刘%' or sname like '李%'

    (10)查询姓刘且名字为两个字的学生情况

    select * 
    from student
    where sname like '刘_';

    (11)查询1983年以后出生的学生姓名。

    select sname from student where sage < 2018-1983

    (12)创建表 studentgrad(sno,mathgrade,englishigrade,chinesegrade)
    计算学生各科总成绩并赋予别名

    Create table studentgrad(
        Sno char(8) ,
        mathgradeint,
        englishigradeint,
        chinesegradeint
    )
    Select sum(mathgrade+chinesegrade+englishigrade) '学生总成绩' from studentgrad 

    (13)利用内部函数 year()查找软件学院学生的出生年份

    select (year(getdate())-student.sage+1) 
    from student 
    where sdept='MA';

    (14)利用字符转换函数实现字符联接。

    select sname + '年龄为'+cast(sage as char(2))+'岁'
    from student;
    Select sname + ‘年龄为’+cast(sage as char(2))+’岁’
    From student

    (15)查询全体学生情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列。

    select*
    from student order by sdept,sage desc;

    (16)查询学生总人数。

    select count(*) 
    from student;

    (17)查询选修了课程的学生人数。

    select count(distinct sno) 
    from sc;

    (18)查询选修了7号课程的学生总人数和平均成绩

    select count(*),avg(grade)as avggrade 
    from student ,sc 
    where student.sno=sc.sno and sc.cno='1';

    (19)查询选修6号课程学生的最好成绩

    select max(grade) as maxgrade 
    from sc
    where cno='2';

    (20)查询每个系的系名及学生人数。

    select sdept,count(*) 
    from student group by sdept;

    (21)查找每门课的选修人数及平均成绩

    select cno,count(*),avg(grade) as avggrade 
    from sc group by cno;

    (22)查找没有先修课的课程情况

    select * 
    from course 
    where cpno is null;

    实验总结:
    1. 函数year(),count(),max()可以方便查询
    2. 模糊查询法要% 如like ‘刘%’
    3. group by 可以分组查询

    实验四:使用SQL语言进行复杂查询

    实验过程及分析:
    1、实验一中的数据为基础
    2、对各表中的数据进行不同条件的连接查询和嵌套查询;
    (1)查询每个学生及其选课情况;

    select student.sno,sname,ssex,sage,sdept,cno,grade
    from student,sc
    where student.sno=sc.sno

    (2)查询每门课的间接先修课

    select first.cno,second.cpno
    from course first,course second
    where first.cpno=second.cno 

    (3)将STUDENT,SC进行右连接

    select student.sno,sname,ssex,sage,sdept,cno,grade
    from student right outer join sc on student.sno=sc.sno

    (4)查询既选修了2号课程又选修了3号课程的学生姓名、学号;

    select student.sno,sname
    from student inner join sc on student.sno=sc.sno
    where cno='3' and sc.sno in
    (select sno
    from sc
    where cno='2')

    (5)查询和刘晨同一年龄的学生

    select student.sno,sname
    from student
    where sname!='刘晨' and sage=
    (select sage 
    from student
    where sname='刘晨')

    (6)选修了课程名为“数据库”的学生姓名和年龄

    select sname,sage
    from student
    where sno in
    (select sno
    from sc
    where cno in
    (select cno
    from course 
    where cname='数据库'))

    (7)查询其他系比IS系任一学生年龄小的学生名单

    select student.sno,sname
    from student
    where sdept<>'IS' and
    sage<any
    (select sage 
    from student
    where sdept='IS')

    (8)查询其他系中比IS系所有学生年龄都小的学生名单

    select student.sno,sname
    from student
    where sdept<>'IS' and 
    sage<all
    (select sage 
    from student 
    where sdept='IS')

    (9)查询选修了全部课程的学生姓名

    select sname
    from student
    where Sno in
    (select Sno from SC
    group by Sno
    having count(*) = (select count(*) from course ))

    (10)查询计算机系学生及其性别是男的学生

    select student.sno,sname
    from student
    where sdept='IS' and ssex='男'

    (11)查询选修课程1的学生集合和选修2号课程学生集合的差集

    select sno
    from sc 
    where cno='1' except 
    select sno
    from sc
    where cno='2'

    (12)查询李丽同学不学的课程的课程号

    select cno
    from course
    where cno not in
    (select cno
    from sc
    where sno in
    (select sno
    from student
    where sname='李丽'))

    (13)查询选修了3号课程的学生平均年龄

    select AVG(sage) as avgsage
    from student inner join sc on student.sno=sc.sno
    where cno='3'

    (14)求每门课程学生的平均成绩

    select cno,AVG(grade) as avggrade
    from sc
    group by cno

    (15)统计每门课程的学生选修人数(超过3人的才统计)。要求输出课程号和选修人数,结果按人数降序排列,若人数相同,按课程号升序排列

    select course.cno '课程号', count(sc.sno) '人数'
    from course,sc 
    where course.cno=sc.cno 
    group by course.cno having count(sc.sno)>3 order by COUNT(sc.sno) desc,course.cno asc

    (16)查询学号比刘晨大,而年龄比他小的学生姓名。

    select sname
    from student
    where sno>
    (select sno from student where sname='刘晨')and
    sage<(select sage from student where sname='刘晨')

    (17)求年龄大于所有女同学年龄的男同学姓名和年龄

    select sname,sage
    from student
    where ssex='男'and sage>
    (select MAX(sage) from student where ssex='女')

    实验总结:
    1. 求总数可以用COUNT()函数
    2. 分组group by 要用having来限制条件
    3. order by是排序要求 desc是降序 ,asc是升序
    4. any()函数是任意的意思,all()是所有

    实验五:SQL的常用数据更新操作

    实验过程及分析:
    1、应用INSERT,UPDATE,DELETE语句进行更新操作;
    (1)插入如下学生记录(学号:95030,姓名:李莉,年龄:18)

    insert into student(sno,sname,sage)
    values ('95030','李莉',18)

    (2)插入如下选课记录(95030,1)

    insert into sc(sno,cno)
    values('95030',1)

    (3)计算机系学生年龄改成20

    update student
    set sage=20
    where sdept='CS'

    (4)把数学系所有学生成绩改成0

    update sc
    set grade=0
    where 'MA'=
    (select sdept
    from student
    where student.sno=sc.sno)

    (5)把低于总平均成绩的女同学成绩提高5分

    update sc 
    set grade+=5
    where grade<
    (select avg(grade) 
    from sc inner join student
    on student.sno=sc.sno
    where ssex='女')

    (6)删除95030学生信息

    delete
    from student
    where sno='95030'

    (7)删除SC表中无成绩的记录

    delete 
    from sc
    where grade is null;

    (8)删除张娜的选课记录

    delete
    from sc
    where sno=(select sno from student 
    where sname='张娜')

    (9)删除不及格的学生选课记录

    delete
    from sc
    where grade<60

    (10)删除数学系所有学生选课记录

    delete
    from sc
    where sno in (select sno from student where sdept='MA')

    (11)删除所有未被选修的课程

    delete
    from course
    where cno not in (select cno from sc)

    (12)查询每一门课程成绩都大于等于80分的学生学号、姓名和性别,把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中

    Create table STU 
    (sno char(8), 
    sname char(8), 
    ssex char(2) 
    )
    
    insert into STU(sno,sname,ssex)
    select distinct student.sno,sname,ssex
    from student,sc 
    where student.sno not in
    (select sno from sc where grade<80) and student.sno=sc.sno
    

    (13)建立一个sdeptgrade 表,包含(sdept,avggrade)字段,对每一个系,求学生的成绩,并把结果存入sdeptgrade

    Create table sdeptgrade 
    (sdept char(8) primary key, 
    avggrade int; ) 
    
    insert into sdeptgrade 
    select student.sdept, avg(sc.grade) 
    from student inner join SC on 
    (student.sno = SC.sno) group by student.sdept;

    实验总结:
    1. 删除主键表数据如果有外键约束就会报错
    2. 插入数据用insert into 表直接+表
    3. 更新用update
    4. 删除直接用delete 可以直接删除一行数据

    实验六:综合应用**

    实验过程及分析:
    建立一个数据库和五张表的表结构
    这里写图片描述
    这里写图片描述
    首先创建表:

    create database Person
    GO 
    use person
    GO
    Create table employee
    (
    emp_no char(5) primary key,
    emp_name char(10) not null,
    Sex char(1) not null,
    Dept char(4) not null,
    Title char(6) not null,
    data_hired datetime not null,
    birthday datetime null,
    salary int not null,
    Addr char(50) null,
    Mod_date datetime Default(getdate())
    )
    create table customer
    (
    cust_id char(5) primary key,
    cust_name char(20) not null,
    Addr char(40) not null,
    tel_no char(10) not null,
    Zip char(6) null
    )
    create table sales
    (
    order_no int primary key,
    cust_id char(5) not null,
    sale_id char(5) not null,
    tot_amt numeric(9,2) not null,
    order_date datetime not null,
    ship_date datetime not null,
    incoice_no char(10) not null
    )
    create table sale_item
    (
    order_no int not null,
    prod_id char(5) not null,
    Qty int not null,
    unit_price numeric(9,2) not null,
    order_date datetime null
    constraint primary_sale primary key(order_no,prod_id)
    )
    create table product
    (
    prod_id char(5) not null primary key,
    prod_naem char(20) not null
    )

    3、录入数据并实现实现如下查询
    (1)查找定单金额高于20000的客户编号;

    select cust_id from sales where tot_amt>2000

    (2)选取销售数量最多的前5条订单订单号、数量;

    select top 5 order_no,Qty from sale_item order by Qty DESC

    (3)显示sale_item表中每种个别产品的订购金额总和,并且依据销售金额由大到小排
    来显示出每一种产品的排行榜;

    select prod_id, sum(Qty*unit_price) '金额' from sale_item group by prod_id order by '金额' DESC

    (5)计算每一产品每月的销售金额总和,并将结果按销售(月份,产品编号)排序;

    select "s2".月份,SUM("s2".tot_amt) '销售金额总和',"s1".prod_id '产品编号'
    from sale_item "s1"
    join (select MONTH(order_date) '月份',order_no,tot_amt from sales) "s2"
    on "s1".order_no="s2".order_no
    group by "s2".月份,"s1".prod_id
    order by "s2".月份,"s1".prod_id

    (6)检索单价高于2400元的的产品编号、产品名称、数量、单价及所在订单号;

    select s.prod_id '产品编号',product.prod_name '产品名称',s.Qty '数量',s.unit_price '单价',s.order_no 
    from product INNER JOIN 
    (select order_no,prod_id,Qty,unit_price from sale_item where unit_price>2400) s
    on product.prod_id=s.prod_id

    (7)计算每一产品销售数量总和与平均销售单价;

    select "s1".销售总额,"s2".unit_price '平均销售单价'
    from(select SUM(Qty*unit_price) '销售总额',prod_id from sale_item group by prod_id) "s1"
    join sale_item "s2"
    on "s1".prod_id="s2" .prod_id

    (8)创建一个视图,该视图只含上海客户信息,即客户号、客户姓名、住址。

    CREATE VIEW view_name AS
    select cust_id,cust_name,Addr from customer where Addr='上海'

    实验总结:
    1. 设置主键,自动为 not null
    2. unique和主键区别:
    unique:唯一并且 一张表可设置多个unique 可空 但是只能有一行数据空
    主键: 唯一并且 一张表只能有一个主键
    何时用到主键?
    设置外键的时候需要主键 还有唯一标识一列的时候 比如身份证
    3. 主键可通过 constraint 主键名 primary key(列,列)来设置*组合键*
    4. 给表取别名的时候 不能用单引号,要用双引号或者不用引号 而给列取别名的时候可以选择单引号 或者 as 连接词 或者不用引号
    5. where之类的范围时 列=单引号内容时值 双引号为列名
    6. top 5表示 取前5名
    7. 视图是为了保存一张表 下次查找该表可直接 使用 如本实验中:

    select * from view_name

    即可查看 视图

    展开全文
  • 数据库实验报告.zip

    2021-01-18 20:23:25
    合肥工业大学宣城校区数据库实验报告 内含次实验,共实验一到实验十
  • 实验3 数据移动 1.数据库备份 2.数据库还原 3.数据库导入(使用文本文件) 4.数据库导出(导出数据到Excel)
  • 作业七 索引 /视图 /同义词 /序列 一实验目的 掌握索引和视图的管理掌握同义词和序列的应用 二实验内容 创建使用和管理索引视图同义词序列 三实验步骤 1索引 创建表 employee并插入记录 create table employee( id ...
  • 、SQL调试及结果 数据库的创建和管理 1.在SQL Server企业管理器中创建一个数据库,具体要求如下: (1)数据库名称为Mydata。 (2)主要数据文件:逻辑文件名为Mydatabase,物理文件名为Mydatabase.mdf,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 468
精华内容 187
关键字:

数据库实验三实验报告