Materi Kuliah MySQL
Bab 1 Introduction
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.
PDF Preview | Download
Bab 2 Install
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.
PDF Preview | Download
Bab 3 Working With MySQL
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.
PDF Preview | Download
Bab 4 Design Relational Database
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.
PDF Preview | Download
Bab 5 Mengelola Database
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.
PDF Preview | Download
Bab 1 Introduction
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.
PDF Preview | Download
Bab 2 Install
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.
PDF Preview | Download
Bab 3 Working With MySQL
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.
PDF Preview | Download
Bab 4 Design Relational Database
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.
PDF Preview | Download
Bab 5 Mengelola Database
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.
PDF Preview | Download
Bab 6 Manipulasi Data MySQL
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.
PDF Preview | Download
Bab 7 Mengambil Seleksi Data
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.
PDF Preview | Download
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.
PDF Preview | Download
Bab 7 Mengambil Seleksi Data
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.
PDF Preview | Download
Bab 8 Menggunakan Operator
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.
PDF Preview | Download
Bab 9 Fungsi-Fungsi Dalam 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.
PDF Preview | Download
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.
PDF Preview | Download
Bab 9 Fungsi-Fungsi Dalam 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.
PDF Preview | Download
Bab 10 Mengakses Banyak Tabel
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.
PDF Preview | Download
Bab 11 Ekspor dan Impor Data
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.
PDF Preview | Download
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.
PDF Preview | Download
Bab 11 Ekspor dan Impor Data
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.
PDF Preview | Download
Bab 13 Administering MySQL
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.
PDF Preview | Download
Bab 14 MySQL Security
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.
PDF Preview | Download
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.
PDF Preview | Download
Bab 14 MySQL Security
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.
PDF Preview | Download
Bab 15 Optimizing Performance
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
PDF Preview | Download
Bab 16 Backup, Replikasi, Recovery
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.
PDF Preview | Download
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
PDF Preview | Download
Bab 16 Backup, Replikasi, Recovery
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.
PDF Preview | Download
Bab 17 Koneksi Ke PHP
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.
PDF Preview | Download
Bab 18 Koneksi Ke Java
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.
PDF Preview | Download
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.
PDF Preview | Download
Bab 18 Koneksi Ke Java
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.
PDF Preview | Download
Bab 19 Koneksi Ke ASP.NET & C#
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.
PDF Preview | Download
Bab 20 Jawaban Soal-Soal
PDF Preview | Download
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.
PDF Preview | Download
Bab 20 Jawaban Soal-Soal
PDF Preview | Download
Materi Kuliah MySQL
Reviewed by MCH
on
December 07, 2016
Rating:
Reviewed by MCH
on
December 07, 2016
Rating:
No comments: