tristamoff

7 лет назад

Академия: Конспект первой недели курса Learn SQL

В продолжении академии от @ontofractal и мы будем изучать сложные и непонятные(на самом деле простые и понятные) вещи.
Я вновь взял курс по SQL.
Если вы взяли курс на web-разработку - знайте, что БД - это узкое место любого сайта и относиться к нему нужно со всей ответственностью.
А теперь сам курс.
Курс разбит на 4 блока. В этом посте будут первые два.

Все картинки кликабельны

Manipulation(даже не знаю как перевести это в данном контексте)

SQL - Structured Query Language, то есть это язык структурированных запросов для управления реляционными базами данных. Его используют все web-фреймворки. Понимание сути данного языка даст вам возможность работать с любыми видами хранилищ.
Самое первое задание - просто извлечь все данные из таблицы, делается это запросом:
SELECT * FROM celebs;
Для наглядности выглядит это следующим образом:

Слева вы видите запрос.
В правом верхнем углу - результат запроса.
В правом нижнем - структуру таблицы.
Важно понимать, что реляционная база данных - это набор таблиц, колонки которых могут быть связаны между собой. А сами таблицы - это строго структурированные данные, представленные в виде строк(запись) и разбитые по столбцам. Каждый столбец(ячейка) имеет конкретный тип данных(строка, число, дата и т.п.)

Создание таблиц

Для создания таблицы нужно воспользоваться запросом типа:

CREATE TABLE table_name (
    column_1 data_type, 
    column_2 data_type, 
    column_3 data_type
  );

CREATE TABLE - это оператор
table_name - имя таблицы
column_1 - название колонки
data_type - тип данных в колонке
Давайте создадим свою таблицу:
CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);

Схематично сущность таблицы выглядит так:

Вставка и чтение данных

Для добавления в таблицу записей следует использовать оператор INSERT INTO
INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21);
Давайте добавим ещё несколько строк:

INSERT INTO celebs (id, name, age) VALUES (2, 'Beyonce Knowles', 33); 
INSERT INTO celebs (id, name, age) VALUES (3, 'Jeremy Lin', 26); 
INSERT INTO celebs (id, name, age) VALUES (4, 'Taylor Swift', 26);

А теперь считаем их из таблицы:
SELECT * FROM celebs;

Как видите - все данные вывелись.
Теперь попробуем вывести только одну колонку. Для этого вместо ключевого слова *\ (звёздочка) нужно перечислить имена требуемых колонок через запятую(или одной колонки)
SELECT name FROM celebs;
Такой запрос извлечёт только колонку name.

Обновление данных в таблице

Для обновления записей используется оператор UPDATE. С запросом типа update надо быть очень внимательным. Если вы забудете указать в каких строках нужно обновить данные - обновлены будут все строки.
Для обновления используется оператор UPDATE. Для задания условия оператор WHERE.
Сам запрос будет выглядеть так:
UPDATE celebs SET age = 22 WHERE id = 1;
Читать его следует так: в таблице celebs в строке, у которой значение ячейки id равно 1 обновить значение ячейки age до 22.

Если в таблице нет строки с id=1 - то ничего обновлено не будет, ошибки тоже не будет.

Изменение структуры таблиц

Бывает, что уже после создания таблицы нужно добавить/удалить колонку. Делается это оператором ALTER TABLE.
В запрос так-же нужно передать имя таблицы, название колонки и при добавлении - её тип.
Если в добавляете колонку - после имени таблицы нужно написать ADD COLUMN, если удаляете - DROP.
Запрос на добавление колонки в нашу таблицу будет таким:
ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

Как вы видите - колонка twitter_handle добавилась в конец таблицы.
И чтобы два раза не вставать - сразу занесём данные в эту колонку:
UPDATE celebs SET twitter_handle = '@taylorswift13' WHERE id = 4;

Удаление строк

Для удаления строк используется оператор DELETE. Ему нужно передать имя таблицы и, при необходимости, условия. Если условий не указать, то, как и в случае с UPDATE будут удалены все строки таблицы.
Давайте удалим все записи из таблицы celebs, в которых не заполнено поле twitter_handle:
DELETE FROM celebs WHERE twitter_handle IS NULL;

Как вы видите - остальные 3 строки пропали.

На этом с основами работы с БД всё, приступаем к более сложным запросам.

