Use SQL in Shell Scripts

Use SQL In Shell Scripts

MySQL:

  • Login In Use Shell Directly

    1
    2
    3
    4
    5
    #!/bin/sh
    user="dbuser"
    pass="dbpassword"
    db="dbnme"
    mysql -u "$user" -p "$pass" "$db"

  • Use SQL Directly In Shell Scripts

    1
    2
    3
    4
    5
    6
    7
    8
    #!/bin/sh
    user="dbuser"
    pass="dbpassword"
    db="dbnme"
    mysql -u "$user" -p "$pass" "$db" <<EOF
      sql-statement-1;
      sql-statement-2;
    EOF

  • Using Shell Variables In SQL

    1
    2
    3
    4
    5
    6
    7
    8
    9
    #!/bin/sh
    user="dbuser"
    pass="dbpassword"
    db="dbnme"
    sql="select * from tal_name"
    mysql -u "$user" -p "$pass"  <<EOF
      use $db;
      $sql;
    EOF

  • one more

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    #!/bin/sh
    user="root"
    pass="root"
    db="ocean"
    echo "---------------------------------------------------------------------------------------------------drop ocean >> create ocean"
    mysql -u "$user" -p"$pass"  <<EOF
      drop database ocean;
    	create database ocean;
    	use ocean;
    	show databases;
    EOF
    echo "---------------------------------------------------------------------------------------------------insert datas use sql file"
    mysql -u "$user" -p"$pass" ocean < my.sql 
    #mysql -u "$user" -p"$pass" ocean < my.sql 
    echo "---------------------------------------------------------------------------------------------------re-entry ocean"
    mysql -u "$user" -p"$pass" ocean

Oracle:

  • Login In Use Shell Directly

    1
    2
    3
    4
    #!/bin/sh
    user="system"
    pass="manager"
    sqlplus -S $user/$pass

  • Use SQL Directly In Shell Scripts

    1
    2
    3
    4
    5
    6
    7
    8
    #!/bin/sh
    user="system"
    pass="manager"
    sqlplus -S $user/$pass <<EOF
      sql-statement-1;
      sql-statement-2;
     exit;
    EOF

  • Using Shell Variables In SQL

    1
    2
    3
    4
    5
    6
    7
    8
    #!/bin/sh
    user="system"
    pass="manager"
    var="$1"
    sqlplus -S $user/$pass <<EOF
     SELECT * FROM tableName WHERE username=$var;
     exit;
    EOF