Oracle Database creation process example

Sqlplus system/system @ orcl -- connect

SQL> ed a -- create SQL text
SQL> get a -- load a. SQL into the cache
SQL>/

Create temporary tablespace sa_temp -- temporary tablespace
Tempfile 'e: \ dbf \ sa_temp.dbf'
Size 10 m
Autoextend on;

Create tablespace sa_space -- tablespace
Logging
Datafile 'e: \ dbf \ sa_space.dbf'
Size 20 m -- 20 M
Autoextend on; -- auto-Increment

Create user sa identified by sa -- create a table space for the user
Default tablespace sa_space
Temporary tablespace sa_temp;

Grant connect, resource, dba to sa; -- grant connection and dba permissions to users

Conn sa/sa -- Role sa

-- Create a student information table
Create table studentInfo (
StuId number primary key not null,
Tel nvarchar2 (15 ),
Sex char (2) not null,
SchoolTime date not null,
Email nvarchar2 (50) not null,
Remark nvarchar2 (500) not null
);

-- Create a course schedule
Create table Course (
CourseId number primary key not null,
CourseCode nvarchar2 (15), -- course code
CourseName nvarchar2 (50)
);

-- Create a student-course relationship table (many to many)
Create table stdent_course (
CourseId number not null,
StuId number not null
);

-- Create Sequence
Create sequence seq_studentInfo_stuId -- Student sequence
Increment by 1 -- add 1 each time
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Nocache -- do not create a buffer

Create sequence seq_course_courseId -- course sequence
Increment by 1 -- add 1 each time
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Nocache -- do not create a buffer

-- Create a trigger
Create or replace trigger tri_studentInfo_stuId -- auto-increment of student primary key
Before
Insert on studentInfo for each row
Begin
Select seq_studentInfo_stuId.nextval into: New. stuId from dual;
End;

Create or replace trigger tri_course_courseId -- auto-increment course primary key
Before
Insert on course for each row
Begin
Select seq_course_courseId.nextval into: New. courseId from dual;
End;

-- Establish the main external relationship of the curriculum
Alter table stdent_course add constraint fk_stdentcourse_courseId
Foreign key (courseId) references course (courseId );

-- Establish external student relationship
Alter table stdent_course add constraint fk_stdentcourse_courseId
Foreign key (stuId) references studentId (stuId );

-- SQL Test
Insert into studentinfo (tel, sex, schooltime, email, remark)
Values ('000000', 'male', to_date ('2017-01-12 ', 'yyyy-MM-dd'), 'ss @ ww.com', 'Love is gang ');
Insert into studentinfo (tel, sex, schooltime, email, remark)
Values ('000000', 'male', to_date ('2017-02-12 ', 'yyyy-MM-dd'), 'ss1 @ ww.com', 'Love is just 111111 ');

Insert into course (coursecode, coursename) values ('001', 'China ');
Insert into course (coursecode, coursename) values ('002', 'mat ');

Insert into stdent_course (stuid, courseid) values (1, 1 );
Insert into stdent_course (stuid, courseid) values (1, 2 );
Insert into stdent_course (stuid, courseid) values (2, 1 );

Select * from studentinfo;
Select * from course;
Select * from stdent_course;