mysql笔试题

--数据库笔试题
--题一：
--新建学生-课程数据库的三个表:
--学生表:Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主码;
--课程表:Course(Cno,Cname,Cpno,Credeit) Cno为主码;
--学生选修表:SC(Sno,Cno,Grade) Sno,Cno,为主码.
alter table student 
drop constraint pk_studengt;
alter table course 
drop constraint pk_course;
alter table sc 
drop constraint pk_sc;


alter table sc
drop constraint fk_sc_studengt;
alter table sc
drop constraint fk_sc_course;
 
alter table student 
add constraint pk_student primary key (sno);
alter table course  
add constraint pk_course primary key (cno);
alter table sc  
add constraint pk_sc primary key (sno,cno);
alter table sc 
add constraint fk_sc_student foreign key (sno) references student(sno);
alter table sc 
add constraint fk_sc_course foreign key (cno) references course(cno);




--Student表：
--学号Sno 姓名Sname 性别Ssex 年龄Sage 所在系Sdept
--95001 李勇 男 20     CS
--95002 刘晨 女 19 IS
--95003 王敏 女 18 MA
--95004 张立 男 19 IS
drop table student;
select * from student;
create table student
(
  sno int not null,
  sname varchar(20) not null,
  ssex  varchar(20) not null,
  sage int not null,
  sdept varchar(20)
);
insert into student(Sno,Sname,Ssex,Sage,Sdept) values (95001,'李勇','男',20,'CS');
insert into student(Sno,Sname,Ssex,Sage,Sdept) values (95002,'李晨','女',20,'IS');
insert into student(Sno,Sname,Ssex,Sage,Sdept) values (95003,'王敏','女',20,'MA');
insert into student(Sno,Sname,Ssex,Sage,Sdept) values (95004,'张立','男',20,'IS');




--Course表：
--编号Sno 课程名Cname 先行课Cpno 学分Credit
--1 数据库 5 4
--2 数学 2
--3 信息系统 1 4
--4 操作系统 6 3
--5 数据结构 7 4
--6 数据处理 2
--7 Pascal语言 6 4
drop table course;
select * from course;
create table course
(
   Cno int not null,
   Cname varchar(20) not null,
   Cpno int  ,
   Credeit int not null
  );
  insert into course(Cno,Cname,Cpno,Credeit) values (1,'数据库',5,4);
  insert into course(Cno,Cname,Cpno,Credeit) values (2,'数学',null,2);
  insert into course(Cno,Cname,Cpno,Credeit) values (3,'信息系统',1,4);
  insert into course(Cno,Cname,Cpno,Credeit) values (4,'操作系统',6,3);
  insert into course(Cno,Cname,Cpno,Credeit) values (5,'数据结构',7,4);
  insert into course(Cno,Cname,Cpno,Credeit) values (6,'数据处理',null,2);
  insert into course(Cno,Cname,Cpno,Credeit) values (7,'pascal语言',6,4);


--SC表: 
--学号Sno 课程号Cno 成绩Grade
--95001 1 92
--95001 2 85
--95001 3 88
--95002 2 90
--95002 3 80
drop table sc;
select * from  sc;
create table sc
(
  Sno int not null,
  Cno int not null,
  Grade int not null
);
insert into sc(Sno,Cno,Grade) values (95001,1,92);
insert into sc(Sno,Cno,Grade) values (95001,2,85);
insert into sc(Sno,Cno,Grade) values (95001,3,88);
insert into sc(Sno,Cno,Grade) values (95002,2,90);
insert into sc(Sno,Cno,Grade) values (95002,3,80);
insert into sc(Sno,Cno,Grade) values (95001,4,88);
insert into sc(Sno,Cno,Grade) values (95001,5,88);
insert into sc(Sno,Cno,Grade) values (95001,6,88);
insert into sc(Sno,Cno,Grade) values (95001,7,88);


drop database 学生课程数据库;


--学生课程数据库


create database 学生课程数据库;


use  学生课程数据库;
drop table student;
select * from student;
create table student
(
  sno int not null,
  sname varchar(20) not null,
  ssex  varchar(20) not null,
  sage int not null,
  sdept varchar(20)
);






drop table course;
select * from course;
create table course
(
   Cno int not null,
   Cname varchar(20) not null,
   Cpno int  ,
   Credeit int not null
  );












drop table sc;
select * from  sc;
create table sc
(
  Sno int not null,
  Cno int not null,
  Grade int 
);


alter table student 
drop constraint pk_studengt;
alter table course 
drop constraint pk_course;
alter table sc 
drop constraint pk_sc;
alter table sc
drop constraint fk_sc_studengt;
alter table sc
drop constraint fk_sc_course;
 
