Reg.ru: домены и хостинг
Крупнейший регистратор и хостинг-провайдер в России.
Более 2 миллионов доменных имен на обслуживании.
Продвижение, почта для домена, решения для бизнеса.
Более 700 тыс. клиентов по всему миру уже сделали свой выбор.
Бесплатный Курс "Практика HTML5 и CSS3"
Освойте бесплатно пошаговый видеокурс
по основам адаптивной верстки
на HTML5 и CSS3 с полного нуля.
Фреймворк Bootstrap: быстрая адаптивная вёрстка
Пошаговый видеокурс по основам адаптивной верстки в фреймворке Bootstrap.
Научитесь верстать просто, быстро и качественно, используя мощный и практичный инструмент.
Верстайте на заказ и получайте деньги.
Бесплатный курс "Сайт на WordPress"
Хотите освоить CMS WordPress?
Получите уроки по дизайну и верстке сайта на WordPress.
Научитесь работать с темами и нарезать макет.
Бесплатный видеокурс по рисованию дизайна сайта, его верстке и установке на CMS WordPress!
*Наведите курсор мыши для приостановки прокрутки.
БД MySQL (типы данных, таблицы, запросы, триггеры, индексы)
Введение
Довольно часто, создавая или заказывая какой-либо ИТ-проект, мы слышим о базах данных.
Что же такое база данных? Для чего она нужна? Как ей пользоваться? На эти и другие вопросы я постараюсь ответить в данном курсе статей о базах данных.
Все аспекты я постараюсь описать примерами. Итак, начнем.
БД - совокупность данных, систематизированных таким образом, чтобы получить их было просто и удобно.
СУБД — система управления базами данных, совокупность функций и процедур для работы с БД.
Существует великое множество СУБД. Для web-разработки, как правило, используется СУБД MySQL.
Почему именно она? Это простая, бесплатная и довольно мощная система.
Для примера можно взять Facebook. Данные хранятся там именно в MySQL. MySQL оптимизирована для получения данных из БД. Практически на всех сайтах мы в основном читаем данные из БД, прежде чем отправить их на страницу.
Исходя из всех преимуществ СУБД MySQL, все примеры будут указаны в кодах этой СУБД.
Установка СУБД
Для начала установим MySQL. Проще всего это сделать установив т.н. Джентльменский набор веб-разработчика. Дистрибутив и порядок установки вы можете найти на сайте http://www.denwer.ru/.
Также вы можете изучить следующий материал, посвященный основам работы с Денвером.
Для работы с БД из всего набора Денвера нам нужна только утилита phpMyAdmin. Запускаем ее в браузере по адресу http://localhost/Tools/phpMyAdmin/index.php
Для начала создадим БД, с которой будем работать. Для этого на вкладке Базы данных (выбрана на рисунке) укажем реквизиты новой БД. Назовем ее test.
Типы данных
В MySQL, как и в других средах разработки, различают несколько типов данных. Изучая СУБД, в первую очередь необходимо изучить все типы данных. В общем, они идентичны во всех СУБД, но у каждой есть свои особенности.
1) INT - Целочисленный формат. В основном используется для идентификаторов и переключателей (0 - нет, 1 - есть).
2) CHAR – Текстовый формат. Ограничен 32 тыс. символов. Используется для хранения небольшого объема текстовой информации. Пример CHAR(128).
3) TEXT - Текстовый формат. Практически неограничен. Используется для хранения большого объема текстовой информации.
4) DATE – Дата.
5) DATETIME – Дата и время.
6) DECIMAL – Числовой формат с разделителем. Пример DECIMAL(14, 2) позволит нам записать число, длинной 14 символов с двумя знаками после запятой.
На самом деле, все типы данных я описывать не стал, этого набора вполне достаточно для реализации практически любого приложения.
Таблицы
Основная сущность БД это таблица. Остальные, как правило, вспомогательные.
Таблица - некоторая группа данных, объединенная по каким-либо общим свойствам. Например, список товаров на складе или список пользователей системы. Такие таблицы при проектировании приложения зовутся справочниками.
Роль таблицы в БД - хранение структурированного набора данных и только. В MySQL таблица представляет собой стандартную таблицу с колонками и строками.
Строки указывают на запись, колонки указывают на реквизиты записи.
Рассмотрим пример. Создадим таблицу "Users" (справочник пользователей) с помощью следующего кода:
CREATE TABLE Users ( user_id INT, /*Идентификатор пользователя*/ user_name CHAR(64) /*ФИО сотрудника*/ );
Старайтесь работать с БД именно с помощью скриптов. Это упростит понимание команд и функций. Кроме того, если у вас нет доступа к среде разработки (phpMyAdmin), это вам очень поможет. Работая со скриптами, используем вкладку SQL (см. рис. ниже)
Рассмотрим код:
CREATE TABLE Users [Создать таблицу] с именем Users ( Описание полей таблицы user_id[наименование поля] as INT [тип данных целочисленный], user_name[наименование поля] as CHAR(64)[текстовое поле длинной 64 символа], );
ВНИМАНИЕ! При создании таблицы старайтесь называть ее в соответствии с данными, которые будут в ней храниться. Также в каждой таблице желательно создавать поле идентификатора записи (ID). Это очень пригодится.
Для удаления таблицы используется код:
DROP TABLE Users;
Для множественного удаления
DROP TABLES Users, GroupUsers;
Будьте внимательны при вызове указанных команд.
Для изменения таблицы используется код:
ALTER TABLE Users
ADD [MODIFY]
COLUMN - Колонки
INDEX - Индекса
;
Пока на этом остановимся.
Запросы
БД, как таковая, должна иметь механизмы ввода-вывода информации. Для вставки, изменения, получения или удаления необходимо использовать запросы. В данном курсе я опишу только запросы SQL (Structured Query Language — «Структурированный язык запросов»), т.к. большинство БД работают именно с запросами такого типа.
Существует 4 вида запросов:
Для примера, поиграем с нашим справочником "Users":
1) Select - Получение данных из таблицы
SELECT * FROM Users;
Давайте разберем запрос. SELECT[Выбрать] *[Все столбцы] FROM[Из таблицы] Users. Данный запрос вернет все записи по всем столбцам таблицы.
Чтобы ограничить выборку (полные выборки требуются только в случае получения отчетов, и то не всегда), добавим в наш запрос условие WHERE.
SELECT * FROM Users WHERE user_id = 1;
Здесь мы получаем данные по пользователю №1. Вроде бы все просто, не правда ли? Идем далее..
2) Update - Обновление данных в таблице
UPDATE Users SET user_name = 'Иванов И.И.' WHERE user_id = 1;
Давайте разберем запрос. UPDATE[Обновить] Users SET[Установить [поле] user_name = 'Иванов И.И.' WHERE[Где] user_id = 1. В данном случае мы изменили ФИО пользователя №1 на "Иванов И.И.".
3) Insert - Вставка новых данных в таблицу
По хорошему, данный запрос нужно писать в первую очередь, т.к. справочник у нас изначально пуст, но обычно разработчики создают таблицы через интерфейс (phpMyAdmin) и сразу добавляют несколько тестовых записей.
INSERT INTO Users (user_id, user_name) VALUES (2, 'Петров П.П.');
Разбираем запрос. INSERT INTO[Вставить в] Users ([перечнь полей]user_id, user_name) VALUES ([Перечнь значений]2, 'Петров П.П.'). Запрос вставит запись в таблицу со значениями user_id равному 2 и user_name равному 'Петров П.П.'.
Если вы заполняете все столбцы таблицы, можно немного упростить запрос и не указывать список полей для вставки.
INSERT INTO Users VALUES (2, 'Петров П.П.');
Однако при этом важно соблюдать порядок столбцов.
При вставке нескольких строк можно воспользоваться запросом множественной вставки.
INSERT INTO Users VALUES (2, 'Петров П.П.'), (3, 'Сидоров С.С.');
4) Delete - Удаление данных из таблицы
DELETE * FROM Users WHERE user_id = 1;
Тут все просто. DELETE[Удалить] *[Все] FROM[Из] Users.
Запросу неважно, что будет указано после команды DELETE, будь это перечень полей или одно поле, он удалит всю строку данных.
ВНИМАНИЕ! В данном запросе не забывайте писать условие WHERE (Это касается и запроса UPDATE), иначе запрос очистит всю вашу таблицу.
При отладке приложения старайтесь при написании команды DELETE изначально описывать запрос в виде SELECT. Нужно нам, например, написать запрос на удаление одного пользователя, мы пишем:
SELECT * FROM Users WHERE user_id = 1;
Проверяем. Запрос выводит одну строку, которую нам надо удалить. Заменяем теперь SELECT на DELETE и спокойно выполняем запрос, не опасаясь за данные.
DELETE * FROM Users WHERE user_id = 1;
В данной статье я указал лишь простые запросы, которых будет вполне достаточно для создания легкого приложения. В последующих опишу уже более сложные запросы.
Пример вставки строк в таблицу. Среда оповещает нас о вставке строки.
Триггеры
Триггер - процедура, выполняемая при перед/после выполнения запросов вставки, обновления или удаления.
Зачем нам нужен триггер? Триггер позволяет нам сократить код приложения при написании проверок или дополнительных действий. Также триггер помогает проверять связи при редактировании записей, но об этом позже.
Рассмотрим на примере.
Допустим мы желаем хранить историю изменений в нашем приложении. Иногда это очень полезно. Создадим таблицу истории. Как правильно описать поля, мы выяснили ранее.
CREATE TABLE History ( history_id INT /*Идентификатор записи*/ history_type INT /*Тип изменения*/ history_date DATETIME /*Дата изменения */ history_text TEXT /*Текст изменения*/ history_user CHAR(64) /*Пользователь*/ );
Определим типы изменений:
1 - добавление записи
2 - изменение записи
Теперь создадим триггер на нашей таблице Users:
CREATE TRIGGER users_trg /*Создать новый триггер users_trg*/
AFTER INSERT ON Users /*Отрабатывать после вставки таблицы*/
FOR EACH /*Для каждой новой записи*/
ROW INSERT INTO history /*Добавить запись в history*/
VALUES ('', 1, now(), 'Пользователь создан', user());
Теперь при добавлении нового пользователя в таблицу истории триггер будет добавлять запись о его создании. По сути, триггеры очень полезны. Проверка может осуществляться на уровне БД, не касаясь приложения.
В некоторых СУБД триггеры используются для заполнения уникального идентификатора таблиц, но в MySQL это не требуется.
Индексы
Индексы - это наборы данных в БД, используемые для оптимизации поиска записей.
В таблицах данные хранятся в случайном порядке и, иногда, запись перебором (если индекса нет) найти очень затратно по времени. Индекс содержит в себе указатели на группы данных в таблице, объединенные по какому-либо критерию.
Рассмотрим на примере нашего справочника. Допустим, мы хотим привязать сотрудников по отделам. Модифицируем наш справочник пользователей, добавив в него ссылку на группу.
ALTER TABLE Users ADD COLUMN group_id_ref INT;
Теперь у нас справочник пользователей имеет привязку к группе. Заполним пустые поля записями 1 и 2. При выполнении запроса вида:
SELECT * FROM Users WHERE group_id_ref = 1;
будут проверяться все записи в таблице на предмет равенства поля group_id_ref единице.
Для ускорения поиска необходимо добавить индекс.
ALTER TABLE Users ADD INDEX group_id_idx (group_id_ref);
ВИМАНИЕ! Эта процедура разовая и проводится на этапе разработки. Времени много не занимает, поэтому советую не опускать этот пункт. Кроме того, это основа быстродействия вашего приложения.
Теперь разберем, чем нам поможет данных индекс. Возьмем тот же запрос:
SELECT * FROM Users WHERE group_id_ref = 1;
Алгоритм поиска по таблице будет идти только по группе записей, group_id_ref которых равен 1.
В нашем случае, когда у пользователей только две группы, время поиска сократится незначительно, но в ситуации, когда групп пользователей много, индекс ускоряет поиск в разы и десятки раз.
Теперь, чтобы как-то определить группы, создадим справочник.
CREATE TABLE GroupUsers ( group_id INT /*Идентификатор группы*/ group_name CHAR(64) /*Наименование группы*/ );
Также нам может пригодиться первичный индекс. Для чего он нужен? Для того, чтобы при добавлении записей в справочники каждая из них имела свой идентификатор.
Это очень удобно. Оперируя данными таблицы, нам достаточно указать идентификатор конкретной строки для ее определения. Как пример, для сравнения представим ситуацию, что в нашей таблице Users есть три записи о пользователях.
Получается, что у нас есть два пользователя с одинаковым именем, но в разных отделах. Такое встречается очень часто, особенно, если ИО заполняется инициалами.
При отсутствии уникального идентификатора запрос на изменение второй записи выглядел бы так:
UPDATE Users SET user_name = 'Иванов Иван Иванович' WHERE user_name = 'Иванов И.И' AND group_id_ref = 2;
При наличии уникального идентификатора user_id запрос выглядит немного проще:
UPDATE Users SET user_name = 'Иванов Иван Иванович' WHERE user_id = 2;
При всем при этом поиск в таблице по тексту — не самое лучшее решение. СУБД позволяет это делать, но вы сильно теряете в скорости. Недостатком такого поиска также является передача в запрос текстовых параметров. Их необходимо экранировать.
В MySQL есть очень простой способ создания уникального индекса.
ALTER TABLE Users ADD user_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (user_id);
Рассмотрим подробнее код:
ALTER TABLE[Изменить таблицу] Users
MODIFY COLUMN[изменить колонку]
user_id AS INT[тип данных целочисленный]
auto_increment[автоматическое заполнение столбца значениями по возрастанию (1,2,3 и т.д.)];
ВНИМАНИЕ! ALTER это изменение структуры БД. Необходимо минимизировать или вообще стараться избегать этой команды в скриптах. Все индексы и счетчики должны быть учтены при проектировке БД.
Вот так выглядит скрипт создания таблицы Users без использования ALTER:
CREATE TABLE Users ( user_id INT auto_increment, /*Идентификатор */ user_name CHAR(64), /*ФИО сотрудника*/ group_id_ref INT, /*Группа*/ PRIMARY KEY (user_id), /*Первичный ключ*/ KEY (group_id_ref) /*Индекс по группе*/ );
Итог
В данной статье мы познакомились с Базами данных, узнали о таблицах, запросах, триггерах и индексах. Пройдемся еще раз по основным объектам.
1) Таблица — объект БД для хранения данных.
2) Запрос — средство ввода-вывода информации.
3) Триггер — процедура, выполняемая при изменении данных в таблице.
4) Индекс — указатель на записи в таблице.
По сути данного набора знаний будет вполне достаточно, чтобы разработать сайт-визитку.
Материал подготовил Владимир Миняйлов специально для сайта CodeHarmony.ru
Исходники:
CREATE TABLE Users (
user_id INT, /*Идентификатор пользователя*/
user_name CHAR /*ФИО сотрудника*/
);
ALTER TABLE Users ADD COLUMN group_id_ref INT;
ALTER TABLE Users ADD INDEX group_id_idx (group_id_ref);
ALTER TABLE Users DROP COLUMN user_id;
ALTER TABLE Users ADD user_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (user_id);
/*Триггер на вставку*/
CREATE TRIGGER users_trg
AFTER INSERT ON Users /*Отрабатывать после вставки*/
FOR EACH
ROW INSERT INTO history
VALUES ('', 1, now(), 'Пользователь создан', user());
CREATE TABLE History (
history_id INT auto_increment, /*Идентификатор*/
history_type INT, /*Тип изменения*/
history_date DATETIME, /*Дата изменения */
history_text TEXT, /*Текст изменения*/
history_user CHAR, /*Пользователь*/
PRIMARY KEY (history_id)
);
INSERT INTO Users VALUES ('', 'Иванов И.И.', 2);
INSERT INTO Users VALUES ('', 'Петров П.П.', 1);
INSERT INTO Users VALUES ('', 'Сидоров С.С.', 1);
CREATE TABLE GroupUsers (
group_id INT auto_increment, /*Идентификатор группы*/
group_name CHAR /*Наименование группы*/
);
INSERT INTO GroupUsers VALUES ('', 'Сотрудники');
INSERT INTO GroupUsers VALUES ('', 'Руководители');
P.S. Хотите углубить свои знания и навыки? Присмотритесь к премиум-урокам по различным аспектам сайтостроения, включая SQL и работу с БД, а также к бесплатному курсу по созданию своей CMS-системы на PHP с нуля.
Понравился материал и хотите отблагодарить?
Просто поделитесь с друзьями и коллегами!
Смотрите также:
