Oracle课堂笔记1

第一章

操作关系型数据库的语言叫sql语言，结构化的查询语言。还有一种面向数据库的编程语言，叫pl/sql编程语言。

安装数据库的时候可以有两种方式选择：

服务器：用来保存数据，以及提供相应的服务。

客户端：用来提供连接，访问数据库。

配置数据库，需要两个重要的服务。

1。监听服务。用来监听网络上哪些机器对数据库发出了连接请求。监听服务配置文件在C:\oracle\ora90\network\ADMIN\listener.ora。

port=1521，oracle数据库的默认端口。host = 本机的ip地址。监听服务需要重新启动，修改之后的配置文件才能被应用。

2。数据库服务。oracleserviceDHC（数据库名）。保证该服务正常启动。

配置客户端：

配置的是网络服务名（数据库别名或者主机别名）的配置文件。该配置文件的位置在C:\oracle\ora90\network\ADMIN\tnsnames.ora。

修正HOST = 192.168.3.100（数据库所在机器的ip地址）。

测试连接：

oracle提供了一个工具sql*plus。

在dos下输入：sqlplus 用户名/密码@网络服务名。

oracle9i在安装的时候有三个默认的用户，scott/tiger 普通用户，system/manager 数据库管理员，sys/change_on_install 超级用户。

也可以用sqlplus/nolog，先进入sql环境。

使用sqlplus工具的一个命令，connect(conn)用户名/密码@网络服务名connect scott/tiger@dhee

show user是sqlplus工具的一个命令，用来显示连接的用户名。

以.sql为扩展名的文件叫做oracle的脚本文件。脚本档中存放了大量的sql语句以及sqlplus工具的命令。好处在于运行该脚本就相当于执行了大量的sql语句和sqlplus工具的命令。

本课程需要运行三个脚本。

在sqlplus下输入@脚本所在路径和脚本名。

@F:\Oracle-Sql\Script\hr_cre.sql 创建表以及数据库的其它数据对象。

@F:\Oracle-Sql\Script\hr_popul.sql 向表中插入资料。

@F:\Oracle-Sql\Script\del_data.sql 删除表中多余的数据。

将sqlplus的提示语言改成中文：

在dos下输入set nls_lang = SIMPLIFIED CHINESE_CHINA.ZHS16GBK，回车。

describe(desc) 表名：sqlplus工具的命令。用来描述表的结构。表有哪些字段，每个字段什么数据类型，是否可以为空等。

三张表：employees（员工表），departments（部门表），locations（地址表）。

desc departments

number(p,s) 数位类型：

p代表数字的位数。s代表小数的位数。其中s可以省略，代表整数。number(4)：-9999 ~~~~ 9999。number(5,2)：-999.99 ~~~~ 999.99

varchar2(n) 字符串类型：

select *

from departments;

其中*代表该表中的所有列，单独使用。from 后面指定表名。

select *

from departments;

其中*代表该表中的所有列，单独使用。from 后面指定表名。

sqlplus里的sql语句可以换行，以分号结束，运行。为了增加程序的可读性，建议各个字句分行写。

select department_id,department_name

from departments;

列与列之间用逗号分割，取出特定列。

edit(ed)：用来编辑缓冲区中的上一条sql语句。修正之后保存，/运行。

数据库中的系统表，叫做数据字典。

desc user_tables：（查询）存放的是当前用户所拥有的表的信息。

select table_name

from user_tables;

当将SQL语句发送给服务器端运行的时候，服务器的第一个动作，将SQL语句中所有的小写字母全部转成大写字母之后再运行。所以为了提高SQL语句的执行效率，建议SQL语句全部由大写字母组成。

可以对数字和日期类型的数据进行算术运算。

数字类型可以+ - * /。

日期类型只能+ , - 。

日期类型的数据加减数字的单位为天。

SELECT SALARY + 300

FROM EMPLOYEES;

空值的关键词为null。

空值在数据库中既不是零也不是空格，所以空值在参与到算术运算中的时候，结果仍然为空值。

可以给列定义有意义的别名。有两种方法：

1.使用as关键词。列名as 别名。

2.直接使用空格隔开。列名别名。

如果需要在别名中加入空格，特殊字符，或者维持大小写，需要在别名两端加上双引号。双引号在ORACLE数据库中非常特殊，只有两处可以使用。

1处在定义别名，一处在TO_CHAR函数对日期的转换。

双引号用来维持列名或者表名的大小写，单引号用来维持资料的大小写。

字符串类型的数据可以进行连接运算，连接运算的符号为||。

SELECT

LAST_NAME || FIRST_NAME

FROM

EMPLOYEES;

ex：求每个员工的工作信息。

岗位

姓名's 岗位是：xxxx。

SELECT

