【正文】 g員工的employee_id,salary,department_idSELECT EMPLOYEE_ID,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE LAST_NAME=39。連接符：||SELECT FIRST_NAME||39。算術(shù)運(yùn)算符：+，，*，/+：只有加法運(yùn)算的功能，沒(méi)有連接作用/：SELECT 5/2FROM DUAL。查詢employees表中所有員工的last_name,job_id,salary,年薪(salary*12)SELECT LAST_NAME,JOB_ID,SALARY,SALARY*12FROM EMPLOYEES。.39。King39。BETWEEN...AND...:使列大于等于一個(gè)值，并且小于等于另一個(gè)值（包含邊界值）查詢employees表中工資在9000到12000的員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY BETWEEN 9000 AND 12000。,39。%o__39。39。%A%39。SELECT LAST_NAMEFROM EMPLOYEESORDER BY LAST_NAME DESC。%a%39。SELECT LENGTHB(39。a39。a39。A39。查詢employees表中l(wèi)ast_name中為4個(gè)字符的員工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。a39。SELECT CEIL()FROM DUAL。星期四39。YYYYMMDD DY39。SELECT TO_CHAR(SYSDATE,39。)FROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,39。)=39。), TO_CHAR(,39。), TO_CHAR(,39。)=39。1999101039。)SYSDATEFROM DUAL。$2,39。NVL2():當(dāng)?shù)谝粋€(gè)參數(shù)不為NULL，返回第二個(gè)參數(shù)，當(dāng)?shù)谝粋€(gè)參數(shù)為NULL，返回第三個(gè)參數(shù)SELECT LAST_NAME,NVL2(COMMISSION_PCT,39。 THEN SALARY*WHEN JOB_ID=39。ST_CLERK39。, SALARY*, 39。/*查詢employees表中所有員工的last_name,hire_date(格式為:19991010),工齡(只保留整數(shù)部分),以及獎(jiǎng)金，如果員工的工齡在15（不包含15年）年以上，獎(jiǎng)金為8倍的工資，如果員工的工齡大于18（不包含18年）年以上，獎(jiǎng)金為10倍的工資，如果員工的工齡在20（不包含20年）年以上，獎(jiǎng)金為15倍的工資，如果員工的工齡小于或等于15年，則沒(méi)有獎(jiǎng)金顯示此員工不在此次活動(dòng)范圍內(nèi).*/SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。查詢員工的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 MAX(HIRE_DATE),MIN(HIRE_DATE)FROM EMPLOYEES。Toronto39。),FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON = JOIN LOCATIONS LOC ON = WHERE =90。3112月9739。SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_ID。/*WHERE與HAVING的區(qū)別：，HAVING過(guò)濾的是分組的結(jié)果，HAVING可以使用組函數(shù)*/查詢平均工資大于10000的部門(mén)編號(hào)與平均工資，結(jié)果按工資降序排序SELECT DEPARTMENT_ID,AVG(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_IDHAVING AVG(SALARY)10000ORDER BY AVG(SALARY) DESC。YYYY39。查詢哪些員工的工資大于104號(hào)員工的工資，顯示這些員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY(SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=104)。多行子查詢：IN,ANY,ALL查詢工資至少大于60號(hào)部門(mén)一名員工的工資的員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY IN (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=60)。ROWID:數(shù)據(jù)在硬盤(pán)或內(nèi)存中的地址ROWNUM:行號(hào),只能小于或小于等于或等于1,否則返回0行數(shù)據(jù)SELECT ROWNUM,LAST_NAME,SALARYFROM EMPLOYEESWHERE ROWNUM=10ORDER BY 2 DESC。) AS SALARY FROM EMPLOYEES ORDER BY HIRE_DATE DESC)WHERE ROWNUM=5。INPUT)。SELECT EMPLOYEE_ID,LAST_NAMEFROM EMPLOYEESUNIONSELECT DEPARTMENT_ID,DEPARTMENT_NAMEFROM DEPARTMENTS。補(bǔ)集:集合集合/*A:1 2 3 4 5B:3 5 AB=1 2 4BA=NULL*/查詢收入最高的610名員工的last_name,salarySELECT LAST_NAME,SALARYFROM (SELECT LAST_NAME,SALARY FROM EMPLOYEES ORDER BY SALARY DESC)WHERE ROWNUM=10MINUSSELECT LAST_NAME,SALARYFROM (SELECT LAST_NAME,SALARY FROM EMPLOYEES ORDER BY SALARY DESC)WHERE ROWNUM=5ORDER BY SALARY DESC。添加數(shù)據(jù)：INSERT INTO語(yǔ)句，并表新行中所有的列賦值INSERT INTO 表名 VALUES(值,值,值,……)。YYYYMMDD39。,3)。將employees收入最高的員工的工資改為公司的最低工資UPDATE EMPLOYEES SET SALARY=(SELECT MIN(SALARY) FROM EMPLOYEES)WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES)。使列不能為空.：，檢查數(shù)據(jù)是否滿足條件。INSERT INTO T2(T_ID,T_NAME,T_AGE) VALUES(2,39。AA39。CREATE TABLE C(C_ID NUMBER(3) PRIMARY KEY,C_NAME VARCHAR2(100))。)。，自動(dòng)回滾數(shù)據(jù)。CC39。女39。INSERT INTO T5 VALUES(5,39。)。女39。男39。視圖中沒(méi)有數(shù)據(jù)，視圖的數(shù)據(jù)來(lái)自于表CREATE [OR REPLACE] VIEW 視圖名ASSELECT語(yǔ)句*/ CREATE OR REPLACE VIEW V1ASSELECT LAST_NAME,DEPARTMENT_ID,SALARYFROM EMPLOYEESWHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID))。CURRVAL:查詢序列當(dāng)前的值，不會(huì)改變序列的值。*/CREATE INDEX EMP_LASTNAME_INDEXON EMPLOYEES(LAST_NAME)。)。 V_STR VARCHAR2(20) NOT NULL:=39。 39。BEGIN SELECT LAST_NAME,SALARY INTO V_NAME,V_SALARYSELECT..INTO時(shí)SELECT語(yǔ)句只能返回一行數(shù)據(jù)。/編寫(xiě)一個(gè)塊，查詢指定部門(mén)的人數(shù)DECLARE V_DEPTID %TYPE:=amp。)。||||39。||)。/查詢指定編號(hào)的員工的所有的信息DECLARE V_EMPID %TYPE:=amp。 V_COUNT NUMBER(1)。 (V_EMPID||39。 39。 V_DATE DATE。我的第一個(gè)PL/SQL塊39。*/SET SERVEROUTPUT ON。AA39。序列：產(chǎn)生一不重復(fù)的數(shù)字,一般用于主鍵/*CREATE SEQUENCE 序列名 [INCREMENT BY n] 每次自增與自減的值，默認(rèn)為1 [START WITH n] 起始值，默認(rèn)為1 [{MAXVALUE n | NOMAXVALUE}] 最大值|沒(méi)有最大值（默認(rèn)） [{MINVALUE n | NOMINVALUE}] 最小值|沒(méi)有最小值（默認(rèn)） [{CYCLE | NOCYCLE}] 循環(huán)|不循環(huán)（默認(rèn)） [{CACHE n | NOCACHE}]。女39。COMMIT。女39。)。INSERT INTO T5 VALUES(3,39。查詢哪些員工在人數(shù)最多的部門(mén)工作，顯示部門(mén)的last_name,department_id,salarySELECT LAST_NAME,DEPARTMENT_ID,SALARYFROM EMPLOYEESWHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID))。AA39。INSERT INTO SC VALUES(2,1,2)。CREATE TABLE SC(SC_ID NUMBER(8) PRIMARY KEY,列級(jí)約束SC_S_ID NUMBER(6) REFERENCES S(S_ID),SC_C_ID NUMBER(3) REFERENCES C(C_ID))。/*創(chuàng)建表Student,有列如下：學(xué)號(hào) 用來(lái)唯一標(biāo)識(shí)一名學(xué)生姓名 必填性別 只能為男或女注冊(cè)時(shí)間 默認(rèn)為當(dāng)前日期*/CREATE TABLE STUDENT(S_ID NUMBER(8) PRIMARY KEY,S_NAME VARCHAR2(24) NOT NULL,S_SEX CHAR(1 CHAR) CHECK(S_SEX=39。,20)。CREATE TABLE T2(T_ID NUMBER(6) PRIMARY KEY,T_NAME VARCHAR2(25) NOT NULL,T_AGE NUMBER(2) CHECK(T_AGE=20 AND T_AGE=50),T_SEX CHAR(1 CHAR) DEFAULT 39。DELETE T1 WHERE T_SEX=39。UPDATE T1 SET T_NAME=39。，并向指定的列中賦值INSERT INTO 表名(列名,列名,列名,……) VALUES(值,值,值,……)。AA39。NUMBER:省略長(zhǎng)度，默認(rèn)為38位的整數(shù)浮點(diǎn)型：NUMBER(8,2):整個(gè)數(shù)字最大的長(zhǎng)度為8位，其中有兩位小數(shù)*/字符類(lèi)型：長(zhǎng)度不能省略/*CHAR(10):AB ,保存固定長(zhǎng)度的字符串。SELECT EMPLOYEE_ID,LAST_NAMEFROM EMPLOYEESUNION ALL不去重復(fù)值SELECT EMPLOYEE_ID,LAST_NAMEFROM EMPLOYEES。查詢各個(gè)部門(mén)入職最早的員工的last_name,hire_date(格式為：19991010),departm