Database yaratish — CREATE DATABASE, psql buyruqlari, \l, \c, \dt

~15 daq · PostgreSQL · DDL va DML

Table yaratishdan oldin unga ega bo'ladigan database yaratilishi kerak. Bu darsda database yaratishni, mavjud databaselarni ko'rishni, ularga kirishni va asosiy psql buyruqlarini o'rganamiz.

Database nima?

Database — bu bir-biriga bog'liq tablelar, funksiyalar va boshqa ob'ektlarning to'plami. PostgreSQL da bir serverda bir nechta mustaqil database bo'lishi mumkin.

PostgreSQL server
├── database: myapp
│   ├── table: users
│   └── table: orders
├── database: testdb
│   └── table: products
└── database: postgres  ← standart, har doim mavjud

Database yaratish

SQL orqali

CREATE DATABASE myapp;

Qo'shimcha parametrlar bilan:

CREATE DATABASE myapp
  OWNER = postgres
  ENCODING = 'UTF8';

Parametr	Nima qiladi
`OWNER`	Databasega ega foydalanuvchi
`ENCODING`	Belgilar kodlash tizimi (UTF8 — standart)

psql terminaldan

createdb myapp

Mavjud databaselarni ko'rish — `\l`

psql ichida \l (list databases) buyrug'i barcha databaselarni ko'rsatadi:

postgres=# \l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype
-----------+----------+----------+-------------+-------------
 myapp     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8

template0 va template1 — PostgreSQL ning ichki shablonlari. Ular bilan ishlamaslik kerak.

Databasega ulanish — `\c`

postgres=# \c myapp
You are now connected to database "myapp" as user "postgres".
myapp=#

Prompt myapp=# ga o'zgardi — endi siz myapp databasesidasiz.

Boshqa user bilan ulanish:

\c myapp myuser

Tablelarni ko'rish — `\dt`

Databasega ulanib, undagi tablelarni ko'rish:

myapp=# \dt

         List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | orders   | table | postgres
 public | users    | table | postgres

Hech qanday table yo'q bo'lsa:

myapp=# \dt
Did not find any relations.

Boshqa foydali psql buyruqlari

Buyruq	Nima qiladi
`\l`	Barcha databaselarni ko'rsatish
`\c dbname`	Databasega ulanish
`\dt`	Joriy databasedagi tablelarni ko'rsatish
`\d tablename`	Table strukturasini ko'rsatish
`\dn`	Schema larni ko'rsatish
`\du`	Foydalanuvchilar va rollarni ko'rsatish
`\q`	psql dan chiqish
`\?`	Barcha psql buyruqlar ro'yxati
`\h CREATE TABLE`	SQL buyruq haqida yordam

Table strukturasini ko'rish — `\d`

Table yaratilgandan keyin uning ustunlarini ko'rish:

myapp=# \d users

                        Table "public.users"
   Column   |          Type          | Nullable |      Default
------------+------------------------+----------+--------------------
 id         | integer                | not null | nextval('users_id_seq')
 email      | character varying(255) | not null |
 created_at | timestamp              |          | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Database o'chirish

DROP DATABASE myapp;

[!WARNING] DROP DATABASE — qaytarib bo'lmaydigan amal. Database ichidagi barcha tablelar va ma'lumotlar yo'q bo'ladi. Ishlatishdan oldin ikki marta o'ylang.

O'chirish faqat shu databasega hech kim ulanmagan bo'lsa ishlaydi. Ulangan bo'lsa:

DROP DATABASE myapp WITH (FORCE);

Amaliy mashq

-- 1. Yangi database yaratish
CREATE DATABASE do_it_yourself;

-- 2. psql da ulanish
-- \c do_it_yourself

-- 3. Tablelar yo'qligini ko'rish
-- \dt

-- 4. Darsdan chiqish
-- \q

💡 Keyingi darsda do_it_yourself databasesida CREATE TABLE bilan jadval yaratamiz.