Значения до какого то символа sql

Как вырезать/вычленить значение до определённого символа в sql?

Имеется таблица типа: Нужно получить: С substring знаком, но умею резать лишь до первой или последней ковычки.

Как указать номер знака («), чтобы возможно было получать адекватные ответы и можно ли создать конструкцию типа if, else if, else if, else для того, чтобы прописать все условия в запрос? Есть ли другие варианты решения данной проблемы?

Соглашусь с @zhukov_roman, что поменяв схему, можно было бы решить много проблем. Памятуя ваш предыдущий вопрос, логичным было бы выделить таблицу для хранения организационных форм, для предприятий (полное и сокращённое имя) и атрибутов предприятий. Тем не менее, отвечая на вопрос в поставленной форме можно попробовать рекурсивно отсекать всё, что вне самых внешних кавычек, чтобы добраться до содержимого внутренних. (Или использовать хитрую регулярку, решающую проблему в лоб но я сходу не придумал). На Oracle это было бы что-то вроде

В PostgreSQL, полагаю, для регулярных выражений можно использовать substring , вместо instr — position

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

Значения до какого то символа sql

О строковых функциях SQL или как манипулировать текстом в базе данных MySQL

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

Передо мной частенько возникают задачи: удалить часть текста из текстовых полей базы данных, объединить строковые данные или еще что-нибудь связанное с текстом. Делать все это через админские панели сайтов очень неудобно и муторно. Гораздо проще бывает написать запрос к базе данных выполняющий все эти действия за пару секунд.

Символьные функции в языке sql

Начнем по порядку с самого простого. Первой рассмотрим строковую функцию ASCII, которая используется для определения ASCII-кода текстовых символов:

integer ASCII(str string)

Функция возвращает целое значение — ASCII-код первого левого символа строки str. В случае если строка str пустая возвращает 0 и NULL если строка str не существует.

SELECT ASCII ('t');
Результат: 116
SELECT ASCII ('test');
Результат: 116
SELECT ASCII (1);
Результат: 49

Далее функция ORD, которая также определяет ASCII- код символов, но может обрабатывать также многобайтовые символы:

integer ORD(str string)

Если первый левый символ строки str многобайтовый, то возвращает его код в формате: ((первый байт ASCII- код)*256+(второй байт ASCII -код))[*256+третий байт ASCII -код. ]. В случае если первый левый символ строки str не является многобайтовым, работает как функция ASCII — возвращает его ASCII-код.

SELECT ORD ('test');
Результат: 116

Функция CHAR, тесно связанная с функцией ASCII и выполняет обратное действие:

string CHAR(int integer, . )

Функция CHAR возвращает строку символов по их ASCII-кодам. Если среди значений встречается значение NULL, то оно пропускается.

SELECT CHAR ( 116, '101', 115, '116' );
Результат: 'test'

SQL функции для объединения строк

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

string CONCAT(str1 string, str2 string. )

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

SELECT CONCAT ('Hello', ' ', 'world', '!');
Результат: 'Hello world!'
SELECT CONCAT ('Hello', NULL, 'world', '!');
Результат: NULL
SELECT CONCAT ('Число пи', '=', 3.14);
Результат: 'Число пи=3.14'

Как видно из примеров, строки объединяются без разделителей. Для того чтобы разделить слова в первом примере в качестве аргумента приходится использовать пробел. Если бы слов было больше, то каждый раз вставлять пробелы было бы не очень удобно.

Для таких случаев существует функция CONCAT_WS:

string CONCAT_WS(separator string, str1 string, str2 string. )

Функция объединяет строки как и функция CONCAT, но вставляет между аргументами разделитель separator. В случае если аргумент separator является NULL, то и результат будет NULL. Аргументы строки равные NULL пропускаются.

SELECT CONCAT_WS (' ', 'Иванов', 'Иван', 'Иванович');
Результат: 'Иванов Иван Иванович'
SELECT CONCAT_WS (NULL, 'Иванов', 'Иван', 'Иванович');
Результат: NULL
SELECT CONCAT_WS (' ', 'Иванов', NULL, 'Иван', 'Иванович');
Результат: ''Иванов Иван Иванович'

В случае объединения большого количества строк, которые необходимо отделять разделителем, функция CONCAT_WS гораздо удобнее функции CONCAT.

Иногда бывает необходимо удлинить строку до определенного количества символов за счет повторения какого-либо символа. Это тоже своего рода объединение строк. Для этого можно использовать функции LPAD и RPAD. Функции имеют следующий синтаксис:

string LPAD(str string, len integer, padstr string)
string RPAD(str string, len integer, padstr string)

Функция LPAD возвращает строку str дополненную слева строкой padstr до длины len. Функция RPAD выполняет тоже самое, только удлинение происходит с правой стороны.

SELECT LPAD ('test', 10, '.');
Результат: . test
SELECT RPAD ('test', 10, '.');
Результат: test.

В данных функциях необходимо обратить внимание на параметр len, который ограничивает количество выводимых символов. Поэтому если длина строки str будет больше чем параметр len, то строка будет обрезана:

SELECT LPAD ('test', 3, '.');
Результат: tes

Определение длины строки в sql запросах

Для определения количества символов в строке в языке SQL отвечает функция LENGTH — длина строки:

integer LENGTH(str string)

Функция возвращает целое число равное количеству символов в строке str.

SELECT LENGTH ('test');
Результат: 4

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

SELECT LENGTH ('тест');

вернет 8. Что, легко заметить, в два раза больше реального количества символов. В этом случае нужно использовать функцию CHAR_LENGTH:

integer CHAR_LENGTH(str string)

Функция также возвращает длину строки str и поддерживает многобайтовые символы.

SELECT CHAR_LENGTH ('тест');
Результат: 4

Поиск подстроки в строке средствами sql

Для вычисления позиции подстроки в строке в языке sql существует несколько функций. Первая, которую мы рассмотрим, функция POSITION:

integer POSITION(substr string IN str string)

Возвращает номер позиции первого вхождения подстроки substr в строке str и возвращает 0 если подстрока не найдена. Функция POSITION может работать с многобайтовыми символами.

SELECT POSITION ('cd' IN 'abcdcde');
Результат: 3
SELECT POSITION ('xy' IN 'abcdcde');
Результат: 0

Следующая функция LOCATE позволяет начинать поиск подстроки с определенной позиции:

integer LOCATE(substr string, str string, pos integer)

Возвращает позицию первого вхождения подстроки substr в строке str, начиная с позиции pos. Если параметр pos не задан, то поиск осуществляется с начала строки. Если подстрока substr не найдена, то возвращает 0. Поддерживает многобайтовые символы.

SELECT LOCATE ('cd', 'abcdcdde', 5);
Результат: 5
SELECT LOCATE ('cd', 'abcdcdde');
Результат: 3

Аналогом функций POSITION и LOCATE является функция INSTR:

integer INSTR(str string, substr string)

Также как и функции выше возвращает позицию первого вхождения подстроки substr в строке str. Единственное отличие от функций POSITION и LOCATE то, что аргументы поменяны местами.

Далее рассмотрим функции, которые помогают получить подстроку.

Первыми рассмотрим сразу две функции LEFT и RIGHT, которые похожи по своему действию:

string LEFT(str string, len integer)
string RIGHT(str string, len integer)

Функция LEFT возвращает len первых символов из строки str, а функция RIGHT столько же последних. Поддерживают многобайтовые символы.

SELECT LEFT ('Москва', 3);
Результат: Мос
SELECT RIGHT ('Москва', 3);
Результат: ква

Далее рассмотрим одинаковые по итоговому результату функции SUBSTRING и MID:

string SUBSTRING(str string, pos integer, len integer)
string MID(str string, pos integer, len integer)

Функции позволяют получить подстроку строки str длиною len символов с позиции pos. В случае если параметр len не задан, то возвращается вся подстрока начиная с позиции pos.

SELECT SUBSTRING ('г. Москва — столица России', 4, 6);
Результат: Москва
SELECT SUBSTRING ('г. Москва — столица России', 4);
Результат: Москва — столица России

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

Интересная функция SUBSTRING_INDEX:

string SUBSTRING_INDEX(str string, delim string, count integer)

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

SELECT SUBSTRING_INDEX ('www.mysql.ru', '.', 1);
Результат: www

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

SELECT SUBSTRING_INDEX ('www.mysql.ru', '.', 2);
Результат: www.mysql

Здесь функция ищет второе вхождение точки, удаляет все символы справа от нее и возвращает получившуюся подстроку. И еще один пример с отрицательным значением параметра count:

SELECT SUBSTRING_INDEX ('www.mysql.ru', '.', -2);
Результат: mysql.ru

