Http://tuning-jeep.ru/ http://tuning-jeep.ru/ порог рено дастер. tuning-jeep.ru |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Часть 2. Централизованные базы данных
Раздел 3. Реализация бд (модели БД)
5.
Глава 5. Реляционные БД SQL
5.1.
Логическая структура
В основе реляционной модели лежит математическое понятие теоретико-множественного отношения, которое представляет собой подмножество декартова произведения списка доменов.
Элементы отношения называются Совокупность схем отношений называется Данные из диаграммы объектов-связей представляются двумя видами отношений.
Реляционная модель есть представление БД в виде совокупности упорядоченных нормализованных отношений. Для реляционных отношений характерны следующие особенности.
Пример 5.1. Представим БД «Учебный процесс»в виде реляционной модели (табл. 5.1). Таблица 5.1. а) Отношение ГРУППА
б) Отношение СТУДЕНТ
в) Отношение КАФЕДРА
г) Отношение ПРЕПОДАВАТЕЛЬ
д) Отношение ПРЕДМЕТ
е) Отношение ИЗУЧЕНИЕ
ж) Отношение УСПЕВАЕМОСТЬ
ГРУППА(Индекс, Название, Количество_студентов, Проходной_балл), где подчеркнут ключ. Процедуры создания и использования реляционных БД основываются на теории реляционных БД, подробно рассмотренной в главе 4, результаты которой используем здесь в прикладных целях. При введении структуры данных используют соответствующие форматы данных. Для таблицы ПРЕПОДАВАТЕЛЬ они представлены в табл. 5.2. Таблица 5.2. Форматы отношения ПРЕПОДАВАТЕЛЬ
![]() 5.2.
Создание БД
Основная задача при проектировании Для формализации процесса построения оптимальной реляционной БД используется теория нормализации, основанная на том, что определенный набор отношений обладает лучшими свойствами при включении, модификации и удалении данных, чем все остальные наборы отношений, с помощью которых могут быть представлены те же данные (глава 4). Нормализация осуществляется последовательно с использованием пяти нормальных форм, включая форму Бойса-Кодда. 5.3.
Использование БД
На этом «бумажное»построение БД заканчивается. Компьютерная реализация Они могут базироваться на 5.3.1.
Язык SQL
Для SQL имеется много вариантов и диалектов. Здесь изложим основные положения базового варианта: более подробное описание языка приведено в Иллюстрацию языка SQL проведем на примере базы данных, представленной в табл. 5.3 - 5.5. Таблица 5.3. ПРОДАВЦЫ
Названия полей: ПНОМ - уникальный нмер, назначенный каждому
продавцу (номер служащего); Таблица 5.4. ЗАКАЗЧИКИ
Названия полей: ЗНОМ - уникальный номер, назначенный каждому
заказчику; Таблица 5.5. ЗАКАЗЫ
Названия полей: ПРНОМ - уникальный номер, данный каждому
приобретению; ![]() В языке SQL возможно выделить три основные группы операций: создание (CREATE), обновление (INSERT, UPDATE, DELETE), запрос (SELECT). Они имеют следующие стандарты, в которых приняты обозначения: | - все, что предшествует символу, можно заменить тем, что следует за ним; {} - единое целое для применения символа; [] - необязательное выражение; ... - повторяется произвольное число раз ; .,... - повторяется произвольное число раз, но любое вхождение отделяется запятой. CREATE TABLE <имя таблицы> ({<имя столбца><тип данных> [размер] [<тип столбца> ...]}.,...); [<тип таблицы>].,..); (5.1) Типы данных могут быть INTEGER, CHARACTER, DECIMAL, NUMERIC, SMALLINT, FLOAT, REAL, PRECISION, LONG, VARCHAR, DATE, TIME. Четыре последние типа не входят в стандарт SQL, но им могут поддерживаться. Тип столбца (и тип таблиц) может быть UNIQUE, PRIMARY KEY, CHECK <предикат>, DEFAULT=<список полей>, REFERENCE <имя таблицы> [(<имя столбца>.,...)]. INSERT INTO <имя таблицы>[(<имя столбца>.,...)] (5.2) {VALUES(<список полей>).,...} DELETE <имя предиката> (5.3) [WHERE <предикат> |WHERE CURRENT OF <имя курсора> (*только вложенный*)]; SELECT * |[{DISTINCT|ALL]<список полей> (5.4) FROM <имя таблицы> [<алиас>]} ... [WHERE <предикат>] [GROUP BY {<имя столбца>|<целое>}.,...]} [HAVING <предикат>] [ORDER BY {<имя столбца>}]|<целое>}...] [{UNION}]; Выделяют две разновидности языка SQL: интерактивный и вложенный.
Будет рассматривать преимущественно - при отсутствии упоминаний - интерактивный язык. В нем возможно выделить: DDL (Язык Описания Данных) - это язык описания схемы, и в ANSI он состоит из команд, создающих объекты (таблицы, индексы, виды) в базе данных; DML (Язык Манипулирования Данными) - это набор команд, определяющих, какие значения представлены в таблицах в любой момент времени; DCD (Язык Управления Данными) состоит из средств, которые определяют разрешение пользователю выполнять определенные действия. По своей сути язык SQL является специфическим декларативным языком запроса, в связи с чем наибольшее число комбинаций имеет место для процедуры SELECT. Однако более удобно расположить команды по технологическому циклу работы с БД: 1) создание БД - структуры таблиц, заполнение БД данными, создание видов, обеспечение целостности, система доступа (разрешений), словарь данных, многопользовательский режим; 2) использование БД - запрос в различных формах (в том числе с обновлением). Отдельно рассмотрим основы Создание БД Структура таблиц. Таблицы («пустые») создаются командой CREATE TABLE (выражение (5.1)). CREATE TABLE Продавцы (пном integer, пимя char (10), (5.5) город char (10), комм decimal); Команда CREATE TABLE в основном определяет имя таблицы, набор имен столбцов, указанных в определенном порядке, типы данных и размеры столбцов. В SQL возможно задание следующих типов данных: integer, character, numeric, decimal, smallint, float, real, double, precision, long, varchar, date, time. Таблицы принадлежат пользователю, который их создал, и имена всех таблиц, принадлежащих данному пользователю, должны отличаться друга от друга, как и имена всех столбцов внутри данной таблицы. Порядок столбцов в таблице определяется порядком, в котором они указаны. Изменение таблицы после ее создания осуществляется командой ALTER TABLE. Обычно она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения. Чтобы добавить столбец к таблице, используют формат: ALTER TABLE <имя таблицы> ADD <имя поля> <тип данных><размер>; Столбец будет добавлен последним и со значением NULL для всех строк таблицы. Удаление производится по команде DROP TABLE <имя таблицы>; Надо быть собственником (т.е. создателем) таблицы, чтобы иметь возможность удалить ее. Аналогично создаются и удаляются индексы. Базовая таблица (5.5) Таблица 5.5. ЗАКАЗЫ
Названия полей: ПРНОМ - уникальный номер, данный каждому
приобретению; Структура и содержание видов. Только что созданная таблица называется базовой. Можно создавать представление (вид, View) - таблицы, содержимое которых берется или выводится из других таблиц. Вид создается командой CREATE VIEW: CREATE VIEW Москва1 AS SELECT * FROM Продавцы WHERE город = "Москва"; Москва1 - представление (вид). Оно используется для целей защиты информации и вычисляется каждый раз при запросе, потому обновление данных автоматическое. Его можно использовать так же, как и любую другую базовую таблицу, однако в процедурах доступа и обновления имеется специфика. Большое количество типов представлений доступно только для чтения. Это означает, что их можно запрашивать, но они не могут подвергаться действиям команд модификации. Имеются некоторые виды запросов, которые недопустимы в определениях представлений: одиночное представление должно основываться на одиночном запросе; ОБЪЕДИНЕНИЕ (UNION) и ОБЪЕДИНЕНИЕ ВСЕГО (UNION ALL), агрегатные функции, DISTINCT в определении, вычисляемые поля не разрешаются в работе с представлениями; упорядочение (ORDER BY) никогда не используется в определении представлений. Удаление представлений осуществляется (его владельцем) командой DROP VIEW <имя вида>. Заполнение БД данными. Значения могут быть помещены и удалены из полей командами языка DML (Язык Манипулирования Данными - ЯМД) INSERT (ВСТАВИТЬ), DELETE (УДАЛИТЬ) - выражения (5.2) и (5.3). Так, например, чтобы ввести строку в таблицу Продавцов, можно использовать следующее условие: INSERT INTO Продавцы VALUES (1001, «Строков», «Москва», .12); Можно вставлять и пустое значение (NULL). Возможно указывать столбцы таблицы в любом порядке, например INSERT INTO Заказчики (город, имя, номер) VALUES («Москва», «Иванов», 2001); Отметим, что столбцы «рейтинг» и «пном» - отсутствуют: эти строки автоматически установлены в значение - по умолчанию. Исключение строк, введенных по ошибке, проводится командой DELETE. Она может удалять только введенные строки, а не индивидуальные значения полей, так что параметр поля является необязательным или недоступным. Чтобы удалить все содержание таблицы Продавцов, надо ввести: DELETE FROM Продавцы; Теперь таблица пуста и ее можно окончательно удалить командой DROP TABLE. Обычно нужно удалить только некоторые определенные строки из таблицы, для чего используется предикат. Например, чтобы удалить данные продавца Козлова из таблицы Продавцы, можно ввести DELETE FROM Продавцы WHERE пном = 1003; Команды INSERT, DELETE совместно с командой UPDATE используются в процедуре обновления при эксплуатации БД. Задание (обеспечение) целостности. Это разновидность команды CREATE TABLE, позволяющая устанавливать ограничения в таблицах. Ограничение столбца вставляется в конец имени столбца после типа данных и перед запятой. Ограничение таблицы помещается в конец имени таблицы после последнего имени столбца, но перед заключительной круглой скобкой: CREATE TABLE <имя таблицы> (<имя столбца><тип данных><ограничение столбца>, (<имя столбца><тип данных><ограничение столбца>, <ограничение таблицы> ( <имя столбца> [, <имя столбца> ]...); Перечислим некоторые ограничения.
Создадим таблицу Заказчиков с полем пном, определенным в качестве внешнего ключа, ссылающегося на таблицу Продавцов: CREATE TABLE Заказчики (зном integer NOT NULL PRIMARY KEY зимя char(10), город char(10), пном integer, FOREIGN KEY (пном) REFERENCES Продавцы (пном); Отметим, что родительский ключ должен быть уникальным и не содержать никаких пустых значений (NULL). Этого недостаточно для родительского ключа при объявлении внешнего ключа. SQL должен быть уверен что двойные значения или пустые значения (NULL) не были введены в родительский ключ, т.е. CREATE TABLE Продавцы (пном integer NOT NULL PRIMARY KEY, пимя char(10) NOT NULL, город char(10),Дкомм decimal); CREATE TABLE ЗаказчикиД( зном integer NOT NULL PRIMARY KEY, зимя char(10) NOT NULL, город char(10),Дпном integer рейтинг integer FOREIGN (пном) REFERENCES Продавцы, UNIQUE (зном, пном); CREATE TABLE ЗаказыД(прном integer NOT NULL PRIMARY KEY, сумпр decimal, датпр date NOT NULL, зном integer NOT NULL пном integer NOT NULL FOREIGN KEY (зном, пном) REFERENCES CUSTOMERS (зном, пном); Таким образом созданы все три таблицы и установлены связи между ними. Если необходимо изменить или удалить текущее ссылочное значение родительского ключа, имеется три возможности: 1) ограничить или запретить изменение (способом ANSI), обозначив, что изменения в родительском ключе ограничены (RESTRICTED); 2) можно сделать изменение в родительском ключе и тем самым автоматические изменения во внешнем ключе, т.е. каскадное изменение (CASCADES); 3) провести изменение в родительском ключе и установить автоматически внешний ключ в NULL (полагая, что NULLS разрешен во внешнем ключе) - пустое изменение внешнего ключа (NULL). Например, обновление и уничтожение CREATE TABLE Заказчики (зном integer NOT NULL PRIMARY KEY, зимя char(10) NOT NULL, город char(10), рейтинг integer, пном integer REFERENCES Продавцы, UPDATE OF CASCADES, DELETE OF Продавцы RESTRICTED); Если теперь попробовать удалить Строков из таблицы Продавцов, команда будет недопустима, пока не будет изменено значение поля пном Заказчиков и Иванов и Крабов - для другого назначенного продавца. В то же время можно изменить значение поля пном для Строкова, а 1009 Иванов и Крабов будут также автоматически изменены. Возможны и NULL (пустые)-изменения. Например, CREATE TABLE Заказы прном integer NOT NULL PRIMARY KEY, сумпр decimal, датпр date NOT NULL, зном integer NOT NULL REFERENCES Заказчики, пном integer REFERENCES Продавцы, UPDATE OF Заказчики CASCADES, DELETE OF Заказчики CASCADES, UPDATE OF Продавцы CASCADES, DELETE OF Продавцы NULLS); Естественно, что в команде DELETE с эффектом NULL в таблице Продавцов ограничение NOT NULL должно быть удалено из поля пном. Система разрешений. Возможна система разрешения (привилегий) или запрета доступа к данным. Напомним, что администраторы баз данных сами создают пользователей и дают им привилегии. Однако и пользователи, которые создают таблицы, сами имеют права на управление этими таблицами.
Рассмотрим основные принципы ее построения на базе Различают следующие приоритеты (по убыванию): роль, пользователь, группа, общий доступ (public). Имеются разрешающие и запрещающие действия. Разрешение дается оператором вида GRANT <вид операции> ON <объект> TO <субъект> [WITH GRANT OPTION]. Последняя строка говорит о передаче права пользования. Для уверенного управления необходимо сочетание Создание пользователя Илья (которому предоставляется возможность создания баз данных), выполняемое администратором БД, определяется командой CREATE USER Илья WITH PRIVILEGES create_db; Лишение этого пользователя привилегий осуществляется командой ALTER USER Илья create_db; или DROP USER Илья; Создание роли (задание пароля) проводится командой CREATE ROLE create_db WITH PASSWORD="12"; а лишение роли - ALTER ROLE create_db; или DROP ROLE create_db; Имеется несколько типов привилегий, соответствующих нескольким типам операций. Привилегии даются и отменяются двумя командами SQL : GRANT (ДОПУСК) и REVOKE (ОТМЕНА). Каждый пользователь в среде SQL имеет специальное идентификационное имя (идентификатор - ID) или номер. ID разрешения - это имя пользователя, и SQL может использовать специальное ключевое слово USER, которое ссылается на идентификатор доступа, связанный с текущей командой. Команда интерпретируется и разрешается (или запрещается). Привилегии объекта связаны одновременно и с пользователями, и с таблицами. Следует помнить, что пользователь, создавший таблицу (любого вида), является владельцем этой таблицы: он имеет все привилегии в этой таблице и может передавать эти привилегии другим пользователям. Привилегии относятся к командам SELECT, INSERT, UPDATE, DELETE, REFERENCES (определение внешнего ключа, который использует один или более столбцов этой таблицы как родительский ключ). К нестандартным командам относятся INDEX (ИНДЕКС), дающий право создавать индекс в таблице, SYNONYM (СИНОНИМ), позволяющий создавать синоним для объекта, и ALTER (ИЗМЕНИТЬ) - для выполнения команды ALTER TABLE в таблице. Механизм SQL назначает пользователям эти привилегии с помощью команды GRANT. Например, пользователь Илья имеет таблицу Заказчиков и хочет позволить пользователю Петр выполнить запрос к ней: GRANT SELECT ON Заказчики TO Петр; Петр может выполнить запросы к таблице Заказчиков, но не может предоставить право SELECT другому пользователю: таблица еще принадлежит Илье. Возможны и групповые привилегии: GRANT SELECT, INSERT ON Заказы TO Илья, Петр; Для команд UPDATE и REFERNCES можно указывать и отдельные поля: GRANT UPDATE (комм) ON Продавцы TO Илья; Для привилегии REFERENCES может быть указан список из одного или более столбцов, для которых ограничена эта привилегия. Например, Илья может предоставить Степану право использовать поля таблицы Заказчиков, как таблицу родительского ключа с помощью такой команды: GRANT REFERENCES (зимя, зном) ON Заказчики TO Степан; SQL поддерживает два аргумента для команды GRANT, которые имеют специальное значение: ALL PRIVILEGES (ВСЕ ПРИВИЛЕГИИ) или просто ALL - для команд и PUBLIC (ОБЩИЕ) - для пользователей. GRANT ALL ON Заказчики TO PUBLIC; Возможность передачи пользователем предоставленных ему привилегий осуществляется предложением WITH GRANT OPTION. Иногда создателю таблицы хочется, чтобы другие пользователи могли получить привилегии в его таблице. Обычно это делается в системах, где один человек или более создает несколько (или все) базовых таблиц в базе данных, а затем передает ответственность за них тем, кто будет фактически с ними работать. SQL позволяет делать это с помощью предложения WITH GRANT OPTION. Пусть Илья передает право Петру на привилегию SELECT в таблице Заказчиков: GRANT SELECT ON Заказчики TO Петр WITH GRANT OPTION; Возможно разрешить выполнение процедуры integ_check GRANT EXECUTE ON PROCEDURE integ_check TO PUBLIC; Привилегия, например, на команду INSERT, может быть отменена: REVOKE INSERT ON заказы FROM Петр; Здесь также можно использовать списки: REVOKE INSERT, DELETE ON Заказчики FROM Петр, Степан; Возможно задание (или отмена) привилегий с помощью рассмотренных ранее видов. Например, CREATE VIEW Москва1 AS SELECT пном, пимя FROM Продавцы; Предоставляет Москва1 привилегию SELECT в виде (представлении), а не в самой таблице Продавцов: GRANT SELECT On Москва1 TO Виктор; Привилегии возможно ограничивать и строками: CREATE VIEW Москва2 AS SELECT * FROM Заказчики WHERE город = «Сочи» WITH CHECK OPTION; GRANT UPDATE ON Москва2 TO Петр; Предложение WITH CHECK OPTION предохраняет Петр от замены значения поля город на любое значение, кроме Сочи. Существует целый ряд вариантов работы с видами В системе любого размера всегда имеются некоторые типы суперпользователей - чаще всего Администратор Базы Данных, или DBA. У него имеются такие системные привилегии: CONNECT (Подключить), RESOURCE (Ресурс) и DBA (Администратор Базы Данных). CONNECT состоит из права зарегистрироваться и права создавать представления и синонимы, RESOURCE состоит из права создавать базовые таблицы, DBA - это привилегия, дающая пользователю высокие полномочия в базе данных. Некоторые системы имеют специального пользователя, иногда называемого SYSADM или SYS (Системный Администратор Базы Данных), который имеет наивысшие полномочия. Команда GRANT, в измененной форме, является пригодной для использования с привилегиями объекта, как и с системными привилегиями: GRANT RESOURCE TO Мирон; Естественно, пользователь Мирон должен быть создан. У пользователя может быть и пароль (например, Иван). Тогда команда имеет вид GRANT CONNECT TO Федор IDENTIFIED BY Иван; что приведет к созданию пользователя с именем Федор, даст ему право регистрироваться и назначит ему пароль Иван. Если нужно запретить пользователю регистрироваться, следует использовать для REVOKE привилегию CONNECT, которая «удаляет» этого пользователя. Запрещение (на создание таблиц в БД newa группе clerck) имеет вид GRANT NOCREATE ON DATABASE newa TO GROUP clerk; Многопользовательский режим. До сих пор подразумевался однопользовательский режим работы БД. SQL позволяет реализовать и многопользовательский режим, в том числе размещение запоминаемых данных, восстановление и сохранение изменений в базе данных, конфигурации вашей базы данных. SQL позволяют создавать синонимы для таблиц (что не является стандартом ANSI).
Петр может создать синоним с именем Москва4 для таблицы с именем Илья.Заказчики с помощью команды CREATE SYNONYM Москва4 FOR Илья.Заказчики; Теперь Петр может использовать таблицу с именем Москва4 в команде точно так же, как ее использует Илья.Заказчики. Чтобы удалить, например, синоним Москва4, когда вместо него уже появился общий синоним Заказчики, Петр может ввести DROP SYNONYM Москва4; Как видно, таблицы БД сохраняются в «именной области пользователя», хотя это никак не отражает их физического расположения. В больших SQL-системах база данных будет разделена на области, так называемые Области Базы Данных или Разделы. Это области сохраняемой информации, которые размещены так, чтобы информация внутри них находилась близко друг к другу для выполнения команд. Системы, которые используют области базы данных, называемые Data Base Spaces (DBS), позволяют с помощью команд SQL обрабатывать эти области как объекты. DBS создаются командами CREATE DBSPACE (СОЗДАТЬ DBS), ACQUIRE DBSPACE (ПОЛУЧИТЬ DBS) или CREATE TABLESPACE (СОЗДАТЬ ТАБЛИЧНУЮ ОБЛАСТЬ) в зависимости от используемой реализации. Одна DBS может вмещать любое число пользователей, и отдельный пользователь может иметь доступ к многим DBS. Привилегия создавать таблицы, хотя и может быть передана по всей базу данных, часто передается в конкретной DBS. Создадим DBS с именем табл1: CREATE DBSPACE табл1 (pctindex 10, pctfree 25); Параметр pctindex определяет, какой процент DBS должен быть оставлен, чтобы сохранять в нем индексы таблиц, рtfree - процент DBS, который оставлен, чтобы расширять таблицам размеры их строк (команда ALTER TABLE может добавлять столбцы или увеличивать размер столбцов, делая каждую строку длиннее). Большинство программ автоматически будут обеспечивать значения по умолчанию, поэтому можно создавать DBS, не определяя эти параметры. DBS может иметь или определенное ограничение размера, или ей может быть позволено расти неограниченно вместе с таблицами. Если DBS создалась, пользователям предоставляются права создавать в ней объекты. Можно, например, предоставить Илье право создать таблицу табл1 с помощью команды GRANT RESOURCE ON табл1 TO Илья; Первый DBS, назначаемый данному пользователю, указывает обычно область, в которой все объекты этого пользователя создаются по умолчанию. Пользователи, имеющие доступ к многочисленным DBS, могут определить, где они хотят разместить определенный объект. При разделении базы данных на DBSы надо иметь в виду типы операций, которые будут часто выполняться. Таблицы, которые будут часто объединяться, или которые имеют одну таблицу, ссылающуюся на другую во внешнем ключе, должны находиться вместе в одной DBS. Команда или группа команд SQL могут быть выполнены или полностью проигнорированы с помощью транзакций. Транзакция начинается всякий раз, когда начинается сеанс с SQL. Все команды, которые введены, будут частью этой транзакции, пока они не завершатся вводом команды COMMIT WORK или команды ROLLBACK WORK. COMMIT может сделать все изменения постоянными с помощью транзакции, а ROLLBACK может откатить их обратно или отменить. Новая транзакция начинается после каждой команды COMMIT или ROLLBACK.
В большинстве реализаций можно установить параметр, называемый AUTOCOMMIT. Он будет автоматически запоминать все действия, которые будут выполняться. Действия, которые приведут к ошибке,всегда будут автоматически «прокручены» обратно. Для фиксации всех можно использовать команду: SET AUTOCOMMIT ON; Вернуться к обычной диалоговой обработке запросов можно с помощью такой команды: SET AUTOCOMMIT OFF; Имеется возможность установки AUTOCOMMIT, которую система выполнит автоматически при регистрации. Обработка одновременных транзакций называется параллелизмом и может приводить к конфликтам. SQL имеет средства управления параллелизмом для точного указания места получения результата: ни одна команда не должна быть выдана, пока предыдущая не будет завершена (включая команды COMMIT или ROLLBACK). Механизм, используемый SQL для управления параллелизмом операций, называется блокировкой. Блокировки задерживают определенные операции в базе данных. Задержанные операции выстраиваются в очередь и выполняются только после снятия блокировки снята. Имеется несколько базовых типов блокировок: распределяемые блокировки, специальные и общие блокировки. Распределяемые (или S-блокировки) могут быть установлены более чем одним пользователем в данный момент времени. Это дает возможность любому числу пользователей обращаться к данным, но не изменять их. Специальные блокировки (или X-блокировки) не позволяют никому вообще, кроме владельца этой блокировки, обращаться к данным. Специальные блокировки используются для команд, которые изменяют содержание или структуру таблицы. Они действуют до конца транзакции. Общие блокировки используются для запросов. Насколько они продолжительны, зависит фактически от уровня изоляции (сколько таблиц будет блокировано). Использование БД Обновление данных. К командам обновления относят INSERT, DELETE, рассмотренные при заполнении БД данными, и UPDATE, изменяющую некоторые или все значения в существующей строке. В команде названо имя используемой таблицы и предложение SET, указывающее на изменение, которое нужно сделать для определенного столбца. Например, чтобы изменить оценки всех заказчиков на 200, можно ввести UPDATE Заказчики SET рейтинг = 200; UPDATE, наподобие DELETE, может работать с предикатами. Например, можно выполнить изменение, одинаковое для всех заказчиков продавца Строков (имеющего пном=1001): UPDATE Заказчики SET рейтинг = 200 WHERE пном = 1001; Можно изменить и несколько столбцов. Пусть продавец Аврорин ушел на пенсию, и надо переназначить его номер новому продавцу: UPDATE Продавцы SET пимя = «Суворов»,город = «Клин»,комм = .10 WHERE пном = 1004; Команда REFERENCES может изменять значения в уже вставленных строках. Запрос. Запрос формируется командой SELECT (выражение (5.4)). В самой простой форме команда SELECT просто извлекает информацию из таблицы. Например, вывод всей таблицы Продавцов (табл. 5.3) Таблица 5.3. ПРОДАВЦЫ
Названия полей: ПНОМ - уникальный нмер, назначенный каждому
продавцу (номер служащего); SELECT пном, пимя, город, комм FROM Продавцы; (5.6) Точка с запятой (;) используется во всех интерактивных командах SQL, чтобы сообщать базе данных, что команда заполнена и готова выполниться. Звездочка (*) в команде может применяться для вывода полного списка столбцов, и предыдущая процедура может выполняться командой SELECT * FROM Продавцы; Имеется большое число вариантов команды SELECT В выражении (5.6) можно задать не все поля или переупорядочить поля. Предложение WHERE команды SELECT позволяет устанавливать предикаты, условие которых может быть или верным, или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы, для которой такое утверждение верно: SELECT пимя, город FROM Продавцы WHERE город = «Москва»; В предложении WHERE используются не только сравнения вида < , >, ≠, =, ≤, ≥, но и области (множества), определяемые выражениямиДIN, BETWEEN, LIKE, IS (NOT) NULL. К агрегатным функциям относят COUNT (количество строк в таблице), SUM (арифметическую сумму всех выбранных значений данного поля), AVG (всех выбранных значений данного поля), MAX или MIN (наибольшее или наименьшее из всех выбранных значений данного поля). Вывод агрегатных функций (вычисляемых полей) имеет такой вид: SELECT SUM (сумпр) FROM Заказы; Он определяет в данном случае сумму всех покупок в таблице Заказов. Аналогична команда для остальных функций. Предложение GROUP BY команды SELECT позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Например, найти наибольшую сумму приобретений, полученную каждым продавцом: SELECT пном, MAX (сумпр) FROM Заказы GROUP BY пном; Получим пном 1001 767.19 1002 1713.23 1003 75.75 1014 1309.95 1007 1098.16. GROUP BY применяет агрегатные функции независимо от серий групп, которые определяются с помощью значения поля в целом. Предложение HAVING определяет критерии, необходимые для удаления определенных групп из вывода. Тогда команда имеет вид SELECT пном, датпр, MAX ((сумпр)) FROM Заказы GROUP BY пном, датпр HAVING MAX ((сумпр)) > 3000.00; Аргументы в предложении HAVING следуют тем же самым правилам, что и в предложении SELECT, состоящей из команд, использующих GROUP BY. Они должны иметь одно значение на группу вывода. В строгой интерпретации ANSI SQL нельзя использовать агрегат агрегата. Если надо выполнить простые числовые вычисления данных (вычисляемые выражения) и затем поместить их в форму, более соответствующую потребностям, то SQL позволяет это сделать. Например, если ввести команду SELECT пном, пимя, город, комм * 100 FROM Продавцы; то в поле комм появятся значения в процентах. Возможно упорядочение вывода по одному или нескольким полям, например по убыванию: SELECT * FROM Заказы ORDER BY зном DESC, сумпр DESC; Вместо имен столбца можно использовать их порядковые номера для указания поля, используемого в упорядочении вывода. Эти номера могут ссылаться не на порядок столбцов в таблице, а на их порядок в выводе: поле, упомянутое в предложении SELECT первым, для ORDER BY - это поле 1, независимо от того, каким по порядку оно стоит в таблице. Например, команда упорядочения по убыванию значения комиссионных такова: SELECT пимя, комм FROM Продавцы GROUP BY 2 DESC;
До сих пор речь шла о работе с одной таблицей, однако возможна работа и с нескольким таблицами. Формат команд меняется мало, при этом можно использовать практически все, что применялось для одиночных таблиц. Создание объединения чаще всего осуществляется командой вида SELECT Заказчики.зимя, Продавцы.пимя, Продавцы.город FROM Продавцы, Заказчики WHERE Продавцы.город = Заказчики.город; Здесь после SELECT указываются через точку имя таблицы и файла:
В указании полей могут использоваться и другие условия, например SELECT пимя, зимя FROM Продавцы, Заказчики WHERE пимя < зимя AND рейтинг < 200;
Аналогично выглядит и команда соединения трех таблиц и более. Заметим, что возможно соединение таблицы самой с собой. Эта схема может использоваться для проверки правильности заполнения таблицы. Для этого используются синонимы таблиц ( Например, найти все пары заказчиков, имеющих одинаковый рейтинг: SELECT перв.зимя, втор.зимя, перв.рейтинг FROM Заказчики перв, Заказчики втор WHERE перв.рейтинг = перв.рейтинг;
В вышеупомянутой команде SQL ведет себя так, как если бы он соединял две таблицы, называемые «первая» и «вторая». Обе они - фактически таблицы Заказчика, но псевдонимы разрешают им быть обработанными независимо. Псевдонимы «первый» и «второй» были установлены в предложении FROM запроса, сразу после имени копии таблицы. Обратите внимание, что псевдонимы могут использоваться в предложении SELECT, даже если они не определены в предложении FROM. Следующая команда будет определять любые ошибки в заполнении: SELECT перв.прном, втор.зном, перв.пном, втор.прном, втор.зном, втор.пном FROM Заказчики перв, Заказчики втор WHERE перв.зном = втор.зном AND перв.пном <> втор.пном; Если заполнение таблицы проведено верно, то вывод будет пустым. При работе с несколькими таблицами запрос может быть составным, использующим или не использующим операции обновления. Внутренние запросы называют подзапросами. Пусть известно имя продавца (Аврорин ), но значение его поля пном неизвестно и надо извлечь все заказы из таблицы Заказов. Тогда SELECT * FROM Заказы WHERE пном = (SELECT пном FROM Продавцы WHERE пимя = «Аврорин»); Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (подзапрос) предложения WHERE. Ответ: пном = 1004. Однако SQL не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предикат прочитал WHERE пном = 1004. Для работы с несколькими таблицами может использоваться и операция объединения (UNION). Например, для получения всех продавцов и заказчиков, размещенных в Москве, можно использовать команду: SELECT пном, пимя FROM ПродавцыД WHERE город = «Москва» UNION SELECT зном, зимя FROM Заказчики WHERE город = «Москва»; Как можно видеть, столбцы, выбранные двумя командами, выведены так, как если бы она была одна. Заголовки столбца исключены, потому что ни один из столбцов, выведенных объединением, не был извлечен непосредственно только из одной таблицы. Только последний запрос заканчивается точкой с запятой. Отсутствие точки с запятой дает понять SQL, что имеется еще один запрос или более. Возможны и запросы с командами обновления, рассмотренными ранее. Вложенный SQL. До сих пор рассматривался Чтобы преодолеть эти ограничения, можно включать SQL в программы, написанные на каком-либо процедурном языке. В качестве такого процедурного языка примем Паскаль, поскольку он прост в понимании и имеет полуофициальный стандарт ANSI. Отметим некоторые ограничения SQL. Логические конструкции типа if ... then («если ... то»), for ... do («для ... выполнить») и while ... repeat («пока ... повторять»), используемые для структур большинства компьютерных программ, здесь отсутствуют. Интерактивный SQL не может делать многие операции со значениями, размещением или распределением их, выводом их на какое-то устройство. Цель вложенного SQL состоит в соединении возможностей декларативного и процедурного языков. Для этого команды SQL помещаются в исходный текст главной программы, которой предшествует фраза EXEC SQL (EXECute SQL). Далее обсуждаются некоторые команды, являющиеся специальными для вложенной формы SQL. Когда вставляются команды SQL в текст программы, написанной на другом языке, надо выполнить прекомпиляцию прежде, чем окончательно ее скомпилировать. Программа, называемая прекомпилятором (или препроцессором), будет просматривать текст программы и преобразовывать команды SQL в форму, удобную для использования базового языка. Затем используется обычный транслятор, чтобы преобразовывать программу из исходного текста в выполняемый код. Иными словами, основная программа вызывает процедуры SQL, которые выбирают параметры из главной программы и возвращают уже обработанные значения в основную программу. Модуль может содержать любое число процедур, каждая из которых состоит из одиночной команды SQL. Идея в том, чтобы процедуры могли работать тем же самым способом, что и процедуры на языке, в который они были вложены. Каждая из программ, использующих вложенный SQL, связана с идентификатором (ID) доступа во время ее выполнения. ID доступа, связанный с программой, должен иметь все привилегии, чтобы выполнять операции SQL, выполняемые в программе. SQL и части базового языка программ будут связываться друг с другом с помощью значений переменных. Разные языки распознают различные типы данных для переменных, и ANSI определяет эквиваленты SQL базового языка Паскаль. Можно использовать переменные из главной программы во вложенных операторах SQL везде, где будут использоваться выражения значений. Главные переменные должны:
Пусть в программе имеются четыре переменные с именами id_num, salesperson, loc и comm. Они содержат значения, которые нужно вставить в таблицу Продавцов. Можно вложить следующую команду SQL в программу: EXEC SQL INSERT INTO Продавцы VALUES (:id_num, :salesperson, :loc, :comm) Обратите внимание, что точка с запятой в конце команды отсутствует. Это потому, что соответствующее завершение для вложенной команды SQL зависит от языка, для которого делается вложение. Команду можно включить в цикл: while not end-of-file (input) do begin readln (id_num, salesperson, loc, comm); EXEC SOL INSERT INTO Salespeople VALUES (:id_num, :salesperson, :loc, :comm); end; Фрагмент программы на ПАСКАЛе определяет цикл, который будет считывать значения из файла, сохранять их в четырех поименованных переменных, сохранять значения этих переменных в таблице Продавцов и затем считывать следующие четыре значения. Все переменные, на которые имеется ссылка в предложениях SQL, должны сначала быть объявлены в SQL DECLARE SECTION (РАЗДЕЛ ОБЪЯВЛЕНИЙ), использующем обычный синтаксис главного языка. Раздел объявлений должен начинаться и кончаться вложенными командами SQL - BEGIN DECLARE SECTION (Начало Раздела Объявлений) и ENDДDECLARE SECTION (Конец Раздела Объявлений), которым предшествует, как обычно, EXEC SQL (Выполнить). Чтобы объявить переменные предыдущего примера, можно ввести: EXEC SQL BEGIN DECLARE SECTION; Var id-num: integer; Salesperson: packed array (1 .. 10) of char; loc: packed array (1 .. 10) of char; comm: real; EXEC SQL END DECLARE SECTION; где Var - заголовок, который предшествует ряду объявляемых переменных и упакованным (или распакованным) массивам. Переместим строку Строков из таблицы Продавцов в переменные главного языка: EXEC SQL SELECT snum, sname, city, comm INTO :id_num, :salesperson, :loc, :comm FROM Salespeople WHERE snum = 1001; Возможно использовать запросы, выводящие многочисленные строки, применяя курсор. Курсор - это вид переменной, которая связана с запросом. Значением этой переменной может быть каждая строка, которая выводится при запросе. Заметим, что язык SQL может использоваться как для построения, так и для запросов, в то время как язык QBE может применяться только для обновления и - в основном - для запросов. 5.3.2.
Язык QBE
Работа на языке SQL проста, но требует знания языка. Для пользователей, совершенно не знакомых с программированием, запрос может осуществляться одним из следующих способов: Первый способ зависит от варианта разработки, второй способ, предложенный С помощью этого способа на экран вызывается одна или несколько таблиц. В первом столбце каждой из них указывается имя таблицы (файла). В столбцах, под именами полей могут быть указаны условия запроса. Переход от одной таблицы к другой осуществляется с помощью указания в соответствующих полях одинаковых подчеркнутых переменных, написанных большими буквами. Выводимые на печать поля указываются символом P (print), точкой и подчеркнутой переменной (например, P.A). Приведем два примера запросов, базирующихся на примере 4.1. Пример 5.2. Получить фамилии студентов 4-го курса кафедры ИиУС, имеющих балл не менее 4 (рис. 5.3). Пример 5.3. Получить фамилии студентов, руководителем которых является профессор кафедры ИиУС (рис. 5.4). Могут быть использованы (в первом столбце запроса) агрегатные функции (SUM, AVG, MAX, MIN, CNT-счетчик). Возможно создавать виды (View). QBE поддерживает справочник таблиц В последнем случае в первом столбце запроса размещают операторы U. (или UPDATE), D. (DELETE), I. (INSERT) для модификации, уничтожения и вставки данных соответственно. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
© Центр дистанционного образования МГУП |