数据库练习

weixin_64697722

已于 2022-10-14 19:42:22 修改

阅读量198

点赞数

分类专栏： MySql 文章标签：数据库 sql

于 2022-10-14 19:29:31 首次发布

本文链接：https://blog.csdn.net/weixin_64697722/article/details/127326289

版权

MySql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

查询student表的所有记录

SELECT * FROM user;

查询出学生第二到第四条记录

SELECT * FROM user WHERE id BETWEEN 2 AND 4;

随机查出10个学生的信息

SELECT * FROM user ORDER  BY  RAND() LIMIT 0,10;

查出数学系和汉语言文学系的学生

SELECT user.name,yuan.yname FROM yuan,user WHERE user.yid=yuan.id HAVING   yuan.yname='数学' OR yuan.yname='汉语文学';

查出20到25岁的学生

SELECT * FROM user WHERE age BETWEEN 20 AND 25;

查出20到25岁的学生的成绩信息，要求显示学生名，课程名和分数

SELECT user.name, cores.cname,score.grade FROM user,cores,score WHERE

 score.stu__id=user.id AND score.c_id=cores.id AND age BETWEEN 20 AND 25;

查询每个院系各有多少人（分组）

SELECT yuan.yname, COUNT(*) AS num FROM user,yuan WHERE user.yid=yuan.id GROUP BY yuan.yname;

查出没有成绩的学生（is null）

SELECT * FROM user,score WHERE score.stu__id=user.id AND grade IS NULL;

查出每个学生的总成绩 (sum())

SELECT user.name, sum(grade) FROM user,score WHERE score.stu__id=user.id GROUP BY user.name;

查出小五的考试科目和考试成绩

SELECT user.name, cores.cname,score.grade FROM cores,score,user

WHERE cores.id=score.c_id AND user.id=score.stu__id AND user.name='小五';

查出姓王的考试科目和考试成绩

SELECT user.name, cores.cname,score.grade FROM cores,score,user

WHERE cores.id=score.c_id AND user.id=score.stu__id AND user.name LIKE '王%';

查询计算机成绩低于95的学生信息

SELECT * FROM cores,user,score WHERE cores.id=score.c_id AND score.stu__id =user.id AND grade<95 AND cores.cname='计算机';

从score表中查询每个科目的最高分

SELECT cores.cname,MAX(score.grade) FROM score,cores WHERE score.c_id=cores.id  GROUP BY cores.cname;

用连接的方式查询所有学生的信息和考试信息

SELECT user.id,user.name,user.sex,user.birth,user.age,user.address,cores.cname,score.grade FROM user,cores,score

WHERE user.id=score.stu__id AND cores.id=score.c_id;

计算每个学生的总成绩

SELECT user.name, sum(grade) FROM user,score WHERE score.stu__id=user.id GROUP BY user.name;

计算每个考试科目的平均成绩

SELECT cores.cname,AVG(score.grade) FROM score,cores WHERE score.c_id=cores.id GROUP BY cores.cname;

查询同时参加计算机和英语考试的学生的信息

SELECT * FROM student WHERE id IN

(SELECT 
s.id
FROM  student AS s,score AS sc,cores AS c

WHERE sc.stu__id = s.id AND sc.c_id = c.id AND c.cname = "英语" AND s.id IN 

(SELECT 
s.id
FROM  student AS s,score AS sc,cores AS c

WHERE sc.stu__id = s.id AND sc.c_id = c.id AND c.cname = "计算机" ));

将计算机考试成绩按从高到低进行排序

SELECT 
*
FROM  student AS s,score AS sc,cores AS c

WHERE sc.stu__id = s.id AND sc.c_id = c.id AND c.cname = "计算机" ORDER BY grade desc;

查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

SELECT 
s.name,2022-birth,y.yname,c.cname,grade
FROM  student AS s,score AS sc,cores AS c,yuan AS y

WHERE sc.stu__id = s.id AND sc.c_id = c.id AND s.yid = y.id AND address LIKE "%湖南%";