Пример создания локальной базы данных Microsoft SQL Server в MS Visual Studio. Создание и удаление БД в MS SQL Server

Определите место для папки Databases. После выполнения соединения с сервером (локальное или удаленное), откроется окно обозревателя объектов Object Explorer в левой стороне экрана. В верхней части дерева обозревателя объектов будет сервер, к которому вы подключены. Если дерево не расширено, нажмите на значок "+" рядом с ним. Определите место папки базы данных Databases.

Создайте новую базу данных. Щелкните правой кнопкой мыши по папке Databases и выберите пункт "New Database...". Появится окно, которое позволяет настроить базу данных перед ее созданием. Дайте имя базе данных, которое поможет вам идентифицировать ее. Большинство пользователей могут оставить значения остальных настроек по умолчанию.

• Вы заметите, что при вводе имени базы данных два дополнительных файла будут созданы автоматически: Data и Log. Файл данных (Data) вмещает все данные в вашей базе данных, в то время как файл журнала (Log) отслеживает изменения в базе данных.
• Нажмите кнопку OK, чтобы создать базу данных. Вы увидите вашу новую базу данных, которая появится в развернутой папке Databases. Она будет иметь значок цилиндра.

Создайте таблицу. База данных может только хранить данные, если вы создаете структуру для этих данных. Таблица содержит информацию, которую вы вводите в вашу базу данных, и вам нужно будет создать ее, прежде чем можете продолжить. Разверните новую базу данных в папке Databases, и щелкните правой кнопкой мыши на папке Tables и выберите пункт "New Table...".

• Windows откроется в остальной части экрана, позволяя вам управлять вашей новой таблицей.

Создайте Primary Key (первичный ключ). Настоятельно рекомендуется, чтобы вы создавали первичный ключ в качестве первого столбца в вашей таблице. Он действует как идентификационный номер, или номер записи, что позволит вам легко выводить эти записи позже. Для его создания введите "ID" в столбце Name field, тип int в поле Data Type и снимите флажок "Allow Nulls". Нажмите на значок Key iна панели инструментов, чтобы установить этот столбец в качестве Primary Key (первичного ключа).

• Вы же не хотите допустить нулевые значения, так как всегда хотите иметь запись по крайней мере "1". Если вы разрешите 0, ваша первая запись будет "0".
• В окне Column Properties прокрутите вниз, пока не найдете опцию Identity Specification. Разверните ее и установите "(ls Identity)" на "Yes". Эта опция автоматически увеличит значение столбца ID для каждой записи, автоматически нумеруя каждую новую запись.

Разберитесь, как устроены таблицы. Таблицы состоят из полей или столбцов. Каждый столбец представляет один из аспектов записи базы данных. Например, если вы создаете базу данных сотрудников, вы можете иметь столбец "FirstName", столбец "LastName", столбец "Address" и столбец "PhoneNumber".

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

