Oracle table management

1. Table Name and column name naming rules

(1). It must start with a letter;

(2). The length cannot exceed 30 characters;

(3) Oracle reserved words cannot be used;

(4). can only use the following characters A-Z, A-Z, 0-9, $, # and so on.

 

2. Data Types in Oracle

(1). character class

CHAR: Specifies the length. It can contain a maximum of 2000 characters and has a high query speed (exact match is used for query ).

Varchar: variable length. It can contain a maximum of 4000 characters and slow query speed (one-bit/one-bit matching ).

Clob: large object with a maximum capacity of 4 GB.

(2). Number

Number: the range is from the power of-10 to the power of 10. It can be an integer or a decimal number.

For example, number (999.99) indicates that there are 5 Valid digits in total, with two decimal places ranging from-999.99.

Number (5), which is a five-digit integer ranging from-99999 to 99999.

(3). date type

Date: includes year, month, day, hour, minute, and second. The default format in Oracle is 24-6-2010.

Timestamp: This is an oracle extension of the time type, which can be precise to milliseconds.

(4). Images

BLOB: binary data, which can store pictures and sounds. It supports a maximum of 4 GB. Generally, images and sounds are not stored in the database in real projects, generally, the path for storing images or sounds is put into the database if the security requirements are high.

 

3. Create a table

Use the craete TABLE statement to create a table.

Example: Create Table student (

ID number (4 ),

Name varchar2 (30 ),

Sex char (2 ),

Birthday date,

Sal number (7,2)

);

 

4. modify a table

(1) Add a field

Example: alter table student add (classid number (2 ));

(2) modify the length of a field

Example: alter table student modify (name varchar2 (50 ));

(3). Modify the field type or name (data is not allowed)

Example: alter table student modify (name char (30 ));

(4). delete a field

Example: alter table student drop column Sal;

(5). Modify the table name

Example: Rename student to Stu;

 

5. delete a table

Use the drop TABLE statement to delete a table.

Example: Drop table student;

 

6. insert data

(1). insert data into all fields

Example: insert into student values (1001, 'skycloud', 'mal', '01-August 8-88', 2000 );

In Oracle, the default date format is 'dd-mon-yy'. DD is a day, mon is a month, and YY is a two-digit year.

Modify the default format of the date (temporary modification is still the default format after the database is restarted, if you want to modify the Registry)

For example, alter session set nls_date_format = 'yyyy-mm-dd ';

After modification, you can use the familiar date format to insert date data.

Example: insert into student values (1002, 'skycloud1 ', 'mal', '2017-07-01', 1987 );

(2) Insert Part of the data

Example: insert into student (ID, name, sex, birthday) values (1003, 'skycloud2', 'female ', '20-12-88', null );

How can I query the records whose birthday is null in the student table?

Case study: Select * from student where Birthday = NULL;

Case study: Select * from student where birthday is null;

If you want to query records whose birthdays are not null in the student table, how can you query them?

Select * from student where birthday is not null;

 

7. modify data

(1) modify a field

For example, update student set sex = 'female 'Where id = 1002;

(2) Modify Multiple Fields

Example: Update student set name = 'skycloud3', sex = 'female ', Birthday = '01-07-1987' Where id = 1002;

 

8. delete data

(1). Delete from student; -- delete all records. The table structure is still in progress, logs are written, and the recovery speed is slow.

(2). Delete from student where id = 1003; -- delete a record.

(3). Drop table student; -- delete the table structure and table data.

(4). truncate table student; -- delete all records in the table. The table structure is still in progress, and no logs are written. deleted records cannot be retrieved, which is fast.

(5). savepoint A; -- create a save point.

(6). rollback to a; -- restore to save point.

An experienced DBA should often create a storage point when it is correct.