Thứ sáu, 02/08/2013 | 00:00 GMT+7

Cách tạo truy vấn dữ liệu trong PostgreSQL bằng cách sử dụng lệnh chọn


PostgreSQL là gì?

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. Điều này sẽ cho phép ta hướng dẫn Postgres trả lại tất cả dữ liệu mà nó quản lý phù hợp với tiêu chí ta đang tìm kiếm.

Hướng dẫn này giả định bạn đã cài đặt Postgres trên máy tính của bạn . Ta sẽ sử dụng Ubuntu 12.04, nhưng bất kỳ bản phân phối Linux hiện đại nào cũng sẽ hoạt động.

Đă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 http://pgfoundry.org/frs/download.php/527/world-1.0.tar.gz

Extract repository đã được extract 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àn ở đây. Nếu ta muốn xem các cột tạo nên bảng "thành phố", 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 phát hành "\ 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 quốc gia và lục địa mà nó nằm trên đó với những điều sau:

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à " trùng với 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 "thành phố".

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 quốc gia và châu lục từ bảng quốc gia, 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 ở đầu 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 quốc gia để có được danh sách các bản ghi quốc gia theo thứ tự bảng chữ cái ở 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ỉ chọn một số thông tin nhất định từ bảng bằng cách chỉ định các cột mà ta muốn, nhưng Postgres cung cấp các cơ chế lọc chi tiết hơn.

Ta có thể lọc kết quả bằng cách đưa vào mệnh đề "where". Theo sau mệnh đề where là 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ã quốc gia 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
. . .

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ự đơn lẻ và "%" làm ký tự đại diện trùng với không hoặc nhiều ký tự.

Ta cũng có thể kết hợp các cụm từ lọc với "và" hoặc "hoặc". 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 giống như với dữ liệu chọn lọc thông thường, chưa được lọc.
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)

Thao tác 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 "sắp xếp theo" của câu lệnh (ĐẶT HÀNG THEO lục địa, quốc gia) nên quen thuộc.

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

Để 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 tham gia

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 đề "tham gia". Các bảng có liên quan với nhau nếu mỗi bảng có một cột có thể đề cập đến cùng một dữ liệu.

Trong database mẫu của ta , bảng "quốc gia" và "thành phố" của ta chia sẻ một số dữ liệu. Ta có thể thấy rằng bảng "quốc gia" tham chiếu đến bảng "thành phố" 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 "thủ đô" trong bảng "quốc gia" là một tham chiếu đến cột "id" trong bảng "thành phố". Đ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 của ta , lựa chọn bảng có nội dung "TỪ quốc gia THAM GIA city TRÊN 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à "phép nối bên trong".

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 tham gia 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 của ta , ta đang chọn các bản ghi có giá trị phù hợp trong cả hai bảng, trong đó cột capital của bảng quốc gia sẽ được so sánh với cột id của bảng city .

Tiêu chí lựa chọn đặt tên cho các tham gia bảng trong PostgreSQL

Bây giờ ta đang bắt đầu câu lệnh truy vấn của ta . Phần chọn các cột. Phần này sẽ khá đơn giản để giải mã dựa trên phần cuối cùng.

Bây giờ ta phải đặt tên cho bảng có các cột nếu ta đang cố gắng chọn một tên cột có trong cả hai bảng.

Ví dụ: ta đã chọn cột "tên" từ cả bảng quốc gia và bảng city . Nếu ta bỏ đi "tên_bảng". phần lựa chọn, kết quả phù hợp sẽ không rõ ràng và Postgres sẽ không biết dữ liệu nào sẽ trả về.

Ta giải quyết vấn đề này bằng cách nói rõ ràng về bảng sẽ chọn khi có va chạm đặt tên. Đôi khi, việc đặt tên cho các bảng sẽ hữu ích dù tên có phải là duy nhất hay không để duy trì khả năng đọc., / P>

Kết luận

Đến đây bạn sẽ có một ý tưởng cơ bản về cách hình thành các truy vấn. Điều này cung cấp cho bạn khả năng trả về dữ liệu cụ thể từ nhiều nguồn khác nhau. Điều này rất hữu ích cho việc xây dựng hoặc sử dụng các ứng dụng và trang web tương tác xung quanh công nghệ này.


Tags:

Các tin liên quan

Cách tạo, xóa & quản lý bảng trong PostgreSQL trên server cloud
2013-08-01