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

Для выполнения и разработки клиентского приложения к базе данных на Delphi(Делфи) работы необходимо рассмотреть примеры создания SQL-запросов на сервере баз данных MYSQL. Предметная область системы является учет поступления товаров на склад магазина.

Создадим БД «SKLAD», состоящую, из трех сущностей: Товар («Tovar»), Поставщик («Postav») и Приход («Prihod»). Физическое описание сущностей приведено в таблицах:

Описание атрибутов сущности «Товар» / «Tovar»

НазваниеКлючОписаниеТипДлинаПо умолчанию
idtovarPKномер товараint11NOT NULL AUTO_INCREMENT
name название товараvarchar50NOT NULL
edizm единица измеренияvarchar10NULL
zena цена за единицу измеренияint5NULL

Описание атрибутов сущностей «Поставщик» / «Postav»

НазваниеКлючОписаниеТипДлинаПо умолчанию
idpostavPKномер поставщикаint11NOT NULL  AUTO_INCREMENT
name наименование поставщикаvarchar50NOT NULL
gorod город поставщикаvarchar20NULL
ulica улица поставщикаvarchar20NULL
telef телефон поставщикаvarchar15NULL

Описание атрибутов сущности «Приход»/«Prihod»

НазваниеКлючОписаниеТипДлинаПо умолчанию
idprihodPKномер приходаint11NOT NULL AUTO_INCREMENT
idtovarFKномер товараint11NOT NULL
idpostavFKномер поставщикаint11NOT NULL
datprih дата приходаdate NOT NULL
kolvo количествоint4NOT NULL

Для сущностей «Товары» и «Поставщики» первичный ключами являются поля idtovar и idpostav, а для сущности «Приход товаров» — idprihod. Формирование ссылочной целостности реализовано на уровне клиентского приложения при формирование SQL-запросов.

Для реализации сущностей воспользуемся стандартной утилитой mysql, входящей в стандартный комплект сервера MySQL. При запуске утилиты необходимо указать следующие параметры: -h сетевое имя сервера; -u пользователь базы данных; -p пароль.

Сконфигурируем и запустим приложение OpenServer для работы с сервером баз данных MySQL-5.6, далее вызовем консоль. Вначале выберем рабочую версию сервера

Из консоли запустим утилиту со следующими параметрами командной строки: >mysql.exeh localhostu 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

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

  1. Какой оператор используется для получения информации о таблице?
  2. Как получить список всех баз данных, доступных в системе?
  3. Как узнать число строк в таблице с помощью оператора SELECT?
  4. Что делает следующий оператор? SELECT l_name, f_name from employee_data where title NOT LIKE ‘%продавец%’ AND age < 30;
  5. Назовите операторы для выполнения агрегирующих операций?
  6. Какой оператор используется для обновления данных таблицы?
  7. Покажите шаблоны добавления данных в таблицу?
  8. Какие операторы используются для редактирования структуры таблицы?

Задание

Создайте базу данных «Оптовая база» и повариантно SQL-запросы к базе данных. База данных состоит из следующих таблиц:

  • Товары (Goods)

содержит атрибуты: код_товар, категория, наименования_продукта;

  • Покупатель (Customer)

содержит: код_покупателя, фамилия, имя отчество, телефон, индекс, страна, город, область, адрес, предприятие, кредит;

  • Поставщик (Postav)

содержит: код_поставщика, название, адрес, телефон, скидки;

  • Продажи и заказы товара (Ord_Sale)

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

В случае отсутствия требуемых атрибутов запросов, их необходимо добавить в таблицы.

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