Ben Forta - MariaDB Crash Course

Upper Saddle River, NJ Boston Indianapolis San Francisco
New York Toronto Montreal London Munich Paris Madrid
Cape Town Sydney Tokyo Singapore Mexico City

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and the publisher was aware of a trademark claim, the designations have been printed with initial capital letters or in all capitals.

The author and publisher have taken care in the preparation of this book, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or consequential damages in connection with or arising out of the use of the information or programs contained herein.

The publisher offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales, which may include electronic versions and/or custom covers and content particular to your business, training goals, marketing focus, and branding interests. For more information, please contact:

U.S. Corporate and Government Sales
(800) 382-3419

For sales outside the United States, please contact:

International Sales

Visit us on the Web: informit.com/aw

Editor-in-Chief
Mark Taub

Acquisitions Editor
Mark Taber

Managing Editor
Kristy Hart

Project Editors
Elaine Wiley

Jovana San Nicolas-Shirley

Copy Editor
Geneil Breeze

Indexer
Erika Millen

Proofreader
Leslie Joseph

Publishing Coordinator
Vanessa Evans

Book Designer
Gary Adair

Compositor
Gloria Schurick

Library of Congress Cataloging-in-Publication Data

Forta, Ben.
MariaDB crash course / Ben Forta.
p. cm.
Includes index.
ISBN 978-0-321-79994-4 (pbk.)
1. MariaDB. 2. Database management. 3. Client/server computing. I. Title.
QA76.9.D3F663 2012
004.36--dc23
2011023506

Copyright 2012 Pearson Education, Inc.

All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. To obtain permission to use material from this work, please submit a written request to Pearson Education, Inc., Permissions Department, One Lake Street, Upper Saddle River, New Jersey 07458, or you may fax your request to (201) 236-3290.

ISBN-13: 978-0-321-79994-4
ISBN-10: 0-321-79994-1

Text printed in the United States on recycled paper at R.R. Donnelley in Crawfordsville, Indiana.

First printing September 2011

As the creator of MariaDB (and MySQL), I am thrilled to see the first MariaDB book in print. I am equally thrilled that Ben Forta wrote it. Ben has a gift for presenting complex topics (and really understanding SQL can be complex) in an easy-to-understand way. MariaDB Crash Course is an easy read and goes from explaining the basics to the very complex (including joins, regular expressions, and triggers) simply and without painful effort. I recommend this book to anyone new to SQL who wants to quickly learn how to get the best out of MariaDB.

Michael Monty Widenius
Creator of MariaDB and MySQL

Id like to thank the folks at Addison-Wesley for once again granting me the flexibility and freedom to build this book as I saw fit. Special thanks to Mark Taber for helping turn this one around in record time, and for his guidance into what this series is evolving into.

Thanks to project editor Elaine Wiley for keeping the project moving and me on schedule, no easy task.

Thanks to Monty Widenius, (creator of MariaDB and MySQL), Daniel Bartholomew, and Colin Charles for their thorough technical review and feedback.

And finally, this book was written in response to an unsolicited request by Monty Widenius. Monty is the driving force behind some of the most successful database projects in history, and yet he still took the time to review the manuscript, provide feedback, and write a much-appreciated foreword and recommendation. Thank you for your time and support, Monty. I hope this title lives up to your expectations.

Ben Forta is Adobe Systems Director of Developer Relations and has more than 20 years experience in the computer industry in product development, support, training, and product marketing. Ben is the author of the best-selling Sams Teach Yourself SQL in 10 Minutes (now in its third edition, and translated into more than a dozen languages), spinoff titles on MySQL and SQL Server T-SQL, ColdFusion Web Application Construction Kit and Advanced ColdFusion Application Development (both published by Adobe Press), Sams Teach Yourself Regular Expressions in 10 Minutes, as well as books on Flash, Java, Windows, and other subjects. He has extensive experience in database design and development, has implemented databases for several highly successful commercial software programs and Web sites, and is a frequent lecturer and columnist on Internet and database technologies. Ben lives in Oak Park, Michigan, with his wife, Marcy, and their seven children. Ben welcomes your e-mail at .

MariaDB is an offshoot of MySQL, one of the most popular database management systems in the world. From small development projects to some of the best-known and most prestigious sites on the Web, MySQL has proven itself to be a solid, reliable, fast, and trusted solution to all sorts of data storage needs.

In 2008, MySQL was acquired by Sun Microsystems, which was in turn acquired by Oracle Corporation in 2010. While the initial acquisition by Sun was hailed by many in the MySQL community as exactly what the project needed, that sentiment did not last, and the subsequent acquisition by Oracle was unfortunately met with far lower expectations. Many of MySQLs developers left Sun and Oracle to work on new projects. Among them was Michael Monty Widenius, creator of MySQL and one of the projects longtime technical leads.

Monty and his team created a fork (offshoot) of the MySQL codebase and named his new DBMS MariaDB. The stated goals for the new MariaDB DBMS include

Create a DBMS that is so compatible with MySQL that it could be used as a drop-in replacement (you could uninstall MySQL, install MariaDB, and your programs should continue to run as is). This is accomplished by building MariaDB on the MySQL codebase.

Improve the source code to make MariaDB far more reliable and stable.

Add features (and community contributions) at a faster rate.

Develop a new underlying database engine (dont worry if that sounds obscure for now) named Aria to improve performance and reliability.

This book is based on my best-selling Sams Teach Yourself SQL in 10 Minutes. That book has become one of the most-used SQL tutorials in the world, with an emphasis on teaching what you really need to knowmethodically, systematically, and simply. But as popular and as successful as that book is, it does have some limitations:

In covering all the major DBMSs, coverage of DBMS-specific features and functionality had to be kept to a minimum.