Cách quản lý cơ sở dữ liệu SQL

Giới thiệu

Cơ sở dữ liệu SQL được cài đặt với tất cả các lệnh bạn cần để thêm, sửa đổi, xóa và truy vấn dữ liệu của mình. Hướng dẫn kiểu cheat sheet này cung cấp tài liệu tham khảo nhanh về một số lệnh SQL được sử dụng phổ biến nhất.

Cách sử dụng Hướng dẫn này:

• Hướng dẫn này ở định dạng cheat sheet với các đoạn mã dòng lệnh độc lập
• Chuyển đến bất kỳ phần nào có liên quan đến nhiệm vụ bạn đang cố gắng hoàn thành
• Khi bạn nhìn thấy highlighted text (văn bản được đánh dấu) trong các lệnh của hướng dẫn này, hãy nhớ rằng văn bản này phải tham chiếu đến các cột, bảng và dữ liệu trong cơ sở dữ liệu của riêng bạn.
• Trong toàn bộ hướng dẫn này, tất cả các giá trị dữ liệu ví dụ đã cho đều được gói gọn trong dấu nháy đơn ('). Trong SQL, cần phải bọc bất kỳ giá trị dữ liệu nào bao gồm các chuỗi trong dấu nháy đơn. Điều này không bắt buộc đối với dữ liệu số, nhưng nó cũng sẽ không gây ra bất kỳ sự cố nào nếu bạn bao gồm dấu nháy đơn.

Xin lưu ý rằng, mặc dù SQL được công nhận là tiêu chuẩn, nhưng hầu hết các chương trình cơ sở dữ liệu SQL đều có phần mở rộng độc quyền của riêng chúng. Hướng dẫn này sử dụng MySQL làm hệ thống quản lý cơ sở dữ liệu quan hệ ví dụ (relational database management system - RDBMS), nhưng các lệnh được cung cấp sẽ hoạt động với các chương trình cơ sở dữ liệu quan hệ khác, bao gồm PostgreSQL, MariaDB và SQLite. Khi có sự khác biệt đáng kể giữa các RDBMS, chúng tôi đã đưa vào các lệnh thay thế.

Mở Database Prompt (sử dụng Xác thực Socket/Trust)

Theo mặc định trên Ubuntu 18.04, root MySQL user có thể xác thực mà không cần mật khẩu bằng lệnh sau:

sudo mysql

Để mở PostgreSQL prompt, hãy sử dụng lệnh sau. Ví dụ này sẽ đăng nhập bạn với tư cách là người dùng postgres, đây là vai trò siêu người dùng được bao gồm, nhưng bạn có thể thay thế vai trò đó bằng bất kỳ vai trò nào đã được tạo sẵn:

sudo -u postgres psql

Mở Database Prompt (sử dụng Xác thực mật khẩu)

Nếu root MySQL user của bạn được đặt để xác thực bằng mật khẩu, bạn có thể thực hiện bằng lệnh sau:

mysql -u root -p

Nếu bạn đã thiết lập tài khoản non-root user cho cơ sở dữ liệu của mình, bạn cũng có thể sử dụng phương pháp này để đăng nhập với tư cách người dùng đó:

mysql -u user -p

Lệnh trên sẽ nhắc bạn nhập mật khẩu sau khi bạn chạy nó. Nếu bạn muốn cung cấp mật khẩu của mình như một phần của lệnh, hãy ngay lập tức làm theo tùy chọn -p với mật khẩu của bạn, không có khoảng cách giữa chúng:

mysql -u root -ppassword

Tạo cơ sở dữ liệu

Lệnh sau tạo cơ sở dữ liệu với cài đặt mặc định.

CREATE DATABASE database_name;

Nếu bạn muốn cơ sở dữ liệu của mình sử dụng bộ ký tự và đối chiếu khác với mặc định, bạn có thể chỉ định những ký tự đó bằng cú pháp sau:

CREATE DATABASE database_name CHARACTER SET character_set COLLATE collation;

Liệt kê cơ sở dữ liệu

Để xem cơ sở dữ liệu nào tồn tại trong bản cài đặt MySQL hoặc MariaDB của bạn, hãy chạy lệnh sau:

SHOW DATABASES;

Trong PostgreSQL, bạn có thể xem cơ sở dữ liệu nào đã được tạo bằng lệnh sau:

\list

Xóa cơ sở dữ liệu

Để xóa cơ sở dữ liệu, bao gồm bất kỳ bảng và dữ liệu nào được giữ trong đó, hãy chạy lệnh tuân theo cấu trúc sau:

DROP DATABASE IF EXISTS database;

Tạo người dùng

Để tạo hồ sơ người dùng cho cơ sở dữ liệu của bạn mà không chỉ định bất kỳ đặc quyền nào cho nó, hãy chạy lệnh sau:

CREATE USER username IDENTIFIED BY 'password';

PostgreSQL sử dụng một cú pháp tương tự, nhưng hơi khác một chút:

CREATE USER user WITH PASSWORD 'password';

Nếu bạn muốn tạo một người dùng mới và cấp cho họ các đặc quyền trong một lệnh, bạn có thể làm như vậy bằng cách đưa ra câu lệnh GRANT. Lệnh dưới đây tạo một người dùng mới và cấp cho họ đầy đủ các đặc quyền đối với mọi cơ sở dữ liệu và bảng trong RDBMS:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Lưu ý từ khóa PRIVILEGES trong câu lệnh GRANT trước đó. Trong hầu hết các RDBMS, từ khóa này là tùy chọn và câu lệnh này có thể được viết tương đương như sau:

GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Tuy nhiên, hãy lưu ý rằng từ khóa PRIVILEGES là bắt buộc để cấp các đặc quyền như thế này khi chế độ SQL nghiêm ngặt được bật.

Xóa người dùng

Sử dụng cú pháp sau để xóa hồ sơ người dùng cơ sở dữ liệu:

DROP USER IF EXISTS username;

Lưu ý rằng theo mặc định, lệnh này sẽ không xóa bất kỳ bảng nào được tạo bởi người dùng đã xóa và việc cố gắng truy cập các bảng đó có thể dẫn đến lỗi.

Chọn cơ sở dữ liệu

Trước khi bạn có thể tạo bảng, trước tiên bạn phải thông báo cho RDBMS cơ sở dữ liệu mà bạn muốn tạo bảng. Trong MySQL và MariaDB, hãy làm như vậy với cú pháp sau:

USE database;

Trong PostgreSQL, bạn phải sử dụng lệnh sau để chọn cơ sở dữ liệu mong muốn:

\connect database

Tạo bảng

Cấu trúc lệnh sau đây tạo một bảng mới với tên table và bao gồm hai cột, mỗi cột có kiểu dữ liệu cụ thể của riêng chúng:

CREATE TABLE table ( column_1 column_1_data_type, column_2 column_2_data_taype );

Xóa bảng

Để xóa hoàn toàn một bảng, bao gồm tất cả dữ liệu của nó, hãy chạy như sau:

DROP TABLE IF EXISTS table

Chèn dữ liệu vào bảng

Sử dụng cú pháp sau để điền một bảng với một hàng dữ liệu:

INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_A', 'data_B', 'data_C' );

Bạn cũng có thể điền vào một bảng có nhiều hàng dữ liệu bằng một lệnh đơn lẻ, như sau:

INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_1A', 'data_1B', 'data_1C' ),  ( 'data_2A', 'data_2B', 'data_2C' ), ( 'data_3A', 'data_3B', 'data_3C' );

Xóa dữ liệu khỏi bảng

Để xóa một hàng dữ liệu khỏi bảng, hãy sử dụng cấu trúc lệnh sau. Lưu ý rằng value phải là giá trị được giữ trong column đã chỉ định trong hàng mà bạn muốn xóa:

DELETE FROM table WHERE column='value';

DELETE FROM table;

Thay đổi dữ liệu trong bảng

Sử dụng cú pháp sau để cập nhật dữ liệu được giữ trong một hàng nhất định. Lưu ý rằng mệnh đề WHERE ở cuối lệnh cho SQL biết hàng nào cần cập nhật. value là giá trị được giữ trong column_A thẳng hàng với hàng bạn muốn thay đổi.

UPDATE table SET column_1 = value_1, column_2 = value_2 WHERE column_A=value;

Chèn một cột

Cú pháp lệnh dưới đây sẽ thêm một cột mới vào bảng:

ALTER TABLE table ADD COLUMN column data_type;

Xóa một cột

Một lệnh theo cấu trúc này sẽ xóa một cột khỏi bảng:

ALTER TABLE table DROP COLUMN column;

Thực hiện các truy vấn cơ bản

Để xem tất cả dữ liệu từ một cột trong bảng, hãy sử dụng cú pháp sau:

SELECT column FROM table;

Để truy vấn nhiều cột từ cùng một bảng, hãy phân tách các tên cột bằng dấu phẩy:

SELECT column_1, column_2 FROM table;

Bạn cũng có thể truy vấn mọi cột trong bảng bằng cách thay thế tên của các cột bằng dấu hoa thị (*). Trong SQL, dấu hoa thị đóng vai trò giữ chỗ để đại diện cho “all”:

SELECT * FROM table;

Sử dụng mệnh đề WHERE

Bạn có thể thu hẹp kết quả của truy vấn bằng cách nối thêm câu lệnh SELECT với mệnh đề WHERE, như sau:

SELECT column FROM table WHERE conditions_that_apply;

Ví dụ: bạn có thể truy vấn tất cả dữ liệu từ một hàng bằng cú pháp như sau. Lưu ý rằng value phải là một giá trị được giữ trong cả column đã chỉ định và hàng bạn muốn truy vấn:

SELECT * FROM table WHERE column = value;

Làm việc với toán tử so sánh (Comparison Operators)

Toán tử so sánh trong mệnh đề WHERE xác định cách so sánh cột đã chỉ định với giá trị. Dưới đây là một số toán tử so sánh SQL phổ biến:

Làm việc với ký tự đại diện

SQL cho phép sử dụng ký tự đại diện (wildcard characters). Chúng rất hữu ích nếu bạn đang cố gắng tìm một mục cụ thể trong một bảng, nhưng không chắc mục đó chính xác là gì.

Dấu hoa thị (*) là phần giữ chỗ đại diện cho “all”, điều này sẽ truy vấn mọi cột trong bảng:

SELECT * FROM table;

Dấu hiệu tỷ lệ phần trăm (%) đại diện cho 0 hoặc nhiều ký tự không xác định.

SELECT * FROM table WHERE column LIKE val%;

Dấu gạch dưới (_) được sử dụng để biểu thị một ký tự không xác định:

SELECT * FROM table WHERE column LIKE v_lue;

Đếm các mục trong một cột

Hàm COUNT được sử dụng để tìm số lượng mục nhập trong một cột nhất định. Cú pháp sau sẽ trả về tổng số giá trị được giữ trong column:

SELECT COUNT(column) FROM table;

Bạn có thể thu hẹp kết quả của hàm COUNT bằng cách nối thêm mệnh đề WHERE, như sau:

SELECT COUNT(column) FROM table WHERE column=value;

Tìm giá trị trung bình trong một cột

Hàm AVG được sử dụng để tìm giá trị trung bình (trong trường hợp này là giá trị trung bình) giữa các giá trị được giữ trong một cột cụ thể. Lưu ý rằng hàm AVG sẽ chỉ hoạt động với các cột chứa các giá trị số; khi được sử dụng trên một cột chứa các giá trị chuỗi, nó có thể trả về lỗi hoặc 0:

SELECT AVG(column) FROM table;

Tìm tổng các giá trị trong một cột

Hàm SUM được sử dụng để tìm tổng của tất cả các giá trị số được giữ trong một cột:

SELECT SUM(column) FROM table;

Như với hàm AVG, nếu bạn chạy hàm SUM trên một cột chứa các giá trị chuỗi, hàm này có thể trả về lỗi hoặc chỉ 0, tùy thuộc vào RDBMS của bạn.

Tìm giá trị lớn nhất trong một cột

Để tìm giá trị số lớn nhất trong một cột hoặc giá trị cuối cùng theo thứ tự bảng chữ cái, hãy sử dụng hàm MAX:

SELECT MAX(column) FROM table;

Tìm giá trị nhỏ nhất trong một cột

Để tìm giá trị số nhỏ nhất trong một cột hoặc giá trị đầu tiên theo thứ tự bảng chữ cái, hãy sử dụng hàm MIN:

SELECT MIN(column) FROM table;

Sắp xếp kết quả với mệnh đề ORDER BY

Mệnh đề ORDER BY được sử dụng để sắp xếp kết quả truy vấn. Cú pháp truy vấn sau đây trả về các giá trị từ column_1 và column_2, đồng thời sắp xếp kết quả theo các giá trị được giữ trong column_1 theo thứ tự tăng dần hoặc, đối với giá trị chuỗi, theo thứ tự bảng chữ cái:

SELECT column_1, column_2 FROM table ORDER BY column_1;

Để thực hiện hành động tương tự, nhưng sắp xếp kết quả theo thứ tự bảng chữ cái giảm dần hoặc đảo ngược, hãy thêm truy vấn bằng DESC:

SELECT column_1, column_2 FROM table ORDER BY column_1 DESC;

Sắp xếp kết quả với các mệnh đề GROUP BY

Mệnh đề GROUP BY tương tự như mệnh đề ORDER BY, nhưng nó được dùng để sắp xếp kết quả của một truy vấn bao gồm một hàm tổng hợp, chẳng hạn như COUNT, MAX, MIN hoặc SUM. Về bản thân, các hàm tổng hợp được mô tả trong phần trước sẽ chỉ trả về một giá trị duy nhất. Tuy nhiên, bạn có thể xem kết quả của một hàm tổng hợp được thực hiện trên mọi giá trị phù hợp trong một cột bằng cách đưa vào mệnh đề GROUP BY.

Cú pháp sau sẽ đếm số giá trị phù hợp trong column_2 và nhóm chúng theo thứ tự tăng dần hoặc theo bảng chữ cái:

SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2;

Để thực hiện hành động tương tự, nhưng nhóm các kết quả theo thứ tự bảng chữ cái giảm dần hoặc đảo ngược, hãy thêm truy vấn bằng DESC:

SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2 DESC;

Truy vấn nhiều bảng với mệnh đề JOIN

Các mệnh đề JOINđược sử dụng để tạo các tập kết quả kết hợp các hàng từ hai hoặc nhiều bảng:

SELECT table_1.column_1, table_2.column_2 FROM table_1 JOIN table_2 ON table_1.common_column=table_2.common_column;

Đây là một ví dụ về mệnh đề INNER JOIN. INNER JOIN sẽ trả về tất cả các bản ghi có giá trị khớp trong cả hai bảng, nhưng sẽ không hiển thị bất kỳ bản ghi nào không có giá trị khớp.

Có thể trả về tất cả các bản ghi từ một trong hai bảng, bao gồm các giá trị không khớp tương ứng trong bảng khác, bằng cách sử dụng mệnh đề JOIN bên ngoài. Các mệnh đề JOIN bên ngoài được viết dưới dạng LEFT JOIN hoặc RIGHT JOIN.

Mệnh đề LEFT JOIN trả về tất cả các bản ghi từ bảng “bên trái” và chỉ các bản ghi phù hợp từ bảng “bên phải”. Trong ngữ cảnh của mệnh đề JOIN bên ngoài, bảng bên trái là bảng được tham chiếu trong mệnh đề FROM và bảng bên phải là bất kỳ bảng nào khác được tham chiếu sau câu lệnh JOIN. Phần sau đây sẽ hiển thị mọi bản ghi từ table_1 và chỉ các giá trị phù hợp từ table_2. Bất kỳ giá trị nào không khớp trong table_2 sẽ xuất hiện dưới dạng NULL trong tập hợp kết quả:

SELECT table_1.column_1, table_2.column_2 FROM table_1 LEFT JOIN table_2 ON table_1.common_column=table_2.common_column;

Mệnh đề RIGHT JOIN hoạt động giống như mệnh đề LEFT JOIN, nhưng nó in tất cả các kết quả từ bảng bên phải và chỉ các giá trị phù hợp từ bên trái:

SELECT table_1.column_1, table_2.column_2 FROM table_1 RIGHT JOIN table_2 ON table_1.common_column=table_2.common_column;

Kết hợp nhiều câu lệnh SELECT với mệnh đề UNION

Toán tử UNION rất hữu ích để kết hợp kết quả của hai (hoặc nhiều) câu lệnh SELECT vào một tập kết quả duy nhất:

SELECT column_1 FROM table UNION SELECT column_2 FROM table;

Ngoài ra, mệnh đề UNION có thể kết hợp hai (hoặc nhiều) câu lệnh SELECT truy vấn các bảng khác nhau vào cùng một tập kết quả:

SELECT column FROM table_1 UNION SELECT column FROM table_2;

Kết luận

Hướng dẫn này bao gồm một số lệnh phổ biến hơn trong SQL được sử dụng để quản lý cơ sở dữ liệu, người dùng và bảng cũng như truy vấn nội dung chứa trong các bảng đó. Tuy nhiên, có nhiều cách kết hợp các mệnh đề và toán tử mà tất cả đều tạo ra các tập kết quả duy nhất. Nếu bạn đang tìm kiếm một hướng dẫn toàn diện hơn để làm việc với SQL, chúng tôi khuyên bạn nên xem Tài liệu tham khảo SQL về Cơ sở dữ liệu của Oracle.