orcale 基本语句2

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
 
SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
 
14 rows selected
 
SQL> select sum(sal) sumsal, avg(sal) avgsal,count(*),count(sal) from emp;
 
    SUMSAL     AVGSAL   COUNT(*) COUNT(SAL)
---------- ---------- ---------- ----------
     29025 2073.21428         14         14
 
SQL> select sum(sal),avg(sal),min(sal),mix(sal),count(sal),count(*) from emp;
 
select sum(sal),avg(sal),min(sal),mix(sal),count(sal),count(*) from emp
 
ORA-00904: "MIX": 标识符无效
 
SQL> select sum(sal),avg(sal),min(sal),max(sal),count(sal),count(*)from emp;
 
  SUM(SAL)   AVG(SAL)   MIN(SAL)   MAX(SAL) COUNT(SAL)   COUNT(*)
---------- ---------- ---------- ---------- ---------- ----------
     29025 2073.21428        800       5000         14         14
 
SQL> select  * frpm  dept;
 
select  * frpm  dept
 
ORA-00923: 未找到要求的 FROM 关键字
 
SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> select deptno,sum(sal) from  emp ;
 
select deptno,sum(sal) from  emp
 
ORA-00937: 不是单组分组函数
 
SQL> select deptno ,sum(sal), from emp group by deptno;
 
select deptno ,sum(sal), from emp group by deptno
 
ORA-00936: 缺失表达式
 
SQL> select  deptno,sum(dal) from emp group by deptno;
 
select  deptno,sum(dal) from emp group by deptno
 
ORA-00904: "DAL": 标识符无效
 
SQL> select deptno,sum(sal) from emp group by deptno;
 
DEPTNO   SUM(SAL)
------ ----------
    30       9400
    20      10875
    10       8750
 
SQL> select deptno,sum(sal) from emp group by  deptno having sum(sal)>9000;
 
DEPTNO   SUM(SAL)
------ ----------
    30       9400
    20      10875
 
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select deptno,sum(sal),loc,count(*) from emp,dept  group by  deptno ,loc  having sum(sal)>9000;
 
select deptno,sum(sal),loc,count(*) from emp,dept  group by  deptno ,loc  having sum(sal)>9000
 
ORA-00918: 未明确定义列
 
SQL> select e.deptno,sum(sal),loc,count(*) from emp e,dept d where e.deptno=d.deptno  group by e.deptno,loc having sum(sal)>9000;
 
DEPTNO   SUM(SAL) LOC             COUNT(*)
------ ---------- ------------- ----------
    20      10875 DALLAS                 5
    30       9400 CHICAGO                6
 
SQL> slect  loc,e.deptno,sum(sal),count(loc),count(sal),count(*) from emp e,dept d where e.deptno=d.deptno group by loc,e.deptno having sum(sal)>1000;
 
slect  loc,e.deptno,sum(sal),count(loc),count(sal),count(*) from emp e,dept d where e.deptno=d.deptno group by loc,e.deptno having sum(sal)>1000
 
ORA-00900: 无效 SQL 语句
 
SQL> select  loc,e.deptno,sum(sal),count(loc),count(sal),count(*) from emp e,dept d where e.deptno=d.deptno group by loc,e.deptno having sum(sal)>1000;
 
LOC           DEPTNO   SUM(SAL) COUNT(LOC) COUNT(SAL)   COUNT(*)
------------- ------ ---------- ---------- ---------- ----------
CHICAGO           30       9400          6          6          6
DALLAS            20      10875          5          5          5
NEW YORK          10       8750          3          3          3
 
SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
 
14 rows selected
 
SQL> select deptno, ename,hiredate from emp where (deptno,hiredate) in (select (deptno ,max(hiredate) from emp group by demptno));
 
select deptno, ename,hiredate from emp where (deptno,hiredate) in (select (deptno ,max(hiredate) from emp group by demptno))
 
ORA-00920: 无效的关系运算符
 
SQL>
SQL> select sname,birthday,dno
  2    from  student
  3    where (dno , birthday) in  ( select dno ,max(birthday)
  4                                                from student
  5                                                group by dno ) ;
 
select sname,birthday,dno
  from  student
  where (dno , birthday) in  ( select dno ,max(birthday)
                                              from student
                                              group by dno )
 
ORA-00942: 表或视图不存在
 
SQL> select ename, deptno,hiredate  from  emp  where   (deptno,hiredate) in (select deptno,max(hiredate) from emp  group by deptno);
 
ENAME      DEPTNO HIREDATE
---------- ------ -----------
ADAMS          20 1987-5-23
JAMES          30 1981-12-3
MILLER         10 1982-1-23
 
SQL> round(68.247);
 
round(68.247)
 
ORA-00900: 无效 SQL 语句
 
SQL> select  round(68.247) from dual;
 
ROUND(68.247)
-------------
           68
 
SQL> select  round(68.247,-1) from dual;
 
ROUND(68.247,-1)
----------------
              70
 
SQL> select round(68.247,-2) from dual;
 
ROUND(68.247,-2)
----------------
             100
 
