SQL数据库的、外键和查询

创建表的时候增加外键：在所有的表字段之后，使用foreign key(外键字段) references 外部表(主键字段)

在新增表之后增加外键：修改表结构，使用alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段)

修改外键&删除外键

alter table 表名 drop foreign key 外键名

外键条件

外键要存在，首先必须保证表的存储引擎是innodb

列类型必须与父表的主键类型一致

一张表中的外键名字不能重复

增加外键的字段数据已经存在，必须保证数据与父表主键要求对应

外键约束

有三种约束模式

district：严格模式（默认的）

cascade：级联模式

set null：置空模式

语法：foreign key(外键字段) references 父表(主键字段) on delete 模式 on update 模式

联合查询

基本语法：

select 语句1

union [union 选项]

select 语句2……

union 选项

all：保留所有，不管重复

distinct：去重，默认的

子查询（sub query）

按位置分类

from子查询

where子查询

exists子查询

按结果分类

标量子查询

列子查询

行子查询

表子查询

子查询

列子查询

=any等价于in -- 其中一个即可

any等价于some-- 二者是一样的

=all为全部

-- 创建外键

create table my_foreign1(

idint primary key auto_increment,

name varchar (20)not null comment

'学生姓名',

c_idint comment'班级id',

-- 增加外键

foreign key(c_id)references

my_class(id)

)charset utf8

-- 创建表

create table my_foreign2(

idint primary key auto_increment,

name varchar (20)not null comment

'学生姓名',

c_idint comment'班级id'  -- 普通字段

)charset utf8

-- 增加外键

alter table my_foreign2add

-- 指定外键的名字

constraint student_class_1  -- 可以指定多个外键 但是名字不能相同

-- 指定外键的字段

foreign key(c_id)

-- 引用父表主键

references my_class(id)

-- 删除外键

alter table my_foreign1drop

foreign key my_foreign1_ibfk_1  -- my_foreign1_ibfk_1 通过外键的名字来删

-- 插入数据；外键字段在父表不存在

insert into my_foreign2values (

null,'郭富城',4)  -- 没有4号班级

insert  into my_foreign2values (

null,'项羽',1)

insert  into my_foreign2values (

null,'刘邦',2)

insert  into my_foreign2values (

null,'韩信',3)

-- 更新父表的记录

update my_classset id=4 where id=1  -- 失败；id=1记录已经被学生引用

update my_foreign2set c_id=2 where id=4  -- 更新

update my_classset id=4 where id=3  -- 可以；没有学生引用此班级

-- mysql中添加外键约束遇到一下情况：

-- cannot add foreign key constraint

-- 出现这个问题的原因是，外键的使用：

-- 1. 外键字段不能为该表的主键；

-- 2. 外键字段参考字段必须为参考表的主键

-- 插入数据

insert into my_foreign1values (

null,'马超','3'

)

-- 增加外键

alter table my_foreign1add

foreign key(c_id)references

my_class(id)  -- 失败；因为没有3号班了

-- 创建外键，指定模式；删除置空；更新级联

create table my_foreign3(

idint primary key auto_increment,

name varchar (20)not null,

c_idint,

-- 增加外键

foreign key (c_id)

-- 引用表

references my_class(id)

-- 指定删除模式

on delete set null

-- 指定更新模式

on update cascade

)charset utf8

-- 插入数据

insert into my_foreign3values (

null,'刘备',1),

(null,'曹 *** ',1),

(null,'孙权',1),

(null,'祝贺量',2),

(null,'周瑜',2)

-- 解除My_foreign2表的外键

alter table my_foreign2drop

foreign key student_class_1

-- 更新父表主键

update my_classset id=3 where id=1

-- 删除父表主键

delete from  my_classwhere id=2

-- 联合查询

select * from my_class

union  -- 默认去重

select * from my_class

select * from my_class

union all  -- 不去重

select * from my_class

select id,c_name,roomfrom my_class

union all  -- 不去重

select name,number,idfrom my_student

-- 需求；男生升序；女生降序（年龄）

(select * from my_student

where sex='男'

order by ageasc limit9999999)

union

(select * from my_student

where sex='女'

order by agedesc limit9999999)

select * from my_studentwhere

c_id=(

-- 标量子查询

select idfrom my_classwhere

c_name='python1903')-- id一定只有一个值（一行一列）

insert into my_classvalues (1,

'python1907','B407')

-- 列子查询

select * from my_studentwhere

c_idin(select idfrom my_class)

-- any,some,all

select * from my_studentwhere

c_id=any(select idfrom my_class)

select * from my_studentwhere

c_id=some(select idfrom my_class)

select * from my_studentwhere

c_id=all(select idfrom my_class)

select * from my_studentwhere

c_id!=any(select idfrom my_class)  -- 所有结果（null除外）

select * from my_studentwhere

c_id!=some(select idfrom my_class)  -- 所有结果（null除外）

select * from my_studentwhere

c_id!=all(select idfrom my_class)  -- 所有2号班级（null除外）

select * from my_studentwhere

age=(select max(age)from

my_student)

and

height=(select max(height))from

my_student)

-- 行子查询

select * from my_student

-- (age,height)称之内为行元素

where (age,height)=(select max(

age),max(height)from my_student)

update my_studentset height=188

where name='王五'

select * from my_studentorder by

agedesc,heightdesc limit1

select * from my_studentorder by

heightdesc

-- 表子查询

select * from my_studentgroup by

c_idorder by heightdesc  -- 每个班选出第一个学生再按身高排序

select * from (select * from

my_studentorder by heightdesc)

as studentgroup by student.c_id

外键(FK)是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。

当创建或更改表时可通过定义 FOREIGN KEY 约束来创建外键。

例如，数据库 pubs 中的 titles 表与 publishers 表有链接，因为在书名和出版商之间存在逻辑联系。

titles 表中的 pub_id 列与 publishers 表中的主键列相对应。titles 表中的 pub_id 列是到 publishers 表的外键。

扩展资料：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值或使用空值。

学号在成绩表（表2）中是主键，在学生表（表1）中是外键。如果不使用外键，表1的学号字段插了一个值（比如20140999999），但是这个值在表2中并没有，这个时候，数据库允许插入，并不会对插入的数据做关系检查。

然而在设置外键的情况下，插入表1学号字段的值必须要求在表1的学号字段能找到。 同时，如果要删除表2的某个学号字段，必须保证表2中没有引用该字段值的列，否则就没法删除。

这就是所谓的保持数据的一致性和完整性。如右图，如果表1还引用表2的某个学号，却把表1中的这个学号删了，表2就不知道这个学号对应的学生是哪个学生。

参考资料：百度百科-外键