shell 脚本 - 实现 mysql的增删改查

#! /bin/bash

dbuser="root"
export MYSQL_PWD=123456

username="username"
password="password"
tel="tel"
email="email"
wechat="wechat"


judge_cmd() {
	if [ "$1" != "-add" ] && [ "$1" != "-update" ] && [ "$1" != "-delete" ] && [ "$1" != "-found" ] && [ "$1" != "-help" ] && [ "$1" != "-ascend" ] && [ "$1" != "-descend" ] && [ "$1" != "-file" ]
	then
		echo "请查看完帮助手册后输入正确参数 -help"
		exit 0
	fi
	
}

mysql_init() {
        mysql -u${dbuser}  <<EOF
        CREATE DATABASE IF NOT EXISTS mydb;
	
 	USE mydb;

        CREATE TABLE IF NOT EXISTS loginuser(
        username VARCHAR(32) UNIQUE KEY,
        password VARCHAR(128)
        );
 
        CREATE TABLE IF NOT EXISTS userinfo(
        username VARCHAR(32) UNIQUE KEY NOT NULL,
        password VARCHAR(128),
        tel VARCHAR(16),
        email VARCHAR(64),
        wechat VARCHAR(32)
        );
EOF
}

mysql_tri_add() {
	mysql -u${dbuser} mydb <<EOF
	DROP TRIGGER IF EXISTS tri_user_insert;
	DELIMITER ;;
        CREATE TRIGGER tri_user_insert AFTER INSERT ON userinfo FOR EACH ROW BEGIN
                INSERT INTO loginuser(username, password) VALUES(new.username, new.password);
        END
        ;;
        DELIMITER ;
EOF
}

mysql_tri_delete() {
	mysql -u${dbuser} mydb <<EOF
	DROP TRIGGER IF EXISTS tri_user_delete;
        DELIMITER ;;
        CREATE TRIGGER tri_user_delete AFTER DELETE ON userinfo FOR EACH ROW BEGIN
                DELETE FROM loginuser WHERE username=old.username;
        END
        ;;
        DELIMITER ;   
EOF
}

mysql_tri_update() {	
	mysql -u${dbuser} mydb <<EOF
	DROP TRIGGER IF EXISTS tri_user_update;
        DELIMITER ;;
        CREATE TRIGGER tri_user_update AFTER UPDATE ON userinfo FOR EACH ROW BEGIN
                UPDATE loginuser SET password=new.password WHERE username=old.username;
        END
        ;;
        DELIMITER ;
EOF
}

