中国石油大学（华东）数据库原理实验（MySQL版）

目录

实验一 SQL练习1

一、实验目的

二、实验学时

三、实验内容

四、实验报告

实验二 SQL练习2

一、实验目的

二、实验学时

三、实验内容

四、实验报告

实验三 SQL练习3

一、实验目的

二、实验学时

三、实验内容

四、实验报告

实验四 其它数据库对象的管理

一、实验目的

二、实验学时

三、实验内容

四、实验报告

实验五 安全机制

一、实验目的

二、实验学时

三、实验内容 

四、实验报告

---

本实验结果相关的图表略。

1. 实验一 SQL练习1

一、实验目的

    1．掌握基本表的定义；

    2．掌握插入数据、修改数据和删除数据语句的常用形式。

二、实验学时

2学时

三、实验内容

1．新建一个数据库，数据库名字以自己的姓名命名，并有以下要求：

⑴ 该数据库记录某采油厂对油/水井实施作业时所消耗的成本。（作业：为保证油水井正常生产所实施的工程项目）

⑵ 成本的消耗（成本表）分为预算、结算、入账三个状态。

预算：采油队向管理部门提出作业申请，并经管理部门批准后，由管理部门负责录入。

结算：某次作业施工结束后，由管理部门与施工单位共同核算各种成本，由管理部门负责录入。

入账：结算后，财务部门将成本计入采油厂账目，由财务部门录入。

⑶ 预算状态时需要录入的数据：（★：字符型   ◆数字型   ▲日期型）

★单据号：某一作业项目的编号

★预算单位：指需要对油水井实施作业的采油队代码

★井号：需要实施作业的油水井

◆预算金额   ★预算人   ▲预算日期

⑷ 结算状态时需要录入的数据：（★：字符型   ◆数字型   ▲日期型）

调出某条预算记录，录入以下数据：

▲开工日期   ▲完工日期   ★施工单位   ★施工内容

◆材料费（要求另外用表单独记录材料消耗的明细）

材料消耗表（★单据号  ★物码   ◆消耗数量）

◆人工费   ◆设备费   ◆其它费用

◆结算金额（材料费+人工费+设备费+其它费用）

★结算人   ▲结算日期

⑸ 入账状态时需要录入的数据：（★：字符型   ◆数字型   ▲日期型）

调出某条结算记录，录入以下数据：

◆入账金额   ★入账人   ▲入账日期

⑹ 为了避免出现数据的不一致和方便数据录入，要求定义以下基础表：（★：字符型）

单位表：★单位代码   ★单位名称

油水井表：★井号   ★井别：油井/水井   ★单位代码  （表示某口井由哪个单位负责管理）

材料表：★物码   ★名称   ★计量单位   ◆单价

2．根据以上要求，用建表语句定义所需的数据表（暂时不定义完整性规则）。

3．用数据插入语句录入以下数据：

单位表：

1122       采油厂

112201     采油一矿       112202     采油二矿

112201001  采油一矿一队   112201002  采油一矿二队

112201003  采油一矿三队

112202001  采油二矿一队   112202002  采油二矿二队

油水井表：

（y001   油井   112201001）  （y002   油井   112201001）

（y003   油井   112201002）  （s001   水井   112201002）

（y004   油井   112201003）  （s002   水井   112202001）

（s003   水井   112202001）  （y005   油井   112202002）

材料表：

wm001 材料一 吨  10    wm002 材料二 米  10

wm003 材料三 桶  10    wm004 材料四 袋  10

4．录入以下数据：

某作业项目单据号  zy2023001

预算单位：112201001   井号：y001   预算金额：10000.00

预算人：张三   预算日期：2023-5-1   开工日期：2023-5-4

完工日期：2023-5-25   施工单位：作业公司作业一队

施工内容：堵漏    材料费：7000.00(其中材料一：2000.00

材料二：2000.00    材料三：2000.00   材料四：1000.00)

人工费：2500.00    设备费：1000.00   其它费用：1400.00

结算金额：11900.00  结算人：李四   结算日期：2023-5-26

入账金额：11900.00  入账人：王五   入账日期：2023-5-28

某作业项目单据号  zy2023002

预算单位：112201002   井号：y003   预算金额：11000.00

预算人：张三   预算日期：2023-5-1    开工日期：2023-5-4

完工日期：2023-5-23   施工单位：作业公司作业二队

施工内容：检泵     材料费：6000.00(其中材料一：2000.00

材料二：2000.00     材料三：2000.00)

人工费：1500.00     设备费：1000.00   其它费用：2400.00

结算金额：10900.00   结算人：李四   结算日期：2023-5-26

入账金额：10900.00   入账人：王五   入账日期：2023-5-28

某作业项目单据号  zy2023003

预算单位：112201002   井号：s001   预算金额：10500.00

预算人：张三   预算日期：2023-5-1   开工日期：2023-5-6

完工日期：2023-5-23   施工单位：作业公司作业二队

施工内容：调剖    材料费：6500.00(其中材料一：2000.00

材料二：2000.00    材料三：2500.00)

