Как создать базу данных в среде SQL Server. Основы T-SQL

1 голос

Приветствую вас на моем блоге сайт. Сегодня поговорим про sql запросы для начинающих. У некоторых вебмастеров может возникнуть вопрос. Зачем изучать sql? Разве нельзя обойтись ?

Оказывается, что для создания профессионального интернет-проекта этого будет недостаточно. Sql используется чтобы работать с БД и создания приложений для Вордпресс. Рассмотрим, как использовать запросы подробнее.

Что это такое

Sql - язык структурированных запросов. Создан для определения типа данных, предоставления доступа к ним и обработке информации за короткие промежутки времени. Он описывает компоненты или какие-то результаты, которые вы хотите видеть на интернет-проекте.

Если говорить по-простому, то этот язык программирования позволяет добавлять, изменять, искать и отображать информацию в БД. Популярность mysql связана с тем, что он используется для создания динамических интернет-проектов, основа которых составляет база данных. Поэтому для разработки функционального блога вам необходимо выучить этот язык.

Что может делать

Язык sql позволяет:

• создавать таблицы;
• изменять получать и хранить разные данные;
• объединять информацию в блоки;
• защитить данные;
• создавать запросы в access.

Важно! Разобравшись с sql вы сможете писать приложения для Вордпресс любой сложности.

Какая структура

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

У нее имеется имя, колонки и ряд с какой-то информацией. Создавать подобные таблицы можно при помощи sql запросов.

Что нужно знать

Основные моменты при изучении Sql

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

Create database ‘bazaname’

В кавычках пишем имя БД на латинице. Старайтесь придумать для нее понятное имя. Не создавайте базу типа «111», «www» и тому подобное.

После создания БД устанавливаем :

SET NAMES ‘utf-8’

Это нужно чтобы контент на сайте правильно отображаться.

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

CREATE TABLE ‘bazaname’ . ‘table’ (

id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,

log VARCHAR(10),

pass VARCHAR(10),

date DATE

Во второй строке мы прописали три атрибута. Посмотрим, что они означают:

• Атрибут NOT NULL означает, что ячейка не будет пустой (поле обязательное для заполнения);
• Значение AUTO_INCREMENT — автозаполнение;
• PRIMARY KEY — первичный ключ.

Как добавить информацию

Чтобы заполнить поля созданной таблицы значениями, используется оператор INSERT. Пишем такие строки кода:

INSERT INTO ‘table’

(login , pass , date) VALUES

(‘Vasa’, ‘87654321’, ‘2017-06-21 18:38:44’);

В скобках указываем название столбцов, а в следующей - значения.

Важно! Соблюдайте последовательность названий и значений столбцов.

Как обновить информацию

Для этого используется команда UPDATE. Посмотрим, как изменить пароль для конкретного пользователя. Пишем такие строки кода:

UPDATE ‘table’ SET pass = ‘12345678’ WHERE id = ‘1’

Теперь поменяйте пароль ‘12345678’. Изменения происходят в строке с «id»=1. Если не писать команду WHERE - поменяются все строки, а не конкретная.

Рекомендую вам приобрести книгу «SQL для чайников ». С ее помощью вы сможете шаг за шагом профессионально работать с БД. Вся информация построена по принципу от простого к сложному, и будет хорошо восприниматься.

Как удалить запись

Если вы написали что-то не так, исправьте это при помощи команды DELETE. Работает так же, как и UPDATE. Пишем такой код:

DELETE FROM ‘table’ WHERE id = ‘1’

Выборка информации

Для извлечения значений из БД используется команда SELECT. Пишем такой код:

SELECT * FROM ‘table’ WHERE id = ‘1’

В данном примере в таблице выбираем все имеющиеся поля. Это происходит если прописать в команде звездочку «*». Если нужно выбрать какое-то выборочное значение пишем так:

SELECT log , pass FROM table WHERE id = ‘1’

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

Удаление таблицы

Происходит при помощи запроса DROP. Для этого напишем такие строки:

DROP TABLE table;

Вывод записи из таблицы по определенному условию

Рассмотрим такой код:

SELECT id, countri, city FROM table WHERE people>150000000

Он отобразит записи стран где населения больше ста пятидесяти миллионов.

Объединение

Связать вместе несколько таблиц возможно используя Join. Как это работает посмотрите подробнее в этом видео:

PHP и MySQL

Еще раз хочу подчеркнуть, что запросы при создании интернет-проекта - это обычное дело. Чтобы их использовать в php-документах выполните такой алгоритм действий:

• Соединяемся с БД при помощи команды mysql_connect();
• Используя mysql_select_db() выбираем нужную БД;
• Обрабатываем запрос при помощи mysql_fetch_array();
• Закрываем соединение командой mysql_close().

Важно! Работать с БД не сложно. Главное - правильно написать запрос.

Начинающие вебмастера подумают. А что почитать по этой теме? Хотелось бы порекомендовать книгу Мартина Грабера «SQL для простых смертных ». Она написана так, что новичкам все будет понятно. Используйте ее в качестве настольной книги.

Но это теория. Как же обстоит дело на практике? В действительности интернет-проект нужно не только создать, но еще и вывести в ТОП Гугла и Яндекса. В этом вас поможет видеокурс «Создание и раскрутка сайта ».

Видео инструкция

Остались еще вопросы? Посмотрите подробнее онлайн видео.

Вывод

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

Каждый веб-разработчик должен знать SQL, чтобы писать запросы к базам данных. И, хотя, phpMyAdmin никто не отменял, зачастую необходимо испачкать руки, чтобы написать низкоуровневый SQL.

Именно поэтому мы подготовили краткий экскурс по основам SQL. Начнем же!

1. Создание таблицы

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

Создадим простую таблицу по имени month . Она состоит из 3 колонок:

• id – Номер месяца в календарном году (целое число).
• name – Название месяца (строка, максимум 10 символов).
• days – Количество дней в этом месяце (целое число).

Вот как будет выглядеть соответствующий SQL запрос:

CREATE TABLE months (id int, name varchar(10), days int);

Также при создании таблиц целесообразно добавить первичный ключ для одной из колонок. Это позволит держать записи уникальными и ускорит запросы на выборку. Пусть в нашем случае уникальным будет название месяца (столбец name )

CREATE TABLE months (id int, name varchar(10), days int, PRIMARY KEY (name));

Дата и время

Тип данных	Описание
DATE	Значения даты
DATETIME	Значения даты и времени с точностью до минты
TIME	Значения времени

2. Вставка строк

Теперь давайте заполнять нашу таблицу months полезной информацией. Добавление записей в таблицу производится через инструкцию INSERT . Есть два способа записи этой инструкции.

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

Этот способ записи прост, но небезопасен, поскольку нет гарантии, что по мере расширения проекта и редактировании таблицы, столбцы будут располагаться в том же порядке, что и ранее. Безопасный (и в тоже время более громоздкий) способ записи инструкции INSERT требует указания как значений, так и порядка следования столбцов:

Здесь первое значение в списке VALUES соответствует первому указанному имени столбца и т.д.

3. Извлечение данных из таблиц

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

Самый простое использование инструкции SELECT - запрос, который возвращает все столбцы и строки из таблицы (например, таблицы по имени characters ):

SELECT * FROM "characters"

Символ звездочка (*) означает, что мы хотим получить данные из всех столбцов. Так базы данных SQL обычно состоят из более чем одной таблицы, то требуется обязательно указывать ключевое слово FROM , следом за которым через пробел должно следовать название таблицы.

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

SELECT id, name FROM month

Кроме того, во многих случаях мы хотим, чтобы полученные результаты были отсортированы в определенном порядке. В SQL мы делаем это с помощью ORDER BY . Он может принимать опциональный модификатор – ASC (по-умолчанию) сортирующий по возрастанию или DESC , сортирующий по убыванию:

SELECT id, name FROM month ORDER BY name DESC

При использовании ORDER BY убедитесь, что оно будет последним в инструкции SELECT . В противном случае будет выдано сообщение об ошибке.

4. Фильтрация данных

Вы узнали, как выбрать из базы данных с помощью SQL запроса строго определенные столбцы, но что если нам нужно получить еще и определенные строки? На помощь здесь приходит условие WHERE , позволяющее нам фильтровать данные в зависимости от условия.

В этом запросе мы выбираем только те месяцы из таблицы month , в которых больше 30 дней с помощью оператора больше (>).

SELECT id, name FROM month WHERE days > 30

5. Расширенная фильтрация данных. Операторы AND и OR

Ранее мы использовали фильтрацию данных с использованием одного критерия. Для более сложной фильтрации данных можно использовать операторы AND и OR и операторов сравнения (=,<,>,<=,>=,<>).

Здесь мы имеем таблицу, содержащую четыре самых продаваемых альбомов всех времен. Давайте выберем те из них, которые классифицируются как рок и у которых менее 50 миллионов проданных копий. Это можно легко сделать путем размещения оператора AND между этими двумя условиями.

SELECT * FROM albums WHERE genre = "рок" AND sales_in_millions <= 50 ORDER BY released

6. In/Between/Like

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

• IN – служит для указания диапазона условий, любое из которых может быть выполнено
• BETWEEN – проверяет, находится ли значение в указанном диапазоне
• LIKE – ищет по определенным паттернам

Например, если мы хотим выбрать альбомы с поп и соул музыкой, мы можем использовать IN("value1","value2") .

SELECT * FROM albums WHERE genre IN ("pop","soul");

Если мы хотим получить все альбомы, изданные между 1975 и 1985годами, мы должны записать:

SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;

7. Функции

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

• COUNT() – возвращает количество строк
• SUM() – возвращает общую сумму числового столбца
• AVG() – возвращает среднее значение из множества значений
• MIN() / MAX() – получает минимальное / максимальное значение из столбца

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

SELECT MAX(released) FROM albums;

8. Подзапросы

В предыдущем пункте мы научились делать простые расчеты с данными. Если мы хотим использовать результат от этих расчетов, нам не обойтись без вложенных запросов. Допустим, мы хотим вывести artist , album и release year для старейшего альбома в таблице.

Мы знаем, как получить эти конкретные столбцы:

SELECT artist, album, released FROM albums;

Мы также знаем, как получить самый ранний год:

SELECT MIN(released) FROM album;

Все, что нужно сейчас, - это объединить два запроса с помощью WHERE:

SELECT artist,album,released FROM albums WHERE released = (SELECT MIN(released) FROM albums);

9. Объединение таблиц

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

В таблице video_games есть колонка разработчик (developer_id ), но в ней содержится целое число, а не имя разработчика. Это число представляет собой идентификатор (id ) соответствующего разработчика из таблицы разработчиков игр (game_developers ), связывая логически два списка, что позволяет нам использовать информацию, хранящуюся в них обоих одновременно.

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

SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country FROM video_games INNER JOIN game_developers ON video_games.developer_id = game_developers.id;

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

10. Алиасы

Если вы посмотрите на предыдущий пример, то вы заметите, что существуют две колонки называемые name . Это сбивает с толку, так что давайте установим псевдоним одного из повторяющихся столбцов, например, name из таблицы game_developers будет называться developer .

Мы также можем сократить запрос задав псевдонимы имен таблиц: video_games назовем games , game_developers - devs :

SELECT games.name, games.genre, devs.name AS developer, devs.country FROM video_games AS games INNER JOIN game_developers AS devs ON games.developer_id = devs.id;

11. Обновление данных

Часто мы должны изменить данные в некоторых строках. В SQL это делается с помощью инструкции UPDATE . Инструкция UPDATE состоит из:

• Таблицы, в которой находится значение для замены;
• Имен столбцов и их новых значений;
• Выбранные с помощью WHERE строки, которые мы хотим обновить. Если этого не сделать, то изменятся все строки в таблице.

Ниже приведена таблица tv_series с сериалами с их рейтингом. Однако, в таблицу закралась маленькая ошибка: хотя сериал Игра престолов и описывается как комедия, он на самом деле ей не является. Давайте исправим это!

Данные таблицы tv_series UPDATE tv_series SET genre = "драма" WHERE id = 2;

12. Удаление данных

Удаление строки таблицы с помощью SQL - это очень простой процесс. Все, что вам нужно, - это выбрать таблицу и строку, которую нужно удалить. Давайте удалим из предыдущего примера последнюю строку в таблице tv_series . Делается это с помощью инструкции >DELETE

DELETE FROM tv_series WHERE id = 4

Будьте осторожными при написании инструкции DELETE и убедитесь, что условие WHERE присутствует, иначе все строки таблицы будут удалены!

13. Удаление таблицы

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

TRUNCATE TABLE table_name;

В случае, когда мы на самом деле хотим, чтобы удалить и данные, и саму таблицу, то нам пригодится команда DROP:

DROP TABLE table_name;

Будьте очень осторожны с этими командами. Их нельзя отменить!/p>

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

Аннотация: Определяется процесс создания базы данных. Описываются операторы создания, изменения базы данных. Рассматривается возможность указания имени файла или нескольких файлов для хранения данных, размеров и местоположения файлов. Анализируются операторы создания, изменения, удаления пользовательских таблиц. Приводится описание параметров для объявления столбцов таблицы. Дается понятие и характеристика индексов. Рассматриваются операторы создания и изменения индексов. Определяется роль индексов в повышении эффективности выполнения операторов SQL.

База данных

Создание базы данных

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

Стандарт SQL оставляет за разработчиками СУБД право выбора конкретного механизма создания и уничтожения каталогов , однако механизм создания и удаления схем регламентируется посредством операторов CREATE SCHEMA и DROP SCHEMA . В стандарте также указано, что в рамках оператора создания схемы должна существовать возможность определения диапазона привилегий, доступных пользователям создаваемой схемы . Однако конкретные способы определения подобных привилегий в разных СУБД различаются.

В настоящее время операторы CREATE SCHEMA и DROP SCHEMA реализованы в очень немногих СУБД. В других реализациях, например, в СУБД MS SQL Server, используется оператор CREATE DATABASE .

Создание базы данных в среде MS SQL Server

Процесс создания базы данных в системе SQL-сервера состоит из двух этапов: сначала организуется сама база данных , а затем принадлежащий ей журнал транзакций . Информация размещается в соответствующих файлах, имеющих расширения *.mdf (для базы данных ) и *.ldf . (для журнала транзакций ). В файле базы данных записываются сведения об основных объектах (таблицах , индексах , представлениях и т.д.), а в файле журнала транзакций – о процессе работы с транзакциями (контроль целостности данных, состояния базы данных до и после выполнения транзакций).

Создание базы данных в системе SQL-сервер осуществляется командой CREATE DATABASE . Следует отметить, что процедура создания базы данных в SQL-сервере требует наличия прав администратора сервера.

<определение_базы_данных> ::= CREATE DATABASE имя_базы_данных [ <определение_файла> [,...n] ] [,<определение_группы> [,...n] ] ] [ LOG ON {<определение_файла>[,...n] } ] [ FOR LOAD | FOR ATTACH ]

