2014 Fall2014 Fall 11

Chapter 7:Chapter 7:Introduction to SQLIntroduction to SQL

楊立偉教授台灣大學工管系

註 註 :: 於於 1111 版為版為 Chapter 6Chapter 6

Chapter 7 22

SQL OverviewSQL Overview Structured Query Language Structured Query Language 結構式查詢結構式查詢

語言語言 The standard for relational database The standard for relational database

management systems (RDBMS) management systems (RDBMS) 19861986 成為成為 ANSIANSI 標準標準 , 1987, 1987 成為成為 ISOISO 標準標準 各家廠商的實作可能略有不同各家廠商的實作可能略有不同

RDBMS: A database management RDBMS: A database management system that manages data as a system that manages data as a collection of tables in which all collection of tables in which all relationships are represented by relationships are represented by common values in related tablescommon values in related tables

Chapter 7 33

History of SQLHistory of SQL 1970–E. Codd develops relational database 1970–E. Codd develops relational database

conceptconcept 1974-1979–System R with Sequel (later SQL) 1974-1979–System R with Sequel (later SQL)

created at IBM Research Labcreated at IBM Research Lab 1979–Oracle markets first relational DB with SQL1979–Oracle markets first relational DB with SQL 1986–ANSI SQL standard released1986–ANSI SQL standard released 1989, 1992, 1999, 2003–Major ANSI standard 1989, 1992, 1999, 2003–Major ANSI standard

updatesupdates Current–SQL is supported by most major Current–SQL is supported by most major

database vendorsdatabase vendors Oracle, Microsoft SQL Server, IBM DB2, MySQL, Postgre SQL, etc.Oracle, Microsoft SQL Server, IBM DB2, MySQL, Postgre SQL, etc.

Chapter 7 44

Purpose of SQL StandardPurpose of SQL Standard

Specify syntax/semantics for data definition Specify syntax/semantics for data definition

and manipulation and manipulation 資料定義與操作的語法資料定義與操作的語法 Define data structures Define data structures 定義了資料結構定義了資料結構 Enable portability Enable portability 實現了可攜性實現了可攜性 Allow for later growth/enhancement to Allow for later growth/enhancement to

standard standard 允許日後做擴充允許日後做擴充

Chapter 7 55

Benefits of a Standardized Benefits of a Standardized Relational LanguageRelational Language

Reduced training costs Reduced training costs 降低學習成本降低學習成本 Productivity Productivity 提高生產力提高生產力 Application portability Application portability 應用程式可攜性應用程式可攜性 Application longevity Application longevity 應用程式長久性應用程式長久性 Reduced dependence on a single Reduced dependence on a single

vendor vendor 減少依賴單一廠商減少依賴單一廠商 Cross-system communication Cross-system communication 有助跨有助跨

系統溝通系統溝通

Chapter 7 66

SQL EnvironmentSQL Environment CatalogCatalog

A set of schemas that constitute the description of a databaseA set of schemas that constitute the description of a database SchemaSchema

The structure that contains descriptions of objects created by a The structure that contains descriptions of objects created by a user (base tables, views, constraints)user (base tables, views, constraints)

Data Definition Language (DDL)Data Definition Language (DDL) Commands that define a database, Commands that define a database, including creating, altering, including creating, altering,

and dropping tables and establishing constraintsand dropping tables and establishing constraints Data Manipulation Language (DML)Data Manipulation Language (DML)

Commands that Commands that maintain and query a databasemaintain and query a database Data Control Language (DCL)Data Control Language (DCL)

Commands that control a database, including administering Commands that control a database, including administering privileges and committing dataprivileges and committing data

Chapter 7 77

Figure 7-1A simplified schematic of a typical SQL environment, as described by the SQL: 2008 standard

不同的 Environment(或稱 Space)

開發用 正式用

Chapter 7 88

Some SQL Data typesSome SQL Data types(Table 7-2) (Table 7-2)

資料型別可依廠商別而略有不同或自有擴充

Chapter 7 99

Figure 7-4 DDL, DML, DCL, and the database development process

DDL :CREATE TABLEALTER TABLEDROP TABLE…

DML : INSERTUPDATEDELETESELECT…

設計

開發實作

維護

Chapter 7 1010

SQL Database DefinitionSQL Database Definition Data Definition Language (DDL)Data Definition Language (DDL)

Major CREATE statements:Major CREATE statements:

CREATE SCHEMA–defines a portion of the CREATE SCHEMA–defines a portion of the

database owned by a particular userdatabase owned by a particular user

CREATE TABLE–defines a table and its columnsCREATE TABLE–defines a table and its columns

CREATE VIEW–defines a logical view from one CREATE VIEW–defines a logical view from one

or more tables or more tables 由一至多張表格所構成的虛擬表格 ( 視界 )

Chapter 7 1111

Table CreationTable CreationFigure 7-5 General syntax for CREATE TABLE

Steps in table creation:

1. Identify data types for attributes

2. Identify columns that can and cannot be null

3. Identify columns that must be unique (candidate keys)

4. Identify primary key–foreign key mates

5. Determine default values

6. Identify constraints on columns (domain specifications)

7. (optional) Create the table and associated indexes

語法表示　 [ ] 表選項 , 可填可不填 { } 表多選 , 多個選一個

Chapter 7 1212

The following slides create tables The following slides create tables for this E-R modelfor this E-R model

Chapter 7 1313

Figure 7-6 SQL database definition commands for Pine Valley Furniture

Overall table definitions

Chapter 7 1414

Defining attributes and their data types

decimal [(p[, s])] 和 numeric [(p[ , s])]• p 固定有效位數，小數點左右兩側都包括在內• s 小數位數的數字。• numeric 與 decimal 的功能相同。

為 key 取一個名字

語法參考 http://technet.microsoft.com/zh-tw/library/ms187746.aspx

Chapter 7 1515

Non-nullable specification

Identifying primary key

Primary keys can never have NULL values

Chapter 7 1616

Non-nullable specifications

Primary key

Some primary keys are composite– composed of multiple attributes

注意 PK 為複合欄位時的寫法

為 key 取一個名字

Chapter 7 1717

Default value 指定預設值

Domain constraint

Controlling the values in attributes

Chapter 7 1818

Primary key of parent table

Identifying foreign keys and establishing relationships

Foreign key of dependent table

Chapter 7 1919

Data Integrity ControlsData Integrity Controls Referential integrity–constraint Referential integrity–constraint

that ensures that foreign key that ensures that foreign key values of a table must match values of a table must match primary key values of a related primary key values of a related table in 1:M relationshipstable in 1:M relationships

Restricting:Restricting: Deletes of primary recordsDeletes of primary records Updates of primary recordsUpdates of primary records Inserts of dependent recordsInserts of dependent records

Chapter 7 2020

Relational integrity is enforced via the primary-key to foreign-key match

Figure 7-7 Ensuring data integrity through updates

1

2

3

4

自動檢查完整性

有四種指定方法

註 : 有些較簡易的RDBMS 可能未支援

Chapter 7 2121

Changing and Removing Changing and Removing TablesTables

ALTER TABLE statement allows you to ALTER TABLE statement allows you to change change columncolumn specifications: specifications: ALTER TABLE CUSTOMER_T ADD (TYPE ALTER TABLE CUSTOMER_T ADD (TYPE

VARCHAR(2))VARCHAR(2)) ALTER TABLE CUSTOMER_T DROP TYPEALTER TABLE CUSTOMER_T DROP TYPE 尚包含改名、改型別等功能；其它請參考各尚包含改名、改型別等功能；其它請參考各 RDBMSRDBMS 語法語法

DROP TABLE statement allows you to DROP TABLE statement allows you to remove tables from your schema:remove tables from your schema: DROP TABLE CUSTOMER_TDROP TABLE CUSTOMER_T

Chapter 7 2222

Create column indexCreate column index Speed up in specific columns Speed up in specific columns

替某個或某些欄位建立索引 ExampleExample

CREATE INDEX indexname ON CREATE INDEX indexname ON

