Oracle基础学习.doc

Oracle是甲骨文公司大型关系数据库systemdheehttp:/IP:5560/isqlplus导入数据库脚本：脚本路径所有的关系数据对表的操作使用的都是SQL语句查询表使用SQL语句中的SELECT语句基本SELECT语句：SELECT 列名,列名,|*|算术表达式FROM 表名-查询employees表中所有员工的last_name,salarySELECT LAST_NAME,SALARY FROM EMPLOYEES;Select last_name,salaryFrom employees;-查询departments表中所有部门的信息SELECT *FROM DEPARTMENTS;-算术运算符：+，-，*，/+：只有加法运算的功能，没有连接作用/：SELECT 5/2FROM DUAL;-查询employees表中所有员工的last_name,job_id,salary,年薪(salary*12)SELECT LAST_NAME,JOB_ID,SALARY,SALARY*12FROM EMPLOYEES;/*列别名1.列名 列别名2.列名 AS 列别名注意：当列别名要区分大小写，或者列别名中包含了特殊字符，或者列别名为关键时，需要将别名放在一对双引号中双引号在关系数据库中表示列别名*/SELECT LAST_NAME USER,JOB_ID,SALARY,SALARY*12 AS YEAR SALFROM EMPLOYEES;-字符串：一对单引号SELECT DHEE AS 名字,大连 AS cityFROM DUAL;-连接符：|SELECT FIRST_NAME|.|LAST_NAME AS 姓名FROM EMPLOYEES;-屏蔽查询结果中重复记录：DISTINCTSELECT DISTINCT JOB_IDFROM EMPLOYEES;SELECT DISTINCT JOB_ID,SALARYFROM EMPLOYEES;-过滤与排序-过滤：WHERE子句SELECTFROMWHERE 条件;-查询employees表中50号部门员工的last_name,salary,department_idSELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=50;-查询employees表中所有工资大于10000的员工的last_name,job_id,salarySELECT LAST_NAME,JOB_ID,SALARYFROM EMPLOYEESWHERE SALARY10000;-查询employees表中king员工的employee_id,salary,department_idSELECT EMPLOYEE_ID,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE LAST_NAME=King;-查询条件为字符串时，查询的值必须放在一对单引号中，并且查询的内容区分大小写-查询employees表中哪些员工的职位(job_id)为it_prog，显示这些员工的last_name,job_id,salarySELECT LAST_NAME,JOB_ID,SALARYFROM EMPLOYEESWHERE JOB_ID=IT_PROG;SELECT LAST_NAME,HIRE_DATEFROM EMPLOYEESWHERE HIRE_DATE=07-6月-94;-查询条件为日期类型时，查询的值必须放在一对单引号中，并且日期格式必须为Oracle默认的日期格式。-Oracle默认的日期格式为：DD-MON-RR-查询employees表中97年以后入职的员工的last_name,hire_date(包含97年)SELECT LAST_NAME,HIRE_DATEFROM EMPLOYEESWHERE HIRE_DATE=01-1月-97;-BETWEEN.AND.:使列大于等于一个值，并且小于等于另一个值（包含边界值）-查询employees表中工资在9000到12000的员工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY BETWEEN 9000 AND 12000;-查询employees表中95年到97年入职员工的last_name,hire_date(包含95年与97年)SELECT LAST_NAME,HIRE_DATEFROM EMPLOYEESWHERE HIRE_DATE BETWEEN 01-1月-95 AND 31-12月-97;-IN(值列表)：使列与列表中任意一个值进行匹配-查询employees表中在20，50，90部门工作的员工的last_name,salary,department_idSELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID IN (20,50,90);-查询employees 表中职位(job_id)为it_prog或st_clerk或sa_rep的员工的last_name,job_id,salarySELECT LAST_NAME,JOB_ID,SALARYFROM EMPLOYEESWHERE JOB_ID IN (IT_PROG,ST_CLERK,SA_REP);-LIKE:模糊查询/*%:任意长度的任意字符_:一个长度的任意字符*/SELECT EMPLOYEE_ID,LAST_NAME,SALARYFROM EMPLOYEESWHERE LAST_NAME LIKE _a%;-查询employees表中倒数第三个字符为o的员工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE %o_;SELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE K%;-查询employees表中job_id中包含A_的员工的last_name,job_idSELECT LAST_NAME,JOB_IDFROM EMPLOYEESWHERE JOB_ID LIKE %A/_% ESCAPE /;-IS NULL:-NULL,0,-NULL:与类型无关，表示未知或不确定。-查询employees表中所有员工的last_name,departmnet_idSELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID IS NULL;-AND,OR,NOT-查询employees表中工资在9000到12000的员工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY BETWEEN 9000 AND 12000;SELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY=9000 AND SALARY=5000 AND SALARY10000ORDER BY sal DESC;SELECT LAST_NAME,HIRE_DATEFROM EMPLOYEESORDER BY HIRE_DATE DESC;SELECT LAST_NAMEFROM EMPLOYEESORDER BY LAST_NAME DESC;/*1.ORDER BY后面可以放列名2.ORDER BY后面可以放列别名(注意大小写)3.ORDER BY后面可以放查询结果中列的序号*/-查询employees表中所有员工的last_name,job_id,department_id,salary,结果按部门升序，工资降序排序SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,SALARYFROM EMPLOYEESORDER BY DEPARTMENT_ID ASC NULLS FIRST,SALARY DESC;SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,SALARYFROM EMPLOYEESORDER BY DEPARTMENT_ID DESC NULLS LAST,SALARY DESC;-NULLS FIRST/NULLS LAST-单行函数：SELECT LAST_NAME,UPPER(LAST_NAME)-将参数的值变为大写FROM EMPLOYEES;SELECT LAST_NAME,LOWER(LAST_NAME)-将参数的值变为小写FROM EMPLOYEES;-查询employees表中last_name中包含A（不区分大小写）的员工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE UPPER(LAST_NAME) LIKE %A%;SELECT LAST_NAMEFROM EMPLOYEESWHERE LOWER(LAST_NAME) LIKE %a%;SELECT INITCAP(abc abc!ABC#ABC$ABC%ABCABC)-将单词的单字母变为大写FROM DUAL;SELECT LAST_NAME,LENGTH(LAST_NAME)FROM EMPLOYEES;SELECT LENGTH(中国)-求字符数FROM DUAL;SELECT LENGTHB(中国)-求字节数FROM DUAL;SELECT LAST_NAME,SUBSTR(LAST_NAME,3)-截取字符串：从第N位开始截取一直截取到最后FROM EMPLOYEES;SELECT LAST_NAME,SUBSTR(LAST_NAME,3,2)-截取字符串：从第N位开始截取，截取指定长度的字符串FROM EMPLOYEES;SELECT LAST_NAME,SUBSTR(LAST_NAME,-3)-截取字符串：从倒数第N位开始截取一直截取到最后FROM EMPLOYEES;SELECT LAST_NAME,SUBSTR(LAST_NAME,-3,2)-截取字符串：从倒数第N位开始截取，截取指定长度的字符串FROM EMPLOYEES;SELECT LAST_NAME,INSTR(LAST_NAME,a)-在第一个参数中查找第二个参数首次出现的位置,没找到返回0FROM EMPLOYEES;-显示第一个参数，并用第二个参数指定第一个参数的长度，如果第一个参数的长度不够用第三个参数在左/右边补齐长度SELECT LAST_NAME,LPAD(LAST_NAME,20,$),RPAD(LAST_NAME,20,$)FROM EMPLOYEES;SELECT LAST_NAME,REPLACE(LAST_NAME,a,1)-将第一个参数中出现的第二个参数用第三个参数替换掉FROM EMPLOYEES;SELECT TRIM( ABC ABC ) AS A-删除字符串左右出现的空格FROM DUAL;SELECT TRIM(A FROM AAABACAAA)-删除字符串左右出现的指定的字符FROM DUAL;-查询employees表中所有员工的last_name,要求显示的内容中首字母为小写，其它字母均为大写SELECT LOWER(SUBSTR(LAST_NAME,1,1)|UPPER(SUBSTR(LAST_NAME,2)FROM EMPLOYEES;-查询employees表中last_name中包含a（小写）的员工的last_name(不能使用LIKE)SELECT LAST_NAMEFROM EMPLOYEESWHERE INSTR(LAST_NAME,a)0;-查询employees表中last_name中为4个字符的员工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE _;SELECT LAST_NAMEFROM EMPLOYEESWHERE LENGTH(LAST_NAME)=4;-查询employees表中last_name中包含a或e的员工的last_nameSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE %a% OR LAST_NAME LIKE %e%;SELECT LAST_NAMEFROM EMPLOYEESWHERE INSTR(LAST_NAME,a)0 OR INSTR(LAST_NAME,e)0;-四舍五入：SELECT ROUND(256.987),ROUND(256.987,2),ROUND(256.987,0),ROUND(256.987,-1)FROM DUAL;-截断数字：SELECT TRUNC(256.987),TRUNC(256.987,2),TRUNC(256.987,0),TRUNC(256.987,-1)FROM DUAL;-MOD():取余数SELECT MOD(15,2)FROM DUAL;SELECT FLOOR(12.1)FROM DUAL;SELECT CEIL(12.1)FROM DUAL;-获得数据库服务器的时间：SYSDATE,日期+时间SELECT SYSDATEFROM DUAL;/*SQL Server:getDate():获得系统时间MySQL:currdate():获得系统日期,没有时间now():获得系统日期+时间*/*日期+天数=日期日期-天数=日期日期-日期=天数日期不能加日期*/SELECT SYSDATE-100FROM DUAL;-查询employees表中所有员工的last_name,hire_date,入职年数SELECT LAST_NAME,HIRE_DATE,TRUNC(SYSDATE-HIRE_DATE)/365)FROM EMPLOYEES;-MONTHS_BETWEEN(D1,D2):求两个参数相差的月数SELECT LAST_NAME,HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 年数FROM EMPLOYEES;-ADD_MONTHS(D,N):在一个日期上加上指定的月数SELECT LAST_NAME,HIRE_DATE,ADD_MONTHS(HIRE_DATE,35)FROM EMPLOYEES;-NEXT_DAY():SELECT NEXT_DAY(SYSDATE,7)FROM DUAL;SELECT NEXT_DAY(SYSDATE,星期四)FROM DUAL;-LAST_DAY():求日期所在月份的最后一天SELECT LAST_DAY(SYSDATE)FROM DUAL;-类型转换：隐式转换，显式转换-隐式转换：Oracle自动将一个类型转换为另一个类型。SELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=50;-显式转换：/*数字与字符进行类型转换日期与字符进行类型转换数字与日期不能进行类型转换*/-TO_CHAR(D,M):将日期类型D按模板M转换为字符类型的值。SELECT LAST_NAME,HIRE_DATE,TO_CHAR(HIRE_DATE,YYYY-MM-DD DY)FROM EMPLOYEES;SELECT LAST_NAME,TO_CHAR(HIRE_DATE,YYYY)FROM EMPLOYEES;SELECT TO_CHAR(SYSDATE,D DD DDD)FROM DUAL;SELECT TO_CHAR(SYSDATE,YYYY-MM-DD HH24:MI:SS)FROM DUAL;SELECT LAST_NAME,HIRE_DATE,TO_CHAR(HIRE_DATE,FMYYYY年MM月DD日 DY)FROM EMPLOYEES;-查询employees表中所有周三入职的员工的last_name,hire_date(格式为：YYYY-MM-DD DY)SELECT LAST_NAME,TO_CHAR(HIRE_DATE,YYYY-MM-DD DY)FROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,DY)=星期三;SELECT LAST_NAME,TO_CHAR(HIRE_DATE,YYYY-MM-DD DY)FROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,D)=4;-TO_CHAR(N,M):将数字类型N按模板M转换为字符类型的值SELECT TO_CHAR(256.987), TO_CHAR(256.897,FM99999.99), TO_CHAR(256.987,99), TO_CHAR(256.987,9999999999999), TO_CHAR(256.897,FM00000.00), TO_CHAR(256.897,FM99999.00), TO_CHAR(256456.897,FM9,999,999.00), TO_CHAR(256456.897,FM$9,999,999.00), TO_CHAR(256456.897,FML9,999,999.00)FROM DUAL;-查询employees表中所有5月入职员工的last_name,hire_date(格式为:1999年1月1日 星期五)，结果按hire_date升序排序。SELECT LAST_NAME,TO_CHAR(HIRE_DATE,FMYYYY年MM月DD日 DY) AS HIRE_DATEFROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,MON)=5月 ORDER BY 2 ASC; -查询employees表中所有员工的last_name与员工3倍的工资（格式为：$50,000.00）,结果按工资的降序排序。SELECT LAST_NAME,TO_CHAR(SALARY*3,FM$999,999.00)FROM EMPLOYEESORDER BY SALARY DESC;-TO_DATE(C,M)：根据模板M将字符类型C值转换为日期类型的值SELECT TO_DATE(1999-10-10,YYYY-MM-DD)FROM DUAL;SELECT TO_DATE(2012-10-1,YYYY-MM-DD)-SYSDATEFROM DUAL;-TO_NUMBER(C,M):根据模板M将字符类型C转换为数字类型的值SELECT TO_NUMBER(256.23),TO_NUMBER(2,256.23,9,999.00),TO_NUMBER($2,256.23,$9,999.00)FROM DUAL;-当NULL参与到算术运算时结果一定为NULL-NVL():当第一个参数不为NULL时，返回第一个参数的值，当第一个参数为NULL时，返回第二个参数的值，两个参数的类型必须一致。SELECT LAST_NAME,SALARY,NVL(TO_CHAR(COMMISSION_PCT),没有佣金) AS 佣金,(SALARY*12)+(SALARY*12*NVL(COMMISSION_PCT,0) AS 年收入FROM EMPLOYEES;-NVL2():当第一个参数不为NULL，返回第二个参数，当第一个参数为NULL，返回第三个参数SELECT LAST_NAME,NVL2(COMMISSION_PCT,有佣金,没有佣金)FROM EMPLOYEES;-查询employees表中所有员工的last_name,job_id,salary,以及增加后的工资,如果员工的job_id为IT_PROG时，增加后的工资为工资为1.2倍，如果job_id为ST_CLERK时，增加后的工资为工资的1.5倍，如果job_id为SA_REP时，增加后的工资为工资的2倍，其它job_id的员工的工资不变。SELECT LAST_NAME,JOB_ID,SALARY,CASEWHEN JOB_ID=IT_PROG THEN SALARY*1.20WHEN JOB_ID=ST_CLERK THEN SALARY*1.50WHEN JOB_ID=SA_REP THEN SALARY*2ELSE SALARYEND AS 增加后工资FROM EMPLOYEES;SELECT LAST_NAME,JOB_ID,SALARY,CASE JOB_IDWHEN IT_PROG THEN SALARY*1.20WHEN ST_CLERK THEN SALARY*1.50WHEN SA_REP THEN SALARY*2ELSE SALARYEND AS 增加后工资FROM EMPLOYEES;SELECT LAST_NAME,JOB_ID,SALARY,DECODE(JOB_ID,IT_PROG,SALARY*1.2,ST_CLERK,SALARY*1.5,SA_REP,SALARY*2,SALARY) AS 增加后工资FROM EMPLOYEES;-多表连接/*笛卡尔集：行数：表行数的乘积产生笛卡尔集的原因：没有连接条件或连接条件无效*/-等值连接（内连接，简单连接）-查询所有部门的department_name,citySELECT DEPARTMENT_NAME,CITYFROM DEPARTMENTS,LOCATIONSWHERE DEPARTMENTS.LOCATION_ID=LOCATIONS.LOCATION_ID;-查询所有员工的last_name,department_nameSELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES,DEPARTMENTSWHERE EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID;-表别名:表名 表别名SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID;-查询员工的last_name,department_id,department_nameSELECT EMP.LAST_NAME,DEPT.DEPARTMENT_ID,DEPT.DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID;-查询员工的last_name,job_id,job_title(jobs表中)SELECT EMP.LAST_NAME,EMP.JOB_ID,JOB.JOB_TITLEFROM EMPLOYEES EMP,JOBS JOBWHERE EMP.JOB_ID=JOB.JOB_ID;/*查询employees表中所有员工的last_name,hire_date(格式为:1999-10-10),工龄(只保留整数部分),以及奖金，如果员工的工龄在15（不包含15年）年以上，奖金为8倍的工资，如果员工的工龄大于18（不包含18年）年以上，奖金为10倍的工资，如果员工的工龄在20（不包含20年）年以上，奖金为15倍的工资，如果员工的工龄小于或等于15年，则没有奖金显示此员工不在此次活动范围内.*/SELECT LAST_NAME,TO_CHAR(HIRE_DATE,YYYY-MM-DD) AS HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 工龄,CASEWHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)20 THEN TO_CHAR(SALARY*15)WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)18 THEN TO_CHAR(SALARY*10)WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)15 THEN TO_CHAR(SALARY*8)ELSE 此员工不在此次活动范围内END AS 奖金FROM EMPLOYEES;SELECT LAST_NAME,TO_CHAR(HIRE_DATE,YYYY-MM-DD) 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 AND TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)20 THEN SALARY*15ELSE 0END AS 奖金FROM EMPLOYEES;-查询员工的last_name,job_title,department_name,citySELECT EMP.LAST_NAME,JOB.JOB_TITLE,DEPT.DEPARTMENT_NAME,LOC.CITYFROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC,JOBS JOBWHERE EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID AND DEPT.LOCATION_ID=LOC.LOCATION_ID AND EMP.JOB_ID=JOB.JOB_ID;SQL:1999-查询员工的last_name,department_nameSELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP ,DEPARTMENTS DEPTWHERE EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID;SELECT EMP.LAST_NAME,DEPT.DEPARTMENT_NAMEFROM EMPLOYEES EMP INNER JOIN DEPARTMENTS DEPT ON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_IDWHERE EMP.LAST_NAME LIKE %a%;-查询员工的last_name,job_title,department_name,citySELECT EMP.LAST_NAME,JOB.JOB_TITLE,DEPT.DEPARTMENT_NAME,LOC.CITYFROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC,JOBS JOBWHERE EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID AND DEPT.LOCATION_ID=LOC.LOCATION_ID AND EMP.JOB_ID=JOB.JOB_ID;SELECT EMP.LAST_NAME,JOB.JOB_TITLE,DEPT.DEPARTMENT_NAME,LOC.CITYFROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_IDJOIN JOBS JOB ON JOB.JOB_ID=EMP.JOB_IDJOIN LOCATIONS LOC ON LOC.LOCATION_ID=DEPT.LOCATION_ID;-内连接：查询出满足连接条件的数据-外连接：查询出满足连接条件与不满足连接条件的数,左外连接、右外连接-查询所有员工的last_name,department_nameSELECT EMP.LAST_NAME,DEPT.DEPARTMENT_NAMEFROM EMPLOYEES EMP LEFT JOIN DEPARTMENTS DEPTON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID;SELECT EMP.LAST_NAME,DEPT.DEPARTMENT_NAMEFROM EMPLOYEES EMP RIGHT JOIN DEPARTMENTS DEPTON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID;SELECT EMP.LAST_NAME,DEPT.DEPARTMENT_NAMEFROM EMPLOYEES EMP FULL OUTER JOIN DEPARTMENTS DEPTON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID;-ORACLE中独有的外连接:(+)-左外连接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID(+);-右外连接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE EMP.DEPARTMENT_ID(+)=DEPT.DEPARTMENT_ID;-SQL Server:*-左外连接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE EMP.DEPARTMENT_ID*=DEPT.DEPARTMENT_ID;-右外连接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE EMP.DEPARTMENT_ID=*DEPT.DEPARTMENT_ID;-组函数（聚合函数）-SUM():求总和SELECT SUM(SALARY)FROM EMPLOYEES;-AVG():求平均值SELECT AVG(SALARY)FROM EMPLOYEES;-MAX()/MIN():求最大值/最小值SELECT MAX(SALARY),MIN(SALARY)FROM EMPLOYEES;SELECT MAX(HIRE_DATE),MIN(HIRE_DATE)FROM EMPLOYEES;SELECT MAX(LAST_NAME),MIN(LAST_NAME)FROM EMPLOYEES;-求50号部门的平均工资，总工资，最高工资与最低工资SELECT AVG(SALARY),SUM(SALARY),MAX(SALARY),MIN(SALARY)FROM EMPLOYEESWHERE DEPARTMENT_ID=50;-COUNT()SELECT COUNT(*)-返回结果的行数FROM EMPLOYEESWHERE DEPARTMENT_ID=50;SELECT COUNT(COMMISSION_PCT)-返回指定列中不为NULL的值的个数FROM EMPLOYEES;-查询employees表中last_name中包含A（不区分大小写）的员工的人数SELECT COUNT(*)FROM EMPLOYEESWHERE UPPER(LAST_NAME) LIKE %A%;SELECT EMP.LAST_NAME,EMP.JOB_ID,EMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAMEFROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_IDJOIN LOCATIONS LOC ON LOC.LOCATION_ID=DEPT.LOCATION_IDWHERE LOC.CITY=Toronto;SELECT EMP.LAST_NAME,EMP.JOB_ID,EMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC WHERE EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_ID AND LOC.LOCATION_ID=DEPT.LOCATION_ID AND LOC.CITY=Toronto;-创建一个查询显示雇员的 last_name 并带星号显示他们的月薪，每个星号表示1000美圆。按薪水降序排序数据。列标签为 EMPLOYEES_AND_THEIR_SALARIES。-查询所有90号部门员工的last_name,salary(格式为：￥50,000.00),citySELECT EMP.LAST_NAME,TO_CHAR(EMP.SALARY,FML99,999.00),LOC.CITYFROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_IDJOIN LOCATIONS LOC ON LOC.LOCATION_ID = DEPT.LOCATION_IDWHERE DEPT.DEPARTMENT_ID=90;-查询97年以后入职员工的人数。SELECT COUNT(*)FROM EMPLOYEESWHERE HIRE_DATETO_DATE(1997-12-31,YYYY-MM-DD);SELECT COUNT(*)FROM EMPLOYEESWHERE HIRE_DATE31-12月-97;SELECT COUNT(*)FROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,YYYY)1997;-GROUP BY子句:分组。统计，报表。SELECT 4FROM 1WHERE 2GROUP BY 3ORDER BY 5 -查询每个部门的最高的工资SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_IDORDER BY 1 ASC;SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_ID;-查询每个职位的平均工资，显示job_id与平均工资SELECT JOB_ID,AVG(SALARY)FROM EMPLOYEESGROUP BY JOB_ID;-查询每个部门最高的工资SELECT MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_ID;-查询每个部门的人数，显示department_name与人数SELECT DEPT.DEPARTMENT_NAME,COUNT(*)FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_IDGROUP BY DEPT.DEPARTMENT_NAME;-查询每个部门的人数，显示department_id,department_name与人数SELECT EMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAME,COUNT(*)FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON EMP.DEPARTMENT_ID=DEPT.DEPARTMENT_IDGROUP BY EMP.DEPARTMENT_ID,DEPT.DEPARTMENT_NAMEORDER BY 1 ASC