oracle學習（二）

--刪除表--
drop table account;

--學生資訊表--
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)--設為主健
);
--插入資料--
insert into student
select  '小豬豬','M',to_date('20070421','YYYYMMDD') from dual
union--多條 insert
select  '小豬豬','M',to_date('20070421','YYYYMMDD') from dual;

在union不能使用STU_ID.NEXTVAL
--課程資訊--
create table courses(
COURSE_ID NUMBER(10) NOT NULL,
COURSE_NAME varchar2(20) NOT NULL,
constraint PK_COURSES PRIMARY KEY(COURSE_ID)
);
--查看錶欄位--
desc student
----選修課----
create table courses(
COURSE_ID NUMBER(10) NOT NULL,
COURSE_NAME varchar2(20) NOT NULL,
constraint PK_COURSES PRIMARY KEY(COURSE_ID)
);

----建立自增長-----
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, 'Zhang san','w',to_date('20070421','YYYYMMDD'));
 
--查詢data類型--
 SELECT stu_id,stu_name,stu_sex,to_char(STU_BIRTHDAY,'yyyy-mm-dd hh:mm:ss') FROM STUDENT ;
 
 --DBLINK資料鏈--
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)
     )
   )';
--訪問對方資料庫上建立的表--
 select * from SYSTEM.PERSON@DBLINK_hj; 

----視圖-----
create view VW_STUDNET AS SELECT STU_NAME FROM STUDENT;
SELECT * FROM VW_STUDNET;

---同義字----
CONNECT estore/estore AS sysdba;
CREATE PUBLIC SYNONYM student FOR oe.student;