oracle 數據庫有服務端和客戶端組成

數據庫:

基本的概念:

數據庫管理系統(Database Management System,DBMS):

管理(存儲+操作(CRUD))數據的一個軟件系統

關系型數據庫管理系統(RDBMS):

數據庫(Database):存放數據的磁盤,是RDBMS的一部分

市面上常見的數據庫管理系統有哪些?

ORACLE, Sql Server, Sqlite(手機數據庫), DB2, Access, mysql,sybase,h3...

SQL(Structure Query Language)結構化查詢語言,

它是操作數據庫管理系統的一個標準:主要對數據庫進行CRUD的操作,

數據庫的運算都是通過SQL來完成的;

create:創建

delete:刪除

update:修改

retrieve:檢索

市面上的主流數據庫都遵守SQL規范:

注:不同的數據庫對SQL的支持又有些不同;

sql根據命令功能的不同分為5個部分:

1.DCL(Data Control Language)數據控制語句:grant,revoke

2.DDL(Data Definition Language)數據定義語句:

create,alter,drop,truncate,comment on,rename to...

3.DML(Data Manipulating Language)數據操縱語句:insert,update,delete

4.DQL(Data Query Language)數據查詢語句:select

5.DTL(Data Transaction Language)數據事務語句:commit,rollback,savepoint

-----------------------------------------------------------------------------

Oracle www.oracle.com  

...8i,9i,10g,11g,12c

安裝注意點: 目錄中不能由中文:建議把防火墻和360一些安全軟件關閉;

oracle 數據庫有服務端和客戶端組成:

服務端:由一堆的數據庫后臺進程與監聽器以及數據庫所組成

最主要的兩個服務:

OracleServiceXE Oracle數據庫服務主服務(數據庫實例名,倉庫名)

OracleXETNSListener Oracle數據庫監聽器,監聽客戶端對數據庫的訪問連接

客戶端:有多種

1.oracle自帶一個命令行客戶端:sqlplus

  啟動:運行cmd 打開cmd命令窗口輸入sqlplus

2.PL/SQL Developer 第三方圖形界面

3.Navicat 第三方圖形界面

-----------------------------------------------------------------------------

創建用戶2步驟:

1.創建一個(普通)用戶:

語法:create user 用戶名 identified by 密碼;

2.授權給一個用戶:

角色:oracle中內置了3個角色:

DBA:  系統管理權限;

connect:  創建會話連接權限;

resource: 操作基本數據庫對象的權限;

 grant 權限,權限... to 用戶名;

修改密碼: alter user 用戶名 identified by 新密碼;

刪除用戶: drop user 用戶名;(刪除時注意權限問題)

賬戶解鎖: alter user 用戶名 account unlock;

-----------------------------------------------------------------------------

sqlplus中常用命令:

1.切換用戶:

  conn 用戶名/密碼;

2.顯示當前登錄的用戶名:

  show user;

3.執行數據庫腳本:

  start 或者 @ *.sql(可以直接把腳本拖到命令窗口中)

  例如:start D:/oralce_cn.sql;

  數據庫腳本:用于創建數據庫對象的語句集合

  目的:在數據庫中創建儲存數據的表格并且在表格中初始化數據

4.查看表的結構:這是sqlplus的命令,不是sql的命令

  desc 表的名字;

5.清屏:

  clear screen;

6.回憶記錄:

  方向鍵上下

7.編輯多行sql語句:

  edit

-------------------------------------------------------------------------

基本查詢:

表格(table):它是由行(rows)和列(column)組成的二維空間

它是數據庫存貯數據的基本單位,將來我們的數據就是存貯在表中;

查詢的本質:

1.要查什么:

2.去哪里查:

語法: select 列名 from 表格名;

注意:如果查詢所有列,則用*代替!

例如:

-- 找出員工的所有的信息;

select * from s_emp;

-- 從s_emp表中查詢出員工的名字,薪水,職位;

select first_name,salary,title from s_emp;

-- 從s_emp表中查出員工的姓氏,名字;

select first_name,last_name from s_emp;

-- 找出本公司的所有的職稱(title);

select title from s_title;

select distinct title from s_emp;

-- 找出所有員工的姓名、工資、入職日期

select first_name,salary,start_date from s_emp;

-- 找出所有的客戶名及他的電話號碼

select name,phone from s_customer;

-- 找出員工姓名及他的職稱

select first_name,title from s_emp;

-- 找出每個訂單的費用、支付方式、出貨日期

select total,payment_type,date_shipped from s_ord;

排重: distinct

      select distinct title from s_emp;

--------------------------------------------------------------------------

