Что такое код drop database


Содержание

Что такое код drop database

Используя запрос DROP можно удалить таблицы (TABLE), индексы (INDEX) и базы данных (DATABASE).

DROP TABLE

DROP TABLE, применяемый в базе данных Oracle.
Обычно с таблицей в базе данных связано несколько объектов, например индекс, создаваемый первичным ключом, или ограничение UNIQUE, налагаемое на столбцы таблицы.
При удалении таблицы Oracle автоматически удаляет и любой связанный с ней индекс. Для удаления таблицы из БД необходимо выполнить команду DROP TABLE:

DROP TABLE Пример 1
Удаление таблицы:

Однако удалить таблицу не всегда столь просто. В любой момент мы можем создать таблицу с ограничениями целостности. Ограничение целостности (Integrityconstraint ) – это правило, устанавливаемое для таблицы и ограничивающее тип данных, которые можно вводить в эту таблицу. Если попытаться удалить таблицу с ограничениями целостности, возвращается сообщение об ошибке следующего вида: «Unique/primary keys in table referenced by foreign keys» (на уникальные/первичные ключи таблицы ссылаются внешние ключи).

Когда существуют ограничения для других таблиц, на которые ссылается удаляемая таблица, можно пользоваться каскадной конструкцией CASCADE CONSTRAINTS:
DROP TABLE. Пример 2
Удаление таблицы с ограничениями целостности:

DROP TABLE table CASCADE CONSTRAINTS;

DROP TABLE, применяемый в mySQL
DROP TABLE. Пример 3

Для удаления таблицы также используется запрос:

DROP TABLE. Пример 4

В случае, если необходимо установить проверку на существование таблицы при удалении (если существует удалить таблицу) запрос принимает следующий вид:

DROP TABLE IF EXISTS table;

Данный запрос будет выполнен в том случае, если удаляемая таблица существует в базе данных.

DROP INDEX
Данный запрос DROP INDEX используется для удаления индексов в таблице.

DROP INDEX, применяемый в базе данных Oracle:
Когда индекс в базе данных больше не нужен, разработчик может удалить его командой DROP INDEX. После удаления индекса эффективность поиска с использованием столбца или столбцов, ограниченных индексом, больше не повышается и упоминание об индексе исчезает из словаря данных. Индекс, применяемый для первичного ключа, удалить нельзя.

Синтаксис оператора DROP INDEX одинаков для удаления индекса любого типа (уникальности, битовой карты или В-дерева). Чтобы каким-то образом улучшить индекс, нужно сначала удалить его, а потом создать новый.

DROP INDEX. Пример 1

DROP INDEX my_index;

DROP INDEX, применяемый в mySQL:

DROP INDEX. Пример 2
Для удаления индексов (INDEX) используется запрос:

DROP INDEX my_index ON table;

Данный запрос удаляет индексы, указанные в my_index из таблицы table, но она не работает в версиях MySQL до 3.22. В версиях 3.22 и более поздних используется команда:

ALTER TABLE table_name DROP INDEX index_name;

DROP DATABASE

DROP DATABASE. Пример 1

DROP DATABASE database;

Запрос DROP DATABASE удаляет базу данных database.

TRUNCATE TABLE

Запрос TRUNCATE TABLE используется для того, чтобы удалить данные внутри таблицы, тем самым не затрагивая саму таблицу. В нужном случае TRUNCATE TABLE может быть очень полезен.

TRUNCATE TABLE, примеры использования TRUNCATE TABLE
TRUNCATE TABLE. Пример 1

TRUNCATE TABLE table;

DROP SEQUENCE
DROP SEQUENCE используется для удаления последовательности.

DROP SEQUENCE. Пример 1

DROP SEQUENCE sequence_name;

DROP SYNONYM
DROP SYNONYM используется для удаления синонимов.

DROP SYNONYM. Пример 1

DROP SYNONYM synonym_name;

Для удаления общих синонимов необходимо воспользоваться командой DROP PUBLIC SYNONYM.

DROP SYNONYM. Пример 2

DROP PUBLIC SYNONYM synonym_name;

Вы должны войти, чтобы оставить комментарий.

DROP DATABASE

Use the DROP DATABASE command to delete the target database and, if RMAN is connected to a recovery catalog, unregister it. RMAN removes all datafiles, online redo logs, and control files belonging to the target database. By default, RMAN prompts for confirmation.

Execute this command only at the RMAN prompt. You must be connected to a target database. The target database must be mounted exclusive and not open, and started in RESTRICT mode.

Syntax Element Description
INCLUDING BACKUPS Deletes backup sets, proxy copies, image copies, and archived redo logs associated with the target database from all configured device types.

Note: If you have been using a recovery catalog but run RMAN in NOCATALOG mode when you drop the database, then RMAN will not delete any backups which are known to the recovery catalog but no longer exist in the target database control file.

NOPROMPT Does not prompt for confirmation before deleting the database.

Example 2-69 Deleting a Database

In this example, you want to delete a test database called test1 that is registered in the recovery catalog. You start the RMAN client, connect to database test1 as TARGET , and connect to the recovery catalog. You then run the following commands to delete the target database files, as well as all backups, copies, and archived logs associated with the database:

DROP DATABASE

English-Russian SQL Server dictionary . 2013 .

Смотреть что такое «DROP DATABASE» в других словарях:

Database Console Commands (Transact-SQL) — The Database Console Commands (DBCC) are a series of statements in Transact SQL programming language to check the physical and logical consistency of a Microsoft SQL Server database.[1] These commands are also used to fix existing issues.[1] They … Wikipedia

Drop It Like It’s Hot — Single by Snoop Dogg featuring Pharrell from the album R G (Rhythm Gangsta): The Masterpiece … Wikipedia

Drop Zone (film) — Drop Zone Theatrical release poster Directed by John Badham Produced by … Wikipedia

Drop Dead Diva — Inter title Genre Comedy drama Fantasy Legal Drama Created by … Wikipedia

Drop — may refer to: Contents 1 General use 2 Sports 3 Computers and technology … Wikipedia

Drop Dead Fred — Theatrical Release Poster Directed by Ate de Jong Produced by … Wikipedia

Drop Dead Diva (season 2) — Drop Dead Diva Season 2 Country of origin USA No. of episodes 13 Broadcast Original channel Lifetime … Wikipedia

Drop Squad — Directed by Dav >Wikipedia

Drop the World — Single by Lil Wayne featuring Eminem from the album Rebirth R … Wikipedia

Drop the Dead Donkey — Format Satirical sitcom Created by Andy Hamilton Guy Jenkin … Wikipedia

Drop Dead Diva (season 1) — Drop Dead Diva Season 1 Country of origin USA No. of episodes 13 Broadcast Original channel Lifetime … Wikipedia

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Часть 6.3: Команда DROP в SQLite3. DDL оператор DROP в SQLite

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Мы с тобой уже познакомились с операторами DDL в SQLite3 и рассмотрели особенности двух DDL команд: оператор CREATE в SQLite3 и команда ALTER в SQLite3. Давай теперь рассмотрим последнюю команду из группы DDL: команда DROP. Команда DROP или оператор DROP в SQLite3 позволяет удалять сущности, хочу обратить твое внимание на слово сущности, так как команда DROP в SQLite удаляет не только таблицы, но и триггеры, VIEW, индексы. Давай разбираться с оператором DROP.

Команда DROP в SQLite3. DDL оператор DROP в SQLite

Общая информация о команде DROP в SQLite3

Команда DROP в SQLite3 позволяет нам удалять объекты базы данных. При помощи команды DROP в SQLite3 мы можем удалить: таблицу, индекс, триггер, представление. Как и в случае с командой ALTER, команду DROP мы рассмотрим только в этой части и больше не будем уделять ей какого-то особого внимания.

Команда DROP TABLE в SQLite3. Оператор DROP TABLE в SQLite3.

Команда DROP TABLE в SQLite3 позволяет удалять таблицы из нашей базы данных. Синтаксис оператора DROP TABLE в SQLite3 прост до безобразия.

Синтаксис команды DROP TABLE в SQLite3. Синтаксис оператора DROP TABLE в SQLite3

Приведем пример удаления таблицы (команды DROP TABLEв SQLite3).

Думаю, вы уже догадались, что конструкция IF EXIST заставляет сперва проверить SQLite3, а существует ли данная таблица вообще, прежде чем попытаться применить команду DROP TABLE. Мы также можем явно указать базу данных, из которой хотим удалить таблицу, для этого ставим точку перед именем таблицы и указываем имя базы данных.

Команда DROP INDEX в SQLite3. Оператор DROP INDEX в SQLite

Команда DROP INDEX в SQLite3 позволяет удалять индексы из базы данных. Синтаксис оператора DROP INDEX в SQLite3 очень простой.

Синтаксис команды DROPINDEX в SQLite3. Синтаксис оператора DROP INDEX в SQLite3

Приведем пример удаления индекса из базы данных (команда DROP INDEX) в SQLite3

Мы еще не изучали индексы, поэтому кому-то может показать странным необязательное указание базы данных при удалении индекса. Все дело в том, что имена индексов должны быть уникальны в рамках одной базы данных, как и имена таблиц, триггеров, представлений (это утверждение справедливо для любой СУБД). Конструкция IF EXISTS также необязательна.

Команда DROP TRIGGER в SQLite3. Оператор DROP TRIGGER в SQLite3

Посмотрим, как можно удалить триггер в SQLite3, команда DROP TRIGGER в SQLite3 позволяет нам его удалить. Синтаксис оператора DROP TRIGGER очень прост.

Команда DROP TRIGGER в SQLite. Оператор DROP TRIGGER в SQLite

Удаление триггера начинается с ключевого слова DROP TRIGGER, далее идет необязательная проверка на существование триггера в базе данных IF EXISTS, затем можно указать имя базы данных (можно и не указывать) и через точку указать имя триггера, который необходимо удалить из базы данных, предложение завершается точкой с запятой.

Команда DROP VIEW в SQLite3. Оператор DROP VIEW в SQLite3

Удалить представление в SQLite3 очень просто, для этого в SQLite есть команда DROP VIEW. Синтаксис оператора DROP VIEW в SQLite3 представлен ниже.

Команда DROP VIEW в SQLite3. Оператор DROP VIEW в SQLite

Удаление представления начинается с ключевого слова DROP VIEW, далее идет необязательная проверка на существование представления в базе данных IFEXISTS, затем можно указать имя базы данных (можно и не указывать) и через точку указать имя представления, которое необходимо удалить из базы данных, предложение завершается точкой с запятой.

Мы полностью рассмотрели синтаксис и особенности оператора DROP в SQLite, а так же мы закончили рассмотрение SQL команд группы DDL.

SQL DROP DATABASE Инструкция

Инструкция DROP базы данных SQL

Инструкция DROP DATABASE используется для удаления существующей базы данных SQL.

Синтаксис

Примечание: Будьте внимательны перед удалением базы данных. Удаление базы данных приведет к потере полной информации, хранящейся в базе данных!

Пример удаления базы данных

Следующая инструкция SQL удаляет существующую базу данных «testDB»:

Пример

Совет: Перед удалением базы данных убедитесь, что у вас есть права администратора. После удаления базы данных ее можно проверить в списке баз данных со следующей командой SQL: Показать базы данных;

Функции Flashback и восстановление базы данных Oracle

Предлагаемая Oracle технология Flashback позволяет “перематывать” базу данных или какие-то из ее частей до определенного момента времени в прошлом, не прибегая к традиционным, более длительным приемам восстановления, которые предусматривают использование файлов резервных копий и архивных журналов повторного выполнения.

Часто она оказывается быстрым и эффективным средством восстановления после логических повреждений или ошибок пользователей.

Преобразование SCN-номеров в значения TIMESTAMP и наоборот

Для преобразования SCN-номеров в соответствующие значения TIMESTMAP и наоборот доступны две специальных SQL-функции — SCN_TO_TIMESTAMP и TIMESTAMP_TO_SCN . Функция SCN_TO_TIMESTAMP позволяет преобразовывать SCN в календарное значение ( TIMESTAMP ).

Функция TIMESTAMP_TO_SCN выполняет противоположное SCN_TO_TIMESTAMP действие, т.е. преобразует календарное значение ( TIMESTAMP ) в соответствующий SCN-номер.

Для указания точной точки, до которой требуется выполнить восстановление, можно использовать как значение времени (часы, минуты, секунды), так и SCN-номер. В случае применения значения времени Oracle будет выбирать SCN-номер, находящийся в пределах трех секунд от этого значения. Oracle сохраняет отображение между указываемыми значениями времени и SCN-номерами на протяжении периода, который указан в параметре UNDO_RETENTION .

Уровни Flashback

Функции Flashback можно использовать как на уровне отдельных строк и таблиц, так и на уровне всей базы данных, как объясняется далее.

  • Уровень строк. Функции Flashback можно применять для отмены ошибочных изменений в отдельных строках. Существуют четыре функции Flashback, которые можно применять на уровне отдельных строк, и действие всех их основано на использовании данных отмены, хранящихся табличном пространстве отката. Ниже перечислены эти функции.
  • Функция Flashback Query (функция ретроспективного запроса). Позволяет просматривать старые данные строк на основе указанного момента во времени или SCN-номера. Более старые данные можно не только просматривать, но и при необходимости извлекать и отменять внесенные в них по ошибке изменения.
  • Функция Flashback Versions Query (функция ретроспективного запроса версий строки). Позволяет просматривать все версии одной и той же строка за определенный период и, если необходимо, отменять логические ошибки. Может также предоставлять хронологию аудита изменений и тем самым, по сути, позволять сравнивать настоящие данные со старыми без выполнения DML-операций.
  • Функция Flashback Transaction Query (функция ретроспективного запроса транзакций). Позволяет просматривать изменения, внесенные на уровне отдельных транзакций. Помогает при осуществлении анализа и аудита транзакций, например, при двукратном выполнении пакетного задания и необходимости определить, какие объекты пострадали. С помощью этой функции можно легко отменять изменения, внесенные целой транзакцией за определенный период времени.
  • Функция Flashback Transaction Backout (функция ретроспективной отмены транзакций). Позволяет отменять транзакции вместе со всеми ее зависимыми транзакциями одним щелчком.
  • Уровень таблиц. Существуют две основных функции Flashback, которые можно применять на уровне отдельных таблиц.
  • Функция Flashback Table (функция ретроспективного отката таблицы). Позволяет восстанавливать таблицу до определенного момента времени в прошлом или до определенного SCN-номера без выполнения восстановления файлов данных. Для отмены изменений в таблице использует DML-изменения. Работает за счет применения данных отмены.
  • Функция Flashback Drop (функция ретроспективного отката удаления). Позволяет отменять действие оператора DROP TABLE без выполнения процедуры восстановления до состояния на определенный момент времени в прошлом (PITR). Для восстановления удаленной таблицы использует корзину (Recycle Bin).
  • Уровень базы данных. Функция Flashback Database (функция ретроспективного отката базы данных) позволяет восстанавливать всю базу данных до состояния на определенный момент времени в прошлом и тем самым отменять все произошедшие с того времени изменения. Например, с ее помощью можно легко восстанавливать удаленную схему или усеченную по ошибке таблицу. Она работает в основном за счет использования журналов ретроспективного отката для извлечения более старых версий блоков данных, а также, в некоторой степени, за счет использования архивных журналов повторного выполнения для полного восстановления базы данных без восстановления файлов данных и проведения традиционной процедуры восстановления носителя.

