Create schema
Материал из pNp Wiki
Содержание
Создание базы данных и таблиц 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)]>
Можно создать таблицу вручную, если есть необходимость:
MariaDB [(none)]> create table andy_test_db.people (id int, FirstName varchar(255), SecondName varchar(255), City varchar(255));
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]>
Посмотрим на созданную таблицу в нашей базе данных andy_test_db:
MariaDB [andy_test_db]> describe andy_test_db.people;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| FirstName | varchar(255) | YES | | NULL | |
| SecondName | varchar(255) | YES | | NULL | |
| City | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MariaDB [andy_test_db]>show create table andy_test_db.people\G
*************************** 1. row ***************************
Table: people
Create Table: CREATE TABLE `people` (
`id` int(11) DEFAULT NULL,
`FirstName` varchar(255) DEFAULT NULL,
`SecondName` varchar(255) DEFAULT NULL,
`City` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MariaDB [andy_test_db]>
