🗊Презентация SQL. (Лекция 6)

База данных «Колледж»

Пример. Создать таблицу STUDENTS. CREATE TABLE STUDENTS (SNUM INTEGER, SFAM CHAR (20), SNAME CHAR (15), SFATH CHAR (15), STIP DECIMAL)

Для удаления таблицы необходимо: 1)      быть ее создателем или иметь на это право; 2)      перед удалением необходимо ее очистить от данных, это позволяет избежать случайной потери информации. DROP TABLE <name of table>; Пример. Удалить все сведения и таблицу PREDMET. DELETE FROM PREDMET; DROP TABLE PREDMET;

DDL – язык определения данных. В SQL существует ряд операторов, позволяющих изменять структуру данных . Операторы DDL позволяют не вникать в детали хранения информации в БД на физическом уровне, используя стандартные понятия поля и таблицы.

Это операции: Это операции: 1)создание новой БД; 2)определение новой структуры и создание таблицы; 3) удаление таблицы; 4)изменение структуры существующей таблицы; 5)обеспечение условий безопасности; 6)создание индексов для доступа к таблице; 7)управление размещением данных на устройствах.

Основу DDL составляют три команды: 1)      CREATE - создать; 2)      DROP – удалить; 3)      ALTER – изменить.

Создание базы данных. В системе MS SQL эти действия выполняются оператором: CREATE DATABASE <name of database> ON < name 1>, <…>, < name n>;

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

1)Для разделения элементов команды используются пробелы, пробел не может быть частью имени ( MY_Table). 1)Для разделения элементов команды используются пробелы, пробел не может быть частью имени ( MY_Table). 2)Значение аргумента размерность [<size of attribution>] – зависит от типа данных и может заноситься по умолчанию самой СУБД. Это удобно для числовых полей. Тип CHAR требует обязательного указания размера – количества символов в поле. По умолчанию размер равен одному символу. 3)Имена таблиц должны отличаться, но могут использоваться одинаковые имена полей (STUDENTS.SNUM USP.SNUM). 4)Пользователи не владельцы таблиц должны к ним обращаться по имени владельца. (SA.STUDENTS).

Пример. Создать таблицу STUDENTS. CREATE TABLE STUDENTS (SNUM INTEGER, SFAM CHAR (20), SNAME CHAR (15), SFATH CHAR (15), STIP DECIMAL)

Добавление новых полей выполняется командой: Добавление новых полей выполняется командой: ALTER TABLE <name of table> ADD <Name of attribution1> <type of attribution1> [(<size of attribution1>)], … <Name of attribution n> <type of attribution n> [(<size of attribution n>)]); Добавляемые поля автоматически получают значения NULL.

Пример. Предположим мы решили добавить номер курса и специальность. ALTER TABLE STUDENTS ADD COURS INTEGER, SPEC CHAR (20);

Для удаления таблицы необходимо: 1)      быть ее создателем или иметь на это право; 2)      перед удалением необходимо ее очистить от данных, это позволяет избежать случайной потери информации. DROP TABLE <name of table>; Пример. Удалить все сведения и таблицу PREDMET. DELETE FROM PREDMET; DROP TABLE PREDMET;

Пример Создать для пользователя копию таблицы PREDMET, добавить в нее поля: лабораторные работы, их количество. CREATE TABLE PREDMET_NEW ( PNUM INTEGER, PNAME CHAR (30), COURS INTEGER, HOURS INTEGER, LAB CHAR (30), NUM INTEGER);

INSERT INTO PREDMET_NEW SELECT * FROM PREDMET; INSERT INTO PREDMET_NEW SELECT * FROM PREDMET; Новые поля заполнятся значениями по умолчанию или значениями NULL.

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

Когда создаётся индекс, в поле БД запоминается порядок всех значений этого поля в области памяти. Когда создаётся индекс, в поле БД запоминается порядок всех значений этого поля в области памяти. При наличии индекса система могла бы найти искомый номер прямо в этом упорядоченном массиве и указать, какую искомую строку следует найти. У индексов есть и недостатки : 1)наличие индексов замедляет операции модификации INSERT, DELETE; 2)сам индекс занимает тоже место в памяти.

Индексы могут состоять из нескольких полей, при этом первое поле считается главным, второе поле упорядоченным внутри первого и т. д. Индексы могут состоять из нескольких полей, при этом первое поле считается главным, второе поле упорядоченным внутри первого и т. д. Создаются индексы командой: CREATE INDEX <Name of index> on <Name of table>(< Name of attribution1 >,[< Name of attribution2 >,…]); Разумеется, таблица должна быть создана ранее, и иметь имена полей указанных в команде. Имя индекса является уникальным и не может быть использовано в других целях. SQL сам определяет, когда индекс необходим и использует его автоматически.

