目录开头一.DMLData Manipulation(操作) Language—— CURD1. Create(新增) insert 数据插入1两种插入形式a. 单行全列插入b. 多行指定列插入2插入冲突的两种解决方案a.on duplicate key updateb. replace into2.Retrieve (检索)SELECT 数据查询1查询列的控制a.全列查询b.指定列查询c.表达式查询d.别名 ASe.结果去重distinct2where 条件过滤行a.比较运算符b.逻辑运算符3ORDER BY结果排序4 limit分页筛选3.Update (更新)4.Delete删除1delete 删除2truncate 截断表5.插入查询结果6. 聚合统计7. group by 分组查询1简单分组2having : 分组后过滤8. SQL执行顺序 重要二. 内置函数1. 日期时间函数1获取当前时间类a. current_date()b.current_time()c. now() / current_timestamp()2日期提取与计算a.date(datetime)b.date_add(date,interval 值 单位)c. date_sub(date,interval 值 单位)d.datediff(date1,date2)2.字符串函数a.concat(str1,str2,....) 字符串拼接b.length(str) 所占字节c.substring(str,起始位置长度)字符串截取d.instr(str,子串)查询子串e.replace(str,旧子串新子串)替换子串f. ucase / lcase 大小写替换g.left / right 截取h.空格处理i.charset(str)3.数学函数4.其他常用函数a.ifnull(val1,val2)b.md5(str)c.password(str)d.user()e.databse()三. 复合查询1.多表查询1笛卡尔积多表查询的底层本质2等值连接内连接3表别名的使用2.自连接3.子查询嵌套查询1多行子查询2多行子查询a.in等于其中任意一个b.all 比所有都...c.any比任意一个...3多列子查询4from 子句中的子查询派生表4.合并查询四.表的内外连接1. 内连接 inner join2.外连接 outer join1左外连接 left join2右外连接 right join结尾往期回顾开头Ok了上一期我们认识了什么是MySQL学习了相关的DDL操作、数据类型、表的约束。下面我们趁热打铁接着学习DML内置函数、复合查询。这部分是 MySQL 最核心的数据操作与查询能力也是实际开发中使用频率最高的内容一.DMLData Manipulation(操作) Language—— CURD1. Create(新增) insert 数据插入用于向数据表中新增数据行支持单行、批量、冲突处理等多种形式基本语法INSERT [INTO] 表名 [(列名1, 列名2, ...)] VALUES (值列表1), (值列表2), ...核心规则列名可省略省略时默认按表定义的列顺序匹配所有列——全列插入值列表的数量、顺序、数据类型必须与指定列一一对应。未指定的列会自动填充默认值无默认值则填充 NULL1两种插入形式下面用这个作为例子a. 单行全列插入不指定列名按表的列顺序给所有列赋值。-- 示例向 students 表插入完整的一行 INSERT INTO students VALUES (100, 10000, 唐三藏, NULL);注意自增主键可以手动赋值也可以填 NULL 让系统自动生成b. 多行指定列插入指定要插入的列一次插入多行数据是生产环境推荐的批量写入方式性能远高于逐条插入。-- 示例只插入 id、sn、name 三列一次插入两行 INSERT INTO students (id, sn, name) VALUES (102, 20001, 曹孟德), (103, 20002, 孙仲谋);2插入冲突的两种解决方案当插入数据的主键或唯一键与表中已有数据重复时普通 INSERT 会直接报错。针对冲突场景有两种成熟的处理方案a.on duplicate key update冲突时不报错转而更新指定字段的值。-- 主键冲突时更新 sn 和 name 字段 INSERT INTO students (id, sn, name) VALUES (100, 10010, 唐大师) ON DUPLICATE KEY UPDATE sn 10010, name 唐大师;执行结果通过ROW_COUNT()判断1无冲突数据成功插入2有冲突数据已更新0有冲突但更新后的值和原值完全相同b. replace into冲突时先删除原行再插入新行相当于「删除 插入」的原子操作REPLACE INTO students (sn, name) VALUES (20001, 曹阿瞒);执行结果1无冲突直接插入2有冲突删除旧行 插入新行注意REPLACE 会触发删除触发器且自增主键会重新生成生产环境需谨慎使用。2.Retrieve (检索)SELECT 数据查询查询是 SQL 中最核心、最常用的操作完整语法结构SELECT [DISTINCT] 列/表达式 FROM 表名 [WHERE 条件] [GROUP BY 分组] [HAVING 分组过滤] [ORDER BY 排序] [LIMIT 分页]下面从「列控制 → 行筛选 → 排序 → 分页」逐层讲解。1查询列的控制下面以这个表作为例子往里面插入了一些数据a.全列查询SELECT * FROM exam_result;⚠️ 生产环境禁止使用*查询列越多数据传输量越大性能差可能导致索引失效表结构变更时查询结果不稳定b.指定列查询自定义返回的列与顺序c.表达式查询支持常量、单字段运算、多字段组合运算d.别名 AS给查询结果的列起别名AS关键字可以省略SELECT id, name, chinese math english 总分 FROM exam_result;e.结果去重distinct去除查询结果中完全重复的行SELECT DISTINCT math FROM exam_result;2where 条件过滤行WHERE 是行级过滤只有满足条件的行才会进入后续查询。a.比较运算符 重点NULL 的比较普通无法判断 NULLWHERE qq NULL查不到任何数据判断空值必须用IS NULL。是 MySQL 特有的安全等于可以同时判断普通值和 NULLb.逻辑运算符下面以这个为例出几个题来看看英语不及格的同学及英语成绩语文成绩在 [80, 90] 分的同学及语文成绩数学成绩是 78 或者 73 或者 98 的同学及数学成绩姓孙的同学及孙某同学姓孙的% 匹配任意多个包括 0 个任意字符孙某同学_ 匹配严格的一个字符总分在 200 分以下的同学别名不能用在 WHERE 条件中语文成绩 80 并且不姓孙的同学3ORDER BY结果排序ORDER BY 列1 [ASC|DESC], 列2 [ASC|DESC], ...ASC升序从小到大默认值DESC降序从大到小多字段排序按书写顺序优先级先按第一列排相同再按第二列排特点与注意事项NULL 的排序NULL 视为比任何值都小升序时排在最前面降序时排在最后面支持按表达式、别名排序不加 ORDER BY 的查询返回顺序是不确定的永远不要依赖默认顺序查询同学及总分由高到低ORDER BY 子句中可以使用列别名查询姓孙的同学或者姓曹的同学数学成绩结果按数学成绩由高到低显示4 limit分页筛选-- 取前n条 LIMIT n -- 从位置s开始取n条s从0开始计数 LIMIT s, n -- 更清晰的写法推荐 LIMIT n OFFSET s3.Update (更新)UPDATE 表名 SET 列1 值1, 列2 值2, ... [WHERE 条件] [ORDER BY 排序] [LIMIT 数量];要点说明可以更新单列或多列逗号分隔更新值可以是固定值也可以是基于原值的表达式如math math 30没有 WHERE 条件会更新整张表生产环境必须慎用MySQL 不支持math 30这种简写必须写完整的math math 30将全部人的数学成绩加10分将姓孙同学的数学成绩变更为 100 分** 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分**4.Delete删除1delete 删除DELETE FROM 表名 [WHERE 条件] [ORDER BY 排序] [LIMIT 数量];按条件删除指定行没有 WHERE 则删除全表数据属于 DML 语句走事务可以回滚删除后不会重置自增计数器后续插入的自增 ID 继续递增2truncate 截断表TRUNCATE [TABLE] 表名;只能整表操作不能按条件删除部分数据本质是直接重建表不是逐行删除速度远快于 DELETE属于 DDL 语句不走事务数据不可回滚会重置自增计数器自增 ID 从 1 重新开始DELETE vs TRUNCATE 核心对比5.插入查询结果可以将 SELECT 查询的结果直接插入到另一张表中INSERT INTO 目标表 [(列1, 列2, ...)] SELECT ... FROM 源表;经典场景表数据去重-- 1. 创建一张结构相同的空表 CREATE TABLE no_duplicate_table LIKE duplicate_table; -- 2. 将去重后的数据插入新表 INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table; -- 3. 原子重命名完成替换 RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table;6. 聚合统计聚合函数对一组行进行统计计算最终返回一个结果都可以搭配DISTINCT进行去重统计如COUNT(DISTINCT math)统计班级共有多少同学统计本次考试的数学成绩分数个数统计数学成绩总分统计平均总分返回英语最高分返回 70 分以上的数学最低分7. group by 分组查询按指定列的值将数据分组每组单独计算聚合结果。SELECT 分组列, 聚合函数... FROM 表名 [WHERE 条件] GROUP BY 分组列1, 分组列2, ... [HAVING 分组后条件];1简单分组-- 每个部门的平均工资、最高工资 SELECT deptno, AVG(sal), MAX(sal) FROM EMP GROUP BY deptno; -- 每个部门、每个岗位的平均工资 SELECT deptno, job, AVG(sal), MIN(sal) FROM EMP GROUP BY deptno, job;2having : 分组后过滤HAVING用于对分组后的结果进行过滤必须配合GROUP BY使用示例-- 显示平均工资低于2000的部门和平均工资 SELECT deptno, AVG(sal) avg_sal FROM EMP GROUP BY deptno HAVING avg_sal 2000;WHERE vs HAVING 核心区别8. SQL执行顺序 重要可以把 SQL 查询想象成「工厂加工产品」FROM把原材料原始数据运到车间WHERE质检筛选挑出合格的原材料GROUP BY 聚合把原材料分组加工成半成品HAVING对半成品做质检筛选SELECT给成品贴标签、印产品名生成别名ORDER BY按标签上的产品名给成品排序LIMIT装箱只取前 N 件二. 内置函数1. 日期时间函数日期函数是业务中使用频率最高的一类函数用于处理时间的获取、计算、提取等操作是按时间统计、时间范围判断的核心工具1获取当前时间类a. current_date()功能获取当前系统的日期格式为YYYY-MM-DD语法CURRENT_DATE()示例b.current_time()功能获取当前系统的日期格式为YYYY-MM-DD语法CURRENT_DATE()示例c. now() / current_timestamp()功能获取当前系统的完整日期 时间格式为YYYY-MM-DD HH:MM:SS说明二者功能完全等价是同义词示例2日期提取与计算a.date(datetime)功能从 datetime 类型的值中提取日期部分丢弃时分秒典型场景按天统计数据、列表只展示日期不展示时间b.date_add(date,interval 值 单位)功能在指定日期 / 时间的基础上增加指定的时间量支持单位year年、month月、day天、hour时、minute分、second秒示例c. date_sub(date,interval 值 单位)功能在指定日期 / 时间的基础上减去指定的时间量用法和 DATE_ADD 完全对称示例d.datediff(date1,date2)功能计算两个日期之间相差的天数结果为date1 - date2注意只计算日期部分的差值忽略时分秒结果可正可负示例2.字符串函数a.concat(str1,str2,…) 字符串拼接功能按顺序拼接多个字符串合并为一个完整字符串易错点任意一个参数为 NULL最终拼接结果就为 NULL使用时注意做空值处理示例-- 格式化输出学生成绩信息 SELECT CONCAT(name, 的语文是, chinese, 分数学是, math, 分) AS 分数 FROM exam_result;b.length(str) 所占字节功能返回字符串的字节长度不是字符个数关键说明单字节字符英文、数字、符号1 个字符占 1 字节多字节字符中文占用字节数由字符集决定utf8 编码下 1 个中文占 3 字节注意如果需要获取字符个数使用CHAR_LENGTH()函数不要和 LENGTH 混淆c.substring(str,起始位置长度)字符串截取功能从字符串的指定位置开始截取指定长度的子串易错点MySQL 中字符串位置从 1 开始计数不是编程常见的 0省略长度参数时会直接截取到字符串末尾d.instr(str,子串)查询子串功能查找子串在原字符串中第一次出现的位置返回值找到返回对应位置从 1 开始找不到返回 0e.replace(str,旧子串新子串)替换子串功能将字符串中所有匹配的旧子串全部替换为新子串示例-- 将员工姓名中所有的S替换成上海 SELECT REPLACE(ename, S, 上海), ename FROM EMP;f. ucase / lcase 大小写替换UCASE(str)/UPPER(str)将字符串全部转为大写LCASE(str)/LOWER(str)将字符串全部转为小写g.left / right 截取left(str, 长度)从字符串最左侧开始截取指定长度的子串right(str, 长度)从字符串最右侧开始截取指定长度的子串h.空格处理ltrimstr)去除字符串左侧的空格rtrim(str)去除字符串右侧的空格trim(str)去除字符串首尾两侧的所有空格i.charset(str)功能返回字符串使用的字符集3.数学函数ceiling 与 floor 的区别正数场景CEILING 向前进 1FLOOR 直接舍弃小数负数场景CEILING(-23.7) -23FLOOR(-23.7) -24注意取整方向format 函数注意点不仅会四舍五入还会自动添加千分位分隔符返回结果为字符串类型rand 扩展用法生成 0~100 的随机整数FLOOR(RAND() * 100)生成 m~n 之间的随机数m RAND() * (n - m)4.其他常用函数a.ifnull(val1,val2)功能空值处理函数。如果 val1 为 NULL返回 val2否则返回 val1 本身地位开发中最高频的函数之一用于避免 NULL 值参与运算导致结果异常示例SELECT IFNULL(abc, 123); -- 结果abc SELECT IFNULL(NULL, 123); -- 结果123b.md5(str)功能对字符串进行 MD5 摘要运算返回一个 32 位的十六进制字符串使用场景常用于密码加密、数据一致性校验示例SELECT MD5(admin); -- 返回结果21232f297a57a5a743894a0e4a801fc3c.password(str)功能MySQL 自带的专属密码加密函数是 MySQL 内部用户账号使用的加密算法注意这是 MySQL 专属的加密方式不要用于业务系统的用户密码加密d.user()功能返回当前登录 MySQL 的用户名和主机地址e.databse()功能返回当前正在使用的数据库名三. 复合查询单表查询只能处理单一数据表的数据但实际业务中数据往往分散在多张关联表中这时就需要用到复合查询。下面给出一个具体的例子下面都会围绕这个例子来学习后面的内容构建了一个数据库scott里面放了三张表dept:部门编号部门名工作地点emp:员工编号员工名职位主管编号入职时间薪资奖金部门编号salgrade薪资等级最低最高1.多表查询1笛卡尔积多表查询的底层本质当 FROM 后面跟多张表且不加任何连接条件时会产生笛卡尔积第一张表的每一行都会和第二张表的所有行组合一次最终结果行数 表 1 行数 × 表 2 行数其实就是一种穷举如下图部分结果所有多表查询都必须加连接条件用来过滤掉笛卡尔积中的无效组合只保留关联匹配的行2等值连接内连接通过两张表的关联字段值相等来匹配数据是最常用的多表查询方式语法格式SELECT 列名... FROM 表1, 表2 WHERE 表1.关联列 表2.关联列 [AND 其他过滤条件];显示部门号为10的部门名员工名和工资显示各个员工的姓名工资及工资级别3表别名的使用当查询涉及多张表时推荐给表起别名简化书写同时避免同名字段冲突。规则表名 别名之后所有字段都可以用别名.列名引用注意一旦给表起了别名就不能再用原表名引用字段-- 用别名简化写法 SELECT e.ename, e.sal, d.dname FROM EMP e, DEPT d WHERE e.deptno d.deptno;2.自连接自连接就是一张表自己和自己连接查询本质还是多表连接只不过两张表是同一张表的不同角色副本作用用于查询表内自身存在层级关系的数据最典型的就是员工 - 上级关系EMP 表中每个员工有mgr字段上级编号而上级本身也是员工也在 EMP 表中显示员工FORD的上级领导的编号和姓名使用子查询使用多表查询自连接3.子查询嵌套查询子查询是指嵌入在其他 SQL 语句中的 SELECT 语句也叫嵌套查询。外层的查询叫主查询内层的查询叫子查询执行顺序通常先执行子查询得到结果后再执行主查询子查询可以出现在 WHERE、FROM、SELECT、HAVING 等子句中1多行子查询返回结果一行一列单个值搭配运算符、、、、、!等比较运算符显示SMITH同一部门的员工2多行子查询返回结果多行一列多个值的集合搭配运算符IN、ALL、ANYa.in等于其中任意一个查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自己的b.all 比所有都…判断字段值要大于 / 小于子查询结果中的每一个值。 ALL(...) 大于最大值 ALL(...) 小于最小值显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号c.any比任意一个…判断字段值只要大于 / 小于子查询结果中的任意一个值即可。 ANY(...) 大于最小值 ANY(...) 小于最大值显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号包含自己部门的员工3多列子查询返回结果一行多列语法特点主查询的比较条件必须用括号包裹多个列列的顺序、数量、类型必须和子查询完全一致查询和 SMITH 的部门、岗位都完全相同的所有雇员不含 SMITH 本人4from 子句中的子查询派生表特点子查询出现在 FROM 子句中将查询结果当作一张临时表来使用强制要求必须给子查询的结果起别名否则主查询无法引用适用场景把聚合计算的结果作为中间表再和其他表关联查询显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资查找每个部门工资最高的人的姓名、工资、部门、最高工资显示每个部门的信息部门名编号地址和人员数量4.合并查询将多个 SELECT 语句的查询结果纵向拼接成一个结果集。和连接不同连接是横向加列合并是纵向加行。多个 SELECT 语句的结果必须满足列数相同对应列的数据类型兼容最终结果的列名以第一个 SELECT 语句的列名为准union 和 union all 的区别将工资大于2500或职位是MANAGER的人找出来四.表的内外连接1. 内连接 inner join只显示两边表条件匹配成功的数据两边都有才会出结果不匹配的直接丢弃。我们之前写的from 表1,表2 where 条件就是隐式内连接语法select 字段 from 表1 inner join 表2 on 连接条件 [and 其他筛选条件];on写表之间关联条件and追加过滤条件。案例查询 SMITH 员工名字和所在部门名称-- 写法1老式隐式内连接逗号分隔表 where select ename, dname from EMP, DEPT where EMP.deptnoDEPT.deptno and enameSMITH; -- 写法2标准inner join显式内连接 select ename, dname from EMP inner join DEPT on EMP.deptnoDEPT.deptno and enameSMITH;内连接特点如果某个员工没有部门、某个部门没有员工这条记录不会查询出来2.外连接 outer join外连接会保留某一张表全部数据另一张表匹配不上字段填NULL。分为左外连接、右外连接MySQL 中left join等价left outer joinouter 可以省略。1左外连接 left join左边表全部数据全部展示右边表匹配不到的地方填 NULLselect 字段 from 表1 left join 表2 on 连接条件;2右外连接 right join右边表全部数据全部展示左边表匹配不到填 NULL语法select 字段 from 表1 right join 表2 on 连接条件;结尾这一期对于MySQL的学习就到这里了希望对你的学习有所帮助。感谢你的点赞、关注。我主页里有更好康的呦往期回顾【MySQL】 第1期 从核心概念到表约束全解析【Linux】第16期 讲解数据链路层、ARP、NAPT 与内网穿透【Linux】第15期 讲解IP网络层核心原理与机制