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

• Операторы SQL: Команда Команда CREATE TABLE
• Операторы SQL: Команда EXPLAIN

• Транзакция. Уровни изоляции.
• Хранимые процедуры, функции и триггеры

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

• Движок БД MySQL: MySQL Standards Compliance: выражение "SQL Standard" означает поддержку текущего(последнего) стандарта SQL, то есть SQL:2008 - шестой версии.
• Использование и настройка PostgreSQL: Appendix D. Соответствие стандартам SQL или ссылка на оригинал SQL Conformance
• Декартово произведение в SQL

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

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

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

• операторы определения данных (Data Definition Language, DDL) - язык описания схемы в ANSI, состоит из команд, которые создают объекты (таблицы, индексы, просмотры, и так далее) в базе данных (CREATE, DROP, ALTER и др.).
• операторы манипуляции данными (Data Manipulation Language, DML) - это набор команд, которые определяют, какие значения представлены в таблицах в любой момент времени (INSERT, DELETE, SELECT, UPDATE и др.).
• операторы определения доступа к данным (Data Control Language, DCL) - состоит из средств, которые определяют, разрешить ли пользователю выполнять определенные действия или нет (GRANT/REVOKE , LOCK/UNLOCK).
• операторы управления транзакциями (Transaction Control Language, TCL)

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

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

• Декларативность. С помощью SQL программист описывает только то, какие данные нужно извлечь или модифицировать. То, каким образом это сделать, решает СУБД непосредственно при обработке SQL-запроса. Однако не стоит думать, что это полностью универсальный принцип — программист описывает набор данных для выборки или модификации, однако ему при этом полезно представлять, как СУБД будет разбирать текст его запроса. Чем сложнее сконструирован запрос, тем больше он допускает вариантов написания, различных по скорости выполнения, но одинаковых по итоговому набору данных
• Сложность. Хотя SQL и задумывался как средство работы конечного пользователя, в конце концов он стал настолько сложным, что превратился в инструмент программиста.
• Процедурные расширения. Поскольку SQL не является языком программирования (то есть не предоставляет средств для автоматизации операций с данными), вводимые разными производителями расширения касались в первую очередь процедурных расширений. Это хранимые процедуры (stored procedures) и процедурные языки-«надстройки». Практически в каждой СУБД применяется свой процедурный язык. Стандарт для процедурных расширений представлен спецификацией SQL/PSM.

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

• база данных (database);
• таблица (table);
• столбец (column);
• индекс (index);
• снимок (view);
• синоним (synonym).

