- Shopping Bag ( 0 items )
Want a NOOK? Explore Now
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.
SQL for MySQL DevelopersSQL for MySQL DevelopersPrefaceIntroduction
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 everyaspect 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
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
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 programming languages and database servers is required.The History of This Book
It was 1984, and the database world was under the spell of a revolution. SQL had started its triumphal procession. Vendors such as IBM and Oracle had introduced the commercial versions of their SQL database servers, and the marketing machine went at full speed. The market reacted positively to this rise of first-generation SQL database servers. Many organizations decided to buy such a database server and gradually phase out their existing products.
My employer at that time had decided to get involved in this tumult as well. The company also wanted to make money with this new database language, and the plan was to start organizing SQL courses. Because of my background knowledge, I was charged with this task. That SQL would become such a success and that my agreement to present the courses would have far- reaching consequences (personally as well as professionally) never entered my mind.
After studying SQL closely, I started to develop the material for the course. After teaching SQL for two years with great pleasure, I got an idea to write a book about SQL. It would have to be a book completely dedicated to this language, with its many possibilities and idiosyncrasies.
After producing gallons of blood, sweat, and tears, I completed the first Dutch edition in 1986, entitled Het SQL Leerboek. The book did not focus on a specific SQL database server, but on the SQL standard. Barely before the book was published, I was asked to write an English version. That book, Introduction to SQL, was published in 1987 as the first English book completely devoted to SQL. After that, I wrote versions in German and Italian. Obviously, a need existed for information about SQL. Everyone wanted to learn about SQL, but not much information was available.
Because SQL was still young, development went fast. Statements were added, extended, and improved. New implementations became available, new application areas were discovered, and new versions of the SQL standard appeared. Soon a new edition of the book had to be written. And more was to come. And this will not be the last because SQL has gloriously won the revolution in the database world, and no competition is in sight on the horizon.
Through the years, many vendors have implemented SQL. At first, all these products had much in common, but slowly the number of differences increased. For that reason, I decided in 2003 to write a book specifically dedicated to the SQL dialect of MySQL. I thought it would be a piece of cake. I would use Introduction to SQL as an example, add some details of MySQL, and remove a few general aspects. How long could that take? Two weeks of hard work and some speed typing, and I'd have the book ready. However, that appeared to be a serious underestimation. To give a complete view of all the features, I had to dive deeply into the SQL dialect of MySQL. This book, which definitely took more than two weeks of writing, is the result of that time-consuming effort. Obviously, it is related to the book from which it is derived; however, it contains many MySQL-related details not included in Introduction to SQL.And Finally...
Writing this book was not a solo project. Many people have contributed to this book or previous editions. I would like to use this preface to thank them for their help, contributions, ideas, comments, mental support, and patience.
It does not matter how many times a writer reads his own work; editors remain indispensable. A writer reads not what he has written, but what he thinks he has written. In this respect, writing is like programming. That is why I owe a great deal to the following persons for making critical comments and giving very helpful advice: Klaas Brant, Marc van Cappellen, Ian Cargill, Corine Cools, Richard van Dijk, Rose Endres, Wim Frederiks, Andrea Gray, Ed Jedeloo, Josien van der Laan, Oda van der Lans, Deborah Leendertse, Arjen Lentz, Onno de Maar, Andrea Maurino, Sandor Nieuwenhuijs, Henk Schreij, Dave Slayton, Aad Speksnijder, Nok van Veen, John Vicherek, and David van der Waaij. They all have read this manuscript (or parts of it) or the manuscript of a previous edition, a translation of it, or an adjusted version.
I would like to thank Wim Frederiks and Roland Bouman separately for all the hours they spent editing this book. Both patiently studied each page and pointed out the errors and inconsistencies. I am very grateful to them for all the work they put into this project.
I would also like to thank the thousands of students across the world whom I have taught SQL over the past years. Their comments and recommendations have been invaluable in revising this book. In addition, a large number of readers of the previous edition responded to my request to send comments and suggestions. I want to thank them for the trouble they took to put these in writing.
From the first day I started working on the project, I had the support of the MySQL organization. They helped me by making the required software available. I want to thank this group very much for the support and help.
Again, I owe Diane Cools many thanks. As an editor, she made this book readable to others. For a writer, it is also reassuring to find someone who, especially in difficult times, keeps stimulating and motivating you. Thanks, Diane!
Finally, again I would like to ask readers to send comments, opinions, ideas, and suggestions concerning the contents of the book to sql@r20.nl, referencing SQL for MySQL Developers. Many thanks, in anticipation of your cooperation.
Rick F. van der Lans
Den Haag, The Netherlands, March 2007
© Copyright Pearson Education. All rights reserved.
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
CHAPTER 7 SELECT Statement:The FROM Clause 171
CHAPTER 8 SELECT Statement: The WHERE Clause 213
CHAPTER 9 SELECT Statement: SELECT Clause and Aggregation Functions 315
CHAPTER 10 SELECT Statement: The GROUP BY Clause 349
CHAPTER 11 SELECT Statement: The HAVING Clause 375
CHAPTER 12 SELECT Statement: The ORDER BY Clause 383
CHAPTER 13 SELECT Statement: The LIMIT Clause 395
CHAPTER 14 Combining Table Expressions 409
CHAPTER 15 The User Variable and the SET Statement 421
CHAPTER 16 The HANDLER Statement 429
CHAPTER 17 Updating Tables 437
CHAPTER 18 Loading and Unloading Data 461
CHAPTER 19 Working with XML Documents 471
PART III Creating Database Objects 491
CHAPTER 20 Creating Tables 493
CHAPTER 21 Specifying Integrity Constraints 539
CHAPTER 22 Character Sets and Collations 561
CHAPTER 23 The ENUM and SET Types 577
CHAPTER 24 Changing and Dropping Tables 591
CHAPTER 25 Using Indexes 603
CHAPTER 26 Views 631
CHAPTER 27 Creating Databases 653
CHAPTER 28 Users and Data Security 659
CHAPTER 29 Statements for Table Maintenance 683
CHAPTER 30 The SHOW, DESCRIBE, and HELP Statements 693
PART IV Procedural Database Objects 701
CHAPTER 31 Stored Procedures 703
CHAPTER 32 Stored Functions 745
CHAPTER 33 Triggers 755
CHAPTER 34 Events 767
PART V Programming with SQL 783
CHAPTER 35 MySQL and PHP 785
CHAPTER 36 Dynamic SQL with Prepared Statement 807
CHAPTER 37 Transactions and Multiuser Usage 815
APPENDIX A Syntax of SQL 839
APPENDIX B Scalar Functions 903
APPENDIX C System Variables 953
APPENDIX D Bibliography 963
Index 967
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.
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:
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:
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.
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:
Therefore, keep an eye on this web site.
Some general knowledge of programming languages and database servers is required.
It was 1984, and the database world was under the spell of a revolution. SQL had started its triumphal procession. Vendors such as IBM and Oracle had introduced the commercial versions of their SQL database servers, and the marketing machine went at full speed. The market reacted positively to this rise of first-generation SQL database servers. Many organizations decided to buy such a database server and gradually phase out their existing products.
My employer at that time had decided to get involved in this tumult as well. The company also wanted to make money with this new database language, and the plan was to start organizing SQL courses. Because of my background knowledge, I was charged with this task. That SQL would become such a success and that my agreement to present the courses would have far- reaching consequences (personally as well as professionally) never entered my mind.
After studying SQL closely, I started to develop the material for the course. After teaching SQL for two years with great pleasure, I got an idea to write a book about SQL. It would have to be a book completely dedicated to this language, with its many possibilities and idiosyncrasies.
After producing gallons of blood, sweat, and tears, I completed the first Dutch edition in 1986, entitled Het SQL Leerboek. The book did not focus on a specific SQL database server, but on the SQL standard. Barely before the book was published, I was asked to write an English version. That book, Introduction to SQL, was published in 1987 as the first English book completely devoted to SQL. After that, I wrote versions in German and Italian. Obviously, a need existed for information about SQL. Everyone wanted to learn about SQL, but not much information was available.
Because SQL was still young, development went fast. Statements were added, extended, and improved. New implementations became available, new application areas were discovered, and new versions of the SQL standard appeared. Soon a new edition of the book had to be written. And more was to come. And this will not be the last because SQL has gloriously won the revolution in the database world, and no competition is in sight on the horizon.
Through the years, many vendors have implemented SQL. At first, all these products had much in common, but slowly the number of differences increased. For that reason, I decided in 2003 to write a book specifically dedicated to the SQL dialect of MySQL. I thought it would be a piece of cake. I would use Introduction to SQL as an example, add some details of MySQL, and remove a few general aspects. How long could that take? Two weeks of hard work and some speed typing, and I'd have the book ready. However, that appeared to be a serious underestimation. To give a complete view of all the features, I had to dive deeply into the SQL dialect of MySQL. This book, which definitely took more than two weeks of writing, is the result of that time-consuming effort. Obviously, it is related to the book from which it is derived; however, it contains many MySQL-related details not included in Introduction to SQL.
Writing this book was not a solo project. Many people have contributed to this book or previous editions. I would like to use this preface to thank them for their help, contributions, ideas, comments, mental support, and patience.
It does not matter how many times a writer reads his own work; editors remain indispensable. A writer reads not what he has written, but what he thinks he has written. In this respect, writing is like programming. That is why I owe a great deal to the following persons for making critical comments and giving very helpful advice: Klaas Brant, Marc van Cappellen, Ian Cargill, Corine Cools, Richard van Dijk, Rose Endres, Wim Frederiks, Andrea Gray, Ed Jedeloo, Josien van der Laan, Oda van der Lans, Deborah Leendertse, Arjen Lentz, Onno de Maar, Andrea Maurino, Sandor Nieuwenhuijs, Henk Schreij, Dave Slayton, Aad Speksnijder, Nok van Veen, John Vicherek, and David van der Waaij. They all have read this manuscript (or parts of it) or the manuscript of a previous edition, a translation of it, or an adjusted version.
I would like to thank Wim Frederiks and Roland Bouman separately for all the hours they spent editing this book. Both patiently studied each page and pointed out the errors and inconsistencies. I am very grateful to them for all the work they put into this project.
I would also like to thank the thousands of students across the world whom I have taught SQL over the past years. Their comments and recommendations have been invaluable in revising this book. In addition, a large number of readers of the previous edition responded to my request to send comments and suggestions. I want to thank them for the trouble they took to put these in writing.
From the first day I started working on the project, I had the support of the MySQL organization. They helped me by making the required software available. I want to thank this group very much for the support and help.
Again, I owe Diane Cools many thanks. As an editor, she made this book readable to others. For a writer, it is also reassuring to find someone who, especially in difficult times, keeps stimulating and motivating you. Thanks, Diane!
Finally, again I would like to ask readers to send comments, opinions, ideas, and suggestions concerning the contents of the book to sql@r20.nl, referencing SQL for MySQL Developers. Many thanks, in anticipation of your cooperation.
Rick F. van der Lans
Den Haag, The Netherlands, March 2007
© Copyright Pearson Education. All rights reserved.
Anonymous
Posted Sun May 06 00:00:00 EDT 2007
Van der Lans offers an impressively thorough education in MySQL 5. It is remarkable how much capability is offered for free in the open source MySQL. The book can also be used to learn generic SQL from scratch. Some early chapters give a quick recap, that should be understandable to an experienced programmer, who does not know SQL. If this describes you, the biggest adjustment to learning it might be its declarative nature, as opposed to the procedural ethos of most programming languages. One striking feature of the book is the extensive explanation of SELECT. This is the key statement of SQL, and you need to be facile with it. Nine [sic] chapters are devoted to SELECT. Gives you some idea of both the power and complexity of the command. Rarely do other SQL books allocate so much space to a rendition of SELECT. Often, they teach you the basic modes of using it, and then you are left on your own. (Gee, thanks!) Whereas here, for example, an entire chapter is about the SELECT HAVING clause, and another chapter is on the SELECT LIMIT clause. Across these nine chapters are enough worked examples that you can fruitfully mine. Later chapters delve into more specialised aspects of SQL. Sure, these are all significant. But maybe the most useful is the idea of stored procedures and functions. Vital to optimising performance. Lest this very mass of detail prove offputting, keep in mind the 'Reference' in the book's title. You don't have to read it all at once. As you go thru it, you should develop an intuition of what can be safely relegated to a later reading, when you have specific need of those topics.
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Overview
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 ...