Ebook MySQL
Databases and database management systems have become the backbone of most Web-related applications as well as an assortment of other types of applications and systems that rely on data stores to support dynamic information needs. Without the availability of flexible, scalable data sources, many organizations would come to a standstill, their ability to provide services, sell goods, rent movies, process orders, issue forms, lend books, plan events, admit patients, and book reservations undermined by the inability to access the data essential to conducting business. As a result, few lives are unaffected by the use of databases in one form or another, and their ubiquitous application in your everyday existence can only be expected to grow.
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.
When you install MySQL, a directory structure is set up to support the various database-related functions. The directories contain the files necessary to initialize the database, start the MySQL server, and set up the server to run automatically. In addition, the directories include the server-related and client programs included with MySQL, as well as script, log, and document files related to the MySQL operation. This section discusses the details of the directory structure and then focuses specifically on the data directory, which houses the actual database files.
The relational versi first entered the database scene in 1970, when Dr. E. F. Codd published his seminal work, “A Relational Model of Data for Large Shared Data Banks” in the journal Communication of the ACM, Volume 13, Number 6 (June 1970). In this paper, Codd introduced a relational data structure that was based on the mathematical principles of set theory and predicate logic. The data structure allowed data to be manipulated in a manner that was predictable and resistant to error. To this end, the relational model would enforce data accuracy and consistency, support easy data manipulation and retrieval, and provide a structure independent of the applications accessing the data.
At the heart of the relational model — and of any relational database — is the table, which is made up of a set of related data organized in a column/row structure, similar to what you might see in a spreadsheet program such as Microsoft Excel. The data represents the physical implementation of some type of object that is made up of a related set of data, such entities as people, places, things, or even processes. For example, the table in Figure 4-1 contains data about authors. Notice that all the values in the table are in some way related to the central theme of authors.
Once you install MySQL and are comfortable using the tools in MySQL — particularly the mysql client utility — you can begin creating databases in the MySQL environment. Recall from Chapter 4 that when you add a database to MySQL, a corresponding subdirectory is automatically added to the data directory. Any tables added to the database appear as files in the subdirectory. If you then remove a database, the corresponding subdirectory and files are also deleted.
As you can see, the first step in setting up a database in MySQL is to create the database object and, subsequently, the corresponding subdirectories. From there, you can modify the database definition or delete the database. This section discusses each of these tasks.
Before you can do anything with the data in a database, the data must exist. For this reason, the first SQL statements that you need to learn are those that insert data in a MySQL database. When you add data to a database, you’re actually adding it to the perorangan tables in that database.
Because of this, you must remain aware of how tables relate to each other and whether foreign keys have been defined on any of their columns. For example, in a default configuration of a foreign key, you cannot add a row to a child table if the referencing column of the inserted row contains a value that does not exist in the referenced column of the parent table. If you try to do so, you receive an error. (For more information about foreign keys, refer to Chapter 5.)
Whenever you want to retrieve data from a MySQL database, you can issue a SELECT statement that specifies what data you want to have returned and in what manner that data should be returned. For example, you can specify that only specific columns or rows be returned. You can also order the rows based on the values in one or more columns. In addition, you can group together rows based on repeated values in a column in order to summarize data.
The SELECT statement is one of the most powerful SQL statements in MySQL. It provides a great deal of flexibility and allows you to create queries that are as simple or as complex as you need to make them. The syntax for a SELECT statement is made up of a number of clauses and other elements, most of which are optional, that allow you to refine your query so that it returns only the information that you’re looking for.
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.
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.
In a normalized database, groups of data are stored in individual tables, and relationships are established between those tables to link related data. As a result, often when creating SELECT, UPDATE, or DELETE statements, you want to be able to access data in different tables to carry out an operation affected by those relationships.
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.
Once you install MySQL, you might find that you want to view information about the MySQL server, such as status or version information. You might also find that you want to perform administrative tasks such as stopping the server or flushing the host’s cache. To allow you to perform these tasks, the MySQL installation includes the mysqladmin client utility, which provides an administrative interface to the MySQL server. Using this tool, you can perform a variety of administrative tasks, such as obtaining information about the MySQL configuration, setting passwords, starting the server, creating and dropping databases, and reloading privileges.
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.
In MySQL, the most useful step that you can take to maximize performance is to ensure that your tables are properly indexed. Indexes provide an effective way to access data in your tables and speed up searches. An index provides an organized list of pointers to the actual data. As a result, when MySQL executes a query, it can scan the index to locate the correct data, rather than having to scan the entire table.
Ensuring that your MySQL databases are backed up regularly should be part of any maintenance routine. Despite your best efforts to protect your databases, events such as power failures, natural disasters, and equipment failure can lead to the corruption and loss of data. Consequently, you should ensure that your databases have been safely copied to safe and reliable systems.
The primary method that MySQL provides for backing up all types of tables in your database is the mysqldump client utility. The utility allows you to back up individual databases, tables in those databases, or multiple databases. When you run mysqldump, the utility creates a text file that contains the SQL statements necessary to create your database and tables safely and add data to those tables. This file is referred to as a backup file or dump file.
The way in which you use the utility and the type of information contained in the file depends on the type of backup you’re performing (whether you’re backing up individual databases, perorangan tables, or multiple databases). This section describes how you use the mysqldump client utility to perform the various types of backups.
PHP is a server-side scripting language that is used in Web-based applications. PHP also refers to the preprocessor that is installed on your system when you install PHP. (PHP stands for PHP: Hypertext Preprocessor, which makes PHP a recursive acronym.) The PHP scripts are embedded in the Web pages, along with HTML, to create a robust Web application that can generate dynamic content that is displayed to the user. PHP is similar in some ways to other application languages. For example, PHP, Java, and C# (one of the languages used in ASP.NET applications) are all considered object-oriented procedural languages and are derived from a similar source — the C and C++ programming languages. PHP is used primarily for Web-based applications, whereas Java and C# are used not only to build Web-based applications but also to build larger PC-based and client/server applications that require a more rigid structure.
Java is a robust application language based on the principles of object-oriented programming (OOP). As the name indicates, OOP refers to a programming model that is based on the concept of encapsulating code and the data that it manipulates into defined objects. Each object is based on an object class that specifies how that object can be built. An object, then, is an instance of the class from which it is derived.
ASP.NET is a server-side technology that allows you to provide dynamic Web content to your users. Working in conjunction with the .NET Framework, ASP.NET is a component that is attached to your Web server, allowing you to display ASP.NET Web pages on any browser just like basic HTML pages. To build an ASP.NET application, you can use a full-fledged programming language such as C#. A programming language such as C# is far more powerful and extensive than a server-side scripting languages such as PHP and VBScript. As a result, you can build applications that extend far beyond the capabilities of those built with simple server-side scripting languages.