*********************************
** oracle 学习笔记第一天 **
** author Ice Xu (XuBin) **
** date 2006-10-30 **
*********************************
我们目前使用的是oralce 9i 9201 版本
恢复练习表命令：
sqlplus openlab/open123 @summit2.sql
登陆oracle的命令：
sqlplus 用户名/密码
show user 显示当前登陆的身份.
set pause on
set pause off 分页显示.
oracle中默认日期和字符是左对齐，数字是右对齐
table or view does not exist ; 表或示图不存在
edit 命令用于自动打开vi修改刚修执行过的sql的命令。
修改方法二：
l 3 先定位到行 c /旧串/新串
执行出错时，利用错误号来查错误:
!oerr ora 942 (装完系统后会装一个oerr工具，用于通过错误号来查看错误的具体信息)
想在sql中执行unix命令时，把所有的命令前加一个!就可以， 或者host( 用于从sql从切换至unix环境中去)
/*** 初次使用时注意 ****
运行角本时的命令：
先切换到unix环境下，cd $oracle_home cd sqlplus cd demo 下面有两个角本建表语句。
@demobld.sql
sqlplus nanjing/nanjing @demobid.sql 直接运行角本
保存刚才的sql语句： save 命令 第二次保存时要替换之前的角本 save 文件名 replace
把刚才保的sql重新放入 buffer中
spool 文件名
此命令会把所有的操作存在某个文件中去
spool off
练习1：查看s_emp表中员工的年工资
select first_name , salary*12 salary from s_emp;
给列起别名的命令：
利用关键字 as 或者用空格 "别名" 双引号内大小写敏感保持引号内容原样输出，如果不加双引号时，默认为大写
拼接字段：
select first_name||last_name "employees" from s_emp ;
oracle中表达字符串用单引号来表达：
select first_name||' '||last_name from s_emp;(在两个字段之间拼接一个空格)
查看当前用户所有的表：
练习2：(常用于批量更改数据)
set echo off
spool selecttab.sql;
select 'select * from ' || table_name ||' ; ' "table name " from user_tables;
spool off;
set head off(去除第一行)
set feed off(去除最后一行)
练习3：(查出s_emp表中所有员工的一年的总收入)
select first_name , salary*12*( 1+nvl(commission_pct/100 , 0 ) ) " year salary " from s_emp;
nvl函数 专用于处理空值的影响.
下午：
column 定义格式化输出
column last_name Heading format a15;
column last_name;
column salary justify left format $99,999.00 ( 定义工资的显示形式 )
$ echo $LANG
zh_CN.hp15CN
$ echo $NLS_LANG
simplified chinese_china.zhs16cgbk
ORDER BY 排序 升序和降序 ASC 升序(默认) DESC 降序
select * from s_emp order by dept_id , salary desc 部门号升序，工资降序
关键字distinct也会触发排序操作。
过滤操作： where 子句
select * from s_emp where dept_id=42; 查看部门号为42的所有员工
select * from s_emp where salary>1000 查看工资高于1000的所有员工
select salary from s_emp where first_name='Geroge' 找出名字为Geroge的员工的工资数
select table_name from user_tables where table_name='S_EMP'; 查某个具体表名时，表名的字符串必须要为大写
日期的默认的格式 DD-MON-RR
BETWEEN AND 在什么之间 NOT BETWEEN AND 注意区间：[ ]是一个闭区间
IN( LIST) 在某个集合中 NOT IN (list) 空值会有影响 (等于list其中任何一个就行，为提高效率常把比例高的放在前面)
LIKE 模糊配置 NOT LIKE 通配比较
IS NULL 是空
AND
OR
NOT
练习4:(找出表名以S_开头的所有表)对于一些特殊字符，要用到escape转义
select table_name from user_tables where table_name like 'S\_%' escape '\';
单行函数： (dual 哑表 )
字符函数：
lower 转小写 select lower('SQLPLUS') from dual;
upper 转大写 select upper('sqlplus') from dual;
initcap 首字符大写 select initcap('tarena') from dual;
concat 连接字符串 select concat(first_name , last_name) from s_emp;
substr 求子串 select substr('tarenasd0603' ,1,6) from dual; (取前六个字符) select substr('tarenasd0603',-2) from dual; (取后两个字符)
length 求字符长度 select length('tarena') from dual;
nvl 空值函数
select first_name , substr(first_name , -2 ) from s_emp; (查出s_emp表中所有用户名字的最后两个字符)
练习5： select first_name , salary from s_emp where lower(first_name)='george';
数值函数：
round 函数 select round(45.935, 2) from dual; 不带参数时默认为0位小数
trunc 函数 select trunc(45.995, 1) from dual;
日期函数：DD-MON-RR 日期格式敏感
世纪、年、月、日
sysdate 返回当前系统时间 select sysdate from dual;
更改当前会话的设置格式：
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
select sysdate-1, sysdate+1, sysdate , sysdate+1 from dual; 注意单位是以天为单位
MONTHS_BETWEEN (DATE1 , DATE2 ) 求两个日期之前相差的月数
add_months(date , 4 ) 在 date上再添加4个月
select next_day(sysdate,'FRIDAY') from dual ; 求这个日期的下一个FRIDAY
last_day 求月的最后一天
round 函数: select round(sysdate, 'MONTH') from dual; 参数可以为： MONTH YEAR(看上半年还是下半年)
select trunc(last_day(sysdate)+1) from dual;
select add_months(trunc(sysdate, 'MONTH'), 1 ) from dual ;
关于日期的两种形式：
转换函数：
to_char
从数字转化为char
从日期转化为char to_char(date, 'fmt' ) select to_char(sysdate, 'yyyy mm dd hh24:mi:ss') from dual;
select to_char(sysdate ,'YEAR MONTH dy
to_date
字符转日期 select to_date('2000 11 20', 'yyyy mm dd ') from dual;
select round(to_date('10-OCT-06' ,'dd-mon-RR') ) from dual;
to_number
字符转数字
select to_number('10') from dual ;