Какие типы данных в sql можно использовать при создании поля температура

5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях

Хотя составление SQL-запросов — это не самое интересное в работе дата-сайентистов, хорошее понимание SQL чрезвычайно важно для того, кто хочет преуспеть в любом занятии, связанном с обработкой данных. Дело тут в том, что SQL — это не только SELECT , FROM и WHERE . Чем больше SQL-конструкций знает специалист — тем легче ему будет создавать запросы на получение из баз данных всего, что ему может понадобиться.

Автор статьи, перевод которой мы сегодня публикуем, говорит, что она направлена на решение двух задач:

Изучение механизмов, которые выходят за пределы базового знания SQL.
Рассмотрение нескольких практических задач по работе с SQL.

Вопрос №1: второе место по зарплате

Напишите SQL-запрос для получения из таблицы со сведениями о заработной плате сотрудников ( Employee ) записи, содержащей вторую по размеру заработную плату.

Например, такой запрос, выполненный для таблицы, представленной ниже, должен вернуть 200 . Если в таблице нет значения, меньшего, чем самая высокая зарплата — запрос должен вернуть null .

+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+

▍Решение А: использование IFNULL и OFFSET

Вот основные механизмы, которые будут использованы в данном варианте решения задачи:

IFNULL(expression, alt) : эта функция возвращает свой аргумент expression в том случае, если он не равен null . В противном случае возвращается аргумент alt . Мы воспользуемся этой функцией для того чтобы возвратить null в том случае, если в таблице не окажется искомого значения.
OFFSET : этот оператор используется с выражением ORDER BY для того чтобы отбросить первые n строк. Это нам пригодится по той причине, что нас интересует вторая строка результата (то есть — вторая по величине зарплата, данные о которой есть в таблице).

SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 ), null) as SecondHighestSalary FROM Employee LIMIT 1

▍Решение B: использование MAX

В запросе, представленном ниже, используется функция MAX . Здесь выбирается самое большое значение заработной платы, не равное максимальной заработной плате, полученной по всей таблице. В результате мы и получаем то, что нам нужно — вторую по величине заработную плату.

SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary != (SELECT MAX(salary) FROM Employee)

Вопрос №2: дублирующиеся адреса электронной почты

Напишите SQL-запрос, который обнаружит в таблице Person все дублирующиеся адреса электронной почты.

+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+

▍Решение А: COUNT в подзапросе

Сначала мы создаём подзапрос, в котором выясняется частота появления каждого адреса в таблице. Затем результат, возвращаемый подзапросом, фильтруется с использованием инструкции WHERE count > 1 . Запрос вернёт сведения об адресах, встречающихся в исходной таблице больше одного раза.

SELECT Email FROM ( SELECT Email, count(Email) AS count FROM Person GROUP BY Email ) as email_count WHERE count > 1

▍Решение B: выражение HAVING

HAVING : это выражение, которое позволяет использовать инструкцию WHERE вместе с выражением GROUP BY .

SELECT Email FROM Person GROUP BY Email HAVING count(Email) > 1

Вопрос №3: растущая температура

Напишите SQL-запрос, который находит в таблице Weather все даты (идентификаторы дат), когда температура была бы выше температуры на предшествующие им даты. То есть, нас интересуют даты, в которые «сегодняшняя» температура выше «вчерашней».

+---------+------------------+------------------+ | Id(INT) | RecordDate(DATE) | Temperature(INT) | +---------+------------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------------+------------------+

▍Решение: DATEDIFF

DATEDIFF : эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.

SELECT DISTINCT a.Id FROM Weather a, Weather b WHERE a.Temperature > b.Temperature AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

Вопрос №4: самая высокая зарплата в подразделении

В таблице Employee хранятся сведения о сотрудниках компании. В каждой записи этой таблицы содержатся сведения об идентификаторе ( Id ) сотрудника, о его имени ( Name ), о зарплате ( Salary ) и о подразделении компании, где он работает ( Department ).

+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+

В таблице Department содержатся сведения о подразделениях компании.

+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+

Напишите SQL-запрос, который находит в каждом из подразделений сотрудников с максимальной заработной платой. Например, для вышеприведённых таблиц подобный запрос должен возвращать результаты, представленные следующей таблицей (при этом порядок строк в таблице значения не имеет):

+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Jim | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+

▍Решение: команда IN

Команда IN позволяет задавать в инструкции WHERE условия, соответствующие использованию нескольких команд OR . Например, две следующие конструкции идентичны:

WHERE country = ‘Canada’ OR country = ‘USA’ WHERE country IN (‘Canada’, ’USA’).