В этом примере функция SUBSTRING_INDEX ищет вторую точку, отсчитывая позицию справа, удаляет символы слева от нее и выдает полученную подстроку.

Удаление пробелов из строки

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

string LTRIM(str string)

Удаляет с начала строки str пробелы и возвращает результат.

string RTRIM(str string)

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

SELECT LTRIM (' текст ');
Результат: 'текст '
SELECT RTRIM (' текст ');
Результат: ' текст'

И третья функция TRIM позволяет сразу удалять пробелы из начала и из конца строки:

string TRIM([[BOTH | LEADING | TRAILING] [remstr] string FROM] str string)

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

SELECT TRIM (' текст ');
Результат: 'текст'

С помощью пара метра remstr можно задавать символы или подстроки, которые будут удаляться из начала и конца строки. С помощью управляющих параметров BOTH, LEADING, TRAILING можно задавать откуда будут удаляться символы:

BOTH — удаляет подстроку remstr с начала и с конца строки;
LEADING — удаляет remstr с начала строки;
TRAILING — удаляет remstr с конца строки.

SELECT TRIM (BOTH 'а' FROM 'текст');
Результат: 'текст'
SELECT TRIM (LEADING 'а' FROM 'текстааа');
Результат: 'текстааа'
SELECT TRIM (TRAILING 'а' FROM 'ааатекст');
Результат: 'ааатекст'

Функция SPACE позволяет получить строку состоящую из определенного количества пробелов:

string SPACE(n integer)

Возвращает строку, которая состоит из n пробелов.

Функция REPLACE нужна для замены заданных символов в строке:

string REPLACE(str string, from_str string, to_str string)

Функция заменяет в строке str все подстроки from_str на to_str и возвращает результат. Поддерживает многобайтные символы.

SELECT REPLACE ( 'замена подстроки', 'подстроки', 'текста' )
Результат: 'замена текста'

string REPEAT(str string, count integer)

Функция возвращает строку, которая состоит из count повторений строки str. Поддерживает многобайтовые символы.

Функция REVERSE переворачивает строку:

string REVERSE(str string)

Переставляет в строке str все символы с последнего на первый и возвращает результат. Поддерживает многобайтовые символы.

SELECT REVERSE ('текст');
Результат: 'тскет'

Функция INSERT для вставки подстроки в строку:

string INSERT(str string, pos integer, len integer, newstr string)

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

SELECT INSERT ('text', 2, 5, 'MySQL');
Результат: 'tMySQL'
'SELECT INSERT ('text', 2, 0, 'MySQL');
Результат: 'tMySQLext'
SELECT INSERT ('вставка текста', 2, 7, 'MySQL');
Результат: 'SELECT INSERT ('вставка текста', 2, 7, 'MySQL');'

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

string LCASE(str string) и string LOWER(str string)

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

SELCET LOWER ('АБВГДеЖЗиКЛ');
Результат:'абвгдежзикл'

Если же наоборот необходимо прописные буквы заменить заглавными, то также можно применить одну из двух функцийй:

string UCASE(str string) и string UPPER (str string)

Функции возвращают строку str, заменив все прописные символы на заглавные. Также поддерживают многобайтовые символы.
Пример:

SELECT UPPER ('Абвгдежз');
Результат: 'АБВГДЕЖЗ'

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

Как вырезать/вычленить значение до определённого символа в sql?

Имеется таблица типа: Нужно получить: С substring знаком, но умею резать лишь до первой или последней ковычки.

В PostgreSQL, полагаю, для регулярных выражений можно использовать substring , вместо instr — position

2.25. Функции работы со строками

У SQL сервера достаточно много мощных функций для работы со строками и в этом разделе мы рассмотрим наиболее интересные и часто используемые из них. Из моего личного опыта (ваши задачи могут дать другой результат), наиболее часто используемой является функция SUBSTRING. Именно с нее мы и начнем.

SUBSTRING

Помниться, что мы добавили к значениям в колонке имен работников префикс ‘mr.’ (см. разд. 2.17). А как теперь от него избавится во время обращения к таблице? Достаточно просто, если воспользоваться функцией SUBSTRING, которая возвращает указанную часть строки. Этой функции необходимо передать три параметра:

Поле, часть строки которого нужно получить;
Первый символ;
Количество интересующих нас символов.

Посмотрим, как вышесказанное можно реализовать в виде запроса:

В этом примере, мы выбираем только два поля: «idPeoples» и поле, результат которого зависит от проверки CASE. В данном случае CASE проверяет результат работы функции SUBSTRING, которая выбирает символы из поля «vcFamil» начиная с первого по третий. Если результат равен ‘mr.’, то необходимо обрезать этот префикс.

Для того, чтобы отбросить ненужные символы от значения поля, мы снова пользуемся функцией SUBSTRING, но теперь выбираем символы, начиная с четвертного (начиная с первого, после ‘mr.’). В качестве количества символов я указал число 255, что больше максимального значения поля, а значит, строка будет выбрана до конца, начиная 4-го.

Теперь попробуем обновить данные в таблице, чтобы в поле «vcName», чтобы в нем не было лишних символов ‘mr.’. Для этого выполняем следующий запрос:

В этом примере полю «vcName»присваивается результат сравнения CASE, который мы уже рассмотрели выше. Таким образом, мы избавились от лишних букв в фамилиях.

Задачу обрезание лишних символов из начала строки можно было бы решить и с использованием функции LEFT, которая возвращает указанное количество символов, начиная с 1-го. Функции нужно передать следующие два параметра:

Поле, подстроку которого нужно получить;
Количество символов.

Следующий пример формирует ФИО, в котором имя и отчество сокращены:

Поле «vcFamil» выводится полностью, а вот от имени и отчества выводится только один левый (первый) символ.

Теперь посмотрим, как можно было использовать LEFT для обрезания префикса ‘mr.’:

Функция LEN позволяет определить длину строки или значения поля. Функции достаточно передать строку или имя поля, длина значений которого нас интересует. Например, следующий запрос отобразить длину всех значений в поле «vcFamil»:

В следующем примере мы ищем записи, в которых фамилия состоит 7-и символов:

LOWER

Если ваш сервер настроен так, что строки чувствительные к регистру букв, то с поиском по строковым полям могут быть серьезные проблемы. Если вы указали фамилию как Иванов, то это значение не будет равно ИВАНОВ, а значит, мы не увидим необходимую запись. Проблему решает функция LOWER, которая приводит указанную строку к нижнему регистру.

Рассмотрим пример. В следующем запросе мы выбираем все фамилии, при этом они отображаются в нижнем регистре (маленькими буквами):

Теперь посмотрим на следующий пример:

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

UPPER

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

Если вам нужно сравнить две строки не обращая внимания на используемых регистр букв внутри строк, можно использовать как UPPER, так и LOWER. Разницы никакой нет, поэтому выбирайте то, что больше нравится.

LTRIM и RTRIM

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

Когда смотришь на эти фамилии, то видно, что вторая строка сдвинута вправо за счет пробела вначале. Это значит, что база данных будет воспринимать эти значения по-разному. Чтобы избавится от лишних пробелов, как раз используют функции LTRIM и RTRIM. Например:

В этом примере поле «vcFamil» сравнивается с фамилией Сидоров, с пробелом в начале. Чтобы убрать пробел используется функция LTRIM. В следующем примере мы убираем и левые и правые пробелы:

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

Если работник с фамилией Сидоров (без пробелов в конце) существует в таблице, и запрос отобразил его, то сервер автоматически убрал пробел.

PATINDEX

С помощью функции PATINDEX можно искать часть подстроки по определенному шаблону. Допустим, что нам надо найти все фамилии, в которых есть две буквы «о», между которыми может находиться любой символ. Эту задачу можно решить с помощью следующего запроса:

Если посмотреть на функцию, то пока не понятно, чем она отличается от LIKE с шаблоном? Все очень просто – LIKE используется для создания ограничений в секции WHERE, а PATINDEX возвращает индекс символа, начиная с которого идет указанный шаблон в строке. Если бы мы использовали LIKE, то сервер вернул бы нам только те строки, где найден шаблон:

Если использовать функцию PATINDEX, то в результат попадут все строки (мы не ограничиваем вывод в секции WHERE), но там где в фамилии нет шаблона, в соответствующей строке будет стоять ноль, а там где есть, будет стоять 1. Посмотрим на пример результата выполнения запроса с использованием функции PATINDEX:

В данном примере шаблон ‘%О_О%’ присутствует в фамилии Сидоров. Начиная с четвертого символа идут буквы «оро».

REPLACE

Функция replace позволяет найти в значении поля подстроку и заменить ее на новое значение. У этой функции три параметра:

