. Advertisement .
. Advertisement .
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.
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
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.
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/.
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.
- Go to file and direct to Run SQL script
- Choose which .sql file you want to execute
- Click Run
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.
Leave a comment