SQL编程基础

目录

数据

常量

变量

全局变量和会话变量

用户变量

局部变量

流程控制语句

顺序结构

BEGIN···END

条件分支结构

IF···ELSE

CASE

循环结构

WHILE···END WHILE

REPEAT···END REPEAT

LOOP···END LOOP

游标

存储过程与存储函数

触发器

---

结构化查询语言（Structured Query Language），是一系列操作数据库及数据库对象的命令语句，其主要包括，常量变量，表达式，运算符，控制语句

数据

常量

 也称为文字值或标量值，指程序运行中值始终不变的量

字符串常量	包含在单引号 ' ' 或 双引号 " " 中，由字母（a-z，A-Z），数字字符（0-9）及特殊字符（如感叹号 ! 艾特符号 @ 井号 #）组成 示例：'114514'，"1919810@qq.com"
十进制整型常量	使用不带小数点的十进制数据表示 示例：1234，+128，-75
十六进制整型常量	使用前缀0x，后跟16进制数字串 示例：0x1F00，0xEEC，0X31
日期常量	使用单引号 ' ' 将日期时间字符串引起来，MySQL是按年-月-日的顺序表示日期的。中间的间隔符使用 - ，/ ，\ ，@ , % 等特殊符号
实型常量	有定点表示和浮点表示两种方式（浮点数我也看不懂） 示例：897.1，-110.04,19E24，-83E2
位字段值	使用b'value'符号写位字段值，value是一个用0和1写成的二进制值。直接显示b'value'可能是一系列特殊的符号。例如 b'0' 显示为空白 示例：SELECT BIN(b'111101'+0)，OCT(b'111101'+0)
布尔常量	使用0为FALSE，使用1为TRUE，布尔值仅包含0和1 示例：获取TRUE和FALSE的值——SELECT TRUE,FALSE;
NULL值	NULL值可适用于各种列类型，它通常用来表示“没有值” “无数据” 等意义，不等同于数字类型的“0”或字符串类型的空字符串

变量

 在程序运行过程中值可以改变的量。可利用变量存储程序运行过程中涉及的数据，如计算结果，用户输入的字符串及对象的状态等

变量由变量名和变量值构成，其值类型与常量一样。变量名不得与命令或函数名相同

在MySQL中，存在3种类型的变量，系统变量、用户变量和全局变量，系统变量又分为全局变量和会话变量

• 全局变量和会话变量

全局变量在MySQL启动时由服务器自动将它们初始化为默认值

会话变量在每次建立一个新的连接时，服务器会将当前所有全局变量的值复制一份作为会话变量

二者的区别在于，全局变量的修改将影响到整个服务器，即所有会话；会话变量的影响仅影响修改变量的这个会话，全局变量一般作为MySQL服务器的调节参数

 大多数系统变量应用于其他SQL语句中时，必须在名称前加两个@符号，但某些特定的系统变量要省略这两个@符号，如CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER

查询系统变量

 SHOW [ GLOBAL | SESSION ] VARIABLES [ LIKE '字符串' ];

参数说明：GLOBAL指全局，SESSION指会话

                  LIKE一定要加，不然刷屏啥也看不清

显示系统变量的值

SELECT @@变量名;

修改系统变量的值

SET 变量名=值;

| SET [ GLOBAL | SESSION ] 变量名=值;

| SET @@[ GLOBAL. | SESSION. ]变量名=值;

#显示a开头的会话变量
mysql> SHOW SESSION VARIABLES LIKE 'a%';
+-----------------------------+-------------------------+
| Variable_name               | Value                   |
+-----------------------------+-------------------------+
| activate_all_roles_on_login | OFF                     |
| admin_address               |                         |
| admin_port                  | 33062                   |
| admin_ssl_ca                |                         |
| admin_ssl_capath            |                         |
| admin_ssl_cert              |                         |
| admin_ssl_cipher            |                         |
| admin_ssl_crl               |                         |
| admin_ssl_crlpath           |                         |
| admin_ssl_key               |                         |
| admin_tls_ciphersuites      |                         |
| admin_tls_version           | TLSv1.2,TLSv1.3         |
| authentication_policy       | caching_sha2_password,, |
| auto_generate_certs         | ON                      |
| auto_increment_increment    | 1                       |
| auto_increment_offset       | 1                       |
| autocommit                  | ON                      |
| automatic_sp_privileges     | ON                      |
| avoid_temporal_upgrade      | OFF                     |
+-----------------------------+-------------------------+
19 rows in set, 1 warning (0.01 sec)

#显示当前MySQL版本，当前日期
mysql> SELECT @@VERSION,CURRENT_DATE;
+-----------+--------------+
| @@VERSION | CURRENT_DATE |
+-----------+--------------+
| 8.0.28    | 2022-08-02   |
+-----------+--------------+
1 row in set (0.00 sec)

#关闭自动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

#修改全局变量sort_buffer_size的值为40000
mysql> set @@GLOBAL.sort_buffer_size=40000;
Query OK, 0 rows affected (0.00 sec)

#修改会话变量，查询行数限制为100
mysql> SET SESSION SQL_SELECT_LIMIT=100;
Query OK, 0 rows affected (0.00 sec)

#修改当前变量sort_buffer_size的值为默认值
mysql> SET LOCAL sort_buffer_size=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

• 用户变量

用户变量是指用户可以在表达式中使用自己定义的变量，在使用前必须定义和初始化；用户变量在用户断开连接后会自动释放

定义用户变量

SET @变量名[ : ]=值 [,@变量名2=值,...];

| SELECT @变量名:=值 [,@变量名2:=值];

参数说明

@变量名：可用中文、字母、数字、“.”、“_”和“$”组成，若要在变量名内包含空格和#，则需要让变量名包含在单引号或双引号，如@"cyka blyat"

值：可以设置为整数，实数，字符串和NULL

SET和SELECT定义变量的区别：在非SET语句中，“=”被视为比较操作符，因此SELECT定义用户变量必须用：=来定义

#定义a用户变量的值为字符串
SET @a='blyat';

#定义b用户变量的值为整数
SELECT @b:=110;
+---------+
| @b:=110 |
+---------+
|     110 |
+---------+

#定义多个用户变量
SELECT @c:=1,@d:=2;
+-------+-------+
| @c:=1 | @d:=2 |
+-------+-------+
|     1 |     2 |
+-------+-------+

#显示创建后的变量的值
SELECT @a,@b,@c,@d;
+-------+------+------+------+
| @a    | @b   | @c   | @d   |
+-------+------+------+------+
| blyat |  110 |    1 |    2 |
+-------+------+------+------+

#用户变量的赋值可以通过计算
SET @d=@c+3;

#用户变量的赋值可以通过查询
SET @c=(SELECT sphone FROM student limit 2,1);

#使用用户变量存储筛选条件
SELECT sno,sname FROM student WHERE classno=@c;

• 局部变量

局部变量只能在BEGIN···END语句块中使用，局部变量与BEGIN···END语句块和流程控制语句都只能用于函数、存储过程、触发器和事务的定义中

定义局部变量

DECLARE 变量名 数据类型;

#定义局部变量a，数据类型为整型，默认值10
DECLARE a int DEFAULT 10;
#为局部变量a赋值
SET a=100;

流程控制语句

SQL的基本结构是顺序结构、条件分支结构和循环结构。在SQL中，流程控制语句就是用来控制程序执行流程的语句（划掉），也称流控制语句和控制流语句。

• 顺序结构

BEGIN···END

BEGIN END可以定义SQL语句块，这些语句块作为一组语句执行，允许语句嵌套

语法格式

BEGIN

SQL语句

END

• 条件分支结构

IF···ELSE

IF ELSE语句可以用于指定SQL语句的执行条件，当指定条件为真，则执行条件表达式后的SQL语句；当指定条件为假时，则执行其他条件表达式后的SQL语句。

语法格式

IF 条件表达式1 THEN

        SQL语句

[ ELSEIF 条件表达式2 THEN SQL语句 ]

[ ELSE SQL语句 ]

END IF;

CASE

CASE语句根据表达式的真假来确定是否返回某个值，可选择多个分支

语法格式

简单格式

CASE 列名

WHEN 条件表达式 THEN SQL语句;

