MySQL之索引与约束条件

字段约束

作用

顾名思义就是给字段加以限制
其保证数据库的完整性与一致性
通过约束条件防止数据库产生一些不必要的数据保证数据库的正确性相容性安全性

null和not null

mysql> create database test1;

mysql> use test1;

mysql> create table test1(id int not null,name varchar(254) not null);      # 设置约束条件不能为空

mysql> insert into test1 values (1,'SR');

mysql> insert into test1 values (1,'');      # 如果以空字符可以正常插入数据

mysql> insert into test1 values (1,null);            # 为null则报错
ERROR 1048 (23000): Column 'name' cannot be null

mysql> select * from test1 where name is not null;      # 当查询条件为not null的时候即使数据为空也可以被查询出来
+----+------+
| id | name |
+----+------+
|  1 | SR   |
|  1 |      |
+----+------+

mysql> select * from test1 where name ！= '';      # 当查询条件不为空的时候 数据为空不能被查询出来
+----+------+
| id | name |
+----+------+
|  1 | SR   |
+----+------+

PS:当约束条件为not null的时候可以设置为"" 但是不能插入null

上述现象扩展

""和null的区别

null在数据库中是占用空间的为空值不占用空间
对于MyISAM表中null需要一个额外的为四舍五入到最接近的字节

null和not null效率

not null效率高于null
对于null字段来说其本身占用空间在数据查询的时候会参与字段比较
null字段不会被索引如果参与索引的时候效率会下降很多

内核优化

MySQL 难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。
可空列需要更多的存储空间，还需要一个额外字节作为判断是否为 NULL 的标志位 “需要 MySQL内部进行特殊处理”
可空列被索引后，每条记录都需要一个额外的字节，还能导致 MyISAM 中固定大小的索引变成可变大小的索引

default

作用

为指定字段设置默认值
如果在插入数据的时候给默认值赋值则使用赋值的数据

mysql> create table test2(id int not null,name varchar(255) not null default 'SR');

mysql> insert into test2(id)values(1);      # 不给name字段插入数据


mysql> insert into test2(id,name)values(2,'MZ');      # 手动给name字段添加数据

mysql> select * from test2;
+----+------+
| id | name |
+----+------+
|  1 | SR   |      # 使用默认值
|  2 | MZ   |      # 使用指定的值
+----+------+

PS:
1:如果时间字段，默认为当前时间 ，插入 0 时，默认为当前时间。
2:如果是 enum 类型，默认为第一个元素

auto_increment

作用

自动增长
其作用数据类型只能为整形
每次插入一条数据的时候都会在该字段的最大值+1


mysql> create table test3 (id int not null auto_increment primary key ,test int);

mysql> desc test3;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| test  | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

mysql> insert into test3(test) values(1);

mysql> insert into test3(test) values(2);

# id字段自动从1开始插入数据并且自动递增
mysql> select * from test3;
+----+------+
| id | test |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+

mysql> insert into test3(id,test) values(4,9);

mysql> insert into test3(id,test) values(9,9);	# 跳级插入

mysql> insert into test3(test) values(9);		# 从最大值+1

mysql> select * from test3;
+----+------+
| id | test |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    9 |
|  4 |    9 |
|  9 |    9 |
| 10 |    9 |
+----+------+

外键约束

作用

'''
foreign key 就是表与表之间的某种约定的关系，由于这种关系的存在，我们能够让表与表之间的数据，更加的完整， 关联性更强。
关于完整性， 关联性我们举个例子
例：
有二张表，一张是用户表，一张是订单表
1. 如果我删除了用户表里的用户，那么订单表里面与这个用户有关的数据，就成了无头数据了，不完整了。
2. 如果我在订单表里面，随便插入了一条数据，这个订单在用户表里面，没有与之对应的用户。这样数据也不完整了。
3. 如果有外键的话，就方便多了，可以不让用户删除数据，或者删除用户的话，通过外键同样删除订单表里面的数据，这样也能让数据完整。
'''

参数解释

foreign key 当前表的字段
references 外部表名
on update cascade 是级联更新的意思
on update cascade 是级联更新的意思

约束条件

确保参照的表和字段存在
组成外键的字段被索引。
必须使用 ENGINE 指定存储引擎为： innodb。
外键字段和关联字段，数据类型必须一致。

外键创建

mysql> create table orderinfo(o_id int(11) auto_increment, u_id int(11) default '0', username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references userinfo(id) on delete cascade on update cascade) ENGINE=innodb;

mysql> create table userinfo(id int(11) not null auto_increment, name varchar(50) not null default '', sex int(1) not null default '0', primary key(id))ENGINE=innodb;

mysql> insert into userinfo(name,sex)values('HA',1),('LB',2),('HPC',1);
Query OK, 3 rows affected (0.00 sec)

mysql> insert into orderinfo (u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256);
Query OK, 3 rows affected (0.00 sec)

数据测试

mysql> select * from orderinfo;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
|    1 |    1 | HA       |   234 |
|    2 |    2 | LB       |   146 |
|    3 |    3 | HPC      |   256 |
+------+------+----------+-------+


mysql> select id,name,sex,money,o_id from userinfo,orderinfo where id=u_id;
+----+------+-----+-------+------+
| id | name | sex | money | o_id |
+----+------+-----+-------+------+
|  1 | HA   |   1 |   234 |    1 |
|  2 | LB   |   2 |   146 |    2 |
|  3 | HPC  |   1 |   256 |    3 |
+----+------+-----+-------+------+

# 级联删除
mysql> delete from userinfo where id=1;      #删除数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from orderinfo；      # 查看级联表

+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
|    2 |    2 | LB       |   146 |
|    3 |    3 | HPC      |   256 |
+------+------+----------+-------+

# 级联更新
mysql> update userinfo set id=6 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from userinfo;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  3 | HPC  |   1 |
|  6 | LB   |   2 |
+----+------+-----+


# 测试数据完整性
insert into orderinfo (u_id,username,money)values(5,'Find',346);      # 报错此时无u_id=5的用户
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test1`.`orderinfo`, CONSTRAINT `orderinfo_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userinfo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

mysql> insert into userinfo values(5,'Find',1);
Query OK, 1 row affected (0.04 sec)

mysql> insert into orderinfo (u_id,username,money)values(5,'Find',346);
Query OK, 1 row affected (0.00 sec)

mysql> select * from orderinfo;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
|    2 |    6 | LB       |   146 |
|    3 |    3 | HPC      |   256 |
|    7 |    5 | Find     |   346 |
+------+------+----------+-------+

索引

作用

索引是一种特殊的文件（ InnoDB 数据表上的索引是表空间的一个组成部分）
它们包含着对数据表里所有记录的引用指针

[索引详解]https://www.cnblogs.com/SR-Program/p/12008958.html

优缺点

优点

加快查询速度增加查询效率

缺点

索引需要单独的文件来存放索引如果索引量比较大占用存储空间
索引与数据库中的数据相互对应如果数据量较大当有数据增加的时候索引需要同步更新降低效率

普通索引

作用

最基础的索引只是用来加快查询速度
其不具备唯一性

普通索引创建

# 使用index或者key指定索引字段
# 索引名称可以添加也可以省略 如果省略默认以字段名字作为索引名称
mysql> create table test4(id int,name varchar(254),index(id));

mysql> desc test4;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| name  | varchar(254) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

mysql> show create table test4;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| test4 | CREATE TABLE `test4` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(254) DEFAULT NULL,
  KEY `id` (`id`)     # 索引名称 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> create table test4(id int,name varchar(254),index name_index(name));      # 指定索引名称index_name


mysql> show create table test5;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test5 | CREATE TABLE `test5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(254) DEFAULT NULL,
  KEY `name_index` (`name`)      # 指定索引名称
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

key为MUL表示普通索引该列值可以重复
该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值 NULL

索引删除与添加

mysql> alter table test5 drop key name_index;
mysql> desc test5;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(254) | YES  |     | NULL    |       |      #  无索引字段
+-------+--------------+------+-----+---------+-------+
mysql> alter table test5 add key name_index(name);

mysql> desc test5;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(254) | YES  | MUL | NULL    |       |      # 添加成功
+-------+--------------+------+-----+---------+-------+

唯一索引

作用

其与普通索引类似
但是在索引列所在的字段中插入的数据值必须唯一
唯一性索引允许有空值允许为null

唯一索引创建

mysql> create table test6(id int auto_increment primary key,name varchar(254), unique index(name));

mysql> desc test6;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(254) | YES  | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> insert into test6 values(1,'SR');

mysql> insert into test6 values(2,null);

mysql> insert into test6 values(3,'SR');      # 报错含有重复值
ERROR 1062 (23000): Duplicate entry 'SR' for key 'name'

唯一索引添加与删除

mysql> alter table test6 drop  index name;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test6;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(254) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table test6 add unique index(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

主键索引

作用

使用主键索引查询速度最快
主键索引所在的字段数据必须唯一
主键索引不许为空

主键索引创建

mysql> create table test7(id int auto_increment not null, name varchar(254),primary key (id));
Query OK, 0 rows affected (0.06 sec)

mysql> desc test7;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(254) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into test7 values('','SR');      # 数据不能为空
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1
mysql>
mysql> insert into test7 values(1,'SR');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test7 values(1,'SR');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

主键索引删除与添加

# 不建议当有生产数据的时候在来创建主键索引 因为此时有数据 无法保证数据唯一 如果不唯一则无法创建主键索引
alter table test7 drop primary key ;      # 此时报错的原因因为自增长
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> alter table test7 change id id int not null;      # 去掉auto_increment

mysql> alter table test7 drop primary key ;      # 删除主键

mysql> alter table test7 change id id int not null primary key auto_increment;

复合索引

作用

索引可以包含一个、两个或更多个列
两个或更多个列上的索引被称作复合索引

复合索引创建

mysql> create table test8( host varchar(15) not null ,port smallint(4) not null ,access enum('deny','allow') not null, primary key (host,port));

mysql> desc test8;
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| host   | varchar(15)          | NO   | PRI | NULL    |       |
| port   | smallint(4)          | NO   | PRI | NULL    |       |
| access | enum('deny','allow') | NO   |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+

mysql> insert into test8 values('10.96.52.46',22,'deny');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test8 values('10.96.52.46',21,'allow');
Query OK, 1 row affected (0.05 sec)

mysql> insert into test8 values('10.96.52.46',21,'allow');      # 数据重复
ERROR 1062 (23000): Duplicate entry '10.96.52.46-21' for key 'PRIMARY'

全文索引

作用

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们索结果。
MySQL 在数据量较大的情况下，高并发连接的情况下。select 语句 where bName like '%网%'使用% _ 通配符，不通过索引，直接全表扫描。ABSUWU LIKE ‘%U_U’数据库压力大。
MySQL 的解决方案：全文索引： 3.2 开始支持全文索引。无法正确支持中文。从 MySQL 5.7.6 开始 MySQL 内置了 ngram 全文检索插件，用来支持中文分词

创建

# 全文索引字段必须是varchar text
# 存储引擎必须是myisam
# mysql自带的全文搜索引擎只能作用于myisam存储引擎

mysql> create table test10 (id int,article text, fulltext key(article)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> show index from test10G;
*************************** 1. row ***************************
        Table: test10
   Non_unique: 1
     Key_name: article
 Seq_in_index: 1
  Column_name: article
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT      # 全文引擎
      Comment:
Index_comment:
1 row in set (0.00 sec)

相关阅读:
java 集合Map
java 集合Collection
Python 列表生成式，迭代器&生成器，Json&pickle数据序列化
 Python 函数
 Python列表，字典，元组，字符串操作，文件操作，字符编码
 python的输入输出与循环
 通过数据流发送接收图片
 php中变量的详细介绍
 php数组循环的三种方式
 php session访问限制
原文地址：https://www.cnblogs.com/SR-Program/p/13363286.html