Как видно, предлагаемая Oracle технология Flashback состоит из разных функций. Функция Flashback Table и все работающие на уровне строк функции Flashback основаны на использовании данных отката и потому рассматривались в главе 6. Еще, однако, существует механизм Flashback Data Archive (Архив ретроспективных данных), который позволяет хранить и отслеживать транзакционные изменения в данных таблицы. Его можно применять для запросов, предусматривающих извлечение хронологических данных, или для удовлетворения требований по соблюдению законодательных норм. Функции Flashback Drop и Flashback Database полагаются на новую концепцию корзины и данных журнала ретроспективного отката и служат для отмены ошибок на различных уровнях. Они более подробно рассматриваются позже в этой главе.

Функции Flashback и традиционные приемы восстановления

В отличие от традиционных приемов восстановления, функции Flashback рассчитаны главным образом не на выполнение восстановления после потери носителя, а на выполнение восстановления после человеческих ошибок. Например, может происходить случайное изменение не того набора данных или удаление таблицы, либо просто возникать необходимость в выполнении запроса к хронологическим данным и проведении анализа изменений. В некоторых крайних случаях может также возникать потребность в возврате всей базы данных в состояние, в котором она пребывала в какой-то момент времени в прошлом.

На заметку! В случае повреждения диска или появления в базе данных физического (а не логического, связанного с ошибками приложений или пользователей) повреждения, необходимо все равно использовать традиционные методы восстановления из резервных копий и применять архивные журналы повторного выполнения для приведения базы данных в актуальное состояние.

Раньше единственным способом для выполнения восстановления после человеческой ошибки было применение традиционных приемов резервного копирования и восстановления. Процесс восстановления файлов базы данных и последующего наката до актуального состояния с помощью данных всех журналов повторного выполнения, однако, часто выливался в серьезные периоды простоя, а технология Flashback предлагает гораздо более эффективный и быстрый способ выполнения восстановления, в большинстве случаев позволяющий не переводить базу данных в автономный режим и оставлять ее доступной для пользователей. Более того, она еще позволяет выполнять выборочное восстановление только определенных объектов. В случае применения традиционных приемов администратору баз данных не остается никакого выбора, как выполнять восстановление всей базы данных.

Функция Flashback Drop

Функция Flashback Drop представляет собой средство для восстановления случайно удаленной таблицы (и ее индексов) без потери недавних транзакций. Большинству опытных администраторов наверняка приходилось сталкиваться с ситуациями случайного удаления какой-нибудь производственной таблицы. Выполнение оператора DROP TABLE происходит за считанные секунды, приглашение SQL возвращается после него очень быстро, а вот его последствия могут оказываться ужасными. К сожалению, никакого приглашения подтвердить намерение удалить таблицу перед тем, как она исчезнет, не отображается!

Начиная с Oracle Database 10g, при удалении таблицы Oracle не избавляется от нее немедленно. Вместо этого таблица вместе с любыми зависящими от нее объектами перечисляется в корзине (о которой более подробно будет рассказываться чуть позже) и хранится настолько долго, насколько возможно. В случае быстрого обнаружения, что была допущена ошибка, для немедленного восстановления удаленной таблицы можно применить следующую простую команду:

Совет. Одним из наилучших способов для исключения вероятности случайного удаления таблицы является использование в SQL*Plus новых влияющих на вид приглашения переменных, позволяющих делать так, чтобы в приглашении отображалось имя используемой базы данных и имя пользователя. Более подробно об этих переменных рассказывалось в этой статье.

Как работает функция Flashback Drop

До выхода Oracle Database 10g выполнение команды DROP TABLE приводило к немедленному удалению таблицы и всех ее зависимых объектов и возвращению всего пространства, которая она занимала, обратно базе данных.

Начиная с Oracle Database 10g, однако, таблица и ее зависимые объекты больше не удаляются немедленно. Вместо этого они переименовываются и временно остаются в том же самом месте, где Oracle хранит их настолько долго, насколько получается, в зависимости от возникающих потребностей в пространстве. Как уже отмечалось ранее, все такие “удаленные” объекты перечисляются в корзине (Recycle Bin), которая представляет собой логический контейнер (таблицу словаря данных, в которой хранится информация об удаленных таблицах, наподобие их новых и исходных имен). Содержимое корзины можно просматривать, как и содержимое любой обычной таблицы, выполняя к ней простой запрос SELECT * FROM DBA_RECYCLEBIN . Пока таблица все еще перечисляется в корзине, ее можно восстанавливать в любое время с помощью функции Flashback Drop.

Совет. После выхода версии Oracle Database 10g Release 2 появилась возможность использовать параметр инициализации RECYCLEBIN для отключения функции Flashback Drop. По умолчанию для этого параметра установлено значение ON, при котором все удаляемые таблицы попадают в корзину и могут восстанавливаться позже с помощью функции Flashback Drop. За счет установки для этого параметра значения OFF можно отключить функцию Flashback Drop и таблицы при удалении помещаться в корзину не будут.

Выполнение запроса к представлению DBA_FREE_SPACE будет показывать, что пространство, которое ранее занимали “удаленные” объекты, теперь свободно. На самом деле, однако, это пространство не становится немедленно доступным для использования в базе данных: оно представляет собой потенциально свободное пространство, которое сможет применяться в базе данных позже, как только данные объекты будут удалены навсегда. Следовательно, несмотря на то, что сообщается в представлении DBA_FREE_SPACE , “удаленные” объекты будут продолжать занимать исходное выделенное для них место в табличных пространствах до тех пор, пока не будут безвозвратно удалены из корзины. Это удаление может произойти при следующих обстоятельствах.

  • В случае безвозвратного удаления объектов из корзины пользователем с помощью команды PURGE ( DROP TABLE имя_таблицы PURGE );
  • В случае автоматического удаления этих объектов из корзины Oracle из-за нехватки пространства, т.е. при отсутствии достаточного количества свободного места в табличном пространстве для создания нового объекта или расширения пространства, занимаемого существующим объектом.

Совет. В базе данных Oracle Database 11g функция Flashback Drop включена автоматически. Для ее использования ничего конфигурировать не требуется.

Из всего вышесказанного следует, что при выполнении команды DROP TABLE (или DROP INDEX ) в Oracle Database 11g запрашиваемые объекты на самом деле не удаляются. Oracle просто скрывает их, и потому их можно восстанавливать позже с помощью функции Flashback Drop. При желании действительно навсегда удалить какой-нибудь объект, можно указать в команде DROP параметр PURGE :

Давайте рассмотрим весь этот процесс немного более детально.

Корзина

Как уже упоминалось ранее, корзина (Recycle Bin) представляет собой логическую структуру, а точнее — таблицу, которая хранится в словаре данных RECYCLEBIN$ . Просматривать содержимое корзины на уровне подключенного к системе в текущий момент пользователя можно через представление USER_RECYCLEBIN (по сути, RECYCLEBIN является синонимом USER_RECYCLEBIN ), а просматривать ее содержимое на уровне всей базы данных — через представление DBA_RECYCLEBIN . Ниже приведен пример.

На уровне пользователя выборка данных осуществляется не из представления DBA_RECYCLEBIN , а из представления RECYCLEBIN . Кроме того, для просмотра содержимого корзины в сеансе SQL*Plus можно использовать команду SHOW RECYCLEBIN :

Совет. Столбцы CAN_UNDROP и CAN_PURGE в представлении DBA_RECYCLEBIN показывают, можно ли, соответственно, отменить удаление объекта и удалить его навсегда. Команда SHOW RECYCLEBIN отображает лишь те объекты, удаление которых можно отменить (undrop).

Как не трудно заметить, при перемещении таблицы в корзину Oracle присваивает ей генерируемое системой имя, которое обычно составляет 30 символов в длину. При желании выполнить запрос к объекту в корзине, нужно обязательно использовать такое сгенерированное системой имя, заключая его в двойные кавычки:

На заметку! Объекты в корзине можно только запрашивать с помощью команды SELECT . Команды INSERT, UPDATE и DELETE работать не будут.

Oracle переименовывает все без исключения объекты в корзине, в том числе и любые зависимые объекты, вроде индексов, ограничений и триггеров. При восстановлении таблицы Oracle будет восстанавливать и зависимые объекты тоже, но у них будут оставаться такие непонятные сгенерированные системой имена, из-за чего их нужно будет снова переименовывать соответствующим образом.

Для выяснения того, какие таблицы в текущий момент находятся в корзине, можно выполнить запрос к представлению DBA_TABLES . Таблица, которая была удалена и находится в корзине, будет иметь в столбце DROPPED значение YES , а все остальные — значение NO .

Восстановление удаленной таблицы

Любую удаленную таблицу можно восстановить, если она находится в корзине, с помощью команды FLASHBACK TABLE имя_таблицы TO BEFORE DROP (которая будет приводить не только к восстановлению таблицы, но и к ее удалению из корзины). Ниже приведен пример восстановления удаленной ранее таблицы persons :

В качестве альтернативного варианта, можно также использовать и сгенерированное системой имя таблицы:

При желании удаленную ранее таблицу можно переименовать как часть операции FLASHBACK (и в этом случае тоже можно указывать как сгенерированное системой, так и исходное имя таблицы):

Подобное особенно удобно, когда в системе уже была создана новая таблица с таким же именем, что и у удаленной таблицы.

В случае удаления таблицы, а затем создания новой с таким же именем и потом удаления и ее тоже, в корзине может содержаться несколько версий удаленной таблицы с уникальным сгенерированным системой именем у каждой. Выполнение команды FLASHBACK TABLE. TO BEFORE DROP с использованием исходного имени таблицы тогда будет приводить к восстановлению самой последней версии этой таблицы. Если необходимо вернуться к какой-то более старой версии, можно либо выполнять одну и ту же команду до тех пор, пока не будет восстановлена требуемая версия, либо сразу предоставить уникальное сгенерированное системой имя конкретно той версии, которую нужно восстановить.

Безвозвратное удаление таблиц

Как уже упоминалось ранее, при желании удалить таблицу немедленно и навсегда, без ее помещения в корзину, нужно использовать команду DROP TABLE имя_таблицы PURGE :

Совет. Новая конструкция PURGE оказывается особенно удобной, когда требуется удалить какую-то секретную таблицу и ради безопасности нельзя, чтобы она попала корзину.

С помощью команды PURGE TABLE или PURGE INDEX можно безвозвратно стереть удаленную ранее таблицу или индекс из корзины:

В качестве альтернативного варианта, можно также использовать и сгенерированное системой имя:

При наличии в корзине нескольких таблиц с одинаковым исходным именем, команда PURGE будет приводить к удалению первой таблицы, которая была исходно удалена.

На заметку! После удаления объекта из корзины с помощью команды PURGE или указании при удалении объекта параметра PURGE , применять потом функцию FLASHBACK DROP для извлечения этого объекта (или любых зависимых от него объектов) нельзя, потому что удаляемые подобным образом объекты удаляются навсегда.

С помощью команды PURGE TABLESPACE можно удалить из корзины все объекты, которые являются частью определенного табличного пространства:

Следующая команда приведет к удалению из табличного пространства users всех объектов, принадлежащих одному только пользователю scott (вместе с любыми зависимыми от них объектами, которые находятся в других табличных пространствах):

Для безвозвратного удаления всех объектов из табличного пространства, не помещая их в корзину, можно использовать команду DROP TABLESPACE. INCLUDING CONTENTS . В таком случае помимо этих объектов будут также навсегда удаляться и любые принадлежащие данному табличному пространству объекты, которые в текущий момент уже находятся в корзине. Выполнение команды DROP TABLESPACE самой по себе, т.е. без конструкции INCLUDING CONTENTS , будет завершаться ошибкой, если только табличное пространство не является пустым.

Чтобы навсегда удалить все объекты, которые в текущий момент находятся в корзине, можно воспользоваться командой PURGE RECYCLEBIN (или PURGE USER_RECYCLEBIN ).

Эти команды будут приводить к удалению лишь тех объектов, которые принадлежат запустившему их пользователю. Для того чтобы очистить корзину от всех объектов, независимо от того, кому они принадлежат, можно применить команду PURGE DBA_RECYCLEBIN . Однако по вполне очевидным причинам для выполнения этой команды требуется обладать привилегиями SYSDBA .

На заметку! Команда DROP USER. CASCADE указывает Oracle, что нужно удалить пользователя и все объекты, которыми он владеет, из базы данных, и будет автоматически приводить к безвозвратному удалению принадлежащих этому пользователю объектов из корзины.

И, наконец, не следует забывать о том, что Oracle может автоматически удалять объекты из корзины в случае нехватки пространства. Удаление всегда начинается с самых старых объектов.

Необходимые права

Для извлечения таблицы с помощью команды FLASHBACK TABLE имя_таблицы TO BEFORE DROP требуется либо быть владельцем этой таблицы, либо обладать привилегиями на выполнение в ней операций удаления ( DROP TABLE или DROP ANY TABLE ). Чтобы использовать команду PURGE , нужно иметь похожие привилегии. Для выполнения запроса к объекту, уже находящему в корзине, требуется обладать привилегией SELECT и привилегией FLASHBACK на уровне этого объекта.

Функция Flashback Database

До выхода Oracle Database 10g, в случае появления логического повреждения в базе данных, требовалось прибегать к традиционным приемам восстановления до состояния на определенный момент времени, восстанавливать файлы данных из резервных копий и потом применять архивные журналы повторного выполнения для приведения базы данных в актуальное состояние. Это зачастую отнимало много времени и усилий. Каким бы незначительным не был масштаб повреждения, все равно требовалось выполнять восстановление всех файлов данных и применять архивные журналы повторного выполнения.

На заметку! Oracle может проверять целостность блоков данных за счет вычисления для них контрольных сумм перед их записью на диск и при их последующем считывании. Несовпадение исходной и текущей контрольной суммы блока свидетельствует о том, что, скорее всего, в нем присутствует повреждение. Установив для параметра инициализации DB_BLOCK_CHECKSUM значение FULL , можно заставить базу данных выполнять такую проверку в самом кэше буферов и тем самым исключать вероятность возникновения повреждений на уровне физического диска. По умолчанию для параметра DB_BLOCK_CHECKSUM устанавливается значение FALSE .

Функция Flashback Database позволяет восстанавливать файлы данных без выполнения их восстановления из резервных копий и с использованием лишь части информации журналов повторного выполнения. Она просто возвращает все файлы базы данных в состояние, в котором они находились на определенный момент времени в прошлом. В случае использования этой функции время восстановления напрямую зависит от количества изменений, которые требуется отменить. Следовательно, решающую роль в том, сколько времени занимает процесс восстановления, играет масштаб ошибки, а не размер базы данных. Это означает, что справляться с восстановлением после логических ошибок можно лишь за часть того времени (возможно, даже, в зависимости от размера базы данных, лишь за сотую долю того времени), которое пришлось бы потратить в случае применения традиционных методов.

На заметку! Применять функцию Flashback Database допускается только при отсутствии проблем с носителем. В случае потери или повреждения файла данных придется выполнять восстановление с использованием резервных копий.

Функцией Flashback Database можно пользоваться в следующих ситуациях:

  • когда необходимо извлечь какую-нибудь удаленную схему;
  • когда ошибка пользователя оказывает негативное воздействие на всю базу данных;
  • когда таблица была усечена по ошибке;
  • когда пакетное задание вносит только частичные изменения.

Функция Flashback Database использует журналы ретроспективного отката базы данных (flashback database logs), которые хранятся в новой области пакетного восстановления, для отмены изменений вплоть до момента, идущего непосредственно перед указанным целевым временем или SCN-номером. Поскольку указываемое целевое и фактическое время восстановления могут немного отличаться, далее применяются архивные журналы повторного выполнения для записи в базу данных изменений, которые успели произойти в том коротком промежутке между целевым и фактическим временем восстановления.

После включения функции Flashback Database для выполнения возвращения базы данных до какого-то предыдущего момента во времени, SCN-номера или порядкового номера журнала достаточно выдать команду FLASHBACK DATABASE. Выполнять команду FLASHBACK DATABASE можно как в RMAN, так и в SQL*Plus. Единственное отличие состоит в том, что RMAN будет автоматически извлекать все необходимые архивные журналы повторного выполнения, в то время как в SQL*Plus может потребоваться предоставлять эти журналы самостоятельно, если только в SQL*Plus не была включена функция SET AUTORECOVERY ON .

Чуть позже мы обязательно посмотрим, как функция Flashback Database выглядит в действии, но сначала давайте разберемся с тем, как она включается и отключается.

Совет. Из-за необходимости иметь в наличии текущие файлы данных для того, чтобы применять к ним изменения, применять функцию Flashback Database в случае повреждения или потери файлов нельзя.

Конфигурирование функции Flashback Database

Чтобы сконфигурировать функцию Flashback Database, потребуется выполнить ряд действий.

1. Удостоверьтесь в том, что база данных находится в режиме архивирования журналов ( ARCHIVELOG ), либо выполнив запрос к представлению V$DATABASE , либо воспользовавшись следующей командой:

В показанном выше выводе видно, что база данных действительно функционирует в режиме ARCHIVELOG . Если это не так, включить режим архивирования журналов можно с помощью оператора ALTER DATABASE , как показано ниже, сначала остановив базу данных и запустив ее заново в режиме монтирования:

2. Настройте область пакетного восстановления, как было описано в главе 15.

3. Установите параметр инициализации DB_FLASHBACK_RETENTION_TARGET так, чтобы он указывал, насколько далеко назад можно будет выполнять ретроспективный откат базы данных. В следующем коде для него устанавливается значение 1440 (означающее 1440 минут, т.е. 1 день):

4. Остановите и перезапустите базу данных в монопольном режиме монтирования. Если существует только один экземпляр, можно применить и просто команду MOUNT :

5. Включите функцию Flashback Database:

6. Откройте базу данных с помощью команды ALTER DATABASE OPEN и затем удостоверьтесь в том, что функция Flashback Database действительно включена, выполнив запроса к представлению V$DATABASE :

Более простой путь предполагает использование для настройки функции Flashback Database интерфейса OEM Database Control и выполнение в нем перечисленных ниже шагов (при условии, что база данных функционирует в режиме ARCHIVELOG ).

  1. Отобразите домашнюю страницу базы данных (Database Home Page) и щелкните на вкладке Maintenance (Сопровождение).
  2. Перейдите в раздел Backup/Recovery (Резервное копирование/Восстановление) и щелкните на ссылке Configure Recovery Settings (Сконфигурировать параметры восстановления).
  3. В разделе Flash Recovery Area (Область пакетного восстановления) укажите значения в полях Flash Recovery Area Location (Размещение области пакетного восстановления) и Flash Recovery Area Size (Размер области пакетного восстановления).
  4. Отметьте флажок рядом с опцией Enable Flashback Database — flashback logging can be used for fast database point-in-time recovery (Включить функцию ретроспективного отката базы данных — журналы ретроспективного отката могут применяться для выполнения быстрого восстановления базы данных до состояния на определенный момент в прошлом), как показано на рисунке ниже:

Отключение функции Flashback Database

Отключить функцию Flashback Database можно посредством команды ALTER DATABASE FLASHBACK OFF . Перед использованием этой команды, однако, сначала нужно обязательно остановить экземпляр базы данных и перезапустить его в режиме монтирования (или монопольном режиме монтирования):

Совет. При отключении функции Flashback Database все журналы Flashback Database будут удалены из области пакетного восстановления.

При желании использовать функцию Flashback Database, но только не для определенных табличных пространств, можно отключить ее на уровне этих пространств с помощью команды ALTER TABLESPACE :

После отключения этой функции подобным образом Oracle больше не будет сохранять в журналах данные Flashback Database для конкретного табличного пространства. Если нужно включить эту функцию для данного пространства снова, достаточно просто выполнить такую команду:

Как объяснялось в главе 5, создавать табличное пространство с отключенной функцией Flashback можно указанием при создании табличного пространства конструкции FLASHBACK OFF . По умолчанию эта функция включена.

Понятия, связанные с работой функции Flashback Database

После включения функции Flashback Database также активизируется и новый фоновый процесс RVWR (Recovery Writer — процесс записи данных восстановления). Этот процесс, через нечастые промежутки времени, чтобы сократить нагрузку на ЦП и средства ввода-вывода, копирует предыдущий образ (before-image) каждого изменившегося в файлах данных блока из буфера памяти (буфера ретроспективных данных) в журналы ретроспективного отката базы данных, которые хранятся в области пакетного восстановления. Область пакетного восстановления представляет собой область, которая специально выделяется на диске под хранение имеющих отношение к восстановлению компонентов, наподобие упомянутых копий образов, инкрементных резервных копий и архивных журналов повторного выполнения.

На заметку! Для повышения пропускной способности диска в Oracle рекомендуют использовать для областей пакетного восстановления быстро работающие файловые системы и множественные дисководы со слоями небольшого размера (128 Кбайт).

Журналы ретроспективного отката базы данных (Flashback Database) похожи на традиционные используемые Oracle журналы повторного выполнения (в те и другие запись данных выполняется из области буфера), но имеют одно серьезное отличие, которое состоит в том, что архивных журналов ретроспективного отката базы данных не бывает! Oracle всегда сохраняет все журналы ретроспективного отката базы данных в области пакетного восстановления.

На заметку! Для выполнения операции ретроспективного отката базы данных необходимо обладать привилегиями SYSDBA .

Предыдущие образы блоков данных можно использовать для воссоздания файла данных в том виде, в котором он существовал на определенный момент времени в прошлом. По сути, с их помощью можно отменять любые изменения, которые были внесены в файл данных после указанного целевого момента времени. В действительности журналы Flashback Database применяются для выполнения восстановления до момента, который непосредственно предшествовал указанному целевому времени. После этого Oracle использует традиционные архивные журналы для записи любых изменений, которые успели произойти за короткий промежуток между целевым и фактическим временем восстановления.

Например, при желании выполнить ретроспективный откат до 9:00, может оказаться, что ближайшая к целевому времени запись данных в журналы Flashback Database была выполнена в 8:57. Тогда, следовательно, чтобы охватить этот трех- или четырех-минутный промежуток, потребуется обязательно применить изменения из архивных или оперативных журналов повторного выполнения. Из-за этого, хотя традиционная процедура PITR, подразумевающая использование резервных копий и файлов архивных журналов повторного выполнения, и не выполняется, наличие доступа к журналам повторного выполнения за весь период времени, охватываемый журналами Flashback Database, все равно является обязательным.

При непосредственном выполнении команды FLASHBACK DATABASE Oracle сначала проверяет, доступны ли требуемые файлы архивных и оперативных журналов повторного выполнения, и если да, тогда автоматически возвращает все текущие оперативные файлы данных к указанному в этой команде SCN-номеру или времени.

Совет. Время, необходимое для выполнения ретроспективного отката базы данных, зависит от того, насколько далеко назад требуется вернуться, и какое количество изменений в блоках данных успело произойти за этот промежуток времени. В базе данных, которая интенсивно используется для выполнения DML-операций, будет больше изменений в блоках данных, чем, в той, которая применяется в основном для обслуживания запросов.

Ограничения, касающиеся хранения журналов Flashback Database

Следует помнить о том, что Oracle не гарантирует возможности выполнения обратного отката базы данных на такое количество времени назад, которое указано в параметре инициализации FLASHBACK_RETENTION_TARGET (каковое в примере составляет один день). Если Oracle перестанет хватать свободного пространства в области пакетного восстановления, будут удаляться более старые журналы ретроспективного отката для высвобождения места под поступающие новые резервные копии файлов данных, архивные файлы журналов повторного выполнения или любые другие связанные с резервным копированием файлы, которые являются частью области пакетного восстановления.

Более того, как отмечалось ранее, область пакетного восстановления выделяется специально под хранение файлов, имеющих отношение к восстановлению. Из-за этого база данных ставит хранение таких связанных с восстановлением файлов выше сбережения журналов Flashback Database. Следовательно, при необходимости уместить в области пакетного восстановления какие-то другие файлы, имеющие отношение к восстановлению, Oracle будет автоматически удалять журналы Flashback Database.

Поэтому очень важно следить за размером области пакетного восстановления и проверять, чтобы в ней всегда хватало пространства, чтобы не подвергаться риску потери журналов Flashback Database, которые могут потребоваться для выполнения восстановления базы данных в будущем.

На заметку! Допускается создавать гарантированную точку восстановления для уверенности в том, что Oracle будет всегда продолжать хранить журналы Flashback Database и журналы повторного выполнения, необходимые для выполнения ретроспективного отката базы данных до указанного момента времени. Более подробно о точках восстановления и гарантированных точках восстановления в том числе будет рассказываться позже в этой главе, в разделе “Использование точек восстановления”.

Объем пространства, который необходимо выделять для области пакетного восстановления, зависит от значения, которое устанавливается для параметра DB_FLASHBACK_RETENTION_TARGET . После того, как база данных поработает с включенной функцией Flashback Database приличное количество времени (достаточное для того, чтобы удалось зафиксировать типичную рабочую нагрузку и частоту выполнения в базе данных операций по изменению данных), объем требуемого пространства можно оценивать путем выполнения запроса к представлению V$FLASHBACK_DATABASE_LOG , как показано ниже:

Хотя этот запрос и помогает оценить требуемый объем пространства для журналов Flashback Database, нет никакой гарантии, что этого пространства будет хватать.

Для выяснения того, насколько далеко назад получится выполнить откат базы данных в любой заданный момент времени, можно выдать такой запрос:

Если результат покажет, что выполнение отмотки базы данных до времени, указанного в параметре DB_FLASHBACK_RETENTION_TARGET , невозможно, следует задуматься об увеличении размера области пакетного восстановления.

Представление V$FLASHBACK_DATABASE_STAT позволяет следить за любыми отклонения в генерации данных Flashback на протяжении дня. На основании предоставляемых этим представлением статистических данных очень удобно регулировать значение параметра RETENTION_TARGET , размер области пакетного восстановления либо то и другое вместе.

Функция Flashback Database на примере

Теперь можно посмотреть, как функция Flashback Database выглядит в действии на реальном примере. Здесь используются команды SQL, но при желании все описываемые шаги можно выполнять и с помощью утилиты RMAN. Для начала в данном примере предлагается создать таблицу persons и загрузить в нее тестовые данные. Необходимые для этого шаги перечислены ниже.

1. Создайте таблицу:

2. Получите информацию об общем количестве строк в новой таблице:

3. Узнайте, как выглядит текущий SCN-номер базы данных:

4. Выполните оператор INSERT для удваивания строк в таблице persons , как показано ниже:

5. Удостоверьтесь в том, что данные были действительно вставлены, следующим образом:

Далее предположим, что требуется выполнить откат базы данных до того времени, когда в таблице persons содержалось еще 32768 строк. По сути, это означает, что необходимо вернуться к SCN-номеру 5965123 . Соответствующие шаги выглядят следующим образом.

1. Остановите базу данных Oracle и запустите ее снова в монопольном режиме монтирования, как показано ниже:

На заметку! Для применения функции Flashback Database обязательно нужно, чтобы база данных функционировала в режиме монтирования.

2. Выполните следующую простую команду FLASHBACK DATABASE :

Обратите внимание, что конструкция TO SCN подразумевает возврат базы данных к состоянию, в котором она находилась при указанном SCN-номере. Однако базу данных также можно возвращать и к состояния, в котором она пребывала непосредственно перед получением указанного SCN-номера, с помощью конструкции TO BEFORE SCN . Вдобавок можно использовать конструкции TO TIMESTAMP или TO BEFORE TIMESTAMP и возвращать базу данных к состоянию, в котором она находилась, соответственно, на какой-то определенный момент времени в прошлом и непосредственно за секунду до наступления этого момента.

3. Попробуйте открыть базу данных, чтобы выдать запрос к таблице persons :

Как видно, попытка открыть базу данных не удалась: для получения к откатанной базе данных доступа по записи потребуется повторить попытку с использованием оператора ALTER DATABASE OPEN RESETLOGS . Однако перед этим не помешает сначала открыть базу данных с помощью оператора ALTER DATABASE OPEN READ ONLY , удостоверившись в том, что ретроспективный откат базы данных был выполнен до корректной точки во времени или до правильного SCN-номера. Если после такой первоначальной проверки в режиме только для чтения окажется, что ретроспективный откат был выполнен слишком далеко назад в прошлое, можно воспользоваться журналами повторного выполнения и промотать базу данных немного вперед, а если окажется, что откат был выполнен недостаточно далеко назад, можно снова выдать команду FLASHBACK DATABASE и указать в ней более ранний SCN-номер.

Только после обретения полной уверенности в том, что ретроспективный откат был выполнен до времени, которое требовалось, можно финализировать этот процесс с помощью следующей команды:

