mysql怎么将select查询的表名当表

问题描述

某校学生选课管理数据库各表情况说明如下：

Student( 
	Sno    CHAR(7) primary key,
	Sname  CHAR(10) ,            
	Ssex   CHAR(2) ,
    Sage   int DEFAULT 20,
    Sdept  CHAR(20) 
)
Course(
    Cno Char(10)    PRIMARY KEY,
    Cname Char(20)  NOT NULL,
    Ccredit  int    CHECK(Ccredit>0),
    Semester int    CHECK(Semester>0),
    Period   int    CHECK(Period>0),
)
SC(
    Sno CHAR(7)    NOT NULL,
    Cno CHAR(10)  NOT NULL, 
    Grade   int,        
    PRIMARY KEY (Sno,Cno),        
    FOREIGN KEY(Sno)  REFERENCES Student(Sno),
    FOREIGN KEY(Cno)  REFERENCES Course(Cno)                
)

针对该课数据库进行以下操作：

1. 查询Student表中的所有记录的Sname和Sage列。
2. 查询学生所有的系别即不重复的Sdept列。
3. 查询Student表的所有记录。
4. 查询全体学生的出生年份和性别，并给出生年份列命名为“BIRTHDAY”。
5. 查询SC表中分数大于85分的学生学号。
6. 查询SC表中成绩在60到80之间的所有记录。
7. 查询SC表中成绩为85，86或88的记录。
8. 查询Student表中不姓“王”的同学记录。
9. 查询全校同学名字中第二个字为“小”的同学的具体情况。
10. 查询课程名中含有“_”的课程的名字和学分。
11. 查询学生表中姓赵、钱、孙、李的同学。
12. 查询Sc表中没有成绩的同学的学号。
13. 查询Student表中“信息系”班或性别为“女”的同学记录。（复合条件查询，集合查询）
14. 查询计算机系男生的具体情况。
15. 以年龄降序查询Student表的所有记录。
16. 以Cno升序、Grade降序查询Sc表的所有记录。
17. 查询所有学生的Sname、Cname和Grade列。（连接查询，嵌套查询）
18. 查询所有选修“计算机导论”课程的同学的成绩。（连接查询）
19. 查询和“李军”同性别的同学Sname. （自身连接查询，嵌套查询）
20. 查询所有同学的基本情况和选课情况，包括未选课的同学。（外连接查询）
21. 查询选修13号课程且成绩高于80分的同学的名字。（连接查询，嵌套查询）
22. 查询和学号为0608002的同学同年出生的所有学生的Sno、Sname列。（自身连接查询，嵌套查询）
23. 查询王位同学所有的成绩。（连接查询，嵌套查询）
24. 查询非计算机系的不超过计算机系所有学生的年龄的学生姓名。（用ANY,ALL）
25. 查询存在有85分以上成绩的课程Cno。（用exists）
26. 查询计算机系同学的人数。
27. 查询数学系同学所选课程的平均分。
28. 查询Student表中年龄最大和最小的同学的具体情况。
29. 查询最高分同学的Sno、Cno列。
30. 查询13号课程的平均分。
31. 查询选修了13号课程且成绩比该课程平均成绩低的同学的学号和成绩。
32. 查询选修了13号课程或1号课程的同学的学号。（复合条件查询，集合查询）
33. 查询各个课程号及相应的选课人数。
34. 查询选修了3门以上课程且总分大于200分的同学的学号。

具体操作详解

1.建表

create table Student( 
	Sno    CHAR(7) primary key,
	Sname  CHAR(10),           
	Ssex   CHAR(2),
    Sage   int DEFAULT 20,
    Sdept  CHAR(20) 
)
create table Course(
    Cno Char(10)    PRIMARY KEY,
    Cname Char(20)  NOT NULL,
    Ccredit  int    CHECK(Ccredit>0),
    Semester int    CHECK(Semester>0),
    Period   int    CHECK(Period>0),
)
create table SC(
    Sno CHAR(7)    NOT NULL,
    Cno CHAR(10)  NOT NULL, 
    Grade   int,        
    PRIMARY KEY (Sno,Cno),        
    FOREIGN KEY(Sno)  REFERENCES Student(Sno),
    FOREIGN KEY(Cno)  REFERENCES Course(Cno)                
)

2.插入数据

