MySQL 数据学习笔记速查表（约束、索引）

需要休息的KK.

于 2024-04-02 18:30:00 发布

阅读量364

点赞数 5

分类专栏： mysql 文章标签： mysql 学习笔记数据库索引约束

本文链接：https://blog.csdn.net/weixin_54217348/article/details/137218274

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

十一、六大约束

1、主键约束（primary key）

表中每一行都应该有唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键

应该总是定义主键，虽然并不总是需要主键，推崇保证每张表都具有一个主键以便于方便管理

# 主键约束：保证数据完整性，防止数据表中的两条记录完全相同
# 使用方法：1、创建表时，添加主键约束：字段名 类型 primary key
# 示例代码：
create table student(
    stu_id varchar(30) primary key,
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);
		
# 2、列声明后，可以设置复合主键约束：primary key(列名,列名……)
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30),
    primary key(stu_id)
);
		
# 3、修改表时，添加主键约束：alter table 表名 add primary key(列名);
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);
alter table student add primary key(stu_id);
		
# 4、删除主键约束，alter table 表名 drop primary key;
alter table student drop primary key;

2、唯一约束（unique）

# 唯一约束：指定一个或多个列的组合值具有唯一性，防止在列中输入重复值
# 使用方法：1、创建表时，添加唯一约束：字段名 类型 unique
# 示例代码：
create table student(
    stu_id varchar(30),
    stu_name varchar(30) unique,
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);
		
# 2、列声明后，设置唯一约束：constraint (自定义约束名) unique(列名1,列名2……);
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30),
    constraint uni_stu_name unique(stu_name)
);
		
# 3、修改表时，添加唯一约束：alter table 表名 add unique(列名1,列名2……);
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);
alter table student add unique (stu_name);
		
# 4、删除唯一约束：alter table 表名 drop index（自定义的约束名）;
alter table student drop indxe uni_stu_name;

# 为了避免异常 应使用not null unique （非空+唯一） stu_name varchar(30) NOT NULL UNIQUE

3、非空约束（not null）

# 非空约束：保证字段不允许为空NULL值
# 使用方法：1、创建表时，添加非空约束：字段名 类型 not null
# 示例代码：
create table student(
    stu_id varchar(30),
    stu_name varchar(30) not null,
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);
		
# 2、修改表时，添加非空约束：alter table 表名 modify 列名 类型 not null;
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);
alter table student modify stu_name varchar(30) not null;
		
# 3、删除非空约束：alter table 表名 modify 列名 类型 null;
alter table student modify stu_name varchar(30) null;

4、默认约束（default）

# 默认约束：每个字段只能定义一个默认约束
# 使用方法：1、创建表时，添加默认约束：字段名 类型 default 默认值;
# 示例代码：		
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30) defalult '男',
    stu_birthday varchar(30),
    stu_class varchar(30)
);

# 2、修改表时，添加默认约束：alter table 表名 modify 列名 类型 default 默认的值;
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);
alter table student modify stu_gender varchar(30) default '男';

# 3、删除默认约束：alter table 表名 modify 列名 类型 default null;    
alter table student modify stu_gender varchar(30) default null;

5、外键约束（foreign key）

# 外键约束：用来在两个表的数据之间建立连接。可以是一个列或者多列，一个表只可有有一个主键，但可以有多个外键。
# 使用方法：1、创建表时，建立外键约束：constraint (自定义约束名) foreign key (指定外键) references 参照表 参照表的主键
# 示例代码：		
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30),
    constraint fk_stu_class foreign key (stu_class) references calss(c_id)
);
		
# 2、修改表时，建立外键约束：alter table 表名 add constraint (自定义约束名) foreign key (指定外键) references (参照表) 参照表的主键
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);
alter table student 
add constraint fk_stu_class foreign key (stu_class) references calss(c_id);

# 3、删除外键约束：alter table 表名 drop foreign key (自定义的外键名)
alter table student drop foreign key fk_stu_class;

6、检查约束（check）

# 检查约束：CHECK 约束用于限制列中的值的范围。
# 使用方法：1、创建表时，建立检查约束：字段名 类型 check 检查条件;
create table student(
    stu_id varchar(30) check(stu_id>0),
    stu_name varchar(30) check(length(stu_name)>3),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);

# 2、修改表时，添加非空约束：alter table 表名 add constraint (自定义约束名) check 检查条件;
create table student(
    stu_id varchar(30),
    stu_name varchar(30),
    stu_gender varchar(30),
    stu_birthday varchar(30),
    stu_class varchar(30)
);
alter table student add constraint check_name_length check(length(stu_name)>3);

# 3、删除检查约束：drop constraint (自定义的约束名);
alter table student drop check check_name_length

7、约束实例

