工作中，我們經(jīng)常用到哪些SQL？
工作中我們基本上每天都要與數(shù)據(jù)庫打交道，數(shù)據(jù)庫的知識點(diǎn)呢也特別多，全部記住呢也是不可能的，也沒必要把所有的記?。ㄓ行┱Z句命令可能我們一輩子都用不到）。
所以呢在工作之余，把工作中經(jīng)常用到的一些語句整理出來，忘記的時候可以當(dāng)做字典來查。
個人在工作中用Oracle數(shù)據(jù)庫比較多，就以關(guān)系型數(shù)據(jù)庫Oracle為例進(jìn)行整理，后面可能會整理一些非關(guān)系型數(shù)據(jù)庫，如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：?數(shù)據(jù)庫表名稱，在一個數(shù)據(jù)庫中數(shù)據(jù)表名稱不能重復(fù)
--?column_name?：表中的列名，列名在一個表中也不能重復(fù)
--?datatype：該列存放數(shù)據(jù)的數(shù)據(jù)類型
--?[null|not?null]?：該列是否允許為空
--?[constraint]：為表中的列設(shè)置約束（主鍵約束、外檢約束、檢查約束等）
②舉例：創(chuàng)建一張商品信息表(productinfo)，表包含商品編號、商品名稱、商品價格、商品數(shù)量、商品類型、商品描述、產(chǎn)地7個字段，并設(shè)置主鍵約束、非空、檢查約束等
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;
②舉例：刪除上面所創(chuàng)建的商品信息表
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(數(shù)據(jù)操縱語言)和DQL（數(shù)據(jù)查詢語言）
2.1 insert 語句
① 語法：
insert?into?table_name(colunm_name1,colunm_name2,colunm_name3,...)values(data1,data2,data3...)
--?colunm_name1:?指定表中要添加數(shù)據(jù)的列名，可以是一個或多個
--?data1：要填入指定列的數(shù)據(jù)值，值的數(shù)目要與列數(shù)量一致
② 舉例：向商品信息表中添加一條商品信息
insert?into?productinfo
??(productid,?productname,?productprice,?qty,?category,?desperation,?orign)
values
??('10001',?'電視機(jī)',?3250,?2,?'01',?'65寸智能電視',?'小米集團(tuán)');
2.2 update 語句
① 語法：
update?table_name?set?colunm_name1=data1,colunm_name2=data2,...{where?condition};
② 舉例：將商品信息表中的電視機(jī)名稱修改成“小米電視機(jī)”，價格修改成4500
update?productinfo
???set?productname?=?'小米電視機(jī)',
???????productprice?=?4500
?where?productid?=?'10001';
2.3 delete 語句
① 語法：
delete?from?table_name?{where?condition};
② 舉例：刪除商品信息表中編號為10001 的數(shù)據(jù)
delete?productinfo
?where?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語句一樣都是用來刪除表中數(shù)據(jù)，但是兩者是有區(qū)別的，使用truncate語句是沒有條件的刪除，可以把表中數(shù)據(jù)全部刪除，truncate刪除表中數(shù)據(jù)的速度比delete快
① 語法
truncate?table?table_name;
② 舉例：刪除商品信息表中全部數(shù)據(jù)
truncate?table?productinfo;
2.5.2 merge 語句
merge語句與update語句功能類似，都是修改表中數(shù)據(jù)。但是兩者是有區(qū)別的，merge可以對數(shù)據(jù)表同時進(jìn)行增加和修改操作
① 語法
merge?[into]?table_name1
????using?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?表之間的關(guān)系，或其他條件
--?merge_update_clause：條件匹配執(zhí)行語句
--?merge_insert_clause：條件不匹配執(zhí)行語句?可以去掉
② 舉例：當(dāng)滿足條件時，根據(jù)fin_item_compare表中的itemcode 更新ipb_itemlist表的sicode字段
merge?into?ipb_itemlist?t1?using?fin_item_compare?t2
on?(t1.orgcode?=?t2.orgcode?and?t1.itemid?=?t2.itemid??and?t1.isdrug=?'1'?and?t1.inid?=?'30675328')
when?matched?then
update?set?t1.sicode?=?t2.itemcode
三、用戶角色權(quán)限
3.1 用戶相關(guān)
3.1.1 創(chuàng)建用戶
① 語法：
create?user?username?
identified?by?password
or?externally?as?certificate_dn
or?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]?：默認(rèn)表空間；
--[temporary?tablespace?tablespaceName]?：臨時表空間；
--[profile?profile]：設(shè)置當(dāng)前用戶使用的概要文件的名稱
--[quota?integer|unlimited?on?tablespaceName]：設(shè)置當(dāng)前用戶使用表空間的最大值，如果設(shè)置成unlimited?表示對表空間的使用沒有限制
--[password?expire]:?設(shè)置當(dāng)前用戶密碼立即處于過期狀態(tài)，用戶如果想再登陸數(shù)據(jù)庫必須要更改密碼
--[account?lock|unlock]:?設(shè)置當(dāng)前用戶鎖的狀態(tài)，lock表示不能訪問數(shù)據(jù)庫unlock表示可以訪問數(shù)據(jù)庫
②舉例：創(chuàng)建一個user2的用戶，并且設(shè)置密碼為立即過期方式
create?user?user2????
identified?by?abcd???--口令是abcd
default?tablespace?test????--默認(rèn)表空間是test
quota?10M?on?test??????????--?在表空間test中使用的磁盤限額是10MB
temporary?tablespace?temp??--臨時表空間為temp
profile?pro_test???????????--使用概要文件是pro_test
password?expire????????????--密碼為立即過期狀態(tài)
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
⑤舉例：強(qiáng)制用戶修改口令字
Alter?user?user2?password?expire;???--強(qiáng)制用戶修改口令
⑥用戶加鎖、解鎖
Alter?user?user2?account?lock;??--?加鎖
Alter?user?user2?account?unlock;??--?解鎖
3.1.3 刪除用戶
①語法：
drop?user?username
② 舉例：刪除user2用戶
drop?user?user2;
3.1.4 監(jiān)視用戶
①查詢用戶會話信息
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 權(quán)限管理相關(guān)
3.2.1 ?權(quán)限分類
系統(tǒng)權(quán)限：系統(tǒng)規(guī)定用戶使用數(shù)據(jù)庫的權(quán)限。（系統(tǒng)權(quán)限是對用戶而言)。
實體權(quán)限：某種權(quán)限用戶對其它用戶的表或視圖的存取權(quán)限。（是針對表或視圖而言的）。
3.2.2 ?系統(tǒng)權(quán)限
①系統(tǒng)權(quán)限分類
DBA: 擁有全部特權(quán)，是系統(tǒng)最高權(quán)限，只有DBA才可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)。
RESOURCE:擁有Resource權(quán)限的用戶只可以創(chuàng)建實體，不可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)。
CONNECT:擁有Connect權(quán)限的用戶只可以登錄Oracle，不可以創(chuàng)建實體，不可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)。
對于普通用戶：授予connect, resource權(quán)限。
對于DBA管理用戶：授予connect，resource, dba權(quán)限。
②系統(tǒng)權(quán)限授權(quán)命令
系統(tǒng)權(quán)限只能由DBA用戶授出：sys, system最開始只能是這兩個用戶。普通用戶通過授權(quán)可以具有與system相同的用戶權(quán)限，但永遠(yuǎn)不能達(dá)到與sys用戶相同的權(quán)限，system用戶的權(quán)限也可以被回收。
授權(quán)語法：
grant?connect,?resource,?dba?to?用戶名1?[,用戶名2]...;
舉例：給user2授權(quán)
grant?connect,?resource?to?user2;
查詢用戶權(quán)限：
select?*?from?dba_role_privs;
select?*?from?dba_sys_privs;
select?*?from?role_sys_privs;drop?user?用戶名?cascade;?--加上cascade則將用戶連同其創(chuàng)建的東西全部刪除
③ 系統(tǒng)權(quán)限傳遞
增加WITH ADMIN OPTION選項，則得到的權(quán)限可以傳遞。
grant?connect,?resorce?to?user2?with?admin?option;??--可以傳遞所獲權(quán)限。
④ 系統(tǒng)權(quán)限收回
Revoke?connect,?resource?from?user2;
說明：
(1)如果使用WITH ADMIN OPTION為某個用戶授予系統(tǒng)權(quán)限，那么對于被這個用戶授予相同權(quán)限的所有用戶來說，取消該用戶的系統(tǒng)權(quán)限并不會級聯(lián)取消這些用戶的相同權(quán)限。
(2)系統(tǒng)權(quán)限無級聯(lián)，即A授予B權(quán)限，B授予C權(quán)限，如果A收回B的權(quán)限，C的權(quán)限不受影響；系統(tǒng)權(quán)限可以跨用戶回收，即A可以直接收回C用戶的權(quán)限。
3.2.3 實體權(quán)限
①實體權(quán)限分類：
select、 update、 insert、alter、index、 delete、all ?（all包括所有權(quán)限）、execute（執(zhí)行存儲過程權(quán)限）
舉例：proudct 屬于user01表，將proudct 權(quán)限授權(quán)給usert02
user01:
grant?select,?update,?insert?on?product?to?user02;
grant?all?on?product?to?user02;
user02:
select?*?from?user01.product;?--此時user02可以查詢到user01.product
②將表的操作權(quán)限授予全體用戶
grant?all?on?product?to?public;??--?public表示是所有的用戶，這里的all權(quán)限不包括drop。
[實體權(quán)限數(shù)據(jù)字典]:
select?owner,?table_name?from?all_tables;?--?用戶可以查詢的表
select?table_name?from?user_tables;??--?用戶創(chuàng)建的表
select?grantor,?table_schema,?table_name,?privilege?from?all_tab_privs;?--?獲取可以存取的表（被授權(quán)的）
select?grantee,?owner,?table_name,?privilege?from?user_tab_privs;???--?授出權(quán)限的表(授出的權(quán)限)
③DBA用戶可以操作全體用戶的任意基表(無需授權(quán)，包括刪除)
DBA用戶具有以下權(quán)限：
/*
創(chuàng)建其他用戶的表
*/
Create?table?stud02.product(
?id?number(10),
?name?varchar2(20)
);?
/*
刪除其他用戶的表
*/
drop?table?stud02.emp;
/*
根據(jù)用戶1的數(shù)據(jù)為用戶2創(chuàng)建表
*/
create?table?stud02.employee
?as
?select?*?from?scott.emp;
3.2.3 ?實體權(quán)限傳遞(with grant option)
user01:
grant?select,?update?on?product?to?user02?with?grant?option;?--?user02得到權(quán)限，并可以傳遞。
3.2.4 實體權(quán)限回收
user01:
Revoke?select,?update?on?product?from?user02;??--傳遞的權(quán)限將全部丟失。
說明
（1）如果取消某個用戶的對象權(quán)限，那么對于這個用戶使用WITH GRANT OPTION授予權(quán)限的用戶來說，同樣還會取消這些用戶的相同權(quán)限，也就是說取消授權(quán)時級聯(lián)的。
3.3 角色相關(guān)
角色是一組權(quán)限的集合，將角色賦給一個用戶，這個用戶就擁有了這個角色中的所有權(quán)限
3.3.1 系統(tǒng)預(yù)定義角色
oracle數(shù)據(jù)庫安裝之后會自動創(chuàng)建一些角色
① CONNECT, RESOURCE, DBA
這些預(yù)定義角色主要是為了向后兼容。其主要是用于數(shù)據(jù)庫管理
② DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE
這些角色主要用于訪問數(shù)據(jù)字典視圖和包。
③ EXP_FULL_DATABASE， IMP_FULL_DATABASE
這兩個角色用于數(shù)據(jù)導(dǎo)入導(dǎo)出工具的使用。
④ AQ_USER_ROLE， AQ_ADMINISTRATOR_ROLE
AQ:Advanced Query。這兩個角色用于oracle高級查詢功能。
⑤ SNMPAGENT
用于oracle enterprise manager和Intelligent Agent
⑥ RECOVERY_CATALOG_OWNER
用于創(chuàng)建擁有恢復(fù)庫的用戶
⑦ HS_ADMIN_ROLE
3.3.2 管理角色
① 建一個角色
create?role?role1;
② 將權(quán)限授權(quán)給角色
grant?create?any?table,create?procedure?to?role1;
③ 將角色授予角色給用戶
grant?role1?to?user1;
④ 查看角色所包含的權(quán)限
select?*?from?role_sys_privs;
⑤ 創(chuàng)建帶有口令的角色(在生效帶有口令的角色時必須提供口令)
create?role?role1?identified?by?password1;
⑥ 修改角色：是否需要口令
alter?role?role1?not?identified;
alter?role?role1?identified?by?password1;
⑦ 設(shè)置當(dāng)前用戶要生效的角色
(注：角色的生效是一個什么概念呢？假設(shè)用戶a有b1,b2,b3三個角色，那么如果b1未生效，則b1所包含的權(quán)限對于a來講是不擁有的，只有角色生效了，角色內(nèi)的權(quán)限才作用于用戶，最大可生效角色數(shù)由參數(shù)MAX_ENABLED_ROLES設(shè)定；在用戶登錄后，oracle將所有直接賦給用戶的權(quán)限和用戶默認(rèn)角色中的權(quán)限賦給用戶。）
set?role?role1;--使role1生效
set?role?role,role2;--使role1,role2生效
set?role?role1?identified?by?password1;--使用帶有口令的role1生效
set?role?all;--使用該用戶的所有角色生效
set?role?none;--設(shè)置所有角色失效
set?role?all?except?role1;?--除role1外的該用戶的所有其它角色生效。
select?*?from?SESSION_ROLES;--查看當(dāng)前用戶的生效的角色。
⑧ 修改指定用戶，設(shè)置其默認(rèn)角色
alter?user?user1?default?role?role1;
alter?user?user1?default?role?all?except?role1;
⑨ 刪除角色
角色刪除后，原來擁用該角色的用戶就不再擁有該角色了，相應(yīng)的權(quán)限也就沒有了。
drop?role?role1;
四、工作常用sql總結(jié)
4.1 cmd連接Oracle
sqlplus?scott/tiger@192.168.205.100:1521/orcl
4.2 查看數(shù)據(jù)庫版本
select?*?from?v$version;
4.3 查看所有dblink
select?*?from?ALL_DB_LINKS;
4.4 查看所有定時job
select?*?from?all_jobs;
4.5 查看當(dāng)前用戶所有序列
select?*?from?user_sequences;?--last_number就是此刻執(zhí)行nextval的值，last_number?-?increment_by?就是當(dāng)前值
4.6 查看數(shù)據(jù)庫允許的最大連接數(shù)
select?value?from?v$parameter?where?name?='processes';
4.7 查看當(dāng)前的session連接數(shù)
select?count(*)?from?v$session;
4.8 查看并發(fā)連接數(shù)
select?count(*)?from?v$session?where?status='ACTIVE';
4.9 查詢用戶擁有的所有表
select?*?from?all_tables?where?owner='TEST';
4.10 查詢數(shù)據(jù)庫進(jìn)程數(shù)
select?value?from?v$parameter?where?name?=?'processes';?--取得進(jìn)程數(shù)的上限。
select?count(*)?from?v$process;?--取得數(shù)據(jù)庫目前的進(jìn)程數(shù)。
4.11 數(shù)據(jù)誤刪除恢復(fù)
select?*?from?tablename?as?of?timestamp?sysdate?-1/24;?--一小時前表數(shù)據(jù)
select?*?from?tablename?as?or?timestamp?sysdate-5/1440;?--5分鐘前的表數(shù)據(jù)
4.12 獲取某張表的所有字段
select?*?from?user_tab_columns?where?table_name?='表名大寫'
4.13 生成連續(xù)時間區(qū)間內(nèi)時間
--2019-03-13?日00?點(diǎn)到23?點(diǎn)內(nèi)時間
SELECT?to_char(to_date('2019-03-13?00',?'yyyy-mm-dd?hh24')?+
???????????????(ROWNUM?-?1)?/?24,
???????????????'yyyy-mm-dd?hh24')?sdate
??FROM?dual
CONNECT?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 表空間查看及擴(kuò)表空間
①查詢表空間的大?。ū砜臻g名稱、總大小、還剩多少）
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
② 查詢需要擴(kuò)的表空間的絕對路徑
?select?t1.name,?t2.name
???from?v$tablespace?t1,?v$datafile?t2
??where?t1.ts#?=?t2.ts#
③擴(kuò)表空間（三種方法）
注意：一個數(shù)據(jù)文件最大只能32G；
--1.手工改變已存在數(shù)據(jù)文件的大小
ALTER?TABLESPACE?app_data?ADD?DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP01.DBF'?SIZE?20480M;
--2.允許已存在的數(shù)據(jù)文件自動增長
ALTER?DATABASE?DATAFILE?'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP01.DBF'
AUTOEXTEND?ON?NEXT?100M?MAXSIZE?20480M;?
--3.增加數(shù)據(jù)文件(設(shè)置的每個文件初始分配空間為7g,?autoextend?on為自動增長大小，oracle單個文件大小最大不超過32g)
--這里增加兩個數(shù)據(jù)文件，需要擴(kuò)容的表空間是APP_DATA
ALTER?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 數(shù)據(jù)庫鎖表解鎖
注意： 如果數(shù)據(jù)庫是集群，則在解鎖的時候需要所有節(jié)點(diǎn)都查看，否則可能會漏掉
① 查看鎖表情況
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,查出鎖表語句后直接執(zhí)行即可
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不是內(nèi)部命令
確保oracle安裝成功
找到此路徑oracle的安裝目錄: 我的是在D:\app\Administrator\product\11.2.0\dbhome_1\BIN ,將此路徑配置到環(huán)境變量path中即可
4.17 Oracle 小數(shù)轉(zhuǎn)字符時候，保留字符串小數(shù)點(diǎn)前面和后面的0
① 保留小數(shù)點(diǎn)前面的0
SQL>?select?to_char(0.1)?from?dual
??2??/
?
TO_CHAR(0.1)
------------
.1
--解決辦法
SQL>?select?to_char(0.1,'fm9999990.9999')?from?dual
??2??/
?
TO_CHAR(0.1,'FM9999990.9999')
-----------------------------
0.1
② 保留小數(shù)點(diǎn)后面的0
SQL>?select?to_char(2.30)?from?dual
??2??/
?
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
③ 即保留小數(shù)點(diǎn)前面的0也保留小數(shù)點(diǎn)后面的0
SQL>?select?to_char(0.10)?from?dual
??2??/
?
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行數(shù)據(jù)
???select?*
?????from?(select?*
?????????????from?(select?t.*,?rownum?rn?from?emp?t)
????????????where?rownum?<=?10)
????where?rn?>=?6
4.19 Oracle根據(jù)生日計算年齡，精確到天
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 顯示文章、提交人和最后回復(fù)時間
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;?--限制查詢數(shù)據(jù)范圍時包括了邊界值
select?a,b,c,?from?table1?where?a?not?between?數(shù)值1?and?數(shù)值2;--限制查詢數(shù)據(jù)范圍時不包括邊界
4.24 in 用法
select?*?from?table1?where?a?[not]?in?('值1','值2','值4','值6');
4.25 兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息
delete?from?table1?where?not?exists?(?select?*?from?table2?where?table1.field1=table2.field1?);
4.26 四表聯(lián)查
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值相同的數(shù)據(jù)中對應(yīng)的a最大的記錄的所有信息
select?a,b,c?from?tablename?ta?where?a=(select?max(a)?from?tablename?tb?where?tb.b=ta.b);--可以用于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績排名,等等
4.30 包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重復(fù)行
(select?a?from?tableA?)?except?(select?a?from?tableB)?except?(select?a?from?tableC);
4.31 隨機(jī)取出10條數(shù)據(jù)
select?top?10?*?from?tablename?order?by?newid();
4.32 刪除重復(fù)記錄
delete?from?tablename?where?id?not?in?(select?max(id)?from?tablename?group?by?col1,col2,...);
4.33 union 和union all 語句
--返回兩個查詢選定的所有不重復(fù)的行
select?deptno?from?emp?union?select?deptno?from?dept;
--合并兩個查詢選定的所有行，包括重復(fù)的行
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 語句
--返回由第一個查詢選定但是沒有被第二個查詢選定的行,?也就是在第一個查詢結(jié)果中排除在第二個查詢結(jié)果中出現(xiàn)的行
select?deptno?from?dept?minus?select?deptno?from?emp;
4.36 Oracle 導(dǎo)入導(dǎo)出
--第一種?exp導(dǎo)出imp導(dǎo)入
exp?system/manager@127.0.0.1:1521/orcl?file=d:\scott.dmp?owner=scott?log=d:\ch_exp.log?buffer=999999
imp?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導(dǎo)出impd?導(dǎo)入
expdp?scott/tiger@orcl?schemas=scott?dumpfile=expdp.dmp?DIRECTORY=dpdata1
impdp?scott/tiger?DIRECTORY=dpdata1?DUMPFILE=expdp.dmp?SCHEMAS=scott
目前能想到的就這么多了，后面會據(jù)需更新。大佬們覺得有漏的也可以多多指點(diǎn)
