mysql 定义外键并增删改查_20150102--Mysql外键+增删改查-02

外键

外键：外面的键，前表中的某个字段与另外的表中的字段(主键)有一个对应的关系

外键关键字：foreign key，也是一种索引。

创建外键

外键和主键有点类似，但是不一样。

1. 在所有的字段之后，指定对应的外键。

foreign key(字段名) references 表名(字段)

外键约束

外键的作用是用来维护两张表之间的关系，关系是用来限制表操作的。

父表(主表)：被外键指向的表叫做主表

子表(从表)：存在外键字段的表叫子表

1. 限制子表数据的插入：子表中的外键字段的值必须事先已经在父表中存在

2. 限制父表对数据进行操作：更新和删除，限制指的是对应主键的修改。

修改外键：外键不能被直接修改，应当先删除后新增

删除外键

alter table 表名 drop foreign key 外键名;

新增外键

修改表结构

alter table 表名 add constraint 外键名 foreign key(字段) references 表名(字段);

约束模式

用户可以根据自身的需求对外键采用不同的约束方式

有三种约束模式

严格模式：restrict，严格约束外键对应的表

置空模式：set null，将一个外键对应的字段数据给变成null，当父表数据改变的时候，子表的数据被设置成null

级联模式：cascade，级联操作，当父表数据被改变的时候，子表跟着改变

语法

foreign key(字段) references 表(字段) on delete 约束模式 on upldate 约束模式

通常的约束模式：on update cascade on delete set null

更新父表

删除父表数据

外键条件

1.外键表和被引用的表(父子表)都应该是InnoDB的存储引擎

2. 外键字段和被引用的主键字段，必须数据类型完全一致

3. 外键的名字必须具有唯一性

4. 外键可以在一张表中创建多个

5. 如果要使用约束模式：置空模式，必须保证子表对应的外键字段本身允许为空

高级数据操作

新增数据

标准语法

insert into 表名 (字段列表) values(值列表);

蠕虫复制

从一个已有的数据中获取数据，然后将数据插入到对应数据表中。

insert into 表名 (字段列表) select 字段列表 from 表名;

蠕虫复制的作用

1. 短时间内迅速增加表中的数据(意义不大)，测试数据表的压力

2. 从表中去复制数据

a) 复制表结构：create table 表名 like 已经存在的表;

b) 蠕虫复制数据

主键冲突

主键冲突指的是当数据进行插入操作的时候，如果主键冲突了该怎么办。

解决方案1：如果主键冲突，执行更新操作，没有冲突直接插入

insert into 表名 values (值列表) on duplicate key update 字段 = 值[,字段 = 值]

解决方案2：如果主键冲突，先删除，后新增

replace into 表名 values(值列表);

删除数据

标准语法

delete from 表名 where条件;

高级语法

delete from 表名 where条件 [order by排序] [limit 数值]; -- 排序后删除指定条数数据

truncate语法：清空表包括对应的自动增长：先删除表，后新增表

更新数据

与高级的删除数据一样：利用排序和限制

update 表名 set 字段 =值 where条件 order by字段 limit 数值;

查询数据

标准语法

select 字段列表/* from 表名 where条件;

完整语法

select 选择模式 表达式[字段列表(别名)/*] [from 子句] [where子句] [group by子句] [having子句] [order by子句] [limit子句]

选择模式

选择模式指的是当select查到数据之后，对数据的处理方式：查询全部(默认的all)，去重(去掉重复的：记录重复，distinct)

表达式

select指定的要返回数据形式

通常要查询的数据，都是字段列表或者*(全部字段)，有时候会因为同时查询多个表而出现字段重名的问题。需要使用字段别名来决绝问题。

语法：字段 [as] 别名

别名不能真正改变字段名。

from子句

from子句指 的是数据源

from后面必须接数据源，数据源不一定是一张表

1. from后面可以跟多张表，使用逗号分隔

笛卡尔积没有意义：应当尽量避免。

2. 表别名

3. 数据源不一定是一张已经存在的表，需要的只是一个二维表的结构。from后面可以跟一个子查询。

4. 虚拟表

一个不存在的表：dual，凡是没有数据源的select语句都可以为了保证语法的完整性，而增加from子句，数据源是dual

where子句

where子句用于筛选条件，对数据(记录)进行逐行判断，返回是一个类似布尔的结果。

where原理

只有where操作的时候，是直接在磁盘上对数据进行判断，where之后的所有子句都是对内存中的数据进行操作

补充知识

mysql中没有布尔类型，但是却“支持”布尔类型，是内部会进行数据转换，识别关键字

where判断：返回的结果是布尔类型

比较运算：=，>，>=，

逻辑运算：&&and和||or，用于多条件联合判断

模糊匹配：like ‘patter’，匹配符：%和_