11月24日——培训第4天

宿舍越来越冷了，床头窗户漏风，需要用报纸把它们给糊上才行啊，要不然过几天一旦大风降温我们就完蛋了。最好不要闹到
搬家的地步，那么多的书实在是太重了，我可搬不动啊

今天很早就到了，我们是第一批到的，系统已经全部完成，以刘栋的机器为模板全部机房重新装了一遍，装后都是他初始的配置，里面竟然还有北大青鸟accp3.0的ppt资料！呵呵……没想到啊，而且更令人兴奋的是，现在的usb接口似乎已经换成是2.0的了，传输速度那是相当的快啊……

张老师今天早上过来告诉我们，已经有一个一期班的学员找到了月薪6000元的工作，这我并不感到吃惊，一期班里面本来就有这种以前就过业的或是出色的本科未毕业的应届生，这没有什么可奇怪的。但是不想让他这么早去工作，毕竟还有一个月的时间，而且需要学员帮助打开培训市场……刚刚知道，原来那个月薪6000的就是那个敲代码比其他人打字速度还要快的家伙，而且日语一级，那确实是没有什么悬念了。

现在机器上的oracle和盘符起了冲突，需要重启……

通过删除监听器以及重新建立监听器的方法，终于恢复了oem的登陆使用，刚才张老师业向我们着实炫耀了一番，表明了希望通过一期学员的成果向企业打出招牌的信心，感觉是不是有些过分乐观了呢？不过至少值得放心的是，我们这个班的管理和他们不会有太大的差别，只是课程设置方面而已。

netmeeting看来是没有什么问题了，希望别再出问题啊……这可真是让人吃不消。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

netMeeting顺利启动了，一切看样子都没有什么问题

马瑞爽发现了一个sqlplus的使用窍门，是有关round函数的使用的

例：select round(45.932,2) from dual;  得到的结果是45.93。

如果要换成对55.932取两位小数呢，则在下面敲打c /4/5，回车后再打run就可以了。（这是在网上查到的，有很多这种窍门）

其实上例中的c就是change命令，使用help index查看命令，再使用help "具体命令"  就可以查看具体命令了，如果看不明白的话

可以上网查询，都是能够查到的。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

上午课程正式开始，

order by语句：

select first_name , hire_date , salary from employees orderby hire_date , salary desc ;（先按照hire_date升序排列
如果hire_date一样的话，按照降序对salary进行排列）。

注意desc只对salary有作用！必须为每一个orderby后的参数都提供升序还是降序！

单行函数：

比如concat函数，单行函数是对每一行都进行运算，试验单行函数可以使用dual表；

initcap函数：首字母大写效果。

select initcap('l.amc.oop') from dual ; 结果是L.Amc.Oop，当然也可以用一切非数字非字母的字符来分隔字符串，可以起到同样的

首字母大写的效果。

lower()和upper()函数用于将括号内的用单引号括起来的字符串全部小写化或大写化。

select substr('helloworld',0,5) from dual ; 结果是hello，如果把0改为1结果一样，但是如果改为-1的话答案是d，因为
substr函数的意义是从右边第一个参数开始截取，截到第五个……但是由于d是最右边的字母，所以……只能取出d一个字母。

select substr('helloworld',instr('helloworld','or'),5) from dual;
这句话的意思是先看or这个字符串第一次在helloworld中出现的位置，再从这个位置开始往后面截取5位字符。
注意：如果from后面接的表不是dual而是employees的话那么结果会出现107行呢。

select substr('helloworld',instr('helloworld','l',1,2)) from dual;
这句话的意思是从helloword字符串的第二个l开始把后面的都截取下来；instr('helloworld','l',1,2)的意思是从第1个字符
开始判断，找到第二个l字符出现的位置并且返回整数值。instr(string,substring,position,occurrance).

select substr('helloworld',instr('helloworld','l',4,2)) from dual;
这句话和上面不同的地方是从第四个字符开始搜索第二个l在字符串中出现的位置，这样搜到的肯定是helloworld的第三个字符l，
然后substr截取到的毫无一位就是ld，因为substr的第三个参数没有写，所以是把剩下都截取了。

