Table of contents for SQL for MySQL developers : a comprehensive tutorial and reference / Rick F. van der Lans.

Bibliographic record and links to related information available from the Library of Congress catalog.

Note: Contents data are machine generated based on pre-publication provided by the publisher. Contents may have variations from the printed book or be incomplete or contain other coding.


Counter
 CONTENTS
CONTENTS	V
PREFACE	XVI
PART I: INTRODUCTION	1
1 INTRODUCTION TO MYSQL	2
1.1 INTRODUCTION	2
1.2 DATABASE, DATABASE SERVER AND DATABASE LANGUAGE	2
1.3 THE RELATIONAL MODEL	4
1.4 WHAT IS SQL?	8
1.5 THE HISTORY OF SQL	10
1.6 FROM MONOLITHIC VIA CLIENT/SERVER TO THE INTERNET	11
1.7 STANDARDIZATION OF SQL	12
1.8 WHAT IS OPEN SOURCE SOFTWARE?	15
1.9 THE HISTORY OF MYSQL	16
1.10 THE STRUCTURE OF THE BOOK	17
2 THE TENNIS CLUB SAMPLE DATABASE	19
2.1 INTRODUCTION	19
2.2 DESCRIPTION OF THE TENNIS CLUB	19
2.3 THE CONTENTS OF THE TABLES	22
2.4 INTEGRITY CONSTRAINTS	23
3 INSTALLING THE SOFTWARE	26
3.1 INTRODUCTION	26
3.2 DOWNLOADING MYSQL	26
3.3 INSTALLATION OF MYSQL	26
3.4 INSTALLING A QUERY TOOL	27
3.5 DOWNLOADING SQL STATEMENTS FROM THE WEBSITE	27
3.6 READY?	27
4 SQL IN A NUTSHELL	28
4.1 INTRODUCTION	28
4.2 LOGGING ON TO THE MYSQL DATABASE SERVER	28
4.3 CREATING NEW SQL USERS	29
4.4 CREATING DATABASES	30
4.5 SELECTING THE CURRENT DATABASE	31
4.6 CREATING TABLES	31
4.7 POPULATING TABLES WITH DATA	33
4.8 QUERYING TABLES	34
4.9 UPDATING AND DELETING ROWS	36
4.10 OPTIMIZING QUERY PROCESSING WITH INDEXES	37
4.11 VIEWS	38
4.12 USERS AND DATA SECURITY	39
4.13 DELETING DATABASE OBJECTS	40
4.14 SYSTEM VARIABLES	41
4.15 GROUPING OF SQL STATEMENTS	42
4.16 THE CATALOG TABLES	42
4.17 RETRIEVING ERRORS AND WARNINGS	48
4.18 DEFINITIONS OF SQL STATEMENTS	49
PART II: QUERYING AND UPDATING DATA	51
5 SELECT STATEMENT: COMMON ELEMENTS	52
5.1 INTRODUCTION	52
5.2 LITERALS AND THEIR DATA TYPES	52
5.3 EXPRESSIONS	64
5.4 ASSIGNING NAMES TO RESULT COLUMNS	66
5.5 THE COLUMN SPECIFICATION	68
5.6 THE USER VARIABLE AND THE SET STATEMENT	69
5.7 THE SYSTEM VARIABLE	71
5.8 THE CASE EXPRESSION	74
5.9 THE SCALAR EXPRESSION BETWEEN BRACKETS	78
5.10 THE SCALAR FUNCTION	78
5.11 CASTING OF EXPRESSIONS	82
5.12 THE NULL VALUE AS EXPRESSION	84
5.13 THE COMPOUND SCALAR EXPRESSION	85
5.14 THE AGGREGATION FUNCTION AND THE SCALAR SUBQUERY	101
5.15 THE ROW EXPRESSION	102
5.16 THE TABLE EXPRESSION	104
5.17 ANSWERS	104
6 SELECT STATEMENTS, TABLE EXPRESSIONS, AND SUBQUERIES	108
6.1 INTRODUCTION	108
6.2 THE DEFINITION OF THE SELECT STATEMENT	108
6.3 PROCESSING THE CLAUSES IN A SELECT BLOCK	111
6.4 POSSIBLE FORMS OF A TABLE EXPRESSION	115
6.5 WHAT IS A SELECT STATEMENT?	118
6.6 WHAT IS A SUBQUERY?	118
6.7 ANSWERS	123
7 SELECT STATEMENT: THE FROM CLAUSE	127
7.1 INTRODUCTION	127
7.2 TABLE SPECIFICATIONS IN THE FROM CLAUSE	127
7.3 AGAIN, THE COLUMN SPECIFICATION	128
7.4 MULTIPLE TABLE SPECIFICATIONS IN THE FROM CLAUSE	129
7.5 PSEUDONYMS FOR TABLE NAMES	132
7.6 VARIOUS EXAMPLES OF JOINS	133
7.7 MANDATORY USE OF PSEUDONYMS	136
7.8 ACCESSING TABLES OF DIFFERENT DATABASES	138
7.9 EXPLICIT JOINS IN THE FROM CLAUSE	138
7.10 OUTER JOINS	141
7.11 THE NATURAL JOIN	146
7.12 ADDITIONAL CONDITIONS IN THE JOIN CONDITION	147
7.13 THE CROSS JOIN	149
7.14 REPLACING JOIN CONDITIONS BY USING	149
7.15 THE FROM CLAUSE WITH TABLE EXPRESSIONS	150
7.16 ANSWERS	155
8 SELECT STATEMENT: THE WHERE CLAUSE	160
8.1 INTRODUCTION	160
8.2 CONDITIONS USING COMPARISON OPERATORS	161
8.3 COMPARISON OPERATORS WITH SUBQUERIES	166
8.4 COMPARISON OPERATORS WITH CORRELATED SUBQUERIES	171
8.5 CONDITIONS WITHOUT A COMPARISON OPERATOR	173
8.6 CONDITIONS COUPLED WITH AND, OR, XOR AND NOT	174
8.7 THE IN OPERATOR WITH EXPRESSION LIST	178
8.8 THE IN OPERATOR WITH SUBQUERY	182
8.9 THE BETWEEN OPERATOR	189
8.10 THE LIKE OPERATOR	191
8.11 THE REGEXP OPERATOR	194
8.12 THE MATCH OPERATOR	200
8.13 THE IS NULL OPERATOR	210
8.14 THE EXISTS OPERATOR	212
8.15 THE ALL AND ANY OPERATORS	214
8.16 SCOPE OF COLUMNS IN SUBQUERIES	220
8.17 MORE EXAMPLES WITH CORRELATED SUBQUERIES	224
8.18 CONDITIONS WITH NEGATION	229
8.19 ANSWERS	231
9 SELECT STATEMENT: SELECT CLAUSE AND AGGREGATION FUNCTIONS
	239
