book

Managing & Using MySQL, 2nd Edition

by Tim King, George Reese, Randy Yarger, Hugh E. Williams

April 2002

Intermediate to advanced

442 pages

16h 37m

English

O'Reilly Media, Inc.

Read now

Unlock full access

From Tim King
I. Introduction
1. MySQL
1.1. Relational Databases
1.2. The History of MySQL
1.3. MySQL Design
1.4. MySQL Features
1.5. MySQL Applications
1.6. What You Get
2. Installation
2.1. Preparation
2.2. Unix Installation
2.2.1. Binary (Tarball) Distributions
2.2.2. Binary (RPM) Distributions
2.2.3. Source Distributions
2.3. Windows Installation
2.3.1. Windows 9x Startup
2.3.2. Windows NT/2000 Startup
3. SQL According to MySQL
3.1. SQL Basics
3.1.1. The SQL Story
3.1.2. The Design of SQL
3.1.3. Sending SQL to MySQL
3.2. Database Creation
3.3. Table Management
3.4. MySQL Data Types
3.4.1. Numeric Types
3.4.2. Character Types
3.4.3. Binary Data Types
3.4.4. Enumerations and Sets
3.4.5. Other Kinds of Data
3.5. Indexing
3.6. Managing Data
3.6.1. Inserts
3.6.2. Sequence Generation
3.6.3. Updates
3.6.4. The WHERE Clause
3.6.5. Deletes
3.7. Queries
3.7.1. Joins
3.7.2. Aliasing
3.7.3. Ordering and Grouping
3.7.3.1. Basic ordering3.7.3.2. Localized sorting3.7.3.3. Grouping
3.7.4. Limiting Results
3.8. SQL Operators
3.8.1. Logical Operators
3.8.2. Null’s Idiosyncrasies
3.8.3. Membership Tests
3.8.4. Pattern Matching
3.9. Advanced Features
3.9.1. Full Text Searching
3.9.1.1. The Basics3.9.1.2. Relevance values3.9.1.3. Boolean mode3.9.1.4. Tips
3.9.2. Transactions
3.9.3. Table Locking
3.9.4. Functions
3.9.4.1. Date functions3.9.4.2. String functions
3.9.5. Outer Joins
3.9.6. Unions
3.9.7. Batch Processing
3.9.7.1. Command-line loads3.9.7.2. The LOAD command3.9.7.3. Pulling data from MySQL
4. Database Administration
4.1. Configuration
4.1.1. File Locations
4.1.2. File Content
4.2. Server Startup and Shutdown
4.2.1. Unix/Linux
4.2.1.1. SVR44.2.1.2. Other Unix
4.2.2. Mac OS X
4.2.3. Windows NT/2000
4.3. Logging
4.3.1. The Error Log
4.3.2. The Binary Log
4.3.3. The Slow Query Log
4.3.4. Log Rotation
4.4. Backup
4.4.1. mysqldump
4.4.2. mysqlhotcopy
4.5. Recovery
4.5.1. mysqldump Recovery
4.5.2. mysqlhotcopy Recovery
4.6. Table Maintenance and Crash Recovery
4.6.1. Checking a Table
4.6.2. Repairing a Table
4.6.3. Scheduled Table Checking
II. MySQL Administration
5. Performance Tuning
5.1. An Approach to Performance Tuning
5.2. Application Tuning
5.2.1. Host Application Tuning
5.2.2. SQL Query Tuning
5.2.2.1. Index guidelines5.2.2.2. EXPLAIN SELECT5.2.2.3. Other options
5.3. Database Server Tuning
5.4. Operating System/Hardware Tuning
6. Security
6.1. Database Security
6.1.1. User Management
6.1.2. Privilege Management
6.1.2.1. GRANT and REVOKE6.1.2.2. The security tables
6.1.3. Recovering from Password and Permission Problems
6.2. System Security
6.2.1. Operating System Security
6.2.2. Hardware Security
6.2.3. Network Security
6.2.3.1. Network topology6.2.3.2. Encryption6.2.3.3. Direct compromise
6.3. Application Security
6.3.1. The Application Server
6.3.1.1. User management6.3.1.2. Resource protection
6.3.2. Client Applications
7. Database Design
7.1. Database Design Primer
7.1.1. Database Entities
7.1.2. Entity Attributes
7.1.3. Data Model
7.2. Normalization
7.2.1. First Normal Form
7.2.2. The Unique Identifier
7.2.3. Relationships
7.2.4. Second Normal Form
7.2.5. Kinds of Relationships
7.2.6. Refining Relationships
7.2.7. More 2NF
7.2.8. Third Normal Form
7.3. A Logical Data-Modeling Methodology
7.4. Physical Database Design
7.4.1. Tables and Columns
7.4.2. Foreign Keys
III. MySQL Programming
8. Database Applications
8.1. Architecture
8.1.1. Client/Server Architecture
8.1.1.1. Application logic8.1.1.2. Fat and thin clients
8.1.2. Distributed Application Architecture
8.1.3. Web Architecture
8.2. Connections and Transactions
8.2.1. Connections
8.2.2. Transactions
8.2.2.1. Transaction isolation levels8.2.2.2. Using READ UNCOMMITTED
8.3. Object/Relational Modeling
9. Perl
9.1. Introduction to DBI
9.1.1. Basic Perl Example
9.1.2. Setting Up the Database and Program
9.1.3. Error Handling and Attributes
9.1.4. Introducing Bind Variables and Optimizations
9.2. DBI and CGI
9.2.1. Introduction to Perl CGI
9.2.2. The Model/View/Controller Methodology
9.2.3. A Sample CGI/DBI Program
9.3. A General Model for Maintainable Perl Programs
9.3.1. A Model for Relational Data
9.3.2. Implementing the Model
9.3.2.1. The Publisher class9.3.2.2. Methods that build and execute SQL9.3.2.3. Methods that handle WHERE clauses9.3.2.4. Getter/setter methods9.3.2.5. Primary key select method9.3.2.6. Constructors9.3.2.7. The DB class9.3.2.8. The mysql class9.3.2.9. The Cache class
9.3.3. Example of the Model’s Use
10. Python
10.1. DB-API
10.1.1. The Database Connection
10.1.2. Cursors
10.1.3. Parameterized SQL
10.1.4. Other Objects
10.2. Proprietary Operations
10.3. Applied DB-API
11. PHP
11.1. Introducing PHP
11.1.1. A Short Language Primer
11.2. Installing PHP
11.2.1. Getting Started Under Unix
11.2.1.1. Installation problems
11.2.2. Getting Started Under Microsoft Windows
11.3. Accessing the MySQL DBMS with PHP
11.3.1. The Wedding Gift Registry Database
11.3.2. Opening and Using a Database Connection
11.3.3. Handling Results
11.3.4. Frequently Used MySQL Library Functions
11.3.5. Handling MySQL Errors
11.3.6. Include Files
11.4. Securing User Data
11.5. Managing Sessions
11.6. Writing Data with PHP
11.7. Using the HTML <form> Environment
11.8. Where to Find Out More
12. C API
12.1. API Overview
12.1.1. The Connection
12.1.2. Queries and Results
12.1.3. Closing the Connection
12.2. The C API in Practice
12.2.1. Support Functions
12.2.2. Quote Retrieval
12.2.3. Adding Symbols
12.3. Advanced Issues
13. Java
13.1. The JDBC API
13.1.1. The JDBC Architecture
13.1.2. Connecting to MySQL
13.1.2.1. Data source connectivity13.1.2.2. Driver manager connectivity
13.1.3. Maintaining Portability Using Properties Files
13.1.3.1. Properties files13.1.3.2. Data sources revisited
13.2. Simple Database Access
13.2.1. Queries and Result Sets
13.2.2. Error Handling and Clean Up
13.3. Dynamic Database Access
13.3.1. Metadata
13.3.2. Processing Dynamic SQL
13.4. A Guest Book Servlet
14. Extending MySQL
14.1. User-Defined Functions
14.1.1. Standard Functions
14.1.1.1. The init routine14.1.1.2. The main routine14.1.1.3. The deinit routine
14.1.2. Aggregate Functions
14.1.2.1. init14.1.2.2. reset14.1.2.3. add14.1.2.4. main14.1.2.5. deinit14.1.2.6. Aggregate example
14.1.3. Calling a UDF
14.2. Alternative Character Sets
14.2.1. Simple Character Sets
14.2.2. Complex Character Sets
IV. MySQL Reference
15. SQL Syntax for MySQL
15.1. Basic Syntax
15.1.1. Literals
15.1.2. Identifiers
15.1.3. Comments
15.2. SQL Commands
16. MySQL Data Types
16.1. Numeric Data Types
16.2. String Data Types
16.3. Date Data Types
16.4. Complex Data Types
17. Operators and Functions
17.1. Operators
17.1.1. Rules of Precedence
17.1.2. Arithmetic Operators
17.1.3. Comparison Operators
17.1.4. Logical Operators
17.2. Functions
17.2.1. Aggregate Functions
17.2.2. General Functions
18. MySQL PHP API Reference
18.1. Data Types
18.2. Functions
19. C Reference
19.1. Data Types
19.2. Functions
20. The Python DB-API
20.1. Module: MySQLdb
20.1.1. Module Attributes
20.1.2. Module Methods
20.1.3. Connection Attributes
20.1.4. Connection Methods
20.1.5. Cursor Attributes
20.1.6. Cursor Methods
Index
About the Authors
Colophon
Copyright

Content preview from Managing & Using MySQL, 2nd Edition

A Sample CGI/DBI Program

In this section, we present—once again—a program that queries a MySQL database and prints results from the query. This time, however, we will format the results into a CGI page. Instead of generating HTML directly, like the $cgi->h2( ) call in the previous section, we’re going to separate the Model from the view in classic MVC fashion. We’ll use a popular Perl module called the Template Toolkit.

In this program, we store all the data we want to print in a set of Perl hashes. There is a headers hash used to generate the headers of a table (TH elements in HTML) and a records hash used to store information on each row retrieved from the database. We even use a hash element to store the title of the web page. This hash is the Model in our methodology, because it indicates the data and their relationships without making any presuppositions about how they are displayed.

Then we invoke the Perl Template module to turn our hash into HTML. The module uses our hash and a template we develop and store in a file called book_view.tt. Here’s the beginning of the file:

<html>
 <head>
     <title>[% title %]</title>
 </head>

It’s HTML, but has a placeholder for the title. We can therefore use the template over and over with pages that have different titles. There are placeholders for the other elements of our hash as well, and a lot of logic to do looping and other runtime choices. For instance, Example 9-4 shows a loop in the book_view.tt template file that causes odd-numbered ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596002114Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Managing & Using MySQL, 2nd Edition

by Tim King, George Reese, Randy Yarger, Hugh E. Williams

A Sample CGI/DBI Program

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

More than 5,000 organizations count on O’Reilly

Julian F.

Addison B.

Amir M.

Mark W.

You might also like

MySQL 8 Cookbook

Advanced MySQL 8

MySQL Stored Procedure Programming

MySQL Reference Manual

Publisher Resources