Строка, в которой нужно искать подстроку;
Подстрока, которую ищем;
Значение, которое нужно подставить.

Посмотрим пример использования этой функции:

Мы выбираем из таблицы два поля: фамилию и результат функции REPLACE. Функция ищет в поле «vcFamil» строку «оро» и заменяет ее на строку «аро». Чтобы лучше было понятно, посмотрим на результат работы функции:

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

REPLICATE

С помощью функции REPLICATE можно размножать строку. У функции два параметра:

Строка или имя поля, которое нужно вывести несколько раз;
Количество необходимых повторений

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

В результате мы увидим нечто подобное:

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

Красиво? А главное удобно в управлении.

REVERSE

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

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

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

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

SPACE

С помощью функции SPACE можно создавать пробелы. В качестве единственного параметра нужно указать число, которое определяет количество возвращаемых пробелов. Работа функции идентична REPLICATE, если в качестве клонируемого символа указать пробел.

Допустим, что нам нужно вывести на экран поля фамилию и имя, разделенные 5-ю пробелами. Можно сделать так:

А можно воспользоваться функцией SPACE:

Зачем нужна функция, когда можно воспользоваться без нее? Допустим, что вам нужно использовать 5 пробелов в нескольких местах большого сценария. Все легко решается без функций, но в последствии оказалось, что количество пробелов должно быть не 5, а 10. Придется пересматривать весь сценарий и корректировать пробелы. А если бы мы использовали SPACE в сочетании с переменными, то проблема решилась бы намного проще.

Рассмотрим пример, в котором множественные пробелы используются дважды и для задания количества используется переменная:

Теперь, достаточно только изменить значение переменной, и количество пробелов изменено во всем сценарии. А главное – что количество пробелов может быть определено динамически, на основе запросов к таблице.

С помощью функции STR можно форматировать дробные числа в строку. Чем это отличается от преобразования типов? Тип остается тем же, а на экран мы выводим строку в нужном виде. Функции нужно передать три параметра:

Дробное число, которое нужно форматировать;
Общее количество символов, включая числа до и после запятой, пробелы и знак;
Количество знаков после запятой.

Допустим, что нам нужно вывести название и цену товара. Но цена имеет тип money, который содержит слишком большое количество нулей. Чтобы избавиться от лишних чисел после запятой и получить строку, можно сначала привести тип money к типу number(10, 2), а потом результат привести к строке. Но можно решить все одной командой форматирования STR:

Выполните этот запрос и обратите внимание, что второе поле (отформатированная цена) выровнена вправо:

Выравнивание происходит из-за второго параметра – числа 10. Мы задали общее число символов, и выравнивание будет происходить по правой позиции указанного значения. Если второй параметр равен 10, а число состоит из 4 символов, то в начало результирующей строки будет добавлено 6 пробелов. Учитывайте это, при использовании функции STR.

STUFF

Функция STUFF позволяет вставить строку в определенную позицию другой строки. У этой функции четыре параметра:

Строка, которую нужно изменить;
Позиция, в которую должна произойти вставка;
Количество удаляемых символов;
Вставляемая строка.

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

Рассмотрим пример, в котором цена вставляется в поле названия товара, начиная с первой позиции, не удаляя ни одного из символов:

Результат работы функции будет следующим:

На этом примере более наглядно видно, что вставляемая цена выравнивается вправо. Так как мы указали в функции STR количество символов равное 10, то вставляется не реальный размер цены, а именно 10 символов.

Попробуйте увеличить третий параметр до 1. В этом случае, первый символ в названии товара будет удален, а вместо него будет вставлена цена.

Функции работы со строками в MS SQL SERVER 2005

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

ASCII	NCHAR	SOUNDEX
CHAR	PATINDEX	SPACE
CHARINDEX	REPLACE	STR
DIFFERENCE	QUOTENAME	STUFF
LEFT	REPLICATE	SUBSTRING
LEN	REVERSE	UNICODE
LOWER	RIGHT	UPPER
LTRIM	RTRIM

Начнем с двух взаимно обратных функций — ASCII и CHAR.

Функция ASCII возвращает ASCII-код крайнего левого символа строкового выражения, являющегося аргументом функции.

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

Результат — 11. Чтобы выяснить, какие это буквы, мы можем применить функцию CHAR, которая возвращает символ по известному ASCII-коду (от 0 до 255):

Следует отметить, что аналогичный результат можно получить проще с помощью еще одной функции — LEFT, которая имеет следующий синтаксис:

LEFT (<строковое выражение>, <целочисленное выражение> )

и вырезает заданное вторым аргументом число символов слева из строки, являющейся первым аргументом. Итак,

А вот как, например, можно получить таблицу кодов всех алфавитных символов:

Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье .

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

Чтобы таблица выглядела более патриотично, достаточно заменить латинские буквы «a» и «A» на неотличимые на взгляд русские — «а» и «А», а «z» и «Z» на «я» и «Я». Вот только буквы «ё» вы не увидите в этой таблице, т.к. в кодовой таблице ASCII эти символы лежат отдельно, что легко проверить:

Я полагаю, что не будет сложным добавить эту букву в таблицу, если потребуется.

Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции — CHARINDEX и PATINDEX. Обе они возвращают начальную позицию (позицию первого символа подстроки) подстроки в строке. Функция CHARINDEX имеет синтаксис:

CHARINDEX (искомое_выражение, строковое_выражение[, стартовая_позиция])

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

будет выводить те корабли, в которых имеется сочетание символов «sh». Здесь используется тот факт, что если искомая строка не будет обнаружена, то функция CHARINDEX возвращает 0. Результат выполнения запроса будет содержать следующие корабли:

name

Kirishima

Musashi

Washington

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

Следующий пример определяет позиции первого и второго вхождения символа «a» в имени корабля «California»

Обратите внимание, что при определении второго символа в функции используется стартовая позиция, которой является позиция следующего за первой буквой «a» символа — CHARINDEX(‘a’, name)+1. Правильность результата — 2 и 10 — легко проверить :-).

Функция PATINDEX имеет синтаксис:

PATINDEX (‘%образец%’ , строковое_выражение)

Главное отличие этой функции от CHARINDEX заключается в том, что поисковая строка может содержать подстановочные знаки — % и _. При этом концевые знаки «%» являются обязательными. Например, использование этой функции в первом примере будет иметь вид

А вот, например, как можно найти имена кораблей, которые содержат последовательность из трех символов, первый и последний из которых есть «e»:

Результат выполнения этого запроса выглядит следующим образом:

name

Revenge

Royal Sovereign

Парная к LEFT функция RIGHT возвращает заданное число символов справа из строкового выражения:

RIGHT(<строковое выражения>,<число символов>)

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

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

Соединение двух строковых значений в одно называется конкатенацией, и в SQL Server для этой операции используется знак «+» (в стандарте «||»). Итак,

Здесь мы разделяем пробелом имя класса и имя корабля. Кроме того, чтобы не повторять всю конструкцию в качестве аргумента функции, используем подзапрос. Результат будет иметь вид:

Iowa Missouri

North Carolina Washington

А если строковое выражение будет содержать лишь одну букву? Запрос выведет ее. В этом легко убедиться, написав

Чтобы исключить этот случай, можно воспользоваться еще одной полезной функцией LEN (<строковое выражение>), которая возвращает число символов в строке. Ограничимся случаем, когда число символов больше единицы:

Замечание. Реализация этой функции в MS SQL Server имеет одну особенность, а именно, при подсчете длины не учитываются концевые пробелы.
Действительно, выполним следующий код:

Функция REPLICATE дополняет константу ‘abcde’ пятью пробелами справа, которые не учитываются функцией LEN, — в обоих случаях получаем 5.
Функция DATALENGTH возвращает число байтов в представлении переменной и демонстрирует нам различие между типами CHAR и VARCHAR. DATALENGTH даст нам 12 для типа CHAR и 10 — для VARCHAR.
Как и следовало ожидать, DATALENGTH для переменной типа VARCHAR вернула фактическую длину переменной. Но почему для переменной типа CHAR результат оказался равным 12? Дело в том, что CHAR — это тип фиксированной длины. Если значение переменной оказывается меньше ее длины, а длину мы объявили как CHAR(12), то значение переменной будет «выровнено» до требуемой длины за счет добавления концевых пробелов.

На сайте имеются задачи, в которых требуется упорядочить (найти максимум и т.д.) в числовом порядке значения, представленные в текстовом формате. Например, номер места в самолете («2d») или скорость CD («24x»). Проблема заключается в том, что текст сортируется так (по возрастанию)

11a

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

11a

