- •1.3. Понятия реляционной модели: атом, домен, кортеж, отношение. Термины реляционной модели и теории множеств. Свойства реляционных баз данных. Реляционная алгебра. Операции над множествами. Примеры.
- •1Nf. Отношение находится в первой нормальной форме тогда и только тогда, когда значения всех его атрибутов атомарны.
- •3Nf. Отношение находится в третьей нормальной форме тогда и только тогда, когда оно находится во второй нормальной форме и не содержит транзитивных зависимостей.
- •2.2.История развития и стандарты языка sql. Наборы команд sql и примеры операторов. Типы данных, управляющие конструкции языка Transact-sql.
- •Операторы манипулирования данными -- операторы добавления, изменения и удаления записей.
- •Insert – добавление информации к таблице
- •2.6. Курсоры в Microsoft sql Server: примеры создания, использования и удаления.
- •2.8 Субд Oracle – сравнение с другими сетевыми реляционными субд, состав, архитектура, типы данных, язык, примеры запросов. Язык sql3 – способы работы с объектами в реляционных бд.
- •2.9 Субд MySql – сравнение с другими сетевыми реляционными субд, интерфейсы управления и администрирования, организация данных, типы данных, язык, примеры запросов.
- •3.1 Ретроспектива развития однопользовательских субд. Сравнительная характеристика современных однопользовательских субд.
- •Типы переменных:
- •Массивы
- •Объекты
- •Доступность
- •Обработка ошибок
- •Параметры процедур и функций.
- •Чтобы задать свойство раздела формы или отчета
- •4.2. Способы организации Интернет-доступа к сетевым субд. Примеры программирования активных серверных страниц для организации доступа к субд в технологиях asp, asp.Net, php.
- •5.1. Недостатки и ограничения реляционной модели. Постреляционные бд, примеры. Введение объектной модели в язык sql3. Примеры sql-запросов, содержащих объекты.
- •5.2. Идея ообд. Преимущества и недостатки объектно-ориентированных баз данных. Стандарт odmg: общие сведения.
- •Преимущества:
- •Недостатки:
Операторы манипулирования данными -- операторы добавления, изменения и удаления записей.
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]’)
)