Структура языка SQL

Если столбец допускает NULL-значение, то его можно указать в явном  виде. Кроме того, можно заменить имеющееся значение на значение по умолчанию (DEFAULT) для данного столбца.

Ссылка на "выражение" может  относиться к текущим значениям  в изменяемой таблице. Например, мы можем уменьшить все цены ПК-блокнотов на 10 процентов с помощью следующего оператора:

UPDATE Laptop SET price=price*0.9

Разрешается также значения одних  столбцов присваивать другим столбцам. Пусть, например, требуется заменить жесткие диски менее 10 Гб в ПК-блокнотах. При этом емкость новых дисков должна составлять половину объема RAM, имеющейся в данных устройствах. Эту задачу можно решить следующим образом:

UPDATE Laptop SET hd=ram/2 WHERE hd

Естественно, типы данных столбцов hd и ram должны быть совместимы. Для приведения типов может использоваться выражение cast [4, с.3].

Если требуется изменять данные в зависимости от содержимого  некоторого столбца, можно воспользоваться  выражением case [4, с.3].

Если, скажем, нужно поставить жесткие диски объемом 20 Гб на ПК-блокноты с памятью менее 128 Мб и 40 гигабайтные - на остальные ПК-блокноты, то можно написать такой запрос:

UPDATE Laptop    

SET hd = CASE WHEN ram<128 THEN 20 ELSE 40 END

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

UPDATE Laptop   

SET speed = (SELECT MAX(speed) FROM Laptop)

Необходимо сказать несколько  слов об автоинкрементируемых столбцах. Если столбец code в таблице Laptop определен как IDENTITY(1,1), то поступают следующим образом. Сначала необходимо вставить нужную строку, используя SET IDENTITY_INSERT, после чего удалить старую строку:

SET IDENTITY_INSERT Laptop ON

INSERT INTO Laptop_ID(code, model, speed, ram, hd, price, screen)    

SELECT 5, model, speed, ram, hd, price, screen    

FROM Laptop_ID WHERE code=4

DELETE FROM Laptop_ID WHERE code=4

Разумеется, другой строки со значением code=5 в таблице быть не должно.

В Transact-SQL оператор UPDATE расширяет стандарт за счет использования необязательного предложения FROM. В этом предложении специфицируется таблица, обеспечивающая критерий для операции обновления. Дополнительную гибкость здесь дает использование операций соединения таблиц.

Пример. Пусть требуется указать "No PC" (нет ПК) в столбце type для тех моделей ПК из таблицы Product, для которых нет соответствующих строк в таблице PC. Решение посредством соединения таблиц можно записать так:

UPDATE Product    

SET type='No PC'    

FROM Product pr LEFT JOIN PC ON pr.model=pc.model    

WHERE type='pc' AND pc.model IS NULL

Здесь используется внешнее соединение, в результате чего столбец pc.model для моделей ПК, отсутствующих в таблице PC, будет содержать NULL-значение, что и используется для идентификации подлежащих обновлению строк. Естественно, эта задача имеет решение и в "стандартном" исполнении:

UPDATE Product    

SET type='No PC'    

WHERE type='pc' and model NOT IN (SELECT model FROM PC)

3) Оператор DELETE

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

DELETE FROM [WHERE ];

Если предложение WHERE отсутствует, удаляются все строки из таблицы  или представления (представление  должно быть обновляемым). Более быстро эту операцию (удаление всех строк  из таблицы) в Transact-SQL можно также выполнить с помощью команды

TRUNCATE TABLE

Однако есть ряд отличий в  реализации команды TRUNCATE TABLE по сравнению  с использованием оператора DELETE, которые  следует иметь в виду:

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

2. Не отрабатывают триггеры. Как  следствие, эта команда неприменима,  если на данную таблицу имеется  ссылка по внешнему ключу. 

3. Значение счетчика (IDENTITY) сбрасывается в начальное значение.

Пример. Требуется удалить из таблицы Laptop все ПК-блокноты с размером экрана менее 12 дюймов.

DELETE FROM Laptop    

WHERE screen

Все блокноты можно удалить с  помощью оператора

DELETE FROM Laptop

Или

TRUNCATE TABLE Laptop

Transact-SQL расширяет синтаксис оператора  DELETE, вводя дополнительное предложение  FROM

4) FROM.

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

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

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

Используя стандартный синтаксис, эту задачу можно решить следующим  запросом:

DELETE FROM Product    

