RUI个人博客 首页>>Oracle Basic Knowledge>>[杂] sql相关笔记整理汇总,杂 1~

[杂] sql相关笔记整理汇总,杂 1~

本文是我在最开始学习oracle时候的笔记,现整合汇总到一起进行记录一下。

1. 排序显示空行的问题,

2.常用的单行函数

3. 不常见类型的小实验 --日期显示、显示异常、货币问题   时间yy解析 rr解析

4. SQL语句编译顺序:

5.Merge操作 (相当于update 和 insert操作二合一)

6. 视图相关内容  练习和理解

with check option 子句  --row_number() 

rollup: 对group by 的分组结果进行小计和总计;   

cube: 对group by 的分组结果进行小计和总计

grouping(): 解释小计值出现对应结果的原因

1. 排序显示空行的问题,
SCOTT@RACDB1>select * from emp
  2  order by comm;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ------------------------------
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30 noname
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30 noname
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30 noname
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30 noname
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20 RESEARCH
      7839 KING       PRESIDENT            17-NOV-81          5000                    10 ACCOUNTING
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20 RESEARCH
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30 noname
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20 RESEARCH
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10 ACCOUNTING
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30 noname
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20 RESEARCH
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20 RESEARCH
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10 ACCOUNTING

14 rows selected.
SCOTT@RACDB1>l
  1  select * from emp
  2* order by comm
SCOTT@RACDB1>a  nulls first
  2* order by comm nulls first
SCOTT@RACDB1>/

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- -----------------------------------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20 RESEARCH
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10 ACCOUNTING
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20 RESEARCH
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30 noname
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20 RESEARCH
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20 RESEARCH
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30 noname
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10 ACCOUNTING
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20 RESEARCH
      7839 KING       PRESIDENT            17-NOV-81          5000                    10 ACCOUNTING
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30 noname
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30 noname
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30 noname
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30 noname

14 rows selected.

SCOTT@RACDB1>l
  1  select * from emp
  2* order by comm nulls first
SCOTT@RACDB1>del
SCOTT@RACDB1>a order by comm desc;
  1* select * from emporder by comm desc
SCOTT@RACDB1>.
SCOTT@RACDB1>/
select * from emporder by comm desc
                       *
ERROR at line 1:
ORA-00933: SQL command not properly ended

SCOTT@RACDB1>select * from emp
  2  order by comm desc;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- --------------------------------------------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20 RESEARCH
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10 ACCOUNTING
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20 RESEARCH
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30 noname
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20 RESEARCH
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20 RESEARCH
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30 noname
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10 ACCOUNTING
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20 RESEARCH
      7839 KING       PRESIDENT            17-NOV-81          5000                    10 ACCOUNTING
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30 noname
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30 noname
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30 noname
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30 noname

14 rows selected.
SCOTT@RACDB1>a  nulls last;
  2* order by comm desc nulls last
SCOTT@RACDB1>/

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- -------------------------------------------
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30 noname
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30 noname
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30 noname
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30 noname
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20 RESEARCH
      7839 KING       PRESIDENT            17-NOV-81          5000                    10 ACCOUNTING
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20 RESEARCH
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30 noname
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20 RESEARCH
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10 ACCOUNTING
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30 noname
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20 RESEARCH
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20 RESEARCH
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10 ACCOUNTING

14 rows selected.

2.常用的单行函数

单行函数 函数说明
lower(s) 将所给的字符串 s 全部转换成小写
upper(s) 将所给的字符串 s 全部转换成大写
initcap(s) 将所给的字符串s中的每个单词转换成首字母大写
concat(s1,s2) 将字符串s1和s2 拼接起来  等同于 s1||s2
substr(s,m,[n]) 表示从所给的字符串 s 中取得指定范围的子串
注意:m 表示从字符串 s 的第几位开始,正整数表示从左到右数第几位开始,负整数表示从右至左第几位开始,但是截取的方向都是向右
           n 表示截取子串的长度,如果不写,即从第 m 位开始向右知道结束
length(s) 表示返回所给字符串 s 的字符数,即长度
instr(s,s1,[m],[n])
表示子字符串 s1 在字符串 s 中,从第 m 位开始搜索,第 n 次出现的时候,所在的位置是哪
注意:如果 m 是负整数,表示从右第 m 开始向左搜索;
          如果 m,n 都省略,表示从左边第 1 位开始向右搜索,在 s 中第 1 次出现 s1 的位置在哪;
lpad(s,n,s1) 在字符串 s 左边补充子串 s1,直到长度为n;
rpad(s,n,s1) 在字符串 s 右边补充子串 s1,直到长度为n;
trim(leading|trailing|both s1 from s)
表示将子字符串 s1 从 s 中移除;
注意:leading 表示将 s 左边的 s1 去除;(ltrm)
         trailing 表示将 s 右边的 s1 去除;(rtrim)
         both 表示将 s 两边的 s1 去除(默认)
replace(s,s1,s2)
表示将字符串 s 中的 s1 用 s2 替换掉
round(c,[n])
表示求数字 c 的四舍五入值
注意:n 表示保留到小数点后面多少位;
           如果 n 省略,则保留成整数;
           如果 n 为负数,则保留到小数点左边多少位;
