ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

Phan3

?Download as PPT, PDF?
?
K
khacthuong2008
1 of 38
Downloaded 24 times
www.elarion.com C? S? D? Li?U [email_address] Never stop improving quality Ph?n 3: Ng?n ng? truy v?n SQL
Gi?i thi?u ng?n ng? truy v?n SQL Kh¨¢i ni?m: L ¨¤ m?t lo?i ng?n ng? m¨¢y t¨ªnh ph? bi?n d¨´ng d? t?o, th¨ºm, x¨®a, s?a v¨¤ l?y d? li?u t? m?t h? qu?n tr? c? s? d? li?u . Do IBM nghi¨ºn c?u . Ra ??i v¨¤o n?m 1970. ???c ANSI v¨¤ ISO ti?p t?c ph¨¢t tri?n. C¨¢c phi¨ºn b?n SQL: N?m Phi¨ºn b?n 1986 SQL-86 1992 SQL-92 1999 SQL1999
C¨¢c thao t¨¢c tr¨ºn SQL C¨¢c thao t¨¢c tr¨ºn SQL: M? t? d? li?u. Thao t¨¢c d? li?u. Truy v?n d? li?u.
M? t? d? li?u M? t? d? li?u: L?nh T?o CSDL. L?nh T?o b?ng. L?nh X¨®a b?ng L?nh Th¨ºm c?t L?nh X¨®a c?t L?nh S?a C?t L?nh T?o kh¨®a ch¨ªnh L?nh T?o kh¨®a ngo?i L?nh T?o r¨¤ng bu?c mi?n gi¨¢ tr? L?nh T?o r¨¤ng bu?c duy nh?t L?nh T?o ch? m?c
L?nh t?o CSDL L?nh t?o CSDL: C¨² ph¨¢p : CREATE DATABASE <t¨ºn database> VD: CREATE DATABASE LARION_TDT
L?nh t?o b?ng L?nh t?o b?ng: C¨² ph¨¢p: CREATE TABLE <t¨ºn b?ng> ( <t¨ºn c?t 1><ki?u d? li?u 1> [NOT NULL] ¡­ ) VD: CREATE TABLE SINHVIEN (MSSV char(4) not null, HOLOT varchar(20) not null, TEN varchar(10) not null, PHAI bit, NGAYSINH datetime) SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH
L?nh x¨®a b?ng & l?nh th¨ºm c?t L?nh x¨®a b?ng: C¨² ph¨¢p: DROP TABLE <t¨ºn b?ng>; VD: DROP TABLE SINHVIEN; L?nh th¨ºm c?t: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ADD <t¨ºn c?t> <ki?u d? li?u> [NOT NULL]; VD: ALTER TABLE SINHVIEN ADD DOANVIEN bit; SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH DOANVIEN
L?nh x¨®a c?t L?nh x¨®a c?t: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> DROP COLUMN <t¨ºn c?t>; VD: ALTER TABLE SINHVIEN DROP COLUMN DOANVIEN; SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH
L?nh s?a c?t L?nh s?a c?t: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ALTER COLUMN <t¨ºn c?t> <ki?u d? li?u>; VD: ALTER TABLE SINHVIEN ALTER COLUMN NGAYSINH SmallDateTime ;
L?nh t?o kh¨®a ch¨ªnh L?nh t?o kh¨®a ch¨ªnh C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ADD CONTRAINT <t¨ºn r¨¤ng bu?c> PRIMARY KEY (danh s¨¢ch c?t); L?u ?: T¨ºn r¨¤ng bu?c l¨¤ duy nh?t. C¨¢c c?t trong danh s¨¢ch t¨ºn c?t ph?i c¨® thu?c t¨ªnh NOT NULL. VD: ALTER TABLE SINHVIEN ADD CONTRAINT KC_SV PRIMARY KEY (MSSV); SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH
L?nh t?o kh¨®a ngo?i L?nh t?o kh¨®a ngo?i: C¨² ph¨¢p: ALTER TAB LE <t¨ºn b?ng> ADD CONSTRAIN T <t¨ºn r¨¤ng bu?c> FOREIGN KEY (danh s¨¢ch c?t) REFERENCES <t¨ºn b?ng> (danh s¨¢ch c?t); VD: ALTER TABLE SINHVIEN ADD CONSTRAINT KN_SV FOREIGN KEY (MSSV) REFERENCES DSLOP(MSSV) SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH MALOP DSLOP MALOP TENLOP NIENKHOA
L?nh t?o r¨¤ng bu?c v? mi?n gi¨¢ tr? L?nh t?o r¨¤ng bu?c v? mi?n gi¨¢ tr?: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ADD CONSTRAINT <t¨ºn r¨¤ng bu?c> CHECK (?i?u ki?n); VD: ALTER TABLE SINHVIEN ADD CONSTRAINT KT_NGAYSINH CHECK (YEAR(NGAYSINH) BETWEEN 1980 AND 1990); Khi th¨ºm b?: h? th?ng s? b¨¢o l?i do c¨® r¨¤ng bu?c n?m sinh . SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH 080091T Nguy?n Ti?n Th¨¤nh 1 09/09/1990 0900501 Hu?nh Ph¨²c ?i?n 1 07/07/1991 070021T Nguy?n Ng?c B¨¬nh 0 24/12/1979
L?nh t?o r¨¤ng bu?c duy nh?t L?nh t?o r¨¤ng bu?c duy nh?t: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ADD CONSTRAINT <t¨ºn r¨¤ng bu?c> UNIQUE (danh s¨¢ch t¨ºn c?t); VD: ALTER TABLE SINHVIEN ADD CONSTRAINT DN_NGAYSINH UNIQUE (NGAYSINH); Khi th¨ºm b? H? th?ng s? b¨¢o l?i do ?? c¨® r¨¤ng bu?c ng¨¤y sinh SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH 080091T Nguy?n Ti?n Th¨¤nh 1 09/09/1990 090025T ??ng H?ng H?nh 0 07/07/1991 0900501 Hu?nh Ph¨²c ?i?n 1 07/07/1991
L?nh t?o ch? m?c L?nh t?o ch? m?c: T?o ch? m?c ?? l?u th? t? s?p x?p c¨¢c b?n ghi theo gi¨¢ tr? t?ng d?n c¨¢c c?t N¨ºn t?o ch? m?c cho c¨¢c b?ng c¨® nhi?u b?n ghi v¨¤ ¨ªt ???c c?p nh?t C¨² ph¨¢p: CREATE INDEX <t¨ºn index> ON <t¨ºn b?ng> (<c?t 1,<c?t 2>,..); VD: CREATE INDEX CM_HOLOT ON SINHVIEN (HOLOT); CREATE INDEX CM_TEN ON SINHVIEN (TEN);
L?nh x¨®a ch? m?c L?nh x¨®a ch? m?c: D¨´ng x¨®a m?t ch? m?c ?? t?o tr??c ?¨®. C¨² ph¨¢p: DROP INDEX <t¨ºn index>; VD: DROP INDEX CM_HOLOT; DROP INDEX CM_HOLOT;
Thao t¨¢c v?i d? li?u C¨¢c thao t¨¢c v?i d? li?u: L?nh th¨ºm m?u tin L?nh x¨®a m?u tin L?nh c?p nh?t m?u tin
L?nh th¨ºm m?u tin L?nh th¨ºm m?u tin: C¨² ph¨¢p: INSERT INTO <t¨ºn b?ng>(<t¨ºn c?t 1>,..) VALUES (<bi?u th?c 1>,¡­); N?u c¨¢c bi?u th?c sau VALUES ?¨²ng th? t? trong b?ng th¨¬ c¨¢c c?t sau INTO c¨® th? ???c b? qua. VD: INSERT INTO SINHVIEN (MSSV,HOLOT,TEN,PHAI,NGAYSINH) VALUES (¡¯080099T¡¯,¡¯H? Th¨¢i¡¯,¡¯B?o¡¯,¡¯1¡¯,¡¯06/01/1990¡¯); ho?c INSERT INTO SINHVIEN VALUES (¡¯080099T¡¯,¡¯H? Th¨¢i¡¯,¡¯B?o¡¯,¡¯1¡¯,¡¯06/01/1990¡¯); SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH 080099T H? Th¨¢i B?o 1 06/01/1990
L?nh x¨®a m?u tin (1/2) L?nh x¨®a m?u tin: C¨² ph¨¢p: DELETE FROM <t¨ºn b?ng> WHERE <?i?u ki?n>; Ng? ngh?a: c¨¢c m?u tin th?a m?nh ?? where s? b? x¨®a kh?i b?ng. N?u kh?ng c¨® where th¨¬ t?t c? m?u tin s? b? x¨®a VD: cho Table M?n h?c MONHOC MAMON TENMON KHOAHOC TH101 Tin h?c ??i c??ng 2008 TH308 C?u tr¨²c d? li?u 2009 DA102 ?? ¨¢n 1 2009
L?nh x¨®a m?u tin (2/2) Y¨ºu c?u: x¨®a c¨¢c m?n h?c c¨® KHOAHOC l¨¤ 2008. C?u l?nh: DELETE FROM MONHOC WHERE KHOAHOC=¡®2008¡¯; K?t qu?: MONHOC MAMON TENMON KHOAHOC TH308 C?u tr¨²c d? li?u 2009 DA102 ?? ¨¢n 1 2009 TH101 Tin h?c ??i c??ng 2008
L?nh c?p nh?t m?u tin (1/2) L?nh c?p nh?t m?u tin: C¨² ph¨¢p: UPDATE <t¨ºn b?ng> SET <t¨ºn c?t 1> = <bi?u th?c 1>, ¡­ WHERE <?i?u ki?n>; Gi¨¢ tr? c¨¢c c?t c?a c¨¢c m?u tin th?a ?i?u ki?n WHERE s? ???c thay th? b?ng c¨¢c gi¨¢ tr? <bi?u th?c 1>,¡­ t??ng ?ng. N?u kh?ng c¨® WHERE th¨¬ t?t c? c¨¢c m?u tin c?a b?ng s? ???c s?a ??i. VD: <Xem trang sau>
L?nh c?p nh?t m?u tin (2/2) VD: cho b?ng BANGDIEM m?n Automat nh? sau: Y¨ºu c?u : t?ng cho sinh vi¨ºn m?i ng??i 1 ?i?m C?u l?nh: UPDATE BANGDIEM SET DIEM=DIEM+1; K?t qu?: BANGDIEM MSSV DIEM 070011T 5 080130T 6 081307T 6 BANGDIEM MSSV DIEM 070111T 6 080130T 7 081307T 7
Truy v?n d? li?u Truy v?n d? li?u: C¨² ph¨¢p t?ng qu¨¢t Ph¨¢t bi?u SELECT v?i * Ph¨¢t bi?u SELECT v?i AS Ph¨¢t bi?u SELECT v?i TOP N Ph¨¢t bi?u SELECT v?i DISTINCT M?nh ?? WHERE M?nh ?? ORDER BY M?nh ?? GROUP BY M?nh ?? HAVING Truy v?n t? nhi?u b?ng Truy v?n con
C¨² ph¨¢p t?ng qu¨¢t C¨² ph¨¢p t?ng qu¨¢t: SELECT <danh s¨¢ch c?t> FROM <danh s¨¢ch b?ng> WHERE <?i?u ki?n> GROUP BY <t¨ºn c?t> HAVING <?i?u ki?n cho GROUP BY> ORDER BY <danh s¨¢ch c?t>
Ph¨¢t bi?u SELECT * Ph¨¢t bi?u SELECT v?i * : D?u * d¨´ng ?? ??i di?n cho t?t c? c¨¢c c?t C¨² ph¨¢p: SELECT * FROM <t¨ºn b?ng>; VD: 2 c?u truy v?n sau cho c¨´ng k?t qu?: C?u t?ng qu¨¢t: SELECT MSSV,DIEM FROM BANGDIEM; C?u c¨® d?u * : SELECT * FROM BANGDIEM; BANGDIEM MSSV DIEM 070011T 5 080130T 6
Ph¨¢t bi?u SELECT v?i AS Ph¨¢t bi?u SELECT v?i AS: D¨´ng ??t l?i t¨ºn c?t khi hi?n th? k?t qu? C¨² ph¨¢p: SELECT <t¨ºn c?t> AS <t¨ºn mu?n hi?n th?> VD: Y¨ºu c?u ??i t¨ºn hi?n th? c?a c?t DIEM th¨¤nh DIEMTHI C?u l?nh: SELECT DIEM AS DIEMTHI FROM BANGDIEM BANGDIEM MSSV DIEM 070011T 5 080130T 6 MSSV DIEMTHI 070011T 5 080130T 6
Ph¨¢t bi?u SELECT v?i TOP N Ph¨¢t bi?u SELECT v?i TOP N: Cho ph¨¦p l?y ra N m?u tin t? m?t b?ng. C¨² ph¨¢p: SELECT TOP <N> * FROM <t¨ºn b?ng> VD: Cho b?ng sau, l?y ra 2 m?u tin ??u ti¨ºn. C?u l?nh: SELECT TOP 2 * FROM BANGDIEM BANGDIEM MSSV DIEM 070111T 6 080130T 7 081307T 7 MSSV DIEM 070111T 6 080130T 7
Ph¨¢t bi?u SELECT v?i DISTINCT Ph¨¢t bi?u SELECT v?i DISTINCT: Khi k?t qu? tr? v? c¨® nhi?u m?u tin tr¨´ng nhay, ?? ch? l?y 1 m?u tin ta d¨´ng DISTINCT. C¨² ph¨¢p: SELECT DISTINCT <t¨ºn c?t> FROM <t¨ºn b?ng> VD: Cho b?ng sau cho bi?t c¨® nh?ng l?p n¨¤o C?u l?nh: SELECT DISTINCT MALOP FROM DANHSACHSV K?t qu?: DANHSACHSV MSSV MALOP TENSV 080206K 08KK1D H? Ho¨¤i Anh 080016Q 08QT1D L?u H??ng Giang 080123Q 08QT1D Ho¨¤ng Th¨´y Linh MALOP 08KK1D 08QT1D
M?nh ?? WHERE (1/2) M?nh ?? WHERE: D¨´ng ?? ??t ?i?u ki?n tr¨ªch d? li?u. C¨¢c to¨¢n t? c?a m?nh ?? WHERE: So s¨¢nh: >, <, >=, <=, =, <> Logic: And, Or, Not BETWEEN ¡­ AND ¡­: l?y gi¨¢ trong 1 v¨´ng. LIKE: so s¨¢nh g?n gi?ng IN: ph¨¦p so s¨¢nh trong 1 t?p h?p, danh s¨¢ch. IS NULL (IS NOT NULL) : ki?m tra gi¨¢ tr? c¨® r?ng hay kh?ng EXISTS: tr? v? TRUE n?u c¨® ¨ªt nh?t 1 m?u tin t?n t?i VD: <Xem trang sau>
M?nh ?? WHERE (2/2) VD: Cho b?ng sau, T¨¬m c¨¢c sinh vi¨ºn c¨® ng¨¤y sinh t? 01/06/1990 ??n 31/12/1990 C?u l?nh: SELECT * FROM SINHVIEN WHERE NGAYSINH BETWEEN ¡®01/06/1990 ¡¯ AND ¡¯31/12/1990¡¯; SINHVIEN MSSV HOTEN NGAYSINH QUEQUAN 080777K Nguy?n Qu?c ??t 20/10/1990 ??ng Th¨¢p 080302A Nguy?n Lan Anh 01/01/1990 V?ng T¨¤u 080096T Cao Minh Ti?n 03/10/1990 H? Ch¨ª Minh MSSV HOTEN NGAYSINH QUEQUAN 080777K Nguy?n Qu?c ??t 20/10/1990 ??ng Th¨¢p
M?nh ?? ORDER BY M?nh ?? ORDER BY: S?p x?p k?t qu? theo th? t? mong mu?n C¨² ph¨¢p: ORDER BY <danh s¨¢ch c?t> [ASC|DESC] V?i ASC s?p t?ng d?n ho?c DESC s?p x?p gi?m d?n. VD: Cho b?ng sau: Y¨ºu c?u: Tr¨ªch ra danh s¨¢ch sinh vi¨ºn s?p x?p t?ng d?n C?u l?nh: SELECT * FROM SINHVIEN ORDER BY MSSV ASC MSSV HOTEN NGAYSINH QUEQUAN 080777K Nguy?n Qu?c ??t 20/10/1990 ??ng Th¨¢p 080096T Cao Minh Ti?n 03/10/1990 H? Ch¨ª Minh MSSV HOTEN NGAYSINH QUEQUAN 080096T Cao Minh Ti?n 03/10/1990 H? Ch¨ª Minh 080777K Nguy?n Qu?c ??t 20/10/1990 ??ng Th¨¢p
M?nh ?? GROUP BY (1/2) M?nh ?? GROUP BY: Nh¨®m d? li?u theo t?ng nh¨®m ?? th?c hi?n c¨¢c ph¨¦p to¨¢n th?ng k¨º C¨² ph¨¢p: GROUP BY <danh s¨¢ch t¨ºn c?t> M?t s? h¨¤m th?ng d?ng d¨´ng v?i GROUP BY: AVG: t¨ªnh gi¨¢ tr? trung b¨¬nh. MIN: t¨ªnh gi¨¢ tr? nh? nh?t. MAX: t¨ªnh gi¨¢ tr? l?n nh?t. COUNT: ??m s? ph?n t?. SUM: t¨ªnh t?ng c¨¢c ph?n t? VD: <Xem trang sau>
M?nh ?? GROUP BY (2/2) VD: Cho b?ng sau, cho bi?t s? l??ng sinh vi¨ºn t?ng l?p C?u l?nh: SELECT MALOP, COUNT(*) AS SOLUONG FROM SINHVIEN GROUP BY MALOP K?t qu?: DANHSACHKHENTHUONG MSSV MALOP TENSV PHAI XEPLOAI 080010A 08AV1D B¨´i Long H?i Nam Gi?i 080120Q 08QT2D H? Minh T?m N? Gi?i 080133Q 08QT2D ?o¨¤n Th? Vinh Nam Kh¨¢ 080361T 08TN1D ??ng Long ?? Nam Kh¨¢ MALOP SOLUONG 08AV1D 1 08QT2D 2 08TN1D 1
M?nh ?? HAVING M?nh ?? HAVING: ??t ?i?u ki?n sau khi ?? nh¨®m d? li?u b?ng m?nh ?? GROUP BY VD: tr¨ªch danh s¨¢ch c¨¢c l?p c¨® t? 2 sinh vi¨ºn ???c th??ng C?u l?nh: SELECT MALOP, COUNT(*) AS SOSV FROM SINHVIEN GROUP BY MALOP HAVING COUNT (*)>=2; DANHSACHKHENTHUONG MALOP MSSV TENSV PHAI XEPLOAI 08AV1D 080010A B¨´i Long H?i Nam Gi?i 08QT2D 080120Q H? Minh T?m N? Gi?i 08QT2D 080133Q ?o¨¤n Th? Vinh Nam Kh¨¢ MALOP SOSV 08QT2D 2
Truy v?n t? nhi?u b?ng (1/2) Truy v?n t? nhi?u b?ng: Khi th?ng tin c?n l?y ra c¨® t? nhi?u b?n kh¨¢c nhau c?n th?c hi?n truy v?n t? nhi?u b?ng. N?u c?n k?t n b?ng th¨¬ c?n c¨® n-1 ?i?u ki?n k?t. C¨¢c t¨ºn c?t c¨´ng c¨® ? nhi?u b?ng c?n ghi theo d?ng [T¨ºn b?ng].[T¨ºn c?t] C¨® th? s? d?ng t¨ºn t?t c?a c¨¢c b?n. VD: Tr¨ªch ra MSSV,HOTEN,MALOP,TENLOP c?a c¨¢c sinh vi¨ºn . <Xem ti?p trang sau>
Truy v?n t? nhi?u b?ng (2/2) C?u l?nh: SELECT MSSV,TENSV,MALOP,TENLOP FROM SINHVIEN S , LOP L WHERE S. MALOP = L. MALOP; K?t qu?: SINHVIEN MSSV TENSV PHAI MALOP 080010A B¨´i Long H?i Nam 08AV1D 080120Q H? Minh T?m N? 08QT1D 080133Q ?o¨¤n Th? Vinh Nam 08QT1D LOP MALOP TENLOP 08AV1D Anh V?n 08QT1D Qu?n Tr? 08TH1D Tin H?c 08KK1D K? To¨¢n MSSV TENSV MALOP TENLOP 080010A B¨´i Long H?i 08AV1D Anh V?n 080120Q H? Minh T?m 08QT1D Qu?n Tr? 080133Q ?o¨¤n Th? Vinh 08QT1D Qu?n Tr?
Truy v?n con (1/2) Truy v?n con: ???c s? d?ng khi c?n k?t qu? t? m?t c?u truy v?n kh¨¢c g?i l¨¤ truy v?n con. Khi th?c hi?n truy v?n con s? ???c th?c hi?n tr??c r?i l?y k?t qu? ?? th?c hi?n truy v?n l?n. C¨² ph¨¢p: SELECT <danh s¨¢ch c?t> FROM <danh s¨¢ch b?ng> WHERE <?i?u ki?n> ¡­ <t¨ºn c?t> IN (NOT IN, =, <>,¡­) ( SELECT <danh s¨¢ch c?t> FROM <danh s¨¢ch b?ng> WHERE <?i?u ki?n>);
Truy v?n con (2/2) VD: L?y ra danh s¨¢ch nh?ng sinh vi¨ºn c¨® ?i?m to¨¢n cao nh?t C?u l?nh: SELECT MSSV,TENSV,DIEM FROM DIEMTOAN WHERE DIEM=( SELECT MAX (DIEM) FROM DIEMTOAN); DIEMTOAN MSSV TENSV DIEM 080010A B¨´i Long H?i 9 080120Q H? Minh T?m 10 080133Q ?o¨¤n Th? Vinh 9 MSSV TENSV DIEM 080120Q H? Minh T?m 10
T¨¤i li?u tham kh?o Gi¨¢o tr¨¬nh CSDL , ThS.L¨º Th? Ng?c Th?o - ?H T?n ??c Th?ng.

