Oracle 資料庫二 基本查詢

来源:http://www.cnblogs.com/cyg-06/archive/2016/11/08/6022934.html
-Advertisement-
Play Games

查詢當前用戶：show user 查看當前用戶下的表：select *from tab; 設置行寬： show linesize;(查看行寬) set linesize 120;(設置行寬) 設置列寬：col 列名 for a8; (設置字元串) col 列名 for 9999;(設置數字的) 查看 ...


查詢當前用戶：show user

查看當前用戶下的表：select *from tab;

設置行寬： show linesize;(查看行寬)     set linesize 120;(設置行寬)

設置列寬：col 列名 for a8; (設置字元串)  col 列名 for 9999;(設置數字的)

查看表的結構：dese emp;

查詢所有的員工信息：select * from emp;

投影：select empno,ename,sal from emp;

濾空函數：select empno,ename,(sal*12+nvl(comm,0)) from emp;

取別名的三種方式：

as "別名"

"別名"

別名  ——不能用關鍵字

去重：distinct

select distinct (列) from emp;

select distince 列,列 from emp;

字元串的連接

select concat('hello','world') from emp;

select 'hello'||'world' from emp;

可用偽表來測試：dual;

where的應用：

　　查詢10號部門的員工：

　　select * from emp where deptno=10;

　　查詢名字叫KING的員工

　　select * from emp where ename='KING';

修改日期格式：

