oracle表、约束、空间、别名等

    -建表

    create table userinfo(
    	id number(6),
    	name varchar2(3),
    	sex number(1),
    	age number(3),
    	birthday date,
    	address varchar2(50),
    	email varchar2(30),
    	phone number(11)
    );
    

    
    drop table userinfo;
    
    建表带约束
    
    create table uerinfo(
    	id number(6) primary key,--主键
    	name varchar2(20) not null,--非空
    	sex number(1),
    	age number(3) default 18,--默认18
    	birthday date,
    	address varchar2(50),
    	email varchar2(30) unique,
    	phone number(11)
    	deptno number(2) references dept(deptno)--外键
    );
    
    
    带约束名称
    
    create table userinfo(
    	id number(6) constraint id_pk primary key,
    	name varchar2(20) constraint name_nn not null,
    	sex number(1),
    	age number(3) default 18,
    	birthday date,
    	address varchar2(50),
    	email varchar2(30) constraint email_uqe unique
    	phone number(2) constraint dept_deptno_ref references dept(DEPTNO)
    );
    
    
    列模式
    
    create table userinfo2(
    	id number(6),
    	name varchar2(20),
    	sex number(1),
    	age number(3) default 18,
    	address varchar2(50),
    	email varchar2(3),
    	phone number(11),
    	deptno number(2)
    	
    	constraint id_pk primary key(id), --也可以两个以上，联合主键
    	constraint dept_deptno_ref foreign key(deptno) references dept(deptno),
    	constraint email_name_uqe unique(email,name)
    	
    );
    
    
    alter 模式
    alter table userinfo2 add(msn varchar2(20));
    alter table userinfo2 modify(msn varchar2(25));
    alter table userinfo2 drop(msn);
    alter table userinfo2 drop constraint id_pk;
    alter table userinfo2 add constraint id_pk primary key(id);
    
    建立视图
    create table v$_dept_view as SELECT deptno,DNAME FROM scott.DEPT;
    alter view v$_dept_view compile;
    SELECT * FROM v$_dept_view;
    create view ly_view_t as select deptno,dname from scott.dept;
    
    创建索引
    普通索引 --create index idx_dpt_dname on scott.dept(dname);
    联合索引 --create index idx_dpt_dname_deptno on dept(dname,deptno);
    唯一索引
    create unique index idx_emp_ename on scott.emp(ename);
    反向键索引
    create index idx_emp_re_no on scott.emp(empno) reverse;
    位图索引
    create bitmap index idx_emp_ename on scott.emp(dname);
    索引组织表,一定要有主键
    
    create table tab (
        id int primary key,
        name varchar2(20)
    )   organization index;
   索引组织表的insert效率非常低
    
    分区表索引
    create index idx_name on table(col) local/global;
    
    创建序列
    
    create sequence seq;
    	select seq.nextval from dual;
    	insert into tab values (seq.nextval,‘movie’);
    
    create sequence seqtab
    	start with 1 --从1开始
    	increment by 2 --每次+3
    	nomaxvalue --没有最大值
    	minvalue 1 --最小值1
    	nocycle --不循环
    	mocache; --不缓存
    
    
     建立别名
    create synonym s_emp for scott.emp;
    create public synonym s_dept for scott.dept;
    SELECT * from scott.emp;
    SELECT * from scott.dept;
    
    创建表空间
    
        create tablespace test
        datafile 'E:\test.dbf'
        size 5M
        autoextend on next 2M maxsize 10M;
        
        创建用户、分配可以操作表空间
        create user abc
        identified by abc
        default tablespace  test
        temporary tablespace temp;
        
        创建表空间
        create tablespace test
        datafile 'e:\test.dbf'
        size 1M
        autoextend on;
        
        扩展表空间--修改表空间大小
        alter database
        datafile 'e:test.dbf'
        resize 2M;
        
        扩展表空间--添加数据文件
        alter tablespace test
        add datafile 'e:\test2.dbf'
        size 1M;
        
        设置dbf文件自动增长
        alter database 
        datafile 'e:test2.dbf'
        autoextend on next 2M maxsize 4M;
        
        表空间重命名
        alter tablespace test 
        rename to test;
        
        分离表空间（脱机）
        alter tablespace test
        offline temporary;
        
        归档模式下脱机
        alter tablespace test
        offline immediate;
        
        使表空间联机
        alter tablespace test online;
        
        删除无数据的表空间
        drop tablespace test;
        
        删除带数据的表空间
        drop tablespace test
        including contents;