SQL PPT 讲稿1.ppt

* DBS201: Introduction to Structured Query Language (SQL) Lecture 9 * Agenda The basic commands and functions of SQL How to use SQL to query a database to extract useful information (The SELECT statement) * Introduction to SQL SQL: Structured Query Language Designed specifically for communicating with databases SQL functions fit into three broad categories: Data definition language (DDL) Data manipulation language (DML) Transaction Control Language (TCL) * Introduction to SQL (continued) Data definition language SQL includes commands to create Database objects such as tables Commands to define access rights to those database objects Data manipulation language Includes commands to insert, update, delete, and retrieve data within the database tables objects Transaction control language Includes commands to ensure the integrity of the database. * Introduction to SQL (continued) SQL is relatively easy to learn Basic command set has a vocabulary of less than 100 words Sample vocabulary: CREATE COLLECTION CREATE TABLE CREATE VIEW * Introduction to SQL (continued) Sample vocabulary (continued): DROP COLLECTION DROP TABLE DROP VIEW ALTER INSERT SELECT DELETE * Introduction to SQL (continued) American National Standards Institute (ANSI) prescribes a standard SQL Several SQL dialects exist Oracle, MySQL, Access etc * Data Manipulation Commands SELECT attributes also called fields FROM table(s) WHERE condition(s) ORDERBY attribute(s) * Sample Table: PART PARTNUMBER PARTDESC ONHAND CLASS WAREHOUSE PRICE AX12 Iron 104 HW 3 23.95 AZ52 Dartboard 20 SG 2 12.95 BA74 Basketball 40 SG 1 29.95 BH22 Cornpopper 95 HW 3 24.95 BT04 Gas Grill 11 AP 2 149.99 BZ66 Washer 52 AP 3 399.99 CA14 Griddle 78 HW 3 39.99 CB03 Bike 44 SG 1 299.99 CX11 Blender 112 HW 3 22.95 CZ81 Treadmill 68 SG 2 349.99 * Listing Table Rows SELECT Used to list contents of table Syntax SELECT Field1, Field 2,… FROM tablename WHERE Condition 1 AND/OR Condition 2