02-数据库学习笔记（SQL引擎)

一.SQL语言分类SQL 是我们和关系型数据库交流的语言按照功能可分为四大类类别作用例子DML数据操纵语言数据操作SELECT, INSERT, UPDATE, DELETEDDL数据定义语言数据结构定义CREAT,TABLE, ALTER, DROPDCL数据控制语言权限控制GRANT, REVOKETCL事务控制语言-----COMMIT、ROLLBACK、SAVEPOINT二.DDL数据定义语言它负责定义数据库的结构。通俗来说“我要建什么表、修改什么表、删除什么表”1.数据库操作常见DDLCREATE 创建ALTER 修改DROP 删除TRUNCATE 清空-- 创建数据库CREATEDATABASEtest_db;-- 查看所有数据库SHOWDATABASES;-- 使用数据库USEtest_db;-- 删除数据库DROPDATABASEIFEXISTStest_db;2.常用数据类型MySQL整数INT,BIGINT字符串VARCHAR(长度),CHAR小数DECIMAL(总长度,小数位)日期DATE、DATETIME布尔用TINYINT(1)0 假 1 真3. 创建表 CREATE TABLECREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENT,-- 主键自增唯一标识nameVARCHAR(20)NOTNULL,-- 非空ageINTDEFAULT18,-- 默认值18scoreDECIMAL(5,2),create_timeDATETIME);约束关键字PRIMARY KEY主键唯一且非空NOT NULL不能为空DEFAULT默认值UNIQUE唯一值FOREIGN KEY外键表关联4. 修改表 ALTER-- 新增字段ALTERTABLEstudentADDemailVARCHAR(50);-- 修改字段类型ALTERTABLEstudentMODIFYemailVARCHAR(100);-- 修改字段名ALTERTABLEstudent CHANGE email mailVARCHAR(100);-- 删除字段ALTERTABLEstudentDROPmail;5. 删除表DROPTABLEIFEXISTSstudent;三.DML数据操纵语言它负责操作表里面的具体数据。注意DDL操作的是表的结构。DML操作的是表里的数据常见DMLINSERT 插入UPDATE 修改DELETE 删除1. 插入 INSERT-- 指定字段插入INSERTINTOstudent(name,age,score)VALUES(张三,20,92.5);-- 全字段插入顺序必须和表一致INSERTINTOstudentVALUES(2,李四,19,88.0,2026-01-01 10:00:00);-- 批量插入INSERTINTOstudent(name,age)VALUES(王五,18),(赵六,21);2. 更新 UPDATE不加WHERE会全表更新-- 修改id1的学生分数UPDATEstudentSETscore95WHEREid1;-- 多字段同时修改UPDATEstudentSETage20,score90WHEREname张三;3. 删除 DELETE不加WHERE清空整张表DELETEFROMstudentWHEREid3;-- 清空表不重置自增主键DELETEFROMstudent;-- 快速清空表重置自增不可回滚TRUNCATETABLEstudent;四.DQL数据查询语言负责查询数据。1. 基础查询-- 查询所有列SELECT*FROMstudent;-- 查询指定列SELECTid,name,scoreFROMstudent;-- 起别名 ASSELECTnameAS姓名,score 分数FROMstudent;-- 去重 DISTINCTSELECTDISTINCTageFROMstudent;2. WHERE 条件过滤常用运算符 ! 逻辑符AND OR NOT模糊匹配LIKE-- 分数大于90SELECT*FROMstudentWHEREscore90;-- 年龄18或19SELECT*FROMstudentWHEREage18ORage19;-- 姓名带张 %匹配任意字符 _匹配单个字符SELECT*FROMstudentWHEREnameLIKE张%;-- 区间 between 包含两端SELECT*FROMstudentWHEREscoreBETWEEN80AND100;-- 多值匹配 INSELECT*FROMstudentWHEREageIN(18,20,22);-- 判断空值 NULL必须用IS NULL不能用NULLSELECT*FROMstudentWHEREscoreISNULL;3. 排序 ORDER BY-- 分数升序默认ASCSELECT*FROMstudentORDERBYscoreASC;-- 分数降序 DESCSELECT*FROMstudentORDERBYscoreDESC;-- 多条件排序先分数降序同分按年龄升序SELECT*FROMstudentORDERBYscoreDESC,ageASC;4.嵌套查询SELECT姓名FROM学生WHERE学号IN(SELECT学号FROM选课WHERE课程号001);五.DCL数据控制语言负责控制用户权限。比如一个学校数据库学生 → 只能查看自己的成绩老师 → 可以查看学生成绩教务处 → 可以修改成绩不可能所有人都拥有数据库最高权限所以需要权限管理。常见DCLGRANT 授予权限REVOKE 收回权限六.TCL:事物控制语言负责控制事务保证一组数据库操作要么全部成功要么全部失败。比如银行转账张三账户 -1000↓李四账户 1000这其实是两个操作如果发生张三 -1000 ✅李四 1000 ❌那就出大问题了,所以数据库需要事务开始事务↓张三 -1000↓李四 1000↓全部成功↓COMMIT如果中途出错ROLLBACK把之前的操作撤销。常见TCLCOMMIT 提交ROLLBACK 回滚SAVEPOINT 保存点七.总结分类全称作用常见命令DDL数据定义语言定义结构CREATE、ALTER、DROPDML数据操纵语言修改数据INSERT、UPDATE、DELETEDQL数据查询语言查询数据SELECTDCL数据控制语言权限管理GRANT、REVOKETCL事务控制语言管理事务COMMIT、ROLLBACK以管理图书馆数据为例DDL创建图书馆决定图书表里有哪些字段DML管理图书增删改DQL找图书DCL:管理工作人员权限TCL保证借书操作完整性八.常见聚合函数1.什么是聚合函数聚合函数的核心作用是对一组数据进行统计计算最终返回单一结果常用于数据统计分析场景。聚合函数 把多行数据“汇总”成一个结果。2.五个核心聚合函数COUNT () 统计行数COUNT(*)统计表中所有行包含 NULLCOUNT(字段)统计该字段不为 NULL 的行数SELECTCOUNT(*)FROMtb_user;-- 总人数SELECTCOUNT(name)FROMtb_user;-- 姓名不为空的人数SUM () 求和只计算数字类型NULL 自动忽略SELECTSUM(age)FROMtb_user;-- 所有人年龄总和AVG () 平均值总和 ÷ 有效行数自动跳过 NULLSELECTAVG(age)FROMtb_user;-- 平均年龄MAX () 最大值数字、字符串、日期都能用SELECTMAX(age)FROMtb_user;-- 最大年龄MIN () 最小值SELECTMIN(age)FROMtb_user;-- 最小年龄3.聚合函数和普通函数有什么区别普通函数通常是一行数据 → 一个结果例如姓名 → 转成大写聚合函数则是多行数据 → 一个结果九.分组与过滤1.分组GROUP BY)GROUP BY的作用是把表中相同值的行归为一组方便对每组数据单独用聚合函数统计比如按性别分组统计男女人数。2.过滤WHEREHAVING)WHERE是在分组前过滤原始数据比如只保留 18 岁以上的用户再分组HAVING是在分组后过滤聚合结果比如只保留人数大于 2 的分组。WHERE在分组前过滤原始数据减少分组的数据量HAVING在分组后过滤统计结果针对聚合后的分组做筛选。而且WHERE不能用聚合函数HAVING可以用这也是两者的重要区别。十.高级分组GROUPING SETS)1.核心作用GROUPING SETS是对多个分组维度进行组合统计的高级语法相当于把多个GROUP BY的结果合并起来避免用UNION ALL拼接。比如订单表按 “用户 状态”“用户”“状态” 分别分组统计金额用GROUPING SETS可以写成SELECTuser_id,status,SUM(amount)FROMtb_orderGROUPBYGROUPING SETS((user_id,status),(user_id),(status));这样会同时返回三个分组维度的统计结果。它比多次 GROUP BY 再 UNION ALL 更高效因为只扫描一次表。十一.字符串操作String Operation1.模式匹配模式匹配即模糊查询通过LIKE关键字配合特殊通配符可实现各类模糊匹配需求具体规则如下%匹配任意长度的字符串包括空字符串例如’%;cs’可匹配“rzacs”“taylorcs”甚至“cs”前缀为空。_匹配任意单个字符仅能匹配一个字符例如’*acs’可匹配“racs”“sacs”但无法匹配“rzacs”前缀为两个字符。比如name LIKE 李 %能找出所有姓李的人name LIKE _明 能找出第二个字是明的两个字姓名。2.常用字符串操作函数拼接字符串CONCAT ()作用把多个文本拼在一起SELECTCONCAT(姓名,name)FROMtb_user;-- 结果姓名zhangsan-- 拼接带分隔符 CONCAT_WS(分隔符, 字符串1, 字符串2)SELECTCONCAT_WS(-,男,20)-- 结果男-20大小写转换UPPER/LOWERUPPER()全部转大写LOWER()全部转小写SELECTUPPER(mysql);-- MYSQLSELECTLOWER(MySQL);-- mysql截取子串SUBSTRING/SUBSTR语法SUBSTRING(字符串, 起始位置, 截取长度)⚠️ MySQL 字符串下标从 1 开始不是 0SELECTSUBSTRING(abcdef,2,3);-- 从第2位取3个字符bcd获取字符串长度LENGTH/CHAR_LENGTHLENGTH()字节长度中文一个汉字占 3 字节CHAR_LENGTH()字符个数推荐统计文字个数SELECTCHAR_LENGTH(数据库);-- 3个字符SELECTLENGTH(数据库);-- 9个字节替换字符串REPLACEREPLACE(原字符串, 要替换内容, 新内容)SELECTREPLACE(abc123,123,xyz);-- abcxyz查找字符串POSITION/LOCATE查找子串在字符串中第一次出现的位置找不到返回 0SELECTLOCATE(sql,mysql);-- 2十二.日期与时间操作不同数据库系统的日期时间操作语法差异较大无统一标准十三.结果控制与输出重定向1.排序结果控制排序用ORDER BY默认升序加 DESC 是降序比如按年龄从大到小排SELECT * FROM tb_user ORDER BY age DESC;2.分页结果控制分页就是把查询结果分成多页显示避免一次性加载太多数据。比如查询 100 条用户数据每页显示 10 条就会分成 10 页用户可以一页一页看。在 SQL 里用LIMIT实现比如LIMIT 0,10是第 1 页LIMIT 10,10是第 2 页前面的数字是起始位置后面是每页条数。3.输出重定向结果存储正常执行 SQL 查询查询结果会直接打印在终端 / 软件界面上输出重定向改变结果输出位置不打印到屏幕而是存入本地文件、服务器文件。十四.横向连接 (LATERAL JOINS)1.什么是横向连接纵向上下增加数据行UNION 合并多条查询上下拼接横向左右拼接多张表的字段把多张表的列合并到一张结果表这就是 JOIN 横向连接。两张表左右拼在一起行不变、字段变多属于横向合并。举例学生表id,name 成绩表stu_id,score横向连接一行数据同时出现学生姓名 分数。2.四种常用横向连接准备两张测试表student:sidname1小明2小红3小刚score成绩sidscore1902881.INNER JOIN内连接只保留两边都能匹配上的数据两边没有对应数据直接丢弃。结果只有小明、小红小刚无成绩不显示。2.LEFT JOIN左外连接左边表全部保留右表匹配不到的字段填充 NULL。小明、小红有分数小刚 score 列显示 NULL。3.RIGHT JOIN右外连接右边表全部保留左表无匹配则为 NULL。这里右表是成绩表只会出现有成绩的学生。4.CROSS JOIN交叉连接笛卡尔积不加匹配条件左表每一行和右表每一行全部两两组合。student 3 行score 2 行结果共 3×26 条记录无业务意义极少使用。总结INNER JOIN两边匹配才显示LEFT JOIN左表全部保留右表匹配不上填 NULLRIGHT JOIN右表全部保留左表匹配不上填 NULLCROSS JOIN无条件全部配对十五.公用表表达式CTE1.核心定义CTE 就是用 WITH 定义一个临时查询结果只在当前 SQL 里能用能让复杂查询更清晰。比如WITH t AS (SELECT * FROM student WHERE score80)然后主查询直接用 t代替嵌套子查询。2.核心优势可将复杂的子查询拆解为独立的CTE模块使SQL代码逻辑更清晰、更易于维护尤其适用于子查询嵌套层数较多的场景能显著提升代码可读性。(可类比为宏定义十六.窗口函数WINDOW FUNCTIONS1.核心特点窗口函数能对一组数据进行计算但不会像聚合函数那样合并行而是每行都保留原数据并新增计算结果。2.窗口函数与聚合函数的区别聚合函数如AVG、COUNT将一组数据聚合为单一结果例如按课程分组后仅得到每门课程的平均绩点每组对应一个结果。窗口函数不改变原始数据的行数保留所有记录仅对每一行数据计算其所在“窗口”的统计结果例如为每门课程的学生按成绩排名每一行数据均对应一个排名值不丢失任何原始记录。3.常用窗口函数比如 ROW_NUMBER 给每行标序号RANK 有并列排名会跳号DENSE_RANK 并列不跳号聚合类像 SUM、AVG 可以按窗口范围计算比如SUM (score) OVER (PARTITION BY class_id)能算出每个学生所在班级的总分。使用时用 OVER () 指定窗口PARTITION BY 分组ORDER BY 排序。比如 “按班级分组给学生成绩排名”用ROW_NUMBER () OVER (PARTITION BY class_id ORDER BY score DESC)就能实现。