MySQL（黑马+动力节点-旧版）笔记1

1.MySQL中提供的“substring函数”使用的过程中，注意一下它里面的特殊性：它截取字符串长度时的索引不是从0开始而是从1开始，这和其他课程里的索引都是从0开始不同，注意一下就行。

————————————————————

2.Mysql中的“ifnull” 函数用于判断第一个表达式是否为null，如果第一个值为null则返回第二个参数的值。第一个值不为null就执行第一个值

（这里和“三元运算符里的值为true则返回左边的值、不为true则返回右边的值”别记混了）。

ifnull函数的语法：ifnull(a, b)，如下图例子；此外注意下图例子中第二个：'   '是空文本，不是null，所以返回的是第一个值即'   '里的空文本。

————————————————————

3.MySQL里的约束关键字除了pk（后面可加auto_increment），not null，unique，default等外，还有后续8.0.16版本才开始支持的'check'关键字→保证字段值满足某一个条件，它的用法和公式见下图  eg保证'年龄'大小得满足在0到120之间：

age int check ( age >0&& age <=120) COMMENT '年龄'，…

 

————————————————————

4.动力节点34道练习题之75题里的经验知识点

075-34这题

（1）为什么P075-34这题的第一步的select语句后面不加group by会报错？

分析问题：

为什么一些MySQL的select语句中不加group by会报错？

很大可能就是因为你写的select语句中使用了聚合函数，并且没加group by，所以报错了。

报错原因：

在 MySQL 中，如果你在 SELECT 语句中使用了聚合函数（如 SUM()、COUNT()、AVG() 等），并且同时选择了其他非聚合列，则需要使用 GROUP BY 子句对那些非聚合列进行分组。如果不这样做，MySQL 将无法确定如何处理这些非聚合列的数据，并因此抛出错误。

这是因为当使用聚合函数时，每一行数据都可能会被汇总成一行结果；而未经过聚合操作的字段如果没有指定如何归类到最终的结果集中，数据库就不知该如何展示它们——这也就是为什么会出现这样的限制规则。简单来说就是为了让查询逻辑更明确合理，保证返回值是有意义而不是随机取某一条记录对应的值。

不过需要注意的是，在某些版本和配置下的 MySQL 允许这种做法，默认会从各组内选择一个任意值作为代表，但这通常不是我们想要的效果，并且该设置也可能在未来版本中被取消支持或者更改行为。所以为了确保 SQL 的兼容性和可移植性以及查询意图清晰表达，建议总是搭配合适的 GROUP BY 使用。

————————

在 MySQL 中，SELECT 语句后面的 GROUP BY 子句用于将查询结果按照一个或多个列进行分组。如果不加 GROUP BY ，通常意味着你并不需要对数据进行分组汇总处理。

当你不需要按某些字段值的唯一组合来聚合数据的时候，自然就不必加上 GROUP BY 。例如：

简单检索：如果你只是想从表中获取一些记录而无需做任何统计、计算等操作时；

非聚合函数场景：当你的查询里没有像 COUNT()、SUM() 等聚集函数，并且也没有意图基于特定字段创建不同的“组”来进行数据分析时；

全表扫描读取所有行的数据：比如直接通过 WHERE 条件筛选出符合条件的所有记录而不涉及分组需求。

此外，在 SQL 标准下，如果在一个包含聚集函数（如 SUM(), AVG(), MAX(), MIN(), COUNT()）的 SELECT 查询中指定了非聚组列，则必须同时指定 GROUP BY 子句以确定如何组织这些数据。但是也有一些数据库系统允许一定条件下省略它，这取决于具体的 RDBMS 实现规则以及 SQL 模式设置。

总之，是否添加 GROUP BY 取决于你要解决的问题及所期望的结果集结构。

————————

（2）from和join的地方给表起别名t，e后，前面select的字段名也要用上新的别名要不然会报错  eg：t.*，e.ename，e.sal

 （3）select的时候别只记得选上e.ename、e.sal，还有t.*别忘了选上（毕竟是连接查询，题目要求的结果里有临时表t表的内容）

（4）嵌套子查询   中   选择一行select结果当成新表   写进括号当成新表时，注意：select要粘贴进去，而分号“  ；”号别粘贴进去

（5）注意最后join on 的on写入条件的时候，如果多个条件不能用逗号“  ，”号否则会报错（select选择多个字段才是用逗号“  ，”号，区分开别弄混了），多个条件间应该用AND连接

————————————————————

5.动力节点34道练习题之76题里的经验知识点

076-34这题
（1）第一步
第一得清楚题目要求什么，然后要知道这里涉及表连接，要分析出得把emp和salgrade连接，连接条件也要分析出是sal   between s.losal and s.hisal

第二步
（这里很灵活当时没想到）是在原步骤上改动代码，基于以上结果继续按照deptno（e.deptno）分组，求grade的平均值。即join  on后面加上group  by和字段

然后第二步求薪水等级平均值当时不知道该
怎么做了，正确做法不用当成临时表t，而是拿第一步的代码改动，把select后的字段改动，去掉不必要的字段、以及在此处直接使用“字段的运算”〈注意正确的格式，avg和表的别名的那个点.的位置，正确的格式是：“avg（s.grade）”〉
 

————————————————————

6.动力节点34道练习题之77题里的经验知识点

077-34这题
（1）方法一中注意：
降序/升序排序不是group by，而是order by（且要order by sal）

（2）之前自己没注意到的小点：select后面字段的先后顺序决定了表中字段的先后（在左/在右）顺序

