Add a field example using SQL statements in oracle (delete a field statement in SQL)

Syntax for adding fields: alter table tablename add (column datatype [default value] [null/not null],...);

Alter table tablename modify (column datatype [default value] [null/not null],...);

Syntax for deleting a field: alter table tablename drop (column );

Multiple columns are separated by commas.

Use alter table to add, delete, and modify a column.

Create a table structure:
Copy codeThe Code is as follows:
Create table test1
(Id varchar2 (20) not null );


Add a field:
Copy codeThe Code is as follows:
Alter table test1
Add (name varchar2 (30) default 'Anonymous 'not null );


Use an SQL statement to add three fields at the same time:
Copy codeThe Code is as follows:
Alter table test1
Add (name varchar2 (30) default 'Anonymous 'not null,

Age integer default 22 not null,

Has_money number (9, 2)

);


Modify a field
Copy codeThe Code is as follows:
Alter table test1
Modify (name varchar2 (16) default 'unknown ');


In addition, the more formal syntax is:
Copy codeThe Code is as follows:
-- Add/modify columns
Alter table TABLE_NAME rename column FIELD_NAME to NEW_FIELD_NAME;

Delete A Field
Copy codeThe Code is as follows:
Alter table test1
Drop column name;

It should be noted that if a value already exists in a column, an error will occur if you want to modify it to a column width smaller than these values.

For example, if we insert a value
Copy codeThe Code is as follows:
Insert into test1
Values ('1', 'We love you ');

Then I modified the column: alter table test1.
Modify (name varchar2 (8 ));
The following error is returned:
ERROR is located in row 2nd:
ORA-01441: cannot reduce column length because some values are too large

Advanced usage:

Rename a table
Copy codeThe Code is as follows:
Alter table table_name rename to new_table_name;


Modify column name

Syntax:
Copy codeThe Code is as follows:
Alter table table_name rename column supplier_name to sname;

Example:
Copy codeThe Code is as follows:
Alter table s_dept rename column age to age1;

 

Appendix: Create a table with a primary key>
Copy codeThe Code is as follows:
Create table student (
Studentid int primary key not null,
Studentname varchar (8 ),
Age int );


1. Create a primary key constraint when creating a table
(1) No Name
Copy codeThe Code is as follows:
Create table student (
Studentid int primary key not null,
Studentname varchar (8 ),
Age int );

(2) named
Copy codeThe Code is as follows:
Create table students (
Studentid int,
Studentname varchar (8 ),
Age int,
Constraint yy primary key (studentid ));

2. Delete the existing primary key constraints in the table

(1) No Name

SELECT * from user_cons_columns;
Find the primary key name in the student table. The primary key name is SYS_C002715.
Alter table student drop constraint SYS_C002715;

(2) named
Copy codeThe Code is as follows:
Alter table students drop constraint yy;

3. Add a primary key constraint to the table
Copy codeThe Code is as follows:
Alter table student add constraint pk_student primary key (studentid );