一区二区三区在线-一区二区三区亚洲视频-一区二区三区亚洲-一区二区三区午夜-一区二区三区四区在线视频-一区二区三区四区在线免费观看

服務器之家:專注于服務器技術及軟件下載分享
分類導航

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

延伸 · 閱讀

精彩推薦
主站蜘蛛池模板: 91精品国产综合久久精品 | 欧美精品v欧洲高清 | 国产成人精品免费午夜 | 久久久久青草大香线综合精品 | 成在线人免费视频一区二区三区 | 动漫美女被吸乳 | tube8最近日本护士 | 粉嫩极品国产在线观看免费 | 国产精品怡红院在线观看 | 国产欧美一区视频在线观看 | 美女机机对机机的视频(免费) | 久久精品观看影院2828 | 精品国产免费一区二区三区 | 大陆黄色片 | 日韩人成免费网站大片 | 精品欧美一区二区精品久久 | 无遮18禁在线永久免费观看挡 | 办公室出轨秘书高h | 福利片福利一区二区三区 | 91国语精品自产拍在线观看一 | 无码骚夜夜精品 | 欧美精品亚洲精品日韩1818 | 国产视频二 | 精品久久久久久影院免费 | 好湿好紧太硬了我太爽了h 好湿好滑好硬好爽好深视频 | 国产精品四虎在线观看免费 | 亚洲免费二区 | 性xxx免费| 亚洲人成激情在线播放 | 国产精品久久久久久爽爽爽 | 男男playh片在线观看 | 蜜汁肉桃全文免费阅读 | 哇嘎在线精品视频在线观看 | chinese老太grandma | 九九99热 | 美女草b| 国产高清dvd | 国产成人咱精品视频免费网站 | 国产主播福利在线观看 | 日韩网站在线观看 | 国模娜娜一区二区三区 |