服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - SQL数据库十四种案例介绍

SQL数据库十四种案例介绍

2022-02-13 19:19C君莫笑 Mysql

大家好，本篇文章主要讲的是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

  • SQL
  • sql数据库

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1449 　 1053 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1862 　 1864 　 1146 　 2003 　 1175 　 1215 　 1615 　 1054 　 2006 　 1130 　 1366 　 10048 　 28000 　 1786 　 1135 　 1577 　 1665 　 1067 　 1093 　
    © 2019-2024 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图