Основные операторы SQL

Автор: | 27.02.2023
Нет комментариев
Post Views: 1 425

План:

  1. Введение
  2. Базы Данных
  3. Настройка таблиц
  4. Ввод значений в таблицы
  5. Выбор данных
  6. Запросы для нескольких таблиц
  7. Операторы сравнений
  8. Вспомогательные символы

Введение

В прошлой статье мы узнали о языке SQL и SQLite, после чего выбрали клиент для работы с упрощённой версией и разобрали интерфейс программы DB Browser. Сегодня же мы создадим Базу Данных и таблицы в установленном клиенте, поработаем над запросами для нескольких таблиц и разберём часто используемые операторы SQL для комфортной работы с БД.

Базы Данных

Любая работа с запросами начинается с создания базы данных. В DB Browser для этого действия имеется соответствующая функция. По нажатию на кнопку «Новая БД» будет предложено выбрать расположение файла. В нём будет храниться вся информация о таблицах, их параметрах и значениях. Все Ваши дальнейшие действия будут проводиться в данной базе данных, которую Вы можете в любой момент сохранить нажатием кнопки «Сохранить всё» во вкладке Файл или сочетанием клавиш Ctrl+Shift+S.

Для создания БД через консоль SQLite применяется команда .open имя_файла.db. Например, .open base.db. Созданный файл сохраняется в папку консоли SQLite.

 

Настройка таблиц

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

Важно! Записывайте все названия одним словом или набором символов без разделения пробелом

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

CREATE TABLE "phonebook" (
"FirstName" TEXT NOT NULL,
"LastName" TEXT NOT NULL,
"Age" NUMERIC NOT NULL,
"PhoneNumber" INTEGER UNIQUE,
"Country" INTEGER);

Ввод значений в таблицы

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

В консоли добавить значения Вы можете через команду INSERT INTO имя_таблицы (имяСтолбца1, имяСтолбца2) с указанием значений через оператор VALUES в соответствии с порядком записанных столбцов.

INSERT INTO phonebook (FirstName, LastName, Age, PhoneNumber,Country)
VALUES ('Mike', 'Stone', '23', '90134679552', 'Germany');
INSERT INTO phonebook (FirstName, LastName, Age, PhoneNumber, Country)
VALUES ('Paula', 'Bronte', '19', '79468524693', 'Italy');

Выбор данных

Работа с вводом и выводом данных производится в разделе SQL. В нём вводится необходимый запрос и отображается получаемый результат.

SELECT *
FROM phonebook;

Любой запрос начинается с оператора выбора SELECT. FROM также является незаменимым элементом любой записи и устанавливает связь с таблицей, к которой данный запрос относится. «;» обозначает конец запроса. * выводит все столбцы в порядке, указанном при создании таблицы. Если же требуется вывести их в другом порядке или отобрать лишь часть информации, то порядок столбцов прописывается вручную. Пример указан ниже.

SELECT FirstName, Age, PhoneNumber
FROM phonebook;

В этом запросе мы вывели имя, возраст и номер телефона из таблицы phonebook.

Функция вывода таблицы через консоль не отличается от записи в DB Browser. Выведенные столбцы могут выглядеть неудобными для восприятия, однако эту проблему можно решит, установив .mode line или .mode column. Первый вариант выведет каждый столбец на новой строке, а второй разделит столбцы пустым пространством в строке.

Запросы для нескольких таблиц

Так как База данных может содержать не одну таблицу, то и запрос может обращаться к нескольким из них. Чтобы добавить две и более таблиц, нужно указать их название после оператора FROM:

SELECT *
FROM phonebook, country
WHERE country.CountryName=phonebook.country

Таким образом мы выводим все поля (SELECT *) в таблицах phonebook и country (FROM phonebook, country). Далее указывается связь между двумя столбцами (country.CountryName=phonebook.country). Чтобы соотнести элементы с их таблицами, нужно записать полное имя поля (ИмяБазы.ИмяПоля). В случае, если имена полей одинаково уникальны, то наименования таблиц можно и опустить.

SELECT *
FROM phonebook, country
WHERE CountryName=country

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

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

SELECT phonebook.FirstName, phonebook.PhoneNumber, country.CountryName
FROM phonebook, country
WHERE CountryName=phonebook.Country

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

Для вывода уникальных, неповторяющихся строк используется функция DISTINCT. Если же она не указана, то по умолчанию используется оператор *, выводящий любые значения строк, в том числе и повторяющиеся. Введённый для примера запрос выводит уникальные значения, исключая из столбца Country все повторяющиеся значения.

SELECT DISTINCT Country
FROM phonebook;

