SQLite Запрос: Выбор, Где, ПРЕДЕЛ, СМЕЩ, Подсчет, Группировка.

В разделах ниже рассматриваются чтение данных с помощью операторов SELECT и FROM, фильтрация строк с помощью оператора WHERE и его операторов, сортировка и ограничение результатов, агрегатные функции, GROUP BY и HAVING, подзапросы, операции над множествами, обработка значений NULL, выражения CASE и распространенные табличные выражения.

Написать SQL запросов в SQLite базы данных, вы должны знать, как работают предложения SELECT, FROM, WHERE, GROUP BY, ORDER BY и LIMIT и как их использовать.

В ходе этого урока вы научитесь использовать эти предложения и писать SQLite статьи.

Чтение данных с помощью Select

Предложение SELECT — это основной оператор, который вы используете для запроса SQLite база данных. В предложении SELECT вы указываете, что выбрать. Но прежде чем использовать предложение select, давайте посмотрим, откуда мы можем выбирать данные с помощью предложения FROM.

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

Обратите внимание, что во всех следующих примерах вам необходимо запустить sqlite3.exe и открыть соединение с образцом базы данных как поточное:

Шаг 1) На этом этапе

Откройте «Мой компьютер» и перейдите в следующую папку: «C:\sqlite», затем откройте файл «sqlite3.exe»:

Шаг 2) Откройте базу данных “TutorialsSampleDB.db” с помощью следующей команды:

Теперь вы готовы выполнить любой тип запроса к базе данных.

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

SELECT *

Эта команда выберет все столбцы из всех таблиц (или подзапросов), на которые имеются ссылки в предложении FROM. Например:

SELECT *
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

При этом будут выбраны все столбцы как из таблиц студентов, так и из таблиц кафедр:

ВЫБРАТЬ имя таблицы.*

Это позволит выбрать все столбцы только из таблицы «имя_таблицы». Например:

SELECT Students.*
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

При этом будут выбраны все столбцы только из таблицы студентов:

Буквальное значение

Литеральное значение — это постоянное значение, которое можно указать в операторе выбора. Обычно вы можете использовать литеральные значения так же, как имена столбцов в предложении SELECT. Эти литеральные значения будут отображаться для каждой строки из строк, возвращаемых запросом SQL.

Вот несколько примеров различных литеральных значений, которые вы можете выбрать:

• Числовой литерал – числа в любом формате, например 1, 2.55 и т. д.
• Строковые литералы – любая строка «США», «это образец текста» и т. д.
• NULL – значение NULL.
• Current_TIME – покажет вам текущее время.
• CURRENT_DATE – это даст вам текущую дату.

Это может быть удобно в некоторых ситуациях, когда вам нужно выбрать постоянное значение для всех возвращаемых строк. Например, если вы хотите выбрать всех студентов из таблицы «Студенты» с новым столбцом под названием «Страна», содержащим значение «США», вы можете сделать это:

SELECT *, 'USA' AS Country FROM Students;

Это даст вам все столбцы студентов, а также новый столбец «Страна», вот такой:

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

Имена и псевдонимы

Псевдоним — это новое имя столбца, позволяющее выбрать столбец с новым именем. Псевдонимы столбцов задаются с помощью ключевого слова «AS».

Например, если вы хотите выбрать столбец «Имя студента», который будет возвращаться с «Имя студента» вместо «Имя студента», вы можете дать ему такой псевдоним:

SELECT StudentName AS 'Student Name' FROM Students;

Это даст вам имена студентов с именем «Имя студента» вместо «Имя студента», например:

Обратите внимание, что имя столбца по-прежнему "StudentName"; название столбца StudentName осталось прежним, оно не изменилось из-за псевдонима.

Псевдоним не изменит имя столбца; он просто изменит отображаемое имя в предложении SELECT.

Также обратите внимание, что ключевое слово «AS» не является обязательным, вы можете указать псевдоним без него, примерно так:

SELECT StudentName 'Student Name' FROM Students;

И это даст вам тот же результат, что и предыдущий запрос:

Вы также можете давать псевдонимы таблицам, а не только столбцам. С тем же ключевым словом «AS». Например, вы можете сделать это:

SELECT s.* FROM Students AS s;

Это даст вам все столбцы в таблице Студенты:

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

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Этот запрос выберет имя каждого студента из таблицы «Студенты» с названием его факультета из таблицы «Кафедры»:

Однако тот же запрос можно записать так:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Мы присвоили таблице Students псевдоним «s», а таблице departments — псевдоним «d». Затем, вместо использования полных имен таблиц, мы использовали их псевдонимы для ссылки на них. INNER JOIN объединяет две или более таблиц с помощью условия. В нашем примере мы объединили таблицу Students с таблицей Departments по столбцу DepartmentId. Подробное объяснение INNER JOIN также приведено в разделе «SQLite Играя" руководство.

Это даст вам точный результат, как в предыдущем запросе:

ГДЕ

Написание SQL-запросов с использованием только предложения SELECT и предложения FROM, как мы видели в предыдущем разделе, даст вам все строки из таблиц. Однако, если вы хотите отфильтровать возвращаемые данные, вам необходимо добавить предложение «WHERE».

Предложение WHERE используется для фильтрации набора результатов, возвращаемого SQL-запросом. Вот как работает предложение WHERE:

• В предложении WHERE вы можете указать «выражение».
• Это выражение будет оцениваться для каждой строки, возвращаемой из таблиц, указанных в предложении FROM.
• Выражение будет оценено как логическое выражение с результатом true, false или null.
• Тогда будут возвращены только строки, для которых выражение было оценено с истинным значением, а строки с ложными или нулевыми результатами будут игнорироваться и не включаться в набор результатов.

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

Список операторов в SQLite и как их использовать

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

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

Обратите внимание, что вы можете использовать выражения как в предложении SELECT, так и в предложении WHERE.

В следующих примерах мы попробуем использовать выражения и операторы как в предложении select, так и в предложении WHERE. Чтобы показать вам, как они работают.

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

SQLite оператор конкатенации «||»

Этот оператор используется для объединения одного или нескольких литеральных значений или столбцов друг с другом. Он выдаст одну строку результатов из всех объединенных литеральных значений или столбцов. Например:

SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName
FROM Students;

Это объединится в новый псевдоним «StudentIdWithName»:

• Строковое значение "Идентификатор с именем: "
• со значением столбца «StudentId» и
• со значением из столбца «Имя студента»

SQLite Оператор CAST:

Оператор CAST используется для преобразования значения из... тип данных к другому типу данных.

Например, если у вас есть числовое значение, хранящееся в виде строки, например, «'12.5'», и вы хотите преобразовать его в числовое значение, вы можете использовать оператор CAST следующим образом: «CAST('12.5' ​​AS REAL)». Или, если у вас есть десятичное значение, например, 12.5, и вам нужно получить только целую часть, вы можете преобразовать его в целое число следующим образом: «CAST(12.5 AS INTEGER)».

Пример

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

SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;

Это даст вам:

Результат выглядит следующим образом:

• CAST('12.5' ​​AS REAL) – значение '12.5' ​​является строковым значением, оно будет преобразовано в РЕАЛЬНОЕ значение.
• CAST(12.5 AS INTEGER) – значение 12.5 является десятичным значением, оно будет преобразовано в целое значение. Десятичная часть будет усечена и станет 12.

SQLite Арифметический OperaТорс:

Возьмите два или более числовых литеральных значения или числовых столбцов и верните одно числовое значение. Арифметические операторы, поддерживаемые в SQLite составляют:

• Сложение «+» дает сумму двух операндов.
• нижеtracция “–” – подtracЭто два операнда, и в результате получается разница.
• Умножение «*» – произведение двух операндов.
• Остаток (по модулю) «%» – это остаток, полученный при делении одного операнда на второй операнд.
• Деление «/» — возвращает результат частного, полученный в результате деления левого операнда на правый операнд.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

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

SELECT 25+6, 25-6, 25*6, 25%6, 25/6;

Это даст вам:

Обратите внимание, как мы использовали здесь оператор SELECT без предложения FROM. И это разрешено в SQLite пока мы выбираем буквальные значения.

SQLite Операторы сравнения

Сравните два операнда друг с другом и верните true или false следующим образом:

• «<» — возвращает true, если левый операнд меньше правого операнда.
• «<=» — возвращает true, если левый операнд меньше или равен правому операнду.
• «>» – возвращает true, если левый операнд больше правого операнда.
• «>=» — возвращает true, если левый операнд больше или равен правому операнду.
• Операторы «=» и «==» возвращают true, если оба операнда равны. Обратите внимание, что оба оператора одинаковы, и между ними нет никакой разницы.
• Операторы «!=» и «<>» возвращают true, если два операнда не равны. Обратите внимание, что оба оператора одинаковы, и между ними нет никакой разницы.

Обратите внимание, что, SQLite выражает истинное значение с помощью 1 и ложное значение с помощью 0.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

SELECT
  10<6 AS '<', 10<=6 AS '<=',
  10>6 AS '>', 10>=6 AS '>=',
  10=6 AS '=', 10==6 AS '==',
  10!=6 AS '!=', 10<>6 AS '<>';

Это даст что-то вроде этого:

SQLite Операторы сопоставления с образцом

«LIKE» используется для сопоставления с шаблоном. С помощью «LIKE» можно искать значения, соответствующие шаблону, указанному с помощью подстановочного знака.

Операнд слева может быть либо строковым литералом, либо строковым столбцом. Шаблон можно указать следующим образом:

• Содержит шаблон. Например, StudentName LIKE '%a%' – это позволит найти имена студентов, в именах которых буква «a» присутствует в любой позиции столбца StudentName.
• Начинается с шаблона. Например, «StudentName LIKE 'a%'» — поиск имен студентов, начинающихся с буквы «а».
• Заканчивается по шаблону. Например, «StudentName LIKE '%a'» — поиск имен студентов, заканчивающихся на букву «а».
• Поиск совпадений с любым отдельным символом в строке, использующим символ подчеркивания «_». Например, «StudentName LIKE 'J___'» — поиск имен студентов длиной в 4 символа. Имя должно начинаться с буквы «J» и может содержать любые три символа после нее.

Примеры сопоставления шаблонов:

Получить имена студентов, начинающиеся с буквы «j»:

SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

Результат:

Получить имена студентов, заканчивающиеся на букву «y»:

SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

Результат:

Получите имена студентов, содержащие букву «n»:

SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

Результат:

Оператор “GLOB” эквивалентен оператору LIKE, но GLOB чувствителен к регистру, в отличие от оператора LIKE. Например, следующие две команды вернут разные результаты:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE 'j%';

Это даст вам:

Первый оператор возвращает 0 (ложь), поскольку оператор GLOB чувствителен к регистру, поэтому «j» не равно «J». Однако второй оператор вернет 1 (истина), поскольку оператор LIKE нечувствителен к регистру, поэтому «j» равно «J».

Другие операторы:

SQLite И

Логический оператор, объединяющий одно или несколько выражений. Он вернет true, только если все выражения дадут «истинное» значение. Однако он вернет false только в том случае, если все выражения дают «ложное» значение.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

Следующий запрос будет искать студентов, у которых StudentId > 5 и StudentName начинается с буквы N. Возвращаемые студенты должны соответствовать двум условиям:

SELECT *
FROM Students
WHERE (StudentId > 5) AND (StudentName LIKE 'N%');

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

SQLite OR

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

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

Следующий запрос будет искать студентов, у которых StudentId > 5 или StudentName начинается с буквы N. Возвращаемые студенты должны соответствовать хотя бы одному из условий:

SELECT *
FROM Students
WHERE (StudentId > 5) OR (StudentName LIKE 'N%');

Это даст вам:

В качестве вывода на скриншоте выше вы получите имя студента, в имени которого есть буква «n», а также идентификатор студента, имеющий значение>5.

Как видите, результат отличается от запроса с оператором AND.

SQLite МЕЖДУ

Оператор BETWEEN используется для выбора значений, находящихся в диапазоне между двумя значениями. Например, «X BETWEEN Y AND Z» вернет true (1), если значение X находится между двумя значениями Y и Z. В противном случае он вернет false (0). «X BETWEEN Y AND Z» эквивалентно «X >= Y AND X <= Z», где X должно быть больше или равно Y и X меньше или равно Z.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

