## And 3.Hive的安装使用### 3.1.环境准备[scode type="yellow" size="small"]#### 3.1.1.Hive包准备：将准备好的包均放置到hadoop01下的 `/opt/software`目录内统一管理---这里需要使用的Hive包版本为 `apache-hive-3.1.2-bin.tar.gz` 官方下载：[Index of /dist/hive (apache.org)](https://archive.apache.org/dist/hive/) ![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/3694645491.png)mysql包为5.7：`mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar` 官方下载：[MySQL :: MySQL Community Downloads](https://dev.mysql.com/downloads/)![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/4126966633.png)[/scode]#### 3.1.2.计划安装目标这里我将会把mysql安装到hadoop03上，然后将hive安装到另外两台主机上（hadoop01，hadoop02）### 3.2.安装mysql这里安装mysql可以参考之前文章：[post cid="689" cover="" size=""/]#### 3.2.1.分发mysql包将hadoop01的mysql压缩包拷到hadoop03主机上```shell scp -r /opt/software/mysql/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar root@hadoop03:/opt/software/mysql/ ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1766991032.png)到hadoop03主机上检查：![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/2185681748.png)#### 3.2.2.解压mysql的rpm包直接将mysql包解压```shell tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/3523791577.png)#### 3.2.3.检查环境在安装之前先检查一下目标环境是否有已经安装的mysql，避免出错```shell rpm -qa | grep mysql ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/4060731945.png)#### 3.2.4.安装和基础配置没有问题就可以直接安装了，在当前目录下（mysql解压目录）：```shell rpm -ivh mysql-community-*.rpm --nodeps --force ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1122263750.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1101665076.png)启动mysql检查是否正常可用：```shell systemctl start mysqldl ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/2266666158.png)```shell systemctl status mysqld ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/3687470022.png)查看mysql的默认初始密码：```shell grep password /var/log/mysqld.log cat /var/log/mysqld.log |grep password ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/3884259881.png)```shell mysql -uroot -p ## 复杂密码有些字符可能会被shell解析，所以密码等弹出输入符后再输入 +.uuiXtm,7xp ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/359990328.png)修改mysql默认密码，先修改密码策略：```mysql show variables like "password"; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/2034910355.png)提示需要先修改初始密码：```mysql set password for 'root'@'localhost' = 'P@ssword1'; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/492977243.png)重启MySQL：``` mysql> exit Bye [root@hadoop03 mysql]# systemctl restart mysqld ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/3454294234.png)再次登录mysql，使用已经修改的新密码：![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/3010763425.png)查看密码策略：``` show variables like "%password%"; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/3521963899.png)将 `validate_password_length` 和 `validate_password_policy` 记下，这里需要让配置一直有效，需要修改mysqld的配置文件：``` mysql> exit Bye [root@hadoop03 mysql]# vi /etc/my.cnf ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/2687874470.png)重启mysql：```shell systemctl restart mysqld ```进入mysql修改密码：```mysql set password for root@'localhost' = '123456'; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1771417111.png)为了后面方便，我将mysql的root用户的host修改为 `%` 允许任何地址连接登录，可以不修改```mysql update mysql.user set host="%" where user='root' and host="localhost"; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/3526273479.png)#### 3.2.5.为Hive创建元数据存储数据库```mysql create database hive character set latin1; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/2410952189.png)#### 3.2.6.创建数据库用户hive并分配权限```mysql create user "hive"@"%" identified by "123456"; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/2777040909.png)```mysql grant all privileges on hive.* to 'hive'@'%' with grant option; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1726206723.png)```mysql flush privileges; -- 刷新 show grants for hive; -- 查看用户权限 ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1739122687.png)#### 3.2.7.允许从本地查看数据库用户信息```mysql grant all privileges on hive.* to 'hive'@'localhost' identified by '123456'; flush privileges; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1003895059.png)```mysql select host,user,authentication_string from mysql.user; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/3005858338.png)### 3.3.安装Hive#### 3.3.1.分发解压hive安装包##### 3.3.1.1.分发将hive包分发到hadoop02上```shell scp /opt/software/apache-hive-3.1.2-bin.tar.gz root@hadoop02:/opt/software/ ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/4210367871.png)##### 3.3.1.2.解压这边统一将安装放置到 `/opt/module` 目录下1.进入到压缩包目录下：``` cd /opt/software ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/12/148091993.png)2.解压压缩包：```shell tar -zxvf apache-hive-3.1.2-bin.tar.gz -C /opt/module/ ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1266673968.png)3.进入 `module` 检查：``` cp /opt/module ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1475916015.png)#### 3.3.2.配置安装##### 3.3.2.1.配置环境变量```shell vi /etc/profile ```编辑 `profile` 配置文件，添加下方配置：``` ##########hive3.1.2 export HIVE_HOME=/opt/module/apache-hive-3.1.2-bin export HIVE_CONF_DIR=$HIVE_HOME/conf export HCAT_HOME=$HIVE_HOME/hcatalog export PATH=$HIVE_HOME/bin:$PATH ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1146322990.png)source命令让profile文件配置生效：```shell source /etc/profile ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/2320267532.png)##### 3.3.2.2.配置hive###### 3.3.2.2.1.配置hive-env.sh进入hive的 `conf` 目录```shell cd /opt/module/apache-hive-3.1.2-bin/conf/ ```cp复制一份 `hive-env.sh.template` 命名为 `hive-env.sh` ，并且编辑它：```shell cp hive-env.sh.template hive-env.sh vi hive-env.sh ```将hadoop_home路径填入：```shell HADOOP_HOME=/opt/module/hadoop-3.1.4 ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/370528095.png)###### 3.3.2.2.2.配置hive-site.xml不要切换目录，直接继续vi编辑 `hive-site.xml` 文件配置如下（按需修改）：``` javax.jdo.option.ConnectionURL jdbc:mysql://hadoop03:3306/hive?createDatabaseIfNotExist=true javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName hive javax.jdo.option.ConnectionPassword 123456 hive.metastore.warehouse.dir /user/hive/data hive.hbase.snapshot.restoredir /tmp system:java.io.tmpdir /opt/module/apache-hive-3.1.2-bin/iotmp system:user.name hive hive.exec.local.scratchdir ${system:java.io.tmpdir}/${system:user.name} hive.downloaded.resources.dir ${system:java.io.tmpdir}/${hive.session.id}_resources ```###### 3.3.2.2.3.应用hive-log4j2.properties``` cp hive-log4j2.properties.template hive-log4j2.properties ```###### 3.3.2.2.4.应用hive-exec-log4j2.properties``` cp hive-exec-log4j2.properties.template hive-exec-log4j2.properties ```###### 3.3.2.2.5.在HDFS创建hive目录[font color="#ff0000"]保证hadoop集群开启状态，且正常使用[/font]![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1899029227.png)使用hdfs命令为hive创建目录```shell hadoop fs -mkdir -p /user/hive/data ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/2014023578.png)确保路劲已经创建![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/1154068075.png)###### 3.3.2.2.6.拷JDBC驱动包将mysql的JDBC驱动包拷到$HIVE_HOME/libJDBC包在上方mysql官网中可下载，再贴一个：[MySQL ：： 下载 MySQL Connector/J （存档版本）](https://downloads.mysql.com/archives/c-j/)```shell cd $HIVE_HOME/lib wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-5.1.49.tar.gz ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/12/453402469.png)###### 3.3.2.2.7.Hive初始化``` schematool -dbType mysql -initSchema ```##### 3.3.2.3.##### 3.3.2.4.
一带一路金砖赛大数据集群运维管理赛项总决赛赛前练习
# 一带一路金砖赛大数据集群运维管理赛项总决赛赛前练习## SQL数据分析共享单车数据分析（0 / 20分）### 项目背景本项目数据为共享单车使用的数据信息，数据包含2021、2022年共享单车使用的详细数据，数据包含使用的季节、时间、天气、温度、体感温度、是否注册等信息。### 数据说明数据地址：/root/mysql/bicycle.csv（ [bicycle.csv](https://www.zmzaxg.top/usr/uploads/2023/12/1665488629.zip) 下载后重命名去掉 `.zip` ） 数据库：train（自行创建） 数据表：bicycle 基础数据特征： **共享单车数据表**（bicycle）包含日期、季节、是否假日、是否工作日、天气、体感温度、相对湿度、未注册用户、注册用户（datetime、season、holiday、workingday、weather、atemp、humidity、casual、registered）| datetime | season | holiday | workingday | weather | atemp | humidity | casual | registered | | ------------- | ------ | ------- | ---------- | ------- | ------ | -------- | ------ | ---------- | | 2021/1/1 0:00 | 1 | 0 | 0 | 1 | 14.395 | 81 | 3 | 13 |字段说明及建立表格的数据类型如下：| 字段名 | 字段中文名 | 数据类型 | 数据说明 | 示例数据 | | ---------- | ---------- | ------------- | --------------------------------------------------------------------------------------------------------------------------------------- | ------------- | | datetime | 日期 | varchar(255) | 共享单车使用的时间段数据，数据为：日期 + 时间（小时:分钟）。注意：时间最小颗粒度为小时 | 2021/1/1 0:00 | | season | 季节 | varchar(255) | 共享单车使用的季节数据，1：春季，2：夏季，3：秋季，4：冬季 | 1 | | holiday | 是否假日 | varchar(255） | 标识当前日期是否为被视为假日，其中1表示为假日，0表示为非假日。注意：假日不包含正常休假的周末 | 0 | | workingday | 是否工作日 | varchar(255) | 表示当前日期既不是周末也不是节假日，其中0表示非工作日、1表示工作日 | 0 | | weather | 天气 | varchar(255) | 表示当前日期的天气，1、晴天，2、雾/多云，3、小雪，4、大雨/雷暴 | 1 | | atemp | 体感温度 | double | 表示当前日期的体感温度，温度的数据范围为1-45摄氏度。注意：对温度字段进行分析时，需要对温度字段进行分段，具体分为[0-20)、[20-30)、30以上 | 14.395 | | humidity | 相对湿度 | int | 表示当前日期的相对湿度，湿度的范围为0-100。注意：在分析时需要对对湿度进行分段，具体分为[0-30)、[30-60)、[60-80)、80以上 | 81 | | casual | 未注册用户 | int | 未注册用户的使用人数，也表示该时间段的使用次数。 | 3 | | registered | 注册用户 | int | 注册用户的使用人数，也表示该时间段的使用次数。 | 13 |### 数据处理#### 1、数据字段数据集中已经给出对应的字段，这里无需再做修改。#### 2、时间日期列处理（1）修改字段类型```sql alter table bicycle modify datetime datetime; ```（2）分别提取日期列和时间列```sql # a、添加日期列date alter TABLE bicycle add date date; # 获取time_of_payment中日期数据命令： update bicycle set date = DATE_FORMAT(datetime,'%Y-%m-%d');# b、添加时间列time（自行操作） # 获取time_of_payment中时间数据，格式为'%H:%i:%s' ```### 数据分析``` 1. 计算全量数据，求共享单车年使用次数，结果存入视图table1。 字段要求：（year，sum） 2. 对2022年共享单车使用次数进行分析，求注册用户和未注册用户的年使用数，结果存入视图table2。 字段要求： （year , sum_c , sum_r） 注意：其中sum_c表示未注册用户，sum_r表示注册用户。 3. 进一步对2022年注册用户共享单车使用数量进行分析，计算该年的单月使用人数，结果存入视图table3。 字段要求： (month , sum_r) 4. 对2022年注册用户共享单车数据进行时段分析，计算该年各时间段(24小时制)的单车使用数量（累计值），结果存入视图table4。 字段要求： (hour,sum_r) ```---**在以下操作前需要先创建库，表，将数据导入到表中：**And 1.查看所有数据库```sql show databases; ```![image-20231206192732235.png](https://www.zmzaxg.top/usr/uploads/2023/12/3898537695.png)And 2.创建train数据库并进入```sql create database train; use train; ```![image-20231206193049050.png](https://www.zmzaxg.top/usr/uploads/2023/12/4058953887.png)And 3.创建bicycle数据表并查看是否完整```sql create table bicycle( datetime varchar(255), season varchar(255), holiday varchar(255), workingday varchar(255), weather varchar(255), atemp double, humidity int, casual int, registered int); --创建desc bicycle; --查看 ```![image-20231206193633291.png](https://www.zmzaxg.top/usr/uploads/2023/12/2456640622.png)![image-20231206193707952.png](https://www.zmzaxg.top/usr/uploads/2023/12/3029770297.png)And 4.导入csv数据表内容，并查看数据导入是否出错```sql load data local infile '/root/mysql/bicycle.csv' into TABLE bicycle fields TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES[ROWS]; --导入select * from bicycle limit 10; --查看数据行，检查是否有导入错误 ```![image-20231206194245584.png](https://www.zmzaxg.top/usr/uploads/2023/12/4128781694.png)![image-20231206194535648.png](https://www.zmzaxg.top/usr/uploads/2023/12/4073161203.png)And 5.将datatime字段的类型更改成datetime时间串，并将日期和时间独立区分新添字段```sql alter table bicycle modify datetime datetime; --修改datetime字段类型alter table bicycle add date date; --add添加data字段，类型为date update bicycle set date=DATE_FORMAT(datetime,"%Y-%m-%d"); --将datetime字段中的年月日格式填入到date字段中alter table bicycle add time time; --add添加time字段，类型为time update bicycle set time=DATE_FORMAT(datetime,"%H:%i:%s"); --将datetime字段中的时分秒格式填入到time字段中 ```![image-20231206194949056.png](https://www.zmzaxg.top/usr/uploads/2023/12/2021980362.png)![image-20231206195513179.png](https://www.zmzaxg.top/usr/uploads/2023/12/3707789156.png)这里再 `select`检查一下表内数据是否正确完整![image-20231206195920462.png](https://www.zmzaxg.top/usr/uploads/2023/12/1972029209.png)---考核条件如下：\1. 环境已经安装mysql8，自行开启数据库服务、创建数据库train，创建数据表bicycle,导入数据，计算共享单车年使用次数，结果存入视图table1 。（0.00 / 5分）操作环境: mysql> 这里题目要求字段 `year`和 `sum`，其中sum可以通过最上方中的字段说明中了解为 `casual`和 `registered`字段相加的值，以年数量排列： > > ```sql > create view table1 as(select year(date) year,sum(casual)+sum(registered) sum from bicycle group by year(date)); > > select * from table1; > ``` > > ![image-20231206200929627.png](https://www.zmzaxg.top/usr/uploads/2023/12/405197777.png)\2. 对2022年共享单车使用次数进行分析，求注册用户和未注册用户的年使用数，结果存入视图table2。（0 / 5分）操作环境: mysql> 字段要求： （year , sum_c , sum_r）要创建的视图内容为2022年的数据 > > ```sql > create view table2 as(select year(date) year, sum(casual) sum_c, sum(registered) sum_r from bicycle where year(date)=2022 group by year(date)); > > select * from table2; > ``` > > ![image-20231206201751773.png](https://www.zmzaxg.top/usr/uploads/2023/12/2719925359.png)\3. 进一步对2022年注册用户共享单车使用数量进行分析，计算该年的单月使用人数，结果存入视图table3。（0 / 5分）操作环境: mysql> 字段要求： (month , sum_r) 同样时2022年的数据，以单月分析 > > ```sql > create view table3 as(select month(date) month,sum(registered) sum_r from bicycle where year(date)=2022 group by month(date)); > > select * from table3; > ``` > > ![image-20231206202438656.png](https://www.zmzaxg.top/usr/uploads/2023/12/4059047911.png)\4. 对2022年注册用户共享单车数据进行时段分析，计算该年各时间段(24小时制)的单车使用数量（累计值），结果存入视图table4。（0 / 5分）操作环境: mysql> 这题与第三题一致，改几个字段名就行；字段要求： (hour,sum_r) > > ```sql > create view table4 as(select hour(time) hour, sum(registered) sum_r from bicycle where year(date)=2022 group by hour(time)); > > select * from table4; > ``` > ![image-20231206203221226.png](https://www.zmzaxg.top/usr/uploads/2023/12/1798580417.png)
[goal title="进度条："][item check="true"] 基本的select语句 [/item][item check="true"] 运算符 [/item][item check="true"] 排序与分页 [/item][item check="true"] 多表查询 [/item][item check="false"] 多表查询练习 [/item][item check="true"] 单行函数 [/item][item check="true"] 聚合函数 [/item][item check="true"] 子查询 [/item] [item progress="99%"] 当前进度[/item] [/goal]# 数据> [font color="#2F4F4F"] > > 所使用的数据表均打包成数据库，下方安装文章内提供。 > > [/font] > > ↓↓↓↓↓[post cid="689" cover="" size=""/]> ↑↑↑↑↑ > > 以下所有查询操作记得要先进数据库后操作！！## And 1.基本的select语句[scode type="yellow" size="small"]### And 1.1.【题目】1. 查询员工12个月的工资总和，并起别名为ANNUAL SALARY 2. 查询employees表中去除重复的job_id以后的数据 3. 查询工资大于12000的员工姓名和工资 4. 查询员工号为176的员工的姓名和部门号 5. 显示表 departments 的结构，并查询其中的全部数据[/scode]#### And 1.1.1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY```mysql select employee_id,last_name,salary*12 "ANNUAL SALARY" from employees; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3032310783.png)#### And 1.1.2.查询employees表中去除重复的job_id以后的数据```mysql select distinct job_id from employees; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1357916311.png)#### And 1.1.3.查询工资大于12000的员工姓名和工资```mysql select last_name,salary from employees where salary>12000; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2195429631.png)#### And 1.1.4.查询员工号为176的员工的姓名和部门号```mysql select last_name,department_id from employees where employee_id=176; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/350459894.png)#### And 1.1.5.显示表 departments 的结构，并查询其中的全部数据```mysql desc departments; select * from departments; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3118930042.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3405628445.png)## And 2.运算符[scode type="green" size="small"]### And 2.1.【题目】1. 选择工资不在5000到12000的员工的姓名和工资 2. 选择在20或50号部门工作的员工姓名和部门号 3. 选择公司中没有管理者的员工姓名及job_id 4. 选择公司中有奖金的员工姓名，工资和奖金级别 5. 选择员工姓名的第三个字母是a的员工姓名 6. 选择姓名中有字母a和k的员工姓名 7. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息 8. 显示出表 employees 部门编号在 80-100 之间的姓名、工种 9. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id[/scode]#### And 2.1.1.选择工资不在5000到12000的员工的姓名和工资```mysql select last_name,salary from employees where salary12000; select last_name,salary from employees where salary not between 5000 and 12000; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2899378446.png)#### And 2.1.2.选择在20或50号部门工作的员工姓名和部门号```mysql select last_name,department_id from employees where department_id=20 or department_id=50; select last_name,department_id from employees where department_id in(20,50); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3747958796.png)#### And 2.1.3.选择公司中没有管理者的员工姓名及job_id```mysql select last_name,job_id from employees where manager_id is null; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/183040953.png)#### And 2.1.4.选择公司中有奖金的员工姓名，工资和奖金级别```mysql select last_name,salary,commission_pct from employees where commission_pct is not null; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/713569480.png)#### And 2.1.5.选择员工姓名的第三个字母是a的员工姓名```mysql select last_name from employees where last_name like "__a%"; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1206943420.png)#### And 2.1.6.选择姓名中有字母a和k的员工姓名```mysql select last_name from employees where last_name like '%a%k%' or last_name like '%k%a%'; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/907423179.png)#### And 2.1.7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息```mysql select employee_id,first_name,last_name from employees where first_name like "%e"; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3618263521.png)#### And 2.1.8.显示出表 employees 部门编号在 80-100 之间的姓名、工种```mysql select last_name,job_id from employees where department_id between 80 and 100; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/589959446.png)#### And 2.1.9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id```mysql select last_name,salary from employees where manager_id in(100,101,110); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3884173079.png)## And 3.排序与分页[scode type="red" size="small"]### And 3.1.【题目】1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示 2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据 3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序[/scode]#### And 3.1.1.查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示```mysql select last_name,department_id,salary*12 annual_sal from employees order by annual_sal desc, last_name asc; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/558340776.png)#### And 3.1.2.选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据```mysql select last_name,salary from employees where salary not between 8000 and 17000 order by salary desc limit 20,20; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2088950518.png)#### And 3.1.3.查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序```mysql select last_name,email,department_id from employees where email like '%e%' order by LENGTH(email) desc, department_id asc; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/433109866.png)## And 4.多表查询[scode type="share" size="small"]### And 4.1.【题目】1. 显示所有员工的姓名，部门号和部门名称。 2. 查询90号部门员工的job_id和90号部门的location_id 3. 选择所有有奖金的员工的 last_name , department_name , location_id , city 4. 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 5. 查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’ 6. 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employees Emp# manager Mgr#kochhar 101 king 100 7. 查询哪些部门没有员工 8. 查询哪个城市没有部门 9. 查询部门名为 Sales 或 IT 的员工信息[/scode]#### And 4.1.1.显示所有员工的姓名，部门号和部门名称。```mysql select last_name, e.department_id, department_name from employees e left outer join departments s d ON e.`department_id` = d.`department_id`; ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/770993547.png)#### And 4.1.2.查询90号部门员工的job_id和90号部门的location_id```mysql select job_id,location_id from employees e join departments d ON e.`department_id`=d.`departmentnt_id` where e.`department_id`=90; select job_id,location_id from employees e,departments d where e.`department_id`=d.`department__id` AND e.`department_id`=90; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1764189965.png)#### And 4.1.3.选择所有有奖金的员工的 last_name , department_name , location_id , city```mysql select last_name,department_name,d.location_id,city from employees e LEFT outer Join departmentts d ON e.`department_id`=d.`department_id` LEFT outer Join locations l ON d.`location_id`=l.`locationn_id` where e.`commission_pct` is not NULL; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/357780860.png)#### And 4.1.4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name```mysql select last_name,job_id,e.department_id,d.department_name from employees e LEFT outer Join depaartments d ON e.`department_id`=d.`department_id` LEFT outer Join locations l ON d.`location_id`=l.`loocation_id` where city='Toronto'; select last_name,job_id,e.department_id,department_name from employees e,departments d,locations l where e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` AND l.city='Torontoo'; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3549763243.png)#### And 4.1.5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’```mysql ##同上 select department_name,street_address,last_name,job_id,salary from employees e,departments d,locations l where e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` And d.`departtment_name`='Executive'; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3828861028.png)#### And 4.1.6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式\employees|Emp#|manager|Mgr#\\kochhar|101|king|100\| employees | Emp# | manager | Mgr# | | :-------: | :--: | :-----: | :--: | | kochhar | 101 | king | 100 |```mysql select emp.last_name employees,emp.employee_id "Emp#",mgr.last_name manager,mgr.employee_id "Mgr#" from employees emp LEFT outer Join employees mgr ON mgr.`employee_id`=emp.`manager_id`; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/670175113.png)#### And 4.1.7.查询哪些部门没有员工```mysql select d.department_id,d.department_name from departments d LEFT outer Join employees e ON e.`departmedepartment_id`=d.`department_id` where e.`department_id` is null; select d.department_id,d.department_name from departments d where not EXISTS( select * from employees e where e.`department_id`=d.`department_id` ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2292640495.png)#### And 4.1.8.查询哪个城市没有部门```mysql select l.location_id,l.city from locations l LEFT outer Join departments d ON l.`location_id`=d.`location_id` where d.`location_id` is null; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3392028600.png)#### And 4.1.9.查询部门名为 Sales 或 IT 的员工信息```mysql select employee_id,last_name,department_name from employees e,departments d where e.`department_id`=d.`department_id` AND d.`department_name` in ('Sales','IT'); select employee_id,last_name,department_name from employees e LEFT outer Join departments d ON e.`department_id`=d.`department_id` where d.`department_name` in ('Sales','IT'); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3783370221.png)### And 4.2.【练习】暂定。## And 5.单行函数### And 5.1.【题目】[scode type="red" size="small"]1. 显示系统时间(注：日期+时间) 2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary） 3. 将员工的姓名按首字母排序，并写出姓名的长度（length） 4. 查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT 5. 查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序 6. 查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空 7. 查询公司中入职超过10000天的员工姓名、入职时间 8. 做一个查询，产生下面的结果：` earns monthly but wants `| Dream Salary | | :-----------------------------------------: | | **King earns 24000 monthly but wants 7200** | 9. 使用case-when，按照下面的条件：job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E产生下面的结果：| Last_name | job_id | Grade | | :-------: | :-----: | :---: | | King | AD_PRES | A |[/scode]#### And 5.1.1.显示系统时间(注：日期+时间)```mysql SELECT NOW() FROM DUAL; ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/1849259266.png)#### And 5.1.2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）```mysql SELECT employee_id, last_name, salary, salary * 1.2 "new salary" FROM employees; ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/4157876955.png)#### And 5.1.3.将员工的姓名按首字母排序，并写出姓名的长度（length）```mysql SELECT last_name, LENGTH(last_name) FROM employees ORDER BY last_name DESC; ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/2909023181.png)#### And 5.1.4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT```mysql SELECT CONCAT(employee_id, ',' , last_name , ',', salary) OUT_PUT FROM employees; ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/1515430244.png)#### And 5.1.5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序```mysql SELECT DATEDIFF(SYSDATE(), hire_date) / 365 worked_years, DATEDIFF(SYSDATE(),hire_date) worked_days FROM employees ORDER BY worked_years DESC; ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/318968098.png)#### And 5.1.6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空```mysql SELECT last_name, hire_date, department_id FROM employees #WHERE hire_date >= '1997-01-01' #WHERE hire_date >= STR_TO_DATE('1997-01-01', '%Y-%m-%d') WHERE DATE_FORMAT(hire_date,'%Y') >= '1997' AND department_id IN (80, 90, 110) AND commission_pct IS NOT NULL ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/4111710989.png)#### And 5.1.7.查询公司中入职超过10000天的员工姓名、入职时间```mysql SELECT last_name,hire_date FROM employees #WHERE TO_DAYS(NOW()) - to_days(hire_date) > 10000; WHERE DATEDIFF(NOW(),hire_date) > 10000; ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/2080015661.png)#### And 5.1.8.做一个查询，产生下面的结果：```mysql SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ', TRUNCATE(salary * 3, 0)) "Dream Salary" FROM employees; ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/1432988400.png)#### And 5.1.9.使用case-when，按照下面的条件：```mysql SELECT last_name Last_name, job_id Job_id, CASE job_id WHEN 'AD_PRES' THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG' THEN 'C' WHEN 'SA_REP' THEN 'D' WHEN 'ST_CLERK' THEN 'E' ELSE 'F' END "grade" FROM employees; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1533974532.png)## And 6.聚合函数### And 6.1.【题目】[scode type="share" size="small"]1. where子句可否使用组函数进行过滤? 2. 查询公司员工工资的最大值，最小值，平均值，总和 3. 查询各job_id的员工工资的最大值，最小值，平均值，总和 4. 选择具有各个job_id的员工人数 5. 查询员工最高工资和最低工资的差距（DIFFERENCE） 6. 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内 7. 查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序 8. 查询每个工种、每个部门的部门名、工种名和最低工资[/scode]#### And 6.1.1.where子句可否使用组函数进行过滤?不能#### And 6.1.2.查询公司员工工资的最大值，最小值，平均值，总和```mysql select max(salary),min(salary),avg(salary),sum(salary) from employees; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/193891628.png)#### And 6.1.3.查询各job_id的员工工资的最大值，最小值，平均值，总和```mysql select job_id,max(salary),min(salary),avg(salary),sum(salary) from employees grooup by job_id; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2748510669.png)#### And 6.1.4.选择具有各个job_id的员工人数```mysql select job_id,COUNT(*) from employees group by job_id; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3616586354.png)#### And 6.1.5.查询员工最高工资和最低工资的差距（DIFFERENCE）```mysql select max(salary),min(salary),max(salary)-min(salary) DIFFERENCE from employees; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2205988719.png)#### And 6.1.6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内```mysql select manager_id,min(salary) from employees where manager_id is not null group by manager_id having min(salary)>6000; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3194676263.png)#### And 6.1.7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序```mysql select department_name,location_id,count(employee_id),avg(salary) from employees e right Join departments d ON e.`department_id`=d.`department_id` group by departmentt_name,location_id order by avg(salary) desc; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/588261607.png)#### And 6.1.8.查询每个工种、每个部门的部门名、工种名和最低工资```mysql select department_name,job_id,min(salary) from employees e RIGHT outer Join deparartments d ON e.`department_id`=d.`department_id` group by department_name,job_id; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2375870204.png)## And 7.子查询### And 7.1.【题目】[scode type="blue" size="small"]1. 查询和Zlotkey相同部门的员工姓名和工资 2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。 3. 选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary 4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 5. 查询在部门的location_id为1700的部门工作的员工的员工号 6. 查询管理者是King的员工姓名和工资 7. 查询工资最低的员工信息: last_name, salary 8. 查询平均工资最低的部门信息 9. 查询平均工资最低的部门信息和该部门的平均工资（相关子查询） 10. 查询平均工资最高的 job 信息 11. 查询平均工资高于公司平均工资的部门有哪些? 12. 查询出公司中所有 manager 的详细信息 13. 各个部门中 最高工资中最低的那个部门的 最低工资是多少? 14. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary 15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号 16. 选择所有没有管理者的员工的last_name 17. 查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan' 18. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询） 19. 查询每个部门下的部门人数大于 5 的部门名称（相关子查询） 20. 查询每个国家下的部门个数大于 2 的国家编号（相关子查询）[/scode]#### And 7.1.1.查询和Zlotkey相同部门的员工姓名和工资```mysql select last_name,salary from employees where department_id=( select department_id from employees where last_name="Zlotkey" ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1524181237.png)#### And 7.1.2.查询工资比公司平均工资高的员工的员工号，姓名和工资。```mysql select employee_id,last_name,salary from employees where salary>( select avg(salary) from employees ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2241915199.png)#### And 7.1.3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary```mysql select last_name,job_id,salary from employees where salary>ALL( select salary from employees where job_id="SA_MAN" ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2291180080.png)#### And 7.1.4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名```mysql select employee_id,last_name from employees where department_id=ANY( select DISTINCT department_id from employees where last_name like "%u%" ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3300945559.png)#### And 7.1.5.查询在部门的location_id为1700的部门工作的员工的员工号```mysql select employee_id from employees where department_id in ( select department_id from departments where location_id=1700 ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/704483308.png)#### And 7.1.6.查询管理者是King的员工姓名和工资```mysql select last_name,salary from employees where manager_id in ( select employee_id from employees where last_name="King" ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1952776253.png)#### And 7.1.7.查询工资最低的员工信息: last_name, salary```mysql select last_name,salary from employees where salary=( select min(salary) from employees ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/962196294.png)#### And 7.1.8.查询平均工资最低的部门信息```mysql SELECT d.* FROM departments d,( SELECT department_id,AVG(salary) avg_S FROM employees GROUP BY department_id ORDER BY avg_S ASC ) dd WHERE dd.department_id=d.department_id LIMIT 0,1; ##嵌入语句使用 group by 将平均工资按照的department_id(部门id)聚合分组，以部门的平均工资asc降序排列，使用别名表将departments表依照嵌入查询的别名表dd排序，limit 0,1 取升序中的第一行数据。 ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1774716662.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/2799904344.png)#### And 7.1.9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）```mysql SELECT d.*,dd.avg_S FROM departments d,( SELECT department_id,AVG(salary) avg_S FROM employees GROUP BY department_id ORDER BY avg_S ASC ) dd WHERE dd.department_id=d.department_id LIMIT 0,1; ##在上方的语句d.*后加上dd别名表的平均工资字段列 ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1662963663.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/3625454602.png)#### And 7.1.10.查询平均工资最高的 job 信息```mysql SELECT j.* FROM jobs j,( SELECT job_id, AVG(salary) FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC ) e WHERE j.job_id=e.job_id LIMIT 0,1; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/735596306.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1988763063.png)#### And 7.1.11.查询平均工资高于公司平均工资的部门有哪些?```mysql SELECT department_id FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING AVG(salary)>( SELECT AVG(salary) FROM employees ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1032144231.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/3846685696.png)#### And 7.1.12.查询出公司中所有 manager 的详细信息```mysql SELECT employee_id, last_name, salary FROM employees WHERE employee_id IN(SELECT DISTINCT manager_id FROM employees);SELECT e.employee_id, e.last_name, e.salary FROM employees e JOIN employees ee WHERE e.employee_id=ee.manager_id;SELECT employee_id, last_name, salary FROM employees e1 WHERE EXISTS ( SELECT * FROM employees e2 WHERE e2.manager_id = e1.employee_id); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1071161282.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/3128694530.png)#### And 7.1.13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?```mysql SELECT MIN(salary) FROM employees WHERE department_id=( SELECT department_id FROM employees GROUP BY department_id HAVING MAX(salary)=( SELECT MIN(max_S) FROM ( SELECT MAX(salary) max_S FROM employees GROUP BY department_id ORDER BY department_id ) tabale_max_S ) ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/866319198.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/130168591.png)#### And 7.1.14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary```mysql SELECT last_name, department_id, email, salary FROM employees WHERE employee_id IN( SELECT DISTINCT manager_id FROM employees e,( SELECT department_id, AVG(salary) avg_S FROM employees GROUP BY department_id ORDER BY avg_S DESC LIMIT 0,1 ) ee WHERE e.department_id=ee.department_id ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/2957497255.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1218401464.png)#### And 7.1.15.查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号```mysql SELECT department_id FROM departments WHERE department_id NOT IN ( SELECT department_id FROM employees WHERE job_id="ST_CLERK" ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1126238324.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/2195220076.png)#### And 7.1.16.选择所有没有管理者的员工的last_name```mysql SELECT last_name FROM employees e WHERE NOT EXISTS( SELECT * FROM employees ee WHERE e.manager_id=ee.employee_id ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/353229241.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/1288043202.png)#### And 7.1.17.查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'```mysql SELECT employee_id, last_name, hire_date, salary FROM employees WHERE manager_id = ( SELECT employee_id FROM employees WHERE last_name="De Haan" ); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/839109191.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/3068180868.png)#### And 7.1.18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）```mysql SELECT employee_id, last_name, salary FROM employees e,( SELECT department_id,AVG(salary) avg_S FROM employees GROUP BY department_id ) ee WHERE e.department_id=ee.department_id AND e.salary>ee.avg_S; ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/921863779.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/11/2412995424.png)#### And 7.1.19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）```mysql SELECT department_name, department_id FROM departments d WHERE 5
## And 1.获取RPM包自建服务器暂时拆了，不提供自建备份下载，自行前往[mysql.com](https://mysql.com)下载压缩包解压，贴个图：![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1571074702.png)windows的就安装程序，下载好后双击安装完事，自行研究。解压后的包：![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/833338685.png)将文件上传到linux内![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/3296316746.png)## And 2.安装mysql以上，可以看到有10个rpm包，安装实际上并不需要全部使用上，当然可以直接使用`rpm -ivh ./*.rpm --nodeps --force`一次性将rpm包全都安装上：![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3016935075.png)单独安装所需要的包的话就得单个顺序的安装，如下四个包即可：```shell rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3637258621.png)这里安装时候报错`from install of mysql-community-common-5.7.28-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.68-1.el7.x86_64`，需要先卸载一下`mariadb-libs`组件：这里可以使用`rpm -qa`或者`yum list installed`来查看一下自带相关组件：### And 2.1.卸载组件```shell yum list installed |grep mariadb rpm -qa |grep mariadb ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2197305135.png)可以使用`yum remove`或者`rpm -ev`来卸载：```shell yum remove mariadb-libs.x86_64 -y ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/169612359.png)查看一下是否卸载完成：![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2967431592.png)```shell rpm -ev mariadb-libs-5.5.68-1.el7.x86_64 ```这里rpm卸载时候提示`postfix`被需要：`错误：依赖检测失败： libmysqlclient.so.18()(64bit) 被 (已安裝) postfix-2:2.10.1-9.el7.x86_64 需要 libmysqlclient.so.18(libmysqlclient_18)(64bit) 被 (已安裝) postfix-2:2.10.1-9.el7.x86_64 需要`。![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1396236274.png)这里就先卸载一下`postfix`再卸载`mariadb`：```shell rpm -ev postfix-2:2.10.1-9.el7.x86_64 rpm -ev mariadb-libs-5.5.68-1.el7.x86_64 ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2585157031.png)### And 2.2.继续单独顺序安装```shell rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/528129952.png)```shell rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/4160872411.png)```shell rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2617316045.png)```shell rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2039559602.png)安装完成。。。## And 3.启动、修改、远程mysql安装好后需要先启动一次mysqld服务后才能有默认密码可以先启动添加到开机自启后再重设一下mysql的root用户密码。### And 3.1.添加到开机```shell systemctl status mysqld ```可以看到mysql处于关闭状态。![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/295397817.png)给加一下开机自启再开一下：```shell systemctl enable mysqld systemctl start mysqld systemctl status mysqld ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2891000499.png)### And 3.2.mysql修改这里将修改mysql的root用户的默认密码，并且修改其可以远程登录访问。以上mysq启动完成后，我们`cat`出mysql的日志文件可以看到mysq安装的默认密码。```shell cat /var/log/mysqld.log ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1511386721.png)直接全打印出来会有很多内容，可以使用grep检索一下内容：```shell grep password /var/log/mysqld.log ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/618482408.png)由于默认密码有特殊字符会被shell终端解析，所以-p后不输入密码，提示密码输入处输入```shell mysql -uroot -p ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3661724998.png)登录mysql后先修改一下root的密码：```mysql set password for root@localhost = PASSWORD('123456'); ```这里报错说密码安全策略不符合![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3469603577.png)先查看一下默认的密码策略：```mysql show variables like "validate_password%"; ```提示需要用`alter uaer`重设一下密码![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1359903741.png)```mysql alter user root@localhost identified by '123456'; ```同样提示需要遵循密码策略![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2209657010.png)那就先设个复杂密码，后面再修改就行```mysql set password for root@localhost = PASSWORD('P@ssword1.'); ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2006866863.png)修改完密码后回到查看密码策略：```mysql show variables like 'validate_password%'; ```可以看到密码策略的设置项，我们要修改的有密码等级`policy`和密码长度`length`![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1597115971.png)将上面对应的两行记下：`validate_password_policy``validate_password_length`exit退出mysql回到shell终端，编辑`my.cnf`配置：```shell vi /etc/my.cnf ```在“[mysqld]”下加入上面记录的两行，并添加如下图的参数：![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/4262054968.png)保存退出，重启一下mysql服务```shell service mysqld restart ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3743439224.png)再次登录到mysql查看密码策略设置，可以看到配置生效了：![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3689320394.png)再次修改密码为：“123456”：![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1487380586.png)查看一下当前mysql里有哪几个用户：```mysql select host,user,password from mysql.user; ```这里会报没有‘password’列，是因为mysql5.7开始就不再使用`password`来存密码md5，改用`authentication_string`来存储了：```mysql select host,user,authentication_string from mysql.user; ```可以看到现在的root用户只能本地登录![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1837465836.png)将root的host修改为`%`允许其可以在任意位置登录使用：```mysql update mysql.user set host="%" where user="root"; ```看到root的host变成了`%`即可。![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1074937024.png)exit退出mysql，重启一下mysql服务![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1437653136.png)检查一下防火墙有没有关闭```shell systemctl status firewalld.service ```开着防火墙，stop给关一下。![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3766489086.png)```shell systemctl stop firewalld systemctl status firewalld ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3760492374.png)## And 3.3.远程连接验证以上完成后到其他可以ping通其的，带有mysql（客户端）环境的设备上连接测试，如果没有环境，可直接到And 4.2.1安装图形化工具连接测试：查看mysql服务器的ip：![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/1095747746.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3512540369.png)```cmd mysql -uroot -p123456 -h 192.168.23.228 ```远程连接成功即可。![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/966337609.png)## And 4.导入数据库sql文件### And 4.1.下载sql文件↓↓下载下方zip文件，直接重命名去除`.zip`↓↓→[重命名去掉 ”.zip“ 使用](https://www.zmzaxg.top/usr/uploads/2023/10/2571417792.zip)←### And 4.2.导入sql数据文件这里可以直接使用shell命令导入sql文件到mysql，但这里将使用sql工具导入与使用#### And 4.2.1.安装SQLyog程序自建服务器暂时拆了，不提供，自行前往官网安装#### And 4.2.2.连接mysql在SQLyog首页![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2178984299.png)在弹出的连接窗口中填入信息ip和密码，测试连接![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3773409679.png)没有问题就直接连接，可以保存方便下次连接![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/3256053856.png)#### And 4.2.3.导入sql包使用SQLyog连接上mysql后，在数据库过滤器中右击![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1988202677.png)在弹出窗口选择下载保存好上方sql文件的路径执行操作![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/4180274910.png)![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/10/1473025010.png)完成后再次在数据库过滤器右击刷新对象![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/2968165776.png)可以看见多了一个数据库：![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/879055475.png)依次选择展开就可以看到数据表数据表信息和数据了：![image.png](https://www.zmzaxg.top/usr/uploads/2023/10/1068768619.png)
## And 1.引文由于上一篇`Mysql相关`文章出现的mysql安装和使用的问题，为了不浪费太多的时间和文章阅读视界，就没有将编译安装mysql写进去，这里就另起一篇编译安装步骤的。## And 2.正文我这边准备了一台刚刚最小化安装的centos，只安装了net-tools方便使用，mysql都是没安装的，当然如果你mysql坏了也建议将mysql相关文件都清理一下再来操作。![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/2583262382.png)### And 2.1.安装环境#### And 2.1.1.安装开发者工具组件```shell yum group list ## 列出组件列表 yum group install "开发工具" 或者用：yum group install "Development Tools" ## 安装开发者工具组件如果不想手动选择安装项可以直接在最后面加参数“-y”来默认选yes ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/2899410049.png)等待开发工具组件安装完成![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/427526874.png)#### And 2.1.2.安装必要依赖包```shell yum -y install gcc gcc-c++ cmake ncurses-devel autoconf bison perl perl-devel openssl openssl-devel ```等待安装完成![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/1596968512.png)### And 2.2.开始编译安装#### And 2.2.1.创建Mysql目录及用户组与用户创建目录```shell mkdir /usr/local/mysql mkdir /usr/local/mysql/data ```创建用户组和用户```shell groupadd mysql ## 创建用户组 useradd -r -g mysql mysql ## 创建用户，-r定为系统账号，-g用为指定用户组；用户组为mysql，用户名为mysql，没有密码 ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/3677354943.png)#### And 2.2.2.下载解压boost包boost库可以来提高编译效率先在/usr/local创建文件夹```shell mkdir -p /usr/local/boost ## -p用于可递进创建，防止boost之前的文件夹不存在时无法创建 cd /usr/local/boost wget https://nchc.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz ## 下载boost包，如果失效可以去其他源找找 tar -zxvf boost_1_77_0.tar.gz ## 解压boost包，-z操作gzip格式文件，-x解压操作，-v显示过程，-f显示名称 ```#### And 2.2.3.下载安装Mysql源码包我们到mysql官网：[MySQL](https://www.mysql.com/)![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/05/336056190.png)下拉找到社区版进入![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/05/2248890523.png)![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/05/9775966.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/3415969306.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/1290310200.png)进入下载页面拉到后面有一个直接下载链接，右击复制![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/05/2767062829.png)然后到你的一个下载路径里`~注意！！ 这里避个坑：我们虚拟机配置就那样，不配装8.0的mysql，这里我就慢慢磨了好久，上面和下方有些图片都可以看到有好些带mysql8，因为图片基本一样就没有修改了，重要的命令啥的都已经修正~````shell cd /下载/mysql/ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.42.tar.gz ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/05/2932620002.png)下载完后解压一下```shell tar -zxvf mysql-5.7.42.tar.gz ```![请输入图片描述](https://www.zmzaxg.top/usr/uploads/2023/05/2855384109.png)再使用mv将文件夹移动到其他保存的路径上，随之进入这个文件夹里```shell mv /下载/mysql/mysql-5.7.42 /usr/local/mysql cd /usr/local/mysql/mysql-5.7.42/ ```生成`Makefile````shell ## 这里注意一下 ## -DWITH_BOOST=[你上面boost包解压后的路径] 这里使用1.15版本的boost即可，其他版本可能会太低或者太高不支持 ## -DFORCE_INSOURCE_BUILD=1 这里是强制执行，可以先去掉这行，如果有提示需要再添加 ## -DWITH_SYSTEMD=1 这条是来控制是否安装mysqld_safe ### 其他详细可以用 sudo cmake .. -LH 查看cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ -DSYSCONFDIR=/etc \ -DSYSTEMD_PID_DIR=/usr/local/mysql \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DWITH_BOOST=/usr/local/boost ## -DWITH_SYSTEMD=1 \ ## -DFORCE_INSOURCE_BUILD=1### ------------------------------------------- ### ## CMAKE参数说明： 注意： \后面不能有空格 ## -DCMAKE_INSTALL_PREFIX=/usr/local/mysql //安装路径 ## -DMYSQL_DATADIR=/usr/local/mysql/data //数据文件存放位置 ## -DSYSCONFDIR=/etc //my.cnf路径 ## -DWITH_MYISAM_STORAGE_ENGINE=1 //支持MyIASM引擎 ## -DWITH_INNOBASE_STORAGE_ENGINE=1 //支持InnoDB引擎 ## -DWITH_MEMORY_STORAGE_ENGINE=1 //支持Memory引擎 ## -DWITH_READLINE=1 //快捷键功能(我没用过) ## -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock //连接数据库socket路径 ## -DMYSQL_TCP_PORT=3306 //端口 ## -DENABLED_LOCAL_INFILE=1 //允许从本地导入数据 ## -DWITH_PARTITION_STORAGE_ENGINE=1 //安装支持数据库分区 ## -DEXTRA_CHARSETS=all //安装所有的字符集 ## -DDEFAULT_CHARSET=utf8 //默认字符 ## -DDEFAULT_COLLATION=utf8_general_ci //设置默认校对规则 ### ------------------------------------------- ### ```在这里生成步骤上，可能会有缺少部分环境，可以根据提示内容配置一下，直到配置没有问题：![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/2934444842.png)执行完毕就可以继续使用`make`编译安装```shell ## ############################################# ## ## ！！注意，编译过程将是漫长的，耐心等待即可！！ ## ## ############################################# ##make ## 编译make test ## 测试make install ## 安装 ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/2325893280.png)![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/692245842.png)编译完成测试全都通过，然后就直接`make install`来安装![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/3561236792.png)来检查一下mysql的文件![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/3596848569.png)给里面添加一个data目录：mkdir data再移动一下源文件：~~~shell mv ./mysql-5.7.42 /usr/local/mysql ~~~这里再给文件权限分配一下```shell chown -R mysql:mysql /usr/local/mysql chmod -R 755 /usr/local/mysql ## 上面已经提前创建好了用户组和用户，这里直接给权限就行cd /usr/local/mysql/ chown -R mysql:mysql . chown -R mysql:mysql /usr/local/mysql/data ## 进入mysql目录内再分配一下 ```这里我们先进入到mysql的bin目录~~~shell cd /usr/local/mysql/bin/ ~~~然后保证$file/mysql/data目录内容是空的，使用下面这条命令```shell ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql ```![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/3468668618.png)cd /usr/local/mysql/ chown -R mysql:mysql . chown -R mysql:mysql /usr/local/mysql/data进入mysql目录内再分配一下#### And 2.2.4.将Mysql添加到系统服务然后再创建一个`my-default.cnf`文件，有可能你自带是有的，我这里是没有````shell vi support-files/my-default.cnf## 内容如下，如果有不同路径就改改 [client] port = 3306 socket = /tmp/mysql.sock[mysqld] port = 3306 socket = /tmp/mysql.sock user = mysqlbasedir = /usr/local/mysql datadir = /usr/local/mysql/data pid-file = /usr/local/mysql/mysql.pidlog_error = /usr/local/mysql/mysql-error.log slow_query_log = 1 long_query_time = 1skip-external-locking key_buffer_size = 32M max_allowed_packet = 1024M table_open_cache = 128 sort_buffer_size = 768K net_buffer_length = 8K read_buffer_size = 768K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M thread_cache_size = 16 query_cache_size = 16M tmp_table_size = 32M performance_schema_max_table_instances = 1000explicit_defaults_for_timestamp = true #skip-networking max_connections = 500 max_connect_errors = 100 open_files_limit = 65535log_bin=mysql-bin binlog_format=mixed server_id = 232 expire_logs_days = 10 early-plugin-load = ""default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_buffer_pool_size = 128M innodb_log_file_size = 32M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50[mysqldump] quick max_allowed_packet = 16M[mysql] no-auto-rehash[myisamchk] key_buffer_size = 32M sort_buffer_size = 768K read_buffer = 2M write_buffer = 2M ````使用cp命令，拷贝替换到etc下的my.cnf```shell cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf ```检查一下是否成功![image.png](https://www.zmzaxg.top/usr/uploads/2023/05/3713097943.png)#### And 2.2.5.配置Mysql切换到bin目录```shell cd /usr/local/mysql/bin/ ```执行mysql~~~shell ./mysql -u root -p ## 登录set password for root@localhost = password('yourpass'); ## 修改密码 ~~~#### And 2.2.6.配置mysql命令可执行和脚本启动```shell #当前修改环境变量更稳定可靠的方式应该是在**/etc/profile.d文件夹内添加新的命令号，耦合性低，便于管理。例如将mysql添加进入环境变量，在该文件夹内新增mysql.sh**文件 export MYSQL_HOME=/usr/local/mysql export PATH=$PATH:$MYSQL_HOME/bincp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql source /etc/profile service mysql start ```