oracle創建資料庫「oracle數據源配置」

Oracle 資料庫

1. Oracle資料庫：

Oracle公司成立於1977年，最早開發資料庫的廠商，經過近40年的發展，oracle公司目前已經是世界資料庫以及相關領域領導者。

Oracle資料庫系統是由Oracle公司（甲骨文）提供的一款關係資料庫管理系統。

目前主流的版本是Oracle9i，Oracle10g，Oracle11g，Oracle12c

表是資料庫中存儲數據的基本單元。

2. 表空間：

1）表空間是用來存儲資料庫對象（表，視圖，索引，序列…）的容器，一個表空間只能屬於一個資料庫。所有的資料庫對象都存放到指定的表空間中 。

2）表空間是由一個或者若干個數據文件組成，這些數據文件是資料庫實際存放數據的位置

3）每個資料庫創建的時候，系統都會默認的創建一個SYSTEM表空間，一個資料庫可以包含若干個表空間，也可以 只有一個SYSTEM表空間。

3.Oracle中常用數據類型

1）字元型：char， varchar，varchar2

a）char（n）：用來保存固定長度的字元串，n代表字元串的長度，當實際資料庫不足定義的長度，右邊會使用空格補全

比如: char（10）’liu’,實際保存時候佔據10個存儲空間，浪費了7個存儲空間

b）varchar（n）：可變的字元類型，n代表字元串的長度。當實際數據小於n的時候，則按照實際長度進行存儲數據

比如：varchar2（10）。『liu』實際保存時候佔據3個存儲空間，比較節約空間

c）varchar2（n）：Oracle自行定義的類型，兼容性特別好，建議使用varchar2取代varchar類型。

2.）數值型：number

number：可以用來存儲整數和小數類型。

number（n）：只能保存整數m ，整數的最大的位數是n。

比如：number（3），表示能保存最大的整數的位數是3。

number（n，m）：可以保存有效數字的位數最多是n，小數的位數最多是m，整數的最多的位數是n-m

比如：number（7,2），7表示有效數字的位數是7,2代表小數的位數，5代表整數的位數。

3）日期時間類型：date

data：包含（世紀），年，月，日，時，分，秒

DD-MON-YY（默認日期格式）

DD:幾號

MON：月份

YY：年份，後兩位年份

比如：2011-12-23 23-12月-11

SQL語句是學習的重點，掌握了SQL語句就可以操作任意的資料庫。

SQL語句的分類：

1）數據定義語言（DDL）：用來創建，修改，刪除表結構

create（創建） drop（刪除） alter（修改）

2）數據操作語言（DDL）：用來操作表中的數據，完成插入，修改，刪除功能

insert（插入） delete（刪除） update（修改）

3）事務控制語言（TCL）：用來管理資料庫中的事務

commit rollback

4）數據查詢語言（SQL）:用來查詢所需要的數據

select

5.創建表：

create table person1(

id number(4) primary key,

name varchar2(12),

age number(3)

);

create table person2(

id number(4) primary key,

name varchar2(12)

);

create table person3(

id number(4) primary key,

name varchar2(12),

job varchar2(12)

);

create table person4(

id number(4) primary key,

name varchar2(12),

address varchar2(12),

age number(3)

);

案例：創建一張表customer， cid number(4)

編號 主鍵，cname varchar2（12）姓名，

sex char（3）性別，address varchar2（12）地址，phone varchar2（11），email varchar2（50）

create table customer(

cid number(4) primary key,

cname varchar2(12),

sex char(3),

address varchar2(12),

phone varchar2(11),

email varchar2(50)

);

案例：創建一張book表，bid number（4）圖書

編號 主鍵，bname varchar2(50)書名，pub varchar2(50) 出版社， author varchar2（50）作者，type varchar2（50）類型，numinput number（10）進貨量，numstore number（10）庫存量

create table book1(

bid number(4) primary key,

bname varchar2(50),

pub varchar2(50),

author varchar2(50),

type varchar2(50),

numinput number(10),

numstore number(10)

);

2) desc 表名：查詢表結構

案例：desc person1；desc person2;

6插入操作（insert）：

1）向所有列都插入列值

格式：

insert into 表名

values（列值1，列值2，列值3…..）

a）每次執行insert語句，往表中插入一條數據

b）不要反覆地執行同一條insert語句，避免出現重複數據。

c）insert語句中任意一個位置出現錯誤，則整個數據都無法插入表中。

insert into person1

values(1,’李世民’,32);

d)主鍵所修飾的列的列值非空唯一。往person1表中任意插入3條數據

insert into person1

values(10,’程咬金’,30);

insert into person1

values(11,’李靖’,28);

insert into person1

values(12,’蘇烈’,24);

2）查詢表中所有的數據：

select * from 表名；

insert into person2 values(1,’劉備’);

2)向指定列插入列值

格式：

insert into 表名（列名1，列名2，列名3…）

values（列值1，列值2，列值3….）

a）表名中的列必須跟values中列值一一對應。

b）主鍵所在的列，必須要插入列值

insert into person4(id) values(1);

insert into person4(id,name) values(2,’曹操’);

7.修改語句（update）

1)格式：

update 表名 set 列名1=該列新值，

列名2=該列新值，列名3=該列新值

where 條件：

2）根據where條件修改表中的數據，如果沒有where條件修改表中所有的數據

person2;

ID NAME

—– —————–

1 劉備

2 關羽

3 張飛

4 諸葛亮

案例：修改person2表ID是2的信息，把姓名改為馬超

update person2 set name=’馬超’

where id=2；

where條件

根據where條件修改表中對應的數據，如果沒有where條件，修改表中所有的數據

案例：練習修改

2刪除語句

1）使用delete刪除

delete from 表名 where 條件

根據where條件，刪除表中的數據，如果沒有where條件，刪除表中所有的數據

案例： 刪除person表中所有的數據

案例2：刪除person4表中id=1的數據

案例3：刪除person4表中姓名是劉備的數據

2）使用truncate刪除：直接刪除表中所有的數據，而且刪除速度很快，但是刪除的數據不能還原（不能恢復）

格式：truncate table=表名

truncate table person4;

案例： 刪除person2，person3表中的數據

3）刪除表

格式：drop table 表名;

drop table person1;

案例：刪除person2，person3，person4

4簡單的查詢語句（沒有where條件）

1）查詢表中所有的數據

格式：

select * from 表名（emp/dept）

2）查詢表中某些列的列值

格式：

select 列名1，列名2，列名3….from表名

select empno,ename,job from emp

案例：查詢emp表中員工的編號，姓名，職位，工資，入職時間

select empno,ename,job,sal,hiredate from emp

案例：查詢emp表中員工的編號，姓名，上級領導（經理）的編號，入職時間

select empno,ename,mgr,hiredate from emp

案例：查詢emp表中員工的姓名，工資，獎金，所屬部門的編號

select ename,sal,comm,deptno from emp

案例：查詢dept表中部門的名稱和地址

select dname,loc from dept

案例：查詢salgrade表中登記最低工資和最高工資

select losal,hisal from salgrade

5.別名：給表或者列起別名

1)給列起別名

a）使用as

格式：

select 列名1 as 別名1，列名2 as 別名2…from表名

–select empno as 員工的編號， enamel as 姓名，job as 職位 from emp

案例：查詢emp表中員工的姓名，職位，上級領導的編號，入職時間以及工資，對查詢的列起別名

select ename as 姓名,job as 職位,mgr as 上級領導的編號,hiredateas 入職時間,sal as 工資 from emp

案例：查詢dept表中所有的信息，對查詢返回的每一個列都起別名

–select deptno as 部門編號,dname as 部門名稱,loc as 部門地址 from dept

b）使用空格起別名

格式：

select 列名1 別名1， 列名2 別名2…. from 表名

select deptno 部門編號，dname 部門名稱 from dept

案例：查詢emp表中員工的姓名，職位，工資，獎金以及所在部門的編號，對查詢返回的每一個列都去起別名

–select ename 姓名,job 職位,sal 工資,comm 獎金,deptno 所在部門的編號

from emp

2）給表起別名：多表連接查詢中，容易出現多張表中列名一致現象，通過給表起列名，別名指向對應表中的列

格式：

select 別名.列名1，別名.列名2….

from 表名 別名

select e.aname ,e.job,e.sal,e.hiredate

from emp e

6排序：對查詢返回的結果，根據某一個列或者多個列的列值大小進行升序或者降序排列

1.根據某一個列的列值大小進行升序或者降序排列

格式：

select */列名 from 表名

order by 列名 asc/desc

asc：升序，默認值

desc：降序

案例：查詢emp表中員工的編號，姓名，職位，根據與昂的編號進行降序排列

–select empno,ename,job from emp

order by empno desc

案例：查詢emp表中員工的姓名，職位，工資，入職時間以及獎金，最後根據員工的工資進行升序排列

–select ename,job,sal,hiredate,comm from emp

order by sal asc

案例：查詢emp表中員工的編號，姓名，入職時間以及上級領導的編號，最後根據入職時間進行升序排列

— select empno,ename,hiredate,mgr from emp

order by hiredate asc

案例：查詢emp表中員工的編號，姓名，職位以及所屬部門的編號，最後根據部門的編號進行降序排列

–select empno,ename,job,deptno from emp

order by deptno desc

2）根據多個列的列值大小進行升序或者降序排列

格式：

select */列名 from 表名

order by 列名1 asc/desc，列名2 asc/desc

首先根據列名1中的列值大小進行升序或者降序排列，如果列名1中的列值大小一致，根據列名2中的列值大小進行升序或者降序排列

案例：查詢emp表中員工的姓名，職位，工資，獎金。入職時間，首先根據員工的工資進行升序排列，如果工資一致根據員工的入職時間進行降序排列

— select ename,job,sal,comm,hiredate from emp

order by sal ,hiredate desc

案例：查詢emp表中員工的編號，姓名，職位，工資，入職時間以及所屬部門的編號，根據員工 所屬部門

的編號進行升序排列，如果部門編號一致，根據原的編號進行降序排列

— select empno,ename,job,sal,hiredate,deptno from emp order by deptno,empno desc

3)排序永遠是最後被執行的。

7.distinct：去掉重複的列值

案例：查詢emp表中部門的編號

–select distinct deptno from emp

案例：查詢emp表中員工的職位名稱（去掉重複的職位）

–select DISTINCT job from emp

8.nvl（）函數的使用：

1）數字類型的數據可以進行正常的算術運算

案例：查詢emp表中員工的姓名，職位，工資以及入職的時間，年薪（工資*12）

— select ename,job,sal,hiredate,sal*12 年薪 from emp

知識點：

1. nvl（）函數的使用

案例：查詢emp表中員工的姓名，職位，工資，以及入職時間和年薪（工資*12+獎金）

1）任何空值（null）使用「+」來連接其他類型的數據，返回的結果也是空值。

2）nvl（）函數：專門用來處理空值的問題

nvl（列名，數值）：如果改列的列值不為null，返回改列的列值；如果改列的列值為null，返回是數值。

nvl(comm,o):如果改列的列值不為null，返回comm的列值；如果改列的列值為null，返回時0

–select ename,job,sal,comm,sal*12+nvl(comm,0)年薪 from emp

2帶條件的查詢語句：

格式：

select */列名 from 表名

where 條件

根據where條件查詢對應的數據

1）關係運算符；

>，>=（大於或者等於）,<,<=（小於或者等於）,=,

!=(不等於)，<>（不等於）

案例：查詢emp表中工資高於1000的所有元的編號，姓名，職位，工資

–select empno,ename,job,sal from emp

where sal > 1000

案例：查詢emp表中姓名是King的 員工的編號，姓名，職位

select empno,ename,job from emp

where ename=’KING’

案例：查詢工資低於等於3000的所有員工的編號，姓名，職位，工資，最後根據工資進行升序排列

— select empno,ename,job,sal from emp

where sal<=3000

order by sal

案例：查詢emp表中職位不是『manager』的所有元的編號，姓名，職位，工資，最後根據編號進行降序排列（兩種方法）

— select empno,ename,job,sal from emp

where job <> ‘MANAGER’（job！=’MANAGER’）

order by empno desc

2)判斷改列的列值是否為空值（null）

列名 is null：改列的列值為null

案例：查詢emp表中獎金為空的員工的編號，姓名，職位以及獎金。

–select empno,ename,job,comm from emp

where comm is null

案例：查詢emp表中沒有上級領導的員工的編號，姓名，上級領導標號以及工資

–select empno,ename,mgr,sal from emp

where mgr is null

列名 is not null：改列的列值不為null

案例：查詢emp表中含有上級領導的員工 所有的信息

–select* from emp where mgr is not null

案例：查詢emp表中含有獎金的員工的編號，姓名，職位，工資，獎金，最後根據編號進行降序排列

3）like：模糊查詢

%：指代任意的0個或者多個字元。

_：指代任意的一個字元

案例：查詢emp表中員工的姓名的第一個字元時『s』的員工的姓名

分析：『s』的後面可能有也有可能沒有字元 s%

–select ename from emp

where ename like ‘S%’

案例：查詢emp表中名字中的最後一個字元是「e」的員工的姓名

–select ename from emp

where ename like ‘%E’

案例：查詢emp表中名字中包含『E』的所有員工的信息

分析：1）以「E」開頭 E%

2）以「E」結尾 %E

3）包含「E」%E%

%E%包含所有的可能性

–select * from emp

where ename like ‘%E%’

案例：查詢emp表中名字的倒數第2個字元是『k』的員工的姓名

分析：首先字元 ‘k’DE 前面可能有字元也有可能沒有字元，在’k’的後面一定只有一個字元

–select * from emp

where ename like ‘%K_’

案例：查詢emp表中員工的倒數第三個字元是’i’的員工的姓名

–select ename from emp

where ename like ‘%I_ _’

not like：

案例：查詢emp表中名字不是以『S』開頭的信息

–select *from emp

where ename not like ‘S%’

案例：查詢emp表中名字的最後一個字母不是’N’的員工的姓名

案例：查詢emp表中姓名中沒有字母k所有員工的編號，姓名，工資，最後根據原的編號進行升序排列

3.邏輯運算符：and，or，not

1）and：並且，連接多個條件，都必須滿足

案例：查詢emp表中員工的工資在1000-3000之間的每一個員工的編號，姓名，職位，工資

–select empno,ename,job,sal from emp

where sal >=1000 and sal <=3000

案例：查詢emp表中員工的職位是’MANAGER’，並且在30號部門所在的所有員工的所有信息

— select * from emp

where job=’MANAGER’ and deptno=’30’

案例：查詢emp表中含有上級領導的員工，並且名字中不包含字母『s』的所有員工的編號，姓名，職位，工資，最後根據員工的編號進行升序排列

— select empno,ename,job,sal from emp

where mgr is not null and ename not like ‘%S%’

order by empno

2）or：或者，連接多個或者關係的條件

案例：查詢emp表中員工的工資高於1000，或者員工在20號部門的員工的所有信息

select * from emp

where sal > 1000 or deptno = 20

案例：查詢emp表中名字的倒數第二個字母是『L』或者沒有上級的員工的編號，姓名，職位，工資，最後根據工資進行升序排列，如果工資一致，根據員工的編號進行降序排列

–select empno,ename,job,sal from emp

where ename like ‘%L_’ or mgr is null

order by sal, empno desc

案例：查詢emp表中員工不在10號部門。或者工資低於3000的所有員工的編號，姓名，職位，工資。所在部門的編號以及入職時間，最後根據入職時間進行升序排列

— select empno,ename,job,sal,deptno,hiredate from emp

where deptno != ’10’ or sal < 3000

order by hiredate

3）not：否定，對整個條件的否定（取反）

案例：查詢emp表中名字不是king的信息

–select * from emp

where ename <> ‘KING’

select * from emp

where not ename =’KING’

案例：查詢emp表中不是30號部門的所有員工的姓名，編號

–select empno,ename, deptno from emp

where not deptno=30

4 聚合函數（分組）：

count（），sun（），avg（），max（），min（）

1）count（*/列名）：「*」統計該表中數據的總條數；「列名」統計該列中列值不為null的數據的總條數。

案例：查詢emp表中員工總人數

–select count(*) from emp

案例：查詢emp表中查詢含有上級領導的員工的數量

— select count(mgr) from emp

案例：查詢emp表中含有獎金的員工的數量

— select count(comm) from emp

案例：查詢emp表中職位種類的個數

— select count(distinct job)from emp

2）sum（列名）：統計該列的列值總和

–select sum (sal) from emp

案例：查詢emp表中獎金的累加之和

–select sum(comm) from emp

3）avg（列名）：統計該列的平均值

–select avg(comm) from emp

案例：查詢emp表中工資的平均值

