Trang chủ / Chuyên đề / Lệnh truy vấn SQL trong postgresql sử dụng select
Thứ sáu, 02/08/2013 | 00:00 GMT+7

Lệnh truy vấn SQL trong postgresql sử dụng select

PostgreSQL là một hệ quản trị database open-souce sử dụng ngôn ngữ truy vấn SQL. PostgreSQL, hay đơn giản là "Postgres", là một công cụ rất hữu ích trên server VPS vì nó có thể xử lý nhu cầu lưu trữ dữ liệu của các trang web và các ứng dụng khác. Trong hướng dẫn này, ta sẽ xem xét cách truy vấn database PostgreSQL. 

Ở đây Ta sẽ sử dụng hệ điều hành Ubuntu, các bản phân phối Linux khác cũng sẽ hoạt động tương tự.

Đăng nhập vào PostgreSQL

Ta sẽ download database mẫu để làm việc từ internet.

Đầu tiên, đăng nhập vào user Postgres mặc định bằng lệnh sau:

sudo su - postgres

Ta sẽ có được file database bằng lệnh:

wget https://ftp.postgresql.org/pub/projects/pgFoundry/dbsamples/world/world-1.0/world-1.0.tar.gz

Giải nén repository (kho-lưu-trữ) đã được giải nén và thay đổi thành folder nội dung:

tar xzvf world-1.0.tar.gz
cd dbsamples-0.1/world

Tạo database để nhập cấu trúc file vào:

createdb -T template0 worlddb

Cuối cùng, ta sẽ sử dụng file .sql làm đầu vào cho database mới được tạo:

psql worlddb < world.sql

Bây giờ ta đã sẵn sàng đăng nhập vào môi trường mới tạo của bạn:

psql worlddb

Cách hiển thị dữ liệu trong PostgreSQL

Trước khi bắt đầu, ta hãy tìm hiểu về loại dữ liệu ta vừa nhập. Để xem danh sách các bảng, ta có thể sử dụng lệnh sau:

\d+
                         List of relations
 Schema |      Name       | Type  |  Owner   |  Size  | Description 
--------+-----------------+-------+----------+--------+-------------
 public | city            | table | postgres | 264 kB | 
 public | country         | table | postgres | 48 kB  | 
 public | countrylanguage | table | postgres | 56 kB  | 
(3 rows)

Ta có ba bảng ở đây. Nếu ta muốn xem các cột tạo nên bảng "city", ta có thể sử dụng lệnh này:

\d city
          Table "public.city"
   Column    |     Type     | Modifiers 
-------------+--------------+-----------
 id          | integer      | not null
 name        | text         | not null
 countrycode | character(3) | not null
 district    | text         | not null
 population  | integer      | not null
Indexes:
    "city_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "country" CONSTRAINT "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)

Ta có thể xem thông tin về từng cột, cũng như mối quan hệ của bảng này với các tập dữ liệu khác.

Cách truy vấn dữ liệu với lựa chọn trong PostgreSQL

Ta truy vấn (yêu cầu) thông tin từ Postgres bằng cách sử dụng câu lệnh "select". Các câu lệnh này sử dụng cú pháp chung sau:

SELECT columns_to_return FROM table_name;

Ví dụ: nếu ta dùng "\ d country", ta có thể thấy rằng bảng "country" có nhiều cột. Ta có thể tạo một truy vấn liệt kê tên của country và continent mà nó nằm trên đó:

SELECT name,continent FROM country;
                     name                     |   continent   
----------------------------------------------+---------------
 Afghanistan                                  | Asia
 Netherlands                                  | Europe
 Netherlands Antilles                         | North America
 Albania                                      | Europe
 Algeria                                      | Africa
 American Samoa                               | Oceania
 Andorra                                      | Europe
 . . .

Để xem tất cả các cột trong một bảng cụ thể, ta có thể sử dụng ký tự đại diện dấu hoa thị (*). Điều này nghĩa là "mọi khả năng" và kết quả là sẽ trả về mọi cột.

SELECT * FROM city;
  id  |               name                | countrycode |           district            | population 
------+-----------------------------------+-------------+-------------------------------+------------
    1 | Kabul                             | AFG         | Kabol                         |    1780000
    2 | Qandahar                          | AFG         | Qandahar                      |     237500
    3 | Herat                             | AFG         | Herat                         |     186800
    4 | Mazar-e-Sharif                    | AFG         | Balkh                         |     127800
    5 | Amsterdam                         | NLD         | Noord-Holland                 |     731200
    6 | Rotterdam                         | NLD         | Zuid-Holland                  |     593321
    7 | Haag                              | NLD         | Zuid-Holland                  |     440900
