mysql sql查询

来源：互联网发布：手机视频格式转换软件编辑：程序博客网时间：2024/04/27 08:21

if Object_id('sc') is not null  
  Drop table sc  
if Object_id('student') is not null  
  Drop table student  
if Object_id('course') is not null  
  Drop table course  
  
--定义SC表  
create table sc(  
  sno char(20),  
  cno char(20),  
  grade smallint,  
  primary key(sno,cno)  
);  
  
--定义student表  
create table student(  
   sno char(10) primary key,  
   sname char(20) not null,  
   sage smallint,  
   ssex char(2),  
   sdept varchar(20)  
);  
  
  
--定义course表  
create table course(  
    cno char(10) primary key,  
    cname varchar(20) not null,  
    credit smallint  
  
);  
  
--插入student表数据  
  
insert into student values('001','张三','20','f','CS');  
insert into student values('002','李晨','21','f','E');  
insert into student values('003','李丽','21','m','E');  
insert into student values('004','高飞','23','f','MA');  
insert into student values('005','苏瑞','20','f','CS');  
insert into student values('006','赵达','22','f','MA');  
insert into student values('007','韩愈','22','m','C');  
insert into student values('008','郑乐','24','m','CS');  
insert into student values('009','许三朵','23','f','MA');  
insert into student values('010','王晓','20','m','C');  
insert into student values('011','丁二','21','f','E');  
insert into student values('012','钟瑜','23','f','CS');  
insert into student values('013','刘莉莉','21','m','MA');  
insert into student values('014','柳叶青','23','f','C');  
insert into student values('015','张其安','22','m','CS');   
  
  
--插入course表信息  
insert into course values('c01','数据库','2');  
insert into course values('c02','数学','4');  
insert into course values('c03','物理','3');  
insert into course values('c04','C语言','3');  
insert into course values('c05','历史','2');  
insert into course values('c06','操作系统','3');  
insert into course values('c07','iOS开发','4');  
insert into course values('c08','软件工程','2');  
insert into course values('c09','汇编语言','2');  
insert into course values('c10','英语','3');  
  
  
--插入SC选课表  
  
--001学生选课  
insert into sc values('001','c02','89');  
insert into sc values('001','c04','70');  
insert into sc values('001','c08','74');  
insert into sc values('001','c10','87');  
insert into sc values('001','c03','60');  
  
--002学生选课  
insert into sc values('002','c01','77');  
insert into sc values('002','c03','59');  
insert into sc values('002','c02','90');  
insert into sc values('002','c04','100');  
insert into sc values('002','c05','45');  
insert into sc values('002','c06','65');  
insert into sc values('002','c07','80');  
insert into sc values('002','c08','98');  
insert into sc values('002','c09','66');  
insert into sc values('002','c10','88');  
  
  
--003学生选课  
--未选课  
  
--004学生选课  
insert into sc values('004','c09','90');  
insert into sc values('004','c06','43');  
insert into sc values('004','c10','85');  
insert into sc values('004','c05','86');  
insert into sc values('004','c07','60');  
insert into sc values('004','c08','78');  
  
--005学生选课  
  
insert into sc values('005','c10','88');  
insert into sc values('005','c02','69');  
insert into sc values('005','c04','84');  
insert into sc values('005','c05','88');  
insert into sc values('005','c03','65');  
insert into sc values('005','c06','70');  
insert into sc values('005','c07','81');  
  
  
--006学生选课  
  
insert into sc values('006','c10','62');  
insert into sc values('006','c02','87');  
insert into sc values('006','c04','60');  
insert into sc values('006','c05','74');  
insert into sc values('006','c03','63');  
insert into sc values('006','c06','70');  
insert into sc values('006','c07','91');  
insert into sc values('006','c01','70');  
insert into sc values('006','c09','81');  
  
  
--007学生选课  
  
insert into sc values('007','c01','80');  
insert into sc values('007','c02','67');  
insert into sc values('007','c04','62');  
insert into sc values('007','c05','74');  
insert into sc values('007','c03','45');  
insert into sc values('007','c08','68');  
insert into sc values('007','c09','99');  
insert into sc values('007','c10','100');  
  
  
--008学生选课  
  
insert into sc values('008','c08','61');  
insert into sc values('008','c02','82');  
insert into sc values('008','c04','88');  
  
  
--009学生选课  
  
insert into sc values('009','c02','67');  
insert into sc values('009','c06','34');  
insert into sc values('009','c08','78');  
insert into sc values('009','c10','74');  
insert into sc values('009','c03','80');  
insert into sc values('009','c04','70');  
insert into sc values('009','c07','94');  
  
  
--010学生选课  
  
insert into sc values('010','c10','80');  
insert into sc values('010','c02','84');  
insert into sc values('010','c04','65');  
insert into sc values('010','c05','72');  
insert into sc values('010','c03','87');  
insert into sc values('010','c08','70');  
insert into sc values('010','c07','91');  
insert into sc values('010','c01','70');  
insert into sc values('010','c09','81');  
  
  
--011学生选课  
  
insert into sc values('011','c04','64');  
insert into sc values('011','c07','75');  
insert into sc values('011','c09','60');  
insert into sc values('011','c02','84');  
  
  
--012学生选课  
  
insert into sc values('012','c01','97');  
insert into sc values('012','c02','83');  
insert into sc values('012','c03','60');  
insert into sc values('012','c04','70');  
insert into sc values('012','c05','88');  
insert into sc values('012','c06','64');  
insert into sc values('012','c07','91');  
insert into sc values('012','c08','70');  
insert into sc values('012','c09','84');  
insert into sc values('012','c10','81');  
  
  
--013学生选课  
  
insert into sc values('013','c01','95');  
insert into sc values('013','c02','86');  
insert into sc values('013','c04','70');  
insert into sc values('013','c06','57');  
insert into sc values('013','c08','63');  
insert into sc values('013','c10','54');  
  
--014学生选课  
  
insert into sc values('014','c01','78');  
insert into sc values('014','c02','87');  
insert into sc values('014','c03','75');  
insert into sc values('014','c05','99');  
insert into sc values('014','c06','34');  
insert into sc values('014','c07','48');  
insert into sc values('014','c09','84');  
insert into sc values('014','c10','89');  
  
  
--015学生选课  
  
insert into sc values('015','c02','87');  
insert into sc values('015','c07','96');  

--以下是老师布置的练习题，我觉得做完以后大有收获，拿出来大家交流一下

1、在Course表中添加“教师”列（20个长度的变长字符串）

2、为每门课程添加教师信息。

3、将教师列修改为非空列。

4、查询选修了刘老师的课程的学生。

5、检索选修了课程号为C01或C02课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。

6、检索所有学生的姓名、所选课程的课程名和成绩以及课程号，并且按成绩的降序和课程号的升序进行排列。

7. 列出没有选课的学生姓名

8. 列出平均分最高的学生所在系的所有学生的姓名

9.查询CS系C01课程的成绩比C01课程的平均分高的学生学号

10.查询既选修了C01又选修了C02的学生

11.统计及格的课程数在四门以上的学生所选课程的平均成绩。最后按降序列出平均成绩名次名单来。

12.检索所有CS系学生都选修了的课程（列出课程号）

13.查询年龄高于其所在系的平均年龄的学生姓名

14.查询每位同学的选课中成绩最高的课程对应的学号，姓名，课程名，成绩

15.为MA系学生选修必修课C05

16.将CS系,C01课程学生的成绩加10分

17.将每位同学的最低分加10分（选）

18.将”数据库”的选课记录全部删除

[sql] view plain copy
--1、检索所有学生中年龄最大的学生的姓名及年龄。  
select sno,sname from student  
where sage=(select max(sage) from student)  
  
select sno,sname from student  
where sage>=all(select sage from student)  
--2、求每一个学生的最高分和最低分。  
select sno,max(grade) as '最高分',min(grade) as '最低分'from sc  
group by  sno;  
  
select sno,grade from sc  
where grade>=all(select grade from student) or grade<=all(select grade from student)  
  
--3、查询CS系所有男同学考C05课程的成绩，列出这些学生的学号，姓名，成绩，并按成绩降序排列。  
select student.sno,sname,grade from sc,student  
where sdept='CS' and cno='c05'   
and student.sno =sc.sno  
order by grade desc;  
  
select sno,grade from sc  
where sno in(select sno from student where sdept='cs')and cno='c05'  
order by grade desc;  
--4、检索选修了“C语言”课程的学生的姓名（可用子查询—IN或Exists）  
--子查询in  
select sname from student  
where sno in(  
select sno from sc   
where cno in (  
select cno from course where cname='C语言'))  
  
--子查询exists  
select sname from student  
where exists(  
select * from sc   
where sno=student.sno and exists(  
select * from course where cno=sc.cno and cname='C语言'  
)  
)  
  
select sname from student,sc  
where sc.sno=student.sno and cno=(select cno from course where cname='C语言')  
  
  
--5、检索选修了课程号为C01或C02课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。  
select sname,cname,grade from student,sc,course  
where student.sno=sc.sno   
and sc.cno=course.cno   
and sc.cno in('c01','c02') and grade>=70;  
  
--6、检索所有学生的姓名、所选课程的课程名和成绩以及课程号，并且按成绩的降序和课程号的升序进行排列（使用外连接将没有选课的同学列出来）。  
select sname,sc.cno,grade from course,student, sc  
where student.sno=sc.sno and course.cno=sc.cno  
order by grade desc;  
  
--7. 列出没有选课的学生姓名  
select sno,sname from student  
where sno not in(select sno from sc)  
  
select sno,sname from student  
where not exists(select sno from sc where sno=student.sno)  
--8. 列出平均分最高的学生所在系的所有学生的姓名  
select sname,sdept from student  
where sdept in(  
select sdept from student    
where sno in(  
select sno from sc   
group by sno  
having avg(grade)>=all(select avg(grade)from sc group by sno))  
)  
  
  
--9.查询CS系C01课程的成绩比C01课程的平均分高的学生学号  
select student.sno from student,sc  
where sc.sno=student.sno and sdept='CS'and sc.cno='c01'and grade>  
(select avg(grade) from sc  
where cno='c01');  
  
  
--10.查询既选修了C01又选修了C02的学生  
--in  
select sno from student  
where sno in(select sno from sc where cno='c01') and sno in(select sno from sc where cno='c02')  
  
select sno from student  
where exists(select sno from sc where cno='c01' and sno=student.sno) and sno in(select sno from sc where cno='c02'and sno=student.sno)  
  
select sno from student  
where sno in(select sno from sc   
where cno='c01'   
intersect  
select sno from sc  
where cno='c02')  
  
--11.统计及格的课程数在四门以上的学生所选课程的平均成绩。最后按降序列出平均成绩名次名单来。  
select sno,avg(grade) from sc  
where grade>=60  
group by sno  
having count(*)>4  
order by avg(grade) desc;  
  
--12.检索所有CS系学生都选修了的课程（列出课程号）  
  
select cno from sc,student  
where sc.sno=student.sno and student.sdept='CS'  
group by cno  
having count(*)=(select count(*) from student where sdept='CS')  
  
  
select cno from course  
where not exists(  
select * from student  
where sdept='CS' and not exists(  
select * from sc  
where student.sno=sc.sno and course.cno=sc.cno  
)  
)  
  
--没有选课的学生  
select sno from student  
where not exists(  
 select * from sc where sc.sno=student.sno   
)  
  
--13.查询年龄高于其所在系的平均年龄的学生姓名  
select sname from student x  
where sage>(select avg(sage) from student y where x.sdept=y.sdept)  
  
  
--14.查询每位同学的选课中成绩最高的课程对应的学号，姓名，课程名，成绩  
select sc.sno,sname,cname,grade from sc,student,course  
where sc.sno=student.sno and sc.cno=course.cno   
and grade=(select max(grade) from sc where sc.sno=student.sno)  
  
  
select sc.sno,sname,cname,grade from sc,student,course  
where sc.sno=student.sno and sc.cno=course.cno  
and grade>=all(select grade from sc where student.sno=sc.sno)  
  
--创建视图然后查询  
create view s1(sno,sname,cname,grade)  
as  
select sc.sno,sname,cname,grade from sc,student,course  
where sc.sno=student.sno and course.cno=sc.cno  
  
--select sno,sname,max(grade) from s1  
--group by (sno,sname)  
  
--15.为MA系学生选修必修课C05  
insert into sc(sno,cno)   
select sno,'c05' from student   
where sdept = 'MA' and   
sno not in (select sno from sc where cno='c05')  
  
insert into sc(sno,cno)  
select sno,'c05' from student   
where sdept='MA' and not exists(select sno from sc where cno='c05' and sno=student.sno )  
--16.将CS系,C01课程学生的成绩加10分  
update sc  
set grade=grade-10  
where cno='c01' and sno in(select sno from student where sdept='CS')   
  
select * from sc,student  
where cno='c01' and student.sno=sc.sno and student.sdept='CS'  
--17.将每位同学的最低分加10分（选）  
update sc   
set grade=grade+10  
where grade=(select min(grade) from sc y where sc.sno=y.sno)  
  
update sc   
set grade=grade-10  
where grade=(select min(grade) from sc y   
group by sno  
having sc.sno=y.sno  
)  
--18.将”数据库”的选课记录全部删除  
delete from sc  
where '数据库'=(select cname from course where sc.cno=course.cno);  
  
delete from sc  
where cno=(select cno from course where course.cname='数据库');  

0 0