《數(shù)據(jù)庫系統(tǒng)》英文教學(xué)課件

《數(shù)據(jù)庫系統(tǒng)》英文教學(xué)課件,數(shù)據(jù)庫系統(tǒng),數(shù)據(jù)庫,系統(tǒng),英文,教學(xué),課件 SQL:The Query LanguageGuifeng ZhengSchool of SoftwareSUN YAT-SEN UNIVERSITYThe important thing is not tostop questioning.Albert EinsteinReviewRelational Algebra(Operational Semantics操作語義)Given a query,how to mix and match the relational algebra operators to answer itUsed for query optimization用于查詢優(yōu)化Relational Calculus(Declarative Semantics說明性語義)Given a query,what do I want my answer set to include?Algebra and safe calculus are simple and powerful models for query languages for relational modelHave same expressive power有相同的表達力SQL can express every query that is expressible in relational algebra/calculus.(and more)Guifeng Zheng,DBMS,SS/SYSU3RA SQLnSQL RA Projection PnSQL RA Selection snSQL RA Join/crossqComma-separated listnSQL renaming RA rho rnMore ops laternKeep RA in the back of your mindSELECTWHEREFROMRelational Query LanguagesSQL QueryRel.Algebra Query 1Rel.Algebra Query 2Rel.Algebra Query n.Pick the cheapest one查詢分析Relational Query LanguagesnTwo sublanguages:qDDL Data Definition定義 LanguagenDefine and modify schema(at all 3 levels)qDML Data Manipulation操作 LanguagenQueries can be written intuitively.nDBMS is responsible for efficient evaluation.qThe key:precise semantics for relational queries.qOptimizer can re-order operationsnWont affect query answer.qChoices driven by“cost model”成本模型The SQL Query LanguagenThe most widely used relational query language.nStandardized(although most systems add their own“special sauce”-including PostgreSQL)nWe will study SQL92-a basic subsetExample Databasesidsnameratingage1Fred7222Jim2393Nancy827Sailorssidbidday11029/1221029/13Reservesbidbnamecolor101Ninared102Pintablue103Santa MariaredBoatsThe SQL DDLCREATE TABLE Sailors(sid INTEGER,sname CHAR(20),rating INTEGER,age REAL,PRIMARY KEY sid);CREATE TABLE Boats(bid INTEGER,bname CHAR(20),color CHAR(10)PRIMARY KEY bid);CREATE TABLE Reserves(sid INTEGER,bid INTEGER,day DATE,PRIMARY KEY(sid,bid,day),FOREIGN KEY sid REFERENCES Sailors,FOREIGN KEY bid REFERENCES Boats);sidsnameratingage1Fred7222Jim2393Nancy827bidbnamecolor101Ninared102Pintablue103Santa Mariaredsidbidday11029/1221029/13FOREIGN KEY 外鍵 The SQL DMLnFind all 18-year-old sailors:SELECT*FROM Sailors SWHERE S.age=18 To find just names and ratings,replace the first line:SELECT S.sname,S.ratingsidsnameratingage1Fred7222Jim2393Nancy827Sailors Querying Multiple RelationsSELECT S.snameFROM Sailors S,Reserves RWHERE S.sid=R.sid AND R.bid=102sidsnameratingage1Fred7222Jim2393Nancy827Sailorssidbidday11029/1221029/13ReservesCross ProductNatural JoinBasic SQL QuerySELECT DISTINCT target-listFROM relation-listWHERE qualificationrelation-list:List of relation names,possibly with a range-variable after each nametarget-list:List of expressions over attributes of tables in relation-listDISTINCT:optional.Answer should not contain duplicates.SQL default:duplicates are not eliminated!(Result a“multiset”)qualification:Comparisons combined using AND,OR and NOT.Comparisons are Attr op const or Attr1 op Attr2,where op is one of,etc.1.FROM:compute cross product of tables.2.WHERE:Check conditions,discard tuples that fail.3.SELECT:Delete unwanted fields.4.DISTINCT(optional):eliminate duplicate rows.Note:Probably the least efficient way to compute a query!qQuery optimizer will find more efficient ways to get the same answer.Query SemanticsSELECT DISTINCT target-listFROM relation-listWHERE qualificationGuifeng Zheng,DBMS,SS/SYSU13SQL Query SemanticsParallel assignment all tuplesDoesnt impose any orderAnswer=for all assignments x1 in R1,xn in Rn do if Conditions then Answer=Answer (a1,ak)return AnswerSELECT a1,a2,akFROM R1 AS x1,R2 AS x2,Rn AS xnWHERE ConditionsGuifeng Zheng,DBMS,SS/SYSU14SQL Query SemanticsNested loops:Answer=for x1 in R1 do for x2 in R2 do .for xn in Rn do if Conditions then Answer=Answer (a1,ak)return AnswerSELECT a1,a2,akFROM R1 AS x1,R2 AS x2,Rn AS xnWHERE ConditionsFind sailors whove reserved at least one boatnWould DISTINCT make a difference here?nWhat is the effect of replacing S.sid by S.sname in the SELECT clause?qWould DISTINCT make a diff to this variant of the query?S.sidSailors S,Reserves RS.sid=R.sidSELECTFROMWHEREAbout Range VariablesnNeeded when ambiguity could arise.qe.g.,same table used multiple times in FROM (“self-join”)SELECT x.sname,x.age,y.sname,y.ageFROM Sailors x,Sailors yWHERE x.age y.agesidsnameratingage1Fred7222Jim2393Nancy827Sailors xsidsnameratingage1Fred7222Jim2393Nancy827Sailors yArithmetic ExpressionsSELECT S.age,S.age-5 AS age1,2*S.age AS age2FROM Sailors SWHERE S.sname=dustinSELECT S1.sname AS name1,S2.sname AS name2FROM Sailors S1,Sailors S2WHERE 2*S1.rating=S2.rating-1String Comparisons _ stands for any one character and%stands for 0 or more arbitrary characters.SELECT S.snameFROM Sailors SWHERE S.sname LIKE B_%BFind sids of sailors whove reserved a red or a green boatSELECT R.sidFROM Boats B,Reserves RWHERE R.bid=B.bid AND (B.color=red OROR B.color=green)SELECT R.sidFROM Boats B,Reserves RWHERE R.bid=B.bid AND B.color=red UNIONUNION SELECT R.sidFROM Boats B,Reserves RWHERE R.bid=B.bid AND B.color=green.or:SELECT R.sidFROM Boats B,Reserves RWHERE R.bid=B.bid AND (B.color=red ANDAND B.color=green)Find sids of sailors whove reserved a red and a green boatFind sids of sailors whove reserved a red and a green boatSELECT S.sidFROM Sailors S,Boats B,Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=redINTERSECTINTERSECTSELECT S.sidFROM Sailors S,Boats B,Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=greenCould use a self-join:SELECT R1.sidFROM Boats B1,Reserves R1,Boats B2,Reserves R2WHERE R1.sid=R2.sidR1.sid=R2.sid AND R1.bid=B1.bid AND R2.bid=B2.bid AND(B1.color=red ANDAND B2.color=green)Find sids of sailors whove reserved a red and a green boatFind sids of sailors who have not reserved a boatSELECT S.sidFROM Sailors SEXCEPTEXCEPTSELECT S.sidFROM Sailors S,Reserves RWHERE S.sid=R.sid Nested Queries:INSELECT S.snameFROM Sailors SWHERE S.sid ININ (SELECT R.sid FROM Reserves R WHERE R.bid=103)Names of sailors whove reserved boat#103:SELECT S.snameFROM Sailors SWHERE S.sid NOT INNOT IN (SELECT R.sid FROM Reserves RWHERE R.bid=103)Names of sailors whove not reserved boat#103:Nested Queries:NOT INNested Queries with CorrelationnSubquery must be recomputed for each Sailors tuple.qThink of subquery as a function call that runs a querySELECT S.snameFROM Sailors SWHERE EXISTSEXISTS (SELECT *FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid)Names of sailors whove reserved boat#103:More on Set-Comparison Operatorsnweve seen:IN,EXISTSncan also have:NOT IN,NOT EXISTSnother forms:op ANY,op ALLnFind sailors whose rating is greater than that of some sailor called Horatio:SELECT*FROM Sailors SWHERE S.rating ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.sname=Horatio)Conceptual SQL EvaluationSELECT DISTINCT target-listFROM relation-listWHERE qualificationGROUP BY grouping-listHAVING group-qualificationSELECTRelation cross-product Apply selections(eliminate rows)Project away columns(just keep those used in SELECT,GBY,HAVING)WHEREFROMGROUP BYForm groups&aggregateHAVINGEliminate groupsDISTINCTEliminate duplicatesSorting the Results of a QuerynORDER BY column ASC|DESC,.nCan order by any column in SELECT list,including expressions or aggs:SELECT S.rating,S.sname,S.ageFROM Sailors S,Boats B,Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=redORDER BY S.rating,S.sname;SELECT S.sid,COUNT(*)AS redrescntFROM Sailors S,Boats B,Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=redGROUP BY S.sidORDER BY redrescnt DESC;Null ValuesnField values are sometimes unknown(e.g.,a rating has not been assigned)or inapplicable(e.g.,no spouses name).qSQL provides a special value null for such situations.nThe presence of null complicates many issues.E.g.:qSpecial operators needed to check if value is/is not null.qIs rating8 true or false when rating is equal to null?What about AND,OR and NOT connectives?qWe need a 3-valued logic (true,false and unknown).Joins Explicit join semantics needed unless it is an INNER join(INNER is default)SELECT(column_list)FROM table_name INNER|LEFT|RIGHT|FULL OUTER JOIN table_name ON qualification_listWHERE Inner JoinOnly rows that match the qualification are returned.SELECT s.sid,s.name,r.bidFROM Sailors s INNER JOIN Reserves rON s.sid=r.sidReturns only those sailors who have reserved boats.SELECT s.sid,s.name,r.bidFROM Sailors s INNER JOIN Reserves rON s.sid=r.sid Left Outer JoinReturns all matched rows,plus all unmatched rows from the table on the left of the join clause(use nulls in fields of non-matching tuples)SELECT s.sid,s.name,r.bidFROM Sailors s LEFT OUTER JOIN Reserves rON s.sid=r.sidSELECT s.sid,s.name,r.bidFROM Sailors s LEFT OUTER JOIN Reserves rON s.sid=r.sid Right Outer JoinRight Outer Join returns all matched rows,plus all unmatched rows from the table on the right of the join clauseSELECT r.sid,b.bid,b.nameFROM Reserves r RIGHT OUTER JOIN Boats bON r.bid=b.bidSELECT r.sid,b.bid,b.nameFROM Reserves r RIGHT OUTER JOIN Boats bON r.bid=b.bid Full Outer JoinFull Outer Join returns all(matched or unmatched)rows from the tables on both sides of the join clause SELECT r.sid,b.bid,b.nameFROM Reserves r FULL OUTER JOIN Boats bON r.bid=b.bidSELECT r.sid,b.bid,b.nameFROM Reserves r FULL OUTER JOIN Boats bON r.bid=b.bid Note:in this case it is the same as the ROJ!bid is a foreign key in reserves,so all reservations musthave a corresponding tuple in boats.Views:Defining External DB SchemasCREATE VIEW view_nameAS select_statementMakes development simplerOften used for securityNot“materialized”CREATE VIEW RedsAS SELECT B.bid,COUNT(*)AS scount FROM Boats B,Reserves R WHERE R.bid=B.bid AND B.color=red GROUP BY B.bidSELECT bname,scount FROM Reds R,Boats B WHERE R.bid=B.bidAND scount 10RedsCREATE VIEW RedsAS SELECT B.bid,COUNT(*)AS scount FROM Boats B,Reserves R WHERE R.bid=B.bid AND B.color=red GROUP BY B.bidViews Instead of Relations in Queries Discretionary Access Control GRANT privileges ON object TO users WITH GRANT OPTIONObject can be a Table or a ViewPrivileges can be:SelectInsertDeleteReferences(cols)allow to create a foreign key that references the specified column(s)AllCan later be REVOKEdUsers can be single users or groupsSee Chapter 17 for more details.Two more important topicsnConstraintsnSQL embedded in other languagesIntegrity Constraints(Review)nAn IC describes conditions that every legal instance of a relation must satisfy.qInserts/deletes/updates that violate ICs are disallowed.qCan ensure application semantics(e.g.,sid is a key),or prevent inconsistencies(e.g.,sname has to be a string,age must be=1 AND rating=10)CREATE TABLE Reserves(sname CHAR(10),bid INTEGER,day DATE,PRIMARY KEY (bid,day),CONSTRAINT noInterlakeResCHECK (Interlake(SELECT B.bnameFROM Boats BWHERE B.bid=bid)Slide 46DBMSuserAd hocqueryresultStored procedurequeryresultEmbedded SQLC/Java programresultSQLDatabase API:ODBC,JDBC,PHPresultWriting Applications with SQLnSQL is not a general purpose programming language.+Tailored for data retrieval and manipulation+Relatively easy to optimize and parallelize-Cant write entire apps in SQL aloneOptions:Make the query language“Turing complete”Avoids the“impedance mismatch”but,loses advantages of relational language simplicityAllow SQL to be embedded in regular programming languages.Q:What needs to be solved to make the latter approach work?Embedded SQLnDBMS vendors traditionally provided“host language bindings”qE.g.for C or COBOLqAllow SQL statements to be called from within a programqTypically you preprocess your programs qPreprocessor generates calls to a proprietary DB connectivity librarynGeneral patternqOne call to connect to the right database(login,etc.)qSQL statements can refer to host variables from the languagenTypically vendor-specificqWe wont look at any in detail,well look at standard stuffnProblemqSQL relations are(multi-)sets,no a priori bound on the number of records.No such data structure in C.qSQL supports a mechanism called a cursor to handle this.Slide 49Why is cursor needed?hostprogramDBMSEmbedded SQLresultcursorCursor bridges the gap between value-orientedhost program and set-oriented DBMSA program variablecan hold one valueat a timeSlide 50Example Embedded SQLFrom within a host language,find the names and account numbers of customers with more than the variable amount dollars in some account.nSpecify the query in SQL and declare a cursor for it EXEC SQLdeclare c cursor forselect customer-name,account-numberfrom depositor,accountwhere depositor.account-number=account.account-number and account.balance :amountEND-EXEC Database APIs:Alternative to embeddingnRather than modify compiler,add a library with database calls(API)qspecial objects/methodsqpasses SQL strings from language,presents result sets in a language-friendly wayqODBC a C/C+standard started on WindowsqJDBC a Java equivalentqMost scripting languages have similar thingsnE.g.For Perl there is DBI,“oraPerl”,other packagesnMostly DBMS-neutral qat least try to hide distinctions across different DBMSsArchitecturenA lookup service maps“data source names”(“DSNs”)to drivers qTypically handled by OSnBased on the DSN used,a“driver”is linked into the app at runtimenThe driver traps calls,translates them into DBMS-specific codenDatabase can be across a networknODBC is standard,so the same program can be used(in principle)to access multiple database systemsnData source may not even be an SQL database!Application ODBC driverData SourceODBC/JDBCnVarious vendors provide drivers qMS bundles a bunch into WindowsqVendors like DataDirect and OpenLink sell drivers for multiple OSesnDrivers for various data sourcesqRelational DBMSs(Oracle,DB2,SQL Server,etc.)q“Desktop”DBMSs(Access,Dbase,Paradox,FoxPro,etc.)qSpreadsheets(MS Excel,Lotus 1-2-3,etc.)qDelimited text files(.CSV,.TXT,etc.)nYou can use JDBC/ODBC clients over many data sourcesqE.g.MS Query comes with many versions of MS Office(msqry32.exe)nCan write your own Java or C+programs against xDBCJDBCnPart of Java,easy to usenJava comes with a JDBC-to-ODBC bridgeqSo JDBC code can talk to any ODBC data sourceqE.g.look in your Windows Control Panel or MacOS Utilities folder for JDBC/ODBC drivers!nJDBC tutorial onlineqhttp:/ 200656hello.phpnhttp:/pages.stern.nyu.edu/mjohnson/dbms/php/hello.phpnQ:What the difference between and n?Hello from PHPHere comes the PHP part:?php print Hello,World!n;?Thats it!M.P.Johnson,DBMS,Stern/NYU,Spring 200657PHP varsnNames always start with$qhttp:/pages.stern.nyu.edu/mjohnson/dbms/php/math.php?$num1=58;$num2=67;print First number .$num1.;print Second number .$num2.;$total=$num1+$num2;print The sum is .$total.;?M.P.Johnson,DBMS,Stern/NYU,Spring 200658Combining PHP and HTMLnhttp:/pages.stern.nyu.edu/mjohnson/dbms/php/combine.php?php for($z=0;$z Iteration number M.P.Johnson,DBMS,Stern/NYU,Spring 200659PHP&MySQL1.Open a connection and open our DB:2.Run query:$db=mysql_connect(“l(fā)ocalhost,user,pass);mysql_select_db(test,$db);$result=mysql_query($query,$db);M.P.Johnson,DBMS,Stern/NYU,Spring 200660PHP&MySQL3.Extract next row of data from the results:qWhat this means:myrow is an array that can then be accessedqOther options,see codenIn general,to scroll through results,do:$myrow=mysql_fetch_row($result)while($myrow=mysql_fetch_row($result)#print rows dataAPI SummaryAPIs are needed to interface DBMSs to programming languagesnEmbedded SQL uses“native drivers”and is usually faster but less standardnODBC(used to be Microsoft-specific)for C/C+nJDBC the standard for JavanScripting languages(PHP,Perl,JSP)are becoming the preferred technique for web-based systemsSummarynRelational model has well-defined query semanticsnSQL provides functionality close to basic relational model(some differences in duplicate handling,null values,set operators,)nTypically,many ways to write a queryqDBMS figures out a fast way to execute a query,regardless of how it is written.Guifeng Zheng,DBMS,SS/SYSU63ReviewnExamples from SELECT LFROM R1,RnWHERE CPL(sC(R1 x Rn)Guifeng Zheng,DBMS,SS/SYSU64Another complex examplenPeople(ssn,name,street,city,state,state)nQ:Who lives on Georges street?nA:First,generate pairs of(renamed)people:qrp1(People)x rp2(People)nThen pick out pairs with George:qsp1.name=George(rp1(People)x rp2(People)nAnd refine to rows with George and someone else:qsp1.name=George AND p1.namep2.name(rp1(People)x rp2(People)nFinally,project out the names:qPp2.name(sp1.name=George AND p1.namep2.name(rp1(People)x rp2(People)Guifeng Zheng,DBMS,SS/SYSU65Live examplesnQ:produce a list of employees and their bossesqWhat if no boss?Or no subordinate?nJoins on emp,emp man:qComma-basedqInnerqNaturalqCrossqOuter left,right,fullGuifeng Zheng,DBMS,SS/SYSU66More live examplesnInner joins require an ON clauseqLike a where clauseqArbitrary boolean expressionqIf always true(1=1),reduces to cross joinnNew compar op:BETWEENqa between 5 and 10 a=5 and a=10nQ:produce a list of employees with their salary gradesqemp,salgrade