SQL

Giới thiệu

SQL (Structured Query Language – Ngôn ngữ truy vấn có cấu trúc) là 1 ngôn ngữ lập trình cho 1 mục đích đặc biệt là để quản lý dữ liệu trong các hệ thống quản lý cơ sở dữ liệu quan hệ (RDBMSRelational Database Management System), hoặc cho việc xử lý dòng dữ liệu trong các hệ quản trị dòng dữ liệu quan hệ (RDSMSRelational Data Stream Management System).

Ban đầu dựa trên đại số quan hệ và các phép toán logic quan hệ trên bộ (tuple relational calculus), SQL chứa 1 ngôn ngữ định nghĩa dữ liệu và 1 ngôn ngữ thao tác dữ liệu. Phạm vi của SQL bao gồm thêm, truy vấn, cập nhật và xóa dữ liệu, tạo và thao tác cơ sở dữ liệu, điều khiển truy cập dữ liệu.

SQL là 1 trong những ngôn ngữ thương mại đầu tiên cho mô hình quan hệ của Edgar F. Codd, như mô tả trong 1 bài viết của ông vào năm 1970 “A Relational Model of Data for Large Shared Data Banks”. Mặc dù không hoàn toàn tôn trọng mô hình quan hệ được mô tả bởi Codd, nó trở thành ngôn ngữ cơ sở dữ liệu được dùng rộng rãi nhất.

SQL đã trở thành 1 tiêu chuẩn của Viện Tiêu Chuẩn Quốc Gia Hoa Kỳ (American National Standards InstituteANSI) trong năm 1986, và của Tổ chức tiêu chuẩn quốc tế (International Organization for StandardizationISO) năm 1987. Từ đó, tiêu chuẩn này đã được xem lại để bổ sung 1 tập các tính năng lớn hơn. Mặc dù sự tồn tại của các tiêu chuẩn như vậy, phần lớn mã SQL không hoàn toàn di động giữa các hệ thống cơ sở dữ liệu khác nhau mà không phải điều chỉnh.

sql_info

Lịch sử

SQL ban đầu được phát triển tại IBM bởi Donald D. ChamberlinRaymond F. Boyce vào những năm đầu thập niên 1970. Phiên bản này, ban đầu được gọi là SEQUEL (Structured English Query Language), được thiết kế để thao tác và lấy dữ liệu lưu trữ trong hệ thống quản lý dữ liệu bán quan hệ ban đầu của IBM, System R, 1 nhóm tại Phòng thí nghiệm San Jose của IBM đã phát triển trong suốt thập niên 1970. Từ viết tắt SEQUEL sau đó được đổi thành SQL bởi vì “SEQUEL” là 1 tên thương hiệu của công ty hàng không Hawker Siddeley của Anh.

Những năm cuối thập niên 1970, Relational Software Inc. (nay là Oracle Corporation) đã thấy được khả năng của các khái niệm của Codd, Chamberlin, và Boyce, và đã phát triển hệ quản trị cơ sở dữ liệu dựa trên SQL của riêng họ với nguyện vọng bán nó cho Hải quân Mỹ (U.S. Navy), Cơ quan tình báo trung ương (CIA) và các cơ quan chính phủ khác của Hoa Kỳ. Vào tháng 6/1979, Relational Software Inc. giới thiệu Oracle V2, bản thực thi thương mại đầu tiêu của SQL,  cho các máy tính VAX.

Sau khi kiểm tra SQL tại các trang kiểm tra khách hàng để xác định sự hữu dụng và thực tiễn của hệ thống, IBM bắt đầu phát triển các sản phẩm thương mại dựa trên nguyên mẫu System R của họ bao gồm System/38, SQL/DS, và DB2 và phát hành chúng chính thức vào các năm 1979, 1981 và 1983.

Cú pháp

Các thành phần của ngôn ngữ SQL