trunc(c,[n])
表示将数字 c 截断,保留到小数点后第 n 位
注意:n 表示保留到小数点后第 n 位,则第 n 位之后的数字,不考虑四舍五入,直接舍去 
mod(m,n)
表示取得 m 除以 n 得到的余数
注意:n=0 时,mod(m,n)=m (特殊)
ceil(c)
表示将数字 c 向上取整;
注意:ceil(c) 取得的结果应该为大于或者等于 c 的最小整数
floor(c)
表示将数字 c 向下取整;
注意:floor(c) 取得的结果应该为小于或者等于 c 的最大整数
months_between(date1,date2)
表示取得两个日期之间的月数(date1-date2);
add_months(date,n)
表示在所给时间的基础上加、减多少个月
注意:这里的月表示自然月,每月的天数有数据库自动计算取得,不用人为指定每月多少天
           n 的值可以为小数,但是运算时会将 n 向下取整
next_day(date,' char')
表示在所给时间的基础上,看一下下个星期几是多少号
注意:char 不一定要写成英文的星期,取决于数据库服务器的字符集编码
last_day(date)
表示取得所给时间所在月的最后一天
round(date,'[fmt]')
表示将所给的时间四舍五入保留到年、月  
round(sysdate,'month')      round(sysdate,'year')
trunc(date,'[fmt]')
表示将所给的时间截断到年、月
显示的数据类型转换

to_char(number|date,'fmt')
表示将 number或者date类型的数据按照 fmt指定的格式转换成字符串
fmt格式:'yyyy'   'year'  'mm'  'month'  'mon'  'dd'    'day'   'dy'等 (下面有部分实验)
to_number(char[,'fmt'])
表示将字符串按照某种格式转换成数字
注意:如果字符串中包含数字之外的字符,则转换会报错!
           转换完成后,即可对此结果进行加减乘除四则运算;
to_date(char[,'fmt'])
表示将字符串按照某种特定格式转换成日期;
注意:如果字符串中包含不可识别的字符,则转换会报错!
           转换完成后,即可对此结果进行加减运算;
nvl(e1,e2)
如果第1个参数e1的值为 null,则返回 e2的值
nvl2(e1,e2,e3)
如果第1个参数e1的值为 null,则返回e3的值,
如果第2个参数e1的值不为 null,则返回e2的值
nullif(e1,e2)
如果e1=e2,则返回null,如果e1<>e2,则返回e1    两个参数类型必须一致!
coalesce(e1,e2,e3,...en)
返回参数中第1个非空表达式
case ..else..end
case循环,必须以 end结束    else可以不写
decode(col|expr,search1,result1[,search2,result2,.....,][,default])
如果列或表达式满足 search1就返回result1, 满足search2就返回result2......剩余返回default
注意: decode 只是将case 的书写方式进行了简写

3. 不常见类型的小实验 --日期显示、显示异常、货币问题   时间yy解析 rr解析

不常见类型实验:


SCOTT@RACDB1>select to_char(sysdate,'hh24:mi:ss am') t1,    
  2  to_char(sysdate,'dd "of" month') t2,
  3  to_char(sysdate,'ddspth') t3
  4  from dual;

T1          T2                             T3
----------- ------------------------------ --------------
17:15:46 pm 28 of may                      twenty-eighth

SCOTT@RACDB1>select to_char(12345678.11223344,'$099,999,999.999999999') 

             from dual;


TO_CHAR(12345678.112233
-----------------------
 $012,345,678.112233440


-- 整数部分的长度无法涵盖给定数字的整数部分,则显示异常
SCOTT@RACDB1>select to_char(12345678.11223344,'$099,.999999999') from dual;   

TO_CHAR(12345678
----------------
################

-- 小数部分的长度无法涵盖给定数字的小数部分,则四舍五入
SCOTT@RACDB1>select to_char(12345678.11223344,'$099,999,999.99') from dual;

TO_CHAR(12345678
----------------
 $012,345,678.11

SCOTT@RACDB1>select to_char(12345678.11883344,'$099,999,999.99') from dual;

TO_CHAR(12345678
----------------
 $012,345,678.12

-- 以本地货币的形式进行显示,则用 L 开头


SCOTT@RACDB1>select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ---------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.4.0

20 rows selected.

SCOTT@RACDB1>alter session set NLS_CURRENCY='RMB';

Session altered.

SCOTT@RACDB1>select to_char(12345678.11223344,'L099,999,999.999999999') result 

            from dual;


RESULT
--------------------------------
        RMB012,345,678.112233440

提示:将给出的字符串转换成日期时,需要对其中的年份进行解析,分为yy解析和rr解析方式;
原则:yy解析会把16解析成本世纪(数据库服务器所处的世纪)的AA16年
     rr解析会把16做判断,得出是上世纪的BB16年,或者本世纪的AA16年,或者下世纪的CC16年

SYS@RACDB1>alter system set fixed_date = '1995-01-01';

System altered.

SYS@RACDB1>select sysdate from dual;

SYSDATE
------------
01-JAN-95

SYS@RACDB1>alter session set nls_date_format='yyyy-mm-dd';

Session altered.

SYS@RACDB1>select sysdate from dual;

SYSDATE
----------
1995-01-01

SYS@RACDB1>select to_date('27-nov-95','dd-mm-yy') t1,

                  to_date('27-nov-17','dd-mm-yy') t2

    from dual;

T1         T2
---------- ------------------------------


1995-11-27 2017-11-27
-- 恢复服务器的时间
SYS@RACDB1>alter system set fixed_date=none;

System altered.

SYS@RACDB1>select sysdate from dual;

SYSDATE
----------
2016-05-28

本文档:sql相关笔记整理汇总,杂 1~ http://www.5ibig.com/article/index/arcid/52.htm

文档:sql相关笔记整理汇总,杂 2~ http://www.5ibig.com/article/index/arcid/53.htm

昵  称:
邮  箱:
评论内容:
验 证 码:
可用[code][/code]插入代码
点击刷新验证码