MySQL 5.0 Certification Study Guide

, by DuBois, Paul; Hinz, Stefan; Pedersen, Carsten

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780672328121 | 0672328127
Cover: Paperback
Copyright: 8/24/2005

Sorry, this item is currently not available.

The MySQL certification program is a carefully developed, high-quality certification process that can help you prove your skills in developing, maintaining, and administering MySQL databases and applications.

Introduction

(1)

About This Book

(1)

Sample Exercises

(1)

Other Required Reading

(2)

Manuals

(1)

Sample Data

(1)

Study Guide Errata

(1)

Certification Information at www.mysql.com

(1)

The MySQL Certification Candidate Guide

(1)

The Certification Mailing List

(1)

Conventions Used in This Book

(2)

Running MySQL on Microsoft Windows

(1)

About the Exams

(6)

Registering for an Exam

(1)

Going to the Exam

(2)

Taking the Exam

(1)

Reading Questions

(1)

Answering Questions

(2)

After the Exam

(1)

Retaking Exams

(1)

Warning

(1)

Interpreting Describe Output

(2)

Sample Tables

(7)

MySQL Developer Exams

MySQL Developer I Exam

Client/Server Concepts

(14)

General MySQL Architecture

(1)

Invoking Client Programs

(9)

General Command Option Syntax

(1)

Connection Parameter Options

(4)

Using Option Files

(2)

Selecting a Default Database

(1)

Establishing a Connection with a GUI Client

(1)

Server SQL Modes

(4)

The mysql Client Program

(10)

Using mysql Interactively

(3)

Statement Terminators

(1)

The mysql Prompts

(1)

Using Editing Keys in mysql

(1)

Using Script Files with mysql

(1)

mysql Output Formats

(1)

Client Commands and SQL Statements

(1)

Using Server-Side Help

(2)

Using the - -safe-updates Option

(1)

MySQL Query Browser

(10)

MySQL Query Browser Capabilities

(1)

Using MySQL Query Browser

(1)

Using the Query Window

(3)

Entering Queries

(1)

The Result Area

(1)

The Script Editor

(1)

Stored Routine Management

(1)

The Object and Information Browsers

(1)

The MySQL Table Editor

(1)

Connection Management

(2)

Using the Connection Dialog

(1)

Editing Connection Profiles

(1)

The Options Dialog

(2)

MySQL Connectors

(4)

MySQL Client Interfaces

(1)

MySQL Connector/ODBC

(1)

MySQL Connector/J

(1)

MySQL Connector/NET

(2)

Data Types

(38)

Data Type Overview

(2)

Numeric Data Types

(3)

Integer Data Types

(1)

Floating-Point Data Types

(1)

Fixed-Point Data Types

(1)

The BIT Data Type

(1)

String Data Types

(8)

Character Set Support

(2)

Non-Binary String Data Types: Char, Varchar, Text

(2)

Binary String Data Types: Binary, Varbinary, Blob

(1)

The Enum and Set Data Types

(3)

Temporal Data Types

(10)

The Date, Time, Datetime, and Year Data Types

(1)

The Timestamp Data Type

(5)

Per-Connection Time Zone Support

(3)

Column Attributes

(2)

Numeric Column Attributes

(1)

String Column Attributes

(1)

General Column Attributes

(1)

Using the Auto_Increment Column Attribute

(6)

Handling Missing or Invalid Data Values

(7)

Handling Missing Values

(1)

Handling Invalid Values in Non-Strict Mode

(2)

Handling Invalid Values in Strict Mode

(1)

Enabling Additional Input Data Restrictions

(1)

Overriding Input Data Restrictions

(2)

Identifiers

(6)

Identifier Syntax

(1)

Case Sensitivity of Identifiers

(1)

Using Qualified Names

(1)

Using Reserved Words as Identifiers

(4)

Databases

103

(6)

Database Properties

103

(1)

Creating Databases

104

(1)

Altering Databases

105

(1)

Dropping Databases

105

(1)

Obtaining Database Metadata

106

(3)

Tables and Indexes

109

(26)

Table Properties

109

(2)

Creating Tables

111

(6)

Creating Tables Using an Explicit Definition

112

(1)

Specifying the Storage Engine for a Table

