Структура языка SQl

Содержание

Введение

1 Что такое SQL

1.1 SQL

1.2 Структура языка SQL

1.3 Типы данных SQL

2 Операторы SQL

2.1 Запись операторов SQL

2.2 Операторы задания прав доступа в базе данных

2.3 Операторы транзакций и восстановления данных

2.4 Операторы манипуляции данными

3 Разработка запросов

3.1 Связанные подзапросы и объединения

3.2 Подготовка запросов и параметры запроса

Заключение

Глоссарий

Список использованных источников

Приложение А

Введение

Современная жизнь немыслима без эффективного управления. Важной категорией являются системы обработки информации, от которых во многом зависит эффективность работы любого предприятия или учреждения. Такая система должна:

1.        Обеспечивать получение общих и/или детализированных отчетов по итогам работы;

2.        Позволять легко определять тенденции изменения важнейших показателей;

3.        Обеспечивать получение информации, критической по времени, без существенных задержек;

4.        Выполнять точный и полный анализ данных.

Современные СУБД в основном являются приложениями Windows, так как данная среда позволяет более полно использовать возможности персональной ЭВМ. Снижение стоимости высокопроизводительных ПК обусловил не только широкий переход к среде Windows, где разработчик программного обеспечения может в меньшей степени заботиться о распределении ресурсов, но также сделал программное обеспечение ПК в целом и СУБД в частности менее критичными к аппаратным ресурсам ЭВМ.[1]

Среди наиболее ярких представителей систем управления базами данных можно отметить: Lotus Approach, Microsoft Access, Borland dBase, Borland Paradox, Microsoft Visual FoxPro, Microsoft Visual Basic, а также Microsoft SQL Server и Oracle, используемые в приложениях, построенных по технологии «клиент-сервер». Фактически, у любой современной СУБД существует аналог, выпускаемый другой компанией, имеющий аналогичную область применения и возможности, любое приложение способно работать со многими форматами представления данных, осуществлять экспорт и импорт данных благодаря наличию большого числа конвертеров. Общепринятыми, также, являются технологии, позволяющие использовать возможности других приложений, например, текстовых процессоров, пакетов построения графиков и т.п., и встроенные версии языков высокого уровня (чаще – диалекты SQL и/или VBA) и средства визуального программирования интерфейсов разрабатываемых приложений. Поэтому уже не имеет существенного значения, на каком языке и на основе какого пакета написано конкретное приложение, и какой формат данных в нем используется. Более того, стандартом «де-факто» стала «быстрая разработка приложений» или RAD (от английского Rapid Application Development), основанная на широко декларируемом в литературе «открытом подходе», то есть необходимость и возможность использования различных прикладных программ и технологий для разработки более гибких и мощных систем обработки данных. Поэтому в одном ряду с «классическими» СУБД все чаще упоминаются языки программирования Visual Basic и Visual C++, которые позволяют быстро создавать необходимые компоненты приложений, критичные по скорости работы, которые трудно, а иногда невозможно разработать средствами «классических» СУБД. Современный подход к управлению базами данных подразумевает также широкое использование технологии «клиент-сервер».

Таким образом, на сегодняшний день разработчик не связан рамками какого-либо конкретного пакета, а в зависимости от поставленной задачи может использовать самые разные приложения. Поэтому, более важным представляется общее направление развития СУБД и других средств разработки приложений в настоящее время.

1 Что такое SQL

1.1 SQL

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

Cхема работы SQL.(приложение А)

Согласно этой схеме, в вычислительной системе имеется база данных, в которой хранится важная информация. Если пользователю необходимо прочитать данные из базы данных, он запрашивает их у SQL с помощью СУБД. SQL обрабатывает запрос, находит требуемые данные и посылает их пользователю. Процесс запрашивания данных и получения результата называется запросом к базе данных: отсюда и название — структурированный язык запросов.[2]

Однако это название не совсем соответствует действительности. Cегодня SQL представляет собой нечто гораздо большее, чем простой инструмент создания запросов, хотя именно для этого он и был первоначально предназначен. Несмотря на то, что чтение данных по-прежнему остается одной из наиболее важных функций SQL, сейчас этот язык используется для реализации всех функциональных возможностей, которые СУБД предоставляет пользователю, а именно:

1. Организация данных. SQL дает пользовател возможность изменять структуру представления данных, а также устанавливать отношения между элементами базы данных.

2. Чтение данных. SQL дает пользователю или приложению возможность читать из базы данных содержащиеся в ней данные и пользоваться ими.

3. Обработка данных. SQL дает пользователю или приложению возможность

4. изменять базу данных, т.е. добавлять в нее новые данные, а также удалять

5. или обновлять уже имеющиеся в ней данные.

6. Управление доступом. С помощью SQL можно ограничить возможности

пользователя по чтению и изменению данных и защитить их от

несанкционированного доступа.

7. Совместное использование данных. SQL координирует совместное использование данных пользователями, работающими параллельно, чтобы они не мешали друг другу.

8. Целостность данных. SQL позволяет обеспечить целостность базы данных, защищая ее от разрушения из-за несогласованных изменений или отказа системы.

1.2 Структура языка SQL

Все операторы языка SQL можно разделить на несколько типов:

Операторы манипулирования данных - позволяют управлять значениями, представляемыми в таблицах.

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

Операторы модификации данных - позволяют создавать/удалять базовые таблицы и изменять их структуру (добавлять столбцы, связи между таблицами).

Операторы защиты данных - позволяют авторизировать пользователей, давать им привилегии (разрешения на просмотр данных из одной или нескольких таблиц, изменение данных и т.д.).

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

Оператор ошибки - после выполнения последнего SQL запроса содержит код выполнения операции (код ошибки либо удачного выполнения).

Все используемые в языке SQL операторы представляют собой предложения, содержащие определенные операторы и их операнды. В некоторых операторах

возможна вложенность нескольких операций.[3]

Таким образом, пользователю необходимо сформировать текстовую строку, содержащую операторы SQL и с помощью функции используемого им языка программирования высокого уровня отправить этот запрос на SQL Server, хранящий базу данных. В результате от SQL Server придет ответ, содержащий запрошенные данные из одной или нескольких таблиц, удовлетворяющие условию запроса, отсортированные в нужном порядке, либо произведет запрашиваемые действия над данными.

1.3 Типы данных SQL

Символьные типы данных - содержат буквы, цифры и специальные символы.

CHAR или CHAR(n) - символьные строки фиксированной длины. Длина строки определяется параметром n. CHAR без параметра соответствует CHAR(1). Для хранения таких данных всегда отводится n байт вне зависимости от реальной длины строки.

VARCHAR(n) - символьная строка переменной длины. Для хранения данных этого типа отводится число байт, соответствующее реальной длине строки.

Целые типы данных - поддерживают только целые числа (дробные части и десятичные точки не допускаются). Над этими типами разрешается выполнять арифметические операции и применять к ним агрегирующие функции (определение максимального, минимального, среднего и суммарного значения столбца реляционной таблицы).

INTEGER или INT - целое, для хранения которого отводится, как правило, 4 байта. Интервал значений от -2147483647 до + 2147483648

SMALLINT - короткое целое (2 байта), интервал значений от -32767 до +32768

Вещественные типы данных - описывают числа с дробной частью.

FLOAT и SMALLFLOAT - числа с плавающей точкой (для хранения отводит-

ся обычно 8 и 4 байта соответственно).

DECIMAL(p) - тип данных аналогичный FLOAT с числом значащих цифр p.

DECIMAL(p,n) - аналогично предыдущему, p - общее количество десятичных цифр, n - количество цифр после десятичной запятой.

Денежные типы данных - описывают, естественно, денежные величины. Если ваша система такого типа данных не поддерживает, то используйте DECIMAL(p,n).

MONEY(p,n) - все аналогично типу DECIMAL(p,n). Вводится только потому, что некоторые СУБД предусматривают для него специальные методы форматирования.

Дата и время - используются для хранения даты, времени и их комбинаций. Большинство СУБД умеет определять интервал между двумя датами, а также уменьшать или увеличивать дату на определенное количество времени.

DATE - тип данных для хранения даты.

TIME - тип данных для хранения времени.

INTERVAL - тип данных для хранения временного интервала.

DATETIME - тип данных для хранения моментов времени (год + месяц + день + часы + минуты + секунды + доли секунд).

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

BINARY

BYTE

BLOB

Последовательные типы данных - используются для представления возрастающих числовых последовательностей.

SERIAL - тип данных на основе INTEGER, позволяющий сформировать уникальное значение (например, для первичного ключа). При добавлении записи СУБД автоматически присваивает полю данного типа значение, получаемое из возрастающей последовательности целых чисел.

В заключение следует сказать, что для всех типов данных имеется общее

значение NULL - "не определено". Это значение имеет каждый элемент столбца до тех пор, пока в него не будут введены данные. При создании таблицы можно явно указать СУБД могут ли элементы того или иного столбца иметь значения NULL.

2 Операторы SQL

2.1 Запись операторов SQL

В этом разделе кратко описана структура операторов SQL и представлена система обозначений, которая используется для определения формата различных конструкций языка SQL. Оператор SQL состоит из зарезервированных слов, а также из слов, определяемых пользователем. Зарезервированные слова являются постоянной частью языка SQL и имеют определенное значение. Их следует записывать именно так, как указано в стандарте, и нельзя разбивать на части для переноса из одной строки в другую. Слова, определяемые пользователем, задаются самим пользователем (в соответствии с определенными синтаксическими правилами) и представляют собой имена различных объектов базы данных — таблиц, столбцов, представлений, индексов и т.д. Слова в операторе размещаются в соответствии с установленными синтаксическими правилами. Хотя в стандарте это не указано, многие диалекты языка SQL требуют задания в конце оператора некоторого символа, обозначающего окончание его текста; как правило, с этой целью используется точка с запятой (;). Большинство компонентов операторов SQL не чувствительно к регистру. Это означает, что могут использоваться любые буквы - как строчные, так и прописные. Одним важным исключением из этого правила являются символьные литералы - данные, которые должны вводиться точно так же, как были введены соответствующие им значения, хранящиеся в базе данных. Например, если в базе данных хранится значение фамилии 'SMITH1 , а в условии поиска указан символьный литерал ' Smith1 , то эта запись не будет найдена. Поскольку язык SQL имеет свободный формат, отдельные операторы SQL и их последовательности будут иметь более удобный для чтения вид при использовании отступов и выравнивания. Рекомендуется придерживаться следующих правил.[4]

Каждая конструкция в операторе должна начинаться с новой строки.

Начало каждой конструкции должно быть обозначено таким же отступом, что

и начало других конструкций оператора.

Если конструкция состоит из нескольких частей, каждая из них должна

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

В этой и следующей главе для определения формата операторов SQL мы будем применять следующую расширенную форму системы обозначений BNF (Backus Naur Form — форма Бэкуса-Наура).

Прописные буквы будут использоваться для записи зарезервированных слов и должны указываться в операторах точно так же, как это будет показано.

Строчные буквы будут использоваться для записи слов, определяемых пользователем.

Вертикальная черта (|) указывает на необходимость выбора одного из нескольких приведенных значений, например a b | с.

Фигурные скобки определяют обязательный элемент, например {а}.

Квадратные скобки определяют необязательный элемент, например [а].

Многоточие (...) используется для указания необязательной возможности повторения конструкции от нуля до нескольких раз, например {а Ь} [,с...]. Эта запись означает, что после а или Ь может следовать от нуля до нескольких повторений с, разделенных запятыми.              На практике для определения структуры базы данных (в основном ее таблиц) используются операторы DDL, а для заполнения этих таблиц данными и выборки из них информации с помощью запросов — операторы DML. В этой главе вначале мы познакомимся с операторами DML и лишь затем обратимся к операторам языка DDL. Подобный подход отражает большую важность операторов DML с точки зрения рядового пользователя.

2.2 Операторы задания прав доступа в базе данных

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

