Для выполнения и разработки клиентского приложения к базе данных на Delphi(Делфи) работы необходимо рассмотреть примеры создания SQL-запросов на сервере баз данных MYSQL. Предметная область системы является учет поступления товаров на склад магазина.
Создадим БД «SKLAD», состоящую, из трех сущностей: Товар («Tovar»), Поставщик («Postav») и Приход («Prihod»). Физическое описание сущностей приведено в таблицах:
Описание атрибутов сущности «Товар» / «Tovar»
| Название | Ключ | Описание | Тип | Длина | По умолчанию |
|---|---|---|---|---|---|
| idtovar | PK | номер товара | int | 11 | NOT NULL AUTO_INCREMENT |
| name | название товара | varchar | 50 | NOT NULL | |
| edizm | единица измерения | varchar | 10 | NULL | |
| zena | цена за единицу измерения | int | 5 | NULL |
Описание атрибутов сущностей «Поставщик» / «Postav»
| Название | Ключ | Описание | Тип | Длина | По умолчанию |
|---|---|---|---|---|---|
| idpostav | PK | номер поставщика | int | 11 | NOT NULL AUTO_INCREMENT |
| name | наименование поставщика | varchar | 50 | NOT NULL | |
| gorod | город поставщика | varchar | 20 | NULL | |
| ulica | улица поставщика | varchar | 20 | NULL | |
| telef | телефон поставщика | varchar | 15 | NULL |
Описание атрибутов сущности «Приход»/«Prihod»
| Название | Ключ | Описание | Тип | Длина | По умолчанию |
|---|---|---|---|---|---|
| idprihod | PK | номер прихода | int | 11 | NOT NULL AUTO_INCREMENT |
| idtovar | FK | номер товара | int | 11 | NOT NULL |
| idpostav | FK | номер поставщика | int | 11 | NOT NULL |
| datprih | дата прихода | date | NOT NULL | ||
| kolvo | количество | int | 4 | NOT NULL |
Для сущностей «Товары» и «Поставщики» первичный ключами являются поля idtovar и idpostav, а для сущности «Приход товаров» — idprihod. Формирование ссылочной целостности реализовано на уровне клиентского приложения при формирование SQL-запросов.
Для реализации сущностей воспользуемся стандартной утилитой mysql, входящей в стандартный комплект сервера MySQL. При запуске утилиты необходимо указать следующие параметры: -h сетевое имя сервера; -u пользователь базы данных; -p пароль.
Сконфигурируем и запустим приложение OpenServer для работы с сервером баз данных MySQL-5.6, далее вызовем консоль. Вначале выберем рабочую версию сервера
Из консоли запустим утилиту со следующими параметрами командной строки: >mysql.exe —h localhost —u root
Введем пароль доступа к серверу баз данных.
В результате в окне приложения mysql.exe появиться сообщение:
Метка «mysql>» обозначает, что программа mysql готова к вводу команд.
Для получения сведений о существующих базах данных на сервере необходимо выполнить команду show databases:
mysql> show databases;
+————+
| Database |
+————+
| mysql |
| test |
+————+
2 rows in set (0.00 sec)
Для реализации собственной базы данных необходимо выполнить запрос на создание базы данных:
mysql > create database sklad;
Обратимся к БД с помощью команды USE:
mysql> use sklad
, в результате появится сообщение
Database changed
Команда USE db_name предписывает MySQL использовать базу данных с именем db_name в последующих запросах по умолчанию. Указанная база данных остается в этом состоянии до конца данного сеанса или пока не будет выдана еще одна команда USE:
mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
mysql> USE db2;
mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable
То обстоятельство, что отдельная база данных посредством команды USE выбирается как используемая в текущий момент по умолчанию, не является препятствием для доступа к таблицам других баз данных. Следующий пример иллюстрирует получение доступа к таблице author базы данных db1 и к таблице editor базы данных db2:
mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
WHERE author.editor_id = db2.editor.editor_id;
Для получения сведений о существующих таблицах баз данных sklad необходимо выполнить команду show tables:
mysql> show tables;
+—————-+
| Tables_in_sklad |
+—————-+
0 row in set (0.00 sec)
Для создания таблиц базы данных, например tovar, postav, prihod воспользуемся оператором SQL-языка create table:
mysql> create table tovar (
idtovar int(11) not null auto_increment,
name varchar(50) default null,
edizm varchar(10) default null,
zena int(4) default null,
primary key (idtovar)
)
collate=’utf8_general_ci’
engine=innodb;
mysql> create table postav (
idpostav int(11) not null auto_increment,
name varchar(50) default null,
gorod varchar(20) default null,
ulica varchar(20) default null,
telef varchar(15) default null,
primary key (idpostav)
)
collate=’utf8_general_ci’
engine=innodb;
mysql> create table prihod (
idprihod int(11) not null auto_increment,
idtovar int(11) default null,
datprih date default null,
kolvo int(4) default null,
idpostav int(11) default null,
primary key (idprihod),
constraint `fk_prihod_postav` foreign key (`idpostav`) references `postav` (`idpostav`),
constraint `fk_prihod_tovar` foreign key (`idtovar`) references `tovar` (`idtovar`)
)
collate=’utf8_general_ci’
engine=innodb;
Для добавления одиночных записей используется оператор INSERT необходимо задать шаблон, состоящий из названий полей заполняемой таблицы.
mysql> insert into tovar (name,edizm,zena) values (‘утюг evgo’,’шт‘, 45);
Информация извлекается из таблиц при помощи оператора SELECT. В классическом варианте вызова SELECT из таблицы извлекаются сразу все данные:
mysql> select * from tovar;
Ниже в табл. 1.4 приведены тестовые SQL-запросы, которые будут использованы в клиентском приложении.
Таблица 1.4
Примеры реализации SQL-запросов в СУБД MySQL
| № | Формулировка запроса | SQL-запрос |
| 1 | найти товары дороже 10 рублей. | select name as ‘наименование ‘ from tovar where zena>10 |
| 2 | найти поставщиков в москве. | select name as ‘поставщик ‘,ulica as ‘улица‘, telef as ‘телефон‘ from postav where gorod= ‘москва‘ |
| 3 | найти товары, поступившие на склад 01.04.2003. | select tovar.name as ‘наименование’, kolvo as ‘количество’, postav.name as ‘поставщик’, telef as ‘телефон’ from postav, prihod, tovar where tovar.idtovar=prihod.idtovar and prihod.idpostav=postav.idpostav and datprih= ‘2003-04-01’ |
| 4 | найти адреса поставщиков производящие товар «молоко сгущенное”. | select distinct postav.name as ‘поставщик’, ulica as ‘улица’, telef as ‘телефон’ from postav, tovar, prihod where tovar.idtovar=prihod.idtovar and prihod.idpostav=postav.idpostav and tovar.name= ‘молоко сгущенное’ |
| 5 | найти сумму стоимости товаров поступивших на склад. | select name as ‘наименование’, sum(kolvo*zena) as ‘сумма‘ from tovar, prihod where tovar.idtovar=prihod.idtovar group by name |
Набрав в командной строке утилиты mysql команду describe сервер предоставляет информацию о столбцах таблицы.
Набрав в командной строке утилиты mysql команду quit происходит разрыв соединения с сервером:
mysql> quit
, в результате появится сообщениевыхода
Bye
Контрольные вопросы
- Какой оператор используется для получения информации о таблице?
- Как получить список всех баз данных, доступных в системе?
- Как узнать число строк в таблице с помощью оператора SELECT?
- Что делает следующий оператор? SELECT l_name, f_name from employee_data where title NOT LIKE ‘%продавец%’ AND age < 30;
- Назовите операторы для выполнения агрегирующих операций?
- Какой оператор используется для обновления данных таблицы?
- Покажите шаблоны добавления данных в таблицу?
- Какие операторы используются для редактирования структуры таблицы?
Задание
Создайте базу данных «Оптовая база» и повариантно SQL-запросы к базе данных. База данных состоит из следующих таблиц:
- Товары (Goods)
содержит атрибуты: код_товар, категория, наименования_продукта;
- Покупатель (Customer)
содержит: код_покупателя, фамилия, имя отчество, телефон, индекс, страна, город, область, адрес, предприятие, кредит;
- Поставщик (Postav)
содержит: код_поставщика, название, адрес, телефон, скидки;
- Продажи и заказы товара (Ord_Sale)
должна содержать следующие атрибуты: код_заказа, номер_заказа, код_покупателя, код_товар, код_постащика, дата_заказа, заказано, дата_продажи, продано, цена, примечание.
В случае отсутствия требуемых атрибутов запросов, их необходимо добавить в таблицы.