MySql 约束与索引


MySql

提示:这里可以添加本文要记录的大概内容:
例如:随着人工智能的不断发展,机器学习这门技术也越来越重要,很多人都开启了学习机器学习,本文就介绍了机器学习的基础内容。


提示:以下是本篇文章正文内容,下面案例可供参考

其他代码:

show database; 显示所有数据库
show table ; 显示所有的表
describe 表 显示该表的结构

一、约束

主键约束

能确定唯一 一张表的一个记录 不会重复为空

Create table user1 (
id int primary key ,
name varchar(20)
);

联合主键

联合主键 的 主键值 加起来不重复 就可以 (联合主键不能有字段 为空

Create  table user2 (
id int,
name varcahr(20),
password varchar(20),
primary key(id,name)
);

添加 删除主键

忘记创建 主键 :

alter table user4 add primary key(id);

如何删除 :

alter table user4 drop primary key ;

如何修改主键约束 :(使用modify 修改字段 添加约束 )
alter table user4 modify id int primary key ;

自增约束

create table user3(
id int primary key auto_increment,
name varchar(20)
);

例如 :
在不添加 id 时 如果设置 auto_increment 则可以自动添加

外键约束

涉及到两个表: 父表 子表
主表 , 副表

---- 班级表

create table classes(
id int primary key,
name varchar(20)
);

-------学生表

create table students(
id int primary key,
name varchar(20),
class_id int ,
**foreign key(class_id) references classes(id)**   
);

代表 这个表的 class_id 必须来自 表 classes id 这个字段

  1. 主表 classes 中没用的数据值 在副表中 是 不可以使用的

唯一约束

约束修饰的字段不可以重复

Create table user5(
  id int,
  name varchar (20)
);

alter table user5 add unique(name);

第二种 :

Create table user6(
  id int,
  name varchar (20)
  unique (name)
);

第三种 :

Create table user6(
  id int unique ,
  name varchar (20)
);

第四种:(类似联合主键 只要都不重复就OK)

Create table user6(
  id int,
  name varchar (20)
  unique (id,name)
);

删除 唯一约束 :

alter table user7 drop index name;

Modify 添加?

alter table user7 modify name varchar(20) unique;\

总结

  1. 建表的时候可以添加 约束
  2. 使用 alter 。。。。。 add。。。。。
  3. alter。。。。。。modify。。。。
    删除:
  4. alter。。。。。drop。。。。。

非空约束

修饰的字段 不能为空 NULL (可以使用 alter modify)

create table user9 (
id int ,
name varchar (20) not null
);

默认约束

当我们 插入 字段的时候 如果没用传值 就会使用 默认值

Create table user10(
id int ,
name varchar(20),
age int default 10
);

索引

什么是索引?

索引是一种高效获取数据的存储结构,例:hash、 二叉、 红黑。
一、mysql 表中如果是单主键的话,那这个主键也会被 系统默认建为 索引

在mysql 5.5 以后 mysql 用的存储引擎 为 innoDB
在mysql5.5 以前 mysql 用的存储引擎 为 MyISAM

Innodb(聚集索引)

若以这个引擎创建数据库表Create table user (……),它实际是生成两个文件:

user.ibd 索引文件
user.frm数据结构类型

因为innodb引擎创建表默认就是以主键为索引,所以不需要myi文件。

下图为innodb表的结构图:很显然它与myisam最大的区别是将整条数据存在叶子节点,而不是地址。(叶子节点存的是主键索引和数据信息)

若此时,你在其他列创建索引例如name,它就会另外创建一个以name为索引的索引树,(叶子节点存的是索引和主键索引)。

你在执行select * from user where name = ‘吴磊’,他的执行过程如下:

(1)找到name索引树
(2)根据name的值找到该树下叶子的name索引和主键值
(3)用主键值去主键索引树去叶子节点到该条数据信息

二、mysql 表中如果是复合主键的话,那系统会遵循左对齐原则,即如复合主键 a 和 b字段和c字段…, 默认建的主键索引是abc即主键全有,把所有主键全部建立在一个单独的主键索引中。

在这里插入图片描述

Myisam引擎(非聚集索引)

若以这个引擎创建数据库表Create table user (……),它实际是生成三个文件:

user.myi 索引文件
user.myd数据文件
user.frm数据结构类型。

如下图:当我们执行 select * from user where id = 1的时候,它的执行流程。

(1)查看该表的myi文件有没有以id为索引的索引树。
(2)根据这个id索引找到叶子节点的id值,从而得到它里面的数据地址。(叶子节点存的是索引和数据地址)。
(3)根据数据地址去myd文件里面找到对应的数据返回出来。

在这里插入图片描述
MyISAM引擎和InnoDB引擎的区别
  MyISAM:支持全文索引;不支持事务;它是表级锁;会保存表的具体行数.
  InnoDB:5.6以后才有全文索引;支持事务;它是行级锁;不会保存表的具体行数.
  一般:不用事务的时候,count计算多的时候适合myisam引擎。对可靠性要求高就是用innodby引擎。推荐用InnoDB引擎.

加了索引之后能够大幅度的提高查询速度,但是索引也不是越多越好,一方面它会占用存储空间,另一方面它会使得写操作变得很慢。通常我们对查询次数比较频繁,值比较多的列才建索引。
  例如:select * from user where sex = “女”, 这个就不需要建立索引,因为性别一共就两个值,查询本身就是比较快的。
     select * from user where user_id = 1995 ,这个就需要建立索引,因为user_id的值是非常多的。

B+Tree的特性

(1)由图能看出,单节点能存储更多数据,使得磁盘IO次数更少。

(2)叶子节点形成有序链表,便于执行范围操作。

(3)聚集索引中,叶子节点的data直接包含数据;非聚集索引中,叶子节点存储数据地址的指针。

尚未解决:
InnoDB与MyISAM 不同点的原理,什么是行级锁
B树以及b+树的存储结构 以及原理
表空间的存储结构

MYSQL INNODB数据存储结构

MySQL 高级知识(索引、优化)

Mysql存储引擎详解

Mysql索引原理

Mysql索引实现原理

MySQL 索引,B+树原理,以及建索引的几大原则

©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页