一、oracle基本sql語句和函數詳解

来源:http://www.cnblogs.com/u931455/archive/2016/04/11/5377437.html
-Advertisement-
Play Games

一、 數據定義語言(ddl) 數據定義語言ddl(data definition language)用於改變資料庫結構，包括創建、更改和刪除資料庫對象。 用於操縱表結構的數據定義語言命令有： create table alter table truncate table drop table eg、 ...


一、 數據定義語言(ddl)

數據定義語言ddl(data definition language)用於改變資料庫結構，包括創建、更改和刪除資料庫對象。

用於操縱表結構的數據定義語言命令有：

create table

alter table

truncate table

drop table

eg、

--創建tb_stu表數據結構

create table tb_stu(

id number,

name varchar2(20)

);

 

--修改tb_stu表數據結構，新增一列

alter table tb_stu add pwd varchar2(6);

--修改欄位

alter table tb_stu modify pwd varchar2(20);

--重命名錶: rename table_name1 to table_name2;

RENAME student TO tb_student;

--重命名列：alter table table_name rename column col_oldname to colnewname;

ALTER TABLE student RENAME COLUMN pwd TO pwd1;

--刪除欄位

alter table tb_stu drop column pwd;

 

 

--刪除tb_stu表數據

truncate table tb_stu;

 

--刪除tb_stu表

drop table tb_stu;

二、 數據操作語言(dml)

數據操縱語言dml(data manipulation language)用於檢索、插入和修改數據，dml利用insert、select、update 及 delete 等語句來操作資料庫對象所包含的數據。

(1)、利用現有的表創建表

語法：create table <new_table_name> as

select column_names from <old_table_name>;

eg、

1)、create table tb_dept as select * from dept;

2)、create table tb_dept as select a.deptno, a.dname from dept a;

3)、create table tb_dept as select * from dept a where a.deptno=10;

 

(2)、選擇無重覆的行，使用distinct關鍵字

eg、select distinct a.dname from tb_dept a;

 

(3)、使用列別名

select a.deptno 部門編號, a.dname "部門 名稱" from tb_dept a;

--中間有空格，用""

 

(4)、插入來自其他表中的記錄

語法：insert into <table_name> [(cloumn_list)]

select column_names from <other_table_name>;

eg、insert into student2 select * from student;

 

三、 事務控制語言(tcl)

放到“事務”講解

四、 數據控制語言(dcl)

放到“用戶和許可權”講解

五、 sql操作符

略

六、 oracle函數

1、字元函數

initcap(char)函數：首字母大寫

eg、select initcap('hello') from dual;

輸出結果：Hello

 

lower(char)函數：字母全部轉換為小寫

eg、select lower('heLLo') from dual;

輸出結果：hello

 

upper(char)函數：字母全部轉換為大寫

eg、select upper('hello') from dual;

輸出結果：HELLO

 

ltrim(char,set)函數：去掉左邊指定的字元

eg、select ltrim('xyzadmin','xyz') from dual;

輸出結果：admin

 

rtrim(char,set)函數：去掉右邊指定的字元

eg、select rtrim('xyzadmin','admin') from dual;

輸出結果：xyz

 

translate(char, from, to)函數：返回將from中的每個字元替換為to中相應字元以後的string

eg、1)、select translate('jack','j','b') from dual; --將j替換為b

輸出結果：back

2)、select translate('123abc','2dc','4e') from dual;

輸出結果：143ab

解析：2替換為4，

d因為字元串里沒有，所以不作替換，

c由於沒有對應的替換字元，所以字元串里的c會被刪除

 

replace(char, searchstring, [rep string]) 函數：替換

eg、select replace('jack and jue' ,'j', 'bl') from dual;

輸出結果：black and blue

 

instr(char, m, n)函數：返回截取的字元串在源字元串中的位置，沒有返回0

eg、select instr ('worldwide', 'd') from dual;

輸出結果：5

instr(C1,C2,I,J) -->判斷某字元或字元串是否存在，存在返回出現的位置的索引，否則返回小於1;在一個字元串中搜索指定的字元,返回發現指定的字元的位置;
C1 被搜索的字元串
C2 希望搜索的字元串
I 搜索的開始位置,預設為1
J 出現的位置,預設為1

 

substr(char, m, n)函數：截取字元串

eg、select substr('abcdefg', 3, 2) from dual;

輸出結果：cd

 

concat(expr1, expr2)函數：連接字元串

eg、select concat('Hello',' world') from dual;

輸出結果：Hello world

 

chr(number)函數：給出整數,返回對應的字元;

eg、select chr(54740), chr(65) from dual;

輸出結果：趙 A

 

lpad和rpad函數：粘貼字元

