Skip to content

1.Databases_and_tables.md

Latest commit

 

History

History
executable file
·
364 lines (270 loc) · 22.6 KB

1.Databases_and_tables.md

File metadata and controls

executable file
·
364 lines (270 loc) · 22.6 KB

Бази даних. Основи MySQL

Вступ

MySQL відноситься до реляційних СУБД (система управління базами даних), що означає, що в основі проектування та побудови даних лежать зв'язки між ними, і саме зв'язками між даними необхідно керуватися під час проектування баз даних.

Бази даних призначені для роботи з інформацією: для зберігання та збереження, пошуку та редагування даних, підпорядкованих якійсь структурі.

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

В СУБД може бути скільки завгодно баз даних.

Основою однієї бази даних є таблиця. Зовні вона нагадує табличку в екселі. У таблиці в "заголовку" знаходяться імена полів, а кожен "ряд", званий у теорії баз даних записом, містить один запис значення для кожного поля. Якщо таблиця Student, наприклад, містить імена студентів(name) та їх вік(age), то говорять, що у таблиці Student поля name та age. Кожен студент, записаний у таблицю, є одним записом, і виглядає це приблизно так:

Student

name age
Mark 21
Jeny 22
Tom 21

Розглянемо питання створення бази даних та таблиць детальніше.

Підключення до СУБД mySQL

Якщо в системі встановлена СУБД MySQL, можна запустити відповідну консольну програму mysql, яка дозволяє виконувати всі доступні операції з базами даних.

Якщо сприймати параметр як ключ та його значення, то -u username представляє собою пару ключ параметра -u та його значення username Зверніть увагу, що після вказівки ключа параметра (наприклад, -u) можна писати значення параметра як після пропуску, так разом з ключем параметра.

Далі наведено приклад виклику програми з параметрами, вказаними після ключа. Параметри означають, що замість написаного там треба підставити ваші значення:

mysql -uusername -ppassword -Ddatabase name -hhost name

Детальніше:

  • mysql - назва програми, що викликається.
  • -uusername - ім'я користувача. Ім'я користувача підставляється замість {username} (В open server ім'я стандартного користувача - root)
  • -ppassword - пароль користувача. В open server користувач root не має пароля, і вказувати цей параметр не потрібно.
  • -Ddatabase name - назва бази даних. Вказується, коли треба підключитись до конкретної бази даних. Якщо такої потреби немає, параметр не вказується.
  • -hhostname - ім'я або адресу хоста для бази даних. У випадку з open server це localhost, який можна не вказувати.

Рядок для стандартного підключення в open server:

mysql -uroot

Список параметрів консольної програми mysql

Вибір бази даних, перегляд таблиць

Отже, ми в консолі mysql. Переглянемо перелік наявних баз даних. Команда show databases; показує всі наявні та доступні вашому користувачеві бази даних. Зверніть увагу, що команди mysql, як і команди багатьох інших мов, необхідно закінчувати крапкою з комою.

У разі першого запуску mysql, користувач побачить лише три бази даних:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Далі ми на прикладі вбудованої бази даних розберемося, як вибирати конкретну базу даних та знайомитись з її таблицями:

mysql> use mysql;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

Командой use mysql; користувач каже СУБД про те, що хоче працювати з базою даних mysql, однойменної із самої СУБД. Далі, аналогічно команді show databases; для демонстрації доступних баз даних, застосуємо команду show tables; для демонстрації списку доступних таблиць:

mysql> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| ..........................|
| user                      |
+---------------------------+
28 rows in set (0.01 sec)

Усього в цій базі доступно 28 таблиць, як видно з останнього рядка, але нас не цікавлять ці таблиці, тому для стислості відображення залишимо в уроці лише п'ять.

Досить ризиковано щось міняти в трьох стандартних базах даних, це може призвести до переустановки mysql. Щоб спробувати щось зробити самим, створимо свою базу даних. Але перед цим слід розібратися з користувачами.

Користувачі та привілеї

Перевіримо, під яким користувачем ми підключені до СУБД:

mysql> select user(), current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

Отже, ми підключені як користувач root на хостингу localhost. Вбудована в MySQL функція user() повертає ім'я поточного користувача, current_user() повертає поточне ім'я користувача, під яким користувач автентифікувався у поточній сесії. Іноді вони можуть відрізнятися, що сигналізує про проблему автентифікації.

Користувач root є в системах unix та linux суперкористувачем, який має права на все. Вважається небезпечним постійно працювати під суперкористувачем, тому ми створимо собі іншого користувача, який матиме майже ті ж права.

Для початку, перевіримо, які користувачі та під якими хостами у нас вже є. Для цього перейдемо в базу даних mysql, якщо ми не в ній:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Потім виконаємо найпростіший запит до бази даних:

mysql> select user, host from user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
3 rows in set (0.01 sec)

Отже, у нас в базі два користувачі (кількість користувачів може відрізнятися). Створимо свого користувача спеціальною командою mysql:

mysql> CREATE USER 'stud'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)

Отже, у нас створився новий користувач stud, і пароль у нього password.

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

mysql> show grants for stud@localhost;
+------------------------------------------+
| Grants for stud@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'stud'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

Отже, новому користувачу відкрито лише привілей USAGE, що, як побачимо трохи пізніше, означає "Нічого не можна". Нас це не влаштовує, адже наш поточний користувач root має більше привілеїв:

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

