29. Манипуляция данными. Язык запросов SQL.
Создав базу данных, в дальнейшем требуется обслуживание этой базы, т.е. выполнение операций над данными. Манипуляция данными подразумевает выборку, обновление, вставку, удаление записей, а также выполнение арифметических и логических операций над данными. Для манипулирования данными используются специальные языки. Одним из таких языков является SQL.
SQL — это язык программирования, предназначенный для работы с наборами фактов и отношениями между ними. В программах управления реляционными базами данных, таких как Microsoft Office Access, язык SQL используется для работы с данными. В отличие от многих языков программирования SQL удобочитаем и понятен. Язык SQL является международным стандартом, признанным такими комитетами по стандартизации, как ISO(на английском языке) и ANSI(на английском языке).
Как и в любом языке программирования, SQL состоит из команд, ключевых слов и синтаксических правил.
Каждая инструкция состоит из команды, которая описывает действие. После команды идет одно или несколько предложений, которые уточняют параметры выполняемой инструкции.
Имена полей могут быть короткими (если запрос к одной таблице) или полными (запрос к нескольким таблицам). В полном имени поля имя таблицы отделяется от имени поля точкой. Если имя поля состоит из слов разделенных пробелами, то оно заключается в квадратные скобки.
Все ключевые слова, имена функций и, как правило, имена таблиц и столбцов представляются 7-мибитными символами кодировки ASCII (иначе говоря - латинскими буквами).
В языке SQL не делается различия между прописными (большими) и строчными (маленькими) буквами, т.е., например, строки "SELECT", "Select", "select" представляют собой одно и то же ключевое слово.
Для конструирования имен таблиц и их столбцов допустимо использовать буквы, цифры и знак "_" (подчеркивание), но первым символом имени обязательно должна быть буква.
Запрещено использование ключевых слов и имен функций в качестве идентификаторов таблиц и имен столбцов.
Оператор начинается с ключевого слова-глагола (например, "CREATE" - создать, "UPDATE" - обновить, "SELECT" - выбрать и т.п.) и заканчивается знаком ";" (точка с запятой). Оператор записывается в свободном формате и может занимать несколько строк. Допустимыми разделителями лексических единиц в операторе являются: один или несколько пробелов, один или несколько символов табуляции, один или несколько символов "новая строка".
К сожалению, разработчики реальных СУБД неаккуратно обращаются с требованиями стандартов языка SQL в части комментариев. Поэтому комментарии при использовании в различных СУБД в текстах "программ" на языке SQL могут помечаться следующими способами:
- от двойного минуса ("--") до конца строки;
- от символа "#" до конца строки;
- между последовательностями "/*" и "*/" (стиль комментариев языка СИ).
Типы данных, используемые в языке SQL для хранения информации в столбцах таблиц БД, весьма разнообразны. Производители конкретных реляционных СУБД считают своим долгом "улучшить" множество типов данных, регламентируемых стандартом, реализуя свои собственные версии и расширения.
Автором учебного пособия в качестве базовых предлагается считать следующие типы данных:
INT[(len)] - целое число длиной 4 байта, представляемое при выводе максимально len цифрами;
SMALLINT[(len)] - целое число длиной 2 байта, представляемое при выводе максимально len цифрами;
FLOAT[(len,dec)] - действительное число, представляемое при выводе максимально len символами с dec цифрами после десятичной точки;
CHAR(size) - строка символов фиксированной длины размером size символов;
VARCHAR(size) - строка символов переменной длины максимальным размером до size символов;
BLOB (Binary Large OBject) - массив произвольных (двоичных) байтов (максимальный размер зависит от реализации, обычно это 65535 байт); этот тип данных может использоваться, например, для хранения изображений;
DATE - астрономическая дата;
TIME - астрономическое время.
Символьные константы (типа CHAR и VARCHAR) а также типы DATE, TIME записываются как последовательности символов, заключенные в одиночные или двойные кавычки.
Десятичные константы (типа FLOAT) могут записываться в "научной" нотации как последовательности следующих компонент:
Например, десятичное число -0,123 может быть записано как -12.3е-2.
Отличие типов данных CHAR и VARCHAR заключается в том, что для хранения в таблице строк символов типа CHAR используется точно size байт (хотя содержание хранимых строк может быть значительно короче), в то время как для строк типа VARCHAR незанятые символами строк ("пустые") байты в таблице не хранятся.
Рассмотрим наиболее используемую в SQL команду выборки SELECT.
Для извлечения данных, содержащихся в таблицах SQL БД, используется оператор SELECT, имеющий в общем случае сложный и многовариантный синтаксис.
Упрощенно оператор SELECT выглядит следующим образом:
SELECT [ALL | DISTINCT] в_выражение, ...
FROM имя_табл [син_табл], ...
[WHERE сложн_условие]
[GROUP BY полн_имя_столбца|ном_столбца, ...]
[ORDER BY полн_имя_столбца|ном_столбца [ASC|DESC], ...]
[HAVING сложн_условие];
Результатом работы оператора является выводимая на стандартный вывод (экран дисплея) вновь построенная таблица.
При описании синтаксиса оператора SELECT использованы следующие обозначения:
син_табл - необязательный синоним имени таблицы, используемый для сокращения длины записи выражений и условий в операторе SELECT.
полн_имя_столбца - полное имя столбца в виде
[имя_табл|син_табл.]имя_столбца
Конкретизирующий таблицу префикс в имени столбца необходим только для различения столбцов, имеющих одинаковое имя в разных таблицах из списка FROM.
ном_столбца - номер столбца результирующей таблицы.
Описание столбцов результирующей таблицы
1. Специальным (и часто используемым) видом в_выражение является символ "*", имеющий смысл "все столбцы таблиц из списка FROM".
Пример
Вывод всего содержимого таблицы materials.
SELECT * FROM materials;
2. Простым (и также часто используемым) случаем в_выражение является полное имя столбца одной из таблиц списка FROM.
Пример
SELECT node, type FROM loadings;
Для исключения дублирующих строк можно, добавив в оператор ключевое слово DISTINCT, запрещающее включение в итоговую таблицу одинаковых строк.
SELECT DISTINCT node, type FROM loadings;
3. В общем случае в_выражение может представлять собой сложное скобочное выражение над содержимым столбцов таблицы, использующее арифметические, строковые, логические операции и функции. Пример:
SELECT name, density*1000, elastics*1e+9 FROM materials;
Операторы и функции, возвращающие логическое значение (1 - "истина", 0 - "ложь")
Синтаксис
x = y
x <> y
x > y
x < y
x <= y
x >= y 1 ("истина") или 0 ("ложь") в зависимости от результата операции сравнения (соответственно, "равно", "не равно", "больше", "меньше", "не больше", "не меньше")
NOT L 1, если L=0 ; 0, если l=1
L1 AND L2 результат логической операции "И" над L1 и L2
L1 OR L2 результат логической операции "ИЛИ" над L1 и L2
BETWEEN(x, y, z) результат выполнения логического выражения (x>=y AND x<=z)
ISNULL (v) 1, если v имеет значение "пусто" (NULL) 0, в противном случае
IFNULL (v1, v2) v1, если v1 не "пусто" v2, в противном случае
s LIKE образец 1, при удачном сопоставлении строки s с образец
0, в противном случае
s NOT LIKE образец 0, при удачном сопоставлении строки s с образец
1, в противном случае
Примечание. x, y, z - числа или выражения, имеющие числовой результат. l, l1, l2 - логические константы (1 или 0) или логические выражения. s - строка или выражение, имеющее результат в виде строки. v, v1, v2 - переменные или выражения.
образец - константа в виде строки символов, возможно, содержащая метасимволы "%" и "_". В образец метасимвол "_" сопоставим с любым одиночным символом строки s, метасимвол "%" - с любой цепочкой символов любой ( в том числе нулевой) длины.
4. В общем случае в_выражение допускает использование агрегативных (называемых также групповыми) функций, принимающих в качестве своего единственного аргумента значения всех ячеек указанного столбца результирующей таблицы.
SUM(x) сумма значений столбца x результирующей таблицы
MAX(x) наибольшее значение из всех значений ячеек столбца x
MIN(x) наименьшее значение из всех значений ячеек столбца x
AVG(x) среднее значение для всех значений ячеек столбца x
COUNT(x) общее количество ячеек в столбце x
Пример:
Следующий оператор SELECT позволяет определить общее количество записей в таблице:
SELECT COUNT(*) FROM elements;
Описание критерия выборки содержимого строк результирующей матрицы
В качестве критерия выбора информации из таблиц списка FROM оператора SELECT выступает сложн_условие, записываемое после ключевого слова WHERE и имеющее следующий вид:
прост_условие
или
прост_условие AND сложн_условие
или
прост_условие OR сложн_условие
Типичными вариантами прост_условие являются следующие.
Сравнение
полн_имя_столбца @ полн_имя_столбца_или_константа
где @ - один из операторов сравнения: > ("больше"), < ("меньше"), >= (" не меньше"), <= ("не больше"), = ("равно"), <> ("не равно"), а полное_имя_столбца - имя столбца, конкретизированное при необходимости именем или синонимом имени таблицы, как это было описано выше.
Упорядочивание и группирование строк результирующей таблицы
Для обеспечения структурированности в расположении строк результирующей таблицы в операторе SELECT используются конструкции GROUP BY и ORDER BY.
Упорядочение строк достигается перечислением полных имен столбцов, по которым в возрастающем (ASC) или убывающем (DESC) порядке сортируются строки результирующей таблицы. При этом строки упорядочиваются в первую очередь по столбцу, указанному первым в списке ORDER BY. Затем, если среди значений ячеек первого столбца есть повторяющиеся, производится упорядочение по второму столбцу и так далее.
Пример
Пусть необходимо вывести информацию о конечных элементах, упорядочив ее;
в первую очередь по идентификаторам узлов, являющихся первой вершиной конечного элемента;
во вторую очередь по идентификаторам узлов, являющихся второй вершиной конечного элемента;
Для решения этой задачи можно использовать следующий оператор
SELECT * FROM elements ORDER BY n1, n2;
Конструкция HAVING сложн_условие, как необязательная составная часть предложения GROUP BY, позволяет определять дополнительный (к WHERE сложн_условие) критерий выборки строк в группы. Этот дополнительный критерий применяется в режиме постпроцессорной обработки к таблице, полученной в результате использования критерия из конструкции WHERE.
TOP n [PERCENT]. При использовании этого аргумента оператор SELECT возвращает только первые n строк из набора результатов. Если задано ключевое слово PERCENT, то будут возвращаться первые строки, составляющие n процентов от общего количества строк. При использовании ключевого слова PERCENT, число n должно быть в пределах от 0 до 100. Если в запросе имеется предложение ORDER BY, то строки вывода сначала сортируются, а затем из отсортированного набора результатов выдаются первые n строк или n процентов от общего количества строк. (О предложении ORDER BY см. раздел "Предложение ORDER BY" далее.)
Ниже даны три примера запуска оператора SELECT с разными аргументами. В первом из них при запуске используется аргумент DISTINCT, во втором – аргумент TOP 50 PERCENT, а в третьем – аргумент TOP 5:
SELECT DISTINCT au_fname, au_lname
FROM authors
GO
SELECT TOP 50 PERCENT au_fname, au_lname
FROM authors
GO
SELECT TOP 5 au_fname, au_lname
FROM authors
GO
Первый запрос вернет 23 строки, каждая из которых будет уникальной. Второй запрос вернет 12 строк (приблизительно 50%, с округлением до большего числа), а третий запрос вернет 5 строк.
Кроме рассмотренной команды SELECT, в SQL имеются инструкции проекции, соединения, объединения, создания и изменения структуры таблиц БД, которые в рамках данного курса не рассматриваются.