лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
CAST (пункт 5.9). Если требуется изменять данные в зависимости от содержимого некоторого столбца, можно воспользоваться выражением CASE (пункт 5.9) Если, скажем, нужно поставить жесткие диски объемом 20 Гбайт на портативные компьютеры с памятью менее 128 Мбайт и 40 гигабайтные — на остальные портативные компьютеры, то можно написать такой запрос: 1. UPDATE Laptop 2. SET hd = CASE 3. WHEN ram < 128 4. THEN 20 5. ELSE 40 6. END ; Для вычисления значений столбцов допускается также использование подзапросов. Например, требуется укомплектовать все портативные компьютеры самыми быстрыми процессорами из имеющихся в наличии. Тогда можно написать: 1. UPDATE Laptop 2. SET speed = ( SELECT MAX ( speed ) 3. FROM Laptop 4. ) ; Необходимо сказать несколько слов об автоинкрементируемых столбцах. Если столбец code в таблице Laptop определен как IDENTITY(1,1), то следующий оператор 1. UPDATE Laptop 2. SET code = 5 3. WHERE code = 4 ; не будет выполнен, так как автоикрементируемое поле не допускает обновления, и мы получим соответствующее сообщение об ошибке. Чтобы выполнить все же эту задачу, можно поступить следующим образом. Сначала вставить нужную строку, используя SET IDENTITY_INSERT, после чего удалить старую строку: 1. SET IDENTITY_INSERT Laptop_ID ON ; 2. INSERT INTO Laptop_ID ( code, model, speed, ram, hd, price, screen ) 3. SELECT 5 , model, speed, ram, hd, price, screen 4. FROM Laptop_ID WHERE code = 4 ; 5. DELETE FROM Laptop_ID 6. WHERE code = 4 ; Разумеется, другой строки со значением code = 5 в таблице быть не должно. В Transact-SQL оператор UPDATE расширяет стандарт за счет применения необязательного предложения FROM. В этом предложении специфицируется таблица, обеспечивающая критерий для операции обновления. Дополнительную гибкость здесь дают операции соединения таблиц. Пример 6.2.1. Пусть требуется указать «No PC» (нет ПК) в столбце type для тех моделей ПК из таблицы Product, для которых нет соответствующих строк в таблице PC. Решение посредством соединения таблиц можно записать так 1. UPDATE Product 2. SET type = 'No PC' 3. FROM Product pr LEFT JOIN 4. PC ON pr.model=PC.model 5. WHERE type = 'pc' AND 6. PC.model IS NULL ; Здесь применяется внешнее соединение, в результате чего столбец PC.model для моделей ПК, отсутствующих в таблице PC, будет содержать NULL- значение, что и используется для идентификации подлежащих обновлению строк. Естественно, эта задача имеет решение и в «стандартном» исполнении: 1. UPDATE Product 2. SET type = 'No PC' 3. WHERE type = 'pc' AND 4. model NOT IN ( SELECT model 5. FROM PC 6. ) ; Оператор DELETE Оператор DELETE удаляет строки из временных или постоянных базовых таблиц, представлений или курсоров, причем в двух последних случаях действие оператора распространяется на те базовые таблицы, из которых извлекались данные в эти представления или курсоры. Оператор удаления имеет простой синтаксис: 1. DELETE FROM <имя таблицы > 2. [ WHERE <предикат> ] ; Если предложение WHERE отсутствует, удаляются все строки из таблицы или представления (представление должно быть обновляемым). Более быстро эту операцию (удаление всех строк из таблицы) можно в Transact-SQL также выполнить с помощью команды 1. TRUNCATE TABLE <имя таблицы> Однако есть ряд особенностей в реализации команды TRUNCATE TABLE, которые следует иметь в виду: не журнализируется удаление отдельных строк таблицы; в журнал записывается только освобождение страниц, которые были заняты данными таблицы; не отрабатывают триггеры, в частности, триггер на удаление; команда неприменима, если на данную таблицу имеется ссылка по внешнему ключу, и даже если внешний ключ имеет опцию каскадного удаления. значение счетчика (IDENTITY) сбрасывается в начальное значение. Пример 6.3.1 Требуется удалить из таблицы Laptop все портативные компьютеры с размером экрана менее 12 дюймов. 1. DELETE FROM Laptop 2. WHERE screen < 12 ; Все блокноты можно удалить с помощью оператора 1. DELETE FROM Laptop; или 1. TRUNCATE TABLE Laptop; Transact-SQL расширяет синтаксис оператора DELETE, вводя дополнительное предложение FROM: 1. FROM <источник табличного типа> При помощи источника табличного типа можно конкретизировать данные, удаляемые из таблицы в первом предложении FROM. При помощи этого предложения можно выполнять соединения таблиц, что логически заменяет использование подзапросов в предложении WHERE для идентификации удаляемых строк. Поясним сказанное на примере. Пример 6.3.2 Пусть требуется удалить те модели ПК из таблицы Product, для которых нет соответствующих строк в таблице PC. Используя стандартный синтаксис, эту задачу можно решить следующим запросом: 1. DELETE FROM Product 2. WHERE type = 'pc' AND 3. model NOT IN ( SELECT model 4. FROM PC 5. ) ; Заметим, что предикат type = 'pc' необходим здесь, чтобы не были удалены также модели принтеров и портативных компьютеров. Эту же задачу можно решить с помощью дополнительного предложения FROM следующим образом: 1. DELETE FROM Product 2. FROM Product pr LEFT JOIN 3. PC ON pr.model = PC.model 4. WHERE type = 'pc' AND 5. PC.model IS NULL ; Здесь применяется внешнее соединение, в результате чего столбец PC.model для моделей ПК, отсутствующих в таблице PC, будет содержать NULL- значение, что и используется для идентификации подлежащих удалению строк. Оператор TRUNCATE TABLE Как отмечалось выше, при выполнении этой команды значение счетчика (IDENTITY) сбрасывается в начальное значение. Давайте проверим это утверждение в MS SQL Server. Для начала создадим таблицу с автоинкрементируемым столбцом, и добавим в нее три строки. 1. CREATE TABLE Truncate_test ( id INT IDENTITY ( 5 , 5 ) PRIMARY KEY , val INT ) ; 2. GO 3. INSERT INTO Truncate_test ( val ) 4. VALUES ( 1 ) , ( 2 ) , ( 3 ) ; 5. SELECT * FROM Truncate_test; 6. GO Начальным значением счетчика является 5, приращение счетчика выполняется также с шагом 5. В результате получим: id val 5 1 10 2 15 3 Теперь удалим строки с помощью оператора DELETE, после чего снова вставим те же строки в таблицу. 1. DELETE FROM Truncate_test; 2. GO 3. INSERT INTO Truncate_test ( val ) 4. VALUES ( 1 ) , ( 2 ) , ( 3 ) ; 5. SELECT * FROM Truncate_test; 6. GO id val 20 1 25 2 30 3 Как видно из результата, состояние счетчика не было сброшено, и приращение продолжилось с последнего значения (15), в отличие от использования оператора TRUNCATE TABLE: 1. TRUNCATE TABLE Truncate_test; 2. GO 3. INSERT INTO Truncate_test ( val ) 4. VALUES ( 1 ) , ( 2 ) , ( 3 ) ; 5. SELECT * FROM Truncate_test; 6. GO id val 5 1 10 2 15 3 В то же время Стандарт предполагает несколько иное поведение. Стандартный синтаксис имеет вид 1. TRUNCATE TABLE < имя таблицы > [{ CONTINUE IDENTITY } | { RESTART IDENTITY }] т.е. значение счетчика может быть сброшено (опция RESTART IDENTITY) или продолжено (опция CONTINUE IDENTITY). И, кстати, значением по умолчанию является как раз CONTINUE IDENTITY, что эквивалентно поведению при использованию оператора DELETE (без предложения WHERE). Оператор TRUNCATE TABLE неприменим, если на таблицу имеется ссылка по внешнему ключу. Это стандартное поведение имеет место в SQL Server. Если создать, например, такую ссылающуюся таблицу, которая даже не будет содержать данных 1. CREATE TABLE Trun_Ref ( id INT REFERENCES Truncate_test ) ; оператор TRUNCATE TABLE приведет к следующей ошибке: Cannot truncate table 'Truncate_test' because it is being referenced by a FOREIGN KEY constraint. (Невозможно усечь таблицу 'Truncate_test', поскольку на нее ссылается ограничение FOREIGN KEY) . Теперь проверим, насколько близки к стандарту другие реализации. PostgreSQL 1. Поддерживаются опции CONTINUE IDENTITY и RESTART IDENTITY, при этом опция CONTINUE IDENTITY принимается по умолчанию. 2. Можно удалить одним оператором строки из нескольких таблиц, перечислив их через запятую. 3. Допускаются каскадные операции, т.е. усечение связанных таблиц: 1. TRUNCATE TABLE Truncate_test RESTART IDENTITY CASCADE; Причем происходит именно усечение, а не удаление связанных строк. Т.е. если вставить в ссылающуюся таблицу среди прочих строку с NULL- значением во внешнем ключе 1. INSERT INTO Trun_Ref VALUES ( 1 ) , ( 2 ) , ( NULL ) ; то она также будет удалена наряду с остальными. Oracle 1. В Oracle нет функции для автоинкремента, которую можно указать в определении столбца. Однако поведение автоинкремента можно сымитировать с помощью последовательности (SEQUENCE). Например, подобную ранее рассмотренной таблицу Truncate_test в Oracle можно создать следующим образом: 1. CREATE SEQUENCE u_seq 2. START WITH 5 3. INCREMENT BY 5 ; 4. / 5. CREATE TABLE Truncate_test ( id INT PRIMARY KEY , val int ) ; 6. / 7. INSERT INTO Truncate_test ( id, val ) 8. VALUES ( u_seq. NEXTVAL , 1 ) ; 9. INSERT INTO Truncate_test ( id, val ) 10. VALUES ( u_seq. NEXTVAL , 2 ) ; 11. INSERT INTO Truncate_test ( id, val ) 12. VALUES ( u_seq. NEXTVAL , 3 ) ; 2. При выполнении оператора TRUNCATE TABLE Truncate_test состояние счетчика (последовательности) не сбрасывается, и нумерация будет продолжена. 3. Каскадные операции не допускаются, т.е. оператор неприменим, если на таблицу есть ссылка по внешнему ключу. MySQL 1. Не поддерживаются опции CONTINUE IDENTITY и RESTART IDENTITY, при этом состояние счетчика (AUTO_INCREMENT) сбрасывается. 2. Допускаются каскадные операции по аналогичии с оператором DELETE, т.е. если внешний ключ имеет опцию ON DELETE CASCADE, то удаляются только связанные записи. Это значит, что строки с NULL-значением внешнего ключа остаются после выполнения оператора 1. TRUNCATE TABLE Truncate_test; Готовимся ко второму этапу тестирования Задачи, рассмотренные нами ранее в главах 3–4, составляют первый, или обучающий, этап тестирования на сайте. Эти относительно простые задачи, для решения которых, как правило, не требуется знания особенностей реализации. Это означает, что решение практически любой из них можно получить, используя конструкции языка SQL, оформленные стандартом SQL-92. На втором этапе предлагаются более сложные задачи, для решения которых уже не обойтись без знаний конкретной реализации, например, функций работы со строками и значениями типа даты/времени. Задачи этого этапа не рассматриваются в книге, так как по результатам их решения на сайте можно получить сертификат. Тем не менее, мы считаем необходимым познакомить читателя со специфическими особенностями SQL Server, а также некоторыми алгоритмическими приемами, которые могут применяться для решения как задач на сайте, так и задач, часто возникающих на практике. Функции Transact- SQL для работы со строками и данными типа даты/времени Стандарт SQL-92 специфицирует незначительное число функций для работы со строковыми значениями и значениями даты и времени. Что касается последних, то они ограничиваются лишь функциями, возвращающими системную дату/время. Например, функция CURRENT_TIMESTAMP возвращает сразу и дату, и время. Плюс имеются функции возвращающие что-либо одно. Естественно, в силу такой ограниченности, реализации языка расширяют стандарт за счет добавления функций, облегчающий работу пользователей с данными этого типа. Это обусловлено еще и тем, что на нижнем уровне соответствия стандарту (Entry Level) не требуется поддержка стандартизованных функций этих типов. Функция DATEADD Функция DATEADD (datepart, number, date) возвращает значение типа datetime, которое получается добавлением к дате date количества интервалов типа datepart, равного number (целое число). Например, мы можем к заданной дате добавить любое число лет, дней, часов, минут и т. д. Допустимые значения аргумента datepart приведены ниже в таблице и взяты из электронной документации к SQL Server — Books On Line (BOL). Datepart Допустимые сокращения Year — год yy, yyyy Quarter — квартал qq, q Month — месяц mm, m Dayofyear — день года dy, y Day — день dd, d Week — неделя wk, ww Hour — час hh Minute — минута mi, n Second — секунда ss, s Millisecond - миллисекунда ms Пусть сегодня 28.10.2005, и мы хотим узнать, какой день будет через неделю. Мы можем написать: 1. SELECT DATEADD ( day, 7 , current_timestamp ) ; а можем и так: 1. SELECT DATEADD ( ww, 1 , current_timestamp ) ; В результате получим одно и то же значение; что-то типа 2005-11-04 00:11:28.683. Однако мы не можем в этом случае написать: 1. SELECT DATEADD ( mm, 1 / 4 , current_timestamp ) ; и не потому, что четверть месяца не равна в точности неделе, а потому, что дробная часть значения аргумента datepart отбрасывается, и мы получим 0 вместо одной четвертой и, как следствие, текущий день. Кроме того, мы можем использовать вместо CURRENT_TIMESTAMP функцию T-SQL GETDATE() с тем же самым эффектом. Наличие двух идентичных функций поддерживается, видимо, в ожидании последующего развития стандарта. Пример 7.1.1 Определить, какой будет день через неделю после последнего полета. Примечание: В примерах данной главы используется база данных «Аэрофлот». Описание этой схемы и всех остальных схем, используемых в настоящее время на сайте для решения задач, можно найти в Примечании 1. 1. SELECT DATEADD ( day, 7 , ( SELECT MAX ( date ) max_date 2. FROM pass_in_trip 3. ) 4. ) ; Применение подзапроса в качестве аргумента допустимо, так как этот подзапрос возвращает единственное значение типа datetime. На примере задачи 7.1.1 рассмотрим добавление интервала к дате для других СУБД. MySQL MySQL имеет похожую функцию с непохожими аргументами. Вот синтаксис этой функции: 1. DATE_ADD ( date, INTERVAL value addunit ) Здесь date - дата, к которой прибавляется интервал; value - величина интервала; addunit - тип интервала. Допустимы следующие типы интервалов, имена которых говорят сами за себя: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH Решение нашей задачи для MySQL примет вид: 1. SELECT DATE_ADD (( SELECT MAX ( date ) FROM pass_in_trip ) , 2. interval 7 day ) next_wd; next_wd 2005-12-06 00:00:00 Чтобы добавить интервал, представляющий собой несколько компонентов времени, используется подстрока из стандартного представления даты/времени. Так, например, чтобы добавить к '2018-01-27T13:00:00' один день и 3 часа, можно написать: 1. SELECT DATE_ADD ( '2018-01-27T13:00:00' , interval '1T3' DAY_HOUR ) ; 2018-01-28 16:00:00 Добавление 1 дня и 15 секунд будет выглядеть так: 1. SELECT DATE_ADD ( '2018-01-27T13:00:00' , interval '01T00:00:15' DAY_SECOND ) ; 2018-01-28 13:00:15 PostgreSQL и Oracle Эти СУБД не используют функцию. Для добавления интервала применяется обычный оператор сложения "+": 1. SELECT MAX ( "date" ) + interval '7' day next_wd 2. FROM pass_in_trip; Обратите внимание, что величина интервала должна иметь символьный тип данных. Добавить 1 день и 3 часа PostgreSQL У PostgreSQL нет составных интервалов, поэтому можно либо выразить величину интервала в терминах меньшего интервала 1. SELECT timestamp '2018-01-27T13:00:00' + interval '27' hour; либо добавить два интервала 1. SELECT timestamp '2018-01-27T13:00:00' + interval '3' hour + interval '1' day; Аналогично можно поступить для добавления одного дня и 15 секунд, например: 1. SELECT timestamp '2018-01-27T13:00:00' + interval '15' second + interval '1' day; |