9.1 INTRODUCTION	239
9.2 SELECTING ALL COLUMNS (*)	240
9.3 EXPRESSIONS IN THE SELECT CLAUSE	241
9.4 REMOVING DUPLICATE ROWS WITH DISTINCT	241
9.5 WHEN ARE TWO ROWS EQUAL?	244
9.6 MORE SELECT OPTIONS	246
9.7 AN INTRODUCTION TO AGGREGATION FUNCTIONS	247
9.8 COUNT FUNCTION	249
9.9 MAX AND MIN FUNCTIONS	252
9.10 THE SUM AND AVG FUNCTION	256
9.11 THE VARIANCE AND STDDEV FUNCTIONS	259
9.12 THE VAR_SAMP AND STDDEV_SAMP FUNCTIONS	261
9.13 THE BIT_AND, BIT_OR AND BIT_XOR FUNCTIONS	262
9.14 ANSWERS	262
10 SELECT STATEMENT: THE GROUP BY CLAUSE	266
10.1 INTRODUCTION	266
10.2 GROUPING ON ONE COLUMN	266
10.3 GROUPING ON TWO OR MORE COLUMNS	270
10.4 GROUPING ON EXPRESSIONS	272
10.5 GROUPING OF NULL VALUES	273
10.6 GROUPING WITH SORTING	273
10.7 GENERAL RULES FOR THE GROUP BY CLAUSE	274
10.8 THE GROUP_CONCAT FUNCTION	276
10.9 COMPLEX EXAMPLES WITH GROUP BY	278
10.10 GROUPING WITH WITH ROLLUP	282
10.11 ANSWERS	284
11 SELECT STATEMENT: THE HAVING CLAUSE	287
11.1 INTRODUCTION	287
11.2 EXAMPLES OF THE HAVING CLAUSE	288
11.3 A HAVING BUT NOT A GROUP BY CLAUSE	289
11.4 GENERAL RULE FOR THE HAVING CLAUSE	290
11.5 ANSWERS	291
12 SELECT STATEMENT: THE ORDER BY CLAUSE	293
12.1 INTRODUCTION	293
12.2 SORTING ON COLUMN NAMES	293
12.3 SORTING ON EXPRESSIONS	295
12.4 SORTING WITH SEQUENCE NUMBERS	296
12.5 SORTING IN ASCENDING AND DESCENDING ORDER	298
12.6 SORTING OF NULL VALUES	300
12.7 ANSWERS	300
13 SELECT STATEMENT: THE LIMIT CLAUSE	302
13.1 INTRODUCTION	302
13.2 GET THE TOP ?	304
13.3 SUBQUERIES WITH A LIMIT CLAUSE	307
13.4 LIMIT WITH AN OFFSET	309
13.5 THE SELECT OPTION SQL_CALC_FOUND_ROWS	310
13.6 ANSWERS	310
14 COMBINING TABLE EXPRESSIONS	312
14.1 INTRODUCTION	312
14.2 COMBINING WITH UNION	312
14.3 RULES FOR USING UNION	315
14.4 KEEPING DUPLICATE ROWS	317
14.5 SET OPERATORS AND THE NULL VALUE	318
14.6 ANSWERS	319
15 THE USER VARIABLE AND THE SET STATEMENT	320
15.1 INTRODUCTION	320
15.2 DEFINING VARIABLES WITH THE SET STATEMENT	320
15.3 DEFINING VARIABLES WITH THE SELECT STATEMENT	322
15.4 APPLICATION AREAS FOR USER VARIABLES	323
15.5 LIFE SPAN OF USER VARIABLES	324
15.6 THE DO STATEMENT	325
15.7 ANSWERS	326
16 THE HANDLER STATEMENT	327
16.1 INTRODUCTION	327
16.2 A SIMPLE EXAMPLE OF THE HANDLER STATEMENT	327
16.3 OPENING A HANDLER	328
16.4 BROWSING THROUGH THE ROWS OF A HANDLER	328
16.5 CLOSING A HANDLER	331
16.6 ANSWERS	332
17 UPDATING TABLES	333
17.1 INTRODUCTION	333
17.2 INSERTING NEW ROWS	333
17.3 POPULATING A TABLE WITH ROWS FROM ANOTHER TABLE	336
17.4 UPDATING VALUES IN ROWS	338
17.5 UPDATING VALUES IN MULTIPLE TABLES	343
17.6 SUBSTITUTING EXISTING ROWS	345
17.7 DELETING ROWS FROM A TABLE	346
17.8 DELETING ROWS FROM MULTIPLE TABLES	347
17.9 THE TRUNCATE STATEMENT	349
17.10 ANSWERS	349
18 LOADING AND UNLOADING DATA	351
18.1 INTRODUCTION	351
18.2 UNLOADING DATA	351
18.3 LOADING DATA	354
19 WORKING WITH XML DOCUMENTS	359
19.1 XML IN A NUTSHELL	359
19.2 STORING XML DOCUMENTS	360
19.3 QUERYING XML DOCUMENTS	362
19.4 QUERYING USING POSITIONS	368
19.5 THE EXTENDED NOTATION OF XPATH	369
19.6 XPATH EXPRESSIONS WITH CONDITIONS	371
19.7 CHANGING XML DOCUMENTS	372
PART III: CREATING DATABASE OBJECTS	374
20 CREATING TABLES	375
20.1 INTRODUCTION	375
20.2 CREATING NEW TABLES	375
20.3 DATA TYPES OF COLUMNS	377
20.4 ADDING DATA TYPE OPTIONS	386
20.5 CREATING TEMPORARY TABLES	391
20.6 WHAT IF THE TABLE ALREADY EXISTS?	392
20.7 COPYING TABLES	393
20.8 NAMING TABLES AND COLUMNS	397
20.9 COLUMN OPTIONS: DEFAULT AND COMMENT	398
20.10 TABLE OPTIONS	399
20.11 THE CSV STORAGE ENGINE	405
20.12 TABLES AND THE CATALOG	407
20.13 ANSWERS	409
21 SPECIFYING INTEGRITY CONSTRAINTS	411
21.1 INTRODUCTION	411
21.2 PRIMARY KEYS	412
21.3 ALTERNATE KEYS	415
21.4 FOREIGN KEYS	416
21.5 THE REFERENCING ACTION	420
21.6 CHECK INTEGRITY CONSTRAINTS	422
21.7 NAMING INTEGRITY CONSTRAINTS	424
21.8 DELETING INTEGRITY CONSTRAINTS	425
21.9 INTEGRITY CONSTRAINTS AND THE CATALOG	425
21.10 ANSWERS	425
22 CHARACTER SETS AND COLLATIONS	428
22.1 INTRODUCTION	428
22.2 AVAILABLE CHARACTER SETS AND COLLATIONS	429
22.3 ASSIGNING CHARACTER SETS TO COLUMNS	431
22.4 ASSIGNING COLLATIONS TO COLUMNS	432
22.5 EXPRESSIONS WITH CHARACTER SETS AND COLLATIONS	434
22.6 SORTING AND GROUPING WITH COLLATIONS	436
22.7 THE COERCIBILITY OF EXPRESSIONS	438
22.8 RELATED SYSTEM VARIABLES	439
22.9 CHARACTER SETS AND THE CATALOG	440
22.10 ANSWERS	440
23 THE ENUM AND SET TYPES	441
23.1 INTRODUCTION	441
23.2 THE ENUM DATA TYPE	441
23.3 THE SET DATA TYPE	444
23.4 ANSWERS	450
24 CHANGING AND DROPPING TABLES	451
24.1 INTRODUCTION	451
24.2 DELETING ENTIRE TABLES	451
24.3 RENAMING TABLES	452
24.4 CHANGING THE TABLE STRUCTURE	453
24.5 CHANGING COLUMNS	454
24.6 CHANGING INTEGRITY CONSTRAINTS	458
24.7 ANSWERS	459
25 USING INDEXES	461
25.1 INTRODUCTION	461
25.2 ROWS, TABLES, AND FILES	461
25.3 HOW DOES AN INDEX WORK?	462
25.4 PROCESSING A SELECT STATEMENT: THE STEPS	466
25.5 CREATING INDEXES	469
25.6 DEFINING INDEXES TOGETHER WITH THE TABLES	471
25.7 DROPPING INDEXES	472
25.8 INDEXES AND PRIMARY KEYS	473
25.9 THE BIG PLAYERS_XXL TABLE	474
25.10 CHOOSING COLUMNS FOR INDEXES	475
25.11 INDEXES AND THE CATALOG	480
25.12 ANSWERS	481
26 VIEWS	483
26.1 INTRODUCTION	483
26.2 CREATING VIEWS	483
26.3 THE COLUMN NAMES OF VIEWS	486
26.4 UPDATING VIEWS: WITH CHECK OPTION	487
26.5 OPTIONS OF VIEWS	488
26.6 DELETING VIEWS	490
26.7 VIEWS AND THE CATALOG	490
26.8 RESTRICTIONS ON UPDATING VIEWS	491
26.9 PROCESSING VIEW STATEMENTS	492
26.10 APPLICATION AREAS FOR VIEWS	495
26.11 ANSWERS	499
27 CREATING DATABASES	501
27.1 INTRODUCTION	501
27.2 DATABASES AND THE CATALOG	501
27.3 CREATING DATABASES	502
27.4 CHANGING DATABASES	503
27.5 DROPPING DATABASES	504
28 USERS AND DATA SECURITY	505
28.1 INTRODUCTION	505
28.2 ADDING AND REMOVING USERS	506
28.3 CHANGING THE NAMES OF USERS	508
28.4 CHANGING PASSWORDS	508
28.5 GRANTING TABLE AND COLUMN PRIVILEGES	509
28.6 GRANTING DATABASE PRIVILEGES	511
28.7 GRANTING USER PRIVILEGES	513
28.8 PASSING ON PRIVILEGES: WITH GRANT OPTION	516
28.9 RESTRICTING PRIVILEGES	517
28.10 RECORDING PRIVILEGES IN THE CATALOG	517
28.11 REVOKING PRIVILEGES	519
28.12 SECURITY OF AND THROUGH VIEWS	521
28.13 ANSWERS	522
29 STATEMENTS FOR TABLE MAINTENANCE	524
29.1 INTRODUCTION	524
29.2 THE ANALYZE TABLE STATEMENT	524
29.3 THE CHECKSUM TABLE STATEMENT	526
29.4 THE OPTIMIZE TABLE STATEMENT	527
29.5 THE CHECK TABLE STATEMENT	527
29.6 THE REPAIR TABLE STATEMENT	529
29.7 THE BACKUP TABLE STATEMENT	530
29.8 THE RESTORE TABLE STATEMENT	530
30 THE SHOW, DESCRIBE, AND HELP STATEMENTS	532
30.1 INTRODUCTION	532
30.2 OVERVIEW OF SHOW STATEMENTS	532
30.3 ADDITIONAL SHOW STATEMENTS	535
30.4 THE DESCRIBE STATEMENT	536
30.5 THE HELP STATEMENT	536
PART IV: PROCEDURAL DATABASE OBJECTS	538
31 STORED PROCEDURES	539
31.1 INTRODUCTION	539
31.2 AN EXAMPLE OF A STORED PROCEDURE	539
31.3 THE PARAMETERS OF A STORED PROCEDURE	541
31.4 THE BODY OF A STORED PROCEDURE	542
31.5 LOCAL VARIABLES	543
31.6 THE SET STATEMENT	545
31.7 FLOW CONTROL STATEMENTS	546
31.8 CALLING STORED PROCEDURES	551
31.9 QUERYING DATA WITH SELECT INTO	553
31.10 ERROR MESSAGES, HANDLERS AND CONDITIONS	556
31.11 RETRIEVING DATA WITH A CURSOR	560
31.12 INCLUDING SELECT STATEMENTS WITHOUT CURSORS	564
31.13 STORED PROCEDURES AND USER VARIABLES	565
31.14 CHARACTERISTICS OF STORED PROCEDURES	565
31.15 STORED PROCEDURES AND THE CATALOG	567
31.16 REMOVING STORED PROCEDURES	568
31.17 SECURITY WITH STORED PROCEDURES	569
31.18 ADVANTAGES OF STORED PROCEDURES	569
32 STORED FUNCTIONS	571
32.1 INTRODUCTION	571
32.2 EXAMPLES OF STORED FUNCTIONS	572
32.3 MORE ON STORED FUNCTIONS	576
32.4 REMOVING STORED FUNCTIONS	577
33 TRIGGERS	578
33.1 INTRODUCTION	578
33.2 AN EXAMPLE OF A TRIGGER	578
33.3 MORE COMPLEX EXAMPLES	581
33.4 TRIGGERS AS INTEGRITY CONSTRAINTS	584
33.5 REMOVING TRIGGERS	586
33.6 TRIGGERS AND THE CATALOG	586
33.7 ANSWERS	586
34 EVENTS	588
34.1 WHAT IS AN EVENT?	588
34.2 CREATING EVENTS	589
34.3 PROPERTIES OF EVENTS	595
34.4 CHANGING EVENTS	596
34.5 REMOVING EVENTS	597
34.6 EVENTS AND PRIVILEGES	597
34.7 EVENTS AND THE CATALOG	598
PART V: PROGRAMMING WITH SQL	600
35 MYSQL AND PHP	601
35.1 INTRODUCTION	601
35.2 LOGGING ON TO MYSQL	601
35.3 SELECTING A DATABASE	602
35.4 CREATING AN INDEX	603
35.5 RETRIEVING ERROR MESSAGES	605
35.6 MULTIPLE CONNECTIONS WITHIN ONE SESSION	606
35.7 SQL STATEMENTS WITH PARAMETERS	607
35.8 SELECT STATEMENT WITH ONE ROW	608
35.9 SELECT STATEMENT WITH MULTIPLE ROWS	609
35.10 SELECT STATEMENT WITH NULL VALUES	612
35.11 QUERYING DATA ABOUT EXPRESSIONS	613
35.12 QUERYING THE CATALOG	614
35.13 REMAINING MYSQL FUNCTIONS	616
36 DYNAMIC SQL WITH PREPARED STATEMENT	618
36.1 INTRODUCTION	618
36.2 WORKING WITH PREPARED SQL STATEMENTS	618
36.3 PREPARED STATEMENTS WITH USER VARIABLES	620
36.4 PREPARED STATEMENTS WITH PARAMETERS	621
36.5 PREPARED STATEMENTS IN STORED PROCEDURES	621
37 TRANSACTIONS AND MULTI-USER USAGE	624
37.1 INTRODUCTION	624
37.2 WHAT IS A TRANSACTION?	624
37.3 STARTING OF TRANSACTIONS	629
37.4 SAVEPOINTS	629
37.5 STORED PROCEDURES AND TRANSACTIONS	631
37.6 PROBLEMS WITH MULTI-USER USAGE	632
37.7 LOCKING	635
37.8 DEADLOCKS	636
37.9 THE LOCK TABLE AND UNLOCK TABLE STATEMENTS	636
37.10 THE ISOLATION LEVEL	637
37.11 WAITING FOR A LOCK	639
37.12 MOMENT OF PROCESSING STATEMENTS	639
37.13 WORKING WITH APPLICATION LOCKS	640
37.14 ANSWERS	641
APPENDIX A SYNTAX OF SQL	643
A.1 INTRODUCTION	643
A.2 THE BNF NOTATION	643
A.3 RESERVED WORDS IN SQL	646
A.4 SYNTAX DEFINITIONS OF SQL STATEMENTS	649
APPENDIX B SCALAR FUNCTIONS	687
APPENDIX C SYSTEM VARIABLES	728
APPENDIX D BIBLIOGRAPHY	735
INDEX	2

Library of Congress Subject Headings for this publication:

SQL (Computer program language).
MySQL (Electronic resource).