初学Oracle_单条selcet语句

最新推荐文章于 2024-11-13 10:59:25 发布
Miarcle_Jarvis
最新推荐文章于 2024-11-13 10:59:25 发布
阅读量254
点赞数
分类专栏： Oracle 文章标签： oracle
本文链接：https://blog.csdn.net/qq_32634545/article/details/54586472
版权
Oracle 专栏收录该内容
2 篇文章 0 订阅
订阅专栏
SQL> desc emp;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select *from emp;


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20


      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30


      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30




     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20


      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
        30


      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
        30




     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
        10


      7788 SCOTT      ANALYST         7566 19-4月 -87           3000
        20


      7839 KING       PRESIDENT            17-11月-81           5000
        10




     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30


      7876 ADAMS      CLERK           7788 23-5月 -87           1100
        20


      7900 JAMES      CLERK           7698 03-12月-81            950
        30




     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-12月-81           3000
        20


      7934 MILLER     CLERK           7782 23-1月 -82           1300


SQL> select ename||'LinkISing' from emp；
字符串连接，字符串中有单引号的时候用''表示'


ENAME||'LINKISING'
-------------------
SMITHLinkISing
ALLENLinkISing
WARDLinkISing
JONESLinkISing
MARTINLinkISing
BLAKELinkISing
CLARKLinkISing
SCOTTLinkISing
KINGLinkISing
TURNERLinkISing
ADAMSLinkISing

ENAME||'LINKISING'

SQL> select distinct deptno from emp;
distinct 关键字数表示去除重复


    DEPTNO
----------
        30
        20
        10


SQL> select distinct deptno,job from emp;
distinct连接两个内容的时候，表示去掉两个内容都相同的值


    DEPTNO JOB
---------- ---------
        20 CLERK
        30 SALESMAN
        20 MANAGER
        30 CLERK
        10 PRESIDENT
        30 MANAGER
        10 CLERK
        10 MANAGER
        20 ANALYST

SQL> select ename,sal from emp where sal>1500;
where关键字后跟过滤条件，上段代码表示选择出薪水值大于1500的人员名字
略;

SQL> select ename,sal,deptno from emp where deptno<>10;
上段代码表示取出deptno不等于10的人员名字薪水
Oracle里用<>表示不等于


ENAME             SAL     DEPTNO
---------- ---------- ----------
SMITH             800         20
ALLEN            1600         30
WARD             1250         30
JONES            2975         20
MARTIN           1250         30
BLAKE            2850         30
SCOTT            3000         20
TURNER           1500         30
ADAMS            1100         20
JAMES             950         30
FORD             3000         20

ENAME             SAL       COMM
---------- ---------- ----------
ALLEN            1600        300
WARD             1250        500
MARTIN           1250       1400
TURNER           1500          0

SQL> select ename,sal,comm from emp where sal in (800,1500,2000);
表示取出薪水值是800,1500,2000人员的名字，in表示是
略;


SQL> select ename,sal,comm from emp where ename in ('SMITH','KING');
in也可以用来选取字符串，如上段代码


ENAME             SAL       COMM
---------- ---------- ----------
SMITH             800
KING             5000



SQL> select ename,sal from emp where deptno = 10 and sal >1000;
where也可以用于两个过滤条件，用and连接


ENAME             SAL
---------- ----------
CLARK            2450
KING             5000
MILLER           1300


SQL> select ename,sal from emp where sal not in (800,1500);
not in表示不在800,1500区域的值

ENAME             SAL
---------- ----------
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
ADAMS            1100
JAMES             950
FORD             3000


ENAME             SAL
---------- ----------
MILLER           1300


SQL> select ename from emp where ename like '%ALL%';
表示选取名字中有ALL的人
ENAME
----------
ALLEN


SQL> select ename from emp where ename like '_A%';
表示选取第二个字母是A的人名

SQL> select ename from emp where ename like '%$%%' escape '$';
当人名中有%的话使用转义字符区分，escape表示用$作为转义字符 ，Oracle默认转义字符是\

SQL> select empno,ename from emp order by empno asc;
order by 关键字用于排序，默认是升序排序
使用order by xxx desc表示降序排序


     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS


     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

SQL> select ename,sal,deptno from emp order by deptno asc,sal desc;
上段代码表示deptno按照升序排序，当deptno内部相同时，sal升序排序


