mobile-android/21.Sprint/02. SQL.md

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

Приступим!

Язык запросов

Базовые действия для работы с элементами в БД — это «создание», «чтение», «обновление» и «удаление». Эти четыре функции образуют широко известный акроним в программировании — CRUD (create, read, update, delete). Для реализации этих базовых действий в начале 1970-х IBM разработала первый прототип языка SQL. А уже первой системой управления базами данных, использующей SQL, стала Oracle V2.

Особенность SQL — вы не пишете алгоритм программы, а формулируете запрос на извлечение или модификацию данных, а сам алгоритм выполнения этого запроса выполняется уже движком СУБД. Потому SQL — это язык декларативного программирования.

Запрос состоит из различных частей, которые помогают задать:

• таблицы данных, из которых следует брать или модифицировать данные;
• условия выборки данных из этих таблиц;
• представление конечных данных, полученных из выбранных таблиц по заданным условиям.

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

1. DDL (data definition language) — операторы определения данных. Они работают на уровне целых таблиц.
   • CREATE — оператор создания таблицы,
   • ALTER — изменение таблицы,
   • DROP — удаление таблицы.
2. DML (data manipulation language) — операторы манипуляции данными. Их задача — манипуляция данными внутри конкретной таблицы.
   • SELECT — выбор данных в соответствии с условием,
   • INSERT — добавление новых данных,
   • UPDATE — изменение существующих данных,
   • DELETE — удаление данных.
3. DCL (data control language) — операторы определения доступа к данным для разных пользователей.

БЛОК Каждая СУБД имеет собственный диалект языка SQL. В курсе мы рассмотрим только диалект SQlite, потому что он лучше всего подходит для практических задач. Зная диалект SQlite, вы без труда освоите другие, так как разница между диалектами несущественная. КОНЕЦ БЛОКА

Разберём на примере приложения по поиску фильмов базовые операторы SQL. В прошлом уроке мы ввели три сущности: Фильм, Актер и Режиссер. Ниже мы будем работать именно с ними.

Оператор CREATE

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

Оператор CREATE используется для создания новой таблицы в базе данных. Параметр PRIMARY KEY используется для задания первичного ключа таблицы.

Синтаксис запроса CREATE с параметром PRIMARY KEY выглядит следующим образом:

CREATE TABLE table_name (
   column1 datatype1 [NOT NULL], 
   column2 datatype2, 
   ...,
   PRIMARY KEY (one_or_more_columns)
);

Где table_name — это название таблицы, column1, column2 — это названия столбцов таблицы, а datatype1, datatype2 — это типы данных для каждого столбца.

Параметр PRIMARY KEY указывает на столбец (или столбцы), который будет использоваться в качестве первичного ключа таблицы. Параметр NOT NULL указывает на то, что значение столбца не может быть NULL. По умолчанию значение может быть NULL.

Например, чтобы создать новую таблицу Актер с полями ID (первичный ключ), Имя и Фамилия, Рост, Дата_рождения, нужно написать следующий запрос:

CREATE TABLE Актер (
    ID INTEGER PRIMARY KEY, 
    Имя TEXT, 
    Фамилия TEXT,
    Дата_рождения DATE,
    Рост INTEGER,
);

Запрос INSERT

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

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Здесь table_name — это название таблицы, в которую нужно вставить данные, а column1, column2 — это названия столбцов таблицы. Вы можете вставить значения для каждого столбца, используя оператор VALUES, который следует за названиями столбцов. Например, чтобы вставить новую запись в таблицу Фильм с названием «Догвилль», вы можете написать следующий запрос:

INSERT INTO Фильм (Название, Описание, Картинка, Год_выпуска, Режиссер) 
VALUES ('Догвилль', 'Грейс приезжает в город, где ей совсем не рады. Эпос Ларса фон Триера о насилии в театральных декорациях', 'https://avatars.mds.yandex.net/.../3840x', 2003, 1);

При выполнении INSERT-запроса очень важно перечислить все столбцы таблицы. Вставляемые значения могут быть NULL, если при создании таблицы тип столбца не был указан как NOT NULL. При этом перечисление первичного ключа является необязательным параметром.

БЛОК При вставке записи ID новой записи обычно не пишется, так как СУБД проставляет ID записи автоматически, в случае если первичный ключ таблицы имеет тип INTEGER (целочисленный). В разных СУБД этот механизм реализуется по-разному. В БД SQLite, которая используется в Android, достаточно при создании таблицы написать ID INTEGER PRIMARY KEY — как в примере выше, в MySQL нужно явно дописать параметр AUTO_INCREMENT: ID int NOT NULL AUTO_INCREMENT. КОНЕЦ БЛОКА

Оператор SELECT

Запрос SELECT используется для извлечения данных из таблицы. Он является одним из наиболее часто используемых запросов SQL. Синтаксис запроса SELECT выглядит следующим образом:

SELECT column1, column2, ...
FROM table_name;

Где column1, column2 — это названия столбцов таблицы, а table_name — это название таблицы, из которой необходимо извлечь данные. Вы можете указать несколько столбцов, которые нужно извлечь, разделив их запятой. Если вы хотите извлечь все столбцы, вы можете использовать символ *. Например, чтобы извлечь все данные из таблицы Фильм, вы можете написать следующий запрос:

SELECT *
FROM Фильм;

В конце каждого SQL-запроса всегда должен стоять знак окончания ;.

Запрос SELECT может быть дополнен другими параметрами, такими как WHERE, ORDER BY, GROUP BY. Рассмотрим их подробнее.

WHERE

Параметр WHERE используется для фильтрации данных в соответствии с заданным условием. Синтаксис запроса SELECT с параметром WHERE выглядит следующим образом:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Здесь condition — это условие, которое определяет, какие записи должны быть извлечены из таблицы. Вы можете использовать операторы сравнения: >, < или =, — чтобы задать условие. Например, чтобы извлечь фильмы из таблицы Фильм, у которых год выпуска позже 2000, вы можете написать следующий запрос:

SELECT * 
FROM Фильм
WHERE Год_выпуска > 2000;

В запросах с WHERE также можно использовать базовые операнды для составных условий: AND, OR, NOT, IS NULL и прочие.

Например, чтобы получить список фильмов, у которых Год_выпуска > 2000 и при этом ID_режиссера = 1 (то есть фильмы снял Ларс фон Триер), нужно написать следующий запрос:

SELECT * 
FROM Фильм
WHERE Год_выпуска > 2000 AND ID_режиссера = 1;

LIKE

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

SELECT *
FROM Фильм
WHERE description LIKE '%ребенок%';

Символ % указывает на один, два или несколько символов. Также вместо % можно использовать символ _, он указывает на один символ.

ORDER BY

Параметр ORDER BY используется для сортировки результатов по заданному столбцу. Синтаксис запроса SELECT с параметром ORDER BY выглядит так:

SELECT column1, column2, ... 
FROM table_name
ORDER BY column_name [ASC|DESC];

Здесь column1, column2 — это названия столбцов таблицы, а column_name — это название столбца, по которому нужно отсортировать результаты. Вы можете использовать параметр ASC (по возрастанию) или DESC (по убыванию), чтобы задать порядок сортировки. Например, чтобы извлечь данные из таблицы Фильм только из столбцов Название и Год_выпуска, отсортированных по году выпуска в порядке убывания, вы можете написать следующий запрос:

SELECT Название, Год_выпуска 
FROM Фильм
ORDER BY Год_выпуска DESC;

Секции в SQL-запросе делятся на обязательные и необязательные. Обязательными в операторе SELECT являются всего лишь две — SELECT и FROM. Необязательные секции не ограничиваются теми, что мы привели в примерах выше, их намного больше. Но чаще всего в Android-разработке используются именно WHERE и ORDER BY.

БЛОК Заголовок: Как быстро читать SQL-запрос

1. Начните читать с секции FROM — откуда берутся данные.
2. Затем приступайте к секциям ниже, которые прописывают условия выборки данных WHERE и их порядок ORDER BY.
3. А после переходите к секции SELECT — там прописываются столбцы, которые будут показаны в результате. КОНЕЦ БЛОКА

