Chapter2. anatomy database system (3) _ MySQL

Chapter2. anatomy database system (3) bitsCN.com

Link

Is MySQL a relational database system? Many database experts will tell you that there are only a few real relational database systems in the world. They will also point out to what extent the features you need can be supported by the database system. They won't tell you how many relational models the database has come up with Codd. From a pure market perspective, MySQL provides many functions that should be supported by relational databases. These include establishing a relationship between a table and a table through a foreign key, a query mechanism based on a relational algorithm, and using an index and Cache mechanism in the database. Obviously, MySQL provides more functions than that. Is MySQL a relational database? Well, it depends on your definition of the relationship. From the perspective of Application Evolution, MySQL is indeed a well-known relational database. However, if you strictly follow the Codd relational model for comparison, you will find that MySQL does not fully implement all the functions in the model proposed by Codd. In addition, some other relational databases have not fully implemented the relational model proposed by Codd.

Relational database system architecture

　　A relational database is a complex system consisting of many components that are specially designed to handle all the features required for storage and retrieval. The architecture of a relational database system is often compared with that of an operating system. When you think about the use of relational databases, especially when providing services to clients as a server, you will find many similarities with common operating systems. For example, in the case of multiple clients, the system must support multi-request processing. these requests may cause the system to simultaneously read and write the same piece of data, you may also perform read/write operations on the data in the same region (that is, the table) at the same time. Therefore, relational databases must support concurrency to operate efficiently. Similarly, it also needs to support high-speed data access from each client. This requires the rational use of the file cache technology to ensure that recent or frequently used data can reside in the memory for efficient access. The concurrency technology also needs to be similar to the virtual memory mechanism in the operating system to manage the memory in the database. Other similar aspects include providing network connection support like the operating system and optimization algorithms for improving query statement execution efficiency.

We will start exploring our database architecture from the perspective of using query statements to retrieve data. In the following sections, you can select the sections you are interested in. if you are familiar with the technology involved in a section, skip this section. However, I still hope you can take a full look at all of them. these sections provide details on how a typical relational database is structured.

　　Client application

Most client applications of relational databases are independent programs that communicate with databases in some ways (such as socket-based network connections or pipelines. In some cases, the database is accessed directly in the program. in this case, the database is part of this client application. In this case, we call it an embedded database. Chapter 6 introduces more information about embedded databases.

For applications that exchange information with the database through some channels, most of the links are established on the protocols provided by the database connector. Most DataBase connectors are based on Open DataBase Connectivity (ODBC) models. MySQL also supports database interconnection based on java and. NET. Most database connectors based on the open database interconnection model also support connection to databases through network protocols.

What is ODBC? ODBC is an application interface (API). ODBC is designed to parse SQL statements so that the server can retrieve data and present the data returned by the server to the client. An ODBC implementation includes the use of APIs to call ODBC library functions to act as the intermediary, and a database driver designed for a specific database. We can literally regard ODBC as a connector that provides user access, application interfaces, and database drivers. Therefore, an ODBC Connector acts as the interpreter between the server and the client. ODBC has almost become the standard for all relational databases, and there are also various connectors that support different types of clients and different types of servers.

When we talk about client applications, we generally assume that it is only a program that retrieves data from the server or enables the server to store data. However, the program we use to set/maintain the server is also a client program. Most of these programs use the same communication channel as database applications to connect to the server. Some use ODBC connector, and some use its JAVA-based variant (JDBC) to connect to the database. Some of the special protocols used to manage servers are designed based on some special management requirements. For example, phpMyAdmin uses a port or socket.

No matter how they implement it, the client application sends commands to the server to retrieve the data pointed to by these commands, and then returns the data to the user after translating and processing the data. The standard command statement is SQL. The client uses SQL commands, connector translation, and database-driven network connections maintained by the connector to communicate to the database and obtain data from the database. -1 describes this process.

　　

　　Query interface

A query language, such as SQL, is a language that can be used to submit requests to a database (because it has its own syntax and semantics ). In fact, using SQL in a database is considered to be one of the main reasons for database success. The query language provides a set of statements to facilitate the use of databases. Data Definition Language (DDL) is used to create and manage databases. Its tasks include changing the table structure, defining indexes, and managing constraints. Data Manipulation Language (DML) can be used to query and update Data in a database. Its tasks include adding, querying, and modifying Data. These two languages constitute the main commands supported by the database.

SQL commands use special syntax. The following shows the SELECT statement syntax in SQL.

SELECT [DISTINCT] listofcolumnsFROM listoftables[WHERE expression (predicates in CNF)][GROUP BY listofcolumns][HAVING expression][ORDER BY listof columns];

The syntax of this command is as follows:

1. in the FROM statement, the Cartesian product of the table is formed to form the references required in other statements. (Form the Cartesian product of the tables in the FROM clause, thus forming a projection of only those references that appear in other clses .) 2. if the WHERE statement exists, use these expressions to provide a reference table. (If a WHERE clause exists, apply all expressions for the given tables referenced.) 3. If the group by statement exists, GROUP the query results based on the attributes provided BY the group by statement. 4. if The HAVING statement exists, filter the data. 5. if the order by statement exists, sort the query results. 6. if the DISTINCT statement exists, the same element will be removed from the query results.

The code example above shows the elements of most SQL commands. all such commands have their necessary parts and some optional parts based on keywords.

Once these commands are transferred to the server, the database server immediately begins to parse and execute these commands. From this point of view, a query statement is simply called a query because it submits the problem description to the server and then the server returns an answer based on the descriptions. In the following section, we assume that "query" is similar to a SELECT statement and data is returned. In fact, any query, whether it is data operations or data definition, is executed in the same way as the preceding process. From this point, we can think that the server executes these processes on its own. The first step of the process is to parse what the client needs. that is to say, the query statement needs to analyze these statements in syntax and divide the statements into a small part that can be executed independently.

(To be continued)

Author: casatwy Source: http://www.cnblogs.com/casatwy welcome reprint, Please also keep this statement. Thank you!

BitsCN.com