实验一 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 成本消耗表;
评论区