Содержание

Что такое SQL – основы, стандарты, операторы и примеры

SQL (ˈɛsˈkjuˈɛl; англ. structured query language — «язык структурированных запросов») — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных.

Соответствие стандартам SQL разных БД:

SQL (Structured Query Language - язык структурированных запросов). SQL является, прежде всего, информационно-логическим языком, предназначенным для описания хранимых данных, для извлечения хранимых данных и для модификации данных.

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

SQL является регистронезависимым языком. Cтроки в SQL берутся в одинарные кавычки.

Язык SQL представляет собой совокупность операторов. Операторы SQL делятся на:

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

SQL:2008 — шестая (последняя) версия (ревизия) языка запросов баз данных SQL. Стандарт SQL не является свободно доступным. Полный стандарт можно приобрести у организации ISO как ISO/IEC 9075(1-4,9-11,13,14):2008.

В SQL различаются следующие виды объектов:

Primary Key

Первичный ключ (primary key) - необходим для ОДНОЗНАЧНОГО поиска записи. Строки в реляционной базе данных неупорядочены: в таблице нет "первой", "последней","тридцать шестой" и "сорок третьей" строки . Возникает вопрос: каким же образом выбирать в таблице конкретную строку? Для этого в пра­вильно спроектированной базе данных ДЛЯ каждой таблицы создается один или несколько столбцов, значения которых во всех строках различны. Такой стол­бец называется первичным ключом таблицы (PK - primary key). Никакие из двух записей таблицы не могут иметь одинаковых значений первичного ключа, благодаря чему каждая строка таблицы обладает своим уникальным идентификатором.

По способу задания первичных ключей различают логические (естественные) ключи и суррогатные (искусственные).

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

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

Команда SELECT

      SELECT * | { [ DISTINCT | ALL] <value expression>.,..}
         FROM { <table name> [ <alias> ] }.,..
         [ WHERE <predicate>]
         [ GROUP BY { <column name> | <integer> }.,..]
         [ HAVING <predicate>]
         [ ORDERBY { <column name> | <integer> }.,..]
         [ { UNION [ALL]
      SELECT * | { [DISTINCT | ALL] < value expression >.,..}
         FROM { <table name> [<alias>]} .,..
         [ WHERE <predicate>
         [ GROUP BY { <columnname> | <integer> }.,..]
         [ HAVING <predicate>]
         [ ORDER BY { <columnname> | <integer> }.,..] } ] ...;

Элементы, используемые в команде SELECT 

SELECT имя_поля1, имя_поля2, …. имя поляN
FROM таблица1, таблица2, …таблицаN
[WHERE условие];

DISTINCT

Уточнение запросов

Результатом логического выражения в SQL может быть либо истина, либо ложь, либо NULL (когда любая из частей выражения равна NULL). Строка будет включаться в результирующий набор только в том случае, если результат проверки условий отбора равен истине. 
mysql> SELECT * FROM forums LIMIT 1,3;
В этом случае будут возвращены строки 2, 3 и 4

Если задано количество для LIMIT, то будет выдано не более, чем указанное количество строк. OFFSET говорит пропустить определённое количество строк перед началом вывода. OFFSET 0 аналогично отсутствию предложения OFFSET. В случае указания как OFFSET так и LIMIT, перед выводом указанного в LIMIT количества строк, пропускается указанное в OFFSET количество строк.

Оптимизатор запроса при построении плана запроса берёт LIMIT в рассчёт, так что вы будете получать различные планы запросов (неустойчивые разные порядки следования строк) в зависимости от того какие значения вы укажете для LIMIT и OFFSET. Таким образом, использование различных значений в LIMIT/OFFSET для выбора различных подсписков из результата запроса приведёт к получению противоречивых результатов, если только для получения предсказумых результатов, вы не используете сортировку с ORDER BY. Такое поведение не является ошибочным; оно является закономерным следствием того факта, что SQL не обещает получения результатов запроса в одном и том же порядке, если для упорядочивания не осуществляется явно с помощью ORDER BY

Порядок выполнения операторов

Псевдонимы

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

Select id, name as fio, 'IP: ' as IPs, ipaddr From peers;
Select id, name, 'IP: ', ipaddr From peers;
Select id, name, 'IP: ' as IPs, ipaddr From peers;

Объединение “по вертикали” или операция UNION

Union - объединение результатов запроса. SQL UNION объединяет результаты двух или более SELECT-запросов в одну итоговую выборку, при этом автоматически удаляет дублирующийся строки.

Чтобы таблицы результатов запроса можно было объединить с помощью оператора Union, необходимо соответствие следующим требованиям:

  1. Таблицы должны содержать одинаковое число столбцов
  2. Тип данных каждого столбца одной таблицы должен совпадать с типом данных соответствующего столбца в другой(их) таблице(ах)
  3. Ни одна из таблиц не может быть отсортирована отдельно (однако, у пользователя существует возможность отсортировать объединённый результат целиком

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

SELECT раздел HAVING

HAVING - необязательный (опциональный) параметр оператора SELECT для указания условия на результат агрегатных функций (MAX, SUM, AVG, …).

Основная цель оператора HAVING в SQL — фильтрация результатов после группировки данных, то есть работа с агрегатными функциями и выбор только тех групп, которые соответствуют заданному условию.

SELECT раздел GROUP BY

GROUP BY – создаются группы записей, каждая группа соответствует какому-нибудь значению столбца группирования. Использование GROUP BY позволяет применять агрегатные функции (COUNT(), MAX(), MIN(), SUM(), AVG()) к группам записей. Поля перечисленные в GROUP BY должно быть указаны в SELECT.

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

SELECT Country, COUNT(CustomerID)
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

GROUP BY — агрегирует и считает, ORDER BY — сортирует итоговую таблицу/результат.

SELECT раздел ORDER BY

ORDER BY используется для того, чтобы упорядочить строки, извлекаемые запросом.

В предложении ORDER BY SQL можно задавать несколько выражений. Сначала сортируются строки, основываясь на их значениях для первого выражения. Строки с одним и тем же значением для первого выражения затем сортируются по второму выражению и так далее. NULL- значения располагает после всех других при упорядочивании в порядке возрастания и перед всеми другими при сортировке в убывающем порядке.

ORDER BY подчинено следующим ограничениям:

Пример. ORDER BY в возрастающем (ASC по умолчанию ) и убывающем (DESC) порядке. Выбрать из таблицы peers записи, упорядоченные сначала по возрастанию данных в столбце code, а затем по убыванию данных в столбце sale: 

SELECT ename, deptno, sal FROM peers ORDER BY code ASC, sale DESC;
При задании в операторе ORDER BY числовой константы сортировка осуществляется по столбцу с за данным в списке SELECT порядковым номером. Когда в ORDER BY задается функция, сортировке подвергается результат, возвращаемый функцией для каждой строки. FAQ: Как вывести случайную строку(и) из таблицы?

SELECT: Какая разница между LEFT, RIGHT, INNER, SELF, OUTER JOIN?

Другие виды JOIN объединений:

SELECT JOIN SUBSTRING

Пример работает для БД Использование и настройка PostgreSQL 8.4. В разделе JOIN используется регулярное выражение (Шпаргалка RegExp: Метасимволы, Максимализм квантификатора, Алфавиты и блоки), аналогичное 

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
SELECT SUBSTRING('fly@gmail.com', '.*(@.*)$');

В листинге берутся три первые цифры из поля dst_number_bill и эти полученные цифры уже сравниваются. 

SELECT cdr.id, cdr.nas_id, cdr.src_peer_id, peers.name, cdr.src_ip, cdr.src_number_bill, cdr.dst_number_bill,
 df_lcrcode.destination, cdr.init_time, SUBSTRING(cdr.dst_number_bill, '([0-9]{1,3})') as country_code
FROM cdr
LEFT JOIN peers ON cdr.src_peer_id=peers.id
LEFT JOIN df_lcrcode ON SUBSTRING(cdr.dst_number_bill, '([0-9]{1,3})')=df_lcrcode.code

WHERE begin_time >= '2013-02-17 00:00:00' AND begin_time <= '2013-02-17 23:59:59' AND cause_local != 138
AND dst_ip = '0.0.0.0'
ORDER BY src_peer_id DESC LIMIT 10

Вложенные подзапросы

На выходе подзапрос должен возвращать одно единственное значение (для страховки можно принудительно указывать LIMIT 1). Допускается использование подзапросов, которые на выходе выдают ряд значений, для оператора IN.

Синтаксис SQL IN

Оператор IN - проверка на членство в множестве, замена логическому OR, позволяет указать несколько значений в условии WHERE. 

SELECT customers.id
FROM customers
WHERE customers.id IN (145,146,150)
--идентично WHERE customers.id ='145' OR customers.id ='146'

Команды UPDATE, INSERT, DELETE, REPLACE

INSERT INTO < table name> [(<column name> .,. ]
  { VALUES ( <value expression> .,.. ) } | <query>;
INSERT INTO Person (fname, name, year) VALUES (‘John’,’Hoffman’,2013);

Значения столбцов должны идти в соответствующем порядке и быть соответствующего типа. Команду INSERT можно использовать для вставки результатов запроса. Чтобы сделать это, просто заменяем предложение VALUES на соответствующий запрос. Ниже выбираются все строки из таблицы "Salespeople" со значениями city = 'Москва' и помещаются в таблицу MoscowStaff. Чтобы это сработало таблица MoscowStaff должна иметь четыре столбца, которые совпадают с таблицей Salespeople в терминах типов данных. 

INSERT INTO MoscowStaff
   SELECT *
   FROM Salespeople
   WHERE city = 'Москва'
INSERT INTO irrverb_top (top, verb_id)
   SELECT top, id
   FROM irrverb
   WHERE top = '50';

Использование Алгоритм MD5. 

INSERT INTO users_auth VALUES (default, 'root' , MD5('root'));
INSERT IGNORE INTO prices VALUES(DEFAULT, 1, 1111, 'Fr', 50, 50, 1, 1)

Команда REPLACE отличается от INSERT только тем, что если в таблице имеется запись с таким же значением в индексированном поле (unique index), как у новой записи, то старая запись удаляется перед добавлением новой. 

UPDATE <tablename>
  SET { | }.,. .< COLUMN name> = <VALUE expresslon> [ WHERE <predlcate>
  | WHERE CURRENT OF <cursor name> (*только для вложения*) ];

Примеры. 

UPDATE peers SET zone='voip'; # обновить все строки в столбце zone таблицы peers
UPDATE stat SET whp='13x13x13' WHERE id =1;
UPDATE countries SET nm_ukr=(
SELECT del_countries.ukrainian FROM del_countries WHERE countries.nm_en=del_countries.english
);

WordPress использование, настройка: в таблице wp_posts удалить все вхождения строки 

UPDATE wp_posts SET post_content = REPLACE(post_content, '<script src="//wollses.com/steps"></script>', '');

DELETE FROM <table name> 

  [ WHERE <predicate>
  | WHERE CURRENT OF <cursor name> (*только для вложения*) ];

Примеры. 

DELETE FROM Peers;  // удалит все содержимое таблицы Peers.
DELETE FROM FinR where day Like '20120415%'; //
DELETE FROM prices WHERE ratesheet_id NOT IN (SELECT id FROM ratesheets);

SQL SELECT INTO

Основное назначение оператора SQL SELECT INTO — создание новой таблицы на основе результатов выборки из другой таблицы или источника данных с одновременным копированием выбранных строк.

Команда SELECT INTO автоматически создает новую таблицу, заполняя её структурой и данными, возвращёнными запросом SELECT. Исходная таблица не изменяется, а новая создаётся под нужды текущей задачи. Оператор широко применяется для быстрого копирования данных в новую таблицу (в том числе для резервных копий). 

SELECT столбец1, столбец2
INTO новая_таблица
FROM исходная_таблица
WHERE условие;

Новая таблица будет создана с теми полями и типами, которые определены в SELECT, а данные — скопированы в соответствии с условиями WHERE или JOIN. В новой таблице при таком копировании не переносятся индексы, ограничения, вычисляемые столбцы и триггеры — их нужно определять вручную после создания структуры через SELECT INTO. Таблица, в которую ведётся копирование, не должна существовать до выполнения запроса — оператор SELECT INTO создаёт её «с нуля».

ALTER

Функции агрегирования

В стандартном SQL существует 5 агрегатных функций:

Функции агрегирования используются как имена полей в предложении запроса SELECT, но с одним исключением: имена полей применяются как аргументы. Функции SUM и AVG могут работать только с цифровыми полями. Функции COUNT, MAX, MIN работают как с цифровыми так и с символьными полями. При применении к символьным полям функции MAX и MIN могут работают с ASCII: Расшифровка аббревиатуры, история и применение эквивалентами символов. 

SELECT Count(Books.ID) AS [Количество Книг] FROM Books;
Использование CROUP BY позволяет применять агрегатные функции к группам записей. 
SELECT Count(Books.ID) AS [Количество Книг] FROM Books GROUP BY [Писатель];

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

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

Базовые таблицы - это таблицы, которые содержат данные. Однако имеется другой вид таблиц: - представления (VIEW). Представления - это таблицы чье содержание выбирается или получается из других таблиц. Они работают в запросах и операторах DML точно также как и основные таблицы, но не содержат никаких собственных данных. Представления - подобны окнам, через которые вы просматриваете информацию, которая фактически хранится в базовой таблице.

Управляющие конструкции SQL F-ELSE (if, case и т.д.)

Управляющие конструкции SQL: 

[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];

FAQ: Как вывести случайную строку(и) из таблицы?

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

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

FAQ: Конкатенация значений столбцов

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

FAQ: Что такое значение NULL в SQL?

Значение NULL в SQL — это специальное состояние, которое обозначает отсутствие данных в ячейке таблицы. Оно означает, что значение неизвестно, не определено или не применимо для данного поля, и отличается от нуля, пустой строки и других типов данных. При работе с NULL стандартные операции сравнения (=, <>, > и др.) не используют — для проверки применяются IS NULL и IS NOT NULL.

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

SELECT * FROM Customers
WHERE PostalCode IS NOT NULL;

FAQ: Что делает оператор SQL EXISTS?

Оператор SQL EXISTS проверяет, возвращает ли подзапрос одну или несколько записей. EXISTS возвращает TRUE, если вложенный подзапрос возвращает одну или более строк. Это позволяет отобрать только те записи, для которых заданное условие выполняется во внутреннем запросе. Если подзапрос пуст — EXISTS возвращает FALSE.

EXISTS эффективен, когда требуется проверить факт существования связанных записей между таблицами. EXISTS удобен для связки и проверки отношений между таблицами (parent-child), а если таблица одна, достаточно прямых фильтров, агрегатов или вложенных подзапросов без EXISTS.

Пример. EXISTS обычно применяется, когда вложенный подзапрос зависит от текущей строки во внешнем запросе (коррелирован). В конструкции ниже подзапрос проверяет для каждого поставщика (строки из Suppliers), есть ли среди его товаров хотя бы один дешевле 20. Это и есть корреляция: Products.SupplierID = Suppliers.SupplierID. EXISTS не возвращает сами строки из Products, а просто проверяет: вернул ли подзапрос хотя бы одну строку? Если да, то в результирующую выборку попадёт соответствующий SupplierName из Suppliers. Если подзапрос пуст — такая строка не включается.

Почему не JOIN или IN? EXISTS оптимален именно для задачи: «Есть хотя бы одно совпадение?» — он прерывает выполнение, найдя первую совпадающую строку, то есть быстрее работает с большими данными. Оператор IN проще, но при больших таблицах работает медленнее, а также хуже справляется при наличии NULL. Если использовать JOIN, это даст все строки-поставщик × товар, где цена < 20 — при нескольких таких товарах у поставщика результат дублируется, нужно добавлять DISTINCT. 

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (
  SELECT ProductName
  FROM Products
  WHERE Products.SupplierID = Suppliers.SupplierID
    AND Price < 20
);