Databases Reference
In-Depth Information
CHAPTER 8
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
Inserting Data Using Queries
Much of the time, you'll create tables using data from another source. The examples
you've seen so far in Chapter 5 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 an
SQL
INSERT
statement. The other ways to insert data include using SQL
SELECT
state-
ments 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 you've decided to create a new table in the
music
database. It's going to store
a shuffle list, tracks that are randomly selected from your music collection, put into a
list, and played to you in that order. It's a way of tasting part of the collection, redis-
covering some old favorites and learning about hidden treasures in those albums you
haven't explored. We've decided to structure the table as follows:
