工作中,我們經常用到哪些SQL語句呢?

工作中我們基本上每天都要與資料庫打交道，資料庫的知識點呢也特別多，全部記住呢也是不可能的，也沒必要把所有的記住（有些語句命令可能我們一輩子都用不到）。所以呢在工作之餘，把工作中經常用到的一些語句整理出來，忘記的時候可以當做字典來查。個人在工作中用Oracle資料庫比較多，就以關係型資料庫Oracle為例進行整理，後面可能會整理一些非關係型資料庫，如mogodb之類的。如果你覺得有所價值可以參考。如果有不全或者錯誤的也歡迎大家指正。一、DDL部分（create、drop、alter）

1.1 create 語句上

①語法：

create table table_name ( column_name datatype [null|not null], column_name datatype [null|not null], ... [constraint])-- table_name： 資料庫表名稱，在一個資料庫中數據表名稱不能重複-- column_name ：表中的列名，列名在一個表中也不能重複-- datatype：該列存放數據的數據類型-- [null|not null] ：該列是否允許為空-- [constraint]：為表中的列設置約束（主鍵約束、外檢約束、檢查約束等）

②舉例：創建一張商品信息表(productinfo)，表包含商品編號、商品名稱、商品價格、商品數量、商品類型、商品描述、產地7個欄位，並設置主鍵約束、非空、檢查約束等

create table productinfo(productid varchar2(10) PRIMARY KEY, productname varchar2(50) not null, productprice number(8,2) not null, qty number(10), category varchar2(10), desperation varchar2(400), orign varchar2(50) CONSTRAINT productname_uk UNIQUE(productname))

1.2 drop 語句

① 語法：

drop table table_name;

②舉例：刪除上面所創建的商品信息表

drop table productinfo;

1.3 alter 語句

① 語法

alter table table_name add column_name | modify column_name | drop column column_name;--add column_name : 用於向表中添加列--modify column_name ： 用來修改表中已存在的列信息--drop column ： 刪除表中列

② 舉例 ： 向商品信息表中添加備註欄位、修改備註欄位類型、刪除備註欄位

alter table productinfo add remark varchar2(200);alter table productinfo modify remark number(2,2);alter table productinfo drop column remark;

二、DML(數據操縱語言)和DQL（數據查詢語言）

2.1 insert 語句

① 語法：

insert into table_name(colunm_name1,colunm_name2,colunm_name3,...)values(data1,data2,data3...)-- colunm_name1: 指定表中要添加數據的列名，可以是一個或多個-- data1：要填入指定列的數據值，值的數目要與列數量一致

② 舉例：向商品信息表中添加一條商品信息

insert into productinfo(productid, productname, productprice, qty, category, desperation, orign)values ('10001', '電視機', 3250, 2, '01', '65寸智能電視', '小米集團');

2.2 update 語句

① 語法：

update table_name set colunm_name1=data1,colunm_name2=data2,...{where condition};

② 舉例：將商品信息表中的電視機名稱修改成「小米電視機」，價格修改成4500

update productinfoset productname = '小米電視機', productprice = 4500 where productid = '10001';

2.3 delete 語句

① 語法：

delete from table_name {where condition};

② 舉例：刪除商品信息表中編號為10001 的數據

delete productinfowhere productid = '10001';

2.4 select 語句

① 語法：

select colunm_name1,colunm_name2,colunm_name3,... from table_name {where condition};

② 舉例：查詢出商品編碼為10001的商品信息

select productid, productname, productprice, qty, category, desperation, orign from productinfo where productid = '10001'

2.5 其他操縱語言

2.5.1 truncate 語句

truncate語句和delete語句一樣都是用來刪除表中數據，但是兩者是有區別的，使用truncate語句是沒有條件的刪除，可以把表中數據全部刪除，truncate刪除表中數據的速度比delete快

① 語法

truncate table table_name;

② 舉例：刪除商品信息表中全部數據

truncate table productinfo;

2.5.2 merge 語句

merge語句與update語句功能類似，都是修改表中數據。但是兩者是有區別的，merge可以對數據表同時進行增加和修改操作

① 語法

merge [into] table_name1using table_name2 on (condition) when matched then merge_update_clause when not matched then merge_insert_clause;-- table_name1 : 要修改或添加的表-- table_name2：參照的更新的表-- condition ： table_name1 和 table_name2 表之間的關係，或其他條件-- merge_update_clause：條件匹配執行語句-- merge_insert_clause：條件不匹配執行語句 可以去掉

