Размещено на http://www.allbest.ru/

54

Размещено на http://www.allbest.ru/

Реляционные базы данных

Введение

Задача длительного хранения и обработки информации появилась практически сразу с появлением первых компьютеров. Для решения этой задачи в конце 60-х годов были разработаны специализированные программы, получившие название систем управления базами данных (СУБД). СУБД проделали длительный путь эволюции от системы управления файлами, через иерархические и сетевые базы данных. В конце 80-х годов доминирующей стала система управления реляционными базами данных (СУРБД). С этого времени такие СУБД стали стандартом де-факто, и для того, чтобы унифицировать работу с ними, был разработан структурированный язык запросов (SQL), который представляет собой язык управления именно реляционными базами данных.

Замечание

Взаимодействие с базой данных происходит при помощи Системы Управления Базой Данных (СУБД), которая расшифровывает запросы и производит операции с информацией в базе данных. Поэтому более правильно было бы говорить о запросе к СУБД и о взаимодействии с СУБД из Web-приложения. Но так как это несколько усложняет восприятие, далее везде мы будем говорить "база данных", подразумевая при этом СУБД.

Существуют следующие разновидности баз данных:

· иерархические;

· реляционные;

· объектно-ориентированные;

· гибридные.

Иерархическая база данных основана на древовидной структуре хранения информации. В этом смысле иерархические базы данных очень напоминают файловую систему компьютера.

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

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

Гибридные СУБД совмещают в себе возможности реляционных и объектно-ориентированных баз данных.

В Web-приложениях, как правило, используются реляционные базы данных. Мы будем рассматривать пример базы данных, на которой основано большинство форумов, в том числе и тот, который мы далее будем разрабатывать. В этой базе хранится информация об авторах форума (authors), о названиях форумов (forums), о темах форума (themes) и, собственно, сами сообщения (posts). Таким образом, наша база данных будет включать следующие таблицы:

Таблица 1 Таблицы базы данных Forum

authors
forums
posts
themes

Модель реляционной базы данных представляет данные в виде таблиц, разбитых на строки и столбцы, на пересечении которых находятся данные. Пример такой таблицы показан в Табл.2:

Таблица 2 Структура реляционной базы данных.

	ряд
строка	id_forum	name	Description
	1	дизайн	Обсуждаются вопросы дизайна
	2	MySQL	Обсуждаются вопросы, связанные с MySQL
	3	PHP	Обсуждаются вопросы, связанные с PHP
	4	разное	Другие вопросы

В табл.2 приведён пример таблицы forums базы данных большого форума, в котором имеется несколько разделов, посвящённых различным этапам построения Web-приложения. Каждая строка этой таблицы представляет собой один раздел форума. Четыре строки таблицы представляют собой весь форум.

Каждый столбец таблицы forums представляет один элемент данных для каждого из форумов. Столбец id_forum содержит уникальный идентификатор форума, столбец name содержит название форума и столбец description содержит краткое описание проблемы, обсуждаемой на форуме.

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

· Данные хранятся в таблицах, состоящих из столбцов и строк;

· На пересечении каждого столбца и строчки стоит в точности одно значение;

· У каждого столбца есть своё имя, которое служит его названием, и все значения в одном столбце имеют один тип. Например, в столбце id_forum все значения имеют целочисленный тип, а в строке name - текстовый;

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

Запросы к базе данных возвращают результат в виде таблиц, которые тоже могут выступать как объект запросов.

1. Нормализация базы данных

Определение:

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

Определение:

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

Нормализация несет с собой немало преимуществ. Очевидно, что в нормализованной базе данных уменьшается вероятность возникновения ошибок, она занимает меньше места на жестком диске и т.д.

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

Таблица 3. Пример избыточности в таблицах базы данных

Профессия	Сотрудник
"Инженер"	Гусев И.К.
"Инженер"	Иванов П.В.
"Рабочий"	Иванов К.Л.
"Рабочий"	Дружков П.К.
"Рабочий"	Фомичев В.М.

Эта таблица избыточна - для каждого из сотрудников повторяются одинаковые названия профессий. Т.е. схема такой базы данных не нормализована. Для небольшой базы данных это не критично, но в больших по объёму базах данных это скажется на размере базы и, в конечном счёте, на скорости доступа.

Для нормализации необходимо разбить эту таблицу на две - для профессий (см. табл. 4) и для фамилий сотрудников (см. табл. 5).

Таблица 4. Таблица профессий

Профессия	Первичный ключ
"Инженер"	1
"Рабочий"	2

Таблица 5. Таблица сотрудников

Профессия (внешний ключ)	Сотрудник
1	Гусев И.К.
1	Иванов П.В.
2	Иванов К.Л.
2	Дружков П.К.
2	Фомичев В.М.

Теперь каждый тип профессии обозначен уникальным числом, и строка в базе данных присутствует только в единственном экземпляре: в таблице профессий. Размер поля "профессия" уменьшился, так как строка занимает больше памяти, чем число.

Замечание

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

Первичные ключи

Первичный ключ (primary key) представляет собой один из примеров уникальных индексов и применяется для уникальной идентификации записей таблицы. Никакие из двух записей таблицы не могут иметь одинаковых значений первичного ключа. Первичный ключ обычно сокращенно обозначают как PK (primary key).

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

