第一题

# 第一步：找小于等于80分的学员姓名
select distinct name from t_student where fenshu <= 80

# 第二步：not in
select distinct name from t_student where name not in(select distinct name from t_student where fenshu <= 80)

第二题

其中，两个表的关联字段为申请单号。
1）查询身份证号为440401430103082的申请日期。
2）查询同一个身份证号码有两条以上记录的身份证号码及记录个数。
3）将身份证号码为440401430103082的记录在两个表中的申请状态均改为07。
4）删除g_cardapplydetail表中所有姓李的记录。
模拟数据：考试做这种题目最重要的是要冷静下来，只有静下来SQL才能写好。要模拟数据。看到数据SQL就好写了。

1）查询身份证号为440401430103082的申请日期。
bigint转date，可以使用from_unixtime函数。

select a.g_applydate from g_cardapply a join g_cardapplydetail b on a.g_applyno = b.g_applyno where b.g_idcard = '440401430103082'

2）查询同一个身份证号码有两条以上记录的身份证号码及记录个数。

select count(g_idcard),g_idcard from g_cardapplydetail group by g_idcard having count(g_idcard) >= 2

3）将身份证号码为440401430103082的记录在两个表中的申请状态均改为07。

UPDATE 
    g_cardapply
JOIN 
    g_cardapplydetail 
ON 
    g_cardapply.g_applyno = g_cardapplydetail.g_applyno 
AND
    g_cardapplydetail.g_idcard = '440401430103082'
SET g_cardapply.g_state = '07',
g_cardapplydetail.g_state = '07'

4）删除g_cardapplydetail表中所有姓李的记录。

delete t1,t2 from g_cardapply t1 join g_cardapplydetail t2 on t1.g_applyno=t2.g_applyno where t2.g_name like '李%';

第三题

表名：stuscore
1）统计如下：课程不及格[0~59]的多少个，良[60~80]多少个，优[81-100]多少个。
2）计算科科及格的人的平均成绩。

第四题

1）请用一条SQL语句查询出不同部门中担任“钳工”的职工平均工资。
2）请用一条SQL语句查询出不同部门中担任“钳工”的职工平均工资高于2000的部门。

第五题

Employee是雇员信息表：
雇员姓名（主键）：person-name
街道：street
城市：city
Company是公司信息表：
公司名称（主键）：company-name
城市：city
Works是雇员工作信息表：
雇员姓名（主键）：person-name
公司名称：company-name
年薪：salary
Manages是雇员工作关系表：
雇员姓名（主键）：person-name
经理姓名：manager-name
模拟数据：
员工表：employee

公司表：company

雇员工作信息表：Works

雇员工作关系表：Manages

请给出下面每一个查询的SQL语句：

1. 找出所有居住地与工作的公司在同一城市的员工的姓名。
2. 找出比Small Bank Corporation的所有员工收入都高的所有员工的姓名。
3. 找出平均年薪在10000美元以上的公司及其平均年薪。

第六题

客户表Client

订单表Order

客户订单表ClientOrder

图书表Book

1. 请写出一条SQL语句，查询出每个客户的所有订单并按照地址排序，要求输出格式为：address client_name phone order_id
2. 请写出一条SQL语句，查询出每个客户订购的图书总价。要求输出格式为：client_name total_price
3. 如果要求每个订单可以包含多种图书，应该如何修改Order表的主键？为了保证每个订单只被一个客户拥有，应该在ClientOrder表上增加怎样的约束？

第七题

模拟数据：
学生表：student

课程表：course

成绩表：sc

教师表：teacher

1. 查询1号课比2号课成绩高的所有学生学号。
2. 查询平均成绩大于60分的学号和平均成绩。
3. 查询所有学生学号、姓名、选课数、总成绩。
4. 查询姓“李”的老师的个数。
5. 查询没学过“叶平”老师课的学号、姓名。

第八题

学生表：student

课程表：class

选课表：chosen_class

1. 没有选修课程编号为C1的学生姓名
2. 列出每门课程名称和平均成绩，并按照成绩排序
3. 选了2门课以上的学生姓名。

第九题

要转换成：

MySQL行转列

MySQL行转列又叫做数据透视。什么叫做行转列？将原本横向排列的数据透视成纵向排列的数据，进而进行计算、分析、展示等操作。

假设有一个学生选课成绩表，包含学生姓名（stu_name）、课程名称（course_name）和分数（score）三个字段。在原始数据中，每个学生在不同的课程中都有自己的得分情况，数据样例如下：

可以使用行转列操作，将每个学生在不同课程中的分数拆分成多条记录，每条记录包含一个课程以及对应的分数。转换后的数据样例如下：

从上表中可以看出，在行转列之后，每一行记录都表示了一个学生在不同课程中的分数。这样更便于对不同科目的分数进行比较、计算平均值等分析操作。

使用case when+group by完成

