Sql функции для ввода данных. Функции языка SQL. Операторы SQL для манипулирования данными

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

Шаг 15. Функции SUM, AVG, MIN, MAX, COUNT…

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

SELECT AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНИЙ СТАЖ СОТРУДНИКОВ] FROM D_STAFF

SQL функция AVG.

Аналогично можно вычислить минимальное и максимальное значения (MIN, MAX), общую сумму (SUM) и т.д. Советую попробовать это выполнить с использованием обучающей программы. Стоит попробовать определить дополнительные критерии отбора записей, участвующих в определении итогового значения функции с использованием оператора WHERE.

Перечисленные выше функции для определения своего значения используют результат запроса целиком. Такие функции называются агрегатными . Также, есть ряд функций, аргументом которых являются не все значения определенной в запросе колонки, а каждое отдельное значение каждой отдельной строки результата. Примером такой функции является SQL функция вычисления длины текстового поля LEN:

SELECT S_NAME, LEN(D_STAFF.S_NAME) AS [ДЛИНА] FROM D_STAFF

Можно использовать суперпозицию SQL функций , как показано ниже, и вычислить максимальное значение длины поля S_NAME.

SELECT MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА] FROM D_STAFF

SQL функция MAX.

Ну и в заключении все вместе.

SELECT SUM(D_STAFF.S_EXPERIENCE) AS [СУММА], AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНЕЕ], MIN(D_STAFF.S_EXPERIENCE) AS [МИНИМУМ], MAX(D_STAFF.S_EXPERIENCE) AS [МАКСИМУМ], COUNT(*) AS [КОЛИЧЕСТВО ЗАПИСЕЙ], MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА] FROM D_STAFF

Пример использования агрегатных SQL функций.

Обратите внимание на аргумент функции COUNT. Я указал в качестве аргумента (*), поскольку хочу получиться именно общее число записей. Если указать, например COUNT(S_NAME), то результатом будет число непустых значений S_NAME (S_NAME IS NOT NULL). Можно было бы написать COUNT(DISTINCT S_NAME) и получить количество уникальных значений S_NAME, но MS Access такой вариант, к сожалению, не поддерживает. В нашем примере COUNT(S_NAME) и COUNT(*) дают абсолютно одинаковый результат.

Шаг 16. Преобразование текста

Часто, текстовые значения заполняются пользователями программного обеспечения по-разному: кто пишет Ф.И.О. с заглавной буквы, кто нет; кто-то пишет все заглавными буквами. Многие отчетные формы требуют унифицированного подхода, да и не только отчетные формы. Для решения этой задачи в SQL есть две функции UCASE и LCASE. Пример запроса и результат его обработки приведены ниже:

SELECT UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) AS FROM D_STAFF

SQL функции UCASE и LCASE.

Шаг 17. SQL и работа со строками

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

SELECT UCASE(MID(P_NAME,3,5)) FROM D_PROFILE

Суперпозиция SQL функций UCASE и MID.

Мы “вырезали” из значений наименований профилей по 5 символов, начиная с 3-го, и получили кучу повторяющегося “мусора”. Для того чтобы оставить только уникальные значения будем использовать ключевое слово DISTINCT.

SELECT DISTINCT UCASE(MID(P_NAME,3,5)) AS FROM D_PROFILE

Выбор уникальных значений агрегатной функции.

Иногда приходится в качестве аргументов функции MID использовать выражения с функцией LEN. В следующем примере мы уже выводим последние 5 символов в наименованиях профилей.

SELECT UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FROM D_PROFILE

Использование SQL функции LEN.

Шаг 18. Использование SQL функций в критерии отбора записей. Оператор HAVING

Разобравшись с функциями, практически сразу возникает вопрос, как их можно использовать в критериях отбора записей? Некоторые функции, а именно те, которые не являются агрегатными - использовать достаточно легко. Вот, например, список сотрудников, чье Ф.И.О. более 25 символов.

SELECT S_NAME FROM D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

Использование неагрегатной функции LEN в условиях SQL запроса.

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

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

