oracle中sql查询

---

一、查询

别名

	   列名  别名      表名
select BOOKID id from BOOKS;

或者

		列名     别名     表名
select BOOKID as id from BOOKS;

拼接

				  ||'拼接字段'||
select BOOKNAME ||'spe_char'||BOOKNAME from BOOKS;

或者不加中间字符

				||
select BOOKNAME||BOOKNAME from BOOKS;

nvl替换null值

			  nvl(选择字段,null替换的值)
select BOOKID,nvl(BOOKCOUNTS,6) from BOOKS;

例如上述，BOOKCOUNTS中的null值变为6

distinct数据去重

select distinct BOOKNAME,BOOKCOUNTS from BOOKS;

只能放在select关键词后面

修改列宽（控制sqlplus）

		列名	格式化  a长度
column BOOKNAME format a15;
或
col BOOKNAME format a15;
查询：
select BOOKNAME from BOOKS;

清楚格式的设置：
clear col; 或 clear column;

2.排序order by

select * 
from BOOKS
		列名正序			列名倒叙
order by BOOKCOUNTS asc , BOOKID desc;

先通过第一个排序，如果有数值相同则通过第二个排序。

3.查询条件where

							条件
select * from BOOKS where BOOKCOUNTS>2;

BETWEEN AND

select * from BOOKS where BOOKCOUNTS BETWEEN 4 AND 6;

in()

select * from BOOKS where BOOKID in(1,3,5);

like

	% 0到多个字符 
	_ 通配一个符
select *  from BOOKS where BOOKNAME like 'J%';

	\ 转义字符 需要escape '\'
select * from BOOKS where BOOKNAME like 
'\\%' escape '\';

is null 或者 is not null 在字段后加

select * from BOOKS where BOOKNAME is not null;

and or 连接条件 ‘或’ ‘与’

3.函数

单行函数

① upper()英文大写
② LOWER(X)X转换成小写
③ LENGTH(X)返回X的长度
④ INITCAP(X) X首字母转换为大写
⑤ SUBSTR(X,start[,length]) ，返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾
⑥ REPLACE(X,old,new) ，在X中查找old，并替换成new

select upper(BOOKNAME) from BOOKS;

聚合函数

avg() 平均 COUNT(*)数值

select avg(BOOKCOUNTS) from BOOKS;

转换函数

TO_CHAR ，把一个数字或日期数据转换为字符
TO_NUMBER ，把字符转换为数字
TO_DATE ，把字符转换为日期

select to_number('100') from dual;
select to_char(sysdate,'yy-mm-dd hh:mi:ss') from dual;

哑表dual

select  1+1 from dual;

日期函数

SQL> alter session set nls_language='simplified chinese';
select sysdate from dual;

4. 多表查询

笛卡尔积

假设集合中 A={a,b} B={1,2},他们的笛卡尔积为：
{(a,1),(a,2),(b,1),(b,2)}

select * from BOOKS,T_USER;

等值连接

select * from BOOKS,T_USER where BOOKID=ID;

不等值连接

select * from BOOKS,T_USER where BOOKID>ID;

外连接（左外连接、右外连接、全连接）

左外连接

select BOOKCOUNTS,AGE from BOOKS left outer join T_USER 
on BOOKID=ID;

将左边BOOKS表中null的书籍也显示了出来

可以简写成这样
select BOOKCOUNTS,AGE 
from BOOKS,T_USER 
where BOOKID=ID(+);

右外连接

select BOOKCOUNTS,AGE from BOOKS right outer join T_USER 
on BOOKID=ID;
或
select BOOKCOUNTS,AGE from BOOKS,T_USER
where BOOKID(+)=ID;

我们想把s_dept表中的多出的一个数据st查询并显示出来，那么就在另一张表上面添加一个加号(+)

全连接

select BOOKCOUNTS,AGE from BOOKS full outer join T_USER
on BOOKID=ID; 

自连接

select B1.BOOKID,B2.FUID BABABOOK
FROM BOOKS B1,BOOKS B2
WHERE B2.BOOKID=B1.BOOKID

查询结果集操作

union 并集

union all 结果集一起显示

minus 第一个结果集出去第二个结果集相同的部分

intersect 去交集

5.聚合函数group by

select 字段1,字段2
from 表 
where 条件 
group by 分组条件 
having 分组筛选条件 
order by 排序条件

select语句执行顺序：
1. from子句，组装来自表的数据，有可能是多张表 2. where子句，基于指定的条件对记录行进行筛选
3. group by子句，将数据划分为多个分组
4. 使用聚合函数对没个小组中的数据进行计算
5. having子句，进行条件筛选，这里可以使用聚合函数的计算结果
6. 计算所有的运算表达式，主要是select部分
7. order by子句，对结果集进行排序
聚合函数能够出现的位置：

1. select后面
2. having后面
3. order by后面