Администратор Базы Данных (имеющий DBA права), а так же пользователь, которому было выдано право выдавать права (Оператором GRANT WITH GRANT

OPTIONS)

REVOKE ALL ON customer FROM PUBLIC

GRANT ALL ON customer TO iwanow, petrow WITH GRANT OPTION

GRANT UPDATE(fname,lname,company, sity),SELECT

ON customer TO PUBLIC

REVOKE CONNECT  FROM sidorowa, root

REVOKE DBA FROM ivanov

Отобрать у вас права DBA (если вы, конечно, им являетесь) может только другой DBA.

На время транзакции все измененные строки автоматически блокируются системой от изменения (но не от просмотра). Вы можете явно локировать всю таблицу целиком, тогда система не будет блокировать строки по отдельности. Вы можете блокировать таблицу целиком не только от изменения но и от просмотра.

BEGIN WORK

LOCK TABLE kadry

        . . .

UNLOCK TABLE kadry

        . . .

LOCK TABLE kadry EXCLUSIVE

Если ваш оператор пытается записать в блокированную другим пользователем строку, то оператор "сваливается". Вы можете установить для своей программы режим "Ждать разблокирования строк".

SET LOCK MODE TO WAIT

2.3 Операторы транзакций и восстановления данных

В базе данных, не имеющей системного журнала невозможно выполнение

транзакций и восстановления до текущей контрольной точки. Поскольку за все хорошее приходится платить, наличие системного журнала у базы данных вызывает заметный рост накладных расходов и замедление работы запросов. К тому же при активной работе с базой системный журнал быстро "распухает". За ним нужно следить и периодически чистить.

Указать базе новый системный журнал.

START  DATABASE zawod  WITH LOG IN "/ARM/log/zawod"

Восстанавливают разрушенную (например из-за сбоя оборудования) базу данных так:

В среде UNIX

1. Уничтожают остатки базы.

2. Заливают вместо них копию базы с ленты (например,  недельной

давности) используя команду cpio или утилиту INFORMIX dbimport.

3.Входят в среду INFORMIX и выполняют операторы:

DATABASE kadry  EXCLUSIVE # чтобы никто не лез

ROLLFORWARD DATABASE kadry # прогнать базу вперед

# по системному журналу

CLOSE DATABASE # теперь всем можно работать

Транзакция

BEGIN WORK # начать транзакцию

   . . . # операторы

IF все нормально THEN COMMIT WORK

ELSE ROLLBACK WORK

END IF

2.4 Операторы манипуляции данными

Следующая группа операторов предназначена для манипулирования данными в таблицах. В нее входят операторы выбора (SELECT) строк из таблицы (или таблиц), уничтожения (DELETE) строк в таблице, вставки (INSERT) строк, и изменения (UPDATE) значений в существующих в таблице строках.

Оператор DELETE. Уничтожить в таблице kadry все строки, в которых номер цеха равен 4, а фамилия кончается на буквы "ов"

DELETE FROM kadry WHERE ceh=4 AND fio MATCHES "*ов"

В результате из списков будут вычеркнуты работники 4-го цеха "Петров", "Иванов", "Сидоров" и т.п. Как видим, INFORMIX предоставляет националистически озабоченным руководителям мощные средства для воплощения в жизнь своих идей.

А этот оператор уничтожит ВСЕ строки в таблице kadry, владельцем которой является moshkow, но не саму таблицу

DELETE FROM moshkow.kadry

Простейшая форма оператора SELECT.

Первый пример находит в таблице kadry строку, в которой столбец tabnum=345. Из этой строки берутся только три указанных столбца.

Второй пример выбирает ВСЕ строки из таблицы ceh, и все столбцы.

SELECT fio, dolvn, zarplata FROM kadry WHERE tabnom=345

SELECT * FROM ceh

SELECT kadry.fio, ceh.nameceh WHERE kadry.nomerceh=ceh.nomerceh

Третий пример выбирает фамилии работников из таблицы кадры, а названия цехов, в которых они работают, из таблицы ceh.

