MySQL学习笔记(5) 增删改查，高级查询，和索引

1. 背景

本文讲查询数据。

2.知识

基本的就是增删改查。一般说 CRUD, CRUD是指在做计算处理时的增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。

2.1 简单查询

SELECT 语句用于从数据库中检索查询。
示例：

select * from tb_table1 where name='li4'```

where 关键字后面跟查询条件
from 关键字后面跟表名或者视图名
order by 后跟排序的字段

2.2 插入

使用 insert into 语句可以插入一条数据记录。

insert into tb_table1 (name,deptId) values ('wang',0);

insert into 后跟表名，括号内写字段名
values 后的括号内写具体字段对应的值

2.2 更新

update 用于更新一条数据记录的值。

update tb_table1 set name='zhang33' where id=1;

update 后跟表名
set 后跟修改的字段和值
where 指定筛选条件

2.3 删除

delete 用于删除一条数据记录。

delete from tb_table1 where name='li4';

delete 后跟表名
where 指定筛选条件

2.4 高级查询

是否包含在内 --- 使用 IN 关键字的查询

select * from tb_table1 where id in (1,3,4);
select * from tb_table1 where name in ('wang','zhang33');

范围区间查询 --- 使用 BETWEEN AND 关键字的查询

select * from tb_table1 where id between 2 and 4;

字符串模糊搜索 --- 使用 LIKE 关键字的查询

select * from tb_table1 where name like 'zh%'

% 百分号是通配符，这里表示 zh 开头的都查询出来。

查看空值（NULL） --- 使用 IS NULL 关键字的查询

select * from tb_table1 where salary is NULL;  # 正确
select * from tb_table1 where salary = NULL; # 错误的，查不到结果。

多条件查询 --- 使用 AND 、OR关键字的查询

select * from tb_table1 where deptId=0 and salary is null;

多字段排序 -- Order by 后使用多个字段

select * from tb_table1 order by name, deptId;

分组 -- 使用 group by

select count(*) from tb_table1 group by class;
select count(*),class from tb_table1 group by class;

分组后再过滤 -- 在 group by 中使用 having

select count(*),class from tb_table1 group by class having class = 1;

分页查询 -- 使用 LIMIT

mysql> select * from tb_table1 limit 4,2;

LIMIT 后第一个数字指跳过多少行。
LIMIT 后逗号后的数字指取多少行。

计数，求和，平均，取最大最小值 -- 使用聚合函数

select count(deptId),class from tb_table1 group by class;
select sum(deptId),class from tb_table1 group by class;
select avg(deptId),class from tb_table1 group by class;
select min(Id),class from tb_table1 group by class;
select max(Id),class from tb_table1 group by class;

连接查询：内连接，左连接，右连接

# 内连接 inner join  
select * from tb_table1 as t inner join account as a on t.id = a.userId;
# 左连接
select * from tb_table1 as t left join account as a on t.id = a.userId;
# 右连接
select * from tb_table1 as t right join account as a on t.id = a.userId;

子查询， ANY SOME IN 等

select * from tb_table1 where id IN (select userId from account WHERE money>100)

合并查询 -- 使用 UNION

select * from tb_table1 where deptId =1 union select * from tb_table1 where deptId =2;

正则表达式查询 -- 使用 REGEXP

select * from tb_table1 WHERE name REGEXP '^z';

3. 索引

3.1 概念

索引就像是一本书前面的目录，能加快数据库的查询速度。
它是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

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

其中MySQL中的索引的存储类型有两种：BTREE、HASH。也就是用树或者Hash值来存储该字段,具体又和标的存储引擎有关。
MyISAM 和 InnoDB 存储引擎只支持 BTREE 索引。
MEMORY / HEAP 存储引擎可以支持 BTREE 和 HASH 索引。

不使用索引的情况下进行检索时，需要遍历和读取整个表，是很耗时的操作。而有了索引后，MySQL 不在全部扫描，直接在索引里找，借助于索引特殊的数据结构（比如 BTREE）可以快速定位这一行数据的位置。

索引的分类：
普通索引和唯一索引

普通索引：是MySQL的基本索引类型，允许重复和空值。
唯一索引：值必须是唯一的，可以空值但不能重复。即使是组合索引也必须唯一。
主键索引：是一种特殊的唯一索引，不能有空值。

单列索引和组合索引

单列索引：一个索引仅包含一个列的索引。
组合索引：由多个字段组合创建的索引。注意在查询条件中使用了左边的字段时，索引才被使用。

全文索引
全文索引（ FULLTEXT），在创建了全文索引的列上支持值的全文检索。它可以在 CHAR, VARCHAR 或者 TEXT 类型的列上创建。
注意：只有 MyISAM 引擎的表才能创建全文索引

3.2 创建索引

创建索引的三个方法：

1. 创建表时即创建索引
1. 在已存在的表上，使用 “ALTER TABLE” 关键字创建索引
1. 在已存在的表上，使用 “CREATE INDEX” 关键字创建索引

3.2.1 创建表时即创建索引

1、创建普通索引, 在建表时使用关键 “ INDEX ”。示例：

CREATE TABLE book
(
  id                INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  bookName          VARCHAR(255) NOT NULL ,
  authors           VARCHAR(255) NOT NULL,
  info              VARCHAR(255) NOT NULL,
  comment           VARCHAR(255) NOT NULL,
  year_publication  YEAR NOT NULL,
  INDEX(year_publication)
);

2、创建唯一索引, 在建表时使用关键 “ UNIQUE INDEX ”。示例：

CREATE TABLE table1 
(
  id  INT NOT NULL,
  name varchar(255) NOT NULL,
  UNIQUE INDEX TheUniqueIdx1(id)
);

3、创建单列索引：

CREATE TABLE table2 
(
  id  INT NOT NULL,
  name varchar(255) NOT NULL,
  INDEX TheSingleIdx1(name(20))
);
#注意，指定了索引长度 20

4、创建组合索引：

CREATE TABLE table5
(
  id  INT NOT NULL,
  name varchar(255) NOT NULL,
  age INT NOT NULL,
  INDEX TheMultiIdx2(id,name,age)
);

5、创建全文索引，使用关键字 " FULLTEXT INDEX "。只有 MyISAM 存储引擎才支持全文索引，且仅可以为 CHAR, VARCHAR, 和 TEXT 列创建全文索引。

CREATE TABLE table7
(
  id    INT NOT NULL,
  info  VARCHAR(255) NOT NULL,
  FULLTEXT INDEX TheFulltextIdx(info)
) ENGINE=MyISAM

6、创建空间索引，使用 “ SPATIAL INDEX ” 关键字。它作用于字段类型为 GEOMETRY 上。

CREATE TABLE table8
(
  id    INT NOT NULL,
  poi   GEOMETRY NOT NULL,
  SPATIAL INDEX TheSpatialIdx(poi)
) ENGINE=MyISAM

3.2.2 使用 “ALTER TABLE” 关键字在已存在的表上创建索引

和建表时类似，示例：

# 普通索引
ALTER TABLE book ADD INDEX TheIdx1(bookName);
# 唯一索引
ALTER TABLE book ADD UNIQUE INDEX TheIdx2(id);
# 组合索引
ALTER TABLE book ADD UNIQUE INDEX TheIdx3(id,authors);

3.2.3 使用 “CREATE INDEX” 关键字在已存在的表上创建索引

CREATE INDEX 其实等效于 ALTER TABLE，在 MySQL中 CREATE INDEX 被映射到一个 ALTER TABLE 语句上。示例：

# 普通索引
CREATE INDEX BkIndex11 ON book(bookName);
#  唯一索引
CREATE UNIQUE INDEX BkIndex12 ON book(id);
# 组合索引
CREATE UNIQUE INDEX BkIndex13 ON book(authors,info);

3.3 删除索引

在 MySQL 中可以使用 ALTER TABLE 或者 DROP INDEX 语句来删除一个索引。

两种方法是等效的，DROP INDEX 在内部被映射到一个 ALTER TABLE 上。

# 删除一个索引
ALTER TABLE book  DROP INDEX TheIdx1;
# 删除一个索引
DROP INDEX TheIdx2 ON book;

3.4 扩展知识

聚簇索引和非聚簇索引
聚簇索引并不是一种独特的索引类型，而是一种数据存储方式。

即按照索引的存储方式分类：

聚簇索引 (Clustered Index)
非聚簇索引 (Non- Clustered Index)，又叫二级索引（secondary index ）

简单说就是：

聚簇索引中索引的顺序就是数据的物理存储顺序；
而非聚簇索引的索引顺序与数据物理排列顺序无关。

InnoDB 引擎是按 B+TREE 结构存储的
InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放了整张表的行记录数据,也将聚集索引的叶子节点称为数据页。

Innobd中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。

非聚簇索引（辅助索引) 是在聚簇索引之上创建的索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

这两种索引内部都是B+树，聚簇索引的叶子节点存放着一整行的数据。而非聚簇索引存放的是主键，要定位到数据记录行还需要通过主键再到B+树上检索一次。

Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引。

4. 扩展

EXPLAIN 关键字，用于获取查询执行计划（即 MySQL 如何执行查询的说明。

EXPLAIN 在对SQL优化分析时很有用，我们可以用 explain 这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

比如：

EXPLAIN select * from book where year_publication= 1990 \G;

# 执行后：
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

参考：https://dev.mysql.com/doc/refman/8.0/en/explain.html

END

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 201,552评论 5赞 474
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 84,666评论 2赞 377
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 148,519评论 0赞 334
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,180评论 1赞 272
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,205评论 5赞 363
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,344评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,781评论 3赞 393
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,449评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,635评论 1赞 295
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,467评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,515评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,217评论 3赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,775评论 3赞 303
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,851评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,084评论 1赞 258
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,637评论 2赞 348
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,204评论 2赞 341