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

RestrictingandSortingData

ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:LimittherowsretrievedbyaquerySorttherowsretrievedbyaquery

LimitingRowsUsingaSelection“retrieveall

employees

indepartment90”EMPLOYEES…

LimitingtheRowsSelectedRestricttherowsreturnedbyusingtheWHEREclause.TheWHEREclausefollowstheFROMclause.SELECT *|{[DISTINCT]column|expression[alias],...}FROM table[WHERE condition(s)];

UsingtheWHEREClauseSELECTemployee_id,last_name,job_id,department_idFROMemployeesWHEREdepartment_id=90;

CharacterStringsandDatesCharacterstringsanddatevaluesareenclosedinsinglequotationmarks.Charactervaluesarecasesensitive,anddatevaluesareformatsensitive.ThedefaultdateformatisDD-MON-RR.SELECTlast_name,job_id,department_idFROMemployeesWHERElast_name=Whalen;

ComparisonConditionsOperator== = MeaningEqualtoGreaterthanGreaterthanorequaltoLessthanLessthanorequaltoNotequalto

SELECTlast_name,salaryFROMemployeesWHEREsalary=3000;UsingComparisonConditions

OtherComparisonConditionsOperatorBETWEEN

...AND...IN(set)LIKEISNULLMeaningBetweentwovalues(inclusive),

MatchanyofalistofvaluesMatchacharacterpatternIsanullvalue

UsingtheBETWEENConditionUsetheBETWEENconditiontodisplayrowsbasedonarangeofvalues.SELECTlast_name,salaryFROMemployeesWHEREsalaryBETWEEN2500AND3500;LowerlimitUpperlimit

SELECTemployee_id,last_name,salary,manager_idFROMemployeesWHEREmanager_idIN(100,101,201);UsingtheINConditionUsetheINmembershipconditiontotestforvaluesinalist.

UsingtheLIKEConditionUsetheLIKEconditiontoperformwildcardsearchesofvalidsearchstringvalues.Searchconditionscancontaineitherliteralcharactersornumbers:%denoteszeroormanycharacters._denotesonecharacter.SELECT first_nameFROM employeesWHERE first_nameLIKES%;

Youcancombinepattern-matchingcharac