Chöông 2. NGOÂN NGÖÕ TRUY VAÁN SQL ICAÙCH TAÏO QUAN HEÄ BAÈNG ACCESS
Microsoft Access laø moät heä quaûn trò cô sôû döõ lieäu quan heä. Ta coù theå duøng HQTCSDL Access hay goïi taét laø Access ñeå thöïc haønh moâ hình quan heä.
Sau ñaây ta seõ taïo löôïc ñoà csdl quaûn lyù sinh vieân nhö ôû trang 6 cuûa taøi lieäu.
-
Khôûi ñoäng Access: Start-> Programs-> Microsoft Access.
-
Taïo löôïc ñoà csdl roãng coù teân laø qLSV: Blank Database->OK->qLSV->Create
-
Taïo quan heä baèng caùch:Tables->New->Design View->OK
-
Data type laø Text (kieåu chuoãi), Yes/No (Kieåu luaän lyù), Date/Time (kieåu ngaøy thaùng), Currency (kieåu soá)
-
Field size laø kích thöôùc kieåu döõ lieäu
-
Khi taïo xong caùc thuoäc tính vaø khoùa chính ta löu laïi vaø ñaët teân cho quan heä Sv baèng caùch : File->Close->Yes->Sv->OK.
-
Taïo caùc quan heä coøn laïi theo böôùc 3 vaø 4
-
Nhaäp döõ lieäu cho quan heä Sv baèng caùch: Tables->Sv->Open
IICAÂU LEÄNH TRUY VAÁN 1BIEÅU THÖÙC (EXPRESSION)
Caùc thaønh phaàn taïo neân bieåu thöùc bao goàm:
Literal value
Laø caùc döõ lieäu coù giaù trò ñuùng nhö vaên baûn theå hieän.
Döõ lieäu chuoãi coù daïng: “New York”
Döõ lieäu soá coù daïng: 1056; 1056.25
Döõ lieäu ngaøy coù daïng: #1-Jan-94#; #12/2/2001#
Constant
Laø moät teân ñaïi dieän cho moät giaù trò khoâng thay ñoåi nhö :
Const
|
Giaù trò ñaïi dieän
|
True
|
-1
|
False
|
0
|
Null
|
0
|
Toaùn töû soá hoïc:
Toaùn töû
|
YÙ nghóa
|
Ví duï
|
Keát quaû
|
+
|
Coäng soá hoïc
Coäng ngaøy
|
5+2
#28/08/01# + 4
|
7
#01/09/01#
|
-
|
Tröø soá hoïc
Ngaøy tröø soá
Ngaøy tröø ngaøy
|
5-2
#02/09/01# - 3
#29/9/01#-#24/3/84#
|
3
#30/08/01#
16.597
|
*
|
Pheùp nhaân
|
5*2
|
10
|
/
|
Pheùp chia
|
5/2
|
2.5
|
\
|
Chia nguyeân
|
5\2
|
2
|
^
|
Luyõ thöøa
|
5^2
|
25
|
Mod
|
Laáy soá dö cuûa pheùp chia
|
5 Mod 2
|
1
|
Toaùn töû luaän lyù
Toaùn töû
|
YÙ nghóa
|
Ví duï
|
Keát Quaû
|
Not
|
Luaät phuû ñònh
|
Not True
Not False
|
False
True
|
And
|
Luaät vaø
|
True And True
True And False
False And True
False And False
|
True
False
False
False
|
Or
|
Luaät hay
|
True Or True
True Or False
False Or True
False Or False
|
True
True
True
False
|
Toaùn töû so saùnh
Toaùn töû
|
YÙ nghóa
|
Ví duï
|
Keát quaû
|
<
|
Nhoû hôn
|
2 < 5
|
True
|
<=
|
Nhoû hôn hay baèng
|
2 <= 5
|
True
|
>
|
Lôùn hôn
|
2 > 5
|
False
|
>=
|
Lôùn hôn hay baèng
|
2 >= 5
|
False
|
=
|
Baèng nhau
|
2 = 5
|
False
|
<>
|
Khaùc nhau
|
2 <> 5
|
True
|
Caùc toaùn töû khaùc
Toaùn töû
|
YÙ nghóa
|
Ví duï
|
Keát quaû
|
Between ... And ...
|
Giöõa hai giaù trò. Duøng trong query
|
2 Between 1 And 5
|
True
|
Like
|
Gioáng nhö
|
“Hung” Like “Hu*”
|
True
|
&
|
Noái chuoãi
|
“Nguyeãn Vaên” & “Huøng”
|
“Nguyeãn Vaên Huøng”
|
Vôùi toaùn töû like ta coù theå duøng caùc kyù töï ñaïi dieän sau:
Wildcard characters
|
Kyù töï
|
YÙ nghóa
|
Ví duï
|
?
|
Ñaïi dieän cho moät kyù töï baát kyø
|
A? -> AN, AM, AC
|
*
|
Ñaïi dieän cho moät chuoãi kyù töï baát kyø.
|
A* -> AI, ANH,
|
#
|
Ñaïi dieän cho moät kyù soá
|
A## ->A13, A24, A35
|
[]
|
Ñaïi dieän cho caùc kyù töï naèm trong
|
A[IN] -> AI, AN
|
-
|
Ñaïi dieän cho caùc kyù töï naèm trong khoaûng
|
A[M-O] -> AM, AN, AO
|
!
|
Ñaïi dieän cho kyù töï khoâng phaûi kyù töï naèm sau !
|
A[!C] -> AA, AB, AD, AE, ...
|
Haøm:
Haøm coù daïng tenHam(danhSachDoiSo). Haøm luoân luoân ñaïi dieän cho moät trò goïi laø trò traû veà.
IIf(ñieàu kieän, trò 1, trò 2)
Kieåm tra ñieàu kieän, neáu ñieàu kieän ñuùng traû trò 1 ngöôïc laïi traû trò 2
Ví duï: IIf(namNu = 1, “Nam”,”Nu”)
Date()
Traû veà ngaøy thaùng naêm cuûa heä thoáng.
Now(bieåu thöùc ngaøy)
Traû veà giôø, phuùt, giaây, ngaøy thaùng naêm cuûa heä thoáng.
Time(bieåu thöùc ngaøy)
Traû veà giôø phuùt giaây cuûa heä thoáng.
Day(bieåu thöùc ngaøy)
Traû veà moät soá töø 1 ñeán 31 laø ngaøy cuûa Date.
Month(bieåu thöùc ngaøy)
Traû veà moät soá töø 1 ñeán 12 laø thaùng cuûa Date
Year(bieåu thöùc ngaøy)
Traû veà naêm cuûa ngaøy
Len( bieåu thöùc chuoãi)
Traû veà chieàu daøi cuûa chuoãi.
Chr(maõ Ascii)
Traû veà kyù töï coù maõ ASCII töông öùng.
InStr(Start, s1, s2)
Traû veà vò trí chuoãi s2 naèm trong s1
LCase(s), UCase(s)
Ñoåi chuoãi s thaønh chuoãi goàm caùc kyù töï thöôøng (hoa)
Left(s, n), Right(s, n)
Traû veà chuoãi goàm n kyù töï beân traùi (phaûi) cuûa chuoãi s
Mid(s, i, n)
Traû veà chuoãi con cuûa chuoãi s, goàm n kyù töï keå töø kyù töï thöù i
Nz(v1, v2)
Neáu v1 = Null thì Traû veà v2, ngöôïc laïi traû veà v1
Caùc haøm tính toaùn treân nhoùm:
SUM (thuoäc tính )
Tính toång giaù trò cuûa thuoäc tính cuûa caùc boä trong baûng
MAX( thuoäc tính)
tính giaù trò lôùn nhaát cuûa thuoäc tính cuûa caùc boä trong baûng
MIN(thuoäc tính)
tính giaù trò nhoû nhaát cuûa thuoäc tính cuûa caùc boä trong baûng
AVG(thuoäc tính>)
tính giaù trò trung bình cuûa thuoäc tính cuûa caùc boä trong baûng
COUNT(thuoäc tính)
chæ ñeám nhöõng boä maø giaù trò cuûa thuoäc tính laø khaùc NULL
Bieåu thöùc
Bieåu thöùc laø toå hôïp caùc toaùn töû, literal value, haèng, teân haøm, teân thuoäc tính.
Bieåu thöùc ñöôïc löôïng gía thaønh moät gía trò.
2CAÂU LEÄNH SQL
SQL laø ngoân ngöõ truy vaán döïa treân ñaïi soá quan heä. Caâu leänh cuûa SQL duøng ñeå ruùt trích döõ lieäu cuûa moät moät hay nhieàu quan heä. Keát quaû cuûa moät caâu leänh SQL (truy vaán) laø moät quan heä. Ñeå ñôn giaûn trong caùch trình baøy, ta xem quan heä maø caâu truy vaán söû duïng ñeå taïo ra quan heä khaùc goïi laø quan heä nguoàn, quan heä keát quaû cuûa truy vaán laø quan heä ñích.
iTruy vaán ñònh nghóa döõ lieäu (data definition query)
Taïo löôïc ñoà quan heä rSV:
Taïo löôïc ñoà quan heä rKQ:
-
Create Table rKQ (MASV text (10),MAMH Text (10), DIEMTHI Double,CONSTRAINT khoaChinh PRIMARY KEY (MASV,MAMH) )
Chuù yù: Hai caùch taïo treân cho ta thaáy hai caùch taïo khoùa: khoùa chæ goàm moät thuoäc tính vaø khoùa coù nhieàu thuoäc tính
Theâm coät DTHOAI coù kieåu döõ lieäu text 20 kyù töï vaøo löôïc ñoà quan heä rSV:
-
Alter Table rSV Add Column DTHOAI Text (20)
Söûa kieåu kieåu döõ lieäu cuûa moät coät:
Xoùa coät cuûa moät löôïc ñoà ñaõ coù:
-
Alter Table rSv Drop Column DTHOAI
Xoùa raøng buoäc khoùa chính:
Theâm raøng buoäc khoùa chính:
-
Alter Table rSv Add Constraint khoaChinh Primary Key (MASV)
Theâm raøng buoäc mieàn giaù trò:
-
ALTER TABLE doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1)
iiTruy vaán choïn (select query)
Khi coù nhu caàu theå hieän caùc doøng döõ lieäu cuûa moät quan heä hay cuûa nhieàu quan heä döôùi daïng moät quan heä coù soá coät vaø soá doøng theo yù muoán nhö baûng ñieåm cuûa sinh vieân, danh saùch sinh vieân thì ta söû duïng truy vaán choïn.
Ñeå truy vaán choïn ta söû duïng caâu leänh SQL sau:
Select [Distinct|Top n[%]] field1 [As alias1][,field2 [As alias2][,...]]
From table1 [Inner Join table2 On table1.field1 table2.field2] ...
[Where dieuKien]
[Order By field1[Asc|Desc][,field2[Asc|Desc]][,...]]];
|
Distinct: loaïi boû caùc boä truøng trong quan heä ñích
Top n[%]: Choïn n hay n% maãu tin ñaàu tieân.
table: Teân table hay query chöùa döõ lieäu.
field: Teân field hay moät bieåu thöùc.
Alias: Tröôøng hôïp field laø moät bieåu thöùc thì laø moät teân môùi cuûa bieåu thöùc.
Inner Join: moãi maãu tin cuûa table1 noái vôùi baát kyø maãu tin naøo cuûa table2 coù döõ lieäu cuûa field1 thoûa maõn ñieàu kieän so saùnh vôùi döõ lieäu cuûa field2 taïo thaønh maãu tin cuûa query.
ñieàu kieän: Bieåu thöùc maø döõ lieäu maãu tin phaûi thoûa maõn
Chuù yù:
Khi neâu roõ thuoäc tính ñoù thuoäc veà quan heä naøo ta vieát theo cuù phaùp teânQuanHeä.teânThuoäcTính.
Ví duï: Laäp danh saùch sinh vieân goàm MASV,HOTEN,HOCBONG
SELECT MASV,HOTEN,HOCBONG
FROM Sv;
Ví duï: Laäp danh saùch sinh vieân nöõ , coù hoïc boång thuoäc coù maõ lôùp laø “CÑTH2B”.
SELECT MASV,HOTEN,HOCBONG
FROM Sv
WHERE NU And MALOP='CÑTH2B' And HOCBONG>0;
Ví duï: Laäp danh saùch sinh vieân goàm MASV,HOTEN,HOCBONG trong ñoù hoïc boång ñöôïc saép giaûm daàn
SELECT MASV,HOTEN,HOCBONG
FROM Sv
ORDER BY HOCBONG DESC;
Ví du : Laäp danh saùch sinh vieân goàm MASV,HOTEN,TENLOP
SELECT MASV,HOTEN,TENLOP
FROM Sv Inner Join Lop On Sv.MALOP=Kh.LOP;
Ví duï: Laäp danh saùch sinh vieân coù MASV,HOTEN,NU,NGAYSINH,DIEMTHI vôùi ñieåm thi moân ‘CSDL’ >= 8
SELECT Kq.MASV,HOTEN,NU,NGAYSINH,DIEMTHI
FROM Kq Inner Sv On Kq.MASV = Sv.MaSV
WHERE MAMH='CSDL' AND DIEMTHI>=8;
Ví duï: Laäp danh saùch sinh vieân coù ho laø “NGUYEN”
SELECT *
FROM Sv
WHERE HOTEN Like “NGUYEN*”;
iiiTruy vaán nhoùm döõ lieäu (Select query coù group by)
Khi coù nhu caàu chia caùc boä theo töøng nhoùm roài tính toaùn treân töøng nhoùm naøy nhö tính ñieåm trung bình cuûa taát caû caùc moân hoïc cuûa töøng sinh vieân, hay laø caàn tính soá löôïng sinh vieân cuûa moãi lôùp , hay laø caàn bieát toång soá moân maø moät sinh vieân ñaõ ñaêng kyù hoïc thì ta söû duïng truy vaán theo nhoùm.
Ñeå truy vaán coäng nhoùm ta söû duïng caâu leänh SQL sau:
Select [Distinct|Top n[%]] field1 [As alias1][,field2 [As alias2][,...]]
From table1 [Inner Join table2 On table1.field1 table2.field2] ...
[Where dieuKienLocMauTinNguon]
[Group By fieldGroupBy[,fieldGroupBy[,...]]
[Having dieuKienLocMauTinTongHop]
[Order By field1[Asc|Desc][,field2[Asc|Desc]][,...]]];
ñieàuKieänLoïcMaãuTinNguoàn: ñieàu kieän maø caùc maãu tin nguoàn phaûi thoûa maõn (pheùp choïn)
fieldGroupBy: teân field maø caùc maãu tin coù döõ lieäu gioáng nhau treân aáy ñöôïc xeáp vaøo cuøng nhoùm.
ñieàuKieänLoïcMaãuTinToångHôïp: ñieàu kieän maø caùc maãu tin toång hôïp phaûi thoûa maõn (pheùp choïn)
Ví du:ï Laäp danh saùch sinh vieân coù ñaêng kyù ít nhaát laø ba moân hoïc
SELECT Kq.MASV,HOTEN,NU,NGAYSINH,COUNT(MAMH) As SLMH
FROM Sv Inner Join Kq On Sv.MASV = Kq.MASV
GROUP BY Kq.MASV,HOTEN,NU,NGAYSINH
HAVING COUNT(MAMH)>=3;
Ví du: Ñeám soá löôïng sinh vieân nöõ cuûa moãi khoa
SELECT Kh.MAKHOA,TENKHOA,COUNT(Kh.MAKHOA) AS SOLUONG
FROM (Sv Inner Join Lop On Sv.MALOP = LOP.MALOP)
INNER JOIN Kh On Lop.MAKHOA=Kh.MAKHOA
WHERE NU
GROUP BY Kh.MAKHOA,TENKHOA;
ivTruy vaán loàng nhau (nested query, sub query)
Laø nhöõng caâu leänh truy vaán maø trong thaønh phaàn WHERE hay HAVING coù chöùa theâm moät caâu leänh Select khaùc. Caâu leänh select khaùc naøy goïi laø subquery. Ta loàng caâu Select vaøo phaàn Where hay Having theo cuù phaùp sau:
-
bieuthuc toanTuSoSanh [ANY | ALL | SOME] (cauLenhSQL)
ANY, SOME laø baát kyø, ALL laø taát caû
Caùc maãu tin cuûa query chính thoûa maõn toaùn töû so saùnh vôùi baát kyø/ taát caû maãu tin naøo cuûa subquery
-
bieuThuc [NOT] IN (cauLenhSQL)
Caùc maãu tin cuûa query chính coù giaù trò baèng vôùi moät giaù trò trong subquery
-
[NOT] EXISTS (cauLenhSQL).
Caùc maãu tin cuûa query chính thoûa maõn khi subquery coù maãu tin
Ví du: Laäp danh saùch sinh vieân coù hoïc boång cao nhaát
SELECT *
FROM Sv
WHERE HOCBONG>=ALL(SELECT HOCBONG FROM SV);
Ví du: Laäp danh saùch sinh vieân coù ñieåm thi moân CSDL cao nhaát
SELECT SV.MASV,HOTEN,NU,NGAYSINH,DIEMTHI
FROM sv Inner Join kq On Sv.MASV = Kq.MASV
WHERE MAMH='CSDL' AND DIEMTHI >= ALL
(SELECT DIEMTHI FROM KQ WHERE MAMH='CSDL');
Hieåu vaø vaän duïng toát leänh truy vaán döõ lieäu laø moät vieäc laøm cöïc kyø caàn thieát ñeå taïo ra caùc keát quaû cho baùo caùo, thoáng keâ soá lieäu.
vTruy vaán caäp nhaät döõ lieäu (action query, data modification query)
Cuù phaùp: Update table Set field1 = bieåuThöùc1, field2 = bieåuThöùc2 Where ñieàuKieän
Cuù phaùp: Delete From table Where ñieàuKieän
viTruy vaán hôïp (union query)
Khi coù nhu caàu thöïc hieän truy vaán coù keát quaû nhö toaùn töû hôïp, ta söû duïng caâu leänh SQL sau:
Select .....Union Select ....
Ví du: Leänh sau ñaây gaáp ñoâi danh saùch sinh vieân
SELECT MASV,HOTEN,NGAYSINH UNION SELECT MASV,HOTEN,NGAYSINH
Chia sẻ với bạn bè của bạn: |