Ngôn ngữ SQL được chia nhỏ thành các thành phần gồm:

  • Clause – mệnh đề: là thành phần cấu thành các khai báo và truy vấn. (Trong 1 số trường hợp, các mệnh đề là tùy chọn).
  • Expression – biểu thức: có thể cho ra hoặc là các giá trị vô hướng, hoặc là các bảng chứa các dòng và các cột dữ liệu.
  • Predicate –  Logic vị từ: chỉ ra điều kiện có thể được đánh giá thành 1 trong 3 giá trị logic của SQL (true/ false/ unknown) hoặc các giá trị Boolean thật sự và được dùng để giới hạn ảnh hưởng của các khai báo vào truy vấn, hoặc để đổi luồng chương trình.
  • Query – Truy vấn: Lấy dữ liệu dựa trên những tiêu chí cụ thể. Đây là 1 thành phần quan trọng của SQL.
  • Statement – Khai báo: Có thể có 1 ảnh hưởng lâu dài trên lược đồ dữ liệu và dữ liệu, hoặc có thể điều khiển các giao dịch, luồng chương trình, các kết nối, các phiên làm việc hoặc các chuẩn đoán. Các khai báo trong SQL cũng bao gồm 1 dấu chấm phẩy (;) là ký tự kết thúc khai báo. Dù nó không được yêu cầu trên mọi nền tảng, nó được định nghĩa như 1 phần tiêu chuẩn trong cú pháp của SQL.
  • Các khoảng trắng – thường được bỏ qua trong các khai báo và truy cấn SQL, giúp cho mã SQL trở nên dễ đọc hơn.

Các toán tử

Toán tử Mô tả Ví dụ
= So sánh bằng Author = ‘Alcott’
<> Không bằng, khác (nhiều hệ quản trị CSDL chấp nhận != bên cạnh <>) Dept <> ‘Sales’
> Lớn hơn Hire_Date > ‘2012-01-31’
< Nhỏ hơn Bonus < 50000.00
>= Lớn hơn hoặc bằng Dependents >= 2
<= Nhỏ hơn hoặc bằng Rate <= 0.05
BETWEEN Giá trị nằm giữa vùng được khai báo Cost BETWEEN 100.00 AND 500.00
LIKE Đối chiếu 1 mẫu ký tự First_Name LIKE ‘Will%’
IN Bằng 1 trong nhiều giá trị có thể DeptCode IN (101, 103, 209)
IS hoặc IS NOT So sánh với null (thiếu dữ liệu) Address IS NOT NULL
IS NOT DISTINCT FROM Bằng với giá trị hoặc cả 2 là null Debt IS NOT DISTINCT FROM Receivables
AS Dùng để đổi tên trường khi xem kết quả SELECT employee AS ‘department1’

Các truy vấn

Tác vụ phổ biến nhất trong SQL là truy vấn, được thực hiện với khai báo SELECT. SELECT lấy dữ liệu từ 1 hoặc nhiều bảng, hoặc các biểu thức. Khai báo SELECT tiêu chuẩn không gây ra ảnh hưởng lâu dài trên cơ sở dữ liệu (CSDL). 1 vài sự thực thi không tiêu chuẩn của SELECT có thể có ảnh hưởng lâu dài, như là cú pháp SELECT INTO tồn tại trong 1 vài CSDL.

Các truy vấn cho phép người dùng mô tả dữ liệu mà họ muốn, để các DBMS chịu trách nhiệm cho việc lên kế hoạch, tối ưu, và thực hiện các tác vụ vật lý cần thiết để cho ra kết quả nó chọn.

1 truy vấn bao gồm 1 danh sách các cột trong kết quả cuối cùng, theo sau ngay từ khóa SELECT. 1 dấu hình sao (*) có thể được dùng để chỉ ra rằng truy vấn đó sẽ trả về tất cả các cột của bảng được truy vấn. SELECT là khai báo phức tạp nhất trong SQL, với các từ khóa và các mệnh đề tùy chọn bao gồm:

  • Mệnh đề FROM, xác định bảng để lấy dữ liệu. Mệnh đề FROM có thể bao gồm mệnh đề con JOIN để chỉ ra các luật cho việc liên kết các bảng.
  • Mệnh đề WHERE bao gồm 1 vị từ so sánh, giới hạn số dòng trả về bởi truy vấn. Mệnh đề WHERE loại bỏ tất cả các dòng từ tập kết quả nơi mà vị từ so sánh không trả về kết quả True.
  • Mệnh đề GROUP BY được dùng để nhóm các dòng có giá trị chung vào 1 tập các dòng nhỏ hơn. GROUP BY thường được dùng kết hợp với các hàm tổng hợp SQL hoặc để loại bỏ các dòng trùng lặp từ 1 tập kết quả. Mệnh đề WHERE được áp dụng trước mệnh đề GROUP BY.
  • Mệnh đề HAVING bao gồm 1 vị từ được dùng để lọc các dòng là kết quả từ mệnh đề GROUP BY. Bởi vì nó hoạt động trên các kết quả của mệnh đề GROUP BY, các hàm tống hợp có thể được dùng trong vị từ của mệnh đề HAVING.
  • Mệnh đề ORDER BY nhận diện những cột nào sẽ dùng để sắp xếp dữ liệu kết quả, và cách sắp xếp (tằng dần hoặc giảm dần). Thiếu mệnh đề ORDER BY, thứ tự các dòng được trả về bởi 1 truy vấn SQL không được định nghĩa.
Truy vấn con

Các truy vấn có thể được lồng vào nhau để kết quả của 1 truy vấn này có thể được dùng trong truy vấn khác qua 1 toán tử quan hệ hoặc hàm tổng hợp. 1 truy vấn được lồng vào cũng được biết như 1 truy vấn con. Trong khi hợp và các toán tử trên bảng khác cung cấp các tính toán cấp trên (tức là nhanh hơn) thay thế trong nhiều trường hợp, việc sử dụng các truy vấn con giới thiệu 1 hệ thống phân cấp trong việc thực thi mà có thể hữu dụng hay cần thiết.

1 truy vấn con có thể sử dụng các giá trị từ truy vấn bên ngoài, trong trường hợp nó được xem như 1 truy vấn con tương quan.

Từ năm 1999 tiêu chuẩn SQL cho phép các truy vấn con có tên được gọi là biểu thức bảng chung (Common Table Expression – CTE). Các CTE cũng có thể đệ quy bằng việc tham chiếu đến chính nó. Cơ chế trả kết quả cho phép việc duyệt cây hoặc đồ thị (khi được diễn tả như các quan hệ) và các tính toán dấu chấm tĩnh tổng quát hơn.

Null và Logic 3 giá trị (3 Valued logic – 3VL)

Khái niệm về Null được giới thiệu vào SQL để xử lý việc thiếu thông tin trong mô hình quan hệ. Từ NULL là từ khóa dành riêng trong SQL, được dùng để xác định điểm đánh dấu đặc biệt Null. Các so sánh với Null, ví dụ như so sánh bằng (=) trong mệnh đề WHERE, cho kết quả là 1 giá trị Unknown. Trong khai báo SELECT, SQL chỉ trả về các kết quả cho mệnh đề WHERE trả về giá trị True; tức là nó không bao gồm những kết quả với các giá trị False và Unknown.

Cùng với True và False, Unknown cho kết quả từ những so sánh trực tiếp với Null do đó mang lại 1 mảnh của logic 3 giá trị (3VL) đến SQL. Các bảng thật SQL sử dụng cho AND, OR, và NOT tương ứng với 1 đoạn phổ biến của logic 3 giá trị của Kleene và Lukasiewicz (khác nhau trong định nghĩa của họ về ý nghĩa, tuy nhiên SQL định nghĩa không có những tác vụ như vậy).

 

SQL 3 VLTuy nhiên có vài tranh luận về việc giải thích ngữ nghĩa của NULL trong SQL bởi vì cách xử lý của nó bên ngoài các so sánh trực tiếp. Như chúng ta thấy trong bảng trên, các so sánh bằng trực tiếp giữa 2 giá trị NULL (ví dụ NULL = NULL) trả về 1 giá trị Unknown thực sự. Điều này là phù hợp với việc giải thích rằng NULL không có 1 giá trị (hoặc không là thành viên của bất kỳ miền dữ liệu nào) nhưng đúng ra là 1 giá trị giữ chỗ hoặc “đánh dấu” cho thông tin thiếu. Tuy nhiên, nguyên tắc mà 2 giá trị NULL không bằng nhau bị vi phạm trong đặc tả SQL cho toán tử UNION và INTERSECT, là những toán tử có xác định NULL với nhau. Do đó, các phép toán tập hợp này trong SQL có thể cho ra kết quả không biểu diễn các thông tin chắc chắn, không giống các phép toán so sánh tường minh với NULL ( ví dụ như những phép toán trong mệnh đề WHERE được đề cập ở trên). Trong đề nghị năm 1979 của Codd ( cơ bản đã được thông qua bởi SQL92) việc không nhất quán ngữ nghĩa này được hợp lý hóa bằng các cho rằng việc loại bỏ các trùng lặp trong các phép toán tập hợp xảy ra “ở một mức độ chi tiếp thấp hơn việc kiểm tra bằng nhau trong việc đánh giá các phép toán lấy dữ liệu”. Tuy nhiên, giáo sư khoa học máy tính Ron van der Meyden kết luận rằng “Những mâu thuẫn trong tiêu chuẩn SQL nghĩa là nó không thể gán các ngữ nghĩa luận lý trực quan bất kỳ nào để xử lý NULL trong SQL”.

