Context、Select(day01)

Oracle
sql: 4天
plsql: 2天
proc: 2天

1. 資料庫介紹
   1.1 資料庫簡介
   1.1.1 數據管理技術的發展
   人工管理階段：20世紀50年代中期之前

   文件管理階段：20世紀的50年代末期到60年代中期
   缺點：數據冗餘
   數據的不准確
   數據之間的聯繫弱
   資料庫管理階段：
   資料庫技術誕生的三大標誌性事件：
   1968年，IBM公司---> IMS
   1969年，DBTG發佈了一系列的報告---> 標準和規範
   1970年，IBM的研究員E.F.Codd 發表了一系列論文
   提出了關係模型
   特點：採用複雜的結構化的數據模型
   最低的冗餘度
   數據完整性
   資料庫系統為用戶提供了方便的介面
   控制功能：
   併發控制
   資料庫的恢復
   安全性
   系統更加靈活
   信息處理方式不在以程式為中心，而是以數據為中心

   1.1.2 和資料庫相關的幾個術語
   數據(data):資料庫存儲的基本對象，包括文字、圖形、聲音、視頻等
   資料庫(database):長期存儲在電腦內、有組織、可共用的大量數據的集合
   資料庫管理系統(DBMS):是位於操作系統和用戶之間的一層應用程式，科學的組織、存儲數據，高效的獲取和維護數據
   資料庫系統(DBS): 由資料庫、資料庫管理系統、應用程式和資料庫管理員(或用戶) 組成的系統。
   資料庫管理員: DBA

   1.1.3 關係資料庫
   用二維表保存數據的資料庫
   表頭 欄位
   行
   列
   欄位值

1.2 主流的關係型資料庫產品
商業型
Oracle Oracle(甲骨文) 10g 11g 12c
DB2 IBM
sql server 微軟
sybase
開源
mysql Oracle

1.3 sql語言
SQL(Structured Query Language): 結構化查詢語言
SQL分為：
數據查詢語言(DQL): select
用來查詢數據中的數據 使用最廣泛、語法最靈活
數據操作語言(DML): insert delete update
用來改變資料庫中的數據
數據定義語句(DDL): create drop alter
用來創建、刪除、修改資料庫對象
事務控制語句(TCL): commit rollback savepoint
用來保證數據的一致性
數據控制語句(DCL): grant、revoke、create user
用來執行許可權的授予和回收、創建用戶等

1.4 遠程登錄伺服器
1.4.1 開發工具
sql*plus
sqlplus:oracle提供，和資料庫進行交互的工具
命令提示符下的工具
oracle sqldeveloper: 可視化工具

  1.4.2 遠程登錄
    第一步：遠程登錄伺服器
        telnet  ip
        用戶名：
        密碼：

    第二步：使用sqlplus工具
      sqlplus
      輸入用戶名:
      輸入密碼:

    現場班：
    telnet 172.60.5.80     或172.60.5.81
    用戶：oracle
    密碼:   oracle

    sqlplus
    輸入用戶名: openlab
    輸入密碼: open123

    SQL>
    sqlplus命令：可以不以分號結尾
    sql語句: 必須以分號結尾(學習的內容)

1.5 描述表結構
sqlplus命令：desc
desc 表名[;]
desc s_emp;

Name Null? Type
------------------------------- --------------- -------------
ID 員工編號 NOT NULL NUMBER(7)
LAST_NAME 姓 NOT NULL VARCHAR2(25)
FIRST_NAME 名 VARCHAR2(25)
USERID VARCHAR2(8)
START_DATE 入職日期 DATE
COMMENTS VARCHAR2(255)
MANAGER_ID 領導編號 NUMBER(7)
TITLE 職位 VARCHAR2(25)
DEPT_ID 部門編號 NUMBER(7)
SALARY 工資 NUMBER(11,2)
COMMISSION_PCT 提成 NUMBER(4,2)