• nchar(#) - это тип данных следует использовать для текста, как имена, адреса и т.д. Число в скобках – это максимальное количество символов, разрешенное для это го поля. Установление лимита гарантирует, что ваш размер базы данных остается управляемым. Номера телефонов должны быть сохранены в этом формате, так как вы не выполняете математические функции с ними.
• int - это целые числа, и обычно используются в поле идентификатора.
• decimal(x,y) - будут хранить числа в десятичной форме, а числа в скобках обозначают соответственно общее количество цифр и количество цифр после десятичной. Например, decimal(6,2) будет сохранять числа как 0000.00.

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

Добавьте данные в вашу таблицу. После того, как вы сохранили таблицу, можете начать добавлять в нее данные. Откройте папку Tables в окне обозревателя объектов Object Explorer. Если вашей новой таблицы нет в списке, щелкните правой кнопкой мыши на папке Tables и выберите Refresh. Щелкните правой кнопкой мыши по таблице и выберите "Edit Top 200 Rows".

Для этого понадобится установленная система управления базами данных (СУБД) DB2. Мы будем использовать диалект языка SQL, который используется именно в этой СУБД.

Первая команда, которую мы будем применять для создании базы данных - это команда CREATE DATABASE. Её синтаксис следующий:

CREATE TABLE ИМЯ_ТАБЛИЦЫ (имя_первого_столбца тип данных, ..., имя_последнего_столбца тип данных, первичный ключ, ограничения (не обязательно))

Так как наша база данных моделирует сеть аптек, то в ней есть такие сущности, как "Аптека" (таблица Pharmacy в нашем примере создания базы данных), "Препарат" (таблица Preparation в нашем примере создания базы данных), "Доступность (препаратов в аптеке)" (таблица Availability в нашем примере создания базы данных), "Клиент" (таблица Client в нашем примере создания базы данных) и другие, которые здесь подробно и разберём.

Разработке модели "сущность-связь" можно посвятить не одну статью, но если нас прежде всего интересуют команды языка SQL для создания базы данных и таблиц в ней, то условимся считать, что связи между сущностями уже нам понятны. На рисунке ниже приведено представление модели нашей базы данных с атрибутами сущностей (таблиц) и связями между таблицами.

Для увеличения рисунка можно нажать на него левой кнопкой мыши.

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

Теперь приступим к созданию команд. Первая наша команда SQL создаёт базу данных PHARMNETWORK:

Код SQL

CREATE DATABASE PHARMNETWORK

Описание таблицы PHARMACY (Аптека):

Пишем команду, которая создаёт таблицу PHARMACY (Аптека), значения первичного ключа PH_ID генерируются автоматически от 1 с шагом 1, вносится проверка на то, чтобы значения атрибута Address в этой таблице были уникальными:

Код SQL

CREATE TABLE PHARMACY(PH_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Address varchar(40) NOT NULL, PRIMARY KEY(PH_ID), CONSTRAINT PH_UNIQ UNIQUE(Address))

Описание таблицы GROUP (Группа препаратов):

Пишем команду, которая создаёт таблицу Group (Группа препаратов), значения первичного ключа GR_ID генерируются автоматически от 1 с шагом 1, проводится проверка уникальности наименования группы (для этого используется ключевое слово CONSTRAINT):

Код SQL

CREATE TABLE GROUP(GR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, PRIMARY KEY(GR_ID), CONSTRAINT GR_UNIQ UNIQUE(Name))

Описание таблицы PREPARATION (Препарат):

Команда, которая создаёт таблицу PREPARATION, значения первичного ключа PR_ID генерируются автоматически от 1 с шагом 1, определяется, что значения внешнего ключа GR_ID (Группа препаратов) не могут принимать значение NULL, определена проверка уникальности значений атрибута Name:

Код SQL

CREATE TABLE PREPARATION(PR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, GR_ID int NOT NULL, PRIMARY KEY(PR_ID), constraint PR_UNIQ UNIQUE(Name))

Далее нам требуется позаботиться об ограничениях целостности. Это очень удобно слелать с помощью команды alter table. Эта команда изучается на уроке SQL ALTER TABLE - изменение таблицы базы данных.

Теперь самое время создать таблицу AVAILABILITY (Доступность или Наличие препарата в аптеке). Её описание:

Пишем команду, которая создаёт таблицу AVAILABILITY. Определяются даты начала (не может быть NULL) и окончания (по умолчанию NULL).

Код SQL

CREATE TABLE AVAILABILITY(A_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), PH_ID INT NOT NULL, PR_ID INT NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL, QUANTITY INT NOT NULL, MART varchar(3) DEFAULT NULL, PRIMARY KEY(A_ID), CONSTRAINT AVA_UNIQ UNIQUE(PH_ID, PR_ID))

Создаём таблицу DEFICIT (Дефицит препарата в аптеке, то есть, неудовлетворённый запрос). Её описание:

Пишем команду, которая создаёт таблицу DEFICIT:

Код SQL

CREATE TABLE DEFICIT(D_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), PH_ID INT NOT NULL, PR_ID INT NOT NULL, Solution varchar(40) NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL)

Осталось немного. Мы уже дошли до команды, которая создаёт таблицу Employee (Сотрудник). Её описание:

Пишем команду, которая создаёт таблицу Employee (Сотрудник), с первичным ключом, генерируемым по тем же правилам, что и первичные ключи предыдущих таблиц, в которых они существуют. Внешним ключом PH_ID Сотрудник связан с PHARMACY (Аптекой).:

Код SQL