Не всегда нужно извлекать все колонки из таблицы. Для того, чтобы извлечь только некоторые из них - вместо звёздочки нужно перечислить их через запятую. Если вы напишите имя колонки неверно - вы получите ошибку и запрос не сработает.
Пример запроса:
SELECT name, imdb_rating FROM movies;
Такой запрос извлечёт из таблицы movies колонки name и imdb_rating.
Такой запрос сработает быстрее, чем SELECT * FROM movies;.

Выборка уникальных значений

Если значение какой-либо колонки повторяется в разных записях, а вам не нужны эти повторения - то можно уникализировать выборку по конкретному полю, для этого используется оператор DISTINCT
Например у вас в таблице movies 500 фильмов, у каждого указан жанр. Конечно же он будет повторяться. Если написать:
SELECT DISTINCT genre FROM movies;
то будут выбраны только уникальные жанры, повторений не будет.

Выборка с условием

В 95% выборок используются условия. Данных в таблицах, как правило, много или они не все нужны. Например вам нужно взять из таблицы фильмов данные по фильму номер 8. Вы ведь не будете извлекать все данные?!
Для задания условий используется ключевое слово WHERE, после которого указывают сами условия(или условие).

Математические условия

Например:
SELECT * FROM movies WHERE imdb_rating > 8;
Означает “извлечь все записи из таблицы movies, в которых значение ячейки imdb_rating больше восьми”.

Как вы видите - в выборке в колонке imdb_rating значения больше 8.
Так-же и операторы могут быть разными:

= - равно
!= - не равно
> - больше
< - меньше
>= - больше или равно
<= - меньше или равно
LIKE - содержит

Вхождение в строку

Очень удобный оператор LIKE, он используется при фильтрации выборки по колонкам с текстовым значением.
Например:
Найдёт все записи, в которых значение в колонке name начинается на Se, затем идёт любой один символ, и заканчивается на en
SELECT * FROM movies WHERE name LIKE 'Se_en';
То есть в выборку попадут строки, где name равно Seven, SeFen, Se9en, Se-en, Se en и т.п.
Символ % заменяет любое количество знаков.
SELECT * FROM movies WHERE name LIKE 'Se%';
Выдаст все строки, где name равно Sevven, Sefiop, Senks777, Se, Ser-i-on и т.п.

Поиск по интервалу

Если нужно указать интервал(диапазон) - то можно воспользоваться оператором BETWEEN
Например :
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;
Такой запрос найдёт все строки, у которых в ячейке year будет значение между 1990 и 2000
Аналогичную выборку даст запрос:
SELECT * FROM movies WHERE year>=1990 AND year<=2000;

Использование нескольких условий

Если в запросе нужно указать несколько условий - то они “соединяются” ключевыми словами AND или OR.
Например:
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000 AND genre = 'comedy';
Такой запрос выдаст все комедии за 1990-2000 года.

Как вы видите в результате запроса - оба условия сработали. Фильмов за 2001 год нет, и все они комедии.

Сортировки

Так же немаловажен и порядок вывода записей таблицы. Например, вам нужно вывести записи в хронологическом порядке, или по рейтингу. Для упорядочивания записей используется оператор ORDER BY. После него указывается имя колонки и, опционально, направление сортировки DESC или ASC.
Например:
SELECT * FROM movies ORDER BY imdb_rating DESC;
Записи будут отсортированы по колонке imdb_rating. Вначале будут строки с большим значением imdb_rating, а потом с меньшим.

Лимиты выборки

Если выдача с условиями слишком большая, или вам нужно какое-то максимальное число записей - то для этого есть оператор LIMIT.
Он ставится в конце запроса и после него идёт цифра и количеством строк. Если вам нужны не первые n записей, то после ключевого слова LIMIT ставятся две цифры через запятую - это отступ(старт, сдвиг) и лимит.
Например:
Вернёт первые три строки
SELECT * FROM movies ORDER BY imdb_rating ASC LIMIT 3;

Вернёт пять строк, начиная строки с третьей
SELECT * FROM movies ORDER BY imdb_rating ASC LIMIT 2, 5;
Почему с третьей, а не со второй? цифра 2 - это количество строк, которые надо пропустить. Получается первые две пропускаем, а третья уже попадает в выборку.

Составные части запроса тоже можно представить в виде диаграммы:

Что для меня было наиболее интересным и впечатляющим в данной неделе курса?

В этой неделе курса форма подачи информации - от простого к сложному. Знания, получаемые в каждом информационном блоке как снежный ком накладываются на друг на друга и заключительных практических примерах закрепляется информация из первых блоков. Такой подход заставляет всегда держать всё в голове и регулярно применять это на практике.

В следующих двух блоках будут агрегирующие функции и ещё много всего интересного и полезного.