《数据库原理》上机实验：SQL实验2（详细代码+截图）

实验二 SQL练习2

一、实验目的

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

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

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

二、实验学时

2学时

三、实验内容

1．完成以下操作：

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

CREATE INDEX Pro_date_index ON Project_table(Pro_date);

CREATE INDEX All_exp_date_index ON Project_table(All_exp_dete);

CREATE INDEX Exp_date_index ON Project_table(Exp_data);

 

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

2．完成以下操作：

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

select * from Project_table

where Pro_date >='2020-5-1' AND Pro_date <='2020-5-28'

      AND Pro_unit =

         (select Unit_code

          from Unit_table

          where Unit_name ='采油一矿二队'

          );

 

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

select * from Project_table, Unit_table

where Pro_unit = Unit_code AND Unit_name = '采油一矿二队'

      AND All_exp_date between '2020-5-1' and '2020-5-28';

在实际实现过程，发现一开始自己把列名定义为英文，操作起来十分费劲。为此，决定把列名改为中文的，以方便操作，具体如下：

RENAME TABLE Project_table TO 成本表;

 

RENAME TABLE Unit_table TO 单位表;

RENAME TABLE Oilwell_table TO 油水井表;

RENAME TABLE Material_table TO 材料表;

 

ALTER TABLE 成本表 CHANGE Pro_no 单据号 CHAR(20);

ALTER TABLE 成本表 CHANGE Pro_unit 预算单位 CHAR(20);

ALTER TABLE 成本表 CHANGE Well_no 井号 CHAR(20);

ALTER TABLE 成本表 CHANGE Pro_Price 预算金额 FLOAT(10);

ALTER TABLE 成本表 CHANGE Pro_Peo 预算人 CHAR(20);

ALTER TABLE 成本表 CHANGE Pro_date 预算日期 DATE;

ALTER TABLE 成本表 CHANGE Pro_begin 开工日期 DATE;

ALTER TABLE 成本表 CHANGE Pro_end 完工日期 DATE;

ALTER TABLE 成本表 CHANGE Pro_con 施工单位 CHAR(20);

ALTER TABLE 成本表 CHANGE Pro_con_con 施工内容 CHAR(30);

ALTER TABLE 成本表 CHANGE Mata_exp 材料费 FLOAT(10);

ALTER TABLE 成本表 CHANGE Mata_exp1 材料一 FLOAT(10);

ALTER TABLE 成本表 CHANGE Mata_exp2 材料二 FLOAT(10);

ALTER TABLE 成本表 CHANGE Mata_exp3 材料三 FLOAT(10);

ALTER TABLE 成本表 CHANGE Mata_exp4 材料四 FLOAT(10);

ALTER TABLE 成本表 CHANGE Peo_exp 人工费 FLOAT(10);

ALTER TABLE 成本表 CHANGE Equ_exp 设备费 FLOAT(10);

ALTER TABLE 成本表 CHANGE Oth_exp 其他费用 FLOAT(10);

ALTER TABLE 成本表 CHANGE All_exp 结算金额 FLOAT(10);

ALTER TABLE 成本表 CHANGE All_exp_peo 结算人 CHAR(10);

ALTER TABLE 成本表 CHANGE All_exp_dete 结算日期 DATE;

ALTER TABLE 成本表 CHANGE All_exp_in 入账金额 FLOAT(10);

ALTER TABLE 成本表 CHANGE Exp_Peo 入账人 CHAR(10);

ALTER TABLE 成本表 CHANGE Exp_data 入账日期 DATE;

 

接着修改索引：

CREATE INDEX Budget_date_index ON 成本表(预算日期);

CREATE INDEX Settlement_date_index ON 成本表(结算日期);

CREATE INDEX Billing_date_index ON 成本表(入账日期);

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

SELECT  * FROM 单位表,成本表

WHERE 单据号 IN

      (SELECT 单据号

       FROM 成本表,单位表

       WHERE 预算单位 = 单位代码

             AND 结算日期 BETWEEN '2020-5-1' AND '2020-5-28'

             AND 单位名称 = '采油一矿二队'

      );

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

SELECT * FROM 成本表,单位表

