常见经典关系模式及查询之学生选课模式

关系模式描述：红色为主键

S (SNO,SNAME）          学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER)  课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE)     选课关系。SCGRADE 为成绩

建表语句：



            
create table S

            
(

            
  SNO   VARCHAR2(3),

            
  SNAME VARCHAR2(10)

            
);

            
insert into S (SNO, SNAME) values ('1', '赵一');

            
insert into S (SNO, SNAME) values ('2', '钱二');

            
insert into S (SNO, SNAME) values ('3', '孙三');

            
insert into S (SNO, SNAME) values ('4', '李四');

            
insert into S (SNO, SNAME) values ('5', '王五');

            
insert into S (SNO, SNAME) values ('6', '张六');

            
        

            
create table C

            
(

            
  CNO      VARCHAR2(3),

            
  CNAME    VARCHAR2(30),

            
  CTEACHER VARCHAR2(10)

            
);

            
insert into C (CNO, CNAME, CTEACHER) values ('1', '语文', '语文老师');

            
insert into C (CNO, CNAME, CTEACHER) values ('2', '数学', '数学老师');

            
insert into C (CNO, CNAME, CTEACHER) values ('3', '英语', '英语老师');

            
insert into C (CNO, CNAME, CTEACHER) values ('4', '历史', '历史老师');

            
insert into C (CNO, CNAME, CTEACHER) values ('5', '地理', '地理老师');

            
insert into C (CNO, CNAME, CTEACHER) values ('6', '生物', '生物老师');

            
        

            
create table SC

            
(

            
  SNO     VARCHAR2(3),

            
  CNO     VARCHAR2(3),

            
  SCGRADE NUMBER

            
);

            
insert into SC (SNO, CNO, SCGRADE) values ('1', '1', 11);

            
insert into SC (SNO, CNO, SCGRADE) values ('1', '2', 12);

            
insert into SC (SNO, CNO, SCGRADE) values ('1', '3', 13);

            
insert into SC (SNO, CNO, SCGRADE) values ('1', '4', 14);

            
insert into SC (SNO, CNO, SCGRADE) values ('1', '5', 15);

            
insert into SC (SNO, CNO, SCGRADE) values ('1', '6', 16);

            
insert into SC (SNO, CNO, SCGRADE) values ('2', '1', 21);

            
insert into SC (SNO, CNO, SCGRADE) values ('2', '2', 22);

            
insert into SC (SNO, CNO, SCGRADE) values ('2', '3', 23);

            
insert into SC (SNO, CNO, SCGRADE) values ('3', '4', 34);

            
insert into SC (SNO, CNO, SCGRADE) values ('3', '5', 35);

            
insert into SC (SNO, CNO, SCGRADE) values ('3', '6', 36);

            
insert into SC (SNO, CNO, SCGRADE) values ('4', '1', 41);

            
insert into SC (SNO, CNO, SCGRADE) values ('4', '2', 42);

            
insert into SC (SNO, CNO, SCGRADE) values ('5', '3', 53);

            
insert into SC (SNO, CNO, SCGRADE) values ('5', '4', 54);

            
        
 
1.    找出没有选修过“语文老师”老师讲授课程的所有学生姓名



            
正确写法一：
SELECT S.SNAME
FROM S
WHERE NOT EXISTS
      (SELECT * FROM SC,C
       WHERE SC.CNO=C.CNO
       AND SC.SNO=S.SNO
       AND C.CTEACHER='语文老师')
            
        

            
正确写法二：
SELECT S.SNAME
FROM S
WHERE SNO NOT IN
      (SELECT SNO 
       FROM SC,C
       WHERE SC.CNO=C.CNO
       AND C.CTEACHER='语文老师')

            
