SQL之学生选课数据库

1、E-R图

 

2、关系模式

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

3、实现SQL

/*建立学生表*/
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*/
)

4、修改基本表

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

4.1 为Student添加用户密码属性列

alter table STUDENT add Spwd varchar2(20);

4.2 增加课程名取唯一值约束

alter table Course Add unique(Cname);

4.3 将Spwd的数据类型由varchar2(20)改为varchar2(30)

alter table STUDENT alter column Spwd varchar2(30);

5、删除基本表

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

Restrict说明删除是有条件的，cascade说明该表的删除没有任何限制。

6、数据查询

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

6.1 查询全体学生的学号和姓名

select sno,sname from STUDENT t

6.2 查询学生的姓名和出生年

select sname,(2013-Sage) as birthYear from STUDENT t

6.3 查询学生的姓名和所在系名，并将系名转换为小写

select sname,lower(sdept) from STUDENT t

6.4 消除取值重复行

select distinct cno from SC t

6.5 查询GIS专业学生的学号和姓名

select sno,sname from  STUDENT where sdept='GIS'

6.6 查询年龄小于25的学生的学号和姓名

select sno,sname from  STUDENT where sage<25

6.7 查询年龄介于20-25之间的学生的学号和姓名

select sno,sname from  STUDENT where sage between 20 and 25

6.7 查询年龄不介于20-25之间的学生的学号和姓名

select sno,sname from  STUDENT where sage not between 20 and 25

6.8 查询GIS和RS系学生的学号和姓名

select sno,sname from  STUDENT where sdept in ('GIS','RS')

6.9  查询不在GIS和RS系学生的学号和姓名

select sno,sname from  STUDENT where sdept not in ('GIS','RS')

6.10 字符匹配

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

6.11 排序查询

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

6.12 聚集函数

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

6.12.1 查询学生总人数

select count(*) as Scount from student

6.12.2 查询GIS课程的平均成绩

select avg(grade) as gisAvg from sc where cno=(select cno from course where cname='GIS')

6.12.2 查询学生牛一的平均成绩

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

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

select cno,count(sno) from SC group by cno

6、复合查询

6.14 查询选修2号课程且成绩在80分以上的学生

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

6.15 查询每个学生的学号、姓名、选修课程名以及成绩

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

6.16 查询和牛一在同一个系的学生

select sno, sname
  from STUDENT
 where sdept = (select sdept from STUDENT where sname = '牛一')

6.17 查询选修了GIS课程的学生

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

7、数据更新

7.1 插入数据

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

7.2 修改数据

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

7.3 删除数据

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

附录：

视图：从一个或者几个基本表（视图）导出的表，他是一个虚表。

1、建立视图

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

2、删除视图

drop view < 视图名 > [ cascade ];

3、视图的其他操作

视图的其他操作与表的操作类似。

4、视图的作用

1、简化用户操作；
2、使用户能以多种角度看同一数据；
3、对重构数据库提供了一定的逻辑独立性；
4、对机密数据提供安全保护；
5、适当使用视图可以更清楚的表达查询。

转载于:https://www.cnblogs.com/lzugis/archive/2013/04/07/6539934.html