博客主页小谢同学的小破站✍️ 本文由小谢同学的小破站原创首发于 CSDN☕ Java学习专栏java MySQL专栏MySQL 数据结构与算法Java 数据结构与算法C语言专栏: C语言 欢迎点赞 收藏⭐ 留言发现错误欢迎指正✨ 脚踏实地持续深耕奔赴自己的目标✨– – – – – 分割线----------MySQL 进阶笔记用户 / 会话 / 全局 / 局部变量 CASE 分支语法:1. 四大变量1.1 系统变量1.2 用户自定义变量1.3 局部变量2. SQL编程2.1 IF语句2.2 CASE语句前言:这里是小谢同学的 MySQL 学习记录整理全局、会话、局部变量以及 SQL 编程语法。笔记用于自我复盘巩固有错误欢迎大家指出专栏还有 Java、网络、C 语言系列笔记欢迎翻阅1. 四大变量变量类型声明方式作用域生命周期查看系统变量系统定义好的全局/会话服务器运行期间global.xxx / session.xxx用户自定义变量var当前会话会话断开即销毁var局部变量DECLARE存储过程 / 函数内部代码块结束即销毁直接用变量名1.1 系统变量系统变量又分为:全局变量与会话变量全局变量(Global):对所有新的连接都生效,修改后已经存在的连接不受影响会话变量(Session):只对当前连接有效,断开连接需要重新部署例如大楼统一规定上班时长、门禁规则只要进这个大楼所有人都要遵守这套规则。修改global全局变量:可以形象的理解为修改的是公司制度新进来的员工才会收到新规则的约束而老员工是已经存在的会话不会立即生效当MySQL重新启动之后,全局变量会默认恢复(相当于大楼断电重启,临时修改的制度表作废)会话变量session 每个员工自己的个人专属设置每个客户端连接相当于一个独立员工。例如员工在擦自己桌子时不会影响到别人,喝水同理修改会话变量只会对当前一对话生效,别的连接完全不受影响连接断开会直接销毁,设置全部消失设置与查看系统变量:-- 查看所有系统变量SHOWGLOBALVARIABLES;showSESSIONVARIABLES;-- 查看指定的系统变量采用模糊查询的形式selectGLOBAL.系统变量名;selectSESSION.系统变量名;-- 不带前缀默认查 session 级SELECTautocommit;-- 查询auto开头的全局变量与查看以char结尾的会话系统变量SHOWGLOBALVARIABLESLIKEauto%;SHOWSESSIONVARIABLESLIKEchar%;-- 修改(会话级别,当前会话生效)SETSESSIONautocommit0;-- 修改全局SETGLOBALautocommit0;1.2 用户自定义变量用户自定义变量是由用户自己定义的,会话级别的变量,使用前缀作为标识,相当于当前会话的临时记事本;复制方式分为两种:一种为进行赋值,或者:进行赋值(更推荐使用:这样更加明确是赋值操作而不是判断相等)使用SET进行赋值-- 方式一:SETname:张三;SETage18;selectname,age;使用SELECT进行赋值-- 方式二selectcount:COUNT(*)fromstudent;selectcount;不需要声明变量的类型,赋值时会自行进行判断没有赋值的变量值为null作用域: 只在当前会话中有效1.3 局部变量局部变量这个概念和我们之前学习的C语言和Java中类似,只不过MySQL中局部变量是存在于存储过程或者函数内部声明的变量,内部使用DECLARE来标识局部变量的使用与赋值:DELIMITER//CREATEPROCEDUREp1()BEGIN-- 声明DECLARExINTDEFAULT0;DECLAREnamevarchar(20);SETx :100;SETname :张三;SELECTx,name;END//DELIMITER;CALLp1();注意要点:DECLARE必须写在BEGIN…END块的前面必须指定类型(强类型),用户自定义变量为弱类型作用域:只在BEGIN …END内部2. SQL编程2.1 IF语句结构化查询语言(StructuredQueryLanguage)简称为SQL,是一种特殊的编程语言,其中SQL也包含条件判断,循环等等功能;IF语句:核心语法:IF条件1THEN-- 语句ELSEIF条件2THEN-- 语句ELSE-- 语句ENDIF;示例:根据小萌的期末成绩来判断成绩等级:DELIMITER//CREATEPROCEDUREp2(INscoreINT)BEGINIFscore90THENSELECT优秀;ELSEIFscore60THENSELECT及格;ELSESELECT不及格;ENDIF;END//DELIMITER;CALLp2(99);注意点:与C和JAVA想必’LSEIIF是一个单词而不是ELSE IF结束条件为END IF(中间带有空格),如果没有结束条件,程序则不知道何时结束存储过程里的IF 语句vs 普通查询里的IF(条件, 值1, 值2)函数2.2 CASE语句多分支选择比多层IF更清晰。有两种写法且既能当语句用存储过程里也能当表达式用SELECT 里。第一种简单的写法:简单的值匹配例如:小萌在点外卖,买一号商品为:果汁,2号商品为猪脚饭,3号商品为桂林米粉DELIMITER//CREATEPROCEDUREp3(INidINT,OUTfoodvarchar(20))BEGINCASEidWHEN1THENSETfood :果汁;WHEN2THENSETfood :猪脚饭;WHEN3THENSETfood :桂林米粉;ENDCASE;END//DELIMITER;CAllp3(1,food);SELECTfood;第二种写法:搜索CASE还是以成绩为例子:DELIMITER//CREATEPROCEDUREp4(INscoreINT,OUTleverVARCHAR(20))BEGINCASEWHENscore90ANDscore100THENSETlever :优秀;WHENscore60ANDscore90THENSETlever :及格;ELSESETlever :非法参数;ENDCASE;END//DELIMITER;CALLp4(99,lever);SELECTlever;注意事项:当表达式用结尾是END当语句用存储过程里结尾是END CASEWHEN按顺序匹配命中第一个就停止所以条件顺序很重要ELSE可以省略省略后如果都不匹配返回NULL经典面试题:行转列我们首先准备一张表插入并插入数据:-- 创建成绩表CREATETABLEscore(idINTPRIMARYKEYAUTO_INCREMENT,student_nameVARCHAR(20),subjectVARCHAR(20),scoreINT);INSERTINTOscore(student_name,subject,score)VALUES(张三,语文,88),(张三,数学,95),(张三,英语,78),(李四,语文,76),(李四,数学,82),(李四,英语,90);目标效果:student_name语文数学英语张三889578李四768290代码实现:SELECTstudent_name,SUM(CASEWHENsubject语文THENscoreELSE0END)AS语文,SUM(CASEWHENsubject数学THENscoreELSE0END)AS数学,SUM(CASEWHENsubject英语THENscoreELSE0END)AS英语FROMscoreGROUPBYstudent_name;逻辑梳理:我们需要查询的是每一个人的成绩第一步先执行 CASE WHEN还没分组、没求和MySQL 先一行一行扫描整张表对每一行都执行 3 个 CASE 判断。此时到这里就拿到了张三的每一条分散的记录第二步GROUP BY student_nameGROUP BY student_name告诉数据库把名字相同的所有行归为一组。⚠️ 坑点来了分组之后一组里面有多条数据不能直接返回多条必须压缩成 1 行所以必须使用聚合函数SUM() / MAX()第三步SUM () 聚合收拢多行合并成一行