说到数据库每次面试都会在sql语句上吃大亏,考察的问题无非是去重,连表查询,求最值,平均值等,看起来很简单吧,但是写起来还真有点困难,不会sql面试会大打折扣。于是决定好好整理下sql,希望对大家有帮助
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
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家的支持。
本文分享自 自动化测试 To share 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!