MariaDB databases en tabellen

Docent uitwerking

Geschreven door Peter van Gemert

Op 20 augustus 2023

Log in op database

  databaseserver $ mysql -uadmin_pgt -p

Maak datacenter database

  databaseserver mariadb> CREATE DATABASE datacenter;
  databaseserver mariadb> SHOW databases;
  databaseserver mariadb> USE datacenter;

Maak oses table

  databaseserver mariadb> CREATE TABLE datacenter.oses (
      ->   osid INTEGER UNSIGNED auto_increment NOT NULL,
      ->   osnaam varchar(100) NOT NULL,
      ->   CONSTRAINT oses_PK PRIMARY KEY (osid)
      -> )
      -> ENGINE=InnoDB
      -> DEFAULT CHARSET=utf8mb4
      -> COLLATE=utf8mb4_general_ci;
  
  databaseserver mariadb> SHOW tables;
  databaseserver mariadb> DESCRIBE oses;
  databaseserver mariadb> SHOW CREATE TABLE oses;

Maak server table

  databaseserver mariadb> CREATE TABLE datacenter.servers (
      ->   serverid INT UNSIGNED auto_increment NOT NULL,
      ->   osid INTEGER UNSIGNED NOT NULL,
      ->   servernaam varchar(100) NOT NULL,
      ->   ipbeheer varchar(15) NULL,
      ->   ipdatabase varchar(15) NULL,
      ->   ipnfs varchar(15) NULL,
      ->   ipiscsi varchar(15) NULL,
      ->   ipweb varchar(15) NULL,
      ->   ipnat varchar(15) NULL,
      ->   CONSTRAINT servers_PK PRIMARY KEY (serverid),
      ->   CONSTRAINT servers_FK FOREIGN KEY (osid) REFERENCES datacenter.oses(osid)
      -> )
      -> ENGINE=InnoDB
      -> DEFAULT CHARSET=utf8mb4
      -> COLLATE=utf8mb4_general_ci;
  
  databaseserver mariadb> SHOW TABLES;
  databaseserver mariadb> DESCRIBE servers;