工作中我們基本上每天都要與資料庫打交道，資料庫的知識點呢也特別多，全部記住呢也是不可能的，也沒必要把所有的記住（有些語句命令可能我們一輩子都用不到）。
所以呢在工作之餘，把工作中經常用到的一些語句整理出來，忘記的時候可以當做字典來查。
個人在工作中用Oracle資料庫比較多，就以關係型資料庫Oracle為例進行整理，後面可能會整理一些非關係型資料庫，如mogodb之類的。
如果你覺得有所價值可以參考。如果有不全或者錯誤的也歡迎大家指正。
①語法：
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)
)
① 語法：
drop table table_name;
②舉例：刪除上面所建立的商品資訊表
drop table productinfo;
① 語法
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;
① 語法：
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寸智慧電視', '小米集團');
① 語法：
update table_name set colunm_name1=data1,colunm_name2=data2,...{where condition};
② 舉例：將商品資訊表中的電視機名稱修改成「小米電視機」，價格修改成4500
update productinfo
set productname = '小米電視機',
productprice = 4500
where productid = '10001';
① 語法：
delete from table_name {where condition};
② 舉例：刪除商品資訊表中編號為10001 的資料
delete productinfo
where productid = '10001';
① 語法：
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'
truncate語句和delete語句一樣都是用來刪除表中資料，但是兩者是有區別的，使用truncate語句是沒有條件的刪除，可以把表中資料全部刪除，truncate刪除表中資料的速度比delete快
① 語法
truncate table table_name;
② 舉例：刪除商品資訊表中全部資料
truncate table productinfo;
merge語句與update語句功能類似，都是修改表中資料。但是兩者是有區別的，merge可以對資料表同時進行增加和修改操作
① 語法
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 表之間的關係，或其他條件
-- merge_update_clause：條件匹配執行語句
-- merge_insert_clause：條件不匹配執行語句 可以去掉
② 舉例：當滿足條件時，根據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
① 語法：
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] ：預設表空間；
--[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 --口令是abcd
default tablespace test --預設表空間是test
quota 10M on test -- 在表空間test中使用的磁碟限額是10MB
temporary tablespace temp --臨時表空間為temp
profile pro_test --使用概要檔案是pro_test
password expire --密碼為立即過期狀態
①語法：
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; -- 解鎖
①語法：
drop user username
② 舉例：刪除user2使用者
drop user user2;
①查詢使用者對談資訊
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;
系統許可權：系統規定使用者使用資料庫的許可權。（系統許可權是對使用者而言)。
實體許可權：某種許可權使用者對其它使用者的表或檢視的存取許可權。（是針對表或檢視而言的）。
①系統許可權分類
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使用者的許可權。
①實體許可權分類：
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;
user01:
grant select, update on product to user02 with grant option; -- user02得到許可權，並可以傳遞。
user01:
Revoke select, update on product from user02; --傳遞的許可權將全部丟失。
說明
（1）如果取消某個使用者的物件許可權，那麼對於這個使用者使用WITH GRANT OPTION授予許可權的使用者來說，同樣還會取消這些使用者的相同許可權，也就是說取消授權時級聯的。
角色是一組許可權的集合，將角色賦給一個使用者，這個使用者就擁有了這個角色中的所有許可權
oracle資料庫安裝之後會自動建立一些角色
① CONNECT, RESOURCE, DBA
這些預定義角色主要是為了向後相容。其主要是用於資料庫管理
② DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE
這些角色主要用於存取資料字典檢視和包。
③ EXP_FULL_DATABASE， IMP_FULL_DATABASE
這兩個角色用於資料匯入匯出工具的使用。
④ AQ_USER_ROLE， AQ_ADMINISTRATOR_ROLE
AQ:Advanced Query。這兩個角色用於oracle高階查詢功能。
⑤ SNMPAGENT
用於oracle enterprise manager和Intelligent Agent
⑥ RECOVERY_CATALOG_OWNER
用於建立擁有恢復庫的使用者
⑦ HS_ADMIN_ROLE
① 建一個角色
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來講是不擁有的，只有角色生效了，角色內的許可權才作用於使用者，最大可生效角色數由引數MAX_ENABLED_ROLES設定；在使用者登入後，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;
sqlplus scott/[email protected]:1521/orcl
select * from v$version;
select * from ALL_DB_LINKS;
select * from all_jobs;
select * from user_sequences; --last_number就是此刻執行nextval的值，last_number - increment_by 就是當前值
select value from v$parameter where name ='processes';
select count(*) from v$session;
select count(*) from v$session where status='ACTIVE';
select * from all_tables where owner='TEST';
select value from v$parameter where name = 'processes'; --取得程序數的上限。
select count(*) from v$process; --取得資料庫目前的程序數。
select * from tablename as of timestamp sysdate -1/24; --一小時前表資料
select * from tablename as or timestamp sysdate-5/1440; --5分鐘前的表資料
select * from user_tab_columns where table_name ='表名大寫'
--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 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
①查詢表空間的大小（表空間名稱、總大小、還剩多少）
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.name
from 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_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 ;
注意： 如果資料庫是叢集，則在解鎖的時候需要所有節點都檢視，否則可能會漏掉
① 檢視鎖表情況
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');
①免密登陸
sqlplus /nolog
② 切換到使用者
conn /as sysdba
③ 修改密碼
alter user sys identified by 123456; -- 將sys使用者密碼修改為123456
注意：如果提示sqlplus /nolog不是內部命令
① 保留小數點前面的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
② 保留小數點後面的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
③ 即保留小數點前面的0也保留小數點後面的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
-- 查詢5到10行資料
select *
from (select *
from (select t.*, rownum rn from emp t)
where rownum <= 10)
where rn >= 6
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));
select a,b,c from a where a IN (select d from b ) ;--
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b;
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c;
select * from table1 where time between time1 and time2; --限制查詢資料範圍時包括了邊界值
select a,b,c, from table1 where a not between 數值1 and 數值2;--限制查詢資料範圍時不包括邊界
select * from table1 where a [not] in ('值1','值2','值4','值6');
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 );
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 .....
select * from 日程安排 where datediff('minute',f開始時間,getdate())>5;
select top 10 * form table1 where 範圍;
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b);--可以用於論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等
(select a from tableA ) except (select a from tableB) except (select a from tableC);
select top 10 * from tablename order by newid();
delete from tablename where id not in (select max(id) from tablename group by col1,col2,...);
--返回兩個查詢選定的所有不重複的行
select deptno from emp union select deptno from dept;
--合併兩個查詢選定的所有行，包括重複的行
select deptno from emp union all select deptno from dept;
--只返回兩個查詢都有的行
select deptno from emp intersect select deptno from dept;
--返回由第一個查詢選定但是沒有被第二個查詢選定的行, 也就是在第一個查詢結果中排除在第二個查詢結果中出現的行
select deptno from dept minus select deptno from emp;
--第一種 exp匯出imp匯入
exp system/[email protected]:1521/orcl file=d:\scott.dmp owner=scott log=d:\ch_exp.log buffer=999999
imp system/[email protected]: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=dpdata1
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott
目前能想到的就這麼多了，後面會據需更新。大佬們覺得有漏的也可以多多指點