113

(2)

Creating Tables Based on Existing Tables

115

(1)

Using Temporary Tables

116

(1)

Altering Tables

117

(3)

Adding and Dropping Columns

118

(1)

Modifying Existing Columns

119

(1)

Renaming a Table

120

(1)

Specifying Multiple Table Alterations

120

(1)

Dropping Tables

120

(1)

Emptying Tables

121

(1)

Indexes

122

(7)

Types of Indexes

122

(1)

Creating Indexes

122

(6)

Choosing an Indexing Algorithm

128

(1)

Dropping Indexes

129

(1)

Obtaining Table and Index Metadata

130

(5)

Querying for Data

135

(32)

Using Select to Retrieve Data

135

(1)

Specifying Which Columns to Retrieve

136

(3)

Renaming Retrieved Columns

137

(1)

Identifying the Database Containing a Table

138

(1)

Specifying Which Rows to Retrieve

139

(11)

Using Order By to Sort Query Results

140

(3)

The Natural Sort Order of Data Types

143

(3)

Limiting a Selection Using Limit

146

(2)

Using Distinct to Eliminate Duplicates

148

(2)

Aggregating Results

150

(6)

The Min() and Max() Aggregate Functions

151

(1)

The Sum() and AVG() Aggregate Functions

152

(1)

The Count() Aggregate Function

152

(1)

The Group_Concat() Function

153

(2)

Aggregation for Null Values or Empty Sets

155

(1)

Grouping Results

156

(9)

Group By and Sorting

160

(1)

Selecting Groups with Having

160

(1)

Using Group By and With Rollup

161

(4)

Using Union

165

(2)

SQL Expressions

167

(26)

Components of SQL Expressions

167

(2)

Numeric Expressions

169

(1)

String Expressions

170

(9)

Case Sensitivity in String Comparisons

171

(5)

Using Like for Pattern Matching

176

(3)

Temporal Expressions

179

(1)

Null Values

180

(1)

Functions in SQL Expressions

181

(11)

Comparison Functions

182

(2)

Control Flow Functions

184

(1)

Aggregate Functions

185

(1)

Mathematical Functions

186

(2)

String Functions

188

(1)

Temporal Functions

189

(2)

Null-Related Functions

191

(1)

Comments in SQL Statements

192

(1)

Updating Data

193

(16)

Update Operations

193

(1)

The Insert Statement

194

(5)

Adding Multiple Records with a Single Insert Statement

196

(1)

Handling Duplicate Key Values

197

(1)

Using Insert . . . On Duplicate Key Update

198

(1)

The Replace Statement

199

(2)

The Update Statement

201

(3)

Using Update with Order By and Limit

203

(1)

Preventing Dangerous Update Statements

204

(1)

Multiple-Table Update Statements

204

(1)

The Delete and Truncate Table Statements

204

(2)

Using Delete with Order By and Limit

205

(1)

Multiple-Table Delete Statements

205

(1)

Privileges Required for Update Statements

206

(3)

MySQL Developer II Exam

Joins

209

(18)

Overview

209

(1)

Writing Inner Joins

210

(7)

Writing Inner Joins with the Comma Operator

210

(7)

Writing Inner Joins with Inner Join

217

(1)

Writing Outer Joins

217

(5)

Writing Left Join Queries

218

(3)

Writing Right Join Queries

221

(1)

Resolving Name Clashes Using Qualifiers and Aliases

222

(3)

Qualifying Column Names

222

(2)

Qualifying and Aliasing Table Names

224

(1)

Multiple-Table Update and Delete Statements

225

(2)

Subqueries

227

(16)

Types of Subqueries

227

(1)

Subqueries as Scalar Expressions

228

(1)

Correlated Subqueries

229

(1)

Comparing Subquery Results to Outer Query Columns

230

(6)

Using All, Any, and Some

230

(4)

Using In

234

(1)

Using Exists

235

(1)

Comparison Using Row Subqueries

236

(1)

Using Subqueries in the From Clause

237

(1)

Converting Subqueries to Joins

238

(3)

Converting Subqueries to Inner Joins

238

(2)

Converting Subqueries to Outer Joins

240

(1)

Using Subqueries in Updates

241

(2)

Views

243

