База данных для сайта для чего
Что такое база данных на сайте. Просто о сложном
Для чего она нужна, как ею управлять и причем здесь скорость загрузки сайта? А еще есть так называемые ревизии, которые добавляют своих особенностей при работе. Посмотрим на все это со стороны, чтобы затем можно было умело обращаться с базой данных своего сайта. Заодно узнаем, сколько времени нужно запросу, чтобы сходить на базу и принести обратно на сайт найденную информацию.
Краткий вводный абзац
Любую информацию на сайте нужно где-то хранить. Это факт очевидный. А вот места хранения могут быть разными. Первый вариант – прямо внутри html или php файла. Такой способ встречается часто. Это когда вы открываете страницу в админке, чтобы отредактировать там информацию, а внутри страница пустая. Совсем. Но при просмотре страницы на сайте там есть текст, картинки, другие данные.
На it-волонтере у меня было, наверное, с десяток задач, когда нужно было поменять информацию именно таким способом. Все дело в том, что в этом случае текст и ссылки на картинки добавлены напрямую в php-файл темы сайта. Для изменения страницы нужно зайти на хостинг в папку темы и отредактировать нужный файл.
Второй вариант хранения данных более удобен и привычен. Это когда вы открываете в админке страницу, видите там все данные и спокойно меняете их. Обновляете страницу и все готово. При такой схеме данные обновляются динамически и берутся уже из базы данных. Вот про нее и поговорим.
Что такое база данных
Помимо информации страниц, в базе данных содержится много служебной информации. В общем, важный файл. Посмотреть список баз данных вашего аккаунта на хостинге можно в разделе «Базы данных».
Список баз данных на хостинге Timeweb.
Это перечень баз. Зайти внутрь каждой и посмотреть, что там делается, можно по ссылке полного доступа – на скриншоте сверху обведена красным. phpMyAdmin – это, в свою очередь, веб-приложение для управления базами данных. И информация внутри него будет уже чуть более необычная. Поэтому зайти туда и посмотреть можно, но менять там что-либо – только точно зная, что вы делаете. Ну, или имея в запасе резервную копию базы данных.
Причем здесь скорость загрузки сайта
Связь здесь самая прямая. Чем меньше база данных, тем быстрее в ней найдется информация для дальнейшего отображения на сайте. И наоборот. К тому же, помимо контента страниц, в базе данных хранится и другая, служебная, информация.
А это, в первую очередь, данные всех плагинов сайта. Если у вас есть плагин безопасности, который записывает всю активность пользователей (неудачные попытки входа на сайт, активные сессии), то где он хранит все эти данные? Все там же, в базе данных. Только в отдельной таблице.
База данных хранит в себе все комментарии на сайте, данные всех зарегистрированных пользователей, все ссылки и настройки сайта. Короче говоря, вообще все. Без базы данных ваш сайт просто не откроется. Вместо этого выводится пустой экран с фразой «Ошибка установки соединения с базой данных». Поэтому чем больше на сайте контента и плагинов, тем больше размер базы данных. А это значит, что со временем скорость работы сайта может упасть.
Что такое ревизии постов и страниц
По-английски это называется revision. На русский в данном контексте можно перевести как копия (или редакция) страницы. Каждый раз, когда вы изменили страницу на сайте и сохранили ее, WordPress создает копию страницы с вашими изменениями. Если через 5 минут вы вспомнили, что забыли поставить в тексте запятую и снова измените и сохраните страницу, то будет создана еще одна копия.
С одной стороны, эта схема хороша. Ведь можно при необходимости откатиться на предыдущую редакцию страницы. Но, с другой стороны, таких копий может быть создано очень много. А все они хранятся, как вы понимаете, все в той же базе данных. И все это не лучшим образом сказывается на быстродействии сайта.
Поэтому время от времени такие копии нужно удалять. Сделать это можно с помощью плагина Optimize Database after Deleting Revisions.
Скриншот страницы плагина в каталоге WordPress.
Помимо удаления ревизий плагин хорош тем, что показывает размер каждой таблицы в базе данных и ее общий размер. И если после анализа базы вы видите, что в какой-то таблице слишком много строк, то нужно посмотреть внимательно – что хранится в этой таблице и как это можно оптимизировать.
У меня стоят вот такие настройки плагина:
Скриншот настроек плагина оптимизации базы данных.
Первые две галочки отвечают как раз за удаление ревизий у постов и страниц. Плагин делает свою работу, а большего от него и не нужно. Зачастую такая оптимизация позволяет освободить немало места и сделать базу данных легче.
К слову о плагинах – на сайте Теплицы есть статья про плагины под разные задачи для сайта на WordPress. Посмотрите, почитайте.
И вообще, воспринимайте базу данных как шкаф для хранения карточек. Каждый ящик заполнен информацией по своему разделу. Если места в ящике не хватает, то нужно или прибраться в нем, или заводить новый ящик рядом. Много данных – много ящиков. Такая система есть в библиотеках, где хранятся карточки книг – на какой полке какая книга находится. Это самый показательный пример работы базы данных.
Что в итоге
Да, вся эта база данных чуть сложнее, чем правка страниц в админке сайта. Но все равно разобраться можно. Очень рекомендую как минимум проверить размер базы данных вашего сайта. Сделать это можно на хостинге или через плагин, кому как удобней. Увидите там много строк в какой-нибудь таблице – значит, есть повод разобраться в причинах и прибраться там. Сделайте сами или создавайте задачу на it-волонтере. Я подобных задач там не припомню, будет интересно.
База данных сайта
7 ноября 2017 Опубликовано в разделах: Азбука терминов. 31638
База данных по автомобилям состоит из множества таблиц. Это модели: ВАЗ, ГАЗ, FORD, VW, Ferrari и т.д. Каждая таблица имеет поля.
ВАЗ: 2101, 2104, 2105, 2107 и т.д.
В каждом поле внесены записи со значениям-характеристиками: цветовые гаммы, ЛС, мощность движка и т.д.
Таблицы связаны специальными отношениями, поэтому с записями можно работать: объединять, сортировать, делать выборку посредством указания одного запроса. Современные веб-ресурсы используют базы данных для своего функционирования.
Базы данных и организация веб-ресурса
Каждый сайт состоит из HTML-страниц. На них есть определенный каркас — то, что одинаково на любой странице. И есть контент — на каждой странице он разный.
Раньше интернет-сайты создавали на чистом HTML, и это было неудобно, так как все данные были представлены как отдельные HTML-файлы. Нельзя было осуществлять поиск, группировку, сортировку информации. К тому же, информация могла часто дублироваться. При появлении PHP у веб-мастеров появилась возможность разделения сайта на его каркас и данные в базе. Теперь структуру сайта можно хранить отдельно от контента, что позволяет быстрее и удобнее администрировать веб-ресурс, легко дорабатывать его дизайн и функционал.
Преимущества использования базы банных
Как работать с БД
Если вы в совершенстве владеете html и css, то все равно обращаетесь к Dreamweaver, чтобы снизить сложность работы с версткой сайта. Для работы с БД необходима также программа обработки SQL под названием MySQL. Она установлена на хостинге в оболочке phpMyAdmin.
По умолчанию сама БД сайта находится в каталоге data на веб-сервере интернет-проекта. К примеру, если БД имеет название bd, то все ее значения находятся в data/bd. Как правило, на хостинге доступ к файлам БД закрыт, их следует “вытягивать” посредством запросов SQL через консоль. Упрощает работу с запросами именно MySQL. Для того чтобы попасть в MySQL, необходимо зайти по ссылке, которую дает хостинг-провайдер, и ввести логин-пароль от базы.
Подключение базы к сайту происходит в конфигурационном файле при помощи указания названия, пользователя и пароля. Название файла и его и месторасположение зависит от вида вашей CMS. Для MODx это config.inc по пути /core/config/.
Необходимо периодически создавать бэкапы — резервные копии сайта и базы данных. Обычно хостинги предоставляют услуги по созданию копий сайта.
Восстановить предыдущую версию можно с той даты, за которую сохранены база и конфигурация сайта. Легче периодически делать копии, чем восстанавливать портал с нуля.
Базы данных для web-сайтов
Р. Киплинг, «Слоненок»
С чего обычно начинает человек, делающий свои первые сайты? Сперва он осваивает HTML — благо это язык гипертекстовой разметки, а не язык программирования. Затем он узнает о возможности отделить оформление страницы от ее содержания с помощью таблиц каскадных стилей (CSS). Наконец следом многие осваивают JavaScript, язык программирования скриптов на стороне клиента.
На заметку: скрипты делятся на два вида. Одни выполняются на стороне клиента — то есть без изменений скачиваются на компьютер пользователя, где их выполняет сам браузер. Другие выполняются на стороне сервера. Пользователь не может получить их исходный код, а видит только результат работы — будь то текст, картинка или HTML-страничка. Первые (например, JavaScript) созданы затем, чтобы слегка разнообразить отображение страниц, вторые (например, PHP или JSP) на лету создают динамические страницы: каталоги, форумы, поисковики. Именно они и позволяют делать все то, о чем мы поговорим в этой статье.
Динамическим сайт можно назвать с того момента, когда содержимое его страниц будет не просто лежать на сервере, отображаясь каждому зашедшему посетителю, а станет формироваться автоматически, в зависимости от запроса и по актуальным данным.
К примеру, если вы хотите сделать сайт с новостной лентой, легко обновляемой галереей или гостевой книгой, то без программирования на стороне сервера уже не обойтись. Вы придете к необходимости создания того, что сейчас называется «движком» сайта, или, если выражаться более солидно, системой управления содержимым (калька с английского Content Management System (CMS).
Для большей наглядности зайдем с другой стороны и на примере посмотрим, чем отличается статический сайт от динамического. Представьте себе продуктовый ларек и продуктовый магазин. У ларька все на одном месте — и доставка, и склад, и продажа. Все это обычно вручную обслуживается одним человеком. И все на виду. Другое дело — магазин. Есть торговый зал, где продавцы общаются с покупателями. А есть служебные помещения, где совершенно другие люди занимаются оформлением и обеспечением торговли. А есть еще и складские помещения. Чувствуете разницу?
CMS-сайт примерно так и устроен. Есть лицевые страницы, которые видит посетитель сайта. А есть и особые, редакторские страницы, доступ к которым получают те, кто наполняет его содержимым. Они совсем не обязаны быть программистами — «движок» затем и создан, чтобы автоматизировать и упростить их работу.
Ну хорошо, скажете вы, внимательно проследив за аналогией с магазином. Доставку и продажу мы разделили. Это понятно. А как насчет упомянутого склада? Вот! Тут-то мы подходим к сути данной статьи. Все правильно. Допустим, мы организовали регистрацию пользователей — теперь нужно где-то хранить их имена, пароли и уровни доступа. Завели на сайте интернет-магазин — понадобится хранилище информации о товарах, ценах, заказах и т.п. Для этого-то и нужна база данных на сайте.
Мне доводилось встречать выложенные в интернете бесплатные «движки», авторы которых заявляли в числе достоинств тот факт, что их движок обходится без MySQL. На практике это всегда означало, что авторы в качестве базы данных использовали текстовый файл, где данные разделяются запятыми. Это хорошо тем, что от хостинга не требуется поддержки баз данных. Ну допустим. А чем это плохо? Пока база данных мала, никаких особых проблем не возникнет. Но если она состоит из десятков тысяч записей о товарах, да еще и хранит разного рода связанную с ними информацию (о заказах, например), то разницу вы почувствуете очень быстро.
Самая большая, но далеко не единственная проблема — скорость отбора нужной информации. Чтобы найти нужную строку в таком текстовом файле, его приходится всегда просматривать от начала до нужного места. Никакой возможности быстрого позиционирования там не существует. Есть и другие недостатки такого подхода — они станут ясны при перечислении достоинств того типа хранения данных, который можно встретить в профессиональных «движках». Это основанные на SQL реляционные базы данных.
Из них наиболее популярны в интернете MySQL и PostgresSQL. Кстати, первая начинала свой путь с довольно простой и бесхитростной SQL-системы, но с каждой версией она все больше наращивала возможности, и место предельно упрощенной SQL-системы постепенно заняла SQLite.
Реляционные базы данных
В. Левшин, Э. Александрова,
«Путешествие по Карликании и Аль-Джебре»
Само понятие реляционный (англ. relation — отношение) связано с разработками известного английского специалиста в области систем баз данных Эдгара Кодда (Edgar Codd), сотрудника фирмы IBM. В 1970 году им был создан формальный аппарат реляционной алгебры для обработки данных. Позже он сформулировал 12 правил, которым должна соответствовать любая система по управлению реляционными базами данных (RDBMS — Relation Database Management System).
Хотя язык SQL создавался с целью воплотить идеи Эдгара Кодда в жизнь, сам Кодд не признал SQL, как и ряд других СУБД (систем по управлению баз данных), в качестве истинно реляционных. Это и стало причиной, побудившей его к публикации своих знаменитых «12 правил». Но поскольку и сам теоретик, и практики, воплощавшие его мечту, работали в одной и той же фирме, это привело к уходу Кодда из IBM. Вместе с рядом единомышленников, в том числе известным теоретиком в той же области Кристофером Дейтом, он основал собственную консалтинговую компанию.
Особенности реляционных баз
Основные особенности реляционных баз можно сформулировать так:
Чтобы такое изложение не воспринималось скучным и сложным, приведу поясняющий пример. Вот простая таблица — справочник стран. Назовем ее COUNTRIES.
| Справочник стран COUNTRIES | |
| ID | NAME |
| 1 | Россия |
| 2 | Франция |
| 3 | Марокко |
| 4 | Япония |
В таблице COUNTRIES всего два столбца:
Столбец ID служит первичным ключом таблицы, а столбец NAME содержит ту полезную информацию, которую мы и будем стремиться извлекать запросами. Все данные столбца ID — целочисленны, столбца NAME — содержат текстовую информацию.
Отношения между таблицами
Чтобы база данных стала реляционной, одних данных мало. Между ними нужны еще и связи (те самые relations, от которых и пошло слово «реляционный»).
Для связи между таблицами служит так называемый внешний ключ (foreign key). Название довольно точно выражает его суть. Если в таблице A есть столбец для хранения первичного ключа таблицы B, то такой столбец и называется внешним ключом. Первичные и внешние ключи устанавливают связи между таблицами, превращая набор таблиц в цельную конструкцию — реляционную базу данных.
Приведу пример. Допустим, мы создали еще одну простую таблицу — справочник товаров. Назовем ее GOODS.
| Товарный справочник GOODS | ||||
| ID | NAME | PRICE | UNIT | COUNTRY |
| 1 | Яблоки | 50.00 | кг | Россия |
| 2 | Груши | 60.40 | кг | Франция |
| 3 | Апельсины | 40.00 | кг | Марокко |
| 4 | Макароны | 21.00 | шт | Франция |
| 5 | Кефир | 25.30 | шт | Россия |
| 6 | Молоко | 30.50 | шт | Россия |
Ее колонки: ID — первичный ключ, NAME — название товара, PRICE — его цена, UNIT — краткое название единицы измерения, COUNTRY — название страны-производителя.
Хорошо ли построена такая таблица? Вроде бы всем упоминавшимся выше принципам она удовлетворяет: уникальные имена столбцов с однородными данными, строки с уникальным первичным ключом. Казалось бы, все на месте. Тем не менее построена она непрофессионально. Здесь мы подходим к принципам, о которых я еще не упоминал, — к понятию о нормализации таблиц. Суть в том, чтобы всюду, где только можно, избегать избыточности в хранении данных путем выделения их в отдельные таблицы.
Посмотрим на нашу таблицу GOODS. Чем она плоха? Представьте себе, что завтра придется изменить название какой-нибудь страны. Такое случается часто. Бирма когда-то меняла свое название на Мьянму, Польша — на Польскую Республику. Хочется ли вам менять огромное количество строк во всех таблицах, где эти страны упоминаются? Представьте также, что вас попросят отобрать запросом весь штучный товар. Можете ли вы быть уверены в том, что оператор всюду набил эту аббревиатуру правильно и одинаково? Скорее всего, окажется, что в таблице встречаются все мыслимые вариации: «шт», «Шт», «шт.», «штук» и «штуки».
Думаю, проблема понятна. Выходом из этой ситуации будет выделение из нее двух других таблиц: справочника стран (COUNTRIES) и справочника единиц измерений (UNITS).
| Справочник единиц измерения UNITS | ||
| ID | NAME | SHORT_NAME |
| 1 | Штуки | шт |
| 2 | Килограммы | кг |
Сам справочник товаров GOODS будет теперь выглядеть совершенно по-другому (см. таблицу).
| Товарный справочник GOODS после нормализации | ||||
| ID | NAME | PRICE | UNIT_ID | COUNTRY_ID |
| 1 | Яблоки | 50.00 | 2 | 1 |
| 2 | Груши | 60.40 | 2 | 2 |
| 3 | Апельсины | 40.00 | 2 | 3 |
| 4 | Макароны | 21.00 | 1 | 2 |
| 5 | Кефир | 25.30 | 1 | 1 |
| 6 | Молоко | 30.50 | 1 | 1 |
Что изменилось? Вместо столбцов с названиями единиц измерения и стран появились столбцы UNIT_ID и COUNTRY_ID с кодами, отсылающими нас к другим таблицам. Это и есть внешние ключи. Что означает значение 2 в столбце UNIT_ID? Оно означает, что интересующая нас информация по единице измерения находится той строке таблицы UNITS, где ID = 2. Достаточно заглянуть в этот справочник, чтобы убедиться, что называется эта единица полностью «штуки», а кратко — «шт».
Объяснение всех видов и принципов нормализации выходит далеко за рамки данной статьи. Главное — почувствовать общие принципы. Единожды научившись строить базы данных правильно, вы уже не сможете иначе. Для этого не обязательно знать теорию в полном объеме — зачастую здравого смысла и интуиции бывает достаточно.
Вернемся к нашей маленькой базе данных. Ну хорошо, нормализовали мы таблицу. Сможем теперь менять названия стран, не исправляя всю таблицу. Замечательно. Но как теперь увидеть эти названия? Ведь в справочнике товаров появились коды, и таблица сразу потеряла свою наглядность.
Вот тут-то мы и подходим к понятию уже не раз упоминавшихся запросов, которые, используя связи, извлекают из них нужную информацию и выдают нам опять же в виде так называемой отчетной таблицы.
Язык запросов. Мини-учебник по SQL
Аладдин и волшебная лампа
Что такое SQL?
SQL — это самый распространенный язык запросов к базам данных. Расшифровывается аббревиатура так: Structured Query Language — «язык структурированных запросов».
Он создавался затем, чтобы привести работу с различными типами баз данных (а их сейчас известно множество) к единому стандарту, сделать работу по управлению данными независимой ни от аппаратной, ни от программной части компьютера.
Последнее удалось не в полной мере, так как в SQL различных систем на какой-то стадии появились расхождения, поскольку разработка SQL-управляемых систем часто опережает формирование стандартов. Но в целом идею такой стандартизации можно считать реализованной.
Собственно, именно поэтому базы данных профессионально сделанных сайтов, как правило, реляционны и SQL-управляемы.
Строим запросы
Все команды SQL делятся на две группы. Одни направлены на создание и изменение структуры самой базы, другие отвечают за операции с данными — выбор, добавление новых записей и т.п.
Временно оставив в стороне формирование структуры таблиц, поговорим об операциях с данными. Начнем с запросов. В основе запроса лежит команда SELECT. Ее задача — взять исходные данные таблиц и на основании запроса пользователя построить временную «отчетную» таблицу, которую и вернуть в виде результата. Очевидно, что запросы ничего не меняют в базе данных. Их задача — извлекать данные в указанном виде. Итак, приступим:
Этот запрос извлечет все данные из таблицы GOODS, сортированные по названиям товаров. Все — потому что после ключевого слова SELECT стоит «звездочка»: она дословно означает «все колонки».
Порядок сортировки (в нашем случае — «name»), вообще говоря, может состоять из нескольких полей, перечисленных через запятую. В этом случае сначала выборка сортируется по первой указанной колонке, а те значения, которые оказались в ней одинаковыми, дополнительно сортируются по второй, и так далее.
Ограничиваем набор столбцов
Как видите, вместо звездочки мы поставили список из двух названий колонок. Теперь в итоговую выборку войдут не все поля, а только эти два. Результат запроса вы можете увидеть в таблице.
| Результат выборки | |
| NAME | PRICE |
| Яблоки | 50.00 |
| Груши | 60.40 |
| Апельсины | 40.00 |
| Макароны | 21.00 |
| Кефир | 25.30 |
| Молоко | 30.50 |
Задаем дополнительное условие выбора
Этот запрос извлечет названия и цены товаров для всех записей, где цена меньше 30, и отсортирует их в порядке убывания цен. Ключевое слово WHERE служит для ограничения выборки по строкам, а ключевое слово DESC (descending) указывает сортировать не по возрастанию (как обычно), а по убыванию. Результат опять же можно посмотреть в виде таблицы.
| Результат выборки | |
| NAME | PRICE |
| Кефир | 25.30 |
| Макароны | 21.00 |
Связываем таблицы
Переходим к более сложным запросам. Сейчас наша задача будет состоять в том, чтобы вернуть данным из таблицы GOODS их вид до нормализации. Или, проще говоря, получить наглядную таблицу, где во всех колонках стоят не номера, а нормальные текстовые названия.
SELECT goods.id, goods.name, goods.price,
units.name AS unit, countries.name AS country
FROM units, countries, goods
WHERE units.id= goods.unit_id AND countries.id= goods.country_id
ORDER BY goods.id
Рассмотрим этот запрос подробнее. В нем фигурирует уже не одна, а три таблицы, связанные через внешние и первичные ключи. Две связи, как видите, прописаны сразу после ключевого слова WHERE.
Из таблиц UNITS и COUNTRIES отобраны строки, первичные ключи которых равны значениям внешних ключей UNIT_ID и COUNTRY_ID. Чтобы уточнить, из какой таблицы мы хотим взять поля ID и NAME (ведь такие названия есть в нескольких таблицах), мы через точку приписываем имя таблицы к их названиям.
Для удобства можно временно назначить таблицам более короткие псевдонимы — это делается в секции FROM, через пробел сразу после названий таблиц. Назначим для UNITS, COUNTRIES и GOODS в качестве псевдонимов буквы U, C и G, соответственно:
SELECT g.id, g.name, g.price, u.name AS unit, c.name AS country
FROM units u, countries c, goods g
WHERE u.id=g.unit_id AND c.id=g.country_id
ORDER BY g.id
Обратите внимание, что и в выходной таблице некоторые столбцы тоже переименованы, заменены на псевдонимы. Это особенно актуально, когда в одном запросе встречаются одинаковые имена столбцов у разных таблиц (в данном случае — NAME). Ключевое, хотя и необязательное, слово AS служит как раз для этого.
У последнего запроса есть два недостатка. Во-первых, значения кодов страны или единицы измерения, указанные в основной таблице, могут отсутствовать в справочниках. В этом случае соответствующие товары просто выпадут из результатов запроса. Во-вторых, в силу определенных причин такой запрос может выполняться не вполне оптимально, иначе говоря — медленно.
Чтобы устранить оба этих недостатка, воспользуемся альтернативным способом присоединения вспомогательных таблиц, используя ключевое слово JOIN. Допустим, что в нашей базе данных единица измерения товара будет всегда обязательна к заполнению, а вот страна может указываться не всегда:
SELECT g.id, g.name, g.price, u.name AS unit, c.name AS country
FROM goods g
LEFT JOIN units u ON u.id=g.unit_id
LEFT OUTER JOIN countries c ON c.id=g.country_id
ORDER BY g.id
В этом запросе явно обозначена главная таблица — GOODS. Также здесь обеспечено «левое присоединение» вспомогательных таблиц через внешние ключи к основной таблице (LEFT JOIN) и внешнее (OUTER) присоединение таблицы стран.
Последнее означает, что присоединение строки произойдет даже в том случае, если код страны в таблице GOODS не будет указан. Название страны будет при этом пустым, но ни одна строка товарного справочника не будет потеряна.
Считаем строки
Важное место в SQL занимают так называемые агрегирующие запросы. Они нужны для вычисления числа строк, суммирования, определения максимального или минимального значения. Замечу, что они не фильтруют строки, а дают на выходе свою собственную строку, состоящую из результатов вычислений. Приведем простейшие из таких запросов.
Он использует функцию COUNT и возвращает число строк в таблице GOODS. При этом результату, который возвращает функция, присваивается псевдоним CNT. Вот как это выглядит:
| Результат выборки |
| CNT |
| 6 |
Вроде бы мы получили на выходе число. Но на самом деле это просто вырожденная таблица из одной строки и одного столбца. Усложним задачу:
SELECT MAX(price) AS max_price, MIN(price) AS min_price
FROM goods WHERE unit_id=2
Такой запрос вернет нам максимальную и минимальную цены, встречающиеся у весовых товаров (напомню, что первичный ключ 2 для весовых товаров в нашей базе данных соответствует килограммам). Результат будет выглядеть как таблица из одной строки и двух столбцов. Называются столбцы, как мы и указали: MAX_PRICE и MIN_PRICE, а в двух ячейках размещаются вычисленные значения.
Более сложные агрегирующие запросы
Из предыдущих примеров может сложиться впечатление, что агрегирующие запросы всегда возвращают только одну строку. Но это не так. Существуют и более сложные запросы, позволяющие собирать информацию, группируя ее по значениям какой-либо из колонок (одной или нескольких). Следующий запрос покажет нам минимальную и максимальную цены товаров, сгруппированные по странам-производителям:
SELECT country_id, MAX(price) AS max_price, MIN(price) AS min_price
FROM goods GROUP BY country_id
Для указания столбцов, по которым производится группировка, служит блок, начинающийся с GROUP BY. Выборка показана в таблице.
| Результат выборки | ||
| COUNTRY_ID | MAX_PRICE | MIN_PRICE |
| 1 | 50.00 | 25.30 |
| 2 | 60.40 | 21.00 |
| 3 | 40.00 | 40.00 |
Как уже неоднократно упоминалось, результат любого запроса — это таблица. Соответственно, ее снова можно использовать в качестве исходной таблицы для других запросов. Так и поступим — возьмем предыдущий пример и сделаем так, чтобы в поле COUNTRY показывались названия стран вместо их кодов.
SELECT c.name AS country, a.max_price, a.min_price
FROM countries c,
(SELECT country_id, MAX(price) AS max_price, MIN(price) AS min_price
FROM goods GROUP BY country_id) a
WHERE c.id=a.country_id
Наш предыдущий запрос включен сюда в скобках как самая обычная таблица с псевдонимом А (вот и еще одна полезная функция псевдонимов).
| Результат выборки | ||
| COUNTRY | MAX_PRICE | MIN_PRICE |
| Россия | 50.00 | 25.30 |
| Франция | 60.40 | 21.00 |
| Марокко | 40.00 | 40.00 |
Как вносить изменения в таблицы?
Если продолжить сравнивать базу данных с магазином, команда SELECT отражает лишь содержимое его прилавков, но не отражает сам процесс торговли. Товар на прилавках должен пополняться при поставках, исчезать в ходе продаж, менять цену и т.п. Значит, в SQL должны быть средства для обеспечения этих манипуляций. И такие средства, конечно, есть.
Управление данными в SQL осуществляется тремя основными командами: INSERT — для добавления новых строк, UPDATE — для их редактирования, DELETE — для удаления.
Добавим в таблицу COUNTRIES новую страну:
INSERT INTO countries (id,name) VALUES (4,»Бирма»)
Это важно: обратите внимание, что первичный ключ 4 до сих пор не был задействован в этой таблице. Так и должно быть — его уникальность обязана соблюдаться.
После этого мы узнаем, что страна эта давно уже сменила название на Мьянму и спешно исправляем оплошность:
Выяснив, что товары из Мьянмы в ближайшее время вноситься в наш справочник товаров не будут, мы решаем удалить эту строку из справочника. Делаем это так:
Хочу обратить внимание, что блок WHERE в командах UPDATE и DELETE может воздействовать не только на одну строку, но и на любую их совокупность, заданную условием, а его отсутствие оказывает действие на всю таблицу. Для того чтобы удалить из таблицы GOODS все весовые товары, мы можем выполнить команду:
DELETE FROM goods WHERE unit_id=2
А для того чтобы очистить всю эту таблицу — команду:
В системе MySQL существует еще особая команда REPLACE, замещающая строки. Работает она как силовой вариант команды INSERT (то есть если строка с требуемым первичным ключом уже существует, команда не ругнется, а удалит двойника и все равно вставит затребованную строку).
Создаем базу данных
Управление базами данных как объектами
Будем считать, что наша небольшая экскурсия по запросам и командам SQL со стороны «торгового зала» завершена. Заглянем теперь в его «служебные помещения» и познакомимся с тем, как создается сама база данных. Эта часть языка SQL не столь стандартизирована и сильно отличается в различных реализациях. Поэтому в дальнейших примерах я буду придерживаться синтаксиса, принятого в самой популярной на веб-серверах системе — MySQL.
MySQL — продукт шведской компании MySQL AB. Ее основатели — Дэвид Аксмарк, Аллан Ларсон и Майкл Видениус (последний больше известен по прозвищу — Монти). По одной из версий, первая часть названия продукта (My) — не что иное, как англизированная запись имени дочери М. Видениуса. Однако точно за происхождение названия сегодня не могут поручиться даже отцы-создатели. Существует версия, по которой «my» — это префикс, с которого начинались названия рабочих каталогов на их компьютерах.
Из всех команд чаще всего нам будут нужны три: CREATE (создать), ALTER (изменить) и DROP (уничтожить).
Чтобы создать новую базу данных с названием, ну скажем, OUR_SHOP, следует выполнить команду:
Еще лучше сразу при ее создании установить нужную кодировку (ведь по умолчанию в MySQL используется latin1). В итоге команда будет выглядеть так.
CREATE DATABASE our_shop CHARACTER SET cp1251
Если вы забыли сделать это сразу, не беда. Для того и существуют команды по изменению:
ALTER DATABASE our_shop CHARACTER SET cp1251
Когда, наигравшись вдоволь с пробной базой данных, вы захотите ее уничтожить, воспользуйтесь командой:
DROP DATABASE our_shop
Управление таблицами
Чтобы создать таблицу GOODS, на которой мы отрабатывали манипуляции с данными, потребуется составить команду примерно такого вида:
CREATE TABLE goods (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL UNIQUE,
price DECIMAL(10,2) NOT NULL,
unit_id INT DEFAULT 1,
country_id INT )
Разберем эту команду подробнее. Тип INT устанавливается для столбцов с целочисленными данными, тип VARCHAR(100) обеспечивает хранение строк с длиной не более 100 символов, DECIMAL(10,2) соответствует действительным числам с не более чем десятью знаками и точностью в два знака после запятой.
Столбец ID объявлен первичным ключом (PRIMARY KEY).
Ключевое слово AUTO_INCREMENT означает, что при добавлении новых строк с неуказанным значением ID оно будет автоматически заполняться следующим значением. Это удобно, поскольку обычно нет нужды вручную указывать значения первичных ключей, а за тем, чтобы они были уникальными, пусть лучше следит база данных.
NOT NULL означает запрет на пустые значения в столбце, иными словами, гарантирует обязательность заполнения.
Команда DEFAULT задает значение по умолчанию — то, которое будет записываться в базу при добавлении новой строки, если не указано иное. В нашем случае она обеспечивает автоматическое объявление товара штучным (код = 1) в случае, если при добавлении новых строк не будет указан другой код.
Признак UNIQUE обеспечивает уникальность значений в колонке (в нашем случае — уникальность названий товаров).
Если в будущем вы захотите перенастроить объявленные командой CREATE столбцы таблицы, сделать это можно командой ALTER. Например, таблицу GOODS можно нарастить строчной колонкой REMARK (подкоманда ADD):
ALTER TABLE goods ADD remark VARCHAR(50)
Поработав с ней немного и убедившись, что 50 символов для примечания явно недостаточно, увеличиваем максимальный размер строки до 250 (блок CHANGE):
ALTER TABLE goods CHANGE remark remark VARCHAR(250)
Так как имя столбца мы не изменяли (новое совпадает со старым), то его просто повторяем в этой команде (как бы меняем само на себя).
И наконец, убедившись через какое-то время, что без примечания в товарном справочнике вполне можно обойтись, мы удаляем ставшую ненужной колонку (блок DROP):
ALTER TABLE goods DROP remark
Удалить таблицу целиком можно командой DROP:
Стоит ли говорить о том, что пользоваться командами с этим ключевым словом следует с особой осторожностью?
Индексы и индексация таблиц
Представьте себе, что ваш приятель загадал число между 1 и 1000 и просит вас угадать его за минимальное число попыток, сообщая лишь о том, в большую или меньшую сторону вы ошиблись. Как вы поступите? Очевидно, предложите при первой попытке версию 500 (то есть начнете с середины). Если он ответит: «меньше», — предложите 250. Если «больше» — 750. Так, разбивая интервалы пополам, вы уложитесь в 10 попыток (ведь 2 10 > 10 3 ). Если бы приятель загадал число в пределах миллиарда, то количество попыток уложилось бы в 30 (2 30 > 10 9 ).
Угадывая число, вы проводили поиск примерно так, как ведут его системы баз данных, использующие индексы. Понятное дело, их работа гораздо сложнее, но главная идея именно в этом — за небольшое число попыток найти нужное значение из миллиардов возможных. Поля, по которым вам часто придется делать в базе поиск, фильтрацию или связывание таблиц между собой, есть смысл проиндексировать, то есть создать специальный связанный с таблицей объект, содержащий информацию, необходимую для вышеописанного быстрого поиска.
Как это делается практически? Поясню на примерах. Допустим, вас часто просят отобрать информацию о товарах российского производства. Чтобы по колонке COUNTRY_ID таблицы GOODS фильтрация производилась быстрее, создадим по ней индекс с именем IDX_GOODS_COUNTRY:
CREATE INDEX idx_goods_country ON goods(country_id)
Если в будущем вы передумаете использовать созданный индекс, то без труда его сможете удалить:
DROP INDEX idx_goods_country
Транзакции
Чтобы вы могли наглядно представить себе, что такое транзакция, и понять, зачем она нужна, приведу один простой пример. Представьте себе, что со счета Иванова на счет Петрова переводится сумма в 1000 рублей. Счета и того, и другого находятся в одной и той же базе данных. Как осуществляется перевод? Одна команда UPDATE уменьшает счет Иванова на 1000 руб., другая — на ту же сумму увеличивает счет Петрова.
А теперь представьте себе, что по каким-то причинам деньги у Иванова сняли (первая команда прошла), а Петрову их на счет не положили (вторая команда по какой-то причине не смогла быть завершена). Причин может быть масса: счет Петрова заблокирован, после первой команды завис компьютер и т.п. Думаю, из этого примера понятно, что либо обе команды должны быть выполнены, либо ни одна из них. Вместе они образуют единый и неделимый логический блок. Такие блоки и называются транзакциями.
В англоязычной литературе принято набор основных свойств транзакций обозначать мнемонической аббревиатурой ACID (в переводе — «кислота»). Буквами этого слова закодированы четыре свойства: Atomicity — неделимость, Consistency — согласованность, Isolation — изоляция, Durability — устойчивость). Транзакция неделима в том смысле, что представляет собой единое целое и является минимальным блоком алгоритма. Она согласованна, потому что не нарушает отношения между элементами данных или целостность базы данных даже при одновременной работе многих пользователей. Транзакция всегда изолирована, поскольку ее результаты не зависят от предыдущих или последующих транзакций. И наконец, устойчивость означает, что если транзакция завершена (зафиксирована), то внесенные ею изменения гарантированно сохранятся в базе данных.
Для объявления начала транзакции в MySQL используется стартовая команда:
После нее выполняются действия по изменению базы данных. Пока транзакция не будет завершена, промежуточные результаты этих действий видит только тот, кто их совершает. Остальные пользователи базы данных видят данные такими, какими они были до начала транзакции.
Успешное выполнение всех операций завершается командой фиксации:
После завершения транзакции сделанные ею изменения сразу становятся видны во всех сессиях, работающих с базой данных.
Для отката изменений, сделанных в ходе выполнения транзакции, используется команда:
Завершая разговор о транзакциях, хочу отметить, что MySQL может работать в двух различных режимах: с включенной и выключенной автоматической фиксацией. Включенная автофиксация означает, что каждая выполняемая команда фиксируется или откатывается автоматически, а об объявлении нескольких команд единым блоком речь не идет.
В таком режиме работает MySQL версий 3 и ниже или при установленном значении системной переменной AUTOCOMMIT=1. В режиме выключенной автофиксации (AUTOCOMMIT=0) каждое изменение в базе данных следует фиксировать явным образом (завершать командой COMMIT). Кому-то это может показаться утомительным, но именно такой режим обеспечивает целостную работу с данными, так как позволяет использовать всю мощь механизма транзакций. Именно в этом режиме работы мы можем переложить деньги Иванова на счет Петрова, не опасаясь, что они зависнут между счетами.