整體介紹

SQL語言是一種數(shù)據(jù)庫語言

?1、DDL：數(shù)據(jù)定義語言

create-創(chuàng)建? drop-刪除 alter-修改 rename-重命名 truncate-截斷

2、DML：數(shù)據(jù)操作語句

insert-插入 delete-刪除 update-更新 select-查詢

3、DCL：數(shù)據(jù)控制語句

grant-授權 revoke-回收權力 commit-提交事務 rollback-回滾事務

注：Oracle命令不區(qū)分大小寫，但數(shù)據(jù)和內容區(qū)分?

一、select語法

1、基本結構和簡單查詢

基本結構：select 查詢的信息 from 數(shù)據(jù)來源

select * from emp

select deptno，sal，ename from emp

*：表示查詢該表的所有數(shù)據(jù)

查詢的信息可以是*也可以是一個或多個字段名；多個字段之間用逗號分隔，字段的順序即查詢結果順序

2、去重（distinct）

select distinct deptno from emp；

distinct去重是去除的整條記錄的重復

3、別名

給列取別名有兩種寫法：

寫法1：?字段名后跟空格和別名

select ename 姓名，sal 工資 form emp

寫法2：使用as關鍵字

select ename as 姓名，sal as 工資 from emp；

4、排序（order by）

降序：desc? 升序：asc（默認升序排序）

select ename，sal from emp order by sal desc

多字段排序：用逗號分隔

select ename，sal from emp order by deptno asc，sal desc

解釋：查詢emp表中的ename，sal字段，查詢結果集按照先deptno字段值升序排序，在deptno升序的基礎上，按照sal降序排序；

5、偽列

生成表中沒有的數(shù)據(jù)列稱為偽列

select ename，sal，sal*2 年薪 from emp

其中年薪屬于別名而sal*2是表中沒有的數(shù)據(jù)，是通過計算得到，所以稱為偽列

6、字符串拼接 ||

select ename，ename || ‘a’ 別名 from emp

注：兩個變量拼接時，如a||b，當a為null則結果為b

7、虛表 dual

只有一行一列，是用來構成select的語法規(guī)則，可以執(zhí)行插入、更新、刪除操作，還可以執(zhí)行drop操作

注：雖然可以執(zhí)行drop操作，但是還是不建議對dual執(zhí)行drop操作，否則會使系統(tǒng)不能用、起不了數(shù)據(jù)庫。

select 11*22 from dual

8、select 執(zhí)行順序

?select distinct 查詢字段1 別名，查詢字段2 as 別名，表達式 別名 from 數(shù)據(jù)來源 order by 排序字段 desc

?以上是之前知識的綜合語句：

解析順序：from====》select====》order by

先找表，再找需要查詢的內容，最后對結果集排序

9、條件查詢*

結構：select 查詢內容 from 數(shù)據(jù)來源 where 行記錄條件

?解析過程：1、查詢數(shù)據(jù)源 2、判斷是否符合行記錄條件 3、將滿足條件的記錄在select指定的信息中存放（結果集當中）

條件運算

=、>、<、>=、<>、!=、between and、in

基礎結構：

select * form emp where deptno=10；

?between...and... 值在一定范圍內、閉區(qū)間（包含兩段界限）

select * from emp where sal between 2000 and 4000

in（a，b）滿足a或b都可以

select * from emp where deptno in（10，20）

條件連接運算

and、or、not

and 同時滿足

select * from emp where sal>1500 and deptno=20;

?or 滿足一個即可

select * from emp where sal>1500 or deptno=20;

?not 取反

select * from emp where not deptno=20;

10、null處理

nvl（expl，res）

null在參加計算式，結果為null，使用nvl（expl，res）處理空值：expi-表達式、res-結果

當expi不為空時，結果為expi，為空時結果為res

select ename，sal，comm，sal+nvl（comm，0）月收入 from emp

null排序

nulls first、nulls last 將空值放置在列前或后

select * from emp order by comm desc nulls first

null查詢

不為空

select * from emp where comm is not null；

?為空

select * from emp where comm is null；

?不為空

select * from emp where not comm is null；

11、模糊查詢 like

select * from emp where like ‘%s%’

%表示任意個字符? _表示一個任意字符

當模糊查詢的內容包含”%“、”_“時

select * from emp where like ‘%a%%’ escape（‘a’）

escape中的a屬于標識符，用于證明a后的字符不是特殊字符，只對其身后的一位字符有效

如想查詢‘a%’則，...like ‘%aaa%%’ escape（‘a’）

