DDL (data definition Language) The basis of the definition language

Data definition Language is one of the three main components of the SQL language (the other two are the data manipulation language DML (Mainpulation Language) and the Data Control language Control Language)).

1: Creating User Create

Create a user using the following syntax

Create user username identified by password [default tablespace defaults tablespace] [temporary tablespace temporary tablespace]. If you do not want to use the original tablespace of the system, you can create a temporary table space yourself as follows and the default table space.

To create a data table space:

Create tablespace tablespace_name//Creating a tablespace name (logical)

DataFile ' tablespace address: xxx.dbf '//corresponding data file (this is a file that can be viewed on disk after creation)

Size 50m//Initial size 50M

Autoextend on next 50m//expansion after initial storage capacity, expansion 50M at a time

MaxSize 200m//limit maximum space 200M

Extent management Local//store management method

To create a temporary tablespace:

Create temporary tablespace//To use temporary key when creating a temporary tablespace

Test1_temp tempfile ' table space address xxx.dbf '//note here the Tempfile keyword

Size 100M

Autoextend on Next 50M

MaxSize 300M

Extent management Local;

With the data table space and temporal tablespace created above, you can create a data table space and a temporary table space for the specified

Create user U1 identified by 1234

Default Tablespace test1_data

Temporary tablespace test1_temp;

2: Give the user the right to link and to create a table.

There are two ways of doing this:

2.1 Grant connect,resource to User name

Where connect is the right to link, resource represents the data table space and temporary table space that can be specified when the user is created

Revoke a given right: Revoke permissions from user name

2.2 You can also use grant to give it the right to link, and then apply quota to give it the right to use the specified tablespace

Grant connect to username//right to give user link

Alter user username quota 100M on table Space 1//give user 100M tablespace

Alter user username quota Unlimited on tablespace 1//allows users to freely use tablespace 1

3: Build Table structure

The basic syntax:

CREATE table < table name > (< define column >[{,< column definition >,< table constraint;}])

CREATE TABLE Student (

Sno Char (Ten) primary key,

Sname varchar2 () NOT NULL,

Sage smallint,

Ssex Char (5)

);

4: Copy base table

4.1: Duplicate table structure

CREATE TABLE T1 as Select*from Student where 1=2; this syntax duplicates only the table structure and does not copy the data inside the table.

4.2: Copy Table

CREATE TABLE T2 as Select*from Student; This method is used to replicate the data in the table;

5: Modify the properties of a table

ALTER TABLE student Add Native_place Var2char (30); Add native to table structure project

ALTER TABLE student Modify Native_place char (20); Modify an existing native Origin project

ALTER TABLE student Drop column native_place; Delete a column that increases or decreases

6: Delete Table

6.1 Deleting table structures and data in tables

Drop Table Name

6.2 Delete the table structure and the external code associated with the table

Drop Table name Cascade Constraints

The above two methods once removed will not be recoverable.

DDL (data definition Language) The basis of the definition language