米字键

博客园 首页 新随笔 联系 订阅 管理
  59 随笔 :: 5 文章 :: 0 评论 :: 46554 阅读
student数据库
?
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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
create  table student(
 
 sid     int(11) primary key not null,-- 唯一标识，不能为空
 
 sname   char(25) not null,-- 不能为空
 
 age     int(11) not null,-- 不能为空
 
 sex     char(2) not null,-- 不能为空
 
 department char(40) ,
 
 address  char(200) ,
 
 birthplace  varchar(256)
 
);
 
create  table sc(
 
  sid   int(11) not null,
 
  cid   int(11) not null,
 
  grade int(11)
 
);
 
create  table course(
 
  cid    int(11) not null primary key default 4,
 
  cname  char(40),
 
  teacher  char(40)
 
);
 
#以下是插入课程表的数据
 
delete from course ;
 
insert into course values('8108001','math','sandy');
 
insert into course values('8108002','english','sherry');
 
insert into course values('8108003','computer','sandy');
 
insert into course values('8108004','web','sandy');
 
insert into course values('8108005','java','sandy');
 
insert into course values('8108006','C languge','sherry');
 
insert into course values('8108007','python','xiaozhu');
 
insert into course values('8108008','testing','xiaozhu');
 
insert into course values('8108009','linux','sherry');
 
insert into course values('8108010','shell','sherry');
 
#以下是插入成绩级表的数据
 
delete from sc;
 
insert into sc values('3108001','8108010','90');
 
insert into sc values('3108001','8108003','67');
 
insert into sc values('3108002','8108003','54');
 
insert into sc values('3108002','8108010','84');
 
insert into sc values('3108003','8108003','78');
 
insert into sc values('3108004','8108004','89');
 
insert into sc values('3108005','8108006','56');
 
insert into sc values('3108006','8108005','60');
 
insert into sc values('3108007','8108004','79');
 
insert into sc values('3108008','8108008','89');
 
insert into sc values('3108009','8108002','46');
 
insert into sc values('3108010','8108003','87');
 
insert into sc values('3108011','8108001','85');
 
insert into sc values('3108011','8108002','81');
 
insert into sc values('3108012','8108001','97');
 
insert into sc values('3108012','8108002','55');
 
insert into sc values('3108013','8108002','86');
 
insert into sc values('3108013','8108001','71');
 
insert into sc values('3108014','8108002','69');
 
insert into sc values('3108014','8108001','78');
 
insert into sc values('3108015','8108002','67');
 
insert into sc values('3108016','8108001','85');
 
insert into sc values('3108016','8108003','85');
 
insert into sc values('3108016','8108002','85');
 
insert into sc values('3108016','8108004','85');
 
insert into sc values('3108016','8108005','85');
 
insert into sc values('3108016','8108006','80');
 
insert into sc values('3108016','8108007','79');
 
insert into sc values('3108016','8108009','36');
 
insert into sc values('3108016','8108010','78');
 
insert into sc values('3108016','8108008','88');
 
insert into sc values('3108016','8108021','83');
 
insert into sc values('3108015','8108001','85');
 
insert into sc values('3108015','8108003','85');
 
insert into sc values('3108015','8108004','85');
 
insert into sc values('3108015','8108005','85');
 
insert into sc values('3108015','8108006','80');
 
insert into sc values('3108015','8108007','79');
 
insert into sc values('3108015','8108009','36');
 
insert into sc values('3108015','8108010','78');
 
insert into sc values('3108015','8108008','88');
 
insert into sc values('3108015','8108021','83');
 
#以下是插入学生信息数据
 
delete from student;
 
insert into student values('3108001','wang min',21,'f','computer-tec','zhongshan
 
road','jiangsu');
 
insert into student values('3108002','jidu',20,'m','english','zhongshan road','fujian');
 
insert into student values('3108003','wangqing',19,'f','computer-tec','zhongshan
 
road','jiangsu');
 
insert into student values('3108004','liuxin',23,'f','chinese','zhongshan road','shanghai');
 
insert into student values('3108005','ligu',22,'f','computer-tec','zhongshan road','jiangsu');
 
insert into student values('3108006','songjia',19,'m','english','zhongshan road','jiangsu');
 
insert into student values('3108007','huamao',20,'f','chinese','zhongshan road','shanghai');
 
insert into student values('3108008','zhujiao',21,'f','english','zhongshan road','jiangsu');
 
insert into student values('3108009','wuyi',23,'m','computer-tec','zhongshan road','jiangsu');
 
insert into student values('3108010','jilian',18,'f','chinese','zhongshan road','hunan');
 
insert into student values('3108011','linbiao',22,'m','computer-tec','zhongshan
 
road','jiangsu');
 
insert into student values('3108012','maoguai',21,'m','english','zhongshan road','fujian');
 
insert into student values('3108013','rongqi',23,'m','computer-tec','zhongshan
 
road','jiangsu');
 
insert into student values('3108014','sangzi',20,'f','chinese','zhongshan road','hunan');
 
insert into student values('3108015','surui',16,'f','computer-tec','zhongshan road','fujian');
 
insert into student values('3108016','liushaoqi',24,'m','english','zhongshan road','hunan');
 
commit;

　　

?
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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
-- 查询地址
select DISTINCT address FROM student;
-- 查询名字，数学成绩，英语查询
SELECT *from sc
select *from course,sc,student;
-- 查询名字，数学成绩查询
select c.cid,stu.sname,cname,sc.grade  from student stu,sc,course c where stu.sid=sc.sid and sc.cid=c.cid and cname='math'
 
-- 查询年龄大于20岁的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age > 20;
 
-- 查询年龄大于等于20岁的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age >= 20;
-- 查询年龄大于等于20岁的学员并且年龄小30岁的的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age >= 20 and age <=30;
select sid,sname,age,sex,department,address,birthplace from student where age BETWEEN 20 and 30;
-- 查询年龄等于18岁的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age = 18;
-- 查询年龄不等于18岁的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age <> 18
-- 查询年龄等于18岁或者 年龄小于20岁或者年龄等于22岁的学员信息
Select sid,sname,age,sex,department,address,birthplace from student where age = 18 or age =20 or age =22;
select sid,sname,age,sex,department,address,birthplace from student where age in (18,20,22);
-- 查询学习英语的学生
select stu.sname,co.cname,sc.grade from student stu, sc, course co where stu.sid=sc.sid and co.cid=sc.cid and co.cname='english'
 
select c.cid,cname,sc.grade from sc,course c where  sc.cid=c.cid and cname='math';
select c.cid,cname from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and teacher='sandy';
 
 
 
-- sandy老师所教的课程号、课程名称；
select stu.sname,co.cid,cname,teacher from student stu ,sc,course co where stu.sid=sc.sid and sc.cid=co.cid and teacher='sandy';
-- 在学生表中按性别排序，且男在前女在后显示记录。
select sid,sname,age,sex,department,address,birthplace from student order by sex desc;
-- “wuyi”所选修的全部课程名称；
select stu.sname,course.cname from student stu,sc,course where stu.sid=sc.sid and sc.cid=sc.cid and stu.sname='武邑' ;
-- -所有成绩都在80分以上的学生姓名及所在系；
select DISTINCT stu.sname,stu.department,sc.grade from student stu,sc,course co where stu.sid = sc.sid and sc.cid = co.cid and sc.sid not in (select sid from sc where grade<80);
-- 没有选修“english”课的学生的姓名；
select sname from student where   not sid in(select sid from sc where cid in (select cid from course where cname='english'));
-- 与“jilian”同乡的男生姓名及所在系；
select sname,birthplace,department from student where birthplace in (select birthplace from student where sname='jilian')
 
-- 英语成绩比数学成绩好的学生;
 
 
SELECT
    *
FROM
    student s,
    (
        SELECT
            t.sid,
            grade
        FROM
            sc t,
            course c,
            student s
        WHERE
            c.cid = t.cid
        AND s.sid = t.sid
        AND cname = 'english'
    ) a,
    (
        SELECT
            t1.sid,
            grade
        FROM
            sc t1,
            course c1,
            student s1
        WHERE
            c1.cid = t1.cid
        AND s1.sid = t1.sid
        AND cname = 'math'
    ) b
WHERE
    s.sid = a.sid
AND a.sid = b.sid
AND a.grade > b.grade;
 
 
-- 选修同一门课程时，女生比所有男生成绩都好的学生名单；
select * from student s,sc t,student s1,sc t1 where s.sid=t.sid and s1.sid=t1.sid and s.sid=s1.sid and s.sex='f'and s1.sex='m' and
 
t.cid=t1.cid and t.grade>t1.grade;
-- 至少选修两门及以上课程的学生姓名、性别；
 
select sname,sex from student s,sc t where s.sid=t.sid having count(t.cid)>=2
-- 选修了sandy老师所讲课程的学生人数；
select count(sid) from student s where sid in (select distinct sid from sc where cid in (select cid from course where
 
teacher='sandy'));
 
-- 本校学生中有学生姓名/性别重复的同学，请编写脚本查出本校所有学生的信息，显示学号，姓名，性别，总成绩，对于姓名/性别重复的学生信息只取总成绩最高的那一条记录。
select s.sid,sname,sex,sum(grade) from student s,sc t where s.sid=t.sid
 
group by  s.sid,sname;
-- “english”课程得最高分的学生姓名、性别、所在系；
select sname,sex,department from student where sid = (select sid from sc where grade =(select max(grade) from sc where cid= (select cid
 
from course where cname='english')));
 
-- 统计班级有多少学生
select count(*) from student;
 
-- 统计数学的做高分学生名称
select sid,sname,age,department from student where sid=( select sid from sc where grade=(
select max(grade) from sc where cid=(
select cid from  course where cname='math')));
-- 查询数学最低分的学生
select sid,sname,age,department from student where sid=( select sid from sc where grade=(
select min(grade) from sc where cid=(
select cid from  course where cname='math')));
-- 查询数学的总成绩
select sum(grade) from sc where cid=(
select cid from  course where cname='math')
-- 查询数学的平均份
select avg(grade) from sc where cid=(
select cid from  course where cname='math')
-- 查询男同学女同学的平均分
select sex  from student where sid=(
select sid from sc where grade=(
select avg(grade) from sc where cid=(
select cid from  course where cname='math'))) GROUP BY sex;
select*from student;
-- 查询 从0开始查询下，查询3条数据
select * from student limit 0,3
-- 每页显示3条数据，查询第一页的数据
select * from student limit 0,3
-- 每页显示3条数据，查询第二页的数据
select * from student limit 3,3
-- 每页显示3条数据，查询第三页的数据
select * from student limit 6,3
-- 起始索引=（当前页码-1）*每页显示的条数

　　

posted on   米字键  阅读(206)  评论(0)  编辑  收藏  举报
相关博文：
·  部门数据库
·  数据库 备份数据命令
·  sql练习-1
·  SQLServer练习
·  数据库语句-mysql
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示
SQL AI 助手