Базата данни: от създаването до DROP

Накратко: CREATE DATABASE прави нова база на сървъра, а DELETE, TRUNCATE, DROP TABLE и DROP DATABASE махат съответно редове, всички редове, таблица или цялата база.

Проблем

Складът иска своя база

Магазинът отваря склад. Складът има свои таблици: рафтове, доставки, инвентаризации. Те не са част от сайта и никой не иска грешка в склада да спре поръчките. Затова складът получава своя отделна база, на същия сървър.

Досега работеше само в базата „магазин“. Сега ще видиш целия живот на една база: как се прави, как се свързваш с нея, как се пълни и как се маха.

Идеята

Един сървър, много бази

Сървърът на базите (PostgreSQL, MySQL, SQL Server) държи много бази. Всяка има свои таблици, свои потребители и права. Нова база се прави с CREATE DATABASE:

-- PostgreSQL
CREATE DATABASE warehouse;
\c warehouse          -- psql се свързва с новата база

-- MySQL
CREATE DATABASE warehouse;
USE warehouse;        -- следващите заявки са в нея

Тези команди не вървят в полетата тук, защото сайтът ползва SQLite. В SQLite няма сървър: базата е един файл. Нова база е нов файл: sqlite3 warehouse.db в терминала го прави, ако го няма. Затова SQLite няма CREATE DATABASE.

Коя база ползва полето сега? main е базата на магазина:

SQLбаза: магазин

SELECT name FROM pragma_database_list;

SELECT name FROM pragma_database_list;

▶ ИзпълниВърни заявкатаНова база

Идеята

Нова база в SQLite: ATTACH

Вътре във вече отворена връзка SQLite закача още една база с ATTACH. На компютъра пишеш име на файл (ATTACH 'warehouse.db' AS warehouse) и файлът се създава, ако го няма. В браузъра ползваме ':memory:': нова празна база в паметта, която изчезва с полето. Името след AS е името на базата, а таблиците в нея се пишат с него отпред:

SQLбаза: магазин

ATTACH ':memory:' AS warehouse;

CREATE TABLE warehouse.shelves (
  id INTEGER PRIMARY KEY,
  code TEXT NOT NULL,
  product_id INTEGER NOT NULL
);
INSERT INTO warehouse.shelves (code, product_id) VALUES ('A1', 7), ('A2', 9);

SELECT name FROM pragma_database_list;

ATTACH ':memory:' AS warehouse; CREATE TABLE warehouse.shelves ( id INTEGER PRIMARY KEY, code TEXT NOT NULL, product_id INTEGER NOT NULL ); INSERT INTO warehouse.shelves (code, product_id) VALUES ('A1', 7), ('A2', 9); SELECT name FROM pragma_database_list;

▶ ИзпълниВърни заявкатаНова база

Таблицата shelves е в базата warehouse, а не в магазина. Как точно се пишат колоните в CREATE TABLE, е в следващия урок. Тук важното е къде отиват.

Виж го

Заявка през две бази

В SQLite и MySQL една заявка може да чете от две бази наведнъж. Рафтовете са в склада, а имената и наличностите в магазина:

SQLбаза: магазин

ATTACH ':memory:' AS warehouse;
CREATE TABLE warehouse.shelves (id INTEGER PRIMARY KEY, code TEXT NOT NULL, product_id INTEGER NOT NULL);
INSERT INTO warehouse.shelves (code, product_id) VALUES ('A1', 7), ('A2', 9);

SELECT s.code, p.name, p.stock
FROM warehouse.shelves s
JOIN main.products p ON p.id = s.product_id;

ATTACH ':memory:' AS warehouse; CREATE TABLE warehouse.shelves (id INTEGER PRIMARY KEY, code TEXT NOT NULL, product_id INTEGER NOT NULL); INSERT INTO warehouse.shelves (code, product_id) VALUES ('A1', 7), ('A2', 9); SELECT s.code, p.name, p.stock FROM warehouse.shelves s JOIN main.products p ON p.id = s.product_id;

▶ ИзпълниВърни заявкатаНова база

main.products е таблицата на магазина. main. може и да се пропусне: таблица без име на база SQLite търси във временната база, после в main и чак после в закачените. В PostgreSQL това не става: заявката вижда само базата, с която е свързана. Там отделни части на една система се делят на схеми в една база (warehouse.shelves е схемата warehouse), а отделни бази са наистина отделни.

Пробвай

Базата на склада

Направи базата warehouse (в паметта), в нея таблица deliveries с колони id (първичен ключ), product_id и quantity (цели числа, задължителни) и запиши доставка: 40 бройки от продукт 12.

SQLбаза: магазин

ATTACH ':memory:' AS warehouse;

CREATE TABLE warehouse.deliveries (
  id INTEGER PRIMARY KEY
);

ATTACH ':memory:' AS warehouse; CREATE TABLE warehouse.deliveries ( id INTEGER PRIMARY KEY );

Провери▶ Изпълни

Идеята

Четири начина да махнеш

