SQL for MySQL Developers : A Comprehensive Tutorial and Reference (Adobe Reader)

**The Most Complete and Practical Guide to MySQL Version 5вЂTMs 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вЂTMs dialect works as it does–and how to get the most out of it. YouвЂTMll gain powerful insight into everything from basic queries to stored procedures, transactions to data security. В Whether youвЂTMre a programmer, Web developer, analyst, DBA, or database user, this book can take you from “apprentice” to true SQL expert. If youвЂTMve used SQL in older versions of MySQL, youвЂTMll become dramatically more effective–and if youвЂTMre migrating from other database platforms, youвЂTMll 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вЂTMs catalog All of the bookвЂTMs 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 В В В SQL for MySQL developers : a comprehensive tutorial and reference......Page 1 Contents......Page 8 PART I: Introduction......Page 28 1.1 Introduction......Page 30 1.2 Database, Database Server, and Database Language......Page 31 1.3 The Relational Model......Page 33 1.4 What Is SQL?......Page 38 1.5 The History of SQL......Page 43 1.6 From Monolithic via Client/Server to the Internet......Page 45 1.7 Standardization of SQL......Page 48 1.8 What Is Open Source Software?......Page 52 1.9 The History of MySQL......Page 53 1.10 The Structure of This Book......Page 54 2.2 Description of the Tennis Club......Page 56 2.3 The Contents of the Tables......Page 60 2.4 Integrity Constraints......Page 62 3.2 Downloading MySQL......Page 64 3.5 Downloading SQL Statements from the Web Site......Page 65 3.6 Ready?......Page 66 4.2 Logging On to the MySQL Database Server......Page 68 4.3 Creating New SQL Users......Page 70 4.5 Selecting the Current Database......Page 72 4.6 Creating Tables......Page 73 4.7 Populating Tables with Data......Page 75 4.8 Querying Tables......Page 76 4.9 Updating and Deleting Rows......Page 79 4.10 Optimizing Query Processing with Indexes......Page 81 4.11 Views......Page 82 4.13 Deleting Database Objects......Page 84 4.14 System Variables......Page 85 4.15 Grouping of SQL Statements......Page 86 4.16 The Catalog Tables......Page 87 4.17 Retrieving Errors and Warnings......Page 95 4.18 Definitions of SQL Statements......Page 96 PART II: Querying and Updating Data......Page 98 5.1 Introduction......Page 100 5.2 Literals and Their Data Types......Page 101 5.3 Expressions......Page 115 5.4 Assigning Names to Result Columns......Page 119 5.5 The Column Specification......Page 121 5.6 The User Variable and the SET Statement......Page 122 5.7 The System Variable......Page 124 5.8 The Case Expression......Page 128 5.9 The Scalar Expression Between Brackets......Page 133 5.10 The Scalar Function......Page 134 5.11 Casting of Expressions......Page 138 5.12 The Null Value as an Expression......Page 141 5.13 The Compound Scalar Expression......Page 142 5.14 The Aggregation Function and the Scalar Subquery......Page 163 5.15 The Row Expression......Page 164 5.16 The Table Expression......Page 166 5.17 Answers......Page 167 6.2 The Definition of the SELECT Statement......Page 172 6.3 Processing the Clauses in a Select Block......Page 177 6.4 Possible Forms of a Table Expression......Page 183 6.5 What Is a SELECT Statement?......Page 186 6.6 What Is a Subquery?......Page 187 6.7 Answers......Page 193 7.2 Table Specifications in the FROM Clause......Page 198 7.3 Again, the Column Specification......Page 200 7.4 Multiple Table Specifications in the FROM Clause......Page 201 7.5 Pseudonyms for Table Names......Page 205 7.6 Various Examples of Joins......Page 206 7.7 Mandatory Use of Pseudonyms......Page 210 7.9 Explicit Joins in the FROM Clause......Page 212 7.10 Outer Joins......Page 216 7.11 The Natural Join......Page 222 7.12 Additional Conditions in the Join Condition......Page 223 7.14 Replacing Join Conditions with USING......Page 226 7.15 The FROM Clause with Table Expressions......Page 227 7.16 Answers......Page 235 8.1 Introduction......Page 240 8.2 Conditions Using Comparison Operators......Page 242 8.3 Comparison Operators with Subqueries......Page 249 8.4 Comparison Operators with Correlated Subqueries......Page 254 8.5 Conditions Without a Comparison Operator......Page 256 8.6 Conditions Coupled with AND, OR, XOR, and NOT......Page 258 8.7 The IN Operator with Expression List......Page 262 8.8 The IN Operator with Subquery......Page 268 8.9 The BETWEEN Operator......Page 277 8.10 The LIKE Operator......Page 279 8.11 The REGEXP Operator......Page 282 8.12 The MATCH Operator......Page 291 8.13 The IS NULL Operator......Page 303 8.14 The EXISTS Operator......Page 305 8.15 The ALL and ANY Operators......Page 308 8.16 Scope of Columns in Subqueries......Page 316 8.17 More Examples with Correlated Subqueries......Page 321 8.18 Conditions with Negation......Page 326 8.19 Answers......Page 329 9.1 Introduction......Page 342 9.2 Selecting All Columns (*)......Page 343 9.3 Expressions in the SELECT Clause......Page 344 9.4 Removing Duplicate Rows with DISTINCT......Page 345 9.5 When Are Two Rows Equal?......Page 348 9.6 More Select Options......Page 350 9.7 An Introduction to Aggregation Functions......Page 351 9.8 COUNT Function......Page 354 9.9 MAX and MIN Functions......Page 358 9.10 The SUM and AVG Function......Page 363 9.11 The VARIANCE and STDDEV Functions......Page 368 9.13 The BIT_AND, BIT_OR, and BIT_XOR Functions......Page 370 9.14 Answers......Page 372 10.1 Introduction......Page 376 10.2 Grouping on One Column......Page 377 10.3 Grouping on Two or More Columns......Page 380 10.4 Grouping on Expressions......Page 383 10.5 Grouping of Null Values......Page 384 10.6 Grouping with Sorting......Page 385 10.7 General Rules for the GROUP BY Clause......Page 386 10.8 The GROUP_CONCAT Function......Page 389 10.9 Complex Examples with GROUP BY......Page 390 10.10 Grouping with WITH ROLLUP......Page 396 10.11 Answers......Page 399 11.1 Introduction......Page 402 11.2 Examples of the HAVING Clause......Page 403 11.3 A HAVING Clause but not a GROUP BY Clause......Page 405 11.4 General Rule for the HAVING Clause......Page 406 11.5 Answers......Page 408 12.2 Sorting on Column Names......Page 410 12.3 Sorting on Expressions......Page 412 12.4 Sorting with Sequence Numbers......Page 414 12.5 Sorting in Ascending and Descending Order......Page 416 12.6 Sorting Null Values......Page 419 12.7 Answers......Page 420 13.1 Introduction......Page 422 13.2 Get the Top......Page 425 13.3 Subqueries with a LIMIT Clause......Page 429 13.4 Limit with an Offset......Page 431 13.5 The Select Option SQL_CALC_FOUND_ROWS......Page 432 13.6 Answers......Page 433 14.1 Introduction......Page 436 14.2 Combining with UNION......Page 437 14.3 Rules for Using UNION......Page 440 14.4 Keeping Duplicate Rows......Page 443 14.5 Set Operators and the Null Value......Page 444 14.6 Answers......Page 445 15.2 Defining Variables with the SET Statement......Page 448 15.3 Defining Variables with the SELECT Statement......Page 450 15.4 Application Areas for User Variables......Page 452 15.5 Life Span of User Variables......Page 453 15.7 Answers......Page 455 16.2 A Simple Example of the HANDLER Statement......Page 456 16.3 Opening a Handler......Page 457 16.4 Browsing the Rows of a Handler......Page 458 16.6 Answers......Page 462 17.2 Inserting New Rows......Page 464 17.3 Populating a Table with Rows from Another Table......Page 469 17.4 Updating Values in Rows......Page 471 17.5 Updating Values in Multiple Tables......Page 477 17.6 Substituting Existing Rows......Page 479 17.7 Deleting Rows from a Table......Page 481 17.8 Deleting Rows from Multiple Tables......Page 483 17.10 Answers......Page 485 18.2 Unloading Data......Page 488 18.3 Loading Data......Page 492 19.1 XML in a Nutshell......Page 498 19.2 Storing XML Documents......Page 500 19.3 Querying XML Documents......Page 503 19.4 Querying Using Positions......Page 511 19.5 The Extended Notation of XPath......Page 513 19.6 XPath Expressions with Conditions......Page 515 19.7 Changing XML Documents......Page 516 PART III: Creating Database Objects......Page 518 20.2 Creating New Tables......Page 520 20.3 Data Types of Columns......Page 523 20.4 Adding Data Type Options......Page 535 20.5 Creating Temporary Tables......Page 541 20.6 What If the Table Already Exists?......Page 542 20.7 Copying Tables......Page 543 20.8 Naming Tables and Columns......Page 548 20.9 Column Options: Default and Comment......Page 549 20.10 Table Options......Page 551 20.11 The CSV Storage Engine......Page 559 20.12 Tables and the Catalog......Page 561 20.13 Answers......Page 564 21.1 Introduction......Page 566 21.2 Primary Keys......Page 568 21.3 Alternate Keys......Page 571 21.4 Foreign Keys......Page 573 21.5 The Referencing Action......Page 577 21.6 Check Integrity Constraints......Page 580 21.7 Naming Integrity Constraints......Page 583 21.9 Integrity Constraints and the Catalog......Page 584 21.10 Answers......Page 585 22.1 Introduction......Page 588 22.2 Available Character Sets and Collations......Page 590 22.3 Assigning Character Sets to Columns......Page 591 22.4 Assigning Collations to Columns......Page 593 22.5 Expressions with Character Sets and Collations......Page 595 22.6 Sorting and Grouping with Collations......Page 598 22.7 The Coercibility of Expressions......Page 600 22.8 Related System Variables......Page 601 22.10 Answers......Page 603 23.1 Introduction......Page 604 23.2 The ENUM Data Type......Page 605 23.3 The SET Data Type......Page 609 23.4 Answers......Page 616 24.2 Deleting Entire Tables......Page 618 24.4 Changing the Table Structure......Page 620 24.5 Changing Columns......Page 622 24.6 Changing Integrity Constraints......Page 626 24.7 Answers......Page 629 25.1 Introduction......Page 630 25.2 Rows, Tables, and Files......Page 631 25.3 How Does an Index Work?......Page 632 25.4 Processing a SELECT Statement: The Steps......Page 637 25.5 Creating Indexes......Page 641 25.6 Defining Indexes Together with the Tables......Page 644 25.7 Dropping Indexes......Page 645 25.8 Indexes and Primary Keys......Page 646 25.9 The Big PLAYERS_XXL Table......Page 647 25.10 Choosing Columns for Indexes......Page 649 25.11 Indexes and the Catalog......Page 654 25.12 Answers......Page 657 26.2 Creating Views......Page 658 26.3 The Column Names of Views......Page 662 26.4 Updating Views: WITH CHECK OPTION......Page 663 26.5 Options of Views......Page 665 26.6 Deleting Views......Page 666 26.7 Views and the Catalog......Page 667 26.8 Restrictions on Updating Views......Page 668 26.9 Processing View Statements......Page 669 26.10 Application Areas for Views......Page 672 26.11 Answers......Page 677 27.2 Databases and the Catalog......Page 680 27.3 Creating Databases......Page 681 27.4 Changing Databases......Page 682 27.5 Dropping Databases......Page 683 28.1 Introduction......Page 686 28.2 Adding and Removing Users......Page 687 28.3 Changing the Names of Users......Page 689 28.4 Changing Passwords......Page 690 28.5 Granting Table and Column Privileges......Page 691 28.6 Granting Database Privileges......Page 694 28.7 Granting User Privileges......Page 697 28.8 Passing on Privileges: WITH GRANT OPTION......Page 700 28.9 Restricting Privileges......Page 701 28.10 Recording Privileges in the Catalog......Page 702 28.11 Revoking Privileges......Page 704 28.12 Security of and Through Views......Page 707 28.13 Answers......Page 709 29.1 Introduction......Page 710 29.2 The ANALYZE TABLE Statement......Page 711 29.3 The CHECKSUM TABLE Statement......Page 712 29.4 The OPTIMIZE TABLE Statement......Page 713 29.5 The CHECK TABLE Statement......Page 714 29.6 The REPAIR TABLE Statement......Page 716 29.7 The BACKUP TABLE Statement......Page 717 29.8 The RESTORE TABLE Statement......Page 718 30.2 Overview of SHOW Statements......Page 720 30.3 Additional SHOW Statements......Page 725 30.5 The HELP Statement......Page 726 PART IV: Procedural Database Objects......Page 728 31.1 Introduction......Page 730 31.2 An Example of a Stored Procedure......Page 731 31.3 The Parameters of a Stored Procedure......Page 733 31.4 The Body of a Stored Procedure......Page 734 31.5 Local Variables......Page 736 31.7 Flow-Control Statements......Page 739 31.8 Calling Stored Procedures......Page 746 31.9 Querying Data with SELECT INTO......Page 749 31.10 Error Messages, Handlers, and Conditions......Page 753 31.11 Retrieving Data with a Cursor......Page 758 31.12 Including SELECT Statements Without Cursors......Page 763 31.14 Characteristics of Stored Procedures......Page 764 31.15 Stored Procedures and the Catalog......Page 767 31.16 Removing Stored Procedures......Page 768 31.17 Security with Stored Procedures......Page 769 31.18 Advantages of Stored Procedures......Page 770 32.1 Introduction......Page 772 32.2 Examples of Stored Functions......Page 773 32.3 More on Stored Functions......Page 779 32.4 Removing Stored Functions......Page 780 33.1 Introduction......Page 782 33.2 An Example of a Trigger......Page 783 33.3 More Complex Examples......Page 786 33.4 Triggers as Integrity Constraints......Page 790 33.7 Answers......Page 792 34.1 What Is an Event?......Page 794 34.2 Creating Events......Page 795 34.3 Properties of Events......Page 804 34.4 Changing Events......Page 805 34.6 Events and Privileges......Page 806 34.7 Events and the Catalog......Page 807 PART V: Programming with SQL......Page 810 35.1 Introduction......Page 812 35.2 Logging On to MySQL......Page 813 35.3 Selecting a Database......Page 814 35.4 Creating an Index......Page 815 35.5 Retrieving Error Messages......Page 817 35.6 Multiple Connections Within One Session......Page 818 35.7 SQL Statements with Parameters......Page 820 35.8 SELECT Statement with One Row......Page 821 35.9 SELECT Statement with Multiple Rows......Page 823 35.10 SELECT Statement with Null Values......Page 827 35.11 Querying Data About Expressions......Page 828 35.12 Querying the Catalog......Page 830 35.13 Remaining MYSQL Functions......Page 832 36.2 Working with Prepared SQL Statements......Page 834 36.4 Prepared Statements with Parameters......Page 837 36.5 Prepared Statements in Stored Procedures......Page 838 37.2 What Is a Transaction?......Page 842 37.3 Starting Transactions......Page 848 37.4 Savepoints......Page 849 37.5 Stored Procedures and Transactions......Page 851 37.6 Problems with Multiuser Usage......Page 852 37.7 Locking......Page 856 37.9 The LOCK TABLE and UNLOCK TABLE Statements......Page 857 37.10 The Isolation Level......Page 859 37.12 Moment of Processing Statements......Page 861 37.13 Working with Application Locks......Page 862 37.14 Answers......Page 864 A.2 The BNF Notation......Page 866 A.3 Reserved Words in SQL......Page 870 A.4 Syntax Definitions of SQL Statements......Page 872 APPENDIX B: Scalar Functions......Page 930 APPENDIX C: System Variables......Page 980 APPENDIX D: Bibliography......Page 990 A......Page 994 C......Page 995 D......Page 999 E......Page 1003 F......Page 1004 H......Page 1007 I......Page 1008 J-K......Page 1009 L......Page 1010 M......Page 1011 N......Page 1012 O......Page 1013 P......Page 1014 R......Page 1015 S......Page 1016 T......Page 1026 U......Page 1028 V......Page 1029 Z......Page 1031

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

The Most Complete and Practical Guide to MySQL Version 5s 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 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 MySQLs dialect works as it doesand how to get the most out of it. Youll gain powerful insight into everything from basic queries to stored procedures, transactions to data security. Whether youre a programmer, Web developer, analyst, DBA, or database user, this book can take you from apprentice to true SQL expert. If youve used SQL in older versions of MySQL, youll become dramatically more effectiveand if youre migrating from other database platforms, youll gain practical mastery fast . Coverage includes All of the books sample programs are available for download from (http://www.r20.nl) 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 Common Elements CHAPTER 6 SELECT Statements, Table Expressions, and Subqueries CHAPTER 7 SELECT FROM Clause CHAPTER 8 SELECT The WHERE Clause CHAPTER 9 SELECT SELECT Clause and Aggregation Functions CHAPTER 10 SELECT The GROUP BY Clause CHAPTER 11 SELECT The HAVING Clause CHAPTER 12 SELECT The ORDER BY Clause CHAPTER 13 SELECT 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 This is the eBook version of the printed book. If the print book includes a CD-ROM, this content is not included within the eBook version. 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 &nb 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 .. MySQL 5.x offers a SQL dialect with immense power. In SQL for MySQL Developers, Rick F. van der Lans helps you master this version of SQL, and leverage its full potential. Using case study examples and hands-on exercises, van der Lans illuminates every key concept, technique, and statement-including advanced features that make it easier to create even the most complex statements and programs. Drawing on decades of experience as a 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.· 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· Building stored procedures and triggers· Embedding SQL statements within PHP programs· Using transactions, savepoint, rollback, isolation level, and repeatable read· Optimizing performance by reformulating SQL statements· Utilizing MySQL's catalog· Reference section: SQL statement definitions and SQL function lists