LAST_NAME || ' ' || FIRST_NAME || '''s岗位是:'||JOB_ID || '。' 岗位

FROM

EMPLOYEES;

ex：求员工号，姓名，雇佣日期。其中雇佣日期为被雇佣两周之后的日期。姓名为姓+ 空格+ 名。

员工号姓名雇佣日期

SELECT

EMPLOYEE_ID 员工号,

LAST_NAME||' '||FIRST_NAME "姓名",

HIRE_DA TE + 14 雇佣日期

FROM

EMPLOYEES;

ex：求哪些部门有员工。并去掉重复数据。

SELECT

DISTINCT DEPARTMENT_ID 部门号

FROM

EMPLOYEES;

去掉结果集中重复的行数据，使用的是DISTINCT关键词。一个SELECT语句中只能有一个DISTINCT关键词，写在SELECT关键词后面。DISTINCT关键词会将结果集按照第一列的升序自动排序。

ex：求公司中共有哪些岗位。

SELECT

DISTINCT JOB_ID 岗位

FROM

EMPLOYEES;

第二章

用WHERE子句过滤掉不满足条件的数据。书写的位置，紧随FROM子句。WHERE子句后面跟的是能够返回布尔值的表达式。

SELECT

EMPLOYEE_ID,DEPARTMENT_ID

FROM

EMPLOYEES

WHERE

DEPARTMENT_ID = 90;

ex：求薪水大于5000元的员工号，姓名，薪水。

SELECT

EMPLOYEE_ID 员工号,LAST_NAME||FIRST_NAME 姓名,SALARY ||'元' 薪水

FROM

EMPLOYEES

WHER

SALARY > 5000

SELECT和WHERE子句中不能使用列的别名。

执行顺序：FROM --> WHERE ---> SELECT(先取数据，然后定义别名)。

字符串和日期类型的数据要放在单引号中。

比较的时候，字符串区分大小写。

日期格式敏感。必须满足DD-MON-YY的格式。

SELECT

EMPLOYEE_ID

FROM

EMPLOYEES

WHERE

HIRE_DA TE = '24-5月-99';

BETWEEN A AND B：大于等于A小于等于B。SALARY BETWEEN 1000 AND 5000。

IN：等于值列表中的任意一个。DEPARTMENT_ID IN (10,20,30)

IS NULL：用来判断空值。NULL在用比较运算符（=,<>,>,<,<=,>=）进行判断的时候恒为假。DEPARTMENT_ID IS NULL

ex：求在95年到98年被雇佣的员工。

员工号雇佣日期部门号

SELECT

EMPLOYEE_ID 员工号,

HIRE_DA TE 雇佣日期,

DEPARTMENT_ID ||'号' 部门号

FROM

EMPLOYEES

WHERE

HIRE_DA TE BETWEEN '1-1月-95' AND '31-12月-98';

AND：逻辑与。并且的关系。

OR：逻辑或。或者的关系。

NOT：逻辑非。相反的关系。

优先级：NOT AND OR。

ex：求没有经理或者岗位为IT_PROG，ST_CLERK的员工。abel为last_name,xxx为岗位。

员工号岗位

FROM

EMPLOYEES

WHERE

MANAGER_ID IS NULL

OR

JOB_ID='IT_PROG'

OR

JOB_ID='ST_CLERK';

JOB_ID IN('IT_PROG','ST_CLERK')等价于上面两条语句。IN操作符的性能要好于OR。

ex：求所有有奖金的80号部门员工。

员工号奖金额

SELECT

EMPLOYEE_ID 员工号,

'￥' || COMMISSION_PCT * SALARY 奖金额

FROM

EMPLOYEES

WHERE

COMMISSION_PCT IS NOT NULL

AND

DEPARTMENT_ID = 80

对空值取反：MANAGER IS NOT NULL。=== NOT (MANAGER_ID IS NULL)

对IN取反：DEPARTMENT_ID NOT IN (A,B,C,D) ==== NOT (DEPARTMENT_ID IN (A,B,C,D))

对BETWEEN AND取反：SALARY NOT BETWEEN A AND B === NOT (SALARY BETWEEN A AND B)

不同的数据类型参与到同一种运算中的时候，数据会进行隐式转换。将数据转成相同的数据类型之后再进行运算。比较运算，算术运算：字符串类型转成数字类型，或者字符串类型转成日期类型。

连接运算：数字类型，日期类型隐式转成字符串类型。尽量避免隐式转换。

LIKE操作符可以进行不完全的匹配，模糊查询。

LIKE操作符必须要配合两个通配符来使用。

_：代表一个字符。

%：代表零个或者多个字符。

ex：求姓名中包含a和e的员工。

员工号姓名

SELECT

EMPLOYEE_ID 员工号,

LAST_NAME||' '||FIRST_NAME 姓名

FROM

EMPLOYEES

WHERE

LAST_NAME||' '||FIRST_NAME LIKE '%a%e%'

ex：求工作跟IT相关并且在99年被雇佣的员工。

员工号岗位雇佣日期

SELECT

EMPLOYEE_ID 员工号,

JOB_ID 岗位,

HIRE_DA TE 雇佣日期

FROM

EMPLOYEES

WHERE

JOB_ID LIKE '%IT%'

AND

HIRE_DA TE LIKE '%99';

如果需要查询的字符串中包含_或者%的话，需要使用ESCAPE关键词，自定义转义字符，将通配符转义成普通字符来使用。

SELECT

EMPLOYEE_ID 员工号,

JOB_ID 岗位,

HIRE_DA TE 雇佣日期

FROM

EMPLOYEES

WHERE

JOB_ID LIKE '$%I$_T%' ESCAPE '$'

--$自定义的转义字符，$符号后面的通配符被转义成普通字符。

如果需要按照指定的列进行排序，使用ORDER BY子句。写在SELECT语句的最后。

可以按照列名排序，还可以按照列的位置排序。

升序（ASC），默认的。降序（DESC），需要显示指定。多个列参与排序，用逗号分割。

SELECT

EMPLOYEE_ID,DEPARTMENT_ID,SALARY

FROM

EMPLOYEES

-- ORDER BY SALARY DESC

-- ORDER BY 2 DESC = DEPARTMENT_ID DESC

ORDER BY DEPARTMENT_ID DESC,SALARY DESC;

执行顺序：FROM --> WHERE ---> SELECT ---> ORDER BY（在ORDER BY中可以使用列的别名，并且如果按照表达式排序的话，建议你使用别名）

SELECT

EMPLOYEE_ID,DEPARTMENT_ID,SALARY * 12 TOTSAL

FROM

EMPLOYEES

-- ORDER BY TOTSAL --建议使用

ORDER BY 12 * SALARY

ex：求有奖金或者10号，90号部门工作的员工。按照被雇佣的年数降序排序。员工号奖金额雇佣日期

SELECT

EMPLOYEE_ID 员工号,

COMMISSION_PCT*SALARY 奖金额,

HIRE_DATE 雇佣日期

FROM

EMPLOYEES

WHERE

COMMISSION_PCT IS NOT NULL

OR

DEPARTMENT_ID IN (10,90)

ORDER BY

HIRE_DATE DESC;

ex：求99年雇佣的50号部门，电话号码以515开头的110号部门的员工。

员工号姓名部门

SELECT

EMPLOYEE_ID 员工号,

LAST_NAME||FIRST_NAME 姓名,

DEPARTMENT_ID||'号部门' 部门

FROM

EMPLOYEES

WHERE

HIRE_DA TE LIKE '%99'

AND

DEPARTMENT_ID = 50

OR

PHONE_NUMBER LIKE '515%'

AND

DEPARTMENT_ID = 110

第三章

SQL语句中可以使用函数。函数分为两种：

1。单行函数，一次处理一个数据，返回一个结果。LOWER('ABC')

2。多行函数，一次处理多个数据，返回一个结果。SUM(N)。

单行函数分为五大类：

1。字符函数：用来处理字符串类型的数据。

2。数字函数：用来处理数字类型的数据。

3。日期函数：用来处理日期类型的数据。

1。字符函数：

LOWER(P)：将P转成小写返回。LOWER('ABC') --> abc。

UPPER(P)：将P转成大写返回。UPPER('abc') --> ABC。

INITCAP(P)：将P中各个单词的首字母大写，其余小写。其中P里面的非字母和数字元元的字符作为单词的分隔符号号。INITCAP('ABC_DEF') --> Abc_Def

SELECT

LOWER('ABC') L,

UPPER('abc') U,

INITCAP('ABC_DEF') I

FROM

DUAL

DUAL：ORACLE数据库中的伪表。用来验证函数和客观事实。

ex：求邮件地址中包含完整的姓的员工。姓大写输出，邮件位元址首字母大写输出。

员工号姓邮件地址

SELECT

EMPLOYEE_ID 员工号,

UPPER(LAST_NAME) 姓,

INITCAP(EMAIL) 邮件地址

FROM

EMPLOYEES

WHERE

LOWER(EMAIL) LIKE '%' || LOWER(LAST_NAME) || '%'

CONCAT(P1,P2)：将P1，P2直接相连。CONCA T('A','B') --> AB 作用等同于||。

SUBSTR(P1,P2)：取子串。在P1中，从P2开始取，取到字符串尾。SUBSTR('HELLO',3) --> LLO 。ORACLE 中的字符串的位置从1开始。

SUBSTR(P1,P2,P3)：取子串。在P1中，从P2开始取，取P3位。SUBSTR('HELLO',3,2) --> LL 。ORACLE中的字符串的位置从1开始。

如果P2为负数，表示位置从右向左的方向数位置。SUBSTR('HELLO',2,2) --> EL SUBSTR('HELLO',-2,2) --> LO。LENGTH(P1)：求P1的字符个数。LENGTH('你好')：2。

LENGTHB(P1)：求P1的字节个数。LENGTH('你好')：4。

INSTR(P1,P2)：判断P2在P1中第一次出现的位置。INSTR('HELLO','L') --> 3 如果P2在P1中不存在，返回值为0。

INSTR(P1,P2,P3)：在P1中从P3位置开始判断P2在P1中第一次出现的位置。INSTR('HELLO','L',4) --> 4 。INSTR(P1,P2,P3,P4)：在P1中从P3位置开始判断P2在P1中第P4次出现的位置。INSTR('HELLO','L',1,2) --> 4 。

SELECT

CONCA T('HELLO ','WORLD') C,

SUBSTR('HELLO',3,2) S1,

SUBSTR('HELLO',3) S2,

SUBSTR('HELLO',-5,2) S3,

LENGTH('你好') L1,

LENGTHB('你好') L2,

FROM

DUAL

ex：将员工的姓名按首字母排序，并写出姓名的长度（length）

姓名长度

SELECT

CONCA T(LAST_NAME,FIRST_NAME) 姓名,

LENGTH(CONCAT(LAST_NAME,FIRST_NAME)) 长度

FROM

EMPLOYEES

ORDER BY

SUBSTR(LAST_NAME,1,1) DESC

单行函数可以嵌套，从内向外执行。

ex：求姓名中的第四个字母为大写字母，并且姓名中出现了至少两次a的员工。其中姓名为姓首字母大写，名的首字母大写，其余小写。

员工号姓名

（1）

SELECT

EMPLOYEE_ID 员工号,

UPPER(SUBSTR(LAST_NAME,1,1)||LOWER(SUBSTR(LAST_NAME,2))) 姓,

UPPER(SUBSTR(FIRST_NAME,1,1)||LOWER(SUBSTR(FIRST_NAME,2))) 名

FROM

EMPLOYEES

WHERE

SUBSTR(LAST_NAME||FIRST_NAME,4,1)=

UPPER(SUBSTR(LAST_NAME||FIRST_NAME,4,1))

AND

INSTR(LAST_NAME||FIRST_NAME,'a',1,2)>0

（2）

SELECT

EMPLOYEE_ID 员工号,

INITCAP(REPLACE(LAST_NAME,' ')||' '||REPLACE(FIRST_NAME,' ')) 姓名

FROM

EMPLOYEES

WHERE

SUBSTR(LAST_NAME||FIRST_NAME,4,1)=UPPER(SUBSTR(LAST_NAME||FIRST_NAME,4,1))

AND

INSTR(LOWER(LAST_NAME||FIRST_NAME),'a',1,2)>0

LPAD(P1,P2,P3)：在P1的左端填充P3，把P1填满P2位。LPAD('HELLO',10,'#') --> #####HELLO

RPAD(P1,P2,P3)：在P1的右端填充P3，把P1填满P2位。RPAD('HELLO',10,'#') --> HELLO#####

TRIM(P1 FROM P2)：从P2的两端去掉P1。TRIM('H' FROM 'HELLOHHWORLDHHH') --> ELLOHHWORLD TRIM(P1)：从P1的两端去掉空格。TRIM(' HELLO WORLD ') ---> HELLO WORLD

REPLACE(P1,P2)：将P1中的P2替换成空。

REPLACE(P1,P2,P3)：将P1中的P2替换成P3。

RPAD('HELLO',10,'#') R,

TRIM('H' FROM 'HELLOHHHHWORLDHH') T1,

TRIM(' HELLO WORLD ') T2,

REPLACE('HELLO','L') R1,

REPLACE('HELLO','L','Y') R2

FROM

DUAL

ex：求电话号码由15位数字组成的员工。其中薪水为满一千元补充一个*。岗位从_的位置取到字符串尾。

员工号薪水岗位

100 **2000 PROC

101 ***3000 SALES

SELECT

EMPLOYEE_ID 员工号,

LPAD(SALARY,LENGTH(SALARY)+SALARY/1000,'*') 薪水,

SUBSTR(JOB_ID,INSTR(JOB_ID,'_')+1) 岗位

FROM

EMPLOYEES

WHERE

LENGTH(REPLACE(PHONE_NUMBER,'.'))=15

2。数字函数

TRUNC(P1)：对P1截断取整。TRUNC(45.936) --> 45

TRUNC(P1,P2)：对P1截断保留P2位小数。 TRUNC(45.936,2) --> 45.93

ROUND(P1)：对P1四舍五入取整。ROUND(45.936) --> 46

ROUND(P1,P2)：对P1四舍五入保留P2位小数。ROUND(45.936,2) --> 45.94

MOD(P1,P2)：求P1，P2的余数。当P2为零，函数返回P1的值。MOD(1000,300) ---> 100 MOD(1000,0)--〉1000

FLOOR(P1)：小于等于P1的最大整数。FLOOR(45.936) --> 45

CEIL(P1)：大于等于P1的最小整数。CEIL(45.936)--> 46

SELECT

TRUNC(45.936) T1,

TRUNC(45.936,2) T2,

ROUND(45.936) R1,

ROUND(45.936,2) R2,

FLOOR(45.936) F,

CEIL(45.936) C

FROM

DUAL

3。日期函数

SYSDATE：返回服务器的系统时间。

ex：求雇佣周数大于500周的员工。其中不满一周的不计算在内。并且按照周数的降序排序输出。

员工号雇佣的周数

TRUNC((SYSDATE-HIRE_DATE)/7) 雇佣的周数

FROM

EMPLOYEES

WHERE

SYSDATE-HIRE_DATE >3500

ORDER BY

雇佣的周数DESC

MONTHS_BETWEEN(DATE1,DATE2)：返回两个日期相差的月份数。

ADD_MONTHS(DATE1,NUMBER1)：返回的是在DATE1上加NUMBER1个月之后的日期是多少。

LAST_DAY(DATE1)：返回的是DATE1所在月份的最后一天的日期是多少。

NEXT_DAY(DATE1,V1)：返回的是DA TE1的下一个V1的日期是多少。NEXT_DAY(SYSDATE,'星期一')：07-1月-08。V1的取值星期一~~~ 星期日。

ex：求被雇佣20年以上的员工。电话号码取第三到第五位的数字。超过半年的按一年计算，按照年数的升序排序。

员工号雇佣年数电话号码

SELECT

EMPLOYEE_ID 员工号,

ROUND(MONTHS_BETWEEN(SYSDA TE,HIRE_DA TE)/12) 雇佣年数,

SUBSTR(REPLACE(PHONE_NUMBER,'.'),3,3) 电话号码

FROM

EMPLOYEES

WHERE

MONTHS_BETWEEN(SYSDATE,HIRE_DA TE)/12 > 20

ORDER BY

雇佣年数ASC

ex：假设80，90号部门的员工被雇佣的期限为10年，求到现在为止被解雇的员工。

员工号雇佣日期被解雇日期

SELECT

EMPLOYEE_ID 员工号,

HIRE_DA TE 雇佣日期,

ADD_MONTHS(HIRE_DA TE,120) 被解雇的日期

FROM

EMPLOYEES

WHERE

DEPARTMENT_ID IN (80,90)

AND

MONTHS_BETWEEN(SYSDATE,HIRE_DA TE)>120

ex：求当前日期的两个月零三天的下一个星期日是哪一天。

日期

SELECT

ex：求岗位元中_后面的第一个字母是P的员工。岗位元元取后三个字母显示，雇佣日期为被雇佣半个月之后的日期，年份为满10年加一个#。只要超过一年，都按照整年计算，按年数的升序排序。

员工号岗位年数雇佣日期

SELECT

EMPLOYEE_ID 员工号,

SUBSTR(JOB_ID,-3,3) 岗位,

LPAD(CEIL((MONTHS_BETWEEN(SYSDATE,HIRE_DATE))/12),

LENGTH(CEIL((MONTHS_BETWEEN(SYSDATE,HIRE_DA TE))/12))+

TRUNC((CEIL((MONTHS_BETWEEN(SYSDA TE,HIRE_DATE))/12))/10),'#')||'年' 年数,

ADD_MONTHS(HIRE_DA TE,0.5) 雇佣日期

FROM

EMPLOYEES

WHERE

UPPER(SUBSTR(JOB_ID,INSTR(JOB_ID,'_')+1,1))='P'

AND

JOB_ID LIKE '%/_P%' ESCAPE '/'

ORDER BY

(CEIL((MONTHS_BETWEEN(SYSDATE,HIRE_DATE))/12)) ASC

ex：假设有奖金的员工每年可以拿到14月的薪水以及1年的奖金额，求这些员工到现在为止拿了多少钱？

员工号总计

SELECT

EMPLOYEE_ID 员工号,

(COMMISSION_PCT*SALARY*12+SALARY*14)*

TRUNC(MONTHS_BETWEEN(SYSDA TE,HIRE_DATE)/12)+

(COMMISSION_PCT*SALARY+SALARY)*

MOD(TRUNC(MONTHS_BETWEEN(SYSDA TE,HIRE_DATE)),12) 总计

FROM

EMPLOYEES

WHERE

COMMISSION_PCT IS NOT NULL

4。转换函数

TO_CHAR(DATE1,FORMAT)：其中FORMA T可以省略。TO_CHAR(SYSDTAE)。

FORMAT参数中可以出现的元素：

YYYY：四位的年份。

YY：两位的年份。

YEAR：英文的年。

MONTH：全称的月份。

MON：简称的月份。

MM：带有前导零的月份。

其中MONTH和MON在中文下没有区别，英文下MONTH ---> JUNE，MON -- JUN

DAY：全称的星期。

HH24：24小时进制的小时。

HH：12小时进制的小时。

MI：分钟。

SS：秒。

AM/PM：上下午。

SELECT

TO_CHAR(SYSDATE,'YYYY/YY/YEAR MONTH/MON/MM DAY/DY/DD HH24/HH:MI:SS AM/PM')

FROM

DUAL

ex：求在97年，99年的星期一到星期五被雇佣的员工。

员工号雇佣日期

SELECT

EMPLOYEE_ID 员工号,

TO_CHAR(HIRE_DATE,'YYYY/MONTH/DD DY') 雇佣日期

FROM

EMPLOYEES

WHERE

TO_CHAR(HIRE_DATE,'YYYY') IN ('1997','1999')

AND

TO_CHAR(HIRE_DATE,'DY') NOT IN ('星期六','星期日')

如果需要在格式参数中引入特殊字符，需要使用双引号。

FM可以取掉格式参数中的多余空格和前导零。书写的位置可以任意，写在FM后面的数据中的多余空格和前导零。

TO_CHAR(HIRE_DATE, 'FMYYYY"年"/MON/DD"日" DY') ---〉1999年/11月/6日星期二

TO_CHAR(NUMBER1,FORMAT)：FORMAT参数可以省略。TO_CHAR(123) --> 123

FORMAT：

9：代表数字。

0：代表占位符，0所对应的位置如果有数字则显示数字，没有数字，显示0。

L：本地货币符号。

$：美元符号。

.：小数点。小数点后面的数字可以用9表示也可以用0表示。

,：千位符。

需要注意的是，如果格式参数中小数点后面的位数小于原始数据的小数点后面的位数，那么会进行四舍五入保留。如果格式参数中整数字元的位数小于原始数据的整数字元数，则得不到正确的结果。所以格式参数中整数字元的位数要大于等于原始数据的整数字元。

SELECT

TO_CHAR(12345,'$99,999.99') T1,

TO_CHAR(12345,'00000') T2,

TO_CHAR(87512345,'L99,999,999.00') T3,

TO_CHAR(12345,'00000000') T4, --占位

TO_CHAR(45678.127,'99,999.99') T5, --四舍五入

TO_CHAR(45678,'999') T6 --错误数据

FROM

TO_DATE(V1,FORMAT)：是TO_CHAR函数的逆转换。

SELECT

TO_NUMBER('RMB12,456','L99,999'),

TO_DA TE('2008/01/01','YYYY/MM/DD')

FROM

DUAL

ex：求哪些员工是在每个月的最后一天被雇佣的。

员工号薪水雇佣日期

100 RMB5,000.0 1998年/9月/31日下午

SELECT

EMPLOYEE_ID 员工号,

TO_CHAR(SALARY,'L99,999.0') 薪水,

TO_CHAR(HIRE_DATE,'FMYYYY"年"/MON/DD"日" AM/PM') 雇佣日期

FROM

EMPLOYEES

WHERE

HIRE_DA TE=LAST_DAY(HIRE_DATE)

ex：求2010年8月21日的日期是星期几。

星期几

SELECT

TO_CHAR(TO_DATE('2010年8月21日','YYYY"年"MM"月"DD"日"'),'DY') 星期几

FROM

DUAL

ex：求岗位元元中_后的第三个字母为e，并且最后一个字母为k。假设每个员工10年被重新雇佣一次，求哪些员工已经被重新雇佣了。

员工号雇佣日期重新被雇佣日期年薪

100 1997年11月1日 2007年11月1日 RMB12,000

SELECT

EMPLOYEE_ID 员工号,

TO_CHAR(HIRE_DATE,'FMYYYY"年"MMDD"日"') 雇佣日期,

TO_CHAR(ADD_MONTHS(HIRE_DATE,120),'FMYYYY"年"MONDD"日"') 重新雇佣日期,

TO_CHAR(SALARY*12,'FML99,999,99') 年薪

FROM

EMPLOYEES

WHERE

LOWER(SUBSTR(JOB_ID,INSTR(JOB_ID,'_')+3,1))='e'

AND

LOWER(SUBSTR(JOB_ID,-1,1))='k'

AND

MONTHS_BETWEEN(SYSDATE,HIRE_DATE)>120

NVL2(P1,P2,P3)：当P1为空的时候，函数返回P3的值，当P1不为NULL的时候，函数返回P2的值。要求P2,P3参数的数据类型必须相同。

NULLIF(P1,P2)：当P1=P2的时候，函数返回NULL。当P1<>P2的时候，函数返回P1的值。

COALESCE(P1,P2,P3....)：参数的个数任意，返回若干个参数中第一个不为空的参数的值，要求若干个参数的数据类型必须相同，并且在一个时刻至少有一个不为空的参数。

SELECT

NVL(NULL,'NVL函数') N1,

NVL('HELLO','NVL函数') N2,

NVL2(NULL,'HELLO','WORLD') NL1,

NVL2('NULL','HELLO','WORLD') NL2,

NULLIF('HELLO','HELLO') NUIF1,

NULLIF('HELLO','WORLD') NUIF2,

--COALESCE(NULL,NULL)C1 --错误语句，因为参数中没有非空参数

COALESCE(NULL,1,2,3) C1,

COALESCE(NULL,NULL,2,3) C2,

COALESCE(NULL,NULL,NULL,3) C3

FROM

DUAL

ex：求98年3月，97年8月被雇佣的员工。其中姓名中，如果姓与名的长度相等，则只显示名，如果不等，则显示姓。薪水为月薪，如果有奖金则显示奖金额，如果没有，则显示

'该员工没有奖金'。合计为该员工一年的薪水与奖金的合计。按照合计的降序排序，

相同的合计，按照员工号的升序排序

员工号姓名月薪奖金额合计

100 XXX 12,000.0元1,200元15,000元

SELECT

EMPLOYEE_ID 员工号,

NVL2(NULLIF(LENGTH(LAST_NAME),LENGTH(FIRST_NAME)),LAST_NAME,FIRST_NAME) 姓名, TO_CHAR(SALARY,'L999,999.9') 月薪,

NVL(TO_CHAR(COMMISSION_PCT*SALARY,'999,999.9'),'该员工没有奖金') 奖金额,

TO_CHAR((SALARY*12*(1+NVL(COMMISSION_PCT,0))),'9,999,999') 合计

FROM

EMPLOYEES

WHERE

TO_CHAR(HIRE_DATE,'YYYYMM') IN ('199803','199708')

ORDER BY

合计DESC,EMPLOYEE_ID

实现IF THEN ELSE逻辑分支结构有两种方法：

CASE表达式

DECODE函数

SELECT

--判断表达式与值相等：

--WHEN可以有多个，THEN与ELSE后面返回值的数据类型必须相同。

CASE TO_CHAR(SYSDATE,'YYYY') WHEN '1997' THEN '香港回归'

--ELSE可以省略，如果没有匹配的值，表达式返回空值

ELSE '没什么特殊的'

END 年,

--判断表示式与值不等：

CASE WHEN MONTHS_BETWEEN(SYSDA TE,'09-1月-98') < 36 THEN '不足三年'

WHEN MONTHS_BETWEEN(SYSDA TE,'09-1月-98') < 72 THEN '不足六年'

WHEN MONTHS_BETWEEN(SYSDA TE,'09-1月-98') < 120 THEN '不足十年'

--ELSE可以省略，如果没有匹配的值，表达式返回空值

ELSE '超过十年'

END 说明

FROM

DUAL

ex：求50号部门，姓名中只包含两个a的员工。姓名为从第一个a开始到第二个a.

电话号码为从中间位置的数字开始截取3位。雇佣年数为不满一年也按照一年计算。

资历根据雇佣年数显示：如果年数为5-10年，则为新员工。

如果年数为11-15年，则为技术总监。

如果年数超过15年，则为总统。

员工号姓名电话号码雇佣年数资历

100 axxa 234 14 技术总监

SELECT

EMPLOYEE_ID 员工号,

SUBSTR(LAST_NAME||FIRST_NAME,INSTR(UPPER(LAST_NAME||FIRST_NAME),'A',1,1),

INSTR(UPPER(LAST_NAME||FIRST_NAME),'A',1,2)-

INSTR(UPPER(LAST_NAME||FIRST_NAME),'A',1,1)+1) 姓名,

SUBSTR(REPLACE(PHONE_NUMBER,'.'),ROUND(LENGTH(REPLACE(PHONE_NUMBER,'.'))/2),3) 电话号码,

CEIL(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) 雇佣年数,

CASE WHEN CEIL(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) BETWEEN 5 AND 10 THEN '新员工' WHEN CEIL(MONTHS_BETWEEN(SYSDA TE,HIRE_DATE)/12) BETWEEN 11 AND 15 THEN '技术总监'

WHEN CEIL(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) > 15 THEN '总统' END 资历

FROM

EMPLOYEES

WHERE

DEPARTMENT_ID = 50

AND

INSTR(UPPER(LAST_NAME||FIRST_NAME),'A',1,2)>0

AND

INSTR(UPPER(LAST_NAME||FIRST_NAME),'A',1,3)=0

--DECODE函数

--DECODE(P1,P2,P3,P4....)：该函数至少要有3个参数。

--判断表达式与值相等：

--WHEN可以有多个，THEN与ELSE后面返回值的数据类型必须相同。

CASE TO_CHAR(SYSDATE,'YYYY') WHEN '1997' THEN '香港回归'

WHEN '1998' THEN '相约98'

WHEN '1999' THEN '澳门回归'

WHEN '2008' THEN '奥运在北京，观光到大连'

--ELSE可以省略，如果没有匹配的值，表达式返回空值

ELSE '没什么特殊的'

END "CASE",

DECODE(TO_CHAR(SYSDATE,'YYYY'),'1997','香港回归',

'1998','相约98',

'1999','澳门回归',

'2008','奥运在北京，观光到大连',

'没什么特殊的') DECODE

FROM

DUAL

第四章

--如果多个表中包含相同的列名，需要使用表名指定操作的列。

--多表查询的时候，可以为表指定别名，方式只有一种，使用空格

--为了提高SQL执行效率，在每一列的前面指定表名。

--多表查询可能会产生一种错误的结果，叫做迪卡尔乘积。

--比如EMPLOYEES表中有3条数据，DEPARTMENTS表中有5条数据，得到的结果为3*5=15条--为了避免产生迪卡尔乘积，多表关联必须给出有效的关联条件

--如果关联N张表，至少需要N-1个有效的关联条件

SELECT

E.EMPLOYEE_ID,E.DEPARTMENT_ID,D.DEPARTMENT_NAME

FROM

EMPLOYEES E,DEPARTMENTS D --E和D就是为表定义的别名

WHERE

E.DEPARTMENT_ID = D.DEPARTMENT_ID

ex：求跟149号部门经理在同一个部门的其它员工。

员工号部门号部门名所在城市

SELECT

E.EMPLOYEE_ID 员工号,

E.DEPARTMENT_ID 部门号,

D.DEPARTMENT_NAME 部门名,

L.CITY 所在城市

FROM

EMPLOYEES E,DEPARTMENTS D,LOCA TIONS L

WHERE

--多表查询的WHERE子句从下往上执行，

--所以将能够过滤掉大量数据的条件放到下面先执行

AND

D.MANAGER_ID = 149

ex：求100，124号经理所管理的员工信息。

员工号薪水岗位等级

SELECT

E.EMPLOYEE_ID 员工号,E.SALARY 薪水,J.GRADE_LEVEL 岗位等级

FROM

EMPLOYEES E,JOB_GRADES J

WHERE

--非等值连接

E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL

AND

E.MANAGER_ID IN (100,124)

ex: 求在Americas工作的员工。其中姓名中如果包含a和e，则取第一个a到第一个e之间的字符串表示，以外的场合取后四位显示。其中年薪包含一年的奖金和薪水。

税为如果年薪在5-10万，则缴纳2%的税。11-15万，则缴纳4%的税，超过15万则缴纳5%的税。少于5万，则显示不用缴税。

员工号姓名年薪税

100 axxxe 130，000 14,000

SELECT

E.EMPLOYEE_ID 员工号,

CASE WHEN UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME) LIKE '%A%E%'

THEN

SUBSTR(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME,INSTR(UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME),'A'),

INSTR(UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME),'E',

INSTR(UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME),'A'))-INSTR(UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME),'A')+1) WHEN UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME) LIKE '%E%A%'

THEN

SUBSTR(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME,INSTR(UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME),'E'),

INSTR(UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME),'A',

INSTR(UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME),'E'))-INSTR(UPPER(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME),'E')+1) ELSE SUBSTR(https://www.wendangku.net/doc/cb901579.html,ST_NAME||E.FIRST_NAME,-4)

END 姓名,

TO_CHAR(E.SALARY*12*(1+NVL(https://www.wendangku.net/doc/cb901579.html,MISSION_PCT,0)),'9,999,999') 年薪,

CASE WHEN E.SALARY*12*(1+NVL(https://www.wendangku.net/doc/cb901579.html,MISSION_PCT,0)) BETWEEN 5 AND 10

THEN TO_CHAR(E.SALARY*12*(1+NVL(https://www.wendangku.net/doc/cb901579.html,MISSION_PCT,0))*0.02,'99,999') WHEN E.SALARY*12*(1+NVL(https://www.wendangku.net/doc/cb901579.html,MISSION_PCT,0)) BETWEEN 11 AND 15

THEN TO_CHAR(E.SALARY*12*(1+NVL(https://www.wendangku.net/doc/cb901579.html,MISSION_PCT,0))*0.04,'99,999') WHEN E.SALARY*12*(1+NVL(https://www.wendangku.net/doc/cb901579.html,MISSION_PCT,0)) > 15

FROM

EMPLOYEES E,DEPARTMENTS D,LOCA TIONS L,REGIONS R,COUNTRIES C

WHERE

D.DEPARTMENT_ID=

E.DEPARTMENT_ID

AND

L.LOCA TION_ID=D.LOCATION_ID

AND

C.COUNTRY_ID=L.COUNTRY_ID

AND

R.REGION_ID=C.REGION_ID

AND

R.REGION_NAME='Americas'

连接方式有四种：

关联条件用=连接叫等值连接。

不是用=连接的叫非等值连接。

外连接用来取出不满足连接条件的数据。外连接的符号(+)。

外连接分为两种，左外连接和右外连接。

E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID --〉(+)加在连接条件的左端，叫做右外连接，取出右端表中所有数据，包括不满足条件的数据。

E.DEPARTMENT_ID = D.DEPARTMENT_ID(+) --〉(+)加在连接条件的右端，叫做左外连接，取出左端表中所有数据，包括不满足条件的数据。

SELECT

E.EMPLOYEE_ID,

D.DEPARTMENT_ID,

D.DEPARTMENT_NAME

FROM

EMPLOYEES E,

DEPARTMENTS D

WHERE

E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID

不能在连接条件的两端同时加外连接符号。外连接不能加在同一张表上。

ex: 选择所有员工的

last_name department_name city

XXX XXXX XXXX

SELECT

https://www.wendangku.net/doc/cb901579.html,ST_NAME,

E.DEPARTMENT_ID,

L.CITY

FROM

EMPLOYEES E,

DEPARTMENTS D,

LOCA TIONS L

WHERE

E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)

自连接

ex: 求员工号，员工名，经理号，经理名

SELECT

E.EMPLOYEE_ID,

https://www.wendangku.net/doc/cb901579.html,ST_NAME,

E.MANAGER_ID,

https://www.wendangku.net/doc/cb901579.html,ST_NAME

FROM

EMPLOYEES E,EMPLOYEES M

WHERE

E.MANAGER_ID = M.EMPLOYEE_ID

ex: 求公司里所有经理的信息。

经理号经理名部门名

100 XXX IT

SELECT DISTINCT

M.MANAGER_ID 经理号,

https://www.wendangku.net/doc/cb901579.html,ST_NAME 经理名,

D.DEPARTMENT_NAME 部门名

FROM

EMPLOYEES E,

EMPLOYEES M,

DEPARTMENTS D

WHERE

E.EMPLOYEE_ID=M.MANAGER_ID

AND

E.DEPARTMENT_ID=D.DEPARTMENT_ID

ex: 求LAST_NAME是Rajs的经理的经理是谁。

SELECT DISTINCT

P.EMPLOYEE_ID 员工号,

https://www.wendangku.net/doc/cb901579.html,ST_NAME 姓

FROM

EMPLOYEES E,

EMPLOYEES M,

EMPLOYEES P

WHERE

E.MANAGER_ID=M.EMPLOYEE_ID

AND

M.MANAGER_ID=P.EMPLOYEE_ID

AND

https://www.wendangku.net/doc/cb901579.html,ST_NAME='Rajs'

ex: 求LAST_NAME是Rajs的经理的经理所管理的员工有哪些。