Chng 1

Gii Thiu SQL Server 2008

Kt thc chng ny cc bn c th :
Trnh by c cc khi nim c bn SQL Server 2008 M t c cc thnh phn v kin trc SQL Server 2008. Thc hnh c cch to c s d liu, to bng, to kt ni gia cc bng Thc hnh c sao lu v phc hi c s d liu.

1.1 Tng quan v SQL Server 2008

Trong mt th gii d liu ngy nay, d liu v cc h thng qun l d liu cn phi lun lun c bo m v trng thi c sn. SQL Server 2008 cho php cc nh pht trin gim c s phc tp ca c s h tng trong khi vn bo m cung cp mt nn tng d liu doanh nghip c kh nng bo mt, kh nng m rng v qun l tt hn, cng vi thi gian cht ca ng dng gim.
Nhng im mi ca SQL server 2008:

Nn tng cho cc nhim v then cht - SQL Server 2008 cho php cc t chc c th chy hu ht cc ng dng phc tp ca h trn mt nn tng an ton, tin cy v c kh nng m rng. Bn cnh cn gim c s phc tp trong vic qun l c s h tng d liu. SQL Server 2008 cung cp mt nn tng tin cy v an ton bng cch bo m nhng thng tin c gi tr trong cc ng dng ang tn ti v nng cao kh nng sn c ca d liu. SQL Server 2008 gii thiu mt c ch qun l cch tn da trn chnh sch, c ch ny cho php cc chnh sch c th c nh ngha qun tr t ng cho cc thc th my ch trn mt hoc nhiu my ch. Thm vo , SQL Server 2008 cho php thi hnh truy vn d bo vi mt nn tng ti u.

S pht trin ng - SQL Server 2008 cng vi .NET Framework gim c s phc tp trong vic pht trin cc ng dng mi. ADO.NET Entity Framework cho

Trang 1

php cc chuyn gia pht trin phn mm c th nng cao nng sut bng lm vic vi cc thc th d liu logic p ng c cc yu cu ca doanh nghip thay v lp trnh trc tip vi cc bng v ct. Cc m rng ca ngn ng truy vn tch hp (LINQ) mi trong .NET Framework cch mng ha cch cc chuyn gia pht trin truy vn d liu bng vic m rng Visual C# v Visual Basic .NET h tr c php truy vn ging SQL vn c. H tr cho cc h thng kt ni cho php chuyn gia pht trin xy dng cc ng dng cho php ngi dng mang d liu cng vi ng dng ny vo cc thit b v sau ng b d liu ca chng vi my ch trung tm. D liu quan h m rng - SQL Server 2008 cho php cc chuyn gia pht trin khai thc trit v qun l bt k kiu d liu no t cc kiu d liu truyn thng n d liu khng gian a l mi.

Thng tin trong ton b doanh nghip - SQL Server 2008 cung cp mt c s
h tng c th m rng, cho php qun l cc bo co, phn tch vi bt k kch thc v s phc tp no, bn cnh n cho php ngi dng d dng hn trong vic truy cp thng tin thng qua s tch hp su hn vi Microsoft Office. iu ny cho php CNTT a c thng tin ca doanh nghip rng khp trong t chc. SQL Server 2008 to nhng bc i tuyt vi trong vic lu tr d liu, cho php ngi dng hp nht cc trung tm d liu vo mt ni lu tr d liu tp trung ca ton doanh nghip.

Trang 2

Hnh 1.1. Ton cnh nn tng d liu ca Microsoft Cc bc ci t SQL Server 2008 Bc 1: Chy file setup.exe ci t, chn Installation -> New SQL Server stand-alone ...

Hnh 1.2. Giao din SQL Server Installation Center

Trang 3

Bc 2: Chn Ok -> Next .

Hnh 1.3. Giao din Setup Support Files Bc 3: chn kiu ci t mi

Trang 4

Hnh 1.4. Giao din Installation Type Bc 4: Nhp product key

Trang 5

Hnh 1.5. Giao din Product Key Bc 5: Sau khi ng License Terms, chn cc thnh phn ci t

Trang 6

Hnh 1.6. Giao din Feature Selection Bc 6: Thit lp ci t chn Default instance

Trang 7

Hnh 1.7. Giao din Instance Configuration Bc 7: Cu hnh server

Trang 8

Hnh 1.8. Giao din Server Configuration Bc 8: Cu hnh d liu nh sau chn Window Authentication v Add current User

Trang 9

Hnh 1.9. Giao din Database Engine Configuration Bc 9: Cu hnh analysis services Add Current User Bc 10: Cu hnh report chn option nh hnh nhn Next, Next Cho n khi hon tt

Trang 10

Hnh 1.10. Giao din Reporting Services Configuration

1.2 SQL Server Management Studio

M SQL Server Management Studio ta lm nh sau: Vo start -> chn program -> chn Microsoft SQL Server 2008 -> chn SQL Server Management Studio

Trang 11

Hnh 1.11. Kt ni vo SQL Server Ch nhng thnh phn trn hp thoi sau: Server Type: Nh v d ca cun sch ny, cho php server type l Database Engine. Cc ty chn khc l kiu d liu khc nhau ca servers n s hin th kt ni. Server Name: Hp combo th 2 cha 1 danh sch ca SQL Server ci t m chn. Trong hp thoi hnh 12, bn s thy tn ca my tnh c ci t trn local. Nu bn m hp Server name bn c th tm kim nhiu server local hoc network connection bng cch chn <Browse for more...>. Authentication: Combobox cui cng xc nh cc loi hnh kt ni bn mun s dng.Trong gio trnh ny chng ta kt ni n SQL Server s dng Windowns Authentication. Nu bn ci t SQL Server vi ch hn hp(mix mode), th bn c th thay i chn la SQL Server authentication, th n s m hai hp thoi v cho php nhp username v password. Sau khi nhn nt Connect s xut hin mn hnh sau:

Trang 12

Hnh 1.12. SQL Server Management Studio To c s d liu (database) Chn database -> Click phi -> Chn New Database

Hnh 1.13. Hp thoi Object Explorer Trong hp thoi New Database t tn cho database name -> Chn OK

Trang 13

Hnh 1.14. Giao din New Database To bng (table) Vo database qun l bn hng chn table. Sau click phi ln table -> Chn New Table

Trang 14

Hnh 1.15. Khi chn New Table s xut hin bn phi mn hnh bn di Sau ta nhp Column Name, Data Type Nhn Enter nhp ct kt tip.

Hnh 1.16.

Trang 15

Lu table trn thanh Standard toolbar -> chn Save

Hnh 1.17. To quan h kt ni gia cc bng (relatetionship) 1. To kha chnh (Primary key) cho table trong SQL Server Management Studio, to ct v kiu d liu. Sau trn thanh toolbar, chn nt Set Primary Key Bn cng c th click phi ln column chn Set Primary Key. .

Hnh 1.18.

2. To kha ngoi (Foreign key) trong ca s thit k table. Foreign c dng lin kt cc table li vi nhau. Cn lu khi to foreign key l tn ct, kiu d liu ging tn ct ca kha chnh m table n i din. V d sau MaDMSP lm foreign key

Trang 16

Hnh 1.19.

3.

To s (Diagrams)

Diagrams l 1 ca s hin th mi quan h gia cc table ca 1 database. To diagram ta thc hin nh sau: Trong ca s Object Explorer chn tn database cn to -> Click phi vo Database Diagrams -> Chn New Database Diagram

Hnh 1.20.

Trang 17

Sau khi chn New Database Diagram s xut hin hp thoi Add cc table, sau khi add xong chn Close.

Hnh 1.21. thit lp mi quan h gia cc table ta chn ct d liu ca ct lm kha chnh trong bng cha (parent table) v ko n n kha ngoi trong bng con (child table)

Hnh 1.22. Sau khi ko mi quan h cho 2 table s xut hin hp thoi nh hnh 1.21.

Trang 18

Hnh 1.23. Khi ta chn OK gia 2 table s xut hin mt kt ni gia 2 table

Hnh 1.24. Trong cng mt cch, bn c th to mi quan h khc. Khi bn hon tt, bn c th lu v ng diagram.

4. Back up v Restore d liu 4.1. Back up Click phi vo database cn back up -> Chn Tasks -> Chn Back up Thc hin cc thao tc theo th t cc hnh bn di

Trang 19

Hnh 1.25.

Trang 20

Hnh 1.26.

Trang 21

Hnh 1.27.

Hnh 1.29.

Trang 22

Hnh 1.30.

Trang 23

Hnh 1.31.

Hnh 1.32. 4.2. Restore

Click phi vo database cn Restore -> Chn Tasks -> Chn Restore -> Database Thc hin cc thao tc theo th t cc hnh bn di

Trang 24

Hnh 1.33.

Trang 25

Hnh 1.34.

Hnh 1.35
Trang 26

Hnh 1.36

Trang 27

Hnh 1.37

Hnh 1.38

Trang 28

Hnh 1.39

Trang 29

Hnh 1.40

Trang 30

Chng 2

Truy Vn D Liu C Bn

Kt thc chng ny cc bn c th :
Trnh by c cc lnh T-SQL : bin, ifelse, casewhen, Thc hin c thao tc truy vn d liu Trnh by v vn dng c cc mnh trong truy vn Trnh by v s dng c cc hm c bn ca SQL Server

2.1 Khi nim c bn v T-SQL

