时间：2021-07-01 10:21:17 帮助过：1人阅读
索引类型可分为以下几种：普通索引，唯一性索引，主键索引和符合索引等。
索引创建原则：
索引并不是越多越好，在数据亮不大，列中的值变化不多的情况下不建议建立索引，而对于需要经常排序的列和需要唯一型约束对应使用唯一型索引。
2.1 普通索引
最基本的索引，不具备唯一性，能加快查询速度。
语法：
create table tb_name(字段定义 ，index 索引名称 (字段)，index 索引名称(字段))；
注意:在创建索引的时候，可以使用key也可以使用index，其index 索引名称 (字段)，索引名称不是必加项，不加的话使用字段作为索引名。
例：创建表时，创建一个普通索引
mysql> create table suoyin1(id int(5),name varchar(20),pwd varchar(28),index(pwd)); mysql> desc suoyin1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(5) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | pwd | varchar(28) | YES | MUL | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
2.1.1 添加索引-alter
语法：alter table tb_name add index 索引名称 (字段1，字段2...);
mysql> alter table suoyin2 add tel varchar(13); Query OK, 0 rows affected (0.29 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table suoyin2 add index index_tel (tel); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc suoyin2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(5) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | pwd | varchar(28) | YES | MUL | NULL | | | tel | varchar(13) | YES | MUL | NULL | | +-------+-------------+------+-----+---------+-------+
2.1.2 查看索引：
mysql> desc suoyin2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(5) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | pwd | varchar(28) | YES | MUL | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
key类型为MUL表示的是普通索引，允许重复值。
2.1.3 索引删除
mysql> alter table suoyin2 drop key index_tel; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc suoyin2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(5) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | pwd | varchar(28) | YES | MUL | NULL | | | tel | varchar(13) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
2.2 唯一索引
唯一索引故名思意，该索引列的值不能重复，只能出现一次，必须唯一，用来约束内容，字段也只能出现一次，用来约束内容，唯一型索引允许有NUL值。
语法：create table tb_name(字段定义:unique key 索引名 (字段))；
注意：常用在值不能重复的字段上，比如用户名，电话号码和身份证号等。
mysql> create table suoyin3(uuid int(10) auto_increment primary key,Name varchar(18),Pwd varchar(15),unique index (Name)); mysql> desc suoyin3; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | uuid | int(10) | NO | PRI | NULL | auto_increment | | Name | varchar(18) | YES | UNI | NULL | | | Pwd | varchar(15) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+
2.2.1 修改唯一型索引
删除索引：
alter table tb_name drop key key_name; mysql> alter table suoyin3 drop key Name;
添加索引：
alter table tb_name add unique(name); mysql> alter table suoyin3 add key name (Name);
2.3 主键索引：
在查询数据库是，按照主键索引查找速度最快，每个表只能有一个主键列，可以有多个普通索引列，主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一，不允许为空。
2.3.1 创建主键是索引:
语法：create table tb_name(列定义，)
mysql> create table primary1(uuid int(10) not null auto_increment primary key,name varchar(18) not null); Query OK, 0 rows affected (0.16 sec) mysql> desc primary1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | uuid | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(18) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
2.3.1 删除主键索引键值，必须先修改索引对应字段的修饰符
mysql> alter table primary1 change uuid uuid int(10) not null; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table primary1 drop primary key; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0
添加索引：
mysql> alter table primary1 change uuid uuid int(14) not null primary key; or mysql> alter table primary1 change uuid uuid int(10) auto_increment primary key;
三、复合索引：
一个表中创建的索引多余2个的时候，则被称作符合索引，
例：创建一个表存放服务器允许或拒绝的IP和port，要记录中的IP和Port需要唯一。
mysql> create table firewall(host varchar(15) not null,port smallint(4) not null,access enum(‘deny‘,‘allow‘)not null,primary key(host,port)); Query OK, 0 rows affected (0.13 sec)
数据插入效果
mysql> insert into firewall values(‘192.168.31.101‘,56,‘allow‘); Query OK, 1 row affected (0.01 sec) mysql> insert into firewall values(‘192.168.31.101‘,56,‘allow‘); ERROR 1062 (23000): Duplicate entry ‘192.168.31.101-56‘ for key ‘PRIMARY‘ mysql> insert into firewall values(‘192.168.31.102‘,56,‘deny‘); Query OK, 1 row affected (0.00 sec) mysql> insert into firewall values(‘192.168.31.101‘,80,‘deny‘); Query OK, 1 row affected (0.01 sec) mysql> insert into firewall values(‘192.168.31.102‘,56,‘deny‘); ERROR 1062 (23000): Duplicate entry ‘192.168.31.102-56‘ for key ‘PRIMARY‘
在插入相同的IP和端口时会报错。
创建表时，加入各种索引的顺序如下：
create table tb_name (字段定义，primarykey (‘key字段‘)，unique key ‘BI‘ (‘ukey‘),key (‘key_word‘),key (other));
四、全文索引
MySQL的全文索引是目前搜索引擎使用的一种关键技术，它能够利用分词技术，等多种算法智能分析处文本中文字中关键字词的频率及重要性，然后按照一定的算法规则智能赛选出搜索结果。
在MySQL 5.7版本之前全文索引只支持MISAM存储引擎的，在之后的版本中InnoDB引擎也引入了全文索引功能。
例如：MySQL在数据量比较大和高并发链接的情况下，
select语句 where bName like ‘%网%‘
其中%在此表示通配符，不通过索引，直接进行权标扫描。
使用全文索引对MySQL数据库的压力比较大。
创建全文索引：
方式一：
create table tb_name(字段定义，fulltext key 索引名(字段))；
方式二：
alter table tb_nameadd fulltext 索引名(字段)；
五、外键约束
外键约束，故名思意就是建立表与表之间设置某种关系，正是由于这种关系的存在，能够使表之间的数据进行关联，并通过使用外键约束使得表与表之间更加完整，使表的关联性更强，也因此保证列表的完整性。
5.1 创建外键语法：
create table tb_name(... [constraint [约束名] foreign key [外键字段]] references [外键表名](外键字段1，外键字段2...)[on
delete cascade][on update cascade]);
注：on update cascade是级联更新操作，on delete cascade是级联删除。也就是在你更新或删除主键表，那外键表也会跟随一起更新和删除。
外键创建成功需要满足以下几点：
1、确保参照的字段和表真实存在
2、组成外键的字段被索引
3、必须使用type指定的存储引擎为innodb；
4、外键字段和关联字段，数据类型必须一致。
例：创建一个用户表和产品表并通过引入外键foreign key使其进行关联；
mysql> create table USER(uid int(10) auto_increment,uname varchar(18) not null,sex ENUM(‘男‘,‘女‘)default ‘女‘,Tel varchar(14),address varchar(60),primary key u_id(uid)); Query OK, 0 rows affected (0.09 sec) mysql> create table 订单(oid int(10) auto_increment,uid int(10) not null,goods varchar(48) not null,gid int(18) not null,money int(10) not null,primary key o_id(oid),index g_name(goods),foreign key order_f_key(uid) references USER(uid) on delete cascade on update cascade); Query OK, 0 rows affected (0.18 sec) 插入数据 mysql> insert into USER(uname,sex) values(‘张飞‘,‘男‘),(‘关羽‘,‘男‘),(‘小乔‘,‘女‘); mysql> insert into 订单(oid,uid,goods,gid,money) values(0000001,1,‘双汇牛肉‘,100000001,56),(0000002,3,‘邦杰牛肉‘,100000011,‘54‘); Query OK, 2 rows affected (0.08 sec)
查询：
mysql> select u.uid,u.uname,o.gid,o.goods,o.money from USER as u left join 订单 as o on u.uid=o.uid; +-----+--------+-----------+--------------+-------+ | uid | uname | gid | goods | money | +-----+--------+-----------+--------------+-------+ | 1 | 张飞 | 100000001 | 双汇牛肉 | 56 | | 3 | 小乔 | 100000011 | 邦杰牛肉 | 54 | | 2 | 关羽 | NULL | NULL | NULL | +-----+--------+-----------+--------------+-------+ 3 rows in set (0.00 sec)
删除测试：
将表‘用户‘uid=1的用户删除
mysql> delete from table USER where uid=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘table USER where uid=1‘ at line 1
测试更新：
mysql> update USER set uid=6 where uname=‘张飞‘; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
查询更新结果
mysql> select u.uid,o.gid,o.goods,o.money from USER as u left join 订单 as o on u.uid=o.uid; +-----+-----------+--------------+-------+ | uid | gid | goods | money | +-----+-----------+--------------+-------+ | 6 | 100000001 | 双汇牛肉 | 56 | | 3 | 100000011 | 邦杰牛肉 | 54 | | 2 | NULL | NULL | NULL | +-----+-----------+--------------+-------+ 3 rows in set (0.00 sec)
以上结果表明外键的引入有效的保存了数据表的完整型。
？？？？创建表之后再创建外键如何操作
mysql> create table order1(oid int(10) auto_increment,uid int(11) default ‘0‘,username varchar(18),money int(11),primary key(oid),index(uid)); mysql> alter table order1 add foreign key(uid) references USER(uid) on delete cascade on update cascade; Query OK, 0 rows affected (0.26 sec) Records: 0 Duplicates: 0 Warnings: 0
自定义外键名：
mysql> alter table order1 add constraint `fk_name` foreign key(uid) references USER(uid) on delete cascade on update cascade; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0
删除外键：
mysql> alter table order1 drop foreign key order1_ibfk_1;
查看外键：
mysql> show create table 订单\G; *************************** 1. row *************************** Table: 订单 Create Table: CREATE TABLE `订单` ( `oid` int(10) NOT NULL AUTO_INCREMENT, `uid` int(10) NOT NULL, `goods` varchar(48) NOT NULL, `gid` int(18) NOT NULL, `money` int(10) NOT NULL, PRIMARY KEY (`oid`), KEY `g_name` (`goods`), KEY `order_f_key` (`uid`), CONSTRAINT `订单_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `USER` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified
mysql>
六、视图：
mysql> create view bc as select b.bName,b.price,c.bTypeName from books as b left join category as c on b.bTypeId=c.bTypeId;
查看创建信息
mysql> show create view bc\G *************************** 1. row *************************** View: bc Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bc` AS select `b`.`bName` AS `bName`,`b`.`price` AS `price`,`c`.`bTypeName` AS `bTypeName` from (`books` `b` left join `category` `c` on((`b`.`bTypeId` = `c`.`bTypeId`))) character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec) 查看 mysql> select * from bc\G;
本章内容主要介绍了mysql数据库的文件索引和一些字段约束的介绍，并在结尾引入MySQL视图的用法，下一张内容将对视图做更加明细的介绍和一些操作......
本文出自 “小熊运维” 博客，请务必保留此出处http://maoxiaoxiong.blog.51cto.com/11705634/1983637
6、MySQL字段约束介绍
标签：字段约束、索引 主键