Внешние объединения SQL

Стандарт SQL2 расширил понятие условного объединения. В стандарте SQL1 при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадали только сцепленные по заданным условиям кортежи исходных отношений, для которых эти условия были определены и истинны. Однако в действительности часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL1, которые стали называться внутренними.

В общем случае синтаксис части FROM в стандарте SQL2 выглядит следующим образом:

FROM <список исходных таблиц> |

< выражение естественного объединения > |

< выражение объединения >

< выражение перекрестного объединения > |

< выражение запроса на объединение >

<список исходных таблиц>::= <имя_таблицы_1>

[ имя синонима таблицы_1] [ ...]

[,<имя_таблицы_п>[ <имя синонима таблицы_n> ] ]

<выражение естественного объединениям:: =

<имя_таблицы_1> NATURAL { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN <имя_таблицы_2>

<выражение перекрестного объединениям: = <имя_таблицы_1> CROSS JOIN <имя_таблицы_2>

<выражение запроса на объединением:=

<имя_таблицы_1> UNION JOIN <имя_таблицы_2>

<выражение объединениям := <имя_таблицы_1> { INNER |

FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN {ON условие [USING (список столбцов)]} <имя_таблицы_2>

В этих определениях INNER — означает внутреннее объединение, LEFT — левое объединение, то есть в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL (неопределено). Ключевое слово RIGHT означает правое внешнее объединение, и в отличие от левого объединения в этом случае в результирующее отношение включаются все строки таблицы 2, а недостающие части из таблицы 1 дополняются неопределенными значениями, Ключевое слово FULL определяет полное внешнее объединение: и левое и правое. При полном внешнем объединении выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из таблицы 1, дополненные неопределенными значениями, и все строки из таблицы 2, также дополненные неопределёнными значениями.

Ключевое слово OUTER означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.

Рассмотрим примеры выполнения внешних объединений. Снова вернемся к БД «Сессия». Создадим отношение, в котором будут стоять все оценки, полученные всеми студентами по всем экзаменам, которые они должны были сдавать. Если студент не сдавал данного экзамена, то вместо оценки у него будет стоять неопределенное значение. Для этого выполним последовательно естественное внутреннее объединение таблиц R2 и R3 по атрибуту Группа, а полученное отношение соединим левым внешним естественным объединением с таблицей R1, используя столбцы ФИО и Дисциплина. При этом в стандарте разрешено использовать скобочную структуру, так как результат объединения может быть одним из аргументов в части FROM оператора SELECT.

SELECT Rl.ФИО, R1.Дисциплина. Rl.Оценка

FROM (R2 NATURAL INNER JOIN R3 ) LEFT JOIN Rl USING ( ФИО. Дисциплина)

Результат:

	ФИО	Дисциплина	Оценка
	Петров Ф. И.	Базы данных	5
	Сидоров К. А.	Базы данных	4
	Миронов Л. В.	Базы данных	2
	Степанова К. Е.	Базы данных	2
	Крылова Т. С.	Базы данных	5
	Владимиров В. А.	Базы данных	5
	Петров Ф. И.	Теория информации	Null
	Сидоров К. А.	Теория информации	4
	Миронов А. В.	Теория информации	Null
	Степанова К. Е.	Теория информации	2
	Крылова Т. С.	Теория информации	5
	Владимиров В. А.	Теория информации	Null
	Петров Ф. И.	Английский язык	5
	Сидоров К. А.	Английский язык	Null
	Миронов А. В.	Английский язык	Null
	Степанова К. Е.	Английский язык	Null
	Крылова Т. С.	Английский язык	Null
	Владимиров В. А.	Английский язык	4
	Трофимов П. А.	Сети и телекоммуникации	4
	Иванова Е. А.	Сети и телекоммуникации	5

	ФИО	Дисциплина	Оценка
	Уткина Н. В.	Сети и телекоммуникации	5
	Трофимов П. А.	Английский язык	5
	Иванова Е. А.	Английский язык	3
	Уткина Н. В.	Английский язык	Null

Рассмотрим еще один пример, для этого возьмем БД «Библиотека». Она состоит из трех отношений, имена атрибутов здесь набраны латинскими буквами, что является необходимым в большинстве коммерческих СУБД.

BOOKS(ISBN, TITL. AUTOR. COAUTOR. YEARJZD, PAGES)

READER(NUM_READER. NAME_READER, ADRESS. HOOM_PHONE. WORK_PHONE. BIRTH_DAY)

EXEMPLARE (INV, ISBN, YES_NO. NUM_READER. DATE_IN. DATE_DUT)

Здесь таблица BOOKS описывает все книги, присутствующие в библиотеке, она имеет следующие атрибуты:

• ISBN — уникальный шифр книги;
• TITL — название книги;
• AUTOR — фамилия автора;
• COAUTOR — фамилия соавтора;
• YEARIZD — год издания;
• PAGES — число страниц.

Таблица READER хранит сведения обо всех читателях библиотеки, и она содержит следующие атрибуты:

• NUM_READER — уникальный номер читательского билета;
• NAME_READER — фамилию и инициалы читателя;
• ADRESS — адрес читателя;
• HOOM_PHONE — номер домашнего телефона;
• WORK_PHONE — номер рабочего телефона;
• BIRTH_DAY — дату рождения читателя.

Таблица EXEMPLARE содержит сведения о текущем состоянии всех экземпляров всех книг. Она включает в себя следующие столбцы:

• INV — уникальный инвентарный номер экземпляра книги;
• ISBN — шифр книги, который определяет, какая это книга, и ссылается на сведения из первой таблицы;
• YES_NO — признак наличия или отсутствия в библиотеке данного экземпляра в текущий момент;
• NUM_READER — номер читательского билета, если книга выдана читателю, и Null в противном случае;
• DATE_IN — если книга у читателя, то это дата, когда она выдана читателю; a DATE_OUT — дата, когда читатель должен вернуть книгу в библиотеку.

Определим перечень книг у каждого читателя; если у читателя нет книг, то номер экземпляра книги равен NULL. Для выполнения этого поиска нам надо использовать левое внешнее объединение, то есть мы берем все строки из таблицы READER и соединяем со строками из таблицы EXEMPLARE, если во второй таблице нет строки с соответствующим номером читательского билета, то в строке результирующего отношения атрибут EXEMPLARE.INV будет иметь неопределенное значение NULL:

SELECT READER.NAME_READER, EXEMPLARE.INV

FROM READER RIGHT JOIN EXEMPLARE ON READER.NUM_READER=EXEMPLARE.NUM_READER

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

SELECT *

FROM ( BOOKS LEFT JOIN EXEMPLARE)

LEFT JOIN (READER NATURAL JOIN EXEMPLARE)

USING (ISBN)

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

Перекрестное объединение в трактовке стандарта SQL2 соответствует операции расширенного декартова произведения, то есть операции соединения двух таблиц, при которой каждая строка первой таблицы соединяется с каждой строкой второй таблицы.

Операция запроса па объединение эквивалентна операции теоретико-множественного объединения в алгебре. При этом требование эквивалентности схем исходных отношений сохраняется. Запрос на объединение выполняется по следующей схеме:

SELECT - запрос

UNION SELECT - запрос

UNION SELECT - запрос

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

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

SELECT READER. NAME_READER

FROM READER, EXEMPLARE.BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Идиот"

UNION

SELECT READER.NAME_READER

FROM READER, EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Преступление и наказание"

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

Запрос на объединение может объединять любое число исходных запросов.

Так, к предыдущему запросу можно добавить еще читателей, которые держат на руках книгу «Замок»:

UNION

SELECT READER. NAME_READER

FROM READER. EXEMPLARE,BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND .

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Замок"

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

SELECT - запрос

UNION ALL

SELECT - запрос

UNION ALL

SELECT - запрос

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

SELECT DISTINCT READER.NAME_READER

FROM READER. EXEMPLARE.BOOKS

WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND

EXEMPLRE.ISBN = BOOKS.ISBN AND

BOOKS.TITLE = "Идиот" OR

BOOKS.TITLE = "Преступление и наказание" OR

BOOKS.TITLE = "Замок"

Ни один из исходных запросов в операции UNION не должен содержать предложения упорядочения результата ORDER BY, однако результат объединения может быть упорядочен, для этого предложение ORDER BY с указанием списка столбцов упорядочения записывается после текста последнего исходного SELECT-запроса.

Работа с базами данных. Начало

Работа с базами данных. Начало

Автор: Дмитрий Лебедев

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

В качестве примера взят сервер баз данных MySQL (полагаю, что освоив его, вы без большого труда освоите и другие. Когда-нибудь я напишу и о них, если сам освою :).

Первый разговор пойдет о функциях PHP, используемых для работы с MySQL. Итак, начнём.

1. Администрирование базы данных

Способы администрирования БД в порядке убывания удобства:

• phpMyAdmin (весьма рекомендую!)
• Написать скрипт, который бы передёргивал базу (см. пример)
• mysql.exe в пакете mysql
• mysql_manager.exe (там, вроде, как-то можно, только на грани шаманства)

Особенно рекомендую первый способ. С ним не придётся изучать запросы ALTER TABLE, ADD COLUMN и т.п. Я их не знаю до сих пор. Тем более, что "такие вопросы, товарищ посол, с кондачка не решаются" - когда вам понадобится автоматически изменить структуру базы или таблицы? Пару слов о втором способе. Это так сказать обходная технология, которую я применял, не зная про phpMyAdmin и утилиту mysqldump. В скрипте пишутся команды, удаляющие базу и создающие её вновь. Когда-то помогало, но вообще это, ещё раз скажу, обходная технология, "подпорка".

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

2. Соединение с сервером БД

...осуществляется при помощи функции mysql_connect: $connect = mysql_connect(<хост>, <логин>, <пароль>); По умолчанию, на mysql-сервере в таблице пользователей есть пользователь root, который может иметь доступ только с localhost-а, то бишь с того же самого компьютера, где стоит сервер mysql. ВНИМАНИЕ! "Иметь доступ с localhost-а" значит, что доступ имеет ваш скрипт PHP, а вы можете обращаться к нему с любого другого компьютера.

Что происходит, когда мы вызываем функцию mysql_connect? С началом выполнения вашего скрипта, php выделяет в своей памяти место для информации о нём и его переменных. В информации о выполняемом скрипте хранится, в том числе, и информация о соединениях с базами данных. Переменная $connect - грубо говоря указатель на место, где данная информация хранится. Переменная эта точно такая же, как и остальные - если вы используете функции, то надо объявлять глобальные переменные, чтобы обратиться к ней.

Почему вообще используется переменная? Это на случай, если для работы вам необходимо использовать несколько серверов баз данных (или, например, для обеспечения бОльшей безопасности вы используете разные логины, у которых могут быть разные привилегии). В таких случаях в каждом запросе нужна определённость, по какому, так сказать, каналу идёт команда. Но если вы используете только одно соединение, указывать его в параметрах функций запросов (о них - ниже) не нужно - php находит первое (и в данном случае единственное) установленное соединение и использует его.

3. Запрос-выборка и обработка результатов

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

$result = mysql_db_query(string база данных, string запрос [, переменная соединения]);

или

$result = mysql_query(string запрос [, переменная соединения]);

ВНИМАНИЕ! Чтобы использовать функцию mysql_query, в которой база данных не указывается, надо предварительно выбрать используемую базу данных:

mysql_select_db(string база данных);

Теперь у нас есть переменная $result. Это указатель на результат выполнения запроса. Там есть сколько-то строк таблицы. Получить эти строки можно через функции mysql_fetch_row и mysql_fetch_array:

echo "<table>";
while ($row = mysql_fetch_array($result))
  echo "<tr><td>", $row["field1"], "</td><td>", $row["field2"], "</td></tr>";
echo "</table>";

Функция mysql_fetch_array выдаёт в указанную переменную (в данном случае $row) массив, индексы которого - имена полей (причём, если вы в списке полей запроса пишете table.field, то индекс массива будет field). mysql_fetch_row выдаёт массив, индексы которого - числа, начиная с 0.

Какой функцией лучше пользоваться? Если вы запрашиваете звёздочку, т.е. все поля таблицы, а выводить поля нужно в определённой последовательноси (когда, например, у таблицы рисуется шапка), лучше пользоваться mysql_fetch_array. Если вы запрашиваете одно-два-три поля, чётко зная их последовательность, можно делать mysql_fetch_row - это уменьшит объем кода программы.

4. Запросы-действия

Это команды DELETE и UPDATE. Подобные запросы - в "правах" такие же, как и SELECT, поэтому отправка команды серверу происходит тем же способом - mysql_query (mysql_db_query). Но в данном случае функция не возвращает результата:

$result = mysql_query("SELECT * FROM sometable");
но
mysql_query("DELETE FROM sometable WHERE id=...");

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

5. Обработка ошибок запросов

Сообщение о последней ошибке можно получить через функцию mysql_error:

echo "Ошибка базы данных. MySQL пишет:", mysql_error();

Если результат функции пишется в переменную, можно проверить её:

$result = mysql_query($request);
if (!$result)
  echo "Ошибка базы данных. MySQL пишет:", mysql_error();
else {
  echo "<table>";
  while ($row = mysql_fetch_array($result))
    echo "<tr><td>", $row["field1"], "</td><td>", $row["field2"], "</td></tr>";
  echo "</table>";
  };

Если в переменную не пишем, то так:

$request = "UPDATE (...)";
mysql_query($request);
if (!mysql_error())
  echo "Обновление данных прошло успешно!";
else echo "Ошибка базы данных. MySQL пишет:", mysql_error();

Если запрос генерируется автоматически, можно выводить и сам запрос (полезно создавать переменную, которая бы его содержала, и использовать её в качестве параметра функции).

Вложенные запросы SQL

Теперь вернемся к БД «Сессия» и рассмотрим на ее примере использование вложенных запросов.

С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст.

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

FROM Rl AS A, Rl AS В

ИЛИ

FROM Rl A. Rl В:

оба выражения эквивалентны и рассматриваются как применения оператора SELECT к двум экземплярам таблицы R1.

Например, покажем, как выглядят на SQL некоторые запросы к БД «Сессия»:

• Список тех, кто сдал все положенные экзамены.

SELECT ФИО

FROM Rl as a 

WHERE Оценка > 2

GROUP BY ФИО

HAVING COUNT(*) = (SELECT COUNT(*)

FROM R2.R3

WHERE R2.Группа=R3.Группа AND ФИОа.ФИО)

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

• Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

SЕLЕСТ ФИО 

FROM R2 a, R3

WHERE R2.Fpynna=R3.Группа AND Дисциплина = "БД" AND NOT EXISTS

(SELECT ФИО FROM Rl WHERE ФИО=а.ФИО AND Дисциплина = "БД")

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

Предикат NOT EXISTS обратно — истинен только тогда, когда подзапрос SubQuery пуст.

Обратите внимание, каким образом NOT EXISTS с вложенным запросом позволяет обойтись без операции разности отношений. Например, формулировка запроса со словом «все» может быть выполнена как бы с двойным отрицанием. Рассмотрим пример базы, которая моделирует поставку отдельных деталей отдельными поставщиками, она представлена одним отношением SP «Поставщики—детали» со схемой

SP (Номер_поставщика. номер_детали) Р (номер_детали. наименование)

Вот каким образом формулируется ответ на запрос: «Найти поставщиков, которые поставляют все детали».

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА FROM SP SP1 WHERE NOT EXISTS

(SELECT номер_детали

FROM P WHERE NOT EXISTS

(SELECT * FROM SP SP2

WHERE SР2.номер_поставщика=SР1.номер_поставщика AND

sр2.номер_детали = Р.номер_детали)):

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

SELECT DISTINCT Номер_поставщика

FROM SP

GROUP BY Номер_поставщика

HAVING CounKDISTINCT номер_детали) =

(SELECT Count( номер_детали)

FROM P)

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

Ключевое слово ANY, поставленное в любом предикате сравнения, означает, что предикат будет истинен, если хотя бы для одного значения из подзапроса предикат сравнения истинен. Ключевое слово ALL требует, чтобы предикат сравнения был бы истинен при сравнении со всеми строками подзапроса.

Например, найдем студентов, которые сдали все экзамены на оценку не ниже чем «хорошо». Работаем с той же базой «Сессия», но добавим к ней еще одно отношение R4, которое характеризует сдачу лабораторных работ в течение семестра:

R₁ = (ФИО, Дисциплина, Оценка);

R₂ = (ФИО, Группа);

R₃ = (Группы, Дисциплина )

R₄ = (ФИО, Дисциплина, Номер_лаб_раб, Оценка);

Select R1.ФИО From R1 Where 4 > = All (Select Rl.Оценка

From Rl as R11

Where R1.Фио = R11.Фио)

Рассмотрим еще один пример:

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

Select R1.Фио

From R1 Where R1.Оценка >= ANY (Select R4.Оценка

From R4

Where Rl.Дисциплина = R4. Дисциплина AND R1.Фио = R4.Фио)

Работа с MySQL. Новостная лента для странички

Работа с MySQL. Новостная лента для странички

Автор: Дмитрий Лебедев

Конкретный пример работы с MySQL - простейшая новостная лента

1. Создание таблицы

Итак, что нам нужно в новостной ленте? Сам текст новости, дата... ну и пусть ещё у новости будет заголовок. Берём phpMyAdmin, создаём базу данных (можно и не создавать). Жмём на её название в списке баз. В правом окне помимо (пустого) списка таблиц текущей БД есть формы действий. Находим форму "создать таблицу". Пишем имя таблицы news, и число полей - нам нужно 4. Вводим информацию о полях таблицы:

Имя поля  Тип данных   Пустое   Дополнительно
news_id   MEDIUMINT    NOT NULL AUTO_INCREMENT
(для этого поля надо поставить галочку "первичный")

ntext     TEXT         NOT NULL
ntitle    VARCHAR(255)
ndate     DATETIME     NOT NULL

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

В ntext будет храниться сам текст новости. Длина одной заметки может быть до 65 килобайт. Пометка NOT NULL означает, что поле не может быть пустым, а при попытке вставить в таблицу строку, в которой поле ntext будет пустым, MySQL сильно руганётся.

Заголовок новости - ntitle - пусть будет опциональным. Иногда заголовок просто неуместен, или придумать его сложно. Максимальная длина заголовка в нашей таблице будет 255 символов.

Поле ndate содержит дату и время новости. Естественно, что оно не может быть пустым - по нему идёт сортировка таблицы.

А вот так выглядит запрос на создание таблицы:

CREATE TABLE news (news_id MEDIUMINT NOT NULL AUTO_INCREMENT, ntext TEXT NOT NULL, ntitle VARCHAR(255), ndate DATETIME NOT NULL, PRIMARY KEY(news_id));

Программный код:

$request = "CREATE TABLE ... ";
mysql_query($request);
if (mysql_error())
  echo "Ошибка БД в запросе "$request". MySQL пишет: ". mysql_error();
else
  echo "Таблица создана";

2. Запись новостей

Создаётся новость путём отправки запроса точно так же, как и запроса на создание таблицы или выборки строк. Перед вставкой данных в запрос, их лучше обработать функцией addslashes, чтобы одинарные кавычки MySQL воспринял, как часть текста:

mysql_query ("INSERT INTO news (ntitle, ntext, ndate) VALUES ('". addslashes($ntitle). "', '". addslashes($ntext). "', NOW())");

В поле типа DATE вставляем текущее время функцией now. Если нужно вставить не текущее время, можно сделать так: ..., '". date("Y-m-d H-i-s", $date). "', ...

Этот формат - для типа DATETIME. Если тип данных DATE, то надо использовать "Y-m-d", и если TIME, соответственно "H-i-s". Переменная $date здесь содержит дату/время, определенные функцией mktime.

3. Вывод новостной ленты

Итак, мы уже соединились с сервером БД и выбрали базу (mysql_connect и mysql_select_db). Теперь осталось вывести записи. Отправляем запрос, проверяем на возможную ошибку и выводим строки.

$request = "SELECT ntext, ntitle, ndate FROM news";

Теперь бы только отсортировать данные... Изменим в запрос:

$request = "SELECT ntext, ntitle, ndate FROM news ORDER BY ndate DESC";

это означает, что сортировка идёт по полю ndate в порядке убывания (DESCending) даты (2000-12-05, 2000-12-04, ...). Сортировка по возрастанию - префикс ASC (ASCending), обычно сортировка по возрастанию установлена default и этот префикс использовать не надо.

Теперь выводим ленту:

$request = "SELECT ntext, ntitle, ndate FROM news ORDER BY ndate DESC";
$result = mysql_query($request);
if (!mysql_error()) {

// Цикл, вынимающий строку как массив с числовым индексом
  while ($row = mysql_fetch_row($result)) {
    print("<tr><td><h3>". $row[1]. "</h3>");
    print("<font size=-1>". $row[2]. "</font>");
    print("<p align=justify>". $row[0]. "</p>");
    };
  }

/* в случае ошибки БД программа выводит сообщение сервера (конечно, можно обойтись 
без такой проверки, но тогда пользователю посыплются ругательства PHP). */

else {
  print ("Ошибка БД в запросе "$request". MySQL пишет ". mysql_error());
};

/* если дальше предусмотрено выполнение каких-либо операций, лучше всего сразу 
очистить память */
mysql_free_result ($result);

Ещё одна полезная вещь: если это новостная лента, скажем, на главной странице сайта, нужно ограничить количество новостей (скаджм 10). И ещё надо, чтоб дата отображалась не как "2000-12-05 22-26-47", а "5.12.2000 22:46":

$request = "SELECT ntext, ntitle, date_format(ndate,'%e.%m.%Y %H:%i') as ndate1 FROM news ORDER BY ndate DESC LIMIT 10";

Ограничиваем количество новостей ровно десятью, и не надо никаких условий в цикле while. Функция date_format форматирует дату так, как нам надо (буквы - как в функции PHP date, а "e" - это число месяца без нуля в начале). Почему пишу "as ndate1", а не "as ndate"? Проверьте сами :) (подсказка: см. директиву ORDER BY в том же запросе).

Но, господа, это всё было только цветочки. Самое главное, чего пока нет - это возможности администратору работать с новостями, кликая мышкой, а не набирая SQL-запросы. Итак,

4. Механизм администрирования - теория

В первую очередь рекомендую заняться именно им, чтобы потом не было мучительно больно: новостная лента "почти" готова, только редактирование новости никак не написано, и приходится писать запросы "INSERT INTO news..." каждый раз вручную. Можно, конечно, и через хвалёный phpMyAdmin писать новости, но это тоже "не то".

В директории с сайтом создаём директорию admin (можно более оригинально - например chief, nachalnik...). Когда сайт будет выложен на WWW, положим в эту директорию .htaccess и поставим пароль, чтоб кто попало не лазил.

А теперь думаем, что же надо для нормальной работы с новостями. Форма для ввода новых новостей, обрабочтик формы. Так, а ещё? Список новостей с возможностью тыкнуть в неё, чтобы тут же отредактировать. Потом можно сделать галочки напротив заголовков новостей, чтоб можно было отметить неугодные и грохнуть их тут же (а лучше, чтоб было подтверждение "да/нет"). А потом сделать ввод даты ограниченным: вместо текстовых полей - раскрывающиеся списки, которые бы спасли от опечаток. Много... Но чтобы потом не было мучительно больно...

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

Ссылка по теме:

• Web database tutorial (каталог сайтов на PHP+MySQL)

Применение агрегатных функций и вложенных запросов в операторе выбора

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

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

Это делается с помощью агрегатных функций. Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций представлен в таблице 5.7.

Таблица 5.7. Агрегатные функции

	Функция	Результат
	COUNT	Количество строк или непустых значений полей, которые выбрал запрос
	SUM	Сумма всех выбранных значений данного поля
	AVG	Среднеарифметическое значение всех выбранных значений данного поля
	M1N	Наименьшее из всех выбранных значений данного поля
	MAX	Наибольшее из всех выоранных значений данного поля

	R1
		ФИО	Дисциплина	Оценка
	Группа 1	Петров Ф. И.	Базы данных	5
		Сидоров К. А.	Базы данных	4
		Миронов А. В.	Базы данных	2
		Степанова К. Е.	Базы данных	2
		Крылова Т. С.	Базы данных	5
		Владимиров В. А.	Базы данных	5
	Группа 2	Сидоров К. А.	Теория информации	4
		Степанова К. Е.	Теория информации	2
		Крылова Т. С.	Теория информации	5
		Миронов А. В.	Теория информации	Null
	Группа 3	Трофимов П. А.	Сети и телекоммуникации	4
		Иванова Е. А.	Сети и телекоммуникации	5
		Уткина Н. В.	Сети и телекоммуникации	5
	Группа 4	Владимиров В. А.	Английский язык	4
		Трофимов П. А.	Английский язык	5
		Иванова Е. А.	Английский язык	3
		Петров Ф. И.	Английский язык	5 i

Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.

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

