SQL Server – коротко о главном
В качестве среды программирования мы будем использовать СУБД (систему управления базами данных ) SQL Server версии 2005 или выше, Express Edition. SQL Server – это хорошо масштабируемый, полностью реляционный, быстродействующий многопользовательский сервер баз данных масштаба предприятия, способный обрабатывать большие объемы данных для клиент-серверных приложений. Его основные характеристики: · многопользовательская поддержка; · многоплатформность; · поддержка 64-разрядной архитектуры; · масштабируемость (многопроцессорная обработка и поддержка терабайтных БД – 10е12 байт); · стандарт SQL92 (язык Transact SQL); · параллельные архивирование и восстановление БД; · репликация данных; · распределенные запросы; · распределенные транзакции; · динамические блокировки; · интеграция с IIS и Visual Studio.
Существуют дистрибутивы для разных версий операционной системы Windows, как 32-битовых, так и 64-битовых.
Установка. 1 этап. Установите SQL Server Express из дистрибутива. Учтите, что для установки требуется .Net Framework 2.0 (для 2005) или более поздняя версия. Не забудьте включить текущего пользователя в группу администраторов. Для версии 2008 установка выглядит несколько сложнее; если возникают трудности, пользуйтесь справочной системой сайта Microsoft.com. В процессе установки в общем меню программ создается папка Microsoft SQL Server. В этой папке в подпапке Configuration Tools содержится очень полезная утилита с названием SQL Server Configuration Manager. С помощью этой утилиты можно запускать и останавливать сервер, а также выполнять его настройку.
2 этап. Установите SQL Server Management Studio. Эта среда позволяет как выполнять некоторые административные задачи с помощью визуальных средств, так и запускать SQL-сценарии в текстовом режиме.
Запустите SQL Server Management Studio. При запуске задается имя сервера (обычно оно выглядит как ИмяКомпьютера\SQLEXPRESS).
Служебные базы данных, которые создаются по умолчанию: · master – системная БД с конфигурацией SQLServer; · model – шаблон для всех пользовательских БД; · msdb – планирование заданий по расписанию и т.п.; · tempdb – БД для временных объектов.
Любая БД состоит из: · диаграмм (автоматически не создаются!); · таблиц (пользовательских и системных); · представлений; · хранимых процедур; · пользователей; · ролей; · правил; · значений по умолчанию; · пользовательских типов.
БД можно создать: · с помощью визуальных средств Management Studio: (Databases – New database); · с помощью средств ER-проектирования, например Platinum ERWin, · с помощью SQL-команд, запуская их из SQL Management Studio. Главный файл БД (один) имеет тип.MDF. Можно задавать дополнительные файлы, их тип.NDF. Файл журнала транзакций имеет тип.LDF.
SQL Server Management Studio позволяет выполнять как отдельные SQL-команды, так и SQL-файлы целиком. Перед выполнением запроса следует выбрать базу данных в списке в левой верхней части окна. Для выполнения файла следует нажать кнопку с восклицательным знаком или клавишу F5. Можно выполнить только часть файла, для этого ее нужно предварительно выделить. Результаты запроса выдаются в нижнем окне. Оно имеет две закладки – для результатов и для сообщений. Результаты можно выдавать в виде текста или в виде таблицы. Если в каком-то из окон не читается русский текст, поменяйте в этом окне шрифт на русифицированный.
SQL Server Express edition не включает в себя справочную систему. Вся необходимая информация может быть найдена в онлайн-режиме в справочной системе Microsoft MSDN на сайте msdn.microsoft.com.
DDL. Таблицы
DDL (Data Definition Language) – язык описания данных, составная часть SQL. Рассмотрим команды создания базы данных и таблиц.
Для создания базы данных служит команда CREATE DATABASE имя_БД Для активизации базы данных служит команда USE имя_БД Выполняйте команду активизации базы данных при каждом запуске SQL Management Studio, поскольку по умолчанию в качестве активной установлена БД master. Для создания таблиц используется команда CREATE TABLE. Краткий формат этой команды (квадратные скобки означают необязательные элементы): CREATE TABLE имя_таблицы( Список_определений_полей, [Список_ограничений_таблицы]); Более подробно смотрите в MSDN. Определение поля имеет формат: Имя_поля тип_поля[(размер)] [NULL] [NOT NULL] [IDENTITY] [DEFAULT умолчание] [Список_ограничений_поля] Ограничение поля имеет формат [CHECK (условие)] [PRIMARY KEY] [UNIQUE] [REFERENCES имя_таблицы(имя_поля)] Чаще всего используются типы полей: VARCHAR – строковый тип переменной длины; NUMERIC – числовой тип; DATETIME – тип дата/время. Какие еще типы полей есть в SQL server? – обращайтесь к MSDN.
NULL – специальное «неопределенное» значение, предусмотренное стандартом SQL. Определение NULL/NOT NULL служит для указания, что данный тип поля допускает/запрещает ввод NULL-значений. IDENTITY начальное_значение, приращение – определение, указывающее, что данное поле представляет собой счетчик. Это означает, что значения в данное поле вставляются сервером с нарастанием автоматически при вставке строки. Если «начальное_значение» и «приращение» пропущены, они полагаются равными 1. DEFAULT умолчание – определение, указывающее значение по умолчанию, т.е., значение, которое присваивается данному полю, если при вставке новой строки этому полю не было явно присвоено некоторое значение. CHECK (условие)- ограничение, содержащее условие на поле, которое будет проверяться при вводе новых строк и при изменении значений в уже существующих строках. Если при добавлении или изменении строки условие оказывается ложным, то строка не добавляется/ не изменяется. PRIMARY KEY – ограничение, указывающее, что в данной таблице данное поле представляет собой первичный ключ. (Составной первичный ключ таким образом объявлять нельзя!) При использовании этого ограничения создается первичный индекс. UNIQUE – ограничение, указывающее, что в данном поле могут храниться только уникальные значения. При использовании этого ограничения создается уникальный индекс.
Например, в таблице «Предприятия» номер предприятия будет первичным ключом и счетчиком, название фирмы не допускает значений NULL: CREATE TABLE k_firm (firm_num NUMERIC(6) IDENTITY PRIMARY KEY, firm_name VARCHAR(100) NOT NULL, firm_addr VARCHAR(100) ); В таблице «Договоры» для поля даты договора задается значение по умолчанию – текущая дата, для типа договора задается условие, что он должен принадлежать заданному списку значений.
CREATE TABLE k_contract contract_num NUMERIC(6) IDENTITY PRIMARY KEY, contract_date DATETIME DEFAULT GETDATE(), contract_type CHAR(1) CHECK (contract_type IN ('A','B','C')), firm_num NUMERIC(6) NOT NULL, staff_num NUMERIC(6) );
REFERENCES имя_таблицы(имя_поля) - ограничение внешнего ключа, или декларативной ссылочной целостности. Декларативная ссылочная целостность требует, чтобы в поле внешнего ключа можно было вводить только такие значения первичного ключа, которые присутствуют в родительской таблице. Например, в таблицу «Сотрудники» мы не можем внести номер несуществующего отдела. Кроме того, из родительской таблицы нельзя удалить строку, если в дочерней таблице имеются строки с таким внешним ключом. Мы не можем удалить отдел, если с ним связаны сотрудники. CREATE TABLE k_staff (staff_num NUMERIC(6) IDENTITY, staff_name VARCHAR(30) NOT NULL, staff_post VARCHAR(30), dept_num NUMERIC(6) REFERENCES k_dept (dept_num), staff_hiredate DATETIME NOT NULL, staff_termdate DATETIME ); Ограничения уровня таблицы задаются после списка определений полей. Каждое из них содержит ключевое слово CONSTRAINT и уникальное имя. Эти ограничения применяются обычно в том случае, если включают в себя несколько полей, например, составной первичный или внешний ключ или условие CHECK, содержащее сразу несколько полей таблицы. Следует заметить, что любое ограничение уровня поля можно переписать как ограничение уровня таблицы.
Ограничение CHECK уровня таблицы может быть определено, например, так: CREATE TABLE k_bill (bill_num NUMERIC(6) IDENTITY PRIMARY KEY, bill_date DATETIME DEFAULT GETDATE(), bill_term DATETIME DEFAULT GETDATE()+30, contract_num NUMERIC(6), CONSTRAINT ch_bill_date CHECK (bill_term-bill_date<91) ); т.е., срок действия счета не может превышать 91 день.
Ограничение внешнего ключа на уровне таблицы определяется так:
CONSTRAINT имя_ограничения FOREIGN KEY (список_полей) REFERENCES родительская_таблица(внешний ключ) например, CREATE TABLE k_contract (contract_num NUMERIC(6) IDENTITY PRIMARY KEY, contract_date DATETIME DEFAULT GETDATE(), contract_type CHAR(1) CHECK (contract_type IN ('A','B','C')), firm_num NUMERIC(6) NOT NULL, staff_num NUMERIC(6), CONSTRAINT fk_contract_firm_num FOREIGN KEY (firm_num) REFERENCES k_firm (firm_num), CONSTRAINT fk_contract_staff_num FOREIGN KEY (staff_num) REFERENCES k_staff (staff_num) ); т.е, для таблицы договоров есть два различных внешних ключа: номер предприятия и номер сотрудника, ссылающиеся на таблицы «Предприятия» и «Сотрудники».
В том случае, когда первичный ключ состоит из нескольких полей, его придется создавать как ограничение уровня таблицы:
CONSTRAINT имя_ограничения PRIMARY KEY (список_полей)
Например, в таблице протоколов счета первичный ключ состоит из двух полей, на основе каждого из них также создается внешний ключ: CREATE TABLE k_protokol price_num NUMERIC(6) NOT NULL, bill_num NUMERIC(6) NOT NULL, kolvo NUMERIC(6) NOT NULL, price_sum NUMERIC(9,2) NOT NULL, CONSTRAINT pk_protokol_num PRIMARY KEY (price_num, bill_num), CONSTRAINT fk_protokol_price_num FOREIGN KEY (price_num) REFERENCES k_price (price_num), CONSTRAINT fk_protokol_bill_num FOREIGN KEY (bill_num) REFERENCES k_bill (bill_num) );
Кроме команды CREATE TABLE, к секции DDL относятся также команды ALTER TABLE (изменение описания таблицы) и DROP TABLE (удаление таблицы). Так, например, с помощью команды ALTER TABLE можно добавлять или удалять столбцы или ограничения для уже созданной таблицы. Подробнее об этих командах можно прочитать в MSDN, а примеры их использования приведены в следующем параграфе.
Пример сценария создания БД "РОГА И КОПЫТА"
Рассмотрим полностью сценарий создания базы данных для фирмы «Рога и копыта». Сначала создаются родительские таблицы, затем дочерние, т.е., такие, которые содержат ограничения внешних ключей.
CREATE DATABASE kontora USE kontora
CREATE TABLE k_firm (firm_num NUMERIC(6) IDENTITY PRIMARY KEY, firm_name VARCHAR(100) NOT NULL, firm_addr VARCHAR(100), firm_phone NUMERIC(7) )
Мы не можем пока определить внешний ключ для поля staff_num, так как таблица "Сотрудники" еще не определена.
CREATE TABLE k_dept (dept_num NUMERIC(6) IDENTITY PRIMARY KEY, dept_short_name VARCHAR(10) NOT NULL, dept_full_name VARCHAR(100), staff_num NUMERIC(6) )
Таблица "Сотрудники" После создания этой таблицы сразу же можем определить внешний ключ для поля staff_num таблицы k_dept. Это можно сделать с помощью команды ALTER TABLE. CREATE TABLE k_staff (staff_num NUMERIC(6) IDENTITY, staff_name VARCHAR(30) NOT NULL, staff_post VARCHAR(30), dept_num NUMERIC(6), staff_hiredate DATETIME NOT NULL, staff_termdate DATETIME, CONSTRAINT pk_staff_num PRIMARY KEY (staff_num), CONSTRAINT fk_staff_dept_num FOREIGN KEY (dept_num) REFERENCES k_dept (dept_num) )
ALTER TABLE k_dept ADD CONSTRAINT fk_staff_num FOREIGN KEY (staff_num) REFERENCES k_staff(staff_num)
(contract_num NUMERIC(6) IDENTITY PRIMARY KEY, contract_date DATETIME DEFAULT GETDATE(), contract_type CHAR(1)
CHECK (contract_type IN ('A','B','C')), firm_num NUMERIC(6) NOT NULL, staff_num NUMERIC(6), CONSTRAINT fk_contract_firm_num FOREIGN KEY (firm_num) REFERENCES k_firm (firm_num), CONSTRAINT fk_contract_staff_num FOREIGN KEY (staff_num) REFERENCES k_staff (staff_num) )
ALTER TABLE здесь приводится просто для иллюстрации, как можно добавлять столбцы в уже созданную таблицу. CREATE TABLE k_bill (bill_num NUMERIC(6) IDENTITY PRIMARY KEY, bill_date DATETIME DEFAULT GETDATE(), bill_term DATETIME DEFAULT GETDATE()+30, bill_peni NUMERIC(6) DEFAULT 0, contract_num NUMERIC(6), CONSTRAINT fk_bill_contract_num FOREIGN KEY (contract_num) REFERENCES k_contract (contract_num), CONSTRAINT ch_bill_date CHECK (bill_term-bill_date<91) )
ALTER TABLE k_bill ADD bill_sum NUMERIC(6) DEFAULT 0 NOT NULL
Таблица "Платежи" Первичный ключ здесь состоит из нескольких полей, поэтому ограничение PRIMARY KEY можно создавать только на уровне таблицы, а не на уровне поля.
CREATE TABLE k_payment (payment_num NUMERIC(2) DEFAULT 0, bill_num NUMERIC(6), payment_date DATETIME DEFAULT GETDATE(), payment_sum NUMERIC(9,2), CONSTRAINT pk_payment_num PRIMARY KEY (payment_num, bill_num), CONSTRAINT fk_payment_bill_num FOREIGN KEY (bill_num) REFERENCES k_bill (bill_num) )
CREATE TABLE k_price (price_num NUMERIC(6) IDENTITY PRIMARY KEY, price_name VARCHAR(100) NOT NULL, price_sum NUMERIC(9,2), type_num NUMERIC(6) )
Первичный ключ здесь также состоит из нескольких полей, поэтому ограничение PRIMARY KEY можно создавать только на уровне таблицы, а не на уровне поля. Для каждого из этих полей здесь также создается ограничение внешнего ключа.
CREATE TABLE k_protokol (price_num NUMERIC(6) NOT NULL, bill_num NUMERIC(6) NOT NULL, kolvo NUMERIC(6) NOT NULL, price_sum NUMERIC(9,2), CONSTRAINT pk_protokol_num PRIMARY KEY (price_num, bill_num), CONSTRAINT fk_protokol_price_num FOREIGN KEY (price_num) REFERENCES k_price (price_num), CONSTRAINT fk_protokol_bill_num FOREIGN KEY (bill_num) REFERENCES k_bill (bill_num) )
Перед удалением каждой таблицы выполняется проверка – существует ли эта таблица. Информацию обо всех объектах БД можно получить из системной таблицы sysobjects. Тип объекта базы данных ‘U’ означает ‘user table’, т.е., пользовательская таблица, ‘F’ – ‘foreigh key’, т.е., внешний ключ. Для нашей базы данных сценарий может выглядеть следующим образом:
IF EXISTS(SELECT name FROM sysobjects WHERE name='k_payment' AND type='U') DROP TABLE k_payment IF EXISTS(SELECT name FROM sysobjects WHERE name='k_protokol' AND type='U') DROP TABLE k_protokol IF EXISTS(SELECT name FROM sysobjects WHERE name='k_bill' AND type='U') DROP TABLE k_bill IF EXISTS(SELECT name FROM sysobjects WHERE name='k_price' AND type='U') DROP TABLE k_price IF EXISTS(SELECT name FROM sysobjects WHERE name='k_contract' AND type='U') DROP TABLE k_contract IF EXISTS(SELECT name FROM sysobjects WHERE name='fk_staff_num' AND type='F') ALTER TABLE k_dept DROP CONSTRAINT fk_staff_num IF EXISTS(SELECT name FROM sysobjects WHERE name='k_staff' AND type='U') DROP TABLE k_staff IF EXISTS(SELECT name FROM sysobjects WHERE name='k_dept' AND type='U') DROP TABLE k_dept IF EXISTS(SELECT name FROM sysobjects WHERE name='k_firm' AND type='U') DROP TABLE k_firm IF EXISTS(SELECT name FROM sysobjects WHERE name='k_bill_list' AND type='U') DROP TABLE k_bill_list
Удаляются сначала дочерние таблицы, затем родительские. Таблицы "Отделы" и "Сотрудники" взаимно ссылаются друг на друга по внешним ключам, поэтому сначала приходится удалить одно из ограничений внешнего ключа, и только потом удалять таблицы. Можно ли в данном случае поступить наоборот, т.е. удалить ограничение из таблицы k_staff? Что еще нужно будет изменить в сценарии?
Вопрос.
Чем отличаются ограничения уровня поля и ограничения уровня таблицы?
Читайте также: E-mail addresses and servers Воспользуйтесь поиском по сайту: ©2015 - 2024 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...
|