小编给大家分享一下SQL数据库的案例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
数据表
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, '李四');问题：
查询“语文”课程比“数学”课程成绩低的所有学生的学号
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;查询平均成绩大于60分的同学的学号和平均成绩
select userid,avg(score) from Markgroup by userid having avg(score)>60;查询所有同学的学号、姓名、选课数、总成绩
select s.userid ,s.username ,count_courseid as 选课数, sum_score as 总成绩from Student sleft join (select userid,count(courseid ) as count_courseid,sum(score) as sum_score from Mark group by userid )scon s.userid = sc.userid;查询姓‘李'的老师的个数：
select count(teachername )from Teacher where teachername like '张%';检索语文课程分数小于60，按分数降序排列的同学学号：
select userid ,scorefrom Markwhere courseid ='3001'and score<60order by score desc;查询学/没学过”张三”老师讲授的任一门课程的学生姓名
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 ='张三');查询全部学生选修的课程和课程号和课程名：
select courseid ,courseme from Course where courseid in (select courseid from Mark group by courseid);检索选修两门课程的学生学号：
select userid from Markgroup by userid having count(8) == 2;查询各个课程及相应的选修人数
select courseid ,count(*) from Course group by courseid ;查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select Student.username ,Mark.scorefrom 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);求选了课程的学生人数：
select count(2) from (select distinct userid from Mark)a;查询课程编号为“语文”且课程成绩在80分以上的学生的学号和姓名
select Mark.userid,Student.username from Mark left join Student on Mark.userid = Student.userid where Mark.courseid = '3001' and Mark.score>80;查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select courseid ,avg(score)from Mark group by courseid order by avg(score),courseid desc;查询课程名称为“数学”，且分数高于85的学生名字和分数：
select c.courseme ,Student.userid ,Student.username ,Mark.scorefrom Course cleft join Mark on Mark.courseid = c.courseid LEFT JOIN Student on Student.userid = Mark.userid where c.courseme = '数学' and Mark.score>85;以上是“SQL数据库的案例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注编程网行业资讯频道！
