Oracle-08-Modify Table Structure (add, delete, modify, and query existing tables)

Oracle-08-Modify Table Structure (add, delete, modify, and query existing tables)

I. Example of adding a primary key when creating a table:

Create table stu (

Id number (5) primary key,

Name varchar2 (20)

);

The above is a primary key constraint. For more information, see. The data in the id column of the constraint table must not be empty and must not be repeated.

 

2. Three Common Operations for modifying the table structure:

1. Add a new column to a table

Syntax format:

Alter table name add

(Column Name Data Type [default value] [, column name…])

If one column is not enough, you can add one column after a comma.

[Note] alter is a DDL statement. Once changed, it is changed permanently.

 

For example, add the "age" column to Table A. The data type of this column is "number" and the default value is 23.

Alter table A add agenumber (2) default 23

 

Perform the following operations:

Step 1: first use lisi to log on to the database. Because there are too many tables in the original sys user, it may be difficult to query. Change the Custom User lisi to log on to the database, which is convenient to query, for example:

 

Step 2: query the number of tables under the current user,Note:

 

Step 3: If no table exists under the lisi user, create a table, for example:

 

Step 4: Use Step 2 to query again, for example:

 

Then desc A is used to query the structure of Table A, for example:

 

Table A is found under the lisi user.

Step 5: add the age column to Table A, for example:

 

Step 6: Use desc A to view the structure of Table A again, for example:

 

 

2. modify an existing column in a table

Syntax format:

Alter table name modify

(Column Name Data Type [default value] [, column name…])

[Note] Once the alter statement is executed, it is permanently modified.

 

For example, change the data type of the age column in table A to varchar2 (2)

Alter table A modify agevarchar2 (2)

 

Perform the following operations:

Step 1: Add A row of data to the empty table A to indicate the error message, for example:

 

Query the table structure and table content, such:

 

The data content is added successfully.

Step 2: change the age column to varchar2 (2), for example:

 

An error is reported. Because the modified age column already has data, the modified column must be empty before modification.

Step 3: Clear the age column of Table A with the following commands:

 

Query the table structure and table content:

 

Step 4: change the age column in table A to varchar2 (2), for example:

 

Note: to modify an existing column in a table, note the following:

(1) You can increase the width of a character-type column. Simply put,For example, if the original column is of the varchar2 (2) type,Regardless of whether the column is empty,Can increase the length, for example, adding 2 to 10, that is, varchar (10 );

(2) You can increase the width and accuracy of numeric columns,Number type, for example, number type;

(3) The column width can be reduced only when the values of all columns are empty or there is no data in the table;

(4) the data type of a column can be changed only when the values of all columns are empty,As shown in the preceding example;

(5) If you change the default value of a column, the default value can only affect subsequent operations,For example, after the default value of age column in table A is changed to 23, the original data in Table A is still empty, and the default value of age in Table A is 23. Therefore, only subsequent operations are affected:

Original table data query:

 

Modify the default value of the age column to 23:

 

After querying Table A again, we found that the age column of the original data was not changed:

 

Insert new data sheet 4:

 

The query shows that the age of new data sheet 4 is the new default value 23:

 

Therefore, modifying the default value of a column does not affect the original data, but only the post data;

(6) A char column can be changed to a varchar2 column only when the size of a column is not changed, or, you can change a varchar2 column to a char column,If the data length of the char type is insufficient, spaces are automatically filled. Therefore, if the varchar2 type is changed to the char type, the length will change, which is not safe.

 

3. delete a column from a table

Syntax format:

Alter table Name drop column name

 

For example, delete the age column in table.

Alter table A drop column age

 

Practice:

Step 1: first look at the current table A, for example:

 

Step 2: delete the age column, for example:

 

Step 3: Check whether the deletion is successful, for example: