L3 SQL 入門

3-1 SQL 概述

• 資料操作語言(DML):提供資料修改刪除等操作指令
• 完整性(intergrity):SQL DDL 包含設令完整性限制等指令，確保資料的完整及可靠性

• 檢視表定義(view drfinition):包含定義檢視表的命令
• 交易控制(transaction control):包含交易流程的命令
• 嵌入式 SQL 與動態 SQL(embedded SQL and dynamic SQL):定義 SQL 如何嵌入通用的程式設計語言
• 授權(authorization):包含設定權限等指令

3-2 SQL 定義

3.2.1 基本類型

• char(n):存放固定長度 n 的字串資料

• varchar(n):存放字串大小為 n 的字串

• int:存放整數，最大長度為 4 位元

• smallint:存放整數，最大整數為 2 位元

• numeric(p,d):存放小數的資料，p 表示精準度，d 表示 d 位數的小數

    EX:numeric(3,1)為三位數，包含2位整數與1位小數
  

• real,double,precision:存近似浮點數，精準值依機器定

• float(n):存放浮點數值，精準度為 n

• null:表示不存在或不確定資料

3.2.2 基本架構

• 建立資料表(creat table):

    create table instructor(
        ID      char(5),
        name    varchar(20) not null,
        dept_name varchar(20),
        salary numeric(8,2)

• 主鍵(primary key):限制唯一的值，主要用於區分資料表中的每一筆資料
• 外來鍵參考(foreign key):限制屬性的值必須參考到關聯中主鍵屬性的值
• 非空值(not null):限制屬性中不得為 null 值

    create table instructor(
        ID          char(5),
        name        varchar(20) not null,
        dept_name   varchar(20),
        salary      numeric(8,2),
        primary key (ID),
        foreign key (dept_name) references department)

• 插入資料表(insert):

    insert into instructor values (‘10211’, null, ’Biology’, 66000);

• 刪除關聯中的原組(delete):

    delete from student;

• 刪除資料表(drop table):

    drop table r;//(delete from r;)

• 修改資料表(alter)

    alter table r add A D
    //A為新增的屬性名稱，D為類型

    alter table r drop A 

3-3 SQL查詢的基本結構

3.3.1 單一關聯的查詢

• from:欲查詢的關聯

• select(選擇):顯示查詢欄位的最終結果

    可在後加上運算，但不會改變原本的資料
  

  • select distinst:查詢結果不含重複值
  • select all(*):顯示所有查詢結果

    select distinct dept_name
    from instructo

• where:可用來設定查詢的條件

    select name
    from instructor
    where dept_name= ‘Comp. Sci.'and salary > 80000

    資訊系薪水大於80000的教授

3.3.2 多關聯查詢

• 資料不會存在同一資料表，需要多關聯的查詢方式
• 執行順序:from,where,select
• 查詢有開課的教師數量:

    select name, course_id
    from instructor, teaches
    where  instructor.ID = teaches.ID

3.3.3 自然關聯

• 自然關聯(natual join):只考慮雙方資料表中有相同屬性值的欄位

select name, title
from instructor natural join teaches 

• 列出教師的名稱以及他們教導課程的職稱

select name, title
from instructor natural join teaches, course
where teaches.course_id= course.course_id;

3.4其他運算

3.4.1 重命名操作

old-name as new-name

select ID, name, salary/12 as monthly_salary
from instructor

3.4.2 字符串操作

(%):任何子字符串匹配
(_): 任何字符匹配。

• 查找名稱包含子字符串"dar"的所有教師的名稱

select name
from instructor
where name like '%dar%' 

3.4.3 Select中的選擇子句

• 表示所有欄位的資料

select instructor *

3.4.4 查詢結果排序

• 按字母順序列出所有教師的名稱

select distinct name
from    instructor
order by name

• 默認值為升序(ASC)
• 可以為每個屬性指定DESCFOR降序

order by name desc

• 對多個屬性進行排序

order by dept_name, name

3.4.5 Where子句

• between:指定值可在某數據之間

    EX：查找所有教師的名稱
    薪水在90,000美元到100,000美元之間
  

  select name
  from instructor 
  where salary between 90000 and 100000
  

• 比較元組

  select name, course_id
  from instructor, teaches
  where (instructor.ID, dept_name) = (teaches.ID, ’Biology’);
  

3.5 集合運算

• 操作聯合，交叉，除外，上述每個操作都會自動消除重複值
• 保留重複值:union all, intersect all,except all.

3.6 空值

• null表示一個未知值或不存在值

• 涉及null的任何算術表達式結果都是null

    5 + null returns null
  

• is null:檢查空值

    Example: Find all instructors whose salary is null.
  

  select name
  from instructor
  where salary is null
  

• NULL的三維邏輯

  • OR: (unknown or true) = true,
    (unknown or false) = unknown
    (unknown or unknown) = unknown
  • AND:(true and unknown) = unknown,
    (false and unknown) = false,
    (unknown and unknown) = unknown
  • NOT: (notunknown) = unknown

3.7聚集函數

聚集函數為多個值輸入後返回為單一值的函數

3.7.1 基本聚集

• 查找在2010年春季學期教授課程的教師總數

  select count (distinct ID)from teacheswhere semester = ’Spring’ and year = 2010

3.7.2 聚集的分組

• 找到每個部門的教練的平均工資

  select dept_name, avg (salary)
  from instructor
  group by dept_name;

• select中唯一能出現的非聚集屬性函數，必須存在於group by子句中

3.7.3 Having子句

• 找到所有部門平均工資大於42000的姓名

  select dept_name, avg (salary)
  from instructor 
  group by dept_name
  having avg (salary) > 42000;

• having和where的差別在於前者能使用聚集函數，後者不行

3.7.4 null值和聚合

• 全部薪水

  select sum(salary )
  from instructor
  

  以上語句忽略NULL，如有為空集合結果為null
  

3.8 子查詢

3.8.1 集合成員

• 在查詢中使用查詢結果

select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in
 (select course_id, sec_id, semester, year
  from teaches
  where teaches.ID= 10101);

3.8.2 集合比較

• some:至少符合一個條件
• all:必須符合所有條件
• 找出有薪水的教師的名稱大於生物部中的（至少一個）教練的薪資

select name
from instructor
where salary > some (select salary
    from instructor
    where dept name = ’Biology’);

• 
• 找出薪水大於生物部門所有教師的所有教師姓名。

select name
from instructor
where salary > all (select salary
    from instructor
    where dept name = ’Biology’);

3.8.3 新關聯測試

• exists:若子查詢結果為空集合回傳flase，有資料則回傳ture
• exists:r ⇔ r ≠Ø
• not exists:r ⇔ r = Ø
• “查找秋季2009年學期和2010年春季學期所教授的所有課程”

select course_id
from section as S
where semester = ’Fall’ and year= 2009 and 
    exists (select *
            from section as T
            where semester = ’Spring’ and year= 2010 and S.course_id= T.course_id);

3.8.4 重複值測試

• unique:檢查子查詢結果是否有重複值，有則回傳flase,否為true

select T.course_id
from course as T
where unique (select R.course_id
            from section as R
            where T.course_id= R.course_id and R.year= 2009);

3.8.5 from的子查詢

• 找到那些平均工資大於42,000美元部門的平均教師薪水

select dept_name, avg_salary
from (select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name)
where avg_salary> 42000;

• lateral:查詢外部查詢的屬性

select name, salary, avg_salary
from instructor I1, lateral (select avg(salary) as avg_salary
                from instructor I2
                where I2.dept_name= I1.dept_name);

3.8.6 with子句

• with提供了暫存資料集的方法，其定義僅可使用一次
• 查出最大預算的所有部門

with max_budget(value) as (select max(budget)from department)
select budget
from department, max_budget
where department.budget= max_budget.value;

3.8.7 標量子查詢

• 標量子查詢:子查詢查詢結果只回傳單一屬性的情況

3.9 修改資料庫

3.9.1 刪除

• 刪除薪水低於教師平均工資的所有教師

delete from instructor
where salary< (select avg (salary) from instructor);

• 先計算Avg(Salary)並找到要刪除的所有元組
• 之後刪除上面找到的所有元組

3.9.2 增加

• 為課程添加新元組

insert into course (course_id, title, dept_name, credits)
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

or

insert into course (course_id, title, dept_name, credits)
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

• 將所有教師添加到Studet關係與Tot_CredS到0

insert into studentselect ID, name, dept_name, 0
from instructor

3.9.3 更新

• 工資超過100,000美元的教練的工資增加3%，其他人為5%

update instructor
    set salary = salary * 1.03
    where salary > 100000;
update instructor
    set salary = salary * 1.05
    where salary <= 100000;

• 可以使用case語句更好

update instructor
    set salary = case
            when salary <= 100000 then salary * 1.05
            else salary * 1.03
            end

• 可搭配子查詢
• 重新計算和更新所有學生的Tot_creds值

update student S 
    set tot_cred= ( select sum(credits)
    from takes natural join course
    where S.ID= takes.ID and 
        takes.grade<> ’F’ and
        takes.grade is not null);