我爱编程

《Oracle数据库管理与开发》读书笔记

2017-06-27  本文已影响67人  Azur_wxj
设置列别名

设置列别名,需要在列名和别名中间加AS或者空格，如

select ename as “姓名” ........
select ename "姓名" ......

值得注意的是，这个别名只能用双引号括起来，也可以不用双引号，但不能用单引号，否则报错，因为单引号是字符类型。

不用双引号，则别名自动转为大写；如果用双引号，则原样输出。在我的数据库中，因为支持UTF-8字符，所以使用中文作为别名，双引号用不用都可以。

字符的拼接

字符的拼接，使用||符来进行，比如

select ename||' 你好' from emp;

那么就会显示


则可以设置列的别名

select ename||' 你好' "姓名" from emp;

注意，字符拼接是与字符串拼接，因此使用单引号而不能使用双引号，而列的别名则不能使用单引号，而应该使用双引号。
DISTINCT：去重

使用distinct可以去重。如select distinct ename from emp但是，在大型查询时应该避免，因为Oracle通过排序来进行去重，这会使得效率降低

字符串和日期型

在Oracle中，字符串是使用单引号括起来的字串，它是区分大小写的。而日期型数据，应该使用单引号括起来。

LIKE：通配符查询

使用LIKE，不仅可以起到和=一样的效果

....where ename='AMY';
....where ename like 'AMY';

还可以配合通配符进行模糊查询。

例如，S_P%匹配以这样三个字符为前缀的所有字符串：它的第一个字符和第三个字符只能是S和P，而第二个是任意字符。

转移字符

以\%表示输出%，而不是一个通配符。同时可以自己定义转移符

'~%' ESCAPE '~'

即使用ESCAPE定义之后的字符为转移符，上面匹配一个%。

order by排序子句必须是SQL最后一个子句
SQL*Plus命令：describe或者简写desc

desc emp用来描述一个表。（注意！！SQL语句会存入缓冲区，但是SQLPlus命令不会被存入缓冲区*）

SQL*Plus命令：set linesize n或者简写set line n

设置输出结果的宽度为n个字符，默认为80。如果输出的结果一行宽度大于n，就会隔行写，显示非常丑陋。

SQL*Plus命令：L、/、n text、n、a或append、del、c或change
SQL*Plus命令：save

save 路径\xxx.sql会将缓冲区的SQL语句（即最近一次执行的SQL语句）存入路径下的xxx.sql这个脚本文件中。
当第二次执行这个命令是，会提示文件已存在，此时需要追加模式选项：

sava 路径\xxx.sql  [replace|append]

使用replace表示覆盖原有内容，而append表示追加。
注意，如果在SQLplus没有在指定路径的文件读写权限，那么会报错表示无法创建文件。如果省略路径直接提供脚本文件名，则会在oracle的默认目录下创建。
我的目录是C:\app\Berlin\oracleDB\product\12.2.0\dbhome_1\bin

SQL*Plus命令：get和@（或start）
SQL*Plus命令：spool
spool 路径/xxx.txt
......一系列查询语句和输出结果
spool off

上述表示，创建xxx.txt文件，到spool off位置，中间所有显示在屏幕上的内容都送到xxx.txt文件中去。spool out代表输送到打印机。


打开C:\app\Berlin\oracleDB\product\12.2.0\dbhome_1\bin\result.txt文件，其内容为
SQL*Plus命令：col（列格式化命令）
SQL*Plus命令：ttitle

ttitle 正文|on|off或tti 正文|on|off设置每页的顶标题。

SQL*Plus命令：btitle

btitle 正文|on|off或bti 正文|on|off设置每页的底标题。

环境变量查询

使用show all来查看SQLplus所有环境变量。
使用show 环境变量查看具体环境变量。

环境变量echo

使用@ xxx.sql可以载入并执行脚本文件，但是它输出结果而不输出SQL语句本身。
使用set echo on打开echo机制。则使用@ xxx.sql即可以输出命令语句也可以输出结果。

环境变量feedback
set feedback n|off|on
set feed n|off|on  //简写

当查询结果大于等于n条时，显示行数，即最后面的“已选择xx行”。n默认为6条。

环境变量heading
set heading off|on
set hea off|on  //简写

是否显示列标题

环境变量arraysize
set arraysize n
set array n  //简写

每次取回的行数上限，默认20，最多5000.

环境变量pagesize
set pagesize n
set page n  //简写