lpad在列的左邊粘貼字元

rpad在列的右邊粘貼字元

eg、SELECT lpad('林計欽', 10, '*') FROM dual;

輸出結果：****林計欽

註意：不夠字元則用*來填滿

 

trim函數：去除字元串左右兩邊的空字元串；如果不指定,預設為空格符。

 

length(char)函數：返回字元串的長度

eg、select length('abcdef') from dual;

輸出結果：6

2、日期時間函數

add_months函數：增加或減去月份

eg、

select to_char(add_months(to_date('2011-11-30','yyyy-mm-dd'),2),'yyyy-mm-dd') from dual;--前進

輸出結果：2012-01-31

select to_char(add_months(to_date('2011-11-30','yyyy-mm-dd'),-2),'yyyy-mm-dd') from dual;--後退

輸出結果：2011-9-30

 

months_between(date2,date1)函數：給出date2-date1的月份

eg、select months_between('19-12月-1999','19-3月-1999') from dual;

輸出結果：9

 

last_day函數：返回日期的最後一天

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

輸出結果：2011-11-30 23:27:20

 

round和trunc函數：按照指定的精度進行四捨五入

eg、select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

輸出結果： 56 -55 55 -55

 

next_day(date,'day')函數：給出日期date和星期幾計算下一個星期的日期

eg、select next_day('30-11月-2011','星期三') next_day from dual;

輸出結果：07-12月-11

 

extract(datetime)函數：獲取時間函數

eg、

輸出結果：

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; --獲取年

SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; --獲取月

SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; --獲取日

select to_char(sysdate,'hh24') from dual; --獲取小時

select to_char(sysdate, 'mi') from dual;--獲取分鐘

select to_char(sysdate, 'ss') from dual;--獲取秒

 

select extract(year from systimestamp) year

,extract(month from systimestamp) month

,extract(day from systimestamp) day

,extract(minute from systimestamp) minute

,extract(second from systimestamp) second

,extract(timezone_hour from systimestamp) th

,extract(timezone_minute from systimestamp) tm

,extract(timezone_region from systimestamp) tr

,extract(timezone_abbr from systimestamp) ta

from dual;

3、數字函數

abs函數：絕對值

eg、select abs(-15) from dual;

輸出結果：15

round(m, n)四捨五入函數：

eg、select round(45.926, 2) from dual;

輸出結果：45.93

select round(45.926, -1) from dual;

輸出結果：50

select round(245.926, -2) from dual;

輸出結果：200

select round(275.926, -2) from dual;

輸出結果：300

 

trunc(m, n)截取函數

一、日期

trunc函數為指定元素而截取的日期值。

其具體的語法格式如下：

TRUNC（date[,fmt]）

其中：date 一個日期值

fmt 日期格式，該日期將由指定的元素格式所截去。忽略它則由最近的日期截去

如果當日日期是：2011-3-18

select trunc(sysdate) from dual --2011-3-18 今天的日期為2011-3-18

select trunc(sysdate, 'mm') from dual --2011-3-1 返回當月第一天.

select trunc(sysdate,'yy') from dual --2011-1-1 返回當年第一天

select trunc(sysdate,'dd') from dual --2011-3-18 返回當前年月日

select trunc(sysdate,'yyyy') from dual --2011-1-1 返回當年第一天

select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回當前星期的第一天

select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 當前時間為14:41

select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函數沒有秒的精確

 

二、數字

trunc（number,num_digits）

number 需要截尾取整的數字。

num_digits 用於指定取整精度的數字。num_digits 的預設值為 0。

trunc()函數截取時不進行四捨五入

 

select trunc(123.458) from dual --123

select trunc(123.458,0) from dual --123

select trunc(123.458,1) from dual --123.4

select trunc(123.458,-1) from dual --120

select trunc(123.458,-4) from dual --0

select trunc(123.458,4) from dual --123.458

select trunc(123) from dual --123

select trunc(123,1) from dual --123

select trunc(123,-1) from dual --120

 

mod(m,n)求餘函數

eg、select mod(5,2) from dual;

輸出結果：1

 

ceil(n)函數：取大於等於數值n的最小整數

eg、select ceil(44.778) from dual;

輸出結果：45

 

floor(n)函數：取小於等於數值n的最大整數

eg、select ceil(44.778) from dual;

輸出結果：44

4、轉換函數

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

select to_date('2011-12-01 10:33:56', 'yyyy-mm-dd hh24:mi:ss') from dual;

select to_number('1000') from dual;

5、混合函數

nvl(string1, replace_with)功能：如果string1為null，則nvl函數返回replace_with的值，否則返回string1的值。 　　

註意事項：string1和replace_with必須為同一數據類型，除非顯示的使用to_char函數。

 