12、where子句

select * from emp where deptno = （select deptno from dept where dname = ‘SALES’）

二、函數(shù)

1、函數(shù)介紹

根據(jù)函數(shù)的返回結果，分為單行函數(shù)和多行函數(shù)。

單行函數(shù)：一條記錄返回一個結果。

多行函數(shù)（組函數(shù)、聚合函數(shù)）：可同時對多條記錄進行操作，并返回一個結果。

2、常用的單行函數(shù)

字符函數(shù)

concat（x，y）：連接字符串x和y

instr（x，str，start，n）：在x中查找str，從start開始，也可以從第n次開始。初始位置從1開始。

select instr（‘HelloWorld’，‘l’，1，3）from dual

length（x）：返回x的長度

lower（x）：x轉換為小寫

upper（x）：x轉換為大寫

itrim（x，trim_str）：把x左邊截去trim_str字符串，沒有寫trim_str就截去空格

replace（x，old，new）：在x中，將old替換為new，替換的是能找到的所以old

substr（x，start，length）：從start開始，截取length個字符，缺length默認到結尾

日期函數(shù)

sysdate、current_date：當前日期（精確到秒）

add_months(d1,n1)：在d1基礎上再加n1個月后的日期

last_day（d1）：放回d1所在月份的最后一天

months_between（d1，d2）：從d1到d2之間的月數(shù)

next_day(d1[ ,c1])：d1日期的下周星期幾（c1）

轉換函數(shù)

to_char(x,c)：將日期或數(shù)據(jù)x按照c格式轉換

to_date(x,c)：將字符串x按照c的格式轉換為日期

to_number(x)：將字符串x轉為數(shù)字型

3、組行數(shù)

avg（）平均值、sum（）求和、min（）最小值、max（）最大值、count（）統(tǒng)計

注：null不參與運算

group by 分組

結構：select...from...group by....

select deptno，avg（sal）from emp group by deptno

having 過濾組信息

select deptno，avg（sal）from emp group by deptno having avg（sal）>2000

執(zhí)行順序：①找到數(shù)據(jù)源from emp ②對數(shù)據(jù)源進行分組group by deptno ③對分組內容進行過濾having avg（sal）>2000 ④從分組中查詢數(shù)據(jù)select deptno，avg（sal）

過濾行記錄和組信息

行記錄的過濾是針對每條記錄的篩選，組信息的過濾是針對組的篩選，是可以同時出現(xiàn)的先篩選行，再過濾組

結構：⑤select...①from...②where...③group by...④having...（序號代表執(zhí)行順序）

三、分頁和去重

1、分頁

假分頁：一次查詢出數(shù)據(jù)庫中的所有記錄，然后在每頁中顯示指定的記錄

缺：服務器負載大 優(yōu)：與數(shù)據(jù)庫只對接一次

真分頁：對數(shù)據(jù)庫多次查詢，每次只獲得本頁的數(shù)據(jù)并顯示

缺：頻繁操作數(shù)據(jù)庫 優(yōu)：服務器負載小

2、rownum

是對每一個結果集中的每一條記錄的編號，從1開始

查詢偽列rownum

select ename，sal，deptno，rownum from emp

將查詢出的結果集作為臨時的數(shù)據(jù)來源，此時結果集中的rownum rw為普通字段

select * from（select ename，sal，deptno，rownum rw from emp）where rw>5 and rw <=10

3、排序分頁（每頁只展示3條數(shù)據(jù)）

select ename，sal，deptno，r1，r2
  from（select ename，sal，deptno，r1，rownum r2
    from（select ename，sal，deptno，rownum r1
      from emp order by sal desc））
where r2>(3-1)*3 and r2<=3*3

4、rowid去重

rowid相當于偽列，由Oracle生成，是唯一的

delete from copy where rowid not in（select min（rowid）from copy group by deptno）

四、表連接

概念：多表查詢-多表拼接

1、92語法

結構：select...from table1,table2...where

很多時候需要為表取別名（①簡化表名②可能存在自連接的情況）

原理：from后面出現(xiàn)的順序，前面的表作為內存的for循環(huán)，后出現(xiàn)的表作為外層的for循環(huán)

笛卡兒積

兩個集合中的每一個成員，都與對方集合中的任意一個成員有關聯(lián)；如果沒有給篩選條件，即為笛卡兒積

等值連接

在笛卡兒積的基礎上取條件列相同的值

select * from emp e，deptno d where e.deptno=d.deptno

非等值連接

!=、>、<、<>、between...and...

