Oracle数据库基础（一）

桃扇骨 2022-03-12 05:52 379阅读 0赞

先数据库引入：引入基本数据

prompt PL/SQL Developer import file
    set feedback off
    set define off
    prompt Creating BONUS...
    create table BONUS
    (
      ename VARCHAR2(10),
      job   VARCHAR2(9),
      sal   NUMBER,
      comm  NUMBER
    )
    tablespace USERS
      pctfree 10
      initrans 1
      maxtrans 255;
    
    prompt Creating DEPT...
    create table DEPT
    (
      deptno NUMBER(2) not null,
      dname  VARCHAR2(14),
      loc    VARCHAR2(13)
    )
    tablespace USERS
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );
    alter table DEPT
      add constraint PK_DEPT primary key (DEPTNO)
      using index 
      tablespace USERS
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );
    
    prompt Creating EMP...
    create table EMP
    (
      empno    NUMBER(4) not null,
      ename    VARCHAR2(10),
      job      VARCHAR2(9),
      mgr      NUMBER(4),
      hiredate DATE,
      sal      NUMBER(7,2),
      comm     NUMBER(7,2),
      deptno   NUMBER(2)
    )
    tablespace USERS
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );
    alter table EMP
      add constraint PK_EMP primary key (EMPNO)
      using index 
      tablespace USERS
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );
    alter table EMP
      add constraint FK_DEPTNO foreign key (DEPTNO)
      references DEPT (DEPTNO);
    
    prompt Creating SALGRADE...
    create table SALGRADE
    (
      grade NUMBER,
      losal NUMBER,
      hisal NUMBER
    )
    tablespace USERS
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );
    
    prompt Disabling triggers for BONUS...
    alter table BONUS disable all triggers;
    prompt Disabling triggers for DEPT...
    alter table DEPT disable all triggers;
    prompt Disabling triggers for EMP...
    alter table EMP disable all triggers;
    prompt Disabling triggers for SALGRADE...
    alter table SALGRADE disable all triggers;
    prompt Disabling foreign key constraints for EMP...
    alter table EMP disable constraint FK_DEPTNO;
    prompt Loading BONUS...
    prompt Table is empty
    prompt Loading DEPT...
    insert into DEPT (deptno, dname, loc)
    values (10, 'ACCOUNTING', 'NEW YORK');
    insert into DEPT (deptno, dname, loc)
    values (20, 'RESEARCH', 'DALLAS');
    insert into DEPT (deptno, dname, loc)
    values (30, 'SALES', 'CHICAGO');
    insert into DEPT (deptno, dname, loc)
    values (40, 'OPERATIONS', 'BOSTON');
    commit;
    prompt 4 records loaded
    prompt Loading EMP...
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
    insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
    commit;
    prompt 14 records loaded
    prompt Loading SALGRADE...
    insert into SALGRADE (grade, losal, hisal)
    values (1, 700, 1200);
    insert into SALGRADE (grade, losal, hisal)
    values (2, 1201, 1400);
    insert into SALGRADE (grade, losal, hisal)
    values (3, 1401, 2000);
    insert into SALGRADE (grade, losal, hisal)
    values (4, 2001, 3000);
    insert into SALGRADE (grade, losal, hisal)
    values (5, 3001, 9999);
    commit;
    prompt 5 records loaded
    prompt Enabling foreign key constraints for EMP...
    alter table EMP enable constraint FK_DEPTNO;
    prompt Enabling triggers for BONUS...
    alter table BONUS enable all triggers;
    prompt Enabling triggers for DEPT...
    alter table DEPT enable all triggers;
    prompt Enabling triggers for EMP...
    alter table EMP enable all triggers;
    prompt Enabling triggers for SALGRADE...
    alter table SALGRADE enable all triggers;
    set feedback on
    set define on
    prompt Done.

\-------------------------------------------  
\--选择所有列  
\-------------------------------------------

1、\*表示所有列（选择）：

select * from emp;

2.指定所有列的具体的名称：

select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp; --效率高

\-------------------------------------------  
\--选择指定列  
\-------------------------------------------

3.查询指定列(投影)

select empno,ename,job from emp;

\-------------------------------------------  
\--使用算数运算符  
\--      null 表示值不存在，或值无效，不同于0，也不同于''  
\--                   null值与任意值计算都为null  
\--  
\--       nvl(参数一，参数二)：当参数一为null时，取参数二的值，否则默认取参数一  
\-------------------------------------------

--1.统计所有雇员的年薪
    select empno,ename,sal,sal*12 from emp;
    
    --2.统计所有雇员的年薪（假设试用期6个月，试用工资为转正工资的80%，包括奖金）
    select empno, ename, sal, comm, (sal * 6) + (sal * 6 * 1.2) + (comm) from emp; --comm有问题，当值为null时，获取的结果也为null
    select empno,
           ename,
           sal,
           comm,
           ((sal * 6) + (sal * 6 * 1.2) + (nvl(comm, 0))) --使用nvl()函数处理计算过程中的null值
      from emp;

\-------------------------------------------  
\--取别名  
\--    使用列别名的方法：  
\--           1.列名 别名  
\--           2.列名 as 别名

\--    以下三种情况列别名两侧需要添加双引号：  
\--           1.列别名中包含有空格  
\--           2.列别名中要求区分大小写  
\--           3.列别名中包含有特殊字符  
\-------------------------------------------

--1.统计所有雇员的年薪,且年薪使用别名yearSalary表示（假设试用期6个月，试用工资为转正工资的80%，包-括奖金）
    select empno,
           ename,
           sal,
           comm,
           ((sal * 6) + (sal * 6 * 1.2) + (nvl(comm, 0))) yearSalary
      from emp;

--列别名使用两种方法
    select empno 编号,   --列名 别名
           ename as 姓名 --列名 as 别名
    from emp;
    
    --注意
    select empno,ename,sal * 12 "year salary" from emp;--列别名中包含有空格
    select empno,ename,sal * 12 "yearSalary" from emp; --列别名中要求区分大小写
    select empno,ename,sal * 12 "year'salary" from emp;--列别名中包含有特殊字符

\-----------------------------------  
\--连接操作符  
\--原义字符串：使用单引号''括起来，并出现在每行数据中  
\-----------------------------------

--1.查询雇员的姓名与职位，并以“XXX的职位是XXX”格式显示
    select ename,job,(ename || '的职位是' || job) from emp;

\-----------------------------------  
\--消除重复行 : distinct  
\-----------------------------------

--1.查询所有有员工的部门编号信息
    select distinct deptno from emp;--单列去重
    
    --2.查询所有有员工的部门编号，以及岗位信息
    select distinct deptno,job from emp;--多列重复

\-----------------------------------  
\--descripbe名称，查看表结构（SQL\*PLUS命令）  
\-----------------------------------

--1.查看emp表的结构
    desc emp;--仅允许在命令窗口执行