SQL Server – коротко о главном

В качестве среды программирования мы будем использовать СУБД (систему управления базами данных) SQL Server.

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

– многопользовательская поддержка;

– многоплатформность;

– поддержка 64-разрядной архитектуры;

– масштабируемость (многопроцессорная обработка и поддержка терабайтных БД – 10е12 байт);

– стандарт SQL92 (язык Transact SQL);

– параллельные архивирование и восстановление БД;

– репликация данных;

– распределенные запросы;

– распределенные транзакции;

– динамические блокировки;

– интеграция с IIS и InterDev.

Компоненты (жирным шрифтом выделено то, что мы будем активно использовать):

– Books Online – справочная система;

– Client Network Utility – настройка клиента для работы в сети;

– Enterprise Manager – основное средство для администрирования;

– Import and Export Data – конвертирование данных;

– Performance Monitor – монитор производительности;

– Query Analyzer – среда для выполнения запросов;

– Server Network Utility – настройка сервера для работы в сети;

– Service Manager – утилита для запуска и останова системы.

Администрирование баз данных в основном осуществляется через Enterprise Manager.

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

Стандартные базы данных:

– master – системная БД с конфигурацией SQLServer

– model – шаблон для всех пользовательских БД

– msdb – планирование заданий по расписанию и т.п.

– tempdb – для временных объектов

Примеры БД:

– pubs

– NorthWind

БД состоит из:

– диаграмм (автоматически не создаются!);

– таблиц (пользовательских и системных);

– представлений;

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

– пользователей;

– ролей;

– правил;

– значений по умолчанию;

– пользовательских типов.

БД можно создать:

– с помощью мастера (для чайников);

– с помощью визуальных средств Enterprise Manager:

Databases – New database:

– главный файл имеет тип.MDF (один);

– дополнительные файлы –.NDF (м.б. несколько);

– файл журнала транзакций –.LDF.

Можно задавать максимальный размер БД и рост в % и Мб.

– с помощью средств ER-проектирования, например Platinum ERWin,

– с помощью SQL-команд, запуская их из Query Analyzer.

Query Analyzer: позволяет выполнять как отдельные SQL-команды, так и SQL-файлы целиком.

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

Результаты запроса выдаются в нижнем окне. Оно имеет две закладки – для результатов и для сообщений. Результаты можно выдавать в виде текста или в виде таблицы. Если в каком-то из окон не читается русский текст, поменяйте в этом окне шрифт на русифицированный.

Рекомендация.

Самостоятельно изучите интерфейс Enterprise Manager и Query Analyzer. Поэкспериментируйте с базами данных pubs и NorthWind.

Задание для индивидуальной работы 3

Не используйте визуальные средства SQL server для создания вашей базы данных. Используйте только Query Analyzer. Как создавать БД и таблицы – см. следующую тему.

DDL. Таблицы

DDL – Data Definition Language – язык описания данных, составная часть SQL. Рассмотрим команды создания базы данных и таблиц.

Для создания базы данных служит команда

CREATE DATABASE имя_БД

Для активизации базы данных служит команда

USE имя_БД

Выполняйте команду активизации базы данных при каждом входе в Query Analyzer, поскольку по умолчанию в качестве активной установлена БД master.

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

Краткий формат этой команды (квадратные скобки означают необязательные элементы):

CREATE TABLE имя_таблицы(

Список_описаний_полей,

[Список_ограничений_таблицы]);

Более подробно смотрите в Books Online.

Описание поля имеет формат:

Имя_поля тип_поля[(размер)]

[NULL] [NOT NULL]

[PRIMARY KEY]

[UNIQUE]

[IDENTITY]

[DEFAULT умолчание]

[CHECK (условие)]

[REFERENCES имя_таблицы(имя_поля)]

Чаще всего используются типы полей:

VARCHAR – строковый тип переменной длины;

NUMERIC – числовой тип;

DATETIME – тип дата/время.

(какие еще типы полей есть в SQL server? – обращайтесь к Book s Online )

NULL – специальное “неопределенное” значение, предусмотренное стандартом SQL. Ограничение NULL/NOT NULL служит для указания, что данный тип поля допускает/запрещает ввод NULL-значений.

PRIMARY KEY – ограничение, указывающее, что в данной таблице данное поле представляет собой первичный ключ (составной первичный ключ таким образом объявлять нельзя!). При использовании этого ограничения создается первичный индекс.

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

IDENTITY начальное_значение, приращение – ограничение, указывающее, что данное поле представляет собой счетчик, т.е, значения в данное поле вставляются автоматически с нарастанием при вставке строки. Если “начальное_значение” и “приращение” пропущены, они полагаются равными 1.

DEFAULT умолчание – очевидно, значение по умолчанию, т.е., значение, которое присваивается данному полю, если при вставке новой строки этому полю не было явно присвоено некоторое значение.

CHECK (условие)- условие на поле, которое будет проверяться при вводе новых строк.

Например, в таблице “Предприятия” номер предприятия будет первичным ключом и счетчиком, название фирмы не допускает значений 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 уровня таблицы может быть определено, например,так:

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 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.

CREATE TABLE k_staff

(staff_num NUMERIC(6) IDENTITY,

staff_name VARCHAR(30) NOT NULL,

staff_post VARCHAR(30),

dept_num NUMERIC(6) NOT NULL,

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)

Таблица "Договоры"

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)

)

Таблица "Счета"
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? Что еще нужно будет изменить в сценарии?

Вопрос.

С помощью каких команд можно изменить структуру уже созданной таблицы? (см. Books Online)

Вопрос.

Какие еще команды относятся к DDL? (см. Books Online)

Задание для индивидуальной работы 4.

Напишите и отладьте SQL-сценарий создания вашей базы данных и таблиц для нее.

Дата добавления: 2015-05-09