【mysql】必知必会学习笔记

目录

• 基础语法
  • 检索数据
  • 过滤数据
  • 聚集函数
  • 其余函数
  • 做题中做错的题目
  • 子查询
  • 视图
  • 事务
    • ACID
    • 操作
    • 事务隔离级别
      • 模拟实验
• 性能优化
  • 优化维度
  • 数据库范式

基础语法

检索数据

记录生疏的知识点

1. 在生产环境下，应尽量避免使用select *
   原因：将所有的列都检索出来增加数据库的负担。增加数据库的网络传输量。在日常的工作中往往不需要全部的列，需要养成良好的习惯
2. 去重记录
   distinct 是对后面列名的组合进行去重

SELECT DISTINCT attack_range FROM heros # 只有近战和远程两个
SELECT DISTINCT attack_range FROM heros # 有69条记录了

3. 排序记录

• 可根据多个字段排序，从前到后，前一个相同了，根据后一个排序
• 可根据非选择字段排序，如

SELECT NAME FROM heros ORDER BY hp_5s_growth DESC

• 位置：放在sql语句最后 （sql语句整体每一个位置很重要，后面会讲到）

4. 约束返回的条件
   这个其实不生疏，但是在使用的时候要反复提醒自己去这样做。
   约束返回数量可以减少数据表的网络传输量，提高查询效率。
5. select 的执行顺序
   重要：图像化想象就是从中间回到头再转到尾巴。下面的这个例子可以很好理解

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1  注意是一个多表查询
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

6. count的讨论
   COUNT() = COUNT(1) > COUNT(字段)
   如果要统计某一个非空字段，则使用COUNT(字段)
   如果要统计COUNT()，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升

过滤数据

1. 比较运算符
   除了常规的大于、等于以及小于这些，还有between and，以及 is null
2. 逻辑运算符
   当 WHERE 子句中同时存在 OR 和 AND 的时候，AND 执行的优先级会更高，也就是说 SQL 会优先处理 AND 操作符，然后再处理 OR 操作符。
   除了常规的or，and等，还有in，not
3. 通配符过滤
   前面是条件已经的情况，当条件待定的需使用 like 结合 % 以及 _ 等通配符

SELECT NAME,birthdate FROM heros
WHERE NAME LIKE '%太%'
结果：东皇太一、太乙真人

在实际中，尽量少用通配符。使用like检索字段，即使该字段有索引，也有可能使得索引失效。比如 name like '%太%' 或这 '%颇' 都会进行全表扫描。当匹配条件第一个字符不为 % 时，则不会全表

tips：

1. 对日期类型数据进行检索，所以使用到了 DATE 函数，将字段 birthdate 转化为日期类型再进行比较。

