Как составить свою базу данных

Время на прочтение
3 мин

Количество просмотров 44K

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

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

С содержанием таблиц удобно ознакомиться в формате excel:

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

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

Агрегация, группировка, сортировка

Сумма заказов за весь год:

SELECT SUM(amount_of_orders) AS orders_per_year FROM year_statistics;

Убывающая сортировка заказов по месяцам:

SELECT month_name, amount_of_orders
FROM year_statistics
ORDER BY amount_of_orders DESC;

Вывести месяц, где больше всего заказов:

SELECT month_name, amount_of_orders FROM year_statistics
WHERE amount_of_orders = (SELECT MAX(amount_of_orders)
FROM year_statistics);

Популярность районов по количествам клиентов:

SELECT district
FROM customers
GROUP BY district
ORDER BY COUNT(district) DESC;

Сколько каждый курьер доставил заказов:

SELECT courier_id, COUNT(order_id)
From delivery_list
WHERE date_arrived IS NOT NULL
GROUP BY courier_id;

Общие запросы и использование операторов IN, EXISTS, UNION и др.

Выборка клиентов, которые живут в районе “South”:

SELECT * FROM Customers
WHERE district IN ('South');

Информация о заказах, которые не были доставлены клиентам:

SELECT * FROM delivery_list
WHERE taken NOT IN ('Yes');

Запрос продуктов из меню, которые были заказаны:

SELECT menu_name FROM products
WHERE EXISTS
(SELECT * FROM orders_products
WHERE orders_products.product_id = products.product_id);

Запрос тех продуктов, которые не заказывали:

SELECT menu_name FROM products
WHERE NOT EXISTS
(SELECT * FROM orders_products
WHERE orders_products.product_id = products.product_id);

Получаем общую таблицу с информацией о клиентах и курьеров:

SELECT 'Customer' AS category, first_name, last_name, phone_number
FROM customers
UNION
SELECT 'Employee' AS category, first_name, last_name, phone_number
FROM courier_info;

INNER, NATURAL, CROSS, LEFT JOIN

Наиболее интересный запрос, который позволяет видеть детали заказа(номер, название блюда, количество и цена). К тому же здесь использован метод ROUND, позволяющий округлять дробные числа:

SELECT orders_products.order_id, products.menu_name, quantity,
ROUND(price*quantity, 2) AS total_price
FROM orders_products
INNER JOIN products ON orders_products.product_id = products.product_id
ORDER BY order_id, quantity;

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

SELECT *, SEC_TO_TIME(TIMESTAMPDIFF(second, date_get, date_arrived))
AS time_of_delivery
FROM orders
NATURAL JOIN delivery_list;

Не совсем тривиальный запрос на выборку о том, что каждому курьеру на машине доступен любой район из таблицы клиентов:

SELECT DISTINCT courier_info.courier_id, customers.district
FROM courier_info
CROSS JOIN customers WHERE courier_info.delivery_type = 'car'
ORDER BY courier_id;

И напоследок запрос на информацию об имени клиента, его мобильном телефоне и номере заказа:

SELECT customers.first_name, customers.last_name,
customers.phone_number, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Заключение

По итогу мы с вами разобрали множество полезных запросов на выборку SQL. Были показаны основные и более редкие операции. В сущности не важно, сколько данных в Вашей таблице – десять или тысяча, от этого запросы не поменяются, а всегда будут оставаться такими же. Главное, чтобы был понятен смысл, количество данных играет намного меньшую роль. Бояться и расстраиваться от того, что у Вас пока не получается получить желаемый запрос, совершенно глупо. Абсолютно нормально, если Вы гуглите, читаете книгу на интересующую тему, а результата так и нет. На это может уходить от десятка минут до целых дней. Мы все люди и одному человеку не под силу знать все. Наберитесь терпения, спросите у товарищей, на форумах и просто продолжайте искать сами, у Вас все получится! Удачи.

В современном мире нужны инструменты, которые бы позволяли хранить, систематизировать и обрабатывать большие объемы информации, с которыми сложно работать в Excel или Word.

Подобные хранилища используются для разработки информационных сайтов, интернет-магазинов и бухгалтерских дополнений. Основными средствами, реализующими данный подход, являются MS SQL и MySQL.

Продукт от Microsoft Office представляет собой упрощенную версию в функциональном плане и более понятную для неопытных пользователей. Давайте рассмотрим пошагово создание базы данных в Access 2007.

Microsoft Access 2007 – это система управления базами данных (СУБД), реализующая полноценный графический интерфейс пользователя, принцип создания сущностей и связей между ними, а также структурный язык запросов SQL. Единственный минус этой СУБД – невозможность работать в промышленных масштабах. Она не предназначена для хранения огромных объемов данных. Поэтому MS Access 2007 используется для небольших проектов и в личных некоммерческих целях.

Но прежде чем показывать пошагово создание БД, нужно ознакомиться с базовыми понятиями из теории баз данных.

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

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

1. Предметная область – множество созданных таблиц в базе данных, которые связаны между собой с помощью первичных и вторичных ключей.
2. Сущность – отдельная таблица базы данных.
3. Атрибут – заголовок отдельного столбца в таблице.
4. Кортеж – это строка, принимающая значение всех атрибутов.
5. Первичный ключ – это уникальное значение (id), которое присваивается каждому кортежу.
6. Вторичный ключ таблицы «Б» – это уникальное значение таблицы «А», использующееся в таблице «Б».
7. SQL запрос – это специальное выражение, выполняющее определенное действие с базой данных: добавление, редактирование, удаление полей, создание выборок.

Теперь, когда в общих чертах есть представление о том, с чем мы будем работать, можно приступить к созданию БД.

Создание БД

Для наглядности всей теории создадим тренировочную базу данных «Студенты-Экзамены», которая будет содержать 2 таблицы: «Студенты» и «Экзамены». Главным ключом будет поле «Номер зачетки», т.к. данный параметр является уникальным для каждого студента. Остальные поля предназначены для более полной информации об учащихся.

Итак, выполните следующее:

1. Запустите MS Access 2007.
2. Нажмите на кнопку «Новая база данных».
3. В появившемся окне введите название БД и выберите «Создать».
   

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

Создание и заполнение таблиц

После успешного создания БД на экране появится пустая таблица. Для формирования ее структуры и заполнения выполните следующее:

1. Нажмите ПКМ по вкладке «Таблица1» и выберите «Конструктор».
   
2. Теперь начинайте заполнять названия полей и соответствующий им тип данных, который будет использоваться.
   

   Внимание! Первым полем принято устанавливать уникальное значение (первичный ключ). Для него предпочтительно числовое значение.

1. После создания необходимых атрибутов сохраните таблицу и введите ее название.
2. Снова нажмите ПКМ по вкладке с уже новым название и выберите «Режим таблицы».
   
3. Заполните таблицу необходимыми значениями.
   

Совет! Для тонкой настройки формата данных перейдите на ленте во вкладку «Режим таблицы» и обратите внимание на блок «Форматирование и тип данных». Там можно кастомизировать формат отображаемых данных.

Создание и редактирование схем данных

Перед тем, как приступить к связыванию двух сущностей, по аналогии с предыдущим пунктом нужно создать и заполнить таблицу «Экзамены». Она имеет следующие атрибуты: «Номер зачетки», «Экзамен1», «Экзамен2», «Экзамен3».

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

1. Перейти во вкладку «Работа с базами данных».
2. Нажать на кнопку «Схема данных».
3. Если схема не была создана автоматически, нужно нажать ПКМ на пустой области и выбрать «Добавить таблицы».
   
4. Выберите каждую из сущностей, поочередно нажимая кнопку «Добавить».
5. Нажмите кнопку «ОК».

Конструктор должен автоматически создать связь, в зависимости от контекста. Если же этого не случилось, то:

1. Перетащите общее поле из одной таблицы в другую.
2. В появившемся окне выберите необходимы параметры и нажмите «ОК».
   
3. Теперь в окне должны отобразиться миниатюры двух таблиц со связью (один к одному).
   

Выполнение запросов

Что же делать, если нам нужны студенты, которые учатся только в Москве? Да, в нашей БД только 6 человек, но что, если их будет 6000? Без дополнительных инструментов узнать это будет сложно.

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

Виды запросов

SQL синтаксис реализует принцип CRUD (сокр. от англ. create, read, update, delete — «создать, прочесть, обновить, удалить»). Т.е. с помощью запросов вы сможете реализовать все эти функции.

На выборку

В этом случае в ход вступает принцип «прочесть». Например, нам нужно найти всех студентов, которые учатся в Харькове. Для этого нужно:

1. Перейти во вкладку «Создание».
2. Нажать кнопку «Конструктор запросов» в блоке «Другие».
3. В новом окне нажмите на кнопку SQL.
   
4. В текстовое поле введите команду: SELECT * FROM Студенты WHERE Адрес = “Харьков”; где «SELECT *» означает, что выбираются все студенты, «FROM Студенты» – из какой таблицы, «WHERE Адрес = “Харьков”» – условие, которое обязательно должно выполняться.
5. Нажмите кнопку «Выполнить».
   
6. На выходе мы получаем результирующую таблицу.
   

А что делать, если нас интересуют студенты из Харькова, стипендии у которых больше 1000? Тогда наш запрос будет выглядеть следующим образом:

SELECT * FROM Студенты WHERE Адрес = “Харьков” AND Стипендия > 1000;

а результирующая таблица примет следующий вид:

На создание сущности

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

1. Перейти во вкладку «Создание».
2. Нажать кнопку «Конструктор запросов» в блоке «Другие».
3. В новом окне нажмите на кнопку SQL, после чего в текстовое поле введите команду:

CREATE TABLE Преподаватели
(КодПреподавателя INT PRIMARY KEY,
Фамилия CHAR(20),
Имя CHAR (15),
Отчество CHAR (15),
Пол CHAR (1),
Дата_рождения DATE,
Основной_предмет CHAR (200));

где «CREATE TABLE» означает создание таблицы «Преподаватели», а «CHAR», «DATE» и «INT» – типы данных для соответствующих значений.

1. Кликните по кнопке «Выполнить».
2. Откройте созданную таблицу.
   

Внимание! В конце каждого запроса должен стоять символ «;». Без него выполнение скрипта приведет к ошибке.

На добавление, удаление, редактирование

Здесь все гораздо проще. Снова перейдите в поле для создания запроса и введите следующие команды:

Создание формы

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

1. Откройте интересующую таблицу.
2. Перейдите во вкладку «Создание».
3. Нажмите на необходимый формат формы из блока «Формы».
   

   Совет! Рекомендуется использовать «Разделенную форму» – кроме самого шаблона, в нижней части будет отображаться миниатюра таблицы, которая сделает процесс редактирования еще более наглядным.

4. С помощью навигационных кнопок переходите к следующей записи и вносите изменения.
   

Все базовые функции MS Access 2007 мы уже рассмотрели. Остался последний важный компонент – формирование отчета.

Формирование отчета

Отчет – это специальная функция MS Access, позволяющая оформить и подготовить для печати данные из базы данных. В основном это используется для создания товарных накладных, бухгалтерских отчетов и прочей офисной документации.

Если вы никогда не сталкивались с подобной функцией, рекомендуется воспользоваться встроенным «Мастером отчетов». Для этого сделайте следующее:

1. Перейдите во вкладку «Создание».
2. Нажмите на кнопку «Мастер отчетов» в блоке «Отчеты».
   
3. Выберите интересующую таблицу и поля, нужные для печати.
   
4. Добавьте необходимый уровень группировки.
   
5. Выберите тип сортировки каждого из полей.
   
6. Настройте вид макета для отчета.
   
7. Выберите подходящий стиль оформления.
   

   Внимание! В официальных документах допускается только стандартный стиль оформления.

8. Просмотрите созданный отчет.
   

Если отображение вас не устраивает, его можно немного подкорректировать. Для этого:

1. Нажмите ПКМ на вкладке отчета и выберите «Конструктор».
2. Вручную расширьте интересующие столбцы.
   
3. Сохраните изменения.

Вывод

Итак, с уверенностью можно заявить, что создание базы данных в MS Access 2007 мы разобрали полностью. Теперь вам известны все основные функции СУБД: от создания и заполнения таблиц до написания запросов на выборку и создания отчетов. Этих знаний хватит для выполнения несложных лабораторных работ в рамках университетской программы или использования в небольших личных проектах.

Для проектирования более сложных БД необходимо разбираться в объектно-ориентированном программировании и изучать такие СУБД, как MS SQL и MySQL. А для тех, кому нужна практика составления запросов, рекомендую посетить сайт SQL-EX, где вы найдете множество практических занимательных задачек.

Удачи в освоении нового материала и если есть какие-либо вопросы – милости прошу в комментарии!