MySQL -- SQL笔试题相关

1.银行代缴花费bank_bill

字段名	描述
serno	流水号
date	交易日期
accno	账号
name	姓名
amount	金额
brno	缴费网点

• serno: 一个 BIGINT UNSIGNED 类型的列，作为主键，且不为空。该列是自动增量的，每次插入新行时，都会自动递增生成一个唯一的值。

• date: 一个 DATE 类型的列，存储日期信息，不为空。

• accno: 一个 VARCHAR(100) 类型的列，用于存储账号信息，不为空。

• name: 一个 VARCHAR(50) 类型的列，用于存储姓名信息，不为空。

• amount: 一个 DECIMAL(10, 1) 类型的列，用于存储金额信息，不为空。该列的定义表示它可以存储最大 10 位数，其中小数点后有 1 位。

• brno: 一个 VARCHAR(150) 类型的列，用于存储分行信息，不为空。

建表如下：

CREATE TABLE `bank_bill` (
  `serno` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `accno` varchar(100) NOT NULL,
  `name` varchar(50) NOT NULL,
  `amount` decimal(10,1) NOT NULL,
  `brno` varchar(150) NOT NULL,
  PRIMARY KEY (`serno`)
) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8

 插入些数据：

INSERT INTO bank_bill (serno, date, accno, name, amount, brno) VALUES
(10001, '2024-05-01', '1234567890', 'zhang', 1000.5, '支行 A'),
(10002, '2024-05-01', '2345678901', 'li', 2000.3, '支行 C'),
(10003, '2024-05-01', '3456789012', 'zhang', 1500.2, '支行 B'),
(10004, '2024-05-01', '4567890123', 'wang', 2500.7, '支行 B'),
(10005, '2024-05-02', '5678901234', 'li', 1800.4, '支行 E'),
(10006, '2024-05-02', '6789012345', 'liu', 2200.9, '支行 B'),
(10007, '2024-05-03', '7890123456', 'luo', 1700.6, '支行 C'),
(10008, '2024-05-03', '8901234567', 'xie', 1900.8, '支行 A'),
(10009, '2024-05-04', '9012345678', 'zhang', 2100.2, '支行 D'),
(10010, '2024-05-04', '0123456789', 'ma', 2400.5, '支行 C');

 

1、统计表中缴费的总笔数和总金额

select count(serno),sum(amount) from bank_bill;

 

2、给出一个sql，按网点和日期统计每个网点每天的营业额，并按照营业额进行倒序排序

 select brno,date,sum(amount) as money from bank_bill group by brno,date order by money desc;

 3.查询每个客户的平均缴费金额：

SELECT name, AVG(amount) AS avg_amount
FROM bank_bill
GROUP BY name;

4.找出最大单笔交易的客户姓名和金额：

SELECT name, amount
FROM bank_bill
WHERE amount = (SELECT MAX(amount) FROM bank_bill);

5.统计每个网点的交易次数和总金额，并按交易次数排序：

SELECT brno, COUNT(*) AS transaction_count, SUM(amount) AS total_amount
FROM bank_bill
GROUP BY brno
ORDER BY transaction_count ASC;

 6.列出交易额在平均交易额之上的所有交易记录：

SELECT *
FROM bank_bill
WHERE amount > (SELECT AVG(amount) FROM bank_bill);

7.按月份统计交易总额和总笔数：

SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(amount) AS total_amount, COUNT(*) AS total_transactions
FROM bank_bill
GROUP BY YEAR(date), MONTH(date);

2.SQL面试题

转载知乎：【SQL】SQL面试50题 分类梳理与解答 - 知乎 (zhihu.com)

1.Like

查询「李」姓老师的数量 【Like】

select count(*) from teacher where Tname like '李%';

查询名字中含有「风」字的学生信息【Like】

select * from student where Sname like '%风%';

2.聚合函数