Ngoài ra, từ khi các toán tử SQL trả về giá trị Unknown khi so sánh bất kỳ cái gì với NULL 1 cách trực tiếp, SQL cung cấp 2 vị từ so sánh dành cho NULL: IS NULLIS NOT NULL để kiểm tra dữ liệu có phải là NULL hay không.

Việc định lượng phổ quát không được hỗ trợ tường minh bởi SQL, và phải được làm như 1 định lượng tồn tại phủ định. Cũng có 1 toán tử so sánh không có định “<row value expression> IS DISTINCT FROM <row value expression>”, trả về TRUE trừ khi cả 2 toán hạng bằng nhau hoặc đều là NULL. Tương tự như vậy, IS NOT DISTINCT FROM được định nghĩa như là “NOT <row value expression> IS DISTINCT FROM <row value expression>”. SQL:1999 cũng đã giới thiệu các biến kiểu BOOLEAN, mà theo tiêu chuẩn cũng có thể giữ các giá trị Unknown. Trong thực tế, 1 số hệ thống (như PostgreSQL) thực thi BOOLEAN Unknown như 1 BOOLEAN NULL.

Thao tác trên dữ liệu

Ngôn ngữ thao tác dữ liệu ( Data Manipulation Language – DML) là 1 tập con của SQL được dùng để thêm, cập nhật và xóa dữ liệu:

  • INSERT thêm các dòng vào 1 bảng đã có, ví dụ:
INSERT INTO example (field1, field2, field3)
VALUES (‘test’,’N’,NULL);
  • UPDATE điều chỉnh 1 tập các dòng đã có, ví dụ:
UPDATE example
SET field1=’updated value’
WHERE field2=’N’;
  • DELETE xóa các dòng đang có của 1 bảng, ví dụ:
DELETE FROM example
WHERE field2=’N’;
  • MERGE được dùng để kết hợp dữ liệu của nhiều bảng. Nó kết hợp các thành phần INSERT và UPDATE. Nó được định nghĩa trong tiêu chuẩn SQL:2003; trước đó, 1 vài cơ sở dữ liệu đã cung cấp chức năng tương tự qua cú pháp khác, thỉnh thoảng được gọi là “upsert”.
MERGE INTO TABLE_NAME USING table_reference ON (condition)
WHEN MATCHED THEN
UPDATE SET column1=value1[, column2 = value2 …]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 …]) VALUES ( value1 [, value2 …])

Quản lý giao dịch

Các giao dịch, nếu có, bao trùm các phép toán DML:

  • START TRANSACTION ( hoặc BEGIN WORK, hoặc BEGIN TRANSACTION, dựa trên phương ngữ SQL) đánh dấu việc bắt đầu của 1 giao dịch cơ sở dữ liệu, mà hoặc là hoàn thành hoàn toàn, hoặc không hoàn thành.
  • SAVE TRANSACTION ( hoặc SAVEPOINT) lưu các trạng thái của cơ sở dữ liệu tại điểm hiện tại của giao dịch.
