ch5-SQL_1.ppt

Chapter3 SQL Chapter 5: SQL Contents Basic Query Structure Set Operations Aggregate Functions Null Values Nested Subqueries Complex Queries History IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory Renamed Structured Query Language (SQL) ANSI and ISO standard SQL: SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003 History Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features. Not all examples here may work on your particular system SQL Features Non procedure Simple core functionality is only used nine words 综合统一 集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。 可以独立完成数据库生命周期中的全部活动： ? 定义关系模式，插入数据，建立数据库； ? 对数据库中的数据进行查询和更新； ? 数据库重构和维护 ? 数据库安全性、完整性控制等 Basic Query Structure SQL is based on relational algebra and relational calculus A typical SQL query has the form: Basic Query Structure equivalent to the relational algebra expression The result of an SQL query is a relation Note: for our examples we use the tables: Branch (branch-name, branch-city, assets) Customer (customer-name, customer-street, customer-city) Loan (loan-number, amount, branch-name) Account (account-number, balance, branch-name) Borrower (customer-name, loan-number) Depositor (customer-name, account-number) The select Clause The select clause list the attributes desired in the result of a query corresponds to the projection operation of the relational algebra The select Clause SQL names are case insensitive (i.e., you may use upper- or lower-case letters.) Duplicate Removal SQL allows duplicates in relations as well as in query results. Use select distinct to force the elimination of duplicates.Find the names of all branches in the loan relation, and remove duplicates select distinct branch-name from loan The keyword all specifies that duplicates are not removed. select all branch-name from loan Duplicate Removal 注意：distinct 作用于所有的目标列 select distinct branch