資料庫管理 筆記 - 第六章 Advanced SQL

筆記說明

此筆記用途在於台北科技大學資訊與財金管理系大二上資料庫管理重點整理
並非所有人都適用，部分對我而言稍加容易的內容並不會寫在此內。

PROCESSING MULTIPLE TABLES 處理兩者以上的表

• Join
  a relational operation that causes(原因) two or more tables with a common domain to be combined into a single table or view
• Equi-join 類似於交集 - 有重複值
  A join in which the joining condition(情況) is based on equality between values in the common columns; common columns appear redundantly(多餘) in the result table，也就是只要兩個表的某個值相同就會輸出，但那兩個同樣的值會被輸出兩次
• Natural (inner) join 類似於交集
  An equi-join(上面那點) in which one of the duplicate columns is eliminated(消除) in the result table
• Outer join 類似於餘集
  輸出表為值不相同的，A join in which rows that do not have matching values in common columns are nonetheless(儘管如此) included in the result table (as opposed(反對) to innerjoin, in which rows must have matching values in order to appear in the result table)
• Union join 類似於聯集
  Includes all data from each table that was joined
• 圖表

  

• 圖示 join 查詢

  

上述 join 的 example

• Equi-join

  

• Inner Join
  • INNER JOIN clause(子句) is an alternative(可替代) to WHERE clause, and is used to match primary(主) and foreign(外來) keys.
  • An INNER join will only return rows from each table that have matching rows in the other.
  • This query produces the same results as the previous(先前) equi-join example
  • 程式碼 (圖)

    

• Nature Join

  

• Outer Join
  • LEFT OUTER JOIN clause causes(原因) rows from the first mentioned(提起) table (customer) to appear even if there is no corresponding order data.
  • Unlike(不像) an INNER join, this will include customer rows with no matching order rows.
  • 程式碼 (圖)

    

透過命名變數讓輸出的名子改變

What are the employee ID and name of each employee and the name of his or her supervisor(主管) (label the supervisor’s name Manager)?

SUBQUERIES 子查詢

placing an inner query(內部查詢) (SELECT statement) inside an outer query(外部查詢)

• Options
  • In a condition of the WHERE clause
  • As a “table” of the FROM clause
  • Returning a field(區塊) for the SELECT clause
  • Within the HAVING clause 在 having 子句內
• 能夠做到的事
  • Noncorrelated(非相關性)
    • executed once for the entire outer query 執行一次整個外部查詢
    • Do not depend on data from the outer query 指不需要再外面寫一個 select，內部就可以寫外部查詢了
  • Correlated
    • executed once for each row returned by the outer query 對每一欄執行一次外部查詢
    • Make use of data from the outer query 利用外部查詢的資料
    • Can use the EXISTS and ALL operators 可以使用EXISTS和ALL運算
    • Example

      

• Example

  • 

  • 另外一個方法可以解決上述問題

    

• 查詢方法圖示

  

ANOTHER SUBQUERY EXAMPLE /(DERIVED TABLE) 另一種子查詢

將我們子查詢的值設成變數，在使他可以跟上面的查詢進行比較

COMBINING QUERIES

UNION

將多個查詢 union 在一起，但所有的資料欄位與屬性都必須相同

Combine the output (union of multiple queries) together into a single result table
With UNION queries, the quantity and data types of the attributes in the SELECT clauses of both queries must be identical.

If 寫法，CONDITIONAL EXPRESSIONS USING CASE KEYWORD 使用大小寫關鍵字的條件表達

Select Case 可以做到程式中的 if-then，只需要透過此方式即可。

CASE expression acts like an if-then statement. It allows you to choose what will appear in a column of the result set, depending on a condition.

範例如下

操作 View(關聯表)，MORE COMPLICATED SQL QUERIES 更多複雜的 SQL 操作

• 資料庫可能會有好幾千個 table，而且 table 有很多 column
  Production databases contain hundreds or even thousands of tables, and tables could include hundreds of columns.
• 因此查詢可能會變得非常複雜
  So, sometimes query requirements can be very complex.
• 有時可以透過 View 來合併查詢
  sometimes it’s useful to combine queries, through the use of Views.
• 如果使用 View(其中一個查詢)，也可以讓另外一個查詢使用 View，類似操作 table
  If you use a view (which is a query), you could have another query that uses the view as if it were a table.
• 簡單來說 View 就是虛擬的 table，結合現在的 table or View 產生

View 的操作範例

TIPS FOR DEVELOPING QUERIES

• 熟悉資料模型，實體與關聯性
  Be familiar with the data model (entities and relationships)
• 了解想要的結果
  Understand the desired(了解) results
• 知道結果中鎖需要的屬性
  Know the attributes desired in results
• 辨識實體所包含的屬性
  Identify the entities that contain desired attributes
• Review ERD
• 對每一個 link 建構一個 where 相等性
  Construct a WHERE equality for each link
• 如果需要，使用 GROUP BY 和 HAVING 子句
  Fine tune with GROUP BY and HAVING clauses if needed
• 考慮異常資料對資料庫的影響
  Consider the effect on unusual data

查詢效率的注意事項 QUERY EFFICIENCY CONSIDERATIONS

• 在 select 中標註屬性而不用 *
  Instead of SELECT *, identify the specific attributes in the SELECT clause; this helps reduce network traffic of result set
• 限制子查詢的數量，盡量在單查詢中查尋完畢
  Limit the number of subqueries; try to make everything done in a single query if possible
• 如果要多次使用同樣查詢，就建立成關聯表(View)
  If data is to be used many times, make a separate query and store it as a view

查詢設計方法 GUIDELINES FOR BETTER QUERY DESIGN

• 了解再查詢資料中使用 index
  Understand how indexes are used in query processing
• 保持優化且為最新
  Keep optimizer statistics up to date
• 對於文字或字段兩種資料型態兼容
  Use compatible data types for fields and literals(文字)
• Write simple queries
• 把複雜查詢拆成簡單查詢
  Break complex queries into multiple simple parts
• 盡量不要把查詢內嵌在另一個查詢
  Don’t nest one query inside another query
• 自身查詢不跟自身連接
  Don’t combine a query with itself (if possible avoid self-joins)
• 創建暫存表在擁有非常多查詢時
  Create temporary tables for groups of queries
• Combine update operations 合併更新操作
• 僅檢索需要的數據
  Retrieve only the data you need
• 沒有 index 就沒辦法對 DBMS 排序
  Don’t have the DBMS sort without an index
• Learn! (…)
• 考慮某些特殊查詢在總查詢處理時間會不會過多
  Consider the total query processing time for ad hoc queries

使用與定義關聯表 USING AND DEFINING VIEWS

• Dynamic View
  • 對於用戶新增動態虛擬表
    A “virtual table” created dynamically upon request by a user
  • 沒有資料儲存在 View，而是將原始資料提供給用戶
    No data actually stored; instead data from base table made available to user
  • 為 table or View 中的 SQL select 語法
    Based on SQL SELECT statement on base tables or other views
  • Advantage 優點
    • Simplify query commands
    • Assist(協助) with data security
    • Enhance(提高) programming productivity(生產率)
    • Contain most current base table data
    • Use little storage space
    • Provide customized(專屬打造) view for user
    • Establish(建立) physical(物理) data independence
    • 每次使用 View 都需要時間
      Use processing time each time view is referenced(被引用)
    • 可能沒有辦法直接更新
      May or may not be directly updateable
    • 謹慎使用 View
      As with all SQL constructs, you should use views with discretion
• 實質關聯表 Materialized View
  • 複製資料，實際儲存在 View
    Copy or replication of data, data actually stored
  • 需要不斷更新才可以與原本查詢的 table 資料相符
    Must be refreshed periodically to match corresponding base tables
• Sample
  • View has a name
  • View is based on a SELECT statement
  • CHECK_OPTIO 只能用來可以被更新的關聯表，並禁止更新不包含在 View 裡的資料
    CHECK_OPTION works only for updateable views and prevents updates that would create rows not included in the view

例行程序 ROUTINES AND TRIGGERS

• Routines 需要執行的程式區塊
• Functions 給 Routines 參數並回傳值
• Procedures 給 Routines 參數並不傳值
• Triggers 當 Routines 啟動到特殊事件時觸發
• Procedures and functions are called explicitly(明確調用). Triggers are event-driven(事件觸發).
• 圖示

  

• Triggers Sample 有兩張

  
  

SQL ENHANCEMENTS(增強)

• User-defined data types (UDT) 自定義資料型態
• 可設定子類別為標準型態 or object
  Subclasses of standard types or an object type
• Analytical functions (for OLAP)
  • CEILING, FLOOR, SQRT, RANK, DENSE_RANK, ROLLUP, CUBE, SAMPLE,
  • WINDOW –improved numerical analysis capabilities
• New Data Types
  BIGINT, MULTISET (collection), XML
• CREATE TABLE LIKE
  create a new table similar to an existing one
• MERGE

SQL EXTENSIONS(擴展)

• Persistent(永久的) Stored Modules (SQL/PSM)
• Capability(能力) to create and drop(刪除) code modules
• New statements: CASE, IF, LOOP, FOR, WHILE, etc.
• Makes SQL into a procedural language(可以變成函式相互呼叫)
• Oracle has propriety version(專有版本) called PL/SQL, and Microsoft SQL Server has Transact/SQL