Apache Derby 简明教程
Apache Derby - Schemas
数据库架构是表示整个数据库的逻辑视图的骨架结构。它定义了数据如何组织以及它们之间的关系如何关联。它制定了将应用于数据的所有约束。
A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied to the data.
Creating a Schema
您可以使用 CREATE SCHEMA 语句在 Apache Derby 中创建模式。
You can create a schema in Apache Derby using the CREATE SCHEMA statement.
Syntax
以下是 CREATE SCHEMA 语句的语法。
Following is the syntax to the CREATE SCHEMA statement.
CREATE SCHEMA schema_name AUTHORIZATION id
Example
以下示例在 Derby 数据库中创建一个名为 my_schema 的模式。
Following example creates a schema named my_schema in Derby database.
ij> CREATE SCHEMA AUTHORIZATION my_schema;
0 rows inserted/updated/deleted
然后,您可以在此模式中创建表,如下所示。
Then, you can create a table in this schema as shown below.
ij> CREATE TABLE my_schema.Emp ( Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
Name VARCHAR(255),
Salary INT NOT NULL,
Location VARCHAR(255),
Phone_Number BIGINT
);
> > > > > 0 rows inserted/updated/deleted
您可以在此处使用 SHOW SCHEMAS 查询来验证模式列表,您可以在此处找到所创建模式的列表。
You can verify the list of schemas using the SHOW SCHEMAS query here you can find the list of schemas created.
ij> show schemas;
TABLE_SCHEM
------------------------------
APP
MY_SCHEMA
NULLID
SQLJ
SYS
SYSCAT
SYSCS_DIAG
SYSCS_UTIL
SYSFUN
SYSIBM
SYSPROC
SYSSTAT
12 rows selected
Dropping a Schema
您可以使用 DROP SCHEMA 语句删除现有模式。
You can drop an existing schema using the DROP SCHEMA statement.
Syntax
以下是 DROPS SCHEMA 语句的语法。
Following is the syntax of DROPS SCHEMA statement.
DROP SCHEMA my_schema RESTRICT;
Example
只有当架构中没有任何对象时,您才能删除该架构。要删除架构,请删除其中的所有表并删除架构。
You can delete a schema only if does not have any objects in it. To delete the schema, delete all the tables in it and delete the schema.
ij> DROP TABLE my_schema.Emp;
0 rows inserted/updated/deleted
以下示例删除上面创建的模式。
Following example drops the above created schema.
ij> DROP SCHEMA my_schema RESTRICT;
0 rows inserted/updated/deleted
JDBC Example
以下 JDBC 示例创建并删除名为 my_schema 的模式。
Following JDBC example creates and drops a schema named my_schema.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateSchemaExample {
public static void main(String args[]) throws Exception {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:sampleDB;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
stmt.execute("CREATE SCHEMA AUTHORIZATION my_schema");
//Executing the query
String query = "CREATE TABLE my_schema.Employees( "
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), "
+ "Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "PRIMARY KEY (Id))";
stmt.execute(query);
System.out.println("Table created in schema");
stmt.execute("DROP TABLE my_schema.Employees");
stmt.execute("DROP SCHEMA my_schema RESTRICT");
System.out.println("Schema dropped");
}
}