Содержание

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 - объединение результатов запроса. Чтобы таблицы результатов запроса можно было объединить с помощью оператора Union, необходимо соответствие следующим требованиям:

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

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

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

SELECT раздел HAVING

HAVING

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

SELECT раздел GROUP BY

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

GROUP 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 раздел 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.

Операторы EXISTS, ANY(ANY и SOME абсолютно идентичны и являются взаимозаменяемыми),ALL умеют работать с множеством значений.

Синтаксис 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);

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 (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: Конкатенация значений столбцов

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