Здесь мы хотим получить таблицу, содержащую название подразделения ( Department ), имя сотрудника ( Employee ) и его заработную плату ( Salary ). Для этого мы формируем таблицу, в которой содержатся сведения об идентификаторе подразделения ( DepartmentID ) и о максимальной зарплате по этому подразделению. Далее мы объединяем две таблицы по условию, в соответствии с которым записи в результирующую таблицу попадают только в том случае, если DepartmentID и Salary есть в ранее сформированной таблице.

SELECT Department.name AS 'Department', Employee.name AS 'Employee', Salary FROM Employee INNER JOIN Department ON Employee.DepartmentId = Department.Id WHERE (DepartmentId , Salary) IN ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId )

Вопрос №5: пересаживание учеников

Мэри — учительница в средней школе. У неё есть таблица seat , хранящая имена учеников и сведениях об их местах в классе. Значение id в этой таблице постоянно возрастает. Мэри хочет поменять местами соседних учеников.

Вот таблица исходного размещения учеников:

+---------+---------+ | id | student | +---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +---------+---------+

Вот что должно получиться после пересаживания соседних учеников:

+---------+---------+ | id | student | +---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +---------+---------+

Напишите запрос, который позволит учительнице решить вышеописанную задачу.

Обратите внимание на то, что если количество учеников является нечётным — последнего ученика никуда пересаживать не надо.

▍Решение: использование оператора WHEN

SQL-конструкцию CASE WHEN THEN можно рассматривать как оператор if в программировании.

В нашем случае первый оператор WHEN используется для проверки того, назначен ли последней строке в таблице нечётный идентификатор. Если это так — строка не подвергается изменениям. Второй оператор WHEN отвечает за добавление 1 к каждому нечётному идентификатору (например — 1, 3, 5 превращается в 2, 4, 6) и за вычитание 1 из каждого чётного идентификатора (2, 4, 6 превращаются в 1, 3, 5).

SELECT CASE WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND MAX(id) FROM seat) THEN id WHEN id%2 = 1 THEN id + 1 ELSE id - 1 END AS id, student FROM seat ORDER BY id

Итоги

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

P.S. В нашем маркетплейсе есть Docker-образ с SQL Server Express, который устанавливается в один клик. Вы можете проверить работу контейнеров на VPS. Всем новым клиентам бесплатно предоставляются 3 дня для тестирования.

Уважаемые читатели! Что вы можете посоветовать тем, кто хочет освоить искусство создания SQL-запросов?

Блог компании RUVDS.com
Занимательные задачки
SQL
Карьера в IT-индустрии

Типы данных в MySQL

В этой инструкции рассказываем про основные типы данных в MySQL и даем советы, где лучше использовать тот или иной тип.

Эта инструкция — часть курса «MySQL для новичков».

Смотреть весь курс

Введение

При создании таблиц в MySQL для каждого столбца нужно указывать тип данных. Он определяет, какие данные там могут храниться, как будут обрабатываться и сколько места будут занимать. В MySQL все типы данных делятся на несколько классов: числовые типы, символьные, дата/время и так далее. В каждом классе есть несколько типов данных, которые внешне могут быть похожи, но их поведение или принципы хранения отличаются. Важно выбрать правильный тип сразу при создании таблицы, потому что потом готовую структуру и приложения будет сложней переделать.

В этой статье мы расскажем об основных типах данных в MySQL и дадим советы, где лучше использовать тот или иной тип.

Возможности MySQL в «Облачных базах данных»

Числовые типы

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

Целые числа

Хранят только целые числа, без дробной части. Делятся на signed (со знаком) и unsigned (без знака). Типы singed позволяют хранить как положительные, так и отрицательные значения. Типы unsigned хранят только положительные числа, но зато диапазон значений больше. Это может быть полезно в случаях, когда хранимые значения заведомо не могут быть отрицательным. Например, количество товара или идентификатор записи в таблице.

Тип	Размер (байт)	Диапазон signed	Диапазон unsigned
TINYINT	1	-128 … 127	0 … 255
SMALLINT	2	-3 2768 … 32 767	0 … 65 535
MEDIUMINT	3	-8 388 608 … 8388607	0 … 16 777 215
INT	4	2 147 483 648 … 2 147 483 647	0 … 42 94 967 295
BIGINT	8	-2 63 … 2 63 -1	0 … 2 64 -1

Числа с плавающей точкой

Хранят приблизительные значения. Не резервируют определенное количество бит для целочисленной или дробной частей. Поэтому у всех значений в таблице количество до и после запятой будет разным.

Тип	Размер (байт)	Диапазон
FLOAT	4	-3.402823466E+38 … -1.175494351E-38 и 1.175494351E-38 … 3.402823466E+38
DOUBLE	8	-1.7976931348623157E+308 … -2.2250738585072014E-308 и2.2250738585072014E-308 … 1.7976931348623157E+308

До версии MySQL 8.0.17 эти типы данных поддерживали диапазон unsigned, но он работал не так, как с целыми числами. Беззнаковый диапазон просто запрещал сохранять отрицательные значения, при этом максимальное значение не увеличивалось. Начиная с версии 8.0.17, использование unsigned не рекомендуется и считается устаревшим. В будущих версиях MySQL поддержку беззнакового диапазона для этого типа данных могут вовсе удалить.

Числа с фиксированной точкой

Эти типы данных используются для сохранения заданной точности. Такие числа резервируют определенное количество бит для целочисленной и дробной частей. Независимо от того, насколько большое или маленькое число, оно всегда будет использовать одно и то же количество бит для сохранения каждой части. Эти типы данных подходят для случаев, когда важна точность, например, деньги.

В MySQL для хранения чисел с фиксированной точкой используются типы DECIMAL(M,D) и NUMERIC(M,D), но по факту это синонимы. Можно использовать любой из этих типов, результат будет одинаковым. В таких столбцах максимально можно хранить до 65 чисел вместе с целочисленной и дробной частями.

Символьные (строковые)

Символьные типы используются для хранения текстов. Есть два основных типа: CHAR и VARCHAR. С точки зрения пользователя они выглядят похоже, но MySQL хранит и обрабатывает их по-разному.

CHAR хранит строку фиксированной длины до 255 символов. Если длина вставляемой записи меньше, то MySQL автоматически дополняет значение пробелами. Например, если мы указали тип CHAR(10) и сохранили строку «Привет», то по факту в БД будет храниться строка «Привет » (обратите внимание на четыре пробела в конце строки).

Кажется, что это лишняя трата места. Но если во всех ваших записях хранятся строки примерно одной длины, то тип CHAR будет очень производительным. Например, его можно использовать для хранения хешей, у которых длина всегда одинакова.

VARCHAR хранит строки переменной длины до 65 535 символов. Причем в памяти хранится именно та длина, которая была указана при создании. VARCHAR занимает меньше места, чем CHAR, но подвержен фрагментации и из-за этого может проигрывать в скорости обработки данных.

Читать:

X res technology что это

Текстовые и бинарные

Текстовые (TEXT) и бинарные (BLOB) типы данных используются для хранения больших объемов текста или двоичных данных. Эти типы похожи, но отличаются по способу хранения и обработки внутри MySQL.

BLOB обрабатывается как двоичные данные. В нем не хранится набор символов, а операции сортировки и сравнения основаны на числовых значениях байтов.

TEXT обрабатывается как символьные строки. В нем хранится именно набор символов, а значения сортируются и сравниваются на основе сопоставления набора символов..

Текстовые типы отлично подходят для хранения больших текстов: статей, докладов и так далее. А бинарные типы могут хранить любые файлы или мультимедиа-контент.

Тип	Размер (байт)	Макс. размер символов
TINYTEXT / TINYBLOB	255	255
TEXT / BLOB	65 535	65 535
MEDIUMTEXT / MEDIUMBLOB	16 777 215	2 24 -1
LONGTEXT / LONGBLOB	4 294 967 295	2 32 -1

Кажется, что типы TINYTEXT и TEXT похожи на CHAR и VARCHAR. Но разница в том, что MySQL не умеет индексировать текстовые и бинарные типы и не может использовать индексы для сортировки.

Дата/время

Типы данных, которые позволяют работать с датой и временем.

Тип	Размер (байт)	Описание
DATE	3	Только дата в формате YYYY-MM-DD. Допустимые значения от 1000-01-01 до 9999-12-31
DATETIME	8	Дата и время в формате YYYY-MM-DD HH:MM:SS. Допустимые значения от 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP	4	Дата и время. Хранится в виде количества секунд, прошедших с 1 января 1970 года по гринвичу. Занимает в два раза меньше места, чем тип DATETIME. Но при этом диапазон ограничен значениями от 1970-01-01 00:00:01 до 2038-01-09 03:14:07
TIME	3	Только время в формате HH:MM:SS. Допустимые значения от 00:00:00 до 23:59:59
YEAR(N)	1	Только год в формате YYYY или YY. Допустимые значения от 1901 до 2155 или от 70 до 69 (1970 — 2069)

JSON

Это относительно новый тип данных, который появился в MySQL версии 5.7.8. Он позволяет нативно хранить и обрабатывать данные в JSON-формате.

В отличие от хранения объектов в виде текста, в использовании специального типа данных есть несколько преимуществ:

Валидация JSON-объектов. Если попытаться сохранить неправильный JSON, MySQL сгенерирует ошибку.
Возможность нативно работать с JSON, выбирать и обновлять только отдельные части объектов, а не весь объект целиком.
MySQL сохраняет тип JSON в специальном внутреннем формате. Такой способ более производительный, чем работа с JSON в виде строки.

Составные типы

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

ENUM может хранить только одно значение из списка, занимает 1-2 байта.
SET может хранить одновременно до 64 значений из списка, занимает от 1 до 8 байт.

Заключение

Мы рассмотрели основные типы данных и полей в MySQL, объяснили разницу между схожими типами. Теперь вы можете создавать свои структуры БД, используя полученные знания.

Создание базы данных в MySQL

Работа с базами данных в PHP

База данных (БД) — это программа, которая позволяет хранить и обрабатывать информацию в структурированном виде. БД — отдельная независимая программа, которая не входит в язык программирования. В базе данных можно сохранять любую информацию, чтобы позже получать к ней доступ.

Зачем нужны базы данных

Базы данных нужны для хранения информации. Чтобы получить полное понимание необходимости использования БД в современном веб-программировании, необходимо ответить на три вопроса:

Какую информацию и зачем мы храним?
В каком виде и как надо хранить эту информацию?
Как и каким способом можно получить доступ к этой информации?

Предположим, вы решили сделать сайт, где каждый пользователь может вести личный дневник наблюдения за погодой в своем городе. Такой сайт должен иметь как минимум одну форму ввода со следующими полями: город, дата, температура, облачность, погодное явление, и так далее.

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

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

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

Именно для этих целей и существуют базы данных.

Как хранится информация в базе данных

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

�� База данных — это высокоуровневое понятие, которое означает объединение совокупности данных, хранимых для выполнения одной цели.

Если мы делаем современный сайт, то все его данные будут храниться внутри одной базы данных.

Таблица

По отношению к базе данных таблица является вложенным объектом. То есть одна БД может содержать в себе множество таблиц. Аналогией из реального мира может быть шкаф (база данных) внутри которого лежит множество коробок (таблиц).

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

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

Запись

Запись — это строка электронной таблицы. Это неделимая сущность, которая хранится в таблице. Когда мы сохраняем данные веб-формы с сайта, то на самом деле добавляем новую запись в какую-то из таблиц базы данных.

Запись состоит из полей (столбцов) и их значений. Но значения не могут быть какими угодно. Определяя столбец, программист должен указать тип данных, который будет храниться в этом столбце: текстовый, числовой, логический, файловый и так далее. Это нужно для того, чтобы в будущем в базу не были записаны данные неверного типа.

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

Создадим для сайта новую БД и дадим ей название weather_diary .

Создадим в БД новую таблицу с именем weather_log и определим там следующие столбцы:

— Город (тип: текст); — День (тип: дата); — Температура (тип: число); — Облачность (тип: число; от 0 (нет облачности) до 4 (полная облачность)); — Были ли осадки (тип: истина или ложь); — Комментарий (тип: текст).

При сохранении формы будем добавлять в таблицу weather_log новую запись, и заполнять в ней все поля информацией из полей формы.

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

Реляционная база данных

Английское слово relation можно перевести как «связь», «отношение». А определение «реляционные базы данных» означает, что таблицы в этой БД могут вступать в отношения и находиться в связи между собой.

Например, одна таблица может ссылаться на другую таблицу. Это часто требуется, чтобы сократить объём и избежать дублирования информации. В сценарии с дневником погоды пользователь вводит название своего города. Это название сохраняется вместе с погодными данными. Но можно поступить иначе:

Создать новую таблицу с именем cities .
Все города в России известны, поэтому их все можно добавить в одну таблицу.
Переделать форму, изменив поле ввода города с текстового на поле типа select , чтобы пользователь не вписывал город, а выбирал его из списка.
При сохранении погодной записи, в поле для города поставить ссылку на соответствующую запись из таблицы городов.

Так мы решим сразу две задачи:

Сократим объём хранимой информации, так как погодные записи больше не будут содержать название города;
Избежим дублирования: все пользователи будут выбирать один из заранее определённых городов, что исключит опечатки.

Связи между таблицами в БД бывают разных видов. В примере выше использовалась связь типа «один-ко-многим», так как одному городу может соответствовать множество погодных записей, но не наоборот! Бывают связи и других типов: «один-к-одному» и «многие-ко-многим», но они используются значительно реже.

Приходите на курсы в HTML Academy. Серьёзно, у нас хорошо.23

«Доктайп» — журнал о фронтенде. Читайте, слушайте и учитесь с нами.