一、資料庫存儲管理實驗
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 );