›
›

Giáo trình SQL Server 2005 (Phần 1)

pdf 55 trang phuongnguyen 24460
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình SQL Server 2005 (Phần 1)", để 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:

  • pdfgiao_trinh_sql_server_2005_phan_1.pdf

Nội dung text: Giáo trình SQL Server 2005 (Phần 1)

  1. Mục lục Mục lục 1 1 Giới thiệu về SQL Server 2005 5 1.1 Cài đặt SQL Server 2005 Express Edition 5 1.1.1 Các yêu cầu cho hệ thống 32bit 5 1.1.2 Các bước cài đặt SQL Server 2005 Express Edition 7 1.2 Một số thao tác cơ bản trên SQL Server 2005 Express Edition. 16 1.2.1 Tạo một CSDL mới 16 1.2.2 Tạo bảng mới 17 1.2.3 Xóa bảng, xóa CSDL 19 1.2.4 Mở một query editor để viết câu lệnh SQL 19 2 Structured Query Language (SQL) 20 2.1 SQL là ngôn ngữ của cơ sở dữ liệu quan hệ 20 2.2 Vai trò của SQL 20 2.3 Giới thiệu sơ lược về Transact SQL (T-SQL) 21 2.3.1 Ngôn ngữ định nghĩa dữ liệu ( Data Definition Language – DDL) 22 2.3.2 Ngôn ngữ điều khiển dữ liệu (Data control language – DCL) 22 2.3.3 Ngôn ngữ thao tác dữ liệu (Data manipulation language – DML) 23 2.3.4 Cú pháp của T-SQL 24 2.3.5 Các kiểu dữ liệu 25 2.3.6 Biến (Variables) 26 2.3.7 Hàm (Function) 27 2.3.8 Các toán tử (Operators) 27 2.3.9 Các thành phần điều khiển (Control of flow) 28 2.3.10 Chú thích (Comment) 28 2.3.11 Giá trị NULL 28 3 Ngôn ngữ thao tác dữ liệu – DML 29 3.1 Câu lệnh SELECT 29 3.1.1 Danh sách chọn trong câu lệnh SELECT 30 3.1.2 Mệnh đề FROM 34 3.1.3 Mệnh đề WHERE - điều kiện truy vấn dữ liệu 34 3.1.4 Phép hợp (UNION) 38 3.1.5 Phép nối 41 3.1.6 Các loại phép nối 43 1
  2. 3.1.7 Phép nối theo chuẩn SQL-92 45 3.1.8 Mệnh đề GROUP BY 47 3.1.9 Truy vấn con (Subquery) 50 3.2 Thêm, cập nhật và xóa dữ liệu 51 3.2.1 Thêm dữ liệu 52 3.2.2 Cập nhật dữ liệu 53 3.2.3 Xóa dữ liệu 54 4 Ngôn ngữ định nghĩa dữ liệu – DDL 56 4.1 Tạo bảng 56 4.2 Các loại ràng buộc 58 4.2.1 Ràng buộc CHECK 58 4.2.2 Ràng buộc PRIMARY KEY 59 4.2.3 Ràng buộc FOREIGN KEY 60 4.3 Sửa đổi định nghĩa bảng 61 4.4 Xóa bảng 63 4.5 Khung nhìn - VIEW 63 4.6 Thêm, cập nhật, xóa dữ liệu trong VIEW 65 4.7 Thay đổi định nghĩa khung nhìn 65 4.8 Xóa khung nhìn 66 5 Thủ tục lưu trữ, hàm và trigger 67 5.1 Thủ tục lưu trữ (Stored procedure) 67 5.1.1 Tạo thủ tục lưu trữ 68 5.1.2 Lời gọi thủ tục 69 5.1.3 Biến trong thủ tục lưu trữ 69 5.1.4 Giá trị trả về trong thủ tục lưu trữ 70 5.1.5 Tham số với giá trị mặc định 71 5.1.6 Sửa đổi thủ tục 72 5.1.7 Xóa thủ tục 72 5.2 Hàm do người dùng định nghĩa (User Defined Function-UDF) 72 5.2.1 Hàm vô hướng - Scalar UDF 73 5.2.2 Hàm nội tuyến - Inline UDF 74 5.2.3 Hàm bao gồm nhiều câu lệnh bên trong – Multi statement UDF 75 5.2.4 Thay đổi hàm 76 5.2.5 Xóa hàm 77 5.3 Trigger 77 2
  3. 5.3.1 Các đặc điểm của trigger 77 5.3.2 Các trường hợp sử dụng trigger 77 5.3.3 Khả năng sau của trigger 78 5.3.4 Định nghĩa trigger 78 5.3.5 Kích hoạt trigger dựa trên sự thay đổi dữ liệu trên cột 82 5.3.6 Sử dụng trigger và Giao tác (TRANSACTION) 83 5.4 DDL TRIGGER 84 5.5 Enable/ Disable TRIGGER 85 6 Sao lưu và phục hồi dữ liệu (Backup and Restore) 87 6.1 Các lý do phải thực hiện Backup 87 6.2 Các loại Backup 87 6.2.1 Full backup và Differential backup 87 6.2.2 Transaction log backup 88 6.3 Các thao tác thực hiện quá trình Backup và Restore trong SQL Server 2005 Express Edition 89 6.3.1 Sao lưu (Backup) 89 6.3.2 Phục hồi (Restore) 91 7 Các hàm quan trọng trong T-SQL 94 7.1 Các hàm làm việc với kiểu dữ liệu số 94 7.1.1 Hàm ISNUMERIC 94 7.1.2 Hàm ROUND 94 7.2 Các hàm làm việc với kiểu dữ liệu chuỗi 95 7.2.1 Hàm LEFT 95 7.2.2 Hàm RIGHT 95 7.2.3 Hàm SUBSTRING 95 7.2.4 Hàm LEN 96 7.2.5 Hàm REPLACE 96 7.2.6 Hàm STUFF 96 7.2.7 Hàm LOWER/UPPER 97 7.2.8 Hàm LTRIM/RTRIM 97 7.3 Các hàm làm việc với kiểu dữ liệu Ngày tháng/ Thời gian 97 7.3.1 Hàm GETDATE 97 7.3.2 Hàm DAY/ MONTH/ YEAR 97 7.3.3 Hàm DATEPART 98 7.3.4 Hàm DATENAME 99 3
  4. 7.4 Hàm CAST và CONVERTER 99 8 Kết nối vào SQL Server 2005 từ các ngôn ngữ lập trình để xây dựng các ứng dụng liên quan đến CSDL 101 8.1 Cấu hình Microsoft SQL Server 2005 101 8.1.1 Cho phép tiếp nhận các kết nối từ xa trên thể hiện của SQL Server 102 8.1.2 Kích hoạt dịch vụ SQL Server Browser 102 8.1.3 Tạo các ngoại lệ trên Windows Firewall 103 8.2 Kết nối vào SQL Server trong các ngôn ngữ lập trình 104 8.2.1 C# và VB.NET 104 8.2.2 VB 6 106 Tài liệu tham khảo 108 4
  5. 1 Giới thiệu về SQL Server 2005 SQL Server 2005 là một hệ thống quản lý cơ sở dữ liệu (Relational Database Management System (RDBMS) ) sử dụng Transact-SQL để trao đổi dữ liệu giữa Client computer và SQL Server computer. Một RDBMS bao gồm databases, database engine và các ứng dụng dùng để quản lý dữ liệu và các bộ phận khác nhau trong RDBMS. SQL Server 2005 được tối ưu để có thể chạy trên môi trường cơ sở dữ liệu rất lớn (Very Large Database Environment) lên đến Tera-Byte và có thể phục vụ cùng lúc cho hàng ngàn user. SQL Server 2005 có thể kết hợp "ăn ý" với các server khác như Microsoft Internet Information Server (IIS), E-Commerce Server, Proxy Server Các phiên bản của SQL Server 2005: Enterprise: Hỗ trợ không giới hạn số lượng CPU và kích thước Database. Hỗ trợ không giới hạn RAM (nhưng tùy thuộc vào kích thước RAM tối đa mà HĐH hỗ trợ) và các hệ thống 64bit. Standard: Tương tự như bản Enterprise nhưng chỉ hỗ trợ 4 CPU. Ngoài ra phiên bản này cũng không được trang bị một số tính năng cao cấp khác. Workgroup: Tương tự bản Standard nhưng chỉ hỗ trợ 2 CPU và tối đa 3GB RAM Express: Bản miễn phí, hỗ trợ tối đa 1CPU, 1GB RAM và kích thước Database giới hạn trong 4GB. Chi tiết có thể tham khảo tại địa chỉ: 1.1 Cài đặt SQL Server 2005 Express Edition 1.1.1 Các yêu cầu cho hệ thống 32bit Express Edition System Requirements 32-bit Processor PIII 600MHZ hoặc cao hơn Tốt nhất: 1GHZ hoặc cao hơn Framework Microsoft .NET Framework 2.0 Operating Windows XP with Service Pack 2 hoặc cao hơn System • • Microsoft Windows 2000 Professional SP4 5
  6. Express Edition System Requirements 32-bit • Microsoft Windows 2000 Server Service Pack 4 hoặc cao hơn • Windows Server 2003 Standard, Enterprise, or Datacenter editions with Service Pack 1 hoặc cao hơn • Windows Server 2003 Web Edition SP1 • Windows Small Business Server 2003 with Service Pack 1 hoặc cao hơn • Vista Home Basic và các phiên bản cao hơn (SQL Express SP1 and SQL Express Advanced SP2) • Windows XP Embedded SP2 Feature Pack 2007 • Windows Embedded for Point of Service SP2 Memory 192 MB RAM hoặc cao hơn; tốt nhất: 512 MB hoặc cao hơn Hard Disk • 350 MB ổ cứng cho các cài đặt cơ bản • 425 MB ổ cứng cho các cài đặt SQL Server Books Online, SQL Server Mobile Books Online, và sample databases Drive CD-ROM or DVD-ROM drive Display Super VGA (1,024x768) hoặc cao hơn Other Devices Mouse, Keyboard Other Microsoft Internet Explorer 6.0 SP1 hoặc cao hơn Requirements Chi tiết yêu cầu hệ thống cho các phiên bản Microsoft SQL Server 2005 có thể tham khảo tại địa chỉ: Download và cài đặt Microsoft .NET Framework 2.0: Để cài đặt thành công SQL Server Express Edition hay các phiên bản SQL Server 2005 khác, Microsoft .NET Framework 2.0 phải được cài đặt trước. Gỡ bỏ các phiên bản Beta, CTP hoặc Tech Preview của SQL Server 2005, Visual Studio 2005 và Microsoft .NET Framework 2.0. Download và cài đặt 6
  7. Cài đặt SQL Server 2005 Express Edition: Microsoft SQL Server 2005 Express Edition là phiên bản miễn phí, dễ sử dụng và “nhẹ” của Microsoft SQL Server 2005. Microsoft SQL Server 2005 Express Edition được tích hợp trong Visual Studio 2005 tạo ra sự dễ dàng trong việc phát triển các ứng dụng hướng CSDL. SQL Server 2005 Express Edition được tự do sử dụng trong các ứng dụng thương mại và dễ dàng cập nhật lên các phiên bản cao hơn khi cần thiết. Cài đặt SQL Server Management Studio Express: SQL Server Management Studio Express cung cấp giao diện để người dùng dễ dàng tương tác với các thành phần của Microsoft SQL Server 2005 Express Edition. Trước khi cài đặt SQL Server Management Studio Express, MSXML 6.0 phải được cài đặt Download tại địa chỉ: 1.1.2 Các bước cài đặt SQL Server 2005 Express Edition Double click vào file cài đặt Microsoft SQL Server Express Edition. Click Next: 7
  8. Lưu ý: SQL Server 2005 có hai kiểu authentication (kiểm tra người dùng). Windows authentication mode: Việc kiểm tra người dùng của SQL Server 2005 sẽ phụ thuộc vào việc kiểm tra người dùng của Windows. Khi người dùng có quyền đăng nhập vào Windows, người dùng đó sẽ có quyền đăng nhập vào SQL Server. Kiểu kiểm tra người dùng này thường được sử dụng khi ứng dụng khai thác dữ liệu và SQL Server được cài trên cùng một máy tính. SQL Server authentication mode: Việc kiểm tra người dùng của SQL Server 2005 sẽ không phụ thuộc vào việc kiểm tra người dùng của Windows. Khi người dùng có quyền đăng nhập vào Windows, người dùng đó chưa chắc sẽ có quyền đăng nhập vào SQL Server. Để đăng nhập vào SQL Server, người dùng này phải có một bộ username và password do SQL Server quản lý. Kiểu kiểm tra người dùng này thường được sử dụng khi ứng dụng khai thác dữ liệu và SQL Server không được cài trên cùng một máy tính. Khi chọn Mixed mode, SQL Server có thể dùng bất kỳ kiểu kiểm tra người dùng nào khi cần thiết. Đây là một thiết lập thực sự rất hữu ích khi xây dựng các ứng dụng CSDL. Ngoài ra, ta cũng phải đánh password vào hai ô bên dưới để có thể đăng nhập vào SQL Server khi ta xây dựng một ứng dụng truy xuất vào CSDL ở máy này khi ta đang ở máy khác. Click Next ba lần: 13
  9. Cài đặt SQL Server Management Studio Express. Sau khi cài đặt, đăng nhập vào SQL Server 2005 Express Edition như sau: Khi đăng nhập có thể chọn Windows Authentication hoặc SQL Server Authentication. Nếu chọn SQL Server Authentication thì phải nhập password. Password này được thiết lập trong quá trình cài đặt SQL Server 2005 Express Edition. Nếu trong quá trình cài đặt SQL Server 2005 Express Edition chúng ta không cho phép SQL Server kích hoạt ngay khi khởi động máy, bấm nút Connect sẽ gây ra lỗi. Để khắc phục vào Start->Run đánh services.msc->Enter. Tìm service SQL Server (SQLExpress), double click và trong comboxbox Startup type chọn Automatic -> Apply - >Start -> OK. Giao diện sau khi đăng nhập thành công 15
  10. 1.2 Một số thao tác cơ bản trên SQL Server 2005 Express Edition. Microsoft SQL Server Management Studio cung cấp một giao diện thân thiện giúp cho người dùng thực hiện các thao tác một cách dễ dàng. Một số các thao tác cơ bản bao gồm: tạo CSDL mới, xóa CSDL, tạo bảng, xóa bảng Cũng cần lưu ý rằng các thao tác thực hiện thông qua giao diện thì đều có thể được thực hiện được bằng các câu lệnh SQL. 1.2.1 Tạo một CSDL mới 16
  11. Đặt tên Database trong Textbox Database Name, click OK. 1.2.2 Tạo bảng mới 17
  12. Bảng gồm các các cột. Mỗi cột gồm tên cột (Column Name), kiểu dữ liệu (Data Type) và một giá trị cho biết cột đó có thể chứa giá trị NULL hay không. Trong bảng sẽ có ít nhất một cột làm khóa chính (primary key). Cột làm khóa chính sẽ có biểu tượng chìa khóa trước tên cột. Sau khi tạo xong tất cả các cột của bảng, tiến hành Save -> OK 18
  13. 1.2.3 Xóa bảng, xóa CSDL Click chuột phải lên bảng hay CSDL muốn xóa -> Delete - >OK. Trong trường hợp xóa một CSDL, nên chọn dấu tích vào Close existing connections. Khi đó SQL Server 2005 sẽ ngắt tất cả các kết nối vào CSDL này và việc xóa sẽ không gây báo lỗi. 1.2.4 Mở một query editor để viết câu lệnh SQL Cần chú ý là câu lệnh SQL sẽ có tác dụng trên CSDL đang được chọn trong ComboBox. Do đó cần chú ý lựa chọn đúng CSDL cần tương tác. 19
  14. 2 Structured Query Language (SQL) 2.1 SQL là ngôn ngữ của cơ sở dữ liệu quan hệ SQL, viết tắt của Structured Query Language (ngôn ngữ hỏi có cấu trúc), là công cụ sử dụng để tổ chức, quản lý và truy xuất dữ liệu đuợc lưu trữ trong các cơ sở dữ liệu. SQL là một hệ thống ngôn ngữ bao gồm tập các câu lệnh sử dụng để tương tác với cơ sở dữ liệu quan hệ. Khả năng của SQL vượt xa so với một công cụ truy xuất dữ liệu, mặc dù đây là mục đích ban đầu khi SQL được xây dựng nên và truy xuất dữ liệu vẫn còn là một trong những chức năng quan trọng của nó. SQL được sử dụng để điều khiển tất cả các chức năng mà một hệ quản trị cơ sở dữ liệu cung cấp cho người dùng bao gồm: Định nghĩa dữ liệu: SQL cung cấp khả năng định nghĩa các cơ sở dữ liệu, các cấu trúc lưu trữ và tổ chức dữ liệu cũng như mối quan hệ giữa các thành phần dữ liệu. Truy xuất và thao tác dữ liệu: Với SQL, người dùng có thể dễ dàng thực hiện các thao tác truy xuất, bổ sung, cập nhật và loại bỏ dữ liệu trong các cơ sở dữ liệu. Điều khiển truy cập: SQL có thể được sử dụng để cấp phát và kiểm soát các thao tác của người sử dụng trên dữ liệu, đảm bảo sự an toàn cho cơ sở dữ liệu Đảm bảo toàn vẹn dữ liệu: SQL định nghĩa các ràng buộc toàn vẹn trong cơ sở dữ liệu nhờ đó đảm bảo tính hợp lệ và chính xác của dữ liệu trước các thao tác cập nhật cũng như các lỗi của hệ thống. Như vậy, có thể nói rằng SQL là một ngôn ngữ hoàn thiện được sử dụng trong các hệ thống cơ sở dữ liệu và là một thành phần không thể thiếu trong các hệ quản trị cơ sở dữ liệu. Mặc dù SQL không phải là một ngôn ngữ lập trình như C, C++, Java, song các câu lệnh mà SQL cung cấp có thể được nhúng vào trong các ngôn ngữ lập trình nhằm xây dựng các ứng dụng tương tác với cơ sở dữ liệu. Khác với các ngôn ngữ lập trình quen thuộc như C, C++, Java, SQL là ngôn ngữ có tính khai báo. Với SQL, người dùng chỉ cần mô tả các yêu cầu cần phải thực hiện trên cơ sở dữ liệu mà không cần phải chỉ ra cách thức thực hiện các yêu cầu như thế nào. Chính vì vậy, SQL là ngôn ngữ dễ tiếp cận và dễ sử dụng. 2.2 Vai trò của SQL Bản thân SQL không phải là một hệ quản trị cơ sở dữ liệu, nó không thể tồn tại độc lập. SQL thực sự là một phần của hệ quản trị cơ sở dữ liệu, nó xuất hiện trong các hệ quản trị cơ sở dữ liệu với vai trò ngôn ngữ và là công cụ giao tiếp giữa người sử dụng và hệ quản trị cơ sở dữ liệu. 20
  15. Trong hầu hết các hệ quản trị cơ sở dữ liệu quan hệ, SQL có những vai trò như sau: SQL là ngôn ngữ hỏi có tính tương tác: Người sử dụng có thể dễ dàng thông qua các trình tiện ích để gởi các yêu cầu dưới dạng các câu lệnh SQL đến cơ sở dữ liệu và nhận kết quả trả về từ cơ sở dữ liệu SQL là ngôn ngữ lập trình cơ sở dữ liệu: Các lập trình viên có thể nhúng các câu lệnh SQL vào trong các ngôn ngữ lập trình để xây dựng nên các chương trình ứng dụng giao tiếp với cơ sở dữ liệu SQL là ngôn ngữ quản trị cơ sở dữ liệu: Thông qua SQL, người quản trị cơ sở dữ liệu có thể quản lý được cơ sở dữ liệu, định nghĩa các cấu trúc lưu trữ dữ liệu, điều khiển truy cập cơ sở dữ liệu, SQL là ngôn ngữ cho các hệ thống khách/chủ (client/server): Trong các hệ thống cơ sở dữ liệu khách/chủ, SQL được sử dụng như là công cụ để giao tiếp giữa các trình ứng dụng phía máy khách với máy chủ cơ sở dữ liệu. SQL là ngôn ngữ truy cập dữ liệu trên Internet: Cho đến nay, hầu hết các máy chủ Web cũng như các máy chủ trên Internet sử dụng SQL với vai trò là ngôn ngữ để tương tác với dữ liệu trong các cơ sở dữ liệu. SQL là ngôn ngữ cơ sở dữ liệu phân tán: Đối với các hệ quản trị cơ sở dữ liệu phân tán, mỗi một hệ thống sử dụng SQL để giao tiếp với các hệ thống khác trên mạng, gởi và nhận các yêu cầu truy xuất dữ liệu với nhau. SQL là ngôn ngữ sử dụng cho các cổng giao tiếp cơ sở dữ liệu: Trong một hệ thống mạng máy tính với nhiều hệ quản trị cơ sở dữ liệu khác nhau, SQL thường được sử dụng như là một chuẩn ngôn ngữ để giao tiếp giữa các hệ quản trị cơ sở dữ liệu. 2.3 Giới thiệu sơ lược về Transact SQL (T-SQL) Transact-SQL là ngôn ngữ SQL mở rộng dựa trên SQL chuẩn của ISO (International Organization for Standardization) và ANSI (American National Standards Institute) được sử dụng trong SQL Server khác với P-SQL (Procedural-SQL) dùng trong Oracle. SQL chuẩn bao gồm khoảng 40 câu lệnh. Trong các hệ quản trị cơ sở dữ liệu khác nhau, mặc dù các câu lệnh đều có cùng dạng và cùng mục đích sử dụng song mỗi một hệ quản trị cơ sở dữ liệu có thể có một số thay đổi nào đó. Điều này đôi khi dẫn đến cú pháp chi tiết của các câu lệnh có thể sẽ khác nhau trong các hệ quản trị cơ cơ sở dữ liệu khác nhau. T-SQL được chia làm 3 nhóm: 21
  16. 2.3.1 Ngôn ngữ định nghĩa dữ liệu ( Data Definition Language – DDL) Đây là những lệnh dùng để tạo (create), thay đổi (alter) hay xóa (drop) các đối tượng trong CSDL. Các câu lệnh DDL thường có dạng: Create object Alter object Drop object Trong đó object có thể là: table, view, storedprocedure, function, trigger Ví dụ: Câu lệnh Create sau sẽ tạo một bảng mới có tên là Nhanvien trong CSDL Test. Bảng Nhanvien này gồm có ba cột: manv, tennv, diachi. Lưu ý: Nếu trong SQL Server 2005 Express Edition chưa có CSDL Test, hãy tạo một CSDL có tên Test theo hướng dẫn trong Chương 1. create table Nhanvien ( manv int primary key, tennv nvarchar(50) not null, diachi nvarchar(50) not null ) Để chạy câu lệnh SQL trên, mở một Query Editor, copy câu lệnh vào Query Editor, bôi đen toàn bộ câu lệnh và bấm F5. Tiếp theo, dùng lệnh alter để thay đổi cấu trúc bảng Nhanvien.Cụ thể là một thêm một cột mới có tên ghichu vào bảng Nhanvien. alter table Nhanvien add ghichu nvarchar(50) not null Cuối cùng, dùng lệnh drop để xóa hoàn toàn bảng Nhanvien ra khỏi CSDL, nghĩa là toàn bộ định nghĩa bảng và các dữ liệu bên trong đều bị xóa. drop table Nhanvien Lưu ý: Lệnh drop khác với lệnh delete. Lệnh delete chỉ xóa các dòng dữ liệu có trong bảng 2.3.2 Ngôn ngữ điều khiển dữ liệu (Data control language – DCL) Đây là các lệnh quản lý quyền truy cập lên các object (table, view, storedprocedure ). Bao gồm: Grant Deny Revoke 22
  17. Ví dụ: Lệnh grant sẽ cấp quyền Select trên bảng Nhanvien trong CSDL Test cho các Users thuộc Role public grant select on nhanvien to public Sau khi thực hiên lệnh này, có Users trong Role public có thể thực hiện câu lệnh Select trên bảng Nhanvien trong CSDL Test. Dùng lệnh deny để từ chối quyền select trên bảng Nhanvien trong CSDL Test của các Users thuộc Role public deny select on nhanvien to public Sau khi thực hiện lệnh này, có Users trong Role public sẽ không thể thực hiện câu lệnh Select trên bảng Nhanvien trong CSDL Test. Dùng lệnh revoke để xóa bỏ các quyền được cấp hay từ chối trươc đó. revoke select on nhanvien to public Sau khi thực hiện lệnh này, các quyền được gán hay từ chối của Users trong Role public trên bảng Nhanvien trong CSDL Test sẽ được “xóa” hoàn toàn. 2.3.3 Ngôn ngữ thao tác dữ liệu (Data manipulation language – DML) Đây là các lệnh phổ biến dùng để xử lý dữ liệu. Bao gồm: Select Insert Update Delete Ví dụ: Câu lệnh sau sẽ lọc ra các nhân viên có tên bắt đầu bằng chữ A trong bảng Nhanvien. select * from Nhanvien as nv where nv.tennv like 'A%' Dấu * hàm ý là lựa chọn tất cả các cột của bảng Nhanvien. Toán tử like và ký tự đại diện sẽ được nói trong phần sau. Câu lệnh sau sẽ thêm dữ liệu về một nhân viên mới vào trong bảng Nhanvien. 23
  18. insert into Nhanvien values(1, N'Nguyễn Văn An', N'22 Nguyễn Thiện Thuật') Câu lệnh sau sẽ cập nhật lai địa chỉ của nhân viên có manv là 1 update Nhanvien set diachi = N'22 Nguyễn Thị Minh Khai' where manv = 1 Câu lệnh sau sẽ xóa thông tin của nhân viên có manv là 1 trong bảng Nhanvien delete Nhanvien where manv = 1 2.3.4 Cú pháp của T-SQL Các đối tượng trong cơ sở dữ liệu dựa trên SQL (table, view, index, storedprocedure ) được xác định thông qua tên của đối tượng (hay còn gọi là identifier). Tên của các đối tượng là duy nhất trong mỗi cơ sở dữ liệu. Tên được sử dụng nhiều nhất trong các truy vấn SQL và được xem là nền tảng trong cơ sở dữ liệu quan hệ là tên bảng và tên cột. Có hai loại Identifiers một loại thông thường (Regular Identifier) và một loại gọi là Delimited Identifier, loại này cần có dấu "" hay dấu [] để ngăn cách. Loại Delimited được dùng đối với các chữ trùng với từ khóa của SQL Server (reserved keyword) hay các chữ có khoảng trống. Ví dụ: Select * From “My table” Where [sum] = 10 Trong các cơ sở dữ liệu lớn với nhiều người sử dụng, khi ta chỉ định tên của một bảng nào đó trong câu lệnh SQL, hệ quản trị cơ sở dữ liệu hiểu đó là tên của bảng do ta sở hữu (tức là bảng do ta tạo ra). Thông thường, trong các hệ quản trị cơ sở dữ liệu này cho phép những người dùng khác nhau tạo ra những bảng trùng tên với nhau mà không gây ra xung đột về tên. Nếu trong một câu lệnh SQL ta cần chỉ đến một bảng do một người dùng khác sở hữu (hiển nhiên là phải được phép) thì tên của bảng phải được viết sau tên của người sở hữu và phân cách với tên người sở hữu bởi dấu chấm: tên_người_sở_hữu.tên_bảng Một số đối tượng cơ sở dữ liệu khác (như khung nhìn, thủ tục, hàm), việc sử dụng tên cũng tương tự như đối với bảng. Ta có thể sử dụng tên cột một cách bình thường trong các câu lệnh SQL bằng cảch chỉ cần chỉ định tên của cột trong bảng. Tuy nhiên, nếu trong câu lệnh có liên quan đến hai cột trở 24
  19. lên có cùng tên trong các bảng khác nhau thì bắt buộc phải chỉ định thêm tên bảng trước tên cột; tên bảng và tên cột được phân cách nhau bởi dấu chấm Ví dụ: Giả sử chúng ta có CSDL như sau: Để tìm ra khách hàng có tên Nguyễn Văn An đã đặt hàng vào ngày nào, câu truy vấn như sau: Select orderid, orderdate from orders, customers where orders.customerid = customers.customerid and customername = N'Nguyễn Văn An' 2.3.5 Các kiểu dữ liệu Bảng dưới đây liệt kê một số kiểu dữ liệu thông dụng được sử dụng trong SQL. Char(n) Kiểu chuỗi với độ dài cố định Nchar(n) Kiếu chuỗi với độ dài cố định hỗ trợ UNICODE Varchar(n) Kiểu chuỗi với độ dài chính xác Nvarchar(n) Kiểu chuỗi với độ dài chính xác hỗ trợ UNICODE Int Số nguyên có giá trị từ -231 đến 231 - 1 Tinyint Số nguyên có giá trị từ 0 đến 255. Smallint Số nguyên có giá trị từ -215 đến 215 – 1 25
  20. Bigint Số nguyên có giá trị từ -263 đến 263-1 Numeric Kiểu số với độ chính xác cố định. Decimal Tương tự kiểu Numeric Float Số thực có giá trị từ -1.79E+308 đến 1.79E+308 Real Số thực có giá trị từ -3.40E + 38 đến 3.40E + 38 Money Kiểu tiền tệ Bit Kiểu bit (có giá trị 0 hoặc 1) Datetime Kiểu ngày giờ (chính xác đến phần trăm của giây) Smalldatetime Kiểu ngày giờ (chính xác đến phút) Binary Dữ liệu nhị phân với độ dài cố định (tối đa 8000 bytes) Varbinary Dữ liệu nhị phân với độ dài chính xác (tối đa 8000 bytes) Image Dữ liệu nhị phân với độ dài chính xác (tối đa 2,147,483,647 bytes) Text Dữ liệu kiếu chuỗi với độ dài lớn (tối đa 2,147,483,647 ký tự) Ntext Dữ liệu kiếu chuỗi với độ dài lớn và hỗ trợ UNICODE (tối đa 1,073,741,823 ký tự) Ví dụ: Mỗi cột trong bảng sẽ chứa những dữ liệu thuộc về duy nhất một kiểu dữ liệu trong SQL Server. Cột nào chứa những dữ liệu thuộc kiểu nào sẽ được quy định lúc định nghĩa bảng. Create table Nhanvien ( MANV NVARCHAR(10) NOT NULL, HOTENNVARCHAR(30) NOT NULL, GIOITINH BIT, NGAYSINH SMALLDATETIME, NOISINH NCHAR(50), HSLUONG DECIMAL(4,2), MADV INT ) 2.3.6 Biến (Variables) Biến trong T-SQL cũng có chức năng tương tự như trong các ngôn ngữ lập trình khác nghĩa là cần khai báo trước loại dữ liệu trước khi sử dụng. Biến được bắt đầu bằng dấu @ (Ðối với các biến toàn cục - global variable - thì có hai dấu @@) 26
  21. Ví dụ: Ví dụ dưới đây khai báo một biến có tên @numberOfCustomers thông qua từ khóa declare. Biến này lưu số khách hàng đếm được thông qua hàm count. Sau đó in ra giá trị của biến. declare @numberOfCustomers int select @numberOfCustomers = count(*) from Customers print @numberOfCustomers 2.3.7 Hàm (Function) Có 2 loại hàm: một loại là được xây dựng sẵn trong SQL Server 20005 Express Edition (built-in) và một loại do người dùng tự định nghĩa (user-defined) Các hàm Built-In được chia làm 3 nhóm: Rowset Functions : Loại này thường trả về một object và được đối xử như một table. Ví dụ như hàm OPENQUERY sẽ trả về một recordset và có thể đứng vị trí của một table trong câu lệnh Select. Aggregate Functions : Loại này làm việc trên một số giá trị và trả về một giá trị đơn hay là các giá trị tổng. Ví dụ như hàm AVG sẽ trả về giá trị trung bình của một cột. Scalar Functions : Loại này làm việc trên một giá trị đơn và trả về một giá trị đơn. Trong loại này lại chia làm nhiều loại nhỏ như các hàm về toán học, về thời gian, xử lý kiểu dữ liệu String Ví dụ như hàm MONTH('2002-09-30') sẽ trả về tháng 9. Các hàm User-Defined (được tạo ra bởi câu lệnh CREATE FUNCTION và phần body thường được gói trong cặp lệnh BEGIN END) cũng được chia làm các nhóm như sau: Scalar Functions : Loại này cũng trả về một giá trị đơn bằng câu lệnh RETURNS. Table Functions : Loại này trả về một table 2.3.8 Các toán tử (Operators) Trong SQL Server các biểu diễn (expression) có thể xuất hiện nhiều toán tử. Độ ưu tiên của toán tử sẽ quyết định thứ tự thực hiện của các phép tính. Thứ tự thực hiện ảnh hưởng rất lớn đến kết quả. Bảng dưới đây mô tả các toán tử trong SQL Server 2005 Express Edititon và mức độ ưu tiên của các toán tử đó. Level Operators 1 * (Multiply), / (Division), % (Modulo) 2 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), 27
  22. 3 =, >, =, , !=, !>, !< (Comparison operators) 4 NOT 5 AND 6 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME 7 = (Assignment) 2.3.9 Các thành phần điều khiển (Control of flow) Như BEGIN END, BREAK, CONTINUE, GOTO, IF ELSE, RETURN, WHILE 2.3.10 Chú thích (Comment) T-SQL dùng kí hiệu để chú thích cho một dòng đơn và kí hiệu /* */ để chú thích cho một nhóm dòng Ví dụ: /* Minh họa chú thích Chú thích cho một dòng đơn và một nhóm các dòng*/ DECLARE @MyNumber int khai báo biến SET @MyNumber = 4 - 2 + 27 kết quả là 29 SELECT @MyNumber 2.3.11 Giá trị NULL Một cơ sở dữ liệu là sự phản ánh của một hệ thống trong thế giới thực, do đó các giá trị dữ liệu tồn tại trong cơ sở dữ liệu có thể không xác định được. Một giá trị không xác định được xuất hiện trong cơ sở dữ liệu có thể do một số nguyên nhân sau: Giá trị đó có tồn tại nhưng không biết. Không xác định được giá trị đó có tồn tại hay không. Tại một thời điểm nào đó giá trị chưa có nhưng rồi có thể sẽ có. Giá trị bị lỗi do tính toán (tràn số, chia cho không, ) Những giá trị không xác định được biểu diễn trong cơ sở dữ liệu quan hệ bởi các giá trị NULL. Đây là giá trị đặc biệt và không nên nhầm lẫn với chuỗi rỗng (đối với dữ liệu kiểu chuỗi) hay giá trị không (đối với giá trị kiểu số). Giá trị NULL đóng một vai trò quan trọng trong các cơ sở dữ liệu và hầu hết các hệ quản trị cơ sở dữ liệu quan hệ hiện nay đều hỗ trợ việc sử dụng giá trị này. 28
  23. 3 Ngôn ngữ thao tác dữ liệu – DML SQL được xem như là công cụ hữu hiệu để thực hiện các yêu cầu truy vấn và thao tác trên dữ liệu. Trong chương này, ta sẽ bàn luận đến nhóm các câu lệnh trong SQL được sử dụng cho mục đích này. Nhóm các câu lệnh này được gọi chung là ngôn ngữ thao tác dữ liệu (DML: Data Manipulation Language) bao gồm các câu lệnh sau: SELECT: Sử dụng để truy xuất dữ liệu từ môt hoặc nhiều bảng. INSERT: Thêm dữ liệu. UPDATE: Cập nhật dữ liệu DELETE: Xoá dữ liệu Trong số các câu lệnh này, có thể nói SELECT là câu lệnh tương đối phức tạp và được sử dụng nhiều trong cơ sở dữ liệu. Với câu lệnh này, ta không chỉ thực hiện các yêu cầu truy xuất dữ liệu đơn thuần mà còn có thể thực hiện được các yêu cầu thống kê dữ liệu phức tạp. Cũng chính vì vậy, phần đầu của chương này sẽ tập trung tương đối nhiều đến câu lệnh SELECT. Các câu lệnh INSERT, UPDATE và DELETE được bàn luận đến ở cuối chương 3.1 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. Câu lệnh này có thể dùng để thực hiện phép chọn (tức là truy xuất một tập con các dòng trong một hay nhiều bảng), phép chiếu (tức là truy xuất một tập con các cột trong một hay nhiều bảng) và phép nối (tức là liên kết các dòng trong hai hay nhiều bảng để truy xuất dữ liệu). 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. Cú pháp chung của câu lệnh SELECT có dạng: SELECT [ALL | DISTINCT][TOP n] danh_sách_chọ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]] Điều cần lưu ý đầu tiên đối với câu lệnh này là các thành phần trong câu lệnh SELECT nếu được sử dụng phải tuân theo đúng thứ tự như trong cú pháp. Nếu không, câu lệnh sẽ được xem là không hợp lệ. 29
  24. 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). Ví dụ: Ví dụ dưới đây hiển thị tên khách hàng và địa chỉ các khách hàng hiện có. select customername, gender, address from customers 3.1.1 Danh sách chọn trong câu lệnh SELECT Danh sách chọ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 chọn trong câu lệnh SELECT bao gồm các trường hợp sau: 30
  25. Chọn tất cả các cột: Như đã nói trong chương 1, chúng ta dùng dấu * trong câu lệnh Select để hàm ý chọn hết 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 đã được tạo ra khi bảng được định nghĩa. Ví dụ: Select * from Customers Chọn một số cột cụ thể: 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 chọ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 chọn. Ví dụ: Select CUSTOMERNAME, ADDRESS From Customers Lưu ý: Nếu truy vấn được thực hiện trên nhiều bảng/khung nhìn và trong các bảng/khung nhìn có các trường trùng tên thì tên của những trường này nếu xuất hiện trong danh sách chọn phải được viết dưới dạng: tên_bảng.tên_trường Thay đổi tiêu đề các cột: Trong kết quả truy vấn, tiêu đề của các cột mặc định sẽ là tên của các trường tương ứng trong bảng. Tuy nhiên, để các tiêu đề trở nên thân thiện hơn, ta có thể đổi tên các tiêu đề của 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 31
  26. Ví dụ: select [Mã khách hàng] = Customerid, customername as [Tên khách hàng], address [Địa chỉ] from Customers Sử dụng cấu trúc CASE WHEN: Cấu trúc CASE được sử dụng trong danh sách chọn nhằm thay đổi kết quả của truy vấn tuỳ thuộc vào các trường hợp khác nhau. 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ả_của_else] END hoặc: CASE WHEN điều_kiện THEN kết_quả [ ] [ELSE kết_quả_của_else] END Ví dụ: Câu lệnh SQL dưới đây sẽ hiện thị giới tính của khách hàng tùy theo giá trị thực được lưu trong CSDL. Nếu giá trị trong CSDL là FALSE-> hiện thị giới tính NỮ, nếu giá trị là TRUE-> hiện thị giới tính NAM. select CUSTOMERNAME, ADDRESS, case GENDER when 1 then 'NAM' else N'NỮ' end as [GIỚI TÍNH] from customers Câu lệnh trên cũng có thể viết như sau: 32
  27. select CUSTOMERNAME, ADDRESS, case when GENDER = 1 then 'NAM' else N'NỮ' end as [GIỚI TÍNH] from customers Loại bỏ các dòng dữ liệu trùng nhau: Từ khóa DISTINCT sẽ loại bỏ các dòng dữ liệu giống nhau. Trong ví dụ trên, có hai khách hàng có tên Cao Van Trung. Nếu ta chỉ truy vấn tên khách hàng, để loại bỏ sự trùng lắp ta dùng từ khóa DISTINCT select distinct CUSTOMERNAME from customers Lựa chọn một số lượng giới hạn các dòng: Từ khóa TOP n sẽ trả về chỉ n dòng dữ liệu Ví dụ: ví dụ sau chỉ trả về duy nhất hai dòng dữ liệu select top 2 Customername from customers Nếu sử dung TOP n PERCENT thì sẽ trả về n % số dòng dữ liệu hiện có trong CSDL. 33
  28. 3.1.2 Mệnh đề FROM Mệnh đề FROM trong câu lệnh SELECT được sử dung 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í dụ: Câu lệnh sau hiển thị thông tin khách hàng Select * from Customers Trong mệnh đề FROM có thể sử dụng bí danh (alias) nhằm làm cho câu truy vấn dễ nhìn hơn. Ví dụ: Select * from Customers c Where c.CustomerID = 1 3.1.3 Mệnh đề WHERE - điều kiện truy vấn dữ liệu Mệnh đề WHERE trong câu lệnh SELECT đượ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ả mãn điều kiện được chỉ định mới được hiển thị trong kết quả truy vấn. Ví dụ: Lọc ra thông tin các khách hàng có mã Select * From Customers Where CustomerID > 3 Trong mệnh đề WHERE thường sử dụng: Các toán tử kết hợp điều kiện (AND, OR) Các toán tử so sánh Kiểm tra giới hạn của dữ liệu (BETWEEN/ NOT BETWEEN) 34
  29. Tập hợp Kiểm tra khuôn dạng dữ liệu. Các giá trị NULL 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 ! 20 Kiểm tra giới hạn củ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: Mệnh đề Ý nghĩa variable BETWEEN a AND b a b Ví dụ: ví dụ này tương tự ví dụ ở trên nhưng điều kiện là độ tuổi nằm trong khoảng từ 20 đến 30 tuổi. select CUSTOMERNAME, convert (varchar, BIRTHDAY, 103) as BIRTHDAY, ADDRESS from Customers where Customername = 'Le Thi Hoa' and year(getdate()) - year(BIRTHDAY) between 20 and 30 35
  30. Toán tử làm việc trên tập hợp (IN/ NOT IN) Từ khoá IN/ NOT 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/ 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. Ví dụ: Câu lệnh dưới đây lấy ra các thông tin của khách hàng có mã là 5,6 hoặc 7 select CUSTOMERID, CUSTOMERNAME, convert(varchar,BIRTHDAY, 103) as BIRTHDAY, ADDRESS from Customers where CUSTOMERID in (5,6,7) Ví dụ: Ví dụ này minh họa một câu lệnh SELECT khác đứng sau mệnh đề IN/ NOT IN select CUSTOMERID, CUSTOMERNAME, convert(varchar,BIRTHDAY, 103) as BIRTHDAY, ADDRESS from Customers where CUSTOMERID not in ( select CUSTOMERID from customers where customerid >= 7) Toán tử LIKE/ NOT LIKE và ký tự đại diện (WildCard) Từ khoá LIKE (NOT LIKE) sử dụng trong câu lệnh SELECT nhằm mô tả khuôn dạng của dữ liệu cần tìm kiếm. Chúng thường được kết hợp với các ký tự đại diện sau đây: Ký tự đại diện Ý nghĩa % Chuỗi ký tự bất kỳ gồm không hoặc nhiều ký tự _ Một ký tự bất kì [] Một ký tự nằm trong giới hạn được chỉ định. Ví dụ:[a-f] hàm ý chỉ một trong các ký tự: a, b, c, d, e, f. [^] Một ký tự không nằm trong giới hạn được chỉ định. Ví dụ:[^a-f] hàm ý chỉ một ký tự khác tất cả các ký tự: a, b, c, d, e, f. Ví dụ: Ví dụ dưới đây tìm ra các khách hàng có tên bắt đều bằng Nguyen 36
  31. select * from customers where customername like 'Nguyen%' Giá trị NULL Dữ liệu trong một cột cho phép NULL sẽ nhận giá trị NULL trong các trường hợp sau: Nếu không có dữ liệu được nhập cho cột và không có mặc định cho cột hay kiểu dữ liệu trên cột đó. Người sử dụng trực tiếp đưa giá trị NULL vào cho cột đó. Một cột có kiểu dữ liệu là kiểu số sẽ chứa giá trị NULL nếu giá trị được chỉ định gây tràn số. 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í dụ: select * from Customers where birthday is null Tạo mới bảng dữ liệu từ cau 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ụ: select CUSTOMERNAME, convert(varchar,BIRTHDAY, 103) as BIRTHDAY, ADDRESS into NEWCUSTOMERS from Customers Lưu ý: Nếu trong danh sách chọn có các biểu thức thì những biểu thức này phải được đặt tiêu đề 37
  32. 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. 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. Ví dụ: Ví dụ đưới đây sắp xếp thông tin các khách hàng theo thứ tự tuổi giảm dần. select CUSTOMERNAME, year(getdate())- year(BIRTHDAY) as AGE, ADDRESS from Customers order by AGE DESC 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: select CUSTOMERNAME, year(getdate())- year(BIRTHDAY) as AGE, ADDRESS from Customers order by 2 DESC 3.1.4 Phép hợp (UNION) Phép hợp đượ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_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]] 38
  33. Trong đó Câu_lệnh_1 có dạng SELECT danh_sách_cột [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] và Câu_lệnh_i (i = 2, ,n) có dạng SELECT danh_sách_cột [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] Ví dụ: Phép hợp giữa hai bảng dưới đây cho kết quả như sau: UNION select A,B from A union select F,G from B 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. 39
  34. UNION ALL Khi sử dụng toán tử UNION để thực hiện phép hợp, ta cần chú ý các nguyên tắc sau: 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. Nguyên nhân là do phép hợp so sánh các cột từng cột một theo thứ tự được cho trong mỗi truy vấn. 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. Mệnh đề ORDER BY và COMPUTE dùng để sắp xếp kết quả truy vấn hoặc tính toán các giá trị thống kê chỉ được sử dụng ở cuối câu lệnh UNION. Chúng không được sử dụng ở trong bất kỳ truy vấn thành phần nào. Mệnh đề GROUP BY và HAVING chỉ có thể được sử dụng trong bản thân từng truy vấn thành phần. Chúng không được phép sử dụng để tác động lên kết quả chung của phép hợp. Phép toán UNION có thể được sử dụng bên trong câu lệnh INSERT. Phép toán UNION không được sử dụng trong câu lệnh CREATE VIEW. 40
  35. 3.1.5 Phép nối Khi cần thực hiện một yêu cầu truy vấn dữ liệu từ hai hay nhiều bảng, ta phải sử dụng đến phép nối. Một câu lệnh nối kết hợp các dòng dữ liệu trong các bảng khác nhau lại theo một hoặc nhiều điều kiện nào đó và hiển thị chúng trong kết quả truy vấn. Ví dụ: Để tìm ra khách hàng có mã là 3 đã đặt hàng trong những ngày nào thì câu truy vấn như sau: select c.CUSTOMERNAME, o.ORDERDATE from customers c, orders o where c.customerid = o.customerid and c.customerid = 3 Giải thích câu truy vấn: Bảng Customers Bảng Order Trước tiên vào bảng Customers tìm ra dòng có có mã khách hàng là 3. Tìm kiếm trong bảng Orders các dòng có giá trị trường CUSTOMERID là 3 và cho các dòng này vào kết quả truy vấn. Như vậy để thực hiện yêu cầu truy vấn, chúng ta phải thực hiện phép kết nối giữa hai bảng Customers và Orders với điều kiện kết nối là CUSTOMERID của bảng CUSTOMERS bằng với CUSTOMERID của bảng ORDERS. Phép nối là cơ sở để thực hiện các yêu cầu truy vấn dữ liệu liên quan đến nhiều bảng. Một câu lệnh nối thực hiện lấy các dòng dữ liệu trong các bảng tham gia truy vấn, so sánh giá trị của các dòng này trên một hoặc nhiều cột được chỉ định trong điều kiện nối và kết hợp các dòng thoả mãn điều kiện thành những dòng trong kết quả truy vấn. Để 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: 41
  36. 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à gì Trong các yếu tố kể trên, việc xác định chính xác điều kiện để thực hiện phép nối giữa các bảng đóng vai trò quan trọng nhất. Trong đa số các trường hợp, điều kiện của phép nối được xác định nhờ vào mối quan hệ giữa các bảng cần phải truy xuất dữ liệu. Thông thường, đó là điều kiện bằng nhau giữa khoá chính và khoá ngoài của hai bảng có mối quan hệ với nhau. Như vậy, để có thể đưa ra một câu lệnh nối thực hiện chính xác yêu cầu truy vấn dữ liệu đòi hỏi phải hiểu được mối quan hệ cũng như ý nghĩa của chúng giữa các bảng dữ liệu. Một câu lệnh nối cũng được bắt đầu với từ khóa SELECT. Các cột được chỉ định tên sau từ khoá SELECT là các cột được hiển thị trong kết quả truy vấn. Việc sử dụng tên các cột trong danh sách chọn có thể là: Tên của một số cột nào đó trong các bảng có tham gia vào truy vấn. Nếu tên cột trong các bảng trùng tên nhau thì tên cột phải được viết dưới dạng tên_bảng.tên_cột Dấu sao (*) được sử dụng trong danh sách chọn khi cần hiển thị tất cả các cột của các bảng tham gia truy vấn. Trong trường hợp cần hiển thị tất cả các cột của một bảng nào đó, ta sử dụng cách viết: tên_bảng.* Mệnh đề FROM trong phép nối Sau mệnh đề FROM của câu lệnh nối là danh sách tên các bảng (hay khung nhìn) tham gia vào truy vấn. Nếu ta sử dụng dấu * trong danh sách chọn thì thứ tự của các bảng liệt kê sau FROM sẽ ảnh hưởng đến thứ tự các cột được hiển thị trong kết quả truy vấn. Mệnh đề WHERE trong phép nối Khi hai hay nhiều bảng được nối với nhau, ta phải chỉ định điều kiện để thực hiện phép nối ngay sau mệnh đề WHERE. Điều kiện nối được biểu diễn dưới dạng biểu thức logic so sánh giá trị dữ liệu giữa các cột của các bảng tham gia truy vấn. Các toán tử so sánh dưới đây được sử dụng để xác định điều kiện nối Phép toán Ý nghĩa = Bằng > Lớn hơn >= Lớn hơn hoặc bằng < Nhỏ hơn 42
  37. Khác !> Không lớn hơn !< Không nhỏ hơn 3.1.6 Các loại phép nối Phép nối bằng: Một phép nối bằng (equi-join) là một phép nối trong đó giá trị của các cột được sử dụng để nối được so sánh với nhau dựa trên tiêu chuẩn bằng và tất cả các cột trong các bảng tham gia nối đều được đưa ra trong kết quả. Một dạng đặc biệt của phép nối bằng được sử dụng nhiều là phép nối tự nhiên (natural- join). Trong phép nối tự nhiên, điều kiện nối giữa hai bảng chính là điều kiện bằng giữa khoá ngoài và khoá chính của hai bảng; Và trong danh sách chọn của câu lệnh chỉ giữ lại một cột trong hai cột tham gia vào điều kiện của phép nối. Ví dụ phép kết nối bằng: select * from Customers c, Orders o where c.customerid = o.customerid Ví dụ phép kết nối tự nhiên: select c.CUSTOMERID, c.CUSTOMERNAME, c.BIRTHDAY, c.GENDER, c.ADDRESS, o.ORDERDATE from Customers c, Orders o where c.customerid = o.customerid hoặc viết gọn: select c.*, o.ORDERDATE from Customers c, Orders o where c.customerid = o.customerid Trong phép kết nối bằng, trường CUSTOMERID xuất hiện hai lần. Sự dư thừa được loại bỏ bằng cách sử dụng phép kết nối tự nhiên và việc chỉ định rõ các cột cột cần truy xuất. 43
  38. Trong các câu lệnh nối, ngoài điều kiện của phép nối được chỉ định trong mệnh đề WHERE còn có thể chỉ định các điều kiện tìm kiếm dữ liệu khác (điều kiện chọn). Thông thường, các điều kiện này được kết hợp với điều kiện nối thông qua toán tử AND. Ví dụ: select c.*, o.ORDERDATE from Customers c, Orders o where c.customerid = o.customerid and c.customerid = 3 Phép tự nối Phép tự nối là phép nối mà trong đó điều kiện nối được chỉ định liên quan đến các cột của cùng một bảng. Trong trường hợp này, sẽ có sự xuất hiện tên của cùng một bảng nhiều lần trong mệnh đề FROM và do đó các bảng cần phải được đặt bí danh. Ví dụ: Giả sử có yêu cầu tìm ra các khách hàng có nhiều hơn một đơn đặt hàng trong cùng ngày select c1.CUSTOMERID, c1.CUSTOMERNAME from customers c1, customers c2, orders o1, orders o2 where c1.customerid = o1.customerid and c2.customerid = o2.customerid and c1.customerid = c2.customerid and o1.orderdate = o2.orderdate and o1.orderid <> o2.orderid Câu truy vấn được giải thích như sau: Lần lượt lấy ra các mã khách hàng, mã hóa đơn và ngày đặt hàng từ bảng c1, o1 đem so sánh lần lượt với các mã khách hàng, mã hóa đơn và ngày đặt hàng từ bảng c2, o2. Nếu việc so sánh hai tập hợp này thỏa điều kiện sau đây: mã khách hàng trùng nhau, ngày đặt hàng trùng nhau và có mã hóa đơn khác nhau thì thông tin khách hàng này được cho vào kết qua truy vấn. Phép nối ngoài Trong các phép nối đã đề cập ở trên, chỉ những dòng có giá trị trong các cột được chỉ định thoả mãn điều kiện kết nối mới được hiển thị trong kết quả truy vấn, và được gọi là phép nối trong (inner join) Theo một nghĩa nào đó, những phép nối này loại bỏ thông tin chứa trong những dòng không thoả mãn điều kiện nối. Tuy nhiên, đôi khi ta cũng cần giữ lại những thông tin này bằng cách cho phép những dòng không thoả mãn điều kiện nối có mặt trong kết quả của phép nối. Để làm điều này, ta có thể sử dụng phép nối ngoài. SQL cung cấp các loại phép nối ngoài sau đây: 44
  39. Phép nối ngoài trái (ký hiệu: *=): Phép nối này hiển thị trong kết quả truy vấn tất cả các dòng dữ liệu của bảng nằm bên trái trong điều kiện nối cho dù những dòng này không thoả mãn điều kiện của phép nối Phép nối ngoài phải (ký hiệu: =*): Phép nối này hiển thị trong kết quả truy vấn tất cả các dòng dữ liệu của bảng nằm bên phải trong điều kiện nối cho dù những dòng này không thoả điều kiện của phép nối. Tuy nhiên trong SQL Server 2005 Express Edition không hỗ trợ trực tiếp các phép nối *= và =*. Mặt khác trong các phiên bản SQL Server sắp tới các phép nối này sẽ hoàn toàn không được hỗ trợ. Do đó Microsoft khuyến cáo người sử dụng dùng các phép nối LEFT JOIN, RIGHT JOIN. Các phép nối này sẽ được nói rõ trong phần dưới đây. 3.1.7 Phép nối theo chuẩn SQL-92 Chuẩn SQL2 (SQL-92) đưa ra một cách khác để biểu diễn cho phép nối, trong cách biểu diễn này, điều kiện của phép nối không được chỉ định trong mệnh đề WHERE mà được chỉ định ngay trong mệnh đề FROM của câu lệnh. Cách sử dụng phép nối này cho phép ta biểu diễn phép nối cũng như điều kiện nối được rõ ràng, đặc biệt là trong trường hợp phép nối được thực hiện trên ba bảng trở lên. Phép nối trong Đ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 Ví dụ: Phép nối ngoài SQL2 cung cấp các phép nối ngoà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) 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 Ví dụ: Để tìm ra các khách hàng có đặt hàng thay vì sử dụng câu truy vấn sau: select * customers c, orders o where c.customerid = o.orderid 45
  40. Ta có thể sử dụng câu truy vấn sau: select * from customers c inner join orders o on c.customerid = o.customerid Nếu phép nối ngoài trái hiển thị trong kết quả truy vấn cả những dòng dữ liệu không thoả điều kiện nối của bảng bên trái trong phép nối thì phép nối ngoài đầy đủ hiển thị trong kết quả truy vấn cả những dòng dữ liệu không thoả điều kiện nối của cả hai bảng tham gia vào phép nối. Ví dụ: Giả sử có CSDL như sau: Thực hiện phép nối ngoài trái, nối ngoài phải và nối ngoài đầy đủ cho kết quả như sau: Phép nối ngoài trái: select * from faculty f left join class c on f.facultyid = c.facultyid Phép nối ngoài phải: select * from faculty f right join class c on f.facultyid = c.facultyid Phép nối ngoài đầy đủ: select * from faculty f full join class c 46
  41. on f.facultyid = c.facultyid Một đặc điểm nổi bật của SQL2 là cho phép biểu diễn phép nối trên nhiều bảng dữ liệu một cách rõ ràng. Thứ tự thực hiện phép nối giữa các bảng được xác định theo nghĩa 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ụ: Liệt kê tên các mặt hàng có trong đơn đạt hàng có mã là 1. select i.ITEMNAME, o.ORDERDATE from (orders o inner join orderdetail od on o.orderid = od.orderid) inner join items i on od.itemid = i.itemid where o.orderid = 1 3.1.8 Mệnh đề GROUP BY Ngoài khả năng thực hiện các yêu cầu truy vấn dữ liệu thông thường (chiếu, chọn, nối, ) như đã đề cập như ở các phần trước, câu lệnh SELECT còn cho phép thực hiện các thao tác truy vấn và tính toán thống kê trên dữ liệu. Mệnh đề GROUP BY sử dụng trong câu lệnh SELECT nhằm phân hoạch 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 (aggregate functions) đượ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 SQL cung cấp các hàm gộp dưới đây: Hàm gộp Chức năng SUM([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. 47
  42. COUNT(*) Đếm số các dòng được chọ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 Hàm SUM và AVG chỉ làm việc với các biểu thức số. Hàm SUM, AVG, COUNT, MIN và MAX bỏ qua các giá trị NULL khi tính toán. Hàm COUNT(*) không bỏ qua các giá trị NULL. Mặc định, các hàm gộp thực hiện tính toán thống kê trên toàn bộ dữ liệu. Trong trường hợp cần loại bỏ bớt các giá trị trùng nhau (chỉ giữ lại một giá trị), ta chỉ định thêm từ khoá DISTINCT ở trước biểu thức là đối số của hàm. 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, ta 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. Ví dụ: Tính tuổi trung bình, tuổi nhỏ nhất và lớn nhất của các khách hàng select min(year(getdate())-year(BIRTHDAY)) as MINAGE, max(year(getdate())-year(BIRTHDAY)) as MAXAGE, avg(year(getdate())-year(BIRTHDAY)) as AVGAGE from customers Thống kê trên nhóm Trong trường hợp cần thực hiện tính toán các giá trị thống kê trên các nhóm dữ liệu, ta 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. Ví dụ: Câu truy vấn sau cho biết số tổng số tiển khách hàng phải trả cho tất cả các lần đặt hàng select c.CUSTOMERID, c.CUSTOMERNAME, convert(varchar(20),cast(SUM(i.UNITPRICE*od.QUANTITY) as money),1) as SUMTOTAL from customers c inner join orders o on o.customerid = c.customerid inner join orderdetail od on o.orderid = od.orderid inner join items i on i.itemid = od.itemid group by c.customerid, c.customername 48
  43. Nếu muốn hiện số tiền khách hàng phải trả cho từng đơn đặt hàng, chỉ cần thêm trường ORDERID vào mệnh đề group by. select c.CUSTOMERID, c.CUSTOMERNAME, convert(varchar(20),cast(SUM(i.UNITPRICE*od.QUANTITY)as money),1) as SUMTOTAL from customers c inner join orders o on o.customerid = c.customerid inner join orderdetail od on o.orderid = od.orderid inner join items i on i.itemid = od.itemid group by c.customerid, c.customername, o.orderid 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ì những biểu thức này 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ệ. Mệnh đề HAVING chỉ định điều kiện trong hàm gộp Mệnh đề HAVING được sử dụng nhằm chỉ định điều kiện đối với các giá trị thống kê được sản sinh từ các hàm gộp tương tự như cách thức mệnh đề WHERE thiết lập các điều kiện cho câu lệnh SELECT. Mệnh đề HAVING thường không thực sự có nghĩa nếu như không sử dụng kết hợp với mệnh đề GROUP BY. Một điểm khác biệt giữa HAVING và WHERE là trong điều kiện của WHERE không được có các hàm gộp trong khi HAVING lại cho phép sử dụng các hàm gộp trong điều kiện của mình. Ví dụ: Tìm ra các khách hàng có tổng số tiền phải thanh toán cho tất cả các lần đặt hàng lớn hơn 100 triệu. select c.CUSTOMERID, c.CUSTOMERNAME, convert(varchar(20),cast(SUM(i.UNITPRICE*od.QUANTITY)as money),1) as SUMTOTAL from customers c inner join orders o on o.customerid = c.customerid inner join orderdetail od on o.orderid = od.orderid inner join items i on i.itemid = od.itemid group by c.customerid, c.customername having sum(i.UNITPRICE*od.QUANTITY) > 100000000 49
  44. 3.1.9 Truy vấn con (Subquery) 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. Cú pháp của truy vấn con như sau: (SELECT [ALL | DISTINCT] danh_sách_chọn FROM danh_sách_bảng [WHERE điều_kiện] [GROUP BY danh_sách_cột] [HAVING điều_kiện]) Khi sử dụng truy vấn con cần lưu ý một số quy tắc sau: 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 (tức là chỉ có duy nhất một cột trong danh sách chọn). 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. 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) Phép so sánh đối vớ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 đó phép toán số học có thể sử dụng bao gồm: =, , =, <=; Và truy vấn con phải có kết quả bao gồm đúng một cột. Ví dụ: Câu truy vấn sau đây tìm tên khách hàng có tuổi lớn nhất select c.CUSTOMERNAME, c.ADDRESS from customers c where year(getdate()) - year(BIRTHDAY) = 50
  45. (select max(year(getdate()) - year(BIRTHDAY)) from customers) Nếu truy vấn con trả về nhiều hơn một giá trị, việc sử dụng phép so sánh như trên sẽ không hợp lệ. Trong trường hợp này, sau phép toán so sánh phải sử dụng thêm lượng từ ALL hoặc ANY. Lượng từ ALL được sử dụng khi cần so sánh giá trị của biểu thức với tất cả các giá trị trả về trong kết quả của truy vấn con; ngược lai, phép so sánh với lượng từ ANY có kết quả đúng khi chỉ cần một giá trị bất kỳ nào đó trong kết quả của truy vấn con thoả mãn điều kiện Ví dụ: Toán tử IN/NOT IN 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, ta có thể sử dụng toán tử IN (NOT IN) như sau: WHERE biểu_thức [NOT] IN (truy_vấn_con) Ví dụ: Truy vấn con với EXISTS Lượng từ 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) Lượng từ EXISTS (tương ứng NOT EXISTS) trả về giá trị True (tương ứng False) nếu kết quả của truy vấn con có ít nhất một dòng (tương ứng không có dòng nào). Điều khác biệt của việc sử dụng EXISTS với hai cách đã nêu ở trên là trong danh sách chọn của truy vấn con có thể có nhiều hơn hai cột. Ví dụ: Truy vấn con và mệnh đề HAVING Một truy vấn con có thể được sử dụng trong mệnh đề HAVING của một truy vấn khác. Trong trường hơp này, kết quả của truy vấn con được sử dụng để tạo nên điều kiện đối với các hàm gộp. Ví dụ: 3.2 Thêm, cập nhật và xóa dữ liệu Các câu lệnh thao tác dữ liệu trong SQL không những chỉ sử dụng để truy vấn dữ liệu mà còn để thay đổi và cập nhật dữ liệu trong cơ sở dữ liệu. So với câu lệnh SELECT, việc sử dụng các câu lệnh để bổ sung, cập nhật hay xoá dữ liệu đơn giản hơn nhiều. Trong phần còn lại của chương này sẽ đề cập đến 3 câu lệnh: 51
  46. Lệnh INSERT Lệnh UPDATE Lệnh DELETE 3.2.1 Thêm dữ liệu Dữ liệu trong các bảng được thể hiện dưới dạng các dòng (bản ghi). Để bổ sung thêm các dòng dữ liệu vào một bảng, ta sử dụng câu lệnh INSERT. Hầu hết các hệ quản trị CSDL dựa trên SQL cung cấp các cách dưới đây để thực hiện thao tác thêm dữ liệu cho bảng: Thêm từng dòng dữ liệu với mỗi câu lệnh INSERT. Đây là các sử dụng thường gặp nhất trong giao tác SQL. Thêm 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. Thêm từng dòng dữ liệu Để bổ sung một dòng dữ liệu mới vào bảng, ta 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ị) Trong câu lệnh INSERT, 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ị. Trong trường hợp này, 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 bổ sung 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 Ví dụ: Thêm thông tin một khách hàng mới vào bảng Customer insert into customers (customername, birthday, gender, address) values('Nguyen Van An', '4/2/1976', 'True', '14 Thong Nhat') hoặc insert into customers values('Nguyen Van An', '4/2/1976', 'True', '14 Thong Nhat') Lưu ý: Trường CUSTOMERID được thiết lập identity là “YES” nên ta không cần thêm giá trị trường này mà SQL sẽ tự động tạo ra một giá trị cho trường này. Chi tiết về identity sẽ nói trong chương 4. Trong trường hợp chỉ nhập giá trị cho một số cột trong bảng, ta 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). Nếu một cột không có giá trị mặc định và không chấp nhận giá trị NULL mà không đuợc nhập dữ liệu, câu lệnh sẽ bị lỗi. Thêm một tập các dòng dữ liệu vào bảng 52
  47. Một cách sử dụng khác của câu lệnh INSERT được sử dụng để bổ sung 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 bổ sung 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 Ví dụ: insert into Customers_Backup select * from Customers 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. 3.2.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] Sau UPDATE là tên của bảng cần cập nhật dữ liệu. Một câu lệnh UPDATE có thể cập nhật dữ liệu cho nhiều cột bằng cách chỉ định các danh sách tên cột và biểu thức tương ứng sau từ khoá SET. Mệnh đề WHERE trong câu lệnh UPDATE được sử dụng để chỉ định các dòng dữ liệu chịu tác động của câu lệnh (nếu không chỉ định, phạm vi tác động của câu lệnh được hiểu là toàn bộ các dòng trong bảng) Ví dụ: update customers set customername = 'Cao Van Chung' where customerid = 9 Trong câu lệnh UPDATE có thể sử dụng CASE WHEN. Ví dụ: select * into tmp1 from customers 53
  48. update tmp1 set address = case when customerid < 2 then 'Nguyen Trung Truc' else 'Nguyen Thi Minh Khai' end 3.2.3 Xóa dữ liệu Để xoá dữ liệu trong một bảng, ta 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] Trong câu lệnh này, tên của bảng cần xoá dữ liệu được chỉ định sau DELETE FROM. Mệnh đề WHERE trong câu lệnh được sử dụng để chỉ định điều kiện đối với các dòng dữ liệu cần xoá. Nếu câu lệnh DELETE không có mệnh đề WHERE thì toàn bộ các dòng dữ liệu trong bảng đều bị xoá. Ví dụ: delete from Items where itemid = 3 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, ta 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 ta chỉ định thêm điều kiện nối giữa các bảng Ví dụ: delete from orderdetail from items where items.itemid = orderdetail.itemid and items.itemname = 'LAPTOP' 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ụ: delete from orderdetail from items 54
  49. where items.itemid = (select i.itemid from items i inner join orderdetail od on i.itemid = od.itemid WHERE itemname = 'LAPTOP') 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 vì sử dụng câu lệnh DELETE trong trường hợp này, ta có thể sử dụng câu lệnh TRUNCATE có cú pháp như sau: TRUNCATE TABLE tên_bảng Ví dụ: truncate table tmp1 55