WHERE type='pc' AND model NOT IN (SELECT model FROM PC)

Заметим, что предикат type='pc' необходим здесь, чтобы не были удалены также модели принтеров и ПК-блокнотов.

Эту же задачу можно решить с помощью  дополнительного предложения FROM следующим  образом:

DELETE FROM Product    

FROM Product pr LEFT JOIN PC ON pr.model=pc.model    

WHERE type='pc' AND pc.model IS NULL

Здесь используется внешнее соединение, в результате чего столбец pc.model для моделей ПК, отсутствующих в таблице PC, будет содержать NULL-значение, что и используется для идентификации подлежащих удалению строк.

2.3 Транзакции в SQL

 

Под SQL-транзакцией (иногда называемой просто транзакцией) понимается последовательность выполнения операторов SQL, являющаяся атомарной по отношению  к восстановлению. Эти операции выполняются  одной или более единицами  компиляции и модулями или путем  прямого вызова SQL. От реализации зависит, могут ли в одной транзакции выполняться динамические и/или статические операторы выборки и манипулирования данными и динамические и/или статические операторы определения и манипулирования схемой.

Каждый модуль или прямой вызов SQL, инициирующие выполнение оператора, ассоциируются с транзакцией. SQL-транзакция начинается при выполнении процедуры из некоторого модуля или прямого вызова оператора SQL вне активной транзакции. Транзакция завершается при выполнении операторов COMMIT или ROLLBACK. Если SQL-транзакция завершается успешным выполнением оператора COMMIT, то все изменения, произведенные ею над данными и/или схемой становятся постоянно хранимыми и доступными всем параллельно выполняющимся или образуемым впоследствии транзакциям. Если транзакция завершается оператором ROLLBACK или если выполнение оператора COMMIT оказывается неуспешным, то все изменения, произведенные транзакцией над данными и/или схемой, ликвидируются.

У каждой SQL-транзакции имеется  режим доступа - "только чтение" или "чтение и запись". Режим доступа может быть явно установлен оператором SETTRANSACTION; по умолчанию он устанавливается в "чтение-запись". Термин "только чтение" применяется только к постоянно хранимым базовым и представляемым таблицам.

2.4 Защита данных

 

К операторам защиты данных относят операторы авторизации  и привилегий. Синтаксис команд GRANT и REVOKE

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]

    ON {tbl_name | * | *.* | db_name.*}

    TO user_name [IDENTIFIED BY [PASSWORD] 'password']

        [, user_name [IDENTIFIED BY 'password'] ...]

    [REQUIRE

        [{SSL| X509}]

        [CIPHER cipher [AND]]

        [ISSUER issuer [AND]]

        [SUBJECT subject]]

    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |

                          MAX_UPDATES_PER_HOUR # |

                          MAX_CONNECTIONS_PER_HOUR #]]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]

    ON {tbl_name | * | *.* | db_name.*}

    FROM user_name [, user_name ...]

Команды GRANT и REVOKE позволяют системным администраторам создавать пользователей MySQL, а также предоставлять права пользователям или лишать их прав на четырех уровнях привилегий:

Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице mysql.user.

Привилегии базы данных применяются ко всем таблицам указанной  базы данных. Эти привилегии хранятся в таблицах mysql.db и mysql.host.

Привилегии таблицы  применяются ко всем столбцам указанной  таблицы. Эти привилегии хранятся в таблице mysql.tables_priv.

Привилегии столбца  применяются к отдельным столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql.columns_priv.

Ниже приведен список возможных значений параметра priv_type для операторов GRANT и REVOKE:

ALL [PRIVILEGES] - Задает все простые привилегии, кроме WITH GRANT OPTION

ALTER - Разрешает использование ALTER TABLE

CREATE - Разрешает использование  CREATE TABLE

CREATE TEMPORARY TABLES - Разрешает использование CREATE TEMPORARY TABLE

DELETE - Разрешает использование DELETE

DROP - Разрешает использование  DROP TABLE.

EXECUTE - Разрешает пользователю запускать  хранимые процедуры 

FILE - Разрешает использование SELECT ... INTO OUTFILE и LOAD DATA INFILE

INDEX - Разрешает использование CREATE INDEX and DROP INDEX

LOCK TABLES - Разрешает использование  LOCK TABLES на таблицах, для которых  есть привилегия SELECT.

PROCESS - Разрешает использование SHOW FULL PROCESSLIST

