Oracle 基础知识

本篇内容主要是基础知识，简单查询语句以及一些Oracle的常用函数。

大部分内容都是平时上课或是从老师ppt总结的，部分有参考《Oracle Datebase 11g SQL 开发之南》，如有不准确或是不正确的地方还望指正。

建表语句可以从如下链接下载：store_schema.sql

下载完成后用记事本打开，复制全部内容在command window下即可（开始可能会有报错，无视就好，原因是建表语句中有“drop table XXX;”这一句，防止重复建表，而在建表之前是没有XXX表的，所以会报错）。

注：

数据表和《Oracle Datebase 11g SQL 开发之南》中的实例相同。
转载请标明转载出处 http://santong.me 谢谢啦！

基本信息

Oracle版本：Oracle 11g

管理工具：PL/SQL；

常用窗口：command窗口，SQL窗口；

常用数据库账号：

store/store123（当把建表语句复制进command window时会创建）；
system/your_password(有时可能用到，system密码取决于你安装oracle时自己的设置)；

基础SQL语句

基础知识

Oracle大小写不敏感，即SELECT 和 select是等价的；
Oracle数据表中每行纪录对应不同的rowid，rownum，称为伪列，其中rowid是唯一标示；当执行查询语句时，rownum会根据返回的行从1开始重新顺序编号；
Oracle中的表可以不定义主键；
可以从dual表中查询与表内容无关的值；
```
select 1+1 from dual;             // 返回2；
```

Oracle中算术运算符有: + - * /

select to_date('25-1-2015')+2    // to_date()为日期函数;
from dual;                        // 返回2015/1/27;

可以在查询语句中用as关键字为列设置别名；

select name,price+2 as price 
from products;                    // 返回两列，名为name和price；

使用||连接列；

// 虽然查询了两个列，但是只返回一列名为Customer Name；
select first_name||' '||last_name as "Customer Name"
from customers;

在数据库中难免会有空值null，可以使用is
```
select * from  customers
where dob is null; 
```

利用distinct禁止重复显示相同行；

select distinct customer_id     
from purchases;

比较符号有： = , != , <> , < , > , <= , >=；

注意：与rownum相关的比较符只能是<或者是<=;
Oracle 10g引入了两种新的数据类型：BINARY_FLOAT和BINARY_DOUBLE，前者可以存储一个单精度的32位浮点数，后者可以存储一个双精度的64位浮点数，这两种新的数据类型根据二进制浮点运算的IEEE标准提出的。其需要的存储空间较小，可表示范围更大，执行速度更快，并且运算封闭，取整透明。如果需要开发一个涉及大量数字运算的系统，建议考虑如上类型。

逻辑操作优先级 NOT > AND > OR;

select * from customers
where not(dob>'01-1月-1970');

Oracle注释用 “–”

查询语句

*表示所有列：

select * from student;

where语句来过滤行：

select * from student
where student_id = 2;

any和some

表示满足一定范围内任意的值即满足条件(any也可以用some代替)：

// 返回大于2的customer_id，即只大于2，3，4中的2就满足了条件
select customer_id from customers
where customer_id > any(2,3,4);

all

表示要大于一定范围内所有值才满足条件：

// 返回大于4的customer_id，即要大于2，3，4中的最大值才满足条件
select customer_id from customers
where customer_id>all(2,3,4);

like和not like

用于匹配字符串，_代表一个字符，%代表任意个字符（可以是0个），like可以和not组合使用：

// 返回first_name中包含一个“o”的名字
select first_name from customers
where first_name like '_o%';

// 返回first_name中不包含“o”的名字
select first_name from customers
where first_name not like '_o%';

escape

用于当使用like需要匹配”_”或是”%”的时候用来定义转义符：

select * from promotions
where name like '%\%%' escape '\';//此时\为转义符

in和not in

用来检索列值是否在列表中，not in检索不在列表中的列值。

注意：当not in和null组合使用时，无任何返回结果，因为null表示不知道是什么值即可能表示任何值，所以查询无任何结果：

//in
select * from customers
where customer_id in (2,3,5,6);
//not in
select * from customers
where customer_id not in (2,3,5,6);
//查询无结果
select * from customers
where customer_id not in (2,3,5,6,null);

between

检查列值是否在指定区间内：

select * from customers
where customer_id between 1 and 3;

order by子句

默认根据列值的升序排列,字句后加desc进行降序排列：

//根据列last_name排列
select * from customers
order by last_name;
//1代表按表的第一列排序
select * from customers
order by 1;
//字句末家desc进行降序排列
select * from customers
order by customer_id desc;

内连接

目的是进行多张表的联合查询，取多张表的交集，实现有以下几种方式：

第一种方式是inner join…on…;