(14)

Reasons to Use Views

243

(1)

Creating Views

244

(9)

Restrictions on Views

247

(1)

View Algorithms

248

(2)

Updatable Views

250

(3)

Altering Views

253

(1)

Dropping Views

253

(1)

Checking Views

254

(1)

Obtaining View Metadata

254

(2)

Privileges Required for Views

256

(1)

Importing and Exporting Data

257

(16)

Import and Export Operations

257

(1)

Importing and Exporting Using SQL

258

(9)

Importing Data with Load Data Infile

258

(6)

Exporting Data with Select Into Outfile

264

(1)

Data File Format Specifiers

265

(2)

Importing and Exporting Null Values

267

(1)

Importing and Exporting Data from the Command Line

267

(6)

Importing Data with mysqlimport

267

(3)

Exporting Data with mysqldump

270

(3)

User Variables

273

(2)

User Variable Syntax

273

(1)

User Variable Properties

274

(1)

Prepared Statements

275

(6)

Benefits of Prepared Statements

275

(1)

Using Prepared Statements from the mysql Client

275

(1)

Preparing a Statement

276

(1)

Executing a Prepared Statement

277

(2)

Deallocating Prepared Statements

279

(2)

Stored Procedures and Functions

281

(26)

Benefits of Stored Routines

282

(1)

Differences Between Stored Procedures and Functions

283

(1)

The Namespace for Stored Routines

284

(1)

Defining Stored Routines

284

(1)

Creating Stored Routines

285

(17)

Compound Statements

287

(2)

Declaring Parameters

289

(2)

The Declare Statement

291

(1)

Variables in Stored Routines

291

(1)

Conditions and Handlers

292

(3)

Cursors

295

(2)

Retrieving Multiple Result Sets

297

(1)

Flow Control

298

(4)

Altering Stored Routines

302

(1)

Dropping Stored Routines

303

(1)

Invoking Stored Routines

303

(1)

Obtaining Stored Routine Metadata

304

(1)

Stored Routine Privileges and Execution Security

305

(2)

Triggers

307

(6)

Reasons to Use Triggers

307

(1)

Trigger Concepts

308

(1)

Creating a Trigger

309

(2)

Restrictions on Triggers

311

(1)

Referring to Old and New Column Values

311

(1)

Destroying a Trigger

311

(1)

Privileges Required for Triggers

312

(1)

Obtaining Database Metadata

313

(12)

Overview of Metadata Access Methods

313

(1)

Using Information_Schema to Obtain Metadata

314

(3)

Using Show and Describe to Obtain Metadata

317

(5)

Using mysql show to Obtain Metadata

322

(3)

Debugging MySQL Applications

325

(6)

Interpreting Error Messages

325

(1)

The Show Warnings Statement

326

(3)

The Show Errors Statement

329

(1)

The perror Utility

329

(2)

Basic Optimizations

331

(20)

Overview of Optimization Principles

331

(1)

Using Indexes for Optimization

332

(5)

Types of Indexes

332

(1)

Principles for Index Creation

333

(1)

Indexing Column Prefixes

334

(1)

Leftmost Index Prefixes

335

(2)

General Query Enhancement

337

(7)

Query Rewriting Techniques

337

(1)

Using Explain to Obtain Optimizer Information

338

(1)

Optimizing Queries by Limiting Output

339

(1)

Using Summary Tables

340

(3)

Optimizing Updates

343

(1)

Choosing Appropriate Storage Engines

344

(1)

Normalization

345

(6)

MySQL DBA Exams

MySQL DBA I Exam

MySQL Architecture

351

(8)

Client/Server Overview

351

(1)

Communication Protocols

352

(2)

The SQL Parser and Storage Engine Tiers

354

(1)

How MySQL Uses Disk Space

355

(1)

How MySQL Uses Memory

355

(4)

Starting, Stopping, and Configuring MySQL

359

(20)

Types of MySQL Distributions

359

(2)

MySQL Binary Distributions

360

(1)

MySQL Source Distributions

360

(1)

Starting and Stopping MySQL Server on Windows

361

(4)

Server Startup Prerequisities on Windows

361

(2)

Running MySQL Server Manually on Windows

363

(1)

Running MySQL Server as a Windows Service