Первичный ключ (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

• <value expression> Выражение, которое производит значение. Оно может включать в себя или содержать <column name>.
• <table name> Имя или синоним таблицы или представления
• <alias> Временный синоним для <table name>, определённый в этой таблице и используемый только в этой команде
• <predicate> Условие, которое может быть верным или неверным для каждой строки или комбинации строк таблицы в предложении FROM.
• <column name> Имя столбца в таблице.
• <integer> - число, которое отражает порядковый номер колонки запроса (поля таблицы) в запросе.

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

• DISTINCT [ON] -ключевое слово указывает не выводить дубликаты записей столбца ipaddr. Указывать ключевое слово DISTINCT можно только один раз - оно применяется не к отдельным столбцам, перечисленным во фразе SELECT, а ко всей строке результирующей таблицы, формируемой предложением SELECT. Исключением является его использование в аргументе агрегатной функции. Ключевое слово DISTINCT нельзя использовать вместе с символом *.

  SELECT DISTINCT ipaddr FROM peers;

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

  SELECT DISTINCT ON (first_column, fourth_column) second_column,third_column
  FROM sample;

• Уточнение запросов
• SELECT список столбцов
• FROM список таблиц Выбор данных из нескольких таблиц
• WHERE – условие поиска (указывает записи, которые должны войти в результатную таблицу). Логические операторы для ключевого слова where:
  • > - больше
  • >= - больше либо равно
  • < - меньше
  • ⇐ - меньше либо равно
  • = - равно
  • <> - не равно (в некоторых базах используется знак !
  • and - логическое "И" (умножение)
  • or - логическое "или" (сложение)
  • not - логическое "не" (отрицание)
  • between - принадлежность диапазону
  • in - проверка на членство в множестве, замена логическому or. Синтаксис SQL IN
  • like - проверка на соответствие шаблону, использует два символа: % - любое количество знаков и _ любой единичный символ
  • is null - проверка на равенство значению NULL, например select * from countries where nm_ukr is NULL;

• GROUP BY – столбцы группировки (группирует записи по значениям определенных столбцов)
• HAVING – условие поиска (указывает группы записей, которые должны войти в результатную таблицу)
• ORDER BY – сортировка (столбцы сортировки и условия сортировки)
• LIMIT и OFFSET - позволяют вам получить только часть строк, сгенерированных запросом. Оператор LIMIT может также принимать два числовых аргумента, которые должны быть целыми числами. В этом случае последний аргумент задает максимальное количество возвращаемых строк, а первый сообщает Движок БД MySQL начиная с какой по счёту строки производить отсчёт

mysql> SELECT * FROM forums LIMIT 1,3;
В этом случае будут возвращены строки 2, 3 и 4

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

• Порядок выполнения операторов в SQL-выражениях:
  1. FROM – выбирает таблицу из базы данных
  2. WHERE – из таблицы выбираются записи, отвечающие условию поиска и отбрасываются все остальные
  3. CROUP BY – создаются группы записей, каждая группа соответствует какому-нибудь значению столбца группирования
  4. HAVING – обрабатывает каждую из созданных групп записей, оставляя только те из них, которые удовлетворяю условие поиска
  5. SELECT – выбирает из результатной таблицы только указанные столбцы
  6. ORDER BY – сортирует записи результатной таблицы
  7. LIMIT

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

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

• Можно форматировать вывод результата запроса на экран. Например, результат запроса создаст виртуальный столбец, каждая строка которого содержит 'IP:'. Второй запрос дополнительно присваивает новому столбцу имя IPs.

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

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

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

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

HAVING

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

• Найти дубликаты

  SELECT T1.code FROM prices AS T1
  GROUP BY T1.code
  HAVING COUNT(*)>1

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

GROUP BY с промежуточным итогом:

• Rollup analyze is supported with ROLLUP operator
• Функции ROLLUP и CUBE в предложении SELECT

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

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

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

• Если в утверждении SELECT используются и оператор ORDER BY и оператор DISTINCT, то предложение ORDER BY не может ссылаться на столбцы, не упоминаемые в списке выбора выбираемых столбцов.
• Предложение ORDER BY не может появляться в подзапросах внутри других утверждений.

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

SELECT ename, deptno, sal FROM peers ORDER BY code ASC, sale DESC;

• W3schools: SQL Joins
• Какая разница между LEFT, RIGHT, INNER, OUTER JOIN?
• MySQL 1054 Unknown column 'table1.id' in 'on clause'

• Простой JOIN (=пересечение JOIN =INNER JOIN ) - означает показывать только общие записи обоих таблиц. Каким образом записи считаются общими определяется полями в join- выражении. Например следующая запись:

  FROM t1 JOIN t2 ON t1.id = t2.id

  означает что будут показаны записи с одинаковыми id, существующие в обоих таблицах.

• LEFT JOIN (или LEFT OUTER JOIN) означает показывать все записи из левой таблицы (той, которая идет первой в join- выражении) независимо от наличия соответствующих записей в правой таблице. Если записей нет в правой таблицы устанавливается пустое значение NULL.
• RIGHT JOIN (или RIGHT OUTER JOIN) действует в противоположность LEFT JOIN - показывает все записи из правой (второй) таблицы и только совпавшие из левой (первой) таблицы.
• Другие виды JOIN объединений: MINUS - вычитание; FULL [OUTER] JOIN - полное объединение; CROSS JOIN - “Каждый с каждым” или операция декартова произведения.

Пример работает для БД Использование и настройка 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

• Использование вложенных SQL-запросов

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

• Пример. Использования подзапроса с оператором INSERT. В таблицу df_lcr_list передаются два значения(datestart и dateend), login_id ищется подзапросом по заранее известному имени пользователя, в таблицу вставляется текущее время.

  INSERT INTO df_lcr_list (datestart,dateend,login_id, date_event)
  SELECT '20120405','20120405',id, now()
  FROM users 
  WHERE login='username';

• Пример. Использования подзапроса(subquery) с оператором UPDATE. Subquery выводит множество значений.

  UPDATE accounts SET balance=0
  WHERE uid IN (SELECT id  FROM users WHERE email LIKE 'ltaixp1%');

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

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

• INSERT

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 Если в команде INSERT со строками, имеющими много значений, указывается ключевое слово IGNORE, то все строки, имеющие дублирующиеся ключи PRIMARY или UNIQUE в этой таблице, будут проигнорированы и не будут внесены. Если не указывать IGNORE, то данная операция вставки прекращается при обнаружении строки, имеющей дублирующееся значение существующего ключа

INSERT IGNORE INTO prices VALUES(DEFAULT, 1, 1111, 'Fr', 50, 50, 1, 1)

• REPLACE

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

• UPDATE

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

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);

• Использование и настройка PostgreSQL. Переименовать базу данных

  ALTER DATABASE name_db RENAME TO new_name_db;

• Изменение значения по умолчанию для колонки. Чтобы установить новое значение по умолчанию для колонки, используйте команду вида:

  ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
  OR
  ALTER TABLE nases ALTER COLUMN zone SET DEFAULT 'voip';

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

  ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

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

• Функция как значение по умолчанию для колонки. В этом случае столбец timetracking имеет тип данных timestamp и значит для нее значением по умолчанию можно задать встроенную функцию now() т.е. при добавлении новой строки в столбец будет записана текущая дата и время

  ALTER TABLE timetracking ALTER COLUMN date_wd SET DEFAULT now();

• Добавление ограничения. Чтобы добавить какое-либо ограничение, используется табличный синтаксис определения этого ограничения. Например:

  ALTER TABLE products ADD CHECK (name <> '');
  ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
  ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

  Чтобы добавить ограничение не-null, которое нельзя записать как ограничение на таблицу, используйте синтаксис:

  ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

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

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

• COUNT - функция возвращает количество строк, которые соответствует определенным критериям.,
• SUM - возвращает сумму (общую) значений в определённом столбце. Строки столбцов со значениями NULL игнорируются функцией SUM.
• AVG - среднее значение в столбце,
• MAX,
• MIN

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

SELECT Count(Books.ID) AS [Количество Книг] FROM Books;

SELECT Count(Books.ID) AS [Количество Книг] FROM Books GROUP BY [Писатель];

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

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

• Команда CREATE VIEW. Представление создается командой CREATE VIEW. Она состоит из слов CREATE VIEW (СОЗДАТЬ ПРЕДСТАВЛЕНИЕ), имени представления которое нужно создать, слова AS (КАК), и далее запроса. Создадим представление Londonstaff:

  CREATE VIEW Londonstaff 
  AS SELECT * 
  FROM Salespeople 
  WHERE city = 'London';

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

  SELECT * FROM Londonstaff;

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

  DROP VIEW Londonstaff;

• Использование и настройка PostgreSQL: LOOP, WHILE, FOR

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

• if
• case
• loop -бесконечный цикл.
• leave - аналог break
• iterate
• repeat
• while

• PostgreSQL. FOR.

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

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

• Движок БД MySQL:

  SELECT column FROM table
  ORDER BY RAND()
  LIMIT 1

• Ввести 4 случайных строки из базы данных сервера Использование и настройка PostgreSQL:

  SELECT *
  FROM aphorism
  WHERE lang='ru'
  ORDER BY RANDOM()
  LIMIT 4

• Microsoft SQL Server:

  SELECT TOP 1 column FROM table
  ORDER BY NEWID()

• Oracle:

  SELECT column FROM
  ( SELECT column FROM table
  ORDER BY dbms_random.value )
  WHERE rownum = 1

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

• В Использование и настройка PostgreSQL оператором конкатенации является двойная вертикальная черта:

  SELECT company||'('||customer_id||')' as company FROM peers

• В Движок БД MySQL используется функция CONCAT.