人工费：2000.00    设备费：500.00   其它费用：1400.00

结算金额：10400.00  结算人：李四   结算日期：2023-5-26

入账金额：10400.00  入账人：王五   入账日期：2023-5-28

某作业项目单据号  zy2023004

预算单位：112202001   井号：s002   预算金额：12000.00

预算人：张三   预算日期：2023-5-1   开工日期：2023-5-4

完工日期：2023-5-24   施工单位：作业公司作业三队

施工内容：解堵    材料费：6000.00(其中材料一：2000.00

材料二：2000.00    材料四：2000.00)

人工费：2000.00    设备费：1000.00   其它费用：1600.00

结算金额：10600.00  结算人：李四   结算日期：2023-5-26

入账金额：10600.00  入账人：赵六   入账日期：2023-5-28

某作业项目单据号  zy2023005

预算单位：112202002   井号：y005   预算金额：12000.00

预算人：张三   预算日期：2023-5-1   开工日期：2023-5-4

完工日期：2023-5-28   施工单位：作业公司作业三队

施工内容：防砂    材料费：7000.00(其中材料一：2000.00

材料二：2000.00    材料四：3000.00)

人工费：1000.00    设备费：2000.00   其它费用：1300.00

结算金额：11300.00   结算人：李四   结算日期：2023-6-1

5．完成以下操作：

⑴ 将编号为zy2023005的项目的人工费和结算金额增加200元。

⑵ 删除已经结算但未入账的项目。

⑶ 撤消上述两个操作。

四、实验报告

提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。

# 1新建一个数据库，数据库名字以自己的姓名命名
CREATE DATABASE `金小矿` CHARACTER SET 'utf8mb4';
# 2根据以上要求，用建表语句定义所需的数据表
create table 成本消耗表
(
  单据号 varchar(20),
  预算单位 varchar(20),
  井号 varchar(20),
  预算金额 decimal(10, 2),
  预算人 varchar(20),
  预算日期 date,
  开工日期 date,
  完工日期 date,
  施工单位 varchar(20),
  施工内容 varchar(20),
  材料费 decimal(10,2),
  材料一费用 decimal(10,2),
  材料二费用 decimal(10,2),
  材料三费用 decimal(10,2),
  材料四费用 decimal(10,2),
  人工费 decimal(10,2),
  设备费 decimal(10,2),
  其他费用 decimal(10,2),
  结算金额 decimal(10,2),
  结算人 varchar(20),
  结算日期 date,
  入账金额 decimal(10,2),
  入账人 varchar(20),
  入账日期 date
 );
create table 单位表
(
    单位代码 char(9),
    单位名称 char(12)
);
create table 油水井表
(
    井号 char(4),
    井别 char(2),
    单位代码 char(9)
);
create table 材料表
(
    物码 char(20),
    名称 char(20),
    计量单位 char(20),
    单价 decimal(10,2)
);
create table 材料消耗表
(
    单据号 char(20),
    物码 char(20),
    消耗数量 int
);
# 3向表中插入数据
insert into 单位表
values('1122','采油厂'),
      ('112201', '采油一矿'),
      ('112202', '采油二矿'),
      ('112201001', '采油一矿一队'),
      ('112201002', '采油一矿二队'),
      ('112201003', '采油一矿三队'),
      ('112202001', '采油二矿一队'),
      ('112202002', '采油二矿二队');     
select * from 单位表;
insert into 油水井表
values('y001','油井','112201001'),
      ('y002','油井','112201001'),
      ('y003','油井','112201002'),
      ('s001','水井','112201002'),
      ('y004','油井','112201003'),
      ('s002','水井','112202001'),
      ('s003','水井','112202001'),
      ('y005','油井','112202002');
select * from 油水井表;
insert into 材料表
values('wm001','材料一','吨',10),
      ('wm002','材料二','米',10),
      ('wm003','材料三','桶',10),
      ('wm004','材料四','袋',10);