注意：如果instr(string,substring,position,occurrance)语句的position为负5，则是从母串的右边开始的第五个字符开始往左边找，
occurrance是待查找子串在目串中出现的位置。具体可以查看帮助文档。

所以，substr(截取子串函数),instr(查找子串出现的位置),以及length(查出字符串长度)在数据库字符串操作中是十分有用的！

 

select max(length(first_name)) from employees;返回员工表中首名字最长的长度。

select lpad(first_name,11,'-') from employees;这个语句用来把员工首名字都变成11位，不够11位的在左边补'-'
当然也可以补充字符串，注意：如果字符串本身超过了11位，那么他肯定截取前11位。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

作业第一题：
在employees表中查询last_name字段，要求总长度是20字符，并且必须以4个'*'开头，不足的部分要在右边补上'*'
select rpad(concat('****',last_name),20,'*') from employees ;
select rpad('**yuan'||last_name||'**',20,'*') from employees ; 也可以这么来处理左边加上字符串的情况，用||可以处理
多个字符串的连接，但是呢，这个||别的数据库并不支持，最好还是用concat函数吧……

select rpad(lpad(last_name,length(last_name)+4,'*'),20,'*') from employees ;  这种做法也是可以的，lpad函数可以根据
length函数来对具体的字符串显示长度进行量身定做，这样lpad函数做到让每个last_name都可以达到左边先加上4个"*"，然后再用
rpad截取，这样会保留左边的所有。

作业第二题：
2、在employees表中查询first_name与last_name组成的完整姓名。显示姓名的总长度为20，姓名部分为15，不足部分补'.'。
   如果姓名长度超过了15，则只显示姓名的前15个字符，后五位仍然补'.'。

select rpad(rpad(first_name||' '||last_name,15),20,'.') from employees ;
这道题目其实利用了一个特点，那就是如果第三个参数不填写的话，那么默认会填充空格。

作业字符串函数第二题：

使用问题：截取helloworld字符串中的第二个l和第三个l之间的子串。

select substr('helloworld',instr('helloworld','l',1,2),instr('helloworld','l',1,3)-instr('helloworld','l',1,2)+1) from dual;
当然也可以用两个substr函数达到目的，第一个substr函数截取第三个l之前的字符串a，第二个substr函数截取字符串a中的第二个l后面的
子串就可以了。

作业第三题：
3、在employees表中查询first_name与last_name组成的完整姓名。显示姓名的总长度为15，如果姓名长度小于15，补'.'
   以表示姓名被完整显示，如果姓名长度大于15，则从第12位开始截取并补'*'以表明姓名被截取。

 

trim函数：

select trim('l' from 'lllwwlllll') from dual ; 结果是ww
select ltrim('lllwwllll','l') from dual ; 结果是wwllll
select rtrim('lllwwllll','l') from dual ; 结果是lllww

select trim(leading 'l' from 'lllwwlllll') from dual ; 结果是wwlllll
select trim(trailing 'l' from 'lllwwlllll') from dual ; 结果是lllww

数学函数round，trunc，mod
round(45.926,2) ;       45.93(如果是0的话就是46，是-1的话就是50了，是-2的话就是0)
trunc(45.926,2) ;       45.92(如果是0的话就是45，是-1的话就是40了，是-2的话就是0)
mod(1600,300);          100

语句show parameter nls_  可显示oracle加载的函数，里面有日期函数，nls就是native language support(本地化的语言支持)
日期类型不同于string类型，显示时是由oracle自己自动转换的……日期可以进行数学运算：

select sysdate from dual ;   显示当前日期。
select sysdate + 100 from dual;  今天日期以后的100天的日期。

select months_between(sysdate,'16-4月-1984') from dual ;自己的生日到今天过了多少个月？？（可以在前面加个round函数）

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

下午了，课程继续，讲时间日期：
select to_char(sysdate,'yyyy') from dual;   结果是2006
select to_char(sysdate,'yyyysp') from dual;   结果是tow thousand and sex
这种方法可以将日期转化为特定格式要求的字符串
select to_char(sysdate,'yyyy"年"') from dual; 结果是200年

select to_char(sysdate,'mm') from dual;  结果是11（今天是11月）
select to_char(sysdate,'month') from dual ; 结果是11月
select to_char(sysdate,'mon') from dual ; 结果仍然是11月

select months_between(sysdate,to_date('1977-11-21','yyyy-mm-dd')) from dual; 也能得到答案

select to_char(sysdate,'day') from dual ;  得到星期五
select to_char(sysdate,'dd') from dual;    得到24(一个月里面的第24天)
select to_char(sysdate,'dy') from dual ;   得到星期五

select to_char('16-4-1984','day') from dual ;

to_char（将日期按照指定格式转化为字符串）的第一个参数必须是date型，第二个参数是转换的日期格式（具体参见老师给的英文手册中第二章节的format model）

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
这样可以得到当前日期的年月日时分秒

 

to_date(将字符串转换为日期，为了日期之间的运算或是插入数据库之前，
都需要调用这个函数)

select to_char(sysdate-to_date('1984-4-16'，'yyyy-mm-dd')) from dual ;
计算我的出生日期到今天过了多少天了，返回的是个小数。

select to_char(to_date('1977-11-21','yyyy-mm-dd'),'day') from dual;
用来判断1977年11月21日是星期几

select to_char(hire_date,'day') from employees;  选出所有员工hire_date是的星期几。

select sysdate-to_date('1977-11-21','yyyy-mm-dd') from dual ;
出生日期到现在是多少天。

select to_char(to_date('1977-11-21','yyyy-mm-dd')+10000, 'yyyy-mm-dd') from dual ;
出生日期后的10000天的准确日期是多少

select months_between(sysdate,'16-4月-1984') from dual ;自己的生日到今天过了多少个月？？（可以在前面加个round函数）

to_date和to_char日期格式之间的相互转换很重要！

 

现在有个问题，如何设置oracle默认日期格式为'yyyy-mm-dd'?

可以输入命令alter session set nls_date_format='yyyy-mm-dd';
就可以了，这样以后得到的日期就是设置的型号了，但是仅仅在当前会话中有效

select to_char(sysdate,'yyyy-mm-dd-day','nls_date_language=american') from dual;

输出是 2006-11-24-friday。
注意如果没有第三个参数，那么输出的肯定是2006-11-24-星期五

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
现在是课间休息时间，刚刚得知有个学员，也是北航的，今天没有来，也没有请假，为这个事情方老师还特意下午出去找他，
然后张老师接了个电话，说是因为他基础太差，觉得我们都太牛了，想回去好好补补基础再来……这话我只能相信50％，基础差
不假，但要说我们太牛了，这从何说起呢？有的时候是不能够太在意别人的，我以前不也是那样么……脸皮得厚一点，该干啥干
啥，别人是别人的事情，别跟着瞎掺和，一点意思都没有，心思全用在别人身上有什么用，多花点心思在自己身上有什么不好……
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
休息结束

select to_char(sysdate,'ddspth') from dual;
返回twenty-fourth

select to_char(sysdate,'mmspth') from dual;
返回eleventh

select to_char(sysdate,'yyyyspth') from dual;
返回two thousand sixth

select to_char(sysdate,'ddsp') from dual;
返回two thousand six

select to_char(sysdate,'yyyyth') from dual;
返回two thousand sixth

其实sp的意思就是spell out ，th就是日期后面的th

这些to_char中的第二个参数都是日期格式，可以查阅相关文档。

select to_char(to_date('77-11-21','yy-mm-dd'),'yyyy-mm-dd') from dual;
返回的是2077-11-21

select to_char(to_date('77-11-21','rr-mm-dd'),'yyyy-mm-dd') from dual;
返回的就是1977-11-21

rr的作用：如果只是给了年份的缩写，那么rr会自己判断，假如给了个17，那么
rr会取判断当前的年份和谁比较近，是和1900年近呢，还是和2000年近，如果和
1900年近的话就是1917，否则就是2017了；

而yy则会出现问题，具体rr的算法并不是这样设定的，具体算法可参看英文ppt的
第128页（总共578页）

yy就是看你当前在哪个世纪，是哪个世纪就在前面加上对应世纪的前缀，比如
当前是2066年，那么不管你的缩写是多少，肯定都是20打头的年份，这就是
yy的处理方式。

