备案控制台
开发者社区 数据库 文章 正文

sql语句练习

2017-11-10 975
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

表结构图如下：

wKiom1m56N-jqdsBAAB-q8SwhwE496.png




创建表和插入数据：

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
SET  FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP  TABLE  IF EXISTS `class`;
CREATE  TABLE  `class` (
   `cid`  int (11)  NOT  NULL  AUTO_INCREMENT,
   `caption`  varchar (32)  NOT  NULL ,
   PRIMARY  KEY  (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5  DEFAULT  CHARSET=utf8;
 
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT  INTO  `class`  VALUES  ( '1' ,  '三年二班' );
INSERT  INTO  `class`  VALUES  ( '2' ,  '三年三班' );
INSERT  INTO  `class`  VALUES  ( '3' ,  '一年二班' );
INSERT  INTO  `class`  VALUES  ( '4' ,  '二年九班' );
 
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP  TABLE  IF EXISTS `course`;
CREATE  TABLE  `course` (
   `cid`  int (11)  NOT  NULL  AUTO_INCREMENT,
   `cname`  varchar (32)  NOT  NULL ,
   `teacher_id`  int (11)  NOT  NULL ,
   PRIMARY  KEY  (`cid`),
   KEY  `fk_course_teacher` (`teacher_id`),
   CONSTRAINT  `fk_course_teacher`  FOREIGN  KEY  (`teacher_id`)  REFERENCES  `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5  DEFAULT  CHARSET=utf8;
 
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT  INTO  `course`  VALUES  ( '1' ,  '生物' ,  '1' );
INSERT  INTO  `course`  VALUES  ( '2' ,  '物理' ,  '2' );
INSERT  INTO  `course`  VALUES  ( '3' ,  '体育' ,  '3' );
INSERT  INTO  `course`  VALUES  ( '4' ,  '美术' ,  '2' );
 
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP  TABLE  IF EXISTS `score`;
CREATE  TABLE  `score` (
   `sid`  int (11)  NOT  NULL  AUTO_INCREMENT,
   `student_id`  int (11)  NOT  NULL ,
   `course_id`  int (11)  NOT  NULL ,
   `num`  int (11)  NOT  NULL ,
   PRIMARY  KEY  (`sid`),
   KEY  `fk_score_student` (`student_id`),
   KEY  `fk_score_course` (`course_id`),
   CONSTRAINT  `fk_score_course`  FOREIGN  KEY  (`course_id`)  REFERENCES  `course` (`cid`),
   CONSTRAINT  `fk_score_student`  FOREIGN  KEY  (`student_id`)  REFERENCES  `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53  DEFAULT  CHARSET=utf8;
 
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT  INTO  `score`  VALUES  ( '1' ,  '1' ,  '1' ,  '10' );
INSERT  INTO  `score`  VALUES  ( '2' ,  '1' ,  '2' ,  '9' );
INSERT  INTO  `score`  VALUES  ( '5' ,  '1' ,  '4' ,  '66' );
INSERT  INTO  `score`  VALUES  ( '6' ,  '2' ,  '1' ,  '8' );
INSERT  INTO  `score`  VALUES  ( '8' ,  '2' ,  '3' ,  '68' );
INSERT  INTO  `score`  VALUES  ( '9' ,  '2' ,  '4' ,  '99' );
INSERT  INTO  `score`  VALUES  ( '10' ,  '3' ,  '1' ,  '77' );
INSERT  INTO  `score`  VALUES  ( '11' ,  '3' ,  '2' ,  '66' );
INSERT  INTO  `score`  VALUES  ( '12' ,  '3' ,  '3' ,  '87' );
INSERT  INTO  `score`  VALUES  ( '13' ,  '3' ,  '4' ,  '99' );
INSERT  INTO  `score`  VALUES  ( '14' ,  '4' ,  '1' ,  '79' );
INSERT  INTO  `score`  VALUES  ( '15' ,  '4' ,  '2' ,  '11' );
INSERT  INTO  `score`  VALUES  ( '16' ,  '4' ,  '3' ,  '67' );
INSERT  INTO  `score`  VALUES  ( '17' ,  '4' ,  '4' ,  '100' );
INSERT  INTO  `score`  VALUES  ( '18' ,  '5' ,  '1' ,  '79' );
INSERT  INTO  `score`  VALUES  ( '19' ,  '5' ,  '2' ,  '11' );
INSERT  INTO  `score`  VALUES  ( '20' ,  '5' ,  '3' ,  '67' );
INSERT  INTO  `score`  VALUES  ( '21' ,  '5' ,  '4' ,  '100' );
INSERT  INTO  `score`  VALUES  ( '22' ,  '6' ,  '1' ,  '9' );
INSERT  INTO  `score`  VALUES  ( '23' ,  '6' ,  '2' ,  '100' );
INSERT  INTO  `score`  VALUES  ( '24' ,  '6' ,  '3' ,  '67' );
INSERT  INTO  `score`  VALUES  ( '25' ,  '6' ,  '4' ,  '100' );
INSERT  INTO  `score`  VALUES  ( '26' ,  '7' ,  '1' ,  '9' );
INSERT  INTO  `score`  VALUES  ( '27' ,  '7' ,  '2' ,  '100' );
INSERT  INTO  `score`  VALUES  ( '28' ,  '7' ,  '3' ,  '67' );
INSERT  INTO  `score`  VALUES  ( '29' ,  '7' ,  '4' ,  '88' );
INSERT  INTO  `score`  VALUES  ( '30' ,  '8' ,  '1' ,  '9' );
INSERT  INTO  `score`  VALUES  ( '31' ,  '8' ,  '2' ,  '100' );
INSERT  INTO  `score`  VALUES  ( '32' ,  '8' ,  '3' ,  '67' );
INSERT  INTO  `score`  VALUES  ( '33' ,  '8' ,  '4' ,  '88' );
INSERT  INTO  `score`  VALUES  ( '34' ,  '9' ,  '1' ,  '91' );
INSERT  INTO  `score`  VALUES  ( '35' ,  '9' ,  '2' ,  '88' );
INSERT  INTO  `score`  VALUES  ( '36' ,  '9' ,  '3' ,  '67' );
INSERT  INTO  `score`  VALUES  ( '37' ,  '9' ,  '4' ,  '22' );
INSERT  INTO  `score`  VALUES  ( '38' ,  '10' ,  '1' ,  '90' );
INSERT  INTO  `score`  VALUES  ( '39' ,  '10' ,  '2' ,  '77' );
INSERT  INTO  `score`  VALUES  ( '40' ,  '10' ,  '3' ,  '43' );
INSERT  INTO  `score`  VALUES  ( '41' ,  '10' ,  '4' ,  '87' );
INSERT  INTO  `score`  VALUES  ( '42' ,  '11' ,  '1' ,  '90' );
INSERT  INTO  `score`  VALUES  ( '43' ,  '11' ,  '2' ,  '77' );
INSERT  INTO  `score`  VALUES  ( '44' ,  '11' ,  '3' ,  '43' );
INSERT  INTO  `score`  VALUES  ( '45' ,  '11' ,  '4' ,  '87' );
INSERT  INTO  `score`  VALUES  ( '46' ,  '12' ,  '1' ,  '90' );
INSERT  INTO  `score`  VALUES  ( '47' ,  '12' ,  '2' ,  '77' );
INSERT  INTO  `score`  VALUES  ( '48' ,  '12' ,  '3' ,  '43' );
INSERT  INTO  `score`  VALUES  ( '49' ,  '12' ,  '4' ,  '87' );
INSERT  INTO  `score`  VALUES  ( '52' ,  '13' ,  '3' ,  '87' );
 
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP  TABLE  IF EXISTS `student`;
CREATE  TABLE  `student` (
   `sid`  int (11)  NOT  NULL  AUTO_INCREMENT,
   `gender`  char (1)  NOT  NULL ,
   `class_id`  int (11)  NOT  NULL ,
   `sname`  varchar (32)  NOT  NULL ,
   PRIMARY  KEY  (`sid`),
   KEY  `fk_class` (`class_id`),
   CONSTRAINT  `fk_class`  FOREIGN  KEY  (`class_id`)  REFERENCES  `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17  DEFAULT  CHARSET=utf8;
 
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT  INTO  `student`  VALUES  ( '1' ,  '男' ,  '1' ,  '理解' );
INSERT  INTO  `student`  VALUES  ( '2' ,  '女' ,  '1' ,  '钢蛋' );
INSERT  INTO  `student`  VALUES  ( '3' ,  '男' ,  '1' ,  '张三' );
INSERT  INTO  `student`  VALUES  ( '4' ,  '男' ,  '1' ,  '张一' );
INSERT  INTO  `student`  VALUES  ( '5' ,  '女' ,  '1' ,  '张二' );
INSERT  INTO  `student`  VALUES  ( '6' ,  '男' ,  '1' ,  '张四' );
INSERT  INTO  `student`  VALUES  ( '7' ,  '女' ,  '2' ,  '铁锤' );
INSERT  INTO  `student`  VALUES  ( '8' ,  '男' ,  '2' ,  '李三' );
INSERT  INTO  `student`  VALUES  ( '9' ,  '男' ,  '2' ,  '李一' );
INSERT  INTO  `student`  VALUES  ( '10' ,  '女' ,  '2' ,  '李二' );
INSERT  INTO  `student`  VALUES  ( '11' ,  '男' ,  '2' ,  '李四' );
INSERT  INTO  `student`  VALUES  ( '12' ,  '女' ,  '3' ,  '如花' );
INSERT  INTO  `student`  VALUES  ( '13' ,  '男' ,  '3' ,  '刘三' );
INSERT  INTO  `student`  VALUES  ( '14' ,  '男' ,  '3' ,  '刘一' );
INSERT  INTO  `student`  VALUES  ( '15' ,  '女' ,  '3' ,  '刘二' );
INSERT  INTO  `student`  VALUES  ( '16' ,  '男' ,  '3' ,  '刘四' );
 
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP  TABLE  IF EXISTS `teacher`;
CREATE  TABLE  `teacher` (
   `tid`  int (11)  NOT  NULL  AUTO_INCREMENT,
   `tname`  varchar (32)  NOT  NULL ,
   PRIMARY  KEY  (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6  DEFAULT  CHARSET=utf8;
 
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT  INTO  `teacher`  VALUES  ( '1' ,  '张磊老师' );
INSERT  INTO  `teacher`  VALUES  ( '2' ,  '李平老师' );
INSERT  INTO  `teacher`  VALUES  ( '3' ,  '刘海燕老师' );
INSERT  INTO  `teacher`  VALUES  ( '4' ,  '朱云海老师' );
INSERT  INTO  `teacher`  VALUES  ( '5' ,  '李杰老师' );
SET  FOREIGN_KEY_CHECKS=1;


sql练习：

1、查询所有的课程的名称以及对应的任课老师姓名

1
select  course.cname 课程,teacher.tname 讲师  from  course  inner  join  teacher  on  course.teacher_id=teacher.tid;

 

2、查询学生表中男女生各有多少人

1
select  gender 性别, count (*) 人数  from  student  group  by  gender;

 

3、查询物理成绩等于100的学生的姓名

1
select  sname 姓名  from  student  where  sid  in ( select  student_id  from  score  inner  join  course  on  score.course_id=course.cid  where  course.cname= '物理'  and  score.num=100);

 

4、查询平均成绩大于八十分的同学的姓名和平均成绩

1
select  student.sname 姓名, avg (score.num) 平均分  from  score  left  join  student  on  score.student_id=student.sid  group  by  student_id;


5、查询所有学生的学号，姓名，选课数，总成绩

1
select  student.sid 学号,student.sname 姓名, count (score.course_id) 课程数, sum (score.num) 总成绩  from  student  left  join  score  on  student.sid=score.student_id  group  by  student.sid;


6、 查询姓李老师的个数

1
select  tname 姓李的老师  from  teacher  where  tname  like  "李%" ;


7、 查询没有报李平老师课的学生姓名

1
select  sid 学号,sname 姓名  from  student  where  sid  not  in  ( select  student_id  from  score  where  course_id  in ( select  course.cid  from  teacher  left  join  course  on  teacher.tid=course.teacher_id  where  teacher.tname= "李平老师" ));


8、 查询物理课程比生物课程高的学生的学号

1
2
3
4
5
6
7
8
9
select  phy.student_id 学号  from 
( select  score.student_id,score.num  from  score 
left  join  course  on  score.course_id=course.cid 
where  cname= "物理" )  as  phy 
inner  join 
( select  score.student_id,score.num  from  score 
left  join  course  on  score.course_id=course.cid 
where  cname= "生物" )  as  bio 
on  phy.student_id=bio.student_id  where  phy.num>bio.num


9、 查询没有同时选修物理课程和体育课程的学生姓名

1
2
3
4
5
6
7
  select  sname 姓名  from  student  where  sid  not  in
( select  student_id  from  score  left  join  course 
     on  score.course_id=course.cid
     where  cname= "体育"  or  cname= "物理"
     group  by  student_id
     having  count (course_id)>1)
);


10、查询挂科超过两门(包括两门)的学生姓名和班级

1
2
3
4
5
select  sname,caption  from  student  inner  join  class  on
student.class_id=class.cid  where  student.sid  in
( select  student_id  from  score  where  score.num<60
     group  by  student_id
     having  count (student_id)>=2);


11 、查询选修了所有课程的学生姓名

1
2
3
4
select  sname 姓名  from  student  where  sid  in
     ( select  student_id  from  score
     group  by  student_id
     having  count (student_id) = ( select  count (cid)  from  course));


12、查询李平老师教的课程的所有成绩记录

1
2
3
4
5
select  course.cname 课程,score.num 成绩  from  score  inner  join  course
on  course_id=cid
where  course_id  in 
( select  cid  from  course  left  join  teacher  on  teacher_id=tid
where  tname  like "李平%" );

 

13、查询全部学生都选修了的课程号和课程名

1
2
3
4
select  cid,cname  from  course  where  cid  in
( select  course_id  from  score
group  by  course_id
having  count (course_id) = ( select  count (sid)  from  student));


14、查询每门课程被选修的次数

1
2
3
select  course.cname 课程, count (course_id) 选修次数 
from  score  inner  join  course  on  score.course_id=course.cid
group  by  course_id;


15、查询只选修了一门课程的学生姓名和学号

1
2
3
4
5
select  sid 学号,sname 姓名  from  student
where  sid  in
( select  student_id  from  score 
group  by  student_id
having  count (student_id)=1);


16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

1
2
3
4
select  sname 姓名, sum (num) 总分  from  score  inner  join  student
on  score.student_id=student.sid
group  by  student_id
order  by  sum (num)  desc ;


17、查询平均成绩大于85的学生姓名和平均成绩

1
2
3
4
5
select  student.sname 姓名, avg (num) 平均成绩 
from  student  inner  join  score
on  student.sid=student_id
group  by  student_id
having  avg (num)>85;


18、查询生物成绩不及格的学生姓名和对应生物分数

1
2
3
4
5
select  sname 姓名,num 分数  from  score  inner  join  student 
on  student_id=student.sid
where  course_id  in  ( select  cid  from  course 
where  cname= "生物" )
having  num <60;


19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

1
2
3
4
5
6
7
8
9
10
select  student.sname 姓名, avg (num) 平均成绩 
from  score  inner  join  student
on  score.student_id=student.sid
where  course_id  in 
( select  cid  from  course  inner  join  teacher
on   teacher_id=tid
where  teacher.tname  like  "李平%"  )
group  by  student_id
order  by  avg (num)  desc
limit 1

本文转自lyndon博客51CTO博客，原文链接http://blog.51cto.com/lyndon/1965155如需转载请自行联系原作者

迟到的栋子
文章标签：
SQL
关键词：
SQL练习
SQL语句
SQL语句练习
科技探索者
目录
相关文章
穆雄雄.
|
3天前
|
SQL Perl
PL/SQL经典练习
PL/SQL经典练习
穆雄雄.
15 0
让线程再跑一会
|
3天前
|
SQL 分布式计算 数据挖掘
Hive SQL初级练习（30题）
Hive SQL初级练习（30题）
让线程再跑一会
331 0
爱吃豆的土豆
|
7月前
|
SQL 存储 Java
Mybatis实战练习四【单个条件（动态SQL）&添加数据】（下）
Mybatis实战练习四【单个条件（动态SQL）&添加数据】
爱吃豆的土豆
54 0
HelloDam
|
7月前
|
SQL 存储 关系型数据库
【MYSQL高级】Mysql 表的七种连接方式【附带练习sql】
【MYSQL高级】Mysql 表的七种连接方式【附带练习sql】
HelloDam
98 0
爱吃豆的土豆
|
7月前
|
SQL Java 数据库连接
Mybatis实战练习四【单个条件（动态SQL）&添加数据】（上）
Mybatis实战练习四【单个条件（动态SQL）&添加数据】
爱吃豆的土豆
42 0
恬0917
|
3天前
|
SQL
SQL语句多表查询练习
SQL语句多表查询练习
恬0917
13 0
飞翔的佩奇
|
3天前
|
SQL 关系型数据库 MySQL
mysql查询语句练习总结(涵盖所有sql语法)
mysql查询语句练习总结(涵盖所有sql语法)
飞翔的佩奇
45 0
大数据流动
|
6月前
|
SQL 算法 JavaScript
在线就能用的 SQL 练习平台（附SQL学习文档）
在线就能用的 SQL 练习平台（附SQL学习文档）
大数据流动
186 0
阿芒96
|
10月前
|
SQL 数据挖掘 数据库
牛客网之SQL刷题练习——一个实用的网站
牛客网之SQL刷题练习——一个实用的网站
阿芒96
270 0
姚远OracleACE
|
11月前
|
SQL 数据库
学通4中数据库SQL教程练习和答案
编写一个SQL语句，输出下面的结果
姚远OracleACE
93 0

热门文章

最新文章

  • 1
    一文解析 ODPS SQL 任务优化方法原理
  • 2
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 3
    IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
  • 4
    NL2SQL进阶系列(4)：ConvAI、DIN-SQL等16个业界开源应用实践详解[Text2SQL]
  • 5
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 6
    【MySQL】SQL优化
  • 7
    sql 数据库优化
  • 8
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
  • 9
    Oracle的PL/SQL表达式：数据的魔法公式
  • 10
    sql语句创建数据库
  • 1
    [MySQL]SQL优化之索引的使用规则
    44
  • 2
    [MySQL] SQL优化之性能分析
    39
  • 3
    sql文件批处理程序-java桌面应用
    28
  • 4
    《牛客笔试选择题》sql错题集
    32
  • 5
    AI代码生成器——AI2sql
    136
  • 6
    10个高级的 SQL 查询技巧
    63
  • 7
    【问题】Cause: java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK
    118
  • 8
    解决SQL报错：索引中丢失IN或OUT參数
    26
  • 9
    Mybatis拦截器实现带参数SQL语句打印
    47
  • 10
    Flink CDC产品常见问题之sql运行中查看日志任务失败如何解决
    137

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考