MySQL Certification : Developer's Guide,9780672326325

MySQL Certification : Developer's Guide

by ; ;
ISBN13: 9780672326325
ISBN10: 0672326329
Format: Paperback
Pub. Date: 2004-01-01
Publisher(s): MySQL Press
Upgraded Edition: Click here!
List Price: $54.01

Rent Book

Select for Price
Add to Cart
There was a problem. Please try again later.

New Book

We're Sorry
Sold Out

Used Book

We're Sorry
Sold Out

eBook

We're Sorry
Not Available

Buy from our Marketplace starting at $2.49

How Marketplace Works:

  • This item is offered by an independent seller and not shipped from our warehouse
  • Item details like edition and cover design may differ from our description; see seller's comments before ordering.
  • Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
  • Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
  • Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.

Summary

Covers the principles, concepts, and techniques of MySQL to prepare for the core certification examinations.

Table of Contents

1 MySQL and MySQL AB 15(8)
1.1 The Difference Between MySQL and MySQL AB
15(1)
1.2 How MySQL AB Operates
16(1)
1.3 MySQL Core Values
16(1)
1.4 MySQL Dual Licensing
17(2)
1.4.1 Use of MySQL Under the General Public License
18(1)
1.4.2 Use of MySQL with a Commercial License
18(1)
1.5 Organization and Structure of the MySQL Reference Manual
19(2)
1.5.1 General Reference Manual Organization
19(1)
1.5.2 MySQL Reference Manual Change Notes
20(1)
1.6 The MySQL Mailing Lists
21(1)
1.7 Exercises
21(2)
2 MySQL Software 23(6)
2.1 Major Program Components Used in MySQL
23(1)
2.2 Major Operating System Families Supported by MySQL
24(1)
2.3 Differences Between Major MySQL Distributions
25(1)
2.4 Available MySQL Client Interfaces
26(1)
2.5 Exercises
27(2)
3 Using MySQL Client Programs 29(30)
3.1 Invoking Command-Line Client Programs
30(6)
3.1.1 Specifying Command-Line Options
31(5)
3.1.2 Selecting a Default Database
36(1)
3.2 The mysgl Client Program
36(7)
3.2.1 Using mysgl Interactively
37(1)
3.2.2 Using Editing Keys in mysgl
38(1)
3.2.3 Statement Terminators
39(1)
3.2.4 The mysgl Prompts
39(1)
3.2.5 Using Script Files with mysgl
40(1)
3.2.6 mysgl Output Formats
41(1)
3.2.7 mysgl Client Commands and SQL Statements
41(2)
3.2.8 Using the --safe-updates Option
43(1)
3.3 Using mysglimport
43(3)
3.4 Using mysgldump
46(3)
3.4.1 General mysgldump Operation
46(2)
3.4.2 Reloading mysgldump Output
48(1)
3.5 Checking Tables with mysgl check and my; samchk
49(3)
3.6 Using MySQLCC
52(1)
3.7 MySQL Connectivity Drivers
53(1)
3.7.1 Using MySQL Connector/ODBC
53(1)
3.7.2 Using MySQL Connector/J
53(1)
3.8 Exercises
53(6)
4 Data Definition Language 59(82)
4.1 General Database and Table Properties
60(1)
4.2 Storage Engines and Table Types
61(2)
4.2.1 MyISAM Tables
61(1)
4.2.2 InnoDB Tables
61(1)
4.2.3 MERGE Tables
62(1)
4.2.4 BDB (Berkeley DB) Tables
62(1)
4.2.5 HEAP (MEMORY) Tables
62(1)
4.3 Limits on Number and Size of Database Components
63(1)
4.4 Identifier Syntax
64(1)
4.4.1 Legal Characters for Identifiers
64(1)
4.4.2 Using Qualifiers for Table and Column Names
65(1)
4.5 CREATE DATABASE and DROP DATABASE
65(1)
4.6 CREATE TABLE
66(2)
4.7 DROP TABLE
68(1)
4.8 ALTER TABLE
69(4)
4.8.1 Adding and Dropping Columns
70(1)
4.8.2 Modifying Existing Columns
71(1)
4.8.3 Renaming a Table
72(1)
4.8.4 Specifying Multiple Alterations
72(1)
4.9 Creating and Dropping Indexes
73(6)
4.9.1 Defining Indexes at Table-Creation Time
73(2)
4.9.2 Creating and Using Primary Keys
75(2)
4.9.3 Modifying Indexes of Existing Tables
77(2)
4.10 Column Types
79(21)
4.10.1 Numeric Column Types
80(2)
4.10.2 String Column Types
82(6)
4.10.3 Date and Time Column Types
88(2)
4.10.4 Column Options
90(2)
4.10.5 Using the AUTO_INCREMENT Column Option
92(5)
4.10.6 Automatic Type Conversion and Value Clipping
97(3)
4.11 Using SHOW and DESCRIBE to Review Table Structures
100(2)
4.11.1 Listing Database or Table Names
100(2)
4.11.2 Getting Table Information
102(1)
4.12 Exercises
102(39)
5 The SELECT Statement 141(42)
5.1 Identifying What Values to Display
142(3)
5.1.1 Using Aliases to Name Output Columns
144(1)
5.1.2 Identifying the Database Containing a Table
144(1)
5.1.3 Retrieving Records from Multiple Tables
145(1)
5.2 Restricting a Selection Using WHERE
145(1)
5.3 Using ORDER BY to Sort Query Results
146(6)
5.3.1 The Natural Sort Order of Column Types
149(3)
5.4 Limiting a Selection Using LIMIT
152(2)
5.5 Aggregate Functions, GROUP BY, and HAVING
154(8)
5.5.1 Computing Summary Values
154(4)
5.5.1.2 The MIN() and MAX() Aggregate Functions
155(3)
5.5.2 Grouping Rows with GROUP BY
158(2)
5.5.3 GROUP BY and Sorting
160(1)
5.5.4 Selecting Groups with HAVING
160(2)
5.6 Using DISTINCT to Eliminate Duplicates
162(2)
5.7 Concatenating SELECT Results with UNION
164(2)
5.8 Exercises
166(17)
6 Basic SQL 183(48)
6.1 Using SQL Expressions and Functions
183(7)
6.1.1 Case Sensitivity in String Comparisons
185(5)
6.2 Using LIKE for Pattern Matching
190(3)
6.3 Using IN() to Test Membership in a List of Values
193(1)
6.4 Case Sensitivity of Identifiers and Reserved Words
194(1)
6.5 Using Reserved Words as Identifiers
194(2)
6.6 NULL Values
196(5)
6.6.1 NULL Values and Column Definitions
196(1)
6.6.2 NULL Values and NOT NULL Columns
197(1)
6.6.3 NULL Values in Expressions and Comparisons
197(2)
6.6.4 NULL Values and Aggregate Functions
199(2)
6.7 Comments in SQL Statements
201(1)
6.8 Exercises
202(29)
7 Update Statements 231(26)
7.1 The INSERT and REPLACE Statements
232(6)
7.1.1 The INSERT Statement
232(3)
7.1.2 The REPLACE Statement
235(2)
7.1.3 Handling Illegal Values
237(1)
7.1.4 Handling Duplicate Key Values
237(1)
7.2 The UPDATE Statement
238(3)
7.2.1 Preventing Dangerous UPDATE Statements
240(1)
7.2.2 Using UPDATE with ORDER BY and LIMIT
240(1)
7.2.3 Multiple-Table UPDATE Statements
241(1)
7.3 The DELETE and TRUNCATE TABLE Statements
241(2)
7.3.1 Using DELETE with ORDER BY and LIMIT
242(1)
7.3.2 Multiple-Table DELETE Statements
242(1)
7.4 Exercises
243(14)
8 Joins 257(42)
8.1 Writing Timer Joins
258(7)
8.1.1 Writing Inner Joins with the Comma Operator
258(7)
8.1.2 Writing Inner Joins with INNER JOIN
265(1)
8.2 Writing Outer Joins
265(5)
8.2.1 Writing LEFT JOIN Queries
266(2)
8.2.2 Writing RIGHT JOIN Queries
268(2)
8.3 Converting Subqueries to Joins
270(3)
8.3.1 Converting Subqueries to Inner Joins
270(2)
8.3.2 Converting Subqueries to Outer Joins
272(1)
8.4 Resolving Name Clashes Using Qualifiers and Aliases
273(3)
8.4.1 Qualifying Column Names
273(2)
8.4.2 Qualifying and Aliasing Table Names
275(1)
8.5 Multiple-Table UPDATE and DELETE Statements
276(1)
8.6 Exercises
277(22)
9 Importing and Exporting Data 299(18)
9.1 The LOAD DATA INFILE Statement
299(6)
9.1.1 Specifying the Datafile Location
300(2)
9.1.2 Loading Specific Table Columns
302(1)
9.1.3 Skipping Datafile Lines
303(1)
9.1.4 LOAD DATA INFILE and Duplicate Records
303(1)
9.1.5 Interpreting the Result of a LOAD DATA INFILE Statement
304(1)
9.1.6 Privileges Needed for LOAD DATA INFILE
304(1)
9.1.7 Efficiency of LOAD DATA INFILE
305(1)
9.2 The SELECT...INTO OUTFILE Statement
305(1)
9.3 Datafile Format Specifiers
306(2)
9.4 Importing and Exporting NULL Values
308(1)
9.5 Exercises
309(8)
10 MySQL Architecture 317(1)
10.1 Client/Server Overview
318(1)
10.2 Choosing the Right Client
318(275)
10.2.1 The mysgl admin Client
319(1)
10.2.2 The mysgl Client
319(1)
10.2.3 The MySQLCC Client
320(1)
10.2.4 Other Administrative Clients
320(1)
10.2.5 Client Program Limitations
321(1)
10.2.6 The perror Utility
321(1)
10.3 Connecting the Client to the Server
322(1)
10.4 How MySQL Uses Disk Space
323(1)
10.5 How MySQL Uses Memory
323(2)
10.6 Log and Status Files
325(1)
10.7 MySQL Storage Engines
326(1)
10.7.1 MyISAM Tables
327(1)
10.7.2 InnoDB Tables
327(1)
10.7.3 MERGE Tables
328(1)
10.7.4 BDB (Berkeley DB) Tables
328(1)
10.7.5 HEAP (MEMORY) Tables
328(1)
10.7.6 Specifying the Storage Engine for a Table
329(1)
10.7.7 Using TEMPORARY Tables
330(1)
10.8 Exercises
330(13)
11 MySQL Installation and Configuration 343(1)
11.1 Running MySQL on Windows
343(1)
11.1.1 Installing the MySQL Distribution
343(1)
11.1.2 Starting and Stopping the MySQL Server
344(2)
11.2 Running MySQL on Unix
346(1)
11.2.1 Installing MySQL Using RPM Files
346(1)
11.2.2 Installing MySQL Using a tar File
347(1)
11.2.3 Starting and Stopping the MySQL Server
348(2)
11.3 Runtime MySQL Configuration
350(2)
11.4 Compiling MySQL from a Source Distribution
352(1)
11.5 Upgrading MySQL
353(1)
11.6 Optimizing the Operating System for MySQL Use
354(1)
11.7 Configuring Disks for MySQL Use
355(2)
11.7.1 Moving Databases Using Symbolic Links
357(1)
11.8 Choosing Hardware for MySQL Use
357(1)
11.9 Exercises
358(3)
12 Security Issues 361(1)
12.1 Securing MySQL
362(1)
12.1.1 Securing MySQL at the Filesystem Level
362(2)
12.1.2 Securing the Initial MySQL Accounts
364(3)
12.2 User Account Management
367(1)
12.2.1 Types of Privileges That MySQL Supports
368(1)
12.2.2 The Grant Tables
369(2)
12.2.3 Granting and Revoking Privileges
371(5)
12.2.4 Changing Account Passwords
376(1)
12.2.5 Specifying Resource Limits
377(1)
12.3 Client Access Control
378(1)
12.3.1 Connection Request Checking
379(2)
12.3.2 Statement Privilege Checking
381(1)
12.3.3 Resource Limit Checking
381(1)
12.3.4 Disabling Client Access Control
381(1)
12.4 Exercises
382(7)
13 Optimizing for Query Speed 389(1)
13.1 Index Optimization and Index Usage
390(1)
13.1.1 Types of Indexes
390(1)
13.1.2 Obtaining Table Index Information
391(1)
13.1.3 Using Indexes
392(4)
13.1.4 FULLTEXT Indexes
396(1)
13.2 Using EXPLAIN to Analyze Queries
397(1)
13.2.1 Identifying Candidates for Query Analysis
398(1)
13.2.2 How EXPLAIN Works
399(2)
13.2.3 Analyzing a Query
401(4)
13.2.4 EXPLAIN Output Columns
405(4)
13.3 General Query Enhancement
409(2)
13.3.1 Optimizing Queries by Limiting Output
411(1)
13.3.2 Optimizing Updates
412(1)
13.3.3 Using Scheduling Modifiers
413(1)
13.4 Optimizing the Logical Database Structure
413(1)
13.4.1 Choosing Appropriate Table Types
414(1)
13.4.2 Using Summary Tables
415(3)
13.5 Exercises
418(17)
14 MyISAM Tables 435(1)
14.1 Special MyISAM Features
436(1)
14.1.1 MyISAM Table Portability
436(1)
14.1.2 Specifying MyISAM Maximum Row Count
437(2)
14.1.3 MyISAM Table Symlinking
439(1)
14.1.4 Using MyISAM RAID Tables
440(1)
14.2 MyISAM-Specific Optimizations
441(1)
14.2.1 MyISAM Storage Formats
441(1)
14.2.2 Using Compressed MyISAM Tables
442(3)
14.3 MyISAM Locking Strategies
445(1)
14.3.1 MyISAM Locking Characteristics
446(1)
14.3.2 Query Scheduling Modifiers ..e
447(1)
14.4 MyISAM Backup and Recovery
448(1)
14.4.1 Using mysgldump
448(2)
14.4.2 Using mysgl hotcopy
450(1)
14.4.3 Using BACKUP TABLE and RESTORE TABLE
451(1)
14.5 Checking and Repairing MyISAM Tables
452(1)
14.6 MyISAM Table Maintenance
453(1)
14.7 Exercises
454(17)
15 InnoDB Tables 471(1)
15.1 The InnoDB Tablespace and Logs
472(1)
15.1.1 InnoDB Tablespace Portability
473(1)
15.2 InnoDB Transaction Support
473(1)
15.2.1 Performing Transactions
474(1)
15.2.2 Transaction Isolation Levels
475(2)
15.2.3 Setting the Isolation Level
477(1)
15.3 How InnoDB Uses Locks
478(1)
15.4 InnoDB-Specific Optimizations
479(1)
15.5 InnoDB Backup and Recovery
480(1)
15.6 Checking and Repairing InnoDB Tables
481(1)
15.7 InnoDB Maintenance
482(1)
15.7.1 Configuring the InnoDB Tablespace
482(3)
15.7.2 Configuring InnoDB Buffers and Logs
485(1)
15.7.3 Monitoring InnoDB
486(3)
15.8 Exercises
489(14)
16 Advanced Server Features 503(1)
16.1 Interpreting mysgid Server Information
503(1)
16.1.1 Accessing Server Configuration Information
504(3)
16.1.2 Accessing Server Status Information
507(2)
16.1.3 Measuring Server Load
509(2)
16.1.4 Accessing Server Error Messages
511(1)
16.2 Tuning Memory Parameters
512(1)
16.2.1 Global (Serverwide) Variables
513(3)
16.2.2 Per-Client Variables
516(1)
16.3 Using the Query Cache
517(2)
16.4 Using Multiple Servers
519(2)
16.5 Replication
521(1)
16.6 Exercises
522(17)
A Quick Reference 539(1)
A.1 SQL Statements
539(32)
A.2 SQL Functions
571(15)
A.3 Column Types
586(4)
A.4 Server System Variables
590(3)
B MySQL Certification Candidate Guide 593(6)
B.1 What Is a MySQL Certification?
593(1)
B.1.1 Benefits to Individuals
593(1)
B.1.2 Benefits to Companies
594(1)
B.2 Other Sources of Information
594(1)
B.3 The MySQL Certification Program
595(1)
B.3.1 The MySQL Certification Levels
595(1)
B.4 Registering for Exams
595(2)
B.4.1 Exam Prices
595(1)
B.4.2 Beta Exams
596(1)
B.4.3 How to Register for or Reschedule Exams
596(1)
B.4.4 Retaking Exams
596(1)
B.5 Preparing for the Exams
597(1)
B.6 How the Exams Proceed
597(1)
B.7 After the Exam
598(1)
B.8 Staying Updated
599(1)
B.9 How Long Are the Certifications Valid?
599(1)
B.10 MySQL 4 Core Certification
599
B.10.1 When Should I Certify?
600(1)
B.10.2 What Knowledge Is Tested?
600(1)
B.10.3 Exam Contents-MySQL Core Certification
600(3)
B.11 Certified MySQL 4 Professional
603(1)
B.11.1 When Should I Certify?
603(1)
B.11.2 What Knowledge Is Tested?
603(1)
B.11.3 Exam Contents-Certified MySQL Professional
603(2)
B.12 Pearson VUE Contact Information
605(1)
B.13 MySQL AB Certification NDA and LUA
606(1)
B.13.1 MySQL AB Certification Non-Disclosure and Logo Usage Agreement
607(1)
B.13.2 Agreement
607(4)
Index 611

An electronic version of this book is available through VitalSource.

This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.

By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.

A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.

Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.

Please view the compatibility matrix prior to purchase.