Свободно ръководство с отворен код. Оригиналът можете да откриете на адрес: https://github.com/aquilax/baza_danni

Лицензна информация

Произведението Практическо ръководство по управление на бази данни създадено от aquilax ползва Криейтив Комънс Признание-Некомерсиално-Споделяне на споделеното 2.5 България договор.
Базирано на следната творба:bazadanni.com.

Увод

През последните десетина години ми се е налагало да използвам няколко системи за управление на бази от данни (Paradox, dBase, MS Access, Excel, MS SQL, MySQL, Memcache, App Engine datastore, SQLite, PostreSQL дори малко Oracle). Всяка система си има своите особености и приложение но общото между тях е че улесняват работата с данни.

Една от основните задачи на програмирането е именно обработката на данни. Затова реших да събера кратко практическо ръководство за работа с база данни. По голямата част от проектите, върху които работя, са интерфейс към някакви данни.

Общо за релационните бази данни

Релационните бази данни носят това име заради връзките между елементите им, които ги правят истински полезни.

Атом

Атомът е най-малката неделима единица данни с която можем да работи. Примери за атоми са: 1, 123, А, София, Иван Петров.

Поле

Поле е мястото, където се съхраняват атомите. Полето обикновено има тип, който определя какви данни могат да се съхраняват в него. Основните типове данни на полета са:

INTEGER - цели числа;

Целите числа могат да са със знак или без знак. Числата без знак могат да са само положителни но затова пък горната им граница е 2 пъти по висока от аналогичните числа със знак; Примери за цели числа: 1, 100, -23, 33222;

FLOAT - числа с плаваща запетая (или дробни числа);

Числата със плаваща запетая са удобни за представяне на десетични дроби. ВНИМАНИЕ!: Въпреки че числата с плаваща запетая изглеждат удобни за работа с пари, избягвайте тази употреба. Точността на операциите с числа с плаваща запетая не е гарантирана а това може да ви докара много главоболия. Практиката е за пари да се използват цели числа (които да се интерпретират като такива с фиксирана точка), иначе казано вместо да записвате 2.15 за сумата от 2 лева и 15 стотинки, използвайте цяло число и запишете 215 стотинки.

Примери за числа с плаваща запетая са: 1.00, 3.145, 8.22 (забележете че десетичната запетая се изписва като точка, въпреки че по БДС десетичният разделител в България е символа за запетая);

CHAR - текст с фиксирана дължина

Текста с фиксирана широчина е такъв на който знаете предварително дължината.

Примери за такива полета са MD5 сумите, Единния граждански номер, данъчния номер;

Принципно полетата с фиксирана широчина се управляват по-ефективно от СУБД, отколкото тези с променлива дължина. Все пак не прекалявайте. Приложението на фиксираната дължина е ограничено.

VARCHAR - текст с променлива дължина;

Най-често използвания тип за съхранение на текстови данни. Полето с променлива дължина има размер, който указва максималния размер на текста, който може да се събере в него. Ще се запитате тогава какво му е променливото на това поле. Променлива е дължината, която СУБД заделя за данните при физическия запис.

TEXT - дълъг текст;

Текстовото поле се използва за съхранение на голямо количество текст. Казвам голямо а не неограничено количество, защото обикновено има ограничение за максималния размер на текста и то зависи от конкретната база. Текстовото поле често се нарича текстов блоб (blob = BLOB binary large object). Разликата с CHAR и VARCHAR полетата е че текстовите полета обикновено се съхраняват във физически отделни файлове и не поддържат всички функции за обработка т.е. най-често се извличат и съхраняват като цели обекти.

DATE - дата;

Поле за дата. Различните СУБД го интерпретират по различен начин (примерно MS SQL и MS Access според регионалните настройки, докато PostgreSQL използва японския запис YYYY-MM-DD).

TIMESTAMP - дата и час;

Поле за време час (включително и милисекунди понякога). Много полезен тип, обикновено се използва за означаване на момент на добавяне или промяна на запис. Обикновено се преобразува лесно до Unix timestamp, който е удобен за програмна обработка.

Различните СУБД (Система за управление на база данни) поддържат много повече типове данни. Някой от тях екзотични но безспорно полезни. Примерно масиви, координати, IP адреси и.т.н. Тези допълнителни типове и прилежащите функции за работа с тях могат да се окажат решаващ фактор при избора на система.

Колона

Колона в теорията на БД е поредица поредица от един тип поле. Можете да си го представите като колона в Excel. Колоната съдържа един тип данни, като типа се определя от типа на полето.

