Данная статья посвящена хранимым функциям MySQL. Разберем простые примеры. Для начала создадим функцию HelloWorld
Пример простейшей функции без параметров
drop function if exists HelloWorld;
DELIMITER //
CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE result varchar(30);
SET result = 'HELLO WORLD';
RETURN (result);
END//
DELIMITER ;
select HelloWorld();
|
|
Результат
Пример функции с параметрами
Теперь разберем результат посложнее. Добавим параметры. По умолчанию в хранимых функциях все параметры типа IN, то есть входящие параметры.
drop function if exists plus;
DELIMITER //
CREATE FUNCTION plus(a int,b int) RETURNS int
DETERMINISTIC
BEGIN
DECLARE result INT;
SET result = a+b;
RETURN (result);
END//
DELIMITER ;
Select plus(4,7);
|
|
Результат
Немного разберем код. Если по параметрам все более менее понятно, то непонятно по слову Deterministic. Это слово означает, что результат функции будет повторяться от итерации к итерации при одних и тех же параметрах. Если это не так, то пишут NOT DETERMENISTIC. Подробнее об этом можно почитать в документации
Насколько я понял, когда мы используем функции рандома или любые другие функции, которые приводят к неповторяющемуся результату, то тогда мы должны писать NOT DETERMENISTIC.
Как посмотреть список хранимых функций на сервере?
Пользуясь таблицей из официальной документации можем построить такой запрос
select name from mysql.proc where type='function'
select name from mysql.proc where type='function' |
Этот запрос выдаст все функции по всем базам данных, если нам нужно уточнить какую-то базу данных, то можно написать
select name from mysql.proc where type='function' and db='test_db';
select name from mysql.proc where type='function' and db='test_db'; |
Результат будет примерно таким…
This entry was posted in MySQL, Без рубрики. Bookmark the permalink.
Источник: http://digital-flame.ru/2016/01/28/mysql-hranimyie-funktsii-prostyie-primeryi/
Агрегатные функции SQL — SUM, MIN, MAX, AVG, COUNT
Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT.
Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе.
Рассмотрим сначала случаи использования агрегатных функций без группировки.
Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:
SELECT SUM(ИМЯ_СТОЛБЦА) …
После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.
Сначала работать будем с базой данных фирмы — Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке.
Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):
- Для получения суммы размеров всех заработных плат используем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):
- SELECT SUM(Salary) FROM Staff
- Этот запрос вернёт значение 287664,63.
А теперь упражнение для самостоятельного решения. В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.
Функция SQL MIN
Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.
Пример 3. База данных и таблица — те же, что и в примере 1.
- Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):
- SELECT MIN(Salary) FROM Staff WHERE Dept=42
- Запрос вернёт значение 10505,90.
И вновь упражнение для самостоятельного решения. В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:
Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.
Правильное решение и ответ.
Функция SQL MAX
Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.
Пример 5. База данных и таблица — те же, что и в предыдущих примерах.
- Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):
- SELECT MAX(Salary) FROM Staff WHERE Dept=42
- Запрос вернёт значение 18352,80
Пришло время упражнения для самостоятельного решения.
Пример 6. Вновь работаем с двумя таблицами — Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц).
Правильное решение и ответ.
Функция SQL AVG
Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.
Пример 7. База данных и таблица — те же, что и в предыдущих примерах.
- Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):
- SELECT AVG(Years) FROM Staff WHERE Dept=42
- Результатом будет значение 6,33
В следующем упражнении для самостоятельного решения помимо агрегатной функции требуется использовать также предикат BETWEEN.
Пример 8. Работаем с одной таблицей — Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.
Правильное решение и ответ.
Функция SQL COUNT
Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) …
, то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое).
Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) …, то результатом будет количество всех записей (строк) таблицы.
Пример 9. База данных и таблица — те же, что и в предыдущих примерах.
- Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm — не NULL, вернёт следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):
- SELECT COUNT(Comm) FROM Staff
- Результатом будет значение 11.
Пример 10. База данных и таблица — те же, что и в предыдущих примерах.
- Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT (на MS SQL Server — с предваряющей конструкцией USE company1;):
- SELECT COUNT(*) FROM Staff
- Результатом будет значение 17.
В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.
Пример 11. Работаем с одной таблицей — Staff. Вывести число сотрудников в отделе планирования (Plains).
Правильное решение и ответ.
Агрегатные функции вместе с SQL GROUP BY (группировкой)
Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок, посвящённый отдельно этому оператору.
Работать будем с базой данных «Портал объявлений 1». Скрипт для создания этой базы данных, её таблицы и заполения таблицы данных — в файле по этой ссылке.
Пример 12. Итак, есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю.
Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts — о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость).
Столбец Units содержит данные о количестве поданных объявлений, а столбец Money — о денежных суммах, вырученных за подачу объявлений.
Category | Part | Units | Money |
Транспорт | Автомашины | 110 | 17600 |
Недвижимость | Квартиры | 89 | 18690 |
Недвижимость | Дачи | 57 | 11970 |
Транспорт | Мотоциклы | 131 | 20960 |
Стройматериалы | Доски | 68 | 7140 |
Электротехника | Телевизоры | 127 | 8255 |
Электротехника | Холодильники | 137 | 8905 |
Стройматериалы | Регипс | 112 | 11760 |
Досуг | Книги | 96 | 6240 |
Недвижимость | Дома | 47 | 9870 |
Досуг | Музыка | 117 | 7605 |
Досуг | Игры | 41 | 2665 |
- Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу объявлений в каждой категории. Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
- SELECT Category, SUM(Money) AS Money FROM ADS GROUP BY Category
- Результатом будет следующая таблица:
Category | Money |
Досуг | 16510 |
Недвижимость | 40530 |
Стройматериалы | 18900 |
Транспорт | 38560 |
Электротехника | 17160 |
Пример 13. База данных и таблица — та же, что в предыдущем примере.
- Используя оператор SQL GROUP BY, выяснить, в какой части каждой категории было подано наибольшее число объявлений. Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
- SELECT Category, Part, MAX(Units) AS Maximum FROM ADS GROUP BY Category
- Результатом будет следующая таблица:
Category | Part | Maximum |
Досуг | Музыка | 117 |
Недвижимость | Квартиры | 89 |
Стройматериалы | Регипс | 112 |
Транспорт | Мотоциклы | 131 |
Электротехника | Холодильники | 137 |
- Итоговые и индивидуальные значения в одной таблице можно получить объединением результатов запросов с помощью оператора UNION.
- Поделиться с друзьями
- Реляционные базы данных и язык SQL
Источник: https://function-x.ru/sql_sum_min_max_avg_count.html
Математические функции в SQL
Вы здесь:
Главная — MySQL — SQL — Математические функции в SQL.
Привет всем! Мало кто знает, что в SQL есть математические функции, однако, они есть, и вот о некоторых из них мы и поговорим в этой статье.
Если вы знакомы хоть с одним из нормальных языков программирования, то, думаю, вам все будет понятно и знакомо.
ABS
ABS возвращает абсолютное значение переданного числа.
SELECT ABS(-10) as module FROM table;
Возвратится 10, ибо это его модуль.
ACOS
Нужен аркосинус? Нет проблем! Вводите число, получайте ответ.
SELECT ACOS(.5) as arcosine FROM table;
Вернется ответ: 1.047197551
ASIN
Возвращает арсинус.
SELECT ASIN(.3) as arsin FROM table;
ATAN
Возвращает артангес
SELECT ATAN(.3) as artangets FROM table;
ATAN2
То же самое, но для 2 чисел.
SELECT ATAN2(.3, .2) as artangets2 FROM table;
Вернет: 0.982793723
POWER
Возвращает число, возведенное в какую-то степень.
SELECT POWER(3,2) as power FROM table;
Вернет: 9
SIN, TAN, COS
Возвращают, соотвественно, синус, тангенс, косинус.
SQRT
Возвращает квадратный корень.
SELECT SQRT(25) as root FROM table;
- Понятно, что вернет 5.
- Вот такие часто используемые математические функции есть в SQL.
- Так что, не стоит впутывать сюда PHP, если вам нужно сделать что-то из этого, лучше пусть этим сразу займется сервер базы данных, так будет быстрее.
- Спасибо за внимание и удачных вам запросов!
Предыдущая статья Следующая статья
Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!
Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.
Если Вы не хотите пропустить новые материалы на сайте,то Вы можете подписаться на обновления: Подписаться на обновления
Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.
Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):
-
Кнопка:
Она выглядит вот так:
-
Текстовая ссылка:Как создать свой сайт
Она выглядит вот так: Как создать свой сайт
- BB-код ссылки для форумов (например, можете поставить её в подписи):
[URL=»https://myrusakov.ru»]Как создать свой сайт[/URL]
Источник: https://MyRusakov.ru/sql-math-functions.html
Упражнения по SQL
Не многие владеют в совершенстве языками, а если говорит о языках программирования, то большая часть знает о них только то, что изучали в школе. Однако в полной мере об используемых языках, для управления компьютерными программами, можно почерпнуть только при глубоком изучении материала, в высших учебных заведениях и путем самостоятельного обучения.
Огромное количество программ позволяют выбирать данные для финансовых и других отчетов в считанные минуты. Одна из самых простых и к тому же удобных — SQL SELECT.
При знакомстве с ней любой программист сможет без проблем рассказать о том, что SQL – это язык программирования, помогающий структурировать все необходимые запросы, а SELECT – это команда, используемая в комбинации с SQL. Безусловно, в сочетании SQL SELECT позволяют проводить все необходимые действия, изложенные на примерах данной страницы.
В связи, с чем нижеизложенный материал более подробно и наглядно характеризует возможности SQL SELECT. Поэтому, установив программу, используемую SQL SELECT, вы сэкономите время своих сотрудников, а также деньги предприятия.
С помощью запроса SQL SELECT можно выполнять выборку данных из таблицы. Следующие примеры запросов SQL SELECT используются в таких SQL СУБД как MySQL, Oracle, Access и других.
SQL Select
Для выполнения следующих SQL запросов SELECT нам необходимо прежде всего изучить структуру таблиц.
FAKULTET | KOD_F | Integer | PRIMARY KEY |
NAZV_F | Char, 30 | ||
SPEC | KOD_S | Integer | PRIMARY KEY |
KOD_F | Integer | ||
NAZV_S | Char, 50 | ||
STUDENT | KOD_STUD | Integer | PRIMARY KEY |
KOD_S | Integer | ||
FAM | Char, 30 | ||
IM | Char, 15 | ||
OT | Char, 15 | ||
STIP | Decimal, 3 | ||
BALL | Decimal, 3 |
Примеры запросов SELECT на языке SQL:
SQL Пример SELECT №1
Выбрать студентов, получающих стипендию, равную 150.
SELECT fname FROM STUDENT WHERE STIP=150;
С помощью данного SQL запроса SELECT выбираются все значения из таблицы STUDENT, поле STIP которых строго равно 150.
SQL Пример SELECT №2
Выбрать студентов, имеющих балл от 82 до 90. Студенты должны быть отсортированы в порядке убывания балла.
SELECT FAM FROM STUDENT WHERE BALL BETWEEN 81 AND 91 ORDER BY BALL DESC;
Как видно из SQL примера, чтобы выбрать студентов, которые имеют балл от 82 до 90, мы используем условие BETWEEN. Чтобы отсортировать в убывающем порядке DESC.
SQL Пример SELECT №3
Выбрать студентов, фамилии которых начинаются с буквы «А».
SELECT
Источник: http://exercises-on-sql.blogspot.com/2019/
Математические функции в SQL (модуль числа, округление, возведение в степень, вычисление корня и другие функции)
Здравствуйте, уважаемые читатели блога webcodius.ru. Сегодня продолжим изучение баз данных, а именно пройдемся по математическим функциям языка sql. Использование математических функций sql поможет перенести часть логики приложения с web-сервера на сервер базы данных, тем самым разгрузив web-сервер на который обычно ложится основная нагрузка.
Сразу отмечу, что в случае ошибки все математические функции возвращают NULL. Итак, перейдем к делу.
Знаки числа
- Начнем с функции ABS (x), которая возвращает абсолютное значение переданного ей числа x. Пример:
- SELECT ABS (-5)
Результат: 5
SELECT ABS (5) - Результат: 5
Знак числа можно определить с помощью функции SIGN (x).
Функция возвращает -1 если x отрицательное число, 1 если положительное и 0 если x является нулем. Пример:
- SELECT SIGN (-5)
Результат: -1
SELECT SIGN (0)
Результат: 0
SELECT SIGN (5) - Результат: 1
Округление чисел
Начнем с функции FLOOR (x). Возвращает ближайшее целое число не превышающее x. Пример:
SELECT FLOOR (5.5)
Результат: 5
SELECT FLOOR (5.2)
Результат: 5
SELECT FLOOR (5.7)
Результат: 5
SELECT FLOOR (5)
Результат: 5
SELECT FLOOR (-5.2)
Результат: -6
Как видно, при задании любого положительного дробного числа от 5 до 6 возвращается целое число 5. А при передаче параметра -5.2 вернется -6, так как -6 меньше -5.2.
Обратное действие выполняет функция CEILING (x). Она возвращает ближайшее целое число, которое превышает переданный параметр x. Пример:
SELECT CEILING (5.5)
Результат: 6
SELECT CEILING (5.2);
Результат: 6
SELECT CEILING (5.7)
Результат: 6
SELECT CEILING (5)
Результат: 5
SELECT CEILING (-5.5)
Результат: -5
Для округления дробного числа до ближайшего целого используется функция ROUND (x, d). Функция может принимать один или два параметра.
Первый параметр x — число, которое необходимо округлить. Второй параметр d — целое число, определяющее разряд до которого необходимо округлить x.
В случае если передан один параметр x, то происходит просто округление до ближайшего целого. Например:
SELECT ROUND (50.45)
Результат: 50
SELECT ROUND (50.76)
- Результат: 51
- При значении аргумента, равного середине между двумя целыми числами, результат будет зависеть от конкретной СУБД, где используется язык SQL.
- Если в функцию передан второй параметр, то после запятой останется столько знаков сколько указано в параметре. Например, если после запятой необходимо оставить один символ, то в качестве второго параметра указываем цифру 1:
- SELECT ROUND (50.76, 1)
Результат: 50,8 - Существует возможность округлять число до любого разряда до запятой, для этого просто вторым параметром указываем отрицательное число. Например:
- SELECT ROUND (251.55, -1)
Результат: 250 - В SQL еще имеется функция, которая не округляет, а отсекает десятичную часть дробного числа. Функция TRUNCATE (x, y) возвращает число x, усеченное до y десятичных знаков:
- SELECT TRUNCATE (1.999, 1)
- Результат: 1.9
- SELECT TRUNCATE (1.999, 0)
- Результат: 1
Функции выполняющие сложные математические операции
Начнем с самого простого. Функция MOD (x, y) возвращает остаток от деления x на y. Например:
SELECT MOD (10, 3)
Результат: 1
Следующая функция EXP (x), которая возвращает значение e (Число Эйлера) возведенное в степень x. Или научным языком, возвращает экспоненту числа. Пример:
- SELECT EXP (3)
Результат: 20.085536923187668 - Далее рассмотрим функцию LOG (x), которая возвращает натуральный логарифм числа x. Пример:
- SELECT LOG (10)
Результат: 2.302585092994046 - Для получения логарифма числа x, для произвольной основы логарифма y можно пользоваться формулой LOG (x)/LOG (y). Например:
- SELECT LOG (8)/LOG (2)
Результат: 3 - Для получения десятичного логарифма числа x существует функция LOG10 (x). Пример:
- SELECT LOG10 (100)
Результат: 2
Для возведения в степень в языке SQL есть целых две функции: POW (x, y) и POWER (x, y). Возвращают число x возведенное в степень y. Пример:
- SELECT POW (2, 3)
Результат: 8
SELECT POWER (3, 2) - Результат: 9
- А функция SQRT (x) вычисляет квадратный корень числа x. Пример:
- SELECT SQRT (16)
Результат: 4 - Чтобы использовать в своих вычисления число «пи» в SQL есть функция PI (), которая возвращает значение этого числа:
- SELECT PI ()
Результат: 3.141593
Тригонометрические функции в языке SQL
Кратенько пройдемся по тригонометрическим функциям:
- COS (x) — косинус угла x;
- SIN (x) — синус угла x;
- TAN (x) — тангенс угла x;
- COT (x) — котангенс угла x.
- Везде x задается в радианах. Примеры:
- SELECT COS (PI ())
Результат: -1
SELECT SIN (PI ()/2)
Результат: 1
SELECT TAN (PI ()/4)
Результат: 1
SELECT COT (PI ()/3) - Результат: 0.577350269189626
Функции ACOS (x) и ASIN (x) вычисляют соответственно арккосинус и арксинус числа x, т.е. функции возвращают величину, косинус или синус которой равен x. При этом если значение x не находится в диапазоне от -1 до 1, то функции возвращают NULL. Например:
SELECT ASIN (-1)
Результат: -1.5707963267949
SELECT ACOS (-1)
Результат: 3.14159265358979
SELECT ACOS (1.1)
Результат: NULL
Функция ATAN (x) вычисляет арктангенс числа x, т.е. возвращает величину, тангенс которой равен x. Пример:
- SELECT ATAN (3);
Результат: 1.24904577239825 - Для преобразования радиан в градусы и обратно используются функции DEGREES (x) и RADIANS (x) соответственно:
- SELECT DEGREES (PI ())
Результат: 180
SELECT RADIANS (180) - Результат: 3.14
Случайные числа
Функция RAND (x) генерирует случайное значение в диапазоне от 0 до 1. Если указан аргумент x, то он используется как начальное значение этой величины. Пример:
SELECT RAND ();
Результат:0.472241415009636
SELECT RAND (0.5);
Результат: 0.943597390424144
На этом все. Вроде рассмотрел все часто используемые в SQL математические функции. Возможно вам будет интересно узнать и о функциях обработки строк в SQL.
Источник: https://webcodius.ru/sql/matematicheskie-funkcii-v-sql-modul-chisla-okruglenie-vozvedenie-v-stepen-vychislenie-kornya-i-drugie-funkcii.html
2.26. Математические функции
У MS SQL Server есть несколько математических функций, которые позволят нам перенести на сервер максимальное количество логики и освободить клиентские компьютеры от лишних расчетов, а значит и нагрузки.
2.26.1. Знаки
Иногда бывает необходимо определить значение числового поля, не обращая внимания на знак (абсолютное значение). Для этого используется функция ABS, которой в качестве параметра передается значение поля или переменной, а на выходе мы получаем то же число, но без знака.
В следующем примере объявляется целочисленная переменная, которой присваивается число -10. После выполнения ABS, на экране мы увидим число 10, а знак будет отброшен:
DECLARE @num int
SET @num=-10
SELECT ABS(@num)
Следующий пример отображает название товара и абсолютное значение количества, ведь количество товара не может быть отрицательным (хотя, в некоторых задачах бывают и исключения):
SELECT [Название товара], ABS(Количество)
FROM Товары
Для определения знака можно использовать функцию SIGN. В качестве параметра этой функции нужно передать число, а в качестве результата мы получаем:
- -1 если число отрицательное;
- 0 если число равно нулю;
- 1 если положительное.
Следующий запрос вернет нам значение -1:
SELECT SIGN(-12)
2.26.2. Округление
Функция CEILING возвращает ближайшее целое число, которое является больше, указанного в качестве параметра. Другими словами, происходит округление в большую сторону. Например, если в качестве параметра передать число 1.3, то ближайшее целое число, которое будет больше указанного, это 2. Следующий пример демонстрирует это в виде SQL запроса:
DECLARE @num float
SET @num = 1.3
SELECT CEILING(@num)
Если передать отрицательное число, например, -12.5, то будет возвращено число 12, которое больше, и при этом ближайшее.
Давайте посмотрим, как можно округлить цены в таблице товаров до ближайшего целого:
SELECT [Название товара], CEILING(Цена), ABS(Количество)
FROM Товары
Если в качестве параметра передать целое число, то функция вернет его, потому что оно уже является целым.
Обратную задачу выполняет функция FLOOR, которая округляет дробное число до ближайшего меньшего числа. Например:
DECLARE @num float
SET @num = 1.3
SELECT FLOOR(@num)
В этом примере мы округляем число 1.3 до ближайшего меньшего, поэтому результатом будет 1.
Для отрицательного числа -4.6 ближайшим будет -5.
Для округления дробного числа до ближайшего целого (не имеет значения, в какую сторону) используется функция ROUND. У нее два параметра: дробное число, которое нужно округлить, и целое число, определяющее, до какого разряда необходимо округлить.
Давайте рассмотрим несколько примеров округления на примере числа 262.1537. Для начала округлим до целого числа, для этого второй параметр должен быть равен нулю:
SELECT ROUND(262.1537, 0)
В результате мы увидим число 262.0000.
Если необходимо оставить после запятой один символ, то в качестве второго параметра нужно указать число 1. «Запятая», это по российским правилам, а SQL сервер работает по американским стандартам и тут дробную часть отделяет точка. Итак, следующим пример оставляет один разряд после запятой:
SELECT ROUND(262.1537, 1)
В результате мы получаем число 262.2000.
Можно округлять и до любого разряда до запятой, для этого можно во втором параметре указать отрицательное значение. Если необходимо округлить до одного разряда до запятой, то в качестве второго параметра указываем число -1:
SELECT ROUND(262.1537, -1)
Результатом будет 260.0000.
2.26.3. Сложная математика
К функциям сложной математики я отнес логарифмы, экспоненту, потому что для их расчета нужно немалое процессорное время и перенести эти расчеты на сервер будет неплохим решением.
Функция EXP возвращает экспоненту указанного в качестве параметра числа:
DECLARE @angle float
SET @angle = 90
SELECT EXP(@angle)
Для определения логарифма указанного числа используется функция LOG:
DECLARE @angle float
SET @angle = 5.3
SELECT LOG(@angle)
2.26.4. Случайное значение
Для генерации случайного значения используется функция RAND. В качестве параметра передается переменная, которая определяет начальное значение или источник. Например:
DECLARE @r int
SET @r=20000000
SELECT RAND(@r)
Попробуйте выполнить этот запрос несколько раз, и вы увидите в результате одно и то же значение. Это серьезный недостаток. Для одного и того же значения переменной, указанного в качестве параметра, будет одно и то же случайное число. Получается, что результат RAND нельзя назвать случайным.
2.26.5. Тригонометрические функции
Честно сказать, я не очень дружу с тригонометрией. Нет, с математикой в школе все было отлично, но после этого прошло очень много времени и многое позабылось, поэтому приходиться потратить много времени, чтобы восстановить всю необходимую информацию в памяти. А это необходимо, когда приходиться работать с графикой.
Тригонометрические функции слишком схожи, поэтому будем рассматривать их все оптом, так дешевле :).
Для определения арккосинуса используется функция ACOS, которой необходимо передать дробное число от -1 до 1, которое определяет необходимый угол:
DECLARE @angle float
SET @angle = -1
SELECT ACOS(@angle)
В результате мы увидим арккосинус угла -1, который равен 3.1415926535897931. Ничего вам это не напоминает? Если вспомнить математику, то это число должно напомнить вам константу Пи.
Для получения числа Пи можно воспользоваться и более простым методом – вызвать функцию PI:
SELECT PI()
Функция ASIN возвращает арксинус указанного угла. Угол также задается в диапазоне от -1 до 1.
DECLARE @angle float
SET @angle = -1
SELECT ASIN(@angle)
Функция ATAN возвращает арктангенс:
DECLARE @angle float
SET @angle = -1
SELECT ATAN(@angle)
Функция COS возвращает косинус:
DECLARE @angle float
SET @angle = 90
SELECT COS(@angle)
Для определения синуса используем функцию SIN:
DECLARE @angle float
SET @angle = 90
SELECT SIN(@angle)
Для определения тангенса используется функция TAN:
DECLARE @angle float
SET @angle = 90
SELECT TAN(@angle)
2.26.6. Степень
Для работы со степенями есть только две функции SQRT – квадратный корень и SQUARE – возведение числа в квадрат. Следующий пример показывает, как можно определить квадратный корень числа 4:
SELECT SQRT(4)
В результате мы увидим число 2. Следующий пример считает квадрат числа 6:
SELECT SQUARE(6)
В результате мы увидим число 36.
Источник: https://www.flenov.info/books/read/transact-sql/38
Математические функции SQL
Очень часто требуется использовать в процедурах генератор случайных чиселВажно сказать, что для подобных задач в MySQL предусмотрены функции RAND() и RAND(N). Функция RAND() возвращает случайное число в диапазоне от 0 до 1.
Пример 1
SELECT RAND();
Результат1 : 0.04547451068225737.
Результат2: 0.2076478397544919.
Результат3: 0.9018156974593323
Функция RAND(N) при генерации случайного числа в качестве начального значения использует число N. Исходя из всᴇᴦο выше сказанного, мы приходим к выводу, что при каждой генерации с одним и ᴛᴇᴍ N получается одно и то случайное число.
Пример 2
SELECT RAND(10);
Результат1: 0.6570515219653505.
Результат2: 0.6570515219653505.
Результат3: 0.6570515219653505.
Функция ABS(x) возвращает модуль числа х.
Пример 3
- SELECT ABS(-60);
- Результат: 60.
- SELECT ABS(120-100);
- Результат: 20.
Функция SIGN(X) возвращает -1, если х отрицательно, 0, если х равно 0, 1, если х положительно. Используется определения знака числа.
Пример 4
- В таблице tovar имеется два поля:
- plan – запланированное к производству количество товара, fact – фактически произведенное количество.
- Нужно вывести напротив каждого товара либо ʼʼплан выполненʼʼ , либо ʼʼдефицит товараʼʼ, либо ʼʼплан перевыполненʼʼ .
- SELECT tovar_name, CASE SIGN(plan-fact)
- WHEN -1 THEN ʼʼдефицит товараʼʼ
- WHEN 0 THEN ʼʼплан выполненʼʼ
- ELSE ʼʼплан перевыполненʼʼ
- END
- FROM tovar;
Функция MOD (x,y) возвращает остаток от деления x на у. Кроме функции MOD в mysql тот результат можно получить используя оператор %.
Пример 5
- SELECT 32/8; или SELECT 32 % 8;
- Результат: 0.
- SELECT 33/8; SELECT 33 % 8;
- Результат 1.
Функция FLOOR(x) возвращает наибольшее целое число, ĸᴏᴛᴏᴩᴏᴇ меньше или равно x.
Пример 6
- SELECT FLOOR(23.6);
- Результат: 23.
- SELECT FLOOR(-23.6);
- Результат: -24.
ФункцияCEILING(X) возвращает наименьшее целое число, ĸᴏᴛᴏᴩᴏᴇ больше или равно X:
Пример 7
- SELECT CEILING (23.6);
- Результат: 24.
- SELECT CEILING(-23.6);
- Результат: -23.
Функция TRUNCATE(x,n) отсекает десятичные знаки числа так, чтобы их оставалось ровно n.
Пример 8
SELECT TRUNCATE(10.535364,2);
Результат: 10.53.
Дополнительный материал 1
Следует понимать, что функции FLOOR и CELLING и TRUNCATE не дают округления!
Округление
Для округления чисел до ближайшᴇᴦο целого используется функция ROUND(x).
Пример 9
- SELECT ROUND(23.6);
- Результат: 24.
- SELECT ROUND (-23.6);
- Результат: -24.
Так можно воспользоваться функцией ROUND (x,n), которая округляет число х до некоторого числа, имеющᴇᴦο n десятичных знаков.
Пример 10
SELECT ROUND(10.535364,2);
Результат: 10.54.
SELECT ROUND(-1.5364,3);
Результат: -1.536.
Cтепени и логарифмы
Функции POW(x,y) и POWER(x,y) возвращают значение х в степени y.
Пример 11
SELECT POW(2,3);
Результат: 8.
Функция SQRT(x) возвращает неотрицательный квадратный корень числа x.
Пример 12
- SELECT SQRT(9);
- Результат: 3.
- SELECT SQRT(-9);
- Результат: NULL.
Функция EXP() возвращает значение числа e в степени x.
Пример 13
SELECT EXP(2);
Результат: 7.389056.
Функция LOG(x) возвращает натуральный логарифм числа x.
Пример 14
SELECT LOG(10);
Результат: 2.302585.
SELECT LOG (-10);
Результат: NULL.
Функция LOG10(x) возвращает десятичный логарифм числа x.
Пример 15
- SELECT LOG10(10);
- Результат: 1.
- SELECT LOG10(100);
- Результат: 2.
Тригономертия
Функция PI() возвращает число Pi с точностью до 5 знаков. Тригонометрические функции вычисляются при помощи следующих функций MySQL:
- COS(x) – косинус x, где х задан в радианах;
- SIN(x) – синус x, , где х задан в радианах;
- TAN(x) – тангенс x, где х задан в радианах;
- ACOS(x) – арккосинус x, где х задан в диапазоне [-1;1] ;
- ASIN(x) – арксинус x, где х задан в диапазоне [-1;1];
- ATAN(x) – арксинус x;
- COT(x) – котангенс x.
Для преобразования радианов в градусы используется функция DEGREES(x).
Пример 16
SELECT DEGREES(PI());
Результат: 180.0.
Для преобразования градусов в радианы используется функция RADIANS(x).
Пример 17
SELECT RADIANS(180);
Результат: 3.1415926.
Источник: http://referatwork.ru/info-lections-55/tech/view/401_matematicheskie_funkcii_sql
SQL | Функции (агрегатные и скалярные функции) | Портал информатики для гиков
Для выполнения операций над данными в SQL есть много встроенных функций, они подразделяются на две категории и далее подкатегоризованы на разные семь функций в каждой категории. Категории:
- Агрегатные функции: Эти функции используются для выполнения операций со значениями столбца, и возвращается одно значение.
- AVG ()
- COUNT ()
- ПЕРВЫЙ()
- ПОСЛЕДНИЙ()
- МАКСИМУМ()
- MIN ()
- СУММА ()
- Скалярные функции: Эти функции основаны на вводе пользователем, они также возвращают одно значение.
- UCASE ()
- LCASE ()
- (MID)
- LEN ()
- КРУГЛЫЙ()
- СЕЙЧАС()
- ФОРМАТ()
Студенты-Table
Агрегатные функции
- AVG () : возвращает среднее значение после расчета по значениям в числовом столбце. Синтаксис:SELECT AVG(column_name) FROM table_name;
Запросы:- Вычисление средних баллов студентов.SELECT AVG(MARKS) AS AvgMarks FROM Students;
Выход: - Вычисление среднего возраста студентов.SELECT AVG(AGE) AS AvgAge FROM Students;
Выход:
- Вычисление средних баллов студентов.SELECT AVG(MARKS) AS AvgMarks FROM Students;
- COUNT (): используется для подсчета количества строк, возвращаемых в инструкции SELECT. Он не может быть использован в MS ACCESS. Синтаксис:SELECT COUNT(column_name) FROM table_name;
Запросы:- Подсчет общего количества студентов.SELECT COUNT(*) AS NumStudents FROM Stuents;
Выход: - Вычисление числа студентов с уникальным / отличным возрастом.SELECT COUNT(DISTINCT AGE) AS NumStudents FROM Students;
Выход:
- Подсчет общего количества студентов.SELECT COUNT(*) AS NumStudents FROM Stuents;
- FIRST (): функция FIRST () возвращает первое значение выбранного столбца. Синтаксис:SELECT FIRST(column_name) FROM table_name;
Запросы:- Получение отметок первого ученика со стола учеников.SELECT FIRST(MARKS) AS MarksFirst FROM Students;
Выход: - Получение возраста первого ученика из таблицы учеников.SELECT FIRST(AGE) AS AgeFirst FROM Students;
Выход:
- Получение отметок первого ученика со стола учеников.SELECT FIRST(MARKS) AS MarksFirst FROM Students;
- LAST (): функция LAST () возвращает последнее значение выбранного столбца. Может использоваться только в MS ACCESS. Синтаксис:SELECT LAST(column_name) FROM table_name;
Запросы:- Извлечение отметок последнего ученика из таблицы «Студенты».SELECT LAST(MARKS) AS MarksLast FROM Students;
Выход: - Выбор возраста последнего ученика из таблицы учеников.SELECT LAST(AGE) AS AgeLast FROM Students;
Выход:
- Извлечение отметок последнего ученика из таблицы «Студенты».SELECT LAST(MARKS) AS MarksLast FROM Students;
- MAX (): функция MAX () возвращает максимальное значение выбранного столбца. Синтаксис:SELECT MAX(column_name) FROM table_name;
Запросы :- Получение максимальных оценок среди студентов из таблицы «Студенты».SELECT MAX(MARKS) AS MaxMarks FROM Students;
Выход: - Получение максимального возраста среди студентов из таблицы «Студенты».SELECT MAX(AGE) AS MaxAge FROM Students;
Выход:
- Получение максимальных оценок среди студентов из таблицы «Студенты».SELECT MAX(MARKS) AS MaxMarks FROM Students;
- MIN (): функция MIN () возвращает минимальное значение выбранного столбца. Синтаксис:SELECT MIN(column_name) FROM table_name;
Запросы:- Получение минимальных оценок среди студентов из таблицы «Студенты».SELECT MIN(MARKS) AS MinMarks FROM Students;
Выход: - Извлечение минимального возраста среди учеников из таблицы учеников.SELECT MIN(AGE) AS MinAge FROM Students;
Выход:
- Получение минимальных оценок среди студентов из таблицы «Студенты».SELECT MIN(MARKS) AS MinMarks FROM Students;
- SUM (): функция SUM () возвращает сумму всех значений выбранного столбца. Синтаксис:SELECT SUM(column_name) FROM table_name;
Запросы:- Выборка суммирования итоговых оценок среди студентов из таблицы «Студенты».SELECT SUM(MARKS) AS TotalMarks FROM Students;
Выход: - Выборка суммирования общего возраста среди учеников из таблицы учеников.SELECT SUM(AGE) AS TotalAge FROM Students;
Выход:
- Выборка суммирования итоговых оценок среди студентов из таблицы «Студенты».SELECT SUM(MARKS) AS TotalMarks FROM Students;
Скалярные функции
- UCASE () : преобразует значение поля в верхний регистр. Синтаксис:SELECT UCASE(column_name) FROM table_name;
Запросы:- Преобразование имен учеников из таблицы учеников в верхний регистр.SELECT UCASE(NAME) FROM Students;
Выход:NAME HARSH SURESH PRATIK DHANRAJ RAM
- Преобразование имен учеников из таблицы учеников в верхний регистр.SELECT UCASE(NAME) FROM Students;
- LCASE () : преобразует значение поля в нижний регистр. Синтаксис:SELECT LCASE(column_name) FROM table_name;
Запросы:- Преобразование имен учеников из таблицы учеников в строчные.SELECT LCASE(NAME) FROM Students;
Выход:NAME harsh suresh pratik dhanraj ram
- Преобразование имен учеников из таблицы учеников в строчные.SELECT LCASE(NAME) FROM Students;
- MID (): функция MID () извлекает текст из текстового поля. Синтаксис:SELECT MID(column_name,start,length) AS some_name FROM table_name;
specifying length is optional here, and start signifies start position ( starting from 1 )
Запросы:- Получение первых четырех символов имен учеников из таблицы «Студенты».SELECT MID(NAME,1,4) FROM Students;
Выход:
- Получение первых четырех символов имен учеников из таблицы «Студенты».SELECT MID(NAME,1,4) FROM Students;
- LEN (): функция LEN () возвращает длину значения в текстовом поле. Синтаксис:SELECT LENGTH(column_name) FROM table_name;
Запросы:- Получение длины имен студентов из таблицы студентов.SELECT LENGTH(NAME) FROM Students;
Выход:
- Получение длины имен студентов из таблицы студентов.SELECT LENGTH(NAME) FROM Students;
- ROUND (): функция ROUND () используется для округления числового поля до указанного числа десятичных знаков. ПРИМЕЧАНИЕ: Многие системы баз данных приняли стандарт IEEE 754 для арифметических операций, который говорит, что при округлении любого числового .5 оно приводит к до ближайшего четного целого числа, то есть 5,5 и 6,5 оба округляются до 6.Синтаксис:SELECT ROUND(column_name,decimals) FROM table_name;
decimals- number of decimals to be fetched.
Запросы:- Получение максимальных оценок среди студентов из таблицы «Студенты».SELECT ROUND(MARKS,0) FROM table_name;
Выход:
- Получение максимальных оценок среди студентов из таблицы «Студенты».SELECT ROUND(MARKS,0) FROM table_name;
- NOW (): функция NOW () возвращает текущую системную дату и время. Синтаксис:SELECT NOW() FROM table_name;
Запросы:- Получение текущего системного времени.SELECT NAME, NOW() AS DateTime FROM Students;
Выход:NAME DateTime HARSH Источник: http://espressocode.top/sql-functions-aggregate-scalar-functions/
SQL за 20 минут
Предлагаем вашему вниманию статью с кричащим названием «SQL за 20 минут». Конечно, весь SQL за 20 минут вы не освоите, но хороший старт получите.
Каждый уважающий себя веб-разработчик должен знать SQL. Хоть он и существует аж с 70-х годов прошлого века, он до сих пор очень широко используется, и без него будет сложно создать нечто серьёзное.
Большинство full-stack фреймворков умеют работать с SQL. В их числе: ActiveRecord, Doctrine, Hibernate и многие другие. Несмотря на это, иногда приходится «замарать руки» и пуститься в настоящий SQL.
Именно поэтому мы подготовили короткое вступление, в котором мы пройдёмся по фундаментальным вещам в SQL. Мы настоятельно рекомендуем вам попробовать все приведённые ниже примеры самостоятельно, ведь, как известно, теория — ничто без практики.
Что ж, приступим!
Создаём таблицу
Для того, чтобы создать таблицу в SQL, используется выражение CREATE TABLE. Он принимает в качестве параметров все колонки, которые мы хотим внести, а также их типы данных.
Давайте создадим табличку с названием «Months», в которой будет три колонки:
- id — иными словами, порядковый номер месяца (целочисленный тип или int)
- name — название месяца (строка или varchar(10) (10 символов — максимальная длина строки))
- days — число дней в конкретном месяце (целочисленный тип или int)
Код будет выглядеть вот так:
CREATE TABLE months (id int, name varchar(10), days int);
Также, когда создаются таблицы, принято добавлять так называемый primary key. Это колонка, значения в которой уникальны. Чаще всего primary key колонкой является id, но в нашем случае это может быть и name, так как имена всех месяцев уникальны. Для более подробной информации предлагаем перейти по этой ссылке.
Ввод данных
Теперь давайте добавим пару месяцев в нашу табличку. Сделать это можно с помощью команды INSERT. Есть два разных способа использовать INSERT:
Первый способ не подразумевает указания названий колонок, а лишь принимает значения в том порядке, в котором они указаны в таблице.
INSERT INTO months VALUES (1,'January',31);
Первый способ короче второго, однако если в будущем мы захотим добавить дополнительные колонки, все предыдущие запросы работать не будут. Для решения данной проблемы следует использовать второй способ. Его суть в том, что перед вводом данных мы указываем названия колонок.
INSERT INTO months (id,name,days) VALUES (2,'February',29);
В случае, если мы не укажем одну из колонок, на её место будет записано NULL или заданное значение по умолчанию, но это уже совсем другая история.
Select
Данный запрос используется в случае, если нам нужно показать данные в таблице. Наверное, самым простым примером использования SELECT будет следующий запрос:
SELECT * FROM characters
Результатом данного запроса будет таблица со всеми данными в таблице characters. Знак звёздочки (*) означает то, что мы хотим показать все столбцы из таблицы без исключений. Так как в базе данных обычно больше одной таблицы, нам необходимо указывать название таблицы, данные из которой мы хотим посмотреть. Сделать это мы можем, используя ключевое слово FROM.
Когда вам нужны лишь некоторые столбцы из таблицы, то вы можете указать их имена через запятую вместо звёздочки.
SELECT name, weapon FROM characters
Также иногда нам нужно отсортировать выводимые данные. Для этого мы используем ORDER BY «название столбца». ORDER BY имеет два модификатора: ASC (по возрастанию) (по умолчанию) и DESC (по убыванию).
SELECT name, weapon FROM «characters» ORDER BY name DESC
Where
Теперь мы знаем, как показать только конкретные столбцы, но что если мы хотим включить в вывод лишь некоторые конкретные строки? Для этого мы используем WHERE. Данное ключевое слово позволяет нам фильтровать данные по определённому условию.
В следующем запросе мы выведем только тех персонажей, которые в качестве оружия используют пистолет.
SELECT *
FROM characters
WHERE weapon = 'pistol';И/или
Условия в WHERE могут быть написаны с использованием логических операторов (AND/OR) и математические операторы сравнения (=, , =, ).
К примеру, у нас есть табличка, в которой записаны данные о 4 самых продаваемых музыкальных альбомах всех времён. Давайте выведем только те, жанром которых является рок, а продажи были меньше, чем 50 миллионов копий.
SELECT *
FROM albums
WHERE genre = 'rock' AND sales_in_millionsИсточник: https://proglib.io/p/sql-for-20-minutes
- Получение текущего системного времени.SELECT NAME, NOW() AS DateTime FROM Students;