Чтобы добиться такого порядка, нужно выполнить сортировку по числовым значениям, присутствующим в тексте. Можно предложить такой алгоритм:
1. Извлечь число из строки.
2. Привести его к числовому формату.
3. Выполнить сортировку по приведенному значению.

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

Если только этим и ограничиться, то получим

place

11a

Приведение к числовому формату может быть следующим:

Осталось выполнить сортировку

Что и требовалось доказать.

Ранее мы для извлечения числа из текстовой строки пользовались функцией LEFT, т.к. нам было известно априори, какое число символов нужно убрать справа (один). А если же нужно извлечь строку из подстроки не по известной позиции символа, а по самому символу? Например: извлечь все символы до первой буквы «х» (значение скорости CD).

В этом случае мы можем использовать также уже рассмотренную ранее функцию CHARINDEX, которая позволит определить неизвестную позицию символа:

Функция SUBSTRING

SUBSTRING (<выражение>, <начальная позиция>, <длина> )

Эта функция позволяет извлечь из выражения его часть заданной длины, начиная от заданной начальной позиции. Выражение может быть символьной или бинарной строкой, а также иметь тип text или image. Например, если нам потребуется получить 3 символа в названии корабля, начиная со 2-го символа, то сделать без помощи функции SUBSTRING будет не так просто. А так мы пишем:

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

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

Функция REVERSE

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

будет ‘fedcba’. Если бы в языке отсутствовала функция RIGHT, то запрос

можно было бы равносильно заменить запросом

Я вижу пользу этой функции в следующем. Пусть нам требуется определить позицию не первого, а последнего вхождения некоторого символа (или последовательности символов) в строке. Вспомним пример, в котором мы определяли позицию первого символа «а» в названии корабля «California»:

Определим теперь позицию последнего вхождения в это название символа «а». Функция

позволит найти эту позицию, но справа. Для получения позиции этого же символа слева достаточно написать

Функция REPLACE

REPLACE ( <строка1> , <строка2> , <строка3> )

Заменяет в строке1 все вхождения строки2 на строку3. Эта функция, безусловно, полезна в операторах обновления (UPDATE), если нужно изменить (исправить) содержимое столбца. Пусть, например, нужно заменить все пробелы дефисом в названиях кораблей. Тогда можно написать

(Этот пример можно выполнить на странице с упражнениями DML, где разрешаются запросы на изменение данных)

Однако эта функция может найти применение и в более нетривиальных случаях. Давайте определим, сколько раз в названии корабля используется буква «a». Идея проста: заменим каждую искомую букву двумя любыми символами, после чего посчитаем разность длин полученной и искомой строки. Итак,

А если нам нужно определить число вхождений произвольной последовательности символов, скажем, передаваемой в качестве параметра в хранимую процедуру? Использованный выше алгоритм в этом случае следует дополнить делением на число символов в искомой последовательности:

Для удвоения числа искомых символов здесь применялась конкатенация — @str + @str . Однако для этой цели можно использовать еще одну функцию — REPLICATE, которая повторяет первый аргумент такое число раз, которое задается вторым аргументом.

Т.е. мы повторяем дважды подстроку, хранящуюся в переменной @str .

Если же нужно заменить в строке не определенную последовательность символов, а заданное число символов, начиная с некоторой позиции, то проще использовать функцию STUFF:

STUFF (<строка1> , <стартовая позиция> , <L> , <строка2>)

Эта функция заменяет подстроку длиной L, которая начинается со стартовой позиции в строке1, на строку2.

Пример. Изменить имя корабля: оставив в его имени 5 первых символов, дописать «_» (нижнее подчеркивание) и год спуска на воду. Если в имени менее 5 символов, дополнить его пробелами.

Можно решать эту задачу с помощью разных функций. Мы же попытаемся это сделать с помощью функции STUFF. В первом приближении напишем (ограничимся запросом на выборку):

Третьим аргументом (количество символов для замены) я использую LEN(name), т.к. мне нужно заменить все символы до конца строки, поэтому я беру с запасом — исходное число символов в имени. И все же этот запрос вернет ошибку. Причем дело не в третьем аргументе, а в четвертом, где выполняется конкатенация строковой константы и числового столбца. Ошибка приведения типа. Для преобразования числа к его строковому представлению можно воспользоваться еще одной встроенной функцией — STR:

STR ( <число с плавающей точкой> [ , <длина> [ , <число десятичных знаков> ] ] )

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

STR(3.3456, 5, 1)	3.3
STR(3.3456, 5, 2)	3.35
STR(3.3456, 5, 3)	3.346
STR(3.3456, 5, 4)	3.346

Обратите внимание, что если полученное строковое представление числа меньше заданной длины, то добавляются лидирующие пробелы. Если же результат больше заданной длины, то усекается дробная часть (с округлением); в случае же целого числа получаем соответствующее число звездочек «*»:

STR(12345,4,0)

****

Кстати, по умолчанию используется длина в 10 символов. Имея в виду, что год представлен четырьмя цифрами, напишем

Уже почти все правильно. Осталось учесть случай, когда число символов в имени менее 6, т.к. в этом случае функция STUFF дает NULL. Ну что ж вытерпим до конца мучения, связанные с использованием этой функции в данном примере, попутно применив еще одну строковую функцию. Добавим конечные пробелы, чтобы длина имени была заведомо больше 6. Для этого имеется специальная функция SPACE

SPACE(<число пробелов>):

Функции LTRIM и RTRIM

LTRIM (<строковое выражение>)

RTRIM (<строковое выражение>)

отсекают соответственно лидирующие и конечные пробелы строкового выражения, которое неявно приводится к типу VARCHAR.

Пусть требуется построить такую строку: имя пассажира_идентификатор пассажира для каждой записи из таблицы Passenger. Если мы напишем

то в результате получим что-то типа:

Это связано с тем, что столбец name имеет тип CHAR(30). Для этого типа короткая строка дополняется пробелами до заданного размера (у нас 30 символов). Здесь нам как раз и поможет функция RTRIM:

Функции LOWER и UPPER

LOWER(<строковое выражение>)

UPPER(<строковое выражение>)

преобразуют все символы аргумента соответственно к нижнему и верхнему регистру. Эти функции оказываются полезными при сравнении регистрозависимых строк.

Пара интересных функций SOUNDEX и DIFFERENCE:

SOUNDEX(<строковое выражение>)

DIFFERENCE (<строковое выражение_1>, <строковое выражение_2>)

Позволяют определить близость звучания слов. При этом SOUNDEX возвращает четырехсимвольный код, используемый для сравнения, а DIFFERENCE собственно и оценивает близость звучания двух сравниваемых строковых выражений. Поскольку эти функции не поддерживают кириллицы, отсылаю интересующихся к BOL за примерами их использования.

В заключение приведем функции и несколько примеров использования юникода.

Функция UNICODE

UNICODE (<строковое выражение>)

возвращает юникод первого символа строкового выражения.

Функция NCHAR

NCHAR (<целое>)

возвращает символ по его юникоду. Несколько примеров.

возвращает код ASCII и юникод русской буквы «а»: 224 и 1072.

Пытаемся восстановить символ по его коду. Получаем «а» и NULL. NULL-значение возвращается потому, что кода 1072 нет в обычной кодовой таблице.

Теперь все нормально, в обоих случаях «а». Наконец,

даст «a», т.к. юникод 224 соответствует именно этой букве.

Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок «Без проверки» на странице с упражнениями на SELECT.

О строковых функциях SQL или как манипулировать текстом в базе данных MySQL

Символьные функции в языке sql

integer ASCII(str string)

SELECT ASCII ('t');
Результат: 116
SELECT ASCII ('test');
Результат: 116
SELECT ASCII (1);
Результат: 49

integer ORD(str string)

SELECT ORD ('test');
Результат: 116

Функция CHAR, тесно связанная с функцией ASCII и выполняет обратное действие:

string CHAR(int integer, . )

SELECT CHAR ( 116, '101', 115, '116' );
Результат: 'test'

SQL функции для объединения строк

string CONCAT(str1 string, str2 string. )

Для таких случаев существует функция CONCAT_WS:

string CONCAT_WS(separator string, str1 string, str2 string. )

string LPAD(str string, len integer, padstr string)
string RPAD(str string, len integer, padstr string)

Читать:

Как снять закрепление областей в excel

SELECT LPAD ('test', 10, '.');
Результат: . test
SELECT RPAD ('test', 10, '.');
Результат: test.

SELECT LPAD ('test', 3, '.');
Результат: tes

Определение длины строки в sql запросах

Для определения количества символов в строке в языке SQL отвечает функция LENGTH — длина строки:

integer LENGTH(str string)

Функция возвращает целое число равное количеству символов в строке str.