列的數據類型:

1.數字類型,可以做所有的數學運算

  number     

  number(4) 代表整數 最大能存9999

  number(7,2) 代表double類型  整數長度為5 小數位為2.

2.字符型,可以做拼接的運算

  注:字符串在數據庫中用''

  varchar2(20) 代表字符串最長為20;

  char(20) 代表定長20;

3.日期類型,可以做加減運算

  Date 年月日時分秒

  Timestamp 年月日時分秒,還有小數位,如3.2秒

4.大數據類型

  clob character large object 大字符型對象,最大可存4G

  blob binary large object 大二進制對象,最大可存4G

注:大數據類型不支持查看結果

如:

sql具有運算的能力

--從員工表中查詢出員工名字和他的年薪

select first_name 姓名,salary*12 年薪 from s_emp;

select first_name "姓   名",salary*12 "年   薪" from s_emp;

sql具有字符串拼接能力并且可以給列取名

-- 從s_emp表中查出員工的姓名;

select first_name||' '||last_name 全名 from s_emp;

--字符串拼接(***是**入職的,工資是***,職稱是***)

select first_name||' '||last_name||'是'||start_date||'入職的,工資是'||salary||',職稱是'||title  描述 from s_emp;

注意:

oralce數據庫中:字符串使用''包裹,""可以取別名,以及用來保存格式,還可以區分大小寫

""的作用給我們的查詢出的列起個別名 并保持格式

 別名不一定需要"";如果要保持格式必須有""

-----------------------------------------------------------------------------

空值置換函數(nvl)

--查詢所有員工的名字,年薪(不考慮提成)

select first_name,salalry*12 from s_emp;

null和所有的值進行運算最后都為null;

空值置換函數nvl(commission_pct/100,0)

如果commission_pct/100的值為null 則表達式的值為0

如果commission_pct/100的值不為null 則表達式的值為commission_pct/100

--查詢所有員工的名字,年薪(考慮提成)

select first_name,salary*12*(1 + nvl(commission_pct/100,0)) from s_emp;

注:nvl()中的兩個值要為同一數據類型

---------------------------------------------------------------------------

條件查詢:

where字句用來過濾查詢的數據,它對字面量大小寫是敏感的

出現在where后面的運算符有:

1.比較運算符:

 > , < , >= , <= , =  , 不等于(<> != ^= )

--查出在41部門的員工名字,工資;

select first_name,salary from s_emp where dept_id=41;

--查出工資高于1500的員工的信息;

select * from s_emp where salary>1500;

-- 找出工資大于1200元的員工全名、工資、職稱

select first_name||' '||last_name 全名,salary 工資,title 職稱

from s_emp where salary>1200;

2.邏輯運算符:

and,or,not

--查出41部門工資高于1200的員工名字,工資;

select first_name,salary from s_emp where dept_id=41 and salary>1200;

--查出在41,50,42部門的員工名,薪水;

select first_name,salary from s_emp where dept_id=41 or dept_id=42 or dept_id=50;

3.其他運算符:

in(list) list是一個列表，多個值使用逗號隔開

not in()

between ? and ? 在指定的范圍之內，是全閉空間

is null

is not null

模糊匹配:like

通配符:

_ 通配任意單個字符

% 通配任意多個字符

--查出在41,42,50部門的員工名,薪水;

select first_name,salary from s_emp where dept_id in(41,42,50);

--找出工資在1200到1500之間的員工名

select * from s_emp where salary>=1200 and salary<=1500;

select * from s_emp where salary between 1200 and 1500;

--找出工資大于1500并且沒有提成的員工;

select * from s_emp where salary>1500 and commission_pct is null;

注意:查詢數據時條件是否為null,我們使用關鍵字is或者is null,千萬不能使用=/!=

--查出名字是以M打頭的員工;

select * from s_emp where first_name like 'M%';

--查出姓名中第三個字母是e的員工;

select * from s_emp where first_name like '__e%';

如果我們要通配的字符中本身就有_或%,則

需要通過關鍵字escape來轉義;

select * from s_emp where first_name like '/_A%' escape '/';

練習:

-- 找出沒有提成率的員工

select * from s_emp where commission_pct is null;

-- 找出有提成率的員工

select * from s_emp where commission_pct is not null;

-- 找出費用超過10000元的訂單編號及支付方式

select id,payment_type from s_ord where total>10000;

-- 找出工資在950(含)至1200(含)元的員工姓名、職稱

select first_name,title from s_emp where salary between 950 and 1200;

-- 找出名字中含有字母a的員工 

select * from s_emp where first_name like '%a%';

