MySQL-笔记-08.数据库编程

目录

8.1 编程基础

8.1.1 基本语法

8.1.2 运算符与表达式

1. 标识符

2. 常量

（1） 字符串常量

（2）日期时间常量

（3）数值常量

（4）布尔值常量

（5）NULL值

3.  变量

（1）用户变量

        ① SET语句

        ② SELECT … INTO语句

（2）系统变量

（3）局部变量

4. 运算符

（1）算术运算符

 （2）位运算符

 （3）比较运算符

 （4）逻辑运算符

 （5）运算符的优先级

8.1.3 语句块和注释

1. 语句块与BEGIN…END语句

2. 注释

3. DELIMITER命令

8.1.4 流程控制语句

1.  分支语句

（1）if-then-else语句

 （2）CASE语句

 2.  循环语句

（1）while语句

（2）REPEAT语句

（3）LOOP语句

8.2 函数使用

1. 内置函数

 （1）数学函数

（2）字符串函数

 （3）日期和时间函数

（4）其他函数

2. 用户定义函数

(1)  创建函数

(2)  调用函数

(3)  删除函数

(4)  查看函数的状态

(5)  查看函数定义

---

8.1 编程基础

8.1.1 基本语法

MySQL语句是组成MySQL脚本的基本单位，每条语句能完成特定的操作：

以分号“；”结尾：在关系型数据库中，语句是逐条执行的，每条语句能完成特定的操作，通常要在句子的句尾使用分号“；”结尾。

不区分大小写：SQL不区分关键字的大小写，但是为了理解方便，通常关键字大写，数据库名、表名和列名等小写。需要注意：插入到表中的数据是区分大小写的。如：a123和A123是不一样的数据。

英文状态下的符号：SQL语句的单词之间必须使用英文空格或换行符来进行分隔，所有的标点符号都必须是英文状态下的符号，否则会发生错误。

8.1.2 运算符与表达式

        运算符是一种符号，通过运算符连接运算量构成表达式。简单表达式可以是一个常量、变量、列或标量函数。可以用运算符将两个或更多的简单表达式连接起来组成复杂表达式。运算符用来指定要在一个或多个表达式中执行的操作：

1. 标识符

• 标识符是用户编程时使用的名字。
• 每一个对象都由一个标识符来唯一地标识。
• 对象标识符是在定义对象时创建的，该标识符随后用于引用该对象。
• 一般由字母、数字或下划线( _ )组成，且第一个字符必须是字母或下划线。

2. 常量

        常量是指在程序中可以直接引用的量，其值在程序运行期间保持不变，它的表示形式决定了其数据类型。常量可分为数值常量、字符串常量、日期时间常量、布尔常量和NULL值常量等。

（1） 字符串常量

        字符串常量是用单引号或双引号括起来的字符序列。在MySQL中推荐使用单引号，若字符串中本身有单引号字符，则单引号要用两个单引号来表示。如：'China'、'O''Brien'、'X+Y='均为字符串常量。

（2）日期时间常量

        datetime常量使用特定格式的字符日期值表示，用单引号括起来。如：’2024/02/21'、’2024-02-21 21:32:45'。

（3）数值常量

        数值常量由数字组成，可以分成整数常量和实数常量。 ① 整数常量是不带小数点的十进制整数。如156、-100。 ② 实数常量是包含小数点的数值常量。如3.14、-100.23、2.67E5

（4）布尔值常量

        布尔常量只有TURE和FALSE两个值。TURE对应的数值为“1”；FALSE对应的数值为“0”

（5）NULL值

        NULL值适用于各种类型，表示没有值或无数据，不等价于空字符串或数据0值。允许空值意味着用户在向表中插入数据时可以忽略该列值。空值可以表示整型、实型、字符型数据等

3.  变量

        变量是指在程序运行期间取值可以变化的量，用于临时存储数据，变量中的数据随着程序的运行而变化。

• 一个变量有2个基本要素：变量名和变量的数据类型。
• 每个变量都用唯一的变量名来标识，用户可以通过变量名来访问内存中的数据，变量的数据类型决定了变量的值和对应的运算。
• MySQL变量可分为用户变量、系统变量和局部变量。

（1）用户变量

        用户使用变量定义语句定义的变量称为用户变量。

        ① SET语句

        可以使用SET语句来定义局部变量，并为其赋值。SET语句的语法格式如下：

SET @local_variable1=express1[,@local_variable2= express2,…];

 说明：

• 在用户变量前添加@符号，便于区分变量名和字段名称。
• 用户变量名可以包含字母、数字、“.”“_”和“$”。
• 定义多个用户变量时，每个用户变量之间用逗号分隔。
• 表达式是赋给用户变量的值，可以是常量、变量或表达式。

        ② SELECT … INTO语句

        SELECT…INTO语句可以将查询得到的一行结果中的字段值赋值给对应的用户变量，语法格式如下：

SELECT 字段1 [ ,字段2 …]  INTO  @用户变量1 [ , @用户变量2 … ]
FROM 表名
WHERE 条件;
或者
SELECT @用户变量1 := 表达式1

【例4.1】求两个变量的和。

SET @a=1, @b=2, @sum=0;     
SET @sum=@a+@b;
SELECT @sum;

【例4.2】查询全体常数人数，并保存到变量@num中。（以下表示3种方式）

SET @num=(SELECT COUNT(*) FROM student);
SELECT @num;

SELECT COUNT(*) INTO @num FROM student;
SELECT @num;

SELECT @num:=(SELECT COUNT(*) FROM student);

（2）系统变量

        系统变量是MySQL的一些特殊设置，当MySQL数据库服务器启动时，初始化这些变量为默认值。大多数系统变量名称前都需要加两个@，某些特定的系统变量不加这两个@，如CURRENT_DATE（当前系统日期）、CURRENT_TIME（当前系统时间）、CURRENT_USER（当前用户名称）等。

【例4.3】查看当前系统日期和使用的MySQL的版本信息。

SELECT CURRENT_DATE AS 当前日期,@@version AS 当前版本;

（3）局部变量

        局部变量的作用范围是BEGIN … END语句块中，用来存放存储过程体中的临时结果。

        局部变量只能定义在存储过程、存储函数和触发器中。可以使用declare语句来声明局部变量，DECLARE语句的语法格式如下：

DECLARE  local_variable1[,local_variable2,...] data_type [DEFAULT value]; 

 局部变量和用户变量的主要区别：

• 作用范围不同，用户变量存在于整个会话中，局部变量只存在于BEGIN … END语句块中。
• 用户变量前有@符号，局部变量前没有@符号。
• 局部变量使用前需用DECLARE语句声明，并指明数据类型。
• 局部变量作为存储过程或存储函数的形式参数时，无需声明，但需指定数据类型。

4. 运算符

        MySQL语言运算符共有4类，即算术运算符、位运算符、比较运算符和逻辑运算符。由运算符把操作数连接起来的式子称为表达式。

（1）算术运算符

        算术运算符用于对两个表达式进行数学运算，这两个表达式可以是任何数值类型。

 （2）位运算符

        位运算符用于对数据进行按位与（&）、或（|）、异或（^）、取反（~）等运算。在MySQL语句中进行整型数据的位运算时，先将它们转换为二进制数，然后进行计算。其中与、或、异或运算符需要两个操作数，而求反运算符仅需要一个操作数。

 （3）比较运算符

        比较运算符用来比较两个表达式的值，可用于字符、数字或日期数据。MySQL中的比较运算符有大于（>）、小于（<）、大于等于（>=）、小于等于（<=）、不等于（!= 或 < >）和等于（=）等等，比较运算返回布尔值，通常出现在条件表达式中。

 （4）逻辑运算符

        逻辑运算符有与（AND）、或（OR）、非（NOT）和异或（XOR）等，用于对某个条件进行测试，以获得其真实情况。逻辑运算符和比较运算符一样，返回TRUE或FALSE的布尔数据值。

 （5）运算符的优先级

        当一个复杂的表达式有多个运算符时，运算符优先级决定运算执行的先后次序。执行的顺序有时会影响所得到的运算结果。MySQL运算符的优先级如表所示，在一个表达式中，按运算符优先级先高（优先级数字小的）后低（优先级数字大的）的顺序进行运算。

优先级	运算符	优先级	运算符
1（最高）	!	8	|（位或）
2	+（正）、 -（负）、 ～（位取反）	9	=、<>、!=、<、<=、>、>=、<=>、 Is、Like、In（比较运算）
3	^（位异或）	10	Between、Case、While、Then、Else
4	*、/、%	11	Not
5	+（加）、 -（减）	12	And、&&
6	<<、>>	13	Or、||
7	&（位与）	14（最低）	=、:=（赋值运算）

8.1.3 语句块和注释

1. 语句块与BEGIN…END语句

（1）语句块

        是由若干条语句构成的程序代码单元，在逻辑上被当作一个整体，语句块中的语句要么被执行，要么整体都不被执行。

（2）BEGIN...END语句

        BEGIN...END用来设定一个语句块，将BEGIN...END中的所有语句视为一个逻辑单元执行。语句块BEGIN...END的语法格式为：

BEGIN 
    { mysql_statement \ mysql_statement_block }
END

2. 注释

   三类注释符

（1）-- 单行注释，注意-- 后有一个空格。

（2）# 单选注释。

（3）/**/ 多行注释

3. DELIMITER命令

        在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。 但有时候，一次执行多条sql命令，此时必需改变结束符。

         DELIMITER命令可以重新定义代码执行的结束符。

语法结构：DELIMITER 结束符 可以使用“$$ ”“## ”等特殊的符号为结束符，注意避免使用MySQL中的转义字符“\ ” 。

比如：

        DELIMITER $$表示新的定界符为“$$”，自此开始，直到遇到下一个“$$”，MySQL才会整体执行这段语句代码。

        如果想恢复使用分号“;”作为结束符，则需要执行如下的语句。 DELIMITER ;

DELIMITER $$
	BEGIN
		SELECT sno,sname,ssex FROM student WHERE ssex='男';
		SELECT sno,sname,ssex FROM student WHERE ssex='女';
	END
$$
DELIMITER ;

8.1.4 流程控制语句

        在存储过程和存储函数中可以使用流程控制语句来控制程序的流程。在MySQL中，流程控制语句大致分为两类：一类用于实现分支结构，另一类用于实现循环结构。

        本任务将主要针对分支结构与循环结构的实现进行详细的讲解。

1.  分支语句

（1）if-then-else语句

        可以根据不同的条件执行不同的操作，语法格式：

IF 条件1  THEN语句序列1
[ ELSEIF 条件2  THEN 语句序列2 ]
…
[ ELSE 语句序列n ]
END IF;

 IF语句执行流程：

• 先计算条件的值。
• 当某个条件值为真（TRUE）时，则执行相应的语句序列；
• 如果没有一个条件值为真，则执行ELSE中的语句序列n。

 【例4.7】查询课程表course中“数据库应用”课程的学时数chours，如果查询结果为空，则显示“无学时数信息”，否则显示学时数。

DELIMITER ##   
CREATE PROCEDURE GetHoursForDatabaseCourse()  
BEGIN      
	DECLARE vhour INT;      
	SELECT chour INTO vhour FROM course WHERE cname = '数据库应用';
	IF vhour IS NULL THEN 
		SELECT '无学时数信息' AS 学时数;      
	ELSE         
		 SELECT vhour AS 学时数;     
 	END IF; 	 
END ##    
DELIMITER ;

 （2）CASE语句

        CASE是另一种分支语句，有两种语法格式。

CASE语句格式一：

CASE 表达式
     WHEN 值1 THEN 语句序列1
     [ WHEN 值2 THEN 语句序列2 ]
      …
     [ ELSE 语句序列n ]
END CASE;

CASE语句格式一执行流程如下：

• 先计算出表达式的值然后与WHEN …  THEN 语句块中的值进行比较
• 如果某个值比较的结果为真，则执行对应语句序列中的语句
• 如果每一个语句块中的值都不匹配，则执行ELSE语句序列n中的语句

 CASE语句格式二：

CASE 
     WHEN 条件1 THEN 语句序列1
     [ WHEN 条件2 THEN 语句序列2 ]
      …
     [ ELSE 语句序列n ]
END CASE;

 CASE语句格式二执行流程如下：

• CASE关键字后没有参数。在WHEN … THEN 语句块中指定一个条件
• 如果条件的结果为真时，则执行对应语句序列中的语句
• 如果每一个语句块中的条件表达式均不为真，则执行ELSE语句序列n中的语句

 【例4.8】应用简单CASE语句查询教师的职称。

SELECT tname AS 姓名,
    CASE tprof
         WHEN '教授' THEN '正高'
         WHEN '副教授' THEN '副高'
         WHEN '高级工程师' THEN '副高'
         WHEN '高级经济师' THEN '副高'
         ELSE '中级'
    END AS 职称
FROM teacher

【例4.8】应用搜索CASE语句输出score表中成绩的等级。

SELECT sno AS 学号, cno AS 课程号,
  CASE
    WHEN grade<60 then '不及格'
    WHEN grade <80 then '一般'
    WHEN grade <90 then '良好'
    ELSE '优秀'
  END AS成绩
FROM score;

 2.  循环语句

         有三种形式，分别为：while语句  repeat语句 loop语句

（1）while语句

while 条件 DO
          语句序列
END WHILE;

