sql千分位

『壹』 oracle理論學習詳解及各種簡單操作例子怎麼解決

1. 資料庫的發展過程
層次模型 -->網狀模型 -->關系模型 -->對象關系模型
2. 關於資料庫的概念
DB:資料庫（存儲信息的倉庫）
DBMS：資料庫管理系統（用於管理資料庫的工具）
RDBMS:關系型資料庫管理系統
ORDBMS:對象關系型的資料庫管理系統
3. Oracle資料庫的主要特點
1)支持多用戶、大事務量的處理
2)資料庫安全性和完整性控制
3)支持分布式數據處理
4)可移植性
4.Oracle一些常見問題？
1)如果我只有一張表，為什麼我還要創建資料庫?
sql語言要求所有表都需放在資料庫里。這項設計當然有它好的理由。SQL能控制多為用戶同時訪問表的行為。能夠授予或撤銷對整個資料庫的訪問權。這有時比控制每張表的許可權要簡單很多
2)創建庫的命令的字母全是大寫，一定要這樣嗎？
有些系統確實要求某些關鍵字採用大寫形式。但SQL本身不區分大小寫。也就是說，命令不大寫也可以,但命令大寫是良好的SQL編程慣例。
3)給資料庫、表和列命名時有什麼主意事項嗎？
創建具有描述性的名稱通常有不錯的效果。有時候要多用幾個單詞來命名。所有名稱都不能包含空格，所以使用下劃線能夠讓你創建更具描述性的名稱。命名時最好避免首字母大寫，因為SQL不區分大小寫。極有可能會搞錯資料庫。
4)為什麼不能直接把BLOB當成所有文本值的類型?
因為這樣很浪費空間。VARCHAR或CHAR只會佔用特定空間。不會多於256字元。但BLOB需要很大的存儲空間。隨著資料庫的增長，佔用存儲空間就是冒著耗盡硬碟空間的風險。另外，有些重要的字元串運算無法操作BLOB類型的數據。只能用於VARCHAR或CHAR。
5)為什麼需要INT和DEC這類數值類型？
節省資料庫存儲空間和效率有關。為表的沒列選擇最合適的數據類型可以為表瘦身，還可以使數據操作更為快速。
5.Oracle關系資料庫基礎
1)主鍵：表中其中一列或幾列的組合，其值能唯一標識表中每一行。
表中任何列都可以作為主鍵，但要滿足如下條件：
任何兩行都不具有相同的主鍵值
每個行都必須具有一個主鍵值（主鍵列不允許為null值）
主鍵列中的值不允許修改或更新
主鍵值不能重用（如果某行從表中刪除，它的主鍵不能賦給以後的新行）
一般以id或uuid作為主鍵的名字
2)外鍵是什麼：
在一個關系（參照表）中是主鍵，而另一個關系引用這個鍵。那麼這個鍵在另一個關系中就是外鍵。
3)外建能幹什麼：
使兩個關系（表）形成關聯，外鍵只能引用參照表中的主鍵。保持數據一致性,完整性。
如圖：

4)如何在資料庫內表示一對一

5)如何在資料庫內表示一對多

6)關系模型的完整性約束是什麼？
是通過關系的某種約束條件對關系進行約束。也就是說關系的值隨時間變化時應該滿足一些約束條件。如年齡不能超過1000，性別必須是男或者女
7)關系模型的完整性約束能有什麼?
實體完整性、參照完整性、用戶自定義完整性。
實體完整性：針對基本關系而言，也就是一個二維表,主鍵不能為NULL
參照完整性：表之間存在關系，自然就存在關系的引用（外鍵）,表和表之間的關系通過外鍵實現,外鍵可以為NULL或引用表的主鍵
用戶自定義完整性：針對不同的需求定義自己的完整性約束,如不允許學生編號中出現非數字字元，性別必須是男或者女
6. Oracle自學筆記
1)資料庫和表的名稱不一定要大寫。
2)列是存儲在表中的一塊數據,行是一組能夠描述某個事物的列的集合。列和行構成了表。
3)創建oracle資料庫。使用oracle自帶的Database Configuration Assistant 來創建庫
4)使用DBA身份 創建表空間。具體sql如下：
create tablespace pzw datafile 'C:/oracle/pzw.dbf' SIZE 1024M REUSE AUTOEXTEND OFF extent management local segment space
management auto;
5)刪除用戶命令
drop user pzw cascade;
6)刪除表空間命令
DROP TABLESPACE pzw INCLUDING CONTENTS AND DATAFILES;
7 )查看錶空間命令。

