基础札记--shell 操作 mysql sql语句

核心的用法：

1.连接到指定db：

conn="mysql -hlocalhost -P3306 -uuser_w -puser_w -D customer_db"

2.操作指定sql

sql="delete from order_details where uid=$USERID"

 $conn -e "$sql"

3.取得select语句的返回值

sql="select uid from user_infos where mobile=$1"

USERID=`echo $($conn -e "$sql") | awk -F ' ' '{print $2}'`

这条sql真实返回：

+----------+

| uid      |

+----------+

| 1111222 |

+----------+

1 row in set (0.00 sec)

echo之后返回是uid 1111222

然后通过awk取terminal显示的第二个值，其中，-F 表示以什么分割，这里是以空格' '分割。

详细示例demo如下：

#!/bin/bash

END_COLOR='\e[0m'

source ~/.bash_profile

conn="mysql -hlocalhost -P3306 -uuser_w -puser_w -D customer_db"

if [[ $? -ne 0 ]]; then
       echo -e $RED "error: connect to db error." $END_COLOR
       exit 1
fi

sql="select uid from user_infos where mobile=$1"

USERID=`echo $($conn -e "$sql") | awk -F ' ' '{print $2}'`

echo $USERID

sql="delete from bank_cards where mobile=$1;"

$conn -e "$sql"

if [[ $? -ne 0 ]]; then
       echo -e $RED "error: delete from bank_card fail." $END_COLOR
       exit 1
fi

#DEL from order_details
 sql="delete from order_details where uid=$USERID"

 $conn -e "$sql"

if [[ $? -ne 0 ]]; then
       echo -e $RED "error: delete from order_details fail." $END_COLOR
       exit 1
fi

#DEL from order_generations

sql="delete from order_generations where uid=$USERID"

$conn -e "$sql"

if [[ $? -ne 0 ]]; then
       echo -e $RED "error: delete from order_generations fail." $END_COLOR
       exit 1
fi

#DEL from order_refund_records

sql="delete from order_refund_records where uid=$USERID"

$conn -e "$sql"

if [[ $? -ne 0 ]]; then
       echo -e $RED "error: delete from order_refund_records fail." $END_COLOR
       exit 1
fi

#DEL from orders

sql="delete from orders where uid=$USERID"

$conn -e "$sql"

if [[ $? -ne 0 ]]; then
       echo -e $RED "error: delete from orders fail." $END_COLOR
       exit 1
fi

#DEL from user_credit_infos

sql="delete from user_credit_infos where uid=$USERID"

$conn -e "$sql"

if [[ $? -ne 0 ]]; then
       echo -e $RED "error: delete from user_credit_infos fail." $END_COLOR
       exit 1
fi

#DEL from user_infos

sql="delete from user_infos where uid=$USERID"

$conn -e "$sql"

if [[ $? -ne 0 ]]; then
       echo -e $RED "error: delete from user_infos fail." $END_COLOR
       exit 1
fi

echo 'clear all the data success!'