精华内容
下载资源
问答
  • MySQL视图存储过程

    2021-03-15 00:51:11
    在线QQ客服:1922638专业的SQL Server、MySQL数据库同步软件什么是视图(视图)虚拟表内容类似于真实表,有字段和记录该视图不以数据库中存储的数据的形式存在行和列的数据来自查询视图定义时引用的基表,并且是在特定...

    bc3f9276acf88283a0569b9b9ef9ee92.png

    在线QQ客服:1922638

    专业的SQL Server、MySQL数据库同步软件

    什么是视图(视图)

    虚拟表

    内容类似于真实表,有字段和记录

    该视图不以数据库中存储的数据的形式存在

    行和列的数据来自查询视图定义时引用的基表,并且是在特定引用视图时动态生成的

    更新视图的数据就是更新基表的数据

    更新基本表数据,并且视图数据将相应更改

    查看优势

    简单

    用户无需关心如何在视图中查询和获取数据

    视图中的数据已经是经过过滤且合格的结果集

    安全

    用户只能在视图中查看数据

    数据独立

    确定视图结果后,就可以屏蔽表结构对用户的影响

    查看使用限制

    无法在视图上创建索引

    子查询不能在视图的from子句中使用

    以下情况下的视图不可更新

    包含以下关键字的SQL语句:聚合函数(SUM,MAX,COUNT等),DISTINCT,GROUP BY,HAVING,UNION或UNION ALL

    恒定视图,JOIN,来自无法更新的视图

    WHERE子句中的子查询引用FROM子句中的表

    使用的临时表

    创建视图

    语法格式

    创建视图视图名称作为SQL查询;

    创建视图视图名称(字段名称列表)作为SQL查询;

    注意:如果未在视图中定义字段名称,则默认使用基表的字段名称。如果定义了字段名称,则视图表中的字段数必须等于基本表中的字段数

    在视图中修改和删除数据时,基表也会更改,而在视图中修改和删除基表也将更改

    查询视图

    从条件名称的视图名称中选择字段名称列表;

    插入记录

    在视图名称(字段名称列表)中插入值(字段值列表);

    更新记录

    更新视图名称设置字段名称=值,其中;

    删除记录

    从条件名称中删除视图名称;

    删除视图

    语法格式

    放下视图的视图名称;

    查看当前库下所有表的状态信息

    显示表状态;

    显示表状态,其中comment =” view” \\\\ G;

    查看用于创建视图的特定命令

    显示创建视图的视图名称;

    用于创建视图的完整格式

    命令格式

    设置字段别名

    视图中的字段名称不能重复,因此请定义别名

    创建视图时定义字段别名

    重要选项的说明

    或替换

    创建或替换视图视图名称作为选择查询

    创建时,如果视图已经存在,它将替换现有视图

    创建视图时的算法

    带有复选选项以限制视图操作

    本地关键字和级联关键字确定检查范围

    本地仅检查当前视图的局限性

    级联也满足基本表的限制(默认)

    创建t5表

    View v11不仅受到gid的限制, =基表v10的100,也由其自己的gid 30个条件

    存储过程简介

    存储过程等效于由MySQL语句组成的脚本

    (1)引用存储在数据库中的SQL命令的集合

    (2)在存储过程中可以使用变量,条件判断,过程控制等

    存储过程的优点

    提高性能

    可以减轻网络负担

    可以防止直接访问表

    避免重复执行SQL操作

    基本用途

    语法格式

    delimiter关键字用于指定存储过程的分隔符(默认为;)

    如果未指定定界符,则编译器会将存储过程视为SQL语句,因此执行错误

    创建一个存储过程:

    查看存储过程

    方法1

    mysql \ gt;显示程序状态;

    方法2

    mysql>选择数据库名称,从mysql.proc中键入,其中name =”存储过程名称”;

    调用/删除存储过程

    调用存储过程

    调用存储过程名称;

    删除存储过程

    删除过程存储过程名称;

    调用存储过程

    变量类型

    变量类型:

    名称

    说明

    会话变量

    会话变量和全局变量称为系统变量,它们是使用set命令定义的

    全局变量

    全局变量的修改将影响整个服务器,但是会话变量的修改将仅影响当前会话。选择@@主机名;

    用户变量

    在客户端连接到数据库服务的整个过程中有效。当前连接断开时,所有用户变量均无效。定义set @ varname =值输出select @varname;

    局部变量

    存储过程的开始/结束,其有效范围仅限于语句块。执行语句块后,变量无效,并使用declare定义局部变量

    注意:调用@

    时,不需要在变量名称之前添加局部变量和参数变量

    会话变量

    全局变量

    用户变量

    局部变量(在存储过程中定义)

    使用查询结果为变量赋值

    (1)

    (2)

    参数类型

    调用参数时,无需在名称前添加@

    创建过程名称(

    类型参数名称数据类型,

    类型参数名称数据类型

    )

    算术运算

    4f56293304130404a590978c8dfad0cc.png

    关键字

    名称

    说明

    in

    输入参数

    该功能是将值传递给存储过程。在调用存储过程时必须对其进行分配。在存储过程中无法修改此参数的值;默认类型为

    退出

    输出参数

    此值可以在存储过程中更改,并且可以返回

    inout

    输入/输出参数

    在调用时指定,并且可以更改和返回

    输入用户名并在userdb表中找到用户信息

    输出x + y值z

    输出满足条件的i + j值

    inout用法

    流量控制

    条件判断,数值比较

    04422a8de59122ac18a34fb72224e273.png

    逻辑比较,范围,空,非空,模糊,常规

    a09e90a0afa05e2fec6a0d2b6a4252cd.png0L0插入图片>

    (1)如果选择格式

    如果条件测试结束

    代码

    万一;

    如果有条件测试,则

    代码

    其他

    代码

    万一;

    (2)while循环结构

    反复测试条件,并在条件成立后执行命令序列

    进行条件判断时

    环体

    ……

    结束

    (3)循环死循环

    无条件重复执行一段代码

    循环

    循环主体

    ……

    结束循环

    (4)重复条件循环

    条件为真时结束循环

    重复

    环体

    ……

    直到有条件的标准

    结束重复

    (5)离开控制循环,适用于while,循环,重复正文

    离开标签名称//跳出寻欢

    重复标记名称//放弃此循环

    展开全文
  • MySQL视图存储过程

    2021-01-19 02:56:23
    视图视图是一种特殊的表,但不包含应有的任何列和数据,只包含使用时动态检索数据的查询(即:sql查询语句)。使用视图的目的,在本质上就是用来查看存储在别处的数据。【引例】/*使用sql查询*/select cust_name,...

    MySQL视图和存储过程

    一. 视图

    视图是一种特殊的表,但不包含表中应有的任何列和数据,只包含使用时动态检索数据的查询(即:sql查询语句)。

    使用视图的目的,在本质上就是用来查看存储在别处的数据。

    【引例】

    /*使用sql查询*/

    select cust_name,cust_contact from customers,orders,orderitems

    where customers.cust_id=orders.cust_id

    and orderitems.order_num=orders.order_num

    and prod_id='TNT2';

    查询结果

    cust_name

    cust_contact

    1

    Yosemite Place

    Y Sam

    2

    Coyote Inc.

    Y Lee

    /*创建视图*/

    create view productcustomers AS

    select cust_name,cust_contact,prod_id

    from customers,orders,orderitems

    where customers.cust_id=orders.cust_id

    and orderitems.order_num=orders.order_num;

    /*查询结果*/

    select cust_name,cust_contact from productcustomers where prod_id='TNT2';

    查询结果

    cust_name

    cust_contact

    1

    Yosemite Place

    Y Sam

    2

    Coyote Inc.

    Y Lee

    从此引例中可以看出,所谓视图就是封装了一堆的sql查询语句

    ⭐【作用】

    重用sql。

    简化复杂的sql操作,封装后可以方便的使用视图,而不必知道它的基本查询细节。

    保护数据,可以只授予表的特定部分的访问权限而不是整个表的访问权限。

    ⭐【规定】

    名字唯一(不能与表名和其他视图名重名)

    视图不能创建索引,也不能有关联的触发器

    视图和表可以混着用

    【案例一】用视图封装格式

    /*

    concat()函数

    功能:将多个字符串连接成一个字符串。

    语法:concat(str1, str2,...)

    返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。

    RTRIM(str)

    返回删除了后面空格字符的字符串str。

    LTRIM(str)

    返回删除了前面空格字符的字符串str

    */

    select CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title

    from vendors

    order by vend_name;

    查询结果

    vend_title

    1

    ACME(USA)

    2

    Anvils R Us(USA)

    3

    Furball Inc.(USA)

    4

    Jet Set(England)

    5

    Jouets Et Ours(France)

    6

    LT Supplies(USA)

    /*创建视图*/

    create view vendorlocation as

    select CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title

    from vendors

    order by vend_name;

    /*使用视图*/

    select * from vendorlocation;

    二. 存储过程

    先考虑这么一个问题:当我们执行某个处理需要针对许多表的多条sql语句,语句执行的顺序也是不固定的,可能会随某些数据在表中存不存在而发生变化,这个时候怎么处理?

    简单来说,存储过程就是为了方便以后使用而事先保存的sql语句集合。

    【引例】

    /*

    创建存储过程

    1. 如果需要参数,可以在()中给出,即使没有参数,也要写()。

    2. 用 begin 和 end 来限制执行体,end要分号结尾。

    */

    create procedure productprice()

    begin

    select AVG(prod_price) as priceAvg from products;

    end;

    /*调用存储过程*/

    call productprice();

    结果展示

    priceAvg

    1

    16.133571

    🌞可以将存储过程理解成sql中的函数

    【案例一】使用参数(传出)

    /*

    创建存储过程:

    1. 此存储过程接收三个参数:pl、ph、pa

    2. out:指出相应的参数是用来传出去的(返回给调用者)

    3. in:调用者传递给存储过程的

    4. inout:同时具备上述两者作用

    */

    create procedure productprice2(

    out pl DECIMAL(8,2),

    OUT ph DECIMAL(8,2),

    OUT pa decimal(8,2)

    )

    begin

    select min(prod_price) into pl from products;

    select max(prod_price) into ph from products;

    select avg(prod_price) into pa from products;

    end;

    /*

    调用存储过程

    1. @用来声明变量

    2. @pricelow,@pricehigh,@priceavg在此处用来接收返回值

    3. 该调用并不显示任何数据,只返回变量。

    */

    call productprice2(@pricelow,@pricehigh,@priceavg);

    /*查看变量*/

    select @pricelow,@pricehigh,@priceavg;

    结果展示

    @pricelow

    @pricehigh

    @priceavg

    1

    2.50

    55.00

    16.13

    【案例二】(传入和传出)

    /*创建存储过程*/

    create procedure ordertotal(

    in onnumber int,

    out ototal decimal(9,2)

    )

    begin

    select sum(item_price*quantity) from orderitems where order_num=onnumber into ototal;

    end;

    /*调用存储过程*/

    call ordertotal(20009,@ototall);

    /*查看变量*/

    select @ototall;

    结果展示

    @ototall

    149.87

    【案例三】综合

    /*

    创建存储过程

    判断逻辑:针对有些顾客要收营业税,有些则不

    taxable为1则征收

    taxable为0则不征收

    */

    create procedure ordertotal(

    in onnumber int,

    in taxable boolean,

    out ototal decimal(8,2)

    )

    begin

    -- declare variable for total 声明一个总量(局部变量)

    declare total decimal(8,2);

    # declare tax percentage 交税的百分比

    declare taxpercent int default 6;

    -- 销售额=销售总量×单价

    select sum(item_price*quantity) from orderitems

    where order_num=onnumber into total;

    -- 判断要不要交税;注意if格式

    if taxable then

    select total+(total/100*taxpercent) into total;

    end if;

    select total into ototal;

    end;

    /*不交税的调用过程*/

    call ordertotal(20005,0,@total);

    /*交税的调用过程*/

    call ordertotal(20005,1,@total1);

    /*展示结果*/

    select @total,@total1;

    结果展示

    @total

    @total1

    1

    149.87

    158.86

    展开全文
  • **************************************MySql视图view的使用:创建、修改、删除************...视图创建后,可以像一张表一样使用,但只能用于数据查询,如:可以在一个查询中使用、可以在存储过程中、可以在另一个...

    **************************************

    MySql视图view的使用:创建、修改、删除

    ***************************************

    1. MySql创建视图

    创建视图与创建表语法类似,不同的是创建视图是从一条查询语句创建的。视图创建后,可以像一张表一样使用,但只能用于数据查询,如:可以在一个查询中使用、可以在存储过程中、可以在另一个视图中使用。MySql创建视图语法如下:

    CREATE VIEW视图名AS SELECT查询语句;

    2. MySql视图修改

    已经创建的视图,有时会需要修改其查询字段或查询条件,MySql视图修改语法如下:

    ALTER VIEW视图名AS SELECT查询语句;

    3. MySql视图删除

    MySql视图删除语法与删除表DROP TABLE类型,语法如下:

    DROP VIEW视图名;

    3. MySql视图使用

    创建好视图之后,可以直接通过视图名使用视图,语法如下:

    SELECT * FROM视图名

    ***************************************

    CREATE VIEW语句简介

    ***************************************

    要在MySQL中创建新视图,请使用CREATE VIEW语句。在MySQL中创建视图的语法如下:

    让我们更详细地查看了解以上语法。

    视图处理算法

    算法属性允许您控制MySQL在创建视图时使用的机制。MySQL提供三种算法:MERGE,TEMP TABLE和UNDEFINED。

    使用MERGE算法,MySQL首先将输入查询与定义视图的SELECT语句组合成单个查询。然后MySQL执行组合查询以返回结果集。如果SELECT语句包含聚合函数,如:MIN,MAX,SUM,COUNT,AVG或DISTINCT,GROUP BY,HAVING,LIMIT,UNION,UNION ALL,子查询等等则不允许MERGE算法。如果SELECT语句没有引用表,则也不允许MERGE算法。如果不允许MERGE算法,MySQL将算法更改为UNDEFINED。注意,输入查询和视图定义中的查询到一个查询的组合被称为视图决议。

    使用TEMP TABLE算法,MySQL首先根据定义视图的SELECT语句创建一个临时表,然后对该临时表执行输入查询。因为MySQL必须创建一个临时表来存储结果集,并将数据从基表移动到临时表,TEMP TABLE算法的效率低于MERGE算法。此外,使用TEMP TABLE算法的视图不可更新。

    UNDEFINED是创建视图时未指定显式算法的默认算法。UNDEFINED算法让MySQL做出选择使用MERGE或TEMP TABLE算法。MySQL优先选择MERGE算法再到TEMP TABLE算法,因为MERGE算法更有效率。

    视图名称

    在数据库中,视图和表共享相同命名空间,因此,视图和表不能具有相同的名称。此外,视图的名称必须遵循表命名规则。

    SELECT语句

    在SELECT语句中,可以从数据库中存在的任何表或视图查询数据。SELECT语句必须遵循几个规则:

    SELECT语句可以在WHERE子句中包含子查询,但不能在FROM子句中。

    SELECT语句不能引用任何变量,包括局部变量,用户变量和会话变量。

    SELECT语句不能引用预处理语句的参数。

    注意,SELECT语句不需要引用任何表。

    **********************************************

    Mysql中创建存储过程和函数的语法

    **********************************************

    mysql中存储过程和函数的语法非常接近所以就放在一起,主要区别就是函数必须有返回值,并且函数的参数只有IN类型而存储过程有IN、OUT、INOUT这三种类型。

    例子:

    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    #创建数据库

    DROP DATABASE IF EXISTS Dpro;

    CREATEDATABASE Dpro

    CHARACTER SET utf8

    ;

    USE Dpro;

    #创建部门表

    DROP TABLE IF EXISTS Employee;

    CREATE TABLE Employee

    (id INT NOT NULL PRIMARY KEY COMMENT '主键',

    name VARCHAR(20) NOT NULL COMMENT '人名',

    depid INT NOT NULL COMMENT '部门id'

    );

    #插入测试数据

    INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);

    #创建存储过程

    #改变默认的分隔符(;)为“$$”来作为存储过程结束的标识,随后再恢复默认值。

    DELIMITER $$

    DROP PROCEDURE IF EXISTS Pro_Employee;

    CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )

    READS SQL DATA

    SQL SECURITY INVOKER

    BEGIN

    SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

    # 多条sql语句

    END$$

    # $$代表存储过程的结束,再恢复默认的分隔符为(;)

    DELIMITER;

    #执行存储过程,变量名前面加上@,

    CALL Pro_Employee(101,@pcount);

    # 变量的查询也需要加上@

    SELECT @pcount;

    # 列出所有存储过程

    SHOW PROCEDURE STATUS;

    # 查看一个已存在的存储过程的信息

    SHOW CREATE PROCEDURE ;

    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    DELIMITER $$

    create procedure存储过程名字()

    (

    [in|out|inout]参数datatype

    )

    begin

    MySQL语句;

    end$$

    DELIMITER;

    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    注意⚠️

    MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”,则默认为“in”。习惯上,对于是“in”的参数,我们都不会显式指定。

    MySQL 存储过程名字后面的“()”是必须的,即使没有一个参数,也需要“()”

    MySQL 存储过程参数,不能在参数名称前加“@”,

    MySQL 客户端用户变量要加个“@”

    提供了结构控制语句,比如IF,WHILE,CASE等等,可以完成复杂的操作.另外,定义存储过程,以usp_前缀是区别系统存储过程和用户自定义存储过程的最佳实践.SET 对已声明的变量赋值或重新赋值,SELECT 显示变量;SELECT var into out_var 将变量值写入OUT参数。BEGIN

    DECLARE myvar INT;

    SET myvar = (SELECT id FROM users LIMIT 1);

    SELECT myvar into num;

    END $$

    MySQL 存储过程的参数不能指定默认值

    如果 MySQL 存储过程中包含多条 MySQL 语句,则需要 begin end 关键字。

    MySQL 存储过程中的注释。/* … */多行注释

    条件语句:declare c int; if a is null then  set c = 0;  end if;

    调用MySQL 存储过程时候,需要在过程名字后面加“()”,即使没有一个参数,也需要“()”

    因为MySQL 存储过程参数没有默认值,所以在调用MySQL 存储过程时候,不能省略参数。可以用null 来替代。

    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    语法解释:

    在创建存储过程的时候一般都会用

    DELIMITER $$…BEGIN…END$$DELIMITER ;放在开头和结束,目的就是避免mysql把存储过程内部的";"解释成结束符号,最后通过“DELIMITER ;”来告知存储过程结束。

    存储过程的参数有三种类型:

    IN: 输入参数. 在调用存储过程时指定,默认未指定类型时则是此类型.

    OUT: 输出参数. 在存储过程里可以被改变,并且可返回.

    INOUT: 输入输出参数. IN 和 OUT 结合

    characteristic部分:

    LANGUAGE SQL:用来说明语句部分是SQL语句,未来可能会支持其它类型的语句。

    [NOT]DETERMINISTIC:如果程序或线程总是对同样的输入参数产生同样的结果,则被认为它是“确定的”,否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC,默认的就是NOT DETERMINISTIC(非确定的)CONTAINS SQL:表示子程序不包含读或写数据的语句。

    NO SQL:表示子程序不包含SQL语句。

    READS SQL DATA:表示子程序包含读数据的语句,但不包含写数据的语句。

    MODIFIES SQL DATA:表示子程序包含写数据的语句。

    SQL SECURITY DEFINER:表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行。

    SQL SECURITY INVOKER:表示执行存储过程中的程序是由调用该存储过程的用户的权限来执行。(例如上面的存储过程我写的是由调用该存储过程的用户的权限来执行,当前存储过程是用来查询Employee表,如果我当前执行存储过程的用户没有查询Employee表的权限那么就会返回权限不足的错误,如果换成DEFINER如果存储过程是由ROOT用户创建那么任何一个用户登入调用存储过程都可以执行,因为执行存储过程的权限变成了root)

    COMMENT 'string':备注,和创建表的字段备注一样。

    注意:在编写存储过程和函数时建议明确指定上面characteristic部分的状态,特别是存在复制的环境中,如果创建函数不明确指定这些状态会报错,从一个非复制环境将带函数的数据库迁移到复制环境的机器上如果没有明确指定DETERMINISTIC,NO SQL,or READS SQL DATA该三个状态也会报错。

    **********************************************

    Mysql中事务处理块

    **********************************************

    展开全文
  • 1 使用视图(VIEW)1.1 视图视图是虚拟的表,只包含使用时动态检索数据的查询。视图的常见应用:重用SQL语句。简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道其基本查询细节。使用表的一部分而不是整个...

    1 使用视图(VIEW)

    1.1 视图

    视图是虚拟的表,只包含使用时动态检索数据的查询。

    视图的常见应用:

    重用SQL语句。

    简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道其基本查询细节。

    使用表的一部分而不是整个表。

    保护数据。可以授予用户访问表的特定部分的权限,而不是整个表的访问权限。

    更改数据格式和提示。视图可返回与底层表的表示和格式不同的数据。

    创建视图之后,可以用与表基本相同的方式使用它们。可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他视图或表,甚至添加和更新数据。

    创建和使用视图的一些常见规则和限制:

    与表一样,视图必须唯一命名,不能给视图取与别的视图或表相同的名字。

    对于可以创建的视图数目没有限制。

    创建视图,必须有足够的访问权限。

    视图可以嵌套,可以利用从其他视图中检索数据的查询来构造视图。

    视图不能索引,也不能有关联的触发器或默认值。

    有些DBMS把视图作为只读的查询,这表示可以从视图检索数据,但不能将数据写回底层表。

    1.2 创建视图CREATE VIEW

    视图是虚拟的表,只包含使用时动态检索数据的查询。

    视图用CREATE VIEW语句来创建。

    使用SHOW CREATE VIEW viewname;来查看创建视图的语句。

    用DROP删除视图,其语法为DROP VIEW viewname;。

    更新视图时,可先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在,则第2条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图。

    1.2.1 利用视图简化复杂的联结

    常见的视图应用是隐藏复杂的SQL。

    (1)先创建ProductCustomers视图。

    CREATE VIEW ProductCustomers AS

    SELECT cust_name, cust_contact, prod_id

    FROM Customers, Orders, OrderItems

    WHERE Customers.cust_id = Orders.cust_id

    AND OrderItems.order_num = Orders.order_num;

    (2)检索订购了产品RGAN01的顾客。

    SELECT cust_name, cust_contact

    FROM ProductCustomers

    WHERE prod_id = 'RGAN01';

    3c1856e32da8

    3c1856e32da8

    利用视图,可一次性编写基础的SQL,然后根据需要多次使用。

    1.2.2 用视图重新格式化检索出的数据

    将供应商Vendors表中的vend_name和vend_country合并输出显示。

    CREATE VIEW VendorLocations AS

    SELECT concat(RTRIM(vend_name), '(', RTRIM(vend_country), ')')

    AS vend_title

    FROM Vendors;

    SELECT *

    FROM VendorLocations;

    3c1856e32da8

    3c1856e32da8

    1.2.3 用视图过滤不想要的数据

    定义CustomerEmailList视图,过滤没有Email的顾客。

    CREATE VIEW CustomerEmailList AS

    SELECT cust_id, cust_name, cust_email

    FROM Customers

    WHERE cust_email IS NOT NULL;

    SELECT *

    FROM CustomerEmailList;

    3c1856e32da8

    3c1856e32da8

    1.2.4 使用视图与计算字段

    检索某个订单的产品,计算每种产品的总价格。

    CREATE VIEW OrderItemsExpanded AS

    SELECT order_num, prod_id, quantity, item_price,

    quantity * item_price AS expanded_price

    FROM OrderItems;

    SELECT *

    FROM OrderItemsExpanded

    WHERE order_num = 20008;

    3c1856e32da8

    3c1856e32da8

    1.2.5 更新视图

    迄今为止的所有视图都是和SELECT语句使用的。然而,视图的数据能否更新?答案视情况而定。

    通常,视图是可更新的,即,可以对它们使用INSERT、UPDATE和DELETE。更新一个视图将更新其基表,如果对视图增加或者删除行,实际上是对其基表增加或删除行。

    但是,并非所有视图都是可更新的。基本上可以说,如果MySQL不能正确地确定被更新的基数据,则不允许更新(包括插入和删除)。

    换句话说,如果视图定义中有以下操作,则不能进行视图的更新。

    分组(使用GROUP BY和HAVING)。

    联结。

    子查询。

    并。

    聚集函数(MIN()、COUNT()等)。

    DISTINCT。

    导出(计算)列。

    换句话说,本章的许多例子的视图都是不可更新的。这听上去好像是一个严重的限制,但实际上不是,因为视图主要用于数据检索。

    一般,应该将视图用于检索(SELECT语句)而不用于更新(INSERT、UPDATE和DELETE)。

    2 使用存储过程

    2.1 存储过程

    迄今为止,使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单,经常会有一个完整的操作需要多条语句才能完成。

    比如,考虑以下情况:

    为了处理订单,需要核对以保证库存中有相应的物品。

    如果库存有物品,这些物品需要预定以便不将它们再卖给别的人,并且要减少可用的物品数量以及反映正确的库存量。

    库存中没有的物品需要订购,这需要与供应商进行某种交互。

    关于哪些物品入库(并且可以立即发货)和哪些物品退订,需要通知相应的客户。

    执行上述的例子,需要针对许多表的多条SQL语句。此外,需要执行的具体语句及其次序也不是固定的,它们可能会(和将)根据哪些物品在库存中,哪些不在而变化。

    存储过程简单来说,就是为以后的使用而保存的一条或多条SQL语句的集合。可将其视为批文件,但它们的作用不限于批处理。

    2.2 为什么要使用存储过程

    使用存储过程的主要理由:

    通过把处理封装在容易使用的单元中,简化复杂的操作。

    由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程,则使用的代码都是相同的。(这一点的延伸就是防止错误,需要执行的步骤越多,出错的可能性就越大。防止错误保证了数据的一致性。)

    简化对变动的管理。如果表名、列名或业务逻辑有变化,只需要更改存储过程的代码,使用它的人员甚至不需要知道这些变化。(这一点的延伸就是安全性,通过存储过程限制对基础数据的访问减少了数据讹误的机会。)

    提高性能。因为使用存储过程比使用简单的SQL语句要快。

    存在一些只能用在单个请求中的MySQL元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码。

    使用存储过程的3个主要好处就是:简单、安全、高性能。

    存在的缺陷:

    一般来说,存储过程的编写比基本SQL语句复杂,编写存储过程需要更高的技能,更丰富的经验。

    可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限,允许用户使用存储过程,但不允许他们创建存储过程。

    2.3 使用存储过程

    2.3.1 执行存储过程

    MySQL称存储过程的执行为调用,因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。

    CALL productpricing(@pricelow,@pricehigh,@priceaverage);

    执行名为productpricing的存储过程,它计算并返回产品的最低、最高和平均价格。

    2.3.2 创建存储过程

    CREATE PROCEDURE productpricing()

    BEGIN

    SELECT AVG(prod_price) AS priceaverage

    FROM Products;

    END;

    3c1856e32da8

    由于默认的MySQL语句分隔符为;,如果命令行实用程序要解释存储过程自身内的;字符,则它们最终不会成为存储过程的成分,这会导致存储过程中的SQL出现句法错误,正如上图所示。

    解决办法就是使用更改命令行实用程序的语法分隔符,如下所示:

    DELIMITER // # 临时改成//分隔符

    CREATE PROCEDURE productpricing()

    BEGIN

    SELECT AVG(prod_price) AS priceaverage

    FROM Products;

    END//

    DELIMITER ; # 改成默认;分隔符,不然以后写的SQL语句都要以//作为结尾,才不会报错。

    3c1856e32da8

    调用存储过程:

    CALL productpricing();

    3c1856e32da8

    CALL productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数,所以存储过程名后需要有()符号,即使不传递参数也需要。

    可以看作调用一个定义好的函数,一定要带()符号。

    2.3.3 删除存储过程

    删除刚创建的存储过程:

    DROP PROCEDURE productpricing;

    删除存储过程的时候,只需给出存储过程名即可,不需要带上()符号。

    ** 建议:如果指定的存储过程不存在的话,则DROP PROCEDURE将会产生错误,为防止出现错误,可使用DROP PROCEDURE IF EXISTS。**

    DROP PROCEDURE IF EXISTS productpricing;

    3c1856e32da8

    2.3.4 使用带参数的存储过程

    productpricing只是一个简单的存储过程,它简单地显示SELECT语句的结果。一般,存储过程并不显示结果,而是把结果返回给你指定的变量。

    注意:创建下面的存储过程的时候,记得先删除以前创建的productpricing。

    DELIMITER //

    CREATE PROCEDURE productpricing(

    OUT pl DECIMAL(8,2),

    OUT ph DECIMAL(8,2),

    OUT pa DECIMAL(8,2)

    )

    BEGIN

    SELECT MIN(prod_price) INTO pl

    FROM Products;

    SELECT MAX(prod_price) INTO ph

    FROM Products;

    SELECT AVG(prod_price) INTO pa

    FROM Products;

    END //

    DELIMITER ;

    3c1856e32da8

    此存储过程接受3个参数:pl存储产品最低价格,ph存储产品最高价格,pa存储产品平均价格。

    MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)和INOUT(对存储过程传入和传出)类型的参数。

    存储过程的代码位于BEGIN和END语句内,它们是一系列SELECT语句,用来检索值,然后保存到相应的变量(通过指定INTO关键字)。

    为调用此存储过程,必须指定3个变量名:

    CALL productpricing(@pricelow,

    @pricehigh,

    @priceaverage);

    3c1856e32da8

    所有MySQL变量都必须以@开始。

    为了显示检索出的产品平均价格:

    SELECT @priceaverage;

    为获得3个值,可使用以下语句:

    SELECT @pricehigh, @pricelow, @priceaverage;

    3c1856e32da8

    3c1856e32da8

    创建另一个存储过程,这次使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计:

    DELIMITER //

    CREATE PROCEDURE ordertotal(

    IN onumber INT,

    OUT ototal DECIMAL(8,2)

    )

    BEGIN

    SELECT SUM(item_price * quantity)

    FROM OrderItems

    WHERE order_num = onumber

    INTO ototal;

    END //

    DELIMITER ;

    3c1856e32da8

    调用该存储过程时,必须给ordertotal传递两个参数:第一个参数为订单号,第二个参数为包含计算出来的合计的变量名。

    CALL ordertotal(20005, @total);

    SELECT @total;

    CALL ordertotal(20009, @total);

    SELECT @total;

    3c1856e32da8

    3c1856e32da8

    2.3.5 建立智能存储过程

    上述的所有存储过程基本上都是封装MySQL简单的SELECT语句,只有在存储过程内包含业务规则和智能处理时,它们的作用才能真正显现出来。

    考虑如下场景,你需要获得与以前一样的订单合并,但需要对合计增加营业税,不过只针对某些顾客。那么,你需要做下面几件事情:

    获得合计。

    把营业税有条件地添加到合计。

    返回合计(带税或者不带税)。

    存储过程的完整工作如下(记得先删除以前创建的ordertotal存储过程):

    DELIMITER //

    CREATE PROCEDURE ordertotal(

    IN onumber INT,

    IN taxable BOOLEAN,

    OUT ototal DECIMAL(8,2)

    )

    BEGIN

    # 声明局部变量

    DECLARE total DECIMAL(8,2);

    DECLARE taxrate INT DEFAULT 6;

    SELECT SUM(item_price * quantity)

    FROM OrderItems

    WHERE order_num = onumber

    INTO total;

    IF taxable THEN

    SELECT total + (total/100 * taxrate) INTO total;

    END IF;

    # 结果保存到ototal中

    SELECT total INTO ototal;

    END //

    DELIMITER ;

    3c1856e32da8

    DECLARE语句定义了两个局部变量,DECLARE要求指定变量名和数据类型,它支持可选的默认值。IF语句检查taxable是否为真,如果为真,则用另一个SELECT语句增加营业税到局部变量total。最后,将局部变量total保存到ototal。

    调用该存储过程:

    CALL ordertotal(20005, 0, @total);

    SELECT @total;

    CALL ordertotal(20005, 1, @total);

    SELECT @total;

    3c1856e32da8

    3c1856e32da8

    2.3.6 检查存储过程

    为显示用来创建一个存储过程的CREATE语句,使用SHOW CREATE PROCEDURE语句:

    SHOW CREATE PROCEDURE ordertotal;

    3c1856e32da8

    为了获得包括何时、由谁创建等详细信息的存储过程列表,使用SHOW PROCEDURE STATUS。

    SHOW PROCEDURE STATUS;

    3c1856e32da8

    可看出,SHOW PROCEDURE STATUS;显示了太多无关紧要的信息,为限制其输出,可以使用LIKE指定一个过滤模式。例如:

    SHOW PROCEDURE STATUS LIKE 'ordertotal';

    3c1856e32da8

    如果您发现文中有不清楚或者有问题的地方,请在下方评论区留言,我会根据您的评论,更新文中相关内容,谢谢!

    展开全文
  • 大二学数据库的时候,只是隐约听到老师提起过视图啊,存储过程啊,触发器啊什么的,但只是淡淡的记住了名字,后来自己做些小项目,小程序,也没有用上过,都只是简单的建表,关联表之类的,导致我对这些东西的理解只能停留在...
  • 原因: 后来查资料得知如果用户有dba角色,角色里包含的权限在存储过程里不会被继承;所以又单独对tb_bil_acct_566 ,seq_bil_acct_his_hisid_566.nextval等作了显式赋权,问题得以解决。 grant 授权的授权结果从...
  • 1.创建函数mysql>delimiter //mysql>create function 函数名(参数1 参数1类型,...) returns 返回类型>begin>return 返回值;...//创建存数过程建表create table user(id mediumint(...
  • 视图的操作存储过程存储函数触发器的操作(最末尾有测试数据库表和数据代码)一、视图的操作1.1视图可以用来做什么?简单的说视图就是一个查询,它在业务只需要最简单的查询语句就可以获取到业务需要的数据。...
  • 这里写目录标题一:视图1....数据库只存放视图的定义,而不存放视图包含的数据,这些数据仍存放在原来的基本表。 2.视图的作用 1.重用SQL语句。 2.简化复杂的SQL操作,不必知道它的查询细节。 3.使用户可以从多角
  • 存储过程中遇到ora-00942表或视图不存在SQL> create or replace procedure system.opt is2 begin3 insert into system.fga_log_view_bak(4 SESSION_ID ,5 TSMP ,6 DB_USER ,7 OS_USE...
  • MySql视图存储过程

    2021-02-02 16:51:02
    1、视图视图是虚拟的表,它只包含使用时动态检索数据的查询。安全1、使用视图的好处服务器一、重用SQL语句;函数二、简化复杂的SQL操做(能够方便的重用它而没必要知道它的基本查询细节);性能三、使用表的组成部分而...
  • 视图存储过程

    2021-04-22 09:51:54
    1.视图的创建与使用 为什么需要视图? 不同的人员关注不同的数据
  • p}' >> $object_file fi # 存储过程 if [[ $output_type == *"procedure"* ]] then echo "-- ------------------------------------------------------------" >> $object_file echo "-- procedure" >> $object_...
  • END $$ 12.2.5 定义条件和处理 条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。 1.条件的定义 DECLARE condition_name CONDITION FOR condition_value condition_value: SQLSTATE [VALUE] ...
  • 掌握创建索引、视图存储过程和触发器的方法,并了解作用一.索引二.视图三.存储过程四.触发器 一.索引 1.理论讲解 为什么要用索引? 数据库数据不断增多,结构复杂度越来越高,查询更加不方便,索引能加快查询速度...
  • 导读:该文章为视图存储过程、用户权限练习;如果有不对的地方欢迎指出与补充;该基础练习基于MySQL5.0以上;语句格式:1. 视图格式:create view view_name[列名,列名.....] as select 子查询 with check option...
  • MySQL视图、函数、存储过程视图视图的概念视图的操作视图的使用场景存储过程和函数存储过程 视图 视图的概念 1、什么是视图视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select...
  • 定义所有女生信息的视图“F_Student”(5分)CREATE VIEW F_StudentASSELECT *FROM StudentWHERE Sex='女'2.通过视图“F_Student”查询小于18岁的女生人数。(5分)SELECT COUNT(*)FROM F_StudentWHERE Sage<183.定义...
  • 视图储存过程、游标和触发器视图存储过程为什么要使用存储过程使用存储过程执行存储过程创建存储过程删除存储过程使用参数建立智能存储过程检查存储过程游标使用游标使用游标数据触发器 MySQL 5添加了对视图存储...
  • 关于Mysql视图存储过程1 视图view1 创建视图1 格式为: `CREATE VIEW 视图名称 AS SQL语句 `2 使用navicat查看视图3 视图数据集2 修改视图1 格式为:2 使用格式1修改视图3 使用navicat查看视图4 使用格式2修改...
  • 一、存储过程1、概念简介存储程序是被存储在服务器的组合SQL语句,经编译创建并保存在数据库,用户可通过存储过程的名字调用执行。存储过程核心思想就是数据库SQL语言层面的封装与重用性。使用存储过程可以较少...
  • 使用视图我们可以把查询过程中的临时表摘出来,用视图去实现,这样以后再想操作该临时表的数据时就无需重写复杂的sql了,直接去视图中查找即可,但视图有明显地效率问题,并且视图是存放在数据库中的,如果我们程序...
  • 使用视图我们可以把查询过程中的临时表摘出来,用视图去实现,这样以后再想操作该临时表的数据时就无需重写复杂的sql了,直接去视图中查找即可,但视图有明显地效率问题,并且视图是存放在数据库中的,如果我们程序...
  • 1.MySQL分页查询1.1 limit函数:SELECT * FROM emp LIMIT 3 –只查询三条数据,其他忽略1.2 select * from emp order by empno limit2,4 ;...2.视图2.1视图理解:经常用到的数据为了便于查询,将数据结果放到视图...
  • 三、存储过程 1.含义 2.好处 3.分类 4.创建存储过程 5.注意 6.调用存储过程 7.删除存储过程 8.查看存储过程结构 四、函数 1.含义 2.创建函数 3.调用函数 4.查看函数结构 5.删除函数 6.函数和存储
  • 存储过程视图

    2021-04-28 08:18:31
    是在大型数据库系统,一组为了完成特定功能的SQL 语句集,它存储在数据库,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库的一个重要对象。...
  • 结尾) 二、MySQL视图 1、概念 视图存储在数据库的查询的sql 语句 ,本质是查询语句的结果; 存入一个临时空间 是一个虚拟表,其内容由查询定义 ,视图在数据库没有原本的物理存储,只是相当于临时表; 对其中...
  • [命令行使用] 存储过程 存储过程是一组预先编译好的SQL语句的集合(批处理语句) 好处:(存储过程类似于java的方法) (1)提高了代码的重用性 (2)简化操作 (3)减少编译次数,并且减少和数据库连接的次数,提高效率 ...
  • 视图 1. 常见的数据库对象 对象 描述 ...表是存储数据的逻辑单元,以行和列的形式存在,列就是字段,行就是记录 ...系统表的数据通常由数据库系统维护,程序员通常不应该修改,只可查看 ...存储过程(PROCEDURE)
  • 我们都知道Oracle数据字典文档静态数据字典的视图共分为三类,动态Oracle数据字典的相关视图又分为哪几类呢?以及Oracle数据字典的相关视图又有什么样的作用呢?这些问题在此篇文章中都会找到答案。动态数据字典&...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 234,208
精华内容 93,683
关键字:

视图中包含存储过程