- •Концепции баз данных Системы управления файлами
- •Иерархические базы данных
- •Сетевые базы данных
- •Реляционные базы данных
- •Объектно-ориентированные базы данных
- •Объектно-реляционные базы данных
- •Инсталляция с помощью менеджера пакетов RedHat Linux
- •Загрузка MySql
- •Проверка исходных требований
- •Выбор версии
- •Инсталляция в Windows
- •Инсталляция вручную
- •Компиляция программы
- •Предоставление привилегий
- •Создание базы данных в Windows
- •Создание базы данных в Linux
- •Команда create database
- •Работа с таблицами
- •Использование базы данных
- •Создание таблицы
- •Синтаксис команды create table
- •Удаление таблиц
- •Синтаксис команды drop table
- •Числовые типы данных
- •Типы данных даты и времени
- •Типы данных datetime, date и timestamp
- •Тип данных time
- •Тип данных year
- •Символьные типы данных
- •Типы данных char и varchar
- •Типы данных blob и text
- •Тип перечисления enum
- •Тип множества set
- •Выбор правильного типа данных в столбце
- •Требования к памяти для различных типов столбцов
- •Запись данных в таблицы
- •Задания
- •Возможные решения
- •Выборка данных с помощью условий
- •Операторы больше и меньше
- •Задания
- •Возможные решения
- •Поиск текстовых данных по шаблону
- •Задания
- •Возможные решения
- •Предложение having
- •Задание
- •Возможное решение
- •Удаление записей из таблицы
- •Задания
- •Возможные решения
- •Операторы in и between
- •Задания
- •Возможные решения
- •Упорядочивание данных
- •Задания
- •Возможные решения
- •Ограничение количества извлекаемых данных
- •Извлечение подмножеств
- •Задания
- •Возможные решения
- •Ключевое слово distinct
- •Задания
- •Возможные решения
- •Изменение записей
- •Задания
- •Возможные решения
- •Поиск среднего значения и суммы Суммирование значений столбца с помощью функции sum
- •Вычисление среднего значения
- •Задания
- •Возможные решения
- •Работа с датой
- •Особенности типа данных Date
- •Операции с датами
- •Определение диапазонов
- •Строковые функции
- •Ascii(строка) ord(строка)
- •Concat(строка1, строка2, ...)
- •Concat_ws(разделитель, строка1, строка2, ...)
- •Conv(n, основание_начальное, основание_конечное)
- •Elt(n, строка1, строка2, строкаЗ, ...)
- •Field(строка, строка1, строка2, строка3, ...)
- •Find_in_set(строка, список_строк)
- •Substring_index(строка, разделитель, количество)
- •Trim([[both | leading | trailing] [удаляемая_строка] from] строка)
- •Uncompress(строка_для_распаковки)
- •Unhex(строка)
- •Битовые функции
- •Побитовое или ( | )
- •Побитовое и ( & )
- •Функции шифрования
- •Aes_encrypt(строка, строка_ключа) aes_decrypt(зашифрованная_строка, строка_ключа)
- •Decode(зашифрованная_строка, строка_пароля)
- •Encode(строка, строка_пароля)
- •Des_decrypt(зашифрованная_строка [, строка_ключа])
- •Des_encrypt(строка[, (номер_ключа | строка_ключа)])
- •Encrypt(строка [, нач])
- •Информационные функции benchmark(количество, выражение)
- •Charset(строка)
- •Coercibility(строка)
- •Collation(строка)
- •Md5(строка)
- •Password(строка)
- •Last_insert_id(выражение)
- •Прочие функции
- •Get_lock(строка, таймаут)
- •Inet_aton(выражение)
- •Inet_ntoa(выражение)
- •Is_free_lock(строка)
- •Master_pos_wait(имя_журнала, позиция_в_журнале [, таймаут])
- •Release_lock(строка)
- •Вывод данных из базы данных
- •Создаем ссылки на лету
- •Сохранение данных в базе данных
Поиск среднего значения и суммы Суммирование значений столбца с помощью функции 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. Значения можно складывать, вычитать, умножать или делить. В действительности можно записывать полноценные арифметические выражения.
Вычисление среднего значения
Агрегатная функция 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;
Возможные решения
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. Процент зарплаты, который сотрудники получают в качестве надбавок
Именование столбцов
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. Вывод процента зарплаты, которую сотрудники получают в качестве надбавок с использованием псевдо-имен
Подсчет числа записей
Агрегатная функция COUNT() подсчитывает и выводит общее число записей. Например, чтобы подсчитать общее число записей в таблице, выполните следующую команду.
select COUNT(*) from employee_data;
На рис. 7.18. приведен результат запроса.
Рис. 7.18. Общее количество записей
Как мы уже знаем, знак * означает "все данные".
Теперь давайте подсчитаем общее число сотрудников, которые занимают должность "программист".
select COUNT(*) from employee_data
where title = 'программист';
На рис. 7.19. приведен результат запроса.
Рис. 7.19. Общее количество сотрудников-программистов
Группировка данных
Предложение 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 сначала создает группы различных должностей, а затем выполняет подсчет в каждой группе.
Сортировка данных
Теперь давайте найдем и выведем число сотрудников, имеющих различные должности, и отсортируем их с помощью ORDER BY.
select title, count(*) AS Number
from employee_data
GROUP BY title
ORDER BY Number;
На рис. 7.22. приведен результат запроса.
Рис. 7.22. Количество сотрудников по должностям с сортировкой
Задания
Подсчитайте число сотрудников, которые проработали в BigFoot более трех лет.
Подсчитайте количество сотрудников в группах одного возраста.
Измените предыдущее задание так, чтобы возраст выводился в убывающем порядке.
Найдите средний возраст сотрудников в различных подразделениях (должностях).
Измените предыдущий оператор так, чтобы данные выводились в убывающем порядке среднего возраста.
Возможные решения
1. mysql> select count(*) from employee_data
-> where yos > 3;
На рис. 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. Средний возраст сотрудников по должностям с использованием псевдо-имени столбца
Примечание: Нам нужно задать псевдо-имя для столбца, содержащего среднее значение возраста, чтобы его можно было сортировать.
Лекция 8.
Математические функции 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(в). Арктангенс числа
ATAN2(число1, число2)
Эта функция возвращает угол в радианах точки с заданными координатами.
На рис. 8.5(а), 8.5(б) и 8.5(в) приведены примеры работы с функцией ATAN2.
Рис. 8.5(а). Угол по координатам точки
Рис. 8.5(б). Угол по координатам точки
Рис. 8.5(в). Угол по координатам точки
CEILING(число)
CEIL(число)
Эта функция округляет число до ближайшего большего целого числа.
На рис. 8.6(а), 8.6(б) и 8.6(в) приведены примеры работы с функцией CEIL.
Рис. 8.6(а). Функция CEIL
Рис. 8.6(б). Функция CEIL
Рис. 8.6(в). Функция CEIL
COS(число)
Возвращает косинус числа
На рис. 8.7 приведен пример работы с функцией COS.
Рис. 8.7. Косинус числа
COT(число)
Возвращает котангенс числа.
На рис. 8.8(а) и 8.8(б) приведены примеры работы с функцией COT.
Рис. 8.8(а). Котангенс числа
Рис. 8.8(б). Котангенс числа
CRC32(выражение)
Вычисляет проверочное значение в циклическом избыточном коде и возвращает 32-разрядное целое. Результат равен NULL, если передается аргумент NULL. Ожидается, что аргумент будет строкой, и будет рассматриваться в качестве таковой в противном случае.
На рис. 8.9 приведен пример работы с функцией CRC32.
Рис. 8.9. Циклический избыточный код
DEGREES(число)
Возвращает аргумент, преобразованный из радианов в градусы.
На рис. 8.10 приведен пример работы с функцией DEGREES.
Рис. 8.10. Преобразование из радианов в градусы
ЕХР(число)
Эта функция возводит число e (основание натурального логарифма) в заданную степень.
На рис. 8.11(а) и 8.11(б) приведены примеры работы с функцией EXP.
Рис. 8.11(а). Экспонента
Рис. 8.11(б). Экспонента
FLOOR(число)
Эта функция округляет число до ближайшего меньшего целого числа.
На рис. 8.12(а), 8.12(б) и 8.12(в) приведены примеры работы с функцией FLOOR.
Рис. 8.12(а). Функция FLOOR
Рис. 8.12(б). Функция FLOOR
Рис. 8.12(в). Функция FLOOR
GREATEST(...)
Эта функция возвращает наибольшее значение из списка. Она может работать как с числами, так и со строками.
На рис. 8.13 приведен пример работы с функцией GREATEST.
Рис. 8.13. Наибольшее значение из списка
LEAST(...)
Функция возвращает наименьшее значение из списка.
На рис. 8.14 приведен пример работы с функцией LEAST.
Рис. 8.14. Наименьшее значение из списка
LN(число)
LOG(число)
Эта функция возвращает натуральный логарифм числа.
На рис. 8.15(а) и 8.15(б) приведены примеры работы с функцией LN.
Рис. 8.15(а). Натуральный логарифм числа
Рис. 8.15(б). Натуральный логарифм числа
LOG(число1, число2)
При вызове с одним параметром функция LOG возвращает натуральный логарифм числа, а при вызове с двумя параметрами - возвращает логарифм числа2 по основанию число1.
На рис. 8.16(а) и 8.16(б) приведены примеры работы с функцией LOG2.
Рис. 8.16(а). Логарифм числа по основанию
Рис. 8.16(б). Логарифм числа по основанию
LOG(число1, число2) эквивалентна LOG(число2) / LOG(число1).
LOG2(число)
Возвращает логарифм числа по основанию 2.
На рис. 8.17(а) и 8.17(б) приведены примеры работы с функцией LOG.
Рис. 8.17(а). Логарифм числа по основанию 2
Рис. 8.17(б). Логарифм числа по основанию 2
Функция LOG2() удобна для того, чтобы определить, сколько бит потребуется для сохранения числа. Вместо нее можно использовать LOG(число) /LOG(2).
LOG10(число)
Возвращает логарифм числа по основанию 10.
На рис. 8.18(а), 8.18(б) и 8.18(в) приведены примеры работы с функцией LOG10.
Рис. 8.18(а). Десятичный логарифм
Рис. 8.18(б). Десятичный логарифм
Рис. 8.18(в). Десятичный логарифм
MOD(число1, число2)
число1 % число2
число1 MOD число2
Эта функция возвращает остаток от деления первого числа на второе подобно оператору %.
На рис. 8.19(а), 8.19(б), 8.19(в) и 8.19(г) приведены примеры работы с функцией MOD.
Рис. 8.19(а). Остаток от деления
Рис. 8.19(б). Остаток от деления
Рис. 8.19(в). Остаток от деления
Рис. 8.19(г). Остаток от деления
PI()
Возвращает значение числа
. По умолчанию
отображается пять знаков после десятичной
запятой, но внутренне MySQL использует
полное представление действительного
числа двойной точности.
На рис. 8.20(а) и 8.20(б) приведены примеры работы с функцией PI.
Рис. 8.20(а). Число Пи
Рис. 8.20(б). Число Пи
POW(число1, число2)
POWER(число1, число2)
Возвращает значение число1, возведенное в степень число2.
На рис. 8.21(а), 8.21(б) и 8.21(в) приведены примеры работы с функцией POW.
Рис. 8.21(а). Возведение числа в степень
Рис. 8.21(б). Возведение числа в степень
Рис. 8.21(в). Возведение числа в степень
RADIANS(число)
Возвращает аргумент, преобразованный из градусов в радианы.
На рис. 8.22(а) и 8.22(б) приведены примеры работы с функцией RADIANS.
Рис. 8.22(а). Преобразование из градусов в радианы
Рис. 8.22(б). Преобразование из градусов в радианы
RAND([число])
Возвращает случайное число двойной точности в диапазоне от 0 до 1. Если указан целочисленный аргумент, он служит начальным числом для генератора случайных чисел (генерируя повторяющуюся последовательность). Если аргумент отсутствует, используется значение системных часов.
На рис. 8.23(а) и 8.23(б) приведены примеры работы с функцией RAND.
Рис. 8.23(а). Создание случайных чисел
Рис. 8.23(б). Создание случайных чисел
Функцию можно использовать для извлечения строк в случайном порядке.
mysql> SELECT * FROM имя_таблицы ORDER BY RAND();
ORDER BY RAND() в комбинации с LIMIT удобно для выбора случайного примера из набора строк:
mysql> SELECT * FROM tablel, table2 WHERE a=b AND c<d
-> ORDER BY RAND() LIMIT 1000;
Следует отметить, что RAND() в конструкции WHERE вычисляется заново при каждом выполнении WHERE.
ROUND(число [, точность])
Эта функция округляет число с плавающей запятой до целого числа или, если указан второй аргумент, до заданного количества цифр после запятой. Если точность отрицательная, обнуляется целая часть числа.
На рис. 8.24(а), 8.24(б), 8.24(в), 8.24(г), 8.24(д) и 8.24(е) приведены примеры работы с функцией ROUND.
Рис. 8.24(а). Округление числа
Рис. 8.24(б). Округление числа
Рис. 8.24(в). Округление числа
Рис. 8.24(г). Округление числа
Рис. 8.24(д). Округление числа
Рис. 8.24(е). Округление числа
Следует отметить, что поведение ROUND(), когда аргумент точно на середине отрезка между двумя целыми, зависит от реализации библиотеки С. Различные реализации округляют до ближайшего четного, либо всегда в большую сторону, либо всегда в меньшую сторону, либо в сторону ближайшего нуля. Если вам нужно иметь предсказуемое поведение в этом случае, применяйте вместо этой функции TRUNCATE() ИЛИ FLOOR().
SIGN(число)
Возвращает знак аргумента как -1, 0 или 1, в зависимости от того, число отрицательное, нуль или положительное.
На рис. 8.25(а), 8.25(б) и 8.25(в) приведены примеры работы с функцией SIGN.
Рис. 8.25(а). Знак числа
Рис. 8.25(б). Знак числа
Рис. 8.25(в). Знак числа
SIN(число)
Эта функция возвращает синус числа в радианах.
На рис. 8.26(а) и 8.26(б) приведены примеры работы с функцией SIN.
Рис. 8.26(а). Синус числа
Рис. 8.26(б). Синус числа
SQRT(число)
Эта функция возвращает квадратный корень числа.
На рис. 8.27(а), 8.27(б) и 8.27(в) приведены примеры работы с функцией SQRT.
Рис. 8.27(а). Квадратный корень
Рис. 8.27(б). Квадратный корень
Рис. 8.27(в). Квадратный корень
TAN(число)
Возвращает тангенс числа.
На рис. 8.28 приведен пример работы с функцией TAN.
Рис. 8.28. Тангенс числа
TRUNCATE(число1, число2)
Возвращает число1 с дробной частью, усеченной до число2 десятичных разрядов. Если число2 равно 0, результат не имеет точки и дробной части. Если число2 отрицательное, целая часть числа длиной число2 обнуляется.
На рис. 8.29(а), 8.29(б), 8.29(в), 8.29(г) и 8.29(д) приведены примеры работы с функцией TRUNCATE.
Рис. 8.29(а). Усечение числа
Рис. 8.29(б). Усечение числа
Рис. 8.29(в). Усечение числа
Рис. 8.29(г). Усечение числа
Рис. 8.29(д). Усечение числа
Все числа округляются в сторону нуля. Следует отметить, что десятичные числа обычно не хранятся в компьютерах именно в виде чисел, а в виде двоичных значений двойной точности, поэтому иногда результат может вызвать удивление (рис. 8.29(е))
Рис. 8.29(е). Усечение числа
Это происходит потому, что 10.28 на самом деле сохраняется как 10.27999999999999...
Лекция 9.
