实验资料 实验源码

实验目的

通过上机练习掌握数据库编程技术

实验原理

  1. 用SQL进行高级数据库操作;
  2. 用JDBC编程连接数据库并进行操作。

实验内容

详情见实验指导书

实验步骤

Phase1

  1. 按实验要求建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE TABLE employees (
emp_no INT NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender CHAR(1) NOT NULL,
hire_date DATE NOT NULL,
CONSTRAINT pk_employees PRIMARY KEY (emp_no)
);
CREATE TABLE departments (
dept_no CHAR(4) NOT NULL,
dept_name VARCHAR(40) NOT NULL,
CONSTRAINT pk_departments PRIMARY KEY (dept_no)
);
CREATE TABLE dept_emp (
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
CONSTRAINT pk_dept_emp PRIMARY KEY (emp_no, dept_no),
CONSTRAINT fk_dept_emp_employees FOREIGN KEY (emp_no) REFERENCES employees(emp_no),
CONSTRAINT fk_dept_emp_departments FOREIGN KEY (dept_no) REFERENCES departments(dept_no)
);
CREATE TABLE dept_manager (
dept_no CHAR(4) NOT NULL,
emp_no INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
CONSTRAINT pk_dept_manager PRIMARY KEY (emp_no, dept_no),
CONSTRAINT fk_dept_manager_employees FOREIGN KEY (emp_no) REFERENCES employees(emp_no),
CONSTRAINT fk_dept_manager_departments FOREIGN KEY (dept_no) REFERENCES departments(dept_no)
);
CREATE TABLE titles (
emp_no INT NOT NULL,
title VARCHAR(50) NOT NULL,
from_date DATE NOT NULL,
to_date DATE,
CONSTRAINT pk_titles PRIMARY KEY (emp_no, title, from_date),
CONSTRAINT fk_titles_employees FOREIGN KEY (emp_no) REFERENCES employees (emp_no)
);
CREATE TABLE salaries (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
CONSTRAINT pk_salaries PRIMARY KEY (emp_no, from_date),
CONSTRAINT fk_salaries_employees FOREIGN KEY (emp_no) REFERENCES employees(emp_no)
);
  1. 将提供的数据导入到已创建的表中
1
2
3
4
5
6
7
8
9
10
11
12
13
COPY employees FROM '/root/resources/data_employees.txt'delimiter ',';
COPY titles FROM '/root/resources/data_titles.txt'delimiter ',';
COPY salaries FROM '/root/resources/data_salaries.txt'delimiter ',';
COPY departments FROM '/root/resources/data_departments.txt'delimiter ',';
COPY dept_emp FROM '/root/resources/data_dept_emp.txt'delimiter ',';
COPY dept_manager FROM '/root/resources/data_dept_manager.txt'delimiter ',';
-- 插入数据后可用SQL语句查询结果
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM titles;
SELECT COUNT(*) FROM salaries;
SELECT COUNT(*) FROM departments;
SELECT COUNT(*) FROM dept_emp;
SELECT COUNT(*) FROM dept_manager;
  1. 将表departments复制到新表中
1
CREATE TABLE departments_copy AS (SELECT * FROM departments);
  1. 新建log表存修改记录
1
2
3
4
5
6
7
8
9
10
CREATE TABLE departments_copy_log
(
log_id SERIAL, -- 日志流水编号(自增)
login_name VARCHAR(256), -- 登录名
update_date TIMESTAMP WITH TIME ZONE, -- 修改时间
dept_no CHAR(4), -- 部门编号
dept_name_old VARCHAR(40), -- 部门名称的旧值
dept_name_new VARCHAR(40), -- 部门名称的新值
CONSTRAINT departments_copy_log_pk PRIMARY KEY(log_id)
);
  1. 编写触发器,实现修改部门名称时,将数据库用户登录名、修改时间、部门编号、部门名称的旧值、部门名称的新值记录到departments_copy_log表中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Sequence
CREATE SEQUENCE adder
START WITH 1
INCREMENT BY 1;
-- Function
create or replace function changeLog() returns trigger as $departments$
begin
insert into departments_copy_log(log_id,login_name,update_date,dept_no,dept_name_old,dept_name_new)
values(DEFAULT,'PYZ',now(),old.dept_no,old.dept_name,new.dept_name);
return null;
end;
$departments$ language plpgsql;

create trigger changelogtrigger
after update of dept_name on departments
for each row
execute procedure changeLog();
  1. 执行查询语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
