Rick F. van der Lans0131497359, 978-0-13-149735-1
В
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 В В В
Table of contents :
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
Reviews
There are no reviews yet.