1、使用rpm安裝mysql

yum -y install mariadb-server.x86_64

2、二進(jìn)制安裝mariadb
(1)創(chuàng)建用戶

useradd -r -d /data/mysql -s /sbni/nologin mysql

(2)準(zhǔn)備數(shù)據(jù)目錄

install -o mysql -g mysql -d /data/mysql

(3)準(zhǔn)備二進(jìn)制程序

tar xvf mariadb-10.2.19-linux-x86_64.tar.gz
ln -s mariadb-10.2.19-linux-x86_64 mysql
chown -R mysql:mysql /usr/local/mysql

(4)準(zhǔn)備配置文件

mkdir /etc/mysql/
cp /usr/local/mysql/support-files/my-huge.cnf  /etc/mysql/my.cnf

[mysqld]
datadir         = /data/mysql
innodb_file_per_table = on
skip_name_resolve = on

(5)創(chuàng)建數(shù)據(jù)庫(kù)文件

scripts/mysql_install_db --datadir=/data/mysql --user=mysql

(6)準(zhǔn)備啟動(dòng)腳本并啟動(dòng)服務(wù)

cp support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

(7)PATH路徑

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql

(8)安全初始化

mysql_secure_installation

3、編譯安裝mariadb
(1)安裝依賴包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel

(2)準(zhǔn)備用戶和數(shù)據(jù)目錄

useradd -r -d /data/mysql -s /sbin/nologin mysql
install -o mysql -g mysql -d /data/mysql
tar xvf mariadb-10.2.19.tar.gz

(3)cmake 編譯安裝

cd mariadb-10.2.18/
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/mysql \
-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

(4)準(zhǔn)備環(huán)境變量

echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

(5)生成數(shù)據(jù)庫(kù)文件

cd /app/mysql/
scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql

(6)準(zhǔn)備配置文件

cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf

(7)準(zhǔn)備啟動(dòng)腳本

cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld

(8)啟動(dòng)服務(wù)

chkconfig --add mysqld ;service mysqld start

4、students表的數(shù)據(jù)，實(shí)現(xiàn)下面的需求，寫出sql：

MariaDB [db_1]> select * from student;
+----+--------+-------+-------+------+-------+------+
| id | name   | phone | score | sex  | class | age  |
+----+--------+-------+-------+------+-------+------+
|  1 | adc    | 110   |    86 | m    | 1     |   21 |
|  2 | zhou   | NULL  |    80 | f    | 1     |   26 |
|  3 | fei    | NULL  |   100 | f    | 2     |   27 |
|  4 | lin    | NULL  |    88 | m    | 2     |   22 |
|  5 | wang   | 1000  |    99 | m    | 2     |   33 |
|  6 | 飛哥   | 10000 |    99 | m    | 2     |   56 |
+----+--------+-------+-------+------+-------+------+

1）在students表中，查詢年齡大于25歲，且為男性的同學(xué)的名字和年齡

MariaDB [db_1]> select name,age from student where age>25;
+--------+------+
| name   | age  |
+--------+------+
| zhou   |   26 |
| fei    |   27 |
| wang   |   33 |
| 飛哥   |   56 |
+--------+------+
4 rows in set (0.00 sec)

2）以ClassID為分組依據(jù)，顯示每組的平均年齡

MariaDB [db_1]> select class,avg(age) from student group by class;
+-------+----------+
| class | avg(age) |
+-------+----------+
| 1     |  23.5000 |
| 2     |  34.5000 |
+-------+----------+
2 rows in set (0.00 sec)

3）顯示第2題中平均年齡大于30的分組及平均年齡

MariaDB [db_1]> select class,avg(age) from student group by class having avg(age)>30;
+-------+----------+
| class | avg(age) |
+-------+----------+
| 2     |  34.5000 |
+-------+----------+
1 row in set (0.00 sec)

4）顯示以L開頭的名字的同學(xué)的信息

MariaDB [db_1]> select * from student where name like 'l%';
+----+------+-------+-------+------+-------+------+
| id | name | phone | score | sex  | class | age  |
+----+------+-------+-------+------+-------+------+
|  4 | lin  | NULL  |    88 | m    | 2     |   22 |
+----+------+-------+-------+------+-------+------+
1 row in set (0.00 sec)

5）顯示phone非空的同學(xué)的相關(guān)信息

MariaDB [db_1]> select * from student where phone is not null;  
+----+--------+-------+-------+------+-------+------+
| id | name   | phone | score | sex  | class | age  |
+----+--------+-------+-------+------+-------+------+
|  1 | adc    | 110   |    86 | m    | 1     |   21 |
|  5 | wang   | 1000  |    99 | m    | 2     |   33 |
|  6 | 飛哥   | 10000 |    99 | m    | 2     |   56 |
+----+--------+-------+-------+------+-------+------+
3 rows in set (0.01 sec)