Создание и удаление БД в MS SQL Server.

Последнее обновление: 09.07.2017

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

Для создания базы данных используется команда CREATE DATABASE .

Чтобы создать новую базу данных откроем SQL Server Management Studio. Нажмем на назначение сервера в окне Object Explorer и в появившемся меню выберем пункт New Query .

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

CREATE DATABASE usersdb

Тем самым мы создаем базу данных, которая будет называться "usersdb":

Для выполнения команды нажмем на панели инструментов на кнопку Execute или на клавишу F5. И на сервере появится новая база данных.

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

USE usersdb;

Прикрепление базы данных

Возможна ситуация, что у нас уже есть файл базы данных, который, к примеру, создан на другом компьютере. Файл базы данных представляет файл с расширением mdf, и этот файл в принципе мы можем переносить. Однако даже если мы скопируем его компьютер с установленным MS SQL Server, просто так скопированная база данных на сервере не появится. Для этого необходимо выполнить прикрепление базы данных к серверу. В этом случае применяется выражение:

CREATE DATABASE название_базы_данных ON PRIMARY(FILENAME="путь_к_файлу_mdf_на_локальном_компьютере") FOR ATTACH;

В качестве каталога для базы данных лучше использовать каталог, где хранятся остальные базы данных сервера. На Windows 10 по умолчанию это каталог C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA . Например, пусть в моем случае файл с данными называется userstoredb.mdf. И я хочу этот файл добавить на сервер как базу данных. Вначале его надо скопировать в выше указанный каталог. Затем для прикрепления базы к серверу надо использовать следующую команду:

CREATE DATABASE contactsdb ON PRIMARY(FILENAME="C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\userstoredb.mdf") FOR ATTACH;

После выполнения команды на сервере появится база данных contactsdb.

Удаление базы данных

Для удаления базы данных применяется команда DROP DATABASE , которая имеет следующий синтаксис:

DROP DATABASE database_name1 [, database_name2]...

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

DROP DATABASE contactsdb

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

Приветствую вас на моем блоге сайт. Сегодня поговорим про 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 запросов не так трудно, как кажется, но сделать это нужно любому вебмастеру. В этом помогут видеокурсы, описанные выше. Подпишитесь на мою группу ВКонтакте чтобы первыми узнавать о появлении новой интересной информации.

В организации базы данных задействуется большое число различных объектов. Все объекты базы данных являются либо физическими, либо логическими. Физические объекты связаны с организацией данных на физических устройствах (дисках). Физическими объектами компонента 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. Для присоединяемой базы данных должны быть доступными все требуемые файлы. Если какой-либо файл данных имеет путь, отличающийся от исходного пути, то для этого файла необходимо указать текущий путь.

Заключительно частью моделирования информационной системы является создание реальной базы данных, опираясь на проведенное выше проектирование. Сюда входит выбор подходящей базы данных, ее развертывание и настройка, создание объектов структуры базы данных при помощи соответствующих средств, наполнение БД тестовым набором данных.

Для реализации реляционной базы данных предложенной структуры в данной курсовой работе выбрана СУБД MS SQL Server - один из лидеров современного рынка реляционных СУБД, распространяемая свободно для разработки или тестирования приложений и лицензируемой для промышленного использования. Преимуществами MS SQL являются:

Высокая функциональность;

Полная поддержка стандарта SQL и его расширения T-SQL;

Широкая распространенность и доступность поддержки;

Надёжность;

Производительность.

Наличие встроенных средств для работы и администрирования БД.

MS SQL является лидером современного рынка реляционных СУБД. Актуальной стабильной версией MS SQL на момент написания работы была версия 2012. Установка данной СУБД не представляет трудностей. Процесс установки сопровождается мастером, который запрашивает у пользователя необходимые данные и дальше самостоятельно устанавливает сервер БД, настраивает его на оптимальное быстродействие.

Для создания базы данных в СУБД MS SQL использовались запросы на ста­н­­дартизированном языке SQL (англ. Structured Query Language, «Струк­ту­ри­ро­ванный язык запросов»). В процессе создания базы данных для всех ат­ри­бу­тов реляционной были указаны подходящие типы данных. Названия сущностей и атрибутов были переведены на английский язык, ключевые поля сфор­ми­ро­ва­ны с префикса «ID_» и суффикса, состоящего из имени связанной с ним таб­ли­цы. В таблице 8 показано соответствие названия сущности физической таблице в БД.

Таблица 8

Соответствие названия сущности физической таблице

Физическая модель информационной системы в БД показана на рис. 5.

Рис. 5. Физическая модель информационной системы

Исходный код создания всех отношений базы данных представлен в Приложении к данной работе.

После создания базы данных её было наполнено пробным набором данных. Исходный код запросов на заполнение базы тестовым набором данных представлен в Приложении к данной работе.

Созданные на сервере таблицы базы данных показаны на рис. 6.

Рис. 6. Диаграмма данных сервера MS SQL

5. Разработка объектов базы данных, демонстрирующих логику предметной области

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

• Представления;

  Хранимые процедуры;

  Триггеры.

Запросы на базу данных.

Представляют собой базовое средство для получения информации из базы данных. Для написания запроса используется стандарт SQL. Для демонстрации напишем запрос, выводящий перечень туристов, отправившихся отдыхать в отель «San Simeon Apartments», фамилии туристов отсортируем по алфавиту. Запрос и результат его выполнения показан на рис. 7.

Рис. 7. Результат выполнения запроса

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

Рис. 8. Представление и результат его работы

Хранимые процедуры. Это объекты в базе данных, позволяющие хранить в ней наборы инструкций по обработке и изменению данных. Текст процедуры пишется на диалекте языка SQL для MS SQL Server, называемом Transacts SQL или сокращенно T-SQL. Для демонстрации напишем хранимую процедуру, позволяющую изменять на заданный процент стоимость проживания во всех отелях. Текст хранимой процедуры показан на рис. 9.

Рис. 9. Хранимая процедура

Триггеры. Это специальная хранимая процедура, которая вызывается не пользователем, а самим сервером БД в ответ на событие INSERT, DELETE, UPDATE на указанной таблице. Триггеры широко используются программистами для автоматизации контроля и изменений данных в БД. Как правило, триггер работает незаметно для пользователя БД и проявляет себя тогда, когда он вводит ошибочные с точки зрения программиста данные. Припустим, что туристическое агентство не работает с детьми младше 10 лет. Напишем триггер, который не позволит ввести возраст туриста меньше 10 лет. Текст триггера показан на рис. 10.

Рис. 10. Триггер, не позволяющий внести возраст туриста меньше 10 лет