Для таких случаев в SQL ввели ключевое слово HAVING, которое поможет нам решить проблему с должностями и сотрудниками.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION HAVING COUNT(S_POSITION)>1

Использование агрегатных функций в условиях SQL запроса.

Шаг 19. Группировка данных в результатах SQL запроса оператором GROUP BY

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

SELECT S_POSITION FROM D_STAFF

А это два варианта, позволяющие вывести только уникальные значения S_POSITION.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION

SELECT DISTINCT S_POSITION FROM D_STAFF

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

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ] FROM D_STAFF_PROFILE GROUP BY PROFILE_ID

Использование агрегатной SQL функции вместе с группировкой.

Оператор GROUP BY позволяет также группировать результат запроса более чем по одному полю, перечисляя их через запятую. Я надеюсь, что после всего вышесказанного дополнительные комментарии к результату последнего запроса не нужны.

SELECT S.S_POSITION AS , S.S_NAME AS [СОТРУДНИК], COUNT(SP.STAFF_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ В ТАБЛИЦЕ D_STAFF_PROFILE] FROM D_STAFF S, D_STAFF_PROFILE SP WHERE S.XD_IID=SP.STAFF_ID GROUP BY S.S_POSITION, S.S_NAME

Группировка строк результата SQL запроса по нескольким полям.

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

Введение

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

Определяют несколько категорий операторов SQL:

• определение объектов базы данных;
• манипулирование значениями;
• защита и управление;
• параметры сеанса;
• информация о базе;
• статический SQL;
• динамический SQL.

Операторы SQL для манипулирования данными

INSERT. Вставляет строки в существующую таблицу. Может использоваться как для одного значения, так и нескольких, определённых по некоему условию. Например:

имя таблицы (имя столбца 1, имя столбца 2)

VALUES (значение 1, значение 2).

Для использования оператора INSERT при нескольких значениях, применяется такой синтаксис:

имя таблицы 1 (имя столбца 1, имя столбца 2)

SELECT имя столбца 1, имя столбца 2

FROM имя таблицы 2

WHERE имя таблицы 2.имя столбца 1>2

Этот запрос выберет все данные из таблицы 2, которые больше 2 по столбцу 1 и вставит их в первую.

UPDATE. Как видно из названия, этот оператор SQL запроса обновляет данные в существующей таблице по определённому признаку.

UPDATE имя таблицы 1

SET имя столбца 2 = «Василий»

WHERE имя таблицы 1.имя столбца 1 = 1

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

Данные из таблицы. Можно указать какое-либо условие или же убрать все строки.

DELETE FROM имя таблицы

WHERE имя таблицы.имя столбца 1 = 1

Приведённый запрос удалит из базы все данные со значением один в первом столбце. А вот так можно очистить всю таблицу:

Оператор SELECT

Главное назначение SELECT — выборка данных по определенным условиям. Результатом его работы всегда является новая таблица с отобранными данными. Оператор MS может быть использован в массе различных запросов. Поэтому наряду с ним можно рассмотреть и другие смежные ключевые слова.

Для выбора всех данных из определённой таблицы используется знак «*».

FROM имя таблицы 1

Результатом работы данного запроса будет точная копия таблицы 1.

А здесь происходит выборка по условию WHERE, которое достаёт из таблицы 1 все значения, больше 2 в столбце 1.

FROM имя таблицы 1

WHERE имя таблицы 1.имя столбца 1 > 2

Также можно указать в выборке, что нужны только определённые столбцы.

SELECT имя таблицы 1.имя столбца 1

FROM имя таблицы 1

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

имя таблицы 1.имя столбца 1

имя таблицы 1.имя столбца 2

имя таблицы 1.имя столбца 3

имя таблицы 1.имя столбца 2 * имя таблицы 1.имя столбца 3 AS SUMMA

FROM имя таблицы 1

Данный, на первый взгляд сложный запрос выполняет выборку всех значений из таблицы 1, затем создаёт новые колонки EQ и SUMMA. В первую заносит знак «+», во вторую произведение данных из столбца 2 и 3. Полученный результат можно представить в виде таблицы, для понимания как это работает:

При использовании оператора SELECT, можно сразу провести упорядочивание данных по какому-либо признаку. Для этого используется слово ORDER BY.

имя таблицы 1.имя столбца 1

имя таблицы 1.имя столбца 2

имя таблицы 1.имя столбца 3

FROM имя таблицы 1

ORDER BY имя столбца 2

Результирующая таблица будет выглядеть таким образом:

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

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

Таблица «Сотрудники»

Таблица «Зарплата»

Теперь нужно, как-то связав эти две таблицы получить общие значения. Используя основные операторы SQL сделать это можно так:

Сотрудники.Номер

Сотрудники.Имя

Зарплата.Ставка

Зарплата.Начислено

FROM Сотрудники, Зарплата

WHERE Сотрудники.Номер = Зарплата.Номер

Здесь происходит выборка из двух разных таблиц значений, объединённых по номеру. Результатом будет следующий набор данных:

Ещё немного о SELECT. Использование агрегатных функций

Один из основных операторов может производить некоторые вычисления при выборке. Для этого он использует определённые функции и формулы.

К примеру, чтобы получить количество записей из таблицы «Сотрудники», нужно использовать запрос:

SELECT COUNT (*) AS N

FROM Сотрудники

В результате получится таблица с одним значением и столбцом.

Можно применить такой запрос и посмотреть что получится:

SUM(Зарплата.Начислено) AS SUMMA

MAX(Зарплата.Начислено) AS MAX

MIN(Зарплата.Начислено) AS MIN

AVG(Зарплата.Начислено) AS SRED

FROM Зарплата

Итоговая таблица будет такой:

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

Объединение, пересечение и разности

Объединить несколько запросов в SQL

SELECT Сотрудники.Имя

FROM Сотрудники

WHERE Сотрудники.Номер = 1

SELECT Сотрудники.Имя

FROM Сотрудники, Зарплата

WHERE Зарплата.Номер = 1

При этом стоит учитывать, что при таком объединении таблицы должны быть совместимы. То есть иметь одинаковое количество столбцов.

Синтаксис оператора SELECT и порядок его обработки

Первым делом SELECT определяет область, из которой он будет брать данные. Для этого используется ключевое слово FROM. Если не указано, что именно выбрать.

Затем может присутствовать SQL оператор WHERE. С его помощью SELECT пробегает по всем строкам таблицы и проверяет данные на соответствие условию.

Если в запросе имеется GROUP BY, то происходит группировка значений по указанным параметрам.

Операторы для сравнения данных

Их имеется несколько типов. В SQL операторы сравнения могут проверять различные типы значений.

«=». Обозначает, как можно догадаться, равенство двух выражений. Например, он уже использовался в примерах выше - WHERE Зарплата.Номер = 1.

«>». Знак больше. Если значение левой части выражения больше, то возвращается логическое TRUE и условие считается выполненным.

«<». Знак меньше. Обратный предыдущему оператор.

Знаки «<=» и «>=». Отличается от простых операторов больше и меньше, тем, что при равенстве операндов условие также будет истинным.

LIKE

Перевести данное ключевое слово можно как «похожий». Оператор LIKE в SQL используется примерно по такому же принципу — выполняет запрос по шаблону. То есть он позволяет расширить выборку данных из базы используя регулярные выражения.

Например, поставлена такая задача: из уже известной базы «Сотрудники» получить всех людей, чьё имя заканчивается на «я». Тогда запрос можно составить так:

FROM Сотрудники

WHERE Имя LIKE `%я`

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

CASE

Данный оператор SQL Server представляет собой реализацию множественного выбора. Он напоминает конструкцию switch во многих языках программирования. Оператор CASE в SQL выполняет действие по нескольким условиям.

Например, нужно выбрать из таблицы «Зарплата» максимальное и минимальное значение.

Тогда запрос можно составить так:

FROM Зарплата

WHERE CASE WHEN SELECT MAX(Начислено) THEN Максимум

WHEN SELECT MIN(Начислено) THEN Минимум

