数据库基础(11) . SQL脚本
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;
原文地址:https://blog.csdn.net/yuanchun05/article/details/143655445
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!