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

た入场券 2022-05-26 00:45 204阅读 0赞

核心的用法：

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!'