（3）方法二（实在没想到）：表的自连接，还有和自己命名的两张表a，b里的sal比大小（表的自连接方式仍然用join on的非等值连接＞/＜连接），然后再来个where  sal  not  in，这确实新手想不到

————————————————————

7.动力节点34道练习题之78题里的经验知识点

078-34这题
（1）方法一：先求平均薪资再降序求第一个
（当时又不知道怎么计算算平均薪水），直接select字段计算avg（sal）as avgsal就行

 

（2）这题主要的其中一个考点就是正确使用group by 和order by ，以前老是分不清什么时候该用哪个

———————

以下是关于 MySQL 中 GROUP BY 和 ORDER BY 的详细解释：

1. GROUP BY
含义：
GROUP BY 用于将查询结果按照指定列的值进行分组。通常配合聚合函数一起使用，例如 COUNT()、SUM()、AVG() 等。

使用场景：
当你需要对数据进行统计分析时，比如按类别汇总销售额、计算每个用户的订单总数等。

示例：
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

上述 SQL 查询会返回每个部门及其员工的数量。

2. ORDER BY
含义：
ORDER BY 用于对查询结果进行排序，默认升序排列（ASC），也可以通过添加 DESC 实现降序排列。

使用场景：
当你希望查询的结果能够按照某些字段排序显示出来，比如按时间顺序查看记录，或者从高到低展示销售金额。

示例：
SELECT name, salary
FROM employees
ORDER BY salary DESC; -- 按工资从高到低排序

 
（3）方法二：用max函数，但是还得求部门编号所以要花三步有点麻烦〈其中第二步还要创建临时表t连接；以及第三步还要来个having=（）套娃上一步〉
————————————————————

8.sqlyog使用小技巧

双击一个单词：选中整个单词
三击一个单词：选中一整行

————————————————————

9.为什么要用自连接？（有时候会突然莫名其妙不理解，所以就时不时看一下这条笔记就行）

→确实就是为了解决“同一张表”的“不同行”间的关系

：

通常情况下，我们使用JOIN操作来连接多张表，以获取相关的数据。但是在某些情况下，我们需要在“同一张表”中查找“不同行”之间的关联数据，这时就得使用自连接了。

————————————————————

10.有时候报错了可能是符号写成中文了没发现

（有次报错找了半天，结果终于发现原来是“字段间的逗号”不知道什么时候输成中文格式了，这逗号真的很难看出来）

————————————————————

11.

隐式内连接，即sql99写法，即join on（动力节点喜欢用；这种写法更好，就是解题时好像更难一点哈哈）；

补：join on后可以加where，而反之where后不能加join on；所以join on写法更好，后面还能加上where，此处where的作用是  进一步条件过滤

 

显示内连接，即sql92写法，即where（黑马喜欢用）。

———

  隐式内连接  将多表连接查询的写法，因为之前一直都听动力节点的课所以一时不熟这种写法所以在此写这条笔记记录一下

→

隐式内连接  连接多表时，要满足：

①每两个表间就有一个连接条件（不管是内连接还是外连接，其中 两表的连接条件 的作用就是 避免笛卡尔积现象）

②还要满足题目要求/实际需求的  查询条件

———

所以下面例题里：

2张表连接就得有1个连接条件，3张表连接就得有2个连接条件

还别忘了额外  题目/需求  里要求的查询条件（如下图二题里的dept.name＝'研发部'）

 

 

————————————————————

12.distinct在多表连接查询时对 字段 去重的使用示例（因为查询结果出现了和id有关的重复数据，所以这里对id字段进行去重）→

格式：

select  distinct  （要去重的）字段1，字段2 from  表1 ，表2  where  表1和表2的连接条件

 

————————————————————

13.在select语句中也能嵌套 子查询 ，这里的子查询是 一整行 作为 一个字段 ，示例→

 

————————————————————

14.数据库软件DataGrip里的一个功能（黑马用的、平时自己用的是SQLyog，以后可能会用上DataGrip所以记一下）

→

使一行的代码变成分行（分步）的代码的格式：

选中这行代码，右键，Reformat  Code（格式化代码）

 效果如下 

————————————————————

15.子查询的分类以及注意“where后的字段嵌套子查询时注意使用操作符中（连接符号）=/in这两个的时候要看所嵌套的子查询返回的结果行数是不是单行再决定用哪个（别判断都没判断就想当然用了）”：
①标量子查询（结果：一个值）
②列子查询（列子查询只有一列，结果：一列一行/多行）
③行子查询（行子查询只有一行，结果：一行一列/多列）
④表子查询（结果：多行多列）
———
其中要注意嵌套子查询的操作符（常用的操作符：= 、in、not in、any、some、all）里的=和in→
（1）如果子查询返回的是“单行”结果，则连接嵌套子查询的操作符用“=”

（2）如果子查询返回的是“多行”结果，则连接嵌套子查询的操作符用“in”

———
注意，只有单行能用=，多行就不能用=而要用in了
———
所以→
行子查询：能用=，也能用in
列子查询、表子查询：不能用=只能用in
———
还有要注意用=/in时，前面的job,salary的顺序要和后面括号里的顺序对应

————————————————————

16.select语句中“字段和count（*）”组合的意思和作用以及它们之间的关系
———
引入→
select deptno，count（*）number from emp group by deptno；
这个select语句中“count（*）”是什么意思？count（*）和deptno有什么关系？
———
解答（感谢ai）→
在这条SQL语句 `select deptno, count(*) number from emp group by deptno;` 中：
———
（1） 关于 "count(*)" 的含义
   "count(*)"是一个聚合函数，用于统计查询结果集中符合条件的行数。这里的 "*" 表示对所有列的所有非空记录进行计数。换句话说，它会计算每个部门（由 `deptno` 标识）中员工的数量。
   
