Oracle體系介紹

Oracle默認(rèn)用戶

• SCOTT用戶是Oracle 數(shù)據(jù)庫的一個(gè)示范帳戶,，在數(shù)據(jù)庫安裝時(shí)創(chuàng)建（我們用的版本現(xiàn)在是HR用戶）

SQL基礎(chǔ)語法

SQL組成

• 數(shù)據(jù)定義語言（DDL 操作表的結(jié)構(gòu)）
  create（創(chuàng)建）,、alter（修改）、drop（刪除）
• 數(shù)據(jù)操縱語言(DML 操作表的數(shù)據(jù))
  insert（添加）,、delete（刪除）,、update（修改）、select（查詢）
  數(shù)據(jù)控制語句（DTL）
  授權(quán)的語句
  事務(wù)控制語句（TCL）

--修改數(shù)據(jù)
update student set password='888888'where id=1;
--事務(wù)提交
commit
--事務(wù)回滾
rollback;

數(shù)據(jù)定義語言（DDL）
作用：

	數(shù)據(jù)定義語言用于改變數(shù)據(jù)庫結(jié)構(gòu),，包括創(chuàng)建,、更改和刪除數(shù)據(jù)庫對象

操縱表結(jié)構(gòu)的sql語句：

CREATE TABLE －創(chuàng)建表
ALTER TABLE  －修改表
TRUNCATE TABLE －清空表（不存儲日志）
DROP TABLE  －刪除表
add constraint 主鍵名 primary key（id）
add constraint 唯一標(biāo)示符名 unique（username）

起名規(guī)則：（pk：primary key的簡稱）pk_表名_字段名

	--備份表結(jié)構(gòu)和數(shù)據(jù)
create table student_bak20210224 as select * from student;

--利用已有的表和數(shù)據(jù)創(chuàng)建新的表，只備份表結(jié)構(gòu)
create table student_bak20210224_2 as select * from student where 1=2;

數(shù)據(jù)操縱語言（DML）
作用：

	數(shù)據(jù)操縱語言用于檢索,、插入和修改數(shù)據(jù)

數(shù)據(jù)操縱語言命令包括：

SELECT  查詢
INSERT   插入
UPDATE  更新
DELETE   刪除

查詢數(shù)據(jù)：

--查詢表所有數(shù)據(jù)
select * from student;

--查詢表所有數(shù)據(jù),，并可以可視化修改數(shù)據(jù)
select * from student for update;
--給表去別名,多用于多表關(guān)聯(lián)了查詢，用來區(qū)分不同的表中相同的字段名
select s.id,s.name from student s;
--給列取別名
select s.id 編號,s.name 姓名 from student s,；

添加數(shù)據(jù)

--插入數(shù)據(jù)to_date('2001-01-01','yyyy-MM-dd')系統(tǒng)函數(shù),，把字符串類型轉(zhuǎn)換成date類型
-- sysdate系統(tǒng)函數(shù)，獲取當(dāng)前系統(tǒng)時(shí)間
insert into student(id,name,username,password,sex,age,birthday,create_time) 
values (1,'張三','zhangsan ','123456',1,19,to_date('2001-01-01','yyyy-MM-dd'),sysdate);

修改數(shù)據(jù)

-修改全部數(shù)據(jù)的名字為迪麗熱巴
update student set name='迪麗熱巴';
--將sex為2的數(shù)據(jù)改為0
update student set sex=0 where sex=2;

刪除數(shù)據(jù)

--刪除數(shù)據(jù)
delete from student where id=1;

Oracle數(shù)據(jù)類型

• 字符
  Oracle中l(wèi)ong類型是字符串類型

  char類型（固定長度1~2000字節(jié)）
  varchar類型跟varchar2()(可變長度1~4000字節(jié))
  long類型（可變長度最多為2g）
  

• 數(shù)值

    可以存儲整數(shù),、浮點(diǎn)數(shù)和實(shí)數(shù)
    最高精度為 38 位
    數(shù)值數(shù)據(jù)類型的聲明語法：
    NUMBER[（p[,s]）]
    P表示精度,，S表示小數(shù)點(diǎn)的位數(shù)
  

• 日期時(shí)間

    存儲日期和時(shí)間值,，包括年、月,、日,，小時(shí)、分鐘,、秒
    主要的日期時(shí)間類型有：
    DATE - 存儲日期和時(shí)間部分，精確到秒
    TIMESTAMP - 存儲日期,、時(shí)間和時(shí)區(qū)信息,，秒值精確到小數(shù)點(diǎn)后6位
  

• 偽列（ROWID,ROWNUM）

    Oracle 中偽列就像一個(gè)表列，但是它并沒有存儲在表中
    偽列可以從表中查詢,，但不能插入,、更新和刪除它們的值
    常用的偽列有ROWID和ROWNUM
  