Оператор WHERE (Где/Который) используется для поиска и вывода значений, соответствующих определённому условию. Вместе с WHERE применяется LIKE, используемый для поиска необходимых значений по указанному шаблону. Важно не забывать выделять искомое в столбце значение двойными кавычками.

SELECT *
FROM phonebook
WHERE Country like "Italy"

Прочитать третью строку можно как «ГДЕ поле Country соответствует Italy». В результате запроса из таблицы phonebook будут выбраны все столбцы, значение Country которых будет равняться одному значению “Italy“.

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

  • “>” – Больше;
  • “<” – Меньше;
  • “=” – Равен;
  • “<>” – Неравен;
  • “>=” – Больше или равен;
  • “<=” – Меньше или равен.

Например:

SELECT *
FROM phonebook
WHERE Age >= 19

Используя эти операторы можно также сравнивать и строковые значения. Строки, записанные в верхнем регистре, будут считаться меньшими по сравнению с символами нижнего регистра. То есть, “В” будет меньше “в” и больше “А”.

Для большей вариативности запросов используются также логические операторы: AND (и), OR (или) и NOT (не).

SELECT *
FROM phonebook
WHERE Country LIKE "Italy" AND Age = 19
OR Country NOT LIKE "USA" AND Age = 23

Так мы вывели строки, в которых поле Country соответствует “Italy” И поле Age равняется 19 ИЛИ поле Country НЕ соответствует “USA” И поле Age равняется 23. В результате получены строки, соответствующие данным критериям. Также для запросов могут применяться круглые скобки, объединяющие условия в них:

SELECT phonebook.FirstName, phonebook.PhoneNumber, country.CountryName
FROM phonebook, country
WHERE Country = country.CountryName
AND (CountryName LIKE "Italy" OR CountryName like "Spain")

В результате из таблиц phonebook и country выведены значения, в которых Country соответствует CountryName, а также CountryName равняется значению “Italy” ИЛИ “Spain“.

Далее мы познакомимся с операторами IN, BETWEEN, IS NULL, упрощающими запись и улучшающими вид кода. IN используется для компактного перечисления значений в одном столбце. Сравните данный код:

SELECT *
FROM phonebook
WHERE Country LIKE "Italy" OR Country LIKE "USA" OR Country LIKE "Spain";

И этот:

SELECT *
FROM phonebook
WHERE Country IN ("Italy", "USA", "Spain");

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

Оператор BETWEEN также упрощает вид запроса и предназначен для указания диапазонов чисел или строковых значений:

SELECT *
FROM phonebook
WHERE Age BETWEEN 19 AND 23

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

SELECT *
FROM phonebook
WHERE Age >= 19 AND Age <= 23

Таким образом, данный оператор указывает диапазон, включающий в себя также указанные числа или символы. В результате запроса будет получен набор строк, содержащий в поле Age значения от 19 до 23 включительно.

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

SELECT *
FROM country
WHERE CountryName IS NULL

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

Вспомогательные символы

Последними на сегодня будут вспомогательные символы _ (нижнее подчеркивание) и % (процент). Используются они после оператора LIKE при указании значений.

SELECT *
FROM country
WHERE CountryName LIKE "______"

Подчеркивание указывает на один произвольный символ. В примере записаны 6 подчеркиваний, соответственно, 6 любых символов. В качестве результата выводятся строки, в столбце CountryName которых записаны страны с числом букв, равным 6. Ниже представлены другие варианты записей с использованием оператора LIKE с символом подчеркивания ( _ ).

SELECT *
FROM country
WHERE CountryName LIKE "S___n"

Необходимое значение – строка, значение CountryName которой начинается на S, заканчивается на n и содержит между буквами ещё 3 символа.

SELECT *
FROM country
WHERE CountryName LIKE "_tal_"

Выводится строка со значением, начинающимся и заканчивающимся на любой символ и включающая в себя tal.

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

SELECT *
FROM country
WHERE CountryName LIKE "G%"

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

SELECT *
FROM country
WHERE CountryName LIKE "%y"

Или между ними:

SELECT *
FROM country
WHERE CountryName LIKE "S%n"

Эти два символа можно комбинировать любым образом и располагать в любой части запрашиваемого слова. На примере ниже запрос выведет строки, в которых столбец CountryName содержит значения, начинающиеся любым символом (_), после которого стоит буква e, а число стоящих символов после неё не ограничено (%).

SELECT *
FROM country
WHERE CountryName LIKE "_e%"

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

SELECT *
FROM country
WHERE CountryName LIKE "S%__l%"

SELECT *
FROM country
WHERE CountryName NOT LIKE "%r_a"

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *