II. RF KMF DokuWiki

Ez az oldal csak olvasható. Megtekintheted a forrását, de nem változtathatod meg. Ha úgy gondolod, hogy ez helytelen, kérdezd az Adminisztrátorokat!
===Оператори та функції 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). Без врахування тегів. Якщо статті проіндексовані для повнотекстового пошуку, то з індексу вибираються  декілька (наприклад, десять) найбільш використованих слів, потім виконується пошук по ним.\\ 

Приклад. Знайти користувачів по інтересам для заданого користувача. Використовуючи повнотекстового пошук легко знайти інших користувачів, у яких інтереси найбільше відповідають даним. \\ 

**Зауваження.** Потрібно врахувати, що самі індекси можуть займати дуже багато місця на диску, а будь-які зміни в таблицях  потребують багато часу.
II. RF KMF DokuWiki

Felhasználói eszközök

Eszközök a webhelyen

Eszközök az oldalon