-- 找出名字中第二個字母是a的員工

select * from s_emp where first_name like '_a%';

-- 找出2月份入職的員工名、入職時間、工資

select first_name,start_date,salary from s_emp where 

start_date like '%2月%';

-- 查詢出職稱是Stock Clerk的員工全名、工資，并按工資的降序排序

select first_name||' '||last_name,salary from s_emp where

title='Stock Clerk' order by salary desc;

select first_name||' '||last_name,salary from s_emp where

title='Stock Clerk' order by 2 desc;

注意:order by 2表示按照查詢結果的第二列來排序!

--------------------------------------------------------------------------

排序字句:

order by 列名 asc(升序,默認可不寫) | desc (降序)

--找出6月份入職的員工全名,工資,職稱,并按工資降序排序

select first_name||' '||last_name 全名,salary,title from s_emp where start_date like '%6月%' order by 2 desc;

--查詢出職稱中帶VP的員工名字,工資,并按工資的降序排序

select first_name,salary,title from s_emp where title like '%VP%' order by 2 desc;

--查詢出年薪低于25000的員工名,職稱,并按年薪升序排序

select first_name,title,salary*12*(1+nvl(commission_pct/100,0)) 

from s_emp where salary*12*(1+nvl(commission_pct/100,0))<25000 order by 

3;

--------------------------------------------------------------------------

dual是一張虛擬的表格,沒有任何的意義,只是為了充當構建完整的select語句

如:查詢當前數據庫系統時間

select sysdate from dual;

單行函數(single function):

多行函數(組函數)

oracle中提供了大量的函數

處理方式的不同分為:

1.數字函數:

  round(列|值|表達式,有效位數); 四舍五入

  trunc(列|值|表達式,有效位數); 直接截取

  mod(列|值,列|值); 求模(余)

  abs(列|值) 求絕對值

例如:

select round(3.1415926,3) from dual;

select trunc(3.1415926,3) from dual;

select mod(10,3) from dual;

select abs(-10) from dual;

--查詢出訂單表中總金額(取整)

select round(total,0) from s_ord;

2.字符函數

length(列|值|表達式); 求長度

例如:

select length('abc') from dual;

select first_name,length(first_name) from s_emp;

upper(列|值|表達式); 轉大寫字母

lower(列|值|表達式); 轉小寫字母

initcap(列|值|表達式); 每個單詞首字母大寫

例如:

select upper('abc') from dual;

select lower('ABC') from dual;

select initcap('hello world') from dual;

lpad(列|值,寬度,填充字符);  從左邊補不足寬度個填充字符

rpad(列|值,寬度,填充字符);  從右邊補不足寬度個填充字符

例如:

select lpad('abc',10,'de') from dual;

ltrim(列|值,截取字符); 從左邊截取字符 

rtrim(列|值,截取字符); 從右邊截取字符

例如:

select rtrim('abcdededededede','de') from dual;

select rtrim('abcdededeedede','de') from dual;

select rtrim('abcdededeadedede','de') from dual;

replace(列|值,被置換的字符,置換的字符) 置換指定字符

select replace('hello world!','o','*') from dual;

translate()轉換指定字符

select translate(

'I l6o9ve y8o7u!',

'abcdefghijklmnopqrstuvwxyz0123456789',

'abcdefghijklmnopqrstuvwxyz'

) from dual;

select replace('abcdededeadedede','de','') from dual;

或者

select translate('abcdededeadedede','abcde','abc') from dual;

instr()查找指定字符串在長字符串中所在的位置

--select instr('go,go,quickly!','go') from dual;

--select instr('go,go,quickly!','go',2) from dual;

--select instr('go,go,quickly!','go',-1) from dual;

--select instr('go,go,quickly!','go',-1,2) from dual;

注意:數據庫字符串下標從1開始計數

substr()

select substr('hello world!', 3) from dual;

select length(substr('hello world!',3,4)) from dual;

//從3號下標開始切割,保留4個字符

decode(),類似一個三目運算符

比較第一和第二個參數,如果相等取第三個參數,如果不相等取第四個參數

select decode('A','A','B','C') from dual;//B

select decode(1,1,2,3) from dual;//2

select decode(1,2,2,3) from dual;//3

case when 可以把它看成switch功能:

select name "部門名",

case region_id

     when 1 then '北美'

     when 2 then '南美'

     when 3 then '中東/非洲'

     when 4 then '亞洲'

     when 5 then '歐洲'

     else '未知區域'

end "區域名"

from s_dept;

練習:

--查出客戶表(s_customer)中phone列最后一個-線后面的部分;