Ось ALL PRIVILEGES - це означає, що можна все. WITH GRANT OPTION означає, що користувач може змінювати привілеї інших користувачів.

Якщо наш користувач може роздавати привілеї, скористаємося цим:

mysql> GRANT ALL PRIVILEGES ON * . * TO 'stud'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Команда видає привілеї зазначеному користувачеві на вказані таблиці (*.* означає, всі таблиці всіх баз даних). Перевіримо результат:

mysql> show grants for stud@localhost;
+---------------------------------------------------+
| Grants for stud@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'stud'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

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

Список усіх привілеїв, які можна видати користувачеві.

Вийдемо з-під root користувача, зайдемо під студентом і приступимо до створення нової бази даних:

mysql> \q
Bye

Вийшли з консолі mysql.

$ mysql -ustud -hlocalhost -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.11 Homebrew

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Ми увійшли, використовуючи команду mysql-ustud-hlocalhost-p. Якщо просто написати -p не вводячи сам пароль, система попросить ввести пароль нижче і не відобразить його на екрані, що зручно та безпечно.

Перевіримо, чи під тим користувачем ми зайшли і що в нього за привілеї:

mysql> show grants;
+---------------------------------------------------+
| Grants for stud@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'stud'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

Трохи про створення користувачів та привілеї.

Добре, ми готові до створення БД та таблиць.

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

Створення своєї бази даних – справа нескладна:

create database test;
Query OK, 1 row affected (0.00 sec)

Базу створено. Переключимося до неї і додамо до неї першу табличку, тут буде трохи складніше:

mysql> use test;
Database changed  

mysql> create table cars(
     id int(5) not null primary key auto_increment,
     brand varchar(50) not null default ''
     );
Query OK, 0 rows affected (0.01 sec)

Розберемо цю команду докладніше:

  1. У першому рядку виконується команда create table, за якою слідує назва таблиці та дужка, що відкриває, для перерахування полів таблиці.
  2. У другому та третьому рядках створюються поля таблиці. Такі команди мають назву: create_definition. Якщо про поле таблиці (а найчастіше так і є), вказується ім'я поля (id), потім тип поля (int), потім у дужках максимальна кількість символів або розрядів, які будуть зайняті кожним записом у цьому поле.
  3. Після вказівки розміру поля слідують модифікатори.
  4. Модифікатор not null говорить СУБД у тому, що це поле слід записувати значення NULL. Якщо not null не вказано, і при створенні запису про поле нічого не буде відомо, СУБД за промовчанням запише NULL.
  5. Модифікатор auto_increment дозволяє автоматично заповнювати вказане поле, кожного разу додаючи одиницю до останнього значення, що існує в таблиці в цьому полі. У таблиці може бути лише одне поле з таким модифікатором.
  6. Модифікатор primary key означає, що це поле буде використано як первинний ключ, тобто. з цього поля можна буде однозначно зрозуміти, про який запис йдеться.
  7. У третьому рядку створюється поле brand, типу varchar, до 50 символів, за замовчуванням не нуль.
  8. Модифікатор default дозволяє задати для поля значення за промовчанням. В даному випадку це порожній рядок '.
  9. Потім просто закривається дужка, відкрита після create table, і йде стандартна для команд mysql точка з комою.

Відмінна довідкова стаття, яка розкриває та уточнює тему створення таблиць, модифікаторів, типів полів тощо. за посиланням.

Подивимося на результуючу таблицю:

mysql> desc cars;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(50) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Команда desc (скорочення від describe) з ім'ям таблиці виводить короткий опис таблиці.

Також можна подивитися повну команду створення існуючої таблиці, такою, якою її бачить mysql

mysql> show create table cars;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cars  | CREATE TABLE `cars` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `brand` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Результат у консолі трохи не такий гарний, оскільки здійснюються перенесення рядків. Як видно з результату команди show create table, таблиці в mysql за замовчуванням створюються на движку InnoDB і з набором символів utf8, що не може нас не тішити. Можливі двигуни з їх плюсами та мінусами ми розглянемо пізніше.

Інші операції з таблицями та базами даних

Для зміни існуючої таблиці є команда alter table:

mysql> alter table cars add column year int(4) not null default 2010;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc cars;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(50) | NO   |     |         |                |
| year  | int(4)      | NO   |     | 2010    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Ми додали до таблиці машин колонку з роком випуску машини, використовуючи команду alter table. Тим самим шляхом видалимо цю колонку:

mysql> alter table cars drop column year;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc cars;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(50) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Команда alter table досить складна, але багато що дозволяє. Команда, як і при створенні таблиці, дозволяє додавати і видаляти колонки, змінювати їх різними способами, додавати на них різні індекси, вішати ключі на таблиці і видаляти їх і так далі.

Для видалення таблиць є дві цікаві команди:

drop table з іменем таблиці просто видаляє таблицю. trunc table з ім'ям таблиці видаляє лише вміст таблиці, не змінюючи її структури, але перестворюючи первинний ключ, отже він знову додаватиме "з одиночки".

База даних видаляється командою drop database з ім'ям таблиці та крапкою коми наприкінці.

Корисні посилання

Ознайомча стаття

Домашнє завдання

Наступний Урок, Маніпуляції з даними та найпростіші запити