Ред

Ред или също така запис е поредица от различни типове полета със стойностите им. Например "Иван Петров, София, 02 222 222 22" е ред за лицето Иван Пертов, живущ в София с телефонен номер, 02 222 222 22. Този ред се състои от три полета: име, град, телефонен номер.

Таблица

Таблица е списък от редове с една и съща структура обединени в таблица. Ако използваме аналогията с Excel, таблицата е един лист от електронната таблица. Таблицата е основна функционална единица в релационните бази данни.

База данни

Логическата съвкупност от таблици се организира в база данни. Обикновено таблиците в една БД имат пряка или косвена връзка помежду си макар че това не е задължително. Базата данни е пясъчникът, където строите замъците си и се борите за чисти и точни данни. Базата може да е изолирана или да ви позволява достъп и до други бази в зависимост от СУБД.

SQL

SQL е един от най-широко използваните езици. Това не е толкова учудващо, тъй като SQL е сравнително лесен. Основната му функционалност се реализира от четири команди, като в болшинството от случаите се използва една от тях: SELECT. Синтаксиса на SQL варира в различните БД но основата му е стандартизирана, при това цели седем пъти досега. SQL командите завършват със символа ";".

CREATE

CREATE е командата за създаване на обекти в SQL. Обектите най-често са таблици но могат да бъдат сторнати процедури (stored procedure), вюта (view), тригери, генератори и.т.н. Създаване на таблица:

CREATE TABLE table_name (
  field1_name field1_type,
  field2_name field2_type
);

Този израз създава таблица table_name със две колони: field1_name от тип field1_type и field2_name от тип field2_type.

Малко по-реален пример:

CREATE TABLE user (
  id integer,
  username varchar(30),
);

DROP

DROP унищожава обект а синтаксисът му е обезпокоително прост:

DROP TABLE user;

Командата унищожава таблица user и ви дава пълно право да започнете да се тревожите за архивиране не данните.

INSERT

INSERT е командата с най-лошият синтаксис в SQL а той е:

INSERT INTO user (id, name) VALUES (1, "aquilax");

Виждате ли къде е проблема? имената на колоните и техните стойности са в различни списъци. Това не е такъв проблем в "тесни" таблици но ако имате над 5 колони и някой от тях са текстови, ще усетите неудобството в пълната му сила. Разбира се това се проявява само когато, пишете заявките си сами.

MySQL предлага следния удобен синтаксис за INSERT:

INSERT INTO user SET id = 1, name = "aquilax";

За съжаление този синтаксис не е стандартен и доколкото знам другите СУБД не го поддържат.

NoSQL

Упражнения

Упражнение 1 - Електронното тефтерче

Целта на първото упражнение е да създадем електронно тефтерче. Преди появата мобилните телефони, всички си записвахме телефоните а и адресите на познати и приятели в тефтерчета които често бяха азбучно разграфени. Целта ни е да създадем SQL версия на такова тефтерче, в което да добавяме информация за нашите приятели и познати.

Да определим първо какви данни ще записваме за всеки човек:

Име - за да знаем за кой става въпрос;
Адрес - адресът на който живее;
Телефонен номер;
Категория - дали човекът е познат приятел роднина и.т.н.

Изглежда че можем да поберем всичко в следната таблица:

CREATE TABLE chovek (
  name varchar(10),
  address varchar(30),
  phone varchar(15),
  category varchar(15)
);

Създаваме таблицата и можем да добавим няколко човека в нея. Можем да не указваме колоните на таблицата за INSERT ако спазваме последователността на колоните и укажем стойности за всяка колона.

INSERT INTO chovek VALUES ("Иван", "София, Красно село", "02 123 123", "Приятел");
INSERT INTO chovek VALUES ("Петкан", "Остров тимбукту, под палма 3", "", "Приказен герой");

Петкан няма телефон затова подаваме празен стринг "".

INSERT INTO chovek VALUES ("Пипилота Виктуалия Транспаранта Ментолка Ефраимова - Дългото чорапче", "Вила вилекула", NULL, "Приказен герой");

Пипи дългото чорапче е интересен случай. Първо името и е много дълго цели 69 символа при 10 указани в схемата на таблицата. Някой СУБД, като SQLite или MySQL ще позволят да въведем записа, независимо че надхвърля ограничението от 10 символа, докато други като PostgreSQL ще откажат. Правилният подход в случая е да променим типа на полето за име и да увеличим размерът му.

