MySQL 5 Certification Study Guide [With] CDROM: Guarding Your IT Infrastructure [With CDROM]De (autor) Stefan Hinz, Carsten Pedersen, Paul Dubois
en Limba Engleză Mixed media product – 24 Aug 2005
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.
Dimensiuni: 175 x 229 x 38 mm
Greutate: 1.09 kg
Editura: MySQL Press
Locul publicării: Indianapolis, United States
About This Book.
Other Required Reading
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
After the Exam
Interpreting DESCRIBE Output
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
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
The Result Area
The Script Editor
Stored Routine Management
The Object and Information Browsers
The MySQL Table Editor
Using the Connection Dialog
Editing Connection Profiles
The Options Dialog
4. MySQL Connectors.
MySQL Client Interfaces
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
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
Case Sensitivity of Identifiers
Using Qualified Names
Using Reserved Words as Identifiers
Obtaining Database Metadata
8. Tables and Indexes.
Creating Tables Using an Explicit Definition
Specifying the Storage Engine for a Table
Creating Tables Based on Existing Tables
Using TEMPORARY Tables
Adding and Dropping Columns
Modifying Existing Columns
Renaming a Table
Specifying Multiple Table Alterations
Types of Indexes
Choosing an Indexing Algorithm
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
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
GROUP BY and Sorting
Selecting Groups with HAVING
Using GROUP BY and WITH ROLLUP
10. SQL Expressions.
Components of SQL Expressions
Case Sensitivity in String Comparisons
Using LIKE for Pattern Matching
Functions in SQL Expressions
Control Flow Functions
Comments in SQL Statements
11. Updating Data.
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.
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
Types of Subqueries
Subqueries as Scalar Expressions
Comparing Subquery Results to Outer Query Columns
Using ALL, ANY, and SOME
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
Reasons to Use Views
Restrictions on 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
The DECLARE Statement
Variables in Stored Routines
Conditions and Handlers
Retrieving Multiple Result Sets
Altering Stored Routines
Dropping Stored Routines
Invoking Stored Routines
Obtaining Stored Routine Metadata
Stored Routine Privileges and Execution Security
Reasons to Use Triggers
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
Choosing Appropriate Storage Engines
MYSQL DBA EXAMS.
MySQL DBA I Exam.
23. MySQL Architecture.
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
Loading Time Zone Tables
Setting the Default SQL Mode
25. Client Programs for DBA Work.
Overview of Administrative Clients
Client Program Limitations
26. MySQL Administrator.
MySQL Administrator Capabilities
Using MySQL Administrator
Starting MySQL Administrator
Selecting an Operational Mode
Server Monitoring Capabilities
Backup and Restore Capabilities
MySQL Administrator System Tray Monitor
27. Character Set Support.
Choosing Data Types for Character Columns
Explicit Table 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
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.
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
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
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.
Operating System Security
Log Files and 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
MyISAM Row-Storage Formats
Keep Optimizer Information Up to Date
Specifying MyISAM Maximum Row Count
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
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
Optimizing the Operating System for MySQL Use
42. Scaling MySQL.
Using Multiple Servers
Setting Up Replication
The Binary and Relay Logs
Replication Compatibility and Upgrading
B. Other Offers.
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.
Textul de pe ultima copertă
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.