数据库基本操作（二）--数据查询

//数据查询

create database JXGL; //创建数据库

use JXGL;//使用数据库

create table S(

Sno char(10) not null unique,

Sname char(20) not null unique,

Ssex char(2),

Sage int,

Sdept char(20));

create table C(

Cno char(2) not null PRimary key (Cno),

Cname char(20),

Teacher char(20));

create table Sc(

Sno char(10) not null,

Cno char(2) not null,

Grade smallint);

insert into S values('200215121','李勇','男',20,'CS');

insert into S values('200215122','刘晨','女',19,'CS');

insert into S values('200215123','王敏','女',18,'MA');

insert into S values('200215124','张立','男',19,'IS');

insert into C values('2','数学','张三');

insert into C values('6','数据处理','张三');

insert into C values('4','操作系统','张三');

insert into C values('7','PASCAL' ,'张三');

insert into C values('5','数据结构','李四');

insert into C values('1','数据库' ,'李四');

insert into C values('3','信息系统','王五');

insert into Sc values ('200215121','1',92);

insert into Sc values('200215121','2',85);

insert into Sc values('200215121','3',88);

insert into Sc values('200215122','2',90);

insert into Sc values('200215122','3',80);

insert into Sc values('200215122','1',null);

insert into Sc values ('200215124','1',89);

insert into Sc values('200215124','5',90);

insert into Sc Values('200215124','3',92);

/**　　

S表示学生，它的各属性依次为 学号、姓名、年龄、性别、系；

SC表示成绩，它的各属性依次为 学号、课程号和分数；

C表示课程，它的各属性依次为 课程号、课程名和任课教师。

　　S（Sno，Sname，Sage，Ssex，Sdept）

　　SC（Sno，Cno，Grade）

　　C（Cno，Cname，Teacher）

*/

//1.查询学生选课表中的全部数据

select * from C;

//2.查询CS系学生的姓名,年龄

select Sname,Sage from S where Sdept='CS';

//3.查询成绩在70~80分之间的学生的学号,课程号和成绩

select Sno,Cno,grade from Sc where Grade>=70 and Grade<=80;

//4.查询CS系年龄在18~20之间且性别为"男"的学生的姓名和年龄

select Sname,Sage from S where Ssex='男' and Sage between 18 and 20;

alter table S add constraint Ssex check (Ssex in('男','女'));

select Sname,Sage from S where Ssex not like'女' and Sage in(18,19,20 );

//5.查询课程号为"C01"的课程的最高分数 select MAX(Grade) from Sc where Cno='Co1';

//6.查询CS系学生的最大年龄和最小年龄

select min(Sage)最小年龄,max(Sage)最大年龄 from S where Sdept='CS';

//7.统计每个系的学生人数

select count(*) 人数 from S where Sdept='CS';

//8.统计每门课程的选课人数和考试最高分

select Count(*) 选课人数, max(Grade) 最高分 from Sc,C where Sc.Cno=C.Cno group by C.Cno ;

//9.查询每个学生的选课门数和考试总成绩,并按选课门数的升序显示结果

select count(Sno) ,sum(Grade)from Sc group by Sno order by count(Sno) asc;

//10.查询总成绩超过200分的学生,要求列出学号,总成绩

select Sc.Sno 学号,sum(Grade) 总成绩 from Sc group by Sno having sum(Grade)>=200;

---------------------------------------------------------------------------------

//11.查询选修了"1"的学生的姓名和所在系

select S.Sname,Sdept from S,Sc where Sc.Sno=S.Sno and Cno='1';

//12.查询成绩在80分以上的学生的姓名,课程号和成绩,并按成绩的降序排列

select Sname,Sc.Cno,Grade from S,Sc where Grade>80 and Sc.Sno=S.Sno order by Grade desc;

//13.查询那些课程没有人选修.要求列出课程号和课程名（not in）

select Cno,Cname from C where Cno not in (select Cno from Sc);

/*

//14.要求用子查询

(1).查询选修了课程"2"的学生的姓名和所在系。

(2).查询IS系成绩在80分以上的学生的学号,姓名。

*/

select Sname,Sdept from S where Sno in (select Sno from Sc where Cno='2');

select Sno,Sname from S where Sdept ='IS' and Sno in (select Sno from Sc where Grade>=80);

//15.求数学系学生的学号和姓名

select Sno,Sname from S where Sdept ='MA';

//16.求选修了课程的学生学号

select distinct Sno from Sc where Cno in (select Cno from C);

//17.求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列

select S.Sno,Grade from S,Sc,C where Cname='数学' and C.Cno=Sc.Cno and S.Sno=Sc.Sno order by Grade desc,S.Sno asc;

//18.求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。

select S.Sno,Grade,Grade*0.8 from S,Sc,c where Cname='数学' and Grade between 80 and 90 and C.Cno=Sc.Cno and Sc.Sno=S.Sno;

//19.求CS系或IS系并且姓刘的学生的信息。

select * from S where Sdept in('CS','IS') and Sname like '刘%';

//20.求缺少了成绩的学生的学号和课程号（is null）。

select S.Sno,Cno from S,Sc where Grade is null and Sc.Sno=S.sno;

//21 .查询每个学生的情况以及他（她）所选修的课程。

select S.Sno,Sname,Ssex,Sage,Sdept,Cname,Grade from S,C,Sc where S.Sno=Sc.Sno and C.Cno=Sc.Cno;

//22.求学生的学号、姓名、选修的课程名及成绩

select S.Sno,Sname,C.Cno,Grade from S,Sc,C where S.Sno=Sc.Sno and Sc.Cno=C.Cno;

//23.求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。

select S.Sno,Sname,Grade from S,Sc,C where Grade>=90 and Cname='数学' and S.Sno=Sc.Sno and Sc.Cno=C.cno;

//24.查询选修课程包含王一老师所授课程的学生学号

select S.Sno from S,C,Sc where Teacher like '王%' and S.Sno=Sc.Sno and Sc.Cno=C.Cno;

//25.查询王一老师所授课程的课程号和课程名。

select C.Cno,Cname from C where Teacher like '王%';

//26.查询学号为'200215121'学生所学课程的课程名与任课教师。

select Cname,Teacher from S,C,Sc where S.Sno='200215121' and S.Sno=Sc.Sno and Sc.Cno=C.Cno;

//27.查询至少选修王一老师所授课程中一门课程的女学生姓名（=any）。

select Sname from S,Sc,C where Teacher like '王%' and Ssex='女'and S.Sno=Sc.Sno and Sc.Cno= C.Cno;

select Sname from S,Sc where Ssex='女' and S.Sno=Sc.Sno and Sc.Cno=any( select Cno from C where Teacher like '王%');

//28.查询姓张同学没有选修的课程的课程号（not int）。

select Cno from C where Cno not in (Select Cno from Sc,S where Sc.Sno=S.Sno and Sname like '张%');

//29.查询至少选修两门课程的学生学号。

select Sno from Sc group by Sno having Count(cno)>2;

//30.查询全部学生都选项修的课程的课程号与课程名。

select C.Cno,Cname from Sc,C where (select distinct count(Sno) from Sc group by Sno)=any(select count(Cno) from Sc )and Sc.Cno=C.Cno ;(这条语句错误) select Cno,Cname from C where Cno in (Select Sc.Cno from Sc,c where Sc.Sno=C.Cno group by Sc.Cno having count(*)=(select count(*) from S));

select cno,cname from c where cno in(select sc.cno from sc,c where sc.cno=c.cno group by sc.cno having count(*)=(select count(*) from s);

希望可以和大家多交流交流！！