Оператор INSERT может вставить в таблицу одну строку, если используется в форме INSERT INTO ... VALUES, а может вставить в таблицу целый набор строк, выбранных подзапросом SELECT из другой таблицы.

INSERT INTO kadry VALUES (4,0,"Грицько",num,"10/25/1939",NULL)

INSERT INTO customer VALUES (ps_customer.*)

#ps_customer - переменная типа RECORD - аналог структуры в

#языке Си. Этот оператор вставляет значения элементов записи

#ps_customer в соответствующие поля таблицы customer

INSERT INTO kadry (tabnom, fio, nomerceh, dolvnostx)

SELECT  0 , fio, 4, dolvnostx FROM kadryold

WHERE nomerceh=3 AND fio IS NOT NULL

#последний оператор вставляет сразу несколько строк

Если мы хотим, чтобы при вставлении строки в столбец типа SERIAL

автоматически заносилось очередное значение счетчика, нужно вставлять в этот столбец константу 0.

Если не во все столбцы вставляемой строки вносится значение (как это сделано в третьем операторе), то незаполненные столбцы заполняются значением NULL.

В операторах DELETE, UPDATE, SELECT может присутствовать WHERE

предложение, в котором можно задать условия на строки, которые требуется обработать (соответственно уничтожить, изменить или выбрать). Рассмотрим примеры использования WHERE предложения.

Оператор UPDATE меняет значения столбцов, в строках, удовлетворяющим WHERE условию.

UPDATE kadry SET fio="Зыкова" WHERE fio="Гирусова"

UPDATE ceh SET kod_ceha[1,4]=nameceh[5,8] WHERE nomerceh BETWEEN 3 AND 5 OR nameceh IN ("токарный","литейный")

В  таблице ceh в цехах номер 3,4,5 а так же в токарном и литейном первые четыре символа в коде цеха будут заменены на подстроку поля nameceh из той же строки.

Предложение WHERE.

Предложение WHERE может присутствовать в любом из операторов DELETE, UPDATE, SELECT, когда нужно задать условия на строки, которые требуется обработать (соответственно, уничтожить, изменить или выбрать). Рассмотрим структуру и примеры использования предложений WHERE.

В предложении WHERE пишется логическое условие, которое получается соединением с помощью логических операторов AND, OR и NOT элементарных сравнений типа:

выражение1 < выражение2,

выражение1 >= выражение2, и т.п.,

а так же элементарных сравнений специального вида:

column-name IS [NOT] NULL

выражение [NOT] BETWEEN выражение1 AND выражение2

выражение [NOT] IN (выраж1 , ...  [, ...] )

Можно выяснить, подходит ли символьная строка под определенный шаблон, или нет. Для этого используются две операции сравнения по шаблону - LIKE и MATCHES.

Символьное выражение MATCHES "шаблон"

Символьное выражение LIKE "шаблон"

LIKE имеет более простой шаблон. В нем используются только два спецсимвола: (%) замещает произвольное количество символов, (_) замещает ровно один символ. Все остальные символы в шаблоне обозначают сами себя. Если мы хотим включить в шаблон % или отменив их специальный смысл, то перед ними надо поставить ESC символ (по умолчанию это (\)).

Допустим нам нужно выбрать из таблицы tab8 все строки, в которых символьный столбец string1 содержит символ "+"  а предпоследняя буква в нем - "Ы". Оператор выборки будет выглядеть так:

SELECT * FROM tab8 WHERE string1 LIKE "%+%Ы_"

MATCHES использует такие спецсимволы шаблона: *,?,[,],^,-.

*     заменяет любое количество символов

?     заменяет один любой символ

[...]   заменяет один символ из перечисленных в скобках возможно указание от и до (-), и не (^)

[abH] - любой из символов a, b, H

[^d-z] - любой символ, исключая d,e,f,g, ... ,y,z

\ отменяет спецсмысл спецсимволов *,?,[,]

Если вы хотите воспользоваться спецсимволами как обычными, примените escape-char. Если escape-char="\", то \? Обозначает просто символ ?, \* обозначает просто символ *, \\ обозначает просто символ \ . Зато знак кавычки (") внутри шаб-лона нужно обозначать двумя кавычками ("").

