金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > 游标和递归sql,表的操作

游标和递归sql,表的操作

发布时间:2019-11-03 06:16编辑:数据库浏览(166)

    DECLARE @UserID INT; --推广员帐号
    DECLARE @ProxyID INT; --代理帐号
    DECLARE @Score INT=1000; --分数
    SELECT
        @UserID = [SpreaderID]
    FROM
        [QPAccountsDB].[dbo].[AccountsInfo]
    WHERE
        UserID = 5055;
    SELECT --查出推广员的代理帐号
            @ProxyID = ProxyID
    FROM
            [QPAccountsDB].[dbo].[AccountsInfo]
        LEFT JOIN
            [QPProxyDB].[dbo].[BS_ProxyInfo]
                ON BS_ProxyInfo.account = AccountsInfo.Accounts
    WHERE
            UserID = @UserID;
    PRINT @ProxyID;
    CREATE TABLE #ProxyInfo
        (
            belongsAgent     INT,
            assignProportion TINYINT
        );
    WITH cte
    AS (   SELECT
               belongsAgent
           FROM
               [QPProxyDB].[dbo].[BS_ProxyInfo]
           WHERE
               ProxyID = @ProxyID
               AND belongsAgent <> -1
           UNION ALL
           SELECT
                   a.belongsAgent
           FROM
                   [QPProxyDB].[dbo].[BS_ProxyInfo] a
               JOIN
                   cte                              b
                       ON a.ProxyID = b.belongsAgent
           WHERE
                   a.belongsAgent <> -1)
    INSERT #ProxyInfo
        (
            belongsAgent,
            assignProportion
        )
           SELECT
               BS_ProxyInfo.ProxyID,
               assignProportion
           FROM
               cte LEFT JOIN [QPProxyDB].[dbo].[BS_ProxyInfo] ON BS_ProxyInfo.ProxyID = cte.belongsAgent
           ORDER BY
               BS_ProxyInfo.belongsAgent ASC;
    ---游标更新删除当前数据
    ---1.声明游标
    DECLARE cursor01 CURSOR SCROLL FOR
        SELECT
            *
        FROM
            #ProxyInfo
        ORDER BY
            belongsAgent ASC;
            DECLARE @AllTax INT 
            SET @AllTax =@Score
    --2.打开游标
    OPEN cursor01;
    --3.声明游标提取数据所要存放的变量
    DECLARE
        @belongsAgent     INT,
        @assignProportion TINYINT;
    --4.定位游标到哪一行
    FETCH FIRST FROM cursor01
    INTO
        @belongsAgent,
        @assignProportion; --into的变量数量必须与游标查询结果集的列数相同
    WHILE @@fetch_status = 0 --提取成功,进行下一条数据的提取操作 
        BEGIN
    
          SET   @AllTax=@assignProportion*@AllTax/100
    UPDATE [QPProxyDB].[dbo].[BS_ProxyInfo] SET allTax+=@AllTax WHERE ProxyID=@belongsAgent
            FETCH NEXT FROM cursor01
            INTO
                @belongsAgent,
                @assignProportion; --移动游标
        END;
    CLOSE cursor01;
    DEALLOCATE cursor01;
    DROP TABLE #ProxyInfo;
    

    1.事务

    本节掌握

    • 存储引擎介绍(了解)
    • 表的增删改查

     

    在sql  servce 中 事务是一个工作单元 可能包含查询和修改数据以及修改数据定义等多个活动 也可以显示或隐式定义事务边界

    一、存储引擎(了解)

    前几节我们知道mysql中建立的库===》文件夹,库中的表====》文件

    现实生活中我们用来存储数据的文件有不同的类型,每种文件类型对应各自不同的处理机制:比如处理文本用txt类型,处理表格用excel,处理图片用png等

    数据库中的表也应该有不同的类型,表的类型不同,会对应mysql不同的存取机制,表类型又称为存储引擎。

    ps: 存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)

    在Oracle 和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。而MySql
    数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据
    自己的需要编写自己的存储引擎

    图片 1

    SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每 个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设 计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这 么强的要求 ;有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据 的查询。

     

    显示定义事务 BEGIN TRAN 开始  如果要提交事务 使用 COMMIT TRAN   撤销事务(回滚) ROLLBACK TRAN  如下示例

    二、mysql支持的存储引擎

    mysql> show enginesG;# 查看所有支持的引擎
    mysql> show variables like 'storage_engine%'; # 查看正在使用的存储引擎
    

     

    1、InnoDB 存储引擎

    支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其

    特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。

    InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。

    InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。

    对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。

    InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。

    2、MyISAM 存储引擎

    不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。

    3、NDB 存储引擎

    年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。

    4、Memory 存储引擎

    正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。

    5、Infobright 存储引擎

    第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。

    6、NTSE 存储引擎

    网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。

    7、BLACKHOLE

    黑洞存储引擎,可以应用于主备复制中的分发主库。

    MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。

     

    指定表类型/存储引擎

    create table t1(id int)engine=innodb;# 默认不写就是innodb
    

     

    小练习:

    创建四张表,分别使用innodb,myisam,memory,blackhole存储引擎,进行插入数据测试 

    create table t1(id int)engine=innodb;
    create table t2(id int)engine=myisam;
    create table t3(id int)engine=memory;
    create table t4(id int)engine=blackhole;
    

    查看data文件下db1数据库中的文件:

    图片 2

    #.frm是存储数据表的框架结构
    
    # .ibd是mysql数据文件 
    
    #.MYD是MyISAM表的数据文件的扩展名
    
    #.MYI是MyISAM表的索引的扩展名
    
    #发现后两种存储引擎只有表结构,无数据
    
    #memory,在重启mysql或者重启机器后,表内数据清空
    #blackhole,往表内插入任何数据,都相当于丢入黑洞,表内永远不存记录
    

     

    BEGIN TRAN;
    INSERT INTO Sales.Orders
          (custid, empid, orderdate, requireddate, shippeddate, 
           shipperid, freight, shipname, shipaddress, shipcity,
           shippostalcode, shipcountry)
        VALUES
          (85, 5, '20090212', '20090301', '20090216',
           3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
           N'10345', N'France');
    commit tran
    

    三、表介绍

    表相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段

    图片 3

    id,name,sex,age,birth称为字段,其余的,一行内容称为一条记录

     

    一个简单的显示事务    也可以是隐式事务

    四、创建表

    语法:

    create table 表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
    );
    
    #注意:
    1. 在同一张表中,字段名是不能相同
    2. 宽度和约束条件可选
    3. 字段名和类型是必须的
    

    1.创建数据库

    create database db2 charset utf8;
    

    2.使用数据库

    use db2;
    

    3.创建a1表

    create table a1(
      id int,
      name varchar(50),
      age int(3)
    );
    

    4.插入表的记录

    insert into a1 values
    (1,'mjj',18),
    (2,'wusir',28);
    

    ps:以;作为mysql的结束语

    5.查询表的数据和结构

    (1)查询a1表中的存储数据

    mysql> select * from a1;
    +------+-------+------+
    | id | name | age |
    +------+-------+------+
    | 1 | mjj   | 18  |
    | 2 | wusir | 28  |
    +------+-------+------+
    2 rows in set (0.02 sec)

    mysql>

     

    (2)查看a1表的结构

    mysql> desc a1;
    +-------+-------------+------+-----+---------+-------+
    | Field     | Type           | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id        | int(11)        | YES  |      | NULL    |       |
    | name      | varchar(50)    | YES  |      | NULL    |       |
    | age       | int(3)         | YES  |      | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.16 sec)
    

    (3)查看表的详细结构

    mysql> show create table a1G;
    *************************** 1. row ***************************
           Table: a1
    Create Table: CREATE TABLE `a1` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(50) DEFAULT NULL,
      `age` int(3) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    

    6.复制表

    (1)新创建一个数据库db3

    mysql> create database db3 charset utf8;
    Query OK, 1 row affected (0.00 sec)
    

    (2)使用db3

    mysql> use db3;
    Database changed
    
    #这是上个创建的db2数据库中的a1表
    mysql> select * from db2.a1;
    +------+-------+------+
    | id   | name  | age  |
    +------+-------+------+
    |    1 | mjj   |   18 |
    |    2 | wusir |   28 |
    +------+-------+------+
    

    (3)复制db2.a1的表结构和记录

    # 这就是复制表的操作(既复制了表结构,又复制了记录)
    mysql> create table b1 select * from db2.a1;
    Query OK, 2 rows affected (0.03 sec)
    

    (4)查看db3.b1中的数据和表结构

    #再去查看db3文件夹下的b1表发现 跟db2文件下的a1表数据一样
    mysql> select * from db3.b1;
    +------+-------+------+
    | id   | name  | age  |
    +------+-------+------+
    |    1 | mjj   |   18 |
    |    2 | wusir |   28 |
    +------+-------+------+
    2 rows in set (0.00 sec)
    

     

    ps1:如果只要表结构,不要记录

    #在db2数据库下新创建一个b2表,给一个where条件,条件要求不成立,条件为false,只拷贝表结构
    mysql> create table b2 select * from db2.a1 where 1>5;
    Query OK, 0 rows affected (0.05 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    查看表结构:

    # 查看表结构
    mysql> desc b2;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(50) | YES  |     | NULL    |       |
    | age   | int(3)      | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.02 sec)
    
    #查看表结构中的数据,发现是空数据
    mysql> select * from b2;
    Empty set (0.00 sec)
    

     

    ps2:还有一种做法,使用like(只拷贝表结构,不拷贝记录)

    mysql> create table b3 like db2.a1;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> desc b3;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(50) | YES  |     | NULL    |       |
    | age   | int(3)      | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.02 sec)
    
    mysql> select * from db3.b3;
    Empty set (0.00 sec)
    

     

    7.删除表:

    drop table 表名;
    

     

    GO
     INSERT INTO Sales.Orders
          (custid, empid, orderdate, requireddate, shippeddate, 
           shipperid, freight, shipname, shipaddress, shipcity,
           shippostalcode, shipcountry)
        VALUES
          (85, 5, '20090212', '20090301', '20090216',
           3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
           N'10345', N'France');
    GO
    

    通过GO 在当前批执行完成时自动 提交事务   当然默认情况下SQL Server 将每个单独的语句作为一个事务 每个语句结束后SQL Server 会自动提交事务 也可通过设置会话来改变默认设置 

    SET IMPLICIT_TRANSACTIONS ON
    

    设置为ON 后不需要指定BEGIN TRAN 语句开始事务 但必须以COMMIT TRAN或 TOLLBACK TRAN 标记结束

    事务有4个属性-原子性,一致性,隔离性,持续性 首字母缩写为ACID

    原子性(Atomicity)**: 事务是一个原子工作单元,事务中的所有修改要么提交,要么撤销。在事务提交指令记录到事务日志之前 如果系统出现了故障,重新启动时,SQL Server 会撤销所做的修改。  如果事务中出现错误 默认会自动回滚   也可以通过 @@TRANCOUNT 检测事务事务完成 或者说是 当前环境是否在事务中 如果在就返回 1 没有就是0 

    SELECT    @@TRANCOUNT
    

    一致性(Consistency):指数据状态, 在隔离级别中  每个级别都要是 一致性级别 只有事务保持一致性级别才能访问。在约束中 也指 主外键 书屋会转换数据库的一致性状态到另一个一致性状态 保持一致性

    隔离性(Isolation):隔离是一种控制访问数据的机制,确保事务所范围数据是在其所期望的一致性级别中的数据。在SQL Server 中支持两种不同的模式来处理隔离:基于锁的传统模式和行版本控制(新模式) 但是默认的是 锁模式  而且当前加锁也是共享锁 ,如果数据状态不一致 读取数据就会被阻止 直到状态一致 。而改成行版本控制模式 读取就不需要等待 也不会加共享锁 在不需要及时显示的数据时 这种模式是提高并发的处理方式。 具体实现要看使用的隔离级别。

    持续性(Durability):数据修改在写入到数据库磁盘之前,总是先写入数据库的事务日志磁盘。提交后,指令记录在事务日志磁盘上,在尚未修改磁盘数据之前,事务是持续的,回滚也只是删除事务日志的所有修改记录。

    本文由金沙国际官网发布于数据库,转载请注明出处:游标和递归sql,表的操作

    关键词:

上一篇:没有了

下一篇:Mysql数据库的增删改查,隐式事务