（2） "count(*)" 和 "deptno" 的关系
   - 这里通过 `group by deptno` 将数据按照部门编号 (`deptno`) 分组，对于每一组 (即每一个部门)，`count(*)` 统计该组中有多少条记录(也就是这个部门有多少名员工)。因此，“count(*)”依赖于 “deptno”，因为它是基于按部门分组后的统计数据。

（3）整句话的作用
   - 整体来看这条 SQL 语句是从名为 `emp` 的表中检索出每个部门(`deptno`)以及对应的员工人数(count(*))的结果集，并将此数目命名为 'number' 显示出来。
———
所以总结：count（*）依赖于deptno这个字段，且计算的是group by deptno分组之后的行数。

即count（*）的结果和“它前面的字段（deptno）”、“group by 字段”有关。

所以一些select的题目里经常会用到count（*）。

————————————————————

17.COUNT函数的用法，主要用于统计表行数（不是算列数，是算行数！）。主要用法有count(*)、count(列名/字段)、count(常量/1)。

count(*)、count(列名/字段)、count(常量/1)之间的区别：
count(*) 和 count(常量/1)表示的是直接查询符合条件的数据库表的行数。
而count(列名/字段)表示的是查询符合条件的列的值不为null的行数。

即，简单来说：
count(*)、count（常量/1）会统计值为 null的行,
而 count(列名/字段)不会统计此列为 null值的行.

———————

补：count（1）在mysql中是什么意思？（关键内容看“具体含义:②”和“示例SQL查询”）
———
COUNT(1) 是 MySQL 中的一个聚合函数用法，用于统计查询结果集中符合条件的行数。虽然写的是 COUNT(1)，但实际上它的作用等同于 COUNT(*) 或者 COUNT(column_name)。
———
具体含义：
①COUNT(*) 表示计算所有行的数量，包括包含 NULL 值的列。
②COUNT(1) 的意思是每行返回值固定为 1，然后通过 COUNT 对这些“1”进行计数。实际上它跟直接使用 COUNT(*) 没有本质区别。
③如果你写了 COUNT(column_name)，则只会对指定列中非 NULL 的值进行计数。
———
注意：很多人认为 COUNT(1)、COUNT(*) 和 COUNT(id) 性能上有所不同，但在现代数据库优化器下，它们通常会被处理成相同的执行计划，性能差异可以忽略不计。
———
示例 SQL 查询：
SELECT COUNT(1) AS total_rows FROM users;
上述语句表示从表 users 中统计所有的记录总数，并将结果显示为别名 total_rows。

————————————————————

18.对于“where后不能直接使用聚合函数”的2种解决方法→

方法一：

用“where+嵌套子查询”

eg：

where  e1.sal>(select avg(sal) as avgsal from emp);

方法二：

用“group by+having+聚合函数”
eg：
select
deptno
form
emp
group by
deptno
having
count（deptno）＞=5
；

补：

count（）也是聚合函数，所以where后不能用count（），所以用“group by加having加count（）”的方式去使用count（）函数

————————————————————

19.查询时如果题目要求满足多个条件直接用多个and连接起来就行，eg：
select…from…where  条件1 and 条件2 and 条件3 and… course_code='245886'

———————

20.“用=/in连接一串数字”时要不要给“这串数字”加单引号就看“建表定义其数据”时它是int还是char类型，前者不用加单引号后者要加
（提示：可以在数据库软件SQLyog/navicat图形化页面左侧展开查看各列字段的数据类型）

eg：course_code='245886'

age=24

———————————————————

21.多字段排序的写法：order by  x，y，z
多个字段之间用逗号隔开
（不建议以多个字段排序，最好就用一个）

———————————————————

22.字段间英文逗号注意别输成中文格式而导致整天报错了：
select语句中多个字段之间用的逗号很可能“在命名字段的中文别名时切换中文输入法”而“不小心输成中文格式的逗号了”，所以导致报错了（自己已经碰到好几次了），所以以后注意下（好像中文逗号和英文逗号仔细看还是有点区别的、还是能辨别出来长得不同的）

———————————————————

23.使用模糊查询字符“_  、%”时，别忘了前面连接符不是用=而是用like
eg：查询学号前面几位是20150201的学生的所有信息
select * from course_reg where studenid like '20150201%'；

———————————————————

24.内连接和外连接
———————
内连接的种类：等值内连接，非等值内连接，自连接
内连接的写法：隐式写法（sql92，where），显示写法（sql99，join on）
inner可以省略
———
外连接的种类和写法：左外连接（left join on），右外连接（right join on）
outer可以省略，left/right不能省略
———
inner和outer都可以省略，因为区分内连接和外连接的标识不是它俩，而是有没有left/right，只要有left/right就是外连接、否则都是内连接
———————
内连接和外连接的区别：
前者连接的两表/多表没有主副之分，后者连接的两表有主副之分
———
而外连接里的表之间的主副之分的作用：查询保留主表含null的记录

外连接连接的两表/多表时其中的主表的全部内容包括null的内容全要被查出来；
所以要求查出哪张表的所有内容、且包含null的内容，就把这张表当成主表
———————
什么时候用内连接什么时候用外连接？
当要连接的两表/多表里有为null的值，而且要求查出来时就用外连接，否则用内连接的话结果里就没有含null的那行的记录了

eg：
列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
select  
e1.ename as '员工',d.dname as '部门',s.grade as '薪水等级',e2.ename as '领导'
from
emp e1
left  join    ——这里就要加left变成外连接
emp e2
on 
e1.mgr=e2.empno
join
salgrade s
on
e1.sal between s.losal and s.hisal
join
dept d
on 
d.deptno=e1.deptno
where 
e1.sal>(select avg(sal) as avgsal from emp);
 

 上面其中一个join 不改成外连接left join的话，这图片里的king就没了，因为king没有领导（mgr）所以为空值，而结果里又不能没有他的记录，所以要用外连接、外连接将空值（领导）补成“null”

———————————————————

25.三张表/多张表的连接方法：
多次join on

→格式：
select
       字段x，字段y，…
from
       表1
join
       表2
on
       条件a
join
       表3
on
       条件b
join
        …
on
        …
；

补：其中如果需要查出某个表里含null的行不想丢失这行的话就可以“把join改成left/right join”让这张表变成主表

（好吧，误会了，还是听课后去实践、再最后再回来复习听课就恍然大悟了，不实践把之前的都忘记了，还以为这是什么新奇的写法、呃只是多个表连接的写法而已）

———————————————————

26.在mysql当中怎么计算两个日期的"年差"，差了多少年？ 

用这个函数→
TimeStampDiff （间隔类型，前一个日期，后一个日期） 
———————
timestampdiff ( YEAR , hiredate , now ())
间隔类型：
 SECOND 秒，
 MINUTE 分钟，
 HOUR 小时，
 DAY 天，
 WEEK 星期，
 MONTH 月，
 QUARTER 季度，
 YEAR 年

补1：有时候TimeStampDiff（）要和ifnull（）联合使用，因为可能部门没人人数为0所以有null值导致计算结果成为了null，而我们要把其显示成0也不要显示成null

使用格式→
eg：求服务期限（任职时间）
ifnull（TimeStampDiff（YEAR , hiredate , now ()），0）

甚至还能根据查询需求再嵌套一层函数
eg：求平均服务期限（平均的任职时间）
ifnull（avg（TimeStampDiff（YEAR , hiredate , now ()）），0）

不过上面两个eg例子里的服务期限最好起个别名吧要不然真的长、引用也不方便，比如取成servicetime、avgservicetime

———————
补2：“ifnull（）函数”的内容复习

IFNULL 函数的语法：
IFNULL(expression, replacement)

关键点：
①IFNULL 函数只能接收两个参数。
②如果第一个参数不是 NULL，它会返回该值；如果是 NULL，则返回第二个参数。
③第二个参数 replacement 可以是常量、字段值或表达式。

补2.1：很多时候select语句中用ifnull（）函数是为了不让某栏内容为null而是0，即显示0也不要显示为null，所以见到的ifnull多数是ifnull（express，0）
———————————————————

27.算年薪yearsal还要加上每个月的津贴comm、这个津贴不是人人都有所以要给comm套上ifnull（）函数把null变成0值参与计算。
格式：
（sal+ifnull（comm，0））*12

eg：
select 
ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by yearsal;

（“津贴”在emp表里的字段名：comm；commission的缩写，津贴）

————————————————————

28.从1题里纠错两个习惯错误

题目：
列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
select
e1.name，d.dname，e2.name，s.grade
from
emp e1
join
emp e2
on
e1.mgr=e2.empno
join
dept d
on
e1.deptno=d.deptno
join
salgrade s
on
e1.sal between s.losal and s.hisal
where
e1.sal＞（select avg（e1.sal）from emp）；

纠错：
①给emp表起了别名后，别忘了不是name而是ename，不是e.name/e1.name，而是e.ename/e1.ename、即前面的e别忘了，所以开头正确的行应该是→

select
e1.ename，d.dname，e2.ename，s.grade

②最后where语句错了，嵌套子查询里的avg（）括号里面不用使用别名、emp表也不用起别名，所以结尾正确的行应该是→
where
e1.sal＞（select avg（sal）from emp）；

————————————————————

29.mysql中的安全模式（拓展补充知识点：数据库系统概论——在使用修改update语句时，出现error，使用set sql_safe_updates=0;  执行完后执行update语句即可成功。）

在mysql中，如果在update和delete没有加上where条件，数据将会全部修改。不只是初识mysql的开发者会遇到这个问题，工作有一定经验的工程师难免也会忘记写入where条件。为了避免失误造成的数据全部修改和删除，可开启mysql的安全模式。
———————
设置安全模式SET SQL_SAFE_UPDATES可以关闭和开启安全模式

①关闭安全模式
SET SQL_SAFE_UPDATES = 0;
②开启安全模式
SET SQL_SAFE_UPDATES = 1;

eg：给任职日期超过30年的员工加薪10%.

SET SQL_SAFE_UPDATES = 0;

update emp set sal=sal*1.1 
where timestampdiff(year,hiredate,now())>30;

select * from emp;（因为前面没有select语句所以改动后查看改动结果来个select语句）

————————————————————

30.select后的字段要留有和group by有关的字段、不能少，要不然group by不知道按什么字段去分组。

eg：取得每个薪水等级有多少员工
select
s.grade，count（*）as  number
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
s.grade
；

————————————————————

31.not in、distinct、is not null三者搭配字段的使用格式/场景（其他使用场景还没见过，有用的话再补充）
→
（1）where  字段  not in （含字段集的临时表t）；
（2）select  distinct  字段，from 表…；
（3）where  字段  is not null；
———
eg：求出普通员工（不是领导的）的最高薪资
select max(sal) 
from emp where empno not in  
(select distinct mgr from emp where mgr is not null);
———
补充：这例题中用上面这三者它们各自的作用→
①not in是筛选出“所有员工编号”中没在“领导编号”里的人的编号即“所有普通员工的编号”（所有员工包括：普通员工、领导）
②distinct是去重留下不重复的，即排除所有员工的重复领导编号（一些员工对应同一位领导，所以员工表emp的ename对应的mgr可能重复）
③is not null是排除没有领导的king的mgr对应的null值，因为如果不排除的话null值会通过not in的关联把max（sal）的筛选结果变成null

 

————————————————————

32.筛选最值时选择用max/min还是asc/desc+limit ？
→记住：limit降/升序法是为了获取分组函数和被分组字段之外的信息才用的，否则max/min最方便
———————
eg：列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.

①select max(sal) from emp where deptno=30；——作为嵌套子查询
②select 
    e.ename,e.sal,d.dname
from   emp e
join   dept d
on  e.deptno=d.deptno
where
    e.sal>(select max(sal) from emp where deptno=30);

 

———————————————————
33.查询数据时有些情况注意要排除“自己”，所以要在语句后加上“and 字段 <> 某值”

eg：列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.

select ename,sal 
from emp 
where sal in (select sal from emp where deptno=30) and deptno <> 30;

因为emp里有所有员工的sal所以最后别忘了排除部门30他自己

 

————————————————————

34.与null值有关的外连接left/right join和ifnull各自的意义和二者的区别
（1）意义：用外连接left/right join是为了保留（不忽略）含null值的数据，用ifnull是把null值改成“0/其他数值”再参与运算、避免null参与运算将结果全变成null

（2）区别：外连接left/right join的使用对象是表，ifnull的使用对象是字段，二者区分开
———————
如这下面这题就是很好的例子
———
列出在每个部门工作的员工数量,平均工资和平均服务期限.
标答（标准答案）：
select
    d.deptno,
count(e.ename) as employee_number,
ifnull(avg(e.sal),0) as avgsal,
avg(timestampdiff(year,hiredate,now())) as avgservicetime
from 
    emp e
right join 
    dept d
on
    e.deptno=d.deptno
group by
    d.deptno;
———
注意1：之前一直没留意dept表，dept表里还有个部门编号为40的没有员工的部门，所以要查出部门人数是0的部门→最终查询结果里挂个deptno为40的名就行、这个部门的其他列要求内容为0不为null就行）→所以要改用（右）外连接right  join

注意2：是ifnull函数嵌套在avg函数外，别把avg套在ifnull外、逆反天罡了，正确格式：
ifnull（avg（e.sal），0）

 

————————————————————

35.内连接、外连接、自连接 的区别

内连接：只返回在两个表中都有匹配的行，常用于精确获取两个表中存在关联的数据。

外连接：返回一个或两个表中所有相关的行，即使没有匹配的情况。左连接和右连接分别返回左表和右表中的所有行。

自连接：用于将一个表与自己连接，从而查找表中的层级关系或其他复杂的关联。

 

———————————————————
36.连接查询（join、临时表t）和嵌套子查询（select）的关系：可互替的等量关系→

可以使用连接查询(join)代替子查询（select），连接查询需要建立临时表（临时表t）

拓展：联表操作不需要查询数据, 只需要在联表成新表之后查询一次, 因此连接查询速度比嵌套子查询快
———
使用子查询进行select语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作

 

———————————————————
37.笛卡尔积(交叉连接) cross join
eg：select * from student cross join class；

 

———————————————————
38.关于group by/order by后的字段要不要一定出现在select语句中的讨论（*和最后留的一个待定的疑难*）

①大部分情况group by和order by后接的字段要在select语句中出现，因为这样才能看出“select查询结果”是按什么字段分组/排序的；

②当然这个字段也可以不在select语句中、只需表中有这个字段就行，但是这样就看不出来查询结果是按什么分的组/排的序；

③有些情况确实不需要group by/order by后的字段一定出现在select语句中，比如多表连接查询的过程中这个字段可能只是充当“辅助”的作用。
〈如下例题中标⭐️号处的where后的二层嵌套子查询中group by deptno的deptno并没有在select语句中出现、但是deptno字段存在于表中、充当帮助avg（）函数“分组后”计算平均薪水的作用，然后deptno其本身并不需要出现；后面的order by后倒是跟了avgsal因为题目正好也需要查询结果里有avgsal〉

eg：求平均薪水的等级最低的部门的部门名称
select 
    t.dname,t.avgsal,s.grade
from
    (select d.dname,d.deptno,avg(e.sal) as avgsal from dept d join emp e on d.deptno=e.deptno group by deptno) t
join
    salgrade s
on    
        t.avgsal between s.losal and s.hisal
where
    (select grade from salgrade s where (⭐️select avg(sal) as avgsal⭐️ from emp ⭐️group by deptno⭐️ order by avgsal limit 1) between s.losal and s.hisal) = s.grade;

 

———————————
*补*：用order by排序的字段不一定要在select语句中，即使是select distinct与order by同时使用。
（这个有些文章说要有些说不要，不知道正确的说法是什么，以后有时间抽空解决/补一下这个疑难！！！！！！！！！！！！）

 

————————————————————

39.“索性选择性”计算公式里的函数的嵌套顺序&先后顺序别搞错了，是

select count ( distinct substring ( email ,1,5))/ count (*) from tb _ user ;

即：select count（distinct substring（字段名，从1开始的索引，截取的长度））/count（*）from 表名；

 

———————————————————
40.终止一条正在编写的语句（比如开启事务 start transaction后中途如果想终止编写、开始其他命令的话）输入：/c

———————————————————
41.concat()函数——拼接字符串函数
①功能：将多个字符串连接成一个字符串。②语法：concat(str1, str2,…)

返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

———————————————————
42.update和delete的语法格式
修改字段数据：update  表名  set 字段1=…，字段2=…；
删除字段数据：delete  from 表名 where  条件（和字段有关的条件）；

———————————————————

43.创建“存储过程”的语句中注意：除了最后end后面要用“；分号”结束语句外、begin和end中间的存储sql语句也要最后带上分号→

还要注意这点和“在括号里的嵌套子查询的结尾不用带上分号”区分开！

————————————————————

44.function存储函数的使用语法和示例
—————
（1）步骤：
①创建函数－根据ID获取员工姓名与员工工资（这个示例是在命令行里执行的所以用了delimiter）
DELIMITER //
CREATE FUNCTION GetEmployeeInformationByID(id INT)
RETURNS VARCHAR(300)
BEGIN
RETURN(SELECT CONCAT(‘employee name:’,employee_name,‘—’,'salary: ',employee_salary) FROM employees WHERE employee_id=id);
END//
DELIMITER ;

②调用函数
在MySQL——函数的使用方法与MySQL内部函数的使用方法一样。（函数后面括号里填需要查的信息对应的id即可），eg：
select GetEmployeeInformationByID（1）；

注：调用存储函数不用call，而是用select调用，其中要依据具体情况看是否要定义用户变量后再调用：

1）如果select调用的结果“是原存储函数F1”不在begin和end之间、则调用结果时直接select F1（）；调用

例：上面代码和图片

2）如果select调用的结果“不是原存储函数F1，而是由于计算过程需要新declare声明的变量result”，则由于result在begin和end之间即变成了局部变量则超出begin和end之间的范围就会失效、则调用结果“result”时先把此结果定义成用户变量set @result=F1（）；

再select @result；调用

例：

create function F1（）；

begin

declare result…；

result有关的计算…

end；
set @result＝F1（）；
select @result；

—————
（2）代码分析：
①CREATE FUNCTION GetEmployeeInformationByID(id INT)
RETURNS VARCHAR(300)

这里我们声明了一个名为 GetEmployeeInformationByID 的函数，它接收一个整型参数 id 并返回长度不超过 300 的字符数据类型（VARCHAR）。该函数的作用是通过传入的员工编号获取对应的姓名和薪资信息。

②RETURN(SELECT CONCAT('employee name:',employee_name,'---','salary: ',employee_salary) 
       FROM employees 
       WHERE employee_id=id);

此段代码表示从 employees 表中选择符合条件的数据行(WHERE employee_id = id)并将其转化为字符串形式输出，其中包含字段有：employee_name(员工名字), employee_salary(薪水)，并通过特定连接词拼接成一段文字描述：“员工名:name — 工资: salary”。
————————————
（3）这段MySQL代码中returns和return的关系以及它们各自的含义是什么？

在这段 MySQL 存储函数代码中，RETURNS 和 RETURN 的作用是密切相关的，但它们的功能并不完全一样：

①. RETURNS
定义了该存储函数的返回值类型。
在声明部分指定整个函数将返回的数据类型，在这里是 VARCHAR(300) 类型。这意味着这个函数最终会向调用者提供一个长度不超过 300 字符的字符串作为结果。
②. RETURN
实际上用于在函数体内部将计算得到的结果返回给外部调用方。
具体到本例，RETURN (SELECT ...); 这一行表示从数据库表 employees 中查询符合条件的内容，并通过此行指令把所得数据传递出去。

两者之间的关系可以简单理解为：RETURNS 声明了一个函数理论上应该返回什么样的数据；而 RETURN 则是在实际运行过程中负责送出具体的数值或表达式结果。

在这个例子里面：
RETURNS VARCHAR(300)
表明此函数承诺会给出一条字符长度上限为300的信息串；

而在稍后的主体操作里，
RETURN(SELECT CONCAT('employee name:',employee_name,'---','salary: ',employee_salary) FROM employees WHERE employee_id=id);

它确实按照之前的约定实现了这一目标——组合员工姓名与薪水信息形成一段文本并予以反馈。

———

补：关于存储函数function返回return的结果是select的多个字段信息时要用concat（）函数拼接的问题→

1）因为returns的结果是varchar所以要在return后的select语句中用concat（）函数拼接select的多个字段成最终输出结果，不能直接return select语句，因为return最终返回输出的结果类型是varchar
2）所以以后要注意如果return输出的是int/select的单个字段则与concat（）函数无关，如果return的是select的多个字段则一定要用concat（）函数拼接成字符串形式变成varchar类型输出才行

eg：下图二的2小题

 

create function F1（dname char（20））
returns varchar（100）
begin
return（select concat（“id：”，id，“—”，“name：”，name，“—”，“description：”，description） from departments where name=dname）；
end；
select F1（'销售部'）；

————————————————————
45.delete table和drop table
（1）区别：
delete→仅清空表数据、而不破坏表结构
drop→则是完全摧毁表结构及其表数据
———
（2）能否支持回滚rollback：
如果你需要能够回滚的操作，应该选择使用 delete来清除数据而非破坏表的整体架构（表结构）；而当你确定不再需要用到某张表格时才考虑采用drop。即delete后还能回滚，但drop后不能回滚 
———
（3）操作对象：
或者当你需要撤销数据库中已经存在的表时就可以用drop，即：
drop的操作对象→整个表
delete的操作对象→表中的数据
———
补：
再比如，能够删除一列（包括列名和列中的数据）的命令
“alter table emp drop column addcolumn”
中用的就是drop而不是delete（好像应该也没有delete删除列数据的语法）

 

