dmiZdPiu

1Перенес в my-mans
2
3-- подключиться к postgres (утилита psql)
4psql -U postgres
5
6-- команды помощи
7help
8\h -- помощь по командам SQL
9\? -- помощь по командам psql
10
11-- выход из консоли postgres
12\q
13
14-- создать базу
15CREATE DATABASE my_database;
16
17-- вывести все базы
18\l
19
20-- подключиться к базе
21\connect my_database;
22
23-- создать таблицу my_table с полями field1 (тип целочисленный, обязательное для заполнения), field2 (тип строка 255 символов)
24CREATE TABLE my_table (field1 INT NOT NULL, field2 VARCHAR(255));
25
26-- вывести все таблицы
27\d
28
29-- удалить таблицу my_table
30DROP TABLE my_table;
31
32-- внести в таблицу запись
33INSERT INTO my_table(field1, field2) VALUES(1,'Any text value');
34
35-- вывести записи
36SELECT * FROM my_table; -- все записи
37SELECT * FROM my_table WHERE field1 = 1; -- все, где field1 = 1 
38SELECT * FROM my_table WHERE field1 != 1; -- и т д
39SELECT * FROM my_table WHERE field1 > 1;
40SELECT * FROM my_table LIMIT 100; -- первые 100 записей;
41SELECT * FROM my_table LIMIT 100 OFFSET 200; -- запись с 201 по 300;
42
43-- сортировка при выводе
44SELECT * FROM my_table ORDER BY field1 ASC; -- вывести отсортировав в возрастающем порядке
45SELECT * FROM my_table ORDER BY field1 DESC; -- вывести отсортировав в убывающем порядке
46
47-- изменить запись таблицы (поле field2 строки, где field1 = 1);
48UPDATE my_table SET field2 = 'Other text value' WHERE field1 = 1;
49
50-- удаление данных
51DELETE FROM my_table; -- удалить все записи;
52DELETE FROM my_table WHERE field1 = 1; -- удалить запись где field1 = 1;
53
54
55-- ***************************************
56-- нормализация (разбиение таблиц на несколько)
57-- ***************************************
58
59-- Constraints - ограничения типов данных
60CREATE TABLE my_table (
61  field1 INT NOT NULL, -- запись обязательна
62  field2 VARCHAR(255) NOT NULL UNIQUE, -- запись должна быть уникальной
63  field3 BOOLEAN NOT NULL DEFAULT TRUE -- значение по умолчанию - true
64  ...
65);
66
67-- Первичный и внешние ключи
68-- при создании записи таблицы с отсутствующим внешним ключом выведется запись об ошибке. будут выводится ошибки и в иных случаях, когда будут нарушаться связи.
69CREATE TABLE IF NOT EXISTS my_table ( -- ключ IF NOT EXISTS проверяет, существует ли таблица.
70  field1 SERIAL INT PRIMARY KEY, -- при добавлении PRIMARY KEY поле автоматически наследует ограничения NOT NULL и UNIQUE, и создается индекс. SERIAL тип данных являющийся автоматически увеличивающимся счетчиком (аналог ключа AUTOINCREMENT в Sqlite)
71  field2 VARCHAR(255) NOT NULL UNIQUE,
72  field3 INT NOT NULL,
73  FOREIGN KEY(field3) REFERENCES other_table(field_name) -- поле ссылается на внешнюю таблицу other_table на поле field_name, которое обязательно должно быть с PRIMARY KEY
74);
75
76-- вывод данных из нескольких таблиц со связанными полями
77SELECT * FROM table_1 LEFT JOIN table_2 ON (table_2.field = table_1.field);
78
79-- алиасы, нужны для удобства. Также, при выводе наименование таблиц или полей выводится алиасом, при его наличии.
80SELECT * FROM table_1 as tab1 LEFT JOIN table_2 as tab2 ON (tab1.field = tab2.field);
81
82
83-- ***************************************
84-- Редактирование таблиц, расширенные возможности SELECT, функции
85-- ***************************************
86
87-- Добавление поля в таблицу
88ALTER TABLE table_name ADD COLUMN new_field
89BOOLEAN NOT NULL DEFAULT TRUE;
90
91-- добавление поля с автоинкрементом и primary key в таблицу
92ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;
93
94-- Удаление поля из таблицы
95ALTER TABLE table_name DROP COLUMN new_field;
96
97-- переименовать поле
98ALTER TABLE table_name RENAME old_field TO new_field;
99
100-- сменить тип данных
101ALTER TABLE table_name ALTER COLUMN any_field SET
102DATA TYPE VARCHAR(255);
103
104-- изменить значение по умолчанию
105ALTER TABLE table_name ALTER COLUMN any_field SET
106DEFAULT 'new value';
107
108-- добавить/удалить constraint NOT NULL
109ALTER TABLE table_name ALTER COLUMN any_field
110SET|DROP NOT NULL;
111
112-- переименовать таблицу
113ALTER TABLE table_name RENAME TO new_table_name;
114
115-- Расширенные возможности SELECT
116SELECT * FROM table WHERE field1 LIKE 'value'; -- field1 = 'value'
117SELECT * FROM table WHERE field1 LIKE 'val%'; -- field1 начинается с 'val'
118SELECT * FROM table WHERE field1 LIKE '%lue'; -- field1 заканчивается на 'lue'
119SELECT * FROM table WHERE field1 LIKE '%e%'; -- field1 содержит 'e'
120-- несколько условий
121SELECT * FROM table WHERE field1 = 'value' AND field2 > 'value2';
122SELECT * FROM table WHERE field1 = 'value' OR field2 > 'value2';
123
124-- вывод уникальных записей
125SELECT DISTINCT field1 FROM table;
126
127-- группирование записей
128
129SELECT field1, COUNT(field1) FROM table GROUP BY field1;
130-- сгруппирует записи таблицы table по полю field и выведет уникальные значения field и количество повторений
131
132SELECT field1, COUNT(field1) FROM table GROUP BY field1
133HAVING COUNT(field) > 3;
134-- сгруппирует записи таблицы table по полю field и выведет уникальные значения field и количество повторений, где количество повторений больше 3