1、使用rpm安裝mysql
安裝環(huán)境
OS: centos 7.5
MYSQL: mysql5.7
- 因為系統(tǒng)自帶的mariadb的mariadb-libs-1:5.5.56-2.el7.x86_64包和mysql沖突瑰排，所以先移除此包
rpm -e mariadb-libs-1:5.5.56-2.el7.x86_64 --nodeps
- 安裝依賴的2個包,有先后順序
yum install mysql-community-common-5.7.26-1.el7.x86_64.rpm
yum install mysql-community-libs-5.7.26-1.el7.x86_64.rpm
- 安裝mysql客戶端工具
yum install mysql-community-client-5.7.26-1.el7.x86_64.rpm
- 安裝mysql server
yum install mysql-community-server-5.7.26-1.el7.x86_64.rpm
- 啟動mysql
systemctl start mysqld.service
- 找出mysql的密碼
cat /var/log/mysqld.log |grep password
- 使用默認密碼進入mysql后贯要，必須修改密碼后才能使用
alter user root@'localhost' identified by '123456';
2、二進制安裝mariadb
- 下載二進制文件椭住，并解壓,建立符號連接
tar xvf mariadb-10.3.15-linux-x86_64.tar.gz -C /usr/local/
ln -sv mariadb-10.3.15-linux-x86_64 mysql
- 建立mysql賬號和組
useradd -r -d /data/mysql mysql
- 建立mysql數(shù)據(jù)庫文件存放的目錄崇渗，并設置好權限
mkdir /data/mysql -p
chown -R mysql.mysql /data/mysql/
- 修改mysql配置文件
[root@localhost my.cnf.d]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
[mysqld_safe]
log-error=/data/mysql/mariadb.log
pid-file=/data/mysql/mariadb.pid
- 創(chuàng)建數(shù)據(jù)庫文件
cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
- 添加啟動服務，并啟動服務
cp ./support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
- 修改PATH變量
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/sql.sh
. /etc/profile.d/sql.sh
- 安全初始化
/user/local/mysql/bin/mysql_secure_installation
3京郑、編譯安裝mariadb
- 建立mysql賬號和組
useradd -r -d /data/mysql mysql
- 建立mysql數(shù)據(jù)庫文件存放的目錄宅广，并設置好權限
mkdir /data/mysql -p
chown -R mysql.mysql /data/mysql/
- 安裝相關依賴包
yum install -y 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
- 解壓源碼包
tar xvf mariadb-10.2.18.tar.gz
- cmake編譯安裝
cd mariadb-10.2.18/
cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/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 -j 8 && make install
- 修改mysql配置文件
[root@localhost my.cnf.d]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
[mysqld_safe]
log-error=/data/mysql/mariadb.log
pid-file=/data/mysql/mariadb.pid
- 生成數(shù)據(jù)庫文件
cd /usr/local/mysql
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
- 準備PATH變量
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
- 添加啟動服務，并啟動服務
cp ./support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
- 運行安全腳本
mysql_secure_installation
4些举、students表的數(shù)據(jù)跟狱，實現(xiàn)下面的需求，寫出sql：
MariaDB [db1]> select * from students;
+—-+——–+——-+——-+——+——-+
| id | name | phone | score | swx | class |
+—-+——–+——-+——-+——+——-+
| 1 | adc | 110 | 86 | m | 1 |
| 2 | zhou | NULL | 80 | f | 1 |
| 3 | fei | NULL | 100 | f | 2 |
| 4 | lin | NULL | 88 | m | 2 |
| 5 | wang | 1000 | 99 | m | 2 |
| 6 | 飛哥 | 10000 | 99 | m | 2 |
+—-+——–+——-+——-+——+——-+
1）在students表中户魏，查詢年齡大于25歲驶臊，且為男性的同學的名字和年齡
select name,score from students where age > 25 and swx='f';
2）以ClassID為分組依據(jù)，顯示每組的平均年齡
select class,avg(age) from students group by class;
3）顯示第2題中平均年齡大于30的分組及平均年齡
select class,avg(age) from students group by class having avg(age) > 30;
4）顯示以L開頭的名字的同學的信息
select * from students where name like 'l%';