364

(1)

Starting and Stopping MySQL Server on Unix

365

(3)

Server Startup Prerequisites on Unix

365

(2)

Choosing a Server Startup Method on Unix

367

(1)

Runtime MySQL Configuration

368

(4)

Log and Status Files

372

(3)

The General Query Log

373

(1)

The Binary Log

373

(1)

The Slow Query Log

374

(1)

The Error Log

374

(1)

Status Files

375

(1)

Loading Time Zone Tables

375

(1)

Security-Related Configuration

376

(1)

Setting the Default SQL Mode

377

(1)

Upgrading MySQL

377

(2)

Client Programs for DBA Work

379

(6)

Overview of Administrative Clients

379

(1)

MySQL Administrator

380

(1)

mysql

381

(1)

mysqladmin

381

(1)

mysqlimport

382

(1)

mysqldump

382

(1)

Client Program Limitations

383

(2)

MySQL Administrator

385

(10)

MySQL Administrator Capabilities

385

(1)

Using MySQL Administrator

386

(2)

Starting MySQL Administrator

386

(1)

Selecting an Operational Mode

387

(1)

Server Monitoring Capabilities

388

(3)

Server Information

389

(1)

Server Connections

389

(1)

Health

389

(1)

Server Logs

390

(1)

Replication Status

390

(1)

Catalogs

390

(1)

Server Configuration

391

(2)

Service Control

391

(1)

Startup Variables

392

(1)

User Administration

392

(1)

Backup and Restore Capabilities

393

(1)

Making Backups

393

(1)

Restoring Backups

394

(1)

MySQL Administrator System Tray Monitor

394

(1)

Character Set Support

395

(4)

Performance Issues

395

(1)

Choosing Data Types for Character Columns

396

(3)

Locking

399

(6)

Locking Concepts

399

(2)

Explicit Table Locking

401

(2)

Advisory Locking

403

(2)

Storage Engines

405

(32)

MySQL Storage Engines

405

(3)

The MyISAM Engine

408

(4)

MyISAM Locking Characteristics

409

(2)

MyISAM Row-Storage Formats

411

(1)

The Merge Engine

412

(2)

Merge Locking Characteristics

414

(1)

The InnoDB Engine

414

(18)

The InnoDB Tablespace and Logs

415

(1)

InnoDB and Acid Compliance

416

(1)

The InnoDB Transaction Model

416

(2)

InnoDB Locking Characteristics

418

(2)

InnoDB Isolation Levels, Multi-Versioning, and Concurrency

420

(2)

Using Foreign Keys

422

(4)

Configuring and Monitoring InnoDB

426

(6)

The Memory Engine

432

(1)

Memory Indexing Options

433

(1)

The Federated Engine

433

(2)

The Cluster Storage Engine

435

(1)

Other Storage Engines

436

(1)

Table Maintenance

437

(10)

Types of Table Maintenance Operations

437

(1)

SQL Statements for Table Maintenance

438

(2)

Check Table

439

(1)

Repair Table

439

(1)

Analyze Table

439

(1)

Optimize Table

439

(1)

Client and Utility Programs for Table Maintenance

440

(7)

The mysqlcheck Client Program

440

(1)

The myisamchk Utility

441

(1)

Options for mysqlcheck and myisamchk

442

(1)

Repairing InnoDB Tables

443

(1)

Enabling MyISAM Auto-Repair

444

(3)

The Information_Schema Database

447

(4)

Information_Schema Access Syntax

447

(2)

Information_Schema Versus Show

449

(1)

Limitations of Information_Schema

450

(1)

Data Backup and Recovery Methods

451

(18)

Introduction

451

(1)

Binary Versus Textual Backups

452

(1)

Making Binary Backups

453

(4)

Making Binary MyISAM Backups

453

(1)

Making Binary InnoDB Backups

454

(1)

Other Binary Backup Tools

455

(1)

Conditions for Binary Portability

456

(1)

Making Text Backups

457

(4)

Making Text Backups via SQL

457

(1)

Making Text Backups with mysqldump

457

(4)

Making Text Backups with MySQL Administrator

461

(1)

Backing Up Log and Status Files

461

(1)

Replication as an Aid to Backup

462

