II. RF KMF DokuWiki

Створення бази даних

  CREATE DATABASE <Ім’я БД>; 
  CREATE DATABASE SalesDept; 

При установці MySQL встановлюється кодування. Задане кодування для БД буде дійсне для всіх таблиць БД.
Його можна змінити для кожної БД:

  CREATE DATABASE <Ім’я БД>
  CHARACTER SET <Ім’я кодування> COLLATE <правило порівняння>; 
  CREATE DATABASE SalesDept CHARACTER SET cp1251 COLLATE cp1251_general_ci; 

Для порівняння українських текстів використовується: COLLATE utf8_unicode_ci.

Видалення БД:

 DROP DATABASE < Ім'я бази дани**Félkövér szöveg**х>; 

Можливості кодування в MySQL
Отримання списків кодування і правил порівняння

SHOW CHARACTER SET;  
SHOW COLLATION;
Пр. SHOW COLLATION LIKE %1251 %;. 

Закінчення «_ci» (case insensitive - «нечутливий») – при порівнянні та сортуванні регістр символів не враховується;

• «_cs» (case sensitive - «чутливий») – регистр враховується;
• «_bin» (binary) – сортування по числовим кодам символів.

Зміна кодування і правила порівняння:

 ALTER DATABASE <Ім'я бази даних>  CHARACTER SET < Ім'я кодування>   [COLLATE < Ім'я правила порівняння>]; 

Нове кодування буде використано лише для нових даних.

Кодування і правило порівняння для стовпця В MySQL можна вказати кодування \\окремо для кожного символьного стовпця:

CHARACTER SET <Ім’я кодування> 
COLLATE <Ім’я правила порівняння>. 

Наприклад, для того, щоб імена кліентів зберігались в кодуванні CP-1251, у випадку кодування для всієї таблиці Customers (Кліенти) є UTF-8, столбец name можно описат таким чином:

name VARCHAR(100) CHARACTER SET cp1251 
	 COLLATE cp1251_general_ci 

Вибір активної БД (відкриття БД) USE < Ім'я бази даних>;

USE SalesDept; 

Після команди USE можна виконувати операції з таблицями цієї БД без імені БД, як префіксу.
Пр. Замість SalesDept.Customers – Customers.

Вивід списку всіх БД на сервері MySQL.

SHOW DATABASES;   

В БД завжди знаходяться три системні бази даних:

• INFORMATION_SCHEMA – словник БД (інформація про всі БД), який можна лише проглядати (зміни в ньому автоматично виконує СУБД MySQL).
• mysql – службова БД, яку використовує сервер MySQL. В ній зберігаються відомості про зареєстрованих користувачів, їх права доступу, довідкова інформація,…
• test – порожня база даних, яку можна використовувати для навчання, експериментування (її можна видалити).

Цілі числа

BIT[(N)] – бітове число з N бітів. Якщо N не вказано – один біт. TINYINT UNSIGNED: 0.. 255. SMALLINT UNSIGNED : від 0.. 65 535. MEDIUMINT UNSIGNED : 0.. 16 777 215. INTEGER (синонім INT) : -2 147 483 648 .. 2 147 483 647. INT UNSIGNED: 0.. 4 294 967 295.

Для простого первинного ключа зручно використовувати тип SERIAL:

SERIAL (= BIGINT, UNSIGNED, NOT NULL, AUTO_INCREMENT,  UNIQUE) 

Використання AUTO_INCREMENT За допомогою функції SQL LAST_INSERT_ID () можна отримати останнє автоматично згенероване AUTO_INCREMENT значення.
Для стовпчика AUTO_INCREMENT рекомендується використовувати найменший цілочисельний тип даних, який достатній для того, щоб вмістити максимальну необхідне значення послідовності. Коли стовпчик досягає верхньої межі заданого типу даних, тоді неможливо згенерувати наступний порядковий номер.
Використовуйте UNSIGNED атрибут, щоб збільшити діапазон. Наприклад, для типу TINYINT максимально допустимий порядковий номер становить 127, а для TINYINT UNSIGNED – 255. Щоб почати AUTO_INCREMENT з значення, відмінного від 1, встановіть це значення за допомогою CREATE TABLE або ALTER TABLE, наприклад:

ALTER TABLE Т AUTO_INCREMENT = 100;

Тип BOOL (BOOLEAN). Приймає значення: TRUE, FALSE.

FLOAT: від -3,402823466Е38 до -1,175494351Е-38, 4 байт (точність 7 цифр)
DOUBLE ( DOUBLE PRECISION або REAL) : 1,7976931348623157Е308 до -2,2250738585072014Е-308, 8 байт (точність 15 цифр)
FLOAT(N). При N=1..24 = FLOAT, при N=25..53 = DOUBLE.
Пр. 47.85 або 4.785E+04.

• DECIMAL ( DEC, NUMERIC або FIXED). Може мати до 65 значущих цифр і до 30 цифр після коми. По замовчуванню: (10, 0).
  

DECIMAL зберігаються, як рядки, а не як двійкове число з плаваючою крапкою. Можна задати максимальну кількість значущих цифр і цифр після коми.
FLOAT(7, 5) – 2 цифри в цілій частині, 5 цифр після коми.

• UNSIGNED – від’ємні значення заборонені.
• ZEROFILL – при виводі зображення числа доповнюється нулями.

Приклад

DOUBLE(10, 5) ZEROFILL	12.23 виведеться, як 0012.23000. 

DATE.  Дата в форматі «YYYY-MM-DD».
DATETIME.  Дата і час в форматі «YYYY-MM-DD HH:MM:SS».
TIME. Час в форматі «HH:MM:SS». 
TIMESTAMP.  Формат «YYYY-MM-DD HH:MM:SS». 

Команди INSERT , UPDATE автоматично записують у стовпeць типу TIMESTAMP системну дату і час. Якщо системну дату і час потрібно вставляти лише для команди INSERT, то після TIMESTAMP задається опція

DEFAULT CURRENT_TIMESTAMP. 

YEAR(4) (або YEAR).  Рік у форматі «YYYY». 
YEAR(2).  Рік у форматі «YY». 

Діапазон значень в форматі «YY» від 70 ( 1970 р.) до 69 ( 2069 р.). Якщо у стовпець типу дати-часу вноситься символьне або числове значення, то MySQL перетворює їх до типу дати-часу.

• CHAR[(N)] N = 1..255. Якщо N не задано CHAR, то 1 символ.
• VARCHAR (N), N ⇐ 65535 – рядок змінної довжини до N байт (символів ???).

При однобайтовому кодуванні ( KOI8-R, CP-866, CP-1251) символів ⇐ 65 535. Для кодування UTF-8 (Unicode Transformation Format) MySQL виділяє 2 або 3 байтів на символ. Кількість символів ⇐ 21 844.

• BINARY(N) бінарний (байтовий) рядок фіксованої довжини N – сортування символів з врахуванням регістру та порядком таблиці ASCII.
• VARBINARY(N) бінарний (байтовий) рядок змінної довжини.

Тип BLOB – двійковий об'єкт великого розміру, який може зберігати дані змінної довжини. BLOB можна розглядати, як стовпець типу VARCHAR BINARY.
Існує 4 модифікації цього типу:

• TINYBLOB (до 255 байт),
• BLOB (до 65535 байт),
• MEDIUMBLOB (до 16777216 байт),
• LONGBLOB (до 4 294 967 295 байт).

Для BLOB сортування та порівняння даних виконується з врахуванням регистру.

Тип TEXT можна ввавжати типом BLOB, який незалежний від регистру. Стовпець TEXT можна розглядати, як стовпець VARCHAR ”необмеженої” довжини. Має 4 модифікації - TINYTEXT, TEXT, MEDIUMTEXT і LONGTEXT, які відповідають чотирьом типам BLOB.

• TINYTEXT символьний рядок змінної довжини ⇐255 байт;
• TEXT, довжина до 65535 байт;
• MEDIUMTEXT, довжина до 16 777 215 байтів.
• LONGTEXT, довжина до 4 294 967 295 байтів.

EXT[(N )] – символьний рядок змінної довжини. Якщо кількість символів не вказана, то: 65 535 байт. Якщо вказана, то створюється стовпець з типом TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT найменшого достатнього розміру.

Основні властивості типів TEXT та BLOB

• Стовпці з типами BLOB і TEXT можна індексувати.
• Для типів BLOB і TEXT не може бути заданим атрибут DEFAULT.
• Для забезпечення можливість виконання команд GROUP BY або ORDER BY з стовпцями типу BLOB, TEXT необхідно виділити частину стовпця фіксиваної довжини за допомогою функції SUBSTRING.

SELECT avtor, SUBSTRING(zmist,1, 20) AS substr FROM kniga ORDER BY substr; Без використання SUBSTRING сортування виконується на основі перших байтів, кількість яких задається системною змінною max_sort_length.
Значення max_sort_length по замовчуванню = 1024.
При запуску серверу це значення можна змінити у команді

 mysqld з параметром –о. 

Функція SUBSTRING () вибирає підрядок з рядка, починаючи з будь-якої позиції (використовується з MySQL 4.0 ):

SUBSTRING (string, start [, length])

або:

SUBSTRING (string FROM start [FOR length] )

Приклади:

SELECT SUBSTRING (CustomerName, 2, 5) AS ExtractString  FROM Customers;
SELECT SUBSTRING ("SQL Tutorial", 5, 3) AS ExtractString;
SELECT SUBSTRING ("SQL Tutorial", 5 ) AS ExtractString;

При групуванні виразів, які включають величини BLOB або TEXT, є можливість вказати позицію стовпця:

SELECT id, SUBSTRING(blob_col, 10,100) FROM T1 GROUP BY 2;
SELECT id, SUBSTRING(blob_col, 20,100) AS b FROM T1 GROUP BY b; 

Максимальний розмір даних для BLOB або TEXT визначається його типом, але найбільше значення, яке фактично може бути передано між клієнтом і сервером, обмежено величиною доступної пам'яті і розміром буферів зв'язку.

Розмір буферу блоку передачі можна змінити, але це необходимо 

зробити як на сервері, так і на стороні клієнта.
Внутрішнім представленям величин типу BLOB або TEXT є об'єкт, який розміщується окремо (не в таблиці) на відміну від інших типів стовпців, для яких пам'ять виділяється зразу, коли таблиця відкривається.

• ENUM('<Значення 1>', '<Значення 2>',…). Рядок, який містить точно елемент із заданого списку. В список можна включити до 65535 елементів.
• SET('<Значення 1>', '<Значення 2>',…). Рядок, який може містити будь-який набір елементів із заданого списку (в тому числі NULL). В список можна включити до 64 елементів. Елементи списку не повинні містити коми.

Типи даних ENUM і SET є символьними лише умовно. Вони задаються списком рядків, але при внутрішньому представленні БД елементи цих множин зберігаються, як номери (числа).
Елементи типу ENUM нумеруються починаючи з 1. В залежності від числа елементів в списку під стовпець може відводитись 1 байт (до 256 елементів) або 2 байти (від 257 до 65536 елементів у списку).
Елементи з множини SET обробляються як біти, розмір типу визначвється числом елементів в списку: 1 байт ( 1.. 8 елементів), 2 байти ( 9.. 16 елементів), 3 байти (17.. 24 елементів), 4 ===байта (25.. 32 елементи) і 8 байт ( 33.. 64 елементів).

Починаючи з версії 5.7 MySQL може працювати з типом даних JSON (JavaScript Object Notation).
Це зручно в тому випадку, коли в таблиці необхідно зберігати масив або об'єкт. В явному виді такі дані не можна зберігати (реляційна модель!), тому їх приводять до формату JSON, в якому дані зберігаються, як звичайний рядок.
JSON - простий формат обміну даними, зручний для читання та написання як людиною, так і комп'ютером. Він базується на підмножині мови програмування JavaScript. Наприклад:

{ "firstName": «Іван", "lastName": «Іванов", "address": { "streetAddress": «Вузька в., 10, кв.21", "city": 
"Львів", "postalCode": 101111 }, "phoneNumbers": [ "123-1234", "123-4567" ] }

Такі дані можна використовувати в SQL-запитах.

CREATE TABLE Ім’я_таб   (Ім’я_стов_1  Тип_1 [Властивості стов_1], ..., Ім’я_стов_ N Тип_N [Властивості_N] [Опис 
 ключів та індексів]) [Опціональні властивості таблиці];  

CREATE TABLE Customers  (id SERIAL, name VARCHAR(30), phone VARCHAR(15), address  VARCHAR(50), rating INT,      
  PRIMARY KEY (id))      ENGINE InnoDB CHARACTER SET utf8; 

Тип SERIAL (= типам BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE) – id нумерує рядки автоматично.
ENGINE задає тип таблиці: InnoDB, MyISAM.
Таблиці типу InnoDB забезпечують підтримку цілісності зв’язків між таблицями.

Опціональні властивості стовпців таблиці

• Кодування можна задавати також для окремого стовпця:

CHARACTER SET <Ім’я кодування> COLLATE <Ім’я правила порівняння>. По замовчуванню CHARACTER SET задає кодування UTF-8.

• COMMENT ‘Коментар'. Довжина до 255 символів.
• DEFAULT <Значення>. Не можна використовувати з типами TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, а також для AUTO_INCREMENT.
• [ <Ім’я ключа>] PRIMARY KEY (<Список стовпців>).

Ключові стовпці автоматично мають властивість NOT NULL.

• Для типів TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT потрібно вказати кількість символів для ключа: PRIMARY KEY (description, details(10)).

INDEX [<Ім’я индекса>] (<Список стовпців>) - створює індекс для вказаних стовпців. 

При створенні індексу для стовпців з типом TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB і LONGTEXT потрібно вказати кількість символів для індексування:

INDEX ( books(20)).

Замість ключового слова INDEX можна використати його синонім – слово KEY.

[CONSTRAINT < Ім’я обмеження>] UNIQUE [<Ім’я індексу>] (<Список стовпців>)

створює унікальний індекс для вказаних стовпців – набори значень у стовпцях унікального індексу повинні бути різні (за виключенням значень NULL).
Замість ключового слова UNIQUE можна його синоніми: UNIQUE INDEX, UNIQUE KEY.

JSON стовпці не можуть бути проіндексовані. Це обмеження можна обійти, створивши індекс для згенерованого стовпчика, який вибирає з JSON- стовпчика скалярне значення. Наприклад.

 CREATE TABLE t_emp (c JSON, gen INT GENERATED ALWAYS AS (c->"$.id"), 
   INDEX i (gen)); 
 INSERT INTO t_emp (c) VALUES ('{"id": "1", "name": "Іван"}'), 
   ('{"id": "2", "name": "Василь"}'),
   ('{"id": "3", "name": "Володимир"}'), 
   ('{"id": "4", "name": "Аня"}');
  SELECT c->>"$.name" AS name FROM t_emp WHERE gen > 2;

Результат: Володимир, Аня.

Для NDB-таблиць можна вказати, чи буде стовпець зберігатися на диску або в пам'яті, за допомогою опції STORAGE.

• STORAGE DISK – збереження стовпчика на диску,
• STORAGE MEMORY – збереження стовпчика в пам'яті.

CREATE TABLE повинен включати опцію TABLESPACE.

CREATE TABLE t1 (c1 INT STORAGE DISK,
        c2 INT STORAGE MEMORY)  TABLESPACE ts_1 ENGINE NDB; 

NDB Cluster, NDB кластерний тип особливо підходить для прикладних програм з високоефективними потребами для пошуку у БД (час, доступність).
MySQL Cluster складається з набору комп'ютерів, кожен виконує ряд процесів, включаючи сервер MySQL, вузли зберігання для NDB, сервер управління і (можливо) спеціалізовані програми доступу до даних.
Всі ці програми працюють синхронно, щоб сформувати MySQL Cluster.
Таблиці зберігаються в вузлах пам'яті для NDB Cluster. Вони безпосередньо доступні з усіх інших серверів MySQL в кластері.
Якщо одна прикладна програма модифікує дані, то всі інші сервери, які роблять запити, можуть побачити ці зміни негайно.
На даний час NDB кластерний тип підтримуються операційними системами Linux, Mac OS X і Solaris. Ведуться роботи по підтримці NDB Cluster на всіх операційних системах, підтримуваних MySQL, включаючи Windows.

 CREATE TABLE t1 (
  c1 INT AUTO_INCREMENT PRIMARY KEY,
  c2 VARCHAR(100),
  c3 VARCHAR(100) )
 ENGINE=NDB;

Для таблиць типу NDB опція STORAGE DEFAULT эквивалентна STORAGE MEMORY.

MySQL підтримує невидимі стовпці починаючи з MySQL 8.0.23. Невидимий стовпець прихований від запитів, але може бути доступний при явному посиланні на нього. До MySQL 8.0.23 всі стовпчики були видимі.
Невидимі стовпці можуть бути корисними, якщо додаток використовує SELECT * запити для доступу до таблиці і продовжує працювати без змін, навіть тоді, коли у таблицю додається новий невидимий стовпець. Стовпець залишається «прихованим» від SELECT * запитів, і додаток продовжує працювати у звичайному режимі. Нова версія програми може посилатися на невидимий стовпець, якщо це необхідно, шляхом явної посилання на нього.

CREATE TABLE t1 (i INT,  j DATE INVISIBLE);
ALTER TABLE t1 ADD COLUMN k INT INVISIBLE;
ALTER TABLE t1 CHANGE COLUMN j j DATE VISIBLE;
ALTER TABLE t1 MODIFY COLUMN j DATE INVISIBLE;
ALTER TABLE t1 ALTER COLUMN j SET VISIBLE;
ALTER TABLE t1 ALTER COLUMN j SET INVISIBLE;

Таблиця повинна мати хоча б один видимий стовпець. Спроба зробити всі стовпці невидимими призводить до помилки. Невидимі колони підтримують звичайні атрибути стовпців:
NULL, NOT NULL, AUTO_INCREMENT, і.т.д. Згенеровані стовпці можуть бути невидимими.
Індекси можуть містити невидимі стовпці, включаючи визначення PRIMARY KEY і UNIQUE індекси. Хоча в таблиці повинен бути хоча б один видимий стовпець, визначення індексу не обов'язково повинно мати видимі стовпці.
Обмеження зовнішнього ключа можуть бути визначені для невидимих стовпців, а обмеження зовнішнього ключа можуть посилатися на невидимі стовпці.
CHECK-обмеження можуть бути визначені для невидимих стовпців. Для нових або змінених рядків порушення CHECK-обмеження невидимого стовпця викликає помилку.
CREATE TABLE … LIKE включає невидимі стовпці, і вони невидимі в новій таблиці.
CREATE TABLE … SELECT не включає невидимі стовпці, якщо вони явно не вказані в SELECT-і. Однак навіть при явній посиланням стовпець, невидимий в існуючій таблиці, видно в новій таблиці:

 CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
 CREATE TABLE t2 AS SELECT col1, col2 FROM t1;
 SHOW CREATE TABLE t2 \ G
     Table: t2
Create Table: CREATE TABLE `t2` (
 `Col1` int DEFAULT NULL,
 `Col2` int DEFAULT NULL
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

Якщо невидимість повинна бути збережена, то потрібно дати визначення невидимого стовпчика в CREATE TABLE:

 CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
 CREATE TABLE t2 (col2 INT INVISIBLE) AS SELECT col1, col2 FROM t1;

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

 CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
  INSERT INTO t1 (col1, col2) VALUES (1, 2), (3, 4);
  SELECT * FROM t1;

^col1^

 SELECT col1, col2 FROM t1;

Для INSERT ( REPLACE) неявне присвоєння за замовчанням відбувається з відсутнім списком стовпців, порожнім списком стовпців або непустою списком стовпців, який не включає невидимий стовпець:

 CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
 INSERT INTO t1 VALUES (...);
 INSERT INTO t1 () VALUES (...);
 INSERT INTO t1 (col1) VALUES (...);

Для перших двох INSERT операторів VALUES () список повинен містити значення для кожного видимого стовпчика, а не невидимого стовпчика. Для третього INSERTоператора VALUES () список повинен містити таку саму кількість значень, що і кількість іменованих стовпців.

ENGINE <Тип таблиці>.

• Тип InnoDB підтримує транзакції і блокування окремих рядків (забезпечується висока продуктивність операцій з даними у режимі «багато користувачів»). Підтримується цілісність зв’язків між таблицями.
• Тип MyISAM – висока швидкість пошукових запитів, менше навантаження на системні ресурси. Не підтримують операції транзакцій і цілісність зв’язків між такими таблицями.
• Archive – стиснутий формат (коли рідко використовується таблиця)
• Federated (зберігає структуру таблиці і посилання їх на дані )
• Memory (зберігається в ОП, кеш-індекси)
• CVS (ASCII-файли, поля відокремлені комами)
• Merge (віртуальна таблиця для об'єднання декількох MyISAM-таблиць).

Зовнішній ключ таблиці типу InnoDB:

 [CONSTRAINT <ім’я зовнішнього ключа>] FOREIGN KEY [<ім’я індексу>] (<Список стовпців>)  REFERENCES <ім’я 
  батьківської таблиці> (<Список стовпців первинного ключа батьківської таблиці >)  [<Правила підтримки 
  цілісності зв’язку>]

Після побудови зовнішнього ключа дочірня таблиця буде зв’язана з батьківською таблицею. Для числових стовпців повинні співпадати розміри;
для символьних – кодування і правило порівняння значень.
Зовнішній ключ не може містити стовпці типу TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT.
Для стовпців зовнішнього ключа автоматично створюється індекс, тому операції з зовнішніми ключами при перевірці цілісності зв’язку виконується швидко.

Правила підтримки цілісності зв’язку для операції видалення та зміни даних
Пр. Таблиці Products (Товари) і Orders (Замовлення)

 CREATE TABLE Products   (id SERIAL, description VARCHAR(100),  details TEXT,  	price DECIMAL(8,2), PRIMARY 
 KEY (id)) 
ENGINE InnoDB CHARACTER SET utf8; 

 CREATE TABLE Orders (id SERIAL,  date DATE, product_id BIGINT UNSIGNED NOT NULL,  qty INT UNSIGNED, amount 
 DECIMAL(10,2), customer_id BIGINT UNSIGNED, PRIMARY KEY (id),  
   FOREIGN KEY (product_id) REFERENCES Products (id) 
   ON DELETE RESTRICT ON UPDATE CASCADE, 
   FOREIGN KEY (customer_id) REFERENCES Customers (id) 
   ON DELETE RESTRICT ON UPDATE CASCADE) 
   ENGINE InnoDB CHARACTER SET utf8;