CUSTOMER_T(CUSTOMER_NAME)CUSTOMER_T(CUSTOMER_NAME)

This makes an index for the CUSTOMER_NAME field of This makes an index for the CUSTOMER_NAME field of

the CUSTOMER_T tablethe CUSTOMER_T table

該欄位的查詢速度會大幅增加該欄位的查詢速度會大幅增加 Every key field (PK or FK) is suggested to add Every key field (PK or FK) is suggested to add

indexindex

加快跨表關聯加快跨表關聯

Chapter 7 2323

Insert StatementInsert Statement Adds data to a table Adds data to a table 開始加入資料至表格內開始加入資料至表格內 Inserting into a tableInserting into a table

INSERT INTO CUSTOMER_T VALUES (001, INSERT INTO CUSTOMER_T VALUES (001, ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601);‘Gainesville’, ‘FL’, 32601);

Inserting a record that has some null attributes Inserting a record that has some null attributes requires identifying the fields that actually get requires identifying the fields that actually get datadata INSERT INTO PRODUCT_T (PRODUCT_ID, INSERT INTO PRODUCT_T (PRODUCT_ID,

PRODUCT_DESCRIPTION,PRODUCT_FINISH, STANDARD_PRICE, PRODUCT_DESCRIPTION,PRODUCT_FINISH, STANDARD_PRICE, PRODUCT_ON_HAND) VALUES (1, ‘End Table’, ‘Cherry’, 175, 8);PRODUCT_ON_HAND) VALUES (1, ‘End Table’, ‘Cherry’, 175, 8);

Inserting from another table Inserting from another table 直接將查詢結果加入直接將查詢結果加入 INSERT INTO CA_CUSTOMER_T SELECT * FROM CUSTOMER_T INSERT INTO CA_CUSTOMER_T SELECT * FROM CUSTOMER_T

WHERE STATE = ‘CA’;WHERE STATE = ‘CA’;

Chapter 7 2424

Creating Tables with Identity ColumnsCreating Tables with Identity Columns

Inserting into a table does not require explicit customer ID Inserting into a table does not require explicit customer ID entry or field listentry or field list

INSERT INTO CUSTOMER_T VALUES ( ‘Contemporary INSERT INTO CUSTOMER_T VALUES ( ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601);Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601);

New with SQL:2008

自動編號欄位型別

加入資料時不需指定該欄位之值

Chapter 7 2525

Delete StatementDelete Statement

Removes rows from a table Removes rows from a table

將表格內將表格內 [[ 部份部份 ]] 資料刪除資料刪除 Delete certain rowsDelete certain rows

DELETE FROM CUSTOMER_T WHERE STATE = DELETE FROM CUSTOMER_T WHERE STATE = ‘HI’;‘HI’;

使用使用 WHEREWHERE 條件子句條件子句 Delete all rowsDelete all rows

DELETE FROM CUSTOMER_T;DELETE FROM CUSTOMER_T;

Chapter 7 2626

Update StatementUpdate Statement Modifies data in existing rowsModifies data in existing rows

修改表格內資料之值修改表格內資料之值 Update a certain rowUpdate a certain row

UPDATE PRODUCT_T SET UNIT_PRICE = 775 UPDATE PRODUCT_T SET UNIT_PRICE = 775 WHERE PRODUCT_ID = 7;WHERE PRODUCT_ID = 7;

使用使用 WHEREWHERE 條件子句 條件子句 (( 欄位條件的布林邏輯組合欄位條件的布林邏輯組合 )) Delete a lot of rows Delete a lot of rows 小心使用！小心使用！

UPDATE PRODUCT_T SET UPDATE PRODUCT_T SET PRODUCT_DESCRIPTION=“”; PRODUCT_DESCRIPTION=“”; 清空欄位清空欄位

UPDATE PRODUCT_T SET UNIT_PRICE = 775; UPDATE PRODUCT_T SET UNIT_PRICE = 775; 何何意？意？

Chapter 7 2727

SELECT StatementSELECT Statement Used for queries on single or multiple tablesUsed for queries on single or multiple tables Clauses of the SELECT statement:Clauses of the SELECT statement:

SELECT SELECT 要取出哪些欄位要取出哪些欄位 List the columns (and expressions) that should be returned from the List the columns (and expressions) that should be returned from the

queryquery FROM FROM 從哪張表從哪張表

Indicate the table(s) or view(s) from which data will be obtainedIndicate the table(s) or view(s) from which data will be obtained WHERE WHERE 要取出哪些筆紀錄 要取出哪些筆紀錄 (( 條件子句條件子句 ))

Indicate the conditions under which a row will be included in the resultIndicate the conditions under which a row will be included in the result GROUP BY GROUP BY 紀錄是否要合併紀錄是否要合併 , , 用哪些欄位合併用哪些欄位合併

Indicate categorization of results Indicate categorization of results HAVING HAVING 若紀錄有合併若紀錄有合併 , , 是否要再做篩選 是否要再做篩選 (( 條件子句條件子句 ))

Indicate the conditions under which a category (group) will be Indicate the conditions under which a category (group) will be includedincluded

ORDER BY ORDER BY 依哪些欄位做排序依哪些欄位做排序 Sorts the result according to specified criteriaSorts the result according to specified criteria

Chapter 7 2828

Figure 7-10 SQL statement processing order

內部 RDBMS 在解釋這句命令時的處理順序

Chapter 7 2929

SELECT Example (1)SELECT Example (1)

Find products with standard price less than Find products with standard price less than $275$275

SELECTSELECT PRODUCT_NAME, STANDARD_PRICE PRODUCT_NAME, STANDARD_PRICE

FROMFROM PRODUCT_V PRODUCT_V

WHEREWHERE STANDARD_PRICE < 275; STANDARD_PRICE < 275;

Table 7-3: Comparison Operators in SQL

Chapter 7 3030

SELECT Example (2) Using SELECT Example (2) Using AliasAlias

Alias is an alternative column or table nameAlias is an alternative column or table name

SELECT CUSTOMER_V.CUSTOMER,SELECT CUSTOMER_V.CUSTOMER,CUSTOMER_V.CUSTOMER_ADDRESS CUSTOMER_V.CUSTOMER_ADDRESS

FROM CUSTOMER_VFROM CUSTOMER_VWHERE CUSTOMER_V.CUSTOMER = ‘Home Furnishings’;WHERE CUSTOMER_V.CUSTOMER = ‘Home Furnishings’;

SELECT SELECT CUSTCUST.CUSTOMER AS .CUSTOMER AS NAMENAME, , CUST.CUSTOMER_ADDRESS CUST.CUSTOMER_ADDRESS FROM CUSTOMER_V FROM CUSTOMER_V CUSTCUSTWHERE WHERE NAMENAME = ‘Home Furnishings’; = ‘Home Furnishings’;

取個別名 , 比較方便指定 , 也可省去重複打字

原句

使用別名

Chapter 7 3131

SELECT Example (3) SELECT Example (3) Using a FunctionUsing a Function

可以使用函數對欄位做運算可以使用函數對欄位做運算 例如 例如 COUNT(), MAX(), MIN(), SUM(), AVERAGE()COUNT(), MAX(), MIN(), SUM(), AVERAGE()

…… 等等 依依 RDBMSRDBMS 不同另有許多擴充函數不同另有許多擴充函數

Using the COUNT Using the COUNT aggregate functionaggregate function to to find totals find totals 找出總筆數找出總筆數

SELECT SELECT COUNT(*)COUNT(*) FROM ORDER_LINE_VFROM ORDER_LINE_VWHERE ORDER_ID = 1004;WHERE ORDER_ID = 1004;

Note: with aggregate functions Note: with aggregate functions you can’t have single-valued you can’t have single-valued columns included in the SELECT clausecolumns included in the SELECT clause

* * 是 是 "" 所有欄位所有欄位 " " 的簡寫的簡寫改以特定欄位亦可改以特定欄位亦可

Chapter 7 3232

SELECT Example (4) Boolean SELECT Example (4) Boolean OperatorsOperators