select * from 材料表;
insert into 材料消耗表
values
('zy2023001','wm001',200),
('zy2023001','wm002',200),
('zy2023001','wm003',200),
('zy2023001','wm004',100),
('zy2023002','wm001',200),
('zy2023002','wm002',200),
('zy2023002','wm003',200),
('zy2023003','wm001',200),
('zy2023003','wm002',200),
('zy2023003','wm003',250),
('zy2023004','wm001',200),
('zy2023004','wm002',200),
('zy2023004','wm004',200),
('zy2023005','wm001',200),
('zy2023005','wm002',200),
('zy2023005','wm004',300);
select * from 材料消耗表;
insert into 成本消耗表
values ('zy2023001', '112201001', 'y001', '10000.00', '张三',
        '2023-05-01', '2023-05-04', '2023-05-25', '作业公司作业一队', '堵漏',
        '7000.00', '2000.00', '2000.00', '2000.00', '1000.00',
        '2500.00', '1000.00', '1400.00',
        '11900.00', '李四', '2023-05-26', '11900.00', '王五', '2023-05-28'),
       ('zy2023002', '112201002', 'y003','11000.00', '张三',
        '2023-05-01', '2023-05-04', '2023-05-23', '作业公司作业二队', '检泵',
        '6000.00', '2000.00', '2000.00', '2000.00', NULL,
        '1500.00', '1000.00', '2400.00',
        '10900.00', '李四', '2023-05-26', '10900.00', '王五', '2023-05-28'),
       ('zy2023003', '112201002', 's001','10500.00', '张三',
        '2023-05-01', '2023-05-04', '2023-05-23',
        '作业公司作业二队', '调剖', '6500.00', '2000.00', '2000.00', '2500.00', NULL,
        '2000.00', '500.00', '1400.00',
        '10400.00', '李四', '2023-05-26', '10400.00', '王五', '2023-05-28'),
       ('zy2023004', '112201001', 's002',
        '12000.00', '张三', '2023-05-01', '2023-05-04', '2023-05-24',
        '作业公司作业三队', '解堵', '6000.00', '2000.00', '2000.00', NULL, '2000.00',
        '2000.00', '1000.00', '1600.00',
        '10600.00', '李四', '2023-05-26', '10600.00', '赵六', '2023-05-28'),
       ('zy2023005', '112201002', 'y005',
        '12000.00', '张三',
        '2023-05-01', '2023-05-04', '2023-05-28',
        '作业公司作业三队', '防沙',
        '7000.00', '2000.00', '2000.00', NULL, '3000.00',
        '1000.00', '2000.00', '1300.00',
        '11300.00', '李四', '2023-06-01', NULL, NULL, NULL);
select * from 成本消耗表;
#查看开关状态
show variables like 'SQL_SAFE_UPDATES';
#修改数据库模式
SET SQL_SAFE_UPDATES = 0;
#开始事务create table 成本消耗表
(
  单据号 varchar(20),
  预算单位 varchar(20),
  井号 varchar(20),
  预算金额 decimal(10, 2),
  预算人 varchar(20),
  预算日期 date,
  开工日期 date,
  完工日期 date,
  施工单位 varchar(20),
  施工内容 varchar(20),
  材料费 decimal(10,2),
  材料一费用 decimal(10,2),
  材料二费用 decimal(10,2),
  材料三费用 decimal(10,2),
  材料四费用 decimal(10,2),
  人工费 decimal(10,2),
  设备费 decimal(10,2),
  其他费用 decimal(10,2),
  结算金额 decimal(10,2),
  结算人 varchar(20),
  结算日期 date,
  入账金额 decimal(10,2),
  入账人 varchar(20),
  入账日期 date
 );
create table 单位表
(
    单位代码 char(9),
    单位名称 char(12)
);
create table 油水井表
(
    井号 char(4),
    井别 char(2),
    单位代码 char(9)
);
create table 材料表
(
    物码 char(20),
    名称 char(20),
    计量单位 char(20),
    单价 decimal(10,2)
);
create table 材料消耗表
(
    单据号 char(20),
    物码 char(20),
    消耗数量 int
);
# 3向表中插入数据
insert into 单位表
values('1122','采油厂'),
      ('112201', '采油一矿'),
      ('112202', '采油二矿'),
      ('112201001', '采油一矿一队'),
      ('112201002', '采油一矿二队'),
      ('112201003', '采油一矿三队'),
      ('112202001', '采油二矿一队'),
      ('112202002', '采油二矿二队');     
select * from 单位表;
insert into 油水井表
values('y001','油井','112201001'),
      ('y002','油井','112201001'),
      ('y003','油井','112201002'),
      ('s001','水井','112201002'),
      ('y004','油井','112201003'),
      ('s002','水井','112202001'),
      ('s003','水井','112202001'),
      ('y005','油井','112202002');
select * from 油水井表;
insert into 材料表
values('wm001','材料一','吨',10),
      ('wm002','材料二','米',10),
      ('wm003','材料三','桶',10),
      ('wm004','材料四','袋',10);