Рассмотрим основные параметры представленного оператора.

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

При создании и изменении базы данных можно указать имя файла, который будет для нее создан, изменить имя, путь и исходный размер этого файла. Если в процессе использования базы данных планируется ее размещение на нескольких дисках, то можно создать так называемые вторичные файлы базы данных с расширением *.ndf . В этом случае основная информация о базе данных располагается в первичном (PRIMARY ) файле, а при нехватке для него свободного места добавляемая информация будет размещаться во вторичном файле . Подход, используемый в SQL-сервере, позволяет распределять содержимое базы данных по нескольким дисковым томам.

Параметр ON определяет список файлов на диске для размещения информации, хранящейся в базе данных .

Параметр PRIMARY определяет первичный файл . Если он опущен, то первичным является первый файл в списке.

Параметр LOG ON определяет список файлов на диске для размещения журнала транзакций . Имя файла для журнала транзакций генерируется на основе имени базы данных , и в конце к нему добавляются символы _log .

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

<определение_файла>::= ([ NAME=логическое_имя_файла,] FILENAME="физическое_имя_файла" [,SIZE=размер_файла ] [,MAXSIZE={max_размер_файла |UNLIMITED } ] [, FILEGROWTH=величина_прироста ])[,...n]

Здесь логическое имя файла – это имя файла, под которым он будет опознаваться при выполнении различных SQL-команд.

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

