SELECT

Kirish: ushbu darsda siz jadvaldan ma'lumot olish uchun PostgreSQL SELECT so'rovini qanday ishlatishni o'rganasiz.

PostgreSQL SELECT so'rovi

SELECT so'rovi jadvaldan ma'lumot olish uchun ishlatiladi. SELECT so'rovining asosiy sintaksisi:

SELECT
  column1,
  column2,
  ...
FROM
  table_name;

Bu sintaksisda:

• Birinchi, FROM bandida ma'lumot olishni istagan jadval nomini ko'rsating.
• Ikkinchi, SELECT bandida natija to'plamiga kiritilishi kerak bo'lgan ustunlar ro'yxatini bering.

Barcha ustunlardan ma'lumot olish

inventories jadvalidagi barcha ma'lumotlarni olish uchun SELECT so'rovida yulduzcha (*) ishlatiladi:

SELECT * FROM inventories;

Natija:

 name             | brand   | quantity | price
------------------+---------+----------+---------
 iPhone 14 Pro    | Apple   |       10 | 999.99
 Galaxy S23 Ultra | Samsung |       15 | 1199.99
 Pixel 7 Pro      | Google  |        8 | 899.99
 Xperia 1 IV      | Sony    |        9 | 1099.99

[!TIP] Amalda, SELECT * ni ishlatish tavsiya etilmaydi, chunki jadval katta bo'lsa, zarur bo'lmagan ustunlarni ham qaytaradi va samaradorlikni pasaytiradi.

Ba'zi ustunlardan ma'lumot olish

Faqat kerakli ustunlarni olish uchun SELECT dan keyin ustun nomlarini vergul bilan ajratib ko'rsating:

SELECT
  name,
  price
FROM
  inventories;

Natija:

 name             | price
------------------+---------
 iPhone 14 Pro    | 999.99
 Galaxy S23 Ultra | 1199.99
 Pixel 7 Pro      | 899.99
 Xperia 1 IV      | 1099.99

Ma'lumotlarni o'zgartirish uchun SELECT so'rovini ishlatish

SELECT so'rovida ustunlarni matematik ifodalar bilan birlashtirib hisoblash mumkin:

SELECT
  name,
  quantity * price
FROM
  inventories;

Natija:

 name             | ?column?
------------------+----------
 iPhone 14 Pro    |  9999.90
 Galaxy S23 Ultra | 17999.85
 Pixel 7 Pro      |  7199.92
 Xperia 1 IV      |  9099.93

Bu so'rovda inventories jadvalining name ustunidan ma'lumot olamiz. Bundan tashqari, quantity va price ustunlaridan ma'lumot olib, ularni bir vaqtda ko'paytiramiz. Ko'paytirish operatoridan (*) tashqari, qo'shish (+), ayirish (-) va bo'lish (/) kabi boshqa matematik operatorlardan ham foydalanish mumkin. SELECT so'rovida ustunlarda hisob-kitob amalga oshirilsa, bu ustun ko'pincha hisoblangan ustun deb ataladi.

Ustun taxalluslari (Column aliases)

Hisoblangan ustun (quantity * price) natijasi ?column? bo'lib chiqadi. Bu PostgreSQL tomonidan avtomatik belgilanadigan vaqtinchalik ustun nomi. Lekin bu nom mazmunli emas. Hisoblangan ustun (yoki oddiy ustun) ga mazmunli nom berish uchun ustun taxallusidan foydalanish mumkin. Ustun taxallusi — SELECT so'rovida ustun uchun belgilanadigan vaqtinchalik nom. Ustun taxallusini aniqlash sintaksisi:

column_name AS column_alias

Bu sintaksisda ustun nomidan keyin AS kalit so'zi va ustun taxallusini ko'rsatasiz. AS kalit so'zi ixtiyoriy, shuning uchun uni qo'ymasangiz ham bo'ladi:

column_name column_alias

Masalan, hisoblangan ustun uchun ustun taxallusini quyidagicha belgilash mumkin:

SELECT
  name,
  quantity * price AS amount
FROM
  inventories;

Natija:

 name             | amount
------------------+----------
 iPhone 14 Pro    |  9999.90
 Galaxy S23 Ultra | 17999.85
 Pixel 7 Pro      |  7199.92
 Xperia 1 IV      |  9099.93

Natijada ?column? o'rniga amount ko'rsatiladi. AS kalit so'zisiz ham ishlatish mumkin:

SELECT
  name,
  quantity * price amount
FROM
  inventories;

Bir xil natijani qaytaradi. E'tibor bering, ustun taxallusini faqat hisoblangan ustun uchun emas, har qanday ustun uchun belgilashingiz mumkin. Masalan, quyidagi so'rov name ustuniga product_name taxallusini belgilaydi:

SELECT
  name AS product_name,
  quantity * price AS amount
FROM
  inventories;

Natija:

 product_name     | amount
------------------+----------
 iPhone 14 Pro    |  9999.90
 Galaxy S23 Ultra | 17999.85
 Pixel 7 Pro      |  7199.92
 Xperia 1 IV      |  9099.93

Xulosa

• Jadvaldan ma'lumot olish uchun SELECT column_list FROM table_name sintaksisidan foydalaning.
• Jadvalning barcha ustunlarini olish uchun SELECT * dan foydalaning.
• Hisoblangan ustun yoki ustunlarga mazmunli nom berish uchun ustun taxalluslaridan foydalaning.