WHILE语句执行流程如下：

• 首先判断条件是否成立
• 如果条件成立，则执行语句序列
• 然后再次判断条件是否成立，如果条件成立则继续循环，否则结束循环。

【例4.9】计算1+2+3+…+100的和。

DECLARE n INT DEFAULT 1;
DECLARE sum INT DEFAULT 0;
WHILE n<=100 DO
   SET sum = sum +n;
   SET n=n+1;
END WHILE;

 注意：变量 sum 必须使用DEFAULT 0 赋初值为0，否则其初值默认为NULL，最终得到的结果为NULL，不能得到正确的结果。

（2）REPEAT语句

REPEAT
	语句序列
	UNTIL 条件
END REPEAT;

 REPEAT语句执行流程如下：

• 先执行语句序列，然后判断条件是否成立，若条件不成立则继续循环，否则结束循环。
• REPEAT语句是“先执行，后判断”，循环体至少被执行一次；
• 而WHILE语句是“先判断，后执行”，循环体可能一次也不被执行。

【例4.10】计算2+4+6+…+100的和。

DECLARE n INT DEFAULT 2;
DECLARE sum INT DEFAULT 0;
REPEAT 
   SET sum = sum +n;
   SET n=n+2;
UNTIL n>100
END REPEAT;

（3）LOOP语句

[语句标号: ]  LOOP
	语句序列
END LOOP  [语句标号] ;

 LOOP语句执行流程如下：

重复执行语句序列，语句序列中通常存在一个LEAVE语句，执行到该语句时退出循环。其中的语句标号是用户自定义的名称。

① 退出循环语句LEAVE

        LEAVE 语句标号;

        其中，语句标号是LOOP语句中自定义的名称。执行到该语句时结束循环。

② 再次循环语句ITERATE的语法格式如下。

        ITERATE 语句标号;

        ITERATE 语句只能出现在WHILE、REPEAT和LOOP语句中，结束本次的循环，然后开始下一次的循环。

【例4.11】计算5的阶乘。

DECLARE n, f INT DEFAULT 1;
label1:LOOP 
   SET f=f*n;
   SET n=n+1;
   IF n>5 THEN
      LEAVE label1;
   END IF;
END LOOP label1;

8.2 函数使用

        函数是一组编译好的SQL语句，它们可以带一个或多个参数，也可以不带参数，它返回一个数值，或执行一些操作。函数能够重复执行一些操作，从而避免不断重写代码。

  MySQL提供了丰富的内置函数，方便用户对数据进行相应的处理，同时用户也可以自定义函数。

（1）内置函数。内置函数是一组预定义的函数，是MySQL的一部分，按MySQL中定义的方式运行且不能修改。在MySQL中，内置函数主要用来获得系统的有关信息、执行数学计算和统计、实现数据类型的转换等。MySQL中常用的内置函数，包括字符串函数、数学函数、日期和时间函数、系统函数等。

（2）用户定义函数。在MySQL中，由用户定义的MySQL函数即为用户定义函数。它将频繁执行的功能语句块封装到一个命名实体中，该实体可以由MySQL语句调用。

1. 内置函数

        在MySQL中提供了许多内置的标准函数，每个标准函数可以实现某个特定的功能，方便用户使用。 函数的调用格式如下。 函数名([ 参数1 [, 参数2…]])

说明：

• 参数可以是常量、变量或表达式。
• 函数可以没有参数，也可以有一个或多个参数，多个参数之间用逗号进行分隔。
• 调用函数后，得到一个函数的返回值。
• 函数名和括号之间不能有空格；没有参数的函数也不能省略括号()。

 （1）数学函数

        数学函数完成数学计算功能。

函数	函数功能	示例	返回结果
ABS(n)	返回数值表达式n的绝对值	ABS(-2.5)	2.5
ROUND(n, m)	返回按照指定的小数位数m对n值四舍五入的结果	ROUND(12.38, 1)	12.4
TRUNCATE(n, m)	返回按照指定的小数位数m对n值截取的结果	TRUNCATE (12.38, 1)	12.3
SQRT(n)	返回数值表达式的平方根	SQRT(9)	3

（2）字符串函数

        字符串函数用来处理字符串型变量或字符串表达式。 

【例8-5】查询学生表student中姓“李”的学号sno、姓名sname、性别ssex。

SELECT sno,sname,ssex 
FROM student 
WHERE LEFT(sname,1)="李";

 【例8-6】将教师表teacher中姓名tname字段分成姓和名字两列显示。

