精华内容
下载资源
问答
  • 第七章 数据库及数据库对象

    万次阅读 热门讨论 2019-03-29 20:04:21
    第七章 数据库及数据库对象 SQL Server数据库分类 用户数据库 系统数据库,保存维护系统正常运行的信息 SQL Server系统数据库 master :记录实例的所有系统级信息(元数据,端点,连接服务器和系统配置),记录...

    第七章 数据库及数据库对象

    SQL Server数据库分类

    • 用户数据库
    • 系统数据库,保存维护系统正常运行的信息

    SQL Server系统数据库

    • master :记录实例的所有系统级信息(元数据,端点,连接服务器和系统配置),记录其它数据库的存在、位置,初始化信息
    • msdb:供代理服务调度报警和作业以及记录操作员时使用
    • model:创建数据库的模板,创建数据库时将自动复制model的内容到新建的数据库中
    • tempdb:临时数据库,用于保存临时对象或中间结果集
    • Resource:只读数据库,包含了所有系统对象,不可见

    SQL Server数据库的组成

    SQL Server数据库组成

    数据库存储空间的分配

    • 创建数据库时,model数据库自动被复制到新建与用户的数据库中,而且时复制到主要数据文件中
    • 数据存储分配的单位是数据页,一页是8KB(8060B为数据,132B为系统信息)的连续磁盘空间
    • 不允许表中的一行数据存储在不同页上(varchar(max),nvarchar(max),text,ntext,varbinary(max)和image类型除外)

    数据库文件组

    • 主文件组,包含主要数据文件和任何没有明确分配给其它文件组的数据文件,系统表的所有页均分配在主文件组中
    • 用户定义的文件组
      注:
    • 日志文件不在文件组内,日志文件与数据空间是分开管理的
    • 一个文件不能是多个文件组的成员
    • 文件组被填满后会逐渐增长

    数据库文件的属性

    • 文件名及其位置:数据文件和日志文件都有一个逻辑文件名和物理文件名。
    • 初始大小:指定每个数据文件和日志文件的初始大小。
    • 增长方式:指定是否自动增长。默认为自动增长。
    • 最大大小:文件增长的最大空间限制。默认情况无限制。

    例:

    /**
     * 创建指定一个数据文件和一个日志文件的数据库。创建一个名为RShDB的数据库,该数据库由一个数据文件和一个日志文件组成。 
     * 数据文件只有主要数据文件,其逻辑文件名为RShDB_Data,物理文件名为RShDB_Data.mdf,存放在D:\RShDB_Data文件夹下,
     * 初始大小为10MB,最大大小为30MB,自动增长时的递增量为5MB。日志文件的逻辑文件名为RShDB_log,物理文件名为
     * RShDB_log.ldf,也存放在D:\RShDB_Data文件夹下,初始大小为3MB,最大大小为12MB,自动增长时的递增量为2MB。
     */ 
     create database RShDB
     on
     (name=RShDB_Data,
     filename='D:\RShDB_Data\RShDB_Data.mdf',
     size=10,
     maxsize=30,
     filegrowth=5)
     log on
     (name=RShDB_log,
     filename='D:\RShDB_Data\RShDB_log.ldf',
     size=3,
     maxsize=12,
     filegrowth=2)
    
    /**
      * 创建具有文件组的数据库。创建一个名为Sales的数据库,该数据库除了主文件组PRIMARY外,还包括SalesGroup1
      * 和SalesGroup2两个文件组
      * 1. 主文件组包含Spri1_dat和Spri2_dat两个数据文件,这两个文件的FILEGROWTH均为当前文件大小的15%
      * 2. SalesGroup1文件组包含SGrp1Fi1_dat和SGrp1Fi2_dat两个文件,这两个文件的FILEGROWTH均为5MB
      * 3. SalesGroup2文件组包含SGrp2Fi1_dat和SGrp2Fil_dat两个文件,这两个文件的FILEGROWTH均为5MB
      * 为简单起见,假设这些文件均存放在D:\Sales文件夹下,所有数据文件的初始大小都是10MB,最大大小都是50MB。
      * 该数据库只包含一个日志文件Sales_log,该文件也存放在D:\Sales文件夹下,初始大小是5MB,最大大小是25MB,每次
      * 每次增加5MB。
     */
     create database Sales
     on primary
     (name=Spri1_dat,
      filename='D:\Sales\Spri1_dat.mdf',
      size=10MB,
      maxsize=50MB,
      filegrowth=15%),
     (name=Spri2_dat,
      filename='D:\Sales\Spri2_dat.ndf',
      size=10MB,
      maxsize=50MB,
      filegrowth=15%),
     filegroup SalesGroup1
     (name=SGrp1Fi1_dat,
      filename='D:\Sales\SGrp1Fi1_dat.ndf',
      size=10MB,
      maxsize=50MB,
      filegrowth=5MB),
     (name=SGrp1Fi2_dat,
      filename='D:\Sales\SGrp1Fi2_dat.ndf',
      size=10MB,
      maxsize=50MB,
      filegrowth=5MB),
     filegroup SalesGroup2
     (name=SGrp2Fi1_dat,
      filename='D:\Sales\SGrp2Fi1_dat',
      size=10MB,
      maxsize=50MB,
      filegrowth=5MB),
     (name=SGrp2Fi2_dat,
      filename='D:\Sales\SGrp2Fi2_dat',
      size=10MB,
      maxsize=50MB,
      filegrowth=5MB)
     log on
     (name=Sales_log,
      filename='D:\Sales\Sales_log.ldf',
      size=5MB,
      maxsize=25MB,
      filegrowth=5MB)   
    

    修改数据库

    问题:

    • 如果数据空间不够,则不能再对数据库插入数据
    • 如果日志空间不够,则不能再对数据库进行任何修改操作
    扩大数据空间
    /**
     * 为RShDB数据库添加一个新的数据文件,逻辑文件名为RShDB_Data2,物理存储位置为E:\Data文件夹下,物理文件名为
     * RShDB_Data2.ndf,初始大小为6MB,不自动增长。
     */
    alter database RShDB_Data2
    add file(
        name=RShDB_Data2,
        filename='E:\Data\RshDB_Data2.ndf',
        size=6MB,
        filegrowth=0) 
    
    /**
     * 扩大数据库中students_data1文件的初始大小,将其初始大小改为8MB.
     */
     alter database students
     modify file(
         name=students_data1,
         size=8MB)
    
    /**
     * 为RShDB数据库添加一个新的日志文件,逻辑文件名为RShDB_log1,物理存储位置为E:\Data文件夹下,物理文件名为
     * RShDB_log1.ldf,初始大小为4MB,每次增加1MB,最多增加到10MB.
     **/
     alter database RShDB
     add log file(
         name=RShDB_log1,
         filename='E:\Data\RShDB_log1.ldf',
         size=4MB,
         maxsize=10MB,
         filegrowth=1MB
     )
    
    收缩数据库大小
    /**
     * 收缩Students数据库,使该数据库中所有的文件都有20%的可用空间。
     */
    dbcc shrinkDatabase(Students,20) 
    
    /**
     * 将Students数据库中的students_data1文件收缩到4MB
     */
    dbcc shrinkFile(students_data1,4) 
    
    添加和删除数据库文件

    数据文件是按比例填充数据的。日志文件是填充到满。

    /**
     * 删除students数据库中的students_data1文件
     */
    alter database students
    remove file students_data1
    
    /**
     * 删除students数据库中的students_log1文件
     */
    alter database students
    remove file students_log1 
    

    分离数据库

    删除数据库,但不删除数据库的数据文件和日志文件。

    /**
     * 分离Students数据库,并跳过“更新统计信息”
     */
    exec sp_detach_db 'Students','true' 
    

    附加数据库

    与分离数据库对应。

    /**
     * 附加之前已分离的Students数据库
     */
    create database Students
    on(filename='F:\Data\students_data1') 
    
    /**
     * 假设已对Students数据库进行了分离操作,并将其中student_data2.ndf文件和student_data2.ldf文件均移动到了
     * E:\NewData文件夹下。移动数据库文件后,附加该数据库。
     */
    create database Students
    on  (filename='F:\Data\students_data1.mdf'),
        (filename='E:\Data\students_data2.ndf'),
        (filename='E:\NewData\students_log1.ldf') 
    

    架构

    架构是数据库下的一个逻辑命名空间,可以存放表、视图等数据库对象,它是一个数据库对象的容器。类比:数据库–操作系统,架构–文件夹,对象–文件。因此,通过将同名表放置在不同架构中,使一个数据库可以包含同名的表。

    定义架构

    /**
     * 为用户ZHANG定义一个架构,架构名为S_C。
     */
    create schema S_C authorization ZHANG
    
    /**
     * 定义一个用隐含名字的架构。
     */
    create schema authorization ZHANG
    
    /**
     * 在定义架构的同时定义表。 
     */
    create schema TEST authorization ZHANG
    create table T1
    (C1 int,
     C2 char(10),
     c3 smalldatetime,
     c4 numeric(4,1)) 
    

    删除架构

    /**
     * 删除架构S_C
     */
    drop schema S_C
    

    分区表

    分区表是将表中的数据水平划分成不同的子集,这些数据子集存储在数据库的一个或多个文件组中

    一般选择分区的条件:

    • 该表包含以多种不同方式使用的大量数据
    • 数据是分段的,比如数据以年份间隔

    分区表包括:

    • 分区函数,告诉数据库管理系统以什么方式对表进行分区
    • 分区方案,将分区函数生成的分区映射到文件组中

    分区函数

    CREATE PARTITION FUNCTION partition_function_name(input_parameter_type)
    AS RANGE [LEFT|RIGHT]
    FOR VALUES ([boundary_value[,…n]])

    • partition_function_name:分区函数名。分区函数名必须在数据库中唯一。
    • input_parameter_type:用于分区的列的数据类型。不可以是text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)和用户定义的数据类型。
    • boundary_value:分区的边界值。
    • LEFT|RIGHT:指定边界值归在左侧分区还是右侧分区。默认LEFT。
    /**
     * 在int列上创建左侧分区函数。下列分区函数将表分为四个分区。
     */
    create partition function myRangePF1(int)
    as range left for values(1,100,1000); 
    
     /**
      * ---------------------------------------------------------------------------
      * 分区 |    1    |           2          |            3            |    4    |
     * ---------------------------------------------------------------------------
     * 值   | col1<=1 | col1>1 AND col1<=100 | col1>100 AND col1<=1000 | col1>1000
     * ---------------------------------------------------------------------------
     */
    
    /**
     * 在int列上创建右侧分区函数。下列分区函数将表分为四个分区。
     */
    create partition function myRangePF2(int)
    as range right for values(1,100,1000); 
    
    /**
     * ---------------------------------------------------------------------------
     * 分区 |    1   |           2          |            3            |     4    |
     * ---------------------------------------------------------------------------
     * 值   | col1<1 | col1>=1 AND col1<100 | col1>=100 AND col1<1000 | col1>=1000
     * ---------------------------------------------------------------------------
     */
    

    分区方案

    CREATE PARTITION SCHEME partition_scheme_name
    AS PARTITION partition_function_name
    [ALL] TO(|file_group_name|[PRIMARY] | [,…n])

    • partition_scheme_name:分区方案名。分区方案名在数据库中必须唯一
    • partition_function_name:分区函数名。
    • ALL:指定所有分区都映射到file_group_name中提供的文件组,或映射到主文件组(如果指定了PRIMARY)
    • file_group_name|[PRIMARY] | [,…n]:指定分区对应的文件组名
     /**
      * 创建用于将每个分区映射到不同文件组的分区方案。下列代码首先创建一个分区函数,并将表分为四个分区。然后创建一个
      * 分区方案,在其中指定拥有着四个分区中每一个分区的文件组。此示例假定数据库中已经存在文件组。
      */
      create partition function myRangePF1(int)
      as range left for values(1,100,1000);
      go
      create partition scheme myRangePS1
      as partition myRangePF1
      to(test1fg,test2fg,test3fg,test4fg);
      
     /** 
      * ---------------------------------------------------------------------------
      * 文件组 | test1fg |        test2fg       |          test3fg        | test4fg |
      * ---------------------------------------------------------------------------
      * 分区   |    1    |           2          |            3            |    4    |
      * ---------------------------------------------------------------------------
      * 值     | col1<=1 | col1>1 AND col1<=100 | col1>100 AND col1<=1000 | col1>1000
      * ---------------------------------------------------------------------------
      */
    
    /**
      * 创建将所有分区映射到同一个文件组的分区方案。
      */
      create partition function myRangePF3(int)
      as range left for values(1,100,1000);
      go
      create partition scheme myRangePS3
      as partition myRangePF3
      all to (test1fg);
    
    /**
      * 首先创建一个分区函数,将表或索引分为四个分区。然后创建一个分区方案,最后创建使用此分区方案的表。
      */
      create partition function myRangePF1(int)
      as range left for values(1,100,100);
      go
      create partition scheme myRangePS1
      as partition myRangePF1
      to(test1fg,test2fg,test3fg,test4fg);
      go 
      create table PartitionTable(
          col1 int,
          col2 char(10)
      )on myRangePS1(col1);
    

    索引

    CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
    ON (column [ASC|DESC] [,…n])
    [INCLUDE(column_name[,…n])]
    [WHERE<filter_predicate>]
    [ON { partition_scheme_name(column_name)
    | filegroup_name
    | default
    ]
    [FILESTREAM_ON {filestream_filegroup_name | partition_scheme_name | “NULL”}]

    <object> ::=
    {
    [database_name.[schema_name].|schema_name.] table_or_view_name
    }

    • 默认索引为NONCLUSTERED
    • 默认排序方式为ASC
     /**
      * 在Table_Customer表的Cname列上创建非聚集索引。
      */
      create index Cname_ind on Table_Customer(Cname)
    
     /**
      * 在Table_Customer表的IdentityCard列上创建唯一性聚集索引。
      */
      create unique clustered index ID_ind
      on Table_Customer(IdentityCard)
    
     /**
      * 在Table_Customer表的IdentityCard列上创建一个非聚集索引,要求索引键值按Cname升序和CardID降序排序
      */ 
      create index COMP_ind on Table_Customer(Cname asc,CardID desc)
    
     /**
      * 创建分区索引。本示例在TransactionHistory表的ReferenceOrderID列上为现有分区方案TransactionPS1创建
      * 非聚集分区索引。
      */
      create nonclustered index IX_TransactionHistory_ReferenceOrderID
      on Transaction(ReferenceOrderID)
      on TransactionsPS1(TransactionDate);
    

    DROP INDEX {index_name ON <object>[,…n]}

     /**
      * 删除Table_Customer表中的Cname_ind索引
      */
      drop index Cname_id
    

    索引视图

    普通的视图不保存结果集,而是在使用时执行查询语句。而索引视图的结果集是存储在数据库中的。建有唯一聚集索引的视图称为索引视图,也称为物化视图。

    适合建立索引视图的条件:

    • 很少更新基本表
    • 基础数据以批处理的形式定期更新,但在更新之前主要作为只读数据进行处理,则可以考虑在更新前删除所有索引视图,然后再重建索引视图

    索引视图可以提高下列查询类型的性能:

    • 处理大量行的连接和聚合
    • 许多查询经常执行的连接和聚合操作

    索引视图通常不会提高下列查询类型的性能:

    • 具有大量写操作的OLTP系统
    • 具有大量更新操作的数据库
    • 不涉及聚合或连接的查询
    • GROUP BY列具有高基数度(查询后的数据量接近基本表中的总数据量)的数据聚合

    定义索引视图的要求:

    • 视图不能引用其它视图
    • 视图引用的所有基本表必须位于同一个数据库中
    • 必须使用 SCHEMABINDING选项创建视图
    • 视图中的表达式引用的所有函数必须是确定的
    • 对视图创建的第一个索引必须是唯一聚集索引,之后再创建其它的非聚集索引
    /**
      * 创建视图并为该视图创建一个唯一聚集索引,然后使用两个查询语句查询索引视图
      */
      create view  Sales.vOrders
      with schemabinding
      as
      select sum(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) as Revenue,OrderDate,ProductID,COUNT_BIG(*) as COUNT
      from Sales.SalesOrderDetail as od,Sales.SalesOrderHeader as o
      where od.SalesOrderID=o.SalesOrderID
      group by OrderDate,ProductID
      
      go
      
      create unique clustered index IDX_V1
      on Sales.vOrders(OrderDate,ProductID);
      
      go
      
      select sum(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) as Rev,OrderDate,ProductID
      from Sales.SalesOrderDetail as od 
      join Sales.SalesOrderHeader 
      on od.Sales.SalesOrderID=o.Sales.SalesOrderID
      and ProductID between 700 and 800
      and OrderDate>=convert(datetime,'05/01/2002',101)
      group by OrderDate,ProductID
      order by Rev desc
      
      go
      
      select OrderDate,sum(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) as Rev
      from Sales.SalesOrderDetail as od 
      join Sales.SalesOrderHeader 
      on od.Sales.SalesOrderID=o.Sales.SalesOrderID
      and datepart(month,OrderDate)=3
      and datepart(year,OrderDate)=2002
      group by OrderDate
      order by OrderDate asc
    
    展开全文
  • Oracle数据库对象简介

    万次阅读 2013-01-15 10:20:36
    对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径。    表和视图    Oracle中表是数据存储的基本结构。ORACLE8引入了分区表和对象表,ORACLE8i引入了临时表,使...

    Oracle数据库数据对象中最基本的是表和视图,其他还有约束、序列、函数、存储过程、包、触发器等。对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径。

     

      表和视图

     

      Oracle中表是数据存储的基本结构。ORACLE8引入了分区表和对象表,ORACLE8i引入了临时表,使表的功能更强大。视图是一个或多个表中数据的逻辑表达式。本文我们将讨论怎样创建和管理简单的表和视图。
      管理表

      表可以看作有行和列的电子数据表,表是关系数据库中一种拥有数据的结构。用CREATE TABLE语句建立表,在建立表的同时,必须定义表名,列,以及列的数据类型和大小。例如:

    CREATE TABLE products
      ( PROD_ID NUMBER(4),
       PROD_NAME VAECHAR2(20),
       STOCK_QTY NUMBER(5,3)
      );

      这样我们就建立了一个名为products的表, 关键词CREATE TABLE后紧跟的表名,然后定义了三列,同时规定了列的数据类型和大小。

      在创建表的同时你可以规定表的完整性约束,也可以规定列的完整性约束,在列上普通的约束是NOT NULL,关于约束的讨论我们在以后进行。

      在建立或更改表时,可以给表一个缺省值。缺省值是在增加行时,增加的数据行中某一项值为null时,oracle即认为该值为缺省值。

      下列数据字典视图提供表和表的列的信息:

       . DBA_TABLES
       . DBA_ALL_TABLES
       . USER_TABLES
       . USER_ALL_TABLES
       . ALL_TABLES
       . ALL_ALL_TABLES
       . DBA_TAB_COLUMNS
       . USER_TAB_COLUMNS
       . ALL_TAB_COLUMNS

      表的命名规则

      表名标识一个表,所以应尽可能在表名中描述表,oracle中表名或列名最长可以达30个字符串。表名应该以字母开始,可以在表名中包含数字、下划线、#、$等。

      从其它表中建立表

      可以使用查询从基于一个或多个表中建立表,表的列的数据类型和大小有查询结果决定。建立这种形式的表的查询可以选择其他表中所有的列或者只选择部分列。在CREATE TABLE语句中使用关键字AS,例如:

    SQL>CREATE TABLE emp AS SELECT * FROM employee

    TABLE CREATED

    SQL> CREATE TABLE Y AS SELECT * FROM X WHERE no=2

      需要注意的是如果查询涉及LONG数据类型,那么CREATE TABLE....AS SELECT....将不会工作。

      更改表定义

      在建立表后,有时候我们可能需要修改表,比如更改列的定义,更改缺省值,增加新列,删除列等等。ORACLE使用ALTER TABLE语句来更改表的定义

      1、增加列

      语法:

    ALTER TABLE [schema.] table_name ADD column_definition

      例:

    ALTER TABLE orders ADD order_date DATE;

    TABLE ALTER

      对于已经存在的数据行,新列的值将是NULL.

      2、更改列

      语法:

    ALTER TABLE [schema.] table_name MODIFY column_name new_attributes;

      例:

    ALTER TABLE orders MODITY (quantity number(10,3),status varchar2(15));

      这个例子中我们修改了表orders,将STATUS列的长度增加到15,将QUANTITY列减小到10,3;

      修改列的规则如下:

       . 可以增加字符串数据类型的列的长度,数字数据类型列的精度。

       . 减少列的长度时,该列应该不包含任何值,所有数据行都为NULL.

       . 改变数据类型时,该列的值必须是NULL.

       . 对于十进制数字,可以增加或减少但不能降低他的精度。

      3、删除数据列

      优化ORACLE数据库,唯一的方法是删除列,重新建立数据库。在ORACLE8i中有很多方法删除列,你可以删除未用数据列或者可以标示该列为未用数据列然后删除。

      删除数据列的语法是:

    ALTER TABLE [schema.] table_name DROP {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]

      要注意的是在删除列时关于该列的索引和完整性约束也同时删除。注意关键字CASCADE CONSTRAINS,如果删除的列是多列约束的一部分,那么这个约束条件相对于其他列也同时删除。

      如果用户担心在大型数据库中删除列要花太多时间,可以先将他们标记为未用数据列,标记未用数据列的语法如下:

    ALTER TABLE [schema.] table_name SET UNUSED {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]

      这个语句将一个或多个数据列标记为未用数据列,但并不删除数据列中的数据,也不释放占用的磁盘空间。但是,未用数据列在视图和数据字典中并不显示,并且该数据列的名称将被删除,新的数据列可以使用这个名称。基于该数据列的索引、约束,统计等都将被删除。

      删除未用数据列的语句是:

    ALTER TABLE [schema.] table_name DROP {UNUSED COLUM | COLUMN CONTINUE}
      删除表和更改表名

      删除表非常简单,但它是一个不可逆转的行为。

      语法:

    DROP TABLE [schema.] table_name [CASCADE CONSTRAINTS]
      
      删除表后,表上的索引、触发器、权限、完整性约束也同时删除。ORACLE不能删除视图,或其他程序单元,但oracle将标示他们无效。如果删除的表涉及引用主键或唯一关键字的完整性约束时,那么DROP TABLE语句就必须包含CASCADE CONSTRAINTS子串。

      更改表名

      RENAME命令用于给表和其他数据库对象改名。ORACLE系统自动将基于旧表的完整性约束、索引、权限转移到新表中。ORACLE同时使所有基于旧表的数据库对象,比如视图、程序、函数等,为不合法。

      语法:

    RENAME old_name TO new_name;

      例:

    SQL> RENAME orders TO purchase_orders;

    TABLE RENAMED
     

      截短表

      TRUNCATE命令与DROP命令相似, 但他不是删除整个数据表,所以索引、完整性约束、触发器、权限等都不会被删除。缺省情况下将释放部分表和视图空间,如果用户不希望释放表空间,TRUNCATE语句中要包含REUSE STORAGE子串。TRUNCATE命令语法如下:

    TRUNCATE {TABLE|CLUSTER} [schema.] name {DROP|REUSE STORAGE}

      例:

    SQL> TRUNCATE TABLE t1;

    TABLE truncate.

     

      视图(VIEW)

      视图是一个或多个表中的数据的简化描述,用户可以将视图看成一个存储查询(stored query)或一个虚拟表(virtual table).查询仅仅存储在oracle数据字典中,实际的数据没有存放在任何其它地方,所以建立视图不用消耗其他的空间。视图也可以隐藏复杂查询,比如多表查询,但用户只能看见视图。视图可以有与他所基于表的列名不同的列名。用户可以建立限制其他用户访问的视图。

      建立视图

      CREATE VIEW命令创建视图,定义视图的查询可以建立在一个或多个表,或其他视图上。查询不能有FOR UPDATE子串,在早期的ORACLE8i版本中不支持ORDER BY子串,现在的版本中CREATE VIEW可以拥有ORDER BY子串。

      例:

    SQL> CREATE VIEW TOP_EMP AS
    SELECT empno EMPLOYEE_ID,ename EMPLOYEE_NAME,salary
    FROM emp
    WHERE salary >2000

      用户可以在创建视图的同时更改列名,方法是在视图名后立即加上要命名的列名。重新定义视图需要包含OR REPLACE子串。

    SQL> CREATE VIEW TOP_EMP
    (EMPLOYEE_ID,EMPLOYEE_NAME,SALARY) AS
    SELECT empno ,ename ,salary
    FROM emp
    WHERE salary >2000

      如果在创建的视图包含错误在正常情况下,视图将不会被创建。但如果你需要创建一个带错误的视图必须在CREATE VIEW语句中带上FORCE选项。如:

    CREATE FORCE VIEW ORDER_STATUS AS
    SELECT * FROM PURCHASE_ORDERS
    WHERE STATUS=APPPOVE;

    SQL>/

    warning :View create with compilation errors

      这样将创建了一个名为ORDER_STATUS的视图,但这样的视图的状态是不合法的,如果以后状态发生变化则可以重新编译,其状态也变成合法的。

      从视图中获得数据

      从视图中获得数据与从表中获得数据基本一样,用户可以在连接和子查询中使用视图,也可以使用SQL函数,以及所有SELECT语句的字串。

     

      插入、更新、删除数据

      用户在一定的限制条件下可以通过视图更新、插入、删除数据。如果视图连接多个表,那么在一个时间里只能更新一个表。所有的能被更新的列可以在数据字典USER_UPDATETABLE_COLUMNS中查到。

      用户在CREATE VIEW中可以使用了WITH子串。WITH READ ONLY子串表示创建的视图是一个只读视图,不能进行更新、插入、删除操作。WITH CHECK OPTION表示可以进行插入和更新操作,但应该满足WHERE子串的条件。这个条件就是创建视图WHERE子句的条件,比如在上面的例子中用户创建了一个视图TOP_EMP,在这个视图中用户不能插入salary小于2000的数据行。

     

      删除视图

      删除视图使用DROP VIEW命令。同时将视图定义从数据字典中删除,基于视图的权限也同时被删除,其他涉及到该视图的函数、视图、程序等都将被视为非法。

      例:

    DROP VIEW TOP_EMP;

     
     
     完整性约束


      完整性约束用于增强数据的完整性,Oracle提供了5种完整性约束:

        Check
        NOT NULL
        Unique
        Primary
        Foreign key

      完整性约束是一种规则,不占用任何数据库空间。完整性约束存在数据字典中,在执行SQL或PL/SQL期间使用。用户可以指明约束是启用的还是禁用的,当约束启用时,他增强了数据的完整性,否则,则反之,但约束始终存在于数据字典中。

      禁用约束,使用ALTER语句

    ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

      或

    ALTER TABLE policies DISABLE CONSTRAINT chk_gender

      如果要重新启用约束:

    ALTER TABLE policies ENABLE CONSTRAINT chk_gender

      删除约束

    ALTER TABLE table_name DROP CONSTRAINT constraint_name

      或

    ALTER TABLE policies DROP CONSTRAINT chk_gender;

      Check 约束

      在数据列上Check 约束需要 一个特殊的布尔条件或者将数据列设置成TRUE,至少一个数据列的值是NULL,Check约束用于增强表中数据内容的简单的商业规则。用户使用Check约束保证数据规则的一致性。Check约束可以涉及该行同属Check约束的其他数据列但不能涉及其他行或其他表,或调用函数SYSDATE,UID,USER,USERENV。如果用户的商业规则需要这类的数据检查,那么可以使用触发器。Check约束不保护LOB数据类型的数据列和对象、嵌套表、VARRY、ref等。单一数据列可以有多个Check约束保护,一个Check约束可以保护多个数据列。

      创建表的Check约束使用CREATE TABLE语句,更改表的约束使用ALTER TABLE语句。

      语法:

    CONSTRAINT [constraint_name] CHECK (condition);

      Check约束可以被创建或增加为一个表约束,当Check约束保护多个数据列时,必须使用表约束语法。约束名是可选的并且如果这个名字不存在,那么oracle将产生一个以SYS_开始的唯一的名字。

      例:

    CREATE TABLE policies
    (policy_id NUMBER,
    holder_name VARCHAR2(40),
    gender VARCHAR2(1) constraint chk_gender CHECK (gender in (M,F),
    marital_status VARCHAR2(1),
    date_of_birth DATE,
    constraint chk_marital CHECK (marital_status in(S,M,D,W))
    );
     

      NOT NULL约束

      NOT NULL约束应用在单一的数据列上,并且他保护的数据列必须要有数据值。缺省状况下,ORACLE允许任何列都可以有NULL值。某些商业规则要求某数据列必须要有值,NOT NULL约束将确保该列的所有数据行都有值。

      例:

    CREATE TABLE policies
    (policy_id NUMBER,
    holder_name VARCHAR2(40) NOT NULL,
    gender VARCHAR2(1),
    marital_status VARCHAR2(1),
    date_of_birth DATE NOT NULL
    );

      对于NOT NULL的ALTER TABLE语句与其他约束稍微有点不同。

    ALTER TABLE policies MODIFY holder_name NOT NULL

      唯一性约束(Unique constraint)

      唯一性约束可以保护表中多个数据列,保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建,在唯一性约束创建后,可以使用ALTER TABLE语句修改。

      语法:

    column_name data_type CONSTRAINT constraint_name UNIQUE 

      如果唯一性约束保护多个数据列,那么唯一性约束要作为表约束增加。语法如下:

    CONSTRAINT constraint_name (column) UNIQUE USING INDEX TABLESPACE (tablespace_name) STORAGE (stored clause)

      唯一性约束由一个B-tree索引增强,所以可以在USING子串中为索引使用特殊特征,比如表空间或存储参数。CREATE TABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。

    CREATE TABLE insured_autos
    (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,
    vin VARCHAR2(10),
    coverage_begin DATE,
    coverage_term NUMBER,
    CONSTRAIN unique_auto UNIQUE (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)
    );

      用户可以禁用未以性约束,但他仍然存在,禁用唯一性约束使用ALTER TABLE 语句

    ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name;

      删除唯一性约束,使用ALTER TABLE....DROP CONSTRAIN语句

    ALTER TABLE insured_autos DROP CONSTRAIN unique_name;

      注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首先禁用或删除外部键(foreign key)。

      删除或禁用唯一性约束通常同时删除相关联的唯一索引,因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误,可以采取下面的步骤:

      1、在唯一性约束保护的数据列上创建非唯一性索引。

      2、添加唯一性约束

      主键(Primary Key)约束

      表有唯一的主键约束。表的主键可以保护一个或多个列,主键约束可与NOT NULL约束共同作用于每一数据列。NOT NULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样,主键由B-tree索引增强。

      创建主键约束使用CREATE TABLE语句与表一起创建,如果表已经创建了,可以使用ALTER TABLE语句。

    CREATE TABLE policies
    (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,
    holder_name VARCHAR2(40),
    gender VARCHAR2(1),
    marital_status VARCHAR2(1),
    date_of_birth DATE
    );

      与唯一性约束一样,如果主键约束保护多个数据列,那么必须作为一个表约束创建。

    CREATE TABLE insured_autos
    (policy_id NUMBER,
    vin VARCHAR2(40),
    coverage_begin DATE,
    coverage_term NUMBER,
    CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin)
    USING INDEX TABLESPACE index
    STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)
    );

      禁用或删除主键必须与ALTER TABLE 语句一起使用

    ALTER TABLE policies DROP PRIMARY KEY;

      或

    ALTER TABLE policies DISABLE PRIMARY KEY;

      外部键约束(Foreign key constraint)

      外部键约束保护一个或多个数据列,保证每个数据行的数据包含一个或多个null值,或者在保护的数据列上同时拥有主键约束或唯一性约束。引用(主键或唯一性约束)约束可以保护同一个表,也可以保护不同的表。与主键和唯一性约束不同外部键不会隐式建立一个B-tree索引。在处理外部键时,我们常常使用术语父表(parent table)和子表(child table),父表表示被引用主键或唯一性约束的表,子表表示引用主键和唯一性约束的表。

      创建外部键使用CREATE TABLE语句,如果表已经建立了,那么使用ALTER TABLE语句。

    CREATE TABLE insured_autos
    (policy_id NUMBER CONSTRAINT policy_fk
    REFERENCE policies(policy_id
    ON DELETE CASCADE,
    vin VARCHAR2(40),
    coverage_begin DATE,
    coverage_term NUMBER,
    make VARCHAR2(30),
    model VARCHAR(30),
    year NUMBER,
    CONSTRAIN auto_fk FROEIGN KEY (make,model,year)
    REFERENCES automobiles (make,model,year)
    ON DELETE SET NULL
    );
     

      ON DELETE子串告诉ORACLE如果父纪录(parent record)被删除后,子记录做什么。缺省情况下禁止在子记录还存在的情况下删除父纪录。

      外部键和NULL值

      在外部键约束保护的数据列中NULL值的处理可能产生不可预料的结果。ORACLE 使用ISO standar Match None规则增强外部键约束。这个规则规定如果任何外部键作用的数据列包含有一个NULL值,那么任何保留该键的数据列在父表中没有匹配值。

      比如,在父表AUTOMOBILES中,主键作用于数据列MAKE,MODEL,YEAR上,用户使用的表INSURED_AUTOS有一个外部约束指向AOTOMOBILES,注意在INSURES_AUTOS中有一数据行的MODEL列为NULL值,这一行数据已经通过约束检查,即使MAKE列也没有显示在父表AUTOMOBILES中,如下表:

      表1 AUTOMOBILES

    MAKE  MODEL  YEAR
    Ford  Taurus 2000
    Toyota Camry  1999

      表2 INSURED_AUTOS

    POLICY_ID MAKE  MODEL  YEAR
    576  Ford  Taurus  2000
    577  Toyota Camry  1999 
    578  Tucker  NULL  1949

      延迟约束检验(Deferred Constraint Checking)

      约束检验分两种情况,一种是在每一条语句结束后检验数据是否满足约束条件,这种检验称为立即约束检验(immediately checking),另一种是在事务处理完成之后对数据进行检验称之为延迟约束检验。在缺省情况下Oracle约束检验是立即检验(immediately checking),如果不满足约束将先是一条错误信息,但用户可以通过SET CONSTRAINT语句选择延迟约束检验。语法如下:

    SET CONSTRAINT constraint_name|ALL DEFEERRED|IMMEDIATE --;

      序列(Sequences)

      Oracle序列是一个连续的数字生成器。序列常用于人为的关键字,或给数据行排序否则数据行是无序的。像约束一样,序列只存在于数据字典中。序列号可以被设置为上升、下降,可以没有限制或重复使用直到一个限制值。创建序列使用SET SEQUENCE语句。

    CREATE SEQUENCE [schema] sequence KEYWORD

      KEYWORD包括下面的值:


    KEYWORD  描述
    START WITH  定义序列生成的第一个数字,缺省为1
    INCREMENT BY  定义序列号是上升还是下降,对于一个降序的序列INCREMENT BY为负值
    MINVALUE  定义序列可以生成的最小值,这是降序序列中的限制值。缺省情况下该值为NOMINVALUE,NOMINVALUE,对于升序为1,对于降序为-10E26.
    MAXVALUE  序列能生成的最大数字。这是升序序列中的限制值,缺省的MAXVALUE为NOMAXVALUE,NOMAXVALUE,对于升序为10E26,对于降序为-1。
    CYCLE  设置序列值在达到限制值以后可以重复
    NOCYCLE  设置序列值在达到限制值以后不能重复,这是缺省设置。当试图产生MAXVALUE+1的值时,将会产生一个异常
    CACHE  定义序列值占据的内存块的大小,缺省值为20
    NOCACHE  在每次序列号产生时强制数据字典更新,保证在序列值之间没有间隔当创建序列时,START WITH值必须等于或大于MINVALUE。

      删除序列使用DROP SEQUENCE语句

    DROP SEQUENCE sequence_name
     

      索引(INDEXES)

      索引是一种可以提高查询性能的数据结构,在这一部分我们将讨论索引如何提高查询性能的。ORACLE提供了以下几种索引:

       B-Tree、哈希(hash)、位图(bitmap)等索引类型
       基于原始表的索引
       基于函数的索引
       域(Domain)索引

      实际应用中主要是B-Tree索引和位图索引,所以我们将集中讨论这两种索引类型。

      B-Tree索引

      B-Tree索引是最普通的索引,缺省条件下建立的索引就是这种类型的索引。B-Tree索引可以是唯一或非唯一的,可以是单一的(基于一列)或连接的(多列)。B-Tree索引在检索高基数数据列(高基数数据列是指该列有很多不同的值)时提供了最好的性能。对于取出较小的数据B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。正如名字所暗示的那样,B-Tree索引是基于二元树的,由枝干块(branch block)和树叶块(leaf block)组成,枝干块包含了索引列(关键字)和另一索引的地址。树叶块包含了关键字和给表中每个匹配行的ROWID。

      位图索引

      位图索引主要用于决策支持系统或静态数据,不支持行级锁定。位图索引可以是简单的(单列)也可以是连接的(多列),但在实践中绝大多数是简单的。位图索引最好用于低到中群集(cardinality)列,在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值,对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外,位图以一种压缩格式存放,因此占用的磁盘空间比B-Tree索引要小得多。

      同义词(Synonyms)

      对另一个数据对象而言同义词是一个别名。public同义词是针对所有用户的,相对而言private同义词则只针对对象拥有者或被授予权限的账户。在本地数据库中同义词可以表示表、视图、序列、程序、函数或包等数据对象,也可以通过链接表示另一个数据库的对象。

      创建同义词语法如下:

    CREATE [PUBLIC] SYNONYM synonym_name FOR [schema.] object[@db_link];
      
      例:

    CREATE PUBLIC SYNONYM policies FOR poladm.policies@prod;

    CREATE SYNONYM plan_table FOR system.plan_table;
     

      过程和函数

      过程和函数都以编译后的形式存放在数据库中,函数可以没有参数也可以有多个参数并有一个返回值。过程有零个或多个参数,没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值,函数和过程的主要区别不在于返回值,而在于他们的调用方式。过程是作为一个独立执行语句调用的:

    pay_involume(invoice_nbr,30,due_date);

      函数以合法的表达式的方式调用:

    order_volumn:=open_orders(SYSDATE,30);

      创建过程的语法如下:

    CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
    [parameter_lister]
    {AS|IS}
    declaration_section
    BEGIN
    executable_section
    [EXCEPTION
    exception_section]
    END [procedure_name] 

      每个参数的语法如下:

    paramter_name mode datatype [(:=|DEFAULT) value]

      mode有三种形式:IN、OUT、INOUT。

      IN表示在调用过程的时候,实际参数的取值被传递给该过程,形式参数被认为是只读的,当过程结束时,控制会返回控制环境,实际参数的值不会改变。

      OUT在调用过程时实际参数的取值都将被忽略,在过程内部形式参数只能是被赋值,而不能从中读取数据,在过程结束后形式参数的内容将被赋予实际参数。

      INOUT这种模式是IN和OUT的组合;在过程内部实际参数的值会传递给形式参数,形势参数的值可读也可写,过程结束后,形势参数的值将赋予实际参数。

      创建函数的语法和过程的语法基本相同,唯一的区别在于函数有RETUREN子句

    CREATE [ OR REPLACE] FINCTION [schema.]function_name
    [parameter_list]
    RETURN returning_datatype
    {AS|IS}
    declaration_section
    BEGIN
    executable_section
    [EXCEPTION]
    exception_section
    END [procedure_name] 

      在执行部分函数必须有哟个或多个return语句。

      在创建函数中可以调用单行函数和组函数,例如:

    CREATE OR REPLACE FUNCTION my_sin(DegreesIn IN NUMBER)
    RETURN NUMBER
    IS
    pi NUMBER=ACOS(-1);
    RadiansPerDegree NUMBER;

    BEGIN
    RadiansPerDegree=pi/180;
    RETURN(SIN(DegreesIn*RadiansPerDegree));
    END


      

      包是一种将过程、函数和数据结构捆绑在一起的容器;包由两个部分组成:外部可视包规范,包括函数头,过程头,和外部可视数据结构;另一部分是包主体(package body),包主体包含了所有被捆绑的过程和函数的声明、执行、异常处理部分。

      打包的PL/SQL程序和没有打包的有很大的差异,包数据在用户的整个会话期间都一直存在,当用户获得包的执行授权时,就等于获得包规范中的所有程序和数据结构的权限。但不能只对包中的某一个函数或过程进行授权。包可以重载过程和函数,在包内可以用同一个名字声明多个程序,在运行时根据参数的数目和数据类型调用正确的程序。

      创建包必须首先创建包规范,创建包规范的语法如下:

    CREATE [OR REPLACE] PACKAGE package_name
    {AS|IS}
    public_variable_declarations |
    public_type_declarations |
    public_exception_declarations |
    public_cursor_declarations |
    function_declarations |
    procedure_specifications
    END [package_name]

      创建包主体使用CREATE PACKAGE BODY语句:

    CREATE [OR REPLACE] PACKAGE BODY package_name
    {AS|IS}
    private_variable_declarations |
    private_type_declarations |
    private_exception_declarations |
    private_cursor_declarations |
    function_declarations |
    procedure_specifications
    END [package_name]

      私有数据结构是那些在包主体内部,对被调用程序而言是不可见的。

     

      触发器(Triggers)

      触发器是一种自动执行响应数据库变化的程序。可以设置为在触发器事件之前或之后触发或执行。能够触发触发器事件的事件包括下面几种:

      DML事件
      DDL事件
      数据库事件

      DML事件触发器可以是语句或行级触发器。DML语句触发器在触发语句之前或之后触发DML行级触发器在语句影响的行变化之前或之后触发。用户可以给单一事件和类型定义多个触发器,但没有任何方法可以增强多触发器触发的命令。下表列出了用户可以利用的触发器事件:

    事件 触发器描述
    INSERT  当向表或视图插入一行时触发触发器
    UPDATE  更新表或视图中的某一行时触发触发器
    DELETE 从表或视图中删除某一行时触发触发器
    CREATE 当使用CREATE语句为数据库或项目增加一个对象时触发触发器
    ALTER 当使用ALTER语句为更改一个数据库或项目的对象时触发触发器
    DROP 当使用DROP语句删除一个数据库或项目的对象时触发触发器
    START 打开数据库时触发触发器,在事件后触发
    SHUTDOWN  关闭数据库时触发,事件前触发
    LOGON 当一个会话建立时触发,事件前触发
    LOGOFF 当关闭会话时触发,事件前触发
    SERVER 服务器错误发生时触发触发器,事件后触发

      创建触发器的语法如下:

    CREATE [OR REPLACE] TRIGGER trigger_name
    {before|after|instead of} event
    ON {table_or_view_name|DATABASE}
    [FOR EACH ROW[WHEN condition]]
    trigger_body

      只有DML触发器(INSERT、UPDATE、DELETE)语句可以使用INSTEAD OF触发器并且只有表的DML触发器可以是BEFORE或AFTER触发器。

      象约束一样触发器可以被设置为禁用或启用来关闭或打开他们的执行体(EXECUTE),将触发器设置为禁用或启用使用ALTER TRIGGER语句:

    ALTER TRIGGER trigger_name ENABLE;
    ALTER TRIGGER trigger_name DISABLE;

      要禁用或启用表的所有触发器,使用ALTER TABLE语句

    ALTER TRIGGER table_name DISABLE ALL TRIGGER;
    ALTER TRIGGER table_name ENABLE ALL TRIGGER;

      删除触发器使用DROP TRIGGER

    DROP TRIGGER trigger_name;

     

      数据字典

      Oracle数据字典包含了用户数据库的元数据。带下划线的表名称中带OBJ$、UET$、SOURCE$,这些表是在执行CREATE DATABASE语句期间由sql.bsq脚本创建的,一般情况下用户很少访问这些表。脚本catalog.sql(通常位于$oracle_home/rdbms/admin)在CREATE DATABASE语句之后立即运行,创建数据字典视图。

      数据字典视图大致可以分为三类:

      .前缀为USER_的数据字典视图,包含了用户拥有的对象的信息。

      .前缀为ALL_的数据字典视图,包含了用户当前可以访问的全部对象和权限的信息。

      .前缀为DBA_的数据字典视图,包含了数据库拥有的所有对象和权限的信息。

      在绝大多数数据字典视图中都有象DBA_TABLES,ALL_TABLES和USER_TABLES这样的视图家族。Oracle中有超过100个视图家族,所以要全面介绍这些视图家族是单调乏味的而且没有多大的意义。在下表中列出了最重要和最常用的视图家族,需要注意的是每个视图家族都有一个DBA_,一个ALL_一个USER_视图。

    视图家族(View Family) 描述
    COL_PRIVS 包含了表的列权限,包括授予者、被授予者和权限
    EXTENTS  数据范围信息,比如数据文件,数据段名(segment_name)和大小
    INDEXES 索引信息,比如类型、唯一性和被涉及的表
    IND_COLUMNS  索引列信息,比如索引上的列的排序方式
    OBJECTS  对象信息,比如状态和DDL time
    ROLE_PRIVS 角色权限,比如GRANT和ADMIN选项
    SEGMENTS  表和索引的数据段信息,比如tablespace和storage
    SEQUECNCES 序列信息,比如序列的cache、cycle和ast_number
    SOURCE  除触发器之外的所有内置过程、函数、包的源代码
    SYNONYMS  别名信息,比如引用的对象和数据库链接db_link
    SYS_PRIVS  系统权限,比如grantee、privilege、admin选项
    TAB_COLUMNS  表和视图的列信息,包括列的数据类型
    TAB_PRIVS  表权限,比如授予者、被授予者和权限
    TABLES 表信息,比如表空间(tablespace),存储参数(storage parms)和数据行的数量
    TRIGGERS  触发器信息,比如类型、事件、触发体(trigger body)
    USERS 用户信息,比如临时的和缺省的表空间
    VIEWS 视图信息,包括视图定义

      在Oracle中还有一些不常用的数据字典表,但这些表不是真正的字典家族,他们都是一些重要的单一的视图。

    VIEW NAME 描述
    USER_COL_PRIVS_MADE 用户授予他人的列权限
    USER_COL_PRIVS_RECD  用户获得的列权限
    USER_TAB_PRIVS_MADE  用户授予他人的表权限
    USER_TAB_PRIVS_RECD 用户获得的表权限

      其他的字典视图中主要的是V$视图,之所以这样叫是因为他们都是以V$或GV$开头的。V$视图是基于X$虚拟视图的。V$视图是SYS用户所拥有的,在缺省状况下,只有SYS用户和拥有DBA系统权限的用户可以看到所有的视图,没有DBA权限的用户可以看到USER_和ALL_视图,但不能看到DBA_视图。与DBA_,ALL,和USER_视图中面向数据库信息相反,这些视图可视的给出了面向实例的信息。

       在大型系统上化几周时间手工输入每一条语句

       手工输入带用户名变量的语句,然后再输入每一个用户名,这需要花好几个小时的时间

       写一条SQL语句,生成需要的ALTER USER语句,然后执行他,这只需要几分钟时间

      很明显我们将选择生成SQL的方法:

      例:

    SELECT ALTER USER||username||
    TEMPORARY TABLESPACE temp;
    FROM DBA_USERS
    WHERE username<>SYS
    AND temporary_tablespace<>TEMP;

      这个查询的结果将被脱机处理到一个文件中,然后在执行:

    ALTER USER SYSTEM TEMPORARY TABLESPACE temp;
    ALTER USER OUTLN TEMPORARY TABLESPACE temp;
    ALTER USER DBSNMP TEMPORARY TABLESPACE temp;
    ALTER USER SCOTT TEMPORARY TABLESPACE temp;
    ALTER USER DEMO TEMPORARY TABLESPACE temp;


    展开全文
  • 删除数据库对象 SQL Server 2008 若要删除在本教程中创建的所有对象,您只需删除数据库即可。但是,在本主题中,您将完成下列步骤执行与教程中每项操作...

    删除数据库对象

    SQL Server 2008
     

    若要删除在本教程中创建的所有对象,您只需删除数据库即可。但是,在本主题中,您将完成下列步骤执行与教程中每项操作相反的操作。

    删除权限和对象

    1. 在删除对象之前,请确保使用正确的数据库:

      USE TestData;
      GO
    2. 使用 REVOKE 语句删除 Mary 对存储过程的执行权限:

      REVOKE EXECUTE ON pr_Names FROM Mary;
      GO
    3. 使用 DROP 语句删除 MaryTestData 数据库的访问权限:

      DROP USER Mary;
      GO
    4. 使用 DROP 语句删除 Mary 对此 SQL Server 2005 实例的访问权限。

      DROP LOGIN [<computer_name>\Mary];
      GO
    5. 使用 DROP 语句删除存储过程 pr_Names

      DROP PROC pr_Names;
      GO
    6. 使用 DROP 语句删除视图 vw_Names

      DROP View vw_Names;
      GO
    7. 使用 DELETE 语句删除 Products 表中的所有行:

      DELETE FROM Products;
      GO
    8. 使用 DROP 语句删除 Products 表:

      DROP Table Products;
      GO
    9. 正使用 TestData 数据库时,无法删除该数据库;因此,请首先将上下文切换到其他数据库,再使用 DROP 语句删除 TestData 数据库:

      USE MASTER;
      GO
      DROP DATABASE TestData;
      GO

    “编写 Transact-SQL 语句”教程到此结束。请记住,本教程只是简要概述,它并未介绍所用语句的所有选项。设计和创建有效的数据库结构以及配置对数据的安全访问,需要比本教程中显示的数据库更复杂的数据库。

    转载于:https://my.oschina.net/u/2301207/blog/363206

    展开全文
  • Oracle中常见的数据库对象

    千次阅读 2017-06-18 13:38:54
    常见的数据库对象包括以下几种: table 表 基本的数据存储集合,由行和列组成。 view 视图 从表中抽出的逻辑上相关的数据集合 sequence 序列 提供有规律的数值。 index 索引 提高查询的效率 synonym 同义词 给...

    常见的数据库对象包括以下几种:
    table 表 基本的数据存储集合,由行和列组成。
    view 视图 从表中抽出的逻辑上相关的数据集合
    sequence 序列 提供有规律的数值。
    index 索引 提高查询的效率
    synonym 同义词 给对象起别名

    table 表前面已经做过介绍,这里主要介绍余下的几种。


    view 视图

    视图是一种虚表.
    视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
    向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
    视图向用户提供基表数据的另一种表现形式

    使用下面的语法格式创建视图

    CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
      [(alias[, alias]...)]
     AS subquery
    [WITH CHECK OPTION [CONSTRAINT constraint]]
    [WITH READ ONLY [CONSTRAINT constraint]];
    
    

    FORCE: 子查询不一定存在
    NOFORCE: 子查询存在(默认)
    WITH READ ONLY:只能做查询操作
    子查询可以是复杂的 SELECT 语句

    视图的优点:
    限制数据访问
    简化复杂查询
    提供数据的相互独立
    同样的数据,可以有不同的显示方式

    PS.视图不能提高性能, 不建议通过视图对表进行修改

    简单视图和复杂视图:
    简单视图的表的数量为一个,没有函数和分组,可以进行DML操作。
    复杂视图的表的数量为一个或多个,可以有函数和分组,有时候可以进行DML操作。

    创建视图

    CREATE VIEW 	empview
    AS SELECT  employee_id, last_name, salary
    FROM    employees
    WHERE   department_id = 10;
    

    使用CREATE OR REPLACE VIEW 子句修改视图

    CREATE OR REPLACE VIEW empview
    (id_number, name, sal, department_id)
    AS SELECT  employee_id, first_name || ' ' || last_name, salary, department_id
    FROM  employees
    WHERE department_id = 10;
    
    

    CREATE VIEW 子句中各列的别名应和子查询中各列相对应

    视图中使用DML的规定:
    可以在简单视图中执行 DML 操作
    当视图定义中包含以下元素之一时不能使用delete:
    组函数
    GROUP BY 子句
    DISTINCT 关键字
    ROWNUM 伪列

    当视图定义中包含以下元素之一时不能使用update :
    组函数
    GROUP BY子句
    DISTINCT 关键字
    ROWNUM 伪列
    列的定义为表达式

    当视图定义中包含以下元素之一时不能使用insert :
    组函数
    GROUP BY 子句
    DISTINCT 关键字
    ROWNUM 伪列
    列的定义为表达式
    表中非空的列在视图定义中未包括

    屏蔽 DML 操作:
    可以使用 WITH READ ONLY 选项屏蔽对视图的DML 操作
    任何 DML 操作都会返回一个Oracle server 错误

    CREATE OR REPLACE VIEW empview2
    (employee_number, employee_name, job_title)
    AS SELECT	employee_id, last_name, job_id
    FROM     employees
    WHERE    department_id = 10
    WITH READ ONLY;
    
    

    删除视图:
    删除视图只是删除视图的定义,并不会删除基表
    的数据

    drop view empview2;
    

    sequence 序列

    序列: 可供多个用户用来产生唯一数值的数据库对象
    自动提供唯一的数值
    共享对象
    主要用于提供主键值
    将序列值装入内存可以提高访问效率

    定义序列:

    CREATE SEQUENCE sequence
           [INCREMENT BY n]
           [START WITH n]
           [{MAXVALUE n | NOMAXVALUE}]
           [{MINVALUE n | NOMINVALUE}]
           [{CYCLE | NOCYCLE}]
           [{CACHE n | NOCACHE}];
    
    

    创建序列:
    创建序列 DEPT_DEPTID_SEQ为表 DEPARTMENTS 提供主键
    不使用 CYCLE 选项

    CREATE SEQUENCE dept_deptid_seq
                    INCREMENT BY 10
                    START WITH 120
                    MAXVALUE 9999
                    NOCACHE
                    NOCYCLE;
    
    

    查询序列
    查询数据字典视图 USER_SEQUENCES 获取序列定义信息

    SELECT	sequence_name, min_value, max_value, 
    	increment_by, last_number
    FROM	user_sequences;
    
    

    如果指定NOCACHE 选项,则列LAST_NUMBER 显示序列中下一个有效的值

    NEXTVAL 和 CURRVAL 伪列:
    NEXTVAL 返回序列中下一个有效的值,任何用户都可以引用
    CURRVAL 中存放序列的当前值
    NEXTVAL 应在 CURRVAL 之前指定 ,二者应同时有效

    使用序列
    将序列值装入内存可提高访问效率
    序列在下列情况下出现裂缝:
    回滚
    系统异常
    多个表同时使用同一序列
    如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值

    修改序列
    修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存

    ALTER SEQUENCE dept_deptid_seq
                   INCREMENT BY 20
                   MAXVALUE 999999
                   NOCACHE
                   NOCYCLE;
    
    

    修改序列的注意事项:
    必须是序列的拥有者或对序列有 ALTER 权限
    只有将来的序列值会被改变
    改变序列的初始值只能通过删除序列之后重建序列的方法实现

    删除序列
    使用DROP SEQUENCE 语句删除序列
    删除之后,序列不能再次被引用

    DROP SEQUENCE dept_deptid_seq;
    
    

    index 索引

    索引:
    一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
    索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
    索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引
    在删除一个表时, 所有基于该表的索引会自动被删除
    通过指针加速 Oracle 服务器的查询速度
    通过快速定位数据的方法,减少磁盘 I/O

    创建索引
    自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
    手动创建: 用户可以在其它列上创建非唯一的索引,以加速查询

    在一个或多个列上创建索引

    语法
    CREATE INDEX index
    ON table (column[, column]...);
    
    示例
    create index myindex
    on emp(deptno);
    
    

    以下情况可以创建索引:
    列中数据值分布范围很广
    列经常在 WHERE 子句或连接条件中出现
    表经常被访问而且数据量很大 ,访问的数据大概占数据总量的2%到4%

    下列情况不要创建索引:
    表很小
    列不经常作为连接条件或出现在WHERE子句中
    查询的数据大于2%到4%
    表经常更新

    查询索引
    可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息

    SELECT	ic.index_name, ic.column_name,
    	ic.column_position col_pos,ix.uniqueness
    FROM  	user_indexes ix, user_ind_columns ic
    WHERE	ic.index_name = ix.index_name
    AND	ic.table_name = 'EMP';
    
    

    删除索引
    使用DROP INDEX 命令删除索引

    DROP INDEX indexname;
    
    

    只有索引的拥有者或拥有DROP ANY INDEX权限的用户才可以删除索引


    synonym 同义词

    使用同义词访问相同的对象:
    方便访问其它用户的对象
    缩短对象名字的长度

    CREATE [PUBLIC] SYNONYM synonym
    FOR    object;
    

    创建和删除同义词

    CREATE SYNONYM  d_sum
    FOR  dept_sum;
    
    DROP SYNONYM d_sum;
    
    

    Coding Diary

    展开全文
  •  0推荐SQLServer如何改变数据库对象所有者,SQLServer ,改变数据库对象,所有者,利用SQL Server内置命令可以实现,在查询分析器里面使用下列命令:命令说明: sp_changeobjectowner更改当前数据库中对象的所有
  • Oracle数据库模式对象管理

    千次阅读 2019-10-11 22:29:08
    实验四 Oracle数据库模式对象管理 一、预习报告 一、 实验目的 1) 掌握表的创建与管理。 2) 掌握索引的创建与管理。 3) 掌握视图的创建与管理。 4) 掌握序列的创建与管理。 5) 了解簇、同义词、数据库链接等...
  • Sql小白入门(三)管理数据库对象

    千次阅读 2017-01-10 22:18:46
     数据库对象是数据库里定义的、用于存储或引用数据的对象,比如表、视图、促,序列、索引和异名。本章的内容以表为主,因为它是关系型数据库里最主要、最简单的数据存储形式。 2、什么是规则  规则是与数据库
  • MSSQLServer:改变数据库对象所有者 利用SQL Server内置命令可以实现,在查询分析器里面使用下列命令:EXEC sp_changeobjectowner dbo.CUSTOMER , admin 其中,dbo是表CUSTOMER的原所有者,admin是该表的新所有者...
  • 1、安装oracle数据库过程中系统表空间是哪一个:( B ) A.sys B.system C.user D.myspace 2、系统中有权利启动和关闭数据库的用户是:( C )。 A.hr B.user C.system D.scott 3、在创建索引的时候,哪一类索引...
  • 本教程演示如何在 T-SQL 编辑器中创建数据库对象的主要功能。 您可以在本教學課程中,了解如何使用 Azure 数据 Studio 來完成下列工作: 搜索数据库对象 编辑表数据 使用代码段快速编写 T-SQL 使用查看数据库对象...
  • Oracle-Oracle数据库模式对象管理

    千次阅读 2019-06-21 21:46:58
    Oracle数据库模式对象管理 下面通过一些简单的例子来熟悉一下在Oracle数据库中: 表的创建与管理。 索引的创建与管理。 视图的创建与管理。 序列的创建与应用。 例子来源于:《Oracle数据库基础教程(第2版)》...
  • oracle数据库对象---同义词(synonym)

    千次阅读 2017-01-10 18:02:44
    oracle数据库对象—同义词简析同义词是现有对象的一个别名,分为私有同义词和公用同义词,如一个表的表名过长或访问其他用户的表需要加上用户名作为前缀,可以用别名来解决。 part_1:查看scott用户是否具有创建同义...
  • 本篇只涉及表被其他数据库对象引用,不涉及外键,想寻找外键的,自己查看下面附有的链接。 首先有下列几种方式: 1、plsql工具 ; 点击工具,找到 查找数据库对象 最后进入到查找页面 ...
  • 使用 JDBC 创建数据库对象

    千次阅读 2004-08-06 18:22:00
    James W. CooperIBM T.J. Watson 研究中心1998 年 5 月 Download it now! PDF (209 KB) Free Acrobat Reader 摘自:Java 1.1 中面向对象编程的原理 摘要本
  • 实验四 数据库模式对象管理

    千次阅读 2016-12-31 20:26:55
    一、实验目的 1) 掌握表、索引、索引化表的概念及管理 2) 掌握分区的概念及分区管理 ...表空间、用户、角色、目录、概要文件及上下文问等数据库对象属于某个模式吗? 3) 如何进行模式的选择与切换
  • Oracle常见数据库对象—序列 一、序列:是oacle提供的用于产生一系列唯一数字的数据库对象。 a) 序列的特点  i. 自动提供唯一的数值  ii. 共享对象  iii. 主要用于提供主键值  iv. 将序列
  • 实验4 Oracle数据库模式对象管理

    千次阅读 多人点赞 2019-05-03 15:07:51
    一、实验目的: (1)掌握表的创建与管理。 (2) 掌握索引的创建与管理。 (3) 掌握视图的创建与管理。...2、按下列方式创建一个用户bs,并给该用户授权。 3、使用bs用户登录数据库,并进行下面...
  • 面向对象技术和数据库

    千次阅读 2007-07-17 22:35:00
    摘要本文简要回顾了面向对象技术和数据库技术结合的发展历程和方向,探讨面向对象技术和数据库技术的结合的三种方式的基本特征、优势、劣势,着重介绍面向对象数据库和以应用面向对象技术的后关系型数据库——Caché...
  • spring获取数据库连接对象

    千次阅读 2017-10-30 10:23:35
    1、导入数据库相关jar包:spring-jdbc-4.3.6.RELEASE.jar、spring-tx-4.3.6.RELEASE.jar 2、在src下创建资源文件db.properties 3、配置beans.xml文件 4、测试文件,获取数据连接对象 ...
  • 某一天,在调试程序时突然发现,在附加数据库后,想添加关系表,结果出来了下面的错误:此数据库没有有效所有者,因此无法安装数据库关系图支持对象。若要继续,请首先使用“数据库属性”对话框的“文件”页或ALTER ...
  • Oracle数据库数据对象分析(上)

    万次阅读 2004-08-06 18:54:00
    对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径。 表和视图 Oracle中表是数据存储的基本结构。ORACLE8引入了分区表和对象表,ORACLE8i引入了临时表,使表的功能更...
  • 利用dbms_metadata.get_ddl查看DDL语句(原创) 当我们想要查看某个表或者是表空间的DDL的时候,...GET_DDL函数返回创建对象的原数据的DDL语句,详细参数如下 -- object_type ---需要返回原数据的DDL语句的对
  • ADO.NET中Command对象检索和操作数据库

    千次阅读 2010-11-21 23:27:00
    Command对象属于.NET数据提供程序,不同的数据提供程序有不同的Command对象。 .NET 数据提供程序中的Command对象 SQL Server 数据提供程序——SqlCommand OLEDB 数据提供程序——OleDbCommand...
  • 通过执行下列操作检查指定数据库中所有对象的逻辑和物理完整性: 对数据库运行 DBCC CHECKALLOC。 对数据库中的每个表和视图运行 DBCC CHECKTABLE。 对数据库运行 DBCC CHECKCATALOG。 ...
  • 利用dbms_metadata.get_ddl查看DDL语句(原创) 当我们想要查看某个表或者是表空间的DDL...GET_DDL函数返回创建对象的原数据的DDL语句,详细参数如下 – object_type —需要返回原数据的DDL语句的对象类型 – name...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 99,577
精华内容 39,830
关键字:

下列属于数据库对象的是