② 舉例：當滿足條件時，根據finitemcompare表中的itemcode 更新ipb_itemlist表的sicode欄位

merge into ipb_itemlist t1 using fin_item_compare t2on (t1.orgcode = t2.orgcode and t1.itemid = t2.itemid and t1.isdrug= '1' and t1.inid = '30675328')when matched thenupdate set t1.sicode = t2.itemcode

三、用戶角色權限

3.1 用戶相關

3.1.1 創建用戶

① 語法：

create user username identified by passwordor externally as certificate_dnor globally as directory_dn[default tablespace tablespacename][temporary tablespace tablespaceName][profile profile][quota integer|unlimited on tablespaceName][password expire][account lock|unlock]--username : 用戶名稱--identified by password：用戶口令--[default tablespace tablespacename] ：默認表空間；--[temporary tablespace tablespaceName] ：臨時表空間；--[profile profile]：設置當前用戶使用的概要文件的名稱--[quota integer|unlimited on tablespaceName]：設置當前用戶使用表空間的最大值，如果設置成unlimited 表示對表空間的使用沒有限制--[password expire]: 設置當前用戶密碼立即處於過期狀態，用戶如果想再登陸資料庫必須要更改密碼--[account lock|unlock]: 設置當前用戶鎖的狀態，lock表示不能訪問資料庫unlock表示可以訪問資料庫

②舉例：創建一個user2的用戶，並且設置密碼為立即過期方式

create user user2 identified by abcd --口令是abcddefault tablespace test --默認表空間是testquota 10M on test -- 在表空間test中使用的磁碟限額是10MBtemporary tablespace temp --臨時表空間為tempprofile pro_test --使用概要文件是pro_testpassword expire --密碼為立即過期狀態

3.1.2 修改用戶

①語法：

alter user username identified{by password [replace old_pwssword]|externally as certificate_dn| globally as directory_dn[default tablespace tablespacename][temporary tablespace tablespaceName][profile profile][quota integer|unlimited on tablespaceName][password expire][account lock|unlock]}

②舉例：修改用戶user2的密碼為123456

alter user user2 identified by 123456 --修改user2密碼為123456

③舉例：修改用戶預設表空間

Alter user user2 default tablespace users; --修改user2預設表空間為users

④舉例：修改用戶臨時表空間

Alter user user2 temporary tablespace temp_data; --修改user2臨時表空間為temp_data

⑤舉例：強制用戶修改口令字

Alter user user2 password expire; --強制用戶修改口令

⑥用戶加鎖、解鎖

Alter user user2 account lock; -- 加鎖Alter user user2 account unlock; -- 解鎖

3.1.3 刪除用戶

①語法：

drop user username

② 舉例：刪除user2用戶

drop user user2;

3.1.4 監視用戶

①查詢用戶會話信息

select username, sid, serial#, machine from v$session;

② 刪除用戶會話信息

Alter system kill session 'sid, serial#';

③查詢用戶SQL語句

SQL> select user_name, sql_text from v$open_cursor;

3.2 權限管理相關

3.2.1 權限分類

系統權限：系統規定用戶使用資料庫的權限。（系統權限是對用戶而言)。

實體權限：某種權限用戶對其它用戶的表或視圖的存取權限。（是針對表或視圖而言的）。

3.2.2 系統權限

①系統權限分類

DBA: 擁有全部特權，是系統最高權限，只有DBA才可以創建資料庫結構。

RESOURCE:擁有Resource權限的用戶只可以創建實體，不可以創建資料庫結構。

CONNECT:擁有Connect權限的用戶只可以登錄Oracle，不可以創建實體，不可以創建資料庫結構。

對於普通用戶：授予connect, resource權限。

對於DBA管理用戶：授予connect，resource, dba權限。

②系統權限授權命令

系統權限只能由DBA用戶授出：sys, system最開始只能是這兩個用戶。普通用戶通過授權可以具有與system相同的用戶權限，但永遠不能達到與sys用戶相同的權限，system用戶的權限也可以被回收。

授權語法：

grant connect, resource, dba to 用戶名1 [,用戶名2]...;

舉例：給user2授權

grant connect, resource to user2;

查詢用戶權限：

select * from dba_role_privs;select * from dba_sys_privs;select * from role_sys_privs;

drop user 用戶名 cascade; --加上cascade則將用戶連同其創建的東西全部刪除

③ 系統權限傳遞

增加WITH ADMIN OPTION選項，則得到的權限可以傳遞。

grant connect, resorce to user2 with admin option; --可以傳遞所獲權限。

④ 系統權限收回

Revoke connect, resource from user2;

說明：

(1)如果使用WITH ADMIN OPTION為某個用戶授予系統權限，那麼對於被這個用戶授予相同權限的所有用戶來說，取消該用戶的系統權限並不會級聯取消這些用戶的相同權限。

(2)系統權限無級聯，即A授予B權限，B授予C權限，如果A收回B的權限，C的權限不受影響；系統權限可以跨用戶回收，即A可以直接收回C用戶的權限。

3.2.3 實體權限

①實體權限分類：

select、 update、 insert、alter、index、 delete、all （all包括所有權限）、execute（執行存儲過程權限）

舉例：proudct 屬於user01表，將proudct 權限授權給usert02

user01:

grant select, update, insert on product to user02;grant all on product to user02;

user02:

select * from user01.product; --此時user02可以查詢到user01.product

②將表的操作權限授予全體用戶

grant all on product to public; -- public表示是所有的用戶，這裡的all權限不包括drop。[實體權限數據字典]:select owner, table_name from all_tables; -- 用戶可以查詢的表select table_name from user_tables; -- 用戶創建的表select grantor, table_schema, table_name, privilege from all_tab_privs; -- 獲取可以存取的表（被授權的）select grantee, owner, table_name, privilege from user_tab_privs; -- 授出權限的表(授出的權限)

③DBA用戶可以操作全體用戶的任意基表(無需授權，包括刪除)

DBA用戶具有以下權限：

/*創建其他用戶的表*/Create table stud02.product(id number(10), name varchar2(20)); /*刪除其他用戶的表*/drop table stud02.emp;/*根據用戶1的數據為用戶2創建表*/create table stud02.employee as select * from scott.emp;

3.2.3 實體權限傳遞(with grant option)

user01:

grant select, update on product to user02 with grant option; -- user02得到權限，並可以傳遞。

3.2.4 實體權限回收

user01:

Revoke select, update on product from user02; --傳遞的權限將全部丟失。

說明

（1）如果取消某個用戶的對象權限，那麼對於這個用戶使用WITH GRANT OPTION授予權限的用戶來說，同樣還會取消這些用戶的相同權限，也就是說取消授權時級聯的。

3.3 角色相關

角色是一組權限的集合，將角色賦給一個用戶，這個用戶就擁有了這個角色中的所有權限

3.3.1 系統預定義角色

oracle資料庫安裝之後會自動創建一些角色

① CONNECT, RESOURCE, DBA

這些預定義角色主要是為了向後兼容。其主要是用於資料庫管理

② DELETECATALOGROLE， EXECUTECATALOGROLE， SELECTCATALOGROLE

這些角色主要用於訪問數據字典視圖和包。

③ EXPFULLDATABASE， IMPFULLDATABASE

這兩個角色用於數據導入導出工具的使用。

④ AQUSERROLE， AQADMINISTRATORROLE

AQ:Advanced Query。這兩個角色用於oracle高級查詢功能。

⑤ SNMPAGENT

用於oracle enterprise manager和Intelligent Agent

⑥ RECOVERYCATALOGOWNER

用於創建擁有恢復庫的用戶

⑦ HSADMINROLE

3.3.2 管理角色

① 建一個角色

create role role1;

② 將權限授權給角色

grant create any table,create procedure to role1;

③ 將角色授予角色給用戶

grant role1 to user1;

④ 查看角色所包含的權限

select * from role_sys_privs;

⑤ 創建帶有口令的角色(在生效帶有口令的角色時必須提供口令)

create role role1 identified by password1;

⑥ 修改角色：是否需要口令

alter role role1 not identified;alter role role1 identified by password1;

⑦ 設置當前用戶要生效的角色

(註：角色的生效是一個什麼概念呢？假設用戶a有b1,b2,b3三個角色，那麼如果b1未生效，則b1所包含的權限對於a來講是不擁有的，只有角色生效了，角色內的權限才作用於用戶，最大可生效角色數由參數MAXENABLEDROLES設定；在用戶登錄後，oracle將所有直接賦給用戶的權限和用戶默認角色中的權限賦給用戶。）

set role role1;--使role1生效set role role,role2;--使role1,role2生效set role role1 identified by password1;--使用帶有口令的role1生效set role all;--使用該用戶的所有角色生效set role none;--設置所有角色失效set role all except role1; --除role1外的該用戶的所有其它角色生效。select * from SESSION_ROLES;--查看當前用戶的生效的角色。

⑧ 修改指定用戶，設置其默認角色

alter user user1 default role role1;alter user user1 default role all except role1;

⑨ 刪除角色

角色刪除後，原來擁用該角色的用戶就不再擁有該角色了，相應的權限也就沒有了。

drop role role1;

四、工作常用sql總結

4.1 cmd連接Oracle

sqlplus scott/tiger@192.168.205.100:1521/orcl

4.2 查看資料庫版本

select * from v$version;

4.3 查看所有dblink

select * from ALL_DB_LINKS;

4.4 查看所有定時job

select * from all_jobs;

4.5 查看當前用戶所有序列

select * from user_sequences; --last_number就是此刻執行nextval的值，last_number - increment_by 就是當前值

4.6 查看資料庫允許的最大連接數

select value from v$parameter where name ='processes';

4.7 查看當前的session連接數

select count(*) from v$session;

4.8 查看並發連接數

select count(*) from v$session where status='ACTIVE';

4.9 查詢用戶擁有的所有表

select * from all_tables where owner='TEST';

4.10 查詢資料庫進程數

select value from v$parameter where name = 'processes'; --取得進程數的上限。select count(*) from v$process; --取得資料庫目前的進程數。

4.11 數據誤刪除恢復

select * from tablename as of timestamp sysdate -1/24; --一小時前表數據select * from tablename as or timestamp sysdate-5/1440; --5分鐘前的表數據

4.12 獲取某張表的所有欄位

select * from user_tab_columns where table_name ='表名大寫'

4.13 生成連續時間區間內時間

--2019-03-13 日00 點到23 點內時間SELECT to_char(to_date('2019-03-13 00', 'yyyy-mm-dd hh24') +(ROWNUM - 1) / 24, 'yyyy-mm-dd hh24') sdate FROM dualCONNECT BY ROWNUM <= (to_date('2019-03-13 23', 'yyyy-mm-dd hh24') - to_date('2019-03-13 00', 'yyyy-mm-dd hh24')) * 24 + 1

4.14 表空間查看及擴表空間

①查詢表空間的大小（表空間名稱、總大小、還剩多少）

select df.tablespace_name 表空間,totalspace 總_M, freespace 剩_M, round((1 - freespace / totalspace) * 100, 2) || '%' 使用率 from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace from dba_data_files group by tablespace_name) df, (select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace from dba_free_space group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name and df.tablespace_name like '%%' order by df.tablespace_name

② 查詢需要擴的表空間的絕對路徑

select t1.name, t2.namefrom v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#

③擴表空間（三種方法）

注意：一個數據文件最大只能32G；

--1.手工改變已存在數據文件的大小ALTER TABLESPACE app_data ADD DATAFILE'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP01.DBF' SIZE 20480M;--2.允許已存在的數據文件自動增長ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP01.DBF'AUTOEXTEND ON NEXT 100M MAXSIZE 20480M; --3.增加數據文件(設置的每個文件初始分配空間為7g, autoextend on為自動增長大小，oracle單個文件大小最大不超過32g)--這裡增加兩個數據文件，需要擴容的表空間是APP_DATAALTER TABLESPACE APP_DATA ADD DATAFILE'C:\APP\ORACLE\ORADATA\DFYYCDB\DATAFILE\APP02.DBF' size 7167M autoextend on ;ALTER TABLESPACE APP_DATA ADD DATAFILE'C:\APP\ORACLE\ORADATA\DFYYCDB\DATAFILE\APP04.DBF' size 7167M autoextend on ;

4.15 資料庫鎖表解鎖

注意： 如果資料庫是集群，則在解鎖的時候需要所有節點都查看，否則可能會漏掉

① 查看鎖表情況

select l.session_id sid,s.serial#, l.locked_mode, l.oracle_username, l.os_user_name, s.machine, s.terminal, o.object_name, s.logon_time FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid--and o.object_name='table_name' --object_name 表示表名 ORDER BY sid, s.serial#;

② 解鎖

alter system kill session 'sid,serial#'; --其中sid和serial#由1中查出

③ 以上兩步也可以合併為一下一個sql,查出鎖表語句後直接執行即可

SELECT 'ALTER system kill session ''' || s.sid || ', ' || s.serial# ||'''; ', object_name, machine, s.sid, s.serial# FROM v$locked_object l, dba_objects o, v$session s WHERE l.object_id　 = o.object_id AND l.session_id = s.sid and o.object_name = upper('R_REGISTER');

4.16 Oracle忘記密碼處理方法

①免密登陸

sqlplus /nolog

② 切換到用戶

conn /as sysdba

③ 修改密碼

alter user sys identified by 123456; -- 將sys用戶密碼修改為123456

注意：如果提示sqlplus /nolog不是內部命令

確保oracle安裝成功找到此路徑oracle的安裝目錄: 我的是在D:\app\Administrator\product\11.2.0\dbhome_1\BIN ,將此路徑配置到環境變量path中即可4.17 Oracle 小數轉字符時候，保留字符串小數點前面和後面的0

① 保留小數點前面的0

SQL> select to_char(0.1) from dual2 /TO_CHAR(0.1)--.1--解決辦法SQL> select to_char(0.1,'fm9999990.9999') from dual 2 /TO_CHAR(0.1,'FM9999990.9999')----0.1

② 保留小數點後面的0

SQL> select to_char(2.30) from dual2 /TO_CHAR(2.30)---2.3--解決辦法SQL> select to_char(2.30,'fm9999999.0000') from dual 2 /TO_CHAR(2.30,'FM9999999.0000')2.3000

③ 即保留小數點前面的0也保留小數點後面的0

SQL> select to_char(0.10) from dual2 /TO_CHAR(0.10)---.1--解決辦法SQL> select to_char(0.10,'fm9999990.00') from dual 2 /TO_CHAR(0.10,'FM9999990.00')---0.10

4.18 Oracle分頁查詢

-- 查詢5到10行數據select * from (select * from (select t.*, rownum rn from emp t) where rownum <= 10) where rn >= 6

4.19 Oracle根據生日計算年齡，精確到天

select trunc(months / 12) || '歲',trunc(mod(months, 12)) || '月', trunc(sysdate - add_months(birth, trunc(months))) || '天' age from (select months_between(sysdate, birth) months, birth from (select date '1992-09-13' birth from dual));

4.20 子查詢

select a,b,c from a where a IN (select d from b ) ;--

4.21 顯示文章、提交人和最後回復時間

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b;

4.22 外連接查詢

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c;

4.23 between語句使用

select * from table1 where time between time1 and time2; --限制查詢數據範圍時包括了邊界值select a,b,c, from table1 where a not between 數值1 and 數值2;--限制查詢數據範圍時不包括邊界

4.24 in 用法

select * from table1 where a [not] in ('值1','值2','值4','值6');

4.25 兩張關聯表，刪除主表中已經在副表中沒有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 );

