Db2 简明教程

DB2 - Schemas

本章介绍和描述了模式的概念。

This chapter introduces and describes the concept of Schema.

Introduction

模式是在数据库中按照逻辑分类的已命名对象的集合。

A schema is a collection of named objects classified logically in the database.

在数据库中，您无法创建名称相同的多个数据库对象。为此，模式提供了一个组环境。您可以在数据库中创建多个模式，并且可以创建名称相同但模式组不同的多个数据库对象。

In a database, you cannot create multiple database objects with same name. To do so, the schema provides a group environment. You can create multiple schemas in a database and you can create multiple database objects with same name, with different schema groups.

schemas

模式可以包含表、函数、索引、表空间、过程、触发器等。例如，您可以为“employee”数据库创建两个名为“Professional”和“Personal”的不同模式。可以创建两个名称相同的不同表“Employee”。在此环境中，一个表包含专业信息，另一个表包含员工的个人信息。尽管有两个名称相同的表，但它们有两个不同的模式“Personal”和“Professional”。因此，用户可以在不遇到任何问题的情况下使用这两个表。当对表命名有约束时，此功能非常有用。

A schema can contain tables, functions, indices, tablespaces, procedures, triggers etc. For example, you create two different schemas named as “Professional” and “Personal” for an “employee” database. It is possible to make two different tables with the same name “Employee”. In this environment, one table has professional information and the other has personal information of employee. In spite of having two tables with the same name, they have two different schemas “Personal” and “Professional”. Hence, the user can work with both without encountering any problem. This feature is useful when there are constraints on the naming of tables.

让我们看看与模式相关的一些命令：

Let us see few commands related to Schema:

Getting currently active schema

Syntax:

Syntax:

db2 get schema

Example : [获取当前数据库模式]

Example: [To get current database schema]

db2 get schema

Setting another schema to current environment

Syntax:

Syntax:

db2 set schema=<schema_name>

Example : [将“schema1”组织到当前实例环境]

Example: [To arrange ‘schema1’ to current instance environment]

db2 set schema=schema1

Creating a new Schema

Syntax : [使用授权用户 ID 创建新的模式]

Syntax: [To create a new schema with authorized user id]

db2 create schema <schema_name> authroization <inst_user>

Example : [创建使用“db2inst2”授权的“schema1”模式]

Example: [To create “schema1” schema authorized with ‘db2inst2”]

db2 create schema schema1 authorization db2inst2

Exercise

让我们创建两个名称相同但模式不同的表。在此，您使用两个不同的模式创建雇员表，一个用于个人信息，另一个用于专业信息。

Let us create two different tables with same name but two different schemas. Here, you create employee table with two different schemas, one for personal and the other for professional information.

Step 1 : 创建两个架构。

Step 1: Create two schemas.

Schema 1 : [创建名为 professional 的架构]

Schema 1: [To create schema named professional]

db2 create schema professional authorization db2inst2

Schema 2 : [创建名为 personal 的架构]

Schema 2: [To create schema named personal]

db2 create schema personal authorization db2inst2

Step 2 : 创建两个表格，名称相同，用于存放员工详细信息

Step 2: Create two tables with the same name for Employee details

Table1 : professional.employee

Table1: professional.employee

db2 create table professional.employee(id number, name
varchar(20), profession varchar(20), join_date date,
salary number);

Table2 : personal.employee

Table2: personal.employee

db2 create table personal.employee(id number, name
varchar(20), d_birth date, phone bigint, address
varchar(200));

执行完这些步骤后，你将得到两个同名表格 'employee'，它们有两个不同的架构。

After executing these steps, you get two tables with same name ’employee’, with two different schemas.