Create schema

Создание базы данных и таблиц MariaDB

Предварительные требования

• Виртуальная машина с двумя сетевыми интерфейсами
• Установленные пакеты: bash-completion, mariadb-server, mariadb

Создание тестовой базы данных MariaDB

Вначале поглядим, какие базы данных у нас уже имеются и создадим свою:

[root@vm-01 ~]# mysql -u root -ptest1234
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database andy_test_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| andy_test_db       |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

Теперь, дабы не придумывать таблицы и не вводить какие-то свои данные, возьмем имеющуюся таблицу и скопируем ее структуру:

MariaDB [(none)]> show create table information_schema.engines\G
*************************** 1. row ***************************
       Table: ENGINES
Create Table: CREATE TEMPORARY TABLE `ENGINES` (
  `ENGINE` varchar(64) NOT NULL DEFAULT '',
  `SUPPORT` varchar(8) NOT NULL DEFAULT '',
  `COMMENT` varchar(160) NOT NULL DEFAULT '',
  `TRANSACTIONS` varchar(3) DEFAULT NULL,
  `XA` varchar(3) DEFAULT NULL,
  `SAVEPOINTS` varchar(3) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MariaDB [(none)]> select * from information_schema.engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT                                                                    | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

MariaDB [(none)]> create table andy_test_db.test_table like information_schema.engines;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]>

Проверим, какие типы данных есть у нашей таблицы andy_test_db.test_table:

MariaDB [(none)]> describe andy_test_db.test_table;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ENGINE       | varchar(64)  | NO   |     |         |       |
| SUPPORT      | varchar(8)   | NO   |     |         |       |
| COMMENT      | varchar(160) | NO   |     |         |       |
| TRANSACTIONS | varchar(3)   | YES  |     | NULL    |       |
| XA           | varchar(3)   | YES  |     | NULL    |       |
| SAVEPOINTS   | varchar(3)   | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MariaDB [(none)]> show create table andy_test_db.test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `ENGINE` varchar(64) NOT NULL DEFAULT '',
  `SUPPORT` varchar(8) NOT NULL DEFAULT '',
  `COMMENT` varchar(160) NOT NULL DEFAULT '',
  `TRANSACTIONS` varchar(3) DEFAULT NULL,
  `XA` varchar(3) DEFAULT NULL,
  `SAVEPOINTS` varchar(3) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MariaDB [(none)]>

Ссылки

Create table