Học PostgreSQL với ví dụ chạy trực tiếp
Hướng dẫn PostgreSQL miễn phí với truy vấn chạy ngay trên trang: kiểu dữ liệu, CREATE TABLE, upsert, JSONB, ngày tháng, CTE, hàm cửa sổ, chỉ mục và VACUUM. Sửa truy vấn rồi chạy lại.
Bắt đầu khóa họcBắt đầu
- PostgreSQL và MySQLPostgreSQL và MySQL đều là cơ sở dữ liệu quan hệ miễn phí. PostgreSQL có SQL phong phú hơn, JSONB và DDL trong transaction; MySQL dễ vận hành hơn ở nơi nó đã là mặc định.
- PostgreSQL và SQL ServerPostgreSQL miễn phí và mã nguồn mở; SQL Server là cơ sở dữ liệu thương mại của Microsoft với T-SQL và bộ công cụ xoay quanh Windows. Đây là cách SQL, JSON, lock và giấy phép của chúng khác nhau.
- PostgreSQL và MongoDBPostgreSQL lưu hàng trong các bảng có schema cố định và có thể chứa tài liệu JSON trong cột jsonb; MongoDB lưu các tài liệu có schema linh hoạt trong collection và chia shard chúng trên nhiều máy chủ.
- Xem database và bảngTrong psql, \l liệt kê các database, \c chuyển sang một database và \dt liệt kê các bảng của nó. Bằng SQL, hãy truy vấn pg_database, information_schema.tables và information_schema.columns.
Bảng và kiểu dữ liệu
- Kiểu dữ liệuCác kiểu dữ liệu PostgreSQL bạn sẽ dùng cho hầu hết mọi cột: số nguyên, numeric, text, boolean, timestamptz, uuid, jsonb, mảng và enum, kèm khi nào nên chọn từng kiểu.
- CREATE TABLECREATE TABLE định nghĩa các cột, kiểu và ràng buộc của một bảng. Gồm khóa identity, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS và LIKE.
- ALTER TABLEALTER TABLE thay đổi một bảng đã có: thêm, đổi tên và xóa cột, đổi kiểu của cột bằng USING, đặt giá trị mặc định và NOT NULL, và thêm ràng buộc.
- Khóa chínhCách định nghĩa khóa chính trong PostgreSQL và cách sinh giá trị cho nó: cột identity, SERIAL kiểu cũ, và UUID với gen_random_uuid(). Kèm khóa chính nhiều cột và đặt lại sequence.
- Khóa ngoạiKhóa ngoại khiến một cột chỉ nhận những giá trị có trong một bảng khác. Gồm REFERENCES, ON DELETE CASCADE, SET NULL và RESTRICT, thêm khóa vào bảng đã có, và đánh index cho nó.
- Kiểu enumKiểu enum là một danh sách cố định, có thứ tự, gồm các giá trị được phép, tạo bằng CREATE TYPE ... AS ENUM. Gồm thêm và đổi tên giá trị, liệt kê, sắp xếp, và khi nào ràng buộc CHECK là lựa chọn tốt hơn.
- Timestamptimestamptz lưu một thời điểm và hiển thị nó theo múi giờ của phiên; timestamp lưu giờ đồng hồ không kèm múi giờ. Không kiểu nào lưu múi giờ bạn đã ghi. Hãy dùng timestamptz cho hầu hết mọi thứ.
Truy vấn
- JoinJoin kết hợp các hàng của hai bảng theo một điều kiện. INNER JOIN giữ các hàng khớp, LEFT JOIN giữ mọi hàng của bảng thứ nhất, FULL JOIN giữ cả hai, CROSS JOIN ghép mọi thứ, và LATERAL chạy một truy vấn con cho mỗi hàng.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort giữ lại hàng đầu tiên của mỗi nhóm, ví dụ đơn hàng mới nhất của từng khách hàng. Đây là cú pháp riêng của PostgreSQL.
- COALESCE, NULLIF và CASECOALESCE(a, b, ...) trả về đối số đầu tiên khác NULL, NULLIF(a, b) biến một giá trị thành NULL, và CASE WHEN chọn một giá trị theo điều kiện.
- LIKE, ILIKE và regexLIKE so khớp văn bản với một mẫu dùng % và _, ILIKE làm điều tương tự nhưng bỏ qua hoa thường, còn các toán tử ~ so khớp biểu thức chính quy POSIX.
- CAST và ::CAST(value AS type) và value::type chuyển một giá trị sang kiểu khác trong PostgreSQL. Đây là cùng một phép chuyển đổi; :: là cú pháp ngắn hơn của PostgreSQL.
Thay đổi dữ liệu
- Upsert (ON CONFLICT)Upsert trong PostgreSQL với INSERT ... ON CONFLICT: DO UPDATE với EXCLUDED, DO NOTHING, cập nhật có điều kiện, bộ đếm, và phân biệt hàng được chèn với hàng được cập nhật.
- RETURNINGRETURNING khiến INSERT, UPDATE, DELETE và MERGE trả lại các hàng mà chúng đã thay đổi: id được sinh, giá trị mới, hàng bị xóa, và hành động nào của MERGE đã chạy.
- TransactionCách transaction hoạt động trong PostgreSQL: BEGIN, COMMIT và ROLLBACK, savepoint, điều gì xảy ra với một transaction đang mở khi có lỗi, và các mức cô lập.
JSON và mảng
- JSONBLưu và truy vấn JSON trong PostgreSQL với jsonb: toán tử -> và ->>, phép chứa @>, jsonb_set và các cách cập nhật khác, biến mảng thành hàng, index GIN, và json so với jsonb.
- MảngCột mảng trong PostgreSQL: cách khai báo, chỉ số bắt đầu từ 1, kiểm tra mảng có chứa giá trị bằng ANY, @> và &&, unnest, và các hàm mảng bạn cần.
- string_agg và array_aggstring_agg nối các giá trị của một nhóm thành một chuỗi và array_agg gom chúng thành một mảng. Gồm sắp xếp, DISTINCT, FILTER, cách xử lý NULL và các lỗi hay gặp.
Chuỗi và ngày tháng
- Hàm chuỗiCác hàm chuỗi PostgreSQL bạn dùng hằng ngày: substring, left và right, || và concat, split_part, position, replace, regexp_replace, trim, lpad và format.
- Hàm ngày thángLàm việc với ngày tháng trong PostgreSQL: now() và current_date, cộng trừ khoảng thời gian, date_trunc để nhóm, extract và age, và định dạng bằng to_char.
- generate_seriesgenerate_series trả về một tập hàng từ giá trị bắt đầu tới giá trị kết thúc: số, ngày và timestamp. Dùng nó để lấp khoảng trống trong báo cáo, sinh dữ liệu thử và dựng lịch.
Truy vấn nâng cao
- CTE (WITH)Biểu thức bảng chung (CTE) đặt tên cho một truy vấn con trong mệnh đề WITH để truy vấn chính đọc nó như một bảng. WITH RECURSIVE cho phép nó tự tham chiếu để duyệt cây và đồ thị.
- Window functionWindow function tính một giá trị cho mỗi hàng từ một nhóm các hàng liên quan mà không gộp chúng lại: xếp hạng, tổng lũy kế, trung bình trượt và so sánh với hàng trước.
Hiệu năng và bảo trì
- IndexCREATE INDEX mặc định tạo một index B-tree để PostgreSQL tìm hàng mà không phải đọc cả bảng. Các loại khác (GIN, GiST, BRIN, hash) dành cho JSONB, mảng, tìm kiếm toàn văn, kiểu khoảng và bảng rất lớn.
- EXPLAIN ANALYZEEXPLAIN cho thấy kế hoạch PostgreSQL chọn cho một truy vấn, còn EXPLAIN ANALYZE chạy truy vấn và thêm số hàng và thời gian thực tế của từng bước. Học cách đọc cây kế hoạch và tên các node thường gặp.
- View và materialized viewView là một truy vấn được lưu lại, chạy mỗi lần bạn đọc nó. Materialized view lưu kết quả của truy vấn và chỉ thay đổi khi bạn chạy REFRESH MATERIALIZED VIEW.
- VACUUMVACUUM xóa các phiên bản hàng chết mà UPDATE và DELETE để lại để dung lượng của chúng được dùng lại. Autovacuum chạy nó cho bạn; trang này cho biết khi nào, cách tinh chỉnh, và khi nào VACUUM FULL đáng để lock bảng.
Hàm và trigger
- Hàm và procedureCREATE FUNCTION định nghĩa một hàm bạn gọi bên trong truy vấn, viết bằng SQL hoặc PL/pgSQL. CREATE PROCEDURE định nghĩa một thủ tục chạy bằng CALL, có thể commit transaction giữa chừng.
- TriggerTrigger tự động chạy một hàm khi các hàng được chèn, cập nhật hoặc xóa. Trong PostgreSQL, bạn viết một hàm trigger trả về trigger, rồi gắn nó vào bảng bằng CREATE TRIGGER.