Amazon no longer offers textbook rentals. We do!

MySQL

by: DuBois, Paul

MySQL

by: DuBois, Paul

ISBN 13:
9780672326738
ISBN 10:
0672326736
Edition: 3rd
Format: Paperback
Copyright: 01/01/2005
Publisher: Sams
Newer Edition

Rent

Sorry, this item is currently unavailable.

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

Extend or Purchase Your Rental at Any Time

Need to keep your rental past your due date? At any time before your due date you can extend or purchase your rental through your account.

Summary

& bull; Updated to cover MySQL 5, a major revision to the world's most popular open - source database. & lt;br/ & gt; & bull; Paul DuBois is a recognized leader in the open source community, and is now on staff at MySQL AB. & lt;br/ & gt; & bull; MySQL remains the definitive reference and guide to using, administering, and programming MySQL databases.

Introduction

(2)

Why Choose MySQL?

(1)

Already Running Another RDBMS?

(1)

Tools Provided with MySQL

(1)

What You Can Expect from This Book

(1)

Road Map to This Book

(2)

Part I: General MySQL Use

(1)

Part II: Using MySQL Programming Interfaces

(1)

Part III: MySQL Administration

(1)

Part IV: Appendixes

(1)

How to Read This Book

(1)

Versions of Software Covered in This Book

(2)

Conventions Used in This Book

(1)

Additional Resources

(2)

Getting Started with MySQL and SQL

(90)

How MySQL Can Help You

(4)

A Sample Database

(4)

The U.S. Historical League

(2)

The Grade-Keeping Project

(1)

How the Sample Database Applies to You

(1)

Basic Database Terminology

(5)

Structural Terminology

(2)

Query Language Terminology

(1)

MySQL Architectural Terminology

(2)

A MySQL Tutorial

(69)

Obtaining the Sample Database Distribution

(1)

Preliminary Requirements

(1)

Establishing and Terminating Connections to the MySQL Server

(2)

Executing SQL Statements

(3)

Creating a Database

(2)

Creating Tables

(18)

Adding New Records

(4)

Resetting the sampdb Database to a Known State

(1)

Retrieving Information

(35)

Deleting or Updating Existing Records

(2)

Tips for Interacting with mysql

(6)

Simplifying the Connection Process

(3)

Issuing Statements with Less Typing

(3)

Where to Now?

101

(2)

MySQL SQL Syntax and Use

103

(106)

MySQL Naming Rules

105

(4)

Referring to Elements of Databases

105

(3)

Case Sensitivity in SQL Statements

108

(1)

The Server SQL Mode

109

(1)

Character Set Support

110

(6)

Character Set Support Before MySQL 4.1

111

(1)

Character Set Support in MySQL 4.1 and Up

112

(4)

Selecting, Creating, Dropping, and Altering Databases

116

(2)

Selecting Databases

116

(1)

Creating Databases

117

(1)

Dropping Databases

118

(1)

Altering Databases

118

(1)

Creating, Dropping, Indexing, and Altering Tables

118

(28)

Storage Engine Characteristics

118

(8)

Creating Tables

126

(10)

Dropping Tables

136

(1)

Indexing Tables

137

(5)

Altering Table Structure

142

(4)

Getting Information About Databases and Tables

146

(5)

Performing Multiple-Table Retrievals with Joins

151

(8)

The Trivial Join

152

(1)

The Cross Join

153

(2)

Left and Right Joins

155

(4)

Performing Multiple-Table Retrievals with Subqueries

159

(7)

Subqueries with Relative Comparison Operators

160

(1)

In and Not In Subqueries

161

(1)

All, Any, and Some Subqueries

162

(1)

Exists and Not Exists Subqueries

163

(1)

Correlated Subqueries

164

(1)

Subqueries in the From Clause

164

(1)

Rewriting Subqueries as Joins

165

(1)

Performing Multiple-Table Retrievals with Union

166

(5)

Multiple-Table Deletes and Updates

171

(1)

Performing Transactions

172

(12)

Using Transactions to Ensure Safe Statement Execution

174

(4)

Using Transaction Savepoints

