Bài giảng thực tập chuyên ngành (sql server)

tương ứng NOT EXISTS) trả về giá trị True (t ương ứng False) nếu kết quả của truy vấn con có ít nhất một d òng (tương ứng không có dòng nào). Điều khác biệt của việc sử dụng EXISTS với hai cách đã nêu ở trên là trong danh sách chọn của truy vấn con có thể có nhiều hơn hai cột. Truy vấn con và mệnh đề HAVING Một truy vấn con có thể được sử dụng trong mệnh đề HAVING của một truy vấn khác. Trong trường hợp này, kết quả của truy vấn con được sử dụng để tạo nên điều kiện đối với các hàm gộp. Bài tập: 1. Cho các lược đồ quan hệ sau: Sach (MaSH, TenSH, TacGia, NXB, NamXB) DocGia (MaDG, TenDG, CMND, NgaySinh, DiaChi) PhieuMuon (MaPM, MaDG, NgayMuon) ChiTietPM (MaCTPN, MaPM, MaSH, NgayTra) a) Viết câu lệnh SQL thực hiện truy vấn sau: Lập danh sách chi tiết phiếu mượn gồm MaSH, TenSH, NgayTra của tất cả các quyển sách trong phiếu mượn có MaPM là „PM0001‟. b) Viết câu lệnh SQL thực hiện truy vấn sau: Lập danh sách gồm MaDG, TenDG của các độc giả mượn sách trong này “24/2/2009”. 31 Chƣơng 7: Sao lƣu và phục hồi CSDL Trong quá trình thực hiện quản trị CSDL SQL Server thì một số nguyên nhân sau đây bắt buộc bạn phải xem xét đến kỹ thuật sao lưu và khôi phục dữ liệu:  Thiết bị lưu trữ (CSDL nằm trên các thiết bị lưu trữ này) bị hư hỏng.  Người dùng vô tình xóa dữ liệu.  Các hành động vô tình hay cố phá hoại CSDL. 7.1. Sao lƣu CSDL Microsoft SQL Server 2005 cung cấp hai kỹ thuật sao lưu CSDL chính: full backup và differential backup. Full backup và Differential backup  Full backup: sao lưu một bản đầy đủ của CSDL tr ên các phương tiện lưu trữ. Quá trình full backup có thể tiến hành mà không cần offline CSDL, nhưng quá trình này lại chiếm một lượng lớn tài nguyên hệ thống và có thể ảnh hưởng nghiêm trọng tới thời gian đáp ứng các yêu cầu của hệ thống.  Differential backup: được xây dựng nhằm làm giảm thời gian cần thiết để thực hiện quá trình full backup. Differential backup chỉ sao lưu những thay đổi trên dữ liệu kể từ lần full backup gần nhất. Trong những hệ thống CSDL lớn, quá tr ình differential backup sẽ sử dụng tài nguyên ít hơn rất nhiều so với quá trình full backup và có thể không ảnh hưởng đến hiệu suất của hệ thống. Quá trình differential chỉ sao lưu những sự thay đổi của dữ liệu từ lần full backup gần nhất, do đó khi có sự cố với CSDL nếu không có bản sao lưu của quá trình full backup thì bản sao lưu của quá trình differential backup sẽ trở nên vô nghĩa. Ví dụ: Công ty XYZ thực hiện full backup vào cuối ngày thứ 6 hàng tuần và thực hiện differential backup vào t ối các ngày từ thứ 2 tới thứ 5. Nếu CSDL có sự cố vào sáng thứ 4, quản trị viên CSDL sẽ phục hồi dữ liệu bằng bản sao lưu của quá trình full backup của ngày thứ 6 tuần trước và sau đó phục hồi các thay đổi của dữ liệu bằng cách áp dụng bản sao lưu của quá trình differential backup vào ngày thứ 3. Transaction log backup Quá trình full backup và differential backup chiếm nhiều tài nguyên hệ thống và ảnh hưởng đến hiệu suất làm việc hệ thống nên thường được thực hiện vào sau giờ làm việc. Tuy nhiên điều này có thể dẫn đến các mất mát dữ liệu trong một ng ày làm việc nếu CSDL có sự cố trước khi quá trình sao lưu diễn ra. Transaction log backup là một giải pháp nhằm giảm thiểu tối đa lượng dữ liệu có thể mất khi có sự cố CSDL. 32 Trong quá trình hoạt động, SQL Server sử dụng transaction log để theo dõi tất cả các thay đổi trên CSDL. Log bảo đảm CSDL có thể phục hồi sau những sự cố đột xuất và cũng đảm bảo người dùng có thể quay ngược các kết quả trong các giao tác CSDL. Các giao tác chưa hoàn thành được lưu trong log trước khi được lưu vĩnh viễn trong CSDL. Transaction log backup sao lưu transactio n log của CSDL vào thiết bị lưu trữ. Mỗi khi transaction log được sao lưu, SQL Server bỏ đi các transaction đã thực hiện thành công (committed tracsaction) và ghi các transaction vào phương ti ện sao lưu. Transaction log backup sử dụng tài nguyên hệ thống ít hơn rất nhiều so với full backup và differential backup, do đó có thể sử dụng transaction log backup bất kỳ thời gian nào mà không sợ ảnh hưởng đến hiệu suất hệ thống. Trở lại với ví dụ về công ty XYZ. Công ty này thực hiện full backup vào tối thứ 6 và differential backup vào tối từ thứ 2 tới thứ 5. Công ty thực hiện thêm quá trình transaction log backup mỗi giờ một lần. Giả sử sự cố CSDL xảy ra vào 9h:05 sáng thứ 4. Quá trình khôi phục lại CSDL nhu sau: Dùng full backup và differential backup c ủa tối thứ 6 và tối thứ 3 để phục hồi lại trạng thái CSDL vào tối thứ 3. Tuy nhiên quá trình này vẫn còn để mất dữ liệu trong 2 giờ (7 – 9h) sáng thứ 4. Tiếp theo sử dụng 2 bản sao lưu transaction backup lúc 8h và 9h sáng để khôi phục CSDL về trạng thái lúc 9h sáng thứ 4. Các thao tác thực hiện quá trình Backup 33 Click OK 34 Click OK. Quá trình sao lưu hoàn tất 7.2. Phục hồi CSDL 35 36 Click OK hai lần 37 Click OK. Quá trình phục hồi hoàn tất Bài tập: Thực hiện sao lưu và phục hồi các cơ sở dữ liệu đã tạo ở chương trước. 38 Chƣơng 8: Lập trình với ADO.NET 8.1. Giới thiệu về nền tảng .NET  Là một công nghệ của MicroSoft Phát triển trên nền tảng ADO.  Cung cấp các lớp đối tượng và hàmg thư viện phục vụ cho việc kết nối và xử lý dữ liệu. Mô hình .NET Framework ADO.NET là một phần của .NET Framework, nó được xem là “bộ thư viện lớp” chịu trách nhiệm xử lý dữ liệu trong ngôn ngữ MS.NET. ADO.NET được thiết kế với dạng dữ liệu “ngắt kết nối”, nghĩa là chúng ta có thể lấy cả một cấu trúc phức tạp của dữ liệu từ database, sau đó ngắt kết nối với database rồi mới thực hiện các thao tác cần thiết. Đây là một sự tiến bộ về mặt thiết kế bởi vì thiết kế ADO trước đây luôn cần duy trì một kết nối trong quá trình thao tác dữ liệu. 8.2. ADO.NET 2.0 và SQL Server .NET Data Providers .NET Data Providers là một tập các đối tượng phục vụ cho việc trao đổi dữ liệu giữa Data Source (dữ liệu nguồn) và đối tượng DataSet. Nó chia ra gồm 2 loại tập đối tượng : 1 tập các đối tượng chịu trách nhiệm quản lý các kết nối (connections) tới DataSource (dữ liệu nguồn) và 1 tập các đối tượng còn lại chịu trách nhiệm xử lý dữ liệu. ADO.NET cung cấp 2 loại .NET Data Providers đó là SQL Server .NET Data Provider và OLE DB .NET Data Provider. SQL Server .NET Data Provider sử dụng truy xuất Database SQL Server 7.0 trở về sau. Còn OLE DB .NET Data Provider sử dụng cho phiên bản 6.5 trở về trước và các loại Database khác (Access, 39 Oracle). SQL Server .NET Data Provider hoạt động hiệu quả hơn bởi vì nó thao tác trực tiếp với SQL Server mà không cần phải thông qua OLE DB Provider. Để sử dụng 2 loại .NET Data Provider này ta phải import 2 Namespace : System.Data.SqlClient cho SQL Server .NET Data Provider và System.Data.OleDb cho OLE DB Data Provider. 8.3. Kết nối đến SQL Server Đối tƣợng Connection Đối tượng Connection là đối tượng chịu trách nhiệm quản lý kết nối tới nguồn dữ liệu (DataSource). Có 2 dạng Connection tương ứng với 2 kiểu dữ liệu SQL Server và OLE DB đó là: SqlConnection và OleDbConnection. Cả 2 đối tượng này đều implement từ interface IDbConnection. Bằng cách sử dụng Interface IDbConnection, các nhà cung cấp dịch vụ Database khác nhau có thể tạo ra các cài đặt phù hợp cho Database riêng của họ. Đối tượng Connection của ADO.NET chỉ nhận một tham số đầu vào là chuỗi kết nối (connection string). Trong chuỗi kết nối, các thông số được cách nhau bằng dấu “;”, connection string có các thông số sau:  Provider: Tên nhà cung cấp Database, đối với OLEDB cần khai báo là SQLOLEDB. Đối với SQL Server thì không thuộc tính này.  DataSource (hoặc Server): tên/địa chỉ database server cần kết nối tới.  Initial catalog (hoặc Database): tên của Database cần truy xuất.  Uid: username để đăng nhập vào Database Server.  Pwd: password để đăng nhập vào Database Server. Còn đây là vd về 1 chuỗi kết nối đối với Database dạng SQL Server: Server=CSC;database=Northwind;uid=sa;pwd=sa; Sau đây là các thuộc tính, phương thức, sự kiện thông dụng của cả SqlConnection và OleDbConnection: Thuộc tính  ConnectionString: thuộc tính thiết lập / lấy chuỗi kết nối.  ConnectionTimeout: thuộc tính thiết lập / lấy thời gian chờ trong khi truy xuất vào database. Khi truy xuất vào Database, chương trình sẽ chờ đúng khoảng thời gian này nếu chờ qua khoảng thời gian này mà vẫn không kết nối được vào database thì chương trình sẽ báo lỗi.  Database: thuộc tính thiết lập/ lấy tên database của đối tượng connection hiện thời.  DataSource: thuộc tính thiết lập/lấy tên của database server của đối tượng connection hiện thời.  State: Lấy trạng thái hiện thời của Connection có các trạng thái sau: Connecting, Broken, Open, Closed, Executing, Fetching. Phương thức 40  BeginTransaction: sử dụng cho trường hợp xử lý giao tác của ứng dụng. Việc xử lý giao tác rất có lợi trong khi xử lý dữ liệu từ database vì có lúc trong khi xử lý dữ liệu gặp lỗi thì có thể thực hiện câu lệnh như Rollback hay trong lúc thao tác cũng có thể thực hiện được các giao tác chính như trên SQL Server như Commit  Close: đóng 1 connection.  Open: Mở 1 connection với các thuộc tính hiện hành.  Sự kiện  InfoMessage: Xảy ra khi provider gửi ra 1 lời cảnh cáo hay thông tin.  StateChange: Xảy ra khi trạng thái của connection thay đổi. Sau đây là đoạn source code sử dụng đối tượng Connection để kết nối vào cở sở dữ liệu: * Dim connString As String = "server=CSC;database=Northwind;uid=sa;pwd=sa" Dim conn As SqlConnection = New SqlConnection(connString) 'mo ket noi conn.Open() 'Thuc hien cac thao tac du lieu 'dong ket noi conn.Close() 8.4. Thực thi các câu lệnh SQL Đối tƣợng Command Sau khi chúng ta đã kết nối vào nguồn dữ liệu, chúng ta cần phải thực hiện thao tác các dữ liệu. Để thao tác được với các dữ liệu chúng ta phải dùng đối tượng Command. Đối tượng Command là đối tượng rất “đa năng”, nó vừa xử lý được sqlserver stored procedures vừa xử lý được các giao tác (transaction). Tương tự như đối tượng Connection, đối tượng Command cũng chia ra làm 2 loại tuỳ theo nguồn dữ liệu: SqlCommand (cho SQL Server) và OleDbCommand (cho OLE DB). Một số thuộc tính và phương thức thông dụng của đối tượng Command: Thuộc tính:  CommandText: Thiết lập/ Lấy lệnh thao tác với dữ liệu.  CommandTimeout: Thiết lập/ Lấy thời gian chờ thực hiện lệnh. Sau khi chờ 1 khoảng thời gian nếu vượt quá sẽ báo lỗi.  CommandType: Thiết lập/ Lấy kiểu của đối tượng lệnh (lệnh trực tiếp, stored procedure)  Parameters: Các tham số truyền vào cho đối tượng command (ở kiểu OleDbParameterCollection/ SQLParameterCollection).  Connection: Thiết lập / lấy kết nối đang được đối tượng Command sử dụng.  Transaction: Thiết lập / lấy giao tác mà đối tượng Command thực thi. 41 Phƣơng thức:  ExecuteReader: Thực thi câu lệnh CommandText của đối tượng Command và trả về kiểu DataReader (OleDbDataReader / SqlDataReader ).  ExecuteNonQuery: Thực thi câu lệnh CommandText của đối tượng Command, đây là dạng câu lệnh cập nhật cơ sở dữ liệu (xoá /sửa) nên chỉ trả về số dòng bị ảnh hưởng mà không trả về dòng dữ liệu nào.  ExecuteScalar: Thực thi câu truy vấn của đối tượng Command và chỉ trả về cột đầu tiên của dòng đầu tiên của kết quả. Các kết quả còn lại bị bỏ qua. - Các hàm khởi tạo của đối tượng SqlCommand (Tương tự cho đối tượng OleDbCommand):  New(): không có tham số nào.  New(cmdText as String) ,trong đó: o cmdText là câu lệnh truyền vào cho đối tượng Command.  New(cmdText as String, connectin as SqlConnection), trong đó: o cmdText như trên. o connection là đối tượng kết nối truyền vào cho đối tượng Command.  New(cmdText as String, connection as SqlConnection, transaction as SqlTransaction), trong đó: o cmdText,connnection như trên. o Transaction: là giao tác truyền cho đối tượng Command. Các ví dụ: - Sử dụng câu lệnh ExecuteNonQuery để update dữ liệu: * Dim connStr As String = "server=CSC;database=Northwind;uid=sa;pwd=sa" Dim cmdStr As String = "Update Customers set CompanyName='CSC' where CustomerID='Seves'" Dim conn As New SqlConnection(connStr) conn.Open() Dim cmd As SqlCommand = New SqlCommand(cmdStr, conn) cmd.ExecuteNonQuery() conn.Close() - Sử dụng câu lệnh ExecuteReader để lấy dữ liệu: * Dim conn As New SqlConnection("server=CSC; database=Northwind;uid=sa; pwd=sa") Dim cmd As New SqlCommand("Select * From Customers", conn) conn.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader() While rdr.Read() Response.Write(rdr.GetString(0) + "") 42 End While rdr.Close() - Sử dụng câu lệnh ExecuteScalar để lấy dữ liệu: * Dim conn As New SqlConnection("server=CSC; database=Northwind2;uid=sa; pwd=sa") conn.Open() Dim cmdStr As String = "Select * From Customers" Dim cmd As SqlCommand = New SqlCommand(cmdStr, conn) Response.Write(cmd.ExecuteScalar()) conn.Close() Bài tập: 1. Trình bày các phương thức và thuộc tính của đối tượng Connection? 2. Trình bày các phương thức và thuộc tính của đối tượng Command? 43 TÀI LIỆU THAM KHẢO [1] Giáo trình hệ quản trị cơ sở dữ liệu SQL Server, Khoa CNTT, Đại học Huế. [2] Giáo trình MSSQL 2005, 44 MỘT SỐ ĐỀ THI MẤU 45 Trường Đại Học Hàng Hải Việt Nam Khoa Công nghệ Thông tin BỘ MÔN HỆ THỐNG THÔNG TIN -----***----- THI KẾT THÚC HỌC PHẦN Tên học phần: THỰC TẬP CHUYÊN NGÀNH (SQL SERVER) Năm học: x Đề thi số: Ký duyệt đề: x Thời gian: 60 phút Câu 1: (5 điểm) Cho lược đồ quan hệ sau: Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) (Suppliers: thông tin về các nhà cung cấp; Parts: thông tin về các loại hàng hóa; Catalog: thông tin về giá bán các loại hàng hóa của các nhà cung cấp). a. Tạo một cơ sở dữ liệu có các bảng tương ứng với lược đồ quan hệ trên. b. Thực hiện truy vấn sau bằng SQL: Hiển thị danh sách tên của những nhà cung cấp có sản phẩm màu đỏ (RED) hoặc màu xanh (GREEN) Câu 2: (5 điểm) Xây dựng một ứng dụng với cơ sở dữ liệu ở câu 1 cho phép người sử dụng cập nhật, sửa, xoá thông tin về các nhà cung cấp, các loại hàng hoá và thông tin về giá bán các loại hàng hoá của các nhà cung cấp. ----------------------------------------***HẾT***---------------------------------------- Lưu ý: - Không sửa, xóa đề thi, nộp lại đề sau khi thi 46 Trường Đại Học Hàng Hải Việt Nam Khoa Công nghệ Thông tin BỘ MÔN HỆ THỐNG THÔNG TIN -----***----- THI KẾT THÚC HỌC PHẦN Tên học phần: THỰC TẬP CHUYÊN NGÀNH (SQL SERVER) Năm học: x Đề thi số: Ký duyệt đề: x Thời gian: 60 phút Câu 1: (5 điểm) Cho lược đồ quan hệ sau: Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) (Suppliers: thông tin về các nhà cung cấp; Parts: thông tin về các loại hàng hóa; Catalog: thông tin về giá bán các loại hàng hóa của các nhà cung cấp). c. Tạo một cơ sở dữ liệu có các bảng tương ứng với lược đồ quan hệ trên. d. Thực hiện truy vấn sau bằng SQL: Hiển thị danh sách tên của những nhà cung cấp có đủ tất cả các loại sản phẩm. Câu 2: (5 điểm) Xây dựng một ứng dụng với cơ sở dữ liệu ở câu 1 cho phép người sử dụng cập nhật, sửa, xoá thông tin về các nhà cung cấp, các loại hàng hoá và thông tin về giá bán các loại hàng hoá của các nhà cung cấp. ----------------------------------------***HẾT***---------------------------------------- Lưu ý: - Không sửa, xóa đề thi, nộp lại đề sau khi thi