Transact-SQL l ngn ng SQL m rng da trn SQL chun ca ISO (International Organization for Standardization) v ANSI (American National Standards Institute) c s dng trong SQL Server khc vi PL-SQL ((Procedural Language/Structured Query Language) dng trong Oracle.

Trong bi ny chng ta s tm hiu s qua v T-SQL. Chng c chia lm 3 nhm: 2.1.1. Data Definition Language (DDL):
y l nhng lnh dng qun l cc thuc tnh ca mt database nh nh ngha cc hng hoc ct ca mt table, hay v tr data file ca mt database..., thng c dng nh sau : Create object_Name Alter object_Name Drop object_Name

Trong object_Name c th l mt table, view, stored procedure, indexes...

Trang 31

V d:
Lnh Create sau s to ra mt table tn Importers vi 3 ct CompanyID,CompanyName,Contact USE Northwind - - s dng c s d liu Northwind CREATE TABLE Importers( CompanyID int NOT NULL, CompanyName varchar(40) NOT NULL, Contact varchar(40) NOT NULL ) Lnh Alter sau y cho php ta thay i nh ngha ca mt table nh thm(hay bt) mt ct hay mt Constraint...Trong v d ny ta s thm ct ContactTitle vo table Importers USE Northwind ALTER TABLE Importers ADD ContactTitle varchar(20) NULL Lnh Drop sau y s hon ton xa table khi database ngha l c nh ngha ca table v data bn trong table u bin mt (khc vi lnh Delete ch xa data nhng table vn tn ti). USE Northwind DROP TABLE Importers

2.1.2. Data Control Language (DCL):

y l nhng lnh qun l cc quyn truy cp ln tng object (table, view, stored procedure...). Thng c dng sau: Grant Revoke Deny

V d:

Trang 32

Lnh sau s cho php user trong Public Role c quyn Select i vi table Customer trong database Northwind (Role l mt khi nim ging nh Windows Group s c bn 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 cc user trong Public Role USE Northwind DENY SELECT ON Customers TO PUBLIC Lnh sau s xa b tc dng ca cc quyn c cho php hay t chi trc USE Northwind REVOKE SELECT ON Customers TO PUBLIC 2.1.3. Data Manipulation Language (DML): y l nhng lnh ph bin dng 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)

Trang 33

Update
USE Northwind UPDATE Territories SET TerritoryDescription = 'Downtown Seattle' WHERE TerritoryID = 98101

Delete
USE Northwind DELETE FROM Territories WHERE TerritoryID = 98101 Ch : trong lnh Delete bn c th c ch From hay khng u c. kim tra cc v d trn ta lm nh sau: Trn thanh toolbar ca mn hnh SQL Server Mangement Studio -> Chn New Query cc cu lnh nh trn. Sau y l 1 v d to table bng cu lnh T-SQL. v g

Hnh 2.1

Trang 34

C php ca T-SQL Phn ny chng ta s bn v cc thnh phn to nn c php ca T-SQL Identifiers y chnh l tn ca cc database object. N dng xc nh mt object. (Ch khi ni n Object trong SQL Server l chng ta mun cp n table, view, stored procedure, index.....V hu nh mi th trong SQL Server u c thit k theo kiu hng i tng (object-oriented)). Trong v d sau TableName, KeyName, Description l nhng identifiers CREATE TABLE TableName (KeyName INT PRIMARY KEY, Description NVARCHAR(80)) C hai loi Identifiers mt loi thng thng (Regular Identifier) v mt loi gi l Delimited Identifier, loi ny cn c du "" hay du [] ngn cch. Loi Delimited c dng i vi cc ch trng vi t kha ca SQL Server (reserved keyword) hay cc ch c khong trng. V d: SELECT * FROM [My Table] WHERE [Order] = 10 Trong v d trn ch Order trng vi keyword Order nn cn t trong du ngoc vung []. Hm (Functions) C 2 loi hm mt loi l built-in v mt loi user-defined Cc hm Built-In c chia lm 3 nhm:

Rowset Functions : Loi ny thng tr v mt object v c i x nh mt table. V d nh hm OPENQUERY s tr v mt recordset v c th ng v tr ca mt table trong cu lnh Select.

Aggregate Functions : Loi ny lm vic trn mt s gi tr v tr v mt gi tr n hay l cc gi tr tng. V d nh hm AVG s tr v gi tr trung bnh ca mt ct.

Trang 35

Scalar Functions : Loi ny lm vic trn mt gi tr n v tr v mt gi tr n. Trong loi ny li chia lm nhiu loi nh nh cc hm v ton hc, v thi gian, x l kiu d liu String....V d nh hm MONTH('2002-09-30') s tr v thng 9.

Cc hm User-Defined (c to ra bi cu lnh CREATE FUNCTION v phn body thng c gi trong cp lnh BEGIN...END) cng c chia lm cc nhm nh sau:

Scalar Functions : Loi ny cng tr v mt gi tr n bng cu lnh RETURNS. Table Functions : Loi ny tr v mt table

Ch Thch (Comments) T-SQL dng du -- nh du phn ch thch cho cu lnh n v dng /*...*/ ch thch cho mt nhm Thc Thi Cc Cu Lnh SQL
Thc thi mt cu lnh n: Mt cu lnh SQL c phn ra thnh cc thnh phn c php nh trn bi mt parser, sau SQL Optimizer (mt b phn quan trng ca SQL Server) s phn tch v tm cch thc thi (Execute Plan) ti u nht v d nh cch no nhanh v tn t ti nguyn ca my nht... v sau SQL Server Engine s thc thi v tr v kt qu. Thc Thi mt nhm lnh (Batches) Khi thc thi mt nhm lnh SQL Server s phn tch v tm bin php ti u cho cc cu lnh nh mt cu lnh n v cha execution plan c bin dch (compiled) trong b nh sau nu nhm lnh trn c gi li ln na th SQL Server khng cn bin dch m c th thc thi ngay iu ny gip cho mt batch chy nhanh hn. Lnh GO Lnh ny ch dng gi mt tn hiu cho SQL Server bit kt thc mt batch job v yu cu thc thi. N vn khng phi l mt lnh trong T-SQL.

2.2 Cch s dng bin, ton t, biu thc, iu kin

Trang 36

Bin (Variable)
SQL Server cung cp 2 loi bin trong T-SQL l: bin ton cc global bt u bng @@ v local variable (bin cc b )c bt u bng @ variable c

Global variable SELECT @@VERSION AS SQL_SERVER_VERSION_DETAILS

Mt s globle variable trong SQL: @@CONNECTIONS, @@CPU_BUSY, @@CURSOR_ROWS, @@ERROR, @@FETCH_STATUS

Local variable
USE Northwind DECLARE @EmpIDVar INT SET @EmpIDVar = 3 SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpIDVar + 1

Lu : Khi khai bo bin local variable ta dng t kha DECLARE Ton t (Operator) Ton t bao gm cc php tnh: +, -, *, /

Trang 37

V d:
DECLARE @A INT, @B INT, @KetQua fLOAT SET @A=5 SET @B=2 SET @KetQua= @A + @B PRINT N'Cng: ' + convert(nvarchar(40),@KetQua)

SET @KetQua= @A - @B PRINT N'Tr: ' + convert(nvarchar(40),@KetQua)

SET @KetQua= @A * @B PRINT N'Nhn: ' + convert(nvarchar(40),@KetQua)

SET @KetQua= @A / @B

PRINT N'Chia: ' + convert(nvarchar(40),@KetQua) Kt qu:

Cng: 7 Tr: 3 Nhn: 10 Chia: 2

Cu trc iu khin
IFELSE

C php:
IF (Biu_thc)

Trang 38

{ Cu lnh hoc nhm lnh c thc thi } ELSE { Cu lnh hoc nhm lnh c thc thi } Lu : Trong SQL nu ta mun thc thi 1 nhm lnh th nhm lnh phi nm trong t kha BEGINEND

V d:
DECLARE @CharGender Char(1), @Gender Varchar(20); SET @CharGender = 'F'; IF (@CharGender<>'F') SET @Gender='Male' ELSE SET @Gender='Female'

SELECT @Gender AS [Gii Tnh]

CASEWHEN Khi chng ta s dng nhiu If..else th c th dung Case..When thay th. C php: CASE Biu thc WHEN Gi tr 1 THEN kt qu WHEN Gi tr 2 THEN kt qu

WHEN Gi tr n THEN kt qu END

Trang 39

V d:
DECLARE @CharGender Char(1), @Gender Varchar(20); SET @CharGender = 'F'; SET @Gender = CASE @CharGender WHEN 'm' THEN 'Male' WHEN 'M' THEN 'Male' WHEN 'f' THEN 'Female' WHEN 'F' THEN 'Female' END;

SELECT @Gender AS [Gii Tnh]

Khi lnh : BEGINEND C php: BEGIN { Cu lnh hoc nhm lnh c thc thi} END Vng lp : WHILE C php: WHILE Biu thc
{

Cu lnh hoc nhm lnh c thc thi } V d: DECLARE @Number As int

Trang 40

SET @Number = 1 WHILE @Number < 5 BEGIN SELECT @Number AS Number SET @Number = @Number + 1 END

FOR Mnh For c dng ch nh chn la BROWSE hoc XML. BROWSE v XML khng lin quan trong cu trc lp.

BEARK Thot khi vng lp WHILE hoc mnh IF ELSE c lng bn trong vng lp WHILE. Cc cu lnh thc thi sau t END c thc thi.

CONTINOUS Chy li vng lp WHILE. Cc cu lnh thc thi sau t kha CONTIOUS iu c b qua.

GOTO

Trang 41

Dng GOTO nhy n tn label trong khi lnh ang c thc thi. Khng c pht biu no gia GOTO v lable c thc thi. C php: GOTO Tn_lable RETURN Chng ta c th dng RETURN bt k thi im no thot khi khi lnh, th tc. Cc pht biu sau RETURN s khng c thc thi. C php: RETURN [ integer_expression ] WAITFOR Thc thi mt khi lnh ca mt t, stored procedure hoc transaction n thi gian ch nh hoc thi gian c kch hot hoc sa mt cu lnh c ch nh hoc tr v t nht mt dng. C Php WAITFOR { DELAY <thi gian> | TIME <thi gian> }

V d
USE msdb; EXECUTE sp_add_job @job_name = 'TestJob'; BEGIN WAITFOR TIME '10:00';

Trang 42

EXECUTE sp_update_job @job_name = 'TestJob', @new_name = 'UpdatedJob'; END; GO

2.3 Truy vn d liu s dng SELECTFROM

Cu lnh SELECT... FROM dng cho php bn c th chn la cc d liu cn thit t mt hoc nhiu bng c quan h bn trong mt c s d liu. C php: SELECT <tn_column_1, tn_column_2,..., tn_column_n> FROM <tn_table> hay SELECT * FROM tn_table (*) dng ly tt c cc ct trong table V d: USE Northwind SELECT RegionID, RegionDescription FROM Region Hoc bn c th vit theo cch sau: USE Northwind SELECT * FROM Region

Trang 43

2.3.1 Truy vn d liu s dng mnh WHERE

Vi c php SELECT... FROM bn di kt hp mnh WHERE cho php bn c th lc cc dng d liu bn trong mt bng phi tha iu kin a ra trong mnh WHERE. C php: SELECT [DISTINCT ][TOP s dng[PERCENT]]<tn_column_1, tn_column_2,..., tn_column_n> FROM <tn_table> WHERE <iu khin> T kha DISTINCT : dng ch nh truy vn ch chn ra cc dng d liu khng trng lp d liu. T kha TOP : dng ch nh truy vn ch chn ra chnh xc bao nhiu dng d liu u tin. Nu c thm t kha PERCENT i km theo th truy vn ch chn ra bao nhiu phn trm mu tin u tin, lc by gi con s m bn ch nh phi nm trong phm vi t 0 n 100. Thng thng khi s dng t kha TOP th bn s kt hp mnh ORDER BY sp xp li d liu theo mt th t no . iu kin lc : l iu kin ch nh vic lc ra cc mu tin bn trong bng. thng l mt biu thc lun l. V d: USE Northwind SELECT RegionID, RegionDescription FROM Region WHERE RegionDescription='Southern' Thng duy nht,

Trang 44

2.3.2 Truy vn v sp xp d liu s dng mnh ORDER BY

Vi c php SELECT...FROM bn di kt hp mnh ORDER BY cho php bn c th ly d liu ca cc ct bn trong mt bng, sau sp xp li d liu theo th t ch nh l tng hoc gim. C php: SELECT <tn_column_1, tn_column_2,..., tn_column_n> FROM <tn_table> [ WHERE <iu khin>] ORDER BY <Tn_Column> ASC/ DESC V d: USE Northwind SELECT ProductID,ProductName,UnitPrice FROM Products WHERE UnitPrice>=34 ORDER BY UnitPrice ASC

Trang 45

2.3.3 S dng hm ca T-SQL trong truy vn d liu

Hm MAX() : Hm ny s tr v gi tr ln nht trong biu thc. N c th dng vi cc kiu d liu s, chui hay ngy thng. Max tr v gi tr ln nht trong ton b gi tr sau khi i chiu. Lu : Hm MAX b qua cc gi tr NULL. V d: USE Northwind SELECT MAX(UnitPrice) AS MaxPrice FROM Products Hm MIN() : Ngc li vi hm MAX. Hm MIN tr v gi tr nh nht trong biu thc. Hm ny c th dng vi cc trng s, chui, ngy thng. Ngoi ra hm ny b qua gi tr NULL: V d: USE Northwind SELECT MIN(UnitPrice) AS MinPrice FROM Products

Trang 46

Hm AGV() : Hm ny tr v gi tr trung bnh ca cc gi tr trong cc trng d liu c ch ra trong biu thc. Lu : Hm AVG ch c dng vi cc trng c kiu d liu l s. Ngoi ra n c kh nng loi b gi tr NULL V d: USE Northwind SELECT AVG(UnitPrice) AS AvgPrice FROM Products Hm SUM() : Hm ny tr v tng ca tt c cc gi tr ca trng d liu trong biu thc. Ngoi ra, bn c th dng ti DISTINCT vi SUM tnh tng cho cc gi tr duy nht ca trng d liu trong biu thc. Cc gi tr NULL s b b qua. Lu : SUM ch dng cho cc trng d liu l kiu s. V d: USE Northwind SELECT SUM(UnitPrice) AS [Tng Sn Phm] FROM Products Hm COUNT() : Hm COUNT c s dng m cc bn ghi c select trong chui truy vn. Hm ny c th m c cc gi tr NULL trong biu thc. Nu ta dng n vi t kha DISTINCT, COUNT m c cc gi tr duy nht. Ngoi ra n c th c dng vi cc trng s v k t. Lu : Cc bn c th dng k t * thay cho biu thc trong hm COUNT. Bng cch ny chng ta c th m c tt c cc bn ghi m khng cn quan tm n cc trng d liu. V d: USE Northwind SELECT COUNT(ProductID) FROM Products

Trang 47

Hm SQUARE() : tnh bnh phng V d: DECLARE @A INT SET @A=5 SELECT SQUARE(@A)AS [@A Bnh Phng]

Hm ROUND() : ROUND lun lun tr v mt gi tr. Nu chiu di ln hn s lng cc ch s trc du thp phn, ROUND tr v 0. Round tr v mt biu thc s c lm trn, bt k loi d liu, khi chiu di l mt s m. V d Kt qu

ROUND(748.58, -1)
ROUND(748.58, -2)

750.00 700.00 1000.00

ROUND(748.58, -3)
V d sau minh ha cch s dng Round

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3) GO

V d sau minh ha lm trn v xp x SELECT ROUND(123.4545, 2); GO

Trang 48

SELECT ROUND(123.45, -2); GO

V d sau s dng hai cu SELECT chng minh s khc bit gia lm trn v ct xn. Cu lnh u tin c kt qu lm trn. Cu lnh th hai c kt qu ct xn.

Hm CHAR() V d: USE Northwind; GO SELECT FirstName + ' ' + LastName, + CHAR(13) + [Address] + CHAR(13) + HomePhone FROM Employees WHERE EmployeeID = 1; GO

Trang 49

Hm UPPER() , LOWER() Vi c php chung bn di ca cc hm UPPER, LOWER c kt qu tr v l mt chui sau khi c chuyn i cc k t bn trong chui thnh ch in (upper), hoc ch thng (lower). V d : hm UPPER USE Northwind; GO SELECT UPPER(FirstName) + ' ' + UPPER(LastName) AS Fullname FROM Employees WHERE EmployeeID=1

V d : hm LOWER USE Northwind; GO SELECT LOWER(UPPER(FirstName)) + ' ' + LOWER(UPPER(LastName)) AS Fullname FROM Employees WHERE EmployeeID=1

Hm LEN() Vi c php n gin ca hm LEN bn di c kt qu tr v l mt s nguyn dng dng ch nh chiu di ca mt chui cha bao nhiu k t.

Trang 50

V d: USE Northwind; GO SELECT LEN(FirstName) AS [Length],FirstName FROM Employees WHERE EmployeeID=1

Hm LTRIM(), RTRIM() Vi c php chung bn di ca cc hm LTRIM, RTRIM c kt qu tr v l mt chui c ct b cc khong trng u chui (ltrim) hoc cc khong trng cui chui (rtrim). C php : LTRIM (chui) RTRIM (Chui) V d : hm LTRIM DECLARE @string_to_trim varchar(60) SET @string_to_trim = ' Five spaces are at the beginning of

this string.' SELECT 'Here is the string without the leading spaces: ' + LTRIM(@string_to_trim) GO

Trang 51

V d: hm RTRIM() DECLARE @string_to_trim varchar(60); SET @string_to_trim = 'Four spaces are after the period in this sentence. '; SELECT @string_to_trim + ' Next string.'; SELECT RTRIM(@string_to_trim) + ' Next string.'; GO

Hm LEFT(),RIGHT(),SUBSTRING
Vi c php chung bn di ca cc hm LEFT, RIGHT, SUBSTRING c kt qu tr v l mt chui con c trch ra t chui ngun. Chui con c trch ra ti v tr bt u t bn tri (left), bn phi (right) hoc ti bt k v tr no (substring) v ly ra bao nhiu k t. C php : LEFT (chui ngun , s k t ) RIGHT (chui ngun , s k t ) SUBSTRING (chui ngun ,v tr, s k t ) Trong : Chui ngun : l chui k t ngun cha cc k t mun c chn la trch ra. S k t : l mt s nguyn dng ch nh s k t bn trong chui ngun s c trch ra.

Trang 52

 V tr : l s nguyn dng ch nh ti v tr bt u trch c p dng cho hm SUBSTRING. Chui con : l chui kt qu tr v sau khi thc hin vic trch cc k t ch nh trong cc tham s trn. V d: hm LEFT SELECT LEFT('abcdefg',2) -> Kt qu : ab V d: hm RIGHT SELECT RIGHT('abcdefg',2)-> Kt qu fg V d: hm SUBSTRING SELECT SUBSTRING ('abcdefg',4,3)-> Kt qu def Hm GETDATE() : ly ngy hin hnh V d: SELECT GETDATE() AS [Ngy gi hin ti] , CONVERT (date, GETDATE()) AS [Ngy hin ti] , CONVERT (time, GETDATE()) AS [Gi hin ti]

Hm DATEPART(YY,getdate()) : ly 1 phn ( ngy , thng hoc nm, ) ca ngy V d: SELECT DATEPART(year, '12:10:30.123') AS [Nm] ,DATEPART(month, '12:10:30.123') AS [Thng] ,DATEPART(day, '12:10:30.123') AS [Ngy] ,DATEPART(dayofyear, '12:10:30.123') AS [Ngy trong nm]

Trang 53

,DATEPART(weekday, '12:10:30.123') AS [Th]

Hm DATEDIFF(X,Y,Z) : tnh khong cch gia hai ngy V d: DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722'; DECLARE @enddate datetime2 = '2007-05-07 12:10:09.3312722'; SELECT DATEDIFF(day, @startdate, @enddate) AS [S ngy]

Hm DAY() : ly ngy V d: SELECT DAY(GETDATE()) AS 'Ngy' Hm MONTH() : ly thng V d: SELECT MONTH(GETDATE()) AS 'Thng' Hm YEAR() : ly nm V d: SELECT YEAR(GETDATE()) AS 'Nm' Hm CAST() : chuyn i kiu V d: DECLARE @NgaySinh datetime, @Tuoi int SET @NgaySinh='1986-12-19'
Trang 54

SET @Tuoi = YEAR(GETDATE())- YEAR(@NgaySinh) SELECT N'Bn c: ' + CAST(@Tuoi AS varchar(10)) + N' tui'

Hm CONVERT() : chuyn i kiu c nh dng V d: DECLARE @NgaySinh datetime, @Tuoi int SET @NgaySinh='1986-12-19' SET @Tuoi = YEAR(GETDATE())- YEAR(@NgaySinh) SELECT N'Bn c: ' + CONVERT(varchar(10), @Tuoi) + N' tui'

Trang 55

Chng 3:

Truy Vn D Liu Trn Nhiu Bng

Kt thc chng ny cc bn c th :
Trnh by v thc hnh c truy vn trn nhiu bng vi Inner Join Trnh by v thc hnh c truy vn trn nhiu bng vi Outer Join Trnh by v thc hnh c truy vn trn nhiu bng vi Cross Join Trnh by v thc hnh c truy vn kt hp d liu s dng Union Trnh by v thc hnh c truy vn to bng vi SelectInto Trnh by v thc hnh c truy vn s dng mnh : Group By, Having,

Merge, Intersect...

Trong chng ny chng ta s tm hiu v cch truy vn d liu trn nhiu bng s dng cc loi mnh JOIN trong SQL Server. Vi mnh SELECT FROM kt hp mnh JOIN cho php bn lin kt hai bng c quan h vi nhau ly ra cc d liu chung. im quan trng gia nhng bng ny phi c cc ct quan h chung nhau va th t quan h khi bn ch nh gia cc bng cng s lm nh hng n kt qu ca truy vn. Bng cch s dng JOIN bn c th ly d liu t nhiu table da trn mi quan h logic gia cc table (logical relationship). C nhng loi JOIN nh sau:

Th t t tri sang phi: Inner Join, Left Outer Join, Right Outer Join, Full Outer Join

Trang 56

3.1 Truy vn d liu s dng INNER JOIN

Dng Inner Join select data t 2 hay nhiu tables trong gi tr ca cc ct c join phi xut hin c 2 tables tc l phn gch cho trn hnh v dng ch nh vic so snh gi tr trong cc ct ca cc bng l tng ng (d liu u c c hai bng). H thng s tr v cc mu tin tha iu kin quan h c hai bng. V d: SELECT TOP 10 ProductName, CategoryName FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID=C.CategoryID ORDER BY ProductName

3.2 Truy vn d liu s dng LEFT JOIN

Dng Left Outer Join select data t 2 hay nhiu tables trong tt c ct bn table th nht v khng tn ti bn table th hai s c select cng vi cc gi tr ca cc ct c inner join. S ct select c s bng vi s ct ca table th nht. Tc l phn t mu trn hnh. V d: SELECT ProductName, CategoryName FROM Products AS P LEFT JOIN Categories AS C ON P.CategoryID=C.CategoryID ORDER BY ProductName

Trang 57

3.3 Truy vn d liu s dng RIGHT OUTER JOIN

Dng Right Outer Join select data t 2 hay nhiu tables trong tt c ct bn table th hai v khng tn ti bn table th nht s c select cng vi cc gi tr ca cc ct c inner join. S ct select c s bng vi s ct ca table th hai. Tc l phn t mu trn hnh. V d: SELECT ProductName, CategoryName FROM Products AS P RIGHT JOIN Categories AS C ON P.CategoryID=C.CategoryID ORDER BY ProductName

3.4 Truy vn d liu s dng CROSS JOIN

Dng Cross Join ghp data t hai table trong s dng thu c bng vi s dng ca table th nht nhn vi s dng ca table th hai. V d: SELECT TOP 5 * FROM dbo.Products CROSS JOIN dbo.Categories Lu : trong cu lnh ny khng c keyword "On".

3.5 Truy vn d liu v kt hp d liu s dng UNION

Trang 58

Vic kt hp d liu ca hai truy vn SELECT FROM bng mnh UNION cho php bn c th to ra mt tp hp cc mu tin t cc mu tin c trong cu lnh SELECT FROM th nht v cc mu tin c trong cu lnh SELECT FROM th hai. Khc vi vic lin kt d liu bng mnh JOIN, mnh UNION thc ra ch thc hin vic thm vo cc dng d liu trong cu lnh SELECT FROM th nht vo cui cc dng d liu trong cu lnh SELECT FROM th hai. Thng thng bn s dng mnh UNION dng ni d liu t cc bng khc nhau trong c s d liu thnh mt b cc mu tin lin tc nhau. Cc ct ch nh trong hai cu lnh SELECT FROM phi c cng kiu d liu tng thch th t nh nhau, tng s cc ct phi bng nhau. Vic nh dng tiu ca cc ct tnh ton ch cn thc hin trong cu lnh truy vn u tin. Kt hp cc kt qu ca hai hay nhiu truy vn thnh mt tp kt qu duy nht m bao gm tt c cc dng thuc v tt c cc truy vn trong union. Cc hot ng UNION khc nhau t cch s dng join kt hp ct t hai table.

Trang 59

V d: CREATE TABLE UnionTest1 ( idcol int IDENTITY, col2 char(3), ); CREATE TABLE UnionTest2 ( idcol int IDENTITY, col4 char(3), );

INSERT INTO UnionTest1 VALUES ('AAA'); INSERT INTO UnionTest1 VALUES ('BBB'); INSERT INTO UnionTest1 VALUES ('CCC');

SELECT * FROM UnionTest1;

INSERT INTO UnionTest2 VALUES ('CCC'); INSERT INTO UnionTest2 VALUES ('DDD'); INSERT INTO UnionTest2 VALUES ('EEE');

Regular UNION

Trang 60

SELECT col2 FROM UnionTest1 UNION SELECT col4 FROM UnionTest2;

UNION ALL

SELECT col2 FROM UnionTest1 UNION ALL SELECT col4 FROM UnionTest2;

3.6 Truy vn to table s dng SELECT INTO

Vi c php SELECTFROM bn di kt hp mnh INTO cho php bn sao chp d liu v cu trc t kt qu ca mt truy vn cho ra mt bng d liu mi bn trong c s d liu hin hnh hoc cc bng d liu tm thi dng tnh ton cc x l phc tp. Trong trng hp nu bn mun to ra bng d liu mi th bt buc tn ca bng phi duy nht trong c s d liu.

Trang 61