select * from v$nls_parameters;(查詢日期格式)  alter session/system set NLS_DATE_FORMAT='yyyy-mm-dd'; (一般修改當前會話session）

between...and...  在...之間

in/not in

is null 為空的

is not null 不為空的

模糊查詢：

select * from emp

where job like'%H%' 包含了H的 / 'H%' 以H開頭 / '%H' 以H結尾的 / '_H%' 第二位為H的 / '%\_%'escape'\' 找帶有_的;

commit 提交

rollback 事務回滾

a兩個空格;  語句後追加

排序：

　　order by 預設升序(asc) + desc降序

　　order by +列、表達式、別名、序號

　　order by 欄位1,欄位2;   先用欄位1來排序，其中相同的再用欄位2來排

null 是oracle中的最大值

null last 讓空值最後顯示

 

字元函數：

upper　　小寫轉換大寫

cower　　大寫轉換小寫

initcap　　首字母大寫

//select upper('hello') 大寫,lower('HELLO') 小寫,initcap('hello world') 首字母大寫 from dual;

length　　獲取字元的長度

//select length('hello') 長度,lengthb('hello') from dual;

lengthb　　獲取位元組的長度

//select length('中') 長度1,lengthb('中') 長度2 from dual;

replace　　替換

//select replace('hello world','l','a') from dual;

substr　　截取

//select substr('hello world',2,4) from dual;

instr　　查找字元串中某個字元的位置

//select instr('hello world','o') from dual;

lpad　　左補齊

//select lpad(999,10,'*') from dual;      *******999

rpad　　右補齊

trim　　去除前後的某個字元或字元串

select trim('h' from 'hello world') from dual;

 

數值函數：

round　　四捨五入小數

//select round(45.926,2) 數據1,round(45.926,1) 數據2,round(45.926,0) 數據三,round(45.926,-1) 數據4, round(45.926,-2) 數據5 from dual;

trunc　　截斷小數

//select trunc(45.926,2) 數據1,trunc(45.926,1) 數據2,trunc(45.926,0) 數據三, trunc(45.926,-1) 數據4, trunc(45.926,-2) 數據5 from dual;

mod　　除數取餘

//select mod(10,3) from dual;

 

日期函數：

sysdate　　今天

sysdate+1　　明天

sysdate-1　　昨天

// select sysdate - (sysdate -1) from dual;　　今天減去昨天

last_day　　某一月的最後一天

//select last_day(sysdate) from dual;

next_day　　獲取下個星期幾

//next_day(sysdate,"星期三")

add_moths　　追加幾個月

//add_months(sysdate,3)

months_beween　　精確計算月份

//months_beween(sysdate.hiredate)

round　　四捨五入日期

//select round(sysdate,'year') from dual;

truuc　　截斷日期

//select round(sysdate,'month') from dual;

 

轉換函數：

to_char()　　將其他類型的數據轉換為字元串

//select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;

//select to_char(12535,'L99,999') from dual;

to_date：　　將其他格式的數據轉換為日期格式

//select to-date(sydate) from dual;

to_number　　將其他格式的數據轉換為數字

//select to_number('999') from dual;

 

通用函數：

nvl: 慮空函數

nvl2: 是nvl函數的一個加強版 nvl2(a,b,c) 如果a是空的則返回c，否則返回b

//select comm,nvl2(comm,comm,100) from emp;

nullif　　nullif(a,b) 如果a等於b則返回空，否則返回a

//select nullif('hello','world') from dual;

coalesce(a,b,c...)　　從左往右一次查找第一個不為空的值返回

漲工資：

第一種方式：case....end   SQL99語句

select job, sal,

case

when job = 'SALESMAN',sal+800

when job = 'MANAGER' then sal+1000

when job = 'CLERK' then sal + 900

else sal+400

end

from emp;

第二種方式：用decode　　Oracle中獨有的

select job,sal,decode(job,'SALESMAN',sal+800,'MANAGER',sal+1000,'CLERK',sal+900,sal+400) from emp;

 

多行函數

多行函數也稱組函數，自帶濾空功能

nvl(com ,0)　　取消濾空

sum　　求和

count(*)　　統計

//select count(empno) from emp;

avg　　平均值

max　　最大值

min　　最小值

group by　　分組

//select sum(sal) ,avg(nvl(sal,0))

deptno from emp

group by;//

查詢部門平均工資大於2000的部門編號：

//select deptno,avg(sal) 平均工資

  from emp
  group by deptno
  having avg(sal) > 2000;//

求20號部門的平均獎金和獎金總和

//select sum(comm) 總和,avg(comm) 獎金1,sum(comm)/count(*) 獎金2

  from emp
  where deptno=20
  group by deptno

查詢大於本部門的平均工資的員工信息

//select ename,empno,deptno,avg(sal) 平均
  from emp
  group by deptno,ename,empno

//

group by 和where 不能一起使用，可以用having來代替

SQL中的順序

1、select *from emp

2、where

3、group by

4、having

增強的group by   ——rollup()

//select deptno,job,sum(sal)
  from emp
  group by rollup(deptno,job);

//

break on deptno skip 2;　　可用來調整格式

break on null;　　用來取消上面的格式

註意：

 

where後面不能跟組函數

 

如條件中要使用組函數，那麼只能用having條件

 

如果條件不需要使用組函數，可以使用where也可以使用having，推薦使用where

多表查詢：

笛卡爾集：

//select e.empno,e.ename,job,d.deptno,d.dname
  from emp e,dept d

等值鏈接：等值連接 用等號去連接2個表，但是一定要註意連接的2個表一定要有關係

//select e.empno,e.ename,job,d.deptno,d.dname
  from emp e,dept d
  where e.deptno = d.deptno;

非等值連接：

//select e.empno,e.sal,s.grade,s.losal,s.hisal
  from emp e,salgrade s
  where e.sal between s.losal and s.hisal;

//-- 統計每一個部門的人數

 -- 顯示部門號 部門名稱 部門人數

select e.deptno,d.dname,count(*)
from emp e,dept d
where e.deptno = d.deptno
group by e.deptno,d.dname;

外連接：分類左外連接和右外連接　　左外連接以左邊表為主表，主表會顯示所有，另一個表如果沒有顯示空,右外連接以右邊表為主表.....

　　左外連接:  where e.deptno = d.deptno 不成立的時候,無論右邊是否有數據與之對應,左邊的都要顯示  where e.deptno = d.depton(+)

　　select d.deptno,d.dname,count(e.empno)
　　from emp e,dept d
　　where e.deptno = d.deptno(+)
　　group by d.deptno,d.dname;

　　右外連接:  where e.deptno = d.deptno 不成立的時候,無論左邊表是否有數據與之對應,右邊表都會顯示  where e.deptno(+) = d.deptno;

　　select d.deptno,d.dname,count(e.empno)
　　from emp e,dept d
　　where e.deptno(+) = d.deptno
　　group by d.deptno,d.dname;

自連接：只有一張表，虛擬出一張自身表；自連接會產生大量的笛卡爾集，只能做數據量小的表，對於大表用層次查詢

select e.ename 領導名字,e.empno 領導ID,p.ename 員工名字,p.empno 員工ID
from emp e,emp p
where e.empno = p.mgr;

層次查詢：

select e.ename 領導名字,e.empno 領導ID
from emp e
connect by prior empno = mgr
start with mgr is null;

 

子查詢：

子查詢解決什麼問題: 一個查詢查詢不到結果的時候，可以使用子查詢來豐富查詢的條件

子查詢的格式: 用一個小括弧包含,然後在裡面寫sql語句

註意事項：

1. 一定要有小括弧
2. 一定要註意你的書寫格式
3. 子查詢可以放在select,from ,where having,order by 後面
4. 子查詢一定不能放在group by的後面
5. 子查詢和主查詢可以不是同一張表,只要子查詢的結果在主查詢中能用就可以
5. 子查詢中一般都不排序,但是在TOP-N中需要排序
7. 一般是先執行子查詢操作,在執行主查詢操作,但是在相關子查詢中先執行主查詢，在執行子查詢
8. 對於子查詢的結果，如果是單行只能用單行操作符，如果是多行，只能用多行操作符
9. 自查中的null處理

-- 子查詢放在select後面

// select ename,job,(select sal from emp where empno=7566) 子查詢 from emp;

-- from後面放置子查詢 重點要掌握的

//select * from (

　　select ename,job,sal from emp
);

