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

Базы данных

Пример

Операторы

Присвоение одного/нескольких значений:

Set/Select <@имя переменной>=<выражение>

Пример. Set @A=14 Select @Fam=”Иванов”, @Imj=”Иван”

Вывод значения одной переменной: Print<выражение>.

Вывод нескольких значений в виде строк: Select <выражение> [,...]

Пример. Select Top 3 * From Сотрудники --вывод первых трех строк

Составной оператор: Begin... End

Оператор перехода: GoTo<метка>... <метка:>....

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

Условный оператор: If<условие> <оператор> [Else <оператор>]

Оператор вывода сообщения: Raiserror(“<сообщение>”,16,10)

Оператор паузы Waitfor{Delay ‘i’ | Time ‘t’} - определение паузы длительностью I секунд (’00:00:45’) или до времени T (13:30:01).

Оператор (функция) выбора:

Case<выражение> -- исходное значение

When <значение 1> Then <результат 1> -- если выражение=значение 1

...

When <значение n> Then < результат n> -- если выражение=значение n

[Else <результат>] -- остальные варианты

End

Declare @A Int, @B Nvarchar (10) Set @A=2 -объявление переменных

Set @B=Case @A --оператор выбора варианта

When 1 Then “Один“ -- первый вариант

When 2 Then “Два“ -- первый вариант

Else “Много“ -- остальные варианты

End -- конец оператора выбора

Print @B -- вывод результата

Оператор цикла

While<условие> -- если условие истинно, то цикл повторяется

Begin --начало цикла

... [Break] --выход из цикла

... [Continue] -переход на начало следующего повторения цикла

End --конец цикла

Открытие базы: Use<имя базы данных>.

Создание правила: Create Rule<имя правила> As @<условие>.

Назначение/отмена правила:

Sp_bindrule/Sp_unbindrule<имя>, ‘<табли­ца>.<столбец>’ [, Futureonly]. Futureonly - назначение применяется только к столбцам, которые будут созданы далее, а не для которых уже задан тип данных.

Удалениеправил:Drop Rule<имя 1> [,<имя 2>]....

Создание стандартного значения:

Create Default<имя константы> As <значение>.

Назначение/отмена стандартного значения:

Sp_bindefult/Sp_unbindefult <имя>,‘<табли­ца>.<имя столбца>’[,Futureonly]. Переименованиеправила,значения:Sp_rename <имя>,<новое имя>.

Удалениезначения:Drop Default <имя 1> [,<имя 2>]....

Create Database<имя базы данных>

[On [Primary] <определение файла первичной группы>,...]

[Log On < определение файла журнала транзакций>,...] [For Restore]

<определение файла>::= (Name=<логическое имя файла>,

FileName=<физическое имя файла>

[, Size=<первоначальный размер файла в МБ>]

[, Maxsize=<максимально допустимый размер файла в МБ> | Unlimited]

[, Filegrowth=<приращение для увеличения размера файла в MB>])

Пример создания базы “Строительство”

Create Database Строительство

On (Name=Строительство_dat,

Filename='D:\PLEHEV\bd\prim\SQL Server\Строительство_dat.mdf',

Size=3MB, Filegrowth=1MB)

Log On (Name=Строительство_log,

Filename='D:\PLEHEV\bd\prim\SQL Server\Строительство_dat.log',

Size=2MB, Filegrowth=1MB)

Удаление баз данных: Drop Database<имя базы данных>,...

Пример удаления базы данных. Drop Database [Строительство]

Открытие базы данных: Use<имя базы данных>.

4.3.6.5. Таблицы

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

Create Table[<имя базы>] <имя таблицы>

({<определение столбца>

| <имя вычисляемой колонки> As <выражение>

| <ограничение целостности уровня таблицы>},...)

[On {<имя группы файлов с таблицей> | Default }]

[TextImage_On {<имя группы файлов с колонками типа text, ntext, image> | Default }]

<определение столбца>::= <имя колонки> <тип данных> [(p [, s])]

[[Default <константное выражение, задающее значение по умолчанию>

|Identity [(<начальное значение (1)>,<шаг приращения (1)>)

[Not For Replication]]]]

[RowGuidCol] [<ограничение целостности уровня колонки>]

<ограничение целостности уровня колонки>::=

[Constraint <имя ограничения>]

{[Null | Not Null] | [{Primary Key | Inique} [Clustered | NoClustered]

[With FillFactor=<фактор заполнения страниц индексов>]

[On {<имя группы файлов с таблицей> | Default }]]]

| [References <имя родительской таблицы>

[(<имя колонки связи в родительской таблице>)]

[Not For Replication] | Check (<условие проверки значения колонки>)}

<ограничение целостности уровня таблицы>::=

[Constraint <имя ограничения>]

{[{Primary Key | Inique} [Clustered | NoClustered] (<имя колонки>,...)

[With FillFactor=<фактор заполнения страниц индексов (0 -100)>]

[On {<имя группы файлов с таблицей> | Default }]]

| Foreign Key [(<имя колонки>,...)]References <имя родительской таблицы>

[(<имя колонки связи>,...)] [Not For Replication]

| Check (<условие проверки значений>)}

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

Drop Table Образование --удаление таблицы

Create Table Образование --создание таблицы

(KVO Int Primary Key Check (KVO>=1 and KVO<=100), --код вида образования

NVO nvarchar (20) not null) --наименование вида образования

Изменение структуры таблицы

Alter Table[<имя базы>] <имя таблицы>

{[Alter Column <имя изменяемой колонки>

{<тип данных> [(p [, s])] [Null | Not Null] | [{Add | Drop} RowGuidCol}}]

| Add {[<определение добавляемой колонки>]

| <имя вычисляемой колонки> As <выражение>},...

| With Check | With NoCheck] Add

{<добавляемое ограничение целостности уровня таблицы>},...

| Drop {<имя удаляемого ограничения целостности уровня колонки>

| Column <имя удаляемой колонки>},...

| {Check | NoCheck} Constraint {All | <имя вкл./откл. ограничения>,...}

| {Enabled | Disable} Trigger {All | <имя вкл./откл. триггера>,...} }

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

Alter Table Образование --корректировка таблицы

Alter Column NVO nvarchar (50) --изменение длины поля NVO

Alter Table Образование --добавление колонки с сокращенным

Add NSVO nvarchar (10) -- наименованием вида образования

Удаление таблиц: Drop Table<имя таблицы> [,...]

Переименование таблицы:

sp_rename<старое имя>, <новое имя>, ‘Object’.

Добавление строк в таблицу

Insert [Into]{<имя таблицы>[With (<блокировка>)] | <имя представления>|}

{ [(<имена столбцов)] {Values ({ Default | Null | <выражение>},...)

| <Select... запрос, строки которого включаются в таблицу>

| Execute <имя хранимой процедуры, результирующие строки которой включаются в запрос>}} | Default Values }}

Пример. Insert Должности Values (3, 'Инженер')

Изменение строк в таблице

Update{<имя таблицы>[With (<блокировка>)] | <имя представления>}

Set {<имя столбца={<выражение> | Default | Null}

| @<имя переменной=<выражение>

| @<имя переменной=<имя колонки>=<выражение>},...

From <исходные таблицы или представления>,...

[{Where <условия отбора строк для их изменения>

|Where Current Of {[Clobal]<имя курсора> | <имя переменной‑курсора>}}]

[Option (<режимы оптимизации>)]

При отсутствии фразы Where изменяются все строки таблицы.

Пример. Update Должности Set nd='Старший инженер' Where kd=3

Удаление строк из таблицы

Delete[From] {<имя таблицы> [With (<блокировка>)]

| <имя представления>}

From <исходные таблицы или представления>,...

[{Where <условия отбора строк для их удаления>

|Where Current Of {[Clobal]<имя курсора> | <имя переменной‑курсора>}}]

[Option (<режимы оптимизации>)]

Пример. Delete Должности Where kd=3

4.3.6.6. Запросы

Select[All | Distinct | Top n [Percent]] With Ties <список> [Into <таблица>]

From {<таблица>|<представление>} [(<параметры оптимизации>)], …

[Where <условие>] [Group By <поля группировки>] [Having <условие>]

[Order By <поля сортировки>] [Compute <функции>] [For Browse]

Рассмотрим дополнительные возможности (п. 1.3.8.3).

Into <таблица> - формирование таблицы.

<параметры оптимизации> ‑ Nolock (чтение несохраненных данных – данных, на которые могут повлиять другие пользователи), HoldBlock/Tab­Lock (блокировка страницы/таблицы для других пользовате­лей), UpdLock (позволяет пользователям читать данные из таблицы, но не обновлять), TabLockx (блокировка чтения и корректировки таблицы для других пользовате­лей), Paglock (устанавливается в том случае, если установлена блокировка типа TabLockx и вы разрешаете блокировку данных другим пользователям), Index=<имя/номер используемого индекса> (если указать ноль, то сканируется таблица).

