Операторы манипулирования данными -- операторы добавления, изменения и удаления записей.

Insert – добавление информации к таблице

INSERT INTO <имя_таблицы> [ (<имя_столбца>,...) ] VALUES

{ 1. [ (<значение>,..) ]

2. |[ < SELECT -запрос> ]

3. |[ DEFAULT VALUES ] }

Синтаксис строки 1 позволяет ввести только одну строку в таблицу. Если вводятся все поля записи, то список столбцов можно не задавать. Вместо значения поля можно указывать DEFAULT . Оператор ввода данных с помощью синтаксиса строки 2 позволяет ввести сразу множество строк, если их можно выбрать из некоторой другой таблицы. При этом порядок следования столбцов и тип данных в столбцах должных совпадать. Строка 3 используется, если создаётся запись с значениями по умолчанию.

Примеры: INSERT INTO publishers VALUES (16," Microsoft Press "," http :// www . microsoft . com ");

INSERT INTO Authors (au_id, au_lname, au_fname) VALUES (666,'Бездомный','Иван')

INSERT INTO Addresses (name, phone, address) VALUES (

SELECT name, phone, address FROM tmp_table)

UPDATE – обновление информации в таблице

UPDATE <имя_таблицы> SET <имя_столбца> = <значение>,...

[ FROM {<имя таблицы> | < SELECT -запрос>},…]

[ WHERE <условие>]

За один вызов UPDATE можно изменить данные в одном или нескольких столбцах для одной или множества записей только в одной таблице. С ключевого слова SET начинается блок, в котором определяется список изменяемых столбцов. Синтаксис <значение>:=={<константа> | <переменная> | <выражение> | DEFAULT | NULL }. Изменению подвергаются все строки, удовлетворяющие критериям ограничения области действия запроса UPDATE , которые задаются с помощью раздела WHERE .

UPDATE Authors SET address = “ Садовая 13 А , кв . 50” WHERE au_lname = ” Берлиоз ” AND au_fname = ” Михаил ”

UPDATE publishers SET url=" сайт неизвестен " WHERE url IS NULL

Если в инструкции UPDATE будет пропущен раздел WHERE , то заданные в разделе SET изменения будут сделаны в каждой строке таблицы. Имеет смысл для проверки сначала выполнить инструкцию типа SELECT Count (*) с тем же критерием WHERE , чтобы узнать, сколько строк будет изменено с помощью UPDATE . При составлении выражения можно ссылаться на любые столбцы таблицы, включая изменяемые.

UPDATE Titles SET price=price+10

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

Пример: в таблице subjects ( subject , fo , name ) в поле fo указан номер федерального округа, к которому принадлежит данный субъект федерации. В частично заполненной таблице peoples ( xsubject , xfo , value ) осталось незаполненным поле xfo с тем же смыслом. Как восстановить недостающую информацию ?

UPDATE Peoples SET xfo = fo FROM subjects WHERE xsubject=subject

DELETE – удаление информации из таблицы

DELETE FROM <имя_таблицы> [ WHERE <условие> ]

С помощью DELETE можно удалить как отдельные строки, так и множество строк. Без WHERE будут удалены все строки таблицы. В разделе WHERE также можно использовать вложенные подзапросы.

DELETE FROM publishers WHERE publisher = "Microsoft Press"

Удалить из таблицы students студентов, имеющих две и более двойки по результатам экзаменов (если это позволяют ограничения целостности):

DELETE FROM students WHERE id_student IN (

SELECT id_student FROM testing WHERE result=2 GROUP BY id_student HAVING count(*)>2 ).

2.5. Элементы Microsoft SQL Server: представления, хранимые процедуры, пользовательские функции, ограничения, триггеры – примеры создания, использования и удаления средствами языка. Использование временных таблиц.

Представления (Views):

Представление для пользователей базы данных выглядит как таблица, однако на самом деле его содержимое формируется запросом. Физически данные, виртуально принадлежащие представлению, находятся в таблицах, к которым обращается этот запрос. Однако для клиентов MS SQL Server запросы на выборку данных из представления выполняются как для полноценной таблицы. Представление может быть использовано: (1) для защиты конфиденциальной информации; (2) для упрощения доступа к информации и (3) сокращения времени доступа. Для таких целей представление может быть проиндексировано.

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

Недостатки: (1) представление не может ссылаться на временные таблицы, и невозможно создать временные представления; (2) в запросе, определяющим представление, нельзя использовать разделы ORDER BY и INTO; (3) имеется ряд ограничений на изменение, добавление и удаление данных в представлении, созданном для нескольких таблиц.

Команды TSQL, используемые для работы с представлениями:

CREATE VIEW <имя представления> AS <SELECT-запрос> – создание представления.

CREATE VIEW My_view AS SELECT au_lname, au_fname, address

FROM authors WHERE state=’CA’

ALTER VIEW <имя представления> AS <SELECT-запрос> – изменение представления (может потребоваться, например, в случае, если нежелательно изменять права доступа к уже существовавшему представлению.

DROP VIEW <имя представления>, …– удаление представлений (одного или нескольких)

Хранимая процедура sp_help возвращает информацию о различных параметрах представления, в качестве единственного аргумента которой указывается имя представления. Эта же процедура возвращает и об объектах других типов – таблицах, триггерах, индексах, хранимых процедурах и др.

Хранимые процедуры (Stored Procedures): являются основным механизмом, с помощью которого регулируются вся работа с базами данных на сервере. Хранимая процедура – это именованный набор команд TSQL , хранящийся непосредственно на сервере и представляющий собой самостоятельный объект базы данных.

В состав MS SQL Server 2000 входит большое количество встроенных системных хранимых процедур. Все они имеют префикс sp _ и охватывают практически все аспекты управления и конфигурирования сервера, позволяя изменять значения в системных таблицах пользовательских и системных баз данных. Хранимые процедуры существуют независимо от таблиц или каких-либо других объектов баз данных. Хранимая процедура может быть вызвана клиентской программой, другой хранимой процедурой или триггером. Когда хранимая процедура выполняется первый раз, сервер создаёт план исполнения процедуры, выполняет её оптимизацию и компиляцию. В дальнейшем при повторном вызове процедуры используется уже сгенерированный план, что позволяет оптимизировать её время исполнения. Хранение процедур в том же месте, где они исполняются, позволяет уменьшить объём передаваемых по сети данных и повышает общую производительность системы. Обычно приложение-клиент в целях безопасности имеет доступ к данным только через аппарат хранимых процедур.

Создание хранимой процедуры: CREATE PROC [ EDURE ] <имя> [;<число>] [{@<переменная-параметр> <тип данных>} [ VARYING ] [= <значение по умолчанию>] [ OUTPUT ] ,…] [ WITH { RECOMPILE | ENCRIPTION }] AS <набор выполняемых SQL -инструкций> [ RETURN ] Здесь <число> – возможность указать идентификационный номер хранимой процедуры; OUTPUT – наличие этого ключевого слова будет означать, что соответствующий параметр предназначается для возвращения данных из хранимой процедуры (при этом параметр может быть использован и для передачи данных в хранимую процедуру); VARYING – ключевое слово, которое используется совместно с параметром OUTPUT , имеющим тип данных Cursor , которое определяет, что в качестве выходного параметра будет представлено результирующее множество; RECOMPILE – план выполнения процедуры создаётся при каждом её вызове. ENCRIPTION – выполняется кодирование хранимой процедуры при записи в системные таблицы. Для возврата из хранимой процедуры можно использовать команду RETURN . Запуск хранимой процедуры осуществляется указанием её имени и, если необходимо, со списком значений параметров. При вызове процедуры из тела другой процедуры используется следующий синтаксис: EXEC [ UTE ] <имя процедуры> {[[@<имя параметра> =] <значение> | @<имя переменной> [ OUTPUT ] | DEFAULT ],…}.

Пример пакета : -- описываем хранимую процедуру

CREATE PROCEDURE MyProc

@lastname char(64), @firstname char(64) AS

SELECT a.au_lname, a.au_fname, t.title

FROM authors a, titles t, titleauthors ta

WHERE a.au_lname = @lastname AND a.au_fname = @firstname

AND a.au_id = ta.au_id AND t.title_id = ta.title_id

-- создаём хранимую процедуру

GO

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

MyProg 'Иван','Бездомный'

Выполнить созданную процедуру может только владелец базы данных, он же может изменить разрешение доступа и позволить другим пользователям работать с этой процедурой. Уровень вложенности хранимых процедур (вызовы одна из другой) – 32. Процедура как результат своей работы может возвратить (1) выборку из таблиц, (2) значения параметров, заданных как OUTPUT, (3) код завершения, который может генерироваться командой RETURN n . Просмотр кода процедуры выполняется с помощью системной процедуры sp _ helptext , а контрольную информацию о ней можно вывести с помощью процедуры sp _ help <имя процедуры>. Удаление хранимой процедуры осуществляется командой DROP PROCEDURE <имя процедуры>, … Изменение имени хранимой процедуры осуществляется системной процедурой sp _ rename .

Для модификации хранимой процедуры используется команда ALTER PROCEDURE <имя процедуры>. Фактически эта команда аналогична CREATE PROCEDURE , только сделанные ранее административные разрешения сохраняются. Для редактирования хранимой процедуры лучше использовать средства, предоставляемые центром управления MS Access Enterprise Manager .

Триггеры (Triggers): Триггером в SQL Server называется специальная хранимая процедура, привязанная к конкретной таблице (представлению) и запускаемая сервером автоматически при обращении к этой таблице. Когда пользователь, например, успешно изменил данные в таблице, сервер автоматически запускает триггер, причём если произойдёт откат триггера, то это повлечёт и отмену пользовательских изменений данных.

Триггеры могут использоваться (1) для нестандартного контроля целостности данных, (2) для вычисления значений в полях таблицы по значениям других полей, (3) для ограничения действий различных групп пользователей. Существует три типа триггеров – соответственно для команд INSERT , UPDATE и DELETE . Внутри триггера допускается использование практически любых команд TSQL , включая вызовы хранимых процедур и обращение к функциям пользователя. Пример: триггер для таблицы authors , который будет запрещать вставку новых строк в таблицу, выдавая при этом сообщение «Вставка строк запрещена».

CREATE TRIGGER auth_tr ON authors

FOR INSERT AS

PRINT ” Вставка строк запрещена ”

ROLLBACK TRAN

Пользовательские функции (User - defined functions): появились только в SQL Server 2000 и представляют собой развитие аппарата хранимых процедур, с возможностью их вызова непосредственно из выражений (как это принято для встроенных функций) и способных возвращать результат (в том числе как множество записей). В теле функций разрешается объявление локальных переменных, использование циклов, ветвлений и любых других программных конструкций, разрешается вызов других функций и хранимых процедур.

Функции не могут возвращать данные непосредственно клиенту, как это может делать хранимая процедура. То есть, не разрешается использование в теле функции команды PRINT , а также команды SELECT для непосредственного возвращения данных.

Пример создания и вызова функции вида inline (всегда возвращающей RecordSet ):

CREATE FUNCTION MyFunc ( @State char(2) )

RETURNS TABLE AS

RETURN SELECT au_id, au_lname, au_fname

FROM authors

WHERE state = @state

GO

SELECT * FROM MyFunc('CA') ORDER BY au_lname, au_fname

Правила (Rules): Используются для ограничения значений, хранимых в столбце таблицы или в пользовательском типе данных. Правила существуют как самостоятельные объекты баз данных, но работают только тогда, когда связываются со столбцом таблицы или пользовательским типом данных. Одно и то же правило может быть связано с несколькими столбцами разных таблиц, но для одного столбца можно определить только одно правило.

Пример: создание правила, выполняющего проверку 15 <= x <= 75 :

CREATE RULE MyRule AS @exp BETWEEN 15 AND 75

-- свяжем это правило со столбцом price таблицы titles

sp_bindrule MyRule 'titles.price'

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

Для освобождения столбца от правила используется хранимая процедура sp _ unbindrule <имя объекта> . Для удаления правила используется команда DROP RULE . Перед удалением правило должно быть освобождено от всех столбцов.

Умолчания (Default): Умолчание – механизм, задающий значение для столбца в случае, если оно не было определено при вставке строки. В качестве значения по умолчанию может быть указана константа или значение, возвращаемое ф-ей. Подобно правилам, умолчания явл-ся самостоят-ми объектами БД.

Пример создания умолчания и связывания его со столбцом:

CREATE DEFAULT MyDef AS ' неизвестно '

sp_bindefault MyDef 'titles.title'

Тип данных значения по умолчанию должен совп. с типом данных столбца или должно быть возможным неявное преобразование значение к типу данных столбца. Для освобождения столбца от умолчания исп-ся хранимая процедура sp _ unbindefault <имя объекта> . Для удал. умолч. исп-ся команда DROP DEFAULT .

Временные таблицы (Temporary tables): бывают локальные и глобальные. Названия локальных таблиц следует начинать с символа #. Такие таблицы существуют до тех пор, пока действует соединение с SQL Server , в котором эти таблицы были созданы, и автоматически уничтожаются при закрытии соединения. Локальные таблицы видимы только для соединения, создавшего их. Названия глобальных таблиц начинаются с символов ##. Существуют эти таблицы так же, как и локальные, однако во время своего существования являются видимыми и из любого другого соединения с сервером. Имя глобальной таблицы должно быть уникальным для сервера.

Ограничения целостности (Constraints):

Ограничения являются интенсивно развиваемой разработкой SQL Server и предназначены заменить правила и умолчания. Ограничения могут быть определены как на уровне столбца, так и на уровне таблицы в целом.

Частично ограничения целостности были рассмотрены ранее для команд CREATE TABLE и ALTER TABLE. В этих командах после (необязательного) ключевого слова CONSTRAINT для отдельного столбца или таблицы в целом могут идти следующие типы ограничений:

• NULL | NOT NULL

• PRIMARY KEY

• UNIQUE

• FOREIGN KEY … REFERENCES

• ON DELETE

• ON UPDATE

• CHECK <логическое выражение> – контроль вводимых значений каким-либо логическим выражением.

Пример:

CREATE TABLE publichers (

pub_id int NOT NULL PRIMARY KEY,

pub_name varchar(40) DEFAULT (’неизвестно’)

CHECK (pub_id LIKE ’99[0-9][0-9]’)

)