Create a new database, table and insert data, using MariaDB in the Linux Terminal (Part 3)
ID: 122
Category: MariaDB Database
Added: 25th of September 2022
Updated On: Tutorial updated and rechecked on 25th of September 2022
Views: 3,709
This part of tutorial assumes you have already installed MariaDB and created a new user, if not please use the menu above to visit these tutorials.
In this tutorial we are going to create a new database, table and insert some data.
The new table will hold information about English football clubs.
To create a new database in MariaDB we use the CREATE DATABASE command.
CREATE DATABASE football_teams;
To view the database, we use the SHOW DATABASES command
SHOW DATABASES;
MySQL Output
Before we can create the table, we need to select the database to use. To do this we enter the following command
USE football_teams;
Next we need to create a new table with the following fields.
id
team
nickname
stadium_name
capacity
Copy and paste the MySQL query to create the table teams.
CREATE TABLE `teams` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`team` varchar(50) NOT NULL,
`nickname` varchar(50) NOT NULL,
`stadium_name` varchar(50) NOT NULL,
`capacity` int(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`id` int(11) NOT NULL AUTO_INCREMENT,
`team` varchar(50) NOT NULL,
`nickname` varchar(50) NOT NULL,
`stadium_name` varchar(50) NOT NULL,
`capacity` int(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Enter the DESCRIBE command to check the table structure
DESCRIBE teams;
MySQL Output
With the table set up, we can now insert some basic data.
INSERT INTO `teams`
(`team`, `nickname`,`stadium_name`, `capacity`)
VALUES
('Arsenal', 'The Gunners','The Emirates Stadium','60161'),
('Brighton & Hove Albion', 'The Seagulls','The Amex Stadium','30666'),
('West Ham', 'The Hammers','London Stadium','60000'),
('Bournemouth', 'The Cherries','Vitality Stadium','11329'),
('Leicester', 'The Foxes','KIng Power','32312');
(`team`, `nickname`,`stadium_name`, `capacity`)
VALUES
('Arsenal', 'The Gunners','The Emirates Stadium','60161'),
('Brighton & Hove Albion', 'The Seagulls','The Amex Stadium','30666'),
('West Ham', 'The Hammers','London Stadium','60000'),
('Bournemouth', 'The Cherries','Vitality Stadium','11329'),
('Leicester', 'The Foxes','KIng Power','32312');
To view all the records, we can use the following query.
SELECT * FROM teams;
MySQL Output
In the next tutorial we will run more queries against the database.
