SQL中的数据库对象

news/2025/1/12 0:41:29/

视图：VIEW

  1. 概念
    ① 虚拟表，本身不存储数据，可以看做是存储起来的SELECT语句
    ② 视图中SELECT语句中涉及到的表，称为基表
    ③ 针对视图做DML操作，对影响到基表中的数据，反之亦然
    ④ 创建、删除视图本身，不会影响到基表

  2. 创建视图

    sql">CREATE VIEW view_emp_avg_salary(dept_id, avg_salary)
AS
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;

  3. 查看视图：DESC 视图名;

  4. 修改视图

    sql"># 方式一
CREATE OR REPLACE VIEW 视图名
AS
SELECT ...#方式二
ALTER VIEW 视图名
AS
SELECT ...

  5. 删除视图

    sql">DROP VIEW 视图名;

索引：INDEX

用于提高查询性能

存储过程：PROCEDURE

用于完成一次完整的业务处理，没有返回值，但可以通过传出参数将多个值传给调用者

  1. 创建
    ① 无参数无返回值
    sql"># 查询所有员工信息
DELIMITER $
CREATE PROCEDURE select_all_emp()
BEGINSELECT * FROM employees;
END $
DELIMITER;
# 调用
CALL select_all_emp();
    ② 无参数有返回值：OUT
    sql"># 查询员工中的最低工资，并将最低工资输出到ms
DELIMITER $
CREATE PROCEDURE select_min_sal(OUT ms DECIMAL)
BEGINSELECT MIN(salary) INTO msFROM employees;
END $
DELIMITER;
# 调用
CALL select_min_sal(@ms);
# 查看参数结果
SELECT @ms;
    ③ 有参数无返回值：IN
    sql"># 查询指定姓名的员工工资
DELIMITER $
CREATE PROCEDURE show_salary_by_lastname(IN lastName VARCHAR(50))
BEGINSELECT salaryFROM employeesWHERE last_name = lastName;
END $
DEIMITER;
# 调用
CALL show_salary_by_lastname('Popp');
    ④ 有参数有返回值：IN、OUT
    sql"># 查询指定姓名的员工工资和部门，并从参数中返回出来
DELIMITER $
CREATE PROCEDURE show_salary_by_lastname2(IN lastName VARCHAR(50), OUT sal DECIMAL, OUT dept_id INT)
BEGINSELECT salary,department_id INTO sal, dept_idFROM employeesWHERE last_name = lastName;
END $
DELIMITER;
# 调用
CALL show_salary_by_lastname2('Popp', @sal, @deptId);
# 查询结果
SELECT @sal, @deptId;
    ⑤ 有参数有返回值：INOUT
    sql"># 查询指定员工的领导姓名，并从参数中返回出来
DELIMITER $
CREATE PROCEDURE show_manager_by_lastname(INOUT lastName VARCHAR(50))
BEGINSELECT m.last_name INTO lastNameFROM employees e JOIN employees mON e.manager_id = m.employee_idWHERE e.last_name = lastName;
END $
DELIMITER;
# 调用
SET @lastname = 'Popp';
CALL show_manager_by_lastname(@lastname);
# 查询结果
SELECT @lastname;
  2. characteristics
    ① LANGUAGE SQL：表明存储过程执行体是由SQL语句构成的
    ② [NOT] DETERMINISTIC：存储过程的执行结果是否确定，默认为不确定
    ③ CONTAINS SQL：存储过程的执行体包含SQL语句，但不包含读写数据的SQL语句，默认是这个
    ④ NO SQL：存储过程的执行体不包含任何SQL语句
    ⑤ READS SQL DATA：存储过程的执行体包含读数据的SQL语句
    ⑥ MODIFIES SQL DATA：存储过程的执行体包含写数据的SQL语句
    ⑦ SQL SECURITY {DEFINER | INVOKER}：执行权限
    ⑧ COMMENT：备注信息
  3. 查看创建信息：SHOW CREATE PRODECURE 存储过程名称;
  4. 查看状态信息：SHOW PROCEDURE STATUS LIKE '存储过程名称';
  5. 修改特性
    sql">ALTER PROCEDURE '存储过程名称'
SQL SECURITY INVOKER
COMMENT '备注信息...';
  6. 删除：DROP PROCEDURE '存储过程名称';

存储函数：FUNCTION

用于完成一次特定的计算，有一个返回值