Параметр SIZE определяет первоначальный размер файла; минимальный размер параметра – 512 Кб, если он не указан, по умолчанию принимается 1 Мб.

Параметр MAXSIZE определяет максимальный размер файла базы данных . При значении параметра UNLIMITED максимальный размер базы данных ограничивается свободным местом на диске.

При создании базы данных можно разрешить или запретить автоматический рост ее размера (это определяется параметром FILEGROWTH ) и указать приращение с помощью абсолютной величины в Мб или процентным соотношением. Значение может быть указано в килобайтах, мегабайтах, гигабайтах, терабайтах или процентах (%). Если указано число без суффикса МБ, КБ или %, то по умолчанию используется значение MБ. Если размер шага роста указан в процентах (%), размер увеличивается на заданную часть в процентах от размера файла. Указанный размер округляется до ближайших 64 КБ.

Дополнительные файлы могут быть включены в группу:

<определение_группы>::=FILEGROUP имя_группы_файлов <определение_файла>[,...n]

Пример 3.1 . Создать базу данных , причем для данных определить три файла на диске C, для журнала транзакций – два файла на диске C.

CREATE DATABASE Archive ON PRIMARY (NAME=Arch1, FILENAME=’c:\user\data\archdat1.mdf’, SIZE=100MB, MAXSIZE=200, FILEGROWTH=20), (NAME=Arch2, FILENAME=’c:\user\data\archdat2.mdf’, SIZE=100MB, MAXSIZE=200, FILEGROWTH=20), (NAME=Arch3, FILENAME=’c:\user\data\archdat3.mdf’, SIZE=100MB, MAXSIZE=200, FILEGROWTH=20) LOG ON (NAME=Archlog1, FILENAME=’c:\user\data\archlog1.ldf’, SIZE=100MB, MAXSIZE=200, FILEGROWTH=20), (NAME=Archlog2, FILENAME=’c:\user\data\archlog2.ldf’, SIZE=100MB, MAXSIZE=200, FILEGROWTH=20) Пример 3.1. Создание базы данных.

