使用shell脚本操作Mysql数据库练习_添加100名用户

练习 前提:授权tom@192.168.%.%对hidb数据库上的students表有所有访问权限；然后通过tom用户完成以下操作；

1. 将birthdate字段改为age；
2. 向students中插入100行数据；
• 用户名为stu1-stu100;性别随机为M或者F；年龄为18-100之间的随机数；
3. 以性别为分组，显示每组的平均年龄；
4. 显示年龄大于40的用户；

  启动数据库服务 systemctl start mariadb.service 登陆数据库 mysql -uroot -h127.0.0.1 -p123456 创建用户并授权访问hidb数据库 MariaDB [hidb]> GRANT ALL ON hidb.* TO 'tom'@'192.168.%.%' IDENTIFIED BY '123456'; 以tom用户身份登陆 mysql -utom -h192.168.60.129 -p123456 切换到hidb数据库 use hidb 查看students表中的字段 desc students; 将birthdate字段改为age alter table students change birthday age INT UNSIGNED NOT NULL;   向student表中插入100行数据 

#!/bin/bash
 
 
get_gender()
{
gender=('M' 'F')
index=$[$RANDOM%2]
echo ${gender[$index]}
}
 
 
get_age()
{
    rand_age=$[$RANDOM%82]
    if [ $rand_age -lt 18 ];then
        let rand_age=$rand_age+18
    fi
    echo $rand_age
}
HOSTNAME="192.168.60.129"
USERNAME="tom"
PASSWORD="123456"
 
 
main()
{
declare -i i=1
while [ $i -le 100 ];do
    name="user$i"
    gen=$(get_gender)
    age=$(get_age)
    let i=$i+1
    INSERT_SQL="INSERT INTO hidb.students (name,gender,age) VALUES ('$name','$gen',$age);"
    mysql -u${USERNAME} -h${HOSTNAME} -p${PASSWORD} -e "${INSERT_SQL}"
done
}
main

    以性别为分组，显示每组的平均年龄；   MariaDB [hidb]> select avg(age) AS avg_age,gender from students GROUP BY gender;     显示年龄大于40的用户；     MariaDB [hidb]> select * from students where age>40;      

#!/bin/bash
 
 
get_gender()
{
gender=('M' 'F')
index=$[$RANDOM%2]
echo ${gender[$index]}
}
 
 
get_age()
{
    rand_age=$[$RANDOM%82]
    if [ $rand_age -lt 18 ];then
        let rand_age=$rand_age+18
    fi
    echo $rand_age
}
HOSTNAME="192.168.60.129"
USERNAME="tom"
PASSWORD="123456"
 
 
main()
{
declare -i i=1
while [ $i -le 1 ];do
    name="user$i"
    gen=$(get_gender)
    age=$(get_age)
    let i=$i+1
    INSERT_SQL="INSERT INTO hidb.students (name,gender,age) VALUES ('$name','$gen',$age);"
    GROUP_SQL="select avg(age) AS avg_age,gender from hidb.students GROUP BY gender;"
    SELECT_SQL="select * from hidb.students where age>40;"
    mysql -u${USERNAME} -h${HOSTNAME} -p${PASSWORD} -e "${SELECT_SQL}"
done
}
main