mysql常见查询练习题

2023-08-07 21:46:40

#建学生信息表student
create table student
(
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)

);
#建立教师表
create table teacher
(
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null

);
#建立课程表course
create table course
(
cno varchar(20) not null primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)

);
#建立成绩表
create table score
(
sno varchar(20) not null primary key,
foreign key(sno) references student(sno),
cno varchar(20) not null,
foreign key(cno) references course(cno),
degree decimal

);

#添加学生信息
insert into student values('108','曾华','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王丽','女','1976-01-23','95033');
insert into student values('101','李军','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陆君','男','1974-06-03','95031');




#添加教师表
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');

#添加课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
#添加成绩表

insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','3-105','64');
insert into score values('105','3-105','91');
insert into score values('109','3-105','78');
insert into score values('103','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');

1、查询Student表中的所有记录的Sname、Ssex和Class列。

1	`select` `Sname,Ssex,Class` `from` `student`

2、查询教师所有的单位即不重复的Depart列。

1	`select` `distinct` `Depart` `from` `teacher`

3、查询Student表的所有记录。

1	`select` `*` `from` `student`

4、查询Score表中成绩在60到80之间的所有记录。

1	`select` `*` `from` `Score` `where` `Degree` `between` `60` `and` `80`

5、查询Score表中成绩为85，86或88的记录。

1	`select` `*` `from` `Score` `where` `Degree` `in(85,86,88)`

6、查询Student表中“95031”班或性别为“女”的同学记录。

1	`select` `*` `from` `Student` `where` `class='95031'` `or` `Ssex='女'`

7、以Class降序查询Student表的所有记录。

1	`select` `*` `from` `student` `order` `by` `class` `desc`

8、以Cno升序、Degree降序查询Score表的所有记录。

1	`select` `*` `from` `Score` `order` `by` `cno` `asc,degree` `desc`

9、查询“95031”班的学生人数。

1	`select` `count(*)` `from` `student` `where` `class='95031'`

10、查询Score表中的最高分的学生学号和课程号。（子查询或者排序）

1	`select` `Sno,Cno` `from` `Score` `where` `Degree=(select` `max(Degree)` `from` `Score)`

1	`select` `Sno,Cno` `from` `Score` `order` `by` `Degree` `desc` `limit 0,1`

11、查询每门课的平均成绩。

1	`select` `Cno,avg(degree)` `from` `Score` `group` `by` `Cno`

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

1	`select` `avg(Degree)` `from` `score` `where` `Cno` `like` `'3%'` `and` `Cno` `in` `(select` `Cno` `from` `score` `group` `by` `Cno` `having` `count(*)>=5) 用in` `不用= 是因为可能会有多个`

1	`简单写法：select` `avg(Degree)` `from` `score` `where` `Cno` `like` `'3%'` `and` `group` `by` `Cno` `having` `count(*)>=5`

13、查询分数大于70，小于90的Sno列。

1	`select` `Sno` `from` `Score` `where` `degree>70` `and` `degree<90`

14、查询所有学生的Sname、Cno和Degree列。

1	`select` `Sname, Cno,Degree` `from` `Score , student` `where` `Score.Sno=student.Sno`

15、查询所有学生的Sno、Cname和Degree列。

1	`select` `Sno,Cname,Degree` `from` `Score , Course` `where` `Score.Cno=Course.Cno`

16、查询所有学生的Sname、Cname和Degree列。

1	`select` `Sname,Cname,Degree` `from` `student,course,score` `where` `student.Sno=score.Sno` `and` `course.Cno=score.Cno`

1	`join` `..` `on` `写法：select` `Sname,Cname,Degree` `from` `student` `join` `score` `on` `student.Sno=score.Sno` `join` `course` `on` `course.Cno=score.Cno`

17、查询“95033”班学生的平均分。

1	`select` `avg(degree)` `as` `'class=95033'` `from` `Score` `where` `Sno` `in` `(select` `Sno` `from` `Student` `where` `Class='95033'` `)`

18、假设使用如下命令建立了一个grade表：

create table grade(low int(3),upp int(3),rank char(1))

insert into grade values(90,100,’A’)

insert into grade values(80,89,’B’)

insert into grade values(70,79,’C’)

insert into grade values(60,69,’D’)

insert into grade values(0,59,’E’)

现查询所有同学的Sno、Cno和rank列。

1	`select` `Sno,Cno,rank` `from` `Score,grade` `where` `degree` `between` `low` `and` `upp`

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

1	`<span style="color: #000000; font-size: 15px">109同学，选修是3-105课的</span>`

1	`select` `*` `from` `score` `where` `Cno='3-105'` `and` `degree>(select` `max(degree )` `from` `Score` `where` `Sno='109'` `and` `Cno='3-105'` `)`

1	`<span style="font-size: 15px">109同学，没有选修3-105课</span>`

1	`select` `*` `from` `score` `where` `Cno='3-105'` `and` `degree>(select` `max(degree )` `from` `Score` `where` `Sno='109')`

and degree<( select max(degree ) from Score where sno in (select Sno from score group by Sno having count(*)>1))

1	`选了多门课程并且是这个课程下不是最高分的`

1	`select` `` `from` `score a` `where` `Sno` `in` `(select` `Sno` `from` `score` `group` `by` `Sno` `having` `count()>1)` `and` `degree<(` `select` `max(degree )` `from` `Score b` `where` `b.cno = a.cno)`

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

1	`Select` `*` `from` `score` `where` `degree>(select` `degree` `from` `Score` `where` `Sno='109'` `and` `Cno='3-105'` `)`

22、查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

1	`select` `sno,sname,sbirthday` `from` `student` `where` `year(sbirthday) = (select` `year(sbirthday)` `from` `student` `where` `sno='108')`

1	`select` `sno,sname,sbirthday` `from` `student` `where` `year(sbirthday) = (select` `year(sbirthday)` `from` `student` `where` `sno='101')`

23、查询“张旭“教师任课的学生成绩。

1	`select` `Sno,degree` `from` `score,Course` `where` `score.Cno=Course.Cno` `and` `Course.Tno= (select` `Tno` `from` `Teacher` `where` `Tname='张旭'` `)`

1	`select` `degree` `from` `score` `where` `Cno` `in` `(select` `cno` `from` `course` `where` `Tno= (select` `Tno` `from` `Teacher` `where` `Tname='张旭'` `) )`

24、查询选修某课程的同学人数多于5人的教师姓名。

1	`select` `Tname` `from` `Teacher, Course` `where` `Teacher.Tno=Course.Tno` `and` `Course.Cno =(select` `Cno` `from` `Score` `group` `by` `Cno` `having` `count(*)>5)`

1	`select` `Tname` `from` `Teacher` `where` `tno=(` `select` `Tno` `from` `Course` `where` `cno=(` `select` `Cno` `from` `Score` `group` `by` `Cno` `having` `count(*)>5 ))`

25、查询95033班和95031班全体学生的记录。

1	`select` `*` `from` `student` `where` `class` `in` `('95033','95031')`

26、查询存在有85分以上成绩的课程Cno.

1	`select` `Cno` `from` `score` `where` `degree>85`

27、查询出“计算机系“教师所教课程的成绩表。

1	`select` `*` `from` `course` `where` `cno` `in` `(select` `cno` `from` `course` `where` `tno` `in` `(select` `tno` `from` `teacher` `where` `Depart='计算机系'))`

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

1 select Tname,Prof from Teacher where Depart ='计算机系' and Prof not in( select Prof from Teacher where Depart ='电子工程系')<br>union <br>select Tname,Prof from Teacher where Depart ='电子工程系' and Prof not in( select Prof from Teacher where Depart ='计算机系')

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

any:代表括号中任意一个成绩就可以

1	`select` `Cno,Sno,Degree` `from` `score` `where` `cno='3-105'` `and` `degree >any(select` `degree` `from` `score` `where` `cno='3-245'` `)` `order` `by` `degree` `desc`

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

all:代表括号中的所有成绩

1	`select` `Cno,Sno,Degree` `from` `score` `where` `cno='3-105'` `and` `degree >all(select` `degree` `from` `score` `where` `cno='3-245'` `)` `order` `by` `degree` `desc`

31、查询所有教师和同学的name、sex和birthday.

1	`select` `tname,tsex,tbirthday` `from` `Teacher` `union` `select` `sname,ssex,sbirthday` `from` `Student`

32、查询所有“女”教师和“女”同学的name、sex和birthday.

1	`select` `Tname,Tsex,Tbirthday` `from` `Teacher` `where` `Tsex='女'` `union` `select` `Sname,Ssex,Sbirthday` `from` `Student` `where` `Ssex='女'`

33、查询成绩比该课程平均成绩低的同学的成绩表。

1	`select` `*` `from` `score a` `where` `degree < (select` `avg(degree)` `from` `score b` `where` `b.cno=a.cno)`

34、查询所有任课教师的Tname和Depart.

1	`select` `Tname,Depart` `from` `Teacher` `where` `tno` `in` `(select` `tno` `from` `course )`

35 、查询所有未讲课的教师的Tname和Depart.

1	`select` `Tname,Depart` `from` `Teacher` `where` `Tno` `not` `in` `(select` `Tno` `from` `Course` `where` `cno` `in` `(select` `cno` `from` `score ))`

36、查询至少有2名男生的班号。

1	`select` `class` `from` `student` `where` `ssex='男'` `group` `by` `class` `having` `count(*)>1`

37、查询Student表中不姓“王”的同学记录。

1	`select` `*` `from` `Student` `where` `Sname` `not` `like` `'王%%'`

38、查询Student表中每个学生的姓名和年龄。

1	`select` `Sname,` `year(now())-year(sbirthday)` `from` `Student`

39、查询Student表中最大和最小的Sbirthday日期值。

1	`select` `Max(Sbirthday ),Min(Sbirthday )` `from` `Student`

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

1	`select` `*` `from` `Student` `order` `by` `class` `desc, Sbirthday`

41、查询“男”教师及其所上的课程。

1	`select` `Tname,Cname` `from` `course,teacher` `where` `course.tno= teacher.tno` `and` `teacher.Tsex='男'`

42、查询最高分同学的Sno、Cno和Degree列。

1	`select` `Sno,Cno,Degree` `from` `score` `where` `degree=(select` `max(degree)` `from` `score)`

排序写法：

1	`select` `Sno,Cno,Degree` `from` `score` `order` `by` `degree` `desc` `limit 0,1`

43、查询和“李军”同性别的所有同学的Sname.

1	`select` `Sname` `from` `Student` `where` `Ssex = (select` `Ssex` `from` `Student` `where` `Sname='李军')`

44、查询和“李军”同性别并同班的同学Sname.

1	`select` `Sname` `from` `Student` `where` `Ssex = (select` `Ssex` `from` `Student` `where` `Sname='李军'` `)` `and` `class=(` `select` `class` `from` `student` `where` `Sname='李军')`

45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

1	`select` `Sno,Cno,degree` `from` `score` `where` `Cno=(` `select` `Cno` `from` `course` `where` `Cname='计算机导论')` `and` `Sno` `in` `(select` `Sno` `from` `student` `where` `Ssex='男')`

码农公寓

相关文章