shell中执行mysql的sql

0.管道函数和echo

echo "show variables where Variable_name='max_connections';" | mysql -pmysql -N

 

1.左位移两位
mysql -uroot -poracle -e "select * from test.t1;"

vi dml.sh

#!/bin/bash
mysql -uroot -poracle <<EOF
insert into test.t1 select 5,'v5';
exit
EOF

2.管道函数&bash函数
#my3306为快速登陆路径的凭证
mysql_config_editor set --login-path=my3306 --user=root --host=127.0.0.1 --port=3306 --password
vi tbs_sel.sh

# 表空间使用率检查函数
function f_check_tbs(){
echo "SELECT a.schema_name db_name,
       CONCAT(IFNULL(ROUND((SUM(b.data_length) + SUM(b.index_length)) / 1024 / 1024,
                           0),
                     0),
              'M') total,
       CONCAT(IFNULL(ROUND(((SUM(b.data_length) + SUM(b.index_length)) -
                           SUM(b.data_free)) / 1024 / 1024,
                           0),
                     0),
              'M') used,
       CONCAT(IFNULL(ROUND(SUM(data_free) / 1024 / 1024, 0), 0), 'M') free,
       CONCAT(IFNULL(ROUND((((SUM(b.data_length) + SUM(b.index_length)) -
                           SUM(b.data_free)) /
                           ((SUM(b.data_length) + SUM(b.index_length))) * 100),
                           0),
                     0),
              '%') ratio,
       COUNT(table_name) TABLES
  FROM information_schema.schemata a
  LEFT JOIN information_schema. TABLES b
    ON a.schema_name = b.table_schema
 WHERE a.schema_name NOT IN
       ('information_schema', 'mysql', 'performance_schema')
 GROUP BY a.schema_name
 ORDER BY 1;"
}
echo "08、数据库表空间使用率"           
f_check_tbs|mysql --login-path=my3306   

3.mysql -e详解
可以用shell脚本操作mysql数据库，使用mysql的-e参数可以执行各种sql的(创建，删除，增，删，改、查)等各种操作 。
用法
mysql  -hhostname -Pport -uusername -ppassword  -e  相关mysql的sql语句，不用在mysql的提示符下运行mysql，即可以在shell中操作mysql的方法。

vi dml.sh

#!/bin/bash
HOSTNAME="127.0.0.1"
PORT="3306"
USERNAME="root"
PASSWORD=""
DBNAME="test_db_test"
TABLENAME="test_table_test"
#创建数据库
create_db_sql="create database IF NOT EXISTS ${DBNAME}"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} -e"${create_db_sql}"
#创建表
create_table_sql="create table IF NOT EXISTS ${TABLENAME} (  name varchar(20), id int(11) default 0 )"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${create_table_sql}"
#插入数据
insert_sql="insert into ${TABLENAME} values('billchen',2)"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${insert_sql}"
#查询
select_sql="select * from ${TABLENAME}"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${select_sql}"