От най-малкото към най-голямото:

• DELETE FROM t WHERE … маха избраните редове. Без WHERE маха всички, но таблицата остава.
• TRUNCATE TABLE t (PostgreSQL, MySQL, SQL Server) маха всички редове наведнъж, много по-бързо от DELETE при голяма таблица. В MySQL и SQL Server връща и номерацията на id в началото, а в PostgreSQL само с TRUNCATE TABLE t RESTART IDENTITY. SQLite няма TRUNCATE: там DELETE FROM t без WHERE прави същото по бърз начин.
• DROP TABLE t маха самата таблица: редовете, колоните, индексите ѝ. След това t я няма.
• DROP DATABASE warehouse маха цялата база с всичките ѝ таблици. В SQLite това е изтриване на файла.

Тук DELETE изпразва таблицата с промоциите, а DROP TABLE я маха. Последната заявка вече няма какво да чете:

SQLбаза: магазин

CREATE TABLE promo_import (code TEXT, discount INTEGER);
INSERT INTO promo_import VALUES ('AUTUMN10', 10), ('BREAD5', 5);

DELETE FROM promo_import;
SELECT COUNT(*) AS rows_left FROM promo_import;

DROP TABLE promo_import;
SELECT COUNT(*) FROM promo_import;

CREATE TABLE promo_import (code TEXT, discount INTEGER); INSERT INTO promo_import VALUES ('AUTUMN10', 10), ('BREAD5', 5); DELETE FROM promo_import; SELECT COUNT(*) AS rows_left FROM promo_import; DROP TABLE promo_import; SELECT COUNT(*) FROM promo_import;

▶ ИзпълниВърни заявкатаНова база

По-точно, за любопитните

DROP не пита „сигурен ли си“ и не се връща с Ctrl+Z. В PostgreSQL DROP DATABASE не става, докато някой е свързан с базата, а в PostgreSQL и SQLite DROP TABLE в транзакция може да се отмени с ROLLBACK. В MySQL DROP и TRUNCATE правят COMMIT сами. Затова преди DROP на истинска база първо се прави копие (за това е урокът за истинския сайт в модула „Базата в програмата“).

Пробвай

Таблица от стар внос

Таблицата promo_import е останала от внос на промоции и вече не трябва. Махни я заедно с данните, така че скриптът да не спре, ако някой вече я е махнал.

SQLбаза: магазин

SELECT name FROM sqlite_master WHERE type = 'table';

SELECT name FROM sqlite_master WHERE type = 'table';

Провери▶ Изпълни

Упражнение

Изпразни, но остави

Таблицата cart_log записва какво клиентите слагат в количката. В началото на всеки месец тя се изпразва, но остава за новите записи. Изпразни я.

SQLбаза: магазин

SELECT * FROM cart_log;

SELECT * FROM cart_log;

Провери▶ Изпълни

Идеята

Откачане

Когато не ти трябва повече, базата се откача с DETACH. Файлът остава на диска, а таблиците ѝ вече не се виждат от тази връзка:

SQLбаза: магазин

ATTACH ':memory:' AS warehouse;
CREATE TABLE warehouse.shelves (id INTEGER PRIMARY KEY, code TEXT);
DETACH warehouse;

SELECT name FROM pragma_database_list;

ATTACH ':memory:' AS warehouse; CREATE TABLE warehouse.shelves (id INTEGER PRIMARY KEY, code TEXT); DETACH warehouse; SELECT name FROM pragma_database_list;

▶ ИзпълниВърни заявкатаНова база

Целият път на една база е: създаване (CREATE DATABASE, в SQLite нов файл или ATTACH), свързване (\c, USE, ATTACH), таблици (CREATE TABLE, следващият урок), данни (INSERT, UPDATE, DELETE), заявки (SELECT) и накрая махане (DROP TABLE, DROP DATABASE). Проектът в края на курса минава целия път с магазина.

Тест

Провери се

1. В PostgreSQL си свързан с базата shop. Как стигаш до таблиците на базата warehouse?

SELECT * FROM warehouse.shelvesОтваряш нова връзка към warehouse (в psql с \c warehouse)USE warehouse;

2. Кое маха редовете на таблицата, но я оставя празна и готова за нови?

DROP DATABASEDELETE без WHERE (или TRUNCATE в PostgreSQL и MySQL)DROP TABLE

3. Скрипт за обновяване пуска DROP TABLE promo_import, но таблицата я няма. Какво става?

Скриптът спира с грешка; DROP TABLE IF EXISTS не би спрялТаблицата се прави празнаНищо

4. Какво е базата данни в SQLite?

Папка с по един файл за таблицаЕдин файл (или база в паметта)Процес на сървър

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: Първичен и външен ключ · JOIN: две таблици в една заявка · INSERT: нови редове · DELETE: изтриване на редове
• Продължава в: Пекарната, етап 1: базата и таблиците · Пекарната, етап 6: отчетът, копието и DROP
• Задачи по темата: Таблици и правила