SELECT LENGTH ('test');
Результат: 4

SELECT LENGTH ('тест');

integer CHAR_LENGTH(str string)

Функция также возвращает длину строки str и поддерживает многобайтовые символы.

SELECT CHAR_LENGTH ('тест');
Результат: 4

Поиск подстроки в строке средствами sql

integer POSITION(substr string IN str string)

SELECT POSITION ('cd' IN 'abcdcde');
Результат: 3
SELECT POSITION ('xy' IN 'abcdcde');
Результат: 0

Следующая функция LOCATE позволяет начинать поиск подстроки с определенной позиции:

integer LOCATE(substr string, str string, pos integer)

SELECT LOCATE ('cd', 'abcdcdde', 5);
Результат: 5
SELECT LOCATE ('cd', 'abcdcdde');
Результат: 3

Аналогом функций POSITION и LOCATE является функция INSTR:

integer INSTR(str string, substr string)

Далее рассмотрим функции, которые помогают получить подстроку.

Первыми рассмотрим сразу две функции LEFT и RIGHT, которые похожи по своему действию:

string LEFT(str string, len integer)
string RIGHT(str string, len integer)

SELECT LEFT ('Москва', 3);
Результат: Мос
SELECT RIGHT ('Москва', 3);
Результат: ква

Далее рассмотрим одинаковые по итоговому результату функции SUBSTRING и MID:

string SUBSTRING(str string, pos integer, len integer)
string MID(str string, pos integer, len integer)

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

Интересная функция SUBSTRING_INDEX:

string SUBSTRING_INDEX(str string, delim string, count integer)

SELECT SUBSTRING_INDEX ('www.mysql.ru', '.', 1);
Результат: www

SELECT SUBSTRING_INDEX ('www.mysql.ru', '.', 2);
Результат: www.mysql

SELECT SUBSTRING_INDEX ('www.mysql.ru', '.', -2);
Результат: mysql.ru

Удаление пробелов из строки

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

string LTRIM(str string)

Удаляет с начала строки str пробелы и возвращает результат.

string RTRIM(str string)

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

SELECT LTRIM (' текст ');
Результат: 'текст '
SELECT RTRIM (' текст ');
Результат: ' текст'

И третья функция TRIM позволяет сразу удалять пробелы из начала и из конца строки:

string TRIM([[BOTH | LEADING | TRAILING] [remstr] string FROM] str string)

SELECT TRIM (' текст ');
Результат: 'текст'

BOTH — удаляет подстроку remstr с начала и с конца строки;
LEADING — удаляет remstr с начала строки;
TRAILING — удаляет remstr с конца строки.

Функция SPACE позволяет получить строку состоящую из определенного количества пробелов:

string SPACE(n integer)

Возвращает строку, которая состоит из n пробелов.

Функция REPLACE нужна для замены заданных символов в строке:

string REPLACE(str string, from_str string, to_str string)

SELECT REPLACE ( 'замена подстроки', 'подстроки', 'текста' )
Результат: 'замена текста'

string REPEAT(str string, count integer)

Функция возвращает строку, которая состоит из count повторений строки str. Поддерживает многобайтовые символы.

Функция REVERSE переворачивает строку:

string REVERSE(str string)

SELECT REVERSE ('текст');
Результат: 'тскет'

Функция INSERT для вставки подстроки в строку:

string INSERT(str string, pos integer, len integer, newstr string)

string LCASE(str string) и string LOWER(str string)

SELCET LOWER ('АБВГДеЖЗиКЛ');
Результат:'абвгдежзикл'

string UCASE(str string) и string UPPER (str string)

SELECT UPPER ('Абвгдежз');
Результат: 'АБВГДЕЖЗ'

Памятка/шпаргалка по SQL

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

Для более полного погружения в SQL рекомендую изучить эти руководства по MySQL и PostgreSQL от Метанита. Они хороши тем, что просты в изучении и позволяют быстро начать работу с названными СУБД.

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

Содержание

Что такое SQL?

SQL — это язык структурированных запросов (Structured Query Language), позволяющий хранить, манипулировать и извлекать данные из реляционных баз данных (далее — РБД, БД).

Почему SQL?

получать доступ к данным в системах управления РБД
описывать данные (их структуру)
определять данные в БД и управлять ими
взаимодействовать с другими языками через модули SQL, библиотеки и предваритальные компиляторы
создавать и удалять БД и таблицы
создавать представления, хранимые процедуры (stored procedures) и функции в БД
устанавливать разрешения на доступ к таблицам, процедурам и представлениям

Процесс SQL

При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др.) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.

В данном процессе участвует несколького компонентов:

диспетчер запросов (Query Dispatcher)
движок оптимизации (Optimization Engines)
классический движок запросов (Classic Query Engine)
движок запросов SQL (SQL Query Engine) и т.д.

Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.

Команды SQL

Стандартными командами для взаимодействия с РБД являются CREATE , SELECT , INSERT , UPDATE , DELETE и DROP . Эти команды могут быть классифицированы следующим образом:

DDL — язык определения данных (Data Definition Language)

N	Команда	Описание
1	CREATE	Создает новую таблицу, представление таблицы или другой объект в БД
2	ALTER	Модифицирует существующий в БД объект, такой как таблица
3	DROP	Удаляет существующую таблицу, представление таблицы или другой объект в БД

DML — язык изменения данных (Data Manipulation Language)

N	Команда	Описание
1	SELECT	Извлекает записи из одной или нескольких таблиц
2	INSERT	Создает записи
3	UPDATE	Модифицирует записи
4	DELETE	Удаляет записи

DCL — язык управления данными (Data Control Language)

N	Команда	Описание
1	GRANT	Наделяет пользователя правами
1	REVOKE	Отменяет права пользователя

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

Что такое таблица?

Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.

Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):

userId	userName	age	city	status
1	Igor	25	Moscow	active
2	Vika	26	Ekaterinburg	inactive
3	Elena	27	Ekaterinburg	active
4	Oleg	28	Moscow	inactive

Что такое поле?

Каждая таблица состоит из небольших частей — полей (fields). Полями в таблице users являются userId, userName, age, city и status. Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице.

Обратите внимание: вместо userId и userName можно было бы использовать id и name , соответственно. Но при работе с несколькими объектами, содержащими свойство id , бывает сложно понять, какому объекту принадлежит идентификатор, особенно, если вы, как и я, часто прибегаете к деструктуризации. Что касается слова name , то оно часто оказывается зарезервизованным, т.е. уже используется в среде, в которой выполняется код, поэтому я стараюсь его не использовать.

Что такое запись или строка?

Запись или строка (record/row) — это любое единичное вхождение (entry), существующее в таблице. В таблице users 5 записей. Проще говоря, запись — это горизонтальное вхождение в таблице.

Что такое колонка?

Колонка (column) — это вертикальное вхождение в таблице, содержащее всю информацию, связанную с определенным полем. В таблице users одной из колонок является city , которая содержит названия городов, в которых проживают пользователи.

Что такое нулевое значение?