• 聚合函数sum/avg/count/max/min经常与group by搭配使用
• 在使用group by时，select后面只能放常数(如数字/字符/时间)、聚合函数、聚合键(group by后面的列名)
• 在使用group by时，千万不要在select后面放聚合键以外的列名！
• where函数后面不能直接使用聚合函数！（考虑放在having后面/变成子查询放在where后面）

查询男生、女生人数【聚合函数】

select ssex,count(*) from student group by ssex;

查询课程编号为02的总成绩【聚合函数】

select cno,sum(score) from sc group by cno having cno='02';

• 在 SQL 查询中，HAVING 子句通常用于在 GROUP BY 子句之后过滤结果。与 WHERE 子句不同，HAVING 子句可以使用聚合函数进行过滤。在你的示例中，因为 cno 是一个非聚合列，可以使用 WHERE 子句来实现相同的过滤效果。

 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列【聚合函数】

select cno,avg(score) as avg_score from sc group by cno order by avg_score desc,cno;

 求每门课程的学生人数 【聚合函数】

select cno,count(sno) as student_number from sc group by cno;

统计每门课程的学生选修人数（超过 5 人的课程才统计）【聚合函数】

select cno, count(*) as student_number 
from sc group by cno having count(*)>5
order by student_number desc, cno;

检索至少选修两门课程的学生学号 【聚合函数】

select sno,count(cno) from sc group by sno having count(cno)>=2;

 3.子查询

• select的结果列全部来自同一张表（而select的条件列来自不同表里），考虑子查询；
• select的结果列来自多张表，考虑联结
• 子查询与in/not in是好基友
• “所有/全部/都"类型的查询可以考虑not in求补集

查询在 SC 表存在成绩的学生信息【子查询】

select * from student where sno in (select sno from sc);

 查询不存在" 01 "课程但存在" 02 "课程的学生的课程情况【子查询】

select * from sc where sno not in (select sno from sc where cno = 01) and sno in (select sno from sc where cno = 02);

 查询同时存在" 01 "课程和" 02 "课程的情况【子查询】

select * from sc where sno in (select sno from sc where cno='01') and sno in (select sno from sc where cno='02');

查询出只选修两门课程的学生学号和姓名【子查询】

select sno,sname from student where sno in (select sno from sc group by sno having count(cno) = 2);

查询没有学全所有课程的同学的信息 【子查询】

select * from student where sno not in (select sno from sc group by sno having count(cno)=(select count(*) from course));

 查询选修了全部课程的学生信息【子查询】

select * from student where sno in (select sno from sc group by sno having count(cno)=(select count(cno) from course));

查询所有课程成绩均小于60分的学号、姓名【子查询】

select sno, sname from student
where sno not in (select distinct sno from sc where score>=60)
and sno in (select distinct sno from sc);

 这种方法更简便：

select sno,sname
from student
where sno in (select sno from sc group by sno having max(score)<60)

查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名【子查询】

select sno, sname from student
where sno in (select sno from sc where cno='01' and score>80);

 查询学过「张三」老师授课的同学的信息 【子查询】

 直接套三个子查询，过于冗长，使用下方法

select * from student
where sno in (select sno from sc, course, teacher
where teacher.tname='张三' and course.tno=teacher.tno and sc.cno=course.cno);

查询没学过"张三"老师讲授的任一门课程的学生姓名 【子查询】

select sname from student
where sno not in (select sno from sc, course, teacher
where teacher.tname='张三' and course.tno=teacher.tno and sc.cno=course.cno);

 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 【子查询】

记得排除学号为"01"本人

select * from student
where sno in 
(select distinct sno from sc where cno in (select cno from sc where sno='01')and sno <>'01');

4.inner join

检索" 01 "课程分数小于 60，按分数降序排列的学生信息【inner join】

select b.*, a.score 
from sc as a inner join student as b on a.sno=b.sno
where a.cno='01' and a.score<60 order by a.score desc;

5.outer join

6.三表联结

7.limit

8.窗口函数

9.case

10.时间函数