这一天的内容,就整理一下余下的SQL部分的笔记。

创建从表(也称子表)

首先说明,为什么需要从表,从表与主表有何关系。

想象一下,生活中常常会遇到一对多的情况,比如一个学校有多个学院,一个学院有多个教师,一位教师有多位学生,一门学生要修多门科目,如此这般。在现有的数据库系统中,一个表的各条记录只能表示一条数据,由于主键不可重复,因此想要只用一个表就解决一对多的情况,是不现实的。主表与从表就是从解决一对多问题的角度出发而设计的。从表中有一列或多列,这(些)列使用了一种特殊的约束,叫做“外键约束”,即这一列的字段必定与另一表(主表)的某一列的字段(要求这一字段为候选键(也称候选码),通常是主键)相同,即形成一种一对多的映射关系。这样,通过外键就可以知道子表中的哪一行数据与主表中的哪一行相对应,这样就可以形成一对多关系。外键约束与主键约束不能并存,即一列不能是主键的同时也是外键,否则无法形成一对多的关系,外键约束的意义也就不存在了。

下面,我将以一个例子说明主表和从表的关系。

首先,建立一个主表:

create table stuInfo(
sid int(4) primary key auto_increment,
sname varchar(8) not null
);

然后,创建一个从表:

create table marks(
mid int(4) primary key,
mysql float not null,
mjava float not null,
mhtml float not null,
mweb float not null,
sid int(4) not null references stuInfo(sid) # sid即为外键
);

为主表和从表添加相关数据:

#添加主表数据
insert into stuInfo(sname)  values('小张'),('小李'),('小王');

#添加从表数据
insert into marks(mid,mysql,mjava,mhtml,mweb,sid)
values(10001,80.5,90,60,70,1);
insert into marks(mid,mysql,mjava,mhtml,mweb,sid)
values(10002,81.5,92,66,80,1);
insert into marks(mid,mysql,mjava,mhtml,mweb,sid)
values(10003,70.5,85,90,60,2);

这样,以上两表就形成了一对多的关系:
stuInfo表:主表(一)
marks表:从表/子表(多)

对于存在外键约束的表的查询,通常采用联合查询,即在查询的过程中将从表与主表的数据关联起来,给出“一对多”的查询结果。


联合查询有三种方法

1. 内连接

内连接显示主表和从表中均包含数据的相关关系,不会显示主表或从表中任一不包含数据的行,因此内连接的查询结果中不会出现主表或从表中的某一行数据均为null的情况。

引用网上的一种较为严格的描述:“内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如,检索 students和courses表中学生标识号相同的所有行。”

基本语法:
select <列名> from <主表表名> <主表别名>, <从表> <从表别名> where <主表表名/主表别名>.<主表中公共列列名> = <从表表名/从表别名>.<从表中公共列列名>;

其中,主表和从表的别名可不写。

表示某表中的某列,可以使用“<表名>.<列名>”格式。
如:
select s.sid,sname,mjava,mhtml,mysql from stuInfo s,marks m where s.sid=m.sid;

对各种数据库均通用的内连接写法,称为“标准内连接写法”,示例如下:
select s.sid,sname,mjava,mhtml,mysql from stuInfo s inner join marks m on s.sid=m.sid;
上述语句中的“inner join”即为内连接的关键字。

2. 外连接

外连接分为三种类型,左外连接,右外连接和全外连接,其显示主表或从表中的全部行,另一表如有与之对应的空行则用null补全。

(1) 左外连接:

主表在前,从表在后,查询结果为主表的全部行,从表中无对应内容的以null代替。

依然引用网上的一段较为严格的描述:“左外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。”

写法示例:
select s.sid,sname,mjava,mhtml,mysql from stuInfo s left outer join marks m on s.sid=m.sid;

(2) 右外连接:

依然是主表在前,从表在后,查询结果为从表的全部行,主表中无对应内容的以null代替。

还是网上的较为严格的描述:“右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。”

写法如下:
select s.sid,sname,mjava,mhtml,mysql from stuInfo s right outer join marks m on s.sid=m.sid;

(3) 全外连接:

依然是主表在前,从表在后,查询结果为主表和从表的所有行,另一表中无对应内容的以null代替。

继续给出网上的严格描述:“完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。”

写法如下:
select s.sid,sname,mjava,mhtml,mysql from stuInfo s full outer join marks m on s.sid=m.sid;

上述三局与外连接有关的SQL语句中,outer关键词均可省略不写。

3. 交叉连接:

查询结果为主表和从表的笛卡尔积,在通常使用中意义不大。(笛卡尔积?百度去!)

写法如下:
select s.sid,sname,mjava,mhtml,mysql from stuInfo s,marks m;

最后再补充一种多表查询的方式:子查询

即where部分的条件也是查询语句,也叫嵌套查询。SQL语句的条件支持多层嵌套。

举个例子就浅显易懂了:
select * from marks where sid=(select sid from stuInfo where sname='小张');

该语句首先查询“小张的学号”,然后将“小张的学号”作为条件,查询marks表中符合这一sid的数据。实际上,这属于一种多表查询的方法,因此我将其补充于这一位置而没有和上一篇的单表查询放在一块。

需要注意的是,子查询的SELECT语句中不能使用 ORDER BY 子句,因为 ORDER BY 子句只能对最终查询结果排序。

与MySQL和SQL语句相关的内容就这么多。关于 Oracle DB 和 MS SQL Server 的部分就不写了,毕竟对我而言,使用率着实不高……

喜大普奔!本部分完成!