Name:表中的欄位名
Null? 該欄位是否允許為空
NOT NULL:不允許為空
Type: 欄位的數據類型
number(p,s) 數字類型
p: 有效數字的位數(從第一個非零數字開始)
1<=p<=38 預設38
s: 精度
-84<=s<=127
number ---> number(38,0)
number(p) ----> number(p,0)
number(p,s)
varchar2(n) 變長字元串 n不能省略 1~4000bytes
char(n) 定長字元串 n預設為1 1~2000bytes

        date    日期

1. select語句
   2.1 幾個概念
   選擇：選中部分行，全部列
   投影：選中全部行，部分列
   表連接：需要的數據來自於多張表
   2.2 select語句的子句
   基本的查詢語句: select..from
   where子句
   order by子句
   單行函數
   表連接
   組函數和分組
   子查詢

3.select..from語句
select 欄位列表 from 表名;
3.1 列出表中一個欄位
select 欄位名 from 表名;
-- 列出所有員工的工資
select salary from s_emp;

3.2 列出表中的多個欄位
select 欄位名,欄位名,.... from 表名;
-- 列出員工的編號、名字、職位、工資
select id,first_name,title,salary from s_emp;

3.3 列出表中全部欄位
  --
  select id,last_name,first_name,userid,..... from s_emp;
  -- 使用 * 代替所有欄位
  select * from s_emp;

3.4 算數運算   一般指的是數字類型
  +  -  *   /  
  -- 列出員工的信息，包括編號、名字、工資、年收入
  12*salary + 1000
  select id,first_name,salary,12*salary+1000 from s_emp;

3.5 欄位或表達式 命名 別名
  3.5.1 語法
   欄位或表達式 [as] 別名
   select id,first_name as name,salary,
       12*salary+1000 yearsal  from s_emp;
  3.5.2 使用 "" 
    -- 屏蔽特殊字元或關鍵字等
    select id,first_name as name,salary,
       12*salary+1000 "year sal"  from s_emp;
    -- 大小寫敏感
    select id,first_name as name,salary,
       12*salary+1000 "YearSal"  from s_emp;

