项目六：数据库编程教学课件（全）.pptx

M y S Q LM y S Q L 数 据 库数 据 库项目教程项目教程本章学习目标：1.掌握SQL语言程序设计2.掌握系统函数的应用3.理解自定义函数的应用4.掌握游标的应用项目六 数据库编程重点： 1.SQL语言的分支结构和循环结构语句的使用2.MySQL语言中常见系统函数的应用3.自定义函数的创建、管理和使用难点： 1.REPEAT循环语句的使用2.JSON函数的应用3.游标的创建和使用任务1 part SQL程序语言变量是指程序运行过程中会变化的量，MySQL支持的变量类型有4种类型。用户变量：这种变量用一个字符作为前缀，在MySQL会话末端结束其定义。系统变量和服务器变量：这种变量包含了MySQL服务器的状态或属性。它们以字符作为前导符（例如：binlog_cache_size）。结构化变量：这种变量是系统变量的一种特例。MySQL目前只在需要定义更多的MyISAM索引缓存区时才会用到这些变量。局部变量：这种变量处于存储过程中，而且只是在存储过程中有效。它们没有特殊的前导标识，因此，给它们起的名字必须与数据表和数据列的名字有所区别。1.1S Q L 程 序 语 言 基 础变量常量是指在程序运行过程中，值不会改变的量。一个数字，一个字母或一个字符串等都可以是一个常量。MySQL中提供了多种类型的常量。（1）字符串常量(2)数值常量(3)日期时间常量(4)布尔值常量(5) NULL值常量1.1S Q L 程 序 语 言 基 础常量运算符是执行数学运算、字符串连接以及列、常量和变量之间进行比较的符号。运算符按照功能不同，分为以下几种。算术运算符: +、一、*、/、%赋值运算符: =、;=逻辑运算符: !(NOT)、&(AND)、|(OR)、XOR位运算符: &、比较运算符：=、（!=）、=、IS NULL1.1S Q L 程 序 语 言 基 础运算符和表达式BEGINEND语句相当于程序设计语句中的一对括号 ,在括号中存放一组SQL语句。在BEGINEND中的语句可以视为一个整体,虽然BEGIN和END表示的含义相当于一对括号，但是绝对不能用括号来代替，它们是SQL语句中的关键字，具体的语法如下:BEGINSql_statement | statement_blockEND1.2 BEGINEND语句IF语句是一个三目运算表达式，其语法格式如下。IF (条件表达式，结果1，结果2);其中，当“条件表达式”的值为TRUE时，返回“结果1，否则返回“结果2。1.3条 件 分 支 语 句IF语句IFNULL语句IFNULL语句是一个双目运算，其语法格式如下。IFNULL (结果1,结果2);其中，若结果1的值不为空，则返回结果1,否则返回结果2。在MySQL中，IFELSE语句允许嵌套使用，且嵌套层数没有限制。语法格式如下。IF 条件表达式1 THEN语句块1;ELSEIF条件表达式2 THEN 语句块2; ELSE语句块m;END IF;1.3条 件 分 支 语 句IFELSE语句1.3条 件 分 支 语 句CASE语句（1）简单CASE结构。简单CASE结构将表达式与一组简单表达式进行比较以确定结果。语法格式如下。CASE 表达式WHEN 数值1 THEN 语句1;WHEN 数值2 THEN语句2;ELSE语句n+1;END CASE;（2）CASE搜索结构CASE搜索结构用于搜索条件表达式以确定相应的操作。语法格式如下。CASE WHEN 条件表达式1 THEN 语句1;WHEN 条件表达式2 THEN语句2;ELSE语句n+1;END CASE;具体的WHILE语句语法形式如下。开始标签:WHILE 条件表达式 DO语句块;END WHILE 结束标签;1.4 WHILE 循环语句LOOP循环语句的语法格式如下。开始标签: LOOP语句块END LOOP 结束标签;其中，“开始标签”参数和“结束标签”参数分别表示循环开始和结束的标识，这两个标识必须相同，可以省略;“语句块”表示需要循环执行的语句。LEAVE语句主要用于跳出循环控制，与高级语言中的BREAK语句相似。其语法格式如下。LEAVE标签名;1.5 L O O P 循 环 语 句REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的语法格式如下。开始标签: REPEAT语句块;UNTIL条件表达式;END REPEAT 结束标签1.6 REPEAT循环语句任务2 part学习系统函数MySQL中的函数分为MySQL提供的系统函数和用户自定义函数两大类。MySQL提供了很丰富的系统函数，主要包括数学函数、字符串函数、日期时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。2.1 函数概述2.2数 学 函 数函数名称作 用ABS(x)求绝对值SQRT(x)求二次方根MOD(x)求余数CEIL(x) 和 CEILING(x) 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整FLOOR(x)向下取整，返回值转化为一个BIGINTRAND(x)生成一个01之间的随机数，传入整数参数是，用来产生重复序列ROUND(x)对所传参数进行四舍五入SIGN(x)返回参数的符号POW(x,y) 和 POWER(x,y)两个函数的功能相同，都是所传参数的次方的结果值SIN(x)求正弦值ASIN(x)求反正弦值，与函数 SIN 互为反函数COS(x)求余弦值ACOS(x)求反余弦值，与函数 COS 互为反函数TAN(x)求正切值ATAN(x)求反正切值，与函数 TAN 互为反函数COT(x)求余切值2.3字 符 串 函 数函数名称函数名称作作 用用LENGTH计算字符串长度函数，返回字符串的字节长度CONCAT合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个INSERT替换字符串函数LOWER将字符串中的字母转换为小写UPPER将字符串中的字母转换为大写LEFT从左侧字截取符串，返回字符串左边的若干个字符RIGHT从右侧字截取符串，返回字符串右边的若干个字符TRIM删除字符串左右两侧的空格REPLACE字符串替换函数，返回替换后的新字符串SUBSTRING截取字符串，返回从指定位置开始的指定长度的字符换REVERSE字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串2.4日 期 时 间 函 数函数名称函数名称作作 用用CURDATE 和 CURRENT_DATE两个函数作用相同，返回当前系统的日期值CURTIME 和 CURRENT_TIME两个函数作用相同，返回当前系统的时间值NOW 、SYSDATE和CURRENT_TIMESTAMP三个函数作用相同，返回当前系统的日期和时间值UNIX_TIMESTAMP获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数FROM_UNIXTIME将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数MONTH获取指定日期中的月份MONTHNAME获取指定日期中的月份英文名称DAYNAME获取指定日期对应的星期几的英文名称DAYOFWEEK获取指定日期对应的一周的索引位置值WEEK获取指定日期是一年中的第几周，返回值的范围是否为 052 或 153DAYOFYEAR获取指定日期是一年中的第几天，返回值范围是1366DAYOFMONTH获取指定日期是一个月中是第几天，返回值范围是131YEAR获取年份，返回值范围是 19702069TIME_TO_SEC将时间参数转换为秒数SEC_TO_TIME将秒数转换为时间，与TIME_TO_SEC 互为反函数DATE_ADD 和 ADDDATE两个函数功能相同，都是向日期添加指定的时间间隔DATE_SUB 和 SUBDATE两个函数功能相同，都是向日期减去指定的时间间隔ADDTIME时间加法运算，在原始时间上添加指定的时间SUBTIME时间减法运算，在原始时间上减去指定的时间DATEDIFF获取两个日期之间间隔，返回参数 1 减去参数 2 的值DATE_FORMAT格式化指定的日期，根据参数返回指定格式的值WEEKDAY获取指定日期在一周内的对应的工作日索引2.5 系统信息函数函数名称函数名称作作 用用VERSION()返回数据库的版本号CONNECTION_ID()返回服务器的连接数DATABASE(), SCHEMA()返回当前数据库名CURRENT_USER(),USER()返回当前登录用户名和主机名的组合2.6 流程控制函数函数名称函数名称作作 用用IF(expr,v1,v2)如果expr为真，返回v1，否则返回v2IFNULL(v1,v2)如果v1不为NULL，返回v1，否则返回v2CASE WHEN expr1 THEN r1 WHEN expr2 THEN r2 ELSE rn END根据条件将数据分为几个档次CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rn END根据条件将数据分为几个档次2.7J S O N 函 数函数名称函数名称作作 用用JSON_ARRAY()创建JSON数组JSON_OBJECT()创建JSON对象JSON_ARRAY_APPEND()向JSON数组中追加数据JSON_SET()修改JSON对象中的数据JSON_REMOVE()删除JSON数组和JSON对象中的数据JSON_EXTRACT()返回JSON数组中KEY所对应的数据JSON_SEARCH()返回JSON数组中给定数据的路径函数MD5()可以对字符串str进行加密，算出一个128位二进制形式的信息，但是系统会显示为32位十六进制的信息；若参数为NULL，则返回NULL值。该函数常用于对一些普通的不需要解密的数据进行加密。2.8 加密函数任务3part学习自定义函数3.1创建自定义函数MySQL中，创建用户自定义函数的语法格式如下。CREATE FUNCTION function_name (proc_params_list)RETURNS type characteristic, Routine_body;各参数的说明如下。function_name：是用户自定义函数的名称；proc_params_list：表示用户自定义函数的参数列表，每个参数由参数名称和参数类型组成；参数列表中参数的定义格式如下。param_name type RETURNS type：指定函数返回值的类型,MySQL自定义函数不支持返回表格类型或含多个值的数据类型。Routine_body：SQL代码内容，可以用BEGINEND来标识SQL代码的开始和结束。characteristic：用于指定用户自定义函数的特性。3.2查 看 自 定 义 函 数MySQL中可以通过SHOW STATUS语句来查看函数的状态，其语法格式如下。SHOW FUNCTION STATUS LIKE pattern;其中，FUNCTION参数表示查询存储函数；LIKE pattern参数用来匹配函数的名称。MySQL中可以通过SHOW CREATE语句查看函数的定义。其基本语法形式如下：SHOW CREATE FUNCTION fn_name ;其中， FUNCTION参数表示查询存储函数；fn_name参数表示存储过程或函数的名称。3.3修改自定义函数MySQL中通过ALTER FUNCTION语句来修改存储函数，其语法格式如下。ALTER FUNCTION fn_name characteristic .characteristic: CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY DEFINER | INVOKER | COMMENT string3.4删 除 自 定 义 函 数删除函数指删除数据库中已经存在的自定义函数。MySQL中使用DROP FUNCTION语句来删除自定义函数。其基本形式如下：DROP FUNCTION fn_name;其中，fn_name参数表示删除函数的名称。【例6.39】删除自定义函数funGetName。代码如下。DROP FUNCTION funGetName;任务4part学习游标的使用4.1声 明 和 打 开 游 标MySQL中使用DECLARE关键字来声明游标，其语法格式如下。DECLARE cursor_name CURSOR FOR sql_statement;其中，cursor_name表示新定义的游标名称；sql_statement则是用于定义游标所要操作结果集的SELECT语句。游标使用前，必须先声明，并打开游标。MySQL中使用OPEN关键字来打开游标。其语法格式如下。OPEN cursor_name;4.2使用游标游标打开后，使用FETCH关键字来获取游标当前指针的记录，并将记录值传给指定变量列表。其语法格式如下。FETCH cursor_name INTO var_name,var_name;其中，cursor_name表示游标的名称；var_name用于存储游标中的SELECT语句查询的结果信息。var_name中变量必须事先定义，且变量的个数必须和游标返回字段的数量相同，否则游标提取数据失败。4.3关 闭 游 标MySQL中使用CLOSE关键字来关闭游标，其语法格式如下。CLOSE cursor_name;【例】关闭db_cursor游标。语法如下。CLOSE db_cursor;在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。注意：隐含关闭，如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。本章课后作业见教材