一、数据库存储管理实验
1、实验目标
- 了解GaussDB 物理存储和逻辑存储
- 理解表空间基本概念
- 掌握创建，删除表空间的方法
- 掌握在表空间上创建对象的方法
2、基础知识
- 逻辑存储结构
-
逻辑存储主要包含表空间、数据
段、数据区、数据页几个概念,
它们的构成管理如图所示:
- 若干连续page给成一个extent ;
- 若干extent构成一个segment,这些extent不一定连续;
- 一个表空间包含若干segment ;
- 每张表至少对应一个segment。
超全的SQL基本操作实验
-
- 物理存储结构
- 数据库在物理上各种不同用途的文件组成,既有二进制文件,也有文本文件.
超全的SQL基本操作实验 - 物理存储与逻辑存储
- 数据库划分为多个逻辑上的存储单元，叫做表空间.一个表空间包含一个或多个数据文件，其目的是：
- 优化数据库空间分配策略
- 设置不同用户空间份额
- 控制部分数据的可用性
- 将数据分布到不同表空间、不同存储介质，提高性能
- 细粒度备份恢复
- 数据库默认表空间包括：
- 系统表空间
- UNDO表空间
- 临时表空间
- 用户表空间
3、实验任务
（1）创建表空间 human_resource_space，表空间参数可以自定
SQL> CREATE TABLESPACE human_resource_space DATAFILE 'test.dat' SIZE 1048576;
Succeed.
（2）创建用户，并指定该用户表空间为human_resource_space，用户名可以自定
SQL> CREATE USER Xiang001 IDENTIFIED BY Gauss123 DEFAULT TABLESPACE human_resource_space;
Succeed.
（3）为用户授权，该用户可以通过DataStudio登录数据库
SQL> GRANT CREATE session to Xiang001;
Succeed.
二、数据库对象管理实验
1、实验目标
- 了解基本表的概念
- 掌握创建、删除基本表的SQL语句
- 掌握利用DataStudio创建、删除基本表的方法
2、基础知识
1、创建表SQL语句
CREATE TABLE table_name
( col1 datatype constraint,
col2 datatype constraint,
…
coln datatype constraint,
constraint
)
TABLESPALE table_space_name;
2、删除表SQL语句
DROP TABLE table_name [PURGE]；
例1： DROP TABLE section；
例2： DROP TABLE section PURGE；
3、实验任务
在表空间 human_resource_space，创建人力资源库的8个基本表，要求表1至表5用SQL语句创建， 表6至表8利用DataStudio完成。
- 雇佣历史表 employment_history
| 字段 | 类型 | 备注 |
|---|---|---|
| Staff_id | NUMBER | 员工编号 |
| Start_date | DATE | 入职日期 |
| End_date | DATE | 离职日期 |
| Employment_id | VARCHARE2(10) | 职位编号 |
| Section_id | NUMBER(4) | 部门编号 |
create table employment_history
(
staff_id NUmber(6),
start_data DATE,
end_data DATE,
employment_id VARCHAR2(10),
section_id NUMBER(4)
)
2.部门表 sections
| 字段 | 类型 | 备注 |
|---|---|---|
| section_id | NUMBER | 部门编号 |
| section_name | VARCHAR2(30) | 部门名字 |
| manager_id | NUMBER | 部门经理编号 |
| place_id | NUMBER | 部门位置编号 |
Create table sections
(
section_id NUMBER,
section_name VARCHAR2(30),
manager_id NUMBER,
place_id NUMBER
)
3.工作地点表 places
| 字段 | 类型 | 备注 |
|---|---|---|
| place_id | NUMBER | 部门位置编号 |
| street_address | VARCHAR2(40) | 街道地址 |
| postal_code | VARCHAR2(12) | 邮编 |
| city | VARCHAR2(30) | 城市 |
| state_province | VARCHAR2(25), | 省份名字 |
| state_id | CHAR | 省份编号 |
Create table places
(
place_id NUMBER,
street_address VARCHAR2(40),
postal_code VARCHAR2(12),
city VARCHAR2(30),
state_province VARCHAR2(25),
state_id CHAR(2)
)
\4. 区域表 areas
| 字段 | 类型 | 备注 |
|---|---|---|
| area_id | NUMBER | 区域编号 |
| area_name | VARCHAR2(25) | 区域名称 |
Create table areas
(
area_id NUMBER,
area_name VARCHAR2(25)
)
5.大学表 colleges
| 字段 | 类型 | 备注 |
|---|---|---|
| college_id | NUMBER | 大学编号 |
| college_name | VARCHAR2(40) | 大学名称 |
Create table colleges
(
college_id NUMBER,
college_name VARCHAR2(40)
)
6.雇佣表 employments
| 字段 | 类型 | 备注 |
|---|---|---|
| employment_id | VARCHAR2(10) | 雇员编号 |
| employment_title | VARCHAR2(35) | 职称编号 |
| min_salary | NUMBER | 最少薪水 |
| max_salary | NUMBER | 最多薪水 |
7.国家及地区表 states
| 字段 | 类型 | 备注 |
|---|---|---|
| state_id | CHAR(2) | 国家编号 |
| state_name | VARCHAR2(40) | 国家名字 |
| area_id | NUMBER | 区域编号 |
同上
8.员工表 staffs
| 字段 | 类型 | 备注 |
|---|---|---|
| staff_id | NUMBER | 员工编号 |
| first_name | VARCHAR2(40) | 名 |
| last_name | VARCHAR2(12) | 姓 |
| VARCHAR2(30) | 邮箱 | |
| phone_number | VARCHAR2(25), | 手机号 |
| hire_date | CHAR | 雇佣日期 |
| employment_id | VARCHAR2(10) | 雇佣编号 |
| salary | NUMBER | 薪水 |
| commission_pct | NUMBER | 员工提成 |
| manager_id | NUMBER | 经理编号 |
| section_id | NUMBER | 部门编号 |
| graduated_name | VARCHAR2(60) | 分级名称 |
同上
三、 数据操作实验
1、实验目标
- 掌握数据初始化的方法；
- 掌握数据查询语句SELECT，包括基本查询，统计查询，连接查询，子查询，查询集合等；
- 掌握数据更新语句，包括INSERT, DELETE, UPDATE.
2、实验任务
- 根据给定信息初始化数据表
- 查看雇佣表的所有信息
- 查询编号为60的部门名称
SELECT section_name from sections where section_id=60; - 查询工资最高的五名员工，返回员工编号及员工姓名
SELECT staff_id,first_name,last_name from staffs a
where 5 > (SELECT count(*) from staffs where a.salary <salary)
ORDER BY salary;
- 查询编号为201员工的部门经理编号及其姓名
- 查询工资差距最大的职位，返回职位编号，和职位名称
SELECT employment_id, employment_title from EMPLOYMENTS a where 1>(SELECT count(*) from EMPLOYMENTS where (a.max_salary-a.min_salary)<(max_salary-min_salary)); - 查询各部门工资最高的员工姓名，及其对应的部门名称*/
SELECT first_name, last_name, section_name FROM staffs, sections WHERE staffs.SECTION_ID = sections.section_id AND ( staffs.SECTION_ID, Salary ) IN (SELECT SECTION_ID, max( Salary ) FROM staffs GROUP BY SECTION_ID ) - 查询雇佣历史表中担任过AC_ACCOUNT和AC_MGR 职位的员工编号
- 查询雇佣历史表中员工的雇佣时长，返回并显示如下信息：员工编号，职位编号，部门编号，雇佣时长
- 查询在城市（city）South San Francisco工作的员工编号和员工姓名，按工资降序排列
SELECT staff_id,first_name,last_name from staffs where section_id=(select section_id from sections where place_id=(select place_id from places where city='South San Francisco')); - 查询员工平均工资在5000以上的部门，返回部门编号及部门名称
SELECT DISTINCT
sections.section_id,
section_name
FROM
staffs,
sections
WHERE
staffs.SECTION_ID = sections.section_id
AND ( staffs.SECTION_ID )
IN (SELECT SECTION_ID
FROM STAFFS
GROUP BY SECTION_ID
HAVING avg(salary)>5000 );
- 查询last_name 以 字母F开头的员工，返回员工编号和姓名
SELECT staff_id,first_name,last_name from
staffs where last_name like 'F%';
- 查询雇佣历史表中在两个及以上不同职位工作过的员工，返回员工编号
SELECT DISTINCT staff_id FROM EMPLOYMENT_HISTORY where staff_id in
(select staff_id from EMPLOYMENT_HISTORY group by staff_id having count(staff_id)>1 );
- 查询各个国家办事处的数量
- 在员工表中新增一位员工信息（内容自拟，但符合其他表约束）
INSERT INTO STAFFS
(staff_id, first_name, last_name, email,phone_number, hire_date,employment_id, salary, commission_pct, manager_id, section_id)
values
(197, 'James', 'Maxwell', '[email protected]', '651.507.9833', to_date('20-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2800.00, null, 124, 50);
- 为平均工资在5000及以下的部门 每位员工加薪1000元
UPDATE STAFFS set STAFFS.SALARY=STAFFS.SALARY+1000
where section_id in (SELECT SECTION_ID
FROM STAFFS
GROUP BY SECTION_ID
HAVING avg(salary)<5000 );