实验目的

  1. 通过上机练习巩固关系数据模型及其基本概念;
  2. 通过上机练习巩固关系数据库语言SQL;
  3. 通过上机练习巩固关系数据库设计方法。

实验原理

  1. 采用PostgreSQL数据库作为实验用DBMS;
  2. 用E/R图建立数据库的概念模型;
  3. 将E/R模型转换为关系模型;
  4. 用SQL创建数据库模式;
  5. 将数据批量装载到数据库中;
  6. 用SQL进行查询和更新操作。

实验内容

详情请查看实验指导书

实验步骤

  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
CREATE TABLE employees(
emp_no INT,
birth_date DATE,
first_name VARCHAR,
last_name VARCHAR,
gender VARCHAR,
hire_date DATE,
CONSTRAINT pk_employees PRIMARY KEY(emp_no)
);
CREATE TABLE titles(
emp_no_IN_titles INT,
title VARCHAR,
from_date DATE,
to_date DATE,
CONSTRAINT fk_titles_employees FOREIGN KEY(emp_no_IN_titles) REFERENCES employees(emp_no)
ON DELETE SET NULL
ON UPDATE SET NULL
);
CREATE TABLE salaries(
emp_no_IN_salaries INT,
salary INT,
from_date DATE,
to_date DATE,
CONSTRAINT fk_salaries_employees FOREIGN KEY(emp_no_IN_salaries) REFERENCES employees(emp_no)
);
CREATE TABLE departments(
dept_no VARCHAR,
dept_name VARCHAR,
CONSTRAINT pk_departments PRIMARY KEY(dept_no)
);
CREATE TABLE dept_emp(
emp_no_IN_dept_emp INT,
dept_no_IN_dept_emp VARCHAR,
from_date DATE,
to_date DATE
);
CREATE TABLE dept_manager(
dept_no_IN_dept_manager VARCHAR,
salary INT,
from_date DATE,
to_date DATE,
CONSTRAINT fk_dept_manager_departments FOREIGN KEY(dept_no_IN_dept_manager) REFERENCES departments(dept_no)
);
  1. 导入数据
1
2
3
4
5
6
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 ',';
  1. 查看是否导入成功
1
2
3
4
5
6
SELECT COUNT(*) FROM employees;--Correct
SELECT COUNT(*) FROM titles;--Correct
SELECT COUNT(*) FROM salaries;--Correct
SELECT COUNT(*) FROM departments;--Correct
SELECT COUNT(*) FROM dept_emp;--Correct
SELECT COUNT(*) FROM dept_manager;--Correct
  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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
-- 1
-- 返回前10行员工数据。
-- (提示:用LIMIT关键字,具体用法查文档)
SELECT *
FROM employees
LIMIT 10;

-- 2
-- 查询first_name为Peternela且last_name为Anick的员工的编号、出生日期、性别和入职日期。
SELECT emp_no,birth_date,gender,hire_date
FROM employees
WHERE first_name='Petermela' AND last_name='Anick';

-- 3
-- 查询出生日期在1961-7-15(包括)到1961-7-20(包括)之间的员工的编号、姓名和出生日期。
SELECT emp_no,CONCAT(last_name,first_name) AS emp_name,birth_date
FROM employees
WHERE birth_date>='1961-7-15' AND birth_date<='1961-7-20';

-- 4
-- 查询所有first_name中含有前缀Peter或last_name中含有前缀Peter的员工数据(返回所有列)。
SELECT *
FROM employees
WHERE first_name LIKE 'Peter%' OR last_name LIKE 'Peter%';

-- 5
-- 查询工资数额的最大值,并将查询结果的列名命名为max_salary。
SELECT MAX(salary) AS max_salary
FROM salaries;

-- 6
-- 查询部门编号及相应部门的员工人数,并按照部门编号由小到大的顺序排序(将员工人数列命名为dept_emp_count)。
SELECT dept_no_IN_dept_emp,COUNT(emp_no_IN_dept_emp) AS dept_emp_count
FROM dept_emp
GROUP BY dept_no_IN_dept_emp
ORDER BY dept_no_IN_dept_emp ASC;

-- 7
-- 查询员工“Peternela Anick”的员工编号、所在部门编号和在该部门的工作起始时间。
SELECT emp_no,dept_no_IN_dept_emp,from_date
FROM employees,dept_emp
WHERE CONCAT(first_name,' ',last_name)='Peternela Anick';

-- 8
-- 查询姓名相同的员工x和员工y的编号和姓名(只列出前10行结果)。
SELECT CONCAT(emp1.first_name,' ',emp1.last_name),emp1.emp_no,emp2.emp_no
FROM employees emp1,employees emp2
WHERE emp1.emp_no<>emp2.emp_no AND emp1.last_name=emp2.last_name AND emp1.first_name=emp2.first_name;

-- 9
-- 查询姓名为“Margo Anily”的员工编号和出生日期为“1959-10-30”且入职日期为“1989-09-12”的员工编号的并集。
SELECT emp_no
FROM employees
WHERE (first_name='Margo' AND last_name='Anily') OR (hire_date='1989-09-12' AND birth_date='1959-10-30');

-- 10
-- 查询员工“Margo Anily”所在的部门的名称(要求用子查询实现)。
SELECT dept_name
FROM departments
WHERE dept_no IN (SELECT dept_no_IN_dept_emp
FROM departments,employees,dept_emp WHERE emp_no=emp_no_IN_dept_emp AND first_name='Margo' AND last_name='Anily');

-- 11
-- 要求用JOIN…ON连接语法实现查询10。
SELECT dept_name
FROM dept_emp JOIN departments
ON dept_emp.dept_no_IN_dept_emp=departments.dept_no
WHERE emp_no_IN_dept_emp IN (SELECT emp_no
FROM employees
WHERE first_name='Margo' AND last_name='Anily');

-- 12
-- 查询在全部部门中工作过的员工的编号和姓名(提示:用NOT EXISTS连接的子查询)。
SELECT COUNT(*) AS dept_count FROM departments;--number of depts
SELECT COUNT(*) AS emp_dept_count FROM dept_emp GROUP BY emp_no_IN_dept_emp;--number of depts each emp works in

SELECT list2.emp_no_IN_dept_emp
FROM
(SELECT COUNT(*) AS dept_count FROM departments) list1,
(SELECT COUNT(*) AS emp_dept_count,emp_no_IN_dept_emp FROM dept_emp GROUP BY emp_no_IN_dept_emp) list2
WHERE list1.dept_count=list2.emp_dept_count;

-- 13
-- 查询员工人数大于等于50000的部门编号、部门名称和部门员工人数,按照部门编号由小到大的顺序排序(将部门员工人数列命名为dept_emp_count)。
SELECT dept_no,dept_name,dept_emp_count
FROM departments,(SELECT dept_no_IN_dept_emp,COUNT(emp_no_IN_dept_emp) AS dept_emp_count
FROM dept_emp
GROUP BY dept_no_IN_dept_emp
HAVING COUNT(*)>50000
ORDER BY dept_no_IN_dept_emp ASC) list
WHERE dept_no=list.dept_no_IN_dept_emp;
-- list
SELECT dept_no_IN_dept_emp,COUNT(emp_no_IN_dept_emp) AS dept_emp_count
FROM dept_emp
GROUP BY dept_no_IN_dept_emp
HAVING COUNT(*)>50000
ORDER BY dept_no_IN_dept_emp ASC;

-- 14
-- 在员工表中添加一行记录:
-- (10000, 1981-10-1, Jimmy, Lin, M, 2011-12-8)
INSERT INTO employees VALUES(10000,'1981-10-1','Jimmy','Lin','M','2011-12-8');

-- 15
-- 将14添加的员工记录的first_name属性值修改为Jim。
UPDATE employees
SET first_name='Jim'
WHERE emp_no=10000;

-- 16
-- 删除14添加的员工记录。
DELETE FROM employees
WHERE emp_no=10000;

-- 17
-- 在员工表中添加一行记录:
-- (10001, 1981-10-1, Jimmy, Lin, M, 2011-12-8),观察执行输出结果。
INSERT INTO employees VALUES(10001,'1981-10-1','Jimmy','Lin','M','2011-12-8');

-- 18
-- 删除编号为10001的员工,观察执行输出结果。
DELETE FROM employees
WHERE emp_no=10001;

实验总结

通过实验一的建表语句,查询语句等,体会到了主键与外键的作用。首先说主键,在一个表中主键一定是唯一的,而且主键一定可以决定唯一一条记录,就像由一个人的身份证号可以得到他的姓名,出生年月等信息。而外键指的其实就是另一个表中的主键,且外键可以不唯一。可以根据使用的的意图设定主键与外键。此外,通过定义主键和外键可以维护数据库的完整性。在实验过程中,由于是第一次引入大量数据,处理表的定义以及各表之间的关系时很容易出错,需要删了重新建表(我使用的是DROP TABLE语句),再重新引入数据。这让我想到如果是在一个企业或者一个项目中,数据库是非常重要的一部分,不应该有任何的纰漏和瑕疵,即使是在有备份的情况下,也要谨慎的建表,主键以及外键。在实验第五部分中,我认为不同的查询语句可能得到相同的结果,但性能不完全相同,一个好的技术人员应该懂得如何既简单又快速的查询到需要的信息。以上是我实验一的实验总结。