CREATE TABLE tbl_1(id INT);
INSERT INTO tbl_1(id) VALUES(1); 
INSERT INTO tbl_1(id) VALUES(2);
COMMIT;
UPDATE tbl_1 SET id=200 WHERE id=1;
SAVEPOINT id_1upd; 
UPDATE tbl_1 SET id=1000 WHERE id=2;
ROLLBACK TO id_1upd;
SELECT id FROM tbl_1;
  • COMMIT làm tất cả thay đổi dữ liệu trong 1 giao dịch được lưu trữ lâu dài trong cơ sở dữ liệu.
  • ROLLBACK loại bỏ tất cả thay đổi dữ liệu từ COMMIT hoặc ROLLBACK gần nhất, trả dữ liệu về như trước khi có những thay đổi trên. Một khi khai báo COMMIT hoàn thành, các thay đổi của giao dịch không thể quay ngược lại.
  • COMMITROLLBACK tiêu hủy giao dịch hiện tại và giải phóng việc khóa dữ liệu. Với sự vắng mặt của khai báo START TRANSACTION hoặc 1 khai báo tương đương, ngữ nghĩa của SQL phụ thuộc vào việc thực thi. Ví dụ sau cho thấy 1 giao dịch chuyển tiền cơ bản, nơi tiền được xóa từ 1 tài khoản và cộng vào tài khoản khác. Nếu 1 trong 2 việc xóa hoặc thêm thất bại, toàn bộ giao dịch sẽ được quay lại.
START TRANSACTION; 
UPDATE Account SET amount=amount-200 WHERE account_number=1234;
UPDATE Account SET amount=amount+200 WHERE account_number=2345; 
IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;

Định nghĩa dữ liệu

Ngôn ngữ định nghĩa dữ liệu (Data Definition Language – DDL) quản lý cấu trúc bảng và chỉ mục. Các thành phần cơ bản nhất của DDL là các khai báo CREATE, ALTER, RENAME, DROP và TRUNCATE:

  • CREATE tạo ra 1 đối tượng (ví dụ như 1 bảng) trong cơ sở dữ liệu:
CREATE TABLE example ( 
    field1 INTEGER, field2 VARCHAR(50), 
    field3 DATE NOT NULL, 
    PRIMARY KEY(field1, field2)
);
  • ALTER điểu chỉnh cấu trúc của 1 đối tượng đang có theo nhiều cách khác nhau, ví dụ, thêm 1 cột vào 1 bảng đang có hoặc 1 ràng buộc.
ALTER TABLE example ADD field4 NUMBER(3) NOT NULL;
  • TRUNCATE xóa tất cả dữ liệu từ 1 bảng theo 1 cách rất nhanh, xóa dữ liệu bên trong bảng mà không xóa chính bảng đó. Nó thường ngụ ý 1 tác vụ COMMIT tiếp theo, do vậy, nó không thể được quay lại (dữ liệu không được viết vào log cho việc rollback sau đó, không giống như DELETE)
TRUNCATE TABLE example;
  • DROP xóa 1 đối tượng trong cơ sở dữ liệu, thông thường không thể cứu vãn, do đó nó không thể quay ngược lại được.
DROP TABLE example;

Các kiểu dữ liệu

Mỗi cột trong 1 bảng SQL khai báo kiểu dữ liệu mà cột đó có thể chứa. ANSI SQL bao gồm các kiểu dữ liệu sau:

Các chuỗi ký tự
  • CHARACTER(n) hay CHAR(n): chuỗi ký tự có chiều dài n ký tự cố định, được thêm các khoảng trắng khi cần thiết.
  • CHARACTER VARYING(n) hay VARCHAR(n): chuỗi ký tự có độ dài thay đổi với chiều dài tối đa là n ký tự.
  • NATIONAL CHARACTER(n) hay NCHAR(n): chuỗi ký tự có chiều dài cố định hỗ trợ 1 tập ký tự quốc tế.
  • NATIONAL CHARACTER VARYING(n) hay NVARCHAR(n): chuỗi NCHAR có chiều dài thay đổi.
Các chuỗi bit
  • BIT(n): 1 mảng có n bit.
  • BIT VARYING(n): 1 mảng có thể có đến n bit.
Số
  • INTEGER, SMALLINT và BIGINT
  • FLOAT, REAL và DOUBLE PRECISION
  • NUMERIC(precision, scale) hay DECIMAL(precision, scale)

SQL cung cấp 1 hàm để làm tròn số hoặc ngày tháng, gọi là TRUNC (trong Informix, DB2, PostgreSQL, Oracle và MySQL) hay ROUND (trong Informix, SQLite, Sybase, Oracle, PostgreSQL và Microsoft SQL Server).

Ngày tháng và thời gian
  • DATE: giá trị ngày tháng (ví dụ 2011-05-03)
  • TIME: giá trị thời gian (ví dụ 15:51:36).
  • TIME WITH TIME ZONE hay TIMETZ: tương tự như TIME, nhưng bao gồm các chi tiết về múi giờ trong câu hỏi.
  • TIMESTAMP: Đây là 1 giá trị DATE và 1 giá trị TIME đặt chung với nhau trong 1 biến (ví dụ: 2011-05-03 15:51:36)
  • TIMESTAMP WITH TIME ZONE hay TIMESTAMPTZ: tương tự như TIMESTAMP, bao gồm chi tiết về múi giờ trong câu hỏi.

Điều khiển dữ liệu

Ngôn ngữ điều khiển dữ liệu (Data Control Language – DCL) cho phép người dùng truy cập và thao tác với dữ liệu. 2 khai báo chính của nó là:

  • GRANT cho phép 1 hoặc nhiều người dùng thực hiện 1 tác vụ hay 1 tập tác vụ trên 1 đối tượng.
  • REVOKE loại bỏ 1 quyền được cấp, có thể là quyền mặc định.

Ví dụ:

GRANT SELECT, UPDATE ON example TO some_user, another_user;
REVOKE SELECT, UPDATE ON example FROM some_user, another_user;

Các mở rộng về tiến trình

SQL được thiết kế cho 1 mục đích cụ thể: để truy vấn dữ liệu chứa trong 1 cơ sở dữ liệu quan hệ. SQL là 1 ngôn ngữ truy vấn khai báo, dựa trên tập, không phải 1 ngôn ngữ như C hay BASIC. Tuy nhiên, các mở rộng cho SQL chuẩn bổ sung thêm các tính năng của ngôn ngữ lập trình tiến trình, như các cấu trúc điều khiển luồng xử lý. Bao gồm:

Source Common name Full name
ANSI/ISO Standard SQL/PSM SQL/Persistent Stored Modules
Interbase / Firebird PSQL Procedural SQL
IBM DB2 SQL PL SQL Procedural Language (implements SQL/PSM)
IBM Informix SPL Stored Procedural Language
IBM Netezza NZPLSQL [1] (based on Postgres PL/pgSQL)
Microsoft / Sybase T-SQL Transact-SQL
Mimer SQL SQL/PSM SQL/Persistent Stored Module (implements SQL/PSM)
MySQL SQL/PSM SQL/Persistent Stored Module (implements SQL/PSM)
NuoDB SSP Starkey Stored Procedures
Oracle PL/SQL Procedural Language/SQL (based on Ada)
PostgreSQL PL/pgSQL Procedural Language/PostgreSQL (based on Oracle PL/SQL)
PostgreSQL PL/PSM Procedural Language/Persistent Stored Modules (implements SQL/PSM)
Sybase Watcom-SQL SQL Anywhere Watcom-SQL Dialect
Teradata SPL Stored Procedural Language
SAP SAP HANA SQL Script

Ngoài các mở rộng của SQL/PSM tiêu chuẩn và các mở rộng SQL độc quyền, khả năng lập trình tiến trình và hướng đối tượng có trên nhiều nền tảng SQL qua việc tích hợp DBMS với các ngôn ngữ khác. Chuẩn SQL định nghĩa các phần mở rộng SQL/JRT (SQL Routines and Types for the Java Programming Language) để hỗ trợ mã Java trong cơ sở dữ liệu SQL. SQL Server 2005 sử dụng SQLCLR (SQL Server Common Language Runtime) để tổ chức các assembly .NET trong cơ sở dữ liệu, trong khi các phiên bản trước của SQL Server bị giới hạn cho các thủ tục lưu trữ mở rộng không được quản lý được viết chủ yếu bằng C. PostgreSQL để người dùng viết các chức năng trong 1 nhóm các ngôn ngữ khác nhau – bao gồm Perl, Python, Tcl và C.

(Nội dung bài viết được tham khảo từ website Wikipedia.org)

Leave a Reply