Oracle筆記之select查詢

群先 2022-03-30

展開全文

Oracle筆記之select查詢

概述
本篇博文中主要探討以下內(nèi)容：

查詢列(字段）

1.1. 部分列
1.2. 所有列
1.3. 去除重復(fù)
1.4. 別名
1.5. 字符串
1.6. 偽列
1.7. 虛表
1.8. null
查詢行(記錄)

2.1. 比較條件
2.2. 且或非
2.3. null
2.4. 集合操作
2.5. like :模糊查詢
2.6. in 與 exists
2.7. 獲取所有行的記錄
2.8. 排序

SELECT [DISTINCT] {*,column alias,..}
FROM table alias
WHERE 條件表達(dá)式
ORDER BY 排序字段列表 [asc|desc]

解析步驟: from -> where -> select -> order by

1.查詢列(字段）

select distinct *|字段|表達(dá)式 as 別名 from 表 表別名
SELECT * FROM 表名; ->查詢某個(gè)表中所有的記錄的所有字段信息
SELECT 列名 FROM 表名; ->查詢某個(gè)表中所有的記錄的指定字段信息
SELECT 列名1,列名2 FROM 表名; -> 查詢某個(gè)表中所有的記錄的字段1 字段2
SELECT distinct 列名 FROM 表名; ->去除重復(fù)記錄
SELECT 表達(dá)式 FROM 表名; ->查詢表達(dá)式
SELECT xxx as 別名 FROM 表名 表別名 ->使用別名

1.1. 部分列

查詢部分字段，指定的字段名:

--1)、檢索單個(gè)列
select ename from emp; --查詢雇員姓名
--2)、檢索多個(gè)列
select deptno,dname,loc from dept; --查詢部門表
的deptno,dname, loc 字段的數(shù)據(jù)。
--以下查詢的數(shù)據(jù)順序不同(查詢的字段順序代表數(shù)據(jù)順序)
select loc,dname,deptno from dept;
select deptno,dname,loc from dept;

1.2. 所有列

查詢所有的字段通配符 * ( 書寫方便、可以檢索未知列;但是降低檢索的性能) ,數(shù)據(jù)的順序跟定義表結(jié)構(gòu)的順序一致

--1)、檢索所有列1
select * from dept; --查詢部門的所有信息
--2)、檢索所有列2
select deptno,dname,loc from dept; --查詢部門的所有信息

1.3. 去除重復(fù)

使用distinct去重，確保查詢結(jié)果的唯一性

select distinct deptno from emp; --去重

1.4. 別名

使用別名便于操作識(shí)別、隱藏底層信息。存在字段別名和表別名

select ename as '雇員 姓名' from emp;
select ename '雇員姓名' from emp;
select ename 雇員姓名 from emp;
select ename as 雇員姓名 from emp;
select ename as ' Ename' from emp;
select 'my' from emp;sql
select ename||'a'||'-->' info from emp;

as: 字段別名可以使用as;表別名不能使用as
“” : 原樣輸出，可以存在空格與區(qū)分大小寫

1.5. 字符串

使用’’表示字符串(注意區(qū)分””) ，拼接使用 ||

select 'my' from emp;
select ename||'a'||'-->' info from emp;

1.6. 偽列

不存在的列，構(gòu)建虛擬的列

select empno, 1*2 as count,'cmj' as
name,deptno from emp;

1.7. 虛表

用于計(jì)算表達(dá)式，顯示單條記錄的值

select 1+1 from dual;

1.8. null

null 遇到數(shù)字參與運(yùn)算的結(jié)果為 null,遇到字符串為空串

select 1+null from dual;
select '1'||null from dual;
select 1||'2'||to_char(null) from dual;
select ename,sal*12+comm from emp;
--nvl內(nèi)置函數(shù)，判斷是否為null,如果為空，取默認(rèn)值0，否
則取字段實(shí)際值 select ename,sal*12+nvl(comm,0) from emp;

2.查詢行(記錄)

where 過濾行記錄條件 ,條件有

a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
b)、and 、or、 not、 union、 union all、
intersect 、minus
c)、null :is null、 is not null、 not is
null
d)、like :模糊查詢 % _ escape('單個(gè)字符’)
f)、in 、 exists(難點(diǎn)) 及子查詢

2.1. 比較條件

= 、>、 <、 >=、 <=、 !=、 <>