(1)

MySQL Cluster as Disaster Prevention

462

(1)

Data Recovery

462

(7)

Reloading mysqldump Output

463

(1)

Reloading Dumps with MySQL Administrator

464

(1)

Processing Binary Log Contents

464

(5)

Using Stored Routines and Triggers for Administration

469

(4)

Using Stored Routines and Triggers for Security Purposes

469

(2)

Using Stored Routines to Enhance Performance

471

(2)

MySQL DBA II Exam

User Management

473

(18)

User Account Management

473

(13)

Types of Privileges That MySQL Supports

474

(2)

The Grant Tables

476

(1)

Approaches to Account Management

477

(1)

Creating and Dropping User Accounts

478

(1)

Specifying Account Names

478

(2)

Granting Privileges

480

(2)

Revoking Privileges

482

(2)

Changing Account Passwords

484

(1)

When Privilege Changes Take Effect

484

(1)

Specifying Resource Limits

485

(1)

Privileges Needed for Account Management

485

(1)

Client Access Control

486

(5)

Connection Request Checking

488

(1)

Statement Privilege Checking

489

(1)

Resource Limit Checking

490

(1)

Disabling Client Access Control

490

(1)

Securing the MySQL Installation

491

(10)

Security Issues

491

(1)

Operating System Security

492

(1)

Filesystem Security

493

(2)

Log Files and Security

495

(1)

Network Security

496

(4)

Securing the Initial MySQL Accounts

496

(2)

General Privilege Precautions

498

(1)

MySQL Cluster Network Security

499

(1)

Federated Table Security

500

(1)

Upgrade-Related Security Issues

501

(4)

Upgrading the Privilege Tables

501

(1)

Security-Related SQL Mode Values

502

(3)

Optimizing Queries

505

(18)

Identifying Candidates for Query Analysis

505

(2)

Using Explain to Analyze Queries

507

(12)

How Explain Works

507

(2)

Analyzing a Query

509

(5)

Explain Output Columns

514

(5)

Using Show Warnings for Optimization

519

(1)

MyISAM Index Caching

519

(4)

Optimizing Databases

523

(22)

General Table Optimizations

523

(2)

Normalization

525

(6)

MyISAM-Specific Optimizations

531

(10)

MyISAM Row-Storage Formats

532

(5)

Keep Optimizer Information Up to Date

537

(1)

Fulltext Indexes

537

(2)

Specifying MyISAM Maximum Row Count

539

(2)

InnoDB-Specific Optimizations

541

(1)

Merge-Specific Optimizations

542

(1)

Memory-Specific Optimizations

543

(2)

Optimizing the Server

545

(18)

Interpreting mysqld Server Information

545

(7)

Accessing Server System Variables

546

(3)

Accessing Server Status Variables

549

(3)

Measuring Server Load

552

(1)

Tuning Memory Parameters

553

(6)

Global (Server-Wide) Parameters

554

(4)

Per-Client Parameters

558

(1)

Using the Query Cache

559

(4)

Enabling the Query Cache

560

(1)

Measuring Query Cache Utilization

561

(2)

Interpreting Diagnostic Messages

563

(4)

Sources of Diagnostic Information

563

(1)

Using the Error Log for Diagnostic Purposes

563

(1)

Using the Slow Query Log for Diagnostic Purposes

564

(3)

Optimizing the Environment

567

(8)

Choosing Hardware for MySQL Use

567

(1)

Configuring Disks for MySQL Use

568

(4)

Moving Databases Using Symbolic Links

569

(1)

MyISAM Table Symlinking

570

(2)

Network Issues

572

(1)

Optimizing the Operating System for MySQL Use

572

(3)

Scaling MySQL

575

(16)

Using Multiple Servers

575

(2)

Replication

577

(10)

Setting Up Replication

578

(2)

The Binary and Relay Logs

580

(1)

Replication-Related Threads

581

(2)

Replication Troubleshooting

583

(1)

Replication Compatibility and Upgrading

583

(4)

Appendixes

A References

587

(2)

B Other Offers

589

(2)

Index

591

Please wait while the item is added to your bag...

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

MySQL 5.0 Certification Study Guide

Summary

Author Biography

Table of Contents

Supplemental Materials