运维面试，遇到的SQL笔试题_运维面试的sql题

最新推荐文章于 2024-09-10 04:43:58 发布

code高级开源

最新推荐文章于 2024-09-10 04:43:58 发布

阅读量480

点赞数 4

文章标签：运维面试 sql

本文链接：https://blog.youkuaiyun.com/code8889/article/details/138254840

版权

create table user(id int primary key,name varchar(10),age int,sex char(1));

create table salary(userid int, salary int, month int, year int);
alter table salary add foreign key(userid) references user(id);


insert into user values(1,'zhangsan',27,'n'),(2,'lisi',28,'n'),(3,'wuwang',29,'v');

insert into salary values(1,1000,1,2004),(1,1000,2,2004),(1,2000,3,2004),(2,2000,1,2004),(2,2000,2,2004),(2,2000,3,2004),(3,2000,1,2004),(3,2000,2,2004),(3,2000,3,2004);

1，求2004年一月份工资最低的员工姓名

mysql> select * from user u join salary s on u.id=s.userid and salary = (select min(salary) from salary where year=2004 and month=1) and year=2004 and month=1;
+----+----------+------+------+--------+--------+-------+------+
| id | name     | age  | sex  | userid | salary | month | year |
+----+----------+------+------+--------+--------+-------+------+
|  1 | zhangsan |   27 | n    |      1 |   1000 |     1 | 2004 |
+----+----------+------+------+--------+--------+-------+------+
1 row in set (0.00 sec)

2，删除User表中Sex为女的用户，在 Salary 表中的所有信息

delete from salary where userid=(select id from user where sex='v');

3，求Sex 等于男的用户，2004 年第一季度的平均工资

mysql> select u.sex,avg(s.salary) from user u inner join salary s on u.id=s.userid and  sex='n' and year=2004 and month between 1 and 3 group by u.sex;
+------+---------------+
| sex  | avg(s.salary) |
+------+---------------+
| n    |     1666.6667 |
+------+---------------+
1 row in set (0.00 sec)

4，求Sex 等于男并且2004年3 月份有涨工资的用户

为了做好运维面试路上的助攻手，特整理了上百道 【运维技术栈面试题集锦】 ，让你面试不慌心不跳，高薪offer怀里抱！

这次整理的面试题，小到shell、MySQL，大到K8s等云原生技术栈，不仅适合运维新人入行面试需要，还适用于想提升进阶跳槽加薪的运维朋友。