UPDATE departments
SET dept_name = CONCAT(dept_name, ' Dept')
WHERE dept_no = 'd005';

UPDATE departments
SET dept_name = CONCAT(dept_name, ' Dept')
WHERE dept_no = 'd006';

UPDATE departments
SET dept_name = CONCAT(dept_name, ' Dept')
WHERE dept_no = 'd007';

UPDATE departments
SET dept_name = CONCAT(dept_name, ' Dept')
WHERE dept_no = 'd008';
  1. 查询结果
1
SELECT * FROM departments_copy_log;

Phase2

  1. 按要求建立视图
1
2
3
create view finance_employees_view 
as select emp_no,first_name,last_name,birth_date,hire_date
from employees;
  1. 查询视图的前10行
1
SELECT * FROM finance_employees_view LIMIT 10;

Phase3

阶段三的目的是为了体现出索引的高效率查找

  • 建立索引
1
2
3
4
create index employees_first_name_index 
on employees(first_name);
create index employees_last_name_index
on employees(last_name);
  • 查看查询时间
1
2
3
4
EXPLAIN ANALYZE
SELECT *
FROM employees
WHERE first_name='Peternela' AND last_name='Anick';

Phase4

与阶段三相似,除了索引,还涉及到了主键外键对查询时间的影响

  • 建立索引
1
2
3
4
create index employees_first_name_index 
on employees(first_name);
create index employees_last_name_index
on employees(last_name);
  • 删除/新建-主外键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- drop foreign keys
ALTER TABLE salaries DROP CONSTRAINT fk_salaries_employees;
ALTER TABLE titles DROP CONSTRAINT fk_titles_employees;
ALTER TABLE dept_emp DROP CONSTRAINT fk_dept_emp_employees;
ALTER TABLE dept_emp DROP CONSTRAINT fk_dept_emp_departments;
ALTER TABLE dept_manager DROP CONSTRAINT fk_dept_manager_employees;
ALTER TABLE dept_manager DROP CONSTRAINT fk_dept_manager_departments;

-- drop primary keys
ALTER TABLE employees DROP CONSTRAINT pk_employees;
ALTER TABLE departments DROP CONSTRAINT pk_departments;
ALTER TABLE dept_emp DROP CONSTRAINT pk_dept_emp;
ALTER TABLE salaries DROP CONSTRAINT pk_salaries;

-- add primary keys
ALTER TABLE employees ADD CONSTRAINT pk_employees PRIMARY KEY(emp_no);
ALTER TABLE departments ADD CONSTRAINT pk_departments PRIMARY KEY(dept_no);
ALTER TABLE dept_emp ADD CONSTRAINT pk_dept_emp PRIMARY KEY(emp_no, dept_no);
ALTER TABLE salaries ADD CONSTRAINT pk_salaries PRIMARY KEY(emp_no, from_date);

-- add foreign keys
ALTER TABLE salaries ADD CONSTRAINT fk_salaries_employees FOREIGN KEY (emp_no) REFERENCES employees(emp_no);
ALTER TABLE titles ADD CONSTRAINT fk_titles_employees FOREIGN KEY (emp_no) REFERENCES employees (emp_no);
ALTER TABLE dept_emp ADD CONSTRAINT fk_dept_emp_employees FOREIGN KEY (emp_no) REFERENCES employees(emp_no);
ALTER TABLE dept_emp ADD CONSTRAINT fk_dept_emp_departments FOREIGN KEY (dept_no) REFERENCES departments(dept_no);
ALTER TABLE dept_manager ADD CONSTRAINT fk_dept_manager_employees FOREIGN KEY (emp_no) REFERENCES employees(emp_no);
ALTER TABLE dept_manager ADD CONSTRAINT fk_dept_manager_departments FOREIGN KEY (dept_no) REFERENCES departments(dept_no);
  • 查看查询时间
1
2
3
4
5
6
7
EXPLAIN ANALYZE
SELECT d.dept_no, d.dept_name, e.emp_no, e.first_name, e.last_name, s.salary
FROM departments AS d
INNER JOIN dept_emp AS de ON d.dept_no=de.dept_no
INNER JOIN employees AS e ON de.emp_no=e.emp_no
INNER JOIN salaries AS s ON e.emp_no=s.emp_no
WHERE e.first_name='Peternela' AND e.last_name='Anick';

Phase5

  1. 按要求创建存储方法
