Язык SQL

8.4 Язык манипулирования данными

Манипулирование данными выполняют инструкции:

• INSERT — добавление строк в таблицу;
• UPDATE — изменение строк в таблице;
• DELETE —удаление строк в таблице.

8.4.1 Вставка

Новая строка вводится в таблицу инструкцией INSERT, упрощённый синтаксис которой выглядит так:

INSERT INTO имя_таблицы_или_представления [(столбец    [,столбец]  ... )] [VALUES    (значение [, значение] ...)]

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

Пример инструкции INSERT: Предполагается, что таблица создана инструкцией

CREATE TABLE qq (c1 NUMBER(3)  PRIMARY KEY,   c2 CHAR(5))

Если таблица с именем qq уже существует, следует начала удалить её инструкцией

DROP TABLE qq

а затем вновь создать. Вставим строку со значениями "7" в столбце c1 и "A" в столбце c2:

INSERT INTO qq VALUES(7, 'A')

Если вам так удобно, читайте инструкцию как фразу на русском языке: "ВВЕСТИ В qq ЗНАЧЕНИЯ (7, 'A')"

Почему мы написали ЗНАЧЕНИЯ, а не СТРОКУ? Да потому, что вводится именно набор значений столбцов в строке, а не обязательно вся строка. Не введённые значения столбцов вызывают вставку NULL, если это допустимо.

Вставляем ещё одну строку:

INSERT INTO qq VALUES(1, 'В')

Обратите внимание на то, что в одинарные кавычки берутся только строчные значения, но не числовые. Проверим, действительно ли введены эти строки. Для этого инструкцией SELECT * FROM qq выберем все строки из таблицы qq. Символ "*" означает выбор всех столбцов. Результат выполнения инструкций вы можете увидеть в листинге 8.1.

USER>>CREATE TABLE qq (c1 NUMBER(3) PRIMARY KEY,c2 CHAR(5))
0 Rows Affected
-------------------------------------------------------
USER>>INSERT INTO qq VALUES(7,'A')
1 Row Affected
-------------------------------------------------------
USER>>INSERT INTO qq VALUES(1,'B')
1 Row Affected
-------------------------------------------------------
USER>>SELECT * FROM qq
c1 c2
7 A
1 B
2 Rows(s) Affected
-------------------------------------------------------

Замечание. Инструкцию SELECT мы рассмотрим позднее, а пока используем указанный простой вариант запроса.

Для ввода больших объёмов данных может быть полезен формат

INSERT INTO имя_таблицы_или_представления запрос

Пусть создана таблица vv со столбцами v1 типа NUMBER(5) и v2 типа CHAR(5) . В нее можно перенести все содержимое таблицы qq:

INSERT INTO vv SELECT * FROM qq

8.4.2 Обновление

Изменение существующих строк выполняет инструкция UPDATE:

UPDATE имя_таблицы_или_представления
SET   столбец=выражение  [,столбец=выражение] ...
[WHERE условие];

Пример инструкции UPDATE:

Заменим в строке (7, "A") значение 7 на 11. Строку выберем по условию с1=7:

UPDATE qq
SET c1=11 
WHERE c1=7 Проверим 

результат запросом:

SELECT * FROM qq

8.4.3 Удаление

Удаляются строки из таблицы инструкцией DELETE:

DELETE [FROM] имя_таблицы_или_представления [WHERE условие]

Если фраза WHERE отсутствует, будут удалены все строки.

Пример инструкции DELETE:

Удаляем первую строку (11, "A"):

DELETE FROM qq WHERE c2='A'

Тот же результат можно было получить опустив слово FROM:

DELETE qq WHERE c2='A'

Результат выполнения запросов вы можете увидеть в листинге 8.2. Обратите внимание на то, что во всех инструкциях мы были уверены, что выбираем единственную строку. Ошибка в выборе условия может привести к непредусмотренным изменениям данных, может быть, большого объёма.

USER>>UPDATE qq SET c1=11 WHERE c1=7
1 Row Affected
-------------------------------------------------------
USER>>SELECT * FROM qq
c1 c2
11 A
1 B
2 Rows(s) Affected
-------------------------------------------------------
USER>>DELETE FROM QQ WHERE c2='A'
1 Row Affected
-------------------------------------------------------
USER>>SELECT * FROM qq
c1 c2
1 B
1 Rows(s) Affected
-------------------------------------------------------

Заметьте, что для инструкции удаления таблицы используется слово DROP, а для удаления строк слово DELETE. Специально выбраны различные слова, чтобы не перепутать.

8.5 Запросы

Инструкцию "SELECT * FROM таблица" мы уже использовали без объяснения. Перейдём к систематическому изучению запросов.

8.5.1 Запросы в рамках исчисления на кортежах и соответствующие запросы в SQL

Останемся пока строго в рамках исчисления на кортежах. Инструкция SELECT (по-русски "ВЫБРАТЬ") должна состоять минимум из двух фраз SELECT и FROM (по-русски "ИЗ"), а именно:

SELECT DISTINCT
{*  |   { столбец|константа [псевдоним]},    ... } FROM    {таблица,    ... }

Фраза SELECT определяет список столбцов таблицы-результата. Если записаны псевдонимы, то используют их как имена.

Фраза FROM задает список таблиц, из которых производится выборка, а слово DISTINCT позволяет избежать дублирования строк, недопустимого в реляционной модели. Если список содержит более одной таблицы, то запрос строит декартово произведение. Поясним список фразы SELECT:

{*  |   {столбец|константа [псевдоним]},    ...   }

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

Во фразе FROM указывают имя таблицы или нескольких таблиц, из которых выбираются данные.

Самый сложный запрос, который можно записать в рамках исчисления на кортежах без использования вложенных подзапросов, имеет формат:

SELECT DISTINCT
{*   |   {столбец|константа  [псевдоним]},     ... } FROM таблица [, псевдоним_табл ] WHERE условие(я)

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

Добавленная фраза WHERE (по-русски "где") определяет условия, которым должны удовлетворять выбираемые кортежи. Для нескольких таблиц во фразе WHERE также записываются условия соединения таблиц.

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

Пример: Запрос в рамках исчисления на кортежах:

SELECT DISTINCT с1 FROM qq WHERE c2<>'A'

Из таблицы qq выбираются строки, в которых значения в столбце c2 отличны от 'A', и выдаётся столбец c1 (листинг 8.3).

USER>> << entering multiline statement mode >>
1>>SELECT DISTINCT c1
2>>FROM qq
3>>WHERE c2<>10
4>>go
c1
1
1 Rows(s) Affected
-------------------------------------------------------

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

8.5.2 Простейший запрос SQL

Расширим определение запроса, принятое в рамках исчисления на кор- тежах:

SELECT [DISTINCT]
{*|{столбец|константа|функция [псевдоним]}, ...} FROM     {таблица,    ... } WHERE условие(я) [GROUP BY список_столбцов]
[ORDER BY {столбец|выражение,    ...} [ASC|DESC]]

Символ "*", как вы помните, означает выбор всех столбцов. Подчеркнём, что этот символ может быть только единственным в списке SELECT. Слово DISTINCT теперь не обязательное, так как в SQL допустимы повторы строк.

Фраза ORDER BY ("упорядочить по") задает упорядочение строк и в запросе всегда стоит последней. По умолчанию упорядочение ведётся по возрастанию (ASCENDING — сокращённо ASC), можно задать упорядочение по убыванию (DESCENDING — сокращённо DESC). Если во фразе ORDER BY записано через запятую несколько столбцов, то вначале выполняется упорядочение по первому столбцу, полученные группы строк упорядочиваются по второму столбцу, и т. д. Как вы помните, в реляционной модели строки не упорядочены.

Функции во фразе SELECT могут быть одно- и многострочными. Последние ещё называют групповыми. Способ группирования определяется списком столбцов во фразе GROUP BY.

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

Учебная схема базы данных

Очень удобно, когда определена всем известная схема базы данных и никому не нужно объяснять её детали. К сожалению, такие схемы распространяются исключительно в рамках одной СУБД или учебника. Мы уже нарушили эту обременительную традицию, загрузив (смотри раздел 8.1) учебную схему scott, известную издавна тем, кто работает в СУБД Oracle. В ней использованы три таблицы: dept (от слова department — отдел) описывает отделы некоторой просто структурированной организации, emp (от слова employee — работник) содержит сведения о работниках, а в salgrade (salary grade — категории оплаты) описана система оплаты.

Ниже расписана структура этих таблиц (таблицы 8.6 и 8.6, 8.7). В верхней строке приводится название столбца, а внизу даны пояснения. Перед именами столбцов первичных ключей помещен признак ключа в виде символа "*". В имя столбца он не входит.

Содержимое учебной базы данных, созданной скриптом demobld.sql, показано в таблицах 8.8 и 8.9, 8.10.

Система оплаты заключается в том, что каждому работнику присваивается категория оплаты grade. В каждой такой категории определена раз и навсегда так называемая вилка, то есть минимальный оклад (losal) и максимальный (hisal). Допустимая для категории заработная плата выбирается из условия:

losal <= заработная_плата <= hisal для данной категории.

Изучим содержимое таблицы emp (таблица8.10). Заметим, что в отделе может не быть сотрудников, если отдел введен, но ещё не сформирован. Но в работающих отделах может быть один и более человек. Так из таблицы emp видно, что в отделе 10 работают Кларк, Кинг и Миллер, а в отделе 30 — Аллен и другие. Соединение таблиц emp и dept может быть реализовано через столбец deptno таблицы dept и столбец с тем же именем deptno в таблице emp. Считается, что все сотрудники, даже президент, относятся к какому-нибудь отделу. Правда, не очень умно подчинять президента начальнику отдела, который подчинён президенту? Ну, может быть, просто зарплата президента отнесена на расходы отдела 10? Потому его и включили в отдел 10.

Теперь проанализируем возможность соединения таблицы emp с собой. Естественно, что президент компании с многозначительной фамилией KING не имеет начальников. В поле mgr (руководитель) у него стоит неопределенное значение NULL. А вот менеджер Блейк (BLAKE) непосредственно подчинен президенту и табельный номер Кинга 7839 стоит в поле mgr во второй строке. В свою очередь, Аллен (ALLEN) подчиняется непосредственно Блейку чей табельный номер 7698 и стоит в столбце mgr второй строки. Значит, соединение таблицы emp с собой с помощью столбцов empno и mgr имеет смысл.

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

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

Выполнение однотабличного запроса

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

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

1. По фразе FROM выбирается указанная таблица.
2. Если имеется фраза WHERE, то в выбранной таблице отбираются строки, удовлетворяющие заданному в ней условию.
3. По списку фразы SELECT создаются столбцы таблицы результата, вычисляются все значения во всех отобранных строках (в списке SELECT могут быть функции).
4. Если имеется слово DISTINCT, то из таблицы результатов удаляются все повторяющиеся строки.
5. Если имеется фраза ORDER BY, то результаты отсортировывают по значениям записанных в ней выражений.

Если бы можно было записывать запрос как последовательность фраз FROM, WHERE, SELECT, ORDER BY, что, в отличие от английского, допускается русским языком, то не пришлось бы вспоминать порядок действий.

Сравнение запросов SQL и запросов в языке исчисления на кортежах

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

Перечислим некоторые расширения, частично упомянутые ранее:

• Многочисленные однострочные функции. Например, функция

  SUBSTR(ИМЯ, начальная_позиция, длина)
  

  которая вырезает часть строки, функция

  DUMP (имя  | строка)
  

  в СУБД Oracle, возвращающая внутреннее представление данных. Нестандартная функция

  DECODE(значение, зн1, рез1,
  зн2, рез2, ... результат_по_умолч)
  

  анализирует "значение" и если оно равно "зн1", то возвращает "рез1", если равно "зн2" возвращает "рез2", и т.д., если "зш" не найдено, вернётся "результат_по_умолчанию". Обратите внимание, DECODE — это включение IF, то есть процедурного указания, во фразу SELECT, по природе своей декларативную. Употребляется также процедурная конструкция CASE, играющая сходную роль (листинг 8.4).

  SELECT ename,job,sal,
  CASE job WHEN  'CLERK'   THEN 1.10*sal
  WHEN  'SALESMAN'   THEN 1.20*sal ELSE 1.05*sal
  FROM emp
  ename job sal Expression_4
  SMITH CLERK 800 880
  ALLEN SALESMAN 1600 1920
  WARD SALESMAN 1250 1500
  JONES MANAGER 2975 3123.75
  MARTIN SALESMAN 1250 1500
  BLAKE MANAGER 2850 2992.5
  CLARK MANAGER 2450 2572.5
  SCOTT ANALYST 3000 3150
  KING PRESIDENT 5000 5250
  TURNER SALESMAN 1500 1800
  ADAMS CLERK 1100 1210
  JAMES CLERK 950 1045
  FORD ANALYST 3000 3150
  MILLER CLERK 1300 1430
  
  

  Пример 8.4. Пример использования процедуры CASE

• Использование операторов IN и BETWEEN (по-русски "между") во фразе WHERE. Пример: Запрос в листинге 8.5 вернёт сведения о работниках с зарплатой 800, 1250 или 2000.

  SELECT ename, sal FROM emp
  WHERE sal IN  (800,   1250, 2000)
  ename sal
  SMITH 800 WARD 1250 MARTIN 1250
  

  Пример 8.5. Пример использования оператора IN

• Использование многострочных функций и управляющей их работой фразы группирования GROUP BY. Пример: Запрос в листинге 8.6 выдает суммарную заработную плату по отделам.

  SELECT deptno,  SUM(sal) FROM emp GROUP BY deptno 
  deptno Aggregate_2
  ename     sal
  10     8750
  20     10875
  30     9400
  

  Пример 8.6. Пример использования многострочных функций
• Использование строк с разделителями и списков в качестве значений, хранящихся в таблицах. С ними связаны регулярные выражения, то есть строки, предназначенные для поиска и обработки текста. В частности, регулярные выражения позволяют разбирать поля кодированных номеров счетов.
• Реализация рекурсивных запросов, эквивалентных заданию переменного (выбираемого) числа соединений таблиц.
• Использование коррелирующих подзапросов, которые срабатывают многократно, причем начинает работу основной запрос, затем подзапрос готовит строку-кандидата для основного запроса, который обрабатывает её и т. д.
• Сращивание многомерной и реляционной моделей данных за счёт конструкции MODEL.

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