MySQL® Crash Course
By Ben Forta
...............................................
Publisher: Sams
Pub Date: December 15, 2005
ISBN: 0-672-32712-0
Pages: 336
Table of Contents | Index
If you are a MySQ L user who wants to find out m ore about the program , but is not interested in becom ing a database adm inistrator just to be able to read book s on MySQ L, then MySQL Crash
Course is for you. This tutorial-based book is organized into a series of short, easy-to-follow lessons that tak e a very hands-on approach to m eeting the needs of the average MySQ L user. The
essentials of MySQ L are taught through a series of self-paced lessons in which you will com plete ex ercises that illustrate the m ost im portant aspects of MySQ L. You will learn how to:
Use the MySQL toolset.
Use the aggregate functions to analyze data.
Perform insert, update and delete operations.
Com bine queries using unions.
Create and m odify tables and access table schem as.
Manage databases, users and security privileges.
Don't get bogged down in the database theory and relational design of other MySQ L book s. Let SQL Crash Course teach you what you need to k now, when you need to k now it, so you can quick ly
get on your way with MySQ L.
MySQL® Crash Course
By Ben Forta
...............................................
Publisher: Sams
Pub Date: December 15, 2005
ISBN: 0-672-32712-0
Pages: 336
Table of Contents | Index
Copyright
About the Author
Acknow ledgments
We Want to Hear fromYou!
Introduction
Who Is This Book For?
Companion Website
Conventions Used in This Book
Chapter 1. Understanding SQL
Database Basics
What Is SQL?
Try It Yourself
Summary
Chapter 2. Introducing MySQL
What Is MySQL?
MySQL Tools
Summary
Chapter 3. Working with MySQL
Making the Connection
Selecting a Database
Learning About Databases and Tables
Summary
Chapter 4. Retrieving Data
The SELECTStatement
Retrieving Individual Columns
Retrieving Multiple Columns
Retrieving All Columns
Retrieving Distinct Rows
Limiting Results
Using Fully Qualified Table Names
Summary
Chapter 5. Sorting Retrieved Data
Sorting Data
Sorting by Multiple Columns
Specifying Sort Direction
Summary
Chapter 6. Filtering Data
Using the WHEREClause
The WHEREClause Operators
Summary
Chapter 7. Advanced Data Filtering
Combining WHEREClauses
Using the INOperator
Using the NOTOperator
Summary
Chapter 8. Using Wildcard Filtering
Using the LIKEOperator
Tips for Using Wildcards
Summary
Chapter 9. Searching Using Regular Expressions
Understanding Regular Expressions
Using MySQL Regular Expressions
Summary
Chapter 10. Creating Calculated Fields
Understanding Calculated Fields
Concatenating Fields
Performing Mathematical Calculations
Summary
Chapter 11. Using Data Manipulation Functions
Understanding Functions
Using Functions
Summary
Chapter 12. Summarizing Data
Using Aggregate Functions
Aggregates on Distinct Values
Combining Aggregate Functions
Summary
Chapter 13. Grouping Data
Understanding Data Grouping
Creating Groups
Filtering Groups
Grouping and Sorting
SELECTClause Ordering
Summary
Chapter 14. Working with Subqueries
Understanding Subqueries
Filtering by Subquery
Using Subqueries As Calculated Fields
Summary
Chapter 15. Joining Tables
Understanding Joins
Creating a Join
Summary
Chapter 16. Creating Advanced Joins
Using Table Aliases
Using Different Join Types
Using Joins with Aggregate Functions
Using Joins and Join Conditions
Summary
Chapter 17. Combining Queries
Understanding Combined Queries
Creating Combined Queries
Summary
Chapter 18. Full-Text Searching
Understanding Full-Text Searching
Using Full-Text Searching
Summary
Chapter 19. Inserting Data
Understanding Data Insertion
Inserting Complete Rows
Inserting Multiple Rows
Inserting Retrieved Data
Summary
Chapter 20. Updating and Deleting Data
Updating Data
Deleting Data
Guidelines for Updating and Deleting Data
Summary
Chapter 21. Creating and Manipulating Tables
Creating Tables
Updating Tables
Deleting Tables
Renaming Tables
Summary
Chapter 22. Using Views
Understanding Views
Using Views
Summary
Chapter 23. Working with Stored Procedures
Understanding Stored Procedures
Why Use Stored Procedures
Using Stored Procedures
Summary
Chapter 24. Using Cursors
Understanding Cursors
Working with Cursors
Summary
Chapter 25. Using Triggers
Understanding Triggers
Creating Triggers
Dropping Triggers
Using Triggers
Summary
Chapter 26. Managing Transaction Processing
Understanding Transaction Processing
Controlling Transactions
Summary
Chapter 27. Globalization and Localization
Understanding Character Sets and Collation Sequences
Working with Character Set and Collation Sequences
Summary
Chapter 28. Managing Security
Understanding Access Control
Managing Users
Summary
Chapter 29. Database Maintenance
Backing Up Data
Performing Database Maintenance
Diagnosing Startup Problems
Review Log Files
Summary
Chapter 30. Improving Performance
Improving Performance
Summary
Appendix A. Getting Started with MySQL
What You'll Need
Obtaining the Software
Installing the Software
Preparing for Your Chapters
Appendix B. The Example Tables
Understanding the Sample Tables
Creating the Sample Tables
Appendix C. MySQL Statement Syntax
ALTER TABLE
COMMIT
CREATEINDEX
CREATEPROCEDURE
CREATETABLE
CREATEUSER
CREATEVIEW
DELETE
DROP
INSERT
INSERT SELECT
ROLLBACK
SAVEPOINT
SELECT
START TRANSACTION
UPDATE
Appendix D. MySQL Datatypes
String Datatypes
Numeric Datatypes
Date and Time Datatypes
Binary Datatypes
Appendix E. MySQL Reserved Words
Developer's Library
Index
Copyright
MySQL® Crash Course
Copyright © 2006 by Sam s Publishing
All rights reserved. No part of this book shall be reproduced, stored in a retrieval system , or transm itted by any m eans, electronic, m echanical, photocopying, recording, or otherwise, without written
perm ission from the publisher. No patent liability is assum ed with respect to the use of the inform ation contained herein. Although every precaution has been tak en in the preparation of this book ,
the publisher and author assum e no responsibility for errors or om issions. Nor is any liability assum ed for dam ages resulting from the use of the inform ation contained herein.
Library of Congress Catalog Card Num ber: 2005927094
Printed in the United States of Am erica
First Printing: Decem ber 2005
08 07 06 05 4 3 2 1
Trademarks
All term s m entioned in this book that are k nown to be tradem ark s or service m ark s have been appropriately capitalized. Sam s Publishing cannot attest to the accuracy of this inform ation. Use of a
term in this book should not be regarded as affecting the validity of any tradem ark or service m ark .
Warning and Disclaimer
Every effort has been m ade to m ak e this book as com plete and as accurate as possible, but no warranty or fitness is im plied. The inform ation provided is on an "as is" basis. The author and the
publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or dam ages arising from the inform ation contained in this book .
Bulk Sales
Sam s Publishing offers ex cellent discounts on this book when ordered in quantity for bulk purchases or special sales. For m ore inform ation, please contact
U.S. Corporate and Government Sales
1-800-382-3419
corpsales@pearsontechgroup.com
For sales outside of the U.S., please contact
International Sales
international@pearsoned.com
Publisher Paul Boger
A cquisitions Editor Loretta Yates
Development Editor Mark Renfrow
Managing Editor Charlotte Clapp
Project Editor Andy Beaster
Production Editor Heather W ilk ins
Indexer Aaron Black
Technical Editors Jochem vanDieten, Tim othy Boronczyk
Publishing Coordinator Cindy Teeters
Interior Designer Susan Geiselm an
Cover Designer Gary Adair
Page Layout Nonie Ratcliff
About the Author
Ben Forta is Macrom edia's Senior Technical Evangelist, and has alm ost 20 years of ex perience in the com puter industry in product developm ent, support, training, and product m ark eting. Ben is the
author of the best-selling Sams Teach Yourself SQL in 10 Minutes (now in its third edition, and translated into m ore than a dozen languages), ColdFusion Web Application Construction Kit and Advanced
ColdFusion Application Development (both published by Q ue), Sams Teach Yourself Regular Expressions in 10 Minutes, as well as book s on Flash, Java, W AP, W indows 2000, and other subjects. He has
ex tensive ex perience in database design and developm ent, has im plem ented databases for several highly successful com m ercial software program s, and is a frequent lecturer and colum nist on
Internet and database technologies. Born in London, England, and educated in London, New York , and Los Angeles, Ben now lives in O ak Park , Michigan with his wife Marcy and their seven children.
Ben welcom es your em ail at ben@forta.com , and invites you to visit his website at www.forta.com /.
Acknowledgments
First of all, I'd lik e to thank the folk s at Sam s for once again granting m e the flex ibility and freedom to build this book as I saw fit. Thank s to Mark Renfrow for all of his feedback (on this, and
previous, book s). Special thank s to Loretta Yates for bravely stepping in m idstream to help get the project back on track , and for even greater bravery in signing up for the nex t two titles in this
s e rie s .
Thank s to m y technical editors, Jochem vanDieten and Tim othy Boronczyk , for a superb (and brutally honest) technical review. O h, and those "m istak es" were all deliberate, just to m ak e sure you
were paying attention. ;-)
And finally, this book was written in response to num erous requests from readers of m y Sams Teach Yourself SQL in 10 Minutes. The feedback and suggestions were invaluable and greatly
appreciated, and I hope I have lived up to your ex pectations.
We Want to Hear from You!
As the reader of this book , you are our m ost im portant critic and com m entator. W e value your opinion and want to k now what we're doing right, what we could do better, what areas you'd lik e to see
us publish in, and any other words of wisdom you're willing to pass our way.
As an associate publisher for Sam s Publishing, I welcom e your com m ents. You can em ail or write m e directly to let m e k now what you did or didn't lik e about this book as well as what we can do to
m ak e our book s better.
Please note that I cannot help you with technical problems related to the topic of this book. We do have a User Services group, however, where I will forward specific technical questions related to the book.
W hen you write, please be sure to include this book 's title and author as well as your nam e, em ail address, and phone num ber. I will carefully review your com m ents and share them with the author
and editors who work ed on the book .
Em ail: feedback @sam spublishing.com
Mail:
Paul Boger
P u b lis h e r
Sams Publishing
800 East 96th Street
Indianapolis, IN 46240 USA
For m ore inform ation about this book or another Sam s Publishing title, visit our website at www.sam spublishing.com . Type the ISBN (ex cluding hyphens) or the title of a book in the Search field to
find the page you're look ing for.
Introduction
MySQ L has becom e one of the m ost popular database m anagem ent system s in the world. From sm all developm ent projects to som e of the best-k nown and m ost prestigious sites on the W eb,
MySQ L has proven itself to be a solid, reliable, fast, and trusted solution to all sorts of data storage needs.
This book is based on m y best-selling Sams Teach Yourself SQL in 10 Minutes. That book has becom e one of the m ost-used SQ L tutorials in the world, with an em phasis on teaching what you really
need to k nowm ethodically, system atically, and sim ply. But as popular and as successful as that book is, it does have som e lim itations:
In covering all of the m ajor DBMSs, coverage of DBMS-specific features and functionality had to be k ept to a m inim um .
To sim plify the SQ L taught, the lowest com m on denom inator had to be foundSQ L statem ents that would (as m uch as possible) work with all m ajor DBMSs. This requirem ent necessitated that
better DBMS-specific solutions not be covered.
Although basic SQ L tends to be rather portable between DBMSs, m ore advanced SQ L m ost definitely is not. As such, that book could not cover advanced topics, such as triggers, cursors,
stored procedures, access control, transactions, and m ore, in any real detail.
And that is where this book com es in. MySQL Crash Course builds on the proven tutorials and structure of Sams Teach Yourself SQL in 10 Minutes, without getting bogged down with anything but MySQ L.
Starting with sim ple data retrieval and work ing on to m ore com plex topics, including the use of joins, subqueries, regular ex pression and full tex t-based searches, stored procedures, cursors,
triggers, table constraints, and m uch m ore. You'll learn what you need to k now m ethodically, system atically, and sim plyin highly focused chapters designed to m ak e you im m ediately and
effortlessly productive.
So turn to Chapter 1, and get to work . You'll be tak ing advantage of all MySQ L has to offer in no tim e at all.
Who Is This Book For?
This book is for you if
You are new to SQ L.
You are just getting started with MySQ L and want to hit the ground running.
You want to quick ly learn how to get the m ost out of MySQ L.
You want to learn how to use MySQ L in your own application developm ent.
You want to be productive quick ly and easily using MySQ L without having to call som eone for help.
Companion Website
This book has a com panion website online at http://forta.com /book s/0672327120/. Visit the site to access
Table creation and population scripts used to create the ex am ple tables used throughout this book
Visit the online support forum
Access online errata (should one be required)
Find other book s that m ay be of interest to you
Conventions Used in This Book
This book uses different typefaces to differentiate between code and regular English, and also to help you identify im portant concepts. as though they were part of
Te x t tha t you type a nd te x t tha t should a ppe a r on your scre e n is pre se nte d in monospace type . It looks like this to mimic the way text looks on your screen.
Placeholders for variables and ex pressions appear in monospace italic font. You should replace the placeholder with the specific value it represents.
This arrow ( ) at the beginning of a line of code m eans that a single line of code is too long to fit on the printed page. Continue typing all the characters after the
the preceding line.
Note
A Note presents interesting pieces of inform ation related to the surrounding discussion.
Tip
A Tip offers advice or teaches an easier way to do som ething.
Caution
A Caution advises you about potential problem s and helps you steer clear of disaster.
New Term
A New Term box provide clear definitions of new, essential term s.
• Input
The Input icon identifies code that you can type in yourself. It usually appears nex t to a listing.
• Output
The O utput icon highlights the output produced by running MySQ L code. It usually appears after a listing.
• A nalysis
The Analysis icon alerts you to the author's line-by-line analysis of input or output.
Chapter 1. Understanding SQL
In this chapter, you'll learn about databases and SQL, prerequisites to learning MySQL.
Database Basics
The fact that you are reading this book indicates that you, som ehow, need to interact with databases. And so before diving into MySQ L and its im plem entation of the SQ L language, it is im portant
that you understand som e basic concepts about databases and database technologies.
W hether you are aware of it or not, you use databases all the tim e. Each tim e you select a nam e from your em ail address book , you are using a database. If you conduct a search on an Internet
search site, you are using a database. W hen you log into your network at work , you are validating your nam e and password against a database. Even when you use your ATM card at a cash
m achine, you are using databases for PIN verification and balance check ing.
But even though we all use databases all the tim e, there rem ains m uch confusion over what ex actly a database is. This is especially true because different people use the sam e database term s to
m ean different things. Therefore, a good place to start our study is with a list and ex planation of the m ost im portant database term s.
Tip
Reviewing Basic Concepts W hat follows is a very brief overview of som e basic database concepts. It is intended to either jolt your m em ory if you already have som e database
ex perience, or to provide you with the absolute basics, if you are new to databases. Understanding databases is an im portant part of m astering MySQ L, and you m ight want to find a
good book on database fundam entals to brush up on the subject if needed.
What Is a Database?
The term database is used in m any different ways, but for our purposes a database is a collection of data stored in som e organized fashion. The sim plest way to think of it is to im agine a database
as a filing cabinet. The filing cabinet is sim ply a physical location to store data, regardless of what that data is or how it is organized.
New Term
Database A container (usually a file or set of files) to store organized data.
Caution
Misuse Causes Confusion People often use the term database to refer to the database software they are running. This is incorrect, and it is a source of m uch confusion. Database software
is actually called the Database Management System (or DBMS). The database is the container created and m anipulated via the DBMS. A database m ight be a file stored on a hard drive,
but it m ight not. And for the m ost part this is not even significant as you never access a database directly anyway; you always use the DBMS and it accesses the database for you.
Tables
W hen you store inform ation in your filing cabinet you don't just toss it in a drawer. Rather, you create files within the filing cabinet, and then you file related data in specific files.
In the database world, that file is called a table. A table is a structured file that can store data of a specific type. A table m ight contain a list of custom ers, a product catalog, or any other list of
inform ation.
New Term
Table A structured list of data of a specific type.
The k ey here is that the data stored in the table is one type of data or one list. You would never store a list of custom ers and a list of orders in the sam e database table. Doing so would m ak e
subsequent retrieval and access difficult. Rather, you'd create two tables, one for each list.
Every table in a database has a nam e that identifies it. That nam e is always uniquem eaning no other table in that database can have the sam e nam e.
Note
Table Names W hat m ak es a table nam e unique is actually a com bination of several things, including the database nam e and table nam e. This m eans that while you cannot use the
sam e table nam e twice in the sam e database, you definitely can reuse table nam es in different databases.
Tables have characteristics and properties that define how data is stored in them . These include inform ation about what data m ay be stored, how it is brok en up, how individual pieces of inform ation
are nam ed, and m uch m ore. This set of inform ation that describes a table is k nown as a schem a, and schem a are used to describe specific tables within a database, as well as entire databases
(and the relationship between tables in them , if any).
New Term
Schema Inform ation about database and table layout and properties.
Note
Schema or Database? O ccasionally schem a is used as a synonym for database (and schem ata as a synonym for databases). W hile unfortunate, it is usually clear from the contex t which
m eaning of schem a is intended. In this book , schem a will refer to the definition given here.
Columns and Datatypes
Tables are m ade up of colum ns. A colum n contains a particular piece of inform ation within a table.
New Term
Column A single field in a table. All tables are m ade up of one or m ore colum ns.
The best way to understand this is to envision database tables as grids, som ewhat lik e spreadsheets. Each colum n in the grid contains a particular piece of inform ation. In a custom er table, for
ex am ple, one colum n contains the custom er num ber, another contains the custom er nam e, and the address, city, state, and ZIP Code are all stored in their own colum ns.
Tip
Breaking Up Data It is ex trem ely im portant to break data into m ultiple colum ns correctly. For ex am ple, city, state, and ZIP Code should always be separate colum ns. By break ing these
out, it becom es possible to sort or filter data by specific colum ns (for ex am ple, to find all custom ers in a particular state or in a particular city). If city and state are com bined into one
colum n, it would be ex trem ely difficult to sort or filter by state.
Each colum n in a database has an associated datatype. A datatype defines what type of data the colum n can contain. For ex am ple, if the colum n is to contain a num ber (perhaps the num ber of
item s in an order), the datatype would be a num eric datatype. If the colum n were to contain dates, tex t, notes, currency am ounts, and so on, the appropriate datatype would be used to specify this.
New Term
Datatype A type of allowed data. Every table colum n has an associated datatype that restricts (or allows) specific data in that colum n.
Datatypes restrict the type of data that can be stored in a colum n (for ex am ple, preventing the entry of alphabetical characters into a num eric field). Datatypes also help sort data correctly, and play
an im portant role in optim izing disk usage. As such, special attention m ust be given to pick ing the right datatype when tables are created.
Rows
Data in a table is stored in rows; each record saved is stored in its own row. Again, envisioning a table as a spreadsheet style grid, the vertical colum ns in the grid are the table colum ns, and the
horizontal rows are the table rows.
For ex am ple, a custom ers table m ight store one custom er per row. The num ber of rows in the table is the num ber of records in it.
New Term
Row A record in a table.
Note
Records or Rows? You m ight hear users refer to database records when referring to rows. For the m ost part, the two term s are used interchangeably, but row is technically the correct
term .
Primary Keys
Every row in a table should have som e colum n (or set of colum ns) that uniquely identifies it. A table containing custom ers m ight use a custom er num ber colum n for this purpose, whereas a table
containing orders m ight use the order ID. An em ployee list table m ight use an em ployee ID or the em ployee Social Security num ber colum n.
New Term
Primary Key A colum n (or set of colum ns) whose values uniquely identify every row in a table.
This colum n (or set of colum ns) that uniquely identifies each row in a table is called a prim ary k ey. The prim ary k ey is used to refer to a specific row. W ithout a prim ary k ey, updating or deleting
specific rows in a table becom es ex trem ely difficult because there is no guaranteed safe way to refer to just the rows to be affected.
Tip
A lways Define Primary Keys Although prim ary k eys are not actually required, m ost database designers ensure that every table they create has a prim ary k ey so future data
m anipulation is possible and m anageable.
Any colum n in a table can be established as the prim ary k ey, as long as it m eets the following conditions:
No two rows can have the sam e prim ary k ey value.
Every row m ust have a prim ary k ey value (prim ary k ey colum ns m ay not allow NULL values).
Note
Primary Key Rules The rules listed here are enforced by MySQ L itself.
Prim ary k eys are usually defined on a single colum n within a table. But this is not required, and m ultiple colum ns m ay be used together as a prim ary k ey. W hen m ultiple colum ns are used, the
rules previously listed m ust apply to all colum ns that m ak e up the prim ary k ey, and the values of all colum ns together m ust be unique (individual colum ns need not have unique values).
Tip
Primary Key Best Practices In addition to the rules that MySQ L enforces, several universally accepted best practices that should be adhered to are
Don't update values in prim ary k ey colum ns.
Don't reuse values in prim ary k ey colum ns.
Don't use values that m ight change in prim ary k ey colum ns. (For ex am ple, when you use a nam e as a prim ary k ey to identify a supplier, you would have to change the prim ary
k ey when the supplier m erges and changes its nam e.)
There is another very im portant type of k ey called a foreign k ey, but I'll get to that later on in Chapter 15, "Joining Tables."
What Is SQL?
SQ L (pronounced as the letters S-Q -L or as sequel) is an abbreviation for Structured Q uery Language. SQ L is a language designed specifically for com m unicating with databases.
Unlik e other languages (spok en languages such as English, or program m ing languages such as Java or Visual Basic), SQ L is m ade up of very few words. This is deliberate. SQ L is designed to do
one thing and do it wellprovide you with a sim ple and efficient way to read and write data from a database.
W hat are the advantages of SQ L?
SQ L is not a proprietary language used by specific database vendors. Alm ost every m ajor DBMS supports SQ L, so learning this one language enables you to interact with just about every
database you'll run into.
SQ L is easy to learn. The statem ents are all m ade up of descriptive English words, and there aren't that m any of them .
Despite its apparent sim plicity, SQ L is actually a very powerful language, and by cleverly using its language elem ents you can perform very com plex and sophisticated database operations.
Note
DBMS-Specific SQL Although SQ L is not a proprietary language and there is a standards com m ittee which tries to define SQ L syntax that can be used by all DBMSs, the reality is that no
two DBMSs im plem ent SQ L identically. The SQ L taught in this book is specific to MySQ L, and while m uch of the language taught will be usable with other DBMSs, do not assum e com plete
SQ L syntax portability.
Try It Yourself
All of the chapters in this book use work ing ex am ples, showing you the SQ L syntax , what it does, and ex plaining why it does it. I'd strongly suggest that you try each and every ex am ple for yourself
so as to learn MySQ L first hand.
Appendix B, "The Ex am ple Tables," describes the ex am ple tables used throughout this book , and ex plains how to obtain and install them . If you have not done so, refer to this appendix before
p ro ce e d in g .
Note
You Need MySQL O bviously, you'll need access to a copy of MySQ L to follow along. Appendix A, "Getting Started with MySQ L," ex plains where to get a copy of MySQ L and provides som e
pointers for getting started. If you do not have access to a copy of MySQ L, refer to that appendix before proceeding.
Summary
In this first chapter, you learned what SQ L is and why it is useful. Because SQ L is used to interact with databases, you also reviewed som e basic database term inology.
Chapter 2. Introducing MySQL
In this chapter, you'll learn what MySQL is, and the tools you can use when working with it.
What Is MySQL?
In the previous chapter you learned about databases and SQ L. As ex plained, it is the database software (DBMS or Database Management System) that actually does all the work of storing, retrieving,
m anaging, and m anipulating data. MySQ L is a DBMS; that is, it is database software.
MySQ L has been around for a long tim e, and is now installed and in use at m illions of installations worldwide. W hy do so m any organizations and developers use MySQ L? Here are som e of the
re a s o n s :
Cost MySQ L is open-source, and is usually free to use (and even m odify) the software without paying for it.
Performance MySQ L is fast (m ak e that very fast).
Trusted MySQ L is used by som e of the m ost im portant and prestigious organizations and sites, all of whom entrust it with their critical data.
Simplicity MySQ L is easy to install and get up and running.
In fact, the only real technical criticism of MySQ L is that it has not always supported the functionality and features offered by other DBMSs. But as new features are added to each new version, this is
ch a n g in g .
Client-Server Software
DBMSs fall into two categories: shared filebased and client-server. The form er (which include products such as Microsoft Access and FileMak er) are designed for desk top use and are generally not
intended for use on higher-end or m ore critical applications.
Databases such as MySQ L, O racle, and Microsoft SQ L Server are client-serverbased databases. Client-server applications are split into two distinct parts. The server portion is a piece of software that
is responsible for all data access and m anipulation. This software runs on a com puter called the database server.
O nly the server software interacts with the data files. All requests for data, data additions and deletions, and data updates are funneled through the server software. These requests or changes
com e from com puters running client software. The client is the piece of software with which the user interacts. If you request an alphabetical list of products, for ex am ple, the client software subm its
that request over the network to the server software. The server software processes the request; filters, discards, and sorts data as necessary; and sends the results back to your client software.
Note
How Many Computers? The client and server software m ay be installed on two com puters or on one com puter. Regardless, the client software com m unicates with the server software for
all database interaction, be it on the sam e m achine or not.
All this action occurs transparently to you, the user. The fact that data is stored elsewhere or that a database server is even perform ing all this processing for you is hidden. You never need to
access the data files directly. In fact, m ost network s are set up so that users have no access to the data, or even the drives on which it is stored.
W hy is this significant? Because to work with MySQ L you'll need access to both a com puter running the MySQ L server software and client software with which to issue com m ands to MySQ L:
The server software is the MySQ L DBMS. You can be running a locally installed copy, or you can connect to a copy running a rem ote server to which you have access.
The client can be MySQ L-provided tools, scripting languages (such as Perl), web application developm ent languages (such as ASP, ColdFusion, JSP, and PHP), program m ing languages (such
as C, C++, and Java), and m ore.
MySQL Versions
Client tools are revisited in a m om ent. First, a quick word about DBMS versions.
The current version of MySQ L is version 5 (although MySQ L 3 and 4 are in use in m any organizations). Here are the m ajor changes introduced in recent revisions:
4 InnoDB engine adding support for transactions and m ore (Chapter 26, "Managing Transaction Processing"), unions (Chapter 17, "Com bining Q ueries"), im proved full tex t searching
(Chapter 18, "Full-Tex t Searching"), and m ore.
4.1 Significant additions to function libraries, subqueries (Chapter 14, "W ork ing with Subqueries"), integrated help, and m ore.
5 Stored procedures (Chapter 23, "W ork ing with Stored Procedures"), triggers (Chapter 25, "Using Triggers"), cursors (Chapter 24, "Using Cursors"), views (Chapter 22, "Using Views"), and
m ore.
Versions 4.1 and 5 added significant functionality to MySQ L, m uch of which is covered in the later chapters in this book .
Tip
Use 4.1 or Higher MySQ L 4.1 introduced significant changes to the MySQ L function libraries, and this book was written for use with that version or later. W hile m uch of the early content
does indeed apply to MySQ L 3 and 4, m any of the ex am ples will not work with those versions.
Note
Version Requirements Noted Any chapter that requires a specific version of MySQ L is clearly noted as such at the start of that chapter.
Caution
Beta Software At the tim e that this book goes to press, MySQ L 5 is still in beta and is not yet final-release software. This does not m ean that you cannot use MySQ L 5, but it does m ean
that you should proceed with caution as the software m ight contain bugs or problem s that have yet to be addressed.
MySQL Tools
As just ex plained, MySQ L is a client-server DBMS, and so to use MySQ L you'll need a client, an application that you'd use to interact with MySQ L (giving it com m ands to be ex ecuted).
There are lots of client application options, but when learning MySQ L (and indeed, when writing and testing MySQ L scripts) you are best off using a utility designed for just that purpose. And there
are three tools in particular that warrant specific m ention.
mysql Command-Line Utility
Every MySQ L installation com es with a sim ple com m and-line utility called mysql. This utility does not have any drop-down m enus, fancy user interfaces, m ouse support, or anything lik e that.
Typing mysql at your operating system com m and prom pt brings up a sim ple prom pt that look s lik e this:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.4-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Note
MySQL Options and Parameters If you just type mysql by itself, you m ight receive an error m essage. This will lik ely be because security credentials are needed or because MySQ L is not
running locally or on the default port. mysql accepts an array of com m and-line param eters you can (and m ight need to) use. For ex am ple, to specify a user login nam e of ben, you'd use
mysql u ben. To spe cify a use rna m e , host na m e , port, a nd be prom pte d for a pa ssword, you'd use mysql u ben p h myserver P 9999.
A com ple te list of com m and-line options and param e te rs can be obtaine d using mysql help.
O f course, your version and connection inform ation m ight differ, but you'll be able to use this utility regardless. You'll note that
C om m ands are type d afte r the mysql> prom pt.
Com m ands end with ; or \g; in other words, just pressing Enter will not ex ecute the com m and.
Type help or \h to obtain he lp. You can also provide additional te x t to obtain he lp on spe cific com m ands (for e x am ple , help select to obtain he lp on using the SELECT state m e nt).
Type quit or exit to quit the com m and-line utility.
The mysql com m and-line utility is one of the m ost used and is invaluable for quick testing and ex ecuting scripts (such as the sam ple table creation and population scripts m entioned in the previous
chapter and in Appendix B, "The Ex am ple Tables"). In fact, all of the output ex am ples used in this book are grabs from the mysql com m and-line output.
Tip
Familiarize Yourself with the m y s q l Command-Line Utility Even if you opt to use one of the graphical tools described nex t, you should m ak e sure to fam iliarize yourself with the mysql
com m and-line utility, as this is the one client you can safely rely on to always be present (as it is part of the core MySQ L installation).
MySQL Administrator
MySQ L Adm inistrator is a graphical interactive client designed to sim plify the adm inistration of MySQ L servers.
Note
Obtaining MySQL A dministrator MySQ L Adm inistrator is not installed as part of the core MySQ L installation. Instead, it m ust be downloaded from http://dev.m ysql.com /downloads/
(versions are available for Linux , Mac O S X, and W indows, and source code is downloadable, too).
MySQ L Adm inistrator prom pts for server and login inform ation (and allows you to save server definitions for future selection), and then displays icons that allow for the selection of different views.
Am ongst these are
Server Information displays status and version inform ation about the connected server and client.
Service Control allows you to stop and start MySQ L as well as specify server features.
User A dministration is used to define MySQ L users, logins, and privileges.
Catalogs lists available databases and allows for the creation of databases and tables.
Note
Create a Datasource for This Book You m ight want to use the Create New Schema option to create a datasource for the tables and chapters in this book . The ex am ples use a datasource
nam e d crashcourse; fe e l fre e to use the sam e nam e or one of your choice .
Tip
Quick A ccess to Other Tools The MySQ L Adm inistrator Tools m enu contains options to launch the mysql com m and-line utility (described previously) and the MySQ L Q uery Browser
(described nex t).
In fact, the MySQ L Q uery Browser also contains m enu options to launch the mysql com m and-line utility and the MySQ L Adm inistrator.
MySQL Query Browser
MySQ L Q uery Browser is a graphical interactive client used to write and ex ecute MySQ L com m ands.
Note
Obtaining MySQL Query Browser Lik e MySQ L Adm inistrator, MySQ L Q uery Browser is not installed as part of the core MySQ L installation. Instead, it too m ust be downloaded from
http://dev.m ysql.com /downloads/ (versions are available for Linux , Mac O S X, and W indows, and source code is downloadable, too).
MySQ L Q uery Browser prom pts for server and login inform ation (saved definitions are shared between MySQ L Q uery Browser and MySQ L Adm inistrator) and then displays the application interface.
Note the following:
MySQ L com m ands are typed into the window at the top of the screen. W hen the statem ent has been entered, click the Ex ecute button to subm it it to MySQ L for processing.
Results (if there are any) are displayed in a grid in the large area to the left of the screen.
Multiple statem ents and results can be rendered in their own tabs to allow for rapid switching between them .
O n the right of the screen is a tab that lists all available datasources (called schemata here), ex pand any datasource to see its tables, and ex pand any table to see its colum ns.
You can also select tables and colum ns to have MySQ L Q uery Browser write MySQ L statem ents for you.
To the right of the Schem ata tab is a History tab that m aintains a history of ex ecuted MySQ L statem ents. This is very useful when you need to test different versions of MySQ L statem ents.
Help on MySQ L syntax , functions, and m ore is available at the bottom right of the screen.
Tip
Execute Saved Scripts You can use MySQ L Q uery Browser to ex ecute saved scriptsthe scripts used to create and populate the tables used in throughout this book , for ex am ple. To do
this, select File, O pen Script, select the script (which will be displayed in a new tab), and click the Ex ecute button.
Summary
In this chapter, you learned what ex actly MySQ L is. You were also introduced to several client utilities (one included com m and-line utility and two optional but highly recom m ended graphical
u tilitie s ).
Chapter 3. Working with MySQL
In this chapter, you'll learn how to connect and login to MySQL, how to issue MySQL statements, and how to obtain information about databases and tables.
Making the Connection
Now that you have a MySQ L DBMS and client software to use with it, it would be worthwhile to briefly discuss connecting to the database.
MySQ L, lik e all client-server DBMSs, requires that you log in to the DBMS before being able to issue com m ands. Login nam es m ight not be the sam e as your network login nam e (assum ing that you
are using a network ); MySQ L m aintains its own list of users internally, and associates rights with each.
W hen you first installed MySQ L, you were probably prom pted for an adm inistrative login (often nam ed root) and a password. If you are using your own local server and are sim ply ex perim enting with
MySQ L, using this login is fine. In the real world, however, the adm inistrative login is closely protected (as access to it grants full rights to create tables, drop entire databases, change logins and
passwords, and m ore).
Tip
Using MySQL A dministrator The MySQ L Adm inistrator Users view provides a sim ple interface that can be used to define new users, including assigning passwords and access rights.
To connect to MySQ L you need the following pieces of inform ation:
The hostnam e (the nam e of the com pute r)this is localhost if conne cting to a local MySQ L se rve r
The port (if a port other than the default 3306 is used)
A valid user nam e
The user password (if required)
As ex plained in Chapter 2, "Introducing MySQ L," all of this inform ation can be passed to the mysql com m and-line utility, or entered into the server connection screen in MySQ L Adm inistrator and
MySQ L Q uery Browser.
Note
Using Other Clients If you are using a client other than the ones m entioned here, you still need to provide this inform ation in order to connect to MySQ L.
After you are connected, you have access to whatever databases and tables your login nam e has access to. (Logins, access control, and security are revisited in Chapter 28, "Managing Security").
Selecting a Database
W hen you first connect to MySQ L, you do not have any databases open for use. Before you can perform any database operations, you need to select a database. To do this you use the USE
k eyword.
New Term
Keyword A reserved word that is part of the MySQ L language. Never nam e a table or colum n using a k eyword. Appendix E, "MySQ L Reserved W ords," lists the MySQ L k eywords.
For e x am ple , to use the crashcourse database you would e nte r the following:
• Input
USE crashcourse;
• Output
Database changed
• A nalysis
The USE state m e nt doe s not re turn any re sults. De pe nding on the clie nt use d, som e form of notification m ight be displaye d. For e x am ple , the Database changed m e ssage shown he re is displaye d by
the mysql com m and-line utility upon successful database selection.
Tip
Using MySQL Query Browser In MySQ L Q uery Browser, double-click on any database listed in the Schem ata list to use it. You'll not actually see the USE com m and being issued, but you
will see the database selected (highlighted in bold) and the application title bar will display the nam e of the selected database.
Rem em ber, you m ust always USE a database before you can access any data in it.
Learning About Databases and Tables
But what if you don't k now the nam es of the available databases? And for that m atter, how are MySQ L Adm inistrator and MySQ L Q uery Browser able to display a list of available databases?
Inform ation about databases, tables, colum ns, users, privileges, and m ore, are stored within databases and tables them selves (yes, MySQ L uses MySQ L to store this inform ation). But these
internal tables are generally not accessed directly. Instead, the MySQ L SHOW com m and is used to display this inform ation (inform ation which MySQ L then ex tracts from those internal tables). Look at
the following ex am ple:
• Input
SHOW DATABASES;
• Output
+--------------------+
| Database |
+--------------------+
| information_schema |
| crashcourse |
| mysql |
| forta |
| coldfusion |
| flex |
| test |
+--------------------+
• A nalysis
SHOW DATABASES; re turns a list of available database s. Include d in this list m ight be database s use d by MySQ L inte rnally (such as mysql and information_schema in this e x am ple ). O f course , your own list of
databases m ight not look lik e those shown here.
To obtain a list of table s within a database , use SHOW TABLES;, as se e n he re :
• Input
SHOW TABLES;
• Output
+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers |
| orderitems |
| orders |
| products |
| productnotes |
| vendors |
+-----------------------+
• A nalysis
SHOW TABLES; re turns a list of available table s in the curre ntly se le cte d database .
SHOW can also be used to display a table's colum ns:
• Input
SHOW COLUMNS FROM customers;
• Output
+---------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | | |
| cust_address | char(50) | YES | | NULL | |
| cust_city | char(50) | YES | | NULL | |
| cust_state | char(5) | YES | | NULL | |
| cust_zip | char(10) | YES | | NULL | |
| cust_country | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
| cust_email | char(255) | YES | | NULL | |
+---------------+-----------+------+-----+---------+----------------+
• A nalysis
SHOW COLUMNS re quire s that a table nam e be spe cifie d (FROM customers in this e x am ple ), and re turns a row for e ach fie ld containing the fie ld nam e , its data type , whe the r NULL is allowe d, k e y inform ation,
de fa ult va lue , a nd e x tra inform a tion (such a s auto_increment for fie ld cust_id).
Note
What Is A uto Increment? Som e table colum ns need unique values. For ex am ple, order num bers, em ployee IDs, or (as in the ex am ple just seen) custom er IDs. Rather than have to
assign unique values m anually each tim e a row is added (and having to k eep track of what value was last used), MySQ L can autom atically assign the nex t available num ber for you each
tim e a row is added to a table. This functionality is k nown as auto increment. If it is needed, it m ust be part of the table definition used when the table is created using the CREATE
statem ent. W e'll look at CREATE in Chapter 21, "Creating and Manipulating Tables."
Tip
The D E S C R I B E Statement MySQ L supports the use of DESCRIBE a s a shortcut for SHOW COLUMNS FROM. In othe r words, DESCRIBE customers; is a shortcut for SHOW COLUMNS FROM customers;.
O ther SHOW statem ents are supported, too, including
SHOW STATUS, use d to display e x te nsive se rve r status inform ation
SHOW CREATE DATABASE a nd SHOW CREATE TABLE, use d to displa y the MySQ L sta te m e nts use d to cre a te spe cifie d da ta ba se s or ta ble s re spe ctive ly
SHOW GRANTS, use d to display se curity rights grante d to use rs (all use rs or a spe cific use r)
SHOW ERRORS a nd SHOW WARNINGS, use d to displa y se rve r e rror or wa rning m e ssa ge s
It is worthwhile to note that client applications use these sam e MySQ L com m ands as you've seen here. Applications that display interactive lists of databases and tables, that allow for the interactive
creation and editing of tables, that facilitate data entry and editing, or that allow for user account and rights m anagem ent, and m ore, all accom plish what they do using the sam e MySQ L com m ands
that you can ex ecute directly yourself.
Tip
Learning More A bout S H O W In the mysql com m and-line utility, e x e cute com m and HELP SHOW; to display a list of allowe d SHOW state m e nts.
Note
New To MySQL 5 MySQ L 5 supports a ne w INFORMATION_SCHEMA com m and that can be use d to obtain and filte r sche m a de tails.
Summary
In this chapter, you learned how to connect and log in to MySQ L; how to select databases using USE; and how to introspect MySQ L databases, tables, and internals using SHOW. Arm ed with this
k nowledge, you can now dig into the all im portant SELECT statem ent.
Chapter 4. Retrieving Data
In this chapter, you'll learn how to use the SELECT statement to retrieve one or more columns of data from a table.
The SELECT Statement
As ex plained in Chapter 1, "Understanding SQ L," SQ L statem ents are m ade up of plain English term s. These term s are called k eywords, and every SQ L statem ent is m ade up of one or m ore
k eywords. The SQ L statem ent you'll probably use m ost frequently is the SELECT statem ent. Its purpose is to retrieve inform ation from one or m ore tables.
To use SELECT to retrieve table data you m ust, at a m inim um , specify two pieces of inform ationwhat you want to select, and from where you want to select it.
Retrieving Individual Columns
W e 'll start with a sim ple SQ L SELECT state m e nt, as follows:
• Input
SELECT prod_name
FROM products;
• A nalysis
The pre vious state m e nt use s the SELECT state m e nt to re trie ve a single colum n calle d prod_name from the products table . The de sire d colum n nam e is spe cifie d right afte r the SELECT k e yword, and the FROM
k eyword specifies the nam e of the table from which to retrieve the data. The output from this statem ent is shown in the following:
• Output
+-----------------+
| prod_name |
+-----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Oil can |
| Fuses |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
| Bird seed |
| Carrots |
| Safe |
| Detonator |
| JetPack 1000 |
| JetPack 2000 |
+-----------------+
Note
Unsorted Data If you tried this query yourself, you m ight have discovered that the data was displayed in a different order than shown here. If this is the case, don't worryit is work ing
ex actly as it is supposed to. If query results are not ex plicitly sorted (we'll get to that in the nex t chapter), data will be returned in no order of any significance. It m ight be the order in
which the data was added to the table, but it m ight not. As long as your query returned the sam e num ber of rows, then it is work ing.
A sim ple SELECT statem ent lik e the one just shown returns all the rows in a table. Data is not filtered (so as to retrieve a subset of the results), nor is it sorted. W e'll discuss these topics in the nex t
few chapters.
Note
Terminating Statements Multiple SQ L statem ents m ust be separated by sem icolons (the ; character). MySQ L (lik e m ost DBMSs) does not require that a sem icolon be specified after
single statem ents. O f course, you can always add a sem icolon if you wish. It'll do no harm , even if it isn't needed.
If you are using the mysql com m and-line client, the sem icolon is always needed (as was ex plained in Chapter 2, "Introducing MySQ L").
Note
SQL Statements and Case It is im portant to note that SQ L state m e nts are not case se nsitive , so SELECT is the sam e as select, which is the sam e as Select. Many SQ L de ve lope rs find that
using uppercase for all SQ L k eywords and lowercase for colum n and table nam es m ak es code easier to read and debug.
However, be aware that while the SQ L language is not case sensitive, identifiers (the nam es of databases, tables, and colum ns) m ight be. In MySQ L 4.1 and earlier, identifiers were case
sensitive by default, and as of MySQ L 4.1.1, identifiers are not case sensitive by default.
As a best practice, pick a case convention, and use it consistently.
Tip
Use of White Space All ex tra white space within a SQ L statem ent is ignored when that statem ent is processed. SQ L statem ents can be specified on one long line or brok en up over m any
lines. Most SQ L developers find that break ing up statem ents over m ultiple lines m ak es them easier to read and debug.
Retrieving Multiple Columns
To retrieve m ultiple colum ns from a table, the sam e SELECT statem ent is used. The only difference is that m ultiple colum n nam es m ust be specified after the SELECT k eyword, and each colum n m ust
be separated by a com m a.
Tip
Take Care with Commas W hen selecting m ultiple colum ns, be sure to specify a com m a between each colum n nam e, but not after the last colum n nam e. Doing so will generate an error.
The following SELECT state m e nt re trie ve s thre e colum ns from the products table :
• Input
SELECT prod_id, prod_name, prod_price
FROM products;
• A nalysis
Just as in the prior ex am ple, this statem ent uses the SELECT statem ent to retrieve data from the products table. In this ex am ple, three colum n nam es are specified, each separated by a com m a. The
output from this statem ent is as follows:
• Output
+---------+----------------+------------+
| prod_id | prod_name | prod_price |
+---------+----------------+------------+
| ANV01 | .5 ton anvil | 5.99 |
| ANV02 | 1 ton anvil | 9.99 |
| ANV03 | 2 ton anvil | 14.99 |
| OL1 | Oil can | 8.99 |
| FU1 | Fuses | 3.42 |
| SLING | Sling | 4.49 |
| TNT1 | TNT (1 stick) | 2.50 |
| TNT2 | TNT (5 sticks) | 10.00 |
| FB | Bird seed | 10.00 |
| FC | Carrots | 2.50 |
| SAFE | Safe | 50.00 |
| DTNTR | Detonator | 13.00 |
| JP1000 | JetPack 1000 | 35.00 |
| JP2000 | JetPack 2000 | 55.00 |
+---------+----------------+------------+
Note
Presentation of Data SQ L statem ents typically return raw, unform atted data. Data form atting is a presentation issue, not a retrieval issue. Therefore, presentation (for ex am ple,
alignm ent and displaying the price values as currency am ounts with the currency sym bol and com m as) is typically specified in the application that displays the data. Actual raw retrieved
data (without application-provided form atting) is rarely displayed as is.
Retrieving All Columns
In addition to being able to specify desired colum ns (one or m ore, as seen previously), SELECT statem ents can also request all colum ns without having to list them individually. This is done using the
asterisk (*) wildcard character in lieu of actual colum n nam es, as follows:
• Input
SELECT *
FROM products;
• A nalysis
W hen a wildcard (*) is specified, all the colum ns in the table are returned. colum ns are in the order in which the colum ns appear in the table definition. However, this cannot be relied on because
changes to table schem as (adding and rem oving colum ns, for ex am ple) could cause ordering changes.
Caution
Using Wildcards As a rule, you are better off not using the * wildcard unless you really do need every colum n in the table. Even though use of wildcards m ight save you the tim e and
effort needed to list the desired colum ns ex plicitly, retrieving unnecessary colum ns usually slows down the perform ance of your retrieval and your application.
Tip
Retrieving Unknown Columns There is one big advantage to using wildcards. As you do not ex plicitly specify colum n nam es (because the asterisk retrieves every colum n), it is possible
to retrieve colum ns whose nam es are unk nown.
Retrieving Distinct Rows
As you have seen, SELECT returns all m atched rows. But what if you did not want every occurrence of every value? For ex am ple, suppose you wanted the vendor ID of all vendors with products in your
products table :
• Input
SELECT vend_id
FROM products;
• Output
+---------+
| vend_id |
+---------+
| 1001 |
| 1001 |
| 1001 |
| 1002 |
| 1002 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1005 |
| 1005 |
+---------+
The SELECT statem ent returned 14 rows (even though there are only 4 vendors in that list) because there are 14 products listed in the products table. So how could you retrieve a list of distinct values?
The solution is to use the DISTINCT k eyword which, as its nam e im plies, instructs MySQ L to only return distinct values.
• Input
SELECT DISTINCT vend_id
FROM products;
• A nalysis
SELECT DISTINCT vend_id te lls MySQ L to only re turn distinct (unique ) vend_id rows, and so only 4 rows are re turne d, as se e n in the following output. If use d, the DISTINCT k e yword m ust be place d dire ctly in
front of the colum n nam es.
• Output
+---------+
| vend_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+---------+
Caution
Can't Be Partially D I S T I N C T The DISTINCT k e yword a pplie s to a ll colum ns, not just the one it pre ce de s. If you we re to spe cify SELECT DISTINCT vend_id, prod_price, a ll rows would be re trie ve d
unless both of the specified colum ns were distinct.
Limiting Results
SELECT statem ents return all m atched rows, possibly every row in the specified table. To return just the first row or rows, use the LIMIT clause. Here is an ex am ple:
• Input
SELECT prod_name
FROM products
LIMIT 5;
• A nalysis
The previous statem ent uses the SELECT statem ent to retrieve a single colum n. LIMIT 5 instj34ructs MySQ L to return no m ore than five rows. The output from this statem ent is shown in the following:
• Output
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Oil can |
| Fuses |
+----------------+
To get the nex t five rows, specify both where to start and the num ber of rows to retrieve, lik e this:
• Input
SELECT prod_name
FROM products
LIMIT 5,5;
• A nalysis
LIMIT 5,5 instructs MySQ L to return five rows starting from row 5. The first num ber is where to start, and the second is the num ber of rows to retrieve. The output from this statem ent is shown in the
f o llo win g :
• Output
+----------------+
| prod_name |
+----------------+
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
| Bird seed |
| Carrots |
+----------------+
So, LIMIT with one value specified always starts from the first row, and the specified num ber is the num ber of rows to return. LIMIT with two values specified can start from wherever that first value tells
it to.
Caution
Row 0 The first row re trie ve d is row 0, not row 1. As such, LIMIT 1,1 will re trie ve the se cond row, not the first one .
Note
When There A ren't Enough Rows The num ber of rows to retrieve specified in LIMIT is the maximum num ber to retrieve. If there aren't enough rows (for ex am ple, you specified LIMIT 10,5,
but there were only 13 rows), MySQ L returns as m any as it can.
Tip
MySQL 5 L I M I T Syntax Does LIMIT 3,4 m ean 3 rows starting from row 4, or 4 rows starting from row 3? As you just learned, it m eans 4 rows starting from row 3, but it is a bit am biguous.
For this re ason, MySQ L 5 supports an alte rnative syntax for LIMIT. LIMIT 4 OFFSET 3 m e ans to ge t 4 rows starting from row 3, just lik e LIMIT 3,4.
Using Fully Qualified Table Names
The SQ L ex am ples used thus far have referred to colum ns by just the colum n nam es. It is also possible to refer to colum ns using fully qualified nam es (using both the table and colum n nam es).
Look at this exam ple:
• Input
SELECT products.prod_name
FROM products;
This SQ L statem ent is functionally identical to the very first one used in this chapter, but here a fully qualified colum n nam e is specified.
Table nam es, too, m ay be fully qualified, as seen here:
• Input
SELECT products.prod_name
FROM crashcourse.products;
O nce again, this state m e nt is functionally ide ntical to the one just use d (assum ing, of course , that the products table is inde e d in the crashcourse database ).
There are situations where fully qualified nam es are required, as we will see in later chapters. For now, it is worth noting this syntax so you'll k now what it is if you run across it.
Summary
In this chapter, you learned how to use the SQ L SELECT statem ent to retrieve a single table colum n, m ultiple table colum ns, and all table colum ns. Nex t you'll learn how to sort the retrieved data.
Chapter 5. Sorting Retrieved Data
In this chapter, you will learn how to use the SELECT statement's ORDER BY clause to sort retrieved data as needed.
Sorting Data
As you learned in the previous chapter, the following SQ L statem ent returns a single colum n from a database table. But look at the output. The data appears to be displayed in no particular order
at all.
• Input
SELECT prod_name
FROM products;
• Output
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Oil can |
| Fuses |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
| Bird seed |
| Carrots |
| Safe |
| Detonator |
| JetPack 1000 |
| JetPack 2000 |
+----------------+
Actually, the retrieved data is not displayed in a m ere random order. If unsorted, data is typically displayed in the order in which it appears in the underlying tables. This could be the order in which
the data was added to the tables initially. However, if data was subsequently updated or deleted, the order is affected by how MySQ L reuses reclaim ed storage space. The end result is that you
cannot (and should not) rely on the sort order if you do not ex plicitly control it. Relational database design theory states that the sequence of retrieved data cannot be assum ed to have significance
if ordering was not ex plicitly specified.
New Term
Clause SQ L statem ents are m ade up of clauses, som e required and som e optional. A clause usually consists of a k eyword and supplied data. An ex am ple of this is the SELECT statem ent's
FROM clause, which you saw in the previous chapter.
To e x plicitly sort data re trie ve d using a SELECT state m e nt, the ORDER BY clause is use d. ORDER BY tak e s the nam e of one or m ore colum ns by which to sort the output. Look at the following e x am ple :
• Input
SELECT prod_name
FROM products
ORDER BY prod_name;
• A nalysis
This statem ent is identical to the earlier statem ent, ex cept it also specifies an ORDER BY clause instructing MySQ L to sort the data alphabetically by the prod_name colum n. The results are as follows:
• Output
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Bird seed |
| Carrots |
| Detonator |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
| Safe |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
Tip
Sorting by Nonselected Columns More often than not, the colum ns used in an ORDER BY clause are ones that were selected for display. However, this is actually not required, and it is
perfectly legal to sort data by a colum n that is not retrieved.
Sorting by Multiple Columns
It is often necessary to sort data by m ore than one colum n. For ex am ple, if you are displaying an em ployee list, you m ight want to display it sorted by last nam e and first nam e (first sort by last
nam e, and then within each last nam e sort by first nam e). This would be useful if there are m ultiple em ployees with the sam e last nam e.
To sort by m ultiple colum ns, sim ply specify the colum n nam es separated by com m as (just as you do when you are selecting m ultiple colum ns).
The following code retrieves three colum ns and sorts the results by two of them first by price and then by nam e.
• Input
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
• Output
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| FC | 2.50 | Carrots |
| TNT1 | 2.50 | TNT (1 stick) |
| FU1 | 3.42 | Fuses |
| SLING | 4.49 | Sling |
| ANV01 | 5.99 | .5 ton anvil |
| OL1 | 8.99 | Oil can |
| ANV02 | 9.99 | 1 ton anvil |
| FB | 10.00 | Bird seed |
| TNT2 | 10.00 | TNT (5 sticks) |
| DTNTR | 13.00 | Detonator |
| ANV03 | 14.99 | 2 ton anvil |
| JP1000 | 35.00 | JetPack 1000 |
| SAFE | 50.00 | Safe |
| JP2000 | 55.00 | JetPack 2000 |
+---------+------------+----------------+
It is im portant to understand that when you are sorting by m ultiple colum ns, the sort sequence is ex actly as specified. In other words, using the output in the previous ex am ple, the products are
sorte d by the prod_name colum n only whe n m ultiple rows have the sam e prod_price value . If all the value s in the prod_price colum n had be e n unique , no data would have be e n sorte d by prod_name.
Specifying Sort Direction
Data sorting is not lim ited to ascending sort orders (from A to Z). Although this is the default sort order, the ORDER BY clause can also be used to sort in descending order (from Z to A). To sort by
descending order, the k eyword DESC m ust be specified.
The following ex am ple sorts the products by price in descending order (m ost ex pensive first):
• Input
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;
• Output
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| JP2000 | 55.00 | JetPack 2000 |
| SAFE | 50.00 | Safe |
| JP1000 | 35.00 | JetPack 1000 |
| ANV03 | 14.99 | 2 ton anvil |
| DTNTR | 13.00 | Detonator |
| TNT2 | 10.00 | TNT (5 sticks) |
| FB | 10.00 | Bird seed |
| ANV02 | 9.99 | 1 ton anvil |
| OL1 | 8.99 | Oil can |
| ANV01 | 5.99 | .5 ton anvil |
| SLING | 4.49 | Sling |
| FU1 | 3.42 | Fuses |
| FC | 2.50 | Carrots |
| TNT1 | 2.50 | TNT (1 stick) |
+---------+------------+----------------+
But what if you were to sort by m ultiple colum ns? The following ex am ple sorts the products in descending order (m ost ex pensive first), plus product nam e:
• Input
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name;
• Output
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| JP2000 | 55.00 | JetPack 2000 |
| SAFE | 50.00 | Safe |
| JP1000 | 35.00 | JetPack 1000 |
| ANV03 | 14.99 | 2 ton anvil |
| DTNTR | 13.00 | Detonator |
| FB | 10.00 | Bird seed |
| TNT2 | 10.00 | TNT (5 sticks) |
| ANV02 | 9.99 | 1 ton anvil |
| OL1 | 8.99 | Oil can |
| ANV01 | 5.99 | .5 ton anvil |
| SLING | 4.49 | Sling |
| FU1 | 3.42 | Fuses |
| FC | 2.50 | Carrots |
| TNT1 | 2.50 | TNT (1 stick) |
+---------+------------+----------------+
• A nalysis
The DESC k e yword only applie s to the colum n nam e that dire ctly pre ce de s it. In the pre vious e x am ple , DESC was spe cifie d for the prod_price colum n, but not for the prod_name colum n. The re fore , the
prod_price colum n is sorte d in de sce nding orde r, but the prod_name colum n (within e ach price ) is still sorte d in standard asce nding orde r.
Tip
Sorting Descending on Multiple Columns If you want to sort descending on m ultiple colum ns, be sure each colum n has its own DESC k eyword.
The opposite of DESC is ASC (for ascending), which m ay be specified to sort in ascending order. In practice, however, ASC is not usually used because ascending order is the default sequence (and is
assum e d if ne ithe r ASC nor DESC are spe cifie d).
Tip
Case Sensitivity and Sort Orders W hen you are sorting tex tual data, is A the sam e as a? And does a com e before B or after Z? These are not theoretical questions, and the answers
depend on how the database is set up.
In dictionary sort order, A is treated the sam e as a, and that is the default behavior in MySQ L (and indeed m ost DBMSs). However, adm inistrators can change this behavior if needed. (If
your database contains lots of foreign language characters, this m ight becom e necessary.)
The k ey here is that, if you do need an alternate sort order, you cannot accom plish it with a sim ple ORDER BY clause. You m ust contact your database adm inistrator.
Using a com bination of ORDER BY and LIMIT, it is possible to find the highest or lowest value in a colum n. The following ex am ple dem onstrates how to find the value of the m ost ex pensive item :
• Input
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
• Output
+------------+
| prod_price |
+------------+
| 55.00 |
+------------+
• A nalysis
prod_price DESC e nsure s that rows are re trie ve d from m ost to le ast e x pe nsive , and LIMIT 1 te lls MySQ L to just re turn one row.
Caution
Position of O R D E R B Y Clause W he n spe cifying an ORDER BY clause , be sure that it is afte r the FROM clause . If LIMIT is use d, it m ust com e after ORDER BY. Using clause s out of orde r will ge ne rate
an error m essage.
Summary
In this chapte r, you le arne d how to sort re trie ve d data using the SELECT state m e nt's ORDER BY clause . This clause , which m ust be the last in the SELECT state m e nt, can be use d to sort data on one or
m ore colum ns as needed.
Chapter 6. Filtering Data
In this chapter, you will learn how to use the SELECT statement's WHERE clause to specify search conditions.
Using the WHERE Clause
Database tables usually contain large am ounts of data, and you seldom need to retrieve all the rows in a table. More often than not, you'll want to ex tract a subset of the t able's data as needed
for specific operations or reports. Retrieving just the data you want involves specifying search criteria, also k nown as a filter condition.
W ithin a SELECT state m e nt, data is filte re d by spe cifying se arch crite ria in the WHERE clause . The WHERE clause is spe cifie d right afte r the table nam e (the FROM clause ) as follows:
• Input
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;
• A nalysis
This state m e nt re trie ve s two colum ns from the products table , but inste ad of re turning all rows, only rows with a prod_price value of 2.50 are re turne d, as follows:
• Output
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| Carrots | 2.50 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
This ex am ple uses a sim ple equality test: It check s to see if a colum n has a specified value, and it filters the data accordingly. But SQ L enables you to do m ore than just test for equality.
Tip
SQL Versus A pplication Filtering Data can also be filtered at the application level. To do this, the SQ L SELECT statem ent retrieves m ore data than is actually required for the client
application, and the client code loops through the returned data to ex tract just the needed rows.
As a rule, this practice is strongly discouraged. Databases are optim ized to perform filtering quick ly and efficiently. Mak ing the client application (or developm ent language) do the
database's job dram atically im pacts application perform ance and creates applications that cannot scale properly. In addition, if data is filtered at the client, the server has to send
unneeded data across the network connections, resulting in a waste of network bandwidth resources.
Caution
W H E R E Clause Position W he n using both ORDER BY and WHERE clause s, m ak e sure ORDER BY com e s afte r the WHERE; othe rwise an e rror will be ge ne rate d. (Se e C hapte r 5, "Sorting R e trie ve d
Data," for m ore inform ation on using ORDER BY.)
The WHERE Clause Operators
The first WHERE clause we look ed at tests for equalitydeterm ining if a colum n contains a specific value. MySQ L supports a whole range of conditional operators, som e of which are listed in Table 6.1.
Operator Table 6.1. WHERE Clause Operators
= D e script ion
<> Eq u a lity
!= No n e q u a lity
< No n e q u a lity
<= Less than
> Less than or equal to
>= Greater than
BETWEEN Greater than or equal to
Between two specified values
Checking Against a Single Value
W e have already seen an ex am ple of testing for equality. Here's one m ore:
• Input
SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';
• Output
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses | 3.42 |
+-----------+------------+
• A nalysis
C he ck ing for WHERE prod_name = 'fuses' re turne d a single row with a value of Fuses. By de fault, MySQ L is not case se nsitive whe n pe rform ing m atche s, and so fuses and Fuses m atche d.
Now look at a few ex am ples to dem onstrate the use of other operators.
This first ex am ple lists all products that cost less than 10:
• Input
SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;
• Output
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
This nex t statem ent retrieves all products costing 10 or less (resulting in two additional m atches):
• Input
SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 10;
• Output
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
Checking for Nonmatches
This nex t ex am ple lists all products not m ade by vendor 1003:
• Input
SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;
• Output
+---------+--------------+
| vend_id | prod_name |
+---------+--------------+
| 1001 | .5 ton anvil |
| 1001 | 1 ton anvil |
| 1001 | 2 ton anvil |
| 1002 | Fuses |
| 1005 | JetPack 1000 |
| 1005 | JetPack 2000 |
| 1002 | Oil can |
+---------+--------------+
Tip
When to Use Quotes If you look closely at the conditions used in the ex am ples' WHERE clauses, you will notice that som e values are enclosed within single quotes (such as 'fuses' used
previously), and others are not. The single quotes are used to delim it strings. If you are com paring a value against a colum n that is a string datatype, the delim iting quotes are required.
Q uotes are not used to delim it values used with num eric colum ns.
The following is the sam e ex am ple, ex cept this one uses the != operator instead of <>:
• Input
SELECT vend_id, prod_name
FROM products
WHERE vend_id != 1003;
Checking for a Range of Values
To check for a range of values, you can use the BETWEEN operator. Its syntax is a little different from other WHERE clause operators because it requires two values: the beginning and end of the range.
The BETWEEN operator can be used, for ex am ple, to check for all products that cost between 5 and 10 or for all dates that fall between specified start and end dates.
The following ex am ple dem onstrates the use of the BETWEEN operator by retrieving all products with a price between 5 and 10:
• Input
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
• Output
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Oil can | 8.99 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
• A nalysis
As se e n in this e x am ple , whe n BETWEEN is use d, two value s m ust be spe cifie dthe low e nd and high e nd of the de sire d range . The two value s m ust also be se parate d by the AND k e yword. BETWEEN
m atches all the values in the range, including the specified range start and end values.
Checking for No Value
W hen a table is created, the table designer can specify whether individual colum ns can contain no value. W hen a colum n contains no value, it is said to contain a NULL value.
New Term
NULL No value, as opposed to a field containing 0, or an em pty string, or just spaces.
The SELECT state m e nt has a spe cial WHERE clause that can be use d to che ck for colum ns with NULL value sthe IS NULL clause . The syntax look s lik e this:
• Input
SELECT prod_name
FROM products
WHERE prod_price IS NULL;
This state m e nt re turns a list of all products that have no price (an e m pty prod_price fie ld, not a price of 0), and be cause the re are none , no data is re turne d. The customers table , howe ve r, doe s
contain colum ns with NULL value sthe cust_email colum n contains NULL if a custom e r has no e m ail addre ss on file :
• Input
SELECT cust_id
FROM customers
WHERE cust_email IS NULL;
• Output
+---------+
| cust_id |
+---------+
| 10002 |
| 10005 |
+---------+
Caution
N U L L and Nonmatches You m ight ex pect that when you filter to select all rows that do not have a particular value, rows with a NULL will be returned. But they will not. Because of the special
m eaning of unk nown, the database does not k now whether they m atch, and so they are not returned when filtering for m atches or when filtering for nonm atches.
W hen filtering data, m ak e sure to verify that the rows with a NULL in the filtered colum n are really present in the returned data.
Summary
In this chapter, you learned how to filter returned data using the SELECT statem ent's WHERE clause. You learned how to test for equality, nonequality, greater than and less than, value ranges, and NULL
va lu e s .
Chapter 7. Advanced Data Filtering
In this chapter, you'll learn how to combine WHERE clauses to create powerful and sophisticated search conditions. You'll also learn how to use the NOT and IN operators.
91
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