SQL> select round(68.247,-3) from dual;
 
ROUND(68.247,-3)
----------------
               0
 
SQL> select trunc(68.247,-1) from dual;
 
TRUNC(68.247,-1)
----------------
              60
 
SQL> select trunc(68.247,-2) from dual;
 
TRUNC(68.247,-2)
----------------
               0
 
SQL> select trunc(68.247,1) from dual;
 
TRUNC(68.247,1)
---------------
           68.2
 
SQL> select trunc(68.247,2) from dual;
 
TRUNC(68.247,2)
---------------
          68.24
 
SQL> select trunc(68.247,3) from dual;
 
TRUNC(68.247,3)
---------------
         68.247
 
SQL>  select  floor(68.247) from dual;
 
FLOOR(68.247)
-------------
           68
 
SQL> select * from emp where lower(ename)='smith';
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 
SQL> select * from emp where ename=upper('smith');
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 
SQL> select concat('long','xiang') from dual;
 
CONCAT('LONG','XIANG')
----------------------
longxiang
 
SQL> select substr('character',4,3) from dual;
 
SUBSTR('CHARACTER',4,3)
-----------------------
rac
 
SQL> select substr('characheter',0,5) from dual;
 
SUBSTR('CHARACHETER',0,5)
-------------------------
chara
 
SQL> select substr('charachter',1,5) from dual;
 
SUBSTR('CHARACHTER',1,5)
------------------------
chara
 
SQL> select substr('charachter',10,4) from dual;
 
SUBSTR('CHARACHTER',10,4)
-------------------------
r
 
SQL> select substr('charachter',11,3) from dual;
 
SUBSTR('CHARACHTER',11,3)
-------------------------
 
SQL> select length('character') from dual;
 
LENGTH('CHARACTER')
-------------------
                  9
 
SQL> select mod(234,100) from dual;
 
MOD(234,100)
------------
          34
 
SQL> select sysdate from dual;
 
SYSDATE
-----------
2013-6-1 下午
 
SQL> select sysdate+30 from dual;
 
SYSDATE+30
-----------
2013-7-1 下午
 
SQL> select system+2 from dual;
 
select system+2 from dual
 
ORA-00904: "SYSTEM": 标识符无效
 
SQL> select sysdate+2 from dual;
 
SYSDATE+2
-----------
2013-6-3 下午
 
SQL> select add_months(sysdate,18) from dual;
 
ADD_MONTHS(SYSDATE,18)
----------------------
2014-12-1 下午 03:02:08
 
SQL> select months_between(sysdate,add_months(sysdate,18)) from dual;
 
