SQL for MySQL Developers: A Comprehensive Tutorial and Reference

Product Description

The Most Complete and Practical Guide to MySQL Version 5’s Powerful SQL Dialect



MySQL version 5 offers a SQL dialect with immense power. In SQL for MySQL Developers, Rick F. van der Lans helps you master this version ofSQL and take advantage of its full potential. Using case study examplesand hands-on exercises, van der Lans illuminates every key concept,technique, and statement–including advanced features that make iteasier to create even the most complex statements and programs.



Drawing on decades of experience as an SQL standards team member and enterprise consultant, he reveals exactly why MySQL’s dialect works as it does–and how to get the most out of it. You’ll gain powerful insight into everything from basic queries to stored procedures, transactions to data security.



Whether you’re a programmer, Web developer, analyst, DBA, or database user, this book can take you from “apprentice” to true SQL expert. If you’ve used SQL in older versions of MySQL, you’ll become dramatically more effective–and if you’re migrating from other database platforms, you’ll gain practical mastery fast.



Coverage includes

*
Writing queries, including joins, functions, and subqueries
*
Updating data
*
Creating tables, views, and other database objects
*
Specifying keys and other integrity constraints
*
Improving efficiency with indexes
*
Enforcing security via passwords and privileges
*
Embedding SQL statements within PHP programs
*
Building stored procedures and triggers
*
Using transactions, locking, rollback, and isolation levels
*
Utilizing MySQL’s catalog

All of the book’s sample programs are available for download from www.r20.nl.



About the Author

Rick F. van der Lans is author of the classic Introduction to SQL, the definitive SQL guide that database developers have relied on for more than 20 years. He is a consultant, author, and lecturer specializing in database technology, development tools, data warehousing, and XML. As managing director of the Netherlands-based R20/Consultancy, he has advised many large companies on defining their IT architectures. He chairs the European Meta Data Conference, and writes columns for several magazines.



Contents

About the Author

Preface

PART I Introduction

CHAPTER 1 Introduction to MySQL

CHAPTER 2 The Tennis Club Sample Database

CHAPTER 3 Installing the Software

CHAPTER 4 SQL in a Nutshell

PART II Querying and Updating Data

CHAPTER 5 SELECT Statement: Common Elements

CHAPTER 6 SELECT Statements, Table Expressions, and Subqueries

CHAPTER 7 SELECT Statement:The FROM Clause

CHAPTER 8 SELECT Statement: The WHERE Clause

CHAPTER 9 SELECT Statement: SELECT Clause and Aggregation Functions

CHAPTER 10 SELECT Statement: The GROUP BY Clause

CHAPTER 11 SELECT Statement: The HAVING Clause

CHAPTER 12 SELECT Statement: The ORDER BY Clause

CHAPTER 13 SELECT Statement: The LIMIT Clause

CHAPTER 14 Combining Table Expressions

CHAPTER 15 The User Variable and the SET Statement

CHAPTER 16 The HANDLER Statement

CHAPTER 17 Updating Tables

CHAPTER 18 Loading and Unloading Data

CHAPTER 19 Working with XML Documents

PART III Creating Database Objects

CHAPTER 20 Creating Tables

CHAPTER 21 Specifying Integrity Constraints

CHAPTER 22 Character Sets and Collations

CHAPTER 23 The ENUM and SET Types

CHAPTER 24 Changing and Dropping Tables

CHAPTER 25 Using Indexes

CHAPTER 26 Views

CHAPTER 27 Creating Databases

CHAPTER 28 Users and Data Security

CHAPTER 29 Statements for Table Maintenance

CHAPTER 30 The SHOW, DESCRIBE, and HELP Statements

PART IV Procedural Database Objects

CHAPTER 31 Stored Procedures

CHAPTER 32 Stored Functions

CHAPTER 33 Triggers

CHAPTER 34 Events

PART V Programming with SQL

CHAPTER 35 MySQL and PHP

CHAPTER 36 Dynamic SQL with Prepared Statement

CHAPTER 37 Transactions and Multiuser Usage

APPENDIX A Syntax of SQL

APPENDIX B Scalar Functions

APPENDIX C System Variables

APPENDIX D Bibliography

Index


Product Details

* Amazon Sales Rank: #699120 in Books
* Published on: 2007-04-30
* Format: Illustrated
* Original language: English
* Number of items: 1
* Binding: Paperback
* 1032 pages

Editorial Reviews

About the Author

Rick F. van der Lans is author of the classic Introduction to SQL, the definitive SQL guide that database developers have relied on for more than 20 years. He is a consultant, author, and lecturer specializing in database technology, development tools, data warehousing, and XML. As managing director of the Netherlands-based R20/Consultancy, he has advised many large companies on defining their IT architectures. He chairs the European Meta Data Conference, and writes columns for several magazines.

Excerpt. © Reprinted by permission. All rights reserved.
SQL for MySQL Developers
Preface
Introduction

Many books have been written about MySQL, the best-known open source database server. Then why another book? Most books about MySQL discuss a wide variety of topics, such as the installation of MySQL, using MySQL from PHP, and security. As a result, each topic cannot be explained in detail, and many questions of readers cannot be answered. This book focuses on one aspect of MySQL: the language that drives MySQL, which is SQL (Structured Query Language). Every developer working with MySQL should master this language thoroughly.

Especially in the more recent versions, SQL has been extended considerably. Unfortunately, many developers still limit themselves to those features that were available in the first versions. Not all the features of MySQL are fully used, which means that the product is not employed in the best way possible. The result is that complex statements and programs must be built needlessly. When you buy a house, you also do not restrict yourself to 20 percent of the rooms, do you? That is why this book contains a complete and detailed description of the SQL dialect as implemented in MySQL version 5.0.18. It should be seen primarily as a textbook rather than as a reference book; it will teach you the language, and you can complete the exercises to test your knowledge. After reading this book, you should be familiar with all the statements and features and some idiosyncrasies of MySQL's SQL, and you should be able to use it efficiently and effectively.
Topics

This book is completely devoted to the SQL dialect as implemented in MySQL. It discusses every aspect of the language thoroughly and critically. These aspects of SQL among others, are covered:

*

Querying data (joins, functions, and subqueries)
*

Updating data
*

Creating tables and views
*

Specifying primary and foreign keys and other integrity constraints
*

Using indexes
*

Considering data security
*

Developing stored procedures and triggers
*

Developing programs with PHP
*

Working with transactions
*

Using the catalog

For Whom Is This Book Intended?

We recommend this book on MySQL's SQL dialect to those who want to use the full power of MySQL effectively and efficiently in practice. This book is therefore suitable for the following groups of people:

*

Developers who develop applications with the help of MySQL
*

Database managers who have to know the possibilities and impossibilities of SQL
*

Students in higher education, including those in technical colleges, polytechnics, universities, and sixth-form colleges
*

Designers, analysts, and consultants who have to deal, directly or indirectly, with MySQL and/or SQL and want to know about its possibilities and impossibilities
*

Home students who are interested in MySQL and/or SQL
*

Users who have the authority to use SQL to query the MySQL database of the company or institute for which they are working
*

Web site developers who are creating web sites with the help of MySQL and languages such as PHP and Python
*

IT hobbyists who are interested in MySQL and want to develop an SQL application using MySQL themselves

A Practical Book

This book should be seen primarily as a textbook and less as a reference work. To this end, it contains many examples and exercises (with answers). Do not ignore the exercises. Experience shows that you will learn the language more thoroughly and more quickly by practicing often and doing many exercises.
The Book's Web Site

When you leaf through the book, you will come across numerous SQL statements. Sometimes these are examples, and sometimes they are answers to questions. After you have installed MySQL, you can run through these statements to see whether they work and see their effects. You could type in all the statements again like a real Spartan, but you can also make life easy for yourself by downloading all the statements from the Internet. A special web site for this book, http://www.r20.nl, includes all the SQL statements.

We also have used the web site for these purposes:

*

The web site includes an installation process and instructions for MySQL. You will find useful tips for installing MySQL under Windows. The site also explains the installation process of the example database.
*

If an error is found in the book, the web site will rectify the mistake.
*

Reader comments that could be of interest to others will be added periodically to site.
*

We even will consider making additional chapters available on the web site in the future.

Therefore, keep an eye on this web site.
Prerequisite Knowledge

Some general knowledge of prog