大綱

• SQL概念與分類
• 數(shù)據(jù)定義語言（DDL）
• DML（Data Manipulation Language，數(shù)據(jù)操作語言）
• 數(shù)據(jù)查詢語言(DQL:Data Query Language)
• 函數(shù)
• 約束與索引

1、概念:

結(jié)構(gòu)化查詢語言(Structured Query Language)簡稱SQL在辆，是一種特殊目的的編程語言，是一種數(shù)據(jù)庫查詢和程序設(shè)計語言椒振，用于存取數(shù)據(jù)以及查詢晰骑、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)彼绷；

2丐黄、 SQL語言基本分類:

2.1 數(shù)據(jù)定義語言（DDL）

包括：create table斋配、alter table、drop table等；

2.2 DML（Data Manipulation Language许起，數(shù)據(jù)操作語言）

用于檢索或者修改數(shù)據(jù)十偶。
DML包括：
INSERT：用于增加數(shù)據(jù)到數(shù)據(jù)庫菩鲜；
UPDATE：用于從數(shù)據(jù)庫中修改現(xiàn)存的數(shù)據(jù)
DELETE：用于從數(shù)據(jù)庫中刪除數(shù)據(jù)园细。

2.3 數(shù)據(jù)查詢語言(DQL:Data Query Language)

其語句，也稱為"數(shù)據(jù)檢索語句"接校，用以從表中獲得數(shù)據(jù)猛频，確定數(shù)據(jù)怎樣在應(yīng)用程序給出。保留字SELECT是DQL(也是所有SQL)用得最多的動詞蛛勉，其他DQL常用的保留字有WHERE鹿寻，ORDER BY，GROUP BY和HAVING诽凌。這些DQL保留字常與其他類型的SQL語句一起使用毡熏。

2.4 DCL（Data Control Language，數(shù)據(jù)控制語言）

用于定義數(shù)據(jù)庫用戶的權(quán)限侣诵。
DCL包括：
ALTER PASSWORD
GRANT
CREATE SYNONYM

2.5 事務(wù)處理語言(TPL)

它的語句能確保被DML語句影響的表的所有行及時得以更新痢法。TPL語句包括BEGIN TRANSACTION，COMMIT和ROLLBACK

2.6 指針控制語言(CCL)

它的語句杜顺，像DECLARE CURSOR财搁，F(xiàn)ETCH INTO和UPDATE WHERE CURRENT用于對一個或多個表單獨行的操作

3、DDL基礎(chǔ)

3.1Create

• Create table
• Create sequence
• Create view
• Create index

Create table （建表）
語法：
CREATE TABLE table_name(
    column_name1 data_type,
    column_name2 data_type,.......
);

• 表名和列的命名規(guī)則

1. 必須以字母開頭
2. 長度不能超過30個字符
3. 不能使用oracle的保留字
4. 只能使用如下字符 A-Z躬络，a-z尖奔，0-9，$,#等

• Oracle中字段的數(shù)據(jù)類型

1. 字符型 char 最大2000個字節(jié) 定長
   char(10) '張三'后面添加6個空格把10個字節(jié)填滿
   varchar2 范圍 最大4000個字節(jié) 變長
2. char 查詢的速度極快浪費空間穷当，查詢比較多的數(shù)據(jù)用提茁。
   varchar2 節(jié)省空間 （oralce中基本都用這個）
   數(shù)字 number 范圍- 10的38次方到10的38次方，可以是整數(shù)也可以是小數(shù)
   number(4)最大表示四位整數(shù)-9999到9999
   number(5,2)表示5為有效數(shù)字 2為小數(shù)的一個小數(shù)-999.99到999.99
3. 日期 date 包含年月日和時分秒 7個字節(jié)
4. 圖片 blob 二進(jìn)制大對象 圖像/聲音 4G

Create table例子：創(chuàng)建一個員工表
create table liuyl_EMP
(empno    NUMBER(4),--員工號
  ename    VARCHAR2(10),--員工姓名
  job      VARCHAR2(9),--職位
  mgr      NUMBER(4),--上級領(lǐng)導(dǎo)
  hiredate DATE,--入職日期
  sal      NUMBER(7,2),--工資
  comm     NUMBER(7,2),--獎金
  deptno   NUMBER(2)—部門編號);

3.2 Drop

• Drop table table_name;
• Drop SEQUENCE seq_name;
• Drop index index_name;
• Drop view view_name;

3.3 Alter

添加字段(學(xué)生所在班級classid)（添加add）
alter table student add(classid number(2));
修改字段的長度（修改modify）
alter table student modify(xm varchar2(12));
刪除一個字段
alter table student drop column sal;
字段如何改名字
--先刪除
a)alter table student drop column sal;
--再添加
b)alter table student add (salary number(7,2));
表名修改
rename student to stu;

4馁菜、DML基礎(chǔ)

4.1 insert(插入數(shù)據(jù))

• 所有字段都插入

insert into liuyl_EMP 
values
  (‘1’,
   '小紅',
   '會計',
   5,
   sysdate,
   2000,
   500,
   20);

• 部分字段插入

insert into dengxianglei_EMP
  (empno, ename, job, mgr, sal, comm, deptno)
values
  ('2',
   '小明',
   '會計',
     null,--空值
   2000,
     500,
      20);

4.2 UPDATE

• 將員工號為1的員工名字改為小胡

UPDATE liuyl_EMP T
SET T.ENAME=‘小胡’,
T.sal=‘5000’
WHERE T.EMPNO=1;

4.3 delete

• 刪除員工號為2的員工信息刪除

delete from liuyl_EMP T
where t.empno=2;

delete from liuyl_EMP;刪除表中所有記錄茴扁，表結(jié)構(gòu)還在，寫日志火邓，可以恢復(fù)丹弱，速度慢
drop table liuyl_EMP;刪除表結(jié)構(gòu)和數(shù)據(jù)
truncate table liuyl_EMP;刪除表中的所有記錄，表結(jié)構(gòu)還在铲咨，不寫日志躲胳，無法找回刪除的記錄，速度快

5纤勒、DQL(數(shù)據(jù)查詢語言坯苹，sql最主要的部分)

Select查詢語法

• SELECT “欄位名” FROM “表格名” ;
• Distinct（去重）
• Where（條件）
• And or
• In\not in
• Between and

select * from dengxianglei_EMP t
where t.empno between 4 and 7;

• Like
• Order by
• Count
• Union \union all\Intersect\Minus

select * from dengxianglei_EMP t  where t.empno = 5
intersect
select * from dengxianglei_EMP t  where t.empno = 6;

select * from dengxianglei_EMP t where t.empno = 5
union
select * from dengxianglei_EMP t where t.empno = 5;

select * from dengxianglei_EMP t where t.empno = 5
union all
select * from dengxianglei_EMP t where t.empno = 5;

select * from dengxianglei_EMP t where t.empno = 6
minus
select * from dengxianglei_EMP t where t.empno = 5;

• Group by、having

select t.sal from dengxianglei_EMP t  group by t.sal having t.sal=2000;

• Exists \ not exists

select *
  from dengxianglei_EMP o
 where exists (select 'x' from dengxianglei_dept t where t.deptno = o.deptno);

• Decode

Decode (a,b,c,d);\decode(a,b,c,d,e)

• Case

select t.ename,case t.ename
         when '小紅' then
          sal * 1
         when '小明' then
          sal * 2
         else sal
       end new_sal from liuyl_emp t;

連接查詢

• 表連接(多表查詢)
• 笛卡爾積(a表記錄條數(shù)*b表記錄條數(shù))

select * from dengxianglei_emp,dengxianglei_dept;

規(guī)定：多表查詢的條件是 至少不能少于 表的個數(shù)-1 才能排除笛卡爾集 （如果有N張表聯(lián)合查詢摇天，必須得有N-1個條件粹湃，才能避免笛卡爾集合）

SELECT e.ename, e.sal, d.dname FROM dengxianglei_emp e, dengxianglei_dept d WHERE e.deptno = d.deptno; 

• 自連接（內(nèi)聯(lián)）
• 左連接恐仑、右連接

