【正文】 SELECT LAST_NAMEFROM EMPLOYEESWHERE LOWER(LAST_NAME) LIKE 39。NULLS FIRST/NULLS LAST單行函數(shù)：SELECT LAST_NAME,UPPER(LAST_NAME)將參數(shù)的值變?yōu)榇髮慒ROM EMPLOYEES。SELECT LAST_NAME,HIRE_DATEFROM EMPLOYEESORDER BY HIRE_DATE DESC。查詢employees表中工資在500012000之間，并且在20或50號部門工作的員工的last_name,salary,department_idSELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE SALARY BETWEEN 5000 AND 12000 AND DEPARTMENT_ID IN (20,50)。查詢employees表中l(wèi)ast_name中包含A（不區(qū)分大小寫）的員工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。AND,OR,NOT查詢employees表中工資在9000到12000的員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY BETWEEN 9000 AND 12000。IS NULL:NULL,0,39。查詢employees表中job_id中包含A_的員工的last_name,job_idSELECT LAST_NAME,JOB_IDFROM EMPLOYEESWHERE JOB_ID LIKE 39。查詢employees表中倒數(shù)第三個字符為o的員工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。SA_REP39。IT_PROG39。 AND 39。011月9739。SELECT LAST_NAME,HIRE_DATEFROM EMPLOYEESWHERE HIRE_DATE=39。查詢employees表中king員工的employee_id,salary,department_idSELECT EMPLOYEE_ID,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE LAST_NAME=39。SELECT DISTINCT JOB_ID,SALARYFROM EMPLOYEES。連接符：||SELECT FIRST_NAME||39。DHEE39。算術(shù)運(yùn)算符：+，，*，/+：只有加法運(yùn)算的功能，沒有連接作用/：SELECT 5/2FROM DUAL。Oracle是甲骨文公司大型關(guān)系數(shù)據(jù)庫systemdhee://IP:5560/isqlplus導(dǎo)入數(shù)據(jù)庫腳本：腳本路徑所有的關(guān)系數(shù)據(jù)對表的操作使用的都是SQL語句查詢表使用SQL語句中的SELECT語句基本SELECT語句：SELECT 列名,列名,……|*|算術(shù)表達(dá)式FROM 表名查詢employees表中所有員工的last_name,salarySELECT LAST_NAME,SALARY FROM EMPLOYEES。查詢employees表中所有員工的last_name,job_id,salary,年薪(salary*12)SELECT LAST_NAME,JOB_ID,SALARY,SALARY*12FROM EMPLOYEES。 AS 名字,39。.39。過濾與排序過濾：WHERE子句SELECTFROM[WHERE 條件]。King39。076月9439。BETWEEN...AND...:使列大于等于一個值，并且小于等于另一個值（包含邊界值）查詢employees表中工資在9000到12000的員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY BETWEEN 9000 AND 12000。3112月9739。,39。)。%o__39。%A/_%39。39。SELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY=9000 AND SALARY=12000。%A%39。SELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE (SALARY=5000 AND SALARY=12000) AND (DEPARTMENT_ID=20 OR DEPARTMENT_ID=50)。SELECT LAST_NAMEFROM EMPLOYEESORDER BY LAST_NAME DESC。SELECT LAST_NAME,LOWER(LAST_NAME)將參數(shù)的值變?yōu)樾慒ROM EMPLOYEES。%a%39。SELECT LAST_NAME,LENGTH(LAST_NAME)FROM EMPLOYEES。SELECT LENGTHB(39。SELECT LAST_NAME,SUBSTR(LAST_NAME,3,2)截取字符串：從第N位開始截取，截取指定長度的字符串FROM EMPLOYEES。a39。),RPAD(LAST_NAME,20,39。a39。SELECT TRIM(39。A39。查詢employees表中所有員工的last_name,要求顯示的內(nèi)容中首字母為小寫，其它字母均為大寫SELECT LOWER(SUBSTR(LAST_NAME,1,1))||UPPER(SUBSTR(LAST_NAME,2))FROM EMPLOYEES。查詢employees表中l(wèi)ast_name中為4個字符的員工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。%a%39。a39。四舍五入：SELECT ROUND(),ROUND(,2),ROUND(,0),ROUND(,1)FROM DUAL。SELECT CEIL()FROM DUAL。MONTHS_BETWEEN(D1,D2):求兩個參數(shù)相差的月數(shù)SELECT LAST_NAME,HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 年數(shù)FROM EMPLOYEES。星期四39。SELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=39。YYYYMMDD DY39。)FROM EMPLOYEES。SELECT TO_CHAR(SYSDATE,39。FMYYYY年MM月DD日 DY39。)FROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,39。SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。)=39。), TO_CHAR(,39。), TO_CHAR(,39。), TO_CHAR(,39。), TO_CHAR(,39。SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。)=39。SELECT LAST_NAME,TO_CHAR(SALARY*3,39。1999101039。SELECT TO_DATE(39。)SYSDATEFROM DUAL。2,39。$2,39。當(dāng)NULL參與到算術(shù)運(yùn)算時結(jié)果一定為NULLNVL():當(dāng)?shù)谝粋€參數(shù)不為NULL時，返回第一個參數(shù)的值，當(dāng)?shù)谝粋€參數(shù)為NULL時，返回第二個參數(shù)的值，兩個參數(shù)的類型必須一致。NVL2():當(dāng)?shù)谝粋€參數(shù)不為NULL，返回第二個參數(shù)，當(dāng)?shù)谝粋€參數(shù)為NULL，返回第三個參數(shù)SELECT LAST_NAME,NVL2(COMMISSION_PCT,39。)FROM EMPLOYEES。 THEN SALARY*WHEN JOB_ID=39。 THEN SALARY*2ELSE SALARYEND AS 增加后工資FROM EMPLOYEES。ST_CLERK39。SELECT LAST_NAME,JOB_ID,SALARY,DECODE( JOB_ID, 39。, SALARY*, 39。查詢所有員工的last_name,department_nameSELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES,DEPARTMENTSWHERE =。/*查詢employees表中所有員工的last_name,hire_date(格式為:19991010),工齡(只保留整數(shù)部分),以及獎金，如果員工的工齡在15（不包含15年）年以上，獎金為8倍的工資，如果員工的工齡大于18（不包含18年）年以上，獎金為10倍的工資，如果員工的工齡在20（不包含20年）年以上，獎金為15倍的工資，如果員工的工齡小于或等于15年，則沒有獎金顯示此員工不在此次活動范圍內(nèi).*/SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。END AS 獎金FROM EMPLOYEES。查詢員工的last_name,job_title,department_name,citySELECT ,FROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC,JOBS JOBWHERE = AND = AND =。查詢員工的last_name,job_title,department_name,citySELECT ,FROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC,JOBS JOBWHERE = AND = AND =。SELECT ,FROM EMPLOYEES EMP FULL OUTER JOIN DEPARTMENTS DEPTON =。右外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE =*。SELECT MAX(HIRE_DATE),MIN(HIRE_DATE)FROM EMPLOYEES。SELECT COUNT(COMMISSION_PCT)返回指定列中不為NULL的值的個數(shù)FROM EMPLOYEES。Toronto39。按薪水降序排序數(shù)據(jù)。),FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON = JOIN LOCATIONS LOC ON = WHERE =90。,39。3112月9739。199739。SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_ID。查詢每個部門的人數(shù)，顯示department_id,department_name與人數(shù)SELECT ,COUNT(*)FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON =GROUP BY ,ORDER BY 1 ASC。/*WHERE與HAVING的區(qū)別：，HAVING過濾的是分組的結(jié)果，HAVING可以使用組函數(shù)*/查詢平均工資大于10000的部門編號與平均工資，結(jié)果按工資降序排序SELECT DEPARTMENT_ID,AVG(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_IDHAVING AVG(SALARY)10000ORDER BY AVG(SALARY) DESC。GROUP BY JOB_IDHAVING MAX(SALARY)=6000。YYYY39。查詢?nèi)藬?shù)大于或等于3人的部門的department_id與人數(shù)SELECT DEPARTMENT_ID,COUNT(*)FROM EMPLOYEESGROUP BY DEPARTMENT_IDHAVING COUNT(*)=3。查詢哪些員工的工資大于104號員工的工資，顯示這些員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY(SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=104)。FMYYYYMMDD39。多行子查詢：IN,ANY,ALL查詢工資至少大于60號部門一名員工的工資的員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY IN (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=