AlgoBook

Chế độ

Kiến thức cơ bản về SQL ​

Tất cả các tác vụ được thực hiện trong SQLite giả định sự hiện diện của một cơ sở dữ liệu đang hoạt động (một hoặc nhiều), vì vậy việc tạo và mở cơ sở dữ liệu (tương tự như một tệp) là các thao tác khung bắt buộc nhằm thiết lập môi trường lập trình cần thiết. Không có tiện ích nào để xóa cơ sở dữ liệu theo chương trình trong SQLite vì giả định rằng bạn có thể đơn giản xóa tệp cơ sở dữ liệu khỏi đĩa.

Các hành động có sẵn trong bối cảnh một cơ sở dữ liệu đã mở có thể được chia có điều kiện thành các nhóm chính sau:

  • Tạo và xóa bảng, cũng như sửa đổi lược đồ của chúng, tức là mô tả cột, bao gồm việc xác định kiểu, tên và các ràng buộc
  • Tạo (thêm), đọc, chỉnh sửa và xóa bản ghi trong bảng; các thao tác này thường được ký hiệu bằng từ viết tắt phổ biến CRUD (Create, Read, Update, Delete)
  • Xây dựng các truy vấn để chọn bản ghi từ một hoặc kết hợp nhiều bảng theo các điều kiện phức tạp
  • Tối ưu hóa thuật toán bằng cách xây dựng chỉ mục trên các cột đã chọn, sử dụng chế độ xem (view), bao bọc các hành động hàng loạt trong giao dịch, khai báo các trình kích hoạt xử lý sự kiện và các công cụ nâng cao khác

Trong cơ sở dữ liệu SQL, tất cả các hành động này được thực hiện bằng các lệnh SQL dành sẵn (hoặc câu lệnh). Do đặc thù của việc tích hợp với MQL5, một số hành động được thực hiện bởi các hàm MQL5 tích hợp sẵn. Ví dụ, việc mở, áp dụng hoặc hủy một giao dịch được thực hiện bởi bộ ba hàm DatabaseTransaction, mặc dù tiêu chuẩn SQL (và triển khai công khai của SQLite) có các lệnh SQL tương ứng (BEGIN TRANSACTION, COMMIT và ROLLBACK).

Hầu hết các lệnh SQL cũng có sẵn trong các chương trình MQL: chúng được truyền đến động cơ thực thi SQLite dưới dạng tham số chuỗi của các hàm DatabaseExecute hoặc DatabasePrepare. Sự khác biệt giữa hai tùy chọn này nằm ở một số sắc thái.

DatabasePrepare cho phép chuẩn bị một truy vấn để thực thi hàng loạt theo chu kỳ sau đó với các giá trị tham số khác nhau ở mỗi lần lặp (bản thân các tham số, tức là tên của chúng trong truy vấn, là giống nhau). Ngoài ra, các truy vấn đã chuẩn bị này cung cấp cơ chế để đọc kết quả bằng cách sử dụng DatabaseRead và DatabaseReadBind. Vì vậy, bạn có thể sử dụng chúng cho các thao tác với tập hợp các bản ghi đã chọn.

Ngược lại, hàm DatabaseExecute thực thi một truy vấn đơn được truyền một cách đơn phương: lệnh đi vào động cơ SQLite, thực hiện một số hành động trên dữ liệu, nhưng không trả về gì. Điều này thường được sử dụng để tạo bảng hoặc sửa đổi dữ liệu hàng loạt.

Trong tương lai, chúng ta sẽ thường xuyên phải thao tác với một số khái niệm cơ bản. Hãy giới thiệu chúng:

Table — một tập hợp dữ liệu có cấu trúc, bao gồm các hàng và cột. Mỗi hàng là một bản ghi dữ liệu riêng biệt với các trường (thuộc tính) được mô tả bằng tên và kiểu của các cột tương ứng. Tất cả các bảng cơ sở dữ liệu được lưu trữ vật lý trong tệp cơ sở dữ liệu và có sẵn để đọc và ghi (nếu quyền không bị hạn chế khi mở cơ sở dữ liệu).

View — một loại bảng ảo được tính toán bởi động cơ SQLite dựa trên một truy vấn SQL đã cho, các bảng khác hoặc các chế độ xem. Các chế độ xem chỉ có thể đọc. Không giống như bất kỳ bảng nào (bao gồm cả bảng tạm thời mà SQL cho phép tạo trong bộ nhớ trong suốt phiên chương trình), các chế độ xem được tính toán lại động mỗi khi chúng được truy cập.

Index — một cấu trúc dữ liệu dịch vụ (cây cân bằng, B-tree) để tìm kiếm nhanh các bản ghi theo giá trị của các trường (thuộc tính) được xác định trước hoặc kết hợp của chúng.

Trigger — một chương trình con gồm một hoặc nhiều câu lệnh SQL được chỉ định để tự động chạy nhằm phản hồi các sự kiện (trước hoặc sau) thêm, thay đổi hoặc xóa một bản ghi trong một bảng cụ thể.

Dưới đây là danh sách ngắn các câu lệnh SQL phổ biến nhất và các hành động mà chúng thực hiện:

  • CREATE — tạo một đối tượng cơ sở dữ liệu (bảng, chế độ xem, chỉ mục, trình kích hoạt);
  • ALTER — thay đổi một đối tượng (bảng);
  • DROP — xóa một đối tượng (bảng, chế độ xem, chỉ mục, trình kích hoạt);
  • SELECT — chọn các bản ghi hoặc tính toán các giá trị đáp ứng các điều kiện đã cho;
  • INSERT — thêm dữ liệu mới (một hoặc một tập hợp bản ghi);
  • UPDATE — thay đổi các bản ghi hiện có;
  • DELETE — xóa các bản ghi khỏi bảng;

Danh sách chỉ hiển thị các từ khóa bắt đầu cấu trúc ngôn ngữ SQL tương ứng. Cú pháp chi tiết hơn sẽ được trình bày dưới đây. Ứng dụng thực tế của chúng sẽ được thể hiện trong các ví dụ sau.

Mỗi câu lệnh có thể kéo dài qua nhiều dòng (các ký tự xuống dòng và khoảng trắng thừa bị bỏ qua). Nếu cần, bạn có thể gửi nhiều lệnh đến SQLite cùng một lúc. Trong trường hợp này, sau mỗi lệnh, bạn nên sử dụng ký tự kết thúc lệnh ';' (dấu chấm phẩy).

Văn bản trong các lệnh được hệ thống phân tích không phân biệt chữ hoa chữ thường, nhưng trong SQL, theo thông lệ, các từ khóa được viết bằng chữ cái in hoa.

Khi tạo một bảng, chúng ta phải chỉ định tên của nó, cũng như danh sách các cột trong dấu ngoặc đơn, cách nhau bằng dấu phẩy. Mỗi cột được đặt một tên, một kiểu và tùy chọn một ràng buộc. Dạng đơn giản nhất:

sql
CREATE TABLE table_name
  ( column_name type [ constraints ] [, column_name type [ constraints ] ...]);

Chúng ta sẽ xem các ràng buộc trong SQL ở phần tiếp theo. Trong lúc này, hãy xem một ví dụ rõ ràng (với các kiểu và tùy chọn khác nhau):

sql
CREATE TABLE IF NOT EXISTS example_table
   (id INTEGER PRIMARY KEY,
    name TEXT,
    timestamp INTEGER DEFAULT CURRENT_STAMP,
    income REAL,
    data BLOB);

Cú pháp để tạo một chỉ mục là:

sql
CREATE [ UNIQUE ] INDEX index_name
  ON table_name( column_name [, column_name ...]);

Các chỉ mục hiện có được tự động sử dụng trong các truy vấn với điều kiện lọc trên các cột tương ứng. Nếu không có chỉ mục, quá trình sẽ chậm hơn.

Việc xóa một bảng (cùng với dữ liệu, nếu đã có gì đó được ghi vào nó) khá đơn giản:

sql
DROP TABLE table_name;

Bạn có thể chèn dữ liệu vào bảng như sau:

sql
INSERT INTO table_name [ ( column_name [, column_name ...] ) ]
  VALUES( value [, value ...]);

Danh sách đầu tiên trong dấu ngoặc đơn bao gồm các tên cột và là tùy chọn (xem giải thích bên dưới). Nó phải khớp với danh sách thứ hai với các giá trị cho chúng. Ví dụ,

sql
INSERT INTO example_table (name, income) VALUES ('Morning Flat Breakout', 1000);

Lưu ý rằng các chuỗi ký tự trong SQL được đặt trong dấu nháy đơn.

Nếu tên cột bị bỏ qua trong câu lệnh INSERT, từ khóa VALUES được giả định là theo sau bởi các giá trị cho tất cả các cột trong bảng, và theo đúng thứ tự được mô tả trong bảng.

Cũng có các dạng phức tạp hơn của toán tử, đặc biệt cho phép chèn bản ghi từ các bảng khác hoặc kết quả truy vấn.

Việc chọn bản ghi theo điều kiện, với giới hạn tùy chọn của danh sách các trường trả về (cột), được thực hiện bằng lệnh SELECT.

sql
SELECT column_name [, column_name ...] FROM table_name [WHERE condition ];

Nếu bạn muốn trả về mọi bản ghi khớp hoàn toàn (tất cả các cột), hãy sử dụng ký hiệu dấu sao:

sql
SELECT * FROM table_name [WHERE condition ];

Khi không có điều kiện, hệ thống trả về tất cả các bản ghi trong bảng.

Là một điều kiện, bạn có thể thay thế một biểu thức logic bao gồm tên cột và các toán tử so sánh khác nhau, cũng như các hàm SQL tích hợp và kết quả của một truy vấn SELECT lồng nhau (các truy vấn như vậy được viết trong dấu ngoặc đơn). Các toán tử so sánh bao gồm:

  • Logical AND
  • Logical OR
  • IN cho một giá trị trong danh sách
  • NOT IN cho một giá trị ngoài danh sách
  • BETWEEN cho một giá trị trong phạm vi
  • LIKE — tương tự về chính tả với một mẫu có ký tự đại diện đặc biệt ('%', '_')
  • EXISTS — kiểm tra xem kết quả của truy vấn lồng có rỗng không

Ví dụ, một lựa chọn tên bản ghi với thu nhập ít nhất 1000 và không cũ hơn một năm (được làm tròn sơ bộ đến tháng gần nhất):

sql
SELECT name FROM example_table
  WHERE income >= 1000 AND timestamp > datetime('now', 'start of month', '-1 year');

Ngoài ra, lựa chọn có thể được sắp xếp theo thứ tự tăng dần hoặc giảm dần (ORDER BY), nhóm theo đặc điểm (GROUP BY), và lọc theo nhóm (HAVING). Chúng ta cũng có thể giới hạn số lượng bản ghi trong đó (LIMIT, OFFSET). Đối với mỗi nhóm, bạn có thể trả về giá trị của bất kỳ hàm tổng hợp nào, đặc biệt là COUNT, SUM, MIN, MAX và AVG, được tính toán trên tất cả các bản ghi của nhóm.

sql
SELECT [ DISTINCT ] column_name [, column_name...](i) FROM table_name
  [ WHERE condition ]
  [ORDER BY column_name [ ASC | DESC ]
     [ LIMIT quantity OFFSET start_offset ] ]
  [ GROUP BY column_name ⌠ HAVING condition ] ];

Từ khóa tùy chọn DISTINCT cho phép loại bỏ các bản sao (nếu chúng được tìm thấy trong kết quả theo tiêu chí lựa chọn hiện tại). Nó chỉ có ý nghĩa khi không có nhóm.

LIMIT sẽ chỉ cho kết quả có thể tái sản xuất nếu có sắp xếp.

Nếu cần, lựa chọn SELECT có thể được thực hiện không từ một bảng mà từ nhiều bảng, kết hợp chúng theo sự kết hợp cần thiết của các trường. Từ khóa JOIN được sử dụng cho việc này.

