In the world of online auction houses, instant mortgages, worldwide reservations, global communication, and overnight deliveries... more

In the world of online auction houses, instant mortgages, worldwide reservations, global communication, and overnight deliveries, it’s not surprising that even the least technically savvy individuals in our culture are, to some degree, familiar with the concept of a database. As anyone who works with data knows, databases form the backbone for this age of information, and access to those databases can determine one’s ability to perform critical tasks effectively and efficiently. To meet the ever-increasing demands for information, programmers are continuously building bigger and better applications that can access and modify data stored in various database systems. Yet in order to create these applications, programmers must have some knowledge of the systems that contain the needed data.

Over the years, as the demands for information have grown, so too have the database systems that have attempted to meet these demands. However, along with this evolution, we have seen an increase in the costs associated with storing data as well as an increase in the demand for products that can run on multiple platforms and can be optimized based on the needs of specific types of organizations. In response to this changing climate, MySQL has emerged as the most popular open-source database management system (DBMS) in the world. Consequently, organizations everywhere are jumping on the MySQL bandwagon, increasing the demand for those who know how to use MySQL to manage data and those who know how to create applications that can access data in MySQL databases.

In learning to use MySQL, whether to work directly in the MySQL environment or create data-driven applications, an individual should have a thorough understanding of how MySQL, as a relational database management system (RDBMS), allows you to manage data and support applications that rely on access to MySQL data. To this end, this chapter introduces you to MySQL and provides you with an overview of databases, RDBMSs, SQL, and data-driven applications. By the end of the chapter, you will understand the following concepts:

• What a relational database is, how it differs from other types of databases, and how it relates to a database management system.

• The programming language SQL, how to interpret SQL syntax, how to create SQL statements, and how SQL is implemented in MySQL.

• How applications can use a host programming language, a MySQL application programming interface (API), and SQL statements to access information in a MySQL database.

... less

Regardless of how you plan to use MySQL—whether to access data from within a data-driven application or to build databases... more

Regardless of how you plan to use MySQL—whether to access data from within a data-driven application or to build databases that support data-driven applications—there will no doubt come a time when you want to install MySQL yourself. You might choose to do this in order to develop and test your own applications, or you might be the one in your organization responsible for implementing MySQL in a production environment. And it certainly wouldn’t be unheard of if you found yourself having to do both.

Fortunately for everyone, MySQL is a relatively easy application to install, and it can be installed on a number of platforms. For example, you can install MySQL on a computer running FreeBSD or on an Apple computer running Mac OS X. The possibilities are numerous. This chapter, though, focuses only on how to install MySQL on computers running Linux or Windows, although much of what you learn can translate to other operating systems.

It should take you little preparation to install MySQL on Linux or Windows. To do so, you must make several preliminary decisions on exactly what you want to install, and then you must download the necessary files. From there, you can install MySQL on your selected platform, which is explained later in this chapter. Specifically, this chapter covers the following topics:

• What steps you must take before installing MySQL. This includes making preliminary decisions about the MySQL version, platform, distribution type, and edition.

• Step-by-step instructions on how to install MySQL on Linux and Windows. The instructions also include details about how to start the MySQL server in both environments.

• How to test your Linux and Windows installations and verify that the mysql administrative database has been properly initialized.

... less

After installing and initializing MySQL, you can begin working with the server and client tools that are included in that... more

After installing and initializing MySQL, you can begin working with the server and client tools that are included in that installation. Before you begin creating databases and tables, inserting and manipulating data, or modifying server and client configurations, you should have a basic understanding of how to use MySQL. This includes not only finding your way through the MySQL directory structure, but also knowing what server-related and client programs are included in the MySQL installation and what steps you can take to control how those programs run. In addition, you must know how to protect your installation so that only those users that you want to have access to MySQL are permitted access.

To prepare you to start using MySQL, this chapter covers many of the topics that can give you the foundation necessary to work with MySQL. The chapter contains details about the directory structure set up when you install MySQL, the programs available as part of that installation, and the methods used to configure the options available to those programs. In addition, the chapter includes information about how to secure MySQL so that you can prevent access by unauthorized users. Specifically, the chapter covers the following topics:

• The MySQL directory structure that is used in Linux RPM and tar installations and in Windows installations. In addition, you’ll learn about the data directory and the grant tables, as they’re implemented in Linux and in Windows.

• Information about MySQL server-related and client programs. You’ll learn how to specify program options at the command prompt and in a configuration file, and you will be instructed on how to use the mysql client utility.

• How to assign passwords to MySQL user accounts.

... less

Chapter 1 introduced you to databases and databases management systems. As you’ll recall from that discussion, a database... more

Chapter 1 introduced you to databases and databases management systems. As you’ll recall from that discussion, a database is a collection of related data organized and classified in a structured format that is defined by metadata. Not all databases are structured the same, though, as can be attested to by the different data models that have emerged over the years. Yet many of these models—and subsequently the systems that were built on them—lacked the flexibility necessary to support increasingly sophisticated software applications. One data model emerged that addressed the limitations of its predecessors and provided the flexibility necessary to meet the demands of today’s application technologies. This model—the relational model—has become the standard on which most database management systems are now built.

MySQL is one of the database management systems based on the relational model. As a result, to design effective databases, you should have a good understanding of that model and how it applies to database design. To that end, this chapter provides you with a conceptual overview of the relational model and explains the components that make up a relational database. The chapter also discusses how data is organized in the relational model and how tables of data are related to one another. Specifically, the chapter covers the following topics:

• You are introduced to the relational model and the components that make up that model.

• You learn how data in a relational structure is organized according to normal forms, which are prescriptive methods for organizing data in a relational database.

• You are provided with the information necessary to identify the relationships between tables in a relational database, including one-to-one, one-to-many, and many-to-many relationships.

• You learn how to create a data model. The process includes identifying entities, normalizing data, identifying relationships, and refining the data model.

... less

In the first four chapters of the book, you were provided with the information necessary to install and use MySQL and design... more

In the first four chapters of the book, you were provided with the information necessary to install and use MySQL and design relational databases that could be implemented in MySQL. You were also provided with a foundation in the principles of SQL and the relational model. From all this information, you should now have the background you need to begin creating databases and adding the objects that those databases should contain.

The first step in setting up a MySQL database is to create the actual database object, which serves as a container for the tables in that database. The database acts as a central point of administration for the tables in the database. The actual data is stored in the tables, which provide a structured organization for the data and maintain the integrity of that data. Associated with each table is a set of indexes that facilitate access to the data in the tables.

In this chapter, you learn how to create and manage the database object, the tables in the database, and the indexes associated with the tables. To provide you with the information necessary to perform all these tasks, the chapter covers the following topics:

• How to create a database and specify a character set and collation name for that database. You also learn how to modify database properties and remove a database from your system.

• The information necessary to create a table, define columns in that table, add constraints to the table, create indexes on the table, and specify the type of table to be created.

• Information about the various types of indexes that you can add to a table, how to add indexes to a table, and how to remove an index from a table.

• How to retrieve information about databases and tables so that you can see what databases and tables exist and how those databases and tables are configured.

At the heart of every RDBMS is the data stored in that system. The RDBMS is configured and the database is designed for the... more

At the heart of every RDBMS is the data stored in that system. The RDBMS is configured and the database is designed for the sole purpose of managing data storage, access, and integrity. Ultimately, the purpose of any RDBMS is to manage data. For this reason, this chapter and the following five chapters focus exclusively on data access and manipulation. You learn how to add and modify data, retrieve data, and use advanced techniques to carry out these operations.

To start you off in the direction of data management, this chapter introduces you to the SQL statements available in MySQL to manipulate data. The statements provide numerous options for effectively inserting data into a database, updating that data, and deleting it once it is no longer useful. By the end of the chapter, you’ll be able to populate your database tables according to the restrictions placed on those tables, and you’ll be able to access those tables to perform the necessary updates and deletions. Specifically, the chapter covers the following topics:

• How to use INSERT and REPLACE statements to add data to tables in a MySQL database

• How to use UPDATE statements to modify data in tables in a MySQL database

