前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Msql面试zongjie

Msql面试zongjie

作者头像
wencheng
发布2020-07-16 18:55:02
3060
发布2020-07-16 18:55:02
举报
文章被收录于专栏:python 自动化测试

前言

说到数据库每次面试都会在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位学生, 要求显示字段: 学号,姓名, 科目,成绩

代码语言:javascript
复制
1.select a.name ,b.score ,b.course from student 
a,grade b where a.id = b.id and course = '数学' order by score desc
代码语言:javascript
复制
2.select a.name ,sum(b.score) as sum_score 
from student a ,grade b where a.id =b.id group by name desc
代码语言:javascript
复制
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

代码语言:javascript
复制
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
代码语言:javascript
复制
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

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

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-06-06,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 自动化测试 To share 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 前言
  • 创建student和grade表
  • 实战题
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档