吉林大学数据库技术与Oracle sql-01.ppt

WritingBasic

SQLSELECTStatements

BasicSELECTStatementSELECT *|{[DISTINCT]column|expression[alias],...}FROM table;SELECTidentifieswhatcolumnsFROMidentifieswhichtable

SELECT*FROMdepartments;SelectingAllColumns

SelectingSpecificColumnsSELECTdepartment_id,location_idFROMdepartments;

WritingSQLStatementsSQLstatementsarenotcasesensitive.SQLstatementscanbeononeormorelines.Keywordscannotbeabbreviatedorsplit

acrosslines.Clausesareusuallyplacedonseparatelines.Indentsareusedtoenhancereadability.

ArithmeticExpressionsCreateexpressionswithnumberanddatedatabyusingarithmeticoperators.Operator+-*/ DescriptionAddSubtractMultiplyDivide

UsingArithmeticOperatorsSELECTlast_name,salary,salary+300FROMemployees;…

OperatorPrecedenceMultiplicationanddivisiontakepriorityoveradditionandsubtraction.Operatorsofthesamepriorityareevaluatedfromlefttoright.Parenthesesareusedtoforceprioritizedevaluationandtoclarifystatements.*/+_

OperatorPrecedenceSELECTlast_name,salary,12*salary+100FROMemployees;…

UsingParenthesesSELECTlast_name,salary,12*(salary+100)FROMemployees;…

DefiningaNullValueAnullisavaluethatisunavailable,unassigned,unknown,orinapplicable.Anullisnotthesameaszeroorablankspace.SELECTlast_name,job_id,salary,commission_pctFROMemployees;……

SELECTlast_name,12*salary*commission_pctFROMemployees;NullValues

inArithmeticExpressionsArithmeticexpressionscontaininganullvalueevaluatetonull.……

DefiningaColumnAliasAcolumnalias:RenamesacolumnheadingIsusefulwithcalculationsImmediatelyfollowsthecolumnname-therecanalsobetheoptionalASkeywordbetweenthecolumnnameandaliasRequiresdoublequotationmarksifitcontainsspacesorspecialcharactersoriscasesensitive

UsingColumnAliasesSELECTlast_nameName,salary*12AnnualSalaryFROMemployees;SELECTlast_nameASname,commission_pctcom