ROWID

是表中行的存儲地址，該地址可以唯一地標(biāo)識數(shù)據(jù)庫中的一行,，可以使用 ROWID 偽列快速地定位表中的一行

ROWNUM

是查詢返回的結(jié)果集中行的序號,，可以使用它來限制查詢返回的行數(shù)?？捎糜诜猪摬樵?

--偽列rowid,rownum不是真實(shí)存在表中的列,，只能作用查詢
select s.*,rowid,rownum from student s;

SQL操作符

• 算數(shù)操作符（+，-,*,/）

    作用：
    算術(shù)操作符用于執(zhí)行數(shù)值計(jì)算
  

	--查詢5年后所有學(xué)生的年齡
select s.age+5 五年后的年齡 from student s;

• 比較操作符
  比較操作符有：

     =,、!=,、<、>,、<=,、>=、BETWEEN…AND,、IN,、NOT IN、LIKE 和 IS NULL,、IS NOT NULL等
  

--查詢年齡等于18歲的學(xué)生
select * from student where age=18;
--查詢年齡大于20歲的學(xué)生
select * from student where age>20;
--查詢年齡小于20歲的學(xué)生
select * from student where age<20;
--查詢年齡大于等于20歲的學(xué)生
select * from student where age>=20;
--查詢年齡小于等于20歲的學(xué)生
select * from student where age<=20;
--查詢年齡不等于18歲的學(xué)生
select * from student where age<>18;
select * from student where age!=18;
--查詢年齡在18到20之間的學(xué)生
select * from student where age between 18 and 20;--包括兩端的值
--查詢年齡是18或者59歲的學(xué)生
select * from student where age in(18,59);
--查詢所有姓張的學(xué)生,%占位符,，表示0個(gè)或多個(gè)字符
select * from student where name like '張%';
--查詢賬號中包含字符a的學(xué)生
select * from student where username like '%a%';
--查詢所有姓名為兩個(gè)字的姓張的學(xué)生,_站位符，表示一個(gè)字符
select * from student where name like '張_';
--查詢姓名第二個(gè)字為“麻”的學(xué)生
select * from student where name like '_麻%';
--查詢出生日期為空的學(xué)生
select * from student where birthday IS NULL;
--查詢出生日期不為空的學(xué)生
select * from student where birthday IS NOT NULL;

• 邏輯操作符（and,、or,、ont）
  作用：

    邏輯操作符用于組合多個(gè)比較運(yùn)算的結(jié)果以生成一個(gè)或真或假的結(jié)果
  

--查詢年齡在18到20之間的學(xué)生
select * from student where age>=18 and age<=20;--and連接2個(gè)條件，表示與,，同時(shí)成立返回true
--查詢年齡是18或者59歲的學(xué)生
select * from student where age=18 or age=59;--or連接兩個(gè)條件,，表示或，只要一個(gè)成立返回true
--查詢年齡不是18或者59歲的學(xué)生
select * from student where age not in（18,59）;--not連接一個(gè)條件,，表示非,，取反

• 集合操作符（union,、union all、intersect,、minus）
  作用：

    集合操作符將兩個(gè)查詢的結(jié)果組合成一個(gè)結(jié)果
  

--union的用法,，用來合并兩個(gè)查詢結(jié)果，如果有相同的的記錄,，則去重 
select * from student where sex=1
union
select * from student where age between 13 and 20;

--union all的用法,，用來合并兩個(gè)查詢結(jié)果 
select * from student where sex=1
union all
select * from student where age between 13 and 20;		

--intersect用法，兩個(gè)查詢結(jié)果中的交集
select * from student where sex=1
intersect
select * from student where age between 13 and 20;

--minus用法,，第一個(gè)查詢結(jié)果集中排出第二個(gè)查詢結(jié)果集中存在的記錄
select * from student where age between 13 and 20
minus
select * from student where sex=1;

• 連接操作符（||）
  作用：

    連接操作符用于將多個(gè)字符串或數(shù)據(jù)值合并成一個(gè)字符串
  

--連接操作符||,，拼接多個(gè)列構(gòu)成一個(gè)列，其他字符需要用單引號括起來
select '編號是：'||s.id||',，姓名是：'||s.name 學(xué)生信息 from student s;

操作符的優(yōu)先級
SQL 操作符的優(yōu)先級從高到低的順序是：

• 算術(shù)操作符 --------最高優(yōu)先級
• 連接操作符
• 比較操作符
• NOT 邏輯操作符
• AND 邏輯操作符
• OR 邏輯操作符 --------最低優(yōu)先級
  算連比nao

SQL函數(shù)

單行函數(shù)

• 單行函數(shù)對于從表中查詢的每一行只返回一個(gè)值

• 可以出現(xiàn)在 SELECT 子句中和 WHERE 子句中

• 分類：

    日期函數(shù)
    數(shù)字函數(shù)
    字符函數(shù)
    轉(zhuǎn)換函數(shù)
    其他函數(shù)
  

日期函數(shù)：

--日期函數(shù)
--獲取系統(tǒng)當(dāng)前時(shí)間
select sysdate from dual;--2021/2/24 18:24:44
select add_months(sysdate,5)from dual;--2021/7/24 18:24:52
--計(jì)算兩個(gè)時(shí)間日期之間相差的月份
select months_between(sysdate,to_date('2021-07-24','yyyy-MM-dd')) from dual;---5
--返回某個(gè)時(shí)間值的當(dāng)月最后一天
select last_day(sysdate) from dual;--2021/2/28 18:25:11
--返回當(dāng)前時(shí)間的年月日（日根據(jù)時(shí)間四舍五入）
select round(sysdate) from dual;--2021/2/25
--返回當(dāng)前時(shí)間后第一個(gè)星期六的時(shí)間
select next_day(sysdate,7) from dual;--2021/2/27 18:25:34
--返回當(dāng)前時(shí)間的年月日（是多少就多少）
select trunc（sysdate）from dual;--2021/2/24
--獲取當(dāng)前時(shí)間的年（月/日）份
select extract(year from sysdate) from dual;--2021

特殊日期函數(shù)

--特殊日期函數(shù)
--獲取當(dāng)前時(shí)間的后四位數(shù),Y的個(gè)數(shù)表示要獲取年份的最后幾個(gè)數(shù)字
select to_char(sysdate,'YYYY')from dual;--2021
--獲取當(dāng)前時(shí)間是第幾季度,，Q為季度的意思,，1-3為第一季度
select to_char(sysdate,'Q') from dual;--1
--獲取當(dāng)前時(shí)間的月份，MM代表月份
select to_char(sysdate,'MM')from dual;--02
--獲取當(dāng)前時(shí)間的月份，RM代表羅馬的月份
select to_char(sysdate,'RM')from dual;--II  
--獲取當(dāng)前時(shí)間為今年的第幾周,，WW當(dāng)年第幾周
select to_char(sysdate,'WW')from dual;--08
--獲取當(dāng)前時(shí)間為當(dāng)前月的第幾周,，Wb表示本月第幾周
select to_char(sysdate,'W')from dual;--4
--獲取當(dāng)前日期為本年的第幾天,，DDD為本年的第幾天第一天為001
select to_char(sysdate,'DDD')from dual;--055
--獲取當(dāng)前日期為本月第幾天,，DD為本月的第幾天
select to_char(sysdate,'DD')from dual;--24
--獲取當(dāng)前日期為本周第幾天，D為本周第幾天
select to_char(sysdate,'D')from dual;--4
--獲取當(dāng)前時(shí)間為星期幾,，DY為星期幾
select to_char(sysdate,'DY')from dual;--星期三
--獲取當(dāng)前時(shí)間小時(shí)數(shù)（hh12為12小時(shí)制,，hh24為24小時(shí)制）
select to_char（sysdate,'hh12'）from dual;--06
select to_char（sysdate,'hh24'）from dual;--18
--獲取當(dāng)前時(shí)間的分鐘數(shù)（由于Oracle不區(qū)分大小寫，MM是月mm是分所以為了區(qū)分Mi代表分）
select to_char(sysdate,'Mi') from dual;==28
--獲取當(dāng)前時(shí)間秒數(shù),，ss代表秒
select to_char（sysdate,'ss'）from dual;--29
--把日期轉(zhuǎn)換成字符串
select to_char(sysdate,'yyyy-MM-dd hh24:Mi:ss') from dual;--2021-02-24 18:28:40

字符函數(shù)

--字符函數(shù)
--將字符首字母大寫
select initcap('hello') from dual;--Hello
--將字母變?yōu)樾?select lower（'HELLO'）from dual;--hello
--將字母變?yōu)榇髮?select upper('hello')from dual;--HELLO
--從左到右刪除指定的字母,，如果指定字母不是第一個(gè)則不刪除
select ltrim('abcd','ab')from dual;--cd
--從右到左刪除指定的字母，如果指定字母不是第一個(gè)則不刪除
select rtrim('abcd','cd')from dual;--ab
--刪除兩頭空格
select trim(' abcd  ')from dual;--abcd
--翻譯（字母替換）
select translate('jack','jc','12') from dual;--1a2k
--替換
select replace('hello word','o','e')from dual;--helle werd
--獲取指定字符的位置
select instr('hello','o' ) from dual;--5
--從指定位置開始截取指定個(gè)數(shù)字符
select substr('hello',3,2)from dual;--ll
--將兩個(gè)字符進(jìn)行拼接
select concat('hello ','word')from dual;--hello word

其他字符函數(shù)

--其他字符函數(shù)
select chr(97) from dual;--a
--將左右邊的字符串填充一些特定的字符數(shù) 
select LPAD('hello',15,'+') from dual;--++++++++++hello
select RPAD('hello',15,'+') from dual;--hello++++++++++
--獲取字符串長度
select length('hello')from dual;--5

數(shù)字函數(shù)

--數(shù)字函數(shù)
--求一個(gè)數(shù)字的絕對值
select abs（-418）from dual;--418
--向上取整
select ceil(3.5)from dual;--4
--向下取整
select floor(3.98)from dual;--3
--Power(m,n)  計(jì)算m的n次方
select power(2,3)from dual;--8
--求余（取模）
select mod(10,3)from dual;--1
--Round(m,n) 四舍五入,，保留n位小數(shù)(也可以對日期進(jìn)行四舍五入)
select round（3.51,1）from dual;--3.5
--Trunc(m,n)直接截取,，保留n位小數(shù)（也可以對日期使用）
select trunc（3.2,0）from dual;--3
--開方根
select sqrt（100）from dual;--10

轉(zhuǎn)換函數(shù)
轉(zhuǎn)換函數(shù)將值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型
常用的轉(zhuǎn)換函數(shù)有：

• TO_CHAR (9-代表整數(shù),0-代表小數(shù),C-國際貨幣符號, L(本地貨幣符號）
• TO_DATE
• TO_NUMBER

--轉(zhuǎn)換函數(shù)
--將字符串轉(zhuǎn)化成日期
select to_date('2021-02-24','yyyy-MM-dd')from dual;--2021/2/24
--將日期轉(zhuǎn)化成字符串
select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss')from dual;--2021-02-24 19:02:57
--將字符串轉(zhuǎn)換成數(shù)字
select to_number('100')from dual;--100
--將數(shù)字轉(zhuǎn)換成字符串
select to_char(19.33215421654)from dual;--19.33215421654

其他函數(shù)

以下是幾個(gè)用來轉(zhuǎn)換空值的函數(shù)：

• NVL(expr1,expr2)->expr1為NULL返expr2；非NULL返expr1;
• NVL2(expr1, expr2, expr3) ->expr1不為NULL返expr2,；為NULL返expr3
• NULLIF (expr1, expr2) ->相等返NULL(空),不等返回expr1; expr1和 expr2類型必須一致

--其他函數(shù)
--如果第一個(gè)數(shù)的值不是空的直接輸出,，否則輸出第二個(gè)參數(shù)
select s.birthday ,NVL(to_char(s.birthday,'yyyy-MM-dd'),0) FROM student s;
----如果第一個(gè)數(shù)的值不是空的輸出第二個(gè)參數(shù)，否則輸出第三個(gè)參數(shù)
SELECT s.birthday, NVL2(to_char(s.birthday,'yyyy-MM-dd'),1,0)   FROM student s;
--兩個(gè)參數(shù)相等輸出空
SELECT s.birthday, NULLIF(0,0)   FROM student s;

分組函數(shù)（avg,，min,，max，sum,，count）

--計(jì)算學(xué)生的最小年齡,，最大年齡，平均年齡，年齡總和,，總?cè)藬?shù)
select min（age）最小年齡,，max(age) 最大年齡,avg(age) 平均年齡,sum(age) 年齡總和,count(*) 總?cè)藬?shù) from student ;

GROUP BY子句

用于將信息劃分為更小的組
每一組行返回針對該組的單個(gè)結(jié)果

HAVING子句

用于指定 GROUP BY 子句檢索行的條件(只能是分組的字段或者聚集函數(shù))

查詢的字段只能是分組的字段或者聚集函數(shù)

--統(tǒng)計(jì)所有男女學(xué)生數(shù)量,只有分過組的字段才能查
select sex,count(sex) from student group by sex;

ORDER BY子句

ASC 按升序排序
DESC按降序排序

asc升序（默認(rèn)，可省略）,，desc降序,，多個(gè)字段排序，逗號隔開

--按照年齡從小到大排序 ,asc升序（默認(rèn),，可省略）
select * from student order by age asc;
--按照年齡從大到小排序,，如果相同再根據(jù)編號從大到小排序。desc降序
select * from student order by age desc,id desc;--多個(gè)字段排序,，逗號隔開

寫的先后順序where ,、group up、having,、order by

--查詢出生日期不為空的且不重復(fù)的年齡，按照降序排序
select age, count(age)
  from student
 where birthday is not null
 group by age
having count(age) = 1
 order by age desc;
 --子查詢,先執(zhí)行子查詢
select * from student where age in(select age
  from student
 where birthday is not null
 group by age
having count(age) = 1) order by age desc;