数据库英文课件4

1、1,More SQL,Database Modification Defining a Database Schema Views,2,Database Modifications,A modification command does not return a result (as a query does), but changes the database in some way. Three kinds of modifications: Insert a tuple or tuples. Delete a tuple or tuples. Update the value(s) of

2、 an existing tuple or tuples.,3,Insertion,To insert a single tuple: INSERT INTO VALUES ( ); Example: add Sydney Greenstreet to the list of stars of The Maltese Falcon. INSERT INTO StarsInVALUES(The Maltese Falcon, 1942, Sydney GreenStreet);,4,Specifying Attributes in INSERT,We may add to the relatio

3、n name a list of attributes. Two reasons to do so: We forget the standard order of attributes for the relation. We dont have values for all attributes, and we want the system to fill in missing components with NULL or a default value.,5,Example: Specifying Attributes,Another way to add Sydney Greens

4、treet to the list of stars of The Maltese Falcon. INSERT INTO StarsIn(movieTitle, movieYear, starName) VALUES(The Maltese Falcon, 1942, Sydney GreenStreet);,6,Inserting Many Tuples,We may insert the entire result of a query into a relation, using the form: INSERT INTO ( );,7,Example: Insert a Subque

5、ry,Using Studio and Movie, add to the relation Studio all movie studios that are mentioned in the relation Movie, but dont appear in Studio.,8,Solution,INSERT INTO Studio(name) (SELECT DISTINCT studioName FROM Movie WHERE studioName NOT IN (SELECT name FROM Studio);,9,Deletion,To delete tuples satis

6、fying a condition from some relation: DELETE FROM WHERE ;,10,Example: Deletion,Delete from relation StarsIn the fact that Sydney GreenStreet was a star in The Maltese Falcon: DELETE FROM StarsIn WHERE movieTitle = The Maltese Falcon AND movieYear = 1942 AND starName = Sydney Greenstreet;,11,Example:

7、 Delete all Tuples,Make the relation Likes empty: DELETE FROM Likes; Note no WHERE clause needed.,12,Example: Delete Many Tuples,Delete from MovieExec all movie executives whose net worth is low-less than ten million dollars. DELETE FROM MovieExec WHERE netWorth 10000000;,13,Updates,To change certai

8、n attributes in certain tuples of a relation: UPDATE SET WHERE ;,14,Example: Update,Modify the relation MovieExec by prepending the title Pres. In front of every movie executives who is the president of a studio: UPDATE MovieExec SET name = Pres. | name WHERE cert# IN (SELECT presC# FROM Studio);,15

9、,Defining a Database Schema,A database schema comprises declarations for the relations (“tables”) of the database. Several other kinds of elements also may appear in the database schema, including views, indexes, and triggers, which well introduce later.,16,Creating (Declaring) a Relation,Simplest f

10、orm is: CREATE TABLE ( ); To delete a relation: DROP TABLE ;,17,Elements of Table Declarations,Most basic element: an attribute and its type. The most common types are: INT or INTEGER REAL or FLOAT CHAR(n ) fixed-length string of n characters. VARCHAR(n ) variable-length string of up to n characters

11、.,18,Example: Create Table,CREATE TABLE MovieStar ( nameCHAR(30), addressVARCHAR(255), genderCHAR(1), birthdateDATE );,19,Dates and Times,DATE and TIME are types in SQL. The form of a date value is: DATE yyyy-mm-dd Example: DATE 2004-09-30,20,Times as Values,The form of a time value is: TIME hh:mm:s

12、s with an optional decimal point and fractions of a second following. Example: TIME 15:30:02.5,21,Modifying relation schemas,We can use ALTER to modify a relation schema. We have several options, the most important of which are: ADD followed by a column name and its data type; DROP followed by a col

13、umn name;,22,Adding Attributes,We may add a new attribute (“column”) to a relation schema by: ALTER TABLE ADD ; Example: ALTER TABLE MovieStar ADD phone CHAR(16);,23,Deleting Attributes,Remove an attribute from a relation schema by: ALTER TABLE DROP ; Example: we dont really need the license attribu

14、te for bars: ALTER TABLE MovieStar DROP birthdate;,24,Default values,When we create or modify tuples, we sometimes dont have values for all components. To address this problem, SQL provides the NULL value. However, there are times when we would prefer to use default value, the value that is placed i

15、n a component if no other value is known.,25,Example,We might wish to use the character ? as the default for an unknown gender, and we might also wish to use the earliest possible date, DATE 0000-00-00 for an unknown birthdate. CREATE TABLE MovieStar ( name CHAR(30), address VARCHAR(255), gender CHA

16、R(1) DEFAULT ?, birthdate DATE DEFAULT DATE 0000-00-00 );,26,Indexes,An index on an attribute A of a relation is a data structure that makes it efficient to find those tuples that have a fixed value for attribute A.,27,To create a index,Create an index on attribute year for the relation Movie CREATE

17、 INDEX YearIndex ON Movie(year); From Movie, create an index on title and year CREATE INDEX KeyIndex ON Movie(title, year);,28,To delete a index,If we wish to delete the index, we simply use its name in a statement like: DROP INDEX YearIndex; Selection of indexes requires a trade-off by the database

18、 designer The existence of an index on an attribute greatly speeds up queries in which a value for that attribute is specified. On the other hand, ervery index built for an attribute of some relation makes insertions, deletion, and updates to that relation more complex and time-consuming.,29,Views,A

19、 view is a “virtual table” = a relation defined in terms of the contents of other tables and views. Declare by: CREATE VIEW AS ; Antonym: a relation whose value is really stored in the database is called a base table.,30,Example: View Definition,To define a view that is a part of the Movie relation,

20、 specifically, the titles and years of the movies made by Paramount Studio: CREATE VIEW ParamountMovie AS SELECT title, year FROM Movie WHERE studioName = Paramount;,31,Example: Accessing a View,Query a view as if it were a base table. Also: a limited ability to modify views if it makes sense as a m

21、odification of one underlying base table. Example query: SELECT title FROM ParamountMovie WHERE year = 1979;,32,What Happens When a View Is Used?,The SQL system will translate the query on the view ParamountMovie into a query about the base table Movie that has the same effect as our original query.

22、 SELECT title FROM Movie WHERE studioName = Paramount AND year = 1979;,33,Define a query based on views and base tables,Example: SELECT DISTINCT starName FROM ParamountMovie, StarsIn WHERE title = movieTitle AND year = movieYear;,34,Renaming attributes,We can give a views attributes names of our own

23、 choosing. For example: CREATE VIEW MovieProd(movieTitle, prodName) AS SELECT title, name FROM Movie, MovieExec WHERE producerC# = cert#;,35,Delete a view,If a view becomes unuseful, we can delete it. For instance: DROP VIEW ParamountMovie;,36,NULL Values,Tuples in SQL relations can have NULL as a v

24、alue for one or more components. Meaning depends on context. Two common cases: Missing value : e.g., we know Joes Bar has some address, but we dont know what it is. Inapplicable : e.g., the value of attribute spouse for an unmarried person.,37,Two important rules,When we operate on a NULL and any ot

25、her value, including another NULL, using an arithmetic operator like or +, the result is NULL. When we compare a NULL value and any value, including another NULL, using a comparison operator like = or , the result is UNKNOWN. The value UNKNOWN is another truth-value, like TRUE and FALSE.,38,To ask i

26、f x has the value NULL,x IS NULL, this expression have the value TRUE if x has the value NULL and it has FALSE otherwise. x IS NOT NULL, this expression have the value FALSE if x has the value NULL and it has TRUE otherwise,39,Comparing NULLs to Values,The logic of conditions in SQL is really 3-valu

27、ed logic: TRUE, FALSE, UNKNOWN. But a query only produces a tuple in the answer if its truth value for the WHERE clause is TRUE (not FALSE or UNKNOWN).,40,Join Expressions,SQL provides several versions of joins. These expressions can be stand-alone queries or used in place of relations in a FROM clause.,41,Products and Natural Joins,Natural join: R NATURAL JOIN S; Product: R CROSS JOIN S