MySQL 4th 原版PDF by DuBois

Arelational database management system (RDBMS) is an essential tool in many environments,from traditional uses in business, research, and educational contexts, to applicationssuch as powering search engines on the Internet. However, despite the importanceof a good database system for manaMySQLAcquisitions EditorFourth editionMark taberCopyright o 2009 by Pearson Education, IncDevelopment EditorAll rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or Michael Thurstontransmitted by any means, electronic, mechanical, photocopying, recording, or otherwiseManaging Editorwithout written permission from the publisher. No patent liability is assumed with respect to Kristy Hartthe use of the information contained herein although every precaution has been taken inthe preparation of this book, the publisher and author assume no responsibility for errors or Project Editoromissions. Nor is any liability assumed for damages resulting from the use of the information contained hereinSan Nicolas-ShirleySBN-13:9780672329388IndexerSBN-10:0672329387Cheryl LenserProofreadersLibrary of Congress Cataloging-in-Publication DataLeslie JosephWater CrestDuBois, Paul, 1956-PublishingMySQL /Paul DuBois. 4th edTechnical editep. cmStephen FreinIncludes indexTim BoronczyksBN9780672329388(pbkPublishing1. SQL (Computer program language)2. MysQL(Electronic resource)3. Database man-Coordinatoragement. I. TitleVanessa evansQA76.73.s67D5882009Cover Designer005.133dc22Gary Adair2008030855CompositorJake mcfarlandPrinted in the united states of americaFirst Printing August 2008TrademarksAll terms mentioned in this book that are known to be trademarks or service marks havebeen appropriately capitalized. Pearson cannot attest to the accuracy of this informationUse of a term in this book should not be regarded as affecting the validity of any trademarkor service markWarning and DisclaimerEvery effort has been made to make this book as complete and as accurate as possiblebut no warranty or fitness is implied. The information provided is on an"as is"basis. Theauthor and the publisher shall have neither liability nor responsibility to any person or entitywith respect to any loss or damages arising from the information contained in this bookBulk salesPearson offers excellent discounts on this book when ordered in quantity for bulk purchasesial sales. For more information, please contactU.S. Corporate and government sales1800-3823419corpsales@ pearsontechgroup comFor sales outside of the U.s., please contactInternational salesinternationalepearson.comContents at a GlanceIntroduction1I: General MySQL Use1 Getting Started with MySQL132 Using SQL to Manage Data1013 Data Types2014 Stored Programs2895 Query Optimization303: Using MySQL Programming Interfaces6 Introduction to MySQL Programming3417 Writing MySQL Programs Using C3598 Writing MySQL Programs Using Perl DBI4359 Writing MySQL Programs Using PHP,,527I: MySQL Administration10 Introduction to MySQL Administration57911 The MySQL Data Directory58512 General MySQL Administration60913 Access Control and security..,.69914 Database Maintenance, Backups, and replication737IV: AppendixesA Obtaining and Installing Software777B Data Type Reference,,797C Operator and Function Reference,,813D System status, and User Variable Reference889E SQL Syntax Reference937F MySQL Program Reference1037Note: Appendixes G, H, and I are located online and are accessible either by registering thisbookatinformit.com/registerorbyvisitingwww.kitebird.com/mysql-bookG C API Reference1121H Perl DBl API Reference1177PHP API Reference,1207Index1225Table of contentsIntroductionWhy Choose MySQL?Already Running Another RDBMs?Tools Provided with MySQLWhat You Can Expect from This bookRoad map to this bookPart I: General MySQL UsePart II: Using MySQL Programming InterfacesPart lll: MySQL Administration12455666778PartⅣV: AppendixesHow to read this bookVersions of software covered in this bookConventions used in this book10Additional resources11I: General MySQL Use1 Getting Started with MySQL131.1 How MySQL Can Help You131.2 A Sample Database171.2.1 The U.S. Historical League171.2.2 The Grade-Keeping Project20.. 3 How the sample Database Applies to You201.3 Basic Database Terminology211.3.1 Structural Terminology.211.3.2 Query Language Terminology221.3.3 MySQL Architectural Terminology241. A MySQL Tutorial251.4.1 Obtaining the Sample Database Distribution261.4.2 Preliminary Requirements.271.4.3 Establishing and Terminating Connections to the MySQL Server281.4.4 EXecuting SQL Statements301.4.5 Creating a Database,,331.4.6 Creating Tables34Contents1.4.7 Adding New Rows,,,531.4.8 Resetting the sampdb database to a known State571.4.9 Retrieving Information1.4.10 Deleting or Updating Existing Rows911.5 Tips for Interacting with mysa931.5.1 Simplifying the Connection Process931.5.2 Issuing Statements with Less typing951.6 Where to Now?,,1002 Using sQL to Manage Data1012.1 The Server SQL Mode1022.2 My SQL Identifier Syntax and Naming Ru1032.3 Case Sensitivity in SQL Statements1062.4 Character Set Support1072.4.1 Specifying Character Sets,,1082.4.2 Determining Character Set Availability and Current Settings1092.4.3 Unicode Support1112.5 Selecting, Creating, Dropping, and Altering Databases,,,1122.5.1 Selecting Databases...,1122.5.2 Creating Databases,,,1132.5.3 Dropping databases1142.5. 4 Altering Databases2.6 Creating, Dropping, Indexing, and Altering Tables1142.6.1 Storage Engine Characteristics1142.6.2 Creating Tables1222.6.3 Dropping Tables,.,1352.6.4 Indexing Tables1362.6.5 Altering Table Structure1412.7 Obtaining Database Metadata1442.7.1 Obtaining Metadata with SHOW1452.7.2 Obtaining Metadata with INFORMATION SCHEMA1472.7.3 Obtaining Metadata from the Command Line1492.8 Performing Multiple-Table Retrievals with Joins2.8.1 The inner join1522.8.2 Qualifying References to Columns from Joined Tables1532.8.3 Left and Right(Outer) Joins54Contents2.9 Performing Multiple-Table Retrievals with Subqueries1582.9. 1 Subqueries with Relative Comparison Operators1592.9.2 IN and Not IN Subqueries1602.9.3 ALL, ANY, and SOME Subqueries1612.9. 4 EXISTS and NoT EXISTS Subqueries1622.9.5 Correlated Subqueries1632.9.6 Subqueries in the FROM Clause1632.9.7 Rewriting Subqueries as Joins,1642.10 Performing Multiple-Table Retrievals with UNION1652.11 Using Views1692.12 Multiple-Table Deletes and Updates..1732.13 Performing Transactions1742. 13. 1 Using Transactions to Ensure Safe Statement Execution1762.13.2 Using Transaction Savepoints,,,,,.1802.13.3 Transaction Isolation1802.13. 4 Non-Transactional Approaches to transactional Problems1822.14 Foreign Keys and Referential Integrity1852. 14.1 Creating and Using Foreign Keys,1872.14.2 Living Without Foreign Keys,1922.15 Using FULLTEXT Searches..1942.15.1 Natural Language FULLTEXT Searches1962.15.2 Boolean mode fulltext searches,1972.15. 3 Query Expansion FULLTEXT Searches992.15. 4 Configuring the FULLTEXT Search Engine2003 Data Typesn,,,,.2011 Data Value Categories033.1.1 Numeric values2033.1.2 String values2043.1.3 Date and Time(Temporal) Values2133. 1. 4 Spatial values2133.15 Boolean values..2133. 1. 6 The null value2143.2 MySQL Data Types2143.2.1 Overview of Data Types2153.2.2 Specifying Column Types in Table Definitions2173.2.3 Specifying Column Default Values218Contents3.2.4 Numeric Data Types.,.2193.2.5 String Data Types2263.2.6 Date and time data types2423.2.7 Spatial Data Types2503.3 How MySQL Handles Invalid Data Values2523.4 Working with Sequences3.4.1 General AUto INCREMENT Properties,2542543.4.2 Storage Engine-Specific AUTO INCREMENT Properties2563. 4. 3 Issues to Consider with aUto INCrement Columns2593.4.4 Tips for Working with AUTO INCREMENT Columns2603.4.5 Generating Sequences Without AUTO INCREMENT...... 2623.5 Expression Evaluation and Type Conversion.....2643.5.1 Writing Expressions2653.5.2 Type Conversion2723.6 Choosing Data Types,2803.6.1 What Kind of values will the column hold?2823.6.2 Do Your Values Lie Within Some Particular Range?2853.6.3 Inter-Relatedness of Data Type Choice Issues,,,2864 Stored Programs..............■■■2894.1 Compound statements and statement Delimiters2904.2 Stored functions and procedures,,2924.2. 1 Privileges for stored Functions and procedures2944.2.2 Stored Procedure Parameter Types2954.3 Triggers2964. 4 Events...2984.5 Security for Stored Programs and views5 Query Optimization3035.1 Using Indexing3045.1.1 Benefits of Indexing3045.1.2 Costs of Indexing..,3075.1.3 Choosing Indexes3085.2 The MySQL Query Optimizer3115.2.1 How the optimizer Works3125.2.2 Using EXPlain to Check Optimizer operation3165.3 Choosing Data Types for Efficient Queries322Contents5. 4 Loading Data Efficiently,3265.5 Scheduling and Locking Issues3295.5.1 Changing Statement Scheduling Priorities3315.5.2 Using Delayed Inserts3315.5.3 Using Concurrent Inserts3325.5.4 Locking Levels and Concurrency3335. 6 Administrative-Level Optimizations3345.6.1 Using Myl SAM Key Caches,3365.6.2 Using the Query Cache3375.6. 3 Hardware Optimizations339I: Using MysQL Programming Interfaces6 Introduction to MySQL Programming,,,,,,,,。,,.3416.1 Why Write Your Own MySQL Programs?3416.2 APIs Available for MySQL3456.2.1 The C aPl3476.2.2 The perl dbl api,,3476.2.3 The phP apl3496.3 Choosing an API3506.3.1 Execution Environment3516.3.2 Performance3526.3.3 Development Time3546.3. 4 Portability3577 Writing MysQL Programs Using C.....,.......,.3597. 1 Compiling and Linking Client Programs3607.2 Connecting to the Server3637.3 Handling Errors and Processing Command Options3677.3.1 Checking for Errors3677.3.2 Getting Connection Parameters at Runtime3717.3.3 Incorporating Option-Processing into a MySQL Client Program.... 3847. 4 Processing SQL Statements3897.4.1 Handling Statements That Modify Rows3907.4.2 Handling Statements That Return a Result Set3917.4.3 A General-Purpose statement Handler3947.4.4 Alternative Approaches to Statement Processing,3967.4.5 mysql store result() Versus mysql use result().. 398Contents7.4.6 Using Result Set Metadata,,4007.4.7 Encoding Special Characters and Binary Data4057. 5 An Interactive Statement-Execution Program4097.6 Writing Clients That Include SSL Support4107. 7 Using the Embedded Server Library4167.7.1 Writing an Embedded Server Application4167.7.2 Producing the Application Executable Binary4197.8 Using Multiple-Statement Execution,,4207.9 Using Server-Side Prepared Statements4228 Writing MysQL Programs Using Perl DBI4358.1 Perl Script Characteristics4368.2 Perl dbl Overview,,,4378.2. 1 DBI Data Types4378.2.2 A Simple dbl script4378.2.3 Handling Errors4438.2. 4 Handling Statements That Modify Rows4468.2.5 Handling Statements That Return a Result Set,,,4478.2.6 Quoting Special Characters in Statement Strings4578.2.7 Placeholders and Prepared statements4608.2.8 Binding Query Results to Script variables.4638.2.9 Specifying Connection Parameters648.2.10 Debugging4688.2. 11 Using Result set Metadata4718.2.12 Performing Transactions,,,4758.3 Putting DbI to Work,,,4778.3.1 Generating the Historical League Directory4788.3.2 Sending Membership Renewal Notices4848.3.3 Historical League Member Entry Editing4908.3.4 Finding Historical League Members with Common Interests4968.3.5 Putting the Historical League Directory Online4978. 4 Using dbl in Web applications,,,5008.4.1 Setting Up Apache for CGI Scripts...5028.4.2 A Brief CGl. pm Primer5038.4.3 Connecting to the MySQL Server from Web Scripts5108. 4, 4A Web-Based Database Bl5138.4.5 A Grade-Keeping Project Score Browser517