Группировка записей - MySQL

Оператор SELECT позволяет группировать возвращаемые значения. Например, клиент Иванов (C_NO=1) несколько раз заказывал какой-то товар. Значит, его номер встречается в таблице ORDERS несколько раз. Другой клиент также мог сделать несколько заказов. Мы можем сгруппировать все записи по полю C_NO (номер клиента), а затем вывести сумму заказа каждого клиента.

SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ORDERS.C_NO
GROUP BY ORDERS.C_NO;

Группировку выполняет оператор GROUP BY, который является частью оператора SELECT. Оператор GROUP BY можно ограничить с помощью HAVING. Этот оператор используется для отбора строк, возвращаемых GROUP BY. HAVING можно считать аналогом WHERE, но только для GROUP BY:

HAVING <условие>

Например, нас интересуют только клиенты, которые заказали товаров на общую сумму, превышающую 1500:

SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ORDERS.C_NO
GROUP BY ORDERS.C_NO
HAVING TOTALSUM > 1500;

В этом запросе мы использовали псевдоним столбца TOTALSUM. В некоторых серверах SQL для определения псевдонима не нужно писать служебное слово AS, а некоторые требуют применения знака равенства:

SUM(ORDERS.AMOUNT) TOTALSUM или TOTALSUM = SUM(ORDERS.AMOUNT)

Сортировка записей

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

SELECT *
FROM CLIENTS
ORDER BY C_NO;

Ключи

Предположим, что кто-то добавил в таблицу CLIENTS запись:

1 Сидоров Свободы 7 Калининград 0113452103

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

ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_NO);

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

CREATE TABLE CLIENTS
(
C_NO    int      NOT NULL,
FIO     char(50) NOT NULL,
ADDR    char(55) NOT NULL,
CITY    char(20) NOT NULL,
PHONE   char(8)  NOT NULL,
PRIMARY KEY (C_NO);
);

Таблица ORDERS содержит сведения о заказах. По полю C_NO этой таблице идентифицируется заказчик. Предположим, что в таблицу ORDERS кто-то ввел значение, которого нет в таблице CLIENTS. Кто заказал товар? Нам нужно не допустить подобной ситуации, поэтому следует использовать подобный запрос:

ALTER TABLE ORDERS
ADD FOREIGN KEY(C_NO) REFERENCES CLIENTS;

Введенные в таблицу ORDERS номера клиентов C_NO должны существовать в таблице CLIENTS. Аналогично нужно добавить внешний ключ по полю T_NO. Эта возможность называется декларативной целостностью.

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

ALTER TABLE CLIENTS
ADD ZIP char(7) NULL;

Этот оператор добавляет в таблицу CLIENTS новое поле ZIP типа char. Обратите внимание, что вы не можете добавить новое поле со значением NOT NULL в таблицу, в которой уже есть данные. Например, если компания работает только с клиентами Москвы и Екатеринбурга, то целесообразно ввести список допустимых значений для таблицы CLIENTS:

ALTER TABLE CLIENTS
ADD CONSTRAINT INVALID_STATE SHECK (CITY IN ('Москва', 'Екатеринбург'));

Использование внешних ключей

Теперь углубимся в изучение SQL. Вы уже знаете, как добавлять первичный ключ, теперь добавим внешний ключ при создании таблицы. Внешние ключи используются для связи одной таблицы с другой. Например, в таблице CLIENTS у нас есть два клиента - Иванов (C_NO=1) и Петров (C_NO=2). Оператор в магазине при оформлении заказа ошибся и указал несуществующий номер, например, C_NO=3. Как мы потом сможем идентифицировать клиента? Для решения такой проблемы и существуют внешние ключи:

CREATE TABLE T
(
/* Описание полей таблицы */
FOREING KEY KEY_NAME (LIST)
REFERENCES ANOTHER_TABLE [(LIST2)]
[ON DELETE OPTION]
[ON UPDATE OPTION]
);

Здесь:

• KEY_NAME - Имя ключа. Имя не является обязательным, но рекомендуется всегда указывать имя ключа - если вы не укажите имя ключа, вы потом не сможете его удалить;
• LIST - это список полей, входящих во внешний ключ. Список разделяется запятыми;
• ANOTHER_TABLE - это другая таблица, по которой устанавливается не внешний ключ, а необязательный элемент;
• LIST2 - это список полей этой таблицы. Типы полей в списке LIST должны совпадать с типами полей в списке LIST2.

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

FOREIGN KEY KEY_NAME (NO, NAME)
REFERENCES ANOTHER_TABLE (NAME, NO)

Это определение некорректно, потому что типы полей NO и NAME не совпадают. Нужно использовать такое определение:

FOREIGN KEY KEY_NAME (NO, NAME)
REFERENCES ANOTHER_TABLE (NO, NAME)
[ON DELETE <OPTION>]
[ON UPDATE <OPTION>]

Если поля имеют одинаковае имена, как в нашем случае, список LIST2 лучше вообще не указывать.

Необязательные параметры ON DELETE <OPTION> и ON UPDATE <OPTION> определяют действие по обновлению информации в базе данных, при удалении информации из таблицы и при ее обновлении. А действия могут быть следующими:

• CASCADE - удаление или обновление значений везде, где оно встречается. Например, у нас есть таблица клиентов и заказов. Иы хотим удалить запись клиента с номером C_NO=1. Из таблицы заказов будут удалены сведения обо всех заказах, сделанных клиентом;
• NOACTION - вы не сможете удалить информацию из таблицы клиентов до тех пор, пока вы не удалите все заказы, сделанные этим клиентом. То есть действие NOACTION запрещает удалять запись из основной таблицы, если она используется в дочерней таблице;
• SETNULL - все значения в дочерней таблице будут заменены на NULL (если значения NULL допускаются);
• С помощью параметра SET_DEFAULT вы можете указать значение по умолчанию. Например, если вы укажите SET_DEFAULT 1, то при удалении клиента с любым номером его заказы бубудт приписываться клиенту с номером 1, который есть в таблице CLIENTS.

Удаление полей и таблиц. Оператор DROP

Стандартом SQL не предусмотрено удаление столбцов, однако в MySQL мы это можем сделать:

ALTER TABLE CLIENTS
DROP ZIP;

А удалить таблицу еще проще:

DROP ORDERS;

Отключение от СУБД

Используя запрос DISCONNECT можно отключиться от используемой базы данных, а затем, используя запрос CONNECT, подключиться к другой базе данных. В некоторых серверах SQL запрос DISCONNECT не работает, а вместо CONNECT применяется запрос USE.

При использовании PHP нет необходимости использовать данные запросы, поскольку для отключения от сервера MySQL используется функция mysql_close(), а для подключения к серверу MySQL используется функция mysql_connect(). Данные функции подробнее рассмотрены.

Загрузка...