北邮数据库课程讲义chapter3.ppt

DataBase System Concepts
Chapter 3
SQL
DataBase System Concepts
SQL
Background
Basic Structure__query
DDL
DML( Insert ,Delete,Update)
Embedded SQL
Dynamic SQL
ODBC,JDBC
Transact_SQL
DataBase System Concepts
Background
Structured query language (SQL)
1970s , IBM Sequel(System R)
1986, ANSI and ISO , SQL_86 ;
1987, IBM, SAA_SQL;
1989, ANSI, SQL_89, an extended standard for SQL;
SQL_92;
1999 SQL;
SQL:2003
DataBase System Concepts
Background
Data definition language (DDL);
Data manipulation language (DML);
Data control language (DCL);
Integrity;
Authorization;
Embedded SQL and dynamic SQL;
SQL is based on set and relational operations with certain modifications and enhancements
DataBase System Concepts
Basic Structure__query
A typical SQL query has the form: select A1, A2, ..., An from r1, r2, ..., rm where P
Ai represent attributes
ri represent relations
P is a predicate.
This query is equivalent to the relational algebra expression.
A1, A2, ..., An(P (r1 x r2 x ... x rm))
The result of an SQL query is a relation
DataBase System Concepts
The select Clause
Find the names of all branches in the loan relation
An asterisk in the select clause denotes “all attributes”
The select clause corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query.
select branch_name from loan
select * from loan
In the “pure” relational algebra syntax, the query would be:
branch_name(loan)
DataBase System Concepts
The select Clause
The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples.
The query:
select loan_number, branch_name, amount  100 from loan
would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100.
DataBase System Concepts
The select Clause
SQL allows duplicates in relations as well as in query res