Shulou(Shulou.com)06/01 Report--

Database object

Object description

Table

A basic set of data stores consisting of rows and columns. A sequence of logically related data sets extracted by View from one or more tables.

Generating regular numerical index index

Improve query performance aliases given to objects by Synonym aliases

Table name and column name considerations:

Must start with a letter

Must be between 1 and 30 characters

Must contain only Amurz, amurz, 0-9, _, $, and #

Must not have the same name as other user-defined objects

Must not be a reserved word for Oracle

The maximum number of columns in the table is 1000

CREATE TABLE statement

Must have:

-CREATE TABLE permission

-Storage space

You must specify:

-Table name

-Field name, field data type and field size

Syntax:

CREATE TABLE [schema.] table_name (column datatype [DEFAULT expr] [,...])

Refer to other users' tables

Other user-defined tables are not in the current user's scenario

You should use the user name as a prefix to refer to other user-defined objects

DEFAULT option

During insertion, specify a default value for the column.

... Hire_date DATE DEFAULT SYSDATE,...

Strings, arithmetic expressions, or SQL functions are all legal

Column names and pseudo columns of other columns are illegal

The default value must satisfy the data type definition of the column

For example: CREATE TABLE hire_dates (id NUMBER (8), hire_date DATE DEFAULT SYSDATE)

Create a tabl

Create table dept

(deptno number (2)

Dname varchar2 (14)

Loc varchar2 (13)

Create_date date default sysdate)

Confirm that the creation is successful:

Desc dept

Name Null? Type

-

DEPTNO NUMBER (2)

DNAME VARCHAR2 (14)

LOC VARCHAR2 (13)

CREATE_DATE DATE

Data type

Data type description VARCHAR2 (size) variable length character data CHAR (size) fixed length character data NUMBER (p S) variable length numeric data DATE date and time values LONG variable length character data (up to 2 GB) CLOB character data (up to 4 GB) RAW and LONG RAW binary data BLOB binary data (up to 4 GB) BFILE stores binary data for external files (up to 4 GB) ROWIDbase-64 system encoded line unique address

Date data type

Data type

TIMESTAMP date INTERVAL YEAR TO MONTH with fractional seconds as time interval of year and month INTERVAL DAY TO SECOND is stored as time interval of day, hour, minute and second

Included constraint

Constraints are mandatory rules at the table level.

If the table has dependencies, constraints can prevent the table from being deleted.

The following are valid types of constraints:

-NOT NULL

-UNIQUE

-PRIMARY KEY

-FOREIGN KEY

-CHECK

Constraint criterion

If you do not specify the constraint name, Oracle server automatically specifies the constraint name in the format of SYS_Cn

When to create a constraint:

-at the same time as creating the table

-after the table is created

Constraints can be defined at the table or column level

You can view constraints through the data dictionary view

Define constraint

Syntax:

Create table [schema.] table

(column datatype [default expr]

[column_constraint]

...

[table_constraint] [,...])

Column-level constraint syntax

Column [CONSTRAINT constraint_name] constraint_type

Table-level constraint syntax:

Column,...

[CONSTRAINT constraint_name] constraint_type

(column,...)

Column level constraint exampl

Create table employees (

Employee_id number (6)

Constraint emp_emp_id_pk primary key

First_name varchar2 (20)

.)

Table-level constraint syntax:

Column,...

[constraint constraint_name] constraint_type

(column,...)

Example of column-level constraints:

Create table employees (

Employee_id number (6)

Constraint emp_emp_id_pk primary key

First_name varchar2 (20)

.)

Example of table-level constraints:

Create table employees (

Employee_id number (6)

First_name varchar2 (20)

...

Job_id varchar2 (10) not null

Constraint emp_emp_id_pk

Primary key (employee_id))

NOT NULL constraint

Make sure that the column does not allow null values:

UNIQUE constraint

Can be defined at the table or column level:

Create table employees (

Employee_id number (6)

Last_name varchar2 (25) not null

Email varchar2 (25)

Salary number (8 dint 2)

Commission_pct number (2Jing 2)

Hire_date date not null

...

Constraint emp_email_uk unique (email))

PRIMARY KEY constraint

FOREIGN KEY constraint

Can be defined at the table or column level:

Create table employees (

Employee_id number (6)

Last_name varchar2 (25) not null

Email varchar2 (25)

Salary number (8 dint 2)

Commission_pct number (2Jing 2)

Hire_date date not null

...

Department_id number (4)

Constraint emp_dept_fk foreign key (department_id)

References departments (department_id)

Constraint emp_email_uk unique (email))

FOREIGN KEY constraints: keyword

FOREIGN KEY: specify the columns in the child table at the table level

REFERENCES: columns identified in the parent table

ON DELETE CASCADE: when the columns in the parent table are deleted, the corresponding columns in the child table are also deleted

ON DELETE SET NULL: the corresponding column in the child table is left empty

CHECK constraint

Define the conditions that must be met for each row

The following expressions are not allowed:

-pseudo columns of CURRVAL, NEXTVAL, LEVEL, and ROWNUM appear

-use the SYSDATE, UID, USER, and USERENV functions

-values of other rows are involved in the query

CREATE TABLE: exampl

Create table employees

(employee_id number (6)

Constraint emp_employee_id primary key

, first_name varchar2 (20)

, last_name varchar2 (25)

Constraint emp_last_name_nn not null

, email varchar2 (25)

Constraint emp_email_nn not null

Constraint emp_email_uk unique

, phone_number varchar2 (20)

, hire_date date

Constraint emp_hire_date_nn not null

, job_id varchar2 (10)

Constraint emp_job_nn not null

, salary number (8pm 2)

Constraint emp_salary_ck check (salary > 0)

, commission_pct number (2par 2)

, manager_id number (6)

Constraint emp_manager_fk references

Employees (employee_id)

, department_id number (4)

Constraint emp_dept_fk references

Departments (department_id))

Violate the constraint

Update employees

Set department_id = 55

Where department_id = 110,

If the primary key row is referenced by another table foreign key, you cannot delete the primary key row

Create a table using a subquery

Use the CREATE TABLE statement and the AS subquery option to combine the creation of the table with the insertion of data.

CREATE TABLE table [(column, column...)] AS subquery

The specified column corresponds to the column in the subquery one by one.

Define columns by column names and default values.

Create table dept80

As

Select employee_id, last_name

Salary*12 annsal

Hire_date

From employees

Where department_id = 80

ALTER TABLE statement

Using the ALTER TABLE statement, you can:

Add a new column

Modify an existing column definition

New column definition default

Delete a column

Rename the column

Change the table to read-only

Read-Only table

Use ALTER TABLE to change the table to read-only mode:

Block changes to DDL and DML during table modification

Change the table back to read / write mode

Alter table employees read only

-- perform table maintenance and then

-- return table back to read/write mode

Alter table employees read write

Delete tabl

Move the table to the Recycle Bin

Use the PURGE clause to completely delete a table

The dependent objects on the table fail and the object permissions are deleted.

Drop table dept

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.