鏈接地址：查看oracle表空間的兩種方式
8)創建用戶。
create user pzw identified by pzw;
9)將包空間分配給用戶
alter user pzw default tablespace pzw;
10)給用戶授權
grant create session, create table, unlimited tablespace to pzw;
11)創建表
CREATE TABLE doughnut_list
(
doughnut_name VARCHAR(10),
doughnut_type VARCHAR(6),
doughnut_birthday DATE
);

12)刪除表
DROP TABLE
doughnut_list;

13)給表中增加一列
Alter table EMP add sale number;

14)資料庫插入一條數據
insert into DEPT_EMP_TABLE (DEPT_EMP_NO,emp_no,Dept_No,Joined_Date) values (009,'00002',1,to_date('2011-2-28 15:42:56','yyyy-mm-dd hh24:mi:ss'));
commit;

15)資料庫修改一條數據
update emp set emp_name='張惠妹',age=20,sex='女',profession='流行歌手' where emp_no = '00002';
commit;
16)資料庫刪除一條數據

delete emp where emp_no = '000013';
commit;

17)查詢全部數據
select * from emp;
18)創建視圖
create view adress_view as select * from pzw.adress;

以下為oracle演示數據操作及練習題（對菜鳥及有用。使用scott 登陸。默認密碼tiger）
1.查看演示數據的表。
select*fromtab
或者selecttable_namefromuser_tables;
2.查看錶結構(plsql操作無效。使用命名提示符 可以操作)
desc dept;
3.查看員工姓名
select ENAMEfrom emp;
4.查詢員工的編號和明星（sql語句不區分大小寫）
select empno, enamefrom emp;
5.查詢所有的欄位
select*fromemp;
一般建議不使用*號，使用*號不明確，建議將相關的欄位寫到select語句的後面,使用*號的效率比較低
6.列出員工的編號,姓名和年薪。
select empno, ename,sal*12from emp;

select語句中可以使用運算符，以上存在一些問題，年薪的欄位名稱不太明確

7.將查詢出來的欄位顯示為中文
select empnoas 員工編號, ename as 員工姓名, sal*12 as 年薪 from emp;
可以採用as命名別名，as可以省略
如：可以採用as命名別名，as可以省略
8.查詢薪水等於5000的員工
select empno, ename, sal from emp where sal=5000;

如果是字元類型的數據進行比較的時候，是區分大小寫的。
9.查詢薪水不等於5000的員工
select empno, ename, sal from emp where sal<>5000;
10.查詢工作崗位不等於manager的員工
select empno,ename,sal,job from emp where job<>
'manager';

在sql語句中如果是字元串採用單引號，引起來，不同於Java中採用雙引號，如果是數值型也可以引起來,只不過是數值類型數據當成字元串來處理
11.查詢薪水為1600到3000的員工（第一種方式，採用>=和<=）
select empno, ename, sal from emp where sal>=1600 and sal<=3000;
查詢薪水為1600到3000的員工（第一種方式，採用between ...and...）
select empno,ename,sal,job from emp where salbetween
1600and 3000;

between ….and …，包含最大值和最小值

between ….and …，不僅僅可以應用在數值類型的數據上，還可以應用在字元數據類型上

between ….and …，對於兩個參數的設定是有限制的，小的數在前，大的數在後
12.查詢津貼為空的員工
select * from emp where commis null;

13.查詢津貼不為空的員工
select * from emp where commis not null;
14.工作崗位為MANAGER,薪水大於2500的員工。
select empno, ename, sal from emp where job='MANAGER'and sal>2500;

and表示並且的含義，表示所有的條件必須滿足
15.查詢出job為manager和job為salesman的員工。
select * from emp where job='MANAGER'or job='SALESMAN';

or，只要滿足條件即可,相當於或者
16.查詢薪水大於1800，並且部門編號為20 或者 30的
select * from emp where sal>1800and (deptno=20or deptno=30);

17.查詢出job為manager和job為salesman的員工
select * from emp where jobin('MANAGER','SALESMAN');
18.查詢job不等於MANAGER並且不能與SALESMAN的員工（第一種寫法）
select * from emp where job<> 'MANAGER' and job <> 'SALESMAN';