select * from 材料表;
insert into 材料消耗表
values
('zy2023001','wm001',200),
('zy2023001','wm002',200),
('zy2023001','wm003',200),
('zy2023001','wm004',100),
('zy2023002','wm001',200),
('zy2023002','wm002',200),
('zy2023002','wm003',200),
('zy2023003','wm001',200),
('zy2023003','wm002',200),
('zy2023003','wm003',250),
('zy2023004','wm001',200),
('zy2023004','wm002',200),
('zy2023004','wm004',200),
('zy2023005','wm001',200),
('zy2023005','wm002',200),
('zy2023005','wm004',300);
select * from 材料消耗表;
insert into 成本消耗表
values ('zy2023001', '112201001', 'y001', '10000.00', '张三',
        '2023-05-01', '2023-05-04', '2023-05-25', '作业公司作业一队', '堵漏',
        '7000.00', '2000.00', '2000.00', '2000.00', '1000.00',
        '2500.00', '1000.00', '1400.00',
        '11900.00', '李四', '2023-05-26', '11900.00', '王五', '2023-05-28'),
       ('zy2023002', '112201002', 'y003','11000.00', '张三',
        '2023-05-01', '2023-05-04', '2023-05-23', '作业公司作业二队', '检泵',
        '6000.00', '2000.00', '2000.00', '2000.00', NULL,
        '1500.00', '1000.00', '2400.00',
        '10900.00', '李四', '2023-05-26', '10900.00', '王五', '2023-05-28'),
       ('zy2023003', '112201002', 's001','10500.00', '张三',
        '2023-05-01', '2023-05-04', '2023-05-23',
        '作业公司作业二队', '调剖', '6500.00', '2000.00', '2000.00', '2500.00', NULL,
        '2000.00', '500.00', '1400.00',
        '10400.00', '李四', '2023-05-26', '10400.00', '王五', '2023-05-28'),
       ('zy2023004', '112201001', 's002',
        '12000.00', '张三', '2023-05-01', '2023-05-04', '2023-05-24',
        '作业公司作业三队', '解堵', '6000.00', '2000.00', '2000.00', NULL, '2000.00',
        '2000.00', '1000.00', '1600.00',
        '10600.00', '李四', '2023-05-26', '10600.00', '赵六', '2023-05-28'),
       ('zy2023005', '112201002', 'y005',
        '12000.00', '张三',
        '2023-05-01', '2023-05-04', '2023-05-28',
        '作业公司作业三队', '防沙',
        '7000.00', '2000.00', '2000.00', NULL, '3000.00',
        '1000.00', '2000.00', '1300.00',
        '11300.00', '李四', '2023-06-01', NULL, NULL, NULL);
select * from 成本消耗表;
#查看开关状态
show variables like 'SQL_SAFE_UPDATES';
#修改数据库模式
SET SQL_SAFE_UPDATES = 0;
#开始事务
begin; #另一种 start transaction
# 5.1将编号为zy2023005的项目的人工费和结算金额增加200元
update 成本消耗表
set 人工费 = 人工费 + 200,
	结算金额 = 结算金额 + 200
where 单据号 = 'zy2023005';   
select * from 成本消耗表;
# 5.2删除已经结算但未入账的项目
delete from 成本消耗表
where 入账金额 is NULL;
select * from 成本消耗表;
# 5.3撤消上述两个操作
rollback;
select * from 成本消耗表;
begin; #另一种 start transaction
# 5.1将编号为zy2023005的项目的人工费和结算金额增加200元
update 成本消耗表
set 人工费 = 人工费 + 200,
	结算金额 = 结算金额 + 200
where 单据号 = 'zy2023005';   
select * from 成本消耗表;
# 5.2删除已经结算但未入账的项目
delete from 成本消耗表
where 入账金额 is NULL;
select * from 成本消耗表;
# 5.3撤消上述两个操作
rollback;
select * from 成本消耗表;

实验二 SQL练习2

一、实验目的

    1．掌握索引的建立、删除及使用；

    2．掌握单表查询、连接查询、嵌套查询和集合查询；

    3．掌握插入数据、修改数据和删除数据语句的非常用形式。

二、实验学时

2学时

三、实验内容

1．完成以下操作：

⑴ 在预算日期、结算日期和入账日期上分别建立索引，并在查询操作中体会索引的作用。

⑵ 在完成第2题的查询操作后，删除预算日期、结算日期和入账日期上的索引。

2．完成以下操作：

⑴ 采油一矿二队2023-5-1到2023-5-28有哪些项目完成了预算，列出相应明细。

⑵ 采油一矿二队2023-5-1到2023-5-28有哪些项目完成了结算，列出相应明细。

⑶ 采油一矿二队2023-5-1到2023-5-28有哪些项目完成了结算，列出相应的材料费消耗明细。

⑷ 采油一矿二队2023-5-1到2023-5-28有哪些项目完成了入账，列出相应明细。

⑸ 列出采油一矿二队2023-5-1到2023-5-28总的预算金额。

⑹ 列出采油一矿二队2023-5-1到2023-5-28总的结算金额。

⑺ 列出采油一矿二队2023-5-1到2023-5-28总的入账金额。

⑻ 列出采油一矿2023-5-1到2023-5-28总的入账金额。

⑼ 有哪些人员参与了入账操作。

⑽ 列出2023-5-1到2023-5-28进行了结算但未入账的项目。

⑾ 列出采油一矿二队的所有项目，按入账金额从高到低排列。

⑿ 列出有哪些施工单位实施了项目，并计算各单位所有项目结算金额总和。

⒀ 找出消耗了材料三且消耗超过了2000元的项目，列出相应消耗明细(利用子查询)。

⒁ 作业公司作业二队参与了哪些项目。

⒂ 作业公司作业一队和作业二队参与了哪些项目（利用union）。

⒃ 采油一矿的油井是哪些作业队参与施工的。

3．完成以下操作：

⑴ 建立汇总表(包含3个属性列：★施工单位、★年月、◆结算金额)保存各个施工单位每月的结算金额总和。

⑵ 用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中。

⑶ 用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”。

⑷ 用带子查询的删除语句删除采油一矿油井作业项目。