В составе Microsoft Visual Studio 2008 находится сервер баз данных Microsoft SQL Server 2005 Express Edition. От полнофункционального сервера данных он отличается только ограничением размера базы данных в 2 гигабайта, что позволяет производить разработку и тестирование приложений баз данных.

Для работы по созданию базы данных и таблиц будем использовать Microsoft SQL Server Management Studio Express. Данный программный продукт является свободнораспространяемым и доступен для скачивания в Интернет.

1.4.1. Определение структуры базы данных

Внешний вид окна программы Microsoft SQL Server Management Studio Express приведен на рис. 14.

Рис. 14. Внешний вид окна программы Microsoft SQL Server Management Studio Express

Для создания базы данных необходимо кликнуть правой кнопкой мыши на пункте «Базы данных» и выбрать пункт меню «Создать базу данных». Окно создания БД представлено на рис. 15.

Рис. 15. Окно создания БД

В данном окне задается имя базы данных, имена и пути к файлам базы данных, начальный размер файлов и шаг увеличения размера БД в случае необходимости. После нажатия кнопки «ОК» созданная БД появляется в списке баз данных (рис. 16).

Рис. 16. Вид Management Studio с созданной базой данных

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

Таблица 6. Соответствие типов данных Microsoft Access и Microsoft SQL

	Тип данных Microsoft Access	Тип данных Microsoft SQL	Описание типа данных Microsoft SQL
	Текстовый		Тип данных для хранения текста до 4000 символов
	Поле МЕМО		Тип данных для хранения символов в кодировке Unicode до 1 073 741 823 символов
	Числовой		Численные значения (целые) в диапазоне от -2 147 483 648 до +2 147 483 647
	Дата/время
	Денежный		Денежный тип данных, значения которого лежат в диапазоне от -922 337 203 685 477.5808 до +922 337 203 685 477.5807, с точностью до одной десятитысячной
			См. пункт 3
	Логический		Переменная, способная принимать только два значения - 0 или 1
	Поле объекта OLE		Переменная для хранения массива байтов от 0 до 2 147 483 647 байт
			См. пункт 2
	Мастер подстановок		См. пункт 1

Для создания таблиц необходимо выбрать в контекстном меню ветки «Таблицы» пункт «Создать таблицу». Среда Management Studio принимает следующий вид (рис. 17).

