12-MySQL索引和外键

索引

  • 什么是索引?
    索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。
  • MySQL中索引的优缺点和使用原则
    • 优点
      • 所有的MySql字段都可以用作索引
      • 大大加快数据的查询速度
    • 原则
      • 对经常用于查询的字段应该创建索引,
      • 数据量小的表最好不要使用索引,索引就可能不会产生优化效果。
  • 索引分类
    • 普通索引:  MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
    • 唯一索引(唯一键): 索引列中的值必须是唯一的,但是允许为空值,
    • 主键索引:是一种特殊的唯一索引,不允许有空值。
    • 具体可以参考文档
示例一:
create table test1(
    id int,
    name varchar(20),
    index idx_name(name) #创建索引
);

create table test2(
    id int,
    name varchar(20),
);
create index idx_name on test2(name); #创建索引

create table test3(
    id int,
    name varchar(20),
);
alter table test3 add index idx_name(name);

示例二:
create table test4(
    id int,
    name varchar(20),
    unique index idx_name(name) #创建索引
);

create table test5(
    id int,
    name varchar(20),
);
create unique index idx_name on test5(name); #创建索引

create table test6(
    id int,
    name varchar(20),
);
alter table test6 add unique index idx_name(name);


4.删除索引
drop index idx_name on test6

数据完整性

  • 实体完整性(行的完整性)
    一行数据是否完整, 如果多条数据相同, 无法区分, 我们称之为实体不完整
name score
lnj  100
lnj  100

解决方案:
- 添加主键约束
id name score
1  lnj  100
2  lnj  100

- 添加唯一键约束
name score
lnj  100
zq  100

- 自动增长列
id name score
1  lnj  100
2  lnj  100
  • 域完整性(列完整性)
    某一列数据是否完整, 如果出现null, 不匹配的数据 都表示不完整
id name score
1   lnj  100
2   zq   null
2   zq   tyt

- 数据类型约束
id name score
1   lnj  100
2   zq   null
2   zq   0

解决方案:

- 非空约束
id name score
1   lnj  100
2   zq   0
2   zq   0

- 默认值约束
id name score
1   lnj  100
2   zq   59.5
2   zq   0
  • 引用完整性
    默认情况下多张表之间是没有任何关系的, 所以给A表可以随意插入数据, 给B表也可以随意插入数据
    例如有一张学生表和一张成绩表, 要求成绩表中保存的必须是学生表中每个学生的成绩
    +  学生表
      +    id      name
      +     1       lnj
      +     2       zq
    
      + 成绩表
      +     id      stuid     score
      +     1         1         100
      +     2         3         99
      +     3         2         86
    
    • 可以通过外键约束添加表与表之间的关系,告诉MySQL在插入数据的时候, 检查是否存在依赖的数据, 存在才可以插入.

外键

  • 什么是外键?
    如果一张表中有一个字段引用了另一张表中的主键, 那么这个字段我们就称之为外键
例如: 成绩表中的stuid引用了学生表中的id, 那么stuid我们就称之为外键
注意点:  成绩表中的stuid引用了学生表中的id, 那么成绩表我们称之为"从表", 学生表称之为主表
示例二:
create table stugrade2(
    id int auto_increment primary key,
    stuid int,
    score float,
    #告诉MySQL将stuid作为外键, 值是引用stuinfo中的id
    foreign key(stuid) references stuinfo(id)
);
insert into stugrade2 values(null, 3, 100); #报错, 因为sutinfo中没有id为3的人
insert into stuinfo values(null, 'lnj');
insert into stugrade2 values(null, 3, 100); #报错, 因为sutinfo中没有id为3的人
insert into stugrade2 values(null, 1, 100);
delete from stuinfo where id=1; #报错, 因为有其它表引用这这条数据
  • 外键的特点
    • 主表中没有对应的数据, 从表不能插入, 因为插入之后数据也不完整
    • 从表引用这主表的数据, 主表不能删除该数据, 因为删除之后数据也不完整
  • 查看外键
    show create table stugrade3\G;
  • 删除外键
    alter table stugrade3 drop foreign key stugrade3_ibfk_1;
  • 外键相关的操作
- 严格模式(默认)
    + 主表不存在对应的数据, 从表不允许插入
    + 从表引用着主表的数据, 主表不能删除
    + 从表引用着主表的数据, 主表不能修改
- 置空操作
    + 如果主表中的数据被删除了, 从表中对应的字段变为null, 我们就称之为置空操作
    + 流入: 主表中id为1的人被删除了, 那么从表中的stuid变为null
- 级联操作
    + 如果主表发生了变化, 从表也跟着变化, 我们就称之为级联操作
    + 例如: 主表中'lnj'的id从1变为3, 那么从表中的stuid也从1变为3

- 格式:
     foreign key(字段) references 主表名(主表主键)[主表删除的动作][主表更新的动作]

示例一:
create table stugrade(
    id int auto_increment primary key,
    stuid int,
    score float,
    #注意点: 一般在企业开发中都是删除就清空, 更新就随着更新
    foreign key(stuid) references stuinfo(id) on delete set null on update cascade
);
insert into stugrade values(null, 1, 100);
update stuinfo set id=666 where id=1;
delete from stuinfo where id=666;
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 216,163评论 6 498
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,301评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 162,089评论 0 352
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,093评论 1 292
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,110评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,079评论 1 295
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 40,005评论 3 417
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,840评论 0 273
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,278评论 1 310
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,497评论 2 332
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,667评论 1 348
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,394评论 5 343
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,980评论 3 325
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,628评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,796评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,649评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,548评论 2 352

推荐阅读更多精彩内容