drop table if exists t_student;
create table t_student(
  stu_name varchar(10),
  course_name varchar(10),
  score int
);
insert into t_student(stu_name, course_name, score) values('张三', '数学', 80);
insert into t_student(stu_name, course_name, score) values('张三', '英语', 85);
insert into t_student(stu_name, course_name, score) values('张三', '历史', 90);
insert into t_student(stu_name, course_name, score) values('李四', '数学', 75);
insert into t_student(stu_name, course_name, score) values('李四', '英语', 92);
insert into t_student(stu_name, course_name, score) values('李四', '历史', 85);
insert into t_student(stu_name, course_name, score) values('王五', '数学', 88);
insert into t_student(stu_name, course_name, score) values('王五', '英语', 90);
insert into t_student(stu_name, course_name, score) values('王五', '历史', 95);
commit;
select * from t_student;

行转列后的效果是：

sql如下：

select
    stu_name,
    max(case course_name when '数学' then score else 0 end) as '数学',
    max(case course_name when '英语' then score else 0 end) as '英语', 
    max(case course_name when '历史' then score else 0 end) as '历史' 
from 
    t_student
group by 
    stu_name;

通过以上内容的学习，我们这个面试题就迎刃而解了：

select
    year,
    max(case season when '一季度' then count else 0 end) as '一季度',
    max(case season when '二季度' then count else 0 end) as '二季度',
    max(case season when '三季度' then count else 0 end) as '三季度',
    max(case season when '四季度' then count else 0 end) as '四季度'
from 
    t_temp 
group by 
    year;

第十题

select 
    x.a 开始数字, y.a 结束数字
from 
    (select m.a,row_number() over(order by m.a) as rownum from (select a, lag(a) over(order by a asc) as pre_a from t) m where m.a - m.pre_a != 1 or m.pre_a is null) x 
join 
    (select n.a,row_number() over(order by n.a) as rownum from (select a, lead(a) over(order by a asc) as next_a from t) n where n.next_a - n.a != 1 or n.next_a is null) y 
on 
    x.rownum = y.rownum;

解答上面这个题目需要具备以下知识点：

• lag函数
• lead函数
• row_number函数

lag函数：获取当前行的上一行数据

select empno,ename,sal,(lag(sal) over(order by sal asc)) as pre_sal from emp;

注意：over函数用来指定“在.....范围内”，通常和lag函数联用。

lead函数：获取当前行的下一行数据

select empno,ename,sal,(lead(sal) over(order by sal asc)) as next_sal from emp;

注意：over函数用来指定“在.....范围内”，通常和lead函数联用。

row_number函数：可以为查询结果集生成行号：

select empno,ename,sal,row_number() over(order by sal) as rownum from emp;

利用row_number函数，将两个不相关的列拼接在一起显示：

select 
    x.a, y.b 
from 
    (select a,row_number() over(order by a) as rownum from t1) x 
join 
    (select b,row_number() over(order by b) as rownum from t2) y 
on 
    x.rownum = y.rownum;

CTE语法（公用表表达式）：Common Table Expression。创建临时表的一种语法：

-- 查询每个部门平均工资的工资等级
-- 第一种写法
select 
    t.deptno,t.avgsal,s.grade 
from 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t 
join 
    salgrade s 
on 
    t.avgsal between s.losal and s.hisal;

-- 第二种写法：使用CTE语法
with cte_exp as(select deptno,avg(sal) as avgsal from emp group by deptno)
select 
    cte_exp.deptno,cte_exp.avgsal,s.grade
from
    cte_exp
join
    salgrade s
on
    cte_exp.avgsal between s.losal and s.hisal;

partition by：将数据分区，和group by区别是：group by是分组，然后和分组函数一起用。partition by分区不需要和分组函数一起使用

select deptno, empno,ename,sal,(lag(sal) over(partition by deptno order by sal asc)) as pre_sal from emp;

MySQL 8.0及以上版本中支持如下常用的窗口函数：

1. ROW_NUMBER()：排名函数，返回当前结果集中每个行的行号；
2. RANK()：排名函数，计算分组结果中的排名，相同的行排名相同且没有空缺，下一个行排名跳过空缺；
3. DENSE_RANK()：排名函数，计算分组结果中的排名，相同的行排名相同，排名连续，没有空缺；
4. NTILE()：将分组结果等分为指定的组数，计算每组的大小；
5. LAG()：返回分组内前一行的值；
6. LEAD()：返回分组内后一行的值；
7. FIRST_VALUE()：返回分组内第一个值；
8. LAST_VALUE()：返回分组内最后一个值；
9. AVG()、SUM()、COUNT()、MIN()、MAX()：聚合函数，可以配合OVER()进行窗口操作。

需要注意的是，MySQL的窗口函数和其他DBMS中的窗口函数相比较，可能略有不同，需要根据MySQL的文档进行使用。