alter table student 
add constraint pk_student primary key (sno);
alter table course  
add constraint pk_course primary key (cno);
alter table sc  
add constraint pk_sc primary key (sno,cno);
alter table sc 
add constraint fk_sc_student foreign key (sno) references student(sno);
alter table sc 
add constraint fk_sc_course foreign key (cno) references course(cno);


insert into student(Sno,Sname,Ssex,Sage,Sdept) values (95001,'李勇','男',20,'CS');
insert into student(Sno,Sname,Ssex,Sage,Sdept) values (95002,'刘晨','女',19,'IS');
insert into student(Sno,Sname,Ssex,Sage,Sdept) values (95003,'王敏','女',18,'MA');
insert into student(Sno,Sname,Ssex,Sage,Sdept) values (95004,'张立','男',19,'IS');


insert into course(Cno,Cname,Cpno,Credeit) values (1,'数据库',5,4);
insert into course(Cno,Cname,Cpno,Credeit) values (2,'数学',null,2);
insert into course(Cno,Cname,Cpno,Credeit) values (3,'信息系统',1,4);
insert into course(Cno,Cname,Cpno,Credeit) values (4,'操作系统',6,3);
insert into course(Cno,Cname,Cpno,Credeit) values (5,'数据结构',7,4);
insert into course(Cno,Cname,Cpno,Credeit) values (6,'数据处理',null,2);
insert into course(Cno,Cname,Cpno,Credeit) values (7,'pascal语言',6,4);






insert into sc(Sno,Cno,Grade) values (95001,1,92);
insert into sc(Sno,Cno,Grade) values (95001,2,85);
insert into sc(Sno,Cno,Grade) values (95001,3,88);
insert into sc(Sno,Cno,Grade) values (95002,2,90);
insert into sc(Sno,Cno,Grade) values (95002,3,80);
insert into sc(Sno,Cno,Grade) values (95001,4,88);
insert into sc(Sno,Cno,Grade) values (95001,5,88);
insert into sc(Sno,Cno,Grade) values (95001,6,88);
insert into sc(Sno,Cno,Grade) values (95001,7,88);








--一:查询表中的列和行
--1:查询全体学生的学与姓名


select  sno,sname from student;


--2:查询全体学生的姓名、学号、所在系。


select sno,sname,sdept from student;




--3:查询全体学生的详细记录


select * from student;


select * from student,course,sc 
where sc.Sno=student.sno and 
      sc.Cno=course.Cno;


--4:查询全体学生的姓名及出生年份


select sname 姓名, DATEPART(YY,GETDATE())-sage 出生年份 from student;




--5:查询全体学生的姓名,出生年份及所在系,要用小写字母表示系名


select sname 姓名, DATEPART(YY,GETDATE())-sage 出生年份 ,lower(sdept) 系名 from student;


--6:查询选修了课程的学生学号


select distinct Sno from SC;


select  distinct student.sno from student,sc where student.sno=sc.Sno;


--7:查询选修了课程的学生姓名


select distinct  student.sname from student,sc where student.sno=sc.Sno;


--二:条件查询:
--1:查询计算机（CS）系全体学生的姓名


select sname from student where sdept='CS';


--2:查询所有年龄在20岁以下的学生姓名及其年龄


select sname,sage from student where sage<20;


--3:查询考试成绩有不及格的学生的学号


select Sno from SC where Grade < 60;


select student.sno from student,sc where student.sno=sc.Sno and Grade<60;


--4:查询年龄在20到23间的学生的姓名,系别及年龄


select sname,sdept,sage from student where sage between 20 and 23;


--5: 查询年龄不在20到23间的学生的姓名,系别及年龄


select sname,sdept,sage from student where sage not between 20 and 23;


--6:查询信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别


select sname,ssex from student where sdept in ('MA','IS','CS');


--7:查询不是信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别


select sname,ssex from student where sdept not in ('MA','IS','CS');




--8:查询学号为”95001”的学生详细情况


select * from student where sno=95001;


--9:查询所有姓刘的学生的姓名,学号和性别


select sname,sno,ssex from student where sname like '刘%';




--10:查询姓”欧阳”且命名为三个汉字的学生的姓名


select sname from student where sname like '欧阳_';


--11:查询名字中第2个字为”阳”字的学生姓名和学号


select sname,sno from student where sname like '_阳%';


--12:查询所有不姓刘的学生姓名


select sname from student where sname  not like '刘%';


--13:查询DB_Design课程的课程号和学分


select cno,grade from course where Cname= 'DB_Design';


select sc.Cno,course.Credeit from course ,sc where course.cno=sc.cno and Cname= 'DB_Design';


--14:查询以”DB_”开头,且倒数第3个字符为i的课程的详细情况 


select * from course  where Cname like 'DB_%' and course.Cname like '%i__' ;


--15:查询缺少成绩的学生的学号和相应的课程号


select sno,cno from sc where Grade is null; 


--16:查询所有成绩的学生学号和课程号


select sno,cno from sc where Grade is not null ;


--17:查询计算机系(CS)年龄在20岁以下的学生姓名


select sname from student where sage<20 and sdept='CS';


--18:查询选修了3号课程的学生的学号及其成绩,分数降序排列


select sno,grade from sc where Cno=3 order by Grade  desc;


--19:查询全体学生情况,结果按所在系的号升序排列,同一系中的学生按年龄降序


select * from student  
order by sdept asc,sage desc; 




--三:使用集函数


--count,sum,avg,max,min


--1:查询学生的总人数


select COUNT (*) from student ;






--2:查询选修了课程的学生人数


select COUNT (distinct sno) from sc;


--3:计算1号课程的学生平均成绩


select AVG(grade) from sc where Cno=1;


--4:查询选修1号课程的学生最高分数


select MAX(grade) from sc where Cno=1;


--5:求各个课程号及相应的选课人数


select cno,COUNT(*) from sc group by cno;


SELECT CNO,COUNT(SNO) 选课人数 FROM SC GROUP BY CNO;




--6:查询选修了3门以上的课程的学生学号


select sno ,count(Cno) from sc group by sno having  COUNT (cno)>=3;


--where 后面不能加组函数


--四:连接查询:


--1:查询每个学生及其选修课程的情况


SELECT STUDENT.SNO,STUDENT.SNAME,COURSE.*,SC.GRADE
FROM COURSE FULL JOIN SC ON COURSE.CNO=SC.CNO FULL JOIN STUDENT ON SC.SNO=STUDENT.SNO;


--2:查询每一门课的间接先修课(即先修课的先修课)


select a.cname,c.Cname from course a  inner join course b on (a.Cpno=b.Cno)
 inner join course c on (b.Cpno=c.Cno );


select Course.Cname, b.Cname 间接先修课 from Course, Course a, Course b 
where Course.Cpno = a.Cno and a.Cpno = b.Cno;










--五：复合条件连接


--1:查询选修2号课程且成绩在90分以上的所有学生。


select  * from  student,sc where student.sno=sc.sno and Cno=2 and Grade>=90;


--六：嵌套查询


--<1>查询与“刘晨”在同一个系学习的学生


select sdept from student where sname='刘晨';


select sname from student where sdept=(select sdept from student where sname='刘晨') and sname!='刘晨';


--<2>查询选修了课程名为“信息系统”的学生学号和姓名


select Cno from course where  Cname='信息系统';


select student.Sno,student.sname from student,sc ,course where student.sno=sc.Sno and sc.Cno= course.Cno 
and sc.Cno=(select Cno from course where  Cname='信息系统');


select student.sno,student.sname  from student where sno in( select sno from sc 
 where sc.cno=(select course.cno from course where cname ='信息系统'));
 
SELECT SNO,SNAME
FROM STUDENT
WHERE SNO IN(SELECT SNO
FROM COURSE,SC
WHERE SC.CNO=COURSE.CNO AND CNAME='信息系统');



--2:带有Any 或all谓词的子查询


--<1>查询其他系中比信息系中某一学生年龄小的学生姓名和年龄


select sage from student where sdept='IS';


select sname,sage from student where sage< any (select sage from student where sdept='IS') 
       and  sdept!='IS' ;
select sname, sage from student 
  where Sdept<>'IS'
  and Sage < any (select Sage from Student where Sdept='IS');


--<2> 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄


select sage from student where sdept='IS';


select sname,sage from student where sage< all (select sage from student where sdept='IS') 
       and  sdept!='IS' ;




--3 带有Exitst谓词的子查询




--<3>查询所有选修了1号课程的学生姓名


select sname from sc,student where student.sno= sc.Sno and sc.Cno=1;


select sname from student where exists(select * from sc where  cno=1 and sc.sno=student.sno);


/*
1. select * from sc where cno=1     "在选课表里查出一号课的选课信息"
2. select * from sc where sc.cno=1 and sc.sno=student.sno   "选一号课的学生信息"  替换student
   就把1的结果生成一个虚拟的新的sc和student的交集表(sc_student)    
3. select sname from student where exists(select * from sc where sc.cno=1 and sc.sno=student.sno);
   sname从 交集表(sc_student)/新student表 里查
*/
--<4>查询没有选修1号课程的学生姓名


select  sname from student where sname not in  (select sname from sc,student where student.sno= sc.Sno and sc.Cno=1);


select  sname from student where   not exists  (select sname from sc where student.sno= sc.Sno and sc.Cno=1);


-- not exists 不是找不到，而是不要这些数据


/*
1. select * from sc where cno=1     "在选课表里查出一号课的选课信息"
2. select * from sc where sc.cno=1 and sc.sno=student.sno   "选一号课的学生信息" 替换student
   就把1的结果生成一个虚拟的新的sc和student的交集表(sc_student)    
3. from student where not exists (select * from sc where sc.Cno=1 and student.sno= sc.Sno)   "没选一号课的学生信息"  替换student
   把交集表(sc_student)里的东西在student表里取反，形成‘非交集表(sc_student)’
4. select  sname from student where not exists (select * from sc where sc.Cno=1 and student.sno= sc.Sno);
   sname从‘非交集表(sc_student)/新student表’里查
   
注意：先交再not
*/




--<5>查询选修所有全部课程的学生姓名
select * from sc;
select cno from course;
select * from student;


select sname from student  where  not  exists 
(select * from course  where  not  exists 
(select * from sc where course.Cno=sc.Cno 
 and student.sno=sc.Sno ));
 
select sname from student where sno in
(select sno from SC  group by sno  having count(cno)=(select COUNT(cno) from course));


/*
1. select * from SC     "查出选课表里的所有信息"
2. select * from SC where Course.Cno = SC.Cno   "所有被选的课的信息" 替换course     
3. not exists (select * from SC where Course.Cno = SC.Cno   "选课表里没有的课的信息"  替换course，更新sc  
4. select * from Course where not exists
  (select * from SC where Course.Cno = SC.Cno and Student.Sno= SC.Sno)   "选了选课表里没有的课的人"  新sc和student表交  替换student
5. not exists (select * from Course where not exists
  (select * from SC where Course.Cno = SC.Cno and Student.Sno= SC.Sno))  "选了所有课的人" 替换student
6. 出结果
注意：先交再not
*/




--<6>查询至少选修了学生95002选修的全部课程的学生号码


select * from sc b where b.Sno='95002' ;--95002的选课信息


select distinct  a.Sno from sc a where not exists 
(select * from sc b where b.Sno='95002' and  not exists 
(select * from sc c  where a.sno=c.sno and c.Cno=b.Cno));


/*
1. select * from sc a    "选课表里的全部选课信息"
2. select * from sc b where b.Sno='95001'    "选课表里学号为95001的选课信息" 替换b
3. select * from sc b where b.Sno='95001' and exists (select * from sc a where a.sno=b.sno     "选了95001所选的全部课的人" 替换a
   
3. select * from sc b where b.Sno='95001' and not exists (select * from sc a where a.sno=b.sno      "没全选95001选的全部课的人" 替换a
4. select * from sc b where b.Sno='95001' and  not exists 
(select * from sc a where a.sno=b.sno and a.Cno=c.Cno)   "没全选95001选的全部课的人" 替换c
5. not exists (select * from sc b where b.Sno='95001' and  not exists 
(select * from sc a where a.sno=b.sno and a.Cno=c.Cno))   "至少选修了学生95001选修的全部课程的人"  替换c
6.出结果
*/






--七：表Ａ数据如下：
--FYear FNum
--2006  1
--2006  2
--2006  3
--2007  4
--2007  5
--2007  6
--写语句完成按如下格式显示：
--年度  2006  2007
--汇总　  6    15
create table a
(
  FYear int not null,
  FNum  int not null
);


insert into a values (2006,1);
insert into a values (2006,2);
insert into a values (2006,3);
insert into a values (2007,4);
insert into a values (2007,5);
insert into a values (2007,6);


select SUM(FNum) from a group by FYear;


select  '汇总' as '年度',  a1.FNum as '2006',  a2.FNum as '2007'
from a a1,a a2 where (a1.FYear='2006' and  a2.FYear='2007') 


select  '汇总' as '年度', sum(distinct a1.FNum) as '2006', sum(distinct a2.FNum) as '2007'
from a a1,a a2 where (a1.FYear='2006' and  a2.FY ear='2007') 


select  '汇总' '年度', SUM(DISTINCT A1.fnum) '2006',SUM(DISTINCT A2.fnum) '2007' from  A A1,A A2 
 GROUP BY A1.FYear,A2.FYear 
having SUM(DISTINCT A1.fnum) < SUM(DISTINCT A2.fnum);