Академия: Конспект первой недели курса INTRO TO SQL: QUERYING AND MANAGING DATA
В первой части академии я был автором, а сейчас попробую себя в роли куратора. Забегая вперёд скажу - мне понравилось и я обязательно продолжу эту тему :)
Так как я это всё уже давно знаю и умею - то моё мнение об этом курсе будет не с позиции новичка, а с позиции специалиста, который с темой знаком непонаслышке. Разобрав каждую часть урока я дам краткую выдержку по предоставленному материалу и своё мнение о нём.
Имея многолетний опыт работы с SQL - я всё же решил посмотреть курс INTRO TO SQL: QUERYING AND MANAGING DATA и узнать - как же сейчас будущие программисты осваивают это поприще.
Вступление
В первом ролике достаточно лаконично, но ёмко объясняется:
- что такое база данных
- для чего она нужна
- что такое SQL
- каким образом в БД структурируются данные
Чего, на мой взгляд, нехватает - не объяснено что такое нереляционные базы данных.
Форма подачи информации удобна. Если чего-то не понимаете разговорный английский - благодаря иллюстрациям всё легко усваивается, и да - субтитры присутствуют.
Ещё хочу отметить, что в первом же ролике говорится о том, что не стоит всё помещать в одну таблицу, а лучше будет разносить данные по разным таблицам(на то нам и нужна реляционная база данных).
Создание таблиц и добавление в них данных
В данном разделе на примере разбираются 3 типа запросов - create table, insert и select.
Создать таблицу достаточно просто:
CREATE TABLE groceries (id INTEGER PRIMARY KEY, name TEXT, quantity INTEGER );
В данном запросе groceries - это имя таблицы. id, name и quantity - это имена колонок.
Ключевые слова TEXT и INTEGER - это типы данных, которые можно хранить в этой ячейке. То есть в name можно записать строку, а в id и quantity - натуральное число.
PRIMARY KEY - это указание того, что ячейка id является ключевой для данной таблицы. Её значение не может повторяться(является уникальным) в рамках данной таблицы.
В ячейку name можно записать и цифры, но храниться они будут как строковые значения, что играет важную роль при сортировке по данной колонке.
Таблица создаётся самая простая, при создании колонок указывается только имя и тип(опять же без прочих подробностей).
Хотел бы отметить, что помимо объяснения синтаксиса преподаются и правила хорошего тона при написании кода(да, sql запросы - это тоже код), что тоже немалый плюс.
Запросы на вставку и выборку тоже достаточно простые, новичок с лёгкостью сможет их повторить.
INSERT INTO groceries VALUES (1, "Bananas", 4);
Таким запросом можно вставить строку в таблицу groceries. Значения в скобках должны идти в той-же последовательности, что и имена столбцов в таблице.
SELECT * FROM groceries;
Это самый простой запрос для осуществления выборки данных из таблицы.
Знак *
означает, что нужно извлечь значения всех столбцов. Ключевое слово после FROM - имя таблицы. Лимиты в запросе не указаны, поэтому будут извлечены все строки.
Наверное стоило бы показать, как добавить несколько строк один запросом. Чего я ждал, но не увидел в этой части, так это использования auto increment.
Практика
Прямо в браузере можно писать запросы, видеть результат их выполнения и даже подсказки, говорящие об ошибках синтаксиса.
http://i.imgur.com/ezuTTPy.png
Вот это реально круто. Почему в моём детстве такого не было?:)
Данное задание легко можно выполнить просмотрев предыдущий ролик. Так что усвоить просмотренную ранее теорию не составит труда.
Если вы заметили - запросы на вставку я написал не как в предыдущем уроке, я так-же указывал имена столбцов. Валидатор корректно распарсил мой запрос и поставил мне зачёт.
Запросы к таблицам
Для данной темы используется несколько другая таблица. В ней на одну колонку больше.
http://i.imgur.com/GiKLzML.png
В уроке объясняются самые элементарные операторы, которые присутствуют в 99% запросов, а именно условие и сортировка.
Для запроса с условием используется оператор WHERE, после которого описываются сами условия выборки.
Например, чтобы выбрать все строки, в которых значение колонки aisle больше 5 следует написать:
SELECT * FROM groceries WHERE aisle > 5;
Синтаксис после оператора WHERE интуитивно понятен.
Знак больше можно заменить на меньше(
<
) или, например, на меньше или равно(<=
).
Для сортировки используется оператор ORDER BY
SELECT * FROM groceries ORDER BY aisle;
Данные при таком запросе будут отсортированы по колонке aisle от меньшего к большему.
Можно совместить в запросе оба оператора:
SELECT * FROM groceries WHERE aisle > 5 ORDER BY aisle;
От себя хочу добавить, что важно помнить правильную последовательность операторов, иначе запрос не будет работать. Ещё в данном уроке не сказано как сортировать данные от большего к меньшему. Реализуется это ключевым словом DESC, которое ставят после имени столбца указанного в сортировке.
Например:
SELECT * FROM groceries WHERE aisle > 5 ORDER BY aisle ASC;
- от меньшего к большему
SELECT * FROM groceries WHERE aisle > 5 ORDER BY aisle DESC;
- от большего к меньшему
Практика по выборке данных
В этом уроке нужно извлечь все данные из таблицы movies
. Это достаточно просто.
Напишем два запроса - выборка всего и выборка с условием и сортировкой:
SELECT * FROM `movies`;
SELECT * FROM `movies` WHERE release_year >= 2000 ORDER BY release_year;
http://i.imgur.com/cti2Mix.png
Тут всё предельно понятно. Другие имена колонок, оператор больше или равно и другое имя таблицы не вызывают сложностей.
Чего в этом уроке точно нехватает - так это использование оператора LIMIT. Ведь выборка может быть и очень большой, что может существенно повлиять на время выполнения запроса и дальнейшую обработку полученных данных. Данный оператор ограничивает количество выбранных строк.
Например:
SELECT * FROM `movies` LIMIT 15;
- будет выбрано только 15 строк
Агрегация данных
Реляционные базы данных умеют не только делать выборки строк, но и выполнять несложные математические операции прямо на этапе выборки данных. Речь идёт о нахождении минимума, максимума, суммы, среднего значения или количества строк.
- COUNT - вернёт количество строк сформированных SELECT запросом;
- SUM - вернёт сумму всех значений указанного поля;
- AVG - вернёт среднее значение для всех значений указанного поля;
- МАХ - вернёт максимальное выбранное значение указанного поля;
- MIN - вернёт минимальное выбранное значение указанного поля;
Как это работает:
Вернёт сумму всех значений колонки quantity:
SELECT SUM(quantity) FROM groceries;
Если в таблице есть ещё одна колонка, для каждого значения которой нужно посчитать сумму другой колонки - то нужно сгруппировать данные по этой колонке:
SELECT SUM(quantity) FROM groceries GROUP BY aisle;
Такой запрос выдаст сумму всех строк quantity для каждого значения aisle.
Словами такое не очень понятно, поэтому я поясню на примере такой схемы:
http://i.imgur.com/XcZHWOK.png
ЗапросSELECT aisle,SUM(quantity) FROM groceries GROUP BY aisle
сгруппирует выборку по колонке aisle и посчитает сумму в колонке quantity.
Результат будет таким:
http://i.imgur.com/uHy8tvA.png
Что хочу сказать. Новичек будет крайне удивлён, когда попытается исключить из запроса строки, в которых сагрегированное значение(count или avg) больше или меньше определённого значения. Хотя на первый взгляд это элементарно. В данном разделе не только не объяснено как решить данную задачу, но и не сказано что оператор where тут бесполезен. Думаю этот момент будет разобран в одном из следующих уроков.
Практика по агрегации данных
В данной части было задание ещё из самого начала курса, по добавлению данных в таблицу и собственно по агрегации. Посчитать сумму по колонке можно одним простым запросом SELECT SUM(minutes) FROM todo_list;
http://i.imgur.com/snes1TV.png
Задание выполнено, 2100 поинтов получено.
Тут можно потренироваться находить среднее значение, минимальное и т.д.
SQL или SEQUEL
SQL - это Structured Query Language
SEQUEL - Structured English QUEry Language
Изначально данный язык назывался SEQUEL, но в дальнейшем аббревиатура была сокращена до SQL. Так-же в этом подразделе обговаривается произношение данного термина. По-русски это может звучать как:
- эскуэль
- эскуэл
- сиквэль
Так как запросы пишутся только на английском языке - то слово English было исключено из сокращения. Так-же были опущены U и E в query.
По мне лучше всего звучит эскуэл. Только букву у следуют произносить как нечто среднее между у и ю. Мягкий знак, который так-же многие добавляют и в произношение термина html тут явно лишний.
Контрольное задание
Данная работа подразумевает, что слушатель уже должен понимать как создать таблицу, поместить в неё данные и потом извлечь их.
Необходимо спроектировать таблицы для интернет-магазина, наполнить их и извлечь данные.
Я выбрал магазин одежды. В моём магазине будет две таблицы: товары и разделы.
Я создал их так:
CREATE TABLE `products` (id INTEGER primary key, name TEXT, price INTEGER, section INTEGER, color TEXT, country TEXT);
CREATE TABLE `sections` (id INTEGER primary key, section_id INTEGER, color_name TEXT);
И наполнил их следующими данными:
INSERT INTO
products
VALUES (1, "Jacket", 111, 10, "Brown", "Denmark");
INSERT INTO products
VALUES (2, "Jacket", 120, 10, "White", "Denmark");
INSERT INTO products
VALUES (3, "Jacket", 120, 10, "Black", "China");
INSERT INTO products
VALUES (4, "T-shirt", 20, 20, "Colorful", "Russia");
INSERT INTO products
VALUES (5, "T-shirt", 20, 20, "Yellow", "Russia");
INSERT INTO products
VALUES (6, "T-shirt", 20, 20, "Blue", "Russia");
INSERT INTO products
VALUES (7, "T-shirt", 40, 20, "Brown", "France");
INSERT INTO products
VALUES (8, "Shirt", 15, 20, "Blue", "France");
INSERT INTO products
VALUES (9, "Shirt", 27, 20, "Purple", "France");
INSERT INTO products
VALUES (10, "Cap", 10, 30, "Black", "China");
INSERT INTO products
VALUES (11, "Cap", 12, 30, "Gray", "China");
INSERT INTO products
VALUES (12, "Baseball cap", 83, 30, "White", "Denmark");
INSERT INTO products
VALUES (13, "Baseball cap", 50, 30, "White", "China");
INSERT INTO products
VALUES (14, "Baseball cap", 45, 30, "Brown", "Russia");
INSERT INTO products
VALUES (15, "Baseball cap", 50, 30, "Brown", "Italy");
INSERT INTO products
VALUES (16, "Pants", 95, 40, "Beige", "Russia");
INSERT INTO products
VALUES (17, "Pants", 120, 40, "Gray", "Italy");
INSERT INTO products
VALUES (18, "Pants", 120, 40, "Brown", "Italy");
INSERT INTO products
VALUES (19, "Pants", 125, 40, "Blue", "Italy");
INSERT INTO products
VALUES (20, "Coat", 100, 10, "Gray", "China");
INSERT INTO
sections
VALUES (1, 10, "Outerwear");
INSERT INTO sections
VALUES (2, 20, "Shirts and T-shirts");
INSERT INTO sections
VALUES (3, 30, "Hats");
INSERT INTO sections
VALUES (4, 40, "Pants and jeans");
Запросы я сделал такие:
Выборка имени товара и его средней цены, отсортированного по количеству от меньшего к большему.
SELECT name, AVG(price) as avg_price FROM `products` GROUP BY name ORDER BY avg_price;
Выборка всех товаров, не дешевле 100.
SELECT * FROM `products` WHERE price >= 100;
Подсчёт количества каждого вида товара, отсортированного по количеству от большего к меньшему.
SELECT name, COUNT(*) as product_count FROM `products` GROUP BY name ORDER BY product_count DESC;
http://i.imgur.com/ERD9yxR.png
Такой подход в постановке задачи я считаю абсолютно правильным. Поясню. Когда перед вами встанет задача сделать интернет-магазин - вам точно так-же придётся проектировать структуру базы данных, ячейки таблиц и индексы, исходя из вашего задания.
Заключение
Данный курс даёт хорошее понимание того, как начать работать с базой данных, как хранить в ней произвольный массив данных и затем удобно его считывать. Ещё момент - парсер “спотыкается” на кириллице. Поэтому лучше используйте английский алфавит, тогда не будет ложных сообщений об ошибках синтаксиса.
Совет ещё не прошедшим данный курс.
Если вам интересна эта тема - смотрите обязательно. Прослушав данный курс хотел бы отметить чёткую структурированность курса, форму подачи материала и грамотное чередование теории с практикой. Сложность материала - необходимый минимум. Вы точно не сумеете запутаться.
PS
@studentka, мне понравился ваш разбор данного курса. Единственная неточность - “books” - это таблица, а не база данных, в остальном всё отлично:)
Заглавное фото с сайта datareview.info