Felhasználói eszközök

Eszközök a webhelyen


mysql:my-6

Оператори та функції 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)
%pAM або 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] * 'позначає будь-яку кількість цифр, а`. *' - будь-яку кількість будь-яких символів.
idname
1Samsung Widescreen 1080p
2Geforce GT 8800
3Mini Tv
4Audio System
5HTC PRO 2

Повнотекстовий пошук в таблицях типу 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). Без врахування тегів. Якщо статті проіндексовані для повнотекстового пошуку, то з індексу вибираються декілька (наприклад, десять) найбільш використованих слів, потім виконується пошук по ним.

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

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

mysql/my-6.txt · Utolsó módosítás: 2021/08/11 21:30 szerkesztette: holovacs