SQL数据库十四种案例介绍

数据表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94

/* Navicat SQLite Data Transfer   Source Server         : school Source Server Version : 30808 Source Host           : :0   Target Server Type    : SQLite Target Server Version : 30808 File Encoding         : 65001   Date : 2021-12-23 16:06:04 */   PRAGMA foreign_keys = OFF ;   -- ---------------------------- -- Table structure for Course -- ---------------------------- DROP TABLE IF EXISTS "main" . "Course" ; CREATE TABLE Course(      courseid integer  primary key autoincrement,      courseme varchar (32),      teacherid int );   -- ---------------------------- -- Records of Course -- ---------------------------- INSERT INTO "main" . "Course" VALUES (3001, '语文' , 1001); INSERT INTO "main" . "Course" VALUES (3002, '数学' , 1002);   -- ---------------------------- -- Table structure for Mark -- ---------------------------- DROP TABLE IF EXISTS "main" . "Mark" ; CREATE TABLE Mark(      userid integer ,      courseid integer not null ,      score int default 0 );   -- ---------------------------- -- Records of Mark -- ---------------------------- INSERT INTO "main" . "Mark" VALUES (2001, 3001, 89); INSERT INTO "main" . "Mark" VALUES (2001, 3002, 90); INSERT INTO "main" . "Mark" VALUES (2002, 3001, 66); INSERT INTO "main" . "Mark" VALUES (2003, 3002, 85);   -- ---------------------------- -- Table structure for sqlite_sequence -- ---------------------------- DROP TABLE IF EXISTS "main" . "sqlite_sequence" ; CREATE TABLE sqlite_sequence( name ,seq);   -- ---------------------------- -- Records of sqlite_sequence -- ---------------------------- INSERT INTO "main" . "sqlite_sequence" VALUES ( 'Teacher' , 1002); INSERT INTO "main" . "sqlite_sequence" VALUES ( 'Student' , 2002); INSERT INTO "main" . "sqlite_sequence" VALUES ( 'Course' , 3002);   -- ---------------------------- -- Table structure for Student -- ---------------------------- DROP TABLE IF EXISTS "main" . "Student" ; CREATE TABLE Student(      userid integer  primary key autoincrement,      username varchar (32),      userage int ,      usersex varchar (32) );   -- ---------------------------- -- Records of Student -- ---------------------------- INSERT INTO "main" . "Student" VALUES (2001, '小明' , 18, '男' ); INSERT INTO "main" . "Student" VALUES (2002, '小红' , 18, '女' );   -- ---------------------------- -- Table structure for Teacher -- ---------------------------- DROP TABLE IF EXISTS "main" . "Teacher" ; CREATE TABLE Teacher(      teacherid integer primary key autoincrement,      teachername varchar (32) );   -- ---------------------------- -- Records of Teacher -- ---------------------------- INSERT INTO "main" . "Teacher" VALUES (1001, '张三' ); INSERT INTO "main" . "Teacher" VALUES (1002, '李四' );

问题：

1、查询“语文”课程比“数学”课程成绩低的所有学生的学号

?

1 2 3 4

select a.userid from ( select userid,score from Mark where courseid = '3001' )a, ( select userid,score from Mark where courseid = '3002' )b where a.userid = b.userid and a.score<b.score;

2、查询平均成绩大于60分的同学的学号和平均成绩

?

1 2 3

select userid, avg (score) from Mark group by userid having avg (score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩

?

1 2 3 4 5 6 7

select s.userid ,s.username ,count_courseid as 选课数, sum_score  as 总成绩 from Student s left join ( select userid, count (courseid ) as count_courseid, sum (score) as sum_score from Mark group by userid  )sc on s.userid = sc.userid;

4、查询姓‘李'的老师的个数：

?

1 2 3

select count (teachername ) from Teacher where teachername  like '张%' ;

5、检索语文课程分数小于60，按分数降序排列的同学学号：

?

1 2 3 4 5

select userid ,score from Mark where courseid = '3001' and score<60 order by score desc;

6、查询学/没学过”张三”老师讲授的任一门课程的学生姓名

?

1 2 3 4 5 6 7 8

select username  from Student where userid  in (      select userid       from Mark,Course,Teacher      where Course.teacherid  = Teacher.teacherid  and Mark.courseid = Course.courseid      and Teacher.teachername = '张三' );

7、查询全部学生选修的课程和课程号和课程名：

?

1 2 3

select courseid ,courseme from Course where courseid  in ( select courseid from Mark group by courseid);

8、检索选修两门课程的学生学号：

?

1 2 3 4

select userid from Mark group by userid having count (8) == 2;

9、查询各个课程及相应的选修人数

?

1	select courseid , count (*) from Course group by courseid ;

10、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩

?

1 2 3 4 5 6 7 8 9 10

select Student.username ,Mark.score from Mark left join Student  on Mark.userid = Student.userid left join Course  on Mark.courseid = Course.courseid left join Teacher  on Course.teacherid  = Teacher.teacherid where Teacher.teachername  = '张三' and Mark.score = ( select max (score) from Mark sc_1 where Mark.courseid = sc_1.courseid);

11、求选了课程的学生人数：

?

1 2	select count (2) from ( select distinct userid from Mark)a;

12、查询课程编号为“语文”且课程成绩在80分以上的学生的学号和姓名

?

1 2 3 4

select Mark.userid,Student.username  from Mark left join Student on Mark.userid  = Student.userid where Mark.courseid  = '3001' and Mark.score>80;

13、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

?

1 2 3 4

select courseid , avg (score) from Mark group by courseid order by avg (score),courseid desc ;

14、查询课程名称为“数学”，且分数高于85的学生名字和分数：

?

1 2 3 4 5

select c.courseme ,Student.userid ,Student.username ,Mark.score from Course c left join Mark on Mark.courseid  = c.courseid LEFT JOIN Student  on Student.userid  = Mark.userid where c.courseme = '数学' and Mark.score>85;

到此这篇关于SQL数据库十四种案例介绍的文章就介绍到这了,更多相关SQL数据库案例内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/qq_34623621/article/details/122110093