В следующем примере запроса мы напишем запрос для получения студентов со значением идентификатора от 5 до 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Это даст только студентам с идентификаторами 5, 6, 7 и 8:

SQLite IN

Принимает один операнд и список операндов. Он вернет true, если значение первого операнда равно одному из значений операндов из списка. Оператор IN возвращает значение true (1), если список операндов содержит первое значение операнда в пределах своих значений. В противном случае он вернет false (0).

Вот так: “col IN(x, y, z)”. Это эквивалентно “ (col=x) or (col=y) or (col=z) ”.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

Следующий запрос выберет только учащихся с идентификаторами 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

НРАВИТСЯ:

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

SELECT *
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Оба запроса дают точный результат. Однако разница между этими двумя запросами заключается в том, что в первом запросе мы использовали оператор «IN». Во втором запросе мы использовали несколько операторов «ИЛИ».

Оператор IN эквивалентен использованию нескольких операторов OR. «WHERE StudentId IN(2, 4, 6, 8)» эквивалентно «WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);»

НРАВИТСЯ:

SQLite НЕ В

Оператор “NOT IN” противоположен оператору IN. Но с тем же синтаксисом он принимает один операнд и список операндов. Он вернет true, если значение первого операнда не равно значению одного из операндов в списке. То есть, он вернет true (0), если список операндов не содержит первый операнд. Например: “col NOT IN(x, y, z)”. Это эквивалентно “(col<>x) AND (col<>y) AND (col<>z)”.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

Следующий запрос выберет студентов с идентификаторами, не равными одному из этих идентификаторов 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Так

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

SELECT *
FROM Students
WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

НРАВИТСЯ:

На скриншоте выше

Мы использовали несколько операторов неравенства «<>», чтобы получить список студентов, которые не равны ни одному из следующих идентификаторов: 2, 4, 6 или 8. Этот запрос вернет всех остальных студентов, кроме этого списка идентификаторов.

SQLite СУЩЕСТВУЕТ

Операторы EXISTS не принимают никаких операндов; после него требуется только предложение SELECT. Оператор EXISTS вернет true (1), если из предложения SELECT есть какие-либо строки, и вернет false (0), если из предложения SELECT вообще нет строк.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

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

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

Это даст вам:

Будут возвращены только три факультета: «Информационные технологии, Физика и Искусство». Факультет «Математика» не будет возвращен, поскольку на этом факультете нет студентов, поэтому идентификатор факультета отсутствует в таблице студентов. Именно поэтому оператор EXISTS проигнорировал факультет «Математика».

SQLite НЕ

Reverses — результат работы предыдущего оператора, следующего за ним. Например:

• NOT BETWEEN – вернет true, если BETWEEN вернет false, и наоборот.
• НЕ НРАВИТСЯ. Возвращает true, если LIKE возвращает false, и наоборот.
• NOT GLOB – вернет true, если GLOB вернет false, и наоборот.
• NOT EXISTS — возвращает true, если EXISTS возвращает false, и наоборот.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

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

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Выход:

Будет возвращено только отделение «Математика». Потому что отделение «Математика» — единственное отделение, которого нет в таблице студентов.

Ограничение и упорядочение

SQLite Оформить заказ

SQLite Порядок — это сортировка результата по одному или нескольким выражениям. Чтобы упорядочить набор результатов, вам необходимо использовать предложение ORDER BY следующим образом:

• Сначала вам необходимо указать предложение ORDER BY.
• Предложение ORDER BY должно быть указано в конце запроса; после него можно указать только предложение LIMIT.
• Укажите выражение, по которому нужно упорядочить данные. Это выражение может быть именем столбца или выражением.
• После выражения вы можете указать дополнительное направление сортировки. Либо DESC, чтобы упорядочить данные по убыванию, либо ASC, чтобы упорядочить данные по возрастанию. Если вы не указали ни один из них, данные будут отсортированы по возрастанию.
• Вы можете указать больше выражений, используя знак «», между ними.

Пример

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

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
ORDER BY d.DepartmentName ASC , s.StudentName DESC;

