Shulou(Shulou.com)06/01 Report--

Database as the most frequently used middleware, as an IT engineer, even if he does not intend to engage in database development or DBA, he should master its basic knowledge, principles and basic use.

To this end, at the beginning of this article, try to explain the basic knowledge and principles of the database.

Type of database

Relational databases: in the past, the most common and widely used databases on the market were called relational databases, such as Oracle, SQL Server (Microsoft, also known as MSSQL), Mysql, DB2 (IBM), which are all relational databases. Because it is the most widely used and most common, every beginner must master it, so the next few articles "basic knowledge and principles of Database" will focus on relational database first, and then have the opportunity to introduce No SQL database.

Non-relational database: called No SQL database, is actually just a general name, there are a variety of column databases, KEY VALUE values of the database.

What is a relational database?

The essence of a relational database is a two-dimensional table about association relations.

A two-dimensional table means a table with rows and columns, which is the same as a single worksheet (sheet) in an EXCEL table. The only difference from each separate worksheet in the EXCEL table is that the tables in the relational database are all about associative relationships.

Suppose you have three worksheets called A, B, and C in your EXCEL table. If table A has A1, A2, A3 columns and content, table B has B1, B2, B3 columns and content, and C also has C1, C2, C3 columns and content, while each cell of B2 is actually referenced from the cell of the A2 column corresponding row, and the C3 cell is also referenced from B3. Then in fact, the three tables A, B and C are related. Then the EXCEL table is closer to the table in the relational database.

However, in the database, there is no stipulation that a cell of B2 must refer to the cell facing the row in A2, which can be the value of any row of cells.

Why use the database?

Why use a database instead of an EXCEL table? This is an important question. If you don't know what a thing is good for or what a problem can be solved, why take the time to learn something new?

First of all, in the case of a small amount of data, it is no problem to use the EXCEL table directly.

But when the data is about 20, 000 rows, I found that a Lenovo notebook of K22 is opened with EXCEL, which may take 7-8 seconds to open, which basically has reached the limit of the delay time that users can tolerate. If a user opens a web page and cannot open it in 7-8 seconds, he will probably choose to cross the page and go to other websites.

Therefore, when the amount of data is large, we find that EXCEL can no longer meet our performance needs, so we need to use the database.

In addition to performance, there are several issues with using a normal two-dimensional table:

Data redundancy: a large number of the same data are stored repeatedly. Let's take the following simple student course selection record table as an example.

Serial number student name gender contact telephone course name teacher 12019063001 Zhang three men 13900000001 Chinese Sun Qi 22019063001 Zhang three men 13900000001 mathematics week 32019063001 Zhang three men 13900000001 English Wu Jiu 42019063002 Li four male 13900000002 Chinese Sun 752019063002 Li four male 13900000002 Mathematical week 62019063003 Wang five female 72019063003 Mathematical week 872019063003 Wang Wu 82019063004 Zhao Liu female 13900000004 physical education Zheng Shi

I saw that Zhang San took three courses of Chinese, maths and English, but every time Zhang San took a course, his "student number", "gender", "contact number" and other data would be repeated once. If there are 100 records about him, the data will be repeated 100 times. Such data redundancy can lead to the following problems:

1) waste of storage space.

2) resulting in an increase in the amount of time it takes to retrieve valid data (because the total amount of data increases and the amount of data traversed during search increases).

Delete exception: if you want to delete 1 data, as a result, the data you don't want to delete will also be deleted. (still take the above example as an example)

Serial number student name gender contact telephone course name teacher 82019063004 Zhao Liuniu 13900000004 physical education Zheng Shi

If Zhao Liu drops out of school, we want to delete Zhao Liu's data, but we will find that only Zhao Liu has taken PE classes.

At this time, once the courses of "Zhao Liu" and "physical Education" and the teacher "Zheng Shi" are deleted from this form for no reason, this is called deletion of anomalies.

Modify exception: modify a data, but need to modify multiple times, and if the modification is not safe, it will lead to data inconsistency.

Serial number student name gender contact telephone course name teacher 12019063001 Zhang San male 13900000001 Chinese Sun Qi 22019063001 Zhang San male 13900000001 Mathematical week 32019063001 Zhang 13900000001 English Wu Jiu

If "Zhang San" changes his mobile phone and changes his mobile phone number, he needs to modify as many records as he has, and the cost of writing operation is greater than that of reading operation, which will bring additional overhead, and if the modification is incomplete, it will cause the contact number of "Zhang San" in the table to be inconsistent.

Insert exception: the data that should be inserted cannot be inserted.

Serial number, student number, name, gender contact telephone number, course name, teacher

Biological Zhang Qiang

How does the database handle the above data?

The database will split the above two-dimensional table into multiple associated two-dimensional tables for storage. Note that there are 2 keywords, 1 is multiple, 2 is related to the relationship. As follows:

1) split a two-dimensional table into multiple two-dimensional tables

Student serial number student name gender contact number 12019063001 Zhang three men 1390000000122019063002 Li four men 1390000000232019063003 Wang five women 1390000000342019063004 Zhao Liu female 13900000004

Course schedule number course ID course name 10001 Chinese 20002 Mathematics 30003 English 40004 physical Education

Teacher serial number teacher ID teacher name 1T0001 Sun 72T0002 Zhou 83T0003 Wu Jiu4T0004 Zheng Shi

2) create an association for the above independent table so that they are related to each other

Course selection record form serial number course ID11390000000100012139000000010002313900000001000341390000000200015139000000020002613900000003000271390000000300038139000000040004

Lecture record form serial number course ID10001T000120002T000230003T000340004T0004

According to the above treatment, what is the effect?

One table, which is divided into the above five tables, seems to complicate the simple problem. What is the use of making it so complicated? What problem has been solved?

1. Solve data redundancy:

If you look at the student table again, the information of the same student is stored only once, which saves space, improves the search performance, and solves the problem of data redundancy.

2. Resolve deletion exception:

If you look at the student table again, if "Zhao Liu" drops out at this time, you need to delete the "Zhao Liu" data in the "student table". In addition, the "elective record table" related to the "student number" field of the "student table" will automatically delete the row of the "Zhao Liu" student number (2019063004), that is, line 8, and the operation will be completed. On the other hand, the data of "Zheng Shi", the teacher of "physical education" and "physical education", will be completely retained in the "curriculum schedule" and "teacher table", and will not be deleted abnormally, which solves the problem of abnormal deletion.

3. Resolve the modification exception:

Now that Zhao Liu has changed his mobile phone, he only needs to update the field of "Zhao Liu"-"contact number" in the student form. Other forms do not save this information at all, that is to say, only one write operation is needed. There will be no data inconsistencies or multiple write operations, solving the modification exception.

4. Resolve the insertion exception:

If there is a new "political class" now, there are no teachers and elective students, all you need to do is to add a row to the curriculum to record the "political class", and other forms need to be written. There will not be the problem that there is no teacher or elective students before, and even the new courses can not be inserted, which solves the insertion exception.

To sum up, we need to use the database to solve the problems that cannot be solved by the above ordinary two-dimensional tables.

Most of the above are the reasons why we need to use the database functionally. In the next article, we will discuss why we should use the database in terms of performance.

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.