Oracle table management

1. Naming rules for tables and columns
A. It must start with a letter.
B. The length cannot exceed 30 characters
C. Oracle reserved words (keywords) cannot be used)
D. Only the following characters can be used for A-Z, a-z, 0-9, $ ,#

2. Create a table
Syntax: create table Name (column name data type, column name data type ,...)
SQL> create table Student (StuNo number (10), Gender char (4), Birthday date, Address varchar (20 ));

3. Add Columns
Syntax: alter table Student add (column name data type)
SQL> alter table Student add (ClassNo char (15 ));

4. Modify the Column Length
Syntax: alter table Student modify (column name data type)
SQL> alter table Student modify (Address varchar (15 ));

5. Modify the column type
Syntax: alter table Student modify (column name data type)
SQL> alter table Student modify (Address varchar (15 ));

6. Delete Columns
Syntax: alter table Student drop column name
SQL> alter table Student drop column Address;

4. Modify the table name
Syntax: rename original table name to new table name
SQL> rename Student to Student1;

8. delete a table
Syntax: drop table Name
SQL> drop table Student;

9 insert value
Syntax: insert into Student values ("value 1", "value 2", "value 3", "value 4", "value 5 ")
SQL> insert into Student values ("09110120", "male", "27-5-1987", "Beijing, China ");

10 change the default date format
The default date format in Oracle is "dd-MON-YY"
Syntax: alter session set nls_date_format = 'new date format'
SQL> alter session set nls_date_format = 'yyyy-mm-dd'

11. Modification records
Syntax: update table name set column name = 'value 1'
SQL> update Student set Address = 'shanghai, China' where StuNo = '000000 ';

12. Delete table information
Syntax: delete from Table Name
SQL> delete from Student

14. delete a record in the table
Syntax: delete from table name where Condition
SQL> delete from Student where StuNo = '000000 ';

15. Delete the information in the table (cannot be rolled back)
Syntax: truncate table name
SQL> truncate table Student;
Features: Quick table deletion. Because no logs are written, you cannot roll back and retrieve the deleted data.

16 Create milestones
SQL> savepoint milestone name