Oracle学习笔记

最新推荐文章于 2022-07-03 12:01:30 发布

whale_hh

最新推荐文章于 2022-07-03 12:01:30 发布

阅读量473

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/whale_gyj/article/details/115077282

版权

oracle数据库与mysql区别的零散知识点–转义字符与escape–dual伪表，用来测试函数和表达式–数值型 number–字符串varchar2一、单行函数1.大小写控制函数upperlowerinitcap2.字符控制函数concatsubstrlength instr（）某字符在字符串中首次出现的位置 lpad();rpad()左/右填补 trim(h from hellohhhhworld)去除首尾字符h r

摘要由CSDN通过智能技术生成

oracle数据库

与mysql区别的零散知识点

–转义字符与escape
–dual伪表，用来测试函数和表达式

–数值型 number

–字符串varchar2

一、单行函数

1.大小写控制函数

upper

lower

initcap

2.字符控制函数

concat

substr

length

instr（）某字符在字符串中首次出现的位置

lpad();rpad()左/右填补

trim(h from hellohhhhworld)去除首尾字符h

replace

3.数字函数

round四舍五入

mod(a,b)求余

trunc（455.76,1）截断
日期

–sysdate返回日期和时间

select sysdate from dual;

在日期上加上或减去一个数字结果仍为日期
两个日期相减返回日期之间相差的天数（日期加法运算无意义）
可以用数字除以24来向日期中加上或减去天数
```
 select sysdate-1,sysdate+1 from dual;
```

4.日期函数：

months_between两个日期相差的月数
```
month_between(a,b)
```
add_months向指定日期中加上若干月数

next_day指定日期的下一个星期*对应的日期

select add_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期四') from dual;

last_day本月的最后一天

#员工是每个月倒数第二天来公司的有哪些 ?
```
select last_name,hire_date 
from employes 
where hire_date=last_day(sysdate)-1;
```

round日期的四舍五入

round(sysdate,'month');#按月
round(sysdate,'year');

trunc日期的截断

trunc(sysdate,'month');
trunc(sysdate,'year');

5.转换函数

隐式数据类型转换 date <——> varchar2 <—–> number
显式数据类型转换

转换函数：toe_char to_date to_number

date和char转换：TO_CHAR(date, 'format_model')

to_date(char,'format_model')

to_char:

select last_name,hire_date 
from employees 
where to_char(hire_date,'yyyy-mm-dd')='1994-06-07';
#需要穿插字符时，添加双引号
select last_name,to_char(hire_date,'yyyy"年"mm"月"dd"日"')
from employees 
where to_char(hire_date,'yyyy"年"mm"月"dd"日"')='1994年06月07日';

to_date:

select TO_DATE('2012年10月29日08:10:21','yyyy"年"mm"月"dd"日"hh:mi:ss')
From dual;

number和char转换

to_char:

select to_char(1234567.89,'999,999,999.99') from dual;
#用零补位
select to_char(1234567.89,'000,000,000.99') from dual;
#货币
select to_char(1234567.89,'$000,000,000.99') from dual;#美元
select to_char(1234567.89,'L000,000,000.99') from dual;#本地货币

to_number:

select to_number('￥001,234,567.89','L000,000,999.99') from dual;

6.通用函数

适用于任何数据类型，同时也适用于空值

NVL 将空值转换成一个已知的值 NVL (expr1, expr2)

NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')

#如：计算员工的年薪

select employee_id,last_name,salary*12*(1+nvl(commission_pct,0)) annual_sal
from employees

#department_id为空时，显示没有部门

select last_name,nvl(to_char(department_id,'999999'),'没有部门') from employees

select last_name,nvl(to_char(department_id),'没有部门') from employees

2.NVL2

NVL2 (expr1, expr2, expr3) :expr1不为NULL，返回expr2；为NULL，返回expr3

3.nullif

NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1

4.COALESCE

可以同时处理交替的多个值。如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE

7.条件表达式

在sql语句中使用IF-THEN-ELSE逻辑

CASE表达式

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

#查询部门号为10,20，30的员工信息，若部门号为10则打印其工资的1.1倍，为20则打印其工资的1.2倍，为30打印1.3倍

select employee_id,last_name,department_id
       case department_id when 10 then salary*1.1
                          when 20 then salary*1.2
                          else salary*1.3 end new_sal
from employees
where department_id in(10,20,30);
#方式2
select employee_id,last_name,department_id
       case department_id when 10 then salary*1.1
                          when 20 then salary*1.2
                          when 30 then salary*1.3 
       else salary end new_sal
from employees;

DECODE函数

DECODE(col|expression, search1, result1 ,
      			   [, search2, result2,...,]
      			   [, default])

#如上例

select employee_id,last_name,department_id
       decode(department_id,10,salary*1.1,
                            20,salary*1.2,
                            salary*1.3) new_sal       
from employees
where department_id in(10,20,30);

#习题：

1.打印出”2009 年10月14日 9:25:40“格式的当前系统的日期和时间

select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss') from dual;#使用双引号向日期中添加字符

2.查询个员工的姓名，并显示其在公司工作的月份数

select last_name,hire_date,round(months_between(sysdate,hire_date),1) worked_month
from employees;

3.做一个查询产生如下结果：kings earns $24000 monthly but wants ￥72000

select last_name||' earns '||to_char(salary,'$999999')||' monthly,but wants '||    to_char(3*salary,'$999999') "dream salary"
from employees;

二、多表查询

(一)内连接

1.等值连接

连接 n个表,至少需要 n-1个连接条件（同mysql）

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;

如：

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

2.非等值连接

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

(二)外连接

1.右外连接

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column(+) = table2.column;

如：

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e, departments d
WHERE  e.department_id(+) = d.department_id ;

2.左外连接

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column = table2.column(+);

如：

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e, departments d
WHERE  e.department_id= d.department_id(+)  ;

sql99语法

(一)内连接

1.CROSS JOIN 子句

使连接的表产生叉集，叉集和笛卡尔集是相同的

2.NATURAL JOIN 子句

(1).会以两个表中具有相同名字的列为条件创建等值连接：

select last_name,department_name  
from employees
natural  join departments ; #会自动匹配表中相同的列，可多列匹配

(2). 使用 USING 子句指定等值连接中需要用到的列：

SELECT e.employee_id, e.last_name, d.location_id
FROM   employees e JOIN departments d
USING (department_id)

(3). 使用 ON 子句指定额外的连接条件

SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

(二)外连接

1.左外连接 LEFT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

2.右外连接 RIGHT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

3 .满外连接

两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
FULL OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

(三)自连接

同mysql

三、分组函数

(一)组函数类型：

语法：

SELECT	[column,] group_function(column), ...
FROM		table
[WHERE	condition]
[GROUP BY	column]
[ORDER BY	column];

avg
count
max
min
stddev
sum
count（expr）计算expr不为空的记录总数

(二)分组数据

1.group by

在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。如：

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中，如：

SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

#不能在 WHERE 子句中使用组函数，可以在 HAVING 子句中使用组函数

2.过滤分组：having子句

行已经被分组。
使用了组函数。
满足HAVING 子句中条件的分组将被显示。

3.嵌套组函数

如：显示各部门平均工资的最大值

SELECT   MAX(AVG(salary))
FROM     employees
GROUP BY department_id;

#习题

查询公司在1995-1998年之间，每年雇用的人数，结果类似下面的格式

total	1995	1996	1997	1998
20	3	4	6	7

select count(*) "total",
    count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
    count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
    count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
    count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998')

四、子查询

语法：

SELECT	select_list
FROM	table
WHERE	expr operator
		 	(SELECT	select_list
		     FROM table);

单行子查询
多行子查询

in

any

all

五、创建和管理

最低0.47元/天解锁文章

whale_hh

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle学习笔记

oracle数据库与mysql区别的零散知识点–转义字符与escape–dual伪表，用来测试函数和表达式–数值型 number–字符串varchar2一、单行函数1.大小写控制函数upperlowerinitcap2.字符控制函数concatsubstrlength instr（）某字符在字符串中首次出现的位置 lpad();rpad()左/右填补 trim(h from hellohhhhworld)去除首尾字符h r
复制链接

扫一扫