oracle——笔记——简单查询语句

拼搏现实的明天。 2023-10-02 08:14 18阅读 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       最高工资
    
    =============================================
    
    
    数据查询语句:
    
    select
    from
    where
    
    
    
    
    
    
    
    查询emp2表中，所有员工的姓名、工资、部门号:
    
    select ename,sal,deptno from emp2;
     
    
    
    
    
    
    查询emp2表中，工资大于1500 的员工信息:
    
    select * from emp2 where sal > 1500;
    
    
    
    
    
    
     
    查询emp2表中，上级是7698 的员工姓名和津贴:
    
    select ename,comm from emp2 where mgr = 7698;
    
    
    
    
    
    
    
    列的别名:
    
    select ename,sal,deptno from emp2;
    
    select ename 姓名,sal 工资,deptno 部门号 from emp2;
    
    select ename as 姓名,sal as 工资,deptno as 部门号 from emp2;
    
    select ename as e,sal as s,deptno as d from emp2;
    
    select ename as "Esc",sal as "alS",deptno as "dDD" from emp2;
    
    select ename as "e%",sal as "s#",deptno as "d()" from emp2;
    
    
    
    
    
    
    ==============================================================================================================
    
    
    
    算术运算 + - * /  ()
    
    支持数值型和日期型(只能加减)数据:
    
    
    
    
    
    
    查询每个员工的年薪:
    
    select ename,sal,sal*12 from emp2;
    
    
    
    
    
     
    给20部门员工，涨薪300之后，工资是多少？:
     
    select ename,sal,sal+300 from emp2 where deptno = 20;
    
    
    
    
    
    
     
    查询每名员工的 工资奖金和 :
     
    select sal,comm,sal+comm from emp2;
    
    
    
    
    
    
    
    运算中如果有空值，那么最后结果为空
    
    --空值替换 nvl()
    
    select sal,comm,nvl(comm,0),sal+nvl(comm,0) from emp2;
    
    select * from emp2;
      
    insert into emp2(empno) values(1122);
    
    delete from emp2 where empno = 1122;
    
    select ename,nvl(ename,'未知') from emp2;
    
    
    
    
    
    
    
    
    
    连接符 :
    
    select ename,sal from emp2;
    
    select ename||'的工资是'||sal||'，部门是'||deptno from emp2;
      
    
    
    
    
    
     
    去重
    
    
    
    查询emp中，有多少个部门:
    
    select distinct deptno from emp2; 
    
    
    
    
    
    
    查询emp中，有多少种工作
    
    select distinct job from emp2;
      
    
    
    
    
    
    
    
    
    
    
    where
    比较运算符
    
    一般的比较运算符 > >= <= < = != <>
    
    特殊的比较运算符
    
       between……and……
       
       in(……)
       
       like ……  像  模糊查询
       
       is null    空值 
       
    
    
    
    
    
    select * from emp2 where comm is null;
    
    
    
    
    select * from emp2 where comm is not null;
       
       
    
    
    
    
    
    
    
    
         
    查询工资在1500 到 3000 的员工信息:
    
    select * from emp2  where sal between 1500 and 3000;  --闭区间
                                          下限     上限 
    
        
    select * from emp2 where sal >= 1500 and sal <= 3000;
       
    
    
    
    
    
    
    
    查询从事CLERK或SALESMAN 的员工信息:
    
    1)字符串
    2)关于大小写
        
    select * from emp2 where job in('CLERK','SALESMAN');
       
    select * from emp2 where job = 'CLERK' or job = 'SALESMAN' ;
       
    select * from emp2 where sal in(1500,3000);
    
    select * from emp2 where sal = 1500 or sal = 3000;
      
    
    
    
    
    
    
    
    
    
    like
    
    select * from emp2 where ename like 'S%';
    
    
    
    
    
    
    
    
    %
    sql里的% 等同于linux的* ，代表零个或多个任意字符
     _ 代表一个任意字符
    
    select * from emp2 where ename like 'S_ITH';
      
    select t.*,t.rowid from emp2 t;
      
    select emp2.*,rowid from emp2;
      
    
    
    
    查询以S%开头的员工信息:
    
    select * from emp2 where ename like 'S\%%' escape '\';       escape '\':定义\这个符号后面符号位普通字符串，不是系统里默认的符号。即转义
    
    select * from emp2 where ename like 'S|%%' escape '|';
      
    select * from emp2  where ename like 'S%\%' escape '\';
    
    
    
    
    escape：
    
    用来转译的，比如数据库中有个表 test字段name 中存了字符'%'，那么我查询的时候如果想查询第二位是 '%'的记录就要用到模糊查询，
    
    
    
    但是'%'是模糊查询的通配符，我不能如下查询：
    
    select * from test where name like '_%%';
    
    
    
    
    这样系统不会把第二个【%】当做字符，只能转译，转译默认的符号是 【\】
    
    select * from test where name like '_\%%';
    
    
    
    
    但是可以自定义转义符，这时候就用 escape，比如定义【*】为转义符
    
    select * from test where name like '_*%%' escape '*';
    
    
    
    
    
    
    -----------------------------------------------------------------------------------------------------
    
    
    
    
    
    
    
    逻辑运算符 and or not ()
    
    运算优先级：not > and > or ,()优先级最高
    
    
    
    
    查询，20 部门中，从事CLERK 工作的员工:
      
    select * from emp2 where deptno = 20 and job = 'CLERK';
    
    
    
    
    查询，30 部门中，奖金为空的员工信息:
      
    select * from emp2  where deptno = 30 and comm is null;
    
    
    
      
    查询，除10 部门之外，工资大于1500 的员工信息:
      
    select * from emp2 where deptno != 10 and sal > 1500;
    
    
    
      
    查询，工作是SALESMAN ，或工资不小于3000 的员工:
      
    select * from emp2 where job = 'SALESMAN' or sal >= 3000;
    
    
      
    查询，工作不是SALESMAN ，也不是CLERK 的员工:
      
    select * from emp2 where job != 'SALESMAN' and job != 'CLERK';
      
    select * from emp2 where job in('SALESMAN','CLERK');
      
    select * from emp2 where job not in('SALESMAN','CLERK');
    
    
    
      
    查询，工作是SALESMAN，或，工作是PRESIDENT并且工资大于1500的员工信息:
      
    select * from emp2 where job = 'SALESMAN' or job = 'PRESIDENT' and sal > 1500;
    
    
    
    
    
    查询，工作是SALESMAN或PRESIDNET，并且工资大于1500 的员工信息:
    
    select * from emp2 where (job = 'SALESMAN' or job = 'PRESIDENT') and sal > 1500;
    
    select * from emp2 where job in('SALESMAN','PRESIDENT') and sal > 1500;
      
    
    
    
    
    
    
    
    
    
    
    
    order by 排序 
    
    select
    from
    where
    order by 列名|别名|算术表达式|函数
        
    order by 列1,别名,函数
    
    order by 列1,列2 desc;
    
    order by的位置：在整个查询语句的最后多次排序 
    
    
    
    
    
    
    
    select * from emp2 order by sal;  --升序
    
    select * from emp2 order by sal asc;  --升序
    
    select * from emp2 order by sal desc; --降序
     
    select ename 姓名,job 工作 from emp2 where deptno = 20 order by 姓名 desc;
     
    
    
    
    
    查询员工信息，结果按照工资奖金和 升序排序:
    
    select emp2.*,sal+nvl(comm,0) from emp2 order by sal+nvl(comm,0);
     
    
    
    
    查询员工信息，结果按照部门号排序;如果部门号相同，按照工资降序排序
    
    select * from emp2 order by deptno asc,sal desc;
    
    
    
    
    
    
    
    
    函数
     
    单组函数          一个值对应一个结果 
    
    分组函数/聚合函数 多个值对应一个结果  avg() sum()
    
    单组函数
      --字符函数
      UPPER()   LOWER()   INITCAP()  ---字母大小写
      大写       小写    首字母大写
      
      replace()      substr()    concat()  nvl() nvl2()
        替换           截取       连接
      
    
    
    
    select ename,lower(ename),initcap(ename) from emp2;
    
    select t.*,t.rowid from emp2 t;
    
    select * from emp2 where lower(job) = 'clerk';
      
    select ename,replace(ename,'S','s') from emp2;
    
    select ename,substr(ename,2,3) from emp2;
                           起始位,长度
      
    
    
    
    查询工作名称以SALES 开头的员工信息:
    
    select * from emp2 where job like 'SALES%';
    
    select * from emp2 where substr(job,1,5) = 'SALES';
      
    select ename||'的工资是'||sal||deptno from emp2;
    
    
    select concat(ename,sal) from emp2;
    
    select concat(concat(ename,'的工资是'),sal) from emp2;
    
    
    
    
    
    
    
    
    
    函数是可以嵌套的
    
    select substr(concat(ename,'的工资是'),5,5) from emp2;
      
    select comm,nvl(comm,0),nvl2(comm,1000,0) from emp2;
                                     非空替换,空值替换  
                                     
           
    
    
    
    
    
    
                              
    数值函数
    
       round(x[,y])  取整或保留指定小数位，规则：四舍五入
       trunc(x[,y])  取整或保留指定小数位，规则：截断
       mod(x,y) 取模/取余
       
       round(5.72) = 6
       round(5.718,2) = 5.72
       round(04.718,-1) = 0
       
       trunc(5.72) = 5
       trunc(5.718,2) = 5.71
       trunc(05.718,-1) = 0
       
       select round(5.72), round(5.718,2),round(5.718,-1) from dual;
       select trunc(5.718,2),mod(8,4),mod(10,3) from dual;
     
    
      
    dual表
       
    作用：语句补全
       
       select * from dual;
       
       select sysdate from dual;
       
       select 12*15,round(1000/23,2) from dual;
       
       select Sys_Context('userenv','db_name') from dual;--查看当前数据库 名
       
       select Dbms_Random.random from dual;--获得一个随机数
       
       select Dbms_Random.value(10,20) from dual;--获得一个随机数
       
    
    
    
    
    
    
    
    
    
    
    
    
    日期函数
    
    使用insert，新增一条记录：
    员工编号：1122
    员工工作：SALESMAN
    入职日期：2018年7月1号
    
       insert into 表名(列名) values(列对应的值);
       
       insert into emp(empno,job,hiredate) 
       values(1122,'SALESMAN','01-7月-18');
       
       insert into emp(empno,job,hiredate) 
       values(1122,'SALESMAN','2018-7-1');   
       
       select * from emp;
       
       --1)默认日期格式
       
        select * from nls_session_parameters;
        alter session set NLS_DATE_FORMAT='YYYY-MM-DD';
        alter session set NLS_DATE_FORMAT='DD-MON-RR';
       
       --2) 日期函数 to_date(日期，格式)
       
       insert into emp(empno,job,hiredate) 
       values(1123,'SALESMAN',to_date('10-1-2018','MM-DD-YYYY')); 
       
        --查询入职日期早于1981年9 月30 号的员工信息
        
        select
        from
        where hiredate < to_date()