Уровень 19
Принципы SQL запросов
Ты со мной или с ними? Пришло время выбирать, на чьей ты стороне!
AND OR
Не путайт AND с OR!
Если истинными должны быть ВСЕ условия, используйте AND.
Если истинными должны быть ХОТЯ БЫ ОДНО из условий, используйте OR.
Так и не разобрались на чьей вы стороне? Тогда листайте уровнь вниз, и мы поможем вам заглянуть внутрь, чтобы понять свою суть.
Конечно, связок может быть сколько угодно. Также в одном условии AND может использоваться вместе с OR.
Оператор OR действительно полезен, но я не понимаю, почему мы не воспользовались AND, в предыдущем примере?
Можно ли использовать более одной связки AND или OR в одном условии WHERE?
Чем AND отличается от OR
Следующие примеры демонстрируют возможные комбинации двух условий, объединенных связками AND и OR.
LIKE: слово для экономии времени
В калифорни слишком много городов. Если Грег попытается перечислить их все в запросе, объединяя связкой OR, это займет у него слишкои иного времени. К счастью, существует полезное ключевое слово LIKE, которое в сочетании со специальными символами ищет часть текстовой строки и возвращает совпадения.
Грег может использовать LIKE следующим образом:
SELECT * FROM my_contacts
WHERE location LIKE`%CA`;
Специальные символы
LIKE обычно используется в сочетаниями с двумя специальными символами - "заместителями", которые представляют фактическое содержание строки. Специальные символы, словно джокер в карточных играх, равны любому символу(или последовательности символов) строк.
МОЗГОВОЙ
ШТУРМ
Какие еще специальные символы встречались Вам в этой уровне?
Я LIKE это
LIKE используется со специальными символами. Первый - знак % - обозначает любое количество произвольных символов.
%
SELECT first_name FROM
my_contacts WHERE first_name
LIKE `%им`;
Второй специальный символ, так часто встречающийся в компании LIKE, - знак подчеркивания ( _ ) - представляет ровно один произвольный символ.
__
SELECT first_name FROM my_contacts WHERE first_name LIKE `_им`;
Проверка диапазонов с использованием AND и операторов сравнения
Владелец бара хочет отобрать напитки, калорийность которых находится в заданном диапазоне. Как составить запрос в диапазоне от 30 до 60 включительно?
SELECT drink_name FROM drink_info
WHERE
calories >= 30
AND
calories <= 60;
Только МЕЖДУ нами ... Есть и другой способ
Также для проверки вхождения значения в диапазон можно воспользоваться ключевым словом BETWEEN. Такая форма записи короче предыдущего запроса, но возвращает те же результаты. Обратите внимание: BETWEEN включает границы диапазона (30 и 60). Конструкция BETWEEN эквивалентна использованию операторов <= и =>, но не < и >.
SELECT drink_name FROM drink_info
WHERE calories BETWEEN 30 AND 60
Условие IN
Аманда, подруга Грега использует список контактов Грега для поиска парней. Она уже побывала на нескольких свиданиях, завела собственную таблицу со своими впечатлениями.
Аманда назвала свою таблицу black_book. Она хочет получить список удачных свиданий, поэтому отбирает значения к положительным оценкам.
Вместо того чтобы строить длинные цепочки OR, мы можем упростить запрос при помощи ключевого слова IN. После IN следует набор значений в круглых скобках. Если значение столбца совпадает с одним из значений набора, то запись или задонное подмножество столбцов включается в результат запроса.
Ключевые слова NOT IN
И конечно, Аманда хочет знать, кто из ее знакомых получил плохие оценки. Если они позвонят, у нее обнаружатся какие-нибудь неотложные дела.
Чтобы получить имена знакомых, получивших низкие оценки, поставьте перед IN ключевое слово NOT. С конструкцией NOT IN в выборку включаются записи, у которых значение столбца не входит в заданный набор
МОЗГОВОЙ
ШТУРМ
Когда NOT IN удобнее IN?
Упорядочение результатов выборки
Дороги друзья, перед нами стоит непростая задача. Необходимо упорядочить нашу фильмотеку. На каждом из 3000 с лишнем фильмов необходимо разместить наклейку с обозначением категории, после чего фильмы расставляются на полки в алфавитном порядке.
Нам нужен список фильмов, в котором внутри каждой категории названия упорядочены по алфавита. Вы уже умеете пользоваться командой SELECT, можете легко получить список фильмов заданной категории и даже выполнить выборку по первой букве названия и по категории.
Вах! Но для упорядочивания такого большого списка фильмов придется выполнить огромное количество команд SELECT. Мои старые платы не выдержат такого! Это ты новенький и красивенький можешь, делать миллион вычислений в секунду, а я от такого просто перегреюсь. Вот лишь небольшая часть того безобразия, что нам прийдется выполнить:
МОЗГОВОЙ
ШТУРМ
Как вы думаете, где в этом списке будут находиться фильмы, названия которых начинаются с цифры или неалфавитного символа (например, с восклицательного знака)? Не знаешь? Погугли! Самое время начать пользоваться поисковиком на полную!
ORDER BY
Хотите упорядочить результаты своего запроса? Это совсем несложно - включите в команду SELECT ключевые слова ORDER BY и имя столбца таблицы.
Хотите упорядочить результаты своего запроса? Это совсем несложно - включите в команду SELECT ключевые слова ORDER BY и имя столбца таблицы.
Время пошевелить мозгами ...
Упорядочение по одному столбцу
Если включить в запрос условие ORDER BY title, нам уже не придется отбирать названия, начинающиеся с определенной буквы - запрос сам вернет данные, выстроенные в алфавитном порядке по значению столбца title.
Для этого нужно лишь исключить из запроса условие title LIKE, а ORDER BY title сделает все остальное.
ORDER BY позволяет отсортировать данные любого столбца.
Время пошевелить мозгами ...
Давайте создадим простую таблицу, состоящую из единственного столбца CHAR (1) с именем «test_chars».
Вставьте в нее числа, буквы (верхнего и нижнего регистра) и не-алфавитные символы, приведенные ниже (каждый символ вставляется в отдельную запись). Вставьте пробел и оставьте одну запись со значением NULL.
Примените к столбцу запрос на выборку с новой конструкцией ORDER BY. Заполните пустые места в книге.
!"#$%&' ()*+,-./0123: ;<=> ?@ABCD[\]^ 'abcd{|}~
ORDER с двумя столбцами
К сожалению, директор придумал для вас еще кое-что...
К счастью, в одной команде можно упорядочить данные сразу по нескольким столбцам.
Похоже, все идет прекрасно: мы можем расставить фильмы по алфавиту и построить алфавитный список для каждой категории.
ORDER с несколькими столбцами
Возможности сортировки не ограничиваются всего двумя столбцами. Вы можете выполнить сортировку по любому количеству столбцов, чтобы получить нужную информацию.
Ты можешь больше! Отсортируй ВСЕ данные!Возможности сортировки не ограничиваются всего двумя столбцами. Вы можете выполнить сортировку по любому количеству столбцов, чтобы получить нужную информацию.
Взгляните на следующую конструкцию ORDER BY с тремя столбцами. Ниже показано, как происходит сортировка.
SELECT * FROM movie_table
ORDER BY category, purchased, title;
Упорядоченная таблица
Давайте посмотрим, какие данные вернет команда SELECT для исходной таблицы фильмов.
... и упорядоченные результаты нашего запроса:
Не люблю старое кино... А если я захочу сначала увидеть новые фильмы? Неужели придется читать список от конца к началу?
Теперь ты уже становишься джедаем SQL, и настало самое время сообщить тебе наше кодовое слово. В SQL есть ключевое слово для изменения направления сортировки.
По умолчанию SQL упорядочивает столбцы ORDER BY по возрастанию: от А к Я, от 1 к 99999 и т. д. Если вы предпочитаете получить данные в обратном порядке, укажите после имени столбца ключевое слово DESC.
Часто Задаваемые вопросы
Да, все зависит от контекста. Если поставить DESC перед именем таблицы - например, DESC movie_table; - то вы получите описание таблицы. В этом случае оно интерпретируется как сокращение от DESCRIBE.
В условии ORDER оно интерпретируется как сокращение от DESCENDING, и определяет порядок результатов.
Я могу использовать в своих запросах полные слова DESCRIBE и DESCENDING, чтобы избежать путаницы?
Как же так получается? Мы ведь использовали ключевое слово DESC для получения ОПИСАНИЯ таблицы. Вы уверены, что оно может использоваться для изменения порядка?
Вы можете использовать DESCRIBE, но DESCENDING работать не будет.
Ключевое слово DESC после имени столбца в условии ORDER BY упорядочивает результаты по убыванию.
DESC u изменение порядка данных
Представьте, что ваши данные стоят на ступеньках лестницы. Когда вы поднимаетесь по лестнице (данные упорядочены по возрастанию), буква А попадется вам раньше буквы Б. Когда вы будете спускаться (данные упорядочены по убыванию), первой на вашем пути попадется буква Я, а последней - буква А.
Следующий запрос возвращает список фильмов, упорядоченных по дате приобретения, начиная с самых новых. Для каждой дата фильмы, приобретенные в этот день, перечисляются в алфавитном порядке.
SELECT title, purchased
FROM movie_table
ORDER BY title ASC, purchased DESC;
Вам письмо!
TO: Персоналу видеотеки Galaxy QA Academy
FROM: Директор
Subject: Налетай!
Всем привет!
Все про прекрасно! Фильмы стоят на нужных местах, и благодаря этим хитроумным условиям ORDER BY каждый клиент может легко найти именно то, что ему нужно.
Чтобы наградить вас всех за примерную работу, завтра в моем доме состоится вечеринка с пиццей. Собираемся к 18:00.
И не забудьте принести отчет!
Ваш директор.
P. S. И не слишком наряжайтесь, мне тут нужно передвинуть кое-какую мебель...
Проблемы с печеньем
Руководитель нашей группы девочек-падаванов пытается разобраться, кто из ее подопечных продал больше всего звездных печенек (они прям как скауты ими промышляют). Пока у нее есть таблица с данными о продажах каждой девочки за день.
Я должна как можно скорее определить победителя.
Принцесса Лея, магистр девочек-паддаванов
Девочка продавшая больше всего печенья, награждается бесплатными уроками верховой езды на Таунтаунах. Все девочки хотят победить, поэтому Лее очень важно побыстрее определить победителя, пока дело не дошло до ссоры.
Используйте свои навыки обращения с ORDER BY и напишите запрос, который поможет Эдвине узнать имя победителя.
Сеансы группового гипноза с GROUP BY
Как поступить в ситуации когда нужно применить запрос для перепрошивки дроидов не ко всем из них, а только для группы определенного типа? Чтобы сгруппировать данные применяется чудо-команда SQL - GROUP BY. Данная конструкция создана для выборки отдельных групп строк из таблицы, к каждой из которых применяются функции, указанные в SELECT (например, COUNT(), MIN() и так далее).
Ещё одним очень частым применением GROUP BY в SQL является выборка уникальных записей из таблиц. В следующем примере вы сможете заметить, что в результирующей выборке нет повторяющихся имен девочек, тогда как в исходной таблице они были повторены и не раз.
Да, ты прав, действия команд в этом сходятся, но на самом деле у GROUP BY позволяет делать более точную группировку, давай рассмотрим на примере.
Допустим, у нас есть таблица с пользователями:
-
id - уникальный идентификатор.
-
email - e-mail пользователя.
-
hash - уникальный хэш пользователя.
И перед нами встала задача выбрать уникальных пользователей, причём именно уникальных людей, а не уникальных учётных записей. Ведь у одного человека может быть и 100 аккаунтов с разными e-mail и, разумеется, id. А hash - это некая строка, характеризующая его как уникального человека.
Таким образом, нам надо выбрать все записи с уникальным hash. Для этого опять же используется GROUP BY:
SELECT * FROM `table` GROUP BY `hash`
В результате, будут извлечены только уникальные hash, то есть 2 одинаковых hash в результирующей выборке Вы не увидите, а с помощью DISTINCT этого бы не получилось!
Ну я понял, что можно вот так сгруппировать результат запроса по по строкам, и убрать дублирующиеся строки, но для этого же есть простая команда DISTINCT, которая просто убираеит их, зачем заморачиваться с группировкой?
Функция AVG с GROUP BY
Другие девочки были огорчены, поэтому Эдвина решила вручить второй приз за высший средний объем продаж за день. Для его вычисления она использует функцию AVG.
Каждая девочка продавала печенье семь дней. Для каждой девочки функция AVG суммирует ее продажи, а затем делит их на 7.
MIN и MAX
Не желая сдаваться, Эдвина применяет к своей таблице функции MIN и MAX. Она хочет узнать, не было ли у других девочек более высоких продаж за день - а может, в свой худший день Бритни заработала меньше других?
Для определения наибольшего значения в столбце используется - функция MAX, а для определения наименьшего значения - функция MIN.
SELECT first_name, MAX (sales)
FROM cookie_sales
GROUP BY first_name;
SELECT first_name, MIX (sales)
FROM cookie_sales
GROUP BY first_name;
Count или подсчет строк
Чтобы узнать какая из девочек продавала печенье больше дней, чем другие, Эдвина пытается использовать для подсчета функцию COUNT. Функция COUNT возвращаем количество записей в столбце
Чтобы узнать, сколько дней продавалось печенье, можно было упорядочить результат по sale_date,
и вычесть из последней даты первую.
Правильно?
Вообще-то нет. Мы не можем быть уверены в том, что между первой и последней датой не было пропущенных дней.
Существует гораздо более простой способ узнать, в течение скольки дней продавалось печенье. Задача решается при помощи ключевого слова DISTINCT. Оно поможет нам не только вычислить нужное значение COUNT, но и получить список дат, не содержащий дубликатов.
Команда SELECT DISTINCT
Для начала посмотрим как работает ключевое слово DISTINCT без функции COUNT
Теперь давайте попробуем выполнить команду с функцией COUNT:
Практическое задание уровня
Для перехода на уровень 20, необходимо набрать минимум 12 баллов (60%) за задания уровня 19.