Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server 2005 - Nguyễn Văn Lợi

358 trang hapham 2690

Download

Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server 2005 - Nguyễn Văn Lợi", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

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

bai_giang_he_quan_tri_co_so_du_lieu_sql_server_2005_nguyen_v.ppt

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server 2005 - Nguyễn Văn Lợi

BỘ THÔNG TIN & TRUYỀN THÔNG TRƯỜNG CAO ĐẲNG CNTT HỮU NGHỊ VIỆT - HÀN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 30LT+30TH Biên soạn: Nguyễn Văn Lợi SQL Server 2005 1
Mục tiêu của học phần • Mụ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 trì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. SQL Server 2005 2
Mục tiêu của học phần • Mục tiêu về kỹ năng: Giúp sinh viên có thể: - Tạo lập được CSDL trong SQL Server - Thành thạo ngôn ngữ T-SQL - Lập trình với CSDL trong SQL Server - Quản trị SQL Server SQL Server 2005 3
TÀI LIỆU THAM KHẢO • Nguyễ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 Xã 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 Xã Hội, Hà Nội. • Phạm Hữu Khang – Đoàn Thiện Ngân (2008), SQL Server 2005 lập trình thủ tục và hàm, NXB Lao Động Xã Hội, Hà Nội. SQL Server 2005 4
TÀI LIỆU THAM KHẢO • Phạm Hữu Khang (2007), Lập trình ứng dụng chuyên nghiệp SQL Server 2000, NXB Lao Động Xã Hội, Hà Nội. • Trần Đăng Công (2006), Giáo trình quản trị và phát triển ứng dụng với microsoft SQL Server, Học Viện Quân Sự • Robin Dewson, Beginning SQL Server 2005 for Developer • SQL Server 2005 Books Online SQL Server 2005 5
BỘ THÔNG TIN & TRUYỀN THÔNG TRƯỜNG CAO ĐẲNG CNTT HỮU NGHỊ VIỆT - HÀN • Chương 1: Tổng quan về SQL Server 2005 • Chương 2: CSDL trong SQL Server • Chương 3: Phát biểu T-SQL dạng truy vấn dữ liệu • Chương 4: Phát biểu T-SQL dạng xử lý dữ liệu • Chương 5: Thủ tục nội tại (Stored Procedure) • Chương 6: Hàm • Chương 7: Trigger • Chương 8: Bảo mật và quản trị người dùng SQL Server 2005 6
Chương 1 TỔNG QUAN VỀ SQL SERVER 2005 1.1 Lịch sử phát triển của SQL Server 1.2 Giới thiệu về SQL Server 2005 1.3 Các ấn bản SQL Server 2005 1.4 Giới thiệu các công cụ 1.5 Cài đặt 1.6 CSDL mẫu SQL Server 2005 7
1.1 Lịch sử phát triển củ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. SQL Server 2005 8
1.1 Lịch sử phát triển củ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 hã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. SQL Server 2005 9
1.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). SQL Server 2005 10
1.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. SQL Server 2005 11
1.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. SQL Server 2005 12
1.2 Giới thiệu về SQL Server 2005 (4/4) Những tính năng mới của SQL Server 2005 so với SQL Server 2000 là: • Nâng cao bảo mật • Mở rộng T-SQL • Tăng cường hỗ trợ người phát triển ứng dụng • Tăng cường khả năng quản trị CSDL • Nâng cao độ sẵn sàng và mở rộng của CSDL • Tăng cường khả năng khai thác thông tin SQL Server 2005 13
1.3. Các ấn bản SQL Server 2005 • Microsoft SQL Server 2005 Enterprise Edition • Microsoft SQL Server 2005 Standard Edition • Microsoft SQL Server 2005 Workgroup Edition • Microsoft SQL Server 2005 Developer Edition • Microsoft SQL Server 2005 Express Edition SQL Server 2005 14
1.4. Giới thiệu các công cụ 1.4.1 SQL Server Configuration Manager 1.4.2 SQL Server Management Studio SQL Server 2005 15
1.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ện • Start/ Programs/ Microsoft SQL Server 2005/ Configuration Tools/ SQL Server Configuration Manager. SQL Server 2005 16
1.4.1 Configuration Manager (2/2) SQL Server 2005 17
1.4.2 SQL Server Management Studio • Là 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 trình trong cùng một màn hì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 2000 SQL Server 2005 18
1.4.2 SQL Server Management Studio Enterprise Manager Query Analyzer SQL Server 2005 19
1.4.2 SQL Server Management Studio SQL Server 2005 20
1.4.2.1 Thực thi Management Studio • Start/ Programs / Microsoft SQL Server 2005 / SQL Server Management Studio • Hộp thoại Connect to Server xuất hiện. SQL Server 2005 21
1.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 chọnDatabase Engine. • Trong hộp danh sách Server Name chọn hoặc nhập vào: dấu chấm hoặc chữ local hoặc nhập tên Server. SQL Server 2005 22
1.4.2.1 Thực thi Management Studio Chọn phương pháp chứng thực trong hộp Authentication. • Windows Authentication thì 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áchLogin • Nếu chọn Remember password thì 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. SQL Server 2005 23
1.4.2.2 Sử dụng các Server đã đăng ký Hiển thị Registered Servers, chọn View/ Registered Servers. SQL Server 2005 24
1.4.2.2 Sử dụng các Server đã đăng ký Registered Servers được sử dụng cho các mụ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ông • Kế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 đã đăng ký. • Nhóm các Server. SQL Server 2005 25
1.4.2.2 Sử dụng các Server đã đăng ký • Đăng ký một thể hiện SQL Server • Kết nối tới một thể hiện SQL Server và hiển thị nó trong cửa sổ Object Explorer • Kết nối tới thể hiện SQL Server và tạo truy vấn mới đến thể hiện này SQL Server 2005 26
1.4.2.3 Công cụ Object Explorer • Khi mở Management Studio thì mặc định cửa sổ Object Explorer hiển thị bên trái màn hình. Nếu Object Explorer chưa hiển thị bạn chọnView/ Object Explorer. SQL Server 2005 27
1.4.2.3 Công cụ Object Explorer Object 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 chọ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ền • Cấu hì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 SQL Server 2005 28
1.4.2.4 Cửa sổ Summary SQL Server 2005 29
1.4.2.4 Cửa sổ Summary • Trong 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 chọn. • Chọ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: • Chọn CSDL DB_LOI • Kích vào mục Disk Usage trong danh sách chọn của nút Report. Cửa sổ hiển thị Disk Usage của CSDL DB_LOI như sau: SQL Server 2005 30
1.4.2.4 Cửa sổ Summary • Cửa sổ hiển thị Disk Usage của CSDL DB_LOI như sau: SQL Server 2005 31
1.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. SQL Server 2005 32
1.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ổ Query • Bướ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 chọn. Mặc định kết xuất dạng bảng được chọn. SQL Server 2005 33
1.4.2.6 Các tùy chọn • ChọnTool/ Options. • Trong cửa sổ Options, bạn mở các nút tùy chọn bên trái để xem các tùy chọn SQL Server 2005 34
1.4.2.6 Các tùy chọn Thiết lập tùy chọnStartup • Trên nhóm Environment, bạn chọn thẻ General. Trong mục At startup cho phép lựa chọ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 chọnWindow/ reset window layout. SQL Server 2005 35
1.4.2.6 Các tùy chọn Thay đổi cách hiển thị kết quả truy vấn • Chọn nút Query Results, rồi chọn nút SQL Server. Trong hộp danh sách Default destination for result bạn chọ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ảng • Results to text: Xuất kết quả truy vấn ra dạng văn bản • Results to file: Xuất kết quả truy vấn ra dạng tập tin • Default location for saving query results: Mặc định nơi lưu trữ kết quả truy vấn. SQL Server 2005 36
1.4.2.7 Sử dụng mã- T SQL mẫu • Chọ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 mã -T SQL mẫu được hiển thị trong cửa sổ Query mới SQL Server 2005 37
1.5 Cài đặt 1.5.1 Yêu cầu phần cứng 1.5.2 Yêu cầu hệ điều hành 1.5.3 Các bước cài đặt SQL Server 2005 38
1.5.1. Yêu cầu phần cứng Bộ Bộ xử lý có tốc độ 700MHz hoặc cao hơn. xử lý Bộ • SQL Server 2005 Enterprise Edition yêu cầu bộ nhớ 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ài các thành phần của Database yêu cầu không cứn gian bộ nhớ từ 95 đến 300 MB, tuy nhiên nó còn g phụ thuộc vào các lựa chọn cụ thể. Nếu bạn cài đặt tất cả các thành phần của SQL Server thì không gian đĩa còn trống tối thiểu là 1GB. SQL Server 2005 39
1.5.2. Yêu cầu hệ điều hành Bảng tương thích giữa các phiên bản của SQL Server2005 và hệ điều àh nh Windows: SQL Server 2005 40
1.5.3. Các bước cài đặt • Việ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 trình bày chi tiết trong phần thực hành SQL Server 2005 41
1.5.4. Gỡ bỏ • Start\ Settings \ Control Panel, cửa sổ Control Panel xuất hiện, chọn mục Add or Remove Programs. Cửa sổ Add or Remove Programs xuất hiện như sau: SQL Server 2005 42
Chương 2 CƠ 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ệu 2.3. Sao chép cơ sở dữ liệu 2.4. Gán và tách cơ sở dữ liệu 2.5. Nhập và xuất cơ sở dữ liệu 2.6. Kiểu dữ liệu và cách khai báo 2.7. Bảng 2.8. View SQL Server 2005 43
2.1. Giới thiệu CSDL trong SQL Server SQL Server thuộc họ 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ử lý giao dịch trực tuyến (Online Transaction Processing - OLTP) và xử lý 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ớn SQL Server 2005 44
2.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 trì 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. SQL Server 2005 45
2.1.1. Các cơ sở dữ liệu xử lý 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. SQL Server 2005 46
2.1.2. Các cơ sở dữ liệu xử lý 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ô hình hóa dữ liệu và hỗ trợ quyết định. SQL Server 2005 47
2.1.3. Cân nhắc khi hoạch định cơ sở dữ liệu • Mục tiêu của kho dữ liệu: Các CSDL OLTP và OLAP có các mục tiêu khác nhau và vì 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. SQL Server 2005 48
2.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 lý củ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 đọc đĩa cho việc đọc và ghi dữ liệu hiệu quả. SQL Server 2005 49
2.1.4. Cấu trúc cơ sở dữ liệu SQL 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 đã kết nối đến Server, Client chỉ thực hiện được quyền khai thác theo quy định đã định sẵn trong CSDL (phân quyền trong CSDL). SQL Server 2005 50
2.1.4. Cấu trúc cơ sở dữ liệu • Cơ 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). SQL Server 2005 51
2.1.5. Sơ đồ quản trị cơ sở dữ liệu • Cơ 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). SQL Server 2005 52
2.1.5. Sơ đồ quản trị cơ sở dữ liệu • Master: 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 hì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. SQL Server 2005 53
2.1.5. Sơ đồ quản trị cơ sở dữ liệu • Model: Đượ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á trình backup restore hệ thống . SQL Server 2005 54
2.1.6 Cấu trúc vật lý của CSDL Như 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). SQL Server 2005 55
2.1.6 Cấu trúc vật lý của CSDL Page • SQL Server quản lý một page có kích thước là 8KB, như vậy1 MB có 128 page, trong mỗi trang có 96 byte chứa thông tin của trang SQL Server 2005 56
2.1.6 Cấu trúc vật lý của CSDL • Extent: 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ượng • Mixform: Có thể dùng lưu trữ 8 đối tượng. SQL Server 2005 57
2.1.6 Cấu trúc vật lý của CSDL • Cấu trúc Extent như sau: SQL Server 2005 58
2.2. Tạo cơ sở dữ liệu • Trong khung Object Explorer, kích nút phải chuột tại mục Databases, chọn New Database. Cửa sổ New Database xuất hiện như sau: SQL Server 2005 59
2.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, chọnTask/ Copy Database, chọn Next. • Cửa sổ Select a Source Server xuất hiện, - Chọ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 chọn Server. - Chọ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 chọn Next. SQL Server 2005 60
2.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, chọ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 chọn Next. SQL Server 2005 61
2.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 chọ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 chọn này thì 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 chọn Next. SQL Server 2005 62
2.3. Sao chép cơ sở dữ liệu Cách 1: • Trong cửa sổ Select Database, bạn chọn CSDL cần sao chép, kích chọ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 Database - Chọn thư mục chứa các tập tin CSDL mới trong mục Destination Folder SQL Server 2005 63
2.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. Chọn Next để tiếp tục. • Trong cửa sổ Schedule the Package, bạn chọnRun immediately nếu muốn thực hiện sao chép database ngay. Còn chọn mục Schedule nếu muốn lập lịch trình để sao chép database. Chọn Next để tiếp tục. • Trong cửa sổ Complete the Wizard, chọn Finish. SQL Server 2005 64
2.3. Sao chép cơ sở dữ liệu Cách 2: • Bước1 : Dừng các dịch vụ SQL Server 2005 • Bước2 : 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ước3 : Khởi động lại các dịch vụ được dừng ở bước1 . SQL Server 2005 65
2.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, Chọn Instance cần Attach CSDL kích nút phải tại mục Databases, chọn Attach, cửa sổ Attach Databases xuất hiện như sau: SQL Server 2005 66
2.4.1 Gán cơ sở dữ liệu Kích vào nút Add, chọn đến thư mục chứa tập tin có đuôi “mdf” của cơ sở dữ liệu cần Attach, Chọn Ok, Chọn tiếp OK. SQL Server 2005 67
2.4.2 Tách cơ sở dữ liệu • Tá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, chọn Tasks/ Detach • Trong cửa sổ Detach Database, Nhấn Ok. SQL Server 2005 68
2.5 Nhập và xuất dữ liệu 2.5.1 Nhập dữ liệu Nhậ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, chọn Tasks/ Import Data • Trong cửa sổ Welcome to SQL Server Import and Export Wizard, bạn chọn Next. SQL Server 2005 69
2.5.1 Nhập dữ liệu Cửa sổ Choose a Data Source xuất hiện: SQL Server 2005 70
2.5.1 Nhập dữ liệu • Chọn kiểu nguồn dữ liệu trong mục Data Source (SQL Server, Oracle, Access, ) • Chọn tập tin cơ sở dữ liệu trong mục file name • Chọn Next SQL Server 2005 71
2.5.1 Nhập dữ liệu • Trong 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: SQL Server 2005 72
2.5.1 Nhập dữ liệu • Chọn tên Server trong mụcServer name • Chọn tên CSDL trong mụcDatabase (có trước hoặc tạo tại thời điểm này bằng cách chọnNew ) • ChọnNext . SQL Server 2005 73
2.5.1 Nhập dữ liệu Trong cửa sổ Specify Table Copy or Query: yều cầu bạn chọn cách chuyển dữ liệu • Copy 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. SQL Server 2005 74
2.5.1 Nhập dữ liệu Trong cửa sổ Select Source Tables and Views • Chọn các bảng, view cần Import và nhận trong mục Tables and Views. • Chọn Next. • Trong cửa sổ Save and Execute Package, chọnNext • Trong cửa sổ Complete the Wizard, chọn Finish • Cửa sổ Operation stopped cho hiển thị kết quả chuyển dữ liệu, chọn Close SQL Server 2005 75
2.5.2 Xuất dữ liệu Dù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. SQL Server 2005 76
2.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 chọ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 chọ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. SQL Server 2005 77
2.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 Types • User-Defined Data Types • User-Defined Types • Xml Schema Collections SQL Server 2005 78
2.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 Numberics • Approximate Numerics • Date and Time • Character Strings • Unicode Character Strings • Binary Strings • Other Data Types SQL Server 2005 79
2.6.1. Nhóm Exact Numberics • BIT: 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 1 • TINYINT: kiểu số nguyên 1 byte để lưu trữ các giá trị nằm trong khoảng từ 0 đến 255 SQL Server 2005 80
2.6.1. Nhóm Exact Numberics • SMALLINT: 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. SQL Server 2005 81
2.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-1 • SMALLMONEY: dùng 4 byte để lưu trữ số kiểu tiền tệ trong khoảng -214.748,3648 đến -214.748,3647 • MONEY: 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,5807 SQL Server 2005 82
2.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. SQL Server 2005 83
2.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. SQL Server 2005 84
2.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 null • VARCHAR: 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 thì đò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ự. SQL Server 2005 85
2.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 null • VARCHAR: 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 thì đò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ự. SQL Server 2005 86
2.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 thì 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 thì 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ự. SQL Server 2005 87
2.6.6 Nhóm Binary Strings • BINARY [ ( 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 8000 • VARBINARY [ ( 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. SQL Server 2005 88
2.6.7 Nhóm Orther Data Types • SQL_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. SQL Server 2005 89
2.6.7 Nhóm Orther Data Types • UNIQUEIDENTIFIER: 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. SQL Server 2005 90
2.7 Bảng 2.7.1 Tạo bảng • Kích đôi chuột lên Database cần tạo bảng • Kích phải chuột lên mục Tables và chọn New Table. Một bảng Table – dbo.Table_1 hiển thị bên phải màn hình Management Studio. Bảng này gọi là Table Designer, có chứa các cột SQL Server 2005 91
2.7.1 Tạo bảng • Column 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 chọn thì 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ó. • Chọ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. SQL Server 2005 92
2.7.2 Thiết lập quan hệ cho cơ sở dữ liệu Quan 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 đó gọ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. SQL Server 2005 93
2.7.2 Thiết lập quan hệ cho cơ sở dữ liệu Nếu trước đó bạn chưa tạo quan hệ cho CSDL, khi chọn vào ngăn Database Diagram thì thông báo xuất hiện như sau: SQL Server 2005 94
2.7.2 Thiết lập quan hệ cho cơ sở dữ liệu Kích nút phải chuột lên mục Database Diagrams, chọnNew Database Diagram. Cửa sổ Add Table xuất hiện như sau: SQL Server 2005 95
2.7.2 Thiết lập quan hệ cho cơ sở dữ liệu Chọ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: SQL Server 2005 96
2.7.2 Thiết lập quan hệ cho cơ sở dữ liệu Thiết lập quan hệ giữa các bảng bằng cách chọ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: SQL Server 2005 97
2.7.2 Thiết lập quan hệ cho cơ sở dữ liệu • Chọ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 OK • Lưu đối tượng Diagram SQL Server 2005 98
2.7.2. Thiết lập quan hệ cho cơ sở dữ liệu Chú ý: • 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 đã 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ệ. SQL Server 2005 99
2.7.3. Thêm ràng buộc cho bảng SQL 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. SQL Server 2005 100
2.7.3. Thêm ràng buộc cho bảng • NOT 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. SQL Server 2005 101
2.7.3 Thêm ràng buộc cho bảng Ví dụ1 : Thêm ràng buộc UNIQUE cho cột TenMH (vì tên các môn họ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 chọn Modify. • Trong bảng Table Designer, nhấp phải chuột lên dòng TenMH chọn Indexes/ Keys. Hộp thoại Indexes/ Keys xuất hiện. SQL Server 2005 102
2.7.3. Thêm ràng buộc cho bảng • Nhấn nút Add, chọn giá trị cho dòng Type là Unique key. • Nhấn Close để đóng hộp thoại. SQL Server 2005 103
2.7.3. Thêm ràng buộc cho bảng Ví dụ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 chọn Modify. • Trong bảng Table Designer, nhấp phải chuột lên dòng DIEM chọ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. Chọn cột bên phải dòng Expression và chọ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 DIEM<=10. • Nhấn Close để đóng hộp thoại Check Constraints SQL Server 2005 104
2.7.4. Xử lý dữ liệu trong bảng Lưu ý: Nếu bạn thêm, sửa, xóa dữ liệu trong bảng phải chú ý đến ràng buộc khóa ngoại. Nghĩa là: • Bạn không thể thêm dữ liệu nếu không có khóa chính tương ứng trong bảng mà nó có quan hệ tới. Nghĩa là giá trị nhập vào cột khóa ngoại phải tồn tại trong cột khóa chính của bảng có cột đó là khóa chính. • Ngược lại bạn không thể xóa một giá trị trong cột khóa chính nếu giá trị đó đang tồn tại trong cột khóa ngoại của bảng tham chiếu đến bảng chứa khóa chính. SQL Server 2005 105
2.8 View 2.8.1. Giới thiệu • Trong quá trình xử lý dữ liệu, ta cần kết nối những bảng có quan hệ với nhau nhằm kết xuất dữ liệu theo ý muốn. Để làm điều này, bạn sử dụng phát biểu SQL hay công cụ hỗ trợ View (khung nhìn hay bảng ảo). • Nếu sử dụng phát biểu SQL khi truy vấn trực tiếp sẽ không cho phép bạn lưu trữ cấu trúc phát biểu SQL như là một đối tượng của SQL Server. SQL Server 2005 106
2.8.1. Giới thiệu Mục đích của việc sử dụng View là: • Hạn chế tính phức tạp của dữ liệu đến với người dùng • Kết nối dữ liệu từ nhiều bảng lại với nhau dễ dàng. • Kết hợp một số hàm và phương thức tạo ra các cột mới. • Khi cần thiết có những câu lệnh SQL dùng trong quá trình viết chương trình như Visual Basic, Java, Bạn có thể dùng View để tạo các câu lệnh tạo theo ý mình, sau đó copy để sử dụng trong lập trình. SQL Server 2005 107
2.8.2 Cách tạo View • Bước1 : Chọn Database chứa đối tượng View cần tạo. • Bước2 : Kích nút phải chuột lên nút Views, chọn New View. Hộp thoại Add xuất hiện. • Bước3 : Chọn bảng dữ liệu hoặc View dùng để tạo View rồi nhấn nút Add. Sau khi chọn xong nhấn nút Close đóng hộp thoại này lại. SQL Server 2005 108
2.8.2 Cách tạo View Cửa sổ View xuất hiện như sau: SQL Server 2005 109
2.8.2 Cách tạo View • Bạn có thể thêm bảng nếu cần thiết bằng cách kích vào biểu tượng Add Table trên thanh công cụ View Designer hoặc kích vào vùng trống khung Show Diagram, chọn mục Add Table • Chọn vào hộp kiểm trước cột dữ liệu bạn muốn hiển thị trong View. Khi đó tên cột bạn chọn sẽ được thêm vào khung Show Criteria. • Trong khung Show Criteria bạn có thể thay đối tên hiển thị của cột trong View bằng tên khác đặt trong cột Alias (bí danh). • Trong cột Output, chọn vào hộp kiểm của cột dữ liệu mà bạn muốn hiển thị ra trong View. SQL Server 2005 110
2.8.2 Cách tạo View • Trong cột Sort Order, bạn chọn 1 nếu muốn sắp xếp và unsorted hoặc không chọn nếu không muốn sắp xếp. • Cột Sort Type cho phép bạn chọn kiểu sắp xếp: Ascending: tăng dần và Descending là giảm dần. • Cột Filter dùng để nhập điều kiện hiển thị trong View. • Cột Or dùng để nhập điều kiện hoặc cho cột. • Kích chuột vào biểu tượng Add Group by để thêm cột Group by vào khung Show Criteria. SQL Server 2005 111
2.8.2 Cách tạo View • Bước4 : Kích vào nút Execute SQL để thực hiện View. Kết quả dữ liệu trả về của View sẽ được hiển thị trong khung Show Results. • Bước5 : Bạn có thể hiện chỉnh lại câu lệnh SQL cho phù hợp với yêu cầu truy vấn trong khung của Show SQL. Kích chuột vào nút Verify SQL Syntax để kiểm tra cú pháp câu lệnh SQL. • Bước6 : Kích chuột vào biểu tượng Save hoặc nhấn Ctrl + S để lưu View. Nhập tên View cần lưu. SQL Server 2005 112
2.8.2. Cách tạo View Ví dụ: Trong CSDl QLDIEM tạo View • Hiển thị bảng điểm lần một của môn học có mã môn học là‘ Tinvp’ và ‘Tindc’ gồm các thông tin sau: Masv, hodem, ten, mamonhoc,diemlan1. • Kết quả trả về sắp xếp theo thứ tự tăng dần của cột điểm lần 1. SQL Server 2005 113
2.8.3. Hiệu chỉnh View • Kích phải chuột lên View muốn thay đổi và chọnModify . • Thực hiện việc sửa đổi View và lưu lại. SQL Server 2005 114
Chương 3 Phát biểu T-SQL dạng truy vấn dữ liệu 3.1. Giới thiệu ngôn ngữ -T SQL và SQL 3.2. Cú pháp chung của câu lệnh SELECT 3.3. Mệnh đề FROM 3.4. Danh sách chọn trong câu lệnh SELECT 3.5. Chỉ định điều kiện truy vấn dữ liệu 3.6. Tạo mới bảng dữ liệu từ kết quả truy vấn của câu lệnh SELECT SQL Server 2005 115
Chương 3 Phát biểu T-SQL dạng truy vấn dữ liệu 3.7. Sắp xếp kết quả truy vấn 3.8. Toán tử Union 3.9. Phép nối 3.10. Thống kê dữ liệu với GROUP BY 3.11. Truy vấn con (Subquery) 3.12. Truy vấn con (Subquery) SQL Server 2005 116
3.1 Giới thiệu ngôn ngữ -T SQL và SQL • T-SQL (Transact - Structured Query Language) là thực hiện ngôn ngữ truy vấn có cấu trúc (SQL) của Microsoft. • T-SQL được thiết kế để nhận, tương át c và thêm dữ liệu vào CSDL SQL Server. SQL Server 2005 117
3.1 Giới thiệu ngôn ngữ -T SQL và SQL • Vào giữa những năm 70, IBM đã thiết lập ra một chuẩn được gọi là Structed Enghlish Query Language được gọi tắt là SEQUEL. Sau này, tên viết tắt SEQUEL được rút gọn thành SQL. • SQL là một loại ngôn ngữ máy tính phổ biến để tạo, sửa, và lấy dữ liệu từ một hệ quản trị cơ sở dữ liệu quan hệ. • SQL được thừa nhận là tiêu chuẩn của ANSI (American National Standards Institute) vào năm 1986 và ISO (International Organization for Standardization) năm 1987 SQL Server 2005 118
3.1 Giới thiệu ngôn ngữ -T SQL và SQL • Tiêu chuẩn SQL đã trải qua một số phiên bản: Năm Tên Tên khác Chú giải 1986 SQL-86 SQL-87 Được công bố đầu tiên bởi ANSI. Được phê chuẩn bởi ISO năm 1987. 1989 SQL-89 1992 SQL-92 SQL2 1999 SQL:1999 SQL3 2003 SQL:2003 SQL Server 2005 119
3.1 Giới thiệu ngôn ngữ -T SQL và SQL • Hầu hết các sản phẩm CSDL dựa trên SQL được thiết lập trên chuẩn ANSI SQL-92 (năm 1992 ANSI SQL được xem xét lại và được biết với tên là ISO ANSI SQL-92) và chúng không được xem xét để làm phù hợp theo các đặc tả của ANSI SQL-99 hay ANSI SQL-2003. • SQL Server 2000 thực hiện theo ANSI SQL-92. • SQL Server 2005 thực hiện theo ANSI SQL-99. SQL Server 2005 120
3.2 Cú pháp chung của câu lệnh Select • Câu lệnh SELECT được sử dụng để truy xuất dữ liệu từ các dòng và các cột của một hay nhiều bảng, khung nhìn. • Ngoài ra, câu lệnh này còn cung cấp khả năng thực hiện ác c thao tác truy vấn và thống kê dữ liệu phức tạp khác. SQL Server 2005 121
3.2 Cú pháp chung của câu lệnh Select SELECT [ALL | DISTINCT][TOP n] danh_sách_chọn [INTO tên_bảng_mới] FROM danh_sách_bảng/khung_nhìn [WHERE điều_kiện] [GROUP BY danh_sách_cột] [HAVING điều_kiện] [ORDER BY cột_sắp_xếp] [COMPUTE danh_sách_hàm_gộp [BY danh_sách_cột]] SQL Server 2005 122
3.2 Cú pháp chung của câu lệnh Select • Câu lệnh SELECT được sử dụng để tác động lên các bảng dữ liệu và kết quả của câu lệnh cũng được hiển thị dưới dạng bảng, tức là một tập hợp các dòng và các cột (ngoại trừ trường hợp sử dụng câu lệnh SELECT với mệnh đề COMPUTE). SQL Server 2005 123
3.3 Danh sách chọn trong câu lệnh SELECT • Danh sách chọn trong câu lệnh SELECT được sử dụng để chỉ định các trường, các biểu thức cần hiển thị trong các cột của kết quả truy vấn. • Các trường, các biểu thức được chỉ định ngay sau từ khoá SELECT và phân cách nhau bởi dấu phẩy. • Sử dụng danh sách chọn trong câu lệnh SELECT bao gồm các trường hợp sau: SQL Server 2005 124
3.3.1 Chọn tất cả ác c cột trong bảng • Khi cần hiển thị tất cả các trường trong các bảng, sử dụng ký tự * trong danh sách chọn thay vì phải liệt kê danh sách tất cả các cột. • Trong trường hợp này, các cột được hiển thị trong kết quả truy vấn sẽ tuân theo thứ tự àm chúng đã được tạo ra khi bảng được định nghĩa. • Ví dụ: Câu lệnh SELECT * FROM LOP SQL Server 2005 125
3.3.2 Tên cột trong danh sách chọn • Trong trường hợp cần chỉ định cụ thể các cột cần hiển thị trong kết quả truy vấn, ta chỉ định danh sách các tên cột trong danh sách chọn. • Thứ tự của các cột trong kết quả truy vấn tuân theo thứ tự của các trường trong danh sách chọn. • Ví dụ: Câu lệnh SELECT malop, tenlop, namnhaphoc FROM lop SQL Server 2005 126
3.3.3 Thay đổi tiêu đề ác c cột Để đặt tiêu đề cho một cột nào đó, ta sử dụng cách viết: • Tiêu_đề_cột = tên_trường • Hoặc tên_trường AS tiêu_đề_cột • Hoặc tên_trường tiêu_đề_cột SQL Server 2005 127
3.3.3 Thay đổi tiêu đề ác c cột • Ví dụ: Câu lệnh dưới đây: SELECT 'Mã lớp'= malop, tenlop 'Tên lớp', khoahoc AS 'Khóa học' FROM lop Cho kết quả là: SQL Server 2005 128
3.3.4 Hằng àv biểu thức • Ngoài danh sách trường, trong danh sách chọn của câu lệnh SELECT còn có thể sử dụng các biểu thức. • Mỗi một biểu thức trong danh sách chọn trở thành một cột trong kết quả truy vấn. • Ví dụ: Câu lệnh dưới đây cho biết tên và số tiết của các môn học SELECT tenhocphan,sotinchi*15 AS sotiet FROM hocphan SQL Server 2005 129
3.3.4 Hằng àv biểu thức • Nếu trong danh sách chọn có sự xuất hiện của giá trị hằng thì giá trị này sẽ xuât hiện trong một cột của kết quả truy vấn ở tất cả các dòng • Ví dụ: Câu lệnh SELECT tenhocphan,'Số tiết: ', sotinchi*15 AS sotiet FROM hocphan Kết quả câu lệnh như sau: SQL Server 2005 130
3.3.4 Hằng và biểu thức SQL Server 2005 131
3.3.5 Loại bỏ các dòng dữ liệu trùng nhau • Trong kết quả của truy vấn có thể xuất hiện các dòng dữ liệu trùng nhau. Để loại bỏ bớt các dòng này, ta chỉ định thêm từ khóa DISTINCT ngay sau từ khoá SELECT. SQL Server 2005 132
3.3.5 Loại bỏ các dòng dữ liệu trùng nhau • Ví dụ: Hai câu lệnh dưới đây SELECT khoahoc FROM lop và: SELECT DISTINCT khoahoc FROM lop có kết quả lần lượt như sau: SQL Server 2005 133
3.3.6 Giới hạn số lượng dòng trong kết quả truy vấn • Ta chỉ định thêm mệnh đề TOP ngay trước danh sách chọn của câu lệnh SELECT hạn chế số lượng các dòng xuất hiện trong kết quả truy vấn. • Ví dụ: Câu lệnh dưới đây hiển thị họ tên và ngày sinh của 5 sinh viên đầu tiên trong danh sách SELECT TOP 5 hodem,ten,ngaysinh FROM sinhvien SQL Server 2005 134
3.3.6 Giới hạn số lượng dòng trong kết quả truy vấn • Ta có thể chỉ định số lượng các dòng cần hiển thị theo tỷ lệ phần trăm bằng cách sử dụng thêm từ khoá PERCENT như ở ví dụ dưới đây. • Ví dụ: Câu lệnh dưới đây hiển thị họ tên và ngày sinh của 10% số lượng sinh viên hiện có trong bảng SINHVIEN SELECT TOP 10 PERCENT hodem, ten, ngaysinh FROM sinhvien SQL Server 2005 135
3.4 Mệnh đề FROM • Mệnh đề FROM trong câu lệnh SELECT được sử dụng nhằm chỉ định các bảng và khung nhìn cần truy xuất dữ liệu. • Sau FROM là danh sách tên của các bảng và khung nhìn tham gia vào truy vấn, tên của các bảng và khung nhìn được phân cách nhau bởi dấu phẩy. • Ví dụ :Câu lệnh dưới đây hiển thị danh sách các khoa trong trường SELECT * FROM khoa SQL Server 2005 136
3.4 Mệnh đề FROM • Ta có thể sử dụng các bí danh cho các bảng hay khung nhìn trong câu lệnh SELECT. Bí danh được gán trong mệnh đề FROM bằng cách chỉ định bí danh ngay sau tên bảng. • Ví dụ: câu lệnh sau gán bí danh là a cho bảng khoa SELECT * FROM khoa a SQL Server 2005 137
3.5 Chỉ định điều kiện • Mệnh đề WHERE được sử dụng nhằm xác định các điều kiện đối với việc truy xuất dữ liệu. • Sau mệnh đề WHERE là một biểu thức logic và chỉ những dòng dữ liệu nào thoả mãn điều kiện được chỉ định mới được hiển thị trong kết quả truy vấn. SQL Server 2005 138
3.5.1 Các toán tử so sánh Toán tử ý nghĩa = Bằng > Lớn hơn = Lớn hơn hoặc bằng Khác !> Không lớn hơn !< Không nhỏ hơn SQL Server 2005 139
3.5.1 Các toán tử so sánh • Ví dụ 1: Hiển thị danh sách các học phần có số tín chỉ lớn hơn 3 • Ví dụ 2:Hiển thị mã sinh viên thi lại học phần có mã là tinvp và tindc SQL Server 2005 140
3.5.2 Kiểm tra giới hạn của dữ liệu • Để kiểm tra xem giá trị dữ liệu nằm trong (ngoài) một khoảng nào đó, ta sử dụng toán tử BETWEEN (NOT BETWEEN) như sau: Cách sử dụng Ý nghĩa giá_trị BETWEEN a AND b a giá_trị b (giá_trị b) SQL Server 2005 141
3.5.2 Kiểm tra giới hạn của dữ liệu • Ví dụ 1:Hiển thị mã sinh viên thi lần 1 học phần óc mã là ‘Sql’ từ 8 đến 10 điểm. • Ví dụ 2:Hiển thị mã sinh viên thi lần 1 học phần óc mã là ‘Tindc’ có điểm là 5 hoặc 6 hoặc 7 điểm. SQL Server 2005 142
3.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. SQL Server 2005 143
3.5.3 Danh sách (IN và NOT IN) • Ví dụ 1: Hiển thị mã sinh viên thi lần 1 học phần có mã là ‘SQL’ có điểm là 6, 8 hoặc 10 • Ví dụ 2: Hiển thị tên khoa chưa có (không quản lý) sinh viên. SQL Server 2005 144
3.5.4 Toán tử LIKE và các ký 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 : Ký tự ý nghĩa đại diện % 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]). SQL Server 2005 145
3.5.4 Toán tử LIKE và các ký tự đại diện • Ví dụ 1: Cho biết masv,hodem,ten của các sinh viên có họ àl Nguyễn • Ví dụ 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à N • Ví dụ 3: Cho biết họ và tên của các sinh viên có tên bắt đầu bằng ký tự V hoặc L SQL Server 2005 146
3.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 NULL hoặc: WHERE tên_cột IS NOT NULL • Ví dụ: Hiển thị tên khoa chưa nhập dữ liệu về điện thoại. SQL Server 2005 147
3.6 Tạo mới bảng dữ liệu từ kết quả củ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 chọn và số dòng sẽ là số dòng kết quả của truy vấn. • Ví dụ: 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. SQL Server 2005 148
3.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 SELECT • Sau 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. SQL Server 2005 149
3.7 Sắp xếp kết quả truy vấn • Ví dụ 1: Hiển thị danh sách các họ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: SQL Server 2005 150
3.7 Sắp xếp kết quả truy vấn • Ví dụ 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, họ đệm gồm các thông tin: hodem, ten, ngaysinh • Ví dụ 2: Hiển thị masv của 2 sinh viên thi học phần có mã là tindc có điểm cao nhất. SQL Server 2005 151
3.8 Toán tử Union,Except và Intersect 3.8.1 Toán tử Union 3.8.2 Toán tử Except 3.8.3 Toán tử Intersect SQL Server 2005 152
3.8.1 Toán tử Union • Toán tử Union được sử dụng trong trường hợp ta cần gộp kết quả củ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_1 UNION [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]] SQL Server 2005 153
3.8.1 Toán tử Union • Ví dụ: Giả sử ta có hai bảng Table1 và Table2 lần lượt như sau: SQL Server 2005 154
3.8.1 Toán tử Union • Câu lệnh SELECT A,B FROM Table1 UNION SELECT D,E FROM table2 Cho kết quả như sau: SQL Server 2005 155
3.8.1 Toán tử Union • Mặ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í dụ: Câu lệnh SELECT A,B FROM Table1 UNION ALL SELECT D,E FROM table2 SQL Server 2005 156
3.8.1 Toán tử Union • Danh 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. SQL Server 2005 157
3.8.1 Toán tử Union • Khi 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. SQL Server 2005 158
3.8.2 Toán tử Except • Toá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 học phần nào SELECT MASV FROM SINHVIEN EXCEPT SELECT MASV FROM DIEMTHI SQL Server 2005 159
3.8.2 Toán tử Except • Toá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 học phần nào SELECT MASV FROM SINHVIEN EXCEPT SELECT MASV FROM DIEMTHI SQL Server 2005 160
3.8.3 Toán tử Intersect • Intersect 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ị Mã sinh viên thi lại cả hai học phần có mã là Tindc và Tinvp SQL Server 2005 161
3.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ấn - Nhữ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à gì. SQL Server 2005 162
3.9.1 Phép nối trong • Phé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ối SQL Server 2005 163
3.9.1 Phép nối trong • Ví dụ : Để hiển thị họ 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' SQL Server 2005 164
3.9.2 Phép nối ngoài Phé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) SQL Server 2005 165
3.9.2 Phép nối ngoài Phé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 SQL Server 2005 166
3.9.2 Phép nối ngoài • Cho bảng NHANVIEN và Bảng DONVI như sau: SELECT * FROM nhanvien LEFT OUTER JOIN donvi ON nhanvien.madv=donvi.madv SQL Server 2005 167
3.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ị họ 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 họ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, diem SQL Server 2005 168
3.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 bì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 chọ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. SQL Server 2005 169
3.10 Thống kê dữ liệu với GROUP BY Hàm gộp Chức năng SUM([ALL | DISTINCT] Tính tổng các giá trị. biểu_thức) AVG([ALL | DISTINCT] Tính trung bình của các biểu_thức) giá trị COUNT([ALL | DISTINCT] Đếm số các giá trị trong biểu_thức) biểu thức. COUNT(*) Đếm số các dòng được chọn. MAX(biểu_thức) Tính giá trị lớn nhất MIN(biểu_thức) Tính giá trị nhỏ nhất SQL Server 2005 170
3.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 chọn của câu lệnh SELECT. • Trong trường hợp này, trong danh sách chọ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ú ý: miền tác động của hàm gộp lúc này là trên toàn bảng SQL Server 2005 171
3.10.1 Thống kê trên toàn bộ dữ liệu • Ví dụ 1: Hiển thị điểm trung bình lần 1 của sinh viên có mã là dl01-001. • Ví dụ 2: Hiển thị điểm cao nhất của họ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 1 SQL Server 2005 172
3.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. SQL Server 2005 173
3.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 học phần óđ . • Ví dụ 2: Cho biết masv,hodem,ten, trung bình điểm thi lần 1 các học phần của các sinh viên. SQL Server 2005 174
3.10.2 Thống kê dữ liệu trên các nhóm • Lưu ý:Trong trường hợp danh sách chọ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 thì 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. SQL Server 2005 175
3.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 đã được Group by. SQL Server 2005 176
3.10.4 Toán tử Cube • Bao gồm trường hợp Rollup • Cho 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. SQL Server 2005 177
3.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. SQL Server 2005 178
3.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 đã chỉ ra tại mệnh đề GROUP BY SQL Server 2005 179
3.10.6 Mệnh đề Having • Ví dụ1 : hiển thị masv thi lần 1 từ hai họ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í dụ3 : hiển thị masv,hodem,ten, trung bình điểm thi lần 1 của các sinh viên có điểm trung bình lớn hơn hoặc bằng 5. • Ví dụ4 : Hiển thị makhoa, tenkhoa, solop của khoa SQL Server 2005 180
3.10.6 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. SQL Server 2005 181
3.10.6 Mệnh đề Having • Ví dụ: Câu lệnh dưới đây cho biết mã, tên và trung bình điểm lần 1 của các học phần có trung bình lớn hơn trung bình điểm lần 1 của tất cả các học phần SELECT d.mahocphan, tenhocphan, AVG(diem) FROM diemthi d inner join hocphan h on d.mahocphan=h.mahocphan WHERE lanthi=1 GROUP BY d.mahocphan,tenhocphan HAVING AVG(diem)> (SELECT AVG(diem) FROM diemthi WHERE lanthi=1) SQL Server 2005 182
3.11 Thống kê dữ liệu với COMPUTE • Mệ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. SQL Server 2005 183
3.11 Thống kê dữ liệu với COMPUTE • Mệ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ột Trong đó: • 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ệu SQL Server 2005 184
3.11 Thống kê dữ liệu với COMPUTE Ví dụ: danh sách các lớp của mỗi khoa và tổng số các lớp của mỗi khoa SELECT khoa.makhoa, tenkhoa, malop, tenlop FROM khoa,lop WHERE khoa.makhoa=lop.makhoa ORDER BY khoa.makhoa COMPUTE COUNT(malop) BY khoa.makhoa SQL Server 2005 185
3.11 Thống kê dữ liệu với COMPUTE Khi sử dụ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ộp • Hà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 chọn. SQL Server 2005 186
3.11 Thống kê dữ liệu với COMPUTE Khi sử dụ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 thì 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. SQL Server 2005 187
3.11 Thống kê dữ liệu với COMPUTE • Trong trường hợp sử dụng COMPUTE mà không có BY thì 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,hedaotao FROM lop ORDER BY makhoa COMPUTE COUNT(malop) SQL Server 2005 188
3.11 Thống kê dữ liệu với COMPUTE • Có 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 SELECT • Ví dụ: SELECT khoa.makhoa, tenkhoa, malop, tenlop FROM khoa,lop WHERE khoa.makhoa=lop.makhoa ORDER BY khoa.makhoa COMPUTE COUNT(malop) BY khoa.makhoa COMPUTE COUNT(malop) SQL Server 2005 189
3.12 TRUY VẤN CON • Truy 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. SQL Server 2005 190
3.12.1 CÚ PHÁP TRUY VẤN CON • Mộ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ột • Mệ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. SQL Server 2005 191
3.12.1 CÚ PHÁP TRUY VẤN CON • Mộ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) SQL Server 2005 192
3.12.1 CÚ PHÁP TRUY VẤN CON • Khi 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) SQL Server 2005 193
3.12.1 CÚ PHÁP TRUY VẤN CON Ví dụ 1: Hiển thị tên khoa có quản lý lớp Ví dụ 2: Hiển thị masv, họ đệm, tên, tên lớp chưa thi học phần àn o. SQL Server 2005 194
3.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ố họ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ố_họ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. SQL Server 2005 195
3.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 học phần óc số tín chỉ lớn hơn hoặc bằng số tín chỉ của học phần óc mã àl tindc • Ví dụ 2: Cho biết masv, hodem, ten sinh viên có điểm thi môn có mã là tinvp cao điểm nhất • Ví dụ 3: Cho biết họ 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. SQL Server 2005 196
3.12.3 Sử dụ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 đã nêu ở trên là trong danh sách chọn của truy vấn con có thể có nhiều hơn hai cột. SQL Server 2005 197
3.12.3 Sử dụng từ khóa EXISTS Ví dụ: Cho biết họ tên của những sinh viên hiện chưa có điểm thi của bất kỳ một học phần àn o. SQL Server 2005 198
Truy vấn con tương quan • Cú pháp SELECT column1, column2, FROM table1 outer WHERE column1 operator (SELECT colum1, column2 FROM table2 WHERE expr1 = outer.expr2) SQL Server 2005 199
Truy 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. SQL Server 2005 200
Truy vấn con tương quan • Chú ý: Chúng ta có thể sử dụng toán tử ANY, ALL và Exists trong câu lệnh truy vấn con tương quan. SQL Server 2005 201
Truy vấn con tương quan Truy 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 nhau • Câ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ề. SQL Server 2005 202
Truy vấn con tương quan SQL Server 2005 203
Truy vấn con tương quan Thự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ử dụng giá trị của ác c hàng ứng viên. • Sử dụ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. SQL Server 2005 204
Chương 4 Phát biểu T-SQL dạng xử lý dữ liệu 4.1 Chèn dữ liệu 4.2 Cập nhật dữ liệu 4.3 Xoá dữ liệu SQL Server 2005 205
4.1 Chèn dữ liệu • Chè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. SQL Server 2005 206
4.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ị) SQL Server 2005 207
4.1.1 Chèn từng dòng dữ liệu • Danh 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 HOCPHAN INSERT INTO HOCPHAN VALUES(‘tcc’,’ToSQLán Server cao 2005 cấp’,3) 208
4.1.1 Chèn từng dòng dữ liệu • Trong 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). SQL Server 2005 209
4.1.1 Chèn từng dòng dữ liệu Câu lệnh dưới đây chèn một bản ghi mới cho bảng SINHVIEN INSERT 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 sinhvien VALUES (‘tm01-001’,‘ Châu Thế’,’Toàn’, NULL,1,NULL,’tm01’) SQL Server 2005 210
4.1.1 Chèn từng dòng dữ liệu Ví dụ1 : Viết câu lệnh SQL tạo bảng LUUSINHVIEN bao gồm các trường HODEM, TEN, NGAYSINH Ví dụ2 : Chèn 3 dữ liệu cho bảng LUUSINHVIEN theo 3 cách khác nhau. SQL Server 2005 211
4.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_SELECT SQL Server 2005 212
4.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 luusinhvien SELECT hodem,ten,ngaysinh FROM sinhvien WHERE noisinh like N‘%Quảng Nam’ SQL Server 2005 213
4.1.2 Chèn nhiều dòng dữ liệu từ bảng khác Lưu ý: • 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 SQL Server 2005 214
4.2 Cập nhật dữ liệu Câ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ảng SET 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] SQL Server 2005 215
4.2 Cập nhật dữ liệu Ví dụ1 : Câu lệnh sau cập nhật lại số tín chỉ bằng 4 cho học phần có mã àl SQL UPDATE HOCPHAN SET sotinchi = 4 WHERE mahocphan =N’SQL’ Ví dụ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 Bình. SQL Server 2005 216
4.2.1 Cấu trúc CASE trong lệnh UPDATE Cấ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: SQL Server 2005 217
4.2.1 Cấu trúc CASE trong lệnh UPDATE Câu lệnh cập nhật: UPDATE NHATKYPHONG SET tienphong=songay* CASE WHEN loaiphong='A' THEN 100 WHEN loaiphong='B' THEN 70 ELSE 50 END SQL Server 2005 218
4.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. SQL Server 2005 219
4.2.3 ĐK Cập nhật DL liên quan đến nhiều bảng • Giả sử bạn có hai bảng MATHANG và NHATKYBANHANG như sau: SQL Server 2005 220
4.2.3 ĐK Cập nhật DL liên quan đến nhiều bảng Câ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 GIA UPDATE nhatkybanhang SET thanhtien = soluong*gia FROM mathang WHERE nhatkybanhang.mahang = mathang.mahang SQL Server 2005 221
4.2.4 Câu lệnh UPDATE với truy vấn con Câu lệnh ở ví dụ trước được viết như sau: UPDATE nhatkybanhang SET thanhtien = soluong*gia FROM mathang WHERE mathang.mahang =(SELECT mathang.mahang FROM mathang WHERE mathang.mahang=nhatkybanhang.mahang) SQL Server 2005 222
4.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] SQL Server 2005 223
4.3 Xoá dữ liệu Ví 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 Ngãi DELETE FROM sinhvien WHERE noisinh LIKE N‘%Quảng Ngãi’ Ví dụ 1: Xóa những sinh viên 19 tuổi từ bảng SINHVIEN SQL Server 2005 224
4.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ảng SQL Server 2005 225
4.3.2 Xoá dữ liệu khi điều kiện liên quan đến nhiều bảng • Câu lệnh dưới đây xoá ra khỏi bảng SINHVIEN những sinh viên lớp Dữ liệu 1 DELETE FROM sinhvien FROM lop WHERE lop.malop=sinhvien.malop AND tenlop=N'Dữ liệu 1' SQL Server 2005 226
4.3.3 Sử dụ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 học. DELETE FROM lop WHERE 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 SINHVIEN SQL Server 2005 227
4.3.4 Xoá toàn bộ dữ liệu trong bảng • Câ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 vì 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ảng SQL Server 2005 228
4.3.4 Xoá toàn bộ dữ liệu trong bảng Ví dụ: Câu lệnh sau xoá toàn bộ dữ liệu trong bảng diemthi: DELETE FROM diemthi Có tác dụng tương tự với câu lệnh TRUNCATE TABLE diemthi SQL Server 2005 229
Chương 5 THỦ TỤC NỘI TẠI (STORED PROCEDURE) 5.1 Giới thiệu 5.2 Phân loại thủ tục 5.3 Tạo thủ tục 5.4 Lời gọi thủ tục 5.5 Khai báo và sử dụng biến SQL Server 2005 230
Chương 5 THỦ TỤC NỘI TẠI (STORED PROCEDURE) 5.6 Giá trị trả về của tham số trong thủ tục 5.7 Phát biểu điều khiển 5.8 Tham số với giá trị mặc định 5.9 Sửa đổi thủ tục 5.10 Xoá thủ tục SQL Server 2005 231
5.1 Giới thiệu thủ tục • Mộ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 trì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ố SQL Server 2005 232
5.1 Giới thiệu thủ tục Các lợi ích của thủ tục: • Lập trình theo module: Thủ tục được thiết lập trong từng CSDL một lần, có thể gọi thực hiện nhiều lần trong một ứng dụng, có thể gọ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. SQL Server 2005 233
5.1 Giới thiệu thủ tục • Giả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 vì 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 gọi nhiều lần trên các máy khách thì 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 đã được biên dịch. • Bảo mật tốt hơn: Thay vì 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. SQL Server 2005 234
5.2 Phân loại thủ tục Trong SQL Server 2005 có 3 nhóm thủ tụ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 trình .NET. SQL Server 2005 235
5.2 Phân loại thủ tục • Nhó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 Procedure - Thủ 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 mình tạo ra bằng tiền tố này. SQL Server 2005 236
5.2 Phân loại thủ tục • Nhó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 Procedure - Thủ 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 mì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_. SQL Server 2005 237
5.3 Tạo thủ tục • Trong khung Object Explorer, chọn Database chứa thủ tục nội tại cần tạo, chọn Programmability. Kích nút phải chuột lên mục Stored Procedures chọ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_thủ_tục [(danh_sách_tham_số)] [WITH RECOMPILE |ENCRYPTION ] AS Các_câu_lệnh_của_thủ_tục SQL Server 2005 238
5.3 Tạo thủ tục Ví dụ 1: Viết thủ tục nhập vào dữ liệu cho bảng HOCPHAN Ví dụ 2: Viết thủ tục thực hiện công việc sau: • Chèn thêm học phần công nghệ phần mềm có mã cnpm và số tín chỉ là 3 vào bảng HOCPHAN • Lên danh sách nhập điểm thi môn công nghệ phần mềm cho các sinh viên học lớp có mã 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à mã các sinh viên học lớp có mã dl01 và các cột điểm là NULL). SQL Server 2005 239
5.3 Lời gọi thủ tục Thực thi thủ tục bằng lời gọi thủ tục có dạng: • Tên_thủ_tục [danh_sách_các_đối_số] Lời gọ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_thủ_tục [danh_sách_các_đối_số] SQL Server 2005 240
5.3 Lời gọi thủ tục Thứ 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ị SQL Server 2005 241
5.4 Lời gọi thủ tục Ví dụ: • 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=3 SQL Server 2005 242
5.5 Khai báo và sử dụng biến 5.5.1 Khai báo biến DECLARE @tên_biến kiểu_dữ_liệu • Tê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) SQL Server 2005 243
5.5.2 Phát biểu Set • Phá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=0 Set @max=(Select max(diemlan1) from DIEMTHI where mahocphan='sql') SQL Server 2005 244
5.5.2 Phát biểu Set Chú ý: 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ị thì lỗi sẽ phát sinh. SQL Server 2005 245
5.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=0 • Select @max= max(diemlan1), @min=min(diemlan1) from DIEMTHI where mahocphan='sql' SQL Server 2005 246
5.6 Giá trị trả về của tham số trong thủ tục • Xét câu lệnh sau đây CREATE PROCEDURE sp_Conghaiso(@a INT,@b INT, @c INT) AS SELECT @c=@a+@b • Thực thi một tập các câu lệnh như sau: DECLARE @tong INT SELECT @tong=0 EXECUTE sp_Conghaiso 100,200,@tong SELECT @tong Kết quả tong=0 SQL Server 2005 247
5.6 Giá trị trả về của tham số trong thủ tục • Nế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 OUTPUT hoặc: @tên_tham_số kiểu_dữ_liệu OUT • Và trong lời gọ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) SQL Server 2005 248
5.6 Giá trị trả về của tham số trong thủ tụ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) AS SELECT @c=@a+@b SQL Server 2005 249
5.6 Giá trị trả về của tham số trong thủ tục Thực hiện lời gọi thủ tục trong một tập các câu lệnh như sau: DECLARE @tong INT SELECT @tong=0 EXECUTE sp_Conghaiso 100,200, @tong OUTPUT SELECT @tong Thì câu lệnh “SELECT @tong” sẽ cho kết quả là: 300 SQL Server 2005 250
5.7 Cấu trúc điều khiển 5.7.1 Cấu trúc If Else 5.7.2 Cấu trúc While 5.7.3 Phát biểu Continue 5.7.4 Phát biểu Break 5.7.5 Phát biểu Return 5.7.6 Cấu trúc Try Catch 5.7.7 Cấu trúc Case SQL Server 2005 251
5.7.1 Cấu trúc If Else Cấu trúc như sau : IF ELSE Trong Cấu trúc If Else, nếu có từ hai lệnh trở lên thì phải đặt giữa hai từ khóa Begin và End. SQL Server 2005 252
5.7.1 Cấu trúc If Else Ví dụ 1: Viết thủ tục đưa vào một masv. Nếu sinh viên đó là Nam thì 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 mã sinh viên và in ra câu ai sinh trước hơn ai. SQL Server 2005 253
5.7.2 Cấu trúc While • Cấ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 SQL Server 2005 254
Ví dụ: Viết thủ tục in ra tổng củaá c c số từ 1 n. CREATE PROCEDURE Tinhtong(@n int) AS BEGIN Declare @tong int Declare @i int set @tong=0 set @i=0 While @i<=@n Begin Set @tong=@tong+@i Set @i=@i+1 End Print @tong END SQL Server 2005 255
5.7.3 Phát biểu Continue • Phát biểu Continue cho phép bạn bỏ qua các khai báo ngay sau nó trong vòng lặp While • Phát biểu này thường được sử dụng với Cấu trúc điều khiển While. SQL Server 2005 256
Viết thủ tục thực hiện in ra tổng các số lẻ từ 1 n. CREATE PROCEDURE Tong_so_le(@n int) AS BEGIN Declare @tong int Declare @i int Set @tong=0 Set @i=0 While @i<@n Begin Set @i=@i+1 If (@i%2=0) continue Set @tong=@tong+@i End Print @tong END SQL Server 2005 257
5.7.4 Phát biểu Break Phát biểu Break cho phép bạn thoát khỏi vòng lặp hay rẽ nhánh. Ví dụ 1: Viết thủ tục kiểm tra một số nguyên đưa vào có phải là số nguyên tố không. SQL Server 2005 258
CREATE PROCEDURE (@n int) AS BEGIN declare @i int set @i=2 declare @kiemtra bit set @kiemtra=1 While @i<=sqrt(@n) if (@n % @i=0) begin set @kiemtra=0 break end else set @i=@i+1 if (@kiemtra=1) print Str(@n)+ N' là số nguyên tố' else print Str(@n)+ N' Không phải là số nguyên tố' END SQL Server 2005 259
5.7.5 Phát biểu Return • Khi cần trả về một giá trị nào đó. Chúng ta sử dụng phát biểu Return. Nếu gặp phát biểu Return, quá trình xử lý sẽ kết thúc. • Trong thủ tục đôi khi chúng ta sử dụng phát biểu Reture để Strored Procedure trả về giá trị tương tự như àh m. Cú pháp phát biểu này như sau: RETURN [Giá trị nguyên] SQL Server 2005 260
Ví dụ: Viết thủ tục đưa vào một mã sinh viên, thủ tục xuất ra số môn thi lần một không đạt của sinh viên đó. CREATE PROCEDURE THILAI(@MASV NVARCHAR(10)) AS BEGIN Declare @COUNT INT Select @count = (Select count(*) from diemthi where diemlan1<5 and masv=@masv) Return @count END SQL Server 2005 261
5.7.5 Phát biểu Return • Sau khi đã tạo thủ tụcTHILAI , bạn thực thi một tập các câu lệnh như sau: Declare @dem int Exec @dem=thilai 'dl01-002' Print N'Số môn thi lại '+ Str(@dem) SQL Server 2005 262
5.7.6 Cấu trúc Try Catch Cấu trúc Try Catch trong SQL Server 2005 được dùng để bắt lỗi tương tự như trong C# và C++. Một nhóm các lệnh được đặt trong khối Try, nếu có một lỗi xuất hiện bên trong khối Try thì điều khiển được gởi đến một nhóm lệnh khác được đặt trong một khối Catch. SQL Server 2005 263
5.7.6 Cấu trúc Try Catch Cấu trúc Try Catch có cấu trúc như sau: BEGIN TRY { } END TRY BEGIN CATCH { } END CATCH [ ; ] SQL Server 2005 264
5.7.6 Cấu trúc Try Catch Một số thông tin về lỗi: • ERROR_NUMBER(): Trả về mã số lỗi. • ERROR_SEVERITY(): Trả về mức độ của lỗi. • ERROR_STATE(): Mã 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 gọi bên ngoài của khối Catch. SQL Server 2005 265
Ví dụ: Điều khiển lỗi trong thủ tục chia hai số CREATE PROCEDURE phepchia(@sobichia float, @sochia float) AS BEGIN 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 catch END SQL Server 2005 266
5.7.6 Cấu trúc Try Catch • Khi thực thi thủ tục trên: phepchia 3 , 0 • Kết quả trình bày trong ngăn Results và ngăn Messages như sau: SQL Server 2005 267
5.7.7 Cấu trúc Case • Cấ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ả_của_else] END SQL Server 2005 268
5.7.7 Cấu trúc Case • Cấ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ả_của_else] END SQL Server 2005 269
5.7.7 Phát biểu Case Ví dụ1 : Để hiển thị mã, họ 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ệnh SELECT masv,hodem,ten, CASE gioitinh WHEN 1 THEN N'Nam' ELSE N'Nữ' END AS gioitinh FROM sinhvien SQL Server 2005 270
5.7.7 Cấu trúc Case Hoặc: SELECT masv,hodem,ten, CASE WHEN gioitinh=1 THEN N'Nam' ELSE N'Nữ' END AS gioitinh FROM sinhvien SQL Server 2005 271
5.7.7 Cấu trúc Case Ví dụ2 : Nhập vào một masv, mahocphan • Nếu Điểm lần 1>=9 thì hiển thị ra câu thông báo: Bạn “ Họ ten sinh vien” học học phần “ tên học phần” xuất sắc • Nếu 9>Điểm >=8 hiển thị giỏi • Nếu 8>Điểm >=7 hiển thị khá • Nếu 7>Điểm >=5 hiển thị trung bình • Điểm<5 hiển thị yếu SQL Server 2005 272
5.8 Tham số với giá trị mặc định • Giá trị mặc định sẽ được gán cho tham số trong trường hợp không truyền đối số cho tham số khi có lời gọi đến thủ tục. • Cú pháp: @tên_tham_số kiểu_dữ_liệu = giá_trị_mặc_định • Ví dụ: Viết thủ tục sp_TestDefault như sau: SQL Server 2005 273
CREATE PROC sp_TestDefault( @tenlop NVARCHAR(30)=NULL, @noisinh NVARCHAR(100)='Huế' AS BEGIN IF @tenlop IS NULL SELECT hodem,ten FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE noisinh like ('%'+@noisinh) ELSE SELECT hodem,ten FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE noisinh like ('%'+@noisinh) AND tenlop=@tenlop END SQL Server 2005 274
5.8 Tham số với giá trị mặc định Thực hiện ác c lời gọi thủ tục với các mục đích khác nhau như sau: • Cho biết họ tên của các sinh viên sinh tại Huế: sp_testdefault • Cho biết họ tên của các sinh viên lớp dữ liệu 2 sinh tại Huế: sp_testdefault @tenlop=N'dữ liệu 2' SQL Server 2005 275
5.8 Tham số với giá trị mặc định • Cho biết họ tên của các sinh viên sinh tại Quảng nam sp_testDefault @noisinh=N'Quảng nam' • Cho biết họ tên của các sinh viên lớp đồ họa 3 sinh tại Đà Nẵng: sp_testdefault @tenlop=N'Đồ họa 3',@noisinh=N'Đà Nẵng' SQL Server 2005 276
5.9 Sửa đổi thủ tục • Kích nút phải chuột lên thủ tục cần sửa, chọn Modify, cửa sổ Query xuất hiện cho phép bạn hiệu chỉnh lại câu lệnh T-SQL để sửa đổi thủ tục. Cú pháp sửa đổi thủ tục nội tại như sau: ALTER PROCEDURE tên_thủ_tục [(danh_sách_tham_số)] [WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION] AS Các_câu_lệnh_Của_thủ_tục SQL Server 2005 277
5.10 Xoá thủ tục • Kích nút phải chuột lên thủ tục cần xóa, chọn Delete. Hoặc có thể sử dụng câu lệnh DROP PROCEDURE với cú pháp như sau: DROP PROCEDURE tên_thủ_tục SQL Server 2005 278
5.11 KIỂU DỮ LIỆU CURSOR (CON TRỎ) 5.11.1 Giới thiệu Cursor 5.11.2 Quá trình xử lý của Cursor 5.11.3 Khai báo Cursor trong thủ tục SQL Server 2005 279
5.11.1 Giới thiệu Cursor Kiểu dữ liệu Cursor được sử dụng trong trường hợp tính toán trên tập bản ghi, nên nó cho phép bạn định vị được bản ghi cần xử lý trong tập dữ liệu. Bạn cũng có thể lấy ra một hoặc nhiều bản ghi từ vị trí hiện hành trong tập dữ liệu kết quả • Cursor cũng cho phép cập nhật bản ghi trong tập dữ liệu tại vị trí Cursor hiện hành. Bên cạnh đó, bạn cũng có thể cho phép che dấu hay hiển thị bản ghi trong tập dữ liệu. SQL Server 2005 280
5.11.1 Giới thiệu Cursor • SQL Server 2005 cung cấp hai phương thức để gọi Cursor àl T-SQL và hàm Database API (Application Programming Interface). Trong đó, loại phương thức T- SQL dùng ngôn ngữ T-SQL có cú pháp hỗ trợ sử dụng Cursor theo các chuẩn sau SQL-92. Loại phương thức Database API bao gồm hàm trong đối tượng ADO, OLE DB, ODBC. • Chú ý: Một ứng dụng không nên lẫn lộn giữa hai phương thức gọi Cursor. SQL Server 2005 281
5.11.2 Quá trình xử lý của Cursor Mặc dù Cursor loại T-SQL và API có cú pháp khác nhau nhưng quá trình xử lý đều theo quy trình tổng quát sau: • Kết hợp một Cursor với tập bản ghi từ phát biểu T-SQL và định nghĩa đặc tính của Cursor ứng với tập bản ghi cần cập nhật. • Thực thi phát biểu T-SQL để trỏ đến Cursor. • Lấy ra những bản ghi trong Cursor bạn muốn, hành động này được gọi là fetch. Sau đó, di chuyển đến những bản ghi kế tiếp. • Thực hiện các thao tác xóa, sửa dữ liệu (nếu cần) tại dòng hiện thời trong Cursor. • Đóng Cursor SQL Server 2005 282
5.11.2.1 Khai báo Cursor • Khai báo Cursor sử dụng T-SQL có cú pháp như sau: DECLARE Tên_Cursor [INSENSITIVE] [SCROLL] CURSOR FOR Câu_lệnh_Select [FOR {READ ONLY| UPDATE [ OF tên_cột [, n]]} ] SQL Server 2005 283
5.11.2.1 Khai báo Cursor • INSENSITIVE: Cho phép bạn định nghĩa Cursor ứng với bản sao tập dữ liệu tạm thời dùng cho Cursor. Mọi đòi hỏi của Cursor đều được lấy từ tập dữ liệu này trong CSDL tempdb. • SCROLL: Chỉ định tùy chọn lấy bản ghi theo hình thức NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE. Đối với trường hợp không khai báo tùy chọn àn y trong khai báo Cursor thì NEXT là tùy chọn mặc định. • READ ONLY cho phép bạn ngăn ngừa cập nhật hay xóa dữ liệu trên Cursor. • UPDATE [ OF tên_cột [, n]: Cột cho phép cập nhật. Nếu chỉ định trong tùy chọn này thì bạn chỉ có thể cập nhật dữ liệu trong những cột này. SQL Server 2005 284
5.11.2.1 Khai báo Cursor • Khai báo biến Cursor_Hocphan như sau DECLARE Cursor_Hocphan CURSOR FOR Select * from hocphan SQL Server 2005 285
5.11.2.2 Mở Cursor Sau khi khai báo biến Cursor, bạn có thể mở Curosr theo cú pháp sau: • OPEN Tên_Cursor • Ví dụ: OPEN Cursor_Hocphan SQL Server 2005 286
5.11.2.3 Lấy dữ liệu Tùy thuộc vào cột dữ liệu mà phát biểu SELECT trả về, bạn có thể sử dụng phát biểu FETCH với cú pháp như sau: FETCH [[NEXT | PRIOR | FIRST| LAST |ABSOLUTE {n| @nvar}| RELATIVE {n| @nvar}] FROM ] {{[GLOBAL]Tên_Cursor}|Tên_biến_Curs or} [INTO Tên_biến [/ n]] SQL Server 2005 287
5.11.2.3 Lấy dữ liệu • NEXT: Cho phép lấy bản ghi ngay sau bản ghi hiện hành và chỉ định bản ghi vừa lấy ra là bản ghi hiện hành. • PRIOR: Cho phép lấy bản ghi trước bản ghi hiện hành và chỉ định bản ghi này là bản ghi hiện hành. • FIRST: Cho phép lấy bản ghi đầu tiên và chỉ định bản ghi này là bản ghi hiện hành. • LAST: Cho phép lấy bản ghi cuối cùng và chỉ định bản ghi này là bản ghi hiện hành. SQL Server 2005 288
5.11.2.3 Lấy dữ liệu • ABSOLUTE {n| @nvar}: - Nếu n hoặc @nvar là số dương thì cho phép lấy ra bản ghi thứ n so với bản ghi đầu tiên rồi chỉ định bản ghi này là bản ghi hiện hành tính từ trên xuống. - Nếu n hoặc @nvar là số âm thì cho phép lấy ra bản ghi thứ n so với bản ghi cuối cùng rồi chỉ định bản ghi này là bản ghi hiện hành tính từ dưới lên. SQL Server 2005 289
5.11.2.3 Lấy dữ liệu • RELATIVE {n| @nvar}: - Nếu n hoặc @nvar là số dương thì cho phép lấy ra bản ghi thứ n so với bản ghi hiện hành rồi chỉ định bản ghi này là bản ghi hiện hành tính từ trên xuống. - Nếu n hoặc @nvar là số âm thì cho phép lấy ra bản ghi thứ n so với bản ghi hiện hành rồi chỉ định bản ghi này là bản ghi hiện hành tính từ dưới lên. SQL Server 2005 290
5.11.2.3 Lấy dữ liệu • Ví dụ: Để lấy dữ liệu của các cột khai báo trong phát biểu SQL vào các biến cục bộ thì bạn khai báo như sau DECLARE @Mahocphan nvarchar(10) DECLARE @Tenhocphan nvarchar(50) DECLARE @Sotinchi smallint FETCH NEXT FROM Cursor_Hocphan INTO @Mahocphan, @Tenhocphan, @Sotinchi SQL Server 2005 291
5.11.2.4 Duyệt bản ghi Để duyệt qua từng bản ghi trong Cursor, bạn có thể sử dụng àh m @@FETCH_STATUS và phát biểu WHILE. Hàm @@FETCH_STATUS trả về giá trị 0 ứng với bản ghi lấy ra hợp lệ. Phát biểu sau sẽ in toàn bộ dữ liệu trong biến Cursor_Hocphan WHILE @@FETCH_STATUS=0 BEGIN PRINT @Mahocphan + ‘-’ + @Tenhocphan + ‘-’ +@Sotinchi FETCH NEXT FROM Cursor_Hocphan INTO @Mahocphan, @Tenhocphan, @Sotinchi END SQL Server 2005 292
5.11.2.5 Đóng và giải phóng bộ nhớ cho biến Cursor Sau khi kết thúc làm việc với Cursor, bạn cần khai áb o để đóng Cursor và giải phóng bộ nhớ đã cấp cho nó với úc pháp như sau: CLOSE Tên_Cursor DEALLOCATE Tên_Cursor Ví dụ: CLOSE Cursor_Hocphan DEALLOCATE SQLCursor_Hocphan Server 2005 293
5.11.2.4 Duyệt bản ghi Để duyệt qua từng bản ghi trong Cursor, bạn có thể sử dụng àh m @@FETCH_STATUS và phát biểu WHILE. Hàm @@FETCH_STATUS trả về giá trị 0 ứng với bản ghi lấy ra hợp lệ. Phát biểu sau sẽ in toàn bộ dữ liệu trong biến Cursor_Hocphan WHILE @@FETCH_STATUS=0 BEGIN PRINT @Mahocphan + ‘-’ + @Tenhocphan + ‘-’ +@Sotinchi FETCH NEXT FROM Cursor_Hocphan INTO @Mahocphan, @Tenhocphan, @Sotinchi END SQL Server 2005 294
5.11.2.4 Khai báo Cursor trong thủ tục và hàm Phần này sẽ trình bày các khai báo Cursor trong thủ tục và hàm cũng như sử dụng các từ khóa NEXT| PRIOR | FIRST| LAST| ABSOLUTE| RELATIVE để lấy ra bản ghi theo yêu cầu. SQL Server 2005 295
Chương 6: HÀM 6.1. Giới thiệu 6.2. Hàm hệ thống 6.3. Hàm do người dùng định nghĩa SQL Server 2005 296
6.1. Giới thiệu • Hàm trả về một giá trị thông qua tên hàm còn thủ tục thì không. • Hàm được sử dụng như là một thành phần của một biểu thức. • Hàm có hai loại: Hàm hệ thống và hàm do người dụng định nghĩa SQL Server 2005 297
6.2 Hàm hệ thống 6.2.1 Hàm hệ thống trả về giá trị đơn 6.2.2 Hàm trả về giá trị tổng hợp 6.2.3 Hàm trả về khoảng giá trị Ranking SQL Server 2005 298
6.2.1 Hàm hệ thống trả về giá trị đơn • Nhóm hàm Configuration (Cấu hình) • Nhóm hàm Cursor (Kiểu con trỏ) • Nhóm hàm Date and Time • Nhóm hàm Mathematical (Toán học) • Nhóm hàm Metadata (Siêu dữ liệu) • Nhóm hàm Security (Bảo mật) • Nhóm hàm String (Chuỗi) • Nhóm hàm khác SQL Server 2005 299
Nhóm hàm Configuration (Cấu hình) • Lấy thông tin về cấu hình hiện hành của SQL Server SQL Server 2005 300
6.2.2 Hàm trả về giá trị tổng hợp • Dùng để tổng hợp hay thống kê dữ liệu • 5 Hàm cơ bản thường được sử dụng nhiều nhất khi khai báo phát biểu Select với mệnh đề Group by là Sum, Count, Avg, Max, Min. SQL Server 2005 301
6.2.3 Hàm trả về khoảng giá trị Ranking Hàm thuộc nhóm Ranking dùng để kết hợp xếp loại tập kết quả. Bao gồm các hàm sau: • Rank • Ntile • Dense_Rank • Row_Number SQL Server 2005 302
Hàm Rank • Hàm này dùng để tạo ra cột vị thứ được sắp xếp dựa vào giá trị của một cột hoặc sắp xếp trên từng nhóm (PARTITION). • Cú pháp của hàm Rank như sau: Rank() Over ([Partition By ] Order By [DESC|ASC]) SQL Server 2005 303
Hàm Rank • Ví dụ: Hiển thị danh sách điểm thi lần 1 họ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.MASV WHERE MAHOCPHAN='TINVP' AND LANTHI=1 SQL Server 2005 304
Hàm Rank • Kết quả SQL Server 2005 305