比如：date(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'

2. 为了避免全表扫描，需要在where 以及 order by 涉及到字段添加索引

聚集函数

定义：对于一批次的数据进行处理，输出一个具体的值。（多个输入，单个输出）。比较复杂的情况下，会先进行筛选，再进行汇聚
分类：MAX、MIN、AVG、COUNT以及SUM

1. 位置：放在select语句中。
2. 会自动会null的字段过滤。并且MAX和MIN可以用在字符串，根据字符进行排序，以及在使用它们俩时，不需要再使用distinct
3. 重要：结合group by一起使用聚合函数

SELECT COUNT(*) AS num, role_main 
	FROM heros
	GROUP BY role_main
	HAVING num >= 10  # having 可以把select中的字段拿过来作为筛选的条件

3.1 字段为null也会被分成一个分组
3.2 where和having的区别
都是起到过滤的作用，只不过 WHERE 是用于数据行，而 HAVING 则作用于分组。 where是先对原始表中的数据进行过滤，having再在其基础之上对过滤后的数据进行分组。用 WHERE 进行数据量的过滤，用 GROUP BY 进行分组
例子：筛选最大生命值大于 6000 的英雄，按照主要定位、次要定位进行分组，并且显示分组中英雄数量大于 5 的分组，按照数量从高到低进行排序。

SELECT COUNT(*) AS num, role_main, role_assist
	FROM heros
	WHERE hp_max > 6000  # 先筛选数据表中的数据
	GROUP BY role_main, role_assist  # 根据上面筛选后的数据再进行分组
	HAVING num > 5
	ORDER BY num DESC

练习题
1.筛选最大生命值大于 6000 的英雄，按照主要定位进行分组，选择分组英雄数量大于 5 的分组，按照分组英雄数从高到低进行排序，并显示每个分组的英雄数量、主要定位和平均最大生命值。

SELECT COUNT(*) AS num, role_main, AVG(hp_max)
	FROM heros
        WHERE hp_max > 6000
	GROUP BY role_main
	HAVING num > 5
	ORDER BY num DESC

2.筛选最大生命值与最大法力值之和大于 7000 的英雄，按照攻击范围来进行分组，显示分组的英雄数量，以及分组英雄的最大生命值与法力值之和的平均值、最大值和最小值，并按照分组英雄数从高到低进行排序，其中聚集函数的结果包括小数点后两位。

SELECT COUNT(*) hero_nums, ROUND(AVG(hp_max + mp_max), 2) AS avg_hpmp, ROUND(MAX(hp_max + mp_max), 2) AS max_hpmp, ROUND(MIN(hp_max + mp_max), 2) AS min_hpmp
	FROM heros
	WHERE (hp_max + mp_max) > 7000
	GROUP BY attack_range
	ORDER BY hero_nums

其余函数

1. upper, lower
2. concat
3. substring
   注意这里索引是从1开始
4. date_format
   查询2020年1月份的所有订单，注意，这里没有限制具体的哪一天，要取出一整个月。

select order_num, order_date from Orders
    where date_format(order_date, '%Y-%m')='2020-01'
    order by order_date

做题中做错的题目

group by => 1
sub query
注意In和=的使用，前者是由于集合中包含了多个结果，而后者是确定的值
子查询的问题

子查询

1. 定义：嵌套在查询中的查询。以完成从查询结果集中再次进行查询，得到想要的结果。
2. 分类：关联子查询和非关联子查询 =》 是否执行多次

• 关联子查询
  获取结果，只执行一次，作为主查询条件
  例子：假设我们想要知道哪个球员的身高最高，最高身高是多少 (同一张表)

SELECT player_name, team_id, height FROM player
	WHERE height = (SELECT MAX(height)FROM player)

• 非关联子查询
  子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。
  例子：查找每个球队中大于平均身高的球员有哪些，并显示他们的球员姓名、身高以及所在球队 ID。

SELECT player_name, height, team_id 
	FROM player AS a 
	WHERE height > 
        (SELECT AVG(height) FROM player AS b 
          WHERE a.team_id = b.team_id)  ## 两张表有关联

3. EXISTS子查询
   例子：想要看出场过的球员都有哪些，并且显示他们的姓名、球员 ID 和球队 ID。

SELECT player_name, team_id, height FROM player AS a
	WHERE EXISTS (
		SELECT player_id FROM player_score AS b
		WHERE a.`player_id` = b.`player_id`
	)

4. 集合比较子查询
   4.1 IN

SELECT player_name, team_id, height FROM player AS a
	WHERE player_id IN  (
		SELECT player_id FROM player_score AS b
		WHERE a.`player_id` = b.`player_id`
	)

4.2 ANY ALL
必须与比较操作符一起使用
例子:如果我们想要查询球员表中，比印第安纳步行者（对应的 team_id 为 1002）中任何(所有)一个球员身高高的球员的信息，并且输出他们的球员 ID、球员姓名和球员身高

SELECT player_name FROM player
	WHERE height > ANY(SELECT height FROM player 
		WHERE team_id = 1002)

练习题：
得到场均得分大于 20 的球员。场均得分从 player_score 表中获取，同时你需要输出球员的 ID、球员姓名以及所在球队的 ID 信息。

SELECT player_name FROM player AS a
	WHERE EXISTS (
		SELECT player_id FROM player_score AS b
		WHERE a.`player_id` = b.`player_id` AND score > 20 )
	

视图

1. 定义：
   本身不具有信息的虚拟表，可以连接一个或者多个数据表。相当于是一张表或多张表的数据结果集
   好处 =》 简化查询，增加复用性
2. 操作
   2.1 创建

CREATE VIEW player_above_avg_height AS 
SELECT player_id, height, player_name FROM player
	WHERE height > (SELECT AVG(height) FROM player)

之后，就从player_above_avg_height可以查询到视图的结果集
注：可以在player_above_avg_height基础之上，嵌套创建试图
2.2 更新

alter view player_above_avg_height as 
select player_id, player_name, team_id, height 
from player
where height > (select avg(height) from player)

2.3 删除

DROP VIEW player_above_avg_height

3. 使用视图简化SQL操作
   3.1 使用视图完成复杂的连接

CREATE VIEW player_height_grade AS 
SELECT player_name, p.`height`, h.`height_level`
	FROM player AS p
	JOIN height_grades AS h
	ON p.`height` BETWEEN h.`height_lowest` AND h.`height_highest`

之后就可以在这个视图上查询

SELECT * FROM player_height_grade
	WHERE height BETWEEN 1.92 AND 2.11

3.2 利用视图对数据进行格式化

CREATE VIEW player_team AS 
select concat(p.`player_name`, '(', t.`team_name`, ')') as player_team
	from player as p 
	join team as t
	on p.`team_id` = t.`team_id`

4. 总结
   其实就是在select的结果集上，做一个封装，将其定义为一张虚拟表
   在这张表就可以完成查询的操作了

事务

定义：进行一次处理的基本单元，要么完全执行，要么都不执行。

ACID

1. 一致性：这里参考维基百科，当事务提交后，或者当事务发生回滚后，数据库的完整性约束不能被破坏。
2. 持久性：通过回滚日志和重做日志保证
   当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。

操作

1. 隐式提交和显式提交
   显示提交需要每次都手动commit
   在mysql中，是显示提交。可以通过以下命令定制

set autocommit = 0; // 关闭自动提交

2. 例子

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
BEGIN;  ## 第二个事务
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

事务隔离级别

出现的问题有：脏读、幻读以及不可重复读

1. 脏读：A还未提交事务，B就可以查看到A添加了记录 （读出到其他未提交事务的内容
2. 不可重复度：两次读取同一个记录，结果不一样 （内容变得不同
3. 幻读：A第一次查询得到N条数据，事务B增加M条数据，事务A查询时得到了N+M条诗句 （内容多出来
   对应的隔离级别有：Read uncommitted、Read committed、Repeatable read 以及 Serializable

模拟实验

首先，使用如下的命令创建实验条件

## 设置
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET autocommit = 0;

## 查看是否设置成功
SHOW VARIABLES LIKE 'tx_isolation';
SHOW VARIABLES LIKE 'autocommit';

性能优化

优化维度

1. 目标：提供相应速度，吞吐量
   where =》 用户反馈，日志分析，服务监控（cpu、io以及内存）
2. 数据库优化（不仅局限于sql优化了）

数据库范式

1. 定义：数据表中属性联系的合理化程序进行定义。可理解为表需要满足的某种设计标准的级别。高阶的范式一定满足低阶范式的要求。