К примеру, в базе данных Forum таблицы themes и posts связаны между собой следующим образом:

Первичным ключом таблицы themes является id_theme, а таблицы posts - id_post. Обратите внимание, что поле id_theme присутствует и в таблице posts. Каждое значение этого поля в таблице posts является внешним ключом (в данном случае это внешний ключ для первичного ключа таблицы themes). Внешний ключ сокращенно обозначают как FK (foreign key).Как видно из рис.1, внешний ключ ссылается на первичный ключ таблицы themes, устанавливая однозначную логическую связь между записями таблиц themes и posts. Иначе говоря, если внешний ключ для записи (сообщения) с PK=1 в таблице posts имеет значение внешнего ключа равное 1, то это значит, что это сообщение относится к теме с PK=1 таблицы themes.

Способы задания первичного ключа

По способу задания первичных ключей различают логические (естественные) ключи и суррогатные (искусственные).

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

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

Совет:

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

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

база данные нормализация

2. Что такое MySQL. Взаимодействие с PHP

Итак, для начала ответим на вопрос: что такое MySQL?

MySQL - это одна из самых популярных и самых распространенных СУБД (система управления базами данных) в интернете. Она не предназначена для работы с большими объемами информации, но ее применение идеально для интернет сайтов, как небольших, так и достаточно крупных.

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

Немаловажным фактором является ее бесплатность. MySQL распространяется на условиях общей лицензии GNU (GPL, GNU Public License). Ранее для долговременного хранения информации мы работали с файлами: помещали в них некоторое количество строчек, а затем извлекали их для последующей работы. Задача длительного хранения информации очень часто встречается в программировании Web-приложений: подсчёт посетителей в счётчике, хранение сообщений в форуме, удалённое управление содержанием информации на сайте и т.д.

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

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

Примечание

Приложение на РНР, использующее для хранения информации базу данных (в частности MySql) всегда работает быстрее приложения, построенного на файлах. Дело в том, что базы данных написаны на языке C++, и написать на PHP программу, которая работала бы с жёстким диском эффективнее базы данных - задача неразрешимая по определению, поскольку программы на PHP в принципе работают медленнее, чем программы на C++, так как РНР - интерпретатор, а С++ - компилятор. Таким образом, основное достоинство базы данных заключается в том, что она берёт на себя всю работу с жёстким диском и делает это очень эффективно.

Взаимодействие PHP и MySQL
	Предыдущая		Следующая

Работа с сервером MySQL

В этом разделе мы поговорим о том, как работать с клиентской программой mysql, с помощью которой можно подсоединяться к MySQL-серверу, выполнять SQL-запросы и просматривать результаты этих запросов. Текст этого раздела рассчитан на то, что на вашем компьютере уже установлена утилита mysql и существует связь с сервером MySQL.

При подключении к серверу MySQL с помощью программы mysql нужно ввести имя пользователя, и, как правило, пароль. Если сервер и клиент находятся на разных машинах, необходимо также указать имя хоста, на котором запущен сервер MySQL:

shell> mysql -h host -u user -p

После этого на экране появится запрос Enter password:, и вам нужно будет ввести свой пароль. Если соединение прошло нормально, то на экране появляется следующая информация и метка командной строки mysql>:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 459 to server version:

Type 'help' for help.

mysql>

Появление метки mysql> означает, что программа mysql готова к работе.

Отсоединиться от сервера можно в любой момент, набрав команду QUIT:

mysql> QUIT

Bye

Кроме этого, разорвать соединение с сервером можно также, одновременно нажав клавиши+.

Замечание

Как правило, на только, что установленном MySQL на локальной машине доступ осуществляется без ввода пароля и хоста, вводом команды mysql в командной строке.

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

Ниже приведена простая команда, запрашивающая у сервера информацию об его версии и текущей дате:

mysql> SELECT VERSION(), CURRENT_DATE;

Ответом MySQL на этот запрос будет следующая таблица:

+--------------+--------------------+

| version() | current_date |

+--------------+--------------------+

1 row in set (0.02 sec)

На примере выполнения этого запроса можно увидеть следующие основные особенности работы с MySQL:

· Команда, посылаемая серверу, обычно состоит из SQL-выражения, за которым следует точка с запятой. Из этого правила есть несколько исключений, когда после команды точка с запятой не ставится, к примеру, уже упомянутая команда QUIT;

· MySQL выводит результаты запроса в виде таблицы;

· После вывода таблицы с результатами запроса, mysql сообщает количество возвращаемых строк и время выполнения запроса. Это удобно, поскольку позволяет оценить как производительность сервера, так и эффективность выполняемого запроса;

· После вывода результатов запроса и времени его выполнения, mysql выводит новую строку mysql>, что означает готовность к выполнению новых команд.

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

mysql> select version(), current_date;

mysql> SELECT VERSION(), CURRENT_DATE;

mysql> Select Version(), Сurrent_DATE;

MySQL позволяет на одной строчке разместить несколько команд, но каждая из них должна заканчиваться точкой с запятой. К примеру:

mysql> SELECT VERSION(); SELECT NOW();