19 .查詢job不等於MANAGER並且不能與SALESMAN的員工（第二種寫法）

select * from emp where jobnot in('MANAGER','SALESMAN');

20.查詢以M開頭的所有員工
select * from emp where ename like 'M %';

21.查詢以T結尾的所有員工
select * from emp where ename like '%T';

22.查詢以O結尾的所有員工
select * from emp where ename like '%O%';

23.查詢姓名中第一個字元為A的所有員工
select * from emp where ename like '_A%';

Like可以實現模糊查詢，like支持%和下劃線匹配

Like中%和下劃線的差別？
%匹配任意字元出現任意次數
下劃線只匹配一個任意字元出現一次

Like語句是可以應用在數值類型的數據上的，但是如果沒有使用引號括起來的話，那麼不能使用%和下劃線。類似於等號的操作，如果使用引號括起來的話，那麼可以使用%和下劃線，將數值類型的數據轉換為字元類型後進行處理。
24.按照薪水由小到大排序
s elect * from emporder by sal;

如果存在where子句那麼order by必須放到where語句的後面
25.手動指定按照薪水由小到大排序
select * from emp order by saldesc;

26. 按照薪水和姓名排序
select * from emp order by sal desc ,ename desc;

如果採用多個欄位排序，如果根據第一個欄位排序重復了，會根據第二個欄位排序

select * from emp order by sal asc;

26.手動指定按照薪水由大到小排序
select * from emporder by sal desc;

27.按照薪水升序(使用欄位的位置來排序)
select * from emp order by 6;

不建議使用此種方式，採用數字含義不明確，程序不健壯
28.查詢員工.將員工姓名全部轉換成小寫。
select lower(ename)from emp;

29.查詢job為manager的員工
select * from emp where job=upper('manager');

30.查詢姓名以M開頭所有的員工
select * from emp wheresubstr(ename, 1,1)='M';

方法的第二個參數表示的是查詢字元的位置，0,1都表示第一個字元，負數表示從結尾開始的位置，第三個參數表示截取字元串的長度。
31.取得員工姓名的長度
select length(ename) from emp;

32.取得工作崗位為MANAGER的所有員工
select * from emp where job=trim('MANAGER ');

trim會去首尾空格，不會去除中間的空格
33.查詢1986-02-20入職的員工（第一種方法，與資料庫的格式匹配上）
select * from emp where HIREDATE='20-2月 -81';