Рис. 17. Среда Management Studio в режиме создания таблицы

Для определения связей между таблицами необходимо задать первичные ключи таблиц. Для этого в контекстном меню соответствующего поля выбрать пункт «Задать первичный ключ» (рис. 18).

Рис. 18. Задание первичного ключа

Для создания связей между таблицами и схемы базы данных необходимо создать новую диаграмму базы данных, выбрав соответствующий пункт в контекстном меню ветви «Диаграммы баз данных». Добавив в появившемся окне необходимые таблицы в диаграмму, получаем следующий вид среды Management Studio (рис. 19).

Рис. 19. Начало построения диаграммы БД

Создание связей происходит путем совмещения связываемых полей. Результатом становится появление окна создания отношения (рис. 20).

Рис. 20. Создание отношения между таблицами БД

Особо отметим пункт «Спецификация INSERT и UPDATE», задающий правила обновления и удаления связанных данных в таблицах.

После создания остальных таблиц и их связей схема данных будет выглядеть следующим образом (рис. 21).

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

Завершив работу со схемой данных, сохраняем ее. Отметим, что в SQL Management Studio, в отличие от Access, для одной базы данных может быть создано несколько диаграмм (рис. 22).

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

Рис. 21. Схема базы данных BDTur_firmSQL

Рис. 22. Несколько диаграмм для одной БД

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

Объектом базы данных, который требуется создать в первую очередь, является сама база данных. Компонент Database Engine управляет как системными, так и пользовательскими базами данных. Пользовательские базы данных могут создаваться авторизованными пользователями, тогда как системные базы данных создаются при установке СУБД.

Для создания базы данных используется два основных метода. В первом методе задействуется обозреватель объектов среды SQL Server Management Studio, как было показано ранее, а во втором применяется инструкция языка Transact-SQL CREATE DATABASE . Далее приводится общая форма этой инструкции, а затем подробно рассматриваются ее составляющие:

CREATE DATABASE db_name { file_spec1} ,...] Соглашения по синтаксису

Параметр db_name - это имя базы данных. Имя базы данных может содержать максимум 128 символов. Одна система может управлять до 32 767 базами данных. Все базы данных хранятся в файлах, которые могут быть указаны явно администратором или предоставлены неявно системой. Если инструкция CREATE DATABASE содержит параметр ON , все файлы базы данных указываются явно.

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

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

Учетная запись компонента Database Engine, применяемая для создания базы данных, называется владельцем базы данных . База данных может иметь только одного владельца, который всегда соответствует учетной записи. Учетная запись, принадлежащая владельцу базы данных, имеет специальное имя dbo . Это имя всегда используется в отношении базы данных, которой владеет пользователь.

Опция LOG ON параметра dbo определяет один или более файлов в качестве физического хранилища журнала транзакций базы данных. Если опция LOG ON отсутствует, то журнал транзакций базы данных все равно будет создан, поскольку каждая база данных должна иметь, по крайней мере, один журнал транзакций. (Компонент Database Engine ведет учет всем изменениям, которые он выполняет с базой данных. Система сохраняет все эти записи, в особенности значения до и после транзакции, в одном или более файлов, которые называются журналами транзакций. Для каждой базы данных системы ведется ее собственный журнал транзакций.)

В опции COLLATE указывается порядок сортировки по умолчанию для базы данных. Если опция COLLATE не указана, базе данных присваивается порядок сортировки по умолчанию, совершенно такой же, как и порядок сортировки по умолчанию системы баз данных.

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

Компонент Database Engine создает новую базу данных по шаблону образцовой базы данных model. Свойства базы данных model можно настраивать для удовлетворения персональных концепций системного администратора. Если определенный объект базы данных должен присутствовать в каждой пользовательской базе данных, то этот объект следует сначала создать в базе данных model.

В примере ниже показан код для создания простой базы данных, без указания дополнительных подробностей. Чтобы исполнить этот код, введите его в редактор запросов среды Management Studio и нажмите клавишу .

USE master; CREATE DATABASE SampleDb;

