Примеры SQL-запросов в MariaDB (MySQL). Расширенная фильтрация данных


Синтаксис:

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

Простые примеры использования select

1. Обычная выборка данных:

> SELECT * FROM users

2. Выборка данных с объединением двух таблиц (JOIN):

SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id

* в данном примере идет выборка данных с объединением таблиц users и users_rights . Объединяются они по полям user_id (в таблице users_rights) и id (users). Извлекается поле name из первой таблицы и все поля из второй.

3. Выборка с интервалом по времени и/или дате

а) известна точка начала и определенный временной интервал:

* будут выбраны данные за последний час (поле date ).

б) известны дата начала и дата окончания:

25.10.2017 и 25.11.2017 .

в) известны даты начала и окончания + время:

* выбираем данные в промежутке между 25.03.2018 0 часов 15 минут и 25.04.2018 15 часов 33 минуты и 9 секунд .

г) вытаскиваем данные за определенные месяц и год:

* извлечем данные, где в поле date присутствуют значения для апреля 2018 года.

4. Выборка максимального, минимального и среднего значения:

> SELECT max(area), min(area), avg(area) FROM country

* max — максимальное значение; min — минимальное; avg — среднее.

5. Использование длины строки:

* данный запрос должен показать всех пользователей, имя которых состоит из 5 символов.

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

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

* из таблицы 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 (с разделением времени за каждый час).

Вставка (INSERT)

Синтаксис 1:

> INSERT INTO

() VALUES ()

Синтаксис 2:

> INSERT INTO

VALUES ()

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

Примеры использования insert

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

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

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

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

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

Обновление (UPDATE)

Синтаксис:

* где table — имя таблицы; field — поле, для которого будем менять значение; value — новое значение; conditions — условие (без него делать update опасно — можно заменить все данные во всей таблице).

Обновление с использованием замены (REPLACE):

UPDATE

SET = REPLACE(, "", "");

UPDATE cities SET name = REPLACE(name, "Масква", "Москва");

Если мы хотим перестраховаться, результат замены можно сначала проверить с помощью SELECT:

Удаление (DELETE)

Синтаксис:

* где table — имя таблицы; conditions — условие (как и в случае с UPDATE, использовать DELETE без условия опасно — СУБД не запросит подтверждения, а просто удалит все данные).

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

Синтаксис:

> CREATE TABLE

( , )

> CREATE TABLE IF NOT EXISTS `users_rights` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`rights` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

* где table — имя таблицы (в примере users_rights ); field1, field2 — имя полей (в примере создается 3 поля — id, user_id, rights ); options1, options2 — параметры поля (в примере int(10) unsigned NOT NULL ); table options — общие параметры таблицы (в примере ENGINE=InnoDB DEFAULT CHARSET=utf8 ).

Использование запросов в PHP

Подключаемся к базе данных:

mysql_connect ("localhost", "login", "password") or die ("MySQL connect error");
mysql_select_db ("db_name");
mysql_query("SET NAMES "utf8"");

* где подключение выполняется к базе на локальном сервере (localhost ); учетные данные для подключения — login и password (соответственно, логин и пароль); в качестве базы используется db_name ; используемая кодировка UTF-8 .

Также можно создать постоянное подключение:

mysql_pconnect ("localhost", "login", "password") or die ("MySQL connect error");

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

Завершить подключение:

* в PHP выполняется автоматически, кроме постоянных подключений (mysql_pconnect).

Запрос к MySQL (Mariadb) в PHP делается функцией mysql_query(), а извлечение данных из запроса — mysql_fetch_array():

$result = mysql_query("SELECT * FROM users");
while ($mass = mysql_fetch_array($result)) {
echo $mass . "
";
}

* в данном примере выполнен запрос к таблице users . Результат запроса помещен в переменную $result . Далее используется цикл while , каждая итерация которого извлекает массив данных и помещает его в переменную $mass — в каждой итерации мы работаем с одной строкой базы данных.

Используемая функция mysql_fetch_array() возвращает ассоциативный массив, с которым удобно работать, но есть еще альтернатива — mysql_fetch_row(), которая возвращает обычный нумерованный массив.

Экранирование

При необходимости включения в строку запроса спецсимвола, например, %, необходимо использовать экранирование с помощью символа обратного слэша — \

Например:

* если выполнить такой запрос без экранирования, знак %, будет восприниматься как любое количество символов после 100.

На этом все. Если Вам нужно помочь с выполнением запроса, пишите мне на почту

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

    производные таблицы;

    обобщенные табличные выражения.

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

