1、编写一键安装Mariadb脚本
系统版本，最小化安装
[root@localhost ~]# uname -r
3.10.0-1127.el7.x86_64
[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)
[root@localhost ~]# vim install_mariadb.sh
#!/bin/bash
#
#*********************************************************
#Author: blackbeard
#Date: 2020-09-27
#FileName: install_mariadb.sh
#Des: The test file
#*********************************************************
RED="\e[1;31m"
END="\e[0m"
DIR=`pwd`
FILENAME="mariadb-10.2.31.tar.gz"
echo -e "$RED[---------------------初始化系统环境---------------------]$END"
sed -i 's/SELINUX=enforcing/SELINUX=disbaled/g' /etc/selinux/config
systemctl disable firewalld
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
sed -i '3d' /etc/chrony.conf
sed -i '2a server ntp1.aliyun.com iburst' /etc/chrony.conf
sed -i '3a server s2m.time.edu.cn iburst' /etc/chrony.conf
systemctl restart chronyd
systemctl enable chronyd
#################################
declare -A app
app[a1]='gcc'
app[a2]='gcc-c++'
app[a3]='cmake'
app[a4]='bison'
app[a5]='bison-devel'
app[a6]='zlib-devel'
app[a7]='libcurl-devel'
app[a8]='libarchive-devel'
app[a9]='boost-devel'
app[a10]='ncurses-devel'
app[a11]='gnutls-devel'
app[a12]='libxml2-devel'
app[a13]='openssl-devel'
app[a14]='libevent-devel'
app[a15]='libaio-devel'
for j in ${!app[*]};do
rpm -q ${app[$j]} &> /dev/null && echo -e "$RED[-------------------${app[$j]}已安装-------------------]$END" || yum install -y ${app[$j]} &> /dev/null
done
echo -e "$RED[--------------------环境设置完成-----------------]$END"
#################################
cd ~
if [ -f $FILENAME ];then
echo -e "$RED[-----------------$DIR存在源码包------------------]$END"
else
echo -e "$RED[----------------$DIR不存在源码包-----------------]$END"
exit 1
fi
#################################
id mysql &> /dev/null
if [ $? -eq 0 ];then
echo -e "$RED[---------------------用户已存在-------------------]$END"
else
echo -e "$RED[-------------------创建用户mysql------------------]$END"
useradd -r -s /sbin/nologin -d /data/mysql mysql
fi
mkdir /data/mysql
chown mysql.mysql /data/mysql
#################################
tar xvf mariadb-10.2.31.tar.gz
cd mariadb-10.2.31
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make && make install
#################################
if [ $? -eq 0 ];then
echo -e "$RED[---------------------安装成功---------------------]$END"
else
echo -e "$RED[---------------------安装失败---------------------]$END"
rm -f CMakeCache.txt
exit 1
fi
echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
cd /app/mysql/
scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf
cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
systemctl start mysqld
2、Event 事件以及它的优缺点
- Event事件是MySQL在相应的时刻调用的过程式函数库对象，实际上就是计划任务，不过比CRON更精确，可以以秒级执行任务，而CRON最小只能以分钟为单位。Event事件可以一次性的执行任务，也可以周期性的执行，由一个特定的线程来管理，即——事件调度器。事件与触发器类似，一条SQL语句执行，触发器就启动了，而事件则根据调度事件来启动。
- 优点：某些对数据的定时性操作不再依赖外部程序，而直接使用数据库本身的功能，可以实现每秒钟就执行一个任务
- 缺点：不可以直接调用，定时触发
3、在 students 表中，查询年龄大于25岁，且为男性的同学的名字和年龄
[root@localhost ~]# mysql < hellodb_innodb.sql
mysql> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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)
4、在 students 表中，以 ClassID 为分组依据，查询显示每组的平均年龄
mysql> SELECT classid,avg(age) FROM students GROUP BY classid HAVING classid IS NOT NULL;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 2 | 36.0000 |
| 1 | 20.5000 |
| 4 | 24.7500 |
| 3 | 20.2500 |
| 5 | 46.0000 |
| 7 | 19.6667 |
| 6 | 20.7500 |
+---------+----------+
7 rows in set (0.00 sec)
5、显示第2题中平均年龄大于30的分组及平均年龄
mysql> SELECT classid,avg(age) FROM students GROUP BY classid HAVING classid IS NOT NULL AND avg(age) > 30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+
2 rows in set (0.01 sec)
网友评论