Изменение таблицы, которая уже была создана.

Команда ALTER TABLE (изменить таблицу), обычно осуществляет добавление столбцов в таблицу, иногда может удалять столбцы или изменять их размеры – осуществлять добавление и удаление ограничений (например, таких как NOT NULL). Обычный синтаксис команды, предназначенной для добавления столбца в таблицу выглядит следующим образом:

 

ALTER TABLE <имя таблицы>

ADD <имя столбца><тип данных>(размер);

 

По этой команде для существующих в таблице строк добавляется столбец, в который заносится NULL-значение. Новый столбец становится последним столбцом в таблице. Допустимо добавление нескольких столбцов с помощью одной команды; в этом случае их определения разделяются запятой.

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

DESC имя таблицы

Исключение таблицы.

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

Синтаксис удаления данных из таблицы:

DELETE FROM имя таблицы;

Синтаксиси удаления пустой таблицы:

DROP TABLE имя таблицы;

 

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

 

 

Выполнение лабораторной работы:

 

1. В начале работы загрузите программный продукт: Пуск Программы Quest Software - TOAD - TOAD.

2. В диалоговом окне “Вход в систему” выберите Database – ZED, User - 4294, пароль Password: 4294 и выберите Connect as - Normal.

 

Рисунок 11.2.

3. Откройте два окна для работы: SQL - окно ввода командной строки и Schema Browser - окно просмотра объектов, размещенных по соответствующим вкладкам.

 

4. С помощью команды CREATE NEW TABLE контекстного меню или CREATE TABLE командной строки (см. рисунок 3.4.) создайте таблицу PERSONS_ (номер).

 

Рисунок 11.3.

и т.д.

или создать таблицу с помощью окна SQL:

 

Рисунок 11.4.

Запуск на выполнение команды

 

 

Рисунок 11.5.

 

5. Просмотрите структуру созданной таблицы с помощью вкладки Columns (Столбцы таблицы).

6. С помощью команды CREATE NEW TABLE контекстного меню или командной строки (см. раздел «Общие сведения») создайте остальные таблицы.

7. Внешние ключи добавляются в структуру таблицы с помощью команды Alter table. Например, создание внешнего ключа таблицы Gruppy_1, ссылающегося на первичный ключ таблицы Formy_ob_1:

alter table gruppy_1

add constraint gr1_fk

foreign key (id_f) references formy_ob_1 (id_f);

8. Добавьте записи в таблицу, перейдя на вкладку Data (Данные). Зафиксировать записи следует знаком «галочка».

9. Удалить таблицу можно с помощью команды Drop Table.

Контрольные вопросы:

1. Что обязательно учитывается при создании таблиц и определении состава полей?

2. Для чего предназначены primary key, foreign key?

3. Как работает ограничение целостности данных?

4. Какими правилами необходимо руководствоваться для заполнения данных?

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

«Создание запросов»

Цель работы: Количество часов: 4

Изучить средство построения запросов (SQL) для вывода на экран некоторых данных, объединенных общим критерием.

 

Рабочее задание:

 

Выполнить запросы посредством языка SQL:

 

1. Составить список студентов группы «4294у», обучающихся сейчас на втором курсе. Список должен содержать: номер группы, фамилия студента, имя студента, отчество студента, дату начала обучения студента.

 

2. Подсчитать количество студентов, обучающихся на дневной форме обучения на втором факультете. Результатом должно быть одно число.

 

3. Составить список специальностей (название и шифр), предоставляемых каждым факультетом.

 

4. Составить список студентов, которые были отчислены, а потом снова восстановлены. Список должен содержать: фамилию студента, имя студента, отчество студента, номер группы (из которой отчислили)), дату отчисления, номер группы (в которую восстановили), дату восстановления. Использовать представления (view) для хранения промежуточных данных об отчисленных, восстановленных и сравнивать в запросе эти данные.

 

5. Вывести фамилия И.О. студентов, обучающихся в определенной группе (для определения инициалов ИО использовать функцию Substr, для соединения значений двух полей – конкатенацию (||), а номер группы в качестве параметра(nomer=:p_nomer))

 

6. Вывести номер группы заданного факультета (параметром), в состав которой входит наибольшее количество студентов (через предварительно созданное представление).

 

 

Общие сведения:

 

Все запросы в SQL конструируются на базе одной команды. Структура этой команды проста, потому что её можно расширять для того, чтобы выполнить очень сложные вычисления и обработку данных. Эта команда называется SELECT.

 

Команда Select.

В простейшей форме команда SELECT дает инструкцию базе данных для поиска информации в таблице. Синтаксис:

SELECT <имя столбца>,<имя столбца>, …

FROM <имя таблицы>;

Например,

SELECT kod_rab,family,name,otch … FROM adres;

 