Это даст вам:

• SQLite сначала упорядочит всех студентов по названию факультета в порядке возрастания
• Затем для каждого названия факультета все студенты этого факультета будут отображаться в порядке убывания их имен.

SQLite Лимит:

Вы можете ограничить количество строк, возвращаемых вашим SQL-запросом, используя предложение LIMIT. Например, LIMIT 10 даст вам только 10 строк и проигнорирует все остальные строки.

В предложении LIMIT можно выбрать определенное количество строк, начиная с определенной позиции, используя предложение OFFSET. Например, «LIMIT 4 OFFSET 4» проигнорирует первые 4 строки и вернет 4 строки, начиная с пятой, то есть вы получите строки 5, 6, 7 и 8.

Обратите внимание, что условие OFFSET является необязательным; вы можете написать его как «LIMIT 4, 4», и это даст вам точный результат.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

В следующем примере мы вернем только 3 студентов, начиная с идентификатора студента 5, используя запрос:

SELECT * FROM Students LIMIT 4,3;

Это даст вам только трех студентов, начиная со строки 5. Таким образом, вы получите строки со StudentId 5, 6 и 7:

Удаление дубликатов

Если ваш SQL-запрос возвращает повторяющиеся значения, вы можете использовать ключевое слово «DISTINCT», чтобы удалить эти дубликаты и вернуть только уникальные значения. После использования ключа DISTINCT можно указать более одного столбца.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

Следующий запрос вернет повторяющиеся значения «названия отдела»: Здесь у нас есть повторяющиеся значения с именами «ИТ», «Физика» и «Искусство».

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Это даст вам повторяющиеся значения для названия отдела:

Обратите внимание, что для названия отдела имеются повторяющиеся значения. Теперь мы будем использовать ключевое слово DISTINCT с тем же запросом, чтобы удалить эти дубликаты и получить только уникальные значения. Так:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Это даст вам только три уникальных значения для столбца названия отдела:

Заполнитель

SQLite Агрегаты — это встроенные функции, определенные в SQLite это сгруппирует несколько значений нескольких строк в одно значение.

Вот агрегаты, поддерживаемые SQLite:

SQLite AVG()

Возвращается среднее значение для всех значений x.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

В следующем примере мы получим среднюю оценку, которую студенты получат на всех экзаменах:

SELECT AVG(Mark) FROM Marks;

Это даст вам значение «18.375»:

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

СЧЕТ() – СЧЕТ(X) или СЧЕТ(*)

Возвращает общее количество раз появления значения x. Вот несколько опций, которые вы можете использовать с COUNT:

• COUNT(x): подсчитывает только значения x, где x — имя столбца. Он будет игнорировать значения NULL.
• COUNT(*): подсчитать все строки из всех столбцов.
• COUNT (DISTINCT x): перед x можно указать ключевое слово DISTINCT, которое будет получать количество различных значений x.

Пример

В следующем примере мы получим общее количество отделов с COUNT(DepartmentId), COUNT(*) и COUNT(DISTINCT DepartmentId) и чем они отличаются:

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

Это даст вам:

Как следующее:

• COUNT(DepartmentId) предоставит вам количество всех идентификаторов отдела и будет игнорировать нулевые значения.
• COUNT(DISTINCT DepartmentId) дает вам различные значения DepartmentId, которых всего 3. Это три разных значения названия отдела. Обратите внимание, что в имени студента имеется 8 значений названия факультета. Но только три разных значения: математика, информатика и физика.
• COUNT(*) подсчитывает количество строк в таблице студентов, которые составляют 10 строк для 10 студентов.

GROUP_CONCAT() – GROUP_CONCAT(X) или GROUP_CONCAT(X,Y)

Агрегатная функция GROUP_CONCAT объединяет несколько значений в одно значение с помощью запятой для их разделения. Он имеет следующие параметры:

• GROUP_CONCAT(X): это объединит все значения x в одну строку с запятой «», используемой в качестве разделителя между значениями. Значения NULL будут игнорироваться.
• GROUP_CONCAT(X, Y): это объединит значения x в одну строку, при этом значение y будет использоваться в качестве разделителя между каждым значением вместо разделителя по умолчанию «,». Значения NULL также будут игнорироваться.
• GROUP_CONCAT(DISTINCT X): это объединит все различные значения x в одну строку с запятой «», используемой в качестве разделителя между значениями. Значения NULL будут игнорироваться.

GROUP_CONCAT(ИмяОтдела) Пример

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

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Это даст вам:

Это даст вам список значений названий 8 отделов, объединенных в одну строку, разделенную запятыми.

GROUP_CONCAT(DISTINCTИмя отдела) Пример

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

SELECT GROUP_CONCAT(DISTINCT d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Это даст вам:

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

GROUP_CONCAT(ИмяОтдела,'&') Пример

Следующий запрос объединит все значения столбца названия факультета из таблицы студентов и факультетов в одну строку, но с символом «&» вместо запятой в качестве разделителя:

SELECT GROUP_CONCAT(d.DepartmentName, '&')
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Это даст вам:

Обратите внимание, что для разделения значений вместо символа по умолчанию «» используется символ «&».

SQLite МАКС() и МИН()

MAX(X) возвращает наибольшее значение из значений X. MAX вернет значение NULL, если все значения x равны нулю. Тогда как MIN(X) возвращает наименьшее значение из значений X. MIN вернет значение NULL, если все значения X равны нулю.

Пример

В следующем запросе мы воспользуемся функциями MIN и MAX, чтобы получить наивысшую и наименьшую оценку из таблицы «Оценки»:

SELECT MAX(Mark), MIN(Mark) FROM Marks;

Это даст вам:

SQLite СУММ(х), Всего(х)

Оба они вернут сумму всех значений x. Но они отличаются в следующем:

• SUM вернет ноль, если все значения равны нулю, но Total вернет 0.
• TOTAL всегда возвращает значения с плавающей запятой. СУММ возвращает целочисленное значение, если все значения x являются целыми числами. Однако, если значения не являются целыми числами, он вернет значение с плавающей запятой.

Пример

В следующем запросе мы будем использовать функции SUM и total, чтобы получить сумму всех оценок в таблице «Оценки»:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks;

Это даст вам:

Как видите, TOTAL всегда возвращает число с плавающей запятой. Но SUM возвращает целочисленное значение, поскольку значения в столбце «Отметка» могут быть целыми числами.

Разница между примером SUM и TOTAL:

В следующем запросе мы покажем разницу между SUM и TOTAL, когда они получают СУММУ значений NULL:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;

Это даст вам:

Обратите внимание, что для TestId = 4 отметок нет, поэтому для этого теста имеются нулевые значения. СУММ возвращает нулевое значение в виде пробела, тогда как ВСЕГО возвращает 0.

Группа по

Предложение GROUP BY используется для указания одного или нескольких столбцов, которые будут использоваться для группировки строк в группы. Строки с одинаковыми значениями будут собраны (организованы) в группы.

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

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

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

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName;

Это даст вам:

Предложение GROUPBY DepartmentName группирует всех студентов в группы, по одной для каждого названия факультета. Для каждой группы «кафедры» будет подсчитано количество студентов.

Пункт HAVING

Если вы хотите отфильтровать группы, возвращаемые предложением GROUP BY, вы можете указать предложение «HAVING» с выражением после GROUP BY. Выражение будет использоваться для фильтрации этих групп.

Пример

В следующем запросе мы выберем те кафедры, на которых обучаются всего два студента:

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName
HAVING COUNT(s.StudentId) = 2;

Это даст вам:

Предложение HAVING COUNT(S.StudentId) = 2 будет фильтровать возвращаемые группы и возвращать только те группы, которые содержат ровно двух учащихся. В нашем случае на факультете искусств учатся 2 студента, поэтому это отображается в выходных данных.

SQLite Запрос и подзапрос

Внутри любого запроса вы можете использовать другой запрос либо в SELECT, INSERT, DELETE, UPDATE, либо внутри другого подзапроса.

Этот вложенный запрос называется подзапросом. Теперь мы увидим несколько примеров использования подзапросов в предложении SELECT. Однако в руководстве по изменению данных мы увидим, как можно использовать подзапросы с операторами INSERT, DELETE и UPDATE.

Использование подзапроса в примере предложения FROM

В следующем запросе мы добавим подзапрос в предложение FROM:

SELECT
  s.StudentName, t.Mark
FROM Students AS s
INNER JOIN
(
   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId
)  ON s.StudentId = t.StudentId;

Запрос:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

Приведенный выше запрос здесь называется подзапросом, поскольку он вложен в предложение FROM. Обратите внимание, что мы дали ему псевдоним «t», чтобы мы могли ссылаться на возвращаемые из него столбцы в запросе.

Этот запрос даст вам:

Итак, в нашем случае

• s.StudentName выбирается из основного запроса, который дает имена студентов и
• t.Mark выбирается из подзапроса; который дает оценки, полученные каждым из этих студентов

Использование подзапроса в примере предложения WHERE

В следующем запросе мы добавим подзапрос в предложение WHERE:

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Запрос:

SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId

Приведенный выше запрос здесь называется подзапросом, поскольку он вложен в предложение WHERE. Подзапрос вернет значения DepartmentId, которые будут использоваться оператором NOT EXISTS.

Этот запрос даст вам:

В приведенном выше запросе мы выбрали факультет, на котором нет студентов. Это факультет математики.

Поставьте Operaции – СОЮЗ,Пересечение

SQLite поддерживает следующие операции SET:

СОЮЗ И СОЮЗ ВСЕХ

Он объединяет один или несколько наборов результатов (группу строк), возвращенных из нескольких операторов SELECT, в один набор результатов.

UNION вернет разные значения. Однако UNION ALL не будет и будет включать дубликаты.

Обратите внимание, что именем столбца будет имя столбца, указанное в первом операторе SELECT.

Пример СОЮЗА

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

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION
SELECT DepartmentId FROM Departments;

Это даст вам:

Запрос возвращает только 5 строк, которые представляют собой отдельные значения идентификатора отдела. Обратите внимание на первое значение, которое является нулевым.

SQLite СОЮЗ ВСЕХ Пример

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

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT DepartmentId FROM Departments;

Это даст вам:

Запрос вернет 14 строк: 10 строк из таблицы студентов и 4 строки из таблицы кафедр. Обратите внимание, что в возвращаемых значениях есть дубликаты. Также обратите внимание, что имя столбца было тем, которое указано в первом операторе SELECT.

Теперь давайте посмотрим, как UNION all будет давать разные результаты, если мы заменим UNION ALL на UNION:

SQLite ПЕРЕСЕЧЕНИЕ

Возвращает значения, существующие в обоих объединенных наборах результатов. Значения, существующие в одном из объединенных наборов результатов, будут игнорироваться.

Пример

В следующем запросе мы выберем значения DepartmentId, которые существуют в таблицах Student и Departments в столбце DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Это даст вам:

Запрос возвращает только три значения: 1, 2 и 3. Эти значения существуют в обеих таблицах.

Однако значения null и 4 не были включены, поскольку нулевое значение существует только в таблице студентов, а не в таблице факультетов. И значение 4 существует в таблице факультетов, а не в таблице студентов.

Вот почему значения NULL и 4 были проигнорированы и не включены в возвращаемые значения.

КРОМЕ

Предположим, что у вас есть два списка строк, список1 и список2, и вам нужны строки только из списка1, которого нет в списке2, вы можете использовать предложение «EXCEPT». Предложение EXCEPT сравнивает два списка и возвращает те строки, которые существуют в списке1 и отсутствуют в списке2.

Пример

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

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Это даст вам:

Запрос возвращает только значение 4. Это единственное значение, которое существует в таблице отделов и не существует в таблице студентов.

Обработка NULL

Значение «NULL» — это особое значение в SQLiteНулевое значение используется для обозначения неизвестного или отсутствующего значения. Обратите внимание, что нулевое значение совершенно отличается от «0» или пустого значения. Поскольку 0 и пустое значение являются известными значениями, нулевое значение является неизвестным.

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

Поиск значений NULL

Вы не можете использовать обычный оператор равенства (=) для поиска нулевых значений. Например, следующий запрос ищет студентов, имеющих нулевое значение DepartmentId:

SELECT * FROM Students WHERE DepartmentId = NULL;

Этот запрос не даст никакого результата:

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

Однако, чтобы запрос работал, необходимо использовать оператор «IS NULL» для поиска значений NULL следующим образом:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Это даст вам:

Запрос вернет тех студентов, у которых значение DepartmentId равно нулю.

Если вам нужно получить значения, которые не являются нулевыми, то необходимо использовать оператор «IS NOT NULL» следующим образом:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Это даст вам:

Запрос вернет тех студентов, у которых нет значения NULL DepartmentId.

Условные результаты

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

Выражение CASE оценит этот список условий для всех значений. Если условие истинно, оно вернет это значение.

Например, если у вас есть столбец «Оценка», и вы хотите выбрать текстовое значение на основе значения оценки следующим образом:

– «Отлично», если оценка выше 85.

– «Очень хорошо», если оценка находится между 70 и 85.

– «Хорошо», если оценка находится между 60 и 70.

Затем вы можете использовать для этого выражение CASE.

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

Оператор CASE может быть определен с использованием следующих синтаксисов:

Вы можете использовать разные условия:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END

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

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln
END

Обратите внимание, что предложение ELSE не является обязательным.

Пример

В следующем примере мы используем выражение CASE со значением NULL в столбце «ID отдела» таблицы «Студенты», чтобы отобразить текст «Отдел не указан» следующим образом:

SELECT
  StudentName,
  CASE
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId
  END AS DepartmentId
FROM Students;

• Оператор CASE проверит значение DepartmentId, является ли оно нулевым или нет.
• Если это значение NULL, то вместо значения DepartmentId будет выбрано буквальное значение «Нет отдела».
• Если это не пустое значение, будет выбрано значение столбца DepartmentId.

Это даст вам вывод, как показано ниже:

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

Общие табличные выражения (CTE) — это подзапросы, определенные внутри оператора SQL с заданным именем.

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

Общее табличное выражение можно определить, поместив предложение With перед оператором SELECT следующим образом:

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

«CTEname» — это любое имя, которое вы можете присвоить CTE; вы можете использовать его для ссылки на него в дальнейшем. Обратите внимание, что вы можете определять операторы SELECT, UPDATE, INSERT или DELETE для CTE.

Теперь давайте посмотрим пример использования CTE в предложении SELECT.

Пример

В следующем примере мы определим CTE из инструкции SELECT, а затем будем использовать его позже в другом запросе:

WITH AllDepartments
AS
(
  SELECT DepartmentId, DepartmentName
  FROM Departments
)
SELECT
  s.StudentId,
  s.StudentName,
  a.DepartmentName
FROM Students AS s
INNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;

В этом запросе мы определили CTE и присвоили ему имя «AllDepartments». Это CTE было определено из запроса SELECT:

SELECT DepartmentId, DepartmentName
  FROM Departments

Затем, после того как мы определили CTE, мы использовали его в запросе SELECT, который следует за ним.

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

Этот запрос даст вам:

Расширенные запросы

Расширенные запросы — это запросы, которые содержат сложные соединения, подзапросы и некоторые агрегаты. В следующем разделе мы увидим пример расширенного запроса:

Где мы получаем,

• Названия факультетов со всеми студентами каждого факультета.
• Имя ученика через запятую и
• Показано, что на факультете есть как минимум три студента.

SELECT
  d.DepartmentName,
  COUNT(s.StudentId) StudentsCount,
  GROUP_CONCAT(StudentName) AS Students
FROM Departments AS d
INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentName
HAVING COUNT(s.StudentId) >= 3;

Мы добавили оператор JOIN для получения имени отдела из таблицы Departments. После этого мы добавили оператор GROUP BY с двумя агрегатными функциями:

• «COUNT» для подсчета студентов в каждой группе факультетов.
• GROUP_CONCAT для объединения учащихся каждой группы через запятую в одну строку.

После GROUP BY мы использовали предложение HAVING, чтобы отфильтровать факультеты и выбрать только те факультеты, на которых учатся не менее 3 студентов.

Результат будет следующим:

Часто задаваемые вопросы (FAQ)