《数据库英文学习.pptx》由会员分享，可在线阅读，更多相关《数据库英文学习.pptx（45页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1Database ModificationsA modification command does not return a result(as a query does),but changes the database in some way.Three kinds of modifications:1.Insert a tuple or tuples.2.Delete a tuple or tuples.3.Update the value(s)of an existing tuple or tuples.第1页/共45页2InsertionTo insert a single tup
2、le: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);第2页/共45页3Specifying Attributes in INSERTWe may add to the relation name a list of attributes.Two reasons to do so:1.We forget the st
3、andard order of attributes for the relation.2.We dont have values for all attributes,and we want the system to fill in missing components with NULL or a default value.第3页/共45页4Example:Specifying AttributesAnother way to add Sydney Greenstreet to the list of stars of The Maltese Falcon.INSERT INTO St
4、arsIn(movieTitle,movieYear,starName)VALUES(The Maltese Falcon,1942,Sydney GreenStreet);第4页/共45页5Inserting Many TuplesWe may insert the entire result of a query into a relation,using the form:INSERT INTO();第5页/共45页6Example:Insert a SubqueryUsing Studio and Movie,add to the relation Studio all movie s
5、tudios that are mentioned in the relation Movie,but dont appear in Studio.第6页/共45页7SolutionINSERT INTO Studio(name)(SELECT DISTINCT studioName FROM Movie WHERE studioName NOT IN (SELECT name FROM Studio);第7页/共45页8DeletionTo delete tuples satisfying a condition from some relation:DELETE FROM WHERE;第8
6、页/共45页9Example:DeletionDelete from relation StarsIn the fact that Sydney GreenStreet was a star in The Maltese Falcon:DELETE FROM StarsInWHERE movieTitle=The Maltese Falcon AND movieYear=1942 AND starName=Sydney Greenstreet;第9页/共45页10Example:Delete all TuplesMake the relation Likes empty:DELETE FROM
7、 Likes;Note no WHERE clause needed.第10页/共45页11Example:Delete Many TuplesDelete from MovieExec all movie executives whose net worth is low-less than ten million dollars.DELETE FROM MovieExecWHERE netWorth 10000000;第11页/共45页12UpdatesTo change certain attributes in certain tuples of a relation:UPDATE S
8、ET WHERE;第12页/共45页13Example:UpdateModify the relation MovieExec by prepending the title Pres.In front of every movie executives who is the president of a studio:UPDATE MovieExecSET name=Pres.|nameWHERE cert#IN (SELECT presC#FROM Studio);第13页/共45页14Defining a Database SchemaA database schema comprise
9、s 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.第14页/共45页15Creating(Declaring)a RelationSimplest form is:CREATE TABLE ();To delete a relation:DROP TABLE;第1
10、5页/共45页16Elements of Table DeclarationsMost basic element:an attribute and its type.The most common types are:INT or INTEGERREAL or FLOATCHAR(n)fixed-length string of n characters.VARCHAR(n)variable-length string of up to n characters.第16页/共45页17Example:Create TableCREATE TABLE MovieStar(nameCHAR(30
11、),addressVARCHAR(255),genderCHAR(1),birthdateDATE);第17页/共45页18Dates and TimesDATE and TIME are types in SQL.The form of a date value is:DATE yyyy-mm-ddExample:DATE 2004-09-30第18页/共45页19Times as ValuesThe form of a time value is:TIME hh:mm:sswith an optional decimal point and fractions of a second fo
12、llowing.Example:TIME 15:30:02.5第19页/共45页20Modifying relation schemasWe 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 column name;第20页/共45页21Adding AttributesWe may add a new attribu
13、te(“column”)to a relation schema by:ALTER TABLE ADD;Example:ALTER TABLE MovieStar ADD phone CHAR(16);第21页/共45页22Deleting AttributesRemove an attribute from a relation schema by:ALTER TABLE DROP;Example:we dont really need the license attribute for bars:ALTER TABLE MovieStar DROP birthdate;第22页/共45页2
14、3Default valuesWhen 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 in a component if no other value is known.第23页/共45页24ExampleW
15、e 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 CHAR(1)DEFAULT?,birthdate DATE DEFAULT DATE 0000-00-00);第24页/共45页2
16、5IndexesAn 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.第25页/共45页26To create a indexCreate an index on attribute year for the relation MovieCREATE INDEX YearIndex ON Movie(year);From Movie,create an ind
17、ex on title and yearCREATE INDEX KeyIndex ON Movie(title,year);第26页/共45页27To delete a indexIf 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 designerThe existence of an index on an attribute greatl
18、y 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.第27页/共45页28ViewsA view is a“virtual table”=a relation defined in terms of
19、 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.第28页/共45页29Example:View DefinitionTo define a view that is a part of the Movie relation,specifically,the titles and years of the movies made by Par
20、amount Studio:CREATE VIEW ParamountMovie ASSELECT title,yearFROM MovieWHERE studioName=Paramount;第29页/共45页30Example:Accessing a ViewQuery a view as if it were a base table.Also:a limited ability to modify views if it makes sense as a modification of one underlying base table.Example query:SELECT tit
21、leFROM ParamountMovieWHERE year=1979;第30页/共45页31What 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.SELECT titleFROM MovieWHERE studioName=Paramount AND year=1979;第3
22、1页/共45页32Define a query based on views and base tablesExample:SELECT DISTINCT starNameFROM ParamountMovie,StarsInWHERE title=movieTitle AND year=movieYear;第32页/共45页33Renaming attributesWe can give a views attributes names of our own choosing.For example:CREATE VIEW MovieProd(movieTitle,prodName)AS S
23、ELECT title,name FROM Movie,MovieExec WHERE producerC#=cert#;第33页/共45页34Delete a viewIf a view becomes unuseful,we can delete it.For instance:DROP VIEW ParamountMovie;第34页/共45页35NULL ValuesTuples in SQL relations can have NULL as a value for one or more components.Meaning depends on context.Two comm
24、on 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.第35页/共45页36Two important rulesWhen we operate on a NULL and any other value,including another NULL,using an arithmetic operator like or+,t
25、he 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.第36页/共45页37To ask if x has the value NULLx IS NULL,this expression have the value TRUE if x has t
26、he 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 第37页/共45页38Comparing NULLs to ValuesThe logic of conditions in SQL is really 3-valued logic:TRUE,FALSE,UNKNOWN.But a query only produces a tuple in the answer
27、 if its truth value for the WHERE clause is TRUE(not FALSE or UNKNOWN).第38页/共45页39Join ExpressionsSQL provides several versions of joins.These expressions can be stand-alone queries or used in place of relations in a FROM clause.第39页/共45页40Products and Natural JoinsNatural join:R NATURAL JOIN S;Prod
28、uct:R CROSS JOIN S;Example:Likes NATURAL JOIN Serves;Relations can be parenthesized subqueries,as well.第40页/共45页41Theta JoinR JOIN S ON Example:using Movie and StarIn:Movie JOIN StarIn ON title=movieTitle AND year=movieyear;第41页/共45页42OuterjoinsR OUTER JOIN S is the core of an outerjoin expression.I
29、t is modified by:1.Optional NATURAL in front of OUTER.2.Optional ON after JOIN.3.Optional LEFT,RIGHT,or FULL before OUTER.uLEFT=pad dangling tuples of R only.uRIGHT=pad dangling tuples of S only.uFULL=pad both;this choice is the default.第42页/共45页43ExampleMovieStar NATURAL FULL OUTER JOIN MovieExec;Where FULL can be replaced by LEFT or RIGHTMovie FULL OUTER JOIN StarIn ON title=movieTitle AND year=movieYear;Where FULL can be replaced by LEFT or RIGHT第43页/共45页44Exercises第44页/共45页45感谢您的观看！第45页/共45页