练习题 - 《linux》

1.按照要求在qf的数据库下创建以下两个表qf_user_information和qf_post_information,（要求见图片）
create database qf;

use qf;

create table qf_user_information;

create table qf_post_information;

insert into qf_user_information values(1,’xingdian’,100,18,30000,’teacher’),(2,’diandian’,10ian’,101,40,10000,’jishuzhichi’),(3,’diange’,101,18,8000,’qiantai’),(4,’dianye’,100,17,35000,,’zhuguan’),(5,’xd’,101,26,9000,’qiantai’),(6,’diandiangege’,104,27,9000,null),(7,’diandiangee’,102,30,8000,’hr’);

insert into qf_post_information values(100,’cloud’),(101,’xd’),(102,’hr’),(103,’cw’);

2.查询qf_user_information表中薪资的总和
select sum(qf_salary) from qf_user_information;
+————————+
| sum(qf_salary) |
+————————+
| 109000 |
+————————+
1 row in set (0.00 sec)

4.查询年龄超过20岁以上的所有的员工及对应的部门
select qf_user_information.qf_name,qf_user_information.qf_age,qf_user_information.qf_post_id ,qf_post_information.qf_post from qf_user_information inner join qf_post_information on qf_user_information.qf_post_id=qf_post_information.qf_post_id and qf_age >20;
+——————+————+——————+————-+
| qf_name | qf_age | qf_post_id | qf_post |
+——————+————+——————+————-+
| diandian | 40 | 101 | xd |
| xd | 26 | 101 | xd |
| diandiange | 30 | 102 | hr |
+——————+————+——————+————-+
3 rows in set (0.00 sec)

6.查询qf_user_information表中所有的薪资，并按照升序进行排列
select qf_name, qf_salary,qf_post from qf_user_information,qf_post_information where qf_user_information.qf_post_id=qf_post_information.qf_post_id order by qf_salary asc;
+——————+—————-+————-+
| qf_name | qf_salary | qf_post |
+——————+—————-+————-+
| diandiange | 8000 | hr |
| diange | 8000 | xd |
| xd | 9000 | xd |
| diandian | 10000 | xd |
| xingdian | 30000 | cloud |
| dianye | 35000 | cloud |
+——————+—————-+————-+
6 rows in set (0.00 sec)

7.查询所有部门对应的员工信息，要求没有员工的部门也要显示
select * from qf_user_information left join qf_post_information on qf_user_information.qf_post_id=qf_post_information.qf_post_id ;
+———-+———————+——————+————+—————-+————————+——————+————-+
| qf_id | qf_name | qf_post_id | qf_age | qf_salary | qf_description | qf_post_id | qf_post |
+———-+———————+——————+————+—————-+————————+——————+————-+
| 1 | xingdian | 100 | 18 | 30000 | teacher | 100 | cloud |
| 4 | dianye | 100 | 17 | 35000 | zhuguan | 100 | cloud |
| 2 | diandian | 101 | 40 | 10000 | jishuzhichi | 101 | xd |
| 3 | diange | 101 | 18 | 8000 | qiantai | 101 | xd |
| 5 | xd | 101 | 26 | 9000 | qiantai | 101 | xd |
| 7 | diandiange | 102 | 30 | 8000 | hr | 102 | hr |
| 6 | diandiangege | 104 | 27 | 9000 | NULL | NULL | NULL |
+———-+———————+——————+————+—————-+————————+——————+————-+
7 rows in set (0.00 sec)

8.查询所有员工对应的部门，要求没有部门的员工也要显示
select * from qf_user_information right join qf_post_information on qf_user_information.qf_post_id=qf_post_information.qf_post_id
+———-+——————+——————+————+—————-+————————+——————+————-+
| qf_id | qf_name | qf_post_id | qf_age | qf_salary | qf_description | qf_post_id | qf_post |
+———-+——————+——————+————+—————-+————————+——————+————-+
| 1 | xingdian | 100 | 18 | 30000 | teacher | 100 | cloud |
| 2 | diandian | 101 | 40 | 10000 | jishuzhichi | 101 | xd |
| 3 | diange | 101 | 18 | 8000 | qiantai | 101 | xd |
| 4 | dianye | 100 | 17 | 35000 | zhuguan | 100 | cloud |
| 5 | xd | 101 | 26 | 9000 | qiantai | 101 | xd |
| 7 | diandiange | 102 | 30 | 8000 | hr | 102 | hr |
| NULL | NULL | NULL | NULL | NULL | NULL | 103 | cw |
+———-+——————+——————+————+—————-+————————+——————+————-+
7 rows in set (0.00 sec)

9.统计xd部门员工的平均薪水
select *,avg(qf_salary) from qf_user_information inner join qf_post_information on qf_user_information.qf_post_id=qf_post_information.qf_post_id and qf_post=’xd’;
+————————+
| avg(qf_salary) |
+————————+
| 9000.0000 |
+————————+
1 row in set (0.00 sec)

10.查询出每个部门的最高薪资员工的信息
select qf_post_information.qf_post_id,qf_post,qf_id,qf_name,qf_age,qf_salary,qf_description
from (
select *
from (
select qf_post_id as id,max(qf_salary) as maxsalary
from qf_user_information
group by qf_post_id) as t
inner join qf_user_information on t.id=qf_user_information.qf_post_id
where t.maxsalary=qf_user_information.qf_salary)as tt
inner join qf_post_information on tt.qf_post_id=qf_post_information.qf_post_id order by qf_post_id;

11.简述以下开启binlog日志和慢查询日志的过程，简单写出其对应的步骤
将log-bin==/var/log/mysql-bin/logbin
server-id=2追加到/etc/my.cnf
将
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time=3
追加到 /etc/my.cnf中
重启mysqld systemctl restart msyqld
查询binlog日志：
mysqlbinlog /var/log/mysql-bin/binlog.000001 -v —base64-output=decode-rows
mysqlbinlog /var/log/mysql-bin/binlog.000001
mysqlbinlog /var/log/mysql-bin/binlog.000001 —start-datetime=”” —stop-datetime=””
mysqlbinlog /var/log/mysql-bin/binlog.000001 —start-position= —stop-datetime=
查询slow日志：
mysql -uroot -p123
select sleep(4);
cat /var/lib/mysql/slow.log

QQ截图20220323172117.png

insert into qf_user_information(1,”xingdian”,100,18,30000,”teacher”),(2,”diandian”,101,40,10000,”jishuzhichi”),(3,”diange”,101,18,8000,”qiantai”),(4,”dianye”,100,17,35000,”zhuguan”),(5,”xd”,101,26,9000,”qiantai”),(6,”diandiangege”,104,27,9000,,),(7,”diandiange”,102,30,8000,”hr”);