不推荐，如果子查询中有null值，结果将是错误的。

            
        

            
错误写法一：
SELECT  S.SNO,S.SNAME
FROM S,C,SC
WHERE SC.CNO=C.CNO
AND SC.SNO=S.SNO
AND C.CTEACHER<>'语文老师'
ORDER BY S.SNO

            
错误分析：这条语句是错误的，错在将sc表与c表做连接，这样做的结果就是会将sc表的所有记录都取出，即使这个学生选了所有的课程，仍然会被选出，因为数学老师也不是语文老师

            
        

            
错误写法二：
SELECT S.SNAME
FROM S
WHERE SNO IN
      (SELECT SNO FROM SC,C
       WHERE SC.CNO=C.CNO
       AND C.CTEACHER<>'语文老师')

            
错误分析同上

            
        
2.    列出有二门以上（含两门）课程低于40分的学生姓名及其平均成绩



            
正确写法一：
SELECT S.SNAME,A.SCORE
FROM 
(SELECT SNO,AVG(SC.SCGRADE) AS SCORE FROM SC
 WHERE SCGRADE<40
 GROUP BY SNO
 HAVING COUNT(*)>=2)A,S
WHERE A.SNO=S.SNO

            
使用子查询

            
        

            
正确写法二：
SELECT S.SNAME,AVG(SC.SCGRADE) FROM SC, S 
WHERE SC.SCGRADE<40 
AND S.SNO = SC.SNO
GROUP BY S.SNAME 
HAVING COUNT(*)>=2

            
不使用子查询的写法

            
        

            
正确写法三：
SELECT S.SNO,S.SNAME,AVG(SC.SCGRADE)
FROM S,SC,(
    SELECT SNO
    FROM SC
    WHERE SCGRADE<40
    GROUP BY SNO
    HAVING COUNT(DISTINCT CNO)>=2
)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME

            
个人不是很推荐这种写法，觉得效率低了点，但是网上很多答案都是这个，关于效率下次讨论

            
        
