mysql shell如何连接数据库操作,shell脚本操作mysql数据库

#创建表

create_table_sql="create table IF NOT EXISTS ${TABLENAME} (name varchar(20), id int(11) default 0)"

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD -D${DBNAME} -e "${create_table_sql}"

#插入数据

insert_sql="insert into ${TABLENAME} values('billchen',2)"

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD -D${DBNAME} -e  "${insert_sql}"

#更新数据

update_sql="update ${TABLENAME} set id=3"

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD -D${DBNAME} -e "${update_sql}"

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD -D${DBNAME} -e "${select_sql}"

#删除数据

delete_sql="delete from ${TABLENAME}"

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD -D${DBNAME} -e "${delete_sql}"

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD -D${DBNAME} -e "${select_sql}"

#导入sql文件

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD $DBNAME < expert.sql

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD $DBNAME < topic.sql

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD $DBNAME < institution.sql

#导出sql文件

mysqldump -h$HOSTNAME -P$PORT -u$USER -p$PASSWD $DBNAME > all.sql

#导出数据库的某个表

mysqldump -h$HOSTNAME -P$PORT -u$USER -p$PASSWD $DBNAME --table $TABLENAME  > table.sql

# 导出数据库(只导结构，不要数据)-d

mysqldump -h$HOSTNAME -P$PORT -u$USER -p$PASSWD -d $DBNAME > nodata_db.sql

# 导出数据库(只导数据，不要结构)-t

mysqldump -h$HOSTNAME -P$PORT -u$USER -p$PASSWD -t $DBNAME > onlydata_db.sql

#转换数据库格式编码

iconv -t utf-8 -c topic.sql > topic_utf8.sql

#修改mysql表的格式

mysql -h$HOSTNAME -P$PORT -u$USER -p$PASSWD -D${DBNAME} -e "alter table $TABLENAME convert to character set utf8mb4;"