Oracle Edit Table Usage

Syntax for adding a field: ALTER TABLE tablename Add (column datatype [default value][null/not null],....);

Modify the syntax of the field: ALTER TABLE tablename modify (column datatype [default value][null/not null],....);

Syntax for deleting a field: ALTER TABLE tablename drop (column);

To create a table structure:
CREATE TABLE Test1
(ID varchar2 () not NULL);

Add a field:

ALTER TABLE Test1
Add (name VARCHAR2 () default ' anonymous ' not NULL);

Add three fields using one SQL statement at a time:

ALTER TABLE Test1
Add (name VARCHAR2 () default ' anonymous ' NOT NULL,

The age integer default is not NULL,

Has_money Number (9,2)

);

Modify a field

ALTER TABLE Test1
Modify (name VARCHAR2 (+) Default ' unknown ');

Delete a field

ALTER TABLE Test1
Drop column name;

Oracle modifies table field names and lengths in a way that is different from standard SQL, and it needs to add specific keywords.

Use the rename keyword to modify the field name: ALTER TABLE name rename column Old field name to new field name;

Alert Table WTC rename column qy to Qcompany;

Use the modify keyword to implement modifications to the data type: ALTER TABLE name modify field name data type;

Alert Table WTC modify column Qcompany varchar2 (500);

Advanced usage:

Renaming a table
ALTER TABLE table_name RENAME to New_table_name;

Modify the name of a column

Grammar:
ALTER TABLE table_name RENAME COLUMN supplier_name to sname;

Example:
ALTER TABLE s_dept Rename column age to Age1;

Attached: Creating a table with a primary key >>

CREATE TABLE Student (
StudentID int PRIMARY key NOT NULL,
Studentname varchar (8),
age int);

1. Create a PRIMARY KEY constraint while creating a table
(1) No naming
CREATE TABLE Student (
StudentID int PRIMARY key NOT NULL,
Studentname varchar (8),
age int);
(2) have a name
CREATE TABLE Students (
StudentID int,
Studentname varchar (8),
Age int,
Constraint yy primary KEY (StudentID));

2. Delete the existing PRIMARY KEY constraint in the table
(1) No naming
Available as SELECT * from User_cons_columns;
Find primary key name in table student table with primary key named sys_c002715
ALTER TABLE student drop constraint sys_c002715;
(2) have a name
ALTER TABLE students drop constraint yy;

3. Add a PRIMARY KEY constraint to the table
ALTER TABLE student ADD constraint Pk_student primary key (StudentID);

Oracle Edit Table Usage