一、一键安装mariadb10.5.5
首先准备mariadb10.5.5安装包,可以通过官网下载,也可以在系统中直接curl命令下载,以下为官网下载链接:https://mariadb.org/download/
将下载好的安装包放在系统/root/目录下
执行以下脚本
#!/bin/bash
#
#********************************************************************
#Author: gaojinzhou
#QQ: 525184587
#Date: 2020-09-25
#FileName: mysql_install.sh
#URL: https://blog.csdn.net/GJZ23456789
#Description: The test script
#Copyright (C): 2020 All rights reserved
#********************************************************************
NAME=mariadb-10.5.5-linux-x86_64.tar.gz
DATA_DIR="/data/mysql"
#安装需要软件包
yum -y install libaio perl-Data-Dumper autoconf ncurses-compat-libs > /dev/null
#创建mysql账户
if `id mysql &>/dev/null` ;then
echo '该账户已存在'
else
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 -d /data/mysql mysql
fi
#准备数据目录
if [ -d $DATA_DIR ];then
echo '数据库已经安装'
else
mkdir $DATA_DIR
chown mysql:mysql $DATA_DIR
fi
#准备二进制程序
if [ -e $NAME ];then
tar xf $NAME -C /usr/local
else
echo '请先将数据库安装包移动到/root/目录下'
fi
cd /usr/local
ln -s mariadb-10.5.5-linux-x86_64 mysql
chown -R root:root /usr/local/mysql/
#准备配置文件
cat > /etc/my.cnf <<EOF
[mysqld]
datadir= $DATA_DIR
EOF
#创建数据库文件
cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql > /dev/null
#准备服务脚本,并启动服务
if [ -e /usr/local/mysql/support-files/mysql.server ];then
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
else
echo '该文件不存在'
fi
chkconfig --add mysqld
service mysqld start
#PATH路径
echo 'PATH=/usr/local/mysql/bin:$PATH'> /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
二、简述Event 事件介绍以及它的优缺点
优点:一些对数据定时性操作不再依赖外部程序,而直接使用数据库本身提供的功能,可以实现每秒钟
执行一个任务,这在一些对实时性要求较高的环境下就非常实用
缺点:定时触发,不可以直接调用
三、在 students 表中,查询年龄大于25岁,且为男性的同学的名字和年龄
首先在数据库中导入数据库文件
[root@Centos7 ~]# mysql < hellodb_innodb.sql
MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| emp |
| scores |
| students |
| teachers |
| testdata |
| toc |
+-------------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> select name,age from students where age>25 and gender='M';
+--------------+-----+
| name | age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
+--------------+-----+
7 rows in set (0.00 sec)
四、在 students 表中,以 ClassID 为分组依据,查询显示每组的平均年龄
MariaDB [hellodb]> select classid,avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| NULL | 63.5000 |
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
8 rows in set (0.00 sec)
五、显示第4题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> select classid,avg(age) from students group by classid having avg(age)>30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| NULL | 63.5000 |
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+
3 rows in set (0.00 sec)