1. MySQL编程基础

1.1 常量与变量

字符串常量：

• 字符串常量指用单引号或双引号括起来的字符序列。

示例：查询表emp中ename值为SCOTT的雇员信息。

SELECT * FROM emp WHERE ename='SCOTT';

数值常量：

• 数值常量可以分为整数常量和小数常量。

示例：将表emp中，SCOTT雇员的comm值改为1250（要求用科学记数法表示）。

UPDATE emp SET COMM=1.25E+3 WHERE ename='SCOTT';

日期和时间常量：

• 日期和时间常量使用特定格式的字符日期值表示，用单引号括起来。

示例：查询表emp中1981年以后雇用员工的ename和hiredate信息。

SELECT ename,hiredate FROM emp WHERE hiredate>'1981/12/31';

布尔值常量：

• 布尔值只有true和false两个值，SQL命令运行结果用1代表true，用0代表false。

示例：查询表emp中所有雇员的姓名ename和工资sal是否大于等于2000的判断结果。

SELECT ename,sal>2000 FROM emp;

NULL值：

• NULL值参与的运算，结果仍为NULL值。

示例：将表emp雇员SCOTT的comm列值改为NULL值，然后再在NULL值的基础上加1250元，请考虑最终comm列值是什么?

UPDATE emp SET comm=NULL WHERE ename='SCOTT';
UPDATE emp SET comm=comm+1250 WHERE ename='SCOTT';
SELECT * FROM emp WHERE ename='SCOTT';

局部变量：

• 局部变量的定义与赋值：

SET @局部变量名=表达式1[,@局部变量名=表达式2,……];

• 局部变量的显示：

SELECT @局部变量名[,@局部变量名,……];

示例：查询表emp中雇员'SMITH'的job和hiredate值赋给变量job_v、hiredate_v，并显示两个变量的值。

SELECT job,hiredate INTO @job_v,@hiredate_v FROM emp WHERE ename='SMITH';
SELECT @job_v,@hiredate_v;

全局变量：

• 全局变量是MySQL系统提供并赋值的变量。

• 用户不能定义全局变量，只能使用。

全局变量名称	说明
@@storage_engine	返回存储引擎
@@version	返回服务器版本号

示例：查看MySQL的版本信息。

SELECT @@version;

1.2 常用系统函数

字符串函数：

• 计算字符串字符数的函数和字符串长度的函数。

• CHAR_LENGTH(str)，返回字符串str所包含的字符个数。

• LENGTH(str)，返回值为字符串的字节长度。一个汉字是3个字节，一个数字或字母是1个字节。

示例：

SELECT  CHAR_LENGTH('CHINA'),LENGTH('CHINA');
SELECT  CHAR_LENGTH('中国') 字符数,LENGTH('中国') 字符串长度;

• 合并字符串函数：

  CONCAT(s1,s2,……) ，返回结果为连接参数产生的字符串，如果任何一个参数为NULL，则返回值为NULL。

示例：

SELECT CONCAT('MySQL版本：',@@version) 版本信息

• 字符串大小写转换函数：

  LOWER(str)，是将字符串str中的字母字符全部转换成小写字母。

  UPPER(str),是将字符串str中的字母字符全部转换成大写字母。

示例：

SET @name='sCOtt';
SELECT * FROM emp WHERE UPPER(ename)=UPPER(@name);

• 删除空格函数：

  LTRIM(str)，返回删除前导空格的字符串str；

  RTRIM(str)，返回删除尾部空格的字符串str；

  TRIM(str)，返回删除两侧空格的字符串str。

示例：

SET @name='  SCOtt  ';
SELECT * FROM emp WHERE UPPER(ename)=TRIM(UPPER(@name));

• 取子串函数：

  SUBSTRING(str,start,length)，返回字符串str从start开始长度为length的子串。

示例：返回emp中ename值以'S'开头的雇员信息。

