SQL之学生选课数据库

Student(Sno, Sname, Sage, Ssex, Sdept)
Course(Cno, Cname, Cpno, Ccredit)
SC(Sno, Cno，Grade)

/*建立学生表*/
create table Student(
    Sno varchar2(9) primary key,
    Sname varchar2(20) unique,
    Ssex varchar2(2),
    Sage integer,
    Sdept varchar2(20)
)
/*建立课程表*/
create table Course(
    Cno varchar(4) primary key,
    Cpno varchar(4),
    Cname varchar2(20),
    Ccredit integer,
    foreign key (Cpno) references Course(Cno)
    /*表级完整性约束，Cpno是外码，被参照表是Course，被参照列是Cno*/
)
/*建立学生选课表*/
Create table SC(
    Sno varchar2(9),
    Cno varchar2(4),
    Grade integer,
    primary key(Sno,Cno),
    /*主码有两个属性，必须作为表级完整性进行定义*/
    foreign key (Sno) references Student(Sno),
     /*表级完整性约束，Sno是外码，被参照表是Student，被参照列是Sno*/
    foreign key (Cno) references Course(Cno)
    /*表级完整性约束，Cno是外码，被参照表是Course，被参照列是Cno*/
)

alter table <列名>
[ add <新列名> <数据类型> [完整性约束] ]
[ Drop [完整性约束名] ]
[ alter column <列名> <数据类型> ];

alter table STUDENT add Spwd varchar2(20);

alter table Course Add unique(Cname);

alter table STUDENT alter column Spwd varchar2(30);

drop table <表名> [ restrict|cascade ];

select [ all|distinct ] [ <目标列表达式> ]
from <表名或者视图名>
[ where <条件表达式> ]
[ group by  <列名1> [ having <条件表达式> ] ]
[ order by  <列名2> [ASC|DESC] ]

[not] like '<匹配串>' [escape '<换码字符>']
	%(百分号)代表任何长度的字符串；
	_(下划线)代表任意单个字符。

[ order by  <列名2> [ASC|DESC] ]
	ASC为升序，默认；
	DESC为降序排序。

count ( [distinct|all] * )   //统计元组个数
count ( [distinct|all] <列名> )    //统计一列中值个数
sum ( [distinct|all] <列名> )  //计算某一列值的和
avg ( [distinct|all] <列名> )  //计算某一列值的平均值
max ( [distinct|all] <列名> )  //计算某一列值的最大值
min ( [distinct|all] <列名> )  //计算某一列值的最小值

select avg(grade) as Niu1Avg
  from sc, course
 where sno = (select sno from student where sname = '牛一')
   and sc.cno = course.cno

select student.sno as sno, student.sname as sname
  from student, sc
 where student.sno = sc.sno
   and sc.cno = '2'
   and sc.grade > 80

select student.sno   as sno,
       student.sname as sname,
       course.cname  as cname,
       sc.grade      as grade
  from student, course, sc
 where student.sno = sc.sno
   and sc.cno = course.cno

select sno, sname
  from STUDENT
 where sno in
       (select sno
          from sc
         where cno in (select cno from course where cname = 'GIS'))

insert into < 表名 > [ ( < 属性1 >[ ,< 属性2 > ...) ]
 values ( < 常量1 > [ ,< 常量2 > ...)

 update < 表名 >
    set < 列名 >= < 值 > [, < 列名 >= < 值 > ] 
[ where < 条件 > ]

delete from < 表名 > [ where < 条件 > ]

create view < 表名 > [ （ < 列名 > ,< 列名 > ] 
as < 子查询 >
[ with check option ]

drop view < 视图名 > [ cascade ];