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 bwhere 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 awhere 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))>1group 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 awhere (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 HAVINGCOUNT(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 c2WHERE 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 no2gradeFROM sc AS sc1,c AS c1,sc AS sc2,c AS c2WHERE 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;