Where <условие> ‑ в этой директиве можно дополнительно указать (если не использовать фразу Join (п. 1.3.8.3)) полное (*=*), ле­во­сто­ро­нее (*=), правостороннее (=*) и внутреннее (=) объединение таблиц в ус­ло­­ви­ях свя­зы­вания таблиц (Where Cotrudniki.koddol*=Dolgnosti.koddol) (п. 1.3.8.3).

For Browse - чтение таблицы, которая редактируется другим клиентом.

Compute <итоговые функции Sum(), Avg(), Min(), Max(), Count()>

[By <поля группировки>] ‑ формирование дополнительных строк с проме­жу­точными итогами, аналогичных строкам в подножиях групп отчетов. Поля группировки должны совпадать с полями сортировки.

Пример вывода итоговой суммы продаж товаров в конце запроса.

SELECT Товары.nt AS Товар, --наименование товара

[Продажа товаров].dp AS [Дата продажи], --дата продажи

[Продажа товаров].st AS Сумма --стоимость продажи

FROM [Продажа товаров]

INNER JOIN Товары ON [Продажа товаров].kt = Товары.kt --связывание

COMPUTE Sum([Продажа товаров].st) --вывод строки с итогом по всем товарам

Пример вывода промежуточных итогов продажи товаров в сто­имост­ном выражении за каждый день.

SELECT Товары.nt AS Товар, --наименование товара

[Продажа товаров].dp AS [Дата продажи], --дата продажи

[Продажа товаров].st AS Сумма --стоимость продажи

FROM [Продажа товаров]

INNER JOIN Товары ON [Продажа товаров].kt = Товары.kt --связывание

ORDER BY [Продажа товаров].dp desc --сортировка по дате продажи

COMPUTE Sum([Продажа товаров].st) By [Продажа товаров].dp --итоги

Пример итогового запроса с группировкой с итогами ежедневных про­даж по товарам в натуральном выражении.

SELECT Товары.nt AS Товар, --первая колонка

[Продажа товаров].dp AS [Дата продажи], --вторая колонка

[Единицы измерения].ne AS [Единица измерения], --третья колонка

SUM([Продажа товаров].kol) AS Количество --итоговое количество

FROM [Единицы измерения] INNER JOIN --связывание таблицы

[Продажа товаров] ON [Единицы измерения].ke = [Продажа товаров].ke

INNER JOIN Товары ON [Продажа товаров].kt = Товары.kt --связывание

GROUP BY Товары.nt, [Продажа товаров].dp, [Единицы измерения].ne

Пример запроса с итогами крупных (более 300000 рублей) продаж по товарам в стоимостном выражении.

SELECT Товары.nt AS Товар, --товары

SUM([Продажа товаров].st*[Продажа товаров].kol) AS Сумма --итоги

FROM [Продажа товаров] --исходные таблицы

INNER JOIN Товары ON [Продажа товаров].kt=Товары.k t --связывание

GROUP BY Товары.nt --группировка

HAVING SUM([Продажа товаров].cena*[Продажа товаров].kol)>300000

ORDER BY 2 desc--сортировка по убыванию итоговой суммы (колонка 2)

Пример формирования запроса с фамилиями высокооплачиваемых сотрудников, у которых оклад в два раза выше среднего по предприятию. SELECT s.fam [фамилия сотрудника], oklad оклад --колонки запроса

FROM [Сотрудники] s --исходная таблица и ее псевдоним

WHERE oklad*2>= --условие отбора записей

(SELECT avg (oklad) FROM Сотрудники) --подзапрос - средний оклад

ORDER BY 2 desc --сортировка по убыванию оклада (вторая колонка)

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

