Lesson3 Single Row Functions：单值函数

• A：函数分为：
  
  • 单值函数：
    1. 字符函数
    2. 日期函数
    3. 转换函数
    4. 数字函数
  • 分组函数：
• B：字符函数
  

  1、lower(字段名):字段中所有的字段值字符转化为小写 select lower(last_name) from s_emp; 2、upper(字段名):字段中所有的字段值都是为大写 select upper(last_name) from s_emp; 3、initcap(字段名):字段值的首字母大写 select initcap(last_name) from s_emp; 4、concat(args1,args2):连接字段和字段（字段和字符），使用嵌套达到多个字段或者多个字符相连接 select first_name||last_name from s_emp; select concat(first_name,last_name) from s_emp; 41、嵌套连接：first_name和'.'连接再和last_name连接 select concat(concat(first_name,'.')last_name) from s_emp; 5、substr(字段，字符下标):该字段字符值，字符下标从1开始，第3个下标截取（包括第3个） select last_name,substr(last_name,3) from s_emp; 51、字符下标可以为负数，字段值最后字符下标为-1 select last_name,substr(last_name,-1) from s_emp; 6、substr(字段，字符下标，字符个数):该字段字符值，字符下标从1开始，第3个下标截取（包括第3个），截取2个字符 select last_name,substr(last_name,3,2) from s_emp; 61、从-3下标正着截取2个字符 select last_name,substr(last_name,-3,2) from s_emp; 7、length(字段):返回字符串的长度 select length(last_name) from s_emp; 71、返回字符串的长度，并根据字符长度排序 select length(last_name) as name from s_emp order by 1; select length(last_name) as len from s_emp order by len; 8、nvl(出现null的字段，数字（字符）):替换null值 select last_name,nvl(commission_pct,0) from s_emp;

  
  
• C：dual哑表：dual是一个虚拟表，没什么实际作用，使select语法完成；sys数据库管理员拥有，oracle保证dual里面永远只有一条记录。
  

  select upper('hello') from s_emp; select upper('hello') from dual; select 1+1 from dual;

  
  
• D：数字函数：
  

  1、round(数字,位数下标):小数点后保留args2位 select round(45.9234,2) from dual; 11、小数点后保留args2位，args2+1位按照四舍五入规则 select round(45.9254,2) from dual; 12、小数点后保留0位 select round(45.9254,0) from dual; select round(45.9254) from dual; 13、保留到十位 select round(45.9254,-1) from dual; 十位14、保留到百位 select round(45.9254,-2) from dual; 百位2、trunc(数字,位数下标)：指定具体位数后面截掉 百位以后截掉 select trunc(45.9254,-2) from dual; 21、十位以后截掉 select trunc(45.9254,-1) from dual; 22、个位以后截掉+1（例如：电话计时） select trunc(45.9254,0)+1 from dual; 23、个分位以后截掉 select trunc(45.9254,1) from dual; 24、 百分位以后截掉 select trunc(45.9254,2) from dual; 3、mod(被除数,除数):两数相除取余数 select mod(3,10) from dual; 31、10%3=1 select mod(10,3) from dual;

  
  
• E：日期函数
  
  • 默认日期格式 DD-MON-YY(显示结果跟语言环境有关)，包含时分秒只是没显示。
    
    • ‘06-9月-19’
      
    • ‘06-SEP-19’
      

      1、sysdate:系统当前时间，根据语言环境，按照指定格式显示 select sysdate from dual; 11、当前时间的后1天 select sysdate+1 from dual; 12、当前时间的后1个月（不一定是30，根据年份的具体月份的天数） select sysdate+30 from dual; 13、当前时间的后1年（闰年为366） select sysdate+365 from dual; 14、当前时间的后1小时 select sysdate+1/24 from dual; 15、当前时间的后1分钟 select sysdate+1/(24*60) from dual; 2、months_between(time1,time2):time1和time2的时间差几个月 1 select months_between(sysdate+30,sysdate) from dual; 21、 .967741935 select months_between(sysdate+29,sysdate) from dual; 22、 1.03225806 select months_between(sysdate+31,sysdate) from dual; 3、add_months(sysdate(固定格式时间字符),月份个数) select add_months('06-9月-19',2) from dual; ADD_MONTHS('06-9月-19',2) 06-11月-1931、当前时间减少2个月 ADD_MONTHS('06-9月-19',-2) 06-7月 -19 select add_months('06-9月-19',-2) from dual; 4、next_day(当前时间,'星期'):指离定星期的最近的一天 select next_day(sysdate,'星期五') from dual; 5、last_day(sysdate(固定时间字符)):指定时间的月份的最后一天 select last_day(sysdate) from dual; 51、 select last_day('06-9月-19') from dual; 6、round(字段)： 61、月份：15舍16进 select round(sysdate,'MONTH') from dual; select round(sysdate+16,'MONTH') from dual; 62、年：6月舍7月进select round(sysdate,'YEAR') from dual; select round(sysdate-90,'YEAR') from dual; 63、天： select round(sysdate,'DAY') from dual; 7、trunc(时间,'YEAR'):当前时间一年最开始 TRUNC(SYSDATE, -------------- 01-1月 -19 select trunc(sysdate,'YEAR') from dual; 72、指定时间的月份的第一天是 TRUNC(SYSDATE, -------------- 01-9月 -19 select trunc(sysdate,'MONTH') from dual; 73、指定时间天数的一个星期的第一天 TRUNC(SYSDATE, -------------- 01-9月 -19 select trunc(sysdate,'DAY') from dual;

      
      
• F：转换函数：
  
  • rr：千年虫规则（只能确定当前时间的前后50年年份）

  1、to_char：将数字或者时间转化为字符 11、时间转化为字符（有固定格式） yyyy：2019 rrrr：2019 yy：19 rr：19(千年虫：遇到跨越世纪问题，yy是用两个字符保存年份的，所以yy无法确定19年是哪个世纪的，rr有自己的规则) mm：09 月份 D：6一星期中的第几天（从星期日开始） DD：06一个月中的第几天 DDD:249一年中的第几天 YEAR：TWENTY NINETEEN英文 MONTH：9月（SEPTEMBER）英文 mon：9月（sep）英文 ddsp:six(一个星期中的第几天)英文 ddspth:sixth(英文序列数的第几天(一个月的)) DAY：全英文的星期 DY：简写的英文星期 hh：小时 mi：分钟 ss：秒12、测试常见的一些日期数据转换为字符串的格式 select to_char(sysdate,'yyyy rrrr yy rr mm D DD DDD YEAR MONTH mon ddsp ddspth DAY DY') from dual; 2019 2019 19 19 09 6 06 249 TWENTY NINETEEN 9月9月six sixth 星期五 星期五13、03 29 53：3点29分53秒 select to_char(sysdate,'hh mi ss') from dual; 14、15 29 53：15点29分53秒（24小时制） select to_char(sysdate,'hh24 mi ss') from dual; select to_char(sysdate,'hh24 mi ss am') from dual; 15、07-09-19 select to_char(sysdate,'dd-mm-yy') from dual; 16、19-09-07 select to_char(sysdate,'yy-mm-dd') from dual; 17、17-9月-19 select to_char(sysdate,'dd-mon-yy') from dual; 18、07-09-19 15:34:40 下午（07-09-19 15:35:04 PM） select to_char(sysdate,'dd-mm-yy HH24:MI:SS AM') from dual; select to_char(sysdate,'dd-mm-yy hh24:mi:ss am') from dual; 2、to_char数字转化为字符9:根据数字实际长度显示 0：根据0所占位数显示 $：美币货币符号 L：本地货币符号 .：小数点 ,：位数，每三位字符有',' fm:设置字符靠左对齐21、个位以前可显示6位实际长度，小数位显示固定两位，字符默认靠右 TO_CHAR(SALARY,'$999,999.00') ------------------------ $2,500.00 $1,450.00 select to_char(salary,'$999,999.00') from s_emp; 22、靠左对齐 TO_CHAR(SALARY,'FM$999,999.00') ------------------------ $1,490.00 $1,515.00 select to_char(salary,'fm$999,999.00') from s_emp; 23、本地钱币符号 TO_CHAR(SALARY,'FML999,999.00') ------------------------------------------ ￥2,500.00 ￥1,450.00 select to_char(salary,'fmL999,999.00') from s_emp; 3、to_number：字符转化为数字，进行计算 select to_number('1000') from dual; 4、to_date:固定日期格式字符串转化为日期 41、中，英文环境下都可以 MYDATE ------------ 10-DEC-19 select to_date('10-12-2019','dd-mm-yy') as mydate from dual; 42、中文环境下 MYDATE -------------- 10-12月-19 select to_date('10-12月-2019','dd-month-yy') as mydate from dual; select to_date('10-12月-2019','dd-mon-yy') as mydate from dual; 43、中文环境下 MYDATE -------------- 09-12月-19 select to_date('2019/12月/9','yy/month/dd') as mydate from dual; select to_date('2019/12月/9','yy/mon/dd') as mydate from dual;

  
  
• F：注意：以上函数可以嵌套使用
  
  • 格式:F3(F2(F1(arg0,arg1),arg2),arg3)
  • select substr(upper(concat(‘hello’,‘world’)),4,4)from dual;
  • 先把’hello’和’world’连接起来,再转换为字母大写然后再从第4个字符开始,连着截取4个字符

Lesson4 Displaying Data from Multiple Tables：多表查询

• A：多表查询：又称联合查表，即一条sql语句涉及到的表有多张，数据通过特定的连接进行联合显示
  
• B：笛卡尔积：在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y.
  
  • 假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
  • 在数据库中，如果直接查询两张或者多张表的时候就会产生笛卡尔积：select last_name,dept_id from s_emp,s_dept;
• C：连接查询：
  
  • a、内连接
    
    1. 等值连接
       

       11、s_emp和s_dept两张表的通过部门id进行等值连接 select last_name,dept_id,name from s_emp se,s_dept sd where se.dept_id=sd.id ; 12、 select last_name,dept_id,s_dept.id,name from s_emp,s_dept where s_emp.dept_id=s_dept.id ; 13、table1 inner join table2 on 条件 select last_name,dept_id,s_dept.id,name from s_emp inner join s_dept on s_emp.dept_id=s_dept.id ;

       
       
    2. 不等值连接
       

       假设数据库中还有一张工资等级表:salgrade 工资等级表salgrade: gradeName列表示等级名称 losal列表示这个级别的最低工资数 hisal列表示这个级别的最高工资数表中的数据类似于下面内容: idsalgrade losal hisal 1初级程序员20004000 2中级程序员40006000例如: 查询出员工的名字、职位、工资、工资等级名称 SELECTe.last_name, e.title, e.salray, s.gradeName FROMs_emp e, salgrade s WHEREe.salray BETWEEN s.losal AND s.hisal

       
       
  • b、外连接：解决两张表关联时候，数据没有显示出来的三种情况
    

    前提：特点:新员工tom不在任何部门,新增部门st下面没有任何员工 insert into s_emp(id,last_name) values(26,'tom'); insert into s_dept(id,name) values(60,'st'); commit; 1、左外链接:tom的dept_id为null，在等值连接的时候，s_emp表没有显示tom员工 oracle自带左外连接s_emp.dept_id=s_dept.id(+) 11、t1 left outer join t2 on 等值连接条件 select last_name,dept_id,s_dept.id,name from s_emp left outer join s_dept on s_emp.dept_id=s_dept.id ; 12、可以省略outer关键字 select last_name,dept_id,s_dept.id,name from s_emp left join s_dept on s_emp.dept_id=s_dept.id ; 13、s_dept.id(+)：该字段主键作为其他表的FK为空时候，显示FK为空表的字段（左外链接）。 select last_name,dept_id,s_dept.id,name from s_emp,s_dept where s_emp.dept_id=s_dept.id(+); 2、右外连接:s_dept的PK主键id作为s_emp的FK外键dept_id；s_emp中一个dept_id值都没有st部门id oracle自带右外连接s_emp.dept_id(+) = s_dept.id 21、t1 right outer join t2 on 等值连接条件 select last_name,dept_id,s_dept.id,name from s_emp right outer join s_dept on s_emp.dept_id=s_dept.id ; 22、outer可以省略 select last_name,dept_id,s_dept.id,name from s_emp right join s_dept on s_emp.dept_id=s_dept.id ; 23、s_emp的外键dept_id中都没有s_dept中的部门id，显示这个部门id select last_name,dept_id,s_dept.id,name from s_emp,s_dept where s_emp.dept_id(+) = s_dept.id; 3、全连接：t1 full outer join t2 on 等值连接条件; 显示s_emp中没有dept_id的 显示s_emp中不包含s_dept主键id select last_name,dept_id,s_dept.id,name from s_emp full outer join s_dept on s_emp.dept_id=s_dept.id ; 31、outer可省略 select last_name,dept_id,s_dept.id,name from s_emp full join s_dept on s_emp.dept_id=s_dept.id ;

    
    
  • c、自连接
    

    select id,last_name,manager_id from s_emp; s_emp表（自己再连接自己）：id,manager_id进行等值连接 例如： ID LAST_NAMEMANAGER_ID 1 Velasquez 2 Ngao1 3 Nagayama1 4 Quick-To-See1 5 Ropeburn1 6 Urguhart2 7 Menchu2 1、两张表s_emp s1,s2,id可以作为manager_id select s1.last_name,s1.manager_id,s2.id,s2.last_name from s_emp s1,s_emp s2 where s1.manager_id=s2.id; 11、显示没有manager_id的员工（左连接显示领导） select s1.last_name,s1.manager_id,s2.id,s2.last_name from s_emp s1,s_emp s2 where s1.manager_id=s2.id(+); 12、将manager_id为null的显示为领导 select s1.last_name,s1.manager_id,s2.id,nvl(s2.last_name,'领导') as manager from s_emp s1,s_emp s2 where s1.manager_id=s2.id(+); 13、显示manager_id不包含id的员工（右外连接不是领导的员工） select s1.last_name,s1.manager_id,s2.id,s2.last_name from s_emp s1,s_emp s2 where s1.manager_id(+)=s2.id;

    
    
• D：查询结果集操作：如果有俩条sql语句,每一条sql都可以查询出一个结果,这个被称之为结果集。
  
  • 使用下面的关键字对2个结果集进行操作
    
    1. union：取两个结果集的并集
    2. union all：2个结果集放在一起显示出来
    3. minus：第1个结果集 减 第2个结果（两个结果集相同的部分）
    4. intersect：2个结果集的交集
  • 注意：两个结果集的查询的字段（列）要完全一致
    

    1、union:2个结果集的并集 select last_name,dept_id,name from s_emp,s_dept sd where dept_id=sd.id(+) union select last_name,dept_id,name from s_emp,s_dept sd where dept_id(+)=sd.id; 2、union all:2个结果集一起显示 select last_name,dept_id,name from s_emp,s_dept sd where dept_id=sd.id(+) union all select last_name,dept_id,name from s_emp,s_dept sd where dept_id(+)=sd.id; 3、minus:第1个结果集去除第2个结果集两者相同的部分 select last_name,dept_id,name from s_emp,s_dept sd where dept_id=sd.id(+) minus select last_name,dept_id,name from s_emp,s_dept sd where dept_id(+)=sd.id; 31、只显示没有员工的部门st select last_name,dept_id,name from s_emp,s_dept sd where dept_id(+)=sd.id minus select last_name,dept_id,name from s_emp,s_dept sd where dept_id=sd.id(+); 4、intersect:2个结果集的交集部分 select last_name,dept_id,name from s_emp,s_dept sd where dept_id=sd.id(+) intersect select last_name,dept_id,name from s_emp,s_dept sd where dept_id(+)=sd.id;

    
    where dept_id=sd.id(+)
    minus
    select last_name,dept_id,name
    from s_emp,s_dept sd
    where dept_id(+)=sd.id;
    

    31、只显示没有员工的部门st select last_name,dept_id,name from s_emp,s_dept sd where dept_id(+)=sd.id minus select last_name,dept_id,name from s_emp,s_dept sd where dept_id=sd.id(+);

    
    【Oracle|Oracle--2--单值函数，多表查询（联合查询）】4、intersect:2个结果集的交集部分
    select last_name,dept_id,name
    from s_emp,s_dept sd
    where dept_id=sd.id(+)
    intersect
    select last_name,dept_id,name
    from s_emp,s_dept sd
    where dept_id(+)=sd.id;
    
    
    

推荐阅读

• 

  多肉晚霞的花语和寓意 的花语是什么
• 

  反复打火对汽车有损害吗
• 

  女人性冷淡的偏方
• 

  打新冠疫苗可以干重活吗
• 

  新皮包怎么去除味道
• 

  蔓性风铃花常见病 巴西宫灯蔓性风铃花
• 

  三菱变频空调压缩机有时不启动怎么办,一不小心就会犯错
• 

  芒果和什么一起吃过敏
• 

  2017年好看的电影有哪些 2017十部必看电影排行榜
• 

  宝山区酒店中央空调不制冷怎么办,想过这些方面的原因吗？
• 

  冬小麦拔节期多长时间 冬小麦拔节期怎么追肥
• 

  建设工程优先受偿权能对抗房屋买受人吗？
• 

  如何使用命令提示符修复Win10电脑？
• 

  红楼梦是哪个朝代的故事
• 

  王者由英雄名字延伸的游戏ID,婉儿尚能忍受,看到王昭君的直接笑喷。怎么评价？
• 

  太阳对煤炭作用介绍
• 

  缓解咳嗽吃梨和什么好？
• 

  mysql加了索引但是不起作用 mysql强加索引
• 

  玉米须子煮水的功效与作用 用玉米须煮水喝的功效
• 

  97热

• Navicat|Navicat 被投毒了 | 调查结果来了
• Oracle|Oracle主键自增设置
• oracle|oracle 查看表空间及用户,oracle 表空间及查看所有用户的表空间
• Oracle|运维工程师常用的Oracle经典语句——5.Oracle表空间创建、修改以及查看表空间使用情况
• 数据库|Oracle查看表空间及使用情况
• Oracle学习笔记|PLSQL - 递归子查询RSF打破CONNECT BY LOOP限制
• Oracle-触发器和程序包
• Oracle|Oracle/PLSQL: Months_Between Function
• Oracle RAC模式下如何部署NTP