CÔNG NGHỆ @ PRO
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.
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