4. Удостоверьтесь в том, что ретроспективный откат базы данных прошел надлежащим образом:

В качестве альтернативного варианта, вместо SCN-номера для указания желаемой точки ретроспективного отката можно также использовать порядковый номер архивного журнала или значение времени, например:

Независимо от того, указывается SCN-номер, значение времени или порядковый номер журнала, после получения уверенности в том, что база данных была точно восстановлена до требуемого состояния, ее можно сделать доступной для пользователей, выполнив следующую команду:

После этого база данных будет отражать состояние, в котором пребывала на момент использования прежнего SCN-номера или значения времени, которое было выбрано. Изменений, которые были внесены в нее после этого момента, больше не будет.

В случае если состояние базы данных после проведения операции Flashback Database по какой-то причине не понравится, с помощью приведенной ниже команды можно отменить результат всей операции Flashback:

Эта команда RECOVER DATABASE выполнит процедуру полного восстановления базы данных, применив все изменения, о которых имеются сведения в архивных журналах повторного выполнения, и снова приведет базу данных в актуальное состояние.

В случае если вдруг окажется, что ретроспективный откат базы данных был выполнен в первый раз недостаточно далеко назад, можно выполнить команду FLASHBACK DATABASE снова и вернуть базу данных еще немного назад.

В случае же если окажется, что ретроспективный откат базы данных был выполнен дальше, чем требовалось, можно воспользоваться командой RECOVER DATABASE UNTIL и вернуть базу данных немного вперед.

Особенности функции Flashback Database

В завершение этого раздела следует перечислить несколько ограничений, о которых нужно обязательно помнить при использовании функции Flashback Database.

  • База данных должна обязательно функционировать в режиме архивирования журналов ( ARCHIVELOG ).
  • В случае утери файла данных или невозможности использовать его по какой-то причине, применять для его восстановления функцию Flashback Database нельзя.
  • Если на протяжении периода, на который требуется выполнить ретроспективный откат назад, выполнялось восстановление или воссоздание управляющего файла, применять функцию Flashback Database нельзя.
  • Выполнять ретроспективный откат базы данных до состояния, в котором она находилась непосредственно перед проведением операции RESETLOGS , нельзя.
  • Выполнять ретроспективный откат файла данных, который сжимался или удалялся во время охватываемого операцией Flashback Table периода времени, нельзя.

На заметку! Начиная с версии Oracle Database 10g Release 2, функцию Flashback Database можно применять для выполнения возврата за пределами операции OPEN RESETLOGS и, следовательно, возвращать текущую базу данных к состоянию предшествующей или родственной инкарнации.

Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть первая

О чем данный учебник

Данный учебник представляет собой что-то типа «штампа моей памяти» по языку SQL (DDL, DML), т.е. это информация, которая накопилась по ходу профессиональной деятельности и постоянно хранится в моей голове. Это для меня достаточный минимум, который применяется при работе с базами данных наиболее часто. Если встает необходимость применять более полные конструкции SQL, то я обычно обращаюсь за помощью в библиотеку MSDN расположенную в интернет. На мой взгляд, удержать все в голове очень сложно, да и нет особой необходимости в этом. Но знать основные конструкции очень полезно, т.к. они применимы практически в таком же виде во многих реляционных базах данных, таких как Oracle, MySQL, Firebird. Отличия в основном состоят в типах данных, которые могут отличаться в деталях. Основных конструкций языка SQL не так много, и при постоянной практике они быстро запоминаются. Например, для создания объектов (таблиц, ограничений, индексов и т.п.) достаточно иметь под рукой текстовый редактор среды (IDE) для работы с базой данных, и нет надобности изучать визуальный инструментарий заточенный для работы с конкретным типом баз данных (MS SQL, Oracle, MySQL, Firebird, …). Это удобно и тем, что весь текст находится перед глазами, и не нужно бегать по многочисленным вкладкам для того чтобы создать, например, индекс или ограничение. При постоянной работе с базой данных, создать, изменить, а особенно пересоздать объект при помощи скриптов получается в разы быстрее, чем если это делать в визуальном режиме. Так же в скриптовом режиме (соответственно, при должной аккуратности), проще задавать и контролировать правила наименования объектов (мое субъективное мнение). К тому же скрипты удобно использовать в случае, когда изменения, делаемые в одной базе данных (например, тестовой), необходимо перенести в таком же виде в другую базу (продуктивную).

Язык SQL подразделяется на несколько частей, здесь я рассмотрю 2 наиболее важные его части:

  • DDL – Data Definition Language (язык описания данных)
  • DML – Data Manipulation Language (язык манипулирования данными), который содержит следующие конструкции:
    • SELECT – выборка данных
    • INSERT – вставка новых данных
    • UPDATE – обновление данных
    • DELETE – удаление данных
    • MERGE – слияние данных

Т.к. я являюсь практиком, как таковой теории в данном учебнике будет мало, и все конструкции будут объясняться на практических примерах. К тому же я считаю, что язык программирования, а особенно SQL, можно освоить только на практике, самостоятельно пощупав его и поняв, что происходит, когда вы выполняете ту или иную конструкцию.

Данный учебник создан по принципу Step by Step, т.е. необходимо читать его последовательно и желательно сразу же выполняя примеры. Но если по ходу у вас возникает потребность узнать о какой-то команде более детально, то используйте конкретный поиск в интернет, например, в библиотеке MSDN.

При написании данного учебника использовалась база данных MS SQL Server версии 2014, для выполнения скриптов я использовал MS SQL Server Management Studio (SSMS).

Кратко о MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) — утилита для Microsoft SQL Server для конфигурирования, управления и администрирования компонентов базы данных. Данная утилита содержит редактор скриптов (который в основном и будет нами использоваться) и графическую программу, которая работает с объектами и настройками сервера. Главным инструментом SQL Server Management Studio является Object Explorer, который позволяет пользователю просматривать, извлекать объекты сервера, а также управлять ими. Данный текст частично позаимствован с википедии.

Для создания нового редактора скрипта используйте кнопку «New Query/Новый запрос»:

Для смены текущей базы данных можно использовать выпадающий список:

Для выполнения определенной команды (или группы команд) выделите ее и нажмите кнопку «Execute/Выполнить» или же клавишу «F5». Если в редакторе в текущий момент находится только одна команда, или же вам необходимо выполнить все команды, то ничего выделять не нужно.

После выполнения скриптов, в особенности создающих объекты (таблицы, столбцы, индексы), чтобы увидеть изменения, используйте обновление из контекстного меню, выделив соответствующую группу (например, Таблицы), саму таблицу или группу Столбцы в ней.

Собственно, это все, что нам необходимо будет знать для выполнения приведенных здесь примеров. Остальное по утилите SSMS несложно изучить самостоятельно.

Немного теории

Реляционная база данных (РБД, или далее в контексте просто БД) представляет из себя совокупность таблиц, связанных между собой. Если говорить грубо, то БД – файл в котором данные хранятся в структурированном виде.

СУБД – Система Управления этими Базами Данных, т.е. это комплекс инструментов для работы с конкретным типом БД (MS SQL, Oracle, MySQL, Firebird, …).

Примечание
Т.к. в жизни, в разговорной речи, мы по большей части говорим: «БД Oracle», или даже просто «Oracle», на самом деле подразумевая «СУБД Oracle», то в контексте данного учебника иногда будет употребляться термин БД. Из контекста, я думаю, будет понятно, о чем именно идет речь.

Таблица представляет из себя совокупность столбцов. Столбцы, так же могут называть полями или колонками, все эти слова будут использоваться как синонимы, выражающие одно и тоже.

Таблица – это главный объект РБД, все данные РБД хранятся построчно в столбцах таблицы. Строки, записи – тоже синонимы.

Для каждой таблицы, как и ее столбцов задаются наименования, по которым впоследствии к ним идет обращение.
Наименование объекта (имя таблицы, имя столбца, имя индекса и т.п.) в MS SQL может иметь максимальную длину 128 символов.

Для справки – в БД ORACLE наименования объектов могут иметь максимальную длину 30 символов. Поэтому для конкретной БД нужно вырабатывать свои правила для наименования объектов, чтобы уложиться в лимит по количеству символов.

SQL — язык позволяющий осуществлять запросы в БД посредством СУБД. В конкретной СУБД, язык SQL может иметь специфичную реализацию (свой диалект).

DDL и DML — подмножество языка SQL:

  • Язык DDL служит для создания и модификации структуры БД, т.е. для создания/изменения/удаления таблиц и связей.
  • Язык DML позволяет осуществлять манипуляции с данными таблиц, т.е. с ее строками. Он позволяет делать выборку данных из таблиц, добавлять новые данные в таблицы, а так же обновлять и удалять существующие данные.

В языке SQL можно использовать 2 вида комментариев (однострочный и многострочный):

Собственно, все для теории этого будет достаточно.

DDL – Data Definition Language (язык описания данных)

Для примера рассмотрим таблицу с данными о сотрудниках, в привычном для человека не являющимся программистом виде:

Табельный номер ФИО Дата рождения E-mail Должность Отдел
1000 Иванов И.И. 19.02.1955 i.ivanov@test.tt Директор Администрация
1001 Петров П.П. 03.12.1983 p.petrov@test.tt Программист ИТ
1002 Сидоров С.С. 07.06.1976 s.sidorov@test.tt Бухгалтер Бухгалтерия
1003 Андреев А.А. 17.04.1982 a.andreev@test.tt Старший программист ИТ

В данном случае столбцы таблицы имеют следующие наименования: Табельный номер, ФИО, Дата рождения, E-mail, Должность, Отдел.

Каждый из этих столбцов можно охарактеризовать по типу содержащемся в нем данных:

  • Табельный номер – целое число
  • ФИО – строка
  • Дата рождения – дата
  • E-mail – строка
  • Должность – строка
  • Отдел – строка

Тип столбца – характеристика, которая говорит о том какого рода данные может хранить данный столбец.

Для начала будет достаточно запомнить только следующие основные типы данных используемые в MS SQL:

Значение Обозначение в MS SQL Описание
Строка переменной длины varchar(N)
и
nvarchar(N)
При помощи числа N, мы можем указать максимально возможную длину строки для соответствующего столбца. Например, если мы хотим сказать, что значение столбца «ФИО» может содержать максимум 30 символов, то необходимо задать ей тип nvarchar(30).
Отличие varchar от nvarchar заключается в том, что varchar позволяет хранить строки в формате ASCII, где один символ занимает 1 байт, а nvarchar хранит строки в формате Unicode, где каждый символ занимает 2 байта.
Тип varchar стоит использовать только в том случае, если вы на 100% уверены, что в данном поле не потребуется хранить Unicode символы. Например, varchar можно использовать для хранения адресов электронной почты, т.к. они обычно содержат только ASCII символы.
Строка фиксированной длины char(N)
и
nchar(N)
От строки переменной длины данный тип отличается тем, что если длина строка меньше N символов, то она всегда дополняется справа до длины N пробелами и сохраняется в БД в таком виде, т.е. в базе данных она занимает ровно N символов (где один символ занимает 1 байт для char и 2 байта для типа nchar). На моей практике данный тип очень редко находит применение, а если и используется, то он используется в основном в формате char(1), т.е. когда поле определяется одним символом.
Целое число int Данный тип позволяет нам использовать в столбце только целые числа, как положительные, так и отрицательные. Для справки (сейчас это не так актуально для нас) – диапазон чисел который позволяет тип int от -2 147 483 648 до 2 147 483 647. Обычно это основной тип, который используется для задания идентификаторов.
Вещественное или действительное число float Если говорить простым языком, то это числа, в которых может присутствовать десятичная точка (запятая).
Дата date Если в столбце необходимо хранить только Дату, которая состоит из трех составляющих: Числа, Месяца и Года. Например, 15.02.2014 (15 февраля 2014 года). Данный тип можно использовать для столбца «Дата приема», «Дата рождения» и т.п., т.е. в тех случаях, когда нам важно зафиксировать только дату, или, когда составляющая времени нам не важна и ее можно отбросить или если она не известна.
Время time Данный тип можно использовать, если в столбце необходимо хранить только данные о времени, т.е. Часы, Минуты, Секунды и Миллисекунды. Например, 17:38:31.3231603
Например, ежедневное «Время отправления рейса».
Дата и время datetime Данный тип позволяет одновременно сохранить и Дату, и Время. Например, 15.02.2014 17:38:31.323
Для примера это может быть дата и время какого-нибудь события.
Флаг bit Данный тип удобно применять для хранения значений вида «Да»/«Нет», где «Да» будет сохраняться как 1, а «Нет» будет сохраняться как 0.

Так же значение поля, в том случае если это не запрещено, может быть не указано, для этой цели используется ключевое слово NULL.

Для выполнения примеров создадим тестовую базу под названием Test.

Простую базу данных (без указания дополнительных параметров) можно создать, выполнив следующую команду:

Удалить базу данных можно командой (стоит быть очень осторожным с данной командой):

Для того, чтобы переключиться на нашу базу данных, можно выполнить команду:

Или же выберите базу данных Test в выпадающем списке в области меню SSMS. При работе мною чаще используется именно этот способ переключения между базами.

Теперь в нашей БД мы можем создать таблицу используя описания в том виде как они есть, используя пробелы и символы кириллицы:

В данном случае нам придется заключать имена в квадратные скобки […].

Но в базе данных для большего удобства все наименования объектов лучше задавать на латинице и не использовать в именах пробелы. В MS SQL обычно в данном случае каждое слово начинается с прописной буквы, например, для поля «Табельный номер», мы могли бы задать имя PersonnelNumber. Так же в имени можно использовать цифры, например, PhoneNumber1.

На заметку
В некоторых СУБД более предпочтительным может быть следующий формат наименований «PHONE_NUMBER», например, такой формат часто используется в БД ORACLE. Естественно при задании имя поля желательно чтобы оно не совпадало с ключевыми словами используемые в СУБД.

По этой причине можете забыть о синтаксисе с квадратными скобками и удалить таблицу [Сотрудники]:

Например, таблицу с сотрудниками можно назвать «Employees», а ее полям можно задать следующие наименования:

  • ID – Табельный номер (Идентификатор сотрудника)
  • Name – ФИО
  • Birthday – Дата рождения
  • Email – E-mail
  • Position – Должность
  • Department – Отдел

Очень часто для наименования поля идентификатора используется слово ID.

Теперь создадим нашу таблицу:

Для того, чтобы задать обязательные для заполнения столбцы, можно использовать опцию NOT NULL.

Для уже существующей таблицы поля можно переопределить при помощи следующих команд:

На заметку
Общая концепция языка SQL для большинства СУБД остается одинаковой (по крайней мере, об этом я могу судить по тем СУБД, с которыми мне довелось поработать). Отличие DDL в разных СУБД в основном заключаются в типах данных (здесь могут отличаться не только их наименования, но и детали их реализации), так же может немного отличаться и сама специфика реализации языка SQL (т.е. суть команд одна и та же, но могут быть небольшие различия в диалекте, увы, но одного стандарта нет). Владея основами SQL вы легко сможете перейти с одной СУБД на другую, т.к. вам в данном случае нужно будет только разобраться в деталях реализации команд в новой СУБД, т.е. в большинстве случаев достаточно будет просто провести аналогию.

Чтобы не быть голословным, приведу несколько примеров тех же команд для СУБД ORACLE:

Для ORACLE есть отличия в плане реализации типа varchar2, его кодировка зависит настроек БД и текст может сохраняться, например, в кодировке UTF-8. Помимо этого длину поля в ORACLE можно задать как в байтах, так и в символах, для этого используются дополнительные опции BYTE и CHAR, которые указываются после длины поля, например:

Какая опция будет использоваться по умолчанию BYTE или CHAR, в случае простого указания в ORACLE типа varchar2(30), зависит от настроек БД, так же она иногда может задаваться в настройках IDE. В общем порой можно легко запутаться, поэтому в случае ORACLE, если используется тип varchar2 (а это здесь порой оправдано, например, при использовании кодировки UTF-8) я предпочитаю явно прописывать CHAR (т.к. обычно длину строки удобнее считать именно в символах).

Но в данном случае если в таблице уже есть какие-нибудь данные, то для успешного выполнения команд необходимо, чтобы во всех строках таблицы поля ID и Name были обязательно заполнены. Продемонстрируем это на примере, вставим в таблицу данные в поля ID, Position и Department, это можно сделать следующим скриптом:

В данном случае, команда INSERT также выдаст ошибку, т.к. при вставке мы не указали значения обязательного поля Name.
В случае, если бы у нас в первоначальной таблице уже имелись эти данные, то команда «ALTER TABLE Employees ALTER COLUMN ID int NOT NULL» выполнилась бы успешно, а команда «ALTER TABLE Employees ALTER COLUMN Name int NOT NULL» выдала сообщение об ошибке, что в поле Name имеются NULL (не указанные) значения.

Добавим значения для полю Name и снова зальем данные:

Так же опцию NOT NULL можно использовать непосредственно при создании новой таблицы, т.е. в контексте команды CREATE TABLE.

Сначала удалим таблицу при помощи команды:

Теперь создадим таблицу с обязательными для заполнения столбцами ID и Name:

Можно также после имени столбца написать NULL, что будет означать, что в нем будут допустимы NULL-значения (не указанные), но этого делать не обязательно, так как данная характеристика подразумевается по умолчанию.

Если требуется наоборот сделать существующий столбец необязательным для заполнения, то используем следующий синтаксис команды:

Так же данной командой мы можем изменить тип поля на другой совместимый тип, или же изменить его длину. Для примера давайте расширим поле Name до 50 символов:

Первичный ключ

При создании таблицы желательно, чтобы она имела уникальный столбец или же совокупность столбцов, которая уникальна для каждой ее строки – по данному уникальному значению можно однозначно идентифицировать запись. Такое значение называется первичным ключом таблицы. Для нашей таблицы Employees таким уникальным значением может быть столбец ID (который содержит «Табельный номер сотрудника» — пускай в нашем случае данное значение уникально для каждого сотрудника и не может повторяться).

Создать первичный ключ к уже существующей таблице можно при помощи команды:

Где «PK_Employees» это имя ограничения, отвечающего за первичный ключ. Обычно для наименования первичного ключа используется префикс «PK_» после которого идет имя таблицы.

Если первичный ключ состоит из нескольких полей, то эти поля необходимо перечислить в скобках через запятую:

Стоит отметить, что в MS SQL все поля, которые входят в первичный ключ, должны иметь характеристику NOT NULL.

Так же первичный ключ можно определить непосредственно при создании таблицы, т.е. в контексте команды CREATE TABLE. Удалим таблицу:

А затем создадим ее, используя следующий синтаксис:

После создания зальем в таблицу данные:

Если первичный ключ в таблице состоит только из значений одного столбца, то можно использовать следующий синтаксис:

На самом деле имя ограничения можно и не задавать, в этом случае ему будет присвоено системное имя (наподобие «PK__Employee__3214EC278DA42077»):

Но я бы рекомендовал для постоянных таблиц всегда явно задавать имя ограничения, т.к. по явно заданному и понятному имени с ним впоследствии будет легче проводить манипуляции, например, можно произвести его удаление:

Но такой краткий синтаксис, без указания имен ограничений, удобно применять при создании временных таблиц БД (имя временной таблицы начинается с # или ##), которые после использования будут удалены.

Подытожим

Немного про временные таблицы

Вырезка из MSDN. В MS SQL Server существует два вида временных таблиц: локальные (#) и глобальные (##). Локальные временные таблицы видны только их создателям до завершения сеанса соединения с экземпляром SQL Server, как только они впервые созданы. Локальные временные таблицы автоматически удаляются после отключения пользователя от экземпляра SQL Server. Глобальные временные таблицы видны всем пользователям в течение любых сеансов соединения после создания этих таблиц и удаляются, когда все пользователи, ссылающиеся на эти таблицы, отключаются от экземпляра SQL Server.

Временные таблицы создаются в системной базе tempdb, т.е. создавая их мы не засоряем основную базу, в остальном же временные таблицы полностью идентичны обычным таблицам, их так же можно удалить при помощи команды DROP TABLE. Чаще используются локальные (#) временные таблицы.

Для создания временной таблицы можно использовать команду CREATE TABLE:

Так как временная таблица в MS SQL аналогична обычной таблице, ее соответственно так же можно удалить самому командой DROP TABLE:

Так же временную таблицу (как собственно и обычную таблицу) можно создать и сразу заполнить данными возвращаемые запросом используя синтаксис SELECT … INTO:

На заметку
В разных СУБД реализация временных таблиц может отличаться. Например, в СУБД ORACLE и Firebird структура временных таблиц должна быть определена заранее командой CREATE GLOBAL TEMPORARY TABLE с указанием специфики хранения в ней данных, дальше уже пользователь видит ее среди основных таблиц и работает с ней как с обычной таблицей.

Нормализация БД – дробление на подтаблицы (справочники) и определение связей

Наша текущая таблица Employees имеет недостаток в том, что в полях Position и Department пользователь может ввести любой текст, что в первую очередь чревато ошибками, так как он у одного сотрудника может указать в качестве отдела просто «ИТ», а у второго сотрудника, например, ввести «ИТ-отдел», у третьего «IT». В итоге будет непонятно, что имел ввиду пользователь, т.е. являются ли данные сотрудники работниками одного отдела, или же пользователь описался и это 3 разных отдела? А тем более, в этом случае, мы не сможем правильно сгруппировать данные для какого-то отчета, где, может требоваться показать количество сотрудников в разрезе каждого отдела.

Второй недостаток заключается в объеме хранения данной информации и ее дублированием, т.е. для каждого сотрудника указывается полное наименование отдела, что требует в БД места для хранения каждого символа из названия отдела.

Третий недостаток – сложность обновления данных полей, в случае если изменится название какой-то должности, например, если потребуется переименовать должность «Программист», на «Младший программист». В данном случае нам придется вносить изменения в каждую строчку таблицы, у которой Должность равняется «Программист».

Чтобы избежать данных недостатков и применяется, так называемая, нормализация базы данных – дробление ее на подтаблицы, таблицы справочники. Не обязательно лезть в дебри теории и изучать что из себя представляют нормальные формы, достаточно понимать суть нормализации.

Давайте создадим 2 таблицы справочники «Должности» и «Отделы», первую назовем Positions, а вторую соответственно Departments:

Заметим, что здесь мы использовали новую опцию IDENTITY, которая говорит о том, что данные в столбце ID будут нумероваться автоматически, начиная с 1, с шагом 1, т.е. при добавлении новых записей им последовательно будут присваиваться значения 1, 2, 3, и т.д. Такие поля обычно называют автоинкрементными. В таблице может быть определено только одно поле со свойством IDENTITY и обычно, но необязательно, такое поле является первичным ключом для данной таблицы.

На заметку
В разных СУБД реализация полей со счетчиком может делаться по своему. В MySQL, например, такое поле определяется при помощи опции AUTO_INCREMENT. В ORACLE и Firebird раньше данную функциональность можно было съэмулировать при помощи использования последовательностей (SEQUENCE). Но насколько я знаю в ORACLE сейчас добавили опцию GENERATED AS IDENTITY.

Давайте заполним эти таблицы автоматически, на основании текущих данных записанных в полях Position и Department таблицы Employees:

То же самое проделаем для таблицы Departments:

Если теперь мы откроем таблицы Positions и Departments, то увидим пронумерованный набор значений по полю ID:

ID Name
1 Бухгалтер
2 Директор
3 Программист
4 Старший программист

ID Name
1 Администрация
2 Бухгалтерия
3 ИТ

Данные таблицы теперь и будут играть роль справочников для задания должностей и отделов. Теперь мы будем ссылаться на идентификаторы должностей и отделов. В первую очередь создадим новые поля в таблице Employees для хранения данных идентификаторов:

Тип ссылочных полей должен быть каким же, как и в справочниках, в данном случае это int.

Так же добавить в таблицу сразу несколько полей можно одной командой, перечислив поля через запятую:

Теперь пропишем ссылки (ссылочные ограничения — FOREIGN KEY) для этих полей, для того чтобы пользователь не имел возможности записать в данные поля, значения, отсутствующие среди значений ID находящихся в справочниках.

И то же самое сделаем для второго поля:

Теперь пользователь в данные поля сможет занести только значения ID из соответствующего справочника. Соответственно, чтобы использовать новый отдел или должность, он первым делом должен будет добавить новую запись в соответствующий справочник. Т.к. должности и отделы теперь хранятся в справочниках в одном единственном экземпляре, то чтобы изменить название, достаточно изменить его только в справочнике.

Имя ссылочного ограничения, обычно является составным, оно состоит из префикса «FK_», затем идет имя таблицы и после знака подчеркивания идет имя поля, которое ссылается на идентификатор таблицы-справочника.

Идентификатор (ID) обычно является внутренним значением, которое используется только для связей и какое значение там хранится, в большинстве случаев абсолютно безразлично, поэтому не нужно пытаться избавиться от дырок в последовательности чисел, которые возникают по ходу работы с таблицей, например, после удаления записей из справочника.

Так же в некоторых случаях ссылку можно организовать по нескольким полям:

В данном случае в таблице «таблица_справочник» первичный ключ представлен комбинацией из нескольких полей (поле1, поле2,…).

Собственно, теперь обновим поля PositionID и DepartmentID значениями ID из справочников. Воспользуемся для этой цели DML командой UPDATE:

Посмотрим, что получилось, выполнив запрос:

ID Name Birthday Email Position Department PositionID DepartmentID
1000 Иванов И.И. NULL NULL Директор Администрация 2 1
1001 Петров П.П. NULL NULL Программист ИТ 3 3
1002 Сидоров С.С. NULL NULL Бухгалтер Бухгалтерия 1 2
1003 Андреев А.А. NULL NULL Старший программист ИТ 4 3

Всё, поля PositionID и DepartmentID заполнены соответствующие должностям и отделам идентификаторами надобности в полях Position и Department в таблице Employees теперь нет, можно удалить эти поля:

Теперь таблица у нас приобрела следующий вид:

ID Name Birthday Email PositionID DepartmentID
1000 Иванов И.И. NULL NULL 2 1
1001 Петров П.П. NULL NULL 3 3
1002 Сидоров С.С. NULL NULL 1 2
1003 Андреев А.А. NULL NULL 4 3

Т.е. мы в итоге избавились от хранения избыточной информации. Теперь, по номерам должности и отдела можем однозначно определить их названия, используя значения в таблицах-справочниках:

ID Name PositionName DepartmentName
1000 Иванов И.И. Директор Администрация
1001 Петров П.П. Программист ИТ
1002 Сидоров С.С. Бухгалтер Бухгалтерия
1003 Андреев А.А. Старший программист ИТ

В инспекторе объектов мы можем увидеть все объекты, созданные для в данной таблицы. Отсюда же можно производить разные манипуляции с данными объектами – например, переименовывать или удалять объекты.

Так же стоит отметить, что таблица может ссылаться сама на себя, т.е. можно создать рекурсивную ссылку. Для примера добавим в нашу таблицу с сотрудниками еще одно поле ManagerID, которое будет указывать на сотрудника, которому подчиняется данный сотрудник. Создадим поле:

В данном поле допустимо значение NULL, поле будет пустым, если, например, над сотрудником нет вышестоящих.

Теперь создадим FOREIGN KEY на таблицу Employees:

Давайте, теперь создадим диаграмму и посмотрим, как выглядят на ней связи между нашими таблицами:

В результате мы должны увидеть следующую картину (таблица Employees связана с таблицами Positions и Depertments, а так же ссылается сама на себя):

Напоследок стоит сказать, что ссылочные ключи могут включать дополнительные опции ON DELETE CASCADE и ON UPDATE CASCADE, которые говорят о том, как вести себя при удалении или обновлении записи, на которую есть ссылки в таблице-справочнике. Если эти опции не указаны, то мы не можем изменить ID в таблице справочнике у той записи, на которую есть ссылки из другой таблицы, так же мы не сможем удалить такую запись из справочника, пока не удалим все строки, ссылающиеся на эту запись или, же обновим в этих строках ссылки на другое значение.

Для примера пересоздадим таблицу с указанием опции ON DELETE CASCADE для FK_Employees_DepartmentID:

Удалим отдел с идентификатором 3 из таблицы Departments:

Посмотрим на данные таблицы Employees:

ID Name Birthday Email PositionID DepartmentID ManagerID
1000 Иванов И.И. 1955-02-19 NULL 2 1 NULL
1002 Сидоров С.С. 1976-06-07 NULL 1 2 1000

Как видим, данные по отделу 3 из таблицы Employees так же удалились.

Опция ON UPDATE CASCADE ведет себя аналогично, но действует она при обновлении значения ID в справочнике. Например, если мы поменяем ID должности в справочнике должностей, то в этом случае будет производиться обновление DepartmentID в таблице Employees на новое значение ID которое мы задали в справочнике. Но в данном случае это продемонстрировать просто не получится, т.к. у колонки ID в таблице Departments стоит опция IDENTITY, которая не позволит нам выполнить следующий запрос (сменить идентификатор отдела 3 на 30):

Главное понять суть этих 2-х опций ON DELETE CASCADE и ON UPDATE CASCADE. Я применяю эти опции очень в редких случаях и рекомендую хорошо подумать, прежде чем указывать их в ссылочном ограничении, т.к. при нечаянном удалении записи из таблицы справочника это может привести к большим проблемам и создать цепную реакцию.

Восстановим отдел 3:

Полностью очистим таблицу Employees при помощи команды TRUNCATE TABLE:

И снова перезальем в нее данные используя предыдущую команду INSERT:

Подытожим

Прочие ограничения – UNIQUE, DEFAULT, CHECK

При помощи ограничения UNIQUE можно сказать что значения для каждой строки в данном поле или в наборе полей должно быть уникальным. В случае таблицы Employees, такое ограничение мы можем наложить на поле Email. Только предварительно заполним Email значениями, если они еще не определены:

А теперь можно наложить на это поле ограничение-уникальности:

Теперь пользователь не сможет внести один и тот же E-Mail у нескольких сотрудников.

Ограничение уникальности обычно именуется следующим образом – сначала идет префикс «UQ_», далее название таблицы и после знака подчеркивания идет имя поля, на которое накладывается данное ограничение.

Соответственно если уникальной в разрезе строк таблицы должна быть комбинация полей, то перечисляем их через запятую:

При помощи добавления к полю ограничения DEFAULT мы можем задать значение по умолчанию, которое будет подставляться в случае, если при вставке новой записи данное поле не будет перечислено в списке полей команды INSERT. Данное ограничение можно задать непосредственно при создании таблицы.

Давайте добавим в таблицу Employees новое поле «Дата приема» и назовем его HireDate и скажем что значение по умолчанию у данного поля будет текущая дата:

Или если столбец HireDate уже существует, то можно использовать следующий синтаксис:

Здесь я не указал имя ограничения, т.к. в случае DEFAULT у меня сложилось мнение, что это не столь критично. Но если делать по-хорошему, то, думаю, не нужно лениться и стоит задать нормальное имя. Делается это следующим образом:

Та как данного столбца раньше не было, то при его добавлении в каждую запись в поле HireDate будет вставлено текущее значение даты.

При добавлении новой записи, текущая дата так же будет вставлена автоматом, конечно если мы ее явно не зададим, т.е. не укажем в списке столбцов. Покажем это на примере, не указав поле HireDate в перечне добавляемых значений:

Посмотрим, что получилось:

ID Name Birthday Email PositionID DepartmentID ManagerID HireDate
1000 Иванов И.И. 1955-02-19 i.ivanov@test.tt 2 1 NULL 2015-04-08
1001 Петров П.П. 1983-12-03 p.petrov@test.tt 3 4 1003 2015-04-08
1002 Сидоров С.С. 1976-06-07 s.sidorov@test.tt 1 2 1000 2015-04-08
1003 Андреев А.А. 1982-04-17 a.andreev@test.tt 4 3 1000 2015-04-08
1004 Сергеев С.С. NULL s.sergeev@test.tt NULL NULL NULL 2015-04-08

Проверочное ограничение CHECK используется в том случае, когда необходимо осуществить проверку вставляемых в поле значений. Например, наложим данное ограничение на поле табельный номер, которое у нас является идентификатором сотрудника (ID). При помощи данного ограничения скажем, что табельные номера должны иметь значение от 1000 до 1999:

Ограничение обычно именуется так же, сначала идет префикс «CK_», затем имя таблицы и имя поля, на которое наложено это ограничение.

Попробуем вставить недопустимую запись для проверки, что ограничение работает (мы должны получить соответствующую ошибку):

А теперь изменим вставляемое значение на 1500 и убедимся, что запись вставится:

Можно так же создать ограничения UNIQUE и CHECK без указания имени:

Но это не очень хорошая практика и лучше задавать имя ограничения в явном виде, т.к. чтобы разобраться потом, что будет сложнее, нужно будет открывать объект и смотреть, за что он отвечает.

При хорошем наименовании много информации об ограничении можно узнать непосредственно по его имени.

И, соответственно, все эти ограничения можно создать сразу же при создании таблицы, если ее еще нет. Удалим таблицу:

И пересоздадим ее со всеми созданными ограничениями одной командой CREATE TABLE:

Напоследок вставим в таблицу наших сотрудников:

Немного про индексы, создаваемые при создании ограничений PRIMARY KEY и UNIQUE

Как можно увидеть на скриншоте выше, при создании ограничений PRIMARY KEY и UNIQUE автоматически создались индексы с такими же названиями (PK_Employees и UQ_Employees_Email). По умолчанию индекс для первичного ключа создается как CLUSTERED, а для всех остальных индексов как NONCLUSTERED. Стоит сказать, что понятие кластерного индекса есть не во всех СУБД. Таблица может иметь только один кластерный (CLUSTERED) индекс. CLUSTERED – означает, что записи таблицы будут сортироваться по этому индексу, так же можно сказать, что этот индекс имеет непосредственный доступ ко всем данным таблицы. Это так сказать главный индекс таблицы. Если сказать еще грубее, то это индекс, прикрученный к таблице. Кластерный индекс – это очень мощное средство, которое может помочь при оптимизации запросов, пока просто запомним это. Если мы хотим сказать, чтобы кластерный индекс использовался не в первичном ключе, а для другого индекса, то при создании первичного ключа мы должны указать опцию NONCLUSTERED:

Для примера сделаем индекс ограничения PK_Employees некластерным, а индекс ограничения UQ_Employees_Email кластерным. Первым делом удалим данные ограничения:

А теперь создадим их с опциями CLUSTERED и NONCLUSTERED:

Теперь, выполнив выборку из таблицы Employees, мы увидим, что записи отсортировались по кластерному индексу UQ_Employees_Email:

ID Name Birthday Email PositionID DepartmentID HireDate
1003 Андреев А.А. 1982-04-17 a.andreev@test.tt 4 3 2015-04-08
1000 Иванов И.И. 1955-02-19 i.ivanov@test.tt 2 1 2015-04-08
1001 Петров П.П. 1983-12-03 p.petrov@test.tt 3 3 2015-04-08
1002 Сидоров С.С. 1976-06-07 s.sidorov@test.tt 1 2 2015-04-08

До этого, когда кластерным индексом был индекс PK_Employees, записи по умолчанию сортировались по полю ID.

Но в данном случае это всего лишь пример, который показывает суть кластерного индекса, т.к. скорее всего к таблице Employees будут делаться запросы по полю ID и в каких-то случаях, возможно, она сама будет выступать в роли справочника.

Для справочников обычно целесообразно, чтобы кластерный индекс был построен по первичному ключу, т.к. в запросах мы часто ссылаемся на идентификатор справочника для получения, например, наименования (Должности, Отдела). Здесь вспомним, о чем я писал выше, что кластерный индекс имеет прямой доступ к строкам таблицы, а отсюда следует, что мы можем получить значение любого столбца без дополнительных накладных расходов.

Кластерный индекс выгодно применять к полям, по которым выборка идет наиболее часто.

Иногда в таблицах создают ключ по суррогатному полю, вот в этом случае бывает полезно сохранить опцию CLUSTERED индекс для более подходящего индекса и указать опцию NONCLUSTERED при создании суррогатного первичного ключа.

Подытожим

На данном этапе мы познакомились со всеми видами ограничений, в их самом простом виде, которые создаются командой вида «ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения …»:

  • PRIMARY KEY – первичный ключ;
  • FOREIGN KEY – настройка связей и контроль ссылочной целостности данных;
  • UNIQUE – позволяет создать уникальность;
  • CHECK – позволяет осуществлять корректность введенных данных;
  • DEFAULT – позволяет задать значение по умолчанию;
  • Так же стоит отметить, что все ограничения можно удалить, используя команду «ALTER TABLE имя_таблицы DROP CONSTRAINT имя_ограничения».

Так же мы частично затронули тему индексов и разобрали понятие кластерный (CLUSTERED) и некластерный (NONCLUSTERED) индекс.

Создание самостоятельных индексов

Под самостоятельностью здесь имеются в виду индексы, которые создаются не для ограничения PRIMARY KEY или UNIQUE.

Индексы по полю или полям можно создавать следующей командой:

Так же здесь можно указать опции CLUSTERED, NONCLUSTERED, UNIQUE, а так же можно указать направление сортировки каждого отдельного поля ASC (по умолчанию) или DESC:

При создании некластерного индекса опцию NONCLUSTERED можно отпустить, т.к. она подразумевается по умолчанию, здесь она показана просто, чтобы указать позицию опции CLUSTERED или NONCLUSTERED в команде.

Удалить индекс можно следующей командой:

Простые индексы так же, как и ограничения, можно создать в контексте команды CREATE TABLE.

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

И пересоздадим ее со всеми созданными ограничениями и индексами одной командой CREATE TABLE:

Напоследок вставим в таблицу наших сотрудников:

Дополнительно стоит отметить, что в некластерный индекс можно включать значения при помощи указания их в INCLUDE. Т.е. в данном случае INCLUDE-индекс чем-то будет напоминать кластерный индекс, только теперь не индекс прикручен к таблице, а необходимые значения прикручены к индексу. Соответственно, такие индексы могут очень повысить производительность запросов на выборку (SELECT), если все перечисленные поля имеются в индексе, то возможно обращений к таблице вообще не понадобится. Но это естественно повышает размер индекса, т.к. значения перечисленных полей дублируются в индексе.

Вырезка из MSDN. Общий синтаксис команды для создания индексов

Подытожим

Индексы могут повысить скорость выборки данных (SELECT), но индексы уменьшают скорость модификации данных таблицы, т.к. после каждой модификации системе будет необходимо перестроить все индексы для конкретной таблицы.

Желательно в каждом случае найти оптимальное решение, золотую середину, чтобы и производительность выборки, так и модификации данных была на должном уровне. Стратегия по созданию индексов и их количества может зависеть от многих факторов, например, насколько часто изменяются данные в таблице.

Заключение по DDL

Как можно увидеть, язык DDL не так сложен, как может показаться на первый взгляд. Здесь я смог показать практически все его основные конструкции, оперируя всего тремя таблицами.

Главное — понять суть, а остальное дело практики.

Удачи вам в освоении этого замечательного языка под названием SQL.

drop database

Description

Removes one or more databases from Adaptive Server, including archive databases.

Syntax

Parameters

is the name of a database to remove. Use sp_helpdb to get a list of databases.

Examples

Example 1

Removes the publishing database and all its contents:

Example 2

key_db is the database where the encryption key resides and col_db is the database containing the encrypted columns. Adaptive Server raises an error and fails to drop key_db. The drop of col_db succeeds. To drop both databases, drop col_db first:

Usage

When dropping an archive database, all the rows for that database are deleted from the sysaltusages table in the scratch database. This requires log space in the scratch database.

Removing a database deletes the database and all its objects, frees its storage allocation, and erases its entries from the sysdatabases and sysusages system tables in the master database.

drop database clears the suspect page entries pertaining to the dropped database from master..sysattributes.

Encrypted columns and drop database

To prevent accidental loss of keys, drop database fails if the database contains keys currently used to encrypt columns in other databases. To drop a database:

Use alter table to decrypt the columns, or modify the columns for encryption using a different key.

Drop the table or database containing the encrypted columns.

Restrictions

You must be using the master database to drop a database.

You cannot drop a database that is in use (open for reading or writing by any user).

You cannot use drop database to remove a database that is referenced by a table in another database. Determine which tables and external databases have foreign-key constraints on primary key tables in the current database, execute:

Use alter table to drop these cross-database constraints, then reissue the drop database command.

You can use drop database to remove a damaged database. If drop database does not run because the database is damaged, use dbcc dbrepair to fix the database:

You cannot drop the sybsecurity database if auditing is enabled. When auditing is disabled, only the system security officer can drop sybsecurity.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Only the database owner can execute drop database, except for the sybsecurity database, which can be dropped only by the system security officer.

Auditing

Values in event and extrainfo columns of sysaudits are:

Command or access audited

Information in extrainfo

Roles – current active roles

Keywords or options – NULL

Previous value – NULL

Current value – NULL

Other information – NULL

Proxy information – original login name, if set proxy is in effect

Иллюстрированный самоучитель по PostgreSQL

DROP DATABASE

Удаление базы данных из системы.

Имя удаляемой базы данных.

  • DROP DATABASE. Сообщение выдается при успешном удалении базы данных.
  • ERROR: user ‘пользователь’ is not allowed to create/drop databases. Ошибка – команда удаления базы данных введена пользователем, не обладающим привилегиями суперпользователя. Удаление баз данных разрешено только пользователям с привилегией CREATEDB. За дополнительной информацией обращайтесь к описанию команды CREATE USER.
  • ERROR: dropdb: cannot be executed on the template database. Ошибка – попытка удаления базы данных tempi atel. Эта база данных является системной и не может удаляться пользователями.
  • ERROR: dropdb: cannot be executed on an open database. Ошибка – попытка удаления базы данных, к которой пользователь подключен в настоящий момент. При получении этой ошибке попробуйте подключиться к базе данных tempi atel и повторно введите команду удаления базы данных, с которой вы раньше работали.
  • ERROR: dropdb: database ‘база_данных’ does not exist. Ошибка – попытка удаления несуществующей базы данных.
  • ERROR: dropdb: database ‘база_данных’ Is not owned by you. Ошибка – попытка удаления базы данных, принадлежащей другому пользователю.
  • ERROR: dropdb: May not be called in a transaction block. Ошибка – попытка вызова команды DROP DATABASE в транзакциониом блоке. Перед удалением базы данных необходимо завершить все текущие транзакции.
  • NOTICE: The database directory ‘каталог’ could not be removed. Попытка удаления каталога данных завершилась неудачей. Для завершения операции каталог данных необходимо удалить вручную.

Описание

Команда DROP DATABASE удаляет базу данных из системы. При удалении базы уничтожаются все данные и информация в системном каталоге, относящиеся к базе. Попытка удаления текущей базы данных, к которой вы подключены в настоящий момент, приводит к ошибке; по этой причине бывает удобнее использовать сценарий dropdb.

Примечание
Удаление базы данных может производиться только владельцем или суперпользователем
.

Пример

Следующая команда удаляет базу данных testdb:

Что такое код drop database

CREATE DATABASE создает базу данных с указанным именем. Правила для допустимых имен баз данных приведены в разделе «3.2 Имена баз данных, таблиц, индексов, столбцов и псевдонимов». Ошибка происходит, если база данных уже существует, и Вы не определяли IF NOT EXISTS .

Базы данных в MySQL выполнены как каталоги, содержащие файлы, которые соответствуют таблицам в базе данных. Поскольку нет никаких таблиц в базе данных, когда она первоначально создана, инструкция CREATE DATABASE только создает каталог под каталогом данных MySQL.

Вы можете также создавать базы данных с помощью программы mysqladmin . Подробности в разделе «12 Клиентские скрипты и утилиты MySQL».

DROP DATABASE удаляет все таблицы в базе данных и удаляет саму базу данных. Если выполнили DROP DATABASE на символически связанной базе данных, связь и первоначальная база данных будут удалены. Будьте ОЧЕНЬ осторожны с этой командой!

DROP DATABASE возвращает число файлов, которые были удалены из каталога баз данных. Обычно это кратное трем число потому, что каждая таблица соответствует файлам .MYD , .MYI и .frm .

Команда DROP DATABASE удаляет из заданного каталога базы данных все найденные файлы со следующими расширениями:

.BAK .DAT .HSH .ISD
.ISM .ISM .MRG .MYD
.MYI .db .frm

Все подкаталоги, имена которых состоят из 2 цифр (каталоги RAID ), также будут удалены.

В MySQL Version 3.22 или позже Вы можете использовать ключевые слова IF EXISTS , чтобы предотвратить ошибку, если база данных не существует.

Вы можете также удалять базы данных с помощью команды mysqladmin . Подробности в разделе «12 Клиентские скрипты и утилиты MySQL».

CREATE TABLE создает таблицу с заданным именем в текущей базе данных. Правила для допустимых имен таблицы даны в разделе «3.2 Имена баз данных, таблиц, индексов, столбцов и псевдонимов». Ошибка происходит, если не имеется никакой текущей базы данных, или если таблица уже существует.

В MySQL Version 3.22 или позже имя таблицы может быть определено как db_name.tbl_name . Это работает независимо от того, имеется или нет текущая база данных.

В MySQL Version 3.23 или позже Вы можете использовать ключевое слово TEMPORARY , когда Вы создаете таблицу. Временная таблица будет автоматически удалена, если подключение завершается. Причем, имя связано с подключением. Это означает, что два различных подключения могут оба использовать то же самое имя временной таблицы без того, чтобы войти в противоречие друг с другом или с существующей таблицей с тем же самым именем. Существующая таблица будет скрыта, пока временная таблица не удалена.

В MySQL Version 3.23 или позже Вы можете использовать ключевые слова IF NOT EXISTS , чтобы ошибка не произошла, если таблица уже существует. Обратите внимание, что не имеется никакой проверки, что структуры таблиц идентичны.

Каждая таблица tbl_name представляется некоторыми файлами в каталоге баз данных. В случае таблиц MyISAM-типа Вы получите:

Файл Назначение
tbl_name.frm Определение таблицы (форма)
tbl_name.MYD Данные таблицы
tbl_name.MYI Индекс таблицы

Для получения большего количества информации относительно свойств различных типов столбцов обратитесь к разделу «5 Типы столбцов»:

  • Если NULL или NOT NULL не определены, столбец обрабатывается, как если бы был определен NULL .
  • Целочисленный столбец может иметь дополнительный атрибут AUTO_INCREMENT . Когда Вы вставляете значение NULL (рекомендуется) или 0 в столбец с атрибутом AUTO_INCREMENT , столбец будет установлен в значение value+1 , где value представляет собой самое большое значение для столбца в настоящее время в данной таблице. AUTO_INCREMENT -последовательности начинаются с 1 . Если Вы удаляете строку, содержащую максимальное значение для столбца AUTO_INCREMENT , значение будет многократно использоваться для таблиц ISAM или BDB , но не для таблиц типов MyISAM или InnoDB . Если Вы удаляете все строки в таблице с помощью вызова DELETE FROM table_name (без WHERE ) в режиме AUTOCOMMIT , последовательность начинается сначала для всех типов таблицы. ОБРАТИТЕ ВНИМАНИЕ: может иметься только один столбец AUTO_INCREMENT на таблицу, и он должен быть индексирован. MySQL Version 3.23 будет также работать правильно, только если столбец auto_increment имеет только положительные значения. Вставка отрицательного числа будет расценена как вставка очень большого положительного номера. Это выполнено, чтобы избежать проблем точности, когда числа переходят от положительного к отрицательному и также гарантировать, что случайно не получается столбец auto_increment, который содержит 0. Чтобы сделать MySQL совместимым с некоторыми прикладными программами ODBC, Вы можете находить последнюю вставленную строку следующим запросом:
  • Значения NULL для столбцов TIMESTAMP будут обработаны иначе, чем для других типов столбцов. Вы не можете сохранять литеральный NULL в столбце TIMESTAMP . Установка столбца в NULL приведет его к текущей (актуальной) дате и времени. Поскольку столбцы TIMESTAMP ведут себя именно так, атрибуты NULL и NOT NULL не применяются нормальным способом и игнорируются, если Вы определяете их. С другой стороны, чтобы сделать это проще для MySQL-клиентуры, сервер сообщает, что таким столбцам могут быть назначены значения NULL (это и правда так), даже при том, что TIMESTAMP никогда фактически не будет содержать значение NULL . Вы можете видеть это, когда Вы используете DESCRIBE tbl_name , чтобы получить описание Вашей таблицы. Обратите внимание, что установка столбца TIMESTAMP в 0 не устанавливает его в NULL , поскольку 0 имеющее силу значение TIMESTAMP .
  • Если никакое значение DEFAULT не определено для столбца, MySQL автоматически назначает его. Если столбец может брать как значение NULL , значением по умолчанию будет именно NULL . Если столбец объявлен как NOT NULL , значение по умолчанию зависит от типа столбца:
    • Для числовых типов, не объявленных с атрибутом AUTO_INCREMENT , значение по умолчанию 0 . Для столбцов AUTO_INCREMENT значением по умолчанию будет следующее значение в последовательности.
    • Для типов даты и времени, но не для TIMESTAMP , значением по умолчанию будет соответствующее нулевое значение для типа. Для первого в таблице столбца TIMESTAMP значением по умолчанию будет текущая (актуальная) дата и время. Подробности в разделе «5.2 Типы Date и Time».
    • Для строк, но не для ENUM , значением по умолчанию является пустая строка. Для типа ENUM первое значение перечисления.

    Значения по умолчанию должны быть константами. Это означает, например, что Вы не можете устанавливать значение по умолчанию для столбца даты в виде значения функции, типа NOW() или CURRENT_DATE .
  • KEY представляет собой синоним для INDEX .
  • В MySQL ключ UNIQUE может иметь только различные значения. Ошибка происходит, если Вы пробуете добавлять новую строку с ключом, который соответствует существующей строке.
  • PRIMARY KEY является уникальным KEY , причем все столбцы ключа должны быть определены как NOT NULL . В MySQL такой ключ именован как PRIMARY . Таблица может иметь только один PRIMARY KEY . Если Вы не имеете PRIMARY KEY , а некоторые прикладные программы просят о PRIMARY KEY в Ваших таблицах, MySQL возвратит первый ключ UNIQUE , который не имеет столбцов NULL .
  • PRIMARY KEY может быть многостолбцовым индексом. Однако, Вы не можете создавать такой индекс, использующий атрибут PRIMARY KEY в спецификации столбца. Выполнение этого отметит только один столбец как первичный. Вы должны использовать вызов PRIMARY KEY(index_col_name, . ) .
  • Если ключи PRIMARY или UNIQUE состоят только из одного столбца, и он имеет тип integer, Вы можете также обратиться к нему как _rowid (нововведение в Version 3.23.11).
  • Если Вы не назначаете имя индексу, будет назначено то же самое имя, какое имеет первый index_col_name , с факультативным суффиксом ( _2 , _3 , . ): чтобы сделать его уникальным. Вы можете видеть индексные имена для таблицы, используя SHOW INDEX FROM tbl_name . Подробности в разделе «4.5.5 Синтаксис SHOW «.
  • Только тип таблицы MyISAM поддерживает индексы на столбцах, которые могут иметь значения NULL . В других случаях Вы должны объявить такие столбцы как NOT NULL .
  • С помощью инструкции col_name(length) Вы можете определять индекс, который использует только часть столбца типа CHAR или VARCHAR . Это может сделать индексный файл намного меньше.
  • Только тип таблицы MyISAM поддерживает индексы на столбцах типов BLOB и TEXT . При помещении индекса на столбце BLOB или TEXT Вы ДОЛЖНЫ всегда определять длину индекса:
  • Когда Вы используете ORDER BY или GROUP BY со столбцом TEXT или BLOB , используется только первые max_sort_length байт. Подробности в разделе «5.3.2 Типы BLOB и TEXT «.
  • В MySQL Version 3.23.23 или позже Вы можете также создавать специальные индексы FULLTEXT . Они используются для полнотекстового поиска. Только тип таблицы MyISAM поддерживает индексы FULLTEXT . Они могут быть созданы только из столбцов типов VARCHAR и TEXT . Индексация всегда выполняется над всем столбцом, частичная индексация пока не поддержана. Подробности в разделе «9.4 Полнотекстовый поиск в MySQL».
  • Определения FOREIGN KEY , CHECK и REFERENCES фактически не делают ничего. Синтаксис для них обеспечивается только для совместимости, чтобы сделать проще портирование кода из других SQL-серверов и выполнять прикладные программы, которые создают таблицы со ссылками. Подробности в разделе «1.4.4 Функциональные возможности, отсутствующие в MySQL».
  • Каждый столбец со значением NULL берет один бит, округляя занятое им место до самого близкого байта.
  • Максимальная длина записи в байтах может быть вычислена следующим образом:
  • Опции table_options и SELECT выполнены только в MySQL Version 3.23 и выше.

    Различные типы таблиц:

    BDB или Berkeley_dbТранзакционно-безопасные таблицы с блокировкой страниц.
    HEAPДанные для этих таблиц сохранены только в памяти.
    ISAMПервоначальный драйвер таблицы.
    InnoDBТранзакционно-безопасные таблицы с блокировкой строки.
    MERGEСовокупность таблиц MyISAM, используемых как одна.
    MyISAMНовый двоично-независимый драйвер таблицы, который заменяет формат ISAM.
    Если тип таблицы определен, и этот специфический тип недоступен, MySQL выберет тип таблицы самый близкий к тому, который Вы определили. Например, если определен TYPE=BDB , но этот дистрибутив MySQL не поддерживает таблицы типа BDB , таблица будет создана как MyISAM . Другие параметры таблицы используются, чтобы оптимизировать поведение таблицы. В большинстве случаев, Вы не должны их определять. Параметры всегда работают для всех типов таблицы, если не оговорено обратное явно.
    AUTO_INCREMENTУстановить следующее значение auto_increment для Вашей таблицы (только MyISAM).
    AVG_ROW_LENGTHПриближенное значение средней длины строки для Вашей таблицы. Вы должны установить это только для больших таблиц с записями переменного размера.
    CHECKSUMУстановите это в 1, если Вы хотите, чтобы MySQL поддержал контрольную сумму для всех строк (делает таблицу немного медленнее, чтобы модифицировать, но делает проще поиск повреждений таблицы, только для MyISAM).
    COMMENT60-символьный комментарий к таблице.
    MAX_ROWSМаксимальное число строк, которое Вы планируете сохранять в таблице.
    MIN_ROWSМинимальное число строк, которое Вы планируете сохранять в таблице.
    PACK_KEYSУстановите это в 1, если Вы хотите иметь меньший индекс. Это обычно делает модификации медленнее, зато чтения быстрее (только для MyISAM и ISAM).
    PASSWORDЗашифровать файл .frm паролем. Эта опция не делает ничего в стандартной версии MySQL.
    DELAY_KEY_WRITEУстановите это в 1, если Вы хотите задержать обновление ключей таблицы до ее закрытия (только MyISAM).
    ROW_FORMATОпределяет, как строки должны быть сохранены. В настоящее время Вы можете использовать только опции DYNAMIC и STATIC для таблиц типа MyISAM.
    Когда Вы применяете таблицу MyISAM , MySQL использует результат max_rows*avg_row_length , чтобы решить насколько большой будет возникающая в результате таблица. Если Вы не определяете любые из вышеупомянутых параметров, максимальный размер для таблицы будет 4G (или 2G, если Ваша операционная система поддерживают только таблицы 2G). Причина для этого: надо уменьшить размеры указателя, чтобы сделать индекс меньше и быстрее, если Вы действительно не нуждаетесь в больших файлах. Если Вы не используете PACK_KEYS , по умолчанию будут упакованы только строки, но не числа. Если Вы используете PACK_KEYS=1 , числа будут упакованы также. При упаковке двоичных цифровых ключей MySQL использует префиксное сжатие. Это означает, что Вы получите большую пользу от этого только, если Вы имеете много чисел, которые являются теми же самыми. Префиксное сжатие означает, что каждый ключ нуждается в одном байте дополнительного пространства, чтобы указать, сколько байтов предыдущего ключа совпадают с текущим. Обратите внимание, что указатель на строку сохранен в порядке со старшим первым байтом непосредственно после ключа, чтобы улучшить сжатие. Это означает, что, если Вы имеете много равных ключей в строке, все последующие те же самые ключи будет обычно только брать 2 байта (включая указатель на строку). Сравните это с обычным случаем, где следующие ключи будут брать storage_size_for_key+pointer_size (обычно 4) байта. С другой стороны, если все ключи полностью различны, Вы будете терять 1 байт на ключ, если он не может иметь значения NULL (в этом случае, упакованная длина ключа будет сохранена в том же самом байте, который используется, чтобы отметить, что этот ключ имеет значение NULL ).
  • Если Вы определяете SELECT после инструкции CREATE , MySQL создаст новые поля для всех элементов в SELECT . Например: Это создаст таблицу типа MyISAM с тремя столбцами: a, b и c. Обратите внимание, что столбцы из инструкции SELECT будут добавлены к правой стороне таблицы. Пример: Для каждой строки в таблице foo , строка будет вставлена в область bar со значениями из foo и значениями по умолчанию для новых столбцов. CREATE TABLE . SELECT автоматически не будет создавать никаких индексов для Вас. Это должно быть выполнено преднамеренно, чтобы сделать команду настолько гибкой, насколько это вообще возможно. Если Вы хотите иметь индексы в созданной таблице, Вы должны определить их перед инструкцией SELECT : Если происходят ошибки при копировании данных в таблицу, они будут автоматически удалены. Чтобы гарантировать, что файл регистрации модификаций может использоваться, чтобы освежить первоначальные таблицы, MySQL не будет позволять параллельные вставки в течение выполнения CREATE TABLE . SELECT .
  • Опция RAID_TYPE поможет Вам обойти 2G/4G ограничение для файла данных MyISAM (но не индексного файла) на операционных системах, которые не поддерживают большие файлы. Вы можете получать также большее быстродействие, помещая каталоги RAID на различных физических дисках. RAID_TYPE будет работать с любой OS, если Вы сконфигурировали MySQL с опцией —with-raid . Пока единственный позволенный RAID_TYPE называется STRIPED (псевдонимы для него: 1 и RAID0 ). Если Вы определяете RA > для таблицы MyISAM , MyISAM создаст каталоги RAID_CHUNKS с именами 00, 01, 02 в каталоге базы данных. В каждом из этих каталогов MyISAM создаст файл table_name.MYD . При записи данных в файл данных, драйвер RAID отобразит первые RAID_CHUNKSIZE *1024 байт на первый файл, второй кусок длиной в RAID_CHUNKSIZE *1024 байт на второй и так далее.
  • UNION используется, когда Вы хотите использовать совокупность идентичных таблиц как одну целую. Это работает только с таблицами типа MERGE. Вы должны иметь привилегии SELECT , UPDATE и DELETE на таблицах, которые Вы отображаете к таблице MERGE . Все отображенные таблицы должны быть в той же самой базе данных, что и таблица MERGE .
  • В созданной таблице ключ PRIMARY будет помещен в начало, за ним расположатся ключи UNIQUE , а за ними нормальные ключи. Это помогает оптимизатору MySQL располагать по приоритетам, который ключ использовать, а также более быстро обнаруживает дублированные ключи UNIQUE .
  • Используя DATA DIRECTORY=»directory» или INDEX DIRECTORY=»directory» Вы можете определять, где драйвер таблицы должен поместить файлы таблицы и индекса. Это работает только для таблиц MyISAM в MySQL 4.0, когда Вы не используете опцию —skip-symlink .

В некоторых случаях MySQL тихо изменяет спецификацию столбца, заданную в инструкции CREATE TABLE . Это может также происходить с ALTER TABLE :

  • VARCHAR -столбцы с длиной меньше, чем четыре, будут изменены на CHAR .
  • Если любой столбец в таблице имеет переменную длину, вся строка будет в результате переменной длины. Следовательно, если таблица содержит любые столбцы переменной длины ( VARCHAR , TEXT или BLOB ), все столбцы CHAR длиной более, чем три символа, будут изменены на VARCHAR . В MySQL VARCHAR всего лишь иной способ хранить символы. MySQL выполняет это преобразование потому, что оно экономит место и делает операции с таблицей быстрее.
  • TIMESTAMP должен быть четный и в диапазоне от 2 до 14. Если Вы определяете 0 или больше, чем 14, размер принудительно выставится в 14. Нечетно-оцененные размеры в диапазоне от 1 до 13 принудительно выставятся в следующее более высокое четное число.
  • Вы не можете сохранять литеральный NULL в столбце TIMESTAMP . Установка его в NULL реально устанавливает его к текущей (актуальной) дате и времени. Потому, что столбцы TIMESTAMP ведут себя именно так, атрибуты NULL и NOT NULL не применяются нормальным способом и игнорируются, если Вы определяете их. DESCRIBE tbl_name всегда сообщает, что столбцу TIMESTAMP может быть назначено значение NULL .
  • MySQL отображает некоторые типы столбца, используемые другими SQL-серверами баз данных к типам MySQL. Подробности в разделе «5.5 Использование типов столбцов из других баз данных».

Если Вы хотите видеть, использовал или нет MySQL столбец не того типа, какой Вы определили, выдайте инструкцию DESCRIBE tbl_name после создания или изменения Вашей таблицы.

Некоторые другие изменения типа столбца могут происходить, если Вы сжимаете таблицу, используя myisampack .

ALTER TABLE позволяет Вам изменять структуру существующей таблицы. Например, Вы можете добавлять или удалять столбцы, создавать или уничтожать индексы, изменять тип существующих столбцов, переименовывать столбцы или непосредственно таблицу. Вы можете также изменять комментарий для таблицы и тип таблицы. Подробности в разделе «7.3 Синтаксис CREATE TABLE «.

Если Вы используете ALTER TABLE , чтобы изменить спецификацию столбца, но DESCRIBE tbl_name указывает, что Ваш столбец не был изменен, возможно, что MySQL игнорировал Вашу модификацию по одной из причин, описанных в разделе «7.3.1 Тихие изменения спецификации столбца». Например, если Вы пробуете изменять столбец VARCHAR на CHAR , MySQL будет все же использовать именно VARCHAR , если таблица содержит другие столбцы переменной длины.

ALTER TABLE работает, делая временную копию первоначальной таблицы. Перенастройка выполняется на копии, затем первоначальная таблица будет удалена, а новая переименована в старую. Это выполнено таким способом, чтобы все модификации были автоматически переназначены к новой таблице. В то время, как выполняется ALTER TABLE , первоначальная таблица читаема другой клиентурой. Модификации и записи остановлены, пока новая таблица не будет готова.

Обратите внимание, что, если Вы используете любую другую опцию ALTER TABLE , чем RENAME , MySQL будет всегда создавать временную таблицу, даже если данные не должны быть строго скопированы (подобно тому, когда Вы изменяете имя столбца). Авторы планируют исправить это в будущем, но поскольку ALTER TABLE вызывается относительно редко, это не имеет высокого приоритета в TODO.

  • Чтобы использовать ALTER TABLE , Вы должны иметь привилегии ALTER , INSERT и CREATE на таблице.
  • IGNORE представляет собой MySQL-расширение ANSI SQL92. Это управляет тем, как работает ALTER TABLE , если имеются дубликаты на уникальных ключах в новой таблице. Если IGNORE не определен, копия прервана и прокручена обратно. Если IGNORE определен, то для строк с дубликатами на уникальном ключе используется только первая строка, другие удалены.
  • Вы можете выдавать несколько команд ADD , ALTER , DROP и CHANGE в одной инструкции ALTER TABLE . Это MySQL-расширение ANSI SQL92, который позволяет только одну команду на инструкцию ALTER TABLE .
  • CHANGE col_name , DROP col_name и DROP INDEX тоже MySQL-расширения ANSI SQL92.
  • MODIFY представляет собой Oracle-расширение ALTER TABLE .
  • Факультативное слово COLUMN реально не работает и может быть свободно пропущено.
  • Если Вы используете ALTER TABLE tbl_name RENAME TO new_name без любых других параметров, MySQL просто переименовывает файлы, которые соответствуют таблице tbl_name . Нет никакой потребности создавать временную таблицу. Подробности в разделе «7.5 Синтаксис RENAME TABLE «.
  • Начиная с MySQL 4.0 вышеупомянутое свойство может быть активизировано явно. ALTER TABLE . DISABLE KEYS предписывает прекратить модифицировать неуникальные индексы для таблиц MyISAM . ALTER TABLE . ENABLE KEYS затем должен использоваться, чтобы освежить отсутствующие индексы. MySQL делает это специальным алгоритмом, который намного быстрее, чем вставка ключей один за другим, блокировка обработки ключей может давать значительное ускорение работы системы на оптовых вставках.
  • Предложение create_definition используют тот же самый синтаксис для ADD и CHANGE , что и CREATE TABLE . Обратите внимание, что этот синтаксис включает имя столбца, а не только тип столбца. Подробности в разделе «7.3 Синтаксис CREATE TABLE «.
  • Вы можете переименовывать столбец, используя CHANGE old_col_name create_definition . Чтобы сделать так, определите старые и новые имена столбца и тип, который столбец в настоящее время имеет. Например, чтобы переименовать столбец типа INTEGER из a в b , Вы можете сделать: Если Вы хотите изменять тип столбца, но не имя, синтаксис CHANGE все еще требует имен двух столбцов, даже если это тот же самый столбец: Однако, начиная с MySQL Version 3.22.16a, Вы можете также использовать MODIFY , чтобы изменить тип столбца без переименования:
  • Если Вы используете CHANGE или MODIFY , чтобы сократить столбец, для которого индекс существует как часть столбца (например, если Вы имеете индекс на первых 10 символах столбца VARCHAR ), Вы не можете делать столбец короче, чем число символов, которые индексированы.
  • Когда Вы изменяете тип столбца, используя CHANGE или MODIFY , MySQL пробует преобразовывать данные к новому типу.
  • В MySQL Version 3.22 или позже Вы можете использовать FIRST или ADD . AFTER col_name , чтобы добавить столбец в специфической позиции внутри строки таблиц. Значение по умолчанию должно добавить столбец в последнюю позицию.
  • ALTER COLUMN определяет новое значение по умолчанию для столбца или удаляет старое значение по умолчанию. Если старое значение по умолчанию удалено, и столбец может быть NULL , новое значение по умолчанию будет NULL . Если столбец не может быть NULL , MySQL назначает значение по умолчанию, как описано в разделе «7.3 Синтаксис CREATE TABLE «.
  • DROP INDEX удаляет индекс. Это расширение MySQL для ANSI SQL92. Подробности в разделе «7.8 Синтаксис DROP INDEX «.
  • Если столбцы удалены из таблицы, столбцы будут также удалены из любого индекса, частью которого они являются. Если все столбцы, которые составляют индекс, удалены, то будет удален и сам индекс.
  • Если таблица содержит только один столбец, он не может быть удален.
  • DROP PRIMARY KEY удаляет первичный индекс. Если такой индекс не существует, это удаляет первый UNIQUE -индекс в таблице (MySQL отмечает первый ключ UNIQUE как PRIMARY KEY , если никакой PRIMARY KEY не был определен явно). Если Вы добавляете UNIQUE INDEX или PRIMARY KEY к таблице, он будет сохранен перед любым не- UNIQUE индексом так, чтобы MySQL мог обнаружить двойные ключи как можно раньше.
  • ORDER BY позволяет Вам создавать новую таблицу со строками в специфическом порядке. Обратите внимание, что таблица не будет оставаться в этом порядке после вставок и удалений. В некоторых случаях это может сделать сортировку проще для MySQL, если таблица сделана так, как Вы желаете упорядочить ее позже. Эта опция главным образом полезна, когда Вы знаете, что Вы обычно собираетесь сделать запрос строк в некотором порядке. Используя эту опцию после больших изменений для таблицы, Вы можете получить более высокую эффективность.
  • Если Вы используете ALTER TABLE на таблице MyISAM , все неуникальные индексы будут созданы в отдельном пакете (подобно тому, как это происходит в REPAIR ). Это ускорит ALTER TABLE , когда Вы имеете много индексов.

Имеется пример, который показывает, некоторые из способов применения ALTER TABLE . Мы начинаем с таблицы t1 , которая создана как показано ниже:

Чтобы переименовывать таблицу из t1 в t2 , надо:

Чтобы изменить столбец a с типа INTEGER на тип TINYINT NOT NULL (имя оставим прежнее), и столбец b переделать с типа CHAR(10) на CHAR(20) с одновременным переименованием b в c , надо сделать:

Чтобы добавить новый столбец типа TIMESTAMP с именем d , надо сделать:

Чтобы добавить индекс на столбце d , и сделать столбец a первичным ключом, надо:

Чтобы удалить столбец c :

Чтобы добавить новый целочисленный столбец c с поддержкой AUTO_INCREMENT , надо:

Обратите внимание, что мы индексировали c потому, что столбец AUTO_INCREMENT должен быть индексирован, а также мы объявляем c как NOT NULL потому, что индексированные столбцы не могут быть NULL .

Когда Вы добавляете столбец AUTO_INCREMENT , значения столбца будут заполнены числами последовательности для Вас автоматически. Вы можете устанавливать первый номер последовательности, выполняя SET INSERT_ > перед ALTER TABLE или используя опцию AUTO_INCREMENT=# .

С таблицами MyISAM, если Вы не изменяете столбец на AUTO_INCREMENT , номер последовательности не будут изменен. Если Вы удаляете столбец с поддержкой AUTO_INCREMENT , а затем добавляете другой с AUTO_INCREMENT , числа снова начнутся с 1.

Переименование выполняется атомно, что означает, что никакой другой поток не может обращаться к любой из таблиц в то время, как выполняется переименование. Это делает возможным заменить таблицу на пустую:

Переименование выполняется слева направо, это означает, что, если Вы хотите менять два имени таблицы, Вы должны сделать так:

Таблицы можно не только переименовывать, но еще и перемещать между разными базами данных. Например:

Когда Вы выполняете RENAME , Вы не можете иметь блокированные таблицы или активные транзакции. Вы должны также иметь привилегии ALTER и DROP на первоначальной таблице и привилегии CREATE и INSERT на новой таблице.

Если MySQL сталкивается с любыми ошибками в процессе переименования, он будет делать обратное переименование для всех переименованных таблиц, чтобы вернуть все обратно в первоначальное состояние.

DROP TABLE удаляет одну или большее количество таблиц. Все данные таблицы и определение таблицы будут удалены , так что будьте внимательны с этой командой!

В MySQL Version 3.22 или позже Вы можете использовать ключевые слова IF EXISTS , чтобы предотвратить ошибку для таблиц, которых нет.

RESTRICT и CASCADE ничего не делают и нужны лишь для совместимости с другими СУБД.

ОБРАТИТЕ ВНИМАНИЕ : DROP TABLE не транзакционно-безопасна и автоматически завершает любые активные транзакции.

Инструкция CREATE INDEX ничего не делает до Version 3.22. В Version 3.22 или позже CREATE INDEX она отображена к инструкции ALTER TABLE , чтобы создать индексы. Подробности в разделе «7.4 Синтаксис ALTER TABLE «.

Обычно Вы создаете все индексы на таблице во время непосредственного создания таблицы с помощью вызова CREATE TABLE . Подробности в разделе «7.3 Синтаксис CREATE TABLE «. CREATE INDEX позволяет Вам добавлять индексы к существующим в базе таблицам.

Список столбцов в форме (col1,col2. ) создает индекс по многим столбцам. Индексные значения будут сформированы, связывая значения из заданных для индексации столбцов.

Для столбцов CHAR и VARCHAR индексы могут быть созданы с использованием только части столбца, применяя синтаксис col_name(length) . На столбцах BLOB и TEXT длина требуется обязательно. Инструкция, показанная ниже, создает индекс с использованием первых 10 символов из столбца name :

Поскольку большинство имен обычно отличается по первым 10 символам, этот индекс не должен быть намного медленнее, чем индекс, созданный из всего столбца name . Также использование частичных столбцов для индексов может делать индексный файл намного меньше, что сохранит много дискового пространства и ускорит операции INSERT !

Обратите внимание, что Вы можете добавлять индекс на столбце, который может иметь значения NULL , и на столбцах типов BLOB и TEXT только, если Вы используете MySQL Version 3.23.2 или более новую и применяете тип таблицы MyISAM .

Индексы FULLTEXT могут индексировать только столбцы типов VARCHAR и TEXT в таблицах типа MyISAM . Индексы FULLTEXT доступны в MySQL Version 3.23.23 и более новых. Подробности в разделе «9.4 Полнотекстовый поиск в MySQL».

DROP INDEX удаляет индекс с именем index_name из таблицы tbl_name . DROP INDEX ничего не делает до версии Version 3.22, а начиная с нее, этот вызов перенацелен на соответствующий вызов ALTER TABLE для удаления индекса. Подробности в разделе «7.4 Синтаксис ALTER TABLE «.

Илон Маск рекомендует:  Наследование кода скриптов различными страницами
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL