Какие операторы позволяют модифицировать данные с помощью sql

Какие операторы позволяют модифицировать данные с помощью sql

Язык SQL, его структура, стандарты, история развития. Подмножество языка DML: операторы SELECT, INSERT, UPDATE, DELETE.
Доступ к данным осуществляется в виде запросов, которые формулируются на стандартном языке запросов. Сегодня для большинства СУБД таким языком является SQL.
Появление и развития этого языка как средства описания доступа к базе данных связано с созданием теории реляционных баз данных. Прообраз языка SQL возник в 1970 году в рамках научно-исследовательского проекта System/R (IBM). Ныне SQL —это стандарт интерфейса с реляционными СУБД.
SQL не является языком программирования в традиционном представлении. На нем пишутся не программы, а запросы к базе данных. Поэтому SQL —декларативный или непроцедурный язык. Это означает, что с его помощью можно сформулировать, что необходимо получить, но нельзя указать, как это следует сделать.
Первый международный стандарт языка SQL был принят в 1989 г. (SQL/89 или SQL1), в 1992 г. был принят стандарт языка SQL (SQL/92 или SQL2). В 1999 г. появился стандарт SQL3. В SQL3 введены новые типы данных, при этом предоставляется возможность задания сложных структурированных типов данных, которые в большей степени соответствуют объектной ориентации. Появились стандарты на события и триггеры, которые раньше не затрагивались в стандартах.
Язык SQL делится на подмножества.
) Язык определения данных (DDL — Data Definition Language) предоставляет пользователям средства указания типа данных и их структуры, а также средства задания ограничений для информации, хранимой в базе данных.
Операторы –CREATE, ALTER, DROP.
) Язык манипулирования данными (DML — Data Manipulation Language) позволяет вставлять, обновлять и извлекать информацию из базы данных.
Операторы –SELECT, INSERT, DELETE, UPDATE.
) Язык управления данными (DCL — Data Control Language) состоит из управляющих операторов.
Операторы –GRANT, REVOKE.
) Язык управления транзакциями.
Операторы –COMMIT, ROLLBACK, SAVEPOINT.

Запрос на языке SQL состоит из одного или нескольких операторов, следующих один за другим и разделенных точкой с запятой.
Каждый столбец в любой таблице хранит данные определенных типов. Различают базовые типы данных —строки символов фиксированной длины — CHAR(n), целые и вещественные числа — NUMERIC(n,m), DEC(n,m), INTEGER, SMALLINT, FLOAT(n), REAL, DOUBLE PRECISION , и дополнительные типы данных —строки символов переменной длины — VARCHAR(n), BIT VARYING(n), денежные единицы, дату и время –DATE, TIMESTAMP, INTERVAL, логические данные –BOOLEAN (два значения —»ИСТИНА» и «ЛОЖЬ»).
Подмножество языка DML: операторы SELECT, INSERT, UPDATE, DELETE.
Язык обработки данных DML позволяет добавлять (insert), изменять (update), удалять (delete) и выбирать (select) информацию в базе данных, т.е. предназначен для работы с информационным содержанием базы данных. Операторы языка DML представляют собой SQL-команды, позволяющие изменять и дополнять хранящуюся в базе данных информацию.

. Подмножество языка DDL: операторы CREATE, ALTER, DROP. Представления, их значение; обновляемые представления.
Язык определения данных DDL представляет собой подмножество команд языка SQL, предназначенное для создания и определения объектов базы данных. Определения объектов, созданные с помощью команд DDL, сохраняются в словаре базы данных.
Язык определения данных DDL обеспечивает:
— Создание объектов базы данных (CREATE)
— Удаление объектов базы данных (DROP)
— Изменение свойств объектов базы данных (ALTER)
Современные базы данных содержат различные типы объектов:
таблицы, индексы, представления, роли, синонимы, последовательности, кластеры, триггеры, процедуры, функции, пакеты, пользователи, профили и т.д.
Все эти объекты создаются, изменяются и удаляются с помощью операторов DDL.
Представления, их значение
Представление –объект базы данных, позволяющий получить определенную пользователем выборку данных из одной или нескольких таблиц. В отличие от таблицы, представление не содержит никаких данных, а лишь запрос на языке SQL, дающий возможность прочитать из базы данных необходимую информацию и представить ее в табличной форме.
Пользователь может не знать, работает он с представлением или с настоящей таблицей. Подобно таблицам, к представлениям можно применять операторы insert, update, delete и select.
Важно понимать принципы использования представлений. Их применение может оказаться необходимым в силу следующих причин:
) Представления обеспечивают дополнительный уровень безопасности базы данных. Например, можно создать общую таблицу со сведениями обо всех сотрудниках компании, но разрешить менеджерам компании получать информацию только об их подчиненных.
) Представления позволяют скрыть от пользователей сложность структуры хранимых данных.
) Представления позволяют использовать разумные названия отдельных столбцов.
) Представления обеспечивают гибкость при изменении формата одной или нескольких входящих в них таблиц.
Обновляемые представления
Если к представлению можно применить операторы обновления, то представление является обновляемым (updateble), иначе оно является читаемым (read-only).
Приведем критерии того, является ли представление обновляемым в SQL:
— оно базируется на одной таблице;
— оно должно включать первичный ключ таблицы;
— оно не должно включать полей, полученных в результате применения функций агрегирования;
— оно не может содержать спецификации DISTINCT;
— оно не должно использовать GROUP BY или HAVING;
— оно не должно использовать подзапросы;
— оно может быть определено на другом представлении, но это представление должно быть обновляемым;
— оно не может содержать константы, строки или выражения в списке выбираемых выходных полей;
— для INSERT оно должно включать поля из таблицы, которые имеют ограничения NOT NULL.

. Подмножество языка DCL: операторы GRANT, REVOKE. Системные привилегии, привилегии на объекты, роли.
Предоставление пользователям необходимых полномочий и лишение полномочий осуществляется с помощью операторов DCL: GRANT и REVOKE.
Оператор GRANT используется для открытия другой схеме доступа к привилегии, а оператор REVOKE — для запрещения доступа, разрешенного оператором GRANT. Оба оператора могут использоваться как для объектных, так и для системных привилегий.
Для объектных привилегий синтаксис оператора GRANT таков:
GRANT привилегия ON объект TO обладатель_привилегий [WITH GRANT OPTION];
где привилегия — это нужная привилегия,
объект — это объект, к которому разрешается доступ, а
обладатель_привилегий — пользователь, получающий привилегию.
Для системных привилегий синтаксис оператора GRANT таков:
GRANT привилегия TO обладатель_привилегий [WITH ADMIN OPTION];
где привилегия — это предоставляемая системная привилегия, обладатель_привилегий — пользователь, получающий привилегию.
Для объектных привилегий синтаксис оператора REVOKE таков:
REVOKE привилегия ON объект FROM обладатель_привилегий [CASCADE CONSTRAINTS];
где привилегия — это отменяемая привилегия,
объект — это объект, на который предоставлена привилегия,
обладатель_привилегий — пользователь, получающий эту привилегию.
Для системных привилегий синтаксис оператора REVOKE таков:
REVOKE привилегия FROM обладатель_привилегий;
где привилегия — это отменяемая системная привилегия,
обладатель_привилегий — пользователь, который более не будет ее иметь.
Примеры операторов GRANT, REVOKE
) GRANT select ON customers TO Adrian;
) GRANT select, insert ON orders TO Adrian, Diana;
) GRANT ALL ON customers TO Adrian;
) GRANT select ON orders TO PUBLIC;
) REVOKE insert ON orders FROM Adrian;
Роли
Для организаций, в которых работает множество пользователей, управление привилегиями является достаточно сложной задачей. Для ее упрощения можно использовать средство, называемое ролями. Роль (role) является совокупностью привилегии, как объектных, так и системных.
Примеры ролей:
) CREATE ROLE spaceadmin IDENTIFIED BY password
GRANT create session, alter session, restricted session, alter database,
create rollback segment, alter rollback segment, drop rollback segment,
create tablespace, alter tablespace, drop TO spaceadmin;
) CREATE ROLE backupadmin IDENTIFIED BY password
GRANT create session, alter session, restricted session, manage tablespace, backup any table TO backupadmin;
Объектные и системные привилегии
До сих пор предполагалось, что каждый пользователь базы данных может обращаться к объектам всех других пользователей базы данных и к информации, содержащейся в этих объектах. В действительности дело обстоит далеко не так. Сервер баз данных предоставляет администратору полный и систематический контроль над тем, что именно каждый отдельный пользователь может читать, модифицировать, стирать или изменять. В сочетании с использованием представлений данных можно полностью контролировать доступ к информации всех пользователей.
Права доступа используются для того, чтобы позволить одному пользователю работать с данными другого пользователя. После получения необходимых
полномочий обладатель прав доступа может работать с объектами, принадлежащими другому пользователю.
Существуют привилегии двух различных видов: объектные и системные.
Объектная привилегия (object privilege) разрешает выполнение определенной операции над конкретным объектом (например, над таблицей — SELECT, DELETE, INSERT, UPDATE, REFERENCES).
Системная привилегия (system privilege) разрешает выполнение операций над целым классом объектов.

Операторы модификации данных

Язык манипуляции данными (DML — Data Manipulation Language) помимо оператора SELECT, осуществляющего извлечение информации из базы данных, включает операторы, изменяющие состояние данных. Этими операторами являются:

Оператор INSERT

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

Как видно из представленного синтаксиса, список столбцов не является обязательным. В том случае, если он отсутствует, список вставляемых значений должен быть полный, т.е. обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку столбцов, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, каждое из этих значений должно быть того же типа (или приводиться к нему), что и тип, определенный для соответствующего столбца в операторе CREATE TABLE. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE:

Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:

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

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

Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два — NULL, а последний столбец — type — ‘PC’). Теперь мы могли бы написать:

В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию — ‘PC’. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL, запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL.

Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT:

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

Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (смотри синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде

Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK, ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отвергнута.

Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D все строки из таблицы Product, относящиеся к моделям персональных компьютеров (type = ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:

Использование в подзапросе символа «*» является в данном случае оправданным, т.к. порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы использовать список столбцов либо в операторе INSERT, либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов:

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

В данном случае в столбец type таблицы Product_D будет подставлено значение по умолчанию ‘PC’ для всех вставляемых строк.

Отметим, что при использовании подзапроса, содержащего предикат, будут вставлены только те строки, для которых значение предиката равно TRUE (не UNKNOWN!). Другими словами, если бы столбец type в таблице Product допускал бы NULL-значение, и это значение присутствовало бы в ряде строк, то эти строки не были бы вставлены в таблицу Product_D.

Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL. Так если нам требуется вставить несколько строк при помощи одного оператора INSERT, можно написать:

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

Вставка строк в таблицу, содержащую автоинкрементируемое поле

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

Ниже приводится пример создания таблицы с автоинкрементируемым столбцом (code) в MS SQL Server.

Автоинкрементируемое поле определяется посредством конструкции IDENTITY (1, 1). При этом первый параметр свойства IDENTITY (1) определяет, с какого значения начнется отсчет, а второй — какой шаг будет использоваться для приращения значения. Таким образом, в нашем примере первая вставленная запись будет иметь в столбце code значение 1, вторая — 2 и т.д.

Поскольку в поле code значение формируется автоматически, оператор

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

В результате выполнения этого оператора в таблицу Printer_Inc будет вставлена информация о модели 3111 цветного лазерного принтера, стоимость которого равна $2599. В поле code окажется значение, которое только случайно может оказаться равным 15. В большинстве случаев этого оказывается достаточно, т.к. значение автоинкрементируемого поля, как правило, не несет никакой информации; главное, чтобы оно было уникальным.

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

Поскольку стандарт языка SQL не предполагает наличия автоинкрементируемых полей, то соответственно не существует и единого подхода. Здесь мы покажем, как это реализуется в MS SQL Server. Оператор

отключает (значение ON) или включает (OFF) использование автоинкремента. Поэтому чтобы вставить строку со значением 15 в поле code, нужно написать

Обратите внимание, что список столбцов в этом случае является обязательным, т.е. мы не можем написать так:

ни, тем более, так

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

Важно отметить, что если значение 15 окажется максимальным в столбце code,то далее нумерация продолжится со значения 16. Естественно, если включить автоинкрементирование: SET IDENTITY_INSERT Printer_Inc OFF.

Наконец, рассмотрим пример вставки данных из таблицы Product в таблицу Product_Inc, сохранив значения в поле code:

По поводу автоинкрементируемых столбцов следует еще сказать следующее. Пусть последнее значение в поле code было равно 16, после чего строка с этим значением была удалена. Какое значение будет в этом столбце после вставки новой строки? Правильно, 17, т.к. последнее значение счетчика сохраняется, несмотря на удаление строки, его содержащей. Поэтому нумерация значений в результате удаления и добавления строк не будет последовательной. Это является еще одной причиной для вставки строки с заданным (пропущенным) значением в автоинкрементируемом столбце.

Основные операторы языка SQL

Прежде чем что-либо делать с данными, нужно создать таблицы, в которых эти данные будут храниться, научиться изменять структуру этих таблиц и удалять их, если потребуется. Для этого в языке SQL существуют операторы CREATE TABLE, ALTER TABLE и DROP TABLE.

1. Оператор CREATE TABLE

Оператор CREATE TABLE создает таблицу с заданным именем в текущей базе данных. Правила для допустимых имен таблицы приведены в документации. Если нет активной текущей базы данных или указанная таблица уже существует, то возникает ошибка выполнения команды.

В версии Mysql 3.22 и более поздних имя таблицы может быть указано как имя_базы_данных.имя_таблицы. Эта форма записи работает независимо от того, является ли указанная база данных текущей.

В версии Mysql 3.23 при создании таблицы можно использовать ключевое слово TEMPORARY. Временная таблица автоматически удаляется по завершении соединения, а ее имя действительно только в течение данного соединения. Это означает, что в двух разных соединениях могут использоваться временные таблицы с одинаковыми именами без конфликта друг с другом или с существующей таблицей с тем же именем (существующая таблица скрыта, пока не удалена временная таблица). В версии Mysql 4.0.2 для создания временных таблиц необходимо иметь привилегии CREATE TEMPORARY TABLES.

В версии Mysql 3.23 и более поздних можно использовать ключевые слова IF NOT EXISTS для того, чтобы не возникала ошибка, если указанная таблица уже существует. Следует учитывать, что при этом идентичность структур этих таблиц не проверяется.

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

1.1. Синтаксис

CREATE [ TEMPORARY ] TABLE [ IF NOT EXISTS ]
имя_таблицы [(определение_столбца. )]
[опции_таблицы] [select_выражение]

В выражении определение_столбца перечисляют, какие столбцы должны быть созданы в таблице. Каждый столбец таблицы может быть пустым (NULL), иметь значение по умолчанию, являться ключом или автоинкрементом. Кроме того, для каждого столбца обязательно указывается тип данных, которые будут в нем храниться. Если не указывается ни NULL, ни NOT NULL, то столбец интерпретируется так, как будто указано NULL. Если поле помечают как автоинкремент (AUTO_INCREMENT), то его значение автоматически увеличивается на единицу каждый раз, когда происходит добавление данных в таблицу и в это поле записывается пустое значение (NULL, т.е. ничего не
записывается) или 0. Автоинкремент в таблице может быть только один, и при этом он обязательно должен быть проиндексирован.

1.2. Последовательность

AUTO_INCREMENT начинается с 1. Наличие автоинкремента является одной из особенностей Mysql. Формально описание столбца (определение_столбца) выглядит так:

имя_столбца тип [ NOT NULL | NULL ]
[ DEFAULT значение_по_умолчанию]
[AUTO_INCREMENT][ PRIMARY KEY ]
[reference_definition]

• целый: INT[(length)] [UNSIGNED] [ZEROFILL]
• действительный: REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
• символьный: CHAR(length) [BINARY] и VARCHAR(length) [BINARY]
• дата и время: DATE и TIME
• для работы с большими объектами: BLOB
• текстовый: TEXT
• перечислимое множество: ENUM(value1,value2,value3. ) и SET(value1,value2,value3. )

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

Операторы манипулирования данными SQL

В операции манипулирования данными входят три операции: операция удаления записей — ей соответствует оператор DELETE, операция добавления или ввода новых записей — ей соответствует оператор INSERT и операция изменения (обновления записей) — ей соответствует оператор UPDATE. Рассмотрим каждый из операторов подробнее.

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

Оператор ввода данных INSERT имеет следующий синтаксис:

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

INSERT INTO BOOKS ( ISBN.TITL.AUTOR.COAUTOR.YEARIZD.PAGES)

VALUES («5-88782-290-2″,»Аппаратные средства IBM PC. Энциклопедия».

В этой книге только один автор, нет соавторов, но мы в списке столбцов задали столбец COAUTOR, поэтому мы должны были ввести соответствующее значение в разделе VALUES. Мы ввели пустую строку, потому что мы знаем точно, что нет соавтора. Мы могли бы ввести неопределенное значение NULL.

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

INSERT INTO BOOKS VALUES («5-88782-290-2»,

«Аппаратные средства IBM PC. Энциклопедия».»Гук М.»,»».2000.816)

Результаты работы обоих операторов одинаковые.

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

INSERT INTO BOOKS ( ISBN,TITL.AUTOR.YEARIZD,PAGES)

VALUES («5-88782-290-2″.»Аппаратные средства IBM PC. Энциклопедия».

Столбцу COAUTOR будет присвоено в этом случае значение NULL.

Какие столбцы должны быть заданы при вводе данных? Это определяется тем, как описаны эти столбцы при описании соответствующей таблицы, и будет рассмотрено более подробно при описании языка DDL (Data Definition Language) в главе 8. Здесь мы пока отметим, что если столбец или атрибут имеет признак обязательный (NOT NULL) при описании таблицы, то оператор INSERT должен обязательно содержать данные для ввода в каждую строку данного столбца. Поэтому если в таблице все столбцы обязательные, то каждая вводимая строка должна содержать полный перечень вводимых значений, а указание имен столбцов в этом случае необязательно. В противном случае, если имеется хотя бы один необязательный столбец и вы не вводите в него значений, задание списка имен столбцов — обязательно.

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

INSERT INTO EXEMPLAR (INV.ISBN.YES_NO.NUM_READER.DATE_IN, DATE_OUT)

VALUES (1872. «5-88782-290-2» .NO,344.GetDate() .DateAdcKd.GetDate() ,14))

И это означает, что мы выдали экземпляр книги с инвентарным номером 1872 читателю с номером читательского билете 344, отметив, что этот экземпляр не присутствует с этого момента в библиотеке, и определили дату выдачи книги как текущую дату (функция GetDateO), а дату возврата задали двумя неделями позднее, использовав при этом функцию DateAdd О, которая позволяет к одной дате добавить заданное количество интервалов даты и тем самым получить новое значение типа «дата». Мы добавили 14 дней к текущей дате.

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

INSERT INTO READER (NAME_READER. ADRESS. HOOM_PHONE. BIRTH_DAY)

SELECT (NAMEJTUDENT. ADRESS, HOOM_PHONE, BIRTH_DAY)

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

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

Синтаксис оператора DELETE следующий:

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

Если условия отбора не задаются, то из таблицы удаляются все строки, однако это не означает, что удаляется вся таблица. Исходная таблица остается, но она остается пустой, незаполненной.

Например, если нам надо удалить результаты прошедшей сессии, то мы можем удалить все строки из отношения R1 командой

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

DELETE FROM R2 WHERE ФИО = ‘Миронов А.В.’

В части WHERE может находиться встроенный запрос. Например, если нам надо исключить неуспевающих студентов, то по закону о высшем образовании неуспевающим считается студент, имеющий две и более задолженности по последней сессии. Тогда нам в условиях отбора надо найти студентов, имеющих либо две или более двоек, либо два и более несданных экзамена из числа тех, которые студент сдавал. Для поиска таких горе-студентов нам надо выбрать из отношения R1 все строки с оценкой 2 или с неопределенным значением, потом надо сгруппировать полученный результат по атрибуту ФИО и, подсчитав количество строк в каждой группе, которое соответствует количеству несданных экзаменов каждым студентом, отобрать те группы, у которых количество строк не менее двух. Теперь попробуем просто записать эту сложную конструкцию на SQL и убедимся, что этот сложный запрос записывается достаточно компактно.

DELETE FROM R2 WHERE R2 . ФИО IN (SELECT Rl.ФИО FROM Rl

WHERE Оценка = 2 OR Оценка IS NULL GROOP BY Rl.ФИО HAVING COUNT(*) >= 2

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

DELETE FROM R2 WHERE R2.ФИО IN

FROM (R2 NATURAL INNER JOIN R3 )

LEFT JOIN Rl USING ( ФИО. Дисциплина)

WHERE Оценка = 2 OR Оценка IS NULL

GROOP BY Rl.ФИО HAVING COUNT(*) >= 2

Все операции манипулирования данными связаны с понятием целостности базы данных, которое будет рассматриваться далее в главе 9. В настоящий момент мне бы хотелось отметить только то, что операции манипулирования данными не всегда выполнимы, даже если синтаксически они написаны правильно. Действительно, если мы бы захотели удалить какую-нибудь группу из отношения R3, то СУБД не позволила бы нам это сделать, так как в отношениях R1 и R2 есть строки, связанные с удаляемой строкой в отношении R3. Почему так делается, мы узнаем позднее, а пока просто примем к сведению, что не все операторы манипулирования выполнимы.

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

SET имя_столбца = новое_значение [WHERE условие_отбора]

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

Для решения ранее поставленной задачи нам необходимо выполнить следующую операцию

SET Rl.Оценка = 4

WHERE R1.ФИО = «Степанова К.Е.» AND R1.Дисциплина = «Базы данных»

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

В этом случае перевод на следующий курс можно выполнить следующей операцией обновления:

SET R4.Kypc = R4.Kypc + 1

И результат будет выглядеть следующим образом:

Операция модификации, так же как и операция удаления, может использовать сложные подзапросы. Расширим нашу базу еще одним отношением, которое будет содержать перечень студентов, получающих стипендию с указанием надбавки, которую они получают за отличную учебу. Исходно там могут находиться все студенты с указанием неопределенного размера стипендии. По мере анализа отношения R1 мы можем постепенно заменять неопределенные значения на конкретные размеры стипендии. Отношение R5 имеет вид:

Будем считать наличие трех пятерок по сессии признаком повышенной стипендии, + 50% к основной, наличие двух пятерок из сданных экзаменов и, отсутствие двоек и троек на сданных экзаменах — признаком повышения стипендии на 25%, наличие хотя бы одной двойки среди сданных экзаменов — признаком снятия или отсутствия стипендии вообще, то есть -100% надбавки. При отсутствии троек на сданных экзаменах назначим обычную стипендию с надбавкой 0%. Однако все эти изменения мы должны будем сделать отдельными операциями обновления.

Назначение повышенной стипендии:

SET R5.Стипендия = 50% WHERE R5.ФИО IN

WHERE Rl.Оценка =5

Назначение стипендии с надбавкой 25%:

UPDATE R5 SET R5.Стипендия = 25%

WHERE R5.ФИО IN (SELECT Rl.ФИО FROM R1

WHERE Rl.ФИО NOT IN (SELECT А.ФИО FROM Rl A

GROOP BY Rl.ФИО HAVING COUNT(*)>2 )

Назначение обычной стипендии:

SET R5.Стипендия = 0% WHERE R5.ФИО IN (SELECT Rl.ФИО FROM Rl

WHERE Rl.Оценка >=4 AND Р1.ФИО NOT IN (SELECT А.ФИО FROM Rl A

SET R5.Стипендия =-100% WHERE R5.ФИО IN

Почему мы в первом запросе на обновление не использовали дополнительную проверку на отсутствие двоек, троек и несданных экзаменов, как мы сделали это при назначении следующих видов стипендии? Просто мы учли особенности нашей предметной области: у нас в соответствии с исходными данными не только 3 экзамена. Но если мы можем предположить, что число экзаменов может быть произвольным и изменяться от семестра к семестру, то нам надо изменить наш запрос. Запрос — это некоторый алгоритм решения конкретной задачи, которую мы формулируем заранее на естественном языке. И оттого, что наша задача решается всего одним оператором языка SQL, она не становится примитивной. Мощность языка SQL и состоит в том, что он позволяет одним предложением сформулировать ответы на достаточно сложные запросы, для реализации которых на традиционных языках понадобилось бы писать большую программу. Итак, подумаем, как нам надо изменить текст нашего запроса на обновление для назначения повышенной стипендии при любом количестве сданных экзаменов. Прежде всего, каждая группа может иметь свое число экзаменов в сессию, это зависит от специальности и учебного плана, по которому учится данная группа. Поэтому для каждого студента нам надо знать, сколько экзаменов он должен был сдавать и сколько экзаменов он сдал на пять, и в том случае, когда эти два числа равны, мы можем назначить ему повышенную стипендию.

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

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

SELECT R3.Группа. Число_экзаменов = COUNT(*)

FROM R3 GROOP BY R3.Группа

Однако нам нужен не этот запрос, нам нужен запрос, в котором мы определяем для каждого студента количество экзаменов. Этот запрос мы должны строить по схеме встроенного запроса:

SELECT COUNT(*) FROM R3

WHERE R2.Группа = R3.Группа GROOP BY R3.Группа

А почему мы здесь в части FROM не написали имя второго отношения R2? Мы имя этого отношения укажем для связи с вышестоящим запросом, когда будем формировать запрос полностью. Теперь попробуем сформулировать полностью запрос. Нам надо объединить отношения R1 и R2 по атрибуту ФИО, нам надо знать группу, в которой учится каждый студент, далее надо выбрать все строки с оценкой 5 и сгруппировать их по фамилии студента, сосчитав количество строк в каждой группе, а выбирать мы будем те группы, в которых число строк в группе равно числу строк во встроенном запросе, рассмотренном ранее, при условии равенства количества строк в группе результату подзапроса, который выводит только одно число.

SELECT Rl.ФИО FROM R1.R2

WHERE Rl. ФИО = R2ФИО AND Rl.Оценка = 5

GROOP BY Rl.ФИО HAVING COUNK*) = (SELECT COUNT(*)

WHERE R2.Группа = R3.Группа

GROOP BY R3.Группа)

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

SET R5.Стипендия = 50%

WHERE Rl. ФИО = Р2.ФИО AND Rl.Оценка = 5

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

WHERE R2.Группа = R3.Группа GROOP BY R3.Группа))

Вот какой сложный запрос мы построили. Это ведь практически один оператор, а какую сложную задачу он решает. Действительно, мощность языка SQL иногда удивляет даже профессионалов, кажется невозможно построить один запрос для решения конкретной задачи, но когда начинаешь поэтапно его конструировать — все получается. Самое сложное — это сделать переход от словесной формулировки задачи к представлению ее в терминах нашего SQL, но этот процесс сродни процессу алгоритмизации при решении задач традиционного программирования, а он всегда был самым трудным, творческим и неформализуемым процессом. Недаром на заре развития программирования известный американский специалист по программированию Дональд Е. Кнут озаглавил свой многотомный капитальный труд по теории и практике программирования «Искусство программирования для ЭВМ» («The art of computer programming»).

Знаете ли Вы, что такое «усталость света»?
Усталость света, анг. tired light — это явление потери энергии квантом электромагнитного излучения при прохождении космических расстояний, то же самое, что эффект красного смещения спектра далеких галактик, обнаруженный Эдвином Хабблом в 1926 г.
На самом деле кванты света, проходя миллиарды световых лет, отдают свою энергию эфиру, «пустому пространству», так как он является реальной физической средой — носителем электромагнитных колебаний с ненулевой вязкостью или трением, и, следовательно, колебания в этой среде должны затухать с расходом энергии на трение. Трение это чрезвычайно мало, а потому эффект «старения света» или «красное смещение Хаббла» обнаруживается лишь на межгалактических расстояниях.
Таким образом, свет далеких звезд не суммируется со светом ближних. Далекие звезды становятся красными, а совсем далекие уходят в радиодиапазон и перестают быть видимыми вообще. Это реально наблюдаемое явление астрономии глубокого космоса. Подробнее читайте в FAQ по эфирной физике.