前言
SQL每個人都要用，但是用來衡量產出的并不是SQL本身，你需要用這個工具，去創造其它的價值。
1.基本命令
1、登陸SQL*Plus（以system用戶登陸）
conn system/jeames@orcl
conn sys/jeames@orcl as sysdba
斷開連接 – disc
2、顯示用戶名
show user
3、設置顯示行的寬度為100
set linesize 100
4、設置每頁顯示的行數目為18
set pagesize 18
5、顯示當前數據庫的全稱
select * from global_name;
6、Oracle中究竟有多少種角色
select * from dba_roles;
7、查詢數據庫的表空間，一般是DBA用戶去查
SQL>select tablespace_name from dba_tablespaces;
8、查詢Oracle中所有的系統權限
SQL>select * from system_privilege_map order by name;
9、查詢Oracle中所有的對象權限
SQL>select distinct privilege from dba_tab_privs;
10、當前用戶下的表
SQL>select table_name from user_tables;
11、 顯示當前數據庫可以訪問的所有數據字典視圖
SQL>select * from dict where comments like’%grant%’;
12、查看某個用戶具有什么樣的角色？
SQL>select * from dba_role_privs where grantee=‘SCOTT’;
13、查看某個用戶（角色）具有什么樣的系統權限？
SQL>select * from dba_sys_privs where grantee=‘SCOTT’;
14、查看某個用戶（角色）具有什么樣的對象權限？
SQL>select * from dba_tab_privs where grantee=‘SCOTT’;
15、查詢Oracle中所有用戶信息
SQL> select * from all_users;
16、關閉數據庫
SQL>shutdown
17、啟動數據庫
SQL>startup
18、顯示初始化參數
SQL>show parameter
19、提交事務
SQL>COMMIT;
20、打開輸出選項
SQL>set serveroutput on
————————————————
2.用戶管理
1、創建用戶watchdog
SQL> create user watchdog identified by watchdog;
2、給用戶watchdog修改密碼
SQL>password watchdog
3、刪除用戶watchdog
–刪除的用戶已經創建了表，就需要在刪除時帶一個參數cascade
SQL>drop user watchdog cascade
4、運行Sql腳本（f:wdd.sql）
SQL>@ d:wdd.sql
SQL>start d: wdd.sql
5、將內容輸出到指定文件中去
SQL>spool d:bb.sql
SQL>select * from emp
SQL>spool off
6、授權resource角色給watchdog
–resource角色可以創建表
SQL>grant resource to watchdog;
7、授權create session權限給watchdog
–create session權限色可以登陸數據庫
SQL> grant create session to watchdog;
8、授權查詢Scott用戶的imp表給watchdog
SQL>grant select on scott.emp to watchdog;
9、授權all權限(scott.emp)給watchdog
SQL>grant all on scott.emp to watchdog;
10、system希望收回watchdog對scott.emp表的查詢權限
–誰授權誰收回
SQL>revoke select on scott.emp from watchdog;
11、希望watchdog用戶可以去查詢Scott的imp表，
還希望watchdog能把這個權限給別人
SQL>grant select on scott.emp to watchdog with grant option;
如果是系統權限，就加入with admin option,其他同理
————————————————
3.管理用戶口令
1、賬戶鎖定
指定cfmaster這個用戶最多只能嘗試3次登錄，鎖定時間為2天
SQL>create profile lock_account limit failed_login_attempts 3 >password_lock_time 2;
SQL>alter user cfmaster profile lock_account;
2、賬戶解鎖
SQL>alter user cfmaster account unlock;
3、終止口令
給用戶xiaoming創建一個profile文件，要求該用戶每隔10天修改自家的密碼
SQL>create profile myprofile limit password_life_time 10
password_grace_time 2;
SQL>alter user xiaoming profile myprofile;
4、刪除profile文件(lock_account)
SQL>drop profile lock_account;
4.表的管理
4.1 創建表
SQL>create table student(
2 xuehao number(4),
3 xingming varchar2(20),
4 sex char(2),
5 birthday date,
6 sal number(7,2)
7 )
8 /
SQL> create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from scott.emp;
————————————————
4.2 修改表
1、給表添加一個字段CLASSID
SQL> alter table student add (classid number(2));
2、修改字段xingming的長度為30
–修改字段的類型要求不能有數據
SQL> alter table student modify (xingming varchar2(30));
3、刪除字段SAL
SQL>alter table student drop column sal;
4、修改表的名字為STU
SQL> rename student to stu;
5、刪除表(stu)
SQL>drop table stu
4.3 添加數據
1、所有字段都插入
SQL> insert into student values(1,‘李芳’,‘女’,‘03-8月-99’);
2、改日期的默認格式
SQL> alter session set nls_date_format=‘yyyy-mm-dd’;
3、部分字段插入（xingming、sex）
SQL>insert into student（xingming,sex） values(‘李芳’,‘女’);
4、改一個字段（修改sex為“女”的生日為”2001-05-22”）
SQL>update student set birthday='2001-05-22’where sex=‘女’;
5、修改多個字段
–修改sex為女的生日為”2014-05-21”，xingming為張三
SQL> update student set birthday=‘2014-05-21’,xingming='張三’where sex=‘女’;
6、刪除所有數據
SQL> delete from student;
7、刪除某行
SQL> delete from stu where fullname=‘王平平’;
8、設置回滾點
SQL> savepoint a;
SQL> rollback to a;
5.表的查詢
5.1 簡單查詢命令
SQL> clear
–清屏
SQL> desc dba_users
–查詢表結構
SQL> set timing on
–打開顯示操作的時間
SQL> select count（）from student;
–統計行數
SQL> select sal12 “總工資” from imp;
–取別名及使用算數表達式
SQL> select sal*12+nvl(comm,0)12 “平均工資” from imp;
–空值處理用nvl函數
SQL> select distinct deptno,job from imp;
–取消重復行
SQL> select ename “姓名”, sal12 as sum from imp;
–使用列的別名
SQL> select ename ||‘is a’||job from imp;
–如何連接字符串（||）
5.2 where子句
1、如何查找1982.1.1日后入職的員工
SQL> select * from imp where hiredate>‘1-1月-1982’；
2、如何顯示工資在2000到2500的員工的情況
SQL> select * from imp where sal>=2000 and sal<=2500;
3、如何顯示empno為7369,7499,7876的員工的情況（IN的使用）.
SQL>select * from imp where empno in（7369,7499,7876）;
5.3 like操作符
1、如何顯示首字符為S的員工姓名和工資
SQL>select * from imp where ename like’S%’;
2、如何顯示第三個字符為大寫O的員工姓名和工資
SQL>select * from imp where ename like’__O%’;
5.4 邏輯操作符號
查詢工資高于500或者是崗位為MANAGER的雇員，
同時還要滿足他們的姓名首字母為大寫的J？
SQL> select * from imp where (sal >500 or job = ‘manager’)
and ename like ‘J%’;
5.5 order by字句
1、如何按照工資的從低到高的順序顯示雇員的信息？
SQL> select * from imp order by sal;
2、按照部門號升序而雇員的工資降序排列
SQL> select * from imp order by deptno, sal desc;
6、表的復雜查詢
1、數據分組
顯示所有員工中最高工資和最低工資？.
SQL> select max(sal),min(sal) from imp;
最高工資那個人是誰？
SQL> select ename from imp where sal=(select max(sal) from imp);
顯示工資高于平均工資的員工信息
SQL> select * from imp where sal > (select avg(sal) from imp);
顯示每個部門的平均工資和最高工資？
SQL> select avg(sal), max(sal), deptno from imp group by deptno;
–分組查詢的話，分組的字段一定要出現在查詢的列表里面，否則會報錯
顯示平均工資低于2000的部門號和它的平均工資？
SQL> select avg(sal), deptno from imp group by deptno
having avg(sal) < 2000;
2、多表查詢
顯示雇員名，雇員工資及所在部門的名字
SQL> select e.ename, e.sal, d.dname from imp e, dept d
where e.deptno = d.deptno;
顯示各個員工的姓名，工資及工資的級別？
SQL> select e.ename, e.sal, s.grade
from imp e, salgrade s where e.sal between s.losal and s.hisal;
顯示雇員名，雇員工資及所在部門的名字，并按部門排序？
SQL> select e.ename, e.sal, d.dname from imp e, dept d
where e.deptno = d.deptno order by e.deptno;
3、合并查詢
1）union該操作符用于取得兩個結果集的并集。
當使用該操作符時，會自動去掉結果集中重復行。
SQL> select ename, sal, job from imp where sal >2500
union
select ename, sal, job from imp where job = ‘MANAGER’;
2）union all該操作符與union相似，
但是它不會取消重復行，而且不會排序。
SQL> select ename, sal, job from imp where sal >2500
UNION ALL
select ename, sal, job from imp where job = ‘MANAGER’;
3）intersect使用該操作符用于取得兩個結果集的交集。
SQL> select ename, sal, job from imp where sal >2500
Intersect
select ename, sal, job from imp where job = ‘MANAGER’;
4）minus使用改操作符用于取得兩個結果集的差集，
他只會顯示存在第一個集合中，而不存在第二個集合中的數據。
SQL> select ename, sal, job from imp where sal >2500
Minus
select ename, sal, job from imp where job = ‘MANAGER’;
7.數據庫管理
1、 導出表
1.1、導出自己的表
exp userid=Scott/targer@wddorcl tables=(imp) file=f:wdd.dmp;
1.2、導出其他方案的表（DBA權限，如system就可以）
exp userid=system/cfmaster@wddorcl tables=(scott.emp) file=f:wdd1.dmp;
1.3、導出表的結構
exp userid=Scott/targer@wddorcl tables=(imp) file=f:wpp.dmp rows=n;
1.4、使用直接導出方式（速度快，數據量大時可以考慮）
exp userid=Scott/targer@wddorcl tables=(imp) file=f:wpp.dmp direct=y;
2、 導出方案（方案中的所有對象，表、索引、約束等）
2.1、導出自己的方案
exp userid=Scott/targer@wddorcl owner=Scott file=f:wpp1.dmp;
2.2、導出其他方案（DBA權限，如system就可以）
exp userid=system/cfmaster@wddorcl owner=(system,scott) file=f:wpp2.dmp;
3、 導出數據庫（導出數據庫中的對象及數據，需要DBA權限，如system就可以）
exp userid=system/cfmaster@wddorcl full=y inctype=complete file=f:wdd.dmp;
4、 導入表
4.1、導入自己的表
imp userid=Scott/targer@wddorcl tables=(imp) file=f:wdd.dmp;
4.2、導入表到其他用戶（DBA權限，如system就可以）
imp userid=system/cfmaster@wddorcl tables=(imp) file=f:wdd.dmp touser=Scott;
4.3、導入表的結構(只導入表的結構，而不導入數據)
imp userid=Scott/targer@wddorcl tables=(imp) file=f:wpp.dmp rows=n;
4.4、導入數據（如果對象已經存在,可以只導入表的數據）
imp userid=Scott/targer@wddorcl tables=(imp) file=f:wdd.dmp ignore=y;
5、 導入方案（方案中的所有對象，表、索引、約束等）
5.1、導入自己的方案
imp userid=Scott/targer file=f:wpp1.dmp;
5.2、導入其他方案（DBA權限，如system就可以）
imp userid=system/cfmaster file=f:wpp1.dmp formuser=system touser=Scott;
6、 導入數據庫（導入數據庫中的對象及數據，需要DBA權限，如system就可以）
imp userid=system/cfmaster full=y file=f:wpp2.dmp;
7、 管理表空間和數據文件
7.1、建立數據表空間
SQL>create tablespace data01 datafile 'f:data01.dbf' size 20m uniform size 128k;
7.2、使用數據表空間
SQL>create table mypart(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace data01;
7.3、改變表空間的狀態
1） 使表空間脫機
SQL>alter tablespace 表空間名 offine;
2)使表空間連機
SQL>alter tablespace 表空間名 onine;
3)只讀表空間
SQL>alter tablespace 表空間名 read only;
4)可讀寫表空間
SQL>alter tablespace data01 read write;
實例：
1) 知道表空間名，顯示該表空間包括的所有表
SQL>select table_name from all_tables where tablespace_name='表空間名';
2) 知道表名，查看該表屬于哪個表空間
SQL>select * from all_tables where table_name='表名';
7.4、 刪除表空間
SQL>drop tablespace 表空間including contents and datafiles;(徹底刪除);
7.5、 擴展表空間
1) 增加數據文件
SQL>alter tablespace data01 add datafile'f:data02.dbf'size 30m;
2) 增加數據文件的大小
SQL>alter database datafile 'f:data01.dbf' resize 50m;(大小不能超過500m)
3) 設置文件的自動增長
SQL>alter database datafile 'f:data01.dbf'autoextend on next 10m maxsize 500m;
7.6、 移動數據文件
1) 確定數據文件所在的表空間
SQL>select tablespace_name from dba_data_files
where file_name='F:DATA02.DBF';
2) 使表空間脫機（確保數據文件的一致性，把表空間轉變為Offline狀態）
SQL>alter tablespace data01 offline;
3) 使用命令移動數據文件到指定的目標位置(或者剪切)
SQL>host move f:data02.dbf e:data02.dbf;
4) 執行alter tablespace命令
SQL>alter tablespace data01 rename datafile 'f:data02.dbf'to'e:data02.dbf';
5) 使得表空間聯機
SQL>alter tablespace data01 online;
補充：
1） 查詢表空間使用情況
SQL>SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
D.TOT_GROOTTE_MB "表空間大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空閑空間(M)",
F.MAX_BYTES "最大塊(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
2） 查詢表空間的總容量
SQL>select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
3)顯示表空間所包含的數據文件
SQL>select file_name,bytes from dba_data_files
where tablespace_name='表空間名';
8.數據庫管理
1、 顯示當前用戶約束信息
SQL> select constraint_name,constraint_type,status,validated
from user_constraints where table_name='EMP';
2、 顯示約束所對應的表列信息
SQL> select column_name,position from user_cons_columns
where constraint_name='約束名';
3、 約束包括：not null、unique、primary key foreign key、check五種
SQL> name varchar2(50) not null
SQL> email varchar2(50) unique
SQL> goodsId char(8) primary key
SQL> cusomerId char(8) references customer(customerId)
SQL> nums number(10) check (nums between 1 and 30)
4、 增加not null約束時需要使用modify選項，而增加其他四種約束使用add選項
SQL> alter table goods modify goodsName not null;
SQL> alter table customer add constraint cardybuque unique(cardId);
--constraint cardybuque即約束及其名字（名字隨便取）
5、刪除約束
SQL> alter table 表名 drop constraint 約束名稱;
在刪除主鍵約束的時候可能有錯誤，比如說
SQL> alter table 表名 drop primary key;
--這是因為如果在兩張表存在主存關系，那么刪除主表的主鍵約束時，必須帶上cascade選項
SQL> alter table 表名 drop primary key cascade；
9、oracle的函數
1、 字符函數
1.1、問題：將所有員工的名字按小寫的方式顯示
SQL> select lower(ename) from emp;
1.2、問題：將所有員工的名字按大寫的方式顯示。
SQL> select upper(ename) from emp;
1.3、問題：顯示正好為5個字符的員工的信息。
SQL> select * from emp where length(ename)=5;
1.4、問題：顯示所有員工姓名的前三個字符。
SQL> select substr(ename,1,3) from emp;
1.5、問題：以首字母大寫,后面小寫的方式顯示所有員工的姓名。
SQL>select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))
from emp;
1.6、問題：顯示所有員工的姓名，用“我是老虎”替換所有“A”
SQL> select replace(ename,'A', '我是老虎') from emp;
2、數學函數
2.1、問題：顯示在一個月為30天的情況下，所有員工的日薪金，忽略余數。
SQL> select trunc(sal/30), ename from emp;
or
SQL> select floor(sal/30), ename from emp;
2.2、在做oracle測試的時候，可以使用dual表
SQL> select mod(10,2) from dual;結果是0
SQL> select mod(10,3) from dual;結果是1
2.3、其它的數學函數
abs(n)： 返回數字n的絕對值
acos(n)： 返回數字的反余弦值
exp(n)： 返回e的n次冪
power(m,n)： 返回m的n次冪
3、日期函數
默認情況下日期格式是dd-mon-yy 即12-7月-78
3.1、問題：查找已經入職8個月多的員工
SQL> select * from emp where sysdate>=add_months(hiredate,8);
//sysdate: 該函數返回系統時間
//add_months(d,n):時間點d再加上n個月
3.2、問題：對于每個員工，顯示其加入公司的天數。
SQL> select floor(sysdate-hiredate) "入職天數",ename from emp;
or
SQL> select trunc(sysdate-hiredate) "入職天數",ename from emp;
3.3、問題：找出各月倒數第3天受雇的所有員工。
SQL> select hiredate,ename from emp
where last_day(hiredate)-2=hiredate;
// last_day(d)：返回指定日期所在月份的最后一天
1、 轉換函數
5.1、問題：日期是否可以顯示 時/分/秒
SQL> select ename, to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’)
from emp;
yy：兩位數字的年份 2004-->04
yyyy：四位數字的年份 2004年
mm：兩位數字的月份 8月-->08
dd：兩位數字的天 30號-->30
hh24： 8點-->20
hh12：8點-->08
mi、ss-->顯示分鐘秒
5.2、問題：顯示薪水的時候，把本地貨幣單位加在前面
SQL>select ename,
to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'),
to_char(sal,'L99999.99')
from emp;
5.3、問題：顯示1980年入職的所有員工
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
5.4、問題：顯示所有12月份入職的員工
SQL> select * from emp where to_char(hiredate, 'mm')=12;
5.5、to_date
//函數to_date用于將字符串轉換成date類型的數據
SQL>insert into emp values
(9998, to_date('1988-12- 12', 'yyyy-mm-dd'));
6、系統函數
//sys_context('USERENV','lanuage')
-- USERENV是固定的，不能改的，lanuage可以換成其它
1）terminal：當前會話客戶所對應的終端的標示符（本地客戶端的計算機名稱）
2）lanuage: 語言
3）db_name： 當前數據庫名稱
4）nls_date_format： 當前會話客戶所對應的日期格式
5）current_schema： 當前會話客戶所對應的默認方案名
6）host： 返回數據庫所在主機的名稱 （本地客戶端的工作組/計算機名稱）
注：返回oracle服務器的IP地址及服務器名稱
SQL>select utl_inaddr.get_host_address,utl_inaddr.get_host_name
from dual;
覺得寫得好的小伙伴給個點贊+關注啦，謝謝~
如果有寫得不正確的地方，麻煩指出，感激不盡。
同時非常期待小伙伴們能夠關注，后面慢慢推出更好的干貨~嘻嘻
