#!/bin/bash
#author     xiaobao 
#date       2017-3-24 21:37:27
#The principle of this sql is:
#1. Create t_prog_test table   
#2. The value of file_name field in t_file_bak table is divided into four parts, and the values are inserted into course_id, student_id, sub_id and extra of t_prog_test table, respectively.
#3. If the extra value is not bak, it is null.
#4. Query t_prog_test, t_file_distribution, t_file_info, t_file1_list, t_file2_list, t_course_info tables correctly, respectively.
#5. Delete all the course_id values A in t_file_distribution, t_file_info, t_file1_list, t_file2_list and t_course_info tables.


#Create tables
USE 'xiaobao_db';
DROP TABLE IF EXISTS `t_prog_test`;
CREATE TABLE `t_prog_test` (
  `course_id` varchar(64) NOT NULL,
  `student_id` varchar(64) NOT NULL,
  `sub_id` varchar(64) NOT NULL,
  `extra` varchar(64) DEFAULT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  KEY `course_id` (`course_id`,`student_id`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#insert data
insert into t_prog_test(course_id, student_id, sub_id, extra, file_name) 
select SUBSTRING_INDEX(file_name, '_', 1) as course_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(file_name, '_', -2), '_', 1) as student_id, 
SUBSTRING_INDEX(SUBSTRING_INDEX(file_name, '_', -1), '.', 1) as sub_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(file_name, '_', -1), '.', -1) as extra,
file_name
from `t_file_bak`; 

#Change the extra value
update t_prog_test set extra='' where extra != 'bak';

#Check whether the form is correct
select * from t_prog_test;

#Association Query
select t1.file_name from t_file_distribution t1
where t1.file_name in (select t2.file_name from t_prog_test t2 where t2.course_id = 'A');

select t1.file_name from t_file_info t1
where t1.file_name in (select t2.file_name from t_prog_test t2 where t2.course_id = 'A');

select t1.file_name from t_file1_list t1
where t1.file_name in (select t2.file_name from t_prog_test t2 where t2.course_id = 'A');

select t1.file_name from t_file2_list t1
where t1.file_name in (select t2.file_name from t_prog_test t2 where t2.course_id = 'A');

select t1.student_id from t_course_info t1
where t1.student_id in (select t2.student_id from t_prog_test t2 where t2.course_id = 'A');

#Relevance deletion
delete from t_file_distribution 
where file_name in (select t2.file_name from t_prog_test t2 where t2.course_id = 'A');

delete from from t_file_info 
where file_name in (select t2.file_name from t_prog_test t2 where t2.course_id = 'A');

delete from from t_file1_list 
where file_name in (select t2.file_name from t_prog_test t2 where t2.course_id = 'A');

delete from from t_file2_list 
where file_name in (select t2.file_name from t_prog_test t2 where t2.course_id = 'A');

delete from from t_course_info 
where student_id in (select t2.student_id from t_prog_test t2 where t2.course_id = 'A');

Write this article because of the problem, because some silent reasons lead to the loss of some files on disk, need to re-write the data to the database, before re-writing to the database, need to delete the data records of the lost files.
Since there are a large number of other files whose data records are recorded on the same table as those of the missing files, it is necessary to filter out the data records of the missing files and then delete the data records. There are two headaches. The first problem is that the data records of files do not exist only in one table, but in many tables, so Association deletion is necessary. The second problem is that correlation field values are necessary for association deletion, but not all the data tables that delete records have associated field values. Find a data table that contains field values that other tables have. Then the field values are split and the tables are rebuilt after the splitting. Then several tables which will delete records are connected with the rebuilt tables, and finally the related deletion is carried out.
In dealing with this problem, I really feel difficult, but when I come back to sort out my ideas after processing, I find that it is not so complicated, mainly because I have too little experience in dealing with database problems, otherwise I will not be burnt out at that time. In order to prevent leakage of work content, the sql script is a general version after processing, children's shoes need a little modification can be used directly. Also welcome the big God message to Tucao.