# 创建客户表
create table customer(
  id int primary key auto_increment,
  name varchar(50) not null unique,
  iden varchar(18) not null unique,
  tel varchar(20) not null,
  address varchar(255) default '北京'
);

# 创建存款类型表
create table savingtype(
  id int primary key auto_increment,
  name varchar(50) not null unique,
  remark varchar(255)
);

# 创建货币类型表
create table curtype(
  id int primary key auto_increment,
  name varchar(50) not null unique
);

# 创建银行卡表
create table card(
  id varchar(16) primary key,
  curtypeid int not null,
  savingtypeid int not null,
  createtime timestamp not null default now(),
  createmoney decimal(18,2) check(createmoney>=10),
  balance decimal(18,2) not null check(balance>=0),
  password varchar(6) not null default '123456' check(length(password)=6),
  customerid int not null,
  islost bool not null default 0,
  constraint fk_curtype foreign key(curtypeid) references curtype(id),
  constraint fk_savtype foreign key(savingtypeid) references savingtype(id),
  constraint fk_cus foreign key(customerid) references customer(id)
);

# 创建交易记录表
create table trade(
  cardid varchar(16) not null,
  tradetime timestamp not null default now(),
  tradetype varchar(10) not null default '支出',
  trademoney decimal(18,2) not null check(trademoney>=0),
  remark varchar(255),
  constraint fk_card foreign key (cardid) references card(id),
  constraint pk_trade primary key (cardid, tradetime)
);

十二、索引

1、什么是索引？

索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。
对于索引，会保存在额外的文件中。

2、索引的作用？

索引是数据库中专门用于帮助用户快速查询数据的一种数据结构。
类似字典目录，查找内容时可以根据内容的编号在目录查找到数据的存放位置，然后直接获取详细内容。

3、索引的优缺点？

优点	缺点
`可以根据需要保证唯一性`	降低表的增删改的效率，因为每次增删改，索引都需要进行动态维护
`可以提高对数据的查询速度`	创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
`可提高表与表之间的连接速度`	索引需要占用物理空间，数据量越大，占用空间越大
`可减少查询中分组和排序的时间`	/

4、索引的用途？

需要索引	不需要索引
`主键自动建立唯一索引`	频繁更新的字段
`频繁作为查询条件的字段`	where条件用不到的字段
`查询中排序的字段`	数据较少
`查询中统计或者分组的字段`	包含大量的重复字段

5、索引类型

普通索引

# index		  关键字
# index_Name  代表创建的索引名字
# column_name 代表创建索引的字段（即针对某个字段创建的索引）
# length	  代表使用前面字段中的长度限制

# 方法一：直接创建索引
CREATE INDEX index_Name ON table_name(column_name(length));

# 方法二：修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column_name(length))

# 方法三：创建表的时候同时创建索引
create table table_name(
    id int,
    name varchar(20),
    INDEX index_name (name(length))
);

# 查看索引
SHOW INDEX FROM test1;
# 删除索引
DROP INDEX index_name ON test1;
ALTER TABLE test1 DROP INDEX index_name

唯一索引

# unique      唯一关键字
# index		  索引关键字
# indexName   代表创建的索引名字
# column_name 代表创建索引的字段（即针对某个字段创建的索引）
# length	  代表使用前面字段中的长度限制

# 方法一：直接创建索引
CREATE UNIQUE INDEX indexName ON table_name(column_name(length))

# 方法二：修改表结构的方式添加索引
# table_name  代表使用的表名
ALTER TABLE table_name ADD UNIQUE indexName ON (column_name(length))

# 方法三：创建表的时候同时创建索引
create table table_name(
    id int,
    name varchar(20),
    UNIQUE index_Name (name(length))
);

# 查看索引
SHOW INDEX FROM table_name;
# 删除索引
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name

主键索引

# primary key 主键关键字
# table_name  代表使用的表名
# column_name 代表创建索引的字段（即针对某个字段创建的索引）

# 方法一：修改表结构的方式添加索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name) ;

# 方法二：创建表的时候同时创建索引
create table table_name(
    id int,
    name varchar(20),
    PRIMARY KEY (id)
);

# 查看索引
SHOW INDEX FROM table_name;
# 删除索引
ALTER TABLE table_name drop primary key;

组合索引

# index		  索引关键字
# indexName   代表创建的索引名字
# column_name 代表创建索引的字段（此时会针对多个字段来创建索引）

# 方法一：修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_Name (column_name,column_name,column_name); 

# 方法二：创建表的时候同时创建索引
create table table_name(
    id int,
    name varchar(20),
    index index_name(id,name)
);

# 查看索引
SHOW INDEX FROM table_name;
# 删除索引
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name

全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配;

fulltext索引配合match against操作使用，而不是一般的where语句加like。
它可以在create table，alter table ，create index使用；
不过目前只有char、varchar，text 列上可以创建全文索引。
值得一提的是：在数据量较大时候，
先将数据放入一个没有全局索引的表中，
然后再用CREATE index创建fulltext索引，
要比先为一张表建立fulltext然后再将数据写入的速度快很多。

# fulltext    全文关键字
# index		  索引关键字
# indexName   代表创建的索引名字
# column_name 代表创建索引的字段（即针对某个字段创建的索引）

# 方法一：直接创建索引
CREATE FULLTEXT INDEX index_name ON index_name(column_name)

# 方法二：修改表结构添加全文索引
ALTER TABLE table_name ADD FULLTEXT index_name(column_name)

# 方法三：创建表的适合添加全文索引
create table table_name(
    id int,
    name varchar(20),
    info varchar(255),
    FULLTEXT (info)
);
# 查看索引
SHOW INDEX FROM table_name;
# 删除索引
alter table table_name drop index index_name; 
drop index index_name on table_name;

6、索引示例

解释 explain

explain 					# 解释 让系统解释以下该sql语句使用了什么约束和索引以及结果
							# 概要描述：
id							# 选择标识符
select_type					# 表示查询的类型。
table						# 输出结果集的表
partitions					# 匹配的分区
type						# 表示表的连接类型
possible_keys				# 表示查询时，可能使用的索引
key							# 表示实际使用的索引
key_len						# 索引字段的长度
ref							# 列与索引的比较
rows						# 扫描出的行数(估算的行数)
filtered					# 按表条件过滤的行百分比
Extra						# 执行情况的描述和说明

2、普通索引


# 普通索引：仅加速查询,最基本的索引没有任何限制，是我们经常使用到的索引 


# 创建表的同时创建普通索引
create table book(
	id int primary key ,
    title varchar(50) not null unique,
    author varchar(50),
    price decimal(10,2),
	# 创建索引 给出索引名称 并指定在索引所在的字段（如果不给名称会默认字段名称）
    index index_author (author) 
);

# 插入多条数据
insert into book values
(1,'mysql','monty',10),
(2,'sql server','microsoft',20),
(3,'oracle','oracle',30),
(4,'db2','ibm',40);

# 创建表之后发现没创建索引？可以通过修改表结构语句创建普通索引
alter table book add index index_name(name);		# 方法1
greate index index_name on book(name);				# 方法2

# 检验索引
# 查询book全表 使用id进行过滤 触发主键约束
explain select * from book where id = 1;
# 使用模糊查询 无法触发约束
explain select * from book where id like '%1%';

# 查询book全表 使用title进行过滤 触发唯一约束
explain select * from book where title = 'mysql';
# 使用模糊查询 无法触发约束
explain select * from book where title like '%m%';

# 查询book全表 使用author进行过滤 触发索引约束
explain select * from book where author = 'monty';
# 使用模糊查询 无法触发约束
explain select * from book where author like '%m%'; 

# 以上行为表明 模糊查询无法触发约束和索引 因此模糊查询效率较慢 需要遍历整表 

# 如果不需要索引了 可以删除索引
alter table book drop index index_author; # 方法1：删除book表中的index_author索引
drop index index_author on book; 		  # 方法2：同上

3、唯一索引

# 唯一索引：加速查询+列值唯一(可以有null)


# 创建表的同时创建唯一索引
create table test1(
	id int not null,
	name varchar(30) not null unique,
	# unique index unique(name)
	# constraint unique_name unique(name)
);　
# 查看索引
show create table test1\G;

# 创建表之后发现没创建索引？可以通过修改表结构语句创建唯一索引
alter table test1 add unique index unique_name(name);	# 方法1
create unique index unique_name on student(name);		# 方法2

# 展示表的索引
show index from test1;

# 检验索引
insert into test1 values(1,'张三');
insert into test1 values(2,'张三');
# 查询test1全表 使用age进行过滤 触发唯一索引约束
explain select * from test1 where name = '张三';
# 使用模糊查询 无法触发约束
explain select * from test1 where name like '%张三%';

# 如果不需要索引了 可以删除索引
alter table test1 drop index unique_name; # 方法1：删除test1表中的unique_name索引
drop index unique_name on test1; 		  # 方法2：同上

4、主键索引

#####################################################
# 主键索引：加速查询+列值唯一(（)不可以有null)+ 表中只有一个 #
#####################################################

# 创建表的同时创建主键约束
# 主键索引建立的规则是int优于varchar(方便使用自增AUTO_INCREMENT)
create table test2(
	id int primary key,
	name varchar(30) not null
	# 或者这样(此方法可以指定多个)
	# primary key(stu_id)
);　　
# 查看索引
show create table test2\G;

# 创建表之后发现没创建索引？可以通过修改表结构语句创建主键索引
alter table student add primary key(stu_id);

# 检验索引
insert into test2 values(1,'张三');
insert into test2 values(1,'李四');

# 查询book全表 使用id进行过滤 触发主键约束
explain select * from test2 where id = 1;
# 使用模糊查询 无法触发约束
explain select * from test2 where id like '%1%';


# 如果不需要索引了 可以删除索引
alter table test2 drop primary key; # 方法1：删除test2表中的primary key索引

5、组合索引

########################################################
# 组合索引：多列值组成一个索引专门用于组合搜索，其效率大于索引合并 #
# 一个表中含有多个单列索引不代表是组合索引					   #
# 将几个列作为一条索引进行检索，使用最左匹配原则				 #
# 通俗一点讲，组合索引是:包含多个字段但是只有一个索引名称		   #
# 在使用查询的时候遵循mysql组合索引的"最左前缀"				#
#########################################################

# 创建表的同时创建组合约束
CREATE TABLE test3(
    id int not null,
    name varchar(30) not null,
    age int not null,
    info varchar(255),
    index MultiIdx(id,name,age)
);
# 查看索引
show create table test3\G;

# 创建表之后发现没创建索引？可以通过修改表结构语句创建主键索引
create index MultiIdx on test3(id,name,age);

# 插入数据
insert into test3 values(1,'张三',18,'张三是一个公众人物');
insert into test3 values(2,'李四',19,'李四也是一个公众人物');

# 检验索引
# 遵循最左前缀
# 查询test4全表 单独使用id进行过滤 触发组合索引约束
explain select * from test3 where id=1;
# 查询test4全表 单独使用name进行过滤 无法触发组合索引约束
explain select * from test3 where name='张三';
# 查询test4全表 单独使用age进行过滤 无法触发组合索引约束
explain select * from t3 where age=19;

# 如果不需要索引了 可以删除索引
alter table test3 drop index MultiIdx; # 方法1：删除test1表中的unique_name索引
drop index MultiIdx on test3; 		   # 方法2：同上

# 解释最左前缀
# 组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀
# 例如，这里由id、name和age3个字段构成的索引
# 索引行中就按id/name/age的顺序存放，
# 索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)
# 如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询

6、全文索引

##############################################
# 全文索引：对文本的内容进行分词，进行搜索			 #
# 全文索引(FULLTEXT)仅可以适用于MyISAM引擎的数据表 #
# 常作用于CHAR，VARCHAR、TEXT数据类型的列		  #
# 全文索引：在很多文字中，通过关键字就能够找到该记录	  #
###############################################

# 创建表的同时创建全文索引
# 需要指定引擎
CREATE TABLE test4(
    id int not null,
    name varchar(30),
    age int not null,
    info varchar(255),
	FULLTEXT INDEX FullTxtIdx(info)
)ENGINE=MyISAM;

# 查看索引
show create table test4\G;
# 展示表索引
show index from test4;

# 创建表之后发现没创建索引？可以通过修改表结构语句创建全文索引
alter table test4 add fulltext index FullTxtIdx(info);		# 方法1
create fulltext index FullTxtIdx on test4(info);			# 方法2

# 插入表数据
insert into test4 values
(1,'AAA',3,'text is so good，hei，my name is bob'),
(2,'BBB',4,'my name is yangkai'),
(3,'CCC',5,'hello word, hello MySQL,hello Java, hello Python');

# 检验索引
# 此时都会触发全文索引
select * from test4 where match(info) AGAINST('yangkai');
select * from test4 where match(info) against('Java');
# 最小搜索长度 MyISAM 引擎下默认是 4，InnoDB 引擎下是 3


# 查看索引状态
explain select * from test4 where match(info) AGAINST('gorlr');

# 如果不需要索引了 可以删除索引
alter table test4 drop index FullTxtIdx; # 方法1：删除test1表中的unique_name索引
drop index FullTxtIdx on test4;		 	 # 方法2：同上

# 注意：
# 在使用全文搜索时需要借助MATCH函数，并且其全文搜索的限制比较多，比如只能通过MyISAM引擎，比如只能在CHAR,VARCHAR,TEXT上设置全文索引
# 比如搜索的关键字默认至少要4个字符，比如搜索的关键字太短就会被忽略掉等

需要休息的KK.

关注

5
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL 数据学习笔记速查表（约束、索引）

MySQL的索引和约束是数据管理的两大核心要素。通过索引，实现快速查询；通过约束，确保数据准确性。掌握MySQL的索引和约束，为业务提供坚实的数据支持，助力您的应用高效、稳定运行。推荐您深入学习，为您的数据世界增添力量！
复制链接

扫一扫