Лабораторная работа: Знакомства с MS SQL Server

Ограничение UNIQUE обеспечивает уникальность значения для указанного столбца. В таблице может быть несколько ограничений UNIQUE.

Ограничение PRIMARY KEY обеспечивает целостность и уникальность значения для указанного столбца с помощью уникального индекса. Можно создать только одно ограниче- ние PRIMARY KEY для таблицы.

Ограничение CHECK обеспечивает целостность домена путем ограничения возможных значений, которые можно ввести в столбец. Условие может включать несколько логических выражений, соединенных операторами AND и OR.

Ограничение DEFAULT позволяет указать значение по умолчанию, если значение не задано. Оно может содержать значения констант, функции, или значение NULL. При этом, нельзя ссылаться на другой столбец таблицы, а также на другие таблицы, представления или хранимые процедуры. Его нельзя создавать для столбцов с типом данных timestamp или столб- цов со свойством IDENTITY.

Свойство IDENTITY указывает, что новый столбец является столбцом идентификато- ров. Для этого столбца формируется уникальное последовательное значение. Обычно исполь- зуется вместе с ограничением PRIMARY KEY для поддержания уникальности идентификато- ров строк в таблице. Свойство IDENTITY может назначаться столбцам типа tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0). Для таблицы можно создать только один столбец идентификаторов. Ограниченные значения по умолчанию и ограничения DEFAULT не могут использоваться в столбце идентификаторов. Необходимо указать как начальное значение, так и приращение, или же не указывать ничего. Если ничего не указано, применяется значение по умолчанию (1,1).

Для удаления таблиц используется команда DROP TABLE, которая имеет следующий синтаксис: DROP TABLE <название_таблицы>.

Практическая часть

Таблица Страны:

Таблица Управление:

Пример 1: Создать таблицу «Управление»:

CREATE TABLE Управление (

ID INT ,

Вид VARCHAR(20)

)

Пример 2: Удалить таблицу «Управление»:

DROP TABLE Управление

Пример 3: Создать таблицу «Управление», значения столбца «ID» сделать уникаль- ными, а столбец «Вид» запретить оставлять незаполненным:

CREATE TABLE Управление (

ID INT UNIQUE,

Вид VARCHAR(20) NOT NULL

)

Пример 4: Создать таблицу «Управление», в столбец «ID» разрешить вводить значения меньше 200, а для столбца «Вид» установить значение по умолчанию «Президентская респуб- лика»:

CREATE TABLE Управление (

ID INT CHECK (ID < 200),

Вид VARCHAR(20) DEFAULT 'Президентская республика'

)

Пример 5: Создать таблицу «Управление», столбец «ID» определить, как основной ключ, и настроить автоматический идентификатор с начальным значением 5 и с шагом 3:

CREATE TABLE Управление (

ID INT PRIMARY KEY IDENTITY(5,3), Вид VARCHAR(20)

)

Задание

Создать таблицу «Управление_ВашаФамилия». Определить основной ключ, иден- тификатор, значение по умолчанию

Создать таблицу «Страны_ВашаФамилия». Определить основной ключ, разреше- ние / запрет на NULL, условие на вводимое значение.

Создать таблицу «Цветы_ВашаФамилия». Определить основной ключ, значения столбца «ID» сделать уникальными, для столбца «Класс» установить значение по умолчанию

«Двудольные».

Создать таблицу «Животные_ВашаФамилия». Определить основной ключ, значе- ния столбца «ID» сделать уникальными, для столбца «Отряд» установить значение по умол- чанию «Хищные»

Лабораторная работа 8

Основы DML

Цель работы

Изучить команду INSERT.

Изучить команду UPDATE.

Изучить команду DELETE.

Изучить команду TRUNCATE TABLE.

Изучить конструкцию SELECT … INTO.

Теоретическая часть

Команда INSERT осуществляет добавление данных в определенную таблицу. После команды INSERT можно добавить необязательное ключевое слово INTO. Упрощенный син- таксис команды имеет следующий вид:

INSERT INTO <таблица> [(<список столбцов>) ] VALUES

(<список значений>)

Если добавляется две и более строки, тогда используется следующий синтаксис: INSERT INTO <таблица>

[(<список столбцов>) ] VALUES

(<список значений>),

…

(<список значений>)

Количество и тип значений должны совпадать со списком столбцов. Последователь- ность столбцов может не совпадать с таблицей. Список столбцов должен быть заключен в круглые скобки, а его элементы должны разделяться запятыми.

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

Если столбец имеет свойство DEFAULT, при отсутствии его, в таблицу вставляется значение по умолчанию.

Если столбец имеет свойство NULL, при отсутствии его, в таблицу вставляется значе- ние NULL.

Если столбец имеет свойство NOT NULL, его обязательно надо включить в список.

В списке значений для каждого столбца из указанных в списке столбцов должно быть одно значение. Список значений должен быть заключен в скобки.

Если значения в списке идут в том же порядке, как в таблице, и для каждого столбца таблицы определено значение, то список столбцов можно не указывать.

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

Если значение для столбца неизвестно, и столбец имеет свойство NULL, для него в списке значений можно указать NULL (без кавычек).

Если требуется перенести строку из одной таблицы в другую таблицу можно использо- вать следующий синтаксис:

INSERT INTO <таблица> [(<список столбцов>) ] SELECT

[(<список столбцов>) ]

FROM

исходная_таблица WHERE

<условие>

Типы данных в исходной и целевой таблицах должны совпадать.

Если таблицы имеют одинаковую структуру, можно после команды INSERT пропустить список столбцов, а после команды SELECT указать все столбцы, с помощью астериска «*».

Для изменения строк в таблице применяется команда UPDATE. Она имеет следующий формальный синтаксис:

UPDATE <таблица>

SET столбец1 = значение1, столбец2 = значение2, …, столбецN = значениеN [WHERE <условие>]

Использование условий необязательно, но тогда обновляются все строки таблицы. Ре- комендуется сначала выполнять выборку строк с помощью SELECT, только потом иcпользо- вать команду UPDATE.

Для удаления одной или нескольких строк из таблицы применяется команда DELETE. Она имеет следующий формальный синтаксис:

DELETE [FROM] <таблица> [WHERE <условие>]

Ключевое слово FROM необязательно.

Использование условий необязательно, но тогда удаляются все строки таблицы. Реко- мендуется сначала выполнять выборку строк с помощью SELECT, только потом иcпользовать команду DELETE.

Для удаления всех строк из таблицы можно использовать команду TRUNCATE TABLE. Она имеет следующий формальный синтаксис:

TRUNCATE TABLE <таблица>

Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов си- стемы и журналов транзакций.

Для создания новой таблицы и ее заполнения можно использовать конструкцию SELECT…INTO. Она имеет следующий формальный синтаксис:

SELECT

<список столбцов> INTO

<новая таблица> FROM

<исходная таблица>

Столбцы в новой таблице создаются в порядке, соответствующем списку выбора и по- лучают такие же имена, значения, типы данных и свойства допустимости значений NULL, ко- торые указаны в соответствующем выражении в списке выбора.

Практическая часть

Таблица Ученики:

Код для создания данной таблицы:

CREATE TABLE Ученики (

ID INT PRIMARY KEY IDENTITY(1,1), Фамилия VARCHAR(50) NOT NULL, Предмет VARCHAR(50) NOT NULL,

Школа VARCHAR(50) NOT NULL,

Баллы FLOAT CHECK ((Баллы >= 0) AND (Баллы <= 100)) NULL

)

Пример 1: В таблицу «Ученики» внести новую запись для ученика гимназии Маркина, который по физике набрал 96 баллов:

INSERT INTO Ученики

(Фамилия, Предмет, Школа, Баллы) VALUES

('Маркин', 'Физика', 'Гимназия', 96)

Пример 2: В таблицу «Ученики» внести две строки, для ученицы лицея Никишиной, которая по химии набрала 77 баллов, и для ученика школы № 18 Андреева, оценка которого по математике неизвестна:

INSERT INTO Ученики

(Фамилия, Предмет, Школа, Баллы) VALUES

('Никишина', 'Химия', 'Лицей', 77),

('Андреев', 'Математика', 'Школа №18', NULL)

Пример 3: В таблице «Ученики» изменить данные Андреева, оценку исправить на 87: UPDATE

Ученики

SET

Баллы = 87

WHERE

Фамилия = 'Андреев'

Пример 4: В таблице «Ученики» изменить данные Никишиной, школу исправить на

«Школа №31», а предмет на математику: UPDATE

Ученики

SET

Школа = 'Школа №31', Предмет = 'Математика'

WHERE

Фамилия = 'Никишина'

Пример 5: В таблице «Ученики» изменить данные всех учеников по математике, оценку уменьшить на 5 баллов:

UPDATE

Ученики

SET

Баллы = Баллы - 5

WHERE

Предмет = 'Математика'

Пример 6: В таблице «Ученики» удалить данные всех учеников из школы №18: DELETE FROM

Ученики WHERE

Школа = 'Школа №18'

Пример 7: Создать таблицу «Лицеисты» и скопировать туда всех лицеистов: SELECT

ID

,Фамилия

,Предмет

,Школа

,Баллы

INTO FROM

Лицеисты Ученики

WHERE

Школа = 'Лицей'

Пример 8: Очистить таблицу «Лицеисты»:

TRUNCATE TABLE Лицеисты

Задание

В таблицу «Ученики» внести новую запись для ученика школы № 18 Трошкова, оценка которого по химии неизвестна.

В таблицу «Ученики» внести три строки.

В таблице «Ученики» изменить данные Трошкова, школу исправить на № 21, пред- мет на математику, а оценку на 56.

В таблице «Ученики» изменить данные всех учеников по химии, оценку увеличить на 10%, если она ниже 60 баллов.

В таблице «Ученики» удалить данные всех учеников из школы №21.

Создать таблицу «Гимназисты» и скопировать туда данные всех гимназистов, кроме тех, которые набрали меньше 60 баллов.

Очистить таблицу «Гимназисты».

Лабораторная работа № 11

Программирование на SQL

Цель работы

Изучение переменных в T-SQL.

Изучение условных выражений.

Изучение циклов.

Теоретическая часть

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

Объявление переменной осуществляется с помощью оператора DECLARE. Упрощен- ный синтаксис команды имеет следующий вид:

DECLARE <@название> AS <тип>

Имена переменных в Transact-SQL начинаются с символа @.

Объявить сразу несколько переменных одним оператором DECLARE можно так: DECLARE <@название1> AS <тип1>, …, <@названиеN> AS <типN>