Производные таблицы

Производная таблица (derived table) - это табличное выражение, входящее в предложение FROM запроса. Производные таблицы можно применять в тех случаях, когда использование псевдонимов столбцов не представляется возможным, поскольку транслятор SQL обрабатывает другое предложение до того, как псевдоним станет известным. В примере ниже показана попытка использовать псевдоним столбца в ситуации, когда другое предложение обрабатывается до того, как станет известным псевдоним:

USE SampleDb; SELECT MONTH(EnterDate) as enter_month FROM Works_on GROUP BY enter_month;

Попытка выполнить этот запрос выдаст следующее сообщение об ошибке:

Msg 207, Level 16, State 1, Line 5 Invalid column name "enter_month". (Сообщение 207: уровень 16, состояние 1, строка 5 Недопустимое имя столбца enter_month)

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

Эту проблему можно решить, используя производную таблицу, содержащую предшествующий запрос (без предложения GROUP BY), поскольку предложение FROM исполняется перед предложением GROUP BY:

USE SampleDb; SELECT enter_month FROM (SELECT MONTH(EnterDate) as enter_month FROM Works_on) AS m GROUP BY enter_month;

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

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

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

Обобщенные табличные выражения

Обобщенным табличным выражением (OTB) (Common Table Expression - сокращенно CTE) называется именованное табличное выражение, поддерживаемое языком Transact-SQL. Обобщенные табличные выражения используются в следующих двух типах запросов:

    нерекурсивных;

    рекурсивных.

Эти два типа запросов рассматриваются в следующих далее разделах.

OTB и нерекурсивные запросы

Нерекурсивную форму OTB можно использовать в качестве альтернативы производным таблицам и представлениям. Обычно OTB определяется посредством предложения WITH и дополнительного запроса, который ссылается на имя, используемое в предложении WITH. В языке Transact-SQL значение ключевого слова WITH неоднозначно. Чтобы избежать неопределенности, инструкцию, предшествующую оператору WITH, следует завершать точкой с запятой.

USE AdventureWorks2012; SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") AND Freight > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005")/2.5;

Запрос в этом примере выбирает заказы, чьи общие суммы налогов (TotalDue) большие, чем среднее значение по всем налогам, и плата за перевозку (Freight) которых больше чем 40% среднего значения налогов. Основным свойством этого запроса является его объемистость, поскольку вложенный запрос требуется писать дважды. Одним из возможных способов уменьшить объем конструкции запроса будет создать представление, содержащее вложенный запрос. Но это решение несколько сложно, поскольку требует создания представления, а потом его удаления после окончания выполнения запроса. Лучшим подходом будет создать OTB. В примере ниже показывается использование нерекурсивного OTB, которое сокращает определение запроса, приведенного выше:

USE AdventureWorks2012; WITH price_calc(year_2005) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_2005 FROM price_calc) AND Freight > (SELECT year_2005 FROM price_calc)/2.5;

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

Параметр cte_name представляет имя OTB, которое определяет результирующую таблицу, а параметр column_list - список столбцов табличного выражения. (В примере выше OTB называется price_calc и имеет один столбец - year_2005.) Параметр inner_query представляет инструкцию SELECT, которая определяет результирующий набор соответствующего табличного выражения. После этого определенное табличное выражение можно использовать во внешнем запросе outer_query. (Внешний запрос в примере выше использует OTB price_calc и ее столбец year_2005, чтобы упростить употребляющийся дважды вложенный запрос.)

OTB и рекурсивные запросы

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

Параметры cte_name и column_list имеют такое же значение, как и в OTB для нерекурсивных запросов. Тело предложения WITH состоит из двух запросов, объединенных оператором UNION ALL . Первый запрос вызывается только один раз, и он начинает накапливать результат рекурсии. Первый операнд оператора UNION ALL не ссылается на OTB. Этот запрос называется опорным запросом или источником.

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

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

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

Для демонстрации рекурсивной формы OTB мы используем таблицу Airplane, определенную и заполненную кодом, показанным в примере ниже:

USE SampleDb; CREATE TABLE Airplane (ContainingAssembly VARCHAR(10), ContainedAssembly VARCHAR(10), QuantityContained INT, UnitCost DECIMAL (6,2)); INSERT INTO Airplane VALUES ("Самолет", "Фюзеляж",1, 10); INSERT INTO Airplane VALUES ("Самолет", "Крылья", 1, 11); INSERT INTO Airplane VALUES ("Самолет", "Хвост",1, 12); INSERT INTO Airplane VALUES ("Фюзеляж", "Салон", 1, 13); INSERT INTO Airplane VALUES ("Фюзеляж", "Кабина", 1, 14); INSERT INTO Airplane VALUES ("Фюзеляж", "Нос",1, 15); INSERT INTO Airplane VALUES ("Салон", NULL, 1,13); INSERT INTO Airplane VALUES ("Кабина", NULL, 1, 14); INSERT INTO Airplane VALUES ("Нос", NULL, 1, 15); INSERT INTO Airplane VALUES ("Крылья", NULL,2, 11); INSERT INTO Airplane VALUES ("Хвост", NULL, 1, 12);

Таблица Airplane состоит из четырех столбцов. Столбец ContainingAssembly определяет сборку, а столбец ContainedAssembly - части (одна за другой), которые составляют соответствующую сборку. На рисунке ниже приведена графическая иллюстрация возможного вида самолета и его составляющих частей:

Таблица Airplane состоит из следующих 11 строк:

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

USE SampleDb; WITH list_of_parts(assembly1, quantity, cost) AS (SELECT ContainingAssembly, QuantityContained, UnitCost FROM Airplane WHERE ContainedAssembly IS NULL UNION ALL SELECT a.ContainingAssembly, a.QuantityContained, CAST(l.quantity * l.cost AS DECIMAL(6,2)) FROM list_of_parts l, Airplane a WHERE l.assembly1 = a.ContainedAssembly) SELECT assembly1 "Деталь", quantity "Кол-во", cost "Цена" FROM list_of_parts;

Предложение WITH определяет список OTB с именем list_of_parts, состоящий из трех столбцов: assembly1, quantity и cost. Первая инструкция SELECT в примере вызывается только один раз, чтобы сохранить результаты первого шага процесса рекурсии. Инструкция SELECT в последней строке примера отображает следующий результат.

Типы данных SQL (Как в Pg , как в стандарте)

Строковые типы

Числовые типы данных

Денежные, Символьные, Двоичные типы данных

Логический тип. Перечисления

Примеры простейших SQL-запросов

Простейшие SELECT-запросы

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

SELECT [ ALL | DISTINCT ] select_item_commalist

FROM table_reference_commalist

[ WHERE conditional_expression ]

[ GROUP BY column_name_commalist ]

[ HAVING conditional_expression ]

[ ORDER BY order_item_commalist ]

В квадратных скобках указаны элементы, которые могут отсутствовать в запросе.

Ключевое слово SELECT сообщает базе данных, что данное предложение является запросом на извлечение информации.

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

Обязательным ключевым словом в предложении-запросе SELECT является слово FROM (из). За ключевым словом FROM указывается список разделенных запятыми имен таблиц, из ко-торых извлекается информация.

Например, select title, description from film

Любой SQL-запрос должен заканчиваться символом «;» (точка с запятой). Результатом данного запроса будет таблица...

Порядок следования столбцов в этой таблице соответствует порядку полей, указанному в запросе

Если необходимо вывести значения всех, столбцов таблицы, то можно вместо перечисления их имен использовать символ «*» (звездочка).

SELECT * FROM film;

В данном случае результатом выполнения запроса будет вся таблица film.

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

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

select first_name from actor;

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

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

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

select distinct first_name from actor;

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

Ключевое слово ALL (все), в отличие от DISTINCT, оказывает противоположное действие, то есть при его использовании повторяющиеся строки включаются в состав выходных данных. Режим, задаваемый ключевым словом ALL, действует по умолчанию, поэтому в реальных запросах для этих целей оно практически не используется.

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

Пример: Написать запрос, выполняющий выборку фамилий всех актеров с именем PENELOPE

select last_name from actor

where first_name="PENELOPE";

В задаваемых в предложении WHERE условиях могут использоваться операции сравнения, определяемые операторами = (равно), > (больше), < (меньше), >= (больше или равно), 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, "" , "" ) ;

DELETE FROM

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

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

ALTER

    Изменение значения по умолчанию для колонки . Чтобы установить новое значение по умолчанию для колонки, используйте команду вида: 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 - среднее значение в столбце,

