• MySQL查询语句 最近做实验时写到了有关查询语句的详细使用案例。借此案例记录一下包括关联查询,子查询,嵌套查询在内的查询语句的用法。 本案例涉及的表如下: student: sc: ...

    MySQL查询语句

    最近做实验时写到了有关查询语句的详细使用案例。借此案例记录一下包括关联查询,子查询,嵌套查询在内的查询语句的用法。
    本案例涉及的表如下:

    student:
    studnet表存储了studnet表存储了学生的基本信息,其中各字段含义如下:
    Sno:学生学号
    Sname:学生姓名
    Ssex:学生性别
    Sage:学生年龄
    Sdept:学生院系

    sc:
    在这里插入图片描述sc表存储了有关学生学科和成绩的相关信息,各字段含义如下:
    Sno:学生学号
    Sname:学生姓名
    Ssex:学生性别
    Cno:课程编号
    Grade:课程成绩

    course:
    在这里插入图片描述crouse表存储了课程的编号和课程名称,个字段含义如下:
    Cno:课程编号
    Cname:课程名称

    具体操作

    一 单表

    1 查询年龄在 19 至 21 岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。

    select sno,sname,sage from student where sage between 19 and 21 and ssex=’女’ order by sage desc

    2 查询姓名中第戎 2 个字为“明”字的学生学号、性别

    select sname ,ssex from student where sname like ‘_明%’

    3 查询 1001 课程没有成绩的学生学号、课程号

    select sno,cno from sc where grade is null and cno=’1001’

    4 查询 JSJ 、SX、WL 系的学生学号,姓名,结果按系及学号排列

    select sno,sname from student where sdept in (‘JSJ’,’SX’,’WL’) order by sdept,sno

    5 按 10 分制查询学生的 sno,cno,10 分制成绩 (1-10 分 为 1 ,11-20 分为 2 ,30-39 分为 3,。。。90-100 为 10)

    select sno , cno , grade/10.0+1 as level from sc

    6 查询 student 表中的学生共分布在那几个系中。(distinct

    select distinct sdept from student

    7 查询 0001 号学生 1001,1002 课程的成绩

    Select cno from sc where sno=’0001’ and (cno=’1001’ or cno=’1002’)

    二 统计

    1 查询姓名中有“明”字的学生人数。

    select count(*) from student where sname like ‘%明%’

    2 计算‘JSJ’系的平均年龄及最大年龄。

    Select avg(sage) , max(sage) from student Where sdept=’JSJ’

    3 计算每一门课的总分、平均分,最高分、最低分,按平均分由高到低排列

    select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno order by avg(grade) desc

    4 计算 1001,1002 课程的平均分。

    Select cno , avg(grade) from sc where cno in (‘1001’,’1002’) Group by cno

    5 查询平均分大于 80 分的学生学号及平均分
    select sc.sno , avg(grade) from sc group by sc.sno having avg(grade)>80

    6 统计选修课程超过 2 门的学生学号

    select sno from sc group by sno having count(*)>2

    7 统计有 10 位成绩大于 85 分以上的课程号。

    Select cno from sc where grade>85 group by cno having count(*) =10

    8 统计平均分不及格的学生学号

    select sno from sc group by sno having avg(grade)<60

    9 统计有大于两门课不及格的学生学号

    select sno from sc where grade<60 group by sno having count(*) >2

    三 连接

    1 查询 JSJ 系的学生选修的课程号 select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’

    2 查询选修 1002 课程的学生的学生姓名 (不用嵌套及嵌套 2 种方法)
    a: select sname from student,sc where student.sno = sc.sno and cno=’1002’)

    b: select sname from student where sno in (select sno from sc where cno=’1002’)

    3 查询数据库原理不及格的学生学号及成绩

    select sno,grade from sc ,course where sc.cno=course.cno and cname=’数据库原理’

    4 查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套 2 种方法)

    a: select sname from student , sc , course where student.sno=sc.sno and sc.cno = course.cno and grade>80 and cname=’数据库原理’

    b: select sname from student where sno in ( select sno from sc where grade>80 and cno in ( select cno from course where cname=’数据库原理’) )

    5 查询平均分不及格的学生的学号,姓名,平均分。

    select sno, max(sname) , avg(grade) as avggrade from sc , student where student.sno=sc.sno group by student.sno having avg(grade) <60

    6 查询女学生平均分高于 75 分的学生姓名。

    A: Select sname from student where ssex=’女’ and sno in (Select sno from sc group by sno having avg(grade)>75)

    B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=’女’ Group by student.sno having avg(grade)>75

    7 查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出,不能 遗漏)

    select student.sno,sname,cno,grade from student left join sc ON student.sno=sc.sno and ssex=’男’

    四 嵌套、相关及其他

    1 查询平均分不及格的学生人数

    select count(*) from student where sno in ( select sno from sc group by sno having avg(grade)<60 )

    2 查询没有选修 1002 课程的学生的学生姓名

    select sname from student where sno not in( select sno from sc where cno=’1002’)

    3 查询平均分最高的学生学号及平均分

    select sno,avg(grade) from sc group by sno having avg(grade) >=all ( select avg(grade) from sc group by sno )

    *4 查询没有选修 1001,1002 课程的学生姓名。

    Select sname from student where not exists (Select * from course where cno in (‘1001’,’1002’) and Not exists ( select * from sc where sno=student.sno and cno=course.cno ) )

    5 查询 1002 课程第一名的学生学号

    select sno from sc where cno=’1002’ and grade >=all (select grade from sc where cno=’1002’)

    6 查询平均分前三名的学生学号

    select top 3 sno from sc group by sno order by avg(grade) desc

    7 查询 JSJ 系的学生与年龄不大于 19 岁的学生的差集

    a: select * from student where sdept=’JSJ’ and sage>19

    b: select * from student where sdept=’JSJ’ except select * from student where sage<19

    8 查询 1001 号课程大于 90 分的学生学号、姓名及平均分大于 85 分的学生学号、姓名

    select student.sno,sname from student,sc where cno=’1001’ and grade>90 union select sno,sname from student where sno in ( select sno from sc group by sno having avg(grade)>85 )

    9 查询每门课程成绩都高于该门课程平均分的学生学号

    select sno from student where sno not in ( select sno from sc X where grade<( select avg(grade)
    from sc Y where Y.sno=X.sno) )

    select sno from student where sno not in ( select sno from sc X where grade < ( select avg(grade) from sc where cno=X.cno ) )

    10 查询大于本系科平均年龄的学生姓名

    select sname from student X where sage > ( select avg(sage) from student y where sdept=x.sdept)

    以上为查询操作的所有内容,如果有错误或使用时有问题请在评论指出。

    展开全文
  • 这篇博文的均是以SQL server 数据库为基础的基本查询语句的实现。涉及到学生表选课表和课程表,基本操作都附加在查询语句后,还请读者留意。 查询分析器下的语句实现: create database scs; use SCS; ...

     这篇博文的均是以 SQL server 数据库为基础的基本查询语句的实现。涉及到学生表选课表和课程表,基本操作都附加在查询语句后,还请读者留意

     

     

    查询分析器下的语句实现:

     

    create database scs;
    use SCS;
    create table student
    (
       sno char(7)  primary key,
       sname char(10)  not null,
       ssex char(2)  ,
       sage tinyint,
       sdept char(20)
    
    );
    create table course 
    (
        cno char(6) primary key,
        cname char(20) not null,
        credit tinyint ,
        semster tinyint
    );
    
    create table sc
    (
        sno char(7)  ,
        cno char(6)  ,
        grade smallint,
        primary key (sno,cno),      ---主键为两个的时候,不能用列级约束
        foreign key(sno) references student(sno),     ---此时约束应该为表级约束,且foreign key(sno/cno),(sno/cno)在列级约束的时候可以省略,在表级约束不行
        foreign key(cno) references course(cno)
    );
    select *from student
    insert into student
    values('1512101','李勇','男',19,'计算机系'),
    ('1512102','刘晨','男',20,'计算机系'),
    ('1512103','王敏','女',18,'计算机系'),
    ('1512104','李小玲','女',19,'计算机系'),
    ('1521101','张立','男',22,'信息系'),
    ('1521102','吴宾','女',21,'信息系'),
    ('1521103','张海','男',20,'信息系'),
    ('1531101','钱小平','女',18,'数学系'),
    ('1531102','王大力','男',19,'数学系')
    
    select * from course
    insert into course
    values('c001','计算机文化学',3,1),
    ('c002','高等数学',6,1),
    ('c003','高等数学',3,2),
    ('c004','大学英语',6,2),
    ('c005','java',2,3),
    ('c006','程序设计',3,3),
    ('c007','数据结构',5,4),
    ('c008','操作系统',4,4),
    ('c009','数据库基础',4,5)
    
    select *from sc
    insert into sc
    values ('1512101','c001',90),
    ('1512101','c002',86),
    ('1512101','c003',92),
    ('1512101','c005',88),
    ('1512101','c006',null),
    ('1512102','c001',76),
    ('1512102','c002',78),
    ('1512102','c005',66),
    ('1512104','c002',66),
    ('1512104','c005',78),
    ('1512104','c008',66),
    ('1521102','c001',82),
    ('1521102','c005',75),
    ('1521102','c007',92),
    ('1521102','c009',50),
    ('1521103','c002',68),
    ('1521103','c006',null),
    ('1521103','c007',null),
    ('1521103','c008',78),
    ('1531101','c001',80),
    ('1531101','c005',50),
    ('1531101','c007',45),
    ('1531102','c001',80),
    ('1531102','c002',75),
    ('1531102','c005',85),
    ('1531102','c009',88)
    
    select sno as 学号,cno as 课程号,grade as 成绩 from sc     ---查询学生选课表的所有信息
    
    select sname as 姓名,sage as 年龄 ,sdept as 系名  from student where sdept='计算机系'    --查询计算机系的学生的姓名和年龄
    
    select sno as 学号,cno as 课程号, grade as 成绩 from sc where grade between 70 and 80    --查询成绩在70-80之间的学生的学号课程号和成绩
    
    select sname as 姓名,sage as 年龄,ssex as 性别, sdept as 系名 from student where sdept='计算机系' and ssex='男' and sage between 18 and 20    ---查询计算机系年龄在18-20岁之间的且性别为男的学生的姓名和年龄
    
    select MAX(grade) as 最高分数 from sc where cno='c001'   --查询课程号c001的课程的最高分数
    select MAX(sage) as 最大年龄,MIN(sage) as 最小年龄 from student where sdept='计算机系'    ---查询计算机系学生的最大年龄和最小年龄
    
    select sdept as 系名,count(*) as 人数 from student 
    group by sdept                   --统计每个系的学生人数
    
    select cno as 课程号, COUNT(*) as 选课人数,MAX(grade) as 考试最高分 from sc
    group by cno              ---统计每门课程的选课人数和考试的最高分
    
    select sno as 学号, COUNT(*)as 选课门数,sum(grade) as 考试总成绩 from sc 
    group by sno
    order by 选课门数                   ---统计每个学生的选课门数和考试的总成绩,并按选课门数的升序显示结果
    
    select sno as 学号, grade =SUM(grade)from sc 
    group by sno   having SUM(grade)>200               ---10、查询总成绩超过200分的学生,要求列出学号,总成绩
    
    
    select sname as 姓名,sdept as 所在系 from student join sc  on student.sno=sc.sno where cno='c002'          --查询选修了c002号课程的学生的姓名和所在系
    select sname as 姓名,cno as 课程,grade as 成绩 from student join sc on student.sno=sc.sno where grade>80
    order by grade desc           ---查询成绩在80分以上的学生的姓名、课程号、成绩,并按成绩的降序排列结果
    select sname as 姓名,student.sno as 学号,sdept as 所在系 from student join sc on student.sno=sc.sno join course on sc.cno=course.cno
    where course.cno=null            ---查询哪些学生没有选课,要求列出其学号,姓名和所在系
    
    select  cname as 课程名,semster as 开课学期 from course where semster =
    (
       select semster from course where cname='java'     ---查询与java在同一学期开设的课程的课程名和开课学期
    )
    
    
     select sname as 姓名,sdept as 所在系,sage as 年龄 from student where sage=
     (select sage from student where  sname='李勇')      ---查询和李勇年龄相同的学生的学号姓名和所在系
      
     select  sname as 姓名,sdept as 所在系 from student join sc on student.sno=sc.sno join course on sc.cno=course.cno
     where course.cno='c001'             ---查询选修了c001课程的学生的姓名和所在系   ,这是连接查询
     select  sname as 姓名,sdept as 所在系 from student where sno=where (select cno from sc)
    
    
    ----实验三实验四内容部分
    select sname as 姓名,sdept as 所在系 from student  where sno in
    (
      select sno from sc where cno='c001'      -----查询选修了“c001”号课程的学生的姓名和所在系
    )
    
    ---或者用到exists
    select sname ,sdept from student  where exists
    (select *from sc
    where sno=student.sno
    and cno='c001'
    )
    
    
    
    select  student.sno ,sname ,cno,grade from student join sc on student.sno=sc.sno  where grade>80                    
                                                                      
     and sc.sno in (select sno from student where sdept='数学系')-----查询数学系成绩80分以上的学生的学号、姓名、课程号和成绩。
    
    select sname from student where sno in
    ( 
         select sno from sc where grade=(select MAX(grade) from sc)      ----查询计算机系考试成绩最高的学生的姓名
    )and sdept='计算机系'     
    
       
    
    select sname ,sdept from student where sno in 
    
    (
    
     select sno from sc  join course on sc.cno=course.cno where grade=(select MAX(grade) from sc) and cname='数据结构'
                               ------查询数据结构考试成绩最高的学生的姓名和所在系。 子查询和连接查询套用
    )
    
    
    
    select sname ,sdept from student where sno in
    (
       select sno from sc where cno in 
       (
       
         select cno from course where cname='数据结构'              ------查询数据结构考试成绩最高的学生的姓名和所在系
       
       ) and grade= (select MAX(grade) from sc)
    
    )
    
     
    ----1、建立一个存储过程proc_ student,要求该存储过程实现:查询某一位学生是否选修某门课程,
    --若没有选修,则该生对这门课程进行选修(即在SC表中插入相应的学生学号和课程号);若已选修,则显示学生学号、课程名称及成绩。
    --执行存储过程proc_ student。
    ---、删除所有新建的存储过程。
    
    if exists( select * from sysobjects where name='proc_student'  and type='p' )
    drop proc proc_student
    
    
    go 
    create proc proc_student (@no char(7),@no1 char(6))
    as 
    begin
    	if exists (select * from student join sc on sc.sno=student.sno where sc.sno=@no and cno=@no1)
    
    		select * from sc where sno=@no and cno =@no1
    	else 
    
    		insert into sc(sno,cno)
    	    values(@no,@no1)
    end
      exec proc_student '1512101','c004'
      select *from sc
      drop proc proc_student
      
      
    
    
    

     

     

    所有作品均为作者原创,正处在学习数据库的道路上,希望学习数据库的你们,可以从我的

    分享中有所收获。个人能力毕竟有限,如果大家有的地方可以进行查询语句的优化,欢迎大家留言指正。

    copyright@作者

     

     

    更多精彩内容请关注公众号:干货分享录

    展开全文
  • MySQL数据库的基本查询语句题目:



    在数据库建立四个表:分别为

    student(sid,sname,sage,ssex)

    teacher(tid,tname)

    course(cid,cname,tid)

    sc(sid,cid,score)


    -- 1、查询“001”课程比"002"课程成绩高的所有学生的学号。

    select a.sid FROM
    (select * from sc where cid="001") as a,
    (select * from sc where cid = "002")as b
    where a.sid = b.sid and a.score>b.score


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

    select sid,avg(score) 
    FROM sc
    GROUP BY sid
    HAVING avg(score)>=70;

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

    select student.sid, sname,COUNT(*),sum(score)
    from sc,student
    where sc.sid = student.sid 
    GROUP BY sid;


    -- 4、查询姓“李”的老师的个数

    select COUNT(*)
    from teacher
    where tname LIKE '大%';


    -- 5、查询没学过“叶平“老师的课程的同学的学号、姓名

    SELECT sid,sname
    from student
    where sid NOT in(select sid
    from sc,course,teacher
    where sc.cid = course.cid and teacher.tid = course.tid and teacher.tname = "叶良辰");


    --  6、查询所有    课程有挂科的同学的学号、姓名

    SELECT sid ,sname
    from student
    where 
    sid in (select sid from sc) AND
    sid not in(select sid from sc where score<60 GROUP BY sid);


    --  7、查询至少一门课与学号为“2”的同学所学相同的学生的学号和姓名

    SELECT DISTINCT(sc.sid)
    from sc,student
    where student.sid = sc.sid and cid in(select cid from sc where sc.sid='2') and student.sid<>'2';


    -- 8、统计列印各科成绩,各分数段人数:课程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 '85-100',
    SUM(case WHEN score between 70 and 84 then 1 else 0 end) as '70-84',
    SUM(case WHEN score between 69 and 60 then 1 else 0 end) as '60-69',
    SUM(case WHEN score between 0 and 100 then 59 else 0 end) as '0-59'
    from course,sc
    where sc.cid=course.cid
    GROUP BY sc.cid;


    --  9、查询每门课程的课程名和选修的学生数

    select cname,count(*)
    from sc,course
    where course.cid=sc.cid 
    group by sc.cid;


    -- 10、查询出只选修了一门课程的全部同学的学号、姓名

    select sc.sid,sname
    from sc,student
    where student.sid=sc.sid
    GROUP BY (sc.sid)
    HAVING COUNT(*)=1;


    -- 11、查询男生、女生的人数

    (select "男生" AS "性别",count(*) from student where ssex="男")
    UNION
    (select "女生" as "性别", count(*) from student where ssex="女");


    --  12、查询姓“李”的师生名单

    (select sname as '名单' from student where sname like '李%')
    union
    (select tname as '名单' from teacher where tname like "李%");





    在数据库建立三个表:

    学生表:student(sno,sname,sage,ssex,sdept)==(学号,姓名,年龄,性别,系别)

    课程表:course(cno,cname,credit)==(课程号,课程名,学分)

    选课表:sc(sno,cno,grade)===(学号,课程号,成绩)

    -- 写出选修了数据结构的同学的学号和姓名

    select *
    from student
    where sno in(select sno
     from sc,course 
    where sc.cno=course.cno and cname='数据结构' 
    );

    -- 1.统计每门课的选课人数,包括没有人选的课程,列出课程号及选课情况,其中选课情况为,如果此门课的选课人数超过100
    -- 人,则显示人多,40-100一般 1-40人好,无人选

    select  course.cno ,
    case 
    when (count(*)>=40 and count(*)<=100) then  '较多' 
    when (count(*)>1 and count(*)<40) then '较少'
    else '无人选' end as '选课情况'
    from course left join sc
    on course.cno=sc.cno
    GROUP BY cno;

    --  2.查询计算机有哪些学生没有选课,列出姓名和学号(用外连接)
    select sname
    from student left join  sc on
    sc.sno=student.sno
    where sdept="计科" and sc.sno is null;

    2>-- 成绩小于60的学生姓名,课程,成绩

    select sname,cname,grade
    from student,sc,course
    where student.sno=sc.sno and sc.cno=course.cno
    and grade<60;

    -- 3. 统计每个学生的选课人数和考试总成绩,并按照选课门数升序排列
    select sno,count(*)'选课门数' ,sum(grade)'总成绩'
    from sc
    GROUP BY sno
    ORDER BY count(*) DESC;



    展开全文
  • Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 _____________________________________________________________________________________________________...

    Student(S#,Sname,Sage,Ssex) 学生表
    Course(C#,Cname,T#) 课程表
    SC(S#,C#,score) 成绩表
    Teacher(T#,Tname) 教师表

    ___________________________________________________________________________________________________________

    1、查询“001”课程比“002”课程成绩高的所有学生的学号;

    select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
    from SC where C#='002') b
    where a.score>b.score and a.s#=b.s#;

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

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

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

    select Student.S#,Student.Sname,count(SC.C#),sum(score)
    from Student left Outer join SC on Student.S#=SC.S#
    group by Student.S#,Sname;

    4、查询姓“李”的老师的个数;

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

    5、查询没学过“叶平”老师课的同学的学号、姓名;

    select Student.S#,Student.Sname
    from Student
    where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');

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

    select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');

    7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

    select S#,Sname
    from Student
    where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平'));

    8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

     

    Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
    from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;


    9、查询所有课程成绩小于60分的同学的学号、姓名;

    select S#,Sname
    from Student
    where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

    10、查询没有学全所有课的同学的学号、姓名;

    select Student.S#,Student.Sname
    from Student,SC
    where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

    11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

    select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';

    12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;

    select distinct SC.S#,Sname
    from Student,SC
    where Student.S#=SC.S# and C# in (select C# from SC where S#='001');

    13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

    update SC set score=(select avg(SC_2.score)
    from SC SC_2
    where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');

    14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;

    select S# from SC where C# in (select C# from SC where S#='1002')
    group by S# having count(*)=(select count(*) from SC where S#='1002');

    15、删除学习“叶平”老师课的SC表记录;

    Delect SC
    from course ,Teacher
    where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';

    16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、
    号课的平均成绩;

    Insert SC select S#,'002',(Select avg(score)
    from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');

    17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分;

    SELECT S# as 学生ID
    ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
    ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
    ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
    ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
    FROM SC AS t
    GROUP BY S#
    ORDER BY avg(t.score)

    18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分

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

    19、按各科平均成绩从低到高和及格率的百分数从高到低顺序;

    SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
    ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
    FROM SC T,Course
    where t.C#=course.C#
    GROUP BY t.C#
    ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

    20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004);

    SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
    ,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
    ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
    ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
    ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
    ,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
    ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
    ,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
    FROM SC 

    21、查询不同老师所教不同课程平均分从高到低显示;

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

    22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004)
    [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩;

    SELECT DISTINCT top 3
    SC.S# As 学生学号,
    Student.Sname AS 学生姓名 ,
    T1.score AS 企业管理,
    T2.score AS 马克思,
    T3.score AS UML,
    T4.score AS 数据库,
    ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
    FROM Student,SC LEFT JOIN SC AS T1
    ON SC.S# = T1.S# AND T1.C# = '001'
    LEFT JOIN SC AS T2
    ON SC.S# = T2.S# AND T2.C# = '002'
    LEFT JOIN SC AS T3
    ON SC.S# = T3.S# AND T3.C# = '003'
    LEFT JOIN SC AS T4
    ON SC.S# = T4.S# AND T4.C# = '004'
    WHERE student.S#=SC.S# and
    ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
    NOT IN
    (SELECT
    DISTINCT
    TOP 15 WITH TIES
    ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
    FROM sc
    LEFT JOIN sc AS T1
    ON sc.S# = T1.S# AND T1.C# = 'k1'
    LEFT JOIN sc AS T2
    ON sc.S# = T2.S# AND T2.C# = 'k2'
    LEFT JOIN sc AS T3
    ON sc.S# = T3.S# AND T3.C# = 'k3'
    LEFT JOIN sc AS T4
    ON sc.S# = T4.S# AND T4.C# = 'k4'
    ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);

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

    SELECT SC.C# 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.C#=Course.C#
    GROUP BY SC.C#,Cname;

    24、查询学生平均成绩及其名次;

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

    25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
    FROM SC t1
    WHERE score IN (SELECT TOP 3 score
    FROM SC
    WHERE t1.C#= C#
    ORDER BY score DESC
    )
    ORDER BY t1.C#;

    26、查询每门课程被选修的学生数;

    select c#,count(S#) from sc group by C#;

    27、查询出只选修了一门课程的全部学生的学号和姓名;

    select SC.S#,Student.Sname,count(C#) AS 选课数
    from SC ,Student
    where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;

    28、查询男生、女生人数;

    Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';
    Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女';

    29、查询姓“张”的学生名单;

    SELECT Sname FROM Student WHERE Sname like '张%';

    30、查询同名同性学生名单,并统计同名人数;

     

    select Sname,count(*) from Student group by Sname having count(*)>1;

    31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime);

    select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
    from student
    where CONVERT(char(11),DATEPART(year,Sage))='1981';

    32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;

     

    Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;

    33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

    select Sname,SC.S# ,avg(score)
    from Student,SC
    where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;

    34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数

    Select Sname,isnull(score,0)
    from Student,SC,Course
    where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;

    35、查询所有学生的选课情况;

    SELECT SC.S#,SC.C#,Sname,Cname
    FROM SC,Student,Course
    where SC.S#=Student.S# and SC.C#=Course.C# ;

    36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

    SELECT distinct student.S#,student.Sname,SC.C#,SC.score
    FROM student,Sc
    WHERE SC.score>=70 AND SC.S#=student.S#;

    37、查询不及格的课程,并按课程号从大到小排列;

    select c# from sc where scor e <60 order by C# ;

    38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;

     

    select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';
     

    39、求选了课程的学生人数;

    select count(*) from sc;

    40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩

    select Student.Sname,score
    from Student,SC,Course C,Teacher
    where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );

    41、查询各个课程及相应的选修人数;

    select count(*) from sc group by C#;

    42、查询不同课程成绩相同的学生的学号、课程号、学生成绩;

     

    select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;

    43、查询每门功成绩最好的前两名
    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
    FROM SC t1
    WHERE score IN (SELECT TOP 2 score
    FROM SC
    WHERE t1.C#= C#
    ORDER BY score DESC
    )
    ORDER BY t1.C#;

    44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列;

    select C# as 课程号,count(*) as 人数
    from sc
    group by C#
    order by count(*) desc,c#;

    45、检索至少选修两门课程的学生学号;

    select S#
    from sc
    group by s#
    having count(*) > = 2;

    46、查询全部学生都选修的课程的课程号和课程名;

     

    select C#,Cname
    from Course
    where C# in (select c# from sc group by c#);


    47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

    select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');

    48、查询两门以上不及格课程的同学的学号及其平均成绩;

    select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;

    49、检索“004”课程分数小于60,按分数降序排列的同学学号;

     

    select S# from SC where C#='004'and score <60 order by score desc;

    50、删除“002”同学的“001”课程的成绩;

    delete from Sc where S#='001'and C#='001';

     

    转载至:https://www.jb51.net/article/67932.htm

     

    展开全文
  • drop database if exists SS; create database SS; use SS; create table Student ( Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallint, Sdept char(20) ...cre
    drop database if exists SS;
    create database SS;
    
    use SS;
    
    create table Student 
    (
     Sno char(9) primary key,
     Sname char(20) unique,
     Ssex char(2),
     Sage smallint,
     Sdept char(20)
    );
    
    create table Course
    (
      Cno char(4) primary key,
      Cname char(40),
      Cpno char(4) references Course(Cno),
      Ccredit smallint
      
    );
    
    
    create table SC
    ( 
       Sno char(9) references Student(Sno),
       Cno char(4) references Course(Cno),
       Grade smallint,
       primary key(Sno,Cno)
    );
    


     

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

    例一):查询全体学生的姓名及其出生年月
       问题分析:
              要查询的数据是:Sname, 出生年份(表中没有此列,不过可以用计算求得)
              从哪些表可以得到要查询的数据:Sname 是Student的属性,Sage也是Student的属性,所以说本体要查询
                          的结果在Student中就可以得到,其中出生年月用现在的年份减去年龄即可  
             查询语句: select Sname,20012 - Sage 
                               from Student;
    ---------------------------------------------------------------------------------
    例二):查询选修了课程的学生的学号
         问题分析:
              要查询的数据:Sno
              分析:选修了课程,换句话说就是课程号Cno不为空的那些元祖对应的Sno,
                       SC表中已经含有了Cno信息同时包含了学号,同时要注意去掉重复的数据
                        因为多个Cno可以对应于一个Sno1
              从哪些表可以得到要查询的数据:Sno 和Cno是SC的属性列,所以在SC中即可获得所需信息
              查询语句:select distinct Sno
                              from SC;
    ---------------------------------------------------------------------------------
    例三):查询院系在CS,MA,IS学生的姓名和性别
           问题分析:
                 要查询的数据:Sname,Ssex
                 从哪些表中可以得到要查询的数据:Sname 和Ssex 是Student的属性列,因此在Student表里查询即可
                 查询语句:
                      1)select Sname,Ssex
                            from Student
                             where Sdept = 'CS' or Sdept = 'MA' or Sdept = 'IS';
                    用谓词in也可以查找属性属于指定集合的元祖,所以有查询2
                     2)select Sname,Ssex
                           from Student
                           where Sdept in ('CS','MA','IS');
    ----------------------------------------------------------------------------------------------
    例四):查询“各个”课程号(Cno)以及相应(也就是说课程号对应的)的选课人数
            问题分析:
                   要查询的数据:Cno ,选课人数
                   查询用到的表、:Cno 以及Sno是SC的属性列
                   分析:由于一个Cno可以对应于多个Sno,,所以可以把具有相同Cno的值用group by分为一组,
                         然后对每一组用 count计算,即计算相同的一组有多少行,用count(Cno)试试看
                   查询语句:
                        先分组
                         1):select Cno,Sno from SC group by Cno,Sno
                             /*注意不能写成select Cno,Sno from SC group by Cno,因为group by中有一个原则
                                   就是select 后面的所有列中,没有使用聚合函数的列必须出现在group by后面*/
                        2):select Cno,count(Sno) as num/*count(Sno)是计算相同的一组中有多少个Sno或者说有多少个Sno为一组*/
                             from 
                             group by Cno

                    需要特别注意的是对group by的目的是为了细化聚集函数的作用对象。如果没有对查询
                    结果进行分组,那么聚集函数讲作用于整个查询结果,例如select count(*) from Student,count(*)
                    讲会计算得出所有元组的个数,也就是学生的总人数;而分组后聚集函数将作用于每一组,也就是每一组都有一个
                    函数值,所以上面count(Sno)是计算Cno为某一个数值a时对应多少个Sno,而不是全部查询结果的Sno
    ------------------------------------------------------------------------------------------------------------------------------------
    例五):查询选修了2门以上的课程的学生学号
           问题分析:
                  查询的数据:Sno
                  查询用到的表:由于课程信息在SC中,所以只需用SC表即可
                  分析:由于一个Sno可以对应于多个Cno,所以可以把就有相同Sno的值用group by分为一组
                        就可以得到一个Sno对应的选择课程情况,然后用count(*)对每一组计数,,此处用having 短语来进行条                                                        件筛选
                查询语句:select Sno from SC group by Sno having count(*)>2;
    ---------------------------------------------------------------------------------------------------------------------------
    例六):查询每个学生的(学号)Sno.(姓名)Sname,Cname(课程名),Grade(成绩)
         问题分析:
                 查询数据:Sno,Sname,Cname,Grade
                 涉及的表:应为Sno和Sname是Student的属性列,Cname是Course的属性列,Grade是SC的属性列,所以用到Student,Course,SC三个表
                 分析:三个表是根据Sno,Cno联系起来的
                 查询语句:select Student.Sno,Sname,Cname,Grade
                           from Student,SC,Course
                           where Student.Sno = SC.Sno and SC.Cno = Course.Cno;
    --------------------------------------------------------------------------------------------------------------------------
    例七):查询与“刘晨”在同一个系学习的学生
            问题分析:
                查询数据:学生信息,限定条件是与刘晨同一个系,
                未知数据:刘晨所在的系
                已知数据:学生的名字刘晨
                涉及的表:Student
                分析:可以根据已知的数据来查询未知的数据
                查询语句:
                   1)确定刘晨所在的系
                        select Sdept from student where Sname = "刘晨"
                   2)查找在所在系与1查询结果相同的学生信息
                        select * from Student where Sdept = 'CS';
                   综合起来就是:select * from Student where Sdept in (select Sdept from student where Sname = "刘晨");
                   由于每个学生只能有一个系,所以1中的查询结果只有一个,所以也可以写成下面的语句
                   :select * from Student where Sdept = (select Sdept from student where Sname = "刘晨");
                   其实,也可以用自身连接来查询:把Student看成两个表来A和B,那么该题的可以叙述成如下所示:
                       查询A的学生信息:要求A所在的系与B中名字叫刘晨的同学所在的系相同,那么可以写成如下
                       select * from Student as A where Sdept = (select Sdept from student as B where Sname = "刘晨");
                      进而改进为
                           select A.* from Student A,Student B where A.Sdept = B.Sdept and B.Sname = "刘晨";
    -----------------------------------------------------------------------------------------------------------------------------
    例八):找出每个学生超过他选修的课程的平均成绩的课程号
         
                
         问题分析:
                  先换个问题求:求每个学生的成绩超过全班平均成绩的Sno,Cno
                  查询语句:select Sno,Cno from SC where Grade > (select AVG(Grade) from SC)
                 所有对比着所换的那个问题,很容易写出下面的语句
                 select Sno,Cno from SC as A where Grade >=(select avg(Grade) from SC as B where A.Sno = B.Sno);  
    ----------------------------------------------------------------------------------------------------------------------------             
    例九):将计算机系所有的同学的成绩设置为零
          问题分析:
              涉及的表:成绩的表在SC中,院系Sdept在Student表中,因此涉及到两个表SC和Student
     

         查询语句1):   UPDATE SC SET Grade = 100
                   WHERE 'CS'= (select Sdept from Student where Student.Sno = SC.Sno);                                      

          查询语句2):   UPDATE SC set Grade = 0
            where SC.Sno  in (select Student.Sno from Student where Sdept = 'CS');

                        

     
                          

    展开全文
  • 查询数据库语句大全

    2012-11-08 09:04:39
    注:以下操作均建立在上篇...使用SQL查询分析器查询数据,练习查询语句的使用,掌握SELECT 语句的完整结构,包括简单查询、嵌套查询、连接查询等基本实现方法。   三、实验主要仪器设备和材料 1.计算机及操
  • 数据库索引和SQL语句

    2019-05-23 20:57:42
    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。 在表Student上按Sno降序建唯一索引 create unique index Stusno on Student (Sno desc); 删除Student表的Stusno...
  • 您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货! 在网上找了好多SQL数据库的教程,但你还会面临这样的问题: 1. 自己写了...
  • 数据库SQL语句差异

    2015-01-21 11:02:15
    因为数据库SQL语句在各大产品中带有“方言性”,即SQLSERVER SYBASE都是用了T-SQL,Mysql是用的标准SQL,Oracle有有自己的PL/SQL。由于这种“方言性”的差异导致很多SQL语句在移植的时候产生困难,在异构数据库的...
  • 一、数据库数据库表定义 1、创建基本表 create table ([列级完整性约束条件] [,[列级完整性约束条件] ......... 
  • 最近在搞java课程设计,在查询数据库数据的时候只要查询语句中包含中文就怎么都查询不到数据,想想应该是文件编码问题,看了一下数据库编码为utf-8,又看了一下源文件编码,居然是gbk,将gbk改为了utf-8,再次查询,...
  • 1.对于student表,将所有所在系部信息为’CS’的改为’计算机科学系’ Update student Set ...对于course表,将数据结构课程的学分改为3。 Update course Set ccredit=3 Where cname=’数据结构’ 3.对于stude...
  • 转载:本文转载自:... 一.有以下几张表及表结构 Student(Sid,Sname,Sage,Ssex) 学生表 Course(Cid,Cname,Tid) 课程表 SC(Sid,Cid,Score) 成绩表 Teacher(Tid,Tname) 教师表 题目: 1.查询“某1”...
  • 数据库基础语句

    2020-02-29 14:20:17
    文章目录DDL数据库表索引DML查找语句以及注意事项DCL别名group by的注意事项常见问题sql语句的执行顺序where和having的区别Not null unique和primary key的区别内连接 左连接 右连接 外连接MySQL中between and的取值...
  • 最近在做数据库课程设计的时候,遇到一个小问题,总是不知道服务器对数据库都执行了哪些操作,进过网上的一番资料查找,终于找到了一个成功的方法。首先说明一下,我用的是腾讯云的ubuntu系统。 第一步:修改/etc/...
  • 常用sql查询语句

    2018-01-05 10:46:29
    # --1.学生表 # Student(S#,Sname,Sage,Ssex) -- # # --2.课程表 # Course(C#,Cname,T#) -- # # --3.教师表 ...# --4....# 完成下列查询需求: ...#查询"01"课程比"02"课程成绩高的学生的信息及课程
  • 数据库数据查询汇总

    2020-01-02 21:56:36
    本篇博文主要是数据库SQL语句的总结,其中会有一些经常会忽略的小知识点。这里总结了数据查询和数据更新,其中比较重要且有难度的是连接查询和嵌套查询。最后列出了我在项目中所遇到的问题,但是SQL语句并没有做多少...
  • #IN 子查询#采用IN 子查询获得参加考试的在读学生名单SELECT student_no,student_name FROM student WHERE student_noIN(SELECT student_no FROM resultWHERE subject_no=(#获得参加Logic Java课程最近一次考试的...
  • 数据库查询语句面试

    2014-05-05 18:00:45
    Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 ...1、查询“001”课程比“002”课程成绩高的所有学生的学号; select a.S# from (select s#,score from SC where C#=’001′) 
  • MySQL语句练习,附带结果截图以供参考
1 2 3 4 5 ... 20
收藏数 46,789
精华内容 18,715
关键字:

数据库查询课程语句