Практическое занятие № 2 (часть 2)

 

Тема: "Работа с таблицами в разных СУБД"

Цель работы: получить практические навыки по созданию и обработке таблиц с помощью команд языка SQL и графической утилиты в СУБД MySQL и SQLite

 

Ход работы

 

Работа с MySQL

Создайте базу данных Sklad

 

Указания: запустите утилиту EMS SQL Manager. В правой части окна щелкните на команде "Создать новую БД". В окне введите имя БД "Sklad" и щелкните Далее. В следующем окне введите параметры учетной записи на сервере. Оставьте все параметры по умолчанию и введите пароль "111" (у вас может быть другой пароль). Также укажите кодировку символов utf-8. На следующем шаге вы увидите команду SQL, которую создала утилита. Щелкните на кнопке "Запустить".

После создания БД появится окно с дополнительными параметрами. Убедитесь, что кодировка клиента также установлена в значение utf-8, щелкните ОК.

В результате в левой части окна появится значок созданной БД. Двойным щелчком откройте БД для дальнейшей работы.

 

С помощью команды SQL в базе данных Sklad создайте таблицы в соответствии со схемой:

Таблица Tovar имеет следующие поля:

 

id_tovar - "Идентификатор товара" - ключевое, счетчик;

kod - «Код товара» – текстовое, уникальное, обязательное;

imya - «Наименование» – текстовое, обязательное;

proizv - «Производитель» – текстовое, обязательное;

dataizgot - «Дата изготовления» – дата;

edizm - «Единица измерения» – текстовое, обязательное, значение по умолчанию «шт»;

cenaopt - «Цена оптовая» – вещественное числовое с двумя знаками после запятой, обязательное.

 

Все поля кроме «Дата изготовления» обязательны для заполнения.

 

Указания:  для ввода и запуска команд SQL в окне утилиты выполните команду "Инструменты – Показать редактор SQL". В окне введите команду:

 

create table tovar

(id_tovar integer primary key auto_increment,

kod varchar(5) not null unique,

imya varchar(20) not null,

proizv varchar(20) not null,

dataizgot date,

edizm varchar(5) not null default 'шт',

cenaopt decimal(6,2) not null)

engine=innodb;

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите элемент "Таблицы" внутри БД "Sklad" и нажмите F5 для обновления. В результате отобразится созданная таблица.

 

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

 

Указания: двойным щелчком откройте созданную таблицу. В правой части отобразится структура таблицы с перечнем ее полей. Перейдите на закладку "Данные" и с помощью кнопки "+" добавьте в таблицу записи. После ввода всех данных щелкните на команде "Обновить".

 

С помощью команды SQL в базе данных Sklad создайте таблицу с именем Uchet для учета поступлений и продаж товаров по датам. Таблица имеет следующие поля:

 

id_uchet - «Идентификатор учета» – ключевое, счетчик;

id_tovar - «Идентификатор товара» – целое, внешний ключ для связывания с таблицей Tovar. Каскадное удаление данных;

data - «Дата» – дата, обязательное;

prihod - «Приход» – целые числа, не обязательное, значение по умолчанию 0;

rashod - «Расход» – целые числа, не обязательное, значение по умолчанию 0.

 

Указания: удалите текст предыдущего запроса и введите команду:

 

create table uchet

(id_uchet integer primary key auto_increment,

id_tovar integer not null,

data date not null,

prihod integer default 0,

rashod integer default 0,

constraint TovarUchet foreign key(id_tovar) references tovar(id_tovar) on delete cascade) ##поле для связывания

engine=innodb;

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите элемент "Таблицы" внутри БД "Sklad" и нажмите F5 для обновления. В результате отобразится созданная таблица. 

 

Откройте созданную таблицу и заполните ее 10 записями (по две записи для каждого товара из главной таблицы).

 

Указания:  таблица связана с главной таблицей по полю id_tovar поэтому при вводе кодов можно брать только такие значения, которые имеются в этом же поле главной таблице. Двойным щелчком откройте созданную таблицу. В правой части отобразится структура таблицы с перечнем ее полей. Перейдите на закладку "Данные" и с помощью кнопки "+" (вставить запись) добавьте в таблицу записи. После ввода всех данных щелкните на команде "Обновить".

 

