Fundamental and Application of
Database
? o?¨$
? o?¨$
? ??
? ??
? e/
?D1???ù??
? e/
?D1???ù??
? ￥t
? ￥t
2.2.1 .d￥"?
?
z i
z μ
z ?
z <l2 5:
4. <l2 5:
Extended Cartesian Product
z R
– n "1" k
1
?íF
z S
– m "1" k
2
?íF
z R ?S
–
n+m
￥íF￥"?
z íF￥ - n
^1" R￥B?íF
z a m
^1" S￥B?íF
– ? k
1
?k
2
?íF
z R ?S = {t
r
t
s
|t
r
∈R ?t
s
∈S }
2.2.2
ê￥1"
?
z ê4
z g?
z ?¤
z "
1. ê4
Selection
z 1) ê4??1K?
Restriction
z 2) ê4
??￥cl
– 1" R?ê4
?@ó?Hq￥ííF
6
F
(R) = {t|t∈R ?F(t)= '? '}
– F ê4Hq
^B?
? Vr
T'?
T1
X
1
, Y
1
[9 X
2
, Y
2
]…
z , 1?
??
# ?! ?" <>
z X
1
Y
1
?
?? aè
ae?f
?
?? 9 V[¨
?
￥?| ?}9
z 9
?
??
? ?
z [ ] V
U ?ê[
z … V
U
?ì
T V[×ˉ/ ?
ê4
?
z 3) ê4
?
^V?￥?é?￥
?
z 4) è
!μB?D
3 - [?
? o ?D
31"
Student a [?1" Course?ê?1" SC b
6
2. g?
Projection
z 1g?
??￥cl
– V R?ê4 ??
??
F??￥1"
4
A
(R) = { t[A] | t ∈R }
A R?31
=￥
??
2. g?
Projection
z 2g??T?1
^V
￥?é?
?
– ?g?-a?? |h
e1"?￥ t
7 O? V ? |h tíF
E
?×ˉ?
4
3. ?¤
Join
z 1 ?¤9?1, ?¤
z 2 ?¤
?￥cl
– V
?1"￥2 5:?ê |
??W
?@B?Hq
￥íF
R S = { | t
r
∈ R ?t
s
∈S ?t
r
[A], t
s
[B] }
z A?B sY1 R?S
V1￥
??F
z , 1?
??
– ?¤
?V R?S￥<l2 5: R ?S?ê |
R1
" A
??F
￥′D
S1" B
??F
′
?@1?1"￥íF b
A, B
t
r
t
s
?¤ (? )
z 3
?è¨ ?¤
?
– ?′ ?¤
equijoin
z
I
1
^?′ ?¤
–,1 “"”￥ ?¤
??1?′ ?¤
z ?′ ?¤￥cl
–V1" RD S￥<l2 5:?ê | A aB
??
′M?￥ *tíF'?′ ?¤1
R S = { | t
r
∈R ?t
s
∈S ?t
r
[A] = t
s
[B] }
A=B
t
r
t
s
?¤ (? )
– 1 ? ?¤
Natural join
z
I
1
^1 ? ?¤
– 1 ? ?¤
^B?+
y￥?′ ?¤
?
?1"?é?1?￥s
A?
^M]
￥
??F
? 2T?ü×ˉ￥
??
??
z 1 ? ?¤￥cl
R?S μM]￥
??F B
RS= { | t
r
∈R ?t
s
∈S ?t
r
[B] = t
s
[B] }
t
r
t
s
?¤ (? )
?i B?￥ ?¤?T
^V?￥?é?
? b
?i B?￥ ?¤?T
^V?￥?é?
? b
1 ? ?¤?31 |h×ˉ
?[
^]
H
1 ? ?¤?31 |h×ˉ
?[
^]
H
V??
￥?é?
? b
V??
￥?é?
? b
A, B
R
S
?¤ (? )
z 5 è
A B C
a
1
b
1
5
a
1
b
2
6
a
2
b
3
8
a
2
b
4
12
B E
b
1
3
b
2
7
b
3
10
b
3
2
b
5
2
R
S
?¤ (? )
R S
A R.B C S.B E
a
1
b
1
5 b
2
7
a
1
b
1
5 b
3
10
a
1
b
2
6 b
2
7
a
1
b
2
6 b
3
10
a
2
b
3
8 b
3
10
C! E
?¤ (? )
?′ ?¤ RS
R.B=S.B
A R.B C S.B E
a
1
b
1
5 b
1
3
a
1
b
2
6 b
2
7
a
2
b
3
8 b
3
10
a
2
b
3
8 b
3
2
?¤ (? )
1 ? ?¤ RS
A B C E
a
1
b
1
5 3
a
1
b
2
6 7
a
2
b
3
8 10
a
2
b
3
8 2
4. "
Division
ó?1" 3 9 :
? 4 : ;
? 9 : ;1
??
F b
3?￥ :D 4?￥ : V[μ?]￥
?? ? A?1M]
￥×" b 3D 4￥"
?¤?B??￥1" 1 9
R ?S = P(x)
|t∈P(X), μ t ∈4
X
(R), 7 O t ?4
Y
(S) ? R
" (? )
z 2"?T
^]
HV??
?é?
?
z 3 è
[ è 6]
?
R
S
" (? )
A B C
a
1
b
1
c
2
a
2
b
3
c
7
a
3
b
4
c
6
a
1
b
2
c
3
a
4
b
6
c
6
a
2
b
2
c
3
a
1
b
2
c
1
B C D
b
1
c
2
d
1
b
2
c
1
d
1
b
2
c
3
d
2
R ?S
A
a
1
R
S
2.2.3 ¨1"}
?V
U1"?T
[ è 1] °?D [?|1 1137￥D
3￥
D|??
4
Sno,Grade
(6
Cno=‘1137
( SC))
2.2.3 ¨1"}
?V
U1"?T
[ è 2] °?D [?|1 1137￥D
3￥
D|??
4
Sno,Sname
( S (6
Cno=‘1137
( SC)) )
[ è 5] °?à
ê? [?|1 1024?1136
￥D
3￥D|
4
1
(6
1=4^2=‘1024’^5=’1136’
( SC ? SC) )
°?a?
°?a?
aA1?
aa?|
{
aa?|
{
? ê4
?5S b K×1 aK' b
? 3? ?¤ -1"
a?1?) ? b
ì? a ??
? g??ê4]
Hé? b
? |g?] - aa￥
? "
?2? b
? ê4] -
?￥2 582? ??1 ?¤
? b
? s 0Vr
T b
a1"}
?￥?NMD?5
a1"}
?￥?NMD?5
a°?a?￥a?
a°?a?￥a?
DBMS￥°?a?0"d
1¨?￥
1¨?￥
“a?
a?
”÷z
÷z
?
?
c 1"
? o
1"
? o
S?y SQL
4 2 -à
?
à
?
?2?°??y
2?°??y
SQL
Structured Query Language
?o?
o?
1"}
? D
D
1"
? -W
?? ? ?°? a?: a?l? e?
?Y¨￥ a? ? <￥ 1"
? o ?y
? " -X?11"
? o￥S?y
4 2 -à
?à
?
4 2 -￥á
3D?Z
￥á
3D?Z
1972 M
M
SQARE (Specifying Qureies As Relational Expression)
1974 M
M
Boyce and Chamberlin
SEQUEL (Structured English Query Language), SQL
1986 M
M
ANSI ?T1 RDBMS￥S?y
￥S?y
SQL-86
1989 M
M
ANSI?? SQL-89
1992 M
M
SQL-92??
??
SQL2
1999 M
M
SQL-99??
??
SQL3
4 2 -à
?à
?
4 2 -￥'à Q
￥'à Q
V
Table V aV2?
? F? b
B?1"?B?V
jm
View
jm ??lF?′V b
4 2 -à
?à
?
4 2 -￥F?
￥F?
?
? ?l
Data Definition
Table, View, Index￥?l i
? 3??
?
? ?:
Data Manipulation
?
? e?
Data Control
? 3 ?
T SQL
Embedded SQL
4 2 -à
?à
?
4 2 -￥+?
￥+?
1. 8?dB
2. údV??
údV??
3.
?_"?￥?TZ
T
4. B??E24
?
P¨Z
T
5. ?ye±^D^¨
?ye±^D^¨
4 2 -à
?à
?
SQL? ?
? ?
?M
?M
? °?
? °? SELECT
? ?l
? ?l CREATE DROP ALTER
? ?:
? ?: INSERT UPDATE DELETE
? e?
? e? GRANT REVOKE
SQL?y￥?M
?y￥?M
4 2 -à
?à
?
Course (Cno, Cname, Cpno, Ccredit)
Cno Cname Cpno Ccredit
1024
? oe ?
? oe ? 1136 4
1128
ú?
?D
ú?
?D NULL 6
1136
? ?
?D
? ?
?D 1128 4
1137
5 ?D
5 ?D NULL 4
1156
??
?? NULL 6
[?V
D
3 [?
? oD
3 [?
? o
Student (Sno, Sname, Ssex, Sage, Sdept)
Sno Sname Ssex Sage Sdedpt
2000012
| ?
3
20
9
?
2000014
?o
?o
o
o 18
9
?
2000113
fv
? 3
18
5 ?
2000256
$Y o
o 19
5 ?
2000278
{O 3
19
5 ?
D
3V
D
3 [?
? oD
3 [?
? o
SC(Sno, Cno, Grade)
Sno Cno Grade
2000012 1024 80
2000012 1136 78
2000012 1137 70
2000012 1156 80
2000014 1024 88
2000014 1136 90
2000014 1156 88
2000113 1156 89
2000256 1137 77
2000256 1156 93
2000278 1137 89
D
3ê [V
D
3 [?
? oD
3 [?
? o
3.3
? ?l
DROP
INDEX
CREATE
INDEX
ì?
ì?
DROP
VIEW
CREATE
VIEW
jm
jm
ALTER
TABLE
DROP
TABLE
CREATE
TABLE
V
V
?? ÷"
÷"
7y
7y
?T`
? ?l
? ?l
? ?l
? ?l
?l a??? ÷"V
?l a??? ÷"V
a?l
7yV
$ 3 & " 5 & 5 " # - & V
? ?? <
)????
?Hq >
<
? ?? <
)????
?Hq > >…
< V)????
?Hq >
è $ 3 & " 5 & 5 " # - & 4 U V E F O U
4 O P $ ) " 3
/ 0 5 / 6 - - 6 / * 2 6 &
4 O B N F $ ) " 3
4 T F Y $ ) " 3
4 B H F * / 5
4 E F Q U $ ) " 3
? ??
a
ü
? ??
a
ü
4 . " - - * / 5 ?3é=é??
? b
* / 5 & ( & 3 * / 5 ?3é=é??
? b
% & $ * . " - Q <
R >
a
ê
Eé?
? Qê ?l
??aμ Rê b
' - 0 " 5
?3é??
? b
$ ) " 3 5 & 3 O
$ ) " 3 O
é1é1 O￥?é3?1 b
7 " 3 $ ) " 3 O
Kvé1Kvé1 O￥Mé3?1 b
( 3 " 1 ) * $ O
é1é1 O￥?ém?3?1 b
7 " 3 ( 3 " 1 ) * $ O
Kvé1Kvé1 O￥Mém?3?1 b
% " 5 & ° ù?ì
T1 : : : : . . % % b
5 * . &
HW?ì
T1 ) ) . . 4 4 b
5 * . & 4 5 " . 1 ° ùF
HW b
? ?l
? ?l
a??V
" - 5 & 3 5 " # - & V
< " % % ?
? ?? <????
? > >
< % 3 0 1 ????
? >
< . 0 % * ' :
? ??
è _ 4 U V E F O UV9F “ ?D
HW ”
? ??1 ° ù?
" - 5 & 3 5 " # - & 4 U V E F O U " % % 4 D P N F % " 5 &
è | M
-￥
? ???1?3é?
?
" - 5 & 3 5 " # - & 4 U V E F O U . 0 % * ' : 4 B H F 4 . " - - * / 5
è ÷"1?D|A? |·B′￥?
?
" - 5 & 3 5 " # - & 4 U V E F O U % 3 0 1 6 / * 2 6 & 4 O P
? ?l
? ?l
a ÷"V
% 3 0 1 5 " # - & V
è ÷" 4 U V E F O UV
% 3 0 1 5 " # - & 4 U V E F O U
? ?l
? ?l
z ? ì
T
CREATE [UNIQUE] [CLUSTER] INDEX <
ì? >
ON <V >(<
>[<Q? >][,<
>[<Q? >] ]…)
– ¨ <V >·?1y
ì?￥'V 3
–
ì? V[y ? ?V￥B
ò
-W¨ù
|s?
– ¨ <Q? >·?
ì?′￥ ?
Q?
6? ASC??
DESC b ?
8′ ASC
– UNIQUEV
üN
ì?￥
?B?
ì?′o?·B￥
?
:
c
– CLUSTERV
U1y ?￥
ì?
^
[
ì?
y ?
ì?
y ?
ì?
è5
[ è 6] 1D
3 - [?
? o?￥ Student
CourseSC ??Vy ?
ì? b ? StudentV?
D|
6?y·B
ì? CourseV? [?|
6?
y·B
ì? SCV?D|
6?? [? |??y
·B
ì? b
CREATE UNIQUE INDEX Stusno ON Student(Sno)
CREATE UNIQUE INDEX Coucno ON Course(Cno)
CREATE UNIQUE INDEX SCno ON SC(Sno ASC
Cno DESC)
y ?
ì?
?
z·B′
ì?
–
?Xc×ˉ′￥
??
? ?y UNIQUE
ì?
–
?
y ? UNIQUE
ì?a? ??:
c
H
DBMS?1?_°?:
c ?
^? |
×ˉ
′ b?M??9F
B? UNIQUE?
?
y ?
ì?
?
z
[
ì?
–
y ?
[
ì?aV?
? 931?·
?￥
[
??′￥
6???ib b9'
[
ì?￥
ì?[
¨?DV?:
c￥t ?
¨?Bá
è
CREATE CLUSTER INDEX Stusname ON
Student(Sname)
StudentV￥ Sname
?
y ?B?
[
ì?7
O StudentV?￥:
c|?v Sname′￥
6?ib
y ?
ì?
?
– B?'V
K o ?y ?B?
[
ì?
–
[
ì?￥¨o ? t ??￥°? V
[4ú°?r
q
–
[
ì?￥
a¨S?
z ?
Vé?9 ÷?T
z ?
?￥Mé
é????T
÷"
ì?
DROP INDEX <
ì? >
– ÷"
ì?
H"d?V
? 3?? ÷ ?μ1
?
ì?￥
í
? b
[ è 7] ÷" StudentV￥ Stusname
ì? b
DROP INDEX Stusname
1?
ì?
1?
ì?
·
·
ì?￥y ?? ÷"?
ì?￥y ?? ÷"?
DBAyV￥ |μ3¨??
yV￥ |μ3¨??
A9? ? i |
?
^ê4
ì?
A9? ? i |
?
^ê4
ì?
·
·
T1B??5??? B?S
y ?
t ￥
ì?
T1B??5??? B?S
y ?
t ￥
ì?
2-3? b
ì? ?? ?°?rT?9{n
H ? b
? b
ì? ?? ?°?rT?9{n
H ?
W b??
÷??T￥? ? by1"dA??
HW ??
W b??
÷??T￥? ? by1"dA??
HW ??
1?t
ì?
1?t
ì?
·
·
V?
?
ì?￥a ??
üA b
V?
?
ì?￥a ??
üA b
3.5 °
°
?
3.5.1 à
?
3.5.2 ?V°?
3.5.3 ?¤°?
3.5.4 3*°?
3.5.5 "?°?
3.5.6 l2
3.5.1 à
?
z ? ì
T
SELECT [ALL|DISTINCT] < "S
Vr
T >
[< "S
Vr
T >] …
FROM <V
jm >[<V
jm > ] …
[ WHERE <HqVr
T > ]
[ GROUP BY <
1> [ HAVING <HqVr
T > ] ]
[ ORDER BY <
2> [ ASC|DESC ] ]
? ì
T
– SELECT0 ·?1A
U￥
??
– FROM0 ·?°?` ('V
jm )
– WHERE0 ·?°?Hq
– GROUP BY0 °?2T?·?
￥′sF
?
??
′M?￥íF1B?F bYè?
?F?
T¨"f
? b
– HAVING ? ?êoμ
?@·?Hq￥F
– ORDER BY0 °?2TV?·?
′￥
6?
?? ??
U è
? o
D
3 - [?
? o
z D
3V Student(SnoSnameSsexSageSdept)
z [?V Course(CnoCnameCpnoCcredit)
z D
3ê [V SC(SnoCnoGrade)
3.3 °
°
?
3.3.1 à
?
3.3.2 ?V°?
3.3.3 ?¤°?
3.3.4 3*°?
3.3.5 "?°?
3.3.6 l2
3.3.2 ?V°?
°??
#B?V
^B?Ke?￥°??T
B aê4V?￥ ??
= aê4V?￥ ??íF
? a°?2T ??
1 a
P¨"f
?
? a°?2TsF
°?·?
[ è 1] °? ?8D
3￥D|D? b
SELECT SnoSname
FROM Student
[ è 2] °? ?8D
3￥? aD| a
? " b
SELECT SnameSnoSdept
FROM Student
°? ??
°? ??
[ è 3] °? ?8D
3￥W%:
c b
SELECT SnoSnameSsexSageSdept
FROM Student
SELECT *
FROM Student
3. °?üV9
?￥′
SELECT0 ￥ < "S
Vr
T >1Vr
T
–
?
?Vr
T
– 3?1è
– f
?
–
Y
– ?
3. °?üV9
?￥′
[ è 4] ° ?8D
3￥? #
3 Mz b
SELECT Sname2000-Sage
FROM Student
{2T
Sname 2000-Sage
--------- -------------
ù§ 1976
>? 1977
| 1978
f ? 1978
3. °?üV9
?￥′
[ è 5] °? ?8D
3￥? a
3 Mz?
?
μ"1 p¨l3 V
U
?μ" b
SELECT Sname'Year of Birth: '2000-Sage
ISLOWER(Sdept)
FROM Student
è5
?
{2T
Sname 'Year of Birth:' 2000-Sage ISLOWER(Sdept)
------- ---------------- --------- --------------
ù§ Year of Birth: 1976 cs
>? Year of Birth: 1977 is
| Year of Birth: 1978 ma
f ? Year of Birth: 1977 is
[ è 5.1]
P¨
Y ?M°?2T￥
S5
SELECT Sname NAME'Year of Birth: ’ BIRTH
2000-Sage BIRTHDAYISLOWER(Sdept) DEPARTMENT
FROM Student
{2T
NAME BIRTH BIRTHDAY DEPARTMENT
------- ---------------- ------------- ------------------
ù§ Year of Birth: 1976 cs
>? Year of Birth: 1977 is
| Year of Birth: 1978 ma
f ? Year of Birth: 1977 is
= aê4V?￥ ??íF
zh" |′×ˉ￥?
z°?
?@Hq￥íF
1. h" |′×ˉ￥?
– SELECT0 ?
P¨ DISTINCT ?
L
! SCV?μ/
?
Sno Cno Grade
------- ------- -------
95001 1 92
95001 2 85
95001 3 88
95002 2 90
95002 3 80
ALL D
D
DISTINCT
[ è 6] °?ê?
[?￥D
3D| b
(1) SELECT Sno
FROM SC;
( a ALL)
SELECT ALL Sno
FROM SC;
2T Sno
-------
95001
95001
95001
95002
95002
è5
?
(2) SELECT DISTINCT Sno
FROM SC;
2T
Sno
-------
95001
95002
è5
?
z ?i DISTINCT ?￥T¨S?
^
?μ "S
è °?ê? [?￥ò??
p￥E
SELECT DISTINCT CnoDISTINCT Grade
FROM SC;
? ?￥E
SELECT DISTINCT CnoGrade
FROM SC;
2.°?
?@Hq￥íF
V
3.3
è ¨ ￥ ° ?H q
°
?
H
qì
M
1
?
=
>
<
>=
<=
!=
<>
!>
!<
NOT +
?1?
??
? ? S ?
BETWEEN AND
NOT BETWEEN AND
? ? "?
IN
NOT IN
3? ? ￥
LIKE
NOT LIKE
b
′
IS NULL
IS NOT NULL
×Hq
AND
OR
WHERE0 è¨￥°?Hq
(1) 1?vl
WHERE0 ￥ <1?Hq >?
P¨1?
??
– =><>=<=!= <>!>!<
–
?
?? NOT + 1?
??
[ è 8] °?
?μ M
- 20
?[/￥D
3? # M
- b
SELECT SnameSage
FROM Student
WHERE Sage < 20
SELECT SnameSage
FROM Student
WHERE NOT Sage >= 20
(2) ??S?
??S?
z
P¨ìM BETWEEN … AND …
NOT BETWEEN … AND …
[ è 10] °? M
- 20~23
?
? 20
?? 23
?-W￥D
3￥? a"Y? M
- b
SELECT SnameSdeptSage
FROM Student
WHERE Sage BETWEEN 20 AND 23
è5
?
[ è 11] °? M
-? 20~23
?-W￥D
3? a
"Y? M
- b
SELECT SnameSdeptSage
FROM Student
WHERE Sage NOT BETWEEN 20 AND
23
? ?
?
?
?
z a °
?B
z a °
?=
z a °
? ?
