The Most Complete and Practical Guide to MySQL Version 5rs"s Powerful SQL Dialect MySQL version 5 offers a SQL dialect with immense power. InSQL 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 statementincluding 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 MySQLrs"s dialect works as it doesand how to get the most out of it. Yours"ll gain powerful insight into everything from basic queries to stored procedures, transactions to data security. Whether yours"re a programmer, Web developer, analyst, DBA, or database user, this book can take you from "apprentice" to true SQL expert. If yours"ve used SQL in older versions of MySQL, yours"ll become dramatically more effectiveand if yours"re migrating from other database platforms, yours"ll gain practical masteryfast. 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 MySQLrs"s catalog All of the bookrs"s sample programs are available for download from www.r20.nl. About the Author RickF. van der Lansis author of the classicIntroduction 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 xix Preface xx PART I Introduction 1 CHAPTER 1 Introduction to MySQL 3 CHAPTER 2 The Tennis Club Sample Database 29 CHAPTER 3 Installing the Software 37 CHAPTER 4 SQL in a Nutshell 41 PART II Querying and Updating Data 71 CHAPTER 5 SELECT Statement: Common Elements 73 CHAPTER 6 SELECT Statements, Table Expressions, and Subqueries 145

Author Biography

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.

About the Author	p. xix
Preface	p. xx
Introduction	p. 1
Introduction to MySQL	p. 3
The Tennis Club Sample Database	p. 29
Installing the Software	p. 37
SQL in a Nutshell	p. 41
Querying and Updating Data	p. 71
Select Statement
Common Elements	p. 73
Select Statements
Table Expressions, and Subqueries	p. 145
Select Statement
The From Clause	p. 171
Select Statement
The Where Clause	p. 213
Select Statement
Select Clause and Aggregation Functions	p. 315
Select Statement
The Group BY Clause	p. 349
Select Statement
The Having Clause	p. 375
Select Statement
The Order By Clause	p. 383
Select Statement
The Limit Clause	p. 395
Combining Table Expressions	p. 409
The User Variable and the SET Statement	p. 421
The HANDLER Statement	p. 429
Updating Tables	p. 437
Loading and Unloading Data	p. 461
Working with XML Documents	p. 471
Creating Database Objects	p. 491
Creating Tables	p. 493
Specifying Integrity Constraints	p. 539
Character Sets and Collations	p. 561
The ENUM and SET Types	p. 577
Changing and Dropping Tables	p. 591
Using Indexes	p. 603
Views	p. 631
Creating Databases	p. 653
Users and Data Security	p. 659
Statements for Table Maintenance	p. 683
The Show, Describe, and HELP Statements	p. 693
Procedural Database Objects	p. 701
Stored Procedures	p. 703
Stored Functions	p. 745
Triggers	p. 755
Events	p. 767
Programming with SQL	p. 783
MySQL and PHP	p. 785
Dynamic SQL with Prepared Statement	p. 807
Transactions and Multiuser Usage	p. 815
Syntax of SQL	p. 839
Scalar Functions	p. 903
System Variables	p. 953
Bibliography	p. 963
Index	p. 967
Table of Contents provided by Publisher. All Rights Reserved.

Supplemental Materials

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Excerpts

= 0) {slash = '\\';} else {slash = '/';}openLoc = figLoc.substring(0, figLoc.lastIndexOf(slash) + 1);while (pPage.substring(0,3) == '../') {openLoc = openLoc.substring(0, openLoc.lastIndexOf(slash, openLoc.length - 2)+ 1);pPage = pPage.substring(3, pPage.length + 1);}popUpWin =window.open('','popWin','resizable=1,scrollbars=1,location=0,toolbar=0,width=525,height=394');figDoc = popUpWin.document;zhtm= ' ' + pPage + ' ';zhtm += ' ';zhtm += ' ';zhtm += ' ';zhtm += '' + pPage.substring(pPage.lastIndexOf('/') + 1, pPage.length) + '';zhtm += ' ';figDoc.write(zhtm);figDoc.close();}// modified 3.1.99 RWE v4.1 --> SQL for MySQL Developers SQL for MySQL Developers Preface IntroductionMany 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 isSQL(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. TopicsThis 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 dataCreating tables and viewsSpecifying primary and foreign keys and other integrity constraintsUsing indexesConsidering data security