sql编程语言.ppt

1、sql编程语言,本讲目标,了解sql概念 掌握表记录各种不同查询 掌握函数的使用,本章内容,本章内容: (1）表查询 (2)函数 本章重点: (1)表记录的操作(2)函数的定义和使用 本章难点:(1)多表查询（2）函数的定义,sql概念,SQL全称是“结构化查询语言(Structured Query Language)”。SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。 SQL语言包含4个部分： 数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。 数据操作语言(DML)，例如：INSERT、UPDATE、DELETE语句。 数据查询语

2、言(DQL)，例如：SELECT语句。 数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。,数据查询,1、单表查询2、多表查询,1、*的使用 2、取消重复:使用DISTINCT,单表查询,Emp员工表,Dept表,工资,3、返回前n行记录 【例】从emp表中检索所有的信息，要求只显示前5行数据 select * from emp where rownum=5,排序后再取前5条数据? select * from emp where rownum=5 order by sal;只能取前5条后再排序 select * from (select * from

3、 emp order by sal) where rownum=5,思考：返回表中从第m行到第n行的记录如何解决？ select * from emp where rownum=8 minus select * from emp where rownum5,select * from emp where rownum=4;?,rownum和rowid的区别,rowid 用于定位数据表中某条数据的位置，是唯一的、也不会改变 rownum 表示查询某条记录在整个结果集中的位置， 同一条记录查询条件不同对应的 rownum 是不同的,而 rowid 是不会变的,例如有如下一张表 :USER,当执行查

4、询 : select rowid,rownum,name,age from USER order by age asc 时结果如下：,当执行查询 : select rowid,rownum,name,age from USER order by age desc 时结果如下：,4、修改检索结果中列的标题 【问题】检索emp表中的empno和ename信息，并修改ename列标题为姓名 方法有三： （1）使用column命令：column old_columnname heading new_columnname （2）将要显示的列标题写在列名后面，两者之间使用空格隔开 （3）将要显示的列标题写

5、在列名后面，两者之间使用AS关键字,5、在查询结果中显示字符串 【问题】检索emp表的信息，要求给出检索结果为： ENAME 职位 JOB - - - SMITH 职位: CLERK ALLEN 职位: SALESMAN WARD 职位: SALESMAN JONES 职位: MANAGER,6、使用WHERE限制检索的条件 【例】选择部门30中的所有员工. 【练习】找出不收取佣金或收取的佣金低于100的员工. 7、表达式作为SELECT语句中的列 【例】找出佣金高于薪金的60%的员工. 【练习】找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事

6、员但其薪金大于或等于2000的所有员工的详细资料.,select empno,ename,job, hiredate,sal, deptnofrom empwhere ( job=MANAGER and deptno=10)or( job=CLERK and deptno=20)or(job MANAGER and jobCLERK) and sal=2000);,8、使用ORDER BY子句重新排列检索结果 【升序例子】显示员工的详细资料,按姓名升序排序. 【降序例子】显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面. 也可以在ORDER BY子句中指定多个列 【例】显示所有

7、员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序. 9、使用IN（NOT IN）关键字 【例】检索部门编码为10、20的员工信息 方法一：使用逻辑运算符OR 方法二：使用IN关键字（比使用逻辑运算符更为简单）,select empno,ename from emp order by 1,2;,操作数据（续）,10、使用LIKE（NOT LIKE）关键字 通配符： %匹配包括0个或多个字符的字符串 _匹配任何一个字符 【问题】显示不带有R的员工的姓名. 【练习】显示姓名字段的任何位置包含A的所有员工的姓名. 【练习】显示姓名字段的包含两个“A”的所有员工信息. 【练习】显示姓名字

8、段的包含两个“/”的所有员工信息.,11、使用IS NULL关键字用于检索列中没有赋值的行 【问题】检索emp表中佣金未定的职员信息,12、使用BETWEENAND（NOT BETWEENAND）关键字 用于检索在某一特定范围内的信息 【问题】检索emp表中薪金大于等于2000并且小于等于3000的职员信息，要求显示员工编号和薪金（使用BETWEENAND） 也可以在WHERE子句中使用前面讲过的逻辑运算符检索某一范围内的信息,13、使用UNION子句用于将两个或多个检索结果合并成一个结果 【问题】找出emp表中所有经理(MANAGER)的详细资料找出emp表中所有办事员(CLERK)的详细资

9、料。并将这两个检索结果合并起来 使用时注意： （1）所有检索中的列数和列的顺序必须相同 （2）所有检索中按顺序对应列的数据类型必须兼容（相同或者可以明确地转换）,聚合函数,AVG：计算查询结果的平均值 MAX：用于查找最大值 MIN：用于查找最小值 SUM：计算查询到的数据值的总和 COUNT：计算查询到的结果的数目,操作数据（续）,14、使用GROUP BY子句 【问题】按部门编号统计每个部门的员工人数 说明 （1）该子句用于将检索结果按照GROUP BY后指定的列进行分组，该子句写在WHERE子句后面 （2）GROUP BY子句经常用于SELECT子句中包含有聚合函数的情况。此时，SELE

10、CT子句中选项列表中出现的列，只能是GROUP BY子句中的列或者包含在聚合函数中。,15、使用HAVING子句 【问题】按部门编号统计每个部门的员工人数，如何只统计职员人数多于4人的部门编号及其人数 说明HAVING经常用在GROUP BY子句之后，用于限定结果集中的分组 可以看到，HAVING子句是对结果进行过滤，而WHERE是对原始记录进行过滤。 注意HAVING子句中的列只能是GROUP BY子句中或者聚合函数中的列,16、使用子查询在一个查询中包含另一个查询 【例】检索薪金大于员工TURNER的薪金的员工编号、员工姓名及其薪金 【问题】检索薪金大于平均薪金的员工信息,内连接,Tabl

11、e products,Table sales,Select productname ，quantity from products ，sales where products. Productid = sales. Productid and quantity2000;,多表查询,17.相等连接、自然连接、带有选择条件的连接 问题:笛卡儿积 运算过程？ 在笛卡儿积的结果中，分析存在的问题以及解决方法： （1）两列deptno的列值不同 解决方法：加连接条件，成为相等连接 【注】为了避免笛卡儿积，必须在WHERE子句中给出表格的连接条件：通常对于N个表格的检索，要有N-1个连接条件 （2）出现了

12、相同的列： 解决方法：去掉相同的列，成为自然连接 （3）加入部门名称为“sales”的条件，成为带有选择条件的连接,18.非相等连接（比较连接） 连接条件不是等号，而是比较运算符 【问题】检索emp表中自己不是自己的上级主管的员工信息，要求显示员工编码、员工姓名、员工上级主管编码 select empno,ename,mgr from emp where emp.empnoemp.mgr,19.自连接 自己和自己的连接 【例】检索emp表中自己是自己的上级主管的员工信息，要求显示员工编码、员工姓名、员工上级主管编码 select empno,ename,mgr from emp where e

13、mp.empno=emp.mgr,20.外连接 上面介绍的都是内连接，以下是外连接： 【问题】检索员工的分布安排情况，要求显示部门编号、部门名称、员工编号、员工姓名和员工薪金。 思考：使用不同的外连接情况如何？ 1、左外连接 2、右外连接 3、全连接 4、交叉连接,Table users,Table groups,Table user_group,内连接连接3 个表 Select , from users,groups,user_group Where users.uid=user_group .uid and groups.gid=user_grou

14、p .gid ;,左外连接 Select * from users left join user_group on users.uid=user_group .uid;,右外连接 Select * from user_group right join groups using（gid）；,全外连接 Select * from user_group full join groups using（gid）；,交叉连接 Select * from users,user_group,groups ；,21.使用EXISTS关键字 如果要解决这样的问题： 【问题】检索已经安排有员工的部门信息 selec

15、t * from dept where exists (select * from emp where deptno=dept.deptno ); 使用EXISTS关键字测试跟随的子查询的行是否存在 要点 一旦找到，立即停止.找到则返回TRUE，否则返回FALSE，而不是返回 一些行 思考：检索没有安排任何员工的部门信息呢？,sql函数,Oracle10g提供了许多功能强大的函数，在编程中经常用到的有以下几类： 数学运算函数 字符串函数 统计函数 日期函数 分析函数 用户自定义函数,数值型函数,abs(x) ceil(x) floor(x) power(x,y) exp(y) mod(x,y)

16、 sqrt(x),ASCII(x) :返回字符表达式最左端字符的ASCII 码值。 CHR(n):将ASCII 码转换为字符。n为0 255的整数 CONCAT(c1,c2)：连接两个字符串，同c1|c2，c1，c2 字符型表达式 INITCAP(c)：返回字符串并将字符串中每个单词的第一个字母变为大写，其它字母小写，c字符型表达式。 LOWER(c)：将字符串全部转为小写 UPPER(c)：将字符串全部转为大写 LENGTH(c)：返回字符串的长度，多字节符(汉字、全角符等)，按1个字符计算 LENGTHB(c)返回字符串的长度;多字节符(汉字、全角符等)，按2个字符计算,字符型函数,字符型

