Чтобы писать запросы SQL в базе данных SQLite, вы должны знать, как работают предложения SELECT, FROM, WHERE, GROUP BY, ORDER BY и LIMIT и как их использовать.
В этом руководстве вы узнаете, как использовать эти предложения и как писать предложения SQLite.
В этом руководстве вы узнаете:
- Чтение данных с помощью Select
- Имена и псевдонимы
- КУДА
- Ограничение и упорядочивание
- Удаление дубликатов
- Совокупный
- Группа по
- Запрос и подзапрос
- Установить операции -UNION, Intersect
- NULL обработка
- Условные результаты
- Общее табличное выражение
- Расширенные запросы
Чтение данных с помощью Select
Предложение SELECT - это основной оператор, который вы используете для запроса базы данных SQLite. В предложении SELECT вы указываете, что выбрать. Но перед предложением select давайте посмотрим, откуда мы можем выбирать данные с помощью предложения FROM.
Предложение FROM используется для указания, где вы хотите выбрать данные. В предложении from вы можете указать одну или несколько таблиц или подзапросов для выбора данных, как мы увидим позже в руководствах.
Обратите внимание, что для всех следующих примеров вы должны запустить sqlite3.exe и открыть соединение с образцом базы данных как текущее:
Шаг 1) На этом шаге
- Откройте «Мой компьютер» и перейдите в следующий каталог « C: \ sqlite » и
- Затем откройте sqlite3.exe :
Шаг 2) Откройте базу данных TutorialsSampleDB.db с помощью следующей команды:
Теперь вы готовы выполнить любой тип запроса к базе данных.
В предложении SELECT вы можете выбрать не только имя столбца, но и множество других опций, чтобы указать, что выбрать. Как следующее:
ВЫБРАТЬ *
Эта команда выберет все столбцы из всех ссылочных таблиц (или подзапросов) в предложении FROM. Например:
ВЫБРАТЬ *ОТ студентовВНУТРЕННЕЕ СОЕДИНЕНИЕ Отделов к студентам.DepartmentId = Departments.DepartmentId;
Это выберет все столбцы из таблиц "Студенты" и "Отделы":
ВЫБЕРИТЕ имя таблицы. *
Это выберет все столбцы только из таблицы «tablename». Например:
ВЫБЕРИТЕ студентов. *ОТ студентовВНУТРЕННЕЕ СОЕДИНЕНИЕ Отделов к студентам.DepartmentId = Departments.DepartmentId;
Это выберет все столбцы только из таблицы студентов:
Буквальное значение
Литеральное значение - это постоянное значение, которое может быть указано в операторе выбора. Вы можете использовать буквальные значения, как правило, так же, как вы используете имена столбцов в предложении SELECT. Эти буквальные значения будут отображаться для каждой строки из строк, возвращаемых запросом SQL.
Вот несколько примеров различных буквальных значений, которые вы можете выбрать:
- Числовой литерал - числа в любом формате, например 1, 2,55,… и т. Д.
- Строковые литералы - любая строка «США», «это образец текста» и т. Д.
- NULL - значение NULL.
- Current_TIME - покажет текущее время.
- CURRENT_DATE - это даст вам текущую дату.
Это может быть удобно в некоторых ситуациях, когда вам нужно выбрать постоянное значение для всех возвращаемых строк. Например, если вы хотите выбрать всех студентов из таблицы «Студенты» с новым столбцом под названием «Страна», содержащим значение «США», вы можете сделать это:
ВЫБЕРИТЕ *, «США» КАК СТРАНУ ОТ студентов;
Это даст вам все столбцы студентов, а также новый столбец «Страна», например:
Обратите внимание, что этот новый столбец Country на самом деле не является новым столбцом, добавленным в таблицу. Это виртуальный столбец, созданный в запросе для отображения результатов, и он не будет создаваться в таблице.
Имена и псевдонимы
Псевдоним - это новое имя столбца, которое позволяет выбрать столбец с новым именем. Псевдонимы столбцов указываются с помощью ключевого слова «AS».
Например, если вы хотите выбрать столбец StudentName, который будет возвращен с «Student Name» вместо «StudentName», вы можете присвоить ему псевдоним, подобный этому:
ВЫБЕРИТЕ StudentName КАК «Имя студента» ОТ студентов;
Это даст вам имена студентов с именем «Student Name» вместо «StudentName», например:
Обратите внимание, что имя столбца по-прежнему « StudentName »; столбец StudentName все тот же, не меняется на псевдоним.
Псевдоним не изменит имя столбца; он просто изменит отображаемое имя в предложении SELECT.
Также обратите внимание, что ключевое слово «AS» является необязательным, вы можете указать псевдоним без него, примерно так:
ВЫБЕРИТЕ StudentName «Имя студента» ИЗ студентов;
И он даст вам тот же результат, что и предыдущий запрос:
Вы также можете назначать псевдонимы таблицам, а не только столбцам. С тем же ключевым словом "AS". Например, вы можете сделать это:
ВЫБРАТЬ s. * FROM Student AS s;
Это даст вам все столбцы в таблице Студенты:
Это может быть очень полезно, если вы присоединяетесь к нескольким столам; вместо того, чтобы повторять полное имя таблицы в запросе, вы можете дать каждой таблице короткий псевдоним. Например, в следующем запросе:
ВЫБЕРИТЕ студентов.StudentName, Departments.DepartmentNameОТ студентовВНУТРЕННЕЕ СОЕДИНЕНИЕ Отделов к студентам.DepartmentId = Departments.DepartmentId;
Этот запрос выберет имя каждого студента из таблицы «Студенты» с названием его отдела из таблицы «Департаменты»:
Однако тот же запрос можно записать так:
ВЫБЕРИТЕ s.StudentName, d.DepartmentNameОТ студентов AS sВНУТРЕННЕЕ СОЕДИНЕНИЕ отделов КАК d ON s.DepartmentId = d.DepartmentId;
- Мы дали таблице «Студенты» псевдоним «s», а таблице «кафедры» - псевдоним «d».
- Затем вместо использования полного имени таблицы мы использовали их псевдонимы для ссылки на них.
- INNER JOIN объединяет две или более таблиц вместе с помощью условия. В нашем примере мы соединили таблицу "Студенты" с таблицей "Отделы" со столбцом "Идентификатор отдела". Также есть подробное объяснение INNER JOIN в учебнике «SQLite Joins».
Это даст вам точный результат, как в предыдущем запросе:
КУДА
Написание SQL-запросов с использованием только предложения SELECT с предложением FROM, как мы видели в предыдущем разделе, предоставит вам все строки из таблиц. Однако, если вы хотите отфильтровать возвращаемые данные, вы должны добавить предложение «WHERE».
Предложение WHERE используется для фильтрации набора результатов, возвращаемого запросом SQL. Вот как работает предложение WHERE:
- В предложении WHERE вы можете указать «выражение».
- Это выражение будет оцениваться для каждой строки, возвращаемой из таблиц, указанных в предложении FROM.
- Выражение будет оценено как логическое выражение с результатом true, false или null.
- Тогда будут возвращены только строки, для которых выражение было оценено с истинным значением, а те, которые дали ложные или нулевые результаты, будут проигнорированы и не включены в набор результатов.
- Чтобы отфильтровать набор результатов с помощью предложения WHERE, вы должны использовать выражения и операторы.
Список операторов в SQLite и способы их использования
В следующем разделе мы объясним, как можно фильтровать с помощью выражений и операторов.
Выражение - это одно или несколько буквальных значений или столбцов, объединенных друг с другом с помощью оператора.
Обратите внимание, что выражения можно использовать как в предложении SELECT, так и в предложении WHERE.
В следующих примерах мы попробуем выражения и операторы как в предложении select, так и в предложении WHERE. Чтобы показать вам, как они работают.
Существуют различные типы выражений и операторов, которые можно указать следующим образом:
SQLite оператор конкатенации "||"
Этот оператор используется для объединения одного или нескольких буквальных значений или столбцов друг с другом. Он выдаст одну строку результатов из всех объединенных литеральных значений или столбцов. Например:
ВЫБЕРИТЕ 'Идентификатор с именем:' || StudentId || StudentName как StudentIdWithNameОТ студентов;
Это будет объединено в новый псевдоним StudentIdWithName :
- Буквальное строковое значение « Id with Name: »
- со значением столбца " StudentId " и
- со значением из столбца " StudentName "
Оператор SQLite CAST:
Оператор CAST используется для преобразования значения из одного типа данных в другой тип данных.
Например, если у вас есть числовое значение, сохраненное в виде строкового значения, например, « '12 .5 ' », и вы хотите преобразовать его в числовое значение, вы можете использовать оператор CAST, чтобы сделать это следующим образом: « CAST ('12,5' AS НАСТОЯЩИЙ) ». Или, если у вас есть десятичное значение, такое как 12,5, и вам нужно получить только целую часть, вы можете преобразовать его в целое число, например «CAST (12,5 AS INTEGER)».
Пример
В следующей команде мы попытаемся преобразовать разные значения в другие типы данных:
ВЫБРАТЬ CAST ('12,5 'КАК REAL) ToReal, CAST (12,5 AS INTEGER) AS ToInteger;
Это даст вам:
Результат такой:
- CAST ('12,5 'AS REAL) - значение '12,5' является строковым значением, оно будет преобразовано в значение REAL.
- CAST (12,5 AS INTEGER) - значение 12,5 является десятичным значением, оно будет преобразовано в целое число. Десятичная часть будет усечена и станет 12.
Арифметические операторы SQLite:
Возьмите два или более числовых буквальных значения или числовых столбцов и верните одно числовое значение. В SQLite поддерживаются следующие арифметические операторы:
|
Пример:
В следующем примере мы попробуем пять арифметических операторов с буквальными числовыми значениями в одном и том же
выберите пункт:
ВЫБРАТЬ 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Это даст вам:
Обратите внимание, как мы использовали здесь оператор SELECT без предложения FROM. И это разрешено в SQLite, если мы выбираем буквальные значения.
Операторы сравнения SQLite
Сравните два операнда друг с другом и верните истину или ложь следующим образом:
|
Обратите внимание, что SQLite выражает истинное значение с помощью 1 и ложное значение с помощью 0.
Пример:
ВЫБРАТЬ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%' » - поиск имен студентов, начинающихся с буквы «a».
- Заканчивается узором. Например, « StudentName LIKE '% a' » - поиск имен учащихся, оканчивающихся на букву «а».
- Соответствие любому одиночному символу в строке с помощью символа подчеркивания «_». Например, « StudentName LIKE 'J___' » - поиск имен учащихся длиной 4 символа. Он должен начинаться с буквы «J» и может содержать еще три символа после буквы «J».
Примеры сопоставления с образцом:
- Получите имена студентов, начинающиеся с буквы «j»:
ВЫБЕРИТЕ StudentName ИЗ студентов, ГДЕ StudentName КАК 'j%';
Результат:
- Имена учеников заканчиваются буквой «y»:
ВЫБЕРИТЕ StudentName ИЗ числа студентов, ГДЕ StudentName КАК '% y';
Результат:
- Получите имена студентов, содержащие букву «n»:
ВЫБЕРИТЕ StudentName ИЗ числа студентов, ГДЕ StudentName КАК '% n%';
Результат:
«GLOB» - эквивалент оператора LIKE, но GLOB чувствителен к регистру, в отличие от оператора LIKE. Например, следующие две команды вернут разные результаты:
ВЫБЕРИТЕ «Джек» ГЛОБ «j%»;ВЫБЕРИТЕ 'Jack' LIKE 'j%';
Это даст вам:
- Первый оператор возвращает 0 (ложь), потому что оператор GLOB чувствителен к регистру, поэтому 'j' не равно 'J'. Однако второй оператор вернет 1 (истина), потому что оператор LIKE нечувствителен к регистру, поэтому 'j' равно 'J'.
Другие операторы:
SQLite И
Логический оператор, объединяющий одно или несколько выражений. Он вернет истину, только если все выражения вернут значение «истина». Однако он вернет false только в том случае, если все выражения дают значение «false».
Пример:
Следующий запрос будет искать студентов, у которых StudentId> 5 и StudentName начинается с буквы N, возвращенные студенты должны соответствовать двум условиям:
ВЫБРАТЬ *ОТ студентовГДЕ (StudentId> 5) И (StudentName КАК 'N%');
В результате на приведенном выше снимке экрана это даст вам только «Нэнси». Нэнси - единственная ученица, отвечающая обоим условиям.
SQLite ИЛИ
Логический оператор, который объединяет одно или несколько выражений, так что, если один из комбинированных операторов дает истину, он вернет истину. Однако, если все выражения возвращают false, оно вернет false.
Пример:
Следующий запрос будет искать студентов, у которых StudentId> 5 или StudentName начинается с буквы N, возвращенные студенты должны соответствовать хотя бы одному из условий:
ВЫБРАТЬ *ОТ студентовГДЕ (StudentId> 5) ИЛИ (StudentName LIKE 'N%');
Это даст вам:
В качестве вывода, на приведенном выше снимке экрана, вы получите имя студента с буквой «n» в имени плюс идентификатор студента, имеющий значение> 5.
Как видите, результат отличается от результата запроса с оператором AND.
SQLite МЕЖДУ
BETWEEN используется для выбора тех значений, которые находятся в диапазоне двух значений. Например, « X BETWEEN Y AND Z » вернет true (1), если значение X находится между двумя значениями Y и Z. В противном случае будет возвращено false (0). « X МЕЖДУ Y И Z » эквивалентно « X> = Y AND X <= Z », X должен быть больше или равен Y, а X меньше или равен Z.
Пример:
В следующем примере запроса мы напишем запрос, чтобы получить студентов со значением Id от 5 до 8:
ВЫБРАТЬ *ОТ студентовГДЕ StudentId МЕЖДУ 5 И 8;
Это даст только учащимся с идентификаторами 5, 6, 7 и 8:
SQLite IN
Принимает один операнд и список операндов. Он вернет истину, если значение первого операнда равно одному из значений операндов из списка. Оператор IN возвращает истину (1), если список операндов содержит значение первого операнда в своих значениях. В противном случае он вернет false (0).
Как это: « col IN (x, y, z) ». Это эквивалентно " (col = x) or (col = y) or (col = z) ".
Пример:
Следующий запрос выберет учащихся только с идентификаторами 2, 4, 6, 8:
ВЫБРАТЬ *ОТ студентовГДЕ StudentId IN (2, 4, 6, 8);
Так:
Предыдущий запрос даст точный результат в виде следующего запроса, потому что они эквивалентны:
ВЫБРАТЬ *ОТ студентовГДЕ (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. Но с тем же синтаксисом; он принимает один операнд и список операндов. Он вернет истину, если значение первого операнда не равно одному из значений операндов из списка. т.е. он вернет истину (0), если список операндов не содержит первый операнд. Как это: « col NOT IN (x, y, z) ». Это эквивалентно « (col <> x) AND (col <> y) AND (col <> z) ».
Пример:
Следующий запрос выберет студентов с идентификаторами, не равными одному из этих идентификаторов 2, 4, 6, 8:
ВЫБРАТЬ *ОТ студентовГДЕ StudentId НЕ ВХОДИТ (2, 4, 6, 8);
Так
В предыдущем запросе мы даем точный результат в виде следующего запроса, потому что они эквивалентны:
ВЫБРАТЬ *ОТ студентовГДЕ (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 нет строк вообще.
Пример:
В следующем примере мы выберем название отдела, если идентификатор отдела существует в таблице студентов:
ВЫБРАТЬ Название отделаОТ Департаментов AS dГДЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ ИДЕНТИФИКАЦИЮ ИЗ СТУДЕНТОВ КАК s WHERE d.DepartmentId = s.DepartmentId);
Это даст вам:
Вернутся только три кафедры « ИТ, физика и искусство ». И название отдела " Математика " не будет возвращено, потому что на этом отделе нет студентов, поэтому идентификатор отдела не существует в таблице студентов. Поэтому оператор EXISTS проигнорировал раздел « Математика ».
SQLite НЕ
Отменяет результат предыдущего оператора, который следует за ним. Например:
- NOT BETWEEN - вернет true, если BETWEEN вернет false, и наоборот.
- NOT LIKE - вернет true, если LIKE вернет false, и наоборот.
- NOT GLOB - вернет true, если GLOB вернет false, и наоборот.
- НЕ СУЩЕСТВУЕТ - он вернет истину, если EXISTS вернет ложь, и наоборот.
Пример:
В следующем примере мы будем использовать оператор NOT с оператором EXISTS, чтобы получить имена отделов, которых нет в таблице «Студенты», что является обратным результатом оператора EXISTS. Таким образом, поиск будет осуществляться через DepartmentId, которых нет в таблице отделов.
ВЫБРАТЬ Название отделаОТ Департаментов AS dГДЕ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ DepartmentIdОТ студентов AS sГДЕ d.DepartmentId = s.DepartmentId);
Выход :
Возвращается только кафедра « Математика ». Потому что « Математический » факультет - единственный факультет, которого нет в таблице студентов.
Ограничение и упорядочивание
Заказ SQLite
SQLite Order позволяет отсортировать результат по одному или нескольким выражениям. Чтобы упорядочить набор результатов, вы должны использовать предложение ORDER BY следующим образом:
- Во-первых, вы должны указать предложение ORDER BY.
- Предложение ORDER BY должно быть указано в конце запроса; после него может быть указано только предложение LIMIT.
- Укажите выражение для упорядочивания данных, это выражение может быть именем столбца или выражением.
- После выражения можно указать необязательное направление сортировки. Либо DESC, чтобы упорядочить данные по убыванию, либо ASC, чтобы упорядочить данные по возрастанию. Если вы не укажете ни один из них, данные будут отсортированы по возрастанию.
- Вы можете указать больше выражений, используя «,» между собой.
Пример
В следующем примере мы выберем всех студентов, упорядоченных по их именам, но в порядке убывания, а затем по названию отдела в порядке возрастания:
ВЫБЕРИТЕ s.StudentName, d.DepartmentNameОТ студентов AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentIdЗАКАЗАТЬ 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, используя запрос:
ВЫБРАТЬ * ИЗ УЧАЩИХСЯ LIMIT 4,3;
Это даст вам только трех студентов, начиная со строки 5. Таким образом, вы получите строки с StudentId 5, 6 и 7:
Удаление дубликатов
Если ваш SQL-запрос возвращает повторяющиеся значения, вы можете использовать ключевое слово « DISTINCT », чтобы удалить эти повторяющиеся значения и вернуть разные значения. Вы можете указать более одного столбца после работы клавиши DISTINCT.
Пример:
Следующий запрос вернет повторяющиеся «значения названия отдела»: Здесь у нас есть повторяющиеся значения с именами IT, Physics и Arts.
ВЫБРАТЬ d.DepartmentNameОТ студентов AS sВНУТРЕННЕЕ СОЕДИНЕНИЕ отделов КАК d ON s.DepartmentId = d.DepartmentId;
Это даст вам повторяющиеся значения для названия отдела:
Обратите внимание, как есть повторяющиеся значения для названия отдела. Теперь мы будем использовать ключевое слово DISTINCT с тем же запросом, чтобы удалить эти дубликаты и получить только уникальные значения. Так:
ВЫБЕРИТЕ ОТЛИЧИТЕЛЬНЫЙ d. Название отделенияОТ студентов AS sВНУТРЕННЕЕ СОЕДИНЕНИЕ отделов КАК d ON s.DepartmentId = d.DepartmentId;
Это даст вам только три уникальных значения для столбца названия отдела:
Совокупный
Агрегаты SQLite - это встроенные функции, определенные в SQLite, которые группируют несколько значений из нескольких строк в одно значение.
Вот агрегаты, поддерживаемые SQLite:
SQLite AVG ()
Возвращает среднее значение для всех значений x.
Пример:
В следующем примере мы получим среднюю оценку, которую студенты должны получить за все экзамены:
ВЫБРАТЬ СРЕДНЕЕ (Отметка) ИЗ отметок;
Это даст вам значение «18,375»:
Эти результаты являются результатом суммирования всех значений оценок, разделенных на их количество.
СЧЁТ () - СЧЁТ (X) или СЧЁТ (*)
Возвращает общее количество появлений значения x. И вот несколько вариантов, которые вы можете использовать с COUNT:
- COUNT (x): подсчитывает только значения x, где x - имя столбца. Он игнорирует значения NULL.
- COUNT (*): подсчитать все строки из всех столбцов.
- COUNT (DISTINCT x): вы можете указать ключевое слово DISTINCT перед x, которое будет получать количество различных значений x.
Пример
В следующем примере мы получим общее количество отделов с COUNT (DepartmentId), COUNT (*) и COUNT (DISTINCT DepartmentId) и их различие:
ВЫБЕРИТЕ СЧЕТЧИК (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) FROM студентов;
Это даст вам:
Как следующее:
- 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 (Название отдела) Пример
Следующий запрос объединит все значения названия кафедры из таблицы студентов и кафедры в одну строковую запятую. Поэтому вместо того, чтобы возвращать список значений, по одному значению в каждой строке. Он вернет только одно значение в одной строке, все значения будут разделены запятыми:
ВЫБЕРИТЕ GROUP_CONCAT (д. Название отделения)ОТ студентов AS sВНУТРЕННЕЕ СОЕДИНЕНИЕ отделов КАК d ON s.DepartmentId = d.DepartmentId;
Это даст вам:
Это даст вам список из 8 значений названий отделов, объединенных в одну строковую запятую.
GROUP_CONCAT (DISTINCT DepartmentName) Пример
Следующий запрос объединит отдельные значения названия отдела из таблицы студентов и факультетов в одну строковую запятую:
ВЫБЕРИТЕ GROUP_CONCAT (ОТЛИЧИТЕЛЬНОЕ d. Имя отделения)ОТ студентов AS sВНУТРЕННЕЕ СОЕДИНЕНИЕ отделов КАК d ON s.DepartmentId = d.DepartmentId;
Это даст вам:
Обратите внимание, как результат отличается от предыдущего; были возвращены только три значения, которые являются названиями различных отделов, а повторяющиеся значения были удалены.
GROUP_CONCAT (DepartmentName, '&') Пример
Следующий запрос объединит все значения столбца названия отдела из таблицы студентов и факультетов в одну строку, но с символом '&' вместо запятой в качестве разделителя:
ВЫБЕРИТЕ GROUP_CONCAT (d.DepartmentName, '&')ОТ студентов AS sВНУТРЕННЕЕ СОЕДИНЕНИЕ отделов КАК d ON s.DepartmentId = d.DepartmentId;
Это даст вам:
Обратите внимание, как символ «&» используется вместо символа по умолчанию «,» для разделения значений.
SQLite MAX () и MIN ()
MAX (X) возвращает максимальное значение из значений X. MAX вернет значение NULL, если все значения x равны нулю. В то время как MIN (X) возвращает наименьшее значение из значений X. MIN вернет значение NULL, если все значения X равны нулю.
Пример
В следующем запросе мы будем использовать функции MIN и MAX, чтобы получить самую высокую и самую низкую оценку из таблицы « Marks »:
ВЫБЕРИТЕ МАКС (Отметка), МИН (Отметка) ИЗ отметок;
Это даст вам:
SQLite СУММ (x), Итого (x)
Оба они вернут сумму всех значений x. Но они разные в следующем:
- SUM вернет null, если все значения равны нулю, но Total вернет 0.
- TOTAL всегда возвращает значения с плавающей запятой. СУММ возвращает целочисленное значение, если все значения x являются целыми числами. Однако, если значения не являются целыми числами, возвращается значение с плавающей запятой.
Пример
В следующем запросе мы будем использовать СУММ и итог, чтобы получить сумму всех оценок в таблицах « Оценки »:
ВЫБЕРИТЕ СУММУ (Отметка), ИТОГО (Отметка) ИЗ отметок;
Это даст вам:
Как видите, TOTAL всегда возвращает число с плавающей запятой. Но СУММ возвращает целочисленное значение, поскольку значения в столбце «Отметка» могут быть целыми числами.
Разница между SUM и TOTAL в примере:
В следующем запросе мы покажем разницу между SUM и TOTAL, когда они получат SUM из значений NULL:
ВЫБЕРИТЕ СУММУ (Отметка), ИТОГО (Отметка) ИЗ отметок ГДЕ TestId = 4;
Это даст вам:
Обратите внимание, что для TestId = 4 нет отметок, поэтому для этого теста есть нулевые значения. SUM возвращает пустое значение, тогда как TOTAL возвращает 0.
Группа по
Предложение GROUP BY используется для указания одного или нескольких столбцов, которые будут использоваться для группировки строк в группы. Строки с одинаковыми значениями будут собраны (организованы) в группы.
Для любого другого столбца, не включенного в группу по столбцам, вы можете использовать для него агрегатную функцию.
Пример:
Следующий запрос даст вам общее количество студентов, присутствующих на каждом факультете.
ВЫБЕРИТЕ d.DepartmentName, COUNT (s.StudentId) AS StudentsCountОТ студентов AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentIdГРУППА ПО d. Название отдела;
Это даст вам:
Предложение GROUPBY DepartmentName сгруппирует всех студентов в группы по одной для каждого названия факультета. По каждой группе «кафедры» будет засчитываться учащихся по ней.
Пункт HAVING
Если вы хотите отфильтровать группы, возвращаемые предложением GROUP BY, вы можете указать предложение «HAVING» с выражением после GROUP BY. Выражение будет использоваться для фильтрации этих групп.
Пример
В следующем запросе мы выберем те факультеты, на которых есть только два студента:
ВЫБЕРИТЕ d.DepartmentName, COUNT (s.StudentId) AS StudentsCountОТ студентов AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentIdГРУППА ПО d. Название отделаHAVING COUNT (s.StudentId) = 2;
Это даст вам:
Предложение HAVING COUNT (S.StudentId) = 2 отфильтрует возвращенные группы и вернет только те группы, которые содержат ровно двух учащихся. В нашем случае на факультете искусств 2 студента, поэтому он отображается в выходных данных.
SQLite запрос и подзапрос
Внутри любого запроса вы можете использовать другой запрос либо в SELECT, INSERT, DELETE, UPDATE, либо внутри другого подзапроса.
Этот вложенный запрос называется подзапросом. Теперь мы увидим несколько примеров использования подзапросов в предложении SELECT. Однако в учебнике по изменению данных мы увидим, как можно использовать подзапросы с операторами INSERT, DELETE и UPDATE.
Использование подзапроса в примере предложения FROM
В следующий запрос мы включим подзапрос в предложение FROM:
ВЫБРАТЬs.StudentName, t.MarkОТ студентов AS sВНУТРЕННЕЕ СОЕДИНЕНИЕ(ВЫБЕРИТЕ StudentId, MarkИЗ испытаний AS tINNER JOIN отмечает AS m ON t.TestId = m.TestId) НА s.StudentId = t.StudentId;
Запрос:
ВЫБЕРИТЕ StudentId, MarkИЗ испытаний AS tINNER JOIN отмечает AS m ON t.TestId = m.TestId
Вышеупомянутый запрос здесь называется подзапросом, потому что он вложен в предложение FROM. Обратите внимание, что мы дали ему псевдоним «t», чтобы мы могли ссылаться на столбцы, возвращаемые из него в запросе.
Этот запрос даст вам:
Итак, в нашем случае
- s.StudentName выбирается из основного запроса, который дает имена студентов и
- t.Mark выбирается из подзапроса; который выставляет оценки, полученные каждым из этих студентов
Использование подзапроса в примере предложения WHERE
В следующем запросе мы включим подзапрос в предложение WHERE:
ВЫБРАТЬ Название отделаОТ Департаментов AS dГДЕ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ DepartmentIdОТ студентов AS sГДЕ d.DepartmentId = s.DepartmentId);
Запрос:
ВЫБЕРИТЕ DepartmentIdОТ студентов AS sГДЕ d.DepartmentId = s.DepartmentId
Вышеупомянутый запрос здесь называется подзапросом, потому что он вложен в предложение WHERE. Подзапрос вернет значения DepartmentId, которые будут использоваться оператором NOT EXISTS.
Этот запрос даст вам:
В приведенном выше запросе мы выбрали отдел, на котором не зарегистрирован ни один студент. Это "математический" факультет вот здесь.
Установить операции - СОЮЗ, Пересечение
SQLite поддерживает следующие операции SET:
СОЮЗ И СОЮЗ ВСЕ
Он объединяет один или несколько наборов результатов (группу строк), возвращаемых несколькими операторами SELECT, в один набор результатов.
UNION вернет разные значения. Однако UNION ALL не будет и будет включать дубликаты.
Обратите внимание, что имя столбца будет именем столбца, указанным в первом операторе SELECT.
UNION Пример
В следующем примере мы получим список DepartmentId из таблицы студентов и список DepartmentId из таблицы департаментов в том же столбце:
ВЫБЕРИТЕ DepartmentId AS DepartmentIdUnioned ИЗ студентовСОЮЗВЫБЕРИТЕ DepartmentId из отделов;
Это даст вам:
Запрос возвращает только 5 строк, которые являются отдельными значениями идентификаторов отделов. Обратите внимание на первое значение, которое является нулевым значением.
SQLite UNION ALL Пример
В следующем примере мы получим список DepartmentId из таблицы студентов и список DepartmentId из таблицы департаментов в том же столбце:
ВЫБЕРИТЕ DepartmentId AS DepartmentIdUnioned ИЗ студентовСОЮЗ ВСЕВЫБЕРИТЕ DepartmentId из отделов;
Это даст вам:
Запрос вернет 14 строк, 10 строк из таблицы студентов и 4 из таблицы кафедр. Обратите внимание, что в возвращаемых значениях есть дубликаты. Также обратите внимание, что имя столбца было тем, которое было указано в первом операторе SELECT.
Теперь посмотрим, как UNION all даст разные результаты, если мы заменим UNION ALL на UNION:
SQLite INTERSECT
Возвращает значения, существующие в обоих комбинированных наборах результатов. Значения, которые существуют в одном из комбинированных наборов результатов, будут проигнорированы.
Пример
В следующем запросе мы выберем значения DepartmentId, которые существуют в таблицах Student и Departments в столбце DepartmentId:
ВЫБЕРИТЕ DepartmentId ИЗ студентовПересечениеВЫБЕРИТЕ DepartmentId из отделов;
Это даст вам:
Запрос возвращает только три значения 1, 2 и 3. Эти значения существуют в обеих таблицах.
Однако значения NULL и 4 не были включены, поскольку значение NULL существует только в таблице студентов, а не в таблице факультетов. И значение 4 существует в таблице факультетов, а не в таблице студентов.
Вот почему оба значения NULL и 4 были проигнорированы и не включены в возвращаемые значения.
КРОМЕ
Предположим, что у вас есть два списка строк, list1 и list2, и вы хотите, чтобы строки были только из списка list1, которого нет в list2, вы можете использовать предложение EXCEPT. Предложение EXCEPT сравнивает два списка и возвращает те строки, которые существуют в list1 и не существуют в list2.
Пример
В следующем запросе мы выберем значения DepartmentId, которые существуют в таблице департаментов и не существуют в таблице студентов:
ВЫБРАТЬ ИД ОТДЕЛЕНИЯ ИЗ ПодразделенийКРОМЕВЫБРАТЬ DepartmentId ИЗ студентов;
Это даст вам:
Запрос возвращает только значение 4. Это единственное значение, которое существует в таблице отделов и не существует в таблице студентов.
NULL обработка
Значение « NULL » - это специальное значение в SQLite. Он используется для представления неизвестного или отсутствующего значения. Обратите внимание, что нулевое значение полностью отличается от « 0 » или пустого «» значения. Однако, поскольку 0 и пустое значение являются известными значениями, нулевое значение неизвестно.
Значения NULL требуют специальной обработки в SQLite, теперь мы увидим, как обрабатывать значения NULL.
Искать значения NULL
Вы не можете использовать обычный оператор равенства (=) для поиска нулевых значений. Например, следующий запрос выполняет поиск учащихся с нулевым значением DepartmentId:
ВЫБРАТЬ * ИЗ студентов ГДЕ DepartmentId = NULL;
Этот запрос не даст результата:
Поскольку значение NULL не равно никакому другому значению, включая само значение NULL, поэтому оно не вернуло никакого результата.
- Однако для того, чтобы запрос работал, вы должны использовать оператор «IS NULL» для поиска нулевых значений следующим образом:
ВЫБРАТЬ * ИЗ студентов, ГДЕ DepartmentId НУЛЕВО;
Это даст вам:
Запрос вернет тех студентов, которые имеют нулевое значение DepartmentId.
- Если вы хотите получить те значения, которые не равны NULL, вы должны использовать оператор « IS NOT NULL » следующим образом:
ВЫБРАТЬ * ОТ студентов, ГДЕ ИДЕНДЕРАТ НЕ ПУСТО;
Это даст вам:
Запрос вернет тех студентов, у которых нет значения DepartmentId NULL.
Условные результаты
Если у вас есть список значений, и вы хотите выбрать любое из них на основе некоторых условий. Для этого условие для этого конкретного значения должно быть истинным, чтобы его можно было выбрать.
Выражение CASE будет оценивать этот список условий для всех значений. Если условие истинно, оно вернет это значение.
Например, если у вас есть столбец «Оценка», и вы хотите выбрать текстовое значение на основе значения оценки, как показано ниже:
- «Отлично», если оценка выше 85.
- «Очень хорошо», если оценка от 70 до 85.
- «Хорошо», если оценка от 60 до 70.
Затем вы можете использовать выражение CASE для этого.
Это можно использовать для определения некоторой логики в предложении SELECT, чтобы вы могли выбирать определенные результаты в зависимости от определенных условий, например, оператора if.
Оператор CASE может быть определен с помощью следующего синтаксиса:
- Вы можете использовать разные условия:
ДЕЛОКОГДА условие1 ТО результат1КОГДА условие2 ТО результат2WHEN condition3 THEN result3… ELSE resultnКОНЕЦ
- Или вы можете использовать только одно выражение и указать разные возможные значения на выбор:
CASE выражениеКОГДА значение1 ТО результат1КОГДА значение2 ТО результат2КОГДА value3 THEN result3… ELSE restulnКОНЕЦ
Обратите внимание, что предложение ELSE не является обязательным.
Пример
В следующем примере мы будем использовать выражение CASE со значением NULL в столбце идентификатора отдела в таблице «Студенты», чтобы отобразить текст «Нет отдела» следующим образом:
ВЫБРАТЬИмя студента,ДЕЛОКОГДА DepartmentId равен нулю, ТО "Нет отдела"ELSE DepartmentIdEND AS DepartmentIdОТ студентов;
- Оператор CASE проверяет значение DepartmentId, является ли оно нулевым или нет.
- Если это значение NULL, тогда будет выбрано буквальное значение «No Department» вместо значения DepartmentId.
- Если это не нулевое значение, будет выбрано значение столбца DepartmentId.
Это даст вам результат, как показано ниже:
Общее табличное выражение
Общие табличные выражения (CTE) - это подзапросы, которые определены внутри оператора SQL с заданным именем.
Он имеет преимущество перед подзапросами, потому что он определяется на основе операторов SQL и упрощает чтение, обслуживание и понимание запросов.
Общее табличное выражение можно определить, поместив предложение WITH перед операторами SELECT следующим образом:
С CTEnameВ КАЧЕСТВЕ(Оператор SELECT)SELECT, UPDATE, INSERT или update здесь FROM CTE
« CTEname » - это любое имя, которое вы можете дать CTE, вы можете использовать его, чтобы ссылаться на него позже. Обратите внимание, что вы можете определить оператор SELECT, UPDATE, INSERT или DELETE для CTE.
Теперь давайте посмотрим, как использовать CTE в предложении SELECT.
Пример
В следующем примере мы определим CTE из оператора SELECT, а затем будем использовать его позже в другом запросе:
Со всеми отделамиВ КАЧЕСТВЕ(ВЫБЕРИТЕ DepartmentId, DepartmentNameОТ отделов)ВЫБРАТЬs.StudentId,s.StudentName,a.DepartmentNameОТ студентов AS sВНУТРЕННИЕ СОЕДИНЯЙТЕСЬ со всеми подразделениями как ON s.DepartmentId = a.DepartmentId;
В этом запросе мы определили CTE и дали ему имя « AllDepartments ». Этот CTE был определен из запроса SELECT:
ВЫБЕРИТЕ DepartmentId, DepartmentNameОТ отделов
Затем, после того как мы определили CTE, мы использовали его в запросе SELECT, который следует за ним.
Обратите внимание, что общие табличные выражения не влияют на вывод запроса. Это способ определить логическое представление или подзапрос, чтобы повторно использовать их в одном запросе. Общие табличные выражения похожи на переменную, которую вы объявляете, и повторно используете ее в качестве подзапроса. Только инструкция SELECT влияет на вывод запроса.
Этот запрос даст вам:
Расширенные запросы
Расширенные запросы - это те запросы, которые содержат сложные соединения, подзапросы и некоторые агрегаты. В следующем разделе мы увидим пример расширенного запроса:
Где мы получаем,
- Названия кафедр со всеми студентами для каждой кафедры
- Имена студентов через запятую и
- Отображение кафедры, на которой обучаются не менее трех студентов
ВЫБРАТЬd.DepartmentName,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) как студентыОТ Департаментов AS dВНУТРЕННЕЕ ПРИСОЕДИНЯЙТЕСЬ к студентам как s.DepartmentId = d.DepartmentIdГРУППА ПО d.DepartmentNameИМЕЕТ СЧЕТ (s.StudentId)> = 3;
Мы добавили предложение JOIN, чтобы получить DepartmentName из таблицы Departments. После этого мы добавили предложение GROUP BY с двумя агрегатными функциями:
- «COUNT» для подсчета студентов для каждой группы факультетов.
- GROUP_CONCAT, чтобы объединить учащихся для каждой группы запятыми, разделенными одной строкой.
- После GROUP BY мы использовали предложение HAVING, чтобы отфильтровать отделы и выбрать только те отделы, в которых есть как минимум 3 студента.
Результат будет следующим:
Резюме:
Это было введение в написание запросов SQLite и основы запросов к базе данных, а также то, как вы можете фильтровать возвращаемые данные. Теперь вы можете писать свои собственные запросы SQLite.