Msql面试zongjie

前言

说到数据库每次面试都会在sql语句上吃大亏，考察的问题无非是去重，连表查询，求最值，平均值等，看起来很简单吧，但是写起来还真有点困难，不会sql面试会大打折扣。于是决定好好整理下sql，希望对大家有帮助

创建student和grade表

student：

grade：

基本语法总结

插入数据

insert into student values(1001 , '周星驰' , 18)

查询所有

select *from student

关键查询

select *from student where id = 1001 select *from student where id = 1001 or name = '周冬雨' or age select *from student where age>18 select *from student where age>18 and age < 22

排序查询

正向: select *from student order by age asc 逆向: select *from student order by age desc

最值查询最大 select max(age) from student 最小 select min(age) from student

平均

select avg(age) from student

统计条数

select count(*) from student

求和

select sum(age) from student

分页查询

select *from student limit 1,3

指定查询

select *from student where age in (22,21)

模糊查询

select *from student where name like '%周%' 去重查询

select distinct age from student select *from student group by age

修改数据

update student set name = "周zz" where id = 1002 update student set name = "周zz" age = 18 where id = 1002

删除数据

delete from student where id = 1006 and id = 1005

实战题

根据student和grade表

1.查询所有学生的数学成绩，显示学生姓名name, 分数， 由高到低

2.统计每个学生的总成绩,显示字段：姓名，总成绩

3.统计每个学生的总成绩（由于学生可能有重复名字）,显示字段：学生id，姓名，总成绩

4.列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩

5.列出各门课程成绩最好的2位学生， 要求显示字段: 学号，姓名, 科目，成绩

1.select a.name ,b.score ,b.course from student 
a,grade b where a.id = b.id and course = '数学' order by score desc

2.select a.name ,sum(b.score) as sum_score 
from student a ,grade b where a.id =b.id group by name desc

3.
SELECT a.id, a.name, c.sum_score
from student a, 
(SELECT b.id, sum(b.score) as sum_score
FROM grade b
GROUP BY id
) c
WHERE a.id = c.id
ORDER BY sum_score
DESC

4.SELECT c.id , a.name, c.course, c.score
FROM grade c, student a,
(SELECT b.course, MAX(b.score) as max_score
FROM grade b
GROUP BY course) t
WHERE c.course = t.course
AND c.score = t.max_score
AND a.id = c.id

5.SELECT t1.id, a.name, t1.course,t1.score
FROM grade t1, student a
WHERE
  (SELECT count(*) FROM grade t2 
  WHERE t1.course=t2.course AND t2.score>t1.score
  )<2
and a.id = t1.id
ORDER BY t1.course,t1.score 
DESC

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家的支持。