178

(1)

Transaction Isolation

178

(2)

Non-Transactional Approaches to Transactional Problems

180

(4)

Foreign Keys and Referential Integrity

184

(8)

Living Without Foreign Keys

191

(1)

Using Fulltext Searches

192

(7)

Natural Language Fulltext Searches

194

(2)

Boolean Mode Fulltext Searches

196

(1)

Query Expansion Fulltext Searches

197

(1)

Configuring the Fulltext Search Engine

198

(1)

New Features in MySQL 5.0

199

(10)

Using Views

199

(4)

Using Stored Procedures

203

(4)

Using Triggers

207

(2)

Working with Data in MySQL

209

(88)

Categories of Data Values

211

(10)

Numeric Values

211

(1)

String Values

212

(8)

Date and Time (Temporal) Values

220

(1)

Spatial Values

220

(1)

The Null Value

220

(1)

MySQL Data Types

221

(40)

Overview of Data Types

221

(2)

Defining Column Types in Table Definitions

223

(1)

Specifying Column Default Values

224

(1)

Numeric Data Types

225

(8)

String Data Types

233

(17)

Date and Time Data Types

250

(9)

Spatial Data Types

259

(2)

How MySQL Handles Invalid Data Values

261

(2)

Working with Sequences

263

(10)

General Auto_Increment Concepts

263

(2)

Auto_Increment Handling Per Storage Engine

265

(4)

Issues to Consider with Auto_Increment Columns

269

(1)

Tips for Working with Auto_Increment Columns

269

(2)

Generating Sequences Without Auto_Increment

271

(2)

Choosing Data Types

273

(8)

What Kind of Values Will the Column Hold?

275

(3)

Do Your Values Lie Within Some Particular Range?

278

(1)

What Are the Performance and Efficiency Issues?

279

(1)

Inter-Relatedness of Data Type Choice Issues

280

(1)

Expression Evaluation and Type Conversion

281

(16)

Writing Expressions

282

(7)

Type Conversion

289

(8)

Query Optimization

297

(38)

Using Indexing

298

(7)

Benefits of Indexing

298

(3)

Costs of Indexing

301

(1)

Choosing Indexes

302

(3)

The MySQL Query Optimizer

305

(11)

How the Optimizer Works

306

(4)

Using Explain to Check Optimizer Operation

310

(5)

Overriding Optimization

315

(1)

Data Type Choices and Query Efficiency

316

(4)

Loading Data Efficiently

320

(4)

Scheduling and Locking Issues

324

(4)

Changing Statement Scheduling Priorities

325

(1)

Using Delayed Inserts

325

(1)

Using Concurrent Inserts

326

(1)

Locking Levels and Concurrency

327

(1)

Optimization for Administrators

328

(7)

Using MyIsam Key Caches

330

(1)

Using the Query Cache

331

(2)

Hardware Issues

333

(2)

Introduction to MySQL Programming

335

(18)

Why Write Your Own MySQL Programs?

335

(5)

APIs Available for MySQL

340

(5)

The C API

341

(1)

The Perl DBI API

341

(3)

The PHP API

344

(1)

Choosing an API

345

(8)

Execution Environment

346

(1)

Performance

346

(3)

Development Time

349

(2)

Portability

351

(2)

Writing MySQL Programs Using C

353

(76)

General Instructions for Building Client Programs

354

(3)

Basic System Requirements

355

(1)

Compiling and Linking Client Programs

355

(2)

Connecting to the Server

357

(4)

Handling Errors and Processing Command Options

361

(22)

Checking for Errors

361

(4)

Getting Connection Parameters at Runtime

365

(13)

Incorporating Option-Processing into a MySQL Client Program

378

(5)

Processing SQL Statements

383

(20)

Handling Statements That Return No Result Set

385

(1)

Handling Statements That Return a Result Set

386

(3)

A General Purpose Statement Handler

389

(2)

Alternative Approaches to Statement Processing

391

(1)

mysql_store_result ( ) and mysql_use_result ( ) Compared

392

(2)

Using Result Set Metadata

394

(5)

Encoding Special Characters and Binary Data

399

(4)

An Interactive Statement-Execution Program

403

(2)

Writing Clients That Include SSL Support

405

(5)

Using the Embedded Server Library

410

(3)

Writing an Embedded Server Application

410

(2)

Producing the Application Executable Binary

412

(1)

Using Multiple-Statement Execution

413

(3)

Using Server-Side Prepared Statements

416

(13)

Writing MySQL Programs Using Perl DBI

429

(92)

Perl Script Characteristics

430

(1)

Perl DBI Overview

431

(41)

DBI Data Types

431

(1)

A Simple DBI Script

432

(4)

Handling Errors

436

(4)

Handling Statements That Return No Result Set

440

(1)

Handling Statements That Return a Result Set

441

(10)

Quoting Issues

451

(3)

Placeholders and Prepared Statements

454

(3)

Binding Query Results to Script Variables

457

(1)

Specifying Connection Parameters

458

(4)

Debugging

462

(3)

Using Result Set Metadata

465

(5)

Performing Transactions

470

(2)

Putting DBI to Work

472

(22)

Generating the Historical League Directory

472

(7)

Sending Membership Renewal Notices

479

(5)

Historical League Member Entry Editing

484

(6)

Finding Historical League Members with Common Interests

490

(1)

Putting the Historical League Directory Online

491

(3)

Using DBI in Web Applications

494

(27)

Setting Up Apache for CGI Scripts

496

(1)

A Brief Introduction to CGLpm

497

(8)

Connecting to the MySQL Server from Web Scripts

505

(2)

A Web-Based Database Browser

507

(5)

A Grade-Keeping Project Score Browser

512

(4)

Historical League Common-Interest Searching

516

(5)

Writing MySQL Programs Using PHP

521

(54)

PHP Overview

523

(23)

Using Functions and Include Files

529

(5)

A Simple Data-Retrieval Page

534

(3)

Processing Statement Results

537

(5)

Testing for Null Values in Query Results

542

(1)

Handling Errors

543

(1)

Quoting Issues

544

(1)

Placeholders and Prepared Statements

545

(1)

Putting PHP to Work

546

(29)

Entering Student Scores

546

(13)

Creating an Interactive Online Quiz

559

(6)

Historical League Online Member Entry Editing

565

(10)

Introduction to MySQL Administration

575

(6)

Overview of Administrative Duties

575

(2)

General Administration

577

(1)

Security

578

(1)

Database Repair and Maintenance

579

(2)

The MySQL Data Directory

581

(26)

Location of the Data Directory

582

(2)

Structure of the Data Directory

584

(15)

How the MySQL Server Provides Access to Data

585

(2)

How MySQL Represents Databases in the Filesystem

587

(1)

How MySQL Represents Tables in the Filesystem

588

(2)

How SQL Statements Map onto Table File Operations

590

(1)

Operating System Constraints on Database and Table Naming

591

(2)

Factors That Affect Maximum Table Size

593

(2)

Implications of Data Directory Structure for System Performance

595

(2)

MySQL Status and Log Files

597

(2)

Relocating Data Directory Contents

599

(8)

Relocation Methods

600

(1)

Assessing the Effect of Relocation

601

(1)

Relocating the Entire Data Directory

602

(1)

Relocating Individual Databases

602

(2)

Relocating Individual Tables

604

(1)

Relocating the InnoDB Shared Tablespace

605

(1)

Relocating Status and Log Files

605

(2)

General MySQL Administration

607

(94)

Securing a New MySQL Installation

608

(6)

How the Grant Tables Are Set Up Initially

609

(2)

Establishing Passwords for the Initial MySQL Accounts

611

(2)

Setting Up Passwords for a Second Server

613

(1)

Arranging for MySQL Server Startup and Shutdown

614

(12)

Running the MySQL Server on Unix

614

(5)

Running the MySQL Server on Windows

619

(2)

Specifying Server Startup Options

621

(2)

Stopping the Server

623

(1)

Regaining Control of the Server When You Can't Connect to It

624

(2)

Managing MySQL User Accounts

