MySql必知必会11-20章总结

11.使用数据处理函数

常用的文本处理函数

image.png
image.png

对Soundex()做一个解释：此函数是对任意文本串转化成其发音的算法。所以只要是发音类似的结果都会被查询出来。

-- 例如学生名字为 Y.Lee录入的时候录入错误，录成 Y.Lie 这时候如下查找会查找不到。
select name from student where name = 'Y.Lee';
#如下使用Soundex()函数就能查找出来
select name from student where Soundex(name) = Soundex('Y.Lee');

日期和时间处理函数

image.png

注意：当使用where条件搜索日期为某一天的记录时候，通常这样写...where date = '2020-02-02';但是这种写法并不严谨，因为如果当数据库date类型为datetime的时候，表中数据会有时间值00:00:00则此时上面的写法就不严谨了，需要用时间处理函数取日期部分，我们可以这样写：

... where Date(date) = '2020-02-02';

例如：我们需要获取2020年2月份的所有订单？如下：

-- 第一种解法：需要知道2月份有多少天，比如2020年闰年29天
...where Date(order_date) between '2020-02-01'  and '2020-02-29';

上面用到between关键字，可以查询2月1到29号的2月份区间的订单，缺点是必须知道2月有多少天，所以我们可以如下写，根本不用关心2月份有多少天。

-- 注意用到了Year()函数和Month()函数
...where Year(order_date) = 2020 and Month(order_date) = 2;

数值处理函数

image.png

12.汇总数据

聚合函数

image.png

注意： AVG()函数只能求特定一列的平均值并且会忽略列值为NULL的行

COUNT()返回总行数，包含NULL值； COUNT(列名)返回总行数，不含NULL值； DISTINCT COUNT()返回总行数，去除重复且不含NULL值,COUNT(1)和count(*)返回结果一致。

Max()函数、Min()函数、Sum()函数都忽略列值为NULL的行

聚集不同值

正如标题字面意思聚集不同值即为把distinct关键字用于聚集函数

-- 求不同价格商品的平均值
select avg(distinct prod_price) from products;

13.分组数据

关于Group by 分组的一些规定：
①group by 字句可以包含任意数量的列，这使得能对分组进行嵌套，为分组提供更细致的控制。
②如果在Group By子句中嵌套了分组,数据将在最后指定的分组上进行汇总。换句话说,在建立分组时，指定的所有列都一起计算(不能从个别的列中取回数据)。
③Group By子句中列出的每一列都必须是检索列(或者有效的表达式,注意不能是聚集函数)。如果在SELECT中使用了检索列(或者表达式),则在Group By子句中使用相同的表达式,不能使用别名。
④除聚集计算语句外,SELECT语句中的每一列都必须在Group By中给出。
⑤如果分组列中包含具有Null值的行,则Null将作为一个分组返回,如果列中有多行Null，他们将作为一个分组返回
⑥Group By必须出现在Where子句之后,Order By子句之前。

WITH ROLLUP 关键字进行分组汇总

使用with ROLLUP关键字，可以得到每个分组以及每个分组汇总级别(针对每个分组)的值。如下：

SELECT
    `name`,
    count( * ) AS num 
FROM
    student 
GROUP BY
    `name` WITH ROLLUP;

image.png

对分组进行过滤 Having

having是对分组后的数据进行过滤，而where是对分组之前的数据进行过滤。

SELECT
    `name`,
    count( * ) AS num 
FROM
    student 
WHERE
    `name` <> 'xm' 
GROUP BY
`name` 
HAVING
    count( * ) > 1;

where过滤掉name='xm'的数据，然后having再过滤掉出满足数量大于1的分组。

注意在使用group by进行分组的时候，也使用order by进行排序，这是保证数据正确排序的唯一方法，千万不要仅仅依赖group by排序数据。

小结：列出select子句的顺序：
select
from
where
group by
having
order by
limit

14.使用子查询

例如：从customers客户表中检索客户列表
对于检索出的每个客户，统计其在orders订单表中的订单数

select `name`，state,
(select count(*) from orders where orders.cust_id = customers.cust_id) as orders
from customers 
order by `name`;

15.联表查询

创建联结

创建联结非常简单，规定要联结的表以及他们如何关联即可。例如：

SELECT
    t1.id,
    t1.`name` className,
    t2.`name` AS studentName,
    t2.score 
FROM
    t_class t1,
    student t2 
WHERE
    t1.id = t2.class_id 
ORDER BY
    t1.id,
    t2.score

image.png

注意：联结表查询的联结条件很重要，上面这段sql的联结条件是where 班级表的id = 学生表的班级id
如果不写联结条件的话，就会生成笛卡尔积。
笛卡尔积就是联结的两张表，用第一张表每一行去联结第二张表每一行，因为没有联结条件，所以就会生成笛卡尔积
例如：上面的联结查询如果不写where联结条件的话,就会得到笛卡尔积

SELECT
    t1.id,
    t1.`name` className,
    t2.`name` AS studentName,
    t2.score 
FROM
    t_class t1,
    student t2 
ORDER BY
    t1.id,
    t2.score

内联

目前为止所用的联结称为等值联结，它基于两个表之间的相等进行联结，这种联结也称为内联，对于这种联结可以使用一种新的推荐语法来写，就是inner join ... on

-- 两个表的联结条件是ON
SELECT
    t1.id,
    t1.`name` className,
    t2.`name` AS studentName,
    t2.score 
FROM
    t_class t1
    INNER JOIN student t2 ON t1.id = t2.class_id 
ORDER BY
    t1.id,
    t2.score

联结多张表

注意：mysql对于一条select 语句中可以联结的表的数量没有限制，但是联结的表越多，性能下降越厉害，所以我们应该考虑必要的表进行联结，尽量减少联结的表的个数，阿里巴巴java开发手册中也写到，超过3张表禁止join.

16.创建高级联结

使用别名是一种很好的习惯（表别名、列别名）

使用不同类型的联结

1.自联结

例如：你的id为a的货品有质量问题，所以你想知道a货品的供货商的其他商品是否也有问题，查询出货品a所属供货商的所有货品。

-- 使用子查询
SELECT
    t1.prod_id,
    t1.prod_name 
FROM
    products t1 
WHERE
    t1.ghs_id = ( SELECT t2.ghs.id FROM products t2 WHERE t2.prod_id = 'a'; )

-- 使用自联结查询
SELECT
    t1.prod_id,
    t1.prod_name 
FROM
    products t1,
    products t2 
WHERE
    t1.ghs_id = t2.ghs_id 
    AND t1.prod_id = 'a';

使用自联结而不是子查询？自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，虽然查询结果是相同的，但是有时候处理联结的速度比子查询快得多。

2.外联结(左外联结、右外联结)

在使用outer join语法时候必须指定left 或者 right 关键字来确定包括其所有行的表，实际上外连接经常被我们简写成left join... on和right join...on

小结

①注意所使用的联结类型。一般我们使用内部联结，但是使用外联结也是有效的。
②保证使用正确的联结条件，否则将返回不正确的数据。
③应该总是提供联结条件on....不然会得出笛卡尔积
④在一个联结中可以包含多个表，甚至对于每个联结可以使用不同的联结类型，虽然是合法的，但是应该在一起测试前，分别测试每个联结，这将使故障排除更简单。

17.组合查询UNION

有两种情况需要用到组合查询：
①在单个查询中从不同的表返回类似结构的数据
②对单个表执行多个查询，按单个查询返回结果

union相当于拼接两个select的结果，每一个where ....or ...都能拆成union组合查询

union使用规则：
①union必须由两条或以上的select语句组成
②union中的每个查询必须包含相同的列、表达式或聚集函数（注意：列的顺序也必须相同）
③列数据类型必须兼容，类型不必完全相同，但是必须DBMS可以隐式转换的类型。

UNION默认取消重复行（保留一条），如果返回所有即使重复可以用UNION ALL

union排序只需要在最后一个select上写order by 即可对整体union后的结果进行排序。

18.全文本搜索

简介：早期mysql只有MyISAM搜索引擎支持全文本搜索，而InnoDB搜索引擎不支持
-------mysql 5.6.4之后InnoDB也开始支持全文搜索
-------在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引
-------从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。
-------我这里使用的版本是mysql5.7.28

-- 查询表状态，搜索引擎一般默认Innodb
show table status;

-- 修改表address的搜索引擎为MyISAM或InnoDB
ALTER TABLE `address` ENGINE=INNODB;

-- 查询mysql是否开启全文搜索,ft_min_word_len=1为开启
SHOW VARIABLES LIKE 'ft%';

-- 查询mysql中文分词的词语最少，默认ngram_token_size = 2，即一个中文词语两个字
-- 如果需要搜索单字，就要把ngram_token_size设置为1
SHOW VARIABLES LIKE 'ngram%';

#查询address表的索引
show index from address;

-- 给address表的address字段添加全文索引，使用中文分词ngram
CREATE FULLTEXT INDEX ft_address ON `address` ( `address` ) WITH PARSER ngram;

-- 在100万数据里全文检索，中文分词搜索 青州
SELECT
    *
FROM
    address t 
WHERE
    MATCH ( address ) Against ( "青州" );

image.png

使用查询扩展

例如，我是用全文搜索not,得到如下一条结果

SELECT
    *
FROM
    address t 
WHERE
    MATCH ( address ) Against ( "not" );

image.png

使用查询扩展之后，可得到如下3条结果，第一行包括not所以等级最高，因为第二条和第三条都有第一条存在的rabbit所以也被搜索出来了。

SELECT
    *
FROM
    address t 
WHERE
    MATCH ( address ) Against ( "not" with query expansion);

image.png

布尔文本搜索

常用的全文检索模式有两种：
1、自然语言模式(NATURAL LANGUAGE MODE) ，
自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
2、BOOLEAN模式(BOOLEAN MODE)
BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

例如，使用布尔模式查询包括rabbit但是不能包括not的结果,只有两条，包含rabbit和not的那条被过滤掉了

SELECT
    *
FROM
    address t 
WHERE
    MATCH ( address ) Against ('+rabbit -not' IN BOOLEAN MODE);

image.png

'apple banana' 
无操作符，表示或，要么包含apple，要么包含banana

'+apple +juice'
必须同时包含两个词

'+apple macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性会更高。

'+apple -macintosh'
必须包含apple，同时不能包含macintosh。

'+apple ~macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性要比不包含macintosh的记录低。

'+apple +(>juice <pie)'
查询必须包含apple和juice或者apple和pie的记录，但是apple juice的相关性要比apple pie高。

'apple*'
查询包含以apple开头的单词的记录，如apple、apples、applet。

'"some words"'
使用双引号把要搜素的词括起来，效果类似于like '%some words%'，
例如“some words of wisdom”会被匹配到，而“some noise words”就不会被匹配。

image.png

小结：

①只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。
②全文索引只支持InnoDB和MyISAM引擎。
③MATCH (columnName) AGAINST ('keywords')。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询，就需要在title和body字段上分别创建新的全文索引。
④MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。
⑤如果要导入大数据集，使用先导入数据再在表上创建全文索引的方式要比先在表上创建全文索引再导入数据的方式快很多，所以全文索引是很影响TPS的。

参考

https://www.cnblogs.com/miracle-luna/p/11147859.html

19.插入数据

-- 给学生表插入一条记录，标准插入语句
INSERT INTO student (id,`name`,kemu,score,class_id) VALUES(11,'小明','体育',100,4);

注意：
①id自增的话可以省略
②表后边的明确的给出列名及对应的顺序是安全的做法，即使表结构发生变化，此insert语句依然能够正常工作。
③如果表的定义允许，可以insert语句中省略某些列，省略的列必须满足以下某个条件，（一、该列定义允许为NULL。二、在标的定义中给出了默认值）
④可以使用insert low_priority into 降低insert语句的优先级，也适用于update和delete

插入多个行

-- 注意：这种方法比一个一个insert快
INSERT INTO student ( id, `name`, kemu, score, class_id )
VALUES
    ( 12, '小a', '体育', 11, 1 ),
    ( 13, '小b', '体育', 22, 2 ),
    ( 14, '小c', '体育', 33, 3 );

20.更新和删除数据

-- 更改id=11的数据的name=小李
UPDATE student 
SET NAME = '小李' 
WHERE
    id = 11;

-- 删除第id=10的那一行
DELETE 
FROM
    student 
WHERE
    id = 10;

更快的删除

如果你想删除表中的所有数据，你可以使用Truncate Table,实际上他的意思是删除表，再重新创建一张表，而不是逐行删除表中的数据，所以速度较快。

TRUNCATE TABLE student;

删除表

DROP TABLE `t_class`;