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

CREATE TABLE [product_D] (    

[maker] [char] (1) NULL ,    

[model] [varchar] (4) NULL ,    

[type] [varchar] (7) NOT NULL DEFAULT 'PC' )

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

INSERT INTO Product_D (model, maker) VALUES (1157, 'B');

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

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

INSERT INTO Product_D VALUES ('B', 1158, DEFAULT);

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

 INSERT INTO Product_D VALUES (DEFAULT, DEFAULT, DEFAULT);

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

INSERT INTO Product_D DEFAULT VALUES;

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

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

INSERT INTO Product_D SELECT * FROM Product WHERE type = 'PC';

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

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

INSERT INTO Product_D    

SELECT 'B' AS maker, 1158 AS model, 'PC' AS type    

UNION ALL    

SELECT 'C', 2190, 'Laptop'    

UNION ALL    

SELECT 'D', 3219, 'Printer';

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

2) Оператор UPDATE.

Оператор UPDATE изменяет имеющиеся  данные в таблице. Команда имеет  следующий синтаксис 

UPDATE    

SET {имя столбца = {выражение для вычисления значения столбца    

| NULL    

| DEFAULT},...}    

[ {WHERE }];

С помощью одного оператора  могут быть заданы значения для любого количества столбцов. Однако в одном  и том же операторе UPDATE можно вносить  изменения в каждый столбец указанной таблицы только один раз. При отсутствии предложения WHERE будут обновлены все строки таблицы.

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

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

UPDATE Laptop SET price=price*0.9

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

UPDATE Laptop SET hd=ram/2 WHERE hd

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

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

Если, скажем, нужно поставить  жесткие диски объемом 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-значение, что и используется для идентификации подлежащих удалению строк.

 

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

 

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

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

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2 глава основной части

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

 

К операторам защиты данных относят операторы авторизации  и привилегий. Синтаксис команд 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