Shell script Loop executes MySQL statement

Reference: Executing MySQL statements in shell scripts

requirement : There is a data table in the database that stores the user's comments on the movie (user_id movie_id rating time), but I'm going to have a total score for each movie.

Workaround :

1), write a SQL file Test.sql:use movie_recommendation;select distinct movie_id from T_user_movie_rating_map;

2), in terminal input MYSQL-UROOT-P123456-E < Test.sql > Movie_id.txt

This will get a list of movie IDs, and then edit the file to delete the movie_id string from the first line.

3), and then edit the following shell script rating.sh:

1#!/bin/Bash2 3  forLineinch$(Catmovie_id.txt)4  Do5result=$ (mysql-uroot-p123456-e"Use movie_recommendation;select avg (rating) from T_user_movie_rating_map where movie_id= $line")6tmp=$ (Echo$result |sed 's//\n/g')7      forTmp_lineinch$tmp8      Do9result=$tmp _lineTen      Done One     Echo$line: $result A     Echo$line, $result >>Rating.txt -  Done

4), in the terminal input. The./rating.sh can write the movie ID and movie corresponding score to the specified folder

Note: The result is processed in the middle, because the query database obtains the result has the title, needs to remove this title

Shell script Loop executes MySQL statement