资源描述:
《数据库系统英第三版课件衷宜2011版 SQL(6).ppt》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、6TheDatabaseLanguageSQLSQL:StructuredQueryLanguageSQLisavery-high-levellanguage.Say“whattodo”ratherthan“howtodoit.”Avoidalotofdata-manipulationdetailsneededinprocedurallanguageslikeC++orJava.Databasemanagementsystemfiguresout“best”waytoexecutequery.Called“queryoptimization.”6TheDatabaseLangua
2、geSQLSQLandrelationalalgebra:AnimportantcoreofSQLisequivalenttorelationalalgebra.TherearemanyimportantfeaturesofSQLthatgobeyondwhatisfoundinrelationalalgebra,suchasaggregationanddatabaseupdates.DialectsofSQL:TheoriginalANSI(AmericanNationalStandardInstitute)SQL-92orSQL2TheproposedSQL3orSQL-99
3、SQL:20036TheDatabaseLanguageSQLVersionsofSQLproducedbytheprincipalvendorsofdatabasemanagementsystems:AllincludethecapabilitiesofANSIstandard.ConformtoalargeextenttoSQL2IncludesomeofthefeaturesintheSQL3standardEachhasitsvariationsandextensionsMaincontentsofSQL:DQL(select)DML(insert,delete,upda
4、te)DDL(create,drop,alter,...)DCL(grant,revoke,...)6.1SimpleQueriesinSQLSelect-From-Wherestatements:SELECT[ALL
5、DISTINCT]select-listFROMRWHEREcondORDERBY{expression
6、integer}[ASC
7、DESC],…*
8、{column-name
9、expression}[ASalias-name],...6.1SimpleQueriesinSQLMeaningofSingle-RelationQueryBeginwiththerela
10、tionintheFROMclause.ApplytheselectionindicatedbytheWHEREclause.ApplytheextendedprojectionindicatedbytheSELECTclause.6.1SimpleQueriesinSQLAllourSQLquerieswillbebasedonthefollowingdatabaseschema.Underlineindicateskeyattributes.Customer(custid,name,prov,city,phone,company)Merchandise(merid,facto
11、ry,type,spec,price,desc)Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,name,headerid)Salesorder(orderno,signdate,empid,custid)Salesitem(orderno,lineno,merid,unitprice,quantity)6.1.1ProjectioninSQLSELECTclauses:*presentsallattributeswithdefaultorderSELECT*FROMcustomer;Necessary
12、attributescanbelistedinSELECTclausesSELECTcustid,nameFROMcustomer;ThekeywordASandanalias(AScanbeomitted)SELECTcustid,nameAScustnameFROMcustomer;ExpressionSELECTorderno,lineno,unitprice*quantityASaccountFROMsalesitem;ConstantSELECToderno,linen