Sql применение. SQL: универсальный язык для работы с базами данных
Язык SQL - Structured Query Language (структурированный язык запросов) разработан фирмой IBM в начале 70-х гг. ХХ в. Это современное средство для работы с базами данных, которое применяется в среде реляционных баз данных (создание, поиск, изменение, обновление, передача данных).
Язык SQL утвержден Американским национальным институтом стандартов (ANSI) и Международной организацией с стандартов (ISO) в качестве официального стандарта для реляционных баз данных и не зависит от специфики компьютера.
SQL является прежде всего информационно-логическим языком, предназначенным для описания, изменения и извлечения данных, хранимых в реляционных базах данных. SQL нельзя назвать языком программирования
Изначально SQL был основным способом работы пользователя с базой данных и позволял выполнять следующий набор операций:
создание в базе данных новой таблицы;
добавление в таблицу новых записей;
изменение записей;
удаление записей;
выборка записей из одной или нескольких таблиц (в соответствии с заданным условием);
изменение структур таблиц.
Со временем SQL усложнился - обогатился новыми конструкциями, обеспечил возможность описания и управления новыми хранимыми объектами (например, индексы, представления, триггеры и хранимые процедуры) - и стал приобретать черты, свойственные языкам программирования.
При всех своих изменениях SQL остаётся единственным механизмом связи между прикладным программным обеспечением и базой данных. В то же время современные СУБД, а также информационные системы, использующие СУБД, предоставляют пользователю развитые средства визуального построения запросов.
Каждое предложение SQL - это либо запрос данных из базы, либо обращение к базе данных, которое приводит к изменению данных в базе. В соответствии с тем, какие изменения происходят в базе данных, различают следующие типы запросов:
запросы на создание или изменение в базе данных новых или существующих объектов (при этом в запросе описывается тип и структура создаваемого или изменяемого объекта);
запросы на получение данных;
запросы на добавление новых данных (записей);
запросы на удаление данных;
обращения к СУБД.
Основным объектом хранения реляционной базы данных является таблица, поэтому все SQL-запросы - это операции над таблицами. В соответствии с этим, запросы делятся на:
запросы, оперирующие самими таблицами (создание и изменение таблиц);
запросы, оперирующие с отдельными записями (или строками таблиц) или наборами записей.
Каждая таблица описывается в виде перечисления своих полей (столбцов таблицы) с указанием
типа хранимых в каждом поле значений;
связей между таблицами (задание первичных и вторичных ключей);
информации, необходимой для построения индексов.
Запросы первого типа в свою очередь делятся на запросы, предназначенные для создания в базе данных новых таблиц, и на запросы, предназначенные для изменения уже существующих таблиц. Запросы второго типа оперируют со строками, и их можно разделить на запросы следующего вида:
вставка новой строки;
изменение значений полей строки или набора строк;
удаление строки или набора строк.
Самый главный вид запроса - это запрос, возвращающий (пользователю) некоторый набор строк, с которым можно осуществить одну из трёх операций:
просмотреть полученный набор;
изменить все записи набора;
удалить все записи набора.
Таким образом использование SQL сводится, по сути, к формированию всевозможных выборок строк и совершению операций над всеми записями, входящими в набор.
Язык программирования
SQL (Structured Query Language — Структурированный язык запросов) — язык управления базами данных для реляционных баз данных. Сам по себе SQL не является Тьюринг-полным языком программирования, но его стандарт позволяет создавать для него процедурные расширения, которые расширяют его функциональность до полноценного языка программирования.
Язык был создан в 1970х годах под названием “SEQUEL” для системы управления базами данных (СУБД) System R. Позднее он был переименован в “SQL” во избежание конфликта торговых марок. В 1979 году SQL был впервые опубликован в виде коммерческого продукта Oracle V2.
Первый официальный стандарт языка был принят ANSI в 1986 году и ISO — в 1987. С тех пор были созданы еще несколько версий стандарта, некоторые из них повторяли предыдущие с незначительными вариациями, другие принимали новые существенные черты.
Несмотря на существование стандартов, большинство распространенных реализаций SQL отличаются так сильно, что код редко может быть перенесен из одной СУБД в другую без внесения существенных изменений. Это объясняется большим объемом и сложностью стандарта, а также нехваткой в нем спецификаций в некоторых важных областях реализации.
SQL создавался как простой стандартизированный способ извлечения и управления данными, содержащимися в реляционной базе данных. Позднее он стал сложнее, чем задумывался, и превратился в инструмент разработчика, а не конечного пользователя. В настоящее время SQL (по большей части в реализации Oracle) остается самым популярным из языков управления базами данных, хотя и существует ряд альтернатив.
SQL состоит из четырех отдельных частей:
- язык определения данных (DDL) используется для определения структур данных, хранящихся в базе данных. Операторы DDL позволяют создавать, изменять и удалять отдельные объекты в БД. Допустимые типы объектов зависят от используемой СУБД и обычно включают базы данных, пользователей, таблицы и ряд более мелких вспомогательных объектов, например, роли и индексы.
- язык манипуляции данными (DML) используется для извлечения и изменения данных в БД. Операторы DML позволяют извлекать, вставлять, изменять и удалять данные в таблицах. Иногда операторы select извлечения данных не рассматриваются как часть DML, поскольку они не изменяют состояние данных. Все операторы DML носят декларативный характер.
- язык определения доступа к данным (DCL) используется для контроля доступа к данным в БД. Операторы DCL применяются к привилегиям и позволяют выдавать и отбирать права на применение определенных операторов DDL и DML к определенным объектам БД.
- язык управления транзакциями (TCL) используется для контроля обработки транзакций в БД. Обычно операторы TCL включают commit для подтверждения изменений, сделанных в ходе транзакции, rollback для их отмены и savepoint для разбиения транзакции на несколько меньших частей.
Следует отметить, что SQL реализует декларативную парадигму программирования: каждый оператор описывает только необходимое действие, а СУБД принимает решение о том, как его выполнить, т.е. планирует элементарные операции, необходимые для выполнения действия и выполняет их. Тем не менее, для эффективного использования возможностей SQL разработчику необходимо понимать то, как СУБД анализирует каждый оператор и создает его план выполнения.
Примеры:
Hello, World!:
Пример для версий Oracle 10g SQL , Oracle 11g SQLСтрока ‘Hello, World!’ выбирается из встроенной таблицы dual , используемой для запросов, не требующих обращения к настоящим таблицам.
select "Hello, World!" from dual ;
Факториал:
Пример для версий Oracle 10g SQL , Oracle 11g SQLSQL не поддерживает циклы, рекурсии или пользовательские функции. Данный пример демонстрирует возможный обходной путь, использующий:
- псевдостолбец level для создания псевдотаблиц t1 и t2 , содержащих числа от 1 до 16,
- агрегатную функцию sum , позволяющую суммировать элементы множества без явного использования цикла,
- и математические функции ln и exp , позволяющие заменить произведение (необходимое для вычисления факториала) на сумму (предоставляемую SQL).
Строка “0! = 1” не войдет в набор строк, полученный в результате, т.к. попытка вычислить ln(0) приводит к исключению.
Числа Фибоначчи:
Пример для версий Oracle 10g SQL , Oracle 11g SQLSQL не поддерживает циклы или рекурсии, кроме того, конкатенация полей из разных строк таблицы или запроса не является стандартной агрегатной функцией. Данный пример использует:
- формулу Бине и математические функции ROUND , POWER и SQRT для вычисления n-ого числа Фибоначчи;
- псевдостолбец level для создания псевдотаблицы t1, содержащей числа от 1 до 16;
- встроенную функцию SYS_CONNECT_BY_PATH для упорядоченной конкатенации полученных чисел.
SELECT REPLACE (MAX (SYS_CONNECT_BY_PATH (fib || ", " , "/" )), "/" , "" ) || "..." fiblist FROM ( SELECT n , fib , ROW_NUMBER () OVER (ORDER BY n ) r FROM (select n , round ((power ((1 + sqrt (5 )) * 0 . 5 , n ) - power ((1 - sqrt (5 )) * 0 . 5 , n )) / sqrt (5 )) fib from (select level n from dual connect by level <= 16 ) t1 ) t2 ) START WITH r = 1 CONNECT BY PRIOR r = r - 1 ;
Hello, World!:
Пример для версий Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012 , MySQL 5 , PostgreSQL 8.4 , PostgreSQL 9.1 , sqlite 3.7.3select "Hello, World!" ;
Факториал:
Пример для версий Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012Используется рекурсивное определение факториала, реализованное через рекурсивный запрос. Каждая строка запроса содержит два числовых поля — n и n!, и каждая следующая строка вычисляется с использованием данных из предыдущей.
Можно вычислить целочисленные факториалы только до 20!. При попытке вычислить 21! возникает ошибка “Arithmetic overflow error”, т.е. происходит переполнение разрядной сетки.
Для вещественных чисел вычисляется факториал 100! (Для этого в примере необходимо заменить bigint на float в 3-ей строке)
Числа Фибоначчи:
Пример для версий Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012Используется итеративное определение чисел Фибоначчи, реализованное через рекурсивный запрос. Каждая строка запроса содержит два соседних числа последовательности, и следующая строка вычисляется как (последнее число, сумма чисел) предыдущей строки. Таким образом все числа, кроме первого и последнего, встречаются дважды, поэтому в результат входят только первые числа каждой строки.
Факториал:
Пример для версий Oracle 10g SQL , Oracle 11g SQLЭтот пример демонстрирует использование оператора model , доступного начиная с версии Oracle 10g и позволяющего обработку строк запроса как элементов массива. Каждая строка содержит два поля — номер строки n и его факториал f.
select n || "! = " || f factorial from dual model return all rows dimension by ( 0 d ) measures ( 0 f , 1 n ) rules iterate (17 ) ( f [ iteration_number ] = decode (iteration_number , 0 , 1 , f [ iteration_number - 1 ] * iteration_number ), n [ iteration_number ] = iteration_number );
Числа Фибоначчи:
Пример для версий Oracle 10g SQL , Oracle 11g SQLЭтот пример демонстрирует использование оператора model , доступного начиная с версии Oracle 10g и позволяющего обработку строк запроса как элементов массива. Каждая строка содержит два поля — само число Фибоначчи и конкатенация всех чисел, меньше или равных ему. Итеративная конкатенация чисел в том же запросе, в котором они генерируются, выполняется проще и быстрее, чем агрегация как отдельное действие.
select max (s ) || ", ..." from (select s from dual model return all rows dimension by ( 0 d ) measures ( cast (" " as varchar2 (200 )) s , 0 f ) rules iterate (16 ) ( f [ iteration_number ] = decode (iteration_number , 0 , 1 , 1 , 1 , f [ iteration_number - 1 ] + f [ iteration_number - 2 ]), s [ iteration_number ] = decode (iteration_number , 0 , to_char (f [ iteration_number ]), s [ iteration_number - 1 ] || ", " || to_char (f [ iteration_number ])) ) );
Факториал:
Пример для версий MySQL 5select concat (cast (t2 . n as char ), "! = " , cast (exp (sum (log (t1 . n ))) as char )) from ( select @ i : = @ i + 1 AS n from TABLE , (select @ i : = 0 ) as sel1 limit 16 ) t1 , ( select @ j : = @ j + 1 AS n from TABLE , (select @ j : = 0 ) as sel1 limit 16 ) t2 where t1 . n <= t2 . n group by t2 . n
Числа Фибоначчи:
Пример для версий MySQL 5Замените TABLE на любую таблицу, к которой есть доступ, например, mysql.help_topic .
select concat (group_concat (f separator ", " ), ", ..." ) from (select @ f : = @ i + @ j as f , @ i : = @ j , @ j : = @ f from TABLE , (select @ i : = 1 , @ j : = 0 ) sel1 limit 16 ) t
Hello, World!:
Пример для версий Oracle 10g SQL , Oracle 11g SQLВ этом примере используется анонимный блок PL/SQL, который выводит сообщение в стандартный поток вывода с помощью пакета dbms_output .
begin dbms_output . put_line ("Hello, World!" ); end ;
Факториал:
Пример для версий Oracle 10g SQL , Oracle 11g SQLЭтот пример демонстрирует итеративное вычисление факториала средствами PL/SQL.
declare n number : = 0 ; f number : = 1 ; begin while (n <= 16 ) loop dbms_output . put_line (n || "! = " || f ); n : = n + 1 ; f : = f * n ; end loop ; end ;
Числа Фибоначчи:
Пример для версий Oracle 10g SQL , Oracle 11g SQLЭтот пример использует итеративное определение чисел Фибоначчи. Уже вычисленные числа хранятся в структуре данных varray — аналоге массива.
declare type vector is varray (16 ) of number ; fib vector : = vector (); i number ; s varchar2 (100 ); begin fib . extend (16 ); fib (1 ) : = 1 ; fib (2 ) : = 1 ; s : = fib (1 ) || ", " || fib (2 ) || ", " ; for i in 3 .. 16 loop fib (i ) : = fib (i - 1 ) + fib (i - 2 ); s : = s || fib (i ) || ", " ; end loop ; dbms_output . put_line (s || "..." ); end ;
Квадратное уравнение:
Пример для версий Oracle 10g SQL , Oracle 11g SQLЭтот пример тестировался в SQL*Plus, TOAD и PL/SQL Developer.
Чистый SQL позволяет вводить переменные в процессе исполнения запроса в виде заменяемых переменных. Для определения такой переменной ее имя (в данном случае A, B и C) следует использовать с амперсандом & перед ним каждый раз, когда нужно сослаться на эту переменную. Когда запрос выполняется, пользователь получает запрос на ввод значений всех заменяемых переменных, использованных в запросе. После ввода значений каждая ссылка на такую переменную заменяется на ее значение, и полученный запрос выполняется.
Существует несколько способов ввести значения для заменяемых переменных. В данном примере первая ссылка на каждую переменную предваряется не одинарным, а двойным амперсандом && . Таким образом значение для каждой переменной вводится только один раз, а все последующие ссылки на нее будут заменены тем же самым значением (при использовании одиночного амперсанда в SQL*Plus значение для каждой ссылки на одну и ту же переменную приходится вводить отдельно). В PL/SQL Developer ссылки на все переменные должны предваряться одиночным знаком & , иначе будет возникать ошибка ORA-01008 “Not all variables bound”.
Первая строка примера задает символ для десятичного разделителя, который используется при преобразовании чисел-корней в строки.
Сам запрос состоит из четырех разных запросов. Каждый запрос возвращает строку, содержащую результат вычислений, в одном из случаев (A=0, D=0, D>0 и D<0) и ничего — в трех остальных случаях. Результаты всех четырех запросов объединяются, чтобы получить окончательный результат.
alter session set NLS_NUMERIC_CHARACTERS = ". " ; select "Not a quadratic equation." ans from dual where && A = 0 union select "x = " || to_char (-&& B / 2 /& A ) from dual where & A != 0 and & B *& B - 4 *& A *&& C = 0 union select "x1 = " || to_char ((-& B + sqrt (& B *& B - 4 *& A *& C )) / 2 /& A ) || ", x2 = " || to_char (-& B - sqrt (& B *& B - 4 *& A *& C )) / 2 /& A from dual where & A != 0 and & B *& B - 4 *& A *& C > 0 union select "x1 = (" || to_char (-& B / 2 /& A ) || "," || to_char (sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || "), " || "x2 = (" || to_char (-& B / 2 /& A ) || "," || to_char (- sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || ")" from dual where & A != 0 and & B *& B - 4 *& A *& C < 0 ;
Основные функциональные возможности языка SQL приведены ниже.
Определение данных. Эта функция SQLпредставляет собой описание структуры поддерживаемых данных и организацию реляционных отношений (таблиц). Для ее реализации предназначены операторы создания базы данных, создания таблиц и доступа к данным.
Создание базы данных . Для создания новой базы данных используется оператор CREATE DATABASE. В структуре оператора указывается имя создаваемой базы данных.
Создание таблиц. Базовая таблица создается с помощью оператора CREATE TABLE. В этом операторе указываются имена полей, типы данных для них, длина (для некоторых типов данных). В SQL используются следующие типы данных:
INTEGER – целое число;
CHAR – символьное значение;
VARCHAR – символьное значение, сохраняются только непустые символы;
DECIMAL – десятичное число;
FLOAT – число с плавающей запятой;
DOUBLE PRECISION – удвоенная точность с плавающей точкой;
DATETIME – дата и время;
BOOL – булевое значение.
В операторе создания таблицы указываются ограничения на значения столбцов и на таблицу. Возможные ограничения показаны в табл. 4.8
Таблица 4.8 Ограничения на определяемые данные
Для реляционной модели данных существенным является указания внешнего ключа(FOREIGNKEY). При объявлении внешних ключей необходимо наложить соответствующие ограничения на столбец, например, NOT NULL.
В SQL-предложении CHECK обозначает семантические ограничения, обеспечивающие целостность данных, чтобы, например, ограничить множество допустимых значения определенного столбца.
Нельзя использовать оператор создания таблицы несколько раз для одной и той же таблицы. Если после ее создания обнаружились неточности в ее определении, то внести изменения можно с помощью оператора ALTER TABLE. Этот оператор предназначен для изменения структуры существующей таблицы: можно удалить или добавить поле к существующей таблице.
Манипулирование данными. SQL позволяет пользователю или прикладной программе изменять содержимое базы данных путем вставки новых данных, удаления или модификации существующих данных.
Вставка новых данных является процедурой добавления строк в базу данных и выполняется с помощью оператора INSERT.
Модификация данных предполагает изменения значений в одном или нескольких столбцах таблицы и выполняется с помощью оператора UPDATE. Пример:
SET сумма=сумма+1000.00
WHERE сумма>0
Удаление строк из таблицы осуществляется с помощью оператора DELETE. Синтаксис оператора имеет вид:
FROM таблица
Предложение WHERE не является обязательным, однако, если его не включить, то будут удалены все записи таблицы. Полезно использовать оператор SELECT c тем же синтаксисом, что и оператор DELETE, чтобы предварительно проверить, какие записи будут удалены.
Обеспечение целостности данных. Язык SQL позволяет определить достаточно сложные ограничения целостности, удовлетворение которым будет проверяться при всех модификациях базы данных. Контроль за результатами транзакций, обработка возникающих ошибок и координирование параллельной работы с базой данных нескольких приложений или пользователей обеспечивается операторами COMMIT(фиксирует удачное окончание текущей транзакции и начало новой) и ROLLBACK (необходимость отката – автоматического восстановления состояния базы данных на начало транзакции)
Выборка данных – одна из важнейших функций базы данных, которой соответствует оператор SELECT. Пример использования оператора был рассмотрен в предыдущем разделе.
В SQL можно создавать вложенные последовательности запросов (подзапросы). Существуют определенные типы запросов, которые лучше реализовывать с помощью подзапросов. К таким запросам относятся так называемые проверки существования. Предположим, что требуется получить данные о студентах, которые не имеют оценку «семь баллов». Если будет возвращено пустое множество, то это означает лишь одно – у каждого студента есть, по крайней мере, одна такая оценка.
Связывание таблиц . Операторы языка SQL позволяют извлекать данные более чем из одной таблицы. Одна из возможностей сделать это заключается в связывании таблиц по одному общему полю.
В операторе SELECT должно присутствовать ограничение на совпадение значений определенного столбца (поля). Тогда из связанных таблиц будут извлекаться только те строки, в которых значения заданного столбца совпадают. Название столбца указывается только вместе с названием таблицы; в противном случае оператор будет неоднозначным.
Можно использовать другие типы связывания таблиц: оператор INTER JOIN (внутреннее соединение) обеспечивает присутствие в результирующем наборе записей, совпадающие значения в связанных полях. Внешние соединения (OUTER JOIN) позволяют включить в результат запроса все строки из одной таблицы и соответствующие им строки из другой
Управление доступом. SQL обеспечивает синхронизацию обработки базы данных различными прикладными программами, защиту данных от несанкционированного доступа.
Доступ к данным в многопользовательской среде регулируется с помощью операторов GRANT и REVOKE. В каждом операторе необходимо указать пользователя, объект (таблицу, представление), по отношению к которому задаются полномочия, и сами полномочия. Например, оператор GRANT задает пользователю Х возможность производить выборку данных из таблицы ТОВАР:
GRANT SELECT ON ТОВАР TO X
Оператор REVOKE аннулирует все предоставленные ранее полномочия.
Встраивание SQL в прикладные программы . Реальные приложения обычно написаны на других языках, генерирующих код на языке SQL и передающих их в СУБД в виде текста в формате ASCII.
Стандартом фирмы IBM для SQL-продуктов регламентировано использование встроенного языка SQL. При написании прикладной программы ее текст представляет собой смесь команд основного языка программирования (например, C, Pascal, Cobol, Fortran, Assembler) и команд SQL со специальным префиксом, например. ExecSQL. Структура SQL-предложений расширена для размещения переменных основного языка в SQL-конструкции.
SQL-процессор видоизменяет вид программы в соответствии с требованиями компилятора основного языка программирования. Функция компилятора состоит в трансляции (перевод) программы с исходного языка программирования на язык, близкий к машинному. После компиляции прикладная программа (приложение) представляет собой самостоятельный модуль.
Диалекты языка SQL
В современных реляционных СУБД для описания и манипулирования данными используются диалекты языка SQL. Подмножество языка SQL, позволяющее создавать и описывать БД, называется DDL (Data Definition Language).
Первоначально язык SQL назывался SEQUEL(Structured English Query Language), потом SEQUEL/2, а затем просто – SQL. Сегодня язык SQL –фактический стандарт для реляционных СУБД.
Первый стандарт языка появился в 1989 г. – SQL-89 и поддерживался практически всеми коммерческими реляционными СУБД. Он имел общий характер и допускал широкое толкование. Достоинствами SQL-89 можно считать стандартизацию синтаксиса и семантики операторов выборки и манипулирования данными, а также фиксацию средств ограничения целостности базы данных. Однако в нем отсутствовал такой важный раздел как манипулирование схемой базы данных. Неполнота стандарта SQL-89 привела к появлению в 1992г. следующей версии языка SQL.
SQL2 (или SQL-92) охватывает практически все необходимые проблемы: манипулирование схемой базы данных, управление транзакциями и сессиями, поддерживает архитектуры клиент-сервер или средства разработки приложений.
Дальнейшим шагом развития языка является вариант SQL 3. Эта версия языка дополняется механизмом триггеров, определением произвольного типа данных, объектным расширением.
В настоящее время существует три уровня языка: начальный, промежуточный и полный. Многие производители своих СУБД применяют собственные реализации SQL, основанные как минимум на начальном уровне соответствующего стандарта ANSI, и содержащие некоторые расширения, специфические для той или иной СУБД. В табл. 4.9 приведены примеры диалектов SQL.
Таблица 4.9 Диалекты языка SQL
| СУБД | Язык запросов |
| СУБД System R | SQL |
| DB2 | SQL |
| Access | SQL |
| SYBASE SQL Anywhere | Watcom-SQL |
| SYBASE SQL Server | Transact_SQL |
| My SQL | SQL |
| Oracle | PL/SQL |
В объектно-ориентированных БД используется язык объектных запросов OQL (Object Query Language). За основу языка OQL была взята команда SELECT языка SQL2 и добавлены возможность направлять запрос к объекту или коллекции объектов, а также возможность вызывать методы в рамках одного запроса.
Совместимость многих используемых диалектов SQL обусловливает совместимость СУБД. Так, СУБД SYBASE SQL Anywhere максимально, насколько это возможно для СУБД такого класса, совместима с СУБД SYBASE SQL Server. Одной из сторон такой совместимости является поддержка в SYBASE SQL Anywhere такого диалекта языка SQL как Transact-SQL . Этот диалект используется в SYBASE SQL Server и может применяться в SYBASE SQL Anywhere наряду с собственным диалектом языка SQL - Watcom-SQL .
Контрольные вопросы
1. Как можно классифицировать СУБД?
2. Какие модели баз данных существуют?
3. Что является основными элементами инфологических моделей?
4. Какие типы связей между сущностями существуют?
5. Что такое ER-диаграммы и для чего они используются?
6. Что позволяет делать процедура нормализации таблиц?
7. Назовите языковые и программные средства СУБД?
8. К каому типу относится СУБД MS Access?
9. Назовите основные объекты СУБД MS Access?
10. Для чего используются основные операторы языка SQL?
Язык структурированных запросов Structure Query Language (SQL) был создан в результате разработки реляционной модели данных и в настоящее время является фактическим стандартом языка реляционных СУБД. Язык SQL сегодня поддерживается огромным количеством СУБД различных типов.
Название языка SQL произносится обычно по буквам «эс-кью-эль». Иногда используют мнемоническое имя «See-Quel».
Язык SQL предоставляет пользователю (при минимальных усилиях с его стороны) следующие возможности:
Создавать базы данных и таблицы с полным описанием их структуры
Выполнять основные операции манипулирования данными: вставка, изменение, удаление данных
Выполнять как простые, так и сложные запросы.
Язык SQL является реляционно полным.
Структура и синтаксис его команд достаточно просты, а сам язык является универсальным, т. е. синтаксис и структура его команд не меняется при переходе от одной СУБД к другой.
Язык SQL имеет два основных компонента:
Язык DDL (Data Definition Language) для определения структур базы данных и управления доступом к данным
Язык DML (Data Manipulation Language), предназначенный для выборки и обновления данных.
Язык SQL является непроцедурным, т. е. при его использовании необходимо указывать то, какая информация должна быть получена, а не то, как ее можно получить. Команды языка SQL представляют собой обычные слова английского языка (SELECT, INSERT и др.). Рассмотрим вначале операторы SQL DML:
SELECT - выборка данных из базы
INSERT - вставка данных в таблицу
UPDATE - обновление данных в таблице
DELETE - удаление данных из таблицы
Оператор SELECT
Оператор выборки SELECT выполняет действия, эквивалентные следующим операциям реляционной алгебры: выборка, проекция и соединение.
Простейший SQL-запрос с его использованием выглядит следующим образом:
SELECT col_name FROM tbl
После ключевого слова select следует список столбцов, разделенных запятыми, данные которых будут возвращены в результате запроса. Ключевое слово from, указывает, из какой таблицы (или представления) извлекаются данные.
Результатом запроса select всегда является таблица, которая называется результирующей таблицей. Более того, результаты запроса, выполненного при помощи оператора select, могут быть использованы для создания новой таблицы. Если результаты двух запросов к разным таблицам имеют одинаковый формат, их можно объединить в одну таблицу. Также таблица, полученная в результате запроса, может стать предметом дальнейших запросов.
Для выборки всех столбцов и всех строк таблицы достаточно сделать запрос SELECT * FROM tbl;
Рассмотрим таблицу Product, содержащую сведения о цене на различные виды продукции:
Результатом запроса
SELECT * FROM Product;
будет вся таблица Product.
Выбрать конкретные столбцы таблицы можно с помощью запроса
SELECT col1, col2, … , coln FROM tbl;
Так, результатом запроса
SELECT Type, Price FROM Product;
будет таблица
К списку столбцов в операторе select прибегают и в том случае, если необходимо изменить порядок следования столбов в результирующей таблице:
Для того чтобы выбрать лишь те строки таблицы, которые удовлетворяют некоторым ограничениям, используется специальное ключевое слово where, после которого следует логическое условие. Если запись удовлетворяет такому условию, она попадает в результат. В противном случае такая запись отбрасывается.
Например, выбор тех товаров из таблицы Product, цена которых удовлетворяет условию Price <3200, можно осуществить, используя запрос
SELECT * FROM Product where Price <3200;
Его результат:
Условие может быть составным и объединяться при помощи логических операторов NOT , AND, OR, XOR, например: where id_ Price>500 AND Price<3500. Допускается также использование выражений в условии: where Price>(1+1) и строковых констант: where name= "автовесы".
Применение конструкции BETWEEN var1 AND var2 позволяет проверить, попадают ли значения какого-либо выражения в интервал от var1 до var2 (включая эти значения):
SELECT * FROM Product where Price BETWEEN 3000 AND 3500;
По аналогии с оператором NOT BETWEEN существует оператор NOT IN.
Имена столбцов, указанные в предложении SELECT, можно переименовать. Для этого используется ключевое слово AS, которое, впрочем, можно опустить, т. к. неявно подразумевается. Например, запрос
SELECT Type AS model, Type_id AS num FROM Product where Type_id =3
вернет (имена псевдонимов следует записывать без кавычек):
Оператор LIKE предназначен для сравнения строки с образцом:
SELECT * FROM tbl where col_name LIKE "abc"
Этот запрос возвращает лишь те записи, которые содержат в столбце col_name строковое значение abc.
В образце разрешается использовать два трафаретных символа: "_" и "%". Первый из них заменяет в шаблоне один произвольный символ, а второй - последовательность произвольных символов. Так, "abc%" соответствует любой строке, начинающейся на abc, "abc_" - строке из 4-х символов, начинающейся на abc, "%z" - произвольной строке, заканчивающейся на z, и, наконец, "%z%" - последовательности символов, содержащих z.
Найти все записи таблицы Product, в которых значение Type начинается с буквы "a" можно так:
SELECT * FROM Product where Type LIKE "а%";
|
автовесы |
Если искомая строка содержит трафаретный символ, то следует задать управляющий символ в предложении ESCAPE. Этот управляющий символ должен использоваться в образце перед трафаретным символом, сообщая о том, что последний следует трактовать как обычный символ. Например, если в некотором поле следует отыскать все значения, содержащие символ "_", то шаблон "%_%" приведет к тому, что будут возвращены все записи из таблицы. В данном случае шаблон следует записать следующим образом:
"%|_%" ESCAPE "|"
Для проверки значения на соответствие строке "20%" можно воспользоваться таким оператором:
LIKE "20#%" ESCAPE "#"
Оператор IS NULL позволяет проверить отсутствие (наличие) NULL-значения в полях таблицы. Использование в этих случаях обычных операторов сравнения может привести к неверным результатам, так как сравнение со значением NULL дает результат UNKNOWN (неизвестно). Таким образом, условие отбора должно выглядеть так:
where col_name IS NULL, вместо where col_name=NULL.
Результат выборки по умолчанию возвращает записи, расположенные в том же порядке, в котором они хранятся в базе данных. Если требуется отсортировать записи по одному из столбцов, необходимо применить конструкцию ORDER BY, после которой указывается имя этого столбца:
SELECT * FROM tbl ORDER BY col_name;
В результате этого запроса записи будут возвращены в порядке возрастания значения атрибута col_name.
Сортировку записей можно производить и по нескольким столбцам. Для этого их названия надо указать после ORDER BY через запятую:
SELECT * FROM tbl ORDER BY col_name1, col_name2.
Записи будут отсортированы по полю col_name1; если встречается несколько записей с совпадающим значением в колонке col_name1, то они будут отсортированы по полю col_name2.
Если требуется отсортировать записи в обратном порядке (например, по убыванию даты), требуется указать ORDER BY col_name DESC.
Для прямой сортировки существует ключевое слово ASC, которое принято в качестве значения по умолчанию.
Если результат выборки содержит сотни и тысячи записей, их вывод и обработка занимают значительное время.
Поэтому информацию часто разбивают на страницы и предоставляют ее пользователю порциями. Постраничная навигация используется при помощи ключевого слова limit, за которым следует число выводимых записей. В следующем запросе извлекаются первые 10 записей, при этом одновременно осуществляется обратная сортировка по полю col_name1:
SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10
Для того чтобы извлечь следующие 10 записей, используется ключевое слово limit с двумя значениями: первое указывает позицию, начиная с которой необходимо вывести результат, а вторая -- количество извлекаемых записей:
SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10,10
Для извлечения следующих 10 записей необходимо использовать конструкцию LIMIT 20, 10.
SQL – это структурированный язык запросов. SQL не существует без баз данных — на нем нельзя писать программы, и в этом смысле он не является языком программирования, таким как РНР, но когда приходится иметь дело с конкретной СУБД, то без знания SQL уже не обойтись. На нем можно писать простенькие запросы, а можно выполнять большие транзакции, состоящие из нескольких сложных запросов. SQL-запрос — это своего рода команда к базе данных. Такая команда может затребовать вернуть информацию, попадающую под конкретные критерии, или дать указание удалить какие-либо записи и т.п. SQL-команда — это простая строка, например:
SELECT * FROM Staff WHERE department
SQL-запросы обычно близки к простому выражению на английском языке. Приведенная выше команда может быть переведена на русский язык следующим образом
ВЫБРАТЬ ВСЕ из Staff ГДЕ clwjiertme"
Вполне понятная команда, жаль только записывается она исключительно на английском. В результате выполнения такого запроса СУБД вернет все записи из таблицы Staff, в которых поле depart* Mit_id равно трем. В нашем примере данный запрос фактически выбирает только программистов из всей базы сотрудников.
Если вы раньше не работали с СУБД, то у вас может возникнуть резонный вопрос: где и как можно выполнить данный запрос? Существует три способа выполнения SQL-запросов.
1. Интерактивная среда взаимодействия с СУБД. Для большинства серверов СУБД существуют программы-клиенты (встроенные или поставляемые третьими лицами), в рабочей среде которых можно писать SQL-запросы, выполнять их и получать результат. Обычно такие средства используются администраторами баз данных и к РНР-программированию прямого отношения не имеют. Примером клиентской программы для работы с MySQL может послужить программа MySQL Administrator (http: /www.mysgl.coin/ product-s/administratoT/) или очень популярная РНР-система phpMyAdmin (http: / /www. phpmyadi’ln. r»et/itumm jiage/index. php). Для начала работы будет достаточно уже установленного дистрибутива, который имеет консольный интерфейс. В Linux необходимо из командной строки набрать команду mysql, чтобы открылось окно с приглашением для ввода SQL-запросов, а в Windows для запуска того же интерфейса нужно запустить файл mysql. ехе из каталога bin.
2. Статические SQL-запросы. Обычно такие запросы прописываются внутри хранимых процедур в самих базах данных или жестко прошиваются в самих приложениях. Статический SQL-запрос определен заранее и меняется только в том случае, если вручную переписывается код программы или хранимой процедуры. Из РНР такой SQL-запрос выполняется с помощью специальных функций, которые будут рассмотрены далее.
3. Динамические SQL-запросы. К данному виду относятся такие запросы, i рые при написании приложения не могут быть определены полностью. i» пример, при написании программы для получения списка сотруднике» mt разным подразделениям предприятия программисту не известно, ci"> о в компании будет подразделений и какие сотрудники будут в них входи i„. Конечно, эти данные можно прописать в программе жестко, но при перво же изменении в структуре компании программу можно будет выбросить ил, потребуется переписать. Динамические запросы позволяют создавать поо-граммы, гибкие к изменениям данных. В РНР такие запросы выполняются практически теми же функциями, что и статические, только в них г’»’щег’^в’> ет возможность передавать некоторые параметры.
В качестве резюме к описанным выше трем пунктам можно сказать, что SQL-запросы выполняются из специальных администраторских программ либо различными способами из РНР-скриптов.
Так как СУБД решает множество задач, SQL тоже вынужден быть многоф циональным языком. Существует несколько типов операций, которые можно < \ ществлять с помощью SQL.
1. Определение структуры базы данных. К этому типу относятся запросы, с помощью которых создаются и модифицируются таблицы и индексы. Обычно это команды CRE; "Е ТА’ LE, ALI’R ТА’ LE, ‘ ” ’.ТЕ INDEX И др.
2. Манипуляция данными. К этому типу относятся запросы на вставку (дв1*и> ление), удаление или изменение данных в таблицах. Это три основные команды: INSERT. DELETE И UPDATE.
3. Выборка данных Сюда входит только одна команда SELECT. Она не вносит изменения в сами данные, но позволяет получать их из базы. Несмотря на то что для выборки данных используется только одна команда, она имеет очень большие возможности и используется в приложениях очень часто.
4. Управление сервером СУБД. К этому типу в основном относятся запросы для управления пользователями и их правами доступа (например, команда GRANT).
Хорошее знание SQL очень облегчает труд программиста при работе с БД. Приложения могут быть небольшими, но иметь большой функционал только за счет того, что много задач на себя возьмет SQL.
Как и в любой другой сфере IT, в SQL существуют стандарты — это ANSI SQL. Аббревиатура ANSI расшифровывается как Amrican National Standards Institute (Американский национальный институт стандартов). Однако не в последнюю очередь из-за различия в функционале самих СУБД реализации SQL для различных
СУБД все же отличаются друг от друга. На данный момент практически каждая СУБД имеет свой диалект, который обычно не сильно отличается от общего стандарта, но имеет свои особенности. Например, с Oracle и PostgreSQL совместим язык PL/SQL, а для работы с MS SQL Server используется T-SQL.
Для последующей работы с базами данных мы рекомендуем сразу изучать тот стандарт, с которым вы планируете работать в дальнейшем. Для большинства Web-разработчиков на данный момент с головой хватает функциональных возможностей СУБД MySQL (к тому же она может использоваться бесплатно), поэтому в данной книге все примеры с MySQL будут приводиться, соответственно, на диалекте этой СУБД. Документация по языку запросов для MySQL можно найти на сайте www.mysql.com.