WHERE 预算单位 = 单位代码

      AND 单位名称 = '采油一矿二队'

      AND 入账日期 BETWEEN '2020-5-1' AND '2020-5-28';

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

SELECT SUM(预算金额) FROM 成本表,单位表

WHERE 预算单位 = 单位代码

      AND 单位名称 = '采油一矿二队'

      AND 预算日期 BETWEEN '2020-5-1' AND '2020-5-28';

 

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

SELECT SUM(结算金额) FROM 成本表,单位表

WHERE 预算单位 = 单位代码

      AND 单位名称 = '采油一矿二队'

      AND 结算日期 BETWEEN '2020-5-1' AND '2020-5-28';

 

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

SELECT SUM(入账金额) FROM 成本表,单位表

WHERE 预算单位 = 单位代码

      AND 单位名称 = '采油一矿二队'

      AND 入账日期 BETWEEN '2020-5-1' AND '2020-5-28';

 

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

SELECT SUM(入账金额) FROM 成本表,单位表

WHERE 预算单位 = 单位代码

      AND 单位名称 LIKE '采油一矿%'

      AND 入账日期 BETWEEN '2020-5-1' AND '2020-5-28';

 

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

SELECT DISTINCT 入账人 FROM 成本表

WHERE 入账日期 IS NOT NULL;

 

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

SELECT 单据号 FROM 成本表

WHERE 结算日期 BETWEEN '2020-5-1' AND '2020-5-28'

      AND 入账日期 IS NULL;

 

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

SELECT 单据号,入账金额 FROM 成本表,单位表

WHERE 预算单位 = 单位代码

      AND 单位名称 = '采油一矿二队'

ORDER BY 入账金额 DESC;

 

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

SELECT 单位名称, COUNT(结算金额), SUM(结算金额)

FROM 成本表,单位表

WHERE 预算单位 = 单位代码

GROUP BY 单位名称;

 

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

SELECT * FROM 材料消耗表

WHERE 物码 IN

      (SELECT 物码 FROM 材料表

       WHERE 名称 = '材料三'

             AND 单价*消耗数量>=2000

      );

 

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

SELECT 单据号 FROM 成本表

WHERE 施工单位 = '作业公司作业二队';

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

SELECT 单据号 FROM 成本表

WHERE 施工单位 = '作业公司作业一队'

UNION

SELECT 单据号 FROM 成本表

WHERE 施工单位 = '作业公司作业二队';

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

SELECT 施工单位 FROM 成本表

WHERE 井号 IN

      (SELECT 井号 FROM 油水井表

       WHERE 井别 = '油井'

       AND 单位代码 IN

           (SELECT 单位代码 FROM 单位表

              WHERE 单位名称 LIKE '采油一矿%'

             )

      );

 

最后删除第1题建立起来的索引；

DROP INDEX Budget_date_index ON 成本表;

DROP INDEX Settlement_date_index ON 成本表;

DROP INDEX Billing_date_index ON 成本表;

3．完成以下操作：

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

CREATE TABLE 汇总表

       (施工单位 VARCHAR(50),

        年月 VARCHAR(50),

          结算金额 DECIMAL(10, 2)

       );

 

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

BEGIN TRANSACTION

DELETE

FROM 成本表

WHERE 预算单位 IN

      (SELECT 单位代码 FROM 单位表

       WHERE 单位名称 LIKE '采油一矿%'

      );

ROLLBACK;

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

INSERT INTO 汇总表(施工单位,年月,结算金额)

       SELECT 单位名称,YEAR(结算日期)*100+MONTH(结算日期), SUM(结算金额)

       FROM 成本表,单位表

       WHERE 预算单位 = 单位代码

       GROUP BY 单位名称,YEAR(结算日期)*100+MONTH(结算日期);

 

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

START TRANSACTION;

UPDATE 成本表

SET 结算人='李兵'

WHERE 预算单位 IN

      (SELECT 单位代码 FROM 单位表

       WHERE 单位名称 LIKE '采油一矿%'

      );

 

SELECT * FROM 成本表;

 

ROLLBACK;

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

DELETE

FROM 成本表

WHERE 预算单位 IN

      (SELECT 单位代码 FROM 单位表

       WHERE 单位名称 LIKE '采油一矿%'

      );

⑸ 撤消上述两个操作。

ROLLBACK;