ЧТО ТАКОЕ - ВЛОЖЕНИЕ SQL
Чтобы вложить SQL в другой язык, вы должны использовать пакет программ который бы обеспечивал поддержку вложения SQL в этот язык и конечно же, поддержку самого языка. Естественно, вы должны быть знакомы с языком который вы используете. Главным образом, вы будете использовать команды SQL для работы в таблицах базы данных, передачи результатов вывода в программу и получение ввода из программы в которую они вкладываются, обобщенно ссылаясь к главной программе (которая может или не может принимать их из диалога или посылать обратно в диалог пользователя и программы).ЗАЧЕМ ВКЛАДЫВАТЬ SQL?
Хотя и мы потратили некоторое врем на то чтобы показать что умеет делать SQL , но если вы - опытный программист, вы вероятно отметили, что сам по себе, он не очень полезен при написании программ. Самое очевидное ограничение - это то, что в то врем как SQL может сразу выполнить пакет команды, интерактивный SQL в основном выполняет по одной команде в каждый момент времени. Типы логических конструкций типа if ... then ("если... то"), for ... do ("для... выполнить") и while ... repeat("пока... повторять") - используемых для структур большинства компьютерных программ, здесь отсутствуют, так что вы не сможете принять решение - выполнять ли, как выполнять, или как долго выполнять одно действие в результате другого действия. Кроме того, интерактивный SQL не может делать многого со значениями, кроме ввода их в таблицу, размещения или распределения их с помощью запросов, и конечно вывода их на какое-то устройство.Более традиционные языки, однако, сильны именно в этих областях. Они разработаны так чтобы программист мог начинать обработку данных, и основываясь на ее результатах, решать, делать ли это действие или другое, или же повторять действие до тех пока не встретится некоторое условие, создавая логические маршруты и циклы. Значения сохраняются в переменных, которые могут использоваться и изменяться с помощью любого числа команд. Это дает вам возможность указывать пользователям на ввод или вывод этих команд из файла, и возможность форматировать вывод сложными способами (например, преобразовывать числовых данных в диаграммы). Цель вложенного SQL состоит в том, чтобы объединить эти возможности, позволяющие вам создавать сложные процедурные программы которые адресуют базу данных посредством SQL - позволяя вам устранить сложные действия в таблицах на процедурном языке который не ориентирован на такую структуру данных, в тоже время поддерживая структурную строгость процедурного языка.
КАК ДЕЛАЮТСЯ ВЛОЖЕНИЯ SQL.
Команды SQL помещаются в исходный текст главной программы, которой предшествует фраза - EXEC SQL (EXECute SQL). Далее устанавливаются некоторые команды которые являются специальными для вложенной формы SQL, и которые будут представлены в этой главе. Строго говор, стандарт ANSI не поддерживает вложенный SQL как таковой. Он поддерживает понятие, называемое - модуль, который более точно, является вызываемым набором процедур SQL, а не вложением в другой язык. Официальное определение синтаксиса вложения SQL, будет включать расширение официального синтаксиса каждого языка в который может вкладываться SQL, что весьма долга и неблагодарна задача, которую ANSI избегает. Однако, ANSI обеспечивает четыре приложения (не являющиеся частью стандарта), которые определяют синтаксис вложения SQL для четырех языков: КОБОЛ, ПАСКАЛЬ, ФОРТРАН, и ПЛ/1. Язык C - также широко поддерживается как и другие языки. Когда вы вставляете команды SQL в текст программы написанной на друг- ом языке, вы должны выполнить предкомпиляцию прежде, чем вы окончательно ее скомпилируете. Программа называемая прекомпилятором (или препроцессором), будет просматривать текст вашей программы и преобразовывать команды SQL, в форму удобную для использования базовым языком.Затем вы используете обычный транслятор чтобы преобразовывать программу из исходного текста в выполняемый код. Согласно подходу к модульному языку определенному ANSI, основная программа вызывает процедуры SQL. Процедуры выбирают параметры из глав- ной программы, и возвращают уже обработанные значения, обратно в основную программу. Модуль может содержать любое число процедур, каждая из которых состоит из одиночной команды SQL. Иде в том, чтобы процедуры могли работать тем же самым способом что и процедуры на языке в который они были вложены(хотя модуль еще должен идентифицировать базовый язык из-за различий в типах данных различных языков). Реализации могут удовлетворить стандарту, выполнив вложение SQL таким способом, как если бы модули уже были точно определены. Для этой цели прекомпилятор будет создавать модуль, называемый модулем доступа. Только один модуль, содержащий любое число процедур SQL, может существовать для данной программы. Размещение операторов SQL непосредственно в главном коде, происходит более просто и более практично чем непосредственно создание самих модулей. Каждая из программ использующих вложение SQL, связана с ID доступа, во время ее выполнения. ID доступа, связанный с программой, должен иметь все привилегии чтобы выполнять операции SQL, выполняемые в программе. Вообще то, вложенная программа SQL регистрируется в базе данных, также как и пользователь, выполняющий программу. Более подробно, это определяет проектировщик, но вероятно было бы неплохо для включить в вашу программу команду CONNECT или ей подобную.
ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННЫХ ОСНОВНОГО ЯЗЫКА В SQL
Основной способ которым SQL и части базового языка ваших программ будут связываться друг с другом - это с помощью значений переменных. Естественно, что разные языки распознают различные типы данных для переменных. ANSI определяет эквиваленты SQL для четыре базовых языков - ПЛ/1, Паскаль, КОБОЛ, и ФОРТРАН; все это подробности описаны в Приложении B . Эквиваленты для других языков - определяет проектировщик. Имейте в виду, что типы, такие как DATE, не распознаются ANSI; и следовательно никаких эквивалентных типов данных для базовых языков, не существуют в стандарте ANSI. Более сложные типы данных базового языка, такие как матрицы, не имеют эквивалентов в SQL. Вы можете использовать переменные из главной программы во вложенных операторах SQL везде, где вы будете использовать выражения значений. (SQL, используемый в этой главе, будет пониматься как вложенный SQL, до тех пор пока это не будет оговорено особо.) Текущим значением переменной, может быть значение, используемое в команде. Главные переменные должны -* быть объявлеными в 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 будут вставлены в вывод. Это показывает что можно использовать и циклы и условия как нормальные для главного языка.
ОБЪЯВЛЕНИЕ ПЕРЕМЕННЫХ
Все переменные на которые имеется ссылка в предложениях 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 должно использоваться только при следующих условиях:* когда вы используете предикат проверяющий значения, которое как вы знаете, могут быть уникальным, как в этом примере. Значения которые, как вы знаете, могут быть уникальными - это те значения которые имеют принудительное ограничение уникальности или уникальный индекс, как это говорилось в Главах 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 - используется только для удобства. Как вы увидите, вы можете использовать запросы выводящие многочисленные строки, используя курсор.
КУРСОР
Одна из сильных качеств 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. Команда выполнилась без ошибки, но не произвела никакого действия. Для различных команд это выглядит по разному:А) Для SELECT, ни одна строка не выбрана запросом.
Б) Для FETCH, последняя строка уже была выбрана, или ни одной строки не выбрано запросом в курсоре.
В) Для INSERT, ни одной строки не было вставлено (подразумевается что запрос использовался чтобы сгенерировать значения для вставки, и был отвергнут при попытке извлечения любой строки.
Г) Для UPDATE и DELETE, ни одна строка не ответила условию предиката, и следовательно никаких изменений сделано в таблице не будет.
В любом случае, будет установлен код SQLCODE = 100.
2. Команда выполнилась нормально, не удовлетворив ни одному из выше указанных условий. В этом случае, будет установлен код SQLCOD = 0.
3. Команда сгенерировала ошибку. Если это случилось, изменения сделанные к базе данных текущей транзакцией, будут восстановлены(см. Главу 23). В этом случае будет установлен код SQLCODE = некоторому отрицательному числу, определяемому проектировщиком. Задача этого числа, идентифицировать проблему, так точно насколько это возможно. В принципе, ваша система должна быть снабжена подпрограммой, которая в этом случае, должна выполниться чтобы вы- дать для вас информацию расшифровывающее значение негатив- ного числа определенного вашим проектировщиком. В этом случае некоторое сообщение об ошибке будет выведено на экран или за- писано в файл протокола, а программа в это врем выполнит восстановление изменений для текущей транзакции, отключится от базы данных и выйдет из нее. Теперь мы можем усовершенствовать
ИСПОЛЬЗОВАНИЕ 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 может состоять из одного или двух слов). Такое место определяется любым способом соответствующим для главного языка, например, с помощью метки в Паскале, или имени раздела или имени параграфа в КОБОЛЕ (в дальнейшем мы будем использовать термин - метка). Метка более удач- но идентифицирует стандартную процедуру распространяемую проектировщиком для включения во все программы.CONTINUE не делает чего-то специального для значения SQLCODE. Оно также является значением по умолчанию. если вы не используете команду WHENEVER, определяющую значение SQLCODE. Однако, эти неактивные определения дают вам возможность переключаться вперед и назад, выполняя и не выполняя действия, в различных точках(метках) вашей программы. Например, если ваша программа включает в себя несколько команд INSERT, использующих запросы, которые реально должны производить значения, вы могли бы напечатать специальное сообщение или сделать что-то такое, что поясняло бы, что запросы возвращаются пустыми и никакие значения не бы- ли вставлены. В этом случае, вы можете ввести следующее: EXEC SQL WHENEVER NOT FOUND GOTO No_rows; No_rows - это метка в некотором коде, содержащем определенное действие. С другой стороны, если вам нужно сделать выборку в программе поз- же, вы можете ввести следующее в этой точке, EXEC SQL WHENEVER NOT FOUND CONTINUE; что бы выполнение выборки повторялось до тех пор пока все строки не будут извлечены, что является нормальной процедурой не требующей специальной обработки.
МОДИФИЦИРОВАНИЕ КУРСОРОВ
Курсоры могут также быть использованы, чтобы выбирать группу строк из таблицы, которые могут быть затем модифицированы или удалены од- на за другой. Это дает вам возможность, обходить некоторые ограничения предикатов используемых в командах 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 начинаются словами 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
Надо “ SELECT * WHERE a=b FROM c ” или “ SELECT WHERE a=b FROM c ON * ” ?
Если вы похожи на меня, то согласитесь: SQL - это одна из тех штук, которые на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить каждый простой запрос, чтобы найти правильный синтаксис.
А потом начинаются джойны, агрегирование, подзапросы, и получается совсем белиберда. Вроде такой:
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;
Буэ! Такое спугнет любого новичка, или даже разработчика среднего уровня, если он видит SQL впервые. Но не все так плохо.
Легко запомнить то, что интуитивно понятно, и с помощью этого руководства я надеюсь снизить порог входа в SQL для новичков, а уже опытным предложить по-новому взглянуть на SQL.
Не смотря на то, что синтаксис SQL почти не отличается в разных базах данных, в этой статье для запросов используется PostgreSQL. Некоторые примеры будут работать в MySQL и других базах.
1. Три волшебных слова
В SQL много ключевых слов, но SELECT , FROM и WHERE присутствуют практически в каждом запросе. Чуть позже вы поймете, что эти три слова представляют собой самые фундаментальные аспекты построения запросов к базе, а другие, более сложные запросы, являются всего лишь надстройками над ними.
2. Наша база
Давайте взглянем на базу данных, которую мы будем использовать в качестве примера в этой статье:
У нас есть книжная библиотека и люди. Также есть специальная таблица для учета выданных книг.
- В таблице "books" хранится информация о заголовке, авторе, дате публикации и наличии книги. Все просто.
- В таблице “members” - имена и фамилии всех записавшихся в библиотеку людей.
- В таблице “borrowings” хранится информация о взятых из библиотеки книгах. Колонка bookid относится к идентификатору взятой книги в таблице “books”, а колонка memberid относится к соответствующему человеку из таблицы “members”. У нас также есть дата выдачи и дата, когда книгу нужно вернуть.
3. Простой запрос
Давайте начнем с простого запроса: нам нужны имена и идентификаторы (id) всех книг, написанных автором “Dan Brown”
Запрос будет таким:
SELECT bookid AS "id", title FROM books WHERE author="Dan Brown";
А результат таким:
| id | title |
|---|---|
| 2 | The Lost Symbol |
| 4 | Inferno |
Довольно просто. Давайте разберем запрос чтобы понять, что происходит.
3.1 FROM - откуда берем данные
Сейчас это может показаться очевидным, но FROM будет очень важен позже, когда мы перейдем к соединениям и подзапросам.
FROM указывает на таблицу, по которой нужно делать запрос. Это может быть уже существующая таблица (как в примере выше), или таблица, создаваемая на лету через соединения или подзапросы.
3.2 WHERE - какие данные показываем
WHERE просто-напросто ведет себя как фильтр строк , которые мы хотим вывести. В нашем случае мы хотим видеть только те строки, где значение в колонке author - это “Dan Brown”.
3.3 SELECT - как показываем данные
Теперь, когда у нас есть все нужные нам колонки из нужной нам таблицы, нужно решить, как именно показывать эти данные. В нашем случае нужны только названия и идентификаторы книг, так что именно это мы и выберем с помощью SELECT . Заодно можно переименовать колонку используя AS .
Весь запрос можно визуализировать с помощью простой диаграммы:
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 |
По большей части запрос похож на предыдущий за исключением секции FROM . Это означает, что мы запрашиваем данные из другой таблицы . Мы не обращаемся ни к таблице “books”, ни к таблице “borrowings”. Вместо этого мы обращаемся к новой таблице , которая создалась соединением этих двух таблиц.
borrowings JOIN books ON borrowings.bookid=books.bookid - это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц "books" и "borrowings", в которых значения bookid совпадают. Результатом такого слияния будет:
А потом мы делаем запрос к этой таблице так же, как в примере выше. Это значит, что при соединении таблиц нужно заботиться только о том, как провести это соединение. А потом запрос становится таким же понятным, как в случае с «простым запросом» из пункта 3.
Давайте попробуем чуть более сложное соединение с двумя таблицами.
Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”.
На этот раз давайте пойдем снизу вверх:
Шаг Step 1 - откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так:
borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid
Результат соединения можно увидеть по ссылке .
Шаг 2 - какие данные показываем? Нас интересуют только те данные, где автор книги - “Dan Brown”
WHERE books.author="Dan Brown"
Шаг 3 - как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги:
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. Агрегирование
Грубо говоря, агрегирования нужны для конвертации нескольких строк в одну . При этом, во время агрегирования для разных колонок используется разная логика.
Давайте продолжим наш пример, в котором появляются повторяющиеся имена. Видно, что Ellen Horton взяла больше одной книги, но это не самый лучший способ показать эту информацию. Можно сделать другой запрос:
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 |
Почти все агрегации идут вместе с выражением GROUP BY . Эта штука превращает таблицу, которую можно было бы получить запросом, в группы таблиц. Каждая группа соответствует уникальному значению (или группе значений) колонки, которую мы указали в GROUP BY . В нашем примере мы конвертируем результат из прошлого упражнения в группу строк. Мы также проводим агрегирование с count , которая конвертирует несколько строк в целое значение (в нашем случае это количество строк). Потом это значение приписывается каждой группе.
Каждая строка в результате представляет собой результат агрегирования каждой группы.
Можно прийти к логическому выводу, что все поля в результате должны быть или указаны в GROUP BY , или по ним должно производиться агрегирование. Потому что все другие поля могут отличаться друг от друга в разных строках, и если выбирать их SELECT "ом, то непонятно, какие из возможных значений нужно брать.
В примере выше функция count обрабатывала все строки (так как мы считали количество строк). Другие функции вроде sum или max обрабатывают только указанные строки. Например, если мы хотим узнать количество книг, написанных каждым автором, то нужен такой запрос:
SELECT author, sum(stock) FROM books GROUP BY author;
Результат:
| author | sum |
|---|---|
| Robin Sharma | 4 |
| Dan Brown | 6 |
| John Green | 3 |
| Amish Tripathi | 2 |
Здесь функция sum обрабатывает только колонку stock и считает сумму всех значений в каждой группе.
6. Подзапросы
Подзапросы это обычные SQL-запросы, встроенные в более крупные запросы. Они делятся на три вида по типу возвращаемого результата.
6.1 Двумерная таблица
Есть запросы, которые возвращают несколько колонок. Хороший пример это запрос из прошлого упражнения по агрегированию. Будучи подзапросом, он просто вернет еще одну таблицу, по которой можно делать новые запросы. Продолжая предыдущее упражнение, если мы хотим узнать количество книг, написанных автором “Robin Sharma”, то один из возможных способов - использовать подзапросы:
SELECT * FROM (SELECT author, sum(stock) FROM books GROUP BY author) AS results WHERE author="Robin Sharma";
Результат:
Можно записать как: ["Robin Sharma", "Dan Brown"]
2. Теперь используем этот результат в новом запросе:
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 семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок SELECT "ом, мы задаем знаения SET "ом.
Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:
UPDATE books SET stock=0 WHERE author="Dan Brown";
WHERE делает то же самое, что раньше: выбирает строки. Вместо SELECT , который использовался при чтении, мы теперь используем SET . Однако, теперь нужно указать не только имя колонки, но и новое значение для этой колонки в выбранных строках.
7.2 Delete
Запрос DELETE это просто запрос SELECT или UPDATE без названий колонок. Серьезно. Как и в случае с SELECT и UPDATE , блок WHERE остается таким же: он выбирает строки, которые нужно удалить. Операция удаления уничтожает всю строку, так что не имеет смысла указывать отдельные колонки. Так что, если мы решим не обнулять количество книг Дэна Брауна, а вообще удалить все записи, то можно сделать такой запрос:
DELETE FROM books WHERE author="Dan Brown";
7.3 Insert
Пожалуй, единственное, что отличается от других типов запросов, это INSERT . Формат такой:
INSERT INTO x (a,b,c) VALUES (x, y, z);
Где a , b , c это названия колонок, а x , y и z это значения, которые нужно вставить в эти колонки, в том же порядке. Вот, в принципе, и все.
Взглянем на конкретный пример. Вот запрос с INSERT , который заполняет всю таблицу "books":
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 , FROM , WHERE , GROUP BY , и рассмотреть отдельные компоненты подзапросов.
Вот он в более удобном для чтения виде:
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 появился в 70-е годы. Его прототип был разработан фирмой IBM и известен под названием SEQUEL (Structured English QUEry Language). SQL вобрал в себя достоинства реляционной модели, в частности то, что в ее основе лежит математический аппарат реляционной алгебры и реляционного исчисления, используя при этом сравнительно небольшое число операторов и простой синтаксис.
Благодаря своим качествам язык SQL стал вначале «де-факто», а затем и официально утвержденным в качестве стандарта языка для работы с реляционными базами данных, поддерживаемого всеми ведущими мировыми фирмами, действующими в области технологии баз данных. Использование выразительного и эффективного стандартного языка позволило в настоящее время обеспечить высокую степень независимости разрабатываемых прикладных программных систем от конкретного типа используемой СУБД, существенно поднять уровень и унификацию инструментальных средств разработки приложений, работающих с реляционными базами данных.
Говоря о стандарте языка SQL, следует заметить, что большинство его коммерческих реализаций имеют большие или меньшие отступления от стандарта. Это, конечно, ухудшает совместимость систем, использующих различные «диалекты» SQL. С другой стороны, полезные расширения реализаций языка относительно стандарта являются средством развития языка и со временем включаются в новые редакции стандарта.
Языку SQL посвящено большое число книг, в том числе и учебного назначения, некоторые из них приведены в списке литературы данного пособия , в частности, учебное пособие специально посвящено вопросам практического изучения языка SQL. В связи с этим в данном пособии мы рассмотрим лишь важные общие особенности данного языка, имеющие значение для последующего изложения материала.
8.1. Отличие SQL от процедурных языков программирования
Язык SQL – относится к классу непроцедурных языков программирования. В отличие от универсальных процедурных языков, которые также могут быть использованы для работы с базами данных, язык SQL ориентирован не на записи , а на множества . Это означает следующее. В качестве входной информации для формулируемого на языке SQL запроса к базе данных используется множество кортежей-записей одной или нескольких таблицотношений. В результате выполнения запроса также образуется множество кортежей результирующей таблицы-отношения. Другими словами в SQL результатом любой операции над отношениями также является отношение. Запрос SQL задает не процедуру, т.е. последовательность действий, необходимых для получения результата, а условия, которым должны удовлетворять кортежи результирующего отношения, сформулированные в терминах входного (или входных) отношений.
8.2. Формы и составные части SQL
Существуют и используются две формы языка SQL: интерактивный SQL
и встроенный SQL.
Интерактивный SQL используется для непосредственного ввода и получения результата SQL-запросов пользователем в интерактивном режиме.
Встроенный SQL состоит из команд SQL, встроенных внутрь программ, которые обычно написаны на некотором другом языке (Паскаль, С, С++ и др.). Это делает программы, написанные на таких языках, более мощными и эффективными, обеспечивая возможность работы с помощью них с данными, хранящимися в реляционных базах данных, требуя, однако, введения дополнительных средств, обеспечивающих интерфейс SQL с языком, в который он встраивается.
И интерактивный, и встроенный SQL обычно разделяют на следующие составные части.
Язык Определения Данных – DDL (Data Definition Language), дает возможность создания, изменения и удаления различных объектов базы данных (таблиц, индексов, пользователей, привилегий и т.д.).
К числу дополнительных функций языка определения данных DDL могут быть включены также средства определения ограничений целостности данных,
определения порядка в структурах хранения данных, описания элементов физического уровня хранения данных.
Язык Обработки Данных – DML (Data Manipulation Language),
предоставляет возможность выборки информации из базы данных и преобразования хранящейся в ней данных.
Тем не менее, это не два различных языка, а компоненты единого SQL.
8.3. Условия и терминология
Ключевые слова – это используемые в выражениях SQL слова, которые имеют специальное назначение. Например, они могут обозначать конкретные команды SQL. Ключевые слова нельзя использовать для других целей, например в качестве имен объектов базы данных.
Операторы SQL являются инструкциями, с помощью которых производится обращение SQL к базе данных. Операторы состоят из одной или более отдельных логических частей, называемых предложениями. Предложения начинаются соответствующим ключевым словом и состоят из ключевых слов и аргументов.
Следует обратить внимание на то, что термины, используемые в языке SQL, несколько отличаются от терминов, принятых при описании реляционной модели. В частности, вместо термина отношение в нем используется термин таблица , вместо терминов кортеж и атрибут , соответственно, строка и столбец .
8.4. Выборка данных. Оператор SELECT
Простейшие SELECT- запросы
Оператор SELECT (ВЫБРАТЬ ) языка SQL является самым важным и самым часто используемым оператором. Он предназначен для выборки информации из таблиц базы данных. Упрощенный синтаксис оператора SELECT выглядит следующим образом.
SELECT < список атрибутов >
FROM < список таблиц>
В квадратных скобках указаны элементы, которые могут в запросе отсутствовать.
Ключевое слово SELECT сообщает СУБД, что данное предложение является запросом на извлечение информации. После слова SELECT через запятую перечисляются наименования полей (список атрибутов), содержимое которых запрашивается.
Обязательным ключевым словом в предложении-запросе SELECT является слово FROM (из). За ключевым словом FROM указывается список разделенных запятыми имен таблиц, из которых извлекается информация.
Например,
SELECT NAME, SURNAME FROM STUDENT;
SQL-запрос должен заканчиваться символом «точка с запятой». Приведенный запрос осуществляет выборку всех значений полей NAME и
SURNAME из таблицы STUDENT.
Его результатом является таблица следующего вида
Порядок следования столбцов в этой таблице соответствует порядку полей NAME и SURNAME , указанному в запросе, а не их порядку во входной таблице
STUDENT.
Обратим внимание на то, что получаемые в результате SQL-запроса таблицы не в полной мере отвечают определению реляционного отношения. В
частности в них могут оказаться дубликаты кортежей с одинаковыми значениями атрибутов.
Например, запрос: “Получить список названий городов, в которых живут студенты, сведения о которых находятся в таблице STUDENT ”, можно записать в следующем виде
SELECT CITY FROM STUDENT ;
Его результатом будет таблица
Белгород
Можно видеть, что в этой таблице могут встречаться одинаковые строки. Они выделены жирным шрифтом.
Для исключения из результата SELECT -запроса повторяющихся записей используется ключевое слово DISTINCT (отличный). Если запрос SELECT извлекает множество полей, то DISTINCT исключает дубликаты строк, в которых значения всех выбранных полей идентичны.
Введение в выражение оператора SELECT , предложения, определяемого ключевым словом WHERE (где), позволяет вводить выражение условия (предикат), принимающее значение истина или ложь для значений полей строк таблиц, к которым обращается оператор SELECT . Предложение WHERE определяет, какие строки указанных таблиц должны быть выбраны. В таблицу, являющуюся результатом запроса, включаются только те строки, для которых условие (предикат), указанное в предложении WHERE , принимает значение истина.
Написать запрос, выполняющий выборку имен (NAME ) всех студентов с фамилией (SURNAME ) Петров, сведения о которых находятся в таблице
SELECT SURNAME, NAME
FROM STUDENT
WHERE SURNAME = ‘ Петров ’;
В задаваемых в предложении WHERE условиях могут использоваться операции сравнения, задаваемые следующими операторами: = (равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно), <> (не равно), а также логические операторы AND , OR и NOT .
Например, запрос для получения имен и фамилий студентов, обучающихся на третьем курсе и получающих стипендию (размер стипендии больше нуля) будет выглядеть таким образом
SELECT NAME, SURNAME FROM STUDENT
WHERE KURS = 3 AND STIPEND > 0 ;
8.5. Реализация операций реляционной алгебры средствами языка SQL. Реляционная полнота SQL
В предыдущих разделах, посвященных рассмотрению реляционной алгебры, говорилось, что одной из важных сторон наличия в реляционной модели такого математического аппарата является возможность оценки и доказательства реляционной полноты практически используемых языков запросов к базам данных, в частности языка SQL. Для того, чтобы показать, что язык SQL является реляционно полным, нужно показать, что любой оператор реляционной алгебры может быть выражен средствами SQL. На самом деле достаточно показать, что средствами SQL можно выразить любой из примитивных реляционных операторов. Ниже приведены примеры реализации реляционных операторов с помощью языка SQL.
Оператор объединения
Реляционная алгебра: A UNION B Оператор SQL:
SELECT * FROM A
SELECT * FROM B ;
Оператор пересечения
Реляционная алгебра: A INTERSECT B
Оператор SQL:
SELECT A. ПОЛЕ1, A. ПОЛЕ2, …,
FROM A , B
WHERE A. ПОЛЕ1=B. ПОЛЕ1 AND A. ПОЛЕ2=B. ПОЛЕ2 AND …;
SELECT A.* FROM A, B
WHERE A.pk =B.pk;
Оператор вычитания
Реляционная алгебра: A MINUS B Оператор SQL:
SELECT * FROM A
WHERE A.pk NOT IN (SELECT pk FROM B);
где A.pk и B.pk первичные ключи таблиц A и B
Оператор декартового произведения
Реляционная алгебра: A TIMES B Оператор SQL:
FROM A , B ;
SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, …
FROM A CROSS JOIN B ;
Оператор проекции
Реляционная алгебра: A Оператор SQL:
SELECT DISTINCT X , Y , …, Z FROM A ;
Оператор выборки
Реляционная алгебра: A WHERE θ Оператор SQL:
SELECT * FROM A
WHERE θ ;
Оператор θ -соединения
Реляционная алгебра: (A TIMES B) WHERE θ Оператор SQL:
SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, …
FROM A , B
WHERE θ ;
SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, …
FROM A CROSS JOIN B WHERE θ ;
Оператор деления
Реляционная алгебра: A(X,Y) DEVIDE BY B(Y) Оператор SQL:
SELECT DISTINCT A . X FROM A
(SELECT *
(SELECT * FROM A A1
A1. X=A. X AND A1. Y=B. Y));
Таким образом, приведенные выражения доказывают, что язык SQL, как и реляционная алгебра, является реляционно полным.
Следует обратить внимание на то, если в приведенных запросах в таблицах будут присутствовать NULL -значения (см. ниже раздел 9.1), то все вышеперечисленные запросы могут отработать неверно, т.к. NULL < > NULL и NULL = NULL – есть ложь.
Это, однако, не опровергает сделанного вывода о реляционной полноте SQL, так как NULL -значения реляционной моделью не поддерживаются.
Программирование на T - SQL
Синтаксис и соглашения T-SQL
Правила формирования идентификаторов
Все объекты в SQL Server имеют имена (идентификаторы). Примерами объектов являются таблицы, представления, хранимые процедуры и т.д. Идентификаторы могут включать до 128 символов, в частности, буквы, символы _ @ $ # и цифры.
Первый символ всегда должен быть буквенным. Для переменных и временных таблиц используются специальные схемы именования. Имя объекта не может содержать пробелов и совпадать с зарезервированным ключевым словом SQL Server, независимо от используемого регистра символов. Путем заключения идентификаторов в квадратные скобки, в именах объектов можно использовать запрещенные символы.
Завершение инструкции
Стандарт ANSI SQL требует помещения в конце каждой инструкции точки с запятой. В то же время при программировании на языке T-SQL точка с запятой не обязательна.
Комментарии
Язык T-SQL допускает использование комментариев двух стилей: ANCI и языка С. Первый из них начинается с двух дефисов и заканчивается в конце строки:
Это однострочный комментарий стиля ANSI
Также комментарии стиля ANSI могут вставляться в конце строки инструкции:
SELECT CityName – извлекаемые столбцы
FROM City – исходная таблица
WHERE IdCity = 1; -- ограничение на строки
Редактор SQL может применять и удалять комментарии во всех выделенных строках. Для этого нужно выбрать соответствующие команды в меню Правка или на панели инструментов.
Комментарии стиля языка С начинаются с косой черты и звездочки (/*) и заканчиваются теми же символами в обратной последовательности. Этот тип комментариев лучше использовать для комментирования блоков строк, таких как заголовки или большие тестовые запросы.
многострочного
комментария
Одним из главных достоинств комментариев стиля С является то, что многострочные запросы в них можно выполнять, даже не раскомментируя.
Пакеты T-SQL
Запросом называют одну инструкцию T-SQL, а пакетом - их набор. Вся последовательность инструкций пакета отправляется серверу из клиентских приложений как одна цельная единица.
SQL Server рассматривает весь пакет как рабочую единицу. Наличие ошибки хотя бы в одной инструкции приведет к невозможности выполнения всего пакета. В то же время грамматический разбор не проверяет имена объектов и схем, так как сама схема может измениться в процессе выполнения инструкции.
Файл сценария SQL и окно анализатора запросов (Query Analyzer) может содержать несколько пакетов. В данном случае все пакеты разделяют ключевые слова терминаторов. По умолчанию этим ключевым словом является GO, и оно должно быть единственным в строке. Все другие символы (даже комментарии) нейтрализуют разделитель пакета.
Отладка T-SQL
Когда редактор SQL обнаруживает ошибку, он отображает ее характер и номер строки в пакете. Дважды щелкнув на ошибке, можно сразу же переместиться к соответствующей строке.
В утилиту Management Studio версии SQL Server 2005 не включен отладчик языка T-SQL, - он присутствует в пакете Visual Studio.
SQL Server предлагает несколько команд, облегчающих отладку пакетов. В частности, команда PRINT отправляет сообщение без генерации результирующего набора данных. Команду PRINT можно использовать для отслеживания хода выполнения пакета. Когда анализатор запросов находится в режиме сетки, выполните следующий пакет:
SELECT CityName
FROM City
WHERE IdCity = 1;
PRINT "Контрольная точка" ;
Результирующий набор данных отобразится в сетке и будет состоять из одной строки. В то же время во вкладке «Сообщения» отобразится следующий результат:
(строк обработано: 1)
Контрольная точка
Переменные
Переменные T-SQL создаются с помощью команды DECLARE, имеющей следующий синтаксис:
DECLARE @Имя_Переменной Тип_Данных [,
@Имя_Переменной Тип_Данных, …]
Все имена локальных переменных должны начинаться символом @. Например, для объявления локальной переменной UStr, которая хранит до 16 символов Unicode, можно использовать следующую инструкцию:
DECLARE @UStr varchar (16)
Используемые для переменных типы данных в точности совпадают с существующими в таблицах. В одной команде DECLARE через запятую может быть перечислено несколько переменных. В частности в следующем примере создаются две целочисленные переменные a и b:
DECLARE
@a int ,
@b int
Область определения переменных (т.е. срок их жизни) распространяется только на текущий пакет. По умолчанию только что созданные переменные содержат пустые значения NULL и до включения в выражения должны быть инициализированы.
Задание значений переменных
В настоящее время в языке SQL предусмотрены два способа задания значения переменной - для этой цели можно использовать оператор SELECT или SET. С точки зрения выполняемых функций эти операторы действуют почти одинаково, не считая того, что оператор SELECT позволяет получить исходное присваиваемое значение из таблицы, указанной в операторе SELECT.
Оператор SET обычно используется для задания значений переменных в такой форме, какая более часто встречается в процедурных языках. В качестве типичных примеров применения этого оператора можно указать следующие:
SET @a = 1;
SET @b = @a * 1.5
Обратите внимание на то, что во всех этих операторах непосредственно осуществляются операции присваивания, в которых используются либо явно заданные значения, либо другие переменные. С помощью оператора SET невозможно присвоить переменной значение, полученное с помощью запроса; запрос должен быть выполнен отдельно и только после этого полученный результат может быть присвоен с помощью оператора SET. Например, попытка выполнения такого оператора вызывает ошибку:
DECLARE @c int
SET @c = COUNT (*) FROM City
SELECT @c
а следующий оператор выполняется вполне успешно:
DECLARE @c int
SET @c = (SELECT COUNT (*) FROM City)
SELECT @c
Оператор SELECT обычно используется для присваивания значений переменным, если источником информации, которая должна быть сохранена в переменной, является запрос. Например, действия, осуществляемые в приведенном выше коде, гораздо чаще реализуются с помощью оператора SELECT:
DECLARE @c int
SELECT @c = COUNT (*) FROM City
SELECT @c
Обратите внимание на то, что данный код немного понятнее (в частности, он более лаконичен, хотя и выполняет те же действия).
Таким образом, можно, сформулировать следующее общепринятое соглашение по использованию того и другого оператора.
Оператор SET используется, если должна быть выполнена простая операция присваивания значения переменной, т.е. если присваиваемое значение уже задано явно в форме определенного значения или в виде какой-то другой переменной.
- Оператор SELECT применяется, если присваивание значения переменной должно быть основано на запросе.
Использование переменных в запросах SQL
Одним из полезных свойств языка T-SQL является то, что переменные могут использоваться в запросах без необходимости создания сложных динамических строк, встраивающих переменные в программный код. Динамический SQL продолжает свое существование, но одиночное значение можно изменить проще - с помощью переменной.
Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE:
DECLARE @IdProd int ;
SET @IdProd = 1;
SELECT
FROM Product
WHERE IdProd = @IdProd;
Глобальные системные переменные
В SQL Server имеется более тридцати глобальных переменных, не имеющих параметров, которые определяются и поддерживаются системой. Все глобальные переменные имеют префикс в виде двух символов @. Вы можете извлечь значение любой из них с помощью простого запроса SELECT, как в следующем примере:
SELECT @@CONNECTIONS
Здесь используется глобальная переменная @@CONNECTIONS для извлечения количества подключений к SQL Server со времени запуска программы.
Среди наиболее часто применяемых системных переменных можно отметить следующие:
- @@ERROR - Содержит номер ошибки, возникшей при выполнении последнего оператора T-SQL в текущем соединении. Если ошибка не обнаружена, содержит 0. Значение этой системной переменной переустанавливается после выполнения каждого очередного оператора. Если требуется сохранить содержащееся в ней значение, то это значение следует переносить в локальную переменную сразу же после выполнения оператора, для которого должен быть сохранен код ошибки.
- @@IDENTITY - Содержит последнее идентификационное значение, вставленное в базу данных в результате выполнения последнего оператора INSERT. Если в последнем операторе INSERT не произошла выработка идентификационного значения, системная переменная @@IDENTITY содержит NULL. Это утверждение остается справедливым, даже если отсутствие идентификационного значения было вызвано аварийным завершением при выполнении оператора. А если с помощью одного оператора осуществляется несколько операций вставки, этой системной переменной присваивается только последнее идентификационное значение.
- @@ROWCOUNT - Одна из наиболее широко используемых системных переменных. Возвращает информацию о количестве строк, затронутых последним оператором. Обычно применяется для контроля ошибок, отличных от тех, которые относятся к категории ошибок этапа прогона программы. Например, если в программе обнаруживается, что после вызова на выполнение оператора DELETE с конструкцией WHERE количество затронутых строк равно нулю, то можно сделать вывод, что произошло нечто непредвиденное. После этого сообщение об ошибке может быть активизировано вручную.
! Следует отметить, что с версии SQL Server 2000 глобальные переменные принято называть функциями. Название глобальные сбивало пользователей с толку, позволяя думать, что область действия таких переменных шире, чем у локальных. Глобальным переменным часто ошибочно приписывалась возможность хранить информацию, независимо от того, включена она в пакет либо нет, что, естественно, не соответствовало действительности.
Средства управления потоком команд. Программные конструкции
В языке T-SQL предусмотрена большая часть классических процедурных средств управления ходом выполнения программы, в т.ч. условная конструкция и циклы.
Оператор IF. . . ELSE
Операторы IF. . .ELSE действуют в языке T-SQL в основном так же, как и в любых других языках программирования. Общий синтаксис этого оператора имеет следующий вид:
IF Логическое выражение
SQL инструкция I BEGIN Блок SQL инструкций END
SQL инструкция | BEGIN Блок SQL инструкций END]
В качестве логического выражения может быть задано практически любое выражение, результат вычисления которого приводит к возврату булева значения.
Следует учитывать, что выполняемым по условию считается только тот оператор, который непосредственно следует за оператором IF (ближайшим к нему). Вместо одного оператора можно предусмотреть выполнение по условию нескольких операторов, объединив их в блок кода с помощью конструкции BEGIN…END.
В приведенном ниже примере условие IF не выполняется, что предотвращает выполнение следующего за ним оператора.
IF 1 = 0
PRINT "Первая строка"
PRINT "Вторая строка"
Необязательная команда ELSE позволяет задать инструкцию, которая будет выполнена в случае, если условие IF не будет выполнено. Подобно IF, оператор ELSE управляет только непосредственно следующей за ним командой или блоком кода заключенным между BEGIN…END.
Несмотря на то, что оператор IF выглядит ограниченным, его предложение условия может включать в себя мощные функции, подобно предложению WHERE. В частности это выражения IF EXISTS().
Выражение IF EXISTS() использует в качестве условия наличие какой-либо строки, возвращенной инструкцией SELECT. Так как ищутся любые строки, список столбцов в инструкции SELECT можно заменить звездочкой. Этот метод работает быстрее, чем проверка условия @@ROWCOUNT>0, потому что не требуется подсчет общего количества строк. Как только хотя бы одна строка удовлетворяет условию IF EXISTS(), запрос может продолжать выполнение.
В следующем примере выражение IF EXISTS используется для проверки наличия у клиента с кодом 1 каких-либо заказов перед удалением его из базы. Если по данному клиенту есть информация хотя бы по одному заказу, удаление не производится.
IF EXISTS (SELECT * FROM WHERE IdCust = 1)
PRINT "Невозможно удалить клиента поскольку в базе имеются связанные с ним записи"
ELSE
WHERE IdCust = 1
PRINT "Удаление произведено успешно"
Операторы WHILE, BREAK и CONTINUE
Оператор WHILE в языке SQL действует во многом так же, как и в других языках, с которыми обычно приходится работать программисту. По сути, в этом операторе до начала каждого прохода по циклу проверяется некоторое условие. Если перед очередным проходом по циклу проверка условия приводит к получению значения TRUE, осуществляется проход по циклу, в противном случае выполнение оператора завершается.
Оператор WHILE имеет следующий синтаксис:
WHILE Логическое выражение
SQL инструкция I
Блок SQL инструкций
Безусловно, с помощью оператора WHILE можно обеспечить выполнение в цикле только одного оператора (по аналогии с тем, как обычно используется оператор IF), но на практике конструкции WHILE, за которыми не следует блок BEGIN. . .END, соответствующий полному формату оператора, встречаются редко.
Оператор BREAK позволяет немедленно выйти из цикла, не ожидая того, как будет выполнен проход до конца цикла и произойдет повторная проверка условного выражения.
Оператор CONTINUE позволяет прервать отдельную итерацию цикла. Кратко можно описать действие оператора CONTINUE так, что он обеспечивает переход в начало цикла WHILE. Сразу после обнаружения оператора CONTINUE в цикле, независимо от того, где он находится, происходит переход в начало цикла и повторное вычисление условного выражения (а если значение этого выражения больше не равно TRUE, осуществляется выход из цикла).
Следующий короткий сценарий демонстрирует использование оператора WHILE для создания цикла:
DECLARE @Temp int ;
SET @Temp = 0;
WHILE @Temp < 3
BEGIN
PRINT @Temp;
SET @Temp = @Temp + 1;
Здесь в цикле целочисленная переменная @Temp увеличивается с 0 до 3 и на каждой итерации ее значение выводится на экран.
Оператор RETURN
Оператор RETURN используется для останова выполнения пакета, а следовательно, хранимой процедуры и триггера (рассматриваются в следующих лабораторных занятиях).
Доступный в Интернете словарь Merriam-Webster определяет базу данных как большой набор данных , организованный специальным образом для обеспечения быстрого поиска и извлечения данных (например, с помощью компьютера).
Система управления базами данных (СУБД) , как правило, представляет собой комплект библиотек, приложений и утилит , освобождающих разработчика приложения от груза забот, касающихся деталей хранения и управления данными . СУБД также предоставляет средства поиска и обновления записей.
За многие годы для решения различных видов проблем хранения данных было создано множество СУБД.
Типы баз данных
В 1960-70-х годах разрабатывались базы данных, которые тем или иным способом решали проблему повторяющихся групп. Эти методы привели к созданию моделей систем управления базами данных. Основой для таких моделей, используемых и по сей день, послужили исследования, проводимые в компании IBM.
Одним из основополагающих факторов проектирования ранних СУБД была эффективность. Гораздо легче манипулировать записями базы данных, имеющими фиксированную длину или, по крайней мере, фиксированное количество элементов в записи (столбцов в строке). Так удается избежать проблемы повторяющихся групп. Тот, кто программировал на каком-либо процедурном языке, без труда поймет, что в этом случае можно прочитать каждую запись базы данных в простую структуру C. Однако в реальной жизни такие удачные ситуации встречаются редко, поэтому программистам приходится обрабатывать не так удобно структурированные данные.
База данных с сетевой структурой
Сетевая модель вводит в базы данных указатели - записи, содержащие ссылки на другие записи. Так, можно хранить запись для каждого заказчика. Каждый заказчик в течение некоторого времени разместил у нас множество заказов. Данные расположены так, что запись заказчика содержит указатель ровно на одну запись заказа. Каждая запись заказа содержит как данные по этому конкретному заказу, так и указатель на другую запись заказа. Тогда в приложении-конвертере валют, которым мы занимались ранее, можно было бы использовать структуру, которая выглядела бы примерно так (рис. 1.):
Рис. 1. Структура записей конвертера валют
Данные загружаются и получается связанный (отсюда и название модели – сетевая) список для языков (рис. 2):
Рис. 2. Связанный список
Два разных типа записей, представленные на рисунке, будут храниться отдельно, каждый - в своей собственной таблице.
Конечно же, было бы более целесообразно, если бы названия языков не повторялись в базе снова и снова. Вероятно, лучше ввести третью таблицу, в которой содержались бы языки и идентификатор (часто в этом качестве используется целое число), который бы использовался для ссылки на запись таблицы языков из записей другого типа. Такой идентификатор называется ключом.
У сетевой модели базы данных есть несколько важных преимуществ. Если нужно найти все записи одного типа, относящиеся к определенной записи другого типа (например, языки, на которых говорят в одной из стран), то можно сделать это очень быстро, следуя по указателям, начиная с указанной записи.
Есть, однако, и недостатки. Если нам нужен перечень стран, в которых говорят по-французски, придется пройти по ссылкам всех записей стран, и для больших баз данных такая операция будет выполняться очень медленно. Это можно исправить, создав другие связанные списки указателей специально для языков, но такое решение быстро становится слишком сложным и, конечно же, не является универсальным, поскольку необходимо заранее решить, как будут организованы ссылки.
К тому же, писать приложение, использующее сетевую модель базы данных, достаточно утомительно, потому что обычно ответственность за создание и поддержание указателей по мере обновления и удаления записей лежит на приложении.
Иерархическая модель базы данных
В конце 1960-х годов IBM использовала в СУБД IMS иерархическую модель построения базы. В этой модели проблема повторяющихся групп решалась за счет представления одних записей как состоящих из множеств других.
Это можно представить как «спецификацию материалов», которая применяется для описания составляющих сложного продукта. Например, машина состоит (скажем) из шасси, кузова, двигателя и четырех колес. Каждый из этих основных компонентов в свою очередь состоит из некоторых других. Двигатель включает в себя несколько цилиндров, головку цилиндра и коленчатый вал. Эти компоненты опять-таки состоят из более мелких; так мы доходим до гаек и болтов, которыми комплектуются любые составляющие автомобиля.
Иерархическая модель базы данных применяется до сих пор. Иерархическая СУБД способна оптимизировать хранение данных в том, что касается некоторых отдельных вопросов, например можно без труда определить, в каком автомобиле используется какая-то конкретная деталь.
Реляционная модель базы данных
Огромный скачок в развитии теории систем управления базами данных произошел в 1970 году, когда был опубликован доклад Е. Ф. Код- да (E. F. Codd) «Реляционная модель для больших разделяемых банков данных» («A Relational Model of Data for Large Shared Data Banks»), см. эту ссылку. В этом поистине революционном труде вводилось понятие отношений и было показано, как использовать таблицы для представления фактов, которые устанавливают отношения с объектами «реального мира» и, следовательно, хранят данные о них.
К этому времени уже стало очевидно, что эффективность, достижение которой первоначально являлось основополагающим при проектировании базы, не так важна, как целостность данных. Реляционная модель придает гораздо большее значение целостности данных, чем любая другая ранее применявшаяся модель.
Реляционную систему управления базами данных определяет набор правил. Во-первых, запись таблицы носит название «кортеж», и именно этот термин используется в части документации на PostgreSQL. Кортеж - это упорядоченная группа компонентов (или атрибутов), каждый из которых принадлежит определенному типу. Все кортежи построены по одному шаблону, во всех одинаковое количество компонентов одинаковых типов. Приведем пример набора кортежей:
{"France", "FRF", 6.56} {"Belgium", "BEF", 40.1}
Каждый из этих кортежей состоит из трех атрибутов: названия страны (строковый тип), валюты (строковый тип) и валютного курса (тип с плавающей точкой). В реляционной базе данных все записи, добавляемые в это множество (или таблицу), должны следовать этой же форме, поэтому записи, представленные ниже, не могут быть добавлены:
Более того, ни в одной таблице не может быть повторения кортежей. То есть в любой таблице реляционной базы данных повторяющиеся строки или записи не разрешены.
Такая мера может выглядеть как драконовская, поскольку может показаться, что для системы, которая хранит заказы, размещаемые клиентами, это означает, что один клиент не сможет заказать какой-то продукт дважды.
Каждый атрибут записи должен быть «атомарным», то есть представлять собой простую порцию информации, а не другую запись или список других аргументов. Кроме того, типы соответствующих атрибутов в каждой записи должны совпадать, как было показано выше. Технически это означает, что они должны быть получены из одного и того же набора значений или домена. Практически же все они должны быть или строками, или целыми числами, или числами с плавающей точкой, или же принадлежать какому-то другому типу, поддерживаему СУБД.
Атрибут, по которому отличают записи, во всем остальном идентичные, называется ключом. В некоторых случаях в качестве ключа может выступать комбинация из нескольких атрибутов.
Атрибут (или атрибуты), предназначенный для того, чтобы отличить некоторую запись таблицы от всех остальных записей этой таблицы (или, другими словами, сделать запись уникальной), называется первичным ключом. В реляционной базе данных каждое отношение (таблица) должно иметь первичный ключ, то есть что-то, что делало бы каждую запись отличной от всех остальных в этой таблице.
Последнее правило, определяющее структуру реляционной базы данных, - это ссылочная целостность. Такое требование объясняется тем, что в любой момент времени все записи базы данных должны иметь смысл. Разработчик приложения, взаимодействующего с базой данных, должен быть внимателен, он обязан убедиться, что его код не нарушает целостности базы. Представьте, что происходит при удалении клиента. Если клиент удаляется из отношения CUSTOMER, необходимо удалить и все его заказы из таблицы ORDERS. В противном случае останутся записи о заказах, которым не сопоставлен клиент.
В следующих моих блогах будет представлена более подробная теоретическая и практическая информация о реляционных базах данных. Пока же запомните, что реляционная модель построена на таких математических понятиях, как множества и отношения, и что при создании систем следует придерживаться определенных правил.
Языки запросов SQL и друие
Реляционные системы управления базами данных, конечно же, предоставляют способы добавления и обновления данных, но это не главное, сила таких систем заключается в том, что они предоставляют пользователю возможность задавать вопросы о хранимых данных на специальном языке запросов. В отличие от более ранних баз данных, которые специально проектировались так, чтобы отвечать на определенные типы вопросов, касающихся содержащейся в них информации, реляционные базы данных являются гораздо более гибкими и отвечают на вопросы, которые еще не были известны при создании базы.
Предложенная Коддом реляционная модель использует тот факт, что отношения определяют множества, а множества можно обрабатывать математически. Кодд предположил, что в запросах мог бы применяться такой раздел теоретической логики, как исчисление предикатов, на его основе и построены языки запросов. Такой подход обеспечивает беспрецедентную производительность поиска и выборки множеств данных.
Одним из первых был реализован язык запросов QUEL, он использовался в созданной в конце 1970х годов базе данных Ingres. Еще один язык запросов, в котором применялся другой метод, назывался QBE (Query By Example - запрос по примеру). Приблизительно в то же самое время группа, работающая в исследовательском центре IBM, разработала язык структурированных запросов SQL (Structured Query Language), это название обычно произносится как «сиквел».
SQL - это стандартный язык запросов , наиболее распространенным его определением является стандарт ISO/IEC 9075:1992, «Information Technology - Database Languages - SQL» (или, проще говоря, SQL92) и его американский аналог ANSI X3.135-1992, отличающийся от первого лишь несколькими страницами обложки. Эти стандарты заменили ранее существовавший SQL89. На самом деле есть и более поздний стандарт, SQL99, но он еще не получил распространения, к тому же большая часть обновлений не затрагивает ядро языка SQL.
Существуют три уровня соответствия SQL92: Entry SQL, Intermediate SQL и Full SQL. Самым распространенным является уровень «Entry», и PostgreSQL очень близок к такому соответствию, хотя есть и небольшие различия. Разработчики занимаются исправлением незначительных упущений, и с каждой новой версией PostgreSQL становится все ближе к стандарту.
В языке SQL три типа команд:
- Data Manipulation Language (DML) - язык манипулирования данными. Это та часть SQL, которая используется в 90% случаев. Она состоит из команд добавления, удаления, обновления и, что важнее всего, выборки данных из базы данных.
- Data Definition Language (DDL) - язык определения данных. Это команды для создания таблиц и управления другими аспектами базы данных, структурированными на более высоком уровне, чем относящиеся к ним данные.
- Data Control Language (DCL) - язык управления данными
Это набор команд, контролирующих права доступа к данным. Многие пользователи баз данных никогда не применяют такие команды, поскольку работают в больших компаниях, где есть специальный администратор базы данных (или даже несколько), который занимается управлением базой данных, в его функции входит и контроль за правами доступа.
SQL
SQL практически повсеместно признан стандартным языком запросов и, как уже упоминалось, описан во многих международных стандартах. В наши дни почти каждая СУБД в той или иной степени поддерживает SQL. Это способствует унификации, т. к. приложение, написанное с применением SQL в качестве интерфейса к базе данных, может быть перенесено и использоваться на другой базе, при этом стоимость такого переноса в терминах затраченного времени и прилагаемых усилий будет невелика.
Однако под давлением рынка производители баз данных вынуждены создавать отличающиеся друг от друга продукты. Так появилось несколько диалектов SQL, чему способствовало и то, что в стандарте, описывающем язык, не определены команды для многих задач администрирования базы данных, которые представляют собой необходимую и очень важную составляющую при использовании базы в реальном мире. Поэтому существуют различия между диалектами SQL, принятыми (например) в Oracle, SQL Server и PostgreSQL.
SQL будет описываться на протяжении всей книги, пока же приведем несколько примеров, чтобы показать, на что этот язык похож. Оказывается, для того чтобы начать работать с SQL, не обязательно изучать его формальные правила.
Создадим при помощи SQL новую таблицу в базе данных. В этом примере создается таблица для товаров, предлагаемых на продажу, которые войдут в заказ:
CREATE TABLE item (item_id serial, description char(64) not null, cost_price numeric(7,2), sell_price numeric(7,2));
Здесь мы определили, что таблице необходим идентификатор, который бы действовал как первичный ключ, и что он должен автоматически генерироваться системой управления базой данных. Идентификатор имеет тип serial, а это означает, что каждый раз при добавлении нового элемента item в последовательности будет создан новый, уникальный item_id. Описание (description) - это текстовый атрибут, состоящий из 64 символов. Себестоимость (cost_price) и цена продажи (sell_price) определяются как числа с плавающей точкой, с двумя знаками после запятой.
Теперь используем SQL для заполнения только что созданной таблицы. В этом нет ничего сложного:
INSERT INTO item(description, cost_price, sell_price) values("Fan Small", 9.23, 15.75); INSERT INTO item(description, cost_price, sell_price) values("Fan Large", 13.36, 19.95); INSERT INTO item(description, cost_price, sell_price) values("Toothbrush", 0.75, 1.45);
Основа SQL - это оператор SELECT . Он применяется для создания результирующих множеств - групп записей (или атрибутов записей), которые соответствуют некоторому критерию. Эти критерии могут быть достаточно сложными. Результирующие множества могут использоваться в качестве целевых объектов для изменений, осуществляемых оператором UPDATE , или удалений, выполняемых DELETE .
Вот несколько примеров использования оператора SELECT:
SELECT * FROM customer, orderinfo WHERE orderinfo.customer_id = customer.customer_id GROUP BY customer_id SELECT customer.title, customer.fname, customer.lname, COUNT(orderinfo.orderinfo_id) AS "Number of orders" FROM customer, orderinfo WHERE customer.customer_id = orderinfo.customer_id GROUP BY customer.title, customer.fname, customer.lname
Эти операторы SELECT перечисляют все заказы клиентов в указанном порядке и подсчитывают количество заказов, сделанных каждым клиентом.
Например, база данных PostgreSQL предоставляет несколько способов доступа к данным, в частности можно:
- Использовать консольное приложение для выполнения операторов SQL
- Непосредственно встроить SQL в приложение
- Использовать вызовы функций API (Application Programming Interfaces, интерфейсов прикладного программирования) для подготовки и выполнения операторов SQL, просмотра результирующих множеств и обновления данных из множества различных языков программирования
- Прибегнуть к опосредованному доступу к данным базы PostgreSQL с применением драйвера ODBC (Open Database Connection - открытого интерфейса доступа к базам данных) или JDBC (Java Database Connectivity - интерфейса доступа Java-приложений к базам данных) или стандартной библиотеки, такой как DBI для языка Perl
Системы управления базами данных
СУБД , как уже говорилось ранее, - это набор программ, делающих возможным построение баз данных и их использование. В обязанности СУБД входит:
- Создание базы данных. Некоторые системы управляют одним большим файлом и создают одну или несколько баз данных внутри него, другие могут задействовать несколько файлов операционной системы или же непосредственно реализовывать низкоуровневый доступ к разделам диска. Пользователи и разработчики не должны заботиться о низкоуровневой структуре таких файлов, т. к. весь необходимый доступ обеспечивает СУБД.
- Предоставление средств для выполнения запросов и обновлений. СУБД должна обеспечивать возможность запроса данных, удовлетворяющих некоторому критерию, например возможность выбора всех заказов, сделанных некоторым клиентом, но еще не доставленных. До того как SQL получил широкое распространение в качестве стандартного языка, способы выражения таких запросов менялись от системы к системе.
- Многозадачность. Если с базой данных работают несколько приложений или к ней одновременно осуществляют доступ несколько пользователей, то СУБД должна гарантировать, что обработка запроса каждого пользователя не влияет на работу остальных. То есть пользователям приходится ждать, только если кто-то другой записывает данные именно тогда, когда им нужно прочитать (или записать) данные в какой-то элемент. Одновременно может происходить несколько считываний данных. На поверку оказывается, что разные базы данных поддерживают разные уровни многозадачности и что эти уровни даже могут быть настраиваемыми.
- Ведение журнала. СУБД должна вести журнал всех изменений данных за некоторый период времени. Он может использоваться для отслеживания ошибок, а также (может быть, это даже важнее) для восстановления данных в случае сбоя системы, например внепланового выключения питания. Обычно производится резервное копирование данных и ведется журнал транзакций, т. к. резервная копия может быть полезна для восстановления базы данных в случае повреждения диска.
- Обеспечение безопасности базы данных. СУБД должна обеспечивать контроль над доступом, чтобы только зарегистрированные пользователи могли манипулировать данными, хранящимися в базе, и самой структурой базы данных (атрибутами, таблицами и индексами). Обычно для каждой базы определяется иерархия пользователей, во главе этой структуры стоит «суперпользователь», который может изменять все что угодно, дальше идут пользователи, которые могут добавлять и удалять данные, а в самом низу находятся те, кто имеет право только на чтение. СУБД должна иметь средства, позволяющие добавлять и удалять пользователей, а также указывать, к каким возможностям базы данных они могут получить доступ.
- Поддержание ссылочной целостности. Многие СУБД имеют свойства, способствующие поддержанию ссылочной целостности, то есть корректности данных. Обычно, если запрос или обновление нарушает правила реляционной модели, СУБД выдает сообщение об ошибке.
|
|