[ ...n ]

[ ELSE SQL语句; ]

END CASE; 

搜索格式

CASE

WHEN 条件表达式 THEN SQL语句;

[ ...n ]

[ ELSE SQL语句; ]

END CASE;

说明：简单格式的条件表达式只能用列内数值比较，搜索格式的条件表达式则可以搭配变量比较

• 循环结构

WHILE···END WHILE

WHILE语句是设置执行条件，当指定的条件为真时，重复循环语句，即先检查后执行

语法格式

[ 循环名: ] WHILE 条件表达式 DO

<SQL语句;>

[ LEAVE 循环名; ]

[ ITERATE 循环名; ]

END WHILE;

说明：循环名一般用英文名字命名，大多数时候是在嵌套循环时起作用；LEAVE与Java中的break同理，ITERATE和Java中的continue同理

REPEAT···END REPEAT

REPEAT语句是在执行后检查执行条件，即先执行后检查

语法格式

[ 循环名: ] REPEAT

<SQL语句;>

[ LEAVE 循环名; ]

[ ITERATE 循环名; ]

UNTIL 条件表达式

END REPEAT;

LOOP···END LOOP

LOOP语句本身没有停止循环的语句，必须遇到LEAVE语句才能停止循环

语法格式

循环名:LOOP

<SQL语句;>

LEAVE 循环名

[ ITERATE 循环名; ]

END LOOP;

游标

游标是一种数据访问机制，允许用户访问单独的数据行

在MySQL中，游标主要包括游标结果集和游标位置两部分，游标结果集是由定义游标的SELECT语句返回行的集合，游标位置则是指向这个结果集中的某一行的指针

游标的使用需要先声明游标，定义其属性和操作结果，其操作大致如下

声明游标

DECLARE 游标名 CURSOR

FOR SQL语句;

打开游标

OPEN 游标名;

检索游标

FETCH 游标名 INTO 变量1[，变量2，...];

关闭游标

CLOSE 游标名;

说明：FETCH语句的功能是获取游标当前指针的记录，并传给指定变量列表，若要获得多行数据，则需要使用循环语句去执行FETCH；此处不演示，在存储过程和函数处再讲。

存储过程与存储函数

这俩是在数据库中定义的一些完成特定功能的SQL语句集合，可包含流程控制语句和各种SQL语句，可接受参数、输出参数和返回单个或多个结果。

优点：

1. 增强了SQL的功能和灵活性
2. 允许模块化程序设计，可多次调用，不必每一次查询都重新写一次程序，可随时修改
3. 能实现较快的执行速度
4. 减少网络流量，如果同一针对数据库对象的操作是使用存储过程执行，对比手打查询，提交的仅仅是调用存储过程的请求，避免了提交大量的查询请求
5. 可当成一种安全机制，对各个存储过程进行权限分配，避免非授权用户的访问

创建存储过程

CREATE PROCEDURE 过程名 ( [ 变量参数 ] )

[ 功能参数 [ ,... ] ]

SQL代码

创建存储函数

CREATE FUNCTION 函数名 ( [ 变量参数 ] )

RETURN 数据类型

[ 功能参数 [ ,... ] ]

SQL代码

说明：

变量参数

 [ IN | OUT | INOUT ] 变量名 数据类型；IN 表示输入参数，OUT表示输出参数，INOUT表示输入输出参数，可声明多个参数

功能参数

LANGUAGE SQL，声明代码使用SQL语句

[ NOT ] DETERMINISTIC，不加NOT表结果是确定的，相同输入会得到相同结果，默认是非确定的，相同输入会得到不同结果

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }，指定子程序使用SQL语句的限制；CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句。默认情况下指定为CONTAINS SQL

SQL SECURITY { DEFINER | INVOKER }，DEFINER表只有定义者可以执行，INVOKER表调用者可以执行，默认指定DEFINER

COMMENT'字符串'：注释信息

---

调用存储过程

CALL [ 数据库名. ] 过程名 ( [ 变量参数 [,...] ] );

调用存储函数

SELECT [ 数据库名. ] 函数名 ( [ 变量参数 [,...] ] );

删除存储过程