Функции агрегирования используются как имена полей в предложении запроса 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 точно также как и основные таблицы, но не содержат никаких собственных данных. Представления - подобны окнам, через которые вы просматриваете информацию, которая фактически хранится в базовой таблице.

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

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

    • Перевод
    • Tutorial
    Надо “ SELECT * WHERE a=b FROM c ” или “ SELECT WHERE a=b FROM c ON * ” ?

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


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


    SELECT members.firstname || " " || members.lastname AS "Full Name" FROM borrowings INNER JOIN members ON members.memberid=borrowings.memberid INNER JOIN books ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock>(SELECT avg(stock) FROM books)) GROUP BY members.firstname, members.lastname;

    Буэ! Такое спугнет любого новичка, или даже разработчика среднего уровня, если он видит SQL впервые. Но не все так плохо.


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


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

    1. Три волшебных слова

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

    2. Наша база

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







    У нас есть книжная библиотека и люди. Также есть специальная таблица для учета выданных книг.

    • В таблице "books" хранится информация о заголовке, авторе, дате публикации и наличии книги. Все просто.
    • В таблице “members” - имена и фамилии всех записавшихся в библиотеку людей.
    • В таблице “borrowings” хранится информация о взятых из библиотеки книгах. Колонка bookid относится к идентификатору взятой книги в таблице “books”, а колонка memberid относится к соответствующему человеку из таблицы “members”. У нас также есть дата выдачи и дата, когда книгу нужно вернуть.
    3. Простой запрос

    Давайте начнем с простого запроса: нам нужны имена и идентификаторы (id) всех книг, написанных автором “Dan Brown”


    Запрос будет таким:


    SELECT bookid AS "id", title FROM books WHERE author="Dan Brown";

    А результат таким:


id title
2 The Lost Symbol
4 Inferno

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

3.1 FROM - откуда берем данные

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


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

3.2 WHERE - какие данные показываем

WHERE просто-напросто ведет себя как фильтр строк , которые мы хотим вывести. В нашем случае мы хотим видеть только те строки, где значение в колонке author - это “Dan Brown”.

3.3 SELECT - как показываем данные

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


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


4. Соединения (джойны)

Теперь мы хотим увидеть названия (не обязательно уникальные) всех книг Дэна Брауна, которые были взяты из библиотеки, и когда эти книги нужно вернуть:


SELECT books.title AS "Title", borrowings.returndate AS "Return Date" FROM borrowings JOIN books ON borrowings.bookid=books.bookid WHERE books.author="Dan Brown";

Результат:


Title Return Date
The Lost Symbol 2016-03-23 00:00:00
Inferno 2016-04-13 00:00:00
The Lost Symbol 2016-04-19 00:00:00

По большей части запрос похож на предыдущий за исключением секции FROM . Это означает, что мы запрашиваем данные из другой таблицы . Мы не обращаемся ни к таблице “books”, ни к таблице “borrowings”. Вместо этого мы обращаемся к новой таблице , которая создалась соединением этих двух таблиц.


borrowings JOIN books ON borrowings.bookid=books.bookid - это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц "books" и "borrowings", в которых значения bookid совпадают. Результатом такого слияния будет:



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


Давайте попробуем чуть более сложное соединение с двумя таблицами.


Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”.


На этот раз давайте пойдем снизу вверх:


Шаг Step 1 - откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так:


borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid

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


Шаг 2 - какие данные показываем? Нас интересуют только те данные, где автор книги - “Dan Brown”


WHERE books.author="Dan Brown"

Шаг 3 - как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги:


SELECT members.firstname AS "First Name", members.lastname AS "Last Name"

Супер! Осталось лишь объединить три составные части и сделать нужный нам запрос:


SELECT members.firstname AS "First Name", members.lastname AS "Last Name" FROM borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid WHERE books.author="Dan Brown";

Что даст нам:


First Name Last Name
Mike Willis
Ellen Horton
Ellen Horton

Отлично! Но имена повторяются (они не уникальны). Мы скоро это исправим.

5. Агрегирование

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


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


SELECT members.firstname AS "First Name", members.lastname AS "Last Name", count(*) AS "Number of books borrowed" FROM borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid WHERE books.author="Dan Brown" GROUP BY members.firstname, members.lastname;

Что даст нам нужный результат:


First Name Last Name Number of books borrowed
Mike Willis 1
Ellen Horton 2

Почти все агрегации идут вместе с выражением GROUP BY . Эта штука превращает таблицу, которую можно было бы получить запросом, в группы таблиц. Каждая группа соответствует уникальному значению (или группе значений) колонки, которую мы указали в GROUP BY . В нашем примере мы конвертируем результат из прошлого упражнения в группу строк. Мы также проводим агрегирование с count , которая конвертирует несколько строк в целое значение (в нашем случае это количество строк). Потом это значение приписывается каждой группе.


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



Можно прийти к логическому выводу, что все поля в результате должны быть или указаны в GROUP BY , или по ним должно производиться агрегирование. Потому что все другие поля могут отличаться друг от друга в разных строках, и если выбирать их SELECT "ом, то непонятно, какие из возможных значений нужно брать.


В примере выше функция count обрабатывала все строки (так как мы считали количество строк). Другие функции вроде sum или max обрабатывают только указанные строки. Например, если мы хотим узнать количество книг, написанных каждым автором, то нужен такой запрос:


SELECT author, sum(stock) FROM books GROUP BY author;

Результат:


author sum
Robin Sharma 4
Dan Brown 6
John Green 3
Amish Tripathi 2

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

6. Подзапросы


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

6.1 Двумерная таблица

Есть запросы, которые возвращают несколько колонок. Хороший пример это запрос из прошлого упражнения по агрегированию. Будучи подзапросом, он просто вернет еще одну таблицу, по которой можно делать новые запросы. Продолжая предыдущее упражнение, если мы хотим узнать количество книг, написанных автором “Robin Sharma”, то один из возможных способов - использовать подзапросы:


SELECT * FROM (SELECT author, sum(stock) FROM books GROUP BY author) AS results WHERE author="Robin Sharma";

Результат:



Можно записать как: ["Robin Sharma", "Dan Brown"]


2. Теперь используем этот результат в новом запросе:


SELECT title, bookid FROM books WHERE author IN (SELECT author FROM (SELECT author, sum(stock) FROM books GROUP BY author) AS results WHERE sum > 3);

Результат:


title bookid
The Lost Symbol 2
Who Will Cry When You Die? 3
Inferno 4

Это то же самое, что:


SELECT title, bookid FROM books WHERE author IN ("Robin Sharma", "Dan Brown"); 6.3 Отдельные значения

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


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


Среднее количество можно получить таким образом:


select avg(stock) from books;

Что дает нам:


7. Операции записи

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

7.1 Update

Синтаксис запроса UPDATE семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок SELECT "ом, мы задаем знаения SET "ом.


Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:


UPDATE books SET stock=0 WHERE author="Dan Brown";

WHERE делает то же самое, что раньше: выбирает строки. Вместо SELECT , который использовался при чтении, мы теперь используем SET . Однако, теперь нужно указать не только имя колонки, но и новое значение для этой колонки в выбранных строках.


7.2 Delete

Запрос DELETE это просто запрос SELECT или UPDATE без названий колонок. Серьезно. Как и в случае с SELECT и UPDATE , блок WHERE остается таким же: он выбирает строки, которые нужно удалить. Операция удаления уничтожает всю строку, так что не имеет смысла указывать отдельные колонки. Так что, если мы решим не обнулять количество книг Дэна Брауна, а вообще удалить все записи, то можно сделать такой запрос:


DELETE FROM books WHERE author="Dan Brown"; 7.3 Insert

Пожалуй, единственное, что отличается от других типов запросов, это INSERT . Формат такой:


INSERT INTO x (a,b,c) VALUES (x, y, z);

Где a , b , c это названия колонок, а x , y и z это значения, которые нужно вставить в эти колонки, в том же порядке. Вот, в принципе, и все.


Взглянем на конкретный пример. Вот запрос с INSERT , который заполняет всю таблицу "books":


INSERT INTO books (bookid,title,author,published,stock) VALUES (1,"Scion of Ikshvaku","Amish Tripathi","06-22-2015",2), (2,"The Lost Symbol","Dan Brown","07-22-2010",3), (3,"Who Will Cry When You Die?","Robin Sharma","06-15-2006",4), (4,"Inferno","Dan Brown","05-05-2014",3), (5,"The Fault in our Stars","John Green","01-03-2015",3); 8. Проверка

Мы подошли к концу, предлагаю небольшой тест. Посмотрите на тот запрос в самом начале статьи. Можете разобраться в нем? Попробуйте разбить его на секции SELECT , FROM , WHERE , GROUP BY , и рассмотреть отдельные компоненты подзапросов.


Вот он в более удобном для чтения виде:


SELECT members.firstname || " " || members.lastname AS "Full Name" FROM borrowings INNER JOIN members ON members.memberid=borrowings.memberid INNER JOIN books ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock> (SELECT avg(stock) FROM books)) GROUP BY members.firstname, members.lastname;

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


Результат:


Full Name
Lida Tyler

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

Теги: Добавить метки


2024 © gtavrl.ru.