select 屬性列表 from 表1 lift/right outer join 表2
on 表1.屬性=表2.屬性;
左連接查詢(可以查詢出表1所有的記錄，表2中匹配的記錄）
右連接查詢(可以查詢出表2所有的記錄为鳄，表1中匹配的記錄)
復(fù)合條件連接查詢查詢
select 屬性列表 from 表1 lift/right outer join 表2
on 表1.屬性=表2.屬性
where 屬性名=' ';(on寫連接條件when中寫其他條件)

習(xí)慣用法(oracle)
Select * from A ,B
WHERE A.NO=B.NO(+)(右鏈接裳仆，可以查詢出A所有的記錄，B中匹配的記錄)
Select * from A ,B
WHERE A.NO(+)=B.NO(左鏈接孤钦，可以查詢出B所有的記錄歧斟，A中匹配的記錄)

• 子查詢

子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢

select * from liuyl_emp de
where de.deptno in (select dd.deptno from liuyl_dept dd
where dd.dname='業(yè)務(wù)部');
select *
  from liuyl_EMP o
 where exists (select 'x' from liuyl_dept t where t.deptno = o.deptno);

• 多列

select * from dengxianglei_emp de
 where (de.empno, de.ename) =
       (select d.empno, d.ename from dengxianglei_emp d where d.empno = 5);

• 分頁查詢（重點）

分析函數(shù)分頁（這個用得多效率稍差）：分析函數(shù)可分為四類：等級函數(shù)(ranking)偏形，聚合函數(shù)(aggregate)静袖，行比較函數(shù)(row comparison)，統(tǒng)計函數(shù)(statistical)俊扭。

select a.*
  from (select de.*, row_number() over(order by de.empno desc) rn
          from liuyl_emp de) a
 where a.rn > 1 and a.rn<4;

rownum分頁（效率好）

 select * from (select de.*,rownum rn from liuyl_emp de
order by de.empno) a
where a.rn<6 and a.rn>2;

• with子集

With alias_name as (select1), --as和select中的括號都不能省略
alias_name2 as (select2),--后面的沒有with队橙，逗號分割，同一個主查詢同級別地方萨惑，with子
查詢只能定義一次

with a as
(select deptno from dept where dname like '%A%')
select * from emp where deptno in (select * from a);
with a as
(select deptno from dept where dname like '%A%'),--a結(jié)果集
a2 as(select * from a where deptno>20)--a1結(jié)果集直接從a中篩選
select * from emp where deptno in (select * from a2);

6捐康、函數(shù)

7、約束

• NOT NULL
• UNIQUE
• CHECK

CHECK 限制是保證一個欄位中的所有資料都是符合某些條件咒钟。
CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));
"SID" 攔只能包含大于 0 的整數(shù)吹由。

• 主鍵 (Primary Key)

簡單來說非空且唯一
主鍵可以在建置新表格時設(shè)定 (運用 CREATE TABLE 語句)，或是以改變現(xiàn)有的表格架構(gòu)方式設(shè)定 (運用 ALTER TABLE)朱嘴。

CREATE TABLE Customer 
(SID number PRIMARY KEY, 
Last_Name varchar(30), 
First_Name varchar(30)); 

alter table liuyl_emp add primary key (empno);

• 外鍵 (Foreign Key)

舉例來說倾鲫，假設(shè)我們有兩個表格：一個 emp（子表） 表格，里面記錄了所有員工的資料萍嬉；另一個 dept（父表） 表格乌昔，里面記錄了所有部門的資料。在這里的一個限制壤追，就是所有的員工的部門號磕道，都一定是要跟在 dept表格中存在。在這里行冰，我們就會在 emp表格中設(shè)定一個外來鍵溺蕉，而這個外來鍵是指向 dept表格中的主鍵。

CREATE TABLE ORDERS 
(Order_ID integer primary key, 
Order_Date date, 
Customer_SID integer references CUSTOMER(SID), 
Amount double); 

8悼做、索引

• 普通索引
  CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)
• 唯一索引
  CREATE unique INDEX “INDEX_NAME” ON “TABLE_NAME” (COLUMN_NAME)--唯一索引
• 聯(lián)合索引
  CREATE INDEX “INDEX_NAME” ON “TABLE_NAME” (COLUMN_NAME1, COLUMN_NAME2…..)--聯(lián)合索引

其實設(shè)置主鍵是系統(tǒng)默認(rèn)給該字段添加唯一索引
create index index_emp on liuyl_emp(ename);

• 刪除索引
  Drop index index_name;

9疯特、視圖

視圖是基于一個表或多個表或視圖的邏輯表，本身不包含數(shù)據(jù)肛走，通過它可以對表里面的數(shù)據(jù)進(jìn)行查詢和修改漓雅。視圖基于的表稱為基表。視圖是存儲在數(shù)據(jù)字典里的一條select語句。 通過創(chuàng)建視圖可以提取數(shù)據(jù)的邏輯上的集合或組合邻吞。

create or replace view liuyl_emp_v as
select * from liuyl_emp;

10组题、開窗函數(shù)

create table EMP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)
alter table EMP
  add constraint PK_EMP primary key (EMPNO);
 
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);

• 1、 rank()/dense_rank() over(partition by ...order by ...)

現(xiàn)在客戶有這樣一個需求抱冷，查詢每個部門工資最高的雇員的信息

--一般寫法
select * from (select ename, job, hiredate, e.sal, e.deptno
          from emp e,
               (select deptno, max(sal) sal from emp group by deptno) t
         where e.deptno = t.deptno
           and e.sal = t.sal)
 order by deptno;
        
select * from (select ename 姓名, job 職業(yè), hiredate 入職日期, e.sal 工資, e.deptno 部門
          from emp e,
               (select deptno, max(sal) sal from emp group by deptno) t
         where e.deptno = t.deptno
           and e.sal = t.sal)
 order by 部門;

image.png

開窗寫法
select empno, ename, job, hiredate, sal, deptno
  from (select empno, ename, job, hiredate, sal, deptno, rank() over(partition by deptno order by sal desc) r from emp)
 where r = 1;
 
select empno, ename, job, hiredate, sal, deptno
  from (select empno, ename, job, hiredate, sal, deptno, dense_rank() over(partition by deptno order by sal desc) r from emp)
 where r = 1

為什么會得出跟上面的語句一樣的結(jié)果呢崔列？這里補(bǔ)充講解一下rank()/dense_rank() over(partition by e.deptno order by e.sal desc)語法。
over: 在什么條件之上徘层。
partition by e.deptno: 按部門編號劃分（分區(qū)）峻呕。
order by e.sal desc: 按工資從高到低排序（使用rank()/dense_rank() 時，必須要帶order by否則非法）
rank()/dense_rank(): 分級
整個語句的意思就是：在按部門劃分的基礎(chǔ)上趣效，按工資從高到低對雇員進(jìn)行分級，“級別”由從小到大的數(shù)字表示（最小值一定為1）猪贪。

那么rank()和dense_rank()有什么區(qū)別呢跷敬？
rank(): 跳躍排序，如果有兩個第一級時热押，接下來就是第三級西傀。
dense_rank(): 連續(xù)排序，如果有兩個第一級時桶癣，接下來仍然是第二級拥褂。

2、min()/max() over(partition by ...)

現(xiàn)在我們已經(jīng)查詢得到了部門最高/最低工資牙寞，客戶需求又來了饺鹃，查詢雇員信息的同時算出雇員工資與部門最高/最低工資的差額。這個還是比較簡單间雀，在第一節(jié)的groupby語句的基礎(chǔ)上進(jìn)行修改如下：

-- 查詢每位雇員信息的同時算出雇員工資與所屬部門最高/最低員工工資的差額

select ename 姓名, job 職業(yè), hiredate 入職日期, e.deptno 部門, e.sal 工資, e.sal-me.min_sal 最低差額, me.max_sal-e.sal 最高差額
  from emp e, (select deptno, min(sal) min_sal, max(sal) max_sal from emp group by deptno) me  
 where e.deptno = me.deptno order by e.deptno, e.sal;  

image.png

上面我們用到了min()和max()悔详，前者求最小值，后者求最大值惹挟。如果這兩個方法配合over(partition by ...)使用會是什么效果呢茄螃？

select ename 姓名, job 職業(yè), hiredate 入職日期, deptno 部門, 
     min(sal) over(partition by deptno) 部門最低工資, 
     max(sal) over(partition by deptno) 部門最高工資 
  from emp order by deptno, sal;  
 
select ename 姓名, job 職業(yè), hiredate 入職日期, deptno 部門, 
     nvl(sal - min(sal) over(partition by deptno), 0) 部門最低工資差額, 
     nvl(max(sal) over(partition by deptno) - sal, 0) 部門最高工資差額
  from emp order by deptno, sal;

3、lead()/lag() over(partition by ... order by ...)

在和最高/最低工資比較完之后還覺得不過癮连锯，這次就提出了一個比較變態(tài)的需求归苍，計算個人工資與比自己高一位/低一位工資的差額

select ename 姓名, job 職業(yè), sal 工資, deptno 部門,  
   lead(sal, 1, 0) over(partition by deptno order by sal)  比自己工資高的部門前一個,  
   lag(sal, 1, 0) over(partition by deptno order by sal)  比自己工資低的部門后一個,  
   nvl(lead(sal) over(partition by deptno order by sal) - sal, 0)  比自己工資高的部門前一個差額,   
   nvl(sal - lag(sal) over(partition by deptno order by sal), 0)   比自己工資高的部門后一個差額
 from emp;   

image.png

4、其他

select ename 姓名, job 職業(yè), sal 工資, deptno 部門, 
    first_value(sal) over(partition by deptno) first_sal, 
    last_value(sal) over(partition by deptno) last_sal, 
    sum(sal) over(partition by deptno) 部門總工資, 
    avg(sal) over(partition by deptno) 部門平均工資, 
    count(1) over(partition by deptno) 部門總數(shù), 
    row_number() over(partition by deptno order by sal) 序號 
 from emp;  

image.png