結(jié)構(gòu)式查詢語言

1、Chapter 7Chapter 7Chapter 7Chapter 7Chapter 7Chapter 7Chapter 7Figure 7-1A simplified schematic of a typical SQL environment, as described by the SQL: 200n standard實務(wù)上或稱為實務(wù)上或稱為DB SpaceChapter 7註：資料型別可能各家廠商略有不同或自有擴充註：資料型別可能各家廠商略有不同或自有擴充Chapter 7Figure 7-4 DDL, DML, DCL, and the database development p

2、rocess必會必會CREATE TABLEALTER TABLEDROP TABLE必會必會INSERTDELETEUPDATESELECTChapter 7Chapter 7Figure 7-5 General syntax for CREATE TABLESteps in table creation:1.Identify data types for attributes2.Identify columns that can and cannot be null3.Identify columns that must be unique (candidate keys)4.Identi

3、fy primary keyforeign key mates5.Determine default values6.Identify constraints on columns (domain specifications)7.Create the table and associated indexes語法表示語法表示 表選項表選項, 可填可不填可填可不填 表多選表多選, 多個選一個多個選一個Chapter 7Chapter 7Figure 7-6 SQL database definition commands for Pine Valley FurnitureOverall tabl

4、e definitionsChapter 7Defining attributes and their data typesdecimal (p, s) 和和 numeric (p , s) p 固定有效位數(shù)，小數(shù)點左右兩側(cè)都包括在內(nèi)固定有效位數(shù)，小數(shù)點左右兩側(cè)都包括在內(nèi) s 小數(shù)位數(shù)的數(shù)字。小數(shù)位數(shù)的數(shù)字。 numeric 與與 decimal 的功能相同。的功能相同。為為 key 取一個名字取一個名字語法參考 http:/ 7Non-nullable specificationIdentifying primary keyPrimary keys can never have NULL v

5、aluesChapter 7Non-nullable specificationsPrimary keySome primary keys are composite composed of multiple attributes注意PK為複合欄位時的寫法為為 key 取一個名字取一個名字Chapter 7Default value 指定預(yù)設(shè)值指定預(yù)設(shè)值Domain constraint指定合法值域指定合法值域Controlling the values in attributesChapter 7Primary key of parent tableIdentifying foreign k

6、eys and establishing relationshipsForeign key of dependent tableChapter 7Chapter 7Relational integrity is enforced via the primary-key to foreign-key matchFigure 7-7 Ensuring data integrity through updates1234自動檢查完整性自動檢查完整性有四種指定方法有四種指定方法注意注意 : 有些較簡單的有些較簡單的RDBMS可能不支援可能不支援Chapter 7Chapter 7Chapter 7Ch

7、apter 7New with SQL:2003自動編號欄位型別自動編號欄位型別加入資料時不需指定該欄位之值加入資料時不需指定該欄位之值Chapter 7Chapter 7Chapter 7Makes it easier to update a tableallows combination of Insert and Update in one statement 有點結(jié)合Insert與Update的意思Useful for updating master tables with new dataChapter 7Chapter 7Figure 7-10 SQL statement proc

8、essing order (adapted from van der Lans, p.100)內(nèi)部內(nèi)部RDBMS在解釋在解釋這句命令時的處理順序這句命令時的處理順序Chapter 7Table 7-3: Comparison Operators in SQLChapter 7取個別名取個別名, 比較方便指定比較方便指定, 也可省去重複打字也可省去重複打字原句原句使用使用別名別名Chapter 7Chapter 7Note: the LIKE operator allows you to compare strings using wildcards. For example, the % wi

9、ldcard in %Desk indicates that all strings that have any number of characters preceding the word “Desk” will be allowedLIKE 是做字串比對用的是做字串比對用的, 支援萬用字元支援萬用字元%或或_ (類似類似Windows的的*與與?)Chapter 7集合圖集合圖Chapter 7Note: the IN operator in this example allows you to include rows whose STATE value is either FL, T

10、X, 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 7Chapter 7原始原始表格表格SELECT area, count(*)FROM memberGROUP BY area;SELECT gender, count(*)FROM memberGROUP BY gender;Chapter

11、7原始原始表格表格SELECT gender, education, count(*) AS pplFROM memberGROUP BY gender, education;Chapter 7原始原始表格表格SELECT gender, education, count(*) AS pplFROM memberGROUP BY gender, educationORDER BY count(*) DESC;Chapter 7原始原始表格表格SELECT gender, education, count(*) AS ppl,max(age)FROM memberGROUP BY gender,

12、 education;使用不同的函數(shù)使用不同的函數(shù)Chapter 7Like 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 resultChapter 7SELECT gender, education, count(*) AS pplFROM memberGROUP BY gender, educationHAVING education=大學(xué)大學(xué);SELECT gender, education, count(*) AS pplFROM memberGROUP BY gender, education;HAVING可以想成是可以想成是GROUP BY後的後的WHEREChapter 7Chapter 7View has a nameVie