It is useful to execute an SQL script and let it work instead of typing all the code yourself. This tutorial will introduce three methods and instructions on how to run an SQL file.

Category: MySQL

How To Create A MySQL Script File

A MySQL script file is a series of statements in a .sql file. When you execute the whole file, all the statements will be activated in a specific order. 

For example, a school database will be followed by a table named students. It will insert data into this stable. 

CREATE DATABASE school;

USE school;

CREATE TABLE students (

    ID MEDIUMINT NOT NULL AUTO_INCREMENT,

    FirstName varchar(255),

    LastName varchar(255),

    PRIMARY KEY (id)

);

INSERT INTO students (FirstName, LastName) VALUES ('Mike', 'Williams');

How To Run An SQL File

Method 1: Run From The Terminal

You can read all the statements in a script with the (<) operator. Continue with the previous example of the school database, now login to the MySQL host server and activate the .sql file:

mysql -u root -p < school.sql

Here, a MySQL root password appears. 

Add the name before the (<) operator, and the script can be executed against your chosen database:

mysql -u root -p database-name < yourscript.sql

Don’t forget to log into your server to verify all the changes:

mysql -u root -p

Output:

mysql> show databases;

Database

information_schema

mysql

performance_schema

school

sys

5 rows in set (0.00 sec)

mysql>

Type SELECT * FROM school.students; to see what the table has.

Output:

mysql> select * from school.students;

ID	FirstName	LastName
1	Mike	Williams

1 row in set (0.00 sec)

mysql>

Method 2: Run From The MySQL Prompt

The source command also allows you to execute the .sql file. This method is suitable for the script located under /root/.

source /root/school.sql

Output:

mysql> source /root/school.sql

Query OK, 1 row affected (0.00 sec)

Database changed

Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

mysql>

You can use this method to run a script against a database. Yet, it is vital to change the database and run the source:

use school;

source /root/school.sql

Logging in from the linux shell is a great option to make your command part of a Shell/Bash script:

mysql --host="mysql_server" --user="user_name" --database="database_name" --

password="user_password" < 

"path/to/sql/file.sql"

Method 3: Use A MySQL Visual Client

MySQL Workbench enables editors to import or write scripts for executing purposes. Download the workbench and connect it to your database server.

Here:

• Go to file and direct to Run SQL script
• Choose which .sql file you want to execute
• Click Run

Conclusion

It is handy to use .sql scripts to execute long SQL statements. This tutorial has offered you three methods and detailed steps on how to run an SQL file.