数据库查询的例子及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');