oracle——笔记——DDL语句

            create
            table emp2 ( empno number(4),
                    ename varchar2(10),
                    job varchar2(9),
                    mgr number(4),
                    hiredate date,
                    sal number(7,2),
                    comm number(7,2),
                    deptno number(2) 
                 );
-----------------------------------------------------------------------------------------------------------insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, ‘SMITH‘, ‘CLERK‘, 7902, to_date(‘17-12-1980‘, ‘dd-mm-yyyy‘), 800.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, ‘ALLEN‘, ‘SALESMAN‘, 7698, to_date(‘20-02-1981‘, ‘dd-mm-yyyy‘), 1600.00, 300.00, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, ‘WARD‘, ‘SALESMAN‘, 7698, to_date(‘22-02-1981‘, ‘dd-mm-yyyy‘), 1250.00, 500.00, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, ‘JONES‘, ‘MANAGER‘, 7839, to_date(‘02-04-1981‘, ‘dd-mm-yyyy‘), 2975.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, ‘MARTIN‘, ‘SALESMAN‘, 7698, to_date(‘28-09-1981‘, ‘dd-mm-yyyy‘), 1250.00, 1400.00, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, ‘BLAKE‘, ‘MANAGER‘, 7839, to_date(‘01-05-1981‘, ‘dd-mm-yyyy‘), 2850.00, null, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, ‘CLARK‘, ‘MANAGER‘, 7839, to_date(‘09-06-1981‘, ‘dd-mm-yyyy‘), 2450.00, null, 10);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, ‘SCOTT‘, ‘ANALYST‘, 7566, to_date(‘19-04-1987‘, ‘dd-mm-yyyy‘), 3000.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, ‘KING‘, ‘PRESIDENT‘, null, to_date(‘17-11-1981‘, ‘dd-mm-yyyy‘), 5000.00, null, 10);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, ‘TURNER‘, ‘SALESMAN‘, 7698, to_date(‘08-09-1981‘, ‘dd-mm-yyyy‘), 1500.00, 0.00, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, ‘ADAMS‘, ‘CLERK‘, 7788, to_date(‘23-05-1987‘, ‘dd-mm-yyyy‘), 1100.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, ‘JAMES‘, ‘CLERK‘, 7698, to_date(‘03-12-1981‘, ‘dd-mm-yyyy‘), 950.00, null, 30);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, ‘FORD‘, ‘ANALYST‘, 7566, to_date(‘03-12-1981‘, ‘dd-mm-yyyy‘), 3000.00, null, 20);

insertinto emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, ‘MILLER‘, ‘CLERK‘, 7782, to_date(‘23-01-1982‘, ‘dd-mm-yyyy‘), 1300.00, null, 10);

=============================================================================================================================createtable dept2 ( deptno number(2),
                     dname varchar2(14),
                     loc varchar2(13)
                   );

----------------------------------------------------insertinto dept2 (DEPTNO, DNAME, LOC)
values (10, ‘ACCOUNTING‘, ‘NEW YORK‘);

insertinto dept2 (DEPTNO, DNAME, LOC)
values (20, ‘RESEARCH‘, ‘DALLAS‘);

insertinto dept2 (DEPTNO, DNAME, LOC)
values (30, ‘SALES‘, ‘CHICAGO‘);

insertinto dept2 (DEPTNO, DNAME, LOC)
values (40, ‘OPERATIONS‘, ‘BOSTON‘);

============================================================createtable salgrade2 ( grade number,
                         losal number,
                         hisal number
                       );

--------------------------------------------------insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (1, 700, 1200);

insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);

insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);

insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);

insertinto salgrade2 (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);


========================================================select*from emp2;

select*from dept2;

select*from salgrade2;

=====================================select*from emp2;        --employee2 员工表select*from dept2;       --department2 部门表select*from salgrade2;   -- salary grade2 工资等级表-----------------------------------------------------emp2

empno    员工编号
ename    员工姓名
job      工作/工种
mgr      manager上级编号
hiredate 入职日期
sal      salary 工资
comm     奖金/津贴
deptno   部门编号
---------------------
dept2

deptno  部门号
dname   部门名称
loc     所在地
---------------------------salgrade2

grade                   等级
losal lowest salary     最低工资
hisal high salary       最高工资

=============================================


 
DDL语句:


create 创建表:

默认值
  createtable emp94(eid number(4) default1234,
                     ename varchar2(10) default‘abc‘,
                     birth date default to_date(‘2007-7-1‘,‘YYYY-MM-DD‘)
                    );
                     
  insertinto emp94(eid) values(1122);
  
  insertinto emp94 values(1122,null,null);
  
  
  


  
复制表 CTAS:


  createtable 表名 as 子查询语句;
  
  createtable db_emp2 asselect*from emp;
  
  createtable demp asselect*from emp e join dept d 
                       on e.deptno = d.deptno;
                       
      


alter 修改表:

  altertable emp94 add addr varchar2(40);               --新增列altertable emp94 add addr varchar2(40) default‘ZZZ‘; --同时设置默认值altertable emp94 modify addr char(10);                --修改列 类型和长度altertable emp94 modify birth default sysdate;         --修改列 默认值altertable emp94 modify eid defaultnull;              --清除默认值altertable emp94 rename column birth to birthday;      --修改列名altertable emp_94 dropcolumn birthday;                --删除列  
  rename emp94 to emp_94;                                 ---修改表名select*from emp_94;
  



drop 删除表:


 droptable 表名;
 
 droptable db_emp;
 



truncate 截断表:


 truncatetable 表名;
 
 truncatetable db_emp;     --delete from db_empselect*from db_emp;
 
 



 比较drop、truncate、delete1）drop和truncate属于DDL语句，执行结果自动提交/保存；
 
    delete属于DML语句，执行之后需要手动提交；在commit之前可以rollback;
    
 2）drop删除表中数据，同时删除表;
 
    truncate和delete只删除表中的数据;
    
 3）truncate只能删除整张表的数据；
 
    delete可以删除整张表的数据，也可以删除where条件指定的数据;
    
 4）执行速度：drop>truncate>delete;
 
 5）drop和truncate释放存储空间，delete不释放;
    




约束:


1）主键约束  primarykey，唯一性和非空性

2）唯一约束  unique，     值唯一，但是可以为空

3）非空约束  notnull，   值非空，但是可以重复

4）检查约束  check，      规定列的取值范围/值域

                  check(sal between6000and10000)
                  check(sex in(‘男‘,‘女‘,‘不详‘))
                  
5）外键约束  foreignkey，表与表之间的关系

select*from emp;

select*from dept;


insertinto emp(empno,deptno) values(4972,50);

insertinto dept(deptno) values(50);

deletefrom dept where deptno =50;



创建表的同时创建约束:


 createtable db_emp(sid number(4),
                     sname varchar2(10) notnull,
                     age number(3),
                     addr varchar2(40),
                     parents varchar(10),
                     classno number(2),
                     constraints pk_db_emp primarykey(sid),
                     constraints uk_db_emp unique(addr),
                     constraints ck_db_emp check(age between12and18),
                     constraints fk_db_emp foreignkey(classno) 
                                           references dept(deptno)
                      );
                      
 insertinto db_emp values(14,‘XX‘,18,‘NanJing‘,null,10)    
                 
 select*from db_emp2; 
 
 deletefrom db_emp where sid =13;
 
 


表已经存在，创建/删除、约束失效/生效:


 altertable db_emp drop constraints UK_DB_EMP;
 
 altertable db_emp add constraints uk_db_emp unique(addr);
 
     ---主键 外键 唯一 检查约束altertable db_emp modify sname null;
 
 altertable db_emp modify parents notnull;
 
 altertable db_emp enable constraints UK_DB_EMP;  --生效altertable db_emp disable constraints UK_DB_EMP; --失效