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、适当使用视图可以更清楚的表达查询。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java3y

移动商城第七篇【购物车增删改查、提交订单】

把商品加入购物车 接下来我们要做的就是将商品加入到购物车中。我们这次使用的是Cookie来将用户的信息存储起来。那为什么要用cookie呢?? 如果将购物车存储...

1.5K140
来自专栏Java架构

前大众点评资深研发专家对Mysql索引的解析与底层数据结构的解刨

mysql索引: 是一种帮助mysql高效的获取数据的数据结构,这些数据结构以某种方式引用数据,这种结构就是索引。可简单理解为排好序的快速查找数据结构。如果要查...

23140
来自专栏企鹅号快讯

利用pandas进行数据分析(二):索引与层次化索引

继上一节的基本数据结构的介绍之后,本节继续介绍中操作和的基本手段。一个最常用的操作就是索引,如何根据分析目的对和进行索引访问得到数据是利用进行数据分析的基本技能...

24190
来自专栏跟着阿笨一起玩NET

C#常用工具类——Excel操作类

18110
来自专栏程序员的SOD蜜

同样的SQL语句在查询分析器执行很快,但是网站上执行超时的诡异问题

    同样的SQL语句在查询分析器执行很快,但是网站上执行超时,这个问题以前遇到过,解决办法是重新启动服务器,但过一段时间后(时间长短不一定,一般为一天后),...

34670
来自专栏tkokof 的技术,小趣及杂念

Singleton模式小探

设计模式,这个多年前因为《设计模式》这本书而开始名声鹊起的软件名词,相信在当今年代早已为相关人士耳熟能详,你可以像大多数人那样对他顶礼膜拜,你也可以如极少数人...

11830
来自专栏Spark学习技巧

Table API&SQL的基本概念及使用介绍

Table API和SQL集成在共同API中。这个API的中心概念是一个用作查询的输入和输出的表。本文档显示了具有表API和SQL查询的程序的常见结构,如何注册...

2.7K60
来自专栏AhDung

【SQL】分享表值函数FMakeRows,用于生成行

评论中又有一位【笑东风】兄给出改善建议,在此先感谢他。原理是借助行数较多的一个系统视图sys.all_columns与自身做cross join,以得到大量现成...

10130
来自专栏杨建荣的学习笔记

一个SQL语句引发的ORA-00600错误排查(一) (r9笔记第64天)

最近有一个同事问我一个问题,说他运行一个SQL语句抛出了ORA-00600的错误,想让我帮忙分析一下,这种问题听了确实有兴趣,了解了问题的大体情 况之后,发现这...

37240
来自专栏数据库新发现

关于shared pool的深入探讨(六)

http://www.eygle.com/internal/shared_pool-6.htm

12220

扫码关注云+社区

领取腾讯云代金券