1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION calc_avg_salary_for_emp_no(IN emp_no_in INT,OUT avg INT)
RETURNS INT AS $avg$
BEGIN
SELECT AVG(salary)
INTO avg
FROM salaries
WHERE emp_no=emp_no_in;
RETURN;
END
$avg$ LANGUAGE plpgsql;
  1. 执行下列语句
1
2
3
4
5
6
7
8
do language plpgsql $$
declare res NUMERIC;
begin
select * from calc_avg_salary_for_emp_no(10002) into res;
raise notice '%',res;
end $$;
//执行结果为:68854.5
//与答案相同
  1. 删除存储过程
1
DROP FUNCTION calc_avg_salary_for_emp_no;

Phase6

  1. 按要求创建存储过程
1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION is_manager(IN emp_no_in INT)
RETURNS BOOLEAN AS $$
DECLARE cnt INT;
BEGIN
SELECT COUNT(*) INTO cnt
FROM dept_manager
WHERE emp_no=emp_no_in;
IF(cnt=0) THEN RETURN FALSE;
ELSE RETURN TRUE;
END IF;
END;
$$ LANGUAGE plpgsql;
  1. 执行下列语句
1
2
3
4
SELECT is_manager(110022) AS is_manager;
-- true
SELECT is_manager(100002) AS is_manager;
-- false
  1. 删除函数
1
DROP FUNCTION is_manager;

Phase7

  1. 按要求创建存储方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE OR REPLACE FUNCTION calc_avg_and_var_salary_for_emp_no(
IN emp_no_in INTEGER,
OUT avg REAL,
OUT var REAL)
RETURNS RECORD AS $$
DECLARE
cur_salary CURSOR(cur_emp_no INTEGER)
FOR SELECT salary FROM salaries WHERE cur_emp_no=emp_no;
sum REAL;
cnt INT;
temp REAL;
BEGIN
SELECT AVG(salary) INTO avg
FROM salaries
WHERE emp_no=emp_no_in;
sum:=0;
cnt:=0;
temp:=0;
OPEN cur_salary(emp_no_in);
WHILE(FOUND)LOOP
FETCH FROM cur_salary INTO temp;
EXIT WHEN NOT FOUND;
cnt:=cnt+1;
sum:=sum+abs(temp-avg)*abs(temp-avg);
END LOOP;
var:=sum/cnt;
CLOSE cur_salary;
END;
$$ LANGUAGE plpgsql;
  1. 执行下列语句
1
2
3
4
5
6
7
8
9
10
11
12
do language plpgsql $$
declare avg_salary REAL;
declare var_salary REAL;
begin
SELECT * FROM calc_avg_and_var_salary_for_emp_no(10002) INTO avg_salary, var_salary;
raise notice 'avg:%', avg_salary;
raise notice 'var:%', var_salary;
end $$;
//执行结果:
//avg:68854.5 ans:68854.5
//var:7.1651755e+06 ans:7165175.583333015
//与答案相同
  1. 删除存储方法
1
DROP FUNCTION calc_avg_and_var_salary_for_emp_no;

实验思考

在这次实验中使用了触发器,函数,存储过程,视图,序列,游标,索引。对数据库的操作与原理有了更深一层的认知。知道分别在什么情况下需要使用触发器,函数和存储过程。了解了他们的区别。函数一般只有一个返回值,而过程可以影响多个变量。通过建立视图来建立一个虚拟的表。当然也有物化视图。对视图的操作和对表的操作基本相同。通过建立索引减少查询语句所花费的时间,实验过程中查询了很多情况下同样的查询语句所消耗的时间,发现建立索引可以很大程度上减少时间的开销。与直接在原有的表中搜索,运用索引可以有效地帮助我们更快得获取有用的信息。经过此次实验,更好地掌握了SQL语言的语法结构,SQL是一种非常严谨的语言,不能出现任何一点纰漏。数据库是一个项目中非常重要的一环。懂得如何处理数据库中所遇到的问题至关重要。在不同的情况下选择用不同的方法解决相应的问题大大减少了维护与改善数据库的精力。今后要从事计算机行业,应该不断锻炼自己,前后端结合,综合发展。不能只学前端也不能只学后端。后端的数据库是必不可少的一个学科。参加实践的过程中应找机会锻炼自己对数据库的维护与开发。更深层次的理解数据库的原理与结构。数据库是DBMS的最新技术,是目前计算机科学的重要分支。反观这学期对数据库的学习以及实验中收获的对数据库的理解和对数据库的操作经验,后者有助于前者的理解,前者指导我实践后者。现在可以更加灵活自如地运用这些指令。以上是本次实验的实验总结。