MySQL 5.7（五）【插入、更新、删除数据 + 索引】

最新推荐文章于 2024-05-05 03:50:35 发布

Convallaria@GJH

最新推荐文章于 2024-05-05 03:50:35 发布

阅读量916

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/Nigori_gjh/article/details/106798086

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

第五篇

插入、更新、删除数据 + 索引

插入、更新、删除数据 + 索引

一、插入、更新、删除数据

（一）插入数据

1、直接插入数据到数据表中

【要保证值的顺序与列字段的顺序相同】

在插入多条数据组时，若某单行有异常，会有如下信息：
Records：表明插入的记录条数。
Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
Warnings：表明有问题的数据值，例如发生数据类型转换。

insert into table_name (<字段1>,<字段2>,<字段3>,<字段4>,<字段5>)

value (值1，值2，值3，值4，值5),(值1，值2，值3，值4，值5),(值1，值2，值3，值4，值5);

2、将查询结果插入数据表中

MySQL 不关心 SELECT 返回的列名，它根据列的位置进行插入，SELECT 的第1列对应待插入表的第1列，第2列对应待插入表的第2列，等等。即使不同结果的表之间也可以方便地转移数据。

insert into table_a (<字段1>,<字段2>,<字段3>)

select (<字段x>,<字段y>,<字段z>) from table_b where <条件表达式>;

（二）、更新数据

更新表中数据

update table_name

set <字段1> = 值1,<字段2> = 值2,<字段3> = 值3,<字段4> = 值4

where <条件表达式>;

若不加 where 子句，MySQL 将更新表中的所有行。

（三）、删除数据

删除表中数据

delete from table_name [where <条件表达式>];

若不加 where 子句，删除表中所有数据。【注：truncate table <table_name>; 直接删除表，故执行速度快。】

二、索引

（一）索引简介

1、含义与特点

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。

索引是在存储引擎中实现的，根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

MySQL中索引的存储类型有两种：BTREE 和 HASH，具体和表的存储引擎相关；

优点：

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度（主要的原因）。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

缺点：

时间方面：创建索引和维护索引要耗费时间，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

2、索引分类

（1）普通索引和唯一索引

普通索引：基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引：索引列的值必须唯一，但允许有空值。若为组合索引，则列值组合必须唯一。主键索引是特殊的唯一索引，不允许有空值。

（2）单列索引和组合索引

单列索引：一个索引只包含单个列，一个表可以有多个单列索引。
组合索引：在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

（3）全文索引

全文索引：类型为 FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在 CHAR、VARCHAR 或 TEXT 类型的列上创建。只有 MyISAM 引擎支持全文索引。

（4）空间索引

空间索引：对空间数据类型的字段建立索引。
- MySQL中的空间数据类型有4种
  - geometry、point、linestring 和 polygon
  - 使用 spatial 关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。
  - 创建空间索引的列，必须将其声明为 not null，仅 MyISAM 引擎支持空间索引。

3、设计索引的规则

1）最左前缀匹配原则

2）较频繁作为查询条件的字段要创建索引，而更新频繁字段不建议创建索引

3）数据表比较小的时候不建议创建索引。

4）扩展索引，不要新建索引。

5）定义有外键的数据列一定要建立索引。

6）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

7）对于定义为text、image和bit的数据类型的列不要建立索引。

（二）索引创建

1、创建表时创建索引

create table table_name [column_name data_type]

[unique|fulltext|spatial] [index|key] [index_name] (column_name [length]) [asc|desc]

UNIQUE（唯一索引） FULLTEXT（全文索引） SPATIAL（空间索引）
INDEX 与 KEY 意义相同，用来指定创建索引
column_ name 创建索引的字段列；
index_ name 索引名称，若不指定，MySQL默认column_ name 为索引名；
length 索引的长度，只有字符串类型的字段才能指定索引长度；
ASC 或 DESC 升序或降序的索引值存储。

（1）创建普通索引

只是加快对数据的访问速度。

mysql> create table book(
    -> book_id int(4) not null,
    -> book_author varchar(255) not null,
    -> book_name varchar(255) not null,
    -> book_info varchar(255) null,
    -> book_comment varchar(255) null,
    -> index(book_name)
    -> );
Query OK, 0 rows affected (0.78 sec)

查看表结构：

| book  | CREATE TABLE `book` (
  `book_id` int(4) NOT NULL,
  `book_author` varchar(255) NOT NULL,
  `book_name` varchar(255) NOT NULL,
  `book_info` varchar(255) DEFAULT NULL,
  `book_comment` varchar(255) DEFAULT NULL,
  KEY `book_name` (`book_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

使用 explain 语句查看索引使用状况:

mysql> explain select * from book where book_name='软件开发';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | book  | NULL       | ref  | book_name     | book_name | 767     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

(1 ) select_type 指定所使用的 SELECT 查询类型,这里值为 SIMPLE，表示简单的 SELECT，不使用UNION或子查询。其他可能的取值有: PRIMARY、UNION、SUBQUERY 等。

(2) table 指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。

(3) type 指定了本数据表与其他数据表之间的关联关系，可能的取值有system、const、eq_ ref、ref、 range、 index 和 All。

(4) possible_ keys 给出了 MySQL 在搜索数据记录时可选用的各个索引。

(5) key 是 MySQL 实际选用的索引。

(6) key_len 给出索引按字节计算的长度，key_ len 数值越小，表示越快。

(7) ref 给出了关联关系中另一个数据表里的数据列的名字。

(8) rows 是 MySQL 在执行这个查询时预计会从这个数据表里读出的数据行的个数。

(9) extra 提供了与关联操作有关的信息。

（2）创建唯一索引

mysql> create table tab_01(
    -> id int not null,
    -> name char(30) not null,
    -> unique index unique_id(id)
    -> );
Query OK, 0 rows affected (0.27 sec)

查看表结构：

| tab_01 | CREATE TABLE `tab_01` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

（3）创建单例索引

mysql> create table tab_02(
    -> id int not null,
    -> name char(50) null,
    -> index single_name(name(20))
    -> );
Query OK, 0 rows affected (0.34 sec)

查看表结构：索引名为 singleidx，索引长度为20。

| tab_02 | CREATE TABLE `tab_02` (
  `id` int(11) NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `single_name` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

（4）创建组合索引

create table tab_03(
    id int not null,
    name varchar(255) not null,
    age int(4) not null,
    info varchar(255),
    index multiidx(id,name,age(4))
    );

查看表结构：

| tab_03 | CREATE TABLE `tab_03` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(4) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `multiidx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

组合索引使用时是遵从“最左前缀”：利用索引中最左边的列集来匹配行。

最左匹配原则：一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

例如：由 id、name 和 age 3个字段构成的索引，索引行中按 id/name/age 的顺序存放，索引可以搜索下面字段组合: (id, name, age)、(id, name)或者 id。

（5）创建全文索引

全文索引非常适合于大型数据集。表的存储引擎为 MyISAM。

create table tab_04(
    id int not null,
    name varchar(255) not null,
    age int(4) not null,
    info varchar(255),
    fulltext index fulltxtidx(info)
    )engine=MyISAM;

查看表结构：

| tab_04 | CREATE TABLE `tab_04` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(4) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `fulltxtidx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

（6）创建空间索引

注意：指定空间类型字段值非空约束，并且表的存储引擎为 MyISAM。

create table tab_05(
    g geometry not null,
		spatial index spatidx(g)
    )engine=MyISAM;

查看表结构：

| tab_05 | CREATE TABLE `tab_05` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

2、在已经存在的表上创建索引

（1）使用 `alter table` 语句创建索引

语法结构：

alter table table_name 

add [unique|fulltext|spatial] [index|key] [index_name] (col_name [length],...) [asc|desc]

ADD 表示向表中添加索引。

使用 show index 查看已经创建的索引

mysql> show index from book;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY   |            1 | book_id     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | book_name |            1 | book_name   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

各参数意义如下：
在这里插入图片描述
普通索引，单例索引

alter table book add index book_authorid (book_author(30));

唯一索引

alter table book add unique index book_authorid (book_author(30));

组合索引

alter table book add unique index book_autandinfoid (book_author(30)，info(50));

全文索引（ MyISAM ）

alter table tab_04 add fulltext index fulltxtidx(info);

空间索引（ MyISAM ）

alter table tab_05 add spatial index spatidx(g);

（2）使用 `create index` 创建索引（映射）

不能创建PRIMARY KEY索引

语法结构：

create [unique|fulltext|spatial] index index_name

on table_name (col_name [length],...) [asc|desc]

普通索引

create index book_authorid on book (book_author);

唯一索引

create unique index book_authorid on book (book_author);

单例索引

create index book_authorid on book (book_author(30));

组合索引

create index book_autandinfoid on book (book_author(30)，info(50));

全文索引（ MyISAM ）

create fulltext index fulltxtidx on tab_04 (info);

空间索引（ MyISAM ）

create spatial index spatidx on tab_05 (g);

（三）索引删除

1、使用 `alter table table_name drop index index_name;` 删除索引

添加 auto_increment 约束字段的唯一索引不能被删除。

2、使用 `drop index index_name on table_name;` 删除索引

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引），需要取消自增长再行删除

alter table user_index
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

在这里插入图片描述

Convallaria@GJH

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL 5.7（五）【插入、更新、删除数据 + 索引】

第五篇插入、更新、删除数据 + 索引一、插入、更新、删除数据（一）插入数据1、直接插入数据到数据表中2、将查询结果插入数据表中（二）、更新数据更新表中数据（三）、删除数据删除表中数据二、索引（一）索引简介1、含义与特点2、索引分类3、设计索引的规则（二）索引创建1、创建表时创建索引（1）创建普通索引2、插入、更新、删除数据 + 索引一、插入、更新、删除数据（一）插入数据1、直接插入数据到数据表中【要保证值的顺序与列字段的顺序相同】在插入多条数据组时，若某单行有异常，会有如下信息：Records
复制链接

扫一扫