设置每页的行数，从而控制每一页显示的数据量。最大值可以为50000
例如，一般查询时，结果为


发现列标题出现两次，这是因为它分成了两页显示，使用

set pagesize 25；

表示一页最多25行，则结果是

虚表dual

显示某些值的时候，这些值不属于任何一个表，则使用虚表，如下面。

lower、upper、initcap、concat、length

转小写、转大写、字符串中每个单词首字母转大写、连接两个字符串、长度。
select lower('ABC') from dual; //从虚表里面查

substr

子串。substr(字串|列名，m，n)，表示返回下标从m到n的子串（包括n）。第一个字符从1开始。省略n则表示到结尾。

instr

查找给定子串出现次数。instr(串|列名，'待查串'，m，n)表示从串中查询'待查串'的出现次数，m可省略，表示从第几个字符开始查；n可省略，表示出现次数，当查出出现次数已到n便不再查询。不指定n，表示1，即查到第一个待查串就停止。

trim

select trim (' DWEYE ') from dual;
默认去掉首尾的空格
elect trim (both from ' DWEYE ') from dual;
与上面等价，只是显式使用了both模式，即首尾。也可以使用leading或者trailing指定首或尾
select trim ('x' from 'xxxxDWEYExxxx') from dual;
去掉首尾的x字符
select trim (both 'x' from 'xxxxDWEYExxxx') from dual;
与上面等价，只不过显式使用了both模式，即首尾。也可以使用leading或者trailing指定首或尾。

注意！trim中是要去掉的字符而不是字符串，你不能写

select trim('abc' from 'abcabc') from dual；  //不能写'abc'

但是可以使用ltrim和rtrim函数，他们可以去掉首部字符串和尾部字符串

select LTRIM('xxxABCxxxx','xxxx') from dual；

上面的代码表示去掉xxxABCxxxx的左边（首部）的字串'xxxx'