ALTER TABLE chovek
ALTER COLUMN name TYPE varchar(100);

Синтаксисът на ALTER се различава в зависимост от СУБД.

Второто интересно нещо в записа на Пипи е ключовата дума NULL. NULL се използва вместо нищо. То не е равно на нула, NULL е равно на нищо така че ако искаме да оставим някое поле празно, можем да използваме NULL като стойност (освен ако в дефиницията на полето не е указано изрично че то не може да е NULL).

Получаваме следната таблица:

name	address	phone	category
Иван	София, Красно село	02 123 123	Приятел
Петкан	Остров тимбукту, под палма 3		Приказен герой
Пипилота Виктуалия Транспаранта Ментолка Ефраимова - Дългото чорапче	Вила вилекула		Приказен герой

Ако искаме да видим кой е телефонът на Иван можем да използваме следната заявка:

SELECT phone FROM chovek WHERE name = "Иван";

която генерира подобен резултат:

phone     
----------
02 123 123

За списък с адресите на всички хора с име започващо с буквата П, можем да използваме:

SELECT address FROM chovek WHERE name LIKE "П%";

и резултатът:

address                                           
--------------------------------------------------
Остров тимбукту, под палма 3
Вила вилекула

Да добавим още един Иван. Този път роднина от Пловдив:

INSERT INTO chovek VALUES ("Иван", "Пловдив, Голямо тепе", "032 32 32 32", "Роднина");

Вече имаме двама човека с името "Иван" в таблицата, ако решим да обновим единия, няма да можем да използваме само името като ключ. Примерно:

UPDATE chovek SET address = "Пловдив, Средно тепе" WHERE name="Иван" AND category="Роднина";

Това изброяване често е дълго и досадно а в някой случаи и то не помага за избора на конкретен елемент. Примерно дядо и внук с еднакви имена живеещи на един и същ адрес. Затова златното правило е винаги да имаме първичен ключ в таблицата. Случаите които първичният ключ пречи са много малко и са по-скоро изключение.

Да започнем отначало с таблицата. Първо трябва да се отървем от старата таблица:

DROP TABLE chovek

След това добавяме първичен ключ в схемата. По традиция името първичния ключ е id или комбинация от името на таблицата плюс _id.

CREATE TABLE chovek (
  id integer NOT NULL AUTO_INCREMENT,
  name varchar(100),
  address varchar(30),
  phone varchar(15),
  category varchar(15)
);

Това е синтаксисът за MySQL. В PostgreSQL можете да използвате типа serial, който автоматично създава генератор а в SQLite формата е:

id INTEGER PRIMARY KEY AUTOINCREMENT

Накрая добавяме отново данните:

INSERT INTO chovek VALUES (NULL, "Иван", "София, Красно село", "02 123 123", "Приятел");
INSERT INTO chovek VALUES (NULL, "Петкан", "Остров тимбукту, под палма 3", "", "Приказен герой");
INSERT INTO chovek VALUES (NULL, "Пипилота Виктуалия Транспаранта Ментолка Ефраимова - Дългото чорапче", "Вила вилекула", NULL, "Приказен герой");
INSERT INTO chovek VALUES (NULL, "Иван", "Пловдив, Голямо тепе", "032 32 32 32", "Роднина");
UPDATE chovek SET address = "Пловдив, Средно тепе" WHERE name="Иван" AND category="Роднина";

и получаваме следната таблица:

SELECT * FROM chovek;

id	name	address	phone	category
1	Иван	София, Красно село	02 123 123	Приятел
2	Петкан	Остров тимбукту, под палма 3		Приказен герой
3	Пипилота Виктуалия Транспаранта Ментолка Ефраимова - Дългото чорапче	Вила вилекула		Приказен герой
4	Иван	Пловдив, Средно тепе	032 32 32 32	Роднина

Сега можем да изтрием много лесно Иван от София, който вече не ни е приятел:

DELETE FROM chovek WHERE id = 1;

Звучи малко грубо но това е само пример.

Упражнение 2 - Заиграване с данните

Вече имаме някакви данни в базата. Време е да се научим как да получаваме интересна информация от данните. Примерно по колко човека познаваме от дадена категория. Това може да се постигне по два начина:

Вариант 1: Групиране и агрегация;

SELECT category, count(*) AS cnt 
FROM chovek 
GROUP BY category;

category                     cnt       
---------------------------  ----------
Приказен герой               2         
Роднина                      1

