lap trinh huong doi tuong wall
Mc lc
. SQL can bn
- Gii thieu ve SQL
- Cau lenh SELECT
- Menh de WHERE
- Toan t lo-gic AND va OR
- Toan t BETWEEN...AND
- DISTINCT
- ORDER BY
- Cau lenh INSERT
- Cau lenh UPDATE
- Cau lenh DELETE
- COUNT
. SQL nang cao
- Ham
- GROUP BY va HAVING
- Bi danh
- Cau lenh JOIN
- Cau lenh CREATE
- Cau lenh ALTER
SQL C.n Bn
Gii thieu ve SQL
SQL la chuan ngon ng ANSI de truy cap CSDL.
SQL la gi?
. SQL la viet tat c
a Structured Query Language - Ngon ng truy van cau truc.
. SQL cho phep b
n truy cap vao CSDL.
. SQL la mot chuan ngon ng c
a ANSI.
. SQL co the thc thi cac cau truy van tren CSDL.
. SQL co the lay d lieu t CSDL.
. SQL co the chen d lieu mi vao CSDL.
. SQL co the xoa d lieu trong CSDL.
. SQL co the sa doi d lieu hien co trong CSDL.
. SQL de hc :-)
Su tam bi: www.daihoc.com.vn
SQL la mot chuan
SQL la mot chuan c
a ANSI (American National Standards Institute - Vien tieu chuan quoc gia
Hoa ky) ve truy xuat cac he thong CSDL. Cac cau lenh SQL d.c s dng de truy xuat va cap
nhat d lieu trong mot CSDL.
SQL ho
t dong vi hau het cac ch..ng trinh CSDL nh. MS Access, DB2, Informix, MS SQL
Server, Oracle, Sybase v.v...
L.u y: Hau het cac ch..ng trinh CSDL ho tr SQL deu co phan m rong cho SQL ch ho
t dong
vi chinh ch..ng trinh do.
Bng CSDL
Mot CSDL th.!ng bao gom mot hoac nhieu bng (table). Moi bng d.c xac d$nh thong qua
mot ten (vi d Customers hoac Orders). Bng ch%a cac mau tin - dong (record - row), la d lieu
c
a bng.
D.i day la mot vi d ve mot bng co ten la Persons (ng.!i):
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger
Bng tren bao gom 3 mau tin (dong), moi mau tin t..ng %ng vi mot ng.!i, va bon cot
(LastName, FirstName, Address va City).
Cau truy van SQL
Vi SQL ta co the truy van CSDL va nhan lay ket qu tr ve thong qua cac cau truy van.
Mot cau truy van nh. sau:
SELECT LastName FROM Persons
Se tr ve ket qu nh. sau:
LastName
Hansen
Svendson
Pettersen
L.u y: Mot so he thong CSDL doi h'i cau lenh SQL phi ket thuc bang mot dau cham phy (;).
Chung ta se khong dung dau cham phy trong bai viet nay.
SQL la ngon ng thao tac d lieu (DML - Data Manipulation Language)
Su tam bi: www.daihoc.com.vn
SQL la cu phap de thc thi cac cau truy van. SQL cung bao gom cu phap de cap nhat - sa doi,
chen them va xoa cac mau tin.
Sau day la danh sach cac lenh va truy van d
ng DML c
a SQL:
. SELECT - lay d lieu t mot bng CSDL.
. UPDATE - cap nhat/sa doi d lieu trong bng.
. DELETE - xoa d lieu trong bng.
. INSERT INTO - them d lieu mi vao bng.
SQL la ngon ng dnh nghia d lieu (DDL - Data Definition Language)
Phan DDL c
a SQL cho phep t
o ra hoac xoa cac bng. Chung ta cung co the d$nh nghia cac
khoa (key), ch mc (index), ch d$nh cac lien ket gia cac bng va thiet lap cac quan he rang
buoc gia cac bng trong CSDL.
Cac lenh DDL quan trng nhat c
a SQL la:
. CREATE TABLE - t
o ra mot bng mi.
. ALTER TABLE - thay doi cau truc c
a bng.
. DROP TABLE - xoa mot bng.
. CREATE INDEX - t
o ch mc (khoa de tim kiem - search key).
. DROP INDEX - xoa ch mc da d.c t
o.
Cau lenh SELECT
Cau lenh SELECT
Cau lenh SELECT d.c dung de truy xuat d lieu t mot bng. Ket qu tr ve d.i d
ng bng
d.c l.u trong 1 bng, gi la bng ket qu - result table (con d.c gi la tap ket qu - result set).
Cu phap
Cu phap c
a cau lenh SELECT nh. sau:
SELECT ten_cac_cot
FROM ten_bng
Truy xuat nhieu cot
De truy xuat cac cot mang ten LastName va FirstName, ta dung mot cau lenh SELECT nh. sau:
SELECT LastName, FirstName FROM Persons
Bng Persons:
Su tam bi: www.daihoc.com.vn
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger
Ket qu tr ve:
LastName FirstName
Hansen Ola
Svendson Tove
Pettersen Kari
Truy xuat tat c cac cot
De truy xuat tat c cac cot t bng Persons, ta dung ky hieu * thay cho danh sach cac cot:
SELECT * FROM Persons
Ket qu tr ve:
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger
Tap ket qu
Ket qu tr ve t mot cau truy van SQL d.c l.u trong 1 tap ket qu (result set). Hau het cac he
thong ch..ng trinh CSDL cho phep duyet qua tap ket qu bang cac ham lap trinh nh. Move-To-
First-Record, Get-Record-Content, Move-To-Next-Record v.v...
Dau cham phy (;) phia sau cau lenh
Dau cham phy la mot cach chuan de phan cach cac cau lenh SQL neu nh. he thong CSDL cho
phep nhieu cau lenh SQL d.c thc thi thong qua mot l!i gi duy nhat.
Cac cau lenh SQL trong bai viet nay deu la cac cau lenh d.n (moi cau lenh la mot va ch mot
lenh SQL). MS Access va MS SQL Server khong d'i h'i phi co dau cham phy ngay sau moi
cau lenh SQL, nh.ng mot so ch..ng trinh CSDL khac co the bat buoc b
n phi them dau cham
phy sau moi cau lenh SQL (cho du do la cau lenh d.n). Xin nhac l
i, trong bai viet nay chung ta
se khong dung dau cham phy cuoi cau lenh SQL.
Menh .e WHERE
Menh de WHERE d
c dung de thiet lap dieu kien truy xuat.
Menh de WHERE
Su tam bi: www.daihoc.com.vn
De truy xuat d lieu trong bng theo cac dieu kien nao do, mot menh de WHERE co the d.c them
vao cau lenh SELECT.
Cu phap
Cu phap menh de WHERE trong cau lenh SELECT nh. sau:
SELECT ten_cot FROM ten_bng
WHERE ten_cot phep_toan gia_tr
Trong menh de WHERE, cac phep toan d.c s dng la
Phep toan Mo t
= So sanh bang
<> So sanh khong bang
> Ln h.n
< Nh h.n
>= Ln h.n hoac bang
<= Nh h.n hoac bang
BETWEEN Nam gi a mot khong
LIKE So sanh mau chuoi
L.u y: Trong mot so phien bn c
a SQL, phep toan <> co the d.c viet d.i d
ng !=
S
dng menh de WHERE
De lay danh sach nhng ng.!i song thanh pho Sandnes, ta s dng menh de WHERE trong cau
lenh SELECT nh. sau:
SELECT * FROM Persons
WHERE City = 'Sandnes'
Bng Persons:
LastName FirstName Address City Year
Hansen Ola Timoteivn 10 Sandnes 1951
Svendson Tove Borgvn 23 Sandnes 1978
Svendson Stale Kaivn 18 Sandnes 1980
Pettersen Kari Storgt 20 Stavanger 1960
Ket qu tr ve:
LastName FirstName Address City Year
Hansen Ola Timoteivn 10 Sandnes 1951
Svendson Tove Borgvn 23 Sandnes 1978
Svendson Stale Kaivn 18 Sandnes 1980
S
dng dau nhay
L.u y rang vi d tren ta da s dng hai dau nhay d.n (') bao quanh gia tr$ dieu kien 'Sandnes'.
Su tam bi: www.daihoc.com.vn
SQL s dng dau nhay d.n bao quanh cac gia tr$ d
ng chuoi van bn (text). Nhieu he CSDL
con cho phep s dng dau nhay kep ("). Cac gia tr$ d
ng so khong dung dau nhay de bao
quanh.
Vi d lieu d
ng chuoi van bn:
Cau lenh dung:
SELECT * FROM Persons WHERE FirstName = 'Tove'
Cau lenh sai:
SELECT * FROM Persons WHERE FirstName = Tove
Vi d lieu d
ng so:
Cau lenh dung:
SELECT * FROM Persons WHERE Year > 1965
Cau lenh sai:
SELECT * FROM Persons WHERE Year > '1965'
Phep toan dieu kien LIKE
Phep toan LIKE d.c dung de tim kiem mot chuoi mau van bn tren mot cot.
Cu phap
Cu phap c
a phep toan LIKE nh. sau:
SELECT ten_cot FROM ten_bng
WHERE ten_cot LIKE mau
Mot ky hieu % co the d.c s dng de d$nh nghia cac ky t d
i dien. % co the d.c dat tr.c
va/hoac sau mau.
S
dng LIKE
Cau lenh SQL sau se tr ve danh sach nhng ng.!i co ten bat dau bang ch O:
SELECT * FROM Persons
WHERE FirstName LIKE 'O%'
Cau lenh SQL sau se tr ve danh sach nhng ng.!i co ten ket thuc bang ch a:
SELECT * FROM Persons
WHERE FirstName LIKE '%a'
Cau lenh SQL sau se tr ve danh sach nhng ng.!i co ten ket ch%a chuoi la:
Su tam bi: www.daihoc.com.vn
SELECT * FROM Persons
WHERE FirstName LIKE '%la%'
Toan t lo-gic AND va OR
AND va OR
Hai toan t AND va OR noi hai hoac nhieu dieu kien trong menh de WHERE l
i vi nhau.
Toan t AND se hien th$ 1 dong neu TAT C. cac dieu kien deu tho man. Toan t OR hien th$ mot
dong neu BAT KY dieu kien nao d.c tho.
Bng d lieu dung trong vi d
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Svendson Stephen Kaivn 18 Sandnes
Vi d 1
S dng AND de tim nhng ng.!i co ten la Tove va h la Svendson:
SELECT * FROM Persons
WHERE FirstName = 'Tove'
AND LastName = 'Svendson'
Ket qu tr ve:
LastName FirstName Address City
Svendson Tove Borgvn 23 Sandnes
Vi d 2
S dng OR de tim nhng ng.!i co ten la Tove hoac h la Svendson:
SELECT * FROM Persons
WHERE firstname = 'Tove'
OR lastname = 'Svendson'
Ket qu tr ve:
LastName FirstName Address City
Svendson Tove Borgvn 23 Sandnes
Svendson Stephen Kaivn 18 Sandnes
Vi d 3
Su tam bi: www.daihoc.com.vn
B
n cung co the s dng ket hp AND va OR cung vi dau ngoac d.n de t
o nen cac cau truy van
ph%c t
p:
SELECT * FROM Persons WHERE
(FirstName = 'Tove' OR FirstName = 'Stephen')
AND LastName = 'Svendson'
Ket qu tr ve:
LastName FirstName Address City
Svendson Tove Borgvn 23 Sandnes
Svendson Stephen Kaivn 18 Sandnes
Toan t BETWEEN...AND
Toan t BETWEEN...AND lay ra mot mien d lieu nam gia hai gia tr. Hai gia tr nay co the la
so, chuoi van bn hoac ngay thang.
SELECT ten_cot FROM ten_bng
WHERE ten_cot
BETWEEN gia_tr_1 AND gia_tr_2
Bng d lieu dung trong vi d
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Nordmann Anna Neset 18 Sandnes
Pettersen Kari Storgt 20 Stavanger
Svendson Tove Borgvn 23 Sandnes
Vi d 1
Tim tat c nhng ng.!i co h (sap xep theo ABC) nam gia Hansen (tinh luon Hansen) va
Pettersen (khong tinh Pettersen):
SELECT * FROM Persons WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen'
Ket qu tr ve:
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Nordmann Anna Neset 18 Sandnes
L.u y quan trng: Toan t BETWEEN...END se tr ve nhng ket qu khac nhau tren cac he
CSDL khac nhau. Vi mot so he CSDL, toan t BETWEEN...END se tr ve cac dong ma co gia tr$
thc s "nam gia" hai khong gia tr$ (t%c la b' qua khong tinh den cac gia tr$ trung vi gia tr$
Su tam bi: www.daihoc.com.vn
c
a hai dau mut). Mot so he CSDL thi se tinh luon cac gia tr$ trung vi hai dau mut. Trong khi
do mot so he CSDL khac l
i ch tinh cac gia tr$ trung vi dau mut th% nhat ma khong tinh dau
mut th% hai (nh. vi d phia tren). Do vay, b
n phi kiem tra l
i he CSDL ma b
n dang dung
khi s dng toan t BETWEEN...AND.
Vi d 2
De tim nhng ng.!i co h (sap xep theo ABC) nam ngoai khong hai gia tr$ vi d 1, ta dung
them toan t NOT:
SELECT * FROM Persons WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen'
Ket qu tr ve:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Svendson Tove Borgvn 23 Sandnes
T khoa DISTINCT
T khoa DISTINCT d
c dung de lc ra cac gia tr khac nhau.
T khoa DISTINCT
Cau lenh SELECT se tr ve thong tin ve cac cot trong bng. Nh.ng neu chung ta khong muon lay
ve cac gia tr$ trung nhau thi sau?
Vi SQL, ta ch can them t khoa DISTINCT vao cau lenh SELECT theo cu phap sau:
SELECT DISTINCT ten_cot FROM ten_bng
Vi d: Tim tat c cac cong ty trong bng dat hang
Bng dat hang c
a ta nh. sau:
Company OrderNumber
Sega 3412
W3Schools 2312
Trio 4678
W3Schools 6798
Cau lenh SQL sau:
SELECT Company FROM Orders
Su tam bi: www.daihoc.com.vn
Se tr ve ket qu:
Company
Sega
W3Schools
Trio
W3Schools
Ten cong ty W3Schools xuat hien hai lan trong ket qu, doi khi day la dieu chung ta khong
muon.
Vi d: Tim tat c cac cong ty khac nhau trong bng dat hang
Cau lenh SQL sau:
SELECT DISTINCT Company FROM Orders
Se tr ve ket qu:
Company
Sega
W3Schools
Trio
Ten cong ty W3Schools bay gi! ch xuat hien 1 lan, doi khi day la dieu chung ta mong muon.
T khoa ORDER BY
T khoa ORDER BY d
c s dng de sap xep ket qu tr ve.
Sap xep cac dong
Menh de ORDER BY d.c dung de sap xep cac dong.
Vi d bng Orders:
Company OrderNumber
Sega 3412
ABC Shop 5678
W3Schools 2312
W3Schools 6798
Vi d:
De lay danh sach cac cong ty theo th% t ch cai (tang dan):
SELECT Company, OrderNumber FROM Orders
ORDER BY Company
Su tam bi: www.daihoc.com.vn
Ket qu tr ve:
Company OrderNumber
ABC Shop 5678
Sega 3412
W3Schools 6798
W3Schools 2312
Vi d:
Lay danh sach cac cong ty theo th% t ch cai (tang dan) va hoa d.n dat hang theo th% t so tang
dan:
SELECT Company, OrderNumber FROM Orders
ORDER BY Company, OrderNumber
Ket qu tr ve:
Company OrderNumber
ABC Shop 5678
Sega 3412
W3Schools 2312
W3Schools 6798
Vi d:
Lay danh sach cac cong ty theo th% t gim dan:
SELECT Company, OrderNumber FROM Orders
ORDER BY Company DESC
Ket qu tr ve:
Company OrderNumber
W3Schools 6798
W3Schools 2312
Sega 3412
ABC Shop 5678
Cau lenh INSERT INTO
Cau lenh INSERT INTO
Cau lenh INSERT INTO d.c dung de chen dong mi vao bng.
Cu phap:
Su tam bi: www.daihoc.com.vn
INSERT INTO ten_bng
VALUES (gia_tr_1, gia_tr_2,....)
B
n cung co the ch ro cac cot/tr.!ng nao can chen d lieu:
INSERT INTO ten_bng (cot_1, cot_2,...)
VALUES (gia_tr_1, gia_tr_2,....)
Chen 1 dong mi
Ta co bng Persons nh. sau:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Cau lenh SQL sau:
INSERT INTO Persons
VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')
se t
ora ket qu trong bng Persons nh. sau:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Stavanger
Chen d lieu vao cac cot/tr.ng c the
Vi bng Persons nh. tren, cau lenh SQL sau:
INSERT INTO Persons (LastName, Address)
VALUES ('Rasmussen', 'Storgt 67')
Se t
o ra ket qu:
LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Stavanger
Rasmussen Storgt 67
Cau lenh UPDATE
Cau lenh UPDATE
Cau lenh UPDATE d.c s dng de cap nhat/sa doi d lieu da co trong bng.
Cu phap:
Su tam bi: www.daihoc.com.vn
UPDATE ten_bng
SET ten_cot = gia_tr_mi
WHERE ten_cot = gia_tr
Vi d: bng Person c
a ta nh. sau:
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Storgt 67
Cap nhat 1 cot tren 1 dong
Gi s ta muon bo xung them phan ten cho ng.!i co h la Rasmussen:
UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen'
Ta se co ket qu nh. sau:
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67
Cap nhat nhieu cot tren 1 dong
Bay gi! ta l
i muon doi ten va d$a ch :
UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'
Ket qu se la:
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger
Cau lenh DELETE
Cau lenh DELETE d
c dung de xoa cac dong ra khi bng.
Cu phap:
DELETE FROM ten_bng
WHERE ten_cot = gia_tr
Vi d: Bng Person c
a ta nh. sau:
Su tam bi: www.daihoc.com.vn
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger
Xoa 1 dong:
Ta xoa ng.!i co ten la Nina Rasmussen:
DELETE FROM Person WHERE LastName = 'Rasmussen'
Ket qu sau khi xoa:
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Xoa tat c cac dong:
Doi khi ta muon xoa tat c d lieu trong bng nh.ng van gi l
i bng cung vi cau truc va tat c
cac thuoc tinh c
a bng, ta co the dung cau lenh:
DELETE FROM table_name
hoac
DELETE * FROM table_name
Ham COUNT
SQL co san lenh de dem cac dong trong CSDL.
Cu phap ca ham COUNT:
SELECT COUNT(ten_cot) FROM ten_bng
Ham COUNT(*):
Ham COUNT(*) tr ve so l.ng cac dong d.c chn trong bng.
Vi d ta co bng Persons nh. sau:
Name Age
Hansen, Ola 34
Svendson, Tove 45
Pettersen, Kari 19
Cau lenh sau se tr ve so l.ng cac dong trong bng:
Su tam bi: www.daihoc.com.vn
SELECT COUNT(*) FROM Persons
va ket qu tr ve se la:
3
Cau lenh sau se tr ve so l.ng nhng ng.!i ln h.n 20 tuoi:
SELECT COUNT(*) FROM Persons WHERE Age > 20
ket qu tr ve se la:
2
Ham COUNT(column):
Ham COUNT(column) se tr ve so l.ng cac dong co gia tr$ khac NULL cot d.c ch d$nh.
Vi d ta co bng Persons nh. sau:
Name Age
Hansen, Ola 34
Svendson, Tove 45
Pettersen, Kari
Cau lenh sau se tr ve so l.ng nhng ng.!i ma cot Age trong bng khong rong:
SELECT COUNT(Age) FROM Persons
va ket qu tr ve se la:
2
Menh de COUNT DISTINCT
L.u y: Cac vi d d.i day ch ho
t dong vi CSDL Oracle va MS SQL Server, khong ho
t
dong tren MS Access (ch.a th nhiem vi cac he CSDL khac!)
T khoa DISTINCT va COUNT co the d.c dung chung vi nhau de dem so l.ng cac ket qu
khong trung nhau.
Cu phap nh. sau:
SELECT COUNT(DISTINCT column(s)) FROM table
Vi d ta co bng Orders nh. sau:
Company OrderNumber
Su tam bi: www.daihoc.com.vn
Sega 3412
W3Schools 2312
Trio 4678
W3Schools 6798
Cau lenh SQL sau:
SELECT COUNT(DISTINCT Company) FROM Orders
se tr ve ket qu la:
3
SQL Nang Cao
Ham
SQL co san kha nhieu ham de thc hien dem va tinh toan.
Cu phap:
Cu phap de gi ham trong cau lenh SQL nh. sau:
SELECT function(ten_cot) FROM ten_bng
Bng d lieu chung ta se dung trong cac vi s tiep theo:
Name Age
Hansen, Ola 34
Svendson, Tove 45
Pettersen, Kari 19
Ham AVG(column)
Ham AVG tr ve gia tr$ trung binh tinh theo cot d.c ch d$nh c
a cac dong d.c chn. Cac gia tr$
NULL se khong d.c xet den khi tinh gia tr$ trung binh.
Vi d:
Cau lenh sau se tinh so tuoi trung binh c
a nhng ng.!i co tuoi tren 20:
SELECT AVG(Age) FROM Persons WHERE Age > 20
ket qu tr ve se la:
Su tam bi: www.daihoc.com.vn
39.5
Ham MAX(column)
Ham MAX tr ve gia tr$ ln nhat trong cot. Cac gia tr$ NULL se khong d.c xet den.
Vi d:
SELECT MAX(Age) FROM Persons
ket qu tr ve:
45
Ham MIN(column)
Ham MAX tr ve gia tr$ nh' nhat trong cot. Cac gia tr$ NULL se khong d.c xet den.
Vi d:
SELECT MIN(Age) FROM Persons
ket qu tr ve:
19
L.u y: Ham MIN va MAX cung co the ap dng cho cac cot co d lieu la chuoi van bn. D lieu
trong cot se d.c so sanh theo th% t tang dan c
a t dien
Ham SUM(column)
Ham SUM tr ve tong gia tr$ c
a cot. Cac gia tr$ NULL se khong d.c xet den.
Vi d:
Tim tong so tuoi c
a tat c nhng ng.!i co trong bng:
SELECT SUM(Age) FROM Persons
ket qu tr ve:
98
Vi d:
Tim tong so tuoi c
a tat c nhng ng.!i co tuoi ln h.n 20:
Su tam bi: www.daihoc.com.vn
SELECT SUM(Age) FROM Persons WHERE Age > 20
ket qu tr ve:
79
GROUP BY va HAVING
Cac ham tap hp (vi d nh
SUM) thong th
ng can them chc nang ca menh de GROUP
BY.
GROUP BY...
Menh de GROUP BY...d.c them vao SQL bi vi cac ham tap hp (nh. SUM) tr ve mot tap hp
c
a cac gia tr$ trong cot moi khi chung d.c gi, va neu khong co GROUP BY ta khong the nao
tinh d.c tong c
a cac gia tr$ theo tng nhom rieng l0 trong cot.
Cu phap c
a GROUP BY nh. sau:
SELECT ten_cot, SUM(ten_cot) FROM ten_bng GROUP BY ten_cot
Vi d s
dng GROUP BY:
Gi s ta co bng Sales nh. sau:
Company Amount
W3Schools 5500
IBM 4500
W3Schools 7100
Cau lenh SQL sau:
SELECT Company, SUM(Amount) FROM Sales
se tr ve ket qu:
Company SUM(Amount)
W3Schools 17100
IBM 17100
W3Schools 17100
Ket qu tr ve tren doi khi khong phi la cai ma ta mong di. Ta them menh de GROUP BY vao
trong cau lenh SQL:
SELECT Company, SUM(Amount) FROM Sales
GROUP BY Company
Su tam bi: www.daihoc.com.vn
va ket qu tr ve lan nay se la:
Company SUM(Amount)
W3Schools 12600
IBM 4500
Ket qu nay dung la cai ma ta mong muon.
HAVING...
Menh de HAVING...d.c them vao SQL vi menh de WHERE khong ap dng d.c doi vi cac ham
tap hp (nh. SUM). Neu khong co HAVING, ta khong the nao kiem tra d.c dieu kien vi cac ham
tap hp.
Cu phap c
a HAVING nh. sau:
SELECT ten_cot, SUM(ten_cot) FROM ten_bng
GROUP BY ten_cot
HAVING SUM(ten_cot) dieu_kien gia_tr
Ta s dng l
i bng Sales tren. Cau lenh SQL sau:
SELECT Company, SUM(Amount) FROM Sales
GROUP BY Company
HAVING SUM(Amount) > 10000
se tr ve ket qu:
Company SUM(Amount)
W3Schools 12600
Bi danh
Vi SQL, bi danh co the d
c s dng cho ten ca cot va ten ca bng.
Bi danh cot:
Cu phap bi danh cot nh. sau:
SELECT ten_cot AS bi_danh_cot FROM ten_bng
Bi danh bng:
Bi danh bng co cu phap nh. sau:
Su tam bi: www.daihoc.com.vn
SELECT ten_cot FROM ten_bng AS bi_danh_bng
Vi d s
dng bi danh cot:
Ta co bng Persons nh. sau:
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger
Cau lenh SQL sau:
SELECT LastName AS H, FirstName AS Ten
FROM Persons
Se tr ve ket qu:
H Ten
Hansen Ola
Svendson Tove
Pettersen Kari
Cau lenh JOIN
Noi ket va khoa
Doi khi chung ta phi lay d lieu t hai bng cung mot luc, chung ta thc hien mot ket noi.
Cac bng trong CSDL co the quan he rang buoc vi nhau thong qua cac khoa. Mot khoa chinh
(primary key) la mot cot ma trong do moi gia tr$ c
a hang phi la duy nhat. Mc dich c
a khoa
la ket noi d lieu l
i vi nhau, t nhieu bng khac nhau ma khong gay trung lap d lieu gia cac
bng.
Trong bng Employees (nhan vien) vi d d.i day co cot Employees_ID la khoa chinh, bo
dm rang khong the co hai dong nao co trung Employees_ID. Employees_ID dung de phan biet
hai nhan vien khi h trung ten.
Trong vi d d.i day:
. Employee_ID la khoa chinh c
a bng Employees.
. Prod_ID la khoa chinh c
a bng Orders.
. Cot Employeed_ID trong bng Orders d.c s dng de ket noi vi bng Employees,
ch den nhan vien trong bng Employees.
Su tam bi: www.daihoc.com.vn
Bng Employees:
Employees_ID Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari
Bng Orders:
Prod_ID Product Employee_ID
234 Printer 01
657 Table 03
865 Chair 03
Ket noi hai bng vi nhau
Chung ta co the lay d lieu t hai bng bang cach ket noi chung, t..ng t nh. sau:
Vi d: Tim xem ai da dat hang sn pham va h da dat mon hang gi:
SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID = Orders.Employee_ID
ket qu tr ve:
Name Product
Hansen, Ola Printer
Svendson, Stephen Table
Svendson, Stephen Chair
Vi d: Tim xem ai da dat hang may in:
SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID = Orders.Employee_ID
AND Orders.Product = 'Printer'
ket qu tr ve:
Name
Hansen, Ola
S
dng JOIN
Ta co the s dng t khoa JOIN de ket noi d lieu t hai bng.
Vi d: INNER JOIN
Su tam bi: www.daihoc.com.vn
Cu phap:
SELECT cot_1, cot_2, cot_3
FROM bng_1
INNER JOIN bng_2
ON bng_1.khoa_chinh = bng_2.khoa_ngoi
Ai da dat hang va h da dat mon hang nao:
SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID = Orders.Employee_ID
INNER JOIN tr ve tat c cac dong c hai bng khi chung t..ng %ng vi nhau. Neu co mot
dong bng Employees khong %ng vi dong nao bng Orders, dong do se khong dc tinh.
ket qu tr ve:
Name Product
Hansen, Ola Printer
Svendson, Stephen Table
Svendson, Stephen Chair
Vi d: LEFT JOIN
Cu phap:
SELECT cot_1, cot_2, cot_3
FROM bng_1
LEFT JOIN bng_2
ON bng_1.khoa_chinh = bng_2.khoa_ngoi
Liet ke tat c cac nhan vien va mon hang ma h dat (neu co):
SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID = Orders.Employee_ID
LEFT JOIN tr ve tat c cac dong c
a bng th% nhat (Employees), ngay c khi cac dong do
khong %ng vi dong nao bng th% hai (Orders). Neu co mot dong nao bng Employees
khong %ng vi bat c% dong nao bng Orders thi dong do cung van dc tinh.
ket qu tr ve:
Name Product
Hansen, Ola Printer
Svendson, Tove
Svendson, Stephen Table
Su tam bi: www.daihoc.com.vn
Svendson, Stephen Chair
Pettersen, Kari
Vi d: RIGHT JOIN
Cu phap:
SELECT cot_1, cot_2, cot_3
FROM bng_1
RIGHT JOIN bng_2
ON bng_1.khoa_chinh = bng_2.khoa_ngoi
Liet ke tat c cac mat hang d.c dat va ten ng.i dat hang (neu co):
SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID = Orders.Employee_ID
RIGHT JOIN tr ve tat c cac dong bng th% hai (Orders), ngay c khi cac dong do khong %ng
vi dong nao bng th% nhat (Employees). Neu co mot dong nao bng Orders khong %ng vi
bat c% dong nao bng Employees thi dong do cung van dc tinh.
ket qu tr ve:
Name Product
Hansen, Ola Printer
Svendson, Stephen Table
Svendson, Stephen Chair
Vi d: Ai da dat hang may in:
SELECT Employees.Name
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID = Orders.Employee_ID
WHERE Orders.Product = 'Printer'
ket qu tr ve:
Name
Hansen, Ola
To CSDL va bng vi CREATE
To mot CSDL
Su tam bi: www.daihoc.com.vn
CREATE DATABASE ten_CSDL
To mot bng trong mot CSDL
CREATE TABLE ten_bng
(
ten_cot_1 kieu_d _lieu,
ten_cot_2 kieu_d _lieu,
.......
)
Vi d
T
o mot bng ten Person co bon cot: LastName, FirstName, Address va Age:
CREATE TABLE Person
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
)
T
o bng va dat kich th.c toi da c
a cac cot:
CREATE TABLE Person
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)
Kieu d lieu se qui d$nh lo
i d lieu nao d.c phep l.u tr trong cot. Sau day la cac kieu d lieu
th.!ng dung nhat trong SQL:
integer(n)
int(n)
smallint(n)
tinyint(n)
Ch l.u tr d lieu la so nguyen. So l.ng toi da cac ch so
d.c qui d$nh bi n.
decimal(n,d)
numeric(n,d)
L.u tr so thap nhan. So l.ng toi da cac ch so d.c qui
d$nh bi n. So l.ng toi da cac ch so sau dau phy thap
phan d.c qui d$nh bi d.
char(n) L.u tr n ky t.
varchar(n) L.u tr toi da n ky t.
date(yyyymmdd) L.u tr ngay thang (d
ng nam-thang-ngay)
To ch mc
Su tam bi: www.daihoc.com.vn
Ch mc d.c t
o ra nham de cac dong trong bng d.c truy xuat nhanh va hieu qu h.n. Ch
mc co the d.c t
o tren mot hoac nhieu cot c
a bng, va moi ch mc d.c dat mot ten. Ng.!i
dung khong thay d.c cac ch mc nay, chung ch d.c dung de tang toc cho CSDL.
L.u y: Sau khi bng da d.c t
o ch mc thi viec cap nhat thay them dong mi vao bng se mat
nhieu th!i gian h.n la doi vi bng khong co ch mc. Ly do la vi khi cap nhat bng, cac ch mc
dong th!i cung phi d.c cap nhat theo. Vi the, ta ch nen t
o ch mc cho cac cot th.!ng xuyen
dung trong cac tac v tim kiem.
Ch mc d.n nhat (Unique Index)
Ch mc d.n nhat se bat buoc hai dong bat ky c
a bng se khong d.c phep mang cung gia tr$
cot d.c t
o ch mc.
Cu phap:
CREATE UNIQUE INDEX ten_ch_mc
ON ten_bng (ten_cot)
Ch mc d.n (Simple Index)
Khi khong dung t khoa UNIQUE trong cau lenh t
o ch mc, cac gia tr$ trung nhau trong cot se
d.c phep.
Cu phap:
CREATE INDEX ten_ch_mc
ON ten_bng (ten_cot)
Vi d
T
o mot ch mc d.n co ten la PersonIndex tren cot LastName c
a bng Person:
CREATE INDEX PersonIndex
ON Person (LastName)
Neu b
n muon t
o ch mc sap xep gim dan, b
n s dng them t danh rieng DESC:
CREATE INDEX PersonIndex
ON Person (LastName DESC)
Neu b
n muon t
o ch mc tren nhieu cot:
CREATE INDEX PersonIndex
ON Person (LastName, Firstname)
Xoa ch mc
Su tam bi: www.daihoc.com.vn
B
n co the xoa ch mc da t
o bang lenh DROP.
DROP INDEX ten_bng.ten_ch_mc
Xoa CSDL hoac bng
De xoa mot CSDL (cac bng trong CSDL cung dong th!i d.c xoa):
DROP DATABASE ten_CSDL
De xoa mot bng (toan bo cau truc, d lieu va ch mc c
a bng se d.c xoa):
DROP TABLE ten_bng
Cau lenh ALTER
Thay doi cau truc bng.
Cau lenh ALTER TABLE d.c s dng de them hoac xoa cot trong mot bng.
ALTER TABLE ten_bng
ADD ten_cot kieu_d _lieu
ALTER TABLE ten_bng
DROP COLUMN ten_cot
L.u y: Mot so he CSDL khong cho phep viec xoa b' cot trong bng.
Vi d: ta co bng Person nh. sau:
LastName FirstName Address
Pettersen Kari Storgt 20
Them mot cot ten la City vao bng Person:
ALTER TABLE Person ADD City varchar(30)
ket qu:
LastName FirstName Address City
Pettersen Kari Storgt 20
Xoa cot Address:
Su tam bi: www.daihoc.com.vn
ALTER TABLE Person DROP COLUMN Address
ket qu:
LastName FirstName City
Pettersen Kari
Su tam bi: www.daihoc.com.vn
Bạn đang đọc truyện trên: Truyen2U.Pro