SELECT LEFT(tname,1) AS 姓,SUBSTRING(tname,2, LENGTH(tname)-1) AS 名字
FROM teacher;

函数	函数功能	示例	返回结果
ASCII(c )	返回字符串c最左边字符的ASCII码	ASCII("A")	65
CHAR(n)	将数值n转换成字符	CHAR(65)	A
CONCAT(c1,c2)	将多个字符串进行连接	CONCAT("AB","XYZ")	"ABXYZ"
LENGTH(c)	求字符串c的长度	LENGTH ("ABCD")	4
LEFT(c, n)	取字符串c左边的n个字符	LEFT("ABCD", 3)	"ABC"
RIGHT(c, n)	取字符串c右边的n个字符	RIGHT("ABCD", 3)	"BCD"
SUBSTRING(c, n1[, n2])	取子字符串，在c串中从n1位置开始取n2个字符。缺省n2时，从n1位置开始取到串尾	SUBSTRING (“ABCDE”, 2, 3)	"BCD"
REPLACE(c1,c2,c3)	用字符串c3替换c1中出现的所有字符串c2，返回替换后的字符串。	REPLACE("ABCDABE","AB","12")	"12CD12E"
LTRIM(c)	去掉字符串c中左边的空格	LTRIM("   ABCD")	"ABCD"
RTRIM(c)	去掉字符串c中右边的空格	RTRIM("ABCD   ")	"ABCD"
TRIM(c)	去掉字符串c中左右两边的空格	TRIM("   ABCD   ")	"ABCD"
LOWER(c)	将字符串c转换为小写字符	LOWER(("AB")	“ab”
UPPER(c)	将字符串c转换为大写字符	UPPER(“ab”)	“AB”

 （3）日期和时间函数

        日期和时间函数用于处理日期和时间型表达式或变量

函数	函数功能	示例	返回结果
CURDATE()	返回系统当前日期	CURDATE()	2023-8-12
CURTIME()	返回系统当前时间	CURTIME()	11:23:58
NOW()	返回系统当前日期和时间	NOW()	2023-8-12 11:23:58
YEAR(d)	返回日期表达式d的年份	YEAR("2023-8-12")	2023
MONTH(d）	返回日期表达式d的月份	MONTH("2023-8-12")	8
DAY(d)	返回日期表达式d的天数	DAY("2023-8-12")	12
DATEDIFF(d1,d2)	返回两个日期之间的天数	DATEDIFF("2023-8-16", "2023-8-10")	6

 【例8-7】查询学生表student中学生的年龄。

“年龄”通过计算表达式“YEAR(NOW())-YEAR(birthdate)”获得，使用AS指定列标题为“年龄”。

SELECT sno AS 学号,sname AS 姓名,YEAR(NOW())-YEAR(birthdate) AS 年龄
FROM student;

（4）其他函数

函    数	函  数  功  能	示    例	返回结果
IF(expr,v1,v2)	判断条件表达式expr的值，如果为真则返回v1的值；否则返回v2的值	IF(5>0,’是’,’否’ )	是
IFNULL(v1,v2)	如果v1的值不为空则返回v1的值；否则返回v2的值	IFNULL(5,2 )	5
VERSION()	返回当前数据库的版本号	VERSION()	8.0.30

2. 用户定义函数

(1)  创建函数

        在MySQL中，可以使用CREATE FUNCTION语句创建函数，其基本语法格式如下：

CREATE FUNCTION func_name ( [ func_parameter [ , … ] ] )
        RETURNS type
        routine_body

 【例4.23】给定学生的学号，返回学生姓名。

DROP FUNCTION IF EXISTS getSname;
DELIMITER @@
CREATE FUNCTION getSname(sn CHAR(12))
RETURNS varchar(20) 
DETERMINISTIC
BEGIN
DECLARE xm varchar(20);
  select sname into xm from student where sno=sn ;
  RETURN xm;
END
@@
DELIMITER ;

(2)  调用函数

        在MySQL中，用户自定义的函数与系统函数的使用方法基本相同，其基本语法格式如下：

SELECT func_name ( [ func_parameter [ , … ] ] )

 【例4.24】调用getSname()函数，查找学号为202301010101的学生的姓名。

SELECT getSname ('202301010101') AS student_name;

(3)  删除函数

        删除函数的基本语法格式如下：

DROP FUNCTION [ IF EXISTS ] func_name

(4)  查看函数的状态

        通过show status命令查看函数的相关信息，其基本语法格式如下：

show function status;

(5)  查看函数定义

        通过show create命令查看函数的定义内容，其基本语法格式如下：

show create function functionName;