[learning notes] Oracle field type, table building statement, adding constraint

SQL statement introduction

  • Data definition language (DDL), including CREATE, ALTER, DROP, etc.
  • Data manipulation language (DML), including INSERT, UPDATE, DELETE, SELECT FOR UPDATE, etc.
  • Data query language (DQL), including basic query statement, Order By clause, Group By clause, etc.
  • Transaction control language (TCL), including COMMIT, SAVEPOINT, ROLLBACK.
  • Data control language (DCL), GRANT, REVOKE.

Field type

VARCHAR2(length)

String type: stores a variable length string. Length is the maximum length of the string. The default value is 1 and the maximum value is no more than 4000.

CHAR(length)

String type: store fixed length string, fixed length of length string, default is 1, maximum is 2000.

NUMBER(a,b)

Numeric type: stores numeric types, including integers and floating-point types. a represents the maximum number of decimal places, including decimal places and decimal points; b represents the number of decimal places. Example:

number(6,2), input 123.12345, actual storage: 123.12.

number(4,2), enter 12312.345, and you will be prompted that it cannot be saved, which exceeds the specified precision of the storage.

DATA

Time type: stores the date and time, including year, month, day, hour, minute and second.

The built-in function sysdate gets the DATA type.

TIMESTAMP

Time type: stores not only the date and time, but also the time zone.

The built-in function systimestamp gets the timestamp type.

CLOB

Large field type: store large text, strings longer than 4000.

BLOB

Binary type: store binary objects, such as pictures, videos, sounds and other converted binary objects.

Create table

-- stuinfo Student information form
create table STUDENT.stuinfo
(
  stuid      varchar2(11) not null,--Student ID
  stuname    varchar2(50) not null,--Student name
  sex        char(1) not null,     --Gender
  age        number(2) not null,   --Age
  classno    varchar2(7) not null, --Class number
  stuaddress varchar2(100) default 'Not entered',  --address
  grade      char(4) not null,     --grade
  enroldate  date,                 --Admission time
  idnumber   varchar2(18) default 'ID card not collected' not null   --ID
)
-- stuinfo The table space stored is users
-- storage Represents a storage parameter
-- initial Representation section(extent)One expansion 64 k
-- minextents The minimum number of segments is 1
-- maxextents The maximum number of sections is unlimited
tablespace USERS
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table STUDENT.stuinfo
  is 'Student information form';
-- Add comments to the columns 
comment on column STUDENT.stuinfo.stuid
  is 'Student ID';
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';

Add constraint

-- Establish/Rebuild primary key index, unique index, foreign key index
-- hold stuid Set as primary key, the data of primary key field must be unique (Student ID is unique)
alter table STUDENT.STUINFO
  add constraint pk_stuinfo_stuid primary key (STUID);
   
-- Establish/Rebuild check constraints
-- Age age Add constraint, age of students can only be 0-50 Between years
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_age check (age>0 and age<=50);
   
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');

Tags: Oracle SQL

Posted on Sat, 07 Mar 2020 18:30:42 -0800 by discorevilo