Содержание
Введение……………………………………………………………………………..4
1.
SQL…………………………………………………………………………………6
1.1 Таблицы SQL……………………………………………………………….…7
1.2 Структура языка SQL……………………………………………………..…8
1.3 Структура запросов SQL……………………………………………………..9
1.4 Операторы модификации данных………………………………………….17
1.5 Транзакции в SQL……………………………………………………….….24
1.6 Защита данных………………………………………………………….……25
1.7 Обработка ошибок…………………………………………………………..27
Заключение…………………………………………………………………………29
Список использованной литературы…………………………………………..30
Приложение А……………………………………………………………………..31
Введение
В настоящее время объемы информации все время возрастают. Наиболее удобным способом хранения информации, на основе опыта нескольких десятилетий, был признан способ хранения информации в виде баз данных.
База данных – это, прежде всего хранилище объектов данных, т.е. набора возможных понятий или событий, описываемых базой данных (БД), Вместе с этим основными функциями БД являются систематизация информации (знаний) и возможность взаимосвязи объектов между собой.
Любую структуру данных можно преобразовать в простую двумерную таблицу. Основная идея реляционного подхода состоит в том, чтобы представить произвольную структуру данных в виде простой двумерной таблицы или, как говорят, нормализовать структуру. И как следствие возникла необходимость использования универсального языка для доступа и управления данными в различных базах данных. Таким языком стал SQL.
Язык для взаимодействия с БД SQL появился в середине 70-х и был разработан в рамках проекта экспериментальной реляционной СУБД System R. Исходное название языка SEQUEL (Structered English Query Language) только частично отражает суть этого языка. Конечно, язык был ориентирован главным образом на удобную и понятную пользователям формулировку запросов к реляционной БД, но на самом деле уже являлся полным языком БД, содержащим помимо операторов формулирования запросов и манипулирования БД средства определения и манипулирования схемой БД. В языке отсутствовали средства синхронизации доступа к объектам БД со стороны параллельно выполняемых транзакций: с самого начала предполагалось, что необходимую синхронизацию неявно выполняет система управления базами данных СУБД [1, с.7].
После появления на рынке двух пионерских СУБД – SQL/DS (1981 год) и DB2 (1983 год) – он приобрел статус стандарта де-факто для профессиональных реляционных СУБД. В 1987 году SQL стал официальным международным стандартом языка баз данных, а в 1992 году вышла вторая версия этого стандарта.
Важной отличительной чертой SQL является его независимость от компьютерной среды (операционной системы и архитектуры). SQL является инструментом, предназначенным для обработки и чтения информации, содержащейся в компьютерной базе данных.
При создании языка запросов нового поколения разработчики старались сделать его простым и легким в освоении инструментом для обращения к БД. В итоге SQL стал слабо структурированным языком, особенно по сравнению с такими языками, как С или Pascal, и в то же время достаточно мощным и относительно легким для изучения [1, с.9].
Одним из наиболее важных шагов на пути к признанию SQL на рынке стало появление стандартов на этот язык. Обычно при упоминании стандарта SQL имеют в виду официальный стандарт, утвержденный Американским институтом национальных стандартов (AmericanNationalStandardsInstitute — ANSI) и Международной организацией по стандартам (InternationalStandardsOrganization— ISO). Однако существуют и другие важные стандарты SQL, включая SQL, реализованный в системе DB2 компании IBM, и стандарт X/OPEN для SQL в среде UNIX. Этот стандарт, незначительно пересмотренный в 1989 году, обычно называют стандартом «SQL-89», или «SQLI».
Пробелы в стандарте SQL-89 и различия между существующими диалектами SQL достаточно значительны, и при переводе приложения под другую СУБД его всегда приходится модифицировать. Эти отличия в большинстве своем устранены в стандарте SQL2.
1.
SQL
SQL является инструментом, предназначенным для обработки и чтения данных, содержащихся в компьютерной базе данных. SQL, является языком программирования
, который применяется для организации взаимодействия пользователя с базой данных.
На рисунке 1 изображена схема работы SQL.
Рисунок 1 – Схема работы SQL
Согласно этой схеме, в вычислительной системе имеется база данных,
в которой хранится важная информация. Если пользователю необходимо прочитать данные из базы данных, он запрашивает их у SQL с помощью СУБД. SQL обрабатывает запрос, находит требуемые данные и посылает их пользователю. Процесс запрашивания данных и получения результата называется запросом
к базе данных: отсюда и название — структурированный язык запросов
[2, с.103].
Однако это название не совсем соответствует действительности. Cегодня SQL представляет собой нечто гораздо большее, чем простой инструмент создания запросов, хотя именно для этого он и был первоначально предназначен. Несмотря на то, что чтение данных по-прежнему остается одной из наиболее важных функций SQL, сейчас этот язык используется для реализации всех функциональных возможностей, которые СУБД предоставляет пользователю, а именно:
1. Организация данных. SQL дает пользователю возможность изменять структуру представления данных, а также устанавливать отношения между элементами базы данных.
2. Чтение данных. SQL дает пользователю или приложению возможность читать из базы данных содержащиеся в ней данные и пользоваться ими.
3. Обработка данных. SQL дает пользователю или приложению возможность изменять базу данных, т.е. добавлять в нее новые данные, а также удалять или обновлять уже имеющиеся в ней данные.
4. Управление доступом. С помощью SQL можно ограничить возможности пользователя по чтению и изменению данных и защитить их от несанкционированного доступа.
5. Совместное использование данных. SQL координирует совместное использование данных пользователями, работающими параллельно, чтобы они не мешали друг другу.
6. Целостность данных. SQL позволяет обеспечить целостность базы данных, защищая ее от разрушения из-за несогласованных изменений или отказа системы.
1.1 Таблицы
SQL
В реляционной базе данных информация организована в виде таблиц
, разделённых на строки и столбцы, на пересечении которых содержатся значения данных [1, с.215].
Основными объектами реляционной базы данных являются:
(TABLE) Таблица –
Прямоугольная таблица, состоящая из СТРОК и СТОЛБЦОВ. Задать таблицу – значит указать, из каких столбцов она состоит.
(ROW) Строка
– Запись, состоящая из полей – столбцов. В каждом поле содержится его значение, либо значение NULL – «пусто». Строк в таблице может быть сколько угодно. Физический порядок их расположения друг относительно друга неопределен.
(COLUMN) Столбец
– Каждый столбец в таблице имеет собственные имя и тип.
1.2 Структура языка
SQL
Все операторы языка SQL можно разделить на несколько типов:
Операторы манипулирования данных
– позволяют управлять значениями, представляемыми в таблицах.
Операторы транзакций
– определяют моменты времени в которые значения базы данных, измененные пользователями становятся актуальными (изменения сделанные одним пользователем вступают в силу для всех пользователей).
Операторы модификации данных
– позволяют создавать/удалять базовые таблицы и изменять их структуру (добавлять столбцы, связи между таблицами).
Операторы защиты данных
– позволяют авторизировать пользователей, давать им привилегии (разрешения на просмотр данных из одной или нескольких таблиц, изменение данных и т.д.).
Оператор запроса
на получение данных – позволяет пользователю получить выбранные определенным образом, данные из одной или нескольких таблиц. Отдельной частью оператора запроса можно выделить оператор курсора
– позволяет просматривать предварительно выбранные данные по одной строке.
Оператор ошибки
– после выполнения последнего SQL запроса содержит код выполнения операции (код ошибки либо удачного выполнения).
Все используемые в языке SQL операторы представляют собой предложения, содержащие определенные операторы и их операнды. В некоторых операторах возможна вложенность нескольких операций [3, с.42].
Таким образом, пользователю необходимо сформировать текстовую строку, содержащую операторы SQL и с помощью функции используемого им языка программирования высокого уровня отправить этот запрос на SQL Server, хранящий базу данных. В результате от SQL Server придет ответ, содержащий запрошенные данные из одной или нескольких таблиц, удовлетворяющие условию запроса, отсортированные в нужном порядке, либо произведет запрашиваемые действия над данными.
1.
3 Структура запросов SQL
Все запросы на получение практически любых данных из одной или нескольких таблиц выполняются с помощью единственного предложения SELECT
.
В синтаксических конструкциях для обращения к БД используются следующие обозначения:
1) звездочка (*) для обозначения «все» – употребляется в обычном для программирования смысле, т.е. «все случаи, удовлетворяющие определению»;
2) квадратные скобки ([]) – означают, что конструкции, заключенные в эти скобки, являются необязательными (т.е. могут быть опущены);
3) фигурные скобки ({}) – означают, что конструкции, заключенные в эти скобки, должны рассматриваться как целые синтаксические единицы, т.е. они позволяют уточнить порядок разбора синтаксических конструкций, заменяя обычные скобки, используемые в синтаксисе SQL;
4) многоточие (…) – указывает на то, что непосредственно предшествующая ему синтаксическая единица факультативно может повторяться один или более раз;
5) прямая черта (|) – означает наличие выбора из двух или более возможностей. Например, обозначение ASC|DESC указывает, можно выбрать один из терминов ASC или DESC; когда же один из элементов выбора заключен в квадратные скобки, то это означает, что он выбирается по умолчанию (так, [ASC]|DESC означает, что отсутствие всей этой конструкции будет восприниматься как выбор ASC);
6) точка с запятой (;) – завершающий элемент предложений SQL;
7) запятая (,) – используется для разделения элементов списков;
8) пробелы ( ) – могут вводиться для повышения наглядности между любыми синтаксическими конструкциями предложений SQL;
9) жирные прописные латинские буквы и символы – используются для написания конструкций языка SQL и должны (если это специально не оговорено) записываться в точности так, как показано-……..;
10) строчные буквы используются для написания конструкций, которые должны заменяться конкретными значениями, выбранными пользователем, причем для определенности отдельные слова этих конструкций связываются между собой символом подчеркивания (_);
11) термины «таблица» и «столбец» заменяют (с целью сокращения текста синтаксических конструкций) термины «имя_таблицы», «имя_столбца», …, соответственно;
12) термин «таблица» – используется для обобщения таких видов таблиц, как базовая_таблица, представление или псевдоним; здесь псевдоним служит для временного (на момент выполнения запроса) переименования и (или) создания рабочей копии базовой_таблицы (представления).
В дальнейшем все примеры приводятся для базы данных приведенной в приложении А.
Оператор SELECT осуществляет выборку из базы данных и имеет наиболее сложную структуру среди всех операторов языка SQL. Простейший оператор SELECT выглядит:
SELECT * FROM PC;
Он осуществляет выборку всех записей из объекта БД табличного типа с именем PC. При этом столбцы и строки результирующего набора не упорядочены. Чтобы упорядочить поля результирующего набора, их следует перечислить через запятую в нужном порядке после слова SELECT:
SELECT price, speed, hd, ram, cd, model, code
FROM Pc;
В таблице 1 приводится результат выполнения этого запроса.
Таблица 1 – Запрос SELECT
price
speed
hd
ram
cd
model
code
600.0
500
5
64
12x
1232
1
850.0
750
14
128
40x
1121
2
600.0
500
5
64
12x
1233
3
850.0
600
14
128
40x
1121
4
850.0
600
8
128
40x
1121
5
950.0
750
20
128
50x
1233
6
400.0
500
10
32
12x
1232
7
350.0
450
8
64
24x
1232
8
350.0
450
10
32
24x
1232
9
350.0
500
10
32
12x
1260
10
980.0
900
40
128
40x
1233
11
Вертикальную проекцию таблицы РC можно получить, если перечислить только необходимые поля. Например, чтобы получить информацию только о скорости процессора и объеме оперативной памяти компьютеров, следует выполнить запрос:
SELECT speed, ram FROM PC;
который вернет следующие данные:
Таблица 2 – Запрос SELECT speed
speed
ram
500
64
750
128
500
64
600
128
600
128
750
128
500
32
450
64
450
32
500
32
900
128
Следует отметить, что вертикальная выборка может содержать дубликаты строк в том случае, если она не содержит потенциального ключа, однозначно определяющего запись[3, с.116]. В таблице PC потенциальным ключом является поле code
, которое выбрано в качестве первичного ключа таблицы. Поскольку это поле отсутствует в запросе, в приведенном выше результирующем наборе имеются дубликаты строк (например, строки 1 и 3). Если требуется получить уникальные строки (скажем, нас интересуют только различные комбинации скорости процессора и объема памяти, а не характеристики всех имеющихся компьютеров), то можно использовать ключевое слово DISTINCT
:
SELECT DISTINCT speed, ram FROM Pc;
что даст такой результат:
Таблица 3 – 1-й результат запроса SELECTDISTINCTspeed
speed
ram
450
32
450
64
500
32
500
64
600
128
750
128
900
128
Помимо DISTINCT может применяться также ключевое слово ALL (все строки), которое принимается по умолчанию.
Чтобы упорядочить строки результирующего набора, можно выполнить сортировку по любому количеству полей, указанных в предложении SELECT. Для этого используется предложение ORDER BY , являющееся всегда последним предложением в операторе SELECT. При этом в списке полей могут указываться как имена полей, так и их порядковые позиции в списке предложения SELECT.
Так если требуется упорядочить результирующий набор по объему оперативной памяти в порядке убывания, можно записать:
SELECT DISTINCT speed, ram
FROM Pc
ORDER BY ram DESC
или
SELECT DISTINCT speed, ram
FROM Pc
ORDER BY 2 DESC
Результат, приведенный ниже, будет одним и тем же.
Таблица 4 – 2-й результат запроса SELECTDISTINCTspeed
speed
ram
600
128
750
128
900
128
450
64
500
64
450
32
500
32
Сортировку можно проводить по возрастанию (параметр ASC принимается по умолчанию) или по убыванию (параметр DESC). Сортировка по двум полям:
SELECT DISTINCT speed, ram
FROM Pc
ORDER BY ram DESC, speed DESC
даст следующий результат:
Таблица 5 – 3-й результат запроса SELECTDISTINCTspeed
speed
ram
900
128
750
128
600
128
500
64
450
64
500
32
450
32
Горизонтальную выборку реализует предложение WHERE , которое записывается после предложения FROM. При этом в результирующий набор попадут только те строки из источника записей, для каждой из которых значение предиката равно TRUE. То есть предикат проверяется для каждой записи.
Предикаты представляют собой выражения, принимающие истинностное значение. Они могут представлять собой как одно выражение, так и любую комбинацию из неограниченного количества выражений, построенную с помощью булевых операторов AND, OR или NOT. Кроме того, в этих комбинациях может использоваться SQL-оператор IS, а также круглые скобки для конкретизации порядка выполнения операций [3, с.209].
Предикат в языке SQL может принимать одно из трех значений TRUE (истина), FALSE (ложь) или UNKNOWN (неизвестно). Исключение составляют следующие предикаты: NULL (отсутствие значения), EXISTS (существование), UNIQUE (уникальность) и MATCH (совпадение), которые не могут принимать значение UNKNOWN.
Правила комбинирования всех трех истинностных значений легче запомнить, обозначив TRUE как 1, FALSE как 0 и UNKNOWN как 1/2 (где то между истинным и ложным).
AND с двумя истинностными значениями дает минимум этих значений. Например, TRUE AND UNKNOWN будет равно UNKNOWN.
OR с двумя истинностными значениями дает максимум этих значений. Например, FALSE OR UNKNOWN будет равно UNKNOWN.
Отрицание истинностного значения равно 1 минус данное истинностное значение. Например, NOT UNKNOWN будет равно UNKNOWN.
Помимо этого используются предикаты сравнения
.
Предикат сравнения представляет собой два выражения, соединяемых оператором сравнения. Имеется шесть традиционных операторов сравнения: =, >, <, >=, <=, <>.
Данные типа NUMERIC (числа) сравниваются в соответствии с их алгебраическим значением.
Данные типа CHARACTER STRING (символьные строки) сравниваются в соответствии с их алфавитной последовательностью. Если a1a2…an и b1b2…bn – две последовательности символов, то первая "меньше" второй, если а1<b1, или а1=b1 и а2<b2 и т.д. Считается также, что а1а2…аn<b1b2…bm, если n<m и а1а2…аn=b1b2…bn, т.е. если первая строка является префиксом второй. Например, ‘folder'<‘for’, т.к. первые две буквы этих строк совпадают, а третья буква строки ‘folder’ предшествует третьей букве строки ‘for’. Также справедливо неравенство ‘bar’ < ‘barber’, поскольку первая строка является префиксом второй.
Данные типа DATETIME (дата/время) сравниваются в хронологическом порядке.
Данные типа INTERVAL (временной интервал) преобразуются в соответствующие типы, а затем сравниваются как обычные числовые значения типа NUMERIC.
Пример. Получить информацию о компьютерах, имеющих частоту процессора не менее 500 Мгц и цену ниже $800:
SELECT * FROM Pc
WHERE speed >= 500 AND price < 800;
Запрос возвращает следующие данные:
Таблица 6 – Пример информационного запроса
code
model
speed
ram
hd
cd
price
1
1232
500
64
5
12x
600.0
3
1233
500
64
5
12x
600.0
7
1232
500
32
10
12x
400.0
10
1260
500
32
10
12x
350.0
Существуют так же и другие предикаты, например BETWEEN
,
IN
, LIKE …
Имена столбцов, указанные в предложении SELECT, можно переименовать. Это делает результаты более читабельными, поскольку имена полей в таблицах часто сокращают с целью упрощения набора. Ключевое слово AS, используемое для переименования, согласно стандарту можно и опустить, т.к. оно неявно подразумевается.
Например, запрос:
SELECT ram AS Mb, hd Gb
FROM Pc
WHERE cd = ’24x’;
переименует столбец ram в Mb (мегабайты), а столбец hd в Gb (гигабайты). Этот запрос возвратит объемы оперативной памяти и жесткого диска для тех компьютеров, которые имеют 24-скоростной CD-ROM:
Таблица 7 – Пример запроса SELECTAS
Mb
Gb
64
8
32
10
Получение итоговых значений:
Существует возможность получения итоговых (агрегатных) функций. Стандартом предусмотрены следующие агрегатные функции:
Таблица 8 – Описание (агрегатных) функции
Функция
Описание
COUNT(*)
Возвращает количество строк источника записей.
COUNT()
Возвращает количество значений в указанном столбце.
SUM()
Возвращает сумму значений в указанном столбце.
AVG()
Возвращает среднее значение в указанном столбце.
MIN()
Возвращает минимальное значение в указанном столбце.
MAX()
Возвращает максимальное значение в указанном столбце.
Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT() состоит в том, что вторая при подсчете не учитывает NULL-значения.
Пример. Найти минимальную и максимальную цену на персональные компьютеры:
SELECT MIN(price) AS Min_price, MAX(price) AS Max_price
FROM PC;
Результатом будет единственная строка, содержащая агрегатные значения:
Таблица 8 – Строка содержащая (агрегатные) значения
Min_price
Max_price
350.0
980.0
Для просмотра данных наиболее удобно использовать совместно значения оператора COUNT
– счетчик (позволяет узнать количество записей в запросе), и оператора CURSOR
– позволяет принимать не все записи сразу а по одной (указанной пользователем).
1.4
Операторы модификации данных
Язык манипуляции данными (DML – Data Manipulation Language) помимо оператора SELECT, осуществляющего извлечение информации из базы данных, включает операторы, изменяющие состояние данных. Этими операторами являются:
INSERT Добавление записей (строк) в таблицу БД
UPDATE Обновление данных в столбце таблицы БД
DELETE Удаление записей из таблицы БД
1) Оператор INSERT
.
Оператор INSERT вставляет новые строки в таблицу. При этом значения столбцов могут представлять собой литеральные константы либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.
Синтаксис оператора:
INSERT INTO <имя таблицы
>[(<имя столбца
>,…)]
{VALUES (< значение столбца
>,…)}
| <выражение запроса
>
| {DEFAULT VALUES};
Как видно из представленного синтаксиса, список столбцов не является обязательным. В том случае, если он отсутствует, список вставляемых значений должен быть полный, т.е. обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку столбцов, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, каждое из этих значений должно быть того же типа (или приводиться к нему), что и тип, определенный для соответствующего столбца в операторе CREATE TABLE[3, с.248].
В качестве примера рассмотрим вставку строки в таблицу Product
, созданную следующим оператором CREATE TABLE:
CREATE TABLE [dbo].[product] (
[maker] [char] (1) NOT NULL ,
[model] [varchar] (4) NOT NULL ,
[type] [varchar] (7) NOT NULL )
Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:
INSERT INTO Product VALUES (‘B’, 1157, ‘PC’);
Если задать список столбцов, то можно изменить "естественный" порядок их следования:
INSERT INTO Product (type, model, maker) VALUES (‘PC’, 1157, ‘B’);
Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы:
CREATE TABLE [product_D] (
[maker] [char] (1) NULL ,
[model] [varchar] (4) NULL ,
[type] [varchar] (7) NOT NULL DEFAULT ‘PC’ )
Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два – NULL, а последний столбец – type
– ‘PC’). Теперь мы могли бы написать:
INSERT INTO Product_D (model, maker) VALUES (1157, ‘B’);
В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию – ‘PC’. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL, запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL.
Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT:
INSERT INTO Product_D VALUES (‘B’, 1158, DEFAULT);
Поскольку все столбцы имеют значения по умолчанию, для вставки строки со значениями по умолчанию можно было бы написать:
INSERT INTO Product_D VALUES (DEFAULT, DEFAULT, DEFAULT);
Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (смотри синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде
INSERT INTO Product_D DEFAULT VALUES;
Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK, ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отвергнута.
Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D
все строки из таблицы Product
, относящиеся к моделям персональных компьютеров (type
= ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:
INSERT INTO Product_D SELECT * FROM Product WHERE type = ‘PC’;
Использование в подзапросе символа "*" является в данном случае оправданным, т.к. порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы использовать список столбцов либо в операторе INSERT, либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов.
Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL. Так если нам требуется вставить несколько строк при помощи одного оператора INSERT, можно написать:
INSERT INTO Product_D
SELECT ‘B’ AS maker, 1158 AS model, ‘PC’ AS type
UNION ALL
SELECT ‘C’, 2190, ‘Laptop’
UNION ALL
SELECT ‘D’, 3219, ‘Printer’;
Использование UNION ALL предпочтительней UNION даже, если гарантировано отсутствие строк-дубликатов, т.к. в этом случае не будет выполняться проверка для исключения дубликатов.
2) Оператор UPDATE.
Оператор UPDATE изменяет имеющиеся данные в таблице. Команда имеет следующий синтаксис
UPDATE
SET {имя столбца = {выражение для вычисления значения столбца
| NULL
| DEFAULT},…}
[ {WHERE }];
С помощью одного оператора могут быть заданы значения для любого количества столбцов. Однако в одном и том же операторе UPDATE можно вносить изменения в каждый столбец указанной таблицы только один раз. При отсутствии предложения WHERE будут обновлены все строки таблицы.
Если столбец допускает NULL-значение, то его можно указать в явном виде. Кроме того, можно заменить имеющееся значение на значение по умолчанию (DEFAULT) для данного столбца.
Ссылка на "выражение" может относиться к текущим значениям в изменяемой таблице. Например, мы можем уменьшить все цены ПК-блокнотов на 10 процентов с помощью следующего оператора:
UPDATE Laptop SET price=price*0.9
Разрешается также значения одних столбцов присваивать другим столбцам. Пусть, например, требуется заменить жесткие диски менее 10 Гб в ПК-блокнотах. При этом емкость новых дисков должна составлять половину объема RAM, имеющейся в данных устройствах. Эту задачу можно решить следующим образом:
UPDATELaptopSEThd=ram/2 WHEREhd
Естественно, типы данных столбцов hd
и ram
должны быть совместимы. Для приведения типов может использоваться выражение cast[4, с.3].
Если требуется изменять данные в зависимости от содержимого некоторого столбца, можно воспользоваться выражением case[4, с.3].
Если, скажем, нужно поставить жесткие диски объемом 20 Гб на ПК-блокноты с памятью менее 128 Мб и 40 гигабайтные – на остальные ПК-блокноты, то можно написать такой запрос:
UPDATELaptop
SEThd = CASEWHENram<128 THEN 20 ELSE 40 END
Для вычисления значений столбцов допускается также использование подзапросов. Например, требуется укомплектовать все ПК-блокноты самыми быстрыми процессорами из имеющихся. Тогда можно написать:
UPDATE Laptop
SET speed = (SELECT MAX(speed) FROM Laptop)
Необходимо сказать несколько слов об автоинкрементируемых столбцах. Если столбец code
в таблице Laptop
определен как IDENTITY(1,1), то поступают следующим образом. Сначала необходимо вставить нужную строку, используя SET IDENTITY_INSERT, после чего удалить старую строку:
SET IDENTITY_INSERT Laptop ON
INSERT INTO Laptop_ID(code, model, speed, ram, hd, price, screen)
SELECT 5, model, speed, ram, hd, price, screen
FROM Laptop_ID WHERE code=4
DELETE FROM Laptop_ID WHERE code=4
Разумеется, другой строки со значением code
=5 в таблице быть не должно.
В Transact-SQL оператор UPDATE расширяет стандарт за счет использования необязательного предложения FROM. В этом предложении специфицируется таблица, обеспечивающая критерий для операции обновления. Дополнительную гибкость здесь дает использование операций соединения таблиц.
Пример
. Пусть требуется указать "No PC" (нет ПК) в столбце type
для тех моделей ПК из таблицы Product
, для которых нет соответствующих строк в таблице PC
. Решение посредством соединения таблиц можно записать так:
UPDATEProduct
SETtype=’NoPC’
FROM Product pr LEFT JOIN PC ON pr.model=pc.model
WHERE type=’pc’ AND pc.model IS NULL
Здесь используется внешнее соединение, в результате чего столбец pc.model
для моделей ПК, отсутствующих в таблице PC
, будет содержать NULL-значение, что и используется для идентификации подлежащих обновлению строк. Естественно, эта задача имеет решение и в "стандартном" исполнении:
UPDATEProduct
SET type=’No PC’
WHERE type=’pc’ and model NOT IN (SELECT model FROM PC)
3) Оператор DELETE
Оператор DELETE удаляет строки из временных или постоянных базовых таблиц, представлений или курсоров, причем в двух последних случаях действие оператора распространяется на те базовые таблицы, из которых извлекались данные в эти представления или курсоры. Оператор удаления имеет простой синтаксис:
DELETE FROM [WHERE ];
Если предложение WHERE отсутствует, удаляются все строки из таблицы или представления (представление должно быть обновляемым). Более быстро эту операцию (удаление всех строк из таблицы) в Transact-SQL можно также выполнить с помощью команды
TRUNCATE TABLE
Однако есть ряд отличий в реализации команды TRUNCATE TABLE по сравнению с использованием оператора DELETE, которые следует иметь в виду:
1. Не журнализируется удаление отдельных строк таблицы. В журнал записывается только освобождение страниц, которые были заняты данными таблицы.
2. Не отрабатывают триггеры. Как следствие, эта команда неприменима, если на данную таблицу имеется ссылка по внешнему ключу.
3. Значение счетчика (IDENTITY
) сбрасывается в начальное значение.
Пример
.
Требуется удалить из таблицы Laptop
все ПК-блокноты с размером экрана менее 12 дюймов.
DELETEFROMLaptop
WHEREscreen
Все блокноты можно удалить с помощью оператора
DELETE FROM Laptop
Или
TRUNCATE TABLE Laptop
Transact-SQL расширяет синтаксис оператора DELETE, вводя дополнительное предложение FROM
4) FROM.
При помощи источника табличного типа
можно конкретизировать данные, удаляемые из таблицы в первом предложении FROM.
При помощи этого предложения можно выполнять соединения таблиц, что логически заменяет использование подзапросов в предложении WHERE для идентификации удаляемых строк.
Поясним сказанное на примере. Пусть требуется удалить те модели ПК из таблицы Product
, для которых нет соответствующих строк в таблице PC
.
Используя стандартный синтаксис, эту задачу можно решить следующим запросом:
DELETE FROM Product
WHERE type=’pc’ AND model NOT IN (SELECT model FROM PC)
Заметим, что предикат type
=’pc’ необходим здесь, чтобы не были удалены также модели принтеров и ПК-блокнотов.
Эту же задачу можно решить с помощью дополнительного предложения FROM следующим образом:
DELETE FROM Product
FROM Product pr LEFT JOIN PC ON pr.model=pc.model
WHERE type=’pc’ AND pc.model IS NULL
Здесь используется внешнее соединение, в результате чего столбец pc.model
для моделей ПК, отсутствующих в таблице PC
, будет содержать NULL-значение, что и используется для идентификации подлежащих удалению строк.
1. 5 Транзакции в SQL
Под SQL-транзакцией (иногда называемой просто транзакцией) понимается последовательность выполнения операторов SQL, являющаяся атомарной по отношению к восстановлению. Эти операции выполняются одной или более единицами компиляции и модулями или путем прямого вызова SQL. От реализации зависит, могут ли в одной транзакции выполняться динамические и/или статические операторы выборки и манипулирования данными и динамические и/или статические операторы определения и манипулирования схемой.
Каждый модуль или прямой вызов SQL, инициирующие выполнение оператора, ассоциируются с транзакцией. SQL-транзакция начинается при выполнении процедуры из некоторого модуля или прямого вызова оператора SQL вне активной транзакции. Транзакция завершается при выполнении операторов COMMIT или ROLLBACK. Если SQL-транзакция завершается успешным выполнением оператора COMMIT, то все изменения, произведенные ею над данными и/или схемой становятся постоянно хранимыми и доступными всем параллельно выполняющимся или образуемым впоследствии транзакциям. Если транзакция завершается оператором ROLLBACK или если выполнение оператора COMMIT оказывается неуспешным, то все изменения, произведенные транзакцией над данными и/или схемой, ликвидируются.
У каждой SQL-транзакции имеется режим доступа – "только чтение" или "чтение и запись". Режим доступа может быть явно установлен оператором SETTRANSACTION; по умолчанию он устанавливается в "чтение-запись". Термин "только чтение" применяется только к постоянно хранимым базовым и представляемым таблицам.
1.6 Защита данных
К операторам защиты данных относят операторы авторизации и привилегий. Синтаксискоманд GRANT и REVOKE
GRANT priv_type [(column_list)] [, priv_type [(column_list)] …]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] ‘password’]
[, user_name [IDENTIFIED BY ‘password’] …]
[REQUIRE
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
MAX_UPDATES_PER_HOUR # |
MAX_CONNECTIONS_PER_HOUR #]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] …]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name …]
Команды GRANT и REVOKE позволяют системным администраторам создавать пользователей MySQL, а также предоставлять права пользователям или лишать их прав на четырех уровнях привилегий:
Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице mysql.user.
Привилегии базы данных применяются ко всем таблицам указанной базы данных. Эти привилегии хранятся в таблицах mysql.db и mysql.host.
Привилегии таблицы применяются ко всем столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql.tables_priv.
Привилегии столбца применяются к отдельным столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql.columns_priv.
Ниже приведен список возможных значений параметра priv_type для операторов GRANT и REVOKE:
ALL [PRIVILEGES] – Задаетвсепростыепривилегии, кроме WITH GRANT OPTION
ALTER – Разрешает использование ALTER TABLE
CREATE – Разрешает использование CREATE TABLE
CREATE TEMPORARY TABLES – Разрешаетиспользование CREATE TEMPORARY TABLE
DELETE – Разрешает использование DELETE
DROP – Разрешает использование DROP TABLE.
EXECUTE – Разрешает пользователю запускать хранимые процедуры
FILE – Разрешаетиспользование SELECT … INTO OUTFILE и LOAD DATA INFILE
INDEX – Разрешаетиспользование CREATE INDEX and DROP INDEX
LOCK TABLES – Разрешает использование LOCK TABLES на таблицах, для которых есть привилегия SELECT.
PROCESS – Разрешаетиспользование SHOW FULL PROCESSLIST
REFERENCES – Зарезервировано для использования в будущем
RELOAD – Разрешает использование FLUSH
REPLICATIONCLIENT – Предоставляет пользователю право запрашивать местонахождение головного и подчиненных серверов
REPLICATION SLAVE – Необходимо для подчиненных серверов при репликации (для чтения информации из бинарных журналов головного сервера).
SELECT – Разрешает использование SELECT
SHOWDATABASES – SHOWDATABASES выводит все базы данных.
SHUTDOWN – Разрешает использование mysqladminshutdown
SUPER – Позволяет установить одно соединение (один раз), даже если достигнуто значение max_connections, и запускать команды CHANGEMASTER, KILLthread, mysqladmindebug, PURGEMASTERLOGS и SETGLOBAL
UPDATE – Разрешает использование UPDATE
USAGE – Синоним “без привилегий” – можно задавать, если необходимо создать пользователя без привилегий.
1.7 Обработка ошибок
При выполнении программных объектов пользователь должен иметь возможность оперативно реагировать на возникающие отклонения от нормального процесса их выполнения. Для решения этой задачи разработчики SQL применили механизм обработки исключительных ситуаций. Данный механизм представляет собой прием, обеспечивающий перехват и обработку ошибок и предупреждений. Исключительные ситуации в SQL возникают при выполнении SQL-операторов. Исключительные ситуации представляются в виде кодов возврата, которые формируются после завершения выполнения операторов. Коды возврата одновременно записываются в системные переменные SQLSTATE и SQLCODE. В этих переменных возникновение исключительных ситуаций кодируются по разному. Так в переменной SQLCODE они представляются в виде десятичных чисел. При этом отрицательные значения соответствуют ошибкам, положительные – предупреждениям, а ноль – успешному завершению оператора. Кодирование исключительных ситуаций в переменной SQLSTATE соответствует стандарту SQL/92. При этом коды возврата представляются в виде строк из пяти символов. В переменной SQLSTATE первые два символа составляют код класса ошибок, следующие три символа – код внутри класса. Код "00000" соответствует успешному выполнению оператора. В зависимости от значений кодов возврата может быть принято решение о повторении оператора, прерывания функционирования приложения и т.д.
ПРИМЕЧАНИЕ.
После считывания значения одной из переменных SQLSTATE или SQLCODE, обе они приводятся в состояние отсутствия ошибок.
Заключение
При написании данной работы мы рассмотрели структуру языка SQL, а так же одну большинство из групп операторов SQL. При рассмотрении были, по возможности, приведены примеры для базы данных, описанной в приложении А. Показаны: область применения и основные функции языка.
В настоящее время язык SQL является стандартом для использования в реляционных базах данных, наиболее современных хранилищах информации. Перспективы их развития БД огромны, но прослеживается четкая тенденция во всех новых СУБД – обязательная поддержка языка SQL.
Список использованной литературы
1. Дейт К.Дж. Введение в системы баз данных. 6-е изд. – М.: Вильямс. 2000. – 317 с.
2. Леонтьев В.П. ПК: универсальный справочник пользователя – М.: 2003. – 251 с.
3. В.В. Фаронов Основы программирования в SQL. – М.: Издатель Молгачева С.В., 2002. – 329 с.
4. http://www.sql-ex.ru/help.
Приложение А
Схема БД Компьютерная фирма
состоит из четырех отношений:
1) Product(maker, model, type);
2) PC(code, model, speed, ram, hd, cd, price);
3) Laptop(code, model, speed, ram, hd, screen, price);
4) Printer(code, model, color, type, price).
Отношение Product представляет производителя (maker), номер модели (model) и тип (PC – ПК, Laptop – ПК-блокнот или Printer – принтер). Предполагается, что номера моделей уникальны для всех производителей и типов продуктов. В отношении PC для каждого номера модели, обозначающего ПК, указаны скорость -speed (процессора в мегагерцах), общий объем RAM (в мегабайтах), размер диска -hd (в гигабайтах), скорость считывающего устройства CD (например, 4х) и цена – price. Отношение Laptop аналогично отношению РС за исключением того, что вместо скорости CD содержится размер экрана -screen (в дюймах). В отношении Printer для каждой модели принтера указывается, является ли он цветным – color (‘y’, если цветной), тип принтера – type (лазерный – Laser, струйный – Jet или матричный – Matrix) и цена.