oracle database: table operations

Create a table:

create table 表名(
	字段名 类型(长度),

Before proceeding, let me introduce the data types of the oracle database

type of dataData description
varchar2(size)Variable-length character string, the maximum length is size bytes, the maximum size is 4000, and the minimum is 1; you must specify a varchar2 size
nvarchar2(size)Variable-length character string, according to the selected national character set, the maximum length is size bytes, the maximum size depends on the number of bytes required to store each character, the upper limit is 4000, the minimum is 1 ; You must specify a varchar2 size
number(p,s)The precision is p and the value range is s. The intensive reading range is from 1 to 38; the value range is from -84 to 127; for example, number (5, 2) means that the integer part has a maximum of 3 digits, and the decimal part is 2 digits; number (5 , -2) means that the integer part is up to 7, where the last two digits of the integer are 0, and the preceding ones are rounded; number means to use the default value, which is equivalent to number(5)
longVariable length character data, its length can reach 2G bytes
dateDate type, ranging from January 1, 4712 BC to December 31, 4712 AD
raw(size)Raw binary data with a length of size bytes, the maximum size is 2000 bytes, you must specify the raw size
long rawVariable-length raw binary data, which can be up to 2G bytes long
char(size)Fixed-length character data, its length is size bytes, the maximum size is 2000 bytes, the minimum and default value is 1
nchar(size)It is also a fixed length. Defined according to the Unicode standard
clongA large character object, which can accommodate single-byte characters, does not support character sets with different widths, and the maximum is 4G bytes
nlongA large character object, which can accommodate single-byte characters, does not support character sets with unequal widths, the maximum is 4G bytes, and stores national character sets
blongA large binary object with a maximum of 4G bytes
bfileContains a locator of a large binary file, which is stored outside the database, making it possible to access the external LOB stored in the database server with character stream I/O, the maximum size is 4G bytes

What we often use is varcharactually varchar2synonyms, which oracleare officially recommended in Chinavarchar2

Modify the structure of the table

  • Modify the table name:, rename 原表名 to 新表名for example rename table1 to table2
  • Modify the column name:, alter table 表名 rename column 列名 to 新列名for example alter table test rename column age to new_age;
  • Modify the field type:, alter table 表名 modify(字段 类型)for example alter table test modify(age varchar2);
  • Add column:, alter table 表名 add (字段 类型)for example alter table test add(habbit varchar2(200))
  • Delete column:, alter table 表名 drop column 字段for example alter table test drop column age;

Create a table:

--创建表 w_user
create table w_user(
       name varchar(20),
       age number(2)

After running, you can see that the newly created table

Insert picture description here

changes the table name:

rename w_user to wb_user;

Refresh and see that the table name has been modified successfully and the column name has been

Insert picture description here

modified: let's change the namefield tow_name

alter table wb_user rename column name to w_name;

Then look at the column name in the table to

Insert picture description here

modify the field type: here change agethe type of the field to varchar2, and pass in a piece of data

alter table wb_user modify(age varchar2(20));
insert into wb_user values('张三','28岁');

Contents in the query table after modification:

Insert picture description here

add column: add a numbered columnnum

alter table wb_user add num number(20);

addThe column name and type can be parentheses or not.

Insert picture description here

Delete column: delete the numcolumn just added

alter table wb_user drop column num;

Check whether the deletion was successful:

Insert picture description here

successfully deleted.