5.3. Основные sql-операторы для доступа и модификации данных

Итак, структура базы данных создана. Надо как-то начинать работать с базой данных - заносить новые данные, извлекать существующие и т.д. Существует четыре основных оператора манипулирования данными - SELECT, INSERT, UPDATE и DELETE. Примеры, которые быдут сопровождать рассказ об этих операторах будут основываться на предположении, что в нашей текущей базе данных есть таблицы companies и items (см. параграф 5). В таблице companies хранится информация о производителях товаров, а в таблице items - о самих товарах.

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

Оператор INSERT вставляет в таблицу новую запись:

INSERT INTO <имя таблицы>(<поле1>, <поле2>, ...) VALUES (<значение1>, <значние2>, ...)

После имени таблицы в скобках надо указать те поля, которым мы хотим присвоить некоторе значение явно. После ключевого слова VALUES в скобках указан список значений для перечисленных полей. Число значений в этом списке должно соответствовать числу указанных полей. Полям, не перечисленным в списке (за исключением поля типа SERIAL) присваивается значение NULL.

Полю типа SERIAL, если его нет в списке или его значением указано 0, присваивается новое уникальное значение. Если для поля типа SERIAL указано отличное от нуля значение, то СУБД использует указанное значение.

Например, последовательность операторов

INSERT INTO companies (name) VALUES ("АО Рога и Копыта")

INSERT INTO companies (name, address) VALUES ("ТОО Добро пожаловать", "Энск, 5-е авеню")

INSERT INTO companies (company_id, name) VALUES (157, "АОЗТ Сделай Сам")

наполнит таблицу companies следующим содержанием

------------------T----------------------T-----------------¬ ¦ company_id ¦ name ¦ address ¦ +-----------------+----------------------+-----------------+ ¦ 1 ¦ АО Рога и Копыта ¦ NULL ¦ ¦ 2 ¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦ ¦ 157 ¦ АОЗТ Сделай Сам ¦ NULL ¦ L-----------------+----------------------+-----------------+

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

USER - имя пользователя, который выполняет этот SQL-оператор;

TODAY - дату выполнения этого оператора;

CURRENT - момент времени, когда выполняется этот оператор.

Например, если некоторая таблица под названием 'protocol' содержит описание некоторых действий и, в частности, поля when и who, указывающие когда и кто выполнил это действие, занесение новой записи в эту таблицу будет выглядеть так:

INSERT INTO protocol (who, when, ...) VALUES (USER, CURRENT YEAR TO MINUTE, ...)

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

UPDATE <имя таблицы> SET <имя поля>=<значение> [,....] [WHERE <условие>]

или

UPDATE <имя таблицы> SET (<имя поля1>, <имя поля2>, ...) = (<значение1>, <значение2>, ...) [WHERE <условие>]

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

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

В качестве условия могут использоваться логические выражения над константами и полями. В логических выражениях допускается испльзовать операции сравнения >, <, >=, <=, =, <>, !=. Для проверки поля на значение NULL используются логические операции IS NULL или IS NOT NULL. Отдельные логические операции могут быть соединены связками AND, OR, NOT и сгруппированы с помощью скобок. Примеры правильных условий:

name IS NULL

price > 200 OR name = "кеды"

(name IS NULL AND address IS NULL) OR (name = "АО Рога и Копыта")

Использование в SQL логических связок и операций сравнения аналогично логическим выражениям в обычных языках программирования. Представляет интерес использование в операциях сравнения и в логических связках значения NULL (неопределено). Если NULL появляется в операциях сравнения (<, >, <= и т.д.), то результатом этой операции тоже будет NULL. Если применить к NULL операцию отрицания (NOT), то снова получим NULL. Логическое умножение (AND) значений NULL и "ложь" дает "ложь", а NULL и "истина" - NULL. Логическое сложение (OR) значений NULL и "ложь" дает в результате NULL, а NULL и "истина" - "истину".

Например, если на момент вставки записи с информацией о фирме "АО Рога и Копыта" мы не знали ее адреса, то записать ее адрес после можно оператором:

UPDATE companies SET address = "Одесса, п/я 13" WHERE name = "АО Рога и Копыта"

Далее, если мы хотим поднять минимальную цену на товары до 1000, следует выполнить оператор:

UPDATE items SET price = 1000 WHERE price < 1000

Теперь, немного забегая вперед (мы еще не рассматривали оператор SELECT), напишем оператор UPDATE, который увеличивает в два раза цену на все товары, поставляемые фирмой "АОЗТ Сделай Сам":

UPDATE items SET price = price*2 WHERE company = (SELECT company_id FROM companies WHERE name = "АОЗТ Сделай Сам")

Это пример двойного запроса, то есть запроса в запросе. Вначале ищется идентификатор фирмы по ее имени (оператор SELECT), а затем обновляется поле 'price' для всех товаров, поставляемых данной фирмой.

Для того, что бы удалить ненужные записи в таблице, существует оператор DELETE:

DELETE FROM <имя таблицы> [WHERE <условие>]

Использование ключевого слово WHERE, задающего условие на записи, подлежащие удалению, аналогично его использованию в операторе UPDATE. Точно так же, если условие не задано, то удалены будут все записи из таблицы.

Предположим, фирма "АОЗТ Сделай Сам" разорилась, больше не поставляет нам ни одного товара, и мы хотим удалить ее из списка поставщиков. Это может быть сделано оператором:

DELETE FROM companies WHERE name = "АОЗТ Сделай Сам"

Теперь рассмотрим оператор выборки SELECT. На его долю приходится, наверное, более 2/3 всех SQL-операторов используемых в программах. Оператор SELECT выбирает из одной или нескольких таблиц множество значений, которое и является результатом его работы. К сложным, многотабличным вариантам этого оператора мы вернемся позже.

Синтаксис оператора SELECT (сильно упрощенный вариант):

SELECT <имя поля> [,...] FROM <имя таблицы> [WHERE <условие>]

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

Примеры:

SELECT company_id, name, address FROM companies

- результатом этого запроса будет вся таблица 'companies'.

SELECT company_id FROM companies WHERE name = "АО Рога и Копыта"

- этот запрос возвращает одно единственное значение (1), а именно уникальный идентификатор фирмы "АО Рога и Копыта".

SELECT name FROM companies

- возвращает имена всех фирм-поставщиков, имеющихся в нашей базе данных и занесенных в таблицу 'companies'.

SELECT name, price FROM items WHERE company = (SELECT company_id FROM companies WHERE name = "АО Рога и Копыта")

- возвращает все названия товаров и их цены, поставляемые фирмой "АО Рога и Копыта".

SELECT name FROM companies WHERE address IS NULL

- возвращает названия тех фирм, у которых неизвестен адрес.