Вложенные запросы в t-sql

SQL Справочник

SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник

Зачем мне изучать SQL, если я занимаюсь данными?

SQL весьма далек от забвения – напротив, это один из самых востребованных навыков, который вы можете найти в описаниях вакансий в области обработки больших данных, независимо от того, хотите ли вы устроиться на должность аналитика данных, инженера по данным, научного сотрудника в области данных или в качестве еще кого-либо. Этот факт подтверждается результатами исследования рынка труда, проведенным O’Reilly в 2016 году: 70% респондентов, участвовавших в опросе, подтвердили, что в своей профессиональной деятельности они используют SQL. Более того, в обзоре результатов этого исследования язык SQL занимает более высокую позицию, по сравнению с другими языками программирования, такими как R (57%) и Python (54%).

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

Неплохо для языка, который был разработан еще в начале 1970-х годов прошлого века, не правда ли?

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

Для объяснения этого факта можно найти несколько причин: во-первых, компании в основном хранят данные в реляционных системах управления базами данных (RDBMS) или в системах управления реляционными потоками данных (RDSMS), и SQL требуется для доступа к таким хранимым данным. SQL – это универсальный язык данных: он дает вам возможность взаимодействовать практически с любой базой данных или даже создавать свои локальные базы данных!

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

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

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

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

• Он довольно прост в изучении, даже для новичков. Рост знаний и навыков происходит довольно быстро, и вы в кратчайшие сроки научитесь писать запросы.
• Изучение SQL подчиняется принципу «однажды изученное может применяться повсюду», поэтому это отличное вложение вашего времени и сил!
• Это отличное дополнение к языкам программирования. В некоторых случаях писать запрос даже предпочтительнее, чем писать код, потому что он более эффективен!
• …

И чего же ты все еще ждешь?

Оптимизация

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

В T-SQL, так же, как и в SQL, оптимизация означает выбор лучшего способа получения данных среди всех возможных альтернатив. Предположим, у нас есть простой запрос с объединением 2х таблиц, у каждой таблицы есть по одному некластерному индексу — и вот у нас уже 4 способа выполнить запрос! Количество возможных вариантов растёт экспоненциально вместе с ростом сложности запросов и количеством способов обращения к данным (т.е., с числом индексов в нужных таблицах). Добавьте к этому, что соединение (JOIN) может быть выполнено при помощи разных стратегий (в общем случае: вложенные циклы, соединение хэшированием или объединением). Становится понятно, почему оптимизация настолько важна в работе SQL Server.

SQL Server использует оптимизатор, основанный на стоимостной оценке. Это значит, что он перебирает все (ну или большинство) возможных вариантов выполнения и пытается оценить возможную стоимость каждой альтернативы. Выбран будет вариант с наименьшей стоимостью.

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

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

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

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

MySQL

Существует множество различных реляционных СУБД. Самая известная СУБД — это Microsoft Access, входящая в состав офисного пакета приложений Microsoft Office.
Нет никаких препятствий для использования в качестве СУБД MS Access, но для задач веб-программирования гораздо лучше подходит альтернативная программа — MySQL.
В отличие от MS Access, MySQL абсолютно бесплатна, может работать на серверах с Linux, обладает гораздо большей производительностью и безопасностью, что делает её идеальным кандидатом на роль базы данных в веб-разработке.
Подавляющее большинство сайтов и приложений на PHP используют в качестве СУБД именно MySQL.

SQL предоставляет возможность выполнять

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

Реляционная база данных – это связанная информация, сохраняемая в двумерных таблицах со строками и столбцами. Строки обычно называются записями, столбцы ­ полями.

В настоящей разработке будут рассмотрены вопросы, связанные с командами языка SQL, позволяющими производить выборку информации из базы данных, изменение информации в таблицах базы данных, но не в их структуре. Эта группа команд языка SQL называется DML – Data Manipulation Language (язык обработки данных).

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

В таблице ТОВАРЫ (Goods), содержится 4 поля, с информацией о товаре:

GNum – номер товара;
GName – наименование товара;
GTypeNum – номер категории товара, к которой он принадлежит;
GPrice – цена товара

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

Goods
GNum	Gname	GTypeNum	GPrice
1	Сахар	1	12
2	Мука	1	9
3	Мыло	3	5
4	Пиво	2	9
5	Фен «Philips»	4	130
6	Печенье	1	15
7	Консервы	1	17
8	Вино	2	35

Таблица КАТЕГОРИЯ ТОВАРА (GoodsTypes) содержит 2 поля:

GTNum – номер категории;
GTName – наименование категории;

GoodsTypes
GTNum	GTName
1	Продовольственные
2	Алкогольные
3	Хозтовары
4	Бытовая техника

В таблице ПОКУПАТЕЛИ (Buyers), содержится 3 поля, с информацией о покупателе:

