Добавление, изменение и удаление данных

 

Команда insert (вставить) позволяет добавлять новые строки в базу данных. Команда update (обновить) позволяет изменить существующие в базе данные. Команда delete (удалить) удаляет данные из базы. А команда writetext (запись текста) позволяет добавлять или изменять тектовые (text) и графические (image) данные без записи больших массивов в системный журнал транзакций.

Все эти операторы вместе называются операторами модификации данных. В этой главе также рассматривается команда truncate table (очистить таблицу), удаляющая все строки в таблице. Другим способом добавления данных в таблицу является их непосредственная перепись из файла с помощью программы-утилиты bcp. Детальная информация об этих возможностях находится в Справочном Руководстве SQL сервера и в Пособии по утилитам SQL Сервера для операционной системы пользователя.

Пользователь может модифицировать данные с помощью одного из операторов insert, update и delete только в одной таблице. Однако, исходные для модификации данные могут выбираться из других таблиц, и даже из других баз данных. Эта возможность является расширением языка Transact-SQL по отношению к стандартной версии языка SQL.

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

 

 

Команды модификации данных доступны не каждому пользователю. Владелец базы данных и владельцы ее объектов с помощью команд grant (предоставить) и revoke (отменить) предоставляют отдельным пользователям право на модификацию данных.

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

 

 

Команды insert, update, delete, и truncate table позволяют изменять данные в базе данных. Однако, если изменить данные в одной таблице и не изменить соответствующие данные в других таблицах, то могут возникнуть противоречия.

Например, если пользователь обнаружил, что в столбец авторского идентификатора au_id для Сильвии Пантели (Sylvia Panteley) введено неправильное значение, а затем изменил его в таблице authors, то ему следует также изменить его в таблице titleauthor и в других таблицах базы данных, в которых имеется этот столбец. Если этого не сделать, то невозможно будет, например, найти названия книг этого автора, поскольку нельзя будет сделать соединение по столбцу au_id.

Общая проблема согласованности данных при их модификации в таблицах  базы данных называется проблемой ссылочной целостности. Одним из способов решения этой проблемы является создание специальных процедур, называемых триггерами, которые автоматически запускаются, когда выполняются команды insert, update и delete (команда truncate table не приводит к запуску триггера). Другой способ состоит в определении ограничений ссылочной целостности непосредственно в таблице. Триггера обсуждаются в главе 15: “Триггера: обеспечение ссылочной целостности”, а ограничения целостности рассматриваются в главе 17: “Создание баз данных и таблиц”.

 

 

Копии старого и нового содержимого каждой строки при каждом выполнении оператора модификации данных вносятся в журнал транзакций (за исключением команды writetext). Это означает, что если пользователь запустил транзакцию командой begin transaction (начать транзакцию) и, обнаружив ошибку, решил ее отменить, то сервер сможет восстановить прежнее состояние базы данных, в котором она находилась до начала транзакции.

 

Замечание: Изменения, произведенные на удаленном SQL сервере с помощью вызова удаленной процедуры (RPC), не могут быть восстановлены.

 

По умолчанию при выполнении команды writetext копии данных не записываются в журнал транзакций. Это позволяет не переполнять его слишком  большими массивами текстовой и графической информации, которые могут содержаться в полях text и image. Если установлена опция with log (с регистрацией) для команды writetext, то изменение состояния, вызываемое этой команды, вместе с копиями соответствующих данных также будут записываться в журнал транзакций.

Более детальное обсуждение транзакций будет сделано в главе 17: “Транзакции: Сохранение согласованности данных и их восстановление”.

 

 

Если пользователь будет выполнять все примеры, приведенные в этой главе, то, по-видимому, целесообразно начать работу с исходной копией базы данных pubs2, а затем вернуть ее в первоначальное состояние после окончания работы. Для получения исходной копии базы данных pubs2 следует обратиться к системному администратору.

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

 

begin tran modify_pubs2

 

Эта транзакция называется modify_pubs2 (изменение базы данных pubs2). Транзакцию можно отменить в любой момент и вернуться к исходному состоянию базы данных, в котором она была до начала транзакции, с помощью следующей  команды:

 

rollback tran modify_pubs2

 

 

Некоторые типы данных, предоставляемые SQL Сервером, требуют соблюдения специальных правил при их вводе и поиске. Эти правила  рассматриваются в нижеследующих подразделах. Более детальную информацию о типах данных можно посмотреть в главе 7: ” Создание баз данных и таблиц”.

 

 

Не следует забывать, что при введении или поиске данных типа character (символьный), text (текстовый) и datetime (дата-время), их следует заключать в простые или двойные кавычки. Нужно использовать простые кавычки, если включена опция quoted_identifier (идентификатор в кавычках), так как в этом случае SQL Сервер рассматривает текстовую строку, заключенную в двойные кавычки, как идентификатор. Более детально вопросы, связанные с вводом текстовых данных, рассматриваются в Справочном Руководстве SQL Сервера.

При введении символьных строк, длина которых превышает заданную, они укорачиваются до указанной длины в столбцах типа char, nchar, varchar, или nvarchar. Чтобы получить об этом предупреждающее сообщение, следует установить опцию string_truncation.

Существуют два способа распознавания символа кавычек при вводе символьных строк. Первый способ заключается в использовании двух кавычек подряд. Например, если ввод строки начинается с простой кавычки и необходимо  включить в нее простую кавычку как один из вводимых символов, то следует указать в этом месте подряд две кавычки, как, например, в предложении: ‘I don’’t understand’. Аналогично можно включить в строку двойные кавычки, например: “He said, “”It’s not realy confusing.”””

Второй способ состоит в использовании кавычек противоположного вида. Другими словами, данные, вводимые в простых кавычках, заключаются в двойные, и наоборот. Например: ‘Джорж сказал, “Должен быть другой путь.”’

Для введения символьной строки, длина которой превышает ширину экрана, нужно ввести обратную косую (\) перед переходом на следующую строку.

Ключевое слово like и символы замены, описанные в главе 2 ”Запросы: выбор данных из таблиц”, могут быть использованы при поиске данных типа character, text, datetime.

О пробелах на концах символьных строк можно прочитать в разделе “Типы данных” в Справочном Руководстве SQL сервера.

 

 

Имеется большой выбор форматов для ввода и вывода данных типа datetime (дата время). Форматы ввода и вывода задаются независимо друг от друга. Формат вывода, заданный по умолчанию, позволяет представлять дату и время в следующем виде:”Apr 15 1987 10:23PM”. Команда convert (преобразовать) предоставляет   опции для вывода секунд и миллисекунд, а также позволяет изменить порядок, в котором выводятся составляющие даты. Детально форматы даты и времени описаны в главе 10, “Использование встроенных функций в запросах”.

 SQL Сервер воспринимает большое количество различных форматов для ввода даты. При вводе не различаются строчные и заглавные буквы, а пробелы могут находиться в любом месте между различными составляющими даты. При вводе значений типа datetime и smalldatetime их всегда следует заключать в простые или двойные кавычки. (Простые кавычки следует использовать при включенной опции quoted_identifier; так как строку, заключенную в двойные кавычки, SQL Сервер рассматривает в этом случае как идентификатор.)

SQL Сервер рассматривает дату и время независимо друг от друга, поэтому время может предшествовать дате или располагаться после нее. Любая из  составляющих комплекса дата-время может быть опущена, кроме того SQL Сервер может задавать дату и время по умолчанию, о чем будет сказано далее. Если опущены обе составляющие, то по умолчанию будет установлено следующее значение:  1 Января, 1900, 12:00:00:000AM.

Для данных типа datetime наименьшим значением является дата 1 Января 1753 года, а наибольшим - 31 Декабря 9999 года. Для данных типа smalldatetime  такими значениями будут соответственно 1 Января  1900 года и 6 Июня  2079 года. Даты, не попадающие в эти интервалы, должны вводиться, храниться и обрабатываться как данные типа char и  varchar. SQL Сервер отвергает любые значения типа даты, не попадающие в указанные интервалы.

 

 

Порядок, в котором располагаются компоненты значения времени является существенным. Сначала нужно ввести часы, затем по порядку минуты, секунды, миллисекунды, АМ или am, PM или pm. 12AM - это полночь, а 12PM - это полдень. Для того, чтобы значение воспринималось как время, необходимо разделять его компоненты двоеточиями или ставить указатель AM/PM. Следует заметить, что тип данных smalldatetime  задает время только с точностью до минут.

Миллисекундам должны предшествовать либо двоеточие, либо точка. Если перед миллисекундами стоит двоеточие, то это число означает тысячные доли секунды. Если же миллисекундам предшествует точка, то однозначное число обозначает десятые доли секунды, двузначное - сотые доли секунды, а трехзначное - тысячные. Например, ‘12:30:20:1’ обозначает 12 часов, 30 минут, 20 и одна тысячная секунды; ‘20:30:20.1’ означает 12 часов, 30 минут, 20 и одна десятая секунды. 

Ниже приведены допустимые форматы для данных типа дата-время:

 

 

14:30

14:30[:20:999]

14:30[:20.9]

4am

4 PM

[0]4[:30:20:500]AM

 

 

 

Команда set dateformat (установить формат даты) позволяет пользователю  точно указать порядок следования компонент даты (месяц, день, год), когда они вводятся как числовые строки с разделителями. Изменение языка с помощью команды set language (установить язык) также может повлять на формат даты, поскольку в каждом языке по умолчанию устанавливается свой формат даты. По умолчанию, устанавливается язык us_english (американский, английский), в котором  компоненты даты задаются в следующем порядке mdy (month-месяц, date-дата, year-год). Более детально команда set рассматривается в Справочном Руководстве SQL Сервера.      

 

                       Замечание: Команда dateformat влияет только на даты, задаваемые в виде чисел с разделителями, например “4.15.90” или “20.05.88”. Она не влияет на даты, в которых месяц указан в символьном виде, например, “April 15, 1990”, или на даты, в которых отсутствуют разделители, например, “19890415”. 

 

SQL Сервер допускает три основных формата для ввода данных типа даты.  Каждый из нижеприведенных форматов даты, при использовании должен заключаться в кавычки и может находиться либо перед, либо после значения времени, как было отмечено выше.

 

·         Если название месяца вводится в буквенном (символьном) виде, то действуют следующие соглашения.

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

Þ       Запятые не обязательны.

Þ       Не различается регистр символов.

Þ       Если указываются только две последние цифры “уу” в компоненте года, то числа меньше 50 означают год “20yy”, а цифры от 50 включительно и больше означают год “19yy”.

Þ       Следует явно указать век, если не указан день или если год отличается от указанных выше значений, принимаемых по умолчанию.

Þ       Если день опущен, то по умолчанию устанавливается первый день месяца.

Þ       Если название месяца указывается в буквенном виде, то установки сделанные командой dateformat (см. команду set) всегда игнорируются.

Þ       Следующие форматы допустимы для алфавитно-цифрового указания даты:

 

              Apr[il] [15][,] 1988

                  Apr[il] 15[,] [19]88

                  Apr[il] 1988 [15]

                  [15] Apr[il][,] 1988

                  15 Apr[il][,] [19]88

                  15 [19]88 apr[il]

                  [15] 1988 apr[il]

                  1988 APR[IL] [15]

                  [19]88 APR[IL] 15

                  1988 [15] APR[IL]

 

·           Если название месяца, вводится в числовом виде, то оно отделяется от остальных частей даты косой(/), дефисом(-) или точкой (.), и действуют следующие соглашения.

Þ       Следует явно указать месяц, день и год.

Þ       Строка даты должна иметь один из следующих видов:

        <число><разделитель><число><разделитель><число>[<время>]

           или

       [<время>]<число><разделитель><число><разделитель><число>

Þ       Интерпретация значений даты зависит от установок, сделанных в команде dateformat. Если порядок указания компонент даты не соответствует установленному, то значение либо вообще не будет рассматриваться как дата, поскольку оно не попадет в допустимый интервал, либо оно будет  интерпретироваться неправильно.  Например, значение “12/10/08” может быть истолковано шестью различными способами в зависимости от порядка следования компонент даты. Более подробно этот вопрос рассматривается в разделе о команде set.

Þ       Например, дату “April 15,1988” в формате mdy можно ввести в следующем виде:

 

[0]4/15/[19]88

[0]4-15-[19]88

[0]4.15.[19]88

 

·           Далее приведены другие форматы ввода этой даты с разделяющей косой (“/”), в которых можно также использовать в качестве разделителей дефисы и точки:

 

15/[0]4/[19]88 (dmy)

[19]88/[0]4/15 (ymd)

[19]88/15/[0]4 (ydm)

[0]4/[19]88/15 (myd)

15/[19]88/[0]4 (dym)

 

·           Если дата задана как 4-, 6-, или 8-цифровая строка без разделителей, либо как пустая строка, либо указана только составляющая времени, то действуют следующие соглашения.

Þ       Форматы, указанные в команде dateformat, игнорируются.

Þ       Если строка состоит из 4-х цифр, то она рассматривается как год, а день и месяц по умолчанию устанавливаются на 1 Января. Первые две цифры в этой строке должны явно указывать столетие (век) и их нельзя опускать.

Þ       Строки, состоящие из 6-ти или 8-ми цифр, всегда интерпретируются в формате ymd (год, месяц, число); месяц и число должны быть двузначными числами, например [19]880415.

Þ       Пустая строка (“”) или пропущенная дата интерпретируются как базовая дата 1 Января 1900 года. Например, значение времени “4:33”  с опущенной компонентой даты означает “1 Января 1900 года, 4:33AM”.

 

 

 

Ключевое слово like (как) и символы замены могут использоваться с данными типа datetime и smalldatetime , также, как и с данными типа char, nchar, varchar, nvarchar, и  text. При использовании ключевого слова like с данными типа datetime и smalldatetime, SQL Сервер вначале преобразует эти данные в стандартный формат datetime, а затем в данные типа varchar. Поскольку стандартный формат datetime  не предусматривает вывод секунд и миллисекунд, то их поиск с помощью ключевого слова like невозможен. Для поиска дат с секундами и миллисекундами должна предварительно использоваться функция преобразования  типов данных convert.

Применение ключевого слова like для поиска данных типа datetime и smalldatetime  является хорошим техническим решением, поскольку данные этих типов могут содержать различные компоненты, которые надо явно указывать, чтобы вести поиск в формате даты, что не всегда удобно. Например, если значение “9:20” было введено в столбец arrival_time (время прибытия), то конструкция

 

where arrival_time = '9:20'

 

не позволит найти соответствующие строки, поскольку SQL Сервер преобразует это значение в дату “Jan 1, 1900 9:20AM”.

Однако, следующая конструкция будет находить нужные данные:

 

where arrival_time like '%9:20%'

 

Если день месяца, указанный в дате, меньше 10, то при использовании ключевого слова like следует сделать два пробела между днем и месяцем, поскольку они добавляются при преобразовании данных типа дата-время в данные типа varchar, в противном случае поиск может окончиться неудачно. Аналогично, если во временной компоненте час меньше 10, то при преобразовании типов добавляются два пробела между годом и часом. Например, команда вида like May 2% c одним пробелом между “May” и “2” будет находить даты между 20 и 29 мая, но не 2 мая. Не нужно вставлять дополнительные пробелы в других операторах сравнения,  поскольку данные типа дата-время преобразуются в данные типа varchar  только для при поиске данных по команде like.

 

 

При введении или поиске данных типа binary (двоичный, бинарный), varbinary (двоичный с переменной длиной) и image (графический), им должен предшествовать префикс ”0х”. Например, чтобы указать двоичное значения “FF”, следует ввести “0хFF”.

Если длина вводимого двоичного числа типа binary, varbinary, превосходит длину поля таблицы, то оно укорачивается без предупреждающего сообщения.

Если столбец таблицы типа binary, varbinary имеет длину 10, то это означает, что для хранения данных в этом столбце отведено 10 байтов, каждый из которых может содержать 2 шестнадцатиричные цифры.

При указании значений по умолчанию в столбцах типа binary, varbinary, следует также предварительно указать префикс “0х”.

В Справочном руководстве SQL Сервера можно посмотреть правила  добавления концевых нулей для шестнадцатиричных величин.    

 

 

Денежные значения, введенные в экспоненциальном формате (Е), интерпретируются как данные типа float (плавающий). Это может привести к ошибке или к потере точности, когда эти данные хранятся как значения типа money (денежный) или smallmoney (малый денежный).

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

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

 

 

Числовые величины типа float (плавающий), real (действительный) и doudle precision (двойная точность) вводятся в виде мантиссы, за которой можно указать   показатель степени. Мантисса может содержать положительный или отрицательный знак (+,-) и десятичную точку. Показатель степени, который следует за символом “е” или “Е”, может иметь знак, но в нем не должно быть десятичной точки.

При проведении вычислений с этими числовыми величинами, SQL Сервер умножает мантиссу на число 10, возведенное в степень, равную показателю степени. Ниже приведены примеры числовых данных типа float, real, double precision.

 

Таблица 8-1: Числовые данные в научном формате

 

Введенное значение	Мантисса	Показатель	Величина
10Е2	10	2	10*102
15.3е1	15.3	1	15.3*101
-2.е5	-2	5	-2*105
2.2е-1	2.2	-1	2.2*10-1
+56Е+2	56	2	56*102

 

Точность, указанная в столбце одного из этих типов, определяет максимальное число двоичных разрядов, которые выделяются для хранения мантиссы. Для столбцов типа float точность может достигать 48 разрядов, а для столбцов типа real и  double precision точность зависит от реализации. Если вводимое в столбец числовое значение превосходит допустимую точность, SQL Сервер сигнализирует об ошибке.

 

 

Точные числовые типы данных decimal (десятичный) и numeric (числовой) могут начинаться со знака (+ или -) и могут содержать десятичную точку. Числовое значение данных этого типа зависит от точности (precision) и шкалы (scale), которые указываются следующим образом:

 

datatype [(точность [, шкала ])] 

 

SQL Сервер интерпретирует каждую комбинацию значений точности и шкалы как отдельный тип данных. Например, numeric (10,0)  и  numeric (5,0) являются двумя различными типами данных. Точность и шкала определяют диапазон значений, которые могут храниться в столбцах типа decimal и numeric, следующим образом:

 

·           Точность определяет максимальное число десятичных разрядов, которые могут храниться в столбце этого типа. Сюда включаются все разряды, расположенные как слева, так и справа от десятичной точки. Точность представления чисел можно выбрать из диапазона от 1 до 38 разрядов, или использовать по умолчанию точность в 18 разрядов;

·           Шкала определяет максимальное число разрядов, которые могут располагаться справа от десятичной точки в дробной части числа. Заметим, что шкала должна быть меньше или равна точности. Шкалу можно выбрать из диапазона от 0 до 38 разрядов, или использовать по умолчанию нулевую шкалу.

 

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

 

Таблица 8-2: Правильное задание точности и шкалы для числовых данных

 

Входные данные	Тип данных	Точность	Шкала	Значение
12.345	numeric(5,3)	5	3	12.345
-1234.567	dec(8,4)	8	4	-1234.567

 

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

 

Таблица 8-3: Неправильное задание точности и шкалы для числовых данных

 

Входные данные	Тип данных	Точность	Шкала
1234.567	numeric(3,3)	3	3
1234.567	decimal(6)	6	0

 

 

 

Числовые данные в столбцы типа int (целое), smallint (малое целое) и tinyint (маленькое целое) могут вводиться в Е-формате (научном, экспоненциальном), описанном в предыдущем разделе.

 

 

Нельзя вводить значения в столбец timestamp (текущее время). Здесь надо либо явно указать неопределенное значение “NULL”, либо ввести это значение неявно, не указывая в списке столбцов запроса этот столбец. SQL Сервер автоматически обновляет значение времени в этом столбце после каждой вставки или обновления данных. Более детально этот вопрос обсуждается в разделе “Вставка данных в столбцы специального типа” этой главы.

 

 

Команда insert (вставить) используется для добавления строк в базу данных двумя различными способами: с помощью ключевого слова values (значения) или с оператором выбора select.

Ключевое слово values используется для указания значений во всех или некоторых столбцах новой строки. Упрощенный вариант синтаксиса команды insert с ключевым словом  values имеет следующий вид:

 

insert название_таблицы

  values (константа1, константа2, ...)

 

Оператор выбора select используется в команде insert для извлечения данных из других таблиц. Упрощенный вариант синтаксиса команды insert с оператором select имеет следующий вид:

 

insert название_таблицы

  select список_столбцов

  from список_таблиц

  where условия_отбора

 

 

Здесь приведен полный синтаксис команды insert:

 

insert [into] [база данных.[владелец]] { название_таблицы | название_вьювера } [(список_столбцов)]

{values (константное_выражение [, константное_выражение]...) | 

оператор_выбора }

 