nvl2(e1, e2, e3)的功能為：如果e1為null，則函數返回e3，否則返回e2。

 

nullif(exp1,expr2)函數的作用是如果exp1和exp2相等則返回空(null)，否則返回第一個值。

eg、select nullif('a', 'a') from dual;

輸出結果：返回空，無值

select nullif('a', '1') from dual;

輸出結果：a

 

6、分組函數

avg(n)、min(n)、max(n)、sun(n)、count(n)

八、oracle多表查詢

 

多表聯合查詢

通過連接可以建立多表查詢，多表查詢的數據可以來自多個表，但是表之間必須有適當的連接條件。為了從多張表中查詢，必須識別連接多張表的公共列。一般是在WHERE子句中用比較運算符指明連接的條件。

 

兩個表連接有四種連接方式：

* 相等連接

* 不等連接(看作單表查詢)

* 外連接(左連接、右連接；左連接即左表全部顯示，右表只顯示匹配的信息，右連接反之。)

* 自連接(自關聯)

 

1．相等連接

通過兩個表具有相同意義的列，可以建立相等連接條件。使用相等連接進行兩個表的查詢時，只有連接列上在兩個表中都出現且值相等的行才會出現在查詢結果中

顯示雇員名稱和所在部門的編號和名稱。

執行以下查詢：

SELECT a.ename, b.id, b.dname

FROM employee a,dept b

WHERE a.id=b.id

說明：相等連接語句的格式要求是，在FROM從句中依次列出兩個表的名稱，在表的每個列前需要添加表名，用“.”分隔，表示列屬於不同的表。在WHERE條件中要指明進行相等連接的列。

以上訓練中，不在兩個表中同時出現的列，前面的表名首碼可以省略。所以以上例子可以簡化為如下的表示：

SELECT *

FROM emp e, dept d

WHERE e.deptno=d.deptno;

 

2．外連接

在以上的例子中，相等連接有一個問題：如果某個雇員的部門還沒有填寫，即保留為空，那麼該雇員在查詢中就不會出現；或者某個部門還沒有雇員，該部門在查詢中也不會出現。

為瞭解決這個問題可以用外連，即除了顯示滿足相等連接條件的記錄外，還顯示那些不滿足連接條件的行，不滿足連接條件的行將顯示在最後。外連操作符為(+)，它可以出現在相等連接條件的左側或右側。出現在左側或右側的含義不同，這裡用如下的例子予以說明。

 

使用外連顯示不滿足相等條件的記錄。

顯示雇員名稱和所在部門的編號和名稱。

執行以下查詢：

左連接方法一(推薦使用，簡潔)：

--+號(附表)對面的是主表，可以理解為主表全部顯示，+號這邊如果沒有匹配就顯示空值

--不管dept是否存在，emp都會顯示(emp當主表)

SELECT * FROM emp e, dept d WHERE d.deptno(+)=e.deptno; --左連接

SELECT * FROM emp e, dept d WHERE e.deptno=d.deptno(+); --右連接

--不管emp是否存在，dept都會顯示(dept當主表)

SELECT * FROM emp e, dept d WHERE e.deptno(+)=d.deptno; --左連接

SELECT * FROM emp e, dept d WHERE d.deptno=e.deptno(+); --右連接

 

左連接方法二：

--不管dept是否存在，employee都會顯示

SELECT * FROM emp e LEFT JOIN dept d ON d.deptno=e.deptno;

SELECT * FROM dept d RIGHT JOIN emp e ON d.deptno=e.deptno;

 

3、自連接(一般用在樹形許可權結構中)

自連接就是一個表，同本身進行連接。對於自連接可以想像存在兩個相同的表(表和表的副本)，可以通過不同的別名區別兩個相同的表。

SELECT worker.ename||' 的經理是 '||manager.ename AS 雇員經理

FROM employee worker, employee manager

WHERE worker.mgr = manager.empno;

 

------------

執行結果為：

1.SMITH 的經理是 FORD

2.ALLEN 的經理是 BLAKE

3.WARD 的經理是 BLAKE

 

 

註：在操作多表聯合查詢時，若出現以下情況，將形成笛卡爾積

– 聯接條件被省略

– 聯接條件無效

– 第一個表中的所有行被聯接到第二個表中的所有行上

為了避免笛卡爾積，請始終包括有效的聯接條件

 

何為笛卡爾積？

笛卡爾（Descartes）乘積又叫直積。假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以擴展到多個集合的情況。

 

九、集合連接

十、擴展

1、cmd控制台登錄oracle資料庫：sqlplus scott/oracle@orcl

 

2、獲取當前時間

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

輸出結果值：2011-11-30 19:40:55

 

獲取當前時間(小數點精確到後面6位,6是可變的)

-->select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') from dual;

輸出結果值：2011-11-30 19:45:35.791000

 

3、查看表結構：desc dept;

 

4、修改日期格式：alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';(註意：只在當前會話中生效)

 

5、兩種插入空值方式

方法一：insert into tb_stu values(1, null, to_date('20111130', 'yyyymmdd'));

方法二：insert into tb_stu(id, birthday) values(1, to_date('20111130', 'yyyymmdd'));

 

6、清屏：clear screen;

​

7、oracle中兩個單引號表示一個單引號

eg、update tb_stu set pwd='1''2' where id=1;

 

8、oracle匹配

eg、select * from tb_stu where pwd like 'A%' --匹配多個

select * from tb_stu where pwd like 'A_' --匹配一個

 

9、drop table tb_dept purge; --永久刪除

drop table tb_dept; --把表放入回收站，並沒有徹底的刪除表

 

10、select to_char(33, 'L99.99') from dual; --獲取本地貨幣符號，輸出結果為：￥33.00

select to_char(33, '$99.99') from dual; --獲取美元

 

11、oracle中為空的兩種表達方式

select '' from dual;

select null from dual;

 

12、查詢當前資料庫中的所有表名

select * from tab;


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

-Advertisement-
Play Games
更多相關文章
  • HTML5學習筆記三 HTML元素、屬性、標題、段落
    一、HTML 元素 HTML 元素以開始標簽起始 HTML 元素以結束標簽終止 元素的內容是開始標簽與結束標簽之間的內容 某些 HTML 元素具有空內容（empty content） 空元素在開始標簽中進行關閉（以開始標簽的結束而結束） 大多數 HTML 元素可擁有屬性 嵌套的 HTML 元素 HT ...
  • 另類提示框
    我這裡說的提示框，就是當用戶將滑鼠移動到需要提示的圖標時，就會在這圖標的位置出現一個提示框了。 咦，那這有什麼好說的呢？ 如果你來實現這一效果，你會怎麼做呢？ 初步的做法嘛，就是利用PS製作一張提示框內容區域的png圖片和一張指向位置的箭頭png圖片，然後利用這張圖片作為提示背景，裡面輸入指定內容唄 ...
  • 框架設計—選擇器模塊
    一個禮拜沒動靜了，實在是懶惰。。 好了，不扯淡了，進入正題：框架封裝之選擇器模塊。 首先，我們為什麼要封裝框架？ 淺顯的文字不具有良好的說服性，來做幾個題目吧： 求一個數組所有項之和 2. 求數組中最大值 3. 獲取數組中指定值 ok,題目做到這就行了，我們可以看出每道題目中都用到了for迴圈，每次 ...
  • HTML5學習筆記二 HTML基礎
    一、HTML 標題 HTML 標題（Heading）是通過<h1> - <h6> 標簽來定義的. 二、HTML 段落 HTML 段落是通過標簽 <p> 來定義的. 三、HTML 鏈接 HTML 鏈接是通過標簽 <a> 來定義的.在 href 屬性中指定鏈接的地址。 四、HTML 圖像 HTML 圖像 ...
  • Array類型
    Array類型 也是 中常用類型之一，其特點是數組中的每一項都可以保存任何類型的數據，數組的大小可以動態調整。 創建數組 方式1：使用 構造函數 var books = new Array(); var books = new Array(20); //如果知道數組的大小，可以給構造函數傳遞該參數 ...
  • 《JavaScript面向對象編程指南》
    第一章、引言 1.5 面向對象的程式設計常用概念 對象 （名詞）：是指“事物”在程式設計語言中的表現形式。 這裡的事物可以是任何東西，我們可以看到它們具有某些明確特征，能執行某些動作。 這些對象特征就叫做屬性（形容詞），動作稱之為方法（動詞）。 類： 實際上就是對象的設計藍圖或製作配方。類更多的是一 ...
  • Atitit.跨語言異常轉換機制 java c# php到js的異常轉換
    Atitit.跨語言異常轉換機制 java c# php到js的異常轉換 1. bizEx 直接抓取，然後js catchEX1 2. Chkec runtimeEx1 3. Other異常。。Js convet 2 js err,then throw ...2 1. bizEx 直接抓取，然後js ...
  • atitit.React   優缺點 相比angular react是最靠譜的web ui組件化方案了
    atitit.React 優缺點 相比angular react是最靠譜的web ui組件化方案了 1. React的組件化才是web ui部件的正確方向1 1.1. 組件化集成html ,css,js自我包含一體化，方便復用。1 1.2. 相比angular。Js方便好用1 2. React的問題 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類