Данная команда просто выводит все данные из таблицы. Также выводит на экран монитора заголовки столбцов.

Примечание! Запрос по своей природе не обязательно упорядочивает выходные данные каким – либо определенным образом. Одна и та же команда, выполненная над одними и теми же данными в различные моменты времени, в результате выдает данные, упорядоченные по-разному.

Если необходимо увидеть каждую колонку таблицы, существует упрощенный вариант сделать это. Можно использовать символ *, который заменяет полный список столбцов.

SELECT * FROM <имя таблицы>;

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

 

Применение предложения WHERE.

Предложение WHERE в команде SELECT просматривает таблицу строка за строкой и для каждой строки проверяет, истинно ли условие.

Синтаксис команды:

SELECT <имя столбца>,<имя столбца>, … FROM <имя таблицы> WHERE <условие>;

 

Примечание! Совершенно необязательно, чтобы столбец, используемый в предложении WHERE, был представлен в числе тех столбцов, которые необходимо видеть среди выходных данных. Также к предложению WHERE относятся все комментарии, сделанные ранее, т.е. можно исключать дублирующиеся значения или переставлять столбцы в командах SELECT, использующих WHERE.

 

Реляционные операторы.

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

 

= равно

> больше, чем

< меньше, чем

>= больше или равно

<= меньше или равно

<>неравно

 

Эти операторы имеют стандартное значение для числовых величин. Их определение от символьных значений зависит от используемого формата представления. SQL сравнивает символьные значения в терминах соответствующих чисел, определенных в формате преобразования. Предположим, необходимо увидеть всех работников с возрастом более 25.

SELECT * FROM adres WHERE vozrast > 25;

 

В выходных данных будет информация, начиная с 25,1.

 

 

Булевы операторы.

SQL распознает основные булевы операторы. Булевы выражения – это те выражения, относительно которых можно сказать истинны они или ложны. Булевы операторы связывают одно или несколько значений “истина/ложь” и в результате получают единственное значение “истина” или “ложь”. Стандартные булевы операторы, распознаваемые SQL – это AND, OR, NOT.

Основные принципы их действия:

§ AND берет два булевых выражения в качестве аргументов и дает в результате истину, если они оба истинны;

§ OR берет два булевых выражения в качестве аргументов и оценивает результат как истину, если хотя бы один из них истинен;

§ NOT берет единственное булево выражение в качестве аргумента и изменяет его значение с истинного на ложное или с ложного на истинное.

 

Используя условия с булевыми операторами, можно значительно увеличить их избирательную мощность.

 

Предположим необходимо увидеть данные на работников, чья фамилия начинается на К и проживают на улице Ломоносова:

 

SELECT * FROM adres

WHERE family like’K%’ AND mesto_git like’%Ломоносова%’;

 

При использовании OR, будут получены сведения обо всех работниках, которые либо живут на улице Ломоносова либо их фамилия на К:

 

SELECT * FROM adres

WHERE family like’K%’ OR mesto_git like’%Ломоносова%’;

 

NOT дает возможность получить отрицание булева выражения:

 

SELECT * FROM adres

WHERE family NOT like’K%’ OR mesto_git NOT like’%Ломоносова%’;

 

Специальные операторы.

Кроме булевых операторов и операторов сравнения, в SQL довольно широко используются специальные операторы, такие как IN, BETWEEN, LIKE.

 

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

 

SELECT * FROM <имя таблицы>

WHERE <имя столбца> IN (‘значение1’,’значение2’,…);

 

Пример: чтобы получить сведения о продавцах, живущих либо в Москве, либо в Киеве можно написать команду:

 

SELECT * FROM prodav WHERE city=‘Москва’ Or city=’Киев’;

Однако, при наличии оператора IN команда пишется существенно проще:

 

SELECT * FROM prodav WHERE city IN (’Киев’, ‘Москва’);

 

Как видно из примера, IN определяет множество, элементы которого точно перечисляются в круглых скобках и разделяются запятыми. Если в поле, имя которого указано слева от IN, есть одно из перечисленных в списке значений (требуется точное совпадение), то предикат считается истинным. Если элементы множества имеют числовой, а не символьный тип, то одиночные кавычки непосредственно слева и справа от значения необходимо опустить. Например, можно найти всех покупателей, обслуживаемых продавцами 123, 124, 125. Запрос будет выглядеть:

 

SELECT * FROM prodav WHERE person_n IN (123,124,125);

 

Оператор BETWEEN сходен с IN. Вместо перечисления элементов множества, как это делается в IN, BETWEEN задает границы, в которые должно попадать значение, чтобы предикат был истинным. Используется ключевое слово BETWEEN, за которым следуют начальное значение, ключевое слово AND и конечное значение. Также как и IN, BETWEEN чувствителен к порядку: первое значение в предложении должно быть первым в соответствии с алфавитном или числовым порядком. Синтаксис команды:

 

SELECT * FROM <имя таблицы> WHERE <имя столбца> BETWEEN значение1 AND значение2;

 

Пример: для запроса о продавцах, комиссионные которых имеют величину в диапазоне 0.10 и 0.12, команда будет выглядеть:

 

SELECT * FROM prodav WHERE comm BETWEEN 0.10 AND 0.12;

 

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

 

SELECT * FROM prodav WHERE (comm BETWEEN 0.10 AND 0.12) AND NOT comm IN (0.10,0.12);

 

Как видно из этой записи, оператор BETWEEN можно комбинировать с булевыми операторами для создания более сложных запросов.

Аналогично всем операторам сравнения, BETWEEN действует и на символьных полях, т.е. для выборки данных можно воспользоваться алфавитным порядком. Следующий запрос выбирает всех покупателей имена которых попадают в заданный алфавитный диапазон:

 

SELECT * FROM pokypat WHERE name BETWEEN ‘A’ AND ‘G’;

LIKE применим только к полям типа CHAR или VARCHAR, поскольку он используется для поиска подстрок: т.е. LIKE осуществляет просмотр строки для выяснения: входит ли заданная подстрока в указанное поле. С этой же целью используются шаблоны – специальные символы, которые могут обозначать все, что угодно. Существует два типа шаблонов, используемых с LIKE:

- символ “подчеркивание” (_) заменяет один любой символ. Например, образцу “b_t” соответствует “bat” или “bit”, но не соответствует “brat”;

- символ “процент” (%) заменяет последовательность символов произвольной длины, в том числе и нулевой. Например, образцу ‘%p%t’ соответствуют ‘put’, ‘posit’, ‘opt’, но не ‘spite’.

 

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

SELECT * FROM <имя таблицы> WHERE <имя столбца> LIKE <значение>;

Пример, можно найти покупателей, фамилии которых начинаются на К:

SELECT * FROM pokypat WHERE name LIKE ‘K%’;

 

LIKE необходим при осуществлении поиска имени или другого значения, полное написание которого неизвестно. Предположим, не совсем понятно, как правильно написать фамилию одного из продавцов, чтобы вывести о нем сведения – Тимченко или Тимчинко. Команда на запрос будет выглядеть так:

 

SELECT * FROM prodav WHERE name LIKE ‘Т_ _ _ _н%’;

Каждый символ подчеркивания в шаблоне представляет единственный символ, поэтому среди выходных данных будут только те фамилии, у которых между Т и Н находится 4 буквы. Символ % в конце строки необходим для тех случаев, когда длина поля name превосходит количество букв в конкретном имени. Т.е. если в слове Тимченко – 8 букв, а длина поля name определена как 30 символов, то в данном случае последовательностью будет ряд пробелов.

Примечание! Символ подчеркивания используется не только как специальный шаблон, он может использоваться в именах, тогда для того, чтобы найти имена, в который имеется символ подчеркивания, используется Escape – символ. Этот символ используется в предикате непосредственно перед символом процента или подчеркивания и означает, что следующий за ним символ интерпретируется именно как обычный символ, а не как символ шаблона. Например, поиск символа подчеркивания в столбце name можно задать следующим образом:

 

SELECT * FROM prodav WHERE name LIKE ‘%/_%’ESCAPE’/’;

Предложение ESCAPE определяет ‘/’ как Escape – символ, который используется в LIKE-строке, за ним следует символ процента, символ подчеркивания или сам символ ‘/’, т.е. тот символ, поиск которого будет осуществляться в столбце и который уже не распознается как символ шаблона. Escape – символ может быть единственным символом и применяться только к единственному символу, который следует за ним. В приведенном примере начальный и конечный символы процента являются символами шаблона, только символ подчеркивания представляет собой как таковой.

 

Специальные операторы IN, BETWEEN и LIKE могут непосредственно предшествовать булеву оператору NOT. Этим они отличаются от операторов сравнения, которые должны содержать NOT перед всем выражением.

Например, если необходимо исключить из выходных данных все пустые значения, то нужно использовать NOT для того, чтобы придать предикату противоположное значение:

SELECT * FROM prodav WHERE comm IS NOT NULL;

 

Можно также использовать NOT и IN. Например, чтобы выбрать всех продавцов, кроме 123,124,125 используется запрос:

SELECT * FROM prodav WHERE person_n NOT IN (123,124,125);

 

Другой способ выразить то же самое:

SELECT * FROM prodav WHERE NOT person_n IN (123,124,125);

Аналогичным образом используются NOT BETWEEN и NOT LIKE.