-
原文:http://www.2cto.com/database/201307/232324.html1mysql的外键相关知识mysql的外键有三种,如果表A的主关键字是表B中的字段,则该字段称为表B的外键,表A称为主表,表B称为从表。外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。常见的外键 有级联(cascade)方式,置空(set null)方式及禁止(no action / restrict)方式a测试级联方式:123456789101112131415161718192021222324252627282930313233343536373839404142
01
create
table
test111 (
02
id
int
not
null
,
03
name
varchar
(30),
04
primary
key
(id)
05
);
06
create
table
test222 (
07
id
int
not
null
,
08
name
varchar
(30),
09
groupid
int
,
10
primary
key
(id),
11
foreign
key
(groupid)
references
test111(id)
on
delete
cascade
on
update
cascade
12
);
13
14
insert
into
test111
values
(1,
'Group1'
);
15
insert
into
test111
values
(2,
'Group2'
);
16
17
insert
into
test222
values
(1,
'qianxin'
, 1);
18
insert
into
test222
values
(2,
'yiyu'
, 2);
19
insert
into
test222
values
(3,
'dai'
, 2);
20
delete
from
test111
where
id=2;
21
update
test111
set
id=2
where
id=1;
这种情况下无法插入,用户组3不存在,与参照完整性约束不符删除时test222r中的2、3记录级联删除更新时test222的1记录的groupid级联修改为2b测试置空(set null)方式12345678910111213141516171819202122232425262728293031323334353637383940414201
create
table
test111 (
02
id
int
not
null
,
03
name
varchar
(30),
04
primary
key
(id)
05
);
06
create
table
test333 (
07
id
int
not
null
,
08
name
varchar
(30),
09
groupid
int
,
10
primary
key
(id),
11
foreign
key
(groupid)
references
test111(id)
on
delete
set
null
on
update
set
null
12
);
13
14
insert
into
test111
values
(1,
'Group1'
);
15
insert
into
test111
values
(2,
'Group2'
);
16
17
insert
into
test333
values
(1,
'qianxin'
, 1);
18
insert
into
test333
values
(2,
'yiyu'
, 2);
19
insert
into
test333
values
(3,
'dai'
, 3);
20
delete
from
test111
where
id=2;
21
update
test111
set
id=2
where
id=1;
无法插入,用户组3不存在,与参照完整性约束不符删除是导致test333中的2、3记录的groupid被设置为NULL、更新时导致t_user中的1记录的groupid被设置为NULLc测试禁止(no action / restrict)方式12345678910111213141516171819202122232425262728293031323334353637383940414201
create
table
test111 (
02
id
int
not
null
,
03
name
varchar
(30),
04
primary
key
(id)
05
);
06
create
table
test444 (
07
id
int
not
null
,
08
name
varchar
(30),
09
groupid
int
,
10
primary
key
(id),
11
foreign
key
(groupid)
references
test111(id)
on
delete
no
action
on
update
no
action
12
);
13
14
insert
into
test111
values
(1,
'Group1'
);
15
insert
into
test111
values
(2,
'Group2'
);
16
17
insert
into
test444
values
(1,
'qianxin'
, 1);
18
insert
into
test444
values
(2,
'yiyu'
, 2);
19
insert
into
test444
values
(3,
'dai'
, 3);
20
delete
from
test111
where
id=2;
21
update
test111
set
id=2
where
id=1;
插入时无法插入,用户组3不存在,与参照完整性约束不符删除时从表中有相关引用,因此主表中无法删除更新时从表中有相关引用,因此主表中无法修改在MySQL中,restrict方式与no action方式作用相同。2 oracle的外键外键是该表是另一个表之间联接的字段 ,必须为另一个表中的主键 用途是确保数据的完整性。它通常包括以下几种:A实体完整性,确保每个实体是唯一的(通过主键来实施)B域完整性,确保属性值只从一套特定可选的集合里选择C关联完整性,确保每个外键或是NULL(如果允许的话)或含有与相关主键值相配的值下面是一个测试例子,如何建立两个表的外键1234567891011121314151617181920212201
CREATE
TABLE
ZZ_STUDENT (
02
ID
CHAR
(10),
03
NAME
VARCHAR
(8),
04
SEX
CHAR
(1)
05
);
06
07
ALTER
TABLE
ZZ_STUDENT
ADD
CONSTRAINT
PK_STUDENT
PRIMARY
KEY
(ID);
08
09
CREATE
TABLE
ZZ_SCORE (ID
CHAR
(10), MATH NUMBER(5, 2));
10
11
ALTER
TABLE
ZZ_SCORE
ADD
CONSTRAINT
FK_SCROE
FOREIGN
KEY
(ID)
REFERENCES
ZZ_STUDENT (ID);
3 数据库内连接外连接左连接右连接内连接:把两个表中数据对应的数据查出来外连接:以某个表为基础把对应数据查出来全连接是以多个表为基础首先插入如下测试数据:123456789101112131415161
insert
into
student(sno,sname)
values
(
'2005001'
,
'小施'
);
2
insert
into
student(sno,sname)
values
(
'2005002'
,
'小王'
);
3
insert
into
student(sno,sname)
values
(
'2005003'
,
'小张'
);
4
insert
into
student(sno,sname)
values
(
'2005004'
,
'小贾'
);
5
insert
into
stu_score(sno,scrore)
values
(
'2005001'
,90.00);
6
insert
into
stu_score(sno,scrore)
values
(
'2005002'
,95.00);
7
insert
into
stu_score(sno,scrore)
values
(
'2005008'
,80.50);
8
insert
into
stu_score(sno,scrore)
values
(
'2005009'
,88.50);
(一) 内连接内连接,inner join,join 查询操作列出与连接条件匹配的数据行,它使用比较运算符比较被连接列的列值。1234567891011----假设a表有M条记录,b表有N条记录,a和b表sno相同的记录有K条
----内连接(数据量=K)
1
select
a.*,b.*
from
student a
join
stu_score b
on
a.sno=b.sno;
2
select
a.*,b.*
from
student a
inner
join
stu_score b
on
a.sno=b.sno;
查询结果如下:
2005001 小施 2005001 90
2005002 小王 2005002 95
(二) 外连接 (左连接、右连接)12345678910111213141516171819202122232425左连接或
left
join
,
left
outer
join
返回包括左表中的所有记录和右表中联接字段相等的记录
右连接或
right
join
,
right
outer
join
返回包括右表中的所有记录和由表中联接字段相等的记录
----左连接/左外连接(数据量=M)
select
a.*,b.*
from
student a
left
join
stu_score b
on
a.sno=b.sno;
select
a.*,b.*
from
student a
left
outer
join
stu_score b
on
a.sno=b.sno;
查询结果如下:
2005001 小施 2005001 90
2005002 小王 2005002 95
2005003 小张
2005004 小贾
----右连接/右外连接(数据量=N)
select
a.*,b.*
from
student a
right
join
stu_score b
on
a.sno=b.sno;
select
a.*,b.*
from
student a
right
outer
join
stu_score b
on
a.sno=b.sno;
查询结果如下:
2005001 小施 2005001 90
2005002 小王 2005002 95
2005008 80.5
2005009 88.5
(三) 完全连接()定义:在内连接的基础上,还包含两个表中所有不符合条件的数据行,并在其中的左表、和右表列填写NULL关键字:FULL JOIN12----完全连接(数据量=M+N-K)
select
a.*,b.*
from
student a
full
join
stu_score b
on
a.sno=b.sno;
查询的时候 mysql是不支持的(四) 交叉连接定义:将两个表的所有行进行组合,连接后的行数为两个表的乘积数。(笛卡尔积)关键词:CROSS JOIN格式:FROM 表名1 CROSS JOIN 表名2----交叉连接(数据量=M*N)1select
a.*,b.*
from
student a
cross
join
stu_score b ;
查询结果如下:123456789101112131415162005001 小施 2005001 90
2005002 小王 2005001 90
2005003 小张 2005001 90
2005004 小贾 2005001 90
2005001 小施 2005002 95
2005002 小王 2005002 95
2005003 小张 2005002 95
2005004 小贾 2005002 95
2005001 小施 2005008 80.5
2005002 小王 2005008 80.5
2005003 小张 2005008 80.5
2005004 小贾 2005008 80.5
2005001 小施 2005009 88.5
2005002 小王 2005009 88.5
2005003 小张 2005009 88.5
2005004 小贾 2005009 88.5
转载于:https://www.cnblogs.com/leesymbol/p/3283106.html