资源描述:
《数据库系统英第三版课件2012版 Database Systems(6).ppt》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、6TheDatabaseLanguageSQL6.1SimpleQueriesinSQL6.2QueriesInvolvingMoreThanOneRelation6.3Subqueries6.4Full-RelationOperations6.5DatabaseModifications6.6TransactionsinSQL6TheDatabaseLanguageSQLSQL:StructuredQueryLanguageMaincontentsofSQL:DQL(select)DML(insert,delete,update)DDL(create,drop,alter,...)
2、DCL(grant,revoke,...)6TheDatabaseLanguageSQLSQLisavery-high-levellanguage.Say“whattodo”ratherthan“howtodoit.”Avoidalotofdata-manipulationdetailsneededinprocedurallanguageslikeC++orJava.Databasemanagementsystemfiguresout(计算出)“thebest”waytoexecutequerycalled“queryoptimization(查询优化).”SQLandrelatio
3、nalalgebra:Animportantcore(核心)ofSQLisequivalenttorelationalalgebra.TherearemanyimportantfeaturesofSQLthatgobeyond(超越)whatisfoundinrelationalalgebra,suchasaggregation(聚合)anddatabaseupdates.6TheDatabaseLanguageSQLThreemajorstandardsofSQL:ANSI(AmericanNationalStandardInstitute)SQLSQL-92orSQL2SQL-9
4、9orSQL3VersionsofSQLproducedbytheprincipalDBMSvendors:AllincludethecapabilitiesofANSIstandard.ConformtoalargeextenttoSQL-92IncludesomeofthefeaturesintheSQL-99standardEachhasitsvariationsandextensionsSelect-From-Wherestatements:SELECT[ALL
5、DISTINCT]select-listFROMR[WHEREcond][ORDERBY{expression
6、i
7、nteger}[ASC
8、DESC],…];select-list:*
9、{column-name
10、expression}[ASalias-name],...ASC:ascendingDESC:descending6.1SimpleQueriesinSQL6.1SimpleQueriesinSQLTheorderofreadingqueriesBeginwiththerelationintheFROMclause.ApplytheselectionindicatedbytheWHEREclause.ApplytheextendedprojectionindicatedbytheSELEC
11、Tclause.TheruleofwritingqueriesCaseInsensitivity(大小写不敏感)Endwith“;”6.1SimpleQueriesinSQLAllourSQLquerieswillbebasedonthefollowingdatabaseschema.Customer(custid,name,prov,city,phone,company)Product(prodid,factory,type,spec,price,desc)Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,
12、name,headerid)Salesorder(orderno,signdate,empid,custid)Salesitem(orderno,lineno,prodid,unitprice,quantity)Underlineindicateskeyattributes.6.1.1ProjectioninSQLSELECTclause:*presentsallattributeswithdefaultorderSELECT*FROMcustomer;N