SELECT * FROM emp WHERE SUBSTRING(ename,1,1)='S';
SELECT * FROM emp WHERE ename LIKE 'S%';

数学函数：

• ABS(x)，返回x的绝对值。

• PI()，返回圆周率π的值。

• SQRT()，返回非负数的二次方根。

• MOD (m,n)，返回m被n除后的余数。

• ROUND(x,y)，把x四舍五入到y指定的精度返回。如果y为负数，则将保留x值到小数点左边y位。

示例：

SELECT SQRT(ROUND(ABS(-4.01*4.01),0)),MOD(-10,3),MOD(10,-3);

日期和时间函数：

• 获取当前系统的日期及取日期的年、月、日函数。

  CURDATE()，返回当前系统日期，格式为'YYYY-MM-DD'。

  YEAR(d)、MONTH(d)、DAY(d)分别返回日期或日期时间d的年、月、日的值。

示例：查询表emp员工SMITH的工作年限。

SELECT ename 姓名,YEAR(CURDATE())-YEAR(hiredate) 工作年限 FROM  EMP WHERE ename='SMITH';

• 获取当前系统日期时间函数。

  CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()，4个函数作用相同，均返回当前系统的日期时间

  格式为'YYYY-MM-DD HH:MM:SS'。

示例：查询表emp员工SMITH的工作年限。

SELECT ename 姓名,YEAR(SYSDATE())-YEAR(hiredate) 工作年限    FROM EMP  WHERE ename='SMITH';

系统信息函数：

• USER()，返回当前登录的用户名。

• DATABASE()，返回当前使用数据库名。

• VERSION()，返回MySQL服务器版本号。

示例：查询表emp员工SMITH的工作年限。

SELECT CONCAT('MySQL版本号：',VERSION(),';用户：',USER()) AS 登录信息;

条件控制函数：

• IF函数：

  IF(条件表达式,v1,v2)

示例：查询表emp前5条记录，显示ename和comm字段的值，当comm字段值为NULL时，显示值为0，否则显示当前字段的值。

SELECT ename,IF(comm IS NULL,0,comm) 奖金FROM emp LIMIT 5;

• CASE()函数：

  CASE 表达式WHEN  v1  THEN  r1WHEN  v2  THEN  r2……[ELSE   rn]
  END

示例：查询'SMITH'所在部门名称。

SELECT ename 姓名,CASE deptnoWHEN  10  THEN  'ACCOUNTING'WHEN  20  THEN  'RESEARCH'WHEN  30  THEN  'SALES'WHEN  40  THEN  'OPERATIONS'END  部门名称FROM  empWHERE  ename='SMITH';

数据类型转换函数：

• CAST(x AS 新类型 )

• CONVERT(x 新类型）

示例：

SELECT CONCAT(@name,'的工资是',CAST(@salary AS CHAR(7))) 信息;

2. 程序控制流语句

2.1 语句块、注释和重置命令结束标记

语句块：

BEGINSQL语句 ｜ SQL语句块
END

• BEGIN…END语句块包含了该程序块的所有处理操作，允许语句块嵌套。

• 在MySQL中单独使用BEGIN…END语句块没有任何意义，只有将其封装在存储过程、存储函数等存储程序内部才有意义。

注释：

• 单行注释：

  使用#符号作为单行语句的注释符，写在需要注释的行或语句后方。

示例：

#取两个数的最大值
SET  @x=5,@y=6;                             #定义两个变量并赋值
SELECT  IF(@x>@y,@x,@y)  最大值;  

• 多行注释：

  使用/*和*/括起来可以连续书写多行的注释语句。

示例：

/*在使用MySQL执行update的时候，如果不是用主键当where语句，会报错，使用主键用于where语句中则正常。因为MySQL运行在safe-updates模式下，该模式会导致非主键条件下无法执行update或者delete命令，执行命令 SET SQL_SAFE_UPDATES = 0修改下数据库模式。*/
SET SQL_SAFE_UPDATES=0;
UPDATE  dept_c  SET  deptno=50 WHERE deptno=10;

重置命令结束标记：

• DELIMITER符号.

• 符号可以是一些特殊符号，如两个“#”（##）、两个“@”（@@）、两个“$”（$$）、两个“%”（%%）等。

• 恢复使用分号作为结束标记，执行 DELIMITER ; 命令即可。

示例：

DELIMITER @@
SELECT * FROM emp@@
DELIMITER ;
SELECT * FROM emp;

2.2 存储函数

存储函数的创建：

CREATE FUNCTION 函数名([参数名 参数数据类型[,…]])
RETURNS  函数返回值的数据类型
BEGIN函数体;RETURN  语句;
END

调用存储函数：

SELECT  函数名([参数值[,…]]);

示例：创建存储函数name_fn，根据所给的部门编号deptno值，函数返回该部门的部门名称dname。

DELIMITER @@
CREATE FUNCTION name_fn(dno  DECIMAL(2))RETURNS  VARCHAR(14)BEGINRETURN(SELECT dname FROM  dept   WHERE  deptno=dno);  END@@

删除存储函数：

DROP FUNCTION 函数名;
# 函数名后面不要加括号。

示例：删除创建的name_fn存储函数。

DROP FUNCTION name_fn;

2.3 条件判断语句

程序中变量的使用：

• 声明变量：

  DECLARE 局部变量名[,……] 数据类型 [DEFAULT 默认值];

  DECLARE声明的局部变量，变量名前不能加@。

  DEFUALT子句提供了一个默认值，如果没有给默认值，局部变量初始值默认为NULL。

• 为变量赋值：

  SET 局部变量名=表达式1[,局部变量名=表达式2,……];

示例：创建求任意两个数和的存储函数sum_fn()。

DELIMITER @@                  
CREATE FUNCTION sum_fn(a DECIMAL(5,2),b DECIMAL(5,2))
RETURNS DECIMALBEGINDECLARE x,y DECIMAL(5,2);  SET x=a,y=b;              RETURN x+y;END@@
​
DELIMITER;
SELECT sum_fn(7,3);

IF语句：

• 形式一：

IF  条件 THENSQL语句块1;[ ELSESQL语句块2;]END IF;

示例：创建函数，返回两个数的最大值

DELIMITER @@
CREATE FUNCTION max_fn(a int,b int)RETURNS  INTBEGINIF a>b THENRETURN a;ELSERETURN b;END IF;
END@@

• 形式二：

IF 条件 THENSQL语句块1;ELSEIF 条件2 THENSQL语句块2;# ……ELSESQL语句n;END IF;

CASE语句：

• 形式一：

CASE  表达式WHEN   表达式值1  THEN   SQL语句块1;WHEN   表达式值2  THEN   SQL语句块2;……WHEN   表达式值n  THEN   SQL语句块n;[ ELSE   SQL语句块n+1;  ]
END;

示例：判断显示emp表中前3条记录的姓名和职务。

SELECT  ename 姓名,CASE jobWHEN 'SALESMAN' THEN '销售员'WHEN 'CLERK'          THEN '管理员'ELSE '经理'END AS 职务
FROM EMP  LIMIT 3;

• 形式二：

CASE   WHEN   条件1  THEN   SQL语句块1;WHEN   条件2  THEN   SQL语句块2;……WHEN   条件n  THEN   SQL语句块n;[ ELSE   SQL语句块n+1;  ]
END;

2.4 循环语句

LOOP循环：

标签：LOOPSQL语句块;IF <条件表达式> THENLEAVE  标签;END IF;
END LOOP;

WHILE循环：

WHILE  条件   DOSQL语句块;
END WHILE;

REPEAT循环：

REPEATSQL语句块;UNTIL  条件
END REPEAT;

3. 存储过程

存储过程是用于执行特定操作的SQL语句的集合。创建一次，可重复调用任意多次。

存储过程的优点：

• 执行速度快。

  存储过程在创建时被编译，在第一次执行之后，就驻留在内存中，之后每次执行该存储过程均不需要再重新编译。

• 减少网络通信流量。

  调用执行仅用一条语句，所以只有少量的SQL语句在网络线上传输。

3.1 创建存储过程

CREATE  PROCEDURE 存储过程名()
BEGIN过程体;
END

示例：创建存储过程emp_p，在emp表中查询职工编号为7369员工的姓名和工作。

SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER @@
CREATE PROCEDURE emp_p()
BEGINSELECT  ename,job FROM  emp   WHERE  empno=7369;
END@@

3.2 调用存储过程

CALL 存储过程名();

示例：调用存储过程emp_P。

DELIMITER ;
CALL emp_p();

3.3 存储过程的参数

CREATE PROCEDURE 存储过程名([ IN | OUT | INOUT]  参数1  数据类型,[ IN | OUT | INOUT]  参数2  数据类型,……
)
BEGIN过程体；
END

IN参数：

• 输入参数，该参数值由调用者传入。

OUT参数：

• 输出参数，向调用者返回一个或多个数据。

示例：创建存储过程dept_p2，该过程根据提供的部门编号，返回部门的名称和地址。

DELIMITER @@
CREATE PROCEDURE dept_p2(IN  i_no   DECIMAL(2,0),OUT o_name VARCHAR(14),OUT o_loc  VARCHAR(13))BEGINSELECT dname,loc  INTO o_name,o_loc FROM dept  WHERE deptno=i_no;END@@

IN OUT参数：

• 输入输出参数。

3.4 删除存储过程

DROP PROCEDURE 存储过程名;

示例：删除存储过程emp_p。

DROP PROCEDURE emp_p;

4. 游标

通过SELECT语句查询时，返回的结果是一个由多行记录组成的集合。

游标是在存储程序中使用包含SELECT语句声明的游标。

4.1 游标的定义和使用

声明游标：

DECLARE 游标名 CURSOR FOR SELECT语句;

• 声明游标的作用是得到一个SELECT查询结果集。

打开游标：

OPEN 游标名;

提取数据：

FETCH 游标名 INTO 变量名1[,变量名2,……];

• 成功打开游标后，游标指针指向结果集的第一行之前。

• FETCH语句将使游标指针指向下一行。

关闭游标：

CLOSE 游标名;

示例：创建存储过程emp_p，提取emp表中7788雇员的姓名和职务。

DELIMITER @@
CREATE PROCEDURE emp_p()BEGINDECLARE v_ename VARCHAR(14);     DECLARE v_job   VARCHAR(13);     DECLARE emp_cursor CURSOR       FOR  SELECT  ename,job  FROM emp  WHERE  empno=7788;OPEN emp_cursor;                 FETCH emp_cursor INTO v_ename,v_job;   CLOSE emp_cursor;                  SELECT v_ename,v_job;END@@
​
DELIMITER ;
CALL emp_p();

4.2 异常处理

DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序;

• 异常处理语句必须放在所有变量及游标定义之后，所有MySQL表达式之前；

• 错误处理类型，只有CONTINUE和EXIT两种。

• CONTINUE表示错误发生后，MySQL立即执行自定义错误处理程序，然后忽略该错误继续执行其它MySQL语句。

• EXIT表示错误发生后，MySQL立即执行自定义错误处理程序，然后立刻停止其它MySQL语句的执行。

• 错误条件，定义了自定义错误处理程序运行的时机。

  1. SQLSTATE 'ANSI标准错误代码'：包含5个字符的字符串值

     DECLARE EXIT HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';

  2. MySQL错误代码：匹配数值类型的错误代码

     DECLARE EXIT HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';

  3. SQLWARNING：匹配所有以01开头的SQLSTATE错误代码

     DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

  4. NOT FOUND：匹配所有以02开头的SQLSTATE错误代码

     DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';

  5. SQLEXCEPION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码

     DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

• 错误处理程序，错误发生后，MySQL会立即执行自定义错误处理程序中的MySQL语句。

  DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';

5. 嵌入式SQL

5.1 区分主语言语句与SQL语句

在嵌入式SQL中，为了能够快速区分SQL语句与主语育语句，所有SOL语句都必须加前缀。当主语言为C语言时，语法形式如下：

EXEC SQL SQL语句;

5.2 嵌人式SOL语句与主语言的通信

1. SQL语句将执行状态信息传递给主语言。

   主语言得到该状态信息后，可根据此状态信息来控制程序流程，以控制后面的SQL语句或主语言语句的执行。向主语言传递SQL执行状态信息，主要用SQL，通信区(SQL Communication Area,SQLCA)实现，

2. 主语言需要提供一些变量参数给SQL语句。

   该方法是在主语言中定义主变量(Host Variable)，在SQL语句中使用主变量,将参数值传递给 SQL语句。

3. 将SQL语句查询数据库的结果返回给主语言做进一步处理。

   如果SQL语句向主语言返回的是一条数据库记录，可使用主变量；若返回值为多条记录的集合，则使用游标。

5.3 主变量的定义和使用

主变量的定义：

在使用主变量之前，必须在SQL语句BEGIN DELARE DECLARE SECTION之END DECLARE SECTION间进行声明。

在声明之后，主变量可以在SQL语句中任何一个能够使用表达式的地方出现，为了与数据库对象名(例如表名、视图名、列名等)区别,应在SQL语句中的主变量名前加冒号(:)。

使用主变量的注意事项：

1. 主变量在使用前，必须在嵌入SQL语句的说明部分明确定义。

2. 主变量在定义时，所用的数据类型应为主语言提供的数据类型，而不是SQL的数据类型。同时要注意主变量的大小写。

3. 在SQL语句中使用主变量时，必须在主变量前加一个冒号(:)，在不含SQL语句的主语言语句中，则不需要在主变量前加冒号。

4. 主变量不能是SQL命令的关键字，例如SELECT 等；

5. 在一条SQL语句中，主变量只能使用一次。

主变量的定义：

EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 */char msno[4],mcno[3],givenson[5];int mgrade;char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;

在SELECT语句中使用主变量：

• 在嵌入式SQL中,如果查询结果为单记录,则SELECT语句需要用INTO子句指定查询结果的存放地点—主变量。

在INSERT语句中使用主变量：

• 在INSERT语句的VALUES子句中,可以使用主变量指定插人的值。

在UPDATE语句中使用主变量：

• 在UPDATE语句的SET子句和WHERE子句中,均可以使用主变量。

在DELETE语句中使用主变量：

• 在DELETE语句的WHERE子句中,可以使用主变量指定删除条件。

5.4 嵌入式SOL中游标的定义与使用

声明游标：

EXECT SQL DECLARE 游标名 CURSORFOR SELECT 语句;

打开游标：

EXEC SQL OPEN 游标名;

提取数据：

EXEC SQL FETCH FROM 游标名 INTO 主变量[,主变量,...]；

关闭游标：

EXEC SQL CLOSE 游标名;

5.5 动态SQL语句

动态SQL预备语句：

EEXEC SQL PREPARE 动态SQL语句名 FROM 共享变量或字符串;

动态SQL执行语句：

EXEC SQL EXECUTE 动态SQL语句名;

当预备语句中组合而成的SQL语句只需执行一次时,预备语句和执行语句可合并成一个语句：

EXEC SQLEXECUTE IMMEDIATE 共享变量或字符串;

当预备语句中组合而成的SQL语句的条件值尚缺时,可以在执行语句中用USING 短语补上：

EXEC SOL EXECUTE 动态SQL语名 USING 共享变量;