Выбрать все данные о заказчиках в названии компании которых вторая буква не лежит в интервале от G до L, а третья буква c.(Кстати, коды русских букв на БЕСТЕ идут подряд, но в отличие от латинских букв, русские не упорядочены по алфавиту.)

SELECT * FROM customer WHERE company MATCES"?[^G-L]c*"

Выбрать все данные о заказчиках в названии компании которых присутствует вопросительный знак.

SELECT *  FROM customer

WHERE company MATCHES "*Я?*"  ESCAPE"Я"

В данном примере использовался ESC-символ "Я" для отмены спецсимвола "?"

3 Разработка запросов

3.1 Связанные подзапросы и объединения

Подзапросом называют запрос SELECT, который включается в другой запрос в качестве параметра или выражения.[5] Они обычно используются, чтобы генерировать значение или набор результатов, которые используются в условиях главного запроса.

Соотнесенный подзапрос является единственным отличием между записями для главного запроса, так как только он зависит от значений, которые меняются от записи к записи. Interbase выполняет такой подзапрос много раз, по разу для каждой записи родительского подзапроса. Вычисление каждой записи является большим проигрышем в производительности по отношению к несвязанному подзапросу. Interbase оптимизирует несвязанные подзапросы вне цикле, исполняет их только один раз, используя затем результаты как отдельный набор данных.

Вот пример связанного подзапроса:

SELECT *

FROM DEPARTMENT D

WHERE EXISTS

( SELECT *

FROM EMPLOYEE E

WHERE E.EMP_NO = D.MNGR_NO

AND E.JOB_COUNTRY = 'England')

А вот пример идентичного запроса с использованием объединения:

SELECT D.*

FROM DEPARTMENT D JOIN EMPLOYEE E ON D.MNGR_NO = E.EMP_NO

WHERE E.JOB_COUNTRY = 'England'

План выполнения запроса. План описывает сценарий, по которому оптимизатор решил выполнить запрос. Для некоторых типов запросов оптимизатор не в состоянии выбрать реально оптимальный план. Человек может проанализировать различные планы и, выбрав нужный, перекрыть им план, выбранный оптимизатором. Результатом может быть потрясающее увеличение скорости на некоторых типах запросов. В самых запущенных случаях можно уменьшить время выполнения 15-ти минутного запроса до трёх секунд.

Возможность указывать план запроса была добавлена в GPRE и DSQL/ISQL. Таким образом, прямое указание плана запроса будет работать в теле просмотра(View), хранимой процедуре или триггере.

3.2 Подготовка запросов и параметры запроса

Interbase поддерживает запросы с параметром в DSQL для тех случаев, когда указанный запрос будет выполняться несколько раз с различными значениями. Например, заполнение таблицы данными может требовать серию команд INSERT со значениями для каждой записи. Применение параметризованных запросов напрямую увеличивает производительность, поскольку Interbase хранит внутреннее представление запроса и его оптимизированный план после однократной подготовка запроса.

Использование параметризованных запросов в Delphi происходит следующими шагами:

Поместите поименованный параметр в запросе на месте постоянной величины. Interbase не поддерживает параметры нигде, кроме как в константах, таблицы и имена полей не могут описываться параметром.

Подготовьте запрос, используя для этого метод Prepare компонента TQuery. Delphi подготавливает запрос, если он еще не подготовлен каждый раз перед выполнением. После выполнения запроса его подготовка автоматически снимается. Таким образом, подготовка запроса заранее предотвращает бессмысленную подготовку и отмену подготовки при каждим вызове запроса.

Присвойте параметры. Например, для компонента TQuery используйте для

этого метод ParamByName.

Выполните запрос. Выражения SELECT должны открываться методом Open, а INSERT, UPDATE и DELETE должны активироваться метом ExecSQL.

При необходимости повторить пункты 3 и 4.

Отменить подготовку запроса. Это делается методом Unprepear компонента TQuery.

В некоторых реальных случаях, включающих повторяющиеся действия, использование параметризованных запросов увеличивает производительность на 100%