دانلود کتاب MySQL 5.0 Certification Study Guide: The authoritative study guide to prepare for and pass the MySQL Certified Developer and MySQL Certified DBA exams

فهرست مطالب :

Cover......Page 1
Table of Contents......Page 9
About This Book......Page 28
Other Required Reading......Page 29
Certification Information at www.mysql.com......Page 30
Conventions Used in This Book......Page 31
Going to the Exam......Page 33
Taking the Exam......Page 35
Answering Questions......Page 36
After the Exam......Page 38
Interpreting DESCRIBE Output......Page 39
Sample Tables......Page 41
MySQL Developer Exams......Page 44
MySQL Developer I Exam......Page 46
1.1 General MySQL Architecture......Page 48
1.2 Invoking Client Programs......Page 49
1.3 Server SQL Modes......Page 58
2.1 Using mysql Interactively......Page 62
2.3 The mysql Prompts......Page 65
2.4 Using Editing Keys in mysql......Page 66
2.6 mysql Output Formats......Page 67
2.7 Client Commands and SQL Statements......Page 68
2.8 Using Server-Side Help......Page 69
2.9 Using the --safe-updates Option......Page 71
3.1 MySQL Query Browser Capabilities......Page 72
3.2 Using MySQL Query Browser......Page 73
3.3 Using the Query Window......Page 74
3.4 The MySQL Table Editor......Page 77
3.5 Connection Management......Page 78
3.6 The Options Dialog......Page 80
4.1 MySQL Client Interfaces......Page 82
4.3 MySQL Connector/J......Page 83
4.4 MySQL Connector/NET......Page 84
5.1 Data Type Overview......Page 86
5.2 Numeric Data Types......Page 88
5.4 String Data Types......Page 91
5.5 Temporal Data Types......Page 99
5.6 Column Attributes......Page 109
5.7 Using the AUTO_INCREMENT Column Attribute......Page 111
5.8 Handling Missing or Invalid Data Values......Page 117
6.1 Identifier Syntax......Page 124
6.3 Using Qualified Names......Page 125
6.4 Using Reserved Words as Identifiers......Page 126
7.1 Database Properties......Page 130
7.2 Creating Databases......Page 131
7.4 Dropping Databases......Page 132
7.5 Obtaining Database Metadata......Page 133
8.1 Table Properties......Page 136
8.2 Creating Tables......Page 138
8.3 Altering Tables......Page 144
8.4 Dropping Tables......Page 147
8.5 Emptying Tables......Page 148
8.6 Indexes......Page 149
8.7 Dropping Indexes......Page 156
8.8 Obtaining Table and Index Metadata......Page 157
9.1 Using SELECT to Retrieve Data......Page 162
9.2 Specifying Which Columns to Retrieve......Page 163
9.3 Specifying Which Rows to Retrieve......Page 166
9.4 Aggregating Results......Page 177
9.5 Grouping Results......Page 183
9.6 Using UNION......Page 192
10.1 Components of SQL Expressions......Page 194
10.2 Numeric Expressions......Page 196
10.3 String Expressions......Page 197
10.4 Temporal Expressions......Page 206
10.5 NULL Values......Page 207
10.6 Functions in SQL Expressions......Page 208
10.7 Comments in SQL Statements......Page 219
11.1 Update Operations......Page 220
11.2 The INSERT Statement......Page 221
11.3 The REPLACE Statement......Page 226
11.4 The UPDATE Statement......Page 228
11.5 The DELETE and TRUNCATE TABLE Statements......Page 231
11.6 Privileges Required for Update Statements......Page 233
MySQL Developer II Exam......Page 234
12.1 Overview......Page 236
12.2 Writing Inner Joins......Page 237
12.3 Writing Outer Joins......Page 244
12.4 Resolving Name Clashes Using Qualifiers and Aliases......Page 249
12.5 Multiple-Table UPDATE and DELETE Statements......Page 252
13.1 Types of Subqueries......Page 254
13.2 Subqueries as Scalar Expressions......Page 255
13.3 Correlated Subqueries......Page 256
13.4 Comparing Subquery Results to Outer Query Columns......Page 257
13.5 Comparison Using Row Subqueries......Page 263
13.6 Using Subqueries in the FROM Clause......Page 264
13.7 Converting Subqueries to Joins......Page 265
13.8 Using Subqueries in Updates......Page 268
14.1 Reasons to Use Views......Page 270
14.2 Creating Views......Page 271
14.4 Dropping Views......Page 280
14.6 Obtaining View Metadata......Page 281
14.7 Privileges Required for Views......Page 283
15.1 Import and Export Operations......Page 284
15.2 Importing and Exporting Using SQL......Page 285
15.3 Importing and Exporting Data from the Command Line......Page 294
16.1 User Variable Syntax......Page 300
16.2 User Variable Properties......Page 301
17.2 Using Prepared Statements from the mysql Client......Page 302
17.3 Preparing a Statement......Page 303
17.4 Executing a Prepared Statement......Page 304
17.5 Deallocating Prepared Statements......Page 306
18 Stored Procedures and Functions......Page 308
18.1 Benefits of Stored Routines......Page 309
18.2 Differences Between Stored Procedures and Functions......Page 310
18.4 Defining Stored Routines......Page 311
18.5 Creating Stored Routines......Page 312
18.6 Altering Stored Routines......Page 329
18.8 Invoking Stored Routines......Page 330
18.9 Obtaining Stored Routine Metadata......Page 331
18.10 Stored Routine Privileges and Execution Security......Page 332
19.1 Reasons to Use Triggers......Page 334
19.2 Trigger Concepts......Page 335
19.3 Creating a Trigger......Page 336
19.6 Destroying a Trigger......Page 338
19.7 Privileges Required for Triggers......Page 339
20.1 Overview of Metadata Access Methods......Page 340
20.2 Using INFORMATION_SCHEMA to Obtain Metadata......Page 341
20.3 Using SHOW and DESCRIBE to Obtain Metadata......Page 344
20.4 Using mysqlshow to Obtain Metadata......Page 349
21.1 Interpreting Error Messages......Page 352
21.2 The SHOW WARNINGS Statement......Page 353
21.4 The perror Utility......Page 356
22.1 Overview of Optimization Principles......Page 358
22.2 Using Indexes for Optimization......Page 359
22.3 General Query Enhancement......Page 364
22.4 Choosing Appropriate Storage Engines......Page 371
22.5 Normalization......Page 372
MySQL DBA Exams......Page 374
MySQL DBA I Exam......Page 376
23.1 Client/Server Overview......Page 378
23.2 Communication Protocols......Page 379
23.3 The SQL Parser and Storage Engine Tiers......Page 381
23.5 How MySQL Uses Memory......Page 382
24.1 Types of MySQL Distributions......Page 386
24.2 Starting and Stopping MySQL Server on Windows......Page 388
24.3 Starting and Stopping MySQL Server on Unix......Page 392
24.4 Runtime MySQL Configuration......Page 395
24.5 Log and Status Files......Page 399
24.6 Loading Time Zone Tables......Page 402
24.7 Security-Related Configuration......Page 403
24.9 Upgrading MySQL......Page 404
25.1 Overview of Administrative Clients......Page 406
25.2 MySQL Administrator......Page 407
25.4 mysqladmin......Page 408
25.6 mysqldump......Page 409
25.7 Client Program Limitations......Page 410
26.1 MySQL Administrator Capabilities......Page 412
26.2 Using MySQL Administrator......Page 413
26.3 Server Monitoring Capabilities......Page 415
26.4 Server Configuration......Page 418
26.5 Backup and Restore Capabilities......Page 420
26.6 MySQL Administrator System Tray Monitor......Page 421
27.1 Performance Issues......Page 422
27.2 Choosing Data Types for Character Columns......Page 423
28.1 Locking Concepts......Page 426
28.2 Explicit Table Locking......Page 428
28.3 Advisory Locking......Page 430
29.1 MySQL Storage Engines......Page 432
29.2 The MyISAM Engine......Page 435
29.3 The MERGE Engine......Page 439
29.4 The InnoDB Engine......Page 441
29.5 The MEMORY Engine......Page 459
29.6 The FEDERATED Engine......Page 460
29.7 The Cluster Storage Engine......Page 462
29.8 Other Storage Engines......Page 463
30.1 Types of Table Maintenance Operations......Page 464
30.2 SQL Statements for Table Maintenance......Page 465
30.3 Client and Utility Programs for Table Maintenance......Page 467
30.4 Repairing InnoDB Tables......Page 470
30.5 Enabling MyISAM Auto-Repair......Page 471
31.1 INFORMATION_SCHEMA Access Syntax......Page 474
31.2 INFORMATION_SCHEMA Versus SHOW......Page 476
31.3 Limitations of INFORMATION_SCHEMA......Page 477
32.1 Introduction......Page 478
32.2 Binary Versus Textual Backups......Page 479
32.3 Making Binary Backups......Page 480
32.4 Making Text Backups......Page 484
32.5 Backing Up Log and Status Files......Page 488
32.8 Data Recovery......Page 489
33.1 Using Stored Routines and Triggers for Security Purposes......Page 496
33.2 Using Stored Routines to Enhance Performance......Page 498
MySQL DBA II EXAM......Page 494
34.1 User Account Management......Page 500
34.2 Client Access Control......Page 513
35.1 Security Issues......Page 518
35.2 Operating System Security......Page 519
35.3 Filesystem Security......Page 520
35.4 Log Files and Security......Page 522
35.5 Network Security......Page 523
35.6 FEDERATED Table Security......Page 527
36.1 Upgrading the Privilege Tables......Page 528
36.2 Security-Related SQL Mode Values......Page 529
37.1 Identifying Candidates for Query Analysis......Page 532
37.2 Using EXPLAIN to Analyze Queries......Page 534
37.4 MyISAM Index Caching......Page 546
38.1 General Table Optimizations......Page 550
38.2 Normalization......Page 552
38.3 MyISAM-Specific Optimizations......Page 558
38.4 InnoDB-Specific Optimizations......Page 568
38.5 MERGE-Specific Optimizations......Page 569
38.6 MEMORY-Specific Optimizations......Page 570
39.1 Interpreting mysqld Server Information......Page 572
39.2 Measuring Server Load......Page 579
39.3 Tuning Memory Parameters......Page 580
39.4 Using the Query Cache......Page 586
40.2 Using the Error Log for Diagnostic Purposes......Page 590
40.3 Using The Slow Query Log for Diagnostic Purposes......Page 591
41.1 Choosing Hardware for MySQL Use......Page 594
41.2 Configuring Disks for MySQL Use......Page 595
41.4 Optimizing the Operating System for MySQL Use......Page 599
42.1 Using Multiple Servers......Page 602
42.2 Replication......Page 604
Appendixes......Page 612
A: References......Page 614
B: Other Offers......Page 616
Index......Page 618
A......Page 619
B......Page 621
C......Page 622
D......Page 626
E......Page 630
F......Page 631
G......Page 632
H - I......Page 633
L......Page 637
M......Page 639
N......Page 645
O......Page 646
P......Page 647
Q......Page 649
R......Page 650
S......Page 652
T......Page 664
U......Page 667
V......Page 669
X - Y - Z......Page 670