DROP table [#Таблица крупных подразделений] --удаление таблицы

SELECT [Подразделение], count(*) [Число сотрудников] --колонки таблицы

Into [#Таблица крупных подразделений] -имя создаваемой таблицы

FROM [Базовое представление] --таблица формируется из базового запроса

GROUP BY [Наименование подразделения] -поле группировки

HAVING count(*)>30 --условие включения группы в таблицу

GO --выполнить предыдущий оператор

SELECT * FROM [#Таблица крупных подразделений] -вывод таблицы

4.3.6.7. Представления

Создание/изменение представления (вида):

Create/Alter View<имя вида> [(<новое имя столбца представления>,...)]

[With encryption] As Select <текст запроса> [With Check Option]

With encryption - шифрование текста команды создания вида.

With Check Option ‑ блокировка сервером изменений, приводящих к исчезновению строк из представления (например, при Where Oklad>100000 блокируются изменения, которые устанавливают оклад меньше 100000).

В запросе запрещается использование переменных, фраз Order By, Compute, Into и ссылки на временные таблицы.

Переименование представления:

sp_rename <старое имя>, <новое имя>, ‘Object’.

Удаление представлений: Drop View<имя представления> [, …].

Просмотр параметров/зависимостей представления:

sp_help/sp_depends<имя представления>.

4.3.6.8. Индексы

Создание индекса (размером до 900 байт):

Create [Unique] [{Clustered | Noclustered} ]

Index <имя индекса> On <имя таблицы> (<поле 1>[, <поле 2>],…)

[With <спиcок дополнительных параметров>] [On <имя группы файлов>]

Clustered/ Noclustered - кластерный/обычный индекс.

Список дополнительных параметров ‑ Ignore_Dup_Key (игнорирование пов­­­­то­­ряющихся индексов, если указан уникальный индекс Unique), Sorted_Data (индекс уже отсортирован), Sorted_Data_Reorg (физически от­сор­­тировать таблицу по индексу), Ignore_Dup_Row (удаление пов­то­ряющихся значений), Drop_Existing (кластерный индекс создается снова), On <имя группы файлов> ‑ индекс создается в отдельной файловой группе.

Удаление индексов:

Drop Index[<владелец таблицы>. ]<имя таблицы>. <имя индекса> [, …]

Переименование индекса производится хранимой процедурой:

sp_rename ‘<старое имя>’, ‘<новое имя>’, ‘INDEX’

Перестроение индексов для перераспределения индексных страниц:

Dbcc Dbreindex([‘[<имя базы>. [<владелец таблицы>. ]]<имя таблицы>’

[,<имя индекса>[,<фактор заполнения>]]]) [With No_Infomsgs]

При отсутствии индекса перестраиваются все индексы таблицы. Если фактор заполнения равен нулю или не указан, то используется фактор заполнения, указанный при создании индекса. Параметр With No_Infomsgs блокирует вывод всех сообщений с уровнем серьезности с 0 до 10. Эта команда блокирует всю таблицу (dbcc dbreindex ('Сотрудники')).

4.3.6.9. Статистика

Статистикаиспользуется для оптимизации доступа к базе данных и содержит информацию о распределении данных в таблице и в индексах. Сер­вер периодически автоматически обновляет эту информацию в пери­оды наименьшей загрузки путем сканирования таблиц.

Создание статистики выполняется командой:

Create Statistics<имя статистики> On <имя таблицы> (<имя колонки>,...)

[With [{Fullscan | Sample <процент сканирования> Percent]}]

Пример. Create Statistics Стат_Сотрудники ON [Сотрудники] (tn, fam)

Обновление статистики вручную выполняется командой:

Update Statistics<имя таблицы> [index | <имя статистики>,... ]

[With [Fullscan | Sample <процент сканирования> {Percent | Rows}] ]

[All | Columns | Index]

Пример. Update Statistics [Сотрудники]

Удаление статистики реализуется командой:

Drop Statistics<имя таблицы>. <имя статистики>,...

Пример. Drop Statistics Сотрудники.Стат_Сотрудники

Просмотр статистики выполняется командой:

Dbcc Show_ Statistics(<имя таблицы>, <имя статистики или индекса>)

Справочная информация выводится в виде таблицы с колонками:

Update ‑ дата и время последнего обновления статистики.

Rows/Rows Sample ‑ число строк в таблице/зафиксированных в статистике.

Steps ‑ число интервалов распределения, созданных в статистике.

Density/All density ‑ плотность данных в индексе/в конкретном индексе.

Average key length ‑ средняя длина значения (плотность) в столбце.

Columns ‑ имя столбца таблицы, для которого выводится плотность.

Steps ‑ шаги диаграммы, созданные для статистики.

Пример. Dbcc Show_Statistics ([Сотрудники],Стат_Сотрудники)

4.3.6.10. Фрагментация

Вывод данных о фрагментации(п. 4.3.1.1) выполняется командой:

Dbcc Showcontig(<идентификатор таблицы> [, <идентификатор индекса>])

Для получения индекса используется команда Select Object_id.

Дата добавления: 2014-12-10