626

(15)

Creating New Accounts and Granting Privileges

627

(11)

Displaying Account Privileges

638

(1)

Revoking Privileges and Removing Users

638

(2)

Changing Passwords or Resetting Lost Passwords

640

(1)

Dealing with Changes to Grant Table Structure

640

(1)

Maintaining Log Files

641

(14)

The Error Log

644

(1)

The General Query Log

645

(1)

The Binary Log and the Binary Log Index File

645

(2)

The Update Log

647

(1)

The Slow-Query Log

647

(1)

The Relay Log and the Relay Log Index File

648

(1)

Log File Expiration

648

(7)

Tuning the Server

655

(7)

Setting and Checking System Variable Values

656

(3)

General Purpose System Variables

659

(3)

Storage Engine Configuration

662

(11)

Selecting Storage Engines

662

(2)

Configuring the MyIsam Storage Engine

664

(3)

Configuring the InnoDB Storage Engine

667

(6)

Controlling How the Server Listens for Connections

673

(1)

Enabling or Disabling Local Capability for Load Data

674

(1)

Configuring Backward Compatibility for Password Handling

675

(1)

Internationalization and Localization Issues

676

(7)

Configuring Time Zone Support

676

(2)

Selecting the Language for Error Messages

678

(1)

Configuring Character Set Support

679

(1)

Upgrading Older Tables to Enable MySQL 4.1 Character Set Support

680

(3)

Running Multiple Servers

683

(10)

General Multiple Server Issues

683

(3)

Configuring and Compiling Different Servers

686

(2)

Strategies for Specifying Startup Options

688

(1)

Using mysqld_multi for Server Management

689

(2)

Running Multiple Servers on Windows

691

(2)

Setting Up Replication Servers

693

(5)

How Replication Works

693

(2)

Establishing a Master-Slave Replication Relationship

695

(3)

Updating MySQL

698

(3)

MySQL and Security

701

(40)

Internal Security: Preventing Unauthorized Filesystem Access

702

(9)

How to Steal Data

703

(1)

Securing Your MySQL Installation

704

(7)

External Security: Preventing Unauthorized Network Access

711

(24)

Structure and Contents of the MySQL Grant Tables

711

(9)

How the Server Controls Client Access

720

(6)

A Privilege Puzzle

726

(4)

Grant Table Risks to Avoid

730

(3)

Setting Up MySQL Accounts Without Grant Statements

733

(2)

Setting Up Secure Connections

735

(6)

Database Backups, Maintenance, and Repair

741

(36)

Performing Database Maintenance with the Server Running

743

(5)

Preventing Interactions Using Internal Locking

744

(3)

Preventing Interactions Using External Locking

747

(1)

General Preventive Maintenance

748

(2)

Using the Server's Auto-Recovery Capabilities

748

(1)

Scheduling Preventive Maintenance

749

(1)

Making Database Backups

750

(10)

Making Backups with mysqldump

751

(3)

Making Binary Database Backups

754

(3)

Backing Up InnoDB or BDB Tables

757

(1)

Making Backups Using a Replication Slave

758

(1)

Using a Backup to Rename a Database

759

(1)

Copying Databases to Another Server

760

(1)

Copying Databases Using a Backup File

760

(1)

Copying Databases Using a Network Transfer

760

(1)

Checking and Repairing Database Tables

761

(7)

Using Check Table to Check Tables

763

(1)

Using Repair Table to Repair Tables

763

(1)

Using mysqlcheck to Check and Repair Tables

764

(1)

Using myisamchk to Check and Repair Tables

765

(3)

Using Backups for Data Recovery

768

(9)

Recovering Entire Databases

769

(1)

Recovering Individual Tables

770

(1)

Re-Executing Statements in Binary Log Files

770

(2)

Recovering InnoDB Tables That Have Foreign Key Relationships

772

(2)

Coping with InnoDB or BDB Auto-Recovery Problems

774

(3)

A Obtaining and Installing Software

777

(22)

Obtaining the sampdb Sample Database Distribution

777

(1)

Obtaining MySQL and Related Software

778

(2)

Choosing a Version of MySQL

780

(1)

Installing MySQL on Unix

780

(13)

Overview of Unix MySQL Installation

781

(1)

Creating a Login Account for the MySQL User

782

(1)

Obtaining and Installing a MySQL Distribution on Unix

783

(4)

Post-Installation Steps

787

(3)

Installing Perl DBI Support on Unix

790

(1)

Installing Apache and PHP on Unix

791

(2)

Installing MySQL on Windows

793

(6)

Installing Perl DBI Support on Windows

797

(1)

Installing Apache and PHP on Windows

797

(1)

Installing Connector/ODBC on Windows

797

(2)

B Data Type Reference

799

(16)

Numeric Types

801

(4)

Integer Types

801

(2)

Floating-Point Types

803

(1)

Fixed-Point Type

804

(1)

Bit Type

805

(1)

String Types

805

(6)

Binary String Types

807

(1)

Non-Binary String Types

808

(2)

Enum and Set Types

810

(1)

Date and Time Types

811

(2)

Spatial Types

813

(2)

C Operator and Function Reference

815

(74)

Operators

816

(16)

Operator Precedence

816

(1)

Grouping Operators

817

(1)

Arithmetic Operators

818

(1)

Comparison Operators

819

(5)

Bit Operators

824

(1)

Logical Operators

825

(2)

Cast Operators

827

(1)

Pattern-Matching Operators

827

(5)

Functions

832

(57)

Comparison Functions

832

(3)

Cast Functions

835

(1)

Numeric Functions

835

(6)

String Functions

841

(12)

Date and Time Functions

853

(16)

Summary Functions

869

(3)

Security and Compression Functions

872

(3)

Advisory Locking Functions

875

(2)

Spatial Functions

877

(6)

Miscellaneous Functions

883

(6)

D System, Status, and User Variable Reference

889

(44)

System Variables

889

(27)

Session-Only System Variables

916

(4)

Status Variables

920

(13)

Statement Counter Status Variables

924

(1)

InnoDB Status Variables

924

(4)

Query Cache Status Variables

928

(1)

SSL Status Variables

928

(5)

User-Defined Variables

933

(1)

E SQL Syntax Reference

933

(88)

SQL Statement Syntax

934

(76)

Stored Routine Syntax

1010

(8)

Control Structure Statements

1014

(2)

Declaration Statements

1016

(2)

Cursor Statements

1018

(1)

Comment Syntax

1018

(3)

F MySQL Program Reference

1021

(80)

Specifying Program Options

1022

(12)

Program Option Conventions

1023

(2)

Standard MySQL Program Options

1025

(3)

Option Files

1028

(4)

Environment Variables

1032

(2)

libmysqld

1034

(1)

myisamchk

1034

(7)

Usage

1036

(1)

Standard Options Supported by myisamchk

1036

(1)

Options Specific to myisamchk

1037

(3)

Variables for myisamchk

1040

(1)

myisampack

1041

(2)

Usage

1042

(1)

Standard Options Supported by myisampack

1042

(1)

Options Specific to myisampack

1042

(1)

mysql

1043

(10)

Usage

1044

(1)

Standard Options Supported by mysql

1045

(1)

Options Specific to mysql

1045

(4)

Variables for mysql

1049

(1)

mysql Commands

1049

(3)

mysql Prompt Definition Sequences

1052

(1)

mysql server

1053

(1)

Usage

1053

(1)

Standard Options Supported by mysql server

1053

(1)

mysql_config

1053

(1)

Usage

1054

(1)

Options Specific to mysql_config

1054

(1)

mysql_install_db

1054

(1)

Usage

1055

(1)

Options Specific to mysql_install_db

1055

(1)

mysqladmin

1055

(4)

Usage

1055

(1)

Standard Options Supported by mysqladmin

1056

(1)

Options Specific to mysqladmin

1056

(1)

Variables for mysqladmin

1056

(1)

mysqladmin Commands

1057

(2)

mysqlbinlog

1059

(2)

Usage

1059

(1)

Standard Options Supported by mysqlbinlog

1059

(1)

Options Specific to mysqlbinlog

1059

(2)

Variables for mysqlbinlog

1061

(1)

mysqlcheck

1061

(3)

Usage

1061

(1)

Standard Options Supported by mysqlcheck

1062

(1)

Options Specific to mysqlcheck

1062

(2)

mysqld

1064

(19)

Usage

1064

(1)

Standard Options Supported by mysqld

1065

(1)

Options Specific to mysqld

1065

(17)

Variables for mysqld

1082

(1)

mysqld_multi

1083

(1)

Usage

1083

(1)

Standard Options Supported by mysqld_multi

1083

(1)

Options Specific to mysqld_multi

1083

(1)

mysqld_safe

1084

(2)

Usage

1084

(1)

Options Specific to mysqld_safe

1084

(2)

Upgrading Tips

1086

(1)

mysqldump

1086

(7)

Usage

1086

(1)

Standard Options Supported by mysqldump

1087

(1)

Options Specific to mysqldump

1087

(5)

Data Format Options for mysqldump

1092

(1)

Variables for mysqldump

1093

(1)

mysqlhotcopy

1093

(3)

Usage

1093

(1)

Standard Options Supported by mysqlhotcopy

1094

(1)

Options Specific to mysqlhotcopy

1094

(2)

mysqlimport

1096

(3)

Usage

1097

(1)

Standard Options Supported by mysqlimport

1097

(1)

Options Specific to mysqlimport

1097

(1)

Data Format Options for mysqlimport

1098

(1)

mysqlshow

1099

(1)

Usage

1099

(1)

Standard Options Supported by mysqlshow

1099

(1)

Options Specific to mysqlshow

1099

(1)

perror

1100

(1)

Usage

1100

(1)

Standard Options Supported by perror

1100

(1)

G C API Reference

1101

(52)

Compiling and Linking

1102

(1)

C API Data Types

1103

(11)

Scalar Data Types

1103

(1)

Non-Scalar Data Types

1104

(9)

Accessor Macros

1113

(1)

C API Functions

1114

(39)

Connection Management Routines

1114

(9)

Error-Reporting Routines

1123

(1)

Statement Construction and Execution Routines

1124

(3)

Result Set Processing Routines

1127

(9)

Information Routines

1136

(2)

Transaction Control Routines

1138

(1)

Multiple Result Set Routines

1138

(1)

Prepared Statement Routines

1139

(8)

Administrative Routines

1147

(2)

Threaded Client Routines

1149

(1)

Embedded Server Communication Routines

1150

(1)

Debugging Routines

1150

(3)

H Perl DBI API Reference

1153

(30)

Writing Scripts

1154

(1)

DBI Methods

1154

(19)

DBI Class Methods

1156

(5)

Database Handle Methods

1161

(6)

Statement Handle Methods

1167

(3)

General Handle Methods

1170

(1)

MySQL-Specific Administrative Methods

1171

(2)

DBI Utility Functions

1173

(1)

DBI Attributes

1174

(8)

Database Handle Attributes

1174

(1)

General Handle Attributes

1174

(1)

MySQL-Specific Database Handle Attributes

1175

(2)

Statement Handle Attributes

1177

(2)

MySQL-Specific Statement Handle Attributes

1179

(3)

Dynamic Attributes

1182

(1)

DBI Environment Variables

1182

(1)

I PHP and Pear DB API Reference

1183

(20)

Writing PHP Scripts

1183

(1)

Pear DB Module Classes

1184

(1)

Pear DB Module Methods

1185

(18)

DB Class Methods

1185

(1)

DB_common Object Methods

1186

(14)

DB_result Object Methods

1200

(2)

DB_Error Object Methods

1202

(1)

Index

1203

Amazon no longer offers textbook rentals. We do!

MySQL

MySQL

9780672326738

0672326736

Summary

Table of Contents

Supplemental Materials

Excerpts

Write a Review

Amazon no longer offers textbook rentals. We do!

MySQL

MySQL

9780672326738

0672326736

Summary

Table of Contents

Supplemental Materials

Excerpts

Write a Review

Related Products