oracle——笔记——DDL语句

我就是我 2023-10-02 08:13 20阅读 0赞

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) 
                     );
    -----------------------------------------------------------------------------------------------------------
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    insert into 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);
    
    =============================================================================================================================
    
    create table dept2 ( deptno number(2),
                         dname varchar2(14),
                         loc varchar2(13)
                       );
    
    ----------------------------------------------------
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (10, 'ACCOUNTING', 'NEW YORK');
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (20, 'RESEARCH', 'DALLAS');
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (30, 'SALES', 'CHICAGO');
    
    insert into dept2 (DEPTNO, DNAME, LOC)
    values (40, 'OPERATIONS', 'BOSTON');
    
    ============================================================
    
    create table salgrade2 ( grade number,
                             losal number,
                             hisal number
                           );
    
    --------------------------------------------------
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (1, 700, 1200);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (2, 1201, 1400);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (3, 1401, 2000);
    
    insert into salgrade2 (GRADE, LOSAL, HISAL)
    values (4, 2001, 3000);
    
    insert into 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 创建表:
    
    默认值
      create table emp94(eid number(4) default 1234,
                         ename varchar2(10) default 'abc',
                         birth date default to_date('2007-7-1','YYYY-MM-DD')
                        );
                         
      insert into emp94(eid) values(1122);
      
      insert into emp94 values(1122,null,null);
      
      
      
    
    
    
    
      
    复制表 CTAS:
    
    
      create table 表名 as 子查询语句;
      
      create table db_emp2 as select * from emp;
      
      create table demp as select * 
                           from emp e join dept d 
                           on e.deptno = d.deptno;
                           
          
    
    
    
    
    
                     
                           
    alter 修改表:
    
      alter table emp94 add addr varchar2(40);               --新增列
      
      alter table emp94 add addr varchar2(40) default 'ZZZ'; --同时设置默认值
      
      alter table emp94 modify addr char(10);                --修改列 类型和长度
      
      alter table emp94 modify birth default sysdate;         --修改列 默认值
      
      alter table emp94 modify eid default null;              --清除默认值
      
      alter table emp94 rename column birth to birthday;      --修改列名
      
      alter table emp_94 drop column birthday;                --删除列
      
      rename emp94 to emp_94;                                 ---修改表名
      
      select * from emp_94;
      
    
    
    
    
    
    
    
    drop 删除表:
    
    
     drop table 表名;
     
     drop table db_emp;
     
    
    
    
    
    
    
    
    truncate 截断表:
    
    
     truncate table 表名;
     
     truncate table db_emp;     --delete from db_emp
     
     select * from db_emp;
     
     
    
    
    
    
    
    
     比较drop、truncate、delete
     
     1)drop和truncate属于DDL语句，执行结果自动提交/保存；
     
        delete属于DML语句，执行之后需要手动提交；在commit之前可以rollback;
        
     2)drop删除表中数据，同时删除表;
     
        truncate和delete只删除表中的数据;
        
     3)truncate只能删除整张表的数据；
     
        delete可以删除整张表的数据，也可以删除where条件指定的数据;
        
     4)执行速度：drop > truncate > delete;
     
     5)drop和truncate释放存储空间，delete不释放;
        
    
    
    
    
    
    
    
    
    约束:
    
    
    1)主键约束  primary key，唯一性和非空性
    
    2)唯一约束  unique，     值唯一，但是可以为空
    
    3)非空约束  not null，   值非空，但是可以重复
    
    4)检查约束  check，      规定列的取值范围/值域
    
                      check(sal between 6000 and 10000)
                      check(sex in('男','女','不详'))
                      
    5)外键约束  foreign key，表与表之间的关系
    
    select * from emp;
    
    select * from dept;
    
    
    insert into emp(empno,deptno) values(4972,50);
    
    insert into dept(deptno) values(50);
    
    delete from dept where deptno = 50;
    
    
    
    
    创建表的同时创建约束:
    
    
     create table db_emp(sid number(4),
                         sname varchar2(10) not null,
                         age number(3),
                         addr varchar2(40),
                         parents varchar(10),
                         classno number(2),
                         constraints pk_db_emp primary key(sid),
                         constraints uk_db_emp unique(addr),
                         constraints ck_db_emp check(age between 12 and 18),
                         constraints fk_db_emp foreign key(classno) 
                                               references dept(deptno)
                          );
                          
     insert into db_emp values(14,'XX',18,'NanJing',null,10)    
                     
     select * from db_emp2; 
     
     delete from db_emp where sid = 13;
     
     
    
    
    
    
    表已经存在，创建/删除、约束失效/生效:
    
    
     alter table db_emp drop constraints UK_DB_EMP;
     
     alter table db_emp add constraints uk_db_emp unique(addr);
     
         ---主键 外键 唯一 检查约束
    
     alter table db_emp modify sname null;
     
     alter table db_emp modify parents not null;
     
     alter table db_emp enable constraints UK_DB_EMP;  --生效
     
     alter table db_emp disable constraints UK_DB_EMP; --失效