Quản lý cơ sở dữ liệu - một số lệnh phổ biến trong SQL

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

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

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

Lưu ý: mặc dù SQL được công nhận là một tiêu chuẩn, hầu hết các chương trình cơ sở dữ liệu SQL có các phần mở rộng độc quyền riêng của họ. 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ệ (RDBMS) ví dụ, nhưng các lệnh được đưa ra 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 đã bao gồm các lệnh thay thế.

Mở cửa sổ dòng lệnh cơ sở dữ liệu (sử dụng xác thực Socket/Trust)

Mặc định trên Ubuntu 18.04, người dùng root MySQL có thể xác thực mà không cần mật khẩu bằng cách sử dụng lệnh sau:

sudo mysql

Để mở cửa sổ dòng lệnh PostgreSQL, sử dụng lệnh sau. Ví dụ này sẽ đăng nhập với tài khoản postgres, đó là vai trò superuser mặc định, nhưng bạn có thể thay thế bằng bất kỳ vai trò đã tạo trước nào:

sudo -u postgres psql

Mở cửa sổ dòng lệnh cơ sở dữ liệu (sử dụng xác thực mật khẩu)

Nếu người dùng root MySQL được đặt để xác thực bằng mật khẩu, bạn làm như vậy bằng lệnh sau:

mysql -u root -p

Nếu bạn đã thiết lập một tài khoản người dùng không phải là root 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 bằng tài khoản đó:

mysql -u user -p

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

mysql -u root -ppassword

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

Lệnh sau tạo một cơ sở dữ liệu với các thiết lập mặc định.

CREATE DATABASE database_name;

Nếu bạn muốn cơ sở dữ liệu của bạn sử dụng bộ ký tự và phân loại khác với các thiết lập mặc định, bạn có thể chỉ định chúng 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ác cơ sở dữ liệu tồn tại trong cài đặt MySQL hoặc MariaDB của bạn, chạy lệnh sau:

SHOW DATABASES;

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

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

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

DROP DATABASE IF EXISTS database;

Tạo người dùng

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

CREATE USER username IDENTIFIED BY 'password';

PostgreSQL sử dụng cú pháp tương tự, nhưng hơi khác nhau 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 sử dụng câu lệnh GRANT. Lệnh sau tạo một người dùng mới và cấp cho họ đầy đủ quyền truy cập vào 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 đây. 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ư:

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

Tuy nhiên, chú ý từ khóa PRIVILEGES là bắt buộc khi bật chế độ SQL để cấp quyền.

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 ý: lệnh này mặc định sẽ không xóa bất kỳ bảng nào được tạo bởi người dùng đã bị xóa, và cố gắng truy cập vào các bảng đó có thể dẫn đến lỗi.

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

Trước khi bạn có thể tạo một bảng, bạn phải thông báo cho RDBMS biết cơ sở dữ liệu mà bạn muốn tạo bảng trong đó. Trong MySQL và MariaDB, làm như vậy bằng 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 một bảng

Cấu trúc lệnh sau 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 nó:

CREATE TABLE table ( column_1 column_1_data_type, column_2 column_2_data_type );

Xóa một bảng

Để xóa một bảng hoàn toàn, bao gồm tất cả dữ liệu của nó, chạy lệ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 dữ liệu vào một hàng của bảng: Bạn cũng có thể điền dữ liệu vào bảng với nhiều hàng sử dụng một lệnh duy nhất, như sau:

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

Xóa dữ liệu từ bảng

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

DELETE FROM table WHERE column='value';

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

Sử dụng cú pháp sau để cập nhật dữ liệu trong một hàng đã cho. Lưu ý rằng mệnh đề WHERE ở cuối lệnh cho biết SQL nên cập nhật hàng nào. Giá trị (value) là giá trị được chứa trong cột_A tương ứ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 sau 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 tuân theo cấu trúc này sẽ xóa một cột từ bảng:

ALTER TABLE table DROP COLUMN column;

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

Để xem tất cả dữ liệu từ một cột duy nhất trong bảng, 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, phân tách 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 tất cả các cột trong một bảng bằng cách thay thế tên cột bằng dấu sao (*). Trong SQL, dấu sao hoạt động như các trình giữ chỗ để đại diện cho "tất cả":

SELECT * FROM table;

Sử dụng mệnh đề WHERE

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

SELECT column FROM table WHERE conditions_that_apply;

Ví dụ, bạn truy vấn tất cả dữ liệu từ một hàng duy nhất bằng cú pháp như sau. Lưu ý rằng giá trị (value) nên là một giá trị được chứa trong cả cột đã 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

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

Làm việc với Wildcard

SQL cho phép sử dụng ký tự Wildcard. Chúng hữu ích khi bạn muốn tìm một mục cụ thể trong một bảng nhưng không chắc chắn về mục đó là gì.

Dấu sao (*) là các giữ chỗ đại diện cho "all", điều này sẽ truy vấn tất cả các cột trong một bảng:

SELECT * FROM table;

Dấu phần trăm (%) đại diện cho một hoặc nhiều ký tự không biết:

SELECT * FROM table WHERE column LIKE val%;

Dấu gạch dưới (_) được sử dụng để đại diện cho một ký tự không biết duy nhất:

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 các mục trong một cột đã cho. Cú pháp sau sẽ trả về tổng số giá trị trong cột:

SELECT COUNT(column) FROM table;

Bạn có thể hạn chế kết quả của hàm COUNT bằng cách 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, trung bình cộng) của các giá trị trong một cột cụ thể. Lưu ý rằng hàm AVG chỉ hoạt động với các cột chứa giá trị số; khi sử dụng trên một cột chứa 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ính tổng các giá trị số trong một cột:

SELECT SUM(column) FROM table;

Tương tự như hàm AVG, nếu bạn chạy hàm SUM trên một cột chứa giá trị chuỗi, nó 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ự chữ cái, 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ự chữ cái, 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 trả về giá trị từ column_1 và column_2 và sắp xếp kết quả theo giá trị trong column_1 theo thứ tự tăng dần hoặc, 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 cùng hành động, nhưng sắp xếp kết quả theo thứ tự giảm dần hoặc đảo ngược thứ tự bảng chữ cái, thêm DESC vào truy vấn:

SELECT column_1, column_2 FROM table ORDER BY column_1 DESC;

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

Mệnh đề GROUP BY tương tự như mệnh đề ORDER BY, nhưng nó được sử 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 như COUNT, MAX, MIN, hoặc SUM. Một mình, các hàm tổng hợp được mô tả trong phần trước 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 tất cả các giá trị khớp trong một cột bằng cách bao gồm mệnh đề GROUP BY.

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

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

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

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

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 có sự khớp tương ứng trong bảng khác, bằng cách sử dụng mệnh đề OUTER JOIN. Mệnh đề OUTER JOIN đượ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 "trái" và chỉ các bản ghi khớp từ bảng "phải". Trong ngữ cảnh của mệnh đề OUTER JOIN, bảng "trái" là bảng được tham chiếu trong mệnh đề FROM, và bảng "phải" là bất kỳ bảng nào được tham chiếu sau câu JOIN. Sau đây là ví dụ sẽ hiển thị tất cả các bản ghi từ table_1 và chỉ các giá trị khớp từ table_2. Bất kỳ giá trị nào không có sự khớp trong table_2 sẽ xuất hiện là NULL trong tậ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ư LEFT JOIN, nhưng nó in tất cả các kết quả từ bảng "phải" và chỉ các giá trị khớ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 SELECT với mệnh đề UNION

Toán tử UNION hữu ích để kết hợp kết quả của hai (hoặc nhiều hơn) câu SELECT thành 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 hơn) câu 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 đề cập đến một số câu lệnh phổ biến trong SQL được sử dụng để quản lý cơ sở dữ liệu, người dùng và bảng, và truy vấn nội dung trong các bảng đó. Tuy nhiên, có nhiều kết hợp của mệnh đề và toán tử tạo ra các tập kết quả duy nhất.

Mọi người cũng tìm kiếm: rdbms trong sql, tách chuỗi sau dấu phẩy sql, các toán tử trong sql, hàm avg trong sql, các lệnh trong sql, use trong sql, viết các lệnh khởi tạo cơ sở dữ liệu, tạo cơ sở dữ liệu trong sql, lệnh go trong sql.