数据库英文第五章(精品·公开课件).ppt

Chapter 5 The Database Language SQL SQL (sometimes pronounced “sequel”) --- stands for “Structured Query Language” Evolution of SQL standard: SQL89 SQL92 (SQL2) SQL3 SQL is based on set and relational operations with certain modifications and enhancements Components of SQL: DDL: Data Definition Language -CREATE TABLE, DROP TABLE, etc. DML: Data Manipulation Language -Query: SELECT -Modification: INSERT, DELETE, UPDATE 5.1 Simple Queries in SQL 5.1 Simple Queries in SQL (cont.) 5.1.1 Projection in SQL -- The select clause Star as List of 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. Find the titles and length of all movies are produced by Disney Studios in 1990? Movie(title, year, length, inColor, studioName, producerC#) SELECT title, length FROM Movie WHERE studioName = ’Disney’ AND year = 1990 In the “pure” relational algebra syntax, what would the query be? Renaming columns The SQL allows renaming relations and attributes using the as clause: old-name AS new-name Example: SELECT title AS name, length AS duration FROM Movie WHERE studioName = ’Disney’ AND year = 1990 5.1.1 Projection in SQL -- The select clause (cont.) Expressions AS Values in Columns Example: SELECT title AS name, length*0.01667 AS lengthInHours FROM Movie WHERE studioName = ’Disney’ AND year = 1990 Trick: If you want an answer with a particular string in each row, use that constant as an expression. SELECT title AS name, length*0.01667 AS length, ‘hrs.’ AS inHours FROM Movie WHERE studioName = ’Disney’ AND year = 1990 Summary: The select clause can contain arithmetic expressions involving the operation, +, –, *, and /, and operating on constants or attributes of tuples. Remember: SQL is case insensitive, meaning you can use upper/lower case as