SELECT R1.Дисциплина. СОUNТ(*)

FROM R1

GROUP BY R1 Дисциплина

Результат:

	Дисциплина	COUNT(*)
	Базы данных	6
	Теория информации	4
	Сети и телекоммуникации	3
	Английский язык	4

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

SELECT R1.Дисциплина. COUNT(*)

FROM R1 WHERE R1.

Оценка IS NOT NULL

GROUP BY Rl.Дисциплина

Получим результат:

	Дисциплина	COUNT(*)
	Базы данных	6
	Теория информации	3
	Сети и телекоммуникации	3
	Английский язык	4

В этом случае строка со студентом

	Миронов А, В.	Теория информации	Null

не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины «Теория информации» будет на 1 меньше.

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

Обратившись снова к базе данных «Сессия» (таблицы Rl, R2, R3), найдем количество успешно сданных экзаменов:

SELECT COUNT(*)

FROM Rl

WHERE Оценка > 2:

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

SELECT Rl.Дисциплина.

COUNT(DISTINCT R1.Оценка)

FROM R1

WHERE R1.Оценка IS NOT NULL

GROUP BY Rl.Дисциплина

Результат:

	Дисциплина	COUNT(DISTINCT R1 .Оценка)
	Базы данных	3
	Теория информации	3
	Сети и телекоммуникации	2
	Английский язык	3

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

SELECT R2.Группа. R1.Дисциплина. COUNT(*), АVР(Оценка)

FROM R1.R2

WHERE Rl.ФИО = R2.ФИО AND

Rl.Оценка IS NOT NULL AND

Rl.Оценка > 2

GROUP BY R2.Группа. Rl.Дисциплина

Результат:

	Дисциплина	COUNT(*)	АVР(Оценка)
	Базы данных	6	3.83
	Теория информации	3	3.67
	Сети и телекоммуникации	3	4.66
	Английский язык	4	4.25

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

Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT, так и в выражении условия обработки сформированных групп HAVING. В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций, могут быть использованы для вывода соответствующих результатов или для условия отбора групп.

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

SELECT R2.Группа

FROM R1.R2

WHERE Rl.ФИО = R2.ФИО AND

Rl.Оценка = 2

GROUP BY R2.Группа . R1.Дисциплина

HAVING count(*)> 1

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

F = <N, ФИО, Филиал, ДатаОткрытия, ДатаЗакрытия, Остаток>;

Q = (Филиал, Город);

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

Например, предположим, что мы хотим найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM(Остаток) из таблицы для каждого филиала. GROUP BY, однако, позволит поместить их все в одну команду:

SELECT Филиал, SUM

GROUP BY Филиал:

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

