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.
Các tin liên quan
Cách tạo, xóa & quản lý bảng trong PostgreSQL trên server cloud2013-08-01