Нулевое значение (NULL) — это значение поля, которое является пустым, т.е. нулевое значение — это значение поля, не имеющего значения. Важно понимать, что нулевое значение отличается от значения 0 и от значения поля, содержащего пробелы ( `). Поле с нулевым значением — это такое поля, которое осталось пустым при создании записи. Также, следует учитывать, что в некоторых СУБД пустая строка ( » ) — это NULL`, а в некоторых — это разные значения.

Ограничения

Ограничения (constraints) — это правила, применяемые к данным. Они используются для ограничения данных, которые могут быть записаны в таблицу. Это обеспечивает точность и достоверность данных в БД.

Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.

Среди наиболее распространенных ограничений можно назвать следующие:

NOT NULL — колонка не может иметь нулевое значение
DEFAULT — значение колонки по умолчанию
UNIQUE — все значения колонки должны быть уникальными
PRIMARY KEY — первичный или основной ключ, уникальный идентификатор записи в текущей таблице
FOREIGN KEY — внешний ключ, уникальный идентификатор записи в другой таблице (таблице, связанной с текущей)
CHECK — все значения в колонке должны удовлетворять определенному условию
INDEX — быстрая запись и извлечение данных

Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.

Целостность данных

В каждой СУБД существуют следующие категории целостности данных:

целостность объекта (Entity Integrity) — в таблице не должно быть дубликатов (двух и более строк с одинаковыми значениями)
целостность домена (Domain Integrity) — фильтрация значений по типу, формату или диапазону
целостность ссылок (Referential integrity) — строки, используемые другими записями (строки, на которые в других записях имеются ссылки), не могут быть удалены
целостность, определенная пользователем (User-Defined Integrity) — дополнительные правила

Нормализация БД

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

предотвращение записи в БД лишних данных, например, хранения одинаковых данных в разных таблицах
обеспечение «оправданной» связи между данными

Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.

Синтаксис SQL

Синтаксис — это уникальный набор правил и рекомендаций. Все инструкции SQL должны начинаться с ключевого слова, такого как SELECT , INSERT , UPDATE , DELETE , ALTER , DROP , CREATE , USE , SHOW и т.п. и заканчиваться точкой с запятой ( ; ) (точка с запятой не входит в синтаксис SQL , но ее наличия, как правило, требуют консольные клиенты СУБД для обозначения окончания ввода команды). SQL не чувствителен к регистру, т.е. SELECT , select и SeLeCt являются идентичными инструкицями. Исключением из этого правила является MySQL , где учитывается регистр в названии таблицы.

Примеры синтаксиса

Типы данных

Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:

Точные числовые

Тип данных	От	До
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint	0	255
bit	0	1
decimal	-10^38 +1	10^38 -1
numeric	-10^38 +1	10^38 -1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

Приблизительные числовые

Тип данных	От	До
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

Дата и время

Тип данных	От	До
datetime	Jan 1, 1753	Dec 31, 9999
smalldatetime	Jan 1, 1900	Jun 6, 2079
date	Дата сохраняется в виде June 30, 1991
time	Время сохраняется в виде 12:30 P.M.

Строковые символьные

N	Тип данных	Описание
1	char	Строка длиной до 8,000 символов (не-юникод символы, фиксированной длины)
2	varchar	Строка длиной до 8,000 символов (не-юникод символы, переменной длины)
3	text	Не-юникод данные переменной длины, длиной до 2,147,483,647 символов

Строковые символьные (юникод)

N	Тип данных	Описание
1	nchar	Строка длиной до 4,000 символов (юникод символы, фиксированной длины)
2	nvarchar	Строка длиной до 4,000 символов (юникод символы, переменной длины)
3	ntext	Юникод данные переменной длины, длиной до 1,073,741,823 символов

Бинарные

N	Тип данных	Описание
1	binary	Данные размером до 8,000 байт (фиксированной длины)
2	varbinary	Данные размером до 8,000 байт (переменной длины)
3	image	Данные размером до 2,147,483,647 байт (переменной длины)

Смешанные

N	Тип данных	Описание
1	timestamp	Уникальные числа, обновляющиеся при каждом изменении строки
2	uniqueidentifier	Глобально-уникальный идентификатор (GUID)
3	cursor	Объект курсора
4	table	Промежуточный результат, предназначенный для дальнейшей обработки

Операторы

Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.

В дальнейших примерах мы будем исходить из предположения, что переменная a имеет значение 10 , а b — 20 .

Арифметические

Оператор	Описание	Пример
+ (сложение)	Сложение значений	a + b = 30
— (вычитание)	Вычитание правого операнда из левого	b — a = 10
* (умножение)	Умножение значений	a * b = 200
/ (деление)	Деление левого операнда на правый	b / a = 2
% (деление с остатком/по модулю)	Деление левого операнда на правый с остатком (возвращается остаток)	b % a = 0

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

Оператор	Описание	Пример
=	Определяет равенство значений	a = b -> false
!=	Определяет НЕравенство значений	a != b -> true
<>	Определяет НЕравенство значений	a <> b -> true
>	Значение левого операнда больше значения правого операнда?	a > b -> false
<	Значение левого операнда меньше значения правого операнда?	a < b -> true
>=	Значение левого операнда больше или равно значению правого операнда?	a >= b -> false
<=	Значение левого операнда меньше или равно значению правого операнда?	a <= b -> true
!<	Значение левого операнда НЕ меньше значения правого операнда?	a !< b -> false
!>	Значение левого операнда НЕ больше значения правого операнда?	a !> b -> true

Логические операторы

N	Оператор	Описание
1	ALL	Сравнивает все значения
2	AND	Объединяет условия (все условия должны совпадать)
3	ANY	Сравнивает одно значение с другим, если последнее совпадает с условием
4	BETWEEN	Проверяет вхождение значения в диапазон от минимального до максимального
5	EXISTS	Определяет наличие строки, соответствующей определенному критерию
6	IN	Выполняет поиск значения в списке значений
7	LIKE	Сравнивает значение с похожими с помощью операторов подстановки
8	NOT	Инвертирует (меняет на противоположное) смысл других логических операторов, например, NOT EXISTS, NOT IN и т.д.
9	OR	Комбинирует условия (одно из условий должно совпадать)
10	IS NULL	Определяет, является ли значение нулевым
11	UNIQUE	Определяет уникальность строки

Выражения

Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.

Базовый синтаксис выражения выглядит так:

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

Логические

Логические выражения извлекают данные на основе совпадения с единичным значением.

Предположим, что в таблице users имеются следующие записи:

userId	userName	age	city	status
1	Igor	25	Moscow	active
2	Vika	26	Ekaterinburg	inactive
3	Elena	27	Ekaterinburg	active
4	Oleg	28	Moscow	inactive

Выполняем поиск активных пользователей:

userId	userName	age	city	status
1	Igor	25	Moscow	active
3	Elena	27	Ekaterinburg	active

Числовые

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

Простой пример использования числового выражения:

addition
15

Существует несколько встроенных функций, таких как count() , sum() , avg() , min() , max() и др. для выполнения так называемых агрегирующих вычислений данных таблицы или колонки.

records
4

AVG — вычисляет среднее значение
SUM — вычисляет сумму значений
MIN — вычисляет наименьшее значение
MAX — вычисляет наибольшее значение
COUNT — вычисляет количество записей в таблице

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

CONCAT — объединение строк
LENGTH — возвращает количество символов в строке
TRIM — удаляет пробелы в начале и конце строки
SUBSTRING — извлекает подстроку из строки
REPLACE — заменяет подстроку в строке
LOWER — переводит символы строки в нижний регистр
UPPER — переводит символы строки в верхний регистр и т.д.

ROUND — округляет число
TRUNCATE — обрезает дробное число до указанного количества знаков после запятой
CEILING — возвращает наименьшее целое число, которое больше или равно текущему значению
FLOOR — возвращает наибольшее целое число, которое меньше или равно текущему значению
POWER — возводит число в указанную степень
SQRT — возвращает квадратный корень числа
RAND — генерирует случайное число с плавающей точкой в диапазоне от 0 до 1

Выражения для работы с датами

Эти выражения, как правило, возвращают текущую дату и время.

Current_Timestamp
2021-06-20 12:45:00

CURRENT_TIMESTAMP — это и выражение, и функция ( CURRENT_TIMESTAMP() ). Другая функция для получения текущей даты и времени — NOW() .

Другие функции для получения текущей даты и времени:

CURDATE / CURRENT_DATE — возвращает текущую дату
CURTIME / CURRENT_TIME — возвращает текущее время и т.д.

Функции для разбора даты и времени:

DAYOFMONTH(date) — возвращает день месяца в виде числа
DAYOFWEEK(date) — возвращает день недели в виде числа
DAYOFYEAR(date) — возвращает номер дня в году
MONTH(date) — возвращает месяц
YEAR(date) — возвращает год
LAST_DAY(date) — возвращает последний день месяца в виде даты
HOUR(time) — возвращает час
MINUTE(time) — возвращает минуты
SECOND(time) — возвращает секунды и др.

Функции для манипулирования датами:

DATE_ADD(date, interval) — выполняет сложение даты и определенного временного интервала
DATE_SUB(date, interval) — выполняет вычитание из даты определенного временного интервала
DATEDIFF(date1, date2) — возвращает разницу в днях между двумя датами
TO_DAYS(date) — возвращает количество дней с 0-го дня года
TIME_TO_SEC(time) — возвращает количество секунд с полуночи и др.

Для форматирования даты и времени используются функции DATE_FORMAT(date, format) и TIME_FORMAT(date, format) , соответственно.

Создание БД

Для создания БД используется инструкция CREATE DATABASE .

Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.

Название БД должно быть уникальным в пределах СУБД.

Создаем БД testDB :

Получаем список БД:

Database
information_schema
postgres
testDB

Удаление БД

Для удаления БД используется инструкция DROP DATABASE .

Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.

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

Проверяем, что БД удалена:

Для получения списка таблиц используется инструкция SHOW TABLES .

Database
information_schema
postgres

Выбор БД

При наличии нескольких БД, перед выполнением каких-либо операций, необходимо выбрать БД. Для этого используется инструкция USE .

Предположим, что мы не удаляли testDB . Тогда мы можем выбрать ее так:

Создание таблицы

Создание таблицы предполагает указание названия таблицы и определение колонок таблицы и их типов данных. Для создания таблицы используется инструкция CREATE TABLE .

Для создания таблицы путем копирования другой таблицы используется сочетание CREATE TABLE и SELECT .

Пример создания таблицы users , где первичным ключом являются идентификаторы пользователей, а поля для имени и возраста пользователя не могут быть нулевыми:

Проверяем, что таблица была создана:

Field	Type	Null	Key
userId	int(11)	NO	PRI
userName	varchar(20)	NO
age	int(11)	NO
city	varchar(20)	NO
status	varchar(8)	YES	NULL

Удаление таблицы

Для удаления таблицы используется инструкция DROP TABLE .

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

Удаляем таблицу users :

Теперь, если мы попытаемся получить описание users , то получим ошибку:

Добавление колонок

Для добавления в таблицу колонок используется инструкция INSERT INTO .

Названия колонок можно не указывать, однако, в этом случае значения должны перечисляться в правильном порядке.

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

Предположим, что мы не удаляли таблицу users . Заполним ее пользователями:

В таблицу можно добавлять несколько строк за один раз.

Также, как было отмечено, при добавлении строки названия полей можно опускать:

userId	userName	age	city	status
1	Igor	25	Moscow	active
2	Vika	26	Ekaterinburg	inactive
3	Elena	27	Ekaterinburg	active
4	Oleg	28	Moscow	inactive

Заполнение таблицы с помощью другой таблицы

Выборка полей

Для выборки полей из таблицы используется инструкция SELECT . Она возвращает данные в виде результирующей таблицы (результирующего набора, result-set).

Для выборки всех полей используется такой синтаксис:

Произведем выборку полей userId , userName и age из таблицы users :

userId	userName	age
1	Igor	25
2	Vika	26
3	Elena	27
4	Oleg	28

Предложение WHERE

Предложение WHERE используется для фильтрации возвращаемых данных. Оно используется совместно с SELECT , UPDATE , DELETE и другими инструкциями.

Условие (condition), которому должны удовлетворять возвращаемые записи, определяется с помощью операторов сравнения или логических операторов типа > , < , = , NOT , LIKE и т.д.

Сделаем выборку полей userId , userName и age активных пользователей:

userId	userName	age
1	Igor	25
3	Elena	27

Сделаем выборку полей userId , age и city пользователя с именем Vika .

userId	age	city
2	26	Ekaterinburg

Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки ( » ), а числа, напротив, указываются как есть.

Операторы AND и OR

Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.

AND

Возвращаемые записи должны удовлетворять всем указанным условиям.

Сделаем выборку полей userId , userName и age активных пользователей старше 26 лет:

userId	userName	AGE
3	Elena	27

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

Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:

userId	userName	age
1	Igor	25
2	Vika	26

Обновление полей

Для обновления полей используется инструкция UPDATE . SET . Эта инструкция, обычно, используется в сочетании с предложением WHERE .

Обновим возраст пользователя с именем Igor :

Если в данном случае опустить WHERE , то будет обновлен возраст всех пользователей.

Удаление записей

Для удаления записей используется инструкция DELETE . Эта инструкция также, как правило, используется в сочетании с предложением WHERE .

Удалим неактивных пользователей:

Если в данном случае опустить WHERE , то из таблицы users будут удалены все записи.

Предложения LIKE и REGEX

LIKE

Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:

проценты ( % )
нижнее подчеркивание ( _ )

% означает 0, 1 или более символов. _ означает точно 1 символ.

N	Инструкция	Результат
1	WHERE col LIKE ‘foo%’	Любые значения, начинающиеся с foo
2	WHERE col LIKE ‘%foo%’	Любые значения, содержащие foo
3	WHERE col LIKE ‘_oo%’	Любые значения, содержащие oo на второй и третьей позициях
4	WHERE col LIKE ‘f%%’	Любые значения, начинающиеся с f и состоящие как минимум из 1 символа
5	WHERE col LIKE ‘%oo’	Любые значения, оканчивающиеся на oo
6	WHERE col LIKE ‘_o%o’	Любые значения, содержащие o на второй позиции и оканчивающиеся на o
7	WHERE col LIKE ‘f_o’	Любые значения, содержащие f и o на первой и третьей позициях, соответственно, и состоящие из трех символов

Сделаем выборку неактивных пользователей:

userId	userName	age	city	status
2	Vika	26	Ekaterinburg	inactive
4	Oleg	28	Moscow	inactive

Сделаем выборку пользователей 30 лет и старше:

userId	userName	age	city	status
1	Igor	30	Moscow	active

REGEX

Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.

В регулярное выражении могут использоваться следующие специальные символы:

^ — начало строки
$ — конец строки
. — любой символ
[символы] — любой из указанных в скобках символов
[начало-конец] — любой символ из диапазона
| — разделяет шаблоны

Сделаем выборку пользователей с именами Igor и Vika :

userId	userName	age	city	status
1	Igor	30	Moscow	active
2	Vika	26	Ekaterinburg	inactive

Предложение TOP / LIMIT / ROWNUM

Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.

Сделаем выборку первых трех пользователей:

userId	userName	age	city	status
1	Igor	30	Moscow	active
2	Vika	26	Ekaterinburg	inactive
3	Elena	27	Ekaterinburg	active

Параметр offset (смещение) определяет количество пропускаемых записей. Например, так можно извлечь первых двух пользователей, начиная с третьего:

Предложения ORDER BY и GROUP BY

ORDER BY

Предложение ORDER BY используется для сортировки данных по возрастанию ( ASC ) или убыванию ( DESC ). Многие СУБД по умолчанию выполняют сортировку по возрастанию.

Обратите внимание: колонки для сортировки должны быть указаны в списке колонок для выборки.

Сделаем выборку пользователей, отсортировав их по городу и возрасту:

userId	userName	age	city	status
2	Vika	26	Ekaterinburg	inactive
3	Elena	27	Ekaterinburg	active
1	Igor	25	Moscow	active
4	Oleg	28	Moscow	inactive

Теперь выполним сортировку по убыванию:

Определим собственный порядок сортировки по убыванию:

GROUP BY

Предложение GROUP BY используется совместно с инструкцией SELECT для группировки записей. Оно указывается после WHERE и перед ORDER BY .

Сгруппируем активных пользователей по городам:

city	amount
Ekaterinburg	2
Moscow	2

Ключевое слово DISTINCT

Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).

