How to Execute an SQL File in MySQL

Summary: in this tutorial, you will learn various ways to execute an SQL file in MySQL using the mysql command line interface, the source command, and the MySQL Workbench.

Running an SQL file in MySQL is an essential skill for database administrators and developers. MySQL offers serval ways to execute an SQL file.

Executing a SQL file using MySQL command-line interface

First, open the Command Prompt on Windows or the Terminal on macOS or Linux.

Second, run the following command:

mysql -u username -p database_name < path/to/your/sqlfile.sqlCode language: SQL (Structured Query Language) (sql)

In this command, you replace the username with your MySQL username, database_name with the target database and path/to/your/sqlfile.sql with the actual path to your SQL file.

The command will prompt you to enter a password for the username. After providing the password, you press the Enter key to execute.

We’ll create a new database called sales for the demonstration.

First, connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, create a new database called sales:

CREATE DATABASE sales;Code language: SQL (Structured Query Language) (sql)

Third, exit the mysql:

exitCode language: SQL (Structured Query Language) (sql)

Create a new file called script.sql with the following code and save it to the c:\temp directory:

CREATE TABLE products(
   id INT AUTO_INCREMENT PRIMARY KEY,
   product_name VARCHAR(255) NOT NULL,
   price DECIMAL(10,2) NOT NULL
);

INSERT INTO products (product_name, price) 
VALUES
('Smartphone XYZ', 599.99),
('Laptop ABC', 1299.99),
('Wireless Earbuds', 79.99),
('4K Ultra HD Smart TV', 899.99),
('Gaming Console XYZ', 399.99);Code language: SQL (Structured Query Language) (sql)

To execute the script.sql file, you use the following command:

mysql -u root -p sales < c:/temp/script.sqlCode language: SQL (Structured Query Language) (sql)

To verify the execution you can follow these steps:

First, connect to the sales database:

mysql -u root -p -D salesCode language: SQL (Structured Query Language) (sql)

Second, show all the tables in the sales database:

+-----------------+
| Tables_in_sales |
+-----------------+
| products        |
+-----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the products table:

select * from products;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------------------+---------+
| id | product_name         | price   |
+----+----------------------+---------+
|  1 | Smartphone XYZ       |  599.99 |
|  2 | Laptop ABC           | 1299.99 |
|  3 | Wireless Earbuds     |   79.99 |
|  4 | 4K Ultra HD Smart TV |  899.99 |
|  5 | Gaming Console XYZ   |  399.99 |
+----+----------------------+---------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Executing an SQL file using the source command

To execute an SQL file using the source command, you follow these steps:

First, connect to the MySQL server using the mysql client program:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, switch to a target database e.g., sales where you want to execute the SQL statements:

use sales;Code language: SQL (Structured Query Language) (sql)

Third, execute the source command:

source path/to/your/sqlfile.sqlCode language: SQL (Structured Query Language) (sql)

For example, you can create a new file called script2.sq in the C:\temp directory with the following code:

-- Create the customers table
CREATE TABLE customers (
   id INT AUTO_INCREMENT PRIMARY KEY,
   customer_name VARCHAR(255) NOT NULL,
   email VARCHAR(255) NOT NULL
);

-- Insert five customers into the customers table
INSERT INTO customers (customer_name, email) VALUES
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]'),
('Alice Johnson', '[email protected]'),
('Bob Williams', '[email protected]'),
('Eva Davis', '[email protected]');Code language: SQL (Structured Query Language) (sql)

The following source command executes the script2.sql file:

source c:/temp/script2.sqlCode language: SQL (Structured Query Language) (sql)

The command returns the following output:

Query OK, 0 rows affected (0.02 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0Code language: SQL (Structured Query Language) (sql)

The following show tables statement displays all tables in the sales database to verify the execution:

show tables;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+
| Tables_in_sales |
+-----------------+
| customers       |
| products        |
+-----------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Executing an SQL file using MySQL workbench

To execute an SQL file using MySQL Workbench, you follow these steps:

1. Open MySQL Workbench and connect to your MySQL server.
2. Navigate to the “File” menu, select “Open SQL Script,” and choose your SQL file.
3. Click the lightning bolt icon or use the shortcut (Ctrl + Enter) to execute the script.

Summary

• Utilize the mysql command-line interface mysql -u username -p database_name < path/to/your/sqlfile.sqlto execute an SQL file.
• Leverage the source command to execute an SQL file.
• Use the MySQL Workbench to execute an SQL file.