Oracle learning VII - Oracle table building

Keywords: Database Oracle SQL

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 typeType 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.
dateTime type: stores date and time, including year, month, day, hour, minute and second. Example: the built-in function sysdate obtains the date type
timestampTime type: stores not only the date and time, but also the time zone. Example: the built-in function systimestamp obtains the timestamp type
clobLarge field type: stores large text, such as unstructured txt text, and strings with fields greater than 4000 in length.
blobBinary 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)
    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
  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 
  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 
  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
  add constraint ch_stuinfo_sex
  check (sex='1' or sex='2');
  add constraint ch_stuinfo_GRADE
  check (grade>='1900' and grade<='2999');

Posted by PRodgers4284 on Mon, 25 Oct 2021 19:48:57 -0700