Проверьте работу каскадного удаления данных.

 

Указания: двойным щелчком откройте таблицу Tovar, перейдите на закладку "Данные", выберите любую запись и щелкните на кнопке "-" (удалить запись) , а затем на команде "Обновить". Откройте таблицу Uchet и убедитесь, что в таблице произошло каскадное удаление подчиненных записей.

 

C помощью команды Transact SQL в таблице Tovar для поля imya измените длину в символах до 50 (изначально задано 20).

 

Указания: удалите текст предыдущего запроса и введите команду:

 

alter table tovar

modify column imya varchar(50) not null;

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите таблицу Tovar и нажмите F5 для обновления. В результате структура таблицы обновится. 

 

C помощью команды Transact SQL добавьте в таблицу Tovar поле «Дата употребления» типа дата/время, не обязательное для заполнения. Откройте таблицу и заполните это поле для всех товаров.

 

Указания:  для добавления в таблицу Tovar поля «Дата употребления» удалите текст предыдущего запроса и введите команду:

 

alter table tovar

add column dataupotr datetime null; 

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите таблицу Tovar и нажмите F5 для обновления. В результате структура таблицы обновится. 

 

С помощью команд SQL для добавленного поля dataupotr задайте ограничение not null

 

Указания:  поле dataupotr мы добавили в предыдущей команде. Если вы не заполнили это поле для каждой записи, то рассматриваемая ниже команда не выполнится. Если мы хотим, чтобы поле dataupotr было обязательным для заполнения, нельзя, чтобы в нем были пустые значения. Поэтому еще раз убедитесь, что данное поле заполнено для каждой записи.

Удалите текст предыдущего запроса и введите команду:

 

alter table tovar

modify column dataupotr datetime not null;

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите таблицу Tovar и нажмите F5 для обновления. В результате структура таблицы обновится. 

 

С помощью команд SQL для поля imya таблицы Tovar добавьте ограничение unique

 

Указания:  для выполнения команды необходимо, чтобы в поле imya не было одинаковых значений. Иначе условие уникальности не выполняется и ограничение не будет создано.

Удалите текст предыдущего запроса и введите команду:

 

alter table tovar

modify column imya varchar(50) not null unique;

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите таблицу Tovar и нажмите F5 для обновления. В результате структура таблицы обновится.  Если открыть таблицу Tovar и попробовать ввести в поле imya одинаковые значения, то СУБД этого не даст.

 

С помощью команд удалите для поля imya ограничение unique

 

Указания:  удалите текст предыдущего запроса и введите команду:

 

alter table tovar

modify column imya varchar(50) not null;

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите таблицу Tovar и нажмите F5 для обновления. В результате структура таблицы обновится.  Теперь в поле imya можно вводить одинаковые значения.

 

С помощью команд SQL для поля proizv таблицы Tovar добавьте ограничение default 'отечественный'

 

Указания:  удалите текст предыдущего запроса и введите команду:

 

alter table tovar

modify column proizv varchar(20) not null default 'отечественный';

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите таблицу Tovar и нажмите F5 для обновления. В результате структура таблицы обновится.  Теперь, если для новой записи поле proizv не заполнять, то в нем будет появляться текст по умолчанию 'отечественный'.

 

С помощью команд SQL удалите для поля proizv ограничение default

 

Указания:  удалите текст предыдущего запроса и введите команду:

 

alter table tovar

modify column proizv varchar(20) not null;

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите таблицу Tovar и нажмите F5 для обновления. В результате структура таблицы обновится.  Теперь, для новой записи поле proizv нужно обязательно заполнять вручную (так как указано ограничение not null).

 

С помощью команды Transact SQL удалите из таблицы добавленное поле dataupotr. Откройте таблицы и убедитесь в правильности выполненных операций.

 

Указания: для удаления из таблицы Tovar поля dataupotr удалите текст предыдущего запроса и введите команду:

 

alter table tovar

drop column dataupotr;

 

Для выполнения запроса щелкните на команде "Выполнить". В левой части выделите таблицу Tovar и нажмите F5 для обновления. В результате структура таблицы обновится. 

 

С помощью команды Transact SQL выполните удаление обеих таблиц из базы данных Sklad.

 

Указания: для удаления таблицы Tovar в начале нужно удалить подчиненную таблицу Uchet. Введите команды:

 

##команды выполнять по очереди

drop table uchet;

 

drop table tovar;

 

Использование утилиты "SQL Manager for MySQL"

 

Повторите все выполненные ранее пункты с помощью утилиты.

 

Указания: для создания таблицы Tovar в дереве объектов раскройте папку БД sklad и в контекстном меню папки "Таблицы" выберите команду "Создать таблицу".

На закладке "Таблица" введите имя таблицы и в списке "" укажите тип таблицы InnoDB.

 

 

На закладке "Поля" введите структуру таблицы.

 

 

 

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

- Не Null - поле обязательное для заполнения;

- Беззнаковый - в поле можно ввести только положительные числа;

- Уникальный - для поля задается ограничение Unique;

- Автоинкремент - поле становится счетчиком;

- Первичный ключ - поле становится ключевым.

Внизу на закладке "По умолчанию" можно задать для поля ограничение default.

После ввода всех полей щелкните на команде "Compile". Затем перейдите на закладку Данные и введите данные в таблицу.

 

Аналогично создайте и заполните подчиненную таблицу Uchet. Обратите внимание, что при заполнении поля id_tovar значениями этого же поля из главной таблицы Tovar.

 

Для связывания таблиц откройте двойным щелчком подчиненную таблицу Uchet, перейдите на закладку "Внешние ключи" и щелкните на команде "Добавить внешний ключ".

 

 

В появившемся окне задайте параметры:

- в первом списке выберите поле подчиненной таблицы, по которой будет выполняться связь;

- в выпадающем списке "Имя внешней таблицы" укажите имя главной таблицы для связывания;

- во втором списке укажите поле главной таблицы, по которому связываются таблицы;

- внизу в списке "Правило для удаления" укажите (CASCADE).

После задания всех параметров щелкните на кнопке ОК, а затем на команде "Compile".

 

Для проверки работы каскадного удаления данных раскройте таблицу Tovar, перейдите на закладку "Данные" удалите любой товар. Откройте таблицу Uchet и на закладке "Данные" убедитесь, что автоматически удалились все подчиненные для этого товара записи.

 

Для добавления, изменения или удаления поля таблицы, добавления, удаления или изменения любого ограничения откройте таблицу двойным щелчком. На закладке "Поля" двойным щелчком откройте параметры нужного поля, внесите изменения и щелкните ОК, а затем на команде "Compile".

 

Для удаления таблицы вызовите ее контекстное меню в левой части окна и выберите команду "Удалить таблица . . .". Обратите внимание, что в начале нужно удалить подчиненную таблицу Uchet, а затем главную таблицу Tovar.

 

Для таблицы Tovar создайте индексы по полям "imya" и "proizv"  с сортировкой по возрастанию и индекс по полю "dataizgot" с  сортировкой по убыванию.

 

Для таблицы Uchet создайте индекс по полю "data" с сортировкой по возрастанию.

 

Удалите созданные индексы.

 

Для таблицы Tovar создайте уникальный индексы по полю "imya".

 

Удалите уникальный индекс.

 

Указания: для создания индексов для таблицы Tovar введите команды:

 

--по полю imya

create index iimya

on tovar (imya);

 

--по полю proizv

create index iproizv

on tovar (proizv);

 

--по полю dataizgot по убыванию

create index idataizgot

on tovar(dataizgot DESC);

 

Для создания индекса для таблицы Uchet введите команду:

 

--по полю data

create index idata

on uchet(data)

 

Для просмотра информации об имеющихся индексах двойным щелчком в левой части окна откройте нужную таблицу, в правой части перейдите на закладку "Индексы".  В этом окне можно создавать, изменять и удалять индексы.

 

Для удаления индексов введите команды:

 

drop index iimya on tovar

 

drop index iproizv on tovar

 

drop index idata on uchet

 

Для создания уникального индекса введите команду:

 

create unique index iimya

on tovar (imya)

 

Откройте таблицу Tovar для редактирования и попробуйте ввести в  поле imya два одинаковых значения (это у вас не получится).

 

Для удаления уникального индекса введите команду:

 

drop index iimya on tovar

 

Повторите действия по созданию и удалению индексов с помощью утилиты "SQL Manager for MySQL".

 

Указания: двойным щелчком откройте таблицу Tovar. На закладке "Индексы" можно просмотреть все индексы по таблице. Можно добавить, изменить или удалить индекс. При создании или изменении индекса открывается окно вида:

 

Вверху укажите имя индекса, в списке слева выберите и перенесите в список справа нужное поле, по которому создается индекс, в списке "Тип индекса" можно признак unique для индекса.

Для создания составного индекса перенесите в правый список несколько полей.

 

После добавления, изменения или удаления индекса для применения изменений щелкайте на команде "Compile".

 

Работа с SQLite

 

Повторите все выполненные ранее пункты в СУБД SQLite с помощью команд языка SQL.

 

Указания: в SQLite БД sklad по умолчанию нет, поэтому начнем работу с создания этой БД. Запустите утилиту SQLite Expert. С помощью команды меню File - New DataBase.

 

 

В новом окне в первом поле укажите путь и имя файла для новой БД и щелкните ОК.

В результате в левой части появится значок созданной БД .

 

Для ввода команд в правой части перейдите на закладку SQL.

 

Обратите внимание, что в SQLite для поля типа date нельзя по умолчанию присвоить функцию текущей даты, а также счетчик обозначается ключевым словом autoincrement.

 

Для создания главной таблицы Tovars в окне введите команду вида:

 

create table tovar

id_tovar integer primary key autoincrement,

kod varchar(5) not null unique,

imya varchar(20) not null,

proizv varchar(20) not null,

dataiz date,

edizm varchar(5) not null default 'шт',

cenaopt decimal(6,2) not null);

 

Для выполнения команды щелкните на кнопке Execute SQL. В результате в левой части окна у вашей БД появится новый узел с именем таблицы.

 

Для заполнения таблицы в левой части выберите нужную таблицу, а в правой части перейдите на закладку Data

 

Для создания подчиненной таблицы Uchet вернитесь на закладку SQL.

 

В окне удалите предыдущую команду и введите команду вида:

 

create table uchet

(id_uchet integer primary key autoincrement,

id_tovar integer not null ,

data date not null,

prihod integer default 0,

rashod integer default 0,

constraint id_tovar foreign key(id_tovar) references tovar(id_tovar) on delete cascade); --поле для связывания

 

Для выполнения команды щелкните на кнопке Execute SQL. В результате в левой части окна у вашей БД появится новый узел с именем таблицы.

 

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

 

Для проверки каскадного удаления откройте таблицу Tovar и удалите любую запись. Откройте таблицу Uchet и убедитесь, что автоматически удалились все подчиненные для этого товара записи.

 

В SQLite имеется только команда добавления поля в таблицу. Добавим поле dataupotr в таблицу tovar.

 

alter table tovar

add column dataupotr date null;

 

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

 

Для удаления созданных таблиц вернитесь на закладку SQL. Обратите внимание, что в начале нужно удалить подчиненную таблицу, а затем - главную.

 

В окне удалите предыдущую команду и введите команды вида:

 

drop table uchet;

drop table tovar;

 

Использование возможностей утилиты SQLite Expert

 

Повторите выполненные действия с помощью средств графической утилиты.

 

Указания: в контекстном меню вашей БД (отображается в левой части окна)  выберите команду "New Table". В правой части откроется окно настройки параметров таблицы.

Введите имя таблицы, а затем с помощью кнопки Add (расположена внизу окна) добавьте нужные поля, задавая имя поля, тип поля, размер, Not NULL (если нужно). Параметры каждого поля задаются в отдельном окне:

 

 

После задания всех параметров щелкните на кнопке Apply.

Для задания ключевого поля перейдите на закладку Indexes, щелкните на кнопке Add.

 

 

В новом окне выберите поле, которое вы хотите сделать ключевым и задайте для него флажок Primary Key и флажок Autoincrement (данный параметр доступен, только если поле имеет тип Integer). Закройте окно с помощью кнопки ОК и сохраните изменения с помощью кнопки Apply.

 

Для задания ограничения Unique перейдите на закладку Constaints. В верхнем списке отображаются поля с ограничением Unique. Щелкните на кнопке Add и флажком отметьте поля, которые должны быть уникальными.

 

 

Закройте окно с помощью кнопки ОК и сохраните изменения с помощью кнопки Apply.

 

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

 

Аналогично создайте подчиненную таблицу Uchet и заполните ее данными.

 

Для связывания таблиц выберите подчиненную таблицу и в правой части перейдите на закладку Foreign Keys. Щелкните на кнопке Add.

 

 

В новом окне в поле "Parent table" укажите главную таблицу для связывания, в поле "Child Key" укажите поле подчиненной таблицы для связывания, в поле "Parent Key" укажите поле главной таблицы для связывания, в поле "On Delete" укажите каскадное удаление данных. Закройте окно с помощью кнопки ОК и сохраните изменения с помощью кнопки Apply.

 

Для проверки каскадного удаления откройте таблицу Tovar и удалите любую запись. Откройте таблицу Uchet и убедитесь, что автоматически удалились все подчиненные для этого товара записи.

 

Для добавления нового поля в левой части окна выберите нужную таблицу и в правой части перейдите на закладку "Design". С помощью кнопки Add добавьте нужное поле, кнопки "Удалить" - удалить поле, кнопки "Modify" - изменить параметры поля. После внесения изменений щелкните на кнопке Apply.

 

Для удаления таблицы вызовите ее контекстное меню в левой части окна и выберите команду "Delete". Обратите внимание, что в начале нужно удалить подчиненную таблицу Uchet, а затем главную таблицу Tovar.

 

Работа с индексами

 

Указания: для работы с индексами можете использовать те же команды, что и для рассмотренных ранее СУБД. В правой части окна перейдите на закладку SQL и выполните поочередно команды:

 

--создадим индексы для таблицы Tovar 

 --по полю imya

create index iimya

on tovar (imya);

 

--по полю proizv

create index iproizv

on tovar (proizv);

 

--по полю dataizgot по убыванию

create index idataizgot

on tovar(dataizgot DESC);

 

--создадим индексы для таблицы Uchet

--по полю data

create index idata

on uchet(data)

 

Для просмотра информации об имеющихся индексах в левой части окна программы выберите таблицу, в правой части перейдите на закладку "Design", а затем на закладку "Indexes". Появится список созданных индексов.  Вы можете создавать, изменять и удалять индексы..

 

Для удаления индексов введите команды:

 

drop index iimya on tovar

 

drop index iproizv on tovar

 

drop index idata on uchet

 

Для создания уникального индекса введите команду:

 

create unique index iimya

on tovar (imya)

 

Откройте таблицу Tovar для редактирования и попробуйте ввести в  поле imya два одинаковых значения (это у вас не получится).

 

Для удаления уникального индекса введите команду:

 

drop index iimya on tovar

 

Повторите действия по созданию и удалению индексов с помощью утилиты "SQLite Expert".

 

Указания: для работы с индексами в левой части окна программы выберите таблицу, в правой части перейдите на закладку "Design", а затем на закладку "Indexes". Появится список созданных индексов.  С помощью кнопки Add добавьте новый индекс, кнопки "Delete" - удалить индекс, кнопки "Modify" - изменить индекс. Работа с индексами выполняется в отдельном окне.

 

 

После внесения изменений щелкните на кнопке Apply.