rr的处理方式说白了就是这样，如果当前是在世纪初，那么给的缩写如果是
世纪初的话那么给定缩写年份就是同世纪的，否则就属于上个世纪；
同理，如果当前是在世纪末，给的缩写如果是世纪末的话就属于本世纪，
否则就是下个世纪初了。

注意：两个日期类型相减得到的是天数，但是日期加上天数肯定得到日期，
      如果两个日期类型相加的话那么不被允许。

常用函数：

months_between计算两个日期之间的月数。
add_months:给指定的日期加上月数。
next_day:

select next_day(sysdate,'星期三')  from dual;
返回29-11月-06
其实就是返回从今天算起的下一个星期三的日期。

addmonths(hire_date,n)
n是任意的整数，也就是从给定日期算起加上n个月后得到的日期

select round(sysdate,'month') from dual;
也就是对月四舍五入，得到01-12月-06
如果用的是trunc，则得到01-11月-06

同样也可以对年进行四舍五入和截断，也是可以的

to_number函数：
select hire_date from employees ;
选出来的日子都是两位数。会有诸如'09'的情况

select to_char(hire_date,'fmdd month yyyy') from employees ;
这样一来日子就是该两位数就是两位数，不会出现09的情况。

select to_char(salary,'$999,999.00') from employees;
这样一来员工的薪水就会按照$12,000.00的格式显示，员工的薪水值不会改变。
注意：这里只能用9或0来限定格式，如果用了别的数字可能就会发生错误了，
具体格式还是要参阅英文参考手册。

有的时候必要的话必须用到函数的嵌套。

oracle中支持case语句（分支结构）

select last_name,salary,
case
when salary>5000 then 'very good'
     when salary<5000 then 'normal'
     end as result
from employees;

select last_name||' '||first_name,salary,
       case when salary<5000 then 'A'
   when salary between 5000 and 8000 then 'B'
   when salary > 8000 then 'C'
   end as result
  from employees
  order by result ;
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
日期的函数就暂时告一段落了，这里十分的乱，需要记忆的东西确实是非常的多……

现在是5点过10分，今天课程告一段落。

之后要把综合题目作一下，明天是多表联查以及子查询

-------------------------------------------------------------------------------------------------------------------
作业题目：
3、在employees表中查询first_name与last_name组成的完整姓名。显示姓名的总长度为15，如果姓名长度小于15，补'.'
   以表示姓名被完整显示，如果姓名长度大于15，则从第12位开始截取并补'*'以表明姓名被截取。

select salary,
case
     when length(first_name || ' ' || last_name) <= 15 then rpad(first_name || ' ' || last_name,15,'.')
     when length(first_name || ' ' || last_name) > 15 then substr(first_name || ' ' || last_name,1,12) || '***'
end as result
from employees ;

 

 

select length(first_name || ' ' || last_name) from employees ;

作业题目：

先执行以下SQL语句，创建一个employees表的备份：

create table emp_copy as
  select *
  from employees
  where manager_id=100 or
        manager_id in (select employee_id from employees where manager_id=100);
新表是什么含义？

回答：select employee_id from employees where manager_id=100:从employees表中选取出id号为100的雇主手下的雇员号
所以新表说白了就是要从原始的employees表中选取id号为100的雇主以及100号雇主手下所有雇员的手下全部雇员的全部信息。

 

现在要求以如下方式显示所有员工：
如果经理为100的员工有101、102、103，而101的直接下属为104、105,　102直接下属为106、107,　103直接下属为108，则显示为
101
--104
--105
102
--106
--107
103
--108
那么这条SQL语句应该怎么写？（注意，不能使用connect by）

 

select
   case
    when manager_id=100 then to_char(employee_id)
    when manager_id in(select employee_id from employees where manager_id=100)
         then concat('--',to_char(employee_id))
   end as result
from employees;

select concat(to_char(110),'||') from dual;  这条语句是试验用的，不用太在意。

5、对上题进行改进，将员工的manager_id为100的员工都置为空（应该怎么做？），那么上面的语句应该怎么写？