CREATE TABLE EMPLOYEE(E_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), F_Name varchar(40) NOT NULL, L_Name varchar(40) NOT NULL, POST varchar(40) NOT NULL, PH_ID INT NOT NULL, PRIMARY KEY(E_ID))

Очередь дошла до создании таблицы CLIENT (Клиент). Её описание:

Пишем команду, создающую таблицу CLIENT (Клиент), в отношении первичного ключа которого справедливо предыдущее описание. Особенность этой таблицы в том, что её атрибуты F_Name и L_Name имеют по умолчанию значение NULL. Это связано с тем, что клиенты могут быть как зарегистрированными, так и незарегистрированными. У последних значения имени и фамилии как раз и будут неопределёнными (то есть NULL):

Код SQL

CREATE TABLE CLIENT(C_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), FName varchar(40) DEFAULT NULL, LName varchar(40) DEFAULT NULL, DateReg varchar(20), PRIMARY KEY(C_ID))

Предпоследняя таблица в нашей базе данных - таблица BASKET (Корзина покупок). Её описание:

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

Код SQL

CREATE TABLE BASKET(BS_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), C_ID INT NOT NULL, E_ID INT NOT NULL, PRIMARY KEY(BS_ID))

И, наконец, последняя таблица в нашей базе данных - таблица BUYING (покупка). Её описание:

Пишем команду, создающую таблицу BUYING (покупка), так же с уникальным и инкрементируемым первичным ключом и связанную внешними ключами BS_ID, PH_ID, PR_ID с Корзиной покупок, Аптекой и Препаратом соответственно:

Код SQL

CREATE TABLE BUYING(B_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), BS_ID INT NOT NULL, PH_ID INT NOT NULL, PR_ID INT NOT NULL, DateB varchar(20) NOT NULL, Price Double NOT NULL, PRIMARY KEY(B_ID))

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

Введение

Виды СУБД

• Иерархические. Данные организованы в виде древовидной структуры. Пример - файловая система, которая начинается с корня диска и далее прирастает ветвями файлов разных типов и папок разной степени вложенности.
• Сетевые. Видоизменение иерархической, у каждого узла может быть больше одного родителя.
• Объектно-ориентированные. Данные организованы в виде классов/объектов c их атрибутами и принципами взаимодействия согласно ООП.
• Реляционные. Данные этого вида СУБД организованы в таблицах. Таблицы могут быть связаны друг с другом, информация в них структурирована.

SQL

• Что такое SQL-Сервер и как он работает? Взаимодействие с СУБД происходит по клиент-серверному принципу. Некая внешняя программа посылает запрос в виде операторов и команд на языке SQL, СУБД его обрабатывает и высылает ответ. Для упрощения примем, что SQL Сервер = СУБД.

• Data Definition Language (DDL ) – определения данных. Создание структуры БД и её объектов;
• Data Manipulation Language(DML ) – собственно взаимодействие с данными: вставка, удаление, изменение и чтение;
• Transaction Control Language (TCL ) – управление транзакциями;
• Data Control Language(DCL ) – управление правами доступа к данным и структурам БД.

JDBC

Первая программа

Разбор кода

Блок констант:

1. DB_Driver : Здесь мы определили имя драйвера, которое можно узнать, например, кликнув мышкой на подключенную библиотеку и развернув её структуру в директории lib текущего проекта.
2. DB_URL : Адрес нашей базы данных. Состоит из данных, разделённых двоеточием:
3. Протокол=jdbc
4. Вендор (производитель/наименование) СУБД=h2
5. Расположение СУБД, в нашем случае путь до файла (c:/JavaPrj/SQLDemo/db/stockExchange). Для сетевых СУБД тут дополнительно указываются имена или IP адреса удалённых серверов, TCP/UDP номера портов и так далее.

Обработка ошибок:

Логика работы:

1. Class.forName (DB_Driver) – убеждаемся в наличии соответствующего JDBC-драйвера (который мы ранее загрузили и установили).
2. DriverManager.getConnection (DB_URL) – устанавливаем соединение СУБД. По переданному адресу, JDBC сама определит тип и местоположение нашей СУБД и вернёт Connection, который мы можем использовать для связи с БД.
3. connection.close() – закрываем соединение с СУБД и завершаем работу с программой.

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