---
tags: 資料庫系統 大三筆記
---
# 資料庫系統 Database Management System
:::info
+ 課程名稱：資料庫系統 Database Management System
+ 授課老師：[name=張雅惠 Ya-Hui Chang ]
+ 開課學期：1121
:::
[TOC]
## CH2
### 名詞
* domain
欄位可能的值，如性別欄位可能的值為{"男", "女"}
* atomic
加設這domain為atomic，那這欄位的值**不能再被分割**
* multivalued(不是atomic)
value為一個集合 ex:{{1, 2}, {2, 3}}
* composite(不是atmoic)
value為一個tuple ex:{("02", "24622192")}
* relation schema
**R = (A1, A2, …, An )**
ex : instructor = (ID, name, dept_name, salary)
* relation
**D~1~ x D~2~ ....D~n~**
ex : D~1~ = {a, b, c}, D~2~ = {1, 2}, D~1~XD~2~ = {(a, 1), (a, 2), (b, 1), (b, 2), (c, 1), (c, 2)}
r = {(a, 1), (a, 2), (b, 2)}
* relation instance
**The current values of a relation**
**簡單來說就是個表格**
* attribute
直行
* tuple
橫列值共同形成的一個tuple
ex : (a, 1)
| A~1~ | A~2~ |
| - | - |
| a | 1 |
| b | 1 |
| b | 2 |
* superkey(SK)
可以正確識別出**唯一**行列的tuple
| ID | Value |
| - | - |
| 1 | a |
| 2 | a |
| 3 | b |
ID為superkey
* candidate key(CK)
**所有**可當作superkey的tuple
* primary key(PK)
任一candidate key都可當作primary key
* Foreign key(FK)
在一個relation的值必須出現在另外一個relation且作為**另外一個relation的primary key**
* Referencing relation
relation的**value**做為另一個relation的**PK**
* Referenced relation
relation的**PK**是另外一個
relation的**value**
**Schema Diagram 的箭頭方向是從vlaue指向另一個relation的PK**
### Select Operation
instructor (ID, name, dept_name, salary)
teaches (ID, course_id, sec_id, semester, year)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
student (ID, name, dept_name, tot_cred)
* $\sigma$
select：從relation選出某一直行的特定值
*多個限制用and或or做連接*
ex : $\sigma_{dept\_name="Physics" \land salary>90000}$
* $\Pi$
選出特定幾個attribute
ex : $\Pi_{name}(\sigma_{dept\_name = "Physics"}(instructor))$
* $\cup(取聯集)$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))\cup$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
* $-$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))-$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
* $\times$
將兩個relation的tuple互相相關聯
**可能會關聯出不想要看到的結果**，通常會與$\sigma一起使用$
ex:$\sigma_{instructor.id=teaches.id}(instructor \times teaches)$
(將instuctor與teaches關聯，並選出instructor.id與teaches.id相同的tuple)
* $\bowtie$
Natural join:要求兩個表格**共同屬性其值相等的資料列**才能配對輸出
$instructor\bowtie_{instructor.id = teaches.id}teaches =$ $\sigma_{instructor.id=teaches.id}(instructor \times teaches)$
* $\cap(取交集)$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))\cap$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
## CH3
instructor (ID, name, dept_name, salary)
teaches (ID, course_id, sec_id, semester, year)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
student (ID, name, dept_name, tot_cred)
### Domain Types
* char(n)
固定長度字串
* varchar(n)
可變長度字串
* int
* smallint
AKA short(C++)
* numeric(p,d)
浮點數，總共p位數，小數d位
ex:numeric(3,1)->xx.x
* real, double precision
AKA double(C++)
* float(n)
浮點數，小數點精確後n位
### Basic Edit
#### Create Table
```sql=
create table r
(A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
```
integrity-constraint：PK與FK關聯性約束條件
```sql=
create table instructor (
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department);
```
#### Insert
```sql=
insert into instructor
values (‘10211’, ‘Smith’, ‘Biology’, 66000);
--字串加單引號
insert into instructor
values (‘10211’, null, ’Biology’, 66000);
--出現錯誤訊息(name not null)
```
#### Delete
Remove **all** tuple
```sql=
delete from student
```
#### Drop Table
**完整**刪掉整個relation
```sql=
drop table r
```
#### Alter
```sql=
alter table r add A D
```
在r新增Domain type為D名稱為A的attribute
若已存在表格中的tuple其新增的attribute會填入null
```sql=
alter table r drop A
```
在r刪除名為A的attribute
### Query
#### Basic
```sql=
select A1, A2, ..., An --選擇的attribute
from r1, r2, ..., rm --查詢的relation
where P --條件
```
ex :
```sql=
select name
from instructor
--從instructor查詢name的attribute
```
```sql=
select distinct dept_name
from instructor
--從instructor查詢dept_name的attribute且消除重複項
```
```sql=
select all dept_name
from instructor
--從instructor查詢dept_name的attribute(保留重複)
```
```sql=
select *
from instructor
--從instructor查詢所有的attribute
```
```sql=
select ID, name, salary/12
from instructor
--從instructor查詢ID、name、salary三個attribute
--且salary的值/12
```
#### Where
ex:
$\Pi_{name}(\sigma_{dept\_name='Comp.Sci.' \land slalry > 70000}(instuctor))$
```sql=
select name
from instructor
where dept_name = 'Comp. Sci.'
and salary > 70000
```
$\Pi_{name}(\sigma_{dept\_name='Comp.Sci.' \land 90000<=slalry \land salary<=100000}(instuctor))$
```sql=
select name
from instructor
where salary between 90000 and 100000
```
$\Pi_{name, course\_id}(\sigma_{dept\_name='Biology' \land instructor.ID=teaches.ID}(instructor \times teaches)$
```sql=
select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');
```
#### Cross
$\Pi_*(instructor \times teaches)$
```sql=
select *
from instructor, teaches
```
搭配where
$instructor\bowtie_{instructor.id = teaches.id \land instrcuctor.dept\_name='Art'}teaches =$ $\sigma_{instructor.id=teaches.id\land instrcuctor.dept\_name='Art'}(instructor \times teaches)$
```sql=
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID
and instructor.dept_name = 'Art'
```
#### Alias
```sql=
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci.'
```
#### String
* %
%前或後的字串為任意
* 'abc%' : abc開頭
* '%abc' : abc結尾
* '%abc%' : 含有abc子字串
* _
_ 可做為任意字元
ex : a_c : 匹配任何第一個為a第三個為c的字串
* 跳脫字元 \\% \\_
#### Order
對查詢結果作排序
* desc 降冪
* asc 升冪
```sql=
select *
from instructor
order by salary desc, name asc
--從instructor查詢所有attribute
--且依照salary降冪 name升冪排序
```
#### Set
* union
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))\cup$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
```sql=
(select course_id
from section
where semester = 'Fall' and year = 2017)
union
(select course_id
from section
where semester = 'Spring' and year = 2018)
```
* except
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))-$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
```sql=
(select course_id
from section
where semester = 'Fall' and year = 2017)
except
(select course_id
from section
where semester = 'Spring' and year = 2018)
```
* intersect
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))\cap$
$\Pi_{course\_id}(\sigma_{semester="Fall"\land year = "2017"}(section))$
```sql=
(select course_id
from section
where semester = 'Fall' and year = 2017)
intersect
(select course_id
from section
where semester = 'Spring' and year = 2018)
```
#### Aggregate Functions
* avg: average value 平均
* min: minimum value 極小
* max: maximum value 極大
* sum: sum of values 總和
* count: number of values 個數
##### Basic
ex :
```sql=
select avg (salary) as avg_salary
from instructor
where dept_name= 'Comp. Sci.'
--從instructor裡dept_name='Comp. Sci.'的salary取平均
```
```sql=
select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2018;
/*
從teaches裡smester='Spring' and year=2018
取得不重複ID的數量
*/
```
```sql=
select count (*) from course;
-- 取得course所有tuple數量
```
##### Group
```sql=
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;
/*
從instuctor以depy_name為一組對salary做平均
後取得dept_name和重新命名salary的平均為avg_salary
*/
```
```sql=
select dept_name, ID, avg (salary)
from instructor
group by dept_name;
--mulii-value occur(多個ID)
```
##### having
```sql=
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;
/*
從instuctor以depy_name為一組對salary做平均
後取得dept_name和重新命名salary的平均為avg_salary
且salary的平均大於42000
*/
```
**aggregate function 不能直接使用在where clause裡.**
放在having和select clause裡的aggregate function 可不同
#### Set Membership
```sql=
select distinct course_id
from section
where semester = 'Fall'
and year = 2017 and course_id
not in (select course_id
from section
where semester = 'Spring'
and year= 2018);
/*
從section中取得course_id
且section='Fall' and year=2017 and course_id不在
(從section中取得course_id
且section='Soring' and year=2018)的結果裡
*/
```
#### Set Comparison
```sql=
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary
and S.dept_name = 'Biology';
/*
找出salary大於dept_name='Biology'的name
*/
```
* Some
有任一達成
```sql=
select name
from instructor
where salary > some (select salary
from instructor
where dept_name = 'Biology');
/*
找出salary大於任一dept_name='Biology'的name
*/
```
* all
達成所有
```sql=
select name
from instructor
where salary > all (select salary
from instructor
where dept_name = 'Biology');
/*
找出salary大於所有dept_name='Biology'的name
*/
```
* exist
```sql=
select course_id
from section as S
where semester = 'Fall' and year = 2017
and exists(select *
from section as T
where semester = 'Spring' and year= 2018
and S.course_id = T.course_id);
/*
找出semester='Fall' and year=2017
而且要有semester='Fpring' and S.course_id=T.course_id的course_id)
*/
```
* unique
```sql=
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id = R.course_id
and R.year = 2017);
/*
找出course_id其course_id在year=2017只出現一次
*/
```
#### Subqueries
從找出來的relation找東西
```sql=
select dept_name, avg_salary
from ( select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name)
where avg_salary > 42000;
/*
從instructor依照dept_name分組
並得到dept_name和avg(salary) 並將avg(salary)改成avg_salary
再從中找出所有avg_salary>42000的tuple
*/
```
```sql=
select dept_name, avg_salary
from (select dept_name, avg (salary)
from instructor
group by dept_name)
as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;
/*
從instructor依照dept_name分組
並得到dept_name和avg(salary)
建立成名為dept_avg的relation
再從中找出所有avg_salary>42000的tuple
*/
```
#### With
```sql=
with dept_total(dept_name, value) as
(select dept_name, sum(salary)
from instructor group by dept_name),
dept_total_avg(value) as
(select avg(value)
from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;
/*
創建一個dept_total(dept_name, value)
從instructor對dept_name分組取總
創建一個dept_total_avg(alue)
從dept_total對dept_name取平均
再從dept_total, dept_total_avg找到
dept_total.value > dept_total_avg.value;
->找到各dept_name平均大於所有dept_name全部平均
*/
```
##### Scalar subquery
```sql=
select dept_name,
( select count(*)
from instructor
where department.dept_name = instructor.dept_name)
as num_instructors
from department;
--相同結果
select department.dept_name,count(*)
from instructor, department
where department.dept_name = instructor.dept_name
group by(department.dept_name)
```
### Advanced Edit
#### Insert
```sql=
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and total_cred > 144;
--insert 2-4行找到的tuple
```
#### Delete
```sql=
delete from instructor
where dept_name='Finance'
--刪除dept_name='Finance'的tuple
```
```sql=
delete from instructor
where dept_name in (select dept_name
from department
where building = 'Watson');
--刪除instructor的tuple其dept_name有在department
--裡building='Watson'的dept_name中
```
```sql=
delete from instructor
where salary < (select avg (salary)
from instructor);
--刪除過程avg(salary)會改變可能會爆炸
```
#### update
```sql=
update instructor
set salary = salary * 1.05
where salary < (select avg (salary)
from instructor);
-- 小於平均的都x1.05
```
* Case Statement
```sql=
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end
-- 如果salary <= 100000 x1.05 其餘x1.03
```
### Other
#### null
```sql=
select name
from instructor
where salary is null
-- 查詢salary為null的
-- where salary is not null
```
and :
```=
(true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
```
or :
```=
(unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknown
```
Sign in with Wallet
Connect another wallet