-- where 後面跟子查詢 　　——-- 一定要註意的地方: 子查詢不能過多的去嵌套，一般嵌套三層，加多了就影響性能

//select * from
emp where sal > (
　　select sal from emp where ename = (
　　　　select ename from emp where empno = 7566
)
);

-- order by 後面跟子查詢　　order by 後面不能跟子查詢,語法上是可以跟的，但是跟了不起作用　　-- group by 後面不能跟子查詢

//select * from emp order by (select count(*) from emp where ename='SCOTT');

//select * from emp order by (select empno from emp where ename='SCOTT') desc;

//select * from emp order by (select count(*) from emp where ename='SCOTT') desc;

--子查詢和主查詢可以不是同一張表,只要子查詢的結果在主查詢中能用就可以

 -- 查詢部門名稱為SAL的所有的員工信息

//select *

  from emp
  where deptno = (
  select deptno from dept where dname='SALES'
  );

對於子查詢的結果，如果是單行只能用單行操作符，如果是多行，只能用多行操作符

單行操作符: ><= <= !=

多行操作符 int not in any all

//select * from emp where deptno in(

  　　select deptno from emp where ename='SCOTT' or ename='CLARK'
  );

//select * from emp where deptno not in(

  　　select deptno from emp where ename='SCOTT' or ename='CLARK'
  );

//select * from emp where sal > any(

  　　select sal from emp where deptno = 30
  );

 

集合：借鑒：http://1632004.blog.163.com/blog/static/29991497201282653334529/

註意：集合運算的時候必須要保證每一個集合擁有同樣多的列數，並且每一列的類型必須要一致,如果不一致可以使用to_char(null)或者to_number(null) 來補齊，補齊的時候一定要註意類型

課堂練習：

1、找到員工表中工資最高的前三名

