Hocsql

HOCSQL
Download Document
Showing page : 1 of 61
This preview has blurred sections. Sign up to view the full version! View Full Document
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: HC SQL SERVER 2000 1 MC LC Overview of SQL Server 2000 ........................................................................................... 2 Transact SQL ...................................................................................................................... 7 Backup And Restore SQL Server ..................................................................................... 24 Stored Procedure and Advanced T-SQL........................................................................... 41 Triggers And Views.......................................................................................................... 51 http://ebooks.vdcmedia.com HC SQL SERVER 2000 2 Overview of SQL Server 2000 Ð đc và hiu bài vit này bn phi có kin thc căn bn v SQL và Access Database Gii Thiu SQL Server 2000 SQL Server 2000 là mt h thng qun lý cơ s d liu (Relational Database Management System (RDBMS) ) s dng Transact-SQL đ trao đi d liu gia Client computer và SQL Server computer. Mt RDBMS bao gm databases, database engine và các ng dng dùng đ qun lý d liu và các b phn khác nhau trong RDBMS. SQL Server 2000 đưc ti ưu đ có th chy trên môi trưng cơ s d liu rt ln (Very Large Database Environment) lên đn Tera-Byte và có th phc v cùng lúc cho hàng ngàn user. SQL Server 2000 có th kt hp "ăn ý" vi các server khác như Microsoft Internet Information Server (IIS), E-Commerce Server, Proxy Server.... SQL Server có 7 editions: Enterprise : Cha đy đ các đc trưng ca SQL Server và có th chy tt trên h thng lên đn 32 CPUs và 64 GB RAM. Thêm vào đó nó có các dch v giúp cho vic phân tích d liu rt hiu qu (Analysis Services) Standard : Rt thích hp cho các công ty va và nh vì giá thành r hơn nhiu so vi Enterprise Edition, nhưng li b gii hn mt s chc năng cao cp (advanced features) khác, edition này có th chy tt trên h thng lên đn 4 CPU và 2 GB RAM. Personal: đưc ti ưu hóa đ chy trên PC nên có th cài đt trên hu ht các phiên bn windows k c Windows 98. Developer : Có đy đ các tính năng ca Enterprise Edition nhưng đưc ch to đc bit như gii hn s lưng ngưi kt ni vào Server cùng mt lúc.... Ðây là edition mà các bn mun hc SQL Server cn có. Chúng ta s dùng edition này trong sut khóa hc. Edition này có th cài trên Windows 2000 Professional hay Win NT Workstation. Desktop Engine (MSDE): Ðây ch là mt engine chy trên desktop và không có user interface (giao din). Thích hp cho vic trin khai ng dng máy client. Kích thưc database b gii hn khong 2 GB. http://ebooks.vdcmedia.com HC SQL SERVER 2000 Win CE : Dùng cho các ng dng chy trên Windows CE 3 Trial: Có các tính năng ca Enterprise Edition, download free, nhưng gii hn thi gian s dng. Cài Ðt SQL Server 2000 (Installation) Các bn cn có Developer Edition và ít nht là 64 MB RAM, 500 MB hard disk đ có th install SQL Server. Bn có th install trên Windows Server hay Windows XP Professional, Windows 2000 Professional hay NT Workstation nhưng không th install trên Win 98 family. Vì mt trong nhng đc đim ca các sn phm Microsoft là d install nên chúng tôi không trình bày chi tit v cách install hay các bưc install mà ch trình bày các đim cn lưu ý khi install mà thôi. Nu các bn gp tr ngi trong vic install thì có th đưa lên forum đ hi thêm. Khi install bn cn lưu ý các đim sau: màn hình th hai bn chn Install Database Server. Sau khi install xong SQL Server bn có th install thêm Analysis Service nu bn thích. màn hình Installation Definition bn chn Server and Client Tools. Sau đó bn nên chn kiu Custom và chn tt c các b phn ca SQL Server. Ngoài ra nên chn các giá tr mc đnh (default) màn hình Authentication Mode nh chn Mixed Mode . Lưu ý vì SQL Server có th dùng chung ch đ bo mt (security) vi Win NT và cũng có th dùng ch đ bo mt riêng ca nó. Trong Production Server ngưi ta thưng dùng Windows Authetication vì đ an toàn cao hơn và d dàng cho ngưi qun lý mng và c cho ngưi s dng. Nghĩa là mt khi bn đưc chp nhn (authenticated) kt ni vào domain thì bn có quyn truy cp d liu (access data) trong SQL Server. Tuy nhiên ta nên chn Mixed Mode đ d dàng cho vic hc tp. Sau khi install bn s thy mt icon nm góc phi bên dưi màn hình, đây chính là Service Manager. Bn có th Start, Stop các SQL Server services d dàng bng cách double-click vào icon này. Mt chút kin thc v các Version ca SQL Server SQL Server ca Microsoft đưc th trưng chp nhn rng rãi k t version 6.5. Sau đó Microsoft đã ci tin và hu như vit li mt engine mi cho SQL Server 7.0. Cho nên có th nói t version 6.5 lên version 7.0 là mt bưc nhy vt. Có mt s đc tính ca SQL Server 7.0 không tương thích vi version 6.5. Trong khi http://ebooks.vdcmedia.com HC SQL SERVER 2000 4 đó t Version 7.0 lên version 8.0 (SQL Server 2000) thì nhng ci tin ch yu là m rng các tính năng v web và làm cho SQL Server 2000 đáng tin cy hơn. Mt đim đc bit đáng lưu ý version 2000 là Multiple-Instance. Nói cho d hiu là bn có th install version 2000 chung vi các version trưc mà không cn phi uninstall chúng. Nghĩa là bn có th chy song song version 6.5 hoc 7.0 vi version 2000 trên cùng mt máy (điu này không th xy ra vi các version trưc đây). Khi đó version cũ trên máy bn là Default Instance còn version 2000 mi va install s là Named Instance. Các thành phn quan trng trong SQL Server 2000 SQL Server 2000 đưc cu to bi nhiu thành phn như Relational Database Engine, Analysis Service và English Query.... Các thành phn này khi phi hp vi nhau to thành mt gii pháp hoàn chnh giúp cho vic lưu tr và phân tích d liu mt cách d dàng. Relational Database Engine - Cái lõi ca SQL Server: Ðây là mt engine có kh năng cha data các quy mô khác nhau dưi dng table và support tt c các kiu kt ni (data connection) thông dng ca Microsoft như ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC). Ngoài ra nó còn có kh năng t điu chnh (tune up) ví d http://ebooks.vdcmedia.com HC SQL SERVER 2000 5 như s dng thêm các tài nguyên (resource) ca máy khi cn và tr li tài nguyên cho h điu hành khi mt user log off. Replication - Cơ ch to bn sao (Replica): Gi s bn có mt database dùng đ cha d liu đưc các ng dng thưng xuyên cp nht. Mt ngày đp tri bn mun có mt cái database ging y ht như th trên mt server khác đ chy báo cáo (report database) (cách làm này thưng dùng đ tránh nh hưng đn performance ca server chính). Vn đ là report server ca bn cũng cn phi đưc cp nht thưng xuyên đ đm bo tính chính xác ca các báo cáo. Bn không th dùng cơ ch back up and restore trong trưng hp này. Th thì bn phi làm sao? Lúc đó cơ ch replication ca SQL Server s đưc s dng đ bo đm cho d liu 2 database đưc đng b (synchronized). Replication s đưc bàn k trong bài 12 Data Transformation Service (DTS) - Mt dch v chuyn dch data vô cùng hiu qu Nu bn làm vic trong mt công ty ln trong đó data đưc cha trong nhiu nơi khác nhau và các dng khác nhau c th như cha trong Oracle, DB2 (ca IBM), SQL Server, Microsoft Access....Bn chc chn s có nhu cu di chuyn data gia các server này (migrate hay transfer) và không ch di chuyn bn còn mun đnh dng (format) nó trưc khi lưu vào database khác, khi đó bn s thy DTS giúp bn gii quyt công vic trên d dàng như th nào. DTS s đưc bàn k trong bài 8. Analysis Service - Mt dch v phân tích d liu rt hay ca Microsoft D liu (Data) cha trong database s chng có ý nghĩa gì nhiu nu như bn không th ly đưc nhng thông tin (Information) b ích t đó. Do đó Microsoft cung cp cho bn mt công c rt mnh giúp cho vic phân tích d liu tr nên d dàng và hiu qu bng cách dùng khái nim hình khi nhiu chiu (multidimension cubes) và k thut "đào m d liu" (data mining) s đưc chúng tôi gii thiu trong bài 13. English Query - Mt dch v mà ngưi Vit Nam chc là ít mun dùng :-) (?) Ðây là mt dch v giúp cho vic query data bng ting Anh "trơn" (plain English). Meta Data Service: Dch v này giúp cho vic cha đng và "xào nu" Meta data d dàng hơn. Th thì Meta Data là cái gì vy? Meta data là nhng thông tin mô t v cu trúc ca data trong database như data thuc loi nào String hay Integer..., mt ct nào http://ebooks.vdcmedia.com HC SQL SERVER 2000 6 đó có phi là Primary key hay không....Bi vì nhng thông tin này cũng đưc cha trong database nên cũng là mt dng data nhưng đ phân bit vi data "chính thng" ngưi ta gi nó là Meta Data. Phn này chc là bn phi xem thêm trong mt thành phn khác ca SQL Server sp gii thiu sau đây là SQL Server Books Online vì không có bài nào trong lot bài này nói rõ v dch v này c. SQL Server Books Online - Quyn Kinh Thánh không th thiu: Cho dù bn có đc các sách khác nhau dy v SQL server thì bn cũng s thy books online này rt hu dng và không th thiu đưc( cho nên Microsoft mi hào phóng đính kèm theo SQL Server). SQL Server Tools - Ðây là mt b đ ngh ca ngưi qun tr cơ s d liu (DBA ) Ái chà nu k chi tit ra thì hơi nhiu đy cho nên bn cn đc thêm trong books online. đây ngưi vit ch k ra mt vài công c thông dng mà thôi. Ðu tiên phi k đn Enterprise Manager. Ðây là mt công c cho ta thy toàn cnh h thng cơ s d liu mt cách rt trc quan. Nó rt hu ích đc bit cho ngưi mi hc và không thông tho lm v SQL. K đn là Query Analyzer. Ði vi mt DBA gii thì hu như ch cn công c này là có th qun lý c mt h thng database mà không cn đn nhng th khác. Ðây là mt môi trưng làm vic khá tt vì ta có th đánh bt k câu lnh SQL nào và chy ngay lp tc đc bit là nó giúp cho ta debug my cái stored procedure d dàng. Công c th ba cn phi k đn là SQL Profiler. Nó có kh năng "chp" (capture) tt c các s kin hay hot đng din ra trên mt SQL server và lưu li dưi dng text file rt hu dng trong vic kim soát hot đng ca SQL Server. Ngoài mt s công c trc quan như trên chúng ta cũng thưng hay dùng osql và bcp (bulk copy) trong command prompt. Tóm li trong bài này chúng ta đã do qua mt vòng đ tìm hiu v SQL Server. Trong bài sau chúng ta cũng s tip tc do chơi thêm mt chút vi TransactSQL trưc khi đi sâu vào các đ tài khác. http://ebooks.vdcmedia.com HC SQL SERVER 2000 7 Transact SQL Gii Thiu Sơ Lưc V Transact SQL (T-SQL) Transact-SQL là ngôn ng SQL m rng da trên SQL chun ca ISO (International Organization for Standardization) và ANSI (American National Standards Institute) đưc s dng trong SQL Server khác vi P-SQL (ProceduralSQL) dùng trong Oracle. Trong bài này chúng ta s tìm hiu sơ qua v T-SQL. Chúng đưc chia làm 3 nhóm: Data Definition Language (DDL): Ðây là nhng lnh dùng đ qun lý các thuc tính ca mt database như đnh nghĩa các hàng hoc ct ca mt table, hay v trí data file ca mt database...thưng có dng Create object_Name Alter object_Name Drop object_Name Trong đó object_Name có th là mt table, view, stored procedure, indexes... Ví d: Lnh Create sau s to ra mt CompanyID,CompanyName,Contact table tên Importers vi 3 ct USE Northwind CREATE TABLE Importers( CompanyID int NOT NULL, CompanyName varchar(40) NOT NULL, Contact varchar(40) NOT NULL ) Lnh Alter sau đây cho phép ta thay đi đnh nghĩa ca mt table như thêm(hay bt) mt ct hay mt Constraint...Trong ví d này ta s thêm ct ContactTitle vào table Importers USE Northwind http://ebooks.vdcmedia.com HC SQL SERVER 2000 8 ALTER TABLE Importers ADD ContactTitle varchar(20) NULL Lnh Drop sau đây s hoàn toàn xóa table khi database nghĩa là c đnh nghĩa ca table và data bên trong table đu bin mt (khác vi lnh Delete ch xóa data nhưng table vn tn ti). USE Northwind DROP TABLE Importers Data Control Language (DCL): Ðây là nhng lnh qun lý các quyn truy cp lên tng object (table, view, stored procedure...). Thưng có dng sau: Grant Revoke Deny Ví d: Lnh sau s cho phép user trong Public Role đưc quyn Select đi vi table Customer trong database Northwind (Role là mt khái nim ging như Windows Group s đưc bàn k trong phn Security) USE Northwind GRANT SELECT ON Customers TO PUBLIC Lnh sau s t chi quyn Select đi vi table Customer trong database Northwind ca các user trong Public Role USE Northwind DENY SELECT ON Customers TO PUBLIC Lnh sau s xóa b tác dng ca các quyn đưc cho phép hay t chi trưc đó USE Northwind REVOKE SELECT ON Customers TO PUBLIC http://ebooks.vdcmedia.com HC SQL SERVER 2000 9 Data Manipulation Language (DML): Ðây là nhng lnh ph bin dùng đ x lý data như Select, Update, Insert, Delete Ví d: Select USE Northwind SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE (CustomerID = 'alfki' OR CustomerID = 'anatr') ORDER BY ContactName Insert USE Northwind INSERT INTO Territories VALUES (98101, 'Seattle', 2) Update USE Northwind UPDATE Territories SET TerritoryDescription = 'Downtown Seattle' WHERE TerritoryID = 98101 Delete USE Northwind DELETE FROM Territories WHERE TerritoryID = 98101 Vì phn này khá căn bn nên chúng tôi thit nghĩ không cn gii thích nhiu. Chú ý trong lnh Delete bn có th có ch From hay không đu đưc. Nhưng mà chúng ta s chy th các ví d trên đâu? Ð chy các câu lnh thí d trên bn cn s dng và làm quen vi Query Analyser http://ebooks.vdcmedia.com HC SQL SERVER 2000 10 Cú Pháp Ca T-SQL: Phn này chúng ta s bàn v các thành phn to nên cú pháp ca T-SQL Identifiers Ðây chính là tên ca các database object. Nó dùng đ xác đnh mt object. (Chú ý khi nói đn Object trong SQL Server là chúng ta mun đ cp đn table, view, stored procedure, index.....Vì hu như mi th trong SQL Server đu đưc thit k theo kiu hưng đi tưng (object-oriented)). Trong ví d sau TableX, KeyCol, Description là nhng identifiers CREATE TABLE TableX (KeyCol INT PRIMARY KEY, Description NVARCHAR(80)) Có hai loi Identifiers mt loi thông thưng (Regular Identifier) và mt loi gi là Delimited Identifier, loi này cn có du "" hay du đ ngăn cách. Loi Delimited đưc dùng đi vi các ch trùng vi t khóa ca SQL Server (reserved keyword) hay các ch có khong trng. http://ebooks.vdcmedia.com HC SQL SERVER 2000 11 Ví d: SELECT * FROM [My Table] WHERE [Order] = 10 Trong ví d trên ch Order trùng vi keyword Order nên cn đt trong du ngoc vuông . Variables (Bin) Bin trong T-SQL cũng có chc năng tương t như trong các ngôn ng lp trình khác nghĩa là cn khai báo trưc loi d liu trưc khi s dng. Bin đưc bt đu bng du @ (Ði vi các global variable thì có hai du @@) Ví d: USE Northwind DECLARE @EmpIDVar INT SET @EmpIDVar = 3 SELECT * FROM Employees WHERE EmployeeID = @EmpIDVar + 1 Functions (Hàm) Có 2 loi hàm mt loi là built-in và mt loi user-defined Các hàm Built-In đưc chia làm 3 nhóm: Rowset Functions : Loi này thưng tr v mt object và đưc đi x như mt table. Ví d như hàm OPENQUERY s tr v mt recordset và có th đng v trí ca mt table trong câu lnh Select. Aggregate Functions : Loi này làm vic trên mt s giá tr và tr v mt giá tr đơn hay là các giá tr tng. Ví d như hàm AVG s tr v giá tr trung bình ca mt ct. Scalar Functions : Loi này làm vic trên mt giá tr đơn và tr v mt giá tr đơn. Trong loi này li chia làm nhiu loi nh như các hàm v toán hc, v thi gian, x lý kiu d liu String....Ví d như hàm MONTH('2002-09-30') s tr v tháng 9. Các hàm User-Defined (đưc to ra bi câu lnh CREATE FUNCTION và phn body thưng đưc gói trong cp lnh BEGIN...END) cũng đưc chia làm các nhóm như sau: Scalar Functions : Loi này cũng tr v mt giá tr đơn bng câu lnh RETURNS. Table Functions : Loi này tr v mt table http://ebooks.vdcmedia.com HC SQL SERVER 2000 12 Data Type (Loi D Liu) Các loi d liu trong SQL Server s đưc bàn k trong các bài sau Expressions Các Expressions có dng Identifier + Operators (như +,-,*,/,=...) + Value Các thành phn Control-Of Flow Như BEGIN...END, BREAK, CONTINUE, GOTO, IF...ELSE, RETURN, WHILE.... Xin xem thêm Books Online đ bit thêm v các thành phn này. Comments (Chú Thích) T-SQL dùng du -- đ đánh du phn chú thích cho câu lnh đơn và dùng /*...*/ đ chú thích cho mt nhóm Thc Thi Các Câu Lnh SQL Thc thi mt câu lnh đơn: Mt câu lnh SQL đưc phân ra thành các thành phn cú pháp như trên bi mt parser, sau đó SQL Optimizer (mt b phn quan trng ca SQL Server) s phân tích và tìm cách thc thi (Execute Plan) ti ưu nht ví d như cách nào nhanh và tn ít tài nguyên ca máy nht... và sau đó SQL Server Engine s thc thi và tr v kt qu. Thc Thi mt nhóm lnh (Batches) Khi thc thi mt nhóm lnh SQL Server s phân tích và tìm bin pháp ti ưu cho các câu lnh như mt câu lnh đơn và cha execution plan đã đưc biên dch (compiled) trong b nh sau đó nu nhóm lnh trên đưc gi li ln na thì SQL Server không cn biên dch mà có th thc thi ngay điu này giúp cho mt batch chy nhanh hơn. Lnh GO Lnh này ch dùng đ gi mt tín hiu cho SQL Server bit đã kt thúc mt batch job và yêu cu thc thi. Nó vn không phi là mt lnh trong T-SQL. Tóm li trong phn này chúng ta đã tìm hiu v Transact- SQL là ngôn ng chính đ giao tip vi SQL Server. Trong bài sau chúng ta s tip tc bàn v cu trúc bên trong ca SQL Server . http://ebooks.vdcmedia.com HC SQL SERVER 2000 13 Design and Implement a SQL Server Database Cu Trúc Ca SQL Server Như đã trình bày các bài trưc mt trong nhng đc đim ca SQL Server 2000 là Multiple-Instance nên khi nói đn mt (SQL) Server nào đó là ta nói đn mt Instance ca SQL Server 2000, thông thưng đó là Default Instance. Mt Instance ca SQL Server 2000 có 4 system databases và mt hay nhiu user database. Các system databases bao gm: Master : Cha tt c nhng thông tin cp h thng (system-level information) bao gm thông tin v các database khác trong h thng như v trí ca các data files, các login account và các thit đt cu hình h thng ca SQL Server (system configuration settings). Tempdb : Cha tt c nhng table hay stored procedure đưc tm thi to ra trong quá trình làm vic bi user hay do bn thân SQL Server engine. Các table hay stored procedure này s bin mt khi khi đng li SQL Server hay khi ta disconnect. Model : Database này đóng vai trò như mt bng km (template) cho các database khác. Nghĩa là khi mt user database đưc to ra thì SQL Server s copy toàn b các system objects (tables, stored procedures...) t Model database sang database mi va to. Msdb : Database này đưc SQL Server Agent s dng đ hoch đnh các báo đng và các công vic cn làm (schedule alerts and jobs). Cu Trúc Vt Lý Ca Mt SQL Server Database Mi mt database trong SQL Server đu cha ít nht mt data file chính (primary), có th có thêm mt hay nhiu data file ph (Secondary) và mt transaction log file. Primary data file (thưng có phn m rng .mdf) : đây là file chính cha data và nhng system tables. Secondary data file (thưng có phn m rng .ndf) : đây là file ph thưng ch s dng khi database đưc phân chia đ cha trên nhiu dĩa. Transaction log file (thưng có phn m rng .ldf) : đây là file ghi li tt c nhng thay đi din ra trong mt database và cha đy đ thông tin đ có th roll back hay roll forward khi cn. http://ebooks.vdcmedia.com HC SQL SERVER 2000 14 Data trong SQL Server đưc cha thành tng Page 8KB và 8 page liên tc to thành mt Extent như hình v dưi đây: Trưc khi SQL Server mun lưu data vào mt table nó cn phi dành riêng mt khong trng trong data file cho table đó. Nhng khong trng đó chính là các extents. Có 2 loi Extents: Mixed Extents (loi hn hp) dùng đ cha data ca nhiu tables trong cùng mt Extent và Uniform Extent (loi thun nht) dùng đ cha data ca mt table. Ðu tiên SQL Server dành các Page trong Mixed Extent đ cha data cho mt table sau đó khi data tăng trưng thì SQL dành hn mt Uniform Extent cho table đó. Nguyên Tc Hot Ðng Ca Transaction Log Trong SQL Server Transaction log file trong SQL Server dùng đ ghi li các thay đi xy ra trong database. Quá trình này din ra như sau: đu tiên khi có mt s thay đi data như Insert, Update, Delete đưc yêu cu t các ng dng, SQL Server s ti (load) data page tương ng lên memory (vùng b nh này gi là data cache), sau đó data trong data cache đưc thay đi(nhng trang b thay đi còn gi là dirty-page). Tip theo mi s thay đi đu đưc ghi vào transaction log file cho nên ngưi ta gi là write-ahead log. Cui cùng thì mt quá trình gi là Check Point Process s kim tra và vit tt c nhng transaction đã đưc commited (hoàn tt) vào dĩa cng (flushing the page). http://ebooks.vdcmedia.com HC SQL SERVER 2000 15 Ngoài Check Point Process nhng dirty-page còn đưc đưa vào dĩa bi mt Lazy writer. Ðây là mt anh chàng làm vic âm thm ch thc gic và quét qua phn data cache theo mt chu k nht đnh sau đó li ng yên ch ln quét ti. Xin gii thích thêm mt chút v khái nim transaction trong database. Mt transaction hay mt giao dch là mt lot các hot đng xy ra đưc xem như mt công vic đơn (unit of work) nghĩa là hoc thành công toàn b hoc không làm gì c (all or nothing). Sau đây là mt ví d c đin v transaction: Chúng ta mun chuyn mt s tin $500 t account A sang account B như vy công vic này cn làm các bưc sau: 1. Tr $500 t account A 2. Cng $500 vào account B Tuy nhiên vic chuyn tin trên phi đưc thc hin dưi dng mt transaction nghĩa là giao dch ch đưc xem là hoàn tt (commited) khi c hai bưc trên đu thc hin thành công. Nu vì mt lý do nào đó ta ch có th thc hin đưc bưc 1 (chng hn như va xong bưc 1 thì đin cúp hay máy b treo) thì xem như giao dch không hoàn tt và cn phi đưc phc hi li trng thái ban đu (roll back). Th thì Check Point Process hot đng như th nào đ có th đm bo mt transaction đưc thc thi mà không làm "dơ" database. http://ebooks.vdcmedia.com HC SQL SERVER 2000 16 Trong hình v trên, mt transaction đưc biu din bng mt mũi tên. Trc nm ngang là trc thi gian. Gi s mt Check Point đưc đánh du vào thi đim gia transaction 2 và 3 như hình v và sau đó s c xãy ra trưc khi gp mt Check point k tip. Như vy khi SQL Server đưc restart nó s da trên nhng gì ghi trong transaction log file đ phc hi data (xem hình v). Ðiu đó có nghĩa là SQL Server s không cn làm gì c đi vi transaction 1 vì ti thi đim Check point data đã đưc lưu vào dĩa ri. Trong khi đó transaction 2 và 4 s đưc roll forward vì tuy đã đưc commited nhưng do s c xy ra trưc thi đim check point k tip nên data chưa kp lưu vào dĩa. Tc là da trên nhng thông tin đưc ghi trên log file SQL Server hoàn toàn có đy đ cơ s đ vit vào dĩa cng. Còn transaction 3 và 5 thì chưa đưc commited (do b down bt ng) cho nên SQL Server s roll back hai transaction này da trên nhng gì đưc ghi trên log file. Cu Trúc Logic Ca Mt SQL Server Database Hu như mi th trong SQL Server đưc t chc thành nhng objects ví d như tables, views, stored procedures, indexes, constraints.... Nhng system objects trong SQL Server thưng có bt đu bng ch sys hay sp. Các objects trên s đưc nghiên cu ln lưt trong các bài sau do đó trong phn này chúng ta ch bn sơ qua mt s system object thông dng trong SQL Server database mà thôi. http://ebooks.vdcmedia.com HC SQL SERVER 2000 17 Mt s Sytem objects thưng dùng: System Stored Procedure ng dng Sp_help ['object'] Cung cp thông tin v mt database object (table, view...) hay mt data type. Sp_helpdb ['database'] Cung cp thông tin v mt database c th nào đó. Sp_monitor Cho bit đ bn rn ca SQL Server Sp_spaceused ['object', 'updateusage' ] Cung cp thông tin v các khong trng đã đưc s dng cho mt object nào đó Sp_who ['login'] Cho bit thông tin v mt SQL Server user Ví d: sp_helpdb 'Northwind' s cho kt qu có dng như bng dưi đây name db_size owner dbid created status ..... ------------------------------------------------------------------------------------------------------------------------------- ------Northwind 3.94 MB sa 6 Aug 6 2000 Status=ONLINE, Updateability=READ_WRITE, ..... stored procedure sp_spaceused như ví d sau USE Northwind Go sp_spaceused 'Customers' s cho bit thông tin v table Customer: name rows reserved data index_size unused ------------------------------------- -----------------------------------------Customers 91 104 KB 24 KB 80 KB 0 KB To Mt User Database Chúng ta có th to mt database d dàng dùng SQL Server Enterprise bng cách right-click lên trên "database" và chn "New Database" như hình v sau: http://ebooks.vdcmedia.com HC SQL SERVER 2000 18 Sau đó chúng ta ch vic đánh tên ca database và click OK. Ngoài ra đôi khi chúng ta cũng dùng SQL script đ to mt database. Khi đó ta phi ch rõ v trí ca primary data file và transaction log file. Ví d: USE master GO CREATE DATABASE Products ON ( NAME = prods_dat, FILENAME = 'c:\program files\microsoft SQL server\mssql\data\prods.mdf', SIZE = 4, MAXSIZE = 10, FILEGROWTH = 1 ) GO Trong ví d trên ta to mt database tên là Products vi logical file name là prods_dat và physical file name là prods.mdf, kích thưc ban đu là 4 MB và data file s t đng tăng lên mi ln 1 MB cho ti ti đa là 10 MB. Nu ta không http://ebooks.vdcmedia.com HC SQL SERVER 2000 19 ch đnh mt transaction log file thì SQL s t đng to ra 1 log file vi kích thưc ban đu là 1 MB. Lưu Ý: Khi to ra mt database chúng ta cũng phi lưu ý mt s đim sau: Ði vi các h thng nh mà đó vn đ tc đ ca server không thuc loi nhy cm thì chúng ta thưng chn các giá tr mc đnh (default) cho Initial size, Automatically growth file. Nhưng trên mt s production server ca các h thng ln kích thưc ca database phi đưc ngưi DBA ưc lưng trưc tùy theo tm c ca business, và thông thưng ngưi ta không chn Autogrowth(t đng tăng trưng) và Autoshrink(t đng nén). Câu hi đưc đt ra đây là vì sao ta không đ SQL Server chn mt giá tr khi đu cho datafile và sau đó khi cn thì nó s t đng ni rng ra mà li phi ưc lưng trưc? Nguyên nhân là nu chn Autogrowth (hay Autoshrink) thì chúng ta có th s gp 2 vn đ sau: Performance hit: nh hưng đáng k đn kh năng làm vic ca SQL Server. Do nó phi thưng xuyên kim tra xem có đ khong trng cn thit hay không và nu không đ nó s phi m rng bng cách dành thêm khong trng t dĩa cng và chính quá trình này s làm chm đi hot đng ca SQL Server. Disk fragmentation : Vic m rng trên cũng s làm cho data không đưc liên tc mà cha nhiu nơi khác nhau trong dĩa cng điu này cũng gây nh hưng lên tc đ làm vic ca SQL Server. Trong các h thng ln ngưi ta có th d đoán trưc kích thưc ca database bng cách tính toán kích thưc ca các tables, đây cũng ch là kích thưc ưc đoán mà thôi (xin xem "Estimating the size of a database" trong SQL Books Online đ bit thêm v cách tính) và sau đó thưng xuyên dùng mt s câu lnh SQL (thưng dùng các câu lnh bt đu bng DBCC .Phn này s đưc bàn qua trong các bài sau) kim tra xem có đ khong trng hay không nu không đ ta có th chn mt thi đim mà SQL server ít bn rn nht (như ban đêm hay sau gi làm vic) đ ni rng data file như th s không làm nh hưng ti performance ca Server. Chú ý gi s ta dành sn 2 GB cho datafile, khi dùng Window Explorer đ xem ta s thy kích thưc ca file là 2 GB nhưng data thc t có th ch chim vài chc MB mà thôi. Nhng Ðim Cn Lưu Ý Khi Thit K Mt Database Trong phm vi bài này chúng ta không th nói sâu v lý thuyt thit k database mà ch đưa ra mt vài li khuyên mà bn nên tuân theo khi thit k. http://ebooks.vdcmedia.com HC SQL SERVER 2000 20 Trưc ht bn phi nm vng v các loi data type. Ví d bn phi bit rõ s khác bit gia char(10), nchar(10) varchar(10), nvarchar(10). Loi d liu Char là mt loi string có kích thưc c đnh nghĩa là trong ví d trên nu data đưa vào "This is a really long character string" (ln hơn 10 ký t) thì SQL Server s t đng ct phn đuôi và ta ch còn "This is a". Tương t nu string đưa vào nh hơn 10 thì SQL s thêm khong trng vào phía sau cho đ 10 ký t. Ngưc li loi varchar s không thêm các khong trng phía sau khi string đưa vào ít hơn 10. Còn loi data bt đu bng ch n cha d liu dng unicode. Mt lưu ý khác là trong SQL Server ta có các loi Integer như : tinyint, smallint, int, bigint. Trong đó kích thưc tng loi tương ng là 1,2,4,8 bytes. Nghĩa là loi smallint tương đương vi Integer và loi int tương đương vi Long trong VB. Khi thit k table nên: Có ít nht mt ct thuc loi ID dùng đ xác đnh mt record d dàng. Ch cha data ca mt entity (mt thc th) Trong ví d sau thông tin v Sách và Nhà Xut Bn đưc cha trong cùng mt table Books BookID Title Publisher PubState PubCity PubCountry 1 Inside SQL Server 2000 Microsoft Press CA Berkely USA 2 Windows 2000 Server MA Boston USA 3 Beginning Visual Basic Wrox 6.0 CA Berkely USA New Riders Ta nên tách ra thành table Books và table Publisher như sau: Books BookID Title PublisherID 1 Inside SQL Server 2000 P1 2 Windows 2000 Server P2 3 Beginning Visual Basic 6.0 P3 http://ebooks.vdcmedia.com HC SQL SERVER 2000 21 và Publishers PublisherID Publisher PubState PubCity PubCountry P1 Microsoft Press CA Berkely USA P2 New Riders MA Boston USA P3 Wrox CA Berkely USA Tránh dùng ct có cha NULL và nên luôn có giá tr Default cho các ct Tránh lp li mt giá tr hay ct nào đó Ví d mt cun sách có th đưc vit bi hơn mt tác gi và như th ta có th dùng mt trong 2 cách sau đ cha data: Books BookID Title Authors 1 Inside SQL Server 2000 John Brown 2 Windows 2000 Server Matthew Bortniker, Rick Johnson 3 Beginning Visual Basic 6.0 Peter Wright, James Moon, John Brown hay Books BookID Title Author1 Author2 Author3 Null Null Matthew Bortniker Rick Johnson Null Peter Wright James Moon John Brown 1 Inside SQL Server 2000 John Brown 2 Windows 2000 Server 3 Beginning Visual Basic 6.0 Tuy nhiên vic lp đi lp li ct Author s to nhiu vn đ sau này. Chng hn như nu cun sách có nhiu hơn 3 tác gi thì chúng ta s gp phin phc ngay....Trong ví d này ta nên cht ra thành 3 table như sau: http://ebooks.vdcmedia.com HC SQL SERVER 2000 22 Books BookID Title 1 Inside SQL Server 2000 2 Windows 2000 Server 3 Beginning Visual Basic 6.0 Authors AuthID First Name Last Name A1 John Brown A2 Matthew Bortniker A3 Rick Johnson A4 Peter Wright A5 James Moon AuthorBook BookID AuthID 1 A1 2 A2 2 A3 3 A4 3 A5 3 A1 Ngoài ra mt trong nhng điu quan trng là phi bit rõ quan h (Relationship) gia các table: One-to-One Relationships : trong mi quan h này thì mt hàng bên table A không th liên kt vi hơn 1 hàng bên table B và ngưc li. One-to-Many Relationships : trong mi quan h này thì mt hàng bên table A có th liên kt vi nhiu hàng bên table B. Many-to-Many Relationships : trong mi quan h này thì mt hàng bên table A có th liên kt vi nhiu hàng bên table B và mt hàng bên table B cũng có th liên kt vi nhiu hàng bên table A. Như ta thy trong ví d trên mt cun sách có th đưc vit bi nhiu tác gi và mt tác gi cũng có th vit nhiu cun sách. Do đó mi quan h gia Books và Authors là quan h Many to Many. Trong trưng hp này ngưi ta thưng dùng mt table trung gian đ gii quyt vn đ (table AuthorBook). http://ebooks.vdcmedia.com HC SQL SERVER 2000 23 Ð có mt database tương đi hoàn ho nghĩa là thit k sao cho data cha trong database không tha không thiu bn cn bit thêm v các th thut Normalization. Tuy nhiên trong phm vi khóa hc này chúng tôi không mun bàn sâu hơn v đ tài này, bn có th xem thêm trong các sách dy lý thuyt cơ s d liu. Tóm li trong bài này chúng ta đã tìm hiu v cu trúc ca mt SQL Server database và mt s vn đ cn bit khi thit k mt database. Trong bài sau chúng ta s bàn v Backup và Restore database như th nào. http://ebooks.vdcmedia.com HC SQL SERVER 2000 24 Backup And Restore SQL Server Chin Lưc Phc Hi D Liu (Data Restoration Strategy) Có mt điu mà chúng ta phi chú ý là hu như bt k database nào cũng cn đưc phc hi vào mt lúc nào đó trong sut chu k sng ca nó. Là mt ngưi Database Administrator bn cn phi gim ti đa s ln phi phc hi d liu, luôn theo dõi, kim tra thưng xuyên đ phát hin các trc trc trưc khi nó xy ra. Phi d phòng các bin c có th xy ra và bo đm rng có th nhanh chóng phc hi d liu trong thi gian sm nht có th đưc. Các dng bin c hay tai ha có th xy ra là: Ðĩa cha data file hay Transaction Log File hay system file b mt Server b hư hng Nhng thm ha t nhiên như bão lt, đng đt, ha hon Toàn b server b đánh cp hoc phá hy Các thit b dùng đ backup - restore b đánh cp hay hư hng Nhng li do vô ý ca user như l tay delete toàn b table chng hn Nhng hành vi mang tính phá hoi ca nhân viên như c ý đưa vào nhng thông tin sai lc. B hack (nu server có kt ni vi internet). Bn phi t hi khi các vn đ trên xy ra thì bn s làm gì và phi luôn có bin pháp đ phòng c th cho tng trưng hp c th. Ngoài ra bn phi xác đnh thi gian ti thiu cn phc hi d liu và đưa server tr li hot đng bình thưng. Các Loi Backup Ð có th hiu các kiu phc hi d liu khác nhau bn phi bit qua các loi backup trong SQL Server Full Database Backups : Copy tt c data files trong mt database . Tt c nhng user data và database objects như system tables, indexes, user-defined tables đu đưc backup. http://ebooks.vdcmedia.com HC SQL SERVER 2000 25 Differential Database Backups : Copy nhng thay đi trong tt c data files k t ln full backup gn nht. File or File Group Backups : Copy mt data file đơn hay mt file group. Differential File or File Group Backups : Tương t như differential database backup nhưng ch copy nhng thay đi trong data file đơn hay mt file group. Transaction Log Backups : Ghi nhn mt cách th t tt c các transactions cha trong transaction log file k t ln transaction log backup gn nht. Loi backup này cho phép ta phc hi d liu tr ngưc li vào mt thi đim nào đó trong quá kh mà vn đm bo tính đng nht (consistent). Trong lúc backup SQL Server cũng copy tt c các hot đng ca database k c hot đng xy ra trong quá trình backup cho nên ta có th backup trong khi SQL đang chy mà không cn phi ngưng li. Recovery Models Full Recovery Model : Ðây là model cho phép phc hi d liu vi ít ri ro nht. Nu mt database trong mode này thì tt c các hot đng không ch insert, update, delete mà k c insert bng Bulk Insert, hay bcp đu đưc log vào transaction log file. Khi có s c thì ta có th phc hi li d liu ngưc tr li ti mt thi đim trong quá kh. Khi data file b hư nu ta có th backup đưc transaction log file thì ta có th phc hi database đn thi đim transaction gn nht đưc commited. Bulk-Logged Recovery Model : mode này các hot đng mang tính hàng lot như Bulk Insert, bcp, Create Index, WriteText, UpdateText ch đưc log minimum vào transaction log file đ đ cho bit là các hot đng này có din ra mà không log toàn b chi tit như trong Full Recovery Mode. Các hot đng khác như Insert, Update, Delete vn đưc log đy đ đ dùng cho vic phc hi sau này. Simple Recovery Model : mode này thì Transaction Log File đưc truncate thưng xuyên và không cn backup. Vi mode này bn ch có th phc hi ti thi đim backup gn nht mà không th phc hi ti mt thi đim trong quá kh. Mun bit database ca bn đang mode nào bn có th Right-click lên mt database nào đó trong SQL Server Enterprise Manager chn Properties>Options->Recovery http://ebooks.vdcmedia.com HC SQL SERVER 2000 26 Tuy nhiên có th ti đây bn cm thy rt khó hiu v nhng điu trình bày trên. Chúng ta hãy dùng mt ví d sau đ làm rõ vn đ. Ví d: Chúng ta có mt database đưc áp dng chin lưc backup như hình v sau: Trong ví d này ta schedule mt Full Database Backup vào ngày Ch Nht và Differential Backup vào các ngày th Ba và Th Năm. Transaction Log Backup đưc schedule hng ngày. Vào mt ngày Th Sáu "đen ti" mt s c xy ra đó là đĩa cha data file ca database b hư và là mt DBA bn đưc yêu cu phi phc hi d liu và đưa database tr li hot đng bình thưng. Bn phi làm sao? Trưc ht bn phi backup ngay Transaction Log File (Trong ví d này Transaction Log File đưc cha trong mt đĩa khác vi đĩa cha Data File nên không b hư và vn còn hot đng). Ngưi ta còn gi file backup trong trưng hp này là " the tail of the log" (cái đuôi). Nu Log File đưc cha trên cùng mt đĩa vi Data file thì bn có th s không backup đưc "cái đuôi" và như vy bn phi dùng đn log file backup gn nht. Khi backup "cái đuôi" này bn cn phi dùng option NO_TRUNCATE bi vì thông thưng các Transaction Log Backup s truncate(xoá) nhng phn không cn dùng đn trong transaction log file, đó là nhng transaction đã đưc commited và đã đưc vit vào database (còn gi là inactive portion of the transaction log) đ gim kích thưc ca log file. Tuy nhiên khi backup phn đuôi không đưc truncate đ đm bo tính consistent (nht quán) ca database. http://ebooks.vdcmedia.com HC SQL SERVER 2000 27 K đn bn phi restore database t Full Backup File ca ngày Ch Nht. Nó s làm 2 chuyn : copy data, log, index... t đĩa backup vào Data Files và sau đó s ln lưt thc thi các transaction trong transaction log. Lưu ý ta phi dùng option WITH NORECOVERY trong trưng hp này (tc là option th 2 "Leave database nonoperational but able to restore additional transaction logs" trong Enterprise Manager). Nghĩa là các transaction chưa hoàn tt (incomplete transaction) s không đưc roll back. Như vy database lúc này s trong tình trng inconsistent và không th dùng đưc. Nu ta chn WITH RECOVERY (hay "Leave database operational. No additional transaction logs can be restored " trong Enterprise Manager) thì các incomplete transaction s đưc roll back và database trng thái consistent nhưng ta không th nào restore các transaction log backup đưc na. Tip theo bn phi restore Differential Backup ca ngày Th Năm. Sau đó ln lưt restore các Transaction Log Backup k t sau ln Differential Backup cui cùng nghĩa là restore Transaction Log Backup ca ngày Th Năm và "Cái Ðuôi". Như vy ta có th phc hi data tr v trng thái trưc khi bin c xy ra. Quá trình này gi là Database Recovery. Cũng xin làm rõ cách dùng t Database Restoration và Database Recovery trong SQL Server. Hai t này nu dch ra ting Vit đu có nghĩa là phc hi cơ s d liu nhưng khi đc sách ting Anh phi cn thn vì nó có nghĩa hơi khác nhau. Như trong ví d trên Khi ta restore database t mt file backup nghĩa là ch đơn gin tái to li database t nhng file backup và thc thi li nhng transaction đã đưc commit nhưng database có th trong trng thái inconsistent và không s dng đưc. Nhưng khi nói đn recover nghĩa là ta không ch phc hi li data mà còn bo đm cho nó trng thái consistent và s dng đưc (usable). Có th bn s hi consistent là th nào? Phn này s đưc nói rõ trong bài sau v Data Integrity. Nhưng cũng xin dùng mt ví d đơn gin đ gii thích. Trong ví d v th nào là mt transaction bài 3 : Gi s s tin $500 đưc tr khi account A nhưng li không đưc cng vào account B và nu database không đưc quá trình khôi phc d liu t đng (automatic recovery process) ca SQL rollback thì nó s trng thái inconsistent. Nu database trng thái ging như trưc khi tr tin hoc sau khi đã cng $500 thành công vào account B thì gi là consistent. Cho nên vic backup Transaction Log File s giúp cho vic recovery data ti bt k thi đim nào trong quá kh. Ði vi Simple Recovery Model ta ch có th recover ti ln backup gn nht mà thôi. http://ebooks.vdcmedia.com HC SQL SERVER 2000 28 Như vy khi restore database ta có th chn option WITH RECOVERY đ roll back các transaction chưa đưc commited và database có th hot đng bình thưng nhưng ta không th restore thêm backup file nào na, thưng option này đưc chn khi restore file backup cui cùng trong chui backup. Nu chn option WITH NORECOVERY các transaction chưa đưc commited s không đưc roll back do đó SQL Server s không cho phép ta s dng database nhưng ta có th tip tc restore các file backup k tip, thưng option này đưc chn khi sau đó ta còn phi restore các file backup khác. Không l ch có th chn mt trong hai option trên mà thôi hay sao? Không hoàn toàn như vy ta có th chn mt option trung lp hơn là option WITH STANDBY (tc là option 3 "Leave database read-only and able to restore additional transaction logs" trong Enterprise Manager). Vi option này ta s có luôn đc tính ca hai option trên : các incomplete transaction s đưc roll back đ đm bo database consistent và có th s dng đưc nhưng ch dưi dng Read-only mà thôi, đng thi sau đó ta có th tip tc restore các file backup còn li (SQL Server s log các transaction đưc roll back trong undo log file và khi ta restore backup file k tip SQL Server s tr li trng thái no recovery t nhng gì ghi trên undo file). Ngưi ta dùng option này khi mun restore database tr li mt thi đim nào đó (a point in time) nhưng không rõ là đó có phi là thi đim mà h mun không, cho nên h s restore tng backup file dng Standby và kim chng mt s data xem đó có phi là thi đim mà h mun restore hay không (chng hn như trưc khi b delete hay trưc khi mt transaction nào đó đưc thc thi) trưc khi chuyn sang Recovery option. Backup Database Trong phn này chúng ta s bàn v cách backup database. Nhưng trưc ht chúng ta hãy làm quen vi mt s thut ng dùng trong quá trình backup và restore. Có nhng t ta s đ nguyên ting Anh mà không dch. Thut Ng Gii Thích Backup Quá trình copy toàn b hay mt phn ca database, transaction log, file hay file group hình thành mt backup set. Backup set đưc cha trên backup media (tape or disk) bng cách s dng mt backup device (tape drive name hay physical filename) Backup Device Mt file vt lý (như C:\SQLBackups\Full.bak) hay tape drive c th (như \\.\Tape0) dùng đ record mt backup vào mt backup media. Backup File File cha mt backup set Backup Media Disk hay tape đưc s dng đ cha mt backup set. Backup media có th cha nhiu backup sets (ví d như t nhiu SQL Server 2000 backups và t nhiu Windows 2000 backups). Backup Set Mt b backup t mt ln backup đơn đưc cha trên backup media. http://ebooks.vdcmedia.com HC SQL SERVER 2000 29 Chúng ta có th to mt backup device c đnh (permanent) hay to ra mt backup file mi cho mi ln backup. Thông thưng chúng ta s to mt backup device c đnh đ có th dùng đi dùng li đc bit cho vic t đng hóa công vic backup. Ð to mt backup device dùng Enterprise Manager bn chn Management->Backup ri Right-click->New Backup Device. Ngoài ra bn có th dùng sp_addumpdevice system stored procedure như ví d sau: USE Master Go Sp_addumpdevice 'disk' , 'FullBackupDevice' , 'E:\SQLBackups\Full.bak' Ð backup database bn có th dùng Backup Wizard hoc click lên trên database mun backup sau đó Right-click->All Tasks->Backup Database... s hin ra window như hình v sau: Sau đó da tùy theo yêu cu ca database mà chn các option thích hp. Ta có th schedule cho SQL Server backup đnh k. http://ebooks.vdcmedia.com HC SQL SERVER 2000 30 Restore Database Trưc khi restore database ta phi xác đnh đưc th t file cn restore. Các thông tin này đưc SQL Server cha trong msdb database và s cho ta bit backup device nào, ai backup vào thi đim nào. Sau đó ta tin hành restore. Ð restore bn Right-click->All Tasks->Restore database... s thy window như hình v sau: Nu bn restore t mt instance khác ca SQL Server hay t mt server khác bn có chn From device option và chn backup device (file backup) tương ng . Lưu ý nu bn mun overwrite database có sn vi data đưc backup bn có th chn option Force restore over existing database như hình v sau: http://ebooks.vdcmedia.com HC SQL SERVER 2000 31 Bn có th chn leave database operational hay nonoperational tùy theo trưng hp như đã gii thích trên. Tóm li trong bài này chúng ta đã tìm hiu mt chút lý thuyt v backup và restore database trong SQL Server. Ð có th hiu rõ hơn bn cn phi thc tp hay làm th đ có thêm kinh nghim. Trong bài sau chúng ta s bàn v đ tài Data Integrity nghĩa là làm sao đ đm bo data cha trong database là đáng tin cy và không b "lũng l" như cách nói bình dân mà tôi thưng hay dùng. Data Integrity and Advanced Query Technique Nói đn Data Integrity là ta nói đn tính toàn vn ca mt database hay nói mt cách khác là data cha trong database phi chính xác và đáng tin cy. Nu data cha trong database không chính xác ta nói database mt tính toàn vn (lost data integrity). Trong bài này chúng ta s bàn qua các phương pháp đ gi cho database đưc toàn vn. http://ebooks.vdcmedia.com HC SQL SERVER 2000 32 Các Phương Pháp Ðm Bo Data Integrity SQL Server dùng mt s cách đ đm bo Data Integrity. Mt s cách như Triggers hay Index s đưc bàn đn trong các bài sau tuy nhiên trong phm vi bài này chúng ta cũng nói sơ qua các cách trên. Data Type : Data type cũng có th đm bo tính toàn vn ca data ví d bn khai báo data type ca mt ct là Integer thì bn không th đưa giá tr thuc dng String vào đưc. Not Null Definitions : Null là mt loi giá tr đc bit, nó không tương đương vi zero, blank hay empty string " " mà có nghĩa là không bit (unknown) hay chưa đưc đnh nghĩa (undefined). Khi thit k database ta nên luôn cn thn trong vic cho phép mt ct đưc Null hay Not Null vì vic cha Null data có th làm cho mt s ng dng vn không xa lý null data k lưng b "té". Default Definitions : Nu mt ct đưc cho mt giá tr default thì khi bn không đưa vào mt giá tr c th nào thì SQL Server s dùng giá tr mc đnh này. Bn phi dùng Default đi vi Not Null definition. Identity Properties : Data thuc dng ID s đm bo tính duy nht ca data trong table. Constraints : Ðây s là phn mà ta đào sâu trong bài này. Constraint tm dch là nhng ràng buc mà ta dùng đ đm bo tính toàn vn ca data. Constraints là nhng quy lut mà ta áp đt lên mt ct đ đm bo tính chính xác ca d liu đưc nhp vào. Rules : Ðây là mt object mang tính backward-compatible ch yu đ tương thích vi các version trưc đây ca SQL Server. Rules tương đương vi CHECK Constraint trong SQL Server 2000 nhưng ngưi ta có xu hưng s dng CHECK Constraint vì nó chính xác hơn và có th đt nhiu Constraints lên mt ct trong khi đó ch có mt rule cho mt ct mà thôi. Chú ý rule là mt object riêng và sau đó liên kt vi mt ct nào đó ca table trong khi CHECK constraint là mt thuc tính ca table nên có th đưc to ra vi lnh CREATE TABLE. Triggers : Mt loi stored procedure đc bit đưc thc thi mt cách t đng khi mt table đưc Update, Insert, hay Delete. Ví d ta mun khi mt món hàng đưc bán ra thì tng s hàng hóa trong kho phi đưc gim xung (-1) chng hn khi đó ta có th dùng trigger đ đm bo chuyn đó. Triggers s đưc bàn k trong các bài sau. Indexes : s đưc bàn đn trong bài nói v Indexes. http://ebooks.vdcmedia.com HC SQL SERVER 2000 33 Constraints Constraints là nhng thuc tính (property) mà ta áp đt lên mt table hay mt ct đ tránh vic lưu d liu không chính xác vào database (invalid data). Tht ra NOT NULL hay DEFAULT cũng đưc xem là mt dng constraint nhưng chúng ta không bao gm hai loi này đây mà ch trình bày 4 loi constraints là Primary Key Constraint, Unique Constraint, Foreign Key Constraint và Check Constraint. Primary Key Constraint: Mt table thưng có mt hay nhiu ct có giá tr mang tính duy nht đ xác đnh mt hàng bt k trong table. Ta thưng gi là Primary Key và đưc to ra khi ta Create hay Alter mt table vi Primary Key Constraint. Mt table ch có th có mt Primary Key constraint. Có th có nhiu ct tham gia vào vic to nên mt Primary Key, các ct này không th cha Null và giá tr trong các ct thành viên có th trùng nhau nhưng giá tr ca tt c các ct to nên Primary Key phi mang tính duy nht. Khi mt Primary Key đưc to ra mt Unique Index s đưc t đng to ra đ duy trì tính duy nht. Nu trong table đó chưa có Clustered Index thì mt Unique + Clustered Index s đưc to ra. Có th to ra Primary Key Constraints như sau: CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 VARCHAR(30) ) hay CREATE TABLE Table1 (Col1 INT, Col2 VARCHAR(30), CONSTRAINT table_pk PRIMARY KEY (Col1) ) Unique Constraint Bn có th to Unique Constraint đ đm bo giá tr ca mt ct nào đó không b trùng lp. Tuy Unique Constraint và Primary Key Constraint đu đm bo tính duy nht nhưng bn nên dùng Unique Constraint trong nhng trưng hp sau: http://ebooks.vdcmedia.com HC SQL SERVER 2000 34 Nu mt ct (hay mt s kt hp gia nhiu ct) không phi là primary key. Nên nh ch có mt Primary Key Constraint trong mt table trong khi ta có th có nhiu Unique Constraint trên mt table. Nu mt ct cho phép cha Null. Unique constraint có th áp đt lên mt ct cha giá tr Null trong khi primary key constraint thì không. Cách to ra Unique Constraint cũng tương t như Primary Key Constraint ch vic thay ch Primary Key thành Unique. SQL Server s t đng to ra mt nonclustered unique index khi ta to mt Unique Constraint. Foreign Key Constraint Foreign Key là mt ct hay mt s kt hp ca nhiu ct đưc s dng đ áp đt mi liên kt data gia hai table. Foreign key ca mt table s gi giá tr ca Primary key ca mt table khác và chúng ta có th to ra nhiu Foreign key trong mt table. Foreign key có th reference (tham chiu) vào Primary Key hay ct có Unique Constraints. Foreign key có th cha Null. Mc dù mc đích chính ca Foreign Key Constraint là đ kim soát data cha trong table có Foreign key (tc table con) nhưng thc cht nó cũng kim soát luôn c data trong table cha Primary key (tc table cha). Ví d nu ta delete data trong table cha thì data trong table con tr nên "m côi" (orphan) vì không th reference ngưc v table cha. Do đó Foreign Key constraint s đm bo điu đó không xy ra. Nu bn mun delete data trong table cha thì trưc ht bn phi drop hay disable Foreign key trong table con trưc. Có th to ra Foreign Key Constraints như sau: CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 INT REFERENCES Employees(EmployeeID) ) hay CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 INT, CONSTRAINT col2_fk FOREIGN KEY (Col2) REFERENCES Employees (EmployeeID) ) Ðôi khi chúng ta cũng cn Disable Foreign Key Constraint trong trưng hp: http://ebooks.vdcmedia.com HC SQL SERVER 2000 35 Insert hay Update: Nu data insert vào s vi phm nhng ràng buc có sn (violate constraint) hay constraint ca ta ch mun áp dng cho data hin thi mà thôi ch không phi data s insert. Tin hành quá trình replicate. Nu không disable Foreign Key Constraint khi replicate data thì có th cn tr quá trình copy data t source table ti destination table mt cách không cn thit. Check Constraint Check Constraint dùng đ gii hn hay kim soát giá tr đưc phép insert vào mt ct. Check Constraint ging Foreign Key Constraint ch nó kim soát giá tr đưa vào mt ct nhưng khác ch Foreign Key Constraint da trên giá tr table cha đ cho phép mt giá tr đưc chp nhn hay không trong khi Check Constraint da trên mt biu thc logic (logic expression) đ kim tra xem mt giá tr có hp l không. Ví d ta có th áp đt mt Check Constraint lên ct salary đ ch chp nhn tin lương t $15000 đn $100000/năm. Ta có th to ra nhiu Check Constraint trên mt ct. Ngoài ra ta có th to mt Check Constraint trên nhiu ct bng cách to ra Check Constraint mc table (table level). Có th to ra Check Constraint như sau: CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 INT CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000), Col3 VARCHAR(30) ) Trong ví d này ta gii hn giá tr chp nhn đưc ca ct Col2 t 0 đn 1000. Ví d sau s to ra mt Check Constraint ging như trên nhưng table level: CREATE TABLE Table1 (Col1 INT PRIMARY KEY, Col2 INT, Col3 VARCHAR(30), CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000) ) Tương t như Foreign Key Constraint đôi khi ta cũng cn disable Check Constraint trong trưng hp Insert hay Update mà vic kim soát tính hp l ca data không áp dng cho data hin ti. Trưng hp th hai là replication. Mun xem hay to ra Constraint bng Enterprise Manager thì làm như sau: http://ebooks.vdcmedia.com HC SQL SERVER 2000 36 Click lên trên mt table nào đó và chn Design Table-> Click vào icon bên phi "Manage Constraints..." Advanced Query Techniques Trong phn này chúng ta s đào sâu mt s câu lnh nâng cao như SELECT, INSERT... Có th nói hu như ai cũng bit qua câu lnh căn bn kiu như "SELECT * FROM TABLENAME WHERE..." nhưng có th có nhiu ngưi không bit đn nhng tính cht nâng cao ca nó. Cú pháp đy đ ca mt câu lnh SELECT rt phc tp tuy nhiên đây ch trình bày nhng nét chính ca lnh này mà thôi: SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] Chúng ta s ln lưt nghiên cu tng clause (mnh đ) trong câu lnh này. SELECT Clause Sau keyword (t khóa) SELECT ta s có mt danh sách các ct mà ta mun select đưc cách nhau bng du ",". Có 3 Keywords cn nhn mnh trong phn SELECT. Distinct : Khi có keyword này vào thì s cho kt qu các ct không trùng nhau. Ví d trong Orders table ca Norwind database (database mu di kèm vi SQL Server) cha giá tr trùng lp (duplicate value) trong ct ShipCity. Nu ta mun select mt danh sách ShipCity trong đó mi city ch xut hin mt ln trong kt qu nhn đưc ta dùng như sau: SELECT DISTINCT ShipCity, ShipRegion FROM Orders ORDER BY ShipCity Top n : Nu ta mun select n hàng đu tiên mà thôi ta có th dùng Top keyword. Nu có thêm ORDER BY thì kt qu s đưc order trưc sau đó mi select. Chúng ta cũng có th select s hàng http://ebooks.vdcmedia.com HC SQL SERVER 2000 37 da trên phn trăm bng cách thêm Keyword Percent vào. Ví d sau s select 10 hàng đu tiên theo th t: SELECT DISTINCT TOP 10 ShipCity, ShipRegion FROM Orders ORDER BY ShipCity As : Ðôi khi chúng ta mun cho SELECT statement d đc hơn mt chút ta có th dùng mt alias (tc là t thay th hay t vit tt) vi keyword As hay không có keyword As: table_name As table_alias hay table_name table_alias. Ví d: USE pubs SELECT p.pub_id, p.pub_name AS PubName FROM publishers AS p Ngoài ra trong Select list ta có th select dưi dng mt expression như sau: SELECT FirstName + ' ' + LastName AS "Employee Name", IDENTITYCOL AS "Employee ID", HomePhone, Region FROM Northwind.dbo.Employees ORDER BY LastName, FirstName ASC Trong ví d trên ta select ct "Employee Name" là sn phm ghép li ca ct FirstName và LastName đưc cách nhau bng mt khong trng. Mt giá tr thuc loi identity đ làm ct "Employee ID". Kt qu s đưc sp theo th t t nh ti ln (ASC) (còn DESC là t ln ti nh) trong đó ct LastName đưc sp trưc ri mi ti ct FirstName. The INTO Clause INTO Clause cho phép ta select data t mt hay nhiu table sau đó kt qu s đưc insert vào mt table mi. Table này đưc to ra do kt qu ca câu lnh SELECT INTO. Ví d: SELECT FirstName, LastName INTO EmployeeNames FROM Employers Câu lnh tên s to ra mt table mi có tên là EmployeeNames vi 2 ct là FirstName và LastName sau đó kt qu select đưc t table Employers s đưc insert vào table mi này. Nu table EmployeeNames tn ti SQL Server s báo li. Câu lnh này thưng hay đưc s dng đ select mt lưng data ln t nhiu table khác nhau vào mt table mi (thưng dùng cho mc đích tm thi (temporary table)) mà khi phi thc thi câu lnh Insert nhiu ln. http://ebooks.vdcmedia.com HC SQL SERVER 2000 38 Mt cách khác cũng select data t mt hay nhiu table và insert vào mt table khác là dùng "Insert Into...Select...". Nhưng câu lnh này không to ra mt table mi. Nghĩa là ta table đó phi tn ti trưc. Ví d: INSERT INTO EmployeeNames SELECT FirstName, LastName FROM Employers Chú ý là không có ch "Value" trong câu Insert này. The GROUP BY and HAVING Clauses GROUP BY dùng đ to ra các giá tr tng (aggregate values) cho tng hàng trong kt qu select đưc. Ch có mt hàng cho tng giá tr riêng bit (distinct) ca tng ct. Các ct đưc select đu phi nm trong GROUP BY Clause. Hãy xem ví d phc tp sau: SELECT OrdD1.OrderID AS OrderID, SUM(OrdD1.Quantity) AS "Units Sold", SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue FROM [Order Details] AS OrdD1 WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID FROM [Order Details] AS OrdD2 WHERE OrdD2.UnitPrice > $100) GROUP BY OrdD1.OrderID HAVING SUM(OrdD1.Quantity) > 100 Trong ví d trên đu tiên ta select nhng order riêng bit (distinct) t Order Details table vi giá > 100. Sau đó tip tc select OrderID, "Units Sold", Revenue t kt qu trên trong đó "Units Sold" và Revenue là nhng aggregate columns (cho giá tr tng mt ct ca nhng hàng có cùng OrderID). HAVING Clause đóng vai trò như mt filter dùng đ lc li các giá tr cn select mà thôi. HAVING Clause thưng đi chung vi GROUP BY mc dù có th xut hin riêng l. UNION Uninon keyword có nhim v ghép ni kt qu ca 2 hay nhiu queries li thành mt kt qu. Ví d: Gi s có table1(ColumnA varchar(10), ColumnB int) và table2(ColumnC varchar(10), ColumnD int). Ta mun select data t table1 và ghép vi data t table2 đ to thành mt kt qu duy nht ta làm như sau: SELECT * FROM Table1 UNION ALL SELECT * FROM Table2 http://ebooks.vdcmedia.com HC SQL SERVER 2000 39 Nu không có keyword ALL thì nhng hàng ging nhau t 2 table s ch xut hin mt ln trong kt qu. Còn khi dùng ALL thì các hàng trong 2 table đu có trong kt qu bt chp vic lp li. Khi Dùng Union phi chú ý hai chuyn: s ct select 2 queries phi bng nhau và data type ca các ct tương ng phi compatible (tương thích). Using JOINS Trong phn này chúng ta s tìm hiu v các loi Join trong SQL Server. Bng cách s dng JOIN bn có th select data t nhiu table da trên mi quan h logic gia các table (logical relationships). Có th tóm tt các loi Join thông dng bng các hình sau: Th t t trái sang phi: Inner Join, Left Outer Join, Right Outer Join, Full Outer Join Inner Joins Dùng Inner Join đ select data t 2 hay nhiu tables trong đó giá tr ca các ct đưc join phi xut hin c 2 tables tc là phn gch chéo trên hình. Ví d: SELECT t.Title, p.Pub_name FROM Publishers AS p INNER JOIN Titles AS t ON p.Pub_id = t.Pub_id ORDER BY Title ASC Left Outer Joins Dùng Left Outer Join đ select data t 2 hay nhiu tables trong đó tt c ct bên table th nht và không tn ti bên table th hai s đưc select cng vi các giá tr ca các ct đưc inner join. S ct select đưc s bng vi s ct ca table th nht. Tc là phn tô màu đ trên hình. Ví d: USE Pubs SELECT a.Au_fname, a.Au_lname, p.Pub_name FROM Authors a LEFT OUTER JOIN Publishers p ON a.City = p.City ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC http://ebooks.vdcmedia.com HC SQL SERVER 2000 40 Right Outer Joins Dùng Right Outer Join đ select data t 2 hay nhiu tables trong đó tt c ct bên table th hai và không tn ti bên table th nht s đưc select cng vi các giá tr ca các ct đưc inner join. S ct select đưc s bng vi s ct ca table th hai. Tc là phn tô màu đ trên hình. Ví d: USE Pubs SELECT a.Au_fname, a.Au_lname, p.Pub_name FROM Authors a RIGHT OUTER JOIN Publishers p ON a.City = p.City ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC Full Outer Joins Dùng Full Outer Join đ select data t 2 hay nhiu tables trong đó tt c ct bên table th nht và th hai đu đưc chn các giá tr bên hai table bng nhau thì ch ly mt ln. Tc là phn tô màu đ trên hình. Ví d: USE Pubs SELECT a.Au_fname, a.Au_lname, p.Pub_name FROM Authors a FULL OUTER JOIN Publishers p ON a.City = p.City ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC Cross Joins Dùng Cross Join ghép data t hai table trong đó s hàng thu đưc bng vi s hàng ca table th nht nhân vi s hàng ca table th hai. Ví d: USE pubs SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers WHERE authors.city = publishers.city ORDER BY au_lname DESC Ð ý là trong câu lnh này không có keyword "On". Mun hiu rõ hơn v các loi join bn cho chy th trên SQL Server và làm phn bài tp s 1. Tóm li trong bài này chúng ta đã tìm hiu data integrity trong SQL Server bng cách dùng các loi Constraint. Ngoài ra Chúng ta cũng bit qua v mt s k thut query nâng cao. Sau bài hc này các bn cn làm bài tp s 1 đ h thng hóa li kin thc đã hc t bài 1 đn bài 5 trưc khi bn hc tip bài s 6. Khi làm bài tp nh phi làm theo th t và tuân th theo các yêu cu ca bài tp đt ra. Không nên b qua bưc nào. http://ebooks.vdcmedia.com HC SQL SERVER 2000 41 Stored Procedure and Advanced T-SQL Trong bài này chúng ta s tìm hiu mt s cách import và export data trong SQL Server. Sau đó s bàn qua các loi Stored Procedure và Cursor. S dng bcp và BULK INSERT đ import data bcp là mt command prompt dùng đ import hay export data t mt data file (Text file hay Excel File) vào SQL Server hay ngưc li. Thưng khi mun chuyn mt s lưng ln data t mt database system khác như Oracle, DB2...sang SQL Server trưc ht ta s export data ra mt text file sau đó import vào SQL Server dùng bcp command. Mt trưng hp thông dng hơn là ta export data t SQL Server sang mt Microsoft Excel file và Excel file này có th là input cho mt program hay mt database system khác. Chúng ta cũng có th chuyn data vào SQL Server dùng câu lnh BULK INSERT. Tuy nhiên BULK INSERT ch có th import data vào trong SQL Server ch không th export data ra mt data file như bcp. Ð có th insert data vào SQL Server Database, data file phi có dng bng nghĩa là có cu trúc hàng và ct. Chú ý khi data đưc bulk copy (copy hàng lot dùng bcp hay BULK INSERT) vào mt table trong SQL Server thì table đó phi tn ti và data đưc cng thêm vào (append). Ngưc li khi export data ra mt data file thì mt file mi s đưc to ra hoc data file s b overwrite nu nó tn ti. Cú pháp đy đ ca lnh bcp có th xem trong SQL Server Books Online. đây ch trình bày mt s ví d đơn gin v cách s dng bcp command và BULK INSERT. Ví d 1: Gi s bn mun export data t table Orders trong PracticeDB (đây là database đưc to ra trong bài tp s 1 ) ra mt text file trong đó các ct đưc phân cách bng du ";". Bn có th làm như sau: m DOS command prompt và đánh vào dòng lnh sau: bcp PracticeDB..Orders out c:\Orders.txt -c T t; Trong ví d trên ta mun bulk copy table Orders ra mt text file trong đó : http://ebooks.vdcmedia.com HC SQL SERVER 2000 42 out: copy data t table hay view ra mt data file (c:\Orders.txt). Ngưc li ta có th dùng switch in đ import data t text file vào SQL Server. -c: bulk copy dùng kiu d liu Character (Char) (nu không ch rõ thì SQL Server s dùng "TAB" character (\t) đ phân đnh các ct và dùng new line character (\n) đ phân đnh các hàng như các giá tr default). -t;: du ";" đi sau switch "t" cho bit ta mun dùng ";" đ phân đnh các ct (nu không s dùng giá tr mc đnh như trên) -T: dùng (NT) Trust connection đ kt ni vi database. Nghĩa là nu user đã authenticated (cho phép) vào đưc Windows system thì đương nhiên đưc s dng SQL Server mà không cn dùng thêm username và password nào khác. Ví d 2: Thay vì copy toàn b table ta có th dùng query đ select mt phn data và export ra text file như sau: bcp "Select * From practiceDB..Orders" queryout c:\Orders.txt -c -SVinhtai -Usa -Pabc Trong ví d này ta select toàn b data trong Orders table ra mt text file dùng query và SQL Server authentication. queryout : cho bit đây là mt query ch không phi là table. -S : tên ca SQL Server (hay tên ca mt Instance) -U : SQL user name dùng đ log on -P : password dùng đ log on. Ví d 3 : dùng BULK INSERT đ bulk copy data t text file vào SQL Server database. M Query Analyser (BULK INSERT là mt T-SQL command ch không phi là mt command prompt utility) và đánh vào các dòng sau : BULK INSERT PracticeDB..Orders FROM 'c:\Orders.txt ' WITH (DATAFILETYPE = 'CHAR') Trong ví d trên DATAFILETYPE= 'CHAR' cho bit data đưc cha dng Char data type. Nêú mun dùng data type dng unicode thì dùng 'WIDECHAR' Chú ý: Các switch trong bcp command là case-sensitive. Nghĩa là ch hoa và ch thưng s có ý nghĩa khác nhau. http://ebooks.vdcmedia.com HC SQL SERVER 2000 43 Distributed Queries Ðôi khi chúng ta mun select data t nhng database system khác như MS Access, Oracle, DB2... hay thm chí t mt SQL Server khác ta cn phi dùng distributed query. SQL Server s dùng k thut OLEDB và các API đ chuyn các query này ti các database system khác. Có 2 cách đ truy cp vào các database system khác là dùng LINKED SERVER và Ad Hoc Computer Name. Linked Server: Linked Server là mt server o đưc dùng đ truy cp vào các database system khác. Mt khi đã setup thì ta có th query data dùng four-part name : linked_server_name.catalog.schema.object_name . Trong đó catalog thưng tương đương vi database name, Schema tương đương vi database owner và object_name tương đương vi table hay view. Ví d: Gi s ta setup mt Linked Server vào Access database "PracticeDB.mdb" trong đó các table đu tương t như PracticeDB database trong SQL Server (đưc to ra trong phn bài tp s 1). M Enterprise Manager -> Chn node Security ca local server -> Right-Click lên node Linked Server chn New Linked Server. Sau đó nhp vào tên ca Linked Server LinkedPracticeDB, trong phn Provider Name chn Microsoft Jet 4.0 OLEDB Provider. Trong phn Data Source nhp vào v trí ca Access database (C:\PracticeDB.mdb) và click OK. Ta s có Linked Server tên LinkedPracticeDB xut hin dưi phn Security/Linked Server. Gi s ta mun select data t Linked Server này ta có th dùng Query Analyser như sau: Select * from LinkedPracticeDB...Customers Trong ví d trên ta dùng tên ca Linked Server và theo sau là ba chm (vì đ truy cp vào database ta phi dùng four-part name nhưng trong trưng hp này ta dùng default value nên không cn cho bit tên ca Catalog và Schema nhưng phi dùng du chm đ phân bit tng phn). Ngoài cách trên ta có th dùng pass-through query vi OPENQUERY function như sau: Select * from OPENQUERY(LinkedPracticeDB,'Select * from Customers') http://ebooks.vdcmedia.com HC SQL SERVER 2000 44 Trong ví d trên ta thy function OPENQUERY s tr v mt data set và có th nm sau keyword FROM như mt table. Khi dùng OPENQUERY function ta cn cho bit tên ca Linked Server và query mà ta mun thc hin. Lưu ý: function trong SQL Server đưc dùng tương t như là stored procedure. Ad Hoc Computer Name Ngoài cách dùng Linked Server như đã trình bày trên ta có th dùng ad hoc computer name (ad hoc nghĩa là lâm thi, tm thi). Nghĩa là đi vi nhng database system mà ta thưng xuyên query thì dùng Linked Server còn đi vi nhng query lâu lâu mi dùng đn thì ta có th select data bng OPENROWSET hay OPENDATASOURCE functions Ví d: ta cũng s select data t Access database như trên dùng OPENROWSET Select * from OPENROWSET('Microsoft.jet.oledb.4.0','C:\PracticeDB.mdb'; 'admin'; '', Customers) Trong ví d trên khi dùng OPENROWSET ta cn phi đưa vào tt c nhng thông tin cn thit đ connect vào database như tên ca Provider, v trí ca file, username, password (trưng hp này không có password) và tên ca table mà ta mun select. Mi ln ta thc thi câu lnh trên SQL Server đu kim tra security trong khi đó nu dùng Linked Server thì ch kim ta mt ln mà thôi. OPENROWSET tương t như OPENQUERY ch nó tr v mt rowset và có th đt vào v trí ca mt table trong câu lnh query. Ngoài cách dùng trên ta cũng có th dùng OPENDATASOURCE đ query như sau: Select * from OPENDATASOURCE('Microsoft.jet.oledb.4.0', 'Data Source = C:\PracticeDB.mdb; User ID = Admin; Password = ') ...Customers Trong ví d trên ta thy OPENDATASOURCE tr v mt phn ca four-part name (nghĩa là tương đương vi tên ca Linked Server) cho nên ta phi dùng thêm ba du chm. Cursors Nu gii thích mt cách ngn gn thì cursor tương t như recordset hay dataset trong programming. Nghĩa là ta select mt s data vào memory sau đó có th ln lưt làm vic vi tng record bng cách Move Next... http://ebooks.vdcmedia.com HC SQL SERVER 2000 45 Có 3 loi cursors là Transact- SQL Cursors, API Cursors và Client Cursors. Trong đó Transact-SQL và API thuc loi Server Cursors nghĩa là cursors đưc load lên và làm vic bên phía server. Trong khuôn kh bài hc này ta ch nghiên cu Transact-SQL cursors. Transact-SQL cursors đưc to ra trên server bng các câu lnh Transact-SQL và ch yu đưc dùng trong stored procedures và triggers. Trưc ht hãy xem qua mt ví d v cursor: DECLARE @au_lname varchar(40), @au_fname varchar(20) DECLARE Employee_Cursor CURSOR FOR SELECT LastName, FirstName FROM Northwind.dbo.Employees OPEN Employee_Cursor FETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fname WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Author:' + @au_fname + ' ' + @au_lname FETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fname END CLOSE Employee_Cursor DEALLOCATE Employee_Cursor Trong ví d trên ta s select LastName và FirstName t Employees table ca Northwind database và load vào Employee_Cursor sau đó ln lưt in tên ca các employee ra màn hình. Ð làm vic vi mt cursor ta cn theo các bưc sau: 1. Dùng câu lnh DECLARE CURSOR đ khai báo mt cursor. Khi khai báo ta cũng phi cho bit câu lnh SELECTs đưc thc hin đ ly data. 2. Dùng câu lnh OPEN đ đưa data lên memory (populate data). Ðây chính là lúc thc hin câu lnh SELECT vn đưc khai báo trên. 3. Dùng câu lnh FETCH đ ly tng hàng data t record set. C th là ta phi gi câu lnh FETCH nhiu ln. FETCH tương t như lnh Move trong ADO recordset ch nó có th di chuyn ti lui bng câu lnh FETCH FIRST, FETCH NEXT, FETCH PRIOR, FETCH LAST, FETCH ABSOLUTE n, FETCH RELATIVE n nhưng khác ch là nó ly data b vào trong variable (FETCH...FROM...INTO variable_name). Thông thưng ta FETCH data trưc sau đó loop cho ti record cui ca Cursor bng vòng lp WHILE bng cách kim tra global variable @@FETCH_STATUS (=0 nghĩa là thành công). http://ebooks.vdcmedia.com HC SQL SERVER 2000 46 4. Khi ta ving thăm tng record ta có th UPDATE hay DELETE tùy theo nhu cu (trong thí d này ch dùng lnh PRINT) 5. Dùng câu lnh CLOSE đ đóng cursor. Mt s tài nguyên (memory resource) s đưc gii phóng nhưng cursor vn còn đưc khai báo và có th OPEN tr li. 6. Dùng câu lnh DEALLOCATE đ phóng thích hoàn toàn các tài nguyên dành cho cursor (k c tên ca cursor). Lưu ý là trong ví d trên trưc khi dùng Cursor ta cũng declare mt s variable (@au_fname và @au_lname) đ cha các giá tr ly đưc t cursor. Bn có th dùng Query Analyzer đ chy th ví d trên. Stored Procedures Trong nhng bài hc trưc đây khi dùng Query Analyzer chúng ta có th đt tên và save các nhóm câu lnh SQL vào mt file dưi dng script đ có th s dng tr li sau này. Tuy nhiên thay vì save vào text file ta có th save vào trong SQL Server dưi dng Stored Procedure. Stored Procedure là mt nhóm câu lnh Transact-SQL đã đưc compiled (biên dch) và cha trong SQL Server dưi mt tên nào đó và đưc x lý như mt đơn v (ch không phi nhiu câu SQL riêng l). Ưu Ðim Ca Stored Procedure Stored Procedure có mt s ưu đim chính như sau: Performance : Khi thc thi mt câu lnh SQL thì SQL Server phi kim tra permission xem user gi câu lnh đó có đưc phép thc hin câu lnh hay không đng thi kim tra cú pháp ri mi to ra mt execute plan và thc thi. Nu có nhiu câu lnh như vy gi qua network có th làm gim đi tc đ làm vic ca server. SQL Server s làm vic hiu qu hơn nu dùng stored procedure vì ngưi gi ch gi mt câu lnh đơn và SQL Server ch kim tra mt ln sau đó to ra mt execute plan và thc thi. Nu stored procedure đưc gi nhiu ln thì execute plan có th đưc s dng li nên s làm vic nhanh hơn. Ngoài ra cú pháp ca các câu lnh SQL đã đưc SQL Sever kim tra trưc khi save nên nó không cn kim li khi thc thi. Programming Framework : Mt khi stored procedure đưc to ra nó có th đưc s dng li. Ðiu này s làm cho vic bo trì (maintainability) d dàng hơn do vic tách ri gia business rules (tc là nhng logic th hin bên trong stored procedure) và database. Ví d nu có mt s thay đi nào đó v mt logic thì ta ch vic thay đi code bên trong stored procedure mà thôi. Nhng http://ebooks.vdcmedia.com HC SQL SERVER 2000 47 ng dng dùng stored procedure này có th s không cn phi thay đi mà vn tương thích vi business rule mi. Cũng ging như các ngôn ng lp trình khác stored procedure cho phép ta đưa vào các input parameters (tham s) và tr v các output parameters đng thi nó cũng có kh năng gi các stored procedure khác. Security : Gi s chúng ta mun gii hn vic truy xut d liu trc tip ca mt user nào đó vào mt s tables, ta có th vit mt stored procedure đ truy xut d liu và ch cho phép user đó đưc s dng stored procedure đã vit sn mà thôi ch không th "đng" đn các tables đó mt cách trc tip. Ngoài ra stored procedure có th đưc encrypt (mã hóa) đ tăng cưng tính bo mt. Các Loi Stored Procedure Stored procedure có th đưc chia thành 5 nhóm như sau: 1. System Stored Prcedure : Là nhng stored procedure cha trong Master database và thưng bt đu bng tip đu ng sp_ . Các stored procedure này thuc loi built-in và ch yu dùng trong vic qun lý database (administration) và security. Ví d bn có th kim tra tt c các processes đang đưc s dng bi user DomainName\Administrators bn có th dùng sp_who @loginame='DomainName\Administrators' . Có hàng trăm system stored procedure trong SQL Server. Bn có th xem chi tit trong SQL Server Books Online. 2. Local Stored Procedure : Ðây là loi thưng dùng nht. Chúng đưc cha trong user database và thưng đưc vit đ thc hin mt công vic nào đó. Thông thưng ngưi ta nói đn stored procedure là nói đn loi này. Local stored procedure thưng đưc vit bi DBA hoc programmer. Chúng ta s bàn v cách to stored prcedure loi này trong phn k tip. 3. Temporary Stored Procedure : Là nhng stored procedure tương t như local stored procedure nhưng ch tn ti cho đn khi connection đã to ra chúng b đóng li hoc SQL Server shutdown. Các stored procedure này đưc to ra trên TempDB ca SQL Server nên chúng s b delete khi connection to ra chúng b ct đt hay khi SQL Server down. Temporary stored procedure đưc chia làm 3 loi : local (bt đu bng #), global (bt đu bng ##) và stored procedure đưc to ra trc tip trên TempDB. Loi local ch đưc s dng bi connection đã to ra chúng và b xóa khi disconnect, còn loi global có th đưc s dng bi bt k connection nào. Permission cho loi global là dành cho mi ngưi http://ebooks.vdcmedia.com HC SQL SERVER 2000 48 (public) và không th thay đi. Loi stored procedure đưc to trc tip trên TempDB khác vi 2 loi trên ch ta có th set permission, chúng tn ti k c sau khi connection to ra chúng b ct đt và ch bin mt khi SQL Server shut down. 4. Extended Stored Procedure : Ðây là mt loi stored procedure s dng mt chương trình ngoi vi (external program) vn đưc compiled thành mt DLL đ m rng chc năng hot đng ca SQL Server. Loi này thưng bt đu bng tip đu ng xp_ .Ví d, xp_sendmail dùng đ gi mail cho mt ngưi nào đó hay xp_cmdshell dùng đ chy mt DOS command... Ví d xp_cmdshell 'dir c:\' . Nhiu loi extend stored procedure đưc xem như system stored procedure và ngưc li. 5. Remote Stored Procedure : Nhng stored procedure gi stored procedure server khác. Vit Stored Procedure Tên và nhng thông tin v Stored Procedure khi đưc to ra s cha trong SysObjects table còn phn text ca nó cha trong SysComments table. Vì Stored Procedure cũng đưc xem như mt object nên ta cũng có th dùng các lnh như CREATE, ALTER, DROP đ to mi, thay đi hay xóa b mt stored procedure. Chúng ta hãy xem mt ví d sau v Stored Procedure: Ð to mt stored procedure bn có th dùng Enterprise Manager click lên trên Stored Procedure -> New Stored Procedure.... Trong ví d này ta s to ra mt stored procedure đ insert mt new order vào Orders table trong Practice DB. Ð insert mt order vào database ta cn đưa vào mt s input như OrderID, ProductName (order món hàng nào) và CustomerName (ai order). Sau đó ta tr v kt qu cho bit vic insert đó có thành công hay không. Result = 0 là insert thành công. CREATE PROCEDURE AddNewOrder @OrderID smallint, @ProductName varchar(50), @CustomerName varchar(50), @Result smallint=1 Output AS DECLARE @CustomerID smallint BEGIN TRANSACTION If not Exists(SELECT CustomerID FROM Customers WHERE [Name]=@CustomerName) --This is a new customer. Insert this customer to the database BEGIN SET @CustomerID= (SELECT Max(CustomerID) FROM Customers) SET @CustomerID=@CustomerID+1 http://ebooks.vdcmedia.com HC SQL SERVER 2000 49 INSERT INTO Customers VALUES(@CustomerID,@CustomerName) If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID) --This order exists and could not be added any more so Roll back BEGIN SELECT @Result=1 ROLLBACK TRANSACTION END Else --This is a new order insert it now BEGIN INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID) SELECT @Result=0 COMMIT TRANSACTION END END Else --The customer exists in DB go ahead and insert the order BEGIN If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID) --This order exists and could not be added any more so Roll back BEGIN SELECT @Result=1 ROLLBACK TRANSACTION END Else --This is a new order insert it now BEGIN INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID) SELECT @Result=0 COMMIT TRANSACTION END END Print @Result Return Ð to ra mt stored procedure ta dùng lnh CREATE PROCEDURE theo sau là tên ca nó (nu là temporary stored procedure thì thêm du # trưc tên ca procedure. Nu mun encrypt thì dùng WITH ENCRYPTION trưc ch AS) và các input hoc ouput parameters. Nu là output thì thêm keyword OUTPUT đng sau parameter. Ta có th cho giá tr default cùng lúc vi khai báo data type ca parameter. K t sau ch AS là phn body ca stored procedure. Trong ví d trên trưc ht ta khai báo mt bin @CustomerID sau đó bt đu mt transaction bng BEGIN TRANSACTION (toàn b công vic insert này đưc thc hin trong mt Transaction nghĩa là hoc là insert thành công hoc là http://ebooks.vdcmedia.com HC SQL SERVER 2000 50 không làm gì c- all or nothing). Trưc ht ta kim tra xem ngưi khách hàng là ngưi mi hay cũ. Nu là ngưi mi thì ta "tin tay" insert vào Customers table luôn còn nu không thì ch insert vào Orders table mà thôi. Nu là ngưi customer mi ta ly CustomerID ln nht t Customers table b vào bin @CustomerID và sau đó tăng lên mt đơn v dùng cho vic Insert Customer dòng k tip. Sau khi insert ngưi customer mi ta tip tc công vic bng vic kim tra xem Order mun insert có tn ti chưa (vì nu order đã tn ti thì khi insert SQL Server s báo li do OrderID là Primary key). Nu như order trên vì lý do nào đó đã có trong DB thì ta roll back và tr kt qu =1 còn nu không thì ta insert mt order mi vào và commit transaction vi kt qu tr v =0. Tương t như vy nu ngưi customer đã tn ti (sau ch else đu tiên) thì ta ch vic insert order ging như trên. Trong mi trưng hp k trên ta đu in ra kt qu và return. Ví d trên đây ch mang tính hc hi còn trên thc t database có th phc tp hơn nhiu nên vic vit stored procedure đòi hi kin thc vng chc v SQL và k năng v programming. Mun hiu rõ hơn v bài hc này bn cn làm bài tp s 2. Tóm li trong bài này chúng ta đã tìm hiu mt s k thut import và export data . Ðng thi bit qua các cách select data t các database system khác dùng distributed query. Nhưng quan trng nht và thưng dùng nht là các stored procedures. Bn cn hiu rõ vai trò ca stored procedure và bit cách to ra chúng. Vì kin thc v database nói chung và SQL Server nói riêng khá rng nên trong khuôn kh mt bài hc chúng tôi không th trình bày cn k tng chi tit và đôi khi có hơi dn ép cho nên bn cn đc đi đc li nhiu ln đ nm đưc ý chính và phi xem thêm sách (nu không có sách thì phi xem thêm SQL Books Online). Sau bài hc này các bn cn làm bài tp s 2 đ h thng hóa li kin thc đã hc. Khi làm bài tp nên làm theo th t và tuân th theo các yêu cu ca bài tp đt ra. Không nên b qua bưc nào. http://ebooks.vdcmedia.com HC SQL SERVER 2000 51 Triggers And Views Trong bài này chúng ta s tìm hiu ng dng ca mt loi stored procedure đc bit gi là Triggers và dùng Views đ th hin data trong mt hay nhiu table như th nào. Triggers Trigger là mt loi stored procedure đc bit đưc execute (thc thi) mt cách t đng khi có mt data modification event xy ra như Update, Insert hay Delete. Trigger đưc dùng đ đm bo Data Integrity hay thc hin các business rules nào đó. Khi nào ta cn s dng Trigger: Ta ch s dng trigger khi mà các bin pháp bo đm data intergrity khác như Constraints không th tha mãn yêu cu ca ng dng. Nên nh Constraint thuc loi Declarative Data Integrity cho nên s kim tra data trưc khi cho phép nhp vào table trong khi Trigger thuc loi Procedural Data Integrity nên vic insert, update, delete đã xy ra ri mi kích hot trigger. Chính vì vy mà ta cn cân nhc trưc khi quyt đnh dùng loi nào trong vic đm bo Data Integrity. Khi mt database đưc denormalized (ngưc li quá trình normalization, là mt quá trình thit k database schema sao cho database cha data không tha không thiu) s có mt s data tha (redundant ) đưc cha trong nhiu tables. Nghĩa là s có mt s data đưc cha cùng mt lúc hai hay nhiu nơi khác nhau. Khi đó đ đm bo tính chính xác thì khi data đưc update mt table này thì cũng phi đưc update mt cách t đng các table còn li bng cách dùng Trigger. Ví d: ta có table Item trong đó có field Barcode dùng đ xác đnh mt mt hàng nào đó. Item table có vai trò như mt cun catalog cha nhng thông tin cn thit mô t tng mt hàng. Ta có mt table khác là Stock dùng đ phn ánh món hàng có thc trong kho như đưc nhp v này nào đưc cung cp bi đi lý nào, s lưng http://ebooks.vdcmedia.com HC SQL SERVER 2000 52 bao nhiêu (tc là nhng thông tin v món hàng mà không th cha trong Item table đưc)...table này cũng có field Barcode đ xác đnh món hàng trong kho. Như vy thông tin v Barcode đưc cha hai nơi khác nhau do đó ta cn dùng trigger đ đm bo là Barcode hai nơi luôn đưc synchonize (đng b). Ðôi khi ta có nhu cu thay đi dây chuyn (cascade) ta có th dùng Trigger đ bo đm chuyn đó. Nghĩa là khi có s thay đi nào đó table này thì mt s table khác cũng đưc thay đi theo đ đm bo tính chính xác. Ví d như khi mt món hàng đưc bán đi thì s lưng hàng trong table Item gim đi mt món đng thi tng s hàng trong kho (Stock table) cũng phi gim theo mt cách t đng. Như vy ta có th to mt trigger trên Item table đ mi khi mt món đưc bán đi thì trigger s đưc kích hot và gim tng s hàng trong Stock table. Ðc đim ca Trigger: Mt trigger có th làm nhiu công vic (actions) khác nhau và có th đưc kích hot bi nhiu hơn mt event. Ví d ta có th vit mt trigger đưc kích hot bi bt k event nào như Update, Insert hay Delete và bên trong trigger ta s vit code đ gii quyt cho tng trưng hp. Trigger không th đưc to ra trên temporary hay system table. Trigger ch có th đưc kích hot mt cách t đng bi mt trong các event Insert, Update, Delete mà không th chy manually đưc. Có th áp dng trigger cho View. Khi mt trigger đưc kích hot thì data mi va đưc insert hay mi va đưc thay đi s đưc cha trong Inserted table còn data mi va đưc delete đưc cha trong Deleted table. Ðây là 2 table tm ch cha trên memory và ch có giá tr bên trong trigger mà thôi (nghĩa là ch nhìn thy và đưc query trong trigger mà thôi). Ta có th dùng thông tin trong 2 table này đ so sánh data cũ và mi hoc kim tra xem data mi va thay đi có hp l trưc khi commit hay roll back. (Xem thêm ví d bên dưi) Có 2 loi triggers (class) : INSTEAD OF và AFTER. Loi INSTEAD OF s b qua (bybass) action đã kích hot trigger mà thay vào đó s thc hin các dòng lnh SQL bên trong Trigger. Ví d ta có mt http://ebooks.vdcmedia.com HC SQL SERVER 2000 53 Update trigger trên mt table vi câu INSTEAD OF thì khi table đưc update thay vì update SQL Server s thc hin các lnh đã đưc vit sn bên trong trigger. Ngưc li loi AFTER (loi default tương đương vi keyword FOR) s thc hin các câu lnh bên trong trigger sau khi các action to nên trigger đã xy ra ri. To Mt Trigger Như Th Nào? Cú pháp căn bn đ to ra mt trigger có dng như sau: CREATE TRIGGER trigger_name ON table_name or view_name FOR trigger_class and trigger_type(s) AS Transact-SQL statements Như vy khi to ra mt trigger ta phi ch rõ là to ra trigger trên table nào và đưc trigger khi nào (insert, update hay delete. Sau ch AS là các câu lnh SQL x lý công vic. Ta hãy nghiên cu mt ng dng thc tin sau. Gi s ta vit mt application cho phép user có th Insert, Update và Delete nhng thông tin nm trong database. User này thưng là nhng ngưi không thông tho lm v computer mà chúng tôi thưng gi đùa là "bà tám". Vào mt ngày đp tri, "bà tám" mt mày tái xanh đn cu cu ta vì đã l tay "delete" nhng thông tin khá quan trng và hy vng ta có th phc hi d liu dùm. Nu chúng ta không phòng xa trưc khi vit application thì coi như cũng vô phương cu cha vì data đã hoàn toàn b delete. Nhưng nu bn là mt "guru" bn s gt gù "chuyn này khó lm!" nhưng sau đó bn ch tn vài phút đng h đ rollback. Mun làm đưc chuyn này chúng ta phi dùng mt "chiêu" gi là Audit (kim tra hay giám sát). Tc là ngoài các table chính ta s thêm các table ph gi là Audit tables. Bt k hot đng nào đng chm vào mt s table quan trng trong database ta đu ghi nhn vào trong Audit table. Ví d khi user update hay delete mt record trong table nào đó thì trưc khi update hay delete ta s âm thm di chuyn record đó sang Audit table ri mi update hay delete table chính. Như vy nu có chuyn gì xy ra ta có th d dàng rollback (tr record v ch cũ). http://ebooks.vdcmedia.com HC SQL SERVER 2000 54 Ví d: Ta có table Orders trong PracticeDB. Ð audit các hot đng din ra trên table này ta to ra mt audit table vi tên Aud_Orders vi các column ging y ht vi Orders table. Ngoài ra ta thêm vào 2 columns : Audit_Type : vi các giá tr có th là 'I','U','D' đ ghi nhn record đưc Insert, Update hay Delete Date_Time_Stamp : Data Type thuc loi DateTime dùng đ ghi nhn thi đim xy ra s thay đi, có vai trò như mt con du. (Nu trong môi trưng nhiu user thì ta thêm mt column UserID đ ghi nhn user nào thay đi). Sau đó ta s to ra 3 trigger dùng cho vic audit như sau: --Insert Trigger CREATE TRIGGER [AuditInsertOrders] ON [dbo].[Orders] FOR Insert AS insert into aud_orders select *,'I',getdate() From inserted --Update Trigger CREATE TRIGGER [AuditUpdateOrders] ON [dbo].[Orders] for UPDATE AS insert into aud_orders select *,'U',Getdate() from deleted --Delete Trigger CREATE TRIGGER [AuditDeleteOrders] ON [dbo].[Orders] FOR DELETE AS insert into aud_orders select *,'D',getdate() From deleted Trong ví d trên khi user insert mt record thì record mi va đưc insert s nm trong inserted table như đã trình bày phn trên. Do đó ta s select tt c các column trong inserted table cng thêm Audit Type "I" và dùng hàm GetDate() trong SQL Server đ ly system date time dùng cho Date_Time_Stamp column, sau đó insert vào Aud_Orders table. Tương t vi trưng hp Update và Delete, record đã đưc update hay delete nm trong deleted table. Như vy tr li trưng hp thí d trên nu "bà tám" yêu cu ta có th vào tìm kim trong audit table đ phc hi li record. Ngoài ra ta có th dùng table này đ tìm ra th phm đã xoá hay sa cha data khi cn thit. http://ebooks.vdcmedia.com HC SQL SERVER 2000 55 Ð to ra hay xem mt trigger bng Enterprise Manager bn làm như sau: Right-Click lên table mà bn mun to trigger->All Tasks-> Manage Triggers. Lưu ý: Ðôi Khi ta ch mun trigger thc s hot đng khi mt hay vài column nào đó đưc Update ch không phi bt k column nào. Khi đó ta có th dùng hàm Update(Column_Name) đ kim tra xem column nào đó có b update hay không. Ví d: To mt trigger cho Customer table. Bên trong Trigger (sau ch AS) ta có th kim tra xem nu column First_Name hay Last_Name b thay đi thì mi hành đng nu không thì không làm gì c IF UPDATE (first_name) OR UPDATE (Last_Name) BEGIN Do some conditional processing when either of these columns are updated. END Nu mun kim tra nhiu columns ta có th dùng hàm khác là Columns_Updated() . Xin xem thêm trong SQL Server Books Online đ bit thêm chi tit v cách s dng. Views Ðnh nghĩa mt cách đơn gin thì view trong SQL Server tương t như Query trong Access database. View có th đưc xem như mt table o mà data ca nó đưc select t mt stored query. Ði vi programmer thì view không khác chi so vi table và có th đt v trí ca table trong các câu lnh SQL. Ðc đim ca View là ta có th join data t nhiu table và tr v mt recordset đơn. Ngoài ra ta có th "xào nu" data (manipulate data) trưc khi tr v cho user bng cách dùng mt s logic checking như (if, case...). Ví d: Create View OrderReport As Select OrderID, (case when [Name] is null then 'New Customer' else [Name] end )As CustomerName, ProductName, DateProcessed From Customers Right Outer Join Orders on Customers.CustomerID=Orders.CustomerID http://ebooks.vdcmedia.com HC SQL SERVER 2000 56 Trong ví d trên ta ch yu tr v data t Orders table trong PracticeDB nhưng thay vì display CustomerID vn không có ý nhiu ý nghĩa đi vi user ta s display tên ca customer bng cách join vi Customer table. Nu Customer Name là Null nghĩa là tên ca customer đã đt order không tn ti trong system. Thay vì đ Null ta s display "New Customer" đ d nhìn hơn cho user. Nói chung câu lnh SQL trong View có th t rt đơn gin như select toàn b data t mt table cho đn rt phc tp vi nhiu tính năng programming ca TSQL. View Thưng Ðưc Dùng Vào Vic Gì? View thưng đưc s dùng vào mt s công vic sau: Tp trung vào mt s data nht đnh : ta thưng dùng view đ select mt s data mà user quan tâm hay chu trách nhim và loi b nhng data không cn thit. Ví d: Gi s trong table ta có column "Deleted" vi giá tr là True hay False đ đánh du mt record b delete hay không. Vic này đôi khi đưc dùng cho vic Audit. Nghĩa là trong mt ng dng nào đó khi user delete mt record nào đó, thay vì ta physically delete record ta ch logically delete bng cách đánh du record là đã đưc "Deleted" đ đ phòng user yêu cu roll back. Như vy ch yu ta ch quan tâm đn data chưa delete còn data đã đưc đánh du deleted ch đưc đ ý khi nào cn roll back hay audit mà thôi. Trong trưng hp này ta có th to ra mt view select data mà Deleted=False và làm vic ch yu trên view thay vì toàn b table. Ðơn gin hóa vic x lý data: Ðôi khi ta có nhng query rt phc tp và s dng thưng xuyên ta có th chuyn nó thành View và đi x nó như mt table, như vy s làm cho vic x lý data d dàng hơn. Customize data: Ta có th dùng view đ làm cho users thy data t nhng góc đ khác nhau mc dù h đang dùng mt ngun data ging nhau. Ví d: Ta có th to ra views trong đó nhng thông tin v customer đưc th hin khác nhau tùy login ID là normal user hay manager. Export và Import data: Ðôi khi ta mun export data t SQL Server sang các ng dng khác như Excel chng hn ta có th dùng view đ join nhiu table và export dùng bcp. http://ebooks.vdcmedia.com HC SQL SERVER 2000 57 Khi s dng view ta có th select,insert, update, delete data bình thưng như vi mt table. Ví d: Select * From OrderReport Where DateProcessed <'2003-01-01' Lưu ý: Trong Enterprise Edition (và Developer Edition) ta có th to Index cho View như cho table. Index s đưc bàn đn trong các bài sau. Mun hiu rõ hơn v bài hc này bn cn làm bài tp s 3. Như vy trong bài này chúng ta đã tìm hiu Trigger, View trong SQL Server và mt s ng dng ca nó. Nói chung view thưng đưc dùng đ tru tưng hóa (abstract) hay lc raw data (data thô) trưc khi tr v cho user trong khi trigger thưng đưc dùng đ bo đm tính integrity ca database. Exercise 1: Advanced Query Please follow those steps to practise: 1. Create a new database called PracticeDB (using Enterprise Manager (EP) or Query Analyser (QA)) 2. Create 2 tables and insert data as follows (use EP or QA) : Customers CustomerID Name (Int) (nVarChar(50)) 1 John Nguyen 2 Bin Laden 3 Bill Clinton 4 Thomas Hardy 5 Ana Tran 6 Bob Carr Orders OrderID CustomerID ProductName (Int) (Int) (nvarchar(50)) 1 2 Nuclear Bomb 2 3 Missile 3 2 Jet-1080 4 1 Beers 5 4 Asian Food 6 7 Wine http://ebooks.vdcmedia.com DateProcessed (datetime) 2002-12-01 2000-03-02 2003-08-03 2001-05-12 2002-10-04 2002-03-08 HC SQL SERVER 2000 58 7 8 Milk 2002-05-02 3. Query data using Select statement with the following Join (use QA) a. Inner Join b. Left Outter Join c. Right Outer Join d. Full Outer Join e. Cross Join The result of the query must be displayed in the following format OrderID CustomerName ProductName DateProcessed ( Hints: Based on the data supplied and your knowledge about various kind of joins guess the results then after each select compare it with your guessing to check if what you understand is correct. You also use As keyword or alias in the queries) 4. Using Select * Into...From statement to create a new table called Processed Orders and populate the new table with the data selecting from Orders table Where DateProcessed is earlier than 2002-10-05. (use QA) 5. Using Insert Into...Select statement to get the top 1 record from Orders table and insert into the ProcessedOrders (use QA) 6. Delete a record from ProcessedOrders where the date processed is 2002-10-04. (use QA) 7. Using Union to merge the two data set from Orders and ProcessedOrders into one data set. (use QA) 8. Apply Constraints (use EP or QA) a.Apply the Primary Constraint to the ID column in the tables b. Apply the Foreign Key Constraint in the Orders table. c. Apply the Check Constraint to the DateProcessed column so that the date is within 1970-01-01 2005-01-01 and try to insert invalid data to see if SQL rejects. d. Apply Unique Constraint to the CustomerName column of Customers 9. Back up database and Restore to somewhere else (on a different server or on the same server but with different database name) (use EP or QA) 10. Truncate and Drop the ProcessedOrders table (use QA) http://ebooks.vdcmedia.com HC SQL SERVER 2000 Exercise 2: Manipulate 59 Data and Stored Procedure Please follow those steps to practise: 9. Use bcp to export all data from Orders table of PracticeDB to c:\Orders.txt (or to c:\Orders.csv) 10. Change some data in the c:\Orders.txt and save. Then import to Orders table from the text file using bcp 11. Import Orders.txt to Orders table using BULK INSERT 12. Create a Linked Server LinkedPracticeDB which link to an Access database PracticeDB.mdb (firstly you have to create an Access database similar to PracticeDB in SQL Server and input some data). Then do a select data using fourpart name and OPENQUERY 13. Using ad hoc computer name with OPENROWSET and OPENDATASOURCE functions to select data from PracticeDB.mdb 14. Create the following Cursor DECLARE @au_lname varchar(40), @au_fname varchar(20) DECLARE Employee_Cursor CURSOR FOR SELECT LastName, FirstName FROM Northwind.dbo.Employees OPEN Employee_Cursor FETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fname WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Author:' + @au_fname + ' ' + @au_lname FETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fname END CLOSE Employee_Cursor DEALLOCATE Employee_Cursor 15. Create the following stored procedure and try to execute with some values CREATE PROCEDURE AddNewOrder @OrderID smallint, @ProductName varchar(50), @CustomerName varchar(50), @Result smallint=1 Output AS DECLARE @CustomerID smallint BEGIN TRANSACTION If not Exists(SELECT CustomerID FROM Customers WHERE [Name]=@CustomerName) BEGIN http://ebooks.vdcmedia.com HC SQL SERVER 2000 SET @CustomerID= (SELECT Max(CustomerID) FROM Customers) SET @CustomerID=@CustomerID+1 INSERT INTO Customers VALUES(@CustomerID,@CustomerName) If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID) BEGIN SELECT @Result=1 ROLLBACK TRANSACTION END Else BEGIN INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID) SELECT @Result=0 COMMIT TRANSACTION END END Else BEGIN If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID) BEGIN SELECT @Result=1 ROLLBACK TRANSACTION END Else BEGIN INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID) SELECT @Result=0 COMMIT TRANSACTION END END Print @Result Return 9. Using VB 6 or VB.NET to execute the AddNewOrder stored procedure 10. Using xp_cmdshell extended stored procedure to send a message (xp_cmdshell net send Hello) http://ebooks.vdcmedia.com 60 HC SQL SERVER 2000 61 Exercise 3: Triggers And Views Please follow those steps to practise: 16. Create 3 triggers to audit the changes to the Orders table. Tips: Create an audit table aud_Orders with the same colums as in the Orders table and 2 more colums AuditType(with values either I,U,D) and DateTimeStamp(which will record the date time stamp of changes) Create Update Triggers (Similar to Insert,Delete) : when a record in the Orders table is updated the trigger will move the old record to the audit table(record the date time and mark it as U) 17. Create a view that shows all the orders with the following colums: OrderID,CustomerName,ProductName,DateProcessed,Status Business rules: If CustomerName is a null value New Customer is returned If DateProcessed is later than current date return Pending, if DateProcessed is ealier return History in Status colum. Tips: a. Using Case When ...Then statement in the view b. Using Getdate() function to get the current date time http://ebooks.vdcmedia.com ...
View Full Document