===Оператори та функції MySQL==== **STRCMP(x, y)** – функція порівнює рядки х і у, повертає: – 1, якщо х < у; 0, якщо х і у співпадають; 1, якщо х> у; NULL, якщо один з аргументів NULL. **MATCH (<Список стовпців>) AGAINST (<Критерій пошуку>)** – повнотекстовий пошук по заданим ключовим словам. Для стовпців має бути створений повнотекстовий індекс. MATCH для кожного рядка повертає величину релевантності (ступінь близькості рядка критерію пошуку). Якщо MATCH використовується в параметрі WHERE команди SELECT, то результатом запиту будуть рядки з відмінною від нуля релевантністю, впорядковані по зменшенню релевантності. SELECT * FROM Products WHERE MATCH (description) AGAINST (‘президент'); Логічні оператори – ** AND, OR, NOT, XOR.**\\ Оператори і функції, які базуються на порівнянні:\\ *LEAST(a1, a2, …, an) – функція повертає найменше значення (NULL, при аі = NULL). *GREATEST(a1, a2, …, an) – функція повертає найбільше значення (або NULL). *INTERVAL(a, b1, b2, …, bn), де b1 < b2 < … < bn цілі. Результат: порядковий номер найбільшого з чисел bі, які <= a. \\ Якщо: a < b1 – функція повертає значення 0; bi < a < bi+1 – функція повертає номер i; якщо: a > bn – функція повертає значення n. *COALESCE (a1, a2, …, an) – функція повертає перше аі, яке не = NULL. *IF(a, b, c) – якщо логічний вираз a =TRUE, то повертає значення b, інакше повертає c. *IFNULL(a, b) – функція повертає значення a, якщо воно не дорівнює NULL, і значення b, якщо a = NULL. (аналог NVL в ORACLE ). *NULLIF(a, b) – функція повертає значення NULL, якщо a = b, інакше – значення a. Приклади. SELECT IFNULL('some value', 'some other value'); повертає 'some value' SELECT IFNULL (NULL, 'some other value'); повертає 'some other value' SELECT COALESCE (NULL, 'some other value'); повертає 'some other value' – аналог функції IFNULL SELECT COALESCE (NULL, 'some value', 'some other value'); повертає 'some value' SELECT COALESCE (NULL, NULL, NULL, NULL, 'first non-null value'); повертає 'first non-null value' **Арифметичні оператори**\\ *а + b *а – b * а * b * а / b (частка) * a % b (або MOD (a,b)) (остача) * а DIV b (частка при діленні цілих чисел). **Алгебраїчні функції**\\ *ABS(x) *CEIL(x) – заокруглення в більшу сторону. Пр. CEIL(12345.6789) = 12346, CEIL(-12345.6789) = -12345 *FLOOR(x) – заокруглення в меншу сторону *ROUND(x, n) – заокруглення. Пр. ROUND(12345.6789, 2) = 12345.68, ROUND(12345.6789, -2) = 12300 *ROUND(x) – заокруглення до цілого *POW(x, y) (або POWER(x, y) ) – піднесення до степеня *EXP(x) * LN(x) (LOG(x)) * LOG10(x) * LOG2(x) * LOG(a,x) * PI()=3,1415… *SIGN(x) – знак числа ( 1, 0, – 1) *SQRT(x) *TRUNCATE(x, n) – відкидає «зайві» цифри. TRUNCATE(12345.6789, 2) = 12345.67, TRUNCATE(12345.6789, -2) = 12300, **Псевдовипадкові числа**\\ *RAND() – псевдовипадкове число від 0 до 1 *RAND(x) – псевдовипадкове число від 0 до 1, ціле x – початкове значення для генератора псевдовипадкових чисел. SELECT RAND()*(y-x)+x; на інтервалі: [x, y] SELECT FLOOR(RAND()*(y-x+1))+x; цілі від x до y включно **Тригонометричні функції**\\ *SIN(x) * COS(x) * TAN(x) * COT(x) * ASIN(x) * ACOS(x) * ATAN(x) * DEGREES(x) – повертає градусну міру кута x в радіанах * RADIANS(x) – повертає радіанну міру кута x в градусах. **Функції дати і часу**\\ *CURDATE(), current_date – повертає актуальну дату *CURTIME(), current_time – повертає актуальний час. *NOW(), LOCALTIMESTAMP – повертає актуальну дату і час. *SYSDATE() – повертає системну дату і час серверу. *DATE('<Дaтa і час>') – повертає лише дату, відкидаючи час. *TIME('<Дaтa і час>') – повертає лише час, відкидаючи дату. *DAYOFWEEK('<Дата або дата і час>') – обчислює порядковий номер дня тижня (1 – неділя, 2 – понеділок,…). *MONTH('<Дата або дата і час>') повертає номер місяця. *YEAR('<Дата або дата і час>') повертає рік. *HOUR('<Час або дата і час>') повертає години. *MINUТЕ('< Час або дата і час >') повертає хвилини. *SECOND('< Час або дата і час >') повертає секунди. * ADDDATE('<Дата або дата і час>',<Кількість днів>) – додає до дати <Кількість днів> * ADDTIME(<Час або дата і час>,< час, що додається>). * SUBDATE('<Дата або дата і час>',<Кількість днів>) – віднімає <Кількість днів>. * DATEDIFF('<Дата_1>','< Дата_2 >') – різниця в днях між першою і другою датою. SELECT ADDDATE('2019-05-01', 7) as nextweek, CURDATE() as today; Результат: 2019-04-26, 2019-04-19 **Функції форматів для дати-час**\\ *DATE_FORMAT('<Дата, час>', '<Формат>') – повертає рядок-дату згідно формату. Параметри формату: \\ %c – номер місяця (0-12)\\ %d – число місяця (00–31)\\ %H – години (00–23)\\ %h – години (01–12)\\ %i – хвилини (00–59)\\ %m – номер місяцю (00–12)\\ %r – час 12-год (HH:MM:SS AM, PM)\\ %S – секунди (00–59)\\ %T – час у 24-год (HH:MM:SS)\\ %w – номер дня тижня (0 – неділя, 1 – понеділок,…)\\ %Y – номер року у форматі YYYY\\ %y – номер року в форматі YY\\ DATE_FORMAT('2007-12-12 12:30:00', %e %M %Y г. %k годин %i хвилин') повертає значення 12 Грудня 2007 г. 12 годин 30 хвилин. TIME_FORMAT('<Час або дата і час>', '<Формат>')\\ STR_TO_DATE('<Рядок>', '<Формат>') – рядок перетворює у дату/час\\ SELECT ADDDATE("2017-06-15 09:34:21", INTERVAL -3 HOUR) new_date; SELECT DATE_ADD(CURDATE(), INTERVAL 7 day) 'через 7 днів'; SELECT sum(sal+comm) sum1, sum(sal+ifnull(comm,0)) sum2 FROM emp ; SELECT sum(sal+ifnull(comm,0)) FROM emp ; SELECT FLOOR(RAND() * 401) + 100 AS `100...500` ; ^Формат^Опис^ |%M|Назва місяця| |%W|Назва дня тиждня (воскресенье...суббота)| |%D|День мiсяцz з суффіксом (0st, 1st, 2nd, 3rd и т.д.)| |%Y|Рік, 4 розряда| |%y|Рік, 2 розряда| |%d|День місяця, число (00..31)| |%e|День місяця, число (0..31)| |%m|Місяць, число (00..12)| |%c|Місяць, число (0..12)| |%b|Скорочена назва місяця| |%j|День року (001..366)| |%H|Година (00..23)| |%k|Година (0..23)| |%h|Година (01..12)| |%I|Година (01..12)| |%l|Година (1..12)| |%i|Хвилина (00..59)| |%r|Час, 12. формат (hh:mm:ss [AP]M)| |%T|Час, 24. формат (hh:mm:ss)| |%s|Секунди (00..59)| |%p|AM або PM| |%u|Тиждень (00..53),де понеділок вважається першим днем тиждня| **Символьні функції**\\ * CHAR_LENGTH('< Рядок >') (або CHARACTER_LENGTH ( '<Рядок>')) – кількість символів в рядку, * CHAR_LENGTH ( 'Крилов') - повертає значення 6. * CHAR(<Код 1>,<Код 2>,...,<Код N> [USING <Код>]) перетворення кодів в символи. Функція CHAR () отримує в якості аргументів коди символів і повертає рядок, що складається з цих символів. При необхідності можна явно вказати кодування, що зіставляє коди символів. CHAR (77, 121, 83, 81, 76); -- ‘MYSQL’ CHAR (53402,53632,53643,53435,53438,53426 USING utf8) - повертає значення Крилов. CHAR (138,224,235,171,174,162 USING cp866) - повертає значення Крилов. FIELD ( '<Рядок-зразок>', '<Рядок 1>', '<Рядок 2>', ..., '<Рядок N>'). Повертає порядковий номер рядка, що збігається із зразком, і 0, якщо жодна з рядків 1, 2, ..., N не збігається зі зразком.\\ FIELD ( 'Петров', 'Іванов', 'Петров', 'Сидоров') - повертає значення 2. CONCAT('<Рядок 1>','< Рядок 2>',..., '< Рядок N>'), Повертає результат об'єднання своїх аргументів. CONCAT_WS ( 'Роздільник', '<Рядок 1>', '<Рядок 2>', ..., '<Рядок N>'). Повертає результат об'єднання своїх аргументів, при цьому перший аргумент використовується як роздільник.\\ CONCAT_WS ( ',', 'Іванов', 'Петров', 'Сидоров') - повертає значення Іванов, Петров, Сидоров. ELT (k, '<Рядок 1>', '<Рядок 2>', ..., '<Рядок N>'). Повертає рядок з порядковим номером k.\\ ELT (2, 'Іванов', 'Петров', 'Сидоров') - повертає значення Петров. REPEAT('< Рядок>', N) – рядок, в який <рядок> входить N разів, REVERSE('< Рядок >') – обернений порядок символів, SPACE( N) – рядок з N пробілів. INSERT('<Рядок>', N, k,'<Заміщаючий підрядок>') – повертає рядок, в якому рядок з позиції N і довжини k, замінено заданим підрядком INSERT ( 'ТОВ "Кускус"', 6,3, 'Кіскіс') - замінює підрядок Кус рядка ТОВ «Кускус» підрядком Кіскіс і повертає значення ТОВ «Кіскіскус». REPLACE('<Рядок>','<Підрядок-1, який заміщується>','<Заміщуючий підрядок-2>'), Функція REPLACE () повертає <рядок>, в якому <Підрядок-1> замінено <Підрядком-2>.\\ REPLACE ( ‘Сьогодні дуже холодно’, ‘холодно’, ‘тепло’) - повертає значення ‘Сьогодні дуже тепло’. LENGTH ( '<Рядок>'), (або OCTET_LENGTH ( '<Рядок>‘)). Повертають довжину рядка в байтах. \\ LENGTH ( 'Крилов') - повертає значення 6 при використанні однобайтового кодування і значення 12 при використанні кодування UTF-8. BIT_LENGTH('<Рядок>') – повертає довжину рядка в бітах. BIT_LENGTH ( 'Крилов') - повертає значення 48 при використанні однобайтового кодування і значення 96 при використанні кодування UTF-8. FIND_IN_SET ( '<Рядок-зразок>', '<Рядок-контейнер>'). Функція отримує в якості аргументу рядок-зразок (цей рядок не повинна містити коми) і рядок-контейнер виду <Підрядок 1>, <Підрядок2>, ..., <Підрядок N> і повертає порядковий номер підрядка, що збігається із зразком , інакше 0.\\ FIND_IN_SET ( 'Петров', 'Іванов, Петров, Сидоров') - повертає значення 2. SUBSTR ( '<Рядок>', <Позиція> [, <Довжина>]), SUBSTRING ( '<Рядок>', <Позиція> [, <Довжина>]). Повертають підрядок, що починається з вказаної позиції. Можна вказати довжину одержуваного підрядка. Якщо номер позиції < 0, то відлік починається від кінця рядка.\\ SUBSTR ( 'Сім чудес світу', 6) повертає значення ‘чудес світу’; SUBSTR ( 'Сім чудес світу', 6,5) повертає значення ‘чудес’; LEFT('<Рядок>', N) – лівий підрядок довжини N, RIGHT('< Рядок>', N) – правий підрядок довжини N, LOCATE('<Підрядок>','< Рядок>'[,<Позиція>]) – повертає позицію, з якої починається перше входження підрядка в рядок. Якщо рядок не містить цей підрядок, то повертає 0. Можна вказати позицію, з якої шукається входження підрядка.\\ LOCATE ( 'сто', 'Не май сто рублів, а май сто друзів ") - повертає значення 9, а функція LOCATE (' сто ',' Не май сто рублів, а май сто друзів", 20) повертає значення 28. LOWER('< Рядок >') ) – переводить до нижнього регістру, UPPER('< Рядок >') ) – переводить до верхнього регістру, LTRIM('< Рядок >') рядок, отриманий видаленням початкових пробілів, RTRIM('< Рядок >') повертає рядок, отриманий видаленням кінцевих пробілів, TRIM('< Рядок >') рядок, отриманий видалення початкових та кінцевих пробілів. **Функції COMPRESS() і UNCOMPRESS()**\\ Операція зберігання довгого тексту в БД впливає на швидкодію системи и на вимоги до об'єму дискового простору. Довгий текст рекомендується стискати за допомогою функції COMPRESS(), а перед його використанням виконати обернене перетворення за допомогою функції UNCOMPRESS().\\ SELECT LENGTH(COMPRESS(REPEAT('A', 1000000))); Результат: ~1000 1 000 000 символів ‘A’ стискається в рядок розміром приблизно в 1000 позицій. **Символьні функції типу REGEXP_**\\ Символьна функція REGEXP_INSTR()\\ SELECT REGEXP_INSTR('dog cat dog', 'dog'); -- 1 SELECT REGEXP_INSTR('dog cat dog', 'dog', 2); -- 9 SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}'); -- 1 SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}'); -- 8 Символьна функція REGEXP_REPLACE ()\\ SELECT REGEXP_REPLACE('a b c', 'b', 'X'); -- a X c Символьна функція REGEXP_LIKE ()\\ SELECT REGEXP_LIKE ('abc', ‘ABC'); -- 1 **REGEXP – операції з регулярними виразами**\\ Деякі характеристики розширених регулярних виразів: * `. ' позначає будь-який символ. * `[...] ‘ - будь-який із символів в дужках. Наприклад, `[abc] 'позначає` a', `b 'або` c'. * `[а-z] 'позначає будь-яку букву нижнього регістра, а` [0-9]' - будь-яку цифру. * `* ‘. Наприклад, `x * 'позначає будь-яку кількість символів` x', *`[0-9] * 'позначає будь-яку кількість цифр, а`. *' - будь-яку кількість будь-яких символів. ^id^name^ |1|Samsung Widescreen 1080p| |2|Geforce GT 8800| |3|Mini Tv| |4|Audio System| |5|HTC PRO 2| SELECT id FROM table WHERE name REGEXP '[0-9]'; -- Результат: 1, 2, 5 **Повнотекстовий пошук в таблицях типу MyISAM**\\ MySQL підтримуєь методи повнотекстового пошуку (fulltext search), які дозволяють дуже швидко знаходити потрібні дані у текстах великого розміру.\\ Повнотекстовий індекс в MySQL — це індекс типу FULLTEXT.\\ Повнотекстові індекси можуть використовуватись з таблицями MyISAM (в MySQL 5.6 і вище вони також можуть використовуватись з таблицями InnoDB). \\ Повнотекстові індекси можуть бути створені тільки для стовпців типу CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT або LONGTEXT.\\ Опис FULLTEXT-індексу можна задати в операторі CREATE TABLE при створенні таблиці, або добавлені за допомогою ALTER TABLE або CREATE INDEX.\\ Для великих наборів даних набагато швидше завантажити дані в таблицю, у якої немає FULLTEXT индексу, а після цього створити індекс, ніж завантажити дані в таблицю, у якої вже існує FULLTEXT індекс.\\ Повнотекстовий пошук можна використовувати, як предметний покажчик: зберігає список слів в стовпцю з посиланнями на їх місця в стовпцю. * Повнотекстовий пошук (Fulltext search) дозволяє швидко знаходити потрібну інформацію у великих об'ємах тексту. Це потужний засіб для пошуку у веб-додатках. * На відміну від операторa LIKE, повнотекстовий пошук передбачає створення відповідного повнотекстового індекса, який є своєрідним словником, що містить посилання на слова у текстових полях.\\ Під словом розуміють сукупність з не менш як трьох символів, серед яких немає символу «пробіл». Будь-яке слово (з нульовою смисловою цінністю), що присутнє в стоп-списку (stopword) ігнорується. Наприклад, пошук за словом «УжНУ» у базі даних новин УжНУ не призводить до якихось результатів, оскільки це слово присутнє більш ніж у половині рядків. По суті, це стоп-слово. Структура повнотекстового індексу: \\ FULLTEXT [<Ім’я індексу>] (<Список стовпців>) Приклад створення повнотекстового індексу FULLTEXT: CREATE TABLE articles ( id int(10) unsigned NOT NULL auto_increment, title varchar(200) default NULL, body text, PRIMARY KEY (id), FULLTEXT KEY ft1 (title, body), FULLTEXT KEY ft2 (body) ) ENGINE = MyISAM DEFAULT CHARACTER SET = utf8; В данному випадку можна шукати рядки за допомогою індексу ft1 по полям title і body, або по індексу ft2 лише по полю body.\\ На основі повнотекстового пошуку може бути визначена релевантність – порівняльна міра відповідності запиту знайденій інформації.\\ **Повнотекстовий пошук. Конструкція MATCH-AGAINST**\\ Для повнотекстового пошуку використовується конструкція MATCH(filelds)… AGAINST(words). Вона повертає умовну релевантність, але спосіб її обчислень залежить від вибраного режиму. \\ Пошук у всіх режимах регістрозалежний.\\ **MATCH-AGAINST IN NATURAL LANGUAGE MODE.** Це основний вид пошуку (використовується по замовчуванню). SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('database'); Шукається слово database в полях title і body на основі індексу ft1. Результат автоматично відсортований по релевантності. Цу при умові, якщо блок WHERE містить конструкцію MATCH-AGAINST і не задано сортування ORDER BY. **Релевантність.**\\ Приклад: SELECT *, MATCH (title, body) AGAINST ('database') as Rel FROM articles WHERE MATCH (title, body) AGAINST ('database') > 0; В змінній Rel буде обчислена релевантність. Вона залежить від кількості слів в полях tilte і body і того, наскільки близько задане слово зустрічається до початку тексту, відношення кількості знайдених слів до кількості всіх слів в полі, і.т.д. \\ Релевантність буде ненульовою, якщо слово database зустрінеться в title або в body. Якщо database зустрінеться і у title і у body, то релевантності буде вищою, ніж у випадку, коли воно двічі буде в body. \\ Саме по собі значення релевантності неістотне. Це лиши порівняльна характеристика, по якій можна сортувати результати вибірки.\\ Для IN NATURAL LANGUAGE MODE діє правило «50% threshold»: якщо слово зустрічається більше як у 50% всіх значень поля, то воно не буде враховуватисть у результаті. \\ **Повнотекстовий пошук. MATCH-AGAINST IN BOOLEAN MODE**\\ У бінарному режимі релевантність обчислюється, як умовна міра співпадання заданого шаблону. Є можливість вказати логічні оператори. Крім того, відсутнє автоматичне сортування, якщо є умова WHERE. \\ У логичному режимі повнотекстового пошуку підтримуються наступні оператори:\\ + (знак "плюс"): знак "плюс" перед словом вказує, що це слово повинно міститись у кожному рядку, що повертається в результаті.\\ - (знак "минус"): знак "мінус" перед словом вказує, що це слово не повинно міститись в жодному результуючому рядку.\\ За замовчанням (якщо не не вказані «+», або «-» ) дане слово не є обов’язковим, але рядки, що його містять, будуть оцінюватись вище. \\ Використовують ще інші знаки: " (подвійні лапки), ~ (тiльда), * (зірочка), > (більше), < (менше).\\ Наприклад, оператор < зменшує вклад слова у величину релевантності, а оператор > - збільшує її.\\ **Повнотекстовий пошук. Зв'язок „багато-до-багатьох”**\\ Для сортування можна використати аліас:\\ SELECT *, MATCH (title, body) AGAINST ('+database MySQL' IN BOOLEAN MODE) AS rel FROM articles WHERE MATCH (title, body) AGAINST ('+database MySQL' IN BOOLEAN MODE) ORDER BY rel; Будуть виведені всі записи, які містять слово database, але якщо запис містить слово MySQL, то його релевантність буде вищою. \\ При використанні повнотекстового пошуку інколи можна реалізувати співвідношення багато-до-багатьох без зв'язуючої таблиці.\\ Пр. Нехай є дві великі таблиці: користувачі і групи користувачів. Кожний користувач має відношення до значної кількості різних груп, які в свою чергу містять велику кількість користувачів. При використанні зв'язуючої таблиці запити містять зв'язки трьох таблиць, що навіть при індексуванні може виконуватись довго. \\ Значно швидше буде виконана команда:\\ SELECT * FROM groups WHERE MATCH (groups) AGAINST ('+user2' IN BOOLEAN MODE); **Релевантність, як міра відношення одного об'єкту до іншого**\\ 1). Алгоритм для знаходження статей, «схожих» на дану статтю. Беруться теги даної статті, і виконується повнотекстовий запит по полю з тегами всіх інших статей з сортуванням по релевантності. На початку будуть записані ті статті, які містять максимальне співпадіння по тегам.\\ 2). Без врахування тегів. Якщо статті проіндексовані для повнотекстового пошуку, то з індексу вибираються декілька (наприклад, десять) найбільш використованих слів, потім виконується пошук по ним.\\ Приклад. Знайти користувачів по інтересам для заданого користувача. Використовуючи повнотекстового пошук легко знайти інших користувачів, у яких інтереси найбільше відповідають даним. \\ **Зауваження.** Потрібно врахувати, що самі індекси можуть займати дуже багато місця на диску, а будь-які зміни в таблицях потребують багато часу.