DROP PROCEDURE [ IF EXISTS ] 过程名;

删除存储函数

DROP FUNCTION [ IF EXISTS ] 函数名;

--先调整全局变量，避免创建存储函数后出现1418错误
SET GLOBAL log_bin_trust_function_creators = 1;
/*顺序结构*/
--不带参
DELIMITER //    --修改语句结束符号，避免“；”冲突导致报错
CREATE PROCEDURE Nopara()
BEGIN
DECLARE iAge int;
DECLARE vAddress LONGTEXT;
SET iAge=20;
SET vAddress='中国山东';
SELECT iAge,vAddress;
END//
CALL Nopara//    --调用存储过程

CREATE FUNCTION Noparaf()
RETURNS char(20)
READS SQL DATA
BEGIN
RETURN (SELECT cname FROM course WHERE cno='c01');
END//
SELECT Noparaf//    --调用存储函数

--带参
CREATE PROCEDURE Onepara(IN classno_tmp varchar(20))    --输入参数
BEGIN
    SELECT sno,sname,ssex,sbirthday,classno FROM student
    WHERE classno=classno_tmp;
END//
CALL Onepara('20070303')//

CREATE PROCEDURE Twopara(IN tmp_sno char(10),OUT count_num int)    --输入输出参数
BEGIN
    SELECT count(*) INTO count_num FROM sc WHERE sno=tmp_sno;
END//
CALL Twopara('2007030446',@count_num)//
SELECT @count_num//    --输出参数后要手动显示


/*条件分支结构*/
--IF语句
SELECT @lxl:='李小龙'//    --定义用户变量
CREATE PROCEDURE IFChoice()
BEGIN
	IF (SELECT sno FROM student WHERE sname=@lxl) IS NOT NULL THEN
		SELECT sno,sname,classno FROM student WHERE sname=@lxl;
	ELSE
		SELECT '查无此人';
	END IF;
END//
CALL IFChoice//

--CASE语句
--简单格式
CREATE PROCEDURE CaseSIM(c_no varchar(10))
BEGIN
    SET @ct=(SELECT cterm FROM teaching WHERE cno=c_no);
    CASE @ct
    WHEN @ct=1 THEN SELECT c_no,'optional';
    WHEN @ct=2 THEN SELECT c_no,'compulsory';
    WHEN @ct=3 THEN SELECT c_no,'major';
    ELSE SELECT c_no,'project';
    END CASE;
END//
CALL CaseSIM('c01')//

--搜索格式
CREATE FUNCTION CaseSEAR(s_no varchar(20))
RETURNS varchar(20)
READS SQL DATA
BEGIN
    SET @ad=(SELECT avg(degree) FROM sc WHERE sno=s_no);
    CASE
        WHEN @ad>=90 THEN SET @result='excellent!';
        WHEN @ad BETWEEN 80 AND 90 THEN SET @result='great';
        WHEN @ad>=60 AND @ad<80 THEN SET @result='not bad';
        ELSE SET @result='worse';
    END CASE;
    RETURN (@result);
END//
SELECT CaseSEAR(2007010105)//


/*循环结构*/
--WHILE语句
CREATE PROCEDURE EWhile()
BEGIN
    SET @i=1,@sum=0;
    WHILE @i<=100 DO
    BEGIN
        SET @sum=@sum+@i;
        SET @i=@i+1;
    END;
    END WHILE;
    SELECT @sum;
END//
CALL EWhile//

--REPEAT语句
CREATE PROCEDURE Rep()
BEGIN
    SET @i=1,@sum=0;
REPEAT
    BEGIN
    IF @i%2=0 THEN
        SET @sum=@sum+@i;
    END IF;
        SET @i=@i+1;
        END;
    UNTIL @i>100
END REPEAT;
SELECT @sum;
END//
CALL Rep//

