MySQL4th原版PDFbyDuBois

MySQL

需积分: 16 200 浏览量更新于2023-05-26 评论收藏 6.69MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

资源详情

资源评论

资源推荐

MySQL®

Fourth Edition

transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise,

without written permission from the publisher. No patent liability is assumed with respect to

the use of the information contained herein. Although every precaution has been taken in

the preparation of this book, the publisher and author assume no responsibility for errors or

omissions. Nor is any liability assumed for damages resulting from the use of the informa-

tion contained herein.

ISBN-13: 978-0-672-32938-8

ISBN-10: 0-672-32938-7

Library of Congress Cataloging-in-Publication Data

DuBois, Paul, 1956-

MySQL / Paul DuBois. — 4th ed.

p. cm.

Includes index.

ISBN 978-0-672-32938-8 (pbk.)

1. SQL (Computer program language) 2. MySQL (Electronic resource) 3. Database man-

agement. I. Title.

QA76.73.S67D588 2009

005.13’3—dc22

2008030855

Printed in the United States of America

First Printing August 2008

Trademarks

All terms mentioned in this book that are known to be trademarks or service marks have

been appropriately capitalized. Pearson cannot attest to the accuracy of this information.

Use of a term in this book should not be regarded as affecting the validity of any trademark

or service mark.

Warning and Disclaimer

Every effort has been made to make this book as complete and as accurate as possible,

but no warranty or fitness is implied. The information provided is on an “as is” basis. The

author and the publisher shall have neither liability nor responsibility to any person or entity

with respect to any loss or damages arising from the information contained in this book.

Bulk Sales

Pearson offers excellent discounts on this book when ordered in quantity for bulk purchases

or special sales. For more information, please contact

U.S. Corporate and Government Sales

1-800-382-3419

corpsales@pearsontechgroup.com

For sales outside of the U.S., please contact

International Sales

international@pearson.com

Acquisitions Editor

Mark Taber

Development Editor

Michael Thurston

Managing Editor

Kristy Hart

Project Editor

Jovana

San Nicolas-Shirley

Indexer

Cheryl Lenser

Proofreaders

Leslie Joseph

Water Crest

Publishing

Technical Editors

Stephen Frein

Tim Boronczyk

Publishing

Coordinator

Vanessa Evans

Cover Designer

Gary Adair

Compositor

Jake McFarland

Contents at a Glance

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

I: General MySQL Use

1 Getting Started with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

2 Using SQL to Manage Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

3 Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201

4 Stored Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289

5 Query Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303

II: Using MySQL Programming Interfaces

6 Introduction to MySQL Programming . . . . . . . . . . . . . . . . . . . . . . . . 341

7 Writing MySQL Programs Using C . . . . . . . . . . . . . . . . . . . . . . . . . . 359

8 Writing MySQL Programs Using Perl DBI. . . . . . . . . . . . . . . . . . . . . . 435

9 Writing MySQL Programs Using PHP . . . . . . . . . . . . . . . . . . . . . . . . 527

III: MySQL Administration

10 Introduction to MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . 579

11 The MySQL Data Directory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585

12 General MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . 609

13 Access Control and Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699

14 Database Maintenance, Backups, and Replication . . . . . . . . . . . . . . 737

IV: Appendixes

A Obtaining and Installing Software . . . . . . . . . . . . . . . . . . . . . . . . . . 777

B Data Type Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 797

C Operator and Function Reference . . . . . . . . . . . . . . . . . . . . . . . . . . 813

D System, Status, and User Variable Reference. . . . . . . . . . . . . . . . . . 889

E SQL Syntax Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 937

F MySQL Program Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1037

Note: Appendixes G, H, and I are located online and are accessible either by registering this

book at informit.com/register or by visiting www.kitebird.com/mysql-book.

G C API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1121

H Perl DBI API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1177

I PHP API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1207

Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1225

Table of Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Why Choose MySQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Already Running Another RDBMS?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Tools Provided with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
What You Can Expect from This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Road Map to This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Part I: General MySQL Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Part II: Using MySQL Programming Interfaces . . . . . . . . . . . . . . . . . . . . . . . 6
Part III: MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Part IV: Appendixes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
How to Read This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Versions of Software Covered in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Conventions Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Additional Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
I: General MySQL Use
1 Getting Started with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.1 How MySQL Can Help You . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.2 A Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
1.2.1 The U.S. Historical League . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
1.2.2 The Grade-Keeping Project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
1.2.3 How the Sample Database Applies to You . . . . . . . . . . . . . . . . . . . . 20
1.3 Basic Database Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1.3.1 Structural Terminology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1.3.2 Query Language Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
1.3.3 MySQL Architectural Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
1.4 A MySQL Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
1.4.1 Obtaining the Sample Database Distribution . . . . . . . . . . . . . . . . . . 26
1.4.2 Preliminary Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
1.4.3 Establishing and Terminating Connections to the MySQL Server . . . . 28
1.4.4 Executing SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
1.4.5 Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
1.4.6 Creating Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34