• How to use DELETE and TRUNCATE statements to delete data from tables in a MySQL database

... less

One of the most important functions that a relational database management system (RDBMS) must support is the ability to access... more

One of the most important functions that a relational database management system (RDBMS) must support is the ability to access data in the databases managed by that system. Data access must extend beyond the mere retrieval of information as it is stored in the tables. You must be able to choose which data you want to view and how that data is displayed. To support this functionality, MySQL provides an SQL statement that is both powerful and flexible in its implementation. The SELECT statement is the primary SQL statement used in MySQL—and in most RDBMSs—to retrieve specific data from one or more tables in a relational database.

By using a SELECT statement, you can specify which columns and which rows to retrieve from one or more tables in your MySQL database. You can also link values together across multiple tables, perform calculations on those values, or group values together in meaningful ways in order to provide summarized information. When you execute a SELECT statement, the values returned by that statement are presented in the form of a result set, which is an unnamed temporary table that contains the information retrieved from the tables. In this chapter, you will learn how to create SELECT statements that allow you to retrieve exactly the information that you need. Specifically, the chapter covers the following topics:

• The SELECT statement and its syntax, as used in MySQL. You learn how to create statements that retrieve all columns in a table or only specific columns. You also learn how to add expressions and define variables in your SELECT statements.

• How to add options to a SELECT statement that determine how the statement is executed.

• How to add optional clauses to your SELECT statement that allow you to limit which rows are returned, to group together rows in order to summarize information, and to specify the order in which rows are displayed.

... less

In previous chapters, you have seen a number of expressions used within SQL statements to help define the actions taken by... more

In previous chapters, you have seen a number of expressions used within SQL statements to help define the actions taken by those statements. For example, you can use expressions in the WHERE clauses of SELECT, UPDATE, and DELETE statements to help identify which rows in a table or tables should be acted upon. An expression, as you’ll recall, is a formula made up of column names, literal values, operators, and functions. Together, these components allow you to create expressions that refine your SQL statements to effectively query and modify data within your MySQL database.

In order to allow the components of an expression to work effectively with each other, operators are used to define those interactions and to specify conditions that limit the range of values permitted in a result set. An operator is a symbol or keyword that specifies a specific action or condition between other elements of an expression or between expressions. For example, the addition (+) operator specifies that two elements within an expression should be added together. In this chapter, you learn how to create expressions that use the various operators supported by MySQL. Specifically, this chapter covers the following topics:

• Which components make up MySQL expressions, how operators allow expression elements to interact with each other, how operators are prioritized within an expression, and how to use parentheses to group components of an expression together.

• Which categories of operators MySQL supports, including arithmetic, comparison, logical, bit, and sort operators, and how to use those operators in expressions contained within SQL statements.

... less

In earlier chapters, you learned how to use expressions in your SQL statements to make those statements more robust and specific... more

In earlier chapters, you learned how to use expressions in your SQL statements to make those statements more robust and specific. As you recall, one of the elements that you can use in an expression is a function. Each function performs a specific task and then returns a value that represents the output resulting from the performance of that task. For many functions, you must provide one or more arguments that supply the parameters used by the functions to perform the necessary tasks. These tasks can include calculating numeric data, manipulating string data, returning system data, converting and extracting data, and performing numerous other operations.

In this chapter, you learn about many of the functions included in MySQL. The chapter explains the purpose of each of these functions, describes the results you can expect when a statement includes a function, and provides numerous examples that demonstrate how to use each function. Although this chapter doesn’t describe every function included with MySQL, it covers many of them, focusing on those that you’re most likely to use when creating SQL statements. Specifically, the chapter covers the following types of functions:

• Comparison, control flow, and cast functions that allow you to compare and convert data

• String, numeric, and date/time functions that allow you to manipulate, calculate, convert, extract, and concatenate data

• Aggregate functions that you can use in SELECT statements to summarize data that has been grouped together by a GROUP BY clause

• Encryption, system-related, and query and insert functions that allow you to perform system operations

... less

In earlier chapters, you learned how to use SELECT statements to retrieve data from a database. As you recall, MySQL supports... more

In earlier chapters, you learned how to use SELECT statements to retrieve data from a database. As you recall, MySQL supports a number of options that allow you to create statements that are as precise as you need them to be. You can retrieve specific rows and columns, group and summarize data, or use expressions that include literal values, operators, functions, and column names. In learning about these options, most of the examples that you looked at retrieved data from only one table. MySQL also allows you to retrieve data from multiple tables and then produce one result set, as you would see when retrieving data from a single table. In fact, you can also access multiple tables from within UPDATE and DELETE statements.

MySQL supports several methods that you can use to access multiple tables in a single SQL statement. The first of these is to create a join in the statement that defines the tables to be linked together. Another method that you can use is to embed a subquery in your statement so that you can use the data returned by the subquery in the main SQL statement. In addition, you can create a union that joins together two SELECT statements in order to produce a result set that contains data retrieved by both statements. In this chapter, you learn about all three methods for accessing data in multiple tables. This chapter covers the following topics:

• Using full and outer joins in SELECT, UPDATE, and DELETE statements that link together two or more tables

• Adding subqueries to your SELECT, UPDATE, and DELETE statements that retrieve data that can be used by those statements

• Create unions that join together two SELECT statements

... less

Up to this point in the book, the process of managing data has been confined to the manipulation of data in your database... more

Up to this point in the book, the process of managing data has been confined to the manipulation of data in your database. For example, to add data to your tables, you manually created INSERT statements that targeted specific tables. To view data, you manually created SELECT statements that retrieved data from specific tables. In each case, the data was added to the tables by specifying those values to be inserted, or the data was retrieved by executing the applicable SELECT statement each time you wanted to view that data. At no time was data copied to or from files outside the database, nor was data copied between tables in the database.

The limitations of these approaches become apparent when you want to add large quantities of data to a database or manage large quantities of data outside the database. MySQL supports a number of SQL statements and commands that allow you to export data into files outside the database, copy data between tables in a database, and import data into the database. By using these statements and commands, you can easily work with large amounts of data that must be added to and retrieved from a database or data that must be copied from one table to the next. This chapter discusses how to use these statements and commands and provides examples of each. Specifically, the chapter explains how to perform the following tasks:

• Create SELECT statements that export data to out files and dump files

• Create INSERT and REPLACE statements that copy data into existing tables and create CREATE TABLE statements that copy data into new tables

• Create LOAD DATA statements and execute mysql and mysqlimport commands that import data into existing tables

As you have worked your way through this book, you have executed a number of SQL statements that have performed various actions... more

As you have worked your way through this book, you have executed a number of SQL statements that have performed various actions in your MySQL database. The statements were executed interactively in an isolated environment in which you ran one statement at a time, without competing for data access with other users or connections. As a result, you continuously maintained control over the database, the tables in the database, and the data in those tables.

In the real world, databases seldom exist in an isolated state, nor is access limited to one user working interactively with the database. In fact, numerous applications and users attempting to access and manipulate data at the same time often share databases. As a result, MySQL supports the use of transactions to ensure safe data access by multiple users. In this chapter, you learn how to use transactions to manage the execution of SQL statements. Specifically, this chapter covers the following topics:

• Understanding what a transaction is and the characteristics of a transaction

• Performing basic transactions and adding savepoints to your transactions

• Setting the autocommit mode for your session and setting the transaction isolation levels

• Locking and unlocking nontransactional tables

... less

In previous chapters, you learned how to install MySQL on Linux and Windows operating systems, maneuver through the MySQL... more

In previous chapters, you learned how to install MySQL on Linux and Windows operating systems, maneuver through the MySQL installation, implement relational databases in the MySQL environment, and execute SQL statements that insert, update, delete, and retrieve data from those databases. In this chapter and the next several chapters, you learn how to perform administrative tasks that allow you to determine how MySQL runs, who can access the MySQL server, and how to replicate your system and prepare for database disaster.

This chapter introduces you to MySQL administration and describes how to modify default MySQL settings, set system variables, and implement logging. Chapter 14 explains how to manage security, and Chapter 15 describes how to optimize performance. In Chapter 16, you learn how to back up and restore your MySQL databases as well as set up replication. This chapter, then, provides the starting point from which you begin to perform basic administrative tasks. Specifically, the chapter covers the following topics:

