Oracle Study Notes 8

The first six notes are Table query operations. As a basic operation of the database, query takes a lot of time. In this note, we will learn how to create and manage tables, create tables, Delete tables, modify tables, rename tables, and clear tables.

Common database objects: tables, views, sequences, indexes, and synonyms

The emp and dept tables used in the previous query are all databases of Oracle. This time, we will use SQL statements to create our own tables.

Before creating a table, you must first understand several data types in the database and naming rules of the table.

Data Type:

Varchar2 represents a string.

Number (n): indicates an integer and the number length is n.

Number (n, 2) indicates a decimal point. Two decimal places can be retained.

Date indicates the date type, which is stored in the standard date format.

Clob character data, up to 4 GB.

Blob indicates binary data. It can store up to 4 GB of data, such as movies and images.

Naming rules for table names and column names:

It must start with a letter;

It must be 1-30 characters long;

Must contain only A-Z, a-z, 0-9, _, $, and #;

It cannot be the same as other user-defined objects;

It must not be a reserved character of Oracle;

Table creation Syntax:

CREATE TABLETable_name (

Column name 1 Data Type [DEFAULT value],

Column name 2 Data Type [DEFAULT value],

Column name 3 data type [DEFAULT value],

...

)

Example: Create an emp1 table

SQL> create table emp1 (
2 id number (10 ),
3 name varchar2 (20 ),
4 salary number (12, 2 ),
5 hiredate date
6 );
 
Table created

The table emp1 is created successfully.

In addition to the preceding table creation method, the second method relies on existing tables.

Basic Syntax:

Create table table_nameAS (subquery );

In a subquery, if select * from emp, or select enamel, sal, hiredate, (specific column name) from emp; besides copying the structure of columns in the emp table, even the data in the table is copied together. If we only want to copy the structure of a table, instead of the table content, add the where condition, that is, select * from emp where 1 = 2, only the structure of the table is copied, and contents of the table are not copied.

ALTER TABLE)

You can use the alter table statement to add new columns, modify existing columns, define default values for new appended columns, delete columns, and rename

Column name and other operations.

 

Append a new column

Basic Syntax:

Alter table table_name

Add (column Name Data Type default value );

For example, for table emp1, add email with 20 characters and set the default value-No address

SQL> alter table emp1
2 add (email varchar2 (20 ));
 
Table altered
 
SQL> desc emp1;

Name Type Nullable Default Comments
-------------------------------------------
ENAME VARCHAR2 (10) Y
Sal number (7,2) Y
HIREDATE DATE Y
EMAIL VARCHAR2 (20) Y

Modify existing columns

Basic Syntax:

Alter table emp1

MODIFY (column name, data type );

For example, the length of the salary column in table emp1 is changed to 50.

Alter table emp1

MODIFY (salary number (50 ));

Delete column

Basic syntax

Alter table emp1

DROP cloumn column name;

Example: Delete the last added email Column

Alter table emp1

DROP cloumn email;

Rename a column

Basic Syntax:

Alter table table_name

RENAME column old_column_name TO new_column_name;

For example, rename the column salary in the emp1 table to sal.

Alter table emp1

RENAME cloumn salary TO sal;

In general development, the structure of a table is rarely modified, because the structure of the table in the database has been fully designed at the early stage of development.

 

Table Deletion

Basic Syntax:

Drop table table_name;

For example, delete the emp1 TABLE --- drop table emp1;

 

Rename a table:

Basic Syntax:

RENAME old_table_name TO new_table_name;

Clear table

Basic Syntax:

Truncate table table_name;

If you want to retain the table structure but clear the records in the table, you can use the preceding statement, which cannot be recovered.