Oracle Learning (2)

-- Delete a table --
Drop Table account;

-- Student information table --
Create Table student (
Stu_id number (10) Not null,
Stu_name varchar2 (20) not null,
Stu_sex char (5) not null,
Stu_birthday date not null,
Constraint pk_student primary key (stu_id) -- set as primary key
);
-- Insert data --
Insert into student
Select 'pig pigs', 'M', to_date ('201312', 'yyyymmdd') from dual
Union -- multiple insert statements
Select 'pig pigs', 'M', to_date ('201312', 'yyyymmdd') from dual;

Stu_id.nextval cannot be used in union.
-- Course information --
Create Table courses (
Course_id number (10) Not null,
Course_name varchar2 (20) not null,
Constraint pk_courses primary key (course_id)
);
-- View table fields --
Desc student
---- Electives ----
Create Table courses (
Course_id number (10) Not null,
Course_name varchar2 (20) not null,
Constraint pk_courses primary key (course_id)
);

---- create auto-increment -----
Create sequence stu_id_seq increment by 1 start with 1
maxvalue 99999 cycle nocache;
insert into student (stu_id, stu_name, stu_sex, stu_birthday) values (stu_id_seq. nextval, 'zhangsan', 'w', to_date ('000000', 'yyyymmdd');
-- query data type --
select stu_id, stu_name, stu_sex, to_char (stu_birthday, 'yyyy-mm-dd hh: mm: ss') from student;
-- dblink Data Link --
create database link dblink_hj
connect to system identified by Oracle
using '(description =
(address_list =
(address = (Protocol = TCP) (host = 192.168.3.11) (Port = 1521)
(CONNECT_DATA =
(SID = orcl)
)';
-- access the table created on the other database --
select * from system. person @ dblink_hj;

---- View -----
Create view vw_studnet as select stu_name from student;
Select * From vw_studnet;

--- Synonym ----
Connect estore/estore as sysdba;
Create public synonym student for OE. student;