数据库查询的例子及SQL语句

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');