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

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

article 2024/11/14 17:42:47

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()
BEGIN
  SET @result = 'Initial Value';
  SELECT @result;
END//
DELIMITER ;
CALL demo();

3.1.3.复位变量

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

SET @my_var = NULL;

3.2.局部变量

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

DELIMITER //
CREATE PROCEDURE demo()
BEGIN
  DECLARE 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)
BEGIN
  DECLARE salary DECIMAL(10, 2);
  SELECT salary INTO salary FROM employees WHERE id = emp_id;

  IF salary > 5000 THEN
    SELECT CONCAT('Employee with ID ', emp_id, ' has a high salary of ', salary);
  ELSEIF salary > 3000 THEN
    SELECT CONCAT('Employee with ID ', emp_id, ' has a moderate salary of ', salary);
  ELSE
    SELECT 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)
BEGIN
  DECLARE job_title VARCHAR(50);
  SELECT job_title INTO job_title FROM employees WHERE id = emp_id;

  SELECT
    CASE job_title
      WHEN '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)
BEGIN
  DECLARE 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 THEN
    SET @bonus = CASE job_title
                  WHEN 'Manager' THEN salary * 0.1
                  ELSE salary * 0.05
                END;
  ELSEIF salary > 3000 THEN
    SET @bonus = CASE job_title
                  WHEN 'Developer', 'Analyst' THEN salary * 0.03
                  ELSE salary * 0.01
                END;
  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()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE total INT DEFAULT 0;

  WHILE i <= 10 DO
    SET 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()
BEGIN
  DECLARE i INT DEFAULT 10;

  REPEAT
    SET 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()
BEGIN
  DECLARE i INT DEFAULT 1;

  outer_loop: LOOP
    IF i > 5 THEN
      LEAVE 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()
BEGIN
  DECLARE n INT DEFAULT 10;
  DECLARE a INT DEFAULT 0;
  DECLARE b INT DEFAULT 1;
  DECLARE i INT DEFAULT 1;

  WHILE i <= n DO
    SELECT 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))
BEGIN
  DECLARE 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)
BEGIN
  DECLARE exists BOOLEAN DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
      -- 当没有找到记录时，设置标志
      SET exists = FALSE;
    END;

  -- 尝试查找员工记录
  SELECT EXISTS(SELECT 1 FROM employees WHERE id = emp_id) INTO exists;

  -- 输出是否存在
  IF exists THEN
    SELECT 'Employee found.';
  ELSE
    SELECT '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))
BEGIN
    SELECT *
    FROM users
    WHERE 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
BEGIN
    IF NEW.age < 18 OR NEW.age > 120 THEN
        SIGNAL 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;