//select rownum r,empno,ename,sal

  from (select empno,ename,sal from emp order by sal,ename,empno

  desc)
  where rownum<=3

rownum　　——偽列   可用來做表的序列號

1、它是按照預設的順序生成，一單生成後不在發生變化

2、它只能使用<  <=，不能使用>   >=

//select * from (
  select rownum r,empno,sal
  from (
  select rownum,empno,sal
  from emp
  order by sal desc
  )
  where rownum < 5
  )
  where r > 2;

rowid　　——也是偽列 返回的是地址，可用來檢索

2、找到員工表中薪水大於本部門平均薪水的員工

//select empno,ename,sal, (select avg(sal) agv from emp where deptno = e.deptno group by deptno) avgsal

  from emp e,(select deptno,avg(sal) agv from emp group by deptno) t
  where e.deptno = t.deptno and e.sal > t.agv

//select empno,ename,sal,(select avg(sal) from emp where deptno = e.deptno) avgsal

  from emp e
  where sal > (
  select avg(sal) from emp where deptno = e.deptno
  );

3、統計每年入職的員工（不能用子查詢）

//select count(ename) total,sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980" from emp;

表相關：

 insert 插入數據

插入一行完整的數據：insert into emp (empno,ename) values (1,'ss');

insert into empvalues (插入的數據)  直接插入值，每一列都要有

更新表裡的數據：update

//update emp set sal=1300 where empno=2015;

//update emp set job ='值',mgr='值' where empno= 2015;

刪除表裡的數據：

//delete from emp where empno =2015;

複製表結構：

//create table 表名 as select * from emp where 1=2;

複製表：

 //create table 表名 as select * from emp;

根據自己的需求創建一張表：

//create table 表名

  (id number,name varchar(20),pwd varchar2(20))

刪除表：

//drop table 表名;

//delete from 表名;

//truncate table 表名;

修改表裡列的名字：

//alter table 表名 rename column 名字 to 修改的名字;

修改表的類型：

//alter table 表名 modfy 列名 類型;

刪除表裡的某一列：

//alter table 表名 drop column 列名;

向表裡追加一列：

//alter table 表名 add 列名 類型;

修改表名（重命名）：

//rename 表名(原) to 表名：

delete 和truncate 的差別：

1、delete是DML操作，truncate是DDL操作

2、delete會產生碎片，而truncate不會

3、delete不會真正的刪除表數據，而truncate會將整個表刪除以後再從新新建一個表

4,、delete不會釋放空間，而truncate會

事物：

將多個操作做成一個原子，要麼這幾個操作都成功，要麼都失敗

事物的開啟和關閉

　　開啟：

　　顯示開啟：start transaction

　　隱示開啟：執行第一個DML的時候會開啟事物

　　關閉;

　　顯示關閉：commit rollback

　　隱示關閉：非正常關閉

創建閃回點：savepoint a;

回滾到閃回點：rollback to a;

其他4個對象：

視圖：

是一個虛擬化的表，是一個邏輯表，本身不包含數據

創建一個視圖：

//create view 名 as select * from emp;

刪除一個視圖：

//drop view 名

清空回收站：purge reyclebin

創建視圖要給用戶授權：

用SYS給Scott用戶授權：

grant create view to scott;

移除授權：

revoke create view from scott;

索引：index

主要用來優化查詢效率 

create index 名 on 表名( 列名)

註意：

通過查看索引的條用日誌才能看到是否調用了索引

索引使用用於經常發生變化的列

索引可以提高查詢的效率，但是不適應於經常變化的列上創建索引

序列：

一組由oracle來負責維護的數組，主要用來做ID的自動增長

創建序列：sequence

create sequence 名

序列的兩個成員變數：nextval(取下一個值),currval(當前的值)

//inser into emp10 (empno,ename) values(mgse.nextval,'ss');

//select mgse.currval from dual;

刪除序列：drop sequeale 名;

複雜的序列創建：