На такой запрос мы получим следующий результат:

+---------------+

| version() |

+---------------+

| 4.0.13-nt |

+---------------+

1 row in set (0.00 sec)

+----------------------------------+

| NOW() |

+----------------------------------+

| 2004-01-25 16:57:00 |

+----------------------------------+

1 row in set (0.03 sec)

Однако помещать все команды на одной строке совершенно необязательно:

mysql> SELECT USER(),

-> CURRENT_DATE;

И вот результат:

+----------------------------+---------------------+

| user() | current_date |

+----------------------------+---------------------+

| ODBC@localhost | 2004-01-25 |

+----------------------------+---------------------+

1 row in set (0.00 sec)

Заметьте, что после того, как мы перешли на новую строку, метка командной строки изменилась с mysql> на ->. Таким образом mysql показывает, что завершенного запроса не получено и она ожидает конца запроса. Эта метка весьма полезна, так как позволяет избежать некоторых ошибок. К примеру, если вы забудете поставить точку с запятой в конце запроса, mysql сообщит вам об этом, выведя метку ->:

mysql> select user()

->

В завершение этого раздела, продемонстрируем, как можно использовать MySQL в качестве простого калькулятора, введя, к примеру, такой запрос:

mysql> select cos(pi()/10), (2*5)-5;

Язык SQL

Структурированный язык запросов SQL позволяет производить различные операции с базами данных: создавать таблицы, помещать, обновлять и удалять из них данные, производить запросы из таблиц и т.д. Далее мы последовательно рассмотрим все эти операторы.

Замечание

Не смотря на то, что последний стандарт SQL принят в 1992 году, на сегодняшний день нет ни одной базы данных, где бы он полностью выполнялся. Более того, в различных базах данных часть операций осуществляется по-разному. Мы будем придерживаться диалекта SQL характерного для СУБД MySQL поэтому не все запросы могут выполняться для других баз данных.

Команды SQL

create database use create table describe	alter table drop table drop database insert into…values	delete select update show

В этом разделе мы изучим основные команды языка SQL. Для этого на компьютере с установленным MySQL необходимо запустить клиента mysql, в окне которого можно вводить команды SQL.

Примечание

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

Типы полей базы данных

Список наиболее часто встречающихся типов приведен в таблицах 13.6 - 13.8. Для многих типов данных задается максимальная ширина отображения, указываемая в скобках, которую мы далее будем обозначать символом max. К примеру, запись INT(2) означает, что значение данного поля не может превышать 100.

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

Таблица 13.6. Числовые типы

Тип	Описание
TINYINT[(max)]	Очень маленькие целые числа диапазона -127…128.
SMALLINT[(max)]	Маленькие целые числа диапазона -32768…32767.
MEDIUMINT[(max)]	Средние целые числа.
INT[(max)]	Обычные целые числа.
FLOAT[(max,P)]	Числа с плавающей точкой одинарной точности.
DOUBLE[(max,P)]	Числа с плавающей точкой двойной точности.
DECIMAL[(max,P)]	Числа с плавающей точкой, приведенные к типу char.

Типы даты и времени приведены в таблице 13.7.

Таблица 13.7. Типы даты и времени

Тип	Описание
DATE	Дата в формате ГГГГ-ММ-ДД.
TIME	Время в формате ЧЧ-ММ-СС.
DATETIME	Дата и время в формате ГГГГ-ММ-ДД ЧЧ-ММ-СС.
YEAR	Год в формате ГГ или ГГГГ.
TIMESTAMP	Метка времени для отсчетов по транзакциям в формате ГГГГ-ММ-ДД ЧЧ-ММ-СС.

Основные строковые типы приведены в таблице 13.8.

Таблица 13.8. Строковые типы

Тип	Описание
CHAR (len)[BINARY]	Строки с длиной len, которое не превышает 255 символов. Ключевое слово BINARY указывает на то, что данные должны обрабатываться независимо от регистра.
CHAR	Синоним CHAR(1).
VARCHAR (len)[BINARY]	Синоним CHAR(len)за исключением того, что строки могут быть произвольной длины.
TEXT	Строки с максимальной длиной символов равной 65535. Данные этого типа чувствительны к регистру.
BLOB	Двоичные строки с максимальной длиной символов равной 65535. Тип BLOB (binary large object - большой двоичный объект) предназначен для хранения двоичных данных, в том числе изображений и звуковых последовательностей.

CREATE DATABASE

Эта команда создает новую базу данных:

CREATE DATABASE db_name;

Здесь db_name является именем создаваемой базы данных. Для того чтобы создать новую базу данных forum, наберите в строке-приглашении клиента MySQL mysql> эту команду и укажите название базы данных:

mysql> CREATE DATABASE forum;

Примечание

Каждый запрос MySQL завершается точкой с запятой.

При успешном выполнении команды MySQL выдаст строку, в которой сообщается, что этот запрос выполнен успешно и показано время, затраченное на выполнение запроса:

Query OK, 1 row affected (0.02 sec)

Далее в тексте строку MySQL с результатом выполнения команды мы будем приводить непосредственно в соответствующем листинге.

Для того, чтобы убедится, что база данных forum успешно создана, можно выполнить команду SHOW DATABASES, которая покажет, какие базы данных существуют на вашем компьютере:

mysql> SHOW DATABASES;

Как видим, среди различных баз данных на компьютере автора присутствует и только что созданная база данных forum:

Замечание

Команда SHOW DATABASES является внутренней командой MySQL, отсутствующей в стандарте SQL и неподдерживаемой другими базами данных.

Замечание

Изначально, в МуSQL присутствует только две базы данных: test и mysql. В последней хранится системный каталог, описывающий внутреннюю структуру СУБД MySQL.

USE

Для того чтобы начать работу с таблицами, необходимо сообщить MySQL с какой базой данных вы намерены работать. Это осуществляется при помощи команды USE:

USE db_name;

Здесь db_name - название выбираемой базы данных. Выберем созданную базу forum:

mysql> CREATE DATABASE forum;

Database changed;

CREATE TABLE

Команда CREATE TABLE создает новую таблицу в выбранной базе данных и которая в простейшем случае имеет следующий синтаксис:

CREATE TABLE table_name [(create_definition, ...)]

Здесь table_name - имя создаваемой таблицы.

Создадим первую таблицу базы данных forum, которая называется authors и содержит различные данные об зарегистрированных посетителях форума: ник (name), пароль (passw), e-mail (email), Web-адрес сайта посетителя (url), номер ICQ (iсq), сведения об посетителе (about), строку содержащую путь к файлу фотографии посетителя (photo), время добавления запроса (time), последнее время посещения форума (last_time), статус посетителя - является ли он модератором, администратором или обычным посетителем (statususer). Кроме перечисленных полей в таблице имеется поле id_author, являющийся первичным ключом таблицы. SQL-запрос, создающий эту таблицу приведен в листинге:

mysql> CREATE TABLE authors (

id_author int(6) NOT NULL auto_increment,

name text,

passw text,

email text,

url text,

iсq text,

about text,

photo text,

time datetime default NULL,

last_time datetime default NULL,

themes int(10) default NULL,

statususer int(2) default NULL,

PRIMARY KEY (id_author)

) TYPE=MyISAM;

Выполнив SQL-команду SHOW TABLES, можно убедиться, что таблица authors успешно создана

Давайте теперь аналогичным образом создадим другие таблицы.

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

Примечание

Для удобства на форуме может быть создано несколько различных разделов. К примеру, на форуме по языкам программирования для того, чтобы не смешивать темы, относящиеся к различным языкам, имеет смысл создать следующие разделы: С++, PHP, Java и т. д.

В таблице forums присутствуют следующие поля: первичный ключ (id_forum), название раздела (name), правила форума (rule), краткое описание форума (logo), порядковый номер (pos), флаг, принимающий значение 1, если форум скрытый и 0, если общедоступный (hide).

Вот SQL-запрос, создающий таблицу forums

mysql> CREATE TABLE forums (

id_forum int(6) NOT NULL auto_increment,

name text,

rule text,

logo text,

pos int(6) default NULL,

hide int(1) default NULL,

PRIMARY KEY (id_forum)

) TYPE=MyISAM;

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

mysql> CREATE TABLE themes (

id_theme int(11) NOT NULL auto_increment,

name text,

author text,

id_author int(6) default NULL,

hide int(1) default NULL,

time datetime default NULL,

id_forum int(2) default NULL,

PRIMARY KEY (id_theme)

) TYPE=MyISAM;

В таблице themes присутствуют следующие поля: первичный ключ (id_theme), название темы (name), автор темы (author), внешний ключ к таблице авторов (id_author), флаг, принимающий значение 1, если тема отмечена скрытой и 0, если отображается (hide) - это поле необходимо для модерирования, время добавления темы (time), внешний ключ к таблице форумов (id_forum), для того чтобы определить к какому разделу форума относится данная тема.

В таблице themes нормализация проведена частично, она содержит два внешних ключа:id_author и id_forum для таблиц посетителей и списка форумов, в тоже время в ней дублируется имя автора author, присутствующее также в таблице посетителей authors под именем name. Этот случай является примером денормализации предназначенной для того, чтобы не запрашивать каждый раз таблицу авторов при выводе списка тем и их авторов, чтобы обеспечить приемлемую скорость работы форума.

Создадим последнюю таблицу posts, в которой хранятся сообщения:

mysql> CREATE TABLE posts (

id_post int(11) NOT NULL auto_increment,

name text,

url text,

file text,

author text,

id_author int(6) default NULL,

hide int(1) default NULL,

time datetime default NULL,

parent_post int(11) default NULL,

id_theme int(11) default NULL,

PRIMARY KEY (id_post)

) TYPE=MyISAM;

В таблице posts присутствуют следующие поля: первичный ключ (id_post), тело сообщения (name), необязательная ссылка на ресурс, которую автор сообщения может ввести при добавлении сообщения (url), путь к файлу прикрепляемому к сообщению (file), имя автора (author), внешний ключ к таблице авторов (id_author), флаг, принимающий значение 1, если сообщение отмечено как скрытое и 0, если он отображается (hide) - это поле необходимо для модерирования, время добавления сообщения (time), сообщение ответом на которое является данное сообщение (parent_post), если это первое сообщение в теме - это поле равно 0, внешний ключ к тем (id_theme), для того чтобы определить к какой теме относится сообщение.

Убедимся, что все таблицы успешно созданы, выполнив команду SHOW TABLES.

DESCRIBE

Команда DESCRIBE показывает структуру созданных таблиц и имеет следующий синтаксис:

DESCRIBE tаble_name

Здесь tаble_name - имя таблицы структура которой запрашивается.

Замечание

Команда DESCRIBE не входит в стандарт SQL и является внутренней командой СУБД MySQL.

Давайте посмотрим, к примеру, структуру таблицы forums, выполнив следующий SQL-запрос:

mysql> DESCRIBE forums;

После выполнения этой команды, интерпретатор mysql выведет следующую таблицу

ALTER TABLE

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

ALTER TABLE table_name alter_spec

Параметр alter_spec имеет значения, представленные в таблице

Синтаксис	Описание команды
ADD create_definition [FIRST|AFTER column_name]	Добавление нового столбца create_definition. create_definition представляет собой название нового столбца и его тип. Конструкция FIRST добавляет новый столбец перед столбцом column_name. Конструкция AFTER добавляет новый столбец после столбца column_name. Если место добавления не указано, по умолчанию столбец добавляется в конец таблицы.
ADD INDEX [index_name] (index_col_name,...)	Добавление индекса index_name для столбца index_col_name. Если имя индекса index_name не указывается, ему присваивается имя совпадающее с именем столбца index_col_name.
ADD PRIMARY KEY (index_col_name,...)	Делает столбец index_col_name или группу столбцов первичным ключом таблицы.
CHANGE old_col_name new_col_name type	Изменение столбца с именем old_col_name на столбец с именем new_col_name и типом type.
DROP col_name	Удаление столбца с именем col_name.
DROP PRIMARY KEY	Удаление первичного ключа таблицы.
DROP INDEX index_name	Удаление индекса index_name.

Добавим в таблицу forums новый столбец test, разместив его после столбца name.

mysql> ALTER TABLE forums ADD test int(10) AFTER name;

Выполнив команду DESCRIBE forums, можно увидеть, что столбец test успешно добавлен после столбца name

Давайте переименуем созданный столбец test в текстовый столбец new_test

mysql> ALTER TABLE forums CHANGE test new_test text;

Как видно из рисунка, столбец успешно переименован:

При изменении только типа столбца, а не его имени, указание имени все-равно необходимо, хотя в этом случае оно будет фактически повторяться.

mysql> ALTER TABLE forums CHANGE new_test new_test int(5) not null;

Результат выполнения этого запроса приведен на рисунке:

Теперь удалим столбец new_test:

mysql> ALTER TABLE forums DROP new_test;

Как видно из рисунка, после удаления этого столбца таблица forums приобрела исходную структуру:

DROP TABLE

Команда DROP TABLE предназначена для удаления одной или нескольких таблиц:

DROP TABLE table_name [ ,table_name,...]

К примеру, для удаления таблицы forums нужно выполнить следующий SQL-запрос:

mysql> DROP TABLE forums;

DROP DATABASE

Команда DROP DATABASE удаляет базу данных со всеми таблицами входящими в её состав:

DROP DATABASE database_name

Удалим, например, базу данных forum:

mysql> DROP DATABASE forum;

INSERT INTO…VALUES

Команда INSERT…VALUES вставляет новые записи в существующую таблицу. Синтаксис команды:

INSERT INTO table_name VALUES (values,…)

После оператора VALUES в скобках через запятую перечисляются значения соответствующих полей таблицы в соответствии с их типами.

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

mysql> INSERT INTO authors VALUES (1, 'Maks', '123', ' maks@mail.ru ',

' www.softtime.ru ', '', 'программист', '', '', '', 0, 0);

mysql> INSERT INTO authors VALUES (2, 'Igor', '123', 'igor@mail.ru',

'http://www.softtime.ru', '', 'Программист', '', '', '', 407, 0);

mysql> INSERT INTO authors VALUES (3, 'Sergey', '212', 'sergey@mail.ru',

'http://www.softtime.ru', '', 'Дизайнер', '', '', '', 408, 0);

DELETE

DELETE FROM table_name [WHERE definition]

Команда DELETE удаляет из таблицы table_name записи, удовлетворяющие заданным вdefinition условиям, и возвращает число удаленных записей.

Вот как можно удалить все записи из таблицы authors:

mysql> DELETE FROM authors;

Важной частью запросов DELETE, UPDATE и SELECT является оператор WHERE, который позволяет задать условия для выбора записей, на которые будут действовать эти команды. Следующий запрос удаляет из таблицы посетителя, первичный ключ для которого равен 1:

mysql> DELETE FROM authors WHERE id_author = 1;

Условия отбора могут быть значительно сложнее, так в листинге 13.16 удаляются все авторы с паролем '123' и первичный ключ которых превышает 10:

mysql> DELETE FROM authors WHERE passw = '123' AND id_author > 10;

Оператор AND является логическим "и". В запросах можно так же применять логическое или "или".

SELECT

Команда SELECT предназначена для извлечения строк данных из одной или нескольких таблиц и имеет в общем случае следующий синтаксис:

SELECT column,...

[FROM table WHERE definition]

[ORDER BY col_name [ASC | DESC], ...]

[LIMIT [offset], rows]

Здесь column - имя выбираемого столбца. Можно указать несколько столбцов через запятую. Если необходимо выбрать все столбцы можно просто указать знак звёздочки *. Ключевое словоFROM указывает таблицу table из которой извлекаются записи. Ключевое слово WHEREопределяет, так же как и в операторе DELETE определяет условия отбора строк. Ключевое словоORDER BY сортирует строки запросов по столбцу col_name в прямом (ASC) или обратном порядке (DESC). Ключевое слово LIMIT сообщает MySQL об выводе только rows запросов начиная с позиции offset.

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

mysql> INSERT INTO forums VALUES (1, 'Форум1', '', '', 1, 0);

mysql> INSERT INTO forums VALUES (2, 'Форум2', '', '', 2, 0);

mysql> INSERT INTO forums VALUES (3, 'Форум3', '', '', 3, 0);

mysql> INSERT INTO forums VALUES (4, 'Форум4', '', '', 4, 0);

mysql> INSERT INTO forums VALUES (5, 'Форум5', '', '', 5, 0);

Для того чтобы посмотреть всю таблицу forums выполняется следующий запрос:

mysql> SELECT * FROM forums;

Выбираем все столбцы из таблицы forums без ограничений. Результат показан на рисунке:

Можно выбрать не все столбцы таблицы, а лишь часть, для этого необходимо явно задать список выбираемых столбцов:

mysql> SELECT id_forum, name FROM forums;

В этом случае MySQL выведет лишь два столбца с первичным ключом id_forum и названием форума name

Оператор LIMIT используется для ограничения количества строк, возвращенных командойSELECT. К примеру:

mysql> SELECT * FROM forums LIMIT 3;

В результате этого запроса будет выведено только первые 3 записи из 5

Оператор LIMIT может также принимать два числовых аргумента, которые должны быть целыми числами. В этом случае последний аргумент задает максимальное количество возвращаемых строк, а первый сообщает MySQL начиная с какой по счёту строки производить отсчёт

mysql> SELECT * FROM forums LIMIT 1,3;

В этом случае будут возвращены строки 2, 3 и 4

Оператор WHERE применяется в команде SELECT точно так же, как и в команде DELETE. Выберем из таблицы только те записи, у которых значение id_forum больше 2:

mysql> SELECT * FROM forums WHERE id_forum > 2;

Результат показан на следующем рисунке:



Порядок сортировки выводимых записей можно задавать при помощи оператора ORDER BY:

mysql> SELECT * FROM forums WHERE id_forum > 2 ORDER BY pos;

В этом запросе выводятся все записи со значением поля id_forum не меньше двух, которые при этом сортируются по значению поля pos. Результат такого запроса показан на рисунке:

UPDATE

UPDATE table

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE definition]

[LIMIT rows]

Команда UPDATE обновляет столбцы таблицы table в соответствии с их новыми значениями в строках существующей таблицы. В выражении SET указывается, какие именно столбцы следует модифицировать и какие величины должны быть в них установлены. В выражении WHERE, если оно присутствует, задается, какие строки подлежат обновлению. В остальных случаях обновляются все строки. Ключевое слово LIMIT позволяет ограничить число обновляемых строк.

В следующим листинге разделу форума с первичным ключом 2 устанавливается новое название (PHP) и устанавливается атрибут hide равным 1, делая форум невидимым.

UPDATE forums SET name='PHP', hide=1 WHERE id_forum=2;

SHOW

С этой командой мы уже встречались ранее, когда выполняли запросы вида show databasesи show tables для получения списка баз данных и таблиц в выбранной базе данных. Рассмотрим еще несколько вариантов использования этой команды. Вывести список всех столбцов выбранной таблицы можно при помощи следующего запроса:

mysql> SHOW FIELDS FROM authors;

Результат приведен на следующем рисунке:

Можно также отобразить информацию обо всех индексах конкретной таблицы:

mysql> SHOW INDEX FROM authors;

Выполнив команду SHOW PROCESSLIST можно увидеть список всех выполняющихся в системе запросов:

Взаимодействие PHP и MySQL

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

Замечание

SQL может применятся в прикладных программах двумя способами: в виде встроенного SQL и интерфейса программирования приложений (Application Program Interface, API). Первый способ напоминает использование PHP - инструкции SQL размещаются среди кода прикладной программы. В настоящий момент такой стиль не поддерживает ни MySQL ни PHP. Второй подход заключается в том, что программа взаимодействует с СУБД посредством совокупности функций. Именно такой подход используется при взаимодействии PHP и MySQL.

mysql_connect

resource mysql_connect ([string server [, string username [, string password]]])

Эта функция устанавливает соединение с сервером server MySQL и возвращает дескриптор соединения с базой данных, по которому все другие функции, принимающие этот дескриптор в качестве аргумента, будут однозначно определять выбранную базу данных. Вторым и третьим аргументами этой функции являются имя пользователя username и его пароль password соответственно:

<?php

$dblocation = "localhost"; // Имя сервера

$dbuser = "root"; // Имя пользователя

$dbpasswd = ""; // Пароль

$dbcnx = @mysql_connect($dblocation,$dbuser,$dbpasswd);

if (!$dbcnx) // Если дескриптор равен 0 соединение не установлено

{

echo("<P>В настоящий момент сервер базы данных не доступен, поэтому

корректное отображение страницы невозможно.</P>");

exit();

}

?>

Переменные $dblocation, $dbuser и $dbpasswd хранят имя сервера, имя пользователя и пароль, и, как правило, прописываются в отдельном файле (к примеру, config.php), который потом вставляется в каждый PHP-файл, в котором имеется код для работы с MySQL:

<?

$dblocation = "localhost";

$dbname = "forum";

$dbuser = "root";

$dbpasswd = "";

?>

mysql_close

bool mysql_close ([resource link_identifier])

Эта функция разрывает соединение с сервером MySQL, и возвращает true при успешном выполнении операции и false в противном случае. Функция принимает в качестве аргумента дескриптор соединения с базой данных, возвращаемый функцией mysql_connect.

К примеру:

// устанавливаем соединение с базой данных

$dbcnx = @mysql_connect($dblocation,$dbuser,$dbpasswd);

if (!$dbcnx)

{

// Выводим предупреждение

echo ("<P>В настоящий момент сервер базы данных не доступен, поэтому

корректное отображение страницы невозможно.</P>");

// Завершаем работу в случае неудачи

exit();

}

if(mysql_close($dbcnx)) // разрываем соединение

{

echo("Соединение с базой данных прекращено");

}

else

{

echo("Не удалось завершить соединение");

}

mysql_select_db

bool mysql_select_db (string database_name [, resource link_identifier])

Использование этой функции эквивалентно вызову команды USE в SQL-запросе, т. е. функция mysql_select_db выбирает базу данных для дальнейшей работы, и все последующие SQL-запросы применяются к выбранной базе данных. Функция принимает в качестве аргументов название выбираемой базы данных database_name и дескриптор соединения resource. Функция возвращает true при успешном выполнении операции и false в противном случае. К примеру:

<?php

// Код соединения с базой данных

if (!@mysql_select_db($dbname, $dbcnx))

{

echo( "<P>В настоящий момент база данных не доступна, поэтому

корректное отображение страницы невозможно.</P>" );

exit();

}

?>

Имеет смысл помещать функции для соединения и выбора базы данных в тот же файл (config.php), где объявлены переменные с именами сервера, пользователя и паролем:

<?php

$dblocation = "localhost";

$dbname = "softtime";

$dbuser = "root";

$dbpasswd = "";

$dbcnx = @mysql_connect($dblocation,$dbuser,$dbpasswd);

if (!$dbcnx)

{

echo( "<P>В настоящий момент сервер базы данных не доступен, поэтому

корректное отображение страницы невозможно.</P>" );

exit();

}

if (!@mysql_select_db($dbname, $dbcnx))

{

echo( "<P>В настоящий момент база данных не доступна, поэтому

корректное отображение страницы невозможно.</P>" );

exit();

}

?>

mysql_query

resource mysql_query (string query)

Эта функция применяется для отправки серверу SQL-запросов. Функция возвращает дескриптор запроса в случае успеха и false в случае неудачного выполнения запроса. В листинге 13.32 показан код, с помощью которого извлекается одна строка из таблицы authorsбазы данных forum.

<?php

include "config.php";

$ath = mysql_query("select * from authors;");

if($ath)

{

$author = mysql_fetch_array($ath);

echo "<br>имя = ".$author['name']."<br>";

echo "пароль = ".$author['passw']."<br>";

echo "e-mail = ".$author['email']."<br>";

echo "url = ".$author['url']."<br>";

echo "ICQ = ".$author['icq']."<br>";

echo "about = ".$author['about']."<br>";

echo "photo = ".$author['photo']."<br>";

echo "time = ".$author['time'];

}

else

{

echo "<p><b>Error: ".mysql_error()."</b></p>";

exit();

}

?>

Результат выполнения показан на следующем рисунке:

mysql_fetch_array

array mysql_fetch_array (resource result)

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

<?php

include "config.php";

$ath = mysql_query("select * from authors;");

if($ath)

{

// Определяем таблицу и заголовок

echo "<table border=1>";

echo "<tr><td>имя</td><td>пароль</td><td>e-mail</td><td>url</td></tr>";

// Так как запрос возвращает несколько строк, применяем цикл

while($author = mysql_fetch_array($ath))

{

echo "<tr><td>".$author['name']."&nbsp;</td><td>".$author['passw']."

&nbsp </td><td>".$author['email']."&nbsp;</td><td>".

$author['url']."&nbsp;</td></tr>";

}

echo "</table>";

}

else

{

echo "<p><b>Error: ".mysql_error()."</b><p>";

exit();

}

?>

Результат показан на следующем рисунке:



mysql_result

mixed mysql_result (resource result, int row)

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

<?php

include "config.php";

$ath = mysql_query("select name from authors;");

if($ath)

{

echo mysql_result($ath,0,'name');

}

else

{

echo "<p><b>Error: ".mysql_error()."</b><p>";

exit();

}

?>

mysql_fetch_object

object mysql_fetch_object (resource result)

Эта функция возвращает поля записи данных в виде объекта. Ниже приведен пример, в котором с помощью этой функции из таблицы authors выводятся имя, URL и e-mail авторов.

<?php

include "config.php";

$ath = mysql_query("select * from authors;");

if($ath)

{

while($row = mysql_fetch_object($ath))

{

echo "<p>name: ".$row->name."</p>";

echo "<p>url: ".$row->url."</p>";

echo "<p>email: ".$row->email."</p>";

}

}

else

{

echo "<p><b>Error: ".mysql_error()."</b><p>";

exit();

}

?>

Результат выполнения показан на следующем рисунке:



mysql_fetch_row

array mysql_fetch_row (resource result)

В отличие от функции mysql_fetch_object, эта функция возвращает не объект, а массив, в котором содержатся значения полей:

<?php

include "config.php";

$ath = mysql_query("select * from authors;");

if($ath)

{

while($row = mysql_fetch_row($ath))

{

echo "<p>name: ".$row[1]."</p>";

echo "<p>url: ".$row[4]."</p>";

echo "<p>email: ".$row[3]."</p>";

}

}

else

{

echo "<p><b>Error: ".mysql_error()."</b><p>";

exit();

}

?>

Результат выполнения этого кода в точности совпадает с приведённым выше.

Интернет и базы данных. Часть 02. Нормализация базы данных.

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

Теория нормализации реляционных баз данных была разработана в конце 70-х годов 20 века. Согласно ей, выделяются шесть нормальных форм, пять из которых так и называются: первая, вторая, третья, четвертая, пятая нормальная форма, а также нормальная форма Бойса-Кодда, лежащая между третьей и четвертой.

База данных считается нормализованной, если ее таблицы (по крайней мере, большинство таблиц) представлены как минимум в третьей нормальной форме. Часто многие таблицы нормализуются до четвертой нормальной формы, иногда, наоборот, производится денормализация. Использования таблиц в пятой нормальной форме (вернее сказать, сознательного приведения их к пятой нормальной форме) в реальных базах данных я лично не встречал.

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

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

Первая нормальная форма

Первая нормальная форма:

· запрещает повторяющиеся столбцы (содержащие одинаковую по смыслу информацию)

· запрещает множественные столбцы (содержащие значения типа списка и т.п.)

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

Вторая нормальная форма

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

Третья нормальная форма

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

Нормальная форма Бойса-Кодда

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

Четвертая нормальная форма

Для приведения таблицы, находящейся в нормальной форме Бойса-Кодда, к четвертой нормальной форме необходимо устранить имеющиеся в ней многозначные зависимости. То есть обеспечить, чтобы вставка / удаление любой строки таблицы не требовала бы вставки / удаления / модификации других строк этой же таблицы.

Пятая нормальная форма

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

Такая вот теория... Разработаны специальные формальные математические методы нормализации таблиц реляционных баз данных. На практике же толковый проектировщик баз данных, детально познакомившись с предметной областью, как правило, достаточно быстро набросает структуру, в которой большинство таблиц находятся в четвертой нормальной форме:).

Краткие итоги. Зачем нужна нормализация

Главное, чего мы добьемся, проведя нормализацию базы данных - это устранение (или, по крайней мере, серьезное сокращение) избыточности, дублирования данных. Как следствие, значительно сокращается вероятность появления противоречивых данных, облегчается администрирование базы и обновление информации в ней, сокращается объем дискового пространства.

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

3. Задача проектирования базы данных методом нормализации

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

Введение.

Глава посвящена анализу проектирования базы данных частного предприятия. В качестве инструмента моделирования структуры данных использован метод нормализации. Этот метод считается фундаментальным и широко используется на практике.

Базы данных (БД) составляют в настоящее время основу компьютерного обеспечения информационных процессов, входящих практически во все сферы человеческой деятельности. Тематика СУБД поистине безгранична и многогранна.

Хотя многие считают, что реляционные СУБД, являясь наиболее распространенным современным аппаратом построения информационных систем, не представляют уже интереса в научном отношении, остается еще много не решенных или частично решенных проблем. Об этом свидетельствует поток статей, посвященных тематике чисто реляционных систем, а также активная деятельность компаний-производителей коммерческих реляционных систем, стремящихся улучшать свои продукты и придавать им новые качества. Продолжающая работа исследователей затрагивают вопросы оптимизации запросов,новых алгоритмов выполнения реляционных отношений, оптимизации структур данных и другие аспекты, непосредственно определяющие эффективность СУБД [1]

Решение вопроса оптимизации структур данных. Понятие «оптимизация структуры данных» обозначает спроектировать такую систему базы данных, структура данных которой должна быть предельно простой и понятной. А именно этот критерий является одним из самых важных критериев разработки коммерческих приложений (банковских систем, Интернет - магазинов, систем электронных платежей, и т.д.), поскольку позволяет программе занимать совсем малую память и выполнять запросы быстрее.

Постановка задачи.

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

1. Какие товары продает фирма, какова их цена, описание, и т.д.

...