【正文】 END。 39。員工編號(hào)。人39。BEGIN SELECT COUNT(*) INTO V_COUNT FROM EMPLOYEES WHERE DEPARTMENT_ID=V_DEPTID。END。 39。 V_SALARY %TYPE。||V_DATE)。 (V_NUM||39。BEGIN V_NUM:=1。/聲明變量：變量名 [CONSTANT] 數(shù)據(jù)類型[(長度 )]所有數(shù)據(jù)類型的變量都默認(rèn)為NULL.DECLARE V_NUM CONSTANT NUMBER(5):=10。)。我的第一個(gè)PL/SQL塊39。BEGIN (39。手動(dòng)創(chuàng)建索引：CREATE INDEX 索引名ON 表名(列名,列名,……)。)。NEXTVAL:獲得序列的下一個(gè)值，每執(zhí)行一次序列的值都會(huì)改變，并且無法回滾。 在數(shù)據(jù)庫啟動(dòng)時(shí)將N個(gè)序列的下一個(gè)值放入緩存中（默認(rèn)）|不將序列的下一個(gè)值放入緩存中*/DROP SEQUENCE SEQ1。/*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*//*視圖：。UPDATE T5 SET T_SEX=39。UPDATE T5 SET T_SEX=39。將T5表中所有性別為男的改為女，所有性別為女的改為男(可以使用多條SQL語句)UPDATE T5 SET T_SEX=39。INSERT INTO T5 VALUES(10,39。)。男39。INSERT INTO T5 VALUES(6,39。)。男39。INSERT INTO T5 VALUES(2,39。創(chuàng)建表T5，有列如下：/*T_ID 編號(hào)，為主鍵T_SEX，性別*/CREATE TABLE T5(T_ID NUMBER(2) PRIMARY KEY,T_SEX CHAR(1 CHAR))。INSERT INTO T1 VALUES(39。,5000,10)。，自動(dòng)提交數(shù)據(jù)。事務(wù)：?jiǎn)挝粫r(shí)間內(nèi)一系列的操作，這些操作要么全部成功，要么全部失敗。Java39。INSERT INTO S VALUES(1,39。CREATE TABLE S(S_ID NUMBER(6) PRIMARY KEY,S_NAME VARCHAR2(20))。男39。,39。INSERT INTO T2 VALUES(1,39。,25,NULL)。女39。：NOT NULL 。男39。,T_DATE=SYSDATE WHERE T_DATE IS NULL。ABC39。CC39。INSERT INTO T1(T_ID,T_NAME) VALUES(2,39。,39。,39。CREATE TABLE T1ASSELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=50。VARCHAR2(10):AB,保存可變長度的字符串。%e%39。查詢50號(hào)部門并且工資大于3000的員工的last_name,salary,department_idSELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=50INTERSECT SELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE SALARY3000。集合運(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。YYYYMMDD39。) FROM EMPLOYEES WHERE EMPLOYEE_ID=amp。)FROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,39。FM99,39。MySQLSELECT LAST_NAME,SALARYFROM EMPLOYEESORDER BY SALARY DESCLIMIT 5。偽列：在建表時(shí)ORACLE為表自動(dòng)添加的列。在SELECT子句中使用子查詢SELECT LAST_NAME,(SELECT DEPARTMENT_NAME FROM DEPARTMENTS WHERE DEPARTMENT_ID=90)FROM EMPLOYEES。查詢?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)。查詢出入職最早的員工的last_name,hire_date(格式為：199911)SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。普通子查詢：先執(zhí)行子查詢，再執(zhí)行主查詢SELECT LAST_NAME,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=144)。) ASC。),COUNT(*)FROM EMPLOYEESGROUP BY TO_CHAR(HIRE_DATE,39。%REP%39。HAVING子句：過濾分組結(jié)果，使用HAVING時(shí)必須使用GROUP BY.SELECT 5FROM 1[WHERE] 2[GROUP BY] 3[HAVING 條件] 4[ORDER BY] 6WHERE子句中不能使用組函數(shù)作為過濾條件。查詢每個(gè)部門的人數(shù)，顯示department_name與人數(shù)SELECT ,COUNT(*)FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON =GROUP BY 。SELECT 4FROM 1[WHERE] 2[GROUP BY] 3[ORDER BY] 5 查詢每個(gè)部門的最高的工資SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_IDORDER BY 1 ASC。)39。SELECT COUNT(*)FROM EMPLOYEESWHERE HIRE_DATE39。1997123139。FML99,39。創(chuàng)建一個(gè)查詢顯示雇員的 last_name 并帶星號(hào)顯示他們的月薪，每個(gè)星號(hào)表示1000美圓。SELECT ,FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON =JOIN LOCATIONS LOC ON =WHERE =39。COUNT()SELECT COUNT(*)返回結(jié)果的行數(shù)FROM EMPLOYEESWHERE DEPARTMENT_ID=50。MAX()/MIN():求最大值/最小值SELECT MAX(SALARY),MIN(SALARY)FROM EMPLOYEES。SQL Server:*左外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE *=。SELECT ,FROM EMPLOYEES EMP RIGHT JOIN DEPARTMENTS DEPTON =。%a%39。) 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 獎(jiǎng)金FROM EMPLOYEES。此員工不在此次活動(dòng)范圍內(nèi)39。查詢員工的last_name,job_id,job_title(jobs表中)SELECT ,FROM EMPLOYEES EMP,JOBS JOBWHERE =。多表連接/*笛卡爾集：行數(shù)：表行數(shù)的乘積產(chǎn)生笛卡爾集的原因：沒有連接條件或連接條件無效*/等值連接（內(nèi)連接，簡(jiǎn)單連接）查詢所有部門的department_name,citySELECT DEPARTMENT_NAME,CITYFROM DEPARTMENTS,LOCATIONSWHERE =。ST_CLERK39。 THEN SALARY*2ELSE SALARYEND AS 增加后工資FROM EMPLOYEES。 THEN SALARY*WHEN 39。SA_REP39。IT_PROG39。沒有傭金39。) AS 傭金,(SALARY*12)+(SALARY*12*NVL(COMMISSION_PCT,0)) AS 年收入FROM EMPLOYEES。)FROM DUAL。),TO_NUMBER(39。),TO_NUMBER(39。YYYYMMDD39。)FROM DUAL。TO_DATE(C,M)：根據(jù)模板M將字符類型C值轉(zhuǎn)換為日期類型的值SELECT TO_DATE(39。 查詢employees表中所有員工的last_name與員工3倍的工資（格式為：$50,）,結(jié)果按工資的降序排序。MON39。查詢employees表中所有5月入職員工的last_name,hire_date(格式為:1999年1月1日 星期五)，結(jié)果按hire_date升序排序。FM$9,999,39。39。999999999999939。39。D39。星期三39。YYYYMMDD DY39。SELECT LAST_NAME,HIRE_DATE,TO_CHAR(HIRE_DATE,39。)FROM DUAL。YYYY39。SELECT LAST_NAME,HIRE_DATE,TO_CHAR(HIRE_DATE,39。類型轉(zhuǎn)換：隱式轉(zhuǎn)換，顯式轉(zhuǎn)換隱式轉(zhuǎn)換：Oracle自動(dòng)將一個(gè)類型轉(zhuǎn)換為另一個(gè)類型。SELECT NEXT_DAY(SYSDATE,39。查詢employees表中所有員工的last_name,hire_date,入職年數(shù)SELECT LAST_NAME,HIRE_DATE,TRUNC((SYSDATEHIRE_DATE)/365)FROM EMPLOYEES。SELECT FLOOR()FROM DUAL。)0。SELECT LAST_NAMEFROM EMPLOYEESWHERE INSTR(LAST_NAME,39。查詢employees表中l(wèi)ast_name中包含a或e的員工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。)0。)刪除字符串左右出現(xiàn)的指定的字符FROM DUAL。SELECT TRIM(39。)將第一個(gè)參數(shù)中出現(xiàn)的第二個(gè)參數(shù)用第三個(gè)參數(shù)替換掉FROM EMPLOYEES。SELECT LAST_NAME,REPLACE(LAST_NAME,39。$39。SELECT LAST_NAME,INSTR(LAST_NAME,39。SELECT LAST_NAME,SUBSTR(LAST_NAME,3)截取字符串：從第N位開始截取一直截取到最后FROM EMPLOYEES。)求字符數(shù)FROM DUAL。)將單詞的單字母變?yōu)榇髮慒ROM DUAL。