Заявката се "чете" по следния начин:

Избери категория и броя редове за всяка категория от таблица chovek като групираш данните по категория.

SQL е много четим език, особено ако подбираме правилно имената на таблиците и колоните.

Интересното в резултата е че познаваме двама приказни герои и един роднина т.е. имаме социални проблеми и нужда от специализирана помощ.

Вариант 2: Вложени заявки (вложени селекти);

В този случай използването на вложени заявки е погрешно и ще забави излишно заявката но за пълнота на изложението ще го покажа тук:

SELECT 
  c1.category, 
  (SELECT count(*) FROM chovek c2 WHERE c1.category = c2.category) AS cnt 
FROM chovek c1 
GROUP BY c1.category;

category                     cnt       
---------------------------  ----------
Приказен герой               2         
Роднина                      1

Доста по дълго и неприятно но все пак прочита е:

Избери категория и за всяка категория преброй записите в таблицата за които имената на категориите са еднакви.

c1 и c2 се наричат псевдоними на таблиците (alias) а cnt се нарича псевдоним на колона (отново alias);

Резултатът от двете заявки е един и същ но е различен начинът по който те работят. Първата се изпълнява на един пас докато втората изпълнява външната заявка и за всеки резултатен ред от нея се изпълнява вътрешната заявка. Това е излишно в този случай и заявката освен по-дълга е и по-неефективна. Все пак има случаи в които вложените заявки ще ви бъдат от голяма полза.

Декомпозиция и нормализация

Нашата малка табличка е малка приятна и лесна за употреба. Но дали е достатъчна. Преди десет години повечето хора имаха най-много един телефонен номер. Днес вече не е така, Повечето ни контакти са с по няколко номера а и все по рядко използват телефонът за разговори.

Трябва да намерим начин да отразим реалността в нашето тефтерче. Интуитивният подход е да добавим още колони в таблицата. Можем да сложим примерно 3 колони за телефонни номера, 2 колони за e-mail адреси, една за IM комуникатор ... и всичко ще е идеално, докато не се запознаем с някой с 4 телефона или 3 e-mail адреса.

Първият проблем е че ще трябва често да променяме таблицата. Това освен неудобно може да бъде бавно или да "счупи" евентуален софтуер, който използва нашата база. Вторият проблем са празните полета. В общия случай приятелите ни може да имат по един телефон но ние все пак ще заделяне по три полета за телефон за всеки човек.

Ще използваме друг подход. Ще декомпозираме нашите данни за да направим базата по-гъвкава и разширяема.

Декомпозиция означава да вземем нашата композиция (в случая таблицата chovek) и да я раздробим на изграждащите я части. Частите в нашия случай са колоните:

id се използва за указател на реда;
name - името на човека което е кажи-речи уникално за всеки;
address - адресът е като името и е уникален за всеки в общия случай. Ще извадим града от адреса, това ще ни позволи да изготвяме лесно няколко интересни справки;
phone - телефонът също е уникален но в случая ще го заменим със средство за комуникация и ще го изведем в отделна таблица;
category - категорията ще заменим с TAG-ове; Така ще разрешим проблемът със колега, който/която в същото време ни е приятел т.е. проблемът с хората които попадат в повече от една категория;

Диаграма на светлото бъдеще:

+----------+       +------+     +-----------------+
| chovek   |       | city |     | contact         |
|----------|       |------|     |-----------------|
| id       |       | id   |     | id              |
| city_id  |       | name |     | contact_type_id |
| name     |       +------+     | chovek_id       |
| address  |                    | val             |
| created  |                    | created         |
+----------+                    +-----------------+

+------------+     +------+     +--------------+
| chovek_tag |     | tag  |     | contact_type |
|------------|     |------|     |--------------|
| id         |     | id   |     | id           |
| chovek_id  |     | name |     | name         |
| tag_id     |     +------+     +--------------+
+------------+

Изглежда чудовищно на фона на удобната ни таблица и донякъде е така. В случая заменяме удобство за гъвкавост. Следват малко разяснения за таблиците:

chovek - тук сме запазили името и адреса на човека, тъй като са сравнително уникални за всеки. Добавена е колоната city_id, която ще е нашата връзка към градовете а също така и една колона created, в която ще си записваме кога сме добавили човека;
city - таблица за населените места (въпреки че се казва city);
tag - тук ще се помещават различните категории хора: познати, приятели, колеги, зомбита;
contact_type - различните типове контакти, които ще записваме за хората: телефон, e-mail, jabber;
contact - това ще е самият контакт. Има полета за връзка към типа контакт и към човека, поле за стойност и още едно за датата и часа в които сме добавили контакта;
chovek_tag - това е свързочна таблица за "много към много връзка". Тя ще свързва хората с категориите;

