【正文】 LAST_NAME LIKE 39。a39。SELECT LENGTHB(39。SELECT LAST_NAMEFROM EMPLOYEESORDER BY LAST_NAME DESC。39。,39。King39。查詢employees表中所有員工的last_name,job_id,salary,年薪(salary*12)SELECT LAST_NAME,JOB_ID,SALARY,SALARY*12FROM EMPLOYEES。連接符：||SELECT FIRST_NAME||39。011月9739。查詢employees表中倒數(shù)第三個字符為o的員工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。查詢employees表中l(wèi)ast_name中包含A（不區(qū)分大小寫）的員工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。SELECT LAST_NAMEFROM EMPLOYEESWHERE LOWER(LAST_NAME) LIKE 39。SELECT LAST_NAME,INSTR(LAST_NAME,39。SELECT TRIM(39。SELECT LAST_NAMEFROM EMPLOYEESWHERE INSTR(LAST_NAME,39。SELECT NEXT_DAY(SYSDATE,39。)FROM DUAL。D39。FM$9,999,39。TO_DATE(C,M)：根據(jù)模板M將字符類型C值轉(zhuǎn)換為日期類型的值SELECT TO_DATE(39。),TO_NUMBER(39。IT_PROG39。ST_CLERK39。) AS HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 工齡,CASEWHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)15 AND TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)=18 THEN SALARY*8WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)18 AND TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)=20 THEN SALARY*10WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)20 THEN SALARY*15ELSE 0END AS 獎金FROM EMPLOYEES。MAX()/MIN():求最大值/最小值SELECT MAX(SALARY),MIN(SALARY)FROM EMPLOYEES。FML99,39。SELECT 4FROM 1[WHERE] 2[GROUP BY] 3[ORDER BY] 5 查詢每個部門的最高的工資SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_IDORDER BY 1 ASC。),COUNT(*)FROM EMPLOYEESGROUP BY TO_CHAR(HIRE_DATE,39。查詢?nèi)藬?shù)最多的部門的department_id與人數(shù)SELECT DEPARTMENT_ID,COUNT(*)FROM EMPLOYEESGROUP BY DEPARTMENT_IDHAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)。FM99,39。集合運(yùn)算：并集，交集，補(bǔ)集并集：集合+集合查詢收入最高與收入最低的員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES)UNIONSELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY=(SELECT MIN(SALARY) FROM EMPLOYEES)ORDER BY 2 ASC。CREATE TABLE T1ASSELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=50。CC39。：NOT NULL 。,39。Java39。INSERT INTO T1 VALUES(39。)。INSERT INTO T5 VALUES(10,39。/*SQL語句的分類：3種：(數(shù)據(jù)定義語言)：CREATE,ALTER,DROP等(數(shù)據(jù)操縱語言)：SELECT,INSERT,UPDATE,DELETE(數(shù)據(jù)控制語言)：COMMIT,ROLLBACK,GRANT,SAVEPOINT等4種：DQL(數(shù)據(jù)查詢語言)：SELECT5種：TCL(事務(wù)控制語言)：COMMIT,ROLLBACK,SAVEPOINT*//*視圖：。手動創(chuàng)建索引：CREATE INDEX 索引名ON 表名(列名,列名,……)。/聲明變量：變量名 [CONSTANT] 數(shù)據(jù)類型[(長度 )]所有數(shù)據(jù)類型的變量都默認(rèn)為NULL.DECLARE V_NUM CONSTANT NUMBER(5):=10。 V_SALARY %TYPE。人39。END。BEGIN SELECT COUNT(*) INTO V_COUNT FROM EMPLOYEES WHERE DEPARTMENT_ID=V_DEPTID。||V_DATE)。)。)。UPDATE T5 SET T_SEX=39。)。男39。,5000,10)。INSERT INTO S VALUES(1,39。INSERT INTO T2 VALUES(1,39。男39。INSERT INTO T1(T_ID,T_NAME) VALUES(2,39。VARCHAR2(10):AB,保存可變長度的字符串。YYYYMMDD39。MySQLSELECT LAST_NAME,SALARYFROM EMPLOYEESORDER BY SALARY DESCLIMIT 5。查詢出入職最早的員工的last_name,hire_date(格式為：199911)SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。%REP%39。)39。創(chuàng)建一個查詢顯示雇員的 last_name 并帶星號顯示他們的月薪，每個星號表示1000美圓。SQL Server:*左外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE *=。此員工不在此次活動范圍內(nèi)39。 THEN SALARY*2ELSE SALARYEND AS 增加后工資FROM EMPLOYEES。沒有傭金39。),TO_NUMBER(39。 查詢employees表中所有員工的last_name與員工3倍的工資（格式為：$50,）,結(jié)果按工資的降序排序。39。星期三39。YYYY39。查詢employees表中所有員工的last_name,hire_date,入職年數(shù)SELECT LAST_NAME,HIRE_DATE,TRUNC((SYSDATEHIRE_DATE)/365)FROM EMPLOYEES。查詢employees表中l(wèi)ast_name中包含a或e的員工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。)將第一個參數(shù)中出現(xiàn)的第二個參數(shù)用第三個參數(shù)替換掉FROM EMPLOYEES。SELECT LAST_NAME,SUBSTR(LAST_NAME,3)截取字符串：從第N位開始截取一直截取到最后FROM EMPLOYEES。NULLS FIRST/NULLS LAST單行函數(shù)：SELECT LAST_NAME,UPPER(LAST_NAME)將參數(shù)的值變?yōu)榇髮慒ROM EMPLOYEES。AND,OR,NOT查詢employees表中工資在9000到12000的員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY BETWEEN 9000 AND 12000。SA_REP39。SELECT LAST_NAME,HIRE_DATEFROM EMPLOYEESWHERE HIRE_DATE=39。DHEE39。 AS 名字,39。076月9439。)。SELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY=9000 AND SALARY=12000。SELECT LAST_NAME,LOWER(LAST_NAME)將參數(shù)的值變?yōu)樾慒ROM EMPLOYEES。SELECT LAST_NAME,SUBSTR(LAST_NAME,3,2)截取字符串：從第N位開始截取，截取指定長度的字符串FROM EMPLOYEES。SELECT TRIM(39。%a%39。MONTHS_BETWEEN(D1,D2):求兩個參數(shù)相差的月數(shù)SELECT LAST_NAME,HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 年數(shù)FROM EMPLOYEES。)FROM EMPLOYEES。SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。), TO_CHAR(,39。SELECT LAST_NAME,TO_CHAR(SALARY*3,39。2,39。)FROM EMPLOYEES。SELECT LAST_NAME,JOB_ID,SALARY,DECODE( JOB_ID, 39。END AS 獎金FROM EMPLOYEES。右外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE =*。按薪水降序排序數(shù)據(jù)。199739。GROUP BY JOB_IDHAVING MAX(SALARY)=6000。FMYYYYMMDD39。查詢?nèi)肼氉钔淼?名員工的last_name,hire_date(格式為:19991010),salary(格式為：50,)SELECT LAST_NAME,HIRE_DATE,SALARYFROM (SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。),DEPARTMENT_IDFROM EMPLOYEESWHERE (NVL(DEPARTMENT_ID,0),HIRE_DATE) IN (SELECT NVL(DEPARTMENT_ID,0),MIN(HIRE_DATE) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)。VARCHAR與VARCHAR2的區(qū)別？*/日期類型：不能寫長度/*DATE:日期+時間*/DROP TABLE T1。BB39。刪除T1表中T_NAME列中所有的數(shù)據(jù)UPDATE T1 SET T_NAME=NULL。AA39。AA39。INSERT INTO T1 VALUES(39。)。INSERT INTO T5 VALUES(9,39。女39。索引：提高查詢的效率。END。END。 (V_DEPTID||39。/
。||V_COUNT||39。 V_NAME %TYPE。END。/*自動創(chuàng)建索引：當(dāng)表有主鍵或唯一約束時， Oracle會自動為列添加唯一的索引。a39。)。女39。,3000,30)。INSERT INTO C VALUES(1,39。男39。在表中標(biāo)識出唯一的一行數(shù)據(jù)(記錄，元組)。INSERT INTO T1(T_NAME,T_ID) VALUES(39。復(fù)制表：/*CREATE TABLE 表名ASSELECT語句*/DROP TABLE T1。SELECT ,FROM EMPLOYEES EMP, (SELECT DEPARTMENT_ID,AVG(SALARY) AS SALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID) AWHERE = AND 。) AS HIRE_DATE,TO_CHAR(SALARY,3