select ename，sal，hiredate，grade s from emp e，salgrade s where e.sal between losl and hisal

自連接

特殊的等值連接（來自于同一張表）

外連接

內連接使用比較運算符根據(jù)每個表共有的列的值匹配兩個表中的行

外連接可以是左向外連接、右向外連接或完整外部連接

注：沒有+的是主表

select * 
  from detp d，（select count（*），deptno 
    from emp group by deptno）c
where d.deptno=c.deptno（+）

2、99語法

笛卡兒積 cross join

select * from dept cross join emp

自然連接 natural join

做等值連接，需要有（同名列，主外鍵關系）

select ename，empno，deptno，dname from emp natural join dept

using連接、等值連接 join using

select ename，empno，deptno,daname from emp join dept using(deptno)

join on

等值：

select ename，empno，e.deptno,dname from emp e join dept d on e.deptno=d.deptno

非等：

select ename，sal,deptno,grade from emp e join salgrade s on sal between losal and hisal

outer join

left [outer] join on | using

right?[outer] join on | using

?全連接 full join on

select ... from table full join table2 on 連接條件 where...

?3、集合操作

union 并集 union All 全集 instersect 交集 Minus 差集

五、DDL語法

1、表操作

創(chuàng)建表

create table 表名（字段名 類型（長度），...）

?數(shù)據(jù)類型

詳細解釋可以查看官方文檔

修改表結構

修改表名：

rname 原表名 to 新表名

修改列名：

alter table 表名 rename column 列名 to?

修改字段類型：

alter table 表名 modify（字段類型）

alter table tb_student modify(studentId char(10))

添加列：

alter table 表名 add 字段 類型

刪除列：

alter table 表名 drop column 字段

2、刪除表

drop table 表名

?3、約束

主鍵約束（primary key）：該字段在表中唯一且非空

唯一性約束（unique）：只能出現(xiàn)一次

非空約束（not null）：不能為空

外鍵約束（foreign key）：外鍵（從表）中的數(shù)據(jù)必須在主表中已經存在才能使用

檢查約束（check）

創(chuàng)建表的同時，將約束進行創(chuàng)建，約束不設定名稱

create table tb_user(
  userid number(5) primary key,
  username varchar2(30) check(length(username) berween 4 and 20) not null,
  age number(3) default(18) check(age>=18),
  gender char(3) default('男') check(gender in('男','女')),
  regtime date default(sysdate)
)

創(chuàng)建表的同時，給約束設定名稱

create table tb_user(
  userid number(5),
  username varchar2(30) not null,
  age number(3) default(18),
  gender char(3) default('男'),
  regtime date default(sysdate)，
  constraint pk_user_id primary key(userid),
  constraint ck_user_name check(length(username) berween 4 and 20),
  constraint ck_user_age check(age>=18),
  constraint ck_user_gender check(gender in('男','女'))
)

創(chuàng)建完表后，追加約束

alter table tb_user add constraint pk_user_id primary key(userid)

?修改約束

alter table tb_user modify(username constrant nn_user_name no null)

?外鍵處理方式

強制不讓刪除

alter table tb_txt add constraint fk_txt_user_id foreign key(userid) references tb_user(userid)

刪除后自動設置為null

alter table tb_txt add constraint fk_txt_user_id foreign key(userid) references tb_user(userid) on delete set null

級聯(lián)刪除

alter table tb_txt add constraint fk_txt_user_id foreign key(userid) references tb_user(userid) on delete cascade

禁用和啟用約束

啟用/禁用（enable/disable）：對后來的數(shù)據(jù)進行校驗

驗證/非驗證（validate/novalidate）：對已存在的數(shù)據(jù)進行校驗

刪除約束

alter table tb_user drop cpnstraint ag_user_email cascade

六、DML

1、插入

insert into 表名 [（字段列表）] values（值列表）

?插入記錄時要滿足長度兼容、約束

默認添加：

insert into 表名 values（值列表）

?使用默認添加時，數(shù)據(jù)必須和表結構字段順序和個數(shù)一致

添加時指定列和順序

insert into 表名（指定列）values（值列表）

?如果是外鍵，被參考的主表字段一定要有數(shù)據(jù)

2、更新

update 表名 set 字段1=值1[字段2=值2，...] where 過濾行記錄

?注：在更新時一定記得加上where條件

3、刪除

delete from 表名 where 過濾行記錄

?注：先刪從表后刪主表文章來源地址http://www.zghlxwxcb.cn/news/detail-651289.html

到了這里，關于Oracle常用基礎知識的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！