mysql实例

一、为管理学员培训信息，建立3个表:

student (SID,SN,SD,SA) SID,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

course (CID,CN ) CID,CN 分别代表课程编号、课程名称

SC ( SID,CID,G ) SID,CID,G 分别代表学号、所选修的课程编号、学习成绩

#使用标准SQL嵌套语句查询选修课程名称为‘语文’的学员 学号 和 姓名 和 单位
SELECT sid, sn, sd FROM student WHERE sid IN (SELECT sid FROM sc, course WHERE sc.cid = course.cid AND course.cn = '语文');

#使用标准SQL嵌套语句查询选修课程编号为’02’的学员姓名和所属单位
SELECT sn, sd FROM student s, sc WHERE s.sid = sc.sid AND sc.cid = '02'; 

#使用标准SQL嵌套语句查询不选修课程编号为’02’的学员姓名和所属单位
#select sn, sd from student s, sc where s.sid = sc.sid and sc.cid != '02';
SELECT sn, sd FROM student s, sc WHERE sid NOT IN (SELECT sid FROM sc WHERE sc.cid='02');

#使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
#SELECT sn, sd FROM student s, sc, course c where s.sid = sc.sid and sc.cid in (select count(*) in c);
SELECT sn, sd FROM student s WHERE sid IN (SELECT sid FROM sc GROUP BY sid HAVING COUNT(*)=(SELECT COUNT(*) FROM c));

#查询选修了课程的学员人数
SELECT COUNT(DISTINCT(sid)) FROM sc;

#查询选修课程超过2门的学员学号，姓名，单位
SELECT sid, sn, sd FROM student s WHERE sid IN (SELECT sid FROM sc GROUP BY sid HAVING COUNT(DISTINCT(cid))>2);

二、dept表：

emp表：

#1.列出emp表中各部门的部门号，最高工资，最低工资
select deptno,max(sal),min(sal) from emp group by deptno;

#2.列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资
select max(sal),min(sal) from emp WHERE job = 'clerk' group by deptno ;

#3.对于emp中最低工资小于2000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资
#SELECT empno,MAX(sal),MIN(sal) FROM emp WHERE job = 'clerk' and min(sal)<2000 GROUP BY empno ;
select b.deptno as 部门号,max(sal) as 最高工资,min(sal) as 最低工资 from emp as b
where job='clerk' and (select min(sal)from emp as a where a.deptno=b.deptno)<2000 group by 
b.deptno;

#4.根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资
select ename,deptno,sal from emp order by deptno desc, sal asc;

#5.列出'buddy'所在部门中每个员工的姓名与部门号
SELECT ename,deptno FROM emp where deptno = (select deptno from emp where ename = 'buddy');
select b.ename as 姓名,b.deptno as 部门号 from emp as b where b.deptno=(select a.deptno from emp as a where a.ename='buddy');

#6.列出每个员工的姓名，工作，部门号，部门名
select e.ename, e.job, d.deptno, d.dname from dept d, emp e where e.deptno = d.deptno;

#7.列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名
SELECT e.ename, e.job, d.deptno, d.dname FROM dept d, emp e WHERE e.deptno = d.deptno and e.job = 'clerk';

#8.对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）
select a.deptno as 部门号,a.ename as 员工,b.ename as 管理者 from emp as a,emp as b 
where a.mgr is not null and a.mgr=b.ename;

#9.对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
select d.deptno, d.dname, e.ename, e.job from emp e,dept d where e.deptno = d.deptno AND e.job = 'clerk';

#10.对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资 from emp as a 
where a.sal>(select avg(b.sal) from emp as b where a.deptno=b.deptno) order by a.deptno;

#11.对于emp，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排序
select a.deptno as 部门号,count(a.sal) as 员工数 from emp as a
where a.sal>(select avg(b.sal) from emp as b where a.deptno=b.deptno) group by a.deptno order by a.deptno;

#12.对于emp中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，平均工资，按部门号排序
select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资 
from emp as a where (select count(c.empno) from emp as c where c.deptno=a.deptno and 
c.sal>(select avg(sal) from emp as b where c.deptno=b.deptno))>1
group by a.deptno order by a.deptno;

#13.对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资,(select count(b.ename) from emp as b 
where b.sal<a.sal) as 人数 from emp as a
where (select count(b.ename) from emp as b where b.sal<a.sal)>=5;

 三、

/*题目3

　　问题描述:

　　已知关系模式:

　　S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名

　　C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

　　SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
*/

 

        

        

        

#1. 找出没有选修过“金刚”老师讲授课程的所有学生姓名
SELECT sname AS 学生姓名 FROM s WHERE  NOT EXISTS (SELECT * FROM c,sc WHERE c.cno=sc.cno 
AND cteacher='金刚' AND sc.sno=s.sno);

SELECT sname AS 学生姓名 FROM s WHERE sno NOT IN (SELECT sno FROM c,sc WHERE c.cno=sc.cno 
AND cteacher='金刚');

#2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
SELECT s.sno AS 学生学号,s.sname AS 学生姓名,AVG(sc.scgrade) AS 平均成绩 FROM s,sc 
WHERE sc.sno=s.sno AND  sc.sno IN(SELECT sc.sno FROM sc WHERE sc.scgrade<60 GROUP BY sc.sno HAVING
COUNT(DISTINCT cno)>2) GROUP BY s.sno,s.sname;

#3. 列出既学过“01”号课程，又学过“02”号课程的所有学生姓名
SELECT s.sno AS 学生学号,s.sname AS 学生姓名 FROM s WHERE sno IN(SELECT sc.sno AS 学生学号 FROM c,sc WHERE 
c.cno=sc.cno AND c.cno IN('01','02') GROUP BY sno HAVING COUNT(DISTINCT sc.cno)=2);

#4. 列出“01”号课成绩比“02”号同学该门课成绩高的所有学生的学号
SELECT  sc1.sno AS 学生学号 FROM sc AS sc1,c AS c1,sc AS sc2,c AS c2
WHERE sc1.cno=c1.cno AND c1.cno='01' AND sc2.cno=c2.cno AND c2.cno='02'
AND sc1.scgrade>sc2.scgrade GROUP BY sc1.sno;

#5. 列出“01”号课成绩比“02”号课成绩高的所有学生的学号及其“01”号课和“02”号课的成绩
SELECT  sc1.sno AS 学生学号, sc1.scgrade AS no1grade ,sc2.scgrade AS no2grade
FROM sc AS sc1,c AS c1,sc AS sc2,c AS c2
WHERE sc1.cno=c1.cno AND c1.cno='01' AND sc2.cno=c2.cno AND c2.cno='02'
AND sc1.scgrade>sc2.scgrade GROUP BY sc1.sno;