⑸ 撤消上述两个操作。

四、实验报告

提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。

# 1.1在预算日期、结算日期和入账日期上分别建立索引，并在查询操作中体会索引的作用
create index budget_date on 成本消耗表(预算日期)
create index settlement_date on 成本消耗表(结算日期)
create index entry_date on 成本消耗表(入账日期)
# 2.1采油一矿二队2023-5-1到2023-5-28有哪些项目完成了预算，列出相应明细
select * from 成本消耗表
where 预算日期 >='2023-5-1' AND 预算日期 ='2023-5-1' AND 结算日期 ='2023-5-1' AND 结算日期 ='2023-5-1' AND 入账日期 ='2023-5-1' AND 预算日期 ='2023-5-1' AND 结算日期 ='2023-5-1' AND 入账日期 ='2023-5-1' AND 入账日期 ='2023-5-1' AND 结算日期 =2000);
# 2.14作业公司作业二队参与了哪些项目
select * from 成本消耗表
where 施工单位 = '作业公司作业二队'
# 2.15作业公司作业一队和作业二队参与了哪些项目（利用union）
select * from 成本消耗表
where 施工单位 = '作业公司作业一队'
union
select * from 成本消耗表
where 施工单位 = '作业公司作业二队'
# 2.16采油一矿的油井是哪些作业队参与施工的
select distinct 施工单位 from 成本消耗表,油水井表,单位表
where 成本消耗表.井号=油水井表.井号 and 油水井表.单位代码=单位表.单位代码 and 单位表.单位名称 like '采油一矿%'
# 1.2 在完成第2题的查询操作后，删除预算日期、结算日期和入账日期上的索引
DROP INDEX budget_date ON 成本消耗表;
DROP INDEX settlement_date ON 成本消耗表;
DROP INDEX entry_date ON 成本消耗表;
# 3.1建立汇总表(包含3个属性列：★施工单位、★年月、◆结算金额)保存各个施工单位每月的结算金额总和：
create table 汇总表
(施工单位 varchar(20),
年月 varchar(20),
结算金额 decimal(10,2));
select * from 汇总表;
# 3.2用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中：
insert into 汇总表(施工单位,年月,结算金额)
select 施工单位,date_format(结算日期, '%Y%m'),sum(结算金额) from 成本消耗表
group by `施工单位`,date_format(结算日期, '%Y%m');
select * from 汇总表;
# 3.3用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”：
begin;
update 成本消耗表 set 结算人='李兵'
where 预算单位 in 
(select 单位代码 from 单位表 where 单位名称 in (select 单位代码 from 单位表 where 单位名称 like '采油一矿%'));
select * from 成本消耗表;
# 3.4用带子查询的删除语句删除采油一矿油井作业项目：
delete from 成本消耗表 
where 预算单位 in (select 单位代码  from 单位表 where 单位名称 like '采油一矿%');
select * from 成本消耗表;
# 3.5撤消上述两个操作：
rollback;
select * from 成本消耗表;

实验三 SQL练习3

一、实验目的

    1．掌握基本表的删除与修改；

    2．掌握实体完整性、参照完整性和用户定义的完整性的定义、检查和违约处理；

    3．掌握视图的定义、查询和更新，了解视图的作用。

二、实验学时

2学时

三、实验内容

1．完成以下操作：

⑴ 向在实验二中所定义的数据表增加“备注”列，其数据类型为字符型，并查看新增列的值。

⑵ 对上述数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后再次执行实验二中实验内容3-⑵的操作，并观察记录执行结果。

⑶ 删除上述数据表中的数据，然后再删除该数据表，对这两个操作进行比较。

2．完成以下任务：

⑴ 对实验一中所定义的5个数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后执行以下2个操作，观察并记录实体完整性的检查和违约处理。

① insert  into  材料消耗表  values('zy2023001','wm004',100)

② insert  into  材料消耗表  values('zy2023002',NULL,200)

⑵ 对实验一中所定义的5个数据表增加相应的参照完整性约束，并观察在数据表中存在数据的情况下参照完整性约束是否创建成功，然后执行以下操作，观察并记录参照完整性的检查和违约处理。

① 将（y007   油井   112203002）插入到油水井表。

② insert  into  材料消耗表  values('zy2023007','wm006',100)

③ 将单位表中的（112202002  采油二矿二队）删除，查看油水井表和成本表中的数据有何变化。

④ 将材料表中的（wm004 材料四 袋 10）修改为（wm04 材料四 袋 10）。

⑤ 撤销上述成功的更新操作。

⑶ 对实验一中所定义的5个数据表按以下要求增加相应的完整性约束条件，并观察在数据表中存在数据的情况下完整性约束是否创建成功。

① 单位表的单位名称不能取空值、且取值唯一。

② 油水井表的井别只允许取“油井”或“水井”，单位代码不能取空值。

③ 材料表的名称不能取空值、且取值唯一，计量单位不能取空值。

④ 材料消耗表的消耗数量不能取空值。

⑤ 对成本表根据实际应用的要求定义适当的用户定义的完整性约束条件。

3．完成以下操作：

⑴ 定义视图V1，用于保存成本表和材料消耗表的全部列。

⑵ 查询上面定义的视图V1，可任意组合查询条件，构造出2个查询。

⑶ 定义一个反映成本表预算状态的视图V2，并向该视图插入('zy2023008','112202002','y005',10000,'张三', '2023-07-02')，查看成本表的数据有何变化。

⑷ 撤销上述成功的更新操作。

四、实验报告

提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。

# 1.1向在实验二中所定义的数据表增加“备注”列，其数据类型为字符型，并查看新增列的值。
alter table 汇总表 add 备注 varchar(20);
select * from 汇总表
# 1.2对上述数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功，然后再次执行实验二中实验内容3-2的操作，并观察记录执行结果。
alter table 汇总表
add primary key(施工单位,年月);
# 子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中：
insert into 汇总表(施工单位,年月,结算金额)
select 施工单位,date_format(结算日期, '%Y%m'),sum(结算金额) from 成本消耗表
group by `施工单位`,date_format(结算日期, '%Y%m');
select * from 汇总表;
# 1.3-1删除上述数据表中的数据，然后再删除该数据表，对这两个操作进行比较。
delete from 汇总表
select * from 汇总表;
# 1.3-2删除上述数据表中的数据，然后再删除该数据表，对这两个操作进行比较。
drop table 汇总表
select * from 汇总表;
# 2.1对实验一中所定义的5个数据表增加主码约束条件，并观察在数据表中存在数据的情况下主码约束是否创建成功
# 然后执行以下2个操作，观察并记录实体完整性的检查和违约处理。
alter table 材料表 alter column 物码 varchar(20) not null;
alter table 材料表 add constraint 主码 primary key (物码);
alter table 材料消耗表 alter column 物码 varchar(20) not null;
alter table 材料消耗表 alter column 单据号 varchar(20) not null;
alter table 材料消耗表 add constraint 主码_材料消耗表 primary key (单据号,物码);
alter table 单位表 alter column 单位代码 varchar(20) not null;
alter table 单位表 add constraint 主码_单位表 primary key (单位代码);
alter table 油水井表 alter column 井号 varchar(20) not null;
alter table 油水井表 add constraint 主码_油水井表 primary key(井号);
alter table 成本消耗表 alter column 单据号 varchar(20) not null;
alter table 成本消耗表 add constraint 主码_成本消耗表 primary key(单据号);
# 2.1.1insert  into  材料消耗表  values('zy2023001','wm004',100)
insert  into  材料消耗表  values('zy2023001','wm004',100)
# 2.1.2insert  into  材料消耗表  values('zy2023002',NULL,200)
insert  into  材料消耗表  values('zy2023002',NULL,200)
# 2.2.0对实验一中所定义的5个数据表增加相应的参照完整性约束，并观察在数据表中存在数据的情况下参照完整性约束是否创建成功，然后执行以下操作，观察并记录参照完整性的检查和违约处理。
alter table 材料消耗表 add foreign key(单据号) references 成本消耗表(单据号);
alter table 材料消耗表 add foreign key(物码) references 材料表(物码);
alter table 油水井表 add foreign key(单位代码) references 单位表(单位代码);
alter table 成本消耗表 add foreign key(预算单位) references 单位表(单位代码);
alter table 成本消耗表 add foreign key(井号) references 油水井表(井号);
# 2.2.1将（y007  油井  112203002）插入到油水井表。
begin
insert into 油水井表 values('y007','油井','112203002');
# 2.2.2insert  into  材料消耗表  values('zy2023007','wm006',100)
insert  into  材料消耗表  values('zy2023007','wm006',100);
# 2.2.3将单位表中的（112202002  采油二矿二队）删除，查看油水井表和成本表中的数据有何变化。
delete from 单位表 where 单位代码 ='112202002' and
单位名称 = '采油二矿二队'
# 2.2.4将材料表中的（wm004 材料四 袋 10）修改为（wm04 材料四 袋 10）。
update 材料表 set 物码 = 'wm04' where '物码' = 'wm004';
# 2.2.5撤销上述成功的更新操作。
rollback;
# 2.3.0对实验一中所定义的5个数据表按以下要求增加相应的完整性约束条件，并观察在数据表中存在数据的情况下完整性约束是否创建成功。
# 2.3.1单位表的单位名称不能取空值、且取值唯一。
alter table 单位表 alter column 单位名称 varchar(12) not null;
alter table 单位表 add unique(单位名称);
# 2.3.2油水井表的井别只允许取“油井”或“水井”，单位代码不能取空值。
alter table 油水井表 add check(井别 = '油井' or 井别 = '水井');
alter table 油水井表 alter column 单位代码 varchar(20) not null;
# 2.3.3材料表的名称不能取空值、且取值唯一，计量单位不能取空值。
alter table 材料表 alter column 名称 varchar(20) not null; 
alter table 材料表 add unique(名称);
alter table 材料表 alter column 计量单位 varchar(20) not null;
# 2.3.4材料消耗表的消耗数量不能取空值。
alter table 材料消耗表 alter column 消耗数量 int not null;
# 2.3.5对成本表根据实际应用的要求定义适当的用户定义的完整性约束条件。
# 假设用户规定成本消耗标的预算金额不能为空
alter table 成本消耗表 alter column 预算金额 varchar(20) not null; 
# 3.1定义视图V1，用于保存成本表和材料消耗表的全部列。
create view V1 as select 成本消耗表.*,材料消耗表.消耗数量,材料消耗表.物码 
from 成本消耗表,材料消耗表 
where 成本消耗表.单据号 = 材料消耗表.单据号;
# 3.2查询上面定义的视图V1，可任意组合查询条件，构造出2个查询。
# 查询1——查询视图V1中井号为y001的全部列
select * from V1 where 井号 = 'y001';
# 查询2——查询视图V1中施工单位为作业公司作业二队的项目的单据号和预算单位
select 单据号,预算单位 from V1 where 施工单位 = '作业公司作业二队';
# 3.3定义一个反映成本表预算状态的视图V2，并向该视图插入('zy2023008','112202002','y005',10000,'张三', '2023-07-02')，查看成本表的数据有何变化。
create view V2 as select 单据号,预算单位,井号,预算金额,预算人,预算日期
from 成本消耗表;
begin;
insert into V2 values('zy2023008','112202002','y005',10000,'张三', '2023-07-02');
select * from V2;
# 3.4撤销上述成功的更新操作。
rollback;
select * from V2;

实验四 其它数据库对象的管理

一、实验目的

    1．掌握事务的概念、性质、定义及使用；

    2．掌握游标的概念、组成、创建及使用；

    3．掌握存储过程的概念、类型、特点、创建、执行及管理。

    4．掌握触发器的概念、创建、管理及使用。

二、实验学时

4学时

三、实验内容

    1．进行如下事务处理练习（把下列五条语句作为一个事务处理，只有五条语句全部成功执行才做提交，并给出成功的提示信息；否则就做回退处理，并给出具体的错误提示信息）：

insert into 成本表 values('zy2023006','112202002','y005',

10000,'张三', '07-01-2023' ,'07-04-2023','07-25-2023',

'作业公司作业一队','堵漏',7000,2500,1000,1400,11900,

'李四','07-26-2023',11900,'王五','07-28-2023')

insert into材料消耗表values('zy2023006','wm001',200)

insert into材料消耗表values('zy2023006','wm002',200)

insert into材料消耗表values('zy2023006','wm003',200)

insert into材料消耗表values('zy2023006','wm004',100)

2．进行如下游标练习：

定义一个游标，用于存放成本表的全部行数据，并打印以下表头和各行数据。

表头：单据号 预算单位 井号 预算金额 预算人 预算日期 开工日期 完工日期 施工单位 施工内容 材料费 人工费 设备费 其它费用 结算金额 结算人 结算日期 入账金额 入账人 入账日期

执行以上所定义的游标，查看是否能正确输出结果。

3．定义一个存储过程，要求完成以下功能：

生成某单位(单位可以是采油厂或采油矿或采油队)某段时间内的成本运行情况（输入参数：单位代码 起始日期 结束日期）。

输出格式    ***单位**时间---**时间成本运行情况

预算金额  结算金额  入账金额  未结算金额  未入账金额

 ****.**   ****.**    ****.**    ****.**     ****.**

其中：未结算金额=预算金额-结算金额

      未入账金额=结算金额-入账金额

分三种情况（单位分别为：采油厂、采油矿、采油队）执行以上定义的存储过程，查看执行输出结果。

4．针对成本表定义三个触发器，分别完成以下功能：

⑴ 对成本表插入一行数据时，自动计算并插入结算金额字段（结算金额=材料费+人工费+设备费+其它费用）。

⑵ 当修改成本表的某行数据时自动修改结算金额字段。

⑶ 当删除成本表中一行数据时，自动删除材料消耗表中相应明细数据。

⑷ 对上述3个触发器用适当的更新语句进行验证，并查看结果是否达到预期结果。

四、实验报告

提交实验内容中用SQL语句完成的题目的SQL语句文档及相应的执行结果。

#1．进行如下事务处理练习（把下列五条语句作为一个事务处理，只有五条语句全部成功执行才做提交，
# 并给出成功的提示信息；否则就做回退处理，并给出具体的错误提示信息）：
-- 开始事务
DROP PROCEDURE IF EXISTS process_transaction;
DELIMITER //
CREATE PROCEDURE process_transaction()
BEGIN
    START TRANSACTION;
    BEGIN
		insert into 成本消耗表 values
		('zy2023006','112202002','y005',10000,'张三', '2023-07-01' ,'2023-07-04','2023-07-25','作业公司作业一队','堵漏',7000,null,null,null,null,2500,1000,1400,11900,'李四','2023-07-26',11900,'王五','2023-07-28');
        insert into 材料消耗表 values ('zy2023006', 'wm001', 200);
        insert into 材料消耗表 values ('zy2023006', 'wm002', 200);
        insert into 材料消耗表 values ('zy2023006', 'wm003', 200);
        insert into 材料消耗表 values ('zy2023006', 'wm004', 100);
    END;
    IF (SELECT COUNT(*) FROM 成本消耗表 WHERE 单据号 = 'zy2023006') > 0 THEN
        -- 提交事务
        COMMIT;
        SELECT '事务执行成功' AS result;
    ELSE
        -- 回滚事务
        ROLLBACK;
        SELECT '事务执行失败' AS result;
    END IF;
END //
DELIMITER ;
CALL process_transaction();
# 2．进行如下游标练习：
# 定义一个游标，用于存放成本表的全部行数据，并打印以下表头和各行数据。
# -- 定义存储过程
-- 删除之前定义的存储过程
DROP PROCEDURE IF EXISTS print_cost_table;
-- 创建存储过程
-- 定义存储过程
DELIMITER //
CREATE PROCEDURE print_cost_table()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id varchar(20);
    DECLARE cur_budget_unit varchar(20);
    DECLARE cur_well varchar(20);
    DECLARE cur_budget_amount decimal(10,2);
    DECLARE cur_budget_person varchar(20);
    DECLARE cur_budget_date date;
    DECLARE cur_start_date date;
    DECLARE cur_end_date date;
    DECLARE cur_construction_unit varchar(20);
    DECLARE cur_construction_content varchar(20);
    DECLARE cur_material_cost decimal(10,2);
    DECLARE cur_labor_cost decimal(10,2);
    DECLARE cur_equipment_cost decimal(10,2);
    DECLARE cur_other_cost decimal(10,2);
    DECLARE cur_settlement_amount decimal(10,2);
    DECLARE cur_settlement_person varchar(20);
    DECLARE cur_settlement_date date;
    DECLARE cur_accounting_amount decimal(10,2);
    DECLARE cur_accounting_person varchar(20);
    DECLARE cur_accounting_date date;
    -- 声明游标
    DECLARE cur_cost_table CURSOR FOR
        SELECT
            单据号, 预算单位, 井号, 预算金额, 预算人, 预算日期,
            开工日期, 完工日期, 施工单位, 施工内容, 材料费,
            人工费, 设备费, 其他费用, 结算金额, 结算人,
            结算日期, 入账金额, 入账人, 入账日期
        FROM 成本消耗表;
    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 打印表头
    SELECT
        '单据号', '预算单位', '井号', '预算金额', '预算人', '预算日期',
        '开工日期', '完工日期', '施工单位', '施工内容', '材料费',
        '人工费', '设备费', '其他费用', '结算金额', '结算人',
        '结算日期', '入账金额', '入账人', '入账日期';
	  -- 进入游标
    OPEN cur_cost_table;
    read_loop: LOOP
        FETCH cur_cost_table INTO
            cur_id, cur_budget_unit, cur_well, cur_budget_amount, cur_budget_person,
            cur_budget_date, cur_start_date, cur_end_date, cur_construction_unit,
            cur_construction_content, cur_material_cost, cur_labor_cost,
            cur_equipment_cost, cur_other_cost, cur_settlement_amount,
            cur_settlement_person, cur_settlement_date, cur_accounting_amount,
            cur_accounting_person, cur_accounting_date;
        -- 判断是否取到数据
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 打印数据
        SELECT
            cur_id, cur_budget_unit, cur_well, cur_budget_amount, cur_budget_person,
            cur_budget_date, cur_start_date, cur_end_date, cur_construction_unit,
            cur_construction_content, cur_material_cost, cur_labor_cost,
            cur_equipment_cost, cur_other_cost, cur_settlement_amount,
            cur_settlement_person, cur_settlement_date, cur_accounting_amount,
            cur_accounting_person, cur_accounting_date;
    END LOOP;
    -- 关闭游标
    CLOSE cur_cost_table;
END //
DELIMITER ;
-- 调用存储过程
CALL print_cost_table();
# 3．定义一个存储过程，要求完成以下功能：生成某单位(单位可以是采油厂或采油矿或采油队)某段时间内的成本运行情况（输入参数：单位代码 起始日期 结束日期）。
DELIMITER //
CREATE PROCEDURE generate_cost_report(IN unit_code CHAR(9), IN start_date DATE, IN end_date DATE)
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_budget_amount DECIMAL(10,2);
    DECLARE cur_settlement_amount DECIMAL(10,2);
    DECLARE cur_accounting_amount DECIMAL(10,2);
    DECLARE cur_unsettled_amount DECIMAL(10,2);
    DECLARE cur_unaccounted_amount DECIMAL(10,2);
    -- 声明游标
    DECLARE cur_cost_table CURSOR FOR
        SELECT
            预算金额, 结算金额, 入账金额
        FROM 成本消耗表
        WHERE 预算单位 IN (SELECT 单位代码 FROM 单位表 WHERE 单位代码 = unit_code OR 单位代码 LIKE CONCAT(unit_code, '%'))
        AND 预算日期 >= start_date AND 预算日期