Изменение записей

Команда UPDATE выполняет изменение данных в таблицах. Она имеет очень простой формат.

UPDATE имя_таблицы SET имя_столбца_1 = значение_1, имя_столбца_2 = значение_2, имя_столбца_3 = значение_3, ... [WHERE условия];

Как и все другие команды SQL можно вводить ее на одной строке или на нескольких строках.

Рассмотрим несколько примеров.

Предположим, директор увеличил свою зарплату на 20000 и надбавки на 5000. Его предыдущая зарплата была 200000, а надбавки были 50000.

UPDATE employee_data SET salary=220000, perks=55000 WHERE title='директор';

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

Рис. 6.15. Данные обновлены

Можно проверить эту операцию, выводя данные из таблицы.

select salary, perks from employee_data WHERE title = 'директор';

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

Рис. 6.16. Данные обновлены

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

UPDATE employee_data SET salary = salary + 20000, perks = perks + 5000 WHERE title='директор';

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

В качестве другого примера можно попробовать изменить название должности "разработчик Web" на "программист Web".

mysql> update employee_data SET -> title = 'программист Web' -> WHERE title = 'разработчик Web';

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

Рис. 6.17. Данные обновлены

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

Извлечение подмножеств

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

Общая форма оператора LIMIT имеет следующий вид:

SELECT (что-нибудь) from таблица LIMIT начальная строка, извлекаемое число записей;

SELECT f_name, l_name from employee_data LIMIT 6,3;

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

Рис. 6.11. Извлечение трёх строк начиная с 6-й

Будут извлечены три строки, начиная с шестой.

Ключевое слово DISTINCT

Рассмотрим теперь, как выбрать и вывести записи таблиц MySQL с помощью ключевого слова DISTINCT (РАЗЛИЧНЫЙ), использование которого исключает появление повторяющихся данных.

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

select title from employee_data;

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

Рис. 6.12. Все должности базы данных компании

Можно видеть, что список содержит повторяющиеся данные. Предложение SQL DISTINCT выводит только уникальные данные. Вот как оно используется.

select DISTINCT title from employee_data;

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

Рис. 6.13. Все должности базы данных компании без повторов

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

Уникальные записи можно также отсортировать с помощью ORDER BY.

select DISTINCT age from employee_data ORDER BY age;

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

Рис. 6.14. Значения возраста сотрудников компании без повторов

DISTINCT часто используется вместе с функцией COUNT, которая будет рассмотрена далее.

Ограничение количества извлекаемых данных

Далее рассмотрим, как ограничить число записей, выводимых оператором SELECT.

По мере увеличения таблиц возникает необходимость вывода только подмножества данных. Это можно добиться с помощью предложения LIMIT.

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

SELECT f_name, l_name from employee_data LIMIT 5;

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

Рис. 6.8. Имена первых пяти сотрудников

Это первые пять записей таблицы.

Можно соединить оператор LIMIT с оператором ORDER BY. Таким образом, следующий оператор выведет четверых самых старых сотрудников компании.

SELECT f_name, l_name, age from employee_data ORDER BY age DESC LIMIT 4;

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

Рис. 6.9. Четверо самых старых сотрудников компании

Аналогично можно вывести двух самых младших сотрудников.

SELECT f_name, l_name, age from employee_data ORDER BY age LIMIT 2;

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

Рис. 6.10. Двое самых младших сотрудников

Операторы IN и BETWEEN

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

SELECT f_name, l_name, title from -> employee_data where -> title = 'разработчик Web' OR -> title = 'системный адм.';

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

Рис. 6.6. Поиск сотрудников, которые являются разработчиками Web или системными администраторами

В SQL имеется более простой способ сделать это с помощью оператора IN (в множестве). Его использование не представляет никаких трудностей.

SELECT f_name, l_name, title from -> employee_data where title -> IN ('разработчик Web', 'системный адм.');

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

