Bài giảng Các ngôn ngữ thao tác dữ liệu SQL

Ngän ngæî SQL Chæång 3: CAÏC NGÄN NGÆÎ THAO TAÏC DÆÎ LIÃÛU 3.1 Ngän ngæî truy váún dæî liãûu SQL Trong muûc naìy trçnh baìy ngän ngæî dæî liãûu (Structered Query Language), SQL laì ngän ngæî con dæî liãûu âæoüc xaïc nháûn laì ráút maûnh , phäø duûng vaì laûi dãù sæí duûng . SQL laì ngän ngæî phi thuí tuûc, chuáøn mæûc vaì âiãøn hçnh. Do váûy hiãûn nay ráút nhiãöu saín pháøm pháön mãöm âãöu âæåüc caìi âàût SQL nhæ : Oracle, DB2,... Trong pháön naìy seî trçnh baìy caïc khaí nàng cuía ngän ngæî SQL. Pheïp toaïn cå baín trong SQL laì pheïp aïnh xaû âæåüc miãu taí bàòng khäúi lãûnh SELECT - FROM - WHERE. Caïc mãûnh âãö cuía ngän ngæî SQL seî âæåüc trçnh baìy chi tiãút bàòng vê duû. Caïc thuáût ngæî trong Cå Såí Dæî Liãûu quan hãû nhæ quan hãû , thuäüc tênh , bäü,.... âæåüc thay thãú bàòng caïc thuáût ngæî nhæ : Baíng( Table), cäüt(colum), baín ghi(Record) hoàûc haìng(Row) trong SQL. Chuïng ta seî xem caïc chæïc nàng cå baín cuía mäüt hãû quaín trë Cå Såí Dæî Liãûu nhæ cung cáúp ngän ngæî âënh nghéa dæî liãûu (DDM), Ngän ngæî thao taïc dæî liãûu(DML) , tçm kiãúm vaì baío vãû dæî liãûu âæåüc thãø hiãûn trong ngän ngæî SQL nhæ thãú naìo. 1. Âënh nghéa dæî liãûu Trong SQL cho pheïp ngæåìi sæí duûng taûo caïc dæî liãûu nhæ Baíng(table), Khung nhçn(View), vaì caïc táûp chè säú(Index) * Lãûnh taûo baíng Cuï phaïp: CREAT TABLE Ten_Bang(Atr1 Type [NOT NULL], Atr2 Type [NOT NULL],...) Trong âoï Ten_Bang: tãn cuía table taûo ra; tãn baíng laì xáu kyï tæû báút kyì khäng coï kyï hiãûu träúng vaì khäng truìng våïi tæì khoïa Atr1, Atr2 : Tãn caïc thuäüc tênh (Caïc cäüt cuía baíng) Type: Kiãøu dæî liãûu cho thuäüc tênh. Mäüt säú loaûi dæî liãûu thæåìng duìng nhæ: integer: säú nguyãn tæì -2 174 483 648 âãún 2 174 483 648 Smallinteger: säú nguyãn tæì - 32 768 âãún 32 767 decimal(n,p) säú tháûp phán våïi âäü daìi täúi âa n kãø caí p chæî säú tháûp phán ( khäng tênh dáúu cháúm tháûp phán). Tæì khoïa number trong SQL âæoüc duìng trong daûng dæî liãûu naìy. float: säú dáúu pháøy âäüng char(n): xáu kyï tæû coï âäü daìi cäú âënh n, n£ 255 varchar(n) xáu kyï tæû coï âäü daìi biãún âäøi, âäü daìi xáu coï thãø tæì 0 âãún n vaì âæåüc xaïc âënh taûi thåìi âiãøm âæa dæî liãûu vaìo læu træî date: dæî liãûu daûng ngaìy thaïng chè thë NOT NULL âãø baïo ràòng giaï trë cuía thuäüc tênh âoï khäng âæåüc thiãúu trong caïc bäü cuía quan hãû Ngän ngæî SQL Vê Duû: Taûo Cå Såí Dæî Liãûu gäöm 3 baíng S(nhaì cung cáúp SUPPLIERS), P ( Saín pháøm PRODUCTS) vaì baíng SP (Cung cáúp) nhæ sau: - Taûo baíng S: CREATE TABLE S ( S# INTEGER NOT NULL,SNAME VARCHAR(8) NOT NULL,AGES INTEGER NOT NULL, CITY VARCHAR(30) NOT NULL) Chuï yï: NULL laì giaï trë ngáöm âënh - Taûo baíng P:(Baíng saín pháøm) CREATE TABLE P (P# INTEGER NOT NULL, PNAME VARCHAR(10) NOT NULL, WEIGHT INTEGER NOT NULL, COLOR VARCHAR(10) NOT NULL, CITY VARCHAR(30) NOTNULL) - Taûo baíng SP: CREATE TABLE SP (S# INTEGER NOT NULL, P# INTEGER NOT NULL, QTY INTEGER,SDATE DATE NOT NULL, PRICE DECIMAL(8,2)). Vaìo dæî liãûu : Coï ba caïch âãø vaìo dæî liãûu cho baíng nhæ sau INSERT INTO S(S#,SNAME,AGES,CITY) VALUE(1,’Thanh Huong’, 30,’ TP Ho Chi Minh’) Nãúu vë trê cuía caïc cäüt trong baíng cäú âënh, ta coï thãø viãút INSERT INTO S VALUE(1,’Thanh Huong’, 30,’ TP Ho Chi Minh’) Ngæåìi vaìo dæî liãûu coï thãø quãn vë trê caïc cäüt, khi âoï ta coï thãø biãøu diãùn nhæ sau: INSERT INTO S(S#,AGES,CITY, SNAME) VALUE(1, 30, ’ TP Ho Chi Minh’, ’Thanh Huong’) 2. Tçm Kiãúm dæî liãûu Trong SQL Âáy laì chæïc nàng cå baín nháút cuía SQL, cho pheïp ngæåìi sæí duûng tçm kiãúm caïc thäng tin trãn Cå Såí Dæî Liãûu våïi Cáu lãûnh SELECT.....FROM....WHERE Cuï phaïp: SELECT [*| DISTINCT] Danh saïch caïc cäüt âæa ra FROM Tãn_Baíng/Tãn_View [WHERE biãøu thæïc âiãöu kiãûn] [GROUP BY Danh saïch caïc cäüt] [HAVING biãøu thæïc âiãöu kiãûn] [ORDER BY Tãn_cäüt [ASC/DESC] Giaíi thêch caïc tham säú bàòng caïc vê duû Ngän ngæî SQL 2.1 Tçm kiãúm theo cáu hoíi âån giaín Tçm kiãúm khäng âiãöu kiãûn: i. Cho biãút danh saïch caïc nhaì cung cáúp: SELECT * FROM S Dáúu ‘*’ coï nghéa laì choün toaìn bäü caïc cäüt trong baíng. Âãø baíng kãút quaí sau khi thæûc hiãûn mäüt mãûnh âãö SQL âuïng laì mäüt quan hãû (Coï nghéa laì khäng coï caïc bäü truìng nhau) thç phaíi coï tæì khoïa DISTINCT. SELECT DISTINCT PNAME FROM SP Tçm kiãúm våïi âiãöu kiãûn âån giaín Tçm maî säú cuía nhæîng nhaì cung cáúp âaî cung cáúp màût haìng P2 SELECT S# FROM SP WHERE P#=’P2’ Tçm kiãúm coï xæí lyï xáu kyï tæû Xæí lyï xáu kyï tæû gáön âuïng coìn goüi laì pheïp tênh “thäng minh” trong træåìng håüp ngæåìi sæí duûng khäng nhåï roî tãn ngæåìi hoàûc âëa danh ... vê duû laì Hoa hay Hoan khi âoï ta coï thãø viãút SELECT * FROM S WHERE SNAME LIKE ‘HOA%” Trong SQL sæí duûng kyï hiãûu ‘%’ laì thay thãú cho mäüt xáu con báút kyì, dáúu phán caïch ‘_’ âãø thay thãú cho mäüt kyï tæû. A%B : Xáu kyï tæû bàõt âáöu bàòng chæî A vaì kãút thuïc bàòng chæî B %A Xáu kyï tæû báút kyì kãút thuïc bàòng chæî A A_B : Xáu bao gäöm 3 kyï tæû , kyï tæû thæï 2 laì báút kyì A_ : Xáu coï hai kyï tæû, kyï tæû âáöu laì A Xæí lyï ngaìy thaïng Tçm nhæîng màût haìng baïn træåïc ngaìy 94-04-24 10 ngaìy SELECT P# FROM SP WHERE ‘94-04-24’ - SDATE =10 Tçm kiãúm sæí duûng IN vaì BETWEEN Tçm nhæîng màût haìng âaî cung cáúp coï giaï tæì 1000 âãún 2000 SELECT P# FROM SP WHERE PRICE BETWEEN 1000 AND 2000 Tçm maî säú nhæîng nhaì cung cáúp âaî cung cáúp êt nháút mäüt trong caïc màût haìng P1,P2,P3 Ngän ngæî SQL SELECT S# FROM SP WHERE P# IN (‘P1’,’P2’,’P3’) CAÏC HAÌM THÆ VIÃÛN Cuîng giäúng nhæ caïc ngän ngæî Cå Såí Dæî Liãûu khaïc, Trong SQL coï caïc haìm máùu gäöm COUNT, MAX, MIN, SUM, AVG. Riãng haìm COUNT khi coï âäúi säú laì ‘*’ coï nghéa laì âãúm säú baíng ghi thoía maîn yãu cáöu tçm kiãúm maì khäng quan tám âãún báút kyì cäüt naìo. Vê du Cho biãút säú láön màût haìng P2 âaî âæåüc cung cáúp SELECT COUNT(*) FROM SP WHERE P#=’P2’ Tçm hiãûu säú màût haìng P1 baïn mäüt láön nhiãöu nháút vaì mäüt láön êt nháút cuía haîng S1 SELECT MAX(QTY) - MIN(QTY) FROM SP WHERE S#=’S1’ AND P#=’P1’ Tçm kiãúm nhåì mãûnh âãö GROUPE BY Tçm maî säú nhæîng màût haìng maì mäùi nhaì cung cáúp âaî cung cáúp cho khaïch haìng. SELECT S#,P# FROM SP GROUPE BY S# trong mãûnh âãö naìy baíng dæî liãûu SP âæåüc láúy ra, sau âoï phán thaình nhoïm theo maî säú cuía ngæåìi cung cáúp (S#). Coï nghéa laì caïc bäü coï cuìng giaï trë S# seî âæåüc xãúp liãn tiãúp nhau, hãút nhoïm naìy âãún nhoïm khaïc. Tçm kiãúm nhåì mãûnh âãö HAVING Mãûnh âãö HAVING thæåìng âæåüc sæí duûng cuìng mãûnh âãö GROUP BY. Sau HAVING la biãøu thæïc âiãöu kiãûn. Biãøu thæïc âiãöu kiãûn naìy khäng taïc âäüng vaìo toaìn baíng âæåüc chè ra åí mãûnh âãö FROM maì chè taïc âäünáönnnn læåüt âãún tæìng nhoïm caïc baín ghi âaî chè ra taûi mãûnh âãö GOUP BY. Vê duû: Tçm maî säú nhæîng nhaì cung cáúp âaî cung cáúp êt nháút hån hai màût haìng SELECT S# FROM SP GROUP BY S# HAVING CUONT (DISTINCT P#)>2 Tçm kiãúm coï sàõp xãúp Tçm tãn caïc màût haìng maìu âoí vaì sàõp xãúp theo thæï tæû cuía maî säú màût haìng SELECT PNAME,P# Ngän ngæî SQL FROM P WHERE COLOUR = ‘RED’ ORDER BY P# ASC Mãûnh âãö ORDER BY cho pheïp ta sàõp xãúp caïc cäüt theo thæï tæû tàng dáön (ASC) hoàûc giaím dáön (DESC), ngáöm âënh laì ASC Mãûnh âãö ORDER BY coï thãø sàõp xãúp theo nhiãöu cäüt. Nãúu noï âæïng sau mãûnh âãö GROUP BY thç miãön taïc âäüng laì tæìng nhoïm cuía cäüt âæåüc chè ra trong mãûnh âãö GROUP BY. Nãúu cäüt sàõp xãúp coï màût åí mãûnh âãö SELECT thç trong mãûnh âãö ORDER BY coï thãø chè cáön chè ra säú thæï tæû cuía cäüt âoï trong danh saïch tham chiãúu laì âuí. trong vê duû trãn ta coï thãø viãút SELECT PNAME,P# FROM P WHERE COLOUR = ‘RED’ ORDER BY 2 ASC Tçm kiãúm våïi cáu hoíi phæïc taûp Trong pháön naìy seî trçnh baìy viãûc tçm kiãúm dæî liãûu trãn nhiãöu baíng thäng qua pheïp kãút näúi hoàûc viãûc sæí duûng caïc aïnh xaû läöng. Khaí nàng kãút näúi dæî liãûu Trong pheïp kãút näúi, caïc cäüt tham gia kãút näúi phaíi coï miãön trë laì so saïnh âæåüc våïi nhau. Tãn cäüt cuía caïc baíng khaïc nhau coï thãø viãút tæåìng minh qua tãn baíng. Vê Duû: Våïi mäùi màût haìng âæåüc cung cáúp , haîy cho biãút maî säú cuía màût haìng vaì âëa chè cuía haîng âaî cung cáúp màût haìng âoï. SELECT DISTINCT P#, CITY FROM SP, S WHERE SP.S# =S.S# Aïnh xaû läöng Ta coï thãø sæí duûng nhæîng mãûnh âãö SELECT läöng nhau Vê duû: Tçm tãn nhæîng haîng âaî cung cáúp màût haìng P2 SELECT SNAME FROM S WHERE S# IN( SELECT S# FROM SP WHERE P#=’P2’) Ngän ngæî SQL Pheïp läöng nhau coï thãø läöng nhiãöu mæïc hoàûc sæí duûng sæû dáùn troí giæîa caïc khäúi mäùi khi hæåïng tåïi mäüt baíng khaïc nhau Vê duû: Tçm tãn caïc haîng khäng cung æïng màût haìng P1 SELECT SNAME FROM S WHERE ‘P1’ NOT IN SELECT P# FROM SP WHERE S#= S.S# Trong SQL coìn cho pheïp tçm kiãúm trãn nhiãöu baíng nhåì mãûnh âeì EXISTS (täön taûi) Vê duû: Tçm kiãúm nhæîng nhaì cung cáúp âaî cung cáúp êt nháút mäüt màût haìng naìo âoï SELECT * FROM S WHERE EXISTS (SELECT * FROM SP WHERE SP.S#=S.S#) Hoàûc coï thãø biãøu diãùn bàòng caïch khaïc SELECT * FROM S WHERE 0< (SELECT COUNT(*) FROM SP WHERE SP.S#=S.S#) Tçm kiãúm coï sæí duûng læåüng tæì ANY vaì ALL Tçm tãn nhæîng màût haìng coï maî säú màût haìng laì màût haìng naìo âoï maì haîng S1 âaî cung cáúp SELECT PNAME FROM P WHERE P#= ANY SELECT P# FROM SP WHERE S#=’S1’ Tçm tãn nhæîng nhaì cung cáúp cung cáúp êt nháút mäüt màût haìng maìu âoí SELECT SNAME Ngän ngæî SQL FROM S WHERE S#= ANY SELECT S# FROM SP,S,P WHERE S.S#=SP.S# AND P.P#=SP.P# AND COLOUR=’RED’ Tçm maî säú nhæîng nhaì cung cáúp säú læåüng mäyt láön mäüt màût haìng naìo âoï låïn hån hoàûc bàòng säú læåüng mäùi láön cung æïng cuía haîng SELECT S# FROM SP WHERE QTY >= ALL SELECT QTY FROM SP Mãûnh âãö trãn hoaìn toaìn tæång âæång våïi SELECT S# FROM SP WHERE QTY = (SELECT MAX( QTY) FROM SP) Tçm nhæîng haîng chæa hoaût âäüng SELECT * FROM S WHERE NOT EXISTS SELECT * FROM SP WHERE SP.S# = S.S# Chuï yï mãûnh âãö NOT EXISTS coï giaï trë âuïng (True) nãúu kãút quaí cuía cáu hoíi sau noï laì mäüt táûp räùng. Tçm kiãúm coï chæïa pheïp tênh táûp håüp Tçm maî säú nhæîng haîng hiãûn thåìi chæa cung cáúp mäüt màût haìng naìo caí SELECT S# FROM S MINUS SELECT S# FROM SP Tçm tãn caïc haîng cung cáúp, cung cáúp táút caí caïc màût haìng SELECT SNAME FROM S WHERE ( SELECT P# Ngän ngæî SQL FRPM SP WHERE S#=S.S#) = (SELECT P# FRPM P) 3. Caïc lãûnh cáûp nháût dæî liãûu Thãm mäüt bäü vaìo trong quan hãû Cuï phaïp: INSERT INTO Tãn_Baíng(Danh saïch tãn cäüt) VALUES(Danh saïch caïc trë) [cáu hoíi con] Vê duû: coï thãø bäø sung mäüt táûp caïc baín ghi laì kãút quaí xæí lyï cuía mäüt cáu hoíi naìo âoï, chàón haûn: Giaí sæí ta coï baíng W coï cuìng læåüc âäö nhæ baíng P. khi âoï mãûnh âãö sau âáy seî bäø sung vaìo baíng P táút caí næîng bäü tæì baíng W nhæîng màût haìng coï máöu âoí INSERT INTO P SELECT * FROM W WHERE COLOUR =’RED’ + Xoïa caïc bäü Cuï phaïp DELETE FROM tãn_baíng [WHERE btâk] Vê duû: Loaûi boí haîng S1 ra khoíi baíng S DELETE FROM S WHERE S#=’S1’ Loüai boí caïcmàût haìng âæåüc cung cáúp sau ngaìy 20 -5- 1994 DELETE FROM SP WHERE SDATE >’1994-5-20’ Loaûi boí caïc haîng chæa cung cáúp màût haìng naìo DELETE FROM S WHERE S# NOT IN (SELECT S# FROM SP) + Sæía âäøi dæî liãûu Ngän ngæî SQL Cuï phaïp: UPDATE [Tãn_baíng] SET [Tãn_cäüt=Biãøu thæïc,...] [FROM Tãn_Baíng] [WHERE btâk] Vê duû: Âäøi maìu caïc màût haìng P2 thaình maìu vaìng UPDATE P SET COLOUR =’ YELLOW’ WHERE P#= ‘ P2’ + Thãm mäüt cäüt vaìo baíng Cuï phaïp: ALTER TABLE Tãn_Baíng [ADD/MODIFY][(Tãn_cäüt Kiãøu dæî liãûu ] ADD: Thãm cäüt måïi vaìo MODIFY : Sæía cäüt daî coï trong quan hãû Vê duû: thãm cäüt DONGIA ( âån giaï) cho baíng SP våïi kiãøu dæî liãûu daûng säú tháûp phán ALTER TABLE SP ADD DONGIA DECIMAL(8.2) + Xoïa baíng: cuï phaïp: DROP TABLE Tãn_baíng Xoïa VIEW Cuï phaïp DROP VIEW Tãn_view Taûo tãûp chè säú Trong SQL khäng coï cå chãú tæû âäüng taûo tãûp chè säú cho caïc cäüt cuía baíng. Viãûc taûo tãûp chè säú do ngæåìi duìng thuûc hiãûn. Cuï phaïp nhæ sau: CREATE [UNIQUE] INDEX tãn_tãûp_chè_säú ON tãn_baíng (tãn_cäüt [ASC|DESC]) Huíy boí chè säú thç sæí duûng mãûnh âãö DROP INDEX tãn_tãûp_chè_säú Vê duû:taûo tãûp chè säú 13 theo cäüt City tàng dáön cuía baíng S CREATE INDEX 13 ON S(CITY ASC) Trong SQL coï thãø täø chæïc âa chè säú, tæïc laì täø chæïc mäüt tãûp chè säú cho nhiãöu cäüt, mäùi cäüt coï thãø coï chiãöu tàng giaím khaïc nhau, thæï tæû âæåüc tênh tæì traïi qua phaíi Vê duû taûo tãûp chè säú 14 cho baíng SP theo cäüt S# tàng dáön vaì theo cäüt P# giaím dáön CREATE INDEX 14 ON SP(S# ASC, P# DESC) 4. Caïc mãûnh âãö an toaìn dæî liãûu Âáy laì yãúu täú khäng thãø thiãúu âæåüc âäúi våïi mäüt hãû quaín trë Cå Såí Dæî Liãûu âãø baío vãû dæî liãûu vaì ngàn chàûn nhæîng ngæåìi khäng âæåüc pheïp truy cáûp. Caïc quyãön truy cáûp vaìo Cå Såí Ngän ngæî SQL Dæî Liãûu gäöm: Read(âoüc), Write(ghi), Insert(bäø sung), Update(Sæía âäøi),Select(choün),Delete(xoïa)Run(chaûy). Viãûc phán phaït quyãön truy cáûp vaìo Cå Såí Dæî Liãûu do ngæåìi quaín trë Cå Såí Dæî Liãûu do ngæåìi quaín trë Cå Såí Dæî Liãûu (database administrator -DBA) thæûc hiãûn båíi lãûnh sau: Giaïn quyãön truy cáûp cho ngæåìi sæí duûng: Lãûnh GRANT cuï phaïp: GRANT Quyãön truy cáûp ON Tãn_baíng/view TO Tãn_User [ with GRANT option] Tæì khoïa with GRANT option âãø cho ngæåìi âæåüc cáúp quyãön coï thãø tiãúp tuûc trao quyãön cho ngæåìi khaïc. Nãúu tãn_User laì Public hoàûc Word thç coï nghéa laì cho moüi ngæåìi sæí duûng. Vê duû: trao quyãön sæí duûng baíng S cho cä Häöng GRANT READ ON S TO HONG with GRANT option Lãûnh naìy cä Häöng coï thãø trao quyãön âoüc baíng S cho ngæåìi duìng khaïc Huíy boí quyãön truy cáûp: REVOKE Tãn_quyãön ON tãn_baíng FROM Tãn_User vê duû:Ruït laûi quyãön âoüc baíng S cuía User Häöng REVOKE READ ON S FROM HONG