3.    列出既学过语文课，又学过数学课的所有学生姓名



            
正确写法一：
SELECT SNAME
FROM 
(SELECT SC.SNO
 FROM C,SC
 WHERE SC.CNO=C.CNO 
 AND C.CNAME IN('语文’,’数学’)
 GROUP BY SC.SNO
 HAVING COUNT(*)=2)A,S
WHERE A.SNO=S.SNO

            
使用子查询

            
        

            
正确写法二：
SELECT S.SNAME
 FROM C,SC, S
 WHERE SC.CNO=C.CNO AND SC.SNO = S.SNO
 AND C.CNAME IN('语文’,’数学')
 GROUP BY S.SNAME
 HAVING COUNT( * ) =2

            
不使用子查询

            
        
4.    列出1号课比2号课成绩低的所有学生的姓名及1号课和2号课的成绩



            
正确写法一：
SELECT SNAME,T.SCORE1,T.SCORE2
FROM 
(SELECT A.SNO,A.SCGRADE AS SCORE1,B.SCGRADE AS SCORE2
 FROM SC A,SC B
 WHERE A.SNO=B.SNO
 AND A.CNO='1'
 AND B.CNO='2'
 AND A.SCGRADE<B.SCGRADE)T,S
WHERE T.SNO=S.SNO

            
使用子查询

            
        

            
正确写法二：
SELECT S.SNAME,A.SCGRADE,B.SCGRADE
FROM S,SC A,SC B
WHERE S.SNO=A.SNO
AND S.SNO=B.SNO
AND A.CNO='1'
AND B.CNO='2'
AND A.SCGRADE<B.SCGRADE

            
不使用子查询

            
        
5.    列出选修了所有课程的学生的姓名



            
正确写法一：
SELECT S.SNAME
FROM SC,S
WHERE S.SNO=SC.SNO
GROUP BY S.SNAME
HAVING COUNT(*)=(SELECT COUNT(*) FROM C)

            
        
 

1.表关系分类:
总体可以分为三类: 一对一 、一对多(多对一) 、多对多
2.如何区分表与表之间是什么关系?
#分析步骤:
#多对一/一对多
#1.站在左表的角度去看右表(情况一)
如果左表中的一条记录,对应右表中多条记录.那么他们的关系则为 一对多 关系.约束关系为:左表普通字段, 对应右表foreignkey字段.
注意:如果左表与右表的情况反之.则关系为 多对一 关系.约束关系为:左表foreignkey字段, 对应右表普通字段.
#一对一
#2.站在左表的角度去看右表(情况二)
如果左表中的一条记录 对应 右表中的一条记录. 则关系为 一对一关系.
约束关系为:左表foreign key字段上 添加唯一(unique)约束, 对应右表 关联字段.
或者:右表foreign key字段上 添加唯一(unique)约束, 对应右表 关联字段.
#多对多
#3.站在左表和右表同时去看(情况三)
如果左表中的一条记录 对应 右表中的多条记录,并且右表中的一条记录同时也对应左表的多条记录. 那么这种关系 则 多对多 关系.
这种关系需要定义一个这两张表的[关系表]来专门存放二者的关系
3.建立表关系
1.一对多关系
例如:一个人可以拥有多辆汽车，要求查询某个人拥有的所有车辆。
分析：人和车辆分别单独建表，那么如何将两个表关联呢？有个巧妙的方法，在车辆的表中加个外键字段(人的编号)即可。
* (思路小结：’建两个表，一’方不动，’多’方添加一个外键字段)*
//建立人员表CREATE TABLEpeople(
idVARCHAR(12) PRIMARY KEY,
snameVARCHAR(12),
ageINT,
sexCHAR(1)
);INSERT INTO people VALUES('H001','小王',27,'1');INSERT INTO people VALUES('H002','小明',24,'1');INSERT INTO people VALUES('H003','张慧',28,'0');INSERT INTO people VALUES('H004','李小燕',35,'0');INSERT INTO people VALUES('H005','王大拿',29,'1');INSERT INTO people VALUES('H006','周强',36,'1');//建立车辆信息表CREATE TABLEcar(
idVARCHAR(12) PRIMARY KEY,
markVARCHAR(24),
price NUMERIC(6,2),
pidVARCHAR(12),CONSTRAINT fk_people FOREIGN KEY(pid) REFERENCESpeople(id)
);INSERT INTO car VALUES('C001','BMW',65.99,'H001');INSERT INTO car VALUES('C002','BenZ',75.99,'H002');INSERT INTO car VALUES('C003','Skoda',23.99,'H001');INSERT INTO car VALUES('C004','Peugeot',20.99,'H003');INSERT INTO car VALUES('C005','Porsche',295.99,'H004');INSERT INTO car VALUES('C006','Honda',24.99,'H005');INSERT INTO car VALUES('C007','Toyota',27.99,'H006');INSERT INTO car VALUES('C008','Kia',18.99,'H002');INSERT INTO car VALUES('C009','Bentley',309.99,'H005');
创建表
例子1:学生和班级之间的关系
班级表
id   class_name1python脱产100期2python脱产300期
学生表foreign keyid     name    class_id1       alex     2
2       刘强东    2
3       马云      1例子2: 一个女孩 拥有多个男朋友...
例子3:....
其他示例
2.一对一关系
例如：一个中国公民只能有一个身份证信息
分析: 一对一的表关系实际上是 变异了的 一对多关系. 通过在从表的外键字段上添加唯一约束(unique)来实现一对一表关系.
#身份证信息表CREATE TABLEcard (
idint NOT NULL AUTO_INCREMENT PRIMARY KEY,
codevarchar(18) DEFAULT NULL,UNIQUE un_code (CODE) --创建唯一索引的目的,保证身份证号码同样不能出现重复
);INSERT INTO card VALUES(null,'210123123890890678'),
(null,'210123456789012345'),
(null,'210098765432112312');
#公民表CREATE TABLEpeople (
idint NOT NULL AUTO_INCREMENT PRIMARY KEY,
namevarchar(50) DEFAULT NULL,
sexchar(1) DEFAULT '0',
c_idint UNIQUE, --外键添加唯一约束,确保一对一
CONSTRAINT fk_card_id FOREIGN KEY (c_id) REFERENCEScard(id)
);INSERT INTO people VALUES(null,'zhangsan','1',1),
(null,'lisi','0',2),
(null,'wangwu','1',3);
创建表
例子一：一个用户只有一个博客
用户表：
主键
id   name1egon2alex3wupeiqi
博客表
fk+unique
id url user_id1  xxxx   1
2  yyyy   3
3  zzz    2例子2: 一个男人的户口本上，一辈子最多只能一个女主的名字.等等
其他示例
3.多对多关系
例如：学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。
分析：这种方式可以按照类似一对多方式建表，但冗余信息太多，好的方式是实体和关系分离并单独建表，实体表为学生表和课程表，关系表为选修表，
其中关系表采用联合主键的方式(由学生表主键和课程表主键组成)建表。
#//建立学生表CREATE TABLEstudent(
idVARCHAR(10) PRIMARY KEY,
snameVARCHAR(12),
ageINT,
sexCHAR(1)
);INSERT INTO student VALUES('S0001','王军',20,1);INSERT INTO student VALUES('S0002','张宇',21,1);INSERT INTO student VALUES('S0003','刘飞',22,1);INSERT INTO student VALUES('S0004','赵燕',18,0);INSERT INTO student VALUES('S0005','曾婷',19,0);INSERT INTO student VALUES('S0006','周慧',21,0);INSERT INTO student VALUES('S0007','小红',23,0);INSERT INTO student VALUES('S0008','杨晓',18,0);INSERT INTO student VALUES('S0009','李杰',20,1);INSERT INTO student VALUES('S0010','张良',22,1);
#//建立课程表CREATE TABLEcourse(
idVARCHAR(10) PRIMARY KEY,
snameVARCHAR(12),
creditDOUBLE(2,1),
teacherVARCHAR(12)
);INSERT INTO course VALUES('C001','Java',3.5,'李老师');INSERT INTO course VALUES('C002','高等数学',5.0,'赵老师');INSERT INTO course VALUES('C003','JavaScript',3.5,'王老师');INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师');INSERT INTO course VALUES('C005','数据库',3.5,'廖老师');INSERT INTO course VALUES('C006','操作系统',3.5,'张老师');
#//建立选修表CREATE TABLEsc(
sidVARCHAR(10),
cidVARCHAR(10),PRIMARY KEY(sid,cid),CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCESstudent(id),CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCEScourse(id)
);INSERT INTO sc VALUES('S0001','C001');INSERT INTO sc VALUES('S0001','C002');INSERT INTO sc VALUES('S0001','C003');INSERT INTO sc VALUES('S0002','C001');INSERT INTO sc VALUES('S0002','C004');INSERT INTO sc VALUES('S0003','C002');INSERT INTO sc VALUES('S0003','C005');INSERT INTO sc VALUES('S0004','C003');INSERT INTO sc VALUES('S0005','C001');INSERT INTO sc VALUES('S0006','C004');INSERT INTO sc VALUES('S0007','C002');INSERT INTO sc VALUES('S0008','C003');INSERT INTO sc VALUES('S0009','C001');INSERT INTO sc VALUES('S0009','C005');
创建表
例子1:中华相亲网: 男嘉宾表+相亲关系表+女嘉宾表
男嘉宾:1孟飞2乐嘉
女嘉宾:1小乐2小嘉
相亲表:(中间表)
男嘉宾  女嘉宾  相亲时间1          1            2017-10-12 12:12:12
1          2           2017-10-13 12:12:12
1          1           2017-10-15 12:12:12例子2: 用户表,菜单表,用户权限表...
其它示例

ER图转换关系模式
(2013-03-21 14:09:07)
标签：
it
转换一般遵循如下原则：
1．一个实体型转换为一个关系模式。实体的属性就是关系的属性。实
体的码就是关系的码。
例如在我们的例子中，学生实体可以转换为如下关系模式，其中学号
为学生关系的码：
学生(学号，姓名，出生日期，所在系，年级，平均成绩) 同样，性
别、宿舍、班级、档案材料、教师、课程、教室、教科书都分别转换
为一个关系模式。
2.一个联系转化为一个关系模式，与该联系相连的各实体的码以及联系
的属性转化为关系的属性，该关系的码则有三种情况：
若联系为1：1，则每个实体的码均是该关系的后选码。
若联系为1：n，则关系的码为n端实体的码。
若联系为m：n，则关系的码为诸实体码的组合。
2.1 联系为1：1
一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对
应的关系模式合并。
如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及
联系本身的属性均转换为关系的属性，l 每个实体的码均是
该关系的候选码。
如果与某一端对应的关系模式合并，则需要在该关系模式的属性中加
入另一个关系模式的码和联系本身的属性。
例如在我们的例子中，"管理"联系为1:1联系，我们可以将其转换为
一个独立的关系模式：
管理(职工号，班级号)或 管理(职工号，班级号)
管理"联系也可以与班级或教师关系模式合并。如果与班级关系模式合
并，则只需在班级关系中加入教师关系的码，即职工号：
班级：｛班级号，学生人数，职工号｝
同样，如果与教师关系模式合并，则只需在教师关系中加入班级关系
的码，即班级号：
教师：｛职工号，姓名，性别，职称，班级号，是否为优秀班主任｝
2.2 联系为1：n
一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的
关系模式合并。
如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及
联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。
如果与n端对应的关系模式合并，则在n端实体对应模式中加入1端
实体所对应关系模式的码，以及联系本身的属性。而关系的码为n端
实体的码。
例如在我们的例子中，"组成"联系为1:n联系，将其转换为关系模式。
一种方法是使其成为一个独立的关系模式：
组成(学号，班级号)
其中学号为"组成"关系的码。
另一种方法是将其学生关系模式合并，这时学生关系模式为：
学生(学号，姓名，出生日期，所在系，年级，班级号，平均成绩)
后一种方法可以减少系统中的关系个数，一般情况下更倾向于采用这
种方法。
2.3 联系为m：n
一个m:n联系转换为一个关系模式。与该联系相连的各实体的码以及
联系本身的属性均转换为关系的属性。而关系的码为各实体码的组合。
例如在我们的例子中，"选修"联系是一个m:n联系，可以将它转换为如
下关系模式，其中学号与课程号为关系的组合码：
选修(学号，课程号，成绩)
三个或三个以上实体间的一个多元联系转换为一个关系模式。与该多
元联系相连的各实体的码以及联系本身的属性均转换为关系的属性。
例如在我们的例子中，"选修"联系是一个m:n联系，可以将它转换为如
下关系模式，其中学号与课程号为关系的组合码：
选修(学号，课程号，成绩)
三个或三个以上实体间的一个多元联系转换为一个关系模式。与该多
元联系相连的各实体的码以及联系本身的属性均转换为关系的属性。
而关系的码为各实体码的组合。
例如在我们的例子中，"讲授"联系是一个三元联系，可以将它转换为
如下
关系模式，其中课程号、教师号和书号为关系的组合码： 讲授(课程号，教师号，书号)
3．同一实体集的实体间的联系，即自联系，也可按上述1:1、1:n和
m:n三种情况分别处理。
例如，如果教师实体集内部存在领导与被领导的1:n自联系，我们可
以将该联系与教师实体合并，这时主码职工号将多次出现，但作用不
同，可用不同的属性名加以区分，比如在合并后的关系模式中，主码
仍为职工号，再增设一个"系主任"属性，存放相应系主任的职工号。
4．具有相同码的关系模式可合并。
为了减少系统中的关系个数，如果两个关系模式具有相同的主码，可
以考虑将他们合并为一个关系模式。合并方法是将其中一个关系模式
的全部属性加入到另一个关系模式中，然后去掉其中的同义属性(可
能同名也可能不同名)，并适当调整属性的次序。
例如我们有一个"拥有"关系模式：拥有(学号，性别)
有一个学生关系模式： 学生(学号，姓名，出生日期，所在系，年级，
班级号，平均成绩)
这两个关系模式都以学号为码，我们可以将它们合并为一个关系模式，
假设合并后的关系模式仍叫学生：
学生(学号，姓名，性别，出生日期，所在系，年级，班级号，平均
成绩)
按照上述4条原则，学生管理子系统中的18个实体和联系可以转换为
下列关系模型：
学生(学号，姓名，性别，出生日期，所在系，年级，班级号，平均
成绩，档案号)
性别(性别，宿舍楼)
宿舍(宿舍编号，地址，性别，人数)
班级(班级号，学生人数)
教师(职工号，姓名，性别，职称，班级号，是否为优秀班主任)
教学(职工号，学号)
课程(课程号，课程名，学分，教室号)
选修(学号，课程号，成绩)
教科书(书号，书名，价钱)
教室(教室编号，地址，容量)
讲授(课程号，教师号，书号)
档案材料(档案号，......)
该关系模型由12个关系模式组成。其中学生关系模式包含了"拥有"联
系、"组成"联系、"归档"联系所对应的关系模式；教师关系模式包含
了"管理"联系所对应的关系模式；宿舍关系模式包含了"住宿"联系所
对应的关系模式；课程关系模式包含了"开设"联系所对应的关系模式。
分享：
喜欢
0
赠金笔
加载中，请稍候......
评论加载中，请稍候...
发评论
登录名：   密码：   找回密码   注册记住登录状态
昵   称：
评论并转载此博文
发评论
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。

为了更好的描述这三个概念，必须对关系模型的一些概念进行解释。
表Student



在关系模型的术语中，关系用来指代表，而元组用来指代行。类似地，属性指代的是表中的列。

在表Student中有五个属性：Sno、Sname、Ssex、Sage和Sdept。

我们用关系实例这个术语来表示一个关系的特定实例，也就是所包含的一组特定的行。表Student的实例中有5个元组，对应5个学生。
上边已经给出关系的概念，下面我们来讨论关系模式。
在Abraham Silberschatz等人所著的《数据库系统概念》一书中是这样解释：

当我们谈论数据库时，我们必须区分数据库模式和数据库实例，前者是数据库的逻辑设计，后者是给定时刻数据库中数据的一个快照。

关系的概念对应于程序设计语言中变量的概念，而关系模式的概念对应于程序设计中类型的概念。

在王珊教授所著的《数据库系统概念》中做出了这样的解释：

关系的描述称为关系模式。

关系可被认为是关系模式在某一时刻的状态或内容

因此可以知道关系模式是比关系更广的一个概念，描述了整个数据库的逻辑设计，关系是关系模式在某一时刻的快照。关系是动态的、随时间不断变化的；关系模式是静态的、稳定的。
关系数据库
关系数据库在王珊教授所著的《数据库系统概念》这样定义：

在关系模型中，实体以及实体间的联系都是用关系来描述。例如导师实体、研究生实体、导师和研究生之间的一对多联系都可以用一个关系来表示。在一个给定的应用领域中，所有关系的集合构成一个关系数据库

在Abraham Silberschatz等人所著的《数据库系统概念》一书中是这样解释：

关系数据库由表的集合构成，每个表都有唯一的名字。



上图便是一个关系数据库，其中Course和Student是数据库student所包含的两个表（即关系）。