В данном контексте система ищет максимальное и минимальное значение в столбце «Начислено». Затем с помощью END создаётся поле «итог», в которое будет заноситься «Максимум» или «Минимум» в зависимости от результата выполнения условия.

Кстати, в SQL имеется и более компактная форма CASE — COALESCE.

Операторы определения данных

Это вид позволяет проводить разнообразное изменение таблиц — создание, удаление, модификации и работу с индексами.

Первый из них, который стоит рассмотреть — CREATE TABLE. Он делает не что иное, как создаёт таблицу. Если просто набрать запрос CREATE TABLE, ничего не случится, так как нужно ещё указать несколько параметров.

Например, для создания уже знакомой таблицы «Сотрудники» нужно использовать команды:

CREATE TABLE Сотрудники

(Номер number(10) NOT NULL

Имя varchar(50) NOT NULL

Фамилия varchar(50) NOT NULL)

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

DROP TABLE

Выполняет одну простую задачу — удаление указанной таблицы. Имеет дополнительный параметр IF EXISTS. Он поглощает ошибку при удалении, если искомая таблица не существует. Пример использования:

DROP TABLE Сотрудники IF EXISTS.

CREATE INDEX

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

CREATE INDEX название_индекса

ON название_таблицы(название_столбца)

Используется данный оператор в T-SQL, Oracle, PL SQL и многих других интерпретациях технологиях.

ALTER TABLE

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

ADD. Осуществляет добавление столбца в таблицу. Синтаксис его такой: ALTER TABLE название_таблицы ADD название_столбца тип_хранимых_данных. Может иметь параметр IF NOT EXISTS, что подавить ошибку, если создаваемый столбец уже есть;

DROP. Удаляет столбец. Также имеет ключ IF EXISTS, без которого сгенерируется ошибка, говорящая о том, что требуемый столбец отсутствует;

CHANGE. Служит для переименования имени поля в указанное. Пример использования: ALTER TABLE название_таблицы CHANGE старое_имя новое_имя;

MODIFY. Данная команда поможет сменить тип и дополнительные атрибуты определённого столбца. А используется он вот так: ALTER TABLE название_таблицы MODIFY название_столбца тип_данных атрибуты;

CREATE VIEW

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

Процесс создания происходит с помощью простого запроса:

CREATE VIEW название представления AS SELECT FROM * название таблицы

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

Немного о функциях

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

COUNT. Производит подсчёт записей или строк в конкретной таблице. В качестве параметра можно указать имя столбца, тогда данные будут взяты из него. SELECT COUNT * FROM Сотрудники;

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

MIN и MAX. Эти функции уже использовались в этой статье. Определяют они максимальное и минимальное значения из указанного столбца;

SUM. Все просто — функция вычисляет сумму значений столбца. Применяется исключительно для числового вида данных. Добавив в запрос параметр DISTINCT, будут суммироваться только уникальные значения;

ROUND. Функция округления десятичных дробных чисел. В синтаксисе используется название столбца и количество знаков после запятой;

LEN. Простая функция, вычисляющая длину значений столбца. Результатом будет новая таблица с указанием количества символов;

NOW. Это ключевое слово используется для вычисления текущей даты и времени.

Дополнительные операторы

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

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

BETWEEN. Очень удобный инструмент для выборки. Он указывает область значений, среди которых нужно получить данные. На вход принимает параметр от и до какого числа используется диапазон;.

NOT. Оператор придаёт противоположность выражению.

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

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

UNION. Очень удобный оператор для объединения нескольких запросов. Он уже встречался среди примеров этой в этой статье. Можно вывести нужные строки из нескольких таблиц, объединив их UNION для более удобного использования. Синтаксис его такой: SELECT имя_столбца FROM имя_таблицы UNION SELECT имя_другого_столбца FROM имя_другой таблицы. В результате получится сводная таблица с объединёнными запросами.

PRIMARY KEY. Переводится как «первичный ключ». Собственно, именно такая терминология и используется в справочных материалах. Он означает уникальный идентификатор строки. Применяется, как правило, при создании таблицы для указания поля, которое и будет содержать его.

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

NULL. Начинающие и не только программисты при составлении запросов очень часто забывают о возможности получения значения NULL. В итоге в код закрадывается ошибка, которую трудно отследить в процессе отладки. Поэтому при создании таблиц, выборке или пересчёте значений нужно остановиться и подумать, а учтено ли возникновение NULL в это участке запроса.

Память. В этой статье были показаны несколько функций, способные выполнять некоторые задачи. При разработке оболочки для работы с базой, можно «перевесить» вычисление простых выражений на систему управления базами данных. В некоторых случаях это даёт значительный прирост в производительности.

Ограничения. Если нужно получить из базы с тысячами строк всего лишь двух, то стоит использовать операторы типа LIMIT или TOP. Не нужно извлекать данные средствами языка разработки оболочки.

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

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

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

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

Типы. Для экономии места и ресурсов нужно чутко относиться к видам используемых данных. Если есть возможность воспользоваться менее «тяжёлым» для памяти типом, то надо применять именно его. Например, если известно, что в данном поле числовое значение не будет превышать 255, то зачем использовать 4-байтный INT, если есть TINYINT в 1 байт.

Заключение

В заключение нужно отметить, что язык структурированных запросов SQL сейчас используется практически повсеместно — сайты, веб-сервисы, программы для ПК, приложения для мобильных устройств. Поэтому знание SQL поможет всем отраслям разработки.

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

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

Основные функциональные возможности языка 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

В объектно-ориентированных БД используется язык объектных запросов 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?

Мы продолжаем изучение языка запросов SQL, и сегодня мы с Вами будем разговаривать о строковых функциях SQL . Мы рассмотрим основные и часто используемые строковые функции, такие как: LOWER, LTRIM, REPLACE и другие, все рассматривать мы будем, конечно же, на примерах.

SELECT name || surname AS FIO FROM table

Или чтобы отделить пробелом введите

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

Функция INITCAP

Дальше идет также очень полезная функция, INITCAP – которая возвращает значение в строке, в которой каждое слово начинается с заглавной буквы, а продолжается маленькими. Это нужно для того, если у Вас в той или иной колонке не соблюдают правила заполнения и для того чтобы вывести все это дело в красивом виде можно использовать данную функцию, например, у Вас в таблице записи в колонке name следующего вида: ИВАН иванов или петр петров, Вы применяете данную функцию.

SELECT INITCAP (name) AS FIO FROM table

И у Вас получится вот так.

Функция UPPER

Похожая функция, только возвращает все символы с заглавной буквы, это UPPER .

SELECT UPPER (name) AS FIO FROM table

• name – название колонки;
• 20 – количество знаков (длина поля );
• ‘-‘ – символ, которым нужно дополнить до необходимого количества знаков.

Функция RPAD

Сразу рассмотрим обратную функцию. RPAD – действие и синтаксис тот же что и у LPAD, только дополняются символы справа (в LPAD слева ).

SELECT RPAD (name, 20, "-") AS name FROM table

Функция LTRIM

Далее идет тоже в некоторых случаях полезная функция, LTRIM – эта функция удаляет крайние левые символы, которые Вы укажите. Например, у Вас в базе есть колонка «город», в которой город указан в виде «г.Москва», а также есть города которые указанны в виде просто «Москва». Но Вам нужно вывести отчет только в виде «Москва» без «г.», но как это сделать, если есть и такие и такие? Вы просто указываете своего рода шаблон «г.» и если крайние левые символы начинаются с «г.», то эти символы просто не будут выводиться.

SELECT LTRIM (city, "г.") AS gorod FROM table

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

Функция RTRIM

Также давайте сразу рассмотрим обратную функцию. RTRIM – то же самое что и LTRIM только символы ищутся справа.

Примечание! В Transact-SQL функции RTRIM и LTRIM удаляют пробелы справа и слева соответственно.

Функция REPLACE

Теперь рассмотрим такую интересную функцию как REPLACE – она возвращает строку, в которой все совпадения символов, заменяются на Ваши символы, которые Вы укажите. Для чего ее можно использовать, например, у Вас в базе есть колонки, в которых встречаются некие разделительные символы, допустим «/». Например, Иван/Иванов, а Вам хотелось бы вывести Иван-Иванов, то напишите

