Fundamental and Application of
Database
? o?¨$
? o?¨$
?
1?
?
1?
? e/
?D1???ù??
? e/
?D1???ù??
? ￥t
? ￥t
? ?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??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′￥?
?
÷"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
?
?
÷
÷
?
B
? ?
?
? ?
?
??
?
??
?
÷"
?
÷"
?
3.4.1 ? ?
?
z
?? ?
? Z
T
– ? ???íF
– ? ?0°?2T
? ???íF
? ???íF
z ? ì
T
INSERT
INTO <V > [(<
??
1>[<
??
2 >…)]
VALUES (<è
1> [<è
2>] … )
z ? ?
? ?
|?íF? ?·?V? b
? ???íF
?
[ è 1] |B??D
3:
c
D| 95020 ? ?? ?Y 3
? " IS
M
- 18
?? ?? StudentV? b
INSERT
INTO Student
VALUES ('95020''?? '' 3 ''IS'18)
? ???íF
?
[ è 2] ? ?BHê [:
c ( '95020''1 ') b
INSERT
INTO SC(SnoCno)
VALUES (' 95020 '' 1 ')
?? ?￥:
c Grade
| b′
? ???íF
?
z INTO0
– ·?1? ?
? ￥V #
??
·?1? ?
? ￥V #
??
–
??
￥
¨? VDV?l?￥
¨??Bá
??
￥
¨? VDV?l?￥
¨??Bá
–
àμ·?
??
V
U1? ?￥
^BH??￥í
àμ·?
??
V
U1? ?￥
^BH??￥í
F O
??
??DV?l?￥
¨?Bá
– ·??s
??
? ?￥íF ?
??
| b·??s
??
? ?￥íF ?
??
| b
′
z VALUES0
– 4￥′A?D4￥′A?D * / 5 00 ? ￥
′￥?
?′￥?
?
′￥ ??
3.4.2 ??
?
z? ì
T
UPDATE <V >
SET <
>=<Vr
T >[<
>=<Vr
T >]…
[WHERE <Hq >]
z? ?
? ?
??·?V?
?@ WHERE0 Hq￥íF
??
?
?
z ????Z
T
–?? B?íF￥′
–?? ?íF￥′
–{0°?￥???
1. ?? B?íF￥′
[ è 4] |D
3 95001￥ M
-?1 22
? b
UPDATE Student
SET Sage=22
WHERE Sno=' 95001 '
2. ?? ?íF￥′
[ è 5] |
?μD
3￥ M
-9F 1
? b
UPDATE Student
SET Sage= Sage+1
?? ?íF￥′ (?
?
)
[ è 6] |?"
?μD
3￥ M
-9F 1
? b
UPDATE Student
SET Sage= Sage+1
WHERE Sdept=' IS '
??
?
?
– SET0
·???Z
T
1??￥
1??￥
??a |′
??a |′
– WHERE0
·?1??￥íF
?
8V
U1??V?￥
?μíF
??
?
?
% # . 4 3????
H?_°???T
^? ?êV
X?l￥????5
–
L8???
– ?
??
???
– ¨??l￥???
z / 0 5 / 6 - -?
?
z 6 / * 2 6 &?
?
z ′×?
?
3.4.3 ÷"
?
DELETE
FROM <V >
[WHERE <Hq >]
– ? ?
? ?
? ÷"·?V?
?@ WHERE0 Hq￥ íF
– WHERE0
?·?1 ÷"￥íF
? ?
8V
U1??V?￥
?μíF
÷"
?
?
z ?? ÷"Z
T
– ÷" B?íF￥′
– ÷" ?íF￥′
–{0°?￥ ÷"?
1. ÷" B?íF￥′
[ è 8] ÷"D|1 95019￥D
3:
c b
DELETE
FROM Student
WHERE Sno='95019'
2. ÷" ?íF￥′
[ è 9] ÷" 2| [?￥
?μê [:
c b
DELETE
FROM SC
WHERE Cno='2'
[ è 10] ÷"
?μ￥D
3ê [:
c b
DELETE
FROM SC
÷"
? (?
?
)
DBMS 3?? ??
H?_°
??íF
^? ?êV
X?l￥????5
– ?v???
? ?
? ÷"
? ) ó ÷"
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.2 ?V°?
?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
?￥′
°?üV9
?￥′
SELECT0 ￥ < "S
Vr
T >1Vr
T
–
?
?Vr
T
– 3?1è
– f
?
–
Y
– ?
3. °?üV9
?￥′
°?üV9
?￥′
[ è 4] ° ?8D
3￥? #
3 Mz b
SELECT Sname2000-Sage
FROM Student
{2T
Sname 2000-Sage
--------- -------------
ù§ 1976
>? 1977
| 1978
f ? 1978
3. °?üV9
?￥′
°?ü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
?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" |′×ˉ￥?
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
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
(3) ??"?
??"?
P¨ìM IN <′V >, NOT IN <′V >
<′V > ¨ù|s?￥BF |′
[ è 12]°??"
IS a
?D"
MA?9
? SD"
CSD
3￥? ??Y b
SELECT SnameSsex
FROM Student
WHERE Sdept IN ( 'IS''MA''CS' );
(3) ??"?
??"?
[ è 13]°?;?
^?" a
?D"9?
^9
?
SD"￥D
3￥? ??Y b
SELECT SnameSsex
FROM Student
WHERE Sdept NOT IN ( 'IS''MA''CS' );
(4) 3?1 ? ￥
z [NOT] LIKE ‘< ? ￥1 ? ￥1 >’ [ESCAPE ‘ <D
?3? >’]
< ? ￥1
? ￥1
> ·? ? ￥ e
·? ? ￥ e
? ￥ e %?3?1cY ￥?￥3?1
? ? ￥ e1%?3?1
H
V[¨ =
?? |}
?? |}
LIKE ìM
ìM
¨
¨
!=
< >
?? |}
?? |}
NOT LIKE ìM
ìM
Y ￥?
Y ￥?
? % (?s|?s| ) }V ?ié
é V[1}V ?ié
é V[1 0￥3?1
– è a%bV
U[ a 7h[ b2á￥ ?ié￥3?2á￥ ?ié￥3?
1 b ? acbaddgbab??
?@? ? ￥1
??
?@? ? ￥1
? _ (/?L/?L ) }V ?i??3?}V ?i??3?
– è a_bV
U[ a 7h[ b2á￥é12á￥é1 3￥ ?i3
?1 b ? acbafb??
?@? ? ￥1
??
?@? ? ￥1
ESCAPE ?
–?¨?1°?￥3?1'
&ücμ %
_
H1
P¨ ESCAPE '<D
?3? >'
?Y ￥?é?l b
è5
1) ? ￥ e1%?3?1
[ è 14] °?D|1 95001￥D
3￥W% f ? b
SELECT *
FROM Student
WHERE Sno LIKE '95001'
?N?
SELECT *
FROM Student
WHERE Sno = '95001'
è5
?
2) ? ￥ e1cY ￥?￥3?1
[ è 15] °?
?μ?
>D
3￥? aD|??Y b
SELECT SnameSnoSsex
FROM Student
WHERE Sname LIKE ‘
>%’
è5
?
? ￥ e1cY ￥?￥3?1
?
[ è 16] °?? " x " O ? 1 ??q3￥D
3￥
? b
SELECT Sname
FROM Student
WHERE Sname LIKE ' x __'
è5
?
? ￥ e1cY ￥?￥3?1
?
[ è 17] °? 3?? 2?31 " "3￥D
3￥?
?D| b
SELECT SnameSno
FROM Student
WHERE Sname LIKE '__ %'
è5
?
? ￥ e1cY ￥?￥3?1
?
[ è 18] °?
?μ??
>￥D
3? b
SELECT SnameSnoSsex
FROM Student
WHERE Sname NOT LIKE '
>%'
è5
?
3)
P¨D
?3?|Y ￥?l1 ?Y3?
[ è 19] °? DB_Design [?￥ [?|?Ds b
SELECT CnoCcredit
FROM Course
WHERE Cname LIKE 'DB\_Design'
ESCAPE '\'
è5
?
P¨D
?3?|Y ￥?l1 ?Y3? (? )
[ è 20] °?[ "DB_" 7h O?
?? 3?3?1 i
￥ [?￥W% f ? b
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i_ _' ESCAPE ' \ '
(5)
# b′￥°?
–
P¨ìM IS NULL IS NOT NULL
– “IS NULL”? ?¨ “= NULL”}9
[ è 21] tD
3ê? [?a
àμ?F I
k
?[μê [
:
c?
àμ I
k? b°? ?
?￥D
3￥D|
?M?￥ [?| b
SELECT SnoCno
FROM SC
WHERE Grade IS NULL
è5 (?
?
)
[ è 22] °
?μμ?￥D
3D|? [?| b
SELECT SnoCno
FROM SC
WHERE Grade IS NOT NULL
(6) ×Hq°?
¨
?
?? AND? OR ? ó2 ?°?Hq
z AND￥a5)ú? OR
z V[¨ ?|?Ma5)
V¨ ?
LC ?
eìM
z [NOT] IN
z [NOT] BETWEEN … AND …
è5
[ è 23] °?9
?" M
- 20
?[/￥D
3?
b
SELECT Sname
FROM Student
WHERE Sdept= 'CS' AND Sage<20
?
?
[ è 12]
[ è 12] °??"
IS a
?D"
MA?9
? SD"
CSD
3￥? ??Y b
SELECT SnameSsex
FROM Student
WHERE Sdept IN ( 'IS''MA''CS' )
V?1
SELECT SnameSsex
FROM Student
WHERE Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS '
?
?
[ è 10]
[ è 10] °? M
- 20~23
?
? 20
?? 23
?
-W￥D
3￥? a"Y? M
- b
SELECT SnameSdeptSage
FROM Student
WHERE Sage BETWEEN 20 AND 23
V?1
SELECT SnameSdeptSage
FROM Student
WHERE Sage>=20 AND Sage<=23
? a°?2T ??
? a°?2T ??
P¨ ORDER BY0
z V[?B? ?
??
??
z
6? ASC ?? DESC ?
8′1
6?
? ??
c b′
H
z ASC ??
1 b′￥íFKaA
U
z DESC ??
1 b′￥íFK5A
U
°?2T ??
?
[ è 24] °?ê?
3| [?￥D
3￥D|#
?°?2T?s
??? ?
b
SELECT SnoGrade
FROM SC
WHERE Cno= ' 3 '
ORDER BY Grade DESC
°?2T
°?2T
Sno Grade
------- -------
95010
95024
95007 92
95003 82
95010 82
95009 75
95014 61
95002 55
°?2T ??
?
[ è 25] °? ?8D
3 f ?°?2T?
?
"￥"|
6? ?
]B"?￥D
3
? M
-?? ?
b
SELECT *
FROM Student
ORDER BY SdeptSage DESC
1 a
P¨
"f
?
5 ??1"f
?
– 9
?
COUNT
[DISTINCT|ALL] *
COUNT
[DISTINCT|ALL] <
>
– 9
?9?
SUM
[DISTINCT|ALL] <
>
– 9
? ü (′
AVG
[DISTINCT|ALL] <
>
P¨"f
?
?
pKv′
MAX
[DISTINCT|ALL] <
>
pKl′
MIN
[DISTINCT|ALL] <
>
– DISTINCT ? 9
?
H1 |h·?
?
￥×ˉ′
– ALL ? ? |h×ˉ′
– ALL1 ?
8′
P¨"f
?
?
[ è 26] °?D
39 |
? b
SELECT COUNT(*)
FROM Student
[ è 27] °?ê?
[?￥D
3 |
? b
SELECT COUNT(DISTINCT Sno)
FROM SC
? ¨ DISTINCT[E
?×ˉ9
?D
3 |
?
P¨"f
?
?
[ è 28] 9
? 1| [?￥D
3 ü (? b
SELECT AVG(Grade)
FROM SC
WHERE Cno= ' 1 '
[ è 29] °?ê? 1| [?￥D
3Kús
? b
SELECT MAX(Grade)
FROM SC
WHER Cno= ' 1 '
? a°?2TsF
P¨ GROUP BY0 sF
%?"f
?￥T¨`
– ?°?2TsF"f
?|T¨???°
?2T
– °?2TsFa"f
?|sYT¨?
?
?F
P¨ GROUP BY0 sF
[ è 30] pò? [?|#M?￥ê [ |
? b
SELECT CnoCOUNT(Sno)
FROM SC
GROUP BY Cno
2T
Cno COUNT(Sno)
1 22
2 34
3 44
4 33
5 48
°?2TsF
?
z GROUP BY0 ￥T¨`
^°?￥?W2T
V
z sFZE ?·? ￥B
′sF′M
?￥1BF
z
P¨ GROUP BY0 a SELECT0 ￥
V?o ?CsF
???"f
?
P¨ HAVING ? ?êK?
{2T
? ?êK?
{2T
[ è 31] °?ê?
3
ê[
[?￥D
3D| b
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3
è5
[ è 32] °?μ 3
ê[
[?
^ 90s[
￥
D
3￥D|#
90s[
￥ [?
?
SELECT Sno, COUNT(*)
FROM SC
WHERE Grade>=90
GROUP BY Sno
HAVING COUNT(*)>=3;
P¨ HAVING ? ?êK?
{2T
? ?êK?
{2T
z oμ
?@ HAVING ?·?Hq￥F?
{
z HAVING ?D WHERE0 ￥ uY T¨
`?]
– WHERE0 T¨?V
jmV?ê4
?@Hq￥íF b
– HAVING ?T¨?FV?ê4
?@Hq
￥F b
3.5.2 ?¤°?
?¤°?
]
H
# ?V￥°??1 ?¤°?
¨ ? ?¤
?V￥Hq?1 ?¤Hq ?¤ìM
B?ì
T
z [<V 1>.]<
1> <1?
?? > [<V 2>.]<
2>
1?
?? = a> a< a>= a<= a!=
z [<V 1>.]<
1> BETWEEN [<V 2>.]<
2>
AND [<V 2>.]<
3>
?¤°?
?¤°?
?
z ?¤3
– ?¤ìM?￥
?1 ?¤3
– ?¤Hq?￥ò ?¤3
??A?
^ V1
￥??A
^M]￥
?¤?T￥3?V?
z 3*?ìE (NESTED-LOOP)
–
n5 V 1?s??B?íF ?aVh 7
S á
íV
2?B°s
?@ ?¤q￥íFs?aü|V 1?￥
?B?íFD?íF ó¤ ???2TV?B?í
F b
– V 2 ??°s?a sV 1??=?íF ?a V
h 7
S á
íV 2?B°s
?@ ?¤Hq￥íFs?
aü|V 1?￥?=?íFD?íF ó ¤ ???2
TV?B?íF b
– ×ˉ
??T°?V 1?￥ ??íF?) ??8
???iE
???iE
(SORT-MERGE)
è¨? = ?¤
–
n5? ?¤
??V 1?V 2 ??
– V 1￥?B?íFVh 7
S á
íV 2
¨?°s
?@ ?¤Hq￥íFs?aü|V 1?￥?B?
íFD?íF ó¤ ???2TV?B?íF b
???V 2??BHv?V 1 ?¤3
′￥íF
H
V 2￥°?? ??
???iE
???iE
– s?V 1￥?=HíF ?aVD?￥??)?
?
¨? á
íV 2°s
?@ ?¤Hq￥íFs?
aü|V 1?￥?B?íFD?íF ó¤ ??
?2TV?B?íF b°¤??V 2?v?V 1 ?¤
3
′￥íF
HV 2￥°?? ??
– ×ˉ
??T°?V 1V 2?￥ ??íF?) ?
?81?
ì? ?¤ (INDEX-JOIN)
–V 2? ?¤3
y ?
ì?
–V 1?￥
??íFGQ? ?¤3
′°?V 2￥
ì?V?s?
?@Hq￥í
Fs?aü|V 1?￥?B?íFD?í
F ó¤ ???2TV?B?íF
3.5.2 SQL?￥ ?¤°?
?￥ ?¤°?
SQL? ?¤°?￥?1 ??
– <l2 5:
– ?′ ?¤ (c1 ? ?¤ )
– d?′ ?¤°?
– 1
& ?¤°?
– ? ?¤°?
– ˉ?Hq ?¤°?
B a<l2 5:
z?{ ?¤ìM￥ ?¤
z ?
P¨
è
SELECT Student.* , SC.*
FROM Student, SC
= a?′Dd?′ ?¤°?
= a?′Dd?′ ?¤°?
?′ ?¤ a1 ? ?¤ ad?′ ?¤
[ è ] °?
??D
3# ê? [?￥ f ? b
SELECT Student.*SC.*
FROM StudentSC
WHERE Student.Sno = SC.Sno
?′ ?¤
z ?¤
??1 = ￥ ?¤?T
– [<V 1>.]<
1> = [<V 2>.]<
2>
– ??0 ??¨V 1?V 2? ]
??
H?A?F
V - b?¨·B
??
H V[F9 V[
8
{V
- b
?′ ?¤
L
! StudentV a SCVsYμ/
?
StudentV
Sno Sname Ssex
Sage
Sdept
95001 ù§ 3 20 CS
95002
>? o 19 IS
95003 |
ù o 18 MA
95004 f ? 3 19 IS
?′ ?¤
SCV
Sno Cno Grade
95001 1 92
95001 2 85
95001
95002
95002
3
2
3
88
90
80
?′ ?¤
2TV
Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade
95001 ù§ 3 20 CS 95001 1 92
95001 ù§ 3 20 CS 95001 2 85
95001 ù§ 3 20 CS 95001 3 88
95002
>? o 19 IS 95002 2 90
95002
>? o 19 IS 95002 3 80
1 ? ?¤
z?′ ?¤￥B?+
y f ?ü "S
?
×ˉ￥
??
?? b
[ è ]
è¨1 ? ?¤?? b
SELECT Student.SnoSnameSsexSage
SdeptCnoGrade
FROM StudentSC
WHERE Student.Sno = SC.Sno
d?′ ?¤°?
d?′ ?¤°?
?¤
???
^ = ￥ ?¤?T
[<V 1>.]<
1><1?
?? >[<V 2>.]<
2>
1?
?? > a< a>= a<= a!=
[<V 1>.]<
1> BETWEEN [<V 2>.]<
2> AND
[<V 2>.]<
3>
? a1
& ?¤
z B?VD 1-é? ?¤?1V￥ 1
& ?¤
z 31óV Y [
U uY
z ??
?μ
?? ?
^]
??yNA?
P¨
Y -
1
& ?¤
?
[ è ] °?
?B
ê [￥W¤5? [
'5?
[￥5? [
SELECT FIRST.CnoSECOND.Cpno
FROM Course FIRSTCourse SECOND
WHERE FIRST.Cpno = SECOND.Cno
1
& ?¤
?
FIRSTV
CourseV
Cno Cname Cpno Ccredit
1
? o
5 4
2
?D
2
3
?"d
1 4
4
?T"d
6 3
5
? 2
7 4
6
? ) ?
2
7 PASCAL?y 6 4
1
& ?¤
?
SECONDV
CourseV
Cno Cname Cpno Ccredit
1
? o
5 4
2
?D
2
3
?"d
1 4
4
?T"d
6 3
5
? 2
7 4
6
? ) ?
2
7 PASCAL?y 6 4
1
& ?¤
?
°?2T
1 7
3 5
5 6
cpnocno
1 a? ?¤
Outer Join
z? ?¤D ?Y ?¤￥ uY
– ?Y ?¤?To
{
?@ ?¤Hq￥íF
– ? ?¤?T[·?V1 ?¤?8|?8V
??
?@ ?¤Hq￥íFBi
{
? ?¤
?
[ è ] °?
??D
3# ê? [?￥ f ? ?
àμ
ê? [?￥D
3 ----¨? ?¤?T
SELECT Student.SnoSnameSsex
SageSdeptCnoGrade
FROM StudentSC
WHERE Student.Sno = SC.Sno(*)
? ?¤
?
2T
Student.Sno Sname Ssex Sage Sdept Cno Grade
95001 ù§ 3 20 CS 1 92
95001 ù§ 3 20 CS 2 85
95001 ù§ 3 20 CS 3 88
95002
>? o 19 IS 2 90
95002
>? o 19 IS 3 80
95003 |
ù o 18 MA
95004 f ? 3 19 IS
? ?¤
?
– V a
?F? ?¤?T? (*) (+)·?d?
8V
–d?8VμB “￡ ? ”￥′??? ??? b
′F?
–′? V[??8V?
?μ?
?@ ?¤Hq￥
íFé? ?¤
–??′?ò
??
^ b′yND′? ?¤
￥2T? ?1d?8V￥
??′ ??
^ b
′
? ?¤
?
z P? ?¤
– ? ?¤?C ?¤Hq￥PH
z ·? ?¤
– ? ?¤?C ?¤Hq￥·H
? aˉ?Hq ?¤
WHERE0 ?c ? ?¤Hq
H?1ˉ?Hq ?¤
[ è ]°?ê? 2| [? O? 90s[
￥
?μD
3￥
D| a?
SELECT Student.Sno, student.Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND
/* ?¤ìM */
SC.Cno= ' 2 ' AND /*
eK?Hq */
SC.Grade > 90 /*
eK?Hq */
V ?¤
[ è ] °?
??D
3￥D| a? aê?￥ [ ? #?
b
SELECT Student.SnoSnameCnameGrade
FROM StudentSCCourse
WHERE Student.Sno = SC.Sno
and SC.Cno = Course.Cno
2T
Student.Sno Sname Cname Grade
95001 ù§
? o 92
95001 ù§
?D 85
95001 ù§ ?"d 88
95002
>?
?D 90
95002
>? ?"d 80
? ?
? ?
?
?
?
z a °
?B
z a °
?=
z a °
? ?