select phone,substr(phone,instr(phone,'-',-1)+1) from s_customer;

--把圖片表(s_p_w_picpath)中文件名(filename)列中后綴給查出來;

select distinct substr(filename,instr(filename,'.',-1)+1) from s_p_w_picpath;

--模擬向銀行中只顯示姓名的第一個字符(奧巴馬變成奧**):

--常規拼接

select substr(first_name,1,1)||rpad('*',length(first_name)-1,'*')

from s_emp;

--replace

select replace(first_name,substr(first_name,2),

rpad('*',length(first_name)-1,'*')) from s_emp;

--translate

select translate(first_name,substr(first_name,2),

rpad('*',length(first_name),'*')) from s_emp;

--找出名字長度超過5的員工

select * from s_emp where length(first_name)>5;

--找出職稱是 stock clerk的員工

select * from s_emp where title=initcap('stock clerk');

--請把員工的工資分為3等,超過2000元的為高等在1500和2000之間的為中等

低于1500的為低等

select first_name 姓名,

case

when salary<1500 then '低等'

when salary between 1500 and 2000 then '中等'

when salary>2000 then '高等'

else '未分等級'

end 工資等級

from s_emp;

特別注意:當when后面出現要比較的列,則case后面就千萬不能再添加了!!!

--輸出每個訂單編號及支付方式，支付方式要么是現金，

要么是信用卡，否則就是未知。

select id,

decode(payment_type,'CASH','現金','CREDIT','信用卡','未知') 

from s_ord;

select id,

case payment_type

when 'CASH' then '現金'

when 'CREDIT' then '信用卡'

else '未知'

end

from s_ord;

3.日期函數

oracle 默認的日期格式為:dd-mm-yy

dd 表示2位數的日

mm 代表2位數的月,如:06

yy 代表2位數年份

HH24/HH 代表小時

mi 代表分鐘

ss 代表秒

fm 表示去掉前面的0,如:fm 06,以后值6

day 表示星期幾

日期函數:

sysdate 當前日期

months_between(date1,date2) 2個日期之間的月數

add_months(date1,n) 在date1的基礎上加上幾個月

next_day(date1,'星期幾') 在date1的基礎上下個星期幾是什么時候

last_day(date) date日期所在月的最后一天是什么時候

修改當前會話語言環境:

alter session set nls_language = 'american';//改為英文

alter session set nls_language = 'simplified chinese';//改為簡體中文

修改當前會話日期的格式:

alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss day';

例如:

--查詢當前日期下一個星期四是幾號?

select next_day(sysdate,'星期四') from dual;

--找出員工的工作月數:

select months_between(sysdate,start_date) from s_emp;

--查詢員工的工作天數:

select first_name,sysdate-start_date from s_emp;

--計算一年前,當前,一年后的時間:

select add_months(sysdate,-12),sysdate,add_months(sysdate,12) from dual;

--當前日期前六個月的最后一天;

select last_day(add_months(sysdate,-6)) from dual;

--顯示下個星期五是什么日期:

select next_day(sysdate,'星期五') from dual;

4.轉換函數

to_number(char), 把字符轉換成數字

to_date(char[,fmt]), 

把字符串轉換成日期,如果不傳fmt參數,則采用默認格式(dd-mm-yy)來解析

to_char 把數字或日期格式化為字符串

  1.to_char(number,fmt); 格式化數字

  2.to_char(date, fmt); 格式化日期

--把123字符串轉換成123數字

select to_number('123') from dual;

--把1986-04-13字符串按照模板yyyy-mm-dd轉換成日期

select to_date('1986-04-13','yyyy-mm-dd') from dual;

--把數字格式化為字符串的時候可以用的一些符號:

 9  代表任意的數據

 L  代表本地的貨幣符號

 $  代表美元

 0  代表0

 .   代表.

 ,   代表,

--格式化輸出員工的工資($1,500.00)

select to_char(salary,'$999,999.99') from s_emp;

練習:

--把員工的入職日期格式化為年/月/日

select to_char(start_date,'yyyy/mm/dd') from s_emp;

--找出5月份入職的員工

select * from s_emp where to_char(start_date,'fmmm')='5';

--找出當月入職的員工

select * from s_emp 

where to_char(sysdate,'fmmm')=to_char(start_date,'fmmm');

--查詢出員工的姓名,入職日期,并按日的升序排序

select first_name,start_date from s_emp 

order by to_number(to_char(start_date,'fmdd'));

--找出每個員工的名字和它的薪水(如:$2,500.00)

select first_name,to_char(salary,'$999,999.99') from s_emp;