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

数据库基础(11) . SQL脚本

news 2024/11/16 0:05:42

1.概述

SQL脚本 : 是由一系列SQL命令组成在一起执行以完成特定的任务。

SQL脚本通常用于执行批量操作，如创建数据库对象（表、视图、存储过程等）、插入数据、执行批处理更新等。

1.1.标识符命名

对象起名（变量、常量、函数等）、注意命名规则

1. 字符集和大小写敏感性
  • 字符集：标识符通常使用ASCII字符集，也可以使用Unicode字符集。MySQL默认支持UTF8字符集。
  • 大小写敏感性：MySQL默认情况下对标识符是大小写不敏感的（在大多数平台上）。但是，如果使用反引号（`）包裹标识符，则可以强制大小写敏感性。
2. 长度限制
  • MySQL：在MySQL中，标识符的最大长度为64个字符（MySQL 5.0.3 及以上版本）。
  • 其他数据库：不同的数据库系统可能有不同的长度限制。例如，Oracle允许最长为30个字符的标识符。
3. 首字符要求
  • 标识符的第一个字符必须是字母（A-Z, a-z）、下划线（_）或某些特殊字符（如 @、$）。
4. 后续字符要求
  • 标识符中的其他字符可以是字母、数字（0-9）、下划线（_）或某些特殊字符（如 @、$）。
5. 避免关键字
  • 标识符不应使用SQL保留关键字（如 SELECT, FROM, WHERE 等）。如果确实需要使用关键字作为标识符，可以使用反引号（`）包裹起来。
