Importing and Exporting Data

This chapter introduces you to importing and exporting data. There are many ways to import and export data in MySQL. You will learn how to import and export data from and to comma-separated values (CSV) files using MySQL Workbench via table data. We will also cover importing and exporting via Structured Query Language (SQL) data with SQL scripts. An additional way to export data via result data and query results will also be covered. The final topic discussed is using SQL syntax to import and export data.

In this chapter, we will cover the following topics:

• Understanding table data import and export
• Understanding SQL data import and export
• Understanding result data export
• Understanding SQL syntax for importing and exporting data

Technical requirements

The code files of this chapter can be found at the following GitHub link: https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/chapter-5.

The CSV files required for this chapter can be found on the following GitHub link: https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/baseballdatabank-csv/csv-files.

Understanding table data import and export

In this section, you will learn how to import and export data with the table data import and export processes in MySQL Workbench. Table data import and export allows you to import and export both CSV and JavaScript Object Notation (JSON) files via MySQL Workbench. It provides you with many configuration options—for example, column separator types such as a comma or a semicolon—and it allows you to map columns in the file to columns in the table.

Importing CSV files with table data import

Using the database (schema) and the tables you created in the last chapter, we will add data to those tables via table data import. I named that schema yourschema, so the directions...

Understanding SQL data import and export

In this section, we will walk through how to import and export data with the SQL data import and export processes in MySQL Workbench.

Importing via data import in MySQL Workbench

Let's walk through how to import data via the Management or Administration tab in the left navigation area. This is what we did in Chapter 2, Installing and Using MySQL Workbench, in the Learning how to restore a database section. Importing using data import is the same process as restoring a database in MySQL Workbench. Follow these steps:

1. Open MySQL Workbench.
2. Connect to your local or dev/test instance where you created your database in the last chapter.

3. Click Administration or Management (depending...

Understanding result data export

In this section, we will walk through how to export data with the result data export process in MySQL Workbench. It's best to use the two previous sections to import data, table data, and SQL data, because importing isn't available via a result set in MySQL Workbench.

Exporting data directly from a result set

Let's walk through how to export data directly from a result set, as follows:

1. Open MySQL Workbench.
2. Connect to your local or dev/test instance where you created your database in the last chapter.
3. Expand the yourschema database with the down arrow and expand the tables with the down arrow, as illustrated in the following screenshot:

4. Right-click the teams table and choose...

Understanding SQL syntax for importing and exporting data

To import and export data via SQL scripts, the --secure_file_priv option must be properly configured. The secure_file_priv option may be disabled by default, or it may require you to place your file in a specific folder. You may be able to change this setting on your local instance, but you most likely won't have this level of permissions on a server, especially in a production environment.

Be VERY careful if you decide to change the ini file on your local instance of MySQL. The ini file contains essential configuration information required to run MySQL Server. You can damage the MySQL installation, for which the only solution may be to uninstall and reinstall MySQL. Do not make any ini file changes on a server without consulting a system or database administrator.

To check the secure_file_priv configuration...

Summary

In this chapter, you were introduced to importing and exporting data. You learned how to import and export data with the table data import and export functionality in MySQL Workbench. You also learned how to import and export data with the SQL data import and export functionality in MySQL Workbench. In the Understanding result data export section, you learned how to export data directly from MySQL Workbench. Finally, you learned how to use SQL syntax to import data with LOAD DATA INFILE, and how to export data with SELECT INTO OUTFILE.

In the next chapter, you will learn how to query a single table. This includes learning how to use the SQL SELECT statement, and FROM, WHERE, and ORDER BY clauses. This will also include how to tell which index your query is using and if you may need additional indexes.

Further reading

For more information, see the following list:

• Refer to this link for more information on the secure-file-priv option: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv
• Refer to this link for more details on LOAD DATA syntax: https://dev.mysql.com/doc/refman/5.7/en/load-data.html
• Refer to this link to learn more about importing and exporting in SQL Server: https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard?view=sql-server-ver15
• Refer to this link to learn more about importing and exporting in PostgreSQL: https://www.postgresql.org/docs/11/sql-copy.html
• Refer to this link to learn more about importing and exporting in Oracle: https://docs.oracle.com/en/cloud/saas/applications-common/19c/oafsm/manage-setup-using-csv-file-packages.html#OAFSM3573446

...

Questions

1. What are the different ways that you can import and export data in MySQL Workbench?
2. How do you import data with the Table Data Import Wizard feature?
3. How do you ensure you accurately map columns with the Table Data Import Wizard feature?
4. How do you export data with the Table Data Import Wizard feature?
5. How do you import data with the SQL data import wizard feature?
6. How do you export data with the SQL Data Import Wizard feature?
7. How do you export data with result data export?
8. What SQL syntax do you use to import data?
9. What SQL syntax do you use to export data?
10. What MySQL ini file setting will prevent you from being able to use SQL scripts to import and export data?