MONTHS_BETWEEN(SYSDATE,ADD_MON
------------------------------
                           -18
 
SQL> select next_day(sysdate) from dual;
 
select next_day(sysdate) from dual
 
ORA-00909: 参数个数无效
 
SQL> select next_day(sysdate,'星期六') from  dual;
 
NEXT_DAY(SYSDATE,'星期六')
--------------------------
2013-6-8 下午 03:06:09
 
SQL> select last_day(sysdate) from dual;
 
LAST_DAY(SYSDATE)
-----------------
2013-6-30 下午 03:0
 
SQL> select round(system,'month') from dual;
 
select round(system,'month') from dual
 
ORA-00904: "SYSTEM": 标识符无效
 
SQL> select round(sysdate,'month')from dual;
 
ROUND(SYSDATE,'MONTH')
----------------------
2013-6-1
 
SQL> select round(sysdate,'year') from dual;
 
ROUND(SYSDATE,'YEAR')
---------------------
2013-1-1
 
SQL> select round(sysdate,'day') from dual;
 
ROUND(SYSDATE,'DAY')
--------------------
2013-6-2
 
SQL> select  sysdate-trunc(sysdate) from dual;
 
SYSDATE-TRUNC(SYSDATE)
----------------------
     0.632731481481481
 
SQL> select To_char(sysdate,'yyyy-mm-dd,day') from dual;
 
TO_CHAR(SYSDATE,'YYYY-MM-DD,DA
------------------------------
2013-06-01,星期六
 
SQL> select to_char(sysdate+49,'yyyy-mm-dd,day') from dual;
 
TO_CHAR(SYSDATE+49,'YYYY-MM-DD
------------------------------
2013-07-20,星期六
 
SQL> select to_number('5021314') from dual;
 
TO_NUMBER('5021314')
--------------------
             5021314
 
SQL> select to_number('5201314lx') from dual;
 
select to_number('5201314lx') from dual
 
ORA-01722: 无效数字
 
SQL> select  to_date('1990-01-1','yyyy-mm-dd') from dual;
 
TO_DATE('1990-01-1','YYYY-MM-D
------------------------------
1990-1-1
 
SQL> select * from emp  where hiredate>to_date('1980-1-1','yyyy-mm-dd') from dual;
 
select * from emp  where hiredate>to_date('1980-1-1','yyyy-mm-dd') from dual
 
ORA-00933: SQL 命令未正确结束
 
SQL> select * from emp  where hiredate>to_date('1980-1-1','yyyy-mm-dd');
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
 
14 rows selected
 
SQL> select * from  emp  where  hiredate>to_date('1985-1-1','yyyyy-mm-dd');
 
select * from  emp  where  hiredate>to_date('1985-1-1','yyyyy-mm-dd')
 
ORA-01812: 只能指定一次年份
 
SQL> select * from  emp  where  hiredate>to_date('1985-1-1','yyyy-mm-dd');
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 
SQL> select ename, sal,comm from emp;
 
ENAME            SAL      COMM
---------- --------- ---------
SMITH         800.00
ALLEN        1600.00    300.00
WARD         1250.00    500.00
JONES        2975.00
MARTIN       1250.00   1400.00
BLAKE        2850.00
CLARK        2450.00
SCOTT        3000.00
KING         5000.00
TURNER       1500.00      0.00
ADAMS        1100.00
JAMES         950.00
FORD         3000.00
MILLER       1300.00
 
14 rows selected
 
SQL> select ename sal+comm from emp;
 
select ename sal+comm from emp
 
ORA-00923: 未找到要求的 FROM 关键字
 
SQL> select ename.sal+comm from emp;
 
select ename.sal+comm from emp
 
ORA-00904: "ENAME"."SAL": 标识符无效
 
SQL> select ename ,sal+comm from emp;
 
ENAME        SAL+COMM
---------- ----------
SMITH     
ALLEN            1900
WARD             1750
JONES     
MARTIN           2650
BLAKE     
CLARK     
SCOTT     
KING      
TURNER           1500
ADAMS     
JAMES     
FORD      
MILLER    
 
14 rows selected
 
SQL> select ename,nvl(sal+comm) from emp;
 
select ename,nvl(sal+comm) from emp
 
ORA-00909: 参数个数无效
 
SQL> select ename,nvl(sal)+nvl(comm) from emp;
 
select ename,nvl(sal)+nvl(comm) from emp
 
ORA-00909: 参数个数无效
 
SQL> select ename,nvl(sal+comm,0) from dual;
 
select ename,nvl(sal+comm,0) from dual
 
ORA-00904: "COMM": 标识符无效
 
SQL> select ename,nvl(sal,0)+nvl(comm,0) from dual;
 
select ename,nvl(sal,0)+nvl(comm,0) from dual
 
ORA-00904: "COMM": 标识符无效
 
SQL> select ename,nvl(sal,0)+nvl(comm,0)from emp;
 
ENAME      NVL(SAL,0)+NVL(COMM,0)
---------- ----------------------
SMITH                         800
ALLEN                        1900
WARD                         1750
JONES                        2975
MARTIN                       2650
BLAKE                        2850
CLARK                        2450
SCOTT                        3000
KING                         5000
TURNER                       1500
ADAMS                        1100
JAMES                         950
FORD                         3000
MILLER                       1300
 
14 rows selected
 
SQL> select ename,nvl(sal+comm,0) from emp;
 
ENAME      NVL(SAL+COMM,0)
---------- ---------------
SMITH                    0
ALLEN                 1900
WARD                  1750
JONES                    0
MARTIN                2650
BLAKE                    0
CLARK                    0
SCOTT                    0
KING                     0
TURNER                1500
ADAMS                    0
JAMES                    0
FORD                     0
MILLER                   0
 
14 rows selected
 
SQL> select ename,vnl2(sal,sal,0)+nvl2(comm,comm,0) from emp;
 
select ename,vnl2(sal,sal,0)+nvl2(comm,comm,0) from emp
 
ORA-00904: "VNL2": 标识符无效
 
SQL> select ename,nvl2(sal,sal,0)+nvl(comm,comm,0) from emp;
 
select ename,nvl2(sal,sal,0)+nvl(comm,comm,0) from emp
 
ORA-00909: 参数个数无效
 
SQL> select ename,nvl2(sal,sal,0)+nvl2(comm,comm,0) from emp;
 
ENAME      NVL2(SAL,SAL,0)+NVL2(COMM,COMM
---------- ------------------------------
SMITH                                 800
ALLEN                                1900
WARD                                 1750
JONES                                2975
MARTIN                               2650
BLAKE                                2850
CLARK                                2450
SCOTT                                3000
KING                                 5000
TURNER                               1500
ADAMS                                1100
JAMES                                 950
FORD                                 3000
MILLER                               1300
 
14 rows selected
 
SQL> select * from emp where deptno=30;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 
6 rows selected
 
SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> select * from bonus;
 
ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
 
SQL> select * from salgrade;
 
     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999
 
SQL> select ename,deptno,job ,sal where  lower(job)='clerk';
 
select ename,deptno,job ,sal where  lower(job)='clerk'
 
ORA-00923: 未找到要求的 FROM 关键字
 
SQL> select ename ,deptno,job,sal from emp where lower(job)='clerk';
 
ENAME      DEPTNO JOB             SAL
---------- ------ --------- ---------
SMITH          20 CLERK        800.00
ADAMS          20 CLERK       1100.00
JAMES          30 CLERK        950.00
MILLER         10 CLERK       1300.00
 
SQL>