查詢1982-02-20入職的員工（第二種方法，將字元串轉換成date類型）
select * from emp where hiredate=to_date('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

to_date可以將字元串轉換成日期，具體格式to_date(字元串，匹配格式)
34.查詢1981- 02-30以後入職的員工，將入職日期格式為yyyy-mm-dd hh:mm:ss
select empno, ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss')from emp where hiredate>to_date('1981-02-2000:00:00', 'YYYY-MM-DD HH24:MI:SS');

35.查詢員工薪水加入前分位
select empno, ename, to_char(sal, '$999,999') from emp;

36.查詢薪水加入千分位和保留兩位小數
select empno, ename, to_char(sal, '$999,999.00') fromemp;

將數字轉換成字元串，格式

控制符

說明

9

表示一位數字

0

位數不夠可以補零

$

美元符

L

本地貨幣符號

.

顯示小數

,

顯示千分位

37.將字元串轉換成數值
select * from emp where sal>to_number('1,500','999,999');
38.取得員工的全部薪水，薪水+津貼
select empno, ename, sal, comm, sal+nvl(comm,0) fromemp;

39.如果job為MANAGER薪水上漲10%，如果job為SALESMAN工資上漲50%（case … when … then …end）
select empno, ename, job, sal, (casejobwhen 'MANAGER' thensal*1.1when 'SALESMAN' thensal*1.5end)as newsal from emp;

40.如果job為MANAGER薪水上漲10%，如果job為SALESMAN工資上漲50%（decode）
select empno, ename, job, sal, decode(job,'MANAGER', SAL*1.1, 'SALESMAN',sal*1.5) as newsal from emp;

41.四捨五入
select round(1234567.4567, 2) from al;

Dual是oracle提供的，主要為了方便使用，因為select的時候需要用from
42.聚合函數

count

取得記錄數

sum

求和

Avg

取平均

Max

取最大的數

min

取最小的數

43.取得所有員工人數
select count(*) from emp;

Count（*）表示取得所有記錄，忽略null，為null值也會取得
44.取得津貼不為null的員工數
select count(comm) from emp;

採用count(欄位名稱)，不會取得為null的紀錄
45.取得工作崗位的個數
select count(distinctjob) from emp;

Distinct可以去除重復的紀錄
46.取得薪水的合計
select sum(sal) from emp;

47取得薪水的合計（sal+comm）
select sum(sal+nvl(comm, 0)) from emp;
48.取得平均薪水
select avg(sal) from emp;

49.取得最高薪水
select max(to_char(hiredate, 'yyyy-mm-dd')) from emp;

50.取得最小薪水
select min(sal) from emp;

51.取得最早入職的員工
select min(hiredate) from emp;

52.可以將這些聚合函數都放到select中一起使用
select count(*), sum(sal), avg(sal), max(sal),min(sal) from emp;

53.取得每個崗位的工資合計，要求顯示崗位名稱和工資合計。
select job, sum(sal) from empgroupby job;

採用group by，非聚合函數所使用的欄位必須參與分組，
Group by中不能使用聚合函數
如果使用了order by，order by必須放到group by後面
54。取得每個崗位的平均工資大於2000
select job, avg(sal) from emp group by job having avg(sal) >2000;

分組函數的執行順序：
1、 根據條件查詢數據
2、 分組
3、 採用having過濾，取得正確的數據
55. 顯示每個員工信息，並顯示所屬的部門名稱
select ename ,dname from emp a ,dept b where a.deptno = b.deptno;

以上查詢也稱為「內連接」，指查詢相等的數據
56.取得員工和所屬的經理的姓名
select a.ename, b.ename from emp a, emp b wherea.mgr=b.empno;

以上稱為「自連接」，只有一張表連接，具體的查詢方法

57.（內連接）顯示薪水大於2000的員工信息，並顯示所屬的部門名稱
SQL99語法：
select ename,sal,dname from emp a join dept b on a.deptno = b.deptno where sal>2000；
SQL92語法
select ename,sal,dname from emp a, dept b where a.deptno=b.deptno and sal > 2000；

Sql92語法和sql99語法的區別：99語法可以做到表的連接和查詢條件分離，特別是多個表進行連接的時候，會比sql92更清晰
58.（外連接）顯示薪水大於2000的員工信息，並顯示所屬的部門名稱，如果某一個部門沒有員工。那麼該部門也必須顯示出來
select dname,ename from emp a right join dept b on a.deptno = b.deptno;
59.查詢員工名稱和所屬經歷的名稱，如果沒有上級經理，也要查詢出來
Select e.ename, m.ename mname from emp e, emp mwhere m.empno(+) = e.mgr;
60.查詢員工信息，查詢哪些人是管理者，要求顯示出其員工編號和員工姓名
select ename,ename from emp where empno in (select distinct mgr from emp where mgr is not null);
61.查詢那些人的薪水高於員工的平均薪水，需要顯示員工編號，員工姓名，薪水。
select empno, ename, sal from emp where sal>(selectavg(sal) from emp);

分析思路：首先根據文字描述找出被依賴的條件，逐次分析
62.查詢各個部門的平均薪水所屬的等級，需要顯示部門編號，平均薪水，等級編號

select a.deptno, a.avg_sal, b.grade from (select deptno, avg(sal) avg_sal fromemp group by deptno) a, salgrade b where a.avg_sal between b.losal and b.hisal;

關鍵點：將子查詢看作一張表
63.查詢員工信息以及部門名稱
Select e.empno, e.ename, e.deptno, (select dname from dept where deptno = e.deptno) as dname from emp e
64.union可以合並集合（相加）

select * from emp where job='MANAGER'
union
select* from emp where job='SALESMAN'
65.minus可以移出集合（相減）
查詢部門編號為10和20的，取出薪水大於2000的。

select * from emp where deptno in(10, 20)
minus
select* from emp where sal>2000
66.rownum隱含欄位
select rownum, a.* from emp a;
67.取得前5條數據
select * from emp where rownum <=5;
68.取得薪水最好的前5名

select empno, ename, sal from (select empno,ename, sal from emp order by sal desc)whererownum <=5