———————————————————
46.MySQL中的定点数类型只有 DECIMAL 一种类型
语法：
decimal(M, D) [unsigned] : 定点数M指定长度，D表示小数点的位数

①decimal(5,2) 表示的范围是 -999.99 ~ 999.99
②decimal(5,2) unsigned 表示的范围 0 ~ 999.99 decimal和float很像，但是有区别:float和decimal表示的精度不一样
③0＜=M＜=65，0＜=D＜=30，D＜M

补：unsigned→unsigned 是一种数据类型的限定符，在 C / C +＋等编程语言中用于表示无符号数的数据类型。当我们将一个整型变量声明为 unsigned 时，意味着它只能存储非负数值（即零或正整数），而无法存储负值。

 

———————————————————
47.TEXT和BLOB数据类型
（1）TEXT 类型
用途：主要用于存储大量的文本信息。
大小范围：
TINYTEXT: 最大长度为 255 字节 (2^8 − 1)
TEXT: 最大长度为 65,535 字节(约64KB) (2^16 − 1)
MEDIUMTEXT: 最大长度为 16,777,215 字节(约16MB)(2^24 − 1)
LONGTEXT: 最大长度为 4,294,967,295 字节(约4GB)(2^32 − 1)

（2）BLOB 类型
用途: 主要是用于储存二进制大型对象，比如图片文件、音频片段或其他非结构化数据形式。
大小范围:
TINYBLOB: 最大长度为 255字节
BLOB: 最大长度为 65,535字节（约为64KB）
MEDIUMBLOB: 最大长度可达16MB左右
LONGBLOB: 可达最大值接近于4GB

 

———————————————————
48.登录MySQL服务器的3种命令格式
①mysql - u root - p
②mysql - h 127.0.0.1 - u root - p 
③mysql - h localhost - u root - p

这三条命令都是用于登录MySQL服务器的，但在具体的连接方式上存在一些细微的区别：

*命令解释（了解即可）
（1） MYSQL -U ROOT -P
这是最基本的形式，默认会尝试通过本地套接字文件（socket file）连接到本机运行的MySQL服务。如果没有特别指定-h选项，则默认主机名是localhost，并且优先走Unix socket而不是TCP/IP。

（2） MYSQL -H 127.0.0.1 -U ROOT -P
-h 127.0.0.1明确指定了IP地址作为目标主机。由于使用了IPv4回环地址 (loopback address)，该命令强制客户端通过TCP协议与本地MySQL服务器通信，而不会使用Unix Socket文件。

（3） MYSQL -H LOCALHOST -U ROOT -P
虽然这里也写了localhost作为主机名，但实际上它的行为取决于系统配置。通常情况下，在Linux等操作系统中，“localhost”会被解析成使用 Unix Socket 文件的方式去建立链接；不过如果设置了特殊规则或将DNS解析改为了指向实际网络接口而非本地循环设备时，可能会转为基于 TCP 的交互过程。

 

————————————————————

49.系统变量、用户变量、局部变量
（根据动力节点课件做的笔记，后期想要补充/修改可以回头对照动力节点视频和课件）
（1）系统变量（全局变量、会话变量）：
①查看系统变量
show variables；
show global variables；
show variables like '%auto&'；
select @@global.xxx；
select @@session.xxx；
select @@xxx；
———
②设置系统变量
set xxx＝0/1；（0代表开，1代表关）
set global xxx=0/1；
set session xxx=0/1；
———————
（2）用户变量：
①定义用户变量并赋值
set @a='qwe'；
set @b：＝'asd'
set @c：＝'zxc'，@d：＝'rty'；
———
②读取用户变量
select @a；
select @a，@b，@c；
———
③赋值用户变量
select @e：＝'fgh'；
—
set @f：＝'vbn'；
select @f；
———————
（3）局部变量：
①声明局部变量a、b
declare a type default xxx；
declare b type default xxx；
———
②给局部变量a赋值
set a：＝xyz；（静态赋值）
select…into a from table；（动态赋值）
———
③读取局部变量a和b
select a，b；

 

————————————————————
50.mysql触发器new old（一）:
"NEW . column_name"或者"OLD . column_name".这样在技术上处理(NEW | OLD . column_name)新和旧
的列名属于创建了过渡变量("transition variables")。
———
①对于INSERT语句,只有NEW是合法的；
②对于DELETE语句，只有OLD才合法；
③而UPDATE语句可以在和NEW以及
OLD同时使用。

———

如下面三张图的例子（来自动力节点课件）就很好体现了new和old的用法，即在“记录操作日志表oper_log”中insert into给字段的值values写入“字段名字”时加上“new./old.”成“new.～～/old.～～”即可

（注意！！！：触发器里写入的values值和普通建表语句里的不同，这里的values值是写入“加上new./old.的字段名”而非具体内容）

 

insert触发器（自己码一遍，了解new和old的用法，复习的时候也可以码下面这三个触发器的代码）
drop trigger trigger_dept_insert if exits；
create trigger trigger_dept_insert
begin
after/before insert on dept for each row
insert into oper_log（id，oper_type，oper_time，oper_desc）values（null，'insert'，now（），concat（'插入数据：deptno='，new.deptno，'，dname='，new.dname，'，loc='，new.loc））；
end；

 update触发器
drop trigger trigger_dept_update if exits；
create trigger trigger_dept_update
after update dept on for each row
begin
insert into oper_log（id，oper_type，oper_time，oper_desc）
values（null，'update'，now（），concat（'更新数据，更新前：deptno='，old.deptno，'，dname='，old.dname，'，lod='，old.loc，'，更新后：deptno='，new.deptno，'，dname='，new.dname，'，loc='，new.loc））；
end；

 delete触发器
drop trigger trigger_dept_delete if exits；
create trigger trigger_dept_delete
after delete on dept for each row
begin
insert into oper_log（id，oper_type，oper_time，oper_desc）
values（null，'delete'，now（），concat（'删除操作，删除的数据是：deptno='，old.deptno，'，dname='，old.dname，'，loc='，old.loc））；
end；

 

补：上面三张图里的concat（）函数括号里拼接的字符串和字段间前后有多个逗号，输出结果的时候看需要添加逗号吧
———
下面是一个UPDATE中同时使用NEW和OLD的例子。（从别人文章里借鉴的例子，没太看懂就当成笔记吧随便了、看上面动力节点的例子就行）
CREATE TRIGGER tr1
BEFORE UPDATE ON t21
FOR EACH ROW
BEGIN
SET @old = OLD.s1;
SET @new = NEW.s1;
END;
———
现在如果t21表中的s1列的值是55，那么执行了"UPDATE t21 SET s1 = s1 + 1"之后@old的值会变成55，
而@new的值将会变成56。

 

———————————

51.mysql触发器new old（二）:

old和new的用法，这一篇文章中简单总结一下。

（1）插入操作 insert

对于insert语句，如果原表中没有数据，那么对于插入数据后表来说新插入的那条数据就是new。比如下面是SQL

drop trigger if exists insert_tableB;

– 如果有这个触发器就先删除

create trigger insert_tableB

– 触发表名称 insert_tableB

after insert

– 触发条件，在insert操作之后

on tableA

– 需要在哪个表触发

for each row

begin

insert into tableB (code,id)

values(new.code,new.id);

– sql语句

end;

当我们在tableA表中执行insert操作后，tableB表会自动插入一条数据，这个时候，这条数据用new表示。

———

即：tableB是用于记入触发器收集tableA被insert/update/delete前/后经历的操作过程的操作日志的内容的表。两张表别搞混了，要清楚哪张表是干嘛的、各自的角色是什么！！！（初学不熟悉的时候把表的角色都没分清）

———

（2）更新操作 update

当使用update语句的时候，当修改原表数据的时候相对于修改数据后表的数据来说原表中修改的那条数据就是old，而修改数据后表被修改的那条数据就是new。

比如我们下面这个触发器：

drop trigger if exists update_tableB;

– 如果有这个触发器就先删除

create trigger update_tableB

– 触发表名称 update_tableB

after update

– 触发条件，在update操作之后

on tableA

– 需要在哪个表触发

for each row

begin

update tableB

set

code_code = new.code,

id_id =new.id

WHERE code_code=old.code;

– sql语句

end;

其中比较关键的是：

update tableB

set

code_code = new.code,

id_id =new.id

WHERE code_code=old.code;

可以看出，我们set的是新值（new）。而用where条件限定的是旧值（old）。

这样就完成更新操作了。

———

（3）删除操作delete

当使用delete语句的时候，删除的那一条数据相对于删除数据后表的数据来说就是old。

比如下面这个触发器

drop trigger if exists delete_tableB;

– 如果有这个触发器就先删除

create trigger delete_tableB

– 触发表名称 delete_tableB

after DELETE

– 触发条件，在delete操作之后

on tableA

– 需要在哪个表触发

for each row

begin

DELETE from tableB

WHERE code_code=old.code;

– sql语句

end;

可以看出，我们删除B表的操作，where限定条件也是old。

———

（4）简单总结

old表示插入之前的值，new表示新插入的值；old用在删除和修改，new用在添加和修改。

 

————————————————————

52.一道关于创建联合索引的sql优化题：

最优解是创建username、password两个字段的联合索引.

———
为什么查询的是id name age三个字段但只要建后两个字段的联合索引而不用带上id字段？

———
→因为：所有建立的非主键索引都是二级索引，二级索引采用的是B+tree的数据结构、其叶子节点下面自带id，所以根据两个字段进行索引查询时能获得行数据的主键（即id）

id是主键为聚集索引，而username、password创建的联合索引即二级索引会自动关联主键（即id）

 

————————————————————

53.“左外连接left join”解决一边表中null值要显示的问题，
而“全外（满外）连接union”解决两边表中null值都要显示的问题。
———
在mysql中有全外（满外）连接，但是和SqlServer不同→
①mysql中支持的全外连接是：union（all）②sqlserver中支持的是：full join（mysql不支持full join，输入会报错）
———
mysql中全连接union的语法：
select 语句1
union（all）
select 语句2；

（注：第一个select语句结尾不要写分号，第二个select语句结尾才写分号）

————————————————————

54.group by分组聚合的select查询字段必须是分组字段列（官方说法）

———————————————————
55.select  xx
from  a
join  b
where  xx
group by xx
having 聚合函数；

这样的方式不直接在where后面用聚合函数而是再加一个group by再带上having也行（这样聚合函数就间接在where后面就不受其影响就能用了）

———————————————————
56.round（）函数的使用示例，eg：保留小数点后两位→
round（avg（salary），2）
———
round() 函数介绍：是许多编程语言中常用的内置函数之一，用于将数字四舍五入到最接近的整数或指定的小数位数。

 

————————————————————

57.mysql初始化sql脚本数据方法（出处→动力节点P7-导入初始化数据）：
1.打开dos命令窗口，win+r打开cmd命令窗口
2.输入mysql -u root -p 密码
3.输入source、空格，然后把文件夹里的sql脚本拖进命令行自动粘贴回车就行