Shulou(Shulou.com)05/31 Report--

This article shares with you the content of a sample analysis of the MySQL database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Summary of the database

Database (Database) is a software system for storing and managing data, just like a logistics warehouse for storing data.

In the business field, information means business opportunities, and a very important way to obtain information is to analyze and process the data, which gives birth to a variety of professional data management software, database is one of them. Of course, the database management system is not established at once, it is also through continuous enrichment and development, it has become what it is today.

1.1. History of development

1.1.1. Manual processing stage

In the early days of the computer before the mid-1950s, its processing capacity was very limited, it could only complete some simple operations, and the data processing capacity was also very limited, so that the computer at that time could only be used in scientific and engineering calculations. There is no dedicated software for managing data on the computer, and the data is carried by the computer or the program that processes it. When the data storage format, read and write path or method changes, the processing program must also make corresponding changes to maintain the correctness of the program.

1.1.2, file system

From the late 1950s to the mid-1960s, with the development of hardware and software technology, computers were used not only in scientific computing, but also in business management. During this period, the data and programs have been completely separated in the storage location, and the data is organized separately into files and saved on external storage devices, so that the data files can be used by multiple different programs at different times.

Although the program and data are separated in the storage location, and the operating system can also help us to manage the storage location and access path of the completed data, the program design is still affected by the data storage format and method. can not be completely independent of the data, and the redundancy of the data is large.

1.1.3, database management system

Since the 1970s, computer software and hardware technology has developed by leaps and bounds, and the most important development in this period is the emergence of a real database management system. It makes the real interface unification and data sharing between applications and data, so that applications can directly operate data in a unified way, that is, applications and data have a high degree of independence.

1.2. Common database technology brands, services and architectures

After so many years of development, there are many database systems on the market, the strongest individual thinks that Oracle, of course, there are many such as: DB2, Microsoft SQL Server, MySQL, SyBase and so on. The following figure lists the common database technology brands, services and architecture.

1.3. Database classification

Databases are usually divided into three types: hierarchical database, network database and relational database.

Different databases are linked and organized according to different data structures.

In today's Internet, the most common database models are mainly two, namely, relational database and non-relational database.

1.3.1, relational database

At present, the main database in mature applications and services with various systems is still a relational database.

Representatives: Oracle, SQL Server, MySQL

1.3.2, non-relational database

With the progress and development of the times, non-relational database came into being.

Representatives: Redis, Mongodb

NoSQL databases have advantages in terms of storage speed and flexibility and are often used for caching.

1.4. Database standardization

After a series of steps, we have finally transformed the customer's requirements into data tables and established the relationships between these tables, so can we use them in development now? The answer is no, why? In the same project, many people participate in the demand analysis, database design, different people have different ideas, different departments have different business requirements, the database we design will inevitably contain a large number of the same data, structural conflicts may also occur, causing inconvenience in the development.

1.4.1. What is a paradigm?

In order to design a standardized database, we are required to do it according to the database design paradigm, that is, the normative principles of database design. The paradigm can guide us to better design the table structure of the database and reduce redundant data, so as to improve the storage efficiency, data integrity and scalability of the database.

When designing relational database, comply with different specification requirements, design reasonable relational database, these different specification requirements are called different paradigms, various paradigms are hierarchical norms, the higher the paradigm, the smaller the database redundancy. At present, there are six paradigms in relational databases: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the Badesco paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF, also known as perfect paradigm). The paradigm that meets the minimum requirements is the first normal form (1NF). The second normal form (2NF) which further meets the requirements of more specifications on the basis of the first normal form is called the second normal form, and so on. Generally speaking, the database only needs to satisfy the third normal form (3NF).

1.4.2. Three paradigms

The first normal form (1NF)

The so-called first normal form (1NF) means that in the relational model, a specification for the addition of columns requires that all columns should be atomic, that is, each column of a database table is an indivisible atomic data item, rather than a collection, array, record and other non-atomic data items. That is, when an attribute in an entity has multiple values, it must be split into different attributes. Each field value in the first normal form (1NF) table can only be an attribute or part of an attribute of an entity. In short, the first paradigm is a domain without repetition.

For example: in Table 1-1, the "Project address" column can also be subdivided into provinces, cities, etc. In foreign countries, more programs divide the "first name" column into two columns, namely "last name" and "first name".

Although the first paradigm requires each column to preserve atomicity and can no longer be divided, this requirement is related to our needs. For example, we have no query and application requirements for "engineering address" in the above table, such as provinces and cities. There is no need to split, and the same is true for the name column.

Table 1-1 original table

Project number

Project name

Engineering address

Employee number

Employee name

Salary and treatment

Position

P001

Hong Kong-Zhuhai-Macao Bridge

Zhuhai, Guangdong

E0001

Jack

6000 / month

Workers

P001

Hong Kong-Zhuhai-Macao Bridge

Zhuhai, Guangdong

E0002

Join

7800 / month

Workers

P001

Hong Kong-Zhuhai-Macao Bridge

Zhuhai, Guangdong

E0003

Apple

8000 / month

Senior technician

P002

South China Sea Aerospace

Hainan Sanya

E0001

Jack

5000 / month

Workers

Second normal form (2NF)

On the basis of 1NF, non-Key attributes must be completely dependent on the primary key. The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to meet the second normal form (2NF) must first meet the first normal form (1NF). The second normal form (2NF) requires that each instance or record in a database table must be uniquely localized. Select an attribute or attribute group that can distinguish each entity as the unique identification of the entity.

The second normal form (2NF) requires that the attributes of the entity are completely dependent on the primary keyword. The so-called complete dependence means that there can not be an attribute that depends only on part of the primary keyword. If it exists, then this part of the attribute and the primary keyword should be separated to form a new entity. There is an one-to-many relationship between the new entity and the original entity. To achieve differentiation, you usually need to add a column to the table to store the unique identity of each instance. In short, the second normal form is that the attribute is completely dependent on the primary key on the basis of the first paradigm.

For example: in Table 1-1, a table describes engineering information, employee information, and so on. This results in a large amount of data duplication. According to the second paradigm, we can split tables 1-1 into tables 1-2 and tables 1-3:

Project information table: (project number, project name, project address):

Table 1-2 Engineering Information Table

Project number

Project name

Engineering address

P001

Hong Kong-Zhuhai-Macao Bridge

Zhuhai, Guangdong

P002

South China Sea Aerospace

Hainan Sanya

L employee information table (employee number, employee name, title, salary level):

Table 1-3 employee information table

Employee number

Employee name

Position

Salary level

E0001

Jack

Workers

3000 / month

E0002

Join

Workers

3000 / month

E0003

Apple

Senior technician

6000 / month

In this way, Table 1-1 becomes two tables, each describing only one thing, clear and clear.

The third normal form (3NF)

The goal of the third paradigm is to ensure that the columns in the table are directly related to the primary key columns, not indirectly. That is, if each column and the primary key column are directly dependent, it satisfies the third normal form.

The third paradigm requires that each column is directly related to the primary key column. We can understand it this way. Suppose Zhang San is Li Si's soldier and Wang Wu is Zhang San's soldier. Is Wang Wu Li Si's soldier? From this relationship, we can see that Wang Wu is also Li Si's soldier, because Wang Wu depends on Zhang San, and Zhang San is Li Si's soldier, so Wang Wu is also Li Si's soldier. There is a relationship of indirect dependence rather than direct dependence as emphasized in our third paradigm.

Now let's see that in the explanation of the second paradigm, we split Table 1-1 into two tables. Whether these two tables are in line with the third paradigm. The employee information table includes: "employee number", "employee name", "Job", and "salary level". We know that the salary level is determined by the job, and here the "salary level" is related to the employee through the "position". Does not conform to the third paradigm. We need to further split the employee information table as follows:

L employee information table: employee number, employee name, job title

L Job list: job number, Job name, salary level

Now that we know the three paradigms of database normalization design, let's take a look at the data table optimized for Table 1-1:

Employee Information Table (Employee)

Employee number

Employee name

Job number

E0001

Jack

one

E0002

Join

one

E0003

Apple

two

Engineering Information sheet (ProjectInfo)

Project number

Project name

Engineering address

P001

Hong Kong-Zhuhai-Macao Bridge

Zhuhai, Guangdong

P002

South China Sea Aerospace

Hainan Sanya

Job schedule (Duty)

Job number

Job title

Salary and treatment

one

Workers

3000 / month

two

Senior technician

6000 / month

Project participant record sheet (Project_ Employee_info)

Serial number

Project number

Personnel number

one

P001

E0001

two

P001

E0002

three

P002

E0003

By comparison, we find that there are more tables, more complex relationships, more trouble in querying data, and more difficulty in programming, but the contents in each table are clearer, there is less repetitive data, and it becomes easier to update and maintain. so how to balance this contradiction?

1.4.3. Paradigm and efficiency

When we design the database, designers, customers and developers usually have some contradictions about the design of the database. Customers prefer convenient and clear results, and developers also want the database relationship to be relatively simple and reduce the difficulty of development. on the other hand, designers need to apply three paradigms to strictly standardize the database, reduce data redundancy and improve database maintainability and expansibility. From this, we can see that in order to meet the three paradigms, our database design will have differences with customers and developers, so in the actual database design, we can not blindly pursue standardization, we should not only consider the three paradigms, reduce data redundancy and various database operation anomalies, but also fully consider the performance of the database, and allow appropriate database redundancy.

II. MySQL introduction 2.1.Summary of MySQL

MySQL is a relational database management system developed by the Swedish company MySQL AB and currently belongs to the products of Oracle. MySQL is one of the most popular relational database management systems. In the aspect of WEB application, MySQL is one of the best RDBMS (Relational Database Management System) application software.

MySQL is a relational database management system in which relational databases store data in different tables instead of all data in one large warehouse, which increases speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts the dual licensing policy, which is divided into community version and commercial version. Because of its small size, high speed and low total cost of ownership, especially open source, the development of small and medium-sized websites generally choose MySQL as the website database.

MySQL official website: https://www.mysql.com/

MySQL download: https://www.mysql.com/downloads/

2.2. System characteristics

1. Written in C and C++, and tested with a variety of compilers to ensure the portability of the source code.

2. Support AIX, FreeBSD, HP-UX, Linux, Mac OS, NovellNetware, OpenBSD, OS/2 Wrap, Solaris, Windows and other operating systems.

3. API is provided for many programming languages. These programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby,.NET and Tcl.

4. Support multithreading and make full use of CPU resources.

5. The optimized SQL query algorithm can effectively improve the query speed.

6. It can be used not only as a separate application in the client server network environment, but also as a library and embedded in other software.

7. Provide multi-language support. Common codes such as GB 2312 and BIG5 in Chinese and Shift_JIS in Japanese can be used as data table names and data column names.

8. Provide a variety of database connections such as TCP/IP, ODBC and JDBC.

9. Provide management tools for managing, checking, and optimizing database operations.

10. Support large databases. It can handle large databases with tens of millions of records.

11. Multiple storage engines are supported.

12.MySQL is open source, so you don't have to pay extra.

13.MySQL uses the standard SQL data language form.

14.MySQL has good support for PHP, and PHP is the most popular Web development language at present.

15.MySQL can be customized, using the GPL protocol, you can modify the source code to develop your own MySQL system.

16. Online DDL/ change capabilities, data architecture support for dynamic applications and developer flexibility (5.6 new)

17. Copy the global transaction identity to support self-healing clustering (added in 5.6)

18. Replication crash-free slaves to improve availability (add 5.6)

19. Copy multithreaded slaves to improve performance (added in 5.6)

20.3x faster performance (5.7 New)

21. New optimizer (5.7 added)

twenty-two。 Native JSON support (5.7new)

23. Multi-source replication (5.7 New)

Spatial expansion of 24.GIS (5.7New)

2.3. Storage engine

MySQL database prepares different engines according to the needs of the application, and different engines have different emphasis. The differences are as follows:

The default database engine prior to MyISAM MySQL 5.0is the most commonly used. Has high insert and query speed, but does not support transactions

The preferred engine for InnoDB transactional database, which supports ACID transactions and row-level locking. MySQL 5.5 has become the default database engine.

BDB is derived from Berkeley DB, an alternative to transactional databases that supports other transaction features such as Commit and Rollback

Memory is a storage engine that stores all data in memory and has high insertion, update and query efficiency. But it takes up memory space proportional to the amount of data. And its contents will be lost when MySQL is restarted

Merge combines a certain number of MyISAM tables into a whole, which is useful for very large-scale data storage.

Archive is ideal for storing large amounts of independent, historical data. Because they are not often read. Archive has efficient insertion speed, but its support for queries is relatively poor.

Federated combines different MySQL servers to logically form a complete database. Very suitable for distributed applications

Cluster/NDB highly redundant storage engine that uses multiple data machines to jointly provide services to improve overall performance and security. Suitable for applications with large amount of data, high security and performance requirements

CSV is a storage engine that logically splits data by commas. It creates a .csv file for each data table in the database subdirectory. This is a normal text file that takes up one line of text per line of data. The CSV storage engine does not support indexing.

BlackHole black hole engine, any data written will disappear. It is generally used to record the relay of binlog replication.

The EXAMPLE storage engine is a stub engine that does nothing. It is intended as an example in the MySQL source code to demonstrate how to start writing a new storage engine. Again, its main interest is for developers. Indexing is not supported by the EXAMPLE storage engine.

In addition, the storage engine interface of MySQL is well defined. Interested developers can write their own storage engine by reading the documentation.

3. Quickly install and run MySQL database

MySQL has always been open source and free, but there have been some changes since it was acquired by Oracle: the previous version is free, the community version is open source free under the GPL agreement, and the commercial version offers richer features, but for a fee.

Download address of Community Edition: https://dev.mysql.com/downloads/ (free)

Download address of Enterprise Edition: https://www.mysql.com/downloads/ (charge)

3.1. Use the green version

In order to use MySQL conveniently and quickly, I have prepared a green MySQL, which can be used directly after decompression without any configuration.

Download address 1: https://pan.baidu.com/s/1hrS5KUw password: sug9

Download address 2: https://www.jb51.net/softs/594937.html

Decompress directly after download:

Click to start PStart.exe this is a custom menu gadget, in order to organize resources used.

There are two green versions of MySQL's software, 5.0 and 5.5.

Navicat for MySQL is a database client management tool

Click to start PStart.exe and the result is as follows:

Click to start the MySQL service and run Navicat for MySQL.

* Note: the above PStart is only a tool for organizing documents, and it is not necessary. If there is an error or empty time during startup, you can directly shut down and start the MySQL service, such as:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.