book

MySQL, 5th Edition

by Paul DuBois

April 2013

Intermediate to advanced

1276 pages

42h 16m

English

Addison-Wesley Professional

Read now

Unlock full access

Why Choose MySQL?What You Can Expect from This BookRoad Map to This BookHow to Read This BookVersions of Software Covered in This BookConventions Used in This BookAdditional Resources

1.1. How MySQL Can Help You1.2. A Sample Database1.3. Basic Database Terminology1.4. A MySQL Tutorial
1.6. Where to Now?
2.1. The Server SQL Mode2.2. MySQL Identifier Syntax and Naming Rules2.3. Case Sensitivity in SQL Statements2.4. Character Set Support2.5. Selecting, Creating, Dropping, and Altering Databases2.6. Creating, Dropping, Indexing, and Altering Tables2.7. Obtaining Database Metadata2.8. Performing Multiple-Table Retrievals with Joins
2.10. Performing Multiple-Table Retrievals with UNION2.11. Multiple-Table Deletes and Updates2.12. Performing Transactions2.13. Foreign Keys and Referential Integrity2.14. Using FULLTEXT Searches
3.1. Data Value Categories3.2. MySQL Data Types
3.4. Working with Sequences3.5. Expression Evaluation and Type Conversion3.6. Choosing Data Types
4.1. Using Views4.2. Using Stored Programs4.3. Security for Views and Stored Programs
5.1. Using Indexing5.2. The MySQL Query Optimizer5.3. Choosing Data Types for Efficient Queries5.4. Choosing Table Storage Formats for Efficient Queries5.5. Loading Data Efficiently5.6. Scheduling, Locking, and Concurrency
6.1. Why Write Your Own MySQL Programs?6.2. APIs Available for MySQL6.3. Choosing an API
7.1. Compiling and Linking Client Programs7.2. Connecting to the Server7.3. Handling Errors and Processing Command Options7.4. Processing SQL Statements7.5. An Interactive Statement-Execution Program7.6. Writing Clients That Include SSL Support7.7. Using Multiple-Statement Execution7.8. Using Server-Side Prepared Statements7.9. Using Prepared CALL Support
8.1. Perl Script Characteristics8.2. Perl DBI Overview8.3. Putting DBI to Work
9.1. PHP Overview9.2. Putting PHP to Work
10.1. MySQL Components10.2. General MySQL Administration10.3. Access Control and Security10.4. Database Maintenance, Backups, and Replication
11.1. The Data Directory Location11.2. Structure of the Data Directory11.3. Relocating Data Directory Contents
12.1. Securing a New MySQL Installation12.2. Arranging for MySQL Server Startup and Shutdown12.3. Using System and Status Variables12.4. The Plugin Interface12.5. Storage Engine Configuration12.6. Globalization Issues12.7. Server Tuning12.8. Server Logs12.9. Running Multiple Servers12.10. Updating MySQL
13.1. Securing Filesystem Access to MySQL13.2. Managing MySQL User Accounts13.3. Grant Table Structure and Contents13.4. How the Server Controls Client Access13.5. Setting Up Secure Connections Using SSL
14.1. Principles of Preventive Maintenance14.2. Performing Database Maintenance with the Server Running14.3. General Preventive Maintenance14.4. Making Database Backups14.5. Copying Databases to Another Server14.6. Checking and Repairing Database Tables14.7. Using Backups for Data Recovery14.8. Setting Up Replication Servers
A.1. Obtaining MySQL and Related SoftwareA.2. MySQL Installation NotesA.3. Perl DBI Installation NotesA.4. PHP and PDO Installation Notes
B.1. Numeric TypesB.2. String TypesB.3. Temporal (Date and Time) Types
C.1. OperatorsC.2. Functions
D.1. System VariablesD.2. Status VariablesD.3. User-Defined Variables
E.1. SQL Statement Syntax (Noncompound Statements)
E.3. Comment Syntax
F.1. Displaying a Program’s Help MessageF.2. Specifying Program OptionsF.3. myisamchkF.4. mysqlF.5. mysql.serverF.6. mysql_configF.7. mysql_install_dbF.8. mysql_upgradeF.9. mysqladminF.10. mysqlbinlogF.11. mysqlcheckF.12. mysqldF.13. mysqld_multiF.14. mysqld_safeF.15. mysqldumpF.16. mysqlimportF.17. mysqlshowF.18. perror
G.1. Compiling and LinkingG.2. C API Data StructuresG.3. C API Functions
H.1. Writing ScriptsH.2. DBI MethodsH.3. DBI Utility FunctionsH.4. DBI AttributesH.5. DBI Environment Variables
I.1. Writing PHP ScriptsI.2. PDO ClassesI.3. PDO Methods

Content preview from MySQL, 5th Edition

8. Writing MySQL Programs Using Perl DBI

This chapter describes how to use the Perl DBI interface for MySQL. It does not discuss DBI philosophy or architecture. For information about those aspects of DBI (particularly in comparison with the C and PHP APIs), see Chapter 6, “Introduction to MySQL Programming.”

The examples discussed here draw on our sample database, sampdb, using the tables created for the grade-keeping project and for the Historical League in Chapter 1, “Getting Started with MySQL.”

I assume here a minimum version of DBI 1.50, although most of the material applies to earlier versions as well. DBI 1.50 requires at least Perl 5.6.0 (with 5.6.1 preferred). As of DBI 1.611, the minimum Perl version is 5.8.1. You must also have the ...