Язык sql. Язык запросов SQL
Основные команды SQL, которые должен знать каждый программист
Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе реляционных баз данных (RDBMS). В этой статье будет рассказано о часто используемых командах SQL, с которыми должен быть знаком каждый программист. Этот материал идеально подойдёт для тех, кто хочет освежить свои знания об SQL перед собеседованием на работу. Для этого разберите приведённые в статье примеры и вспомните, что проходили на парах по базам данных.
Обратите внимание, что в некоторых системах баз данных требуется указывать точку с запятой в конце каждого оператора. Точка с запятой является стандартным указателем на конец каждого оператора в SQL. В примерах используется MySQL, поэтому точка с запятой требуется.
Настройка базы данных для примеров
Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql . После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды (статья предполагает, что MySQL уже установлен в системе):
Mysql -u root -p
Затем введите пароль.
Выполните следующую команду. Назовём базу данных «university»:
CREATE DATABASE university;
USE university;
SOURCE Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения: Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ. Создайте таблицу «instructor»: CREATE TABLE instructor (ID CHAR(5),
name VARCHAR(20) NOT NULL,
dept_name VARCHAR(20),
salary NUMERIC(8,2),
PRIMARY KEY (ID),
FOREIGN KEY (dept_name) REFERENCES department(dept_name));
Можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы следующей командой: DESCRIBE При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов. INSERT INTO SELECT используется для получения данных из определённой таблицы: SELECT Следующей командой можно вывести все данные из таблицы: SELECT * FROM В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных. SELECT DISTINCT Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе: SELECT В запросе можно задавать следующие условия: Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE: SELECT * FROM course WHERE dept_name=’Comp. Sci.’;
SELECT * FROM course WHERE credits>3;
SELECT * FROM course WHERE dept_name="Comp. Sci." AND credits>3;
Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений. SELECT Выведем количество курсов для каждого факультета: SELECT COUNT(course_id), dept_name
FROM course
GROUP BY dept_name;
Ключевое слово HAVING было добавлено в SQL потому, что WHERE не может быть использовано для работы с агрегатными функциями. SELECT Выведем список факультетов, у которых более одного курса: SELECT COUNT(course_id), dept_name
FROM course
GROUP BY dept_name
HAVING COUNT(course_id)>1;
ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC . SELECT Выведем список курсов по возрастанию и убыванию количества кредитов: SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;
BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты. SELECT Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000: SELECT * FROM instructor
WHERE salary BETWEEN 50000 AND 100000;
Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения. Есть два свободных оператора, которые используются в LIKE: Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-»: SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE "CS-___";
С помощью IN можно указать несколько значений для оператора WHERE: SELECT Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.: SELECT * FROM student
WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);
JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением: SELECT Выведем список всех курсов и соответствующую информацию о факультетах: SELECT * FROM course
JOIN department
ON course.dept_name=department.dept_name;
Выведем список всех обязательных курсов и детали о них: SELECT prereq.course_id, title, dept_name, credits, prereq_id
FROM prereq
LEFT OUTER JOIN course
ON prereq.course_id=course.course_id;
Выведем список всех курсов вне зависимости от того, обязательны они или нет: SELECT course.course_id, title, dept_name, credits, prereq_id
FROM prereq
RIGHT OUTER JOIN course
ON prereq.course_id=course.course_id;
View - это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. View всегда показывает самую свежую информацию из базы данных. Создадим view , состоящую из курсов с 3 кредитами: Эти функции используются для получения совокупного результата, относящегося к рассматриваемым данным. Ниже приведены общеупотребительные агрегированные функции: Вложенные подзапросы - это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос. Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов: SELECT DISTINCT course_id
FROM section
WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (SELECT course_id
FROM section
WHERE semester = ‘Spring’ AND year= 2010);
Для извлечения данных из базы данных используется язык SQL. SQL - это язык программирования, который очень напоминает английский, но предназначен для программ управления базами данных. SQL используется в каждом запросе в Access. Понимание принципов работы SQL помогает создавать более точные запросы и упрощает исправление запросов, которые возвращают неправильные результаты. Это статья из цикла статей о языке SQL для Access. В ней описаны основы использования SQL для выборки данных и приведены примеры синтаксиса SQL. SQL - это язык программирования, предназначенный для работы с наборами фактов и отношениями между ними. В программах управления реляционными базами данных, таких как Microsoft Office Access, язык SQL используется для работы с данными. В отличие от многих языков программирования, SQL удобочитаем и понятен даже новичкам. Как и многие языки программирования, SQL является международным стандартом, признанным такими комитетами по стандартизации, как ISO и ANSI . На языке SQL описываются наборы данных, помогающие получать ответы на вопросы. При использовании SQL необходимо применять правильный синтаксис. Синтаксис - это набор правил, позволяющих правильно сочетать элементы языка. Синтаксис SQL основан на синтаксисе английского языка и имеет много общих элементов с синтаксисом языка Visual Basic для приложений (VBA). Например, простая инструкция SQL, извлекающая список фамилий контактов с именем Mary, может выглядеть следующим образом: SELECT Last_Name Примечание:
Язык SQL используется не только для выполнения операций над данными, но еще и для создания и изменения структуры объектов базы данных, например таблиц. Та часть SQL, которая используется для создания и изменения объектов базы данных, называется языком описания данных DDL. Язык DDL не рассматривается в этой статье. Дополнительные сведения см. в статье Создание и изменение таблиц или индексов с помощью запроса определения данных . Инструкция SELECT служит для описания набора данных на языке SQL. Она содержит полное описание набора данных, которые необходимо получить из базы данных, включая следующее: таблицы, в которых содержатся данные; связи между данными из разных источников; поля или вычисления, на основе которых отбираются данные; условия отбора, которым должны соответствовать данные, включаемые в результат запроса; необходимость и способ сортировки. Инструкция SQL состоит из нескольких частей, называемых предложениями. Каждое предложение в инструкции SQL имеет свое назначение. Некоторые предложения являются обязательными. В приведенной ниже таблице указаны предложения SQL, используемые чаще всего. Предложение SQL
Описание
Обязательное
Определяет поля, которые содержат нужные данные. Определяет таблицы, которые содержат поля, указанные в предложении SELECT. Определяет условия отбора полей, которым должны соответствовать все записи, включаемые в результаты. Определяет порядок сортировки результатов. В инструкции SQL, которая содержит статистические функции, определяет поля, для которых в предложении SELECT не вычисляется сводное значение. Только при наличии таких полей В инструкции SQL, которая содержит статистические функции, определяет условия, применяемые к полям, для которых в предложении SELECT вычисляется сводное значение. Каждое предложение SQL состоит из терминов, которые можно сравнить с частями речи. В приведенной ниже таблице указаны типы терминов SQL. Термин SQL
Сопоставимая часть речи
Определение
Пример
идентификатор существительное Имя, используемое для идентификации объекта базы данных, например имя поля. Клиенты.[НомерТелефона] оператор глагол или наречие Ключевое слово, которое представляет действие или изменяет его. константа существительное Значение, которое не изменяется, например число или NULL. выражение прилагательное Сочетание идентификаторов, операторов, констант и функций, предназначенное для вычисления одного значения. >= Товары.[Цена] Общий формат инструкций SQL: SELECT field_1 Примечания:
Access не учитывает разрывы строк в инструкции SQL. Несмотря на это, каждое предложение рекомендуется начинать с новой строки, чтобы инструкцию SQL было удобно читать как тому, кто ее написал, так и всем остальным. Каждая инструкция SELECT заканчивается точкой с запятой (;). Точка с запятой может стоять как в конце последнего предложения, так и на отдельной строке в конце инструкции SQL. В приведенном ниже примере показано, как в Access может выглядеть инструкция SQL для простого запроса на выборку. 1. Предложение SELECT 2. Предложение FROM 3. Предложение WHERE Разберем пример по предложениям, чтобы понять, как работает синтаксис SQL. SELECT , Company
Это предложение SELECT. Оно содержит оператор (SELECT), за которым следуют два идентификатора ("[Адрес электронной почты]" и "Компания"). Если идентификатор содержит пробелы или специальные знаки (например, "Адрес электронной почты"), он должен быть заключен в прямоугольные скобки. В предложении SELECT не нужно указывать таблицы, в которых содержатся поля, и нельзя задать условия отбора, которым должны соответствовать данные, включаемые в результаты. В инструкции SELECT предложение SELECT всегда стоит перед предложением FROM. FROM Contacts
Это предложение FROM. Оно содержит оператор (FROM), за которым следует идентификатор (Контакты). В предложении FROM не указываются поля для выборки. WHERE City="Seattle"
Это предложение WHERE. Оно содержит оператор (WHERE), за которым следует выражение (Город="Ростов"). С помощью предложений SELECT, FROM и WHERE можно выполнять множество действий. Дополнительные сведения об использовании этих предложений см. в следующих статьях: Как и в Microsoft Excel, в Access можно сортировать результаты запроса в таблице. Используя предложение ORDER BY, вы также можете указать способ сортировки результатов при выполнении запроса. Если используется предложение ORDER BY, оно должно находиться в конце инструкции SQL. Предложение ORDER BY содержит список полей, для которых нужно выполнить сортировку, в том же порядке, в котором будут применена сортировка. Предположим, например, что результаты сначала нужно отсортировать по полю "Компания" в порядке убывания, а затем, если присутствуют записи с одинаковым значением поля "Компания", - отсортировать их по полю "Адрес электронной почты" в порядке возрастания. Предложение ORDER BY будет выглядеть следующим образом: ORDER BY Company DESC,
Примечание:
По умолчанию Access сортирует значения по возрастанию (от А до Я, от наименьшего к наибольшему). Чтобы вместо этого выполнить сортировку значений по убыванию, необходимо указать ключевое слово DESC. Дополнительные сведения о предложении ORDER BY см. в статье Предложение ORDER BY . Иногда возникает необходимость работы со сводными данными, такими как итоговые продажи за месяц или самые дорогие товары на складе. Для этого в предложении SELECT к полю применяется агрегатная функция. Например, если в результате выполнения запроса нужно получить количество адресов электронной почты каждой компании, предложение SELECT может выглядеть следующим образом: Возможность использования той или иной агрегатной функции зависит от типа данных в поле и нужного выражения. Дополнительные сведения о доступных агрегатных функциях см. в статье Статистические функции SQL . При использовании агрегатных функций обычно необходимо создать предложение GROUP BY. В предложении GROUP BY указываются все поля, к которым не применяется агрегатная функция. Если агрегатные функции применяются ко всем полям в запросе, предложение GROUP BY создавать не нужно. Предложение GROUP BY должно следовать сразу же за предложением WHERE или FROM, если предложение WHERE отсутствует. В предложении GROUP BY поля указываются в том же порядке, что и в предложении SELECT. Продолжим предыдущий пример. Пусть в предложении SELECT агрегатная функция применяется только к полю [Адрес электронной почты], тогда предложение GROUP BY будет выглядеть следующим образом: GROUP BY Company
Дополнительные сведения о предложении GROUP BY см. в статье Предложение GROUP BY . Если необходимо указать условия для ограничения результатов, но поле, к которому их требуется применить, используется в агрегированной функции, предложение WHERE использовать нельзя. Вместо него следует использовать предложение HAVING. Предложение HAVING работает так же, как и WHERE, но используется для агрегированных данных. Предположим, например, что к первому полю в предложении SELECT применяется функция AVG (которая вычисляет среднее значение): SELECT COUNT(), Company
Если вы хотите ограничить результаты запроса на основе значения функции COUNT, к этому полю нельзя применить условие отбора в предложении WHERE. Вместо него условие следует поместить в предложение HAVING. Например, если нужно, чтобы запрос возвращал строки только в том случае, если у компании есть несколько адресов электронной почты, можно использовать следующее предложение HAVING: HAVING COUNT()>1
Примечание:
Запрос может включать и предложение WHERE, и предложение HAVING, при этом условия отбора для полей, которые не используются в статистических функциях, указываются в предложении WHERE, а условия для полей, которые используются в статистических функциях, - в предложении HAVING. Дополнительные сведения о предложении HAVING см. в статье Предложение HAVING . Оператор UNION используется для одновременного просмотра всех данных, возвращаемых несколькими сходными запросами на выборку, в виде объединенного набора. Оператор UNION позволяет объединить две инструкции SELECT в одну. Объединяемые инструкции SELECT должны иметь одинаковое число и порядок выходных полей с такими же или совместимыми типами данных. При выполнении запроса данные из каждого набора соответствующих полей объединяются в одно выходное поле, поэтому выходные данные запроса имеют столько же полей, сколько и каждая инструкция SELECT по отдельности. Примечание:
В запросах на объединение числовой и текстовый типы данных являются совместимыми. Используя оператор UNION, можно указать, должны ли в результаты запроса включаться повторяющиеся строки, если таковые имеются. Для этого следует использовать ключевое слово ALL. Запрос на объединение двух инструкций SELECT имеет следующий базовый синтаксис: SELECT field_1 Предположим, например, что имеется две таблицы, которые называются "Товары" и "Услуги". Обе таблицы содержат поля с названием товара или услуги, ценой и сведениями о гарантии, а также поле, в котором указывается эксклюзивность предлагаемого товара или услуги. Несмотря на то, что в таблицах "Продукты" и "Услуги" предусмотрены разные типы гарантий, основная информация одна и та же (предоставляется ли на отдельные продукты или услуги гарантия качества). Для объединения четырех полей из двух таблиц можно использовать следующий запрос на объединение: SELECT name, price, warranty_available, exclusive_offer Дополнительные сведения об объединении инструкций SELECT с помощью оператора UNION см. в статье Если вы похожи на меня, то согласитесь: SQL - это одна из тех штук, которые на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить каждый простой запрос, чтобы найти правильный синтаксис. А потом начинаются джойны, агрегирование, подзапросы, и получается совсем белиберда. Вроде такой: Буэ! Такое спугнет любого новичка, или даже разработчика среднего уровня, если он видит SQL впервые. Но не все так плохо. Легко запомнить то, что интуитивно понятно, и с помощью этого руководства я надеюсь снизить порог входа в SQL для новичков, а уже опытным предложить по-новому взглянуть на SQL. Не смотря на то, что синтаксис SQL почти не отличается в разных базах данных, в этой статье для запросов используется PostgreSQL. Некоторые примеры будут работать в MySQL и других базах.
В SQL много ключевых слов, но SELECT , FROM и WHERE присутствуют практически в каждом запросе. Чуть позже вы поймете, что эти три слова представляют собой самые фундаментальные аспекты построения запросов к базе, а другие, более сложные запросы, являются всего лишь надстройками над ними. Давайте взглянем на базу данных, которую мы будем использовать в качестве примера в этой статье: У нас есть книжная библиотека и люди. Также есть специальная таблица для учета выданных книг. Давайте начнем с простого запроса: нам нужны имена
и идентификаторы
(id) всех книг, написанных автором “Dan Brown” Запрос будет таким: А результат таким: Довольно просто. Давайте разберем запрос чтобы понять, что происходит. Сейчас это может показаться очевидным, но FROM будет очень важен позже, когда мы перейдем к соединениям и подзапросам. FROM указывает на таблицу, по которой нужно делать запрос. Это может быть уже существующая таблица (как в примере выше), или таблица, создаваемая на лету через соединения или подзапросы. WHERE просто-напросто ведет себя как фильтр строк
, которые мы хотим вывести. В нашем случае мы хотим видеть только те строки, где значение в колонке author - это “Dan Brown”. Теперь, когда у нас есть все нужные нам колонки из нужной нам таблицы, нужно решить, как именно показывать эти данные. В нашем случае нужны только названия и идентификаторы книг, так что именно это мы и выберем
с помощью SELECT . Заодно можно переименовать колонку используя AS . Весь запрос можно визуализировать с помощью простой диаграммы: Теперь мы хотим увидеть названия (не обязательно уникальные) всех книг Дэна Брауна, которые были взяты из библиотеки, и когда эти книги нужно вернуть: Результат: По большей части запрос похож на предыдущий за исключением
секции FROM . Это означает, что мы запрашиваем данные из другой таблицы
. Мы не обращаемся ни к таблице “books”, ни к таблице “borrowings”. Вместо этого мы обращаемся к новой таблице
, которая создалась соединением этих двух таблиц. borrowings JOIN books ON borrowings.bookid=books.bookid - это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц "books" и "borrowings", в которых значения bookid совпадают. Результатом такого слияния будет: А потом мы делаем запрос к этой таблице так же, как в примере выше. Это значит, что при соединении таблиц нужно заботиться только о том, как провести это соединение. А потом запрос становится таким же понятным, как в случае с «простым запросом» из пункта 3. Давайте попробуем чуть более сложное соединение с двумя таблицами. Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”. На этот раз давайте пойдем снизу вверх: Шаг Step 1
- откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так: Результат соединения можно увидеть по ссылке . Шаг 2
- какие данные показываем? Нас интересуют только те данные, где автор книги - “Dan Brown” Шаг 3
- как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги: Супер! Осталось лишь объединить три составные части и сделать нужный нам запрос: Что даст нам: Отлично! Но имена повторяются (они не уникальны). Мы скоро это исправим. Грубо говоря, агрегирования нужны для конвертации нескольких строк в одну
. При этом, во время агрегирования для разных колонок используется разная логика. Давайте продолжим наш пример, в котором появляются повторяющиеся имена. Видно, что Ellen Horton взяла больше одной книги, но это не самый лучший способ показать эту информацию. Можно сделать другой запрос: Что даст нам нужный результат: Почти все агрегации идут вместе с выражением GROUP BY . Эта штука превращает таблицу, которую можно было бы получить запросом, в группы таблиц. Каждая группа соответствует уникальному значению (или группе значений) колонки, которую мы указали в GROUP BY . В нашем примере мы конвертируем результат из прошлого упражнения в группу строк. Мы также проводим агрегирование с count , которая конвертирует несколько строк в целое значение (в нашем случае это количество строк). Потом это значение приписывается каждой группе. Каждая строка в результате представляет собой результат агрегирования каждой группы. Можно прийти к логическому выводу, что все поля в результате должны быть или указаны в GROUP BY , или по ним должно производиться агрегирование. Потому что все другие поля могут отличаться друг от друга в разных строках, и если выбирать их SELECT "ом, то непонятно, какие из возможных значений нужно брать. В примере выше функция count обрабатывала все строки (так как мы считали количество строк). Другие функции вроде sum или max обрабатывают только указанные строки. Например, если мы хотим узнать количество книг, написанных каждым автором, то нужен такой запрос: Результат: Здесь функция sum обрабатывает только колонку stock и считает сумму всех значений в каждой группе. Подзапросы это обычные SQL-запросы, встроенные в более крупные запросы. Они делятся на три вида по типу возвращаемого результата. Есть запросы, которые возвращают несколько колонок. Хороший пример это запрос из прошлого упражнения по агрегированию. Будучи подзапросом, он просто вернет еще одну таблицу, по которой можно делать новые запросы. Продолжая предыдущее упражнение, если мы хотим узнать количество книг, написанных автором “Robin Sharma”, то один из возможных способов - использовать подзапросы: Результат: Можно записать как: ["Robin Sharma", "Dan Brown"] 2. Теперь используем этот результат в новом запросе: Результат: Это то же самое, что: Бывают запросы, результатом которых являются всего одна строка и одна колонка. К ним можно относиться как к константным значениям, и их можно использовать везде, где используются значения, например, в операторах сравнения. Их также можно использовать в качестве двумерных таблиц или массивов, состоящих из одного элемента. Давайте, к примеру, получим информацию о всех книгах, количество которых в библиотеке превышает среднее значение в данный момент. Среднее количество можно получить таким образом: Что дает нам: Большинство операций записи в базе данных довольно просты, если сравнивать с более сложными операциями чтения. Синтаксис запроса UPDATE семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок SELECT "ом, мы задаем знаения SET "ом. Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким: WHERE делает то же самое, что раньше: выбирает строки. Вместо SELECT , который использовался при чтении, мы теперь используем SET . Однако, теперь нужно указать не только имя колонки, но и новое значение для этой колонки в выбранных строках. Запрос DELETE это просто запрос SELECT или UPDATE без названий колонок. Серьезно. Как и в случае с SELECT и UPDATE , блок WHERE остается таким же: он выбирает строки, которые нужно удалить. Операция удаления уничтожает всю строку, так что не имеет смысла указывать отдельные колонки. Так что, если мы решим не обнулять количество книг Дэна Брауна, а вообще удалить все записи, то можно сделать такой запрос: Пожалуй, единственное, что отличается от других типов запросов, это INSERT . Формат такой: Где a , b , c это названия колонок, а x , y и z это значения, которые нужно вставить в эти колонки, в том же порядке. Вот, в принципе, и все. Взглянем на конкретный пример. Вот запрос с INSERT , который заполняет всю таблицу "books": Мы подошли к концу, предлагаю небольшой тест. Посмотрите на тот запрос в самом начале статьи. Можете разобраться в нем? Попробуйте разбить его на секции SELECT , FROM , WHERE , GROUP BY , и рассмотреть отдельные компоненты подзапросов. Вот он в более удобном для чтения виде: Этот запрос выводит список людей, которые взяли из библиотеки книгу, у которой общее количество выше среднего значения. Результат: Надеюсь, вам удалось разобраться без проблем. Но если нет, то буду рад вашим комментариям и отзывам, чтобы я мог улучшить этот пост. Более традиционные языки, однако, сильны именно в этих областях.
Они разработаны так чтобы программист мог начинать обработку данных,
и основываясь на ее результатах, решать, делать ли это действие или другое, или же повторять действие до тех пока не встретится некоторое условие, создавая логические маршруты и циклы. Значения сохраняются в переменных, которые могут использоваться и изменяться с помощью любого
числа команд. Это дает вам возможность указывать пользователям на ввод
или вывод этих команд из файла, и возможность форматировать вывод сложными способами (например, преобразовывать числовых данных в диаграммы).
Цель вложенного SQL состоит в том, чтобы объединить эти возможности,
позволяющие вам создавать сложные процедурные программы которые
адресуют базу данных посредством SQL - позволяя вам устранить сложные действия в таблицах на процедурном языке который не ориентирован на такую структуру данных, в тоже время поддерживая структурную
строгость процедурного языка.
Затем вы используете обычный транслятор чтобы преобразовывать программу из исходного текста в выполняемый код.
Согласно подходу к модульному языку определенному ANSI, основная программа вызывает процедуры SQL. Процедуры выбирают параметры из глав-
ной программы, и возвращают уже обработанные значения, обратно в основную программу. Модуль может содержать любое число процедур, каждая из
которых состоит из одиночной команды SQL. Иде в том, чтобы процедуры
могли работать тем же самым способом что и процедуры на языке в который они были вложены(хотя модуль еще должен идентифицировать базовый
язык из-за различий в типах данных различных языков).
Реализации могут удовлетворить стандарту, выполнив вложение SQL таким
способом, как если бы модули уже были точно определены. Для этой цели
прекомпилятор будет создавать модуль, называемый модулем доступа. Только
один модуль, содержащий любое число процедур SQL, может существовать
для данной программы. Размещение операторов SQL непосредственно в главном коде, происходит более просто и более практично чем непосредственно
создание самих модулей.
Каждая из программ использующих вложение SQL, связана с ID доступа,
во время ее выполнения. ID доступа, связанный с программой, должен иметь
все привилегии чтобы выполнять операции SQL, выполняемые в программе.
Вообще то, вложенная программа SQL регистрируется в базе данных, также
как и пользователь, выполняющий программу. Более подробно, это определяет проектировщик, но вероятно было бы неплохо для включить в вашу программу команду CONNECT или ей подобную.
* быть объявлеными в SQL DECLARE SESSION (РАЗДЕЛ
ОБЪЯВЛЕНИЙ) который будет описан далее.
* иметь совместимый тип данных с их функциями в команде SQL
(например, числовой тип если он вставляется в числовое поле)
* быть назначенными значению во врем их использования в команде
SQL, если команда SQL самостоятельно не может сделать назначение.
* предшествовать двоеточию (:) когда они упоминаются в команде SQL
Так как главные переменные отличаются от имен столбцов SQL наличием
у них двоеточия, вы можете использовать переменные с теми же самыми
именами что и ваши столбцы, если это конечно нужно.
Предположим что вы имеете четыре переменных в вашей программе, с
именами: id_num, salesperson, loc, и comm. Они содержат значения
которые вы хотите вставить в таблицу Продавцов. Вы могли бы вложить
следующую команду SQL в вашу программу:
EXEC SQL INSERT INTO Salespeople
VALUES (:id_num, :salesperson, :loc, :comm)
Текущие значения этих переменных будут помещены в таблицу. Как вы
можете видеть, переменна comm имеет то же самое им что и столбец в
который это значение вкладывается.
Обратите внимание, что точка с запятой в конце команды отсутствует. Это
потому, что соответствующее завершение для вложенной команды SQL за-
висит от языка для которого делается вложение.
Для Паскаля и PL/1, это будет точка с запятой; для КОБОЛА, слово
END-EXEC ; и для ФОРТРАНА не будет никакого завершения.
В других языках это зависит от реализации, и поэтому мы договоримся
что будем использовать точку с запятой (в этой книге) всегда, чтобы не
противоречить интерактивному SQL и Паскалю. Паскаль завершает вложенный SQL и собственные команды одинаково - точкой с запятой.
Способ сделать команду полностью такой как описана выше, состоит в
том, чтобы включать ее в цикл и повторять ее, с различными значения-
ми переменных, как например показано в следующем примере:
while not end-ot-file (input) do
begin
readln (id_num, salesperson, loc, comm);
EXEC SOL INSERT INTO Salespeople
VALUES (:id_num, :salesperson, :loc, :comm);
end;
Фрагмент программы на ПАСКАЛЕ, определяет цикл, который будет
считывать значения из файла, сохранять их в четырех проименованных
переменных, сохранять значения этих переменных в таблице Продавцов,
и затем считывать следующие четыре значения, повтор этот процесс
до тех пор пока весь входной файл не прочитается. Считается, что каждый набор значений завершается возвратом каретки (для незнакомых
с Паскалем, функция readln считывает вводимую информацию и пере-
ходит на следующую строку источника этой информации). Это дает вам
простой способ передать данные из текстового файла в реляционную структуру.
Конечно, вы можете сначала обработать данные любыми возможными
способами на вашем главном языке, например для исключения всех комиссионных ниже значения.12
while not end-ot-file (input) do
begin
readln (id_num, salesperson, loc, comm);
if comm > = .12 then
EXEC SQL INSERT INTO Salespeople
VALUES (:id_num, :salesperson, :loc, :comm);
end;
Только строки которые встретят условие comm >= .12 будут вставлены в
вывод. Это показывает что можно использовать и циклы и условия как
нормальные для главного языка.
* когда вы используете предикат проверяющий значения, которое как вы
знаете, могут быть уникальным, как в этом примере. Значения которые,
как вы знаете, могут быть уникальными - это те значения которые имеют принудительное ограничение уникальности или уникальный индекс,
как это говорилось в Главах 17 и .
* когда вы используете одну или более агрегатных функций и не используете GROUP BY.
* когда вы используете SELECT DISTINCT во внешнем ключе с предикатом ссылающимся на единственное значение родительского ключа
(обеспечивая вашей системе предписание справочной целостность),
как в следующем примере:
EXEC SQL SELECT DISTINCT snum
INTO:salesnum
FROM Customers
WHERE snum =
(SELECT snum
FROM Salespeople
WHERE sname = "Motika");
Предполагалось что Salespeople.sname и Salespeople.snum - это соответственно, уникальный и первичный ключи этой таблицы, а Customers.snum
- это внешний ключ ссылающийся на Salespeople.snum, и вы предполагали что этот запрос произведет единственную строку.
Имеются другие случаи, когда вы можете знаете, что запрос должен про-
извести единственную строку вывода, но они мало известны и, в большинстве случаев, вы основываетесь на том что ваши данные имеют целостность, которая не может быть предписана с помощью ограничений. Не
полагайтесь на это! Вы создаете программу которая, вероятно, будет использоваться в течение некоторого времени, и лучше всего проиграть ее
чтобы быть гарантированным в будущем от возможных отказов. Во всяком случае, нет необходимости группировать запросы которые производят
одиночные строки, поскольку SELECT INTO - используется только для
удобства.
Как вы увидите, вы можете использовать запросы выводящие многочисленные строки, используя курсор.
А) Для SELECT, ни одна строка не выбрана запросом.
Б) Для FETCH, последняя строка уже была выбрана, или ни
одной строки не выбрано запросом в курсоре.
В) Для INSERT, ни одной строки не было вставлено (подразумевается что запрос использовался чтобы сгенерировать значения для вставки, и был отвергнут при попытке извлечения любой строки.
Г) Для UPDATE и DELETE, ни одна строка не ответила условию предиката, и следовательно никаких изменений сделано
в таблице не будет.
В любом случае, будет установлен код SQLCODE = 100.
2. Команда выполнилась нормально, не удовлетворив ни одному из
выше указанных условий. В этом случае, будет установлен код
SQLCOD = 0.
3. Команда сгенерировала ошибку. Если это случилось, изменения
сделанные к базе данных текущей транзакцией, будут восстановлены(см. Главу 23).
В этом случае будет установлен код SQLCODE = некоторому отрицательному числу, определяемому проектировщиком. Задача этого числа, идентифицировать проблему, так точно насколько это возможно. В принципе, ваша система должна быть снабжена подпрограммой, которая в этом случае, должна выполниться чтобы вы-
дать для вас информацию расшифровывающее значение негатив-
ного числа определенного вашим проектировщиком. В этом случае
некоторое сообщение об ошибке будет выведено на экран или за-
писано в файл протокола, а программа в это врем выполнит восстановление изменений для текущей транзакции, отключится от базы данных и выйдет из нее. Теперь мы можем усовершенствовать
CONTINUE не делает чего-то специального для значения SQLCODE. Оно
также является значением по умолчанию. если вы не используете команду
WHENEVER, определяющую значение SQLCODE. Однако, эти неактивные
определения дают вам возможность переключаться вперед и назад, выполняя и не выполняя действия, в различных точках(метках) вашей программы.
Например, если ваша программа включает в себя несколько команд INSERT,
использующих запросы, которые реально должны производить значения, вы
могли бы напечатать специальное сообщение или сделать что-то такое, что
поясняло бы, что запросы возвращаются пустыми и никакие значения не бы-
ли вставлены. В этом случае, вы можете ввести следующее:
EXEC SQL WHENEVER NOT FOUND GOTO No_rows;
No_rows - это метка в некотором коде, содержащем определенное действие. С другой стороны, если вам нужно сделать выборку в программе поз-
же, вы можете ввести следующее в этой точке,
EXEC SQL WHENEVER NOT FOUND CONTINUE;
что бы выполнение выборки повторялось до тех пор пока все строки не будут извлечены, что является нормальной процедурой не требующей специальной обработки.
* Все вложенные команды SQL начинаются словами EXEC SQL и заканчиваются способом который зависит от используемого главного языка.
* Все главные переменные доступные в командах SQL, должны быть объявлены в разделе объявлений SQL прежде, чем они будут использованы.
* Всем главным переменным должно предшествовать двоеточие когда они
используются в команде SQL.
* Запросы могут сохранять свой вывод непосредственно в главных переменных, используя предложение INTO, если и только если, они выбирают
единственную строку.
* Курсоры могут использоваться для сохранения вывода запроса, и доступа
к одной строке в каждый момент времени. Курсоры бывают объявлеными
(если определяют запрос в котором будут содержаться), открытыми(если
выполняют запрос), и закрытыми (если удаляют вывод запроса из курсора). Если курсор открыт, команда FETCH, используется чтобы перемещать его по очереди к каждой строке вывода запроса.
* Курсоры являются модифицируемыми или только-чтение. Чтобы стать
модифицируемым, курсор должен удовлетворять всем критериям которым удовлетворяет просмотр; кроме того, он не должен использовать
предложений ORDER BY или UNION, которые в любом случае не
могут использоваться просмотрами. Не модифицируемый курсор является курсором только-чтение.
* Если курсор модифицируемый, он может использоваться для определения, какие строки задействованы вложенными командами UPDATE и
DELETE через предложение WHERE CURRENT OF.
DELETE или UPDATE должны быть вне той таблицы к которой кур-
сор обращаетс в запросе.
* SQLCODE должен быть объявлен как переменна числового типа для
каждой программы которая будет использовать вложенный SQL. Его
значение устанавливается автоматически после выполнения каждой
команды SQL.
* Если команда SQL выполнена как обычно, но не произвела вывода или
ожидаемого изменения в базе данных, SQLCODE = 100. Если команда
произвела ошибку, SQLCODE будет равняться некоторому аппаратно-
определенному отрицательному числу которое описывает ошибку.
В противном случае, SQLCODE = 0.
* Предложение WHENEVER может использоваться для определения действия которое нужно предпринять когда SQLCODE = 100 (не найдено)
или когда SQLCODE равен отрицательному числу (SQLERROR).
Действием может быть или переход к некоторой определенной метке в
программе (GOTO
sql часто называют языком эсперанто для систем управления базами данных (СУБД). Действительно, в мире нет другого языка для работы с базами данных (БД), который бы настолько широко использовался в программах. Первый стандарт sol появился в 1986 г. и к настоящему времени завоевал всеобщее признание. Его можно использовать даже при работе с нереляционными СУБД. В отличие от других программных средств, таких, как языки Си и Кобол, являющихся прерогативой программистов-профессионалов, sql применяется специалистами из самых разных областей. Программисты, администраторы СУБД, бизнес-аналитики — все они с успехом обрабатывают данные с помощью sql. Знание этого языка полезно всем, кому приходится иметь дело с БД. В этой статье мы рассмотрим основные понятия sql. Расскажем его предысторию (и развеем попутно несколько мифов). Вы познакомитесь с реляционной моделью и сможете приобрести первые навыки работы с sql, что поможет в дальнейшем освоении языка. Трудно ли изучить sql? Это зависит от того, насколько глубоко вы собираетесь вникать в суть. Для того чтобы стать профессионалом, придется изучить очень многое. Язык sql появился в 1974 г. как предмет небольшой исследовательской работы, состоявшей из 23 страниц, и с тех пор прошел долгий путь развития. Текст действующего ныне стандарта — официального документа "the international standard database language sql" (обычно называемого sql-92) — содержит свыше шести сотен страниц, однако в нем ничего не говорится о конкретных особенностях версий sol, реализованных в СУБД фирм microsoft, oracle, sybase и др. Язык настолько развит и разнообразен, что лишь простое перечисление его возможностей потребует нескольких журнальных статей, а если собрать все, что написано на тему sol, то получится многотомная библиотека. Однако для обычного пользователя совсем не обязательно знать sql целиком и полностью. Как туристу, оказавшемуся в стране, где говорят на непонятном языке, достаточно выучить лишь несколько употребительных выражений и правил грамматики, так и в sql — зная немногое, можно получать множество полезных результатов. В этой статье мы рассмотрим основные команды sql, правила задания критериев для отбора данных и покажем, как получать результаты. В итоге вы сможете самостоятельно создавать таблицы и вводить в них информацию, составлять запросы и работать с отчетами. Эти знания могут стать базой для дальнейшего самостоятельного освоения sql. sql — это специализированный непроцедурный язык, позволяющий описывать данные, осуществлять выборку и обработку информации из реляционных СУБД. Специализированность означает, что sol предназначен лишь для работы с БД; нельзя создать полноценную прикладную систему только средствами этого языка — для этого потребуется использовать другие языки, в которые можно встраивать sql-команды. Поэтому sql еще называют вспомогательным языковым средством для обработки данных. Вспомогательный язык используется только в комплексе с другими языками. В прикладном языке общего назначения обычно имеются средства для создания процедур, а в sql их нет. С его помощью нельзя указать, каким образом должна выполняться некоторая задача, а можно лишь определить, в чем именно она заключается. Другими словами, при работе с sql нас интересуют результаты, а не процедуры для их получения. Наиболее существенным свойством sql является возможность доступа к реляционным БД. Многие даже считают, что выражения "БД, обрабатываемая средствами sql" и "реляционная БД" — синонимы. Однако скоро вы убедитесь, что между ними имеется разница. В стандарте sql-92 даже нет термина отношение (relation). Если не вдаваться в подробности, то реляционная СУБД — это система, основанная на реляционной модели управления данными. Понятие реляционной модели было впервые предложено в работе д-ра Е. Ф. Кодда, опубликованной в 1970 г. В ней был описан математический аппарат для структуризации данных и управления ими, а также предложена абстрактная модель для представления любой реальной информации. До этого при использовании БД требовалось учитывать конкретные особенности хранения в ней информации. Если внутренняя структура БД изменялась (например, с целью повышения быстродействия), приходилось перерабатывать прикладные программы, даже если на логическом уровне никаких изменений не происходило. Реляционная модель позволила отделить частные особенности хранения данных от уровня прикладной программы. В самом деле, модель никак не описывает способы хранения информации и доступа к ней. Учитывается лишь то, как эта информация воспринимается пользователем. Благодаря появлению реляционной модели качественно изменился подход к управлению данными: из искусства оно превратилось в науку, что привело к революционному развитию отрасли. Согласно реляционной модели, отношение (relation) — это некоторая таблица с данными. Отношение может иметь один или несколько атрибутов (признаков), соответствующих столбцам этой таблицы, и некоторое множество (возможно, пустое) данных, представляющих собой наборы этих атрибутов (их называют n-арными кортежами, или записями) и соответствующих строкам таблицы. Для любого кортежа значения атрибутов должны принадлежать так называемым доменам. Фактически доменом является некоторый набор данных, который задает множество всех допустимых значений. Давайте рассмотрим пример. Пусть имеется домен ДниНедели, содержащий значения от Понедельник до Воскресенье. Если отношение имеет атрибут ДеньНедели, соответствующий этому домену, то в любом кортеже отношения в столбце ДеньНедели должно присутствовать одно из перечисленных значений. Появление значений Январь или Кошка не допускается. Обратите внимание: атрибут обязательно должен иметь одно из допустимых значений. Задание сразу нескольких значений запрещено. Таким образом, помимо требования принадлежности значений атрибута некоторому домену, должно соблюдаться условие его атомарности. Это означает, что для этих значений недопустима декомпозиция, т. е. нельзя разбить их на более мелкие части, не потеряв основного смысла. Например, если бы значение атрибута одновременно содержало Понедельник и Вторник, то можно было бы выделить две части, сохранив первоначальный смысл — ДеньНедели; следовательно, это значение атрибута не является атомарным. Однако если попробовать разбить значение "Понедельник" на части, то получится набор из отдельных букв — от "П" до "К"; исходный смысл утерян, поэтому значение "Понедельник" является атомарным. Отношения обладают и другими свойствами. Наиболее значимое из них — математическое свойство замкнутости операций. Это означает, что в результате выполнения любой операции над отношением должно появляться новое отношение. Это свойство позволяет при выполнении математических операций над отношениями получать предсказуемые результаты. Кроме того, появляется возможность представлять операции в виде абстрактных выражений с разными уровнями вложенности. В своей исходной работе д-р Кодд определил набор из восьми операторов, получивший название реляционной алгебры. Четыре оператора — объединение, логическое умножение, разность и Декартово произведение — были перенесены из традиционной теории множеств; остальные операторы были созданы специально для обработки отношений. В последующих работах д-ра Кодда, Криса Дейта и других исследователей были предложены дополнительные операторы. Далее в этой статье будут рассмотрены три реляционных оператора — продукция (project), ограничения (select, или restrict) и слияние (join). Теперь, когда вы познакомились с реляционной моделью, давайте забудем о ней. Конечно, не навсегда, а лишь для того, чтобы объяснить следующее: хотя именно предложенная д-ром Коддом реляционная модель была использована при разработке sql, между ними нет полного или буквального соответствия (это одна из причин, почему в стандарте sql-92 отсутствует термин отношение). Например, понятия таблица sql и отношение не являются равнозначными, потому что в таблицах может быть сразу несколько одинаковых строк, тогда как в отношениях появление идентичных кортежей не разрешено. К тому же в sql не предусмотрено использование реляционных доменов, хотя в некоторой степени их роль играют типы данных (некоторые влиятельные сторонники реляционной модели предпринимают сейчас попытку добиться включения в будущий стандарт sql реляционных доменов). К сожалению, несоответствие между sql и реляционной моделью породило множество недоразумений и споров за прошедшие годы. Но так как основная тема статьи — изучение sql, а не реляционной модели, эти проблемы здесь не рассматриваются. Просто следует запомнить, что между терминами, применяемыми в sql и в реляционной модели, имеются различия. Далее в статье будут использоваться только термины, принятые в sql. Вместо отношений, атрибутов и кортежей будем применять их sql-аналоги: таблицы, столбцы и строки. Возможно, вам уже знакомы такие термины, как статический и динамический sql. sql-запрос является статическим, если он компилируется и оптимизируется на стадии, предшествующей выполнению программы. Мы уже упоминали одну из форм статического sql, когда говорили о встраивании sql-команд в программы на Си или Коболе (для таких выражений существует еще другое название — встроенный sql). Как вы, наверное, догадываетесь, динамический sql-запрос компилируется и оптимизируется в ходе исполнения программы. Как правило, обычные пользователи применяют именно динамический sql, позволяющий создавать запросы в соответствии с сиюминутными нуждами. Один из вариантов изпользования динамических sql-запросов — их интерактивный или непосредственный вызов (существует даже специальный термин — directsql), когда отправляемые на обработку запросы вводятся в интерактивном режиме с терминала. Между статическим и динамическим sql имеются определенные различия в синтаксисе применяемых конструкций и особенностях исполнения, однако эти вопросы выходят за рамки статьи. Отметим лишь, что для ясности понимания примеры даются в форме direct sql-запросов, поскольку это позволяет научиться использовать sql не только программистам, но и большинству конечных пользователей. Теперь вы готовы к написанию своих первых sql-запросов. Если у вас имеется доступ к БД через sql и вы захотите воспользоваться нашими примерами на практике, то учтите следующее: вы должны входить в систему как пользователь с неограниченными полномочиями и вам потребуются программные средства интерактивной обработки sql-запросов (если речь идет о сетевой БД, следует переговорить с администратором БД о предоставлении вам соответствующих прав). Если доступа к БД через sql нет — не огорчайтесь: все примеры очень простые и в них можно разобраться "всухую", без выхода на машину. Для того чтобы выполнить какие-либо действия в sql, следует выполнить выражение на языке sql. Встречается несколько типов выражений, однако среди них можно выделить три основные группы: ddl-команды (data definition language — язык описания данных), dml-команды (data manipulation language — язык манипуляций с данными) и средства контроля за данными. Таким образом, в sql в каком-то смысле объединены три различных языка. Начнем с одной из основных ddl-команд — create table (Создать таблицу). В sql бывают таблицы нескольких типов, основными являются два типа: базовые (base) и выборочные (views). Базовыми являются таблицы, относящиеся к реально существующим данным; выборочные — это "виртуальные" таблицы, которые создаются на основе информации, получаемой из базовых таблиц; но для пользователей формы выглядят как обычные таблицы. Команда create table предназначена для создания базовых таблиц. В команде create table следует задать название таблицы, указать список столбцов и типы содержащихся в них данных. В качестве параметров могут присутствовать также другие необязательные элементы, однако сначала давайте рассмотрим только основные параметры. Покажем простейшую синтаксическую форму для этой команды: create table ИмяТаблицы (Столбец ТипДанных) ; create и table — это ключевые слова sql; ИмяТаблицы, Столбец и ТипДанных — это формальные параметры, вместо которых пользователь каждый раз вводит фактические значения. Параметры Столбец и ТипДанных заключены в круглые скобки. В sql круглые скобки обычно используются для группировки отдельных элементов. В данном случае они позволяют объединить определения для столбца. Стоящий в конце знак "точка с запятой" является разделителем команд. Он должен завершать любое выражение на языке sql. Рассмотрим пример. Пусть нужно создать таблицу для хранения данных обо всех встречах (appointments). Для этого в sql следует ввести команду: create table appointments (appointment_date date) ; После выполнения этой команды будет создана таблица с именем appointments, где имеется один столбец appointment_date, в котором могут записываться данные типа date. Поскольку на текущий момент данные еще не вводились, количество строк в таблице равно нулю (с помощью команды create table только дается определение таблицы; реальные значения вводятся командой insert, которая рассматривается далее). Параметры appointments и appointment_date называются идентификаторами, поскольку они задают имена для конкретных объектов БД, в данном случае — имена для таблицы и столбца соответственно. В sql встречаются идентификаторы двух типов: обычные (regular) и выделенные (delimited). Выделенные идентификаторы заключаются в двойные кавычки, и в них учитывается регистр используемых символов. Обычные идентификаторы не выделяются никакими ограниченными символами, в их написании регистр не учитывается. В этой статье применяются только обычные идентификаторы. Символы, используемые для построения идентификаторов, должны удовлетворять определенным правилам. В обычных идентификаторах могут использоваться только буквы (не обязательно латинские, но и других алфавитов), цифры и символ подчеркивания. Идентификатор не должен содержать знаков пунктуации, пробелов или специальных символов (#, @, % или!); кроме того, он не может начинаться с цифры или знака подчеркивания. Для идентификаторов можно использовать отдельные ключевые слова sql, но делать это не рекомендуется. Идентификатор предназначен для обозначения некоторого объекта, поэтому у него должно быть уникальное (в рамках определенного контекста) имя: нельзя создать таблицу с именем, которое уже встречается в БД; в одной таблице нельзя иметь столбцы с одинаковыми именами. Кстати, имейте в виду, что appointments и appointments — это одинаковые имена для sql. Одним лишь изменением регистра букв создать новый идентификатор нельзя. Хотя таблица может иметь всего один столбец, на практике обычно требуются таблицы с несколькими столбцами. Команда для создания такой таблицы в общем виде выглядит так: create table ИмяТаблицы (Столбец ТипДанных [ { , Столбец ТипДанных } ]) ; Квадратные скобки использованы для обозначения необязательных элементов, фигурные содержат элементы, которые могут представлять собой перечень однопутных конструкций (при вводе реальной sql-команды ни те ни другие скобки не ставятся). Такой синтаксис позволяет задать любое число столбцов. Обратите внимание, что перед вторым элементом стоит запятая. Если в списке имеется несколько параметров, то они отделяются друг от друга запятыми. create table appointments2 (appointment_date date ,
appointment_time time ,
description varchar (256)) ; Данная команда создает таблицу appointments2 (новая таблица должна иметь иное имя, так как таблица appointments уже присутствует в БД). Как и в первой таблице, в ней имеется столбец appointment_date для записи даты встреч; кроме того, появился столбец appointment_time для записи времени этих встреч. Параметр description (описание) является текстовой строкой, где может содержаться до 256 символов. Для этого параметра указан тип varchar (сокращение от character varying), поскольку заранее не известно, сколько места потребуется для записи, но ясно, что описание займет не более 256 символов. При описании параметро в типа символьная строка (и некоторых других типов) указывается длина параметра. Ее значение задается в круглых скобках справа от названия типа. Возможно, вы обратили внимание, что в двух рассмотренных примерах запись команды оформлена по-разному. Если в первом случае команда полностью размещена в одной строке, то во втором после первой открытой круглой скобки запись продолжена с новой строки, и определение каждого следующего столбца начинается с новой строки. В sql нет специальных требований к оформлению записи. Разбиение записи на строки делает ее чтение удобнее. Язык sql позволяет при написании команд не только разбивать команду по строкам, но и вставлять отступы в начале строк и пробелы между элементами записи. Теперь, когда вы знаете основные правила, давайте рассмотрим более сложный пример создания таблицы с несколькими столбцами. В начале статьи была показана таблица employees (Сотрудники). В ней содержатся следующие столбцы: фамилия, имя, дата приема на работу, подразделение, категория и зарплата за год. Для определения этой таблицы используется следующая команда sql: create table employees (last_name character (13) not null,
first_name character (10) not null,
hire_date date ,
branch_office character (15) ,
grade_level smallint ,
salary decimal (9 , 2)) ; В команде встречаются несколько новых элементов. Прежде всего, это выражение not null, стоящее в конце определения столбцов last_name и first_name. С помощью подобных конструкций задаются требования, подлежащие обязательному соблюдению. В данном случае указано, что поля last_name и first_name должны обязательно заполняться при вводе; оставлять эти столбцы пустыми нельзя (это вполне логично: как можно идентифицировать сотрудника, не зная его имени?). Кроме того, в примере присутствуют три новых типа данных: character, smallint и decimal. До сих пор мы почти не говорили о типах. Хотя в sql нет реляционных доменов, однако имеется набор основных типов данных. Эта информация используется при выделении памяти и сравнении величин; в определенной степени сужает список возможных значений при вводе, однако контроль типов в sql менее строгий, чем в других языках. Все имеющиеся в sql типы данных можно разбить на шесть групп: символьные строки, точные числовые значения, приближенные числовые значения, битовые строки, датовремя и интервалы. Мы перечислили все разновидности, однако в этой статье подробно будут рассматриваться лишь отдельные из них (битовые строки, например, не представляют особого интереса для обычных пользователей). Кстати, если вы подумали, что датовремя — это опечатка, то ошиблись. К данной группе (datetime) относится большинство используемых в sql типов данных, связанных со временем (такие параметры, как временные интервалы, выделены в отдельную группу). В предыдущем примере уже встречались два типа данных из группы датовремя — date и time. Следующий тип данных, с которым вы уже знакомы, — character varying (или просто varchar); он относится к группе символьных строк. Если varchar служит для хранения строк переменной длины, то встретившийся в третьем примере тип char предназначен для записи строк, имеющих фиксированное число символов. Например, в столбце last_name будут записываться строки из 13 символов вне зависимости от реально вводимых фамилий, будь то poe или penworth-chickering (в случае с poe оставшиеся 10 символов заполнятся пробелами). С точки зрения пользователя, varchar и char имеют одинаковый смысл. Зачем нужно было вводить два типа? Дело в том, что на практике обычно приходится искать компромисс между быстродействием и экономией пространства на диске. Как правило, применение строк с фиксированной длиной дает некоторый выигрыш в скорости доступа, однако при слишком большой длине строк пространство на диске расходуется неэкономно. Если в appointments2 для каждой строки комментария резервировать по 256 символов, то это может оказаться нерационально; чаще всего строки будут значительно короче. С другой стороны, фамилии также имеют разную длину, но для них, как правило, требуется около 13 символов; в этом случае потери будут минимальными. Существует хорошее правило: если известно, что длина строки меняется незначительно либо она сравнительно невелика, то используйте char; в остальных случаях — varchar. Следующие два новых типа данных — smallint и decimal — относятся к группе точных числовых значений. smallint — это сокращенное название от small integer (малое целое). В sql также предусмотрен тип данных integer. Наличие двух схожих типов и в этом случае объясняется соображением экономии пространства. В нашем примере значения параметра grade_level могут быть представлены с помощью двузначного числа, поэтому использован тип smallint; однако на практике не всегда известно, какие максимальные значения могут быть у параметров. Если такой информации нет, то применяйте integer. Реальный объем, выделяемый для хранения параметров типа smallint и integer, и соответствующий диапазон значений для этих параметров индивидуальны для каждой платформы. Тип данных decimal, обычно используемый для учета финансовых показателей, позволяет задать шаблон с требуемым числом десятичных знаков. Поскольку этот тип служит для точной числовой записи, он гарантирует точность при выполнении математических операций над десятичными данными. Если для десятичных значений использовать типы данных из группы приближенной числовой записи, например float (floating point number — число с плавающей точкой), это приведет к погрешностям округления, поэтому для финансовых расчетов этот вариант не подходит. Для определения параметров типа decimal используется следующая форма записи: где p — это число десятичных знаков, d — количество разрядов после запятой. Вместо p следует записывать общее число значащих цифр в используемых значениях, а вместо d — количество цифр после запятой. Во врезке "Создание таблицы" показан полный вариант обобщенной записи команды create table. В нем присутствуют новые элементы и показан формат для всех рассмотренных типов данных (В принципе встречаются и другие типы данных, но пока мы их не рассматриваем). На первых порах может показаться, что синтаксис sql-команд слишком сложен. Но вы легко в нем разберетесь, если внимательно изучили приведенные выше примеры. На схеме появился дополнительный элемент — вертикальная черта; он служит для разграничения альтернативных конструкций. Другими словами, при определении каждого столбца нужно выбрать подходящий тип данных (как вы помните, в квадратные скобки заключаются необязательные параметры, а в фигурные скобки — конструкции, которые могут повторяться многократно; в реальных sql-командах эти специальные символы не пишутся). В первой части схемы приведены полные названия для типов данных, во второй — их сокращенные названия; на практике можно использовать любые из них. Первая часть статьи завершена. Вторая будет посвящена изучению dml-команд insert, select, update и delete. Также будут рассмотрены условия выборки данных, операторы сравнения и логические операторы, использование null-значений и троичная логика. Создание таблицы. Синтаксис команды create table: в квадратных скобках указаны необязательные параметры, в фигурных — повторяющиеся конструкции. create table table (column character (length) [ constraint ] |
character varying (length) [ constraint ] |
date [ constraint ] |
time [ constraint ] |
integer [ constraint ] |
smallint [ constraint ] |
decimal (precision, decimal places) [ constraint ] |
float (precision) [ constraint ]
[{ , column char (length) [ constraint ] |
varchar (length) [ constraint ] |
date [ constraint ] |
time [ constraint ] |
int [ constraint ] |
smallint [ constraint ] |
dec (precision, decimal places) [ constraint ] |
float (precision) [ constraint ] }]) ; В начале 1970-х гг. в ibm приступили к практическому воплощению модели реляционных БД, предложенной д-ром Коддом. Дональд Чамберлин и группа других сотрудников подразделения перспективных исследований создали прототип языка, получивший название structured english query language (язык структурированных англоязычных запросов), или просто sequel. В дальнейшем он был расширен и подвергнут доработке. Новый вариант, предложенный ibm, получил название sequel/2. Его использовали как программный интерфейс (api) для проектирования первой реляционной системы БД фирмы ibm — system/r. Из соображений, связанных с правовыми нюансами, в ibm решили изменить название: вместо sequel/2 использовать sql (structured query language). Эту аббревиатуру часто произносят как "си-ку-эл".Команды для работы с базами данных
1. Просмотр доступных баз данных
SHOW DATABASES;
2. Создание новой базы данных
CREATE DATABASE;
3. Выбор базы данных для использования
USE 4. Импорт SQL-команд из файла.sql
SOURCE 5. Удаление базы данных
DROP DATABASE Работа с таблицами
6. Просмотр таблиц, доступных в базе данных
SHOW TABLES;
7. Создание новой таблицы
CREATE TABLE Ограничения целостности при использовании CREATE TABLE
Пример
8. Сведения о таблице
9. Добавление данных в таблицу
INSERT INTO 10. Обновление данных таблицы
UPDATE 11. Удаление всех данных из таблицы
DELETE FROM 12. Удаление таблицы
DROP TABLE Команды для создания запросов
13. SELECT
14. SELECT DISTINCT
15. WHERE
Пример
16. GROUP BY
Пример
17. HAVING
Пример
18. ORDER BY
Пример
19. BETWEEN
Пример
20. LIKE
SELECT Пример
21. IN
Пример
22. JOIN
Пример 1
Пример 2
Пример 3
23. View
Создание
CREATE VIEW Удаление
DROP VIEW Пример
24. Агрегатные функции
25. Вложенные подзапросы
Пример
В этой статье
Что такое SQL?
FROM Contacts
WHERE First_Name = "Mary";Инструкции SELECT
Предложения SQL
Термины SQL
Основные предложения SQL: SELECT, FROM и WHERE
FROM table_1
WHERE criterion_1
;Пример в Access
Предложение SELECT
Предложение FROM
Предложение WHERE
Сортировка результатов: ORDER BY
Работа со сводными данными: предложения GROUP BY и HAVING
Задание полей, которые не используются в агрегатной функции: предложение GROUP BY
Ограничение агрегированных значений с помощью условий группировки: предложение HAVING
Объединение результатов запроса: оператор UNION
FROM table_1
UNION
SELECT field_a
FROM table_a
;
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;Надо “ SELECT * WHERE a=b FROM c ” или “ SELECT WHERE a=b FROM c ON * ” ?
SELECT members.firstname || " " || members.lastname
AS "Full Name"
FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid
FROM books
WHERE stock>(SELECT avg(stock)
FROM books))
GROUP BY members.firstname, members.lastname;
1. Три волшебных слова
2. Наша база
3. Простой запрос
SELECT bookid AS "id", title
FROM books
WHERE author="Dan Brown";
id
title
2
The Lost Symbol
4
Inferno
3.1 FROM - откуда берем данные
3.2 WHERE - какие данные показываем
3.3 SELECT - как показываем данные
4. Соединения (джойны)
SELECT books.title AS "Title", borrowings.returndate AS "Return Date"
FROM borrowings JOIN books ON borrowings.bookid=books.bookid
WHERE books.author="Dan Brown";
Title
Return Date
The Lost Symbol
2016-03-23 00:00:00
Inferno
2016-04-13 00:00:00
The Lost Symbol
2016-04-19 00:00:00
borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author="Dan Brown"
SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"
SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author="Dan Brown";
First Name
Last Name
Mike
Willis
Ellen
Horton
Ellen
Horton
5. Агрегирование
SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name",
count(*) AS "Number of books borrowed"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author="Dan Brown"
GROUP BY members.firstname, members.lastname;
First Name
Last Name
Number of books borrowed
Mike
Willis
1
Ellen
Horton
2
SELECT author, sum(stock)
FROM books
GROUP BY author;
author
sum
Robin Sharma
4
Dan Brown
6
John Green
3
Amish Tripathi
2
6. Подзапросы
6.1 Двумерная таблица
SELECT *
FROM (SELECT author, sum(stock)
FROM books
GROUP BY author) AS results
WHERE author="Robin Sharma";
SELECT title, bookid
FROM books
WHERE author IN (SELECT author
FROM (SELECT author, sum(stock)
FROM books
GROUP BY author) AS results
WHERE sum > 3);
title
bookid
The Lost Symbol
2
Who Will Cry When You Die?
3
Inferno
4
SELECT title, bookid
FROM books
WHERE author IN ("Robin Sharma", "Dan Brown");
6.3 Отдельные значения
select avg(stock) from books;
7. Операции записи
7.1 Update
UPDATE books
SET stock=0
WHERE author="Dan Brown";
7.2 Delete
DELETE FROM books
WHERE author="Dan Brown";
7.3 Insert
INSERT INTO x
(a,b,c)
VALUES
(x, y, z);
INSERT INTO books
(bookid,title,author,published,stock)
VALUES
(1,"Scion of Ikshvaku","Amish Tripathi","06-22-2015",2),
(2,"The Lost Symbol","Dan Brown","07-22-2010",3),
(3,"Who Will Cry When You Die?","Robin Sharma","06-15-2006",4),
(4,"Inferno","Dan Brown","05-05-2014",3),
(5,"The Fault in our Stars","John Green","01-03-2015",3);
8. Проверка
SELECT members.firstname || " " || members.lastname AS "Full Name"
FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock> (SELECT avg(stock) FROM books))
GROUP BY members.firstname, members.lastname;
Full Name
Lida Tyler
ЧТО ТАКОЕ - ВЛОЖЕНИЕ SQL
Чтобы вложить SQL в другой язык, вы должны использовать пакет программ который бы обеспечивал поддержку вложения SQL в этот язык и
конечно же, поддержку самого языка. Естественно, вы должны быть знакомы с языком который вы используете. Главным образом, вы будете использовать команды SQL для работы в таблицах базы данных, передачи
результатов вывода в программу и получение ввода из программы в которую они вкладываются, обобщенно ссылаясь к главной программе
(которая может или не может принимать их из диалога или посылать
обратно в диалог пользователя и программы).
ЗАЧЕМ ВКЛАДЫВАТЬ SQL?
Хотя и мы потратили некоторое врем на то чтобы показать что умеет делать SQL , но если вы - опытный программист, вы вероятно отметили, что
сам по себе, он не очень полезен при написании программ.
Самое очевидное ограничение - это то, что в то врем как SQL может сразу выполнить пакет команды, интерактивный SQL в основном выполняет
по одной команде в каждый момент времени.
Типы логических конструкций типа if ... then ("если... то"), for ... do
("для... выполнить") и while ... repeat("пока... повторять") - используемых для структур большинства компьютерных программ, здесь отсутствуют, так что вы не сможете принять решение - выполнять ли, как выполнять, или как долго выполнять одно действие в результате другого действия. Кроме того, интерактивный SQL не может делать многого со значениями, кроме ввода их в таблицу, размещения или распределения их с помощью запросов, и конечно вывода их на какое-то устройство.
КАК ДЕЛАЮТСЯ ВЛОЖЕНИЯ SQL.
Команды SQL помещаются в исходный текст главной программы, которой
предшествует фраза - EXEC SQL (EXECute SQL). Далее устанавливаются некоторые команды которые являются специальными для вложенной
формы SQL, и которые будут представлены в этой главе.
Строго говор, стандарт ANSI не поддерживает вложенный SQL как таковой. Он поддерживает понятие, называемое - модуль, который более точно,
является вызываемым набором процедур SQL, а не вложением в другой язык. Официальное определение синтаксиса вложения SQL, будет включать
расширение официального синтаксиса каждого языка в который может вкладываться SQL, что весьма долга и неблагодарна задача, которую ANSI
избегает. Однако, ANSI обеспечивает четыре приложения (не являющиеся
частью стандарта), которые определяют синтаксис вложения SQL для четырех языков: КОБОЛ, ПАСКАЛЬ, ФОРТРАН, и ПЛ/1.
Язык C - также широко поддерживается как и другие языки.
Когда вы вставляете команды SQL в текст программы написанной на друг-
ом языке, вы должны выполнить предкомпиляцию прежде, чем вы окончательно ее скомпилируете.
Программа называемая прекомпилятором (или препроцессором), будет
просматривать текст вашей программы и преобразовывать команды SQL,
в форму удобную для использования базовым языком.
ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННЫХ ОСНОВНОГО ЯЗЫКА В SQL
Основной способ которым SQL и части базового языка ваших программ будут связываться друг с другом - это с помощью значений переменных.
Естественно, что разные языки распознают различные типы данных для переменных. ANSI определяет эквиваленты SQL для четыре базовых языков -
ПЛ/1, Паскаль, КОБОЛ, и ФОРТРАН; все это подробности описаны в
Приложении B .
Эквиваленты для других языков - определяет проектировщик.
Имейте в виду, что типы, такие как DATE, не распознаются ANSI; и
следовательно никаких эквивалентных типов данных для базовых языков,
не существуют в стандарте ANSI. Более сложные типы данных базового
языка, такие как матрицы, не имеют эквивалентов в SQL.
Вы можете использовать переменные из главной программы во вложенных операторах SQL везде, где вы будете использовать выражения значений. (SQL, используемый в этой главе, будет пониматься как вложенный SQL, до тех пор пока это не будет оговорено особо.)
Текущим значением переменной, может быть значение, используемое в
команде. Главные переменные должны -
ОБЪЯВЛЕНИЕ ПЕРЕМЕННЫХ
Все переменные на которые имеется ссылка в предложениях 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) ot char;
loc: packed array (1. .10) ot char;
comm: real;
EXEC SQL END DECLARE SECTION;
Для незнакомых с ПАСКАЛем, Var - это заголовок который предшествует
ряду объявляемых переменных, и упакованным (или распакованным) массивам являющимися серией фиксированных переменных значений различаемых с помощью номеров(например, третий символ loc будет loc (3)).
Использование точки с запятой после каждой переменной указывает на то
что это - Паскаль, а не SQL.
ИЗВЛЕЧЕНИЕ ЗНАЧЕНИЙ ПЕРЕМЕННЫХ
Кроме помещения значений переменных в таблицы используя команды
SQL, вы можете использовать SQL чтобы получать значения для этих переменных. Один из способов делать это - с помощью разновидности команды SELECT которая содержит предложение INTO. Давайте вернемся
к нашему предыдущему примеру и переместим строку Peel из таблицы
Продавцов в наши переменные главного языка.
EXEC SQL SELECT snum, sname, city, comm
INTO:id_num, :salesperson, :loc, :comm
FROM Salespeople
WHERE snum = 1001;
Выбранные значения помещаются в переменные с упорядоченными именами указанными в предложении INTO. Разумеется, переменные с именами указанными в предложении INTO должны иметь соответствующий
тип чтобы принять эти значения, и должна быть своя переменная для ка-
ждого выбранного столбца.
Если не учитывать присутствие предложения INTO, то этот запрос - похож на любой другой. Однако, предложение INTO добавляет значительное ограничение к запросу. Запрос должен извлекать не более одной строки. Если он извлекает много строк, все они не могут быть вставлены
одновременно в одну и ту же переменную. Команда естественно потер-
пит неудачу. По этой причине, SELECT INTO должно использоваться
только при следующих условиях:
КУРСОР
Одна из сильных качеств SQL - это способность функционировать на всех
строках таблицы, чтобы встретить определенное условие как блок запись,
не зная сколько таких строк там может быть. Если десять строк удовлетворяют предикату, то запрос может вывести все десять строк. Если десять
миллионов строк определены, все десять миллионов строк будут выведены.
Это несколько затруднительно, когда вы попробуете связать это с другими
языками. Как вы сможете назначать вывод запроса для переменных когда
вы не знаете как велик будет вывод? Решение состоит в том, чтобы использовать то, что называется - курсором.
Вы вероятно знакомы с курсором, как с мигающей черточкой, которая отмечает вашу позицию на экране компьютера. Вы можете рассматривать
SQL курсор как устройство, которое аналогично этому, отмечает ваше место в выводе запроса, хотя аналоги не полна.
Курсор - это вид переменной, которая связана с запросом. Значением этой переменной может быть каждая строка, которая выводится при запросе.
Подобно главным переменным, курсоры должны быть объявлены прежде,
чем они будут использованы. Это делается командой DECLARE CURSOR,
следующим образом:
EXEC SQL DECLARE CURSOR Londonsales FOR
SELECT *
FROM Salespeople
WHERE city = "London";
Запрос не выполнится немедленно; он - только определяется. Курсор не-
много напоминает представление, в котором курсор содержит запрос,
а содержание курсора - напоминает любой вывод запроса, каждый раз
когда курсор становится открытым. Однако, в отличие от базовых таблиц
или представлений, строки курсора упорядочены: имеются первая, вторая...
... и последняя строка курсора. Этот порядок может быть произвольным
с явным управлением с помощью предложения ORDER BY в запросе, или
же по умолчанию следовать какому-то упорядочению определяемому инструментально-определяемой схемой.
Когда вы находите точку в вашей программе в которой вы хотите выполнить запрос, вы открываете курсор с помощью следующей команды:
EXEC SQL OPEN CURSOR Londonsales;
Значения в курсоре могут быть получены, когда вы выполняете именно
эту команду, но не предыдущую команду DECLARE и не последующую
команду FETСH. Затем, вы используете команду FETCH чтобы извлечь
вывод из этого запроса, по одной строке в каждый момент времени.
EXEC SQL FETCH Londonsales INTO:id_num,
:salesperson, :loc, :comm;
Это выражение переместит значения из первой выбранной строки, в
переменные. Друга команда FETCH выводит следующий набор значений. Идея состоит в том, чтобы поместить команду FETCH внутрь цикла,
так чтобы выбрав строку, вы могли переместив набор значений из этой
строки в переменные, возвращались обратно в цикл чтобы переместить
следующий набор значений в те же самые переменные.
Например, возможно вам нужно чтобы вывод выдавался по одной строке,
спрашивая каждый раз у пользователя, хочет ли он продолжить чтобы увидеть следующую строку
Look_at_more:= True;
EXEC SQL OPEN CURSOR Londonsales;
while Look_at_more do
begin
EXEC SQL FETCH Londonsales
INTO:id_num, :Salesperson, :loc, :comm;
writeln (id_num, Salesperson, loc, comm);
writeln ("Do you want to see more data? (Y/N)");
readln (response);
it response = "N" then Look_at_more: = False
end;
EXEC SQL CLOSE CURSOR Londonsales;
В Паскале, знак: = означает - " является назначенным значением из ",
в то врем как = еще имеет обычное значение " равно ". Функция
writeln записывает ее вывод, и затем переходит к новой строке.
Одиночные кавычки вокруг символьных значений во втором writeln и в
предложении if ... then - обычны для Паскаля, что случается при
дубликатах в SQL.
В результате этого фрагмента, Булевая переменна с именем Look_at
_more должна быть установлена в состояние верно, открыт курсор, и
введен цикл. Внутри цикла, строка выбирается из курсора и выводится
на экран. У пользователя спрашивают, хочет ли он видеть следующую
строку. Пока он не ответил N (Нет), цикл повторяется, и следующая
строка значений будет выбрана.
Хотя переменные Look_at_more и ответ должны быть объявлены как
Булевая переменна и символьная(char) переменна, соответственно, в
разделе объявлений переменных в Паскаля, они не должны быть включены в раздел объявлений SQL, потому что они не используются в командах SQL.
Как вы можете видеть, двоеточия перед именами переменных не используются для не-SQL операторов. Далее обратите внимание, что имеется оператор CLOSE CURSOR соответствующий оператору OPEN
CURSOR. Он, как вы поняли, освобождает курсор значений, поэтому
запрос будет нужно выполнить повторно с оператором OPEN CURSOR,
прежде чем перейти в выбору следующих значений.
Это необязательно для тех строк которые были выбраны запросом после закрытия курсора, хотя это и обычна процедура.
Пока курсор закрыт, SQL не следит за тем, какие строки были выбраны.
Если вы открываете курсор снова, запрос повторно выполняется с этой
точки, и вы начинаете все сначала.
Этот пример не обеспечивает автоматический выхода из цикла, когда
все строки уже будут выбраны. Когда у FETCH нет больше строк которые надо извлекать, он просто не меняет значений в переменных предложения INTO. Следовательно, если данные исчерпались, эти переменные будут неоднократно выводиться с идентичными значениями, до тех
пор пока пользователь не завершит цикл, введя ответ - N.
SQL КОДЫ
Хорошо было бы знать, когда данные будут исчерпаны, так чтобы можно
было сообщить об этом пользователю и цикл завершился бы автоматически.
Это - даже более важно чем например знать что команда SQL выполнена
с ошибкой. Переменна SQLCODE (называемая еще SQLCOD в
ФОРТРАНе) предназначена чтобы обеспечить эту функцию. Она должна быть определена как переменна главного языка и должна иметь
тип данных который в главном языке соответствует одному из точных
числовых типов SQL, как это показано в Приложении B .
Значение SQLCODE устанавливается каждый раз, когда выполняется
команда SQL. В основном существуют три возможности:
1. Команда выполнилась без ошибки, но не произвела никакого
действия. Для различных команд это выглядит по разному:
ИСПОЛЬЗОВАНИЕ SQLCODE ДЛЯ УПРАВЛЕНИЯ ЦИКЛАМИ
наш предыдущий пример для выхода из цикла автоматически, при условии что курсор пуст, все строки выбраны, или произошла ошибка:
Look_at_more: = lhe;
EXEC SQL OPEN CURSOR Londonsales;
while Look_at_more
and SQLCODE = O do
begin
EXEC SQL FETCH London$ales
INTO:id_num, :Salesperson, :loc, :comm;
writeln (id_num, Salesperson, loc, comm);
writeln ("Do you want to see more data? (Y/N)");
readln (response);
If response = "N" then Look_at_more: = Fabe;
end;
EXEC SQL CLOSE CURSOR Londonsales;
ПРЕДЛОЖЕНИЕ WHENEVER
Это удобно для выхода при выполненном условии - все строки выбраны.
Но если вы получили ошибку, вы должны предпринять нечто такое, что
описано для третьего случая, выше. Для этой цели, SQL предоставляет
предложение GOTO. Фактически, SQL позволяет вам применять его до-
статочно широко, так что программа может выполнить команду GOTO
автоматически, если будет произведено определенное значение SQLCODE. Вы можете сделать это совместно с предложением WHENEVER.
Имеется кусок из примера для этого случая:
EXEC SQL WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
SQLERROR- это другой способ сообщить что SQLCODE < 0; а NOT
FOUND - это другой способ сообщить что SQLCODE = 100. (Некоторые реализации называют последний случай еще как - SQLWARNING.)
Error_handler - это им того места в программе в которое будет пере-
несено выполнение программы если произошла ошибка (GOTO может
состоять из одного или двух слов). Такое место определяется любым способом соответствующим для главного языка, например, с помощью метки в Паскале, или имени раздела или имени параграфа в КОБОЛЕ (в
дальнейшем мы будем использовать термин - метка). Метка более удач-
но идентифицирует стандартную процедуру распространяемую проектировщиком для включения во все программы.
МОДИФИЦИРОВАНИЕ КУРСОРОВ
Курсоры могут также быть использованы, чтобы выбирать группу строк
из таблицы, которые могут быть затем модифицированы или удалены од-
на за другой. Это дает вам возможность, обходить некоторые ограничения
предикатов используемых в командах UPDATE и DELETE. Вы можете
ссылаться на таблицу задействованную в предикате запроса курсора или любом из его подзапросов, которые вы не можете выполнить в предикатах
самих этих команд. Как подчеркнуто в Главе 16 , стандарт SQL отклоняет
попытку удалить всех пользователей с рейтингом ниже среднего, в следующей форме:
EXEC SQL DELETE FROM Customers
WHERE rating <
(SELECT AVG (rating)
FROM Customers);
Однако, вы можете получить тот же эффект, используя запрос для выбора соответствующих строк, запомнив их в курсоре, и выполнив DELETE
с использованием курсора. Сначала вы должны объявить курсор:
EXEC SQL DECLARE Belowavg CURSOR FOR
SELECT *
FROM Customers
WHERE rating <
(SELECT AVG (rating)
FROM Customers);
Затем вы должны создать цикл, чтобы удалить всех заказчиков выбранных
курсором:
EXEC SQL WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL OPEN CURSOR Belowavg;
while not SOLCODE = 100 do
begin
EXEC SOL FETCH Belowavg INTO:a, :b, :c, :d, :e;
EXEC SOL DELETE FROM Customers
WHERE CURRENT OF Belowavg;
end;
EXEC SOL CLOSE CURSOR Belowavg;
Предложение WHERE CURRENT OF означает что DELETE применяется к строке которая в настоящее время выбрана курсором. Здесь подразумевается, что и курсор и команда DELETE, ссылаются на одну и ту же таблицу, и следовательно, что запрос в курсоре - это не объединение.
Курсор должен также быть модифицируемым. Являясь модифицируемым, курсор должен удовлетворять тем же условиям что и представления
(см. Главу 21). Кроме того, ORDER BY и UNION, которые не разрешены в представлениях, в курсорах - разрешаются, но предохраняют курсор от
модифицируемости. Обратите внимание в вышеупомянутом примере, что
мы должны выбирать строки из курсора в набор переменных, даже если
мы не собирались использовать эти переменные. Этого требует синтаксис команды FETCH. UPDATE работает так же.
Вы можете увеличить значение комиссионных всем продавцам, которые
имеют заказчиков с оценкой=300, следующим способом. Сначала вы объявляете курсор:
EXEC SOL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN
(SELECT snum
FROM Customers
WHERE rating = 300);
Затем вы выполняете модификации в цикле:
EXEC SQL OPEN CURSOR High_cust;
while SQLCODE = 0 do
begin
EXEC SOL FETCH High_cust
INTO:id_num, :salesperson, :loc, :comm;
EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE CURRENT OF High_cust;
end;
EXEC SQL CLOSE CURSOR High_cust;
Обратите внимание: что некоторые реализации требуют, чтобы вы указы-
вали в определении курсора, что курсор будет использоваться для выполнения команды UPDATE на определенных столбцах. Это делается с помощью заключительной фразы определения курсора - FOR UPDATE . Чтобы объявить курсор High_cust таким способом, так чтобы вы мог-
ли модифицировать командой UPDATE столбец comm, вы должны ввести
следующее предложение:
EXEC SQL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN
(SELECT snum
FROM Customers
WHERE rating = 300)
FOR UPDATE OF comm;
Это обеспечит вас определенной защитой от случайных модификаций,
которые могут разрушить весь порядок в базе данных.
ПЕРЕМЕННАЯ INDICATOR
Пустые (NULLS) значения - это специальные маркеры определяемые сам-
ой SQL. Они не могут помещаться в главные переменные. Попытка вставить NULL значения в главную переменную будет некорректна, так как главные языки не поддерживают NULL значений в SQL, по определению. Хо-
т результат при попытке вставить NULL значение в главную переменную
определяет проектировщик, этот результат не должен противоречить теории
базы данных, и поэтому обязан произвести ошибку: код SQLCODE в виде
отрицательного числа, и вызвать подпрограмму управления ошибкой. Естественно вам нужно этого избежать. Поэтому, вы можете выбрать NULL значения с допустимыми значениями, не приводящими к разрушению вашей
программы. Даже если программа и не разрушится, значения в главных
переменных станут неправильными, потому что они не могут иметь NULL
значений. Альтернативным методом предоставляемым для этой ситуацией
является - функция переменной indicator(указатель).
Переменна indicator - объявлена в разделе объявлений SQL напоминает
другие переменные. Она может иметь тип главного языка который соответствует числовому типу в SQL. Всякий раз, когда вы выполняете операцию,
которая должна поместить NULL значение в переменную главного языка,
вы должны использовать переменную indicator, для надежности.
Вы помещаете переменную indicator в команду SQL непосредственно после
переменной главного языка которую вы хотите защитить, без каких-либо пробелов или запятых, хотя вы и можете, при желании, вставить слово -
INDICATOR.
Переменной indicator в команде, изначально присваивается значение 0.
Однако, если производитс значение NULL , переменна indicator станов-
итс равной отрицательному числу. Вы можете проверить значение перем-
енной indicator, чтобы узнать, было ли найдено значение NULL. Давайте
предположим, что пол city и comm, таблицы Продавцов, не имеют ограни-
чени NOT NULL, и что мы объвили вразделе обьвлений SQL, две
ПАСКАЛЬевские переменные целого типа, i_a и i_b.
(Нет ничего такого в разделеобьвлений, что могло бы представить их
как переменные indicator. Они станут переменными indicator, когда бу-
дут использоваться как переменные indicator.)
Имеется одна возможность:
EXEC SQL OPEN CURSOR High_cust;
while SQLCODE = O do
begin
EXEC SQL FETCH High_cust
INTO:id_num, :salesperson,
:loc:i_a, :commINDlCATOR:i_b;
If i_a > = O and i_b > = O then
{no NULLs produced}
EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE CURRENT OF Hlgh_cust;
else
{one or both NULL}
begin
If i_a < O then
writeln ("salesperson ", id_num, " has no city");
If i_b < O then
writeln ("salesperson ", id_num, " has no
commission");
end;
{else}
end; {while}
EXEC SQL CLOSE CURSOR High_cust;
Как вы видите, мы включили, ключевое слово INDICATOR в одном случае,
и исключили его в другом случае, чтобы показать, что эффект будет одинаковым в любом случае. Каждая строка будет выбрана, но команда UPDATE
выполнится только если NULL значения не будут обнаружены.
Если будут обнаружены NULL значения, выполнится еще одна часть программы, которая распечатает предупреждающее сообщение, где было найдено
каждое NULL значение.
Обратите внимание: переменные indicator должны проверяться в главном
языке, как указывалось выше, а не в предложении WHERE команды SQL.
Последнее в принципе не запрещено, но результат часто бывает непредвиденным.
ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННОЙ INDICATOR ДЛЯ ЭМУЛЯЦИИ NULL ЗНАЧЕНИЙ SQL
Друга возможность состоит в том, чтобы обрабатывать переменную
indicator, связывая ее с каждой переменной главного языка, специальным способом, эмулирующим поведение NULL значений SQL.
Всякий раз, когда вы используете одно из этих значений в вашей программе, например в предложении if ... then, вы можете сначала проверить связанную переменную indicator, является ли ее значение=NULL.
Если это так, то вы обрабатываете переменную по-другому. Например,
если NULL значение было извлечено из пол city для главной перемен-
ной city, которая связана с переменной indicator - i_city, вы должны
установить значение city равное последовательности пробелов. Это будет необходимо, только если вы будете распечатывать его на принтере;
его значение не должно отличаться от логики вашей программы.
Естественно, i_city автоматически устанавливается в отрицательное значение. Предположим, что вы имели следующую конструкцию в вашей
программе:
If sity = "London" then
comm: = comm + .01
else comm: = comm - .01
Любое значение, вводимое в переменную city, или будет равно "London"
или не будет равно. Следовательно, в каждом случае значение комиссионных будет либо увеличено либо уменьшено. Однако, эквивалентные
команды в SQL выполняются по разному:
EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE sity = "London";
и
EXEC SQL UPDATE Salespeople
SET comm = comm .01;
WHERE sity < > "London";
(Вариант на ПАСКАЛе работает только с единственным значением, в то
врем как вариант на SQL работает со всеми таблицами.)
Если значение city в варианте на SQL будет равно значению NULL , оба
предиката будут неизвестны, и значение comm, следовательно, не будет
изменено в любом случае.
Вы можете использовать переменную indicator чтобы сделать поведение вашего главного языка непротиворечащим этому, с помощью создания условия, которое исключает NULL значения:
If i_city > = O then
begin
If city = "London" then
comm: = comm + .01
else comm: = comm - .01;
end;
{begin and end нужны здесь только для понимания}
ПРИМЕЧАНИЕ: Последняя строка этого примера содержит ремарку - { begin и end
необходимы только для понимания }
В более сложной программ, вы можете захотеть установить Булевую переменную в "верно" , чтобы указать что значение city =NULL. Затем вы
можете просто проверять эту переменную всякий раз, когда вам это
необходимо.
ДРУГОЕ ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННОЙ INDICATOR
Переменна indicator также может использоваться для назначения значения NULL. Просто добавьте ее к имени главной переменной в команде
UPDATE или INSERT тем же способом что и в команде SELECT.
Если переменна indicator имеет отрицательное значение, значение NULL
будет помещено в поле. Например, следующая команда помещает значения NULL в пол city и comm, таблицы Продавцов, всякий раз, когда
переменные indicator - i_a или i_b будут отрицательными; в противном
случае она помещает туда значения главных переменных:
EXEC SQL INSERT INTO Salespeople
VALUES (:Id_num, :salesperson, :loc:i_a, :comm:i_b);
Переменна indicator используется также, чтобы показывать отбрасываемую
строку. Это произойдет если вы вставляете значения символов SQL в главную переменную которая не достаточно длинна чтобы вместить все символы.
Это особа проблема с нестандартным типами данных - VARCHAR и LONG
(смотри Приложению C). В этом случае, переменна будет заполнена
первыми символами строки, а последние символы будут потеряны.
Если используется переменна indicator, она будет установлена в положительное значение, указывающее на длину отбрасываемой части строки, позволяя таким образом вам узнать, сколько символов было потеряно.
В этом случае, Вы можете проверить с помощью просмотра -значение переменной indicator > 0, или < 0.
РЕЗЮМЕ
Команды SQL вкладываются в процедурные языках, чтобы объединить силы двух подходов. Некоторые дополнительные средства SQL необходимы,
чтобы выполнить эту работу. Вложенные команды SQL транслируемые программой, называемой прекомпилятором, в форму пригодную для использования транслятором главного языка, и используемые в этом главном языке, как вызовы процедуры к подпрограммам которые создает прекомпилятор, называются - модулями доступа.
ANSI поддерживает вложение SQL в языки: ПАСКАЛЬ, ФОРТРАН,
КОБОЛ, и PL/I.
Другие языки также используются, особенно Си. В попытке кратко описать вложенный SQL, имеются наиболее важные места в этой главе:
Что такое sql?
Что такое реляционная СУБД?
Основные понятия реляционной модели
sql и реляционная модель
Статический и динамический sql
Как изучать sql
Команды языка описания данных
Секрет названия sql