create sequence 名

increment by 2  增加2

start with 100  從100開始

minvalue 100 最小100

maxvalue 200 最大200

nocache  不緩存

同義詞： synonym  別名

主要用來給另外一個用戶的表取別名

create synonym emp,foremp;

Oracle中表的約束條件

1、主鍵約束：用來唯一表示一行記錄

2、非空約束：用來控制某一列不能為空  not null

3、檢查約束：用來控制某一列的值只能是固定幾個

4、外鍵約束：別約束的這一列的值是另一個表的主鍵

5、唯一性約束：有些值唯一


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • InfluxDB學習之InfluxDB的安裝和簡介
    最近用到了 InfluxDB，在此記錄下學習過程，同時也希望能夠幫助到其他學習的同學。 本文主要介紹InfluxDB的功能特點以及influxDB的安裝過程。更多InfluxDB詳細教程請看：InfluxDB系列學習教程目錄 一、InfluxDB 簡介 InfluxDB 是用Go語言編寫的一個開源分 ...
  • MySQL使用if判斷
    select *,if(sva=1,"男","女") as ssva from taname where sva<>"" 12.2. 控制流程函數CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...
  • Greenplum 4.3 For Centos 6.5 安裝指南
    1.greenplum資料庫安裝前期準備工作 1.1 安裝Linux l 記憶體：最小1GB，推薦2GB或以上。 l 交換空間: 記憶體大小是1-2GB時，交換空間是記憶體的1.5倍，記憶體大小2-16GB時，交換空間等於記憶體大小，記憶體大小16G以上時，交換空間為16GB。 l 需要選擇以下組件:GNOME ...
  • 有關binlog的那點事（二）（mysql5.7.13）
    上次，我們僅僅把binlog做了一個概述，並沒有去深入探索（1）binlog file究竟是怎麼構成的？（2）binlog file的單元binlog events是怎麼構成的？（3）我們能不能偽造出一個mysqlbinlog識別的binlog file？ 當然，第三個問題看起來很cool，蠻有挑戰 ...
  • MySQL 指定各分區路徑
    介紹 可以針對分區表的每個分區指定各自的存儲路徑，對於innodb存儲引擎的表只能指定數據路徑，因為數據和索引是存儲在一個文件當中，對於MYISAM存儲引擎可以分別指定數據文件和索引文件，一般也只有RANGE、LIST分區、sub子分區才有可能需要單獨指定各個分區的路徑，HASH和KEY分區的所有分 ...
  • MS SQL巡檢系列——檢查外鍵欄位是否缺少索引
    前言感想：一時興起，突然想寫一個關於MS SQL的巡檢系列方面的文章，因為我覺得這方面的知識分享是有價值，也是非常有意義的。一方面，很多經驗不足的人，對於巡檢有點茫然，不知道要從哪些方面巡檢，另外一方面，網上關於MS SQL巡檢方面的資料好像也不是特別多。寫這個系列只是一個分享，自己的初衷是一個知識... ...
  • CentOS下安裝MySQL
    首先通過網路鏈接的方式線上安裝上mysql伺服器端吧！（備註：我開始登錄伺服器的時候是用的其他用戶而不是超級管理員，所以安裝MySQL的時候需要切換到超級管理員才可以實現軟體的正確安裝。命令則是：su root ，然後輸入你的超級管理員密碼即可登錄到超級管理員終端。） 第一步，安裝MySQL。 [r ...
  • 讀書摘要：第六章 鎖和併發
    1.事務的四個特點，原子性，一致性，隔離性，持久性。2.併發的危險丟失更新：兩個進程讀取同一個數據然後試圖更新會丟失一個進程的更新；臟讀：當一個進程讀取了另一個沒提交進程修改的數據時，如果另一個進程會滾第一個進程就是臟讀；不可重覆讀：在一個事務內不能保證兩次讀取一個數據的結果是一樣的就是不可重覆讀； ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類