SELECT REPLACE (name, "/", "-") FROM table

и у Вас произойдет замена символов.

Данная функция заменяет только полное совпадение символов, если например Вы укажите «—» т.е. три тире она и будет искать только три тире, а каждое отдельное тире заменять не будет, в отличие от следующей функции.

Функция TRANSLATE

TRANSLATE – строковая функция, которая заменяет все символы в строке, на те символы, которые Вы укажите. Исходя из названия функции, можно догадаться, что это полный перевод строки. Отличие данной функции от REPLACE в том, что она заменяет каждый символ, который Вы укажите, т.е. у Вас есть три символа, допустим абв и с помощью TRANSLATE Вы его можете заменить на abc таким образом у Вас а=a, б=b, в=c и по такому принципу будут заменяться все совпадения символов. А если Вы заменяли с помощью REPLACE, то у Вас искалось только полное совпадение символов абв расположенных подряд.

Функция SUBSTR

SUBSTR – данная функция, возвращает только тот диапазон символов, который Вы укажите. Другими словами, допустим, строка из 10 символов, а Вам все десять не нужны, а допустим, нужны только 3-8 (с третьего по восьмой ). С помощью данной функции Вы легко можете это сделать. Например, у Вас в базе есть какой-нибудь идентификатор, фиксированной длинны (типа: AA-BB-55-66-CC) и каждая комбинация символов что-то означает. И в один прекрасный момент Вам сказали вывести только 2 и 3 комбинацию символов, для этого вы пишите запрос следующего вида.

SELECT SUBSTR (ident, "4", "8") FROM table

т.е. мы выводим все символы, начиная с 4 и заканчивая 8, и после этого запроса у Вас выведется вот это:

Функция LENGTH – длина строки

Следующая функция также может пригодиться, это LENGTH – которая просто на всего считает количество символов в строке. Например, Вам нужно узнать, сколько символов в каждой ячейки столбца допустим «name», таблица следующего вида.

после этого запроса Вы получите вот это.

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

Функции языка Transact-SQL могут быть агрегатными или скалярными. Эти типы функций рассматриваются в этой статье.

Агрегатные функции

Агрегатные функции выполняют вычисления над группой значений столбца и всегда возвращают одно значение результата этих вычислений. Язык Transact-SQL поддерживает несколько обычных агрегатных функций:

Вычисляет среднее арифметическое значение данных, содержащихся в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми.

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

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

Подсчитывает количество значений, отличных от null в столбце. Функция count(*) является единственной агрегатной функцией, которая не выполняет вычисления над столбцами. Эта функция возвращает количество строк (независимо от того, содержат ли отдельные столбцы значения null).

Аналогична функции count, с той разницей, что возвращает значение данных типа BIGINT.

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

Скалярные функции

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

числовые функции;

функции даты;

строковые функции;

системные функции;

функции метаданных.

Эти типы функций рассматриваются в последующих разделах.

Числовые функции

Числовые функции языка Transact-SQL - это математические функции для модифицирования числовых значений. Список числовых функций и их краткое описание приводится в таблице ниже:

Функции даты

Функции даты вычисляют соответствующие части даты или времени выражения или возвращают значение временного интервала. Поддерживаемые в Transact-SQL функции даты и их краткое описание приводятся в таблице ниже:

Строковые функции

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

Системные функции

Системные функции языка Transact-SQL предоставляют обширную информацию об объектах базы данных. Большинство системных функций использует внутренний числовой идентификатор (ID), который присваивается каждому объекту базы данных при его создании. Посредством этого идентификатора система может однозначно идентифицировать каждый объект базы данных.

В следующей таблице приводятся некоторые из наиболее важных системных функций вместе с их кратким описанием:

Функции метаданных

По большому счету, функции метаданных возвращают информацию об указанной базе данных и объектах базы данных. В таблице ниже приводятся некоторые из наиболее важных функций метаданных вместе с их кратким описанием: