Database Data Processing Stories many

Every year, the evaluation of teaching will encounter a series of problems in data import. The harvest is quite abundant.

These days, people are always asking me what's wrong with you and why you're always on the data. Data is no problem, in order to be prepared, we need to put the 8 version of the most complete data into the 10 issue database.

Background: The problem is that from the 8 version of the basic system to the 10 edition of the basic system in the database design has a great change, for example, 10 students table stored in the class foreign key, and the 8 period table is the third table to maintain the relationship. There is a need for a course and a teacher in the ' class class ' of 10, and more than one table in the 8-period table is needed to correlate it. 20来 table, a variety of foreign key associations, up to a table to save 50多万条 data, how to do, how to do fast is placed in front of us an urgent need to solve the problem.

Story One:

Since the 8-period tables are linked through the third table. The 10-period table, like 1:n, is basically associated with foreign keys.

For example, I would like to add student information (including school number, name, class foreign key, etc.) to the 10 issue table. I need to start by exporting student information (excluding class foreign keys) from the 8-stage student table and then exporting the relationship between the students and the class from the third table of the class and the students, then combining the two tables into a single table.

This is our original most natural idea, of course, inefficient and Manual Check error rate is very high. So we switched to PLANB. Create a view of the students and the third table in the database, and then export the view to Excel.

Story two:

When we set up the view, we ran into another problem. By uniting two tables, we found that the total number of students was 22052 rows, while the overall view was 22191 rows. This should not happen if the data is not a problem. Because, according to reason, students and class two tables N:1 relationship, and table data is not redundant. It should be the same whether the link or the right link data.

So we used a query statement select ClassID from basicclassentities where ClassID not in (select ClassID from Basicstudententitie s) first forgive me for using such a inefficient query statement. We found out there were exactly 139 classes without students. Later, the study found that there are more than 100 virtual administrative classes (for the test stay), there are really three classes are due to human error is not associated with. Using relational queries between tables and tables, it is true that you can check for errors, which are not listed here.

Story three:

I think of a previous question. So I built a table to practice a bit. Title is: A table has 5 data, B table has 8 data. If there are 3 common data in two tables, how many data can be obtained by connecting the left and right links respectively.

Experiment One:

The data in the student table. data in the teacher table

A right-to- left connection is made through the student table and the teacher's table respectively.

experiment Two: on this basis, the 3 public data is changed to not one by one correspondence , and now has two IDs same as the student same ID.

The results are shown below.

See! Display content as follows

Story Four:

The first time we copied the sorted data into the database, we took the Excel field and the database field in a consistent manner and then CTRL + C +V. As a result, more than 20,000 students need more than 10 minutes to import. So we use the database comes with the import Excel function, found that even 500,000 of data can be instantly done. It is just a table that will be reborn after importing.

Then, from the table data to the other table , use the statement insert INTO Basicstudententities SELECT * from basicstudententities$ is also a matter of seconds.

A lot of fun stories, the next section continues ~

Database Data Processing Stories many