Функции MySQL
Функции шифрования
Обратимое шифрование
При обратимом шифровании, как правило, предоставляются две функции для
шифровки и расшифровки данных. Наиболее серьезную защиту обеспечивают
функции AES_ENCRYPT (str, key_str) и AES_DECRYPT (crypt _str, key_str ) ,
которые используют официальный алгоритм AES (Advanced El1cryptiol1 Stаndard).
SELECT AES_ENCRYPT('MySQL', 'секретный ключ');
SELECT AES_DECRYPT('x��$Fb�;�q��%�
}', 'секретный ключ');Нео6ратимое шифрование
Функция MD5() осуществляет необратимое шифрование данных
по алгоритму MD5 (Message-Digest Algorithm) и имеет следующий
синтаксис:
MD5 ( str)
SELECT MD5('MySQL'), MD5('MySQL');
SELECT MD5('MySQL1'), MD5('MySQL');
Алгоритм MD5 часто применяется также для создания уникального ХЭШ-КОДа
объемных файлов, которые передаются по сети . Загрузив файл, всегда можно
про верить его целостность , вы числ ив код по алгоритму MD5 и сравнив
полученный результат с хэш-кодом, предоставляемым распространителем. Это
позволяет отследить повреждения файла, вызванные передачей через сеть, а
также предотвратить фальсификацию дистрибутива.
Агрегатные функции
Функции, применяемые совместно с конструкцией GROUP ВY, часто называют
агрегатными или суммирующими функциями. Они предназначены для
вычисления одного значения для каждой группы, создаваемой конструкцией
GROUP ВY.
Среднее значение
Функция AVG () возвращает среднее значение аргумента expr и имеет
следующий синтаксис:
AVG ( [ DISTINCT ] expr)
Создадим таблицу
CREATE TABLE catalogs1 (
id_catalog INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
total INT(11) NOT NULL,
PRIMARY KEY (id_catalog)
);
INSERT IGNORE INTO catalogs1 VALUES (1, 'Процессоры', 15);
INSERT IGNORE INTO catalogs1 VALUES (2, 'Материнские платы', 4);
INSERT IGNORE INTO catalogs1 VALUES (3, 'Видеоадаптеры', 7);
INSERT IGNORE INTO catalogs1 VALUES (4, 'Жесткие диски', 18);
INSERT IGNORE INTO catalogs1 VALUES (5, 'Оперативная память', 9);
SELECT * FROM catalogs1;
Запрос
SELECT AVG(total) FROM catalogs1;
Создадим таблицу
CREATE TABLE products (
id_product INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
price DECIMAL(7,2) NOT NULL,
id_catalog INT(11) NOT NULL,
PRIMARY KEY (id_product),
KEY id_catalog (id_catalog)
);
INSERT IGNORE INTO products VALUES (1, 'Материнская плата N 1', '2050.00', 2);
INSERT IGNORE INTO products VALUES (2, 'Оперативная память N 1', '1500.00', 5);
INSERT IGNORE INTO products VALUES (3, 'Процессор N 1', '6000.00', 1);
INSERT IGNORE INTO products VALUES (4, 'Процессор N 2', '3523.00', 1);
INSERT IGNORE INTO products VALUES (5, 'Процессор N 3', '4856.00', 1);
INSERT IGNORE INTO products VALUES (6, 'Оперативная память N 2', '1028.00', 5);
INSERT IGNORE INTO products VALUES (7, 'Материнская плата N 2', '1845.00', 2);
INSERT IGNORE INTO products VALUES (8, 'Жесткий диск N 1', '4210.00', 4);
INSERT IGNORE INTO products VALUES (9, 'Видеоадаптер N 1', '3670.00', 3);
Запрос
SELECT id_catalog, AVG(price) FROM products GROUP BY id_catalog;
SELECT id_catalog, AVG(price*1.2) FROM products GROUP BY id_catalog;
SELECT id_catalog, AVG(price)*1.2 FROM products GROUP BY id_catalog;
Сортировка агрегатных значений
По полученным в результате выполнения агрегатных функций значениям
может выполняться сортировка результирующей таблицы . Для этого столбцу
назначается псевдоним при помощи оператора AS, который передается
конструкции ORDER ВY
SELECT id_catalog, AVG(price) AS price FROM products GROUP BY id_catalog ORDER BY price DESC;
Подсчет количества записей в таблице
Подсчет количества записей в таблице осуществляется при помощи функции
COUNT (), имеющей несколько форм со следующим синтаксисом:
COUNT (expr)
COUNT (*)
COUNT ( DISTINCT expr1 , expr2 , ... )
Создадим таблицу
CREATE TABLe tbl3 (
id INT(11) NOT NULL,
value INT(11) default NULL
);
INSERT IGNORE INTO tbl3 VALUES(1, 230);
INSERT IGNORE INTO tbl3 VALUES(1, NULL);
INSERT IGNORE INTO tbl3 VALUES(3, 405);
INSERT IGNORE INTO tbl3 VALUES(4, NULL);
Запрос
SELECT COUNT(id), COUNT(value) FROM tbl3;
Форма функции COUNT(*) возвращает общее количество строк в таблице, не
зависимо от того, принимает какое-либо поле значение NULL или нет
SELECT COUNT(*) FROM tbl2;
Функция COUNT() может быть использована не только для подсчета общего
количества записей в таблице, но и для подсчета количества строк в выборке
с условием WHERE
SELECT COUNT(*) FROM tbl3 WHERE value < 300;
Подсчет количества товара в каждом разделе
SELECT id_catalog, COUNT(*) AS total FROM products
GROUP BY id_catalog ORDER BY total DESC;
Третий вариант функции COUNT () позволяет использовать ключевое слово
DISTINCT, которое обеспечивает подсчет только уникальных значений столбца
SELECT COUNT(id_catalog), COUNT(DISTINCT id_catalog) FROM products;
Объединение значений группы
Для объединения значения группы предназначена функция GROU P _ CONCAT ( ) ,
которая имеет следующий синтаксис :
GROUP_CONCAT ( [ DISTINCT ] expr [, expr ... ]
[ORDER ВY { unsigned_integer | col_name | expr }
[ASC | DESC] [, col_name ... ]]
[SEPARATOR str_val] )
В простейшем случае функция принимает имя столбца expr и возвращает
строку со значениями столбца, разделенными запятыми.
SELECT GROUP_CONCAT(id_catalog) FROM products;
Ключевое слово DISTINCT требует вернуть только уникальные значения
столбца, а ключевое слово SEPARATOR позволяет задать в качестве
разделителя значений произвольный символ.
SELECT GROUP_CONCAT(DISTINCT id_catalog) FROM products;
SELECT GROUP_CONCAT(DISTINCT id_catalog SEPARATOR '-') FROM products;
SELECT GROUP_CONCAT(DISTINCT id_catalog ORDER BY id_catalog DESC SEPARATOR '-') AS str FROM products;
SELECT GROUP_CONCAT(price ORDER BY price DESC)
FROM products GROUP BY id_catalog;
Поиск минимального и максимального значений
Для поиска минимального значения в столбце expr предназначена функция
MIN() , которая имеет следующий синтаксис:
MIN([ DISTINCT ] expr)
В качестве аргуме нта expr обычно вы ступает имя стол бца. Необязательное
ключевое слово DISTINCT позволяет дать указание СУБД MySQL обрабатывать
только уникальные значения столбца expr.
SELECT MIN(price) FROM products;
Использование конструкции GROU P ВY id_catalog позволяет найти
минимальную цену для каждого из разделов каталога
SELECT id_catalog, MIN(price) FROM products GROUP BY id_catalog;
Для поиска максимального значения в столбце expr предназначена функция
MAX() , которая имеет следующий синтаксис:
MAX([ DISTINCT ] expr)
SELECT MAX(price) FROM products;
SELECT id_catalog, MAX(price) FROM products GROUP BY id_catalog;
Сумма столбца
Сумму столбца expr позволяет подсчитать функция SUM(), которая имеет
следующий синтаксис:
SUМ( [ DISТINCT ] expr)
SELECT SUM(price) FROM products;
SELECT id_catalog, SUM(price) FROM products GROUP BY id_catalog;
Разное
Miscellanеоus-функции, что переводится как смешанные функции, т. е. функции,
которые не поддаются классификации.
Преобразование IР-адреса
Функция INET_ATON (address) принимает IР-адрес address и представляет
его в виде целого числа
SELECT INET_ATON('62.145.69.10'), INET_ATON('127.0.0.1');
Работа с сокращенной формой IР-адреса
SELECT INET_ATON('127.0.01'), INET_ATON('127.1');
Функция INET_NTOA ( address ) принимает IP -aдpec в виде числа (результат
выполнения функции INET_ATON ( ) ) и возвращает адрес в виде строки,
состоящей из четырех чисел, разделенных точкой
SELECT INET_NTOA(1049707786), INET_NTOA(2130706433);