• Using the mysqladmin client utility to verify system settings and perform server-related operations

• Using the SHOW VARIABLES, SELECT, and SHOW STATUS statements to retrieve system variable settings and using start-up options, option files, and the SET statement to set system variables

• Managing error, query, and binary logging

... less

An important component of administering any database is ensuring that only those users that you want to be able to access... more

An important component of administering any database is ensuring that only those users that you want to be able to access the database can do so, while preventing access by all other users. Not only should you be able to control who can log on to the MySQL server, but you should be able to determine what actions authenticated users can take once they connect to the server. All RDBMS products support some level of security in order to protect the data stored in their systems’ databases—and MySQL is no exception.

When a user logs on to a MySQL server, MySQL permits the user to perform only approved operations. MySQL security is managed through a set of tables and privileges that determine who can establish a connection to the MySQL server, from what host that connection can be established, and what actions the user (from the specified host) can take. In this chapter, you learn how this system is set up and how you can add user accounts or remove them from the tables. You also learn how to permit users to perform certain actions, while preventing them from taking other actions. To facilitate your ability to configure MySQL security, this chapter provides information about the following topics:

• The MySQL grant tables, including the user, db, host, tables_priv, and columns_priv tables

• The process used to authenticate connections to the MySQL server and to verify the privileges necessary to perform various operations

• The statements necessary to manage MySQL user accounts, including the GRANT, SHOW GRANTS, SET PASSWORD, FLUSH PRIVILEGES, REVOKE, and DROP USER statements

... less

In many examples throughout this book, you have seen SQL statements executed against small tables that contain relatively... more

In many examples throughout this book, you have seen SQL statements executed against small tables that contain relatively few rows. As a result, the performance of these statements has not been an issue because it takes relatively little time for MySQL to return information or modify data. This is often not the case, however, in the real world. If you’re accessing tables that contain thousands of rows of data (or more), you might find that certain SQL statements are slow and take a relatively long time to be processed, despite how efficiently you think that statement should run. As a result, whenever you’re setting up a database or creating SQL statements to execute against the database, you should take into consideration how well those statements perform when they are executed.

When you begin working with tables that contain large quantities of data, there are several steps that you can take to optimize the performance of your SQL statements. By optimizing performance, you’re maximizing the speed and efficiency at which those statements are executed. For example, in order to ensure that your SELECT statements retrieve data as quickly as possible, you can ensure that your tables have been properly indexed. In this chapter, you learn about various steps that you can take to optimize your system’s performance. Specifically, the chapter covers the following topics:

• Advantages and disadvantages of indexing and when you should use indexing

• Determining how effectively your queries are being executed and steps you can take to improve your data-related operations

• Modifying table definitions to improve query performance

• Enabling your system’s query cache

... less

Despite the steps you take to secure your databases or optimize the performance of SQL statements issued against those databases... more

Despite the steps you take to secure your databases or optimize the performance of SQL statements issued against those databases, disasters can occur that cause the loss or corruption of data. As a result, one of the most important steps that you can take to protect your data is to make certain that you maintain copies of your databases. One method that you can use to copy your databases is to create backup files that contain the database and table definitions necessary to re-create your database structure as well as the statements and data necessary to repopulate your tables after they’ve been created. Once you create backup files, you can immediately re-create your database environment if the need arises, returning your database to the state it was in at the time you performed the last backup. You can then use the binary log files to update your database to a current state.

In addition to performing regular backups of your databases, you can also replicate your databases so that you always have at least one up-to-date copy of each one. Replicating a database means that you maintain a copy of the database that is kept synchronized with the original database. If disaster should occur on the original database, you can then use the replicated database to provide services to applications and users. In addition, replication is also useful in facilitating the backup process and in load balancing queries. This chapter describes how to back up your databases, restore the databases from the backup, and replicate your databases to another server. Specifically, the chapter covers the following topics:

• Using the mysqldump client utility to back up tables in a single database and back up multiple databases

• Using the mysql client utility in batch mode and interactive mode to reload databases from backup files and then using binary log files to update the databases after they’ve been reloaded

• Setting up replication on master and slave servers and then managing the replication process

... less

Throughout the book, you have learned about many different aspects of MySQL, including how to install MySQL, create databases... more

Throughout the book, you have learned about many different aspects of MySQL, including how to install MySQL, create databases and tables, and retrieve and manipulate data in those tables. You have even learned how to perform administrative tasks such as granting privileges to user accounts and optimizing your queries. As you learned to perform these tasks, you often worked with MySQL interactively by issuing SQL statements and commands from within the mysql client utility. The majority of access to MySQL databases is through applications that use application programming interfaces (APIs) to connect to MySQL and issue SQL statements. As a result, the applications—along with the APIs—are the primary vehicles available to users to interact with MySQL data. At the very least, the applications allow users to view data that is retrieved from the database, but in many cases, they are also able to add to, update, or delete that data.

Because of the importance that applications play in accessing data in a MySQL database, the final three chapters of the book focus on connecting to a MySQL database from an application and then accessing data in that database. This chapter explains how to work with a MySQL database from a PHP application. Chapter 18 focuses on Java applications, and Chapter 19 focuses on ASP.NET applications. In each chapter, you learn how to create a database connection, retrieve data, and then modify the data, all in that particular application language. The book begins with PHP because it is one of the most common application languages used to connect to MySQL. The PHP/MySQL application has been implemented worldwide for systems that vary greatly in size and number of users. This chapter, then, provides you with the information you need to allow your PHP application to communicate with MySQL. Specifically, the chapter does the following:

• Introduces you to PHP and how it communicates with a MySQL server and its databases

• Explains how to build a PHP application that connects to a MySQL database, retrieves data from that database, inserts data in the database, modifies that data, and then deletes the data

... less

Most users access data in a MySQL database by using an application that interfaces with that database. In many cases, the... more

Most users access data in a MySQL database by using an application that interfaces with that database. In many cases, the application is built with Web pages that reside on a application server or Web server such as Apache or Internet Information Services. Chapter 17 provides an example of a PHP application that access data in the DVDRentals database. However, Web-based applications are by no means limited to PHP. Another popular programming language that you can use to build Web-based applications is Java. Java supports the same functionality as PHP, and much more. In fact, Java can also be used to build client-server and multi-tiered systems that are not limited to Web-based applications—and all those systems can be built to access a MySQL database.

In this chapter, you learn how to build a Java Web-based application that accesses a MySQL database. The application is based on the Java 2 Enterprise Edition (J2EE) specification, which defines a collection of libraries that contain classes for server-side tasks, including support for the JavaServer Pages (JSP) technology. By using Java and JSP technology, you can create Web pages that deliver dynamic content to your users, similar to the way the PHP delivers content to users. In fact, if you’re familiar with PHP, you’ll find that creating JSP files is quite similar to creating PHP files. To demonstrate how to create a JSP application that connects to a MySQL database, this chapter covers the following topics:

• Introduces you to Java and how it communicates with a MySQL server and its databases

• Explains how to build a JSP application that connects to a MySQL database, retrieves data from that database, inserts data into the database, modifies the data, and then deletes the data

... less

MySQL allows users to access its databases from a variety of applications. If you reviewed chapters 17 or 18, you’ve seen... more

MySQL allows users to access its databases from a variety of applications. If you reviewed chapters 17 or 18, you’ve seen how you can connect to MySQL from PHP and Java. In both cases, you can implement these types of applications from within any Web or application environment that supports the particular language. However, MySQL provides access from another type of application, the type that is implemented within the context of the Microsoft .NET Framework. The .NET Framework is an application development and implementation environment that supports a wide range of technologies. The framework is made up primarily of a library of code that can be utilized by a variety of application languages, such as C#, Visual Basic .NET, and JScript .NET. The .NET Framework also defines an extensive system of data types that facilitate the interoperability of languages that use the framework. The framework also provides the Common Language Runtime (CLR), which maintains the execution of applications developed through the .NET library.

An important component of the .NET Framework is ASP.NET, which allows you to create dynamic Web pages similar to what you’ll find with Java and JSP. However, because ASP.NET is part of the .NET Framework, you can utilize the .NET library when developing Web-based applications. In addition, you can use any of the languages supported by .NET to create your application. In this chapter, you learn how to create an ASP.NET application based on C#. The reason that C# has been chosen is because it is the most powerful language supported by the .NET Framework and the only language that was developed with .NET in mind. (A language such as Visual Basic .NET was updated with .NET in mind, but not created for .NET.) By using C# and ASP.NET to develop your Web pages, you can create robust, powerful applications that can include a rich assortment of features and functionality. As a way to introduce you to ASP.NET and C#, and how you can access a MySQL database from within your application, this chapter covers the following topics:

• Introduces you to ASP.NET and C# and how they communicate with a MySQL server and its databases

• Explains how to build an ASP.NET/C# application that connects to a MySQL database, retrieves data from that database, inserts data into the database, modifies the data, and then deletes the data

... less

This appendix contains sample answers to the exercises at the ends of chapters.... more

This appendix contains sample answers to the exercises at the ends of chapters.

... less

To facilitate your ability to connect to the MySQL server from various types of applications, MySQL supports a number of... more

To facilitate your ability to connect to the MySQL server from various types of applications, MySQL supports a number of application programming interfaces (APIs) that allow client programs to connect to and interface with MySQL databases. As a result, you can create an application in any of the supported programming languages (as indicated by the list of APIs that follows) and use the applicable API to access a MySQL database from the application. The API acts as a bridge between the application language and the database, allowing you to establish a connection, execute SQL statements, and retrieve data that can then be displayed in your application.

If you created one of the applications in Chapter 17, 18, or 19, you already saw how an API is used to facilitate database connectivity. For example, the PHP application uses the PHP API that is included with the PHP preprocessor. On the other hand, the JSP/Java application uses Connector/J to connect to MySQL, and the ASP.NET/C# application uses Connector/ODBC, both of which are MySQL APIs that provide database connectivity. Currently, MySQL supports the following APIs:

... less

Throughout the book, you’ve seen examples and exercises that have all been based on MySQL version 4.1. For the most part,... more

Throughout the book, you’ve seen examples and exercises that have all been based on MySQL version 4.1. For the most part, what you’ve seen is consistent with the 4.1.7 release of MySQL. If you visit the MySQL Web site (www.mysql.com), you’ll find that MySQL AB is actively developing future versions of MySQL. What you’ll discover is that plenty of new functionality is planned for version 5.0, 5.1, and beyond. In addition, existing functionality will be enhanced in various ways. (Each release of MySQL also includes numerous bug fixes.)

Although the MySQL functionality, as it has been presented in the examples and exercises, will be implemented in much the same way as it is described in this book, there will also be some changes. The following list highlights many of the new features that MySQL AB plans to add to its database management system:

• Extend the VARCHAR data type to support more than 255 characters.

• Add foreign key support for all table types, not just InnoDB tables.

• Extend replication to support online backups in order to add a new replication slave without shutting down the master server.

• Implement the RENAME DATABASE statement to allow authorized users to rename a database.

• Add the necessary data types so that MySQL supports all standard SQL and ODBC 3.0 types.

• Enhance the SHOW COLUMNS statement so that it is faster and requires less memory.

• Do not automatically add default values to columns that have not been explicitly defined with the DEFAULT option.

• Add a log file analyzer utility that can provide information such as which tables receive the most hits and how often joins are executed.

• Update the DATETIME data type so that it supports fractions of a second.

• Implement a date/time data type that support time zones in order to make working with dates in different time zones easier.

• Add functionality to support stored procedures, triggers, and views.

By no means does this information represent a complete list of all the new features that will be added to MySQL in future releases, but it does include some of the more important ones. Of these, the features listed in the last bullet (stored procedures, triggers, and views) are ones that you might find of particular interest because most database management systems already implement this functionality and they are part of the SQL standard. In addition, they can play a pivotal role in developing any data-driven application. The remaining part of the appendix looks at each one of these three features individually.

... less