ORACLE-04:DDL the operation of the Language data table

------------I do not have him, but the hand is ripe, humble and foolish, and eager to be hungry-------------

DDL operations:

-- Create student tables

CREATE TABLE Student (

Sno Number (4) is not NULL,

Sname VARCHAR2 (Ten) NOT NULL,

Birthdday Date

);

-- Add two fields to the student table

ALTER TABLE student Add (SAL number (7,2), WeChat varchar2 (20));

-- Modify the length of the sname field

ALTER TABLE student Modify (sname varchar2 (20));

-- new gender field

ALTER TABLE student Add (sax char (2));

-- Modify the name of the field

ALTER TABLE student rename column sax to sex;

-- Add a PRIMARY KEY constraint to the table

ALTER TABLE student ADD constraint Pk_student_sno primary key (SNO);

-- adds a unique constraint to the sname in the table

ALTER TABLE student add constraint uk_student_sname unique (sname);

-- add check constraints to the Sex field in the table

ALTER TABLE student add constraint ck_student_sex check (sex in (' Male ', ' female '));

-- Add a new grade Number field to the table

ALTER TABLE student Add (GID number (4));

-- Create grade table Main Table

CREATE TABLE Grade (

Gradeid Number (4) Not null primary key,

Gradename VARCHAR2 (TEN) NOT NULL

);

-- Create a FOREIGN key constraint

ALTER TABLE student ADD constraint Fk_student_grade_gid foreign key (GID)

References grade (Gradeid);

------------------------------------------------------

Sequence Some like MySQL's self-increment column, SQL Server's identity column

-- Create sequence

Create sequence Sq_studet_sno --The name of the sequence

Start with--the starting value of the sequence number

Increment by--the value of each increment in the step length

MaxValue 9999999999 --The maximum value of the sequence number

Cycle serial number is restarted when cycle--nocycle reaches its maximum value

Cache --whether the serial number is cached, the default is 20, if the step is 10, the cache 20 is 200 numbers, you can use a nocache

-- Modify the sequence name

Rename Sq_student_no to Sq_student_sno

-- query created sequence, system default view

SELECT * from User_sequences;

-- using sequence nextval

Select Sq_student_sno.nextval from dual;

-- View the value of the current sequence Currval

Select Sq_student_sno.currval from dual;

-- simulates adding data to the grade table using sequences

Insert into Grade (Gradeid,gradename)

values (Sq_student_sno.nextval, ' third grade ');  

-- inquiry grade

SELECT * from grade;

ORACLE-04:DDL the operation of the Language data table