BNum – номер покупателя;
BName – наименование фирмы;
BTown – номер города, в котором зарегистрирована фирма;

Buyers
BNum	BName	BTown
1	Дельта	1
2	Золотой колос
3	Дельфин	2
4	Элита	2
5	Москва	3

В таблице ГОРОДА (Towns), содержится 2 поля, с информацией о городах:

TNum – номер города;
TName – наименование города;

Towns
TNum	TName
1	Махачкала
2	Новосибирск
3	Москва

В таблице ПРОДАЖ (Sells), содержится 6 полей, с информацией о продаже товаров:

SNum – номер сделки (продажи) товара;
GNum – номер проданного товара;
BNum – номер покупателя;
SDate – дата продажи товара;
SQnty – количество проданного товара;
SSum – сумма на которую продан товар.

Sells
SNum	GNum	BNum	SDate	SQnty	SSum
1	2	5	20.01.01	50	6700
20	5	2	05.05.01	100	60000
5	1	2		320	192000
7	2	3	05.05.01	230	1000
15	3	1	16.08.01	89	100000

Запрос для выборки данных (select)

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

Строится данный запрос следующим образом:

select col1, col2, ..., colN from table where clause;

где select — это начало запроса, col1, col2, …, colN — это перечисление колонок, которые необходимо отобразить (важно знать, что если требуются все колонки таблицы, то вместо перечисления можно указывать просто символ звездочки *, что очень удобно, особенно, если структура таблицы постоянно корректируется или же заранее не известны все доступные колонки, кроме тех, что в фильтре), from — обозначает, что далее будет указано имя таблицы, where — обозначает, что далее будет указан фильтр, clause — сам фильтр (аналогично delete и update). После sql-запроса ставится точка с запятой

Примечание: Важно отметить, что часть where с clause являются необязательными. То есть, если фильтр не требуется, то их можно не писать

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

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

select Name, Age from somedata where Date < '01.01.1999';

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

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

Скажите нет грубой силе

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

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

Избыточные условия для объединений

Когда вы добавляете слишком много условий для объединений, вы, по сути, предписываете SQL выбрать определенный путь. Может быть, однако, что этот путь не всегда более эффективен.

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

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

SELECT state, COUNT(*) FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY state
SELECT state, COUNT(*) FROM Drivers GROUP BY state HAVING state IN ('GA', 'TX') ORDER BY state

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

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

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

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

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

Этот список был всего лишь небольшим обзором некоторых анти-шаблонов и советов, которые, надеюсь, помогут новичкам. Если вы хотите получить представление о том, что более старшие разработчики считают наиболее частыми антишаблонами, ознакомьтесь с этим обсуждением.

Где используется SQL

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

• DDL – как язык определения данных (так говорит Google), давая независимое создание информационных хранилищ;
• DML – управление, помогающее вести поддержку существующих табличек;
• DCL – контроль, используемый для защиты от неправильного использования и повреждений;
• SSO – клиент-сервер, давая возможность проверки подлинности пользователя в нескольких веб-утилитах в рамках одного сеанса.

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

Внимание: SQL-данные используются практически всеми реляционными базами. В Google и других поисковиках можно отыскать не только туториалы и полезную литературу по работе с рассмотренным языком и запросами

Здесь также предлагаются курсы для обучения программированию и БД. В Google удастся обнаружить не только очные курсы, но и дистанционные. Они помогают быстрее освоиться в выбранной области не только продвинутым программерам, но и новичкам. По выпуску выдают специальные сертификаты

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

Инструкция SHOW

Как узнать содержимое той или иной базы данных? Или содержимое конкретной таблицы? Для этого используется инструкция .

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

SHOW DATABASES

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

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

Самый простой способ создания сервера MySQL — установить бесплатные инструменты, такие как XAMPP или WAMP, которые включают в себя все необходимые утилиты. Но так же можно установить всё вручную.

SHOW TABLES

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

SHOW COLUMNS

отображает информацию о столбцах в данной таблице.

В следующем примере показаны столбцы в таблице wp_comments:

отображает следующие значения для каждого столбца таблицы:

• Field: имя столбца
• Type: тип данных столбца
• Key: указывает, индексирован ли столбец
• Default: значение по умолчанию для столбца
• Extra: может содержать любую дополнительную информацию о данном столбце

Столбцы для таблицы wp_comments были автоматически созданы WordPress, как и вся база данных моего сайта.

Примеры более сложных запросов или используемых редко

1. Объединение с группировкой выбранных данных в одну строку (GROUP_CONCAT)

> SELECT GROUP_CONCAT(DISTINCT CONVERT(id USING  ‘utf8’)  SEPARATOR ‘, ‘) as ids FROM users

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

2. Группировка данных по двум и более полям

> SELECT * FROM users GROUP BY CONCAT(title, ‘::’, birth)