. . .
Ở đây, ta thấy toàn bộ bảng "city".

Sắp xếp các kết quả truy vấn trong PostgreSQL

Bạn có thể sắp xếp các kết quả truy vấn của bạn bằng cách sử dụng mệnh đề "order by". Điều này cho phép bạn chỉ định thứ tự sắp xếp cho dữ liệu trả về.

Mệnh đề "order by" đứng sau câu lệnh select thông thường. Đây là cú pháp chung:

SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];

Nếu ta muốn chọn country và châu lục từ bảng country , sau đó sắp xếp theo châu lục, ta có thể đưa ra những điều sau:

SELECT name,continent FROM country ORDER BY continent;
                     name                     |   continent   
----------------------------------------------+---------------
 Algeria                                      | Africa
 Western Sahara                               | Africa
 Madagascar                                   | Africa
 Uganda                                       | Africa
 Malawi                                       | Africa
 Mali                                         | Africa
 Morocco                                      | Africa
 Côte d\u0092Ivoire                           | Africa
 . . .

Như bạn thấy, theo mặc định, câu lệnh sắp xếp dữ liệu theo thứ tự tăng dần. Điều này nghĩa là nó bắt đầu từ đầu của các tổ chức có chữ cái và số lượng thấp nhất cho các tìm kiếm số.

Nếu ta muốn đảo ngược thứ tự sắp xếp, ta có thể gõ "desc" sau khai báo cột "order by":

SELECT name,continent FROM country ORDER BY continent DESC;
                     name                     |   continent   
----------------------------------------------+---------------
 Paraguay                                     | South America
 Bolivia                                      | South America
 Brazil                                       | South America
 Falkland Islands                             | South America
 Argentina                                    | South America
 Venezuela                                    | South America
 Guyana                                       | South America
 Chile                                        | South America
. . .

Ta cũng có thể chọn sắp xếp theo nhiều cột. Ta có thể có trường sắp xếp chính và sau đó là các trường sắp xếp bổ sung được sử dụng nếu nhiều bản ghi có cùng giá trị trong trường sắp xếp chính.

Ví dụ: ta có thể sắp xếp theo châu lục và sau đó theo country để có được danh sách các bản ghi country theo thứ tự bảng chữ cái trong mỗi châu lục:

SELECT name,continent FROM country ORDER BY continent,name;
                     name                     |   continent   
----------------------------------------------+---------------
 Angola                                       | Africa
 Burundi                                      | Africa
 Benin                                        | Africa
 Burkina Faso                                 | Africa
 Botswana                                     | Africa
 Central African Republic                     | Africa
 Côte d\u0092Ivoire                           | Africa
 Cameroon                                     | Africa
 Congo, The Democratic Republic of the        | Africa
. . .

Bây giờ ta có sắp xếp theo thứ tự bảng chữ cái trong hai cột.

Lọc kết quả truy vấn trong PostgreSQL

Ta đã học cách chọn một số thông tin nhất định từ một bảng bằng cách chỉ định các cột, nhưng Postgres cung cấp nhiều cơ chế lọc chi tiết hơn.

Ta có thể lọc kết quả bằng cách thêm mệnh đề "where". Theo sau mệnh đề where là phần mô tả kết quả mà ta muốn nhận được.

Ví dụ: nếu ta muốn chọn tất cả các city ở USA, ta có thể yêu cầu Postgres trả lại tên của các city có mã country ba chữ số là "USA":

SELECT name FROM city WHERE countrycode = 'USA';
          name           
-------------------------
 New York
 Los Angeles
 Chicago
 Houston
 Philadelphia
 Phoenix
 San Diego
 Dallas
 San Antonio
. . .

Các giá trị chuỗi, như USA ở trên, phải được đặt trong dấu ngoặc kép để Postgres diễn giải chính xác.

Truy vấn trước đó đã sử dụng "=" để so sánh xem giá trị cột có khớp chính xác với giá trị được cho ở bên phải của biểu thức hay không. Ta có thể tìm kiếm linh hoạt hơn mặc dù với toán tử so sánh "like".

Toán tử like có thể sử dụng "_" làm ký tự đại diện để trùng với một ký tự và "%" làm ký tự đại diện trùng với 0 hoặc nhiều ký tự.

Ta cũng có thể kết hợp các cụm từ lọc với "AND" hoặc "OR". Hãy kết hợp một số bộ lọc để tìm các city ở USA có tên bắt đầu bằng "N":

SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%';
        name        
--------------------
 New York
 Nashville-Davidson
 New Orleans
 Newark
 Norfolk
 Newport News
 Naperville
 New Haven
 North Las Vegas
 Norwalk
 New Bedford
 Norman
(12 rows)
Tất nhiên, ta có thể sắp xếp các kết quả này với "order".
SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%' ORDER BY name;
        name        
--------------------
 Naperville
 Nashville-Davidson
 Newark
 New Bedford
 New Haven
 New Orleans
 Newport News
 New York
 Norfolk
 Norman
 North Las Vegas
 Norwalk
(12 rows)

Hoạt động chọn nâng cao trong PostgreSQL

Ta sẽ kiểm tra một số truy vấn phức tạp hơn. Hãy xem xét những điều sau:

SELECT country.name AS country,city.name AS capital,continent FROM country JOIN city ON country.capital = city.id ORDER BY continent,country;
                country                |              capital              |   continent   
---------------------------------------+-----------------------------------+---------------
 Algeria                               | Alger                             | Africa
 Angola                                | Luanda                            | Africa
 Benin                                 | Porto-Novo                        | Africa
 Botswana                              | Gaborone                          | Africa
 Burkina Faso                          | Ouagadougou                       | Africa
 Burundi                               | Bujumbura                         | Africa
 Cameroon                              | Yaoundé                           | Africa
 Cape Verde                            | Praia                             | Africa
 Central African Republic              | Bangui                            | Africa
 Chad                                  | N´Djaména                         | Africa
. . .

Truy vấn này có một vài phần khác nhau. Hãy bắt đầu từ cuối và làm việc ngược lại.

Phần "order by" của câu lệnh (ĐẶT HÀNG THEO continent , country ) .

Phần này của câu lệnh yêu cầu Postgres sắp xếp dựa trên continent trước tiên, sau đó sắp xếp các mục nhập có giá trị continent phù hợp theo cột country .

Để giải thích phần tiếp theo, đặc tả bảng, ta sẽ tìm hiểu về phép nối bảng.

Chọn dữ liệu từ nhiều bảng trong PostgreSQL với JOIN

Postgres cho phép bạn chọn dữ liệu từ các bảng khác nhau, có liên quan bằng cách sử dụng mệnh đề "JOIN". Các bảng có liên quan với nhau nếu mỗi bảng có một cột có thể tham chiếu đến cùng một dữ liệu.

Trong database ví dụ , bảng "country " và "city" chia sẻ một số dữ liệu. Ta có thể thấy rằng bảng "country " tham chiếu đến bảng "city" bằng lệnh:

\d country
. . .
. . .
Foreign-key constraints:
    "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
. . .
. . .

Câu lệnh này cho ta biết rằng cột "capital" trong bảng "country" là một tham chiếu đến cột "id" trong bảng "city". Điều này nghĩa là ta gần như có thể coi hai bảng này là một bảng khổng lồ bằng cách so khớp các giá trị trong các cột đó.

Trong truy vấn , lựa chọn bảng có nội dung "FROM country JOIN city ON country.capital = city.id".

Trong câu lệnh này, ta yêu cầu Postgres trả về thông tin từ cả hai bảng. Câu lệnh "join" chỉ định phép nối mặc định, còn gọi là "inner join".

Một inner join sẽ trả về thông tin có trong cả hai bảng. Ví dụ: nếu ta đang đối sánh các cột không có liên quan rõ ràng như foreign keys, ta có thể gặp phải tình huống trong đó một bảng có các giá trị không được khớp trong bảng kia. Những thứ này sẽ không được trả lại bằng cách sử dụng join thông thường.

Phần sau từ khóa "on" chỉ định các cột mà các bảng chia sẻ để Postgres biết dữ liệu có liên quan như thế nào. Thông tin này được cung cấp bằng cách chỉ định:

table_name.column_name

Trong trường hợp , ta đang chọn các bản ghi có giá trị khớp trong cả hai bảng, trong đó cột capital của bảng country sẽ được so sánh với cột id của bảng city.

Kết luận

Đến đây bạn đã có cơ bản về cách truy vấn database PostGres. Như bạn thấy cách truy vấn dữ liệu là rất linh hoạt, và sẽ rất hữu ích cho việc xây dựng  ứng dụng và các trang web tương tác xung quanh công nghệ database này.


Tags: SQL

Các tin trước

Các lệnh truy cấn quản lý bảng dữ liệu trong postgresql trên cloud-server 2013-08-01

Bảo mật MySQL và MariaDB Databases trên Linux VPS 2013-07-23

Cài đặt Firewall với UFW trên Ubuntu và Debian Cloud Server 2013-06-25

Cơ bản nhất về MYSQL trên Ubuntu CentOS 2012-06-12