Предположим, что мы хотели бы увидеть только те суммарные значения остатков на счетах, которые превышают $5000. Чтобы увидеть суммарные остатки свыше $5000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же как предложение WHERE делает это для индивидуальных строк.

Правильной командой будет следующая:

SELECT Филиал, SUM(Остаток)

FROM F

GROUP BY Филиал

HAVING SUM(Остаток) > 5000;

Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT, где используется GROUP BY. Они должны иметь одно значение на группу вывода.

Следующая команда будет запрещена:

SELECT Филиал.SUM(Остаток)

FROM F GROUP BY Филиал

HAVING ДатаОткрытия = 27/12/1999; 

Поле ДатаОткрытия не может быть использовано в предложении HAVING, потому что оно может иметь больше чем одно значение на группу вывода. Чтобы избежать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос:

SELECT Филиал,SUM(Остаток)

FROM F

WHERE ДатаОткрытия = '27/12/1999'

GROUP BY Филиал;

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

Как и говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции — наиболее общие, но и поля, выбранные с помощью GROUP BY, также допустимы. Например, мы хотим увидеть суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и Урюпинске:

SELECT Филиал.SUМ(Остаток)

FROM F.Q

WHERE F.Филиал = Q.Филиал

GROUP BY Филиал

HAVING Филиал IN ("Санкт-Петербург". "Псков". "Урюпинск");

Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть TRUE. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.