You may have noticed that you have read so many pages of this book, but you have not seen a few comments and terminologies. Although we mentioned how to use the sample database, we haven't introduced anything about the database. However, we are planning to design the database and then implement it, so that we can no longer avoid talking about the database.
You may have noticed that you have read so many pages of this book, but you have not seen a few comments and terminologies. Although we mentioned how to use the sample database, we haven't introduced anything about the database. However, we are planning to design the database and then implement it, so that we can no longer avoid talking about the database.
You may have noticed that you have read so many pages of this book, but you have not seen a few comments and terminologies. Although we mentioned how to use the sample database, we haven't introduced anything about the database. However, we are now planning to design the database and then start to implement it, so that we can no longer avoid talking about database terms. Introducing database terms is the purpose of this section. Some terms described in this section are used, so you must be familiar with them. Fortunately, many concepts in relational databases are quite simple. In fact, the attractiveness of relational databases mainly comes from the simplicity of their basic concepts.
1.3.1 basic terms
In the database World, MySQL is classified as a relational database management system (RDBMS ). The meaning of the so-called relational database management system is as follows:
■ A database ("DB" in RDBMS) is a warehouse for storing information and is organized in a simple and regular manner:
■ Datasets in the database are organized into tables.
■ Each table consists of rows and columns.
■ Each behavior in the table is a record.
■ Records can contain several segments of information. Each column in the table corresponds to a segment of the information.
■ The Management System ("MS") is a software that allows you to use data by inserting, retrieving, modifying, or deleting records.
■ The Word "relationship" ("R") represents a special type of db ms, it searches for common elements to associate the information stored in one table with the information stored in another table. The capability of relational database is that it can easily retrieve data from these tables and combine the information in each table to obtain the answer to the question, these answers are not possible only based on the information of a single table. The following example shows how a relational database organizes data into a table and associates the information in one table with the information in another table. Assume that you manage a Web site containing the title advertisement service. You have an agreement with the company, and these companies want people to display their ads when visiting webpages on your site. Every time a visitor clicks on your page, you provide an advertisement service embedded in the page to the visitor's browser, and estimate a fee for the company.
. To indicate this information, you need to save three tables (see-1 ). One is the company table, which contains the company name, number, address, telephone number, and other columns. The other is the ad table, which lists the ad number, the company number that owns the ad, and the billing number for each click. The third hit table records the number of ad clicks and the date on which the ad provides services by ad number.
You can use the information of a single table to answer certain questions. To determine the number of companies that sign the agreement, you only need to count the number of rows in the company table. Similarly, to determine the number of clicks in a given period of time, you only need to view the hit table. Other questions are more complex, and multiple tables must be considered to determine the answer. For example, to determine how many times each advertisement of Pickles was clicked in July 14, use these tables as follows:
1) query the company name (Pickles, Inc) in the company table to find the company number (14 ).
2) use company numbers to find matching records in the ad table so that the relevant ad numbers can be determined. There are two such advertisements: 48 and 10.
3) For each matching record in the ad table, use the ad number in the record to find matching records in the required date range in the hit table, and then count matching records. There are three matching records with the ad number 48 and two matching records with the ad number 101. It sounds complicated! This is exactly what relational database systems are good. To some extent, this complexity is an illusion, because each of the above steps is just a simple matching operation, it matches the values in the rows of a table with the values in the rows of another table to associate one table with the other. This simple operation can be used in various ways to answer various questions. How many different advertisements does a company have? Which company is the most popular advertisement? What is the revenue of each advertisement? What is the total cost of each company in the current billing cycle? Now we have introduced the theory of relational databases, which is sufficient to understand the rest of this book. We do not have to explore the third paradigm, the entity relationship diagram, and all such things. If you really need to know about these things, it's terrible and it's not a place. We recommend that you read some books in C. J. Date and E. F. Codd.
498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 359 src = "/files/uploadimg/20050812/1716380 .JPG" width = 560> 498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 417 src = "/files/uploadimg/20050812/1716381 .JPG" width = 461>
1.3.2 query language terminology
MySQL uses a Language called Structured Query Language. SQL is today's standard database language, which is used by all major database systems. SQL has a variety of different statements. All statements are designed to interact with the database in a non-boring and useful way. As in other languages, SQL may be a bit odd in first-time contact. For example, to create a table, you must tell the MySQL table structure. We may imagine a table based on the chart, but MySQL does not. Therefore, we need to tell MySQL something when creating a table, as shown below:
498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 135 src = "/files/uploadimg/20050812/1716382 .JPG" width = 226>
If you are not familiar with SQL statements, you may be deeply impressed with such statements, but you do not have to learn how to use SQL effectively as a programmer. If you gradually get familiar with the SQL language, you will look at the CREATE TABLE statement from a different perspective and think it is a partner that can help describe your information, it's not a strange nonsense.
1.3.3 architecture terminology of MySQL
When you use MySQL, you are actually using the following two programs, because MySQL uses the Client/Server Architecture:
■ The Database Server is a program located on the machine where your data is stored. It listens to client requests uploaded from the network and accesses the database content based on these requests, so as to provide the client with the information they require.
■ The client is a program connected to the database server. These programs tell the server what information to query. The MySQL distribution package includes servers and several client programs. The client can be used for the purpose to be achieved. The most common client program is mysql, which is an interactive client program that can publish queries and see the results. Others
The client programs include mysqldump and mysqlimport, which are used to dump the table content to a file or import the file content to a table. mysqladmin is used to view the server status and complete management tasks, for example, tell the server to shut down. If a standard client is not suitable for an application, MySQL also provides a client programming library that allows you to write your own program. The client programming library can be called directly from the C program. If you want to use a language other than C, there are several other interfaces available.
The MySQL client/server architecture has the following benefits:
■ The server provides concurrency control so that two users cannot modify the same records at the same time. All client requests are processed by the server. The server classifies and identifies who is going to do what and when to do it. If multiple clients want to access the same table at the same time, they do not have to decide and negotiate with each other, as long as they send their own requests to the server and let it carefully determine the order in which these requests are completed.
■ You do not have to register on the machine where the database is located. MySQL knows how to work on the Internet, so you can run a client program anywhere, which can be connected to a server on the network. Distance is not a problem. You can access the server from anywhere in the world. If the server is located on a machine in Australia, you can still access your database when traveling to Iceland with your laptop.
Does this mean that anyone can access your data as long as they connect to the Internet? The answer is no. MySQL contains a flexible security system that allows only those with permissions to access data. It can ensure that those people can only do what they are allowed to do. Perhaps Sally in the accounting office can read and update (modify) records, while Phil at the service desk can only view records. You can set the permissions of users. If you want to run a self-contained system (independent system), you only need to set access permissions so that the client can only connect from the host running on the server.
