2022.5.10学习MYSQL的查漏补缺笔记_order by 列名在引号里-CSDN博客

本文链接：https://blog.csdn.net/csdnpika/article/details/124692711

视频笔记：尚硅谷-15-列的别名_去重_NULL_DESC等操作

SQL的规范

规则与规范的区别。规则是必须遵守的，规范是建议遵守的。
推荐的书写规范：
1.数据库名、表名、表别名、字段名、字段别名等都小写
2.SQL 关键字、函数名、绑定变量等都大写

列的别名

as的全称是 alias(别名),可以省略，但是！建议写上 as。
列的别名可以使用一对""引起来，也可以不加双引号，但是！不要使用单引号 ’ ’ 。
ANSI规定:
1. 别名用双引号 " "
2. 字符串、日期时间类型的变量用一对单引号’ '表示是标准的
别名有英文和中文两种，英文不用加双引号，中文必须加。而且在分组或排序的时候使用中文别名，无法分组及排序！！！
order by 后面加别名的时候要去掉双引号！！！

SELECT employee_id emp_id,last_name AS lname,department_id "部门id",salary * 12 AS "annual sal"
 -- 别名中有空格时候必须加双引号
FROM employees;

空值

空值参与运算：结果一定也为空。解决办法：引入IFNULL。（IFNULL是流程控制中的单行函数）

着重号``

必须使用着重号``的情况：字段名或表名与数据库系统的关键字保留字等重名时候，必须加上着重号来防止被软件误解，否则报错

视频笔记：尚硅谷-19-比较运算符的使用 -> 尚硅谷-20-逻辑运算符与位运算符的使用

比较运算符

比较结果有三种：
1.为真返回1
2.为假返回0
3.有 NULL 参与比较则返回 NULL
安全等于运算符 <=>
安全地判断两边表达式是否相等，不会返回 NULL
最小值运算符 LEAST
最大值运算符 GREATEST
LIKE运算符
1.转义字符
2.ESCAPE 关键字：自定义转义字符
正则表达式运算符：匹配精确度比 LIKE运算符低
1.REGEXP
2.RLIKE

逻辑运算符

在MySQL中，逻辑运算符的返回结果为1、0或者NULL
4种逻辑运算符：
1.AND 或 && - -> 逻辑与
2.OR 或 || - -> 逻辑或
3.NOT 或 ! - -> 逻辑非
4.XOR - -> 逻辑异或：追求的"异" 即 两个表达式一个满足且另一个不满足时返回1

位运算符（位的英文是bit）使用频率最低

& - - > 按位与
| - - > 按位或
^ - - > 按位异或
~ - - > 按位取反
```
  - - > 按位右移
```
<< - - > 按位左移

尚硅谷-22-ORDER BY实现排序操作

排序

列的别名可写在 ORDER BY 后面进行排序
因为ORDER BY 在 SELECT 后执行
二级排序
有这个需求的原因：第一个字段有重复值时候，才需要找出第二个字段排序，否则不进行二级排序！
NULL
升序ASC时，NULL在第一行。
降序DESC时，NULL在最后一行。

尚硅谷-23-LIMIT实现分页操作

分页

MySQL使用 LIMIT 实现数据的分页显示
LIMIT的格式：严格来说：LIMIT 位置偏移量,条目数

结构"LIMIT 0,条目数" 等价于 “LIMIT 条目数”

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;

需求：每页显示pageSize条记录，此时显示第pageNo页：

公式：LIMIT (pageNo-1) * pageSize , pageSize;
MySQL8.0新特性：LIMIT m OFFSET n
等价于 LIMIT n,m
LIMIT 可以使用在MySQL、PGSQL、MariaDB、SQLite 等数据库中使用，表示分页。不能使用在SQL Server、DB2、Oracle！具体看课件第5章的 2.3扩展
康师傅在2013年录得oracle教程共21h，网址【尚硅谷】Oracle数据库全套教程，oracle从安装到实战应用

多表连接

尚硅谷-25-为什么需要多表的查询

为什么需要多表连接
1.避免记录冗余
2.减少I/O次数
3.宽表维护复杂
4.使用表的并发度小，提高开发效率
从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。
因为不指明的话，数据库不知道是哪个表的字段会去逐个找，消耗一定时间。
若要实现 n 个表的查询，则有至少 n-1 个连接条件。
1.根据每个表与另一个表的字段的能关联几个及实际需求来确定
2.若少于n-1个连接条件，必然会出现笛卡尔积错误

尚硅谷-26-笛卡尔积的错误与正确的多表查询

笛卡尔积即交叉连接cross join
无连接条件
多表查询的正确方式：需要有连接条件
否则会出现笛卡尔积错误

多表连接的分类

等值连接 vs 非等值连接
非等值连接是指连接条件为某一范围，而不是某个字段，即可返回1
自连接 vs 非自连接
内连接 vs 外连接

7种JOIN的实现

左下图：满外连接（即全连接）和右下图借助 UNION ALL 实现！
尽量使用 UNION ALL 语句而不是 UNION，以提高数据查询的效率。
UNION ALL使用时注意：
前一个SELECT后不能加分号再 UNION
UNION ALL 的两个SELECT的查询结果的列数相等，数据类型相同，名字可以不一样以第一个的为准

SQL99语法的新特性

自然连接
会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。
缺点：不够灵活，开发中使用自然连接频率较低。
USING

多表查询的练习

对多表连接字段是否有空值是不是要特别关注呢？？不是
应该关注什么呢？关注两张表连接对应的 7种join 的实现，能把两张表的关系图形化并标示出每部分的记录数
连接后进行 where 筛选时，只要where 中条件没有涉及到连接字段集的任何一个，则连接的类型可以严格地或是不严格地（即内连接）
因为：不存在说某个表有该字段值而另一个表没有的问题，即不会有外连接的问题

视频笔记：尚硅谷-35-日期时间类型的函数讲解

单行函数

日期和时间函数

MYSQL的时间戳是某个年月日时分秒对应的总秒数，共10位；
JAVA中到毫秒数，13位

查询语句自带循环

聚合函数

avg/sum：只适用于数值类型的字段（或变量），对字符串，日期时间类型操作无意义.原因：avg/sum都涉及加号，MYSQL中+号只代表数学中的加法运算，如果字符串相加，会隐式转换为数值类型，转换不了则为0。
COUNT：
① 作用：计算指定字段在查询结果中出现的个数（不包含NULL值的）
② 公式：AVG(expr) = SUM(expr) / COUNT(expr) ，expr不是*或常量
expr为NULL的时候直接被忽略的，不论是AVG，SUM，COUNT，MAX，MIN
group by 的使用
1.group by 和 group by 字段名 with rollup的区别 : with rollup 多了一个组，这个组是把整体数据当作一个组。
注意：当使用ROLLUP时，不建议用ORDER BY进行排序，因为排序后就不知道哪个是整体数据了，5.7报错，8.0不报错
having 的使用：一旦过滤条件中用到聚合函数，则必须用 having 进行筛选
为什么？？
涉及到SQL的底层执行原理
需求中只要出现 “所有……的……”，脑子中必须绷一根弦，出现所有什么的什么必然涉及外连接问题！！！

子查询

需求中select后既要非聚合函数字段，又要聚合函数字段，此时查询中必然有group by按非组函数字段进行分组
SQL的函数分为两类，一类是单行函数，另一类是聚合函数。
单行函数可以嵌套使用，但是MYSQL中聚合函数是不能嵌套使用的！！！ min(avg(字段名)) 的用法是错误的！！！ ORACLE中聚合函数可嵌套使用。
解决办法：
1.先把内层聚合函数 avg 的结果作为新的字段，作为一张表起别名
2.对1中表的字段再做聚合
多行子查询的空值问题：使用not in 包含null值时，不返回任何结果！
而使用IN 包含null值时，不影响返回结果！

窗口函数

窗口函数是在原表基础上进行排序和统计！
窗口函数 window function 是个很形象的函数。window window 对谁window? 第一个()中为空时，不对特定字段操作，涉及排序；若为字段，则对它操作。over w w=([partition by …] [order by … ] [rows between …] )
连接的表只有一个值时候，不写on条件
是否用窗口函数还是聚合函数的重要区分点：
1.你是想对分组后的各个组的记录集做聚合，则用聚合函数；你是相对分组后每个记录集的每个记录新加列/属性，则用窗口函数！按操作对象是一个还是多个进行区分。
2. 最大的区别是结果集的不同。结果集中分组字段重复出现的是窗口函数；不冗余的是group by + 聚合函数
窗口函数不论是五种的哪一种，当有"order by 字段"，没有定义窗口范围时默认是 rows between unpreceding rows and current row 不对啊，默认是 rows between unbounded preceding and current row；当没有order by 和窗口范围时，窗口范围默认是 rows between unbounded preceding and unbounded following。关键还是看有没有order by ，有的话是窗口开始到当前行，没有的话是窗口开始到窗口结束。
窗口函数中的序号函数和分布函数都与排序有关，而且函数后的()为空
前后函数、首尾函数、其他函数后的()中都不为空，有表达式或其他参数
对前后函数中 lag(expr,n) 函数的理解: lag是往下拉；拉谁啊？拉 expr；拉多少啊？拉n行。命名常为 pre_原字段名