实施文件oracle教程课件6-4、数据查询

1、Oracle 数据查询宋明治新道学院讲师目标理解查询的机制查询所有列查询指定列模糊查询分组查询多表查询-内连接查询内置函数数据查询机制客户程序查询请求查询结果集OracleA B C D E F G select name, pay from emp.salary查询产生一个虚拟表，看到的是以表的形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来数据查询-基础查询全部的行和列select * from zhang.学生表 查询部分行select 学号,成绩,课程 from zhang.成绩表select * from zhang.成绩表where

2、成绩70条件查询数据查询-列名去除重复行select distinct 学号from zhang.成绩表模糊查询like查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容select 姓名 from zhang.学生表 where 姓名 like 张%姓名张一航张莉张宇模糊查询between and把某一字段中内容在特定范围内的记录查询出来select 学号, 分数 from zhang.成绩表 where 分数 between 60 and 80学号分数77786011761780.模糊查询in把某一字段中内容与所列出的查询内容列表匹配的记录查询出来Select 姓名,

3、地址 from zhang.学生表 where 地址 in (北京,广州,上海)学员姓名地址李扬广州于紫电上海李青霜北京司马弓上海.问题成绩表中存储了所有学员的成绩，我想知道：学员的总成绩、平均成绩、有成绩的学员总共有多少名怎么办？聚合函数SUM 求和AVG 求平均值select SUM(成绩) from zhang.成绩表select avg(成绩) from zhang.成绩表聚合函数MAX、MIN 求最大值、最小值COUNT 求有记录的行数select max(成绩), min (成绩)from zhang.成绩表select count (*) from zhang.成绩表数据查询-定

4、义别名定义列别名select sum(成绩) as 总成绩 from zhang.成绩表数据查询-排序升序排列select 学号, 成绩from zhang.成绩表order by 成绩降序排列select 学号, 成绩from zhang.成绩表order by 成绩 descOracle常用函数Oracle中函数划分为单行函数和多行函数单行函数作用于数据库表的某一行并返回一个值字符函数数字函数日期函数转换函数其他函数。多行函数基于数据库表多行进行运算，返回一个值例如对多行记录的某个字段进行求和、求最大值运算等Oracle常用函数常用的字符函数函 数功 能示 例结 果INITCAP (cha

5、r)首字母大写initcap (hello)HelloLOWER (char)转换为小写lower (FUN)funUPPER (char)转换为大写upper (sun)SUNLTRIM (char, set)左剪裁ltrim (xyzadams, xyz)adamsRTRIM (char, set)右剪裁rtrim (xyzadams, ams)xyzadTRANSLATE (char, from, to)按字符翻译translate (jack, abcd, 1234)j13kREPLACE (char, search_str, replace_str)字符串替换replace (jac

6、k and jue, j, bl)black and blueINSTR (char, substr, pos)查找子串位置instr (worldwide, d)5SUBSTR (char, pos, len)取子字符串substr (abcdefg,3,2)cdCONCAT (char1, char2)连接字符串concat (Hello, world)HelloworldOracle常用函数常用的数字函数函 数功 能示 例结 果ABS(n)取绝对值abs(-15)15CEIL(n )向上取整ceil(44.778)45SIN(n)正弦sin(1.571).999999979COS(n)余

7、弦cos(0)1SIGN(n)取符号sign(-32)-1FLOOR(n)向下取整floor(100.2)100POWER(m,n )m的n次幂power(4,2)16MOD(m,n)取余数mod(10,3)1ROUND(m,n)四舍五入round(100.256,2)100.26TRUNC(m,n)截断trunk(100.256,2)100.25SQRT(n)平方根sqrt(4)2Oracle常用函数常用的日期函数函 数功 能示 例结 果MONTHS_BETWEEN返回两个日期间的月份months_between (04-11月-05,11-1月-01)57.7741935ADD_MONTH

8、S返回把月份数加到日期上的新日期add_months(06-2月-03,1)add_months(06-2月-03,-1)06-3月-0306-1月-03NEXT_DAY返回指定日期后的星期对应的新日期next_day(06-2月-03,星期一)10-2月-03Oracle常用函数常用的日期函数函 数功 能示 例结 果LAST_DAY返回指定日期所在的月的最后一天last_day(06-2月-03)28-2月-03ROUND按指定格式对日期进行四舍五入round(to_date(13-2月-03),YEAR) round(to_date(13-2月-03),MONTH)round(to_dat

9、e(13-2月-03),DAY)01-1月-0301-2月-0316-2月-03 TRUNC对日期按指定方式进行截断trunc(to_date(06-2月-03),YEAR)trunc(to_date(06-2月-03),MONTH)trunc(to_date(06-2月-03),DAY)01-1月-0301-2月-0302-2月-03CURRENT_DATE当前日期Oracle常用函数常用的转换函数函 数功 能示 例结 果TO_CHAR转换成字符串类型to_char(1234.5, $9999.9)$1234.5TO_DATE转换成日期类型to_date(1980-01-01, yyyy-m

10、m-dd)01-1月-80TO_NUMBER转换成数值类型to_number(1234.5)1234.5Oracle常用函数常用的其他函数函 数功 能NVL(EXP1, EXP2)如果exp1的值为null，则返回exp2的值，否则返回exp1的值NVL2(EXP1, EXP2, EXP3)如果exp1的值为null，则返回exp2的值，否则返回exp3的值DECODE(VALUE,IF1,THEN1,IF2,THEN2,ELSE) 如果value的值为if1，则返回then1的值，如果value的值为if2，则返回then2的值，否则返回else值分组查询如果想知道，某个人的总成绩或某科目的

11、平均成绩，该如何获得？分组查询分组查询select 课程,avg(成绩) as 单科平均成绩 from zhang.成绩表group by 课程案例分析1要求某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题： 卡里面的“O和0”（欧和零），“I和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“I”都改成“1”；请编写SQL语句实现以上要求；数据库表名：Card；密码字段名：PassWord；案例分析1分析这是更新语句，需要使用UPDATE语句；因为牵涉到字符串的替换，需要使用到Oracle中的函数Replace；案例分析1S

12、QL*Plus两行SQL语句一行SQL语句Update stu.Card Set PassWord = Replace(PassWord ,O,0)Update stu.Card Set PassWord = Replace(PassWord ,i,1)Update stu.Card Set PassWord = Replace(Replace(PassWord ,O,0),i,1)多表联结查询问题学员内部测试成绩查询的每次显示的都是学员的编号信息，因为该表中只存储了学员的编号；实际上最好显示学员的姓名，而姓名存储在学员信息表；如何同时从这两个表中取得数据多表连接查询分类内连接(inner j

13、oin)外连接左外连接(left join)右外连接(right join)完整外连接(full join)多表连接查询内连接SELECT S.SName,C.CourseID,C.Score From zhang.Score AS CINNER JOIN zhang.Students AS SON C.StudentID = S.SCodeSNameSCod赵毅1钱风2孙离3李末4StundentsStudentsIDCourseIDScore100197200289200267300376300381ScoreSNameCourseIDScore赵毅00197赵毅00189钱风00267钱

14、风00276孙离00381查询结果多表连接查询内连接学生表select 学生表.学号,姓名,年龄,性别,成绩 from zhang.学生表 join zhang.成绩表 on 学生表.学号=成绩表.学号成绩表多表连接查询左外连接成绩表学生表select 学生表.姓名,成绩表.课程编号,成绩表.成绩 from zhang.学生表 left join zhang.成绩表on 学生表.学号=成绩表.学号多表连接查询右外连接成绩表学生表select 学生表.学号,姓名,年龄,性别,成绩 from zhang.学生表 right join zhang.成绩表 on 学生表.学号=成绩表.学号数据查询-限制行数限制固定行数select 学号, 姓名,Rownumfrom (select 学号, 姓名 from zhang.学生表Order by 学号, 姓名)Where Rownum=5;案例分析2要求一家银行发行了新的信用卡，刚开始的时候推广得很好，但是逐渐废卡也越来越多（卡上的余额少于2元，并且用户长时间不使用该卡），因此银行在二月份把这些少于2元的卡从都数据库表中删除了，但是很快问题就来了，用户发现他的卡再也不能使用而投诉，因此只能再把这些卡恢复。编写SQL，把CardID为23、30等在子表中没有的记录插入到子表中