mysql_cmd() {
	i=0
	state=0

	while [ $(( i++ )) -lt $# ] 
	do
		var=${@:i:1}		

		if [[ $var = $username ]] || [[ $var = $password ]] || [[ $var = $tel ]] || [[ $var = $email ]] || [[ $var = $wechat ]]
		then
			(( state++ ))
			if [ $state -eq 2 ]
			then
				state=1
				target="$target,"
			fi
			target="$target $var"
			continue
		fi

		if [[ $value = -found ]] || [[ $value = -ascend ]] || [[ $value = -descend ]]
		then
			if [[ $state -eq 0 ]]
			then
				target="*FROM"
			else
				state=0
			fi
		fi

		if [[ $var != $username=* ]] && [[ $var != $password=* ]] && [[ $var != $tel=* ]] && [[ $var != $email=* ]] && [[ $var != $wechat=* ]] && [[ $var != "for" ]]
		then
			echo "请参考帮助手册了解命令 -help"
			exit 1
			
		fi
		

		(( state++ ))
	
		if [[ $var = for ]]
		then
			var="where"
			value=$var
			state=0
		else
			temp=${var/=*}
			var="'${var/*=}'"
			var="$temp=$var"
		fi
		
		
		if [ $state -eq 2 ]
		then
			state=1
			if [[ $value = where ]]
			then
				cond="$cond and"
			else
				cond="$cond,"
				target="$target,"
			fi
		fi
		
		if [[ $value = -add ]]
		then
			var="${var/*=}"
			target="$target $temp"
		fi

		cond="$cond $var"		
		
	done
}


mysql_mode() {

	shift
	all=$*
	last=${!#}	

	if [[ -z $all ]]
	then
		echo "请参考帮助手册输入参数 -help"
		exit 1
	fi

	if [[ $last != $username=* ]] && [[ $last != $password=* ]] && [[ $last != $tel=* ]] && [[ $last != $email=* ]] && [[ $last != $wechat=* ]] && [[ $last != for ]] && [[ $last != all ]] && [[ ! $all =~ for ]]
	then
		mysql_cmd ${all/%$last}
		cond="$cond where username='$last'"
	elif [[ $last = all ]]
	then
		mysql_cmd ${all/%$last}
	else
		mysql_cmd $all
	fi
	
}

mysql_file() {
	IFS=$bak
	add="INSERT INTO userinfo(username, password, tel, email, wechat) VALUES('$1', '$2', '$3', '$4', '$5');"
	tofile="$tofile $add"
}

mysql_rank() {
	
	shift
	all=$*

	if [[ ! $all =~ for ]]
	then
		echo "请参考帮助手册了解命令 -help"
		exit 0
	fi

	if [[ -z ${all/for*} ]]
	then
		scend="SELECT *FROM userinfo order by"
	else	
		mysql_cmd ${all/for*}
		scend="SELECT $target FROM userinfo order by"
	fi

	target=''
	mysql_cmd ${all/*for}
	scend="$scend  $target"
}

mysql_perform() {
	mysql -u$dbuser mydb <<EOF
	$*
EOF
}


if [ "$#" -eq 0 ]
then
	echo "$0 请输入参数 -help"
	exit 1
fi

judge_cmd $*

mysql_init

case $1 in
	-add)
		shift
		value="-add"
		mysql_cmd $*
		add="INSERT INTO userinfo($target) VALUES($cond);"

		mysql_tri_add
		mysql_perform $add 
		;;
	-update)
		mysql_mode $*
		update="UPDATE userinfo SET $cond;"

		mysql_tri_update
		mysql_perform $update
		;;
	-delete)
		mysql_mode $*
		delete="DELETE FROM userinfo $cond;"

		mysql_tri_delete
		mysql_perform $delete
		;;
	-found)
		value="-found"

		if [ $# -eq 2 ] && [[ ${!#} = all ]]
		then
			found="SELECT *FROM userinfo;"
		else	
			mysql_mode $*
			
			if [[ ! $target =~ FROM ]]
			then
				found="SELECT $target FROM userinfo $cond;"
			else
				found="SELECT $target userinfo $cond;"
			fi
		fi

		mysql_perform $found
		;;
	-file)
		shift
		
		if [ $# -ne 1 ]
		then
			echo "请参考帮助手册了解命令 -help"
			exit 0	
		fi

		if [ ! -f $* ]
		then	
			echo "文件不存在，请检查文件名或者路径是否正确"
			exit 0
		fi

		bak=$IFS	

		while read line
		do
			IFS=","
			mysql_file $line
		done < $*
		
		IFS=$bak
		mysql_perform $tofile
		;;
	-ascend)
		value="-ascend"
		mysql_rank $*
		ascend="$scend;"
		mysql_perform $ascend
		;;
	-descend)
		value="-descend"
		mysql_rank $*
		descend="$scend desc;"
		mysql_perform $descend
		;;
	-help)
		echo -e "-add		添加信息 
				
					模板	-add 	[添加信息]
						[添加信息]: 
							字段属性=添加内容 ...
							username的值不能为NULL"
					
		echo -e "-update		更新信息 
					模板 	-update [更新目标]  [更新条件]
						[更新目标]:
							字段属性=更新内容...
						[更新条件]: 
							默认: 更新目标的username对应值
							若全部更新: all 或者 放空
							若指定字段更新: for 字段属性=原先内容 ..."

		echo -e "-delete		删除信息 
					模板 	-delete	 [删除条件]
						[删除条件]：
							默认: 删除目标的username对应值
							若全部删除: all
							若指定字段删除: for 字段属性=删除内容 ..."

		echo -e "-found		查询信息
					模板	-found 	[查找目标]  [查找条件]
						[查找目标]:
							若不输入则默认为查找全部
							若指定字段查找: 字段属性 ...
						[查找条件]:
							默认: 查找目标的username对应值
							若全部查找: all
							若指定字段查找: for 字段属性=查找内容 ..."

		echo -e "-ascend		升序查询
					模板	-ascend  [查找目标]  [升序条件]
						[查找目标]:
							若不输入则默认为查找全部
							若指定字段查找: 字段属性 ...
						[升序条件]:
							for 字段属性 ..."

		echo -e "-descend	降序查询
					模板	-descend [查找目标]  [降序条件]
						[查找目标]:
							若不输入则默认为查找全部
							若指定字段查找: 字段属性 ...
						[降序条件]:
							for 字段属性 ..."

		echo -e "-file		文件导入	
					模板	-file	 [文件名]
						[文件格式]:
							username, password, tel, email, wechat"			

		echo -e "附录		字段属性:	username; password; tel; email; wechat"
		;;
esac