3.6 sql中的字元串
3.6.1 字元串的表示方式
單引號：''
'a' 'Hello world'
3.6.2 字元串的拼接
||
-- 把員工的first_name和last_name拼接起來
select id,first_name||last_name as name from s_emp;
-- 把員工的first_name和last_name之間拼接一個 .
select id,first_name||'.'||last_name as name
from s_emp;
-- 把員工的first_name和last_name之間拼接一個 '
使用轉義字元：'
select id,first_name||''''||last_name as name
from s_emp;

3.7 NULL值的處理
-- 計算員工的年收入，考慮提成
12salary + 12salarycommission_pct/100
12salary(1+commission_pct/100)
-- 下麵語句的結果集是錯誤的
select id,first_name,title,
12salary*(1+commission_pct/100) as yearsal
from s_emp;
NULL值參與運算的表達式的結果為空

  使用函數 nvl 處理NULL值
  nvl(par1,par2) : 當par1不為空，返回par1
                            放par1為空，返回par2
  nvl(12*salary*(1+commission_pct/100),12*salary)
  12*salary*(1+nvl(commission_pct,0)/100)

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

3.8 排重 distinct
-- 列出員工的職位
select distinct title from s_emp;
-- 多列排重
select distinct title,dept_id from s_emp;

1. where子句
   select ...
   from ..
   where 條件;
   4.1 作用
   根據條件對錶中的數據進行篩選，挑選出符合條件的行
   4.2 數字類型的條件
   -- 列出工資大於1400的員工的信息
   select id,first_name,salary from s_emp
   where salary>1400;
   4.3 字元串類型的條件
   -- 列出名字為'Ben'的員工的信息
   select id,first_name,salary from s_emp
   where first_name = 'Ben'; -- 有一行結果
   select id,first_name,salary from s_emp
   where first_name = 'ben'; -- 沒有結果
   -- sql中沒有 ==
   -- sql中不區分大小寫，但是字元串的值是區分的
   4.4 比較運算符
   > < >= <= = !=(<> ^=)
   4.5 sql提供的運算符
   4.5.1 表示一個閉區間 [a,b]
   between a and b
   -- 列出工資在[1100,1550] 之間的員工的信息
   select id,first_name,salary from s_emp
   where salary between 1100 and 1550;

   4.5.2 表示一個列表
   in(值1,值2,...)
   address in('北京','上海','廣州')
   -- 列出部門編號為 31，42，50的員工的信息
   select id,first_name,dept_id from s_emp
   where dept_id in(31,42,50);

   4.5.3 模糊查詢
   like '包含通配符的字元串'

     通配符：
     %: 任意長度的任意字元
     _:  一位任意字元
   
     StuName  like '李_%'
   
     -- 列出first_name首字母為'M'的員工的信息
     select id,first_name from s_emp
       where first_name like 'M%';
     -- 列出first_name第二個字母為'a'的員工的信息
     select id,first_name from s_emp
       where first_name like '_a%';
   
     user_tables: 數字字典 保存當前用戶的所有表的信息
     desc user_tables;
     -- 列出user_tables中以'S_'開頭的表的信息
     select table_name from user_tables
            where table_name like 'S_%';
     -- 使用轉義字元  escape
     select table_name from user_tables
            where table_name like 'S\_%' escape '\';

   4.5.4 空值的判斷
   is null

   -- 列出manager_id為空的員工的信息
   select id,first_name,title from s_emp
        where manager_id = null;
   select id,first_name,title from s_emp
        where manager_id != null;
      -- 使用=或!=判斷null值，結果永遠為假
   
    select id,first_name,title from s_emp
        where manager_id is null;

4.6 sql中的邏輯運算符
and or not
-- 使用and改寫 between and 案例
列出工資在[1100,1550]之間的員工的信息
select id,first_name,salary from s_emp
where salary between 1100 and 1550;
select id,first_name,salary from s_emp
where salary>=1100 and salary<=1550;

  -- 使用or改寫 in 案例
      列出編號為31，42，50的部門的員工的信息
      select id,first_name,dept_id from s_emp
             where dept_id in(31,42,50);
      select id,first_name,dept_id from s_emp
             where dept_id=31 or dept_id=42 or dept_id=50;


   對立面：
   >                            <=
   <                            >=
   =                            !=(<> ^=)
   
   between and          not between and
   in           not in(註意NULL值)
   like         not like
   is null          is not null

   -- 列出有提成的員工的信息
   select id,first_name,commission_pct from s_emp
       where commission_pct is not null;

1. order by子句
   5.1 總是出現在一條select語句的最後
   select 欄位列表
   from 表名
   where 子句
   ......
   order by子句;
   5.2 語法
   order by 排序標準 排序方式
   排序方式：
   asc 升序 (自然順序、字典順序) 預設排序方式
   desc 降序

5.3 按照工資降序排序，列出dept_id為31，32，33的員工的信息
select id,first_name,salary,dept_id from s_emp
where dept_id in(31,32,33)
order by salary desc;

5.4 多列排序
  select id,first_name,salary from s_emp
      order by salary;

    order by 排序標準1 排序方式,排序標準2 排序方式
  select id,first_name,salary from s_emp
      order by salary,id desc;
    -- 多列排序時，每一個排序標準的排序方式是自己定義的

 5.5 排序時，預設按照最大值處理
   -- 根據manager_id排序，列出員工的信息
    select id,first_name,manager_id from s_emp
        order by manager_id desc;

-----------------------------------------------------------------總結
1.資料庫介紹
sql語言
sqlplus的命令：desc
數據類型
2.select語句
3.select...from
4.where子句
比較運算符：> < >= <= = !=(<> ^=)
sql提供的運算符
between and
in
like
is null
sql中的邏輯運算符
and or not

1. order by子句

練習：

1. 查看s_dept和s_region表的表結構
2. 查詢s_dept和s_region表中的數據
3. 計算員工的年收入，列出年收入大於15000的員工的信息，
   並對年收入進行降序排序。