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

设置列别名

设置列别名,需要在列名和别名中间加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';

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

• ‘%’，匹配0或多个字符
• ‘_’，只匹配一个字符

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

转移字符

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

'~%' ESCAPE '~'

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

order by排序子句必须是SQL最后一个子句

• order by可以使用列别名排序。
• 可以用于表达式结果来排序，如ORDER BY 15+SAL表示使用SAL列数据加上15后的结果来排序。
• 可以使用列号来排序（可读性差），如ORDER BY 3 DESC表示用第三列来降序排序，ORDER BY 3 ASC表示用第三列来升序排序，当然ASC是默认模式可以不写。
• 可以指定多列，当两个数据项第一列相等时比较第二列数据，当第二列数据也相等时比较第三列，以此类推。

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

• L。L命令用来调出上一次的语句（注意，SQLplus缓冲区每次只保存一个语句，新SQL语句送入后会刷新上一次的，所以缓冲区每次都存最近一次的语句）
• /。/用来执行。例如，使用L+回车调出上一次命令，然后键入/+回车来执行这个命令。
• n text。当命令是多行时，想要修改一行，则使用n text。n指定行号，text表示用来替换这一行的文本。text可以不用加引号，0 text表示在第一行之前加入语句text。
• n。表示选择行，输入n+回车，定位到第n行，然后：
• a。append或简写为a。表示附加。在当前行或者用n定位到第n行后，使用a text来在这一行之后附加text语句。之后再使用L调出修改后的命令，使用/来重新运行。
• del。del用来删除当前行。del n用来删除第n行。del m n用来删除第m行到第n行的所有行。
• c。或为change。用法为c /旧文本/新文本，表示将旧文本替换为新文本。它与n text区别是，前者是行中部分文本替换，而后者是第n行整行替换。可以使用n定位到第n行，然后使用该命令来替换

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）

• get 路径\xxx.sql会将路径下的xxx.sql这个脚本文件内容存入缓存区。也就是载入但不执行。
• @ 路径\xxx.sql或start 路径\xxx.sql表示载入并且执行。

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（列格式化命令）

• col 列名 for 99999 表示将列名所在列（数字类型）的宽度设置为9个数字宽度。9表示一个数字宽度且不显示前导零，若要强制显示前导零则冠以0。逗号,表示分为符，$表示美元符，L表示本地化货币符号。
  例如假设数据表中有三个数19855510、98765、123,则for L99,9,99.99查出的数据是这样的：
  VALUE
  ---------------------
  #####################
  ￥98,7,65.00
  ￥1,23.00
  这是因为第一个超出了位数（5个整数位）则会显示#。
• col 列名 for a12 表示将列名所在列（字符类型）的宽度设置为12个字符宽度。
• col 列名 heading 列标题 for ....设置宽度，并且以列标题显示。如果列标题中使用竖线|，则表示分两行显示列标题。
• col 列名 用来查询该列是否被修改过格式，若没有被修改过则报错，若修改过则显示格式信息
• col 列名 clear 清除设置的格式。

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

日期函数

• to_date：将字符串转换为日期型数据:to_date('27-6月 -17') //中文日期格式

• months_between(日期1，日期2)：表示日期1与日期2之间的月份数。该数正负号取决于日期1是否大于日期2。大于则为正，反之为负。

• add_months(日期，月数n)：把n个月加到日期上

• next_day(日期，'星期')：返回从日期开始算起，下一个'星期’是几号。如果是美国的日期格式，要输入'MONDAY'等，中国日期格式是'星期一'等。

  select next_day(sysdate,'星期一') from dual;
  

• last_day(日期)： 该日期所在月的最后一天。

• round(日期，单位)：对月份进位。单位为'MONTH','YEAR'

    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日即周日。

• trunc(日期，单位)，和上面类似，不过是全部丢弃。以天为单位，丢弃日期直到遇见最近的周日，以月为单位则全部丢日期到该月一日；以年为单位则退回到该年的一月一日。

• numtodsinterval(数值,单位)、numtoyminterval(数值,单位)，表示将一个数值转为指定日期单位，前者ds表示从天数day到秒数second，后者表示从年数year到月数month。所以可选的单位有：

  • numtodsinterval: 'day' 'hour' minute' 'second'
  • numtoyminterval: 'year' 'month'

  例如，给当前时间加三个月：select sysdate+numtoyminterval(3,'month') from dual;，或者给当前日期减去100分钟：select sysdate-numtodsinterval(100,'minute') from dual;
  又比如，将800分钟转为日期型：

  select numtodsinterval(800,'minute') from dual;
  /*---------------输出*/
  NUMTODSINTERVAL(800,'MINUTE')
  -------------------------------
  +000000000 13:20:00.000000000
  /*即800分钟0天13小时20分钟*/
  

• 如果想知道两个日期之间差了多少年、多少月、多少小时等等，使用下面的模板：

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秒。

• 在中文日期格式下，显示Date只有年月日没有时间，为了参看时间，可以转为字符型：

/*------------------中文日期格式下*/
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这样的，然后把它们用喜欢的方式组合输出。

• YYYY：指定完整年份

• YEAR：用英文表示年（如TWENTY SEVENTEEN）

• MM：两位数表示月份（有前导零）

• MONTH：月份的完整英文表示（中文日期格式显示中文月份，如7月）

• DD：两位数表示日期（有前导零）

• DY：显示星期几的完整英文表示（中文日期格式显示中文星期，如星期一）

• DAY：星期几的英文三位缩写（中文日期格式显示中文星期，如星期一）

• fm：用来压缩前导零和空格

• sp：显示数字的英文

• th：显示序数词

  SQL> select to_char(to_date('03-7月 -17'),'YYYY-fmMM~~~DDspth') from dual;

TO_CHAR(TO_DATE('03-7月-17'),'YYYY-FMMM~~~DDSPTH
------------------------------------------------
2017-7~~~THIRD

to_char（数->字符）

to_char(数,格式)

格式：

• 9：显示一位数字，不显示前导零
• 0：显示一位数字，显示前导零
• ，：千分位符（然鹅写成99,9,99也没问题(⊙o⊙)…）
• .：小数点
• $:美元符
• L：本地化货币符号
• MI：右边显示减号
• PR：负数用尖括号括起来。

 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

• 一个表达式中只要含有空值，则该表达式结果为空值
• 检查是不是空值必须使用is NULL或is not null而不能是=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。

• 别名一旦定义，只能使用别名，不能使用原表名。
• 别名最长为30个字符。

外连接（(+)写法）

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

外连接

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

• 左外连接就是在等值连接的基础上加上主表中的未匹配数据
• 右外连接是在等值连接的基础上加上被连接表的不匹配数据

一下两个都可以

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写法）

• 左外连接

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

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

• 右外连接

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

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

• 事实上，二者是可以互通的：

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

上面代码，交换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

• 全外连接

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

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

having、from中可以使用单行子查询

  from (select * from ...........)