20xxfall

20xxfall内容摘要：

s an index for the CUSTOMER_NAME field of the CUSTOMER_T table 該欄位的查詢速度會大幅增加  Every key field (PK or FK) is suggested to add index 加快跨表關聯 Chapter 7 23 Insert Statement  Adds data to a table 開始加入資料至表格內  Inserting into a table  INSERT INTO CUSTOMER_T VALUES (001, „Contemporary Casuals‟, „1355 S. Himes Blvd.‟, „Gainesville‟, „FL‟, 32601)。  Inserting a record that has some null attributes requires identifying the fields that actually get data  INSERT INTO PRODUCT_T (PRODUCT_ID, PRODUCT_DESCRIPTION,PRODUCT_FINISH, STANDARD_PRICE, PRODUCT_ON_HAND) VALUES (1, „End Table‟, „Cherry‟, 175, 8)。  Inserting from another table 直接將查詢結果加入  INSERT INTO CA_CUSTOMER_T SELECT * FROM CUSTOMER_T WHERE STATE = „CA‟。 Chapter 7 24 Creating Tables with Identity Columns Inserting into a table does not require explicit customer ID entry or field list INSERT INTO CUSTOMER_T VALUES ( „Contemporary Casuals‟, „1355 S. Himes Blvd.‟, „Gainesville‟, „FL‟, 32601)。 New with SQL:2020 自動編號欄位型別 加入資料時不需指定該欄位之值 Chapter 7 25 Delete Statement  Removes rows from a table 將表格內 [部份 ]資料刪除  Delete certain rows  DELETE FROM CUSTOMER_T WHERE STATE = „HI‟。  使用 WHERE條件子句  Delete all rows  DELETE FROM CUSTOMER_T。 Chapter 7 26 Update Statement  Modifies data in existing rows 修改表格內資料之值  Update a certain row  UPDATE PRODUCT_T SET UNIT_PRICE = 775 WHERE PRODUCT_ID = 7。  使用 WHERE條件子句 (欄位條件的布林邏輯組合 )  Delete a lot of rows 小心使用。  UPDATE PRODUCT_T SET PRODUCT_DESCRIPTION=“”。 清空欄位  UPDATE PRODUCT_T SET UNIT_PRICE = 775。 何意。 Chapter 7 27 SELECT Statement  Used for queries on single or multiple tables  Clauses of the SELECT statement:  SELECT 要取出哪些欄位  List the columns (and expressions) that should be returned from the query  FROM 從哪張表  Indicate the table(s) or view(s) from which data will be obtained  WHERE 要取出哪些筆紀錄 (條件子句 )  Indicate the conditions under which a row will be included in the result  GROUP BY 紀錄是否要合併 , 用哪些欄位合併  Indicate categorization of results  HAVING 若紀錄有合併 , 是否要再做篩選 (條件子句 )  Indicate the conditions under which a category (group) will be included  ORDER BY 依哪些欄位做排序  Sorts the result according to specified criteria Chapter 7 28 Figure 710 SQL statement processing order 內部 RDBMS在解釋 這句命令時的處理順序 Chapter 7 29 SELECT Example (1)  Find products with standard price less than $275 SELECT PRODUCT_NAME, STANDARD_PRICE FROM PRODUCT_V WHERE STANDARD_PRICE 275。 Table 73: Comparison Operators in SQL Chapter 7 30 SE。