Many times we need to run a SQL script and we do not have graphical tools such as: phpMyAdmin or Adminer so we need to use the command line and use the mysql shell and follow one of the following methods:
1. Execute SQL script in direct way
$ mysql -u user -p data-base < script.sql2. Authenticate and then run SQL script
$ mysql -u usr -p data-baseThen
source script.sql;Or
. script.sql;If script.sql file is not found in the current directory, then you must specify the path where it is located
Further readings
- man mysql
Spanish video associated
Administering MySQL from the command line, 10 (15)
« Previous PostNext Post »To run SQL script in MySQL, use the MySQL workbench. First, you need to open MySQL workbench.
The snapshot is as follows −
Now, File -> Open SQL Script to open the SQL script.
Alternatively, use the following shortcut key −
Ctrl+Shift+OAfter that an option would be visible for you to choose your .sql file from the disk. In my system, the file is located on the Desktop. I will select the same “tblstudent” SQL file as shown in the following screenshot −
After browsing .sql files, you need to select the option “Reconnect to database” as shown in the following screenshot −
Now, it will ask for password to connect with MySQL. Add the password and click “OK” as in the following screenshot −
Note − Press OK button twice to connect with MySQL.
After completing the above process, the following screen will be visible with our SQL file “tblstudent”, which we uploaded before −
After that you need to execute the script. To execute the script, click the symbol marked in the following screenshot −
After that you will get the following output −
Updated on 30-Jul-2019 22:30:24
- Related Questions & Answers
- How to run .SQL script using JDBC?
- Run SQL file in MySQL database from terminal?
- How to run a PowerShell script from the command prompt?
- How to echo print statements while executing an SQL script?
- Performing Null check using HANA SQL Script
- SQL Script vs Graphical Calcualtion views in SAP HANA
- Run Python script from Node.js using child process spawn() method?
- MySQL Extensions to Standard SQL
- How to make SQL case sensitive string comparison in MySQL?
- How can we run MySQL statements in batch mode?
- mysqld_safe - MySQL Server Startup Script
- mysql.server - MySQL Server Startup Script
- How do I pass a variable to a MySQL script?
- How to run matplotlib in Tkinter?
- How to write a MySQL “LIMIT” in SQL Server?
To run SQL file in database, you need to use below syntax:
mysql -u yourUserName -p yourDatabaseName < yourFileName.sqlTo understand the above syntax, let us open command prompt using windows+R shortcut key.
The snapshot is as follows:
After pressing OK button, you will get a command prompt. The snapshot is as follows:
Now reach the bin directory, the snapshot is as follows:
Here is my file ‘mydb.sql’ which is located in bin directory. The snapshot and content of SQL file is as follows:
Now you can type the above syntax which I have discussed to run SQL file. The snapshot of command is as follows:
Now you can check the table ‘Instructor’ is created in database test or not.
Now switch to database test using USE command. The query is as follows:
mysql>Use testNow check the table ‘Instructor’ is present in test database. The query is as follows:
Yes, we have Instructor table. Now we need to check the record is present or not which is inserted in the table. The query is as follows:
mysql> select *from instructor;The following is the output:
+----+-------+ | id | Name | +----+-------+ | 1 | John | | 2 | Larry | | 3 | Sam | +----+-------+ 3 rows in set (0.00 sec)
Updated on 30-Jul-2019 22:30:25
- Related Questions & Answers
- How to run SQL script in MySQL?
- Executing SQL Statements from a Text File on MySQL Client
- MySQL Differences from Standard SQL
- Convert timestamp coming from SQL database to String PHP?
- Database Wars: MSSQL Server, Oracle PL/SQL and MySQL
- Writing data from database to .csv file
- How to run .SQL script using JDBC?
- Execute MySQL query from the terminal without printing results?
- Create User SQL in SAP HANA database
- Create View SQL in SAP HANA database
- How can we retrieve file from database using JDBC?
- Selecting random entry from MySQL Database?
- Create a database in MySQL from Java?
- Write a program in Python to read sample data from an SQL Database
- What is NoSQL (Not Only SQL database)