REFERENCES - Зарезервировано  для использования в будущем 

RELOAD - Разрешает использование FLUSH

REPLICATION CLIENT - Предоставляет пользователю право запрашивать местонахождение головного и подчиненных серверов

REPLICATION SLAVE - Необходимо  для подчиненных серверов при  репликации (для чтения информации  из бинарных журналов головного сервера).

SELECT - Разрешает использование  SELECT

SHOW DATABASES - SHOW DATABASES выводит все базы данных.

SHUTDOWN - Разрешает использование mysqladmin shutdown

SUPER - Позволяет установить одно соединение (один раз), даже если достигнуто значение max_connections, и запускать команды CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS и SET GLOBAL

UPDATE - Разрешает использование  UPDATE

USAGE - Синоним ``без привилегий'' - можно задавать, если необходимо  создать пользователя без привилегий.

2.5 Обработка ошибок

 

При выполнении программных объектов пользователь должен иметь возможность  оперативно реагировать на возникающие  отклонения от нормального процесса их выполнения. Для решения этой задачи разработчики SQL применили механизм обработки исключительных ситуаций. Данный механизм представляет собой прием, обеспечивающий перехват и обработку ошибок и предупреждений. Исключительные ситуации в SQL возникают при выполнении SQL-операторов. Исключительные ситуации представляются в виде кодов возврата, которые формируются после завершения выполнения операторов. Коды возврата одновременно записываются в системные переменные SQLSTATE и SQLCODE. В этих переменных возникновение исключительных ситуаций кодируются по разному. Так в переменной SQLCODE они представляются в виде десятичных чисел. При этом отрицательные значения соответствуют ошибкам, положительные - предупреждениям, а ноль - успешному завершению оператора. Кодирование исключительных ситуаций в переменной SQLSTATE соответствует стандарту SQL/92. При этом коды возврата представляются в виде строк из пяти символов. В переменной SQLSTATE первые два символа составляют код класса ошибок, следующие три символа - код внутри класса. Код "00000" соответствует успешному выполнению оператора. В зависимости от значений кодов возврата может быть принято решение о повторении оператора, прерывания функционирования приложения и т.д.

 

ПРИМЕЧАНИЕ. После считывания значения одной из переменных SQLSTATE или SQLCODE, обе они приводятся в состояние отсутствия ошибок.

 

 

 

 

 

 

 

 

 

 

3 Таблицы SQL

 

До сих пор понятие «таблица», как правило, связывалось с реальной или базовой таблицей, т.е. c таблицей, для каждой строки которой в действительности имеется некоторый двойник, хранящийся в физической памяти машины. Однако SQL использует и создает ряд виртуальных (как будто существующих) таблиц: представлений, курсоров и неименованных рабочих таблиц, в которых формируются результаты запросов на получение данных из базовых таблиц и, возможно, представлений. Это таблицы, которые не существуют в базе данных, но как бы существуют c точки зрения пользователя.

Базовые таблицы cоздаются c помощью предложения CREATE TABLE (создать таблицу). Запрос для создания описания таблицы Компьютеры будет выглядеть так:

CREATE TABLE Компьютеры

(КМ SMALLINT,

Комп CHAR (70),

В  CHAR (1),

Оcнова CHAR (10),

Выход FLOAT,

Цена SMALLINT);

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

 

3.1 Создание  баз данных

 

В различных СУБД процедура  создания баз данных существенно  отличается. В многопользовательских  системах право создания баз данных обычно закрепляется только за администратором базы данных (АБД). В однопользовательских системах предусмотренная по умолчанию база данных может быть создана непосредственно в процессе установки и настройки параметров самой СУБД, а другие базы данных создаются самим пользователем по мере необходимости. Стандарт ISO не определяет, как должны создаваться базы данных, поэтому в каждом из диалектов языка SQL обычно используется собственный подход. В соответствии со стандартом ISO, таблицы и другие объекты базы данных существуют в некоторой среде (environment). Помимо всего прочего, каждая среда состоит из одного или нескольких каталогов (catalog), а каждый каталог — из набора схем (schema). Схема представляет собой именованную коллекцию объектов базы данных, которые определенным образом связаны друг с другом (все объекты в базе данных должны быть описаны в той или иной схеме). Объектами схемы могут быть таблиц, представления, домены, утверждения, сопоставления, толкования и наборы символов. Все объекты схемы имеют одного и того же владельца и множество общих значений, применяемых по умолчанию.