insert into dbo.Student 
	([Sno],[Sname],[Ssex],[Sage],[Sdept])
values 
	('0608001','王吴','男',19,'计算机系'),
	('0608011','刘武','男',20,'计算机系'),
	('0608013','于敏','女',18,'土木工程系'),
	('0608021','诸葛良','男',22,'信息系'),
	('0608004','张菲','女',20,'计算机系'),
	('0608043','郭小郭','男',18,'土木工程系'),
	('0608002','马腾飞','男',18,'数学系'),
	('0608003','王位','男',20,'计算机系'),
	('0608006','钱惠子','女',17,'信息系'),
	('0608005','朱子良','男',19,'计算机系'),
	('0608012','李高','男',21,'计算机系'),
	('0608051','孙吴','男',17,'信息系'),
	('0608052','赵武','男',20,'计算机系'),
	('0608063','孙鸣','女',18,'计算机系'),
	('0608022','李高见','男',21,'计算机系'),
	('0608023','李军','男',20,'计算机系'),
	('0608014','王小燕','女',19,'计算机系');

insert into dbo.Course
	([Cno],[Cname],[Ccredit],[Semester],[Period])
values
	('001','计算机导论',3,15,45),
	('002','高等数学(上)',4,15,60),
	('013','马克思原理',3,15,45),
	('004','体育_足球',2,15,30),
	('005','数学系统学',2,15,30);

insert into dbo.SC
	([Sno],[Cno],[Grade])
values
	('0608001','001',81),
	('0608011','001',77),
	('0608004','001',null),
	('0608003','001',50),
	('0608005','001',74),
	('0608012','001',88),
	('0608052','001',null),
	('0608063','001',95),
	('0608022','001',64),
	('0608023','001',77),
	('0608014','001',80),
	('0608001','013',61),
	('0608011','013',null),
	('0608004','005',69),
	('0608003','013',80),
	('0608005','013',44),
	('0608012','013',91),
	('0608052','005',66),
	('0608063','013',null),
	('0608022','013',78),
	('0608023','013',83),
	('0608014','013',80),
	('0608013','002',81),
	('0608043','002',71),
	('0608021','005',71),
	('0608006','005',null),
	('0608051','005',60),
	('0608002','005',80),
	('0608043','004',null),
	('0608021','004',51);

3.查询语句

1. 查询Student表中的所有记录的Sname和Sage列。

select Sname,Sage
from Student;

2. 查询学生所有的系别即不重复的Sdept列。

select distinct Sdept
from Student;
//或者
select Sdept
from Student
group by Sdept;

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

select *
from Student;

4. 查询全体学生的出生年份和性别，并给出生年份列命名为“BIRTHDAY”。

select Sname,2020-Sage as BIRTHDAY,Ssex
from Student;

5. 查询SC表中分数大于85分的学生学号。

select distinct Sno
from SC
where Grade>85;
//或
select Sno
from SC
where Grade>85
group by Sno;

6. 查询SC表中成绩在60到80之间的所有记录。

select *
from SC
where Grade between 60 and 80;

7. 查询SC表中成绩为85，86或88的记录。

select *
from SC
where Grade in(85,86,88);

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

select *
from Student
where Sname not like '王%';

9. 查询全校同学名字中第二个字为“小”的同学的具体情况。

select *
from Student,SC
where Sname like '_小%' and Student.Sno=SC.Sno;

10. 查询课程名中含有“_”的课程的名字和学分。

select Cname,Ccredit
from Course
where Cname like '%\_%' escape '\';

11. 查询学生表中姓赵、钱、孙、李的同学。

select *
from Student
where Sname like '赵%'
or Sname like '钱%'
or Sname like '孙%'
or Sname like '李%';

12. 查询Sc表中没有成绩的同学的学号。

select Sno
from SC
where Grade is null;

13. 查询Student表中“信息系”班或性别为“女”的同学记录。（复合条件查询，集合查询）

//复合条件查询
select *
from Student
where Sdept='信息系' or Ssex='女';
//集合查询
select *
from Student
where Sdept='信息系' 
union
select *
from Student
where Ssex='女';

14. 查询计算机系男生的具体情况。

select *
from Student
where Ssex='男' and Sdept='计算机系';

15. 以年龄降序查询Student表的所有记录。

select *
from Student
order by Sage desc;

16. 以Cno升序、Grade降序查询Sc表的所有记录。

select *
from SC
order by Cno,Grade desc;

17. 查询所有学生的Sname、Cname和Grade列。（连接查询）

select Sname,Cname,Grade
from SC,Student,Course
where SC.Sno=Student.Sno and SC.Cno=Course.Cno;

18. 查询所有选修“计算机导论”课程的同学的成绩。（连接查询，嵌套查询）

//连接查询
select Grade
from SC,Course
where SC.Cno=Course.Cno and Cname='计算机导论';
//嵌套查询
select Grade
from SC
where Cno in (
	select Cno
	from Course
	where Cname='计算机导论'
);

19. 查询和“李军”同性别的同学Sname. （自身连接查询，嵌套查询）

//自身连接查询
select S1.Sname
from Student S1,Student S2
where S1.Ssex=S2.Ssex and S2.Sname='李军';
//嵌套查询
select Sname
from Student
where Ssex in(
	select Ssex
	from Student
	where Sname='李军'
);

20. 查询所有同学的基本情况和选课情况，包括未选课的同学。（外连接查询）

//左外连接
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student
left outer join SC on(Student.Sno=SC.Sno);

21. 查询选修13号课程且成绩高于80分的同学的名字。（连接查询，嵌套查询）

//连接查询
select Sname
from Student,SC
where SC.Sno=Student.Sno and SC.Cno='013' and Grade>80;
//嵌套查询
select Sname
from Student
where Sno in(
	select Sno
	from SC
	where SC.Cno='013' and SC.Grade>80
);

22. 查询和学号为0608002的同学同年出生的所有学生的Sno、Sname列。（自身连接查询，嵌套查询）

//自身连接查询
select S1.Sno,S1.Sname
from Student S1,Student S2
where S1.Sage=S2.Sage and S2.Sno='0608002';
//嵌套查询
select Sno,Sname
from Student
where Sage in(
	select Sage
	from Student
	where Sno='0608002'
);

23. 查询王位同学所有的成绩。（连接查询，嵌套查询）

//连接查询
select Grade
from Student,SC
where Student.Sno=SC.Sno and Sname='王位';
//嵌套查询
select Grade
from SC
where Sno in(
	select Sno
	from Student
	where Sname='王位'
);

24. 查询非计算机系的不超过计算机系所有学生的年龄的学生姓名。（用ANY,ALL）

select Sname 
from Student
where Sage<=any(
	select Sage
	from Student
	where Sdept='计算机系'
)and Sdept!='计算机系';

25. 查询存在有85分以上成绩的课程Cno。（用exists）

select Cno
from Course
where exists(
	select *
	from SC
	where Cno=Course.Cno and Grade>85
);

26. 查询计算机系同学的人数。

select count(*) as 总人数
from Student 
where Sdept='计算机系';

27. 查询数学系同学所选课程的平均分。

select avg(Grade) as avgGrade
from SC 
where Sno in(
	select Sno
	from Student
	where Sdept='数学系'
);

28. 查询Student表中年龄最大和最小的同学的具体情况。

select *
from Student
where Sage=(
	select max(Sage)
	from Student
)or Sage=(
	select min(Sage)
	from Student
);

29. 查询最高分同学的Sno、Cno列。

select Sno,Cno
from SC
where Grade=(
	select max(Grade)
	from SC
);

30. 查询13号课程的平均分。

select avg(Grade) as avgGrade
from SC
where Cno='013';

31. 查询选修了13号课程且成绩比该课程平均成绩低的同学的学号和成绩。

select Sno,Grade
from SC
where Grade<(
	select avg(Grade)
	from SC
	where Cno='013'
)and Cno='013';

32. 查询选修了13号课程或1号课程的同学的学号。（复合条件查询，集合查询）

//复合条件查询
select Sno
from SC
where Cno='013' or Cno='001';
//集合查询
select Sno
from SC
where Cno='013'
union
select Sno
from SC
where Cno='001';

33. 查询各个课程号及相应的选课人数。

select Cno,count(Sno) as 选课人数
from SC
group by Cno;

34. 查询选修了3门以上课程且总分大于200分的同学的学号。

select Sno
from SC
group by Sno
having count(Cno)>3 and sum(Grade)>200;