Сделаем выборку городов проживания пользователей:

city
Ekaterinburg
Moscow

Соединения

Соединения (joins) используются для комбинации записей двух и более таблиц.

Предположим, что кроме users , у нас имеется таблица orders с заказами пользователей следующего содержания:

orderId	date	userId	amount
101	2021-06-21 00:00:00	2	3000
102	2021-06-20 00:00:00	2	1500
103	2021-06-19 00:00:00	3	2000
104	2021-06-18 00:00:00	3	1000

Сделаем выборку полей userId , userName , age и amount из наших таблиц посредством их соединения:

userId	userName	age	amount
2	Vika	26	3000
2	Vika	26	1500
3	Elena	27	2000
3	Elena	27	1000

При соединении таблиц могут использоваться такие операторы, как = , < , > , <> , <= , >= , != , BETWEEN , LIKE и NOT , однако наиболее распространенным является = .

Существуют разные типы объединений:

INNER JOIN — возвращает записи, имеющиеся в обеих таблицах
LEFT JOIN — возвращает записи из левой таблицы, даже если такие записи отсутствуют в правой таблице
RIGHT JOIN — возвращает записи из правой таблицы, даже если такие записи отсутствуют в левой таблице
FULL JOIN — возвращает все записи объединяемых таблиц
CROSS JOIN — возвращает все возможные комбинации строк обеих таблиц
SELF JOIN — используется для объединения таблицы с самой собой

Предложение UNION

Предложение/оператор UNION используется для комбинации результатов двух и более инструкций SELECT . При этом, возвращаются только уникальные записи.

В случае с UNION , каждая инструкция SELECT должна иметь:

одинаковый набор колонок для выборки
одинаковое количество выражений
одинаковые типы данных колонок и
одинаковый порядок колонок

Однако, они могут быть разной длины.

Объединим наши таблицы users и orders :

userId	userName	amount	date
1	Igor	NULL	NULL
2	Vika	3000	2021-06-21 00:00:00
2	Vika	1500	2021-06-20 00:00:00
3	Elena	2000	2021-06-19 00:00:00
3	Elena	1000	2021-06-18 00:00:00
4	Alex	NULL	NULL

Предложение UNION ALL

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

Существует еще два предложения, похожих на UNION :

