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

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

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

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

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

3. |[ DEFAULT VALUES ] }

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

Примеры: INSERTINTOpublishersVALUES(16,"MicrosoftPress","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 – удаление информации из таблицы

DELETEFROM<имя_таблицы> [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]’)

)