sql"># 查询指定部门的员工人数
DELIMITER $
CREATE FUNCTION select_count_by_dept(dept_id INT)
RETURNS INT
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGINRETURN(SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END $
DELIMITER;
# 使用
SELECT select_count_by_dept(50);

触发器：TRIGGER

相当于事件监听器，当数据库发生特定事件时，触发器被触发，完成相应的处理

  1. 创建

    代码格式：
    CREATE TRIGGER 触发器名称
    {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
    FOR EACH ROW
    BEGIN
    代码块;
    END;

    ① 案例一：向一张表中添加数据之前，同时向另一张表添加数据

    sql">DELIMITER //
CREATE TRIGGER test_before_trigger
BEFORE INSERT ON table1
FOR EACH ROW
BEGININSERT INTO table2(name)VALUES ('before_name...');
END //
DELIMITER;
# 测试
INSERT INTO table1(age)
VALUES(18);

    ② 案例二：在向employees表中添加员工信息之前，检验此员工的工资是否大于领导的工资，大于的话报错，否则添加成功（NEW代表添加的数据信息，OLD代表删除的数据信息）

    sql">DELIMITER //
CREATE TRIGGER verify_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGINDECLARE manager_sal DECIMAL DEFAULT 0.0;SELECT salary INTO manager_sal FROM employees WHERE employee_id = NEW.manager_id;IF NEW.salary > manager_sal# 抛出错误THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '添加失败：薪资高于领导工资';END IF;
END //
DELIMITER;

  2. 查看
    ① 查看数据库中的所有触发器：SHOW TRIGGERS;
    ② 查看某个触发器的定义：SHOW CREATE TRIGGER 触发器名称;

  3. 删除：DROP TRAGGER 触发器名称;

变量

  1. 系统变量：以@@开头
    ① 查看全局系统变量：SHOW GLOBAL VARIABLES;
    ② 查看会话系统变量
    sql">SHOW SESSION VARIABLES;
SHOW VARIABLES;
    ③ 查看指定系统变量
    sql">SELECT @@global.max_connections; # 查看服务器最大连接数
SELECT @@global.character_set_client; # 查看字符集
SELECT @@session.character_set_client; # 查看字符集
SELECT @@session.pseudo_thread_id; # 查看当前会话的MySQL连接ID
SELECT @@character_set_client; # 先查看会话的字符集，若没有再查看全局的字符集
    ④ 修改系统变量的值
    sql">SET @@global.max_connections = 166; # 修改全局变量，方式一
SET GLOBAL max_connection = 167; # 修改全局变量，方式二SET @@session.character_set_client = 'gbk'; # 修改会话变量，方式一
SET SESSION character_set_client = 'gbk'; # 修改会话变量，方式二
  2. 用户变量
    ① 会话用户变量：以@开头
    sql"># 声明和初始化
SET @a = 1;
SET @b := 2;
SELECT @avg_sal := AVG(salary) FROM employees; # 这种情况必须用:=
SELECT COUNT(*) INTO @emp_total FROM employees;
#使用
SELECT @a, @b, @avg_sal, @emp_total;
    ② 局部变量
    说明：
    a、使用关键字：DECLARE
    b、只能在存储过程和存储函数中使用
    c、必须写在BEGIN...END的首行
    d、格式：DECLARE 变量名 类型 [DEFAULT 值] ，若不指明DEFAULT，则默认是NULL
    sql">DELIMITER $
CREATE PROCEDURE test()
BEGIN# 声明局部变量DECLARE a INT DEFAULT 0;DECLARE b INT;DECLARE name VARCHAR(30);#赋值SET a = 5;SET b := 6;SELECT last_name INTO nameFROM employeesWHERE employee_id = 100;#使用SELECT a, b, name;
END $
DELIMITER;

定义条件与处理程序

  1. 定义条件
    ① 格式：DECLARE 错误名称 CONDITION FOR 错误码;
    ② 目的：给错误码命名
    ③ 错误码分类：MySQL_error_code、sqlstate_value
  2. 定义处理程序
    ① 格式：DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;
    ② 处理方式分类：
    a、CONTINUE：遇到错误不处理，继续执行
    b、EXIT：遇到错误马上退出
    c、UNDO：遇到错误后撤回之前的操作
    ③ 错误类型分类
    a、SQLSTATE '字符串错误码'：字符串类型错误码，长度为5
    b、MySQL_error_code：数值类型错误码
    c、错误名称：定义条件时声明的名称
    d、SQLWARNING：匹配所有以01开头的错误码
    e、NOT FOUND：匹配所有以02开头的错误码
    f、SQLEXCEPTION：匹配没有被d、e匹配到的错误码
  3. 案例
    sql">DELIMITER $
CREATE PROCEDURE UpdateTest()
BEGIN# 因email是NOT NULL，所以执行第一个UPDATE会报错“1048 - Column 'email' cannot be null”# 在此声明错误处理DECLARE CONTINUE HANDLER FOR 1048 SET @message = 'aaa';SET @a = 1;UPDATE employees SET email = NULL WHERE employee_id = 100;SET @a = 2;UPDATE employees SET email = '123@qq.com' WHERE employee_id = 100;SET @a = 3;
END $
DELIMITER;# 调用
CALL UpdateTest();
# 查看变量
SELECT @a, @message; // 3, aaa

流程控制

分支结构

  1. IF

    sql">DELIMITER //
CREATE PROCEDURE testIf()
BEGINDECLARE score DECIMAL(10,2) DEFAULT 85;IF score >= 90 THEN SELECT 'A';ELSEIF score >= 80 AND score < 90 THEN SELECT 'B';ELSEIF score >= 60 AND score < 80 THEN SELECT 'C';ELSE SELECT 'D';END IF;
END //
DELIMITER;CALL testIf();

  2. CASE
    ① CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END CASE;

    sql">DELIMITER //
CREATE PROCEDURE testCase()
BEGINDECLARE n INT DEFAULT 2;CASE nWHEN 1 THEN SELECT 'n = 1';WHEN 2 THEN SELECT 'n = 2';ELSE SELECT 'n = 9';END CASE;
END //
DELIMITER;

    ② CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END CASE;

    sql">DELIMITER //
CREATE PROCEDURE testCase()
BEGINDECLARE n INT default 10;CASEWHEN n > 20 THEN SELECT 'n > 20';WHEN n >= 10 THEN SELECT 'n >= 10';ELSE SELECT 'n < 10';END CASE;
END //
DELIMITER;

循环结构

  1. LOOP

    sql">DELIMITER //
CREATE PROCEDURE testLoop()
BEGINDECLARE n INT DEFAULT 1; # n = 1# 开始循环aaa: LOOPIF n >= 8THEN LEAVE aaa;END IF;SET n = n + 1;END LOOP aaa;SELECT n;
END //
DELIMITER;

  2. WHILE

    sql">DELIMITER //
CREATE PROCEDURE testWhile()
BEGINDECLARE n INT DEFAULT 1;WHILE n < 10DOSET n = n + 1;END WHILE;SELECT n;
END //
DELIMITER;

  3. REPEAT

    sql">DELIMITER //
CREATE PROCEDURE testRepeat()
BEGINDECLARE n INT DEFAULT 1;REPEATSET n = n + 1;UNTIL n > 10END REPEAT;SELECT n;
END //
DELIMITER;

跳转语句

  1. LEAVE
    ① 同BREAK，格式：LEAVE 循环名;
    ② 使用场景：循环体中、BEGIN...END中

    sql">DELIMITER //
CREATE PROCEDURE testLeave(IN num INT)
label_test: BEGINIF num = 0 THEN LEAVE label_test;ELSEIF num < 5 THEN SELECT num;ELSE SELECT 'num >= 5';END IF;SELECT 'is end';
END //
DELIMITER;

  2. ITERATE
    ① 同CONTINUE，格式：ITERATE 循环名;
    ② 使用场景：循环体中

    sql">DELIMITER //
CREATE PROCEDURE test_iterate()
BEGINDECLARE num  INT DEFAULT 0;iterate_test: LOOPSET num = num + 1;IF num > 5 THEN LEAVE iterate_test;ELSEIF num > 2 THEN ITERATE iterate_test;ELSE SELECT 'is end';END IF;END LOOP;SELECT num;
END //
DELIMITER;

游标

随意的定位到某一条记录，可以逐条读取数据。在使用游标过程中，会对数据加锁，不适合高并发场景。

sql">DELIMITER //
CREATE PROCEDURE test_cursor(IN limit_total_salary DECIMAL, OUT total_count INT)
BEGIN# 声明变量DECLARE sum_sal DECIMAL DEFAULT 0.0;DECLARE emp_count INT DEFAULT 0;DECLARE emp_sal DECIMAL;# 声明游标DECLARE cursor_test CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;# 打开游标OPEN cursor_test;# 使用游标WHILE sum_sal < limit_total_salaryDOFETCH cursor_test INTO emp_sal;SET sum_sal = sum_sal + emp_sal;SET emp_count = emp_count + 1;END WHILE;SET total_count = emp_count;# 关闭游标CLOSE cursor_test;
END //
DELIMITER;
# 调用
CALL test_cursor(100000, @count);
SELECT @count;

http://www.ppmy.cn/news/1562369.html

相关文章

windows10下安装Microsoft SQL Server 2016

windows10下安装Microsoft SQL Server 2016

一、下载安装包 网站&#xff1a;MSDN, 我告诉你 - 做一个安静的工具站 选择需要的版本&#xff0c;点击详细信息&#xff0c;复制ed2k链接&#xff0c;打开eMule或迅雷&#xff0c;新建下载&#xff0c;粘贴链接&#xff0c;开始下载。 下载好的文件是一个.iso镜像文件。 二、…
阅读更多...
设计模式 行为型 访问者模式（Visitor Pattern）与 常见技术框架应用 解析

设计模式 行为型 访问者模式（Visitor Pattern）与 常见技术框架应用 解析

访问者模式&#xff08;Visitor Pattern&#xff09;是一种行为设计模式&#xff0c;它允许你在不改变元素类的前提下定义作用于这些元素的新操作。这种模式将算法与对象结构分离&#xff0c;使得可以独立地变化那些保存在复杂对象结构中的元素的操作。 假设我们有一个复杂的对…
阅读更多...
ETL的工作原理

ETL的工作原理

ETL的工作原理 什么是ETL_云计算主题库-阿里云 ETL的工作原理可以分为三个主要的步骤&#xff1a;Extract&#xff08;提取&#xff09;、Transform&#xff08;转换&#xff09;、Load&#xff08;加载&#xff09;。 工作步骤 描述 Extract &#xff08;提取&#xff09;…
阅读更多...
C# winform 多线程 UI更新数据 报错：无法访问已释放的对象。

C# winform 多线程 UI更新数据 报错：无法访问已释放的对象。

System.ObjectDisposedException HResult0x80131622 Message无法访问已释放的对象。 ObjectDisposed_ObjectName_Name SourceSystem.Windows.Forms StackTrace: at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, …
阅读更多...
【Spring Boot 应用开发】-04 自动配置-数据源

【Spring Boot 应用开发】-04 自动配置-数据源

深入讲解 Spring Boot 自动配置中的数据源配置 为了更好地理解 Spring Boot 中的自动配置机制&#xff0c;我们以数据源配置机制为例&#xff0c;按照以下顺序进行讲解&#xff1a; 不使用任何框架来连接数据源的方式使用 Spring MVC 连接数据源的方式使用 Spring Boot 自动配…
阅读更多...
【UI自动化测试】selenium操作补充

【UI自动化测试】selenium操作补充

&#x1f3e1;个人主页&#xff1a;謬熙&#xff0c;欢迎各位大佬到访❤️❤️❤️~ &#x1f472;个人简介&#xff1a;本人编程小白&#xff0c;正在学习互联网求职知识…… 如果您觉得本文对您有帮助的话&#xff0c;记得点赞&#x1f44d;、收藏⭐️、评论&#x1f4ac;&am…
阅读更多...
Lianwei 安全周报|2024.1.7

Lianwei 安全周报|2024.1.7

以下是本周「Lianwei周报」&#xff0c;我们总结推荐了本周的政策/标准/指南最新动态、热点资讯和安全事件&#xff0c;保证大家不错过本周的每一个重点&#xff01; 政策/标准/指南最新动态 01 国家发改委等三部门印发《国家数据基础设施建设指引》 国家数据基础设施是从数据…
阅读更多...
【微信小程序】回到顶部图标-页面滚动事件 | 漫画-综合实训

【微信小程序】回到顶部图标-页面滚动事件 | 漫画-综合实训

一、回到顶部-页面滚动事件 在微信小程序中&#xff0c;如果你想将“回到顶部”的功能按钮换成图标&#xff0c;你可以通过以下步骤实现&#xff1a; 1. 准备图标 首先&#xff0c;你需要准备一个图标图片。这个图标可以是任何你选择的格式&#xff0c;如 PNG 或 SVG。将图标…
阅读更多...
最新文章

Copyright @ 2022~2023