- Katılım
- 13 Ağu 2019
- Mesajlar
- 144
- Tepkime puanı
- 24
- Puanları
- 18
SQL For MySQL Developers Pdf Documan
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.
Rar Password:
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.
Rar Password:
Bu linki görmek için izniniz yok
Giriş yap veya üye ol.
PART I Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
CHAPTER 1 Introduction to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.2 Database, Database Server, and Database Language. . . . . . . . . 4
1.3 The Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.4 What Is SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.5 The History of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.6 From Monolithic via Client/Server to the Internet . . . . . . . . . . 18
1.7 Standardization of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1.8 What Is Open Source Software? . . . . . . . . . . . . . . . . . . . . . . . . 25
1.9 The History of MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
1.10 The Structure of This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
CHAPTER 2 The Tennis Club Sample Database . . . . . . . . . . . . . . . . 29
2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.2 Description of the Tennis Club . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.3 The Contents of the Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
2.4 Integrity Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
CHAPTER 3 Installing the Software . . . . . . . . . . . . . . . . . . . . . . . . . 37
3.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
3.2 Downloading MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
3.3 Installation of MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.4 Installing a Query Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.5 Downloading SQL Statements from the Web Site . . . . . . . . . . 38
3.6 Ready? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
CHAPTER 4 SQL in a Nutshell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
4.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
4.2 Logging On to the MySQL Database Server . . . . . . . . . . . . . . . 41
4.3 Creating New SQL Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
4.4 Creating Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
4.5 Selecting the Current Database . . . . . . . . . . . . . . . . . . . . . . . . 45
4.6 Creating Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
4.7 Populating Tables with Data . . . . . . . . . . . . . . . . . . . . . . . . . . 48
4.8 Querying Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
4.9 Updating and Deleting Rows . . . . . . . . . . . . . . . . . . . . . . . . . . 52
4.10 Optimizing Query Processing with Indexes. . . . . . . . . . . . . . . 54
4.11 Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
4.12 Users and Data Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
4.13 Deleting Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
4.14 System Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
4.15 Grouping of SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . 59
4.16 The Catalog Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
4.17 Retrieving Errors and Warnings . . . . . . . . . . . . . . . . . . . . . . . 68
4.18 Definitions of SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . 69
PART II Querying and Updating Data . . . . . . . . . . . . . . . . 71
CHAPTER 5 SELECT Statement: Common Elements . . . . . . . . . . . . 73
5.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
5.2 Literals and Their Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . 74
5.3 Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
5.4 Assigning Names to Result Columns . . . . . . . . . . . . . . . . . . . . 92
5.5 The Column Specification. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
5.6 The User Variable and the SET Statement . . . . . . . . . . . . . . . . 95
5.7 The System Variable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
5.8 The Case Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
5.9 The Scalar Expression Between Brackets . . . . . . . . . . . . . . . . 106
5.10 The Scalar Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
5.11 Casting of Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
5.12 The Null Value as an Expression . . . . . . . . . . . . . . . . . . . . . . 114
5.13 The Compound Scalar Expression . . . . . . . . . . . . . . . . . . . . 115
5.14 The Aggregation Function and the Scalar Subquery . . . . . . . 136
5.15 The Row Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
5.16 The Table Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
5.17 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
CHAPTER 6 SELECT Statements, Table Expressions,
and Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
6.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
6.2 The Definition of the SELECT Statement . . . . . . . . . . . . . . . . 145
6.3 Processing the Clauses in a Select Block . . . . . . . . . . . . . . . . . 150
6.4 Possible Forms of a Table Expression . . . . . . . . . . . . . . . . . . . 156
6.5 What Is a SELECT Statement? . . . . . . . . . . . . . . . . . . . . . . . . 159
6.6 What Is a Subquery?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
6.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
CHAPTER 7 SELECT Statement:The FROM Clause. . . . . . . . . . . . . 171
7.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
7.2 Table Specifications in the FROM Clause . . . . . . . . . . . . . . . . 171
7.3 Again, the Column Specification. . . . . . . . . . . . . . . . . . . . . . . 173
7.4 Multiple Table Specifications in the FROM Clause . . . . . . . . . 174
7.5 Pseudonyms for Table Names. . . . . . . . . . . . . . . . . . . . . . . . . 178
7.6 Various Examples of Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
7.7 Mandatory Use of Pseudonyms . . . . . . . . . . . . . . . . . . . . . . . 183
7.8 Tables of Different Databases . . . . . . . . . . . . . . . . . . . . . . . . 185
7.9 Explicit Joins in the FROM Clause. . . . . . . . . . . . . . . . . . . . . . 185
7.10 Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
7.11 The Natural Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
7.12 Additional Conditions in the Join Condition. . . . . . . . . . . . . 196
7.13 The Cross Join. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
7.14 Replacing Join Conditions with USING. . . . . . . . . . . . . . . . . 199
7.15 The FROM Clause with Table Expressions . . . . . . . . . . . . . . 200
7.16 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
CHAPTER 8 SELECT Statement: The WHERE Clause . . . . . . . . . . . 213
8.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
8.2 Conditions Using Comparison Operators . . . . . . . . . . . . . . . 215
8.3 Comparison Operators with Subqueries . . . . . . . . . . . . . . . . 222
8.4 Comparison Operators with Correlated Subqueries. . . . . . . . 227
8.5 Conditions Without a Comparison Operator. . . . . . . . . . . . . 229
8.6 Conditions Coupled with AND, OR, XOR, and NOT . . . . . . . 231
8.7 The IN Operator with Expression List. . . . . . . . . . . . . . . . . . . 235
8.8 The IN Operator with Subquery . . . . . . . . . . . . . . . . . . . . . . . 241
8.9 The BETWEEN Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
8.10 The LIKE Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
8.11 The REGEXP Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
8.12 The MATCH Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
8.13 The IS NULL Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
8.14 The EXISTS Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
8.15 The ALL and ANY Operators . . . . . . . . . . . . . . . . . . . . . . . . 281
8.16 Scope of Columns in Subqueries . . . . . . . . . . . . . . . . . . . . . 289
8.17 More Examples with Correlated Subqueries . . . . . . . . . . . . . 294
8.18 Conditions with Negation. . . . . . . . . . . . . . . . . . . . . . . . . . . 299
8.19 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
CHAPTER 9 SELECT Statement: SELECT Clause and
Aggregation Functions . . . . . . . . . . . . . . . . . . . . . . . . 315
9.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
9.2 Selecting All Columns (*) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
9.3 Expressions in the SELECT Clause . . . . . . . . . . . . . . . . . . . . . 317
9.4 Removing Duplicate Rows with DISTINCT. . . . . . . . . . . . . . . 318
9.5 When Are Two Rows Equal?. . . . . . . . . . . . . . . . . . . . . . . . . . 321
9.6 More Select Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
9.7 An Introduction to Aggregation Functions. . . . . . . . . . . . . . . 324
9.8 COUNT Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
9.9 MAX and MIN Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
9.10 The SUM and AVG Function. . . . . . . . . . . . . . . . . . . . . . . . . 336
9.11 The VARIANCE and STDDEV Functions. . . . . . . . . . . . . . . . 341
9.12 The VAR_SAMP and STDDEV_SAMP Functions . . . . . . . . . 343
9.13 The BIT_AND, BIT_OR, and BIT_XOR Functions . . . . . . . . 343
9.14 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
CHAPTER 10 SELECT Statement: The GROUP BY Clause . . . . . . . . 349
10.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
10.2 Grouping on One Column . . . . . . . . . . . . . . . . . . . . . . . . . . 350
10.3 Grouping on Two or More Columns . . . . . . . . . . . . . . . . . . 353
10.4 Grouping on Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
10.5 Grouping of Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
10.6 Grouping with Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
10.7 General Rules for the GROUP BY Clause . . . . . . . . . . . . . . . 359
10.8 The GROUP_CONCAT Function . . . . . . . . . . . . . . . . . . . . . 362
10.9 Complex Examples with GROUP BY. . . . . . . . . . . . . . . . . . . 363
10.10 Grouping with WITH ROLLUP . . . . . . . . . . . . . . . . . . . . . . 369
10.11 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
CHAPTER 11 SELECT Statement: The HAVING Clause . . . . . . . . . . 375
11.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
11.2 Examples of the HAVING Clause . . . . . . . . . . . . . . . . . . . . . 376
11.3 A HAVING Clause but not a GROUP BY Clause . . . . . . . . . 378
11.4 General Rule for the HAVING Clause . . . . . . . . . . . . . . . . . . 379
11.5 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
CHAPTER 12 SELECT Statement: The ORDER BY Clause . . . . . . . . 383
12.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
12.2 Sorting on Column Names . . . . . . . . . . . . . . . . . . . . . . . . . . 383
12.3 Sorting on Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
12.4 Sorting with Sequence Numbers . . . . . . . . . . . . . . . . . . . . . 387
12.5 Sorting in Ascending and Descending Order . . . . . . . . . . . . 389
12.6 Sorting Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
12.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
CHAPTER 13 SELECT Statement: The LIMIT Clause. . . . . . . . . . . . . 395
13.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
13.2 Get the Top… . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
13.3 Subqueries with a LIMIT Clause . . . . . . . . . . . . . . . . . . . . . . 402
13.4 Limit with an Offset . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
13.5 The Select Option SQL_CALC_FOUND_ROWS . . . . . . . . . 405
13.6 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
CHAPTER 14 Combining Table Expressions . . . . . . . . . . . . . . . . . . . 409
14.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
14.2 Combining with UNION. . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
14.3 Rules for Using UNION . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
14.4 Keeping Duplicate Rows. . . . . . . . . . . . . . . . . . . . . . . . . . . . 416
14.5 Set Operators and the Null Value. . . . . . . . . . . . . . . . . . . . . 417
14.6 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
CHAPTER 15 The User Variable and the SET Statement . . . . . . . . . . 421
15.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
15.2 Defining Variables with the SET Statement . . . . . . . . . . . . . 421
15.3 Defining Variables with the SELECT Statement . . . . . . . . . . 423
15.4 Application Areas for User Variables . . . . . . . . . . . . . . . . . . 425
15.5 Life Span of User Variables . . . . . . . . . . . . . . . . . . . . . . . . . . 426
15.6 The DO Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
15.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
CHAPTER 16 The HANDLER Statement. . . . . . . . . . . . . . . . . . . . . . 429
16.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429
16.2 A Simple Example of the HANDLER Statement . . . . . . . . . . 429
16.3 Opening a Handler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
16.4 Browsing the Rows of a Handler . . . . . . . . . . . . . . . . . . . . . 431
16.5 Closing a Handler. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
16.6 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
CHAPTER 17 Updating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437
17.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437
17.2 Inserting New Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437
17.3 Populating a Table with Rows from Another Table . . . . . . . 442
17.4 Updating Values in Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
17.5 Updating Values in Multiple Tables . . . . . . . . . . . . . . . . . . . 450
17.6 Substituting Existing Rows . . . . . . . . . . . . . . . . . . . . . . . . . . 452
17.7 Deleting Rows from a Table . . . . . . . . . . . . . . . . . . . . . . . . . 454
17.8 Deleting Rows from Multiple Tables. . . . . . . . . . . . . . . . . . . 456
17.9 The TRUNCATE Statement . . . . . . . . . . . . . . . . . . . . . . . . . 458
17.10 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458
CHAPTER 18 Loading and Unloading Data . . . . . . . . . . . . . . . . . . . 461
18.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
18.2 Unloading Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
18.3 Loading Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
CHAPTER 19 Working with XML Documents . . . . . . . . . . . . . . . . . . 471
19.1 XML in a Nutshell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
19.2 Storing XML Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
19.3 Querying XML Documents . . . . . . . . . . . . . . . . . . . . . . . . . . 476
19.4 Querying Using Positions . . . . . . . . . . . . . . . . . . . . . . . . . . . 484
19.5 The Extended Notation of XPath . . . . . . . . . . . . . . . . . . . . . 486
19.6 XPath Expressions with Conditions . . . . . . . . . . . . . . . . . . . 488
19.7 Changing XML Documents. . . . . . . . . . . . . . . . . . . . . . . . . . 489
PART III Creating Database Objects. . . . . . . . . . . . . . . . . 491
CHAPTER 20 Creating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
20.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
20.2 Creating New Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
20.3 Data Types of Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496
20.4 Adding Data Type Options. . . . . . . . . . . . . . . . . . . . . . . . . . 508
20.5 Creating Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . 514
20.6 What If the Table Already Exists? . . . . . . . . . . . . . . . . . . . . . 515
20.7 Copying Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 516
20.8 Naming Tables and Columns . . . . . . . . . . . . . . . . . . . . . . . . 521
20.9 Column Options: Default and Comment . . . . . . . . . . . . . . . 522
20.10 Table Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
20.11 The CSV Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . 532
20.12 Tables and the Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
20.13 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537
CHAPTER 21 Specifying Integrity Constraints. . . . . . . . . . . . . . . . . . 539
21.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539
21.2 Primary Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 541
21.3 Alternate Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544
21.4 Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546
21.5 The Referencing Action. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
21.6 Check Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . . . 553
21.7 Naming Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . 556
21.8 Deleting Integrity Constraints. . . . . . . . . . . . . . . . . . . . . . . . 557
21.9 Integrity Constraints and the Catalog . . . . . . . . . . . . . . . . . 557
21.10 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558
CHAPTER 22 Character Sets and Collations . . . . . . . . . . . . . . . . . . . 561
22.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
22.2 Available Character Sets and Collations . . . . . . . . . . . . . . . . 563
22.3 Assigning Character Sets to Columns. . . . . . . . . . . . . . . . . . 564
22.4 Assigning Collations to Columns . . . . . . . . . . . . . . . . . . . . . 566
22.5 Expressions with Character Sets and Collations . . . . . . . . . . 568
22.6 Sorting and Grouping with Collations . . . . . . . . . . . . . . . . . 571
22.7 The Coercibility of Expressions. . . . . . . . . . . . . . . . . . . . . . . 573
22.8 Related System Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . 574
22.9 Character Sets and the Catalog . . . . . . . . . . . . . . . . . . . . . . 576
22.10 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576
CHAPTER 23 The ENUM and SET Types . . . . . . . . . . . . . . . . . . . . . 577
23.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577
23.2 The ENUM Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578
23.3 The SET Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582
23.4 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
CHAPTER 24 Changing and Dropping Tables . . . . . . . . . . . . . . . . . . 591
24.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
24.2 Deleting Entire Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
24.3 Renaming Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
24.4 Changing the Table Structure . . . . . . . . . . . . . . . . . . . . . . . . 593
24.5 Changing Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595
24.6 Changing Integrity Constraints. . . . . . . . . . . . . . . . . . . . . . . 599
24.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602
CHAPTER 25 Using Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
25.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
25.2 Rows, Tables, and Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 604
25.3 How Does an Index Work?. . . . . . . . . . . . . . . . . . . . . . . . . . 605
25.4 Processing a SELECT Statement: The Steps . . . . . . . . . . . . . 610
25.5 Creating Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614
25.6 Defining Indexes Together with the Tables . . . . . . . . . . . . . . 617
25.7 Dropping Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618
25.8 Indexes and Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . 619
25.9 The Big PLAYERS_XXL Table . . . . . . . . . . . . . . . . . . . . . . . . 620
25.10 Choosing Columns for Indexes . . . . . . . . . . . . . . . . . . . . . . 622
25.11 Indexes and the Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . 627
25.12 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630
CHAPTER 26 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
26.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
26.2 Creating Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
26.3 The Column Names of Views . . . . . . . . . . . . . . . . . . . . . . . . 635
26.4 Updating Views: WITH CHECK OPTION. . . . . . . . . . . . . . . 636
26.5 Options of Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
26.6 Deleting Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
26.7 Views and the Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
26.8 Restrictions on Updating Views . . . . . . . . . . . . . . . . . . . . . . 641
26.9 Processing View Statements . . . . . . . . . . . . . . . . . . . . . . . . . 642
26.10 Application Areas for Views . . . . . . . . . . . . . . . . . . . . . . . . 645
26.11 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
CHAPTER 27 Creating Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . 653
27.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
27.2 Databases and the Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . 653
27.3 Creating Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654
27.4 Changing Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 655
27.5 Dropping Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 656
CHAPTER 28 Users and Data Security . . . . . . . . . . . . . . . . . . . . . . . 659
28.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659
28.2 Adding and Removing Users . . . . . . . . . . . . . . . . . . . . . . . . 660
28.3 Changing the Names of Users . . . . . . . . . . . . . . . . . . . . . . . 662
28.4 Changing Passwords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 663
28.5 Granting Table and Column Privileges . . . . . . . . . . . . . . . . . 664
28.6 Granting Database Privileges . . . . . . . . . . . . . . . . . . . . . . . . 667
28.7 Granting User Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670
28.8 Passing on Privileges: WITH GRANT OPTION . . . . . . . . . . 673
28.9 Restricting Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674
28.10 Recording Privileges in the Catalog . . . . . . . . . . . . . . . . . . 675
28.11 Revoking Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 677
28.12 Security of and Through Views . . . . . . . . . . . . . . . . . . . . . . 680
28.13 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 682
CHAPTER 29 Statements for Table Maintenance . . . . . . . . . . . . . . . 683
29.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
29.2 The ANALYZE TABLE Statement . . . . . . . . . . . . . . . . . . . . . 684
29.3 The CHECKSUM TABLE Statement . . . . . . . . . . . . . . . . . . . 685
29.4 The OPTIMIZE TABLE Statement. . . . . . . . . . . . . . . . . . . . . 686
29.5 The CHECK TABLE Statement . . . . . . . . . . . . . . . . . . . . . . . 687
29.6 The REPAIR TABLE Statement . . . . . . . . . . . . . . . . . . . . . . . 689
29.7 The BACKUP TABLE Statement . . . . . . . . . . . . . . . . . . . . . . 690
29.8 The RESTORE TABLE Statement . . . . . . . . . . . . . . . . . . . . . 691
CHAPTER 30 The SHOW, DESCRIBE, and HELP Statements. . . . . . 693
30.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693
30.2 Overview of SHOW Statements . . . . . . . . . . . . . . . . . . . . . . 693
30.3 Additional SHOW Statements . . . . . . . . . . . . . . . . . . . . . . . 698
30.4 The DESCRIBE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . 699
30.5 The HELP Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699
PART IV Procedural Database Objects. . . . . . . . . . . . . . . 701
CHAPTER 31 Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . 703
31.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 703
31.2 An Example of a Stored Procedure . . . . . . . . . . . . . . . . . . . . 704
31.3 The Parameters of a Stored Procedure . . . . . . . . . . . . . . . . . 706
31.4 The Body of a Stored Procedure. . . . . . . . . . . . . . . . . . . . . . 707
31.5 Local Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709
31.6 The SET Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712
31.7 Flow-Control Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . 712
31.8 Calling Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 719
31.9 Querying Data with SELECT INTO. . . . . . . . . . . . . . . . . . . . 722
31.10 Error Messages, Handlers, and Conditions . . . . . . . . . . . . 726
31.11 Retrieving Data with a Cursor. . . . . . . . . . . . . . . . . . . . . . . 731
31.12 Including SELECT Statements Without Cursors . . . . . . . . . 736
31.13 Stored Procedures and User Variables . . . . . . . . . . . . . . . . 737
31.14 Characteristics of Stored Procedures . . . . . . . . . . . . . . . . . 737
31.15 Stored Procedures and the Catalog . . . . . . . . . . . . . . . . . . 740
31.16 Removing Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . 741
31.17 Security with Stored Procedures . . . . . . . . . . . . . . . . . . . . . 742
31.18 Advantages of Stored Procedures . . . . . . . . . . . . . . . . . . . . 743
CHAPTER 32 Stored Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745
32.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745
32.2 Examples of Stored Functions . . . . . . . . . . . . . . . . . . . . . . . 746
32.3 More on Stored Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 752
32.4 Removing Stored Functions . . . . . . . . . . . . . . . . . . . . . . . . . 753
CHAPTER 33 Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755
33.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755
33.2 An Example of a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . 756
33.3 More Complex Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . 759
33.4 Triggers as Integrity Constraints. . . . . . . . . . . . . . . . . . . . . . 763
33.5 Removing Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765
33.6 Triggers and the Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . 765
33.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765
CHAPTER 34 Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 767
34.1 What Is an Event?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 767
34.2 Creating Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 768
34.3 Properties of Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 777
34.4 Changing Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 778
34.5 Removing Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779
34.6 Events and Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779
34.7 Events and the Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 780
PART V Programming with SQL . . . . . . . . . . . . . . . . . . . 783
CHAPTER 35 MySQL and PHP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 785
35.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 785
35.2 Logging On to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 786
35.3 Selecting a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 787
35.4 Creating an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 788
35.5 Retrieving Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . 790
35.6 Multiple Connections Within One Session . . . . . . . . . . . . . . 791
35.7 SQL Statements with Parameters . . . . . . . . . . . . . . . . . . . . . 793
35.8 SELECT Statement with One Row . . . . . . . . . . . . . . . . . . . . 794
35.9 SELECT Statement with Multiple Rows . . . . . . . . . . . . . . . . 796
35.10 SELECT Statement with Null Values . . . . . . . . . . . . . . . . . . 800
35.11 Querying Data About Expressions . . . . . . . . . . . . . . . . . . . 801
35.12 Querying the Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 803
35.13 Remaining MYSQL Functions . . . . . . . . . . . . . . . . . . . . . . . 805
CHAPTER 36 Dynamic SQL with Prepared Statement. . . . . . . . . . . . 807
36.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 807
36.2 Working with Prepared SQL Statements . . . . . . . . . . . . . . . 807
36.3 Prepared Statements with User Variables. . . . . . . . . . . . . . . 810
36.4 Prepared Statements with Parameters . . . . . . . . . . . . . . . . . 810
36.5 Prepared Statements in Stored Procedures . . . . . . . . . . . . . 811
CHAPTER 37 Transactions and Multiuser Usage. . . . . . . . . . . . . . . . 815
37.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815
37.2 What Is a Transaction? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815
37.3 Starting Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 821
37.4 Savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 822
37.5 Stored Procedures and Transactions . . . . . . . . . . . . . . . . . . 824
37.6 Problems with Multiuser Usage . . . . . . . . . . . . . . . . . . . . . . 825
37.7 Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 829
37.8 Deadlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 830
37.9 The LOCK TABLE and UNLOCK TABLE Statements . . . . . . 830
37.10 The Isolation Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 832
37.11 Waiting for a Lock . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 834
37.12 Moment of Processing Statements . . . . . . . . . . . . . . . . . . . 834
37.13 Working with Application Locks . . . . . . . . . . . . . . . . . . . . . 835
37.14 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 837
APPENDIX A Syntax of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 839
A.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 839
A.2 The BNF Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 839
A.3 Reserved Words in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 843
A.4 Syntax Definitions of SQL Statements . . . . . . . . . . . . . . . . . . 845
APPENDIX B Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 903
APPENDIX C System Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 953
APPENDIX D Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 963
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 967