Замечание: Если с помощью команды insert вставляются текстовые (text) или графические (image) данные, то все эти данные записываються в журнал транзакций. Команда writetext позволяет добавлять эти данные без  записи их в журнал транзакций, чтобы избежать записи больших масивов данных в журнал, которые могут содержаться в полях этого типа. Этот вопрос обсуждается также в разделах “Вставка данных в столбцы специального типа” и “Изменение текстовых и графических данных” этой главы.   

 

 

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

 

insert into publishers

values ('1622', 'Jardin, Inc.', 'Camden', 'NJ')

 

Следует заметить, что данные вводятся в том же порядке, в каком названия столбцов приведены в операторе create table (создать таблицу), т.е. вначале указывается идентификационный номер автора ID, затем имя и фамилия, город и в заключение - название штата. Список данных, вводимый с помощью ключевого слова values, заключается в скобки и каждый элемент списка заключается в простые или двойные кавычки.

Для добавления каждой новой строки нужно использовать отдельный оператор insert.

 

 

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

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

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

 

insert into publishers (pub_id, pub_name)

values ('1756', 'The Health Center')

 

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

 

insert publishers (pub_name, pub_id)

values ('The Health Center', '1756')

 

В каждом из приведенных операторов insert значение “1756” вводится в столбец идентификационных кодов, а строка ”The Health Center” вводится в столбец названий издателей. Поскольку столбец pub_id в таблице publishers имеет уникальный индекс, то последовательное выполнение двух этих операторов недопустимо, поскольку при выполнении второго оператора в столбце pub_id уже будет записано значение “1756”, что вызовет сообщение об ошибке.

В нижеприведенном операторе выбора select показана строка, которая была добавлена в таблицу publishers:

 

select *

from publishers

where pub_name = 'The Health Center'

 

pub_id      pub_name                city        state

---------  --------------------   --------   -------

1756        The Health Center    NULL    NULL

 

SQL Сервер записал неопределенное значение NULL в столбцы city и state, поскольку в операторе insert для них не было указано никаких значений, а таблица publishers допускает хранение неопределенных значений в этих столбцах.

 

 

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

Следующий оператор insert добавляет новую строку в таблицу sales_daily. Заметим, что в списке столбцов отсутствует счетчиковый столбец-счетчик row_id:

 

insert sales_daily (stor_id)

values ("7896")

 

Следующий оператор показывает строку, которая была добавлена в таблицу sales_daily. SQL Сервер автоматически записал следующее по порядку значение в столбец row_id:

 

select * from sales_daily

where stor_id = "7896"

 

row_id    stor_id

-------   ---------

  2         7896

 

 

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

 

·           В столбец записывается значения по умолчанию, если оно определено для этого столбца. Более детально о значениях по умолчанию можно прочитать в главе 12 “Определение значений по умолчанию и правил” или в разделе об операторе создания значения по умолчанию create default в Справочном Руководстве SQL Сервера;

·           В столбец записывается неопределенное значение, если опция NULL была указана для этого столбца при создании таблицы и с данным столбцом или типом данных не связано никаких значений по умолчанию. Этот вопрос детально обсуждается в разделе об операторе создания таблицы create table  в Справочном Руководстве SQL Сервера;

·           Если столбец является счетчиком (IDENTITY), то в него заносится следующее по порядку уникальное значение; 

·           Если столбец не допускает записи неопределенного значения NULL и с ним не связано никаких значений по умолчанию, то выдается сообщение об ошибке, и новая строка не будет добавлена.

 

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

 

Таблица 8-4: Значение, записываемое в неуказанные столбцы

 

Значение по умолчанию?	Not NULL	NULL	Столбец-счетчик
Да	Значение по умолчанию	Значение по умолчанию	Следующее по порядку значение
Нет	Сообщение об ошибке	NULL	Следующее по порядку значение

 

С помощью системной процедуры sp_help можно получить информацию об определенной таблице,  значении по умолчанию, или о любом другом объекте, находящемся в списке sysobjects (системные объекты). Определение значений по умолчанию можно посмотреть с помощью системной процедуры sp_helptext.

 

 

Иногда может возникнуть необходимость вставить определенное значение в столбец-счетчик, которое отлично от значения, записываемого SQL Сервером. Например, пользователю нужно вставить в таблицу первую строку со значением в столбце-счетчике, равным 101, а не 1, или строку, которая была ошибочно удалена из таблицы.

Записывать значения в столбец-счетчик разрешается только владельцу таблицы, владельцу базы данных или системному администратору. Перед  изменением значения счетчика пользователю следует установить для данной таблицы опцию identity_insert командой set identity_insert on. Заметим, что опция identity_insert может быть установлена в каждый момент времени только для одной таблицы базы данных.

В следующем примере в столбец-счетчик записывается значение 101:

 

set identity_insert sales_daily on

insert into sales_daily (syb_identity, stor_id)

values (101, '13-J-9')

 

Следует заметить, что в этом операторе insert перечисляются названия всех столбцов таблицы, включая столбец-счетчик, для которого указано определенное значение. Когда для данной таблицы включена опция identity_insert, то в каждом операторе insert, который добавляет данные в эту таблицу, должен быть явно указан полный список названий столбцов. В списке значений должно быть указано новое значение счетчика, поскольку в столбец-счетчик нельзя записывать неопределенное значение.

 

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

 

 

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

В качестве примера рассмотрим столбец pub_id таблицы publishers. С ним связано правило pub_idrules, которое определяет допустимые идентификационные коды издателей. Допустимыми являются коды “1389”, “0736”, “0877”, “1622”, или любой четырехзначный код, который начинается с двух цифр “99”. При попытке ввода любых других значений, будет выдано сообщение об ошибке.

Если появилось такое сообщение об ошибке, то следует посмотреть установленные правила  с помощью системной процедуры sp_helptext:

 

sp_helptext pub_idrule

 

text

---------------------------------------------------------

create rule pub_idrule

as @pub_id in ("1389", "0736", "0877", "1622", "1756") or @pub_id like "99[0-9][0-9]"

 

Более общая информация об интересующем пользователя правиле может быть получена с помощью процедуры sp_help. Можно также вызвать процедуру sp_help c названием таблицы в качестве параметра, чтобы выяснить для каких столбцов этой таблицы установлены правила. Более детально правила описываются в главе 12 “Определение умолчаний и правил отбора данных”.

 

 

Для выбора данных из одной или нескольких таблиц и вставки их в другую таблицу используется конструкция select в операторе insert. Конструкция select позволяет записывать значения как в некоторые, так и во все столбцы таблицы.

Запись значений только в часть столбцов очень удобна, когда данные извлекаются из другой таблицы. После этого можно с помощью оператора update (обновить) добавить значения во все остальные столбцы.

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

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

 

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

 

Если столбцы, в которые добавляются значения и из которых они берутся, следуют в том же порядке, в котором они расположены в операторе создания таблицы create table, то пользователь может не указывать названия столбцов в обеих таблицах. Предположим, например, что таблица newauthors содержит сведения об некоторых авторах и имеет ту же структуру, что и таблица authors. Тогда для того, чтобы переписать все строки из таблицы newsauthors в таблицу authors можно выполнить следующий оператор:

 

insert authors

select *

from newauthors

 

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

Например, в операторе create table для таблицы authors столбцы расположены в следующем порядке: au_id, au_fname, au_lname, address, а в таблице newauthors те же столбцы расположены в другом порядке: au_id, address, au_lname, au_fname.  Тогда пользователь должен в операторе insert установить соответствие между однотипными столбцами. Это можно сделать одним из двух следующих  способов:

 

insert authors (au_id, address, au_lname, au_fname)

select * from newauthors

 

insert authors

select au_id, au_fname, au_lname, address

    from newauthors

 

Если же порядок следования столбцов в обеих таблицах не будет одинаковым, то SQL Сервер не сможет закончить выполнение операции insert, или будет выполять ее неправильно, записывая данные не в те столбцы. Например, может оказаться, что адрес записан в столбце au_lname, где должны находиться фамилии.

 

 

В конструкции select оператора insert можно указывать вычисляемые столбцы. Например, предположим, что таблица tmp содержит новые строки для таблицы titles, но некоторые данные в них устарели, к примеру необходимо удвоить цену книги в столбце price (цена). Оператор, который увеличивает цену и вставляет строки из таблицы tmp в таблицу titles, выглядит следующим образом:

 

insert titles

select title_id, title, type, pub_id, price*2,

         advance, total_sales, notes, pubdate, contract

from tmp

 

При указании вычисляемых столбцов нельзя заменять звездочкой (*) названия столбцов в операторе select. В этом случае название каждого столбца должно быть явно указано в списке выбора.

 

 

 

С помощью оператора select можно добавлять данные в некоторые, но не во все столбцы таблицы, так же, как это делалось в конструкции values. В этом случае нужно просто указать в предложении insert названия столбцов, в которые будут записываться данные.

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

 

insert titleauthor (au_id)

select au_id

    from authors

    where au_id not in

    (select au_id from titleauthor)

 

Однако этот оператор является ошибочным, поскольку в столбец title_id таблицы titleauthor необходимо записать некоторое значение, а в нем не допускается использование неопределенного значения и с ним не связано никаких значений по умолчанию. Поэтому для столбца titles_id нужно указать какое-нибудь псевдозначение (dummy), например константу “xx1111”:

 

insert titleauthor (au_id, title_id)

select au_id, "xx1111"

    from authors

    where au_id not in

    (select au_id from titleauthor)

 

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

 

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

 

insert publishers

select "9999", "test", city, state

    from publishers

    where pub_name = "New Age Books"

 

select * from publishers

 

pub_id   pub_name                 city             state

------  -------------------      ------------   ------

0736    New Age Books          Boston         MA

0877    Binnet & Hardley       Washington   DC

1389    Algodata Infosystems   Berkeley       CA

9999    test                            Boston        MA

 

Отсюда видно, что в результате выполнения этого оператора в таблицу вставлена новая строка с константами “9999” и “test” в столбцах pub_id и pub_name соответственно, а значения города и штата скопированы в нее из столбцов city и state строки, которая удовлетворяет условиям запроса.

 

 

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

В операторе update нужно указать строку или строки, содержимое которых должно быть изменено, а также данные, которые записываются в эти строки. Эти данные могут задаваться константами или выражениями, или выбираться из других таблиц.

Если оператор update нарушает целостность данных, то обновления данных не происходит и выдается сообщение об ошибке. Например, выполнение оператора обновления будет прервано, если он пытается изменить значения счетчика, или записывает в столбец данные другого типа, или нарушает правило отбора данных,  установленное для какого-то столбца.

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

Ограничения при обновлении через вьюверы обсуждаются в главе 9: ”Вьюверы: ограниченный доступ к данным”.

 

 Замечание: Ход выполнения команды обновления заносится в журнал транзакций. Если пользователю необходимо обновить большие массивы текстовой или графической информации, то целесообразно использовать команду writetext, поскольку процесс ее выполнения не записывается в журнал операций. Кроме того, существует предел, равный примерно 125К, на объем изменяемых данных в одном операторе обновления. Обсуждение команды writetext можно посмотреть в разделе “Изменение текстовых и графических данных”.

 

 

 

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

 

update название_таблицы

set название_столбца = выражение

where условия_отбора

 

Например, если Reginald Blotchet-Halls решил бы изменить имя и фамилию на Goodbody Health (Хорошее Здоровье), с тем чтобы поднять тираж своей книги по визуализации, то обновление информации можно осуществить следующим образом :

 

update authors

set au_lname = "Health", au_fname = "Goodbody"

where au_lname = "Blotchet-Halls"

 

Далее приведен упрощенный синтаксис команды update, когда обновляемые  данные выбираются из другой таблицы:

 

update название_таблицы

set название_столбца = выражение

  from название_таблицы

  where условия_отбора

 

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

 

update titles

set total_sales = total_sales + qty

from titles, sales, salesdetail

where titles.title_id = salesdetail.title_id

and salesdetail.stor_id = sales.stor_id

and sales.date in (select max(sales.date) from sales)

 

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

 

update [[база_данных.]владелец.]{ название_таблицы | название_вьювера}

 set    [[[база_данных.]владелец.]{ название_таблицы. | название_вьювера. }]

 название _столбца1 = {выражение1 | null | (оператор_выбора) }

     [,название _столбца2 = {выражение2 | null | (оператор_выбора) }]...

 [from  [[база_данных.]владелец.]{ название_таблицы | название_вьювера }

       [, [[база_данных.]владелец.]{ название_таблицы | название_вьювера }]]...

 [where условия_отбора ]

 

 

 

В конструкции set указываются названия обновляемых столбцов и новые значения этих столбцов. Условия, указываемые в конструкции where, позволяют отобрать строки, значения в которых должны быть обновлены. Следует заметить, что  если конструкция where отсутствует, то значения, указанные в конструкции set, будут записаны во все строки таблицы.

 

Замечание: Перед тем, как начать выполнение примеров, приведенных в этом разделе, нужно знать как переинсталлировать (вернуть в исходное состояние) демонстрационную базу данных pubs2.

 

Например, если все издательства из таблицы publishers перенесут свои штаб-квартиры (оффисы) в Атланту (штат Джорджия), то для изменения соответствующих данных в этой таблице можно выполнить следующий оператор:

 

update publishers

set city = "Atlanta", state = "GA"

 

Таким же образом можно сделать неопределенным названия всех издательств:

 

update publishers

set pub_name = null

 

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

 

update titles

set price = price * 2

 

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

 

 

Конструкция where отбирает строки, которые нужно обновить. Например, если предположить, что произошло невероятное событие и из северной Калифорнии выделился штат Pacifica (сокращенно PC), а жители города Окленда  проголосовали за изменение названия их города на что-нибудь экстравагантное типа Big Bad Bay City (большой плохой прибрежный город), то следующий оператор обновляет в таблице authors адреса жителей бывшего города Окленда:

 

update authors

set state = "PC", city = "Big Bad Bay City"

where state = "CA" and city = "Oakland"

 

Для изменения названия штата у жителей других городов северной Калифорнии следует выполнить еще один оператор обновления.

 

 

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

Например, ранее был приведен пример вставки новых строк в таблицу titleauthor, содержащих информация об авторах, у которых еще нет опубликованных книг. При этом в столбец au_id записывался код автора, а в остальные столбцы записывалось либо пседозначение, либо неопределенное значение. Если один из писателей, например, Дюк Стрингер (Dirk Stringer) опубликовал книгу The Psychology of Computer Cooking (Психология компьютерной кулинарии), то его книге присваивается идентификационный код в таблице titles. Теперь можно обновить строку этого автора в таблице titleauthor, добавив код его книги:

 

update titleauthor

set title_id = titles.title_id

from titleauthor, titles, authors

    where titles.title =

    "The Psychology of Computer Cooking"

    and authors.au_id = titleauthor.au_id

    and au_lname = "Stringer"

 

Заметим, что, если бы в этом операторе обновления не было соединения по столбцу au_id, то изменились бы коды всех книг в столбце title_ids таблицы titleauthor, т.е. всем книгам в этой таблице присвоился бы код книги The Psychology of Computer Cooking. Заметим также, что, если две таблицы имеют одинаковую структуру, за исключением того, что в одной таблице содержатся неопределенные значения, а в другой они не допускаются, то нельзя вставлять данные из первой таблицы во вторую с помощью конструкции select. Другими словами, поле, не допускающее неопределенные значения, не может быть обновлено путем выбора значений из поля, в котором они допускаются, если по крайней мере, одно из выбираемых значений является неопределенным.

 

 

Команда writetext (запись текста) предназначается для изменения текстовых (text) и графических (image) данных без записи их в журнал транзакций, чтобы не увеличивать его объема. Дело в том, что процесс выполнения команды обновления (update), которая также может использоваться для изменения этих данных, всегда фиксируется в журнале транзакций (логируется). В нормальном режиме (по умолчанию) команда writetext не логируется, т.е. предыдущее состояние базы данных не запоминается в журнале транзакций.

  

