Описание : в этой статье вы узнаете, как использовать значения интервала MySQL для выполнения арифметики даты и времени.

Введение в интервальные значения MySQL

Значения интервала MySQL используются в основном для расчета даты и времени. Чтобы создать значение интервала, вы используете следующее выражение:

INTERVAL expr unit

За ним следует ключевое слово INTERVAL expr, определяющее значение интервала и unitуказывающее единицу измерения интервала. Например, чтобы создать 1-дневный интервал, вы используете следующее выражение:

INTERVAL 1 DAY

Обратите внимание, что INTERVAL и UNIT без учета регистра, поэтому следующее выражение эквивалентно приведенному выше:

interval 1 day

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

date + INTERVAL expr unit

date - INTERVAL expr unit

Значения интервалов также используются в различных временных функциях, таких как DATE_ADD, DATE_SUB, TIMESTAMPADD и TIMESTAMPDIFF

MySQL определяет стандартные форматы expr и unit как показано в следующей таблице:

Примеры интервалов в MySQL

Следующее утверждение добавляет 1 день к January 1st 2020:

SELECT '2020-01-01' + INTERVAL 1 DAY;

+-------------------------------+

| '2020-01-01' + INTERVAL 1 DAY |

+-------------------------------+

| 2020-01-02                    |

+-------------------------------+

1 row in set (0.01 sec)

Если значение интервала используется в выражении, которое включает значение DATE или значение интервала DATETIME находится справа от выражения, вы можете использовать отрицательное значение expr, как показано в следующем примере:

SELECT '2020-01-01' + INTERVAL -1 DAY;

+--------------------------------+

| '2020-01-01' + INTERVAL -1 DAY |

+--------------------------------+

| 2019-12-31                     |

+--------------------------------+

1 row in set(0.00sec)

Следующий оператор показывает, как использовать DATE_ADD и DATE_SUB, чтобы добавлять/вычитать 1 месяц из значения даты:

SELECT DATE_ADD('2020-01-01', INTERVAL 1 MONTH)1_MONTH_LATER,

DATE_SUB('2020-01-01',INTERVAL 1 MONTH) 1_MONTH_BEFORE;

+---------------+----------------+

| 1_MONTH_LATER | 1_MONTH_BEFORE |

+---------------+----------------+

| 2020-02-01    | 2019-12-01     |

+---------------+----------------+

1 row in set (0.00 sec)

Следующий запрос использует функцию TIMESTAMPADD(unit, interval, expression) для добавления 30 минут к значению временной метки:

SELECT TIMESTAMPADD(MINUTE,30,'2020-01-01') 30_MINUTES_LATER;

+---------------------+

| 30_MINUTES_LATER    |

+---------------------+

| 2020-01-01 00:30:00 |

+---------------------+

1 row in set (0.00 sec)

Практический пример интервала в MySQL

Давайте создать новую таблицу с именем memberships для демонстрации:

CREATE TABLE memberships (

id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(355) NOT NULL,

plan VARCHAR(255) NOT NULL,

expired_date DATE NOT NULL

);

В таблице memberships в столбце expired_date хранится дата окончания членства каждого участника.

Следующий оператор вставляет несколько строк в таблицу memberships.

INSERT INTO memberships(email, plan, expired_date)

VALUES('andreyex@example.ru','Gold','2020-02-13'),

('alex@example.ru','Platinum','2020-02-10'),

('max@example.ru','Silver','2020-02-08');

Предположим, сегодня 2020-02-06 вы можете найти участников, срок действия которых истек в течение 7 дней, с помощью следующего запроса:

SELECT

email,

plan,

expired_date,

DATEDIFF(expired_date, '2020-02-06') remaining_days

FROM

memberships

WHERE

'2020-02-06' BETWEEN DATE_SUB(expired_date, INTERVAL 7 DAY) AND expired_date;

В этом запросе мы использовали функцию DATE_SUB для вычитания истекшей даты на 7 дней, указанных значением интервала (INTERVAL 7 DAY).

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

2020-02-20T08:58:43
База данных MySQL

					

Резюме : в этой статье мы покажем вам различные способы сброса значений автоинкремента столбцов AUTO_INCREMENT в MySQL.

MySQL предоставляет вам полезную функцию под названием автоинкремент . Вы можете присвоить атрибут AUTO_INCREMENT столбцу таблицы, чтобы создать уникальный идентификатор для новой строки. Как правило, вы используете атрибут AUTO_INCREMENT для столбца первичного ключа таблицы.

Всякий раз, когда вы вставляете новую строку в таблицу, MySQL с помощью атрибута AUTO_INCREMENT автоматически присваивает порядковый номер столбцу.

Например, если в таблице восемь строк, и вы вставляете новую строку без указания значения для столбца автоинкремента, MySQL автоматически вставит новую строку id со значением 9.

Иногда вам может понадобиться сбросить значение столбца автоинкремента, чтобы идентификатор первой записи, который вы вставляете в таблицу, начинался с определенного числа, например, 1.

В MySQL вы можете сбросить значения автоинкремента различными способами.

Примеры сбрасывания значения автоматического приращения в MySQL

Сначала создайте таблицу с именем   tmp и присвойте  атрибут AUTO_INCREMENT столбцу id первичного ключа.

CREATE TABLE tmp (

id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(45) DEFAULT NULL,

PRIMARY KEY (id)

);

Во-вторых, вставьте пример данных в таблицу tmp:

INSERT INTO tmp(name)

VALUES('test 1'),

('test 2'),

('test 3');

В- третьих, запрос к таблице tmp для проверки операции вставки:

SELECT

*

FROM

tmp;

У нас есть три строки со значениями столбца ID: 1, 2 и 3. Отлично! Пора попрактиковаться в сбросе значения автоинкремента столбца ID.

Использование инструкции ALTER TABLE

Вы можете сбросить значение автоинкремента с помощью оператора ALTER TABLE. Синтаксис оператора ALTER TABLE  для сброса значения автоинкремента выглядит следующим образом:

ALTER TABLE table_name AUTO_INCREMENT = value;

Вы указываете имя таблицы после оператора ALTER TABLE и имя value, которое вы хотите сбросить в выражении AUTO_INCREMENT=value.

Обратите внимание, что значение value должно быть больше или равно текущему максимальному значению столбца автоинкремента.

Давайте удалим последнюю запись в таблице tmp с id значением 3:

DELETE FROM tmp

WHERE

ID = 3;

Если вы вставите новую строку, MySQL назначит 4  столбцу id новой строки. Однако вы можете сбросить число, сгенерированное MySQL, на 3, используя следующую инструкцию ALTER TABLE:

ALTER TABLE tmp AUTO_INCREMENT = 3;

Теперь давайте попробуем вставим новую строку в таблицу tmp и запросить данные из нее, чтобы увидеть эффект:

INSERT INTO tmp(name)

VALUES ('MySQL example 3');



SELECT

*

FROM

tmp;

У нас есть три строки с последним значением автоинкремента, равным 3 вместо 4, что мы и ожидали.

Использование оператора TRUNCATE TABLE

Оператор TRUNCATE TABLE удаляет все данные из таблицы и сбрасывает значение автоинкремента на ноль.

Следующее иллюстрирует синтаксис оператора TRUNCATE TABLE:

TRUNCATE TABLE table_name;

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

Использование операторов DROP TABLE и CREATE TABLE

Вы можете использовать пару операторов: DROP TABLE и CREATE TABLE, чтобы сбросить столбец автоинкремента. Обратите внимание, что этот метод удаляет все данные из таблицы навсегда.

Как и оператор TRUNCATE TABLE, эти операторы удаляют таблицу и воссоздают ее, поэтому значение автоинкремента сбрасывается на ноль.

DROP TABLE table_name;

CREATE TABLE table_name(...);

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

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

Легко выбрать самую высокую или самую низкую запись в таблице базы данных с помощью функции MAX или MIN. Тем не менее, немного сложно выбрать n- ую самую высокую запись. Например, получить второй самый дорогой продукт из таблицы продуктов.

Чтобы выбрать n- ую наивысшую запись, вам необходимо выполнить следующие шаги:

Сначала вы получаете n самых высоких записей и сортируете их в порядке возрастания.  N- я высокая запись является последней записью в наборе результатов.

Затем вы сортируете набор результатов в порядке убывания и получаете первый.

Ниже приведен запрос для получения n- ых самых высоких записей в порядке возрастания:

SELECT

*

FROM

my_table_name

ORDER BY my_column_name ASC

LIMIT N;

Запрос для получения n- й наивысшей записи выглядит следующим образом:

SELECT

*

FROM

(SELECT

*

FROM

my_table_name

ORDER BY my_column_name ASC

LIMIT N) AS tbl

ORDER BY my_column_name DESC

LIMIT 1;

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

SELECT

*

FROM

my_table_name

ORDER BY my_column_name DESC

LIMIT n - 1, 1;

Запрос возвращает первую строку после n-1 строк, поэтому вы получите n- ю самую высокую запись.

Пример, как получить n-ую самую высокую запись

Например, если вы хотите получить второй самый дорогой продукт (n = 2) в таблице my_products, вы используете следующий запрос:

SELECT

productCode, productName, buyPrice

FROM

my_products

ORDER BY buyPrice DESC

LIMIT 1 , 1;

Вот результат:

Второй способ получить n-ую самую высокую запись — это использование подзапрос в MySQL:

SELECT *

FROM my_table_name ASa

WHERE n - 1 = (

SELECT COUNT(primary_key_column)

FROMmy_productsb

WHERE  b.my_column_name > a. my_column_name)

Используя первый метод, вы можете получить тот же результат, чтобы получить второй самый дорогой продукт, используйте следующий запрос:

SELECT

productCode, productName, buyPrice

FROM

my_products a

WHERE

1 = (SELECT

COUNT(productCode)

FROM

my_products b

WHERE

b.buyPrice > a.buyPrice);

В этой статье мы показали, как выбрать n- ую запись в таблице базы данных с помощью предложения LIMIT в MySQL.

Резюме : в этом руководстве вы изучите различные методы выбора случайных записей из таблицы базы данных в MySQL.

Иногда вам нужно выбрать случайные записи из таблицы, например:

• Выделите несколько случайных постов в блоге и отобразите их на боковой панели.
• Выбор случайной цитаты для отображения виджета «Цитата дня».
• Выбор случайных картинок в галерее и использование в качестве рекомендуемых картинок.

MySQL выбирает случайные записи, используя ORDER BY RAND()

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

Следующий запрос выбирает случайную строку из таблицы базы данных:

SELECT * FROM table_name

ORDER BY RAND()

LIMIT 1;

Давайте рассмотрим запрос более подробно.

• Функция RAND() генерирует случайное значение для каждой строки в таблице.
• Предложение ORDER BY сортирует все строки в таблице по случайному числу, сгенерированному функцией RAND().
• Предложение LIMIT выбирает первую строку в наборе результатов, отсортированных случайным образом.

Если вы хотите выбрать N случайных записей из таблицы базы данных, вам нужно изменить предложение LIMIT следующим образом:

SELECT * FROM table_name

ORDER BY RAND()

LIMIT N;

Смотрите следующую customersтаблицу из примера базы данных.

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

SELECT

customerNumber,

customerName

FROM

customers

ORDER BY RAND()

LIMIT 5;

Обратите внимание, что вы можете получить другой набор результатов, потому что он случайный.

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

Скорость запроса также зависит от количества строк в таблице. Чем больше строк в таблице, тем больше времени требуется для генерации случайного числа для каждой строки.

MySQL выбирает случайные записи, используя предложение INNER JOIN

Этот метод требует, чтобы таблица имела поле первичного ключа с автоинкрементом и в последовательности не было пробела.

Следующий запрос генерирует случайное число на основе столбца первичного ключа:

SELECT

ROUND(RAND() * ( SELECT MAX(id) FROM  table_name)) AS id;

Мы можем объединить таблицу с набором результатов, возвращенным вышеуказанным запросом, следующим образом:

SELECT t.*

FROM table_name AS t

INNER JOIN

(SELECT ROUND(

RAND()*

(SELECT MAX(id) FROM table_NAME )) AS id

) AS x

WHERE

t.id >= x.id

LIMIT 1;

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

Следующий запрос возвращает случайного клиента из таблицы customers.

SELECT

t.customerNumber, t.customerName

FROM

customers AS t

JOIN

(SELECT

ROUND(RAND() * (SELECT

MAX(customerNumber)

FROM

customers)) AS customerNumber

) AS x

WHERE

t.customerNumber >= x.customerNumber

LIMIT 1;

MySQL выбирает случайные записи, используя переменные

В случае, если в таблице есть столбец id со значениями, которые попадают в диапазон, 1..N и в диапазоне нет пропуска, вы можете использовать следующую технику:

• Сначала выберите случайные числа в диапазоне 1..N.
• Во-вторых, выберите записи на основе случайных чисел.

Следующее утверждение поможет вам сделать это:

SELECT

table. *

FROM

(SELECT

ROUND(RAND() * (SELECT

MAX(id)

FROM

table)) random_num,

@num:=@num + 1

FROM

(SELECT @num:=0) AS a, table

LIMIT N) AS b,

table AS t

WHERE

b.random_num = t.id;

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

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

Описание : в этой статье вы узнаете, как эмулировать функцию row_number() в MySQL. Мы покажем вам, как добавить последовательное целое число к каждой строке или группе строк в наборе результатов.

Обратите внимание, что MySQL поддерживает оконную функцию row_number() начиная с версии 8.0. Если вы используете MySQL 8.0 или более позднюю версию, проверьте функцию ROW_NUMBER(). В противном случае вы можете продолжить обучение, чтобы узнать, как эмулировать функцию ROW_NUMBER().

Введение в функцию ROW_NUMBER()

ROW_NUMBER() является функцией окна, которая возвращает порядковый номер для каждой строки, начиная с 1 для первой строки.

До версии 8.0 MySQL не поддерживал такую ROW_NUMBER(), как Microsoft SQL Server, Oracle или PostgreSQL. К счастью, MySQL предоставляет переменные сеанса, которые вы можете использовать для эмуляции функции ROW_NUMBER().

MySQL ROW_NUMBER — добавление номера строки для каждой строки

Чтобы эмулировать функцию ROW_NUMBER(), вы должны использовать переменные сеанса в запросе.

Следующие операторы возвращают пять сотрудников из  employeesтаблицы и добавляют номер строки для каждой строки, начиная с 1.

SET@row_number=0;

SELECT

(@row_number:=@row_number + 1) AS num,

firstName,

lastName

FROM

employees

ORDER BY firstName, lastName

LIMIT 5;

В этом примере:

• Сначала определите именованную переменную @row_number и установите ее значение равным 0. Переменная сеанса @row_number, указанная с префиксом @.
• Затем выберите данные из таблицы employees и увеличьте значение переменной @row_number на единицу для каждой строки. Мы используем предложение LIMIT, чтобы ограничить количество возвращаемых строк до пяти.

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

SELECT

(@row_number:=@row_number + 1) AS num,

firstName,

lastName

FROM

employees,

(SELECT @row_number:=0) AS t

ORDER BY

firstName,

lastName

LIMIT 5;

Обратите внимание, что производная таблица должна иметь свой собственный псевдоним, чтобы сделать запрос синтаксически правильным.

MySQL ROW_NUMBER- добавление номера строки в каждую группу

Как насчет функциональности ROW_NUMBER() OVER(PARITION BY … )? Например, что делать, если вы хотите добавить номер строки в каждую группу, и он сбрасывается для каждой новой группы.

Давайте посмотрим на таблицу payments из примера базы данных :

SELECT

customerNumber,

paymentDate,

amount

FROM

payments

ORDER BY

customerNumber;

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

Чтобы достичь этого, вы должны использовать две переменные сеанса, одну для номера строки, а другую для хранения старого номера клиента, чтобы сравнить ее с текущей в качестве следующего запроса:

set @row_number := 0;



SELECT

@row_number:=CASE

WHEN@customer_no=customerNumber

THEN @row_number + 1

ELSE 1

END AS num,

@customer_no:=customerNumber customerNumber,

paymentDate,

amount

FROM

payments

ORDER BY customerNumber;

В этом примере мы используем выражение CASE в запросе. Если номер клиента остается неизменным, мы увеличиваем переменную @row_number, в противном случае мы сбрасываем ее до единицы.

Этот запрос использует производную таблицу и перекрестное соединение для получения одинакового результата.

SELECT

@row_number:=CASE

WHEN@customer_no=customerNumber

THEN

@row_number + 1

ELSE

1

END AS num,

@customer_no:=customerNumber CustomerNumber,

paymentDate,

amount

FROM

payments,

(SELECT @customer_no:=0,@row_number:=0) as t

ORDER BY

customerNumber;

В этой статье вы узнали два способа эмулировать оконную функцию row_number в MySQL.