Справка по SQL - W Cat
Шрифт:
Интервал:
Закладка:
Если столбец допускает NULL-значение, то его можно указать в явном виде. Кроме того, можно заменить имеющееся значение на значение по умолчанию (DEFAULT) для данного столбца.
Ссылка на "выражение" может относиться к текущим значениям в изменяемой таблице. Например, мы можем уменьшить все цены ПК-блокнотов на 10 процентов с помощью следующего оператора:
UPDATE Laptop SET price=price*0.9
Разрешается также значения одних столбцов присваивать другим столбцам. Пусть, например, требуется заменить жесткие диски менее 10 Гб в ПК-блокнотах. При этом емкость новых дисков должна составлять половину объема RAM, имеющейся в данных устройствах. Эту задачу можно решить следующим образом:
UPDATE Laptop SET hd=ram/2 WHERE hd<10
Естественно, типы данных столбцов 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), то следующий оператор
UPDATE Laptop SET code=5 WHERE code=4
не будет выполнен, т.к. автоикрементируемое поле не допускает обновления, и мы получим соответствующее сообщение об ошибке. Чтобы выполнить все же эту задачу, можно поступить следующим образом. Сначала вставить нужную строку, используя 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)
Оператор DELETE
Оператор DELETE удаляет строки из временных или постоянных базовых таблиц, представлений или курсоров, причем в двух последних случаях действие оператора распространяется на те базовые таблицы, из которых извлекались данные в эти представления или курсоры. Оператор удаления имеет простой синтаксис:
DELETE FROM [WHERE ];
Если предложение WHERE отсутствует, удаляются все строки из таблицы или представления (представление должно быть обновляемым). Более быстро эту операцию (удаление всех строк из таблицы) в Transact-SQL можно также выполнить с помощью команды
TRUNCATE TABLE
Однако есть ряд отличий в реализации команды TRUNCATE TABLE по сравнению с использованием оператора DELETE, которые следует иметь в виду:
1. Не журнализируется удаление отдельных строк таблицы. В журнал записывается только освобождение страниц, которые были заняты данными таблицы.
2. Не отрабатывают триггеры. Как следствие, эта команда неприменима, если на данную таблицу имеется ссылка по внешнему ключу.
3. Значение счетчика (IDENTITY) сбрасывается в начальное значение.
Пример. Требуется удалить из таблицы Laptop все ПК-блокноты с размером экрана менее 12 дюймов.
DELETE FROM Laptop
WHERE screen<12
Все блокноты можно удалить с помощью оператора
DELETE FROM Laptop
или
TRUNCATE TABLE Laptop
Transact-SQL расширяет синтаксис оператора DELETE, вводя дополнительное предложение FROM
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-значение, что и используется для идентификации подлежащих удалению строк.
Как объединить данные из двух столбцов в один без использования UNION и JOIN?
Моисеенко С.И. (22-08-2008)
Такие вопросы с завидной регулярностью появляются на страницах различных форумов. К слову сказать, для меня до сих пор остается загадкой, почему при этом ставится дополнительное условие не использовать UNION и/или JOIN. Могу лишь предположить, что это вопросы, которые задают на собеседовании при приеме на работу.
Лирическое отступление. Догадываюсь, как ответил бы на этот вопрос Джо Селко: налицо ошибка проектирования, состоящая в том, что один атрибут расщеплен на два. Однако оставим в стороне вопросы проектирования и перейдем к решению этой задачи.
Создадим тестовую таблицу и добавим в нее немного данных:
CREATE TABLE T (
col1 INT
, col2 INT
)
GO
INSERT INTO T
SELECT 1, 1
UNION ALL SELECT 1, 3
UNION ALL SELECT NULL, NULL
UNION ALL SELECT NULL, 2
GO
Итак, имеется таблица T, которая содержит два столбца с данными одного типа:
SELECT col1, col2
FROM T
col1 col2
1 1
1 3
NULL NULL
NULL 2
Требуется получить следующий результат:
col
1
1
NULL
NULL
1
3
NULL 2
Мне известны три способа, реализуемых стандартными средствами интерактивного языка SQL.
1. UNION ALL
Очевидное решение, не требующее комментариев. Заметим лишь, что UNION не подходит для решения этой задачи, т.к. устраняет дубликаты.
SELECT col1 col FROM T
UNION ALL
SELECT col2 FROM T
2. FULL JOIN
Чтобы не потерять дубликаты, находящиеся в разных столбцах, выполним полное соединение (FULL JOIN) по заведомо ложному предикату, скажем, 1 = 2:
SELECT T.col1,T1.col2
FROM T FULL JOIN T AS T1 ON 1=2
Результат:
col1 col2
1 NULL
1 NULL
NULL NULL
NULL NULL
NULL 1
NULL 3
NULL NULL
NULL 2
Далее используем функцию COALESCE, которая даст нам все, что нужно:
SELECT COALESCE(T.col1,T1.col2) col
FROM T FULL JOIN T AS T1 ON 1=2
3. UNPIVOT
Конструкции PIVOT и UNPIVOT появились в последних версиях стандарта SQL и были реализованы SQL Server, начиная с версии 2005. Первая из них позволяет значения в столбце вытянуть в строку, а вторая поможет нам выполнить обратную операцию:
