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;