›
›

Bài giảng Hệ quản trị cơ sở dữ liệu (SQL Server)

ppt 89 trang hapham 4290
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ quản trị cơ sở dữ liệu (SQL Server)", để 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:

  • pptbai_giang_he_quan_tri_co_so_du_lieu_sql_server.ppt

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu (SQL Server)

  1. Ngôn ngữ định nghĩa dữ liệu ◼ Tạo CSDL ◼ Tạo và quản lý các bảng
  2. Tạo lập CSDL ◼ Các đối tượng của CSDL: ◼ Các bảng dữ liệu (Tables) ◼ Các chỉ mục (Index) ◼ Các khung nhìn (Views) ◼ Các thủ tục lưu trú (Store Procedures) ◼ Các bẫy lỗi (Triggers)
  3. Tạo lập CSDL ◼ Các thông tin cần xác định khi tạo CSDL: ◼ Tên CSDL ◼ Kích thước ◼ Tệp, nhóm tệp lưu trữ CSDL
  4. Tạo lập CSDL ◼ Tạo CSDL bằng T_SQL CREATE DATABASE [ ON [ [ , n ] ] [ , [ , n ] ] ] [ LOG ON { [ , n ] } ] ::= [ PRIMARY ] ( [ NAME = logical_file_name , ] FILENAME = 'os_file_name' [ , SIZE = size ] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) [ , n ] ::= FILEGROUP filegroup_name [ , n ]
  5. Tạo lập CSDL ◼ Tạo CSDL bằng T_SQL ◼ Database_name: ◼ Duy nhất trong server ◼ Tuân theo luật đặt định danh ◼ Độ dài lớn nhất là 128 kí tự ◼ ON ◼ Xác định các tệp (primary file, secondary file) và nhóm tệp lưu trữ CSDL ◼ LOG ON ◼ Xác định các log file ◼ Nếu mệnh đề này không có thì một log file sẽ tụ động được tạo ra.
  6. Tạo lập CSDL ◼ Tạo CSDL bằng T_SQL ◼ Ví dụ 1: tạo CSDL có xác định tệp primary và tệp transaction log: USE master GO CREATE DATABASE qlnv ON ( NAME = qlnv_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\qlnvdat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = qlnv_log, FILENAME = 'c:\program files\microsoft sql server\mssql\data\qlnvlog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO
  7. Tạo lập CSDL ◼ Tạo CSDL bằng Enterprise Manager (EM) ◼ Chọn server group và mở rộng ◼ Nhấp chuột phải trên nút Database/ chọn New Database => Xuất hiện cửa sổ New Database ◼ Nhập tên cho DB cần tạo ◼ Thay đổi một số tham số của DB
  8. Tạo lập CSDL ◼ Chỉnh sửa DB ◼ ALTER DATABASE ◼ Ví dụ 2 USE master GO CREATE DATABASE Test1 ON ( NAME = Test1dat1, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat1 . ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO ALTER DATABASE Test1 ADD FILE ( NAME = Test1dat2, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat2.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO
  9. Tạo lập CSDL ◼ Xem thông tin về các CSDL ◼ sp_helpdb [ [ @dbname= ] 'name' ] ◼ Ví dụ 3 exec sp_helpdb qlnv exec sp_helpdb
  10. Tạo lập CSDL ◼ Xoá CSDL ◼ DROP DATABASE database_name [ , ] ◼ Ví dụ 4 DROP DATABASE qlnv
  11. Kiểu dữ liệu ◼ System data types (Kiểu dữ liệu hệ thống) ◼ User-defined data type ◼ CLR types ◼ Spatial data ◼ Filestreams ◼ XML
  12. Kiểu dữ liệu hệ thống ◼ Exact numeric ◼ bit, tinyint, smallint, int, bigint, numeric, decimal, smallmoney, money ◼ Approximate numeric ◼ float, Real ◼ Date & Time ◼ datetime, smalldatetime ◼ Character string ◼ char, varchar, text ◼ Unicode character string ◼ nchar, nvarchar, ntext ◼ Binary character string ◼ binary, varbinary, image ◼ Others sql_variant, timestamp, xml
  13. Tạo lập các bảng dữ liệu ◼ Tạo bảng bằng T_SQL: CREATE TABLE [ . [ . ]] ( (kích thước 1), (kích thước 2), , (kích thước n) ) ;
  14. Tạo lập các bảng dữ liệu ◼ Tạo bảng bằng T_SQL- Ví dụ 5 CREATE TABLE qlnv.nhan_vien ( manv Smallint, ho_ten VARCHAR(25), ngay_sinh DATETIME, gioi_tinh CHAR(3), ma_dv CHAR(2), luong Int );
  15. Tạo lập các bảng dữ liệu ◼ Tạo bảng bằng EM ◼ Chọn CSDL ◼ Chọn mục Tables ◼ Nhấp chuột phải trên Tables và chọn New Tables
  16. Tạo lập các bảng dữ liệu ◼ Sửa đổi cấu trúc bảng (Thêm, xoá, sửa cột):
  17. Tạo lập các bảng dữ liệu ◼ Xem thông tin bảng ◼ sp_help [ [ @objname = ] name ] ◼ Xoá bảng ◼ DROP TABLE table_name
  18. Ngôn ngữ thao tác dữ liệu ◼ Chèn thêm bản ghi vào bảng dữ liệu ◼ Sửa nội dung trong bảng dữ liệu ◼ Xóa các bản ghi trong bảng dữ liệu ◼ Tìm kiếm thông tin
  19. Chèn thêm bản ghi vào bảng dữ liệu INSERT INTO [( )] VALUES ; ➔ Chèn thêm một bản ghi vào cuối bảng INSERT INTO [( )] SELECT . . . ➔ Chèn thêm một số bản ghi vào cuối bảng
  20. Sửa nội dung trong bảng dữ liệu UPDATE SET = [, = , . . . ] [WHERE (s)] ➔ Cập nhật thông tin trên các cột col_name1, col_name2, . . . cho các bản ghi thỏa mãn điều kiện sau Where
  21. Xóa các bản ghi trong bảng dữ liệu DELETE FROM [WHERE (s)] ➔Xóa tất cả các bản ghi thỏa mãn điều kiện sau mệnh đề Where TRUNCATE TABLE → Xoá tất cả bản ghi trong bảng, lược đồ của bảng vẫn giữ nguyên
  22. Tìm kiếm thông tin ◼ CSDL Quản lý thông tin về các nhân viên ◼ PHONG(Maphong, Tenphong, Diachi, SDT) ◼ DMNN(Mann, Tennn) ◼ NHANVIEN(Manv, Hoten, Ngaysinh, Gioitinh, Luong, Sdt, Maphong) ◼ TDNN(Manv, Mann, Tdo)
  23. Tìm kiếm thông tin ◼ Truy vấn tìm kiếm tổng quát: SELECT *|{[DISTINCT] column|expression [alias], } FROM table(s) [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]]; Nghĩa là: Lấy Từ [Thoả mãn ] [Sắp xếp ]
  24. Tìm kiếm thông tin hoặc: SELECT column,group_function(column) FROM tables [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
  25. Tìm kiếm không điều kiện SELECT *|{[DISTINCT] column|expression [alias], } FROM table(s) Ví dụ 6: Cho biết thông tin về các phòng ban? SELECT Maphong,Tenphong, DiaChi, Tel FROM Phong hoặc: SELECT * FROM Phong * : thay thế tập tên cột của một bảng
  26. Tìm kiếm không điều kiện ▪ Ví dụ 7: Cho biết danh sách thưởng của các nhân viên bao gồm các thông tin mã nhân viên, họ tên, lương, và thưởng (bằng lương + 40% lương)? Câu lệnh: SELECT Manv, Hoten, Luong, 1.4*Luong as Thuong FROM Nhanvien Sau Select có thể chứa một biểu thức. Biểu thức và tên trường đó có thể được đặt tên giả: [As] hoặc Biểu thức chứa giá trị NULL ➔ bt có giá trị NULL
  27. Tìm kiếm không điều kiện ▪ Ví dụ 8: Cho biết mã các ngoại ngữ mà các nhân viên trong cơ quan đã học? Câu lệnh: Select Mann From TDNN hoặc Select Distinct Mann From TDNN Distinct: đưa ra bản ghi đại diện của nhóm bản ghi giống nhau
  28. Tìm kiếm không điều kiện ▪ Ví dụ 9: Đưa ra thông tin về 2 nhân viên đầu tiên trong bảng nhân viên? Câu lệnh: Select top 2* From NHANVIEN top n: đưa ra bản ghi n bản ghi đầu tiên
  29. Tìm kiếm có điều kiện SELECT *|{[DISTINCT] column|expression [alias], } FROM table(s) [WHERE condition(s)] ◼ Các phép so sánh trong Condition(s): >, =, , !> (không lớn hơn), !< (không nhỏ hơn) ◼ Các phép logic trong Condition(s): and, or, not, between and, like, in, is null
  30. Truy vấn có điều kiện ◼Ví dụ 10: Cho biết danh sách các nhân viên làm việc ở phòng có mã số là HCA? Câu lệnh: Select * From Nhanvien Where Maphong = ‘HCA’
  31. Truy vấn có điều kiện ▪ Ví dụ 11: Cho biết mã nhân viên, họ tên, ngày sinh, lương của các nhân viên có lương nằm trong khoảng từ 2000000 đồng đến 3000000 đồng? Câu lệnh: Select Manv, Hoten, Ngaysinh, Luong From Nhanvien Where (Luong 2000000) and (Luong 3000000)
  32. Truy vấn có điều kiện hoặc: Select Manv, Hoten, Ngaysinh, Luong From Nhanvien Where Luong Between 2000000 and 3000000
  33. Truy vấn có điều kiện ▪ Ví dụ 12: Cho biết mã nhân viên, họ tên, ngày sinh, lương của các nhân viên có lương không nằm trong khoảng từ 1000000 đồng đến 2000000 đồng? Select Manv, Hoten, Ngaysinh, Luong From Nhanvien Where Luong not between 1000000 and 2000000
  34. Truy vấn có điều kiện ▪ Ví dụ 13: Đưa ra danh sách các nhân viên chưa có số điện thoại? Select * From Nhanvien Where SDT is Null
  35. Truy vấn có điều kiện ▪ Ví dụ 14: Cho biết các nhân viên làm việc ở phòng có mã phòng là hca hoặc KDA? Câu lệnh: Select * From Nhanvien Where Maphong = ‘P1’ or Maphong = ‘P2’ hoặc: Select * From Nhanvien Where Maphong in (‘P1’, ‘P2’)
  36. Truy vấn có điều kiện ▪ Ví dụ 15: Cho biết danh sách các nhân viên có tên bắt đầu bằng ‘N’? Câu lệnh: Select * From Nhanvien Where HoTen like ‘N%’  trong like dùng các kí hiệu thay thế: % (một xâu kí tự bất kì), _ (một kí tự bất kì)  Like chỉ thao tác trên các trường kiểu xâu kí tự
  37. Truy vấn có sắp xếp dữ liệu ◼ Thêm mệnh đề: [ORDER BY column | expr | alias [ASC|DESC], . . .] Ví dụ 16: Hãy đưa ra danh sách nhân viên theo theo chiều tăng dần của lương? Select Manv, Hoten, Ngaysinh, Luong From NHANVIEN Order By Luong Asc
  38. Truy vấn có sắp xếp dữ liệu Ví dụ 17: Hãy đưa ra danh sách nhân viên theo theo chiều tăng dần của lương, nếu 2 nhân viên có cùng lương thì sắp xếp theo ngày sinh giảm dần? Select Manv, Hoten, Ngaysinh, Luong From NHANVIEN Order By Luong Asc, Ngaysinh Desc  thứ tự sắp xếp từ trái ➔ phải
  39. Một số hàm thông dụng ◼ Các hàm xử lý xâu kí tự ◼ ASCII(s): trả về giá trị mã ASCII của kí tự bên nhất trái của xâu ◼ Char(n): trả ra kí tự thứ n trong bảng mã ASCII ◼ Upper(s): chuyển đổi xâu sang chữ hoa ◼ Lower(s): chuyển đổi xâu sang chữ thường
  40. Một số hàm thông dụng ◼ Các hàm xử lý xâu kí tự (tiếp) ◼ +: phép toán ghép các xâu ◼ Len(s): trả về độ dài của xâu ◼ LTRIM(s): loại bỏ các kí tự trắng bên trái xâu ◼ RTRIM(s): loại bỏ các kí tự trắng bên phải xâu ◼ Left(s, n): trả về n kí tự (xâu con) phía bên trái nhất xâu s ◼ Right(s, n): trả về n kí tự (xâu con) phía bên phải nhất xâu s
  41. Một số hàm thông dụng ◼ Các hàm xử lý xâu kí tự (tiếp) ◼ SubString(s, m ,n): trả về xâu con dài n kí tự của xâu s bắt đầu từ ví trí thứ m ◼ Replace(s, s1, s2): thay thế các xâu con s1 xuất hiện trong xâu s bằng xâu s2 ◼ Stuff (s, m, n, s1): xóa xâu con n kí tự bắt đầu từ vị trí thứ m trong xâu s và thay thế vào đó xâu s1 ◼ Reverse(s): trả về xâu đảo ngược của xâu s
  42. Một số hàm thông dụng ◼ Các hàm ngày tháng ◼ Getdate(): trả về ngày tháng năm của hệ thống ◼ DatePart(tham số datepart,date): trả về giá trị nguyên là thành phần của giá trị date ◼ Các tham số của datepart: ◼ qq,q : Quarter ◼ yy, yyyy : Year ◼ mm, m : Month ◼ dy,y : Dayofyear ◼ dd,d : Day ◼ wk, ww : Week ◼ dw : Weekday ◼ hh : Hour ◼ mi,n : Minute ◼ ss,s : Second ◼ ms : Milisecond
  43. Một số hàm thông dụng ◼ Các hàm ngày tháng (tiếp) ◼ DateDiff(datepart, date1,date2): trả về khoảng ngày, tháng, năm, . . . giữa 2 ngày date1 và date2 ◼ Dateadd(datepart,n,date): thêm một số n datepart vào date ◼ Day(d): trả về giá trị số nguyên của ngày ◼ Month(d): trả về giá trị số nguyên của tháng ◼ Year(d): trả về giá trị số nguyên của năm
  44. Một số hàm thông dụng ◼ Các hàm toán học (tiếp) ◼ Square(n) ◼ Sqrt(n) ◼ Round(f,n) ◼ Power(f,n) ◼ Pi() ◼ Sign(f)
  45. Một số hàm thông dụng ◼ Các hàm chuyển đổi ◼ Convert(datatype[(size) ], exp [,style])
  46. Một số hàm thông dụng ◼ Các hàm nhóm dữ liệu: ◼Count ([All | Distinct] Biểu thức): Hàm đếm số lượng các bộ thỏa mãn điều kiện ◼Max (Biểu thức), Min (Biểu thức): Hàm tính giá trị lớn nhất, nhỏ nhất của tập giá trị được tính theo biểu thức
  47. Một số hàm thông dụng Sum ([All | Distinct] Biểu thức kiểu số): Hàm tính tổng của tập giá trị được tính theo biểu thức Avg ([All | Distinct] Biểu thức kiểu số): Hàm tính giá trị trung bình của tập giá trị thuộc tính kiểu số:
  48. Một số hàm thông dụng Ví dụ 18: Cho biết lương trung bình của phòng có mã KDA? Select Avg(Luong) as TBluong From NHANVIEN Where Maphong= ’KDA’
  49. Một số hàm thông dụng Ví dụ 19: Cho biết tổng số nhân viên và trung bình lương của cơ quan? Select count(*) as TongNv, Avg(Luong) as TBluong From NHANVIEN
  50. Truy vấn nhóm dữ liệu NHANVIEN 2250000 Tính trung 2280000 bình lương cho mỗi phòng trong bảng NHANVIEN. 2500000
  51. Truy vấn nhóm dữ liệu SELECT column, group_function(column) FROM tables [WHERE condition] [GROUP BY group_by_expression [HAVING group_condition]] [ORDER BY column];
  52. Truy vấn nhóm dữ liệu Ví dụ 20: Cho biết trung bình lương của mỗi phòng? Select Maphong, avg(Luong) as TBLuong From NHANVIEN Group By Maphong
  53. Truy vấn nhóm dữ liệu Ví dụ 21: Cho biết các phòng có trung bình lương lớn hơn 2250000? Select Maphong, avg(Luong) as TBLuong From NHANVIEN Group By Maphong Having avg(Luong) >=2250000
  54. Truy vấn nhóm dữ liệu Chú ý: ◼ Tất cả các cột hoặc biểu thức sau SELECT nếu không nằm trong hàm mhóm dữ liệu đều phải xuất hiện sau GROUP BY ◼ Các cột hoặc biểu thức xuất hiện sau GROUP BY không nhất thiết phải xuất hiện sau SELECT ◼ Các điều kiện nhóm dữ liệu phải đặt sau HAVING (không đặt sau WHERE)
  55. Truy vấn có liên kết ◼ Các loại truy vấn liên kết: ◼ Liên kết trong (inner join) ◼ Liên kết ngoài (outer join) ◼ Liên kết chéo (cross join) ◼ Tự liên kết (self_join)
  56. Truy vấn có liên kết ◼ Truy vấn liên kết trong (inner join): SELECT *|{[DISTINCT] column|expression [alias], } FROM table1, table2 WHERE table1.col1 table2.col2 [and condition(s)] [ORDER BY column asc|desc, . . .]; Hoặc:
  57. Truy vấn có liên kết SELECT *|{[DISTINCT] column|expression [alias], } FROM table1 INNER JOIN table2 ON table1.col1 table2.col2 [WHERE condition(s)] [ORDER BY column asc|desc, . . .];  Trả ra các bản ghi của hai bảng mà giá trị của col1 liên kết được với ít nhất một giá trị ở col2
  58. Truy vấn có liên kết Ví dụ 22: Với mỗi nhân viên, cho biết mã nhân viên, họ tên, mã phòng và tên phòng họ làm việc? Select manv, hoten, nhanvien.maphong, tenphong From Phong, nhanvien Where phong.maphong=nhanvien.maphong Hoặc: Select manv, hoten, nv.maphong, tenphong From nhanvien nv Inner join phong p On nv.maphong=p.maphong
  59. Truy vấn có liên kết
  60. Truy vấn có liên kết Ví dụ 23: Với mỗi nhân viên (mã nhân viên, họ tên) cho biết các ngoại ngữ mà nhân viên đó học (tên ngoại ngữ, trình độ)? Select manv, hoten, tennn,tdo From nhanvien nv, tdnn td, dmnn dm Where nv.manv =td.manv and dm.mann = td.mann Hoặc: Select manv, hoten, tennn,tdo From nhanvien nv Inner join tdnn td On nv.manv =td.manv Inner join dmnn dm On dm.mann = td.mann
  61. Truy vấn có liên kết ◼ Truy vấn liên kết ngoài: SELECT *|{[DISTINCT] column|expression [alias], } FROM table1 LEFT| RIGHT| FULL OUTER JOIN table2 ON table1.col1 table2.col2 [WHERE condition(s)] [ORDER BY column(s)];
  62. Truy vấn có liên kết ◼ Ví dụ 24: Đưa ra danh sách tất cả các nhân viên cùng với thông tin về phòng ban của họ (kể cả các nhân viên chưa ở phòng nào)? SELECT * FROM Nhanvien nv LEFT OUTER JOIN phong p ON nv.maphong = p.maphong  Trả ra tất cả các bản ghi của nhanvien và chỉ các bản ghi trong phong liên kết với ít nhất một bản ghi bên bảng nhanvien.
  63. Truy vấn có liên kết ◼ Ví dụ 25: Đưa ra danh sách tất cả các phòng cùng với thông tin về các nhân viên của các phòng (kể cả các phòng chưa có nhân viên nào)? SELECT * FROM nhanvien nv RIGHT OUTER JOIN phong p ON nv.maphong = p.maphong  Trả ra tất cả các bản ghi của phong và chỉ các bản ghi so khớp trong nhanvien.
  64. Truy vấn có liên kết ◼ Ví dụ 26: SELECT * FROM nhanvien nv FULL OUTER JOIN phong p ON nv.maphong = p.maphong  Kết hợp của right và left outer join
  65. Truy vấn có liên kết ◼ Truy vấn liên kết chéo (cross join): SELECT *|{[DISTINCT] column|expression [alias], } FROM table1 CROSS JOIN table2 [WHERE condition(s)] [ORDER BY column(s)];  Trả ra tích đề các của hai bảng
  66. Truy vấn có liên kết ◼ Truy vấn tự liên kết (self_join): là truy vấn liên kết mà một bảng lại liên kết với chính nó Ví dụ 27: Dua ra nhan vien co luong lon hon luong mot nhan vien nao do cung phong Select from inner join on and
  67. RÀNG BUỘC DỮ LIỆU (CONSTRAINS) ◼ Các thao tác làm biến đổi dữ liệu của bảng: cập nhật, thêm bản ghi, xóa bản ghi. ◼ Dữ liệu của một số bảng khi có những thay đổi phải tuân theo một số quy luật nào đó nhằm đảm bảo dữ liệu đang tồn tại trong CSDL luôn nhất quán. Các quy luật đó được gọi là các ràng buộc dữ liệu. ◼ Mỗi khi có một thao tác nào đó xảy ra làm thay đổi dữ liệu thì tất cả các ràng buộc sẽ được tự động được kiểm tra lại. Nếu dữ liệu mới không thỏa mãn các ràng buộc thì SQL server sẽ trả về một thông báo lỗi.
  68. RÀNG BUỘC DỮ LIỆU (CONSTRAINS) ◼ Các loại ràng buộc ◼ Ràng buộc khai báo: ◼ ràng buộc cột (miền giá trị): xác định miền giá trị mà thuộc tính sẽ nhận được. ◼ ràng buộc về tính toàn vẹn của thực thể: chỉ rõ khóa của bảng ◼ ràng buộc toàn vẹn tham chiếu: liên quan đến việc quản lý các mối quan hệ giữa các bản ghi của hai bảng (mối quan hệ giữa khóa chính và khóa phụ). ◼ Ràng buộc phức tạp: sự thay đổi DL của một bảng → sự thay đổi DL của một loạt các bảng khác mà phải thực hiện theo một giải thuật (trigger, store procedure).
  69. RÀNG BUỘC DỮ LIỆU (CONSTRAINS) a. Ràng buộc miền giá trị ◼ Not null ◼ Nếu trong khai báo thuộc tính có mệnh đề Not Null thì bắt buộc mỗi bản ghi tại thuộc tính đó phải có chứa giá trị (không được để trống). ◼ Mặc định khi khai báo cột là Null. ◼ Default : đặt giá trị mặc định cho thuộc tính ◼ Được dùng trong trường hợp thêm mới bản ghi. ◼ Không liên quan đến hành động nhập hay xóa. ◼ Nếu giá trị đưa vào khác Null thì giá trị mặc định bị thay thế, ngược lại thì giá trị mặc định không thay đổi.
  70. RÀNG BUỘC DỮ LIỆU (CONSTRAINS) ◼ Các cột dữ liệu tham gia vào quá trình tính toán nên cho giá trị mặc định. ◼ Các thuộc tính không chấp nhận giá trị Null nên đặt giá trị mặc định. ◼ Unique: ◼ Các giá trị trong cột phải duy nhất. ◼ Cho phép xác định khóa dự tuyển cho bảng. ◼ Primary key: thiết đặt khóa chính cho bảng ◼ Check ( ): ◼ giới hạn tập giá trị nhận được của một thuộc tính.
  71. RÀNG BUỘC DỮ LIỆU (CONSTRAINS) ◼ Ví dụ: CREATE TABLE giang_vien( Magv char(5), Ho_ten varchar(35) constrains NN_HT_GV NOT NULL, Ngay_sinh datetime, Gioi_tinh varchar(3) DEFAULT ‘Nam’, Ma_khoa char(2) REFERENCES KHOA(ma_khoa), Luong bigint check(luong>=1000000), Trinh_do varchar(15) NOT NULL CHECK(trinh_do IN (‘Đại học’, ‘Thạc sĩ’, “Tiến sĩ”)) Constrains PK_GV primary key(Magv, hoten) )
  72. RÀNG BUỘC DỮ LIỆU (CONSTRAINS) ◼ Thêm ràng buộc vào bảng đã tồn tại: ALTER TABLE ADD CONSTRAINT Ví dụ: ALTER TABLE giang_vien ADD CONSTRAINT PK PRIMARY KEY(Magv)
  73. RÀNG BUỘC DỮ LIỆU (CONSTRAINS) ◼ Loại bỏ ràng buộc trong bảng: ALTER TABLE DROP CONSTRAINT Ví dụ: ALTER TABLE giang_vien DROP CONSTRAINT PK
  74. RÀNG BUỘC DỮ LIỆU (CONSTRAINS) b. Ràng buộc toàn vẹn tham chiếu: dùng để thiết lập mối quan hệ giữa hai bảng [CONSTRAINT ] [FOREIGN KEY ] REFERENCES [( )] [ON UPDATE {CASCADE| NO ACTION}] [ON DELETE {CASCADE| NO ACTION}] CASCADE: các bản ghi trong bảng tham chiếu được xóa hoặc được cập nhật theo bản ghi ở bảng được tham chiếu. NO ACTION: khóa chính bảo toàn.
  75. RÀNG BUỘC DỮ LIỆU (CONSTRAINS) Ví dụ: CREATE TABLE giang_vien( Magv char(5) PRIMARY KEY, Ho_ten varchar(35) NOT NULL, Ngay_sinh datetime, Gioi_tinh vachar(3) DEFAULT ‘Nam’, Ma_khoa char(2) REFERENCES KHOA(ma_khoa), Luong bigint, Trinh_do varchar(15) NOT NULL CHECK(trinh_do IN (“Đại học”, “Thạc sĩ”, “Tiến sĩ”)) )
  76. VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE) ◼ Khai báo biến trong SQL server ◼ Cú pháp DECLARE @ [, @ , . . .] Ví dụ: DECLARE @x1 char(4) DECLARE @x2 int, @a float DECLARE @Tong_gia_tri numeric; ◼ Gán giá trị cho biến: SET = Ví dụ: SET @x1 =‘CNTT’; SET @a = 200; SET @Tong_gia_tri = (SELECT sum(soluong*dongia)/1000000 from CPP);
  77. VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE) ◼ In giá trị biến: Print(‘Tổng giá trị các chuyến hàng:’ + str(@Tong_gia_tri)); Chú ý: các biến khai báo trong cùng một bó chỉ có tác dụng trong bó đó.
  78. VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE) ◼ SP được xây dựng từ các câu lệnh T-SQL và được lưu trú trên SQL server. ◼ Muốn thực hiện một SP, NSD chỉ cần thực hiện một lời gọi hàm. ◼ Khi SP được chạy lần đầu tiên nó sẽ được biên dịch qua 5 bước và sinh ra một mô hình truy vấn. Mô hình này sẽ được đặt trong một CSDL của SQL server, lần sau chạy lại thủ tục sẽ không phải dịch lại nữa.
  79. VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE) ◼ Năm bước biên dịch thủ tục: ◼ Thủ tục được phân tích ra thành nhiều phần ◼ Kiểm tra sự tồn tại của các đối tượng (view, table, ) mà thủ tục tham chiếu tới. ◼ Lưu trữ tên thủ tục vào bảng sysobject, lưu trữ các mã lệnh của thủ tục vào bảng syscomments. ◼ Sinh ra mô hình truy vấn của thủ tục và lưu vào bảng sysprocedure ◼ Khi SP được chạy lần đầu tiên, cây truy vấn sẽ được đọc và được tối ưu thành một kế hoạch thủ tục và chạy → tiết kiệm thời gian tái phân tích, biên dịch cây truy vấn mỗi khi chạy thủ tục.
  80. VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE) ◼ Trong một phiên làm việc, nếu SP được thực hiện, nó sẽ được lưu trữ vào vùng nhớ đệm. Những lần sau nếu SP được gọi thực hiện lại thì nó sẽ được đọc trực tiếp ra từ vùng nhớ đệm → nâng cao hiệu suất chạy truy vấn.
  81. VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE) ◼ Cú pháp lệnh tạo SP: CREATE PROC[EDURE] [@ [= ] [OUTPUT], . . .] [WITH RECCOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION] AS Giải thích: - = : gán giá trị ban đầu cho tham số - OUTPUT: giá trị thay đổi của tham số ở trong thủ tục được đưa ra bên ngoài thủ tục. Ví dụ
  82. VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE) ◼ Thực hiện thủ tục: EXEC ◼ Thay đổi thủ tục ALTER PRO[CEDURE] . . . ◼ Xóa thủ tục DROP PRO[CEDURE] ◼ Bó thủ tục
  83. VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE) ◼ Sử dụng tùy chọn RECOMPILE trong câu lệnh CREATE PRO: ◼ Thủ tục sẽ được biên dịch lại từ đầu mỗi khi thực hiện ◼ Khi biên dịch lại các thông tin mới của SP được cập nhật và tối ưu lại. ◼ Sử dụng tùy chọn RECOMPILE trong câu lệnh EXEC ◼ Thủ tục sẽ được biên dịch lại trong lần thực thi đó và lưu lại kế hoạch thực thi mới của nó vào vùng nhớ đệm. ◼ Biên dịch lại tất cả các thủ tục của bảng: EXEC sp_recompile
  84. VIII. HÀM CỦA NSD (USER DEFINED FUNCTIONS-UDFs) ◼ UDFs giống như SP nhưng khác ở các điểm sau: UDF SP - Giá trị các tham số không - Có thể đưa giá trị của tham được truyền ra ngoài. số ra ngoài bằng thuộc tính - Trả ra một giá trị bằng mệnh OUTPUT đề RETURN - Có thể trả về một giá trị vô - Chỉ trả về kiểu DL giá trị hướng hoặc một bảng dữ kiểu nguyên liệu.
  85. VIII. HÀM CỦA NSD (USER DEFINED FUNCTIONS-UDFs) ◼ Cú pháp câu lệnh: CREATE FUNCTION ([@ [= ] [, . . .]]) RETURNS | [WITH ENCRYPTION] AS BEGIN [ ] RETURN | ( )