Hệ quản trị cơ sở dữ liệu sql server

BỘ THÔNG TIN & TRUYỀN THÔNGTRƯỜNG CAO ĐẲNG CNTT HỮU NGHỊ VIỆT - HÀNHỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 30LT+30THBiên soạn: Nguyễn Văn Lợi1SQL Server 2005Mục tiêu của học phầnMục tiêu về kiến thức:Cung cấp cho sinh viên những nội dung:Kiến trúc của HQT CSDL SQL Server.Các công cụ, đối tượng của SQL Server.Cài đặt hệ quản trị CSDL SQL Server.Tạo và khai thác cơ sở dữ liệu.Ngôn ngữ T-SQL.Lập trình thủ tục, hàm và Trigger trong SQL Server.Sao lưu và phục hồi dữ liệu.Bảo mật và quản lý người dùng.2SQL Server 2005Mục tiêu của học phầnMục tiêu về kỹ năng: Giúp sinh viên có thể:Tạo lập được CSDL trong SQL ServerThành thạo ngôn ngữ T-SQLLập trình với CSDL trong SQL ServerQuản trị SQL Server3SQL Server 2005TÀI LIỆU THAM KHẢONguyễn Văn Lợi (2009), Giáo trình Hệ quản trị Cơ sở Dữ liệu SQL Server, Trường Cao đẳng CNNT hữu nghị Việt – Hàn.Nguyễn Thiên Bằng – Phương Lan (2006), Khám phá SQL Server 2005, NXB Lao động Xã hội, Hà Nội.Phạm Hữu Khang – Đoàn Thiện Ngân (2007), SQL Server 2005 lập trình T-SQL, NXB Lao Động Xã Hội, Hà Nội.Phạm Hữu Khang – Đoàn Thiện Ngân (2008), SQL Server 2005 lập trình thủ tục và hàm, NXB Lao Động Xã Hội, Hà Nội.4SQL Server 2005TÀI LIỆU THAM KHẢOPhạm Hữu Khang (2007), Lập trình ứng dụng chuyên nghiệp SQL Server 2000, NXB Lao Động Xã Hội, Hà Nội.Trần Đăng Công (2006), Giáo trình quản trị và phát triển ứng dụng với microsoft SQL Server, Học Viện Quân SựRobin Dewson, Beginning SQL Server 2005 for DeveloperSQL Server 2005 Books Online5SQL Server 2005Chương 1: Tổng quan về SQL Server 2005Chương 2: CSDL trong SQL ServerChương 3: Phát biểu T-SQL dạng truy vấn dữ liệuChương 4: Phát biểu T-SQL dạng xử lý dữ liệuChương 5: Thủ tục nội tại (Stored Procedure) Chương 6: HàmChương 7: TriggerChương 8: Bảo mật và quản trị người dùng BỘ THÔNG TIN & TRUYỀN THÔNGTRƯỜNG CAO ĐẲNG CNTT HỮU NGHỊ VIỆT - HÀNNội dung chính :6SQL Server 2005Chương 1TỔNG QUAN VỀ SQL SERVER 2005 1.1 Lịch sử phát triển của SQL Server1.2 Giới thiệu về SQL Server 20051.3 Các ấn bản SQL Server 20051.4 Giới thiệu các công cụ1.5 Cài đặt1.6 CSDL mẫu7SQL Server 20051.1 Lịch sử phát triển của SQL Server (1/2)Năm 1989, Microsoft hợp tác với công ty Sybase và Ashton-Tate để cho ra SQL Server 1.0 for OS/2.Sybase SQL Server 3.0 được phát triển để thực thi trên môi trường hệ điều hành UNIX và VMS.Microsoft SQL Server 4.2 được giới thiệu năm 1992.Microsoft SQL Server 4.21 for Windows NT được ra đời cùng thời gian với Windows NT 3.1.8SQL Server 20051.1 Lịch sử phát triển của SQL Server (2/2)Microsoft SQL Server 6.0 là phiên bản đầu tiên mà Microsoft phát triển độc lập không có sự hợp tác với các hãng khác.Các phiên bản SQL Server tiếp theo là: 6.5, 7.0, 2000, 2005 và mới nhất là SQL Server 2008. 9SQL Server 20051.2 Giới thiệu về SQL Server 2005 (1/4)Là một hệ quản trị cơ sở dữ liệu (HQTCSDL) quan hệ hoạt động theo mô hình khách chủ cho phép đồng thời nhiều người dùng cùng truy xuất đến dữ liệu, quản lý việc truy nhập hợp lệ và các quyền của từng người dùng trên mạng.Nâng cao hiệu năng, độ tin cậy, khả năng lập trình đơn giản và dễ sử dụng hơn so với các phiên bản trước đó.Tập trung vào khả năng xử lý giao dịch trực tuyến trên diện rộng, ứng dụng vào thương mại điện tử và kho dữ liệu (Data warehousing).10SQL Server 20051.2 Giới thiệu về SQL Server 2005 (2/4)Ngôn ngữ truy vấn của Microsoft SQL Server là Transact-SQL (T-SQL).Ngôn ngữ T-SQL trong SQL Server 2005 mở rộng dựa trên chuẩn ANSI SQL-99 trong khi SQL Server 2000 mở rộng dựa trên chuẩn ANSI SQL-92. 11SQL Server 20051.2 Giới thiệu về SQL Server 2005 (3/4)Ngôn ngữ truy vấn của Microsoft SQL Server là Transact-SQL (T-SQL).Ngôn ngữ T-SQL trong SQL Server 2005 mở rộng dựa trên chuẩn ANSI SQL-99 trong khi SQL Server 2000 mở rộng dựa trên chuẩn ANSI SQL-92. 12SQL Server 20051.2 Giới thiệu về SQL Server 2005 (4/4)Những tính năng mới của SQL Server 2005 so với SQL Server 2000 là:Nâng cao bảo mậtMở rộng T-SQLTăng cường hỗ trợ người phát triển ứng dụngTăng cường khả năng quản trị CSDLNâng cao độ sẵn sàng và mở rộng của CSDLTăng cường khả năng khai thác thông tin 13SQL Server 20051.3. Các ấn bản SQL Server 2005Microsoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Workgroup EditionMicrosoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Express Edition14SQL Server 20051.4. Giới thiệu các công cụ1.4.1 SQL Server Configuration Manager1.4.2 SQL Server Management Studio 15SQL Server 20051.4.1 Configuration Manager (1/2)Để thay đổi cách khởi động một thể hiện SQL Server hoặc điều chỉnh cấu hình SQL Server. Để chạy trình quản lý cấu hình SQL Server ta thực hiệnStart/ Programs/ Microsoft SQL Server 2005/ Configuration Tools/ SQL Server Configuration Manager. 16SQL Server 20051.4.1 Configuration Manager (2/2)17SQL Server 20051.4.2 SQL Server Management StudioLà công cụ chính dùng để quản trị CSDL.Nó tích hợp những tác vụ quản lý cơ sở dữ liệu và lập trình trong cùng một màn hình giao diện người dùng. Management Studio có giao diện tương tự như Visual Studio 2005.Nó thay thế cho hai cộng cụ Enterprise Manager và Query Analyzer trong SQL Server 200018SQL Server 20051.4.2 SQL Server Management StudioEnterprise ManagerQuery Analyzer19SQL Server 20051.4.2 SQL Server Management Studio20SQL Server 20051.4.2.1 Thực thi Management StudioStart/ Programs / Microsoft SQL Server 2005 / SQL Server Management StudioHộp thoại Connect to Server xuất hiện.21SQL Server 20051.4.2.1 Thực thi Management StudioĐể kết nối tới thể hiện cục bộ của bộ máy CSDL SQL Server, trong hộp danh sách Server type chọn Database Engine.Trong hộp danh sách Server Name chọn hoặc nhập vào: dấu chấm hoặc chữ local hoặc nhập tên Server.22SQL Server 20051.4.2.1 Thực thi Management StudioChọn phương pháp chứng thực trong hộp Authentication.Windows Authentication thì kích nút Connect để thực hiện kết nối.SQL Server Authentication, cung cấp tên đăng nhập trong hộp danh sách Login Nếu chọn Remember password thì kể từ lần đăng nhập sau bạn không cần phải cung cấp lại mật khẩu nữa.Kích nút Connect để kết nối.23SQL Server 20051.4.2.2 Sử dụng các Server đã đăng kýHiển thị Registered Servers, chọn View/ Registered Servers.24SQL Server 20051.4.2.2 Sử dụng các Server đã đăng kýRegistered Servers được sử dụng cho các mục đích sau:Lưu thông tin kết nối cho các thể hiện SQL Server trên mạng.Hiển thị một thể hiện đang thực thi hay khôngKết nối tới một thể hiện trong Object Explorer hoặc Query Editor.Soạn thông tin về một Server đã đăng ký.Nhóm các Server. 25SQL Server 20051.4.2.2 Sử dụng các Server đã đăng kýĐăng ký một thể hiện SQL ServerKết nối tới một thể hiện SQL Server và hiển thị nó trong cửa sổ Object ExplorerKết nối tới thể hiện SQL Server và tạo truy vấn mới đến thể hiện này 26SQL Server 20051.4.2.3 Công cụ Object ExplorerKhi mở Management Studio thì mặc định cửa sổ Object Explorer hiển thị bên trái màn hình. Nếu Object Explorer chưa hiển thị bạn chọn View/ Object Explorer. 27SQL Server 20051.4.2.3 Công cụ Object ExplorerObject Explorer cho phép bạn thực hiện các tác vụ quản lý trong Management Studio tùy thuộc vào thể hiện bạn đang chọn.Tạo và quản lý CSDL.Xem và thay đổi các thuộc tính CSDL.Tạo vào quản lý các đối tượng CSDL như bảng, view,Cấp phát và thu hồi các quyền và nhóm quyềnCấu hình replication.Quản lý các gói Integration Services.Xem các tập tin log của Windows và SQL Server.Quản lý các SQL Server Agent 28SQL Server 20051.4.2.4 Cửa sổ Summary 29SQL Server 20051.4.2.4 Cửa sổ SummaryTrong cửa sổ Summary có một số nút công cụ để điều chỉnh việc hiển thị thông tin. Mặc định nút List được chọn.Chọn nút Report để xem báo cáo về một đối tượng. Ví dụ để xem báo cáo thông tin sử dụng đĩa của CSDL DB_LOI bạn thực hiện như sau:Chọn CSDL DB_LOIKích vào mục Disk Usage trong danh sách chọn của nút Report. Cửa sổ hiển thị Disk Usage của CSDL DB_LOI như sau: 30SQL Server 20051.4.2.4 Cửa sổ SummaryCửa sổ hiển thị Disk Usage của CSDL DB_LOI như sau: 31SQL Server 20051.4.2.5 Truy vấn dữ liệu trong cửa sổ Query Management Studio hỗ trợ 5 kiểu truy vấn: T-SQL: Dựa trên bộ máy CSDL quan hệ. MDX (Multi – Demensional Expression): Dựa trên Analysis Services. DMX (Data Mining Extensions): Dựa trên Analysis Services. XMLA (XML for Analysis): Dựa trên Analysis Services. SQL Server Mobile Query: Dựa trên ấn bản SQL Server 2005 Mobile. 32SQL Server 20051.4.2.5 Truy vấn dữ liệu trong cửa sổ Query Để thực hiện truy vấn dữ liệu đơn giản dùng T-SQL trên CSDL, thực hiện như sau:Bước 1: Kích nút New Query hoặc Database Engine Query. Cửa sổ Query mở ra hoặc một thẻ Query mới được thêm vào cửa sổ QueryBước 2: Nhập phát biểu T-SQL vào.Bước 3: Kích nút Execute (hoặc nhấn F5) để thực thi câu lệnh. Kết quả nhận được kết xuất theo dạng văn bản, dạng bảng hoặc dạng tập tin tùy chọn. Mặc định kết xuất dạng bảng được chọn. 33SQL Server 20051.4.2.6 Các tùy chọnChọn Tool/ Options.Trong cửa sổ Options, bạn mở các nút tùy chọn bên trái để xem các tùy chọn34SQL Server 20051.4.2.6 Các tùy chọnThiết lập tùy chọn StartupTrên nhóm Environment, bạn chọn thẻ General. Trong mục At startup cho phép lựa chọn cửa sổ nào được hiển thị khi mở Managerment Studio. Mặc định cửa sổ Object Explorer được mở. Để thay đổi cách bố trí các cửa sổ theo mặc định ban đầu, bạn chọn Window/ reset window layout.35SQL Server 20051.4.2.6 Các tùy chọnThay đổi cách hiển thị kết quả truy vấnChọn nút Query Results, rồi chọn nút SQL Server. Trong hộp danh sách Default destination for result bạn chọn dạng kết xuất mặc định cho kết quả truy vấn:Results to grids: Xuất kết quả truy vấn ra dạng bảngResults to text: Xuất kết quả truy vấn ra dạng văn bảnResults to file: Xuất kết quả truy vấn ra dạng tập tinDefault location for saving query results: Mặc định nơi lưu trữ kết quả truy vấn.36SQL Server 20051.4.2.7 Sử dụng mã T-SQL mẫu Chọn View/ Template Explore.Trong cửa sổ Template Explorer, kích đôi chuột vào tên mẫu cần dùng, đoạn mã T-SQL mẫu được hiển thị trong cửa sổ Query mới 37SQL Server 20051.5 Cài đặt1.5.1 Yêu cầu phần cứng1.5.2 Yêu cầu hệ điều hành1.5.3 Các bước cài đặt38SQL Server 20051.5.1. Yêu cầu phần cứngBộ xử lýBộ xử lý có tốc độ 700MHz hoặc cao hơn.Bộ nhớ SQL Server 2005 Enterprise Edition yêu cầu bộ nhớ tối thiểu là 512 MB. SQL Server 2005 Standard Edition, Developer Edition và Express Edition yêu cầu bộ nhớ tối thiểu như cài Windows.Đĩa cứngCài các thành phần của Database yêu cầu không gian bộ nhớ từ 95 đến 300 MB, tuy nhiên nó còn phụ thuộc vào các lựa chọn cụ thể. Nếu bạn cài đặt tất cả các thành phần của SQL Server thì không gian đĩa còn trống tối thiểu là 1GB.39SQL Server 20051.5.2. Yêu cầu hệ điều hànhBảng tương thích giữa các phiên bản của SQL Server 2005 và hệ điều hành Windows:40SQL Server 20051.5.3. Các bước cài đặtViệc cài đặt các ấn bản SQL Server 2005 về cơ bản tương tự nhau Các bước cài đặt được trình bày chi tiết trong phần thực hành41SQL Server 20051.5.4. Gỡ bỏStart\ Settings \ Control Panel, cửa sổ Control Panel xuất hiện, chọn mục Add or Remove Programs. Cửa sổ Add or Remove Programs xuất hiện như sau: 42SQL Server 2005Chương 2CƠ SỞ DỮ LIỆU TRONG SQL SERVER 2.1. Giới thiệu CSDL trong SQL Server 2.2. Tạo cơ sở dữ liệu2.3. Sao chép cơ sở dữ liệu2.4. Gán và tách cơ sở dữ liệu2.5. Nhập và xuất cơ sở dữ liệu2.6. Kiểu dữ liệu và cách khai báo2.7. Bảng2.8. View 43SQL Server 20052.1. Giới thiệu CSDL trong SQL Server SQL Server thuộc họ các sản phẩm và công nghệ mà kho dữ liệu phù hợp với các yêu cầu môi trường xử lý giao dịch trực tuyến (Online Transaction Processing - OLTP) và xử lý phân tích trực tuyến (Online Analytical Processing - OLAP). SQL Server là HQTCSDL quan hệ với các tính năng sau:Quản lý kho dữ liệu cho các giao dịch (Transactions) và phân tích (Analysis).Khả năng lưu trữ dữ liệu với các kiểu dữ liệu có miền giá trị rộng, bao gồm text, numeric, Extensible Markup Language (XML) và các đối tượng lớn44SQL Server 20052.1. Giới thiệu CSDL trong SQL Server Đáp ứng các yêu cầu từ các ứng dụng Client.Sử dụng T-SQL và XML để gởi các yêu cầu giữa ứng dụng client và Server.HQTCSDL SQL Server đáng tin cậy đối với:Duy trì các mối quan hệ giữa các đối tượng dữ liệu trong một CSDL.Bảo đảm rằng dữ liệu được lưu trữ đúng đắn, các quy tắc định nghĩa các quan hệ giữa các đối tượng dữ liệu là không bị vi phạm.Khôi phục tất cả dữ liệu khi hệ thống bị lỗi.45SQL Server 20052.1.1. Các cơ sở dữ liệu xử lý giao dịch trực tuyến (Online transaction processing – OLTP)Các table quan hệ tổ chức dữ liệu trong một cơ sở dữ liệu OLTP để giảm thiểu thông tin dư thừa và tăng tốc độ cập nhật.SQL Server cho phép số lượng lớn người dùng thực thi các giao dịch và sự thay đổi đồng thời dữ liệu theo thời gian thực trong các cơ sở dữ liệu OLTP. 46SQL Server 20052.1.2. Các cơ sở dữ liệu xử lý phân tích trực tuyến (online analytical processing - OLAP)Công nghệ OLAP được sử dụng để tổ chức và tổng hợp số lượng dữ liệu lớn để một người phân tích có thể đánh giá dữ liệu một cách nhanh chóng và theo thời gian thực.Microsoft SQL Server Analysis Services tổ chức dữ liệu này để hỗ trợ các giải pháp đối với doanh nghiệp lớn, xuất phát từ các báo cáo công ty và sự phân tích để mô hình hóa dữ liệu và hỗ trợ quyết định.47SQL Server 20052.1.3. Cân nhắc khi hoạch định cơ sở dữ liệu Mục tiêu của kho dữ liệu: Các CSDL OLTP và OLAP có các mục tiêu khác nhau và vì vậy các yêu cầu thiết kế cũng sẽ khác nhau.Tần suất giao dịch: Một thiết kế đạt hiệu suất cao liên quan đến một mức độ thích hợp của sự chuẩn hóa, các chỉ mục, và sự phân chia dữ liệu có thể đạt được một mức độ rất cao của tần suất giao dịch.48SQL Server 20052.1.3. Cân nhắc khi hoạch định cơ sở dữ liệu Khả năng tăng trưởng về khía cạnh vật lý của kho lưu trữ: Số lượng dữ liệu lớn đòi hỏi phần cứng phù hợp đối với bộ nhớ, dung lượng đĩa cứng, và khả năng của đơn vị xử lý trung tâm.File location: Nơi mà bạn đặt các tập tin có thể có tác động ảnh hưởng về hiệu suất thực thi. Nếu có thể, bạn nên sử dụng nhiều ổ đĩa, dàn trải các tập tin cơ sở dữ liệu của bạn trên nhiều hơn một đĩa. Điều này cho phép SQL Server nhận được sự thuận lợi của việc có nhiều kết nối và nhiều đầu đọc đĩa cho việc đọc và ghi dữ liệu hiệu quả.49SQL Server 20052.1.4. Cấu trúc cơ sở dữ liệuSQL Server quản lý trực tiếp các CSDL. Mỗi CSDL SQL Server sẽ quản lý các cấu trúc vật lý của nó. Việc quản trị cơ sở dữ liệu có một số đặc điểm sau:Để Client khai thác CSDL trước hết phải thực hiện kết nối đến Server quản trị CSDL đó.Chỉ thực hiện khai thác với các CSDL có tên trong danh sách các CSDL mà Server quản lý.Không có các phương thức mở CSDL trực tiếp từ tập tin như Access.Khi đã kết nối đến Server, Client chỉ thực hiện được quyền khai thác theo quy định đã định sẵn trong CSDL (phân quyền trong CSDL).50SQL Server 20052.1.4. Cấu trúc cơ sở dữ liệuCơ sở dữ liệu trong SQL Server lưu trữ theo 2 phần: phần dữ liệu (gồm một tập tin bắt buộc *.mdf và các tập tin phụ *.ndf) và phần nhật ký (*.ldf).51SQL Server 20052.1.5. Sơ đồ quản trị cơ sở dữ liệuCơ sở dữ liệu trong SQL Server chia thành 2 loại: Cơ sở dữ liệu hệ thống (do SQL Server sinh ra khi cài đặt) và cở sở dữ liệu người dùng (do người dùng tạo ta).52SQL Server 20052.1.5. Sơ đồ quản trị cơ sở dữ liệuMaster: Ghi lại tất cả thông tin hệ thống của SQL Server như: tài khoản đăng nhập, các cài đặt cấu hình hệ thống.Tempdb: Cơ sơ dữ liệu này là tài nguyên dùng chung cho tất cả các người dùng truy cập đến một thể hiện của SQL Server. Nó lưu giữ các tất cả các bảng và thủ tục nội tại tạm thời.53SQL Server 20052.1.5. Sơ đồ quản trị cơ sở dữ liệuModel: Được sử dụng như một mẫu cho tất cả các CSDL được tạo ra trong một thể hiện của SQL Server .Msdb: được sử dụng bởi SQL Server, Management Studio và SQL Server Agent để lưu trữ dữ liệu, bao gồm các thông tin lập lịch và thông tin quá trình backup restore hệ thống .54SQL Server 20052.1.6 Cấu trúc vật lý của CSDLNhư cấu trúc các CSDL hệ quản trị CSDL thông thường, SQL Server cũng quản lý tập tin dữ liệu của CSDL ở dạng vật lý theo trang (page) và phân đoạn (extent).55SQL Server 20052.1.6 Cấu trúc vật lý của CSDLPageSQL Server quản lý một page có kích thước là 8KB, như vậy 1MB có 128 page, trong mỗi trang có 96 byte chứa thông tin của trang 56SQL Server 20052.1.6 Cấu trúc vật lý của CSDLExtent: là đơn vị dùng chứa các Table và Index. Mỗi extent có 8 page hay 64KB. SQL Server có 2 kiểu extent:Uniform: Chỉ dùng lưu trữ cho một đối tượngMixform: Có thể dùng lưu trữ 8 đối tượng.57SQL Server 20052.1.6 Cấu trúc vật lý của CSDLCấu trúc Extent như sau:58SQL Server 20052.2. Tạo cơ sở dữ liệuTrong khung Object Explorer, kích nút phải chuột tại mục Databases, chọn New Database. Cửa sổ New Database xuất hiện như sau:59SQL Server 20052.3. Sao chép cơ sở dữ liệu Cách 1:Kích chuột phải vào CSDL muốn sao chép, chọn Task/ Copy Database, chọn Next.Cửa sổ Select a Source Server xuất hiện,Chọn Server nguồn chứa database bạn cần sao chép trong mục Source Server.Kích vào biểu tượng để lựa chọn Server.Chọn phương pháp chứng thực quyền truy cập Server là Use Windows Authentication hay Use SQL Server Authentication, kích chọn Next.60SQL Server 20052.3. Sao chép cơ sở dữ liệu Cách 1:Cửa sổ Select a Destination Server tương tự như cửa sổ Select a Source Server, chọn Server đích chứa database bạn cần sao chép tới trong mục Source Server. Mặc định là local (Server cục bộ), kích chọn Next.61SQL Server 20052.3. Sao chép cơ sở dữ liệu Cách 1:Cửa sổ Select the Trasfer Method yêu cầu bạn chọn phương thức truyền.Use the detach and attach method: Sử dụng phương thức truyền này nhanh hơn, nhưng yêu cầu CSDL ở chế độ offline. Phương thức này tốt nhất để nâng cấp CSDL hay di chuyển CSDL rất lớn. Yêu cầu khi sử dụng lựa chọn này thì không có user nào được phép kết nối đến CSDL nguồn.Use the SQL Managerment Object method: Phương thức này chậm hơn nhưng cho phép database nguồn có thể vẫn còn ở chế độ online. Kích chọn Next. 62SQL Server 20052.3. Sao chép cơ sở dữ liệu Cách 1:Trong cửa sổ Select Database, bạn chọn CSDL cần sao chép, kích chọn Next.Cửa sổ Configure Destination Database xuất hiện yêu cầu bạn :Nhập tên Database mới vào mục Destination DatabaseChọn thư mục chứa các tập tin CSDL mới trong mục Destination Folder63SQL Server 20052.3. Sao chép cơ sở dữ liệu Cách 1:Trong cửa sổ Configure the Package cho phép tạo một gói tích hợp dịch vụ (Integration Services package) với tên được chỉ định trong mục Package name. Chọn Next để tiếp tục.Trong cửa sổ Schedule the Package, bạn chọn Run immediately nếu muốn thực hiện sao chép database ngay. Còn chọn mục Schedule nếu muốn lập lịch trình để sao chép database. Chọn Next để tiếp tục.Trong cửa sổ Complete the Wizard, chọn Finish. 64SQL Server 20052.3. Sao chép cơ sở dữ liệu Cách 2:Bước 1: Dừng các dịch vụ SQL Server 2005Bước 2: Truy cập vào thư mục chứa tập tin CSDL cần sao chép (các tập tin này thường có phần tên trùng với tên CSDL và phần mở rộng là mdf và ldf).Bước 3: Khởi động lại các dịch vụ được dừng ở bước 1.65SQL Server 20052.4. Gán và tách cơ sở dữ liệu 2.4.1 Gán cơ sở dữ liệu Gán (Attach) cơ sở dữ liệu là kỹ thuật ghép nối tập tin CSDL vào thể hiện, Các bước thực hiện như sau:Trong khung Object Explorer của cửa sổ SQL Server Management Studio, Chọn Instance cần Attach CSDL kích nút phải tại mục Databases, chọn Attach, cửa sổ Attach Databases xuất hiện như sau:66SQL Server 20052.4.1 Gán cơ sở dữ liệuKích vào nút Add, chọn đến thư mục chứa tập tin có đuôi “mdf” của cơ sở dữ liệu cần Attach, Chọn Ok, Chọn tiếp OK. 67SQL Server 20052.4.2 Tách cơ sở dữ liệuTách (Detach) là bước thực hiện loại bỏ CSDL ra khỏi thể hiện, thể hiện không quản lý CSDL nhưng khác với xóa CSDL là các tập tin chứa CSDL vẫn còn.Kích nút phải lên CSDL cần tách, chọn Tasks/ DetachTrong cửa sổ Detach Database, Nhấn Ok. 68SQL Server 20052.5 Nhập và xuất dữ liệu2.5.1 Nhập dữ liệuNhập (Import) dữ liệu từ ngoài vào CSDL SQL Server, dữ liệu có thể từ hệ quản trị CSDL khác hoặc CSDL khác của SQL Server.Kích chuột phải lên Databases cần Import dữ liệu, chọn Tasks/ Import Data...Trong cửa sổ Welcome to SQL Server Import and Export Wizard, bạn chọn Next. 69SQL Server 20052.5.1 Nhập dữ liệuCửa sổ Choose a Data Source xuất hiện:70SQL Server 20052.5.1 Nhập dữ liệuChọn kiểu nguồn dữ liệu trong mục Data Source (SQL Server, Oracle, Access,...)Chọn tập tin cơ sở dữ liệu trong mục file nameChọn Next71SQL Server 20052.5.1 Nhập dữ liệuTrong cửa sổ Choose a Destiation bạn cần chỉ định nơi cần sao chép dữ liệu tới bằng cách:72SQL Server 20052.5.1 Nhập dữ liệuChọn tên Server trong mục Server nameChọn tên CSDL trong mục Database (có trước hoặc tạo tại thời điểm này bằng cách chọn New)Chọn Next.73SQL Server 20052.5.1 Nhập dữ liệuTrong cửa sổ Specify Table Copy or Query: yều cầu bạn chọn cách chuyển dữ liệuCopy data from one or more tables or views: Sao chép dữ liệu từ một hoặc nhiều bảng hay view.Write a query to specify the data to transfer: Viết một câu truy vấn để chỉ định dữ liệu cần chuyển.74SQL Server 20052.5.1 Nhập dữ liệuTrong cửa sổ Select Source Tables and ViewsChọn các bảng, view cần Import và nhận trong mục Tables and Views.Chọn Next.Trong cửa sổ Save and Execute Package, chọn NextTrong cửa sổ Complete the Wizard, chọn FinishCửa sổ Operation stopped cho hiển thị kết quả chuyển dữ liệu, chọn Close 75SQL Server 20052.5.2 Xuất dữ liệuDùng để xuất (export) dữ liệu từ một CSDL của SQL Server ra một hệ quản trị CSDL khác hoặc một CSDL khác của SQL Server.Các bước thực hiện Export dữ liệu tương tự như Import nhưng thực hiện Data Source là SQL Server, còn Destination là hệ quản trị CSDL khác hoặc CSDL khác của SQL Server.76SQL Server 20052.6 Kiểu dữ liệu và cách khai báo . Một số kiểu dữ liệu mới được giới thiệu trong phiên bản cơ sở dữ liệu SQL Server 2005 là xml, char(max) và varchar(max).Việc chọn kiểu dữ liệu phải phù hợp dữ liệu của thế giới thực, đồng thời nó còn phụ thuộc vào các kiểu dữ liệu có sẵn của SQL Server 2005.Mục đích cuối cùng của việc chọn lựa kiểu dữ liệu đúng là nó sẽ đem lại lợi ích về lưu trữ cũng như cải thiện được khả năng truy cập dữ liệu khi thi hành.77SQL Server 20052.6 Kiểu dữ liệu và cách khai báo . Các kiểu dữ liệu trong SQL Server 2005 được phân thành 4 nhóm:System Data TypesUser-Defined Data TypesUser-Defined TypesXml Schema Collections78SQL Server 20052.6 Kiểu dữ liệu và cách khai báo . Nhóm kiểu dữ liệu System Data Types bao gồm:Exact NumbericsApproximate NumericsDate and TimeCharacter StringsUnicode Character StringsBinary StringsOther Data Types79SQL Server 20052.6.1. Nhóm Exact NumbericsBIT: kiểu số nguyên 1 byte để lưu trữ một trong hai giá trị 0 hay 1. Ví dụ: Cột thể hiện giới tính là Nam (1) hay Nữ (0) được khai báo như sau:Gioitinh bit not null default 1TINYINT: kiểu số nguyên 1 byte để lưu trữ các giá trị nằm trong khoảng từ 0 đến 25580SQL Server 20052.6.1. Nhóm Exact NumbericsSMALLINT: kiểu số nguyên 1 byte để lưu trữ các giá trị nằm trong khoảng từ -32.768 đến 32.767.INT: kiểu số nguyên 4 byte để lưu trữ các giá trị nằm trong khoảng từ -2.147.483.648 đến 2.147.483.647.BIGINT: kiểu số nguyên 8 byte để lưu trữ các giá trị nằm trong khoảng từ - 9.223.372.036.854.775.808 đến 9.223.372.036.854.775.807.81SQL Server 20052.6.1. Nhóm Exact Numberics DECIMAL hoặc NUMERIC: kiểu dữ liệu sử dụng 9 byte để lưu trữ số chấm động trong khoảng -1038+1 đến 1038-1SMALLMONEY: dùng 4 byte để lưu trữ số kiểu tiền tệ trong khoảng -214.748,3648 đến -214.748,3647MONEY: dùng 8 byte để lưu trữ số kiểu tiền tệ trong khoảng -922.337.203.685.477,5808 đến 922.337.203.685.477,580782SQL Server 20052.6.2. Nhóm Approximate Numberics. FLOAT: dùng 4 hay 8 byte để lưu số dấu chấm động hợp lệ trong khoảng từ -1,79E+308 đến -2.23E-308, giá trị 0 và từ 2.23E-308 đến 1.79E+308.REAL: dùng 4 byte để lưu số dấu chấm động hợp lệ trong khoảng từ -3.40E + 38 đến -1.18E - 38, giá trị 0 và 1.18E - 38 đến 3.40E + 38.83SQL Server 20052.6.3 Nhóm Date and Time DATETIME: dùng 8 byte để lưu giá trị thời gian xảy ra trong khoảng từ 1/1/1753 đến 31/12/9999.SMALLDATETIME: dùng 4 byte để lưu giá trị thời gian xảy ra trong khoảng từ 1/1/1900 đến 6/6/2079.84SQL Server 20052.6.4 Nhóm Character Strings CHAR: cho phép bạn lưu chuỗi có chiều dài cố định tối đa là 8000 ký tự không hỗ trợ Unicode.Ví dụ: SOCMND char(9) not nullVARCHAR: cho phép bạn lưu chuỗi có chiều dài cố định tối đa là 8000 ký tự không unicode, khi bạn sử dụng kiểu này thì đòi hỏi dữ liệu phải có chiều dài không thay đổi.TEXT: khai báo chuỗi có chiều dài thay đổi, các ký tự không hỗ trợ Unicode và chiều dài tối đa của chuỗi là 2^31-1 ký tự.85SQL Server 20052.6.4 Nhóm Character Strings CHAR: cho phép bạn lưu chuỗi có chiều dài cố định tối đa là 8000 ký tự không unicode.Ví dụ: SOCMND char(9) not nullVARCHAR: cho phép bạn lưu chuỗi có chiều dài cố định tối đa là 8000 ký tự không unicode, khi bạn sử dụng kiểu này thì đòi hỏi dữ liệu phải có chiều dài không thay đổi.TEXT: khai báo chuỗi có chiều dài thay đổi, các ký tự không hỗ trợ Unicode và chiều dài tối đa của chuỗi là 2^31-1 ký tự.86SQL Server 20052.6.5 Nhóm Character Strings NCHAR [ ( n ) ]: khai báo chuỗi ký tự có chiều dài cố định, các ký tự hỗ trợ Unicode và có độ dài là n byte. N phải có giá trị từ 1 đến 4.000, nếu không có khai báo n thì mặc định n=1.NVARCHAR [ ( n | max ) ]: khai báo chuỗi có chiều dài thay đổi, các ký tự hỗ trợ Unicode và có độ dài tối đa là n byte. N phải có giá trị từ 1 đến 4.000. Nếu khai báo max thì kích thước lưu trữ tối đa là 2^31-1 byte.NTEXT: khai báo chuỗi có chiều dài thay đổi hỗ trợ Unicode, chiều dài tối đa là 2^30-1 ký tự. 87SQL Server 20052.6.6 Nhóm Binary StringsBINARY [ ( n ) ]: Kiểu dữ liệu nhị phân có độ dài cố định là n byte. N nhận giá trị từ 1 đến 8000VARBINARY [ ( n | max) ]: Kiểu dữ liệu nhị phân có độ dài thay đổi. N nhận giá trị từ 1 đến. khai báo Max cho biết kích thước tối đa là 2^31-1 bytes.IMAGE: Cho phép lưu dữ liệu kiểu dữ liệu nhị phân thay đổi có chiều dài tối đa là 2^31-1 bytes.88SQL Server 20052.6.7 Nhóm Orther Data TypesSQL_VARIANT: Kiểu dữ liệu cho phép lưu trữ các giá trị với nhiều kiểu dữ liệu khác nhau ngoại trừ kiểu text, ntext, image, timestamp và sql_variant.TIMESTAMP: Kiểu dữ liệu nhị phân tự động tạo ra duy nhất trong CSDL. Kiểu dữ liệu này có kích thước 8 byte.89SQL Server 20052.6.7 Nhóm Orther Data TypesUNIQUEIDENTIFIER: Kiểu dữ liệu nhị phân tự động tạo ra duy nhất trong CSDL. Kiểu dữ liệu này có kích thước 16 byte.XML: Kiểu dữ liệu dùng để lưu trữ các tài liệu hay đoạn (fragment) XML, dung lượng tối đa của kiểu dữ liệu này là 2 GB. 90SQL Server 20052.7 Bảng2.7.1 Tạo bảngKích đôi chuột lên Database cần tạo bảngKích phải chuột lên mục Tables và chọn New Table. Một bảng Table – dbo.Table_1 hiển thị bên phải màn hình Management Studio. Bảng này gọi là Table Designer, có chứa các cột91SQL Server 20052.7.1 Tạo bảngColumn Name: Chứa tên cột của bảng sẽ tạo.Data type: Chỉ ra kiểu dữ liệu cho cột.Allow Nulls: Nếu được chọn thì cột này chấp nhận giá trị null (rỗng).Nhập tên cột và kiểu dữ liệu của nó.Chọn các cột khóa chính (Primary key), kích chuột vào biểu tượng để thiết lập.Kích vào nút Save hoặc nhấn Ctrl + S để lưu.92SQL Server 20052.7.2 Thiết lập quan hệ cho cơ sở dữ liệuQuan hệ giúp tránh được nguy cơ dữ liệu không nhất quán. Nếu bảng có quan hệ với bảng khác, nó sẽ có một cột là khóa chính trong một bảng khác. Cột đó gọi là khóa ngoại (Foreign key).Sau khi hoàn tất việc thiết kế các bảng dữ liệu liên quan, bạn tiến hành tạo quan hệ giữa chúng với nhau bằng đối tượng Database Diagram.93SQL Server 20052.7.2 Thiết lập quan hệ cho cơ sở dữ liệuNếu trước đó bạn chưa tạo quan hệ cho CSDL, khi chọn vào ngăn Database Diagram thì thông báo xuất hiện như sau: 94SQL Server 20052.7.2 Thiết lập quan hệ cho cơ sở dữ liệuKích nút phải chuột lên mục Database Diagrams, chọn New Database Diagram. Cửa sổ Add Table xuất hiện như sau:95SQL Server 20052.7.2 Thiết lập quan hệ cho cơ sở dữ liệuChọn bảng cần tạo quan hệ rồi nhấn nút Add, nhấn Close để đóng cửa sổ này lại. Cửa sổ Database Diagram xuất hiện như sau:96SQL Server 20052.7.2 Thiết lập quan hệ cho cơ sở dữ liệuThiết lập quan hệ giữa các bảng bằng cách chọn cột dữ liệu là khóa chính ở bảng thứ nhất rồi kéo thả vào cột dữ liệu ứng với khóa ngoại ở bảng thứ hai. Cửa sổ Tables and Columns xuất hiện như sau:97SQL Server 20052.7.2 Thiết lập quan hệ cho cơ sở dữ liệuChọn cột dữ liệu làm khóa chính và khóa ngoại, kích OK. Cửa sổ Foreign Key Relationship xuất hiện. Bạn hiệu chỉnh một số thuộc tính cần thiết rồi kích OKLưu đối tượng Diagram 98SQL Server 20052.7.2. Thiết lập quan hệ cho cơ sở dữ liệuChú ý:Bạn nên thiết lập quan hệ giữa các bảng trước khi thêm dữ liệu vào bảng.Trường hợp dữ liệu đã tồn tại, để thiết lập quan hệ bạn nên kiểm tra tính thống nhất của dữ liệu trong các bảng dữ liệu mà bạn sẽ thiết lập quan hệ.99SQL Server 20052.7.3. Thêm ràng buộc cho bảngSQL Server 2005 cho phép bạn ràng buộc giá trị trong một cột xác định để dữ liệu không thích hợp sẽ không được thêm vào. Kiểu dữ liệu của cột cũng được phép ràng buộc giá trị trong cột, nhưng nó không được xem là ràng buộc.100SQL Server 20052.7.3. Thêm ràng buộc cho bảngNOT NULL: Khi ràng buộc không rỗng được chỉ ra, cột bắt buộc phải có giá trị khi bạn thêm dữ liệu vào bảng.CHECK: Ràng buộc kiểm tra chỉ ra miền giá trị được phép. Ví dụ: cột lan (lần thi) của bảng THI chỉ nhận giá trị là 1 hoặc 2.UNIQUE: Ràng buộc duy nhất chỉ ra rằng giá trị nhập vào một cột phải duy nhất.PRIMARY KEY: Ràng buộc khóa chính dùng để xác định duy nhất một dòng dữ liệu.FOREIGN KEY: Ràng buộc khóa ngoại dùng để tham chiếu đến một nhận dạng duy nhất trong một bảng khác trong CSDL.101SQL Server 20052.7.3 Thêm ràng buộc cho bảngVí dụ 1: Thêm ràng buộc UNIQUE cho cột TenMH (vì tên các môn học không thể trùng nhau) của bảng MONHOC thực hiện như sau:Nhấp phải chuột lên bảng MONHOC chọn Modify.Trong bảng Table Designer, nhấp phải chuột lên dòng TenMH chọn Indexes/ Keys. Hộp thoại Indexes/ Keys xuất hiện.102SQL Server 20052.7.3. Thêm ràng buộc cho bảngNhấn nút Add, chọn giá trị cho dòng Type là Unique key.Nhấn Close để đóng hộp thoại.103SQL Server 20052.7.3. Thêm ràng buộc cho bảngVí dụ 2: Thêm ràng buộc CHECK cho cột DIEM trong bảng THI như sau:Nhấp phải chuột lên bảng MONHOC chọn Modify.Trong bảng Table Designer, nhấp phải chuột lên dòng DIEM chọn Check Constraints. Hộp thoại Check Constraints xuất hiện.Nhấn nút Add để tạo ràng buộc CHECK. Chọn cột bên phải dòng Expression và chọn nút (). Hộp thoại Check Constraints Expression. Để cột DIEM nhận giá trị từ 0 đên 10 bạn nhập: DIEM>=0 AND DIEMLớn hơn=Lớn hơn hoặc bằngKhác!>Không lớn hơn!b)141SQL Server 20053.5.2 Kiểm tra giới hạn của dữ liệu Ví dụ 1: Hiển thị mã sinh viên thi lần 1 học phần có mã là ‘Sql’ từ 8 đến 10 điểm.Ví dụ 2: Hiển thị mã sinh viên thi lần 1 học phần có mã là ‘Tindc’ có điểm là 5 hoặc 6 hoặc 7 điểm.142SQL Server 20053.5.3 Danh sách (IN và NOT IN)Từ khoá IN được sử dụng khi ta cần chỉ định điều kiện tìm kiếm dữ liệu cho câu lệnh SELECT là một danh sách các giá trị.Sau IN (hoặc NOT IN) có thể là một danh sách các giá trị hoặc là một câu lệnh SELECT khác.143SQL Server 20053.5.3 Danh sách (IN và NOT IN)Ví dụ 1: Hiển thị mã sinh viên thi lần 1 học phần có mã là ‘SQL’ có điểm là 6, 8 hoặc 10Ví dụ 2: Hiển thị tên khoa chưa có (không quản lý) sinh viên. 144SQL Server 20053.5.4 Toán tử LIKE và các ký tự đại diện Từ khoá LIKE (NOT LIKE) sử dụng nhằm mô tả khuôn dạng của dữ liệu cần tìm kiếm.Chúng được kết hợp với các ký tự đại diện :Ký tự đại diệný nghĩa%Chuỗi ký tự bất kỳ gồm không hoặc nhiều ký tự_Ký tự đơn bất kỳ[]Ký tự đơn bất kỳ trong giới hạn được chỉ định (ví dụ [a-f]) hay một tập (ví dụ [abcdef])[^]Ký tự đơn bất kỳ không nằm trong giới hạn được chỉ định ( ví dụ [^a-f] hay một tập (ví dụ [^abcdef]).145SQL Server 20053.5.4 Toán tử LIKE và các ký tự đại diện Ví dụ 1: Cho biết masv,hodem,ten của các sinh viên có họ là NguyễnVí dụ 2: Cho biết masv,hodem,ten của các sinh viên có tên gồm ba ký tự và có ký tự cuối cùng là NVí dụ 3: Cho biết họ và tên của các sinh viên có tên bắt đầu bằng ký tự V hoặc L 146SQL Server 20053.5.5 Giá trị NULL Trong mệnh đề WHERE, để kiểm tra giá trị của một cột có giá trị NULL hay không, ta sử dụng cách viết: WHERE tên_cột IS NULLhoặc: WHERE tên_cột IS NOT NULL Ví dụ: Hiển thị tên khoa chưa nhập dữ liệu về điện thoại.147SQL Server 20053.6 Tạo mới bảng dữ liệu từ kết quả của câu lệnh SELECT Câu lệnh SELECT ... INTO có tác dụng tạo một bảng mới có cấu trúc và dữ liệu được xác định từ kết quả của truy vấn.Bảng mới được tạo ra sẽ có số cột bằng số cột được chỉ định trong danh sách chọn và số dòng sẽ là số dòng kết quả của truy vấn.Ví dụ: truy vấn dữ liệu từ bảng SINHVIEN và tạo một bảng SINHVIENNU bao gồm các trường MASV, HODEM, TEN và NGAYSINH.148SQL Server 20053.7 Sắp xếp kết quả truy vấn Mặc định, các dòng dữ liệu trong kết quả của câu truy vấn tuân theo thứ tự của chúng trong bảng dữ liệu hoặc được sắp xếp theo chỉ mục (nếu trên bảng có chỉ mục).Trong trường hợp muốn dữ liệu được sắp xếp theo chiều tăng hoặc giảm của giá trị của một hoặc nhiều trường, ta sử dụng thêm mệnh đề ORDER BY trong câu lệnh SELECTSau ORDER BY là danh sách các cột cần sắp xếp (tối đa là 16 cột). Dữ liệu được sắp xếp có thể theo chiều tăng (ASC) hoặc giảm (DESC), mặc định là sắp xếp theo chiều tăng.149SQL Server 20053.7 Sắp xếp kết quả truy vấn Ví dụ 1: Hiển thị danh sách các học phần và sắp xếp theo chiều giảm dần của số tín chỉ.Chú ý:Nếu sau ORDER BY có nhiều cột thì việc sắp xếp dữ liệu sẽ được ưu tiên theo thứ tự từ trái qua phải.Thay vì chỉ định tên cột sau ORDER BY, ta có thể chỉ định số thứ tự của cột cần được sắp xếp. Câu lệnh ở ví dụ trên có thể được viết lại như sau: 150SQL Server 20053.7 Sắp xếp kết quả truy vấn Ví dụ 1: Hiển thị danh sách sinh viên nam, kết quả trả về sắp xếp tăng dần theo tên, họ đệm gồm các thông tin: hodem, ten, ngaysinhVí dụ 2: Hiển thị masv của 2 sinh viên thi học phần có mã là tindc có điểm cao nhất. 151SQL Server 20053.8 Toán tử Union,Except và Intersect3.8.1 Toán tử Union3.8.2 Toán tử Except3.8.3 Toán tử Intersect152SQL Server 20053.8.1 Toán tử UnionToán tử Union được sử dụng trong trường hợp ta cần gộp kết quả của hai hay nhiều truy vấn thành một tập kết quả duy nhất. SQL cung cấp toán tử UNION để thực hiện phép hợp. Cú pháp như sau.Câu_lệnh_1UNION [ALL] Câu_lệnh_2[UNION [ALL] Câu_lệnh_3]...[UNION [ALL] Câu_lệnh_n][ORDER BY cột_sắp_xếp] [COMPUTE danh_sách_hàm_gộp [BY danh_sách_cột]] 153SQL Server 20053.8.1 Toán tử UnionVí dụ: Giả sử ta có hai bảng Table1 và Table2 lần lượt như sau: 154SQL Server 20053.8.1 Toán tử UnionCâu lệnh SELECT A,B FROM Table1 UNION SELECT D,E FROM table2 Cho kết quả như sau:155SQL Server 20053.8.1 Toán tử UnionMặc định, nếu trong các truy vấn thành phần của phép hợp xuất hiện những dòng dữ liệu giống nhau thì trong kết quả truy vấn chỉ giữ lại một dòng. Nếu muốn giữ lại các dòng này, ta phải sử dụng thêm từ khoá ALL trong truy vấn thành phần. Ví dụ: Câu lệnh SELECT A,B FROM Table1 UNION ALL SELECT D,E FROM table2 156SQL Server 20053.8.1 Toán tử UnionDanh sách cột trong các truy vấn thành phần phải có cùng số lượng.Các cột tương ứng trong tất cả các bảng, hoặc tập con bất kỳ các cột được sử dụng trong bản thân mỗi truy vấn thành phần phải cùng kiểu dữ liệu.Các cột tương ứng trong bản thân từng truy vấn thành phần của một câu lệnh UNION phải xuất hiện theo thứ tự như nhau.157SQL Server 20053.8.1 Toán tử UnionKhi các kiểu dữ liệu khác nhau được kết hợp với nhau trong câu lệnh UNION, chúng sẽ được chuyển sang kiểu dữ liệu cao hơn (nếu có thể được).Tiêu đề cột trong kết quả của phép hợp sẽ là tiêu đề cột được chỉ định trong truy vấn đầu tiên.158SQL Server 20053.8.2 Toán tử ExceptToán tử Except được sử dụng trong trường hợp bạn cần liệt kê danh sách những bản ghi tồn tại bên bản thứ nhất mà không tồn tại trong bảng thứ hai.Ví dụ: Hiển thị Masv chưa có điểm thi một học phần nào SELECT MASV FROM SINHVIEN EXCEPT SELECT MASV FROM DIEMTHI159SQL Server 20053.8.2 Toán tử ExceptToán tử Except được sử dụng trong trường hợp bạn cần liệt kê danh sách những bản ghi tồn tại bên bản thứ nhất mà không tồn tại trong bảng thứ hai.Ví dụ: Hiển thị Masv chưa có điểm thi một học phần nào SELECT MASV FROM SINHVIEN EXCEPT SELECT MASV FROM DIEMTHI160SQL Server 20053.8.3 Toán tử IntersectIntersect dùng để lấy các bản ghi vừa tồn tại trong bảng thứ nhất vừa tồn tại trong bảng thứ hai.Ví dụ: Hiển thị Makhoa có lớp trực thuộc. SELECT Makhoa FROM KHOA INTERSECT SELECT MAKHOA FROM LOP Ví dụ: Hiển thị Mã sinh viên thi lại cả hai học phần có mã là Tindc và Tinvp161SQL Server 20053.9 Phép nối Để truy vấn dữ liệu từ hai hay nhiều bảng, ta phải sử dụng đến phép nối.Để thực hiện được một phép nối, cần phải xác định được những yếu tố sau:Những cột nào cần hiển thị trong kết quả truy vấnNhững bảng nào có tham gia vào truy vấn.Điều kiện để thực hiện phép nối giữa các bảng dữ liệu là gì.162SQL Server 20053.9.1 Phép nối trongPhép nối trong sử dụng từ khóa INNER JOIN là phép kết nối bằng. Điều kiện để thực hiện phép nối trong được chỉ định trong mệnh đề FROM theo cú pháp như sau: Tên_bảng_1 [INNER] JOIN tên_bảng_2 ON điều_kiện_nối163SQL Server 20053.9.1 Phép nối trongVí dụ : Để hiển thị họ tên và ngày sinh của các sinh viên lớp Dữ liệu 1, ta sử dụng câu lệnh: SELECT masv, hodem,ten,ngaysinh FROM sinhvien s INNER JOIN lop l ON s.malop=l.malop INNER JOIN diemthi d on s.masv=d.masv WHERE tenlop=N'Dữ liệu 1'164SQL Server 20053.9.2 Phép nối ngoàiPhép nối ngoài gồm các phép nối sau đây:Phép nối ngoài trái (LEFT OUTER JOIN)Phép nối ngoài phải (RIGHT OUTER JOIN)Phép nối ngoài đầy đủ (FULL OUTER JOIN)165SQL Server 20053.9.2 Phép nối ngoàiPhép nối ngoài gồm các phép nối sau đây:Cũng tương tự như phép nối trong, điều kiện của phép nối ngoài cũng được chỉ định ngay trong mệnh đề FROM theo cú pháp:Tên_bảng_1 LEFT|RIGHT|FULL [OUTER] JOIN tên_bảng_2 ON điều_kiện_nối 166SQL Server 20053.9.2 Phép nối ngoàiCho bảng NHANVIEN và Bảng DONVI như sau:SELECT *FROM nhanvien LEFT OUTER JOIN donvi ON nhanvien.madv=donvi.madv 167SQL Server 20053.9.3 phép nối trên nhiều bảng Khi thực hiện phép nối nhiều bảng, thứ tự thực hiện phép nối giữa các bảng được xác định theo nghĩa là kết quả của phép nối này được sử dụng trong một phép nối khác.Ví dụ 1: Hiển thị họ tên và ngày sinh của các sinh viên thuộc Khoa Khoa học Máy tính Ví dụ 2: Hiển thị bảng điểm học phần có tên là HQTCSDL SQL Server của lớp Dữ liệu 1 gồm các thông tin sau: masv, hodem, ten, ngaysinh, diem168SQL Server 20053.10 Mệnh đề GROUP BY Mệnh đề GROUP BY sử dụng trong câu lệnh SELECT nhằm gộp các dòng dữ liệu trong bảng thành các nhóm dữ liệu, và trên mỗi nhóm dữ liệu thực hiện tính toán các giá trị thống kê như tính tổng, tính giá trị trung bình,... Các hàm gộp được sử dụng để tính giá trị thống kê cho toàn bảng hoặc trên mỗi nhóm dữ liệu. Chúng có thể được sử dụng như là các cột trong danh sách chọn của câu lệnh SELECT hoặc xuất hiện trong mệnh đề HAVING, nhưng không được phép xuất hiện trong mệnh đề WHERE. 169SQL Server 20053.10 Thống kê dữ liệu với GROUP BY Hàm gộpChức năngSUM([ALL | DISTINCT] biểu_thức)Tính tổng các giá trị.AVG([ALL | DISTINCT] biểu_thức) Tính trung bình của các giá trịCOUNT([ALL | DISTINCT] biểu_thức)Đếm số các giá trị trong biểu thức.COUNT(*)Đếm số các dòng được chọn.MAX(biểu_thức)Tính giá trị lớn nhấtMIN(biểu_thức)Tính giá trị nhỏ nhất170SQL Server 20053.10.1 Thống kê trên toàn bộ dữ liệu Khi cần tính toán giá trị thống kê trên toàn bộ dữ liệu, bạn sử dụng các hàm gộp trong danh sách chọn của câu lệnh SELECT.Trong trường hợp này, trong danh sách chọn không được sử dụng bất kỳ một tên cột hay biểu thức nào ngoài các hàm gộp.Chú ý: miền tác động của hàm gộp lúc này là trên toàn bảng171SQL Server 20053.10.1 Thống kê trên toàn bộ dữ liệu Ví dụ 1: Hiển thị điểm trung bình lần 1 của sinh viên có mã là dl01-001.Ví dụ 2: Hiển thị điểm cao nhất của học phần có tên là HQTCSDL SQL Server.Ví dụ 3: Hiển thị năm sinh lớn nhất của lớp có tên là dữ liệu 1172SQL Server 20053.10.2 Thống kê dữ liệu trên các nhóm Sử dụng mệnh đề GROUP BY để phân hoạch dữ liệu vào trong các nhóm.Các hàm gộp được sử dụng sẽ thực hiện thao tác tính toán trên mỗi nhóm và cho biết giá trị thống kê theo các nhóm dữ liệu. 173SQL Server 20053.10.2 Thống kê dữ liệu trên các nhóm Ví du 1: Hiển thị mahocphan, điểm lớn nhất ứng với học phần đó.Ví dụ 2: Cho biết masv,hodem,ten, trung bình điểm thi lần 1 các học phần của các sinh viên.174SQL Server 20053.10.2 Thống kê dữ liệu trên các nhóm Lưu ý: Trong trường hợp danh sách chọn của câu lệnh SELECT có cả các hàm gộp và những biểu thức không phải là hàm gộp thì các trường trong danh sách phải có mặt đầy đủ trong mệnh đề GROUP BY, nếu không câu lệnh sẽ không hợp lệ.Ví dụ: Hiển thị malop, tenlop, số sinh viên theo từng lớp.175SQL Server 20053.10.3 Toán tử Rollup Cho phép thêm bản ghi ứng với hàm tương ứng trong phát biểu Select nhưng tính toán lại trên bản ghi đã được Group by.176SQL Server 20053.10.4 Toán tử Cube Bao gồm trường hợp RollupCho phép thêm bản ghi tương tự như vậy cho cột thứ hai khai báo sau mệnh đề Group by.177SQL Server 20053.10.5 Hàm Grouping Cho phép thêm cột dữ liệu.Trả về 0 cho dữ liệu thực tế và có giá trị 1 cho giá trị cột là null được tạo ra bởi toán tử Cube hay Rollup.178SQL Server 20053.10.6 Mệnh đề Having Mệnh đề HAVING được sử dụng cùng mệnh đề GROUP BY. Sau HAVING là biểu thức điều kiện. Biểu thức điều kiện này không tác động lên toàn bảng mà chỉ tác động lần lượt lên từng nhóm các bản ghi đã chỉ ra tại mệnh đề GROUP BY 179SQL Server 20053.10.6 Mệnh đề HavingVí dụ 1: hiển thị masv thi lần 1 từ hai học phần trở lên.Ví du 2: Hiển thị tên lớp có tổng số sinh viên lớn 2.Ví dụ 3: hiển thị masv,hodem,ten, trung bình điểm thi lần 1 của các sinh viên có điểm trung bình lớn hơn hoặc bằng 5.Ví dụ 4: Hiển thị makhoa, tenkhoa, solop của khoa 180SQL Server 20053.10.6 Mệnh đề HavingMộ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.181SQL Server 20053.10.6 Mệnh đề HavingVí dụ: Câu lệnh dưới đây cho biết mã, tên và trung bình điểm lần 1 của các học phần có trung bình lớn hơn trung bình điểm lần 1 của tất cả các học phầnSELECT d.mahocphan, tenhocphan, AVG(diem)FROM diemthi d inner join hocphan h on d.mahocphan=h.mahocphanWHERE lanthi=1GROUP BY d.mahocphan,tenhocphanHAVING AVG(diem)>(SELECT AVG(diem) FROM diemthi WHERE lanthi=1)182SQL Server 20053.11 Thống kê dữ liệu với COMPUTEMệnh đề COMPUTE sử dụng kết hợp với các hàm gộp và ORDER BY trong câu lệnh SELECT cũng cho các kết quả thống kê (của hàm gộp) trên các nhóm dữ liệu.Điểm khác biệt giữa COMPUTE và GROUP BY là kết quả thống kê xuất hiện dưới dạng một dòng trong kết quả truy vấn và còn cho biết cả chi tiết về dữ liệu trong mỗi nhóm. 183SQL Server 20053.11 Thống kê dữ liệu với COMPUTEMệnh đề COMPUTE BY có cú pháp như sau: COMPUTE hàm_gộp (tên_cột) [,, hàm_gộp (tên_cột)]BY danh_sách_cộtTrong đó:Các hàm gộp có thể sử dụng bao gồm SUM, AVG, MIN, MAX và COUNT.danh_sách_cột: là danh sách cột sử dụng để phân nhóm dữ liệu184SQL Server 20053.11 Thống kê dữ liệu với COMPUTEVí dụ: danh sách các lớp của mỗi khoa và tổng số các lớp của mỗi khoaSELECT khoa.makhoa, tenkhoa, malop, tenlopFROM khoa,lopWHERE khoa.makhoa=lop.makhoaORDER BY khoa.makhoaCOMPUTE COUNT(malop) BY khoa.makhoa185SQL Server 20053.11 Thống kê dữ liệu với COMPUTEKhi sử dụng mệnh đề COMPUTE ... BY cần tuân theo các qui tắc dưới đây:Từ khóa DISTINCT không cho phép sử dụng với các hàm gộpHàm COUNT(*) không được sử dụng trong COMPUTE.Sau COMPUTE có thể sử dụng nhiều hàm gộp, các hàm phải phân cách nhau bởi dấu phẩy.Các cột sử dụng trong các hàm gộp xuất hiện trong mệnh đề COMPUTE phải có mặt trong danh sách chọn.186SQL Server 20053.11 Thống kê dữ liệu với COMPUTEKhi sử dụng mệnh đề COMPUTE ... BY cần tuân theo các qui tắc dưới đây:Không sử dụng SELECT INTO trong một câu lệnh SELECT có sử dụng COMPUTE.Nếu sử dụng mệnh đề COMPUTE ... BY thì cũng phải sử dụng mệnh đề ORDER BY.Các cột liệt kê trong COMPUTE BY phải giống hệt hay là một tập con của danh sách các cột được liệt kê sau ORDER BY. Chúng phải có cùng thứ tự từ trái qua phải, bắt đầu với cùng một biểu thức và không bỏ qua bất kỳ một biểu thức nào.187SQL Server 20053.11 Thống kê dữ liệu với COMPUTETrong trường hợp sử dụng COMPUTE mà không có BY thì có thể không cần sử dụng ORDER BY, khi đó phạm vi tính toán của hàm gộp là trên toàn bộ dữ liệu.Ví dụ: Câu lệnh dưới đây hiển thị danh sách các lớp và tổng số lớp hiện có:SELECT malop,tenlop,hedaotaoFROM lopORDER BY makhoaCOMPUTE COUNT(malop)188SQL Server 20053.11 Thống kê dữ liệu với COMPUTECó thể thực hiện việc tính toán hàm gộp dòng trên các nhóm lồng nhau bằng cách sử dụng nhiều mệnh đề COMPUTE BY trong cùng một câu lệnh SELECTVí dụ:SELECT khoa.makhoa, tenkhoa, malop, tenlopFROM khoa,lopWHERE khoa.makhoa=lop.makhoaORDER BY khoa.makhoaCOMPUTE COUNT(malop) BY khoa.makhoa COMPUTE COUNT(malop) 189SQL Server 20053.12 TRUY VẤN CONTruy vấn con là một câu lệnh SELECT được lồng vào bên trong một câu lệnh SELECT, INSERT, UPDATE, DELETE hoặc bên trong một truy vấn con khác.Loại truy vấn này được sử dụng để biểu diễn cho những truy vấn trong đó điều kiện truy vấn dữ liệu cần phải sử dụng đến kết quả của một truy vấn khác. 190SQL Server 20053.12.1 CÚ PHÁP TRUY VẤN CONMột truy vấn con phải được viết trong cặp dấu ngoặc.Trong hầu hết các trường hợp, một truy vấn con thường phải có kết quả là một cộtMệnh đề COMPUTE và ORDER BY không được phép sử dụng trong truy vấn con.Các tên cột xuất hiện trong truy vấn con có thể là các cột của các bảng trong truy vấn ngoài.191SQL Server 20053.12.1 CÚ PHÁP TRUY VẤN CONMột truy vấn con thường được sử dụng làm điều kiện trong mệnh đề WHERE hoặc HAVING của một truy vấn khác.Nếu truy vấn con trả về đúng một giá trị, nó có thể sử dụng như là một thành phần bên trong một biểu thức (chẳng hạn xuất hiện trong một phép so sánh bằng)192SQL Server 20053.12.1 CÚ PHÁP TRUY VẤN CONKhi cần thực hiện phép kiểm tra giá trị của một biểu thức có xuất hiện (không xuất hiện) trong tập các giá trị của truy vấn con hay không, bạn có thể sử dụng toán tử IN (NOT IN) như sau:WHERE biểu_thức [NOT] IN (truy_vấn_con) 193SQL Server 20053.12.1 CÚ PHÁP TRUY VẤN CONVí dụ 1: Hiển thị tên khoa có quản lý lớp Ví dụ 2: Hiển thị masv, họ đệm, tên, tên lớp chưa thi học phần nào.194SQL Server 20053.12.2 Phép so sánh đối với kết quả truy vấn con Kết quả của truy vấn con có thể được sử dụng để thực hiện phép so sánh số học với một biểu thức của truy vấn cha. Trong trường hợp này, truy vấn con được sử dụng dưới dạng: WHERE biểu_thức phép_toán_số_học [ANY|ALL] (truy_vấn_con)Trong đó truy vấn con phải có kết quả bao gồm đúng một cột.195SQL Server 20053.12.2 Phép so sánh đối với kết quả truy vấn con Ví dụ 1: Cho biết tất cả thông tin về các học phần có số tín chỉ lớn hơn hoặc bằng số tín chỉ của học phần có mã là tindcVí dụ 2: Cho biết masv, hodem, ten sinh viên có điểm thi môn có mã là tinvp cao điểm nhấtVí dụ 3: Cho biết họ tên của những sinh viên lớp Đồ họa 1 có năm sinh trùng với năm sinh của bất kỳ một sinh viên nào đó của lớp Dữ liệu 1.196SQL Server 20053.12.3 Sử dụng từ khóa EXISTS Từ khóa EXISTS được sử dụng kết hợp với truy vấn con dưới dạng: WHERE [NOT] EXISTS (truy_vấn_con)Để kiểm tra xem một truy vấn con có trả về dòng kết quả nào hay không. Từ khóa EXISTS (tương ứng NOT EXISTS) trả về giá trị True nếu kết quả của truy vấn con có ít nhất một dòng.Đ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.197SQL Server 20053.12.3 Sử dụng từ khóa EXISTS Ví dụ: Cho biết họ tên của những sinh viên hiện chưa có điểm thi của bất kỳ một học phần nào.198SQL Server 2005Truy vấn con tương quanCú phápSELECT column1, column2, ...FROM table1 outerWHERE column1 operator (SELECT colum1, column2 FROM table2 WHERE expr1 = outer.expr2)199SQL Server 2005Truy vấn con tương quanÝ nghĩa:Truy vấn con tương quan được sử dụng khi một câu lệnh truy vấn con phải trả về một kết quả hay một tập hợp kết quả khác nhau cho mỗi hàng ứng viên của câu lệnh chính.Mặt khác, truy vấn con tương quan được sử dụng để trả lời những phần câu truy vấn mà câu trả lời phụ thuộc vào giá trị mỗi hàng ứng viên do câu lệnh chính xử lý.Câu lệnh con tham chiếu đến một cột trong bảng ở câu lệnh chính.200SQL Server 2005Truy vấn con tương quanChú ý:Chúng ta có thể sử dụng toán tử ANY, ALL và Exists trong câu lệnh truy vấn con tương quan. 201SQL Server 2005Truy vấn con tương quanTruy vấn con tương quan được sử dụng trong trường hợp xử lý từng hàng. Mỗi một câu truy vấn con được thực hiện một lần khi thực hiện xử lý từng hàng trong câu lệnh chính.Thực thi các câu lệnh con lồng nhauCâu lệnh con thực hiện trước và trả về giá trịCâu lệnh chính thực hiện một lần, sử dụng kết quả do câu lệnh con trả về. 202SQL Server 2005Truy vấn con tương quan203SQL Server 2005Truy vấn con tương quanThực thi câu lệnh con tương quan Lấy một hàng ứng viên.Thực thi câu lệnh con sử dụng giá trị của các hàng ứng viên.Sử dụng kết quả từ câu lệnh con để loại hoặc không các hàng ứng viên.Lặp lại cho đến khi không còn hàng ứng viên nào.204SQL Server 2005Chương 4Phát biểu T-SQL dạng xử lý dữ liệu 4.1 Chèn dữ liệu4.2 Cập nhật dữ liệu4.3 Xoá dữ liệu205SQL Server 20054.1 Chèn dữ liệuChèn từng dòng dữ liệu với mỗi câu lệnh INSERT. Đây là cách sử dụng thường gặp nhất trong SQL.Chèn nhiều dòng dữ liệu bằng cách truy xuất dữ liệu từ các bảng dữ liệu khác. 206SQL Server 20054.1.1 Chèn từng dòng dữ liệuĐể chèn một dòng dữ liệu mới vào bảng, bạn sử dụng câu lệnh INSERT với cú pháp như sau:INSERT INTO tên_bảng [(danh_sách_cột)]VALUES (danh_sách_trị) 207SQL Server 20054.1.1 Chèn từng dòng dữ liệuDanh sách cột ngay sau tên bảng không cần thiết phải chỉ định nếu giá trị các trường của bản ghi mới được chỉ định đầy đủ trong danh sách trị.Thứ tự các giá trị trong danh sách trị phải bằng với số lượng các trường của bảng cần chèn dữ liệu cũng như phải tuân theo đúng thứ tự của các trường như khi bảng được định nghĩa.Câu lệnh dưới đây chèn thêm một dòng dữ liệu vào bảng HOCPHANINSERT INTO HOCPHANVALUES(‘tcc’,’Toán cao cấp’,3)208SQL Server 20054.1.1 Chèn từng dòng dữ liệuTrong trường hợp chỉ nhập giá trị cho một số cột trong bảng, bạn phải chỉ định danh sách các cột cần nhập dữ liệu ngay sau tên bảng.Khi đó, các cột không được nhập dữ liệu sẽ nhận giá trị mặc định (nếu có) hoặc nhận giá trị NULL (nếu cột cho phép chấp nhận giá trị NULL).209SQL Server 20054.1.1 Chèn từng dòng dữ liệuCâu lệnh dưới đây chèn một bản ghi mới cho bảng SINHVIENINSERT INTO sinhvien (masv,hodem,ten,gioitinh,malop) VALUES (‘tm01-001’,‘Châu Thế’,’Toàn’,1,’tm01’)Câu lệnh trên còn có thể được viết như sau:INSERT INTO sinhvienVALUES (‘tm01-001’,‘ Châu Thế’,’Toàn’,NULL,1,NULL,’tm01’)210SQL Server 20054.1.1 Chèn từng dòng dữ liệuVí dụ 1: Viết câu lệnh SQL tạo bảng LUUSINHVIEN bao gồm các trường HODEM, TEN, NGAYSINHVí dụ 2: Chèn 3 dữ liệu cho bảng LUUSINHVIEN theo 3 cách khác nhau.211SQL Server 20054.1.2 Chèn nhiều dòng dữ liệu từ bảng khác Chèn nhiều dòng dữ liệu vào một bảng, các dòng dữ liệu này được lấy từ một bảng khác thông qua câu lệnh SELECT.Ở cách này, các giá trị dữ liệu được chèn vào bảng không được chỉ định tường minh mà thay vào đó là một câu lệnh SELECT truy vấn dữ liệu từ bảng khác.Cú pháp câu lệnh INSERT có dạng như sau:INSERT INTO tên_bảng[(danh_sách_cột)] câu_lệnh_SELECT212SQL Server 20054.1.2 Chèn nhiều dòng dữ liệu từ bảng khác Giả sử bạn có bảng LUUSINHVIEN bao gồm các trường HODEM, TEN, NGAYSINH.Câu lệnh dưới đây chèn vào bảng LUUSINHVIEN các bản ghi có được từ câu truy vấn SELECT:INSERT INTO luusinhvienSELECT hodem,ten,ngaysinh FROM sinhvienWHERE noisinh like N‘%Quảng Nam’213SQL Server 20054.1.2 Chèn nhiều dòng dữ liệu từ bảng khác Lưu ý:Kết quả của câu lệnh SELECT phải có số cột bằng với số cột được chỉ định trong bảng đích và phải tương thích về kiểu dữ liệu. Trong câu lệnh SELECT được sử dụng mệnh đề COMPUTE ... BY 214SQL Server 20054.2 Cập nhật dữ liệuCâu lệnh UPDATE trong SQL được sử dụng để cập nhật dữ liệu trong các bảng. Câu lệnh này có cú pháp như sau:UPDATE tên_bảngSET tên_cột = biểu_thức[, ..., tên_cột_k = biểu_thức_k][FROM danh_sách_bảng][WHERE điều_kiện]215SQL Server 20054.2 Cập nhật dữ liệuVí dụ 1: Câu lệnh sau cập nhật lại số tín chỉ bằng 4 cho học phần có mã là SQLUPDATE HOCPHANSET sotinchi = 4WHERE mahocphan =N’SQL’Ví dụ 2 : Viết câu lệnh cập nhật lại ngày sinh là 1/1/1990 cho sinh viên tên Bình.216SQL Server 20054.2.1 Cấu trúc CASE trong lệnh UPDATECấu trúc CASE có thể được sử dụng trong biểu thức khi cần phải đưa ra các quyết định khác nhau về giá trị của biểu thức.Giả sử bạn có bảng NHATKYPHONG sau đây:217SQL Server 20054.2.1 Cấu trúc CASE trong lệnh UPDATECâu lệnh cập nhật:UPDATE NHATKYPHONG SET tienphong=songay*CASE WHEN loaiphong='A' THEN 100 WHEN loaiphong='B' THEN 70 ELSE 50END 218SQL Server 20054.2.3 ĐK Cập nhật DL liên quan đến nhiều bảng Mệnh đề FROM trong câu lệnh UPDATE được sử dụng khi cần chỉ định các điều kiện liên quan đến các bảng khác với bảng cần cập nhật dữ liệu.Trong truờng hợp này, trong mệnh đề WHERE thường có điều kiện nối giữa các bảng. 219SQL Server 20054.2.3 ĐK Cập nhật DL liên quan đến nhiều bảngGiả sử bạn có hai bảng MATHANG và NHATKYBANHANG như sau:220SQL Server 20054.2.3 ĐK Cập nhật DL liên quan đến nhiều bảngCâu lệnh dưới đây sẽ cập nhật giá trị trường THANHTIEN của bảng NHATKYBANHANG theo công thức THANHTIEN = SOLUONG  GIAUPDATE nhatkybanhangSET thanhtien = soluong*giaFROM mathang WHERE nhatkybanhang.mahang = mathang.mahang221SQL Server 20054.2.4 Câu lệnh UPDATE với truy vấn conCâu lệnh ở ví dụ trước được viết như sau:UPDATE nhatkybanhangSET thanhtien = soluong*giaFROM mathang WHERE mathang.mahang =(SELECT mathang.mahang FROM mathang WHERE mathang.mahang=nhatkybanhang.mahang)222SQL Server 20054.3 Xoá dữ liệuĐể xoá dữ liệu trong một bảng, bạn sử dụng câu lệnh DELETE. Cú pháp của câu lệnh này như sau:DELETE FROM tên_bảng[FROM danh_sách_bảng][WHERE điều_kiện]223SQL Server 20054.3 Xoá dữ liệuVí dụ 1: Câu lệnh dưới đây xoá khỏi bảng SINHVIEN những sinh viên sinh tại Quảng NgãiDELETE FROM sinhvienWHERE noisinh LIKE N‘%Quảng Ngãi’Ví dụ 1: Xóa những sinh viên 19 tuổi từ bảng SINHVIEN224SQL Server 20054.3.2 Xoá dữ liệu khi điều kiện liên quan đến nhiều bảng Nếu điều kiện trong câu lệnh DELETE liên quan đến các bảng không phải là bảng cần xóa dữ liệu, bạn phải sử dụng thêm mệnh đề FROM và sau đó là danh sách tên các bảng đó.Trong trường hợp này, trong mệnh đề WHERE bạn chỉ định thêm điều kiện nối giữa các bảng225SQL Server 20054.3.2 Xoá dữ liệu khi điều kiện liên quan đến nhiều bảngCâu lệnh dưới đây xoá ra khỏi bảng SINHVIEN những sinh viên lớp Dữ liệu 1DELETE FROM sinhvienFROM lopWHERE lop.malop=sinhvien.malop AND tenlop=N'Dữ liệu 1' 226SQL Server 20054.3.3 Sử dụng truy vấn con trong câu lệnh Delete Một câu lệnh SELECT có thể được lồng vào trong mệnh đề WHERE trong câu lệnh DELETE để làm điều kiện cho câu lệnh tương tự như câu lệnh UPDATE.Ví dụ 1: Câu lệnh dưới đây xoá khỏi bảng LOP những lớp không có sinh viên nào học.DELETE FROM lopWHERE malop NOT IN (SELECT DISTINCT malop FROM sinhvien)Ví dụ 2: Xóa những sinh viên trong bảng LUUSINHVIEN có năm sinh trùng với những sinh viên có trong bảng SINHVIEN227SQL Server 20054.3.4 Xoá toàn bộ dữ liệu trong bảngCâu lệnh DELETE không chỉ định điều kiện đối với các dòng dữ liệu cần xoá trong mệnh đề WHERE sẽ xoá toàn bộ dữ liệu trong bảng. Thay vì sử dụng câu lệnh DELETE trong trường hợp này, bạn có thể sử dụng câu lệnh TRUNCATE có cú pháp như sau:TRUNCATE TABLE tên_bảng228SQL Server 20054.3.4 Xoá toàn bộ dữ liệu trong bảngVí dụ: Câu lệnh sau xoá toàn bộ dữ liệu trong bảng diemthi:DELETE FROM diemthiCó tác dụng tương tự với câu lệnhTRUNCATE TABLE diemthi229SQL Server 2005Chương 5THỦ TỤC NỘI TẠI (STORED PROCEDURE) 5.1 Giới thiệu5.2 Phân loại thủ tục5.3 Tạo thủ tục5.4 Lời gọi thủ tục5.5 Khai báo và sử dụng biến230SQL Server 2005Chương 5THỦ TỤC NỘI TẠI (STORED PROCEDURE) 5.6 Giá trị trả về của tham số trong thủ tục 5.7 Phát biểu điều khiển5.8 Tham số với giá trị mặc định5.9 Sửa đổi thủ tục5.10 Xoá thủ tục231SQL Server 20055.1 Giới thiệu thủ tụcMột thủ tục là một đối tượng trong CSDL bao gồm một tập nhiều câu lệnh SQL được nhóm lại với nhau thành một nhóm để thực hiện một công việc nào đó.Bên trong thủ tục nội tại có thể sử dụng các biến như trong ngôn ngữ lập trình nhằm lưu giữ các giá trị tính toán được, các giá trị được truy xuất được từ CSDL. Một thủ tục có thể nhận các tham số truyền vào cũng như có thể trả về các giá trị thông qua các tham số232SQL Server 20055.1 Giới thiệu thủ tụcCác lợi ích của thủ tục:Lập trình theo module: Thủ tục được thiết lập trong từng CSDL một lần, có thể gọi thực hiện nhiều lần trong một ứng dụng, có thể gọi từ nhiều ứng dụng.Thực hiện nhanh hơn: Thủ tục nội tại được phân tích, tối ưu khi tạo ra nên việc thực thi chúng nhanh hơn nhiều so với việc phải thực hiện một tập rời rạc các câu lệnh SQL tương đương theo cách thông thường.233SQL Server 20055.1 Giới thiệu thủ tụcGiảm thiểu sự lưu thông trên mạng: Khi cần thực hiện một lượng lớn câu lệnh T-SQL, thủ tục nội tại thực hiện nhanh hơn vì khi máy chủ nhận được nhiều cầu lệnh cùng một lúc đều phải kiểm tra tính hợp lệ quyền của tài khoản từ máy khách và các tham số khác. Khi thủ tục cần gọi nhiều lần trên các máy khách thì thủ tục thực hiện một lần đầu tiên, những lần sau máy khách sẽ chạy thủ tục đã được biên dịch.Bảo mật tốt hơn: Thay vì cấp phát quyền trực tiếp cho người sử dụng trên các câu lệnh SQL và trên các đối tượng CSDL, bạn có thể cấp phát quyền cho người sử dụng thông qua các thủ tục nội tại, nhờ đó tăng khả năng bảo mật đối với hệ thống.234SQL Server 20055.2 Phân loại thủ tụcTrong SQL Server 2005 có 3 nhóm thủ tục nội tại sau: Nhóm thứ nhất là do người dùng tạo ra. Nó bao gồm hai loại:Loại thủ tục nội tại được người dùng tạo ra và lưu vào CSDL. Chúng chứa các phát biểu T-SQL.Loại thứ hai được khai báo và tạo ra bằng ngôn ngữ lập trình .NET.235SQL Server 20055.2 Phân loại thủ tụcNhóm thứ hai là thủ tục nội tại hệ thống thực hiện các chức năng quản trị CSDL thường dùng. Các thủ tục này chứa trong CSDL Resource.Danh sách các thủ tục nội tại hệ thống hiển thị trong ngăn System Stored ProcedureThủ tục nội tại trong CSDL Resource luôn có tên với tiền tố là sp_. Do đó bạn không nên đặt tên thủ tục nội tại do mình tạo ra bằng tiền tố này.236SQL Server 20055.2 Phân loại thủ tụcNhóm thứ hai là thủ tục nội tại hệ thống thực hiện các chức năng quản trị CSDL thường dùng. Các thủ tục này chứa trong CSDL Resource.Danh sách các thủ tục nội tại hệ thống hiển thị trong ngăn System Stored ProcedureThủ tục nội tại trong CSDL Resource luôn có tên với tiền tố là sp_. Do đó bạn không nên đặt tên thủ tục nội tại do mình tạo ra bằng tiền tố này.Nhóm thứ ba là thủ tục nội tại hệ thống mở rộng. Loại này cũng được lưu trong CSDL Resouce nhưng có tên bắt đầu với xp_.237SQL Server 20055.3 Tạo thủ tụcTrong khung Object Explorer, chọn Database chứa thủ tục nội tại cần tạo, chọn Programmability. Kích nút phải chuột lên mục Stored Procedures chọn New Stored Procedure, cửa sổ Query xuất hiện cho phép bạn soạn thảo câu lệnh T-SQL để tạo thủ tục. Cú pháp tạo thủ tục nội tại như sau: CREATE PROCEDURE tên_thủ_tục [(danh_sách_tham_số)][WITH RECOMPILE |ENCRYPTION ] AS Các_câu_lệnh_của_thủ_tục 238SQL Server 20055.3 Tạo thủ tụcVí dụ 1: Viết thủ tục nhập vào dữ liệu cho bảng HOCPHANVí dụ 2: Viết thủ tục thực hiện công việc sau:Chèn thêm học phần công nghệ phần mềm có mã cnpm và số tín chỉ là 3 vào bảng HOCPHANLên danh sách nhập điểm thi môn công nghệ phần mềm cho các sinh viên học lớp có mã dl01 (tức là chèn thêm vào bảng DIEMTHI các bản ghi với cột MAHOCPHAN nhận giá trị cnpm, cột MASV nhận giá trị lần lượt là mã các sinh viên học lớp có mã dl01 và các cột điểm là NULL).239SQL Server 20055.3 Lời gọi thủ tụcThực thi thủ tục bằng lời gọi thủ tục có dạng:Tên_thủ_tục [danh_sách_các_đối_số]Lời gọi thủ tục được thực hiện bên trong một thủ tục khác, bên trong một trigger hay kết hợp với các câu lệnh SQL khác, sử dụng cú pháp :EXECUTE|EXEC tên_thủ_tục [danh_sách_các_đối_số]240SQL Server 20055.3 Lời gọi thủ tụcThứ tự của các đối số được truyền cho thủ tục có thể không cần phải tuân theo thứ tự của các tham số như khi định nghĩa thủ tục nếu tất cả các đối số được viết dưới dạng: @tên_tham_số = giá_trị241SQL Server 20055.4 Lời gọi thủ tụcVí dụ: Cách 1:spLenDanhSachDiem 'cnpm','Công nghệ phầm mềm',3,'dl01' Cách 2: spLenDanhSachDiem @malop='dl01',@tenhocphan='Công nghệ phần mềm',@mahocphan='cnpm', @sotinchi=3242SQL Server 20055.5 Khai báo và sử dụng biến5.5.1 Khai báo biếnDECLARE @tên_biến kiểu_dữ_liệuTên biến phải bắt đầu bởi ký tự @ và tuân theo qui tắc về định danh.Tất cả các biến sau khi khai báo sẽ có giá trị khởi tạo là Null.Ví dụ:DECLARE @hodem NVARCHAR(30) 243SQL Server 20055.5.2 Phát biểu SetPhát biểu Set dùng để gán giá trị cho các biến. Cú pháp của phát biểu Set như sau:SET =|()Bạn có thể sử dụng nhiều phát biểu SET trên cùng một dòng bằng cách sử dụng dấu chấm phẩy để phân cách.Set @Tong=0; Set @dem=0Set @max=(Select max(diemlan1) from DIEMTHI where mahocphan='sql')244SQL Server 20055.5.2 Phát biểu SetChú ý: Nếu sử dụng phát biểu SET với phát biểu SELECT, bạn bảo đảm phát biểu SELECT này trả về giá trị đơn. Nếu phát biểu SELECT trả về nhiều giá trị thì lỗi sẽ phát sinh. 245SQL Server 20055.5.3 Phát biểu Select để gán giá trịMột trong những điểm mạnh của phát biểu Select khi sử dụng để gán giá trị cho biến là cùng một lúc có thể lấy giá trị từ CSDL và gán vào nhiều biến.Ví dụ:Select @max=10, @min=0, @tong=0Select @max= max(diemlan1), @min=min(diemlan1) from DIEMTHI where mahocphan='sql' 246SQL Server 20055.6 Giá trị trả về của tham số trong thủ tụcXét câu lệnh sau đâyCREATE PROCEDURE sp_Conghaiso(@a INT,@b INT, @c INT)AS SELECT @c=@a+@bThực thi một tập các câu lệnh như sau:DECLARE @tong INTSELECT @tong=0EXECUTE sp_Conghaiso 100,200,@tongSELECT @tongKết quả tong=0247SQL Server 20055.6 Giá trị trả về của tham số trong thủ tụcNếu muốn giữ lại giá trị của đối số sau khi kết thúc thủ tục, bạn phải khai báo tham số theo cú pháp như sau:@tên_tham_số kiểu_dữ_liệu OUTPUThoặc:@tên_tham_số kiểu_dữ_liệu OUT Và trong lời gọi thủ tục, sau đối số được truyền cho thủ tục, bạn cũng phải chỉ định thêm từ khoá OUTPUT (hoặc OUT)248SQL Server 20055.6 Giá trị trả về của tham số trong thủ tụcĐịnh nghĩa lại thủ tục ở ví dụ trên như sau:CREATE PROCEDURE sp_Conghaiso(@a INT, @b INT, @c INT OUTPUT)ASSELECT @c=@a+@b249SQL Server 20055.6 Giá trị trả về của tham số trong thủ tụcThực hiện lời gọi thủ tục trong một tập các câu lệnh như sau:DECLARE @tong INTSELECT @tong=0EXECUTE sp_Conghaiso 100,200, @tong OUTPUTSELECT @tongThì câu lệnh “SELECT @tong” sẽ cho kết quả là: 300250SQL Server 20055.7 Cấu trúc điều khiển5.7.1 Cấu trúc If...Else5.7.2 Cấu trúc While5.7.3 Phát biểu Continue5.7.4 Phát biểu Break5.7.5 Phát biểu Return5.7.6 Cấu trúc Try...Catch5.7.7 Cấu trúc Case251SQL Server 20055.7.1 Cấu trúc If...ElseCấu trúc như sau :IF ELSE Trong Cấu trúc If...Else, nếu có từ hai lệnh trở lên thì phải đặt giữa hai từ khóa Begin và End.252SQL Server 20055.7.1 Cấu trúc If...ElseVí dụ 1: Viết thủ tục đưa vào một masv. Nếu sinh viên đó là Nam thì hiện ra câu thông báo “Chúc anh sức khỏe” ngược lại nếu sinh viên đó là Nữ hiện ra câu thông báo “Chúc chị sức khỏe”.Ví dụ 2: Viết thủ tục yêu cầu đưa vào hai mã sinh viên và in ra câu ai sinh trước hơn ai.253SQL Server 20055.7.2 Cấu trúc WhileCấu trúc điều khiển While cho phép chúng ta lặp lại thực thi tập lệnh cho đến khi biểu thức điều kiện là False. Cấu trúc Cấu trúc điều khiển While như sau:WHILE 254SQL Server 2005Ví dụ: Viết thủ tục in ra tổng của các số từ 1..n.CREATE PROCEDURE Tinhtong(@n int)ASBEGIN Declare @tong int Declare @i int set @tong=0 set @i=0 While @i}END TRYBEGIN CATCH     { }END CATCH[ ; ]264SQL Server 20055.7.6 Cấu trúc Try...CatchMột số thông tin về lỗi:ERROR_NUMBER(): Trả về mã số lỗi.ERROR_SEVERITY(): Trả về mức độ của lỗi.ERROR_STATE(): Mã trạng thái của lỗi.ERROR_PROCEDURE(): Trả về tên của thủ tục hay trigger xuất hiện lỗi.ERROR_LINE(): Trả về số dòng bên trong thủ tục xuất hiện lỗi.ERROR_MESSAGE(): Trả về dòng văn bản thông báo lỗi một cách đầy đủ.Các hàm này trả về Null nếu nó được gọi bên ngoài của khối Catch.265SQL Server 2005Ví dụ: Điều khiển lỗi trong thủ tục chia hai sốCREATE PROCEDURE phepchia(@sobichia float, @sochia float)ASBEGIN declare @thuong float begin try set @thuong=@sobichia/@sochia print @thuong end try begin catch SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; Print N'Số chia bằng không' end catchEND266SQL Server 20055.7.6 Cấu trúc Try...CatchKhi thực thi thủ tục trên:phepchia 3 , 0Kết quả trình bày trong ngăn Results và ngăn Messages như sau:267SQL Server 20055.7.7 Cấu trúc CaseCấu trúc này có cú pháp như sau:CASE biểu_thức  WHEN biểu_thức_kiểm_tra THEN kết_quả  [ ... ]   [ELSE kết_quả_của_else] END268SQL Server 20055.7.7 Cấu trúc CaseCấu trúc này có cú pháp như sau:Hoặc: CASE WHEN điều_kiện THEN kết_quả        [ ... ] [ELSE kết_quả_của_else] END269SQL Server 20055.7.7 Phát biểu CaseVí dụ 1: Để hiển thị mã, họ tên và giới tính (nam hoặc nữ) của các sinh viên, bạn sử dụng câu lệnhSELECT masv,hodem,ten, CASE gioitinh WHEN 1 THEN N'Nam' ELSE N'Nữ' END AS gioitinh FROM sinhvien270SQL Server 20055.7.7 Cấu trúc CaseHoặc: SELECT masv,hodem,ten, CASE WHEN gioitinh=1 THEN N'Nam' ELSE N'Nữ' END AS gioitinh FROM sinhvien271SQL Server 20055.7.7 Cấu trúc CaseVí dụ 2: Nhập vào một masv, mahocphanNếu Điểm lần 1>=9 thì hiển thị ra câu thông báo: Bạn “ Họ ten sinh vien” học học phần “ tên học phần” xuất sắcNếu 9>Điểm >=8 hiển thị giỏiNếu 8>Điểm >=7 hiển thị kháNếu 7>Điểm >=5 hiển thị trung bìnhĐiểm] Order By [DESC|ASC]) 303SQL Server 2005Hàm RankVí dụ: Hiển thị danh sách điểm thi lần 1 học phần có mã là Tinvp. Trong kết quả trả về có hiển thị thêm cột xếp vị thứ dựa vào điểm thi lần 1. SELECT DT.MASV, HODEM,TEN,GIOITINH, DIEM, RANK() OVER (ORDER BY DIEM DESC) AS [VI THU]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASVWHERE MAHOCPHAN='TINVP' AND LANTHI=1 304SQL Server 2005Hàm RankKết quả 305SQL Server 2005Hàm RankVí dụ: Hiển thị danh sách điểm thi lần 1 học phần có mã là Tinvp. Kết quả trả về hiển thị thêm cột xếp vị thứ theo từng nhóm giới tính dựa vào điểm thi lần 1.SELECT DT.MASV, HODEM,TEN,GIOITINH,DIEM, RANK() OVER (PARTITION BY GIOITINH ORDER BY DIEM DESC) AS [VI THU]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASVWHERE MAHOCPHAN='TINVP' AND LANTHI=1306SQL Server 2005Hàm RankKết quả 307SQL Server 2005Hàm Dense_Ranktương tự hàm Rank, tuy nhiên các giá trị trên cột vị thứ có giá trị liên tục. Dense_RankRank308SQL Server 2005Hàm NtileHàm này tương tự hàm Rank, tuy nhiên các giá trị trên cột vị thứ được giới hạn trong phạm vi chỉ định.Hàm NTILE chỉ có tác dụng chia đều số lượng các bản ghi và đưa vào từng nhóm số.Ví dụ: SELECT DT.MASV, HODEM,TEN,GIOITINH,DIEM, NTILE(2) OVER (ORDER BY DIEM DESC) AS [VI THU]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASV 309SQL Server 2005Hàm NtileSử dụng hàm NTILE cho từng bản ghi trong một nhóm sẽ đưa ra các xếp loại giống nhau. Ví dụ: SELECT DT.MASV, HODEM,TEN,GIOITINH,DIEM, NTILE(3) OVER (PARTITION BY GIOITINH ORDER BY DIEM DESC) AS [VI THU]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASV310SQL Server 2005Hàm Row_NumberTrả về số thứ tự của cho bản ghi trong tập bản ghi mà phát biểu SELECT trả về. Ví dụ:SELECT DT.MASV, HODEM, TEN, GIOITINH , DIEM, Row_Number () OVER (ORDER BY DIEM) AS [SO MAU TIN]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASV311SQL Server 20056.3 Hàm do người dùng định nghĩa6.3.1 Tạo hàm6.3.2 Thay đổi hàm312SQL Server 20056.3.1 Tạo hàm6.3.1.1 Tạo hàm trả về giá trị đơn Kích nút phải chuột lên mục Scalar - valued Functions chọn New Scalar - valued Function, cửa sổ Query xuất hiện cho phép bạn soạn thảo câu lệnh T-SQL để tạo hàm. Cú pháp tạo hàm trả về giá trị đơn như sau:CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) RETURNS (kiểu_trả_về_của_hàm)ASBEGIN Khai báo biến kết quả trả về các_câu_lệnh_của_hàm RETURN kết quả trả về của hàm END313SQL Server 20056.3.1.1 Tạo hàm trả về giá trị đơnVí dụ 1: Định nghĩa hàm tính thứ trong tuần của một giá trị kiểu ngày CREATE FUNCTION thu(@ngay DATETIME)RETURNS NVARCHAR(10)AS BEGIN DECLARE @st NVARCHAR(10) SELECT @st=CASE DATEPART(DW,@ngay) WHEN 1 THEN N'Chủ nhật' WHEN 2 THEN N'Thứ hai' WHEN 3 THEN N'Thứ ba' WHEN 4 THEN N'Thứ tư' WHEN 5 THEN N'Thứ năm' WHEN 6 THEN N'Thứ sáu' ELSE N'Thứ bảy' END RETURN (@st) /* Trị trả về của hàm */ END314SQL Server 20056.3.1.1 Tạo hàm trả về giá trị đơnMột hàm khi đã được định nghĩa có thể sử dụng như các hàm do HQTCSDL cung cấp (thông thường trước tên hàm, bạn phải chỉ định thêm tên của người sở hữu hàm)Ví dụ: Câu lệnh dưới đây sử dụng hàm đã được định nghĩa ở ví dụ trước:SELECT masv, hodem, ten, dbo.thu(ngaysinh) as Thu,ngaysinhFROM sinhvien WHERE malop='dl01' 315SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm nội tuyến trả về dữ liệu kiểu bảng (Inline Table- valued Function):Cú pháp:CREATE FUNCTION tên_hàm ([danh_sách_tham_số])RETURNS TABLE AS RETURN (câu_lệnh_select) 316SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm nội tuyến trả về dữ liệu kiểu bảng:Ví dụ: Định nghĩa hàm func_XemSV:CREATE FUNCTION func_XemSV(@khoahoc SMALLINT)RETURNS TABLEAS RETURN(SELECT masv,hodem,ten,ngaysinh FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE khoahoc=@khoahoc)317SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm nội tuyến trả về dữ liệu kiểu bảng:Ví dụ: Định nghĩa hàm func_XemSV:Để biết danh sách các sinh viên khoá 1, bạn sử dụng câu lệnh như sau:SELECT * FROM dbo.func_XemSV(1)318SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm chứa nhiều lệnh trả về dữ liệu kiểu bảng (Multi-Statement Table-valued Function ):CREATE FUNCTION tên_hàm([danh_sách_tham_số])RETURNS @biến_bảng TABLE định_nghĩa_bảngAS BEGIN     các_câu_lệnh_trong_thân_hàm     RETURN END319SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm chứa nhiều lệnh trả về dữ liệu kiểu bảng (Multi-Statement Table-valued Function ):Ví dụ: Định nghĩa hàm func_TongSV (@khoahoc SMALLINT) như sau: Hiển thị makhoa, tenkhoa, tongsosvNếu @khoahoc=0 thì cho biết tổng số sinh viên hiện có (tất cả các khóa) của mỗi khoa.Ngược lại Sẽ cho kết quả thống kê tổng số sinh viên khoá @khoahoc của mỗi khoa320SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm chứa nhiều lệnh trả về dữ liệu kiểu bảng (Multi-Statement Table-valued Function ):Thực hiện lệnh:SELECT * FROM dbo.func_TongSV(1)HoặcSELECT * FROM dbo.func_TongSV(0) 321SQL Server 20056.3.2 Thay đổi hàmTrong khung Object Explorer, chọn Database chứa hàm cần sửa đổi, chọn Programmability/ Function.Kích nút phải chuột lên hàm cần sửa đổi chọn Modify.Cửa sổ Query xuất hiện chứa lệnh ALTER FUNCTION 322SQL Server 2005Chương 7TRIGGER7.1 DML Trigger 7.1.1 Giới thiệu DML Trigger 7.1.2 Tạo DML Trigger 7.2 DDL Trigger 7.2.1 Giới thiệu DDL Trigger7.2.2 Tạo DDL Trigger 7.2.3 Hàm Eventdata323SQL Server 20057.1 DML Trigger7.1.1 Giới thiệu DML (Data Manipulation Language) Trigger Dùng để kiểm soát sự dữ liệu thay đổi.trigger loại này phải được gắn liền với một bảng nào đó trong CSDL.Khi dữ liệu trong bảng bị thay đổi (INSERT, UPDATE hay DELETE) thì trigger sẽ được tự đông kích hoạt.DML Trigger được sử dụng trong việc bảo đảm toàn vẹn dữ liệu theo quy tắc xác định, được quản lý theo bảng dữ liệu hoặc khung nhìn. Sử dụng DML trigger một cách hợp lý trong CSDL sẽ có tác động rất lớn trong việc tăng hiệu năng của CSDL.324SQL Server 20057.1 DML Trigger7.1.1 Giới thiệu DML Trigger Ý nghĩa: Bảo đảm toàn vẹn dữ liệu theo quy tắc xác định, được quản lý theo bảng dữ liệu hoặc khung nhìn.Sử dụng DML trigger một cách hợp lý trong CSDL sẽ có tác động rất lớn trong việc tăng hiệu năng của CSDL.325SQL Server 20057.1.1 Giới thiệu DML Trigger Thực sự hữu dụng với những khả năng:Nhận biết, ngăn chặn và huỷ bỏ được những thao tác làm thay đổi trái phép dữ liệu trong CSDL.Các thao tác trên dữ liệu có thể được trigger phát hiện ra và tự động thực hiện một loạt các thao tác khác trên CSDL nhằm đảm bảo tính hợp lệ của dữ liệu.Thông qua DML trigger, bạn có thể tạo và kiểm tra được những mối quan hệ phức tạp hơn giữa các bảng trong CSDL mà bản thân các ràng buộc không thể thực hiện được.326SQL Server 20057.1.2 Tạo DML TriggerTạo Trigger theo cấu trúc sau:CREATE TRIGGER tên_triggerON tên_bảngFOR {[INSERT][,][UPDATE][,][DELETE]} AS [IF UPDATE(tên_cột) [AND UPDATE(tên_cột)|OR UPDATE(tên_cột)] ...] các_câu_lệnh_của_trigger327SQL Server 20057.1.2.1 Tạo Trigger cho hành động thêm bản ghiSQL Server định nghĩa hai bảng logic INSERTED và DELETED để sử dụng trong các trigger.Cấu trúc của hai bảng này tương tự như cấu trúc của bảng mà trigger tác động.Dữ liệu trong hai bảng này tùy thuộc vào câu lệnh tác động lên bảng làm kích hoạt trigger, cụ thể trong các trường hợp sau: 328SQL Server 20057.1.2.1 Tạo Trigger cho hành động thêm bản ghiKhi câu lệnh DELETE được thực thi trên bảng, các dòng dữ liệu bị xoá sẽ được sao chép vào trong bảng DELETED. Bảng INSERTED trong trường hợp này không có dữ liệu.Dữ liệu trong bảng INSERTED sẽ là dòng dữ liệu được chèn vào bảng gây nên sự kích hoạt đối với trigger bằng câu lệnh INSERT. Bảng DELETED trong trường hợp này không có dữ liệu.Khi câu lệnh UPDATE được thực thi trên bảng, các dòng dữ liệu cũ chịu sự tác động của câu lệnh sẽ được sao chép vào bảng DELETED, còn trong bảng INSERTED sẽ là các dòng sau khi đã được cập nhật. 329SQL Server 20057.1.2.1 Tạo Trigger cho hành động thêm bản ghiVí dụ:Tạo Trigger thực hiện công việc, khi người dùng nhập bản ghi vào bảng LOP,Nếu cột dữ liệu hedaotao là rỗng thì hệ thống tự động gán giá trị của cột này bằng giá trị là ‘Chính quy’.330SQL Server 20057.1.2.1 Tạo Trigger cho hành động thêm bản ghiCREATE TRIGGER trg_insert_tblLOP ON LOP AFTER INSERTASBEGIN DECLARE @hedaotao nvarchar(25) DECLARE @malop nvarchar(10) SELECT @hedaotao=hedaotao, @malop= malop FROM INSERTED IF @hedaotao='' or @hedaotao is null UPDATE LOP SET hedaotao='Chính quy' Where malop=@malopEND 331SQL Server 20057.1.2 Tạo DML Trigger7.1.2.2 Tạo Trigger cho hành động cập nhật bản ghi:Ví dụ: Tạo Trigger thực hiện công việc, khi người dùng cập nhật điểm lần 1 cho các bản ghi của bảng DIEMTHI,Nếu sau khi cập nhật cột dữ liệu điểm lần 1 có giá trị >=5 thì thì hệ thống tự động xóa dòng điểm thi lần 2 của sinh viên thi học phần đó.332SQL Server 20057.1.2 Tạo DML TriggerCREATE TRIGGER Trg_Update_diemlan1ON DIEMTHI AFTER UPDATEASBEGIN DECLARE @diemlan1 numeric(5,2) DECLARE @masv nvarchar(10) DECLARE @mahocphan nvarchar(10) SELECT @masv=masv, @mahocphan=mahocphan, @diemlan1=diem FROM INSERTED WHERE lanthi=1 IF @diemlan1>=5 DELETE FROM DIEMTHI WHERE masv=@masv and mahocphan=@mahocphan and lanthi=2END333SQL Server 20057.1.2.3 Tạo Trigger cho hành động xóa bản ghiVí dụ: Tạo Trigger thực hiện lưu các mẫu tin bị xóa từ bảng DIEMTHI vào bảng DELECTED_DIEMTHI).CREATE TRIGGER Trg_Delete_DIEMTHION DIEMTHI AFTER DELETEASBEGIN INSERT INTO DELETED_DIEMTHI SELECT * FROM DELETEDEND334SQL Server 20057.1.2.4 Sử dụng mệnh đề IF UPDATESử dụng mệnh đề IF UPDATE trong trigger nếu muốn trigger được kích hoạt khi việc thay đổi dữ liệu chỉ liên quan đến một số cột nhất định nào đó của bảng.IF UPDATE không sử dụng được đối với câu lệnh DELETE.335SQL Server 20057.1.2.4 Sử dụng mệnh đề IF UPDATEVí dụ 7.4: CREATE TRIGGER trg_nhatkybanhang_update_soluongON nhatkybanhangFOR UPDATEASIF UPDATE(soluong) UPDATE mathang SET mathang.soluong = mathang.soluong – (inserted.soluong-deleted.soluong) FROM (deleted INNER JOIN inserted ON deleted.stt = inserted.stt) INNER JOIN mathang ON mathang.mahang = deleted.mahang336SQL Server 20057.1.2.5 Lệnh ROLLBACK TRANSACTIONMột trigger có khả năng nhận biết được sự thay đổi về mặt dữ liệu trên bảng dữ liệu, từ đó có thể phát hiện và huỷ bỏ những thao tác không đảm bảo tính toàn vẹn dữ liệu.Trong một trigger, để huỷ bỏ tác dụng của câu lệnh làm kích hoạt trigger, bạn sử dụng câu lệnh:ROLLBACK TRANSACTION 337SQL Server 20057.1.2.5 Lệnh ROLLBACK TRANSACTIONVí dụ 7.7:Ví dụ: Tạo Trigger để đảm bảo ràng buộc số học phần thi lại của một sinh viên không được vượt quá 2.338SQL Server 20057.2 DDL Trigger 7.2.1 Giới thiệu DDL TriggerDDL (Data Definition Language) Trigger được kích hoạt khi người sử dụng làm thay đổi cấu trúc CSDL hay đối tượng CSDL bằng các phát biểu SQL thuộc DDL như: Create, Alter, Drop, Grant, Deny, Revoke,Nếu DML Trigger dùng để kiểm soát dữ liệu chứa trong Table hay View thì DDL Trigger có thể được sử dụng cho chức năng quản trị CSDL. Đây là loại Trigger mới xuất hiện trong SQL Server 2005339SQL Server 20057.2 DDL Trigger 7.2.1 Giới thiệu DDL TriggerSau khi giai đoạn thiết kế CSDL hoàn tất, để kiểm soát mọi sự thay đổi cấu trúc của CSDL, chúng ta cần sử dụng loại Trigger này.Như vậy, mục đích của DDL Trigger là ngăn ngừa sự thay đổi cấu trúc CSDL. Ngoài ra, bạn có thể ghi lại những hành động làm thay đổi cấu trúc CSDL khi sử dụng DDL Trigger.340SQL Server 20057.2 DDL Trigger 7.2.1 Giới thiệu DDL TriggerMục đích của DDL Trigger là kiểm soát mọi sự thay đổi cấu trúc của CSDL.Có thể ghi lại những hành động làm thay đổi cấu trúc CSDL.341SQL Server 20057.2 DDL Trigger 7.2.2 Tạo DDL TriggerTạo DDL Trigger theo cấu trúc sau:CREATE TRIGGER tên_triggerON DATABASE|ALL SERVERFOR {[ Các sự kiện DDL] [nhóm các sự kiện]} AS các_câu_lệnh_của_trigger342SQL Server 20057.2 DDL Trigger 7.2.2 Tạo DDL TriggerVí dụ 7.8:Ví dụ: Tạo Trigger không cho tạo Database 343SQL Server 20057.2 DDL Trigger 7.2.3 Hàm Eventdata Thông tin về những sự kiện làm kích hoạt DDL trigger được lưu lại trong trong hàm Eventdata. Hàm này trả về một giá trị kiểu xml. Lược đồ xml này bao gồm các thông tin sau:Thời gian của sự kiện.Định danh xử lý hệ thống (The System Process ID: SPID) của kết nối trong lúc trigger thực hiện được thực hiện.Kiểu sự kiện đã kích hoạt trigger Tùy thuộc vào kiểu sự kiện, lược đồ bao gồm thêm những thông tin như: database nơi sự kiện xuất hiện, đối tượng bị tác động khi sự kiện xuất hiện và câu lệnh T-SQL của sự kiện. 344SQL Server 2005Chương 8: BẢO MẬT VÀ QUẢN TRỊ NGƯỜI DÙNG 8.1 Chứng thực người dùng8.2 Gán quyền cho người dùng8.3 Bảo trì cơ sở dữ liệu 345SQL Server 20058.1 Chứng thực người dùngChứng thực (Authentication) nhằm đảm bảo biết được người dùng là ai. Sau khi SQL Server chứng thực người dùng, họ có thể thực hiện bất kỳ hành động được phép nào với đăng nhập của họ cũng như những hành động được phép với nhóm mà họ là thành viên.346SQL Server 20058.1.1 Các kiểu chứng thực Có hai kiểu chứng thực:SQL Server and Windows Authentication: Đây là kiểu chứng thực hỗn hợp. SQL Server hỗ trợ trên cả đăng nhập SQL Server và Windows.Windows Authentication: Kiểu chứng thực này SQL Server chỉ hỗ trợ đăng nhập Windows. 347SQL Server 20058.1.1 Các kiểu chứng thực Thay đổi kiểu chứng thực:Trong cửa sổ Object Explorer, nhấn phải chuột lên Server bạn muốn thay đổi kiểu chứng thực, chọn Properties.Chọn nút SecurityỞ phần Server Authentication chọn lại kiểu chứng thực là Windows Authentication mode hoặc SQL Server and Windows Authentication mode.Nhấn OK.348SQL Server 20058.1.1 Các kiểu chứng thực Lưu ý: Mặc định, phiên bản SQL Server 2005 Express Edition và phiên bản SQL Server 2005 Developer Edition không cho phép các kết nối từ xa. Để cấu hình SQL Server 2005 chấp nhận các kết nối từ xa cần thực hiện các bước sau:Cho phép tiếp nhận các kết nối từ xa trên thể hiện của SQL Server mà các ứng dụng máytrạm cần kết nối.Kích hoạt dịch vụ SQL Server BrowserCấu hình tường lửa cho phép các dữ liệu liên quan đến SQL Server và dịch vụ SQL Server Browser được lưu thông trên mạng.349SQL Server 20058.1.2 Cách tạo đăng nhập8.1.2.1 Tạo đăng nhập Windows8.1.2.2 Tạo đăng nhập SQL Server8.1.2.2 Đăng nhập với User sa 350SQL Server 20058.1.3 Nhóm (Roles)8.1.3.1 Giới thiệuRoles trong SQL Server tương đương với Group trong Windows, gọi chung là nhóm. Bạn tạo nhóm, sau đó cấp quan hệ thành viên của người dùng cho nhóm. Người dùng là thành viên của nhóm sẽ thừa hưởng quyền được cấp cho nhóm.351SQL Server 20058.1.3 Nhóm (Roles)8.1.3.1 Giới thiệuSQL Server phân ra 4 loại nhóm:Nhóm quyền Server (Server Roles): Nhóm này được xây dựng sẵn trong SQL Server và người dùng không thể thay đổi được (thêm, sửa đổi hoặc xóa). Nhóm này cho phép người dùng thành viên thực hiện các tác vụ quản trị cấp Server. Bạn tìm thấy nhóm quyền này ở nút Security cấp Server352SQL Server 20058.1.3 Nhóm (Roles)8.1.3.1 Giới thiệuSQL Server phân ra 4 loại nhóm:Nhóm quyền CSDL (Database Roles): Nhóm quyền CSDL cho phép bạn gán quyền cho người dùng ở cấp CSDL. Nó cung cấp các quyền liên quan đến CSDL353SQL Server 20058.1.3 Nhóm (Roles)8.1.3.1 Giới thiệuSQL Server phân ra 4 loại nhóm:Nhóm quyền CSDL do người dùng định nghĩa: Ở cấp CSDL, bạn không bị hạn chế với các nhóm quyền đã định nghĩa trước. Bạn có thể tự định nghĩa nhóm quyền của riêng bạn. SQL Server phân ra 2 loại nhóm quyền do người dùng định nghĩa:Nhóm quyền chuẩn: Dùng cho các tác vụ gán quyền tới CSDL.Nhóm quyền ứng dụng: Dùng cho các ứng dụng.Nhóm quyền ứng dụng: Các quyền liên quan đến ứng dụng. 354SQL Server 20058.1.3 Nhóm (Roles)8.1.3.2 Thêm người dùng vào nhóm quyền Server8.1.3.3 Thêm người dùng vào nhóm quyền CSDL 8.1.3.4 Cách tạo nhóm quyền do người dùng tự định nghĩa 355SQL Server 20058.2 Gán quyền cho người dùng Chứng thực người dùng là quá trình đảm bảo chỉ có người dùng hợp lệ mới được phép làm việc với CSDL.Sau khi người dùng đạt được truy cập vào CSDL, điều quan trọng là họ có các quyền cụ thể gì với các đối tượng trong CSDL.356SQL Server 20058.2 Gán quyền cho người dùng8.2.1 Tạo người dùng CSDL8.2.2 Quản lý quyền trên đối tượng8.2.3 Quản lý quyền trên đối tượng 357SQL Server 20058.3 Bảo trì cơ sở dữ liệu 8.3.1 Sao lưu dự phòng8.3.2 Khôi phục cơ sở dữ liệu 358SQL Server 2005