linux shell 之数据库

#登录
mysql -u root -p123456

#创建数据库
create database test;
use test;

#创建表
cat create.sh
#!/bin/bash
MYSQL=`which mysql`
mysql test -u root -p <<EOF

#创建newpeople 表，包含4个属性
create table newpeople(name VARCHAR(20), sex CHAR(1), birch DATE, birthaddr VARCHAR(20));
show tables;
#可以加入任意的MySQL命令和SQL语句
EOF

./create.sh
Enter password:
Tables_in_test
newpeople

#插入脚本
cat insert.sh
#!/bin/bash

MYSQL=`which mysql`
mysql test -u root -p <<EOF
#与create.sh脚本的区别在于SQL语句的不同
#向newpeople表插入6条记录，记录间用逗号隔开，最后以分号结束
insert into newpeople values('Z Wu', 'm', '1982-09-11','China'),
('Q Cai', 'f', '1984-11-01', 'China'),
('T Ma', 'm', '1981-07-17', 'USA'),
('D Li', 'm', '1982-09-16', 'China'),
('J Park', 'm', '1970-10-21', 'Korea'),
('Z Lin', 'm', '1983-02-20', 'HongKong');

select * from newpeople;
EOF
./insert.sh
Enter password:
name sex birch birthaddr
Z Wu m 1982-09-11 China
Q Cai f 1984-11-01 China
T Ma m 1981-07-17 USA
D Li m 1982-09-16 China
J Park m 1970-10-21 Korea
Z Lin m 1983-02-20 HongKong

上述脚本insert.sh不够灵活，为了更灵活，我们看下面的脚本insert2.sh
#!/bin/bash

MYSQL=`which mysql`
mysql test -u root -p <<EOF
#与create.sh脚本的区别在于SQL语句的不同
#向newpeople表插入6条记录，记录间用逗号隔开，最后以分号结束
insert into newpeople values('Z Wu', 'm', '1982-09-11','China'),
('Q Cai', 'f', '1984-11-01', 'China'),
('T Ma', 'm', '1981-07-17', 'USA'),
('D Li', 'm', '1982-09-16', 'China'),
('J Park', 'm', '1970-10-21', 'Korea'),
('Z Lin', 'm', '1983-02-20', 'HongKong');

select * from newpeople;
EOF
./insert2.sh Jane f 1986-07-09 Canada
Enter password:
Data successfully added.

cat female.sh
#!/bin/bash

MYSQL=`which mysql`

#生成SQL语句， 用于选择女性的记录
statement="select * from newpeople where sex='f';"

mysql test -u root -p -e "$statement" | #用-e选项将statement提交到MySQL
tr "[\011]" ":" | #将Tab键替换成冒号
awk -F":" '{printf("%-8s\t%s\n", $1, $4)}' #格式化打印第1域和第4域

./female.sh
Enter password:
name birthaddr
Q Cai China
Jane Canada
Jane Canada