Bn c th ch nh cc k t du thng (#) hoc hai k t du thng (##) pha trc tn bng c to trong cu lnh SELECT INTO dng to ra cc bng tm cc b (local) hoc cc bng tm ton cc (global). Bng tm cc b ch c s dng bi ngi tao ra n v h thng s t ng hy b bng tm cc b khi ngi to ra bng ngng ni kt vo Microsoft SQL Server. Ngc li bng tm ton cc c s dng cho nhiu ngi khc nhau v h thng t ng hy bng tm ton cc khi khng cn ngi s dng no ni kt vo Microsoft SQL Server. SELECT ... INTO to ra mt table mi trong filegroup mc nh v chn cc kt qu dng t truy vn vo table mi. C php :

Trong : Tn bng mi : l tn ca bng mi s c to lp c cu trc v d liu t truy vn. Tn bng d liu : l tn ca bng cha d liu ngun cho vic sao chp.

V d: SELECT TOP 10 [CustomerID] ,[Address],[City] ,[Country],[Phone] ,[Fax] INTO KhachHang FROM [Northwind].[dbo].[Customers] ,[Region],[PostalCode]

Trang 62

3.7 Truy vn d liu s dng mnh WITH

Xc nh mt kt qu c t tn tm thi, c bit n nh l mt bng biu chung. V d: USE Northwind WITH Emp(EmployeeID, FirstName, LastName) AS ( SELECT EmployeeID, FirstName, LastName FROM dbo.Employees )

Trang 63

SELECT EmployeeID, FirstName, LastName FROM Emp

3.8 Truy vn d liu s dng mnh ORDER BY

Bn c th sp xp kt qu theo cc k t u tin bng cch s dng mnh Order by. V d: USE Northwind SELECT FirstName, LastName, [Address] FROM dbo.Employees ORDER BY FirstName ASC

Trang 64

3.9 Truy vn d liu s dng mnh MERGE

Mt im mi trong SQL Server 2008 l mnh MERGE. Bn c th kt hp 2 hay nhiu table vi nhau. V d: USE Northwind SELECT EM.FirstName, E.LastName, E.[Address] FROM dbo.Employees E INNER MERGE JOIN dbo.Employees EM ON E.EmployeeID=EM.EmployeeID ORDER BY EM.FirstName ASC

3.10 Truy vn d liu s dng mnh GROUP BY

Vi c php SELECT FROM bn di kt hp mnh GROUP BY cho php bn c th nhm d liu ca cc dng bn trong mt bng v c php s dng cc hm thng k i km theo tnh ton cc d liu c tnh cht thng k tng hp. Thng thng, sau khi nhm d liu, bn nn sp xp li d liu hin th theo mt th t no . Do vy bn s s dng mnh ORDER BY sau mnh GROUP BY. Mnh group by dng gom nhm khi tnh ton. C php :

Trang 65

V d: USE Northwind SELECT Country, COUNT(EmployeeID) AS 'Tng' FROM dbo.Employees GROUP BY Country

3.11 Truy vn d liu s dng mnh HAVING

Vi mnh SELECT FROM bn di kt hp mnh HAVING cho php bn c th lc li d liu sau khi nhm d liu ca cc dng bn trong mt bng. Khc vi mnh WHERE dng lc cc dng d liu hin ang c bn trong bng, mnh HAVING ch c php s dng i km theo mnh GROUP BY dng lc li d liu sau khi nhm. iu ny c ngha l mnh HAVING ch c dng km vi mnh GROUP BY. Mnh HAVING dng xc nh mt iu kin tm kim cho mt nhm hoc mt tp hp. HAVING thng c s dng trong mt mnh GROUP BY. C php :

Trang 66

Trong :
Hm thng k : l tn ca cc hm thng k v cc tham s tng ng dng tnh tng (SUM), tnh gi tr thp nht (MIN), tnh gi tr cao nht (MAX), m cc mu tin (COUNT), tnh gi tr trung bnh (AVG) ca cc d liu bn trong bng. B danh : l tiu mi ca cc ct tnh ton. Cc tiu ny ch c hiu lc lc hin th d liu trong cu lnh truy vn m khng lm nh hng n cu trc bn trong ca bng. Danh sch ct nhm d liu : l danh sch tn cc ct c nhm d liu tnh ton. V d: Ly danh sch cc ha n c tr gi > 10000 v kt qu c sp xp tng dn theo tr gi SELECT OD.OrderID, SUM(UnitPrice*Quantity)AS Subtotal FROM [Order Details] OD JOIN dbo.Orders O ON OD.OrderID=O.OrderID GROUP BY OD.OrderID HAVING SUM(UnitPrice*Quantity)>10000 ORDER BY SUM(UnitPrice*Quantity) ASC

Kt qu :

Trang 67

3.12 Truy vn d liu s dng mnh INTERSECT

INTERESCT t ng loi b cc d liu trng t 2 cu lnh truy vn. V d: USE Northwind SELECT RegionID, RegionDescription FROM dbo.Region INTERSECT SELECT RegionID, RegionDescription FROM dbo.Region

Trang 68

USE Northwind SELECT OrderID FROM dbo.Orders INTERSECT SELECT OrderID FROM [Order Details]

3.13 Truy vn d liu s dng mnh EXCEPT

EXCEPT t ng loi b cc d liu trng t 2 cu lnh truy vn. V d: USE Northwind SELECT OrderID FROM dbo.Orders EXCEPT SELECT OrderID FROM [Order Details]

3.14 Truy vn d liu s dng mnh COMPUTE BY

Vi c php SELECT FROM bn di kt hp mnh COMPUTE cho php bn c th to ra dng thng k d liu bn cui kt qu truy vn. Tuy nhin nu bn s dng thm mnh COMPUTE BY tip theo th h thng s thng k d liu theo tng nhm d liu. C php :

Trang 69

Trong : Count, Min, Max, Sum, Avg : l cc hm thng k tnh ton d liu m kt qu s xut hin cui kt qu truy vn hoc tng nhm d liu. Tn ct : tn cc ct hoc biu thc c tnh ton km vi cc hm thng k ch nh trc . V d: USE Northwind SELECT * FROM Suppliers COMPUTE count(SupplierID)

USE Northwind SELECT * FROM Suppliers ORDER BY SupplierID COMPUTE count(SupplierID) BY SupplierID

3.15 Truy vn d liu s dng mnh FOR

Mnh FOR c s dng ch nh trong s chn la BROWSE hay XML. BROWSE hay XML khng lin quan trong cu trc lp. V d: USE Northwind SELECT EmployeeID, FirstName, LastName, City, Country FROM dbo.Employees ORDER BY FirstName, LastName FOR XML AUTO Kt qu :

Trang 70

Chng 4

Truy Vn D Liu Nng Cao

Kt thc chng ny cc bn c th :
Trnh by c khi nim c bn SubQuery ( truy vn con ) Thc hnh c truy vn d liu s dng SubQueries nh Table (bng) Thc hnh c truy vn d liu s dng SubQueries nh Expression (biu thc ) Thc hnh c truy vn d liu s dng mnh EXISTS v NOT EXIITS Thc hnh c truy vn d liu S dng t kha DISTINCT

Trong khi lp trnh bn trong Transaction-SQL, c i lc bn s s dng n truy vn con tnh ton d liu. Truy vn con ch l mt cu lnh truy vn chn la (SELECT) c lng vo cc cu lnh truy vn khc nhm thc hin cc truy vn tnh ton phc tp. Khi s dng n truy vn con, bn cn lu tm n mt vi yu t sau : Cn m v ng ngoc n cho cu lnh truy vn con. Bn ch c php tham chiu n tn mt ct hoc mt biu thc s tr v gi tr trong truy vn con. Kt qu ca truy vn con c th tr v l mt gi tr n l hoc mt danh sch cc gi tr. Cp lng nhau ca cc truy vn con bn trong Microsoft SQL Server l 32 mc.

4.1 S dng SubQueries nh Table

Chng ta c th s dng mt cu lnh SELECT tr v cc bn ghi m s c s dng bi cu SELECT khc. Cu lnh bao bn ngoi gi l parent query v cu lnh bn trong gi l subquery . Select <Column Name> from <table> Where
Trang 71

V d: gi s chng mun bit cc sn phm m c t hng, chng ta c th s dng cu lnh , trong v d ny chng ta s dng c s d liu Northwind.

H4.1 s dng subqueries

4.2 S dng SubQueries nh mnh

Chng ta c th s dng mt subquery thay th cho mt gi tr trong mnh thay th cho mt gi tr trong mnh SELECT, nh l mt phn ca mnh WHERE. iu ny s c ch ra v d sau y. Khi s dng cc ton t so snh vi sub query c mt s gii hn vi vic tr li s lng dng v ct ca sub-query. Cc gii hn c lit k nh bng di.

Trang 72

Mt ct Mt dng S dng =,>,< v cc ton t so snh khc. S dng ANY, ALL, IN v EXISTS

Nhiu ct S dng EXISTS

Nhiu dng

S dng EXISTS

4.3 S dng SubQueries nh biu thc

Ta c th s dng kt qu tr v ca SubQuery nh l 1 biu thc V d: Ly s lng mt hng thuc loi 1 Select distinct CategoryID , (Select count(ProductID) from Products where CategoryID = 1 group by CategoryID ) as ProductQuantity From Products where CategoryID = 1 Mt s nguyn tc ca SubQuery: Theo sao mt ton t so snh c th bao gm mt biu thc hoc ct (ngoi tr EXISTSIN trong cu lnh SELECT). Nu mnh WHERE trong cu lnh bao gm tn cc ct, s phi ph hp vi cc ct trong danh sch SubQuery. Kiu d liu ntext, image, text khng th s dng trong SubQuery. Bi v phi tr v mt gi tr n, subquery khng bao gm mnh GROUP BY v HAVING, t kha ANY hay ALL. T kha DISTINCT khng th s dng vi subquery. Mnh COMPUTE v INTO khng th c dng trong cu lnh. Mnh ORDER BY ch c th dng khi t kha TOP c nu trong cu lnh. View c to ra bi Subquery khng th c cp nht.

Trang 73

Subquery vi t kha EXISTS, theo quy c, du * thay cho tn ct, subquery c to ra nhm to s tn ti kim tra s tn ti v tr v gi tr TRUE hay FALSE thay v tr v d liu.

V d : Lit k sn phm c ProductID=1, trong subquery dng tnh tng s lng sn phm .

4. 2 Dng Subquery tnh tng s lng

Trang 74

V d : Lit k danh sch cc nh phn phi nhiu mt hng nht

H 4.3 Lit k nh phn phi

V d : Tnh tng s lng cc mt hng bn theo tng nh cung cp

H 4.4 Tng hng bn theo nh cung cp

Trang 75

4.4 S dng t kha EXISTS v NOT EXISTS

Khi mt subquery c t EXISTS, n c chc nng kim tra s tn ti. T kha EXISTS c s dng kim tra s tn ti ca cc dng tr v bi subquery. Subquery lc ny khng thc s tr li d liu, m n tr v mt gi tr TRUE hoc FALSE. Mt subquery bao gm t EXISTS c c php nh sau:

WHERE (NOT) EXISTS (Subquery)

V d: gi s chng ta mun bit ch nhng sn phm no c m 1 c t hng. Chng ta c th s dng t kha EXISTS kim tra nu thng tin v sn phm tn ti trong bng Order Details. Cu lnh v kt qu ca n c ch ra trong hnh 4.5

Trang 76

H 4.5 S dng mnh EXISTS V d: nu chng ta mun ch ra nhng sn phm loi 1 m cha bao gi t hng. Chng ta s s dng t kha NOT EXISTS truy vn.

Trang 77

H 4.6 s dng mnh NOT EXISTS

Mt subqueries c th lng nhiu subqueries (c th ln ti 32 mc) nhng khi s khng c hiu sut thi hnh nh mong mun.

V d: gi s rng chng ta mun tm chi tit nhng ha n m bao gm sn phm c cung cp t thnh ph London.(H4.7)

Trang 78

H 4.7 S dng nested subqueries

4.5 S dng t kha DISTINCT

T kha DISTINCT c tc dng kh cc dng trng nhau c tr v t tp kt qu ca mt chui Select. Trong trng hp cu lnh c t kha DISTINCT th tt c cc dng trng nhau cng hin th. V d, chng ta mun hin th cc ProductID khng trng nhau t bn Order Details vi Discount l 0. Cu lnh v kt qu nh trong hnh 4.5.

Trang 79

H 4.8 S dng mnh DISTINCT.

Trang 80

Chng 5

Modifying Data

Kt thc chng ny cc bn c th :
Thc hnh c thm d liu vo bng s dng lnh INSERT Thc hnh c thm d liu vo bng s dng lnh INSERTSELECT Thc hnh c thm d liu vo bng vi t kha DEFAULT Thc hnh c xa d liu trn bng liu s dng lnh DELETE Thc hnh c cp nht d liu trong bng s dng lnh UPDATE Trnh by khi nim c bn v TRANSACTION

5.1 Thm d liu s dng INSERT

y l cu lnh n gin nht thm mt dng d liu mi vo table s dng cu lnh Insert Into .. Values, vi cu lnh ny bn phi chc chn rng tt c cc gi tr thm vo phi tng ng vi cc ct trong bn cn thm d liu C php Insert Into [table_name] Values (value 1,value 2, value 3,...) V d: chng ta mun thm mt gi tr mi vo bng Region trong c s d liu Northwind , Kt qu nh trong hnh 5.1 Insert into [Region] Values (6,NewRegion)

Trang 81

H 5.1 Thm Mi

5.2 Thm d liu s dng INSERTSELECT

Vi cu lnh Insert into .. values chng ta ch thm c mt dng d liu ti mt thi im thm mi d liu. c th thm nhiu dng d liu ti cng mt thi im insert chng ta c th s dng cu lnh Insert ... select C php INSERT INTO [table_name] ( [column1], [column2], [column3], ) SELECT statement. V d: chng ta to mi mt bng Nams c hai ct NameID ,FullName v ly tt c tn ca cc nhn vin trong bng Employees thm vo bn Names, chng ta s dng cu lnh Insert Select nh hnh bn di (H 5.2)

Trang 82

H 5.2 InsertSelect

5.3 Thm d liu vi t kha DEFAULT

khng phi nhp cng mt gi tr cho nhiu ct d liu, chng ta c th gn cho mt hay nhiu ct trong bng vi mt gi tr mc nh l DEFAULT value. C php: INSERT INTO [table_name] DEFAULT VALUES V d: Chng ta to mt bng tn History, gm 3 trng ID, CreateDate, CreateName. Trong ta gn thuc tnh DEFAULT cho CreateDate

Trang 83

CREATE TABLE History ( ID int IDENTITY, CreateDate DateTime DEFAULT (GETDATE()), CreateName varchar(20) NULL )

H 5.3 Bng History Chng ta Insert vo bng va to mt dng d liu theo cch thng thng. Ta s dng hm GETDATE() ly ngy gi h thng

Trang 84

H 5.4 Insert d liu khng dng DEFAULT VALUES By gi ta thm vo mt dng d liu v s dng DEFAULT VALUES, cc bn vn c th ly gi h thng.

H 5.5 Insert d liu dng DEFAULT VALUES

Trang 85

5.4 Xa d liu s dng cu lnh DELETE

Cu lnh Delete dng xa tt c cc dng ra khi bng. C php DELETE [table_name] WHERE [column]=[value] Lu : Khi s dng lnh Delete m khng c mnh Where th SQL Server s xa ton b cc dng trong bng . V d : trong bng Employee chng ta mun xa nhng nhn vin no thnh ph LonDon th ta c th s dng cu lnh nh sau (H 5.6)

H 5.6 Xa nhn vin V d 2: xa tt c cc loi sn phm cha c sn phm no (H 5.7)

Trang 86

H 5.7 Xa loi sn phm

5.5 Cp nht d liu s dng UPDATE

Cu lnh UPDATE c s dng cp nhp/sa i d liu c trong bng C php Update [table_name] SET [column]=[new value] WHERE [column]=[value] Lu : Khi s dng lnh Update m khng c mnh Where th SQL Server s cp nht ton b cc dng trong bng c cng gi tr ca ct c update V d : nu nh bn mun cp nhp lai tt c cc sn phm c cng mt nh cung cp , bn c th thc hin nh sau (H 5.8)

Trang 87

H 5.8 Cp nhp nh cung cp V d : nu nh bn mun cp nhp li tt c cc sn phm c cng mt nh cung cp v nhng sn phn ny c s lng hng trong kho ln hn 0 , bn c th thc hin nh v d trn v thm vo mnh Where nh sau (H 5.9)

H 5.9 Cp nhp sn phm vi mnh WHERE

Trang 88

 p dng cc cu lnh trn chng ta thc hnh bi tp sau y : u tin , chng ta to mt database BanHang gm bng nh sau: SanPham, KhachHang, DonHang.

H 5.10 To CSDL BanHang Thm d liu vo cc bng dng cu lnh INSERT

H 5.12 Thm d liu bng INSERT

Trang 89

H 5.13 D liu sau khi thm Dng UPDATE thay i gi tr cc ct MaSP, SoLuong ca n hng th nht.

H 5.14 Cp nht d liu ca MaDonHang=1

Trang 90

Ta xa thng tin khch hng c m l 3 bng lnh DELETE xa d liu ca cc bng c quan h vi nhau, chng ta phi xa t bng con trc (DonHang), sau mi xa trong bng cha (KhachHang).

H 5.15 Xa d liu 2 bng lin quan

5.6 S dng TRANSACTION

Transaction l mt tp hp cc cu lnh c kt hp li thc mt cng vic. Transaction c dng o bo rng cc cu lnh c thc thi thnh cng hoc tht bi. C 3 phn chnh trong mt Transaction: BEGIN TRANSACTION: bt u mt Transaction. D liu s khng c cp nht n CSDL cho n khi COMMIT TRANSACTION c gi. COMMIT TRANSACTION: c gi khi tt c cc cu lnh ngay sau BEGIN TRANSACTION thc hin thnh cng, d liu s c ghi xung CSDL. ROLLBACK TRANSACTION: tr tt c d liu v trng thi ban u trc khi BEGIN TRANSACTION c gi. C Php:
BEGIN TRANSACTION SQL Statements COMMIT | ROLLBACK TRANSACTION

Trang 91

Chng ta c th s dng TRYCACTH hoc IF cng vi TRANSACTION

BEGIN TRY BEGIN TRAN -- Code for your transaction COMMIT TRAN END TRY BEGIN CATCH -- output an error message ROLLBACK TRAN END CATCH

V d: Chng ta s dng bng Categories trong CSDL Northwind to 1 Transaction Update n gin.

H 5.16 TRANSACTION UPDATE CategoryName Sau khi Update, sn phm Beverages c i thnh Coffee , kt qu nh hnh sau .

Trang 92

H 5.17 Truy vn bng Categories

Trang 93

Chng 6

Gii Thiu Cc Thnh Phn Khc Trong SQL Server 2008

Kt thc chng ny cc bn c th :
M t c khi nim v s dng c Trigger rng buc d liu M t c khi nim v s dng c Store Procedure Trnh by khi nim c bn v s dng Function v User-Defined Function Trnh by khi nim c bn v s dng View

6.1 Gii thiu v STORE PROCEDURE

STORE PROCEDURE L mt nhm cc cu lnh T-SQL c bin dch t trc( pre-compiled). Mt Store Procedure c th khng cha hoc cha nhiu tham s truyn vo; ng thi c th tr v mt gi tr, mt bng hoc khng tr v 1 gi tr no . S dng Store Procedure thc thi nhanh hn , d thay i , nng cp v bo mt,. C 2 dng Store Procedure: System Store Procedure User-defined Store Procedure

Cc System Store Procedure c sn khi chng ta ci t SQL Server. Tt c cc System Store Procedure u bt u bng tin t sp_ 6.1.1. Mt s nhm System Store Procedure:

Database Engine stored procedures: bao gm cc cu lnh queries ly thng tin ca SQL Server v CSDL.
V d: sp_helpdb ly thng tin ca tt c cc CSDL hin c trn Server

Trang 94

Database Mail stored procedures: dng cho cc thao tc v e-mail trong CSDL (sp_send_dbmail) Security stored procedures: dng cho mc ch bo mt nh thm/ xa User, ng nhp ( sp_addlogin) XML stored procedures: dng qun l cc ti liu XML (sp_xml_preparedocument).

6.1.2 User-defined Store Procedure C php: CREATE PROC | PROCEDURE <store name> @variablename datatype @variablename datatype AS SQL Statement

V d: chng ta to mt Store Procedure tn GetEmployees ly thng tin cc nhn vin theo thnh ph t bng Employees theo tham s truyn vo l @City.

Sau , gi Store Procedure va to, ta dng lnh EXEC <store name>

Trang 95

H 6.2 Gi SP GetEmployees thay i tn hay ni dung mt Store Procedure ta dng ALTER thay cho CREATE

Cc v d minh ha thc hnh v Store Procedure: To cc store procedure s dng CSDL Northwind To store procedure ly danh sch tt c cc mt hng

Trang 96

To store procedure ly danh sch cc mt hng c UnitPrice >= gi tr bt k

To store procedure cp nht UnitPirce vi ProductID v UnitPrice do ngi dng nhp vo

6.2 Gii thiu v Function v User-Defined Function

Function c dng tng t nh Store Procedure gip ti u hot ng ca CSDL; gim thi gian vit li cc lnh SQL thng dng.Ta c th truyn vo cc tham s cho Function. Tuy nhin, Function c nhng c im khc vi Store Procedure: Function lun tr v mt gi tr Function phi c tham s km theo khi gi, ngoi tr 1 s function nh GETDATE(), PI() Function c th c gi bn trong cu lnh SELECT

Nhng Function sn c khi ta ci SQL Server gi l Built-in Function. Mt s v d v Built-in Function:

Trang 97

V d 1 Tnh tng s lng tng mt hng (Quantity) c m n hng (OrderID) l 10248.

H 6.7 Tnh tng bng Sum()

V D 2: i khi, chng ta mun c kt qu truy vn m d liu tr v c kt hp t nhiu ct trong bng; khi ta c th dng CONCAT(). Mi h CSDL cung cp 1 cch khc nhau

MySQL: CONCAT() Oracle: CONCAT(), || SQL Server: +

Trong v d ny chng ta ch p dng CONCAT cho SQL Server. Ch : Ta ch c th kt hp cc ct c cng kiu d liu.

Trang 98

H 6.8 Truy vn khng dng CONCAT

H 6.9 Truy vn dng CONCAT

Trang 99

V d 3: Hm Built-in STUFF() C php:

STUFF ( character_expression , start , length ,character_expression )

Chng ta to 2 bng tn ContactPersons v MyCustomers c d liu nh hnh bn di:

H 6.10 Bng ContactPersons

H 6.11 Bng MyCustomers thu c kt qu nh hnh sau

H 6.12 Kt qu truy vn

Trang 100

Chng ta s dng hm STUFF

H 6.13 Gi hm STUFF()

V d 4: Hm UNPIVOT() Chng ta to mt bng tn Clients create table Clients ( clientID int primary key, clientName varchar(100), contact1 int, contact2 int, contact3 int, contact4 int ) Kt qu :

H 6.14 D liu Bng Clients

Trang 101

Vi cch to bng nh trn, chng ta kh c th m tt c cc ln lin h ca mi khch hng theo mt ct vi cch truy vn thng thng. Chng ta c nhiu cch thc hin Truy vn dng UNION ALL select clientID, contact1 as ContactID from clients where contact1 is not null union all select clientID, contact2 as ContactID from clients where contact2 is not null union all select clientID, contact3 as ContactID from clients where contact3 is not null union all select clientID, contact4 as ContactID from clients where contact4 is not null

H 6.15 Kt qu truy vn dng Union All

Trang 102

Tuy nhin cch lm trn tng i di v kh hiu, do SQL Server 2008 cung cp cho ta mt hm Built-in n gin v d s dng l UNPIVOT().

H 6.16 Gi hm Unpivot() Lu : Trc khi s dng hm Unpivot(), bt buc ta phi gi 1 Store h thng l sp_dbcmptlevel EXEC sp_dbcmptlevel Northwind, 90 Vi c php:

Sp_dbcmptlevel [@dbname] [ @new_cmptlevel] @dbname: tn CSDL @new_cmptlevel = 80 (SQL Server 2000)

= 90 (SQL Server 2005) = 100 (SQL Server 2008)

Trang 103

User Defined Function: ta c th t vit cc Function cho ring mnh nu nh cc Builtin Function khng ph hp vi yu cu.

C 2 dng User Defined Function: Scalar Function v Table-valued Function Scalar Function: lun tr v mt gi tr c th. C php: CREATE FUNCTION FunctionName (@parameter datatype) RETURNS type AS BEGIN SQL Statements RETURN Scalar expression END V d: chng ta s to 1 hm dng thay th cc gi tr NULL thnh NOT APPLICABLE

H 6.17 To hm NewRegion

Trang 104

Sau ta gi hm NewRegion va to trong cu lnh SELECT t bng Employees.

H 6.18 Gi hm NewRegion Table-valued Function: tr v kiu gi tr kiu bng d liu ging nh View (s c trnh by bn di). C php: CREATE FUNCTION FunctionName (@parameter datatype) RETURNS Table AS RETURN Statement V d: to 1 function lit k cc Nhn vin c City=@City

Trang 105

H 6.19 To function GetEmployeeByCity Gi hm GetEmployeeByCity va to vi tham s @City=London

H 6.20 Gi hm GetEmployeeByCity

Trang 106

Tuy nhin ta c th thu c kt qu tng t bng cch kt hp gia 1 hm Built-in l COALESCE() v 1 hm User Defined. To hm User Defined tn MySubString()

H 6.21 Hm MySubString Sau khi gi hm MySubString trong cu cu lnh SELECT ta cng thu c kt qu tng t v d phn hm Built-in

H 6.22 Gi hm MySubString()

Trang 107

 Cc v d minh ha Vit hm tr v tng UnitPrice ca cc mt hng thuc nh cung cp c m s @SupplierID

H6.23 fn_GetSumUnitPrice Gi hm v xem kt qu:

Vit hm tnh tng tin ca mt khch hng

Trang 108

H 6.24 Hm fn_GetTotalAmount Kt qu

H 6.24 gi hm fn_GetTotalAmount

Trang 109

6.3 Gii thiu v VIEW

View l mt i tng cho php ta c th xem chnh xc nhng d liu cn thit, khng ch mt vi trng trong 1 bng m cn c th t nhiu trng t nhiu bng khc nhau. C 2 cch to View: Cch 1: to View trong View Designer Khi ng SQL Server Management Studio (SSMS) Kt ni vi Server Chn CSDL cn lm vic Click phi vo Views Container -> chn New View Trong hp thoi Add Table -> chn cc bng cn thit -> Add Click chn cc trng trong bng va thm vo Chn Execute SQL xem kt qu Ctrl + S lu View Cch 2: to View bng T-SQL C Php: CREAE VIEW <viewName> AS SQL Satements chnh sa hoc xa View ta dung ALTER/ DROP <ViewName> V d: to 1 View tn ViewOrders_Products vi d liu ly t 2 bng Order Details v Products

Trang 110

H 6.25 To ViewOrders_Details Gi View va to vi iu kin ProductID=1

H 6.26 Gi view ViewOrders_Products

Trang 111

Cc v d minh ha To View thng k s lng mt hng theo tng nh cung cp

H 6.27 View vGetProductBySupplier

To view ly danh sch cc ha n c gi tr >= 10000

H 6.28 View vGetOrder

Trang 112

6.4 Gii thiu TRIGGER

Trigger l i tng gng lin vi mt bng, t ng thc hin khi xy ra s thay i d liu trong bng nh Update, Insert hay Delete. Trigger c dng m bo Data Integrity (ton vn d liu) hay thc hin cc Business Rule(rng buc d liu) no . nh ngha Trigger cn ch : Trigger c to trong bng no? Trigger c kch hot khi cu lnh no c thc thi (Insert ,Update hay Delete).

C php CREATE TRIGGER <Trigger Name> ON <Scope> <Trigger Timing> <Trigger Condition> As Begin <Trigger Action> End S dng trigger khi chng ta cp nht d liu trn bng C php CREATE TRIGGER <Trigger name> ON <table> FOR UPDATE AS BEGIN Statements END

Trang 113

Gi s bn mun to ra mt Trigger t ng thng bo ti ngi qun l khi c mt n hng mi c cp nhp.

H 6.29 To trigger send mail

Ch : thc hin c cu lnh trn chng ta phi Enable chc nng Database Mail XPs
Sau khi bn to thnh cng Trigger s t ng gim st v thng bo khi c s thay i trong bn Orders. V d 1: gim st cc hot ng thm ,chnh s v cp nhp hng trong table Products chng ta to ra mt table Audit , vi cc ct AuditID, AuditType,DateTimeAudit,ProductID. Trong AuditType c cc gi tr I,U,D tng ng vi cc hot ng Insert, Update hay Delete.
Trang 114

H 6.30 Bng Audit Sau chng ta to ra Trigger dng cho vic audit nh sau

Trang 115

H 6.31 Trigger Insert Audit Cc v d minh ha : Chng ta to mt database nh bn di, sau to cc Trigger qun l vic thay i d liu

H 6.32 To Database

Trang 116

To Trigger tg_UpdateOrder

To trigger tg_DeleteOrderDetail

Trang 117

To trigger tg_UpdateCustomer

Trang 118

Chng 7:

SQL SERVER 2008 v XML

Kt thc chng ny cc bn c th :
Trnh by c cc khi nim c bn v XML v SQL Server 2008 Trnh by v thc hin c vic lu tr d liu dng XML trong bng Thc hin c truy vn d liu XML Thc hin c Thm , xa , sa d liu XML trong bng

7.1 Gii thiu v XML v SQL Server 2008

Khi nim v XML XML (Extendsible Markup Language) c W3C to ra tr thnh mt dng d liu n gin, linh hot da trn StandardGeneralized Markup Language (SGML). Ngn ng XML c W3C xut vo nm 1996 v cng b vo nm 1998. W3C XML 1.0 a ra mt tp hp cc quy tc cho vic thm cu trc v ni dung d liu bng cch nh du, tiu chun ha vic truyn v chia s d liu gia cc ng dng.
Mt s u im ca XML:

n gin cho vic x l: d liu dng XML c th c x l bi cc chng trnh

chuyn i.

c truyn trc tip trn Internet: XML c to bi cc tp hp k t c nh

ngha nh UTF-8, UTF-16 c thit k d dng i qua tng la, s dng cc chun giao thc trn Internet nh HTTP

n gin vi ngi c: do c to bi dng k t v cu trc ca ti liu XML r rng,

nn con ngi c th c v hiu ni dung bn trong d dng, gip cho vic kim tra li n gin hn kiu d liu dng nh phn.

c to d dng: khng ging dng d liu nh phn, XML c th c to bi mt

trnh son tho n gin.

Cu trc ca mt ti liu XML:

Trang 119

<?xml version=1.0 encoding=UTF-8?> <root> <child 1></child 1> <child 2></child 2> <child 3></child 3> </root> V d : <?xml version="1.0" encoding="utf-8"?> <ProductList> <ProductDetail> <ProductID>1</ProductID> <ProductName>coffee</ProductName> <UnitPrice>10</UnitPrice> <Quantity>20</Quantity> </ProductDetail> <ProductDetail> <ProductID>2</ProductID> <ProductName>sugar</ProductName> <UnitPrice>10</UnitPrice> <Quantity>3</Quantity> </ProductDetail> <ProductList>

XML trong SQL Server 2008

Mt s c im chnh m SQL Server 2008 h tr XML: o o o o Kiu d liu XML (XML data type) Lt XML (XML Schema collections) Ch mc XML (XML indexes) XQuery v XML DML

Kiu d liu XML (XML data type) y l mt trong nhng im quan trng trong SQL Server 2008. XML data type h tr lu tr cc ti liu c nh dng XML, ng thi c dung khai bo cc ct trong mt bng, cc bin T-SQL, tham s, kiu tra v ca 1 hm. Hn na, XML data type mang n mt tp hp cc phng thc dng truy vn d liu. Gii hn:

Trang 120

o D liu dng Xml khng th vt qu 2GB o Khng h tr chuyn i sang kiu d liu dng text hoc ntext. C th dng varchar(max) hoc nvarchar(max) thay th. o Khng th so snh hoc sp xp, khng th dng GROUP BY. Lt XML (XML Schema) SQL Server 2008 h tr to lc XML pha server c th kim tra cu trc ca ti liu XML. Ch mc XML (XML index) Trong SQL Server, khi ta truy vn d liu XML, trc ht d liu s c chuyn sang mt nh dng khc, qu trnh gi l shredding. Qa trnh ny c th mt nhiu thi gian khi truy vn mt s lng ln d liu dng Xml. SQL Server 2008 h tr to ch mc cho cc ct c kiu d liu XML trong bng, gip ti u ha v nng cao kh nng truy vn d liu. XQuery v XML DML Kiu d liu XML cung cp mt s phng thc cho php ta truy vn hoc chnh sa d liu dng Xml. Nhng phng thc nh query(), value(), exist(), nodes(),

modify() h tr truy vn XQuery v XML DML..

7.2 Cch xy dng bng, lu d liu dng XML

trong bng To Bng: to mt ct dng Xml trong bng ta dng lnh CREATE TABLE C php:

CREATE TABLE <table name> ( Col1 <data type>, Col2 <xml>

) V d: Chng ta to 1 bng tn XmlProducts trong CSDL Northwind gm 2 ct ID v xmlCol; trong ct xmlCol c kiu d liu XML

Trang 121

7.3 Thm, Xa, Sa d liu XML trong bng

Thm d liu dng XML vo bng

thm d liu vo cc ct c kiu d liu XML trong bng, ta dng lnh INSERT INTO tng t nh cch thm d liu thng thng C php: INSERT INTO <TableName> ( <Column 1> <Column 2> ) VALUE ( <Value 1> <Value 2> ) Chng ta cng c th thm d liu dng 1 ti liu XML : C php: INSERT INTO <TableName> ( <Xml Column> ) VALUE ( <root> <child node1></ child node1> <child node2></ child node2>

Trang 122

</root> ) V d: Thm d liu vo bng XmlProducts va to

H 7.2 Thm d liu vo bng XmlProducts Thm d liu vo bng dng DECLARE

Chng ta c th dng DECLARE khai bo bin nhm gip cho cu lnh INSERT c n gin r rng hn C php: DECLARE <variable_name> [AS] xml SET <variable_name> = [xml Document] V c: dng DECLARE khai bo 1 bin tn @xmlVar thay th cho ct xmlCol

Trang 123

H 7.3 Dng DECLARE khai bo bin @xmlVar Sau khi khai bo bin chng ta thc hin cu lnh INSERT, khi dng bin @xmlVar thay cho ct xmlCol

Trang 124

H 7.5 Kt qu sau khi thm d liu Cp nht, Xa d liu

i vi cc thao tc chnh sa ni dung vo xa d liu, chng ta cng thc hin vi cu lnh UPDATE v DELETE V d: Thay i tn sn phm c ID=3.

Trang 125

H 7.7 Kt qu sau khi cp nht V d: Xa d liu va cp nht

Trang 126

H 7.8 Lnh DELETE v kt qu

7.4 Truy vn d liu XML

Ngoi cu lnh SELECT n gin c gii thiu, SQL Server 2008 cn h tr mt s cch truy vn cho kiu d lu XML FOR XML: tr v d liu dng mt ti liu XML C php: SELECT <column1>,<column2> FROM <table_name> FOR XML <mode> Vi mode: RAW, AUTO, EXPLICIT, PATH V D: truy vn d liu ca bng xmlProducts

Trang 127

H 7.9 Lnh SELECTFOR XML Sau , chng ta click vo dng d liu tr v, SQL Server s cho thy kt qu

Trang 128

H 7.10 Kt qu truy vn dng XML Tuy nhin, khi dng FOR XML, d liu dng XML khng ng cu trc v thiu mt tag m ta thng gi l Root. thm Root vo ta c th dng FOR XML PATH C php: SELECT <column1>,<column2> FROM <table_name> FOR XML PATH, ROOT<root> V D: Ta thm mt root ProductList vo ti liu XML khi truy vn FOR XML

Trang 129

H 7.11 Truy vn dng PATH Kt qu:

H 7.12 ProductList c thm vo kt qu truy vn

Trang 130

Ngoi vic truy vn cc bng c d liu dng XML, ta c th dng FOR XML truy vn v xem d liu t cc bng thng thng di dng 1 ti liu XML. V d: truy vn d liu cc khch hng trong bng Customers ca CSDL Northwind c CustomerID bt u bng A

H 7.13 Truy vn t Customers

Trang 131

Kt qu:

H 7.14 Danh sch Khch hng tm hiu r hn, cc bn c th tham kho ti http://msdn.microsoft.com/enus/library/ms191268.aspx

Mt s phng thc dng cho kiu d liu XML

SQL Server cung cp cho chng ta mt s phng thc dng truy vn XML, cc phng thc ny c xem tng t nh Subquery, do khng th trong cc cu lnh PRINT hay trong cc mnh GROUP BY. Cch gi cc phng thc ny tng t khi ta gi phng thc/hm trong lp trnh hng i tng

Trang 132

Hm QUERY() C php:

xml_obj.query ( xquery ) : Vi XQuery l node trong ti liu xml

V d: Dng Query() ly tn cc sn phm trong bng xmlProducts

H 7.15 Lit k ProductName dng Query()

Hm Value(): tr v gi tr ca cc node. C php:

xml_obj.value ( xquery, data_type )

V D: Ly gi tr ProductID, ProductName trong bng xmlProducts

Trang 133

H 7.16 Lit k cc sn phm Hm Exist(): kim tra s tn ti gi tr ca cc node C php: xml_obj.exist ( xquery) Cc gi tr tr v: 0: khng tn ti gi tr bn trong node 1: tn ti gi tr bn trong node NULL: xml_obj khng tn ti V d: Kim tra gi tr ProductName c ID=1

Trang 134

H 7.17 Kim tra tn ti gi tr ProductName Hm Nodes() C php:

xml_obj.nodes ( xquery ) AS table ( column )

Table( column ): 1 bng tm c to v cha ct dng xml m d liu c c l gi tr tr v ca nodes() V d: ly tn sn phm trong bng xmlProducts truyn vo bng myTable vi ct myCol dng xml

Trang 135

H 7.18 S dng Nodes() Ch mc XML (XML Index) Ch mc c to trn cc ct c kiu d liu xml, gip nng cao hiu qu truy vn khi ta c mt bng cha mt lng ln cc d liu dng xml. C 2 loi ch mc: Primary XML Index v Secondary XML Index C php: CREATE [ PRIMARY ] XML INDEX index_name ON <object> ( xml_column_name ) [ USING XML INDEX xml_index_name [ FOR { VALUE | PATH | PROPERTY } ]

Trang 136

V d: To Index cho ct xmlCol trong bng xmlProducts

H 7.19 To Primary Index tn xmlColIndex

Trang 137

H 7.20 To Secondary Index tn xmlColValueIndex Xa, Chnh sa Index xa Index, ta dung lnh DROP INDEX C php:

DROP INDEX { index_name ON <object> [ ,...n ] } V d: xa Secondary Index va to

Trang 138

H 7.21 Xa Index chnh sa ta dng ALTER INDEX C php: ALTER INDEX <index_name> ON <object> REBUILD | REORGANIZE | DISABLE V D: chnh sa Index dung Rebuild hoc Disable

Trang 139

H 7.22 Chnh sa Index

Lc XML (XML Schema)

XML Schema m t cu trc ca ti liu dng XML; s dng c php ca XML

nh ngha cc thnh phn, thuc tnh c trong ti liu. nh ngha kiu d liu ca cc thnh phn bn trong. Xc nh v sp xp cc thuc tnh con. Xc nh thnh phn no rng hoc c th thm d liu vo .

C php: To Schema: CREATE XML SCHEMA COLLECTION <schema_name> AS Expression

Trang 140

Chnh sa Schema:
ALTER XML SCHEMA COLLECTION <schema_name> Xa Schema:
DROP XML SCHEMA COLLECTION <schema_name>

V d: To 1 schema tn ContactSchemaCollection, sau to bng Contacts v them d liu vo bng theo cu trc Schema va to

H 7.23 To Schema ContactSchemaCollection

Trang 141

To bng Contacts gm 1 ct Contact

Sau khi to bng, chng ta c th thm d liu

Do ta nh ngha trc cc thnh phn ca XML trong Schema, nn khi ta thm d liu khng ph hp, SQL Server s kim tra vi Schema v bo li

tm hiu r hn v XML Schema cng nh cc cch s dng khc ca Schema, cc bn c th tham kho ti: http://msdn.microsoft.com/en-us/library/ms176009.aspx

Trang 142

Chng 8

.NET Integration & SQL Server

Kt thc chng ny cc bn c th :
Trnh by c cc khi nim v .NET Integration vi SQL Server 20008 Trnh by v xy dng c CLR Store Procedure Trnh by v xy dng c CLR User-Defined Function Trnh by v xy dng CLR User-Defined Type

8.1 Gii thiu v .NET Integration & SQL Server 2008

Common Language Runtime (CLR) cung cp cc phng thc qun l m ngun nh tch hp cc ngn ng, bo mt bng m truy cp, qun l vng i cc i tng, g li i vi ngi dng SQL Server v cc nh pht trin ng dng, tch hp CLR (CLR Integration) trong .Net ngha l ta c th vit v lu tr Store Procedure, triggers, user-defined types, user-defined function bng cch s dng bt k .Net Framework.

Lu rng, CLR Integration khng dng trong Visual Studio 2003 (Framework 1.0) .
Nhng li ch chnh ca CLR Integration: M hnh lp trnh tt hn: cc nh pht trin c th tn dng sc mnh ca th vin .Net Framework, trong cung cp mt tp hp rng ri cc class s dng nhanh chng v hiu qu nhm gii quyt cc vn lp trnh. Ci thin tnh an ton v bo mt: qun l cc m trong mi trng CLR t chc trong c s d liu. SQL Server cung cp mt s thay th an ton v bo mt hn cc phin bn trc. Kh nng xc nh kiu d liu: User-defined type v user-defined aggregates l 2 i tng qun l d liu mi, m rng kh nng lu tr v truy vn SQL Server.

Trang 143

8.2 Xy dng CLR Store Procedure

CLR Store Procedure c s dng nh cc phng thc dng public static. Cc phng thc static c th khai bo dng void hay tr v mt gi tr kiu interger. Nu khai bo dng void, gi tr tr v l 0. Gi tr tr v ca Store Procedure c th l tham s, bng kt qu hoc mt thng bo. Mn hnh lm vic vi CLR Store Procedure trong Visual Studio 2008

H 8.1 Giao din vit Store Procedure

Trang 144

 tm hiu r hn v cch to mt CLR Store Procedure Integration, chng ta s to mt store procedure tn GetProducts trong CSDL Northwind theo cc bc sau :

Bc 1: m VS 2008 chn File -> New Project.

Chn Database -> SQL Server Project t tn Project: SampleCLRStoreProcedure

H 8.2 To Project mi

Trang 145

Sau khi to Project, ta chn Database cn lm vic; trong v d ny ta chn Northwind, VS 2008 m ca s chnh ca project.

H 8.3 Ca s chnh Project

Trang 146

Bc 2: Thm vo project 1 class dng to store procedure

Right click tn Project -> Add -> Store Procedure

H 8.3 Thm Store Procedure

Trang 147

Bc 4: t tn Store Procedure l GetProducts

H 8.4 To store GetProducts

Bc 5: Chng ta to store GetProducts c 1 tham s truyn vo kiu int nhn

gi tr CategoryID do ngi dng nhp vo

Trang 148

H 8.5 Vit code Store GetProducts Ch : kiu d liu dng cho @CategoryID l SqlInt32

Bc 6: Deploy Store GetProducts

Right click Project -> Deploy

Trang 149

H 8.6 Deploy store

Nu kt ni c vi Server, Visual Studio s thng bo Deploy Succeeded. Nu tht bi s xut thng bo trong mc Output: Deploy Failed. Bc 7 : thc thi store procedure va to chng ta s thc hin trong SQL
Server 2008. Chng ta m SQL Server Management Studio -> chn New Query

Trang 150

H 8.7 Thc thi Store

8.3 Xy dng CLR User-Defined Function

User-Defined Function c th nhn tham s, thc hin tnh ton, cc thao tc khc v tr v mt gi tr no . Tng t T-SQL Function, CLR User-Defined Function c 2 loi Function: Scalar function tr v mt gi tr n. Table Valued function: tr v mt tp hp cc dng d liu.

Trang 151

V d: Chng ta s to mt User-defined Function dng kin tra Email c hp l hay khng hiu r hn v cch to v s dng Function.

Bc 1: Add ->User-Defined Function

H 8.8 Thm mi Function

Bc 2 : Vit code cho hm kim tra Email tn ValidateEmail

Trang 152

H 8.9 Ni dung hm ValidateEmail

Bc 3 :Nhn phi chut | Deploy th VS.Net s ci t hm ny vo SQL Server t

ng .

Lu : Khi deploy th VS.Net s to mt tp tin *.dll trong th mc Bin\Debug

chng ta c th ci t trn c c d liu khc bng cch s dng cc lnh T-SQL .

Trang 153

H 8.10 Deploy hm va to

Bc 4 : ng k th vin .dll s dng trong SQL Server 2008 ( ng k bng

tay s dng lnh T-SQL ). Nu chng ta chn Deploy bc 3 th c th sang bc 5.

H 8.11 ng k dll

Bc 5 : Trong trng hp ta khng s dng tn hm c sn trong .Net , ta c th to

hm mi tn RegEx trong SQL Server 2008 da trn hm to trong .Net. Sau khi to hm, chng ta c th gi hm ny trong lnh SELECT

Trang 154

H 8.12 To v gi hm RegEx

8.4 Xy dng CLR User-Defined Types

SQL Server 2008 cho php ta to nhng i tng c s d liu trong .NET Framework CLR. Chng ta c th dng User-Defined Tpyes (UDTs) to ra cc kiu d liu mi lu tr cc i tng ca CLR trong SQL Server. UDTs c th cha nhiu thnh phn v c nhiu thuc tnh khc vi nhng kiu d liu sn c trong SQL Server. xy dng UDTs, ta thc hin tng t nh CLR User-defined Function: Vit code v bin dch th vin to UDTs trong .NET. ng k th vin va to trong SQL Server (CREATE ASSEMBLY) To UDTs trong SQL Server To bng, tham s s dng UDTs.

Trang 155

V d : To mt User-defined Types dng kim tra Mt khu nhp vo trong CSDL

Bc 1: Thm class dng User-defined Type vo project ( tn project l ThuVien)

H 8.13 Thm class User-defined Type

Bc 2 : Vit code cho User-defined va thm vo

using using using using using using

System; System.Data; System.Data.SqlClient; System.Data.SqlTypes; Microsoft.SqlServer.Server; System.Text.RegularExpressions;

Trang 156

using System.IO; [Serializable] [SqlUserDefinedType(Format.UserDefined, IsByteOrdered=true, MaxByteSize=8000)] public struct MatKhau : INullable, IBinarySerialize { private bool m_Null; public string m_ChuoiMK; public override string ToString() { return m_ChuoiMK; } public bool IsNull { get { return m_Null; } } public static MatKhau Null { get { MatKhau matKhau = new MatKhau(); matKhau.m_Null = true; return matKhau; } } public static MatKhau Parse(SqlString chuoi) { if (chuoi.IsNull) { return Null; }

Trang 157

MatKhau matKhau = new MatKhau(); bool hopLe = Regex.IsMatch(chuoi.ToString(), "[a-zA-Z]{4,}[0-9]"); if (hopLe == true) { matKhau.m_ChuoiMK = chuoi.ToString(); return matKhau; } throw new SqlTypeException("Mat khau khong hop le."); } public void Write(BinaryWriter writer) { writer.Write(m_ChuoiMK); } public void Read(BinaryReader reader) { m_ChuoiMK = reader.ReadString(); } }

Bc 3 : Build v Deploy User-defined Type tng t nh phn CLR User-defined

Function.

Bc 4: ng k .dll vo SQL Server

Trang 158

H 8.14 ng k dll

Bc 5 : To bn Users v thm 3 dng d liu, trong c dng d liu u tin

khng ng vi kiu MAT_KHAU m ta nh ngha l phi bao gm c ch ln s. Trong v d ny , chng ta to ra mt kiu d liu tn MAT_KHAU c dng 4 k t u l ch ci sau l cc s nguyn t 0-9

Trang 159

H 8.15 Thng bo li sau khi nhp d liu Theo nh thng bo li trn th ch c 2 dng d liu 2 v 3 c thm vo bng Users, dng u tin khng ng vi kiu d liu.

H 8.16 Lit k d liu va thm

Trang 160

D liu trong ct MyPassword dc chuyn sang dng Binary trong cu lnh

public void Write(BinaryWriter writer) { writer.Write(m_ChuoiMK); }

c th thy c mt khu ta c th s dng hm Cast trong cu lnh Select

Select UserName,Cast(MyPassword as varchar) as MyPassword from Users

Kt qu

Trn y l 2 v d n gin v User-defined Function v User-defined Type, tm hiu chi tit hn v CLR Integration trong SQL Server 2008. Chng ta s dng k thut ny vit cc hm phc tp bng cch s dng cc ngn ng .Net ( C#, VB.Net,.. ) thay v phi vit bng cc lnh T-SQL . Cc bn c th tham kho theo Book Online 2008 v MSDN ca Microsoft.

Trang 161