17、函数,REPLACE(c1,c2,c3)：将字符表达式值中的部分字符串，替换成新的字符串。 SUBSTR(c1,n1,n2)：取子字符串，多字节符(汉字、全角符等)，按1个字符计算。在字符表达式c1里，从n1开始取n2个字符；若不指定n2，则从第n1个字符直到结束的字串,聚合函数对一组值执行计算并返回单一的值。除 COUNT 函数之外，聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。 所有聚合函数都具有确定性。任何时候用一组给定的输入值调用它们时，都返回相同的值。,聚合函数,求和函数SUM()：求和函数用于对数据求和，返回选取结果集中所有值的总和。语法如

18、下：SELECT SUM(column_name) FROM table_name 说明：SUM()函数只能作用于数值型数据，即列column_name中的数据必须是数值型的。如果列存在NULL值，则SUM函数会忽略该值。,聚合函数,计数函数COUNT()：COUNT()函数用来计算表中记录的个数或者列中值的个数，计算内容由SELECT语句指定。使用COUNT函数时，必须指定一个列的名称或者使用星号，星号表示计算一个表中的所有记录。两种使用形式如下。 COUNT(*)，计算表中行的总数，即使表中行的数据为NULL，也被计入在内。 COUNT(column)，计算column列包含的行的数目，如

19、果该列中某行数据为NULL，则该行不计入统计总数。,最大/最小值函数MAX()/MIN()：当需要了解一列中的最大值时，可以使用MAX()函数；同样，当需要了解一列中的最小值时，可以使用MIN()函数。,聚合函数,均值函数AVG()：函数AVG()用于计算一列中数据值的平均值。语法如下：SELECT AVG(column_name) FROM table_name 说明：AVG()函数的执行过程实际上是将一列中的值加起来，再将其和除以非NULL值的数目。所以，与SUM( )函数一样，AVG()函数只能作用于数值型数据，即列column_name中的数据必须是数值型的。,oracle中分析函数：

20、 rank()、dense_rank()和row_rumber()的使用,分析函数,问题：为每组的员工按薪金排序,select deptno,empno,ename,sal,rank() over (partition by deptno order by sal) from emp order by deptno;,select deptno,empno,ename,sal,dense_rank() over(partition by deptno order by sal) from emp order by deptno;,select deptno,empno,ename,sal,ro

21、w_number() over(partition by deptno order by sal) from emp order by deptno;,ROW_NUMBER()是没有重复值的，可以利用它实现分页显示。 DENSE_RANK()是连续排序，有两个第二名时仍然跟着第三名。 RANK()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。,分析函数-组内排序,列出各部门员工薪金最高的前3名,select * from ( select deptno, ename, sal, dense_rank() over ( partition by deptno order by

22、 sal desc ) topn from emp)where topn = 3order by deptno, sal desc;,select * from ( select deptno, ename, sal, row_number() over ( partition by deptno order by sal desc ) top3 from emp)where top3 = 3;,select * from ( select deptno, ename, sal, rank() over ( partition by deptno order by sal desc ) top

23、n from emp)where topn = 3order by deptno, sal desc;,分析函数-组内排序,sysdate add_months(d1,n1) last_day(d1) months_between(d1,d2),日期函数,To_char(n,format), To_char(d,format) To_date(c,format),转换函数,用户定义函数,语法格式 create or replace function function_name /*函数名称*/ (parameter_name1,mode1 datatype1, /*参数定义部分*/ param

24、eter_name2,mode2 datatype2, parameter_name3,mode3 datatype3, ) return return_datatype /*定义返回值类型*/ is/as begin function_body /*函数体部分*/ return scalar_expression /*返回语句*/ end function_name;,其中： function_name：用户定义的函数名。函数名必须符合标识符的定义规则，对其所有 者来说，该名在数据库中是唯一的。 parameter：用户定义的参数。用户可以定义一个或多个参数。 mode：参数类型。函数参数有3种类型。 IN 参数类型：表示输入给函数的参数。 OUT 参数类型：表示参数在函数中被赋值，可以传给函数调用程序。 IN OUT参数类型：表示参数既可以传值也可以被赋值。 datatype：用户定义参数的数据类型。 return_datatype：函数返回值的