sql
SELECT [...] FROM table name_1
  [ INNER | OUTER | CROSS ] JOIN table_name_2
  ON boolean_condition

hoặc

sql
SELECT [...] FROM table name_1
  [ INNER | OUTER | CROSS ] JOIN table_name_2
  USING ( common_column_name [, common_column_name ...] )

SQLite hỗ trợ ba loại JOIN: INNER JOIN, OUTER JOIN và CROSS JOIN. Cuốn sách cung cấp ý tưởng chung về chúng từ các ví dụ, trong khi bạn có thể tự khám phá thêm chi tiết.

Ví dụ, sử dụng JOIN, bạn có thể xây dựng tất cả các tổ hợp bản ghi từ một bảng với các bản ghi từ bảng khác hoặc so sánh các giao dịch từ bảng giao dịch (gọi là "deals") với các giao dịch từ cùng bảng theo nguyên tắc khớp định danh vị thế, nhưng sao cho hướng của các giao dịch (vào thị trường/ra khỏi thị trường) là ngược nhau, dẫn đến một bảng ảo của các giao dịch.

sql
SELECT // liệt kê các cột của bảng kết quả với bí danh (sau 'as')
  d1.time as time_in, d1.position_id as position, d1.type as type, // bảng d1
   d1.volume as volume, d1.symbol as symbol, d1.price as price_in,
  d2.time as time_out, d2.price as price_out,                      // bảng d2
   d2.swap as swap, d2.profit as profit,
  d1.commission + d2.commission as commission                      // tổ hợp
  FROM deals d1 INNER JOIN deals d2      // d1 và d2 - bí danh của một bảng "deals"
  ON d1.position_id = d2.position_id     // điều kiện hợp nhất theo vị thế
  WHERE d1.entry = 0 AND d2.entry = 1    // điều kiện lựa chọn "vào/ra"

Đây là một truy vấn SQL từ trợ giúp MQL5, nơi các ví dụ JOIN có sẵn trong mô tả của các hàm DatabaseExecute và DatabasePrepare.

Đặc tính cơ bản của SELECT là nó luôn trả về kết quả cho chương trình gọi, không giống như các truy vấn khác như CREATE, INSERT, v.v. Tuy nhiên, từ SQLite 3.35, các câu lệnh INSERT, UPDATE và DELETE cũng có khả năng trả về giá trị, nếu cần, bằng cách sử dụng từ khóa bổ sung RETURNING. Ví dụ,

sql
INSERT INTO example_table (name, income) VALUES ('Morning Flat Breakout', 1000)
   RETURNING id;

Dù trong trường hợp nào, kết quả truy vấn trong MQL5 được truy cập thông qua các hàm DatabaseColumn, DatabaseRead và DatabaseReadBind.

Ngoài ra, SELECT cho phép đánh giá kết quả của các biểu thức và trả về chúng nguyên trạng hoặc kết hợp chúng với kết quả từ các bảng. Các biểu thức có thể bao gồm hầu hết các toán tử mà chúng ta quen thuộc từ biểu thức MQL5, cũng như các hàm SQL tích hợp. Xem tài liệu SQLite để biết danh sách đầy đủ. Ví dụ, đây là cách bạn có thể tìm phiên bản build hiện tại của SQLite trong terminal và phiên bản trình chỉnh sửa của bạn, điều này có thể quan trọng để tìm hiểu các tùy chọn nào có sẵn.

sql
SELECT sqlite_version();

Ở đây toàn bộ biểu thức bao gồm một lần gọi duy nhất của hàm sqlite_version. Tương tự như chọn nhiều cột từ một bảng, bạn có thể đánh giá nhiều biểu thức cách nhau bằng dấu phẩy.

Một số hàm thống kê và toán học phổ biến cũng có sẵn.

Các bản ghi nên được chỉnh sửa bằng câu lệnh UPDATE.

sql
UPDATE table_name SET column_name = value [, column_name = value ...] 
  WHERE condition;

Cú pháp cho lệnh xóa như sau:

sql
DELETE FROM table_name WHERE condition;

Được phát hành theo MIT License.

Copyright © 2024