Пример В таблице STUDENT наиболее часто употребимо поле SFAM, создать индекс по этому полю. Пример В таблице STUDENT наиболее часто употребимо поле SFAM, создать индекс по этому полю. CREATE INDEX SFAMIDX on STUDENTS( SFAM); При создании индекса ему не приписана уникальность. Это делается с помощью специального ключевого слова UNIQUE. CREATE UNIQUE INDEX SNUMIDX ON STUDENTS( SNUM); Однако эта команда не будет выполнена, если среди значений этого поля есть не уникальные значения. Поэтому рекомендуем создавать индексы сразу после создания её структуры, до ввода в неё значений.

Для удаления используется команда: Для удаления используется команда: DROP INDEX <Name of index>; Например: DROP INDEX SFAMIDX; Удаление индексов не влияет на данные.

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

Существуют ограничения двух типов: Существуют ограничения двух типов: 1) ограничения поля – применимые только к указанному полю; 2) ограничения таблицы – применимые к указанным группам полей. Ограничения поля (атрибута) – помещается в конец фрагмента команды, объявляющего его имя после типа данных. Ограничения таблицы (отношения) – помещаются в конец объявления имени таблицы после последнего имени поля.

CREATE TABLE <name of table> CREATE TABLE <name of table> (<Name of attribution1> <type of attribution1> [(<size of attribution1>)] <limit1>, <Name of attribution2> <type of attribution2> [(<size of attribution2>] <limit2>, … , <Name of attribution n> <type of attribution n> [(<size of attribution n>)], <limit n>, <limit of table>);

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

Ограничения по уникальности. Уникальные индексы – один из самых простых и наиболее эффективных методов. Однако имеется возможность установить уникальность для отдельных столбцов (полей) таблицы, если существует уверенность, что все значения должны отличаться. При создании таблицы в конкретном поле указывается слово UNIQUE, при этом СУБД будет контролировать процесс ввода и отклонит попытку ввести имевшееся ранее значение. Это ограничение может применяться к полям с ограничением NOT NULL.

Пример 11.3 Устраним повторяющиеся значения в поле SNUM. CREATE TABLE STUDENTS (SNUM INTEGER NOT NULL UNIQUE, SFAM CHAR (20) NOT NULL, SNAME CHAR (15), SFATH CHAR (15), STIP DECIMAL); Напоминаем, что поля являющиеся уникальными являются кандидатами-ключами, или уникальными ключами.

Подобное ограничение в поле SFAM запретило бы иметь однофамильцев в таблице STUDENTS! Подобное ограничение в поле SFAM запретило бы иметь однофамильцев в таблице STUDENTS! Объявление уникальности возможно и для группы полей, с помощью ограничения к таблице. Между уникальностью поля и таблицы существуют различия: 1)      уникальные поля - дают единственную запись-строку; 2)      уникальные группы – уникальная комбинация значений полей из этой группы, при этом не требуется уникальность каждого отдельного поля. С другой стороны, если хотя бы одно поле в группе уникальное, то и значение всей группы уникальное.

Репликация, дублирование и восстановление. Репликация - это процесс, посредством которого данные копируются между базами данных, находящимися на том же самом сервере или на других серверах, связанных через LAN, WAN или Internet Репликация Microsoft SQL Server использует метафоры (способы передачи данных между БД по сети): publisher distributor subscriber.

Publisher - сервер или база данных, которая посылает данные на другой сервер или в другую базу данных. Publisher - сервер или база данных, которая посылает данные на другой сервер или в другую базу данных. Subscriber - сервер или база данных, которая получает данные от другого сервера или другой базы данных. Distributor - сервер, который управляет потоком данных через систему репликации. Этот сервер содержит специализированную базу данных: Distribution database.

Publisher содержит публикацию/публикации. Публикация - это совокупность одной или более статей, которые посылаются серверу подписчику (subscriber) или базе данных. Publisher содержит публикацию/публикации. Публикация - это совокупность одной или более статей, которые посылаются серверу подписчику (subscriber) или базе данных. Статья (Article) - основной модуль репликации и это может быть таблица или подмножество таблицы. Подписка (subscriptions) - это группа данных, которые сервер или база данных получает.

Существуют виды подписки: Существуют виды подписки: push и pull subscriptions Push subscription - это подписка, при которой сервер издатель периодически помещает транзакции на подписавшиеся сервера или базы данных. Pull subscription - это подписка, при которой подписавшийся сервер будет периодически соединяться с тиражируемой информацией и перемещать её из Distribution database.

Distribution database - это системная база данных, которая хранится на дистрибуторе (distributor) и не содержит никаких пользовательских таблиц. Эта база данных используется для хранения снимков заданий и всех транзакций, ожидающих распределения подписчикам. Distribution database - это системная база данных, которая хранится на дистрибуторе (distributor) и не содержит никаких пользовательских таблиц. Эта база данных используется для хранения снимков заданий и всех транзакций, ожидающих распределения подписчикам.

Топология репликации Microsoft SQL Server поддерживает следующие топологии репликации - Центральный publisher - Центральный subscriber - Центральный publisher с отдаленным distributor - Центральный distributor - Издающий subscriber

Центральный publisher Это одна из наиболее используемых топологий репликации. В этом сценарии, один сервер исполняет роли publisher и distributor, а другой сервер/серверы определяется, как подписчик/подписчики.

Центральный subscriber Это обычная топология складирования данных. Несколько серверов или баз данных копируют свои данные на центральный сервер в одну или более базы данных

Центральный publisher с отдаленным distributor В этой топологии база Distribution постоянно находится на сервере, отличном от сервера, где располагается publisher. Эта топология используется для повышения эффективности, когда объём репликации увеличивается, а также, если сервер или сетевые ресурсы ограничены. Это уменьшает загрузку publisher, но увеличивает сетевой трафик. Эта топология требует отдельных инсталляций Microsoft SQL Server для publisher и для distributor.

Центральный distributor В этой топологии, несколько издателей используют только один distributor, который постоянно находится на отличном от издателей сервере. Это одна из наиболее редко используемой топологии репликации, потому что имеет уязвимую точку (на сервере с центральным distributor), и если сервер distributor потерпит неудачу, сценарий репликации будет разрушен полностью.

Издающий subscriber Это топология двойственной роли. В ней, два сервера издают те же самые данные. Сервер издатель посылает данные на subscriber, и затем subscriber издает данные на любое число подписчиков. Это полезно когда publisher должен послать данные подписчикам по медленной или дорогой линии связи.

Типы репликации Microsoft SQL Server 7.0/2000 поддерживает следующие виды репликации: - Snapshot - Transactional - Merge

Snapshot репликация (снимок) Является самой простой. При этом, все копируемые данные (точная копия) будут копироваться из базы данных publisher в базу(ы) данных subscriber/subscribers на периодической основе. Snapshot репликация является лучшим методом копирования данных, которые нечасто изменяются и когда размер копируемых данных не очень большой.

Transactional репликация SQL Server фиксирует (делает моментальные снимки) все изменения, которые были сделаны в статье, и сохраняет, как: INSERT, UPDATE и DELETE инструкции в базе Distribution. Эти изменения посылаются подписчикам от Distribution и применяются к расположенным в них данным.

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

Merge репликация Является наиболее трудным типом репликации. Она предоставляет возможность автономных изменений реплицируемых данных и на publisher и на subscriber. При Merge репликации, SQL Server фиксирует все накопившиеся изменения не только в источнике данных, но и целевых базах данных, и урегулирует конфликты согласно правилам, которые Вы предварительно конфигурируете, или посредством определённого Вами блока принятия решений - resolver-ра.

Merge репликацию лучше использовать, когда Вы хотите обеспечить поддержку автономных изменений реплицируемых данных относительно publisher и относительно subscriber. Merge репликацию лучше использовать, когда Вы хотите обеспечить поддержку автономных изменений реплицируемых данных относительно publisher и относительно subscriber.

Агенты Репликации Microsoft SQL Server 7.0/2000 поддерживает следующих агентов репликации: - Snapshot Agent - Log Reader Agent - Distribution Agent - Merge Agent

Snapshot Agent Агент репликации, который создаёт файлы снимков, хранит снимки на distributor и производит запись информации о состоянии синхронизации в Distribution database. Snapshot Agent используется во всех типах репликации (Snapshot, Transactional и Merge) и может управляться из SQL Server Enterprise Manager.

Log Reader Agent Агент репликации, который перемещает транзакции, отмеченные для репликации из transaction log, находящегося на publisher, в Distribution database. Этот агент репликации не используется в Snapshot репликации.

Distribution Agent Агент репликации, который перемещает обрабатывающие снимки задания из Distribution database к подписчикам и перемещает все транзакции, ожидающие распределения на подписчиков. Distribution Agent используется в Snapshot и Transactional репликациях и может управляться с помощью SQL Server Enterprise Manager.

Merge Agent Агент репликации, который применяет первоначальные, обрабатывающие снимки задания по таблицам базы данных publication на подписчиках, и потом объединяет возможные последующие изменения данных, которые произошли после создания первоначального снимка. Merge Agent используется только в Merge репликации

Резервное копирование MS SQL поддерживает 3 типа backup’а данных Full backup Differential backup Transaction-log backup