当前位置：首页 > article >正文

【专题】数据库编程

article 2024/10/22 11:31:37

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  r1
    WHEN  v2  THEN  r2
    ……
    [ELSE   rn]
END

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

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

数据类型转换函数：

CAST(x AS 新类型 )

CONVERT(x 新类型）

示例：

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

2. 程序控制流语句

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

语句块：

BEGIN
    SQL语句 ｜ 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)
    BEGIN
        RETURN(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 DECIMAL
    BEGIN
        DECLARE x,y DECIMAL(5,2);  
        SET x=a,y=b;              
        RETURN x+y;
    END@@

DELIMITER;
SELECT sum_fn(7,3);

IF语句：

形式一：

IF  条件 THEN
    SQL语句块1;
    [ ELSE
    SQL语句块2;
    ]
    END IF;

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

DELIMITER @@
CREATE FUNCTION max_fn(a int,b int)
    RETURNS  INT
    BEGIN
        IF a>b THEN
        RETURN a;
    ELSE
        RETURN b;
    END IF;
END@@

形式二：

IF 条件 THEN
    SQL语句块1;
    ELSEIF 条件2 THEN
        SQL语句块2;
        # ……
    ELSE
        SQL语句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 job
        WHEN '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循环：

标签：LOOP
    SQL语句块;
    IF <条件表达式> THEN
        LEAVE  标签;
    END IF;
END LOOP;

WHILE循环：

WHILE  条件   DO
    SQL语句块;
END WHILE;

REPEAT循环：

REPEAT
    SQL语句块;
    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()
BEGIN
    SELECT  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)
    
    )
    BEGIN
    SELECT 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()
 BEGIN
    DECLARE 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语句与主语言的通信

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

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

该方法是在主语言中定义主变量(Host Variable)，在SQL语句中使用主变量,将参数值传递给 SQL语句。
将SQL语句查询数据库的结果返回给主语言做进一步处理。

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

5.3 主变量的定义和使用

主变量的定义：

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

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

使用主变量的注意事项：

主变量在使用前，必须在嵌入SQL语句的说明部分明确定义。
主变量在定义时，所用的数据类型应为主语言提供的数据类型，而不是SQL的数据类型。同时要注意主变量的大小写。
在SQL语句中使用主变量时，必须在主变量前加一个冒号(:)，在不含SQL语句的主语言语句中，则不需要在主变量前加冒号。
主变量不能是SQL命令的关键字，例如SELECT 等；
在一条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 游标名 CURSOR
    FOR 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 共享变量;

查看全文

http://www.kler.cn/news/360083.html

人工智能技术的应用前景及其对生活和工作方式的影响

sql server 行转列及列转行

程序设计基础I-单元测试2（机测）

华为eNSP Destination host unreachable和Request timeout!错误（详细解析）

【无标题】如何使用yolo-v8 实现自定义目标检测

教学平台的信息化之路：Spring Boot实践

【ChatGPT】提高 ChatGPT 创意输出的提示词技巧

在windows下利用安装docker加vscode调试OceanBase，

Pandas | 通过PUBG数据集进行数据分析并理解函数使用

鸿蒙网络编程系列22-Web组件文件上传示例

【红日安全】vulnstack （一）

K8S---02.Kubernetes的pod

python支付宝支付和回调

计算机毕业设计Python深度学习房价预测房源可视化房源爬虫二手房可视化二手房爬虫递归决策树模型机器学习深度学习大数据毕业设计

【Vue.js设计与实现】第三篇第10章：渲染器-双端 Diff 算法-阅读笔记

如何使用postman进行自动化

Milvus 到 TiDB 向量迁移实践

关于游戏行业工作的迷茫

Sqlite3 操作笔记

Java设计模式：工厂模式详解

1. MySQL编程基础

1.1 常量与变量

1.2 常用系统函数

2. 程序控制流语句

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

2.2 存储函数

2.3 条件判断语句

2.4 循环语句

3. 存储过程

3.1 创建存储过程

3.2 调用存储过程

3.3 存储过程的参数

3.4 删除存储过程

4. 游标

4.1 游标的定义和使用

4.2 异常处理

5. 嵌入式SQL

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

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

5.3 主变量的定义和使用

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

5.5 动态SQL语句

相关文章：