//p,pt分别是products和product_types的别名
select p.name, pt.name
from products p inner join product_types pt
on p.product_type_id = pt.product_type_id;

第二种方式用where匹配过滤;

//p,pt分别是products和product_types的别名
select p.name, pt.name
from products p, product_types pt
where p.product_type_id = pt.product_type_id;

第三种方式是比较推荐的，使用using来代替on字句，更加简洁常用;

注意:using子句中用到的列名如果出现在select子句中，前面不得加表名或者表别名。
```
//p,pt分别是products和product_types的别名
select p.name, pt.name
from products p inner join product_types pt
using(product_type_id);
```

左外链接

同样是进行多张表的联合查询，但显示join左边表的所有数据：

第一种方式，使用where过滤并在where子句中将join右边的表名后面加(+);

select p.name, pt.name
from products p, product_types pt
where p.product_type_id = pt.product_type_id(+);

第二种方式，使用…left outer join…using…子句;

select p.name,pt.name
from products p left outer join product_types pt
using(product_type_id);

右外链接

同样是进行多张表的联合查询，但显示join右边表的所有数据：

第一种方式，使用where过滤并在where子句中将join左边的表名后面加(+);

select p.name, pt.name
from products p, product_types pt
where p.product_type_id(+) = pt.product_type_id;

第二种方式，使用…right outer join…using…子句;

select p.name,pt.name
from products p right outer join product_types pt
using(product_type_id);

全外链接

同样是进行多张表的联合查询，但显示所有数据，使用…full outer join…using

题外话：其实我觉得oracle很多语句就像说英语一样，比较通顺，个人觉得还是比较容易理解的。。。

select p.name,pt.name
from products p full outer join product_types pt
using(product_type_id);

自连接

如题所说，自己和自己连接，用来实现一些简单查询无法实现或者实现起来较麻烦的查询，注意和内连接区分，内连接是多张表联合查询，自连接使只对一张表进行查询：

select w.last_name||' works for '||m.last_name
from employees w inner join employees m
on w.manager_id=m.employee_id;

Group by子句

用于对行进行分组，也可以和聚合函数 配合，统计各组的信息：

//根据product_type_id进行分组，统计rowid,然后按照product_type_id排序
select product_type_id,count(rowid)
from products
group by product_type_id
order by product_type_id;

基础函数

字符函数

函数	说明
INITCAP(s)	返回s字符串首字母大写、其他字母小写的格式
LOWER(s)	返回s字符串所有字母小写的格式
UPPER(s)	返回s字符串所有字母大写的格式
LENGTH(s)	返回s字符串的长度
SUBSTR(s,start[,length])	返回s的子串，从s的第start个字符开始，连续取length个字符；如果不写length，表示取到结尾
INSTR(s,find[,start][,occurrence])	在字符串s中查找字符串find，返回find所在位置是s的第几个字符。start表示从s的第几个字符开始查找，occurrence表示返回第几次出现的位置
REPLACE(s,find,replace_string)	把s中所有的字符串find都替换成replace_string
CONCAT(s1,s2)	返回s1和s2字符串连接后的字符串，等价于`s1	s2`
NVL(x,value)	如果x的值为空，则返回value；否则返回x

eg.

// 若phone为空，查询结果中该行由'Unknown phone number'取代，其余不变
select customer_id,nvl(phone,'Unknown phone number')
from customers;

数字函数

函数	说明
ABS(x)	返回x的绝对值
SIGN(x)	如果x为正，则返回1；如果为负，则返回-1；如果为0，则返回0
CEIL(x)	返回大于或等于x的最小整数
FLOOR(x)	返回小于或等于x的最小整数
ROUND(x[,y])	返回x四舍五入后的结果，如果有参数y表示在小数点后第y位四舍五入
TRUNC(x[,y])	返回x的整数部分，如果有参数y表示在小数点后第y位进行截断
POWER(x,y)	返回x的y次幂
EXP(x)	返回e的x次幂
LOG(x,y)	返回以x为底、y的对数
LN(x)	返回以e为底、x的对数
SQRT(x)	返回x的平方根

转换函数

函数	说明
TO_NUMBER(x[,format])	把x转换成数字
TO_CHAR(x[,format])	把x转换成字符串
TO_DATE(x[,format])	把x转换成日期

eg.

//将dob从日期型转为字符型
select * from customers
where to_char(dob,'yyyy')='1971';

聚合函数

函数	说明
count(x)	统计行数,count(*)会造成效率损失
SUM(x)	用于统计某列的值的总和
AVG(x)	用于统计某列的平均值
MAX(x)	用于统计某一列的最大值
MEDIAN(x)	用于统计某一列的中间值
MIN(x)	用于统计某一列的最小值

eg.

//统计product_id的行数
select count(product_id) 
from products;

聚合函数中，只有count(*)会统计空值(NULL)。