資料庫實作

資料庫(實作)

SQL 基本類型

char(n)：用來存放固定長度為n的字串資料
varchar(n)：用來存放最大長度為n的字串資料，實際儲存長度會依資料量而調整
int：用來存放整數資料，最大可存放長度為4位元
smallint：用來存放整數資料，最大可存放長度為2位元
numeric(p,d)：用來存放帶有小數的數值，使用時須指明精確度與小數點位數。此表示資料精準度為p 位數，而p 位數中含有d 位數的小數
real, double precision：用來存放近似浮點數值，其精準度會因機器而產生誤差
float(n)：用來存放浮點數值，精準度至少為n位數

建立資料表

1	create table <TABLE NAME> (A1 D1, A2 D2, ..., An Dn,)

A為資料表內之屬性名稱
D為屬性之型態

範例

create table instructor (
                            ID             char(5),
                            name           varchar(20) not null,
                            dept_name      varchar(20),
                            salary         numeric(8,2))

這個例子建立了instructor資料表，裡面有四個屬性

create table takes (
        ID                   varchar(5),
        course_id            varchar(8),
        sec_id               varchar(8),
        semester             varchar(6),
        year                 numeric(4,0),
        grade                varchar(2),
        primary key (ID, course_id, sec_id, semester, year),
        foreign key (ID) references  student,
        foreign key (course_id, sec_id, semester, year) references section );

上面是加了primary key與foreign key的版本

插入資料

1	insert into <TABLE NAME> values (...)

我們可以使用這個關鍵字去插入資料，例如:

1	insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000);

這筆資料的ID為10211，name為Smith以此類推。

若使用

1	insert into instructor values (‘10211’, null, ’Biology’, 66000);

會失敗，因為name被定義為不能為空。

查詢資料

1	select <NAME> from <TABLE NAME>

刪除及修改資料

1	drop table instructor

刪除資料表及其內容

1	delete table instructor

刪除所有資料表的內容，但保留此資料表

上述兩種都會使資料表內容完全清空

若要修改資料表內的屬性值使用alter

1	alter table <TABLE NAME> add A D

A 是新增的屬性名稱，而D則是新增屬性之資料類別
在一開始的新屬性中，所有關聯中的原組都會被指派為null

1	alter table <TABLE NAME> drop A

A為資料表中屬性名稱

1	ALTER TABLE Customer ADD PRIMARY KEY (SID);

為Customer 增加 primary key 若原本的attribute可以為空則必須下

1	ALTER TABLE Customer ALTER COLUMN SID int not NULL;

必須重複一次原本的型態

SQL查詢結構

1	select A From r where P

A為屬性
r為資料表
P為條件

1	select distinct Country from Customers

distinct關鍵字會使查詢結果不含重複值
所以上面的語句就是從Customers中抓出不重複的Country

where則是可以用來設定查詢的條件

1	select ProductName from Products where CategoryID = '1' and Unitprice > 20

上面的語句為查詢Products中CategoryID為1且Uniprice > 20 的ProductName

SQL中的相等為=而不是==

between : 可用來指定值必須介於某一閥值之間

1
2
3

select name
from instructor
where salary between 90000 and 100000

上面的語句為查詢instructor中salary介於90000和100000之間的教師名子

Join

Natural Join

運算涵蓋兩個資料表，然後產生一個共同的關聯為結果

只考慮出現在雙方資料表且有相同屬性值的欄位
兩資料表之間同名的欄位會被自動結合在一起

1
2
3

select name, course_id
from instructor, teachers
where instructor.ID = teachers.ID;

等同於

1 2	select name, course_id from instructors natural join teachers;

其他Join

as(別名運算)

 SELECT t1.student_number, [section].instructor ,t1.grade FROM
    (SELECT student.student_number ,section_identifier ,grade 
    FROM Grade_report, student
    WHERE Grade_report.student_number = student.student_number and student.name = 'Jeff') as t1
INNER  JOIN [section] on [section].section_identifier = t1.section_identifier;

t1表示為Grade_report和student兩個表中student_number一樣且名字為Jeff的關聯
並且加入了section

like(字串運算)

%:任何含有零個或多個字元的字串
_:表示任何單一字元

1
2
3

select name
from instructor
where name like '%dar%';

上面的語句為查詢instructor中含有dar的教師名稱

intro%:任何開頭為intro的字串
%Comp:含有Comp的字串
___:表示正好三個字源的字串
___%:表示至少四個字元的字串

order by(查詢結果排序)

1
2
3

select distinct name
from instructor
order by name

上面的語句為依照name的字母順序列出所有不重複的instructor

order by預設為升冪排序
若要降冪排序必須要在後面加desc

例

1
2
3

select distinct name
from instructor
order by name desc

集合運算

union(交集)
intersect(聯集)
except(差集)

上面三種運算都可以自動消除重複值
若要保留則要改寫成 union all, intersect all, except all

查詢在2009 年秋季或2010 年春季有開課的課程代碼

1
2
3

(select course_id from section where sem = ‘Fall’ and year = 2009)
 union
(select course_id from section where sem = ‘Spring’ and year = 2010)

查詢在2009 年秋季和2010 年春季皆有開課的課程代碼

1
2
3

(select course_id from section where sem = ‘Fall’ and year = 2009)
 intersect
(select course_id from section where sem = ‘Spring’ and year = 2010)

查詢在2009 年秋季有開課但2010 年春季未開課的課程代碼

1
2
3

(select course_id from section where sem = ‘Fall’ and year = 2009)
 except
(select course_id from section where sem = ‘Spring’ and year = 2010)