Mysql Workbench Select Database

• Mysql Workbench Select Database Commands
• Mysql Workbench Select Database Builder
• Mysql Workbench Select Database
• How To Use Mysql Workbench
• Mysql Workbench Create Table Script

Do you need to save your results from a MySQL query to a CSV or text file?

SELECT QUERY is used to fetch the data from the MySQL database. Databases store data for later retrieval. The purpose of MySQL Select is to return from the database tables, one or more rows that match a given criteria. Select query can be used in scripting language like PHP, Ruby, or you can execute it via the command prompt. Do you need to save your results from a MySQL query to a CSV or text file? It's easy to do in MySQL. You can do this using an IDE or the command line, using a built-in MySQL command. Let's take a look. Let's use a simple example of a SELECT statement for this. SELECT id, firstname, lastname FROM customer; Here are the results. Open MySQL Workbench and connect to your database server. From the database home screen ( Figure A ), right-click a blank spot under the SCHEMAS pane and select Create Schema. MySQL Workbench is one of the most popular MySQL database management and monitoring tools for visual database schema design, SQL query building, administration and maintenance. However, if it doesn't meet your requirements, then here are the top 5 MySQL Workbench alternatives that you can try.

It's easy to do in MySQL. You can do this using an IDE or the command line, using a built-in MySQL command.

Let's take a look.

Basic Query

Let's use a simple example of a SELECT statement for this.

Here are the results:

We may see these results in the output of our command line or in the IDE, such as MySQL Workbench.

How can we save them into a text file?

We could copy and paste them, but that's slow and manual.

Save MySQL Results to a File

There's a built-in MySQL output to file feature as part of the SELECT statement.

We simply add the words INTO OUTFILE, followed by a filename, to the end of the SELECT statement.

For example:

This will create a new file called myoutput.txt that contains the results of this query, in a folder called temp.

What happens if you get this error (like I did)?

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

I'll explain more about resolving this later in this guide.

For now, assuming you can run the statement to generate the file, it will look like this:

The text in the file is:

As you can see, the fields are separated by tabs. This is the default behaviour, but it can be changed.

Changing Parameters to Set Comma Separated Values

You can change the parameters of this INTO OUTFILE keyword to change how the file is written.

There are several extra parameters. These are some of the most common:

• FIELDS TERMINATED BY: this indicates the character(s) that are used to end a field.
• ENCLOSED BY: this indicates the character(s) that will be used to surround each field.
• LINES TERMINATED BY: this indicates the character(s) that are used to end a line and start a new line.

For example, to select the data to a CSV file and enclose each field in a double quote:

If we run this statement, we can check the file, which will look like this:

The text in the file is:

So that's how you can generate a CSV or text file in MySQL. You just add the INTO OUTFILE keyword to the end of a SELECT query and specify some parameters.

Include Headings in Output File

You might have noticed that there are no column headings in the output file.

How can you get column headings to display? Unfortunately, there's no easy option you can enable.

One commonly-mentioned way is to use a UNION ALL to select the column headings and the data.

So, your query would look like this:

This would mean your column headings would be shown in the file:

However, there are a few issues with this:

Data Types

This only works if the data types are characters. If you have any other types (such as numbers or dates) in your data, then you'll get issues with your query.

This is because the data type of the column is determined by the first query in the UNION. All of the column headers are text values, so when the second part of the UNION query is run, it may try to add a date into a character column and cause an error.

Can Have Issues with Ordering

If your query includes an ORDER BY clause, then your column headings won't show correctly.

This is because the ORDER BY clause goes at the end of the query, and will include your row of column headers in the ordering. This could mean that your column headers will end up at a place in the results that is not the top.

You could get around this by putting your main query in a subquery and then using UNION on that.

For example:

This should ensure that your column headers are shown at the top.

Possible Performance Issues

If you try to use a UNION or UNION ALL, the MySQL database may try to use a different execution plan to display the data, even though you're only adding a single row.

This could mean the runtime is a lot slower.

So, it's something you should test before you start using this as a permanent solution.

What if the File Already Exists?

So, what happens if you try this command and the file already exists?

For example, assuming the myoutput.txt file exists, you run this command:

You'll get this message:

Error Code: 1086. File ‘/temp/myoutput.txt' already exists

This is pretty clear. The file already exists. So you need to use a filename that does not exist.

Error With Secure-File-Priv

Are you running this SELECT INTO OUTFILE and getting an error about 'secure-file-priv'?

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

This essentially means that MySQL has a specific set of directories that can be used for input and output, and the file we're attempting to write is not in that directory.

To resolve this, we need to:

1. Find out what the specific directories are
2. Add this directory path to our OUTFILE parameter so our file is generated there.

To find out where the directories are, we can run either of these two commands:

The output in this example shows the value to be ‘/usr/files/'.

Mysql Workbench Select Database Commands

So, all you need to do is add this path to the start of the OUTFILE path.

Your SELECT statement would then look like this:

Secure-File-Priv is NULL

If you have the error above, and you look for the secure_file_priv variable, you may find it is NULL. This happens most often on MacOS with MAMP.

To resolve this, we need to add this variable to the my.cnf file.

First, stop your MAMP server by opening MAMP and clicking Stop.

Open a Terminal window.

Enter in this command:

Your screen should look like this.

Press Enter to run the command. This opens up an editor for the file my.cnf.

Copy the following lines into the Terminal window:

(Replace the letters BB with whatever your user folder is)

Press the : key and the cursor will be moved to the bottom.

Enter wq after the : which will write to the file and quit.

Press Enter, and you will return to the normal Terminal window.

Mysql Workbench Select Database Builder

Now, start MAMP again.

You can test the variable was set by running one of the commands from earlier:

Now, update your SELECT INTO statement to add the value here to the path of the OUTFILE:

The file will now be generated.

Conclusion

So that's how you can generate a text or CSV file from the results of a MySQL query. You add an INTO OUTFILE parameter to the end of your SELECT query.

If there are any issues with error messages, they are usually permission related.

MySQL Workbench is a graphical visual database design tool with multiple functionalities. This tutorial will cover database design and modeling with MySQL Workbench.

Prerequisites

This article is suitable for beginner to intermediate MySQL Workbench users. It requires no prior knowledge of MySQL Workbench.

However, knowledge in relational databases and database design may be required. To get started with the MySQL database, check the MySQL Tutorial website.

Introduction to MySQL Workbench

There are two editions of MySQL Workbench: the community edition and the commercial edition. The community edition is open source. Both editions are available for three major platforms; MS Windows, macOS, and Linux. The commercial edition comes with more functionalities at a cost.

Some of the extra functionalities available in the commercial edition are:

MySQL Workbench has five main functions:

Database design (data modeling)

This involves creating simple to complex entity-relationship (ER) models. Reverse engineering creates a database from ER models. Forward engineering creates an ER model from a live database.

Developing SQL

MySQL Workbench has a built-in SQL editor with syntax highlighting and auto-complete. It is used to interact with the MySQL Server.

Administration

Some of the MySQL Workbench database administrative functions are:

• Backup
• Recovery
• Audit
• Monitoring server performance
• Checking database health
• User management

MySQL Workbench has a visual performance dashboard. The visual performance dashboard enables database administrators to view key performance indicators. Below is a screenshot of the MySQL Workbench visual performance dashboard.

Data migration

MySQL Workbench is used to migrate databases from other relational database management systems (RDBMS). Some of the supported RDBMS are PostgreSQL, MS SQL Server, SQLite, MS Access, Sybase, and Sybase SQL Anywhere.

MySQL enterprise support

MySQL Workbench enterprise edition supports MySQL enterprise products.

In this tutorial, the focus will be on database design.

Setting up MySQL Workbench

Workbench is one of the MySQL products. Use MySQL Installer to manage MySQL products installations. Find the list of MySQL Workbench supported platforms on MySQL official website. For the MySQL Workbench hardware requirements, check the image below.

Installing MySQL Workbench on Windows

Download MySQL Installer from the official website. Install MySQL Workbench using the downloaded installer. For this installation, I recommend the default configurations, as shown in the screenshot below.

The Developer Default setup type comes with MySQL Workbench and other developer tools.

You can find more detailed installation guidelines for MySQL Workbench on MySQL Workbench Manual.

Verifying MySQL Workbench installation

Launch MySQL Workbench.

You can do this from the installation wizard.

• Linux: Launch by typing the command mysql-workbench. Alternatively, navigate to Activities > MySQL WorkBench.
• macOS: Navigate to Applications > MySQL Workbench.
• Windows: Navigate to Start > Programs > MySQL > MySQL Workbench.

Archiver 2 1 5 – open create and convert archives. Make sure there is a connection to the MySQL Server local instance, as shown in the screenshot below. If there is no connection, click the + icon to create a new connection, as highlighted in the screenshot below.

Provide the connection details.

The created connection will be displayed, as shown in the screenshot below.

Database design with MySQL Workbench

This section will create a new ER model and then translate it into a physical MySQL database. On MySQL Workbench, navigate to File > New Model as shown below.

Save the model. Double click MySQL Schema and change the name from mydb to booksdb. Click the Add Diagram icon to create a new EER diagram. Refer to the screenshot below.

After adding a new diagram, a new window will be opened, as in the screenshot below.

Scenario

In this tutorial, we will model and create a database that will be used to keep book details. The database should store books with author and publisher details. We will skip the database normalization process details.

The final database will have three main entities with attributes, as shown below:

1. Book: id, title, ISBN, publisher, total_pages, publication_year, author.
2. Author: id, first_name, last_name.
3. Publisher: id, name.

We will use the information in the tables below to design the ER model. All the column names have been defined. Primary keys, foreign keys, and datatypes are also outlined.

Book table

Author table

Publisher table

The relationships between the entities are as below:

Mysql Workbench Select Database

• The relationship between the book and the author is many-to-many. An author can have multiple publications. A book can also have multiple authors. This type of relationship requires an extra table called a bridge table. MySQL Workbench automatically creates a bridge table when we add a many-to-many relationship.
• The relationship between the book and the publisher is one-to-many. A book can only have one publisher. A publisher can publish multiple books.

Using MySQL Workbench, we will design an enhanced entity-relationship (EER) diagram. MySQL Workbench allows us to create tables, edit the attributes, and create relationships between the tables.

The visual design interface

The diagram below shows a MySQL Workbench design window screenshot. The window has different panels highlighted in different colors.

Prerequisites

This article is suitable for beginner to intermediate MySQL Workbench users. It requires no prior knowledge of MySQL Workbench.

However, knowledge in relational databases and database design may be required. To get started with the MySQL database, check the MySQL Tutorial website.

Introduction to MySQL Workbench

There are two editions of MySQL Workbench: the community edition and the commercial edition. The community edition is open source. Both editions are available for three major platforms; MS Windows, macOS, and Linux. The commercial edition comes with more functionalities at a cost.

Some of the extra functionalities available in the commercial edition are:

MySQL Workbench has five main functions:

Database design (data modeling)

This involves creating simple to complex entity-relationship (ER) models. Reverse engineering creates a database from ER models. Forward engineering creates an ER model from a live database.

Developing SQL

MySQL Workbench has a built-in SQL editor with syntax highlighting and auto-complete. It is used to interact with the MySQL Server.

Administration

Some of the MySQL Workbench database administrative functions are:

• Backup
• Recovery
• Audit
• Monitoring server performance
• Checking database health
• User management

MySQL Workbench has a visual performance dashboard. The visual performance dashboard enables database administrators to view key performance indicators. Below is a screenshot of the MySQL Workbench visual performance dashboard.

Data migration

MySQL Workbench is used to migrate databases from other relational database management systems (RDBMS). Some of the supported RDBMS are PostgreSQL, MS SQL Server, SQLite, MS Access, Sybase, and Sybase SQL Anywhere.

MySQL enterprise support

MySQL Workbench enterprise edition supports MySQL enterprise products.

In this tutorial, the focus will be on database design.

Setting up MySQL Workbench

Workbench is one of the MySQL products. Use MySQL Installer to manage MySQL products installations. Find the list of MySQL Workbench supported platforms on MySQL official website. For the MySQL Workbench hardware requirements, check the image below.

Installing MySQL Workbench on Windows

Download MySQL Installer from the official website. Install MySQL Workbench using the downloaded installer. For this installation, I recommend the default configurations, as shown in the screenshot below.

The Developer Default setup type comes with MySQL Workbench and other developer tools.

You can find more detailed installation guidelines for MySQL Workbench on MySQL Workbench Manual.

Verifying MySQL Workbench installation

Launch MySQL Workbench.

You can do this from the installation wizard.

• Linux: Launch by typing the command mysql-workbench. Alternatively, navigate to Activities > MySQL WorkBench.
• macOS: Navigate to Applications > MySQL Workbench.
• Windows: Navigate to Start > Programs > MySQL > MySQL Workbench.

Archiver 2 1 5 – open create and convert archives. Make sure there is a connection to the MySQL Server local instance, as shown in the screenshot below. If there is no connection, click the + icon to create a new connection, as highlighted in the screenshot below.

Provide the connection details.

The created connection will be displayed, as shown in the screenshot below.

Database design with MySQL Workbench

This section will create a new ER model and then translate it into a physical MySQL database. On MySQL Workbench, navigate to File > New Model as shown below.

Save the model. Double click MySQL Schema and change the name from mydb to booksdb. Click the Add Diagram icon to create a new EER diagram. Refer to the screenshot below.

After adding a new diagram, a new window will be opened, as in the screenshot below.

Scenario

In this tutorial, we will model and create a database that will be used to keep book details. The database should store books with author and publisher details. We will skip the database normalization process details.

The final database will have three main entities with attributes, as shown below:

1. Book: id, title, ISBN, publisher, total_pages, publication_year, author.
2. Author: id, first_name, last_name.
3. Publisher: id, name.

We will use the information in the tables below to design the ER model. All the column names have been defined. Primary keys, foreign keys, and datatypes are also outlined.

Book table

Author table

Publisher table

The relationships between the entities are as below:

Mysql Workbench Select Database

• The relationship between the book and the author is many-to-many. An author can have multiple publications. A book can also have multiple authors. This type of relationship requires an extra table called a bridge table. MySQL Workbench automatically creates a bridge table when we add a many-to-many relationship.
• The relationship between the book and the publisher is one-to-many. A book can only have one publisher. A publisher can publish multiple books.

Using MySQL Workbench, we will design an enhanced entity-relationship (EER) diagram. MySQL Workbench allows us to create tables, edit the attributes, and create relationships between the tables.

The visual design interface

The diagram below shows a MySQL Workbench design window screenshot. The window has different panels highlighted in different colors.

The vertical toolbar has different tools used in creating EER diagrams. The screenshot below shows all the tools. Hovering the mouse pointer on each tool will show the name or the function of each tool.

There are six tools used to create different types of relationships in MySQL Workbench. 1:1 is read as one-to-one; 1:n is read as one-to-many, n:m is read as many-to-many.

Add tables

We are going to add three tables to the EER model. The animation below shows the process of adding a table and the columns.

To add a table, follow these steps:

• Select the table tool on the vertical tools panel, then click anywhere on the EER diagram canvas. This creates a table with no columns.
• Double click the table created to open the table properties window.
• On the table properties window, change the table name.
• Add columns to the table. To add new columns, click on the last blank column. Edit the column name and select the appropriate data type for each column.
• Please do not add the columns described as foreign keys. MySQL Workbench has an easier way of adding them by creating relationships, and the foreign keys are added automatically.
• Select all the column properties such as primary key, not null, unique, and autoincrement.
• Repeat the process for all the tables.

Your EER diagram should look as shown below.

How To Use Mysql Workbench

Add relationships

We will add two different relationships to the EER diagram.

There is a one-to-many relationship between the book and the publisher. We will use the relationship tools to add relationships. To create a one-to-many relationship, make sure that one of the tables has a primary key.

Select the appropriate relationship tool. In this case, we are going to use the one-to-many non-identifying relationship. Click the table on the 'many' side. In this case, it is the book table.

Click the table containing the referenced key. In this case, it is the publisher table. A foreign key is created on the book table with the default name as fk_book_has_author_book. To change the foreign key properties, double click the connection line to open the relationship editor.

There is a many-to-many relationship between the book and the author. Select the many-to-many relationship tool. Click on the book table, then click on the author table as shown in the animation below. A bridge table with the name book_has_author is generated automatically.

After creating the relationships, the EER diagram will be as shown in the screenshot below.

You can find the EER diagram workbench file created in this tutorial on Github.

Forward engineering with MySQL Workbench

The visual database model created can be transformed into a physical database. This process is known as forward engineering. SQL code is generated and executed on a target MySQL Server. This is easier than writing the code manually.

Step 1

To launch forward engineering wizard, Navigate to Databases > Forward Engineer.

Step 2

Create a new connection to MySQL Server or select an existing one, as shown in the screenshot below.

Click NEXT.

Step 3

The wizard gives us SQL export options. For this exercise, use the default selected options.

Step 4

Select the objects you would like to include in the EER diagram. These will include tables, views, routines, users, and triggers. In this exercise, we only have tables.

Make sure the option to import 4 table objects is selected, then click NEXT.

Step 5

In this step, we are provided with the generated SQL script. Find the SQL file generated in this tutorial on Github.

Step 6

This is the final step. Click finish to commit. The generated SQL file is used to create a physical database in the connected MySQL Server.

To view the database created on MySQL Workbench, navigate to Database > Connect to Database. Choose an existing connection to connect to MySQL Server or create a new one. The database created will be as shown in the screenshot below.

Reverse engineering with MySQL Workbench

Reverse engineering enables us to have a better view of an existing database. A physical database is converted to an EER diagram. We will be reverse engineering the database we created earlier. Reverse engineering can be done while using a MySQL Create Script or by using a live database.

In this tutorial, we will use the live database we created in the forward engineering section.

Follow these steps:

Step 1

Navigate to Database > Reverse Engineer.

Step 2

Mysql Workbench Create Table Script

Create a new connection to the MySQL Server or select an existing one, as shown in the screenshot below.

Click NEXT.

Step 3

Review the information displayed and make sure that the connection was successful, then click NEXT.

Step 4

Select the schema you would like to reverse engineer. In this case, select booksdb, then click NEXT.

Step 5

The results of the tasks carried out are displayed in the wizard below. Review the results, then click NEXT.

Step 6

We are prompted to select the objects to reverse engineer. By default, all the available objects are selected. Leave the default options, then click NEXT.

Step 7

The wizard shows the reverse engineering progress. In the screenshot below, the process was successful. In case of an error, check error details by clicking Show Logs.

Click NEXT.

Step 8

This step shows the summary of the objects generated. In this case, we have four table objects.

Click Finish.

Output

The EER diagram below is generated. You can save it for later use.

Conclusion

MySQL Workbench is such a useful tool. It can be used by MySQL database administrators, system developers, and database developers. You can create a database from an ER model without writing MySQL statements. At first, the tool may seem complex to use, but with time it gets easier. Practice makes perfect.

Happy Coding!

About the author

Benson Kariuki is a graduate computer science student. He is a passionate and solution-oriented computer scientist. His interests are Web Development with WordPress, Big Data, and Machine Learning.