* итого, в данном примере мы сделаем выгрузку данных из таблицы users и сгруппируем их по полям title и birth. Перед группировкой мы делаем объединение полей в одну строку с разделителем ::.

3. Объединение результатов из двух таблиц (UNION)

> (SELECT id, fio, address, ‘Пользователи’ as type FROM users)
UNION 
(SELECT id, fio, address, ‘Покупатели’ as type FROM customers)

* в данном примере идет выборка данных из таблиц users и customers.

4. Выборка средних значений, сгруппированных за каждый час

SELECT avg(temperature), DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’) as hour_datetime FROM archive GROUP BY DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’)

* здесь мы извлекаем среднее значение поля temperature из таблицы archive и группируем по полю datetimeupdate (с разделением времени за каждый час).

5. Использование операторов IF и CASE

Данные операторы позволяют определять исход запроса исходя из условия.

а) выбрать пол мужской или женский:

SELECT IF(sex = ‘m’, ‘мужчина’, ‘женщина’) as sex FROM people

* в данном примере мы возвращаем слово «мужчина», если поле sex равно ‘m’, иначе — «женщина».

б) заменяем идентификатор времени года более понятным человеку значением:

SELECT CASE season_id WHEN 1 THEN ‘зима’ WHEN 2 THEN ‘весна’ WHEN 3 THEN ‘лето’ WHEN 4 THEN ‘осень’ ELSE ‘неправильный идентификатор времени года’ END as season FROM ` seasons

* в данном примере мы используем оператор CASE. Если 1, то вернем слово «зима», если 2 — «весна» и так далее.

Обработка и выполнение SQL-запросов

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

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

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

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

Теперь вы, вероятно, задаетесь вопросом, что считается «хорошим планом запроса».

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

Затем выполняется выбранный план запроса, они оцениваются механизмом выполнения системы и после этого возвращаются результаты вашего запроса.

Таким образом эту последовательность можно записать в виде следующего списка шагов (см. картинку с английской терминологией ниже):

• SQL-выражение
• Синтаксической разбор
• Компоновка
• Оптимизация запроса
• Выполнение запроса

• Результаты запроса

Из предыдущего раздела может быть уже понятно, что принцип обработки «что на входе, то и на выходе» (Garbage In, Garbage Out (GIGO)) естественным образом распространяется на обработку и выполнение запроса: тот, кто формулирует запрос, также держит в руках и ключи от производительности SQL-запроса. Если оптимизатор получает плохо сформулированный запрос, он может только сделать так …

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

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

• Оператор  ;
• Ключевые слова    или ,
• Оператор  .

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

Тем не менее, вы также должны понимать, что производительность – это то, что понимается в определенном контексте: просто так сказать, что эти причины и ключевые слова плохи, не есть способ понимания производительности SQL запроса. То есть наличие предложения  или  в вашем запросе не обязательно означает, что это плохой запрос …

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

Рабочие потоки (Workers)

Рабочие потоки – это потоки (нити, threads), относящиеся к SQL Server. Несколько таких потоков создаётся сразу при старте службы сервера, дальше, по необходимости, могут быть созданы дополнительные — пока общее число рабочих потоков не достигнет значения параметра max worker threads.

Только рабочие потоки могут выполнять код. Рабочие потоки ждут, пока «ожидающая» (PENDING) задача не станет доступна, затем каждый рабочий поток забирает ровно одну задачу и выполняет её. Рабочий поток занят (работает) пока задача не будет выполнена полностью. Ожидающие (pending) задачи, для которых не нашлось свободного рабочего потока вынуждены висеть, пока какая-нибудь из выполняемых задач не завершится – и не освободится рабочий поток, который сможет взять новую задачу.

Если рабочий поток выбирает пакетный запрос (batch), он один будет выполнять весь пакет, т.е. каждую его команду. Тут должно стать понятно, могут ли разные команды в одном скрипте (=> задаче => рабочем процессе) выполняться параллельно: нет, не могут. Поскольку они все выполняются в одном потоке, каждый запрос из пакета не начнёт выполняться, пока не закончится предыдущий.

Для команд, которые используют внутренний параллелизм (помним про DOP, здесь он будет >1) и создают подзадачи, каждая такая подзадача пройдёт ровно такой же цикл: будет создана со статусом «ожидание» (PENDING), её должен будет подхватить и выполнить рабочий поток (не тот поток, который выполняет «родительский» пакетный запрос! – тот поток по определению уже занят).

Список рабочих потоков можно увидеть в представлении sys.dm_os_workers.

Подзапросы SQL с инструкцией DELETE

Ниже приводится синтаксис и пример использования SQL подзапросов с инструкцией DELETE.

Синтаксис:

DELETE FROM TABLE_NAME

(SELECT COLUMN_NAME    
FROM TABLE_NAME)    

Если нужно удалить заказы из таблицы «neworder», для которых advance_amount меньше максимального значения advance_amount из таблицы «orders», можно использовать следующий код SQL:

Пример таблицы: neworder

ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012

Код SQL:

DELETE FROM neworder
WHERE advance_amount<
(SELECT MAX(advance_amount) FROM orders);

Результат:

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

Вадим Дворниковавтор-переводчик статьи «SQL Subqueries»

Вставка (INSERT)

Синтаксис 1:

> INSERT INTO <table> (<fields>) VALUES (<values>)

Синтаксис 2:

> INSERT INTO <table> VALUES (<values>)

* где table — имя таблицы, в которую заносим данные; fields — перечисление полей через запятую; values — перечисление значений через запятую.
* первый вариант позволит сделать вставку только по перечисленным полям — остальные получат значения по умолчанию. Второй вариант потребует вставки для всех полей.

1. Вставка нескольких строк одним запросом:

> INSERT INTO cities (`name`, `country`) VALUES (‘Москва’, ‘Россия’), (‘Париж’, ‘Франция’), (‘Фунафути’ ,’Тувалу’);

* в данном примере мы одним SQL-запросом добавим 3 записи.

2. Вставка из другой таблицы (копирование строк, INSERT + SELECT):

Синтаксис при копировании строк из одной таблицы в другую выглядит так:

> INSERT INTO <table1> SELECT * FROM <table2> WHERE <условие для select>;

* где table1 — куда копируем; table2 — откуда копируем.

а) скопировать все без разбора:

> INSERT INTO cities-new SELECT * FROM cities;

* в данном примере мы скопируем все строки из таблицы cities в таблицу cities-new.

б) скопировать определенные столбцы строк с условием:

> INSERT INTO cities-new (`name`, `country`) SELECT `name`, `country` FROM cities WHERE name LIKE ‘М%’;

* извлекаем все записи из таблицы cities, названия которых начинаются на «М» и заносим в таблицу cities-new.

в) копирование с обновлением повторяющихся ключей.

Если копировать таблицы несколько раз, то может возникнуть проблема повторения первичного ключа. В базах данных значения таких ключей должны быть уникальными и при попытке вставить повтор мы получим ошибку «Duplicate entry ‘xxx’ for key ‘PRIMARY’». Чтобы новые строки вставить, а повторяющиеся обновить (если есть изменения), используем «ON DUPLICATE KEY UPDATE»:

> INSERT INTO cities-new SELECT * FROM cities ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `country`=VALUES(`country`);

* в данном примере, как и в предыдущих, мы копируем данные из таблицы cities в таблицу cities-new. Но при совпадении значений первичного ключа мы будем обновлять поля name и country.

Сравнение данных за две даты

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

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

Структура таблицы products

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ShopID` int(11) NOT NULL,
  `Name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf-8 AUTO_INCREMENT=10 ;

Структура таблицы statistics

CREATE TABLE IF NOT EXISTS `statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ProductID` bigint(20) NOT NULL,
  `Orders` int(11) NOT NULL,
  `Date` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`),
  KEY `ProductID` (`ProductID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf-8 AUTO_INCREMENT=20 ;

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

SELECT stat1.Name, stat1.Orders, stat1.Date, stat2.Orders, stat2.Date FROM 
(SELECT statistics.ProductID, products.Name, statistics.Orders, statistics.Date 
FROM products JOIN statistics ON products.id = statistics.ProductID WHERE 
DATE(statistics.date) = '2014-09-04') AS stat1 JOIN (SELECT statistics.ProductID, 
statistics.Orders, statistics.Date FROM statistics WHERE DATE(statistics.date) = 
'2014-09-12') AS stat2 ON stat1.ProductID = stat2.ProductID

В итоге имеем такой результат:

+------------------------+----------+------------+----------+------------+
| Name                   | Orders1  | Date1      | Orders2  | Date2      |
+------------------------+----------+------------+----------+------------+
| Процессоры Pentium II  |        1 | 2014-09-04 |        1 | 2014-09-12 |
| Процессоры Pentium III |        1 | 2014-09-04 |       10 | 2014-09-12 |
| Оптическая мышь Atech  |       10 | 2014-09-04 |        3 | 2014-09-12 |
| DVD-R                  |        2 | 2014-09-04 |        5 | 2014-09-12 |
| DVD-RW                 |       22 | 2014-09-04 |       18 | 2014-09-12 |
| Клавиатура MS 101      |        5 | 2014-09-04 |        1 | 2014-09-12 |
| SDRAM II               |       26 | 2014-09-04 |       12 | 2014-09-12 |
| Flash RAM 8Gb          |        8 | 2014-09-04 |        7 | 2014-09-12 |
| Flash RAM 4Gb          |       18 | 2014-09-04 |       30 | 2014-09-12 |
+------------------------+----------+------------+----------+------------+