哈工大数据库课堂记录

DDMSch3  DBMSch52  DBMSch53 为重点  6,7,8，是重点

select sID,sName,GPA from Student where GPA>3.6

对   的含义就是 相同，表示某个键值相同
无重复  distinct   or  或
select distinct sName,major from  Student,Apply where Student.sID = Apply.sID

select sName ,GPA,decision

from Student,Apply   where Student.sID = Apply.sID and sizeHS<1000 and major = 'cs' and

cName = 'Standford';

select sName,GPA,decision from Student,Apply where Student.sID = Apply.sID and(sizeHS<1000 or cName=‘Standford’)

字符匹配：like ,not like
逻辑运算： and ,or ,not

select distict sID,sName,GPA from Student where sID between 123 and 567

查询所有学生中学号是123 或 567 的学生信息
select distict sID,sName,GPA   from Student whose sID in （123,567）

select sID,major  from Apply  where major like '%bio%'




输出Student 和 College的所有组合信息
select * from Student,Apply where Student.sID= Apply.sID;

输出所有申请了学校的学生信息
select Student.* from Student,Apply  where Student.sID = Apply.sID;

select sID,sName,GPA,sizeHS,GPA*(sizeHS/1000)  from student;


改名：
select sID,sName,GPA,sizeHS,GPA*(sizeHS/1000) as scaledGPA from student;

%
%ab%
-
---
---%cd%--


select Student.sID,sName,GPA,Apply.cName,enrollment from Student ,College,Apply where Apply.sID = Student.sID and Apply.cName = College.cName order by GPA desc;

////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////
////集合操作

//// union  或

          select sID from Apply where major ='cs'  union select sID from Apply  where major = 'ee';

      select sID from Apply  where major='cs' union all select sID from Apply where major = 'ee';


////  except   且没

          select sID from Apply where major = 'cs' except select sID from Apply where major ='ee'

      select sID from Apply  where major ='cs'  except all select sID from Apply  where major = 'ee'; 


//// intersect  和

       select sID from Apply where major = 'cs' intersect select sID from Apply where major ='ee'

      select sID from Apply  where major ='cs'  intersect all select sID from Apply  where major = 'ee'; 

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//// 嵌套查询

     嵌套查询的作用是去除了 前面的复杂的对的操作

    查询申请了CS的同学的ID和名字

    select sID,sName from Student  where sID in (select sID from apply where major='cs')

    查询没有申请CS的同学的ID和名字

    select sID,sName from Student  where sID not in (select sID from apply where major='cs')


//// where 中比较集合的方式

       =  比较   <>不等于
    查询选了CS没有选EE的学生

    select sID,sName from Student where sID = any(select sID from apply where major='cs')  and sID<>any(select sID from apply where major='ee')

    查询最高分的学生

    select sName,sID from student s1  where not exists  (select * from student s2 where s1.GPA<s2.GPA)

    查询不是最低分的学生

    select sName,sID from student s1 where exists (select * from student s2  where s2.GPA>s1.GPA)


//////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////  
////   Join(连接)系列操作

    join 后面加using 表示以using 后面的为准

 inner join = join  笛卡尔乘积

 natural join  自然连接   先做连接，找到有重复列的行，去掉重复列

 join 后面接个on  相当于是 where


 outerjoin / left outjoin  /right outerjoin / 


//////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////
////     聚集操作

   求学生平均分
    select avg(GPA) from student

   求申请计算机系的学生的最低分

    select min(GPA)  from Student,Apply where Student.sID = Apply.sID and major = 'CS';

注意：这里的逗号表示了两个表已经进行了连接操作

   计算申请了计算机系的学生的平均分

    select avg(GPA) from Student  where sID in (select sID from Apply where major='CS');   //嵌套查询

  计算学费大于一万五的学校个数

    select count(*)  from College  where enrollment>15000

  求申请了CS的学生比没有申请CS的学生GPA高了多少

    select CS.avgGPA - NonCS.avgGPA  from (select avg(GPA) as avgGPA from Student where sID in(select sID from Apply where major = 'cs')) as CS,(select avg(GPA) as avgGPA from Student where sID  not in(select sID from Apply where major = 'cs')) as NonCS;


////   一个牛逼的东西,group by 聚集操作，牛逼的不行不行的



   求大学收费的和
   select sum(enrollment) from college ;

  求每个州大学收费的和
  select state,sum(enrollment) from College group by state;


  求每个学校被申请的次数
  select cName,count(*) from Apply group by cName;

  求每个学校，每个专业申请的最高分和最低分

  select cName,major,max(GPA),min(GPA)  from  Apply,Student where Apply.sID = Student.sID group by cName,major;



////    牛逼的东西，having 


    比如说，查找申请数少于五的学校

    select cName from Apply  group by cName having count(*)<5;

    查找学生申请数少于5的学校

    select cName from Apply group by cName having count(distinct sID)<5;


//  找出专业学生最好的成绩小于平均分的专业

    select major from Student where Student.sID = Apply.sID group by major having max(GPA)<(select avg(GPA) from student)



////  空值null的处理


        找出GPA不为空的学生名单
    select * from student where GPA is not null

    找出成绩大于3.5的学生名单
    select * from Student where GPA>3.5

    找出成绩不大于3.5的学生名单
    select * from Student where GPA<=3.5

    上述两个的并集合
    select * from student where GPA<=3.5 or GPA>3.5 or GPA is null



////////////////////////////////////////////////////////////////////////////////////////

删除，添加，更新元祖操作  delete from /insert into /update 

    删除所有学生
    delete from student

    删除分数高于3的学生
    delete from student where GPA>3

    删除申请了学校的学生
    delete from Student where sID in(select Apply.sID from Apply)

    删除申请了两个专业的学生的名单
    delete from Student where sID in(select sID from Apply group by sID having count(distinct major)>2)

    删除所有没有计算机申请的学校
    delete  from College where cName not in(select cName from  Apply where major='CS');

    删除所有分数小于平均分的学生名单
    delete from  Student where GPA<(select avg(GPA) from Student);

    插入一条元组
    insert into college values('')

    让所有没有申请学校的学生申请CMU
    insert into Apply select sID,'CMU','PA',11500 from student where sID not in (select sID from apply);





实验一经验总结 

  怎么退出异常状态：使用;号即可
  .separator ","
  .import  文件名  表名


实验    11,select sID,sName,GPA from Student where GPA>3.6;

        12,select sID,sName from Student where GPA>3.6;

        13,select sName,cName from Student,Apply where Student.sID = Apply.sID;

    14,select distinct sName,cName from Student,Apply where Student.sID = Apply.sID;
(从上条我们就可以看出，去除重复元组是有多强)

    15,select sName,GPA,decision from Student,Apply where Student.sID=Apply.sID and cName='Stanford' and major='CS' and sizeHS<1000;

    16,select distinct College.cName from College,Apply where College.cName=Apply.cName and major='CS' and enrollment>20000;

    17,select sName,College.cName,GPA,state,enrollment from Apply,Student,College where Apply.sID = Student.sID and Apply.cName = College.cName order by GPA desc;

    在满足上述条件下想让次要条件按enrollment降序就可以在后面加一句enrollment desc;

    18,参照17附加   

    19,select sID,major from Apply where  major like '%bio%';

    20,select * from College,Student;

    21，...略

    22,select s1.sName,s2.sName from Student s1,Student s2 where s1.sID!=s2.sID and s1.GPA=s2.GPA;(去除重复元素似乎不管用) ----------有点问题

    22，23 同

    24，select cName from College union all select sName from Student;(含重复)

        不含重复版本：
       select distinct cName from College union all select distinct sName from Student;

    25,select distinct cName as name from College union all select distinct sName  as name from Student order by name;

    26,25已经完成

    27，select sID from Apply where major=='CS' intersect select sID from Apply where major = 'EE'; 

    28,select sID from Apply where major=='CS' except select sID from Apply where major = 'EE'; 

    29,select sName,sID from Student where sID in (select sID from Apply where major='CS');

    30,select distinct sName from Student,Apply where Student.sID= Apply.sID and major='CS';

    或者：select distinct sName from Student where sID in(select sID from Apply where major='CS');

    31,select distinct sID from Apply where sID in (select sID from Apply where major = 'CS') and sID not in (select sID from Apply where major = 'EE');

    32,select cName from College s1 where  not exists (select * from College s2 where s1.enrollment<s2.enrollment);

    33,select distinct sName,GPA from Student s1 where not exists (select * from Student s2 where s1.GPA<s2.GPA);

    33,select distinct sName,GPA from Student s1 where not exists (select * from Student s2 where s1.GPA<s2.GPA) ;

    34,..

    35,select sName major from Student,Apply where Student.sID=Apply.sID;

    36，select sName,GPA from Student natural join Apply where sizeHS<1000 and cName='Stanford' and major='CS';

        select sName,GPA from Student  join Apply using (sID) where sizeHS<1000 and cName='Stanford' and major='CS';

        select sName,GPA from Student  join Apply  where Student.sID= Apply.sID and sizeHS<1000 and cName='Stanford' and major='CS';

    37,select * from Student natural join Apply natural join College;

    38,

    39,select avg(GPA) from Student ;

    40,select min(GPA) from Student natural join Apply where major='cs';   //聚集查询

    //   select distinct s1.GPA from Student s1 natural join Apply where not exists (select * from Student s2 where s1.GPA>s2.GPA ) and major='CS';

    41，select avg(GPA)  from Student natural join Apply where major='CS';

    42,select count(*) from College where enrollment>15000;

    43,select count(*)  from Apply  where  cName='Cornell' and sID in (select distinct sID from Apply); //这个有问题,牛逼牛逼

    44，select CS.avgGPA-NonCS.avgGPA from (select avg(GPA) as avgGPA from Student natural join Apply where major='CS') as CS,(select avg(GPA) as avgGPA from Student  where sID not in(select sID from Apply where major='CS')) as NonCS;

    45,select cName,count(*) from Apply group by cName;

    46,select state,sum(enrollment) from College group by state;

    47,select cName,major,max(GPA),min(GPA) from Student natural join Apply group by cName,major;   //group by 可以循环使用

    48,select sID,sName,count(*) from Apply natural join Student group  by Apply.sID;

    50,select cName from Apply group by cName having count(*)<5;

查询所有学生中学号在123，567之间的学生信息

    select distinct sID,sName,GPA from Student where sID between 123 and 567;

查询所有学生中学号是123或567的学生信息

    select distinct sID,sName,GPA from Student where sID in (123,567);

学生申请学校的信息  //报错了 0.0

    select cName,state(select distinct sName from Apply,Student where College.cName=Apply.cName and Apply.sID=Student.sID) as sName from College;

    select distinct cName,state,sName from College natural join Apply natural join Student;     //这个故事告诉我们，distinct只能放在前面



//////////////////////////   

    修改删除机制

delete from 

insert into 

update  table  set  ...



/////////////////////////////////////////////////////////
 自己做的课件上的实验


  .open seen.db3

  create table Student(Sno int,Sname text,Ssex text,Sage int,Sdept text);
  create table Course(Cno int,Cname text,Cpno int,Ccredit int);
  create table SC(Sno int,Cno int,Grade int);

    .separator ","   //这个表示分割字符
    .import Course.txt Course
    .import SC.txt SC
    .import Student.txt Student

///////////////////////////////////
  关于导入导出的技巧：

    1.txt文件千万不能有多余的空格

    2.导入时txt文件里面的分割符要和导入的相一致

    3.import语句不要分号

    4.导入的时候是会重复导入的，前面导入的后面还会有

    5.字符串存储相当严格，连空格都会存储进去

查询信息系男生的学号、姓名、年龄

    select Sno,Sname,Sage from Student where Ssex = '男'and Sdept='IS';

查询选修过课的学生的学号

    select distinct Sno from SC;    

查询年龄在25-30之间的学生姓名及性别.

    select Sname,Ssex,Sage from Student where Sage between 25 and 30;

查询姓“欧阳”的学生.

    select * from Student where Sname like '欧阳%';

查询信息系、数学系和计算机系的学生.

    select * from Student where Sdept in ('IS','MA','CS');

//////// 想要让其不重复 ，对count里面的内容进行修正即可

查询各个课程号与相应的选课人数

    select Cno count(Sno) from SC group by Cno;

查询选修了4门课以上的学生的学号

    select Sno,Sname from SC natural join Student group by Sno having count(Cno)>2;

查询所有选课学生的学号及平均分.

    select Sno,avg(Grade),Sname from SC natural join Student group by Sno;

查询每一年龄上人数超过18的男同学的具
体人数, 并按年龄从小到大的顺序排列.

    select Sage,count(Sage) from Student where Ssex='男' group by Sage having count(*)>1 order by Sage;

    Select Student.Sno, Sname, Cname, Grade From Student natural join SC natural join Course;

查询与‘刘晨’在同一个系学习的学生  (问题是如何去掉刘晨呢)

    select * from Student where Sdept in (select Sdept from Student where Sname like'%刘晨%' and Sdept not in (select Sdept from Student where Sname like '%刘晨%'));

查询选课程名为’代数’的学生的学号、姓名.

    select Sno,Sname from Student natural join SC natural join Course where Cname like '%代数%';

    select Sno,Sname from Student where Sno in (select Sno from SC where Cno in (select Cno from Course where Cname like '%数学%'));  //这种方法就是一层一层的找

    select Sno,Sname from Student natural join SC where Cno in (select Cno from Course where Cname like '%数学%');

 查询选修了1号课的学生姓名.

/////////////////////////////////////////////////////////////////////////////////////////

关于各种操作

//// 选择操作符


////  集合并 A并B  把A和B的所有元素并在一起

      集合交 A交B  从A中减去A和B都有的元素