Notes for Oracle beginners (2)-basic content (updating in real time ..)

Continued Oracle notes (1)-basic content (updating in real time ..)

1. Create a table in oracleThe statement is the same as that in SQL server.
 

SQL> create table Course
2 (cno char (8 ),
3 cname varchar2 (20 ),
4 ccredit int,
5 cpno char (8)
6 );

The table has been created.

However, during the write process, it is found that the attribute of the cno column is not null. So I tried to use the original SQL serverModify column attributesI tried it and found it was not feasible.
 

SQL> alter table Course alter cno char (8) not null;
Alter table Course alter cno char (8) not null
*
Row 3 has an error:
ORA-01735: Invalid alter table Option

I found it online. In oracle, the attributes of the modified column are a bit different.
 

SQL> alter table CourseMODIFY(Cno char (8) not null)
SQL>/

The table has been changed.

SQL> desc course
Is the name empty? Type
-----------------------------------------------------------------------------
Cno not null char (8)
CNAME VARCHAR2 (20)
Ccredit number (38)
Cpno char (8)

 

2. How to add a column?
The SQL server statement is as follows:
Alter table course cdept char (8 );

If you try it in oralce, it runs successfully as follows:
SQL> alter table course

2 add cdept char (8 );

The table has been changed.

ViewTable Structure, As follows:

SQL> desc course;
Is the name empty? Type
----------------------------------------------------------------------
Cno not null char (8)
CNAME VARCHAR2 (20)
Ccredit number (38)
Cpno char (8)
Cdept char (8)

 

3. How to delete a column?
You should be familiar with the SQL server statement as follows:
Alter table Course drop cpno;

An error occurred while trying it out in oracle, saying that the keyword is missing:
 

SQL> alter table course
2 drop cdept;
Drop cdept
*
Row 3 has an error:
ORA-00905: Missing keywords

What keywords are missing? I found the reason online, as if it was column. Add this keyword and try again. You can find it:

SQL> alter table course
2 dropColumnCdept;

The table has been changed.

Now, let's look at the structure in the table below. The cdept column is actually deleted.

 

After creating a table, the following table structures are frequently used. I have not tried them one by one. For your reference:

1. to modify the cname VARCHAR2 (20) attribute in the Course table, change the length to 100 (only small to large, 100 --> 20 ). ALTER table course MODIFY (canonical name VARCHAR2 (100); 2. To modify the name of col1. Alter table Course MODIFY (col1 VARHCAR2 (50), col2 VARCHAR2 (100); 3. MODIFY only the field name: alter table liwh_620 rename column device_number TO serial_number;

Problem:After using scott to log on to Oracle, create a view and prompt "insufficient Permissions". How can this problem be solved?
Answer:
This is because scott has no permission to create a view.
Solution:
First, use the system account to log on. "tigertiger" indicates the password specified during Oracle Installation (which can be modified ):
Sqlplus system/tigertiger

Then execute:
Grant create any view to scott
Prompt: Authorization successful.
Run:
Exit
Exit the current system account.

Use sqlplus to log on to create a view, for example:
Sqlplus scott/tigert
Create a simple view as follows:
Create or replace view v1
As
Select * from t1;

 This is my note today. I mainly introduce some issues and notes when creating a data table. I hope it will be helpful for beginners like me.