More Related Content

Phan3

  • 1. www.elarion.com C? S? D? Li?U [email_address] Never stop improving quality Ph?n 3: Ng?n ng? truy v?n SQL
  • 2. Gi?i thi?u ng?n ng? truy v?n SQL Kh¨¢i ni?m: L ¨¤ m?t lo?i ng?n ng? m¨¢y t¨ªnh ph? bi?n d¨´ng d? t?o, th¨ºm, x¨®a, s?a v¨¤ l?y d? li?u t? m?t h? qu?n tr? c? s? d? li?u . Do IBM nghi¨ºn c?u . Ra ??i v¨¤o n?m 1970. ???c ANSI v¨¤ ISO ti?p t?c ph¨¢t tri?n. C¨¢c phi¨ºn b?n SQL: N?m Phi¨ºn b?n 1986 SQL-86 1992 SQL-92 1999 SQL1999
  • 3. C¨¢c thao t¨¢c tr¨ºn SQL C¨¢c thao t¨¢c tr¨ºn SQL: M? t? d? li?u. Thao t¨¢c d? li?u. Truy v?n d? li?u.
  • 4. M? t? d? li?u M? t? d? li?u: L?nh T?o CSDL. L?nh T?o b?ng. L?nh X¨®a b?ng L?nh Th¨ºm c?t L?nh X¨®a c?t L?nh S?a C?t L?nh T?o kh¨®a ch¨ªnh L?nh T?o kh¨®a ngo?i L?nh T?o r¨¤ng bu?c mi?n gi¨¢ tr? L?nh T?o r¨¤ng bu?c duy nh?t L?nh T?o ch? m?c
  • 5. L?nh t?o CSDL L?nh t?o CSDL: C¨² ph¨¢p : CREATE DATABASE <t¨ºn database> VD: CREATE DATABASE LARION_TDT
  • 6. L?nh t?o b?ng L?nh t?o b?ng: C¨² ph¨¢p: CREATE TABLE <t¨ºn b?ng> ( <t¨ºn c?t 1><ki?u d? li?u 1> [NOT NULL] ¡­ ) VD: CREATE TABLE SINHVIEN (MSSV char(4) not null, HOLOT varchar(20) not null, TEN varchar(10) not null, PHAI bit, NGAYSINH datetime) SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH
  • 7. L?nh x¨®a b?ng & l?nh th¨ºm c?t L?nh x¨®a b?ng: C¨² ph¨¢p: DROP TABLE <t¨ºn b?ng>; VD: DROP TABLE SINHVIEN; L?nh th¨ºm c?t: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ADD <t¨ºn c?t> <ki?u d? li?u> [NOT NULL]; VD: ALTER TABLE SINHVIEN ADD DOANVIEN bit; SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH DOANVIEN
  • 8. L?nh x¨®a c?t L?nh x¨®a c?t: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> DROP COLUMN <t¨ºn c?t>; VD: ALTER TABLE SINHVIEN DROP COLUMN DOANVIEN; SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH
  • 9. L?nh s?a c?t L?nh s?a c?t: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ALTER COLUMN <t¨ºn c?t> <ki?u d? li?u>; VD: ALTER TABLE SINHVIEN ALTER COLUMN NGAYSINH SmallDateTime ;
  • 10. L?nh t?o kh¨®a ch¨ªnh L?nh t?o kh¨®a ch¨ªnh C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ADD CONTRAINT <t¨ºn r¨¤ng bu?c> PRIMARY KEY (danh s¨¢ch c?t); L?u ?: T¨ºn r¨¤ng bu?c l¨¤ duy nh?t. C¨¢c c?t trong danh s¨¢ch t¨ºn c?t ph?i c¨® thu?c t¨ªnh NOT NULL. VD: ALTER TABLE SINHVIEN ADD CONTRAINT KC_SV PRIMARY KEY (MSSV); SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH
  • 11. L?nh t?o kh¨®a ngo?i L?nh t?o kh¨®a ngo?i: C¨² ph¨¢p: ALTER TAB LE <t¨ºn b?ng> ADD CONSTRAIN T <t¨ºn r¨¤ng bu?c> FOREIGN KEY (danh s¨¢ch c?t) REFERENCES <t¨ºn b?ng> (danh s¨¢ch c?t); VD: ALTER TABLE SINHVIEN ADD CONSTRAINT KN_SV FOREIGN KEY (MSSV) REFERENCES DSLOP(MSSV) SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH MALOP DSLOP MALOP TENLOP NIENKHOA
  • 12. L?nh t?o r¨¤ng bu?c v? mi?n gi¨¢ tr? L?nh t?o r¨¤ng bu?c v? mi?n gi¨¢ tr?: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ADD CONSTRAINT <t¨ºn r¨¤ng bu?c> CHECK (?i?u ki?n); VD: ALTER TABLE SINHVIEN ADD CONSTRAINT KT_NGAYSINH CHECK (YEAR(NGAYSINH) BETWEEN 1980 AND 1990); Khi th¨ºm b?: h? th?ng s? b¨¢o l?i do c¨® r¨¤ng bu?c n?m sinh . SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH 080091T Nguy?n Ti?n Th¨¤nh 1 09/09/1990 0900501 Hu?nh Ph¨²c ?i?n 1 07/07/1991 070021T Nguy?n Ng?c B¨¬nh 0 24/12/1979
  • 13. L?nh t?o r¨¤ng bu?c duy nh?t L?nh t?o r¨¤ng bu?c duy nh?t: C¨² ph¨¢p: ALTER TABLE <t¨ºn b?ng> ADD CONSTRAINT <t¨ºn r¨¤ng bu?c> UNIQUE (danh s¨¢ch t¨ºn c?t); VD: ALTER TABLE SINHVIEN ADD CONSTRAINT DN_NGAYSINH UNIQUE (NGAYSINH); Khi th¨ºm b? H? th?ng s? b¨¢o l?i do ?? c¨® r¨¤ng bu?c ng¨¤y sinh SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH 080091T Nguy?n Ti?n Th¨¤nh 1 09/09/1990 090025T ??ng H?ng H?nh 0 07/07/1991 0900501 Hu?nh Ph¨²c ?i?n 1 07/07/1991
  • 14. L?nh t?o ch? m?c L?nh t?o ch? m?c: T?o ch? m?c ?? l?u th? t? s?p x?p c¨¢c b?n ghi theo gi¨¢ tr? t?ng d?n c¨¢c c?t N¨ºn t?o ch? m?c cho c¨¢c b?ng c¨® nhi?u b?n ghi v¨¤ ¨ªt ???c c?p nh?t C¨² ph¨¢p: CREATE INDEX <t¨ºn index> ON <t¨ºn b?ng> (<c?t 1,<c?t 2>,..); VD: CREATE INDEX CM_HOLOT ON SINHVIEN (HOLOT); CREATE INDEX CM_TEN ON SINHVIEN (TEN);
  • 15. L?nh x¨®a ch? m?c L?nh x¨®a ch? m?c: D¨´ng x¨®a m?t ch? m?c ?? t?o tr??c ?¨®. C¨² ph¨¢p: DROP INDEX <t¨ºn index>; VD: DROP INDEX CM_HOLOT; DROP INDEX CM_HOLOT;
  • 16. Thao t¨¢c v?i d? li?u C¨¢c thao t¨¢c v?i d? li?u: L?nh th¨ºm m?u tin L?nh x¨®a m?u tin L?nh c?p nh?t m?u tin
  • 17. L?nh th¨ºm m?u tin L?nh th¨ºm m?u tin: C¨² ph¨¢p: INSERT INTO <t¨ºn b?ng>(<t¨ºn c?t 1>,..) VALUES (<bi?u th?c 1>,¡­); N?u c¨¢c bi?u th?c sau VALUES ?¨²ng th? t? trong b?ng th¨¬ c¨¢c c?t sau INTO c¨® th? ???c b? qua. VD: INSERT INTO SINHVIEN (MSSV,HOLOT,TEN,PHAI,NGAYSINH) VALUES (¡¯080099T¡¯,¡¯H? Th¨¢i¡¯,¡¯B?o¡¯,¡¯1¡¯,¡¯06/01/1990¡¯); ho?c INSERT INTO SINHVIEN VALUES (¡¯080099T¡¯,¡¯H? Th¨¢i¡¯,¡¯B?o¡¯,¡¯1¡¯,¡¯06/01/1990¡¯); SINHVIEN MSSV HOLOT TEN PHAI NGAYSINH 080099T H? Th¨¢i B?o 1 06/01/1990
  • 18. L?nh x¨®a m?u tin (1/2) L?nh x¨®a m?u tin: C¨² ph¨¢p: DELETE FROM <t¨ºn b?ng> WHERE <?i?u ki?n>; Ng? ngh?a: c¨¢c m?u tin th?a m?nh ?? where s? b? x¨®a kh?i b?ng. N?u kh?ng c¨® where th¨¬ t?t c? m?u tin s? b? x¨®a VD: cho Table M?n h?c MONHOC MAMON TENMON KHOAHOC TH101 Tin h?c ??i c??ng 2008 TH308 C?u tr¨²c d? li?u 2009 DA102 ?? ¨¢n 1 2009
  • 19. L?nh x¨®a m?u tin (2/2) Y¨ºu c?u: x¨®a c¨¢c m?n h?c c¨® KHOAHOC l¨¤ 2008. C?u l?nh: DELETE FROM MONHOC WHERE KHOAHOC=¡®2008¡¯; K?t qu?: MONHOC MAMON TENMON KHOAHOC TH308 C?u tr¨²c d? li?u 2009 DA102 ?? ¨¢n 1 2009 TH101 Tin h?c ??i c??ng 2008
  • 20. L?nh c?p nh?t m?u tin (1/2) L?nh c?p nh?t m?u tin: C¨² ph¨¢p: UPDATE <t¨ºn b?ng> SET <t¨ºn c?t 1> = <bi?u th?c 1>, ¡­ WHERE <?i?u ki?n>; Gi¨¢ tr? c¨¢c c?t c?a c¨¢c m?u tin th?a ?i?u ki?n WHERE s? ???c thay th? b?ng c¨¢c gi¨¢ tr? <bi?u th?c 1>,¡­ t??ng ?ng. N?u kh?ng c¨® WHERE th¨¬ t?t c? c¨¢c m?u tin c?a b?ng s? ???c s?a ??i. VD: <Xem trang sau>
  • 21. L?nh c?p nh?t m?u tin (2/2) VD: cho b?ng BANGDIEM m?n Automat nh? sau: Y¨ºu c?u : t?ng cho sinh vi¨ºn m?i ng??i 1 ?i?m C?u l?nh: UPDATE BANGDIEM SET DIEM=DIEM+1; K?t qu?: BANGDIEM MSSV DIEM 070011T 5 080130T 6 081307T 6 BANGDIEM MSSV DIEM 070111T 6 080130T 7 081307T 7
  • 22. Truy v?n d? li?u Truy v?n d? li?u: C¨² ph¨¢p t?ng qu¨¢t Ph¨¢t bi?u SELECT v?i * Ph¨¢t bi?u SELECT v?i AS Ph¨¢t bi?u SELECT v?i TOP N Ph¨¢t bi?u SELECT v?i DISTINCT M?nh ?? WHERE M?nh ?? ORDER BY M?nh ?? GROUP BY M?nh ?? HAVING Truy v?n t? nhi?u b?ng Truy v?n con
  • 23. C¨² ph¨¢p t?ng qu¨¢t C¨² ph¨¢p t?ng qu¨¢t: SELECT <danh s¨¢ch c?t> FROM <danh s¨¢ch b?ng> WHERE <?i?u ki?n> GROUP BY <t¨ºn c?t> HAVING <?i?u ki?n cho GROUP BY> ORDER BY <danh s¨¢ch c?t>
  • 24. Ph¨¢t bi?u SELECT * Ph¨¢t bi?u SELECT v?i * : D?u * d¨´ng ?? ??i di?n cho t?t c? c¨¢c c?t C¨² ph¨¢p: SELECT * FROM <t¨ºn b?ng>; VD: 2 c?u truy v?n sau cho c¨´ng k?t qu?: C?u t?ng qu¨¢t: SELECT MSSV,DIEM FROM BANGDIEM; C?u c¨® d?u * : SELECT * FROM BANGDIEM; BANGDIEM MSSV DIEM 070011T 5 080130T 6
  • 25. Ph¨¢t bi?u SELECT v?i AS Ph¨¢t bi?u SELECT v?i AS: D¨´ng ??t l?i t¨ºn c?t khi hi?n th? k?t qu? C¨² ph¨¢p: SELECT <t¨ºn c?t> AS <t¨ºn mu?n hi?n th?> VD: Y¨ºu c?u ??i t¨ºn hi?n th? c?a c?t DIEM th¨¤nh DIEMTHI C?u l?nh: SELECT DIEM AS DIEMTHI FROM BANGDIEM BANGDIEM MSSV DIEM 070011T 5 080130T 6 MSSV DIEMTHI 070011T 5 080130T 6
  • 26. Ph¨¢t bi?u SELECT v?i TOP N Ph¨¢t bi?u SELECT v?i TOP N: Cho ph¨¦p l?y ra N m?u tin t? m?t b?ng. C¨² ph¨¢p: SELECT TOP <N> * FROM <t¨ºn b?ng> VD: Cho b?ng sau, l?y ra 2 m?u tin ??u ti¨ºn. C?u l?nh: SELECT TOP 2 * FROM BANGDIEM BANGDIEM MSSV DIEM 070111T 6 080130T 7 081307T 7 MSSV DIEM 070111T 6 080130T 7
  • 27. Ph¨¢t bi?u SELECT v?i DISTINCT Ph¨¢t bi?u SELECT v?i DISTINCT: Khi k?t qu? tr? v? c¨® nhi?u m?u tin tr¨´ng nhay, ?? ch? l?y 1 m?u tin ta d¨´ng DISTINCT. C¨² ph¨¢p: SELECT DISTINCT <t¨ºn c?t> FROM <t¨ºn b?ng> VD: Cho b?ng sau cho bi?t c¨® nh?ng l?p n¨¤o C?u l?nh: SELECT DISTINCT MALOP FROM DANHSACHSV K?t qu?: DANHSACHSV MSSV MALOP TENSV 080206K 08KK1D H? Ho¨¤i Anh 080016Q 08QT1D L?u H??ng Giang 080123Q 08QT1D Ho¨¤ng Th¨´y Linh MALOP 08KK1D 08QT1D
  • 28. M?nh ?? WHERE (1/2) M?nh ?? WHERE: D¨´ng ?? ??t ?i?u ki?n tr¨ªch d? li?u. C¨¢c to¨¢n t? c?a m?nh ?? WHERE: So s¨¢nh: >, <, >=, <=, =, <> Logic: And, Or, Not BETWEEN ¡­ AND ¡­: l?y gi¨¢ trong 1 v¨´ng. LIKE: so s¨¢nh g?n gi?ng IN: ph¨¦p so s¨¢nh trong 1 t?p h?p, danh s¨¢ch. IS NULL (IS NOT NULL) : ki?m tra gi¨¢ tr? c¨® r?ng hay kh?ng EXISTS: tr? v? TRUE n?u c¨® ¨ªt nh?t 1 m?u tin t?n t?i VD: <Xem trang sau>
  • 29. M?nh ?? WHERE (2/2) VD: Cho b?ng sau, T¨¬m c¨¢c sinh vi¨ºn c¨® ng¨¤y sinh t? 01/06/1990 ??n 31/12/1990 C?u l?nh: SELECT * FROM SINHVIEN WHERE NGAYSINH BETWEEN ¡®01/06/1990 ¡¯ AND ¡¯31/12/1990¡¯; SINHVIEN MSSV HOTEN NGAYSINH QUEQUAN 080777K Nguy?n Qu?c ??t 20/10/1990 ??ng Th¨¢p 080302A Nguy?n Lan Anh 01/01/1990 V?ng T¨¤u 080096T Cao Minh Ti?n 03/10/1990 H? Ch¨ª Minh MSSV HOTEN NGAYSINH QUEQUAN 080777K Nguy?n Qu?c ??t 20/10/1990 ??ng Th¨¢p
  • 30. M?nh ?? ORDER BY M?nh ?? ORDER BY: S?p x?p k?t qu? theo th? t? mong mu?n C¨² ph¨¢p: ORDER BY <danh s¨¢ch c?t> [ASC|DESC] V?i ASC s?p t?ng d?n ho?c DESC s?p x?p gi?m d?n. VD: Cho b?ng sau: Y¨ºu c?u: Tr¨ªch ra danh s¨¢ch sinh vi¨ºn s?p x?p t?ng d?n C?u l?nh: SELECT * FROM SINHVIEN ORDER BY MSSV ASC MSSV HOTEN NGAYSINH QUEQUAN 080777K Nguy?n Qu?c ??t 20/10/1990 ??ng Th¨¢p 080096T Cao Minh Ti?n 03/10/1990 H? Ch¨ª Minh MSSV HOTEN NGAYSINH QUEQUAN 080096T Cao Minh Ti?n 03/10/1990 H? Ch¨ª Minh 080777K Nguy?n Qu?c ??t 20/10/1990 ??ng Th¨¢p
  • 31. M?nh ?? GROUP BY (1/2) M?nh ?? GROUP BY: Nh¨®m d? li?u theo t?ng nh¨®m ?? th?c hi?n c¨¢c ph¨¦p to¨¢n th?ng k¨º C¨² ph¨¢p: GROUP BY <danh s¨¢ch t¨ºn c?t> M?t s? h¨¤m th?ng d?ng d¨´ng v?i GROUP BY: AVG: t¨ªnh gi¨¢ tr? trung b¨¬nh. MIN: t¨ªnh gi¨¢ tr? nh? nh?t. MAX: t¨ªnh gi¨¢ tr? l?n nh?t. COUNT: ??m s? ph?n t?. SUM: t¨ªnh t?ng c¨¢c ph?n t? VD: <Xem trang sau>
  • 32. M?nh ?? GROUP BY (2/2) VD: Cho b?ng sau, cho bi?t s? l??ng sinh vi¨ºn t?ng l?p C?u l?nh: SELECT MALOP, COUNT(*) AS SOLUONG FROM SINHVIEN GROUP BY MALOP K?t qu?: DANHSACHKHENTHUONG MSSV MALOP TENSV PHAI XEPLOAI 080010A 08AV1D B¨´i Long H?i Nam Gi?i 080120Q 08QT2D H? Minh T?m N? Gi?i 080133Q 08QT2D ?o¨¤n Th? Vinh Nam Kh¨¢ 080361T 08TN1D ??ng Long ?? Nam Kh¨¢ MALOP SOLUONG 08AV1D 1 08QT2D 2 08TN1D 1
  • 33. M?nh ?? HAVING M?nh ?? HAVING: ??t ?i?u ki?n sau khi ?? nh¨®m d? li?u b?ng m?nh ?? GROUP BY VD: tr¨ªch danh s¨¢ch c¨¢c l?p c¨® t? 2 sinh vi¨ºn ???c th??ng C?u l?nh: SELECT MALOP, COUNT(*) AS SOSV FROM SINHVIEN GROUP BY MALOP HAVING COUNT (*)>=2; DANHSACHKHENTHUONG MALOP MSSV TENSV PHAI XEPLOAI 08AV1D 080010A B¨´i Long H?i Nam Gi?i 08QT2D 080120Q H? Minh T?m N? Gi?i 08QT2D 080133Q ?o¨¤n Th? Vinh Nam Kh¨¢ MALOP SOSV 08QT2D 2
  • 34. Truy v?n t? nhi?u b?ng (1/2) Truy v?n t? nhi?u b?ng: Khi th?ng tin c?n l?y ra c¨® t? nhi?u b?n kh¨¢c nhau c?n th?c hi?n truy v?n t? nhi?u b?ng. N?u c?n k?t n b?ng th¨¬ c?n c¨® n-1 ?i?u ki?n k?t. C¨¢c t¨ºn c?t c¨´ng c¨® ? nhi?u b?ng c?n ghi theo d?ng [T¨ºn b?ng].[T¨ºn c?t] C¨® th? s? d?ng t¨ºn t?t c?a c¨¢c b?n. VD: Tr¨ªch ra MSSV,HOTEN,MALOP,TENLOP c?a c¨¢c sinh vi¨ºn . <Xem ti?p trang sau>
  • 35. Truy v?n t? nhi?u b?ng (2/2) C?u l?nh: SELECT MSSV,TENSV,MALOP,TENLOP FROM SINHVIEN S , LOP L WHERE S. MALOP = L. MALOP; K?t qu?: SINHVIEN MSSV TENSV PHAI MALOP 080010A B¨´i Long H?i Nam 08AV1D 080120Q H? Minh T?m N? 08QT1D 080133Q ?o¨¤n Th? Vinh Nam 08QT1D LOP MALOP TENLOP 08AV1D Anh V?n 08QT1D Qu?n Tr? 08TH1D Tin H?c 08KK1D K? To¨¢n MSSV TENSV MALOP TENLOP 080010A B¨´i Long H?i 08AV1D Anh V?n 080120Q H? Minh T?m 08QT1D Qu?n Tr? 080133Q ?o¨¤n Th? Vinh 08QT1D Qu?n Tr?
  • 36. Truy v?n con (1/2) Truy v?n con: ???c s? d?ng khi c?n k?t qu? t? m?t c?u truy v?n kh¨¢c g?i l¨¤ truy v?n con. Khi th?c hi?n truy v?n con s? ???c th?c hi?n tr??c r?i l?y k?t qu? ?? th?c hi?n truy v?n l?n. C¨² ph¨¢p: SELECT <danh s¨¢ch c?t> FROM <danh s¨¢ch b?ng> WHERE <?i?u ki?n> ¡­ <t¨ºn c?t> IN (NOT IN, =, <>,¡­) ( SELECT <danh s¨¢ch c?t> FROM <danh s¨¢ch b?ng> WHERE <?i?u ki?n>);
  • 37. Truy v?n con (2/2) VD: L?y ra danh s¨¢ch nh?ng sinh vi¨ºn c¨® ?i?m to¨¢n cao nh?t C?u l?nh: SELECT MSSV,TENSV,DIEM FROM DIEMTOAN WHERE DIEM=( SELECT MAX (DIEM) FROM DIEMTOAN); DIEMTOAN MSSV TENSV DIEM 080010A B¨´i Long H?i 9 080120Q H? Minh T?m 10 080133Q ?o¨¤n Th? Vinh 9 MSSV TENSV DIEM 080120Q H? Minh T?m 10
  • 38. T¨¤i li?u tham kh?o Gi¨¢o tr¨¬nh CSDL , ThS.L¨º Th? Ng?c Th?o - ?H T?n ??c Th?ng.