Запрос UPDATE

Запрос UPDATE используется для обновления данных в таблице. Синтаксис запроса UPDATE выглядит так:

UPDATE table_name
SET column1 = value1, column2 = value2, ... 
WHERE condition;

Здесь table_name — это название таблицы, которую вы хотите обновить, column1, column2 — это названия столбцов, которые нужно обновить, value1, value2 — это новые значения для этих столбцов. Условие WHERE определяет, какие записи должны быть обновлены. Например, запрос на обновление данных о фильме «Мандерлей» с ID = 4 в таблице Фильм и установление ему нового года выпуска 2023 выглядит так:

UPDATE Фильм 
SET Год_выпуска = 2023
WHERE ID=4;

или

UPDATE Фильм 
SET Год_выпуска = 2023
WHERE Название='Мандерлей';

Запрос DELETE

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

DELETE
FROM 
table_name WHERE condition;

Где table_name — это название таблицы, из которой нужно удалить записи, а условие WHERE определяет, какие записи должны быть удалены. Например, чтобы удалить данные о фильме «Догвилль» из таблицы Фильм, вы можете написать следующий запрос:

DELETE
FROM Фильм
WHERE ID=4;

или

DELETE 
FROM Фильм
WHERE Название='Догвилль';

Вот такие компактные базовые операторы SQL. Давайте закрепим знания о них в квизе.

КВИЗ Множественный выбор

Выберите верно составленные SQL-запросы.

1. SELECT Название, Год_выпуска
   FROM Фильмы
   

НЕВЕРНО. Пояснение: Данный запрос не выполнится, так как после каждого SQL-запроса должен стоять символ окончания ;.

2. SELECT .
   FROM Актер
   WHERE Рост > 170;
   

НЕВЕРНО. Пояснение: Данный запрос не выполнится, так как в нём есть синтаксическая ошибка в секции SELECT. Если мы хотим видеть все столбцы таблицы в ответе, нужно использовать символ *, а не ..

3. SELECT Имя, Фамилия
   FROM Актер
   WHERE Рост > 170;
   

Верно! В этом запросе всё на своих местах.

4. UPDATE Актер
   SET Рост = 180 SET Дата_рождения = 13-02-2000
   WHERE id = 4;
   

НЕВЕРНО. Пояснение: SET нужно ставить только один раз, в самом начале, а потом уже перечислять изменяемые столбцы через запятую.

5. CREATE Съемочная_площадка (
    id integer
    Имя text,
    Страна text,
   );
   

НЕВЕРНО. Пояснение: В данном примере не указан первичный ключ. При создании таблицы указание первичного ключа — обязательно. Правильный запрос выглядел бы так: id integer PRIMARY KEY.

6. DELETE
   FROM Фильм;
   

Верно. Пояснение: Так как секция WHERE в запросах является необязательной, то данный запрос удалит все существующие в таблице записи.

7. INSERT INTO Фильм 
   VALUES ('Танцующая в темноте', 'Рабочая-эмигрантка спасается музыкой и танцами от наступающей слепоты и безысходности. ', 'https://avatars.mds.yandex.net/.../3840x', 2000, 1);
   

НЕВЕРНО. Пояснение: При вставке новой записи INSERT обязательно нужно перечислять столбцы таблицы после имени таблицы, то есть правильный синтаксис выглядит так:

INSERT INTO Фильм (Название, Описание, Картинка, Год_выпуска, Режиссер)
VALUES ('Танцующая в темноте', 'Рабочая-эмигрантка спасается музыкой и танцами от наступающей слепоты и безысходности. ', 'https://avatars.mds.yandex.net/.../3840x', 2000, 1);

В этом уроке вы узнали, что существует язык декларативного программирования SQL для добавления, извлечения, обновления или удаления данных в таблицах. Также рассмотрели основные типы запросов SQL, их особенности и диалект SQLite. Больше о диалекте SQLite и работе с SQL в Android — в следующем уроке.

КНОПКА Вперёд!