Код, приведенный в примере, создает базу данных, которая называется SampleDb. Такая сокращенная форма инструкции CREATE DATABASE возможна благодаря тому, что почти все ее параметры имеют значения по умолчанию. По умолчанию система создает два файла. Файл данных имеет логическое имя SampleDb и исходный размер 2 Мбайта. А файл журнала транзакций имеет логическое имя SampleDb_log и исходный размер 1 Мбайт. (Значения размеров обоих файлов, а также другие свойства новой базы данных зависят от соответствующих спецификаций базы данных model.)

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

USE master; CREATE DATABASE Projects ON (NAME=projects_dat, FILENAME = "D:\projects.mdf", SIZE = 10, MAXSIZE = 100, FILEGROWTH = 5) LOG ON (NAME=projects_log, FILENAME = "D:\projects.ldf", SIZE = 40, MAXSIZE = 100, FILEGROWTH = 10);

Созданная в примере база данных называется Projects. Поскольку опция PRIMARY не указана, то первичным файлом предполагается первый файл. Этот файл имеет логическое имя projects_dat и он сохраняется в дисковом файле projects.mdf. Исходный размер этого файла 10 Мбайт. При необходимости, система выделяет этому файлу дополнительное дисковое пространство в приращениях по 5 Мбайт. Если не указать опцию MAXSIZE или если этой опции присвоено значение UNLIMITED, то максимальный размер файла может увеличиваться и будет ограничиваться только размером всего дискового пространства. (Единицу размера файла можно указывать с помощью суффиксов KB, TB и MB, означающих килобайты, терабайты и мегабайты соответственно. По умолчанию используется единица размера MB, т.е. мегабайты.)

Кроме файла данных создается файл журнала транзакций, который имеет логическое имя projects_log и физическое имя projects.ldf. Все опции спецификации файла журнала транзакций имеют такие же имена и значения, как и соответствующие опции для спецификации файла данных.

В языке Transact-SQL можно указать конкретный контекст базы данных (т.е. какую базу данных использовать в качестве текущей) с помощью инструкции USE . (Альтернативный способ - выбрать имя требуемой базы данных в раскрывающемся списке Database (Базы данных) в панели инструментов среды SQL Server Management Studio.)

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

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

Кроме создания новой базы данных, инструкцию CREATE DATABASE можно применить для получения моментального снимка существующей базы данных (база данных-источник). Моментальный снимок базы данных является согласованной с точки зрения завершенных транзакций копией исходной базы данных на момент создания моментального снимка. Далее показан синтаксис инструкции для создания моментального снимка базы данных:

Таким образом, чтобы создать моментальный снимок базы данных, в инструкцию CREATE DATABASE нужно вставить предложение AS SNAPSHOT OF . В примере ниже иллюстрируется создание моментального снимка базы данных SampleDb и сохранения его в папке D:\temp. (Прежде чем выполнять этот пример, нужно создать данный каталог.)

USE master; CREATE DATABASE SampleDb ON (NAME = "SampleDb_Data" , FILENAME = "D:\temp\snapshot_DB.mdf") AS SNAPSHOT OF SampleDb;

Моментальный снимок существующей базы данных - это доступная только для чтения копия базы данных-источника, которая отражает состояние этой базы данных на момент копирования. (Таким образом, можно создавать множественные моментальные снимки существующей базы данных.) Файл моментального снимка (в примере выше это файл D:\temp\snapshot_DB.mdf) содержит только измененные данные базы данных-источника. Поэтому в коде для создания моментального снимка необходимо указывать логическое имя каждого файла данных базы данных-источника, а также соответствующие физические имена.

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

Моментальные снимки баз данных можно создавать только на дисках с файловой системой NTFS (New Technology File System - файловая система новой технологии), т.к. только эта файловая система поддерживает технологию разреженных файлов, применяемую для хранения моментальных снимков.

Моментальные снимки баз данных обычно применяются в качестве механизма предохранения данных от искажения.

Присоединение и отсоединение баз данных

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

Для отсоединения базы данных от сервера баз используется системная процедура sp_detach_db . (Отсоединяемая база данных должна находиться в однопользовательском режиме.)

Для присоединения базы данных используется инструкция CREATE DATABASE с предложением FOR ATTACH. Для присоединяемой базы данных должны быть доступными все требуемые файлы. Если какой-либо файл данных имеет путь, отличающийся от исходного пути, то для этого файла необходимо указать текущий путь.