Конкурс:Дизайн Кухни

[шпунтик]

Операторы и команды MySQL

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

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

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

[шпунтик]

Создание таблиц. Оператор CREATE

Создать таблицу через SQL-запрос позволяет оператор CREATE. Его синтаксис:

Код

CREATE TABLE Имя_таблицы
(
Имя_поля1 Тип Модификатор
...
Имя_поляN Тип Модификатор
[первичный ключ]
[внешний ключ]
)

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

В качестве модификаторов можно использовать следующие значения:

* NOT NULL - поле не может содержать неопределенного значения (NULL), то есть поле должно быть явно инициализировано;
* PRIMARY KEY - поле будет первичным ключом (идентификатором записи), по которому можно однозначно идентифицировать запись;
* AUTO_INCREMENT - при вставке новой записи значение этого поля будет автоматически увеличено на единицу, поэтому в таблице не будет двух записей с одинаковым значением этого поля;
* DEFAULT - задает значение, которое будет использовано по умолчанию, если при вставке записи поле не будет инициализировано явно. Значение по умолчанию задается так:

| Имя_поля | Тип | DEFAULT | Значение, например |
| NO | INT | DEFAULT | 0 |
| NAME | INT | DEFAULT | 'Петров' |

Теперь создадим таблицы - "Товар", "Клиенты", "Заказы":

Код

CREATE TABLE CLIENTS
(
C_NO int NOT NULL,
FIO char(40) NOT NULL,
ADDR char(30) NOT NULL,
CITY char(15) NOT NULL,
PHONE char(11) NOT NULL
);

Таблица CLIENTS содержит поля C_NO (номер клиента), FIO (Фамилия, Имя, Отчество), ADDR (Адрес), CITY (Город) и PHONE (Телефон). Все эти поля не могут содержать пустого значения (NOT NULL).

Код

CREATE TABLE TOOLS
(
T_NO int NOT NULL,
DSEC char(40) NOT NULL,
PRICE double(9,2) NOT NULL,
QTY double(9,2) NOT NULL
);

Данная таблица будет содержать данные о товарах. Тип double(9,2) означает, что 9 знаков относим под целую чать, и два - под дробную. QTY - это колличество товара на складе.

Код

CREATE TABLE ORDERS
(
O_NO int NOT NULL,
DATE date NOT NULL,
C_NO int NOT NULL,
T_NO int NOT NULL,
QUANTITY double(9,2) NOT NULL,
AMOUNT double(9,2) NOT NULL
);

Эта таблица содержит сведения о заказах - номер заказа (O_NO), дату заказа (DATE), номер клиента (C_NO), номер товара (T_NO), колличество (QUANTITY) и сумму всего заказа AMOUNT (то есть AMOUNT = T_NO * TOOL_PRICE).

[шпунтик]

Добавление данных в таблицу. Оператор INSERT

Для добавления записей используется оператор INSERT:

Код

INSERT INTO Имя_таблицы [(Список полей)]
VALUES (Список констант);

После выполнения оператора INSERT будет создана новая запись, в качестве значений полей будут использованы соответствующие константы, указанные в списке VALUES.

Теперь добавим данные в наши таблице. Добавить данные можно с помощью оператора INSERT. Рассмотрим пример использование оператора INSERT:

Код

INSERT INTO CLIENTS
VALUES (1, 'Иванов И.И.', 'Вокзальная 3', 'Москва', '09599911100');

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

Код

INSERT INTO CLIENTS (FIO, ADDRESS, C_NO, PHONE, CITY)
VALUES ('Петров', 'Мира', '29', 2, '-', 'Екатеринбург');

С помощью INSERT мы можем добавлять данные и в определенные поля, например, C_NO и FIO:

Код

INSERT INTO CLIENTS (C_NO, FIO)
VALUES (1, 'Иванов');

Однако, в нашем случае сервер MySQL не выполнит такой запрос, поскольку все остальные поля равны NULL (пустое значение), а наша таблица не принимает пустые значения. Аналогично можно добавить данные в другие таблицы.

В качестве примера, добавим данные в таблицу TOOLS:

Код

INSERT INTO TOOLS
VALUES (1, 'Клавиатура ABC', 340.98);

Обратите внимание, что мы пока не указали первичные ключи таблицы, поэтому нам никто не мешает добавить в таблицу одинаковые записи. Добавить дату в поле DATE можно с помощью функции TO_DATE:

Код

INSERT INTO ORDERS
VALUES (1, TO_DATE('01/03/05, 'DD/MM/YY'),1,1,1,340.98);

Данная запись означает, что первого марта 2005 года Иванов И.И. (C_NO=1) заказал одну (QUANTITY=1) клавиатуру ABC (T_NO=1).

[шпунтик]

Обновление записей. Оператор UPDATE

Синтаксис оператора UPDATE, который используется для обновления записей, выглядит так:

Код

UPDATE Имя_таблицы
SET Поле1 = Значение1, ... , ПолеN = ЗначениеN
[WHERE Условие];

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

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

Код

UPDATE CLIENTS
SET CITY = 'Псков'
WHERE C_NO = 1;

Данный запрос нужно понимать так: найти запись, поле C_NO которой = 1 (это код клиента Иванова), и установить значение CITY равным "Псков".

[шпунтик]

Удаление записей. Оператор DELETE

Если нам необходимо удалить всех клиентов, номера которых превышают 5, то мы поступим следующим образом:

Код

DELETE FORM CLIENTS
WHERE C_NO > 5;

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

Код

DELETE FROM CLIENTS;

Если вторая часть оператора DELETE-WHERE не указана, занчит, действие оператора распространяется на все записи сразу.

[шпунтик]

Выбор записей. Оператор SELECT

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

Код

SELECT [DISTINCT|ALL] {*| [поле1 AS псевдоним] [,..., полеN AS псевдоним]}
FROM Имя_таблицы1 [,..., Имя_таблицыN]
[WHERE условие]
[GROUP BY список полей] [HAVING условие]
[ORDER BY список полей]

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

Код

SELECT * FROM CLIENTS;

В результате вы получите следующий ответ сервера:
| C_NO | FIO | ADDR | CITY | PHONE |
| 1 | Иванов И.И. | Вокзальная 3 | Москва | 09599911100 |
| 1 | Иванов И.И. | Вокзальная 3 | Москва | 09599911100 |
| 2 | Петров П.П. | Мира 29 | Екатеринбург | 3438920437 |

Обратите внимание на первые две записи - они одинаковые. Теоретически, добавление одинаковых записей возможно - ведь мы не указали первичный ключ таблицы. Если вы хотите исключить одинаковые записи из ответа сервера (но не из таблицы), используйте запрос:

Код

SELECT DISTINCT *
FROM CLIENTS;

Предположим, вы хотите вывести только фамилию и номер телефона клиента, тогда используйте следующий запрос:

Код

SELECT DISTINCT FIO, PHONE
FROM CLIENTS;

Если вам нужно вывести все товары, цена на которые превышает 800, то воспользуйтесь таким запросом:

Код

SELECT *
FROM TOOLS
WHERE PRICE > 800;

Вы можете использовать следующие операторы отношений: , =, , =.

Если в вашей таблице присутствуют несколько однофамильцев, то для вывода информации обо всех из них, используйте модификатор LIKE, например:

Код

SELECT *
FROM CLIENTS
WHERE FIO LIKE '%Иванов%';

Приведенный запрос можно причитать так: вывести информацию о клиентах, фамилия которых похожа на 'Иванов'.

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

Код

SELECT DISTINCT CLIENTS.FIO
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ORDERS.C_NO;

Оператор SELECT позволяет ипользовать вложенные запросы, однако MySQL их не поддерживает.

[шпунтик]

Внутренние функции MIN, MAX, AVG, SUM

При работе с оператором SELECT вам доступны несколько очень полезных внутренних функций MySQL, вычисляющих колличество элементов (COUNT), сумму элементов (SUM), максимальное и минимальное значения (MAX и MIN), а также среднее значение (AVG).

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

Код

SELECT COUNT(*)
FROM CLIENTS;

Код

SELECT MAX(PRICE)
FORM TOOLS;

Код

SELECT SUM(PRICE)
FROM TOOLS;

[шпунтик]

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

Оператор 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_MO этой таблице идентифицируется заказчик. Предположим, что в таблицу 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 ]
[ON UPDATE ]

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

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

* 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(). Данные функции подробнее рассмотрены далее.