Learning MySQL, 2nd Edition

Chapter 7. Doing More with MySQL

MySQL is feature-rich. Over the past three chapters, you’ve seen the wide variety of techniques that can be used to query, modify, and manage data. However, there’s still much more that MySQL can do, and some of those additional features are the subject of this chapter.

In this chapter, you’ll learn how to:

• Insert data into a database from other sources, including with queries and from text files.

• Perform updates and deletes using multiple tables in a single statement.

• Replace data.

• Use MySQL functions in queries to meet more complex information needs.

• Analyze queries using the EXPLAIN statement and then improve their performance with simple optimization techniques.

• Use alternative storage engines to change table properties.

Inserting Data Using Queries

Much of the time, you’ll create tables using data from another source. The examples you saw in Chapter 3 therefore illustrate only part of the problem: they show you how to insert data that’s already in the form you want (that is, formatted as a SQL INSERT statement). The other ways to insert data include using SQL SELECT statements on other tables or databases and reading in files from other sources. This section shows you how to tackle the former method of inserting data; you’ll learn how to insert data from a file of comma-separated values in the next section, “Loading Data from Comma-Delimited Files”.

Suppose we’ve decided to create a new table in the sakila database. It’s going ...