《数据库课件：第8章数据查询》由会员分享，可在线阅读，更多相关《数据库课件：第8章数据查询（83页珍藏版）》请在金锄头文库上搜索。
1、第第8章章 数数 据据 查查 询询主要内容主要内容lSELECT语句介绍l简单查询l分组查询l多表查询l子查询l合并查询lSQL内置函数8.1 SELECT语句介绍语句介绍lSELECT lALL|DISTINCTcolumn_name,expressionlFROM table1_name,table2_name,view_name,lWHERE conditionlGROUP BY column_name1,column_name2, lHAVING group_conditionlORDER BY column_name2 lASC|DESC,column_name2,;8.2 简简 单
2、单 查查 询询l无条件查询l有条件查询l查询排序l查询统计8.2.1 无条件查询无条件查询l查询所有列SELECT * FROM employees; l查询指定列SELECT department_id,department_name FROM departments; l使用算术表达式SELECT employee_id,salary*0.8 FROM .employees;l使用字符常量SELECT employee_id, salary is: , salary FROM employees; l使用函数SELECT employee_id,UPPER(first_name) FROM
3、 employees; l改变列标题SELECT employee_id empno,salary sal FROM employees;l使用连接字符串SELECT 员工名员工名:|first_name|last_name FROM employees; l消除重复行SELECT ALL department_id FROM employees; SELECT DISTINCT department_id FROM employees; 8.2.2 有条件查询有条件查询l查询满足条件的元组可以通过WHERE 子句实现。lWHERE条件中常用的运算符号运算符号谓词比较大小=, , =, =,
4、，!=确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重条件AND，ORl关系运算 SELECT employee_id,salary FROM employees WHERE department_id!= 10; SELECT employee_id,salary FROM employees WHERE salary1000;l确定范围谓词谓词BETWEEN AND与与NOT BETWEEN AND。SELECT * FROM employees WHERE depart
5、ment_id BETWEEN 10 AND 20;SELECT * FROM employees WHERE salary NOT BETWEEN 1000 AND 2000; l确定集合谓词谓词IN可以用来查找属性值属于指定集合的元组。可以用来查找属性值属于指定集合的元组。SELECT employee_id,first_name,last_name,salary FROM employees WHERE department_id IN(10,20,30,50);l字符匹配%（百分号）代表任意长（长度为（百分号）代表任意长（长度为0）字符串。）字符串。_（下划线）代表任意单个字符。（下划
6、线）代表任意单个字符。ESCAPE：转义字符：转义字符SELECT * FROM employees WHERE last_name LIKE %S%; SELECT * FROM employees WHERE first_name LIKE _a%;SELECT * FROM employees WHERE first_name LIKE %x_% ESCAPE x;l空值操作涉及空值查询时使用涉及空值查询时使用IS NULL或或 IS NOT NULL，这里的，这里的IS不能用不能用=替代。替代。SELECT * FROM employees WHERE department_id IS
7、 NULL;SELECT * FROM employees WHERE commission_pct IS NOT NULL;l逻辑操作用逻辑运算符用逻辑运算符NOT、AND和和OR来联结多个查询条件。来联结多个查询条件。优先级：优先级：NOT、AND、OR（用户可以用括号改变优先级）（用户可以用括号改变优先级）。 IN谓词实际上是多个谓词实际上是多个OR运算的缩写。运算的缩写。 SELECT * FROM employees WHERE department_id=10 AND salary 1500; SELECT * FROM employees WHERE (department_id
8、=10 OR department_id=20) AND salary1500;l注意：使用使用BETWEENAND，NOT BETWEENAND，IN，NOT IN运算符的查询条件都可以转换为运算符的查询条件都可以转换为NOT，AND，OR的逻辑运算。例如，下面两个语句是等价的：的逻辑运算。例如，下面两个语句是等价的：SELECT * FROM employees WHERE salary3000 AND salary4000;SELECT * FROM employees WHERE salary BETWEEN 3000 AND 4000;l升序、降序排序ASC: 升序升序 （缺省）；（
9、缺省）；DESC: 降序降序SELECT employee_id,salary FROM employees ORDER BY salary;SELECT employee_id,salary FROM employees ORDER BY salary DESC;l多列排序 首先按照第一个列或表达式进行排序；当第一个列或表达首先按照第一个列或表达式进行排序；当第一个列或表达式的数据相同时，以第二个列或表达式进行排序，以此类式的数据相同时，以第二个列或表达式进行排序，以此类推推 。SELECT * FROM employees ORDER BY department_id,salary DES
10、C; 8.2.3 查询排序查询排序l按表达式排序 可以按特定的表达式进行排序。可以按特定的表达式进行排序。 SELECT employee_id,salary FROM employees ORDER BY salary*12; l使用别名排序 可以使用目标列或表达式的别名进行排序。可以使用目标列或表达式的别名进行排序。SELECT employee_id,salary*12 year_salary FROM employees ORDER BY year_salary; l使用列位置编号排序 如果列名或表达式名称很长，那么使用位置排序可以缩短如果列名或表达式名称很长，那么使用位置排序可以缩短
11、排序语句的长度。排序语句的长度。SELECT employee_id,salary*12 yearsal FROM employees ORDER BY 2; 8.2.4 查询统计查询统计函数格式功能AVGAVG(DISTINCT|ALL)计算一列值的平均值（要求数值列）COUNTCOUNT(DISTINCT|ALL *)统计元组个数COUNTCOUNT(DISTINCT|ALL) 统计一列中 非空值的个数MAXMAX(DISTINCT|ALL)求一列值中的最大值MINMIN(DISTINCT|ALL)求一列值中的最小值SUMSUM(DISTINCT|ALL)计算一列值的总和（要求数值列）ST
12、DDEV STDDEV().计算一列值的标准差VARIANCEVARIANCE()计算一列值的方差l注意除了除了COUNT(*)函数外，其他的统计函数都不考虑返函数外，其他的统计函数都不考虑返回值或表达式为回值或表达式为NULL的情况。的情况。聚集函数只能出现在目标列表达式、聚集函数只能出现在目标列表达式、ORDER BY子句、子句、HAVING子句中，不能出现在子句中，不能出现在WHERE子句和子句和GROUP BY子句中。子句中。默认对所有的返回行进行统计，包括重复的行；如果默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使用要统计不重复的行信息，则可以使用DIS
13、TINCT选项。选项。如果对查询结果进行了分组，则聚集函数的作用范围如果对查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数作用于整个查询结果。为各个组，否则聚集函数作用于整个查询结果。l统计50号部门员工的人数、平均工资、最高工资、最低工资。 SELECT count(*),avg(salary),max(salary),min(salary) FROM employees WHERE department_id=50;l统计所有员工的平均工资和工资总额。SELECT avg(salary),sum(salary) FROM employees;l统计有员工的部门的个数。SELE
14、CT count(DISTINCT department_id) FROM employees;l统计员工工资的方差和标准差。SELECT variance(salary),stddev(salary) FROM employees;8.3 分组查询分组查询l基本语法l单列分组查询l多列分组查询l使用HAVING子句限制返回组SELECT column, group_function, FROM tableWHERE conditionGROUP BY group_by_expressionHAVING group_conditionORDER BY columnASC|DESC;（1）基本语
15、法）基本语法l注意：GROUP BY子句用于指定分组列或分组表达式。子句用于指定分组列或分组表达式。集合函数用于对分组进行统计。如果未对查询分组，集合函数用于对分组进行统计。如果未对查询分组，则集合函数将作用于整个查询结果；如果对查询结果则集合函数将作用于整个查询结果；如果对查询结果分组，则集合函数将作用于每一个组，即每一个分组分组，则集合函数将作用于每一个组，即每一个分组都有一个集合函数。都有一个集合函数。HAVING子句用于限制分组的返回结果。子句用于限制分组的返回结果。WHERE子句对表中的记录进行过滤，而子句对表中的记录进行过滤，而HAVING子子句对分组后形成的组进行过滤。句对分组后
16、形成的组进行过滤。在分组查询中，在分组查询中，SELECT子句后面的所有目标列或目子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是标表达式要么是分组列，要么是分组表达式，要么是集合函数。集合函数。l单列分组查询将查询出来的记录按照某一个指定的列进行分组将查询出来的记录按照某一个指定的列进行分组SELECT department_id,count(*),avg(salary) FROM employees GROUP BY department_id ORDER BY department_id; l多列分组查询在在GROUP BY子句中指定了两个或多个分组列子句中指定了两
17、个或多个分组列SELECT department_id,job_id,count(*),avg(salary) FROM employees GROUP BY department_id,job_id;l使用HAVING子句限制返回组可以使用可以使用HAVING子句，只有满足条件的组才会返回。子句，只有满足条件的组才会返回。SELECT department_id,count(*),avg(salary) FROM employees GROUP BY department_id HAVING avg(salary)8000; 8.4 多表查询多表查询l交叉连接l内连接等值连接等值连接不等值连
18、接不等值连接自身连接自身连接l外连接左外连接左外连接右外连接右外连接全外连接全外连接8.4.1 交叉连接交叉连接l概念两个或多个表之间的无条件连接。一个表中所有记录两个或多个表之间的无条件连接。一个表中所有记录分别与其他表中所有记录进行连接。如果进行连接的分别与其他表中所有记录进行连接。如果进行连接的表中分别有表中分别有n1，n2，n3条记录，那么交叉连接的条记录，那么交叉连接的结果集中将有结果集中将有n1n2n3条记录。条记录。 l标准SQL语句的连接方式SELECT table1.column,talbe2.column, FROM table1 CROSS JOIN table2;lOr
19、acle扩展的连接方式SELECT table1.column,talbe2.column, FROM table1,table2;lemployees表中有107条记录，dept表中有28条记录，那么两个表交叉连接后有2996条记录。SELECT employee_id,first_name,salary,department_nameFROM employees CROSS JOIN departments;l或者：SELECT employee_id,first_name,salary,department_name FROM employees, departments;8.4.2内连
20、接内连接l执行过程l内连接语法l等值内连接l非等值内连接l自身内连接l执行过程首先在表首先在表1中找到第一个元组，然后从头开始扫描表中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表，逐一查找满足连接条件的元组，找到后就将表1中的中的第第1个元组与该元组拼接形成结果表中的一个元组。表个元组与该元组拼接形成结果表中的一个元组。表2全部找完后，再找表全部找完后，再找表1中的第中的第2个元组，然后再从头个元组，然后再从头扫描表扫描表2，逐一查找满足连接条件的元组，找到后就将，逐一查找满足连接条件的元组，找到后就将表表1中的第中的第2个元组与该元组拼接形成结果表中的一个个
21、元组与该元组拼接形成结果表中的一个元组。重复执行，直到表元组。重复执行，直到表1中的全部元组都处理完毕为中的全部元组都处理完毕为止。止。l内连接语法：标准SQL语句的连接方式SELECT table1.column,talbe2.column, FROM table1 INNER JOIN table2 JOIN ON condition;l内连接语法：Oracle扩展的连接方式SELECT table1.column,talbe2.column, FROM table1,table2, WHERE condition;l等值内连接 SELECT employee_id,salary,e.de
22、partment_id, department_name FROM employees e JOIN departments dON e.department_id=d.department_id WHERE e.department_id=10 ;SELECT employee_id,salary,e.department_id,department_name FROM employees e,departments dWHERE e.department_id=d.department_id AND e.department_id=10 ; l非等值内连接SELECT employee_i
23、d,first_name,last_name,salary,grade FROM hr.employees JOIN hr.sal_grades ON salarymin_salary AND salarymin_salary AND salarymax_salary; l自身内连接 SELECT work.employee_id,work.first_name,manager.employee_id,manager.first_name FROM employees work JOIN employees manager ON work.manager_id=manager.employee
24、_id;SELECT work.employee_id,work.first_name,manager.employee_id,manager.first_name FROM employees work, employees manager WHERE work.manager_id=manager.employee_id;8.4.3 外连接外连接l左外连接l右外连接l全外连接l左外连接语法：标准SQL语句的连接方式SELECT table1.column, table2.column, FROM table1 LEFT JOIN table2, ON table1.column table
25、2.column,;l左外连接语法：Oracle扩展的连接方式SELECT table1.column, table2.column, FROM table1, table2,WHERE table1.column table2.column(+);l查询10号部门的部门名、员工号、员工名和所有其他部门的名称，语句为SELECT department_name,employee_id,first_name,last_name FROM departments d LEFT JOIN employees eON d.department_id=e.department_id AND d.depa
26、rtment_id=10;l或SELECT department_name,employee_id,first_name,last_name FROM departments d, employees eWHERE d.department_id=e.department_id(+) AND e.department_id(+)=100; l右外连接语法：标准SQL语句的连接方式SELECT table1.column, table2.column, FROM table1 RIGHT JOIN table2, ON table1.column table2.column;l右外连接语法：Or
27、acle扩展的连接方式SELECT table1.column, table2.column, FROM table1, table2,WHERE table1.column (+) table2.column; l查询20号部门的部门名称及其员工号、员工名，和所有其他部门的员工名、员工号，语句为SELECT employee_id,first_name,department_name FROM departments d RIGHT JOIN employees eON d.department_id=e.department_id AND d.department_id=20;或或SELE
28、CT employee_id,first_name,department_nameFROM departments d, employees eWHERE d.department_id(+)=e.department_id AND d.department_id(+)=20;l全外连接是指在内连接的基础上，将连接操作符两侧表中不符合连接条件的记录加入结果集中。l在Oracle数据库中，全外连接的表示方式为SELECT table1.column, table2.column, FROM table1 FULL JOIN table2, ON table1.column1 = table2.c
29、olumn2;l查询所有的部门名和员工名，语句为SELECT department_name,first_name,last_name FROM employees e FULL JOIN departments d ON e.department_id=d.department_id; 8.5 子子 查查 询询l子查询概述l无关子查询单行单列子查询单行单列子查询多行单列子查询多行单列子查询单行多列子查询单行多列子查询多行多列子查询多行多列子查询l相关子查询l在FROM子句中使用子查询 l在DDL语句中使用子查询l使用WITH子句的子查询l子查询的概念子查询是指嵌套在其他子查询是指嵌套在其他S
30、QL语句中的语句中的SELECT语句，也语句，也称为嵌套查询称为嵌套查询 。在执行时，由里向外，先处理子查询，再将子查询的返在执行时，由里向外，先处理子查询，再将子查询的返回结果用于其父语句（外部语句）的执行。回结果用于其父语句（外部语句）的执行。 l子查询作用在在INSERT或或CREATE TABLE语句中使用子查询语句中使用子查询在在UPDATE语句中使用子查询可以修改一个或多个记录语句中使用子查询可以修改一个或多个记录的数据；的数据；在在DELETE语句中使用子查询可以删除一个或多个记录语句中使用子查询可以删除一个或多个记录在在WHERE和和HAVING子句中使用子查询可以返回一子句中
31、使用子查询可以返回一个或多个值；个或多个值；在在DDL语句中语句中使用使用子查询子查询子查询概述子查询概述8.5.1 无关子查询无关子查询l单行单列子查询l多行单列子查询l单行多列子查询l多行多列子查询l单行单列子查询是指子查询只返回一行数据，而且只返回一列的数据。 l运算符=，=，(SELECT salary FROM employees WHERE employee_id=105); （1）单行单列子查询）单行单列子查询（2）多行单列子查询）多行单列子查询p多行单列子查询是指返回多行数据，且只返回一列的数据。 p运算符号运运 算算 符符含含 义IN与子与子查询返回返回结果中任何一个果中任何
32、一个值相等相等NOT IN与子与子查询返回返回结果中任何一个果中任何一个值都不等都不等ANY比子比子查询返回返回结果中某一个果中某一个值大大=ANY与子与子查询返回返回结果中某一个果中某一个值相等相等ALL比子比子查询返回返回结果中所有果中所有值都大都大ANY(SELECT salary FROM employeesWHERE department_id=50);l查询比50号部门所有员工工资高的员工信息。SELECT employee_id,first_name,last_name,salary FROM employees WHERE salaryALL(SELECT salary FRO
33、M employees WHERE department_id=50);（3）单行多列子查询）单行多列子查询l单行多列子查询是指子查询返回一行数据，但是包含多列数据。l多列数据进行比较时，可以成对比较，也可以非成对比较。成对比较要求多个列的数据必须同时匹配，而非成对比较则不要求多个列的数据同时匹配。l查询与169号员工的工资、工种都相同的员工的信息。SELECT employee_id,first_name,last_name,salary,job_idFROM employees WHERE (salary,job_id)=(SELECT salary,job_id FROMemployee
34、s WHERE employee_id=169);l查询与50号部门某个员工工资相同，工种也与10号部门的某个员工相同的员工的信息。SELECT employee_id,first_name,last_name,salary,job_id FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department_id=50)AND job_id IN (SELECT job_id FROM employees WHERE department_id=50);（4）多行多列子查询）多行多列子查询l多行多列子查询是指
35、子查询返回多行数据，并且是多列数据。l例如，查询与50号部门某个员工的工资和工种都相同的员工的信息，语句为SELECT employee_id,first_name,last_name,salary,job_idFROM employees WHERE (salary,job_id) IN(SELECT salary,job_id FROM employees WHERE department_id=50); 8.5.2相关子查询相关子查询l子查询在执行时并不需要外部父查询的信息，这种子查询称为无关子查询。l如果子查询在执行时需要引用外部父查询的信息，那么这种子查询就称为相关子查询。l在相关子
36、查询中经常使用EXISTS或NOT EXISTS谓词来实现。如果子查询返回结果，则条件为TRUE，如果子查询没有返回结果，则条件为FALSE。l查询没有任何员工的部门号、部门名。SELECT * FROM departments d WHERE NOT EXISTS(SELECT * FROM employees e WHERE e.department_id=d.department_id); l查询比本部门平均工资高的员工信息。SELECT employee_id,first_name,last_name,salary FROM employees e WHERE salary(SELEC
37、T avg(salary) FROM employees WHERE department_id=e.department_id);8.5.3在在FROM子句中使用子查询子句中使用子查询l当在FROM子句中使用子查询时，该子查询被作为视图对待，成为内嵌视图。l查询各个员工的员工号、员工名及其所在部门平均工资。SELECT employee_id,first_name,last_name,d.avgsal FROM employees,(SELECT department_id,avg(salary) avgsal FROM employees GROUP BY department_id) d
38、 WHERE employees.department_id=d.department_id;l查询各个部门号、部门名、部门人数及部门平均工资。SELECT d.department_id,department_name,ds.amount,ds.avgsalFROM departments d, (SELECT department_id, count(*)amount,avg(salary)avgsal FROM employees GROUP BY department_id)ds WHERE d.department_id=ds.department_id;l内嵌视图有一种特殊的应用，
39、称为Top-N-Analysis查询，通过使用伪列ROWNUM，为查询结果集排序，并返回符合条件的记录。lTop-N视图的基本定义形式为：SELECTcolumn_list,ROWNUM FROM (SELECT column_listFROM table ORDER BY Top-N_column) WHERE ROWNUM =N;l查询工资排序在前15名的员工号、员工名、工资及其工资排序号。SELECT * FROM(SELECT employee_id,first_name,last_name,salary FROM employees ORDER BY salary DESC) nes
40、ted_orde WHERE ROWNUM2000;8.5.5 使用使用WITH子句的子查询子句的子查询l如果在一个SQL语句中多次使用同一个子查询，可以通过WITH子句给子查询指定一个名字，从而可以实现通过名字引用该子查询，而不必每次都完整写出该子查询。l查询人数最多的部门的信息。SELECT * FROM departments WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING count(*)=ALL(SELECT count(*) FROM employee
41、s GROUP BY department_id); l相同的子查询连续出现了两次，因此可以按下列方式编写查询语句。WITH deptinfo AS(SELECT department_id,count(*) num FROM employees GROUP BY department_id)SELECT * FROM departments WHERE department_id IN(SELECT department_id FROM deptinfo WHERE num=(SELECT max(num) FROM deptinfo) 8.6 合合 并并 操操 作作l语法lUNIONlIN
42、TERSECTlMINUSl语法： SELECT query_statement1 UNION|UNION ALL|INTERSECT|MINUS SELECT query_statement2;l注意：当要合并几个查询的结果集时，这几个查询的结果集当要合并几个查询的结果集时，这几个查询的结果集必须具有相同的列数与数据类型。必须具有相同的列数与数据类型。如果要对最终的结果集排序，只能在最后一个查询之如果要对最终的结果集排序，只能在最后一个查询之后用后用ORDER BY子句指明排序列。子句指明排序列。 8.6.1 并集运算并集运算lUNION运算符用于获取几个查询结果集的并集，将重复的记录只保留
43、一个，并且默认按第一列进行排序。l查询50号部门的员工号、员工名、工资和部门号以及工资大于8000的所有员工的员工号、员工名、工资和部门号，语句为SELECT employee_id,salary,department_id FROM employees WHERE department_id=50UNIONSELECT employee_id,salary,department_id FROM employees WHERE salary8000 ORDER BY department_id; l如果要保留所有的重复记录，则需要使用UNION ALL运算符。SELECT employee_i
44、d,salary,department_id FROM employees WHERE department_id=50UNION ALLSELECT employee_id,salary,department_id FROM employees WHERE salary8000 ORDER BY department_id;8.6.2 交集运算交集运算lINTERSECT用于获取几个查询结果集的交集，只返回同时存在于几个查询结果集中的记录。同时，返回的最终结果集默认按第一列进行排序。l查询50号部门中工资大于6000的员工号、员工名、工资和部门号，语句为：SELECT employee_id
45、,first_name,last_name,salary,department_idFROM employees WHERE department_id=50INTERSECTSELECT employee_id,first_name,last_name,salary,department_idFROM employees WHERE salary6000; 8.6.3 差集运算差集运算lMINUS用于获取几个查询结果集的差集，即返回在第一个结果集中存在，而在第二个结果集中不存在的记录。同时，返回的最终结果集默认按第一列进行排序。l查询50号部门中工种不是“ST_CLERK”的员工号、员工名和
46、工种名称，语句为： SELECT employee_id,first_name,last_name,job_id FROM employees WHERE department_id=50MINUSSELECT employee_id,first_name,last_name,job_idFROM employees WHERE job_id=ST_CLERK;8.7 SQL内置函数内置函数lSQL函数分类l数值函数l字符函数l日期函数l转换函数l其他函数SQL函数分类函数分类l根据参数作用行数的不同，可以分为：单行函数单行函数多行函数多行函数l根据参数类型不同，可以分为：数值函数数值函数字符
47、函数字符函数日期函数日期函数转换函数转换函数聚集函数聚集函数 8.7.1 数值函数数值函数函数返回值ABS(n)返回n的绝对值CEIL(n)返回大于或等于n的最小整数EXP(n)返回e的n次幂FLOOR(n)返回小于或等于n的最大整数LN(n)返回以E为底的n的对数LOG(m,n)返回以m为底的n的对数MOD(m,n)返回m除以n的余数POWER(m,n)返回m的n次方ROUND(m,n)对m进行四舍五入（n大于0时，将m四舍五入到小数点右边n位，n等于零时，表示对m进行取整，n小于0时，则小数点左边的数字位置被圆整。SIGN(n)判断n的正负（n大于0返回，n等于0返回0，n小于0返回-1S
48、QRT(n)返回n的平方根SQUARE(n)返回n的平方TRUNC(m,n)对m进行截断操作（n截断到小数点后第n位，如果n未给出，则系统默认为0,n也可以为负数，表示小数点左边的数字位置被删除成零。lSELECT salary/22 daysal,round(salary/22,1),ltrunc(salary/22,1), round(salary/22,-1),trunc(salary/22,-1) lFROM employees;lSELECT salary,width_bucket(salary,1000,10000,10) lFROM employees WHERE departm
49、ent_id=30; lSELECT floor(3.5),ceil(3.5),mod(5,3), remainder(5,3), mod(4,3),remainder(4,3) FROM dual;lFLOOR(3.5) CEIL(3.5) MOD(5,3) REMAINDER(5,3) MOD(4,3) REMAINDER(4,3) - 3 4 2 -1 1 18.7.2 字符函数字符函数函数返回值ASCII(char)字符串首字符的ASC码值CHR(n)ASC码值为n的字符CONCAT把两个列值拼接起来；“|”操作符更通用INITCAP(char)将字符串中每个单词的首字母大写INSTR
50、（char1,char2,a,b)INSTRB（char1,char2,a,b)返回指定字符的位置LENGTH(char)LENGTHB(char)计算字符串的长度SUBSTR(char,m,n)求子串。column中从起始位置m开始长度为n的子串Replace(str1,str2,str3)LOWER(char)、UPPER(char)将字符串中所有的大写字母变为小(大)写LPAD(char1,n,char2)从左侧用字符串char2补齐字符串char1至长度n（右对齐）RPAD(char1,n,char2) 从右侧用char2补齐char1至长度n.LTRIM(char,SET)把char
51、中最左侧的若干个字符去掉，以使其首字符不在SET中RTRIM(char,SET)把char中最右侧的若干个字符去掉，以使其尾字符不在SET中TRIM(leading|trailing|both FROM string)lSELECT lpad(abc,5, #) leftpad,rpad(abc,5, #) rightpad, ltrim(abcd, a) lefttrim,rtrim(abcde, e) righttrim, substr(abcd,2,3) substring FROM dual;lLEFTPAD RIGHTPAD LEFTTRIM RIGHTTRIM SUBSTRING
52、- - - - - #abc abc# bcd abcd bcd lSELECT employee_id,lconcat(concat(first_name, ),last_name) employee_name lFROM employees lWHERE employee_id=108;lSELECT instr(abcde,b) position,replace(oracle9i,9i,10g) lnewstring,soundex(hello) soundlFROM dual;lPOSITION NEWSTRING SOUND - 2 oracle10g H4008.7.3 日期函数日
53、期函数l日期函数是指对日期进行处理的函数，函数输入为DATE或TIMESTAMP类型的数据，输出为DATE类型的数据（除MONTH_BETWEEN函数返回整数以外）。lOracle数据库中日期的默认格式为DD-MON-YY。可以通过设置NLS_DATE_FORMAT参数设置当前会话的日期格式，通过NLS_LANGUAGE参数设置表示日期的字符集。例如：ALTER SESSION SET NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS;ALTER SESSION SET NLS_LANGUAGE=AMERICAN;函数返回值ADD_MONTHS(d,n)返回日期 d
54、添加 n 个月的日期CURRENT_DATE返回系统当前日期和时间CURRENT_TIMESTAMP(p) 返回系统当前时间戳EXTRACT(depart FROM d) 返回d中depart对应部分的内容 LAST_DAY(d)返回d所在月份最后一天的日期LOCALTIMESTAMP(P)返回本地时间戳LOCALTIMESTAMP(p)返回当前会话时区所对应的日期时间MONTHS_BETWEEN(d1,d2)日期d1和d2之间相隔的月数NEXT_DAY(d,day)d后第一周指定的日期。ROUND（d，format）日期d按format格式进行舍入SYSDATE返回系统当前日期和时间。SYS
55、TIMESTAMP 返回系统当前时间戳。TRUNC（d,format）返回截尾到由format指定单位的日期TO_CHAR (d，,fmt, nlsparam)将日期时间d转换为符合特定格式的字符串TO_TIMESTAMP(char,fmt,nlsparam)将符合特定日期和时间格式的字符串转换为TIMESTAMP类型值lSELECT SYSDATE,add_months(sysdate,2) ADDM, next_day(sysdate,2) NEXTD,Last_day(sysdate) LASTD, round(sysdate, MONTH) ROUNDM, trunc(sysdate,
56、 MONTH) TRUNCM FROM DUAL;lSYSDATE ADDM NEXTD LASTD ROUNDM TRUNCM - - - - - - 2009-03-27 2009-05-27 2009-03-30 2009-03-31 2009-04-01 2009-03-01 lSELECT extract(YEAR FROM SYSDATE) YEAR, extract(DAY FROM SYSDATE) DAY , extract(HOUR FROM SYSTIMESTAMP) HOUR,extract(MINUTE FROM SYSTIMESTAMP) MINUTE FROM D
57、UAL;lYEAR DAY HOUR MINUTE - 2009 28 14 44 lSELECT dbtimezone,localtimestamp, numtoyminterval(20,MONTH) DAY_SECOND FROM dual;lDBTIMEZONE LOCALTIMESTAMP YEAR_MONTH - +00:00 28-3月-09 10.56.04.882000 下午 +000000001-08 8.7.4 转换函数转换函数函 数说 明CAST(expr AS datatype)将表达式expr按指定的类型返回CHARTOROWID(char)将字符串换转换为ROWI
58、D类型HEXTORAW(char)将十六进制数转换为二进制数RAWTOHEX(raw)将二进制数转换为十六进制数ROWIDTOCHAR(rowid)将ROWID类型转换为字符类型TO_CHAR(d,fmt)将日期d按指定格式转换为字符串TO_CHAR(num,fmt)将数值按指定格式转换为字符串TO_DATE(char,fmt)将字符串按指定格式转换为日期TO_CLOB(char)将字符串转换为CLOB类型数据TO_NUMBER(char,fmt)将字符串按指定格式转换为数值lSELECT to_date(09-3-28, yy-mm-dd) CHARTODATE, to_char(sysda
59、te, yyyy-mm-dd hh:mi:ss) DATETOCHAR, to_char(123, $9999.99) NUMTOCHAR, to_number($1234.56,$9999.99) CHARTONUMBER FROM DUAL;lCHARTODATE DATETOCHAR NUMTOCHAR CHARTONUMBER - 28-3月 -09 2009-03-29 12:53:54 $123.00 1234.56 lSELECT timestamp_to_scn(systimestamp) SCN, scn_to_timestamp(2790424) TIMESTAMP FRO
60、M dual;lSCN TIMESTAMP - 2790484 29-3月 -09 12.58.20.000000000 上午8.7.5 其他函数其他函数函 数说 明GREATEST(expr1,expr2,)返回几个表达式中的最大值LEAST(expr1,expr2,)返回几个表达式中的最小值NULLIF(expr1,expr2)如果expr1与expr2相等，则函数返回NULL，否则返回exp1NVL(expr1,expr2)如果expr1为NULL，则返回expr2，否则返回expr1NVL2(expr1,expr2,exp3)如果expr1为NULL，则返回expr3，否则返回expr2UID返回当前会话的用户IDUSER返回当前会话的数据库用户名DECODE(base_expr,expr1,value1,expr2,value2,default)如果base_expr=exprn，则返回valuen的值lSELECT employee_id, lsalary+nvl(commission_pct*salary,0) totalsalarylFROM .employees lWHERE department_id=30; 总结总结l简单的单表查询l分组查询l连接查询l子查询合并查询lSQL内置函数
