›
›

Bài giảng Ngôn ngữ lập trình Transaction–SQL - Phạm Thị Lan Anh

pdf 52 trang phuongnguyen 2450
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Ngôn ngữ lập trình Transaction–SQL - Phạm Thị Lan Anh", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfbai_giang_ngon_ngu_lap_trinh_transactionsql_pham_thi_lan_anh.pdf

Nội dung text: Bài giảng Ngôn ngữ lập trình Transaction–SQL - Phạm Thị Lan Anh

  1. Ngôn ngữ l ậ p trình Transaction – SQL Sau khi họ c xong ph ầ n này, sinh viên có thể v ậ n d ụ ng các l ệ nh trong ngôn ng ữ l ậ p trình Transaction – SQL để vi ế t các đo ạ n mã lệ nh trong SQL Server GV Phạ m Th ị Lan Anh 1
  2. Nộ i dung bài h ọ c 1. Enterprise Application Architechter 2. Biế n và các ki ể u d ữ li ệ u 3. Toán tử 4. Hàm 5. Các câu lệ nh đi ề u khi ể n 6. Các cách sử d ụ ng các l ệ nh T-SQL GV Phạ m Th ị Lan Anh 2
  3. Thiế t k ế Enterprise Application Architecture Xác đị nh các l ớ p logical (Logical Layers) Thiế t k ế các l ớ p v ậ t lý (Physical Layers) Truy xuấ t d ữ li ệ u GV Phạ m Th ị Lan Anh 3
  4. Logical Layers GV Phạ m Th ị Lan Anh 4
  5. Data presentation Layer Đượ c xem là user service and cho phép user xem và thao tác lên data: web browser and các Microsoft Windows® applications Sử d ụ ng các service mà application logic layer cung cấ p GV Phạ m Th ị Lan Anh 5
  6. Application Logic Layer  Chứ a application logic, đ ị nh nghĩa các rules và processes giúp cho user không cầ n truy xu ấ t tr ự c tiế p vào database  Clients kế t n ố i vào business service đ ể k ế t n ố i vào data server. Business service là các custom- built components hoặ c integrated applications và services, ví dụ nh ư Web services.  Application logic layer chứ a các components đ ể tạ o thành transaction services, messaging services, hoặ c object và connection management services. GV Phạ m Th ị Lan Anh 6
  7. Data Services Layer Data services bao gồ m data access logic và data storage. Bao gồ m các SQL Server stored procedures để qu ả n lý data traffic và integrity trên the database server. GV Phạ m Th ị Lan Anh 7
  8. Thiế t k ế các l ớ p v ậ t lý GV Phạ m Th ị Lan Anh 8
  9. Truy xuấ t d ữ li ệ u GV Phạ m Th ị Lan Anh 9
  10. Khai báo biế n Dùng từ khoá declare đ ể khai báo bi ế n DECLARE {@local_variable data_type} [, n] Gán giá trị cho bi ế n SET @local_variable_name = expression GV Phạ m Th ị Lan Anh 10
  11. Ví dụ DECLARE @vLastName char(20), @vFirstName varchar(11) Gán giá trị cho SET @vLastName = 'Dodsworth‘ biế n bằ ng từ khoá SELECT @vFirstName = FirstName set FROM Northwind Employees hoặ c WHERE LastName = @vLastName bằ ng câu lệ nh select PRINT @vFirstName + ' ' + @vLastName GV Phạ m Th ị Lan Anh 11
  12. Data Type (1)  Integers Bigint: 8 bytes Int: 4 bytes Smallint: 2 bytes Tinyint: 1 byte, từ 0 -> 255.  bit Bit: 1 hoặ c 0 value.  decimal and numeric Decimal từ -10^38+1->10^38 –1. Numeric: giố ng decimal.  money and smallmoney Money: 8 bytes Smallmoney: 4 bytes  Approximate Numerics Float: từ -1.79E + 308 -> 1.79E + 308. Real: từ -3.40E + 38 -> 3.40E + 38. GV Phạ m Th ị Lan Anh 12
  13. Data Type (2)  datetime and smalldatetime Datetime: từ 1/1/1753-> 31/12/9999. Smalldatetime từ 1/1/1900, -> 6/6/2079.  Character Strings Char: Fixed-length non-Unicode character, <= 8,000 ký tự Varchar: Variable-length non-Unicode , <= 8,000 ký tự Text: Variable-length non-Unicode <=2^31 - 1 (2,147,483,647) ký tự  Unicode Character Strings ncharFixed-length Unicode , <=4,000 characters. nvarcharVariable-length Unicode, <=4,000 characters Ntext Variable-length Unicode <= 2^30 - 1 (1,073,741,823) characters.  Other Data Type Cursor: là mộ t tham chi ế u đ ế n m ộ t cursor.  Một bi ế n không th ể có ki ể u là text, ntext, hoặc image GV Phạ m Th ị Lan Anh 13
  14. Toán tử (operators)  Các loạ i toán t ử Số h ọ c: *, /, %, - , + So sánh: =, , >=, <, <= Nố i chu ỗ i: + Luậ n lý: AND, OR, NOT GV Phạ m Th ị Lan Anh 14
  15. Thứ t ự ư u tiên các toán t ử GV Phạ m Th ị Lan Anh 15
  16. Functions (1) Aggregate functions: tính toán trên mộ t nhóm và trả v ề m ộ t giá tr ị . Ví d ụ : SELECT AVG(UnitPrice) FROM Products Products 28.8663 (1 row(s) affected) GV Phạ m Th ị Lan Anh 16
  17. Functions (2)  Scalar functions: Tác độ ng lên m ộ t giá tr ị và tr ả v ề m ộ t giá trị . Có th ể s ử d ụ ng hàm trong các bi ể u th ứ c.  Chúng ta có thể nhóm các scalar function theo nhóm sau: Configuration Trả v ề các thông tin v ề configuration Cursor Trả v ề các thông tin v ề Cursor DateTime Hàm tác độ ng lên giá tr ị dateTime nh ậ p vào và trả v ề m ộ t giá tr ị là string, numeric, ho ặ c datetime Mathematical Hàm số h ọ c Metadata Thông tin về database String Các hàm chuỗ i GV Phạ m Th ị Lan Anh 17
  18. Functions (3)_ Ví dụ SELECT DB_NAME() AS 'database' Database Northwind (1 row(s) affected) SET DATEFORMAT dmy GO DECLARE @vdate datetime SET @vdate = '29/11/00' SELECT @vdate 2000-11-29 00:00:00.000 GV Phạ m Th ị Lan Anh 18
  19. Mathematical Functions ABS DEGREES RAND ACOS EXP ROUND ASIN FLOOR SIGN ATAN LOG SIN ATN2 LOG10 SQUARE CEILING PI SQRT COS POWER TAN COT RADIANS GV Phạ m Th ị Lan Anh 19
  20. Aggregate Functions AVG MAX BINARY_CHECKSUM MIN CHECKSUM SUM CHECKSUM_AGG STDEV COUNT STDEVP COUNT_BIG VAR GROUPING VARP GV Phạ m Th ị Lan Anh 20
  21. DateTime functions Function Determinism DATEADD Deterministic DATEDIFF Deterministic DATENAME Nondeterministic DATEPART Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week. DAY Deterministic GETDATE Nondeterministic GETUTCDATE Nondeterministic MONTH Deterministic YEAR Deterministic GV Phạ m Th ị Lan Anh 21
  22. String functions ASCII NCHAR SOUNDEX CHAR PATINDEX SPACE CHARINDEX REPLACE STR DIFFERENCE QUOTENAME STUFF LEFT REPLICATE SUBSTRING LEN REVERSE UNICODE LOWER RIGHT UPPER LTRIM RTRIM GV Phạ m Th ị Lan Anh 22
  23. Cast và Convert CAST ( expression AS data_type ) CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) GV Phạ m Th ị Lan Anh 23
  24. Control-of-Flow Language 1. BEGIN END : đị nh nghĩa m ộ t kh ố i lệ nh BEGIN sql_statement | statement_block END GV Phạ m Th ị Lan Anh 24
  25. 2. If else IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ] if (select count(*) from customers where country='canada') > 0 begin print ‘There are many Canada customers' end else print ‘Welcome' GV Phạ m Th ị Lan Anh 25
  26. Ví dụ l ệ nh If . Else USE Northwind IF EXISTS (SELECT OrderID FROM Orders WHERE CustomerID = 'Frank') PRINT ' Customer cannot be deleted ' ELSE BEGIN DELETE Customers WHERE CustomerID = 'Frank' PRINT ' Customer deleted ' END GV Phạ m Th ị Lan Anh 26
  27. 3. While WHILE Boolean_expression { sql_statement | statement_block } [ BREAK ] { sql_statement | statement_block } [ CONTINUE ] BREAK: thoát ra khỏ i vòng while CONTINUE: restart lạ i vòng l ậ p, b ỏ qua các lệ nh sau CONTINUE. GV Phạ m Th ị Lan Anh 27
  28. GOTO GOTO LabelName IF (SELECT SYSTEM_USER()) = 'payroll' GOTO calculate_salary Other program code would appear here. When the IF statement evaluates to TRUE, the statements between the GOTO and the calculate_salary label are ignored. When the IF statement evaluates to FALSE the statements following the GOTO are executed. calculate_salary: Statements to calculate a salary would appear after the label. GV Phạ m Th ị Lan Anh 28
  29. Row .Level  Simple CASE function: CASE input_expression WHEN when_expression THEN result_expression [ n ] [ ELSE else_result_expression ] END  Searched CASE function: CASE WHEN Boolean_expression THEN result_expression [ n ] [ ELSE else_result_expression ] END GV Phạ m Th ị Lan Anh 29
  30. Ví dụ SELECT ProductID, 'Product Inventory Status' = CASE WHEN (UnitsInStock < UnitsOnOrder AND Discontinued = 0) THEN 'Negative Inventory - Order Now!' WHEN ((UnitsInStock-UnitsOnOrder) < ReorderLevel AND Discontinued = 0) THEN 'Reorder level reached- Place Order' WHEN (Discontinued = 1) THEN ' Discontinued ' ELSE 'In Stock' END FROM Products GV Phạ m Th ị Lan Anh 30
  31. Kế t qu ả câu l ệ nh GV Phạ m Th ị Lan Anh 31
  32. Áp dụ ng trong database QLVT Liệ t kê các chi ti ế t hoá đ ơ n c ủ a hoá đ ơ n HD01 gồ m các thông tin: Mã v ậ t t ư , SL, Giá bán, KM vớ i KM = 0 nế u SL =10 KM = (SL*giaban) * 0.2 nế u SL >=100 GV Phạ m Th ị Lan Anh 32
  33. Comments  Inline comments SELECT ProductName, (UnitsInStock + UnitsOnOrder) AS Max, Calculates inventory SupplierID FROM Products SELECT  Block comments /* This code retrieves all rows of the products table and displays the unit price, the unit price increased by 10 percent, and the name of the product. */ SELECT UnitPrice, (UnitPrice * 1.1), ProductName FROM Products GV Phạ m Th ị Lan Anh 33
  34. Lệ nh RAISERROR Trả v ề m ộ t thông báo l ỗ i do user đ ị nh nghĩa Cú pháp: RAISERROR ( { msg_id | msg_str } { , severity , state } ) [ WITH option [ , n ] ] GV Phạ m Th ị Lan Anh 34
  35. Tham số  msg_id là mã lỗ i đ ượ c l ư u trong sysmessages table.  msg_str: là chuỗ i thông báo l ỗ i đ ượ c đ ị nh d ạ ng giố ng nh ư l ệ nh printf trong l ậ p trình C  Severity: mứ c đ ộ nghiêm tr ọ ng c ủ a l ỗ i. Có giá tr ị từ 0->18 đ ượ c dùng b ở i user, t ừ 19 -> 25 đ ượ c dùng bở i sysadmin (dùng vớ i WITH LOG).  State: số nguyên t ừ 1 ->127 mô t ả m ứ c đ ộ c ầ n thiế t c ủ a l ỗ i. GV Phạ m Th ị Lan Anh 35
  36. Các cách thự c hi ệ n các l ệ nh T-SQL Các lệ nh có c ấ u trúc đ ộ ng Dùng Batch Dùng Scripts Dùng Transactions Dùng XML GV Phạ m Th ị Lan Anh 36
  37. Dùng cấ u trúc l ệ nh đ ộ ng  Dùng lệ nh EXECUTE vớ i các h ằ ng chu ỗ i và bi ế n  Sử d ụ ng khi ta ph ả i gán giá tr ị cho bi ế n t ạ i th ờ i đi ể m execute  Các biế n và table t ạ m ch ỉ t ồ n t ạ i trong th ờ i gian th ự c thi lệ nh. DECLARE @dbname varchar(30), @tblname varchar(30) SET @dbname = 'Northwind' SET @tblname = 'Products' EXECUTE ('USE ' + @dbname + ' SELECT * FROM '+ @tblname) GV Phạ m Th ị Lan Anh 37
  38. Sử d ụ ng kh ố i (batch)  Mộ t ho ặ c nhi ề u l ệ nh T- SQL đ ượ c submit cùng lúc vớ i nhau.  Sử d ụ ng l ệ nh GO đ ể k ế t thúc m ộ t kh ố i  Các biế n không th ể tham chi ế u sau l ệ nh GO  Không thể dùng các l ệ nh sau đây trong batch: CREATE PROCEDURE CREATE VIEW CREATE TRIGGER CREATE RULE CREATE DEFAULT GV Phạ m Th ị Lan Anh 38
  39. Ví dụ l ệ nh kh ố i l ệ nh batch h ợ p l ệ CREATE DATABASE CREATE TABLE GO CREATE VIEW1 GO CREATE VIEW2 GO GV Phạ m Th ị Lan Anh 39
  40. CREATE DATABASE CREATE TABLE Ví dụ về CREATE TRIGGER khối lệnh CREATE TRIGGER batch không GO hợp lệ GV Ph mạ ị Th Lan Anh 40
  41. Sử d ụ ng script  Script là mộ t t ậ p tin có ph ầ n m ở r ộ ng là .sql, có nộ i dung là các l ệ nh T-SQL, đ ượ c t ạ o b ở i b ấ t kỳ mộ t Text editor nào.  Đượ c th ự c hi ệ n trong công c ụ SQL Query Analyzer hoặ c osql Utility  Đượ c dùng l ạ i khi c ầ n. GV Phạ m Th ị Lan Anh 41
  42. Dùng Transactions  Đượ c x ử lý gi ố ng m ộ t Batch  Bả o đ ả m tính toàn v ẹ n d ữ li ệ u  Toàn bộ các l ệ nh trong transaction s ẽ thành công hoặ c không thành công  Mộ t transaction có th ể có nhi ề u batch  Transaction đượ c b ắ t đ ầ u b ằ ng l ệ nh BEGIN TRANSACTION Và kế t thúc b ằ ng l ệ nh COMMIT TRANSACTION Hoặ c ROLLBACK TRANSACTION GV Phạ m Th ị Lan Anh 42
  43. Ví dụ transaction trong database QLVT BEGIN TRANSACTION insert into chitiethoadon values ('hd001','vt03',50,null,30000) IF @@ERROR 0 BEGIN RAISERROR ('Transaction not completed.', 16, -1) ROLLBACK TRANSACTION END COMMIT TRANSACTION GV Phạ m Th ị Lan Anh 43
  44. Dùng XML  Cho phép Client Browser đị nh d ạ ng d ữ li ệ u:Dùng mệ nh đ ể FOR XML trong l ệ nh SELECT đ ể tr ả k ế t qu ả dạ ng XML  Dùng FOR XML AUTO  Hoặ c FOR XML RAW  Khi dùng mệ nh đ ề FOR XML trong l ệ nh SELECT, ta không thể dùng: Lệ nh SELECT l ồ ng nhau Mệ nh đ ề INTO . Mệ nh đ ề COMPUTE BY. Dùng Stored procedures mà đượ c g ọ i trong l ệ nh INSERT Đị nh nghĩa view ho ặ c user-defined function đ ể tr ả v ề m ộ t rowset. GV Phạ m Th ị Lan Anh 44
  45. Ví dụ dùng XML (1) Use QLVT SELECT makh, tenkh FROM khachhang FOR XML AUTO GV Phạ m Th ị Lan Anh 45
  46. Dùng XML (2) Use QLVT SELECT makh, tenkh FROM khachhang FOR XML RAW GV Phạ m Th ị Lan Anh 46
  47. Dùng database QLVT Viếộạ t m t đo n mã l ệể nh đ cho bi ếổị t t ng tr giá c ủấả a t t c các hoá đơ n c ủ a khách hàng có mã là KH01 trong năm 2000 vớ i k ế t qu ả tr ả v ề Khách hàng KH01 có tổ ng tr ị giá các hoá đ ơ n là Nế u Khách hàng đó không có hoá đ ơ n nào thì in ra chu ỗ i: Khách hàng này không mua hàng trong năm 2000 declare @tg int select @tg=sum(sl*giaban) from chitiethoadon as cthd, hoadon as hd where cthd.mahd = hd.mahd and hd.makh='kh01‘ and year(ngay)=2000 if @tg>0 print 'Khach hang kh01 co tong tri gia ' + str(@tg,10) else print 'Khach hang chua mua hang' GV Phạ m Th ị Lan Anh 47
  48. Dùng database QLDIEM Liệ t kê danh sách các sinh viên g ồ m các thông tin: MASV, HOTEN, NGAYSINH, PHAI (NU hoặ c NAM) c ủ a nh ữ ng sinh viên có tuổ i l ớ n h ơ n ho ặ c b ằ ng 20. GV Phạ m Th ị Lan Anh 48
  49. Khai báo mộ t bi ế n n đ ể ch ứ a s ố l ượ ng các chi tiet hoá đơ n có trong table cthd củ a hoá đ ơ n hd001. N ế u n=0 thì xoá hoá đơ n có mã là hd001 trong table hoá đ ơ n, ngượ c l ạ i thì xu ấ t ra thông báo l ỗ i “Hoa don nay khong xoa duoc” GV Phạ m Th ị Lan Anh 49
  50. Hãy thự c hi ệ n các l ệ nh đ ể xoá m ộ t hoá đơ n có mã hd là hd001. n ế u hoá đ ơ n hd001 có chi tiế t hoá đ ơ n thì ph ả i xoá các chi tiế t hoá đ ơ n c ủ a hd này tr ướ c. Các lệ nh này ph ả i hoàn thành hoàn toàn. GV Phạ m Th ị Lan Anh 50
  51. Lấ y ra danh sách các hoá đ ơ n có t ổ ng tr ị giá lớ n nh ấ t Lấ y ra các khách hàng đã mua nh ữ ng m ặ t hàng mà khách hàng kh01 đã mua. GV Phạ m Th ị Lan Anh 51
  52. Tính số l ượ ng hoá đ ơ n c ủ a 2 khách hàng KH01 và KH02 và in ra kế t qu ả so sánh 2 số l ượ ng hoá đ ơ n này GV Phạ m Th ị Lan Anh 52