select * from emp where deptno !=20;
select * from emp where deptno <>20;
select * from emp where sal between 800 and950; 
--between and是成對(duì)出現(xiàn)的
--查詢 員工的年薪大于20000的 員工名稱、崗位 年薪
--1)、nvl
select ename,job,12*(nvl(comm,0)+sal) income from emp;
--2)、年薪大于20000
--錯(cuò)誤不能使用別名: select ename,job,12*(nvl(comm,0)+sal) income from emp where income>2000;
--a)、嵌套一個(gè): 查詢?cè)谇?過濾在后
select ename,job,income from (select ename,job,12*(nvl(comm,0)+sal) income from emp) where income>2000;
--b)、不使用別名 (推薦) :過濾在前，查詢?cè)诤?/span>
select ename,job,12*(nvl(comm,0)+sal) income
from emp where 12*(nvl(comm,0)+sal) >2000 ;
--了解 any some all
-- >=any(值列表) 大于最小值<=any(值列表)小于最大值
select * from emp where sal >=any(900,2000);
select * from emp where sal <=any(900,2000);
-- some與any 一樣的效果
-- all 大于最大值 小于最小值
select * from emp where sal >=all(900,2000);
select * from emp where sal <=all(900,2000);
--查詢 工種為’SALESMAN’的員工信息 （注意 內(nèi)容區(qū)分大小寫）
--檢索 工資 大于 2000員工名稱 崗位 工資
--檢索 工資 小于 3000員工名稱 崗位 工資
--檢索 工資 2000, 3000員工名稱 崗位 工資
--查詢部門編號(hào)為20的員工名稱

2.2. 且或非

and、 or、 not


select * from emp where sal>=900 and sal<=950;
--查詢 崗位 為 CLERK 且部門編號(hào)為 20的員工名稱 部門 編號(hào)，工資
--查詢 崗位 為 CLERK 或部門編號(hào)為 20的員工名稱 部門 編號(hào)，工資
--查詢 崗位 不是 CLERK 員工名稱 部門編號(hào)，工資

2.3. null

null不能使用條件判斷，只能使用is

select * from emp where sal <=all(900,2000);
--查詢 工種為’SALESMAN’的員工信息 （注意 內(nèi)容區(qū)分大小寫）
--檢索 工資 大于 2000員工名稱 崗位 工資
--檢索 工資 小于 3000員工名稱 崗位 工資
--檢索 工資 2000, 3000員工名稱 崗位 工資
--查詢部門編號(hào)為20的員工名稱

2.4. 集合操作

Union、Union All、Intersect、Minus

Union，并集(去重) 對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作，不包括重復(fù)行同時(shí)進(jìn)行默認(rèn)規(guī)則的排序；
Union All，全集(不去重) 對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作，包括重復(fù)行，不進(jìn)行排序；
Intersect，交集(找出重復(fù)) 對(duì)兩個(gè)結(jié)果集進(jìn)行交集操作，不包括重復(fù)行，同時(shí)進(jìn)行默認(rèn)規(guī)則的排序；
Minus，差集(減去重復(fù)) 對(duì)兩個(gè)結(jié)果集進(jìn)行差操作，不包括重復(fù)行，同時(shí)進(jìn)行默認(rèn)規(guī)則的排序

--查詢工資大于1500 或 含有傭金的人員姓名
--union 去除重復(fù)行
select ename from emp where sal>1500 union select ename from emp where comm is not null;
-- union all 不去除重復(fù)行
select ename from emp where sal>1500 union all
select ename from emp where comm is not null;
--查詢顯示不存在雇員的所有部門號(hào)。
select deptno from dept minus select distinct deptno from emp
--查詢工資大于1500 且 含有傭金的人員姓名
select ename,sal,comm from emp where sal>1500
intersect
select ename,sal,comm from emp where comm is not null

2.5. like :模糊查詢

模糊查詢，使用通配符:

%:零個(gè)及以上(任意個(gè)數(shù)的)的字符
_:一個(gè)字符
遇到內(nèi)容中包含 % _ 使用escape('單個(gè)字符’)指定轉(zhuǎn)義符

--查詢員工姓名中包含字符A的員工信息
select * from emp where ename like '%A%';
--查詢員工姓名中包含第二個(gè)A的員工名稱信息
select * from emp where ename like '_A%';
--數(shù)據(jù)中 員工姓名中 存在 _ % ,如何查找：
--1)、編寫測(cè)試數(shù)據(jù)
insert into emp(empno,ename,sal) values(1000,'t_%test',8989); 
insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);
--2)、查找
--查詢員工姓名中包含字符%的員工名稱 崗位 工資 部門編號(hào)
select ename,job,sal,deptno from emp where ename like '%a%%' escape('a');
--查詢員工姓名中包含第二個(gè)_的員工名稱 崗位 工資 部門編號(hào)

2.6. in 與 exists

in相當(dāng)于使用or的多個(gè)等值，定值集合 ,如果存在子查
詢，確保類型相同、字段數(shù)為1，如果記錄多，效率不
高，用于一些少量定值判斷上

select * from emp where sal in(900,800);
--子查詢(查詢中再有查詢) in 只能存在一個(gè)字段
select * from emp where sal in (select sal from emp e where deptno=10);
--10或30部門的雇員信息
select * from emp where deptno in(10,30);
--部門名稱為 SALES 或 ACCOUNTING 的雇員信息
select deptno from dept where dname in('SALES','ACCOUNTING'); 
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname IN
('SALES', 'ACCOUNTING'));
/*
便于理解 使用java思維
while(外層結(jié)果集){
	while(內(nèi)層結(jié)果集){
		if(emp.deptno==10){
			syso('....');
		}
		if(emp.deptno==30){
			syso('....');
		}
	}
}
==>in :如果記錄多，效率不高，用于 一些 少量定值判斷上
*/

exists條件為true,存在記錄則返回結(jié)果，后續(xù)不再繼續(xù)比較查詢，與查詢的字段無關(guān)，與記錄有關(guān)

--exists :條件為true,存在記錄，則返回結(jié)果，后續(xù)不再繼續(xù) ,與字段無關(guān)，與記錄有關(guān)
--exists 難點(diǎn): 外層結(jié)果集 內(nèi)層結(jié)果集 關(guān)系列(沒有關(guān)系列 true)
/*
while(外層結(jié)果集){
	while(內(nèi)層結(jié)果集){
		if(emp.deptno==dept.deptno||true){
			syso('....');
			break; //跳出本層
		}
	}
}
*/
--無關(guān)系列
/**
while(emp 的14條記錄){
	while(dept的2條記錄){
		if(true){
			syso();
			break;
		}
	}
}
*/
select *
from emp
where exists
(select deptno,dname from dept where dname in
('SALES', 'ACCOUNTING'));
--加入關(guān)系列
/**
while(emp 的14條記錄){
	while(dept的2條記錄){
		if(e.deptno=d.deptno){
			syso();
			break;
		}
	}
}
*/
select * from emp e where exists (select deptno, dname from dept d where dname in ('SALES', 'ACCOUNTING') and e.deptno = d.deptno);
/**
while(emp 的14條記錄){
	while(dept的2條記錄){
		if(e.deptno!=d.deptno){
		syso();
		break;
		}
	}
}
*/
select *
  from emp e
 where exists (select deptno, dname
  from dept d
 where dname in ('SALES','ACCOUNTING')and e.deptno != d.deptno);
--分析以下結(jié)果
select *
  from emp e
 where not exists(select deptno, dname from dept d
 where dname in ('SALES','ACCOUNTING') and e.deptno = d.deptno);
 
select ename, sal
  from emp
 where sal in (select sal from emp e2 where e2.sal >= 2000);
 
select ename, sal
  from emp
 where exists (select ename, sal from emp e2 where e2.sal >= 2000); --exists
 
select empno, ename, sal
  from emp e1
 where exists (select empno, ename, sal, comm
  from emp e2 where comm is not null and e1.empno = e2.empno);
  
select empno, ename, sal from emp e1
 where exists (select empno, ename, sal, comm
  from emp e2
 where comm is not null and e1.deptno = e2.deptno);

2.7. 獲取所有行的記錄

select * from emp;
select * from emp where 1=1 ;
select * from emp where ename like '%';

2.8. 排序

使用 ORDER BY 排序，排序不是真實(shí)改變存儲(chǔ)結(jié)構(gòu)的順序，而是獲取的集合的順序。

順序 :asc（默認(rèn)） desc
多字段: 在前面字段相等時(shí)，使用后面的字段排序
空排序: 降序?yàn)?desc，注意 null 為最后

--按工資降序
select * from emp order by sal desc;
--null問題
select * from emp order by nvl(comm,0),comm desc;
select * from emp order by comm nulls first;
--查詢雇員姓名，年薪 按傭金排序 默認(rèn)為升序(asc)，降序?yàn)閐esc，注意null為最后
select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm desc;
--查詢雇員姓名，年薪 按傭金排序 默認(rèn)為升序(asc)，降序?yàn)閐esc，注意null為最后
select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm desc;
--對(duì)部門編號(hào)為 20 或30的雇員，工資+傭金 進(jìn)行升序排序，如果相同，則按姓名降序。
--1、查詢20、30 雇員
select * from emp where deptno in(20,30);
--2、工資+傭金排序
select ename,sal,comm,sal+nvl(comm,0) c from emp where deptno in(20,30) order by c;
--3、多個(gè)字段排序使用, 排序的字段可以使構(gòu)建出來的虛擬的字段
select ename,sal,comm from emp where deptno in(20,30) order by sal+nvl(comm,0),ename desc;

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：群先 > 《條件查詢》

舉報(bào)/認(rèn)領(lǐng)