Day6 | 数据库操作-索引补充、非空表达式、CASE WHEN函数

今天药忘吃喽~ 2023-02-20 14:57 27阅读 0赞

![format_png][]

# 背景介绍    #

> 本篇针对索引进行展开介绍，以及补充对非空表达式和CASE WHEN函数的介绍。

# 索引 #

## 复习一下 ##

![format_png 1][]

## 唯一索引&主键索引 ##

唯一索引要求作为索引的字段列值唯一；主键索引是特殊的唯一索引，要求作为索引的字段唯一且不为空。

案例：对用户列表创建主键索引和唯一索引。

alter table 用户列表 add PRIMARY key (编号);
    
    create unique index i1 on 用户列表(编号);

![format_png 2][]

使用explain查询来查看索引的使用情况，当使用“编号”列查询时，type为const，同时使用了唯一索引i1和主键索引primary，而使用其他字段查询时则未使用到索引：

![format_png 3][]

## 组合索引 ##

组合索引即多列索引，用多个字段创建索引。

最左原则：创建组合索引(a,b,c)后，可以支持a|(a,b)|(a,b,c)三种索引，但不支持(b,c)。

案例：组合索引的创建

create index i2 on 用户列表(用户名,公司,性别);

![format_png 4][]

## 全文索引 ##

全文索引只为CHAR、VARCHAR、TEXT列服务。

创建数据表时添加全文索引：

CREATE TABLE table_name(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(255),
    FULLTEXT INDEX index_name(name,info)
    );

在现有数据表中添加全文索引：

ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column1,column2,...);

删除全文索引：

DROP INDEX index_name ON table_name;
    或
    ALTER TABLE table_name DROP INDEX index_name;

全文索引的使用：

select * from table_name where MATCH (column_name) AGAINST ('关键字')

**注意：**在使用全文搜索时，需要借助 MATCH 函数，搜索的关键字默认至少要4个字符，如果搜索的关键字太短就会被忽略掉。

**案例：**

1、在用户列表中添加全文索引：

![format_png 5][]

2、删除全文索引：

![format_png 6][]

3、使用全文索引：

注意当关键字小于4个字符时，未查询到相应结果。

![format_png 7][]

# 非空表达式：COALESCE #

非空表达式coalesce()，其作用是返回括号内参数中的第一个非空表达式，顺序为括号内从左向右依次类推。若括号内所有参数均为NULL，则返回NULL值。

coalesce括号内参数只有两个时，相当于IFNULL函数。

SELECT COALESCE(column_name1,column_name2,自定义值,...);

**案例：**

原数据失效时间列为null值

![format_png 8][]

简单查询 VS coalesce查询

![format_png 9][]

# CASE WHEN查询 #

case when函数可以有简单函数和搜索函数两种用法，结构大致相同：

case column_name when 条件/取值 then 结果1 else 结果2 end

case when函数可以与聚合函数结合使用，在搜索函数中when后可以使用and连接多个条件。

**案例：**

简单查询

![format_png 10][]

搜索查询（用CASE WHEN实现数据透视）

![format_png 11][]

# 今日小练习 #

练习一：关于索引的选择题

**Q1:** 下面创建索引的语句正确的是（多选）：

A. CREATE INDEX T1\_ind1,T1\_ind2 ON T1(f1,f2).

B. CREATE INDEX T1\_ind ON T1(f1 ASC,f2);

C. CREATE UNIQUE INDEX T1\_ind ON T1(f1, f2);

D. CREATE INDEX T1\_ind ON T1(f1);

**Q2:** UNIQUE惟一索引的作用是（单选）：

A．保证各行在该索引上的值都不得重复

B．保证各行在该索引上的值不得为NULL

C．保证参加惟一索引的各列，不得再参加其他的索引

D．保证惟一索引不能被删除

练习二：MYSQL实际操作（做完就是case when最强者！）

继续我们熟悉的练习场景：学生、课程、教师、成绩表。*公众号后台回复“DML练习”，获取练习题所需源数据。*

今日练习如下：

**Q3:** 查询所有学生在每门课程中的分数、以及每个学生的总分

**Q4:** 查询各学生的年龄（Tips: 可使用NOW()函数得到当前查询日期与时间，使用DATE\_FORMAT()函数对日期格式进行加工）

**Q5:** （附加题）查询各门课程的课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率注：及格为>=60，中等为70-80，优良为80-90，优秀为>=90

还未构造练习场景的同学，和未完成作业的同学，你真的需要好好温习课程的历史文章内容了哦！坚持，加油鸭！

![format_png 12][]

每一个在看都是一种支持![format_png 13][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9xaWFTbDNSVDFSYzFWVTREQk8xanBxQjZCUGNvaHh0SkFZUVBBUGQ0YXJXSkdLdUNiTlBnN2cwSWlhTWJOdzhQZ2NjaWNqVUxRVk5hTDgzVVNLTmVrcFJpYWcvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dmpGSE5ncHhybVg4QnI1VDE5cWZwanVDS0F3T01Oc3NmbXFpYjhBTlNjejgzMnpCeDJuNjhNVEEvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dlVmeVVQOGI3TjBnUEtaU0g0eWpNVzRmaWNrU1BFYnhyenhuRmJoTFFneERPVEE5UnpyaEs0cmcvNjQw?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dlJTWGNFNjA1aWNFWXFxZFRTN0JsMWJDMU5VVzJ0cnZoMEhLNkEzbEcwbnFVTkc1RUlNTlFpYXJnLzY0MA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dllxaWFubkFJd3lqU2pSUHNHemFjeGtDc0VUeXY0NFFaSG1VaWJqNDRQVUd6cngyVzVaNUJObzhnLzY0MA?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dmljN1lZM2RKT0ptbkN2ZGliUHd6dTN6QTNYRjdRYXlubHhCRWRHaDIzOWljSHVJRkVKb0xQRG1ldy82NDA?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dkRlb0ttOFVFN1h4Uk01WHFoZjdzcE45enpHaDFLa3BxcTBVcWpLa2lhU2F6ak5vbnhKeGljM0JBLzY0MA?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dmdtNmliNjd1UzVyaWFnMmgyOTF6U1gxZ2lhRWljMnhiQVR0N2NxNHBEcXY3VEkzbXZPcUVyWUV5QncvNjQw?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dklXYk9BMmtTQjhTdGlhWVVHaWM1V29EUVNYTk0yS1dOdzdwd1JZWXg5OWNTejdRaDZuYXpMM09nLzY0MA?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dk4ydHhyOHZpY2xZaWJ5MDlwTkZXOGVWRWhIVzVWaWMwa3ZmZllCTExiMVFFR2t3aWJodkFaWHJQUUEvNjQw?x-oss-process=image/format,png
[format_png 10]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4djl2akxDSWJpYzdPaWFjc0dRbUt1QzF4YkNlMldNSWs1U0hsem02bEhwcWZvN01pYnB5SVd3TUc3US82NDA?x-oss-process=image/format,png
[format_png 11]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dllUandRNEw1MWZ0Slh3U1JPd1FaTkRpY1o0Mlg2aFJ0VVpPRE5qaWNYVmlhYjk5YlJ3WHkyR1Zidy82NDA?x-oss-process=image/format,png
[format_png 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzJWQ2oyRmhpY2ljYm9maWFoNGFpY2xQM291aWMydnM2UXdaWldKaHNvS2lhOVY1dkNGTWljV0hPa25jamRPZUZOWklYWEJpYVF6dmN4TmljV3ZSd3cvNjQw?x-oss-process=image/format,png
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBzSlVyWmlhTzNqNVZ6cnFEZ3RKaWN4dkpXbmJ1YzJLUzUyRUN3TlRoU3VGOFNsVlNRaWNMaWFvaWJyRkRoUkVPdXlUbDRDUVhoT0ZNbndiQS82NDA?x-oss-process=image/format,png