ORACLE课件 第2章 编写简单的SELECT语句

1OracleSQL2SELECTOracle3SELECT:1)234•SQL••SQL••NULL•••DISTINCT•SQL*PLUS/iSQL*PLUS5•EMPLOYEES–employee_id)last_namejob_id()salary•JOBS–job_id()job_title•JOB_GRADES–grade_levellowest_salaryhighest_salary6•departments()–department_iddepartment_name()location_id•locations–location_id()street_adresscity7SQL•SQL(StructuredQueryLanguage)•SQLOracleDB2•SQL:–––––8SQL•DQLSelect•DMLInsert/Update/Delete/Merge•DDLCreate/Alter/Drop/Truncate•DCLGrant/Revoke•TCLCommit/Rollback/Savepoint9SELECT•–“*”–SELECT*|{[DISTINCT]|[][,...]}FROM;SELECT*|{[DISTINCT]|[][,...]}FROM;10•SELECT*FROMdepartments;SELECT*FROMdepartments;11•SELECTdepartment_id,department_name,manager_id,location_idFROMdepartments;SELECTdepartment_id,department_name,manager_id,location_idFROMdepartments;12SELECTdepartment_name,location_idFROMdepartments;SELECTdepartment_name,location_idFROMdepartments;13SQL•SQL–KeywordSQLSELECTFROM–statementSQLSELECT*FROMdepartments;–clauseSQLSELECT*FROMdepartments14SQL•SELECTselectSelect•,•SELECTSELSELECT•••15•+-*/•–––•NUMBER(+-*/)•DATE(+-)16SELECTemployee_id,last_name,salary,salary+400FROMemployees;SELECTemployee_id,last_name,salary,salary+400FROMemployees;SELECTemployee_id,last_name,salary,400+salary*12FROMemployees;SELECTemployee_id,last_name,salary,400+salary*12FROMemployees;SELECTemployee_id,last_name,salary,(400+salary)*12FROMemployees;SELECTemployee_id,last_name,salary,(400+salary)*12FROMemployees;17NULL•NULL•NULL––(||)SELECTlast_name,salary,(400+salary)*12+(400+salary)*12*commission_pctFROMemployees;SELECTlast_name,salary,(400+salary)*12+(400+salary)*12*commission_pctFROMemployees;18•––AS•–––19SELECTemployee_idid,last_nameasemployee_name,salarySalary,(400+salary)*12AnnualSalaryFROMemployees;SELECTemployee_idid,last_nameasemployee_name,salarySalary,(400+salary)*12AnnualSalaryFROMemployees;20•||SELECTfirst_name||''||last_name||'''sphonenumberis'||phone_numberemployeePhonenumberFROMemployees;SELECTfirst_name||''||last_name||'''sphonenumberis'||phone_numberemployeePhonenumberFROMemployees;21DISTINCT•DISTINCTSELECTDISTINCTdepartment_idFROMemployees;SELECTDISTINCTdepartment_idFROMemployees;SELECTDISTINCTdepartment_id,job_idFROMemployees;SELECTDISTINCTdepartment_id,job_idFROMemployees;22SQLSQL*PLUS/iSQL*PLUS•SQLSQL*PLUS/iSQL*PLUS–SQLSQL*PLUS/iSQL*PLUSOracle–SQLOracleSQL*PLUS/iSQL*PLUS–SQL*PLUS/iSQL*PLUS–SQLSQL*PLUS/iSQL*PLUS–SQL*PLUS/iSQL*PLUS;23SQL*PLUS/iSQL*PLUS•DESC[RIBE]–DESCemployees•SET–SET–SETHEADING{ON|OFF}ON•:–colHIREDATEformata100;–colDEPTNOformat999;324SQL*PLUS/iSQL*PLUSSETHEADINGOFFcolEMAILformata20selectEMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAILfromemployees;SETHEADINGONSETHEADINGOFFcolEMAILformata20selectEMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAILfromemployees;SETHEADINGON25•Sql*plus–Datecharacter–Numeric–•iSql*plus––26•SQL••••SQL••NULL•••DISTINCT•SQL*PLUS/iSQL*PLUS27•1.•2.10012•3.first_namelast_name“last_namefirst_name”•4.28