--LOOP语句，以游标为例
CREATE PROCEDURE TestCur()
BEGIN
DECLARE done int DEFAULT 0;
DECLARE v_tno varchar(4) DEFAULT "";
DECLARE v_tname varchar(9) DEFAULT "";
DECLARE T_cursor CURSOR FOR SELECT Tno,Tname FROM teacher;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
/*游标遍历溢出时会出现一个预定义的NOT FOUND错误
手动定义一个继续运行的处理程序，即continue handler，然后以done=1作为结束标志*/
SET done=0;
OPEN T_cursor;
f_loop:LOOP    --命名loop循环为f_loop
FETCH T_cursor INTO v_tno,v_tname;
--FETCH后的变量列表（v_tno，v_tname），必须先定义
IF done=1 THEN    --done等于1作为报错标志，运行LEAVE跳出f_loop循环
    LEAVE f_loop;
ELSE
    SELECT v_tno AS '教师号',v_tname AS '教师姓名';
END IF;
END LOOP f_loop;
END//
CALL TestCur//

--以上代码请不要在网页端上下滑动反复看，点首行末端的复制键粘在记事本上即可，可以尝试运行一下
--前提是有这个数据库可以用，作者主页有个gradembak.psc就是这些代码运行的环境

触发器

它是一种特殊的存储过程，它与表紧密相连，它可以是表定义的一部分

优点：

• 自动执行，表的数据被修改后会立即激活
• 通过数据库中的相关表进行层叠更改
• 触发器可以强制限制，其限制比CHECK约束更复杂

创建触发器

CREATE TRIGGER 触发器名 {BEFORE | AFTER} 操作 ON

表名 FOR EACH ROW SQL语句;

说明：

{BEFORE | AFTER}：触发时机，指触发器在激活它的操作语句执行前或后触发

操作：INSERT，UPDATE，DELETE，LOAD DATA，REPLACE等操作

FOR EACH ROW：被操作内容中涉及到的行数会被触发器的SQL语句视为执行次数

SQL语句：触发器执行内容，在触发器中的SQL语句可以关联表中任何列，即OLD.列名和NEW.列名，以此来关联现有行的一列被更新、删除前或后的值；对INSERT，只有NEW是可用的，对DELETE，只有OLD是可用的，而UPDATE则两者都可用 

---

删除触发器

DROP TRIGGER 触发器名;

CREATE TRIGGER classnum   --创建触发器，在学生表被更新后，按更新后的学生所属班级
AFTER INSERT ON student FOR EACH ROW    --更新其对应班级人数
UPDATE class SET number=number+1 WHERE classno = LEFT(new.sno,8)//

--操作过程
mysql> SELECT number AS 插班前人数 FROM class WHERE classno='20070301'//
+------------+
| 插班前人数 |
+------------+
|         49 |
+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO student(sno,sname,ssex) VALUES('2007030148','李勇','男')//
Query OK, 1 row affected (0.01 sec)

mysql> SELECT number AS 插班后人数 FROM class WHERE classno='20070301'//
+------------+
| 插班后人数 |
+------------+
|         50 |
+------------+
1 row in set (0.00 sec)


CREATE TRIGGER snoUpdate    --创建触发器，当student表中sno更新时，sc表中sno也随之更新
AFTER UPDATE ON student FOR EACH ROW
BEGIN
IF new.sno!=old.sno THEN
    UPDATE sc SET sno=new.sno WHERE sno=old.sno;
END IF;
END//

--操作过程
mysql> SELECT a.sno,sname,degree FROM student a,sc b
    -> WHERE a.sno=b.sno AND a.sname='邓维杰'//
+------------+----------+--------+
| sno        | sname    | degree |
+------------+----------+--------+
| 2007010104 | 邓维杰   |     82 |
| 2007010104 | 邓维杰   |     83 |
| 2007010104 | 邓维杰   |     78 |
+------------+----------+--------+
3 rows in set (0.00 sec)

mysql> UPDATE student SET sno='2007010101' WHERE sno='2007010104'//
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT a.sno,sname,degree FROM student a,sc b
    -> WHERE a.sno=b.sno AND a.sname='邓维杰'//
+------------+----------+--------+
| sno        | sname    | degree |
+------------+----------+--------+
| 2007010101 | 邓维杰   |     82 |
| 2007010101 | 邓维杰   |     83 |
| 2007010101 | 邓维杰   |     78 |
+------------+----------+--------+
3 rows in set (0.00 sec)

 

上一篇入口：

        MySQL数据库查询与优化数据表