mysql实例

1.创建学生表、课程表、成绩表、老师表

CREATE TABLE `student`( 
     `sid`    INT, 
     `sname` nvarchar(32), 
     `sage`  INT, 
     `ssex`  nvarchar(8) 
  );

CREATE TABLE `course` ( 
     `cid`    INT, 
     `cname` nvarchar(32), 
     `tid`    INT 
  ); 

CREATE TABLE `sc` ( 
     `sid`    INT, 
     `cid`    INT, 
     `score` INT 
  ); 

CREATE TABLE `teacher`
  ( 
     `tid`    INT, 
     `tname` nvarchar(16) 
  );

2、添加部分数据

  insert into Student 
 select 1,N'刘一',18,N'男' union all
 select 2,N'钱二',19,N'女' union all
 select 3,N'张三',17,N'男' union all
 select 4,N'李四',18,N'女' union all
 select 5,N'王五',17,N'男' union all
 select 6,N'赵六',19,N'女'; 
 
 insert into Teacher 
 select 1,N'李小' union all
 select 2,N'贺高' union all
 select 3,N'李小龙' union all
 select 4,N'李小龙';
 
 insert into Course 
 select 1,N'语文',1 union all
 select 2,N'数学',2 union all
 select 3,N'英语',3 union all
 select 4,N'物理',4;
 
 insert into SC 
 select 1,1,56 union all 
 select 1,2,78 union all 
 select 1,3,67 union all 
 select 1,4,58 union all 
 select 2,1,79 union all 
 select 2,2,81 union all 
 select 2,3,92 union all 
 select 2,4,68 union all 
 select 3,1,91 union all 
 select 3,2,47 union all 
 select 3,3,88 union all 
 select 3,4,56 union all 
 select 4,2,88 union all 
 select 4,3,90 union all 
 select 4,4,93 union all 
 select 5,1,46 union all 

3、sql语句练习
(1)查询“001”课程比“002”课程成绩高的所有学生的学号

select a.sid from (select sid,score from sc where cid='001') a,
(select sid,score from sc where cid='002') b  
where a.score>b.score and a.sid=b.sid; 

(2)查询平均成绩大于60分的同学的学号和平均成绩

select sid,avg(score) from sc group by sid having avg(score) >60;

(3)查询所有同学的学号、姓名、选课数、总成绩

select Student.Sid,Student.Sname,count(SC.Cid),sum(score)
from Student left join SC on Student.Sid=SC.Sid
group by Student.Sid,Sname; 

(4)查询姓“李”的老师的个数

select count(distinct(Tname)) from Teacher where Tname like '李%'; 

(5)查询没学过“李小”老师课的同学的学号、姓名

select Student.Sid,Student.Sname from Student  
where Sid not in (select distinct( SC.Sid) from SC,Course,Teacher 
where  SC.Cid=Course.Cid and Teacher.Tid=Course.Tid and Teacher.Tname='李小');

(6)查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select Student.Sid,Student.Sname from Student where Student.Sid in
(select distinct(a.sid) from (select sid from sc where cid='001')a,
(select sid from sc where cid='002')b where a.sid=b.sid);

(7)查询没有学全所有课的同学的学号、姓名

select Student.Sid,Student.Sname from Student,SC 
where Student.Sid=SC.Sid group by  Student.Sid,Student.Sname 
having count(Cid) <(select count(Cid) from Course); 

(8)查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

    SELECT L.Cid As 课程ID,L.score AS 最高分,R.score AS 最低分 
    FROM SC L ,SC AS R 
    WHERE L.Cid = R.Cid and 
        L.score = (SELECT MAX(IL.score) 
                      FROM SC AS IL,Student AS IM 
                      WHERE L.Cid = IL.Cid and IM.Sid=IL.Sid 
                      GROUP BY IL.Cid) 
        AND 
        R.Score = (SELECT MIN(IR.score) 
                      FROM SC AS IR 
                      WHERE R.Cid = IR.Cid 
                  GROUP BY IR.Cid 
                    ); 

(9)查询不同老师所教不同课程平均分从高到低显示

 SELECT max(Z.Tid) AS 教师ID,MAX(Z.Tname) AS 教师姓名,
C.Cid AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩 
FROM SC AS T,Course AS C ,Teacher AS Z 
where T.Cid=C.Cid and C.Tid=Z.Tid 
GROUP BY C.Cid ORDER BY AVG(Score) DESC 

(10)、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

  SELECT SC.Cid as 课程ID, Cname as 课程名称,
  SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]',
  SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '[85-70]' ,
  SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '[70-60]',
  SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS '[60-]' 
FROM SC,Course where SC.Cid=Course.Cid  GROUP BY SC.Cid,Cname; 

(11)查询学生平均成绩及其名次

SELECT 1+(SELECT COUNT( distinct 平均成绩) 
FROM (SELECT Sid,AVG(score) AS 平均成绩 FROM SC GROUP BY Sid ) AS T1 
WHERE 平均成绩 > T2.平均成绩) as 名次, Sid as 学生学号,平均成绩 
FROM (SELECT Sid,AVG(score) AS 平均成绩 FROM SC GROUP BY Sid) AS T2 
ORDER BY 平均成绩 desc;