4.26 四表聯查

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

4.27 日程安排提前五分鐘提醒

select * from 日程安排 where datediff('minute',f開始時間,getdate())>5;

4.28 查詢前10條記錄

select top 10 * form table1 where 範圍;

4.29 選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b);--可以用於論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等

4.30 包括所有在 TableA中但不在 TableB和TableC中的行並消除所有重複行

(select a from tableA ) except (select a from tableB) except (select a from tableC);

4.31 隨機取出10條數據

select top 10 * from tablename order by newid();

4.32 刪除重複記錄

delete from tablename where id not in (select max(id) from tablename group by col1,col2,...);

4.33 union 和union all 語句

--返回兩個查詢選定的所有不重複的行select deptno from emp union select deptno from dept;--合併兩個查詢選定的所有行，包括重複的行select deptno from emp union all select deptno from dept;

4.34 intersect 語句

--只返回兩個查詢都有的行select deptno from emp intersect select deptno from dept;

4.35 minus 語句

--返回由第一個查詢選定但是沒有被第二個查詢選定的行, 也就是在第一個查詢結果中排除在第二個查詢結果中出現的行select deptno from dept minus select deptno from emp;

4.36 Oracle 導入導出

--第一種 exp導出imp導入exp system/manager@127.0.0.1:1521/orcl file=d:\scott.dmp owner=scott log=d:\ch_exp.log buffer=999999imp system/manager@127.0.0.1:1521/orcl file=d:\scott.dmp log=d:\scott_imp.log fromuser=(scott) touser=(scott) buffer=999999 ignore=y--第二種 expd導出impd 導入expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott

目前能想到的就這麼多了，後面會據需更新。大佬們覺得有漏的也可以多多指點