Oracle学习笔记1

1. Oracle的使用
1.1. SQLPLUS的命令
初始化表的位置：
set NLS_LANG=american_america.us7ascii (设置编码才可以使用下面脚本)
cd $ORACLE_HOME/rdbms cd demo summit2.sql
*********************************
我们目前使用的是oralce 9i 9201 版本
select * from v$version;
恢复练习表命令：
sqlplus **/** @summit2.sql //shell要在这个文件的位置。
登陆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 on 开启记录
spool off 关闭记录
spool 文件名此命令会把所有的操作存在某个文件中去
常见缩写：
nls national language support 国家语言支持
1.2. SQL的结构
|DDL 数据库定义
|DML 数据库管理
SQL――Commit rollback
|DCL 数据库控制
|grant+revoke 权限管理
表分为：系统表（数据字典），用户表
注：知道数据字典可以更便于使用数据库。
1.3. SQL语句
1.3.1. 纵向投影操作 select

select * from student;
select name||’(‘||id||’)’ EMPLOYEE from employee;
select name,salary*13 from employee;

NVL function
如果原来的数值是null的话，由指定数值替代。

select last_name,title,salary*NVL(commission_pct,0)/100 COMM from s_emp;

1.3.2. column使用
column(col) columnName clear/format/heading/justify format
column salary format $9999999.00 设置数字显示形式
column name fromat a15; 设置字符串显示15个字符
column salary justify left/right/center 输出格式
column salary heading text 设置显示的字段名
column clear 清除格式
column last_name;显示该字段名所用的格式
column salary justify left format $99,999.00 ( 定义工资的显示形式 )
1.3.3. order by
ORDER BY 排序升序和降序 ASC 升序(默认) DESC 降序
select * from s_emp order by dept_id , salary desc
部门号升序，工资降序
关键字distinct也会触发排序操作。

select * from employee order by 1; //按第一字段排序
NULL被认为无穷大。order by 可以跟别名。
1.3.4. where 选择操作（横向投影）
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'; 查某个具体表名时，表名的字符串必须要为大写
或者采用 upper(table_name)
select * from user_talbes where table_name like ‘s\_%’ escape ‘\’;
使用转义字符对关键字进行转义。

逻辑运算：
BETWEEN AND 在什么之间

NOT BETWEEN AND 注意区间：[ ]是一个闭区间
IN( LIST) 在某个集合中
NOT IN (list) 空值会有影响 (等于list其中任何一个就行，
为提高效率常把比例高的放在前面)
LIKE 模糊配置
NOT LIKE 通配比较
IS NULL 是空
AND
OR
NOT
练习3：(查出s_emp表中所有员工的一年的总收入)

select first_name , salary*12*( 1+nvl(commission_pct/100 , 0 ) ) " year salary " from s_emp;

nvl函数专用于处理空值的影响.
练习4:(找出表名以S_开头的所有表)对于一些特殊字符，要用到escape转义,并不是一定要用\，escape后面定义是什么字符为转义字符，那就用哪个字符

select table_name from user_tables where table_name like 'S\_%' escape '\';

1.3.5. 单行函数
单行函数： (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 空值函数两个参数的类型要匹配，统一的，表示：如果有，则返回前面的参数，如果没有就返回后面的参数

eg:select first_name,salary from s_emp where lower(first_name)='george';
select first_name , substr(first_name , -2 ) from s_emp; (查出s_emp表中所有用户名

字的最后两个字符)
默认的是从左向右，如果是-2则表示从右向左数
练习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;
日期函数：
oracle数据库中存放时间格式的数据，是以oracle特定的格式存贮的，占7个字节，与查询时显示的时间格式无关，具体哪个字节表示什么，我不太清楚，请高手补充。存贮的时间包括年月日时分秒，最小精度为秒，不存贮秒以下的时间单位。因此在一些前台支持毫秒级的程序(如PB客户端程序)连接到oracle数据库时应注意这点。查询时显示的时间格式，由会话环境决定，或者由用户定义，与数据库无关。
select sysdate from dual; 从伪表查系统时间，以默认格式输出。
sysdate+(5/24/60/60) 在系统时间基础上延迟5秒
sysdate+5/24/60 在系统时间基础上延迟5分钟
sysdate+5/24 在系统时间基础上延迟5小时
sysdate+5 在系统时间基础上延迟5天
所以日期计算默认单位是天
内置函数：
months_between（sysdate,addmonth(sysdate,5)） //两个月有多少天。
add_months(sysdate,-5) 在系统时间基础上延迟5月
add_months(sysdate,-5*12) 在系统时间基础上延迟5年
last_day(sysdate) 一个月最后一天
next_day(sysdate,’Friday’) 下个星期星期几。
round (sysdate,’day’) 不是四除五入了，是过了中午留下，不过的略掉
trunc(sysdate,’month’) 不到一月都省略
例子：
上月末的日期：select last_day(add_months(sysdate, -1)) from dual; 本月的最后一秒：select trunc(add_months(sysdate,1),'month') - 1/24/60/60 from dual 本周星期一的日期：select trunc(sysdate,'day')+1 from dual
年初至今的天数：select ceil(sysdate - trunc(sysdate, 'year')) from dual;
格式转换函数：
to_char显示日期：
从数字转化为char to_char(date,'格式')
从日期转化为char to_char(date, 'fmt' )
yyyy
2007 年份
year
two thousand seven 年份
mm
03 (格式缩写显示也缩写)
month
march 月份
dy
fri 星期几缩写
day
Friday 星期几
dd
16 一个月第几天
mi
30 分钟
ss
35 秒钟
hh
18 小时
rr
07 年 最近时间
yy
07 年当前世纪年份

select to_char(sysdate, 'yyyy mm dd hh24:mi:ss') from dual;
select to_char(sysdate, 'fmyyyy mm dd hh24:mi:ss') from dual;

查出三月分入职的员工：

select first_name,start_date from s_emp where to_char(start_date,'mm')='03';
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 ;

函数、表达式、隐式数据类型转换会导致索引用不上，where条件后面只能放单行函数，它起了一个过滤的的作用。
1.3.6. 组函数
group by 分组子句 对分组后的子句进行过滤还可以用having 条件 对分组后的条件进行过滤 where 是对记录进行过滤
avg(distinct | all )求平均值
count(distinct | all )统计
max(distinct | all ） 求最大值
min(distinct | all )求最小值
sum(distinct | all ) 求和
(所有组函数会忽略空值 , avg sum只能作用于数字类型）
求有提成员工的提成的平均值；

select avg(nvl(commission_pct ,0 ) ) from s_emp;

有多少人有提成：

select count( commission_pct ) from s_emp ;

count(*) 用于统计记录数：

select sum(commission_pct)/ count(*) from s_emp;

员工分部在多少个不同的部门：count 默认为作all的动作

select count(dept_id) from s_emp;

select count(distinct dept_id) from s_emp;

求各个部门的平均工资：group by 子句也会触发排序

select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ;

//对平均工资排序

select dept_id , avg(salary) aa from s_emp group by dept_id;

注意：group by 子句后面跟有条件只能是组函数查询的结果中的字段,所以我们会人为在结果要加入一些group by 要用的字段，如：dept_id可能不想要。
select region_id , count(*) from s_dept 此句会有错，count(*)是单组分组函数，如果加上group by region_id 就是找出同地区的部门数量。

select max(region_id) , count(*) from s_dept; 

(强制语法上可以正确,但是不能保证结果也会正确）
求各个部门不同工种的平均工资：

select dept_id , title, avg(salary) from s_emp group by dept_id , title ;

哪些部门的平均工资比2000高:

select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;

除了42部门以外的部门的平均工资:

select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42;
select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ;

(此种sql效率要高，先过滤) 再计算）
where 单行函数。
having 组函数。
求各个部门的平均工资:

// 这样统计不详细
select max(d.name) , avg (s.salary) from s_emp s, s_dept d where s.dept_id=d.id group by d.name;
//****这问题很经典，为了过 oracle sql 语法关而写max(d.name) ***
select max(d.name) , avg(e.salary) , max(r.name) from s_emp e, s_dept d , s_region r where e.dept_id = d.id and d.region_id=r.id
group by d.id ;

1.3.7. 多表连接
多表连接操作：
两表没有任何关联时会产生迪卡尔机：

select first_name , name from s_emp , s_dept;

1) 等值连接：
练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响）

select first_name , name from s_emp e, s_dept d where e.dept_id=d.id;

同时起了别名
select first_name , name from s_emp e, s_dept d where e.dept_id=d.id and
e.first_name='George';
具体到哪个人所在的部门
表的两边有空值的话，不会显示出来。
练习二：每个员工所在的部门和部门所在的地区

select first_name , s_dept.name, s_region.name from s_emp, s_dept, s_region where
s_emp.dept_id=s_dept.id
s_dept
等价于
select first_name , s_dept.name, s_region.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id;

等值连接：

select a.ename , a.sal, b.grade from emp a , s
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losa
and a.sal<=b.hisal;

当一个表的插入行之间有了关系时就发生了
select first_name , manager_id from s_emp; l
查出所有员工的部门领导的名称：( 这种sq会少一条记录，总经理没有被配置上）
select
e.manager_id=m.id; //关键是同一张表用不同的别名
(防止空值忽略，用（
加（+）一个记录都不能少； select e.first_name , m.first_n
e.manager_id=m.id(+);
+号放在哪边就表示在哪
这个例子中的领导有可能会没有(最高领导就再没有领导了，所以就＋号放在可能出现空值的一边)
标准写法：
显示没有员工的部门

select distinct d.name
from
where
e.dept_id(+)=d.id
and
e.dept_id is null;