Работа с датой и временем

Разбить на страницы

Показывать лекцию целиком

Работа с датой

Файл для работы с этой лекцией - скачать.

До сих пор мы имели дело с текстом ( varchar ) и числами ( int ). Чтобы понять тип данных date (дата) создадим еще одну таблицу, аналогично тому, как была создана первая таблица.

Создадим в текстовом редакторе файл employee_per.dat, который содержит оператор создания таблицы CREATE следующего вида:

CREATE TABLE employee_per (
e_id int unsigned not null primary key,  -- идентификационный номер
address varchar(60),                     -- адрес
phone int,                               -- номер телефона
p_email varchar(60),                     -- адрес e-mail
birth_date DATE,                         -- дата рождения
sex ENUM('M', 'F'),                      -- пол
m_status ENUM('Y','N'),                  -- статус
s_name varchar(40),                      -- имя
children int);                           -- количество детей

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

INSERT INTO employee_per (e_id, address, phone, p_email, 
birth_date, sex, m_status, s_name, children) values (1,
 'Арбат, 12', 7176167, 'anna@yandex.ru', '1972-03-16', 
'F', 'Y', 'Анна Петрова', 2);

Затем загрузим этот файл, как мы делали раньше, в базу данных.

В системе Windows

1). Поместите файл в каталог c:\mysql\bin.

2). Выполните в приглашении DOS команду.

dosprompt> mysql employees <employee_per.dat

3). Запустите программу клиента mysql и проверьте, что таблица была создана, с помощью команды SHOW TABLES;.

В системе Linux

1). Перейдите в каталог, в котором находится файл.

2). В приглашении введите следующую команду:

$prompt> mysql employees <employee_per.dat -u your_username -p

3). Проверьте, что таблица была создана с помощью команды SHOW TABLES; в программе клиента mysql.

Данные таблицы можно вывести с помощью команды DESCRIBE.

mysql> DESCRIBE employee_per;

Результат запроса представлен на рис. 9.1.

(рис 9.1) Просмотр данных таблицы

Обратите внимание, что столбец birth_date имеет тип столбца date (дата). Здесь также присутствует еще один новый тип столбца ENUM, который был рассмотрен в лекции 4.

e_id: идентификатор сотрудника, такой же как в таблице employee_data

address: адрес сотрудника

phone: номер телефона

p_email: личный адрес e-mail

birth_date: дата рождения

sex: Пол сотрудника, мужской (M) или женский (F)

m_status: семейное положение, в браке (Y) или холост (N).

s_name: Имя супруга ( NULL, если сотрудник холост)

children: Число детей ( NULL, если детей нет)

Особенности типа данных Date

Даты в MySQL всегда представлены с годом, за которым следует месяц и затем день месяца. Даты часто записывают в виде YYYY-MM-DD, где YYYY -- 4 цифры года, MM -- 2 цифры месяца и DD -- 2 цифры дня месяца.

Операции с датами

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

Использование операторов = и !=

select p_email, phone from employee_per where birth_date = '1969-12-31';

Результат запроса представлен на рис. 9.2.

(рис 9.2) Поиск по дате рождения

Примечание: MySQL требует, чтобы даты были заключены в кавычки.

Использование операторов >= и <=

select e_id, birth_date from employee_per where birth_date >= '1970-01-01';

Результат запроса представлен на рис. 9.3.

(рис 9.3) Поиск по дате рождения с использованием оператора >=

Определение диапазонов

select e_id, birth_date
from employee_per where
birth_date BETWEEN
'1969-01-01' AND '1974-01-01';

Результат запроса представлен на рис. 9.4.

(рис 9.4) Поиск по дате рождения в определенном диапазоне

Тот же запрос можно представить без конструкции BETWEEN:

select e_id, birth_date
from employee_per where
birth_date >= '1969-01-01' AND birth_date <= '1974-01-01';

Результат запроса будет аналогичен рис. 9.4.

Задания

Вывести идентификаторы и даты рождения всех сотрудников, которые родились до 1965 г.

Вывести идентификаторы и даты рождения сотрудников, родившихся между 1970 и 1973 гг.

Возможные решения

mysql> select e_id, birth_date
    -> from employee_per
    -> where birth_date <= '1964-12-31';

mysql> select e_id, birth_date
    -> from employee_per
    -> where birth_date >= '1970-01-01'
    -> and birth_date <= '1972-12-31';

Результат запроса представлен на рис. 9.5.

(рис 9.5) Поиск по дате рождения в определенном диапазоне

Использование Date для сортировки данных

select e_id, birth_date
from employee_per
ORDER BY birth_date;

Результат запроса представлен на рис. 9.6.

(рис 9.6) Поиск по дате рождения в определенном диапазоне

Выбор данных с помощью Date

Вот как можно выбрать сотрудников, которые родились в марте.

select e_id, birth_date
from employee_per
where MONTH(birth_date) = 3;

Результат запроса представлен на рис. 9.7.

(рис 9.7) Поиск по месяцу

Можно также использовать вместо чисел названия месяцев.

select e_id, birth_date
from employee_per
where MONTHNAME(birth_date) = 'January';

Результат запроса представлен на рис. 9.8.

(рис 9.8) Использование названия месяца в поиске

Будьте внимательны при использовании названий месяцев, так как они различают регистр символов. Поэтому January будет работать, а JANUARY не будет!

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

select e_id, birth_date
from employee_per
where year(birth_date) = 1972;

Результат запроса представлен на рис. 9.9.

(рис 9.9) Поиск по году рождения

select e_id, birth_date
from employee_per
where DAYOFMONTH(birth_date) = 20;

Результат запроса представлен на рис. 9.10.

(рис 9.10) Поиск по дате рождения

Текущие даты

Ранее мы видели, что текущую дату, месяц и год можно вывести с помощью аргумента CURRENT_DATE предложений DAYOFMONTH(), MONTH() и YEAR(), соответственно. То же самое можно использовать для выборки данных из таблиц.

select e_id, birth_date
from employee_per where
MONTH(birth_date) = MONTH(CURRENT_DATE);

Результат запроса представлен на рис. 9.11.

(рис 9.11) Поиск по текущему месяцу

Задания

Вывести идентификаторы, даты рождения и адреса e-mail сотрудников, родившихся в апреле.

Вывести идентификаторы, даты рождения и имена супругов сотрудников, родившихся в 1968 г., и отсортируйте записи на основе имен их супругов.

Выведите идентификаторы сотрудников, родившихся в текущем месяце.

Сколько в базе данных имеется уникальных годов рождения?

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

Сколько сотрудников родились в каждом месяце? Выдача должна содержать названия месяцев (не номера), и записи должны быть упорядочены по убыванию по месяцам, начиная от наибольшего номера.

Возможные решения

mysql> select e_id, birth_date, p_email
    -> from employee_per
    -> where month(birth_date) = 4;

Результат запроса представлен на рис. 9.12.

(рис 9.12) Сотрудники, родившиеся в апреле

или

mysql> select e_id, birth_date, p_email
    -> from employee_per
    -> where MONTHNAME(birth_date) = 'April';

Результат аналогичен рис. 9.12.

mysql> select e_id, birth_date, s_name
    -> from employee_per where
    -> YEAR(birth_date) = 1968
    -> ORDER BY s_name;

Результат запроса представлен на рис. 9.13.

(рис 9.13) Сотрудники 1968 года рождения, с сортировкой по именам

Примечание: Значение NULL показано в самом верху. Более подробно значение NULL будет рассмотрено ниже.

mysql> select e_id from employee_per
    -> where month(birth_date) = month(current_date);

Результат запроса представлен на рис. 9.14.

(рис 9.14) Сотрудники родившиеся в этом месяце

mysql> select distinct year(birth_date) from employee_per;

Результат запроса представлен на рис. 9.15.

(рис 9.15) Уникальные годы рождения

mysql> select year(birth_date) as Year,
    -> count(*) from employee_per
    -> GROUP BY Year;

Результат запроса представлен на рис. 9.16.

(рис 9.16) Количество сотрудников, родившихся в каждом году

mysql> select MONTHNAME(birth_date) AS Month,
    -> count(*) AS Number
    -> from employee_per
    -> GROUP BY Month
    -> ORDER BY Number DESC;

Результат запроса представлен на рис. 9.17.

(рис 9.17) Количество сотрудников, родившихся в каждом месяце

Тип столбца Null

В этой лекции мы уже встречались с типом NULL. Теперь рассмотрим его подробнее.

Тип столбца NULL является специальным значением. Чтобы вставить значение NULL, удалите просто имя столбца из оператора INSERT. Столбцы содержат NULL по умолчанию, если только не определены как NOT NULL. Значение null может использоваться для целочисленных, а также текстовых или двоичных данных.

NULL нельзя сравнивать с помощью арифметических операторов. Сравнение для NULL можно делать с помощью IS NULL или IS NOT NULL.

select e_id, children
from employee_per
where children IS NOT NULL;

Результат запроса представлен на рис. 9.18.

(рис 9.18) Сотрудники, имеющие детей

Приведенная выше команда выводит идентификаторы и количество детей сотрудников, у которых есть дети.

Задания

Найти и вывести идентификаторы и имена супругов всех сотрудников, которые состоят в браке.

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

Сколько имеется сотрудников каждого пола (мужчин и женщин)?

Сколько сотрудников состоят в браке, и сколько холостых?

Найдите общее число детей.

Сделайте уникальные группы по количеству детей и определите число детей каждой группы. Отсортируйте вывод групп по убыванию по количеству детей.

Возможные решения

mysql> select e_id, s_name
    -> from employee_per
    -> where m_status = 'Y';

или

mysql> select e_id, s_name
    -> from employee_per
    -> where s_name IS NOT NULL;

mysql> select e_id, s_name
    -> from employee_per
    -> where m_status = 'Y'
    -> ORDER BY s_name;


   mysql> select e_id, s_name
    -> from employee_per
    -> where s_name IS NOT NULL
    -> ORDER BY s_name;

mysql> select sex, count(*)
    -> from employee_per
    -> GROUP BY sex;

mysql> select m_status, count(*)
    -> from employee_per
    -> GROUP BY m_status;

mysql> select sum(children) from employee_per;

mysql> select children, count(*) AS
    -> number from employee_per
    -> GROUP BY children
    -> ORDER BY number DESC;

Страницы: