《数据库处理——基础、设计与实现(第十版)》第三章

DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-21
WHERE Clause Options: LIKE and Wildcards
SELECT * FROM SKU_DATA WHERE SKU LIKE '%2__';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-6
Using Oracle [SQL*Plus]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-24
SQL Built-in Functions (Continued)
SELECT SUM (ExtendedPrice) AVG (ExtendedPrice) MIN (ExtendedPrice) MAX (ExtendedPrice) ORDER_ITEM; AS AS AS AS OrderItemSum, OrderItemAvg, OrderItemMin, OrderItemMax
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-17
WHERE Clause Options: Ranges with Math Symbols
SELECT FROM WHERE AND * ORDER_ITEM ExtendedPrice >= 100 ExtendedPrice <= 200;
2-2
Using MS Access (Continued)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-3
Using MS Access (Continued)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-16
WHERE Clause Options: Ranges with BETWEEN
SELECT FROM WHERE * ORDER_ITEM ExtendedPrice BETWEEN 100 AND 200;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-15
WHERE Clause Options: NOT IN
SELECT FROM WHERE * SKU_DATA Buyer NOT IN ('Nancy Meyers', 'Cindy Lo'DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-20
WHERE Clause Options: LIKE and Wildcards (Continued)
SELECT FROM WHERE * SKU_DATA SKU_Description LIKE '%Tent%';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-18
WHERE Clause Options: LIKE and Wildcards
• The SQL keyword LIKE can be combined with wildcard symbols:
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-26
SQL Built-in Functions (Continued)
SELECT COUNT (DISTINCT Department) AS DeptCount FROM SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-14
WHERE Clause Options:- IN
SELECT FROM WHERE * SKU_DATA Buyer IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin');
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-22
SQL Built-in Functions
• There are five SQL Built-in Functions:
– COUNT – SUM – AVG – MIN – MAX
2-7
Using Oracle [Quest Software’s TOAD]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-8
Using MySQL [MySQL Command Line Client]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-13
WHERE Clause Options: OR
SELECT FROM WHERE OR * SKU_DATA Department = 'Camping' Department = 'Climbing';
2-4
Using MS Access - Results
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-5
Using MS SQL Server [SQL Query Analyzer]
– SQL 92 Standard (SQL Server, Oracle, etc.):
• _ = Exactly one character • % = Any set of one or more characters
– MS Access (based on MS DOS)
• ? • * = Exactly one character = Any set of one or more characters
FROM
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-25
SQL Built-in Functions (Continued)
SELECT COUNT(*) AS NumRows FROM ORDER_ITEM;
David M. Kroenke’s
Database Processing:
Fundamentals, Design, and Implementation
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-27
Arithmetic in SELECT Statements
SELECT Quantity * Price AS EP, ExtendedPrice FROM ORDER_ITEM;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition © 2006 Pearson Prentice Hall
2-12
WHERE Clause Options: AND
SELECT FROM WHERE AND * SKU_DATA Department = 'Water Sports' Buyer = 'Nancy Meyers';
2-11
Sort Order: Ascending and Descending
SELECT * FROM ORDER_ITEM ORDER BY Price DESC, OrderNumber ASC;
NOTE: The default sort order is ASC – does not have to be specified.