The SQL query tab is a secondary tab that opens by default when you make a connection to a MySQL server. Each query tab is uniquely identified by an incrementing number: query 1, query 2, and so on. To close an open tab, click the x on the tab. For a closer look at query editing in MySQL Workbench, (see Section 8.1.1, “SQL. The SQL query secondary tab opens by default when you make a connection to a server from the Home screen. It includes a query editor area and a toolbar. You can enter SQL statements directly into the query editor area. The statements entered can be saved to a file or snippet for later use.
Creating and learning how to execute a SQL script file is very important, as well as simple. Here we will learn how to run SQL scripts from the command line, special options and possible problems that we will face in the process.
SQL Script File
Script files provide a simple, compact way of working with few – or lots – of SQL scripts. These files are very useful to handle the incremental scripts for our application, import dump files from existing databases or to quickly test scripts in the command line.
Most of the SQL Script files use the extension ‘.SQL’. But they can also be simple text files, as long as the content is valid SQL.
Running SQL Script File
Running SQL scripts from the command line is very straightforward. MySQL allows us to load our files interactively after connecting to the server or directly from the command line.
If we’re already using MySQL we can use the command SOURCE. or .(backslash dot), followed by the path to the file.
Mysql Workbench Select Database
Note: When we are into the MySQL command line, it will start with “mysql >”.
Run SQL script file with SOURCE command
Even being synonyms, if we use the command . with a semicolon at the end, it will output error. This happens because the command will concatenate the semicolon to the file name and won’t be able to find it.
Wrong file path error using . command
If we face this error, just removing the semicolon will make it work:
Run SQL script file with .command
At this point, we need to select a database to load the SQL script file. We can execute a USE statement before the SOURCE command; if we don’t, our file should begin with a USE clause or it will give us an error because we need to select a database first.
Running script without selecting database
Super duper 3 3 13. If we use the command CREATE DATABASE inside the SQL script file, we must add the command USE:
With this, the rest of the SQL queries will use our recently created `biology` database.
But we can also import our file from the command line, without previously being connected to MySQL. For this, we will use the “<” option, like this:
Run Sql Query In Mysql Workbench Commands
Note: If we require a user and password, we should pass the right parameters with them.
Run SQL script file from command line successfully
When the command runs without errors, it won’t show any output, only the new line to continue using the command line. But if the script execution failed, the error will be printed:
Error running SQL script file from command line
Custom messages are useful when we are running a SQL script from the command line to give information about the progress of the execution. To show a message as command line output as we run the SQL script file, we can add SELECT statements like this in our SQL script:
Running SQL script file with custom messages
If we are running a SQL script from the command line, MySQL allows us to provide few options to change the default behavior of the execution:
–force: Using this option will force MySQL to continue running the script even if one or more errors occur. For this example, we will add a line saying “This is an invalid sentence;” and then try to the SQL file.
Error running SQL script file without –force
Once our SQL script file reaches the error, MySQL prints it and stops the execution. Now, let’s use the option –force.
Skipping error with –force option
-e: Using this option will run the SQL script file interactively, and then will exit MySQL. We will need to use the syntax of the command SOURCE, enclosed by quotes:
Run Sql Query In Mysql Workbench Syntax
Using -e to execute SOURCE from command line
–verbose: Using this option will output in the command line each of the executed queries in our SQL script file. Note: First, we will remove the custom messages we added to our script to get a better demonstration of this option.
Using –verbose to print each SQL Statement executed