6. 反引号包裹

  • 如果标识符包含特殊字符或空格，或者需要强制大小写敏感性，可以使用反引号（`）包裹标识符。

    SELECT `user ID` FROM `My Table`;

1.2.操作符优先级

1. 括号 ( 和 )

  • 优先级最高：括号内的表达式优先执行。

    SELECT (1 + 2) * 3; -- 输出 9
2. 字符串连接 CONCAT 或 ||

  • 优先级较高：字符串连接操作。

    SELECT CONCAT('Hello', ' ', 'World'); -- 输出 'Hello World'
3. 位运算符

~, <<, >>, &, |

  • 优先级较高：位运算操作。

    SELECT 5 & 3; -- 输出 1
SELECT 5 << 1; -- 输出 10
4. 负号 - 和正号 +

  • 优先级较高：一元负号和正号。

    SELECT -5 + 3; -- 输出 -2
5. 算术运算符

*, /, %

  • 优先级较高：乘法、除法和取模。

    SELECT 10 / 2; -- 输出 5
SELECT 10 % 3; -- 输出 1
6. 算术运算符

+, -

  • 优先级次之：加法和减法。

    SELECT 1 + 2; -- 输出 3
SELECT 3 - 1; -- 输出 2
7. 比较运算符

=, <>, <, >, <=, >=, BETWEEN, IN, LIKE, NOT LIKE, IS NULL, IS NOT NULL, REGEXP, NOT REGEXP

  • 优先级次之：比较运算符。

    SELECT 1 = 1; -- 输出 TRUE
SELECT 1 < 2; -- 输出 TRUE
SELECT 'abc' LIKE '%a%'; -- 输出 TRUE
8. 逻辑运算符 NOT

  • 优先级较高：逻辑非运算符。

    SELECT NOT (1 = 1); -- 输出 FALSE
9. 逻辑运算符 AND

  • 优先级较高：逻辑与运算符。

    SELECT (1 = 1) AND (2 = 2); -- 输出 TRUE
10. 逻辑运算符 OR

  • 优先级较低：逻辑或运算符。

    SELECT (1 = 1) OR (2 = 3); -- 输出 TRUE

2.基本语法

2.1.注释

SQL脚本中可以使用单行或多行注释：

  • 单行注释：使用 -- 或 # 开头。

    -- 这是一条单行注释
# 这也是一条单行注释

  • 多行注释：使用 /* */ 包围。

    /*
这是一段多行注释
*/

2.2.创建数据库对象

创建表
CREATE TABLE table_name (column1 datatype constraints,column2 datatype constraints,...
);
创建视图
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
创建存储过程
DELIMITER //
CREATE PROCEDURE procedure_name (IN param1 datatype, ...)
BEGIN-- SQL语句
END//
DELIMITER ;
创建触发器
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN-- SQL语句
END;

2.3.数据操作

插入数据
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
更新数据
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
删除数据
DELETE FROM table_name
WHERE condition;
数据查询
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 ASC/DESC;

2.4.事务管理

开始事务
START TRANSACTION;
提交事务
COMMIT;
回滚事务
ROLLBACK;

3.变量

常量：程序运行当中值不变的量，定义常量的格式取决于它所表示的值的数据类型
变量：程序运行当中值会改变的量。

3.1.@var用户变量

用户变量以 @ 符号开头，可以用来存储临时数据，并在多个查询中重复使用。

重要的是要注意，用户变量的作用域仅限于当前会话。这意味着在一个客户端设置的用户变量不会影响到另一个客户端。

3.1.1.声明和赋值

你可以直接给用户变量赋值，也可以通过 SELECT ... INTO 语句来赋值。

3.1.1.1.直接赋值

= 对该用户变量进行赋值.
用户变量赋值有两种方式: 一种是直接用" = “号，另一种是用” := “号。
其区别在于:
使用set命令对用户变量进行赋值时，两种方式都可以使用；
用select语句时，只能用” := “方式，因为select语句中，” = "号被看作是比较操作符。

set @my_var = 'Hello, World!';

用select语句时

select @i := 0 a;
3.1.1.2.使用 SELECT…INTO 赋值
select  'Hello, World!' into @my_var;
3.1.1.3.多行赋值

可以同时给多个用户变量赋值。

set @var1 = 'Hello', @var2 = 'World';
select @var1, @var2; -- 输出 'Hello', 'World'

3.1.2.在查询中使用

用户变量可以在 SELECT 语句、INSERT 语句等中使用。

3.1.2.1.输出
set @my_var = 'Hello, World!';
select @my_var;    -- 输出 'Hello, World!'
3.1.2.2.作为临时表使用
SELECT * FROM team, (SELECT @i:= 0 a) t;
3.1.2.3.作为计算的一部分

用户变量可以用在计算表达式中。

SET @num1 = 10;
SET @num2 = 20;
SELECT @num1 + @num2; -- 输出 30
3.1.2.4.数据累计计算
SELECT *,(@i:=  @i + pt.person_type_id) idadd FROM person_type pt,(SELECT @i:= 0 ) t;SELECT pt.*, @i := @i +1 rownum FROM person_type pt,(SELECT @i:= 0 a) t;

用户变量可以用来累积结果。

SET @total = 0;
SELECT (@total := @total + team.stu_count) AS running_total
FROM team;
3.1.2.5.动态查询构建

用户变量可以用来构建动态的 SQL 查询。

SET @dynamic_query = 'SELECT * FROM team WHERE team_id = 1';
PREPARE stmt FROM @dynamic_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
3.1.2.6.存储过程中的使用

用户变量也可以在存储过程中使用，使得过程更灵活。

DELIMITER //
CREATE PROCEDURE demo()
BEGINSET @result = 'Initial Value';SELECT @result;
END//
DELIMITER ;
CALL demo();

3.1.3.复位变量

如果需要重置用户变量，可以直接给它赋一个新的值。

SET @my_var = NULL;

3.2.局部变量

局部变量只能在存储过程或函数内部使用，并且必须先声明再使用。使用declare来定义局部变量

DELIMITER //
CREATE PROCEDURE demo()
BEGINDECLARE local_var VARCHAR(50);SET local_var = 'Hello from procedure';SELECT local_var;
END //
DELIMITER ;
CALL demo();

3.3.系统变量

系统变量用于存储 MySQL 服务器的状态信息，它们通常是只读的，但也有一些是可以修改的。系统变量有两种类型：会话变量和全局变量。

-- 查看当前时间
SELECT @@global.time_zone, @@session.time_zone;-- 修改会话时区
SET time_zone = '+00:00';-- 恢复会话时区为全局设置
SET SESSION time_zone = @@global.time_zone;

4.控制流语句

4.1.条件语句

在 MySQL 中，条件语句通常用于存储过程或函数中，以实现基于不同条件执行不同操作的逻辑。MySQL 主要使用 IF, CASE 语句来实现条件分支。

4.1.1.使用 IF 语句

IF 语句允许你在满足特定条件时执行一段代码块。如果条件不满足，则可以选择执行另一个代码块或不执行任何操作。

IF condition THEN-- SQL语句
ELSEIF condition THEN-- SQL语句
ELSE-- SQL语句
END IF;
示例：使用 IF 语句检查员工的工资是否超过某个阈值，并相应地打印消息。
DELIMITER //
CREATE PROCEDURE check_salary(IN emp_id INT)
BEGINDECLARE salary DECIMAL(10, 2);SELECT salary INTO salary FROM employees WHERE id = emp_id;IF salary > 5000 THENSELECT CONCAT('Employee with ID ', emp_id, ' has a high salary of ', salary);ELSEIF salary > 3000 THENSELECT CONCAT('Employee with ID ', emp_id, ' has a moderate salary of ', salary);ELSESELECT CONCAT('Employee with ID ', emp_id, ' has a low salary of ', salary);END IF;
END//
DELIMITER ;-- 调用存储过程
CALL check_salary(1);

4.1.2.使用 CASE 语句

CASE 语句提供了一种更简洁的方式来处理多分支条件判断。它可以根据不同的条件返回不同的结果。

示例：使用 CASE 语句根据员工的工作岗位打印不同的职位级别。
DELIMITER //
CREATE PROCEDURE print_job_level(IN emp_id INT)
BEGINDECLARE job_title VARCHAR(50);SELECT job_title INTO job_title FROM employees WHERE id = emp_id;SELECTCASE job_titleWHEN 'Manager' THEN 'Senior Level'WHEN 'Developer', 'Analyst' THEN 'Mid Level'ELSE 'Entry Level'END AS Job_Level;
END//
DELIMITER ;-- 调用存储过程
CALL print_job_level(1);

4.1.3.结合 IF 和 CASE 语句

有时候，你可能需要结合使用 IF 和 CASE 语句来实现更复杂的逻辑。

示例：结合 IF 和 CASE 语句来确定员工的年终奖金。
DELIMITER //
CREATE PROCEDURE calculate_bonus(IN emp_id INT)
BEGINDECLARE salary DECIMAL(10, 2);DECLARE job_title VARCHAR(50);SELECT salary, job_title INTO salary, job_title FROM employees WHERE id = emp_id;SET @bonus = 0;IF salary > 5000 THENSET @bonus = CASE job_titleWHEN 'Manager' THEN salary * 0.1ELSE salary * 0.05END;ELSEIF salary > 3000 THENSET @bonus = CASE job_titleWHEN 'Developer', 'Analyst' THEN salary * 0.03ELSE salary * 0.01END;END IF;SELECT CONCAT('Employee with ID ', emp_id, ' will get a bonus of ', @bonus);
END//
DELIMITER ;-- 调用存储过程
CALL calculate_bonus(1);

这些示例展示了如何在 MySQL 存储过程中使用 IF 和 CASE 语句来实现条件分支逻辑。通过这些例子，你可以更好地理解如何在实际应用中使用这些条件语句。

4.2.循环语句

在 MySQL 中，循环语句通常用于存储过程或函数中，以便重复执行一组操作。MySQL 提供了几种不同的循环结构，包括 REPEAT, WHILE, 和 LOOP。

4.2.1. WHILE 循环

WHILE 循环会在指定的条件为真时重复执行一组语句。一旦条件变为假，循环就会终止。

WHILE condition DO-- SQL语句
END WHILE;
示例：计算 1 到 10 的累加和
DELIMITER //
CREATE PROCEDURE sum_numbers()
BEGINDECLARE i INT DEFAULT 1;DECLARE total INT DEFAULT 0;WHILE i <= 10 DOSET total = total + i;SET i = i + 1;END WHILE;SELECT total;
END//
DELIMITER ;-- 调用存储过程
CALL sum_numbers();

4.2.2. REPEAT 循环

REPEAT 循环会无条件地执行一组语句，直到指定的条件变为真为止。这是 WHILE 循环的逆向形式。

示例：找到第一个大于 10 的偶数
DELIMITER //
CREATE PROCEDURE find_first_even_greater_than_10()
BEGINDECLARE i INT DEFAULT 10;REPEATSET i = i + 1;UNTIL i % 2 = 0 END REPEAT;SELECT i;
END//
DELIMITER ;-- 调用存储过程
CALL find_first_even_greater_than_10();

4.2.3. LOOP 循环

LOOP 是一种通用的循环结构，可以用来重复执行一组语句，直到显式地通过 LEAVE 或 ITERATE 语句退出循环。

LOOP label: LOOP-- SQL语句LEAVE label;-- SQL语句
END LOOP;
示例：打印 1 到 5 的数字
DELIMITER //
CREATE PROCEDURE print_numbers()
BEGINDECLARE i INT DEFAULT 1;outer_loop: LOOPIF i > 5 THENLEAVE outer_loop; -- 退出循环END IF;SELECT i;SET i = i + 1;END LOOP outer_loop;
END//
DELIMITER ;-- 调用存储过程
CALL print_numbers();

4.2.4.综合示例：计算斐波那契数列

下面是一个综合示例，使用 WHILE 循环来计算斐波那契数列的前 10 项。

DELIMITER //
CREATE PROCEDURE fibonacci_sequence()
BEGINDECLARE n INT DEFAULT 10;DECLARE a INT DEFAULT 0;DECLARE b INT DEFAULT 1;DECLARE i INT DEFAULT 1;WHILE i <= n DOSELECT a;SET a = b - a;SET b = b + a;SET i = i + 1;END WHILE;
END//
DELIMITER ;-- 调用存储过程
CALL fibonacci_sequence();

5.错误处理

在 MySQL 中，DECLARE CONTINUE HANDLER 语句用于在存储过程或函数中定义错误处理逻辑。它可以用来处理特定类型的条件（例如 SQL 警告或特定错误码），并在发生这些条件时执行特定的操作。这对于处理运行时错误或特殊情况非常有用。

语法格式

DECLARE CONTINUE HANDLER FOR condition_type
HANDLER_STMT;

其中：

  • condition_type：指定要处理的条件类型，可以是具体的 SQLSTATE 值、SQL 警告 (SQLWARNING)、NOT FOUND (NOT FOUND) 或者 SQLEXCEPTION。
  • HANDLER_STMT：当发生指定的 condition_type 时要执行的语句。

示例：处理除零错误

假设我们需要处理一个可能发生的除零错误，并在发生错误时给出提示信息而不是中断程序执行。

示例：除零错误处理
DELIMITER //
CREATE PROCEDURE safe_division(IN num1 DECIMAL(10, 2), IN num2 DECIMAL(10, 2))
BEGINDECLARE division_result DECIMAL(10, 2);DECLARE exit_handler_for_division_by_zero CONDITION FOR SQLSTATE '22012';-- 定义错误处理程序DECLARE CONTINUE HANDLER FOR SQLSTATE '22012'BEGIN-- 当发生除零错误时，输出错误信息并继续执行SELECT 'Error: Division by zero occurred.';END;-- 尝试执行除法运算SET division_result = num1 / num2;-- 输出结果SELECT division_result;
END//
DELIMITER ;-- 调用存储过程
CALL safe_division(10, 0); -- 触发除零错误

在这个例子中：

  • 我们定义了一个名为 safe_division 的存储过程，该过程接受两个参数 num1 和 num2。
  • 如果 num2 为零，则会导致除零错误（SQLSTATE ‘22012’）。
  • 使用 DECLARE CONTINUE HANDLER 定义了一个错误处理程序，当发生除零错误时，会输出一条错误信息并继续执行后续代码。

示例：处理 NOT FOUND 错误

假设我们需要处理一个查询没有找到记录的情况，并给出提示。

示例：处理未找到记录
DELIMITER //
CREATE PROCEDURE check_employee_exists(IN emp_id INT)
BEGINDECLARE exists BOOLEAN DEFAULT FALSE;DECLARE CONTINUE HANDLER FOR NOT FOUNDBEGIN-- 当没有找到记录时，设置标志SET exists = FALSE;END;-- 尝试查找员工记录SELECT EXISTS(SELECT 1 FROM employees WHERE id = emp_id) INTO exists;-- 输出是否存在IF exists THENSELECT 'Employee found.';ELSESELECT 'Employee not found.';END IF;
END//
DELIMITER ;-- 调用存储过程
CALL check_employee_exists(999); -- 假设不存在此员工ID

在这个例子中：

  • 存储过程 check_employee_exists 接受一个员工 ID，并检查该员工是否存在。
  • 使用 DECLARE CONTINUE HANDLER FOR NOT FOUND 定义了一个错误处理程序，当没有找到记录时，会设置一个标志 exists 为 FALSE。
  • 最后根据 exists 的值输出相应的消息。

通过这些示例，你可以了解如何在 MySQL 存储过程中使用 DECLARE CONTINUE HANDLER 来处理特定类型的错误或条件，并根据需要采取适当的措施。

6.示例：完整的SQL脚本

以下是一个包含多种SQL命令的示例脚本：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS sample_db;-- 使用数据库
USE sample_db;-- 创建表
CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE NOT NULL,age INT CHECK (age >= 18 AND age <= 120),registration_date DATE DEFAULT CURRENT_DATE
);-- 插入数据
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 25);-- 更新数据
UPDATE users
SET age = 26
WHERE name = 'Alice';-- 删除数据
DELETE FROM users
WHERE age > 30;-- 查询数据
SELECT *
FROM users
WHERE age > 20
ORDER BY registration_date DESC;-- 创建视图
CREATE VIEW user_info AS
SELECT name, email, age
FROM users;-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_user_by_name(IN user_name VARCHAR(50))
BEGINSELECT *FROM usersWHERE name = user_name;
END//
DELIMITER ;-- 调用存储过程
CALL get_user_by_name('Alice');-- 开始事务
START TRANSACTION;-- 更新数据
UPDATE users
SET age = 27
WHERE name = 'Alice';-- 提交事务
COMMIT;-- 创建触发器
CREATE TRIGGER update_user_age
BEFORE UPDATE ON users
FOR EACH ROW
BEGINIF NEW.age < 18 OR NEW.age > 120 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid age.';END IF;
END;-- 测试触发器
UPDATE users
SET age = 17
WHERE name = 'Alice';-- 错误处理
DECLARE CONTINUE HANDLER FOR SQLSTATE '45000'BEGIN-- 处理错误SELECT 'Invalid data detected and handled.';END;

http://www.mrgr.cn/news/69573.html

相关文章：

  • 怎么样绑定域名到AWS（亚马逊云）服务器
  • .net core开发windows程序在国产麒麟操作系统中运行
  • 关于深度学习的一些工具安装与细节
  • 数据结构——快速排序
  • 人工智能数据栈互操作性架构师指南
  • 基于深度学习的路面裂缝检测算法matlab仿真
  • 用ArkTS写一个登录页面（实现简单的逻辑）
  • 1.4 算法设计策略与分析方法
  • 测试实项中的偶必现难测bug--苹果支付丢单问题
  • Linux2 指令（二）
  • 计算机网络（3）
  • 基于Springboot+Vue的心理咨询系统 （含源码数据库）
  • 如何选情绪龙头
  • 单页面应用和多页面应用区别及优缺点
  • 【Docker容器化技术】docker安装与配置、常用命令、容器数据卷、应用部署实战、Dockerfile、服务编排docker-compose、私有仓库
  • 从认识 VNode VDOM 到实现 mini-vue
  • 【含文档】基于ssm+jsp的流浪动物收养系统（含源码+数据库+lw）
  • 关于我的编程语言——C/C++——第八篇
  • 大华Android面试题及参考答案
  • C#实现：电脑系统信息的全面获取与监控
  • cell队列监控
  • Redis相关技术内容
  • 花指令例子
  • Java期末复习暨学校第二次上机课作业
  • Python | Leetcode Python题解之第554题砖墙
  • 系统安全第七次作业题目及答案