Oracle create table
Oracle table is the core of Oracle database and the logical basis for storing data. Oracle table is a two-dimensional data structure. The data with column fields and corresponding columns constitute a data storage structure. You can simply look at a two-dimensional table in rows and columns. Columns represent Oracle columns and rows represent a row of data (that is, a data record).
Oracle field data type
| data type | Type interpretation |
|---|---|
| varchar2(n) | String type: stores a string of variable length. n: is the maximum length of the string. When it is not filled in by default, it is 1, and the maximum length does not exceed 4000. |
| char(n) | String type: stores a fixed length string. n: the fixed length size of the string. The default is 1 and the maximum length does not exceed 2000. |
| number(6,2) | Numeric type: stores numeric types, either integer or floating point. a represents the maximum number of digits of the value: including decimal places and decimal points, and b represents the number of decimal places. Example: number(6,2), enter 123.12345, and the actual deposit is 123.12. number(4,2), enter 12312.345, and the prompt cannot be saved, which exceeds the specified accuracy. |
| date | Time type: stores date and time, including year, month, day, hour, minute and second. Example: the built-in function sysdate obtains the date type |
| timestamp | Time type: stores not only the date and time, but also the time zone. Example: the built-in function systimestamp obtains the timestamp type |
| clob | Large field type: stores large text, such as unstructured txt text, and strings with fields greater than 4000 in length. |
| blob | Binary type: it stores binary objects, such as binary objects converted from pictures, videos, sounds, etc |
create table statement
Oracle database table creation is executed through the create table command. A student user is created through Oracle learning IV. now you can create a studinfo (student information table) under the student user to learn the use of the create table command.
Case 1: create stuinfo (student information table)
-- Create table
create table STUDENT.stuinfo
(
stuid varchar2(11) not null,--Student No.:'S'+Shift number (7 digits)+Student serial number (3 digits)(1)
stuname varchar2(50) not null,--Student name
sex char(1) not null,--Gender
age number(2) not null,--Age
classno varchar2(7) not null,--Shift number:'C'+Grade (4 digits)+Class serial number (2 digits)
stuaddress varchar2(100) default 'Address not entered',--address (2)
grade char(4) not null,--grade
enroldate date,--Admission time
idnumber varchar2(18) default 'ID card not collected' not null--ID
)
tablespace USERS --(3)
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table STUDENT.stuinfo --(4)
is 'Student information sheet';
-- Add comments to the columns
comment on column STUDENT.stuinfo.stuid -- (5)
is 'Student number';
comment on column STUDENT.stuinfo.stuname
is 'Student name';
comment on column STUDENT.stuinfo.sex
is 'Student gender';
comment on column STUDENT.stuinfo.age
is 'Student age';
comment on column STUDENT.stuinfo.classno
is 'Student class number';
comment on column STUDENT.stuinfo.stuaddress
is 'Student address';
comment on column STUDENT.stuinfo.grade
is 'grade';
comment on column STUDENT.stuinfo.enroldate
is 'Admission time';
comment on column STUDENT.stuinfo.idnumber
is 'ID number';
Code parsing:
- (1) Location: not null indicates that the student number field (stuid) cannot be empty.
- (2) Where: default means that the value of "address not entered" will be filled in by default when the field stuaddress is not filled in.
- (3) Location: indicates that the table space stored in the table stuinfo is users, and storage indicates storage parameters: the extent is extended by 64k at a time, the number of the most cell segments is 1, and the maximum number of segments is unlimited.
- (4) comment on table: comment on the table name.
- (5) comment on column: comment on a table field.
---------------—
After creating the stuinfo student information table through the above command, you can also add corresponding constraints to the table to ensure the accuracy of the table data. For example, the age of students cannot be older, which may be wrong data, and the data other than 1 (male) and 2 (female) cannot be filled in.
Case 2: stuinfo (student information table) adding constraints
-- Create/Recreate primary, unique and foreign key constraints alter table STUDENT.STUINFO add constraint pk_stuinfo_stuid primary key (STUID); --hold stuid As a primary key, the data of the primary key field must be unique (the student number is unique) -- Create/Recreate check constraints alter table STUDENT.STUINFO add constraint ch_stuinfo_age check (age>0 and age<=50);--Give field age age Add constraints, the age of students can only be 0-50 Within the age of alter table STUDENT.STUINFO add constraint ch_stuinfo_sex check (sex='1' or sex='2'); alter table STUDENT.STUINFO add constraint ch_stuinfo_GRADE check (grade>='1900' and grade<='2999');