Использование NOT перед IN позволяет вывести данные, которые не входят в множество, определяемое условием IN. Следующий оператор выводит список сотрудников, которые не занимают должность программиста или системного администратора.

SELECT f_name, l_name, title from -> employee_data where title NOT IN -> ('программист', 'системный адм.');

Оператор BETWEEN используется для определения целочисленных границ. Поэтому вместо age >= 32 AND age <= 40 можно использовать age BETWEEN 32 AND 40.

select f_name, l_name, age from -> employee_data where age BETWEEN -> 32 AND 40;

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

Рис. 6.7. Поиск сотрудников, возраст которых лежит в промежутке от 32-х до 40 лет

NOT также можно использовать вместе с BETWEEN, как в следующем операторе, который выводит сотрудников, зарплата которых меньше 90000 или больше 150000.

select f_name, l_name, salary -> from employee_data where salary -> NOT BETWEEN -> 90000 AND 150000;

Упорядочивание данных

Рассмотрим вопрос о том, как можно изменить порядок вывода данных, извлеченных из таблиц MySQL, используя предложение ORDER BY оператора SELECT.

Извлекаемые до сих пор данные всегда выводились в том порядке, в котором они были сохранены в таблице. В действительности SQL позволяет сортировать извлеченные данные с помощью предложения ORDER BY. Это предложение требует имя столбца, на основе которого будут сортироваться данные. Давайте посмотрим, как можно вывести имена сотрудников с упорядоченными по алфавиту фамилиями сотрудников (в возрастающем порядке).

SELECT l_name, f_name from employee_data ORDER BY l_name;

А вот так сотрудников можно отсортировать по возрасту.

SELECT f_name, l_name, age from employee_data ORDER BY age;

Предложение ORDER BY может сортировать в возрастающем порядке (ASCENDING или ASC) или в убывающем порядке (DESCENDING или DESC) в зависимости от указанного аргумента.

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

SELECT f_name from employee_data ORDER by f_name DESC;

Примечание: Возрастающий порядок (ASC) используется по умолчанию.

и фамилии всех сотрудников, которые

1. Вывести имена и фамилии всех сотрудников, которые получают зарплату не более 90000 и не являются программистами, старшими программистами или программистами мультимедиа.
2. Что делает следующий оператор?
SELECT l_name, f_name from employee_data where title NOT LIKE '%продавец%' AND age < 30;
3. Вывести все идентификационные номера и имена сотрудников в возрасте от 32 до 40 лет.
4. Выберите имена всех сотрудников в возрасте 32 лет, которые не являются программистами.

1. Найдите всех сотрудников, которые занимают должность "старший программист" и "программист мультимедиа".
2. Выведите список имен сотрудников, зарплата которых составляет от 70000 до 90000.
3. Что делает следующий оператор?
SELECT f_name, l_name, title from employee_data where title NOT IN ('программист', 'старший программист', 'программист мультимедиа');
4. Вот более сложный оператор, который объединяет BETWEEN и IN. Что он делает?
SELECT f_name, l_name, title, age from employee_data where title NOT IN ('программист', 'старший программист', 'программист мультимедиа') AND age NOT BETWEEN 28 and 32;

1. Вывести список сотрудников в порядке, определяемом зарплатой, которую они получают.
2. Выведите список сотрудников в убывающем порядке их стажа работы в компании.
3. Что делает следующий оператор?
SELECT emp_id, l_name, title, age from employee_data ORDER BY title DESC, age ASC;
4. Вывести список сотрудников (фамилию и имя), которые занимают должность "программист" или "разработчик Web" и отсортировать их фамилии по алфавиту.

Найдите имена 5 самых молодых сотрудников компании.Извлеките 5 записей, начиная с 10 строки.Выведите имена и зарплату сотрудников, которые получают самую большую зарплату.Что делает следующий оператор?
SELECT emp_id, age, perks from employee_data ORDER BY perks DESC LIMIT 10;

Сколько уникальных вариантов зарплаты имеется в компании BigFoot? Представьте их в убывающем порядке.Сколько различных имен имеется в базе данных?

Измените фамилию Чащина на Петрова. Внесите соответствующие изменения в базу данных.Название должности "программист мультимедиа" необходимо изменить на "специалист по мультимедиа".Увеличьте зарплату всем сотрудниками (кроме директора) на 10000.

1. Вывести имена и фамилии всех сотрудников, которые получают зарплату не более 90000 и не являются программистами, старшими программистами или программистами мультимедиа.
2. Что делает следующий оператор?
SELECT l_name, f_name from employee_data where title NOT LIKE '%продавец%' AND age < 30;
3. Вывести все идентификационные номера и имена сотрудников в возрасте от 32 до 40 лет.
4. Выберите имена всех сотрудников в возрасте 32 лет, которые не являются программистами.

1. Найдите всех сотрудников, которые занимают должность "старший программист" и "программист мультимедиа".
2. Выведите список имен сотрудников, зарплата которых составляет от 70000 до 90000.
3. Что делает следующий оператор?
SELECT f_name, l_name, title from employee_data where title NOT IN ('программист', 'старший программист', 'программист мультимедиа');
4. Вот более сложный оператор, который объединяет BETWEEN и IN. Что он делает?
SELECT f_name, l_name, title, age from employee_data where title NOT IN ('программист', 'старший программист', 'программист мультимедиа') AND age NOT BETWEEN 28 and 32;

1. Вывести список сотрудников в порядке, определяемом зарплатой, которую они получают.
2. Выведите список сотрудников в убывающем порядке их стажа работы в компании.
3. Что делает следующий оператор?
SELECT emp_id, l_name, title, age from employee_data ORDER BY title DESC, age ASC;
4. Вывести список сотрудников (фамилию и имя), которые занимают должность "программист" или "разработчик Web" и отсортировать их фамилии по алфавиту.

Найдите имена 5 самых молодых сотрудников компании.Извлеките 5 записей, начиная с 10 строки.Выведите имена и зарплату сотрудников, которые получают самую большую зарплату.Что делает следующий оператор?
SELECT emp_id, age, perks from employee_data ORDER BY perks DESC LIMIT 10;

Сколько уникальных вариантов зарплаты имеется в компании BigFoot? Представьте их в убывающем порядке.Сколько различных имен имеется в базе данных?

Измените фамилию Чащина на Петрова. Внесите соответствующие изменения в базу данных.Название должности "программист мультимедиа" необходимо изменить на "специалист по мультимедиа".Увеличьте зарплату всем сотрудниками (кроме директора) на 10000.

Группировка данных

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

select title from employee_data GROUP BY title;

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

Рис. 7.20. Все уникальные должности сотрудников

Можно видеть, что это аналогично использованию DISTINCT в предыдущей лекции.

Вот как можно подсчитать число сотрудников имеющих определенную должность.

select title, count(*) from employee_data GROUP BY title;

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

Рис. 7.21. Количество сотрудников по должностям

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

Именование столбцов

MySQL позволяет задавать имена для выводимых столбцов. Поэтому вместо f_name или l_name и т.д. можно использовать более понятные и наглядные термины. Это делается с помощью оператора AS.

select avg(salary) AS 'Средняя зарплата' from employee_data;

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

Рис. 7.16. Вывод средней зарплаты с использованием псевдо-имен столбцов.

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

select (SUM(perks)/SUM(salary) * 100) AS 'Процент надбавок' from employee_data;

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

Рис. 7.17. Вывод процента зарплаты, которую сотрудники получают в качестве надбавок с использованием псевдо-имен

Максимальное значение

select MAX(salary) from employee_data;

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

Рис. 7.2. Поиск максимальной зарплаты

Минимальное значение

select MIN(salary) from employee_data;

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

Рис. 7.1. Поиск минимальной зарплаты

Подсчет числа записей

Агрегатная функция COUNT() подсчитывает и выводит общее число записей. Например, чтобы подсчитать общее число записей в таблице, выполните следующую команду.

select COUNT(*) from employee_data;

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

Рис. 7.18. Общее количество записей

Как мы уже знаем, знак * означает "все данные".

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

select COUNT(*) from employee_data where title = 'программист';

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

Рис. 7.19. Общее количество сотрудников-программистов

Поиск минимального и максимального значений

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

SQL имеет 5 агрегатных функций.

MIN(): минимальное значениеMAX(): максимальное значениеSUM(): сумма значенийAVG(): среднее значенийCOUNT(): подсчитывает число записей

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

Сортировка данных

Теперь давайте найдем и выведем число сотрудников, имеющих различные должности, и отсортируем их с помощью ORDER BY.

select title, count(*) AS Number from employee_data GROUP BY title ORDER BY Number;

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

Рис. 7.22. Количество сотрудников по должностям с сортировкой

Суммирование значений столбца с помощью функции SUM

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

Давайте посмотрим, сколько компания BigFoot тратит на зарплату своих сотрудников.

select SUM(salary) from employee_data;

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

Рис. 7.7. Сумма всех зарплат

Аналогично можно вывести общую сумму надбавок, выдаваемых сотрудникам.

select SUM(perks) from employee_data;

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

Рис. 7.8. Сумма всех надбавок

Можно найти также общую сумму зарплаты и надбавок.

select sum(salary) + sum(perks) from employee_data;

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

Рис. 7.9. Общая сумма зарплаты и надбавок

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

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

1. mysql> select MIN(perks) from employee_data;

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

Рис. 7.3. Минимальные надбавки

mysql> select MAX(salary) from employee_data -> where title = "программист";

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

Рис. 7.4. Максимальная зарплата среди программистов

3. mysql> select MAX(age) from employee_data -> where title = 'продавец';

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

Рис. 7.5. Возраст самого старого продавца

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

mysql> select f_name, l_name, age -> from employee_data -> order by age DESC limit 1;

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

Рис. 7.6. Имя и фамилия самого старого сотрудника

1. mysql> select SUM(age) from employee_data;

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

Рис. 7.12. Сумма всех возрастов сотрудников

2. mysql> select SUM(yos) from employee_data;

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

Рис. 7.13. Общее количество лет стажа работы сотрудников

3. mysql> select SUM(salary), AVG(age) -> from employee_data where -> title = 'программист';

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

Рис. 7.14. Сумма зарплат и средний возраст программистов

4. Этот оператор выводит процент зарплаты, которую сотрудники BigFoot получают в качестве надбавок.

mysql> select (SUM(perks)/SUM(salary) * 100) -> from employee_data;

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

Рис. 7.15. Процент зарплаты, которую сотрудники получают в качестве надбавок

1. mysql> select count(*) from employee_data -> where yos >= 4;

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

Рис. 7.23. Число сотрудников, которые проработали более трех лет

2. mysql> select age, count(*) -> from employee_data -> GROUP BY age;

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

Рис. 7.24. Количество сотрудников в группах одного возраста

3. mysql> select age, count(*) -> from employee_data -> GROUP BY age -> ORDER by age DESC;

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

Рис. 7.25. Количество сотрудников в группах одного возраста с обратной сортировкой

4. mysql> select title, AVG(age) -> from employee_data -> GROUP BY title;

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

Рис. 7.26. Средний возраст сотрудников по должностям

5. mysql> select title, AVG(age) -> AS 'средний возраст' -> from employee_data -> GROUP BY title -> ORDER BY 'средний возраст' DESC;

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

Рис. 7.27. Средний возраст сотрудников по должностям с использованием псевдо-имени столбца

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

1. mysql> select MIN(perks) from employee_data;

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

Рис. 7.3. Минимальные надбавки

mysql> select MAX(salary) from employee_data -> where title = "программист";

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

Рис. 7.4. Максимальная зарплата среди программистов

3. mysql> select MAX(age) from employee_data -> where title = 'продавец';

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

Рис. 7.5. Возраст самого старого продавца

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

mysql> select f_name, l_name, age -> from employee_data -> order by age DESC limit 1;

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

Рис. 7.6. Имя и фамилия самого старого сотрудника

1. mysql> select SUM(age) from employee_data;

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

Рис. 7.12. Сумма всех возрастов сотрудников

2. mysql> select SUM(yos) from employee_data;

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

Рис. 7.13. Общее количество лет стажа работы сотрудников

3. mysql> select SUM(salary), AVG(age) -> from employee_data where -> title = 'программист';

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

Рис. 7.14. Сумма зарплат и средний возраст программистов

4. Этот оператор выводит процент зарплаты, которую сотрудники BigFoot получают в качестве надбавок.

mysql> select (SUM(perks)/SUM(salary) * 100) -> from employee_data;

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

Рис. 7.15. Процент зарплаты, которую сотрудники получают в качестве надбавок

1. mysql> select count(*) from employee_data -> where yos >= 4;

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

Рис. 7.23. Число сотрудников, которые проработали более трех лет

2. mysql> select age, count(*) -> from employee_data -> GROUP BY age;

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

Рис. 7.24. Количество сотрудников в группах одного возраста

3. mysql> select age, count(*) -> from employee_data -> GROUP BY age -> ORDER by age DESC;

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

Рис. 7.25. Количество сотрудников в группах одного возраста с обратной сортировкой

4. mysql> select title, AVG(age) -> from employee_data -> GROUP BY title;

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

Рис. 7.26. Средний возраст сотрудников по должностям

5. mysql> select title, AVG(age) -> AS 'средний возраст' -> from employee_data -> GROUP BY title -> ORDER BY 'средний возраст' DESC;

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

Рис. 7.27. Средний возраст сотрудников по должностям с использованием псевдо-имени столбца

Вычисление среднего значения

Агрегатная функция AVG() используется для вычисления среднего значения данных в столбце.

select avg(age) from employee_data;

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

Рис. 7.10. Средний возраст сотрудников

Пример выше вычисляет средний возраст сотрудников компании BigFoot, а следующий выводит среднюю зарплату.

select avg(salary) from employee_data;

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

Рис. 7.11. Средняя зарплата сотрудников

Найдите максимальную зарплату среди всех

Найдите минимальные надбавки. Найдите максимальную зарплату среди всех "программистов".Найдите возраст самого старого "продавца".Найдите имя и фамилию самого старого сотрудника.

Вывести сумму всех возрастов сотрудников, работающих в компании BigFoot.Как вычислить общее количество лет стажа работы сотрудников в компании BigFoot?Вычислите сумму зарплат и средний возраст сотрудников, которые занимают должность "программист".Что делает следующий оператор?
select (SUM(perks)/SUM(salary) * 100) from employee_data;

Подсчитайте число сотрудников, которые проработали в BigFoot более трех лет.Подсчитайте количество сотрудников в группах одного возраста.Измените предыдущее задание так, чтобы возраст выводился в убывающем порядке.Найдите средний возраст сотрудников в различных подразделениях (должностях).Измените предыдущий оператор так, чтобы данные выводились в убывающем порядке среднего возраста.

Найдите минимальные надбавки. Найдите максимальную зарплату среди всех "программистов".Найдите возраст самого старого "продавца".Найдите имя и фамилию самого старого сотрудника.

Вывести сумму всех возрастов сотрудников, работающих в компании BigFoot.Как вычислить общее количество лет стажа работы сотрудников в компании BigFoot?Вычислите сумму зарплат и средний возраст сотрудников, которые занимают должность "программист".Что делает следующий оператор?
select (SUM(perks)/SUM(salary) * 100) from employee_data;

Подсчитайте число сотрудников, которые проработали в BigFoot более трех лет.Подсчитайте количество сотрудников в группах одного возраста.Измените предыдущее задание так, чтобы возраст выводился в убывающем порядке.Найдите средний возраст сотрудников в различных подразделениях (должностях).Измените предыдущий оператор так, чтобы данные выводились в убывающем порядке среднего возраста.

Математические функции MySQL

Описанные ниже функции выполняют различные математические операции. В качестве аргументов большинство из них принимает числа с плавающей запятой и возвращает результат аналогичного типа.

ABS (число)

Эта функция возвращает модуль числа

На рис. 8.1(а) и 8.1(б) приведены примеры работы с функцией ABS.

Рис. 8.1(а). Модуль числа

Рис. 8.1(б). Модуль числа

ASIN (число)

Эта функция возвращает арксинус числа. Диапазон допустимых значений – от -1 до 1. Вне этого диапазона значение арксинуса не определено.

На рис. 8.2(а), 8.2(б) и 8.2(в) приведены примеры работы с функцией ASIN.

Рис. 8.2(а). Арксинус числа

Рис. 8.2(б). Арксинус числа

Рис. 8.2(в). Арксинус числа

ACOS (число)

Эта функция возвращает арккосинус числа. Диапазон допустимых значений – от 1 до 1. Вне этого диапазона значение арккосинуса не определено.

На рис. 8.3(а), 8.3(б) и 8.3(в) приведены примеры работы с функцией ACOS.

Рис. 8.3(а). Арккосинус числа

Рис. 8.3(б). Арккосинус числа

Рис. 8.3(в). Арккосинус числа

ATAN (число)

Эта функция возвращает арктангенс числа.

На рис. 8.4(а), 8.4(б) и 8.4(в) приведены примеры работы с функцией ATAN.

Рис. 8.4(а). Арктангенс числа

Рис. 8.4(б). Арктангенс числа

Рис. 8.4(в). Арктангенс числа

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

select e_id, birth_date from employee_per ORDER BY birth_date;

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

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

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

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

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

Рис. 9.2. Поиск по дате рождения

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

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 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.

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

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

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

До сих пор мы имели дело с текстом (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', 'M', '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, если детей нет)

Текущие даты

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

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

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

Рис. 9.11. Поиск по текущему месяцу

Тип столбца 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, birth_date -> from employee_per -> and 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. Поиск по дате рождения в определенном диапазоне

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. Количество сотрудников, родившихся в каждом месяце

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 -> число from employee_per -> GROUP BY children -> ORDER BY number DESC;

mysql> select e_id, birth_date -> from employee_per -> and 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. Поиск по дате рождения в определенном диапазоне

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. Количество сотрудников, родившихся в каждом месяце

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;

Выбор данных с помощью 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. Поиск по дате рождения

и даты рождения всех сотрудников,

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

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

Найти и вывести идентификаторы и имена супругов всех сотрудников, которые состоят в браке.Изменить предыдущее задание так, чтобы вывод был отсортирован по именам супругов.Сколько имеется сотрудников каждого пола (мужчин и женщин)?Сколько сотрудников состоят в браке, и сколько холостых?Найдите общее число детей.Сделайте уникальные группы по количеству детей и определите число детей каждой группы. Отсортируйте вывод групп по убыванию по количеству детей.

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

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

Найти и вывести идентификаторы и имена супругов всех сотрудников, которые состоят в браке.Изменить предыдущее задание так, чтобы вывод был отсортирован по именам супругов.Сколько имеется сотрудников каждого пола (мужчин и женщин)?Сколько сотрудников состоят в браке, и сколько холостых?Найдите общее число детей.Сделайте уникальные группы по количеству детей и определите число детей каждой группы. Отсортируйте вывод групп по убыванию по количеству детей.