Схемите за създаване на таблиците (в случая SQLite3) са:

CREATE TABLE chovek (
  id INTEGER PRIMARY KEY AUTO INCREMENT,
  city_id INTEGER NOT NULL,
  name VARCHAR(100),
  address VARCHAR(30),
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE city (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(30)
);

CREATE TABLE contact (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  contact_type_id INTEGER NOT NULL,
  chovek_id INTEGER NOT NULL,
  val VARCHAR(100),
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE chovek_tag (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  chovek_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL
);

CREATE TABLE tag (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(30)
);

CREATE TABLE contact_type(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(30)
);

И да налеем малко данни:

Два града: София и Пловдив

INSERT INTO city VALUES (NULL, "София");
INSERT INTO city VALUES (NULL, "Пловдив");

SELECT * FROM city;

id	name
1	София
2	Пловдив

Рецепти

Приоритизиране на резултатите от UNIONG

Има случаи в които трябва да използваме UNION за да обединим резултатите от няколко заявки. Подобен пример е търсене на текст в няколко таблици примерно:

SELECT id, name FROM table1 WHERE name LIKE "%search%"
UNION
SELECT id, name FROM table2 WHERE name LIKE "%search%"
UNION
SELECT id, name FROM table3 WHERE name LIKE "%search%"

В общия случай данните, които ще получим ще са подредени в реда, в който сме подредили заявките но това не е гарантирано. Ако искаме да върнем резултатите в ред според таблицата, от която са, можем да използваме следния трик:

SELECT id, name FROM (
  SELECT id, name, 1 AS ordr FROM table1 WHERE name LIKE "%search%"
  UNION
  SELECT id, name, 3 AS ordr FROM table2 WHERE name LIKE "%search%"
  UNION
  SELECT id, name, 2 AS ordr FROM table3 WHERE name LIKE "%search%"
) AS t 
ORDER BY ordr;

Добавяме една "фалшива" колона в резултата, която използваме във външната заявка за сортиране. В случая резултатите ще се подредят в реда първа, трета, втора таблица.

Частично възстановяване на данни от архив

Понякога се случва да напишем заявка от рода на:

UPDATE posts 
SET user_id=1;

докато всъщност искаме да кажем:

UPDATE posts 
SET user_id=1
WHERE id = 155633;

В такива случаи се налага да възстановим данните от архив но докато се ровим из архивите на базата в таблицата продължават да се трупат записи, които са верни. Целта ни е да възстановим колоната user_id в таблица user от архивно копие.

Първо възстановяваме таблицата от архива с ново име, примерно user_bak и наливаме архивните данни. Остава само да обновим колоната със следната заявка:

UPDATE 
user u, user_bak ub
SET u.user_id = ub.user_id
WHERE u.id = ub.id

Накрая изтриваме архивната таблица:

DROP TABLE user_bak;

и отиваме да си премерим пулса.

Тагове

Класификацията с тагове е удобна в случаите, когато един елемент се определя от повече от една характеристики. Реализацията (обикновено) е следната:

CREATE TABLE entry(
  id integer NOT NULL AUTO_INCREMENT,
  entry_name VARCHAR(15),
  PRIMARY KEY (id)
);

CREATE TABLE tag(
  id integer NOT NULL AUTO_INCREMENT,
  tag_name VARCHAR(10),
  PRIMARY KEY (id)
);

CREATE TABLE entry_tag(
  entry_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL
);

Да добавим и уникален индекс за entry_tag за да няма дублиране на думи за един и същ елемент както и на tag.name за да не се дублират самите ключови думи:

CREATE UNIQUE INDEX entry_tag_uniq
  ON entry_tag (entry_id, tag_id);
CREATE UNIQUE INDEX tag_uniq
  ON tag (tag_name);

Малко тестови данни:

INSERT INTO entry VALUES (NULL, "круша"), (NULL, "ябълка"), (NULL, "репичка"), (NULL, "зеле");
INSERT INTO tag  VALUES (NULL, "плод"), (NULL, "зеленчук"), (NULL, "червено"), (NULL, "зелено"), (NULL, "жълто");

И резултатът:

SELECT * FROM tag;
+----+------------------+
| id | tag_name         |
+----+------------------+
|  1 | плод             |
|  2 | зеленчук         |
|  3 | червено          |
|  4 | зелено           |
|  5 | жълто            |
+----+------------------+

SELECT * FROM entry;
+----+----------------+
| id | entry_name     |
+----+----------------+
|  1 | круша          |
|  2 | ябълка         |
|  3 | репичка        |
|  4 | зеле           |
+----+----------------+

Закачане на елемент към ключова дума

Примерно искаме да изкажем следните твърдения:

INSERT INTO entry_tag VALUES (2, 1), (2, 3); /* ябълката е червена */
INSERT INTO entry_tag VALUES (1, 1), (1, 5); /* крушата е плод и е жълта */
INSERT INTO entry_tag VALUES (3, 2), (3, 3); /* репичката е зеленчук и е червена */
INSERT INTO entry_tag VALUES (4, 2), (4, 4); /* зелето е зеленчук и е зелено*/

И изпълненият с числа резултат:

SELECT * FROM entry_tag;
+----------+--------+
| entry_id | tag_id |
+----------+--------+
|        1 |      1 |
|        1 |      5 |
|        2 |      1 |
|        2 |      3 |
|        3 |      2 |
|        3 |      3 |
|        4 |      2 |
|        4 |      4 |
+----------+--------+

Всичко това е много хубаво но не казва нищо за зелето и крушите на случайните минувачи.

Ключови думи към елемент

Да видим какво знаем за крушата (entry.id = 1)

SELECT tag_name 
FROM entry_tag et 
JOIN tag t ON t.id = et.tag_id 
WHERE et.entry_id = 1;

tag_name

плод жълто

Елементи и ключови думи към тях

В случая за MySQL заявката изглежда така:

SELECT e.entry_name, GROUP_CONCAT(t.tag_name) AS tags
FROM entry e
JOIN entry_tag et ON e.id = et.entry_id
JOIN tag t ON t.id = et.tag_id
GROUP BY e.entry_name
ORDER BY e.entry_name

и резултатът е:

+----------------+---------------------------------+
| entry_name     | tags                            |
+----------------+---------------------------------+
| зеле           | зеленчук,зелено                 |
| круша          | плод,жълто                      |
| репичка        | зеленчук,червено                |
| ябълка         | плод,червено                    |
+----------------+---------------------------------+

В PostgreSQL GROUP_CONCAT може да се замести с array_to_string(array_agg(arr), ',') или да си напишете собствена версия на функцията;

Елементи по ключова дума

Това е лесно. Примерно всичко червено:

SELECT e.entry_name 
FROM entry_tag et
JOIN entry e ON e.id = et.entry_id
WHERE et.tag_id = 3;

+----------------+
| entry_name     |
+----------------+
| ябълка         |
| репичка        |
+----------------+

Брой елементи за ключова дума

SELECT t.tag_name, count(*) AS cntr
FROM tag t
JOIN entry_tag et ON et.tag_id = t.id
GROUP BY t.tag_name
ORDER BY t.tag_name;

+------------------+------+
| tag_name         | cntr |
+------------------+------+
| жълто            |    1 |
| зелено           |    1 |
| зеленчук         |    2 |
| плод             |    2 |
| червено          |    2 |
+------------------+------+

Несвързани ключови думи

Да добавим несвързана ключова дума:

INSERT INTO tag VALUES (NULL, "локомотив");

Случва се ключови думи да останат несвързани към елементи. Ако искаме да разберем кои са те можем да използваме следната заявка:

SELECT tag_name
FROM tag t
LEFT JOIN entry_tag et ON t.id = et.tag_id
WHERE et.tag_id IS NULL

+--------------------+
| tag_name           |
+--------------------+
| локомотив          |
+--------------------+

Преместване на таблица в различна схема в PostgreSQL

Рядко се налага, но когато се наложи е полезно да знаем как става:

ALTER TABLE table_name SET SCHEMA schema_name;

Вземане на id последния добавен ред в таблица

Това е фундаментална операция при работа с БД. Повечето СУБД имат собствени механизми за получаване на идентификатора но има няколко варианта които (би трябвало) да работят навсякъде. Приемаме че имаме таблица с колона id, със нарастваща стойност:

SELECT MAX(id) FROM table;

SELECT id FROM table ORDER BY id DESC LIMIT 1;

Практическо ръководство по управление на бази данни

http://plus.google.com/103587746021296602888

2011-08-07