ANDAND, , OROR, and , and NOTNOT Operators for Operators for customizing conditions in WHERE clausecustomizing conditions in WHERE clause

SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH, SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH, STANDARD_PRICESTANDARD_PRICEFROM PRODUCT_VFROM PRODUCT_VWHERE (PRODUCT_DESCRIPTION WHERE (PRODUCT_DESCRIPTION LIKELIKE ‘ ‘%%Desk’Desk’

OROR PRODUCT_DESCRIPTION = ‘Round PRODUCT_DESCRIPTION = ‘Round Table’) Table’)

ANDAND STANDARD_PRICE > 300; STANDARD_PRICE > 300;Note: the LIKE operator allows you to compare strings using wildcards. For example, the % wildcard in ‘%Desk’ indicates that all strings that have any number of characters preceding the word “Desk” will be allowed

LIKE 是做字串比對用的 , 支援萬用字元 % 或 _ ( 或以 * 與 ? 表示 )

Chapter 7

LIKE operator and wildcardsLIKE operator and wildcards % or * : zero to many of any characters% or * : zero to many of any characters _ or ? : one of any characters_ or ? : one of any characters ExampleExample

Mic* matches Mickey, Michael, Michelle, etc.Mic* matches Mickey, Michael, Michelle, etc. *son matches Dickson, Jackson, Bobson, etc.*son matches Dickson, Jackson, Bobson, etc. s?n matches sun, son, san, sin, etc.s?n matches sun, son, san, sin, etc. 可以多個混合使用 例 可以多個混合使用 例 c??p* matches computer, c??p* matches computer,

campcamp

3333

Chapter 7 3434

Venn Diagram from Previous Venn Diagram from Previous QueryQuery

集合圖

By default, processing order of Boolean operators is NOT, then AND, then OR

Chapter 7 3535

SELECT Example (5) SELECT Example (5) Sorting Results Sorting Results with the ORDER BY Clause with the ORDER BY Clause 將查詢結果將查詢結果

做排序做排序 Sort the results first by STATE, and Sort the results first by STATE, and within a state by CUSTOMER_NAMEwithin a state by CUSTOMER_NAME

SELECT CUSTOMER_NAME, CITY, STATESELECT CUSTOMER_NAME, CITY, STATE

FROM CUSTOMER_VFROM CUSTOMER_V

WHERE STATE WHERE STATE ININ (‘FL’, ‘TX’, ‘CA’, ‘HI’) (‘FL’, ‘TX’, ‘CA’, ‘HI’)

ORDER BYORDER BY STATE, CUSTOMER_NAME; STATE, CUSTOMER_NAME;

Note: the IN operator in this example allows you to include rows whose STATE value is either FL, TX, CA, or HI. It is more efficient than separate OR conditions 跟寫 STATE=‘FL’ OR STATE=‘TX’ OR … 是一樣的效果

ORDER BY field1 [ASC|DESC] [,field2 [ASC|DESC]…]可用 ASC 或 DESC 來指定升冪或降冪排列

Chapter 7 3636

SELECT Example (6) SELECT Example (6) Categorizing Results Using the GROUP BY Categorizing Results Using the GROUP BY

ClauseClause For use with aggregate functions For use with aggregate functions 需配合集合函數使用需配合集合函數使用

Scalar aggregateScalar aggregate: single value returned from SQL query with : single value returned from SQL query with

aggregate function aggregate function 若單只使用集合函數若單只使用集合函數 , , 只傳回單筆紀錄只傳回單筆紀錄 , , 如如 count(*)count(*)

Vector aggregateVector aggregate: multiple values returned from SQL query with : multiple values returned from SQL query with

aggregate function (via GROUP BY) aggregate function (via GROUP BY) 若配合若配合 GROUP BYGROUP BY 將傳回多筆將傳回多筆

SELECT CUSTOMER_STATE, COUNT(CUSTOMER_STATE) SELECT CUSTOMER_STATE, COUNT(CUSTOMER_STATE)

FROM CUSTOMER_VFROM CUSTOMER_V

GROUP BYGROUP BY CUSTOMER_STATE; CUSTOMER_STATE;

Note: you can use single-value fields with aggregate functions if they are Note: you can use single-value fields with aggregate functions if they are

included in the GROUP BY clauseincluded in the GROUP BY clause

Chapter 7 3737

原始表格

SELECT area, count(*)FROM memberGROUP BY area;

SELECT gender, count(*)FROM memberGROUP BY gender;

Chapter 7 3838

原始表格

SELECT gender, education,

count(*) AS ppl

FROM member

GROUP BY gender,

education;

Chapter 7 3939

原始表格

SELECT gender, education,

count(*) AS ppl

FROM member

GROUP BY gender, education

ORDER BY count(*) DESC;

Chapter 7 4040

原始表格

SELECT gender, education,

count(*) AS ppl,

max(age)

FROM member

GROUP BY gender,

education;使用不同的函數

Chapter 7 4141

SELECT Example (7) SELECT Example (7) Qualifying Results by Categories Qualifying Results by Categories

Using the HAVING ClauseUsing the HAVING Clause For use with GROUP BY For use with GROUP BY

將將 GROUP BYGROUP BY 後的結果再用條件過濾的意思後的結果再用條件過濾的意思 語法與語法與 WHEREWHERE 一樣一樣SELECT CUSTOMER_STATE, SELECT CUSTOMER_STATE,

COUNT(CUSTOMER_STATE) COUNT(CUSTOMER_STATE) FROM CUSTOMER_VFROM CUSTOMER_VGROUP BY CUSTOMER_STATEGROUP BY CUSTOMER_STATEHAVINGHAVING COUNT(CUSTOMER_STATE) > 1; COUNT(CUSTOMER_STATE) > 1;Like a WHERE clause, but it operates on groups (categories), not on individual rows. Here, only those groups with total numbers greater than 1 will be included in final result

Chapter 7 4242

SELECT gender, education,

count(*) AS ppl

FROM member

GROUP BY gender, education

HAVING education=' 大學 ';

SELECT gender, education,

count(*) AS ppl

FROM member

GROUP BY gender,

education;

HAVING 可以想成是 GROUP BY 後的 WHERE

Chapter 7 4343

Using and Defining ViewsUsing and Defining Views Views provide users controlled access to tablesViews provide users controlled access to tables

Ex. Ex. 只可看到某些欄位只可看到某些欄位 , , 或建立某些常用查詢或建立某些常用查詢 Dynamic ViewDynamic View

A “virtual table” created dynamicallyA “virtual table” created dynamically No data actually storedNo data actually stored Based on SQL SELECT statement on base tables or other viewsBased on SQL SELECT statement on base tables or other views

Materialized ViewMaterialized View Copy or replication of dataCopy or replication of data Data actually storedData actually stored Must be refreshed periodically to match the corresponding Must be refreshed periodically to match the corresponding

base tables base tables 需資料更新以維持一致性需資料更新以維持一致性 , , 少用少用

Chapter 7 4444

Sample CREATE VIEWSample CREATE VIEWCREATE VIEW CREATE VIEW EXPENSIVE_STUFF_VEXPENSIVE_STUFF_V AS AS

SELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICESELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE

FROM PRODUCT_TFROM PRODUCT_T

WHERE UNIT_PRICE >300 ;WHERE UNIT_PRICE >300 ;

View has a name

View is based on a SELECT statement, and acts like Table

可分為 read-only view 或 updateable view ( 多為前者 )

Chapter 7 4545

Advantages of ViewsAdvantages of Views Simplify query commandsSimplify query commands Provide customized view for userProvide customized view for user

常用查詢可建立為常用查詢可建立為 viewview

善用善用 viewview 可簡化複雜查詢可簡化複雜查詢

Disadvantages of ViewsDisadvantages of Views Use processing time each time view is Use processing time each time view is

referencedreferenced May or may not be directly updateableMay or may not be directly updateable

處理速度可能稍慢處理速度可能稍慢有些有些 RDBMSRDBMS 不支援不支援 updateable viewupdateable view