ENAME             SAL     DEPTNO
---------- ---------- ----------
KING             5000         10
CLARK            2450         10
MILLER           1300         10
SCOTT            3000         20
FORD             3000         20
JONES            2975         20
ADAMS            1100         20
SMITH             800         20
BLAKE            2850         30
ALLEN            1600         30
TURNER           1500         30


ENAME             SAL     DEPTNO
---------- ---------- ----------
MARTIN           1250         30
WARD             1250         30
JAMES             950         



SQL> select substr(ename,2,3) from emp;
截取字符

SQL> select chr(65) from dual;
将65转换成ASCLL码表中的值

SQL> select ascii('a') from dual;
把ASCLL码表中的a转化成数字


SQL> select round(23.456,2) from dual;
四舍五入到小数点后两位


SQL> select round(23.456,-1) from dual;
四舍五入到第十位

SQL> select to_char(sal,'$99,999.9999') from emp;
装换格式，数字用数字9的话，当数字前面没有值不显示
数字用数字0的话，当数字签名没有值的时候显示0

TO_CHAR(SAL,'
-------------
    $800.0000
  $1,600.0000
  $1,250.0000
  $2,975.0000
  $1,250.0000
  $2,850.0000
  $2,450.0000
  $3,000.0000
  $5,000.0000
  $1,500.0000
  $1,100.0000


TO_CHAR(SAL,'
-------------
    $950.0000
  $3,000.0000
  $1,300.0000
SQL> select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
把emp中的日期转换成YYYY-MM-DD HH:MI:SS的格式


TO_CHAR(HIREDATE,'Y
-------------------
1980-12-17 12:00:00
1981-02-20 12:00:00
1981-02-22 12:00:00
1981-04-02 12:00:00
1981-09-28 12:00:00
1981-05-01 12:00:00
1981-06-09 12:00:00
1987-04-19 12:00:00
1981-11-17 12:00:00
1981-09-08 12:00:00
1987-05-23 12:00:00

TO_CHAR(HIREDATE,'Y
-------------------
1981-12-03 12:00:00
1981-12-03 12:00:00
1982-01-23 12:00:00



SQL> select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
大于那个时间的选出来

ENAME      HIREDATE
---------- --------------
WARD       22-2月 -81
JONES      02-4月 -81
MARTIN     28-9月 -81
BLAKE      01-5月 -81
CLARK      09-6月 -81
SCOTT      19-4月 -87
KING       17-11月-81
TURNER     08-9月 -81
ADAMS      23-5月 -87
JAMES      03-12月-81
FORD       03-12月-81


ENAME      HIREDATE
---------- --------------
MILLER     23-1月 -82




SQL> select sal from emp where sal >to_number('$1,250.00','$9,999.99');
选出大于1250的人员并且按照$9,999.99格式


       SAL
----------
      1600
      2975
      2850
      2450
      3000
      5000
      1500  
      3000
      1300
SQL> select ename,sal*12*nvl(comm,0)from emp;
nvl函数表示若ename中的值是空值，用0代替

SQL> select count(*) from emp;
一个有多少在emp中

  COUNT(*)
----------
        14

SQL> select deptno,avg(sal) from emp group by deptno;
取出平均值按照deptno分组

    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667
        20       2175
        10 2916.66667


SQL>  select deptn,job,max(sal) from emp group by deptno,job;
取出最大值按deptno和job分组

    DEPTNO JOB         MAX(SAL)
---------- --------- ----------
        20 CLERK           1100
        30 SALESMAN        1600
        20 MANAGER         2975
        30 CLERK            950
        10 PRESIDENT       5000
        30 MANAGER         2850
        10 CLERK           1300
        10 MANAGER         2450
        20 ANALYST         3000
SQL> select deptno,max(sal) from emp group by deptno;
出现在select的字段没有出现在组函数里必须出现在group by里

    DEPTNO   MAX(SAL)
---------- ----------
        30       2850
        20       3000
        10       5000
SQL>  select avg(sal),deptno from emp group by deptno having avg(sal)>2000;
having是对分组后的语句进行筛选
where是对单条语句进行过滤筛选

  AVG(SAL)     DEPTNO
---------- ----------
      2175         20
2916.66667         10
SQL> select avg(sal)
  2  from emp
  3  where sal>1200
  4  group by deptno
  5  having avg(sal)>1500
  6  order by avg(sal) desc;


  AVG(SAL)
----------
2991.66667
2916.66667
      1690