Замечание: Чтобы команда writetext выполнялась в нормальном режиме, т.е. не логировалась, системный администратор должен с помощью системной процедуры sp_dboption установить опцию select into/bulkcopy. Это позволяет производить изменение данных без их логирования. После использования команды writetext необходимо запустить команду dump database. Сброс журнала транзакций (transaction dump) невозможен после нелогированных изменений базы данных при выполнении операций, которые фиксируются в этом журнале.

 

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

 

·           Командой insert вставить в это поле (столбец) текстовые или графические данные;

·           Обновить поле командой update, записав туда данные или NULL.

 

Поскольку “инициализированное” текстовое поле требует 2К памяти, даже если пользователь записывает туда всего несколько слов, то SQL Сервер экономит память и не инициализирует текстовое поле, когда туда вставляется командой insert явное или неявное неопределенное значение. Следующая команда не инициализируют текстовое поле:

 

insert blurbs 

values ("172-32-1176", NULL)

 

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

 

update blurbs

set copy=NULL

where au_id="172-32-1176"

 

После того как поле инициализировано и появился текстовый указатель можно выполнять команду writetext. В следующем примере текст записывается в уже существующую строку таблицы blurbs:

 

declare @val varbinary(16)

select @val = textptr(copy) from blurbs

where  au_id="172-32-1176"

writetext blurbs.copy @val  "This book is a must for true data junkies."

 

В этом примере текстовый указатель записывается в локальную переменную @val, а затем команда writetext записывает новый текст в строку, на которую указывает тестовый указатель.

 

 

Так же как и операторы insert и update, оператор delete (удалить) может модифицировать как одну, так и несколько строк, но является  более удобным для удаления сразу нескольких строк. Как и во всех операторах модификации данных, при удалении строк можно использовать данные из других таблиц.

Например, если пользователь решил удалить одну строку из таблицы publishers, которая относится к издательству Jardin Inc., то следует выполнить следующий оператор:

 

delete publishers

where pub_name = "Jardin, Inc."

 

 

Упрощенный вариант синтаксиса команды delete выглядит следующим образом:

 

delete название_таблицы

where название_столбца = выражение

 

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

 

 

delete [from][[база_данных.]владелец.]{ название_таблицы  | название_вьювера }

    [from [[база_данных.]владелец.]{ название_таблицы | название_вьювера }             

    [, [[база_данных.]владелец.]{ название_таблицы | название_вьювера }]...]                                      

    [where условия_отбора ]  

 

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

 

 

В конструкции where определяются строки, которые нужно удалить. Если в операторе удаления эта конструкция отсутствует, то удаляются все строки таблицы.

 

 

Конструкция from, расположенная на второй позиции в операторе delete, является специальным расширением языка Transact-SQL, которая позволяет выбирать данные из других таблиц и в соответствии с ними удалять данные из исходной таблицы. Фактически. данные, которые выбираются с помощью этой конструкции, формируют условия удаления исходных данных.

Предположим, что некоторая компания изучает результаты публикации книг, написанных авторами из Окленда (который здесь называется Big Bad Bay City). Необходимо удалить все эти книги из таблицы titles, но при этом неизвестны ни названия книг, ни их идентификационные коды. Известны только имена авторов и их адреса.

Чтобы удалить книги из таблицы titles, нужно предварительно найти в  таблице authors коды авторов, живущих в Окленде, и использовать их для поиска кодов книг этих авторов в таблице titleauthor. Другими словами, необходимо провести тройное соединение (соединение по трем таблицам), чтобы найти в таблице titles, книги подлежащие удалению.

Эти три таблицы нужно включить в конструкцию from оператора удаления. Однако, удаляться будут только те строки, которые удовлетворяют условию, указанному в конструкции where. Для удаления соответствующих данных в других таблицах нужно использовать отдельный (другой) оператор удаления.

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

 

delete titles

from authors, titles, titleauthor

where titles.title_id = titleauthor.title_id

and authors.au_id = titleauthor.au_id

and city = "Big Bad Bay City"

 

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

 

 

Команду truncate table (очистить таблицу) можно использовать для быстрого удаления всех строк из таблицы. Она работает почти всегда быстрее чем оператор delete, в котором нет никаких условий, поскольку при выполнении оператора delete в журнале транзакций фиксируется каждое изменение, в то время как при выполнении команды truncate table в журнале запоминаются все страницы данных, занимаемые таблицей. Эта команда сразу освобождает всю память, занимаемую табличными данными и индексами. После этого освобожденная память может использоваться другими объектами базы данных. Освобождаются также распределенные страницы всех индексов. Не следует забывать о команде update statistics (обновление статистики), которую надо запускать после добавления новых строк в таблицу.

Таблица, освобожденная от данных оператором delete или truncate table, остается в базе данных вместе со своими индексами и всеми другими ассоциированными объектами, до тех пор пока не будет выполнен оператор drop table (удалить таблицу).

 

 

Синтаксис команды truncate table имеет следующий вид:

 

truncate table [[база_данных.]владелец.] название_таблицы 

 

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

 

truncate table sales

 

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

Команда truncate table не запускает триггер удаления. Этот вопрос более подробно рассматривается в главе 15 “Триггера: обеспечение ссылочной целостности данных.

 

[ << Назад ] [ Содержание ] [ Далее >> ]