MySQL 5.0 Certification Study Guide
MySQL Press
978-0-672-32812-1 (ISBN)
- Titel ist leider vergriffen;
keine Neuauflage - Artikel merken
This is the official guide to passing the two MySQL certification tests for MySQL 5, the long-awaited major revision of MySQL. The number of MySQL certification exams taken has doubled in the last six months.
Certcities.com lists the MySQL certification as one of the top 10 certifications to grow in 2005. MySQL professionals need a way to distinguish themselves from the vast majority of database administrators and developers. With more than 4 million active installations, MySQL is the world's most popular open-source database. Known for its speed, reliability and case of use, MySQL has become a low-cost alternative to expensive database systems such as Oracle, IBM and Microsoft. MySQL AB has aggressively improved the feature set of MySQL with MySQL 5, making it more suitable for enterprise-level applications and uses. The MySQL certification tests, available at over 3,000 PearsonVUE testing centers, is a key component of this enterprise growth strategy, establishing a base level of skills for database users, administrators and programmers.
The MySQL Core Certification is aimed at the database user who wants proof of his or her abilities in such fundamental areas as SQL, data entry and maintenance, and data extraction. The MySQL Professional Certification test is designed for the advanced user who wants to prove his or her knowledge in such areas as database management, installation, security, disaster prevention and optimization. Both tests are thoroughly covered in the MySQL 5.0 Certification Study Guide. Written by Paul DuBois, the leading author of books on MySQL topics, and reviewed for technical accuracy by MySQL AB, this book is the fastest, most reliable way for MySQL users, developers, and administrators to prepare for either of the MySQL tests.
Paul DuBois is the leading author of books on MySQL topics. His book, entitled MySQL, is widely considered the definitive work on MySQL. He is a developer for MySQL AB, where he edits and maintains the official English-language documentation for MySQL. Stefan Hinz is leads the MySQL documentation team, is a MySQL trainer and consultant, and the German translator of the MySQL Reference Manual. He is also the translator of Paul's MySQL Cookbook (O'Reilly and Associates) and translator and author of MySQL-related German books. Stefan passed the MySQL Certification exam before he joined MySQL AB.
Introduction.
About This Book.
Sample Exercises
Other Required Reading
Manuals
Sample Data
Study Guide Errata
Certification Information at www.mysql.com
The MySQL Certification Candidate Guide
The Certification Mailing List
Conventions Used in This Book
Running MySQL on Microsoft Windows
About the Exams
Registering for an Exam
Going to the Exam
Taking the Exam
Reading Questions
Answering Questions
After the Exam
Retaking Exams
Warning
Interpreting DESCRIBE Output
Sample Tables
MYSQL DEVELOPER EXAMS.
MySQL Developer I Exam.
1. Client/Server Concepts.
General MySQL Architecture
Invoking Client Programs
General Command Option Syntax
Connection Parameter Options
Using Option Files
Selecting a Default Database
Establishing a Connection with a GUI Client
Server SQL Modes
2. The mysql Client Program.
Using mysql Interactively
Statement Terminators
The mysql Prompts
Using Editing Keys in mysql
Using Script Files with mysql
mysql Output Formats
Client Commands and SQL Statements
Using Server-Side Help
Using the --safe-updates Option
3. MySQL Query Browser.
MySQL Query Browser Capabilities
Using MySQL Query Browser
Using the Query Window
Entering Queries
The Result Area
The Script Editor
Stored Routine Management
The Object and Information Browsers
The MySQL Table Editor
Connection Management
Using the Connection Dialog
Editing Connection Profiles
The Options Dialog
4. MySQL Connectors.
MySQL Client Interfaces
MySQL Connector/ODBC
MySQL Connector/J
MySQL Connector/NET
5. Data Types.
Data Type Overview
Numeric Data Types
Integer Data Types
Floating-Point Data Types
Fixed-Point Data Types
The BIT Data Type
String Data Types
Character Set Support
Non-Binary String Data Types: _CHAR, VARCHAR, TEXT
Binary String Data Types: BINARY, VARBINARY, BLOB
The ENUM and SET Data Types
Temporal Data Types
The DATE, TIME, DATETIME, and YEAR Data Types
The TIMESTAMP Data Type
Per-Connection Time Zone Support
Column Attributes
Numeric Column Attributes
String Column Attributes
General Column Attributes
Using the AUTO_INCREMENT Column Attribute
Handling Missing or Invalid Data Values
Handling Missing Values
Handling Invalid Values in Non-Strict Mode
Handling Invalid Values in Strict Mode
Enabling Additional Input Data Restrictions
Overriding Input Data Restrictions
6. Identifiers.
Identifier Syntax
Case Sensitivity of Identifiers
Using Qualified Names
Using Reserved Words as Identifiers
7. Databases.
Database Properties
Creating Databases
Altering Databases
Dropping Databases
Obtaining Database Metadata
8. Tables and Indexes.
Table Properties
Creating Tables
Creating Tables Using an Explicit Definition
Specifying the Storage Engine for a Table
Creating Tables Based on Existing Tables
Using TEMPORARY Tables
Altering Tables
Adding and Dropping Columns
Modifying Existing Columns
Renaming a Table
Specifying Multiple Table Alterations
Dropping Tables
Emptying Tables
Indexes
Types of Indexes
Creating Indexes
Choosing an Indexing Algorithm
Dropping Indexes
Obtaining Table and Index Metadata
9. Querying for Data.
Using SELECT to Retrieve Data
Specifying Which Columns to Retrieve
Renaming Retrieved Columns
Identifying the Database Containing a Table
Specifying Which Rows to Retrieve
Using ORDER BY to Sort Query Results
The Natural Sort Order of Data Types
Limiting a Selection Using LIMIT
Using DISTINCT to Eliminate Duplicates
Aggregating Results
The MIN() and MAX() Aggregate Functions
The SUM() and AVG() Aggregate Functions
The COUNT() Aggregate Function
The GROUP_CONCAT() Function
Aggregation for NULL Values or Empty Sets
Grouping Results
GROUP BY and Sorting
Selecting Groups with HAVING
Using GROUP BY and WITH ROLLUP
Using UNION
10. SQL Expressions.
Components of SQL Expressions
Numeric Expressions
String Expressions
Case Sensitivity in String Comparisons
Using LIKE for Pattern Matching
Temporal Expressions
NULL Values
Functions in SQL Expressions
Comparison Functions
Control Flow Functions
Aggregate Functions
Mathematical Functions
String Functions
Temporal Functions
NULL-Related Functions
Comments in SQL Statements
11. Updating Data.
Update Operations
The INSERT Statement
Adding Multiple Records with a Single INSERT Statement
Handling Duplicate Key Values
Using INSERT ... ON DUPLICATE KEY UPDATE
The REPLACE Statement
The UPDATE Statement
Using UPDATE with ORDER BY and LIMIT
Preventing Dangerous UPDATE Statements
Multiple-Table UPDATE Statements
The DELETE and TRUNCATE TABLE Statements
Using DELETE with ORDER BY and LIMIT
Multiple-Table DELETE Statements
Privileges Required for Update Statements
MySQL Developer II Exam.
12. Joins.
Overview
Writing Inner Joins
Writing Inner Joins with the Comma Operator
Writing Inner Joins with INNER JOIN
Writing Outer Joins
Writing LEFT JOIN Queries
Writing RIGHT JOIN Queries
Resolving Name Clashes Using Qualifiers and Aliases
Qualifying Column Names
Qualifying and Aliasing Table Names
Multiple-Table UPDATE and DELETE Statements
13. Subqueries.
Types of Subqueries
Subqueries as Scalar Expressions
Correlated Subqueries
Comparing Subquery Results to Outer Query Columns
Using ALL, ANY, and SOME
Using IN
Using EXISTS
Comparison Using Row Subqueries
Using Subqueries in the FROM Clause
Converting Subqueries to Joins
Converting Subqueries to Inner Joins
Converting Subqueries to Outer Joins
Using Subqueries in Updates
14. Views.
Reasons to Use Views
Creating Views
Restrictions on Views
View Algorithms
Updatable Views
Altering Views
Dropping Views
Checking Views
Obtaining View Metadata
Privileges Required for Views
15. Importing and Exporting Data.
Import and Export Operations
Importing and Exporting Using SQL
Importing Data with LOAD DATA INFILE
Exporting Data with SELECT ... INTO OUTFILE
Data File Format Specifiers
Importing and Exporting NULL Values
Importing and Exporting Data from the Command Line
Importing Data with mysqlimport
Exporting Data with mysqldump
16. User Variables.
User Variable Syntax
User Variable Properties
17. Prepared Statements.
Benefits of Prepared Statements
Using Prepared Statements from the mysql Client
Preparing a Statement
Executing a Prepared Statement
Deallocating Prepared Statements
18. Stored Procedures and Functions.
Benefits of Stored Routines
Differences Between Stored Procedures and Functions
The Namespace for Stored Routines
Defining Stored Routines
Creating Stored Routines
Compound Statements
Declaring Parameters
The DECLARE Statement
Variables in Stored Routines
Conditions and Handlers
Cursors
Retrieving Multiple Result Sets
Flow Control
Altering Stored Routines
Dropping Stored Routines
Invoking Stored Routines
Obtaining Stored Routine Metadata
Stored Routine Privileges and Execution Security
19. Triggers.
Reasons to Use Triggers
Trigger Concepts
Creating a Trigger
Restrictions on Triggers
Referring to Old and New Column Values
Destroying a Trigger
Privileges Required for Triggers
20. Obtaining Database Metadata.
Overview of Metadata Access Methods
Using INFORMATION_SCHEMA to Obtain Metadata
Using SHOW and DESCRIBE to Obtain Metadata
Using mysqlshow to Obtain Metadata
21. Debugging MySQL Applications.
Interpreting Error Messages
The SHOW WARNINGS Statement
The SHOW ERRORS Statement
The perror Utility
22. Basic Optimizations.
Overview of Optimization Principles
Using Indexes for Optimization
Types of Indexes
Principles for Index Creation
Indexing Column Prefixes
Leftmost Index Prefixes
General Query Enhancement
Query Rewriting Techniques
Using EXPLAIN to Obtain Optimizer Information
Optimizing Queries by Limiting Output
Using Summary Tables
Optimizing Updates
Choosing Appropriate Storage Engines
Normalization
MYSQL DBA EXAMS.
MySQL DBA I Exam.
23. MySQL Architecture.
Client/Server Overview
Communication Protocols
The SQL Parser and Storage Engine Tiers
How MySQL Uses Disk Space
How MySQL Uses Memory
24. Starting, Stopping, and Configuring MySQL.
Types of MySQL Distributions
MySQL Binary Distributions
MySQL Source Distributions
Starting and Stopping MySQL Server on Windows
Server Startup Prerequisites on Windows
Running MySQL Server Manually on Windows
Running MySQL Server as a Windows Service
Starting and Stopping MySQL Server on Unix
Server Startup Prerequisites on Unix
Choosing a Server Startup Method on Unix
Runtime MySQL Configuration
Log and Status Files
The General Query Log
The Binary Log
The Slow Query Log
The Error Log
Status Files
Loading Time Zone Tables
Security-Related Configuration
Setting the Default SQL Mode
Upgrading MySQL
25. Client Programs for DBA Work.
Overview of Administrative Clients
MySQL Administrator
mysql
mysqladmin
mysqlimport
mysqldump
Client Program Limitations
26. MySQL Administrator.
MySQL Administrator Capabilities
Using MySQL Administrator
Starting MySQL Administrator
Selecting an Operational Mode
Server Monitoring Capabilities
Server Information
Server Connections
Health
Server Logs
Replication Status
Catalogs
Server Configuration
Service Control
Startup Variables
User Administration
Backup and Restore Capabilities
Making Backups
Restoring Backups
MySQL Administrator System Tray Monitor
27. Character Set Support.
Performance Issues
Choosing Data Types for Character Columns
28. Locking.
Locking Concepts
Explicit Table Locking
Advisory Locking
29. Storage Engines.
MySQL Storage Engines
The MyISAM Engine
MyISAM Locking Characteristics
MyISAM Row-Storage Formats
The MERGE Engine
MERGE Locking Characteristics
The InnoDB Engine
The InnoDB Tablespace and Logs
InnoDB and ACID Compliance
The InnoDB Transaction Model
InnoDB Locking Characteristics
InnoDB Isolation Levels, Multi-Versioning, and Concurrency
Using Foreign Keys
Configuring and Monitoring InnoDB
The MEMORY Engine
MEMORY Indexing Options
The FEDERATED Engine
The Cluster Storage Engine
Other Storage Engines
30. Table Maintenance.
Types of Table Maintenance Operations
SQL Statements for Table Maintenance
CHECK TABLE
REPAIR TABLE
ANALYZE TABLE
OPTIMIZE TABLE
Client and Utility Programs for Table Maintenance
The mysqlcheck Client Program
The myisamchk Utility
Options for mysqlcheck and myisamchk
Repairing InnoDB Tables
Enabling MyISAM Auto-Repair
31. The INFORMATION_SCHEMA Database.
INFORMATION_SCHEMA Access Syntax
INFORMATION_SCHEMA Versus SHOW
Limitations of INFORMATION_SCHEMA
32. Data Backup and Recovery Methods.
Introduction
Binary Versus Textual Backups
Making Binary Backups
Making Binary MyISAM Backups
Making Binary InnoDB Backups
Other Binary Backup Tools
Conditions for Binary Portability
Making Text Backups
Making Text Backups via SQL
Making Text Backups with mysqldump
Making Text Backups with MySQL Administrator
Backing Up Log and Status Files
Replication as an Aid to Backup
MySQL Cluster as Disaster Prevention
Data Recovery
Reloading mysqldump Output
Reloading Dumps with MySQL Administrator
Processing Binary Log Contents
33. Using Stored Routines and Triggers for Administration.
Using Stored Routines and Triggers for Security Purposes
Using Stored Routines to Enhance Performance
MySQL DBA II EXAM.
34. User Management.
User Account Management
Types of Privileges That MySQL Supports
The Grant Tables
Approaches to Account Management
Creating and Dropping User Accounts
Specifying Account Names
Granting Privileges
Revoking Privileges
Changing Account Passwords
When Privilege Changes Take Effect
Specifying Resource Limits
Privileges Needed for Account Management
Client Access Control
Connection Request Checking
Statement Privilege Checking
Resource Limit Checking
Disabling Client Access Control
35. Securing the MySQL Installation.
Security Issues
Operating System Security
Filesystem Security
Log Files and Security
Network Security
Securing the Initial MySQL Accounts
General Privilege Precautions
MySQL Cluster Network Security
FEDERATED Table Security
36. Upgrade-Related Security Issues.
Upgrading the Privilege Tables
Security-Related SQL Mode Values
37. Optimizing Queries.
Identifying Candidates for Query Analysis
Using EXPLAIN to Analyze Queries
How EXPLAIN Works
Analyzing a Query
EXPLAIN Output Columns
Using SHOW WARNINGS for Optimization
MyISAM Index Caching
38. Optimizing Databases.
General Table Optimizations
Normalization
MyISAM-Specific Optimizations
MyISAM Row-Storage Formats
Keep Optimizer Information Up to Date
FULLTEXT Indexes
Specifying MyISAM Maximum Row Count
InnoDB-Specific Optimizations
MERGE-Specific Optimizations
MEMORY-Specific Optimizations
39. Optimizing the Server.
Interpreting mysqld Server Information
Accessing Server System Variables
Accessing Server Status Variables
Measuring Server Load
Tuning Memory Parameters
Global (Server-Wide) Parameters
Per-Client Parameters
Using the Query Cache
Enabling the Query Cache
Measuring Query Cache Utilization
40. Interpreting Diagnostic Messages.
Sources of Diagnostic Information
Using the Error Log for Diagnostic Purposes
Using The Slow Query Log for Diagnostic Purposes
41. Optimizing the Environment.
Choosing Hardware for MySQL Use
Configuring Disks for MySQL Use
Moving Databases Using Symbolic Links
MyISAM Table Symlinking
Network Issues
Optimizing the Operating System for MySQL Use
42. Scaling MySQL.
Using Multiple Servers
Replication
Setting Up Replication
The Binary and Relay Logs
Replication-Related Threads
Replication Troubleshooting
Replication Compatibility and Upgrading
APPENDIXES.
A. References.
B. Other Offers.
Index.
| Erscheint lt. Verlag | 8.9.2005 |
|---|---|
| Zusatzinfo | Illustrations |
| Verlagsort | Upper Saddle River, NJ |
| Sprache | englisch |
| Maße | 178 x 230 mm |
| Gewicht | 1090 g |
| Themenwelt | Informatik ► Datenbanken ► MySQL |
| Informatik ► Weitere Themen ► Zertifizierung | |
| ISBN-10 | 0-672-32812-7 / 0672328127 |
| ISBN-13 | 978-0-672-32812-1 / 9780672328121 |
| Zustand | Neuware |
| Haben Sie eine Frage zum Produkt? |
aus dem Bereich