replace
replace('总串','被替换字符串','替换字符串’);

总串中把被替换字符串替换为'替换字符串’。

mod(m,n)

相当于 m%n

round、trunc
round(数,n);  //表示小数点后第n位向前四舍五入
trunc(数,n);    //小数点后第n位舍去，为0

考虑数168.99，考虑第二个参数n：
第一位小数n=0，个位数是-1，十位是-2，第二位小数是1，第三位小数是2，……形成一个数轴。
因此，想要百分九向前四舍五入就要n=1；十位6想要舍去就要n=-2.

SQL> select trunc(168.99,-2) from dual;

TRUNC(168.99,-2)
----------------
             100
设置当前会话的日期显示

设置为美国日期格式，如03-DEC-81

alter session set NLS_DATE_LANGUAGE='AMERICAN'

设置为中国日期格式，如17-12月-80

alter session set NLS_DATE_LANGUAGE='SIMPLIFIED CHINESE'
sysdate系统日期
select sysdate from dual;
###输出：27-6月 -17
日期函数
    SQL> select round(to_date('03-7月 -17'),'MONTH') from dual;

    ROUND(TO_DATE(
    --------------
    01-7月 -17

以月为单位，则2017年7月3日，是7月的上半月，所以舍去为7月1日。可知如果以月为单位对7月28日进位，会得到8月1日。

SQL> select round(to_date('03-7月 -17'),'YEAR') from dual;

ROUND(TO_DATE(
--------------
01-1月 -18

以年为单位，7月属于2017年下半年，所以进位为2018年1月1日。

SQL> select round(to_date('03-7月 -17'),'DAY') from dual;

ROUND(TO_DATE(
--------------
02-7月 -17

以天为单位，按周一到周三、周四到周日四舍五入到最近的周日。7月3日为周一，于是舍入到7月2日即周日。

SELECT NUMTOYMINTERVAL(MONTHS_BETWEEN(dt1, dt2), 'month') mon,
    NUMTODSINTERVAL(dt1-(ADD_MONTHS(dt2,TRUNC(MONTHS_BETWEEN(dt1, dt2)))),'day') day
FROM (
    SELECT SYSDATE dt1,
            TO_DATE('20070523 21:23:34','yyyymmdd hh24:mi:ss') dt2
    FROM DUAL
);

显示：

MON             DAY
--------------- ------------------------------
+000000010-04   +000000021 14:27:20.000000000

表示差了10年4个月21天14小时27分钟20秒。

/*------------------中文日期格式下*/
select sysdate from dual;
/*------------------输出*/
SYSDATE
--------------
14-9月 -17

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
/*-------------------输出*/
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2017-09-14 11:56:20
不同类型的显式转换
to_char（日期->字符）
to_char(日期,格式)

格式比如'YYYY-MM~~~DD'这样就能输出如 2017-06~~~08的形式。可见，关键是指定YYYY、DD、MM这样的，然后把它们用喜欢的方式组合输出。

TO_CHAR(TO_DATE('03-7月-17'),'YYYY-FMMM~~~DDSPTH
------------------------------------------------
2017-7~~~THIRD
to_char（数->字符）
to_char(数,格式)

格式：

 select to_char(55555555.55, '99,99.99') from dual;

结果
TO_CHAR(55555555.5
------------------
#########

SQL>  select to_char(5555.55, '99,99.99') from dual;

TO_CHAR(5555.55,'9
------------------
55,55.55


SQL> select to_char(5555.55, '9,99.99') from dual;

TO_CHAR(5555.55,
----------------
########
空值NULL
NVL空值函数
  nvl(a,b)，若a不为空值则返回a，否则返回b。

a、b的类型必须相同

NVL2
NLV2(conditon,A,B)

等价于

condition is not  NULL ? A:B;

B应该能被转换为A的类型（它们类型可以不同），而这个NVL2表达式的返回类型是A的类型，一个例外是当A是定长字符串型时，返回结果是边长字符串类型。

比较函数NULLIF
nullif(a,b)

如果a=b，则返回空值。若不等，则返回a。a不能是控制。

coalesce函数
coalesce(表达式1，表达式2，……，表达式n)

返回第一个不为空的表达式。若都是空值，则返回空值。

条件判断函数decode和case语句
decode( A, B1,C1,B2,C2,....,Bn-1,Cn-1,Cn)

等价于

case A when B1 then C1
       when B2 then C2
       .......
       else Cn end

他们都等价于如下的逻辑代码：

if（A==B1):
    return C1;
else if(A==B2):
    return C2;
....
else:
    return Cn;
count计非空行的数目
表的别名
select * from emp e,dept d where e.ename=........

上面，定义emp和dept表的别名分别为e和d。

外连接（(+)写法）

外连接就是在等值连接的基础上加上未匹配数据。即可能出现下面这种情况：

外连接

分为左外连接、右外链接和全外连接。

一下两个都可以

B=A(+)    
A(+)=B

但是不能写成

(+)A=B
B=(+)A

(+)所在的那一侧的表就是信息缺失的一方（即某些行中没有数据的一方），自然另一侧就是有数据的一方。

SQL:1999语法
FROM emp e
CROSS JOIN dept d1,dept d2

等价于

FROM emp e,dept d1,dept d2

from emp e
join dept d
using(deptno)
........

等价于

from emp e,dept d where e.deptno=d.deptno

其中，using值匹配一列，并且不能使用表的别名。
使用on子句可以定义多个条件（可以有多个on，这样可以分开便与理解*）：

from emp e
join dept d
on(e.deptno=d.deptno)
join salgrade s
on(e.salary=salgrade.hsal)
......

等价于

from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.salary=salgrade.hsal
.........

甚至可以加入where：

from emp e
join dept d
on(e.deptno=d.deptno)
join salgrade s
on(e.salary=salgrade.hsal)
where e...............  //可以on和where，混合使用

同时也可以where换成AND。
值得注意的是，on中一定要使用表名和列名。

外连接（SQL:1999写法）

此时，e是主表，d是被连接表，则因为左外连接就是在等值连接的基础上加上主表中的未匹配数据，所以结果中，d是信息缺失一方，而e是信息完整一方

此时，e是主表，d是被连接表，则因为右外连接就是在等值连接的基础上加上被连接表中的未匹配数据，所以结果中，e是信息缺失一方，而d是信息完整一方。

上面代码，交换d、e：

  from dept d
  left outer join emp e
  on e.deptno=d.deptno

此时，作为左外连接，d是信息完整一方，e是信息缺失，这显然和下面的右外链接等价：

  from emp e
  right outer join dept d
  on e.deptno=d.deptno

即全部包含了e、d所有记录。即可能有e、d等值连接行，有可能有e信息缺失而d信息完整、也有可能有e信息完整而d信息缺失的行。

having、from中可以使用单行子查询
  from (select * from ...........)
上一篇下一篇

猜你喜欢

热点阅读