INTERSECT — используется для комбинации результатов двух и более SELECT , но возвращаются только строки из первого SELECT , совпадающие со строками из второго SELECT
EXCEPT|MINUS — возвращаются только строки из первого SELECT , отсутствующие во втором SELECT

Синонимы

Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.

Синтаксис синонима таблицы:

Синтаксис синонима колонки:

Пример использования синонимов таблиц:

userId	userName	age	amount
2	Vika	26	3000
2	Vika	26	1500
3	Elena	27	2000
3	Elena	27	1000

Пример использования синонимов колонок:

user_id	user_name	user_age
1	Igor	30
3	Elena	27

Индексы

Создание индексов

Индексы — это специальные поисковые таблицы (lookup tables), которые используются движком БД в целях более быстрого извлечения данных. Проще говоря, индекс — это указатель или ссылка на данные в таблице.

Индексы ускоряют работу инструкции SELECT и предложения WHERE , но замедляют работу инструкций UPDATE и INSERT . Индексы могут создаваться и удаляться, не оказывая никакого влияния на данные.

Для создания индекса используется инструкция CREATE INDEX , позволяющая определять название индекса, индексируемые колонки и порядок индексации (по возрастанию или по убыванию).

К индексам можно применять ограничение UNIQUE для того, чтобы обеспечить их уникальность.

Синтаксис создания индекса:

Синтаксис создания индекса для одной колонки:

Синтакис создания уникальных индексов (такие индексы используются не только для повышения производительности, но и для обеспечения согласованности данных):

Синтаксис создания индексов для нескольких колонок (композиционный индекс):

Решение о создании индексов для одной или нескольких колонок следует принимать на основе того, какие колонки будут часто использоваться в запросе WHERE в качестве условия для сортировки строк.

Для ограничений PRIMARY KEY и UNIQUE автоматически создаются неявные индексы.

Удаление индексов

Для удаления индексов используется инструкция DROP INDEX :

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

К таким ситуациям относится следующее:

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

Обновление таблицы

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

Добавляем в таблицу users новую колонку — пол пользователя:

Удаляем эту колонку:

Очистка таблицы

Команда TRUNCATE TABLE используется для очистки таблицы. Ее отличие от DROP TABLE состоит в том, что сохраняется структура таблицы ( DROP TABLE полностью удаляет таблицу и все ее данные).

Очищаем таблицу users :

Проверяем, что users пустая:

Представления

Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.

Представления могут содержать все или только некоторые строки таблицы. Представление может быть создано на основе одной или нескольких таблиц (это зависит от запроса для создания представления).

Представления — это виртутальные таблицы, позволяющие делать следующее:

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

Создание представления

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

Создаем представление для имен и возраста пользователей:

Получаем данные с помощью представления:

userName	age
Igor	30
Vika	26
Elena	27
Oleg	28

WITH CHECK OPTION

WITH CHECK OPTION — это настройка инструкции CREATE VIEW . Она позволяет обеспечить соответствие всех UPDATE и INSERT условию, определенном в представлении.

Если условие не удовлетворяется, выбрасывается исключение.

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

Представление может быть обновлено при соблюдении следующих условий:

SELECT не содержит ключевого слова DISTINCT
SELECT не содержит агрегирующих функций
SELECT не содержит функций установки значений
SELECT не содержит операций установки значений
SELECT не содержит предложения ORDER BY
FROM не содержит больше одной таблицы
WHERE не содержит подзапросы
запрос не содержит GROUP BY или HAVING
вычисляемые колонки не обновляются
все ненулевые колонки из базовой таблицы включены в представление в том же порядке, в каком они указаны в запросе INSERT

Пример обновления возраста пользователя с именем Igor в представлении:

Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.

В представление могут добавляться новые строки с помощью команды INSERT . При выполнении этой команды должны соблюдаться те же правила, что и при выполнении команды UPDATE .

С помощью команды DELETE можно удалять строки из представления.

Удаляем из представления пользователя, возраст которого составляет 26 лет:

Обратите внимание: удаление строки в представлении приводит к ее удалению в базовой таблице.

Удаление представления

Для удаления представления используется инструкция DROP VIEW :

Удаляем представление usersView :

HAVING

Предложение HAVING используется для фильтрации результатов группировки. WHERE используется для применения условий к колонкам, а HAVING — к группам, созданным с помощью GROUP BY .

HAVING должно указываться после GROUP BY , но перед ORDER BY (при наличии).

Транзакции

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

Транзакция — это применение одного или более изменения к БД. Например, при создании/обновлении/удалении записи мы выполняем транзакцию. Важно контролировать выполнение таких операций в целях обеспечения согласованности данных и обработки возможных ошибок.

На практике, запросы, как правило, не отправляются в БД по одному, они группируются и выполняются как часть транзакции.

Свойства транзакции

Транзакции имеют 4 стандартных свойства (ACID):

атомарность (atomicity) — все операции транзакции должны быть успешно завершены. В противном случае, транзакция прерывается, а все изменения отменяются (происходит откат к предыдущему состоянию)
согласованность (consistency) — состояние должно изменться в полном соответствии с операциями транзакции
изоляция или автономность (isolation) — транзакции не зависят друг от друга и не оказывают друг на друга никакого влияния
долговечность (durability) — результат звершенной транзакции должен сохраняться при поломке системы

Управление транзакцией

Для управления транзакцией используются следующие команды:

BEGIN|START TRANSACTION — запуск транзакции
COMMIT — сохранение изменений
ROLLBACK — отмена изменений
SAVEPOINT — контрольная точка для отмены изменений
SET TRANSACTION — установка характеристик текущей транзакции

Команды для управления транзакцией могут использоваться только совместно с такими запросами как INSERT , UPDATE и DELETE . Они не могут использоваться во время создания и удаления таблиц, поскольку эти операции автоматически отправляются в БД.

Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:

Удаляем пользователя с именем Oleg и отменяем эту операцию:

Контрольные точки создаются с помощью такого синтаксиса:

Возврат к контрольной точке выполняется так:

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

Отменяем два последних удаления, возвращаясь к контрльной точке sp2 , созданной после первого удаления:

Делаем выборку пользователей:

userId	userName	age	city	status
1	Igor	31	Moscow	active
3	Elena	27	Ekaterinburg	active
4	Oleg	28	Moscow	inactive

Как видим, из таблицы был удален только пользователь с возрастом 26 лет.

Для удаление контрольной точки используется команда RELEASE SAVEPOINT . Естественно, после удаления контрольной точки, к ней нельзя будет вернуться с помощью ROLLBACK TO .

Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):

Временные таблицы

Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.

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

Временная таблица создается с помощью инструкции CREATE TEMPORARY TABLE , в остальном синтаксис создания таких таблиц идентичен синтаксису создания обычных таблиц.

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

Клонирование таблицы

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

В mysql , например, это можно сделать так:

вызываем команду SHOW CREATE TABLE для получения инструкции, выполненной при создании таблицы, включая индексы и прочее
меняем название таблицы и выполняем запрос. Получаем точную копию таблицы
опционально: если требуется содержимое копируемой таблицы, можно также использовать инструкции INSERT INTO или SELECT

Подзапросы

Подзапрос — это внутренний (вложенный) запрос другого запроса, встроенный (вставленный) с помощью WHERE или других инструкций.

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

Подзапросы могут использоваться в инструкциях SELECT , INSERT , UPDATE и DELETE , а также с операторами = , < , > , >= , <= , IN , BETWEEN и т.д.

Правила использования подзапросов:

они должны быть обернуты в круглые скобки
подзапрос должен содержать только одну колонку для выборки, если основной запрос не содержит несколько таких колонок, которые сравниваются в подзапросе
в подзапросе нельзя использовать команду ORDER BY , это можно сделать в основном запросе. В подзапросе для замены ORDER BY можно использовать GROUP BY
подзапросы, возвращающие несколько значений, могут использоваться только с операторами, которые работают с наборами значений, такими как IN
список SELECT не может содержать ссылки на значения, которые оцениваются (вычисляются) как BLOB , ARRAY , CLOB или NCLOB
подзапрос не может быть сразу передан в функцию для установки значений
команду BETWEEN нельзя использовать совместно с подзапросом. Тем не менее, в самомподзапросе указанную команду использовать можно

Подзапросы, обычно, используются в инструкции SELECT .

userId	userName	age	city	status
1	Igor	30	Moscow	active
3	Elena	27	Ekaterinburg	active

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

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

Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.

Последовательности

Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.

Приведенные ниже примеры рассчитаны на mysql .

Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:

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

По умолчанию значения, генерируемые с помощью AUTO_INCREMENT , начинаются с 1. Для того, чтобы установить другое начальное значение достаточно указать, например, AUTO_INCREMENT = 100 — в этом случае нумерация строк начнется со 100.