Упражнение 43: управление SQL

Оригинал: Exercise 43: SQL Administration

Переводчик: Феликс Ли

Лицензия: CC BY-NC-SA 4.0

Гордо использует Google Translate

Слово управление переопределяется в контексте баз данных. Это может означать "обеспечение того, чтобы сервер PostgreSQL продолжал работать", или "изменение и миграция таблиц для новых программ". В этом упражнении мы рассмотрим простые способы изменения и миграции схем. Полное управление сервером баз данных выходит за рамки данного руководства.

Удаление и изменение таблиц

Вы уже встречались с DROP TABLE, как способом удаления таблицы. Я покажу вам ещё один способ использования этого запроса, а также как использовать ALTER TABLE для добавления или удаления столбцов из таблицы.

/* Удаляем таблицу только если она существует. */
DROP TABLE IF EXISTS person;

/* Создаём снова, чтобы работать с ней. */
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

/* Переименовываем таблицу в peoples. */
ALTER TABLE person RENAME TO peoples;

/* Добавляем новый столбец hatred к peoples. */
ALTER TABLE peoples ADD COLUMN hatred INTEGER;

/* Переименовываем peoples обратно в person. */
ALTER TABLE peoples RENAME TO person;

.schema person

/* Мы больше не нуждаемся в этой таблице. */
DROP TABLE person;
```Я делаю некоторые ложные изменения в таблице, чтобы продемонстрировать эти команды, но использование `ALTER TABLE` и `DROP TABLE` — это всё, что можно сделать с помощью этих запросов в SQLite3. Я объясню это, чтобы вы понимали, что происходит:`ex21.sql:2`

Используем модификатор `IF EXISTS`, чтобы удалять таблицу только при её существовании. Это предотвращает ошибки при выполнении .sql скрипта на новой базе данных без таблицы.

`ex21.sql:5`

Просто создаём таблицу заново, чтобы работать с ней.

`ex21.sql:13`

Используем `ALTER TABLE`, чтобы переименовать таблицу в `peoples`.

`ex21.sql:16`

Добавляем новый столбец `hatred` к переименованной таблице `peoples`, который является типом `INTEGER`.

`ex21.sql:19`

Переименовываем `peoples` обратно в `person`, так как это глупое название для таблицы.

`ex21.sql:21`

Делаем схему `person`, чтобы вы могли видеть, что теперь у неё есть новый столбец `hatred`.

`ex21.sql:24`

После этого упражнения удаляем эту таблицу, чтобы очистить место.

## Миграция и эволюция данных

Давайте применим некоторые из техник, которые вы узнали. Я попрошу вас выбрать вашу базу данных и "эволюционировать" её в различные формы. Убедитесь, что вы хорошо понимаете предыдущие упражнения, и что ваш `code.sql` работает правильно. Если вы ещё не завершили все эти задачи, вернитесь и закончите всё это.

Если у вас возникают проблемы с выполнением этих шагов, обратитесь за помощью к своему преподавателю или коллегам. Чтобы убедиться, что вы работаете в правильном состоянии при выполнении этого упражнения, когда вы запустите ваш `code.sql`, вы должны иметь возможность запустить `.schema`, следуя примеру:```sql
$ sqlite3 ex13.db < code.sql
$ sqlite3 ex13.db ".schema"
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);
CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);
CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER,
    dob DATETIME
);

Убедитесь, что ваши таблицы совпадают с моими таблицами. Если это не так, вернитесь назад и удалите любые команды, которые выполнялись после последнего упражнения, используя ALTER TABLE.

Часто задаваемые вопросы

Задачей является выполнение следующего списка изменений базы данных:+ Добавьте столбец dead в таблицу person, как в таблице pets.

• Добавьте столбец phone_number в таблицу person.
• Добавьте столбец salary в таблицу person, который имеет тип FLOAT.
• Добавьте столбец dob (Дата рождения) в таблицы person и pet, который имеет тип DATETIME.
• Добавьте столбец purchased_on в таблицу person_pet, который имеет тип DATETIME.
• Добавьте столбец parent в таблицу pet, который имеет тип INTEGER и хранит id родителя.
• Используйте оператор UPDATE, чтобы обновить существующие записи базы данных новыми данными. Не забудьте обновить столбец purchased_on в таблице person_pet, указывая, когда человек приобрёл данного питомца.
• Добавьте ещё четырёх людей и пять питомцев, а также установите отношения между ними, указав, какой питомец является родителем. В последней части помните, что вам нужно получить id родителя и установить его в столбце parent.
• Напишите запрос, чтобы найти всех питомцев, приобретённых после 2004 года, вместе со всеми их владельцами. Ключевой момент заключается в использовании столбца purchased_on для отображения связи между таблицами person_pet, pet и parent.
• Напишите запрос, чтобы найти родителей для заданного питомца. Опять же, обратитесь к столбцу pet.parent для реализации этого. На самом деле это очень просто, поэтому не усложняйте задачу.
• Обновите свой файл code.sql, где вы уже добавили все необходимые изменения, чтобы он использовал синтаксис DROP TABLE IF EXISTS.+ Используйте оператор ALTER TABLE, чтобы добавить столбцы height и weight в таблицу person, и добавьте эти изменения в ваш файл code.sql.
• Выполните новый скрипт code.sql, чтобы сбросить базу данных, и убедитесь, что нет ошибок.Вы должны создать файл ex13.sql, содержащий все новые изменения. Затем используйте code.sql для сброса базы данных, выполните ex13.sql для изменения базы данных и выполните SELECT запросы, чтобы подтвердить, что были сделаны правильные изменения.## Глубокое изучение

Продолжите чтение документации по DROP TABLE и ALTER TABLE, а затем посетите страницу с языковыми конструкциями SQLite3 и прочтите остальные CREATE и DROP запросы в документации.