5 Oracle的基本查詢、過濾和排序查詢

5.1 解決SqlPlus出現中文亂碼的問題

• 之前使用的是hr登錄，現在使用設定的另外一個登錄
• 使用scott帳號登錄【sqlplus scott/scott@192.168.10.34:1521/orcl】
  
• 起別名的時候發現中文亂碼問題
  

第一步

• 在sqlplus中執行 select userenv(‘language’) from dual;
• 查看當前資料庫的字符集為：AMERICAN_AMERICA.ZHS16GBK
  
• 我們只需要把客戶端字符集和作業系統字符集都設定成：SIMPLIFIED CHINESE_CHINA.ZHS16GBK 就行了

第二步

• 在系統環境變數中查找一個名為“NLS_LANG”的環境變數，如果找不到，則新建一個，把變數值賦值為：“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
  
• 關閉dos終端，重寫打開（中文亂碼問題解決）
  

5.2 Oracle基本查詢陳述句

spool 保存sql到檔案

1. 保存sql陳述句到檔案【spool C:\Users\shuyy\Desktop\a.txt】
2. spool off 寫入內容到檔案【要在spool C:\Users\shuyy\Desktop\a.txt 與spool off 之間寫入需要保存到檔案中的sql，spool off關閉后，再寫的sql將不會寫入指定檔案中】
   

host cls 清屏

show user 顯示當前登錄用戶

select * from tab; 顯示當前用戶下的表

• Oracle與MySQL資料庫不同，不會像MySQL那樣顯示資料庫，然后使用
  
• Oracle資料庫會直接顯示當前用戶權限所能看到的表【這里的表是Oracle默認提供的一些表】
  

desc 查詢表的結構【desc emp;】

show linesize 顯示行寬

• 查詢emp表發現因為行寬問題，顯示得很不美觀
  
  

set linesize 150 設定行寬

• 美觀很多
  

col ename for a8 設定ename列寬，顯示8個字符，a表示字符

col sal for 9999 設定sal列顯示4個數字，9表示數字

• 999表示顯示3個數字，發現4個數字的資料變成了####
  

/ 表示執行上一條陳述句

c Change 命令，輸錯陳述句時用【如: c /form/from】

* + 乘法和加法運算

#查詢員工資訊： 員工號  姓名 月薪 
select empno "員工號", ename "名字",sal "月薪" from emp;

#查詢員工資訊： 員工號  姓名 月薪 年薪 獎金 年收入
select empno "員工號", ename "名字",sal "月薪", sal*12 "年薪",comm "獎金", sal*12+comm "年收入" from emp;

• 【發現這樣查詢有問題，年薪與獎金為null的資料相加結果是null，顯然這樣是不符合要求的】
  

#讓comm獎金里面的null變成0【nvl(comm,0)】
select empno "員工號", ename "名字",sal "月薪", sal*12 "年薪",comm "獎金", sal*12+nvl(comm,0) "年收入" from emp;

查詢表中的null值

• MySQL可以這樣書寫【Oracle中這樣寫會報錯】：select * from emp where comm = null;
• select * from emp where comm is null;
  

as 列的別名

select empno as "員工號", ename "名字",sal "月薪", sal*12 "年薪",comm "獎金", sal*12+nvl(comm,0) "年收入" from emp;

ed 進入寫入快取檔案 file afiedt.buf【方便修改長sql陳述句】

• 修改完成后Ctrl+s保存，再關閉檔案，輸入 / 即可，表示執行上一條sql陳述句
  

distinct 1.去除重復 2.作用于后面所有的列

#去除重復
select distinct deptno from emp;

#作用于后面所有的列
 select distinct deptno, job from emp;

concat 字串連接

• dual表示一個虛表

select concat('abc','def') from dual;

dual 表示一個虛表

select 3+2 from dual;

|| 也表示字串連接

select 'abc'||'aaa' from dual;

 select ename|| '的工資是'||sal from emp;

5.3 Oracle的過濾和排序查詢

字符大小寫敏感

#如查詢員工為SMITH的資訊
select * from emp where ename='SMITH';
select * from emp where ename='SMITh';

日期格式敏感

#查看當前的日期格式
select * from v$nls_parameters;
#修改當前會話的日期格式【下一次打開還是原來的格式】
#java中是yyyy-MM-dd
alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
#查詢日期
select * from emp where hiredate='17-12月-80';
select * from emp where hiredate='1980-12-17';

between and

#查詢薪水1000~2000之間的員工，結果是包含1000和2000的
select * from emp where sal between 1000 and 2000;
#注：含有邊界 小值在前 大值在后

in 在集合中、not in 不在集合中

#查詢10和20號部門的員工
select * from emp where deptno in(10,20);
#查詢不在10和20號部門的員工
select * from emp where deptno not in(10,20);
#注：如果集合中含有null，不能使用not in; 但可以使用in

like 模糊查詢【與mysql類似】

#查詢名字以S打頭的員工
select * from emp where ename like 'S%';
#查詢名字是4個字的員工，4個下劃線即可
select * from emp where ename like '____';
#查詢名字中含有下劃線的員工 需要轉義字符
select * from emp where ename like '%\_%' escape '\';

rollback 回滾

• Oracle是自動開啟事務，不同于mysql需要手動開啟【start transaction;】

order by 排序

#1.查詢員工資訊 按照月薪排序【默認升序】
select * from emp order by sal;
#2.按年薪降序排序【order by后面  + 列，運算式，別名，序號】
select empno,ename,sal,sal*12 from emp order by sal*12 desc;

#根據別名排序
select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;
#根據列數排序
select empno,ename,sal,sal*12 年薪 from emp order by 9 desc;

#3.按部門升序然后按工資降序排序員工資訊
select * from emp order by deptno,sal desc;
#4.按部門降序然后按工資降序排序員工資訊
select * from emp order by deptno desc,sal desc;
#注：order by 作用于后面所有的列；desc只作用于離它最近的列

#6.查詢員工資訊  按照獎金降序排序，null值是最大，所以會排在最前面
select * from emp order by comm desc;
select * from emp order by comm desc nulls last;

set pagesize 設定分頁大小

#設定分頁大小
set pagesize 20;