20xxfall(文件)
【正文】 L Database Definition ? Data Definition Language (DDL) ? Major CREATE statements: ? CREATE SCHEMA–defines a portion of the database owned by a particular user ? CREATE TABLE–defines a table and its columns ? CREATE VIEW–defines a logical view from one or more tables 由一至多張表格所構(gòu)成的虛擬表格 (視界 ) Chapter 7 11 Table Creation Figure 75 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 語(yǔ)法表示 [ ] 表選項(xiàng) , 可填可不填 { } 表多選 , 多個(gè)選一個(gè) Chapter 7 12 The following slides create tables for this ER model Chapter 7 13 Figure 76 SQL database definition mands for Pine Valley Furniture Overall table definitions Chapter 7 14 Defining attributes and their data types decimal [(p[, s])] 和 numeric [(p[ , s])] ? p 固定有效位數(shù)，小數(shù)點(diǎn)左右兩側(cè)都包括在內(nèi) ? s 小數(shù)位數(shù)的數(shù)字。 ? Inserting from another table 直接將查詢結(jié)果加入 ? INSERT INTO CA_CUSTOMER_T SELECT * FROM CUSTOMER_T WHERE STATE = ?CA?。 Chapter 7 26 Update Statement ? Modifies data in existing rows 修改表格內(nèi)資料之值 ? Update a certain row ? UPDATE PRODUCT_T SET UNIT_PRICE = 775 WHERE PRODUCT_ID = 7。 Table 73: Comparison Operators in SQL Chapter 7 30 SELECT Example (2) Using Alias ? Alias is an alternative column or table name SELECT , FROM CUSTOMER_V WHERE = ?Home Furnishings?。 Note: the LIKE operator allows you to pare 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 是做字串比對(duì)用的 , 支援萬(wàn)用字元 %或 _ (或以 *與 ?表示 ) Chapter 7 LIKE operator and wildcards ? % or * : zero to many of any characters ? _ or ? : one of any characters ? Example ? Mic* matches Mickey, Michael, Michelle, etc. ? *son matches Dickson, Jackson, Bobson, etc. ? s?n matches sun, son, san, sin, etc. ? 可以多個(gè)混合使用 例 c??p* matches puter, camp 33 Chapter 7 34 Venn Diagram from Previous Query 集合圖 By default, processing order of Boolean operators is NOT, then AND, then OR Chapter 7 35 SELECT Example (5) Sorting Results with the ORDER BY Clause 將查詢結(jié)果做排序 ? Sort the results first by STATE, and within a state by CUSTOMER_NAME SELECT CUSTOMER_NAME, CITY, STATE FROM CUSTOMER_V WHERE STATE IN (?FL?, ?TX?, ?CA?, ?H
教學(xué)課件相關(guān)推薦
鄂ICP備17016276號(hào)-1