– -select avg(sal) from emp

4）max（列名）：統計該列的最大值

案例：查詢emp表中員工的最高工資

— select max(sal) from emp

案例：查詢emp表中員工的編號最大值

— select max(empno) from emp

5）min（列名）：統計該列的最小值

案例：查詢emp表中員工的最低工資和最低獎金

–select min(sal) 最低工資, min(comm) 最低獎金 from emp

案例：查詢emp表中員工的人數，工資的總和，平均工資，最高工資，最低工資，對查詢返回的列都起別名

— select count(*) 總人數,SUM(sal) 工資總和,avg(sal) 平均工資,max(sal) 最高工資,

min(sal) 最低工資 from emp

5.分組：根據某一個列把表中的數據分成幾組，然後經常對每一組的數據使用聚合函數（分組函數）。

1）格式：

select 列名/聚合函數 from 表名

where 條件

group by 列名

order by 別名/列名/聚合函數 asc/desc

2）執行的順序：首先執行where條件，對表中所有的數據進行過濾，然後執行group by根據某一個列進行分組（分組以後的每一組數據使用聚合函數），最後執行order by，對查詢返回的結果進行排序。

案例：查詢emp表中每一個部門的編號，人數，最低工資，最高工資，最後根據部門的編號進行升序排序

–select deptno,count(*),min(sal),max(sal)

from emp

group by deptno

order by deptno asc

案例：查詢emp表中工資在1000-3000之間的員工的信息，根據部門分組，查詢每個部門的編號，人數，工資總和，平均工資，最後根據平均工資，進行降序排序

–select deptno,count(*),sum(sal),avg(sal) 平均工資

from emp

where sal>=1000 and sal<=3000

group by deptno

order by avg(sal) 平均工資desc

案例：查詢emp表中含有上級領導的員工，每個職業的名稱，人數，平均工資，工資總和，最後根據人數進行升序排列，如果人數一直根據工資總和進行降序排列

— select job,count(*),avg(sal),sum(sal) from emp

where mgr is not null

group by job

order by count(*) asc,avg(sal) desc

案例：查詢emp表中名字不是以『s』開頭，並且不在20號部門的員工，每個職業的名字，人數，最高工資和最低工資，最後根據最高工資進行降序排列

— select job,count(*),max(sal),min(sal) from emp

where empno not like ‘S%’ and not deptno=20

group by job

order by max(sal) desc

6.Oracle中常用字元處理函數：用來處理char，varchar，varchar2類型數據。

1）length（列名/字元串）：統計當前列值/字元串中字元的個數

–select ename,length(ename) from emp

–select length(‘hello word’) from emp

2)dual:虛擬表，Oracle中專門用來測試的表

–select length(‘hello werld’) from dual

3)upper(列名/字元串)：把當前的列值/字元串中的小寫字母改為大寫字母

–select upper(‘hello WORLD’) from dual

4)lower(列名/字元串)：把當前的列值/字元串中的大寫字母改為小寫字母。

–select lower(‘hello WORLD’) from dual

案例：查詢emp表中員工的姓名和職位 把姓名和職位所有的字母都改為小寫字母顯示

— select ename,lower(ename), job,lower(job) from emp

5）trim(列名/字元串)：刪除列名/字元串兩端的空格

–select trim (‘ hello world ‘) from dual

6)串聯字元串

a）||

–select ename || job from emp

–select ename ||’,’|| job from emp

b)concat（字元串1，字元串2）

select concat(‘努力’,’學習’) from dual

7）substr（參數1，參數2，參數3）：用於截取字元串

參數1：要截取列名/字元串

參數2：如果為正數表示從1開始，根據正數的位置開始往後截取，如果為負數，表示從倒數第幾個開始往後截取

參數3：要截取的字元個數

–select substr(‘hello’,3,2) from dual

–select substr(‘hello’,-3,2) from dual

案例：查詢emp表中員工的姓名和姓名中倒數後兩個字元

— select ename,substr(ename,-2,2) from emp

length(ename)-1:倒數第2個字元位置

–select ename,substr(ename,

length(ename)-1,2)

from emp

案例：查詢emp表中員工姓名的倒數後3個字元

–select ename,substr(ename,-3,3) from emp

—select ename,substr(ename,

length(ename)-2,3)

from emp