sql题目

第一题


第一题：
用一条sql语句，查询出每门课程都大于80分的学生姓名

建表语句：

drop table if exists t_student;
create table t_student(
	name varchar(255),
	kecheng varchar(255),
	fenshu double(3,1)
);
insert into t_student values('张三', '语文', 81);
insert into t_student values('张三', '数学', 75);
insert into t_student values('王五', '英语', 90);
select * from t_student;

思路：查询出小于80分的所欲学生姓名（去重），然后使用not in，不去重会有多个少于80分的重复名字

# 第一步：找小于等于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就好写了。

drop table if exists g_cardapply;
create table g_cardapply(
	g_applyno varchar(8) primary key,
	g_applydate varchar(255),
	g_state varchar(2)
);

insert into g_cardapply values(1,'2008-08-08', '01');
insert into g_cardapply values(2,'2022-10-11', '01');
insert into g_cardapply values(3,'2023-03-23', '01');
insert into g_cardapply values(4,'2007-12-12', '02');
insert into g_cardapply values(5,'2009-12-11', '02');
select * from g_cardapply;

drop table if exists g_cardapplydetail;
create table g_cardapplydetail(
	g_applyno varchar(8),
	g_name varchar(8),
	g_idcard varchar(30),
	g_state varchar(2)
);
insert into g_cardapplydetail values('1','张三','440401430103082','01');
insert into g_cardapplydetail values('2','张三','440401430103082','01');
insert into g_cardapplydetail values('3','张三','440401430103082','01');
insert into g_cardapplydetail values('4','李四','440401430111111','02');
insert into g_cardapplydetail values('5','王五','440401430122222','02');
select * from g_cardapplydetail;

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表中所有姓李的记录。

要注意a表符合姓李的相关日期记录也需要删除

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


第三题


drop table if exists stuscore;
create table stuscore(
	name varchar(255),
	subject varchar(255),
	score int,
	stuid int
);
insert into stuscore values('张三','数学',89,1);
insert into stuscore values('张三','语文',80,1);
insert into stuscore values('张三','英语',70,1);
insert into stuscore values('李四','数学',90,2);
insert into stuscore values('李四','语文',70,2);
insert into stuscore values('李四','英语',80,2);
select * from stuscore;

表名：stuscore
1)统计如下：课程不及格[059]的多少个，良[6080]多少个，优[81-100]多少个。
多种方式：可以采用union实现，也可以采用case when then when then else and实现
case把符合的都转换成了字符串，后续可以再用group by进行分组
then是加上条件，as是起名字，count来统计条数，然后再分组

select case 
when score>=0 and score<=59 then '0-59'
when score>=60 and score<= 80 then '60-80'
when score>=81 and score<=100 then '81-100'
else '其他' end as score_range, count(*)
from stuscore group by score_range;

2)计算科科及格的人的平均成绩。

select name,avg(score) as avgscore
from stuscore 
where
 name not in(select name from stuscore where score<60)
GROUP BY name;


第四题


drop table if exists WCMEmploy;

create table WCMEmploy(
	no int,
	name varchar(255),
	dname varchar(255),
	job varchar(255),
	sal double(10,2)
);
insert into WCMEmploy values(1, '张三', 'A', '钳工', 1500);
insert into WCMEmploy values(2, '李四', 'A', '钳工', 2800);
insert into WCMEmploy values(3, '王五', 'A', '油漆工', 3000);
insert into WCMEmploy values(4, '赵六', 'A', '水电工', 4500);
insert into WCMEmploy values(5, '钱七', 'B', '钳工', 1800);
insert into WCMEmploy values(6, '小毛', 'B', '钳工', 2600);
insert into WCMEmploy values(7, '小明', 'B', '油漆工', 2800);
insert into WCMEmploy values(8, '小刚', 'B', '水电工', 5000);
insert into WCMEmploy values(9, '孙悟空', 'C', '油漆工', 6000);
insert into WCMEmploy values(10, '猪八戒', 'C', '钳工', 2000);
insert into WCMEmploy values(11, '沙和尚', 'C', '水电工', 5000);
insert into WCMEmploy values(12, '武松', 'C', '钳工', 2000);
insert into WCMEmploy values(13, '阮小七', 'D', '水电工', 5000);
insert into WCMEmploy values(14, '哪吒', 'D', '油漆工', 2500);
insert into WCMEmploy values(15, '三太子', 'D', '钳工', 3000);
insert into WCMEmploy values(16, '龙王', 'D', '钳工', 4000);
insert into WCMEmploy values(17, '露西', 'D', '钳工', 3300);
select * from WCMEmploy;

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

先筛选钳工，再分组，分组可以用dname和job的联合分组

select dname,job,avg(sal) from WCMEmploy where job='钳工' group by dname,job;

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

使用having可以对分组结果再过滤

select dname,job,avg(sal) from WCMEmploy where job='钳工' group by dname,job having avg(sal)>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

drop table if exists Employee;
create table Employee(
	`person-name` varchar(255) primary key,
	street varchar(255),
	city varchar(255)
);
insert into Employee values('bob','街道1','天津');
insert into Employee values('frank','街道2','天津');
insert into Employee values('jack','街道3','天津');
insert into Employee values('lucy','街道4','天津');
insert into Employee values('周二','街道5','石家庄');
insert into Employee values('张三','街道6','北京');
insert into Employee values('李四','街道7','北京');
insert into Employee values('王五','街道8','北京');
insert into Employee values('赵六','街道9','石家庄');
insert into Employee values('钱七','街道10','石家庄');
select * from Employee;

drop table if exists Company;
create table Company(
	`company-name` varchar(255) primary key,
	city varchar(255)
);
insert into Company values('Small Bank Corporation', '北京');
insert into Company values('公司B', '石家庄');
insert into Company values('公司C', '天津');
select * from Company;

drop table if exists Works;
create table Works(
	`person-name` varchar(255) primary key,
	`company-name` varchar(255),
	salary double(10,2)
);
insert into Works values('bob','公司C', 22000);
insert into Works values('frank','公司C', 99999);
insert into Works values('jack','公司C', 6000);
insert into Works values('lucy','公司C', 11000);
insert into Works values('周二','公司B', 31000);
insert into Works values('张三','Small Bank Corporation', 11000);
insert into Works values('李四','Small Bank Corporation', 5000);
insert into Works values('王五','Small Bank Corporation', 8000);
insert into Works values('赵六','公司B', 12000);
insert into Works values('钱七','公司B', 21000);
select * from Works;

drop table if exists Manages;
create table Manages(
	`person-name` varchar(255) primary key,
	`manager-name` varchar(255)
);
insert into Manages values('bob','frank');
insert into Manages values('frank',NULL);
insert into Manages values('jack','lucy');
insert into Manages values('lucy','bob');
insert into Manages values('周二','jack');
insert into Manages values('张三','李四');
insert into Manages values('李四','王五');
insert into Manages values('王五','赵六');
insert into Manages values('赵六','钱七');
insert into Manages values('钱七','周二');
select * from Manages;

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

1. 找出所有居住地与工作的公司在同一城市的员工的姓名。

   employee e
   company c
   works w
   将以上三张表进行连接，连接条件：
   e join w on e.person-name=w.person-name员工表和工作表连接
   join c on w.company-name=c.company-name
   select可以先不写 ，用where进行过滤

   select e.`person-name`
   from employee e 
   join works w on e.`person-name`=w.`person-name` 
   join company c on w.`company-name`=c.`company-name`
   where e.city=c.city;

2. 找出比Small Bank Corporation的所有员工收入都高的所有员工的姓名。

   select `person-name` from works where salary>(select max(salary) as maxsal from works where `company-name`='Small Bank Corporation');

3. 找出平均年薪在10000美元以上的公司及其平均年薪。

   思路：按照公司进行分组，对年薪求平均值

   select avg(salary) avgsal,`company-name` from works group by `company-name` having avgsal>10000;


第六题

客户表Client
订单表Order
客户订单表ClientOrder
图书表Book

drop table if exists Client;
create table Client(
	client_id int,
	client_name varchar(255),
	phone varchar(255),
	address varchar(255)
);
insert into Client values(1,'Zhao', 12522542470, '海淀区');
insert into Client values(2,'Wang', 12522542471, '朝阳区');
insert into Client values(3,'Sun', 12522542472, '大兴区');
insert into Client values(4,'Li', 12522542473, '东城区');
select * from Client;

drop table if exists `Order`;
create table `Order`(
	order_id int,
	book_id int
);
insert into `Order` values(11,21);
insert into `Order` values(12,22);
insert into `Order` values(13,23);
insert into `Order` values(14,24);
insert into `Order` values(15,21);
insert into `Order` values(16,22);
insert into `Order` values(17,23);
insert into `Order` values(18,24);
select * from `Order`;

drop table if exists ClientOrder;
create table ClientOrder(
	client_id int,
	order_id int
);
insert into ClientOrder values(1,11);
insert into ClientOrder values(1,12);
insert into ClientOrder values(2,13);
insert into ClientOrder values(2,14);
insert into ClientOrder values(3,15);
insert into ClientOrder values(3,16);
insert into ClientOrder values(4,17);
insert into ClientOrder values(4,18);
select * from ClientOrder;

drop table if exists Book;
create table Book(
	book_id int,
	book_name varchar(255),
	price double(10,2)
);
insert into Book values(21, '管理学', 30);
insert into Book values(22, '计算机网络', 50);
insert into Book values(23, '国家地理杂志', 90);
insert into Book values(24, '西游记', 20);
select * from Book;

1. 请写出一条SQL语句，查询出每个客户的所有订单并按照地址排序，要求输出格式为：address client_name phone order_id

   分成client c
   clientorder co
   c.client_id=co.client_id

   select c.address,c.client_name,c.phone,co.order_id from client c join clientorder co on c.client_id=co.client_id order by c.address;

2. 请写出一条SQL语句，查询出每个客户订购的图书总价。要求输出格式为：client_name total_price

   select c.client_name,sum(b.price) as total_price 
   from client c join clientorder co on c.client_id=co.client_id 
   join `order` o on co.order_id=o.order_id 
   join book b on o.book_id=b.book_id 
   group by c.client_name;

3. 如果要求每个订单可以包含多种图书，应该如何修改Order表的主键？为了保证每个订单只被一个客户拥有，应该在ClientOrder表上增加怎样的约束？

   #把order表的order_id和book_id做复合主键
   #加上唯一性约束：unique


第七题

模拟数据：
学生表：student
课程表：course
成绩表：sc
教师表：teacher

drop table if exists student;
create table student(
	`s#` int,
	sname varchar(255),
	sage int,
	ssex char(1)
);
insert into student values(1,'学生1', 20, '男');
insert into student values(2,'学生2', 20, '男');
insert into student values(3,'学生3', 20, '男');
insert into student values(4,'学生4', 20, '男');
select * from student;

drop table if exists course;
create table course(
	`c#` int,
	cname varchar(255),
	`t#` int
);
insert into course values(1,'数学',1);
insert into course values(2,'语文',1);
insert into course values(3,'英语',2);
insert into course values(4,'政治',2);
select * from course;

drop table if exists sc;
create table sc(
	`s#` int,
	`c#` int,
	score int
);
insert into sc values(1,1,65);
insert into sc values(1,2,66);
insert into sc values(1,3,66);
insert into sc values(1,4,69);
insert into sc values(2,1,55);
insert into sc values(2,2,66);
insert into sc values(2,3,75);
insert into sc values(2,4,86);
insert into sc values(3,1,96);
insert into sc values(3,2,99);
insert into sc values(3,3,70);
insert into sc values(3,4,60);
insert into sc values(4,3,65);
insert into sc values(4,4,99);
select * from sc;

drop table if exists teacher;
create table teacher(
	`t#` int,
	tname varchar(255)
);
insert into teacher values(1,'叶平');
insert into teacher values(2,'李白');
select * from teacher;

1. 查询1号课比2号课成绩高的所有学生学号。

   主要是c#，查询出1号和2号课的学生后，要确定两表的s#是一样的，才是同一个人，然后去对比
   select * from sc where c#=1;
   select * from sc where c#=2;

   select a.`s#` 
   from (select * from sc where `c#`=1) a 
   join (select * from sc where `c#`=2) b 
   on a.`s#`=b.`s#` where a.score>b.score;

2. 查询平均成绩大于60分的学号和平均成绩。

   select `s#`,avg(score) from sc group by `s#` having avg(score) > 60;

3. 查询所有学生学号、姓名、选课数、总成绩。

   求选课数就是count，总成绩sum，联合分组后才可以一起显示学号姓名

   select  s.`s#`,s.sname,count(sc.`c#`),sum(sc.score)
   from student s join sc on s.`s#`=sc.`s#` group by s.`s#`,s.sname;

4. 查询姓“李”的老师的个数。

   count(tname)记录不为null的个数

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

5. 查询没学过“叶平”老师课的学号、姓名。

   select t# from teacher where tname=’叶平’这是叶平老师的编号
   select c# from course where t#=(上面)这是查叶平老师的课的编号
   select distinct s# from sc where c# in(上面)，c#是查询的字段，in就是要查询的内容

   select * from student where `s#` not in(select distinct `s#` from sc where `c#` in(select `c#` from course where `t#`=(select `t#` from teacher where tname='叶平')));


第八题

学生表：student
课程表：class
选课表：chosen_class

1. 没有选修课程编号为C1的学生姓名

   select s_id from chosen_class where c_id=’c1’;选择了的
   select s_id from chosen_class where s_id not in(没选择的

select sname from student where s_id in(select s_id from chosen_class where s_id not in(select s_id from chosen_class where c_id='c1'));

2. 列出每门课程名称和平均成绩，并按照成绩排序

   class c
   chosen_class cc
   c.c_id=cc.c_id

   select c.c_name,avg(cc.grade) as avgscore  from `class` c join chosen_class cc on c.c_id=cc.c_id group by c.c_name order by avgscore;

3. 选了2门课以上的学生姓名。

   思路：按照学生姓名分组，计数
   student s
   chosen_class cc
   条件：s.s_id=cc.s_id

   select s.sname,count(c_id) from student s join chosen_class cc on s.s_id=cc.s_id group by s.sname having count(c_id) > 2;


第九题

要转换成：

/*
第九题
*/
drop table if exists t_temp;
create table t_temp(
	year int,
	season varchar(255),
	count int
);
insert into t_temp values(2010,'一季度',100);
insert into t_temp values(2010,'二季度',200);
insert into t_temp values(2010,'三季度',300);
insert into t_temp values(2010,'四季度',400);
insert into t_temp values(2011,'一季度',150);
insert into t_temp values(2011,'二季度',250);
insert into t_temp values(2011,'三季度',350);
insert into t_temp values(2011,'四季度',450);
select * from t_temp;

MySQL行转列

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

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

stu_name	course_name	score
张三	数学	80
张三	英语	85
张三	历史	90
李四	数学	75
李四	英语	92
李四	历史	85
王五	数学	88
王五	英语	90
王五	历史	95

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

stu_name	数学	英语	历史
张三	80	85	90
李四	75	92	85
王五	88	90	95

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

使用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如下：

当course_name为数学时输出成绩，否则都输出0，然后输出最大值，就筛选出来了，列的名字都是后面起名字获得

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;


第十题


建表

drop table if exists t;
create table t(
	A int
);
insert into t values(1);
insert into t values(2);
insert into t values(3);
insert into t values(5);
insert into t values(6);
insert into t values(7);
insert into t values(8);
insert into t values(10);
select * from t;

答案：

select a,(lag(a) over(order by a)) as per_a from t;
/*
将以上sql语句执行结果当做临时表m，上面的表对比a可以发现规律
筛选条件：m.a-m.per_a <> 1 or m.pre_a is null 
执行完就是左边的a表为135，右表可以先用row_number生成123
*/
select m.a,row_number() over(order by m.a)as rownum from (select a,(lag(a) over(order by a)) as per_a from t) m where m.a-m.per_a <> 1 or m.per_a is null;

select a,(lead(a) over(order by a)) as pre_a from t;
/*
将以上sql查询结果当做临时表n，这个规律可以借鉴上面的，后减前
筛选条件：n.pre_a - n.a <> 1 or n.pre_a is null
*/
select n.a,row_number() over(order by n.a) as rownum from (select a,(lead(a) over(order by a)) as pre_a from t) n where n.pre_a - n.a <> 1 or n.pre_a is null;

/*
将以上两个最终的查询结果看做两张表：x和y 
连接条件：x.rownum=y.rownum 
*/
select x.a as 开始数字,y.a as 结束数字
from (select m.a,row_number() over(order by m.a)as rownum from (select a,(lag(a) over(order by a)) as per_a from t) m where m.a-m.per_a <> 1 or m.per_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)) as pre_a from t) n where n.pre_a - n.a <> 1 or n.pre_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的文档进行使用。