How to create a MySQL script file (.sql)
The MySQL script file is just a series of MySQL statements in a .sql file. The statements will get executed in order. For example, the following script will create a database called school, a table called students, and it will insert some data into the students table. It simply contains all of the statements that I’d otherwise execute normally in a MySQL prompt.
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 a .sql script file
There are three (and probably more) methods of running a .sql script.
Method 1: Run it from the terminal
We can read MySQL statements from a script with (
<) operator. Login to MySQL host server and run the
school.sql like this
mysql -u root -p < school.sql
It will prompt you for the MySQL root password.
If you want to run your script against a specific database, add the database name before (
mysql -u root -p database-name < yourscript.sql
Let’s verify our changes by logging in to our MySQL server:
mysql -u root -p
Check if the database is created:
As we can see the school database has been created.
Let’s see what’s inside the table we created
SELECT * FROM school.students;
Method 2: Run it from the MySQL prompt
You can also run a .sql file from a MySQL prompt using the MySQL
source command. Note that the script is located under /root/.
NOTE: If you want to run a script against a specific database, you change the database first and run source:
use school; source /root/school.sql
Method 3: Using a MySQL visual client like Workbench
Applications like MySQL Workbench offer editers to write or import scripts for execution.
Download MySQL workbench and connect to your MySQL database server.
Go to File > Run SQL script.. choose the .sql file to execute, and click Run.
Using .sql scripts comes handy when executing long SQL statements. In this tutorial, we learned how to create and execute .sql files using different methods.