In this tutorial we will see how to import .sql (tables or databases) files in MySQL-CLI.

MariaDB and MySQL have some differences and similarities although both are commonly known as MySQL. We will use MariaDB as our database management system throughout this tutorial.

You can see below how the MySQL and MariaDB prompts look like :

  • MySQL prompt:
mysql>
  • MariaDB prompt:
MariaDB [(none)]>

What is a .sql file ?

The *.sql extension is a file which contains some sql statements like — Create - Select - Insert - View etc. in a proper order to perform the creation of databases and tables. It is also known as “dump” because it is basically a mysql export.

Creating Database

First of all, we have to create a database in which we can import sql tables.

Let’s create a database named “test” using the CREATE DATABASE statement :

MariaDB [(none)]> CREATE DATABASE test;

Select the “test” database as the default database using USE statement :

MariaDB [(none)]> USE test;

Importing *.sql Sample Tables

We have two sample tables here. We will import them in our “test” database :

  • event table
  • pet table

Event table

Download and extract the following zip file in your working directory : Download event-table.zip

You can see that there are two files in event-table folder :

event-table
├── cr_event_tbl.sql
└── event.txt

But why do we need two separate files ??

It is because the sql file here is only containing the table structure and the text file is holding the values for that table structure. But it is not necessary to use two different files, we can also hold table structure and values together in a sql file.

  1. cr_event_tbl.sql

This file contains the table structure or table format.

Import this file using the SOURCE statement as shown below :

MariaDB [test]> SOURCE cr_event_tbl.sql

It will create an empty table “event” :

MariaDB [test]> SHOW TABLES;
+---------------------+
| Tables_in_test      |
+---------------------+
| event               |
+---------------------+
1 row in set (0.010 sec)
  1. event.txt

This file contains the data (values) for event table. We need to load this data in event table.

Use the below statement to load the event.txt in event table :

MariaDB [test]> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

Now we have the complete table. You can see the complete table using the SELECT statement:

MariaDB [test]> SELECT * FROM event;
+----------+------------+----------+-----------------------------+
| name     | date       | type     | remark                      |
+----------+------------+----------+-----------------------------+
| Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male |
| Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male |
| Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         |
| Chirpy   | 1999-03-21 | vet      | needed beak straightened    |
| Slim     | 1997-08-03 | vet      | broken rib                  |
| Bowser   | 1991-10-12 | kennel   | NULL                        |
| Fang     | 1991-10-12 | kennel   | NULL                        |
| Fang     | 1998-08-28 | birthday | Gave him a new chew toy     |
| Claws    | 1998-03-17 | birthday | Gave him a new flea collar  |
| Whistler | 1998-12-09 | birthday | First birthday              |
+----------+------------+----------+-----------------------------+
10 rows in set (0.012 sec)

Pet table

Download and extract the following zip file in your working directory : Download pet-table.zip

Follow the same procedure as we done above.

Folder structure of pet-table :

pet-table
├── cr_pet_tbl.sql
└── pet.txt
  1. cr_pet_tbl.sql

Importing cr_pet_tbl.sql in “test” database :

MariaDB [test]> SOURCE cr_pet_tbl.sql

This will create a new empty table as name “pet” :

MariaDB [test]> SHOW TABLES;
+---------------------+
| Tables_in_test      |
+---------------------+
| event               |
| pet                 |
+---------------------+
2 rows in set (0.010 sec)
  1. pet.txt

Load data form pet.txt in pet table :

MariaDB [test]> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

Print the entire table using SELECT statement :

MariaDB [test]> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.011 sec)

Importing Sample .sql database

Here, we have a .sql database dump, which means you don’t need to create an empty table first and then import data into it. It will automatically create a database with some sample tables in it.

Download and extract the following zip file in your working directory : Download database

Now just use the SOURCE statement :

MariaDB [(none)]> SOURCE mysqlsampledatabase.sql;

It will create a database, classicmodels with some tables in it.

Select the classicmodels as default database :

MariaDB [(none)]> USE classicmodels;

There are different tables for practicing MySQL statements :

MariaDB [classicmodels]> SHOW TABLES;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.001 sec)

Hope you liked this tutorial. Comment your suggestions and queries. We always appreciate your feedback 😊